PAY New Zealand CEC Contributions

select A.FULL_NAME,A.ASSIGNMENT_ID,A.assignment_number EMPLOYEE_NUMBER, 
sum(decode(A.BALANCE_NAME,'CEC Complying',A.BAL_VALUE,0)) "CEC_Complying",
sum(decode(A.BALANCE_NAME,'CEC KiwiSaver',A.BAL_VALUE,0)) "CEC_Kiwisaver",
sum(decode(A.BALANCE_NAME,'ETC Complying',A.BAL_VALUE,0)) "ETC_Complying",
sum(decode(A.BALANCE_NAME,'ETC KiwiSaver',A.BAL_VALUE,0)) "ETC_Kiwisaver",
sum(decode(A.BALANCE_NAME,'Ordinary Taxable Earnings',A.BAL_VALUE,0)) "Ordinary_Tax_Ear"
from (
select ppf.full_name FULL_NAME,paf.assignment_id ASSIGNMENT_ID,paf.assignment_number,pbt.balance_name BALANCE_NAME,pay_nz_cec_report_pkg.PAY_NZ_GET_BAL_VALUE(paf.assignment_id,pdb.defined_balance_id,:p_start_date,:p_end_date) BAL_VALUE
from per_people_f ppf,
     per_assignments_f paf,
     pay_balance_types pbt,
     pay_defined_balances pdb,
     pay_balance_dimensions pbd
where (paf.assignment_id=:p_assignment_id or :p_assignment_id is null)
and paf.business_group_id=:per_business_group_id
and pbt.balance_name in ('CEC Complying','CEC KiwiSaver','ETC Complying','ETC KiwiSaver','Ordinary Taxable Earnings')
and pbd.dimension_name ='_ASG_RUN'
and paf.assignment_id in  (select paa.assignment_id
            from pay_assignment_actions paa,
                 pay_payroll_actions ppa
            where ppa.action_status ='C'
            and (paa.tax_unit_id = :p_registered_employer_id)
            and ppa.action_type in ('R','Q','I','B','V')
            and ppa.effective_date between :p_start_date and :p_end_date
            and paa.payroll_action_id=ppa.payroll_action_id
            and paf.assignment_id=paa.assignment_id )
and ppf.person_id = paf.person_id
and pbt.legislation_code='NZ'
and pbt.legislation_code=pbd.legislation_code
and pbt.balance_type_id=pdb.balance_type_id
and pbd.balance_dimension_id=pdb.balance_dimension_id
and :p_end_date between ppf.effective_start_date and ppf.effective_end_date
and  paf.effective_end_date = ( select max(effective_end_date) 
                                    from  per_assignments_f   
                                    WHERE assignment_id  =  paf.assignment_id 
                                    and effective_end_date >= :p_start_date 
                                    and effective_start_date <= :p_end_date)
) A
group by A.FULL_NAME,A.ASSIGNMENT_ID,A.assignment_number