Results for “dpnt_cvg_eligy_prfl_name”

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

Overview

The BEN_ELIG_DSBLD_STAT_CVG_D view is a denormalized, reporting-oriented database object in the APPS schema within Oracle E-Business Suite, belonging to the BEN (Advanced Benefits) product family. It is delivered as a VALID view and is documented as a "Retrofitted" object, indicating that its definition was carried forward from an earlier release into the current 12.1.1 / 12.2.2 code line. Its principal purpose is to present eligibility rules that govern coverage for dependents with a registered disabled status. Rather than requiring report authors or integrators to join the underlying rule tables to multiple lookups and formula tables, the view performs that work in a single SELECT, exposing human-readable meanings and formula names alongside the raw identifiers.

The view is especially relevant to users searching on dpnt_cvg_eligy_prfl_name, since that attribute is surfaced directly as the DPNT_CVG_ELIGY_PRFL_NAME column. Consumers of this view obtain the dependent coverage eligibility profile name, the start and end coverage rule meanings, the disabled status meaning, and audit information without reconstructing the joins themselves.

Underlying Base Objects

The documented view text is defined over the following base objects:

• BEN_ELIG_DSBLD_STAT_CVG_F (synonym) — the primary driving table, aliased EDC, holding the eligibility disabled status coverage rule records keyed by ELIG_DSBLD_STAT_CVG_ID and effective-dated by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
• BEN_DPNT_CVG_ELIGY_PRFL_F (synonym) — the dependent coverage eligibility profile, aliased DPNT_CVG_ELIGY_PRFL, joined on DPNT_CVG_ELIGY_PRFL_ID to supply DPNT_CVG_ELIGY_PRFL.NAME.
• HR_LOOKUPS (view) — referenced three times to decode the coverage start code (lookup type BEN_DPNT_CVG_STRT), the coverage end code (BEN_DPNT_CVG_END), and the disabled code (REGISTERED_DISABLED).
• FF_FORMULAS_F (synonym) — joined twice to resolve the formulas behind the coverage start and coverage end rules via CVG_STRT_RL and CVG_THRU_RL.
• FND_USER (synonym) — provides the USER_NAME of the last updater through an outer join on LAST_UPDATED_BY.

All joins to the profile, lookup, formula, and user objects are outer joins ((+)), so the driving rule row is never eliminated when a lookup or formula is missing. The formula joins are additionally constrained by effective date range. The ETRM metadata also lists HR_API (package) among referenced base objects, though it does not appear in the documented SELECT text.

Key Columns

• ROW_ID — the rowid of the base rule row, used for identity and update operations.
• ELIG_DSBLD_STAT_CVG_ID — the unique identifier of the disabled status coverage eligibility rule.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective dating window of the rule.
• DPNT_CVG_ELIGY_PRFL_NAME — the dependent coverage eligibility profile name (sourced from DPNT_CVG_ELIGY_PRFL.NAME).
• CVG_STRT_MEANING / CVG_STRT_NAME — the decoded meaning and the formula name for the coverage start rule.
• CVG_THRU_MEANING / CVG_THRU_NAME — the decoded meaning and formula name for the coverage end rule.
• DSBLD_MEANING — the decoded registered-disabled value.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns, with USER_NAME resolved from FND_USER.

Common Use Cases and Queries

This view is typically used in benefit eligibility reports, data extracts, and integration feeds that need to confirm how a dependent's disabled status influences coverage start and end rules. A representative query filtering by profile name is:

• SELECT ELIG_DSBLD_STAT_CVG_ID, DPNT_CVG_ELIGY_PRFL_NAME, CVG_STRT_MEANING, CVG_THRU_MEANING, DSBLD_MEANING, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE FROM BEN_ELIG_DSBLD_STAT_CVG_D WHERE DPNT_CVG_ELIGY_PRFL_NAME = :p_profile_name;
• Audit-oriented extract joining back to the driving table by ELIG_DSBLD_STAT_CVG_ID to compare current rule formulas against stored values.
• Effective-date-bounded reporting using SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE to list only currently active rules.

Because the view performs the lookup and formula resolution, queries against it remain simple while preserving the underlying rule identifiers for drill-down.