Search Results cn_srp_role_dtls_all




Overview
Explore in AI Chat

Overview

The CN_SRP_ROLE_DTLS_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 detailed assignment records linking salespersons (SRPs) to specific sales roles. This table is fundamental to the configuration and operation of the compensation plan engine, as a salesperson's assigned role directly determines their eligibility for compensation plans, quota assignments, and the calculation rules applied to their transactions. The table supports multi-organization data access through its "_ALL" suffix, storing records for all operating units assigned to the responsibility of the user.

Key Information Stored

While the provided metadata does not list specific columns, the table's primary key and foreign key relationships define its critical data elements. The primary identifier is the SRP_ROLE_ID, a unique system-generated key for each salesperson-role assignment record. A central column is ROLE_MODEL_ID, a foreign key to the CN_ROLE_MODELS_ALL table, which links the assignment to a specific, pre-defined role model containing compensation rules and structures. Other essential columns typically include the SALESREP_ID (linking to the salesperson in the JTF_RS_SALESREPS table), effective start and end dates for the assignment, and organizational context via the ORG_ID column. The table effectively stores the active and historical mapping of sales personnel to the incentive roles that govern their pay.

Common Use Cases and Queries

This table is central to reporting and auditing compensation role assignments. Common operational use cases include generating a list of all salespersons currently assigned to a specific role model for quota distribution, validating role assignments prior to a compensation plan run, and analyzing historical role changes for a salesperson. A typical query would join CN_SRP_ROLE_DTLS_ALL to CN_ROLE_MODELS_ALL to report on active assignments.

  • Sample Query (Active Assignments):
    SELECT srd.salesrep_id, srd.role_model_id, crm.name
    FROM cn_srp_role_dtls_all srd,
         cn_role_models_all crm
    WHERE srd.role_model_id = crm.role_model_id
    AND sysdate BETWEEN srd.start_date_active AND NVL(srd.end_date_active, sysdate+1)
    AND srd.org_id = 123;
  • Reporting Use Case: Auditing the chain of role assignments and their effective dates is critical for resolving compensation disputes and ensuring accurate plan calculations for retroactive adjustments.

Related Objects

The CN_SRP_ROLE_DTLS_ALL table maintains integral relationships with several other key tables in the Incentive Compensation schema, as documented by its foreign key constraints.

  • Referenced Table (Parent):
    • CN_ROLE_MODELS_ALL: Linked via ROLE_MODEL_ID. This is the primary relationship, defining the specific compensation role assigned to the salesperson.
  • Referencing Tables (Child):
    • CN_SRP_CNRT_HISTORY_ALL: Linked via SRP_ROLE_ID. This table tracks changes to a salesperson's compensation relationship, which is tied to their role.
    • CN_SRP_QUOTA_CATES_ALL: Linked via SRP_ROLE_ID. This table stores quota category assignments, which are defined based on the salesperson's role.
    • CN_SRP_ROLE_HISTORY_ALL: Linked via SRP_ROLE_ID. This table maintains a detailed audit history of changes made to the role assignment records in CN_SRP_ROLE_DTLS_ALL itself.