Search Results date_value2




Overview
Explore in AI Chat

Overview

BEN_ELIGY_CRIT_VALUES_D is a dictionary (denormalized, read-only) view owned by the APPS schema in Oracle E-Business Suite, residing in the BEN (Advanced Benefits) product. It exposes the criteria values that define how an eligibility profile is evaluated — that is, the specific threshold or match values associated with each eligibility criterion used by a benefits eligibility profile. In Oracle Advanced Benefits, an eligibility profile is built from one or more eligibility criteria (for example, age, length of service, compensation, or a user-defined criterion), and each criterion is given one or more comparison values. This view presents those comparison values together with descriptive names for the parent profile and criterion, making the data directly consumable by reports, extracts, and integration interfaces without additional joins.

The view is date-effective in nature: it joins to the base table on the effective start date falling within the profile's effective date range, so a given profile-criterion combination surfaces the criteria value row that was in force at that point. This supports point-in-time and current-effective reporting common in benefits administration.

Underlying Base Objects

The view is defined over the following documented base objects:

  • BEN_ELIGY_CRIT_VALUES_F (synonym) — the primary base table holding the criteria value rows, aliased ECV. All value, ordering, flag, and legislation columns originate here.
  • BEN_ELIGY_CRITERIA (synonym) — provides the eligibility criterion name, aliased EGL, joined on ELIGY_CRITERIA_ID.
  • BEN_ELIGY_PRFL_F (synonym) — provides the eligibility profile name, aliased ELP, joined on ELIGY_PRFL_ID.
  • FND_USER (synonym) — resolves the LAST_UPDATED_BY user ID to a user name, aliased FUSER, joined with an outer (+) so rows without a matching user are still returned.
  • HR_LOOKUPS (view) — supplies the meaning for the YES_NO lookup used to describe EXCLD_FLAG, aliased HR.
  • HR_API (package) — referenced indirectly through the date-effective infrastructure that governs row selection for this _D view.

The join to BEN_ELIGY_PRFL_F is constrained by the effective date overlap condition, ensuring the criteria value is reported within the profile's active period.

Key Columns

Common Use Cases and Queries

The view is typically used to report or extract eligibility configuration — for example, documenting which profiles evaluate a date-based criterion, or auditing criteria values by legislation. Because it already resolves the profile name, criterion name, and updater user name, ad-hoc queries require no manual joins.

To list all criteria values using DATE_VALUE1 for the current effective profile:

  • SELECT eligy_prfl_name, eligy_criteria_name, date_value1, date_value2, ordr_num FROM apps.ben_eligy_crit_values_d WHERE date_value1 IS NOT NULL ORDER BY eligy_prfl_name, ordr_num;

To isolate values for a specific profile and legislation:

  • SELECT eligy_criteria_name, number_value1, char_value1, date_value1, excl_flag FROM apps.ben_eligy_crit_values_d WHERE eligy_prfl_name = 'MY_PROFILE' AND legislation_code = 'US';

To audit recent changes to criteria values:

  • SELECT eligy_prfl_name, eligy_criteria_name, last_update_date, last_updated_by FROM apps.ben_eligy_crit_values_d WHERE last_update_date >= TRUNC(SYSDATE) - 30 ORDER BY last_update_date DESC;

Because the view enforces date effectiveness against the profile, consumers should generally not add their own effective-date predicates on the profile unless a specific historical point-in-time reconstruction is required.