PAY End of Year Reconciliation Summary Totals

select  distinct ppa.assignment_id,
PAY_PYAURECP_XMLP_PKG.assignment_action_id_p assignment_action_id,
:lp_registered_employer tax_unit_id,
0 postjun83_tax,
decode(pps.actual_termination_date,null,'C',decode(sign(pps.actual_termination_date - (:CP_fin_yr_end)),1,'C','T')) emp_type,
pps.actual_termination_date term_date,
PAY_PYAURECP_XMLP_PKG.DATE_EARNED_P date_earned,
	PAY_PYAURECP_XMLP_PKG.cf_calc_totalsformula(ppa.assignment_id, :lp_registered_employer, decode ( pps.actual_termination_date , null , 'C' , decode ( sign ( pps.actual_termination_date - ( :CP_fin_yr_end ) ) , 1 , 'C' , 'T' ) ), pps.actual_termination_date) CF_Calc_totals,
	PAY_PYAURECP_XMLP_PKG.cf_calc_group_totalsformula() CF_Calc_Group_Totals,
	PAY_PYAURECP_XMLP_PKG.MANUAL_PS_ISSUED_p MANUAL_PS_ISSUED,
        PAY_PYAURECP_XMLP_PKG.FOREIGN_PS_EXISTS_P FOREIGN_PS_EXISTS
from   per_assignments_f ppa,
       per_periods_of_service pps,
       pay_assignment_actions mpaa,
       pay_payroll_actions    mppa
where mppa.payroll_action_id=:LP_PAYROLL_ACTION_ID
 and  mppa.payroll_action_id = mpaa.payroll_action_id
 and  mppa.business_group_id = ppa.business_group_id
 and  ppa.assignment_id       = mpaa.assignment_id
 and  ppa.period_of_service_id = pps.period_of_service_id
 and  ppa.business_group_id    = pps.business_group_id
 and  ppa.business_group_id   = :lp_business_group_id
 and  nvl(pps.actual_termination_date, add_months(:CP_fin_yr_start,-12)) >= :CP_LST_YR_START
 AND  ppa.effective_end_date = (SELECT max(effective_end_date)
                                FROM per_assignments_f paf1
                                WHERE paf1.effective_end_date   >= :CP_fbt_yr_start
                                AND   paf1.effective_start_date <= :CP_fin_yr_end
                                AND  paf1.business_group_id = :lp_business_group_id
                                 AND paf1.person_id = ppa.person_id
                                and   paf1.assignment_id = ppa.assignment_id
                                )
  &WHERECLAUSE
--;