Search Results cn_hierarchy_edges_all




Overview
Explore in AI Chat

Overview

The CN_HIERARCHY_EDGES_ALL table is a core data structure within the Oracle E-Business Suite Incentive Compensation (CN) module, versions 12.1.1 and 12.2.2. It serves as the definitive repository for storing the parent-child relationships that define organizational hierarchies. These hierarchies are fundamental to the module's operation, enabling the modeling of sales territories, reporting structures, and compensation roll-ups. The table's primary role is to map the logical connections between nodes, thereby allowing the system to traverse organizational structures for purposes such as quota assignment, credit distribution, and performance reporting across multiple operating units.

Key Information Stored

The table's structure is designed to explicitly define hierarchical links. While the full column list is not detailed in the provided metadata, the documented foreign key relationships reveal its most critical columns. The HIERARCHY_API_ID column identifies the specific hierarchy definition to which the edge belongs, linking it to the CN_HIERARCHIES_API_ALL table. The VALUE_ID column stores the identifier for the child node in the relationship, and the PARENT_VALUE_ID column stores the identifier for the parent node. Both of these columns reference the CN_HIERARCHY_NODES_ALL table, which holds the actual entities (like salespeople or positions) represented by the nodes. Together, these columns form the essential "from-to" mapping that constructs the hierarchy.

Common Use Cases and Queries

This table is central to any process requiring hierarchy traversal. A common use case is rolling up transactional credits from individual contributors through their management chain. For reporting, it is frequently used to generate organizational charts or to aggregate metrics by management level. A typical query pattern involves recursive SQL or hierarchical queries (using CONNECT BY or recursive WITH clauses) starting from a specific node. For example, to find all descendants of a particular manager, one would start with that manager's node ID as the PARENT_VALUE_ID and recursively join through the table. Conversely, to find the complete ancestry of a salesperson, queries start with their VALUE_ID and follow the PARENT_VALUE_ID links upward.

Related Objects

The CN_HIERARCHY_EDGES_ALL table maintains strict referential integrity with other key hierarchy tables, as documented by its foreign keys. The primary relationships are:

  • CN_HIERARCHIES_API_ALL: Joined via the HIERARCHY_API_ID column. This table defines the master hierarchy object, including its name, type, and effective dates.
  • CN_HIERARCHY_NODES_ALL: Joined twice. The VALUE_ID column references the child node's record, while the PARENT_VALUE_ID column references the parent node's record in this same table. This table stores the actual entities (e.g., employee, position) that populate the hierarchy.
These relationships form a closed loop: a Hierarchy (API) contains Nodes, and Edges define the directional links between those Nodes.