Results for “ben_cvrd_dpnt_ctfn_prvdd_f_pk”

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

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:

• CVRD_DPNT_CTFN_PRVDD_ID — the surrogate primary key component that uniquely identifies the certification row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity window; together with the surrogate ID these form the primary key BEN_CVRD_DPNT_CTFN_PRVDD_F_PK.
• ELIG_CVRD_DPNT_ID — the covered dependent (eligibility covered dependent) to which the certification applies; this is the principal business linkage.
• PRTT_ENRT_ACTN_ID — the participant enrollment action that generated or is associated with the certification.
• DPNT_DSGN_CTFN_TYP_CD — the code identifying the type of dependent-designation certification.
• DPNT_DSGN_CTFN_RQD_FLAG — indicates whether the certification is required for the dependent designation.
• DPNT_DSGN_CTFN_RECD_DT — the date the certification was received.
• BUSINESS_GROUP_ID — the business group (enterprise) that owns the record.
• CCP_ATTRIBUTE_CATEGORY and CCP_ATTRIBUTE1–CCP_ATTRIBUTE30 — a descriptive flexfield (DFF) block enabling client-specific certification attributes.
• OBJECT_VERSION_NUMBER — optimistic-locking version column.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard EBS WHO audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent-program traceability columns.

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:

• BEN_ELIG_CVRD_DPNT (or the covered-dependent eligibility entity) — joined via ELIG_CVRD_DPNT_ID.
• BEN_PRTT_ENRT_ACTN — joined via PRTT_ENRT_ACTN_ID for enrollment-action context.
• BEN_PRTT_ENRT_RSLT_F — participant enrollment results, linked through the enrollment action.
• BEN_PER_ELIG_CVRD_DPNT_F — person/eligibility covered-dependent rows.
• BEN_DPNT_DSGN_CTFN_TYP — lookup of dependent design certification types referenced by DPNT_DSGN_CTFN_TYP_CD.
• FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — used to decode any flexfield or lookup values exposed in the CCP attribute columns and certification type.