Multiple customers have documents that reference the same master number in Sales Order Processing in Microsoft Dynamics GP

 

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.


Symptoms

Multiple customers have documents that reference the same master number in Sales Order Processing in Microsoft Dynamics GP.

Resolution

To resolve this problem, follow these steps:

1. Run the check links procedure. To do this, follow these steps:
   1. On the File menu, point to Maintenance , and then click Check Links .
   2. In the Series list, click Sales .
   3. Click Sales Setup , click Insert , and then click OK .
   4. When you are prompted to print the Error Log report, select a destination.
2. If you still experience the problem after you run the check links procedure, run the statements below against the company database to find the master numbers with the problem. To do this, follow these steps:
   1. If you are using Microsoft SQL Server 2005, start SQL Server Management Studio. To do this, click
      Start , point to Programs , point to Microsoft SQL Server 2005 , and then click SQL Server Management Studio .
   2. If you are using Microsoft SQL Server 2008, start SQL Server Management Studio. To do this, click
      Start , point to Programs , point to Microsoft SQL Server 2008 , and then click SQL Server Management Studio .
   3. To search the documents that are open and to note the results,run the following statements against the company database.

SELECT * INTO MSTRNMBRWORK from SOP10100
SELECT h.MSTRNUMB, h.CUSTNMBR from MSTRNMBRWORK w, SOP10100 h where

                  w.MSTRNUMB = h.MSTRNUMB AND

                  W. CUSTNMBR <> h.CUSTNMBR

1. 4. To search the History documents and to note the results, run the following statements against the company database.

SELECT * INTO MSTRNMBRHIST from SOP30200
Select h.MSTRNUMB, h.CUSTNMBR from MSTRNMBRHIST w, SOP30200 h where

                  w.MSTRNUMB = h.MSTRNUMB AND

                  w. CUSTNMBR <> h.CUSTNMBR

1. 5. Search the Work documents and the History documents and note the results. To have us run a statement to search the Work and History documents for you, go to the "
      Fix it for me " section. To fix this problem yourself, go to the "
      Let me fix it myself " section.
       

Fix it for me
To fix this problem automatically, click the
Fix this problem button or link. Click
Run in the
File Download dialog box, and then follow the steps in the Fix it wizard.


Note this wizard may be in English only; however, the automatic fix also works for other language versions of Windows.


Note if you are not on the computer that has the problem, save the Fix it solution to a flash drive or a CD and then run it on the computer that has the problem.


Let me fix it myself
SELECT h.MSTRNUMB, h.CUSTNMBR from SOP10100 w, SOP30200 h where

                  w.MSTRNUMB = h.MSTRNUMB AND
                  w. CUSTNMBR <> h.CUSTNMBR

1. 6. To delete the temporary tables that were created in the previous steps, run the following statements against the company database.

DROP TABLE MSTRNMBRWORK
DROP TABLE MSTRNMBRHIST

3. The results of the statements in step 2 provide the problematic master numbers. Follow these steps to correct the problematic master numbers:
   1. To determine the maximum value of the master number in the Sales Master Number Setup table, run the following statement against the company database, and note the results.

SELECT MAX(MSTRNUMB)FROM SOP40500

1. 2. To find the transactions that share a master number between two customers, run the following statement against the company database, and note the results. The transactions exist in the work table or in the history table.
   3. SELECT MSTRNUMB, DEX_ROW_ID, * FROM SOP10100 where MSTRNUMB=xx
   4.  

SELECT MSTRNUMB, DEX_ROW_ID, * FROM SOP30200 where MSTRNUMB=xx
Note Replace xx by using the problematic master numbers that you noted in step 2.

1. 5. Run statements to update the transactions for one customer and to reflect a new unique master number. When you run the statements, use the results from steps 3a and 3b. You may have to update multiple records. For example, you may have to update an order and an invoice.
      • If the transaction is in Work, run the following statement.

UPDATE SOP10100 SET MSTRNUMB=XXXX WHERE DEX_ROW_ID=Y
Note Replace XXXX by using a number that is equal to the maximum value of the master number plus 1. The maximum value of the master number was noted in step 3a. Replace Y with the correct DEX_ROW_ID value that you noted in step 3b.

• • • If the transaction is in History, run the following statement.

UPDATE SOP30200 SET MSTRNUMB=XXXX WHERE DEX_ROW_ID=Y
Note Replace XXXX by using a number that is equal to the maximum value of the master number plus 1. The maximum value of the master number was noted in step 3a. Replace Y with the correct DEX_ROW_ID value that you noted in step 3b.

4. Run the script in step 3a again to determine the new maximum value of the master number.
5. On the Tools menu, point to Setup , point to Sales , and then click Sales Order Processing . Type a number in the
   Next Master Number field. Type a number that is equal to the new maximum value of the master number plus 2. The new maximum value of the master number was determined in step 4.