In a recent implementation of Dynamics GP for a big distributor company, we’ve had a business requirement to track salesperson sales. The simplest automation for that was to create one site (Location ID) for every single salesperson depending on the area they are serving.
 |
| One Salesperson Serving Multiple Areas |
For each area, the salesperson will have one site. The business role is that the salesperson could server one area at a time (The area is a city in our example). In order to simplify the process, the end user creates one salesperson according to the following serial number (SLP-XXXX), one the other hand, the site will have the following numbering criteria:
[icon name=”transgender” class=”” unprefixed_class=””] Area Prefix
[icon name=”transgender” class=”” unprefixed_class=””] Serial Number
In order to automate the process, the following script has been applied in order to create a new primary site for every single salesperson when it is created.
— The following script creates a trigger on the RM00301 table, which uses eConnect stored procedure in order to create a new inventory site according to the salesperson parameters
CREATE TRIGGER [dbo].[DI_RM_GenerateSiteFromSalesPerson] ON [dbo].[RM00301]
AFTER INSERT
AS
DECLARE @SLPRSNID VARCHAR(500),
@SITEID VARCHAR(500),
@ADDRESS1 VARCHAR(500),
@ADDRESS2 VARCHAR(500),
@ADDRESS3 VARCHAR(500),
@CITY VARCHAR(500),
@Segment VARCHAR(500),
@STAXSCHD VARCHAR(500),
@Region VARCHAR(500),
@return_value int,
@O_iErrorState int,
@oErrString varchar(255)
SET @SLPRSNID = (SELECT TOP(1) SLPRSNID FROM INSERTED ORDER BY SLPRSNID)
SET @ADDRESS1 = (SELECT TOP(1) ADDRESS1 FROM INSERTED ORDER BY ADDRESS1)
SET @ADDRESS2 = (SELECT TOP(1) ADDRESS2 FROM INSERTED ORDER BY ADDRESS2)
SET @ADDRESS3 = (SELECT TOP(1) ADDRESS3 FROM INSERTED ORDER BY ADDRESS3)
SET @CITY = (SELECT TOP(1) CITY FROM INSERTED ORDER BY CITY)
IF @CITY = ‘AREA1’
BEGIN
SET @Segment = ’01’
SET @Region = ‘AR1’
END
IF @CITY = ‘AREA2’
BEGIN
SET @Segment = ’03’
SET @Region = ‘AR2’
END
IF @CITY = ‘AREA3’
BEGIN
SET @Segment = ’02’
SET @Region = ‘AR3’
END
SET @STAXSCHD = ‘SALES TAX’
SET @SITEID = REPLACE(@SLPRSNID, ‘SLP’, @Region)
EXEC @return_value = [dbo].[taCreateInventorySite]
@I_vLOCNCODE = @SITEID,
@I_vLOCNDSCR = @ADDRESS2,
@I_vADDRESS1 = @ADDRESS1,
@I_vADDRESS2 = @ADDRESS3,
@I_vADDRESS3 = @SLPRSNID,
@I_vCITY = @CITY,
@I_vLocation_Segment = @Segment,
@I_vSTAXSCHD = @STAXSCHD,
@I_vCountry = ‘JO’,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT
GO