Duplicate SOP Master number in Dynamics GP
The credit card fulfilment add in that I wrote charges cards using sage pay API, from the GP screens or our custom SOP Fulfilment software, when the goods are despatched on a sales order.
In order for the charges to be tracked as the sales document migrates from Sales order to Invoice and subsequent fulfilments I utilise the master number of the order. However it turns out that the master number can end up getting shared by more than one document.
Master Numbers – what are they?
Master numbers can be enabled in the SOP setup screen.
When ever a new document is created from a source document, they are tied together with a common master number. The stored procedure:taSopGetMasterNumber in the company database serves us with the next master number from number in this setup screen when ever a new sales document is created without getting derived from another document.
You can use master numbers in SOP Document enquiry and reporting to see all related documents as the master number ties them all together.
Our Issue
Somehow we have found that some of our sales documents have ended up sharing master numbers with unrelated documents. We have seen this with the SOP number itself too but there it is more obvious.
To find the maser numbers that are causing a problem I put together the following script that will work on SQL server 2005+. Note that we don't use back order document types so only one sales order document exists per master number according to our business logic.
WHERE ORIGTYPE=0 should help identify them if you do use back order documents, but the query will not use the indexes on the tables and thus will take a long time to execute.
-- Script to identify duplicate Master Numbers
-- on Dynamics GP SOP Documents
With WorkHistUnion (MSTRNUMB, SOPNUMBE )as
(SELECT MSTRNUMB,SOPNUMBE FROM SOP10100 WHERE SOPTYPE=2
UNION ALL
SELECT MSTRNUMB,SOPNUMBE FROM SOP30200 WHERE SOPTYPE=2)
SELECT MSTRNUMB, COUNT(MSTRNUMB)from WorkHistUnionGROUP
BY MSTRNUMBHAVING COUNT(MSTRNUMB)>1ORDER BY MSTRNUM
Trying this on one of our GP9 companies I got (278 row(s) affected). So we have 278 instances of sales documents sharing master numbers with unrelated sales orders. Now I have introduced a check that looks for this senario and uses the customer CUSTNMBR as a key when charging cards.
Hopefully this should stop the wrong customer getting charged for a despatch of goods – bug squished!
Why do we get this situation? Dynamics GP does not lean very much on SQL server for database integrity. This is due to the heritage of the product, rooted in DBISAM, it has never used the database to ensure integrity. SQL server is quite capable through the correct transaction handling and constraint indexes of ensuring the master numbers are not shared, but sadly it has never been written into the database.There must be some holes in the application that allows this to happen in a similar way that our telesales team sometimes manage to get the same SOP number as each other.
Edit 2011
Some more web references have shown up since 2009 when I wrote this originally, the reason behind this happening can be found in these references too:
SOP Master Numbers not being assigned properly
SOP Master Numbers not being assigned properly (Dynamics community site)