VIEW: APPS.HRI_MD_REC_TIME_EFFICIENCY_V

Source

View Text - Preformatted

SELECT aac.application_date - vac.date_from days_to_application ,DECODE(aac.accepted_date, to_date(null), to_number(null), aac.accepted_date - vac.date_from) days_to_accept ,DECODE(aac.current_status_code, 'END_SCCSS', aac.hire_date - aac.accepted_date, to_number(null)) days_accept_to_hire ,DECODE(aac.current_status_code, 'END_SCCSS', aac.hire_date - vac.date_from, to_number(null)) days_to_hire ,aac.hire_date hire_date ,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 ,aac.person_id person_id ,vac.position_id position_id ,vac.recruiter_id recruiter_id ,vac.requisition_id requisition_id ,vac.vacancy_id vacancy_id ,vac.vacancy_status_code vacancy_status_code ,vac.budget_measurement_type_code budget_measurement_type_code ,vac.vacancy_category_code vacancy_category_code ,NVL(rac.type, NVL(aac.job_source_type_code, 'NA_EDW') ) rec_source_type_code FROM hri_mb_vacancy_v vac ,hri_mb_apl_activity_v aac ,per_recruitment_activities rac WHERE vac.vacancy_id = aac.fk_vacancy_id AND aac.recruitment_activity_id = rac.recruitment_activity_id (+) AND aac.current_status_code = 'END_SCCSS'

View Text - HTML Formatted

SELECT AAC.APPLICATION_DATE - VAC.DATE_FROM DAYS_TO_APPLICATION
, DECODE(AAC.ACCEPTED_DATE
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, AAC.ACCEPTED_DATE - VAC.DATE_FROM) DAYS_TO_ACCEPT
, DECODE(AAC.CURRENT_STATUS_CODE
, 'END_SCCSS'
, AAC.HIRE_DATE - AAC.ACCEPTED_DATE
, TO_NUMBER(NULL)) DAYS_ACCEPT_TO_HIRE
, DECODE(AAC.CURRENT_STATUS_CODE
, 'END_SCCSS'
, AAC.HIRE_DATE - VAC.DATE_FROM
, TO_NUMBER(NULL)) DAYS_TO_HIRE
, AAC.HIRE_DATE HIRE_DATE
, 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
, AAC.PERSON_ID PERSON_ID
, VAC.POSITION_ID POSITION_ID
, VAC.RECRUITER_ID RECRUITER_ID
, VAC.REQUISITION_ID REQUISITION_ID
, VAC.VACANCY_ID VACANCY_ID
, VAC.VACANCY_STATUS_CODE VACANCY_STATUS_CODE
, VAC.BUDGET_MEASUREMENT_TYPE_CODE BUDGET_MEASUREMENT_TYPE_CODE
, VAC.VACANCY_CATEGORY_CODE VACANCY_CATEGORY_CODE
, NVL(RAC.TYPE
, NVL(AAC.JOB_SOURCE_TYPE_CODE
, 'NA_EDW') ) REC_SOURCE_TYPE_CODE
FROM HRI_MB_VACANCY_V VAC
, HRI_MB_APL_ACTIVITY_V AAC
, PER_RECRUITMENT_ACTIVITIES RAC
WHERE VAC.VACANCY_ID = AAC.FK_VACANCY_ID
AND AAC.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID (+)
AND AAC.CURRENT_STATUS_CODE = 'END_SCCSS'