VIEW: APPS.PAY_HK_MPF_REMITTANCE_V

Source

View Text - Preformatted

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', 'Retro MPF Liability for First Period', 'Retro MPF Liability for Second Period', '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

View Text - HTML Formatted

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'
, 'RETRO MPF LIABILITY FOR FIRST PERIOD'
, 'RETRO MPF LIABILITY FOR SECOND PERIOD'
, '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