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 |
