Results for “ben_covered_dependents”

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

Overview

The BEN_COVERED_DEPENDENTS view is a date-effective (daterange) reporting object owned by the APPS schema and associated with the PER — Human Resources product family within Oracle E-Business Suite. It is classified as a VIEW with VALID status and is documented for both 12.1.1 and 12.2.2 releases. Its principal role is to expose covered dependent records — the dependents and beneficiaries associated with a participant's benefit enrollment — in a form that is automatically filtered to the effective date of the current user session. This makes it the canonical read-only source for reporting, integration, and inquiry screens that must respect effective-dating semantics without requiring the caller to manually construct date-range predicates.

The view surfaced in search results for the keyword covered_dependent_id, which is the primary identifier column of the object. Because the view resolves the effective date from the FND_SESSIONS record bound to the runtime session, queries executed through concurrent programs, OAF pages, or Forms inherit the same date-effective behavior as the underlying application logic.

Underlying Base Objects

The view is defined entirely over the date-effective table BEN_COVERED_DEPENDENTS_F (referenced in the metadata as a SYNONYM) and correlates against FND_SESSIONS (also a SYNONYM). The definition selects all ten exposed columns from BEN_COVERED_DEPENDENTS_F and constrains the result set so that:

• EFFECTIVE_START_DATE <= the session effective date retrieved from FND_SESSIONS for the current USERENV('SESSIONID'), and
• EFFECTIVE_END_DATE >= the same session effective date.

This is the standard Oracle HRMS date-effective view pattern: the _F (fact) table stores all dated versions of a row, while the non-suffixed view presents only the version valid at the session date. Note that BEN_COVERED_DEPENDENTS does not expose a BUSINESS_GROUP_ID or a business-group key column in the documented column list, and no _F-level foreign key to a person or assignment is included; the association to the participant flows through the contact relationship and element entry references.

Key Columns

• COVERED_DEPENDENT_ID — Primary identifier for the covered dependent record; the column most frequently referenced in joins and searches.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date-effective window within which the row version is valid; used by the view's own predicate against FND_SESSIONS.
• CONTACT_RELATIONSHIP_ID — Links the covered dependent to the contact relationship record that describes the relationship between the participant and the dependent (for example, spouse or child).
• ELEMENT_ENTRY_ID — References the element entry created for the dependent during benefit processing, tying the record to payroll/benefits costing.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who columns supporting audit and concurrency tracking.

Common Use Cases and Queries

Typical scenarios include benefit enrollment reconciliation, dependent coverage audits, third-party carrier extracts, and ad-hoc reporting by benefits administrators. A standard query returns all dependents effective as of the session date for a given contact relationship:

• SELECT covered_dependent_id, contact_relationship_id, element_entry_id, effective_start_date, effective_end_date FROM apps.ben_covered_dependents WHERE contact_relationship_id = :p_relationship_id;
• SELECT covered_dependent_id, effective_start_date, effective_end_date FROM apps.ben_covered_dependents ORDER BY covered_dependent_id, effective_start_date;

Because the view derives its effective date from FND_SESSIONS, integrations running outside a Forms/OAF session (for example, from SQL*Plus or a standalone JDBC client) may not have a populated session row, causing the subquery to return no value and the view to return no rows. In such cases, query BEN_COVERED_DEPENDENTS_F directly with an explicit date predicate, or initialize the session context before querying the view.