# Oracle EBS Value Set Query, Lookup Query and DFF Query Generator

> Source: https://netray.co/tools/oracle-ebs-value-set-lookup-dff-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate the queries that read value set definitions and values, rebuild a table-validated LOV, list FND lookup codes and map DFF segments to ATTRIBUTE columns.

## Category

developer

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS developer
- Functional consultant
- Reports developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Value set name

**Key:** value_set

**Type:** text

**Default:** XX_COMPANY_VS

#### Lookup type

**Key:** lookup_type

**Type:** text

**Default:** YES_NO

#### DFF name or title

**Key:** dff

**Type:** text

**Default:** AP_INVOICES

**Help:** DESCRIPTIVE_FLEXFIELD_NAME or the user title.

#### Application short name of the DFF

**Key:** app

**Type:** text

**Default:** SQLAP

#### Language

**Key:** lang

**Type:** text

**Default:** US

**Blocks:**

#### 1. Value set header and type

**Body:** SELECT s.flex_value_set_id,
       s.flex_value_set_name,
       DECODE(s.validation_type,
              'I', 'Independent', 'D', 'Dependent', 'F', 'Table',
              'N', 'None', 'P', 'Pair', 'U', 'Special',
              'X', 'Translatable independent', 'Y', 'Translatable dependent',
              s.validation_type) AS validation_type,
       s.format_type,
       s.maximum_size,
       s.description
FROM   fnd_flex_value_sets s
WHERE  s.flex_value_set_name = '{{value_set}}';

#### 2. Values of an independent or dependent value set

**Body:** SELECT v.flex_value,
       v.description,
       v.enabled_flag,
       v.parent_flex_value_low,
       v.start_date_active,
       v.end_date_active,
       v.summary_flag
FROM   fnd_flex_values_vl v
JOIN   fnd_flex_value_sets s ON s.flex_value_set_id = v.flex_value_set_id
WHERE  s.flex_value_set_name = '{{value_set}}'
ORDER  BY v.parent_flex_value_low, v.flex_value;

#### 3. Rebuild the LOV query of a table-validated value set

**Body:** -- ADDITIONAL_WHERE_CLAUSE is selected on its own: it is a LONG column and cannot be concatenated
SELECT 'SELECT ' || t.value_column_name
       || NVL2(t.meaning_column_name, ', ' || t.meaning_column_name, '')
       || NVL2(t.id_column_name, ', ' || t.id_column_name, '')
       || ' FROM ' || t.application_table_name AS lov_select,
       t.additional_where_clause
FROM   fnd_flex_validation_tables t
JOIN   fnd_flex_value_sets s ON s.flex_value_set_id = t.flex_value_set_id
WHERE  s.flex_value_set_name = '{{value_set}}';

#### 4. Active lookup values

**Body:** SELECT lv.lookup_code,
       lv.meaning,
       lv.description,
       lv.tag,
       lv.view_application_id,
       lv.security_group_id
FROM   fnd_lookup_values lv
WHERE  lv.lookup_type = '{{lookup_type}}'
AND    lv.language = '{{lang}}'
AND    lv.enabled_flag = 'Y'
AND    TRUNC(SYSDATE) BETWEEN NVL(lv.start_date_active, TRUNC(SYSDATE))
                          AND NVL(lv.end_date_active, TRUNC(SYSDATE))
ORDER  BY lv.lookup_code;

#### 5. DFF contexts and segments mapped to ATTRIBUTE columns

**Body:** SELECT df.descriptive_flexfield_name,
       df.title,
       df.application_table_name,
       u.descriptive_flex_context_code,
       u.column_seq_num,
       u.application_column_name,
       u.end_user_column_name,
       u.form_left_prompt,
       u.enabled_flag,
       u.required_flag,
       s.flex_value_set_name
FROM   fnd_descriptive_flexs_vl df
JOIN   fnd_application fa
       ON fa.application_id = df.application_id
JOIN   fnd_descr_flex_col_usage_vl u
       ON u.application_id = df.application_id
      AND u.descriptive_flexfield_name = df.descriptive_flexfield_name
LEFT JOIN fnd_flex_value_sets s
       ON s.flex_value_set_id = u.flex_value_set_id
WHERE  fa.application_short_name = '{{app}}'
AND   (df.descriptive_flexfield_name = '{{dff}}' OR df.title = '{{dff}}')
ORDER  BY u.descriptive_flex_context_code, u.column_seq_num;

**Notes:** Uses the standard FND flexfield and lookup tables and views. Verify column names on your release, and note that lookups can be shared across view applications and security groups, so filter on VIEW_APPLICATION_ID when the same lookup type exists twice.

## Content

**Intro:** This generator writes five queries against the Oracle EBS FND flexfield and lookup tables: value set definition, value set values, a rebuilt LOV query for table-validated sets, active lookup codes and descriptive flexfield segments with their ATTRIBUTE columns. It works on 12.1 and 12.2.

**Sections:**

#### How to use it

**Body:** Enter the value set name, a lookup type, the DFF name or title and its application short name. Block 1 tells you the validation type, which decides the next step: Independent and Dependent sets store values in FND_FLEX_VALUES, so use block 2; Table sets store a query definition, so use block 3 to reconstruct the SELECT a form or concurrent program LOV runs. Block 4 returns the codes behind a lookup-driven list, and block 5 tells you which ATTRIBUTEn column on the base table holds each DFF segment, which is what you need for reports and interfaces.

**Bullets:**

- Find the value set of a concurrent program parameter in block 5 using DFF name $SRS$.<program short name> and the program's application short name.
- Translatable sets (X and Y) also keep their values in FND_FLEX_VALUES_VL.

#### Where these objects live

**Body:** Value sets are defined under Application Developer > Validation > Set, with values maintained under Validation > Values. The header is FND_FLEX_VALUE_SETS; values are in FND_FLEX_VALUES with translations in FND_FLEX_VALUES_TL, joined in FND_FLEX_VALUES_VL. Table validation details sit in FND_FLEX_VALIDATION_TABLES: table name, value, meaning and ID columns and the WHERE/ORDER BY clause. Lookups are maintained under Application Developer > Application > Lookups and stored in FND_LOOKUP_VALUES per language and security group. DFF registration is in FND_DESCRIPTIVE_FLEXS, contexts in FND_DESCR_FLEX_CONTEXTS and segments in FND_DESCR_FLEX_COLUMN_USAGES, with the _VL views adding translated prompts.

#### Reading table-validated LOVs

**Body:** The additional WHERE clause often contains references that only resolve at runtime. :$FLEX$.<value set name> refers to the value chosen in an earlier segment or parameter that uses that value set, and :$PROFILES$.<profile option name> substitutes a profile value such as ORG_ID. To run the rebuilt query in SQL Developer, replace these with literal values or bind variables. If the LOV on a form field is not driven by a value set, it comes from a record group in the form itself; in that case trace the session (Help > Diagnostics > Trace) and read the SQL from the trace file.

#### Gotchas

**Body:** FND_LOOKUP_VALUES has one row per language, so always filter LANGUAGE or you get duplicates on a multilingual instance. The same lookup type can exist under different view applications, which is why block 4 returns VIEW_APPLICATION_ID and SECURITY_GROUP_ID. Disabled DFF segments still appear in the column usage view with ENABLED_FLAG N, and Global Data Elements segments use the context code Global Data Elements. A dependent value set's values repeat per parent, so read PARENT_FLEX_VALUE_LOW together with FLEX_VALUE.

**Cta:** If your users want to ask what a DFF field or lookup code means in plain English, answered from your EBS setup on your own servers, we can set that up.

**Faq:**

#### How do I find the values of a value set in Oracle Apps R12?

**Answer:** Join FND_FLEX_VALUES_VL to FND_FLEX_VALUE_SETS on FLEX_VALUE_SET_ID and filter FLEX_VALUE_SET_NAME. This works for independent, dependent and translatable sets. Table-validated sets do not store values; read FND_FLEX_VALIDATION_TABLES to get the table and columns and run that query instead.

#### How do I find the LOV query behind a parameter in Oracle Apps?

**Answer:** Find the parameter's value set (Concurrent > Program > Parameters, or the $SRS$ DFF in block 5), then rebuild the query from FND_FLEX_VALIDATION_TABLES with block 3, and resolve any :$FLEX$ or :$PROFILES$ references. For form fields with hard-coded record groups, use a session trace to capture the SQL.

#### Which table stores lookups in Oracle EBS?

**Answer:** FND_LOOKUP_VALUES holds the codes, meanings and dates per language, and FND_LOOKUP_TYPES the types. Views such as FND_LOOKUP_VALUES_VL and product views such as FND_LOOKUPS or AR_LOOKUPS add filtering. Some products keep their own lookups in tables like PO_LOOKUP_CODES.

#### How do I find which ATTRIBUTE column holds a DFF segment?

**Answer:** Query FND_DESCR_FLEX_COL_USAGE_VL by DESCRIPTIVE_FLEXFIELD_NAME and context code. APPLICATION_COLUMN_NAME is the base table column, such as ATTRIBUTE3, and END_USER_COLUMN_NAME and FORM_LEFT_PROMPT are the segment name and prompt users see. The context value itself is stored in the table's ATTRIBUTE_CATEGORY or equivalent context column.

## Search terms

value set query in oracle apps r12, oracle ebs query value set, query for lookups in oracle apps, how to find lov query in oracle apps r12, dff query oracle apps r12, fnd_flex_values_vl query, fnd_descr_flex_col_usage_vl query, fnd_lookup_values query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json