Results for “ben_elig_cvrd_dpnt_f”

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

Overview

BEN_ELIG_CVRD_DPNT_F is a date-tracked (the "_F" suffix denotes a datetrack/effectivity-enabled table) transactional entity within the Oracle Advanced Benefits (BEN) module, owned by the APPS/BEN schema. It stores the records of eligible covered dependents — the dependents that a participant may elect to cover under one or more benefit plans for a given eligibility evaluation. Rows in this table represent the intersection of a person enrolled in a Life Event or eligibility group (PER_IN_LER) and an eligible plan/electable choice, capturing whether that dependent is covered, pending, or overridden for the effective period.

Because the table carries a composite, date-effective primary key plus two foreign keys to parent entities, it exhibits the characteristics of a link table in a Data Vault-style model. Its role positions it as the grain-level junction that binds participants, dependents, and electable coverage choices together — effectively a relationship hub between the person, the dependent person, and the enrollment electable choice.

Key Information Stored

The primary key BEN_ELIG_CVRD_DPNT_F_PK is composite, comprising ELIG_CVRD_DPNT_ID (the surrogate identifier), EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE — reinforcing the datetrack semantics. There is no separate single-column unique index documented; the composite key itself is the business-key candidate.

• ELIG_CVRD_DPNT_ID — surrogate identifier for the covered dependent record.
• DPNT_PERSON_ID — the dependent person's identifier.
• PER_IN_LER_ID — FK to BEN_PER_IN_LER, the person-in-eligibility-group record that qualifies the dependent.
• ELIG_PER_ELCTBL_CHC_ID — FK to BEN_ELIG_PER_ELCTBL_CHC, the electable choice (plan/option) the dependent may be covered under.
• BUSINESS_GROUP_ID — multi-tenant / enterprise grouping key.
• CVG_PNDG_FLAG and OVRDN_FLAG — indicate pending coverage and manual override status.
• CVG_STRT_DT, CVG_THRU_DT, OVRDN_THRU_DT — actual coverage start, coverage end, and override end dates (distinct from the row effectivity dates).
• PRTT_ENRT_RSLT_ID — link to the participant enrollment result that produced this covered dependent.
• PDP_ATTRIBUTE_CATEGORY and PDP_ATTRIBUTE1..30 — descriptive flexfield (DFF) segments for customer-defined attributes.
• Standard WHO columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, OBJECT_VERSION_NUMBER, plus concurrent-program identifiers (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID).

Common Use Cases and Queries

The most frequent scenario is reporting current covered dependents per plan or per participant, typically filtered by a valid date range. A representative query joins this table to BEN_PER_IN_LER and BEN_ELIG_PER_ELCTBL_CHC:

• Active covered dependents as of a date: SELECT d.ELIG_CVRD_DPNT_ID, d.DPNT_PERSON_ID, d.CVG_STRT_DT, d.CVG_THRU_DT FROM BEN.BEN_ELIG_CVRD_DPNT_F d WHERE TRUNC(SYSDATE) BETWEEN d.EFFECTIVE_START_DATE AND d.EFFECTIVE_END_DATE;
• Pending vs. finalized coverage: filter on CVG_PNDG_FLAG = 'Y' to isolate coverage not yet confirmed.
• Override reconciliation: use OVRDN_FLAG and OVRDN_THRU_DT to audit manually overridden dependents.
• Enrollment reconciliation: join on PRTT_ENRT_RSLT_ID to trace which enrollment run generated the covered dependent.
• DFF reporting: query PDP_ATTRIBUTE_CATEGORY and the numbered PDP_ATTRIBUTE columns to surface client-defined dependent attributes.

Related Objects

• BEN_ELIG_PER_ELCTBL_CHC — joined via ELIG_PER_ELCTBL_CHC_ID; defines the electable plan/option the dependent is eligible under.
• BEN_PER_IN_LER — joined via PER_IN_LER_ID; the person's eligibility/lear record.
• PER_ALL_PEOPLE_F — source of the dependent person details via DPNT_PERSON_ID.
• BEN_PRTT_ENRT_RSLT_F — participant enrollment result correlated through PRTT_ENRT_RSLT_ID.
• BEN_ELIG_CVRD_DPNT_F_PK — the composite primary key index enforcing datetrack uniqueness.

Together these relationships establish BEN_ELIG_CVRD_DPNT_F as the authoritative junction for dependent-level benefit eligibility and coverage decisions in Advanced Benefits.