Search Results ben_comp_lvl_acty_rt_f_pk




Overview
Explore in AI Chat

Overview

The BEN_COMP_LVL_ACTY_RT_F table is a core data structure within the Oracle E-Business Suite Advanced Benefits (BEN) module, specifically for releases 12.1.1 and 12.2.2. It functions as a rate table that stores the specific monetary or percentage rates to be applied to compensation levels for benefit-related calculations. Its primary role is to define the effective-dated relationship between a compensation level factor and its corresponding rate, enabling the system to accurately compute benefit amounts, contributions, or coverage levels based on an employee's compensation. As a date-effective table (indicated by the '_F' suffix and the EFFECTIVE_START_DATE/EFFECTIVE_END_DATE columns in its primary key), it maintains a historical record of rate changes over time, which is critical for auditing and processing benefits against the correct rates for any given point in time.

Key Information Stored

The table's structure is designed to manage the lifecycle of compensation level rates. The most critical columns include the surrogate primary key COMP_LVL_ACTY_RT_ID, which uniquely identifies a rate record. The EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns define the period during which the rate is active and, together with the ID, form the table's primary key (BEN_COMP_LVL_ACTY_RT_F_PK). The COMP_LVL_FCTR_ID is a foreign key that links the rate to a specific compensation level factor defined in the BEN_COMP_LVL_FCTR table. Other typical columns in such a rate table, though not explicitly listed in the provided metadata, would logically include the actual RATE_VALUE (which could be a number or percentage), the RATE_TYPE, and creation/modification audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

This table is central to benefits administration processes that are tied to compensation. Common use cases include calculating employer contributions to retirement plans, determining life insurance coverage multiples based on salary, or establishing flexible spending account limits. For reporting and data extraction, a typical query would join this table to the compensation level factor table to retrieve the current effective rate for a specific factor. A sample SQL pattern to find active rates would be:

  • SELECT claf.COMP_LVL_FCTR_ID, clafrt.RATE_VALUE, clafrt.EFFECTIVE_START_DATE
  • FROM BEN_COMP_LVL_ACTY_RT_F clafrt,
  • BEN_COMP_LVL_FCTR claf
  • WHERE clafrt.COMP_LVL_FCTR_ID = claf.COMP_LVL_FCTR_ID
  • AND SYSDATE BETWEEN clafrt.EFFECTIVE_START_DATE AND clafrt.EFFECTIVE_END_DATE
  • AND claf.NAME = '<Factor_Name>';

Data fixes often involve end-dating an incorrect rate record and inserting a new effective-dated row with the corrected value, ensuring historical data integrity is preserved.

Related Objects

The BEN_COMP_LVL_ACTY_RT_F table has a direct and documented foreign key relationship, which is fundamental to its configuration. It references the BEN_COMP_LVL_FCTR table via the COMP_LVL_FCTR_ID column. This relationship ensures that every rate is associated with a valid, predefined compensation level factor (such as "Annual Salary Tier 1" or "Monthly Compensation Band B"). The compensation level factor itself would typically be linked to broader benefit plans, formulas, or coverage definitions within the BEN schema. While not listed in the provided metadata, this rate table is likely referenced by various benefit calculation engines, payroll interfaces, and possibly summary reporting views within the Advanced Benefits module.