VIEW: APPS.PAY_HK_IR56_EMPLOYEE_V

Source

View Text - Preformatted

SELECT distinct papf.person_id person_id, paaf.assignment_id assignment_id, hoi.org_information1 employer_return_file_no, hoi.org_information2 designation, hoi.org_information3 designation_name, nvl(substr(hoi.org_information1, -8, 8), org_information1) employer_return_last_8, hou.name employer_name, LTRIM(RTRIM(hl.address_line_1)) || decode(hl.address_line_2, null, null, ', ' || LTRIM(RTRIM(hl.address_line_2))) || decode(hl.address_line_3, null, null, ', ' || LTRIM(RTRIM(hl.address_line_3))) || decode(hl.town_or_city, null, null, ', ' || LTRIM(RTRIM(hl.town_or_city))) || decode(hl.country, null, null, ', ' || LTRIM(RTRIM(hl.country))) employer_address, papf.title title, papf.per_information6 full_name, papf.last_name last_name, papf.previous_last_name maiden_name, papf.per_information9 employee_tax_file_no, papf.national_identifier hk_id_card_no, papf.per_information1 || decode(papf.per_information2, null, null, ' ' || ft.territory_short_name) passport_number, substr(papf.sex, 1, 1) sex, decode(papf.marital_status, 'M', 2, 1) marital_status, phisd.spouse_last_name || decode(phisd.spouse_first_name, null, null, ', ' || phisd.spouse_first_name) || decode(phisd.spouse_middle_names, null, null, ', ' || phisd.spouse_middle_names) spouse_name, phisd.spouse_national_identifier spouse_hk_id_card_no, phisd.spouse_passport_number || decode(phisd.spouse_country_of_issue, null, null, ' ' || ft2.territory_short_name) spouse_passport_number, decode(pa.address_line1,null,'', pa.address_line1 || decode(pa.address_line2, null, decode(pa.address_line3, null, decode(pa.town_or_city,null, decode(hlup.meaning,null, decode(pa.country,null,'',', '),', '), ', ') ,', ' ), ', ' )) || decode(pa.address_line2,null,'', pa.address_line2 || decode(pa.address_line3,null, decode(pa.town_or_city,null, decode(hlup.meaning,null,decode(pa.country,null,'',', '),', '), ', ') ,', ' ) ) || decode(pa.address_line3,null,'', pa.address_line3 || decode(pa.town_or_city,null, decode(hlup.meaning,null,decode(pa.country,null,'',', '),', '),', ') ) || decode(pa.town_or_city,null,'', pa.town_or_city || decode(hlup.meaning,null,decode(pa.country,null,'',', '),', ')) || decode(hlup.meaning,null,'', hlup.meaning || decode(pa.country,null,'',', ')) || pa.country residential_address, decode(pa2.address_line1,null,'', pa2.address_line1 || decode(pa2.address_line2, null, decode(pa2.address_line3, null, decode(pa2.town_or_city,null, decode(hlup2.meaning,null, decode(pa2.country,null,'',', '),', '), ', ') ,', ' ), ', ' )) || decode(pa2.address_line2,null,'', pa2.address_line2 || decode(pa2.address_line3,null, decode(pa2.town_or_city,null, decode(hlup2.meaning,null,decode(pa2.country,null,'',', '),', '), ', ') ,', ' ) ) || decode(pa2.address_line3,null,'', pa2.address_line3 || decode(pa2.town_or_city,null, decode(hlup2.meaning,null,decode(pa2.country,null,'',', '),', '),', ') ) || decode(pa2.town_or_city,null,'', pa2.town_or_city || decode(hlup2.meaning,null,decode(pa2.country,null,'',', '),', ')) || decode(hlup2.meaning,null,'', hlup2.meaning || decode(pa2.country,null,'',', ')) || pa2.country correspondence_address, paei.aei_information1 capacity_employed, hsck.segment1 legal_employer_id, hsck.segment2 principal_employer_name, to_char(nvl(pps.actual_termination_date, pps.projected_termination_date), 'DD/MM/YYYY') expected_cessation_date, to_char(greatest( to_date('01-04-' || to_char(fnd_number.canonical_to_number( to_char(nvl(pps.actual_termination_date, pps.projected_termination_date), 'YYYY')) + decode(sign(nvl(pps.actual_termination_date, pps.projected_termination_date) - to_date('01-04-' || to_char(nvl(pps.actual_termination_date, pps.projected_termination_date), 'YYYY'), 'DD-MM-YYYY')), -1,-1,0)), 'DD-MM-YYYY') , pps.date_start), 'DD/MM/YYYY') || ' to ' || to_char(nvl(pps.actual_termination_date, pps.projected_termination_date), 'DD/MM/YYYY') employment_period, hrl.meaning leaving_reason, pps.pds_information5 new_employer_name_address, pa3.address_line1 || decode(pa3.address_line2, null, null, ', ' || pa3.address_line2) || decode(pa3.town_or_city, null, null, ', ' || pa3.town_or_city) || decode(pa3.style, 'HK', ', ' || hlup3.meaning, null) || decode(pa3.country, null, null, ', ' || pa3.country) future_correspondence_address, pps.pds_information1 departure_date, hrl2.meaning departure_reason, pps.pds_information2 departure_reason_select, pps.pds_information3 return_to_hk, pps.pds_information4 probable_return_date, pps.pds_information6 salaries_tax_borne_by_employer, pps.pds_information7 country_returning_to, pps.pds_information8 seconded_to, hsck.segment6 monies_held_section52, hsck.segment7 amount_monies_held_section52, hsck.segment8 reason_no_hold_section52, pps.date_start service_date_start, nvl(pps.actual_termination_date, pps.projected_termination_date) Service_termination_date, nvl(pps.final_process_date, nvl(pps.actual_termination_date, pps.projected_termination_date)) final_process_date, to_char(nvl(pps.actual_termination_date, pps.projected_termination_date), 'YYYY') + decode( sign( nvl(pps.actual_termination_date, pps.projected_termination_date) - to_date('01-04-' || to_char( nvl(pps.actual_termination_date, pps.projected_termination_date), 'YYYY'), 'DD-MM-YYYY') ), -1, 0, 1) service_tax_reporting_year from hr_organization_information hoi, hr_organization_units hou, hr_locations hl, per_assignments_f paaf, per_people_f papf, pay_hk_ir56_spouse_details_v phisd, per_periods_of_service pps, per_assignment_extra_info paei, hr_soft_coding_keyflex hsck, hr_lookups hrl, hr_lookups hrl2, per_addresses pa, per_addresses pa2, per_addresses pa3, hr_lookups hlup, hr_lookups hlup2, hr_lookups hlup3, fnd_territories_vl ft, fnd_territories_vl ft2 WHERE paaf.person_id = papf.person_id and pps.period_of_service_id = paaf.period_of_service_id and nvl(pps.actual_termination_date, pps.projected_termination_date) between paaf.effective_start_date and papf.effective_end_date and nvl(pps.actual_termination_date, pps.projected_termination_date) between papf.effective_start_date and papf.effective_end_date and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id and hsck.segment1 = to_char(hou.organization_id) and hsck.segment1 = to_char(hoi.organization_id) and hoi.org_information_context = 'HK_LEGAL_EMPLOYER' and hl.location_id = hou.location_id and phisd.person_id (+) = pps.person_id and phisd.contact_type (+) in ('S') and nvl(pps.actual_termination_date, pps.projected_termination_date) between phisd.date_start (+) and nvl(phisd.date_end (+), to_date('31/12/4712', 'DD/MM/YYYY')) and nvl(pps.actual_termination_date, pps.projected_termination_date) between phisd.spouse_effective_start_date (+) and nvl(phisd.spouse_effective_end_date (+), to_date('31/12/4712', 'DD/MM/YYYY')) and pa.person_id (+) = pps.person_id and pa.address_type (+) = 'HK_R' and nvl(pps.actual_termination_date, pps.projected_termination_date) between pa.date_from (+) and nvl(pa.date_to (+), to_date('31/12/4712', 'DD/MM/YYYY')) and hlup.lookup_code (+) = pa.region_1 and hlup.lookup_type (+) = 'HK_AREA_CODES' and pa2.person_id (+) = pps.person_id and pa2.address_type (+) = 'HK_C' and nvl(pps.actual_termination_date, pps.projected_termination_date) between pa2.date_from (+) and nvl(pa2.date_to (+), to_date('31/12/4712', 'DD/MM/YYYY')) and hlup2.lookup_code (+) = pa2.region_1 and hlup2.lookup_type (+) = 'HK_AREA_CODES' and paei.assignment_id (+) = paaf.assignment_id and paei.information_type (+) = 'HR_EMPLOYMENT_INFO_HK' and pps.person_id = papf.person_id and hrl.lookup_type (+) = 'LEAV_REAS' and hrl.lookup_code (+) = pps.leaving_reason and pa3.person_id (+) = pps.person_id and pa3.address_type (+) = 'HK_F' and nvl(pps.actual_termination_date, pps.projected_termination_date) between pa3.date_from (+) and nvl(pa3.date_to (+), to_date('31/12/4712', 'DD/MM/YYYY')) and hlup3.lookup_code (+) = pa3.region_1 and hlup3.lookup_type (+) = 'HK_AREA_CODES' and hrl2.lookup_code (+) = pps.pds_information2 and hrl2.lookup_type (+) = 'HK_DEPARTURE_REASONS' and papf.per_information2 = ft.territory_code (+) and phisd.spouse_country_of_issue = ft2.territory_code (+)

View Text - HTML Formatted

SELECT DISTINCT PAPF.PERSON_ID PERSON_ID
, PAAF.ASSIGNMENT_ID ASSIGNMENT_ID
, HOI.ORG_INFORMATION1 EMPLOYER_RETURN_FILE_NO
, HOI.ORG_INFORMATION2 DESIGNATION
, HOI.ORG_INFORMATION3 DESIGNATION_NAME
, NVL(SUBSTR(HOI.ORG_INFORMATION1
, -8
, 8)
, ORG_INFORMATION1) EMPLOYER_RETURN_LAST_8
, HOU.NAME EMPLOYER_NAME
, LTRIM(RTRIM(HL.ADDRESS_LINE_1)) || DECODE(HL.ADDRESS_LINE_2
, NULL
, NULL
, '
, ' || LTRIM(RTRIM(HL.ADDRESS_LINE_2))) || DECODE(HL.ADDRESS_LINE_3
, NULL
, NULL
, '
, ' || LTRIM(RTRIM(HL.ADDRESS_LINE_3))) || DECODE(HL.TOWN_OR_CITY
, NULL
, NULL
, '
, ' || LTRIM(RTRIM(HL.TOWN_OR_CITY))) || DECODE(HL.COUNTRY
, NULL
, NULL
, '
, ' || LTRIM(RTRIM(HL.COUNTRY))) EMPLOYER_ADDRESS
, PAPF.TITLE TITLE
, PAPF.PER_INFORMATION6 FULL_NAME
, PAPF.LAST_NAME LAST_NAME
, PAPF.PREVIOUS_LAST_NAME MAIDEN_NAME
, PAPF.PER_INFORMATION9 EMPLOYEE_TAX_FILE_NO
, PAPF.NATIONAL_IDENTIFIER HK_ID_CARD_NO
, PAPF.PER_INFORMATION1 || DECODE(PAPF.PER_INFORMATION2
, NULL
, NULL
, ' ' || FT.TERRITORY_SHORT_NAME) PASSPORT_NUMBER
, SUBSTR(PAPF.SEX
, 1
, 1) SEX
, DECODE(PAPF.MARITAL_STATUS
, 'M'
, 2
, 1) MARITAL_STATUS
, PHISD.SPOUSE_LAST_NAME || DECODE(PHISD.SPOUSE_FIRST_NAME
, NULL
, NULL
, '
, ' || PHISD.SPOUSE_FIRST_NAME) || DECODE(PHISD.SPOUSE_MIDDLE_NAMES
, NULL
, NULL
, '
, ' || PHISD.SPOUSE_MIDDLE_NAMES) SPOUSE_NAME
, PHISD.SPOUSE_NATIONAL_IDENTIFIER SPOUSE_HK_ID_CARD_NO
, PHISD.SPOUSE_PASSPORT_NUMBER || DECODE(PHISD.SPOUSE_COUNTRY_OF_ISSUE
, NULL
, NULL
, ' ' || FT2.TERRITORY_SHORT_NAME) SPOUSE_PASSPORT_NUMBER
, DECODE(PA.ADDRESS_LINE1
, NULL
, ''
, PA.ADDRESS_LINE1 || DECODE(PA.ADDRESS_LINE2
, NULL
, DECODE(PA.ADDRESS_LINE3
, NULL
, DECODE(PA.TOWN_OR_CITY
, NULL
, DECODE(HLUP.MEANING
, NULL
, DECODE(PA.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')
, '
, ')
, '
, ' )
, '
, ' )) || DECODE(PA.ADDRESS_LINE2
, NULL
, ''
, PA.ADDRESS_LINE2 || DECODE(PA.ADDRESS_LINE3
, NULL
, DECODE(PA.TOWN_OR_CITY
, NULL
, DECODE(HLUP.MEANING
, NULL
, DECODE(PA.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')
, '
, ')
, '
, ' ) ) || DECODE(PA.ADDRESS_LINE3
, NULL
, ''
, PA.ADDRESS_LINE3 || DECODE(PA.TOWN_OR_CITY
, NULL
, DECODE(HLUP.MEANING
, NULL
, DECODE(PA.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')
, '
, ') ) || DECODE(PA.TOWN_OR_CITY
, NULL
, ''
, PA.TOWN_OR_CITY || DECODE(HLUP.MEANING
, NULL
, DECODE(PA.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')) || DECODE(HLUP.MEANING
, NULL
, ''
, HLUP.MEANING || DECODE(PA.COUNTRY
, NULL
, ''
, '
, ')) || PA.COUNTRY RESIDENTIAL_ADDRESS
, DECODE(PA2.ADDRESS_LINE1
, NULL
, ''
, PA2.ADDRESS_LINE1 || DECODE(PA2.ADDRESS_LINE2
, NULL
, DECODE(PA2.ADDRESS_LINE3
, NULL
, DECODE(PA2.TOWN_OR_CITY
, NULL
, DECODE(HLUP2.MEANING
, NULL
, DECODE(PA2.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')
, '
, ')
, '
, ' )
, '
, ' )) || DECODE(PA2.ADDRESS_LINE2
, NULL
, ''
, PA2.ADDRESS_LINE2 || DECODE(PA2.ADDRESS_LINE3
, NULL
, DECODE(PA2.TOWN_OR_CITY
, NULL
, DECODE(HLUP2.MEANING
, NULL
, DECODE(PA2.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')
, '
, ')
, '
, ' ) ) || DECODE(PA2.ADDRESS_LINE3
, NULL
, ''
, PA2.ADDRESS_LINE3 || DECODE(PA2.TOWN_OR_CITY
, NULL
, DECODE(HLUP2.MEANING
, NULL
, DECODE(PA2.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')
, '
, ') ) || DECODE(PA2.TOWN_OR_CITY
, NULL
, ''
, PA2.TOWN_OR_CITY || DECODE(HLUP2.MEANING
, NULL
, DECODE(PA2.COUNTRY
, NULL
, ''
, '
, ')
, '
, ')) || DECODE(HLUP2.MEANING
, NULL
, ''
, HLUP2.MEANING || DECODE(PA2.COUNTRY
, NULL
, ''
, '
, ')) || PA2.COUNTRY CORRESPONDENCE_ADDRESS
, PAEI.AEI_INFORMATION1 CAPACITY_EMPLOYED
, HSCK.SEGMENT1 LEGAL_EMPLOYER_ID
, HSCK.SEGMENT2 PRINCIPAL_EMPLOYER_NAME
, TO_CHAR(NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)
, 'DD/MM/YYYY') EXPECTED_CESSATION_DATE
, TO_CHAR(GREATEST( TO_DATE('01-04-' || TO_CHAR(FND_NUMBER.CANONICAL_TO_NUMBER( TO_CHAR(NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)
, 'YYYY')) + DECODE(SIGN(NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) - TO_DATE('01-04-' || TO_CHAR(NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)
, 'YYYY')
, 'DD-MM-YYYY'))
, -1
, -1
, 0))
, 'DD-MM-YYYY')
, PPS.DATE_START)
, 'DD/MM/YYYY') || ' TO ' || TO_CHAR(NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)
, 'DD/MM/YYYY') EMPLOYMENT_PERIOD
, HRL.MEANING LEAVING_REASON
, PPS.PDS_INFORMATION5 NEW_EMPLOYER_NAME_ADDRESS
, PA3.ADDRESS_LINE1 || DECODE(PA3.ADDRESS_LINE2
, NULL
, NULL
, '
, ' || PA3.ADDRESS_LINE2) || DECODE(PA3.TOWN_OR_CITY
, NULL
, NULL
, '
, ' || PA3.TOWN_OR_CITY) || DECODE(PA3.STYLE
, 'HK'
, '
, ' || HLUP3.MEANING
, NULL) || DECODE(PA3.COUNTRY
, NULL
, NULL
, '
, ' || PA3.COUNTRY) FUTURE_CORRESPONDENCE_ADDRESS
, PPS.PDS_INFORMATION1 DEPARTURE_DATE
, HRL2.MEANING DEPARTURE_REASON
, PPS.PDS_INFORMATION2 DEPARTURE_REASON_SELECT
, PPS.PDS_INFORMATION3 RETURN_TO_HK
, PPS.PDS_INFORMATION4 PROBABLE_RETURN_DATE
, PPS.PDS_INFORMATION6 SALARIES_TAX_BORNE_BY_EMPLOYER
, PPS.PDS_INFORMATION7 COUNTRY_RETURNING_TO
, PPS.PDS_INFORMATION8 SECONDED_TO
, HSCK.SEGMENT6 MONIES_HELD_SECTION52
, HSCK.SEGMENT7 AMOUNT_MONIES_HELD_SECTION52
, HSCK.SEGMENT8 REASON_NO_HOLD_SECTION52
, PPS.DATE_START SERVICE_DATE_START
, NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) SERVICE_TERMINATION_DATE
, NVL(PPS.FINAL_PROCESS_DATE
, NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)) FINAL_PROCESS_DATE
, TO_CHAR(NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)
, 'YYYY') + DECODE( SIGN( NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) - TO_DATE('01-04-' || TO_CHAR( NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE)
, 'YYYY')
, 'DD-MM-YYYY') )
, -1
, 0
, 1) SERVICE_TAX_REPORTING_YEAR
FROM HR_ORGANIZATION_INFORMATION HOI
, HR_ORGANIZATION_UNITS HOU
, HR_LOCATIONS HL
, PER_ASSIGNMENTS_F PAAF
, PER_PEOPLE_F PAPF
, PAY_HK_IR56_SPOUSE_DETAILS_V PHISD
, PER_PERIODS_OF_SERVICE PPS
, PER_ASSIGNMENT_EXTRA_INFO PAEI
, HR_SOFT_CODING_KEYFLEX HSCK
, HR_LOOKUPS HRL
, HR_LOOKUPS HRL2
, PER_ADDRESSES PA
, PER_ADDRESSES PA2
, PER_ADDRESSES PA3
, HR_LOOKUPS HLUP
, HR_LOOKUPS HLUP2
, HR_LOOKUPS HLUP3
, FND_TERRITORIES_VL FT
, FND_TERRITORIES_VL FT2
WHERE PAAF.PERSON_ID = PAPF.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PAAF.PERIOD_OF_SERVICE_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID
AND HSCK.SEGMENT1 = TO_CHAR(HOU.ORGANIZATION_ID)
AND HSCK.SEGMENT1 = TO_CHAR(HOI.ORGANIZATION_ID)
AND HOI.ORG_INFORMATION_CONTEXT = 'HK_LEGAL_EMPLOYER'
AND HL.LOCATION_ID = HOU.LOCATION_ID
AND PHISD.PERSON_ID (+) = PPS.PERSON_ID
AND PHISD.CONTACT_TYPE (+) IN ('S')
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PHISD.DATE_START (+)
AND NVL(PHISD.DATE_END (+)
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PHISD.SPOUSE_EFFECTIVE_START_DATE (+)
AND NVL(PHISD.SPOUSE_EFFECTIVE_END_DATE (+)
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND PA.PERSON_ID (+) = PPS.PERSON_ID
AND PA.ADDRESS_TYPE (+) = 'HK_R'
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PA.DATE_FROM (+)
AND NVL(PA.DATE_TO (+)
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND HLUP.LOOKUP_CODE (+) = PA.REGION_1
AND HLUP.LOOKUP_TYPE (+) = 'HK_AREA_CODES'
AND PA2.PERSON_ID (+) = PPS.PERSON_ID
AND PA2.ADDRESS_TYPE (+) = 'HK_C'
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PA2.DATE_FROM (+)
AND NVL(PA2.DATE_TO (+)
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND HLUP2.LOOKUP_CODE (+) = PA2.REGION_1
AND HLUP2.LOOKUP_TYPE (+) = 'HK_AREA_CODES'
AND PAEI.ASSIGNMENT_ID (+) = PAAF.ASSIGNMENT_ID
AND PAEI.INFORMATION_TYPE (+) = 'HR_EMPLOYMENT_INFO_HK'
AND PPS.PERSON_ID = PAPF.PERSON_ID
AND HRL.LOOKUP_TYPE (+) = 'LEAV_REAS'
AND HRL.LOOKUP_CODE (+) = PPS.LEAVING_REASON
AND PA3.PERSON_ID (+) = PPS.PERSON_ID
AND PA3.ADDRESS_TYPE (+) = 'HK_F'
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE) BETWEEN PA3.DATE_FROM (+)
AND NVL(PA3.DATE_TO (+)
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND HLUP3.LOOKUP_CODE (+) = PA3.REGION_1
AND HLUP3.LOOKUP_TYPE (+) = 'HK_AREA_CODES'
AND HRL2.LOOKUP_CODE (+) = PPS.PDS_INFORMATION2
AND HRL2.LOOKUP_TYPE (+) = 'HK_DEPARTURE_REASONS'
AND PAPF.PER_INFORMATION2 = FT.TERRITORY_CODE (+)
AND PHISD.SPOUSE_COUNTRY_OF_ISSUE = FT2.TERRITORY_CODE (+)