Results for “ben_elig_per_opt_f”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BEN_ELIG_PER_OPT_F is a core configuration and eligibility table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. The table stores participant eligibility and ineligibility rules for a specific option within a plan. In the Oracle Benefits data model, plans may offer one or more options (for example, different coverage tiers or provider choices), and this table defines the criteria under which a person becomes eligible for, or is excluded from, a given option. Records are date-tracked through EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, allowing historical and future-dated eligibility definitions to coexist.

The ETRM documentation classifies this object, using a heuristic derived from its foreign key structure, as satellite-leaning within a Data Vault modeling convention. This suggests BEN_ELIG_PER_OPT_F behaves primarily as a descriptive, attribute-bearing satellite surrounding a central eligibility business key, rather than acting as a hub or a pure link table. Practitioners designing downstream analytics models may treat it accordingly, anchoring it to its parent eligibility entity.

Key Information Stored

The table is physically owned by the BEN schema and contains 94 documented columns. The single-column surrogate primary key is ELIG_PER_OPT_ID, while the unique index BEN_ELIG_PER_OPT_F_PK (ELIG_PER_OPT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) represents the effective-dated business-key candidate. The metadata also lists a second primary key entry, BEN_ELIG_PER_F_PK (ELIG_PER_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), which ties the option-level rule back to its parent eligibility record.

• ELIG_PER_OPT_ID – Surrogate identifier for the option-level eligibility row.
• ELIG_PER_ID – Foreign reference linking to the parent eligibility definition.
• PER_IN_LER_ID – Links to BEN_PER_IN_LER, tying the rule to a person-in-life-event record.
• OPT_ID – Identifies the specific plan option to which the rule applies.
• ELIG_FLAG – Indicates whether the record expresses eligibility or ineligibility.
• INELG_RSN_CD – Reason code explaining an ineligibility outcome.
• OIPL_ORDR_NUM – Order number controlling evaluation sequence for option-in-plan logic.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – Date-tracked validity window.
• PRTN_OVRIDN_FLAG / PRTN_OVRIDN_RSN_CD / PRTN_OVRIDN_THRU_DT – Partition override controls and reason.
• AGE_VAL / AGE_UOM – Age threshold value and unit of measure.
• LOS_VAL / LOS_UOM – Length-of-service threshold value and unit.
• HRS_WKD_VAL / HRS_WKD_BNDRY_PERD_CD – Hours-worked threshold and boundary period.
• PCT_FL_TM_VAL – Percentage full-time threshold.
• OVRID_SVC_DT – Override service date for eligibility computation.
• WAIT_PERD_STRT_DT / WAIT_PERD_CMPLTN_DT – Waiting-period start and completion dates.

Common Use Cases and Queries

Typical use cases include auditing option-level eligibility rules, reproducing eligibility determinations for a given enrollment period, and extracting configuration for migration or cloning between environments. Analysts frequently join this table to BEN_PER_IN_LER to resolve participant-specific outcomes, and to plan/option definitions to produce readable rule reports.

A representative query returning current option eligibility rules might resemble:

• SELECT e.EXTRACT_DATE, e.ELIG_PER_OPT_ID, e.ELIG_FLAG, e.OPT_ID, e.INELG_RSN_CD FROM BEN.BEN_ELIG_PER_OPT_F e WHERE SYSDATE BETWEEN e.EFFECTIVE_START_DATE AND e.EFFECTIVE_END_DATE;
• Joining to the person-in-life-event table: SELECT e.ELIG_PER_OPT_ID, e.PER_IN_LER_ID, p.* FROM BEN.BEN_ELIG_PER_OPT_F e JOIN BEN.BEN_PER_IN_LER p ON e.PER_IN_LER_ID = p.PER_IN_LER_ID;

Reporting scenarios include eligibility exception analysis (filtering on INELG_RSN_CD), threshold configuration review (AGE_VAL, LOS_VAL, HRS_WKD_VAL, PCT_FL_TM_VAL), and effective-dating integrity checks confirming no overlapping validity windows per ELIG_PER_OPT_ID.

Related Objects

The most significant related object documented is BEN_PER_IN_LER, referenced through the foreign key BEN_ELIG_PER_OPT_F.PER_IN_LER_ID, which represents the participant/life-event linkage that drives eligibility outcomes. The parent eligibility table implied by ELIG_PER_ID (through BEN_ELIG_PER_F_PK) anchors the option rule to the broader eligibility record. Plan and option definitions referenced via OPT_ID provide the descriptive context for the rules stored here. Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, OBJECT_VERSION_NUMBER) are populated by concurrent programs and the Oracle Applications framework, and downstream eligibility processing and enrollment APIs within the Advanced Benefits module consume these rows when evaluating participant elections.