Search Results rt_formula_asgn_id

Overview

The CN_RT_FORMULA_ASGNS_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation module (CN). It functions as a junction table, establishing and managing the assignment of specific rate tables (rate schedules) to compensation calculation formulas. This assignment is effective for defined date ranges, enabling the dynamic application of different earning rates to a single formula over time. Its role is critical for configuring complex, time-sensitive incentive plans where payout rates may change periodically based on business rules, performance tiers, or fiscal calendars. The table supports multi-organization data access through its "_ALL" suffix, indicating it stores data for multiple operating units.

Key Information Stored

The table's primary function is to link a calculation formula to a rate schedule within a specific timeframe. While the full column list is not detailed in the provided metadata, the core columns can be inferred from its relationships and description. The primary key column, RT_FORMULA_ASGN_ID, uniquely identifies each assignment record. Two critical foreign key columns are documented: CALC_FORMULA_ID, which references a specific compensation formula in CN_CALC_FORMULAS_ALL, and RATE_SCHEDULE_ID, which references a specific rate table in CN_RATE_SCHEDULES_ALL. Essential temporal columns, such as START_DATE and END_DATE, define the active period for the assignment. Standard EBS audit columns like CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY are also typically present, along with an ORG_ID column to segregate data by operating unit.

Common Use Cases and Queries

This table is central to administrative and reporting tasks within Incentive Compensation. A primary use case is auditing which rate schedules are applied to a given calculation formula and when, which is vital for troubleshooting payout calculations. Analysts frequently query this table to generate reports on active rate table assignments or to validate configuration before a new compensation period. Common SQL patterns include joining to related tables to get descriptive names. For example, to list all assignments for a specific formula:

• SELECT cf.name AS formula_name, rs.name AS rate_schedule_name, rfa.start_date, rfa.end_date
• FROM cn_rt_formula_asgns_all rfa,
• cn_calc_formulas_all cf,
• cn_rate_schedules_all rs
• WHERE rfa.calc_formula_id = cf.calc_formula_id
• AND rfa.rate_schedule_id = rs.rate_schedule_id
• AND cf.name = '<Formula_Name>'
• AND SYSDATE BETWEEN rfa.start_date AND NVL(rfa.end_date, SYSDATE);

Another critical use case is during the period close or plan changes, where administrators may need to inactivate old assignments or insert new ones to update the rate mapping.

Related Objects

The CN_RT_FORMULA_ASGNS_ALL table has defined relationships with two key master tables in the CN schema, as per the provided metadata:

• CN_CALC_FORMULAS_ALL: This relationship is established via the foreign key column CALC_FORMULA_ID. It links each assignment record to the specific incentive calculation formula that will utilize the assigned rate schedule.
• CN_RATE_SCHEDULES_ALL: This relationship is established via the foreign key column RATE_SCHEDULE_ID. It links each assignment record to the specific table of rates (rate schedule) that will be applied to the associated formula.

These relationships are fundamental to the data model. The table itself is referenced by the primary key constraint CN_RT_FORMULA_ASGNS_PK on the RT_FORMULA_ASGN_ID column. It is also likely referenced by various Incentive Compensation views and APIs that manage formula and rate schedule configuration.