VIEW: APPS.PAY_NZ_SOE_RUN_ELEMENTS_V2

Source

View Text - Preformatted

SELECT 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(decode(substr(piv.uom,1,1), 'M', prv.result_value, null)) amount, pet.processing_priority , pai.locking_action_id run_assignment_action_id ,pac.assignment_id from pay_payroll_actions ppa, per_all_assignments_f paa, pay_assignment_actions pac, pay_run_results prr, pay_run_result_values prv, pay_element_types_f pet, pay_input_values_f piv, pay_element_classifications pec, pay_action_interlocks pai WHERE ppa.action_type in ('R','Q') and ppa.action_status = 'C' and ppa.payroll_action_id = pac.payroll_action_id and pac.assignment_action_id = prr.assignment_action_id and pac.assignment_action_id = pai.locked_action_id and pec.classification_id = pet.classification_id and pet.element_type_id = piv.element_type_id and (piv.name = 'Pay Value' or piv.name = 'Hours') and piv.input_value_id = prv.input_value_id and prr.run_result_id = prv.run_result_id and pec.legislation_code = 'NZ' and prr.element_type_id = pet.element_type_id 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 Specified Superannuation Contributions') > 0 or instr(pec.classification_name, 'Employer Charges') > 0 or instr(pec.classification_name, 'Information') > 0) and pac.assignment_id = paa.assignment_id and ppa.payroll_action_id = pac.payroll_action_id and ppa.effective_date between paa.effective_start_date and paa.effective_end_date and ppa.effective_date between pet.effective_start_date and pet.effective_end_date group by pac.assignment_id , pai.locking_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 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(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRV.RESULT_VALUE
, NULL)) AMOUNT
, PET.PROCESSING_PRIORITY
, PAI.LOCKING_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, PAC.ASSIGNMENT_ID
FROM PAY_PAYROLL_ACTIONS PPA
, PER_ALL_ASSIGNMENTS_F PAA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ACTION_INTERLOCKS PAI
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND (PIV.NAME = 'PAY VALUE' OR PIV.NAME = 'HOURS')
AND PIV.INPUT_VALUE_ID = PRV.INPUT_VALUE_ID
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND PEC.LEGISLATION_CODE = 'NZ'
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
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 SPECIFIED SUPERANNUATION CONTRIBUTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION') > 0)
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PAC.ASSIGNMENT_ID
, PAI.LOCKING_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