Search Results fnd_flex_value_hierarchies

Overview

The FND_FLEX_VALUE_HIERARCHIES 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 a critical function in the management of key flexfield segment values by defining hierarchical parent-child relationships between them. Specifically, this table stores child value ranges for key flexfield segment values, enabling the creation of structured, multi-level hierarchies within a flexfield value set. This capability is fundamental for implementing reporting trees, summary accounts, and roll-up structures in modules such as General Ledger (where it is used for account hierarchies) and Inventory (for item categories). The table is owned by the APPLSYS schema and is essential for the proper functioning of flexfield validation and reporting.

Key Information Stored

The table's primary key uniquely identifies a hierarchical relationship and consists of four columns: FLEX_VALUE_SET_ID, PARENT_FLEX_VALUE, CHILD_FLEX_VALUE_LOW, and CHILD_FLEX_VALUE_HIGH. The FLEX_VALUE_SET_ID is a foreign key linking to FND_FLEX_VALUE_SETS, defining the specific value set in which the hierarchy exists. The PARENT_FLEX_VALUE column holds the parent segment value. The CHILD_FLEX_VALUE_LOW and CHILD_FLEX_VALUE_HIGH columns define an inclusive range of child values that roll up to the specified parent. This range-based design allows a single parent record to represent multiple child values efficiently, which is particularly useful for numeric account ranges in financial reporting.

Common Use Cases and Queries

A primary use case is generating financial reports that require account roll-ups, such as an Income Statement. System administrators and developers query this table to audit, maintain, or troubleshoot flexfield hierarchies. Common SQL patterns include identifying all children for a specific parent value or validating the existence of a hierarchy for a value set. For example, to find all child ranges for a parent account '01-000' in value set ID 101, one might use:
SELECT child_flex_value_low, child_flex_value_high FROM applsys.fnd_flex_value_hierarchies WHERE flex_value_set_id = 101 AND parent_flex_value = '01-000';
Another critical use is in conjunction with the FND_FLEX_VAL_HIERARCHIES view for reporting, as it provides a more user-friendly interface to the hierarchical data. Data fixes often involve inserting or updating records in this table to correct roll-up paths, which must be done with caution to maintain data integrity.

Related Objects

The most direct dependency is the foreign key relationship with the FND_FLEX_VALUE_SETS table, which defines the master list of value sets. The table is also intrinsically linked to FND_FLEX_VALUES, which stores the individual valid segment values that are referenced as parents or within the child ranges. For reporting and application logic, the view FND_FLEX_VAL_HIERARCHIES is commonly used as it presents a denormalized and more accessible version of the hierarchy data. Key flexfield validation APIs and functions within the FND_FLEX_KEYVAL and FND_FLEX_VALUE packages rely on the relationships stored in FND_FLEX_VALUE_HIERARCHIES to perform parent-child validation and roll-up calculations.