Search Results cn_rate_sch_dims_all




Overview
Explore in AI Chat

Overview

The CN_RATE_SCH_DIMS_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module, specifically in versions 12.1.1 and 12.2.2. It functions as a critical junction table that defines the relationship between rate schedules and rate dimensions. Its primary role is to store the assignment of specific rate dimensions to each rate table (schedule), thereby establishing the multi-dimensional structure upon which compensation calculations are built. This table is essential for configuring the complex, multi-variable criteria—such as product, customer, or territory—that determine commission rates for sales personnel.

Key Information Stored

The table's structure centers on linking identifiers and maintaining metadata for these assignments. The primary key, RATE_SCH_DIM_ID, uniquely identifies each dimension-to-schedule assignment record. The two fundamental foreign key columns are RATE_SCHEDULE_ID, which links to the CN_RATE_SCHEDULES_ALL table to identify the specific rate table, and RATE_DIMENSION_ID, which links to the CN_RATE_DIMENSIONS_ALL table to specify the dimension being applied. Additional columns typically include standard Oracle EBS audit fields (e.g., CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) and the ORG_ID for multi-organization support, aligning with the "_ALL" naming convention.

Common Use Cases and Queries

This table is central to administrative setup, reporting, and troubleshooting compensation plans. A common use case is validating the dimensional structure of a rate schedule during plan design. System administrators or compensation analysts may query this table to audit which dimensions are active for a given rate table or to identify schedules using a specific dimension. A typical reporting query would join to the related tables to produce a human-readable list.

Sample Query:
SELECT rs.name rate_schedule_name,
       rd.name rate_dimension_name
FROM cn_rate_sch_dims_all rsd,
       cn_rate_schedules_all rs,
       cn_rate_dimensions_all rd
WHERE rsd.rate_schedule_id = rs.rate_schedule_id
AND rsd.rate_dimension_id = rd.rate_dimension_id
AND rs.rate_schedule_id = :p_rate_sched_id;
This pattern is foundational for understanding the data model driving commission calculations.

Related Objects

The CN_RATE_SCH_DIMS_ALL table sits at the intersection of two primary setup entities, as defined by its documented foreign key relationships:

  • CN_RATE_SCHEDULES_ALL: This is the parent table for rate schedules. The relationship is established via the foreign key column CN_RATE_SCH_DIMS_ALL.RATE_SCHEDULE_ID, which references the primary key in CN_RATE_SCHEDULES_ALL. Each record in CN_RATE_SCH_DIMS_ALL must correspond to a valid rate schedule.
  • CN_RATE_DIMENSIONS_ALL: This is the parent table for the defined rate dimensions. The relationship is established via the foreign key column CN_RATE_SCH_DIMS_ALL.RATE_DIMENSION_ID, which references the primary key in CN_RATE_DIMENSIONS_ALL. Each record must correspond to a valid dimension.

These relationships enforce data integrity, ensuring that dimension assignments are only made to valid schedules using valid dimensions. The table is also a likely parent to other detail tables, such as those storing specific dimension values or rate tiers, forming the complete hierarchy of a compensation rate structure.