eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PAY_HK_IR56_EMPLOYEE_V

Product:	PAY - Payroll
Description:	Hong Kong localisation view.
Implementation/DBA Data:	APPS.PAY_HK_IR56_EMPLOYEE_V

View Text

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 (+)

Columns

Name
PERSON_ID
ASSIGNMENT_ID
EMPLOYER_RETURN_FILE_NO
DESIGNATION
DESIGNATION_NAME
EMPLOYER_RETURN_LAST_8
EMPLOYER_NAME
EMPLOYER_ADDRESS
TITLE
FULL_NAME
LAST_NAME
MAIDEN_NAME
EMPLOYEE_TAX_FILE_NO
HK_ID_CARD_NO
PASSPORT_NUMBER
SEX
MARITAL_STATUS
SPOUSE_NAME
SPOUSE_HK_ID_CARD_NO
SPOUSE_PASSPORT_NUMBER
RESIDENTIAL_ADDRESS
CORRESPONDENCE_ADDRESS
CAPACITY_EMPLOYED
LEGAL_EMPLOYER_ID
PRINCIPAL_EMPLOYER_NAME
EXPECTED_CESSATION_DATE
EMPLOYMENT_PERIOD
LEAVING_REASON
NEW_EMPLOYER_NAME_ADDRESS
FUTURE_CORRESPONDENCE_ADDRESS
DEPARTURE_DATE
DEPARTURE_REASON
DEPARTURE_REASON_SELECT
RETURN_TO_HK
PROBABLE_RETURN_DATE
SALARIES_TAX_BORNE_BY_EMPLOYER
COUNTRY_RETURNING_TO
SECONDED_TO
MONIES_HELD_SECTION52
AMOUNT_MONIES_HELD_SECTION52
REASON_NO_HOLD_SECTION52
SERVICE_DATE_START
SERVICE_TERMINATION_DATE
FINAL_PROCESS_DATE
SERVICE_TAX_REPORTING_YEAR