PAY RTI FPS Run Reconciliation
Description
Categories: BI Publisher
Application: Payroll
Source: RTI FPS Run Reconciliation Report
Short Name: PYGBFPSREC
DB package:
Source: RTI FPS Run Reconciliation Report
Short Name: PYGBFPSREC
DB package:
Run
PAY RTI FPS Run Reconciliation and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
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(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION1)),'DD-MON-YYYY')) START_DATE, max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION2)),'DD-MON-YYYY')) END_DATE, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION23)) HOURS_WORKED, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION16)) IRREG_PAY, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION20)) PERIODS_COVERED, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai2.ACTION_INFORMATION25)) TAX_CODE, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,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,TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai1.ACTION_INFORMATION3),'DD-MON-YYYY'))) 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, sum(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, sum(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, sum(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_INFORMATION16,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, 1))) STUDENT_LOAN_PTD , sum(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, 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, sum(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 , sum(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 , sum(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, sum(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, sum(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, NULL PAYMENT_NON_INDIV, NULL ON_STRIKE, NULL UNPAID_ABSENCE, 0 TRIVIAL_AMOUNT_A, 0 TRIVIAL_AMOUNT_B, 0 TRIVIAL_AMOUNT_C, 0 FLEXIBLE_TAXABLE_PAY, 0 FLEXIBLE_NON_TAXABLE_PAY, max(NVL(pai3.ACTION_INFORMATION13, 'N')) FLEXIBLY_ACCESSING_PENSION, max(NVL(pai3.ACTION_INFORMATION15, 'N')) PENSION_DEATH_BENEFIT, max(NVL(pai3.ACTION_INFORMATION18, 'N')) SERIOUS_ILL_HEALTH_INDICATOR, max(pai3.ACTION_INFORMATION19) STUD_LOAN_PLAN_TYPE from pay_assignment_actions paa, pay_action_information pai1, pay_action_information pai2, pay_action_information pai3, 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 pai1.ACTION_CONTEXT_TYPE = 'AAP' and pai1.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET1' and pai2.action_context_id = paa.assignment_Action_id and pai2.ACTION_CONTEXT_TYPE = 'AAP' and pai2.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2' and pai2.assignment_id = pai1.assignment_id 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) AND :REP_TYPE = 'RTI_FPS_REP' group by decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID) union 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(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION23)) HOURS_WORKED, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION16)) IRREG_PAY, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION20)) PERIODS_COVERED, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai2.ACTION_INFORMATION25)) TAX_CODE, max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,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, sum(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_INFORMATION16,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, 1))) STUDENT_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, 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 |