PAY RTI - Full Payment Submission Output 2013/14

➜ Back to Library
Description
Categories: BI Publisher
Application: Payroll
Source: RTI - Full Payment Submission Output 2013/14
Short Name: PYGBRTIFPSOP13
DB package:
Run PAY RTI - Full Payment Submission Output 2013/14 and other Oracle EBS reports with Blitz Report™ on our demo environment
		 				select
						paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,		
						paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,
                        null pre_pay_action_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_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 OSPP, 	
						pai2.ACTION_INFORMATION21 SAP, 	
						pai2.ACTION_INFORMATION22 ASPP, 
						pai2.ACTION_INFORMATION25 TAX_CODE,	
						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 							
						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,length(max(pai2.ACTION_INFORMATION29)))) act_id,	
						max(paa.ASSIGNMENT_ACTION_ID) ASG_ACTION_ID,
                        substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) pre_pay_action_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,
						max(pai2.ACTION_INFORMATION7) 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.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) OSPP, 	
						max(pai2.ACTION_INFORMATION21) SAP, 	
						max(pai2.ACTION_INFORMATION22) ASPP, 
						max(pai2.ACTION_INFORMATION25) TAX_CODE,	
						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 							
						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_INFORMATION8) DED_PAY, 
                                sum(pai2.ACTION_INFORMATION9) STA_DED, 
                                sum(pai2.ACTION_INFORMATION10) B_PAY, 
                                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
                                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) ) 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  substr(pai1.ACTION_INFORMATION29,1,instr(pai1.ACTION_INFORMATION29,',')-1) = substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1)
						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 pai1.ACTION_INFORMATION21 = 'Y' 
						and pai1.ACTION_INFORMATION27 = 'Y'
						and pai1.ACTION_INFORMATION30 = 'Y' 
						group by substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) 
						order by 1
Parameter Name SQL text Validation
Payroll Action ID 
 
 Char