Results for “ben_elig_mltry_stat_cvg_d”

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

Overview

The BEN_ELIG_MLTRY_STAT_CVG_D view, owned by the APPS schema, is a denormalized reporting object within Oracle Advanced Benefits (BEN). Its name encodes its purpose: eligibility (ELIG), military status (MLTRY_STAT), and coverage (CVG), with the trailing _D denoting a denormalized descriptive view. It is documented as "Retrofitted," indicating it was regenerated or restored to support backward compatibility and the Oracle EBS 12.1.1 / 12.2.2 data model. The view consolidates the base eligibility rule rows from BEN_ELIG_MLTRY_STAT_CVG_F and enriches them with decoded meanings, plan profile names, and formula identifiers, producing a human-readable result set. The user search term cvg_strt_meaning corresponds directly to one of its exposed columns, CVG_STRT_MEANING, which decodes the dependent coverage start rule into its descriptive lookup meaning. This view plays a supporting role in reporting and integrations where business users require readable eligibility definitions rather than raw lookup codes and ID values.

Underlying Base Objects

The view is defined as an outer-join query over several synonym-referenced base objects and supporting lookups:

• BEN_ELIG_MLTRY_STAT_CVG_F — the driving eligibility rule table (aliased EMC) holding the military-status dependent coverage configuration rows.
• BEN_DPNT_CVG_ELIGY_PRFL_F — supplies the dependent coverage eligibility profile name via DPNT_CVG_ELIGY_PRFL_ID.
• FND_USER — resolves LAST_UPDATED_BY to a user identity.
• HR_LOOKUPS — joined three times to decode the military service code (MILITARY_SERVICE), the coverage start code (BEN_DPNT_CVG_STRT), and the coverage end code (BEN_DPNT_CVG_END).
• FF_FORMULAS_F — joined twice to resolve the coverage start and end rule formulas (CVG_STRT_RL, CVG_THRU_RL), with effective-date range matching.
• HR_API (package) is referenced in the ETRM dependency list for the benefits API layer.

Key Columns

• ELIG_MLTRY_STAT_CVG_ID — primary identifier for the eligibility military-status coverage row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity of the rule.
• DPNT_CVG_ELIGY_PRFL_NAME — descriptive name of the dependent coverage eligibility profile.
• MLTRY_STAT_MEANING — decoded military service status.
• CVG_STRT_MEANING — decoded meaning of the dependent coverage start code; the column matching the user's search.
• CVG_STRT_NAME — formula name governing the coverage start rule.
• CVG_THRU_MEANING / CVG_THRU_NAME — decoded coverage end meaning and its formula name.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns.

Common Use Cases and Queries

This view is typically used to audit or report military-status-based dependent coverage eligibility and to trace which formula drives coverage start or end. A representative query filtering on the searched column:

• SELECT elig_mltry_stat_cvg_id, dpgnt_cvg_eligy_prfl_name, mltry_stat_meaning, cvg_strt_meaning, cvg_strt_name FROM ben_elig_mltry_stat_cvg_d WHERE cvg_strt_meaning IS NOT NULL;
• Join to PER_ALL_PEOPLE_F or benefit enrollment tables to correlate eligibility rules with enrolled dependents.
• Filter by SYSDATE BETWEEN effective_start_date AND effective_end_date to isolate currently active rules.