ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F2FF3869E053B46B63588994Y 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 PAY PAYRPP35_XML US PAY Year End Reconciliation Application: Payroll Source: Year End Reconciliation Report (XML) Short Name: PAYRPP35_XML DB package: PAY_PAYGBP35_XMLP_PKG BI Publisher :c_business_group_name :c_date_from :c_date_from_to :c_end_date :c_permit_no :c_request_id :c_start_date :c_tax_district :p_conc_request_id :p_nip_flag :p_payroll_action_id :p_report_title :payroll_id 1 10 :p_payroll_action_id LOV Oracle PY_GB_SRS_EOY_ARCH_ACTIONS Y select payroll_action_id id, fnd_date.date_to_displaydate(effective_date)||' ('||request_id||')' value, null description from pay_payroll_actions a where a.report_type='EOY' and a.report_qualifier='GB' and a.action_status='C' and a.action_type='X' and a.business_group_id= fnd_global.per_business_group_id order by a.effective_date desc, a.creation_date desc Y US Archive End Date