PQP Generate MCR CSV

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Public Sector Payroll
Source: Generate MCR CSV Report
Short Name: PQPMCRCSV
DB package:
Run PQP Generate MCR CSV 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					
					,(select LOOKUP_CODE from HR_LOOKUPS where 
                      LOOKUP_TYPE = 'PQP_GB_MCR_STATUS_INDI' and 
                      meaning = 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_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
Archive Request ID 
 
 LOV Oracle
Download
Related Blitz Reports
PAY GB - DEA Payment Schedule Output PAY Summary of Tax Certificates PAY RTI - Year End P60 Report Process (Multi Threaded) PAY Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins) PAY Employee Payroll History PAY Business Payrolls Survey PAY A8B Summary (1) PAY RTI - Year End P60 Report Process PAY Inland Revenue Remittance Certificate
Blitz Report™

Blitz Report™ provides multiple benefits: