VIEW: APPS.HRFV_VACANCIES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,vac.name vacancy_name ,vac.description description ,hr_bis.bis_decode_lookup('VACANCY_STATUS', vac.status) vacancy_status ,vac.number_of_openings number_of_openings ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE',vac.budget_measurement_type) budget_measurement_type ,vac.budget_measurement_value budget_measurement_value ,vac.date_from vacancy_start_date ,vac.date_to vacancy_end_date ,orgT.name organization_name ,jbt.name job_name ,pft.name position_name ,gdt.name grade_name ,grp.group_name people_group ,locT.location_code location_name ,req.name requisition_name ,req.date_from requisition_start_date ,req.date_to requisition_end_date ,peo1.full_name raised_by ,peo2.full_name recruiter_name ,'_DF:PER:PER_VACANCIES:vac' ,'_DF:PER:PER_REQUISITIONS:req' ,req.business_group_id business_group_id ,vac.grade_id grade_id ,vac.job_id job_id ,vac.location_id location_id ,NVL(vac.organization_id,-1) organization_id ,vac.people_group_id people_group_id ,vac.position_id position_id ,req.person_id raiser_id ,vac.recruiter_id recruiter_id ,req.requisition_id requisition_id ,vac.vacancy_id vacancy_id FROM hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,pay_people_groups grp ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,per_grades_tl gdt ,hr_locations_all_tl locT ,per_all_people_f peo1 ,per_all_people_f peo2 ,per_requisitions req ,per_vacancies vac WHERE vac.organization_id = orgT.organization_id(+) AND orgT.language(+) = userenv('LANG') AND vac.people_group_id = grp.people_group_id(+) AND vac.job_id = jbt.job_id(+) AND jbt.language(+) = userenv('LANG') AND vac.position_id = pft.position_id(+) AND pft.language(+) = userenv('LANG') AND vac.grade_id = gdt.grade_id(+) AND gdt.language(+) = userenv('LANG') AND vac.location_id = locT.location_id(+) AND locT.language(+) = userenv('LANG') AND vac.requisition_id = req.requisition_id AND vac.recruiter_id = peo2.person_id(+) AND vac.date_from BETWEEN peo2.effective_start_date(+) AND peo2.effective_end_date(+) AND req.person_id = peo1.person_id(+) AND req.date_from BETWEEN peo1.effective_start_date(+) AND peo1.effective_end_date(+) AND req.business_group_id = bgrT.organization_id AND bgrT.language(+) = userenv('LANG') AND req.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,req.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, VAC.NAME VACANCY_NAME
, VAC.DESCRIPTION DESCRIPTION
, HR_BIS.BIS_DECODE_LOOKUP('VACANCY_STATUS'
, VAC.STATUS) VACANCY_STATUS
, VAC.NUMBER_OF_OPENINGS NUMBER_OF_OPENINGS
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, VAC.BUDGET_MEASUREMENT_TYPE) BUDGET_MEASUREMENT_TYPE
, VAC.BUDGET_MEASUREMENT_VALUE BUDGET_MEASUREMENT_VALUE
, VAC.DATE_FROM VACANCY_START_DATE
, VAC.DATE_TO VACANCY_END_DATE
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, GRP.GROUP_NAME PEOPLE_GROUP
, LOCT.LOCATION_CODE LOCATION_NAME
, REQ.NAME REQUISITION_NAME
, REQ.DATE_FROM REQUISITION_START_DATE
, REQ.DATE_TO REQUISITION_END_DATE
, PEO1.FULL_NAME RAISED_BY
, PEO2.FULL_NAME RECRUITER_NAME
, '_DF:PER:PER_VACANCIES:VAC'
, '_DF:PER:PER_REQUISITIONS:REQ'
, REQ.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, VAC.GRADE_ID GRADE_ID
, VAC.JOB_ID JOB_ID
, VAC.LOCATION_ID LOCATION_ID
, NVL(VAC.ORGANIZATION_ID
, -1) ORGANIZATION_ID
, VAC.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, VAC.POSITION_ID POSITION_ID
, REQ.PERSON_ID RAISER_ID
, VAC.RECRUITER_ID RECRUITER_ID
, REQ.REQUISITION_ID REQUISITION_ID
, VAC.VACANCY_ID VACANCY_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PAY_PEOPLE_GROUPS GRP
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, PER_GRADES_TL GDT
, HR_LOCATIONS_ALL_TL LOCT
, PER_ALL_PEOPLE_F PEO1
, PER_ALL_PEOPLE_F PEO2
, PER_REQUISITIONS REQ
, PER_VACANCIES VAC
WHERE VAC.ORGANIZATION_ID = ORGT.ORGANIZATION_ID(+)
AND ORGT.LANGUAGE(+) = USERENV('LANG')
AND VAC.PEOPLE_GROUP_ID = GRP.PEOPLE_GROUP_ID(+)
AND VAC.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND VAC.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE(+) = USERENV('LANG')
AND VAC.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND VAC.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE(+) = USERENV('LANG')
AND VAC.REQUISITION_ID = REQ.REQUISITION_ID
AND VAC.RECRUITER_ID = PEO2.PERSON_ID(+)
AND VAC.DATE_FROM BETWEEN PEO2.EFFECTIVE_START_DATE(+)
AND PEO2.EFFECTIVE_END_DATE(+)
AND REQ.PERSON_ID = PEO1.PERSON_ID(+)
AND REQ.DATE_FROM BETWEEN PEO1.EFFECTIVE_START_DATE(+)
AND PEO1.EFFECTIVE_END_DATE(+)
AND REQ.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE(+) = USERENV('LANG')
AND REQ.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, REQ.BUSINESS_GROUP_ID) WITH READ ONLY