Product: | PAY - Payroll |
---|---|
Description: | Hong Kong localisation view. |
Implementation/DBA Data: | APPS.PAY_HK_IR56_SPOUSE_DETAILS_V |
SELECT PCR.PRIMARY_CONTACT_FLAG PRIMARY_CONTACT_FLAG
, PCR.PERSON_ID PERSON_ID
, PCR.CONTACT_PERSON_ID CONTACT_PERSON_ID
, PCR.DATE_START DATE_START
, PCR.DATE_END DATE_END
, PCR.CONTACT_TYPE CONTACT_TYPE
, PAPF.NATIONAL_IDENTIFIER SPOUSE_NATIONAL_IDENTIFIER
, PAPF.LAST_NAME SPOUSE_LAST_NAME
, PAPF.MIDDLE_NAMES SPOUSE_MIDDLE_NAMES
, PAPF.FIRST_NAME SPOUSE_FIRST_NAME
, PAPF.EFFECTIVE_START_DATE SPOUSE_EFFECTIVE_START_DATE
, PAPF.EFFECTIVE_END_DATE SPOUSE_EFFECTIVE_END_DATE
, PAPF.PER_INFORMATION1 SPOUSE_PASSPORT_NUMBER
, PAPF.PER_INFORMATION2 SPOUSE_COUNTRY_OF_ISSUE
FROM PER_CONTACT_RELATIONSHIPS PCR
, PER_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = PCR.CONTACT_PERSON_ID
AND PCR.CONTACT_TYPE = 'S'
AND PCR.PRIMARY_CONTACT_FLAG = 'Y' UNION ALL SELECT PCR.PRIMARY_CONTACT_FLAG PRIMARY_CONTACT_FLAG
, PCR.PERSON_ID PERSON_ID
, PCR.CONTACT_PERSON_ID CONTACT_PERSON_ID
, PCR.DATE_START DATE_START
, PCR.DATE_END DATE_END
, PCR.CONTACT_TYPE CONTACT_TYPE
, PAPF.NATIONAL_IDENTIFIER SPOUSE_NATIONAL_IDENTIFIER
, PAPF.LAST_NAME SPOUSE_LAST_NAME
, PAPF.MIDDLE_NAMES SPOUSE_MIDDLE_NAMES
, PAPF.FIRST_NAME SPOUSE_FIRST_NAME
, PAPF.EFFECTIVE_START_DATE SPOUSE_EFFECTIVE_START_DATE
, PAPF.EFFECTIVE_END_DATE SPOUSE_EFFECTIVE_END_DATE
, PAPF.PER_INFORMATION1 SPOUSE_PASSPORT_NUMBER
, PAPF.PER_INFORMATION2 SPOUSE_COUNTRY_OF_ISSUE
FROM PER_CONTACT_RELATIONSHIPS PCR
, PER_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = PCR.CONTACT_PERSON_ID
AND PCR.CONTACT_TYPE = 'S'
AND NOT EXISTS (SELECT NULL
FROM PER_CONTACT_RELATIONSHIPS PCR2
WHERE PCR2.PERSON_ID = PCR.PERSON_ID
AND PCR2.CONTACT_TYPE = 'S'
AND PCR2.PRIMARY_CONTACT_FLAG = 'Y')