Every Dynamics GP next document number in every module is generally gathered from a setup table. Usually, this table column is called Next
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);
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;
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);
