Query to get Person Name from Position in Fusion HCM
SELECT PPNF.FULL_NAME
FROM FUSION.PER_PERSON_NAMES_F PPNF,
FUSION.PER_ALL_PEOPLE_F PAPF,
FUSION.PER_ALL_ASSIGNMENTS_M PAAM
WHERE PPNF.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PAAM.JOB_ID = P_JOB_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.POSITION_ID = &P_POSITION_ID
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND
PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND
PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
PAPF.EFFECTIVE_END_DATE;
FROM FUSION.PER_PERSON_NAMES_F PPNF,
FUSION.PER_ALL_PEOPLE_F PAPF,
FUSION.PER_ALL_ASSIGNMENTS_M PAAM
WHERE PPNF.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PAAM.JOB_ID = P_JOB_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.POSITION_ID = &P_POSITION_ID
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND
PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND
PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
PAPF.EFFECTIVE_END_DATE;
Comments
Post a Comment