Results for “ben_elig_per_f_pk”

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

Overview

BEN_ELIG_PER_F is a transactional table in the Oracle Advanced Benefits (BEN) module that records participant eligibility determinations. Each row represents the outcome of evaluating a person against a benefit program, plan-in-program, or plan eligibility rule set, and stores the resulting eligible or ineligible status together with the facts and derived values that drove that determination. In Oracle EBS 12.1.1 and 12.2.2 the table is owned by the BEN schema and contains 107 documented columns. It is a core runtime table: eligibility must be resolved before enrollment opportunities can be created and before plan enrollment or life event processing can proceed.

Heuristic Data Vault classification for this object is satellite-leaning. The table is keyed by a surrogate (ELIG_PER_ID) plus effective dating, which is characteristic of a versioned descriptive satellite attached to a person and eligibility-rule identity. Modeling guidance would be to treat it as a satellite around the participant/eligibility-rule linkage rather than as an independent hub.

Key Information Stored

The primary key is BEN_ELIG_PER_F_PK, composed of ELIG_PER_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The same three columns form the sole documented unique index and therefore the business-key candidate set; ELIG_PER_ID is the surrogate, while the effective date pair provides the temporal key.

Columns most relevant to interpretation include:

• ELIG_FLAG — the outcome of the eligibility evaluation (eligible / not eligible).
• PERSON_ID, BUSINESS_GROUP_ID — the participant and the legislation/operating unit context.
• PGM_ID, PL_ID, PLIP_ID, PTIP_ID, LER_ID, PER_IN_LER_ID — the program, plan, plan-in-program, program-in-plan type, life event reason, and person-in-life-event-reason identifiers that scope the rule being evaluated.
• ELIG_PER_ID and the effective date pair — the surrogate and temporal identity of the record.
• INELG_RSN_CD — reason code when the participant is found ineligible.
• AGE_VAL, LOS_VAL, HRS_WKD_VAL, PCT_FL_TM_VAL, COMP_REF_AMT — the measured values (age, length of service, hours worked, percent full time, compensation reference) compared against plan criteria.
• AGE_UOM, LOS_UOM, COMP_REF_UOM, HRS_WKD_BNDRY_PERD_CD — units and boundary-period qualifiers for those measurements.
• DSTR_RSTCN_FLAG, PL_WVD_FLAG, PRTN_OVRIDN_FLAG, FRZ_* flags — restriction, waiver, override, and freeze indicators that alter or suppress the computed result.
• WAIT_PERD_STRT_DT, WAIT_PERD_CMPLTN_DT — waiting-period window boundaries.
• LAST_UPDATE_DATE, CREATED_BY, OBJECT_VERSION_NUMBER, REQUEST_ID, PROGRAM_ID — standard EBS audit and concurrent-program columns.

Common Use Cases and Queries

Typical reporting needs include identifying who is currently eligible for a plan, why a participant was rejected, and how frozen or overridden records affect downstream enrollment. A current-row query filters on the effective dates:

• Eligible participants for a plan: SELECT person_id, pl_id, elig_flag FROM ben.ben_elig_per_f WHERE elig_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Ineligibility analysis: aggregate by INELG_RSN_CD for a program or plan to expose the dominant failure reasons.
• Rule diagnostics: compare AGE_VAL, LOS_VAL, and HRS_WKD_VAL against plan criteria to explain borderline determinations.
• Freeze and override auditing: select rows where FRZ_LOS_FLAG, FRZ_AGE_FLAG, or PRTN_OVRIDN_FLAG is set, joined to PRTN_OVRIDN_RSN_CD.

Date-effective joins should always constrain both EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to avoid returning historical versions.

Related Objects

The documented foreign key relates ELIG_PER_ID rows to BEN_PER_IN_LER through PER_IN_LER_ID, linking an eligibility result to the specific person/life-event-reason context that triggered it. Other significant associations inferred from the identifier columns include BEN_PGM_F (via PGM_ID), BEN_PL_F and BEN_PLIP_F (via PL_ID and PLIP_ID), BEN_PTIP_F (via PTIP_ID), BEN_LER_F (via LER_ID), and PER_ALL_PEOPLE_F (via PERSON_ID). Participant eligibility output also feeds enrollment opportunity and enrollment processing in the BEN module, which consumes these rows to decide which plans a participant may elect.