ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3483869E053B46B63588994 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, --pai3.ACTION_INFORMATION1, 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, 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, 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) TAXABLE_PAY_NO_P60, 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) TAX_PAID_NO_P60, 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) STUDENT_LOAN_NO_P60, 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) SSP_NO_P60, 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) SMP_NO_P60, 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) OSPP_NO_P60, 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) SAP_NO_P60, 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) 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, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION2,0))*0.01)) TAXABLE_PAY_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION3,0))*0.01)) TAX_PAID_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION4,0))*0.01)) STUDENT_LOAN_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION5,0))*0.01)) SSP_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION6,0))*0.01)) SMP_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION7,0))*0.01)) OSPP_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION8,0))*0.01)) SAP_EYU, MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION9,0))*0.01)) ASPP_EYU 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','EYU DATA','P60 DATA') 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' 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 order by 1 Y PAY PYGBRTIRECOP US PAY RTI Year to Date Reconciliation Report Output Application: Payroll Source: RTI Year to Date Reconciliation Report Output Short Name: PYGBRTIRECOP DB package: PAY_GB_RTI_REC BI Publisher :display_total :end_person_id :payroll_action_id :start_person_id 1 10 :payroll_action_id Char Y US Payroll Action ID