Search Results cn_head_hierarchies_all_b_pk

Overview

The CN_HEAD_HIERARCHIES_ALL_B table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module. It serves as the master repository for dimension hierarchies, which are fundamental structures for modeling sales territories, organizational roll-ups, and other multi-level groupings used in compensation plans. These hierarchies define the parent-child relationships between dimension members, enabling the calculation and roll-up of commissions, quotas, and performance metrics across an organization. The table supports multi-organization (Multi-Org) architecture, as indicated by the ORG_ID column, allowing hierarchies to be partitioned by operating unit.

Key Information Stored

The table stores metadata that defines the structure and properties of each hierarchy. The primary columns, as indicated by the provided metadata, include the HEAD_HIERARCHY_ID, which is the unique system-generated identifier and primary key for a hierarchy definition. The DIMENSION_ID is a critical foreign key that links the hierarchy to its base dimension (e.g., Organization, Resource, Product) in the CN_DIMENSIONS_ALL_B table. The ORG_ID column specifies the operating unit for the hierarchy, forming a unique key constraint in conjunction with the HEAD_HIERARCHY_ID. While the excerpt does not list all columns, typical attributes stored would include hierarchy name, description, effective start and end dates, status, and versioning information to manage changes over time.

Common Use Cases and Queries

This table is central to configuring and reporting on incentive compensation structures. A primary use case is the setup and maintenance of sales territory hierarchies, where managers are rolled up to directors and regions. Analysts query this table to audit hierarchy definitions or to generate lists of active hierarchies for a given dimension. Common SQL patterns include joining to the dimension table to retrieve hierarchy context and filtering by ORG_ID for multi-org reporting. For example, a query to list all hierarchies for the 'Organization' dimension in a specific operating unit would join CN_HEAD_HIERARCHIES_ALL_B (HH) to CN_DIMENSIONS_ALL_B (DIM) on HH.DIMENSION_ID = DIM.DIMENSION_ID, filtering by DIM.NAME and HH.ORG_ID.

Related Objects

The table has documented relationships with several key objects in the Incentive Compensation schema. Its structure is defined by the following primary and unique keys: CN_HEAD_HIERARCHIES_ALL_B_PK (HEAD_HIERARCHY_ID) and CN_HEAD_HIERARCHIES_ALL_B_UK (HEAD_HIERARCHY_ID, ORG_ID). As per the provided foreign key data, it has a direct dependency on the CN_DIMENSIONS_ALL_B table via the DIMENSION_ID column. This relationship ensures every hierarchy is associated with a valid base dimension. In a full implementation, this table is also the parent to several critical child tables, such as CN_HIERARCHY_NODES_ALL_B (which stores the individual members and their parentage within a hierarchy) and CN_QUOTA_RULES_ALL (which may assign quotas to hierarchy levels), though these specific relationships are not detailed in the provided excerpt.