Results for “benbv_elig_dsbld_stat_cvg_v”

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

Overview

The view BENBV_ELIG_DSBLD_STAT_CVG_V is an Oracle E-Business Suite (EBS) Advanced Benefits (BEN) database object owned by the APPS schema. Its status is VALID as documented in ETRM for Oracle EBS 12.2.2 (and supported on 12.1.1). The view's stated purpose is to identify the applicable disabled statuses that a dependent contact must have in order for a participant to be permitted to designate that contact for coverage. In practice, it exposes the rows of the disabled-status eligibility rules that are attached to a dependent coverage eligibility profile.

This view is one of the "BENBV_" family of business views (the "BV" suffix) generated by the Advanced Benefits rules engine. These views provide a secured, read-only, descriptive decode layer over the underlying flexfield (_F) tables, so that reports, Fast Formulas, and integration extracts do not have to perform their own lookup-code translation. The view is defined WITH READ ONLY, confirming it is intended for query and reporting use only, not for direct DML.

Underlying Base Objects

According to the ETRM metadata, BENBV_ELIG_DSBLD_STAT_CVG_V is defined over two referenced base objects:

• BEN_ELIG_DSBLD_STAT_CVG_F (referenced as a SYNONYM) — the descriptive flexfield-enabled table that stores the disabled-status coverage eligibility rules. The view reads this table with the alias EDC.
• HR_BIS (a PACKAGE) — the EBS "Business Intelligence System" utility package, invoked in the view text to resolve security and decode lookups.

The relationship is a direct one-to-many projection: every row of BEN_ELIG_DSBLD_STAT_CVG_F whose BUSINESS_GROUP_ID matches the user's security profile is surfaced. The security predicate EDC.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, EDC.BUSINESS_GROUP_ID) restricts the result set to the operating business group of the querying responsibility, falling back to all business groups when no security profile ID is returned. The row identifier for the base record is derived from ELIG_DSBLD_STAT_CVG_ID, and each rule row is linked to a dependent coverage eligibility profile through DPNT_CVG_ELIGY_PRFL_ID.

Key Columns

The view exposes the following important columns:

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the dated validity range of the eligibility rule.
• CVG_STRT_RL / CVG_THRU_RL — the coverage start and coverage through rules associated with the disabled-status requirement.
• DSBLD_CD — the disabled status code (a lookup of type REGISTERED_DISABLED); the derived DSBLD_CD_M column holds the decoded, user-facing meaning produced by HR_BIS.BIS_DECODE_LOOKUP('REGISTERED_DISABLED', ...).
• CVG_STRT_CD and its decode CVG_STRT_CD_M — the coverage start code, decoded through the BEN_DPNT_CVG_START lookup.
• CVG_THRU_CD and its decode CVG_THRU_CD_M — the coverage through code, decoded through the BEN_DPNT_CVG_END lookup.
• ELIG_DSBLD_STAT_CVG_ID — primary identifier of the eligibility rule row.
• BUSINESS_GROUP_ID — the owning business group, used for security filtering.
• DPNT_CVG_ELIGY_PRFL_ID — foreign key to the dependent coverage eligibility profile this rule participates in.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — the standard "WHO" audit columns.
• **"_DF:EDC"** — a special descriptive flexfield context column pointing to the flexfield BEN:BEN_ELIG_DSBLD_STAT_CVG_F:EDC, allowing reports to expand descriptive flexfield segments.

Common Use Cases and Queries

Typical uses include auditing which disabled-status rules gate dependent coverage, diagnosing why a participant cannot enroll a disabled dependent, and extracting eligibility configuration for data warehouse or benefits-conversion loads. Decoded columns (the "_M" columns) let report writers display friendly descriptions without local lookup joins.

A simple query listing all applicable disabled-status rules for a given eligibility profile:

SELECT elig_dsbld_stat_cvg_id,
       dpnt_cvg_eligy_prfl_id,
       dsbld_cd,
       dsbld_cd_m,
       cvg_strt_cd_m,
       cvg_thru_cd_m,
       effective_start_date,
       effective_end_date
FROM   apps.benbv_elig_dsbld_stat_cvg_v
WHERE  dpnt_cvg_eligy_prfl_id = :p_profile_id
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because the view is read-only and applies business-group security through HR_BIS.GET_SEC_PROFILE_BG_ID, queries automatically honor the responsibility's business-group access. Developers should not attempt inserts or updates against it; DML must target the base table BEN_ELIG_DSBLD_STAT_CVG_F through the supported Advanced Benefits APIs.