Results for “dimension_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CN_HEAD_HIERARCHIES_ALL_B is a base table in the CN (Incentive Compensation) schema of Oracle E-Business Suite. It stores the definition of dimension hierarchies used by the Incentive Compensation engine to organize and aggregate transactional data — such as sales credits, revenue, and quota — along analytical axes (for example, organization, product, customer, or geography). Each row represents a hierarchy header that binds a specific hierarchy to a dimension, providing the structural scaffold against which hierarchy detail lines and rollup relationships are defined.

The table is a multi-org (_ALL) object; the presence of ORG_ID indicates rows are partitioned by operating unit or business group, controlling which hierarchies are visible and usable within a given organization context. Under a heuristic Data Vault classification, this object is satellite-leaning: it holds descriptive attributes and foreign-key references attached to a dimension rather than acting as a pure hub or an associative link.

Key Information Stored

The table contains twelve documented columns. The most significant are:

• HEAD_HIERARCHY_ID — surrogate primary key (CN_HEAD_HIERARCHIES_ALL_B_PK) uniquely identifying each hierarchy header. It also participates in the unique index CN_HEAD_HIERARCHIES_ALL_B_U1, confirming its role as the principal business-key candidate.
• DIMENSION_ID — foreign key to CN_DIMENSIONS_ALL_B, identifying the dimension to which this hierarchy belongs.
• ORG_ID — operating unit / organization identifier, driving multi-org data security and partitioning.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing row-level access.
• DESCRIPTION — user-facing name or description of the hierarchy.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J framework.
• ZD_EDITION_NAME — edition-based redefinition column supporting online patching in EBS 12.2.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

The unique index CN_HEAD_HIERARCHIES_ALL_B_U1 (HEAD_HIERARCHY_ID, ORG_ID, ZD_EDITION_NAME) reflects the composite business key, while the secondary unique constraint CN_HEAD_HIERARCHIES_ALL_B_UK reinforces identity across org and edition.

Common Use Cases and Queries

Typical usage centers on inspection, troubleshooting, and reporting of hierarchy configuration. Analysts frequently list hierarchies for a dimension or confirm which hierarchies are active within an operating unit.

Example: retrieve all hierarchies for a specific dimension and org.

• SELECT h.HEAD_HIERARCHY_ID, h.DESCRIPTION
• FROM CN_HEAD_HIERARCHIES_ALL_B h
• WHERE h.DIMENSION_ID = :dimension_id AND h.ORG_ID = :org_id;

Example: cross-reference to dimensions and security groups:

• SELECT h.HEAD_HIERARCHY_ID, d.DIMENSION_NAME, h.DESCRIPTION
• FROM CN_HEAD_HIERARCHIES_ALL_B h, CN_DIMENSIONS_ALL_B d
• WHERE h.DIMENSION_ID = d.DIMENSION_ID;

Reportable scenarios include auditing hierarchy-to-dimension assignments, validating org-level visibility, and supporting integration extracts of hierarchy definitions for downstream BI or analytics platforms. Queries should always filter on ORG_ID and, in R12.2, on ZD_EDITION_NAME to avoid returning stale edition rows.

Related Objects

The following objects are significant to the hierarchy header's lifecycle and query patterns:

• CN_DIMENSIONS_ALL_B — joined via DIMENSION_ID; the parent dimension definition each hierarchy belongs to.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; drives row-level access.
• CN_HEAD_HIERARCHIES_ALL_TL — translation table holding language-specific descriptions where applicable.
• Hierarchy detail / member tables in the CN schema — reference HEAD_HIERARCHY_ID to define rollup structure and member assignments.
• CN_DIMENSIONS_ALL_TL — translated dimension descriptions for reporting joins.
• Incentive Compensation setup and hierarchy APIs (FND_/CN_ concurrent programs and OAF pages) — create and maintain hierarchy records through supported interfaces rather than direct DML.

Direct updates should be avoided; use supported Incentive Compensation functionality to preserve integrity of the header, detail, and rollup relationships.