Search Results ben_ext_crit_val




Overview
Explore in AI Chat

Overview

BEN_EXT_CRIT_VAL is a table in the BEN (Advanced Benefits) schema of Oracle E-Business Suite, holding the value records associated with extract criteria definitions. In the Oracle Advanced Benefits extract framework, extract criteria describe the conditions under which participant, enrollment, or plan data is selected for extraction to external systems such as payroll providers, third-party administrators, or carriers. BEN_EXT_CRIT_VAL supplies the concrete values that those criteria are evaluated against, complementing the criteria type definitions held in BEN_EXT_CRIT_TYP.

The table is documented as VALID in both EBS 12.1.1 and 12.2.2, with a physical schema of 14 columns. Based on its foreign key structure — a single parent reference to BEN_EXT_CRIT_TYP and inbound references from BEN_EXT_CRIT_CMBN — the heuristic Data Vault classification is satellite-leaning. That is, the table is best modeled as a descriptive satellite attached to one or more parent hubs or links (the criteria type and combination structures) rather than as an independent hub or associative link.

Key Information Stored

Each row in BEN_EXT_CRIT_VAL captures one criterion value instance. The most significant columns are:

The documented unique index BEN_EXT_CRIT_VAL_PK covers (EXT_CRIT_VAL_ID, ZD_EDITION_NAME), establishing the business-key candidate pair alongside the surrogate identifier.

Common Use Cases and Queries

Typical usage centers on resolving which participants or enrollments meet an extract criterion and on reporting the configuration of extract criteria. A representative join retrieves each criterion value together with its type description:

  • SELECT v.EXT_CRIT_VAL_ID, v.VAL_1, v.VAL_2, t.NAME FROM BEN_EXT_CRIT_VAL v, BEN_EXT_CRIT_TYP t WHERE v.EXT_CRIT_TYP_ID = t.EXT_CRIT_TYP_ID;
  • Filtering by business group and legislation: WHERE v.BUSINESS_GROUP_ID = :p_bg_id AND v.LEGISLATION_CODE = :p_leg.
  • Resolving criterion combinations through BEN_EXT_CRIT_CMBN to reconstruct the full logical expression used by an extract process.

Reporting use cases include auditing extract criteria configuration, verifying criterion values after a patch or upgrade, and diagnosing extract output discrepancies by tracing which values were applied. Because editions are represented via ZD_EDITION_NAME, queries intended for the current edition should constrain on the appropriate edition name.

Related Objects

BEN_EXT_CRIT_VAL participates in a small, well-defined object network:

  • BEN_EXT_CRIT_TYP — parent table; joined via BEN_EXT_CRIT_VAL.EXT_CRIT_TYP_ID = BEN_EXT_CRIT_TYP.EXT_CRIT_TYP_ID.
  • BEN_EXT_CRIT_CMBN — combination table; references BEN_EXT_CRIT_VAL via EXT_CRIT_VAL_ID, linking criterion values into combined expressions.
  • BEN_EXT_CRIT_VAL_PK — the primary key constraint and index.
  • BEN_EXT_CRIT_VAL_PK unique index — the (EXT_CRIT_VAL_ID, ZD_EDITION_NAME) business-key index.

These three tables together form the core of the Advanced Benefits extract criteria configuration model, with BEN_EXT_CRIT_VAL acting as the satellite holding the values that drive extract selection logic.