Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

SOP Master Numbers not being assigned properly

(0) ShareShare
ReportReport
Posted on by 8,515

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

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: SOP Master Numbers not being assigned properly

    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:

    • Loosing the table lock on the next master number table
    • Dropping the new number exhaustion handling code that handles when you run out of master numbers and wraps it back to start again
    • Change SQL transaction handling from conditional to unconditional possibly nested transaction
    • Introduce TRY CATCH to handle any "general" errors from the update SQL with a rollback

    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.

  • Suggested answer
    Steve Gray Profile Picture
    Steve Gray 25 on at
    RE: SOP Master Numbers not being assigned properly

    I encountered this today, here's the answer that I came up with dynDeveloper.com/.../0WX0

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: SOP Master Numbers not being assigned properly

    Do you run different number ranges within the different Order Type ID's, within the same document types?

    Tim

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SOP Master Numbers not being assigned properly

    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.

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: SOP Master Numbers not being assigned properly

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SOP Master Numbers not being assigned properly

    Hi Tim,

    We do have an integration that uses eConnect to import out web orders into GP.

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: SOP Master Numbers not being assigned properly

    Pracha,

    Do you use any products or integrations that use eConnect to generate sales SOP documents?

    Would be most helpful to know.

    Tim

  • Pracha Profile Picture
    Pracha on at
    RE: SOP Master Numbers not being assigned properly

    I'm in GP2016 and I see this problem too.  Doesn't look like it has been resolved. 

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: SOP Master Numbers not being assigned properly

    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.

  • RE: SOP Master Numbers not being assigned properly

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans