• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:2 mins read
SQL Query to get Position and Job Valid Grades
SQL Query to get Position and Job Valid Grades
- SQL Query to get Position and Job Valid Grades
Total Visits: 19
Todays Visits: 0

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