Search Results ancestor_id

Overview

The CN_DIM_EXPLOSION_ALL table is a core data structure within the Oracle E-Business Suite Incentive Compensation (CN) module. Its primary function is to denormalize hierarchical relationships, specifically the ancestral lineage between nodes within a dimension hierarchy. This design serves as a performance optimization, pre-calculating and storing all parent-child relationships to enable rapid querying and reporting on hierarchical data without requiring complex recursive SQL operations at runtime. The table is central to the processing of compensation plans, territory assignments, and quota management, where understanding the roll-up from individual contributors to higher-level managers or organizational units is critical for accurate calculations and analytics.

Key Information Stored

The table's structure is defined by its composite primary key, which consists of three columns. The VALUE_ID column identifies a specific node within a hierarchy, such as a salesperson or a product. The ANCESTOR_ID column stores the identifier for a node that is an ancestor (a parent, grandparent, etc.) of the VALUE_ID node. The DIM_HIERARCHY_ID column specifies the unique identifier for the dimension hierarchy to which both nodes belong, allowing the system to manage multiple independent hierarchies. Each row effectively represents a single ancestral link, meaning a single VALUE_ID will have multiple rows for each of its ancestors up to the hierarchy's root.

Common Use Cases and Queries

A primary use case is aggregating performance metrics, such as sales or commissions, from lower-level nodes up through their management chain. For example, to find all contributors rolling up to a specific manager, one would query for rows where that manager's node ID is the ANCESTOR_ID. Conversely, to find all managers above a specific contributor, one would query for rows where the contributor's node ID is the VALUE_ID. Common SQL patterns include using the table in JOIN operations to filter or aggregate transactional data based on hierarchical relationships. A typical query to find all transactions for a manager's entire team would join the transaction table on VALUE_ID and filter the result set where ANCESTOR_ID equals the manager's node identifier.

Related Objects

The table maintains direct foreign key relationships with the CN_HIERARCHY_NODES_ALL table, which stores the master definition of all hierarchy nodes. Two distinct foreign keys exist:

• CN_DIM_EXPLOSION_ALL.VALUE_ID references CN_HIERARCHY_NODES_ALL
• CN_DIM_EXPLOSION_ALL.ANCESTOR_ID references CN_HIERARCHY_NODES_ALL

This means both the child node (VALUE_ID) and its ancestor node (ANCESTOR_ID) must be valid, defined records in the CN_HIERARCHY_NODES_ALL table. The table is integral to the Incentive Compensation engine and is likely referenced by various internal APIs, seed data scripts, and standard reports for territory and quota management.