Search Results fnd_flex_hierarchies_uk1

Overview

The FND_FLEX_HIERARCHIES table is a core data object within the Application Object Library (FND) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the master definition table for value hierarchies associated with key flexfields. A hierarchy organizes flexfield values into a parent-child tree structure, enabling summary reporting, roll-up accounting, and structured data validation. This table stores the fundamental metadata for each hierarchy, including its unique identifier and name, and is intrinsically linked to a specific flexfield value set. The existence of a corresponding translation table (FND_FLEX_HIERARCHIES_TL) indicates that hierarchy names support multiple languages, which is critical for global deployments.

Key Information Stored

The table's structure centers on two primary key columns that uniquely identify a hierarchy within the system. The FLEX_VALUE_SET_ID is a foreign key that links the hierarchy to its parent value set in the FND_FLEX_VALUE_SETS table. The HIERARCHY_ID is a numeric identifier for the hierarchy itself. While the provided ETRM metadata does not list all columns, standard implementation includes columns such as HIERARCHY_CODE and NAME. The table enforces data integrity through its primary key (FND_FLEX_HIERARCHIES_PK on FLEX_VALUE_SET_ID and HIERARCHY_ID) and a unique key (FND_FLEX_HIERARCHIES_UK1), also on the same columns, ensuring no duplicate hierarchy definitions exist for a given value set.

Common Use Cases and Queries

This table is primarily accessed for setup, maintenance, and reporting on flexfield value hierarchies. A common administrative task is to list all hierarchies defined for a particular value set, which is essential during flexfield configuration. Developers and functional consultants often query this table to understand the hierarchical relationships available for reporting or to validate setup data. A typical query would join FND_FLEX_HIERARCHIES with FND_FLEX_VALUE_SETS to retrieve meaningful names.

• Sample Query: SELECT ffvs.flex_value_set_name, ffh.hierarchy_code, ffh.name FROM apps.fnd_flex_hierarchies ffh JOIN apps.fnd_flex_value_sets ffvs ON ffh.flex_value_set_id = ffvs.flex_value_set_id WHERE ffvs.flex_value_set_name = '&VALUE_SET_NAME';
• Reporting Use Case: Financial reports that aggregate account balances by a parent value in the Accounting Flexfield (COA) rely on hierarchies defined in this table. The hierarchy definitions here are used by the GL account hierarchy viewer and rollup group functionality.

Related Objects

FND_FLEX_HIERARCHIES is a central node in a network of related flexfield objects. Its most direct relationship is with the FND_FLEX_VALUE_SETS table, as enforced by a foreign key constraint; a hierarchy cannot exist without a value set. The FND_FLEX_HIERARCHIES_TL table stores the translated names for the hierarchies defined in this base table. For the actual parent-child relationships between specific flexfield values, the system uses the FND_FLEX_VALUE_NORM_HIERARCHY table (or similar), which references the hierarchy definition stored here. Programmatically, hierarchies are often managed via APIs such as FND_FLEX_VALIDATION_APIs, which internally reference this table's definitions.