Results for “ben_elig_mrtl_stat_cvg_f”

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

Overview

BEN_ELIG_MRTL_STAT_CVG_F is a core configuration 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 the marital status criteria that determine whether a dependent is included in or excluded from a particular compensation object, such as a benefit plan, program, or eligibility profile. In effect, it defines the marriage-based eligibility rules that drive dependent coverage determinations during the benefits eligibility engine's evaluation of a participant's dependents.

The table follows Oracle's standard effective-dated (date-tracked) design, employing the _F suffix and EFFECTIVE_START_DATE / EFFECTIVE_END_DATE columns to support datetracked (DATE_TRACK) history. The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the table is modeled as neither a hub, link, nor satellite in a strict Data Vault sense; it is a self-contained reference/rule configuration entity without enforced outbound foreign keys in the documented metadata. This classification is offered as a modeling suggestion rather than a physical constraint.

Key Information Stored

The documented physical schema contains 47 columns. The most significant are summarized below.

• ELIG_MRTL_STAT_CVG_ID — Surrogate primary key identifying each marital status coverage eligibility rule row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-track columns defining the validity window of each rule version; these form part of the primary key.
• BUSINESS_GROUP_ID — Identifies the HR business group (enterprise) that owns the configuration row.
• MRTL_STAT_CD — The marital status code value that the rule tests against (for example, married, single, divorced).
• CVG_STRT_CD / CVG_THRU_CD — Coverage start and coverage through codes that determine when dependent coverage begins and ends based on the marital status criterion.
• CVG_STRT_RL / CVG_THRU_RL — Coverage start and coverage through rules that qualify how the start and end codes are applied.
• DPNT_CVG_ELIGY_PRFL_ID — Reference to the dependent coverage eligibility profile that this marital status rule serves.
• EMS_ATTRIBUTE_CATEGORY and EMS_ATTRIBUTE1 through EMS_ATTRIBUTE30 — The descriptive flexfield (DFF) attribute segment values, providing extensible customer-defined context.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — Standard Oracle WHO columns supporting auditability and optimistic locking.

The primary key is BEN_ELIG_MRTL_STAT_CVG_F_PK, comprising ELIG_MRTL_STAT_CVG_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This composite index also serves as the unique business-key candidate, enforcing one rule version per surrogate identifier per effective date range.

Common Use Cases and Queries

Benefits administrators and technical consultants query this table to audit, extract, or troubleshoot marital status eligibility rules. Typical reporting includes reconciling configured rules against plan documentation and validating dependent coverage outcomes.

A common pattern retrieves currently effective rules for a business group:

• Filter by BUSINESS_GROUP_ID and compare SYSDATE against the effective date range (EFFECTIVE_START_DATE <= TRUNC(SYSDATE) AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE >= TRUNC(SYSDATE))).
• Join to the dependent coverage eligibility profile via DPNT_CVG_ELIGY_PRFL_ID to trace the rule back to its owning profile.
• Report on MRTL_STAT_CD distributions to determine how many rules exist per marital status.
• Query DFF segments (EMS_ATTRIBUTE_CATEGORY and EMS_ATTRIBUTE1..30) to expose customer-defined configuration.
• Perform date-tracked history analysis by ordering rows by ELIG_MRTL_STAT_CVG_ID and EFFECTIVE_START_DATE to reconstruct rule evolution.

Because the table is date-tracked, queries should always constrain on the effective window; failing to do so returns multiple row versions per rule and inflates result counts.

Related Objects

The documented metadata classifies this object as standalone, so no enforced foreign keys are recorded. The most significant logical relationships are:

• BEN_ELIG_MRTL_STAT_CVG_F_PK — The primary key unique index on (ELIG_MRTL_STAT_CVG_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE); the principal dependent structure for uniqueness and row identification.
• Dependent coverage eligibility profile table — Referenced through DPNT_CVG_ELIGY_PRFL_ID, the profile that consumes the marital status rule.
• Other BEN eligibility rule configuration tables that share the same eligibility profile key pattern and are evaluated together by the benefits eligibility engine.
• Standard WHO audit columns link conceptually to the FND user and concurrent program context (LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN).

Because the object is standalone and date-tracked, dependencies are logical rather than enforced; integrators should validate referential consistency between ELIG_MRTL_STAT_CVG_ID and its consuming eligibility profiles at the application layer.