This document is to go through the general next numbering routine with the API JDB_SelectKeyedForUpdate ().
Possibly, what is correct behavior of following setup?
- Next Number Constant: Implement Next Numbers by Company/Fiscal Year. If a company record is not set up for a document type, the standard Next Numbers setup will be used. (Method 2)
- Data from F00021
Document Company Doc Type Century Fiscal Year Next Number
00020 PV 20 13 123
00020 PV 20 16 456
Next number will be 123 if you are on DB2 and 456 in other database.
The reason for this answer is that the API JDB_SelectKeyedForUpdate () in X0010 does not append ORDER BY clause in DB2.
Not to have invalid Next Numbering,
Option 1> Remove old information,
Document Company Doc Type Century Fiscal Year Next Number
00020 PV 20 13 123 <--- Delete this row
00020 PV 20 16 456 <--- So your next number will be running number starts from Fiscal year 16
Option 2> Define the value for current fiscal year
Document Company Doc Type Century Fiscal Year Next Number
00020 PV 20 13 123 <---Historical data
00020 PV 20 16 456 <--- Historical data
00020 PV 20 18 1 <--- This number will be returned.
There is existing/closed without any resolution. Refer to <Bug 26038032> - JDB_SELECTKEYEDFORUPDATE NOT APPENDING ORDER BY W/JDB_SETSEQUENCING IN AS400
Note that only limited business functions make use of the API JDB_SelectKeyedForUpdate () and the issue is specific to AS400 environment.
The function X0010 calls API in below sequence,
1. Check exact matching key with JDB_FetchKeyed (), if fails then
2. JDB_SetSequencing to set Descending order for the column F00021.FY which replaces the primary key of F00021
3. JDB_SelectKeyedForUpdate ()
- AS400
sql> SELECT * FROM CRPCTL/F00021 WHERE ( NLKCO = '00021' AND NLDCT = 'PV'
AND NLCTRY <= 20.000000 ) FOR UPDATE OF NLN001
: So it returns the Next Number for smallest Fiscal Year
- Other platform
sql> SELECT PRODCTL.F00021.*, ROWID FROM PRODCTL.F00021 WHERE ( NLKCO =
'00200' AND NLDCT = 'PV' AND NLCTRY <= 20.000000 ) ORDER BY NLKCO ASC,NLDCT ASC,NLCTRY DESC,NLFY DESC FOR UPDATE OF NLN001
: This returns the next number for the biggest Fiscal Year because it appends ORDER BY clause correctly.
----------------- Log in AS400 -------------
Calling Business function X0010GetNextNumber from F0411FSGetNextDocNo for 90100215. Application Name [P0411], Version [M000T002] (BSFNLevel = 2)
IN->[ 1] szSystemCode [04]
IN->[ 2] mnNextNumberingIndexNo [1]
IN->[ 3] szCompanyKey [00021]
IN->[ 4] szDocumentType [PV]
IN->[ 5] mnCentury [20]
IN->[ 6] mnFiscalYear1 [17]
IN->[ 7] szSameAsDocumentType [ ]
IN->[ 8] mnNextNumber001 [0]
Entering JDB_InitUser with (AUTO commit mode)
Entering JDB_BeginTransaction (UserHandle 2B07C540)
Entering JDB_OpenTable(Table = F0002)
Entering JDB_OpenTable(Table = F00021)
Entering JDB_SetNestedSelection (hRequest 2B341CA8)
Entering JDB_FetchKeyed (hRequest 2B341CA8)
SELECT * FROM CRPCTL/F00021 WHERE ( NLKCO = '00021' AND NLDCT = 'PV' AND NLCTRY = 20.000000 AND NLFY = 17.000000 ) ORDER BY NLKCO ASC,NLDCT ASC,NLCTRY ASC,NLFY ASC
No More Data found (hRequest 2B341CA8)
Entering JDB_SetSequencing (hRequest 2B341CA8)
Entering JDB_SelectKeyedForUpdate (hRequest 2B341CA8)
SELECT * FROM CRPCTL/F00021 WHERE ( NLKCO = '00021' AND NLDCT = 'PV' AND NLCTRY <= 20.000000 ) FOR UPDATE OF NLN001
Entering JDB_FetchForUpdate (hRequest 2B341CA8)
Fetched the record (hRequest 2B341CA8)
Entering JDB_UpdateCurrent (Table F00021) (hRequest 2B341CA8)
UPDATE CRPCTL/F00021 SET NLN001=21082463.000000 WHERE CURRENT OF C4575998
OUT->[ 1] szSystemCode [04]
OUT->[ 2] mnNextNumberingIndexNo [1]
OUT->[ 3] szCompanyKey [00021]
OUT->[ 4] szDocumentType [PV]
OUT->[ 5] mnCentury [20]
OUT->[ 6] mnFiscalYear1 [17]
OUT->[ 7] szSameAsDocumentType [ ]
OUT->[ 8] mnNextNumber001 [21082462]
Return value is 0 for X0010GetNextNumber. (BSFNLevel = 2)
------------------ Log in Oracle Database -------------
Calling Business function X0010GetNextNumber from F0411FSGetNextDocNo for SDONGMYE. Application Name [P0411], Version [ZJDE0001] (BSFNLevel = 2)
IN->[ 1] szSystemCode [04]
IN->[ 2] mnNextNumberingIndexNo [1]
IN->[ 3] szCompanyKey [00200]
IN->[ 4] szDocumentType [PV]
IN->[ 5] mnCentury [20]
IN->[ 6] mnFiscalYear1 [17]
IN->[ 7] szSameAsDocumentType [ ]
IN->[ 8] mnNextNumber001 [0]
Entering JDB_OpenTable(Table = F0002)
Entering JDB_OpenTable(Table = F00021)
Entering JDB_FetchKeyed (hRequest 0E961C60)
SELECT * FROM PRODCTL.F00021 WHERE ( NLKCO = '00200' AND NLDCT = 'PV' AND NLCTRY = 20.000000 AND NLFY = 17.000000 ) ORDER BY NLKCO ASC,NLDCT ASC,NLCTRY ASC,NLFY ASC
No More Data found (hRequest 0E961C60)
Entering JDB_SetNestedSelection (hRequest 0E961C60)
Entering JDB_SetSequencing (hRequest 0E961C60)
Entering JDB_SelectKeyedForUpdate (hRequest 0E961C60)
SELECT PRODCTL.F00021.*, ROWID FROM PRODCTL.F00021 WHERE ( NLKCO = '00200' AND NLDCT = 'PV' AND NLCTRY <= 20.000000 ) ORDER BY NLKCO ASC,NLDCT ASC,NLCTRY DESC,NLFY DESC FOR UPDATE OF NLN001
Entering JDB_FetchForUpdate (hRequest 0E961C60)
Fetched the record (hRequest 0E961C60)
Entering JDB_UpdateCurrent (Table F00021) (hRequest 0E961C60)
UPDATE PRODCTL.F00021 SET NLN001=987656.000000 WHERE ROWID = :LASTROWID
OUT->[ 1] szSystemCode [04]
OUT->[ 2] mnNextNumberingIndexNo [1]
OUT->[ 3] szCompanyKey [00200]
OUT->[ 4] szDocumentType [PV]
OUT->[ 5] mnCentury [20]
OUT->[ 6] mnFiscalYear1 [17]
OUT->[ 7] szSameAsDocumentType [ ]
OUT->[ 8] mnNextNumber001 [987655]
Return value is 0 for X0010GetNextNumber. (BSFNLevel = 2