PQP Teachers Pension MCR Reconciliation Report (PDF)

			SELECT  distinct 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_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
					,to_char(to_date(gm_emp_d.Action_information7,'DDMMYYYY'),'DD-MON-YYYY') DATE_OF_BIRTH       --Date of Birth
					,gm_emp_d.Action_information8 EMAIL      --Email Address
					,gm_asg_d.Action_information1 REC_ACT
					,gm_asg_d.Action_information2 ERR_NUM
					,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
					,to_char(to_date(gm_asg_d.Action_information7,'DDMMYYYY'),'DD-MON-YYYY') S_DATE
					,to_char(to_date(gm_asg_d.Action_information8,'DDMMYYYY'),'DD-MON-YYYY') 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_asg_d.Action_information12 ST_IND 
					,NVL(gm_contr.Action_information2,0) ANN_FULL_TIME_SAL_PTD
					,NVL(gm_contr.Action_information3,0) PART_TIME_EAR_PTD
					,NVL(gm_contr.Action_information4,0) OVERTIME_PTD
					,NVL(gm_contr.Action_information5,0) ADD_PEN_PAY_PTD
					,NVL(gm_contr.Action_information6,0) ACT_PEN_PAY_PTD
					,NVL(gm_contr.Action_information7,0) PEN_OVER_PAID_PTD
					,NVL(gm_contr.Action_information8,0) NOT_PENS_PAY_PTD
					,NVL(gm_contr.Action_information9,0) EMP_CONTRIB_PTD
					,NVL(gm_contr.Action_information10,0) MEMB_CONTR_PTD
					,NVL(gm_contr.Action_information11,0) ADD_PEN_CONT_PTD
					,NVL(gm_contr.Action_information12,0) FASTER_ACC_PTD
					,NVL(gm_contr.Action_information13,0) AAB_PTD
					,NVL(gm_contr.Action_information14,0) ADD_CONTR_PTD
					,NVL(gm_contr.Action_information15,0) PRESTON_PTD
					,NVL(gm_contr.Action_information16,0) TIER_PER
					,NVL(gm_contr.Action_information17,0) CALC_METHOD
					,NVL(gm_contr.Action_information18,0) CONT_CALC_DATE
					,NVL(gm_prev_con.Action_information2,0) EMP_CONTR_PREV_PTD
					,NVL(gm_prev_con.Action_information3,0) MEM_CONTR_PREV_PTD
					,NVL(gm_prev_con.Action_information4,0) FASTER_ACC_PREV_PTD
					,NVL(gm_prev_con.Action_information5,0) AAB_PREV_PTD
					,NVL(gm_prev_con.Action_information6,0) ADD_PEN_CONT_PREV_PTD
					,NVL(gm_prev_con.Action_information7,0) ADD_CONT_PREV_PTD
					,NVL(gm_adj_con.Action_information2,0)  EMP_CONTR_ADJ
					,NVL(gm_adj_con.Action_information3,0)  MEMB_CONTR_ADJ
					,NVL(gm_adj_con.Action_information4,0)  ADD_PENS_CONTR_ADJ
					,NVL(gm_adj_con.Action_information5,0)  FASTER_ACCR_ADJ
					,NVL(gm_adj_con.Action_information6,0)  AAB_ADJ
					,NVL(gm_adj_con.Action_information7,0)  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
				ppa.payroll_action_id = :P_PAYROLL_ACTION_ID
				AND paa.payroll_action_id = ppa.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'