Search Results cn_calc_ext_tables_pk

Overview

CN_CALC_EXT_TABLES_ALL is a transactional configuration table in the Oracle Incentive Compensation (CN) module. It stores the definition and registration metadata for external tables that participate in compensation calculation. Each row describes the relationship between a customer-defined external table and one of the internal tables available to the Oracle Incentive Compensation calculation engine (referred to in the base documentation as "OSC tables"). In practice, this object is the master registry that tells the calculation engine where to find external data, how to reference it through an alias, and how to map it against internal structures.

The table resides in the CN schema and is flagged VALID in the ETRM dictionary for both EBS 12.1.1 and 12.2.2. It is a multi-org table: ORG_ID is present and participates in both unique indexes, so definitions are scoped per operating unit. The documented physical schema contains 33 columns, including the standard WHO audit columns and a set of 15 developer-definable ATTRIBUTE columns. A SECURITY_GROUP_ID column provides a foreign key to FND_SECURITY_GROUPS, supporting row-level security partitioning in deployments that use security groups.

Applying a Data Vault classification heuristic to the foreign-key topology, this object displays hub-leaning characteristics. Its primary key, CALC_EXT_TABLE_ID, serves as the identity anchor for external table registrations, while dependent detail and mapping records reference it. Analysts modeling the CN schema within a Data Vault framework may therefore treat CN_CALC_EXT_TABLES_ALL as a hub from which link and satellite structures can be derived.

Key Information Stored

The primary key is the surrogate CALC_EXT_TABLE_ID, enforced by the constraint CN_CALC_EXT_TABLES_PK. Two unique indexes further constrain the business identity of a row:

• CN_CALC_EXT_TABLES_U1 on (CALC_EXT_TABLE_ID, ORG_ID) — confirms per-operating-unit uniqueness of the surrogate identifier.
• CN_CALC_EXT_TABLES_U2 on (ORG_ID, ALIAS) — establishes ALIAS as the human-readable business key within an operating unit.

The most operationally significant columns include:

• CALC_EXT_TABLE_ID — surrogate primary key and the join point for all dependent tables.
• ORG_ID — operating unit that owns the registration; joins to organizational hierarchy structures.
• ALIAS — the logical name by which the calculation engine refers to the external table.
• NAME and DESCRIPTION — descriptive identification of the external table registration.
• INTERNAL_TABLE_ID and EXTERNAL_TABLE_ID — identifiers linking the external definition to its internal counterpart and to the physical external source.
• SCHEMA and EXTERNAL_TABLE_NAME — database schema and physical object name of the external table.
• USED_FLAG — indicates whether the definition is actively referenced by the calculation engine.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enabling security-group isolation.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield columns for extension data.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard WHO audit and optimistic locking columns.

Common Use Cases and Queries

Typical uses center on validating external table registrations and troubleshooting calculation failures caused by misconfigured mappings. A common diagnostic query retrieves active registrations for an operating unit:

• SELECT calc_ext_table_id, name, alias, schema, external_table_name FROM cn.cn_calc_ext_tables_all WHERE org_id = :p_org_id AND used_flag = 'Y';
• Join to CN_CALC_EXT_TBL_DTLS_ALL on CALC_EXT_TABLE_ID to inspect column-level detail for a registration.
• Join to CN_COLUMN_MAPS_ALL on CALC_EXT_TABLE_ID to review the mapping between external and internal columns prior to running a calculation batch.
• Filter by SCHEMA when auditing which database accounts hold referenced external tables for migration or cloning exercises.

Because this is a multi-org table, reporting queries should generally include ORG_ID predicates. Extraction routines should also respect SECURITY_GROUP_ID for row-level security compatibility.

Related Objects

• CN_CALC_EXT_TBL_DTLS_ALL — child detail table; joins on CALC_EXT_TABLE_ID, providing granular column definitions for each external table registration.
• CN_COLUMN_MAPS_ALL — references CALC_EXT_TABLE_ID and stores the column mapping between external and internal tables used by the calculation engine.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID, providing security-group classification for each registration.

These three objects form the immediate relational neighborhood of CN_CALC_EXT_TABLES_ALL and are the primary sources for validating, reporting on, or troubleshooting external table integrations in Oracle Incentive Compensation.