Search Results ben_efc_exclusions




Overview
Explore in AI Chat

Overview

BEN.BEN_EFC_EXCLUSIONS is a transactional table within the Oracle Advanced Benefits (BEN) module. It stores exclusion rules applied to benefits eligibility and enrollment processing — specifically, records that define which persons, organizations, or payroll entities are excluded from a given eligibility/flex-credit action. The table operates in the context of the Extract, Transform, and Load (ETL) style engine that BEN uses to evaluate eligibility criteria (EFC denotes "Eligibility, Flex, and Compensation" processing logic). Each row captures a discrete exclusion instruction associated with an EFC action, attached to a particular entity via ENT_SCODE and a positional or reference key PK_ID.

Under a heuristic Data Vault classification mined from the foreign-key structure, this object presents as standalone — it does not participate as an obvious hub, link, or satellite in a subject-oriented integration model. In practice, it functions as a transactional satellite-like record (event data capturing exclusions) linked to an EFC action, but the metadata does not document a formal parent link, so treat the hub/satellite designation as a modeling suggestion rather than a schema-enforced relationship.

Key Information Stored

The table is documented with 15 physical columns. The most important operational columns include:

  • EFC_ACTION_ID — Identifies the parent EFC action (eligibility, flex-credit, or compensation processing run) to which the exclusion belongs; a leading component of the composite primary key.
  • ENT_SCODE — The entity short code identifying the entity type (person, organization, payroll, etc.) the exclusion applies to.
  • PK_ID — The specific entity instance identifier. The user's search term centers on this column: it is the "primary key" of the referenced business entity within the exclusion row, not the surrogate key of this table.
  • EXCLUSION_TYPE and EXCLUSION_CODE — Classify the nature of the exclusion and the specific business code applied.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effectivity range, both part of the composite primary key, enabling datetracked history of exclusion changes.
  • OLD_VAL / NEW_VAL — Prior and current values, supporting audit trails when exclusion rules change.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework to detect concurrent updates.
  • BUSINESS_GROUP_ID — Multi-tenant discriminator separating enterprise/legislative data partitions.
  • Standard Who columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY.

The surrogate/business-key candidate is BEN_EFC_EXCLUSIONS_PK, composed of (EFC_ACTION_ID, ENT_SCODE, PK_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). This is not a single-column surrogate; it is a composite business key. There is no separate system-generated sequence column documented, so PK_ID should not be mistaken for an internal row identifier — it references the excluded entity's key, scoped by ENT_SCODE.

Common Use Cases and Queries

Typical usage includes auditing which persons or entities were excluded from a benefit action, reconstructing eligibility outcomes as of a past date, and reconciling exclusion logic against enrollment results.

  • Retrieve active exclusions for a given action: 
    SELECT ef.efc_action_id, ef.ent_scode, ef.pk_id,
       ef.exclusion_type, ef.exclusion_code
FROM   ben.ben_efc_exclusions ef
WHERE  ef.efc_action_id = :action_id
AND    TRUNC(SYSDATE) BETWEEN ef.effective_start_date
                          AND ef.effective_end_date
AND    ef.business_group_id = :bg_id;
  • Audit change history for a specific excluded entity using OLD_VAL/NEW_VAL.
  • Point-in-time reporting by filtering on the effectivity range within the composite primary key.

Because PK_ID is part of the business key rather than a system surrogate, joins to entity tables must combine ENT_SCODE with PK_ID to resolve the correct target, and must also honor the effectivity dates to avoid returning overlapping historical rows.

Related Objects

Relationship data classifies the table as standalone, so no foreign keys are formally documented. The most significant logical references, based on the documented columns, are:

  • Parent EFC action table — joined on EFC_ACTION_ID.
  • Entity definition tables — resolved via ENT_SCODE combined with PK_ID.
  • FND/standard Who and Business Group tables — referenced through BUSINESS_GROUP_ID, CREATED_BY, and LAST_UPDATED_BY.
  • BEN eligibility and enrollment result tables that consume exclusion logic during processing.

Consumers should treat the composite primary key and effectivity range as the authoritative access path, since no single surrogate identifier or enforced referential constraint is documented.