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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics GP Essentials / SalesPerson and Sales Terri...

SalesPerson and Sales Territory details - Customer Card and Customer Address Maintenance (Part 2) | SQL Correction Method

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

Proceeding with the previous post on Sales Person and Sales Territory details - Customer Card and Customer Address Maintenance, there is a specific concept that is yet to be explained in this post along with the method on how to correct such issues. 

When creating a new invoice in the system, master details such as customer name, assigned sales person, territory ..etc are inherited from the "master" files and stored in the "transaction" files, which means, any changes or modifications on the master level will not be rolled down into associated transactions. In this essence, in case such a mistake occurred and you had to track down the associated SOP transactions and correct Sales Person and Sales Territory details, you will have to consider SQL Table correction by reconciling these values against the master table.

For this case specifically, in which the SOP sales transaction has inherited an incorrect sales person and sales territory details, the SQL query below reconciles and "Mass" updates the values in both SOP30200 and SOP30300 (Posted Sales Transactions). 

Download Link >> Scripts can be downloaded from this Link 

Correcting Posted SOP Header - Sales Person and Sales Territory Details according to the Customer Card


/*--------------------------------------------------------------------------------
Creation Date: 1st of November, 2015
Created by: Mahmoud M. AlSaadi

View for the posted SOP Header transactions which have incorrect sales person and sales territory details.

Revision History:
Revision No.            Revision Date    Description
1                       01/11/2015      Original Version
------------------------------------------------------------------------------- */

CREATE VIEW V_SalesPerson_MasterVersuTransactoins
AS
    SELECT  HD.SOPNUMBE ,
            HD.DOCDATE ,
            HD.SOPTYPE ,
            HD.SLPRSNID AS SOP_SLPRSNID ,
            HD.SALSTERR AS SOP_SALSTERR ,
            RM_MSTR.SLPRSNID AS Master_SLPRSNID ,
            RM_MSTR.SALSTERR AS Master_SALSTERR ,
            HD.DEX_ROW_ID AS SOP_DexRowID
    FROM    SOP30200 AS HD
            LEFT OUTER JOIN RM00101 AS RM_MSTR ON HD.CUSTNMBR = RM_MSTR.CUSTNMBR
   --  WHERE   DOCDATE > '2015-10-17 00:00:00.000'
            AND ( RTRIM(LTRIM(HD.SLPRSNID)) <> RTRIM(LTRIM(RM_MSTR.SLPRSNID))
                  OR RTRIM(LTRIM(HD.SALSTERR)) <> RTRIM(LTRIM(RM_MSTR.SALSTERR))
                )
GO


/*--------------------------------------------------------------------------------
Creation Date: 1st of November, 2015
Created by: Mahmoud M. AlSaadi

Update Script to correct SOP30200 table against the original Customer Master - Sales person
and sales territory details.

Do Not Run this script on Live database unless you have back up, it may updates hundreds
or even thousands of records. It must be used for special purposes only.

The script has been tested on a limited data sample, it may not cover all possible scenarios

Revision History:
Revision No.            Revision Date    Description
1                       01/11/2015      Original Version
------------------------------------------------------------------------------ */
UPDATE  SOP30200
SET     SLPRSNID = ( SELECT Master_SLPRSNID
                     FROM   V_SalesPerson_MasterVersuTransactoins
                     WHERE  V_SalesPerson_MasterVersuTransactoins.SOPNUMBE = SOP30200.SOPNUMBE
                            AND V_SalesPerson_MasterVersuTransactoins.SOPTYPE = SOP30200.SOPTYPE
AND V_SalesPerson_MasterVersuTransactoins.SOP_DexRowID = SOP30200.DEX_ROW_ID ) ,
        SALSTERR = ( SELECT Master_SALSTERR
                     FROM   V_SalesPerson_MasterVersuTransactoins
                     WHERE          CONVERT(VARCHAR(MAX),V_SalesPerson_MasterVersuTransactoins.SOPNUMBE) =
CONVERT(VARCHAR(MAX), SOP30200.SOPNUMBE)
                     AND V_SalesPerson_MasterVersuTransactoins.SOPTYPE
                         = SOP30200.SOPTYPE
                     AND V_SalesPerson_MasterVersuTransactoins.SOP_DexRowID
                          = SOP30200.DEX_ROW_ID
                   )
WHERE   SOP30200.DEX_ROW_ID IN ( SELECT DISTINCT
                                        SOP_DexRowID
                                 FROM   V_SalesPerson_MasterVersuTransactoins )


Correcting Posted SOP Details- Sales Person and Sales Territory Details according to the Customer Card


/*--------------------------------------------------------------------------------
Creation Date: 1st of November, 2015
Created by: Mahmoud M. AlSaadi

View for the posted SOP Details transactions which have incorrect sales person and sales territory details.

Revision History:
Revision No.            Revision Date    Description
1                       01/11/2015      Original Version
------------------------------------------------------------------------------- */
CREATE VIEW V_SalesPerson_MasterVersuTransactoins_History
AS
    SELECT  A.SOPNUMBE ,
            A.SOPTYPE ,
            A.DOCDATE ,
            A.SLPRSNID ,
            A.SALSTERR ,
            B.SLPRSNID Line_SalesPerson,
            B.SALSTERR Line_Territory,
            B.DEX_ROW_ID
    FROM    SOP30200 AS A
            LEFT OUTER JOIN SOP30300 AS B ON A.SOPNUMBE = B.SOPNUMBE
                                             AND A.SOPTYPE = B.SOPTYPE
--  WHERE   DOCDATE > '2015-10-17 00:00:00.000'

            AND ( A.SLPRSNID <> B.SLPRSNID
                  OR A.SALSTERR <> B.SALSTERR
                )
GO
SELECT  *
FROM    V_SalesPerson_MasterVersuTransactoins_History


/*--------------------------------------------------------------------------------
Creation Date: 1st of November, 2015
Created by: Mahmoud M. AlSaadi

Update Script to correct SOP30300 table against the original Customer Master - Sales person
and sales territory details.

Do Not Run this script on Live database unless you have back up, it may updates hundreds
or even thousands of records. It must be used for special purposes only.

The script has been tested on a limited data sample, it may not cover all possible scenarios

Revision History:
Revision No.            Revision Date    Description
1                       01/11/2015      Original Version
------------------------------------------------------------------------------ */

UPDATE  SOP30300

SET     SLPRSNID = ( SELECT SLPRSNID
                     FROM   V_SalesPerson_MasterVersuTransactoins_History
                     WHERE  V_SalesPerson_MasterVersuTransactoins_History.SOPNUMBE
                                                        = SOP30300.SOPNUMBE
                            AND V_SalesPerson_MasterVersuTransactoins_History.SOPTYPE
                                                     = SOP30300.SOPTYPE
                            AND V_SalesPerson_MasterVersuTransactoins_History.DEX_ROW_ID
                                                     = SOP30300.DEX_ROW_ID
                   ) ,

        SALSTERR = (

              SELECT SALSTERR

        FROM   V_SalesPerson_MasterVersuTransactoins_History

        WHERE  CONVERT(VARCHAR(MAX), V_SalesPerson_MasterVersuTransactoins_History.SOPNUMBE)

                    = CONVERT(VARCHAR(MAX), SOP30300.SOPNUMBE)

         AND V_SalesPerson_MasterVersuTransactoins_History.SOPTYPE

                       = SOP30300.SOPTYPE
         AND V_SalesPerson_MasterVersuTransactoins_History.DEX_ROW_ID
                   = SOP30300.DEX_ROW_ID
                   )
        WHERE   SOP30300.DEX_ROW_ID IN (
        SELECT DISTINCT
                DEX_ROW_ID
        FROM    V_SalesPerson_MasterVersuTransactoins_History )



 

Helping Note !

If you are not a fan of mass SQL updates, you may look at this previous post which provides an alternative correcting methodology; which in turn keeps detailed log of the old and new values. Such solution might be more professional than updating huge records without keeping track of the modification for Audit purposes.


Best Regards,
Mahmoud AlSaadi

This was originally posted here.

Comments

*This post is locked for comments