VIEW: APPS.IRCFV_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 ,NVL(vac.date_to, hr_general.end_of_time) vacancy_end_date_nn /* foreign key resolutions */ ,orgT.name organization_name ,job.name job_name ,pos.name position_name ,grd.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 ,nvl(req.date_to, hr_general.end_of_time) requisition_end_date_nn ,peo1.full_name raised_by ,peo2.full_name recruiter_name ,peo3.full_name manager_name ,hr_bis.bis_decode_lookup('YES_NO',isc.employee) suitable_for_employee ,hr_bis.bis_decode_lookup('YES_NO',isc.contractor) suitable_for_contractor ,hr_bis.bis_decode_lookup('IRC_EMP_CAT',isc.employment_category) employment_category ,isc.keywords keywords ,hr_bis.bis_decode_lookup('IRC_TRAVEL_PERCENTAGE', isc.travel_percentage) travel_percentage ,isc.min_salary min_salary ,isc.max_salary max_salary ,curT.name salary_corrency ,hr_bis.bis_decode_lookup('PAY_BASIS',isc.salary_period) salary_period ,hr_bis.bis_decode_lookup('IRC_PROFESSIONAL_AREA',isc.professional_area) professional_area ,hr_bis.bis_decode_lookup('IRC_WORK_AT_HOME',isc.work_at_home) work_at_home ,qal1.name minimum_qualification_level ,qal2.name maximum_qualification_level /* descriptive flexfield */ ,'_DF:PER:PER_VACANCIES:vac' ,'_DF:PER:PER_REQUISITIONS:req' ,'_DF:PER:IRC_SEARCH_CRITERIA:isc' ,'_DF:PER:IRC_SEARCH_CRITERIA_DDF:isc' /* system codes */ ,vac.budget_measurement_type budget_measurement_type_code ,isc.employee employee_code ,isc.contractor contractor_code ,isc.employment_category employment_category_code ,isc.professional_area professional_area_code ,isc.work_at_home work_at_home_code ,isc.travel_percentage travel_percentage_code ,isc.salary_period salary_period_code /* ids */ ,req.business_group_id business_group_id ,vac.grade_id grade_id ,vac.job_id job_id ,vac.location_id location_id ,vac.manager_id manager_id ,vac.organization_id 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 ,isc.search_criteria_id search_criteria_id ,qal1.qualification_type_id min_qualification_type_id ,qal2.qualification_type_id max_qualification_type_id FROM hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,pay_people_groups grp ,per_jobs job ,per_all_positions pos ,per_grades grd ,hr_locations_all_tl locT ,per_all_people_f peo1 ,per_all_people_f peo2 ,per_all_people_f peo3 ,per_requisitions req ,per_vacancies vac ,irc_search_criteria isc ,fnd_currencies_tl curT ,per_qualification_types qal1 ,per_qualification_types qal2 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 = job.job_id(+) AND vac.position_id = pos.position_id(+) AND vac.grade_id = grd.grade_id(+) 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 vac.manager_id = peo3.person_id(+) AND vac.date_from BETWEEN peo3.effective_start_date(+) AND peo3.effective_end_date(+) AND req.business_group_id = bgrt.organization_id AND bgrT.language = userenv('LANG') AND isc.object_type (+) = 'VACANCY' AND isc.object_id (+) = vac.vacancy_id AND isc.salary_currency = curT.currency_code (+) AND curT.language (+) = USERENV('LANG') AND isc.min_qual_level = qal1.qualification_type_id (+) AND isc.max_qual_level = qal2.qualification_type_id (+) 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
, NVL(VAC.DATE_TO
, HR_GENERAL.END_OF_TIME) VACANCY_END_DATE_NN /* FOREIGN KEY RESOLUTIONS */
, ORGT.NAME ORGANIZATION_NAME
, JOB.NAME JOB_NAME
, POS.NAME POSITION_NAME
, GRD.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
, NVL(REQ.DATE_TO
, HR_GENERAL.END_OF_TIME) REQUISITION_END_DATE_NN
, PEO1.FULL_NAME RAISED_BY
, PEO2.FULL_NAME RECRUITER_NAME
, PEO3.FULL_NAME MANAGER_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ISC.EMPLOYEE) SUITABLE_FOR_EMPLOYEE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ISC.CONTRACTOR) SUITABLE_FOR_CONTRACTOR
, HR_BIS.BIS_DECODE_LOOKUP('IRC_EMP_CAT'
, ISC.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, ISC.KEYWORDS KEYWORDS
, HR_BIS.BIS_DECODE_LOOKUP('IRC_TRAVEL_PERCENTAGE'
, ISC.TRAVEL_PERCENTAGE) TRAVEL_PERCENTAGE
, ISC.MIN_SALARY MIN_SALARY
, ISC.MAX_SALARY MAX_SALARY
, CURT.NAME SALARY_CORRENCY
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, ISC.SALARY_PERIOD) SALARY_PERIOD
, HR_BIS.BIS_DECODE_LOOKUP('IRC_PROFESSIONAL_AREA'
, ISC.PROFESSIONAL_AREA) PROFESSIONAL_AREA
, HR_BIS.BIS_DECODE_LOOKUP('IRC_WORK_AT_HOME'
, ISC.WORK_AT_HOME) WORK_AT_HOME
, QAL1.NAME MINIMUM_QUALIFICATION_LEVEL
, QAL2.NAME MAXIMUM_QUALIFICATION_LEVEL /* DESCRIPTIVE FLEXFIELD */
, '_DF:PER:PER_VACANCIES:VAC'
, '_DF:PER:PER_REQUISITIONS:REQ'
, '_DF:PER:IRC_SEARCH_CRITERIA:ISC'
, '_DF:PER:IRC_SEARCH_CRITERIA_DDF:ISC' /* SYSTEM CODES */
, VAC.BUDGET_MEASUREMENT_TYPE BUDGET_MEASUREMENT_TYPE_CODE
, ISC.EMPLOYEE EMPLOYEE_CODE
, ISC.CONTRACTOR CONTRACTOR_CODE
, ISC.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ISC.PROFESSIONAL_AREA PROFESSIONAL_AREA_CODE
, ISC.WORK_AT_HOME WORK_AT_HOME_CODE
, ISC.TRAVEL_PERCENTAGE TRAVEL_PERCENTAGE_CODE
, ISC.SALARY_PERIOD SALARY_PERIOD_CODE /* IDS */
, REQ.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, VAC.GRADE_ID GRADE_ID
, VAC.JOB_ID JOB_ID
, VAC.LOCATION_ID LOCATION_ID
, VAC.MANAGER_ID MANAGER_ID
, VAC.ORGANIZATION_ID 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
, ISC.SEARCH_CRITERIA_ID SEARCH_CRITERIA_ID
, QAL1.QUALIFICATION_TYPE_ID MIN_QUALIFICATION_TYPE_ID
, QAL2.QUALIFICATION_TYPE_ID MAX_QUALIFICATION_TYPE_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PAY_PEOPLE_GROUPS GRP
, PER_JOBS JOB
, PER_ALL_POSITIONS POS
, PER_GRADES GRD
, HR_LOCATIONS_ALL_TL LOCT
, PER_ALL_PEOPLE_F PEO1
, PER_ALL_PEOPLE_F PEO2
, PER_ALL_PEOPLE_F PEO3
, PER_REQUISITIONS REQ
, PER_VACANCIES VAC
, IRC_SEARCH_CRITERIA ISC
, FND_CURRENCIES_TL CURT
, PER_QUALIFICATION_TYPES QAL1
, PER_QUALIFICATION_TYPES QAL2
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 = JOB.JOB_ID(+)
AND VAC.POSITION_ID = POS.POSITION_ID(+)
AND VAC.GRADE_ID = GRD.GRADE_ID(+)
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 VAC.MANAGER_ID = PEO3.PERSON_ID(+)
AND VAC.DATE_FROM BETWEEN PEO3.EFFECTIVE_START_DATE(+)
AND PEO3.EFFECTIVE_END_DATE(+)
AND REQ.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ISC.OBJECT_TYPE (+) = 'VACANCY'
AND ISC.OBJECT_ID (+) = VAC.VACANCY_ID
AND ISC.SALARY_CURRENCY = CURT.CURRENCY_CODE (+)
AND CURT.LANGUAGE (+) = USERENV('LANG')
AND ISC.MIN_QUAL_LEVEL = QAL1.QUALIFICATION_TYPE_ID (+)
AND ISC.MAX_QUAL_LEVEL = QAL2.QUALIFICATION_TYPE_ID (+)
AND REQ.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, REQ.BUSINESS_GROUP_ID) WITH READ ONLY