DBA Data[Home] [Help]

VIEW: APPS.HRI_MD_REC_VAC_TIME_V

Source

View Text - Preformatted

SELECT vcs.total_days_open total_days_open ,vcs.number_of_openings number_of_openings ,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.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.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 ,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.fk_vacancy_id = vac.vacancy_id AND aac.accepted_date IS NOT NULL GROUP BY vac.total_days_open ,vac.number_of_openings ,vac.budget_measurement_value ,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
View Text - HTML Formatted

SELECT VCS.TOTAL_DAYS_OPEN TOTAL_DAYS_OPEN
, VCS.NUMBER_OF_OPENINGS NUMBER_OF_OPENINGS
, 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.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.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
, 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.FK_VACANCY_ID = VAC.VACANCY_ID
AND AAC.ACCEPTED_DATE IS NOT NULL GROUP BY VAC.TOTAL_DAYS_OPEN
, VAC.NUMBER_OF_OPENINGS
, VAC.BUDGET_MEASUREMENT_VALUE
, 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