PAY RTI YTD Reconciliation Report Output

Description

Imported from BI Publisher
Application: Payroll
Source: RTI YTD Reconciliation Report Output
Short Name: PYGBRTIRECDISOP20
DB package: PAY_GB_RTI_REC_DIFF

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

Copy

                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
                        END) tax_paid_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_information4, 0)) * 0.01))
                        ELSE    NULL
                        END) student_loan_p60diff,
                        MAX(DECODE(pai3.action_information1, 'P60 DIFF', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ssp_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_information6, 0)) * 0.01))
                        ELSE    NULL
                        END) smp_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_information7, 0)) * 0.01))
                        ELSE    NULL
                        END) ospp_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_information8, 0)) * 0.01))
                        ELSE    NULL
                        END) sap_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_information9, 0)) * 0.01))
                        ELSE    NULL
                        END) aspp_p60diff,
                        (CASE
                        WHEN    :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY')
                                THEN    (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_information10, 0.00)) * 0.01))
                                        ELSE    NULL
                                        END)
                        ELSE    0
                        END) postgraduate_loan_p60diff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01))
                        ELSE    NULL
                        END) taxable_pay_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01))
                        ELSE    NULL
                        END) tax_paid_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01))
                        ELSE    NULL
                        END) student_loan_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01))
                        ELSE    NULL
                        END) ssp_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01))
                        ELSE    NULL
                        END) smp_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01))
                        ELSE    NULL
                        END) ospp_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01))
                        ELSE    NULL
                        END) sap_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01))
                        ELSE    NULL
                        END) aspp_afpsdiff,
                        (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, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information10, 0.00)) * 0.01))
                                        ELSE    NULL
                                        END)
                        ELSE    0
                        END) postgraduate_loan_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information11, 0)) * 0.01))
                        ELSE    NULL
                        END) spbp_afpsdiff,
                        (CASE
                        WHEN    :RUN_MODE != 'Compare Payroll vs P60'
                                THEN    MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information12, 0)) * 0.01))
                        ELSE    NULL
                        END) rtc1anic_afpsdiff
                FROM    pay_assignment_actions paa,
                        pay_action_information pai3,
                        pay_action_information person
                WHERE   paa.payroll_action_id = :PAYROLL_ACTION_ID
                AND     pai3.action_context_id = paa.assignment_action_id
                AND     pai3.action_context_type = 'AAP'
                AND     pai3.action_information_category = 'GB RTI REC ASG DET'
                AND     pai3.action_information1 IN ('PAY DATA', 'FPS DATA', 'AFPS DATA', 'P60 DATA', 'FPS DIFF', 'P60 DIFF', 'AFPS DIFF')
                AND     person.action_context_id = paa.assignment_action_id
                AND     person.action_context_type = 'AAP'
                AND     person.action_information_category = 'GB RTI REC EMP DET'
                AND     TO_NUMBER(person.action_information10) BETWEEN :START_PERSON_ID AND :END_PERSON_ID
                AND     :DISPLAY_TOTAL <> 'YES'
                AND     NOT EXISTS(SELECT  *
                                   FROM    pay_message_lines
                                   WHERE   source_id = paa.assignment_action_id)
                GROUP BY paa.assignment_action_id,
                         person.action_information6,
                         person.action_information5,
                         person.action_information2,
                         person.action_information1,
                         person.action_information7,
                         person.action_information8,
                         person.action_information3,
                         person.action_information4,
                         person.action_information9
                ORDER BY full_name,
                         asg_number

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 END) tax_paid_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_information4, 0)) * 0.01)) ELSE NULL END) student_loan_p60diff, MAX(DECODE(pai3.action_information1, 'P60 DIFF', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ssp_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_information6, 0)) * 0.01)) ELSE NULL END) smp_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_information7, 0)) * 0.01)) ELSE NULL END) ospp_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_information8, 0)) * 0.01)) ELSE NULL END) sap_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_information9, 0)) * 0.01)) ELSE NULL END) aspp_p60diff, (CASE WHEN :EFFECTIVE_DATE > TO_DATE('05-APR-2019', 'DD-MON-YYYY') THEN (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_information10, 0.00)) * 0.01)) ELSE NULL END) ELSE 0 END) postgraduate_loan_p60diff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information2, 0)) * 0.01)) ELSE NULL END) taxable_pay_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information3, 0)) * 0.01)) ELSE NULL END) tax_paid_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information4, 0)) * 0.01)) ELSE NULL END) student_loan_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information5, 0)) * 0.01)) ELSE NULL END) ssp_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information6, 0)) * 0.01)) ELSE NULL END) smp_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information7, 0)) * 0.01)) ELSE NULL END) ospp_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information8, 0)) * 0.01)) ELSE NULL END) sap_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information9, 0)) * 0.01)) ELSE NULL END) aspp_afpsdiff, (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, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information10, 0.00)) * 0.01)) ELSE NULL END) ELSE 0 END) postgraduate_loan_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information11, 0)) * 0.01)) ELSE NULL END) spbp_afpsdiff, (CASE WHEN :RUN_MODE != 'Compare Payroll vs P60' THEN MAX(DECODE(pai3.action_information1, 'AFPS DIFF', TO_NUMBER(NVL(pai3.action_information12, 0)) * 0.01)) ELSE NULL END) rtc1anic_afpsdiff FROM pay_assignment_actions paa, pay_action_information pai3, pay_action_information person WHERE paa.payroll_action_id = :PAYROLL_ACTION_ID AND pai3.action_context_id = paa.assignment_action_id AND pai3.action_context_type = 'AAP' AND pai3.action_information_category = 'GB RTI REC ASG DET' AND pai3.action_information1 IN ('PAY DATA', 'FPS DATA', 'AFPS DATA', 'P60 DATA', 'FPS DIFF', 'P60 DIFF', 'AFPS DIFF') AND person.action_context_id = paa.assignment_action_id AND person.action_context_type = 'AAP' AND person.action_information_category = 'GB RTI REC EMP DET' AND TO_NUMBER(person.action_information10) BETWEEN :START_PERSON_ID AND :END_PERSON_ID AND :DISPLAY_TOTAL <> 'YES' AND NOT EXISTS(SELECT * FROM pay_message_lines WHERE source_id = paa.assignment_action_id) GROUP BY paa.assignment_action_id, person.action_information6, person.action_information5, person.action_information2, person.action_information1, person.action_information7, person.action_information8, person.action_information3, person.action_information4, person.action_information9 ORDER BY full_name, asg_number

Parameter Name

SQL text

Validation

Payroll Action ID

Char

Start Person ID

Char

End Person ID

Char

Display Total

Char

Download

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

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

Find out more

Features

Download

FAQ