Search Results ben_eligy_crit_values_f_pk




Overview
Explore in AI Chat

Overview

BEN.BEN_ELIGY_CRIT_VALUES_F is a core transactional table within the Oracle Advanced Benefits (BEN) module. It stores the actual criteria values defined against an eligibility profile, and is documented as a child of the eligibility profile table. In Oracle EBS 12.1.1 and 12.2.2, this table drives the rules engine that decides whether a person, organization, or assignment qualifies for a benefit program, plan, or option. Each row represents one criterion definition bound to an eligibility profile, capturing the comparison values, weighting, and scoring used by eligibility processing.

From a Data Vault modeling perspective, the relationship metadata classifies this object heuristically as standalone, meaning it is not a pure hub or link within a normalized vault structure. The presence of a single foreign key to BEN_ELIGY_CRITERIA, combined with an effective-dated primary key and descriptive attributes, suggests this object behaves most like a satellite attached to the eligibility criteria and profile context rather than a standalone reference hub. This classification should be treated as a modeling suggestion only.

Key Information Stored

The table is an _F (date-tracked) entity governed by the primary key BEN_ELIGY_CRIT_VALUES_F_PK, composed of ELIGY_CRIT_VALUES_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The unique index on these three columns represents the business-key candidate; the surrogate key is ELIGY_CRIT_VALUES_ID.

Common Use Cases and Queries

A frequent requirement is to extract all criteria values configured for a given eligibility profile, showing how each criterion is evaluated:

  • Eligibility rule auditing — retrieving active criteria values for a profile to understand plan qualification logic.
  • Score-based eligibility analysis — aggregating CRITERIA_SCORE and CRITERIA_WEIGHT across a profile's criteria.
  • DFF reporting — querying ECV_ATTRIBUTE1–30 for customer-specific configuration data.

A representative SQL pattern joining child to parent is:

SELECT ecv.ELIGY_CRIT_VALUES_ID, ecv.ELIGY_PRFL_ID, ec.ELIGY_CRITERIA_ID FROM BEN.BEN_ELIGY_CRIT_VALUES_F ecv, BEN.BEN_ELIGY_CRITERIA ec WHERE ecv.ELIGY_CRITERIA_ID = ec.ELIGY_CRITERIA_ID AND ecv.ELIGY_PRFL_ID = :p_profile AND SYSDATE BETWEEN ecv.EFFECTIVE_START_DATE AND ecv.EFFECTIVE_END_DATE;

Because the table is effective-dated, all reporting should filter on the effective range to return the correct as-of snapshot.

Related Objects

  • BEN_ELIGY_CRITERIA — Parent criterion definition; joined on ELIGY_CRITERIA_ID. This is the documented foreign key relationship.
  • BEN_ELIGY_PRFL — Eligibility profile header referenced by ELIGY_PRFL_ID.
  • BEN_ELIGY_PRFL_F — Effective-dated eligibility profile rows.
  • BEN_ELIGY_PRFL_CRITERIA — Association of profiles to criteria.
  • BEN_ELIGY_CRIT_VALUES — Non-date-tracked counterpart or view exposing current values.
  • BEN_ELIG_FACTS and related BEN eligibility APIs — consume criteria values during eligibility processing.

These relationships make BEN_ELIGY_CRIT_VALUES_F central to reporting on and troubleshooting Advanced Benefits eligibility configuration.