Search Results ben_dpnt_cvg_eligy_prfl_f




Overview
Explore in AI Chat

Overview

BEN_DPNT_CVG_ELIGY_PRFL_F is the foundational, date-tracked entity in the Oracle Advanced Benefits (BEN) module that stores dependent coverage eligibility profiles. In Oracle EBS Release 12.1.1 and 12.2.2, this table defines the rules and criteria that determine whether a dependent (spouse, child, or other qualified individual) is eligible to be enrolled under a specific benefit program or plan. Each row represents a version of an eligibility profile, archived across effective date ranges to preserve historical eligibility logic as plan configurations evolve.

The table is owned by the BEN schema and is classified as a standalone entity under the heuristic Data Vault modeling scheme derived from its foreign-key structure. As a suggestion for data-warehouse ingestion, this object is best modeled as a satellite keyed by DPNT_CVG_ELIGY_PRFL_ID, since most columns describe descriptive attributes of a single eligibility profile rather than foreign-key relationships to other business entities. The absence of strong incoming FK linkages supports treating it as a self-contained dimension of eligibility definition data.

Key Information Stored

The table comprises 56 columns. The primary key, enforced by BEN_DPNT_CVG_ELIGY_PRFL_F_PK, is a composite of DPNT_CVG_ELIGY_PRFL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The DPNT_CVG_ELIGY_PRFL_ID is the surrogate identifier assigned to each eligibility profile; the two date columns provide the DateTrack versioning boundary that allows multiple rows per profile across time.

Common Use Cases and Queries

Functional consultants and developers query this table to inspect why a dependent was accepted or rejected during benefits enrollment, to migrate eligibility profiles between environments, or to report on plan eligibility configuration. A typical query filters for the current active version of a profile using SYSDATE between the effective dates:

  • SELECT * FROM ben.ben_dpnt_cvg_eligy_prfl_f WHERE dpnt_cvg_eligy_prfl_id = :id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Reporting on profiles enabled for a specific criterion: SELECT name, dce_desc FROM ben.ben_dpnt_cvg_eligy_prfl_f WHERE dpnt_age_flag = 'Y' AND dpnt_cvg_eligy_prfl_stat_cd = 'A';
  • Extracting DFF context for integration: select DCE_ATTRIBUTE_CATEGORY alongside one or more DCE_ATTRIBUTE columns.

Because BEN is DateTrack-enabled, any query that omits the effective date predicate will return multiple historical versions and must be handled carefully in reports and extracts.

Related Objects

The metadata identifies this table as standalone from an FK perspective. In practice, the following objects participate in the dependent coverage eligibility flow: