Search Results ben_covered_dependents_f




Overview
Explore in AI Chat

Overview

The BEN_COVERED_DEPENDENTS_F table is a core data object within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically for the Benefits Management functionality. It serves as a historical repository that tracks which employee dependents are enrolled or covered under specific benefit plans over time. The table's role is to maintain a precise, date-effective record of the relationship between a benefit enrollment (via an element entry), a dependent, and the specific plan coverage. This temporal integrity is critical for accurate benefits administration, eligibility determination, and audit reporting across versions 12.1.1 and 12.2.2.

Key Information Stored

The table is structured to enforce both uniqueness and historical tracking. Its primary identifier is the system-generated COVERED_DEPENDENT_ID. The temporal dimension is managed by the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns, which define the period for which a specific dependent coverage record is active. Two key foreign key columns establish the critical relationships: CONTACT_RELATIONSHIP_ID links to the PER_CONTACT_RELATIONSHIPS table to identify the specific dependent person, and ELEMENT_ENTRY_ID links to the payroll element entry that represents the employee's enrollment in a particular benefit plan. The table's primary and unique keys ensure that a dependent cannot be linked to the same benefit enrollment more than once within any given effective date range.

Common Use Cases and Queries

This table is central to queries that report on benefit plan participation. Common use cases include generating dependent coverage lists for a specific plan, verifying an individual dependent's eligibility across multiple enrollments, and auditing historical coverage for compliance. A typical reporting query would join this table to PER_CONTACT_RELATIONSHIPS and PAY_ELEMENT_ENTRIES_F, filtered by a specific date to get a snapshot of active covered dependents. For example, to find all dependents currently covered under a specific element entry, one might use:

  • SELECT pcr.* FROM ben_covered_dependents_f bcd
  • JOIN per_contact_relationships_f pcr ON bcd.contact_relationship_id = pcr.contact_relationship_id
  • WHERE bcd.element_entry_id = :p_element_entry_id
  • AND SYSDATE BETWEEN bcd.effective_start_date AND bcd.effective_end_date
  • AND SYSDATE BETWEEN pcr.effective_start_date AND pcr.effective_end_date;

Related Objects

The BEN_COVERED_DEPENDENTS_F table has direct and essential dependencies within the HR schema. Its primary foreign key relationship is with the PER_CONTACT_RELATIONSHIPS table, which stores the master definition of employee-dependent relationships. The link to ELEMENT_ENTRY_ID ties it to the payroll and compensation structures, specifically the PAY_ELEMENT_ENTRIES_F table. This table is also a likely source for various benefits-related views and is integral to the data model supporting the Oracle Benefits core APIs, which manage enrollment and life event processing. Queries against this table must always consider the effective date ranges in both this table and its related foreign key tables to return accurate results.