Search Results child_flex_value_set_id

Overview

The FII_DIM_NORM_HIERARCHY table is a core data repository within the Oracle E-Business Suite (EBS) Financial Intelligence (FII) architecture, specifically for versions 12.1.1 and 12.2.2. Its primary function is to store normalized, multi-level hierarchical relationships between flexfield values. This table is essential for enabling dimensional analysis and roll-up reporting in FII, as it defines the parent-child relationships that organize financial data, such as account, cost center, or product hierarchies, across different value sets. It serves as the normalized backbone for hierarchical data, separating the structural relationships from the descriptive value definitions themselves.

Key Information Stored

The table's structure is designed to capture hierarchical links with precision. The key columns include PARENT_FLEX_VALUE_SET_ID and PARENT_FLEX_VALUE, which identify the parent node in the relationship. The counterpart, CHILD_FLEX_VALUE_SET_ID, is a critical column that identifies the value set to which the child member(s) belong. This allows for hierarchies that span different value sets. The child members are defined either as a single value or a range, using CHILD_FLEX_VALUE_LOW and CHILD_FLEX_VALUE_HIGH. The RANGE_ATTRIBUTE column acts as a flag to distinguish between a single child value and a range of values. The table's primary key is a composite of all six of these columns (PARENT_FLEX_VALUE_SET_ID, PARENT_FLEX_VALUE, CHILD_FLEX_VALUE_SET_ID, CHILD_FLEX_VALUE_LOW, CHILD_FLEX_VALUE_HIGH, RANGE_ATTRIBUTE), ensuring each hierarchical link is uniquely defined.

Common Use Cases and Queries

A primary use case is traversing a financial dimension's hierarchy to support aggregated reporting, such as rolling up detailed general ledger account balances to a summary parent account. Developers and report writers query this table to resolve reporting tree structures. A common query pattern involves joining this table to the flexfield value tables (like FND_FLEX_VALUES) on both the parent and child value set and value columns to retrieve meaningful descriptions. For example, to find all immediate children for a specific parent value, one might use:

• SELECT child_flex_value_low, child_flex_value_high, range_attribute
• FROM fii.fii_dim_norm_hierarchy
• WHERE parent_flex_value_set_id = :p_vset_id
• AND parent_flex_value = :p_parent_val
• AND child_flex_value_set_id = :c_vset_id;

Another critical scenario is validating or analyzing hierarchical data integrity, particularly when the CHILD_FLEX_VALUE_SET_ID differs from the PARENT_FLEX_VALUE_SET_ID, indicating a cross-value-set relationship.

Related Objects

As per the provided metadata, the FII_DIM_NORM_HIERARCHY table does not have foreign key dependencies on other objects, but it is referenced by the APPS synonym FII_DIM_NORM_HIERARCHY. This synonym is the standard access point for all application code within the EBS environment. The table's primary key, FII_DIM_NORM_HIERARCHY_PK, enforces the uniqueness of the hierarchical relationships. In practice, this table is closely related to the FND_FLEX_VALUE_SETS and FND_FLEX_VALUES tables, which store the definition of the value sets and the individual value details, respectively. Joins are typically performed on PARENT_FLEX_VALUE_SET_ID to FND_FLEX_VALUE_SETS.value_set_id and on CHILD_FLEX_VALUE_SET_ID to the same table to resolve set names.