Next numbers (F0002) and how they work

This is a pretty simple post that might be helpful.

You all know that in many situations (not all), JD Edwards get’s it’s next numbers from the next numbers table (with me so far?  I hope so).

The next numbers table is generally in your control library. <rant>I must admit, for the life of me I do not understand WHY this is in the control library.  It should be in the business data library / schema / owner.  This should be fixed “post haste”.  There is no reason that this table should not be classed as data. </rant>

But, let’s get back to the lesson.  The number for address book next numbers comes from F0101, NNN001 (when dealing with default behaviour – you can get fancy with NN by type).  The value in the NN

select NNN001 from prodctl.f0002@jde_p2jde where NNSY = '01';

select max(aban8) from proddta.F0101@jde_p2jde where aban8 < 5000000;

So, we learn from the above that the F0002 contains the next available unique number, you consume then and update it immediately so that the next transaction can select it.  Best to use the BSFN X0002 for grabbing NNs, then you don’t need to worry too much about all of the locking.

    NNN001
----------
   4842748 

MAX(ABAN8)
----------
   4842747 

So you can see from the above, in a normal NN situation, the NN has the NEXT value in it, you do not add 1 to the NN, you use the current value and then when you’ve committed your insert of the new AN8, then update the NN.  I also think that JDE does a “SELECT FOR UPDATE” to ensure that the NN table is locked for the duration of the MST (Multi-statement transaction).  This allows NN’s not to get sprayed all over the shop.

SELECT  *  FROM JDE_CRP.CRPCTL.F0002  (UPDLOCK)  WHERE  ( NNSY = '02' )  ORDER BY NNSY ASC FOR UPDATE OF NNN001, NNN002, NNN003, NNN004, NNN005, NNN006, NNN007, NNN008, NNN009, NNN010 

Then also for SQL

UPDATE JDE_CRP.CRPCTL.F0002  SET NNN001=21573840.000000,NNN002=1.000000,NNN003=0.000000,NNN004=0.000000,NNN005=0.000000,NNN006=0.000000,NNN007=0.000000,NNN008=0.000000,NNN009=0.000000,NNN010=0.000000 WHERE CURRENT OF C4E054F0 

Oracle

SELECT PRODCTL.F0002.*, ROWID FROM PRODCTL.F0002  WHERE  ( NNSY = '04' )  ORDER BY NNSY ASC FOR UPDATE OF NNN001, NNN002, NNN003, NNN004, NNN005, NNN006, NNN007, NNN008, NNN009, NNN010 

UPDATE PRODCTL.F0002  SET NNN001=1469670.000000,NNN002=50.000000,NNN003=5038.000000,NNN004=951130.000000,NNN005=434058.000000,NNN006=4695.000000,NNN007=38391.000000,NNN008=2162.000000,NNN009=10628.000000,NNN010=0.000000 WHERE ROWID = :LASTROWID

So select it, lock it while you’ve selected it and then update it ASAP.  Then use the selected VALUE in all your ongoing transactions.