Results for “elig_mrtl_stat_cvg_id”

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

Overview

BEN_ELIG_MRTL_STAT_CVG_D is a reporting view owned by the APPS schema within the Oracle Advanced Benefits (BEN) module. It is classified under ETRM as "Retrofitted," indicating that the view was introduced or re-created to provide a stable, denormalized read interface over the marital status and dependent coverage eligibility configuration data stored in the underlying _F (Federation) tables of Oracle Advanced Benefits. Rather than exposing the raw foreign-key identifiers and code values held in the base eligibility tables, the view resolves those codes into user-readable meanings through outer joins to HR_LOOKUPS and to formula definitions in FF_FORMULAS_F. Its principal role is to support reporting, extracts, and integration scenarios where an implementer needs to list the marital-status and coverage start/through rules that apply to a dependent coverage eligibility profile, without having to manually decode lookup codes or join to formula tables.

Underlying Base Objects

The view is defined over several documented base objects:

• BEN_ELIG_MRTL_STAT_CVG_F — the driving table, holding eligibility rows that link a dependent coverage eligibility profile to a marital status and to coverage start/through codes and rule formulas.
• BEN_DPNT_CVG_ELIGY_PRFL_F — provides the eligibility profile name (exposed as DPNT_CVG_ELIGY_PRFL_NAME).
• HR_LOOKUPS — referenced three times to decode marital status (MAR_STATUS), coverage start (BEN_DPNT_CVG_STRT), and coverage through (BEN_DPNT_CVG_END) codes into their meanings.
• FF_FORMULAS_F — referenced twice to resolve the coverage start and coverage through rule formula IDs into formula names.
• FND_USER — provides the user name associated with the last update.
• HR_API — listed as a referenced object, typically supporting the secured lookups used for code resolution.

All join conditions are expressed as outer joins (note the (+) operator), so eligibility rows are returned even when a lookup code, profile, formula, or user record is null or not yet populated.

Key Columns

• ROW_ID — the ROWID of the base eligibility row, useful as a unique identifier in extracts.
• ELIG_MRTL_STAT_CVG_ID — the primary identifier of the marital status / coverage eligibility record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked effective range of the eligibility row, also used to select the correct formula version via the effective date range on FF_FORMULAS_F.
• DPNT_CVG_ELIGY_PRFL_NAME — the name of the dependent coverage eligibility profile to which the rule applies.
• MRTL_STAT_MEANING — the decoded marital status (e.g., Married, Single) resolved from the MAR_STATUS lookup.
• CVG_STRT_MEANING / CVG_THRU_MEANING — decoded coverage start and coverage through values.
• CVG_STRT_NAME / CVG_THRU_NAME — the formula names for the coverage start and coverage through rules.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns identifying when and by whom the record was last modified.

Common Use Cases and Queries

Typical uses include auditing which marital statuses trigger a specific dependent coverage rule, validating that coverage start/through formulas are correctly attached to each eligibility profile, and building extracts for benefits configuration documentation. A representative query follows:

• SELECT d.DPNT_CVG_ELIGY_PRFL_NAME, d.MRTL_STAT_MEANING, d.CVG_STRT_MEANING, d.CVG_STRT_NAME, d.CVG_THRU_MEANING, d.CVG_THRU_NAME, d.EFFECTIVE_START_DATE, d.EFFECTIVE_END_DATE FROM BEN_ELIG_MRTL_STAT_CVG_D d WHERE SYSDATE BETWEEN d.EFFECTIVE_START_DATE AND d.EFFECTIVE_END_DATE ORDER BY d.DPNT_CVG_ELIGY_PRFL_NAME;
• Filtering by MRTL_STAT_MEANING to report all profiles that grant dependent coverage based on marital status.
• Joining ELIG_MRTL_STAT_CVG_ID back to configuration tables for drill-down or integration mapping.

Because the view applies outer joins throughout, care should be taken when filtering on any decoded column, as null-returning rows may be excluded unintentionally.