Search Results cn_quota_assigns_pk

Overview

The CN_QUOTA_ASSIGNS_ALL table is a core data object within the Oracle E-Business Suite Incentive Compensation module (CN). It serves as the fundamental junction table that defines the assignment of quota elements to compensation plans. Its primary role is to establish the many-to-many relationship between compensation plan definitions (CN_COMP_PLANS_ALL) and quota definitions (CN_QUOTAS_ALL). This table is central to the configuration of incentive plans, as it determines which specific sales or performance targets (quotas) are applicable to and must be achieved under a given compensation plan. Being an "ALL" table, it is designed to store data for all operating units in a multi-org environment, with data partitioning typically managed via the ORG_ID column.

Key Information Stored

The table stores the essential linkage between two primary entities. The most critical columns are the foreign keys that create this relationship: COMP_PLAN_ID, which references the compensation plan from CN_COMP_PLANS_ALL, and QUOTA_ID, which references the specific quota metric from CN_QUOTAS_ALL. The table's primary key is QUOTA_ASSIGN_ID, a unique system-generated identifier. According to the provided metadata, a unique key constraint (CN_QUOTA_ASSIGNS_UK1) also exists on the combination of COMP_PLAN_ID and QUOTA_ID, enforcing business logic that prevents the same quota from being assigned to the same plan more than once. Other typical columns in such junction tables would include creation and last update dates, and the ORG_ID for multi-org support, though these are not explicitly listed in the provided excerpt.

Common Use Cases and Queries

This table is primarily accessed during compensation plan setup, administration, and reporting. A common operational use case is validating the quotas assigned to a plan before it is activated. For reporting, analysts frequently query this table to list all quotas associated with a specific compensation plan or to identify which plans utilize a particular quota metric. A typical SQL pattern involves joining to the related dimension tables:

• List Quotas for a Plan: SELECT q.NAME FROM cn_quota_assigns_all a, cn_quotas_all q WHERE a.quota_id = q.quota_id AND a.comp_plan_id = :p_plan_id;
• Find Plans Using a Quota: SELECT cp.NAME FROM cn_quota_assigns_all a, cn_comp_plans_all cp WHERE a.comp_plan_id = cp.comp_plan_id AND a.quota_id = :p_quota_id;

Data in this table is typically maintained via the Oracle Incentive Compensation administrator forms or APIs, not via direct SQL manipulation.

Related Objects

As documented in the provided foreign key metadata, CN_QUOTA_ASSIGNS_ALL has direct, critical dependencies on two other primary tables in the CN schema:

• CN_QUOTAS_ALL: Linked via the QUOTA_ID column. This table stores the definition of the quota metrics themselves, such as name, calculation formula, and measurement period.
• CN_COMP_PLANS_ALL: Linked via the COMP_PLAN_ID column. This table stores the master definition of the compensation plan, including its name, effective dates, and status.

The table is also a likely parent to other transactional or calculation tables in the module, as the plan-quota relationship it defines is essential for computing commissions and incentives. The primary key constraint CN_QUOTA_ASSIGNS_PK and unique key constraint CN_QUOTA_ASSIGNS_UK1 are integral to maintaining the integrity of these relationships.