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 post is locked for comments
Glad someone else is giving this a go, for those not wanting to link out to another site here is what Steve is doing...
So you are changing the original GP procedure from...
ALTER PROCEDURE [dbo].[sopGetMasterNumber] @O_iOUTMasterNumber INT = NULL OUTPUT ,@O_iErrorState INT = NULL OUTPUT AS DECLARE @tTransaction TINYINT ,@iError INT SELECT @O_iOUTMasterNumber = 0 ,@O_iErrorState = 0 IF @@trancount = 0 BEGIN SELECT @tTransaction = 1 BEGIN TRANSACTION END UPDATE SOP40100 WITH ( TABLOCKX ,HOLDLOCK ) SET @O_iOUTMasterNumber = NXTMSTNO ,NXTMSTNO = NXTMSTNO 1 IF (@@rowcount <> 1) BEGIN SELECT @O_iErrorState = 21035 END IF ( (@O_iOUTMasterNumber = 99999999) OR (@O_iOUTMasterNumber = 0) ) AND @O_iErrorState = 0 BEGIN SELECT @O_iOUTMasterNumber = 1 UPDATE SOP40100 SET NXTMSTNO = 2 IF (@@rowcount <> 1) BEGIN SELECT @O_iErrorState = 21035 END END IF @O_iErrorState <> 0 BEGIN SELECT @O_iOUTMasterNumber = 0 IF @tTransaction = 1 ROLLBACK TRANSACTION END ELSE BEGIN IF @tTransaction = 1 COMMIT TRANSACTION END RETURN
To...
ALTER PROCEDURE sopGetMasterNumber @O_iOUTMasterNumber INT = NULL OUTPUT ,@O_iErrorState INT = NULL OUTPUT AS DECLARE @tTransaction TINYINT ,@iError INT SELECT @O_iOUTMasterNumber = 0 ,@O_iErrorState = 0 BEGIN TRY BEGIN TRAN -- be carefull not to exit this or the transaction will not get committed, you'll lock the table up UPDATE SOP40100 SET @O_iOUTMasterNumber = NXTMSTNO ,NXTMSTNO = NXTMSTNO 1 --declare @a int = 0 --declare @b int --set @b = 5 / @a COMMIT TRAN END TRY BEGIN CATCH --find out what went wrong --SELECT -- ERROR_NUMBER() AS ErrorNumber, -- ERROR_SEVERITY() AS ErrorSeverity, -- ERROR_STATE() AS ErrorState, -- ERROR_PROCEDURE() AS ErrorProcedure, -- ERROR_LINE() AS ErrorLine, -- ERROR_MESSAGE() AS ErrorMessage; --roll everything back ROLLBACK TRAN SELECT @O_iOUTMasterNumber = 0 ,@O_iErrorState = 21035 END CATCH RETURN GO
To summarise for those less familiar the changes are:
Whilst I'm keen to see some solutions to the problem, I'm nervous as it changes the behaviour of the application with a now nested transaction, the lack of table lock and change in commit behaviour due to that. Also I'd add back in the wrap around code for the master number. On the other hand I'd be interested in the thinking behind how this might fix the issue, hopefully you have some insight.
Tim.
I encountered this today, here's the answer that I came up with dynDeveloper.com/.../0WX0
Do you run different number ranges within the different Order Type ID's, within the same document types?
Tim
Tim W. - We do have people entering orders through out the day as well web orders being imported into GP with eConnect. We are now on GP 2016 R2 and we still have multiple master numbers being assigned to multiple documents. Sometimes it's 2 documents and other times it's 5 or 6 documents with the same master number. There is no rhyme or reason that I can find, it happens to 2 manually entered transactions and a combo of manually and imported transactions. I don't really ever see it happen to 2 or more imported orders from eConnect though - even thought we import multiple orders at once throughout the day.
Like Gene we also use several different Order Type ID's and the duplicates happen there too. Some days it happens one time, other days 5 or 6 times and some days not at all.
Bochoa - do you have a busy or high number of SOP users entering SOP orders as well as the web orders?
I think that is the other ingredient needed for this to occur.
Also is this still causing you a problem?
...and is is always that an imported transaction is one of the transactions sharing a number and never two manually entered transactions?
Tim.
Hi Tim,
We do have an integration that uses eConnect to import out web orders into GP.
Pracha,
Do you use any products or integrations that use eConnect to generate sales SOP documents?
Would be most helpful to know.
Tim
I'm in GP2016 and I see this problem too. Doesn't look like it has been resolved.
It would be interesting to see what happens if you instruct, even for a day, everyone to close the sop transaction window after each order, if they are not entering another and so not sit with a new sopnumber generated, waiting for the next order to be entered.
If the duplicate master number issue is like the duplicate sopnumbe issue then it could be a transcriptional issue with the returning of unused numbers to the "pool". My gut says that you will see much less duplicates if you try this as no one will be closing the window after having had a number allocated to them for extended periods. It would not solve it, but would narrow down the cause.
Tim.
Thank you Tim W. and Tim F. for helping me on this.
I did try the Stored Proc. I am still having the same issue.
I am getting 50-100 per day. The busy season is just about to start!
When I trace the process in a test system, it never fails. However, I cannot have 25 people adding / updating / Transferring / Posting transactions all at the same time like my production environment.
If I am lucky I can get 2-3 people at once. I could trace production, but I have up to 54 concurrent users.
That is a lot of noise. I can filter a little bit, but I am try to find an unknown.... I don't want to filter to much.
I have been trying to resolve this since gp2013 when we change Credit Card vendors. The new vendor uses master number where are old one used SopNumbe+SopType, Orignumb+OrigType to link items.
It seems like a wide spread issue and very little success resolving it.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,836
Most Valuable Professional
nmaenpaa
101,156