VIEW: APPS.HRI_MD_REC_VAC_AGE_DETAIL_V

Source

View Text - Preformatted

SELECT vac.total_days_open total_days_open ,vac.number_of_openings number_of_openings ,hri_opl_recruitment.calc_no_apls(vac.vacancy_id,vac.date_from) vacancy_apl_count ,vac.budget_measurement_value budget_measurement_value ,hri_opl_recruitment.calc_avg_days_to_hire(vac.vacancy_id,vac.date_from) days_to_recruit ,hri_opl_recruitment.calc_avg_fill_to_hire(vac.vacancy_id,vac.date_from) days_accept_to_hire ,hri_opl_recruitment.calc_avg_days_to_fill(vac.vacancy_id,vac.date_from) days_to_accept ,hri_opl_rec_bands.is_in_vac_band(vac.total_days_open, 1) vage1_indicator ,hri_opl_rec_bands.is_in_vac_band(vac.total_days_open, 2) vage2_indicator ,hri_opl_rec_bands.is_in_vac_band(vac.total_days_open, 3) vage3_indicator ,hri_opl_rec_bands.is_in_vac_band(vac.total_days_open, 4) vage4_indicator ,DECODE(vac.vacancy_status_code, 'APPROVED', 1, 0) approved_indicator ,DECODE(vac.vacancy_status_code, 'HOLD', 1, 0) hold_indicator ,DECODE(vac.vacancy_status_code, 'REJECTED', 1, 0) rejected_indicator ,DECODE(vac.vacancy_status_code, 'PENDING', 1, 0) pending_indicator ,DECODE(vac.vacancy_status_code, 'UNAPPROVED', 1, 0) unapproved_indicator ,DECODE(vac.vacancy_status_code, 'CLOSED', 1, 0) closed_indicator ,DECODE(vac.vacancy_status_code, 'APPROVED',0 ,'HOLD',0 ,'REJECTED',0 ,'PENDING',0 ,'UNAPPROVED',0 ,'CLOSED',0 , 1) other_status_indicator ,vac.vacancy_name vacancy_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE', vac.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', vac.vacancy_status_code) VACANCY_STATUS ,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 ,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 vac.recruiter_id = rec.person_id (+) AND TRUNC(SYSDATE) BETWEEN rec.effective_start_date (+) AND rec.effective_end_date (+) AND vac.manager_id = mgr.person_id (+) AND TRUNC(SYSDATE) BETWEEN mgr.effective_start_date (+) AND mgr.effective_end_date (+) AND vac.organization_id = orgT.organization_id (+) AND orgT.language (+) = USERENV('LANG') AND vac.location_id = locT.location_id (+) AND locT.language (+) = USERENV('LANG') AND vac.job_id = job.job_id (+) AND vac.grade_id = grd.grade_id (+) AND vac.position_id = pos.position_id (+) WITH READ ONLY

View Text - HTML Formatted

SELECT VAC.TOTAL_DAYS_OPEN TOTAL_DAYS_OPEN
, VAC.NUMBER_OF_OPENINGS NUMBER_OF_OPENINGS
, HRI_OPL_RECRUITMENT.CALC_NO_APLS(VAC.VACANCY_ID
, VAC.DATE_FROM) VACANCY_APL_COUNT
, VAC.BUDGET_MEASUREMENT_VALUE BUDGET_MEASUREMENT_VALUE
, HRI_OPL_RECRUITMENT.CALC_AVG_DAYS_TO_HIRE(VAC.VACANCY_ID
, VAC.DATE_FROM) DAYS_TO_RECRUIT
, HRI_OPL_RECRUITMENT.CALC_AVG_FILL_TO_HIRE(VAC.VACANCY_ID
, VAC.DATE_FROM) DAYS_ACCEPT_TO_HIRE
, HRI_OPL_RECRUITMENT.CALC_AVG_DAYS_TO_FILL(VAC.VACANCY_ID
, VAC.DATE_FROM) DAYS_TO_ACCEPT
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VAC.TOTAL_DAYS_OPEN
, 1) VAGE1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VAC.TOTAL_DAYS_OPEN
, 2) VAGE2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VAC.TOTAL_DAYS_OPEN
, 3) VAGE3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(VAC.TOTAL_DAYS_OPEN
, 4) VAGE4_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'APPROVED'
, 1
, 0) APPROVED_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'HOLD'
, 1
, 0) HOLD_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'REJECTED'
, 1
, 0) REJECTED_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'PENDING'
, 1
, 0) PENDING_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'UNAPPROVED'
, 1
, 0) UNAPPROVED_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'CLOSED'
, 1
, 0) CLOSED_INDICATOR
, DECODE(VAC.VACANCY_STATUS_CODE
, 'APPROVED'
, 0
, 'HOLD'
, 0
, 'REJECTED'
, 0
, 'PENDING'
, 0
, 'UNAPPROVED'
, 0
, 'CLOSED'
, 0
, 1) OTHER_STATUS_INDICATOR
, VAC.VACANCY_NAME VACANCY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, VAC.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'
, VAC.VACANCY_STATUS_CODE) VACANCY_STATUS
, 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
, 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 VAC.RECRUITER_ID = REC.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN REC.EFFECTIVE_START_DATE (+)
AND REC.EFFECTIVE_END_DATE (+)
AND VAC.MANAGER_ID = MGR.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN MGR.EFFECTIVE_START_DATE (+)
AND MGR.EFFECTIVE_END_DATE (+)
AND VAC.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND VAC.LOCATION_ID = LOCT.LOCATION_ID (+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND VAC.JOB_ID = JOB.JOB_ID (+)
AND VAC.GRADE_ID = GRD.GRADE_ID (+)
AND VAC.POSITION_ID = POS.POSITION_ID (+) WITH READ ONLY