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]
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
Best Regards,
Mahmoud M. AlSaadi
