OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Lookups, Value Sets and Flexfield Tables Reference

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.

Download:

Object	Group	Holds or meaning	Key columns	Joins	Notes
FND_LOOKUP_TYPES	Lookups	Lookup type definitions	LOOKUP_TYPE, APPLICATION_ID, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, CUSTOMIZATION_LEVEL	LOOKUP_TYPE + VIEW_APPLICATION_ID + SECURITY_GROUP_ID = FND_LOOKUP_VALUES	CUSTOMIZATION_LEVEL decides whether users can add codes (User, Extensible, System).
FND_LOOKUP_TYPES_TL	Lookups	Translated name and description of each lookup type	LOOKUP_TYPE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, LANGUAGE, MEANING, DESCRIPTION	Same keys as FND_LOOKUP_TYPES plus LANGUAGE	One row per installed language.
FND_LOOKUP_VALUES	Lookups	Lookup codes and meanings, one row per language	LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, LANGUAGE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, TAG	LOOKUP_CODE is what transaction tables store	Always filter LANGUAGE (or use a VL view), ENABLED_FLAG and the date range.
FND_LOOKUP_VALUES_VL	Lookups	View of FND_LOOKUP_VALUES in the session language	Same columns without LANGUAGE	Joins as FND_LOOKUP_VALUES	Filters LANGUAGE = USERENV('LANG'). Still filter VIEW_APPLICATION_ID when the lookup type exists under several applications.
FND_LOOKUPS	Lookups	View of Application Object Library lookups	LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE	VIEW_APPLICATION_ID = 0 inside the view, plus the lookup security group filter	Lookups such as YES_NO. A lookup defined for another view application is not in this view.
FND_COMMON_LOOKUPS	Lookups	View kept for backward compatibility with older common lookups	APPLICATION_ID, LOOKUP_TYPE, LOOKUP_CODE, MEANING	LOOKUP_TYPE + LOOKUP_CODE	Older code uses it; new code should use FND_LOOKUP_VALUES_VL with explicit filters.
AR_LOOKUPS	Lookups	Receivables lookups view	LOOKUP_TYPE, LOOKUP_CODE, MEANING	VIEW_APPLICATION_ID = 222 in the view	Example: LOOKUP_TYPE 'INV/CM' for transaction classes.
PO_LOOKUP_CODES	Lookups	Purchasing lookups view	LOOKUP_TYPE, LOOKUP_CODE, DISPLAYED_FIELD, DESCRIPTION	LOOKUP_CODE stored on PO tables	Note the column is DISPLAYED_FIELD, not MEANING.
MFG_LOOKUPS	Lookups	Manufacturing lookups view (Inventory, WIP, BOM)	LOOKUP_TYPE, LOOKUP_CODE, MEANING	VIEW_APPLICATION_ID = 700 in the view	Example: LOOKUP_TYPE 'WIP_JOB_STATUS' decodes WIP_DISCRETE_JOBS.STATUS_TYPE.
FND_FLEX_VALUE_SETS	Value sets	Value set definitions	FLEX_VALUE_SET_ID, FLEX_VALUE_SET_NAME, VALIDATION_TYPE, FORMAT_TYPE, MAXIMUM_SIZE, PARENT_FLEX_VALUE_SET_ID	FLEX_VALUE_SET_ID = FND_FLEX_VALUES, FND_FLEX_VALIDATION_TABLES, FND_ID_FLEX_SEGMENTS	PARENT_FLEX_VALUE_SET_ID is set for dependent value sets.
FND_FLEX_VALUES	Value sets	Values of independent and dependent value sets	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	FLEX_VALUE_ID = FND_FLEX_VALUES_TL.FLEX_VALUE_ID	PARENT_FLEX_VALUE_LOW holds the parent value for dependent sets. Table-validated sets have no rows here.
FND_FLEX_VALUES_TL	Value sets	Translated description of each value	FLEX_VALUE_ID, LANGUAGE, DESCRIPTION, FLEX_VALUE_MEANING	FLEX_VALUE_ID	Filter LANGUAGE or use FND_FLEX_VALUES_VL.
FND_FLEX_VALUES_VL	Value sets	View joining values and descriptions in the session language	FLEX_VALUE_SET_ID, FLEX_VALUE, DESCRIPTION, ENABLED_FLAG	FLEX_VALUE_SET_ID	The usual source for account segment descriptions in reports.
FND_FLEX_VALIDATION_TABLES	Value sets	Definition of table-validated value sets	FLEX_VALUE_SET_ID, APPLICATION_TABLE_NAME, VALUE_COLUMN_NAME, ID_COLUMN_NAME, MEANING_COLUMN_NAME, ADDITIONAL_WHERE_CLAUSE	FLEX_VALUE_SET_ID = FND_FLEX_VALUE_SETS	ADDITIONAL_WHERE_CLAUSE can hold $FLEX$ and :$PROFILES$ references.
FND_FLEX_VALUE_NORM_HIERARCHY	Value sets	Parent value child ranges as defined on the Values form	FLEX_VALUE_SET_ID, PARENT_FLEX_VALUE, RANGE_ATTRIBUTE, CHILD_FLEX_VALUE_LOW, CHILD_FLEX_VALUE_HIGH	PARENT_FLEX_VALUE = FND_FLEX_VALUES.FLEX_VALUE	RANGE_ATTRIBUTE C means child values, P means parent values in the range.
FND_ID_FLEXS	Key flexfield	Registered key flexfields	APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NAME, APPLICATION_TABLE_NAME	ID_FLEX_CODE = FND_ID_FLEX_STRUCTURES	GL# Accounting Flexfield, MSTK System Items, MCAT Item Categories.
FND_ID_FLEX_STRUCTURES	Key flexfield	Structures of a key flexfield	APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, ID_FLEX_STRUCTURE_CODE, ENABLED_FLAG	For GL#, ID_FLEX_NUM = GL_LEDGERS.CHART_OF_ACCOUNTS_ID	Use FND_ID_FLEX_STRUCTURES_VL for the structure name.
FND_ID_FLEX_SEGMENTS	Key flexfield	Segments of each structure	APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, APPLICATION_COLUMN_NAME, SEGMENT_NAME, SEGMENT_NUM, FLEX_VALUE_SET_ID, ENABLED_FLAG	FLEX_VALUE_SET_ID = FND_FLEX_VALUE_SETS	APPLICATION_COLUMN_NAME gives SEGMENT1 to SEGMENT30 on the combinations table.
FND_SEGMENT_ATTRIBUTE_VALUES	Key flexfield	Flexfield qualifiers assigned to segments	APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, APPLICATION_COLUMN_NAME, SEGMENT_ATTRIBUTE_TYPE, ATTRIBUTE_VALUE	Same keys as FND_ID_FLEX_SEGMENTS	Find the balancing segment with SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING' and ATTRIBUTE_VALUE = 'Y'; natural account is 'GL_ACCOUNT'.
FND_DESCRIPTIVE_FLEXS	Descriptive flexfield	Registered descriptive flexfields	APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, APPLICATION_TABLE_NAME, CONTEXT_COLUMN_NAME	DESCRIPTIVE_FLEXFIELD_NAME = FND_DESCR_FLEX_CONTEXTS	CONTEXT_COLUMN_NAME is usually ATTRIBUTE_CATEGORY or CONTEXT.
FND_DESCR_FLEX_CONTEXTS	Descriptive flexfield	Contexts of each DFF, including Global Data Elements	APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE, ENABLED_FLAG, GLOBAL_FLAG	Context code value stored in the context column of the base table	GLOBAL_FLAG = 'Y' marks Global Data Elements, shown for every context.
FND_DESCR_FLEX_COLUMN_USAGES	Descriptive flexfield	Segments of each DFF context	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	APPLICATION_COLUMN_NAME = ATTRIBUTE1 to ATTRIBUTEn on the base table	This is how you learn which ATTRIBUTEn holds which business field.
I	Validation type code	Independent	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	Values in FND_FLEX_VALUES	Plain list of values.
D	Validation type code	Dependent	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	PARENT_FLEX_VALUE_SET_ID and FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW	Values valid only for a given value of the parent independent set.
F	Validation type code	Table	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	FND_FLEX_VALIDATION_TABLES	Values come from a table or view at run time.
N	Validation type code	None	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	None	Only format and size are checked.
P	Validation type code	Pair	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	Flexfield routine	Used for ranges of flexfield values, for example in report parameters.
U	Validation type code	Special	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	Flexfield routine	Embeds a flexfield, for example an Accounting Flexfield parameter.
X	Validation type code	Translatable Independent	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	FND_FLEX_VALUES and FND_FLEX_VALUES_TL	Hidden value with translated displayed value.
Y	Validation type code	Translatable Dependent	FND_FLEX_VALUE_SETS.VALIDATION_TYPE	FND_FLEX_VALUES and FND_FLEX_VALUES_TL	Dependent version of translatable values.

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.

DFF and value set documentation

Send us the flexfields your reports depend on and we will return a one-page map of contexts, ATTRIBUTE columns and value set sources.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

Where these are defined

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.

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

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.

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

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

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.

Frequently Asked Questions

Which table stores value set values in Oracle EBS?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools