Oracle HCM: Retrieve Complete Position Details with Job, Department, and Grade

SELECT HAPFT.NAME,
 (SELECT HOUFT.NAME FROM HR_ORGANIZATION_UNITS_F_TL HOUFT, HR_ALL_ORGANIZATION_UNITS_F HAOU 
   WHERE  HAPF.BUSINESS_UNIT_ID = HAOU.ORGANIZATION_ID 
   AND    HAOU.ORGANIZATION_ID = HOUFT.ORGANIZATION_ID
   AND    TRUNC(SYSDATE) BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
   AND    HOUFT.LANGUAGE = 'US' AND ROWNUM = 1) BUSINESS_UNIT,
 (SELECT HOUFT.NAME FROM HR_ORGANIZATION_UNITS_F_TL HOUFT, HR_ALL_ORGANIZATION_UNITS_F HAOU 
   WHERE  HAPF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID 
   AND    HAOU.ORGANIZATION_ID = HOUFT.ORGANIZATION_ID
   AND    TRUNC(SYSDATE) BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
   AND    HOUFT.LANGUAGE = 'US' AND ROWNUM = 1) DEPARTMENT,
    (SELECT PJFT.NAME   
    FROM PER_JOBS_F_TL PJFT , PER_JOBS_F PJF 
    WHERE PJFT.LANGUAGE = 'US' 
    AND   TRUNC(SYSDATE) BETWEEN PJFT.EFFECTIVE_START_DATE AND PJFT.EFFECTIVE_END_DATE
    AND   PJFT.JOB_ID = PJF.JOB_ID 
    AND   TRUNC(SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE AND PJF.EFFECTIVE_END_DATE
   AND   PJF.JOB_ID = HAPF.JOB_ID AND ROWNUM = 1) JOB,
    (SELECT PJF.JOB_CODE   
    FROM PER_JOBS_F_TL PJFT , PER_JOBS_F PJF 
    WHERE PJFT.LANGUAGE = 'US' 
    AND   TRUNC(SYSDATE) BETWEEN PJFT.EFFECTIVE_START_DATE AND PJFT.EFFECTIVE_END_DATE
    AND   PJFT.JOB_ID = PJF.JOB_ID 
    AND   TRUNC(SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE AND PJF.EFFECTIVE_END_DATE
   AND   PJF.JOB_ID = HAPF.JOB_ID AND ROWNUM = 1) JOB_CODE,
    (SELECT PGFT.NAME   
    FROM PER_GRADES_F_TL PGFT , PER_GRADES_F PGF 
    WHERE PGFT.LANGUAGE = 'US' 
    AND   TRUNC(SYSDATE) BETWEEN PGFT.EFFECTIVE_START_DATE AND PGFT.EFFECTIVE_END_DATE
    AND   PGFT.GRADE_ID = PGF.GRADE_ID 
    AND   TRUNC(SYSDATE) BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE
   AND   PGF.GRADE_ID = HAPF.ENTRY_GRADE_ID AND ROWNUM = 1) GRADE,
   HAPF.* 
    FROM HR_ALL_POSITIONS_F_TL HAPFT , HR_ALL_POSITIONS_F HAPF 
    WHERE HAPFT.LANGUAGE = 'US' 
    AND   TRUNC(SYSDATE) BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE
    AND   HAPFT.POSITION_ID = HAPF.POSITION_ID 
    AND   TRUNC(SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE
   ORDER BY HAPF.POSITION_CODE

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.