Results for “cn_srp_payee_assigns_all”

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

Overview

CN_SRP_PAYEE_ASSIGNS_ALL is a transaction and setup table in the Oracle Incentive Compensation (CN) module. It stores the date-effective assignment of payees to salesperson plan elements. Each row represents a single payee assignment to a salesperson plan element that is effective for a defined date range, governed by START_DATE and END_DATE. The table resides in the CN schema and is classified as VALID in the ETRM metadata for release 12.1.1 / 12.2.2.

Within the Incentive Compensation data model, this table functions as the intersection that ties a payee (the individual receiving compensation credit) to a quota or salesperson plan element, and it is the parent of the commission line detail. In heuristic Data Vault terms, the mined relationship structure suggests this object is best modeled as a link: it resolves the many-to-many association between payees, quotas, and salesperson plan elements rather than describing an independent business entity. The presence of START_DATE and END_DATE further suggests the link carries effectivity, so an effectivity satellite would typically accompany the link in a dimensional or Data Vault representation.

Key Information Stored

The table contains 32 documented columns. The most significant of these are:

• SRP_PAYEE_ASSIGN_ID — the surrogate primary key, enforced by the CN_SRP_PAYEE_ASSIGNS_PK constraint. This is the value referenced by downstream commission line records.
• ORG_ID — the operating unit context, which is part of the unique index CN_SRP_PAYEE_ASSIGNS_U1 (SRP_PAYEE_ASSIGN_ID, ORG_ID). The pairing of the surrogate key with ORG_ID forms the documented business-key candidate.
• PAYEE_ID — identifies the payee receiving the assignment.
• SALESREP_ID — identifies the salesperson associated with the assignment.
• SRP_QUOTA_ASSIGN_ID — foreign key to CN_SRP_QUOTA_ASSIGNS_ALL, linking the payee assignment to the underlying quota assignment.
• QUOTA_ID — foreign key to CN_QUOTAS_ALL, identifying the quota against which the payee is assigned.
• START_DATE / END_DATE — the date-effective window during which the assignment is valid.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling row-level access.
• DELETE_FLAG — indicates logical deletion of a row without physical removal.
• OBJECT_VERSION_NUMBER — used for optimistic locking during concurrent updates.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns for capturing client-specific attributes.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — WHO-column audit trail populated for every transactional insert or update.

Common Use Cases and Queries

Typical reporting scenarios include verifying which payees are actively assigned to a salesperson plan element on a given date, reconciling commission lines back to their payee assignment, and auditing quota-to-payee relationships. Because the table is date-effective, queries almost always filter on START_DATE and END_DATE. For example, to list current assignments for an operating unit:

SELECT p.SRP_PAYEE_ASSIGN_ID, p.PAYEE_ID, p.SALESREP_ID, p.QUOTA_ID, p.START_DATE, p.END_DATE
FROM CN_SRP_PAYEE_ASSIGNS_ALL p
WHERE p.ORG_ID = :org_id
AND TRUNC(SYSDATE) BETWEEN p.START_DATE AND NVL(p.END_DATE, TRUNC(SYSDATE))
AND p.DELETE_FLAG = 'N';

A second common pattern joins this table to CN_COMMISSION_LINES_ALL to trace commission amounts back to the responsible payee and quota assignment, which supports incentive payout analysis and dispute resolution.

Related Objects

• CN_COMMISSION_LINES_ALL — references this table through SRP_PAYEE_ASSIGN_ID; each commission line depends on a payee assignment.
• CN_QUOTAS_ALL — joined via QUOTA_ID; supplies the quota definition the assignment operates against.
• CN_SRP_QUOTA_ASSIGNS_ALL — joined via SRP_QUOTA_ASSIGN_ID; provides the parent quota assignment record.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; enforces access control.
• CN_SRP_PAYEE_ASSIGNS_PK — the primary key constraint on SRP_PAYEE_ASSIGN_ID.
• CN_SRP_PAYEE_ASSIGNS_U1 — the unique index on (SRP_PAYEE_ASSIGN_ID, ORG_ID).