Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Thursday, April 23, 2015

Sales Order Integration–Sales Person and Territory are not populated to Line Items


In one of the old environment GP 10 service pack 5, which is integrated with order-taking system, eConnect showed an abnormal case when importing sales orders into Dynamics GP. It is represented with migrating the sales person assigned to each customer for the SOP header table only without populating the sales person ID into the line item details.

Here is a sample of an imported invoice in which all the details are correct with no issues at all:

Sales Invoice

Sales Transaction Entry window

Clicking the blue arrow next to the customer, will open up the sales customer detail entry in which the sales person ID is populated correctly according to the setup configured on the customer card level

Sales Customer Detail

Sales Customer Detail Entry – Sales Person and Territory

On the other hand, clicking on one of the line item and checking the sales item detail entry shows that no “sales person” no “sales territory” is populated.

Sales Line Item Details

Sales Item Detail Entry = Sales Person and Territory

Concern:

And the question is, what are the consequences for such issue, what if the sales person ID is not populated to the detail level and being properly recorded on the header level ?

The sales amount of the sales person will be recorded correctly, since the total of the sales invoice will be added up to the sales of this sales person, and practically commissions are being calculated properly.

>> The problem is, you will not be able to report how much from a specific SKU this sales person has sold, only total numbers. For instance, the sales line items smart list will report nothing for the sales person, as you cannot filter neither by the territory nor the sales person id.

Issue Identification:

The issue is identified on the SQL level as shown below:


SELECT  *

FROM    ( SELECT    SOPNUMBE ,

                    SOPTYPE ,

                    SLPRSNID ,

                    SALSTERR

          FROM      SOP10100

        ) AS A

        LEFT OUTER JOIN ( SELECT    SOPNUMBE ,

                                    SOPTYPE ,

                                    SLPRSNID ,

                                    SALSTERR

                          FROM      dbo.SOP10200

                        ) AS B ON A.SOPNUMBE = B.SOPNUMBE

                                  AND A.SOPTYPE = B.SOPTYPE

WHERE   ( B.SLPRSNID = ''

          OR B.SALSTERR = ''

        )

        AND ( A.SLPRSNID <> ''

              AND A.SALSTERR <> ''

            )


Test - Sales Person ID mismatching

Matching sales person and territory details – SOP header versus details (SOP10100 and SOP10200)

eConnect is not passing neither the sales person ID nor the territory, leaving the default configuration to take effect once a sales order is imported. The result is having both fields populated in the header only leaving empty sales order details.

Resolution:

Method One

The easy method to get this done is practically to adjust the eConnect and pass both parameters for <taSopHdrIvcInsert> and <taSopLineIvcInsert>

eConnect schema - SOP Header and Detail

Method Two:

Another method is to get this corrected in the work tables of Dynamics GP before being posted, through SQL jobs that tracks down such issues and correct it accordingly.

This can be done practically by proposing two different scenarios, the first one is to build an SQL solution which will track down the issue, correct every single record individually and keep a log for every single correction. The other scenario is to mass update the whole records once at a time.

Now lets start with the first scenario (Individual correction, with Log)

The first step is to create the views which will retrieve the corrupted records (Sales line item details missing sales person and territory data, while header has them populated correctly)

View Definition


CREATE   VIEW [dbo].[V_SalesTransactions_SalesPersonMatching]

AS

    SELECT  *

    FROM    ( SELECT    DOCDATE AS HD_DOCDATE ,

                        SOPTYPE AS HD_SOPTYPE ,

                                         CUSTNMBR,

                        SLPRSNID AS HD_SLPRSNID ,

                        SALSTERR AS HD_SALSTERR ,

                        SOPNUMBE AS HD_SOPNUMBE

              FROM      SOP10100

              WHERE     SOPNUMBE IN ( SELECT    DISTINCT

                                                SOPNUMBE

                                      FROM      SOP10200

                                      WHERE     SLPRSNID = ''

                                                AND SALSTERR = '' )

                        AND dbo.SOP10100.SLPRSNID <> ''

            ) AS A

            LEFT OUTER JOIN ( SELECT    SALSTERR AS DT_SALSTERR ,

                                        SLPRSNID AS DT_SLPRSNID ,

                                        SOPNUMBE AS DT_SOPNUMBE ,

                                        SOPTYPE AS DT_SOPTYPE ,

                                        ITEMNMBR,

                                                                     DEX_ROW_ID

                              FROM      SOP10200

                              WHERE     SLPRSNID = ''

                                        AND SALSTERR = ''

                            ) AS B ON A.HD_SOPNUMBE = B.DT_SOPNUMBE

                                      AND A.HD_SOPTYPE = B.DT_SOPTYP


The next step is to create a log table to keep track of every single update, the old and new values of the sales person id and sales territory fields.

 

Log Table Definition


CREATE TABLE [dbo].[TB_UpdatedSalesPerson_Log]

    (

      [SOPTYPE] [smallint] NULL ,

      [SOPNUMBE] [char](21) NULL ,

      [CUSTNMBR] [char](21) NULL ,

      [ITEMNMBR] [char](31) NULL ,

      [OLD_SLPRSNID] [char](15) NULL ,

      [OLD_SALSTERR] [char](15) NULL ,

      [Updated_SLPRSNID] [char](15) NULL ,

      [Updated_SALSTERR] [char](15) NULL ,

      [Dex_Row_ID] [int] NULL ,

      [TimeStamp] [datetime] NULL

    )

 


 

The next step is to build the SQL job which will check the imported data on frequently and keep track of any errors which occurs in the log table

 

SQL Job Definition
      Note:
      Change the database included under “@database_name = N'TWO'”


USE [msdb]

GO

 

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT  @ReturnCode = 0

 

IF NOT EXISTS ( SELECT  name

                FROM    msdb.dbo.syscategories

                WHERE   name = N'[Uncategorized (Local)]'

                        AND category_class = 1 )

    BEGIN

        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',

            @type = N'LOCAL', @name = N'[Uncategorized (Local)]'

        IF ( @@ERROR <> 0

             OR @ReturnCode <> 0

           )

            GOTO QuitWithRollback

 

    END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Jb_Process_SalesPersonMismatching',

    @enabled = 1, @notify_level_eventlog = 0, @notify_level_email = 0,

    @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,

    @description = N'No description available.',

    @category_name = N'[Uncategorized (Local)]', @owner_login_name = N'sa',

    @job_id = @jobId OUTPUT

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,

    @step_name = N'Step One', @step_id = 1, @cmdexec_success_code = 0,

    @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2,

    @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,

    @os_run_priority = 0, @subsystem = N'TSQL',

    @command = N'

IF EXISTS ( SELECT  *

            FROM    V_SalesTransactions_SalesPersonMatching )

    BEGIN

       

        DECLARE @SOPTYPE AS NVARCHAR(MAX) ,

            @SOPNUMBE AS NVARCHAR(MAX) ,

            @CUSTNMBR AS NVARCHAR(MAX) ,

            @ITEMNMBR AS NVARCHAR(MAX) ,

            @Old_SLPRSNID AS NVARCHAR(MAX) ,

            @Old_SALSTERR AS NVARCHAR(MAX) ,

            @Updated_SLPRSNID AS NVARCHAR(MAX) ,

            @Updated_SALSTERR AS NVARCHAR(MAX) ,

            @Dex_ROW_Id AS INT

             

        SET @DEX_ROW_ID = ( SELECT TOP 1

                                    DEX_ROW_ID

                            FROM    V_SalesTransactions_SalesPersonMatching

                          )

        SET @SOPTYPE = ( SELECT DT_SOPTYPE

                         FROM   V_SalesTransactions_SalesPersonMatching

                         WHERE  DEX_ROW_ID = @DEX_ROW_ID

                       )

        SET @SOPNUMBE = ( SELECT    DT_SOPNUMBE

                          FROM      V_SalesTransactions_SalesPersonMatching

                          WHERE     DEX_ROW_ID = @DEX_ROW_ID

                        )

        SET @CUSTNMBR = ( SELECT    CUSTNMBR

                          FROM      V_SalesTransactions_SalesPersonMatching

                          WHERE     DEX_ROW_ID = @DEX_ROW_ID

                        )

        SET @ITEMNMBR = ( SELECT    ITEMNMBR

                          FROM      V_SalesTransactions_SalesPersonMatching

                          WHERE     DEX_ROW_ID = @DEX_ROW_ID

                        )

        SET @Old_SLPRSNID = ( SELECT    DT_SLPRSNID

                              FROM      V_SalesTransactions_SalesPersonMatching

                              WHERE     DEX_ROW_ID = @DEX_ROW_ID

                            )

        SET @Old_SALSTERR = ( SELECT    DT_SALSTERR

                              FROM      V_SalesTransactions_SalesPersonMatching

                              WHERE     DEX_ROW_ID = @DEX_ROW_ID

                            )

        SET @Updated_SLPRSNID = ( SELECT    HD_SLPRSNID

                                  FROM      V_SalesTransactions_SalesPersonMatching

                                  WHERE     DEX_ROW_ID = @DEX_ROW_ID

                                )

        SET @Updated_SALSTERR = ( SELECT    HD_SALSTERR

                                  FROM      V_SalesTransactions_SalesPersonMatching

                                  WHERE     DEX_ROW_ID = @DEX_ROW_ID

                                )

 

 

        UPDATE  SOP10200

        SET     SALSTERR = @Updated_SALSTERR ,

                SLPRSNID = @uPDATED_SLPRSNID

        WHERE   Dex_Row_ID = @Dex_Row_ID

 

 

        INSERT  INTO TB_UpdatedSalesPerson_Log

        VALUES  ( @SOPTYPE, @SOPNUMBE, @CUSTNMBR, @ITEMNMBR, @Old_SLPRSNID,

                  @Old_SALSTERR, @Updated_SLPRSNID, @Updated_SALSTERR,

                  @DEX_ROW_ID, GETDATE() )

    END', @database_name = N'TWO', @flags = 0

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,

    @name = N'Default', @enabled = 1, @freq_type = 4, @freq_interval = 1,

    @freq_subday_type = 2, @freq_subday_interval = 10,

    @freq_relative_interval = 0, @freq_recurrence_factor = 0,

    @active_start_date = 20150422, @active_end_date = 99991231,

    @active_start_time = 0, @active_end_time = 235959,

    @schedule_uid = N'e5712bb7-9053-42c1-84ae-fe65f0f50315'

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,

    @server_name = N'(local)'

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF ( @@TRANCOUNT > 0 )

    ROLLBACK TRANSACTION

EndSave:

 

GO


 

As you process sales orders, the log details will be recorded for every single update statement as illustrated below: 

 

Log table

 

The other scenario which comes with a better performance, but yet miss the structured log of the first scenario is to update all mismatched records according to the view mentioned above


Mass update mismatch records


IF EXISTS ( SELECT  *

            FROM    V_SalesTransactions_SalesPersonMatching )

 UPDATE  SOP10200

 SET    

 SLPRSNID =

( SELECT HD_SLPRSNID

    FROM   V_SalesTransactions_SalesPersonMatching

    WHERE 

       V_SalesTransactions_SalesPersonMatching.DT_SOPNUMBE = dbo.SOP10200.SOPNUMBE

AND V_SalesTransactions_SalesPersonMatching.DT_SOPTYPE = dbo.SOP10200.SOPTYPE

AND V_SalesTransactions_SalesPersonMatching.DEX_ROW_ID = dbo.SOP10200.DEX_ROW_ID

) ,

 SALSTERR =

( SELECT HD_SALSTERR

    FROM   V_SalesTransactions_SalesPersonMatching

    WHERE 

       V_SalesTransactions_SalesPersonMatching.DT_SOPNUMBE = dbo.SOP10200.SOPNUMBE

AND V_SalesTransactions_SalesPersonMatching.DT_SOPTYPE = dbo.SOP10200.SOPTYPE

AND V_SalesTransactions_SalesPersonMatching.DEX_ROW_ID = dbo.SOP10200.DEX_ROW_ID

)

 WHERE   DEX_ROW_ID IN ( SELECT DISTINCT

                                DEX_ROW_ID

                         FROM   

                         V_SalesTransactions_SalesPersonMatching )


 

Summary

Method which builds a database solution to correct imported records has two option; an option for correcting individual records (scenario one) or to mass correct records (scenario two)

 

For scenario one:

  • The individual records in SOP10200 will be revised and logically tested to check whether an update is required or not.
  • An update on SOP10200 is run to match the sales person ID and territory ID with the ones in the SOP10100
  • Log is kept accordingly for all the changes.

Script required for Scenario one:

  • View definition , V_SalesTransactions_SalesPersonMatching
  • Table log definition, TB_UpdatedSalesPerson_Log
  • SQL Job, Jb_Process_SalesPersonMismatching

 

For scenario two:

  • The individual records in SOP10200 will be revised and logically tested to check whether an update is required or not
  • Mass update is performed on all the lines at once
  • No log is kept

Scripts required for Scenario Two:

  • View definition, V_SalesTransactions_SalesPersonMatching
  • Mass update, Mass Update

 

 

All scripts can be downloaded from here >> Download Link

 

Best Regards,
Mahmoud M. AlSaadi

1 comment:

  1. Very nice post.
    Worked through the exact same issue in my GP10 invoice integration, and MS told me SLPRSID (salesperson ID) and SALSTERR (sales territory) not importing was an expected behavior when commissions were off in GP. Setting CREATECOMM = 1 in the sales header worked for me.

    ReplyDelete