Search Results ben_bnft_vrbl_rt_rl_f




Overview
Explore in AI Chat

Overview

BEN_BNFT_VRBL_RT_RL_F is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores the variable rate rule for a benefit — the configuration that governs how a benefit's cost or coverage amount is calculated dynamically rather than as a single fixed value. Each row represents an effective-dated rule definition that ties a benefit offering to a calculation method and, optionally, a formula, allowing the plan to drive rates from formulas, derived attributes, or configurable rule attributes.

The table is date-effectivity enabled: rows are versioned by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, so historical and future rule definitions coexist. The schema also exposes a BUSINESS_GROUP_ID for multi-organization (business group) isolation and a standard WHO column set for audit. The ETRM metadata's heuristic Data Vault classification is standalone, meaning no foreign-key relationships were mined from the documented structure. From a modeling perspective, this object is best treated as a satellite-like, effective-dated configuration entity keyed by its own surrogate identifier rather than as a hub or link.

Key Information Stored

The physical schema documents 44 columns. The most significant are:

  • BNFT_VRBL_RT_RL_ID — the system-generated surrogate identifier for the variable rate rule. It is the leading column of the primary key and is the value carried on any dependent configuration or result rows.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effectivity boundaries. Together with the ID they form the composite primary key BEN_BNFT_VRBL_RT_RL_F_PK (BNFT_VRBL_RT_RL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), which is also the only documented unique index and therefore the business-key candidate.
  • CVG_AMT_CALC_MTHD_ID — identifier of the coverage amount calculation method used by the rule; this is the core reference that determines the calculation strategy applied to the benefit.
  • FORMULA_ID — identifier of the Oracle FastFormula (or equivalent) used when the rule resolves rates through a formula rather than a static method.
  • BUSINESS_GROUP_ID — business group context, enabling the same rule structure to be defined independently for each enterprise group.
  • ORDR_TO_APLY_NUM — ordering sequence that resolves precedence when multiple variable rate rules apply to the same benefit.
  • BRR_ATTRIBUTE_CATEGORY and BRR_ATTRIBUTE1–30 — descriptive flexfield (DFF) columns reserved for customer-defined rule attributes, exposed as the "BRR" attribute context.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J middle tier to prevent lost updates during concurrent maintenance.

The surrogate key (BNFT_VRBL_RT_RL_ID) is distinct from the unique index in that the ID alone identifies the rule, while the ID-plus-dates combination identifies a specific effective-dated version — a distinction that matters for any point-in-time query.

Common Use Cases and Queries

Typical scenarios include auditing benefit rate configuration, diagnosing why a benefit produced an unexpected cost, and reporting on which plans use formula-driven versus method-driven rates. Because the table is effective-dated, every query should constrain against a known effective date.

  • Point-in-time rule lookup: SELECT BNFT_VRBL_RT_RL_ID, CVG_AMT_CALC_MTHD_ID, FORMULA_ID FROM BEN.BEN_BNFT_VRBL_RT_RL_F WHERE BUSINESS_GROUP_ID = :p_bg_id AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ORDER BY ORDR_TO_APLY_NUM;
  • Precedence analysis: aggregate by ORDR_TO_APLY_NUM to detect overlapping or duplicate order values for the same benefit.
  • Formula dependency reporting: filter on FORMULA_ID IS NOT NULL to identify all rules that require FastFormula maintenance.
  • Change history and audit: include LAST_UPDATE_DATE and LAST_UPDATED_BY to trace when a rate rule was last modified.
  • DFF extraction: select BRR_ATTRIBUTE_CATEGORY and the relevant BRR_ATTRIBUTEn columns for customer-specific rule metadata.

Related Objects

The documented metadata records no foreign keys (the table is classified standalone), so the following relationships reflect the standard Advanced Benefits data model and should be verified against the customer's dictionary: