Saturday, January 31, 2009

Why does my next document number change randomly?

This is a very common question among Dynamics GP users. In fact, it is a very common situation that can puzzle even seasoned consultants. So here is the mystery debunked.

Every Dynamics GP next document number in every module is generally gathered from a setup table. Usually, this table column is called Next Number. For example, GL will have a Next Journal Number; POP will have a Next PO Number and so on, depending on the module and the transaction. To focus on one thing and one thing only, I will use POP and the next purchase order document number.

In multi-user PO entry environments, for example, if user A is working on PO 10, user B is working PO 11, and user C is working on PO 12, but user B cancels, void, or delete their PO, when user A attempts to enter another PO, they will still end up with 13. It will appear to user A that the PO numbers are being skipped randomly. But this is clearly not the case we are discussing here, and rather the normal application's behavior.

In principle, the next purchase order document number should be the maximum numeric portion of the next purchase order number increased by 1. Initially, GP will read the value from the POP_Setup table, then it will attempt to reserve this value, this is, making sure the next PO number has not been used.

In it's attempt to reserve the value, GP will first search forward, up to MAX_DOCID_RETRIES (a constant in the application's source code with a value of 1000). If at the end of this attempt to reserve a number the system is still not successful, it will move backwards up to MAX_DOCID_RETRIES again.

This pehaps explains why certain users will see their PO Numbers jump from say 8,120 to 7,230. If the system finds and empty position, it "grabs" (as in reserves) that PO number, incrementing the next PO number to whatever value is next from the "empty" position it found.

Unfortunately, this new value could have already been used, hence it will repeat the check the next time someone enters a PO. This can certainly become an issue, because Dynamics GP cannot always find a value to reserve. This is why some users will experience a blank PO number field when the system exhausts it's attempts.

To correct this issue, it is necessary to find the "real" next PO number (or whatever document number for whatever transaction you are interested in).

1) Copy and execute the two SQL Server UDF from my article "How to split Dynamics GP's alphanumeric column values in SQL Server". These functions will be the starting point and will need to be executed against the company database.

2) Now, they can be used in the following statement, as follows:

FixNextPONumber.sql

-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
DECLARE @MaxPONumber INT;
DECLARE @AlphaPart VARCHAR(10);


SELECT @AlphaPart = dbo.fGetAlpha(PONUMBER)FROM POP40100;


WITH MyPOs (PONUMBER) AS (
SELECT PONUMBER FROM POP10100
UNION ALL
SELECT PONUMBER FROM POP30100
)
SELECT @MaxPONumber = MAX(dbo.fGetNumber(PONUMBER)) + 1
FROM MyPOs;


UPDATE POP40100 SET PONUMBER =
@AlphaPart + LEFT(REPLICATE('0', LEN(PONUMBER) - LEN(@AlphaPart)), LEN(PONUMBER) - LEN(@AlphaPart) - LEN(CONVERT(VARCHAR(20), @MaxPONumber))) +
CONVERT(VARCHAR(20), @MaxPONumber);
If you have noticed this issue in other modules with other transactions and you would like me to post a script to correct this issue, just post back with a request to do so, otherwise, stay tuned! I will update this article with scripts for other modules and other transactions.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


UPDATES TO THIS ARTICLE:

02/02/2009 - Added code to fix the next journal entry number in GL.

FixNextGLNumber.sql

-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied


DECLARE @MaxJournal INT;


WITH MyJrnl(JRNENTRY) AS (
SELECT JRNENTRY FROM GL20000
UNION ALL
SELECT JRNENTRY FROM GL30000
)
SELECT @MaxJournal = MAX(JRNENTRY) + 1 FROM MyJrnl;


UPDATE GL40000 SET NJRNLENT = @MaxJournal;
The next journal entry number happens to be a numeric integer value, hence not requiring our two functions, simplifying the query a great deal.

07/23/2009 -- Added code to fix next voucher number in payables

FixNextPMVoucherNumber.sql

-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
DECLARE @MaxVoucherNumber INT;
DECLARE @AlphaPart VARCHAR(10);

SELECT @AlphaPart = dbo.fGetAlpha(NTVCHNUM)FROM PM40100;

WITH MyVouchers(VCHRNMBR) AS (
SELECT VCHRNMBR FROM PM20000
UNION ALL
SELECT VCHRNMBR FROM PM30200
)
SELECT @MaxVoucherNumber = MAX(dbo.fGetNumber(VCHRNMBR)) + 1
FROM MyVouchers;

UPDATE PM40100 SET NTVCHNUM = @AlphaPart + LEFT(REPLICATE('0', LEN(NTVCHNUM) - LEN(@AlphaPart)), LEN(NTVCHNUM) - LEN(@AlphaPart) - LEN(CONVERT(VARCHAR(20), @MaxVoucherNumber))) + CONVERT(VARCHAR(20), @MaxVoucherNumber);

11 comments:

Jivtesh Singh said...

Mariano,

Great Post!

Something to add - I just tested this in the latest version of Dynamics GP related to GL. What I found was that using the Transaction Entry window I could not cause the Journal Entry Number to skip. It would go back to the number that was deleted.

So If A is working on 10, B on 11, C on 12 and B deleted their JE, whenever one of them enters a new JE - it would pick up 11.

I also noticed that if I add the JE to a batch and delete a batch - it does cause the JE number to be skipped.

Do correct me, if you think I missed something there.

And thanks for the info! It was very useful, like all your posts!

Jivtesh

Anonymous said...

Great information. We have an issue at a client site where voucher numbers in AP are getting reset. We think it due to someone fat fingering in the voucher number field and causing it to not be able to advance.

I looked at using Field Level Security to prevent this but cannot as it is a required field. Do you think VB could be used to basically tab through the field before the user can fat finger it? Any code already out there?

Thanks in advance for your help. Your site is great!

Robyn Evans

Mariano Gomez said...

Robyn,

Thanks for your query and the kind words.

Could you explain a bit more what you mean by "voucher numbers in AP are getting reset"? Reset to what? The is a known issue with alphanumeric fields being reset when they numeric portion reaches the maximum length. For example, if your voucher numeber is VCH99999, you would think for one instance that the following number would be VCH100000. Not the case. GP will reset these to VCH00001 which may have already been used.

MG.-

Kent said...

Mariano,

If our numeric portion has not reached its' max, why would the number get set back to a previous value? This occasionally happens. Is it possible for the user to reset the last number used without Admin rights?

Mariano Gomez said...

Kent,

It is possible for this to happen, especially if the user has access to the setup window where the document number can be changed.

If this becomes a problem I would at least setup Field Level Security to prevent users changing that specific field.

MG.-

Anonymous said...

Hi,

I have experience a similar issue with the Receipt Number in Receivings Transaction Entry. It will sometimes increment or decrement with an average of 100-1000 skipped doc number. I would appreciate if you could provide a script to refrain from this again.

Thank you in advance for your help.

Regards,
Jamie

Anonymous said...

how could we automate this check?
Thanks

B Wise said...

We are having the same issue in the Manufacturing Pick Doc, only we are beginning to see an issue with Allocation and Issue transactions getting the same MPK number.

Is there a fix for this? It is creating a real issue.

Thanks!

Mariano Gomez said...

B Wise,

I believe your best course of action is to open a support case to address this issue, since your problem goes beyond just some document number skipping.

If the issue is also affecting functional aspects of the application, like the allocation process, you are better off addressing this with Microsoft.

MG.-
Mariano Gomez, MVP

Anonymous said...

Hi Mariano

Microsoft have any KB in this

Thanks
Mohammed

Anonymous said...

Hi Mariano,

Do you have a script for next SOP number? We have a customer who is often missing many order numbers (for one Order ID).

Thank you,
Deborah Newcomer