ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3113869E053B46B63588994 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 PAY PYAURECD_XML US PAY Payroll Reconciliation Detail Application: Payroll Source: Payroll Reconciliation Detail (XML) Short Name: PYAURECD_XML DB package: PAY_PYAURECD_XMLP_PKG BI Publisher 1=1 :cp_asg_death_non_tax_earn :cp_asg_ytd_death_non_tax_earn :cp_deductions :cp_direct_payments :cp_emp_chg :cp_end_report :cp_gross_earnings :cp_input_value_name :cp_man_ps_assessable_etp :cp_man_ps_cdep :cp_man_ps_etp :cp_man_ps_lumpsum_payments :cp_man_ps_other_income :cp_man_ps_paysum_gross :cp_man_ps_rep_allowances :cp_man_ps_rfb :cp_man_ps_union_fees :cp_man_ps_workplace_giving :cp_manual_count :cp_net_payment :cp_no_data_found :cp_non_taxable_earnings :cp_pre_tax_deductions :cp_printed_count :cp_ps_assessable_etp :cp_ps_cdep :cp_ps_etp :cp_ps_lumpsum_payments :cp_ps_other_income :cp_ps_paysum_gross :cp_ps_rep_allowances :cp_ps_rfb :cp_ps_union_fees :cp_ps_workplace_giving :cp_tax :cp_taxable_earnings :cp_uom :cp_ytd_deductions :cp_ytd_direct_payments :cp_ytd_emp_chg :cp_ytd_gross_earnings :cp_ytd_net_payment :cp_ytd_non_taxable_earnings :cp_ytd_pre_tax_deductions :cp_ytd_tax :cp_ytd_taxable_earnings :lp_assignment_id :lp_assignment_set :lp_assignment_set_id :lp_business_group_id :lp_delete_actions :lp_employee_type :lp_end_date :lp_end_date_d :lp_lst_year_term :lp_negative_records :lp_organization_id :lp_payroll_action_id :lp_payroll_id :lp_payroll_run_id :lp_paysum_flag :lp_period_end_date :lp_period_end_date_d :lp_registered_employer :lp_sort_order_1 :lp_sort_order_2 :lp_sort_order_3 :lp_sort_order_4 :lp_start_date :lp_start_date_d :lp_ytd_totals :lp_zero_records :orderby_clause :p_assignment_id :p_assignment_set_id :p_business_group_id :p_conc_request_id :p_delete_actions :p_employee_type :p_end_date :p_lst_year_term :p_negative_records :p_organization_id :p_payroll_action_id :p_payroll_id :p_payroll_run_id :p_paysum_flag :p_period_end_date :p_registered_employer :p_sort_order_1 :p_sort_order_2 :p_sort_order_3 :p_sort_order_4 :p_start_date :p_ytd_totals :p_zero_records