Results for “fnd_flex_validation_tables”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

FND_FLEX_VALIDATION_TABLES is a core reference table in the Oracle E-Business Suite Application Object Library (FND) schema, owned by APPLSYS. It stores the metadata that defines how a Key Flexfield or Descriptive Flexfield value set validates its values against an external application table. Rather than holding the validation values themselves, this table records the physical mapping between a value set and the database columns that supply those values — such as the value column, meaning column, ID column, and optional hierarchy, enabled, start-date, and end-date columns. In Oracle EBS 12.1.1 and 12.2.2 it is the configuration artifact consulted by the flexfield runtime engine whenever a value set uses a "Table" validation type instead of an independent or dependent value set.

From a Data Vault modeling perspective, the ETRM relationship metadata classifies this object as satellite-leaning. This is a modeling suggestion rather than a physical constraint: the table hangs off FND_FLEX_VALUE_SETS through a single foreign key and carries descriptive, configuration-oriented attributes rather than acting as an independent business hub.

Key Information Stored

The primary key, FND_FLEX_VALIDATION_TABLES_PK, is defined solely on FLEX_VALUE_SET_ID, making it both the surrogate identifier and the foreign key to FND_FLEX_VALUE_SETS. A separate unique index, FND_FLEX_VALIDATION_TABLES_U1, spans FLEX_VALUE_SET_ID and ZD_EDITION_NAME, which serves as the business-key candidate and supports multi-tenant or edition-based variation of the same value set definition.

The most significant columns fall into three groups:

• Application table binding: APPLICATION_TABLE_NAME and TABLE_APPLICATION_ID identify the external table and the owning application against which validation is performed.
• Value and meaning mapping: VALUE_COLUMN_NAME, VALUE_COLUMN_TYPE, and VALUE_COLUMN_SIZE describe the validated value column; MEANING_COLUMN_NAME, MEANING_COLUMN_SIZE, and MEANING_COLUMN_TYPE describe its descriptive meaning; ID_COLUMN_NAME, ID_COLUMN_SIZE, and ID_COLUMN_TYPE capture the hidden ID column.
• Behavioral controls: ENABLED_COLUMN_NAME, START_DATE_COLUMN_NAME, END_DATE_COLUMN_NAME, HIERARCHY_LEVEL_COLUMN_NAME, SUMMARY_ALLOWED_FLAG, SUMMARY_COLUMN_NAME, COMPILED_ATTRIBUTE_COLUMN_NAME, ADDITIONAL_QUICKPICK_COLUMNS, and ADDITIONAL_WHERE_CLAUSE govern filtering, date-effective validation, hierarchy support, and quick-pick display.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN track change history.

Common Use Cases and Queries

Typical scenarios include diagnosing why a flexfield value fails validation, auditing which value sets reference external tables, and migrating validation definitions between environments. A representative query joins the table to FND_FLEX_VALUE_SETS to resolve the value set name:

• SELECT v.FLEX_VALUE_SET_NAME, t.APPLICATION_TABLE_NAME, t.VALUE_COLUMN_NAME, t.MEANING_COLUMN_NAME FROM FND_FLEX_VALIDATION_TABLES t, FND_FLEX_VALUE_SETS v WHERE t.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID;
• Filter by APPLICATION_TABLE_NAME to locate all value sets validating against a given table such as GL_CODE_COMBINATIONS or HR_ALL_ORGANIZATION_UNITS.
• Report on SUMMARY_ALLOWED_FLAG and HIERARCHY_LEVEL_COLUMN_NAME to identify hierarchies enabled for consolidation or rollup reporting.

Related Objects

The dominant relationship is the foreign key from FND_FLEX_VALIDATION_TABLES.FLEX_VALUE_SET_ID to FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID, which is the parent of every validation-table definition. Additional significant dependencies include FND_FLEX_VALUES and FND_FLEX_VALUES_TL, which hold the resolved validated values and their translated meanings; FND_FLEX_VALUE_SETS_TL, which supplies translated value set names; FND_ID_FLEX_STRUCTURES and FND_ID_FLEX_SEGMENTS, which associate segments with value sets; and FND_FLEX_VALIDATION_EVENTS, which records validation activity. Together these objects form the flexfield value-set validation subsystem within the Application Object Library.