PAY RTI Year to Date Reconciliation Report Output

➜ Back to Library
Description
Categories: BI Publisher
Application: Payroll
Source: RTI Year to Date Reconciliation Report Output
Short Name: PYGBRTIRECOP
DB package: PAY_GB_RTI_REC
Run PAY RTI Year to Date Reconciliation Report Output and other Oracle EBS reports with Blitz Report™ on our demo environment
					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
Parameter Name SQL text Validation
Payroll Action ID 
 
 Char
Download
Blitz Report™

Blitz Report™ provides multiple benefits: