VIEW: APPS.PAY_ZA_SOE_DED_ELEMENT_V1

Source

View Text - Preformatted

SELECT pet.element_type_id row_id ,nvl (pettl.reporting_name ,pettl.element_name) report_name ,pectl.classification_name ,sum (fnd_number.canonical_to_number (prv.result_value)) result_value ,pet.processing_priority ,prr.assignment_action_id ,pec.classification_name base_classification_name FROM pay_element_types_f_tl pettl ,pay_element_classifications_tl pectl ,pay_element_types_f pet ,pay_element_classifications pec ,pay_input_values_f piv ,pay_run_result_values prv ,pay_run_results prr ,pay_assignment_actions asact ,pay_payroll_actions pact WHERE pec.classification_id = pectl.classification_id AND pectl.language = userenv ('LANG') AND pet.element_type_id = pettl.element_type_id AND pettl.language = userenv ('LANG') AND prr.element_type_id = pet.element_type_id AND prr.status IN ('P','PA') AND pact.payroll_action_id = asact.payroll_action_id AND asact.assignment_action_id = prr.assignment_action_id AND pet.classification_id = pec.classification_id AND piv.element_type_id = pet.element_type_id AND prv.input_value_id = piv.input_value_id AND prv.run_result_id = prr.run_result_id AND piv.name = 'Pay Value' AND piv.uom = 'M' AND pact.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pact.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date and not exists (select pet1.element_type_id from pay_element_types_f pet1 where pet1.element_name in ('Medical Tax Credit Used on LMPSM1', 'Medical Tax Credit Used on LMPSM2', 'Medical Tax Credit Used on LMPSM3', 'Medical Tax Credit Used on LMPSM4', 'Medical Tax Credit Used on LMPSM5', 'Medical Tax Credit Used on LMPSM6', 'Medical Tax Credit Used on LMPSM7', 'Medical Tax Credit Used on LMPSM8', 'Medical Tax Credit Used on LMPSM9', 'Medical Tax Credit Used on LMPSM10', 'ZA_Tax_On_Multiple_Lump_Sums', 'ZA_Tax_On_Lump_Sums' ) and pet1.element_type_id = pet.element_type_id and pet1.legislation_code = 'ZA') GROUP BY pec.classification_name ,prr.assignment_action_id ,pet.processing_priority ,pectl.classification_name ,nvl (pettl.reporting_name ,pettl.element_name) ,pet.element_type_id UNION ALL SELECT pet.element_type_id row_id ,nvl (pettl.reporting_name ,pettl.element_name) report_name ,pectl.classification_name ,sum (fnd_number.canonical_to_number (prv.result_value)) result_value ,pet.processing_priority ,pac.locking_action_id ,pec.classification_name base_classification_name FROM pay_element_types_f pet ,pay_element_types_f_tl pettl ,pay_element_classifications pec ,pay_element_classifications_tl pectl ,pay_input_values_f piv ,pay_run_result_values prv ,pay_run_results prr ,pay_assignment_actions asact ,pay_payroll_actions pact ,pay_action_interlocks pac WHERE pec.classification_id = pectl.classification_id AND pectl.language = userenv ('LANG') AND pet.element_type_id = pettl.element_type_id AND pettl.language = userenv ('LANG') AND prr.element_type_id = pet.element_type_id AND prr.status IN ('P','PA') AND pact.payroll_action_id = asact.payroll_action_id AND asact.assignment_action_id = prr.assignment_action_id AND pet.classification_id = pec.classification_id AND piv.element_type_id = pet.element_type_id AND prv.input_value_id = piv.input_value_id AND prv.run_result_id = prr.run_result_id AND piv.name = 'Pay Value' AND piv.uom = 'M' AND pact.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pact.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND pac.locked_action_id = prr.assignment_action_id AND EXISTS ( SELECT NULL FROM pay_payroll_actions ppal ,pay_assignment_actions paal WHERE paal.assignment_action_id = pac.locking_action_id AND ppal.payroll_action_id = paal.payroll_action_id AND ppal.action_type IN ('P','U') ) and not exists (select pet1.element_type_id from pay_element_types_f pet1 where pet1.element_name in ('Medical Tax Credit Used on LMPSM1', 'Medical Tax Credit Used on LMPSM2', 'Medical Tax Credit Used on LMPSM3', 'Medical Tax Credit Used on LMPSM4', 'Medical Tax Credit Used on LMPSM5', 'Medical Tax Credit Used on LMPSM6', 'Medical Tax Credit Used on LMPSM7', 'Medical Tax Credit Used on LMPSM8', 'Medical Tax Credit Used on LMPSM9', 'Medical Tax Credit Used on LMPSM10', 'ZA_Tax_On_Multiple_Lump_Sums', 'ZA_Tax_On_Lump_Sums' ) and pet1.element_type_id = pet.element_type_id and pet1.legislation_code = 'ZA') GROUP BY pec.classification_name ,pac.locking_action_id ,pet.processing_priority ,pectl.classification_name ,nvl (pettl.reporting_name ,pettl.element_name) ,pet.element_type_id UNION ALL SELECT 1 row_id ,nvl (pettl.reporting_name ,pettl.element_name) report_name ,pectl.classification_name ,sum (fnd_number.canonical_to_number (prv.result_value)) result_value ,8900 processing_priority ,prr.assignment_action_id ,pec.classification_name base_classification_name FROM pay_element_types_f_tl pettl ,pay_element_classifications_tl pectl ,pay_element_types_f pet ,pay_element_classifications pec ,pay_input_values_f piv ,pay_run_result_values prv ,pay_run_results prr ,pay_assignment_actions asact ,pay_payroll_actions pact WHERE pec.classification_id = pectl.classification_id AND pectl.language = userenv ('LANG') AND pet.element_type_id = pettl.element_type_id AND pettl.language = userenv ('LANG') AND prr.element_type_id = pet.element_type_id AND prr.status IN ('P','PA') AND pact.payroll_action_id = asact.payroll_action_id AND asact.assignment_action_id = prr.assignment_action_id AND pet.classification_id = pec.classification_id AND piv.element_type_id = pet.element_type_id AND prv.input_value_id = piv.input_value_id AND prv.run_result_id = prr.run_result_id AND piv.name = 'Pay Value' AND piv.uom = 'M' AND pact.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pact.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date and pet.element_name in ('Medical Tax Credit Used on LMPSM1', 'Medical Tax Credit Used on LMPSM2', 'Medical Tax Credit Used on LMPSM3', 'Medical Tax Credit Used on LMPSM4', 'Medical Tax Credit Used on LMPSM5', 'Medical Tax Credit Used on LMPSM6', 'Medical Tax Credit Used on LMPSM7', 'Medical Tax Credit Used on LMPSM8', 'Medical Tax Credit Used on LMPSM9', 'Medical Tax Credit Used on LMPSM10', 'ZA_Tax_On_Multiple_Lump_Sums', 'ZA_Tax_On_Lump_Sums' ) and pet.legislation_code = 'ZA' GROUP BY pec.classification_name ,prr.assignment_action_id ,pectl.classification_name ,nvl (pettl.reporting_name ,pettl.element_name) Union All SELECT 1 row_id ,nvl (pettl.reporting_name ,pettl.element_name) report_name ,pectl.classification_name ,sum (fnd_number.canonical_to_number (prv.result_value)) result_value ,8900 ,pac.locking_action_id ,pec.classification_name base_classification_name FROM pay_element_types_f pet ,pay_element_types_f_tl pettl ,pay_element_classifications pec ,pay_element_classifications_tl pectl ,pay_input_values_f piv ,pay_run_result_values prv ,pay_run_results prr ,pay_assignment_actions asact ,pay_payroll_actions pact ,pay_action_interlocks pac WHERE pec.classification_id = pectl.classification_id AND pectl.language = userenv ('LANG') AND pet.element_type_id = pettl.element_type_id AND pettl.language = userenv ('LANG') AND prr.element_type_id = pet.element_type_id AND prr.status IN ('P','PA') AND pact.payroll_action_id = asact.payroll_action_id AND asact.assignment_action_id = prr.assignment_action_id AND pet.classification_id = pec.classification_id AND piv.element_type_id = pet.element_type_id AND prv.input_value_id = piv.input_value_id AND prv.run_result_id = prr.run_result_id AND piv.name = 'Pay Value' AND piv.uom = 'M' AND pact.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pact.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND pac.locked_action_id = prr.assignment_action_id AND EXISTS ( SELECT NULL FROM pay_payroll_actions ppal ,pay_assignment_actions paal WHERE paal.assignment_action_id = pac.locking_action_id AND ppal.payroll_action_id = paal.payroll_action_id AND ppal.action_type IN ('P','U') ) and pet.element_name in ('Medical Tax Credit Used on LMPSM1', 'Medical Tax Credit Used on LMPSM2', 'Medical Tax Credit Used on LMPSM3', 'Medical Tax Credit Used on LMPSM4', 'Medical Tax Credit Used on LMPSM5', 'Medical Tax Credit Used on LMPSM6', 'Medical Tax Credit Used on LMPSM7', 'Medical Tax Credit Used on LMPSM8', 'Medical Tax Credit Used on LMPSM9', 'Medical Tax Credit Used on LMPSM10', 'ZA_Tax_On_Multiple_Lump_Sums', 'ZA_Tax_On_Lump_Sums' ) and pet.legislation_code = 'ZA' GROUP BY pec.classification_name ,pac.locking_action_id ,pectl.classification_name ,nvl (pettl.reporting_name ,pettl.element_name)

View Text - HTML Formatted

SELECT PET.ELEMENT_TYPE_ID ROW_ID
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORT_NAME
, PECTL.CLASSIFICATION_NAME
, SUM (FND_NUMBER.CANONICAL_TO_NUMBER (PRV.RESULT_VALUE)) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, PRR.ASSIGNMENT_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
FROM PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS ASACT
, PAY_PAYROLL_ACTIONS PACT
WHERE PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV ('LANG')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV ('LANG')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PACT.PAYROLL_ACTION_ID = ASACT.PAYROLL_ACTION_ID
AND ASACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIV.NAME = 'PAY VALUE'
AND PIV.UOM = 'M'
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND NOT EXISTS (SELECT PET1.ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F PET1
WHERE PET1.ELEMENT_NAME IN ('MEDICAL TAX CREDIT USED ON LMPSM1'
, 'MEDICAL TAX CREDIT USED ON LMPSM2'
, 'MEDICAL TAX CREDIT USED ON LMPSM3'
, 'MEDICAL TAX CREDIT USED ON LMPSM4'
, 'MEDICAL TAX CREDIT USED ON LMPSM5'
, 'MEDICAL TAX CREDIT USED ON LMPSM6'
, 'MEDICAL TAX CREDIT USED ON LMPSM7'
, 'MEDICAL TAX CREDIT USED ON LMPSM8'
, 'MEDICAL TAX CREDIT USED ON LMPSM9'
, 'MEDICAL TAX CREDIT USED ON LMPSM10'
, 'ZA_TAX_ON_MULTIPLE_LUMP_SUMS'
, 'ZA_TAX_ON_LUMP_SUMS' )
AND PET1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET1.LEGISLATION_CODE = 'ZA') GROUP BY PEC.CLASSIFICATION_NAME
, PRR.ASSIGNMENT_ACTION_ID
, PET.PROCESSING_PRIORITY
, PECTL.CLASSIFICATION_NAME
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)
, PET.ELEMENT_TYPE_ID UNION ALL SELECT PET.ELEMENT_TYPE_ID ROW_ID
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORT_NAME
, PECTL.CLASSIFICATION_NAME
, SUM (FND_NUMBER.CANONICAL_TO_NUMBER (PRV.RESULT_VALUE)) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, PAC.LOCKING_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
FROM PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS ASACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_ACTION_INTERLOCKS PAC
WHERE PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV ('LANG')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV ('LANG')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PACT.PAYROLL_ACTION_ID = ASACT.PAYROLL_ACTION_ID
AND ASACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIV.NAME = 'PAY VALUE'
AND PIV.UOM = 'M'
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PAC.LOCKED_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND EXISTS ( SELECT NULL
FROM PAY_PAYROLL_ACTIONS PPAL
, PAY_ASSIGNMENT_ACTIONS PAAL
WHERE PAAL.ASSIGNMENT_ACTION_ID = PAC.LOCKING_ACTION_ID
AND PPAL.PAYROLL_ACTION_ID = PAAL.PAYROLL_ACTION_ID
AND PPAL.ACTION_TYPE IN ('P'
, 'U') )
AND NOT EXISTS (SELECT PET1.ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F PET1
WHERE PET1.ELEMENT_NAME IN ('MEDICAL TAX CREDIT USED ON LMPSM1'
, 'MEDICAL TAX CREDIT USED ON LMPSM2'
, 'MEDICAL TAX CREDIT USED ON LMPSM3'
, 'MEDICAL TAX CREDIT USED ON LMPSM4'
, 'MEDICAL TAX CREDIT USED ON LMPSM5'
, 'MEDICAL TAX CREDIT USED ON LMPSM6'
, 'MEDICAL TAX CREDIT USED ON LMPSM7'
, 'MEDICAL TAX CREDIT USED ON LMPSM8'
, 'MEDICAL TAX CREDIT USED ON LMPSM9'
, 'MEDICAL TAX CREDIT USED ON LMPSM10'
, 'ZA_TAX_ON_MULTIPLE_LUMP_SUMS'
, 'ZA_TAX_ON_LUMP_SUMS' )
AND PET1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET1.LEGISLATION_CODE = 'ZA') GROUP BY PEC.CLASSIFICATION_NAME
, PAC.LOCKING_ACTION_ID
, PET.PROCESSING_PRIORITY
, PECTL.CLASSIFICATION_NAME
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)
, PET.ELEMENT_TYPE_ID UNION ALL SELECT 1 ROW_ID
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORT_NAME
, PECTL.CLASSIFICATION_NAME
, SUM (FND_NUMBER.CANONICAL_TO_NUMBER (PRV.RESULT_VALUE)) RESULT_VALUE
, 8900 PROCESSING_PRIORITY
, PRR.ASSIGNMENT_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
FROM PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS ASACT
, PAY_PAYROLL_ACTIONS PACT
WHERE PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV ('LANG')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV ('LANG')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PACT.PAYROLL_ACTION_ID = ASACT.PAYROLL_ACTION_ID
AND ASACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIV.NAME = 'PAY VALUE'
AND PIV.UOM = 'M'
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PET.ELEMENT_NAME IN ('MEDICAL TAX CREDIT USED ON LMPSM1'
, 'MEDICAL TAX CREDIT USED ON LMPSM2'
, 'MEDICAL TAX CREDIT USED ON LMPSM3'
, 'MEDICAL TAX CREDIT USED ON LMPSM4'
, 'MEDICAL TAX CREDIT USED ON LMPSM5'
, 'MEDICAL TAX CREDIT USED ON LMPSM6'
, 'MEDICAL TAX CREDIT USED ON LMPSM7'
, 'MEDICAL TAX CREDIT USED ON LMPSM8'
, 'MEDICAL TAX CREDIT USED ON LMPSM9'
, 'MEDICAL TAX CREDIT USED ON LMPSM10'
, 'ZA_TAX_ON_MULTIPLE_LUMP_SUMS'
, 'ZA_TAX_ON_LUMP_SUMS' )
AND PET.LEGISLATION_CODE = 'ZA' GROUP BY PEC.CLASSIFICATION_NAME
, PRR.ASSIGNMENT_ACTION_ID
, PECTL.CLASSIFICATION_NAME
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) UNION ALL SELECT 1 ROW_ID
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORT_NAME
, PECTL.CLASSIFICATION_NAME
, SUM (FND_NUMBER.CANONICAL_TO_NUMBER (PRV.RESULT_VALUE)) RESULT_VALUE
, 8900
, PAC.LOCKING_ACTION_ID
, PEC.CLASSIFICATION_NAME BASE_CLASSIFICATION_NAME
FROM PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS ASACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_ACTION_INTERLOCKS PAC
WHERE PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV ('LANG')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV ('LANG')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PACT.PAYROLL_ACTION_ID = ASACT.PAYROLL_ACTION_ID
AND ASACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIV.NAME = 'PAY VALUE'
AND PIV.UOM = 'M'
AND PACT.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PAC.LOCKED_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND EXISTS ( SELECT NULL
FROM PAY_PAYROLL_ACTIONS PPAL
, PAY_ASSIGNMENT_ACTIONS PAAL
WHERE PAAL.ASSIGNMENT_ACTION_ID = PAC.LOCKING_ACTION_ID
AND PPAL.PAYROLL_ACTION_ID = PAAL.PAYROLL_ACTION_ID
AND PPAL.ACTION_TYPE IN ('P'
, 'U') )
AND PET.ELEMENT_NAME IN ('MEDICAL TAX CREDIT USED ON LMPSM1'
, 'MEDICAL TAX CREDIT USED ON LMPSM2'
, 'MEDICAL TAX CREDIT USED ON LMPSM3'
, 'MEDICAL TAX CREDIT USED ON LMPSM4'
, 'MEDICAL TAX CREDIT USED ON LMPSM5'
, 'MEDICAL TAX CREDIT USED ON LMPSM6'
, 'MEDICAL TAX CREDIT USED ON LMPSM7'
, 'MEDICAL TAX CREDIT USED ON LMPSM8'
, 'MEDICAL TAX CREDIT USED ON LMPSM9'
, 'MEDICAL TAX CREDIT USED ON LMPSM10'
, 'ZA_TAX_ON_MULTIPLE_LUMP_SUMS'
, 'ZA_TAX_ON_LUMP_SUMS' )
AND PET.LEGISLATION_CODE = 'ZA' GROUP BY PEC.CLASSIFICATION_NAME
, PAC.LOCKING_ACTION_ID
, PECTL.CLASSIFICATION_NAME
, NVL (PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME)