Search Results cn_hierarchy_nodes_all




Overview
Explore in AI Chat

Overview

The CN_HIERARCHY_NODES_ALL table is a core data structure within the Oracle E-Business Suite Incentive Compensation (CN) module. It serves as the master repository for all nodes that constitute the various hierarchical structures used in the application. In the context of Incentive Compensation, hierarchies are fundamental for modeling organizational reporting structures, sales territories, product roll-ups, and other dimensional relationships critical for calculating and attributing commissions. This table stores the individual entities—such as employees, positions, or custom dimension values—that act as the building blocks for these hierarchies, enabling the system to define parent-child relationships and perform roll-up calculations for incentive plans.

Key Information Stored

While the provided metadata does not list specific columns, the table's primary key and foreign key relationships reveal its essential structure. The central column is VALUE_ID, which serves as the unique identifier (primary key) for each node in any hierarchy. This ID typically references a specific record in a dimension table, such as a salesperson or a territory. Other inferred columns would include data to define the node's context and attributes, such as a hierarchy identifier to distinguish between different hierarchy types (e.g., organization vs. territory), the effective start and end dates for the node's validity, and descriptive information. The multi-org structure is supported, as indicated by the "_ALL" suffix, meaning the table stores data for all operating units.

Common Use Cases and Queries

This table is primarily accessed to query the list of valid entities within a compensation hierarchy or to resolve hierarchical relationships via joins to related tables. A common reporting use case is to generate a list of all sales representatives (nodes) within a specific managerial hierarchy for a compensation statement. A typical SQL pattern involves joining CN_HIERARCHY_NODES_ALL to CN_HIERARCHY_EDGES_ALL to traverse parent-child links. For example, to find all child nodes under a specific manager, one might query: SELECT child.VALUE_ID FROM CN_HIERARCHY_NODES_ALL parent, CN_HIERARCHY_EDGES_ALL edge, CN_HIERARCHY_NODES_ALL child WHERE parent.VALUE_ID = :p_manager_id AND edge.PARENT_VALUE_ID = parent.VALUE_ID AND child.VALUE_ID = edge.VALUE_ID. This table is also crucial for backend processes that explode hierarchy data for performance calculation engines.

Related Objects

The CN_HIERARCHY_NODES_ALL table has integral relationships with other key tables in the CN schema, as documented by its foreign keys:

  • CN_HIERARCHY_EDGES_ALL: This table defines the relationships between nodes. It joins to CN_HIERARCHY_NODES_ALL twice: once on VALUE_ID (the child node) and once on PARENT_VALUE_ID (the parent node).
  • CN_DIM_EXPLOSION_ALL: This table stores pre-calculated hierarchical explosions for performance. It references CN_HIERARCHY_NODES_ALL on both VALUE_ID (a node) and ANCESTOR_ID (an ancestor of that node in the hierarchy).

These relationships form the backbone of the hierarchy management system within Incentive Compensation, enabling efficient storage and retrieval of complex reporting structures.