Search Results fnd_flex_value_norm_hierarchy

Overview

The FND_FLEX_VALUE_NORM_HIERARCHY table is a core data object within the Application Object Library (FND) of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the primary repository for defining and storing multiple-level, parent-child hierarchical relationships between values within a flexfield value set. This structure is fundamental for enabling hierarchical reporting, security rules, and data roll-ups across various EBS modules, such as General Ledger for account segment hierarchies or Inventory for item categories. The table is owned by the APPLSYS schema and is critical for the proper functioning of the flexfield architecture, which underpins the application's key flexfields and descriptive flexfields.

Key Information Stored

The table's structure is designed to capture hierarchical relationships with precision. Its primary key uniquely identifies a hierarchical link through a combination of five columns: FLEX_VALUE_SET_ID, PARENT_FLEX_VALUE, RANGE_ATTRIBUTE, CHILD_FLEX_VALUE_LOW, and CHILD_FLEX_VALUE_HIGH. The FLEX_VALUE_SET_ID links the hierarchy to a specific value set. The PARENT_FLEX_VALUE column stores the parent value in the relationship. The RANGE_ATTRIBUTE indicates whether the child is a single value or a range, with the CHILD_FLEX_VALUE_LOW and CHILD_FLEX_VALUE_HIGH columns defining either the specific child value or the lower and upper bounds of a child value range, respectively. This design efficiently supports both simple parent-child links and parent-to-range relationships.

Common Use Cases and Queries

A primary use case is generating hierarchical reports, such as a summarized trial balance by parent account or a roll-up of item categories. Administrators also use this data to configure value security rules that restrict user access based on hierarchical positions. A common analytical query involves using hierarchical SQL (CONNECT BY) to traverse the tree. For example, to find all descendants of a parent value '1000' in value set ID 101, one might use: SELECT child_flex_value_low FROM fnd_flex_value_norm_hierarchy WHERE flex_value_set_id = 101 START WITH parent_flex_value = '1000' CONNECT BY PRIOR child_flex_value_low = parent_flex_value; Another frequent operation is validating the existence of a hierarchy for reporting or a custom program, often joining to FND_FLEX_VALUE_SETS for the value set name.

Related Objects

As per the provided metadata, the most critical relationship is a foreign key to the FND_FLEX_VALUE_SETS table. The join is made on the FLEX_VALUE_SET_ID column, ensuring that every hierarchy record is associated with a valid, defined value set. This enforces referential integrity. While not listed in the provided excerpt, this table is intrinsically linked to the FND_FLEX_VALUES table, which stores the actual value details. In practice, queries often join FND_FLEX_VALUE_NORM_HIERARCHY to both FND_FLEX_VALUE_SETS (for set metadata) and FND_FLEX_VALUES (for child/parent value descriptions). The table is also central to APIs and views like FND_FLEX_VALUE_HIERARCHIES that provide a more accessible interface for hierarchy data.