VIEW: APPS.PAY_NZ_ASG_ELEMENT_PAYMENTS_V

Source

View Text - Preformatted

SELECT pet.rowid row_id , nvl(pet.reporting_name, pet.element_name) report_name , decode(instr(pec.classification_name, 'Earnings'), 0,null, 'Taxable Earnings') || decode(instr(pec.classification_name, 'Reimbursements'), 0,null, 'Non Taxable Allowances') || decode(instr(pec.classification_name, 'Deductions'), 0,null, 'Deductions') || decode(instr(pec.classification_name,'Information'), 0,null, decode(pet.element_name,'Employer Super Contributions','Employer Superannuation Contributions',null)) || decode(instr(pec.classification_name, 'Employer Charges'), 0,null, decode(pet.element_name,'SSCWT Deduction','Employer Superannuation Contributions',null)) classification_name , sum(prv.result_value) result_value , pet.processing_priority , paap.assignment_action_id from pay_element_types_f pet , per_assignments_f paa , pay_action_interlocks pai , pay_element_classifications pec , pay_input_values_f piv , pay_run_result_values prv , pay_run_results prr , pay_assignment_actions paap , pay_payroll_actions pap WHERE prr.element_type_id = pet.element_type_id and prr.status in ('P','PA') and pap.action_type in('P','U') and pai.locking_action_id = paap.assignment_action_id and pai.locked_action_id = prr.assignment_action_id and (pec.legislation_code is null or pec.legislation_code = 'NZ') and pet.classification_id = pec.classification_id and (instr(pec.classification_name, 'Earnings') > 0 or instr(pec.classification_name, 'Reimbursements') > 0 or instr(pec.classification_name, 'Deductions') > 0 or instr(pec.classification_name, 'Employer Charges')>0 or instr(pec.classification_name, 'Information')> 0) 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 paap.assignment_id = paa.assignment_id and pap.payroll_action_id = paap.payroll_action_id and pap.effective_date between paa.effective_start_date and paa.effective_end_date and pap.effective_date between pet.effective_start_date and pet.effective_end_date group by paap.assignment_action_id , pet.processing_priority , decode(instr(pec.classification_name, 'Earnings'), 0,null, 'Taxable Earnings') || decode(instr(pec.classification_name, 'Reimbursements'), 0,null, 'Non Taxable Allowances') || decode(instr(pec.classification_name, 'Deductions'), 0,null, 'Deductions') || decode(instr(pec.classification_name,'Information'), 0,null, decode(pet.element_name,'Employer Super Contributions','Employer Superannuation Contributions',null)) || decode(instr(pec.classification_name, 'Employer Charges'), 0,null, decode(pet.element_name,'SSCWT Deduction','Employer Superannuation Contributions',null)) , nvl(pet.reporting_name, pet.element_name) , pet.rowid

View Text - HTML Formatted

SELECT PET.ROWID ROW_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) REPORT_NAME
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'TAXABLE EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'REIMBURSEMENTS')
, 0
, NULL
, 'NON TAXABLE ALLOWANCES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'EMPLOYER SUPER CONTRIBUTIONS'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL)) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'SSCWT DEDUCTION'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL)) CLASSIFICATION_NAME
, SUM(PRV.RESULT_VALUE) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, PAAP.ASSIGNMENT_ACTION_ID
FROM PAY_ELEMENT_TYPES_F PET
, PER_ASSIGNMENTS_F PAA
, PAY_ACTION_INTERLOCKS PAI
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS PAAP
, PAY_PAYROLL_ACTIONS PAP
WHERE PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PAP.ACTION_TYPE IN('P'
, 'U')
AND PAI.LOCKING_ACTION_ID = PAAP.ASSIGNMENT_ACTION_ID
AND PAI.LOCKED_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'NZ')
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND (INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'REIMBURSEMENTS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES')>0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION')> 0)
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 PAAP.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAP.PAYROLL_ACTION_ID = PAAP.PAYROLL_ACTION_ID
AND PAP.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PAP.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PAAP.ASSIGNMENT_ACTION_ID
, PET.PROCESSING_PRIORITY
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'TAXABLE EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'REIMBURSEMENTS')
, 0
, NULL
, 'NON TAXABLE ALLOWANCES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'EMPLOYER SUPER CONTRIBUTIONS'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL)) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'SSCWT DEDUCTION'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL))
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PET.ROWID