I have been tracking a sporadic problem in one of our companies on GP 10.0 SP2 where new Sales Orders are being assigned previously used Master Numbers (or the exciting new behaviour of no Master number being assigned at all).. This has the effect of "relating" SOP documents that should not be (related). Yesterday "KB856311 - Multiple customers have documents that reference the same master number in Sales Order Processing in Microsoft Dynamics GP" was released, demonstrating that I am not alone in my pain.
There is no cause mentioned in the KB. Has anyone else been tracking this as a BUG? This is only happening very infrequently and we have third parties in place and customized triggers on SOP10100. There are many points of failure to trace.
I am about to implement a trigger on INSERT and UPDATE to SOP10100 on the Master Number field. If anyone else has tracked (and hopefully fixed) this issue, any suggestions and/or methodolgies would be greatly appreciated.
This is a long time known issue and I thought that the patch would have been included in the code by now. I first encountered it at a client 4 years ago.
There is a write up on this issue on our web site under Tips and Tricks Archive / Sales Order Processing called SOP Entry Window Cannot Find Unique Number that describes what is happening and even includes a script that fixes the problem.
Richard L. Whaley Author, Publisher, Consultant
Enhancing your Dynamics Knowledge!
We are not getting the error message (or any error message). We are nowhere near the maximum for MSTRNUMB. There are never *any* "holes" in the SOP40500 to fill in. Dynamics GP seems to be randomly assigning a previous Master Number to new documents. I appreciate your answer and the information contained on your website. I will verify your answer after some time passes (and perhaps more responses). Thanks!
When an order is deleted and the master number is more than a hundred back, the system will refill the missing master number with the next order. Then when the next one is added, it tries to find an unused number and setps forward, but only one hundred steps. Then it fails, sometimes throwing an error, sometimes not. My customer had both errors shown, blank Master Numbers, and duplicate Master Numbers. The patch posted on my site fixed it all.
We are also having this issue sporadically (tends to be one CSR's orders) even though the seed is correct. We will likely resort to a trigger (as much as I hate them as well) since an error is better than the mess this causes.
We implemented the SPROC provided by Richard and it appears to have solved about 95% of the problem. We are still getting "collisions" (two unrelated documents with the same Master Number) and we are still getting Documents with a Master Number of zero (0). Just not as frequently now. I have SQL scripts to help pull apart documents if you're interested.
I would love to get the scripts... we are looking to see if the deleted items are our issue as well (most types cannot be deleted). We are thinking also of a trigger that will prevent the overwrite of an existing master number an an soptype of 2.... Thanks again.
Disclaimer: I have never seen Richard's script therefore I am not in any position to judge it's effectiveness.
With that said, I have developed an approach to solve this issue which relies primarily in identifying all related SOP numbers, prior to running any updates on the master numbers. Please take a look at my article Using T-SQL and recursive CTE to find related sales documents for more details.
MG.-Mariano Gomez, MIS, MVP, MCP, PMP IntellPartners, LLC Blog: http://dynamicsgpblogster.blogspot.com
Is having a master number (0) a problem? Should I expect any problems by going ahead and posting the one invoice that has 0 as the master number?
Not a problem technical problem. This is the way GP stores all documents if you have Master Number Tracking shut off in SOP Options. The problem is that it and all subsequent documents won't be linked specifically to each other. Your Customer Service people may not like it.
We re-link releated document via SQL script, giving them a new master number.
I been in this situation with one of my clients and had to workaround this by creating triggers on both SOP10100 and SOP 30200 to update Master Number field automatically, below the article I wrote in this subject:
*** If my answer resolved you issue, please verify the answer to make it easy for other users who has the same case ***
Mohammad R. Daoud MVP-MCT
Have you seen the GP Excel Paste yet? http://di.jo/GPExcelPaste.aspx
Richard, do you know if this has been fixed in GP2010 or GP 2013?
Richard E. Wheeler 2013 and 2014 MVP
Member Microsoft Academic Alliance
www.rbsolutions.com Revered Business Solutions Ballston Lake, NY 518-877-0763 x10
I don't think it has been fixed in GP2013. We never had this problem before and since upgrading to GP2013 a couple of months ago it has now happened a few l times. I just started searching to see if anyone else has had this issue and came upon this post. Glad I did. I will try the stored procedure Richard Whaley has on his website.
I have a client that upgraded from GP10 to GP2013. The occurrences of this increased dramatically when we upgraded from GP10 to GP 2013. I don't know if it necessarily causes a problem. It is just a nuisance when it comes to reporting.