Fusion Concepts: Lookups
By Vik Kumar-Oracle on Dec 09, 2013
Lookups in applications are used to represent a set of codes and their translated meanings. For example, a product team might store the values 'Y' and 'N' in a column in a table, but when displaying those values they would want to display "Yes" or "No" (or their translated equivalents) instead. Each set of related codes is identified as a lookup type.
Lookups are codes that are defined in the global FND_LOOKUP_VALUES table, which is striped into multiple virtual tables using a VIEW_APPLICATION_ID column. Every product team who owns the lookups specific to their product provides a database view and ADFbc artifacts built on the top of the database view.
FND_LOOKUP_TYPE table has a child table FND_LOOKUP_VALUES which is translation enabled. As a result, FND_LOOKUP_VALUES_VL view is created combining FND_LOOKUP_VALUES_B and FND_LOOKUP_VALUES_TL. If product specific lookups are not required then common FND Lookups for lookups like Yes, No etc. are used.
FND Lookups are applcore specific lookups with reserved application_id = 0. All these applcore seeded lookups are in FND_Lookups table striped by the reserved application_id. FND lookups are also used for common global lookups as mentioned above. FND Lookups are based on database view FND_LOOKUPS_VALUES_VL and ADFbc artifacts are exposed as following:
Entity Object: FndLookupPEO (package oracle.apps.fnd.applcore.lookups.model.publicEntity)
View Object: FndLookupPVO (oracle.apps.fnd.applcore.lookups.model.publicView)
Lookups can be imported to any application by including Applications Core library in the model project into the JDeveloper workspace.
LOOKUP_TYPE VIEW_APPLICATION_ID CUSTOMIZATION_LEVEL =========================================================
ABSENCE_STATUS 0 U
where lookup_type is lookup type, application_id is the stripe id, and customization_level defines the availability and access level.
LOOKUP_TYPE LOOKUP_CODE MEANING ENABLED_FLAG =======================================================================
ABSENCE_STATUS CONFIRMED Confirmed Y
ABSENCE_STATUS PLANNED Planned Y
Where Lookup_type is the lookup type, lookup code is the unique code in the same lookup type, meaning is the label that appears in UI and enabled_flag indicates if the lookup value row is available or not.
LOOKUPS Customization Levels
LOOKUPS can be customized as per customer requirements. The customization levels governs how and by whom a particular lookup can be edited. These are stored in the FND_LOOKUP_TYPES table's customization_level column. There are 3 possible values as follows:
U - User: Following are the possibilities with these lookups:
- Insertion of new codes is allowed
- Update of start date, end date and enabled flag is allowed
- Deletion of codes is allowed
E - Extensible: Following are the possibilities with these lookups:
- Insertion of new codes is allowed
- Deletion of lookup type is not allowed
- Update of start date, end date and enabled flag is allowed only if it is not a seed data Deletion of lookup code is allowed if it is not a seed data
S - System: Following are the possibilities with these lookups:
- Deletion of lookup type as well as codes is not allowed
- Insertion of new lookup codes is not allowed
- Updating start date, end date and enabled flag is not allowed
So, system type customization level is most restrictive.
Using Lookups in Fusion Applications
Whether its product specific lookups or FND Lookups the idea behind using them in Fusion Applications remains the same. Lookups are exposed as LOV (List of Values) in the UI. And to do that one of VO attribute is set to be LOV enabled. Once an attribute is LOV enabled it requires a source to be specified. This is the place where a LOOKUP VO instance based accessor is created as LOV source.
More details on Lookups, its implementation and consuming into Fusion Applications can be found in the Fusion Applications Developer guide.