# Oracle EBS Lookups, Value Sets and Flexfield Tables Reference > Source: https://netray.co/tools/oracle-ebs-lookups-and-value-sets-tables-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Every FND table and view you need to decode lookup codes, value set values, table-validated value sets, key flexfield segments and DFF ATTRIBUTE columns in EBS 12.1 and 12.2. ## Category oracle ## Erp Oracle E-Business Suite ## Audience - Oracle EBS developer - Report developer - Functional consultant - Oracle Apps DBA ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Object **Key:** object **Type:** text #### Group **Key:** group **Type:** text #### Holds or meaning **Key:** holds **Type:** text #### Key columns **Key:** keys **Type:** text #### Joins **Key:** joins **Type:** text #### Notes **Key:** notes **Type:** text **Rows:** **Object:** FND_LOOKUP_TYPES **Group:** Lookups **Holds:** Lookup type definitions **Keys:** LOOKUP_TYPE, APPLICATION_ID, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, CUSTOMIZATION_LEVEL **Joins:** LOOKUP_TYPE + VIEW_APPLICATION_ID + SECURITY_GROUP_ID = FND_LOOKUP_VALUES **Notes:** CUSTOMIZATION_LEVEL decides whether users can add codes (User, Extensible, System). **Object:** FND_LOOKUP_TYPES_TL **Group:** Lookups **Holds:** Translated name and description of each lookup type **Keys:** LOOKUP_TYPE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, LANGUAGE, MEANING, DESCRIPTION **Joins:** Same keys as FND_LOOKUP_TYPES plus LANGUAGE **Notes:** One row per installed language. **Object:** FND_LOOKUP_VALUES **Group:** Lookups **Holds:** Lookup codes and meanings, one row per language **Keys:** LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, LANGUAGE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, TAG **Joins:** LOOKUP_CODE is what transaction tables store **Notes:** Always filter LANGUAGE (or use a VL view), ENABLED_FLAG and the date range. **Object:** FND_LOOKUP_VALUES_VL **Group:** Lookups **Holds:** View of FND_LOOKUP_VALUES in the session language **Keys:** Same columns without LANGUAGE **Joins:** Joins as FND_LOOKUP_VALUES **Notes:** Filters LANGUAGE = USERENV('LANG'). Still filter VIEW_APPLICATION_ID when the lookup type exists under several applications. **Object:** FND_LOOKUPS **Group:** Lookups **Holds:** View of Application Object Library lookups **Keys:** LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE **Joins:** VIEW_APPLICATION_ID = 0 inside the view, plus the lookup security group filter **Notes:** Lookups such as YES_NO. A lookup defined for another view application is not in this view. **Object:** FND_COMMON_LOOKUPS **Group:** Lookups **Holds:** View kept for backward compatibility with older common lookups **Keys:** APPLICATION_ID, LOOKUP_TYPE, LOOKUP_CODE, MEANING **Joins:** LOOKUP_TYPE + LOOKUP_CODE **Notes:** Older code uses it; new code should use FND_LOOKUP_VALUES_VL with explicit filters. **Object:** AR_LOOKUPS **Group:** Lookups **Holds:** Receivables lookups view **Keys:** LOOKUP_TYPE, LOOKUP_CODE, MEANING **Joins:** VIEW_APPLICATION_ID = 222 in the view **Notes:** Example: LOOKUP_TYPE 'INV/CM' for transaction classes. **Object:** PO_LOOKUP_CODES **Group:** Lookups **Holds:** Purchasing lookups view **Keys:** LOOKUP_TYPE, LOOKUP_CODE, DISPLAYED_FIELD, DESCRIPTION **Joins:** LOOKUP_CODE stored on PO tables **Notes:** Note the column is DISPLAYED_FIELD, not MEANING. **Object:** MFG_LOOKUPS **Group:** Lookups **Holds:** Manufacturing lookups view (Inventory, WIP, BOM) **Keys:** LOOKUP_TYPE, LOOKUP_CODE, MEANING **Joins:** VIEW_APPLICATION_ID = 700 in the view **Notes:** Example: LOOKUP_TYPE 'WIP_JOB_STATUS' decodes WIP_DISCRETE_JOBS.STATUS_TYPE. **Object:** FND_FLEX_VALUE_SETS **Group:** Value sets **Holds:** Value set definitions **Keys:** FLEX_VALUE_SET_ID, FLEX_VALUE_SET_NAME, VALIDATION_TYPE, FORMAT_TYPE, MAXIMUM_SIZE, PARENT_FLEX_VALUE_SET_ID **Joins:** FLEX_VALUE_SET_ID = FND_FLEX_VALUES, FND_FLEX_VALIDATION_TABLES, FND_ID_FLEX_SEGMENTS **Notes:** PARENT_FLEX_VALUE_SET_ID is set for dependent value sets. **Object:** FND_FLEX_VALUES **Group:** Value sets **Holds:** Values of independent and dependent value sets **Keys:** FLEX_VALUE_SET_ID, FLEX_VALUE_ID, FLEX_VALUE, PARENT_FLEX_VALUE_LOW, ENABLED_FLAG, SUMMARY_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, COMPILED_VALUE_ATTRIBUTES **Joins:** FLEX_VALUE_ID = FND_FLEX_VALUES_TL.FLEX_VALUE_ID **Notes:** PARENT_FLEX_VALUE_LOW holds the parent value for dependent sets. Table-validated sets have no rows here. **Object:** FND_FLEX_VALUES_TL **Group:** Value sets **Holds:** Translated description of each value **Keys:** FLEX_VALUE_ID, LANGUAGE, DESCRIPTION, FLEX_VALUE_MEANING **Joins:** FLEX_VALUE_ID **Notes:** Filter LANGUAGE or use FND_FLEX_VALUES_VL. **Object:** FND_FLEX_VALUES_VL **Group:** Value sets **Holds:** View joining values and descriptions in the session language **Keys:** FLEX_VALUE_SET_ID, FLEX_VALUE, DESCRIPTION, ENABLED_FLAG **Joins:** FLEX_VALUE_SET_ID **Notes:** The usual source for account segment descriptions in reports. **Object:** FND_FLEX_VALIDATION_TABLES **Group:** Value sets **Holds:** Definition of table-validated value sets **Keys:** FLEX_VALUE_SET_ID, APPLICATION_TABLE_NAME, VALUE_COLUMN_NAME, ID_COLUMN_NAME, MEANING_COLUMN_NAME, ADDITIONAL_WHERE_CLAUSE **Joins:** FLEX_VALUE_SET_ID = FND_FLEX_VALUE_SETS **Notes:** ADDITIONAL_WHERE_CLAUSE can hold $FLEX$ and :$PROFILES$ references. **Object:** FND_FLEX_VALUE_NORM_HIERARCHY **Group:** Value sets **Holds:** Parent value child ranges as defined on the Values form **Keys:** FLEX_VALUE_SET_ID, PARENT_FLEX_VALUE, RANGE_ATTRIBUTE, CHILD_FLEX_VALUE_LOW, CHILD_FLEX_VALUE_HIGH **Joins:** PARENT_FLEX_VALUE = FND_FLEX_VALUES.FLEX_VALUE **Notes:** RANGE_ATTRIBUTE C means child values, P means parent values in the range. **Object:** FND_ID_FLEXS **Group:** Key flexfield **Holds:** Registered key flexfields **Keys:** APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NAME, APPLICATION_TABLE_NAME **Joins:** ID_FLEX_CODE = FND_ID_FLEX_STRUCTURES **Notes:** GL# Accounting Flexfield, MSTK System Items, MCAT Item Categories. **Object:** FND_ID_FLEX_STRUCTURES **Group:** Key flexfield **Holds:** Structures of a key flexfield **Keys:** APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, ID_FLEX_STRUCTURE_CODE, ENABLED_FLAG **Joins:** For GL#, ID_FLEX_NUM = GL_LEDGERS.CHART_OF_ACCOUNTS_ID **Notes:** Use FND_ID_FLEX_STRUCTURES_VL for the structure name. **Object:** FND_ID_FLEX_SEGMENTS **Group:** Key flexfield **Holds:** Segments of each structure **Keys:** APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, APPLICATION_COLUMN_NAME, SEGMENT_NAME, SEGMENT_NUM, FLEX_VALUE_SET_ID, ENABLED_FLAG **Joins:** FLEX_VALUE_SET_ID = FND_FLEX_VALUE_SETS **Notes:** APPLICATION_COLUMN_NAME gives SEGMENT1 to SEGMENT30 on the combinations table. **Object:** FND_SEGMENT_ATTRIBUTE_VALUES **Group:** Key flexfield **Holds:** Flexfield qualifiers assigned to segments **Keys:** APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, APPLICATION_COLUMN_NAME, SEGMENT_ATTRIBUTE_TYPE, ATTRIBUTE_VALUE **Joins:** Same keys as FND_ID_FLEX_SEGMENTS **Notes:** Find the balancing segment with SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING' and ATTRIBUTE_VALUE = 'Y'; natural account is 'GL_ACCOUNT'. **Object:** FND_DESCRIPTIVE_FLEXS **Group:** Descriptive flexfield **Holds:** Registered descriptive flexfields **Keys:** APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, APPLICATION_TABLE_NAME, CONTEXT_COLUMN_NAME **Joins:** DESCRIPTIVE_FLEXFIELD_NAME = FND_DESCR_FLEX_CONTEXTS **Notes:** CONTEXT_COLUMN_NAME is usually ATTRIBUTE_CATEGORY or CONTEXT. **Object:** FND_DESCR_FLEX_CONTEXTS **Group:** Descriptive flexfield **Holds:** Contexts of each DFF, including Global Data Elements **Keys:** APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE, ENABLED_FLAG, GLOBAL_FLAG **Joins:** Context code value stored in the context column of the base table **Notes:** GLOBAL_FLAG = 'Y' marks Global Data Elements, shown for every context. **Object:** FND_DESCR_FLEX_COLUMN_USAGES **Group:** Descriptive flexfield **Holds:** Segments of each DFF context **Keys:** APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE, APPLICATION_COLUMN_NAME, END_USER_COLUMN_NAME, COLUMN_SEQ_NUM, FLEX_VALUE_SET_ID, ENABLED_FLAG **Joins:** APPLICATION_COLUMN_NAME = ATTRIBUTE1 to ATTRIBUTEn on the base table **Notes:** This is how you learn which ATTRIBUTEn holds which business field. **Object:** I **Group:** Validation type code **Holds:** Independent **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** Values in FND_FLEX_VALUES **Notes:** Plain list of values. **Object:** D **Group:** Validation type code **Holds:** Dependent **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** PARENT_FLEX_VALUE_SET_ID and FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW **Notes:** Values valid only for a given value of the parent independent set. **Object:** F **Group:** Validation type code **Holds:** Table **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** FND_FLEX_VALIDATION_TABLES **Notes:** Values come from a table or view at run time. **Object:** N **Group:** Validation type code **Holds:** None **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** None **Notes:** Only format and size are checked. **Object:** P **Group:** Validation type code **Holds:** Pair **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** Flexfield routine **Notes:** Used for ranges of flexfield values, for example in report parameters. **Object:** U **Group:** Validation type code **Holds:** Special **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** Flexfield routine **Notes:** Embeds a flexfield, for example an Accounting Flexfield parameter. **Object:** X **Group:** Validation type code **Holds:** Translatable Independent **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** FND_FLEX_VALUES and FND_FLEX_VALUES_TL **Notes:** Hidden value with translated displayed value. **Object:** Y **Group:** Validation type code **Holds:** Translatable Dependent **Keys:** FND_FLEX_VALUE_SETS.VALIDATION_TYPE **Joins:** FND_FLEX_VALUES and FND_FLEX_VALUES_TL **Notes:** Dependent version of translatable values. **Notes:** EBS 12.1 and 12.2. Module lookup views add fixed VIEW_APPLICATION_ID filters; open the view text in ALL_VIEWS on your instance to see the exact filter. ## Content **Intro:** This reference lists the Application Object Library tables and views behind lookups, value sets and flexfields in Oracle E-Business Suite 12.1 and 12.2, with join keys and the VALIDATION_TYPE codes. Use it to decode a stored code into its meaning in a report or to find which column a DFF segment uses. **Sections:** #### Where these are defined **Body:** Lookups are maintained in Application Developer > Application > Lookups > Application Object Library, with Common Lookups and module-specific lookup screens in some products. Value sets are defined in Application Developer > Application > Validation > Set and their values in Validation > Values; System Administrator has the same forms. Key flexfield segments are in Application > Flexfield > Key > Segments and DFFs in Flexfield > Descriptive > Segments. Each save writes to the tables in this reference, so a report can always resolve the meaning shown on screen. **Bullets:** - Lookup code stored on transaction rows, meaning in FND_LOOKUP_VALUES - Value set value stored on rows, description in FND_FLEX_VALUES_TL - DFF segment stored in ATTRIBUTEn, label in FND_DESCR_FLEX_COLUMN_USAGES #### Anatomy of a table-validated value set **Body:** A value set with VALIDATION_TYPE 'F' has no rows in FND_FLEX_VALUES. Its definition sits in FND_FLEX_VALIDATION_TABLES: APPLICATION_TABLE_NAME (a table, view or inline list of tables with aliases), VALUE_COLUMN_NAME (shown and stored if no ID column), ID_COLUMN_NAME (stored when present), MEANING_COLUMN_NAME and ADDITIONAL_WHERE_CLAUSE. The where clause can reference earlier parameters with :$FLEX$.VALUE_SET_NAME and profile values with :$PROFILES$.ORG_ID. To reproduce the list in SQL, build SELECT value_column, id_column FROM application_table WHERE additional_where_clause with the bind values filled in. **Bullets:** - Example: APPLICATION_TABLE_NAME = 'MTL_SYSTEM_ITEMS_B', VALUE_COLUMN_NAME = 'SEGMENT1', ID_COLUMN_NAME = 'INVENTORY_ITEM_ID' - WHERE ORGANIZATION_ID = :$FLEX$.XX_INV_ORG AND ENABLED_FLAG = 'Y' #### DFF context and ATTRIBUTE columns **Body:** Descriptive flexfields store their context value in a context column, usually ATTRIBUTE_CATEGORY (named in FND_DESCRIPTIVE_FLEXS.CONTEXT_COLUMN_NAME), and segment values in ATTRIBUTE1 to ATTRIBUTEn. Which ATTRIBUTE holds which business field depends on the context: query FND_DESCR_FLEX_COLUMN_USAGES for the DFF name and context code, plus the 'Global Data Elements' context which applies to every row. If a segment uses a value set with an ID column, the ATTRIBUTE column stores the ID, so the report must join back to the value set source to show the meaning. #### Gotcha: language and view application filters **Body:** FND_LOOKUP_VALUES and FND_FLEX_VALUES_TL hold one row per installed language. Joining without LANGUAGE = USERENV('LANG') duplicates every row on a multi-language instance, which is the most common bug in custom lookup decodes. The same LOOKUP_TYPE can exist under more than one VIEW_APPLICATION_ID, so also filter that column or use the module view (AR_LOOKUPS, MFG_LOOKUPS). Finally, a code may be disabled or end-dated but still stored on old transactions: decode history with ENABLED_FLAG and dates ignored, and validate new data with them applied. **Cta:** If you want users to ask what a code or DFF field means and get the answer straight from your EBS setup tables on your own servers, we can set that up. **Faq:** #### Which table stores value set values in Oracle EBS? **Answer:** Value set headers are in FND_FLEX_VALUE_SETS and values in FND_FLEX_VALUES, with descriptions in FND_FLEX_VALUES_TL (or the FND_FLEX_VALUES_VL view). Join on FLEX_VALUE_SET_ID and filter FLEX_VALUE_SET_NAME. Table-validated value sets have no values stored; their source table and columns are in FND_FLEX_VALIDATION_TABLES. #### What is a table type value set in Oracle Apps? **Answer:** A value set with VALIDATION_TYPE 'F' that reads its list from a table or view at run time. You define the table, the value column, an optional ID and meaning column and a WHERE clause. It suits lists that already exist as data, such as items, suppliers or custom setup tables. #### Which table holds lookups in Oracle R12? **Answer:** FND_LOOKUP_TYPES holds the types and FND_LOOKUP_VALUES holds codes and meanings per language. Use FND_LOOKUP_VALUES_VL for the session language, or module views such as AR_LOOKUPS, PO_LOOKUP_CODES and MFG_LOOKUPS that add the right VIEW_APPLICATION_ID filter. #### What do the VALIDATION_TYPE codes mean? **Answer:** I is Independent, D Dependent, F Table, N None, P Pair, U Special, X Translatable Independent and Y Translatable Dependent. The code is in FND_FLEX_VALUE_SETS.VALIDATION_TYPE and decides where values live: FND_FLEX_VALUES for I, D, X and Y, FND_FLEX_VALIDATION_TABLES for F. ## Search terms value set table in oracle ebs, table type value set in oracle apps, lookups in oracle ebs, oracle r12 lookup table, oracle ebs lookup table, fnd_flex_value_sets validation_type, fnd_lookup_values --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json