Understanding Lookups in Oracle Apps R12

An essential guide to one of the most fundamental concepts in Oracle E-Business Suite.

What is a Lookup?

In Oracle E-Business Suite, a "lookup" is a static list of values. It's a simple and powerful tool used to create predefined choices for a field, ensuring data consistency and making the application easier to configure. Think of it as a dropdown list that can be managed without any coding.

The Power of Flexibility: A Simple Example

The main purpose of a lookup is to keep programs flexible. One of the simplest examples is a 'Gender' lookup. On the screen, a user might see 'Male', 'Female', or 'Unknown'. However, in the database, these are stored as short codes: 'M', 'F', and 'U'.

Database Value (Code)

GENDER
M
F
U

Screen Display (Meaning)

Gender
Male
Female
Unknown

Now, imagine your organization decides to change the display text from "Unknown" to "Undisclosed". Instead of updating thousands of employee records in the database, you only need to make a single change in the lookup definition. The database codes remain the same, but the text displayed to all users updates instantly. This is the power of lookups.

Types of Lookups in Oracle Apps R12

Oracle provides three different types of lookups, each with a different level of restriction to protect core application data while still providing flexibility.

User

Fully flexible. Ideal for custom lists specific to your business.

Update start/end date & enabled flag
Delete codes
Update tag
Insert new codes

Extensible

Partially restricted. Extend Oracle's base lists with your own codes.

Insert new codes
Update/delete codes (if not seeded)
Update module
Delete lookup type

System

Highly restricted. Used in core application logic.

Insert new codes
Update/delete codes
Update module
Delete lookup type

How to Use Lookups for Validation

While you cannot directly attach a lookup to a flexfield segment for validation, you can do it indirectly. The standard method is to create a Table-validated Value Set that queries the FND_LOOKUP_VALUES table for your specific lookup type. This allows your flexfield to display the lookup meanings as a list of values while storing the lookup code.

Use Case: Interfacing Data to a Third-Party System

Lookups are incredibly useful for making code more flexible. Imagine you need to create a database view to send employee data to another system, but you only want to extract active 'Employees' and 'Applicants'. Instead of hard-coding these values in your view's WHERE clause:

WHERE person_type IN ('Employee', 'Applicant') -- This is bad practice!

You can use a lookup:

Create a custom lookup type, e.g., XX_EXTRACT_EMPLOYEE_TYPES.
Add the codes 'EMPLOYEE' and 'APPLICANT' to this lookup.
Join your query with the lookup table, as shown in the SQL below.

Now, if you need to add 'Contractor' to the extract, you simply add a new code to the lookup without changing any code. This makes maintenance much easier.

SQL Query

CREATE OR REPLACE VIEW xx_extract_employees_v AS
SELECT papf.employee_number,
       papf.full_name
FROM   per_all_people_f papf,
       per_person_types ppt,
       fnd_lookup_values flv
WHERE  papf.person_type_id = ppt.person_type_id
AND    ppt.system_person_type = flv.lookup_code
AND    flv.lookup_type = 'XX_EXTRACT_EMPLOYEE_TYPES'
AND    TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;