PAY RTI Year to Date Reconciliation Report output

Description
Application: Payroll
Source: RTI Year to Date Reconciliation Report output
Short Name: PYGBRTIRECDISOP
DB package: PAY_GB_RTI_REC_DIFF
PAY RTI Year to Date Reconciliation Report output and other Oracle EBS reports with Blitz Report™ on our demo environment
Copy
					SELECT
                        pai1.action_context_id,
                        pai1.ACTION_INFORMATION2,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',pai1.ACTION_INFORMATION2,null)) NI_CAT_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01)) UPTO_LEL_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01)) UPTO_ET_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01)) UPTO_UAP_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01)) UPTO_UEL_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01)) EMR_CON_D_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01)) EMP_CON_D_RUN,
	                    MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION9,0))*0.01)) NIABLE_YTD_RUN,
						MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01)) PT_UEL_RUN,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',pai1.ACTION_INFORMATION2,null)) NI_CAT_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01)) UPTO_LEL_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01)) UPTO_ET_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01)) UPTO_UAP_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01)) UPTO_UEL_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01)) EMR_CON_D_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01)) EMP_CON_D_FPS,
	                    MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION9,0))*0.01)) NIABLE_YTD_FPS,
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01)) PT_UEL_FPS,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',pai1.ACTION_INFORMATION2,null)) NI_CAT_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01)) UPTO_LEL_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01)) UPTO_ET_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01)) UPTO_UAP_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01)) UPTO_UEL_P60,
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01)) PT_UEL_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01)) EMR_CON_D_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01)) EMP_CON_D_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',pai1.ACTION_INFORMATION2)),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',pai1.ACTION_INFORMATION2,0)),NULL)
						ELSE NULL
						END) NI_CAT_NO_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION3))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01)),NULL)
						ELSE NULL
						END) UPTO_LEL_NO_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION4))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01)),NULL)
						ELSE NULL
						END) UPTO_ET_NO_P60,
					    (CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION5))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01)),NULL)
						ELSE NULL
						END) UPTO_UAP_NO_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION6))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01)),NULL)
						ELSE NULL
						END) UPTO_UEL_NO_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION10))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01)),NULL)
						ELSE NULL
						END) PT_UEL_NO_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION7))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01)),NULL)
						ELSE NULL
						END) EMR_CON_D_NO_P60,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						decode(MAX(decode(pai1.ACTION_INFORMATION1,'P60 DATA',to_number(pai1.ACTION_INFORMATION8))),NULL,MAX(decode(pai1.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01)),NULL)
						ELSE NULL
						END) EMP_CON_D_NO_P60,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',pai1.ACTION_INFORMATION2,null)) NI_CAT_EYU,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01)) UPTO_LEL_EYU,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01)) UPTO_ET_EYU,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01)) UPTO_UAP_EYU,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01)) UPTO_UEL_EYU,
						MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01)) PT_UEL_EYU,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01)) EMR_CON_D_EYU,
                        MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01)) EMP_CON_D_EYU,
	                    MAX(decode(pai1.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai1.ACTION_INFORMATION9,0))*0.01)) NIABLE_YTD_EYU,
                        (CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',pai1.ACTION_INFORMATION2,null))
						ELSE NULL
						END) NI_CAT_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01))
						ELSE NULL
						END) UPTO_LEL_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01))
						ELSE NULL
						END) UPTO_ET_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01)) 
						ELSE NULL
						END) UPTO_UAP_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01))
						ELSE NULL
						END) UPTO_UEL_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01))
						ELSE NULL
						END) PT_UEL_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01))
						ELSE NULL
						END) EMR_CON_D_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01))
						ELSE NULL
						END) EMP_CON_D_FPSDIFF,
						(CASE WHEN :RUN_MODE !='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'FPS DIFF',to_number(nvl(pai1.ACTION_INFORMATION9,0))*0.01))
						ELSE NULL
						END) NIABLE_YTD_FPSDIFF,
                        (CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',pai1.ACTION_INFORMATION2,null))
						ELSE NULL
						END) NI_CAT_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION3,0))*0.01))
						ELSE NULL
						END) UPTO_LEL_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION4,0))*0.01))
						ELSE NULL
						END) UPTO_ET_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION5,0))*0.01))
						ELSE NULL
						END) UPTO_UAP_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION6,0))*0.01))
						ELSE NULL
						END) UPTO_UEL_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION10,0))*0.01))
						ELSE NULL
						END) PT_UEL_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION7,0))*0.01))
						ELSE NULL
						END) EMR_CON_D_P60DIFF,
						(CASE WHEN :RUN_MODE ='Compare All' or :RUN_MODE ='Compare Payroll vs P60' THEN
						MAX(decode(pai1.ACTION_INFORMATION1,'P60 DIFF',to_number(nvl(pai1.ACTION_INFORMATION8,0))*0.01))
						ELSE NULL
						END) EMP_CON_D_P60DIFF
                    FROM pay_action_information pai1
                    WHERE pai1.ACTION_INFORMATION_CATEGORY = 'GB RTI REC NI DET'
						and pai1.action_context_id = :ASG_ACTION_ID
						and pai1.ACTION_CONTEXT_TYPE = 'AAP'
                        and pai1.ACTION_INFORMATION1 in ('PAY DATA','FPS DATA','EYU DATA','P60 DATA','FPS DIFF','P60 DIFF')
                        and :DISPLAY_TOTAL <> 'YES'								
                        group by pai1.action_context_id,pai1.ACTION_INFORMATION2
                        order by 1