Search Results ben_ext_crit_val_pk




Overview
Explore in AI Chat

Overview

BEN.BEN_EXT_CRIT_VAL is a transactional configuration table within the Oracle E-Business Suite Benefits (BEN) schema. It stores the discrete values that populate extract criteria types used by the Benefits extract engine. An extract criteria type defines a category of filtering rule (for example, postal code range, plan enrollment, or eligibility status), and BEN_EXT_CRIT_VAL holds the individual literal values that satisfy or bound that rule. A representative record might instruct the extract process to include all persons residing in postal codes from 91000 through 91999, expressed as separate value rows or paired low/high values.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, carrying the standard Oracle EBS WHO audit columns and an OBJECT_VERSION_NUMBER for optimistic locking. The object is registered under FND Design Data as BEN.BEN_EXT_CRIT_VAL and is marked VALID in both 12.1.1 and 12.2.2.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table hangs off BEN_EXT_CRIT_TYP via EXT_CRIT_TYP_ID, behaving as an attribute-bearing child of a parent criteria definition rather than as an independent hub or a pure associative link.

Key Information Stored

  • EXT_CRIT_VAL_ID – System-generated surrogate primary key (NUMBER 15). It forms the single-column unique index BEN_EXT_CRIT_VAL_PK in release 12.1.1.
  • ZD_EDITION_NAME – Editioning column added in 12.2.x; combined with EXT_CRIT_VAL_ID it constitutes the composite unique key BEN_EXT_CRIT_VAL_PK documented for 12.2.2. This is the business-key candidate surfaced by the user's search term.
  • EXT_CRIT_TYP_ID – Foreign key to BEN_EXT_CRIT_TYP. This is the operative business key linking each value row to its owning criteria type.
  • VAL_1 and VAL_2 – VARCHAR2(90) columns holding the actual criteria values; VAL_2 typically carries the upper bound where a range is required.
  • BUSINESS_GROUP_ID – Foreign key to HR_ORGANIZATION_UNITS, partitioning the row by the enterprise business group.
  • EXT_CRIT_BG_ID – Records the criteria value's business group context.
  • LEGISLATION_CODE – VARCHAR2(30) identifying the statutory jurisdiction for which the value applies.
  • OBJECT_VERSION_NUMBER – Increments on each update, supporting concurrent modification control.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE – Standard WHO audit columns.

Common Use Cases and Queries

Typical use cases include auditing which value ranges are attached to a given criteria type, validating that no criteria type is orphaned, and reporting criteria values by legislation for statutory extracts.

Sample query joining values to their parent criteria type:

  • SELECT v.EXT_CRIT_VAL_ID, v.VAL_1, v.VAL_2, t.CRIT_TYP_NAME FROM BEN_EXT_CRIT_VAL v, BEN_EXT_CRIT_TYP t WHERE v.EXT_CRIT_TYP_ID = t.EXT_CRIT_TYP_ID AND v.BUSINESS_GROUP_ID = :p_bg_id ORDER BY t.CRIT_TYP_NAME, v.VAL_1;
  • SELECT * FROM BEN_EXT_CRIT_VAL WHERE EXT_CRIT_TYP_ID = :p_crit_typ_id AND LEGISLATION_CODE = :p_leg_code;
  • SELECT COUNT(*) FROM BEN_EXT_CRIT_VAL v WHERE NOT EXISTS (SELECT 1 FROM BEN_EXT_CRIT_TYP t WHERE t.EXT_CRIT_TYP_ID = v.EXT_CRIT_TYP_ID);

Because the table is editioned in 12.2.x, queries relying on the primary key should include ZD_EDITION_NAME when run against an edition-enabled environment.

Related Objects

  • BEN_EXT_CRIT_TYP – Parent criteria type table; joined on EXT_CRIT_TYP_ID.
  • BEN_EXT_CRIT_CMBN – Child combination table whose EXT_CRIT_VAL_ID references this table, linking criteria values into combined rules.
  • HR_ORGANIZATION_UNITS – Source of BUSINESS_GROUP_ID and EXT_CRIT_BG_ID.
  • FND_USER – Source of LAST_UPDATED_BY and CREATED_BY.
  • FND_LOGINS – Source of LAST_UPDATE_LOGIN.
  • BEN_EXT_CRIT_VAL_PK / BEN_EXT_CRIT_VAL_FK1 / BEN_EXT_CRIT_VAL_FK2 – Supporting indexes in APPS_TS_TX_IDX.