PAY RTI - Full Payment Summary Output

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: RTI - Full Payment Summary Output
Short Name: PYGBRTIFPSOP
DB package:
		 				select
						paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,
						paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,		
						to_char(-999) PRE_PAYMENT_DETAILS,
						:NEGATIVE_PAYMENT 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_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_INFORMATION27 PAYE_AGG_INDICATOR,
						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_INFORMATION11 B_NIC, 		
						pai2.ACTION_INFORMATION12 B_NNIC, 		
						pai2.ACTION_INFORMATION13 EMP_PAY, 	
						pai2.ACTION_INFORMATION14 I_NIC, 	
						pai2.ACTION_INFORMATION15 EMP_PEN_NOT, 	
						pai2.ACTION_INFORMATION16 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
						from 
						pay_assignment_actions paa,
						pay_action_information pai1,
						pay_action_information pai2,
						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 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,
                        to_char(max(aggr_ptd.pre_pay_id)) PRE_PAYMENT_DETAILS,
                        max(:NEGATIVE_PAYMENT) 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_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_INFORMATION27) PAYE_AGG_INDICATOR,
						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.B_NIC)) B_NIC, 
						to_char(max(aggr_ptd.B_NNIC)) B_NNIC, 
						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, 
						to_char(max(aggr_ptd.STUD_LOAN_R_REC)) 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
						from 
						pay_assignment_actions paa,
						pay_action_information pai1,
						pay_action_information pai2,
						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_INFORMATION11) B_NIC, 
                                sum(pai2.ACTION_INFORMATION12) B_NNIC, 
                                sum(pai2.ACTION_INFORMATION13) EMP_PAY, 
                                sum(pai2.ACTION_INFORMATION14) I_NIC, 
                                sum(pai2.ACTION_INFORMATION15) EMP_PEN_NOT, 
                                sum(pai2.ACTION_INFORMATION16) 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 pai1,
                                pay_action_information pai2,
                                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 pai2.ACTION_INFORMATION29 =  pai1.ACTION_INFORMATION29 
								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)
                                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 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(pai1.ACTION_INFORMATION29,1,instr(pai1.ACTION_INFORMATION29,',')-1)  = substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1)
                        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
Report Cateogry
 
Negative Payment
 
Payroll Action ID