Results for “hr_adp_spouse_v”

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

Overview

The HR_ADP_SPOUSE_V view is a PL/SQL-based reporting object owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. As its name and product assignment (PER – Human Resources) indicate, it is a purpose-built interface view that exposes spouse (dependent contact) information for consumption by ADP payroll extract processes. The view is registered in the ETRM repository with a status of VALID, confirming that it is a supported and deployable object within the standard Oracle HRMS data model. Because it is a VIEW and not a table, HR_ADP_SPOUSE_V stores no data of its own; instead it is a stored SQL statement that joins contact relationship records to person records and returns a flattened result set at run time.

The view plays a specific and narrow role within the ADP interface layer of Oracle HRMS. ADP payroll extracts require dependent and spouse demographic data — names, employee numbers, and dates of birth — so that payroll and benefits processing downstream can correctly identify covered dependents. HR_ADP_SPOUSE_V provides that slice of data in a format aligned to the ADP extract specification, rather than requiring the ADP integration to reconstruct contact relationships directly from the HRMS base tables.

Underlying Base Objects

The view is defined over the following documented base objects:

• PER_ALL_PEOPLE_F (referenced through a synonym) — the dated, effective-dated people table holding person identity and demographic attributes, including FULL_NAME, EMPLOYEE_NUMBER, and DATE_OF_BIRTH.
• PER_CONTACT_RELATIONSHIPS (referenced through a synonym) — the contact relationship table that stores relationships between a person and their contacts, including the contact type that identifies a spouse.
• HR_ADP (package, referenced by function) — the ADP integration package, specifically the function HR_ADP.GET_ADP_EXTRACT_DATE, which supplies the effective date used to filter the view's results.

The join logic links SP.PERSON_ID to C.CONTACT_PERSON_ID and restricts rows to C.CONTACT_TYPE = 'S', isolating spouse contacts from other contact relationship types. Effective-dating is applied on both sides of the join: the person record must be current as of the extract date (between SP.EFFECTIVE_START_DATE and SP.EFFECTIVE_END_DATE), and the contact relationship must also be current as of the extract date (between C.DATE_START and NVL(C.DATE_END, 31-DEC-4712)).

Key Columns

• BUSINESS_GROUP_ID — the business group (legislative/HR operating unit context) that owns the person and relationship records.
• PERSON_ID — the identifier of the employee or primary person in the relationship (from the PER_CONTACT_RELATIONSHIPS side).
• SPOUSE_PERSON_ID — in the documented metadata this column invokes the CONTACT_PERSON_ID value, i.e., the person identifier of the spouse/contact.
• SPOUSE_FULL_NAME — the spouse's full name taken from PER_ALL_PEOPLE_F.FULL_NAME.
• SPOUSE_EMPLOYEE_NUMBER — the spouse's employee number, where the contact is also an employee or has a system-generated person number.
• SPOUSE_DATE_OF_BIRTH — the spouse's date of birth, used for dependent eligibility and benefit determinations.

Note that the underlying view text aliases these columns as CONTACT_PERSON_ID, FULL_NAME, EMPLOYEE_NUMBER, and DATE_OF_BIRTH; the documented display names above are the logical column names exposed for reporting consumers.

Common Use Cases and Queries

The most common use of HR_ADP_SPOUSE_V is as the source for ADP spouse/dependent extracts and for audit reports validating that spouse records on file are complete (name, employee number, and date of birth populated). Because the view is filtered by HR_ADP.GET_ADP_EXTRACT_DATE, its output reflects the HRMS state as of the currently configured extract date rather than the system date.

A representative query that returns the spouse information for a specific person is:

• SELECT person_id, spouse_person_id, spouse_full_name, spouse_employee_number, spouse_date_of_birth FROM apps.hr_adp_spouse_v WHERE person_id = :p_person_id;

To validate completeness of spouse data for the current ADP extract window:

• SELECT business_group_id, person_id, spouse_full_name, spouse_date_of_birth FROM apps.hr_adp_spouse_v WHERE spouse_date_of_birth IS NULL OR spouse_full_name IS NULL;

Because the view depends on the HR_ADP package function for its date filter, any tuning or troubleshooting should verify that HR_ADP.GET_ADP_EXTRACT_DATE returns the intended date and that both the person and contact relationship records are effective on that date; otherwise rows will be silently excluded from the result set.