JDE/API X00022 BSFN UKID Contention

Patrickjk

Active Member
Hello Everyone,

Info: JDE 9.2 and BizTalk for the API.

Question: Is there a way to prevent the selection of the same UKID from table F00022 (JDE BSFN X00022 vs. API PL/SQL stored procedure selecting/updating F00022) between JDE and an API
running simultaneously where both insert records into the same table?

Issue:
- Table F46L99 - License Plate History - The UKID is the primary key.
- Both JDE and the API will be updating the status in the table (F46L99.LMLPSC) which requires a new record to be inserted into the table.
- JDE calls BSFN X00022 for the table to get the next UKID.
- For efficiency, the API won't be calling BSFN X00022. It will be utilizing an Oracle PL/SQL stored procedure. I thought I could select UKID + 1 from table proddta.F00022 to get the next UKID
and then update the table with the applicable value. However, that leaves the possibility that JDE and the PL/SQL stored procedure could select the same UKID since they will be running simultaneously.

Possible Solution:
- The only way I see around the "duplicate key" potential issue is to create a database sequence and have the beginning value begin at an extremely high number, e.g. 500,000,000,000,000
which would/should eliminate the possibility of duplicate UKIDs.

Concerns with possible solution:
- The records from the table couldn’t be sorted just by the Unique Key Id to get the correct order of creation for the records. The sort order would have to be Date descending, Time descending, and Unique Key ID descending.
- Would the large range between the JDE and the stored procedure UKIDs cause any problems with the JDE screen that displays the historical records?

All thoughts and suggestions are appreciated.

Thanks.

Patrick
 
Since X00022 uses JDB_FetchKeyedForUpdate and JDB_UpdateCurrent to lock the record, I would assume you could do the same from the PL/SQL (SELECT FOR UPDATE). I don't have practical experience, just a theory.

Craig
 
Back
Top