Search Results cn_rate_dim_tiers_all




Overview
Explore in AI Chat

Overview

The CN_RATE_DIM_TIERS_ALL table is a core data structure within the Oracle E-Business Suite Incentive Compensation module (CN). It serves as the master repository for tier definitions associated with rate dimensions. In the context of the compensation plan engine, rate dimensions define the measurable criteria (e.g., revenue amount, product quantity) against which performance is evaluated. This table stores the specific tiered thresholds (e.g., 0-1000 units, 1001-5000 units) for those dimensions, which are fundamental for calculating variable pay rates and commissions. Its role is critical for translating raw performance data into the appropriate incentive payout levels as configured in compensation plans.

Key Information Stored

The table's primary purpose is to define tier ranges and their sequence for a given rate dimension. The most critical columns include RATE_DIM_TIER_ID, which is the primary key and unique identifier for each tier record. The RATE_DIMENSION_ID is a foreign key that links the tier to its parent definition in the CN_RATE_DIMENSIONS_ALL table. Other essential columns typically found in such tier tables, though not explicitly listed in the provided metadata, would logically include attributes like TIER_SEQUENCE (to order tiers from low to high), START_VALUE, and END_VALUE to define the numeric bounds of the tier. The table supports multi-organization architecture, as indicated by the "_ALL" suffix, implying it likely contains an ORG_ID column for partitioning data by operating unit.

Common Use Cases and Queries

This table is primarily accessed during the compensation calculation process to determine which tier a participant's performance falls into for a given dimension. Common operational and reporting queries involve listing all configured tiers for analysis or troubleshooting. A typical SQL pattern retrieves tier details for a specific rate dimension, often joined with its parent table for context.

SELECT rd.name dimension_name,
       rdt.tier_sequence,
       rdt.start_value,
       rdt.end_value
FROM   cn_rate_dim_tiers_all rdt,
       cn_rate_dimensions_all rd
WHERE  rdt.rate_dimension_id = rd.rate_dimension_id
AND    rd.rate_dimension_id = :p_dimension_id
ORDER BY rdt.tier_sequence;

Data integrity checks, such as identifying dimensions without any defined tiers or validating for overlapping tier ranges, are also frequent administrative use cases.

Related Objects

The CN_RATE_DIM_TIERS_ALL table sits at the center of a key relationship hierarchy within the Incentive Compensation schema, as documented by its foreign keys.

  • CN_RATE_DIMENSIONS_ALL: This is the parent table. Each record in CN_RATE_DIM_TIERS_ALL must link to a single rate dimension via the RATE_DIMENSION_ID column (Foreign Key: CN_RATE_DIM_TIERS_ALL.RATE_DIMENSION_ID -> CN_RATE_DIMENSIONS_ALL).
  • CN_SCH_DIM_TIERS_ALL: This table references CN_RATE_DIM_TIERS_ALL as a parent. It links the master tier definitions to specific schedule dimensions, using RATE_DIM_TIER_ID as the foreign key (Foreign Key: CN_SCH_DIM_TIERS_ALL.RATE_DIM_TIER_ID -> CN_RATE_DIM_TIERS_ALL).

These relationships ensure that tier definitions are consistently applied across the plan configuration, from the abstract rate dimension down to the concrete assignment within a compensation schedule.