ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3433869E053B46B63588994 select paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID, paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID, null pre_pay_action_id, null period_service_id, 'Y' NP, :GENDER GENDER, pai1.ACTION_INFORMATION1 S_DATE, pai1.ACTION_INFORMATION2 E_DATE, pai1.ACTION_INFORMATION3 PAY_D, pai1.ACTION_INFORMATION4 STARTER_DEC, pai1.ACTION_INFORMATION5 STUD_LOAN_IND, pai1.ACTION_INFORMATION6 OVER_SIX, pai1.ACTION_INFORMATION7 LESS_SIX, pai1.ACTION_INFORMATION8 LIV_ABR, pai1.ACTION_INFORMATION9 EEA_CWC, pai1.ACTION_INFORMATION10 EPM6, pai1.ACTION_INFORMATION11 OCC_PENSION_INDICATOR, pai1.ACTION_INFORMATION12 OCC_IND, pai1.ACTION_INFORMATION13 PAYID, pai1.ACTION_INFORMATION14 PAY_CHAN_IND, pai1.ACTION_INFORMATION15 OPAYID, pai1.ACTION_INFORMATION16 IRR_IND, pai1.ACTION_INFORMATION17 PAY_FRE, pai1.ACTION_INFORMATION18 WEEK_NO, pai1.ACTION_INFORMATION19 MONTH_NO, pai1.ACTION_INFORMATION20 PER_COV, pai1.ACTION_INFORMATION21 AGG_IND, pai1.ACTION_INFORMATION22 PAY_LEAV_IND, pai1.ACTION_INFORMATION23 HRS, pai1.ACTION_INFORMATION24 NIC_CALC, pai1.ACTION_INFORMATION25 TAX_WEEK, pai1.ACTION_INFORMATION26 ANNUAL_OCC, pai1.ACTION_INFORMATION28 BACS, pai2.ACTION_INFORMATION1 TAX_PAYD, pai2.ACTION_INFORMATION2 T_TAX, trunc(pai2.ACTION_INFORMATION3/100)*100 STUD_LOAN_REC, pai2.ACTION_INFORMATION6 TAX_PAY, pai2.ACTION_INFORMATION7 PAY_NNIC, pai2.ACTION_INFORMATION8 DED_PAY, pai2.ACTION_INFORMATION9 STA_DED, pai2.ACTION_INFORMATION10 B_PAY, pai2.ACTION_INFORMATION12 B_PAY_YEAR, pai2.ACTION_INFORMATION13 EMP_PAY, pai2.ACTION_INFORMATION14 I_NIC, pai2.ACTION_INFORMATION15 EMP_PEN_NOT, trunc(pai2.ACTION_INFORMATION16/100)* 100 STUD_LOAN_R_REC, pai2.ACTION_INFORMATION17 TAX_DED, pai2.ACTION_INFORMATION18 SSP, pai2.ACTION_INFORMATION19 SMP, pai2.ACTION_INFORMATION20 SPP, pai2.ACTION_INFORMATION21 SAP, pai2.ACTION_INFORMATION22 ASPP_SHPP, pai2.ACTION_INFORMATION25 TAX_CODE, decode(upper(substr(pai2.ACTION_INFORMATION25,1,1)),'S','S','X') TAX_REGIME, substr(pai2.ACTION_INFORMATION25,2) TAX_CODE_SCOT, pai2.ACTION_INFORMATION27 W1M1_IND, (pai3.ACTION_INFORMATION1) TRIV_COMM_PAYMENT_TYPE_A, trunc(pai3.ACTION_INFORMATION2/100)*100 TRIV_COMM_PAYMENT_AMOUNT_A, (pai3.ACTION_INFORMATION3) TRIV_COMM_PAYMENT_TYPE_B, trunc(pai3.ACTION_INFORMATION4/100)*100 TRIV_COMM_PAYMENT_AMOUNT_B, (pai3.ACTION_INFORMATION5) TRIV_COMM_PAYMENT_TYPE_C, trunc(pai3.ACTION_INFORMATION6/100)*100 TRIV_COMM_PAYMENT_AMOUNT_C, (pai3.ACTION_INFORMATION7) PAYMENT_TO_NON_INDIV_IND , (pai3.ACTION_INFORMATION8) ONSTRIKE_INDICATOR , (pai3.ACTION_INFORMATION9) UNPAID_ABSENCE_INDICATOR, pai3.ACTION_INFORMATION10 B_PAY_YTD, pai2.ACTION_INFORMATION26 EMP_PAY_YTD, pai2.ACTION_INFORMATION28 EMP_PEN_NOT_YTD, pai3.ACTION_INFORMATION11 LATE_PAYE, (pai3.ACTION_INFORMATION13) FLEXIBLY_ACCESSING_PENSION, (pai3.ACTION_INFORMATION15) PENSION_DEATH_BENEFIT, (pai3.ACTION_INFORMATION16) FLEXIBLE_DRAWDOWN_TAXABLE, (pai3.ACTION_INFORMATION17) FLEXIBLE_DRAWDOWN_NON_TAXABLE from pay_assignment_actions paa, pay_action_information pai1, pay_action_information pai2, pay_action_information pai3, per_all_Assignments_f paaf 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 pai3.action_context_id (+) = paa.assignment_Action_id and pai3.ACTION_CONTEXT_TYPE (+) = 'AAP' and pai3.ACTION_INFORMATION_CATEGORY (+) = 'GB_RTI_FPS_ASG_DET3' and paaf.assignment_id = paa.assignment_id and paaf.person_id = :PERSON_ID and pai1.assignment_id = pai2.assignment_id and paaf.effective_start_date = (select max(effective_start_date) from per_all_assignments_f where assignment_id = paaf.assignment_id) and (nvl(pai1.ACTION_INFORMATION21,'N') <> 'Y' or (pai1.ACTION_INFORMATION21 = 'Y' and nvl(pai1.ACTION_INFORMATION27,'N') <> 'Y')) union all select to_number(substr(max(pai2.ACTION_INFORMATION29), instr(max(pai2.ACTION_INFORMATION29),',')+1,(instr(max(pai2.ACTION_INFORMATION29),',',1,2))-instr(max(pai2.ACTION_INFORMATION29),',')-1)) act_id, max(paa.ASSIGNMENT_ACTION_ID) ASG_ACTION_ID, substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) pre_pay_action_id, substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) period_service_id, 'Y' NP, max(:GENDER) GENDER, max(pai1.ACTION_INFORMATION1) S_DATE, max(pai1.ACTION_INFORMATION2) E_DATE, max(pai1.ACTION_INFORMATION3) PAY_D, max(pai1.ACTION_INFORMATION4) STARTER_DEC, max(pai1.ACTION_INFORMATION5) STUD_LOAN_IND, max(pai1.ACTION_INFORMATION6) OVER_SIX, max(pai1.ACTION_INFORMATION7) LESS_SIX, max(pai1.ACTION_INFORMATION8) LIV_ABR, max(pai1.ACTION_INFORMATION9) EEA_CWC, max(pai1.ACTION_INFORMATION10) EPM6, max(pai1.ACTION_INFORMATION11) OCC_PENSION_INDICATOR, max(pai1.ACTION_INFORMATION12) OCC_IND, max(pai1.ACTION_INFORMATION13) PAYID, max(pai1.ACTION_INFORMATION14) PAY_CHAN_IND, max(pai1.ACTION_INFORMATION15) OPAYID, max(pai1.ACTION_INFORMATION16) IRR_IND, max(pai1.ACTION_INFORMATION17) PAY_FRE, max(pai1.ACTION_INFORMATION18) WEEK_NO, max(pai1.ACTION_INFORMATION19) MONTH_NO, max(pai1.ACTION_INFORMATION20) PER_COV, max(pai1.ACTION_INFORMATION21) AGG_IND, max(pai1.ACTION_INFORMATION22) PAY_LEAV_IND, max(pai1.ACTION_INFORMATION23) HRS, max(pai1.ACTION_INFORMATION24) NIC_CALC, max(pai1.ACTION_INFORMATION25) TAX_WEEK, max(pai1.ACTION_INFORMATION26) ANNUAL_OCC, max(pai1.ACTION_INFORMATION28) BACS, max(pai2.ACTION_INFORMATION1) TAX_PAYD, max(pai2.ACTION_INFORMATION2) T_TAX, trunc(max(pai2.ACTION_INFORMATION3)/100)*100 STUD_LOAN_REC, to_char(max(aggr_ptd.TAX_PAY)) TAX_PAY, to_char(max(aggr_ptd.PAY_NNIC)) PAY_NNIC, to_char(max(aggr_ptd.DED_PAY)) DED_PAY, to_char(max(aggr_ptd.STA_DED)) STA_DED, to_char(max(aggr_ptd.B_PAY)) B_PAY, to_char(max(aggr_ptd.B_PAY_YEAR)) B_PAY_YEAR, to_char(max(aggr_ptd.EMP_PAY)) EMP_PAY, to_char(max(aggr_ptd.I_NIC)) I_NIC, to_char(max(aggr_ptd.EMP_PEN_NOT)) EMP_PEN_NOT, trunc(max(aggr_ptd.STUD_LOAN_R_REC)/100)*100 STUD_LOAN_R_REC, to_char(max(aggr_ptd.TAX_DED)) TAX_DED, max(pai2.ACTION_INFORMATION18) SSP, max(pai2.ACTION_INFORMATION19) SMP, max(pai2.ACTION_INFORMATION20) SPP, max(pai2.ACTION_INFORMATION21) SAP, max(pai2.ACTION_INFORMATION22) ASPP_SHPP, max(pai2.ACTION_INFORMATION25) TAX_CODE, decode(upper(substr(max(pai2.ACTION_INFORMATION25),1,1)),'S','S','X') TAX_REGIME, max(substr(pai2.ACTION_INFORMATION25,2)) TAX_CODE_SCOT, max(pai2.ACTION_INFORMATION27) W1M1_IND, max(pai3.ACTION_INFORMATION1) TRIV_COMM_PAYMENT_TYPE_A, trunc(max(pai3.ACTION_INFORMATION2)/100)*100 TRIV_COMM_PAYMENT_AMOUNT_A, max(pai3.ACTION_INFORMATION3) TRIV_COMM_PAYMENT_TYPE_B, trunc(max(pai3.ACTION_INFORMATION4)/100)*100 TRIV_COMM_PAYMENT_AMOUNT_B, max(pai3.ACTION_INFORMATION5) TRIV_COMM_PAYMENT_TYPE_C, trunc(max(pai3.ACTION_INFORMATION6)/100)*100 TRIV_COMM_PAYMENT_AMOUNT_C, max(pai3.ACTION_INFORMATION7) PAYMENT_TO_NON_INDIV_IND , max(pai3.ACTION_INFORMATION8) ONSTRIKE_INDICATOR , max(pai3.ACTION_INFORMATION9) UNPAID_ABSENCE_INDICATOR, max(pai3.ACTION_INFORMATION10) B_PAY_YTD, max(pai2.ACTION_INFORMATION26) EMP_PAY_YTD, max(pai2.ACTION_INFORMATION28) EMP_PEN_NOT_YTD, max(pai3.ACTION_INFORMATION11) LATE_PAYE, max(pai3.ACTION_INFORMATION13) FLEXIBLY_ACCESSING_PENSION, max(pai3.ACTION_INFORMATION15) PENSION_DEATH_BENEFIT, max(pai3.ACTION_INFORMATION16) FLEXIBLE_DRAWDOWN_TAXABLE, max(pai3.ACTION_INFORMATION17) FLEXIBLE_DRAWDOWN_NON_TAXABLE from pay_assignment_actions paa, pay_action_information pai1, pay_action_information pai2, pay_action_information pai3, per_all_Assignments_f paaf, (select sum(pai2.ACTION_INFORMATION6) TAX_PAY, sum(pai2.ACTION_INFORMATION7) PAY_NNIC, sum(pai2.ACTION_INFORMATION8) DED_PAY, sum(pai2.ACTION_INFORMATION9) STA_DED, sum(pai2.ACTION_INFORMATION10) B_PAY, sum(pai2.ACTION_INFORMATION12) B_PAY_YEAR, sum(pai2.ACTION_INFORMATION13) EMP_PAY, sum(pai2.ACTION_INFORMATION14) I_NIC, sum(pai2.ACTION_INFORMATION15) EMP_PEN_NOT, trunc(sum(pai2.ACTION_INFORMATION16)/100)* 100 STUD_LOAN_R_REC, sum(pai2.ACTION_INFORMATION17) TAX_DED, sum(pai2.ACTION_INFORMATION23) dummy1, sum(pai2.ACTION_INFORMATION24) dummy1, max(pai2.ACTION_INFORMATION29) pre_pay_id, substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) pay_id, substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) pos_id from pay_assignment_actions paa, pay_action_information pai2, per_all_Assignments_f paaf where paa.payroll_Action_id = :PAYROLL_ACTION_ID 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 paaf.assignment_id = paa.assignment_id and paaf.person_id = :PERSON_ID and paaf.effective_start_date = (select max(effective_start_date) from per_all_assignments_f where assignment_id = paaf.assignment_id) group by substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) ,substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) ) aggr_ptd 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 pai3.action_context_id(+) = paa.assignment_Action_id and pai3.ACTION_CONTEXT_TYPE(+) = 'AAP' and pai3.ACTION_INFORMATION_CATEGORY(+) = 'GB_RTI_FPS_ASG_DET3' and paaf.assignment_id = paa.assignment_id and paaf.person_id = :PERSON_ID and pai1.assignment_id = pai2.assignment_id and paaf.effective_start_date = (select max(effective_start_date) from per_all_assignments_f where assignment_id = paaf.assignment_id) and substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) = aggr_ptd.pay_id and substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) = aggr_ptd.pos_id and pai1.ACTION_INFORMATION21 = 'Y' and pai1.ACTION_INFORMATION27 = 'Y' and pai1.ACTION_INFORMATION30 = 'Y' and pai2.ACTION_INFORMATION27 = 'Y' and pai2.ACTION_INFORMATION30 = 'Y' group by substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1), substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) order by 1 Y PAY PYGBRTIFPSOP16 US PAY RTI - Full Payment Submission Output 2016/17 Application: Payroll Source: RTI - Full Payment Submission Output 2016/17 Short Name: PYGBRTIFPSOP16 DB package: BI Publisher :gender :payroll_action_id :person_id 1 10 :payroll_action_id Char Y US Payroll Action ID