VIEW: APPS.PAY_HK_ASG_ELEMENT_PAYMENTS_V

Source

View Text - Preformatted

SELECT pai.rowid row_id, pai.locking_action_id assignment_action_id, paaf.assignment_id assignment_id, decode(petf.element_name, 'MPF Withheld', 1, 'MPF Liability', 1, 'Retro MPF Withheld', 2, 'Retro MPF Liability', 2, 'Voluntary MPF Withheld', 3, 'Voluntary MPF Liability', 3, 'Voluntary MPF Lump Sums', 3, 'MPF Information', 3, 99 ) element_name_sort, decode(petf.element_name, 'Retro MPF Withheld', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Withheld', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Retro MPF Liability', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Liability', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Voluntary MPF Lump Sums', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), nvl(petf.reporting_name, petf.element_name) ) element_reporting_name, decode(instr(pec.classification_name, 'Earnings'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Direct Payment'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Deductions'), 0, null, 'Deductions') || decode(instr(pec.classification_name, 'Employer Liabilities'), 0, null, 'Employer Liabilities') || decode(instr(pec.classification_name, 'Company Quarters'), 0, null, 'Earnings') classification_name, petf.processing_priority processing_priority, sum(nvl(prrv.result_value,0)) payment_amount, null period_start_date, null period_end_date, null assessed_ri from per_assignments_f paaf, pay_assignment_actions paa, pay_payroll_actions ppa, pay_action_interlocks pai, pay_run_results prr, pay_element_types_f petf, pay_element_classifications pec, pay_run_result_values prrv, pay_input_values_f pivf where paaf.assignment_id = paa.assignment_id and paa.payroll_action_id = ppa.payroll_action_id and ppa.action_type in ('U','P') and ppa.action_status = 'C' and paa.action_status = 'C' and pai.locking_action_id = paa.assignment_action_id and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date and prr.assignment_action_id = pai.locked_action_id and petf.element_type_id = prr.element_type_id and petf.element_type_id = pivf.element_type_id and ppa.effective_date between petf.effective_start_date and petf.effective_end_date and ppa.effective_date between pivf.effective_start_date and pivf.effective_end_date and pec.classification_id = petf.classification_id and (pec.legislation_code is null or pec.legislation_code = 'HK' ) and (instr(pec.classification_name, 'Earnings') > 0 or instr(pec.classification_name, 'Direct Payment') > 0 or instr(pec.classification_name, 'Deductions') > 0 or instr(pec.classification_name, 'Employer Liabilities') > 0 or instr(pec.classification_name, 'Company Quarters') > 0 ) and prrv.run_result_id = prr.run_result_id and prr.status in ('P','PA') /* Bug 13399650 */ and pivf.input_value_id = prrv.input_value_id and pivf.name = 'Pay Value' and NOT exists (select null from pay_hk_asg_mpf_data_v psd where psd.run_result_id = prr.run_result_id and rownum < 2) group by pai.rowid, pai.locking_action_id, paaf.assignment_id, decode(petf.element_name, 'MPF Withheld', 1, 'MPF Liability', 1, 'Retro MPF Withheld', 2, 'Retro MPF Liability', 2, 'Voluntary MPF Withheld', 3, 'Voluntary MPF Liability', 3, 'Voluntary MPF Lump Sums', 3, 'MPF Information', 3, 99 ), decode(petf.element_name, 'Retro MPF Withheld', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Withheld', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Retro MPF Liability', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Liability', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Voluntary MPF Lump Sums', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), nvl(petf.reporting_name, petf.element_name) ), decode(instr(pec.classification_name, 'Earnings'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Direct Payment'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Deductions'), 0, null, 'Deductions') || decode(instr(pec.classification_name, 'Employer Liabilities'), 0, null, 'Employer Liabilities') || decode(instr(pec.classification_name, 'Company Quarters'), 0, null, 'Earnings'), pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id) || decode(petf.element_name, 'Retro MPF Withheld', 'Retro', 'Voluntary MPF Withheld', 'Voluntary', null), petf.processing_priority union all select pai.rowid row_id, pai.locking_action_id assignment_action_id, paaf.assignment_id assignment_id, decode(petf.element_name, 'MPF Withheld', 1, 'MPF Liability', 1, 'Retro MPF Withheld', 2, 'Retro MPF Liability', 2, 'Voluntary MPF Withheld', 3, 'Voluntary MPF Liability', 3, 'Voluntary MPF Lump Sums', 3, 'MPF Information', 3, 99 ) element_name_sort, decode(petf.element_name, 'Retro MPF Withheld', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Withheld', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Retro MPF Liability', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Liability', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Voluntary MPF Lump Sums', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), nvl(petf.reporting_name, petf.element_name) ) element_reporting_name, decode(instr(pec.classification_name, 'Earnings'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Direct Payment'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Deductions'), 0, null, 'Deductions') || decode(instr(pec.classification_name, 'Employer Liabilities'), 0, null, 'Employer Liabilities') || decode(instr(pec.classification_name, 'Company Quarters'), 0, null, 'Earnings') classification_name, petf.processing_priority processing_priority, sum(nvl(prrv.result_value,0)) payment_amount, psd.period_start_date period_start_date, psd.period_end_date period_end_date, psd.assessed_ri assessed_ri from per_assignments_f paaf, pay_assignment_actions paa, pay_payroll_actions ppa, pay_action_interlocks pai, pay_run_results prr, pay_element_types_f petf, pay_element_classifications pec, pay_run_result_values prrv, pay_input_values_f pivf, pay_hk_asg_mpf_data_v psd where paaf.assignment_id = paa.assignment_id and paa.payroll_action_id = ppa.payroll_action_id and ppa.action_type in ('U','P') and ppa.action_status = 'C' and paa.action_status = 'C' and pai.locking_action_id = paa.assignment_action_id and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date and prr.assignment_action_id = pai.locked_action_id and petf.element_type_id = prr.element_type_id and petf.element_type_id = pivf.element_type_id and ppa.effective_date between petf.effective_start_date and petf.effective_end_date and ppa.effective_date between pivf.effective_start_date and pivf.effective_end_date and pec.classification_id = petf.classification_id and (pec.legislation_code is null or pec.legislation_code = 'HK' ) and (instr(pec.classification_name, 'Earnings') > 0 or instr(pec.classification_name, 'Direct Payment') > 0 or instr(pec.classification_name, 'Deductions') > 0 or instr(pec.classification_name, 'Employer Liabilities') > 0 or instr(pec.classification_name, 'Company Quarters') > 0 ) and prrv.run_result_id = prr.run_result_id and prr.status in ('P','PA') /* Bug 13399650 */ and pivf.input_value_id = prrv.input_value_id and pivf.name = 'Pay Value' and psd.run_result_id = prr.run_result_id and psd.assignment_action_id = prr.assignment_action_id group by pai.rowid, pai.locking_action_id, paaf.assignment_id, decode(petf.element_name, 'MPF Withheld', 1, 'MPF Liability', 1, 'Retro MPF Withheld', 2, 'Retro MPF Liability', 2, 'Voluntary MPF Withheld', 3, 'Voluntary MPF Liability', 3, 'Voluntary MPF Lump Sums', 3, 'MPF Information', 3, 99 ), decode(petf.element_name, 'Retro MPF Withheld', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Withheld', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Retro MPF Liability', pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 'Voluntary MPF Liability', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), 'Voluntary MPF Lump Sums', 'VC: ' || substr(pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id), 1, 16), nvl(petf.reporting_name, petf.element_name) ), decode(instr(pec.classification_name, 'Earnings'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Direct Payment'), 0, null, 'Earnings') || decode(instr(pec.classification_name, 'Deductions'), 0, null, 'Deductions') || decode(instr(pec.classification_name, 'Employer Liabilities'), 0, null, 'Employer Liabilities')|| decode(instr(pec.classification_name, 'Company Quarters'), 0, null, 'Earnings'), pay_hk_payslip_archive.get_scheme_name(prr.run_result_id, prr.assignment_action_id, ppa.business_group_id) || decode(petf.element_name, 'Retro MPF Withheld', 'Retro', 'Voluntary MPF Withheld', 'Voluntary', null), petf.processing_priority, psd.period_start_date, psd.period_end_date, psd.assessed_ri

View Text - HTML Formatted

SELECT PAI.ROWID ROW_ID
, PAI.LOCKING_ACTION_ID ASSIGNMENT_ACTION_ID
, PAAF.ASSIGNMENT_ID ASSIGNMENT_ID
, DECODE(PETF.ELEMENT_NAME
, 'MPF WITHHELD'
, 1
, 'MPF LIABILITY'
, 1
, 'RETRO MPF WITHHELD'
, 2
, 'RETRO MPF LIABILITY'
, 2
, 'VOLUNTARY MPF WITHHELD'
, 3
, 'VOLUNTARY MPF LIABILITY'
, 3
, 'VOLUNTARY MPF LUMP SUMS'
, 3
, 'MPF INFORMATION'
, 3
, 99 ) ELEMENT_NAME_SORT
, DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF WITHHELD'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'RETRO MPF LIABILITY'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF LIABILITY'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'VOLUNTARY MPF LUMP SUMS'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, NVL(PETF.REPORTING_NAME
, PETF.ELEMENT_NAME) ) ELEMENT_REPORTING_NAME
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES')
, 0
, NULL
, 'EMPLOYER LIABILITIES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS')
, 0
, NULL
, 'EARNINGS') CLASSIFICATION_NAME
, PETF.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(NVL(PRRV.RESULT_VALUE
, 0)) PAYMENT_AMOUNT
, NULL PERIOD_START_DATE
, NULL PERIOD_END_DATE
, NULL ASSESSED_RI
FROM PER_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIVF
WHERE PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PRR.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PIVF.EFFECTIVE_START_DATE
AND PIVF.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'HK' )
AND (INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS') > 0 )
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.STATUS IN ('P'
, 'PA') /* BUG 13399650 */
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIVF.NAME = 'PAY VALUE'
AND NOT EXISTS (SELECT NULL
FROM PAY_HK_ASG_MPF_DATA_V PSD
WHERE PSD.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND ROWNUM < 2) GROUP BY PAI.ROWID
, PAI.LOCKING_ACTION_ID
, PAAF.ASSIGNMENT_ID
, DECODE(PETF.ELEMENT_NAME
, 'MPF WITHHELD'
, 1
, 'MPF LIABILITY'
, 1
, 'RETRO MPF WITHHELD'
, 2
, 'RETRO MPF LIABILITY'
, 2
, 'VOLUNTARY MPF WITHHELD'
, 3
, 'VOLUNTARY MPF LIABILITY'
, 3
, 'VOLUNTARY MPF LUMP SUMS'
, 3
, 'MPF INFORMATION'
, 3
, 99 )
, DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF WITHHELD'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'RETRO MPF LIABILITY'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF LIABILITY'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'VOLUNTARY MPF LUMP SUMS'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, NVL(PETF.REPORTING_NAME
, PETF.ELEMENT_NAME) )
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES')
, 0
, NULL
, 'EMPLOYER LIABILITIES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS')
, 0
, NULL
, 'EARNINGS')
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID) || DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, 'RETRO'
, 'VOLUNTARY MPF WITHHELD'
, 'VOLUNTARY'
, NULL)
, PETF.PROCESSING_PRIORITY UNION ALL SELECT PAI.ROWID ROW_ID
, PAI.LOCKING_ACTION_ID ASSIGNMENT_ACTION_ID
, PAAF.ASSIGNMENT_ID ASSIGNMENT_ID
, DECODE(PETF.ELEMENT_NAME
, 'MPF WITHHELD'
, 1
, 'MPF LIABILITY'
, 1
, 'RETRO MPF WITHHELD'
, 2
, 'RETRO MPF LIABILITY'
, 2
, 'VOLUNTARY MPF WITHHELD'
, 3
, 'VOLUNTARY MPF LIABILITY'
, 3
, 'VOLUNTARY MPF LUMP SUMS'
, 3
, 'MPF INFORMATION'
, 3
, 99 ) ELEMENT_NAME_SORT
, DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF WITHHELD'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'RETRO MPF LIABILITY'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF LIABILITY'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'VOLUNTARY MPF LUMP SUMS'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, NVL(PETF.REPORTING_NAME
, PETF.ELEMENT_NAME) ) ELEMENT_REPORTING_NAME
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES')
, 0
, NULL
, 'EMPLOYER LIABILITIES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS')
, 0
, NULL
, 'EARNINGS') CLASSIFICATION_NAME
, PETF.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(NVL(PRRV.RESULT_VALUE
, 0)) PAYMENT_AMOUNT
, PSD.PERIOD_START_DATE PERIOD_START_DATE
, PSD.PERIOD_END_DATE PERIOD_END_DATE
, PSD.ASSESSED_RI ASSESSED_RI
FROM PER_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIVF
, PAY_HK_ASG_MPF_DATA_V PSD
WHERE PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PRR.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PIVF.EFFECTIVE_START_DATE
AND PIVF.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'HK' )
AND (INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS') > 0 )
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.STATUS IN ('P'
, 'PA') /* BUG 13399650 */
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIVF.NAME = 'PAY VALUE'
AND PSD.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PSD.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID GROUP BY PAI.ROWID
, PAI.LOCKING_ACTION_ID
, PAAF.ASSIGNMENT_ID
, DECODE(PETF.ELEMENT_NAME
, 'MPF WITHHELD'
, 1
, 'MPF LIABILITY'
, 1
, 'RETRO MPF WITHHELD'
, 2
, 'RETRO MPF LIABILITY'
, 2
, 'VOLUNTARY MPF WITHHELD'
, 3
, 'VOLUNTARY MPF LIABILITY'
, 3
, 'VOLUNTARY MPF LUMP SUMS'
, 3
, 'MPF INFORMATION'
, 3
, 99 )
, DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF WITHHELD'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'RETRO MPF LIABILITY'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF LIABILITY'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'VOLUNTARY MPF LUMP SUMS'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, NVL(PETF.REPORTING_NAME
, PETF.ELEMENT_NAME) )
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES')
, 0
, NULL
, 'EMPLOYER LIABILITIES')|| DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS')
, 0
, NULL
, 'EARNINGS')
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID) || DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, 'RETRO'
, 'VOLUNTARY MPF WITHHELD'
, 'VOLUNTARY'
, NULL)
, PETF.PROCESSING_PRIORITY
, PSD.PERIOD_START_DATE
, PSD.PERIOD_END_DATE
, PSD.ASSESSED_RI