Lookups in Oracle Apps R12

LookUps In Oracle Apps

Lookups in Oracle are a collection of values. Lookups are a static collection of codes which are used by oracle for working.
Each lookup when defined will have a lookup code and a corresponding meaning for that code. Oracle will internally use codes for working and use meaning when communicating to the user.The meaning of the lookup code is displayed on the screen and the lookup code is stored in the database.
It can be easily understood by following examples:
Example 1: On a form, we have a field called 'Gender'. This fields possible values can be 'Male' and 'Female'. We can create a lookup of type 'Sex' and store 'Male' and 'Female' as the lookup values. In the runtime, we can retrieve these values and show it as the list of values on the field.

Example 2: A bank provides facilities of Saving Account, Current Account and Fixed Deposit Account. We can define a lookup type called 'Account Type' and associate these account type values with this look type.
When ever a lookup is created and entry will be created in the table FND_LOOKUP_TYPES,
Lookup Information:
select *
  from fnd_lookup_types_VL
 Where lookup_type = '&Enter_lookup_type';
=> Note:You can use the above table to find lookups available in the system

For each lookup type when the value is entered for the lookup entries will be created in the table FND_LOOKUP_VALUES.
Values of a lookup:
select *
  from fnd_lookup_values
 Where lookup_type = '&Enter_lookup_type';

Note:For the lookups already defined in Oracle each is associated with an application , thus when searching for application specific lookups user should search with the specific application.

Lookup type can be defined at three levels:

System: These are lookups which are used internally by the system. Users can see and use the lookup but they cannot disable the existing values or add new values.
Extensible : These are system provided lookups. Users can add their values if they want , but Seeded values by Oracle cannot be changed
User Defined : User Defined lookups are client specific which are created to meet the business specific customization s. These are created by the users and the users can add new values and disable the older ones.

Lookup APIs:
FND_LOOKUP_TYPES_PKG.INSERT_ROW for inserting new lookup type,
FND_LOOKUP_VALUES_PKG.INSERT_ROW for inserting new lookup value.

Oracle Applications package to create and insert (FND) LOOKUP Types & Values
Below is a sample script to create lookup types in Oracle and also to load lookup values for the corresponding lookup types using standard Oracle APIs. I have used the FND Application ID to create lookups and customization level is user. There are many other ways to load data into lookups, we can also use FNDLOAD which is a very good option to migrate data lookups from one instance to another.

DECLARE
   ln_rowid    VARCHAR2 (1000);
   ln_rowid1   VARCHAR2 (1000);
BEGIN
   fnd_lookup_types_pkg.insert_row (x_rowid                    => ln_rowid,
                                    x_lookup_type              => 'XXTEST',
                                    x_security_group_id        => 0,
                                    x_view_application_id      => 0,
                                    x_application_id           => 0,
                                    x_customization_level      => 'U',
                                    x_meaning                  => 'XXTEST',
                                    x_description              => 'XXTEST',
                                    x_creation_date            => SYSDATE,
                                    x_created_by               => 0,
                                    x_last_update_date         => SYSDATE,
                                    x_last_updated_by          => 0,
                                    x_last_update_login        => -1
                                   );
   DBMS_OUTPUT.put_line (ln_rowid);
   fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,
                                     x_lookup_type              => 'XXTEST',
                                     x_security_group_id        => 0,
                                     x_view_application_id      => 0,
                                     x_lookup_code              => 'XXHR_INFO',
                                     x_tag                      => NULL,
                                     x_attribute_category       => NULL,
                                     x_attribute1               => NULL,
                                     x_attribute2               => NULL,
                                     x_attribute3               => NULL,
                                     x_attribute4               => NULL,
                                     x_enabled_flag             => 'Y',
                                     x_start_date_active        => TO_DATE ('01-JAN-1950',
                                                                            'DD-MON-YYYY'
                                                                           ),
                                     x_end_date_active          => NULL,
                                     x_territory_code           => NULL,
                                     x_attribute5               => NULL,
                                     x_attribute6               => NULL,
                                     x_attribute7               => NULL,
                                     x_attribute8               => NULL,
                                     x_attribute9               => NULL,
                                     x_attribute10              => NULL,
                                     x_attribute11              => NULL,
                                     x_attribute12              => NULL,
                                     x_attribute13              => NULL,
                                     x_attribute14              => NULL,
                                     x_attribute15              => NULL,
                                     x_meaning                  => 'XXHR Information',
                                     x_description              => NULL,
                                     x_creation_date            => SYSDATE,
                                     x_created_by               => 0,
                                     x_last_update_date         => SYSDATE,
                                     x_last_updated_by          => 0,
                                     x_last_update_login        => -1
                                    );
   DBMS_OUTPUT.put_line (ln_rowid1);
   COMMIT;
END;