PAY Year End Reconciliation

➜ Back to Library
Description
Categories: BI Publisher
Application: Payroll
Source: Year End Reconciliation Report (XML)
Short Name: PAYRPP35_XML
DB package: PAY_PAYGBP35_XMLP_PKG
Run PAY Year End Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Related Blitz Reports
PAY RTI - Year End P60 Report Process (Multi Threaded) PAY RTI - Year End P60 Report Process PAY Tax Payments Listing AP Accounted Invoice Aging PAY Inland Revenue Form IR21 (Singapore) ECC Projects, Projects Costing: Transactions, SQL1 PAY RTI - Full Payment Submission Output 2017/18 PAY RTI - Full Payment Submission Output 2016/17 PAY GB BIK Audit
Blitz Report™

Blitz Report™ provides multiple benefits: