Search Results cn_comp_plans_all




Overview
Explore in AI Chat

Overview

The CN_COMP_PLANS_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module. It serves as the master repository for all compensation plan definitions within the application. A compensation plan is a formalized set of rules, formulas, and parameters that govern how incentive earnings are calculated for sales representatives or other compensated roles. The existence of this table is fundamental to the module's operation, as every incentive transaction and calculation ultimately references a specific plan stored here. Its "ALL" suffix indicates it is a multi-organization table, storing data across all operating units configured within the instance, which is essential for enterprise-wide deployments.

Key Information Stored

The table's primary purpose is to store the unique identity and descriptive attributes of each compensation plan. The key columns, as indicated by the provided metadata, include the system-generated primary key COMP_PLAN_ID, which uniquely identifies each plan record. The table also enforces a unique constraint (CN_COMP_PLANS_UK1) on the combination of NAME and VERSION columns. This design allows for multiple versions of a plan with the same name to coexist, supporting plan evolution over time (e.g., "Q2 Sales Plan - 2023" version 1.0 and 2.0). While the excerpt does not list all columns, typical attributes stored would include plan status (e.g., Active, Inactive), effective start and end dates, plan type classification, and various flags controlling plan behavior and processing rules.

Common Use Cases and Queries

This table is central to administrative, operational, and reporting activities. Common use cases include generating a list of all active compensation plans for a given operating unit, identifying the specific plan version assigned to a salesperson, or auditing plan changes over time. A typical reporting query would join CN_COMP_PLANS_ALL to assignment tables to analyze plan coverage. For example:

  • To list all active plans: SELECT name, version, comp_plan_id FROM cn_comp_plans_all WHERE sysdate BETWEEN start_date AND NVL(end_date, sysdate+1);
  • To find plans assigned to a specific salesperson (SRP), a query would join through CN_SRP_PLAN_ASSIGNS_ALL: SELECT cp.name, cp.version FROM cn_comp_plans_all cp, cn_srp_plan_assigns_all spa WHERE cp.comp_plan_id = spa.comp_plan_id AND spa.salesrep_id = :rep_id;

Related Objects

As the central reference table for plans, CN_COMP_PLANS_ALL has defined relationships with several other critical tables in the CN schema, as shown in the foreign key metadata. Key related objects include:

  • CN_SRP_PLAN_ASSIGNS_ALL: Links compensation plans to individual sales representatives (SRPs).
  • CN_ROLE_PLANS_ALL: Associates compensation plans with defined roles within the hierarchy.
  • CN_QUOTA_ASSIGNS_ALL: Connects quota definitions to specific compensation plans, which is essential for quota-based incentive calculations.

These relationships ensure data integrity and form the backbone of the plan assignment and calculation architecture in Oracle Incentive Compensation.