E9.2 prodctl.f0002 Next Numbers

[Patrickjk]

JDE 9.2 32 bit
Oracle Database = 19.c

Regarding the next number table F0002 for the System Code 00 (Foundation)...I run the following query to see how many batch numbers have been used up for each of the batch types for index 01 (NNN001).

select icicut, count(*) from proddta.f0011 group by icicut

Does anyone have/know the query to determine how many System Code 00 (Foundation) index 04 (Job Number) have been used up, which would be table F986110 - Job Control Status Master?

NOTE: It's getting close where F0002.NNN001 and F0002.NNN004 need to be reset back to 0. Both are getting close to the max value of 99,999,999. I'm especially interested in how many F986110 records are still available.

Any input would be greatly appreciated.

Thank you.

Regards,

Patrick

 

[eydeak]

We do a high enough volume of EDI transactions that the EDOC NN rolls every couple of years. This is the kind of SQL I use to check the gap between the current next number and the next highest EDOC value in the table. It takes into account if there isn't a higher value in the table and wraps back to the beginning to include the gap at the beginning of the table.

Select NNGroup, Table, MinNbr, PrevNbr, CurrentNN, NextNbr, MaxNbr, RecCnt,
(case when CurrentNN > MaxNbr then (99999999 - CurrentNN + MinNbr -1) else (NextNbr - CurrentNN) end) as Gap
from(
select '47-1' as NNGroup, 'F47046' as Table, nnN001 as CurrentNN,
(select count(*) from PRODdta.F47046) as RecCnt,
(select min(SYEDOC) from PRODdta.F47046 where SYEDOC>0) as MinNbr,
(select max(SYEDOC) from PRODdta.F47046) as MaxNbr,
(select max(SYEDOC) from PRODdta.F47046 where SYEDOC <= (select nnN001 from PRODctl.F0002 where nnSY = '47')) as PrevNbr,
(select min(SYEDOC) from PRODdta.F47046 where SYEDOC > (select nnN001 from PRODctl.F0002 where nnSY = '47')) as NextNbr
from PRODctl.F0002
where nnSY = '47'
) as x;

Maybe you can adapt this to fit your needs.