0
0
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.