Search Results cn_srp_pmt_plans_all




Overview
Explore in AI Chat

Overview

The table CN_SRP_PMT_PLANS_ALL 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 master repository for managing the assignment of payment plans to salespeople, formally known as Sales Resources (SRPs). The table's design is date-effective, meaning it maintains a historical record of payment plan assignments. This temporal tracking is critical for ensuring accurate commission calculations, as a salesperson's eligible compensation plan can change over time. The table's role is to establish and maintain the fundamental link between a salesperson and the compensation rules defined in their payment plan, forming the basis for the entire incentive calculation engine.

Key Information Stored

Each row in CN_SRP_PMT_PLANS_ALL represents a unique, date-effective assignment of a single payment plan to a single salesperson. While the full column list is not detailed in the provided metadata, the primary and foreign key relationships reveal the essential data points. The table's primary key, SRP_PMT_PLAN_ID, is a unique system-generated identifier for each assignment record. The most critical foreign key, PMT_PLAN_ID, stores the reference to the specific compensation plan defined in the CN_PMT_PLANS_ALL table. Implicitly, the table must also contain a column identifying the salesperson (likely SALESREP_ID or a similar foreign key to the sales force definition tables) and effective date columns (such as START_DATE and END_DATE) to manage the date-effective nature of the assignment. Additional columns typically track creation and last update dates, and the record's active status.

Common Use Cases and Queries

This table is central to operational reporting and audit processes within Incentive Compensation. A common use case is generating a report of all active payment plan assignments for a given period to validate sales team coverage. Another critical scenario involves tracing historical plan assignments to resolve commission disputes by verifying which plan was in effect for a transaction date. Administrators frequently query this table to identify salespeople assigned to a specific plan being modified. A foundational query pattern retrieves the active plan for a salesperson as of a specific date:

  • SELECT * FROM cn_srp_pmt_plans_all WHERE salesrep_id = :rep_id AND :transaction_date BETWEEN start_date AND NVL(end_date, :transaction_date);

Data from this table is also essential for downstream processes that calculate earned commissions, as these processes join transaction data to the applicable payment plan via this assignment table.

Related Objects

CN_SRP_PMT_PLANS_ALL has defined relationships with other key tables in the CN schema, primarily through foreign key constraints. As documented, the table has a direct foreign key relationship to the payment plan definition table:

  • CN_PMT_PLANS_ALL: This is the primary related object. The column CN_SRP_PMT_PLANS_ALL.PMT_PLAN_ID references CN_PMT_PLANS_ALL, joining the assignment to the specific rules and components of the compensation plan.

While not listed in the provided metadata, this table is also logically related to sales force definition tables (such as CN_SALESREPS) to resolve the salesperson identifier, and it is a parent table to various transaction and calculation detail tables that rely on the correct plan assignment. The table is central to the Incentive Compensation data model, acting as a crucial junction between resources, plans, and transactions.