Results for “emc_attribute1”

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

Overview

The BEN_ELIG_MLTRY_STAT_CVG_X view is a public, date-effective read-only interface owned by the APPS schema within the Oracle Advanced Benefits (BEN) product module. It exposes eligibility rules that determine whether a participant's military status qualifies them for specific coverages within a dependent coverage eligibility profile. The "_X" suffix follows Oracle EBS naming conventions for translated or date-effective views intended for external consumption by reports, concurrent programs, and integrations, rather than for transactional use.

The view presents only rows whose effective date range currently includes the system date, applying the filter TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE. This makes it a point-in-time snapshot of active military-status coverage eligibility rules. Unlike the underlying _F (entity) table, which retains all historical and future-dated versions, the view returns only the currently valid record, shielding consumers from multi-version row management concerns. This behavior is central to how the object is referenced in Oracle Business Intelligence, custom reports, and inbound/outbound extracts.

Underlying Base Objects

The view is defined over a single base object: BEN_ELIG_MLTRY_STAT_CVG_F, referenced via a synonym. The _F suffix denotes a date-effective ("F" for "full" temporal) entity table that stores every version of an eligibility rule with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns. The view aliases this table as EMC and performs no joins, aggregations, or transformations other than the SYSDATE filter. Consequently, the view and its base table share an identical column list; the view simply restricts the row set to the effective version.

Because the SELECT does not join to BUSINESS_GROUP_ID dimension tables, coverage lookups, or military status code descriptions, any downstream query requiring decode values or profile names must join separately to BEN_DPNT_CVG_ELIGY_PRFL or the appropriate lookup views. The object is therefore best characterized as a convenience interface rather than a denormalized reporting structure.

Key Columns

• ELIG_MLTRY_STAT_CVG_ID — Primary key for the eligibility rule.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective boundaries governing rule validity.
• BUSINESS_GROUP_ID — Operating unit identifier scoping the rule.
• DPNT_CVG_ELIGY_PRFL_ID — Foreign key to the dependent coverage eligibility profile that owns the rule.
• MLTRY_STAT_CD — Military status code evaluated by the rule.
• CVG_STRT_CD / CVG_STRT_RL — Coverage start code and rule logic applied when the criterion matches.
• CVG_THRU_CD / CVG_THRU_RL — Coverage end code and rule logic.
• EMC_ATTRIBUTE_CATEGORY and EMC_ATTRIBUTE1 through EMC_ATTRIBUTE30 — A thirty-column descriptive flexfield segment set. The user's search term, emc_attribute1, refers directly to this column, which holds the first user-defined attribute associated with the eligibility rule for a given attribute category. These columns support customer-specific extensions without requiring schema changes.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

The view is typically queried to audit or report on active military-status coverage eligibility. A representative query filter on the DFF attribute searched by the user is:

SELECT emc.elig_mltry_stat_cvg_id,
       emc.dpnt_cvg_eligy_prfl_id,
       emc.mltry_stat_cd,
       emc.emc_attribute_category,
       emc.emc_attribute1
FROM   apps.ben_elig_mltry_stat_cvg_x emc
WHERE  emc.emc_attribute1 = :p_attribute1;

Because the view already filters on SYSDATE, additional DATE predicates are unnecessary unless a historical snapshot is required, in which case the base table BEN_ELIG_MLTRY_STAT_CVG_F should be queried directly with explicit date boundaries. Other common scenarios include generating extracts of coverage start/end rules per military status for benefits interfaces, validating that a given dependent coverage eligibility profile contains the expected combinations of MLTRY_STAT_CD, and joining the profile ID to BEN_DPNT_CVG_ELIGY_PRFL to resolve descriptive names. All access should be granted through the APPS schema synonyms; direct queries against _F tables bypass the effective-dating logic and should be reserved for administration.