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 |
