Search Results cn_srp_role_history_all




Overview
Explore in AI Chat

Overview

The CN_SRP_ROLE_HISTORY_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module, specifically for versions 12.1.1 and 12.2.2. It serves as the system of record for tracking historical changes to salesperson role assignments. In the context of incentive compensation, accurate historical data is critical for calculating commissions, auditing plan performance, and analyzing sales force productivity over time. This table ensures that every change to a salesperson's role or territory assignment is permanently recorded, providing a complete audit trail that supports complex, period-based compensation calculations and reporting.

Key Information Stored

The table's primary purpose is to log the history of sales roles and salesperson assignments. While the full column list is not detailed in the provided metadata, the structure revolves around a unique identifier and key foreign relationships. The primary key column, SRP_ROLE_HISTORY_ID, uniquely identifies each historical record. A critical foreign key column is SRP_ROLE_ID, which links to the CN_SRP_ROLE_DTLS_ALL table to reference the specific sales role detail record. Typically, such history tables also include columns for effective start and end dates (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), the identifier of the salesperson (SRP_ID or RESOURCE_ID), and the role assignment status. Additional columns often track creation and last update dates along with the responsible user IDs for audit purposes.

Common Use Cases and Queries

This table is essential for historical reporting and resolving compensation disputes. A common use case is reconstructing a salesperson's role assignments during a specific fiscal period to verify the correct commission plan was applied. For reporting, analysts query this table to analyze sales force turnover, role tenure, and assignment trends. A typical SQL pattern involves joining to resource and role detail tables to produce a readable history report:

  • SELECT h.SRP_ROLE_HISTORY_ID, r.RESOURCE_NAME, d.ROLE_NAME, h.EFFECTIVE_START_DATE, h.EFFECTIVE_END_DATE FROM CN_SRP_ROLE_HISTORY_ALL h, CN_SRP_ROLE_DTLS_ALL d, JTF_RS_RESOURCE_EXTNS_V r WHERE h.SRP_ROLE_ID = d.SRP_ROLE_ID AND h.SRP_ID = r.RESOURCE_ID ORDER BY r.RESOURCE_NAME, h.EFFECTIVE_START_DATE;

Another critical use case is during the compensation calculation engine's runtime, where the system references this history to determine the applicable rules for a transaction based on its booking date.

Related Objects

The CN_SRP_ROLE_HISTORY_ALL table has a documented foreign key relationship with the CN_SRP_ROLE_DTLS_ALL table, which holds the descriptive details of the sales roles themselves. The join is performed on the SRP_ROLE_ID column. While not listed in the provided metadata, this history table is logically and functionally dependent on core Incentive Compensation setup tables such as CN_SRP_PAYGROUPS (for pay group assignments) and CN_SALESREPS (or JTF_RS_RESOURCE_EXTNS) for salesperson master data. It is also a foundational table for various compensation history and audit reports within the CN module.