Search Results ben_elig_stdnt_stat_cvg_d




Overview
Explore in AI Chat

Overview

The BEN_ELIG_STDNT_STAT_CVG_D view is a denormalized reporting and integration object owned by the APPS schema within the Oracle Advanced Benefits (BEN) module. It presents eligibility rules that determine dependent coverage based on a dependent's student status. In Oracle EBS 12.1.1 and 12.2.2, this view serves as a read-only representation of the underlying eligibility setup, exposing lookup meanings and formula names rather than raw codes and identifiers. This design allows reporting tools, concurrent programs, and external integrations to consume human-readable eligibility configuration without joining multiple lookup and formula tables. The view is marked with the "Retrofitted" status in the ETRM documentation, indicating it was altered to conform to current schema standards. The suffix _D distinguishes this descriptive view from the base transactional table BEN_ELIG_STDNT_STAT_CVG_F. Because it joins to effective-dated formula records on EFFECTIVE_START_DATE boundaries, the view returns the formula version active at the time of the eligibility rule, preserving historical accuracy when the row's effective dates fall within a formula's validity range. This behavior is important for auditing and for reproducing prior eligibility determinations.

Underlying Base Objects

The view is defined over several documented base objects. The primary driver is BEN_ELIG_STDNT_STAT_CVG_F, the table storing student-status coverage eligibility records. It is outer-joined to BEN_DPNT_CVG_ELIGY_PRFL_F, which supplies the dependent coverage eligibility profile name. Lookup meanings are resolved through the HR_LOOKUPS view for three lookup types: BEN_DPNT_CVG_STRT (coverage start), BEN_DPNT_CVG_END (coverage through/end), and STUDENT_STATUS. Formula names are retrieved from FF_FORMULAS_F via the coverage-start rule (CVG_STRT_RL) and coverage-through rule (CVG_THRU_RL). The user who last updated each row is resolved from FND_USER. ETRM also lists the HR_API package as a referenced object, reflecting the API-driven maintenance path for the base records. Because every join to the descriptive tables uses the Oracle outer-join operator (+), the view never drops eligibility rows when a lookup meaning, formula, or user record is missing or unapproved.

Key Columns

Common Use Cases and Queries

The view is typically used to report or validate student-status-dependent coverage eligibility, to reconcile eligibility profiles against lookup configuration, and to troubleshoot why a dependent was accepted or rejected. A representative query filters by the decoded student status:

SELECT elig_stdnt_stat_cvg_id,
       dpnt_cvg_eligy_prfl_name,
       stdnt_stat_meaning,
       cvg_strt_meaning,
       cvg_thru_meaning
FROM   apps.ben_elig_stdnt_stat_cvg_d
WHERE  stdnt_stat_meaning = :p_student_status
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because the view exposes only the descriptive layer, it should not be used for transactional writes; modifications must be performed through the HR_API maintenance path against the base table.