PAY Payroll Reconciliation Detail
Description
Categories: BI Publisher
Application: Payroll
Source: Payroll Reconciliation Detail (XML)
Short Name: PYAURECD_XML
DB package: PAY_PYAURECD_XMLP_PKG
Source: Payroll Reconciliation Detail (XML)
Short Name: PYAURECD_XML
DB package: PAY_PYAURECD_XMLP_PKG
Run
PAY Payroll Reconciliation Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
select classification_name Summary_Classification, element_name Summary_Element, sum(value_ptd) Element_PTD, sum(value_ytd) Element_YTD from (select distinct pap.full_name, pap.employee_number, paa.assignment_id, decode(instr(pec.classification_name,'Reimbursements'),0,pec.classification_name, 'Non Taxable Allowances') classification_name, nvl(pet.reporting_name, pet.element_name) element_name, -- sum(pay_au_rec_pkg.value_ptd((SELECT PACI.PAYROLL_ACTION_ID FROM PAY_ASSIGNMENT_ACTIONS PACI -- WHERE PACI.ASSIGNMENT_ACTION_ID=(SELECT PRRI.ASSIGNMENT_ACTION_ID FROM PAY_RUN_RESULTS PRRI -- WHERE PRRI.RUN_RESULT_ID=PRR.RUN_RESULT_ID)), paa.assignment_id, pet.element_type_id, prr.run_result_id)) Value_PTD, pay_au_rec_pkg.value_ytd(max(ppa.payroll_action_id), max(paa.assignment_id), max(pet.element_type_id),max(prr.run_result_id)) Value_YTD from per_all_people_f pap, per_all_assignments_f paa, pay_all_payrolls_f pay, pay_payroll_actions ppa, pay_assignment_actions pac, pay_run_results prr, pay_element_classifications pec, pay_element_types_f pet, pay_input_values_f piv, pay_action_information pai where 1=1 --pay.business_group_id = :LP_BUSINESS_GROUP_ID -- and piv.name = PAY_PYAURECD_XMLP_PKG.CP_input_value_name_p -- and piv.uom = PAY_PYAURECD_XMLP_PKG.CP_UOM_p and pap.person_id = paa.person_id and pay.payroll_id = paa.payroll_id --and pay.payroll_id = ppa.payroll_id and ppa.payroll_action_id = pac.payroll_action_id and paa.assignment_id = pac.assignment_id --and pac.assignment_action_id = prr.assignment_action_id and prr.assignment_action_id = pai.action_information1 -- and pec.legislation_code = PAY_PYAURECD_XMLP_PKG.cf_legislation_codeformula() and pec.classification_id = pet.classification_id and pet.element_type_id = prr.element_type_id and pet.element_type_id = piv.element_type_id and pai.action_information_category='AU_ARCHIVE_ASG_DETAILS' and pai.action_context_id=pac.assignment_action_id --and pet.element_type_id <> (select element_type_id from pay_element_types_f where element_name = 'SSCWT on KiwiSaver Employer Contribution') --and pet.element_type_id <> (select element_type_id from pay_element_types_f where element_name = 'Retro SSCWT on KiwiSaver Employer Contribution') --and ppa.effective_date between paa.effective_start_date and paa.effective_end_date and paa.effective_end_date= (select max(paaf.effective_end_date) from per_all_assignments_f paaf where paaf.assignment_id=paa.assignment_id) --and paaf.business_group_id =:LP_BUSINESS_GROUP_ID) and ppa.effective_date between pap.effective_start_date and pap.effective_end_date and ppa.effective_date between pet.effective_start_date and pet.effective_end_date and ppa.effective_date between piv.effective_start_date and piv.effective_end_date and ppa.effective_date between pay.effective_start_date and pay.effective_end_date group by pap.full_name,pap.employee_number,decode(instr(pec.classification_name,'Reimbursements'),0,pec.classification_name, 'Non Taxable Allowances'),nvl(pet.reporting_name, pet.element_name),paa.assignment_id order by 2,5,6,7) group by classification_name, element_name ORDER BY 1,2,3,4 |