VIEW: APPS.PAY_HK_ASG_ELEMENTS_V

Source

View Text - Preformatted

SELECT /*+ index(pec pay_element_classification_pk) index(prr pay_run_results_pk) */ petf.rowid row_id, paa.assignment_action_id assignment_action_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)) amount from pay_assignment_actions paa, pay_payroll_actions ppa, pay_run_results prr, pay_element_types_f petf, pay_element_classifications pec, pay_run_result_values prrv, pay_input_values_f pivf where paa.payroll_action_id = ppa.payroll_action_id and ppa.action_type in ('R','Q') and ppa.action_status = 'C' and paa.action_status = 'C' and prr.assignment_action_id = paa.assignment_action_id and petf.element_type_id = prr.element_type_id and petf.element_type_id = pivf.element_type_id and prr.status in ('P','PA') /* Bug 13399650 */ and ppa.date_earned between petf.effective_start_date and petf.effective_end_date and ppa.date_earned 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 pivf.input_value_id = prrv.input_value_id and pivf.name = 'Pay Value' group by petf.rowid, paa.assignment_action_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

View Text - HTML Formatted

SELECT /*+ INDEX(PEC PAY_ELEMENT_CLASSIFICATION_PK) INDEX(PRR PAY_RUN_RESULTS_PK) */ PETF.ROWID ROW_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_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)) AMOUNT
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIVF
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA') /* BUG 13399650 */
AND PPA.DATE_EARNED BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED 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 PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIVF.NAME = 'PAY VALUE' GROUP BY PETF.ROWID
, PAA.ASSIGNMENT_ACTION_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