SELECT PAPF.PERSON_NUMBER,
PPNF.FULL_NAME EMPLOYEE_NAME,
HLA.LOCATION_NAME PHYSICAL_LOCATION
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_NAMES_F PPNF,
PER_PERSONS PP,
HR_LOCATIONS_ALL HLA
WHERE PAPF.PERSON_ID =PAAF.PERSON_ID
AND PPNF.PERSON_ID =PAPF.PERSON_ID
AND PPNF.PERSON_ID =PP.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PAAF.PRIMARY_FLAG='Y'
AND PAAF.ASSIGNMENT_TYPE='E'
AND PAAF.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
AND HLA.LOCATION_ID(+)=PAAF.LOCATION_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
