VIEW: APPS.HRI_MD_REC_VAC_DETAIL_V

Source

View Text - Preformatted

SELECT vcs.total_days_open total_days_open ,vcs.number_of_openings number_of_openings ,vcs.vacancy_apl_count vacancy_apl_count ,vcs.budget_measurement_value budget_measurement_value ,vcs.days_to_recruit days_to_recruit ,vcs.days_accept_to_hire days_accept_to_hire ,vcs.days_to_accept days_to_accept ,hri_opl_rec_bands.is_in_vac_band(vcs.total_days_open, 1) vage1_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.total_days_open, 2) vage2_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.total_days_open, 3) vage3_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.total_days_open, 4) vage4_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_recruit, 1) drec1_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_recruit, 2) drec2_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_recruit, 3) drec3_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_recruit, 4) drec4_indicator ,hri_opl_rec_bands.is_in_apl_band(vcs.days_accept_to_hire, 1) dach1_indicator ,hri_opl_rec_bands.is_in_apl_band(vcs.days_accept_to_hire, 2) dach2_indicator ,hri_opl_rec_bands.is_in_apl_band(vcs.days_accept_to_hire, 3) dach3_indicator ,hri_opl_rec_bands.is_in_apl_band(vcs.days_accept_to_hire, 4) dach4_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_accept, 1) dacc1_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_accept, 2) dacc2_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_accept, 3) dacc3_indicator ,hri_opl_rec_bands.is_in_vac_band(vcs.days_to_accept, 4) dacc4_indicator ,vcs.vacancy_name vacancy_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE', vcs.budget_measurement_type_code) budget_measurement_type ,rec.full_name recruiter_name ,mgr.full_name manager_name ,orgT.name organization_name ,locT.location_code location_name ,job.name job_name ,grd.name grade_name ,pos.name position_name ,hr_bis.bis_decode_lookup('VACANCY_STATUS', vcs.vacancy_status_code) VACANCY_STATUS ,vcs.start_date start_date ,vcs.end_date end_date ,vcs.last_updated last_updated ,vcs.vacancy_category_code vacancy_category_code ,vcs.budget_measurement_type_code budget_measurement_type_code ,vcs.vacancy_status_code vacancy_status_code ,vcs.business_group_id business_group_id ,vcs.grade_id grade_id ,vcs.job_id job_id ,vcs.location_id location_id ,vcs.manager_id manager_id ,vcs.fk_manager_id fk_manager_id ,vcs.organization_id organization_id ,vcs.fk_organization_id fk_organization_id ,vcs.people_group_id people_group_id ,vcs.position_id position_id ,vcs.recruiter_id recruiter_id ,vcs.requisition_id requisition_id ,vcs.vacancy_id vacancy_id FROM (SELECT vac.total_days_open total_days_open ,vac.number_of_openings number_of_openings ,vac.budget_measurement_value budget_measurement_value ,AVG(aac.hire_date - vac.date_from) days_to_recruit ,AVG(aac.hire_date - aac.accepted_date) days_accept_to_hire ,AVG(aac.accepted_date - vac.date_from) days_to_accept ,COUNT(aac.assignment_id) vacancy_apl_count ,vac.vacancy_name vacancy_name ,vac.date_from start_date ,vac.date_to end_date ,vac.last_updated last_updated ,vac.vacancy_category_code vacancy_category_code ,vac.budget_measurement_type_code budget_measurement_type_code ,vac.vacancy_status_code vacancy_status_code ,vac.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.fk_manager_id fk_manager_id ,vac.organization_id organization_id ,vac.fk_organization_id fk_organization_id ,vac.people_group_id people_group_id ,vac.position_id position_id ,vac.recruiter_id recruiter_id ,vac.requisition_id requisition_id ,vac.vacancy_id vacancy_id FROM hri_mb_vacancy_v vac ,hri_mb_apl_activity_v aac WHERE aac.vacancy_id = vac.vacancy_id GROUP BY vac.total_days_open ,vac.number_of_openings ,vac.budget_measurement_value ,vac.vacancy_name ,vac.date_from ,vac.date_to ,vac.last_updated ,vac.vacancy_category_code ,vac.budget_measurement_type_code ,vac.vacancy_status_code ,vac.business_group_id ,vac.grade_id ,vac.job_id ,vac.location_id ,vac.manager_id ,vac.fk_manager_id ,vac.organization_id ,vac.fk_organization_id ,vac.people_group_id ,vac.position_id ,vac.recruiter_id ,vac.requisition_id ,vac.vacancy_id) vcs ,per_all_people_f rec ,per_all_people_f mgr ,hr_all_organization_units_tl orgT ,hr_locations_all_tl locT ,per_all_positions pos ,per_grades grd ,per_jobs job WHERE vcs.recruiter_id = rec.person_id (+) AND TRUNC(SYSDATE) BETWEEN rec.effective_start_date (+) AND rec.effective_end_date (+) AND vcs.manager_id = mgr.person_id (+) AND TRUNC(SYSDATE) BETWEEN mgr.effective_start_date (+) AND mgr.effective_end_date (+) AND vcs.organization_id = orgT.organization_id (+) AND orgT.language (+) = USERENV('LANG') AND vcs.location_id = locT.location_id (+) AND vcs.job_id = job.job_id (+) AND vcs.grade_id = grd.grade_id (+) AND vcs.position_id = pos.position_id (+) AND locT.language(+) = USERENV('LANG') WITH READ ONLY

View Text - HTML Formatted

SELECT VCS.TOTAL_DAYS_OPEN TOTAL_DAYS_OPEN
, VCS.NUMBER_OF_OPENINGS NUMBER_OF_OPENINGS
, VCS.VACANCY_APL_COUNT VACANCY_APL_COUNT
, VCS.BUDGET_MEASUREMENT_VALUE BUDGET_MEASUREMENT_VALUE
, VCS.DAYS_TO_RECRUIT DAYS_TO_RECRUIT
, VCS.DAYS_ACCEPT_TO_HIRE DAYS_ACCEPT_TO_HIRE
, VCS.DAYS_TO_ACCEPT DAYS_TO_ACCEPT
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.TOTAL_DAYS_OPEN
, 1) VAGE1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.TOTAL_DAYS_OPEN
, 2) VAGE2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.TOTAL_DAYS_OPEN
, 3) VAGE3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.TOTAL_DAYS_OPEN
, 4) VAGE4_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_RECRUIT
, 1) DREC1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_RECRUIT
, 2) DREC2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_RECRUIT
, 3) DREC3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_RECRUIT
, 4) DREC4_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(VCS.DAYS_ACCEPT_TO_HIRE
, 1) DACH1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(VCS.DAYS_ACCEPT_TO_HIRE
, 2) DACH2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(VCS.DAYS_ACCEPT_TO_HIRE
, 3) DACH3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(VCS.DAYS_ACCEPT_TO_HIRE
, 4) DACH4_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_ACCEPT
, 1) DACC1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_ACCEPT
, 2) DACC2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_ACCEPT
, 3) DACC3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VCS.DAYS_TO_ACCEPT
, 4) DACC4_INDICATOR
, VCS.VACANCY_NAME VACANCY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, VCS.BUDGET_MEASUREMENT_TYPE_CODE) BUDGET_MEASUREMENT_TYPE
, REC.FULL_NAME RECRUITER_NAME
, MGR.FULL_NAME MANAGER_NAME
, ORGT.NAME ORGANIZATION_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, JOB.NAME JOB_NAME
, GRD.NAME GRADE_NAME
, POS.NAME POSITION_NAME
, HR_BIS.BIS_DECODE_LOOKUP('VACANCY_STATUS'
, VCS.VACANCY_STATUS_CODE) VACANCY_STATUS
, VCS.START_DATE START_DATE
, VCS.END_DATE END_DATE
, VCS.LAST_UPDATED LAST_UPDATED
, VCS.VACANCY_CATEGORY_CODE VACANCY_CATEGORY_CODE
, VCS.BUDGET_MEASUREMENT_TYPE_CODE BUDGET_MEASUREMENT_TYPE_CODE
, VCS.VACANCY_STATUS_CODE VACANCY_STATUS_CODE
, VCS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, VCS.GRADE_ID GRADE_ID
, VCS.JOB_ID JOB_ID
, VCS.LOCATION_ID LOCATION_ID
, VCS.MANAGER_ID MANAGER_ID
, VCS.FK_MANAGER_ID FK_MANAGER_ID
, VCS.ORGANIZATION_ID ORGANIZATION_ID
, VCS.FK_ORGANIZATION_ID FK_ORGANIZATION_ID
, VCS.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, VCS.POSITION_ID POSITION_ID
, VCS.RECRUITER_ID RECRUITER_ID
, VCS.REQUISITION_ID REQUISITION_ID
, VCS.VACANCY_ID VACANCY_ID
FROM (SELECT VAC.TOTAL_DAYS_OPEN TOTAL_DAYS_OPEN
, VAC.NUMBER_OF_OPENINGS NUMBER_OF_OPENINGS
, VAC.BUDGET_MEASUREMENT_VALUE BUDGET_MEASUREMENT_VALUE
, AVG(AAC.HIRE_DATE - VAC.DATE_FROM) DAYS_TO_RECRUIT
, AVG(AAC.HIRE_DATE - AAC.ACCEPTED_DATE) DAYS_ACCEPT_TO_HIRE
, AVG(AAC.ACCEPTED_DATE - VAC.DATE_FROM) DAYS_TO_ACCEPT
, COUNT(AAC.ASSIGNMENT_ID) VACANCY_APL_COUNT
, VAC.VACANCY_NAME VACANCY_NAME
, VAC.DATE_FROM START_DATE
, VAC.DATE_TO END_DATE
, VAC.LAST_UPDATED LAST_UPDATED
, VAC.VACANCY_CATEGORY_CODE VACANCY_CATEGORY_CODE
, VAC.BUDGET_MEASUREMENT_TYPE_CODE BUDGET_MEASUREMENT_TYPE_CODE
, VAC.VACANCY_STATUS_CODE VACANCY_STATUS_CODE
, VAC.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.FK_MANAGER_ID FK_MANAGER_ID
, VAC.ORGANIZATION_ID ORGANIZATION_ID
, VAC.FK_ORGANIZATION_ID FK_ORGANIZATION_ID
, VAC.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, VAC.POSITION_ID POSITION_ID
, VAC.RECRUITER_ID RECRUITER_ID
, VAC.REQUISITION_ID REQUISITION_ID
, VAC.VACANCY_ID VACANCY_ID
FROM HRI_MB_VACANCY_V VAC
, HRI_MB_APL_ACTIVITY_V AAC
WHERE AAC.VACANCY_ID = VAC.VACANCY_ID GROUP BY VAC.TOTAL_DAYS_OPEN
, VAC.NUMBER_OF_OPENINGS
, VAC.BUDGET_MEASUREMENT_VALUE
, VAC.VACANCY_NAME
, VAC.DATE_FROM
, VAC.DATE_TO
, VAC.LAST_UPDATED
, VAC.VACANCY_CATEGORY_CODE
, VAC.BUDGET_MEASUREMENT_TYPE_CODE
, VAC.VACANCY_STATUS_CODE
, VAC.BUSINESS_GROUP_ID
, VAC.GRADE_ID
, VAC.JOB_ID
, VAC.LOCATION_ID
, VAC.MANAGER_ID
, VAC.FK_MANAGER_ID
, VAC.ORGANIZATION_ID
, VAC.FK_ORGANIZATION_ID
, VAC.PEOPLE_GROUP_ID
, VAC.POSITION_ID
, VAC.RECRUITER_ID
, VAC.REQUISITION_ID
, VAC.VACANCY_ID) VCS
, PER_ALL_PEOPLE_F REC
, PER_ALL_PEOPLE_F MGR
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_LOCATIONS_ALL_TL LOCT
, PER_ALL_POSITIONS POS
, PER_GRADES GRD
, PER_JOBS JOB
WHERE VCS.RECRUITER_ID = REC.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN REC.EFFECTIVE_START_DATE (+)
AND REC.EFFECTIVE_END_DATE (+)
AND VCS.MANAGER_ID = MGR.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN MGR.EFFECTIVE_START_DATE (+)
AND MGR.EFFECTIVE_END_DATE (+)
AND VCS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND VCS.LOCATION_ID = LOCT.LOCATION_ID (+)
AND VCS.JOB_ID = JOB.JOB_ID (+)
AND VCS.GRADE_ID = GRD.GRADE_ID (+)
AND VCS.POSITION_ID = POS.POSITION_ID (+)
AND LOCT.LANGUAGE(+) = USERENV('LANG') WITH READ ONLY