Results for “fnd_flex_value_norm_hierarchy”

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

Overview

FND_FLEX_VALUE_NORM_HIERARCHY is an Oracle E-Business Suite Applications Object Library (FND) table owned by the APPLSYS schema. In Oracle EBS 12.1.1 and 12.2.2 it stores multiple-level key flexfield hierarchies — the "normal" (as opposed to rollup-group) parent/child relationships defined within a key flexfield value set. Where a rollup group aggregates a fixed list of child values under a parent, this table instead allows a parent to own a range of child values, expressed as a low and a high boundary. This is what enables flexible value hierarchies to expand dynamically as new detail values are added to a value set, without editing the hierarchy definition itself.

From a Data Vault modeling perspective, the FK structure suggests this object is satellite-leaning: it hangs off FND_FLEX_VALUE_SETS via FLEX_VALUE_SET_ID and records the descriptive attributes of the hierarchy relationship rather than acting as an independent hub. The value set itself behaves as the parent hub, and this table carries the relationship detail and its change history columns.

Key Information Stored

• FLEX_VALUE_SET_ID — Foreign key to FND_FLEX_VALUE_SETS, identifying which key flexfield value set owns the hierarchy. It forms the leading column of the primary key.
• PARENT_FLEX_VALUE — The parent (summary) value in the hierarchy. Part of the primary key.
• RANGE_ATTRIBUTE — Qualifies how the parent associates to its children (for example, whether the relationship is defined by an explicit range or by an attribute condition). Part of the primary key.
• CHILD_FLEX_VALUE_LOW and CHILD_FLEX_VALUE_HIGH — The low and high boundaries of the child range under the parent. Both are part of the primary key.
• START_DATE_ACTIVE and END_DATE_ACTIVE — Effective dating that determines when a hierarchy row is considered active.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard FND audit ("Who" columns) tracking the creation and most recent modification of each hierarchy row.
• ZD_EDITION_NAME — The editioning column used by the EBS Online Patching (ADOP) architecture in 12.2.x to isolate edition-specific data.

The surrogate primary key is FND_FLEX_VALUE_NORM_HIER_PK, composed of FLEX_VALUE_SET_ID, PARENT_FLEX_VALUE, RANGE_ATTRIBUTE, CHILD_FLEX_VALUE_LOW, and CHILD_FLEX_VALUE_HIGH. A second unique index, FND_FLEX_VALUE_NORM_HIER_U1, repeats those columns plus ZD_EDITION_NAME and represents the true business-key candidate under editioning. The table carries thirteen documented columns in total.

Common Use Cases and Queries

Typical scenarios include reporting on parent-child expansion, validating that a requested child value falls within a defined parent range, and diagnosing why a flexfield hierarchy fails to roll up correctly after a value set change.

• Retrieve all child ranges for a given value set and parent:

SELECT parent_flex_value, child_flex_value_low, child_flex_value_high, range_attribute, start_date_active, end_date_active
FROM   applsys.fnd_flex_value_norm_hierarchy
WHERE  flex_value_set_id = :p_value_set_id
AND    parent_flex_value = :p_parent
AND    (end_date_active IS NULL OR end_date_active >= SYSDATE);

• Find the parent that owns a specific detail value by testing the range boundaries:

SELECT parent_flex_value
FROM   applsys.fnd_flex_value_norm_hierarchy
WHERE  flex_value_set_id = :p_value_set_id
AND    :p_child_value BETWEEN child_flex_value_low AND child_flex_value_high;

Join to FND_FLEX_VALUES to translate the internal value identifiers into the descriptive reports used by financial and reporting users.

Related Objects

• FND_FLEX_VALUE_SETS — The parent of this table; join on FND_FLEX_VALUE_NORM_HIERARCHY.FLEX_VALUE_SET_ID = FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID. This is the only documented foreign key relationship and defines the owning value set.
• FND_FLEX_VALUES and FND_FLEX_VALUES_TL — Hold the individual parent and child values referenced by PARENT_FLEX_VALUE, CHILD_FLEX_VALUE_LOW, and CHILD_FLEX_VALUE_HIGH, together with their translated descriptions.
• FND_FLEX_VALIDATION and the key flexfield hierarchy APIs — Consume these range definitions to validate and expand hierarchies at runtime.
• FND_FLEX_VALUE_HIERARCHIES — The companion definition table against which this range-based hierarchy is resolved.

Because the table is editioned and owned by APPLSYS, queries in a 12.2.x environment should account for ZD_EDITION_NAME and, where appropriate, run through the corresponding _VL or API views rather than accessing the base table directly.