«
»

Automatically Generate New Inventory Location Card from Sales Person Card

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:
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 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

 

Best Regards,
Mahmoud M. AlSaadi

Tags:

This entry was posted on Monday, February 5th, 2018 at 8:15 am and is filed under Dynamics GP, Siroco GP Care. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.