PAY RTI - Earlier Year Update Output 2012/13

			            select     
                        					paaf.person_id PERSON_ID,
						min (paa.ASSIGNMENT_ACTION_ID) ASSIGNMENT_ACTION_ID,
						max(addr.ACTION_INFORMATION5) ADD_L1, 
						max(addr.ACTION_INFORMATION6) ADD_L2, 
						max(addr.ACTION_INFORMATION7) ADD_L3, 
						max(addr.ACTION_INFORMATION8) ADD_L4,
						max(addr.ACTION_INFORMATION12) POSTAL_CODE,
						max(addr.ACTION_INFORMATION13) COUNTRY,
						max(person.ACTION_INFORMATION3) LAST_N,
						max(person.ACTION_INFORMATION4) FIR_N,
						max(person.ACTION_INFORMATION5) SEC_N,
						max(person.ACTION_INFORMATION6) TITLE,
						max(person.ACTION_INFORMATION7) NINO,
						max(person.ACTION_INFORMATION8) DOB,
						max(person.ACTION_INFORMATION9) GENDER,
						max(person.ACTION_INFORMATION16) PASSPORT,
						max(person.ACTION_INFORMATION12) P_LASTN,
						max(person.ACTION_INFORMATION13) P_FIRN,
						max(person.ACTION_INFORMATION14) P_SECN,
					    	max(person.ACTION_INFORMATION15) P_NINO
						From pay_action_information person,
						pay_action_information addr,
						pay_assignment_actions paa,
						per_all_Assignments_f paaf
						where 
						paa.payroll_action_id = :PAYROLL_ACTION_ID
						and person.ACTION_CONTEXT_ID = paa.ASSIGNMENT_ACTION_ID
						and person.ACTION_CONTEXT_TYPE = 'AAP'
						and person.ACTION_INFORMATION_CATEGORY = 'GB RTI EMPLOYEE DETAILS'
						and addr.ACTION_CONTEXT_ID = paa.ASSIGNMENT_ACTION_ID
						and addr.ACTION_CONTEXT_TYPE = 'AAP'
						and addr.ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS'
						and paaf.assignment_id = paa.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
							paaf.person_id
                        order by PERSON_ID