PAY RTI FPS Amendments Run Reconciliation

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payroll
Source: RTI FPS Amendments Run Reconciliation Report
Short Name: PYGBFPSAMEREC
DB package:

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

Contact us to schedule a demo or if you need help with the installation

                SELECT  DECODE(NVL(papf.per_information10, 'N'),
                               'Y', 
'P' || papf.person_id, 'A' || paaf.assignment_id) group_id,
                        MAX(DECODE(pai3.action_information3, 
NULL, 0, paa.assignment_action_id)) assignment_action_id,
                        NVL(MAX(papf.per_information10), 
'N') aggregated_paye,
                        NVL(MAX(papf.per_information9), 'N') aggregated_ni,
NVL(MAX(papf.per_information2), 'N') director_ind,
                        MAX(DECODE(pai3.action_information3, 
NULL, 0, papf.person_id)),
                        MAX(DECODE(pai3.action_information3, NULL, 0, paaf.assignment_id)),
MAX(pai3.action_information3) last_n,
                        MAX(pai3.action_information7) nino,
MAX(TO_CHAR(fnd_date.canonical_to_date(pai3.action_information8), 'DD-MON-YYYY')) dob,
MAX(DECODE(pai3.action_information3, NULL, NULL, pai1.action_information13)) payroll_id,
MAX(((DECODE(pai3.action_information3, NULL, NULL, pai1.action_information1)))) start_date,
MAX(((DECODE(pai3.action_information3, NULL, NULL, pai1.action_information2)))) end_date,
MAX(pai1.action_information23) hours_worked,
                        MAX(pai1.action_information16) irreg_pay,
MAX(pai1.action_information20) periods_covered,
                        MAX(pai2.action_information25) 
tax_code,
                        MAX(DECODE(NVL(pai2.action_information27, 0), 'Y', 'Non Cumulative', 
'Cumulative')) tax_basis,
                        MAX(DECODE(pai3.action_information3, NULL, NULL, NVL(pai1.action_information18, 
NVL(pai1.action_information19, 0)))) tax_period,
                        MAX(DECODE(pai3.action_information3, 
NULL, NULL, pai1.action_information3)) payment_date,
                        SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information6, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information6, 0)) * 0.01, 0.01))) taxable_pay_ptd,
MAX(DECODE(pai3.action_information3, NULL, 0, TO_NUMBER(NVL(pai2.action_information1, 0)) * 0.01)) taxable_pay_ytd,
SUM(TO_NUMBER(NVL(pai2.action_information17, 0)) * 0.01) tax_paid_ptd,
                        MAX(DECODE(pai3.action_information3, 
NULL, 0, TO_NUMBER(NVL(pai2.action_information2, 0)) * 0.01)) tax_paid_ytd,
                        SUM(DECODE('Y', 
'Y', TO_NUMBER(NVL(pai2.action_information10, 0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information10, 
0)) * 0.01, 0.01))) ben_taxed_ptd,
                        SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information11, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information11, 0)) * 0.01, 0.01))) ben_processed_ptd,
MAX(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information12, 0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information12, 
0)) * 0.01, 0.01))) nic_free_ben_ytd,
                        SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information8, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information8, 0)) * 0.01, 0.01))) net_pay_dedn_ptd,
SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information7, 0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information7, 
0)) * 0.01, 0.01))) non_tax_or_nic_pmt,
                        SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information14, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information14, 0)) * 0.01, 0.01))) items_subject_to_class_1nic,
SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information16, 0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information16, 
0)) * 0.01, 1))) student_loan_ptd,
                        SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information4, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information4, 0)) * 0.01, 1))) postgraduate_loan_ptd,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information3, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information3, 0)) * 0.01, 1)))) student_loan_ytd,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information5, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information5, 0)) * 0.01, 1)))) postgraduate_loan_ytd,
SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information13, 0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information13, 
0)) * 0.01, 0.01))) pretax_pen_ptd,
                        SUM(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information15, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information15, 0)) * 0.01, 0.01))) posttax_pen_ptd,
MAX(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information26, 0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information26, 
0)) * 0.01, 0.01))) pretax_pen_ytd,
                        MAX(DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information28, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information28, 0)) * 0.01, 0.01))) posttax_pen_ytd,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information18, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information18, 0)) * 0.01, 0.01)))) ssp,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information19, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information19, 0)) * 0.01, 0.01)))) smp,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information20, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information20, 0)) * 0.01, 0.01)))) ospp,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information21, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information21, 0)) * 0.01, 0.01)))) sap,
MAX(DECODE(pai3.action_information3, NULL, 0, DECODE('Y', 'Y', TO_NUMBER(NVL(pai2.action_information22, 
0)) * 0.01, GREATEST(TO_NUMBER(NVL(pai2.action_information22, 0)) * 0.01, 0.01)))) aspp_shpp,                                          
MAX(paaf.assignment_number) assignment_number,
                        MAX(papf.employee_number) employee_number,
MAX(papf.full_name) full_name,
                        MAX(DECODE(pai3.action_information3, NULL, NULL, 
pai4.action_information7)) payment_non_indiv,
                        MAX(DECODE(pai3.action_information3, 
NULL, NULL, pai4.action_information8)) on_strike,
                        MAX(DECODE(pai3.action_information3, 
NULL, NULL, pai4.action_information9)) unpaid_absence,
                        MAX(DECODE(pai3.action_information3, 
NULL, 0, TO_NUMBER(NVL(pai4.action_information2, 0)) * 0.01)) trivial_amount_a,
MAX(DECODE(pai3.action_information3, NULL, 0, TO_NUMBER(NVL(pai4.action_information4, 0)) * 0.01)) trivial_amount_b,
MAX(DECODE(pai3.action_information3, NULL, 0, TO_NUMBER(NVL(pai4.action_information6, 0)) * 0.01)) trivial_amount_c,
MAX(DECODE(pai3.action_information3, NULL, 0, TO_NUMBER(NVL(pai4.action_information16, 0)) * 0.01)) flexible_taxable_pay,
MAX(DECODE(pai3.action_information3, NULL, 0, TO_NUMBER(NVL(pai4.action_information17, 0)) * 0.01)) flexible_non_taxable_pay,
MAX(NVL(pai4.action_information13, 'N')) flexibly_accessing_pension,
                        MAX(NVL(pai4.action_information15, 
'N')) pension_death_benefit,
                        MAX(NVL(pai4.action_information18, 'N')) serious_ill_health_indicator,
MAX(pai4.action_information19) stud_loan_plan_type
                FROM    pay_assignment_actions paa,
(
                        SELECT  MAX(pai11.action_context_id) action_context_id,
MAX(TO_CHAR(fnd_date.canonical_to_date (pai11.action_information1), 'DD-MON-YYYY')) action_information1,
MAX(TO_CHAR(fnd_date.canonical_to_date (pai11.action_information2), 'DD-MON-YYYY')) action_information2,
TO_CHAR(MAX(fnd_date.canonical_to_date (pai11.action_information3)), 'DD-MON-YYYY') action_information3,
MAX(pai11.action_information13) action_information13,
                                MAX(pai11.action_information16) 
action_information16,
                                MAX(pai11.action_information18) action_information18,
MAX(pai11.action_information19) action_information19,
                                MAX(pai11.action_information20) 
action_information20,
                                MAX(pai11.action_information23) action_information23
FROM    pay_assignment_actions paa1,
                                pay_action_information pai11
WHERE   paa1.payroll_action_id = :PAYROLL_ACTION_ID
                        AND     pai11.action_context_id 
= paa1.assignment_action_id
                        AND     pai11.action_context_type = 'AAP'
AND     pai11.action_information_category = 'GB_RTI_FPS_ASG_DET1'
                        GROUP BY DECODE(pai11.action_information29, 
NULL, pai11.action_information_id, pai11.assignment_id)
                        ) pai1,
(
                        SELECT  MAX(pai22.action_context_id) action_context_id,
MAX(pai22.action_information25) action_information25,
                                MAX(pai22.action_information27) 
action_information27,
                                SUM(NVL(pai22.action_information6, 0)) action_information6,
MAX(TO_NUMBER(NVL(pai22.action_information1, 0))) action_information1,
SUM(TO_NUMBER(NVL(pai22.action_information17, 0))) action_information17,
MAX(TO_NUMBER(NVL(pai22.action_information2, 0))) action_information2,
SUM(TO_NUMBER(NVL(pai22.action_information10, 0))) action_information10,
SUM(TO_NUMBER(NVL(pai22.action_information7, 0))) action_information7,
SUM(TO_NUMBER(NVL(pai22.action_information14, 0))) action_information14,
SUM(TO_NUMBER(NVL(pai22.action_information11, 0))) action_information11,
SUM(TO_NUMBER(NVL(pai22.action_information12, 0))) action_information12,
SUM(TO_NUMBER(NVL(pai22.action_information8, 0))) action_information8,
SUM(TO_NUMBER(NVL(pai22.action_information16, 0))) action_information16,
SUM(TO_NUMBER(NVL(pai22.action_information4, 0))) action_information4,
MAX(TO_NUMBER(NVL(pai22.action_information3, 0))) action_information3,
MAX(TO_NUMBER(NVL(pai22.action_information5, 0))) action_information5,
SUM(TO_NUMBER(NVL(pai22.action_information13, 0))) action_information13,
SUM(TO_NUMBER(NVL(pai22.action_information15, 0))) action_information15,
MAX(TO_NUMBER(NVL(pai22.action_information18, 0))) action_information18,
MAX(TO_NUMBER(NVL(pai22.action_information19, 0))) action_information19,
MAX(TO_NUMBER(NVL(pai22.action_information20, 0))) action_information20,
MAX(TO_NUMBER(NVL(pai22.action_information21, 0))) action_information21,
MAX(TO_NUMBER(NVL(pai22.action_information22, 0))) action_information22,
MAX(TO_NUMBER(NVL(pai22.action_information23, 0))) action_information23,
MAX(TO_NUMBER(NVL(pai22.action_information24, 0))) action_information24,
MAX(TO_NUMBER(NVL(pai22.action_information26, 0))) action_information26,
MAX(TO_NUMBER(NVL(pai22.action_information28, 0))) action_information28
                        FROM    
pay_assignment_actions paa2,
                                pay_action_information pai22
WHERE   paa2.payroll_action_id = :PAYROLL_ACTION_ID
                        AND     pai22.action_context_id 
= paa2.assignment_action_id
                        AND     pai22.action_context_type = 'AAP'
AND     pai22.action_information_category = 'GB_RTI_FPS_ASG_DET2'
                        GROUP BY DECODE(pai22.action_information29, 
NULL, pai22.action_information_id, pai22.assignment_id)
                        ) pai2,
pay_action_information pai3,
                        pay_action_information pai4,
per_all_assignments_f paaf,
                        per_all_people_f papf
                WHERE   paa.payroll_action_id 
= :PAYROLL_ACTION_ID
                AND     pai1.action_context_id = paa.assignment_action_id
AND     pai2.action_context_id = paa.assignment_action_id
                AND     pai4.action_context_id(+) 
= paa.assignment_action_id
                AND     pai4.action_context_type(+) = 'AAP'
AND     pai4.action_information_category(+) = 'GB_RTI_FPS_ASG_DET3'
                AND     pai3.action_context_id(+) 
= paa.assignment_action_id
                AND     pai3.action_context_type(+) = 'AAP'
AND     pai3.action_information_category(+) = 'GB RTI EMPLOYEE DETAILS'
                AND     paaf.assignment_id 
= paa.assignment_id
                AND     papf.person_id = paaf.person_id
                AND     papf.effective_start_date 
=
                                                    (
SELECT  MAX(effective_start_date)
                                                    FROM    per_all_people_f
WHERE   person_id = papf.person_id
                                                    AND     effective_start_date 
<= :DATE_RUN
                                                    )
AND     paaf.effective_start_date =
                                                    (
SELECT  MAX(effective_start_date)
                                                    FROM    per_all_assignments_f
WHERE   assignment_id = paaf.assignment_id
                                                    AND     
effective_start_date <= :DATE_RUN
)
                GROUP BY DECODE(NVL(papf.per_information10, 'N'),
                                'Y', 
'P' || papf.person_id, 'A' || paaf.assignment_id)
                ORDER BY last_n,
payroll_id,
                         assignment_number
Parameter Name SQL text Validation
Archive Request ID 
 
 LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: