SELECT a.assignment_action_id assig_act_id
,a.payroll_id child_payroll
,a.assignment_id assig_id
,a.effective_end_date eff_date
,SUBSTR(a.assignment_number,1,14) assign_no
,a.national_insurance_number ni_no
,SUBSTR(a.last_name||','||SUBSTR(NVL(a.first_name,a.middle_name),1,1),1,14) name
,a.ssp sum_ssp
,NVL(TO_CHAR(a.ssp/100,'FM99999990.00'),'0.00') ssp
,a.smp sum_smp
,NVL(TO_CHAR(a.smp/100,'FM99999990.00'),'0.00') smp
,a.sap sum_sap
,NVL(TO_CHAR(a.sap/100,'FM99999990.00'),'0.00') sap
,a.spp_adopt sum_spp_adopt
,NVL(TO_CHAR(a.spp_adopt/100, 'FM99999990.00'), '0.00') spp_adopt
,a.spp_birth sum_spp_birth
,NVL(TO_CHAR(a.spp_birth/100, 'FM99999990.00'), '0.00') spp_birth
,a.tax_code tax_code
,a.w1_m1_indicator basis
,NVL(TO_CHAR(a.taxable_pay/100,'FM999999990.00'),'0.00') gross
,a.taxable_pay sum_gross
,NVL(TO_CHAR(ABS(a.tax_paid)/100,'FM999999990.00'),'0.00') tax
,decode(a.tax_refund, 'R', (-1*a.tax_paid), a.tax_paid) sum_tax
,a.tax_refund tax_refund
,NVL(TO_CHAR(a.previous_taxable_pay/100,'FM999999990.00'),'0.00') prev_gross
,a.previous_taxable_pay sum_prev_gross
,NVL(TO_CHAR(a.previous_tax_paid/100,'FM999999990.00'),'0.00') prev_tax
,a.previous_tax_paid sum_prev_tax
,NVL(TO_CHAR(a.student_loans/100,'FM999999'),'0') student_loan
,TRUNC(a.student_loans/100) sum_student_loan
,NVL(TO_CHAR(TRUNC(a.superannuation_paid/100),'FM999999'),'0') super
,TRUNC(decode(a.superannuation_refund, 'R', TRUNC(((-1*a.superannuation_paid)/100)), TRUNC(a.superannuation_paid/100))) sum_super
,a.superannuation_refund super_refund
,a.termination_date term,
PAY_PAYGBP35_XMLP_PKG.c_ni_arrearsformula(a.assignment_action_id) C_NI_ARREARS
FROM pay_gb_year_end_assignments_v a
WHERE a.payroll_action_id = :p_payroll_action_id
AND ( nvl(a.aggregated_paye_flag, 'N') = 'N'
OR (nvl(a.aggregated_paye_flag, 'N') = 'Y' AND nvl(a.eoy_primary_flag, 'Y') = 'Y')
)
AND (a.taxable_pay <> 0
OR a.tax_paid <> 0
OR a.previous_taxable_pay <> 0
OR a.previous_tax_paid <> 0
OR a.superannuation_paid <> 0
OR a.ssp <> 0
OR a.smp <> 0
OR a.sap <> 0
OR a.spp_adopt <> 0
OR a.spp_birth <> 0
OR a.student_loans > 0
OR nvl(pay_gb_eoy_archive.get_arch_str(a.assignment_action_id,'X_REPORTABLE_NI'), 'N') = 'Y'
OR nvl(pay_gb_eoy_archive.get_arch_num(a.assignment_action_id, 'X_NI_ARREARS'), 0) <> 0
)
and a.payroll_id=:payroll_id
ORDER BY a.last_name,a.first_name |
