ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3473869E053B46B63588994 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 Y PAY PYGBRTIRECDISOP US PAY RTI Year to Date Reconciliation Report output Application: Payroll Source: RTI Year to Date Reconciliation Report output Short Name: PYGBRTIRECDISOP DB package: PAY_GB_RTI_REC_DIFF BI Publisher :asg_action_id :display_total :end_person_id :payroll_action_id :run_mode :start_person_id 1 10 :payroll_action_id Char Y US Payroll Action ID 2 20 :start_person_id Char Y US START_PERSON_ID 3 30 :end_person_id Char Y US END_PERSON_ID 4 40 :display_total Char Y US Display Total