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);