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