Retrive Person Type (Mutilple)

select

    papf.effective_start_date,

    papf.effective_end_date,

    papf.employee_number,

    papf.full_name,

    ppt.user_person_type as person_type

from per_all_people_f papf

left join (

    select person_id, person_type_id

    from per_person_type_usages_f

    where :p_effective_date between effective_Start_date and effective_end_date

) pptuf on pptuf.person_id=papf.person_id

left join (

    select person_type_id, user_person_type

    from per_person_types

) ppt on ppt.person_type_id=pptuf.person_type_id

where papf.person_id=10521

and :p_effective_date between effective_Start_date and effective_end_date