Results for “unique_value”

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

Overview

CN_HIERARCHIES_API_ALL is the interface (staging) table for Oracle Incentive Compensation (CN) used to upload and download compensation hierarchies. It acts as the inbound/outbound funnel through which hierarchy definitions—typically sourced from external files, flat-file loads, or third-party systems—are validated and then promoted into the operational hierarchy structures. Rather than holding the "live" hierarchy tree used at runtime, this table holds the batch representation that the concurrent hierarchy load processes consume and transform. Because it also supports downloading, it can be populated by EBS processes for extraction and reconciliation against external systems.

Under a Data Vault modeling heuristic mined from its foreign key structure, CN_HIERARCHIES_API_ALL classifies as hub-leaning, suggesting that it is best treated as a business hub of hierarchy identities—each row keyed by a surrogate HIERARCHY_API_ID and anchored to a business key of (HIERARCHY_API_ID, ORG_ID). The associated edge table behaves as the satellite/link layer that expands each hub record into its member relationships. This classification is a modeling suggestion, not a functional constraint of the EBS data model.

Key Information Stored

The table retains a 19-column physical schema in the CN schema. The most consequential columns include:

• HIERARCHY_API_ID — the surrogate primary key (CN_HIERARCHIES_API_PK) that uniquely identifies each interface hierarchy record and is propagated as the foreign key into CN_HIERARCHY_EDGES_ALL.
• ORG_ID — the operating unit / organization context; combined with HIERARCHY_API_ID it forms the unique business key candidate (CN_HIERARCHIES_API_U1).
• DIMENSION — the hierarchy dimension (for example, a sales or resource dimension) being defined.
• HEADER_HIER_NAME — the header-level hierarchy name that labels the interface grouping.
• START_PERIOD_NAME / END_PERIOD_NAME — the effective period window covered by the hierarchy definition.
• DIMENSION_VALUE / UNIQUE_VALUE — the member's dimension value and its unique identifier within the dimension.
• ANCESTOR_DIM_VALUE / ANCESTOR_UNIQUE_VALUE — the parent/ancestor member identifiers, defining the upward edge reference.
• LOADING_STATUS — the processing state of each row (loaded, failed, pending), the primary operational control column.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the date range during which the member relationship is effective.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant/security-group segregation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

The dominant scenario is the staged load of hierarchy data. A loader inserts rows into CN_HIERARCHIES_API_ALL and the corresponding edges into CN_HIERARCHY_EDGES_ALL, then runs the hierarchy import concurrent program, which reads LOADING_STATUS to determine which records to process. A second scenario is diagnostic reconciliation: identifying rows that failed validation.

A typical verification query:

• SELECT h.HIERARCHY_API_ID, h.HEADER_HIER_NAME, h.DIMENSION, h.LOADING_STATUS, COUNT(e.HIERARCHY_API_ID) edge_count FROM CN_HIERARCHIES_API_ALL h LEFT JOIN CN_HIERARCHY_EDGES_ALL e ON e.HIERARCHY_API_ID = h.HIERARCHY_API_ID WHERE h.ORG_ID = :org_id GROUP BY ...;
• To surface load failures: SELECT * FROM CN_HIERARCHIES_API_ALL WHERE LOADING_STATUS = 'ERROR' AND ORG_ID = :org_id;
• Period-scoped extraction: filter on START_PERIOD_NAME and END_PERIOD_NAME for coverage reporting.
• Security-group audit: join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID.

Related Objects

• CN_HIERARCHY_EDGES_ALL — the primary dependent table; its HIERARCHY_API_ID foreign key references this table, holding each member/ancestor edge.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for security-group partitioning.
• CN_HIERARCHIES_API_PK — the primary key constraint on HIERARCHY_API_ID.
• CN_HIERARCHIES_API_U1 — the unique index on (HIERARCHY_API_ID, ORG_ID), the documented business-key candidate.
• Incentive Compensation hierarchy import/download concurrent programs — the processes that consume and populate this interface table.
• Operational CN hierarchy tables populated downstream after successful interface processing (via the CN hierarchy load APIs).