Search Results ben_vrbl_rt_elig_prfl_f_pk

Overview

BEN_VRBL_RT_ELIG_PRFL_F is a dated intersection (cross-reference) table within the BEN — Advanced Benefits module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It links variable rate profiles, which drive rate or cost calculations for benefits offerings, to eligibility profiles, which determine which employees or dependents qualify for a given plan or program. Each row therefore expresses a qualified, effective-dated association between one variable rate profile and one eligibility profile.

The table is date-tracked: it carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns and a composite primary key BEN_VRBL_RT_ELIG_PRFL_F_PK defined on VRBL_RT_ELIG_PRFL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This pattern is characteristic of Oracle Advanced Benefits datetrack-enabled configuration tables, where historical and future-dated versions of a business relationship are preserved rather than overwritten.

The metadata describes the object as an intersection table. Under a heuristic Data Vault classification mined from the foreign-key structure, the object is assessed as standalone, but the intersection semantics suggest it is most naturally modeled as a link entity connecting variable rate profiles and eligibility profiles, with the datetracked attributes (effective dates, mandatory flag, descriptive flexfield values) behaving like link satellites. This classification is a modeling suggestion, not a documented Oracle assertion.

Key Information Stored

• VRBL_RT_ELIG_PRFL_ID — surrogate primary key for the intersection row; the single-column sequence-driven identifier.
• VRBL_RT_PRFL_ID — foreign key to the variable rate profile being linked; the principal business participant.
• ELIGY_PRFL_ID — foreign key to the eligibility profile that qualifies the rate profile; the second business participant. Together, VRBL_RT_PRFL_ID and ELIGY_PRFL_ID constitute the business-key candidate for the relationship.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — datetrack boundaries that make the association time-effective and complete the primary key.
• MNDTRY_FLAG — indicates whether the eligibility profile is mandatory for the rate profile association, influencing validation during plan configuration.
• BUSINESS_GROUP_ID — the HR business group (enterprise) owning the row, supporting multi-tenant configuration separation.
• OBJECT_VERSION_NUMBER — optimistic locking counter used by the Oracle Application Object Library during concurrent updates.
• VEP_ATTRIBUTE_CATEGORY and VEP_ATTRIBUTE1–VEP_ATTRIBUTE30 — descriptive flexfield (DFF) context and segment values, allowing customer-specific extension without schema change.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard who-column audit attributes maintained by Oracle Forms.

The table contains 44 documented columns in the 12.2.2 physical schema; the thirty VEP_ATTRIBUTE columns plus context are the DFF extension block, and the remainder are keys, dates, and audit fields.

Common Use Cases and Queries

The primary use case is configuration validation and reporting: determining which eligibility profiles gate a given variable rate profile, and vice versa. A typical query joins this table to the eligibility profile and variable rate profile entities, filtered by effective date:

• List active eligibility profiles for a rate profile: select ELIGY_PRFL_ID from BEN_VRBL_RT_ELIG_PRFL_F where VRBL_RT_PRFL_ID = :p_id and trunc(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
• Identify mandatory eligibility combinations using MNDTRY_FLAG to audit plan setup.
• Reconstruct historical configuration by querying the date-tracked rows rather than the current-only view.
• Extract descriptive flexfield values (VEP_ATTRIBUTE_CATEGORY / VEP_ATTRIBUTE1–30) for client-specific reporting extensions.

This table is generally not exposed for direct end-user maintenance; configuration occurs through the Advanced Benefits setup forms and concurrent processes, so direct DML is discouraged.

Related Objects

• BEN_VRBL_RT_PRFL_F — the variable rate profile entity joined on VRBL_RT_PRFL_ID.
• BEN_ELIG_PRFL_F — the eligibility profile entity joined on ELIGY_PRFL_ID.
• BEN_VRBL_RT_PRFL — the non-dated variable rate profile definition referenced through the profile identifier.
• BEN_ELIG_PRFL — the non-dated eligibility profile definition.
• BEN_VRBL_RT_ELIG_PRFL_F_PK — the composite primary key index enforcing uniqueness on VRBL_RT_ELIG_PRFL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.
• PER_BUSINESS_GROUPS — referenced via BUSINESS_GROUP_ID for enterprise scoping.

Because the metadata classifies the object as standalone with no formally documented foreign-key constraints, these relationships are best treated as functional joins on the identifier columns rather than enforced database constraints.