Search Results ben_actl_prem_vrbl_rt_f




Overview
Explore in AI Chat

Overview

BEN_ACTL_PREM_VRBL_RT_F is a transactional table in the BEN (Advanced Benefits) module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. Its documented description states that it stores "variable rates that can be applied to plan or option in plans premium." It is owned by the BEN schema and carries a VALID status in ETRM. The table enables Oracle Advanced Benefits users to attach variable-rate profiles to the actual premium amounts charged for a plan or a specific plan option, rather than using a single fixed rate. It is an effective-dated, date-tracked (_F) table: the primary key BEN_ACTL_PREM_VRBL_RT_F_PK is composed of ACTL_PREM_VRBL_RT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The metadata provides a heuristic Data Vault classification of standalone, meaning the object is not identified as a hub, link, or satellite, but rather a self-contained effective-dated entity. As a modeling suggestion, this reflects the classic Oracle date-tracked pattern rather than a pure Data Vault construct, whose nearest analogue would be an effective-dated satellite function. The table is documented in ETRM 12.2.2 with 44 physical columns.

Key Information Stored

The most significant columns of this table fall into three groups: the identifier, the business context, and the audit/descriptive attributes.

  • ACTL_PREM_VRBL_RT_ID — system-generated identifier for the variable-rate record; the leading column of the composite primary key and therefore not a standalone business key, though it behaves as a partial surrogate in joins.
  • EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date-tracked validity of the record; together with the ID they form the primary key. These columns control which rate is in force for any given date.
  • ACTL_PREM_ID — the actual premium identifier to which the variable rate is applied; the primary link back to the premium entity.
  • VRBL_RT_PRFL_ID — the variable-rate profile that supplies the rate logic.
  • BUSINESS_GROUP_ID — the HR business group (enterprise) that owns the record; the multi-tenancy key for BEN data.
  • ORDR_NUM — ordering sequence for variables tied to the same premium, controlling calculation order.
  • APV_ATTRIBUTE_CATEGORY and APV_ATTRIBUTE1 through APV_ATTRIBUTE30 — a 30-column flexfield-style attribute block, following Oracle's standard descriptive flexfield pattern, used to capture additional installation-specific data on the variable rate.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns for change tracking and reconciliation.
  • OBJECT_VERSION_NUMBER — optimistic-locking column used by the Oracle Forms/ADF layer to detect concurrent updates.

The documented unique index BEN_ACTL_PREM_VRBL_RT_F_PK is the only business-key candidate, and its leading column is the surrogate ID, so the true business uniqueness is the combination of the ID with the two date columns.

Common Use Cases and Queries

Typical usage centers on premium calculation and benefits reporting. To retrieve the currently effective variable rate for a given premium, the pattern is a date-bounded query:

  • Current rate lookup: SELECT * FROM ben.ben_actl_prem_vrbl_rt_f WHERE actl_prem_id = :prem_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Rate history: query all versions by ACTL_PREM_VRBL_RT_ID ordered by EFFECTIVE_START_DATE to show rate changes over time.
  • Profile usage audit: group by VRBL_RT_PRFL_ID to identify which variable-rate profiles are most heavily referenced across premiums.
  • Enterprise-scoped reporting: filter by BUSINESS_GROUP_ID to restrict results to a single legal entity or business group.
  • Attribute reporting: expose APV_ATTRIBUTE1..30 where an implementation has configured the flexfield for additional rate metadata.

Because the table is date-tracked, every query against it should include an EFFECTIVE_START_DATE / EFFECTIVE_END_DATE predicate unless the intent is deliberately to retrieve historical rows.

Related Objects

Based on the documented relationship data, this table is standalone. No foreign keys are reported in the ETRM metadata, so relationships are inference-based through the ID columns rather than enforced constraints.

  • BEN_ACTL_PREM_F — the premium header entity, joined on ACTL_PREM_ID, providing the premium to which the variable rate applies.
  • BEN_VRBL_RT_PRFL_F — the variable-rate profile, joined on VRBL_RT_PRFL_ID, supplying the rate definition.
  • BEN_VRBL_RT_PRFL_RL_F — the profile rules, used together with the profile to derive the actual rate.
  • BEN_PL_F and BEN_PGM_F — plan and program headers reached indirectly through the premium and option chain.
  • BEN_POPT_F — plan options, where the variable rate may be scoped to an option rather than a whole plan.
  • FND_FLEX_VALUES_VL and the BEN lookup views — used to decode APV_ATTRIBUTE_CATEGORY and related flexfield values.

These joins should be manually verified against the implementation's own foreign-key and flexfield configuration, since the ETRM metadata explicitly classifies this object as standalone.