Results for “cn_comp_plans_all”

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

Overview

CN_COMP_PLANS_ALL is the master (multi-org) table that stores individual compensation plans in Oracle EBS Incentive Compensation (CN). Each row represents a single plan definition — the business object that groups quotas, roles, participants, calculation rules, and worksheets into a single commission or bonus arrangement. In Oracle EBS 12.1.1 and 12.2.2 the table resides in the CN schema, is registered as VALID, and carries 37 documented columns at the 12.2.2 schema level.

Because the table stores the plan header and is referenced by numerous transactional and assignment tables, its heuristic Data Vault classification is hub-leaning. In a dimensional or Data Vault model it is best treated as a hub of the compensation plan business key (COMP_PLAN_ID), with the descriptive and status attributes behaving as satellite payload. Quota assignments, role mappings, worksheet bonus detail, and calculation validations all attach to this hub through COMP_PLAN_ID foreign keys, making the plan the natural grain anchor for reporting and integration.

Key Information Stored

The primary key is the surrogate CN_COMP_PLANS_PK, defined on COMP_PLAN_ID. Two unique indexes act as business-key candidates: CN_COMP_PLANS_U1 (COMP_PLAN_ID, ORG_ID) and CN_COMP_PLANS_U2 (NAME, ORG_ID). The organization column ORG_ID qualifies plans per operating unit, so plan names are unique only within an operating unit context.

• COMP_PLAN_ID — surrogate primary key; the join column used by all dependent tables.
• NAME and VERSION — plan identifier and version label; together with ORG_ID they form the human-readable business key.
• DESCRIPTION — free-text description of the plan.
• START_PERIOD_ID and END_PERIOD_ID — the effective period range (typically referencing the CN/GL period framework) during which the plan is active.
• START_DATE and END_DATE — explicit date boundaries for the plan lifecycle.
• STATUS_CODE — plan status (e.g., active, inactive, incomplete).
• COMPLETE_FLAG — indicates whether the plan setup has been finalized.
• ON_QUOTA_DATE — the date on which the plan is placed on quota.
• ALLOW_REV_CLASS_OVERLAP — flag controlling whether overlapping revenue classes are permitted.
• SUM_TRX_FLAG — flag governing transaction summarization behavior.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing data security.
• ORG_ID — operating unit / multi-org discriminator.
• OBJECT_VERSION_NUMBER — optimistic locking for concurrent updates.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS DFF flexfield block for client-specific extensions.
• WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — audit trail.

Common Use Cases and Queries

Typical reporting needs include listing all active plans for an operating unit, auditing plan completeness, and joining plan headers to their quota and participant assignments. A representative query:

• SELECT comp_plan_id, name, version, status_code, start_date, end_date FROM cn.cn_comp_plans_all WHERE org_id = :p_org_id ORDER BY name;
• Join plans to quota assignments: SELECT p.name, q.* FROM cn.cn_comp_plans_all p, cn.cn_quota_assigns_all q WHERE p.comp_plan_id = q.comp_plan_id;
• Join plans to role/participant mappings via cn_role_plans_all or srp plan assignments via cn_srp_plan_assigns_all on COMP_PLAN_ID to derive which roles or sales representatives are covered.
• Validation and reconciliation: query cn_calc_sub_validations_all and cn_worksheet_bonuses_all filtered by status_code or complete_flag to confirm plan setup before calculation runs.
• Security-aware reporting: restrict results using SECURITY_GROUP_ID against FND_SECURITY_GROUPS.

Because the table is multi-org, always filter on ORG_ID for operational reporting, and prefer the _U1/_U2 unique keys when resolving a plan by identifier and organization.

Related Objects

The following tables depend on CN_COMP_PLANS_ALL through the COMP_PLAN_ID foreign key and are the most significant for downstream reporting and integration:

• CN_QUOTA_ASSIGNS_ALL — quota assignments per plan (COMP_PLAN_ID).
• CN_ROLE_PLANS_ALL — role-to-plan mappings (COMP_PLAN_ID).
• CN_SRP_PLAN_ASSIGNS_ALL — sales representative plan assignments (COMP_PLAN_ID).
• CN_WORKSHEET_QG_DTLS_ALL — worksheet quota/detail records tied to the plan.
• CN_WORKSHEET_BONUSES_ALL — worksheet bonus records per plan.
• CN_CALC_SUB_VALIDATIONS_ALL — calculation sub-validation output keyed by plan.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for data access control.

Collectively these relationships confirm CN_COMP_PLANS_ALL as the central hub through which most Incentive Compensation plan-level reporting should be joined.