web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics GP Essentials / Sales Order Integration–Sal...

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

Views (2896)
Mahmoud Saadi Profile Picture Mahmoud Saadi 32,738


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

This was originally posted here.

Comments

*This post is locked for comments