Results for “fnd_flex_hierarchies”

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

Overview

FND_FLEX_HIERARCHIES is a core Application Object Library (FND) table owned by the APPLSYS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the header-level definition of flexfield hierarchies — named, tree-like groupings of flexfield values — that allow users to organize and roll up values within a given value set. Each row represents a single hierarchy associated with one flexfield value set, and acts as the parent record for translated hierarchy names held in the companion table FND_FLEX_HIERARCHIES_TL, as well as the detailed parent-child assignments typically stored in hierarchy-related structures. Because flexfield hierarchies drive account rollups, reporting aggregations, and Oracle General Ledger summary behavior, this table is central to how hierarchical value relationships are defined and maintained across the EBS data model.

From a Data Vault modeling perspective, the mined FK structure classifies this object as satellite-leaning. That is a heuristic suggestion rather than an enforced design: the table primarily holds descriptive and attribute data (hierarchy code, audit columns) keyed to the parent value set, behaving like a satellite hanging off the FND_FLEX_VALUE_SETS hub, with FND_FLEX_HIERARCHIES_TL functioning as a dependent child rather than a full transactional hub.

Key Information Stored

The table contains nine documented columns. The most significant are:

• FLEX_VALUE_SET_ID — Identifies the flexfield value set to which the hierarchy belongs. This is part of the primary key and the foreign key to FND_FLEX_VALUE_SETS.
• HIERARCHY_ID — The system-generated identifier for the hierarchy itself; the second component of the composite surrogate primary key.
• HIERARCHY_CODE — The user-defined alphanumeric code that uniquely names the hierarchy within its value set. This is a business-key candidate (documented in unique index U2 alongside FLEX_VALUE_SET_ID and ZD_EDITION_NAME).
• ZD_EDITION_NAME — The edition designator supporting the Oracle "edition" feature; it participates in both unique indexes and effectively partitions rows by edition.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking who created and last modified each hierarchy row and when.

The documented surrogate primary key is FND_FLEX_HIERARCHIES_PK (FLEX_VALUE_SET_ID, HIERARCHY_ID). Business-key candidates are captured by two unique indexes: FND_FLEX_HIERARCHIES_U1 (FLEX_VALUE_SET_ID, HIERARCHY_ID, ZD_EDITION_NAME) and FND_FLEX_HIERARCHIES_U2 (FLEX_VALUE_SET_ID, HIERARCHY_CODE, ZD_EDITION_NAME). The distinction matters: the PK guarantees row uniqueness, while the U1/U2 indexes guarantee that no two hierarchies in the same value set and edition share an ID or a code.

Common Use Cases and Queries

Typical uses include locating all hierarchies defined for a value set, resolving a hierarchy code to its internal ID for downstream joins, and reporting on hierarchy metadata alongside translated names.

• List hierarchies for a value set:
  SELECT hierarchy_id, hierarchy_code FROM fnd_flex_hierarchies WHERE flex_value_set_id = :p_value_set_id;
• Join to translated names:
  SELECT h.hierarchy_code, t.hierarchy_name FROM fnd_flex_hierarchies h, fnd_flex_hierarchies_tl t WHERE h.flex_value_set_id = t.flex_value_set_id AND h.hierarchy_id = t.hierarchy_id AND t.language = USERENV('LANG');
• Resolve the owning value set:
  SELECT v.flex_value_set_name FROM fnd_flex_value_sets v, fnd_flex_hierarchies h WHERE v.flex_value_set_id = h.flex_value_set_id;
• Audit recent changes via the WHO columns for control or reconciliation reporting.

Related Objects

• FND_FLEX_VALUE_SETS — Parent table; joined on FLEX_VALUE_SET_ID. Defines the value set a hierarchy belongs to.
• FND_FLEX_HIERARCHIES_TL — Translation table holding language-specific hierarchy names; joined on FLEX_VALUE_SET_ID and HIERARCHY_ID.
• FND_FLEX_HIERARCHIES_PK / _U1 / _U2 — The primary key and unique indexes enforcing identity and business-key uniqueness.
• FND_FLEX_VALUES — Value definitions that populate hierarchy members; related indirectly through the value set.
• FND_FLEX_VALUE_NORM_HIERARCHY — Normalized parent-child hierarchy assignment structure commonly queried together with this header table.

Together these objects form the flexfield hierarchy model used throughout EBS reporting, rollup, and value security logic.