Search Results cn_rate_tiers_pk

Overview

The CN_RATE_TIERS_ALL table is a core transactional data object within the Oracle E-Business Suite Incentive Compensation (CN) module. It serves as the master repository for defining the specific performance tiers and their corresponding payout rates within a compensation plan's rate schedule. Each record in this table represents a discrete tier, such as a quota attainment bracket (e.g., 90-100% of quota), and stores the commission rate, bonus amount, or multiplier applicable to that tier. Its primary role is to provide the foundational rate data used by the compensation engine to calculate commissions and incentives for sales transactions and quota achievements, linking strategic compensation design to operational payout calculations.

Key Information Stored

The table's structure is centered on defining tiered compensation logic. The most critical columns, as indicated by the provided primary and unique key metadata, are RATE_TIER_ID and RATE_SCHEDULE_ID. The RATE_TIER_ID is the unique system-generated identifier for each tier record (Primary Key: CN_RATE_TIERS_PK). The RATE_SCHEDULE_ID is a foreign key that links the tier to its parent compensation structure defined in the CN_RATE_SCHEDULES_ALL table. This column is so integral to the data model that it is defined in two separate unique keys (CN_RATE_TIERS_UK1, CN_RATE_TIERS_UK2), though the exact distinction is not detailed in the excerpt. Other typical columns in such a table, though not explicitly listed here, would include attributes like TIER_SEQUENCE_NUM, START_AMOUNT, END_AMOUNT, RATE, and RATE_TYPE to define the tier's range and associated payout value.

Common Use Cases and Queries

This table is central to compensation analytics and operational reporting. A common use case is auditing the tiered rate structure for a specific compensation plan before a calculation period. Another is troubleshooting commission calculations by tracing the specific rate tier applied to a transaction. A typical query involves joining to the rate schedules and plan definitions to list all tiers for a plan.

• Sample Query: Retrieve all rate tiers for a specific schedule:
  SELECT rt.rate_tier_id, rt.tier_sequence_num, rt.start_amount, rt.end_amount, rt.rate
FROM cn_rate_tiers_all rt
WHERE rt.rate_schedule_id = :p_rate_schedule_id
ORDER BY rt.tier_sequence_num;
• Reporting Use Case: Generating a report showing the distribution of commission lines across different rate tiers to analyze payout patterns and plan effectiveness.

Related Objects

As documented in the foreign key relationships, CN_RATE_TIERS_ALL is a central hub connected to several key compensation tables.

• Parent Reference: The table references CN_RATE_SCHEDULES_ALL via RATE_SCHEDULE_ID. This is the master definition of a rate schedule.
• Child References (Tables dependent on a rate tier):
  • CN_COMMISSION_LINES_ALL: Links via RATE_TIER_ID to record the specific tier used for calculating an individual commission line on a transaction.
  • CN_ROLE_QUOTA_RATES: References RATE_TIER_ID to assign payout rates to quota targets for specific compensation roles.
  • CN_SCH_DIM_TIERS_ALL: Uses RATE_TIER_ID to associate tiers with dimensional data (like product or territory) within a schedule.
  • CN_SRP_RATE_ASSIGNS_ALL: Links via RATE_TIER_ID to assign specific rate tiers to salespeople (SRPs).