Search Results spouse_middle_names

Overview

The view APPS.PAY_HK_IR56_SPOUSE_DETAILS_V is a Hong Kong localisation object supplied within the Oracle E-Business Suite Payroll (PAY) product family. It exists to expose spouse-level personal data required for Hong Kong Inland Revenue Department (IRD) statutory reporting, most notably Form IR56B, which captures employer's return information for employees and their dependents. The view is documented as VALID in the APPS schema in both EBS 12.1.1 and 12.2.2.

Functionally, the view flattens the relationship between an employee (the "primary" person) and the person recorded as their spouse in Oracle HRMS contact relationships, joined to that spouse's person record. It presents a single denormalised row per qualifying employee-spouse pairing, allowing concurrent programs, reports, and interfaces to retrieve spouse name, national identifier, and passport data without re-implementing the contact-relationship logic. The DATE_END column present in the view text and column list reflects the effective-dated nature of the underlying contact relationship, allowing callers to determine when a spouse relationship ceased to be active.

Underlying Base Objects

The view is defined over two direct data sources joined in each branch of a UNION ALL:

• PER_CONTACT_RELATIONSHIPS (referenced as a synonym) — supplies the relationship rows, including PRIMARY_CONTACT_FLAG, PERSON_ID, CONTACT_PERSON_ID, DATE_START, DATE_END, and CONTACT_TYPE.
• PER_PEOPLE_F (a view) — supplies the spouse's person attributes, including NATIONAL_IDENTIFIER, name components, effective dates, and the descriptive flexfield segments PER_INFORMATION1 and PER_INFORMATION2 used here for passport number and country of issue.

The ETRM metadata additionally lists HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY as referenced base objects. These are HRMS packages and security constructs typically inherited through PER_PEOPLE_F and via row-level security applied by HRMS reporting. The two branches of the UNION ALL handle the case where a spouse is flagged as the primary contact (by PRIMARY_CONTACT_FLAG = 'Y'), and the fallback case where no primary spouse contact exists for the employee.

Key Columns

• PERSON_ID — the employee (primary person) identifier.
• CONTACT_PERSON_ID — the spouse's person identifier.
• DATE_START / DATE_END — the effective start and end dates of the spouse contact relationship, used to determine whether the relationship is currently active.
• CONTACT_TYPE — hard-coded to 'S' (spouse) in the view definition.
• PRIMARY_CONTACT_FLAG — indicates the primary spouse contact.
• SPOUSE_NATIONAL_IDENTIFIER, SPOUSE_LAST_NAME, SPOUSE_MIDDLE_NAMES, SPOUSE_FIRST_NAME — spouse identity attributes for IR56B reporting.
• SPOUSE_PASSPORT_NUMBER, SPOUSE_COUNTRY_OF_ISSUE — derived from PER_INFORMATION1 and PER_INFORMATION2 flexfield segments.
• SPOUSE_EFFECTIVE_START_DATE / SPOUSE_EFFECTIVE_END_DATE — effective dating of the spouse person record itself.

Common Use Cases and Queries

Typical usage is within Hong Kong payroll reporting and IR56 extract programs. A simple query to list current spouses might filter on the absence of a DATE_END:

• Identifying employees with a currently active spouse relationship: SELECT PERSON_ID, CONTACT_PERSON_ID, SPOUSE_LAST_NAME, DATE_END FROM APPS.PAY_HK_IR56_SPOUSE_DETAILS_V WHERE DATE_END IS NULL OR DATE_END > SYSDATE;
• Retrieving passport information for IR56B: SELECT PERSON_ID, SPOUSE_PASSPORT_NUMBER, SPOUSE_COUNTRY_OF_ISSUE FROM APPS.PAY_HK_IR56_SPOUSE_DETAILS_V;
• Determining historical spouse relationships by constraining DATE_START and DATE_END to a reporting period.