Query : Query to get Person type for All Employees

SELECT PAPF.PERSON_ID,
PAPF.FIRST_NAME AS FIRST_NAME,
PAPF.LAST_NAME AS LAST_NAME,
PAPF.EMPLOYEE_NUMBER AS EMPLOYEE_NUMBER,
TO_CHAR(PAPF.EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE,
TO_CHAR(PAPF.EFFECTIVE_END_DATE) AS EFFECTIVE_END_DATE,
PAPF.BUSINESS_GROUP_ID AS BUSINESS_GROUP_ID,
ppt.user_person_type
FROM
PER_ALL_PEOPLE_F PAPF,
hr.per_person_type_usages_f pptu,
hr.per_person_types ppt
WHERE PAPF.business_group_id = :$PROFILE$.PER_BUSINESS_GROUP_ID
and trunc(sysdate) between papf.effective_start_date and PAPF.EFFECTIVE_END_DATE
and papf.person_id = pptu.person_id
and pptu.effective_start_date between
papf.effective_start_date and papf.effective_end_date
and papf.person_type_id = pptu.person_type_id
and pptu.person_type_id = ppt.person_type_id
and papf.person_type_id = ppt.person_type_id
and papf.business_group_id = ppt.business_group_id
-- modify this according to your requirement
AND ppt.system_person_type in ('EMP','EMP_APL','EX_EMP')