select
paaf.person_id PERSON_ID,
min (paa.ASSIGNMENT_ACTION_ID) ASSIGNMENT_ACTION_ID,
addr.ACTION_INFORMATION5 ADDRESS_LINE1,
addr.ACTION_INFORMATION6 ADDRESS_LINE2,
addr.ACTION_INFORMATION7 ADDRESS_LINE3,
addr.ACTION_INFORMATION8 ADDRESS_LINE4,
addr.ACTION_INFORMATION12 POSTAL_CODE,
addr.ACTION_INFORMATION13 COUNTRY,
person.ACTION_INFORMATION3 LAST_NAME,
person.ACTION_INFORMATION4 FIRST_NAME,
person.ACTION_INFORMATION5 SECOND_NAME,
person.ACTION_INFORMATION6 TITLE,
person.ACTION_INFORMATION7 NI_NUMBER,
person.ACTION_INFORMATION8 DOB,
person.ACTION_INFORMATION9 GENDER
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
group by
paaf.person_id ,
addr.ACTION_INFORMATION5 ,
addr.ACTION_INFORMATION6 ,
addr.ACTION_INFORMATION7 ,
addr.ACTION_INFORMATION8 ,
addr.ACTION_INFORMATION12 ,
addr.ACTION_INFORMATION13 ,
person.ACTION_INFORMATION3 ,
person.ACTION_INFORMATION4 ,
person.ACTION_INFORMATION5 ,
person.ACTION_INFORMATION6 ,
person.ACTION_INFORMATION7 ,
person.ACTION_INFORMATION8 ,
person.ACTION_INFORMATION9
order by PERSON_ID |