ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715C51A9E05362FB09050512Y 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 PAY PYGBFPSAMEREC US PAY RTI FPS Amendments Run Reconciliation Imported from BI Publisher Application: Payroll Source: RTI FPS Amendments Run Reconciliation Report Short Name: PYGBFPSAMEREC DB package: BI Publisher :date_run :payroll_action_id 1 10 :payroll_action_id LOV Oracle PAY_GB_AFPS_RECON Y select ppa.payroll_action_id id, substr(to_char(ppa.effective_date,'DD-MON-YYYY') || ' (' || to_char(ppa.payroll_action_id) || ')',1,35) value, null description from pay_payroll_actions ppa where ppa.report_type in ('RTI_FPS_AME_19','RTI_FPS_AME_20','RTI_FPS_AME_21','RTI_FPS_AME_22','RTI_FPS_AME_23') and ppa.report_qualifier='GB' and ppa.action_status='C' and ppa.action_type='X' and ppa.business_group_id=fnd_global.per_business_group_id order by ppa.effective_date desc Y US Archive Request ID RTI FPS Amendments Request ID