Search Results ben_ext_crit_typ




Overview
Explore in AI Chat

Overview

BEN_EXT_CRIT_TYP is a table within the BEN schema (Advanced Benefits module) of Oracle E-Business Suite, documented as valid in both 12.1.1 and 12.2.2. The table stores Extract Criteria type definitions — the configuration metadata that governs how benefits extract processes select and qualify records for downstream processing, carriers, or third-party administrators. In the Advanced Benefits architecture, extract criteria act as the filtering rules applied against participant, plan, and enrollment data during an extract run, and BEN_EXT_CRIT_TYP provides the type-level definition to which those rules are attached.

Based on the foreign-key structure documented in the ETRM relationship data, the table is heuristically classified as satellite-leaning. In Data Vault modeling terms, a satellite captures descriptive attributes that change over time around a parent business key; here, the criteria type attributes orbit the extract criteria profile represented by BEN_EXT_CRIT_PRFL. This classification is a modeling suggestion derived from the FK topology rather than a formal Oracle designation.

Key Information Stored

The table contains 13 documented columns in the 12.2.2 physical schema. The most significant are:

  • EXT_CRIT_TYP_ID — the surrogate primary key of the table, defined by the unique index BEN_EXT_CRIT_TYP_PK.
  • EXT_CRIT_PRFL_ID — foreign key to BEN_EXT_CRIT_PRFL, linking each criteria type to its parent extract criteria profile.
  • CRIT_TYP_CD — the criteria type code, a business-meaningful discriminator indicating the category of extraction rule (for example, participant-based, plan-based, or eligibility-based).
  • EXCLD_FLAG — indicates whether the criteria type operates as an exclusion (records matching are omitted from the extract) rather than an inclusion filter.
  • LEGISLATION_CODE — identifies the legislation or regulatory jurisdiction under which the criteria type applies, important for multi-country benefits configurations.
  • BUSINESS_GROUP_ID — the operating unit / business group that owns the record, supporting multi-tenant data partitioning within a single instance.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications framework to detect concurrent updates.

The unique index BEN_EXT_CRIT_TYP_PK spans EXT_CRIT_TYP_ID together with ZD_EDITION_NAME, which supports the edition-based redefinition strategy used in 12.2.2 online patching. Audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) are present per the standard EBS WHO column convention.

Common Use Cases and Queries

Typical uses include diagnosing why a participant was or was not included in a benefits extract, auditing the criteria configuration for a given profile, and reporting on extract rule coverage by legislation.

SELECT ect.ext_crit_typ_id,
       ect.crit_typ_cd,
       ect.excld_flag,
       ect.legislation_code
FROM   ben.ben_ext_crit_typ ect
WHERE  ect.ext_crit_prfl_id = :profile_id
AND    ect.business_group_id = :business_group_id;

Joining to the profile and value tables supports end-to-end extract diagnostics:

SELECT ecp.ext_crit_prfl_id,
       ect.crit_typ_cd,
       ecv.ext_crit_val
FROM   ben.ben_ext_crit_prfl ecp,
       ben.ben_ext_crit_typ  ect,
       ben.ben_ext_crit_val  ecv
WHERE  ect.ext_crit_prfl_id = ecp.ext_crit_prfl_id
AND    ecv.ext_crit_typ_id  = ect.ext_crit_typ_id;

Filtering on EXCLD_FLAG = 'Y' produces a report of all exclusion rules, which is a frequent audit request in benefits compliance reviews.

Related Objects

  • BEN_EXT_CRIT_PRFL — parent extract criteria profile; joined via EXT_CRIT_PRFL_ID. Every criteria type must resolve to a profile.
  • BEN_EXT_CRIT_VAL — child table holding the actual criteria values; joined via BEN_EXT_CRIT_VAL.EXT_CRIT_TYP_ID = BEN_EXT_CRIT_TYP.EXT_CRIT_TYP_ID.
  • BEN_EXT_CRIT_TYP_PK — the unique index/PK constraint enforcing row identity on EXT_CRIT_TYP_ID and ZD_EDITION_NAME.
  • BEN_EXT_RUN_RSLT and other BEN extract run/result objects — downstream consumers that resolve criteria types during an extract execution.
  • BEN_EXT_PTIP_* participant extract objects — reference the criteria definitions when evaluating participant-level inclusion.

Because extract processing chains profile → type → value, any customization or data fix touching BEN_EXT_CRIT_TYP should always be validated against the two dependent tables to preserve referential integrity.