Search Results ben_actl_prem_vrbl_rt_rl_f




Overview
Explore in AI Chat

Overview

The BEN_ACTL_PREM_VRBL_RT_RL_F table is a core configuration and transactional structure within the BEN - Advanced Benefits module of Oracle E-Business Suite (12.1.1 / 12.2.2). It stores the definition of variable rate rules that can be applied to either a plan or a plan option when calculating the actual premium for a participant. In other words, this table holds the logic that determines how flex credits, rate multipliers, or formula-driven adjustments modify a benefits premium based on participant-specific variables (such as age, salary, or coverage level).

The object is owned by the BEN schema and is designated as VALID with 45 physical columns. Based on the primary key structure and the absence of foreign keys to other tables (relationship data classifies it as standalone), a heuristic Data Vault model would suggest this table functions as a satellite attached to a hub or link representing the actual premium definition (ACTL_PREM_ID) and the variable rate rule identity (ACTL_PREM_VRBL_RT_RL_ID). It is date-effective (Effective Start/End Date) and multi-tenant aware (Business Group ID), consistent with Oracle EBS HRMS DateTrack and Business Group security patterns.

Key Information Stored

The table’s 45 columns capture the identity, temporal validity, business context, and rule logic. The most important columns are:

  • ACTL_PREM_VRBL_RT_RL_ID – Surrogate primary key component uniquely identifying the variable rate rule.
  • EFFECTIVE_START_DATE and EFFECTIVE_END_DATE – DateTrack columns establishing the validity period of the rule definition.
  • BUSINESS_GROUP_ID – Multi-tenant partition key for the enterprise business group.
  • ACTL_PREM_ID – Foreign key linking the variable rate rule to the actual premium definition (the plan/option premium record).
  • FORMULA_ID – Identifies the Oracle Fast Formula used to compute the variable rate adjustment.
  • AVA_ATTRIBUTE_CATEGORY – Descriptive flexfield context discriminating category for the 30 AVA_ATTRIBUTE1–30 columns.
  • AVA_ATTRIBUTE1 through AVA_ATTRIBUTE30 – Descriptive flexfield attribute columns used to store additional rule-specific parameters (e.g., age bands, salary thresholds) without schema changes.
  • ORDR_TO_APLY_NUM – Order-to-apply sequence number that controls the precedence when multiple variable rate rules apply to the same premium.
  • RT_TRTMT_CD – Rate treatment code defining how the calculated variable rate is applied (e.g., add, override, multiply).
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER – Standard WHO audit and optimistic-locking columns.

The primary key is BEN_ACTL_PREM_VRBL_RT_RL_F_PK on (ACTL_PREM_VRBL_RT_RL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). The only documented unique index is the primary key itself; no separate business-key candidate unique index is listed.

Common Use Cases and Queries

Typical use cases include: auditing variable rate rule configurations for a plan year, reporting on which formulas drive premium adjustments, and troubleshooting why a participant’s premium differs from the base rate.

Sample query to retrieve active variable rate rules for a specific actual premium:

  • SELECT vr.ACTL_PREM_VRBL_RT_RL_ID, vr.ACTL_PREM_ID, vr.FORMULA_ID, vr.RT_TRTMT_CD, vr.ORDR_TO_APLY_NUM
  • FROM BEN_ACTL_PREM_VRBL_RT_RL_F vr
  • WHERE vr.ACTL_PREM_ID = :p_actl_prem_id
  • AND TRUNC(SYSDATE) BETWEEN vr.EFFECTIVE_START_DATE AND vr.EFFECTIVE_END_DATE
  • AND vr.BUSINESS_GROUP_ID = :p_bg_id
  • ORDER BY vr.ORDR_TO_APLY_NUM;

Another frequent pattern is joining to FF_FORMULAS_F or FF_FORMULA_TYPES to display the formula name instead of the surrogate FORMULA_ID. Because the table is DateTrack-enabled, all queries must filter on effective dates; bypassing this filter can return multiple historical rows for the same rule identifier.

Related Objects

Although the extracted FK metadata classifies this table as standalone, the following objects are commonly referenced or dependent in Advanced Benefits implementations:

  • BEN_ACTL_PREM_F – The actual premium header table; join on ACTL_PREM_ID = BEN_ACTL_PREM_F.ACTL_PREM_ID to link variable rate rules to their parent premium definition.
  • FF_FORMULAS_F / FF_FORMULAS_TL – Oracle Fast Formula definitions; join on FORMULA_ID to retrieve the formula name and text.
  • FND_DESCR_FLEX_COL_USAGE_VL / FND_DESCRIPTIVE_FLEXS – Descriptive flexfield metadata for AVA_ATTRIBUTE_CATEGORY and AVA_ATTRIBUTE1–30.
  • BEN_PL_F / BEN_PGM_F – Plan and program definition tables, reached indirectly via ACTL_PREM_ID for reporting plan-level rate logic.
  • BEN_ACTL_PREM_BKUP_F – Backup copy of actual premium definitions, often used in batch recalculation or migration scripts that also copy variable rate rules.
  • HR_API hooks / BEN_ACTL_PREM_API – Public APIs that create or maintain actual premium records and, by extension, may insert or update corresponding variable rate rule rows.
  • FND_USER – Join on LAST_UPDATED_BY or CREATED_BY for audit reporting.

Referential integrity is enforced at the application layer rather than by database foreign keys, so data extract and ETL processes should validate the ACTL_PREM_ID and FORMULA_ID values against their respective parent tables.