PQP Teachers Pension MCR Reconciliation Report (Excel)

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:
			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