LookUps

Lookup is an important concept in Oracle Applications, used for providing list of values for validation and selection. It can be easily understood by following examples:

Example 1: On a form, we have a field called 'Gender'. This field's 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.

Advantage and Uses of Lookups:

Oracle Applications comes with seeded lookup types. When implementing, we can add more values to it thus extending the Oracle Application functionality without making any code changes. In the above example of bank Account type lookup, we can associate more values with the lookup type 'Account Type' when bank decides to provide additional account types.

Lookup values contain a lookup code and meaning. The meaning of the lookup code is displayed on the screen and the lookup code is stored in the database. When it is desired to change the meaning for better understanding of the user, it can be changed in the lookup type setup and the changed meaning will be displayed in all the screens wherever the lookup is being used.

Lookup type can be defined at three levels:

• System: Existing lookup values cannot be modified and new values cannot be inserted. These kind of lookup types are useful when application logic and functionality depends on the existence of pre-defined lookup values.

• Extensible: Existing lookup values cannot be modified. However, new lookup values can be inserted. This kind of lookup provides flexibility in extending Oracle Applications' functionality.

• User: Existing lookup values can be modified and new values can be inserted. This is the most flexible lookup type.

Oracle Application comes pre-seeded with all the above kind of lookups. It is also possible to define new lookup type by the implementation team for specific extension purposes.

Lookup Components

Lookup consists of lookup type and Lookup values:

Lookup Type: This is header entity and contains name of lookup type and the Application. It also has information on the level of lookup type (System, Extensible or User).

Lookup Value: This is detail entity and child of Lookup Type entity. For a given lookup type, we can have many lookup values.

Lookup type is also associated with a View Application. Based on that, we can divide lookup types in three categories:

• Application Object Library Lookups: The lookup type where view application is 'FND' (or the view application id is 0 - which is the application ID of FND application). These lookups are accessible from Application Developer or System Administrator Responsibilities.

• Common Lookup: The lookup type where the view application is 'AU' (or the view application ID is 3 - which is the application ID of AU application). These lookups are accessible for maintenance from Application Developer responsibilities.

• Quick Code: The lookup type where the View application id is same as Application ID. Some modules like AR, AP etc. make use of Quick codes to enable the lookup value modification through the respective applications. For example, a Quick code called 'SOURCE' is used to defined AP interface source and is available only through AP responsibilities.

Lookup Screen

Navigation: Application Developer => Application => Lookups => Application Object Library

Technical Details on Lookups
Lookup consists of lookup type and Lookup values:

Lookup Type: FND_LOOKUP_TYPES, FND_LOOKUP_TYPES_TL

Lookup Values: FND_LOOKUP_VALUES

Important fields of these tables are explained below:

FND_LOOKUP_TYPES (Lookup Type Base Table)

Application_ID Application associated with the lookup type

Lookup_Type Lookup type code

Customization_Level Customization Level. Value can be E, U or S.

View_Application_ID View Application. It is automatically derived based on which screen you choose to define lookup type.

FND_LOOKUP_TYPES_TL (Lookup type Translated Table)

Lookup_Type Primary key. Same as the primary key in Base table

Language Language

Meaning Meaning in the corresponding Language

Description Description in the corresponding Language

A View FND_LOOKUP_TYPES_VL is available which is a join between FND_LOOKUP_TYPES and FND_LOOKUP_TYPES_TL tables. This view will give the records for the current language.

FND_LOOKUP_VALUES (Lookup Value table)

Lookup_Type Foreign key from FND_LOOKUP_TYPES

Language Language

Lookup_Code Lookup Code.

Meaning Meaning of Lookup Code

Description Description of Lookup Code

Start_date_Active Start date of the lookup Code

End_Date_Active End date of the Lookup Code

A view FND_LOOKUP_VALUES_VL is available which gives records from FND_LOOKUP_VALUES for the current language.

Examples

In order to select all the values for a given lookup type (Say, a lookup type having a meaning of 'Approval Priority Type'), we can use the following query:

SELECT lookup_code

FROM fnd_lookup_values_vl val, fnd_lookup_types_vl type

WHERE type.lookup_type = val.lookup_type

AND type.meaning = 'Approval Priority Type'

The above query gives the following results:

LOOKUP_CODE

------------------------------

STANDARD

URGENT

Lookup APIs
 New Lookup types and values can be created through the Screen (Using System Administrator or Application Developer responsibilities). However, sometime we need to create large number of values for a given lookup type. In that case, it is better to use Oracle's standard API, which can read from a flat file and create the lookups programmatically.  

The standard Oracle APIs that can be used for creation lookups programmatically are given below:

FND_LOOKUP_TYPES_PKG.INSERT_ROW for inserting new lookup type

FND_LOOKUP_VALUES_PKG.INSERT_ROW for inserting new lookup value

Lookups created in one instance of Oracle Applications may need migration to another instance. For example, initially we would create a new lookup type or lookup values in Development instance. These values are needed to be migrated to another instance, say test instance.

We can make use of Oracle's standard FNDLOAD tool to achieve the migration. The tool creates a data file (called .LDT file) from the source instance. This data file is then uploaded into the target instance. The usage of this tool is given below.

Suppose we have a lookup type called 'OKL_AE_LINE_TYPE'. We want to download it from development instance in a file. We can use the following command in UNIX:

$FND_TOP/bin/FNDLOAD apps/<password>@DBNAME 0 Y DOWNLOAD$FND_TOP/patch/115/import/aflvmlu.lct xxx_line_type.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='OKL' LOOKUP_TYPE="OKL_AE_LINE_TYPE"

The above statement invokes tool FNDLOAD using the standard lct file called 'aflvmlu.lct'. The data of the lookup 'OKL_AE_LINE_TYPE' is downloaded in the flat file 'xxx_line_type.ldt'. This lookup then can be uploaded into another instance using the following command:

$FND_TOP/bin/FNDLOAD apps/<password>@DBNAME 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct xxx_line_type.ldt

For more information on FNDLOAD, please go to Chapter on FNDLOAD from the main index.