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