Results for “ben_eligy_crit_values_x”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BEN_ELIGY_CRIT_VALUES_X is a seeded, date-effective view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the BEN (Advanced Benefits) product family and exposes the criteria values associated with eligibility profiles configured within Oracle Advanced Benefits. An eligibility profile in BEN is composed of one or more eligibility criteria, and each criterion is in turn defined by one or more expected values. This view surfaces those expected values in a denormalized, reporting-friendly form, including the standard descriptive flexfield (DFF) attribute columns that many implementers repurpose for customer-specific data.

The view's core characteristic is that it filters the underlying base entity by the current system date. Only rows whose effective date range brackets TRUNC(SYSDATE) are returned. This makes the view suitable for runtime evaluation and for reporting on the currently effective configuration of eligibility criteria, but unsuitable for historical or future-dated analysis.

Underlying Base Objects

The view is defined over a single documented base object, BEN_ELIGY_CRIT_VALUES_F, referenced through a synonym. The _F suffix denotes the base (full) table that stores every dated version of an eligibility criterion value row. The _X view applies the standard EBS date-effectiveness filter:

• TRUNC(SYSDATE) BETWEEN ECV.EFFECTIVE_START_DATE AND ECV.EFFECTIVE_END_DATE

Because the view does not join to any other table, it is a thin, single-source projection. All columns in the view are direct projections of columns in the base table; no derivation or aggregation occurs. The synonym BEN_ELIGY_CRIT_VALUES_F resolves to the APPS-owned base table.

Key Columns

• ELIGY_CRIT_VALUES_ID — Primary key identifying each criterion value row. Note that the base table column is typed ELIGY_CRIT_VALUES_ID, while the view exposes ELIGY_CRIT_VALUES_ID with identical naming.
• ELIGY_PRFL_ID — Foreign key to the eligibility profile to which the criterion value belongs.
• ELIGY_CRITERIA_ID — Foreign key to the eligibility criterion definition (for example, age, length of service, or compensation).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effectiveness boundaries; the view returns only rows effective as of TRUNC(SYSDATE).
• ORDR_NUM — Sequence number controlling evaluation order of the criteria values.
• NUMBER_VALUE1 / NUMBER_VALUE2, CHAR_VALUE1 / CHAR_VALUE2, DATE_VALUE1 / DATE_VALUE2 — Typed value pairs used to express the comparison range for the criterion (for example, a lower and upper age bound).
• EXCLD_FLAG — Indicates whether the criterion value operates as an exclusion.
• BUSINESS_GROUP_ID, LEGISLATION_CODE — Multi-tenant and legislative context of the row.
• ECV_ATTRIBUTE_CATEGORY and ECV_ATTRIBUTE1 through ECV_ATTRIBUTE30 — Descriptive flexfield segments. These are the columns most commonly referenced by implementers extending the criteria value model. The DFF is prefixed ECV_ to distinguish it from other flexfield contexts in the BEN schema.
• Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER support standard EBS auditing and optimistic locking.

Common Use Cases and Queries

The view is typically used to report on current eligibility configuration, to drive fast-formula or PL/SQL evaluation routines, and to reconcile custom DFF data stored on criterion values. The following query lists currently effective criterion values for a given eligibility profile:

• SELECT ECV.ELIGY_CRIT_VALUES_ID, ECV.ELIGY_CRITERIA_ID, ECV.ORDR_NUM, ECV.NUMBER_VALUE1, ECV.NUMBER_VALUE2, ECV.CHAR_VALUE1, ECV.CHAR_VALUE2, ECV.EXCLD_FLAG FROM APPS.BEN_ELIGY_CRIT_VALUES_X ECV WHERE ECV.ELIGY_PRFL_ID = :p_profile_id ORDER BY ECV.ORDR_NUM;

To locate rows where a specific DFF segment — for example, ECV_ATTRIBUTE1, which is the column returned for the common search term ecv_attribute1 — has been populated, use:

• SELECT ECV.ELIGY_CRIT_VALUES_ID, ECV.ELIGY_PRFL_ID, ECV.ELIGY_CRITERIA_ID, ECV.ECV_ATTRIBUTE_CATEGORY, ECV.ECV_ATTRIBUTE1 FROM APPS.BEN_ELIGY_CRIT_VALUES_X ECV WHERE ECV.ECV_ATTRIBUTE1 = :p_value;

Because the view is date-filtered, these queries return only rows valid on the current date. For point-in-time or historical reporting, query the base table BEN_ELIGY_CRIT_VALUES_F directly and supply explicit effective date predicates, since the _X view cannot return superseded or future-dated rows.