Search Results ben_elig_mltry_stat_cvg_f




Overview
Explore in AI Chat

Overview

BEN_ELIG_MLTRY_STAT_CVG_F is a dated (date-tracked) table in the BEN schema of Oracle Advanced Benefits (Oracle EBS 12.1.1 and 12.2.2). It stores the configuration that identifies the dependent's military status required for inclusion in, or exclusion from, a compensation object. In practice, this table lets an eligibility profile evaluate whether a dependent's military status satisfies the criteria defined for a benefits offering, plan, or program. Each row represents one eligibility rule segment keyed by an eligibility military-status coverage identifier and its effective date range.

The documented physical schema contains 47 columns. The primary key is BEN_ELIG_MLTRY_STAT_CVG_F_PK, defined on the columns ELIG_MLTRY_STAT_CVG_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. Because the primary key embeds effective dating, the table follows Oracle's standard date-tracked "table" (rather than "_F" being a purely static construct) convention, where multiple versions of the same logical rule are preserved over time. Per the heuristic Data Vault classification derived from the foreign-key structure, the object behaves as a standalone table — effectively a satellite-like record capturing military-status rule attributes for a given eligibility military-status coverage identifier. This is a modeling suggestion rather than a declared classification.

Key Information Stored

The 47 documented columns fall into several recognizable groups. The most important include:

  • ELIG_MLTRY_STAT_CVG_ID — surrogate identifier for the eligibility military-status coverage rule; a component of the primary key.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective date range that, together with ELIG_MLTRY_STAT_CVG_ID, forms the primary key (BEN_ELIG_MLTRY_STAT_CVG_F_PK).
  • BUSINESS_GROUP_ID — partitions the rule to a specific business group (legislative data group context).
  • MLTRY_STAT_CD — the military status code that is evaluated for the dependent.
  • CVG_STRT_CD / CVG_THRU_CD — coverage start and coverage-through codes that determine the interval boundaries for the rule.
  • CVG_STRT_RL / CVG_THRU_RL — the corresponding start and through rule values that govern how the interval is applied.
  • DPNT_CVG_ELIGY_PRFL_ID — the dependent coverage eligibility profile to which this military-status rule is attached.
  • EMC_ATTRIBUTE_CATEGORY and EMC_ATTRIBUTE1 … EMC_ATTRIBUTE30 — a configurable descriptive-flexfield set for storing additional rule attributes.
  • OBJECT_VERSION_NUMBER — the optimistic-locking version counter used by the Oracle AOL/BC4J layer.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard WHO audit columns.

The surrogate/business-key distinction is significant: the unique index BEN_ELIG_MLTRY_STAT_CVG_F_PK applies to (ELIG_MLTRY_STAT_CVG_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), which serves as the business-key candidate that uniquely identifies a versioned rule. This should not be confused with a single-column surrogate; here the date components are integral to uniqueness.

Common Use Cases and Queries

Typical scenarios include validating a dependent's military-status eligibility before enrollment, troubleshooting why a dependent was excluded from a plan, and auditing configuration of dependent coverage eligibility profiles. A current-version query pattern is:

  • SELECT el.* FROM ben.ben_elig_mltry_stat_cvg_f el WHERE el.elig_mltry_stat_cvg_id = :p_id AND TRUNC(SYSDATE) BETWEEN el.effective_start_date AND el.effective_end_date;
  • Retrieve all active military-status rules for a profile: SELECT * FROM ben.ben_elig_mltry_stat_cvg_f WHERE dpnt_cvg_eligy_prfl_id = :prfl_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date AND business_group_id = :bg_id;
  • Report eligible profiles by military status code: SELECT dpnt_cvg_eligy_prfl_id, mltry_stat_cd, cvg_strt_cd, cvg_thru_cd FROM ben.ben_elig_mltry_stat_cvg_f WHERE mltry_stat_cd = :code;

Reporting usually joins this table back to the dependent coverage eligibility profile so that military-status rules can be presented alongside other eligibility criteria, and uses EFFECTIVE_START_DATE / EFFECTIVE_END_DATE comparisons to isolate the version in force at a point in time.

Related Objects

Given the heuristic "standalone" classification, this table has no inbound foreign keys documented in the metadata, but the following objects are the most significant related or dependent tables, views, and APIs:

  • BEN_ELIGY_PRFL — the parent eligibility profile definition; joined via ELIG_MLTRY_STAT_CVG_ID lineage / profile relationships.
  • BEN_DPNT_CVG_ELIGY_PRFL — the dependent coverage eligibility profile; joined using DPNT_CVG_ELIGY_PRFL_ID.
  • BEN_ELIG_MLTRY_STAT_CVG — the non-dated (table) counterpart or related coverage definition for military status.
  • BEN_ELIG_DPNT_CVG_ELIGY_PRFL and related coverage eligibility profile tables — extended criteria attached to the same profile.
  • BEN_ELIG_CVRD_DPNT_RL — covered dependent rules that consume military-status eligibility results.
  • BEN_ELIG_F / BEN_ELIG_*_F family of date-tracked eligibility tables — joined by eligibility profile and effective dates.
  • HR_LOOKUPS — supplies lookup meanings for MLTRY_STAT_CD, CVG_STRT_CD, and CVG_THRU_CD.
  • FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — validates the EMC_ATTRIBUTE flexfield values.
  • BEN_ELIG_MLTRY_STAT_CVG_F_PK — the unique index enforcing the versioned business key.
  • Public BEN APIs such as ben_eligy_prfl_api and eligibility profile validation APIs, which read this table when evaluating dependent military-status eligibility.