Results for “ben_covered_dependents_v”

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

Overview

BEN_COVERED_DEPENDENTS_V is a reporting and user-interface support view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and is documented in ETRM with the description "Used to support user interface." The view consolidates covered dependent records from the Benefits foundation table BEN_COVERED_DEPENDENTS_F with the person identity, contact relationship, and lookup data required to render meaningful dependent information on Benefits and Human Resources forms.

The view joins Benefits data (BEN_COVERED_DEPENDENTS_F) to contact relationships (PER_CONTACT_RELATIONSHIPS), person records (PER_PEOPLE_F), and the CONTACT lookup type in HR_LOOKUPS. This means that when a user searches on the term "contact_type," BEN_COVERED_DEPENDENTS_V is a natural destination: the view exposes a CONTACT_TYPE column populated from HR_LOOKUPS.MEANING, rather than the raw lookup code. Effective-dating logic is applied through FND_SESSIONS, filtering the results to the session's effective date so that the user interface presents only the currently valid row for each dependent and person record.

Underlying Base Objects

ETRM records the following referenced objects for BEN_COVERED_DEPENDENTS_V:

• BEN_COVERED_DEPENDENTS_F (SYNONYM) — the Benefits foundation table holding covered dependent assignments, including effective dates and the element entry linkage.
• FND_SESSIONS (SYNONYM) — supplies the session effective date used to restrict the query to current rows.
• PER_CONTACT_RELATIONSHIPS (SYNONYM) — links a covered dependent to a contact person and defines the contact type code.
• PER_PEOPLE_F (VIEW) — the effective-dated person view providing FULL_NAME and ORDER_NAME.
• HR_LOOKUPS (VIEW) — resolves the contact type lookup code to its displayed meaning.
• HR_API, HR_GENERAL, HR_PERSON_NAME, HR_SECURITY (PACKAGES) — supporting HR security, naming, and general utility logic that govern access and formatting.

The view is defined with a join path of BEN_COVERED_DEPENDENTS_F to PER_CONTACT_RELATIONSHIPS on CONTACT_RELATIONSHIP_ID, then to PER_PEOPLE_F on CONTACT_PERSON_ID = PERSON_ID, and finally to HR_LOOKUPS where LOOKUP_TYPE = 'CONTACT'. Effective-date predicates are applied to both BEN_COVERED_DEPENDENTS_F and PER_PEOPLE_F using the FND_SESSIONS effective date, ensuring the result set is point-in-time correct for the active session.

Key Columns

• ROW_ID — ROWID of the underlying dependent row, useful for direct-row identification.
• COVERED_DEPENDENT_ID — primary key of the covered dependent record.
• PERSON_ID — the contact person identifier from the relationship record.
• FULL_NAME / ORDER_NAME — formatted and sortable names of the dependent.
• CONTACT_TYPE — the lookup meaning (for example, Spouse, Child, or Other) derived from HR_LOOKUPS.MEANING. This is the column most relevant to searches on "contact_type."
• CONTACT_RELATIONSHIP_ID — foreign key to PER_CONTACT_RELATIONSHIPS.
• NATIONAL_IDENTIFIER — government or national identification number of the contact.
• ELEMENT_ENTRY_ID — links the dependent to the payroll or benefits element entry.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date range for which the covered dependent record is valid.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE for change tracking.

Common Use Cases and Queries

This view is typically queried to produce dependent listings for Benefits enrollment review, contact type analysis, or integration extracts. A representative query filtering on contact type is:

• SELECT covered_dependent_id, full_name, contact_type, national_identifier, effective_start_date, effective_end_date FROM ben_covered_dependents_v WHERE contact_type = 'Spouse' ORDER BY order_name;
• SELECT covered_dependent_id, full_name, contact_type FROM ben_covered_dependents_v WHERE element_entry_id = :p_element_entry_id;
• SELECT contact_type, COUNT(*) FROM ben_covered_dependents_v GROUP BY contact_type ORDER BY contact_type;

Because results are filtered by the FND_SESSIONS effective date, queries run through this view return only the currently effective dependent and person rows. For historical reporting, developers must instead query the underlying BEN_COVERED_DEPENDENTS_F and PER_PEOPLE_F tables directly with explicit date ranges, since the view cannot be parameterized for arbitrary effective dates. Access is also subject to HR_SECURITY, so visible rows depend on the securing user's security profile.