PAY Year End Reconciliation

Description

Application: Payroll
Source: Year End Reconciliation Report (XML)
Short Name: PAYRPP35_XML
DB package: PAY_PAYGBP35_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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

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

Parameter Name

SQL text

Validation

Archive End Date

LOV Oracle

Download