Search Results ben_cvrd_dpnt_ctfn_prvdd_f




Overview
Explore in AI Chat

Overview

The BEN.BEN_CVRD_DPNT_CTFN_PRVDD_F table is a core datastore within the Oracle E-Business Suite Advanced Benefits (BEN) module. It stores certification records provided for covered dependents, capturing the evidence an employee submits to prove that a dependent (or the dependent designation itself) satisfies a plan's eligibility certification requirements. In EBS 12.1.1 and 12.2.2 this table is dated (the _F suffix and the presence of EFFECTIVE_START_DATE/EFFECTIVE_END_DATE indicate a date-tracked, non-translated table), so each certification can exist as multiple effective-dated row versions rather than a single mutable row.

From a data-modeling perspective, the metadata's heuristic Data Vault classification lists this object as standalone. In practice, however, the combination of a surrogate key, effective dates, descriptive certification attributes, and a foreign-key-style reference to the covered dependent (ELIG_CVRD_DPNT_ID) and to the enrollment action (PRTT_ENRT_ACTN_ID) suggests a satellite pattern attached to the covered-dependent hub, with the dated rows acting as the change history. This classification is offered as a modeling suggestion only; the physical object is a standard EBS date-tracked table.

Key Information Stored

The table is defined in the BEN schema with 50 documented columns. The most significant are:

The unique index BEN_CVRD_DPNT_CTFN_PRVDD_F_PK on (CVRD_DPNT_CTFN_PRVDD_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the documented business-key candidate and should be treated as the row-identity key for dated lookups.

Common Use Cases and Queries

Typical scenarios include reporting on certification compliance, auditing which dependents have provided required documentation, and troubleshooting enrollment or eligibility failures. A common query retrieves the currently effective certification for a dependent:

  • SELECT * FROM ben_cvrd_dpnt_ctfn_prvdd_f WHERE elig_cvrd_dpnt_id = :p_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Reporting certifications by type and receipt date: SELECT dpnt_dsgn_ctfn_typ_cd, COUNT(*) FROM ben_cvrd_dpnt_ctfn_prvdd_f WHERE dpnt_dsgn_ctfn_recd_dt >= :from_date GROUP BY dpnt_dsgn_ctfn_typ_cd;
  • Identifying dependents still owing a required certification: filter on dpnt_dsgn_ctfn_rqd_flag = 'Y' with no received date.

Because the table is date-tracked, all queries must apply an effective-date predicate or they will return historic versions, inflating counts and producing duplicate rows in reports.

Related Objects

The metadata classifies this object as standalone with no mined foreign keys, but the following objects are the most significant for joins and dependencies based on the documented columns: