VIEW: APPS.PAY_HK_IR56_SPOUSE_DETAILS_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')