Search Results ben_ext_crit_cmbn




Overview
Explore in AI Chat

Overview

BEN_EXT_CRIT_CMBN is a table within the BEN schema (Advanced Benefits) in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is "Extract criteria combination." It supports the extract infrastructure used by Advanced Benefits to define the criteria under which a participant or enrollment record qualifies for inclusion in a benefits extract. Each row represents a single criterion combination — a logical predicate built from an extract criterion value together with an operator and criterion type — that the extract process evaluates against participant data.

The table has a documented Data Vault classification (heuristic, mined from its foreign key structure) of satellite-leaning. This suggests treating BEN_EXT_CRIT_CMBN as an attribute-bearing satellite attached to the parent extract criterion value business key, rather than as an independent hub or a pure link. The single outbound foreign key to BEN_EXT_CRIT_VAL reinforces this reading: the table stores descriptive and conditional attributes about a criterion value rather than asserting a new many-to-many relationship.

Key Information Stored

The table is documented with 15 physical columns. The most significant are the following.

  • EXT_CRIT_CMBN_ID — The surrogate primary key, defined by the unique index BEN_EXT_CRIT_CMBN_PK. It uniquely identifies each criterion combination row.
  • EXT_CRIT_VAL_ID — The foreign key to BEN_EXT_CRIT_VAL, anchoring the combination to a specific extract criterion value. This is the principal join column.
  • CRIT_TYP_CD — The criterion type code, classifying the nature of the criterion being evaluated.
  • OPER_CD — The operator code, defining the comparison logic (for example, equality, range, inclusion) applied when the criterion is evaluated.
  • VAL_1 and VAL_2 — Two generic value columns holding the operands used by the operator; the paired columns allow range-style or interval comparisons.
  • BUSINESS_GROUP_ID — The business group that owns the row, a common multi-tenancy discriminator in HR/BEN tables.
  • LEGISLATION_CODE — The legislation under which the criterion combination is valid, enabling country-specific extract rules.
  • OBJECT_VERSION_NUMBER — The optimistic locking version column used by the Oracle framework to detect concurrent updates.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — The standard WHO audit columns.
  • ZD_EDITION_NAME — The editioning column introduced under the 12.2 Online Patching model. The documented business-key candidate is the unique index BEN_EXT_CRIT_CMBN_PK, covering EXT_CRIT_CMBN_ID together with ZD_EDITION_NAME.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios resolve an extract's full criteria logic by starting from the combination rows and joining upward to the criterion value. A representative query:

  • Retrieve all combinations for a given criterion value:
    SELECT c.EXT_CRIT_CMBN_ID, c.CRIT_TYP_CD, c.OPER_CD, c.VAL_1, c.VAL_2 FROM BEN.BEN_EXT_CRIT_CMBN c WHERE c.EXT_CRIT_VAL_ID = :p_ext_crit_val_id;
  • Trace the full extract definition: join BEN_EXT_CRIT_CMBN to BEN_EXT_CRIT_VAL on EXT_CRIT_VAL_ID to reach the parent criterion value and its associated extract criterion.
  • Audit recent changes to extract logic: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY across a business group.
  • Data-migration or reconciliation extracts: select all rows for a LEGISLATION_CODE to compare criterion definitions between environments.

Related Objects

The following objects are most significant in relation to BEN_EXT_CRIT_CMBN.

  • BEN_EXT_CRIT_VAL — Referenced through BEN_EXT_CRIT_CMBN.EXT_CRIT_VAL_ID → BEN_EXT_CRIT_VAL. The direct parent table and the primary join path.
  • BEN_EXT_CRIT_CMBN_PK — The unique index enforcing the primary key on EXT_CRIT_CMBN_ID and ZD_EDITION_NAME.
  • The extract criterion and extract definition tables above BEN_EXT_CRIT_VAL, which complete the chain from the extract header down to individual combination rows.
  • Advanced Benefits extract process and its concurrent programs, which consume these rows at run time to evaluate participant eligibility.
  • Standard BEN/HR audit and security objects keyed on BUSINESS_GROUP_ID that govern visibility of these rows.