VIEW: APPS.HR_GRADE_POS_LOV_V

Source

View Text - Preformatted

SELECT grt.name , grd.grade_id , vgp.date_from , vgp.date_to , grd.business_group_id , pos.position_id , lkp.meaning valid_for_pos from hr_lookups lkp , per_valid_grades vgp , per_grades_tl grt , per_grades grd , hr_all_positions pos WHERE grd.grade_id=vgp.grade_id and grd.date_from between vgp.date_from and nvl(vgp.date_to, grd.date_from+1) and lkp.lookup_type='YES_NO' and lkp.lookup_code='Y' and grt.grade_id = grd.grade_id and grt.language = userenv('LANG') and vgp.position_id=pos.position_id and pos.date_effective < nvl(grd.date_to, pos.date_effective+1) and nvl(pos.date_end,grd.date_from+1) > grd.date_from UNION select grt.name , grd.grade_id , grd.date_from , grd.date_to , grd.business_group_id , pos.position_id , lkp.meaning valid_for_pos from hr_lookups lkp , per_grades_tl grt , per_grades grd , hr_all_positions pos where lkp.lookup_type='YES_NO' and lkp.lookup_code='N' and grt.grade_id = grd.grade_id and grt.language = userenv('LANG') and pos.date_effective < nvl(grd.date_to, pos.date_effective+1) and nvl(pos.date_end,grd.date_from+1) > grd.date_from and grd.business_group_id+0=pos.business_group_id+0 and not exists (select null from per_valid_grades vgp where pos.position_id=vgp.position_id and grd.grade_id=vgp.grade_id and vgp.date_from between grd.date_from and nvl(grd.date_to,vgp.date_from+1))

View Text - HTML Formatted

SELECT GRT.NAME
, GRD.GRADE_ID
, VGP.DATE_FROM
, VGP.DATE_TO
, GRD.BUSINESS_GROUP_ID
, POS.POSITION_ID
, LKP.MEANING VALID_FOR_POS
FROM HR_LOOKUPS LKP
, PER_VALID_GRADES VGP
, PER_GRADES_TL GRT
, PER_GRADES GRD
, HR_ALL_POSITIONS POS
WHERE GRD.GRADE_ID=VGP.GRADE_ID
AND GRD.DATE_FROM BETWEEN VGP.DATE_FROM
AND NVL(VGP.DATE_TO
, GRD.DATE_FROM+1)
AND LKP.LOOKUP_TYPE='YES_NO'
AND LKP.LOOKUP_CODE='Y'
AND GRT.GRADE_ID = GRD.GRADE_ID
AND GRT.LANGUAGE = USERENV('LANG')
AND VGP.POSITION_ID=POS.POSITION_ID
AND POS.DATE_EFFECTIVE < NVL(GRD.DATE_TO
, POS.DATE_EFFECTIVE+1)
AND NVL(POS.DATE_END
, GRD.DATE_FROM+1) > GRD.DATE_FROM UNION SELECT GRT.NAME
, GRD.GRADE_ID
, GRD.DATE_FROM
, GRD.DATE_TO
, GRD.BUSINESS_GROUP_ID
, POS.POSITION_ID
, LKP.MEANING VALID_FOR_POS
FROM HR_LOOKUPS LKP
, PER_GRADES_TL GRT
, PER_GRADES GRD
, HR_ALL_POSITIONS POS
WHERE LKP.LOOKUP_TYPE='YES_NO'
AND LKP.LOOKUP_CODE='N'
AND GRT.GRADE_ID = GRD.GRADE_ID
AND GRT.LANGUAGE = USERENV('LANG')
AND POS.DATE_EFFECTIVE < NVL(GRD.DATE_TO
, POS.DATE_EFFECTIVE+1)
AND NVL(POS.DATE_END
, GRD.DATE_FROM+1) > GRD.DATE_FROM
AND GRD.BUSINESS_GROUP_ID+0=POS.BUSINESS_GROUP_ID+0
AND NOT EXISTS (SELECT NULL
FROM PER_VALID_GRADES VGP
WHERE POS.POSITION_ID=VGP.POSITION_ID
AND GRD.GRADE_ID=VGP.GRADE_ID
AND VGP.DATE_FROM BETWEEN GRD.DATE_FROM
AND NVL(GRD.DATE_TO
, VGP.DATE_FROM+1))