Fusion Concepts: Unique ID Generation

All the tables in the Fusion Applications requires a primary key. Using DB sequences is a common practice to generate the primary keys. In EBS every table had a dedicated sequence to generate primary keys. This approach worked fine however, it required huge number of DB sequences  and a lot of them under utilized depending upon the usage of the table in the application. Fusion Applications improved upon it by using a global DB sequence to generate primary keys. Fusion Applications requires the data type of the primary key attribute in the Entity Object to be of type Long. The underlying DB table must have the primary key column of type NUMBER of size 18. ApplCore provides a declarative way to handle unique key generation for entity objects and thus, saving a lot of time and resources in maintaining DB sequences that used to be required in the EBS World. To generate globally unique IDs declaratively, all that is needed is to set the "Application Unique Id" property to true  for EO attribute as shown in below screenshot.

As a result of setting this property to true, the ADF framework will populate this attribute with a unique id everytime a row creation happens via ADFbc. This is accomplished by using the below transient expression in the EO attribute:


Behind the Scenes

The unique ID generator feature in Fusion Applications assigns a block of IDs to the middle-tier cache and shared across all the objects, these are not gapless. Every time a new row creation happens the ID is picked from the middle-tier cache. This saves a database trip on every row creation as ID is picked from the cache instead of executing a query to the database. A database trip is made only when this cache is out of IDs. Table S_ROW_ID is installed during FA installation and is seeded with the entire key range available. Middle-tier code like entity creation directly refers to the table S_ROW_ID  for keys by extracting in the block of 1000 keys at a time and populated to middle-tier cache. For the PL/SQL code usage a DB sequence named  S_ROW_ID_SEQ  is used to provide unique IDs. Using a single DB sequence to generate all the unique IDs does not cause any danger of running out of IDs as the size of data type is defined as 18 (can generate 10^18 unique values) . To avoid collision range up to 10 raise to 14 is reserved for database sequences used by PL/SQL and C code. Fusion Middleware Unique ID generator feature uses the range above 10^14.

I am able to see the S_ROW_ID table in FA schema.
I want to use these sequence nextval from my pl_sql code. But I am not able to find the S_ROW_ID_SEQ in FA schema.

Posted by guest on November 12, 2015 at 03:56 AM PST #

Tested this in my Rel 10 env. This works for me when connected to the Fusion schema.

select S_ROW_ID_SEQ.nextval from dual;

and the owner of the S_ROW_ID_SEQ sequence is FUSION::

select owner from all_objects
where object_name = 'S_ROW_ID_SEQ';

Posted by Oliver Steinmeier on November 12, 2015 at 11:03 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow us on twitter Fusion Applications Extensibility, Customizations and Integration forum Fusion Applications Dev Relations YouTube Channel
This blog offers news, tips and information for developers building extensions, customizations and integrations for Oracle Fusion Applications.


« November 2015