View: PAY_HK_MPF_REMITTANCE_V

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

View Text

SELECT /*+ USE_NL(PPF
, PPS
, PAF
, PIVF
, PRRV
, PPA
, PAA
, PRR
, PETF) INDEX(PPF PER_PEOPLE_F_PK) INDEX(PPS PER_PERIODS_OF_SERVICE_PK) INDEX(PAF PER_ASSIGNMENTS_F_PK) INDEX(PIVF PAY_INPUT_VALUES_F_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) INDEX(PPA PAY_PAYROLL_ACTIONS_PK) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PRR PAY_RUN_RESULTS_N1) INDEX(PETF PAY_ELEMENT_TYPES_F_UK2) */ PPF.PERSON_ID PERSON_ID
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PETF.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PRR.RUN_RESULT_ID RUN_RESULT_ID
, PPF.LAST_NAME || DECODE(PPF.FIRST_NAME
, NULL
, NULL
, '
, ' || PPF.FIRST_NAME) FULL_NAME
, PPS.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, PPS.DATE_START DATE_START
, PPS.ACTUAL_TERMINATION_DATE ACTUAL_TERMINATION_DATE
, PETF.ELEMENT_NAME ELEMENT_NAME
, MAX(DECODE(PIVF.NAME
, 'PAY VALUE'
, TO_NUMBER(PRRV.RESULT_VALUE)
, NULL)) VALUE
, MAX(DECODE(PIVF.NAME
, 'SOURCE'
, TO_NUMBER(PRRV.RESULT_VALUE)
, NULL)) SCHEME_ID
, MAX(DECODE(PIVF.NAME
, 'MEMBERSHIP ID'
, PRRV.RESULT_VALUE
, NULL)) MEMBERSHIP_NUMBER
, MAX(DECODE(PIVF.NAME
, 'PERIOD START DATE'
, FND_DATE.CANONICAL_TO_DATE(PRRV.RESULT_VALUE)
, NULL)) PERIOD_START_DATE
, MAX(DECODE(PIVF.NAME
, 'PERIOD END DATE'
, FND_DATE.CANONICAL_TO_DATE(PRRV.RESULT_VALUE)
, NULL)) PERIOD_END_DATE
, MAX(DECODE(PIVF.NAME
, 'ASSESSED RI'
, TO_NUMBER(PRRV.RESULT_VALUE)
, NULL)) ASSESSED_RI
FROM PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ELEMENT_TYPES_F PETF
, PAY_INPUT_VALUES_F PIVF
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PER_PERIODS_OF_SERVICE PPS
WHERE PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PAF.PERIOD_OF_SERVICE_ID
AND PPS.PERSON_ID = PAF.PERSON_ID /* BUG 6150615*/
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PIVF.EFFECTIVE_START_DATE
AND PIVF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PIVF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID /* BUG 6150615 */
AND PETF.LEGISLATION_CODE = 'HK' /* BUG 6150615 */
AND PIVF.LEGISLATION_CODE = 'HK' /* BUG 6150615 */
AND PETF.ELEMENT_NAME IN ('MPF WITHHELD'
, 'RETRO MPF WITHHELD'
, 'VOLUNTARY MPF WITHHELD'
, 'MPF LIABILITY'
, 'RETRO MPF LIABILITY'
, 'VOLUNTARY MPF LIABILITY') GROUP BY PPF.PERSON_ID
, PAF.ASSIGNMENT_ID
, PPA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PPA.EFFECTIVE_DATE
, PETF.ELEMENT_TYPE_ID
, PRR.RUN_RESULT_ID
, PPF.LAST_NAME || DECODE(PPF.FIRST_NAME
, NULL
, NULL
, '
, ' || PPF.FIRST_NAME)
, PPS.PERIOD_OF_SERVICE_ID
, PPS.DATE_START
, PPS.ACTUAL_TERMINATION_DATE
, PETF.ELEMENT_NAME

Columns