VIEW: APPS.HR_GRADE_JOB_LOV_V

Source

View Text - Preformatted

SELECT grt.name , grd.grade_id , vgj.date_from , vgj.date_to , grd.business_group_id , job.job_id , lkp.meaning valid_for_job from hr_lookups lkp , per_valid_grades vgj , per_grades_tl grt , per_grades grd , per_jobs job WHERE grd.grade_id=vgj.grade_id and vgj.date_from between grd.date_from and nvl(grd.date_to, vgj.date_from+1) and lkp.lookup_type='YES_NO' and lkp.lookup_code='Y' and vgj.job_id=job.job_id and job.date_from < nvl(grd.date_to, job.date_from+1) and nvl(job.date_to, grd.date_from+1) > grd.date_from and grt.grade_id = grd.grade_id and grt.language = userenv('LANG') UNION select grt.name , grd.grade_id , grd.date_from , grd.date_to , grd.business_group_id , job.job_id , lkp.meaning valid_for_job from hr_lookups lkp , per_grades_tl grt , per_grades grd , per_jobs job where lkp.lookup_type='YES_NO' and lkp.lookup_code='N' and grt.grade_id = grd.grade_id and grt.language = userenv('LANG') and job.date_from < nvl(grd.date_to, job.date_from+1) and nvl(job.date_to, grd.date_from+1) > grd.date_from and grd.business_group_id+0=job.business_group_id+0 and not exists (select null from per_valid_grades vgj where job.job_id=vgj.job_id and grd.grade_id=vgj.grade_id and vgj.date_from between grd.date_from and nvl(grd.date_to,vgj. date_from+1))

View Text - HTML Formatted

SELECT GRT.NAME
, GRD.GRADE_ID
, VGJ.DATE_FROM
, VGJ.DATE_TO
, GRD.BUSINESS_GROUP_ID
, JOB.JOB_ID
, LKP.MEANING VALID_FOR_JOB
FROM HR_LOOKUPS LKP
, PER_VALID_GRADES VGJ
, PER_GRADES_TL GRT
, PER_GRADES GRD
, PER_JOBS JOB
WHERE GRD.GRADE_ID=VGJ.GRADE_ID
AND VGJ.DATE_FROM BETWEEN GRD.DATE_FROM
AND NVL(GRD.DATE_TO
, VGJ.DATE_FROM+1)
AND LKP.LOOKUP_TYPE='YES_NO'
AND LKP.LOOKUP_CODE='Y'
AND VGJ.JOB_ID=JOB.JOB_ID
AND JOB.DATE_FROM < NVL(GRD.DATE_TO
, JOB.DATE_FROM+1)
AND NVL(JOB.DATE_TO
, GRD.DATE_FROM+1) > GRD.DATE_FROM
AND GRT.GRADE_ID = GRD.GRADE_ID
AND GRT.LANGUAGE = USERENV('LANG') UNION SELECT GRT.NAME
, GRD.GRADE_ID
, GRD.DATE_FROM
, GRD.DATE_TO
, GRD.BUSINESS_GROUP_ID
, JOB.JOB_ID
, LKP.MEANING VALID_FOR_JOB
FROM HR_LOOKUPS LKP
, PER_GRADES_TL GRT
, PER_GRADES GRD
, PER_JOBS JOB
WHERE LKP.LOOKUP_TYPE='YES_NO'
AND LKP.LOOKUP_CODE='N'
AND GRT.GRADE_ID = GRD.GRADE_ID
AND GRT.LANGUAGE = USERENV('LANG')
AND JOB.DATE_FROM < NVL(GRD.DATE_TO
, JOB.DATE_FROM+1)
AND NVL(JOB.DATE_TO
, GRD.DATE_FROM+1) > GRD.DATE_FROM
AND GRD.BUSINESS_GROUP_ID+0=JOB.BUSINESS_GROUP_ID+0
AND NOT EXISTS (SELECT NULL
FROM PER_VALID_GRADES VGJ
WHERE JOB.JOB_ID=VGJ.JOB_ID
AND GRD.GRADE_ID=VGJ.GRADE_ID
AND VGJ.DATE_FROM BETWEEN GRD.DATE_FROM
AND NVL(GRD.DATE_TO
, VGJ. DATE_FROM+1))