PAY Payroll Reconciliation Detail

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