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 |
