Search Results cn_srp_cnrt_history_all




Overview
Explore in AI Chat

Overview

The CN_SRP_CNRT_HISTORY_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation module (CN). It serves as the central repository for historical records pertaining to compensation contracts assigned to salespeople. In the context of EBS 12.1.1 and 12.2.2, this table is critical for maintaining a complete audit trail of changes to a salesperson's compensation terms over time. Its role is to support compensation calculations, reporting, and analysis by preserving the state of compensation contracts at various points in history, ensuring accurate commission processing and enabling historical trend reporting.

Key Information Stored

The table's primary purpose is to store historical snapshots of compensation contract details. The key identifier is the SRP_CNRT_HISTORY_ID column, which is the table's primary key. A crucial foreign key column is SRP_ROLE_ID, which links each historical record to a specific salesperson role defined in the CN_SRP_ROLE_DTLS_ALL table. While the exact full column list is not detailed in the provided metadata, a table of this nature typically stores data such as the effective start and end dates for the historical record, the compensation plan or contract identifier in effect at that time, associated quotas, rates, and other terms. It also commonly includes standard EBS audit columns like CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY to track who made changes and when.

Common Use Cases and Queries

This table is primarily used for historical analysis and troubleshooting compensation calculations. Common use cases include auditing changes to a salesperson's compensation plan, recreating commission calculations for a prior period, and generating reports on compensation plan adoption over time. A typical query would join this history table to the role details and compensation plan tables to analyze the timeline of contract changes for a specific salesperson. For example:

  • Identifying all compensation contracts a salesperson has been assigned to: SELECT * FROM cn_srp_cnrt_history_all hist, cn_srp_role_dtls_all role WHERE hist.srp_role_id = role.srp_role_id AND role.salesrep_id = :rep_id ORDER BY hist.start_date_active;
  • Comparing compensation terms for a salesperson across two different fiscal quarters by filtering on the relevant effective date ranges stored in the history table.

Related Objects

The CN_SRP_CNRT_HISTORY_ALL table has documented relationships with other key Incentive Compensation tables. Its primary foreign key relationship is with the CN_SRP_ROLE_DTLS_ALL table, which holds detailed information about salesperson roles. The join is performed using the SRP_ROLE_ID column. While not explicitly listed in the provided excerpt, this history table is also logically and often physically related to core compensation objects such as CN_CNRT_HEADERS_ALL (compensation contract headers) and CN_QUOTA_RULES (quota definitions). Queries often involve joining through the role details table to the CN_SALESREPS table to get the full salesperson identity.