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

SYMPTOMS

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. 

MORE INFORMATION

To resolve this problem, follow these steps: 

1. Run the check links procedure. To do this, follow these steps: 
 a. On the File menu, point to Maintenance , and then click Check Links
 b. In the Series list, click Sales
 c. Click Sales Setup , click Insert , and then click OK
 d. 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: 

a. Use one of the following steps, depending on whether you are using Microsoft SQL Server or SQL Server Desktop Engine (also known as MSDE 2000): 

     • 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 . 

     • If you are using Microsoft SQL Server 2000, start SQL Query Analyzer. To do this, click Start , point to Programs , point to Microsoft SQL Server , and then click Query Analyzer .
 
     • If you are using MSDE 2000, start the Support Administrator Console. To do this, click Start , point to Programs , point to Microsoft Administrator Console , and then click Support Administrator Console . 

 b. 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 


 c. 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 

 d. 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 below. Click Run in the File Download dialog box, and then follow the steps in the Fix it wizard. 

       Fix this problem Microsoft Fix it 50536

 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 

e. 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: 

a. 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 

 b. 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. 

SELECT MSTRNUMB, DEX_ROW_ID, * FROM SOP10100 where MSTRNUMB=xx 
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. 


 c. 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.
Properties

Article ID: 856311 - Last Review: 14 Feb 2017 - Revision: 1

Microsoft Dynamics GP 10.0, Microsoft Dynamics GP 2010, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2015, Microsoft Dynamics GP 2016, Sales Order Processing, Sales Order Processing

Feedback