Dynamics GP Essentials: 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 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 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 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 <> ''

            )

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>

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]

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]

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