SELECT
PAPF.PERSON_NUMBER, HAUFT.NAME LEGALEMPLOYER
FROM
HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
HR_ALL_ORGANIZATION_UNITS_F HAOUF,
HR_ORGANIZATION_UNITS_F_TL HAUFT,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF
WHERE 1=1
AND HAOUF.ORGANIZATION_ID = HOUCF.ORGANIZATION_ID
AND HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
AND HAOUF.EFFECTIVE_START_DATE BETWEEN HOUCF.EFFECTIVE_START_DATE AND HOUCF.EFFECTIVE_END_DATE
AND HAUFT.LANGUAGE = ‘US’
AND HAUFT.EFFECTIVE_START_DATE = HAOUF.EFFECTIVE_START_DATE
AND HAUFT.EFFECTIVE_END_DATE = HAOUF.EFFECTIVE_END_DATE
AND HOUCF.CLASSIFICATION_CODE = ‘HCM_LEMP’
AND SYSDATE BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE
AND HAUFT.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_ID
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.PRIMARY_ASSIGNMENT_FLAG = ‘Y’
AND PAAM.ASSIGNMENT_TYPE = ‘E’
AND PAAM.EFFECTIVE_LATEST_CHANGE = ‘Y’
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PAPF.PERSON_NUMBER = :PERSON_NUMBER /HARDCODE YOUR PERSON NUMBER HERE/
ORDER BY 1
