Search Results cn_srp_quota_rates_all




Overview
Explore in AI Chat

Overview

The CN_SRP_QUOTA_RATES_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation module (CN). It serves as a critical junction table that stores the assignment of specific quota component rate tables to individual salespersons (SRPs). This table is fundamental for the calculation of commissions and incentives, as it links a salesperson's assigned quota categories with the precise performance rates and tiers that determine their earnings. Its role is to define the applicable compensation rules for a salesperson within a given quota structure, enabling the system to compute payouts based on achieved performance against assigned quotas. As an "ALL" table, it is designed to support multi-organization structures (Multi-Org) by potentially partitioning data by operating unit.

Key Information Stored

The table's primary purpose is to maintain the relationship between a salesperson's quota assignment and the rate table governing their compensation. While the full column list is not detailed in the provided metadata, the documented primary and foreign keys reveal its essential structure. The primary key, SRP_QUOTA_RATE_ID, uniquely identifies each assignment record. The two critical foreign key columns define the core relationships: SRP_QUOTA_CATE_ID links to the CN_SRP_QUOTA_CATES_ALL table, identifying the specific quota category assigned to the salesperson. ROLE_QUOTA_RATE_ID links to the CN_ROLE_QUOTA_RATES table, identifying the exact rate table (with its tiers, rates, and formulas) that applies to that assignment. Additional columns likely include effective start and end dates, creation and last update information, and the ORG_ID for Multi-Org context.

Common Use Cases and Queries

This table is central to compensation analytics and operational reporting. A primary use case is generating a report of all active rate table assignments for sales personnel to audit compensation plans. Another critical use is during the commission calculation engine runtime, where the system queries this table to find the correct rate table to apply for a given salesperson and quota component. A typical query would join this table to salesperson, quota category, and rate table details to produce a comprehensible assignment listing.

SELECT s.name salesperson,
       qc.name quota_category,
       rqr.name rate_table
  FROM cn_srp_quota_rates_all sqr,
       cn_srp_quota_cates_all sqc,
       cn_role_quota_rates rqr,
       jtf_rs_salesreps s
 WHERE sqr.srp_quota_cate_id = sqc.srp_quota_cate_id
   AND sqr.role_quota_rate_id = rqr.role_quota_rate_id
   AND sqc.salesrep_id = s.salesrep_id
   AND SYSDATE BETWEEN sqr.start_date AND NVL(sqr.end_date, SYSDATE);

Related Objects

The table is integral within the Incentive Compensation schema, with documented foreign key relationships to two key tables:

  • CN_SRP_QUOTA_CATES_ALL: This relationship, via the SRP_QUOTA_CATE_ID column, connects the rate assignment to the specific quota category (e.g., "Q1 Product Sales") assigned to a salesperson.
  • CN_ROLE_QUOTA_RATES: This relationship, via the ROLE_QUOTA_RATE_ID column, connects the assignment to the master definition of the rate table itself, which contains the actual tiers, percentages, and calculation rules.

These relationships form a logical chain: a Salesperson is assigned a Quota Category, which in turn has a specific Rate Table assigned via the CN_SRP_QUOTA_RATES_ALL table, ultimately defining their compensation parameters.