PAY RTI YTD Reconciliation Report Output

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payroll
Source: RTI YTD Reconciliation Report Output
Short Name: PYGBRTIRECDISOP20
DB package: PAY_GB_RTI_REC_DIFF
Run PAY RTI YTD Reconciliation Report Output and other Oracle EBS reports with Blitz Report™ on our demo environment
                SELECT  paa.assignment_action_id asg_action_id,
                        person.action_information6 payroll_id,
                        person.action_information5 asg_number,
                        person.action_information2 ni_number,
                        person.action_information1 full_name,
                        TO_CHAR(fnd_date.canonical_to_date(person.action_information7), 'DD-MON-YYYY') start_date,
                        TO_CHAR(fnd_date.canonical_to_date(person.action_information8), 'DD-MON-YYYY') end_date,
                        NVL(person.action_information3, 'N') aggregated_paye,
                        NVL(person.action_information4, 'N') aggregated_ni,
                        person.action_information9 ni_rep_asg,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01)) taxable_pay_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01)) tax_paid_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01)) student_loan_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ssp_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01)) smp_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01)) ospp_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01)) sap_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01)) aspp_run,
                        (CASE
                        WHEN    :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY')
                                THEN    MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information10, 0)) * 0.01))
                        ELSE    0
                        END) postgraduate_loan_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information11, 0)) * 0.01)) spbp_run,
                        MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information12, 0)) * 0.01)) rtc1anic_run,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01)) taxable_pay_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01)) tax_paid_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01)) student_loan_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ssp_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01)) smp_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01)) ospp_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01)) sap_p60,
                        MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01)) aspp_p60,
                        (CASE
                        WHEN    :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY')
                                THEN    MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(NVL(pai3.action_information10, 0)) * 0.01))
                        ELSE    0
                        END) postgraduate_loan_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(pai3.action_information2))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) taxable_pay_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(pai3.action_information3))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) tax_paid_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(pai3.action_information4))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) student_loan_no_p60,
						(CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER(pai3.action_information10))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information10, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) postgraduate_loan_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER((pai3.action_information5)))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) ssp_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER((pai3.action_information6)))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) smp_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER((pai3.action_information7)))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) ospp_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER((pai3.action_information8)))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) sap_no_p60,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    DECODE(MAX(DECODE(pai3.action_information1, 'P60 DATA', TO_NUMBER((pai3.action_information9)))),
                                               NULL, MAX(DECODE(pai3.action_information1, 'PAY DATA', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01)), NULL)
                        ELSE    NULL
                        END) aspp_no_p60,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01)) taxable_pay_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01)) tax_paid_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01)) student_loan_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ssp_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01)) smp_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01)) ospp_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01)) sap_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01)) aspp_fps,
                        (CASE
                        WHEN    :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY')
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information10, 0)) * 0.01))
                        ELSE    0
                        END) postgraduate_loan_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information11, 0)) * 0.01)) spbp_fps,
                        MAX(DECODE(pai3.action_information1, 'FPS DATA', TO_NUMBER(NVL(pai3.action_information12, 0)) * 0.01)) rtc1anic_fps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01)) taxable_pay_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01)) tax_paid_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01)) student_loan_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ssp_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01)) smp_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01)) ospp_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01)) sap_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01)) aspp_afps,
                        (CASE
                        WHEN    :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY')
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information10, 0)) * 0.01))
                        ELSE    0
                        END) postgraduate_loan_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information11, 0)) * 0.01)) spbp_afps,
                        MAX(DECODE(pai3.action_information1, 'AFPS DATA', TO_NUMBER(NVL(pai3.action_information12, 0)) * 0.01)) rtc1anic_afps,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01))
                        ELSE    NULL
                        END) taxable_pay_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01))
                        ELSE    NULL
                        END) tax_paid_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01))
                        ELSE    NULL
                        END) student_loan_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01))
                        ELSE    NULL
                        END) ssp_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01))
                        ELSE    NULL
                        END) smp_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01))
                        ELSE    NULL
                        END) ospp_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01))
                        ELSE    NULL
                        END) sap_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01))
                        ELSE    NULL
                        END) aspp_fpsdiff,
                        (CASE
                        WHEN    :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY')
                                THEN    (CASE
                                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information10, 0.00)) * 0.01))
                                        ELSE    NULL
                                        END)
                        ELSE    0
                        END) postgraduate_loan_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information11, 0)) * 0.01))
                        ELSE    NULL
                        END) spbp_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'FPS DIFF', TO_NUMBER(NVL(pai3.action_information12, 0)) * 0.01))
                        ELSE    NULL
                        END) rtc1anic_fpsdiff,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'P60 DIFF', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01))
                        ELSE    NULL
                        END) taxable_pay_p60diff,
                        (CASE
                        WHEN    :RUN_MODE = 'Compare All'
                        OR      :RUN_MODE = 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'P60 DIFF', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01))
                        ELSE    NULL