Lookups in Oracle Apps R12

In Oracle Apps R12, the Lookups is one of the fundamental configuration objects. It is the predefined list of values (LOVs) that are displayed to users while transacting or configuring the application. For example, while creating a customer, you might be requested to enter a Customer Category from the predefined list like Retail, Wholesale, Distributor.

Lookups enable consistency, validation, and controlled variability across Oracle Applications. Functional and technical consultants usually come across them in setups, form personalizations, or custom extensions.

What Are Lookups in Oracle Apps R12?

An Oracle Apps lookups is a static list of codes and code meanings that are used to validate user inputs and to standardize system-wide values. Oracle lookups are like mapping tables with lookup types will have some specific code with their meaning, description and effective dates.

Each lookups has below fields:

• Lookup Type: Category name (e.g., CUSTOMER_CATEGORY)
• Lookup Code: Internal code (e.g., RETAIL)
• Meaning: The clear description displayed in the LOV (e.g., Retail Customer)
• Description: Optional notes or details
• Enabled Flag: To enable/disable the code
• Effective Dates: To control the validity period

Types of Lookups in Oracle Apps R12

Oracle specifies various lookup categories. Below are the types of lookups in Oracle Apps R12.

System Lookups

• Delivered by Oracle
• Cannot be edited or deleted
• Example: YES_NO lookup (values: YES, NO)

Extensible Seeded Lookups

• Delivered by Oracle but can be extended
• You can add codes but cannot delete Oracle’s defaults
• Example: CUSTOMER_CATEGORY

User Lookups (Custom Lookups)

• Created by users for specific business needs
• Fully managed by the functional consultant or system administrator
• Example: CAMERA_PRODUCT_TYPE with values DSLR, Mirrorless, Printer

Lookups vs. Value Sets

While both lookups and value sets are used to provide lists of values, they have distinct differences.

Where Are Lookups Used?

Lookups appear across Oracle Apps modules. Below are some of the Oracle Apps Lookups used accross Oracle EBS.

• Order Management: Order types, reasons, line statuses
• Receivables: Customer categories, statement cycles
• Payables: Invoice types, payment methods
• HRMS: Gender, marital status, employee types
• Custom Extensions: LOVs in custom forms and reports

Step-by-Step: How to Create Lookups in Oracle Apps R12

Step 1: Responsibility Navigation

• Login to Oracle Apps Application
• Navigate: Application Devceloper → Application → Lookups → Common

Step 2: Enter Lookup Type

• Enter a unique Lookup Type (e.g., PRODUCT_CATEGORY)
• Provide a Meaning (e.g., Product Category)
• Choose an Application (where it will be used)
• Description (User Description)
• Access Level (User)

Step3: Define Lookup Codes
For each code, provide:

• Code (e.g., CAMERA, PRINTER)
• Meaning (e.g., Camera, Printer)
• Description (optional)
• Tag (Keeps any extra value for identification)
• Enabled Flag
• Start/End Date

Step 4: Save
Your lookup is now available for use in forms, personalization, or extensions.

Example: Lookup for Product Types

Suppose you run a camera and printer business and want to classify items:

• Lookup Type: PRODUCT_CATEGORY
• Lookup Codes & Meanings:
  • CAMERA → Camera
  • PRINTER → Printer
  • ACCESSORY → Accessory

When creating inventory items, this lookup ensures consistent categorization.

Steps to add a DFF to lookups

The FND_LOOKUP_VALUES table has a set of ATTRIBUTE columns (e.g., attribute1 to attribute15) and a context column. Oracle has pre-configured a DFF on this table. By setting up this DFF, you can map the attribute columns to custom segments and use the context column to make the DFF segments context-sensitive based on the lookup type.

1. Define the DFF for FND_LOOKUP_VALUES

• Navigate to the DFF Register form using Application Developer responsibility and go to Flexfield -> Descriptive ->Register.

• Query the DFF: Query for “Table Name” FND_LOOKUP_VALUES table and get the dff title “Common Lookups”

• Navigate to the DFF Segments(Flexfield -> Descriptive -> Segments) and query for DFF title “Common Lookups”

• Unfreeze the Flexfield: Ensure the “Freeze Flexfield Definition” checkbox is unchecked to make changes.

• Add Contexts: Create a new context for each lookup type. The context code must be the exact name of the lookup type (e.g., PRODUCT_CATEGORY).

• Add Segments: Under the appropriate context (either the new one you created or the “Global Data Elements” for a global DFF), add your new segments.

• Give each segment a Name and a Window Prompt

• Map the segment to an available ATTRIBUTE column (e.g., ATTRIBUTE1, ATTRIBUTE2).

• You can attach a Value Set to validate the data entered by the user.

• Freeze and Compile: Once you defined the segments, check the “Freeze Flexfield Definition” box and click the Compile button. This registers your changes in the database and generates the necessary form files.

2. Populate the DFF Segments for your Lookups

After you compile the DFF, the new fields will be available on the Lookups form.

• Navigate to the Lookups form: Go to Application Devceloper → Application → Lookups → Common.

• Query your Lookup Type: Find the lookup type you want to add DFF values to (e.g., PRODUCT_CATEGORY).

• Enter DFF Values: For each lookup code, you can now enter values in the new DFF segments you created. You need to press the “[ ]” in each lookup code and DFF segments will be opened

• Save your work. You store the new DFF values in the ATTRIBUTE columns of the FND_LOOKUP_VALUES table.

SQL Queries for Lookups in Oracle Apps R12

In this ssection you can learn how to use SQL to get lookups, lookups values.

1. To View All Lookups Types

SELECT lookup_type, meaning, description, application_id
  FROM fnd_lookup_types_vl
ORDER BY lookup_type

SELECT lookup_type, meaning, description, application_id
  FROM fnd_lookup_types_vl
ORDER BY lookup_type

2. To View Lookups Codes for a Specific Type in Oracle Apps

SELECT lookup_type, lookup_code, meaning, description,                    enabled_flag, start_date_active, end_date_active
FROM fnd_lookup_values_vl
WHERE lookup_type = 'PRODUCT_CATEGORY'
ORDER BY lookup_code;

SELECT lookup_type, lookup_code, meaning, description,                    enabled_flag, start_date_active, end_date_active
FROM fnd_lookup_values_vl
WHERE lookup_type = 'PRODUCT_CATEGORY'
ORDER BY lookup_code;

3. To Search a Specific Lookups Code in Oracle Apps

SELECT lookup_code, meaning, description
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CUSTOMER_CATEGORY'
  AND lookup_code = 'RETAIL';

SELECT lookup_code, meaning, description
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CUSTOMER_CATEGORY'
  AND lookup_code = 'RETAIL';

Best Practices for Managing Lookups

✅ Use consistent naming conventions
✅ Do not modify system lookups—extend them
✅ Apply effective dates for validity control
✅ Document custom lookups for support and audit purposes
✅ Test changes before deploying to production

Real-World Use Case

A sports academy management system built on Oracle Apps R12 tracks Membership Types:

• Lookup Type: MEMBERSHIP_TYPE
• Lookup Codes: BASIC, PREMIUM, VIP
• Meanings: Basic Membership, Premium Membership, VIP Membership

Whenever a new member is registered, the user selects a membership type. This ensures consistency in reporting, billing, and member management.

Conclusion

Lookups in Oracle Apps R12 are simple yet powerful tools for ensuring data accuracy, flexibility, and standardization. Whether you’re a functional consultant or a technical developer, mastering lookups is key to effective Oracle EBS management.

Properly managing lookups helps reduce errors, enforce business rules, and improve data quality.

👉 Next Step: Learn about Value Sets in Oracle EBS, which build on lookups with advanced validation and dynamic list capabilities.