Results for “dpnt_cvg_elig_det_rl”

48 results




Overview
Explore in AI Chat

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

Overview

The BEN_DPNT_CVG_ELIGY_PRFL view is a public, date-effective (Effective Date Tracking / "ETRM") view owned by the APPS schema within the Oracle E-Business Suite Advanced Benefits (BEN) module. It presents the master definition of a Dependent Coverage Eligibility Profile — the reusable rule set that determines whether a dependent is eligible for a particular benefit program, plan type, or plan. In the Benefits data model, an eligibility profile acts as the gatekeeper for enrollment; the dependent coverage eligibility profile applies specifically to dependent persons and controls the enrollment of dependents (not the employee) into eligible coverages.

The view is a "current effective" view. Its underlying WHERE clause restricts rows to the single version whose effective date range encloses the system date held in the current session record (FND_SESSIONS.EFFECTIVE_DATE for the caller's SESSIONID). This means the view always returns exactly one active version per profile, which is the form most consumers need for reporting and integration.

Underlying Base Objects

The view is defined over a single base table, exposed to the APPS schema through a synonym:

  • BEN_DPNT_CVG_ELIGY_PRFL_F — the date-effective ("_F" indicates the full / effective-dated entity) table that stores every version of every dependent coverage eligibility profile, including its start and end date columns.
  • FND_SESSIONS — the session table read in the subquery to obtain the effective date used to select the current version. In ETRM objects this is the standard mechanism for resolving the as-of-date, allowing the same object to return different versions depending on the application's effective date context.

Because the view merely projects columns from BEN_DPNT_CVG_ELIGY_PRFL_F with the effective-date predicate applied, it does not join to dependent tables or to the actual eligibility determination engine tables. It is a convenient read layer over the profile definition itself, not over the evaluated eligibility results.

Key Columns

Common Use Cases and Queries

Typical uses include auditing which dependent eligibility profiles are active, reporting the profile attributes that drive dependent enrollment, and joining the profile to plan or program configurations that reference it.

List all currently effective dependent coverage eligibility profiles for a business group:

SELECT dce.dpnt_cvg_eligy_prfl_id,
       dce.name,
       dce.dpnt_cvg_eligy_prfl_stat_cd,
       dce.effective_start_date,
       dce.effective_end_date
  FROM apps.ben_dpnt_cvg_eligy_prfl dce
 WHERE dce.business_group_id = :p_business_group_id
 ORDER BY dce.name;

Retrieve profile attributes used for dependent age and student tests:

SELECT name,
       dpnt_age_flag,
       dpnt_stud_flag,
       dpnt_ds bld_flag,
       dpnt_rlshp_flag
  FROM apps.ben_dpnt_cvg_eligy_prfl
 WHERE dpnt_cvg_eligy_prfl_stat_cd = 'A';

Because the view is session-sensitive, report queries should be run from a context where the effective date is meaningful; the predicate is satisfied automatically for the EBS session in which the query executes.