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 create one salesperson according to the following serial number (SLP-XXXX), one the other hand, the site will have the following numbering criteria:
- Area Prefix
- 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 create 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]
DECLARE @SLPRSNID VARCHAR(500),
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'
SET @Segment = '01'
SET @Region = 'AR1'
IF @CITY = 'AREA2'
SET @Segment = '03'
SET @Region = 'AR2'
IF @CITY = 'AREA3'
SET @Segment = '02'
SET @Region = 'AR3'
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
Mahmoud M. AlSaadi