[Fusion] How to Query Lookup Types and Values in Oracle Fusion Cloud?

 

If you've spent any time in Oracle Fusion Setup and Maintenance, you're familiar with the 'Manage Standard Lookups' task. While the UI is great for quick edits, developers often need to pull this data directly for BI Publisher reports or OIC integrations. 

This guide provides a 'copy-paste' ready SQL script to retrieve lookup types, descriptions, and values. We’ll also explain the table logic so you can adapt it for your specific reporting needs.

The Core Query

This query joins the definition table (FND_LOOKUP_TYPES_VL) with the values table (FND_LOOKUP_VALUES) to give you a complete picture of a specific lookup.

SELECT flt.lookup_type

    ,flt.meaning "Lookup Type Meaning"

    ,flt.description "Lookup Type Description"

    ,flv.LOOKUP_CODE

    ,flv.meaning "Lookup Value Meaning"

    ,flv.description "Lookup Value Description"

FROM fnd_lookup_values flv

    ,fnd_lookup_types_VL flt

WHERE flv.lookup_type = 'YOUR_LOOKUP_TYPE_NAME'

    AND flv.lookup_type = flt.lookup_type

ORDER BY to_number(flv.LOOKUP_CODE)

Understanding the Components

1. The Tables

• FND_LOOKUP_TYPES_VL: This is a "Value Layer" view. It combines the base lookup type table with translated meanings, ensuring you see the description in your session's language.
• FND_LOOKUP_VALUES: This table contains the actual codes (the "behind-the-scenes" ID) and meanings (the text the user sees).

2. The Logic

• flv.lookup_type = 'YOUR_LOOKUP_TYPE_NAME': Replace this placeholder with the specific lookup name you found in the Manage Standard Lookups task in Setup and Maintenance.
• to_number(flv.LOOKUP_CODE): In many Fusion lookups, codes are numeric strings (e.g., '10', '20'). Using to_number in the ORDER BY ensures they sort logically (1, 2, 10) rather than alphabetically (1, 10, 2).

Pro-Tip: Filtering by Language

In multi-language environments, FND_LOOKUP_VALUES contains rows for every installed language. If your query returns duplicate rows for the same code, add this condition to your WHERE clause:

AND flv.language = USERENV('LANG')

The next time you’re tasked with finding a "missing" value in a dropdown, skip the multiple clicks in the Setup and Maintenance UI and go straight to the source with this script.