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

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