Search Results ben_vrbl_rt_prfl_rl_f




Overview
Explore in AI Chat

Overview

BEN.BEN_VRBL_RT_PRFL_RL_F is a date-tracked configuration table within the Oracle Advanced Benefits (BEN) module. It defines the rules associated with a variable rate profile, which is the mechanism Oracle Benefits uses to calculate plan costs that vary dynamically based on employee, plan, or enrollment attributes rather than a fixed rate. In Oracle EBS 12.1.1 and 12.2.2 the table is registered as a valid object owned by the BEN schema, with 45 documented columns in the ETRM 12.2.2 physical schema.

The object follows the standard Oracle EBS "F" (date-effective) pattern, carrying EFFECTIVE_START_DATE and EFFECTIVE_END_DATE as part of its primary key, which permits users to maintain historical and future-dated versions of the same rule. From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. This indicates that the table can be modeled as a self-contained entity whose business key is the variable rate profile rule identifier, with no mandated parent satellite or link relationship derived from the documented FK metadata.

Key Information Stored

The surrogate primary key is defined by the constraint BEN_VRBL_RT_PRFL_RL_F_PK, composed of VRBL_RT_PRFL_RL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This composite key has the simultaneous role of the unique business-key candidate documented in the schema; the rule identifier alone distinguishes the logical rule, while the two date columns version it over time.

  • VRBL_RT_PRFL_RL_ID – Surrogate identifier of the variable rate profile rule. The anchoring value for all joins and API lookups.
  • VRBL_RT_PRFL_ID – Reference to the owning variable rate profile, linking the rule to the cost-calculation definition it qualifies.
  • FORMULA_ID – Identifier of the Oracle FastFormula used to evaluate the rule and return the variable rate result.
  • DRVBL_FCTR_APLS_FLAG – Flag indicating whether derivable factors apply when the formula executes, influencing which inputs are passed to the calculation.
  • ORDR_TO_APLY_NUM – Order in which the rule is applied, determining precedence when multiple rules target the same profile.
  • BUSINESS_GROUP_ID – Operating unit / business group partition, essential for multi-organization and security-filtered queries.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – Date-effective window controlling when the rule version is active.
  • VPR_ATTRIBUTE_CATEGORY and VPR_ATTRIBUTE1–VPR_ATTRIBUTE30 – Descriptive flexfield (DFF) columns reserved for client-specific rule attributes without schema changes.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER – Standard EBS audit and optimistic-locking columns.

Common Use Cases and Queries

Functional consultants and developers query this table when tracing how a benefit plan's variable cost is derived. A typical point-in-time lookup resolves the active rule versions for a given profile and business group:

  • Identifying all rules attached to a single VRBL_RT_PRFL_ID, ordered by ORDR_TO_APLY_NUM, to understand evaluation precedence.
  • Retrieving the FORMULA_ID values so the associated FastFormula logic can be inspected for rate derivation errors.
  • Auditing which rules were effective as of a payroll or enrollment date using TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE.
  • Reporting DFF usage, for example grouping rules that populate VPR_ATTRIBUTE_CATEGORY, to confirm client extensions are in use.
  • Extracting rule data to a data warehouse for benefits cost analytics, filtering by BUSINESS_GROUP_ID to respect security partitioning.

Related Objects

  • BEN_VRBL_RT_PRFL_F – The parent variable rate profile table, joined on VRBL_RT_PRFL_ID.
  • FF_FORMULAS_F / FF_FORMULAS_VL – Holds the FastFormula definitions referenced through FORMULA_ID.
  • BEN_PL_F – Benefit plan definition, reached indirectly via the variable rate profile when resolving plan-level costs.
  • BEN_PGM_F – Program definition that groups the plans served by these rate rules.
  • FND_FORM_CUSTOM_RULES and the Flexfield views for the VPR_ATTRIBUTE DFF, which expose the descriptive segments.
  • HR_ALL_ORGANIZATION_UNITS – Supplies organization context for the BUSINESS_GROUP_ID partition.
  • BEN_VRBL_RT_PRFL_RL_F_PK – The unique index enforcing the documented business-key uniqueness.