Results for “cn_srp_pay_groups_all”

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

Overview

CN_SRP_PAY_GROUPS_ALL is a transactional configuration table in the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite. It stores the assignment of a salesperson to a payment group, functioning as the associative record that ties a sales representative to the pay group governing how that representative's compensation is grouped, processed, and disbursed. The table supports date-effective assignments: each row carries a START_DATE and END_DATE that define the validity window of the salesperson-to-pay-group relationship, and at any point in time a salesperson may hold only one active payment group assignment. The _ALL suffix indicates the table is partitioned by operating unit through ORG_ID, and multi-org views (such as CN_SRP_PAY_GROUPS_VL or a corresponding _V synonym) are typically layered on top for reporting in a specific org context.

Based on the documented foreign-key topology, the metadata suggests a satellite-leaning Data Vault classification. The table hangs off CN_PAY_GROUPS_ALL, CN_ROLE_PAY_GROUPS_ALL, and FND_SECURITY_GROUPS via foreign keys, while carrying its own descriptive and effective-dating attributes — the classic profile of a satellite radiating from a parent hub rather than a hub or pure link.

Key Information Stored

• SRP_PAY_GROUP_ID — the surrogate primary key (CN_SRP_PAY_GROUPS_PK), uniquely identifying each assignment record.
• SALESREP_ID — the salesperson to whom the payment group is assigned; the core business reference for the relationship.
• PAY_GROUP_ID — the foreign key to CN_PAY_GROUPS_ALL, identifying the payment group being assigned.
• START_DATE / END_DATE — the date-effective range defining when the assignment is valid; used to enforce the single-active-group rule.
• ORG_ID — the operating unit; combined with SRP_PAY_GROUP_ID in unique index CN_SRP_PAY_GROUPS_U1.
• ROLE_PAY_GROUP_ID — foreign key to CN_ROLE_PAY_GROUPS_ALL, linking the assignment to role-level pay group definitions.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling row-level access.
• LOCK_FLAG — marks records that must not be modified, often during concurrent compensation runs.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OA Framework for concurrent-update detection.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) segment columns for customer-specific extensions.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — WHO-column audit trail present on all transactional EBS tables.

Two unique indexes are documented: CN_SRP_PAY_GROUPS_U1 (SRP_PAY_GROUP_ID, ORG_ID) enforces the primary surrogate uniqueness per org, while CN_SRP_PAY_GROUPS_U2 (SALESREP_ID, PAY_GROUP_ID, START_DATE, END_DATE) is the business-key candidate governing effective-dated assignments.

Common Use Cases and Queries

Typical uses include auditing which payment group a salesperson belonged to during a given compensation period, resolving the active group assignment for a rep, and reconciling pay group membership against CN_PAY_GROUPS_ALL definitions. A common query to find the current active assignment is:

SELECT s.SALESREP_ID, s.PAY_GROUP_ID, s.START_DATE, s.END_DATE
FROM   CN_SRP_PAY_GROUPS_ALL s
WHERE  s.SALESREP_ID = :salesrep_id
AND    SYSDATE BETWEEN NVL(s.START_DATE, SYSDATE)
                   AND NVL(s.END_DATE, SYSDATE)
AND    s.ORG_ID = :org_id;

To trace assignments back to their pay group and role definitions for reporting, join to CN_PAY_GROUPS_ALL on PAY_GROUP_ID and to CN_ROLE_PAY_GROUPS_ALL on ROLE_PAY_GROUP_ID. Historical reporting relies on the START_DATE/END_DATE pair to reconstruct point-in-time membership, which is essential for commission recalculation and dispute resolution.

Related Objects

• CN_PAY_GROUPS_ALL — parent table; joined via CN_SRP_PAY_GROUPS_ALL.PAY_GROUP_ID = CN_PAY_GROUPS_ALL.PAY_GROUP_ID.
• CN_ROLE_PAY_GROUPS_ALL — joined via ROLE_PAY_GROUP_ID; defines role-level pay group scope.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; drives row-level security.
• CN_SALESREPS_ALL — references the salesperson identified by SALESREP_ID.
• CN_SRP_PAY_GROUPS_VL / _V views — org-stripped or translated views built on this table for reporting.
• CN_PAY_GROUP_API / Incentive Compensation assignment APIs — public APIs that insert, update, and end-date rows in this table.