Search Results ben_vrbl_rt_elig_prfl_d




Overview
Explore in AI Chat

Overview

The BEN_VRBL_RT_ELIG_PRFL_D view is a denormalized reporting object within the Oracle Advanced Benefits (BEN) module. Its name is derived from its business purpose: it resolves the relationship between Variable Rate Eligibility Profiles (VRBL_RT_ELIG_PRFL) and displays descriptive ("_D" suffix) attributes for each record. In Oracle EBS 12.1.1 and 12.2.2, the view carries a status of VALID and is owned by the APPS schema.

The view plays a critical role in the Benefits configuration and reporting layer. A variable rate eligibility profile links a specific variable rate profile to an eligibility profile, governing which participants qualify for a given variable rate (for example, an age-banded or service-banded rate). Rather than forcing implementers and developers to join the base configuration tables manually, BEN_VRBL_RT_ELIG_PRFL_D surfaces this relationship alongside human-readable names, effective dating, audit information, and the mandatory-flag meaning. It is consumed in Fast Formula lookups, custom BI Publisher reports, value sets, and integration extracts that need to present eligibility profile configuration in a decoded form.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view is defined over the following objects, most referenced through APPS synonyms:

  • BEN_VRBL_RT_ELIG_PRFL_F — the primary transaction table holding one row per variable-rate-to-eligibility-profile assignment, with effective date ranges. This is the driving "F" (base) table aliased VEP.
  • BEN_VRBL_RT_PRFL_F — the Variable Rate Profile base table (alias VRP), supplying the variable rate profile name.
  • BEN_ELIGY_PRFL_F — the Eligibility Profile base table (alias BEP), supplying the eligibility profile name.
  • HR_LOOKUPS — the Oracle HR lookup view (alias HL1), used to decode the mandatory flag against lookup type YES_NO.
  • FND_USER — the application user table (alias FUSER), used to resolve the last updater's user name.
  • HR_API — referenced as a package dependency, typically for effective-dating or date-track utility routines.

The joins are effective-dated: the view matches each eligibility-profile assignment to its parent variable rate profile and eligibility profile only where the assignment's EFFECTIVE_START_DATE falls within the parent records' effective date ranges, and it enforces BUSINESS_GROUP_ID equality throughout. HR_LOOKUPS and FND_USER are outer-joined (indicated by the (+) operator), so records survive even when the lookup or user cannot be resolved.

Key Columns

  • VRBL_RT_ELIG_PRFL_ID — primary key of the assignment record; the unique identifier linking the variable rate profile to the eligibility profile.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity window of the assignment.
  • VRBL_RT_PRFL_NAME — decoded name of the associated variable rate profile.
  • ELIGY_PRFL_NAME — decoded name of the associated eligibility profile.
  • MNDTRY_FLAG_MEANING — the Yes/No meaning of the mandatory flag, decoded via HR_LOOKUPS.
  • LAST_UPDATE_DATE — audit timestamp of the most recent modification.
  • LAST_UPDATED_BY — the application user name of the last updater.

Common Use Cases and Queries

Typical scenarios include auditing which eligibility profiles gate each variable rate, producing configuration extracts, and driving custom enrollment or reporting logic.

List all active assignments with decoded names:

  • SELECT vrbl_rt_prfl_name, eligy_prfl_name, mndtry_flag_meaning, effective_start_date, effective_end_date FROM apps.ben_vrbl_rt_elig_prfl_d WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date ORDER BY vrbl_rt_prfl_name;

Find all eligibility profiles for a specific variable rate profile:

  • SELECT eligy_prfl_name, mndtry_flag_meaning FROM apps.ben_vrbl_rt_elig_prfl_d WHERE vrbl_rt_prfl_name = :p_rate_name;

Audit recent changes:

  • SELECT vrbl_rt_prfl_name, eligy_prfl_name, last_updated_by, last_update_date FROM apps.ben_vrbl_rt_elig_prfl_d WHERE last_update_date >= :p_from_date ORDER BY last_update_date DESC;

Because the view is effective-dated, queries intended to reflect current configuration should always constrain on SYSDATE between the effective dates.