Search Results cn_quota_pay_elements_all




Overview
Explore in AI Chat

Overview

The CN_QUOTA_PAY_ELEMENTS_ALL table is a core configuration and transactional object within the Oracle EBS Incentive Compensation (CN) module. It stores the mapping between quota definitions and the pay elements that compensate participants against those quotas. In incentive compensation design, a quota represents a target assigned to a participant or organization, while a pay element defines how the participant is rewarded when quota attainment is achieved. This table is the associative structure that binds the two together over a defined validity period.

Because the table carries both effective-dating columns and descriptive attributes, and because its foreign keys reference several independent parent entities, the heuristic Data Vault classification is best treated as a link candidate — a relationship table connecting quotas to pay elements. However, its STATUS, START_DATE, and END_DATE columns also give it satellite-like characteristics, capturing the changing state of the quota-to-pay-element mapping over time. Modelers should consider whether the persistent attributes warrant a separate satellite or can remain inline in the link structure.

Key Information Stored

The physical schema under the CN schema in 12.2.2 documents 30 columns. The most significant columns and their roles are:

  • QUOTA_PAY_ELEMENT_ID — the surrogate primary key uniquely identifying each quota pay element mapping record.
  • QUOTA_ID — foreign key to CN_QUOTAS_ALL, identifying the quota to which the pay element is attached.
  • PAY_ELEMENT_TYPE_ID — identifies the specific pay element type being mapped.
  • START_DATE and END_DATE — effective-dating columns defining the active lifespan of the mapping.
  • STATUS — indicates whether the mapping is active, inactive, or otherwise administratively controlled.
  • ORG_ID — multi-organization identifier supporting the operating unit security model.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling row-level access.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns allowing customer-specific extensions without schema changes.
  • OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent update protection, typical of the OA Framework-based Incentive Compensation UI.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — the standard EBS WHO audit columns.

The unique index CN_QUOTA_PAY_ELEMENTS_U1 spans QUOTA_ID, PAY_ELEMENT_TYPE_ID, START_DATE, END_DATE, and STATUS. This confirms the business key: a specific quota may map to a given pay element type only once for a given effective period and status combination. The surrogate QUOTA_PAY_ELEMENT_ID is distinct from this composite business key.

Common Use Cases and Queries

Implementers and analysts query this table to audit quota compensation design, reconcile pay element assignments before running incentive calculation batches, and produce reporting on which pay elements are associated with which quotas. A common diagnostic is to list all currently active mappings for a given quota:

  • Join CN_QUOTA_PAY_ELEMENTS_ALL to CN_QUOTAS_ALL on QUOTA_ID to resolve quota names for a participant.
  • Filter on STATUS = 'ACTIVE' and TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE to return currently effective mappings.
  • Correlate PAY_ELEMENT_TYPE_ID with pay element setup tables to verify the reward mechanism attached to each quota.
  • Group by ORG_ID and SECURITY_GROUP_ID to review configuration across operating units or responsibility-level security.

Because the table is accessed primarily through the Incentive Compensation administrative forms, direct DML is discouraged; changes should flow through the application to preserve OBJECT_VERSION_NUMBER integrity and WHO audit values.

Related Objects

The documented foreign keys and the overall CN module design point to these significant related objects:

  • CN_QUOTAS_ALL — the parent quota table, joined on QUOTA_ID; every mapping must reference a valid quota.
  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID, governing security group configuration.
  • CN_PAY_ELEMENTS / PAY_ELEMENT_TYPES — the pay element definition sources referenced by PAY_ELEMENT_TYPE_ID.
  • CN_INCENTIVE_COMPENSATION APIs — the PL/SQL packages that create, update, and validate quota pay element mappings.
  • CN_QUOTA_ASSIGNMENTS and related quota assignment tables that depend on the mapping structure when calculating attainment.

Together these objects form the quota-to-compensation configuration chain that the Incentive Compensation engine consumes during calculation cycles.