SQL Query to get Position and Job Valid Grades

In this article we will look into how to get the Position and Job Valid Grades report.

SQL Query for Position Valid Grades:

SELECT 'Position Grade Report' Header,
'US' as COUNTRY_CODE,
PSF.POSITION_CODE,
PGFT.NAME as GRADE,
TO_CHAR(PSF.EFFECTIVE_START_DATE,'MM/DD/YYYY') AS EFFECTIVE_START_DATE ,
TO_CHAR(PSF.EFFECTIVE_END_DATE,'MM/DD/YYYY') AS EFFECTIVE_END_DATE
FROM
HR_ALL_POSITIONS_F_TL PFT,
HR_ALL_POSITIONS_F PSF,
HR_ORGANIZATION_UNITS_F_TL PBU,
PER_GRADES_F_TL PGFT,
PER_VALID_GRADES_F PVG
WHERE
PSF.EFFECTIVE_START_DATE = PFT.EFFECTIVE_START_DATE AND PSF.EFFECTIVE_END_DATE = PFT.EFFECTIVE_END_DATE AND PSF.POSITION_ID = PFT.POSITION_ID 
AND TRUNC(SYSDATE) BETWEEN PSF.EFFECTIVE_START_DATE AND PSF.EFFECTIVE_END_DATE AND PFT.LANGUAGE = 'US'
AND PSF.BUSINESS_UNIT_ID = PBU.ORGANIZATION_ID
AND PBU.LANGUAGE='US'
AND TRUNC(SYSDATE) BETWEEN PBU.EFFECTIVE_START_DATE AND PBU.EFFECTIVE_END_DATE
AND PSF.POSITION_ID=PVG.POSITION_ID
AND TRUNC(SYSDATE) BETWEEN PVG.EFFECTIVE_START_DATE AND PVG.EFFECTIVE_END_DATE
AND PVG.GRADE_ID=PGFT.GRADE_ID
AND PGFT.LANGUAGE='US'
AND TRUNC(SYSDATE) BETWEEN PGFT.EFFECTIVE_START_DATE AND PGFT.EFFECTIVE_END_DATE

SQL Query to get Job Valid Grades:

SELECT
JOB.JOB_CODE AS JOB_CODE,
TO_CHAR(JOB.EFFECTIVE_START_DATE,'MM/DD/YYYY') EFFECTIVE_START_DATE,
TO_CHAR(JOB.EFFECTIVE_END_DATE,'MM/DD/YYYY') EFFECTIVE_END_DATE,
GRADE_CODE,
ST.SET_CODE
FROM    
PER_JOBS_F_VL    JOB,
PER_VALID_GRADES_F PVGF,
PER_GRADES_F PGF,
FND_SETID_SETS ST
WHERE ST.SET_ID = JOB.SET_ID
AND ST.LANGUAGE = 'US'
AND SYSDATE BETWEEN JOB.EFFECTIVE_START_DATE AND JOB.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PVGF.EFFECTIVE_START_DATE AND PVGF.EFFECTIVE_END_DATE
AND PVGF.JOB_ID = JOB.JOB_ID
AND PGF.GRADE_ID = PVGF.GRADE_ID
--and JOB.Job_code in ( 'List of Job Code Names')
ORDER BY JOB_CODE