Search Results range_attribute

Overview

The FII_DIM_NORM_HIERARCHY table is a core data structure within the Financial Intelligence (FII) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It is designed to store normalized, multi-level hierarchical relationships for financial dimension value sets, such as those used in the General Ledger (e.g., Account, Cost Center). Its primary role is to support the complex aggregation and drill-down reporting requirements of the FII data warehouse by providing a consistent, denormalized mapping between parent values and their child ranges or specific values. This enables efficient roll-up calculations and hierarchical analysis for financial and operational reporting.

Key Information Stored

The table's composite primary key defines the granularity of each hierarchical relationship record. The key columns are PARENT_FLEX_VALUE_SET_ID and PARENT_FLEX_VALUE, which identify the parent node in the hierarchy. The child members are defined by CHILD_FLEX_VALUE_SET_ID along with either a single value or a range. The RANGE_ATTRIBUTE column is a critical flag that dictates how the child values are interpreted. When RANGE_ATTRIBUTE is set to 'C', the CHILD_FLEX_VALUE_LOW and CHILD_FLEX_VALUE_HIGH columns define an inclusive range of child values. When set to 'P', these columns typically hold the same specific child value, indicating a direct parent-to-specific-child relationship. This dual-mode design allows the table to efficiently store both range-based and point-in-time hierarchical mappings essential for financial chart of accounts structures.

Common Use Cases and Queries

A primary use case is generating aggregated financial reports by rolling up transactional data from child segment values to parent summary values. For example, to summarize balances for a specific parent account value across a hierarchy, a query would join this table to fact tables on the child value. The following sample pattern retrieves all child values for a given parent:

• SELECT child_flex_value_low, child_flex_value_high, range_attribute
• FROM fii.fii_dim_norm_hierarchy
• WHERE parent_flex_value_set_id = :vs_id
• AND parent_flex_value = :parent_val
• ORDER BY child_flex_value_low;

Another critical scenario involves validating and analyzing hierarchy coverage by checking for child values that are not assigned to any parent or identifying overlapping ranges within the same value set and parent.

Related Objects

The table's primary key, FII_DIM_NORM_HIERARCHY_PK, is the central relationship for this object. It is directly referenced by foreign key constraints in other FII dimension and hierarchy staging or reporting tables to maintain referential integrity. While specific related table names are not listed in the provided metadata, it is foundational to the FII dimensional model. It typically joins to value set tables (like FND_FLEX_VALUE_SETS) via the *_FLEX_VALUE_SET_ID columns and to dimension tables (like FII_DIM_dimension_name) on the child or parent flex value columns to enrich reporting metadata. Its structure is populated and maintained by FII's hierarchy synchronization programs from the source EBS flexfield hierarchies.