Results for “inelg_rsn_cd”

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

Overview

BEN_ELIG_DPNT is a table in the BEN schema (Advanced Benefits) that stores eligible dependent records. In Oracle EBS 12.1.1 and 12.2.2, it serves as the link between a person's eligibility in a Life Event or eligibility configuration and the dependent (or other covered person) who qualifies for a benefit plan, program, or option. The table holds the eligibility start and end dates, an ineligibility flag and reason, and a large set of descriptive flexfield (DFF) attribute columns (EGD_ATTRIBUTE1 through EGD_ATTRIBUTE30) for capturing client-specific data.

The heuristic Data Vault classification mined from the FK structure is link. This is a modeling suggestion: the table sits at the intersection of a person-in-LER (PER_IN_LER_ID), an eligible plan/electable choice (ELIG_PER_ELCTBL_CHC_ID), and a business group (BUSINESS_GROUP_ID), making it a natural junction between eligibility and covered persons rather than a standalone hub or a descriptive satellite.

Key Information Stored

The surrogate primary key is ELIG_DPNT_ID, enforced by the unique index BEN_ELIG_DPNT_PK. There is no separate documented business-key unique index, so ELIG_DPNT_ID is both the surrogate and the primary lookup value (the term the user searched, "elig_dpnt_id," refers to this column).

The most significant columns are:

• ELIG_DPNT_ID — surrogate primary key identifying each eligible dependent row.
• ELIG_PER_ELCTBL_CHC_ID — FK to BEN_ELIG_PER_ELCTBL_CHC, tying the dependent to a specific eligible electable choice.
• PER_IN_LER_ID — FK to BEN_PER_IN_LER, the person's eligibility record within a Life Event Reason.
• BUSINESS_GROUP_ID — FK to HR_ALL_ORGANIZATION_UNITS, identifying the business group/legal entity scope.
• DPNT_PERSON_ID — the dependent person's identifier (from PER_ALL_PEOPLE_F).
• ELIG_STRT_DT / ELIG_THRU_DT — the effective eligibility window.
• DPNT_INELIG_FLAG — indicates the dependent is not eligible.
• INELG_RSN_CD — reason code when ineligibility applies.
• OVRDN_FLAG / OVRDN_THRU_DT — manual override indicator and expiry.
• ELIG_PER_ID, ELIG_PER_OPT_ID, ELIG_CVRD_DPNT_ID — additional linkage columns to person and option eligibility and covered dependent records.
• OBJECT_VERSION_NUMBER and the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, etc.) — audit and concurrency control.

Common Use Cases and Queries

Typical scenarios include determining which dependents are eligible for a plan, auditing ineligibility overrides, and reconciling eligible dependents against actual covered dependents in enrollments. A common query pattern joins to the eligible electable choice and the person-in-LER parents:

• SELECT d.elig_dpnt_id, d.dpnt_person_id, d.elig_strt_dt, d.elig_thru_dt, d.dpnt_inelig_flag FROM ben.ben_elig_dpnt d WHERE d.elig_per_elctbl_chc_id = :chc_id;
• Reporting uses frequently filter on ELIG_STRT_DT/ELIG_THRU_DT for a given AS_OF_DATE, or on DPNT_INELIG_FLAG = 'Y' to isolate overridden or excluded dependents.
• Reconciliation against BEN_ELIG_DPNT_RBV (a view built on this table) is standard for dependent eligibility reporting.
• Join to BEN_PER_IN_LER and BEN_ELIG_PER_ELCTBL_CHC to trace eligibility back to a life event or a plan configuration.

Related Objects

The FK/relationship metadata identifies the following key related objects:

• BEN_ELIG_PER_ELCTBL_CHC — joined on ELIG_PER_ELCTBL_CHC_ID; the eligible electable choice this dependent qualifies for.
• BEN_PER_IN_LER — joined on PER_IN_LER_ID; the person's eligibility record within a Life Event Reason.
• HR_ALL_ORGANIZATION_UNITS — joined on BUSINESS_GROUP_ID; the business group/legal entity.
• BEN_ELIG_DPNT_RBV — a view referencing BEN_ELIG_DPNT via ELIG_DPNT_ID; used for reporting on eligible dependents.
• Additional linkage columns ELIG_PER_ID, ELIG_PER_OPT_ID, and ELIG_CVRD_DPNT_ID connect this table to person eligibility, option eligibility, and covered dependent records respectively.