PQP Teachers Pension MCR Reconciliation Report (Excel)

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Teachers Pension MCR Reconciliation Report (PDF)
Application: Public Sector Payroll
Source: Teachers Pension MCR Reconciliation Report (Excel)
Short Name: PQPGBMCRREP_XLS
DB package:
Run PQP Teachers Pension MCR Reconciliation Report (Excel) and other Oracle EBS reports with Blitz Report™ on our demo environment
			SELECT  distinct gm_asg_d.Action_information1 REC_ACT
			        ,gm_asg_d.Action_information2 ERR_NUM
					,'="' || gm_emp_d.Action_information2 || '"' TRN  --Teachers Reference Number
					,gm_emp_d.Action_information3 NI_NUMBER  --National Insurance Number
					,gm_emp_d.Action_information4 FORE_NAME  --Forename
					,gm_emp_d.Action_information5 SURE_NAME   --Surname
					,gm_emp_d.Action_information6 GENDER     --Gender
					,'="' ||gm_emp_d.Action_information7 || '"' DATE_OF_BIRTH       --Date of Birth
					,gm_adr.Action_information2  ADD1        --addr1
					,gm_adr.Action_information3  ADD2        --addr2
					,gm_adr.Action_information4  ADD3        --addr3
					,gm_adr.Action_information5  ADD4        --addr4
					,gm_adr.Action_information6  ADD5        --addr5
					,gm_adr.Action_information7 POST_CODE   --postcode
					,gm_emp_d.Action_information8 EMAIL      --Email Address
					,'="' ||gm_asg_d.Action_information3 || '"' LOCAL_AUT_NO
					,'="' ||gm_asg_d.Action_information4 || '"' EST_NUM
					,'="' || gm_asg_d.Action_information5 || '"' UNI_ROLE_ID
					,gm_asg_d.Action_information6 ENROL_TYPE
					,'="' || gm_asg_d.Action_information7 || '"' S_DATE
					,'="' || gm_asg_d.Action_information8 || '"' E_DATE
					,gm_asg_d.Action_information9 WITH_CONF
					,gm_asg_d.Action_information10 DAYS_EXCLU 
					,gm_asg_d.Action_information11 PT_FT_IND
					,gm_contr.Action_information2 ANN_FULL_TIME_SAL_PTD
					,gm_contr.Action_information3 PART_TIME_EAR_PTD
					,gm_contr.Action_information4 OVERTIME_PTD
					,gm_contr.Action_information5 ADD_PEN_PAY_PTD					
					,gm_asg_d.Action_information12 ST_IND 
					,gm_contr.Action_information17 CALC_METHOD					
					,gm_contr.Action_information18 CONT_CALC_DATE					
					,gm_contr.Action_information6 ACT_PEN_PAY_PTD
					,gm_contr.Action_information7 PEN_OVER_PAID_PTD
					,gm_contr.Action_information8 NOT_PENS_PAY_PTD
					,gm_contr.Action_information9 EMP_CONTRIB_PTD
					,gm_contr.Action_information10 MEMB_CONTR_PTD
					,gm_contr.Action_information16 MEMB_CONT_PER_TIER 
					,gm_contr.Action_information11 ADD_PEN_CONT_PTD
					,gm_contr.Action_information12 FASTER_ACC_PTD
					,gm_contr.Action_information13 AAB_PTD
					,gm_contr.Action_information14 ADD_CONTR_PTD
					,gm_contr.Action_information15 PRESTON_PTD
					,gm_prev_con.Action_information2 EMP_CONTR_PREV_PTD
					,gm_prev_con.Action_information3 MEM_CONTR_PREV_PTD
					,gm_prev_con.Action_information6 ADD_PEN_CONT_PREV_PTD
					,gm_prev_con.Action_information4 FASTER_ACC_PREV_PTD
					,gm_prev_con.Action_information5 AAB_PREV_PTD
					,gm_prev_con.Action_information7 ADD_CONT_PREV_PTD
					,gm_adj_con.Action_information2  EMP_CONTR_ADJ
					,gm_adj_con.Action_information3  MEMB_CONTR_ADJ
					,gm_adj_con.Action_information4  ADD_PENS_CONTR_ADJ
					,gm_adj_con.Action_information5  FASTER_ACCR_ADJ
					,gm_adj_con.Action_information6  AAB_ADJ
					,gm_adj_con.Action_information7  ADD_CONTR_ADJ
			from pay_payroll_actions ppa
				,pay_assignment_actions paa 
				,pay_action_information gm_adr
				,pay_action_information gm_emp_d
				,pay_action_information gm_asg_d
				,pay_action_information gm_contr
				,pay_action_information gm_prev_con
				,pay_action_information gm_adj_con
			where
				paa.payroll_action_id = :P_PAYROLL_ACTION_ID
				AND gm_asg_d.ACTION_INFORMATION_CATEGORY='GB MCR ASG DETAILS'
				AND gm_asg_d.action_context_id = paa.assignment_action_id				
                AND gm_asg_d.ACTION_CONTEXT_TYPE = 'AAP'
                AND gm_adr.ACTION_INFORMATION_CATEGORY(+)='GB MCR ADDRESS DETAILS'
				AND gm_adr.action_context_id (+)= paa.assignment_action_id
                AND gm_adr.ACTION_CONTEXT_TYPE (+)= 'AAP'
				AND gm_emp_d.ACTION_INFORMATION_CATEGORY(+)='GB MCR EMPLOYEE DETAILS'
				AND gm_emp_d.action_context_id(+) = gm_asg_d.action_context_id
                AND gm_emp_d.ACTION_CONTEXT_TYPE(+)= 'AAP'
				AND gm_emp_d.ASSIGNMENT_ID(+) =gm_asg_d.ASSIGNMENT_ID
				AND gm_contr.ACTION_INFORMATION_CATEGORY='GB MCR CONTR DETAILS'
				AND gm_contr.action_context_id = gm_asg_d.action_context_id
                AND gm_contr.ACTION_INFORMATION19 = gm_asg_d.ACTION_INFORMATION7
                AND gm_contr.ACTION_CONTEXT_TYPE = 'AAP'
				AND gm_prev_con.ACTION_INFORMATION_CATEGORY(+)='GB MCR PREV CONTRIBUTIONS'
				AND gm_prev_con.action_context_id (+)= gm_asg_d.action_context_id
                AND gm_prev_con.ACTION_INFORMATION8 (+)= gm_asg_d.ACTION_INFORMATION7
                AND gm_prev_con.ACTION_CONTEXT_TYPE (+)= 'AAP'
				AND gm_adj_con.ACTION_INFORMATION_CATEGORY(+)='GB MCR ADJ CONTRIBUTIONS'
				AND gm_adj_con.action_context_id (+)= gm_asg_d.action_context_id
                AND gm_adj_con.ACTION_INFORMATION8 (+)= gm_asg_d.ACTION_INFORMATION7
                AND gm_adj_con.ACTION_CONTEXT_TYPE(+) = 'AAP'
Parameter Name SQL text Validation
Archiver Request ID 
 
 LOV Oracle
Download
Related Blitz Reports
PAY Payroll Reconciliation Detail PAY P46 EDI Process v6 Output PQP Generate MCR CSV PAY P46EXP EDI Process v6 Output PAY GB Pensions Automatic Enrollment not applied PAY Payroll Tax Report by Legal Employer PAY GB BIK Audit PAY RTI - Earlier Year Update Output 2014/15 PAY RTI ? Employer Alignment Submission Output
Blitz Report™

Blitz Report™ provides multiple benefits: