Search Results hierarchy_api_id

Overview

The CN_HIERARCHIES_API_ALL table is a core interface table within the Oracle E-Business Suite Incentive Compensation module (CN). Its primary function is to serve as a staging area for the bulk upload and download of organizational hierarchy data. In the context of incentive management, accurate and up-to-date reporting hierarchies are critical for calculating commissions, assigning quotas, and determining roll-up credit. This table acts as the programmatic entry point for integrating hierarchy data from external Human Resources (HR) or other enterprise systems into the CN module's internal structures, ensuring compensation plans align with the current organizational chart.

Key Information Stored

While the provided metadata does not list specific columns, the structure of an API interface table in this context typically includes key fields to manage the data load process and define the hierarchy. Based on its described purpose and primary key, the table is centered around the HIERARCHY_API_ID, a unique identifier for each hierarchy record being processed. Expected columns would include data defining the hierarchy itself—such as PARENT_RESOURCE_ID and CHILD_RESOURCE_ID to represent reporting relationships—along with effective start and end dates. Crucially, it will contain status and error message columns (e.g., PROCESS_STATUS, ERROR_MESSAGE) to track the success or failure of the concurrent program that validates and transfers the data from this interface table into the base application tables.

Common Use Cases and Queries

The most common use case is the execution of a hierarchy import concurrent request. Data is inserted into CN_HIERARCHIES_API_ALL via a custom interface or data load, after which a standard CN program is run to validate relationships and populate the production tables. A typical diagnostic query would check for records that failed processing:

• SELECT hierarchy_api_id, error_message FROM cn_hierarchies_api_all WHERE process_status = 'ERROR';

Another common operation is to identify pending interface records before submission: SELECT COUNT(*) FROM cn_hierarchies_api_all WHERE process_status IS NULL;. For auditing, one might join this table to the base hierarchy tables to trace the source of a loaded relationship.

Related Objects

This interface table has a direct and documented relationship with the core hierarchy storage table. As per the provided metadata:

• CN_HIERARCHY_EDGES_ALL: This table stores the validated, application-ready hierarchy relationships. It references CN_HIERARCHIES_API_ALL via the foreign key column HIERARCHY_API_ID, which points to the primary key of the interface table. This link allows the system to trace which interface record was the source for each production hierarchy edge, providing crucial auditability for the data load process.

The existence of this foreign key confirms that CN_HIERARCHIES_API_ALL is the authoritative source for hierarchy data during the upload lifecycle, with CN_HIERARCHY_EDGES_ALL being the primary consumer of its successfully processed records.