VIEW: APPS.HRI_MD_REC_APL_TIME_V

Source

View Text - Preformatted

SELECT aac.hire_date - vac.date_from days_to_recruit ,aac.hire_date - aac.accepted_date days_accept_to_hire ,aac.accepted_date - vac.date_from days_to_accept ,hri_opl_rec_bands.is_in_vac_band(aac.hire_date - vac.date_from, 1) drec1_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.hire_date - vac.date_from, 2) drec2_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.hire_date - vac.date_from, 3) drec3_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.hire_date - vac.date_from, 4) drec4_indicator ,hri_opl_rec_bands.is_in_apl_band(aac.hire_date - aac.accepted_date, 1) dach1_indicator ,hri_opl_rec_bands.is_in_apl_band(aac.hire_date - aac.accepted_date, 2) dach2_indicator ,hri_opl_rec_bands.is_in_apl_band(aac.hire_date - aac.accepted_date, 3) dach3_indicator ,hri_opl_rec_bands.is_in_apl_band(aac.hire_date - aac.accepted_date, 4) dach4_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.accepted_date - vac.date_from, 1) dacc1_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.accepted_date - vac.date_from, 2) dacc2_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.accepted_date - vac.date_from, 3) dacc3_indicator ,hri_opl_rec_bands.is_in_vac_band(aac.accepted_date - vac.date_from, 4) dacc4_indicator ,aac.hire_date hire_date ,aac.accepted_date accepted_date ,aac.application_date application_start_date ,aac.projected_hire_date projected_hire_date ,aac.job_source_type_code job_source_type_code ,aac.job_source_name job_source_name ,aac.current_status_code application_status_code ,aac.application_id application_id ,aac.assignment_id assignment_id ,aac.business_group_id business_group_id ,aac.grade_id grade_id ,aac.job_id job_id ,aac.location_id location_id ,vac.manager_id manager_id ,vac.fk_manager_id fk_manager_id ,aac.organization_id organization_id ,aac.organization_id fk_organization_id ,aac.people_group_id people_group_id ,aac.person_id applicant_person_id ,aac.position_id position_id ,aac.posting_content_id posting_content_id ,aac.recruiter_id recruiter_id ,aac.recruitment_activity_id recruitment_activity_id ,aac.referred_by_person_id referred_by_person_id ,vac.requisition_id requisition_id ,aac.supervisor_id supervisor_id ,aac.vacancy_id vacancy_id ,aac.vendor_id vendor_id FROM hri_mb_vacancy_v vac ,hri_mb_apl_activity_v aac WHERE vac.vacancy_id = aac.fk_vacancy_id AND aac.accepted_date IS NOT NULL

View Text - HTML Formatted

SELECT AAC.HIRE_DATE - VAC.DATE_FROM DAYS_TO_RECRUIT
, AAC.HIRE_DATE - AAC.ACCEPTED_DATE DAYS_ACCEPT_TO_HIRE
, AAC.ACCEPTED_DATE - VAC.DATE_FROM DAYS_TO_ACCEPT
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.HIRE_DATE - VAC.DATE_FROM
, 1) DREC1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.HIRE_DATE - VAC.DATE_FROM
, 2) DREC2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.HIRE_DATE - VAC.DATE_FROM
, 3) DREC3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.HIRE_DATE - VAC.DATE_FROM
, 4) DREC4_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(AAC.HIRE_DATE - AAC.ACCEPTED_DATE
, 1) DACH1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(AAC.HIRE_DATE - AAC.ACCEPTED_DATE
, 2) DACH2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(AAC.HIRE_DATE - AAC.ACCEPTED_DATE
, 3) DACH3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_APL_BAND(AAC.HIRE_DATE - AAC.ACCEPTED_DATE
, 4) DACH4_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.ACCEPTED_DATE - VAC.DATE_FROM
, 1) DACC1_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.ACCEPTED_DATE - VAC.DATE_FROM
, 2) DACC2_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.ACCEPTED_DATE - VAC.DATE_FROM
, 3) DACC3_INDICATOR
, HRI_OPL_REC_BANDS.IS_IN_VAC_BAND(AAC.ACCEPTED_DATE - VAC.DATE_FROM
, 4) DACC4_INDICATOR
, AAC.HIRE_DATE HIRE_DATE
, AAC.ACCEPTED_DATE ACCEPTED_DATE
, AAC.APPLICATION_DATE APPLICATION_START_DATE
, AAC.PROJECTED_HIRE_DATE PROJECTED_HIRE_DATE
, AAC.JOB_SOURCE_TYPE_CODE JOB_SOURCE_TYPE_CODE
, AAC.JOB_SOURCE_NAME JOB_SOURCE_NAME
, AAC.CURRENT_STATUS_CODE APPLICATION_STATUS_CODE
, AAC.APPLICATION_ID APPLICATION_ID
, AAC.ASSIGNMENT_ID ASSIGNMENT_ID
, AAC.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, AAC.GRADE_ID GRADE_ID
, AAC.JOB_ID JOB_ID
, AAC.LOCATION_ID LOCATION_ID
, VAC.MANAGER_ID MANAGER_ID
, VAC.FK_MANAGER_ID FK_MANAGER_ID
, AAC.ORGANIZATION_ID ORGANIZATION_ID
, AAC.ORGANIZATION_ID FK_ORGANIZATION_ID
, AAC.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, AAC.PERSON_ID APPLICANT_PERSON_ID
, AAC.POSITION_ID POSITION_ID
, AAC.POSTING_CONTENT_ID POSTING_CONTENT_ID
, AAC.RECRUITER_ID RECRUITER_ID
, AAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, AAC.REFERRED_BY_PERSON_ID REFERRED_BY_PERSON_ID
, VAC.REQUISITION_ID REQUISITION_ID
, AAC.SUPERVISOR_ID SUPERVISOR_ID
, AAC.VACANCY_ID VACANCY_ID
, AAC.VENDOR_ID VENDOR_ID
FROM HRI_MB_VACANCY_V VAC
, HRI_MB_APL_ACTIVITY_V AAC
WHERE VAC.VACANCY_ID = AAC.FK_VACANCY_ID
AND AAC.ACCEPTED_DATE IS NOT NULL