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

SalesPerson and Sales Territory details - Customer Card and Customer Address Maintenance

In a previous post, an interesting case related to populating sales person and sales territory details when creating a new sales transaction entry sheds a light on an integration issue that is highly dependent on sales person and territory setup. In this essence, I believe it would be quite important to provide further valuable information as related to the same topic. 

In the sales module, there are two specific windows to be considered when filling out the sales person and territory assignment with a specific customer, which are: Customer Maintenance Window and Customer Address Maintenance window. Technically speaking, the data is stored in RM00101 (corresponding to the customer card) and RM00102 (corresponding to the customer address maintenance). 

Database Tables:

• RM00101 - Customer Card (Cards > Sales > Customer)
• RM00102 - Customer Address  (Cards > Sales > Customer Address)

Here are some facts:

• When filling out the sales person and territory ID in the Customer Maintenance window, the corresponding customer address details are updated accordingly.
• When filling out the sales person and territory ID in the Customer Address Maintenance window, the customer card is NOT updated accordingly.
• When creating a new sales transaction (SOP document), sales person and territory details for a specific customer are derived from the Customer Address Maintenance window (not the customer card)
• In case the both customer maintenance and customer address are filled with sales person and territory details, then the data stored in "Customer Address Maintenance"  will default when creating a sales order. Meanwhile, if there is no details in Customer Address window, the details will be derived from customer maintenance window.

Sales Person and Territory Details - Setup and Default Values

Therefore, when validating sales person and sales territory details. Checking the customer card, customers smart list or customer master report doesn't mean that the "Sales Person" and "Sales Territory" details are the ones that will be populated as default values when creating a sales order. The correct place to check is your customer address (RM00102)

The following SQL Statement will help you reconcile your sales person details between customer maintenance window and customer address window (RM00101 versus RM00102)

     

SELECT  A.CUSTNMBR ,

        A.ADRSCODE ,

        A.SLPRSNID Master_SalesPerson ,

        A.SALSTERR Master_SalesTerritory ,

        B.SLPRSNID Address_SalesPerson ,

        B.SALSTERR Address_SalesTerritory

FROM    dbo.RM00101 AS A

        LEFT OUTER JOIN dbo.RM00102 AS B ON A.CUSTNMBR = B.CUSTNMBR

                                            AND A.ADRSCODE = B.ADRSCODE

WHERE   RTRIM(LTRIM(A.SLPRSNID)) <> RTRIM(LTRIM(B.SLPRSNID))

        OR RTRIM(LTRIM(A.SALSTERR)) <> RTRIM(LTRIM(B.SALSTERR))

               

Best Regards, 

Mahmoud M. AlSaadi