VIEW: APPS.HRI_OBI_MD_REC_VACANCY_V

Source

View Text - Preformatted

SELECT vac.time_day_vac_strt_fk ,vac.time_day_vac_end_fk ,vac.per_person_recr_fk per_person_rcrt_fk ,vac.per_person_rmgr_fk ,vac.per_person_rsed_fk ,vac.per_person_mrgd_fk ,vac.org_organztn_fk ,vac.org_organztn_mrgd_fk ,vac.geo_location_fk ,vac.job_job_fk ,vac.grd_grade_fk ,vac.pos_position_fk ,vac.rvac_vacncy_fk ,CASE WHEN vac.adt_vacancy_status_code = 'CLOSED' AND vac.time_day_vac_end_fk <= TRUNC(SYSDATE) THEN 0 ELSE 1 END vac_open_cnt ,SUM(apl.init_appl_stg_ind * apl.current_record_ind) init_appl_stg_cnt ,SUM(apl.asmt_stg_ind * apl.current_record_ind) asmt_stg_cnt ,SUM(apl.offr_extd_stg_ind * apl.current_record_ind) offr_extd_stg_cnt ,SUM(apl.strt_pndg_stg_ind * apl.current_record_ind) strt_pndg_stg_cnt ,SUM(apl.hire_stg_ind * apl.current_record_ind) hire_pre_pow1_cnt ,SUM(SIGN(apl.init_appl_stg_ind + apl.asmt_stg_ind + apl.offr_extd_stg_ind + apl.strt_pndg_stg_ind) * apl.current_record_ind) appl_in_progress_cnt ,SUM(apl.appl_strt_evnt_ind) appl_strt_evnt_cnt ,SUM(apl.appl_strt_nevnt_ind * (1 - apl.asmt_strt_nevnt_ind) * apl.appl_term_evnt_ind) appl_term_init_evnt_cnt ,SUM(apl.appl_strt_nevnt_ind * (1 - apl.asmt_strt_nevnt_ind) * apl.appl_term_vol_evnt_ind) appl_term_vol_init_evnt_cnt ,SUM(apl.appl_strt_nevnt_ind * (1 - apl.asmt_strt_nevnt_ind) * apl.appl_term_invol_evnt_ind) appl_term_invol_init_evnt_cnt ,SUM(apl.asmt_strt_evnt_ind) asmt_strt_evnt_cnt ,SUM(apl.asmt_strt_nevnt_ind * (1 - apl.offr_extd_nevnt_ind) * apl.appl_term_evnt_ind) appl_term_asmt_evnt_cnt ,SUM(apl.asmt_strt_nevnt_ind * (1 - apl.offr_extd_nevnt_ind) * apl.appl_term_vol_evnt_ind) appl_term_vol_asmt_evnt_cnt ,SUM(apl.asmt_strt_nevnt_ind * (1 - apl.offr_extd_nevnt_ind) * apl.appl_term_invol_evnt_ind) appl_term_invol_asmt_evnt_cnt ,SUM(apl.offr_extd_evnt_ind) offr_extd_evnt_cnt ,SUM(apl.offr_rjct_evnt_ind) appl_term_offr_evnt_cnt ,SUM(apl.offr_rjct_evnt_ind * apl.appl_term_vol_evnt_ind) appl_term_vol_offr_evnt_cnt ,SUM(apl.offr_rjct_evnt_ind * apl.appl_term_invol_evnt_ind) appl_term_invol_offr_evnt_cnt ,SUM(apl.offr_acpt_evnt_ind) offr_acpt_evnt_cnt ,SUM(apl.offr_acpt_nevnt_ind * (1 - apl.appl_hire_nevnt_ind) * apl.appl_term_evnt_ind) appl_term_acpt_evnt_cnt ,SUM(apl.offr_acpt_nevnt_ind * (1 - apl.appl_hire_nevnt_ind) * apl.appl_term_vol_evnt_ind) appl_term_vol_acpt_evnt_cnt ,SUM(apl.offr_acpt_nevnt_ind * (1 - apl.appl_hire_nevnt_ind) * apl.appl_term_invol_evnt_ind) appl_term_invol_acpt_evnt_cnt ,SUM(apl.appl_term_evnt_ind) appl_term_evnt_cnt ,SUM(apl.appl_term_vol_evnt_ind) appl_term_vol_evnt_cnt ,SUM(apl.appl_term_invol_evnt_ind) appl_term_invol_evnt_cnt ,SUM(apl.appl_hire_evnt_ind) hire_appl_evnt_cnt ,SUM(apl.post_hire_pow1_end_evnt_ind) post_hire_pow1_end_evnt_cnt ,SUM(apl.emp_sprtn_evnt_ind) pre_pow1_end_sprtn_evnt_cnt ,SUM((apl.appl_strt_date - vac.vac_strt_date) * apl.appl_strt_evnt_ind) vac_to_appl_strt_evnt_days ,SUM((apl.asmt_strt_date - vac.vac_strt_date) * apl.asmt_strt_evnt_ind) vac_to_asmt_strt_evnt_days ,SUM((apl.offr_extd_date - vac.vac_strt_date) * apl.offr_extd_evnt_ind) vac_to_offr_extd_evnt_days ,SUM((apl.offr_acpt_date - vac.vac_strt_date) * apl.offr_acpt_evnt_ind) vac_to_offr_acpt_evnt_days ,SUM((apl.offr_acpt_date - vac.vac_strt_date) * apl.appl_hire_evnt_ind) vac_to_fill_evnt_days ,SUM((apl.hire_date - vac.vac_strt_date) * apl.appl_hire_evnt_ind) vac_to_appl_hire_evnt_days ,SUM((apl.hire_date - apl.offr_acpt_date) * apl.appl_hire_evnt_ind) fill_to_appl_hire_evnt_days ,SUM((apl.offr_rjct_date - vac.vac_strt_date) * apl.offr_rjct_evnt_ind) vac_to_term_offr_evnt_days ,SUM((apl.appl_term_date - vac.vac_strt_date) * apl.appl_term_evnt_ind) vac_to_appl_term_evnt_days ,SUM((apl.asmt_strt_date - apl.appl_strt_date) * apl.asmt_strt_evnt_ind) appl_to_asmt_strt_evnt_days ,SUM((apl.offr_extd_date - apl.appl_strt_date) * apl.offr_extd_evnt_ind) appl_to_offr_extd_evnt_days ,SUM((apl.offr_acpt_date - apl.appl_strt_date) * apl.offr_acpt_evnt_ind) appl_to_offr_acpt_evnt_days ,SUM((apl.offr_acpt_date - apl.appl_strt_date) * apl.appl_hire_evnt_ind) appl_to_fill_evnt_days ,SUM((apl.hire_date - apl.appl_strt_date) * apl.appl_hire_evnt_ind) appl_to_appl_hire_evnt_days ,SUM((apl.offr_rjct_date - apl.appl_strt_date) * apl.offr_rjct_evnt_ind) appl_to_term_offr_evnt_days ,SUM((apl.appl_term_date - apl.appl_strt_date) * apl.appl_term_evnt_ind) appl_to_appl_term_evnt_days ,SUM((apl.offr_rjct_date - apl.offr_extd_date) * apl.appl_term_vol_evnt_ind) offr_to_term_vol_evnt_days ,SUM((apl.offr_rjct_date - apl.offr_extd_date) * apl.appl_term_invol_evnt_ind) offr_to_term_invol_evnt_days ,SUM((apl.offr_acpt_date - apl.offr_extd_date) * apl.offr_acpt_evnt_ind) offr_to_acpt_evnt_days ,SUM((apl.appl_term_date - apl.offr_acpt_date) * apl.appl_term_vol_evnt_ind) acpt_to_term_vol_evnt_days ,SUM((apl.appl_term_date - apl.offr_acpt_date) * apl.appl_term_invol_evnt_ind) acpt_to_term_invol_evnt_days ,vac.number_of_openings total_openings ,SUM(apl.appl_hire_evnt_ind) filled_openings ,CASE WHEN vac.adt_budget_type_code = 'HEAD' THEN vac.budget_measurement_value ELSE to_number(null) END total_headcount ,SUM(CASE WHEN vac.adt_budget_type_code = 'HEAD' THEN apl.appl_hire_evnt_ind * NVL(apl.headcount, 0) ELSE to_number(null) END) filled_headcount ,CASE WHEN vac.adt_budget_type_code = 'FTE' THEN vac.budget_measurement_value ELSE to_number(null) END total_fte ,SUM(CASE WHEN vac.adt_budget_type_code = 'FTE' THEN apl.appl_hire_evnt_ind * NVL(apl.fte, 0) ELSE to_number(null) END) filled_fte ,CASE WHEN vac.adt_budget_type_code = 'MONEY' THEN vac.budget_measurement_value ELSE to_number(null) END total_money ,to_number(null) filled_money ,CASE WHEN vac.number_of_openings <= SUM(apl.appl_hire_evnt_ind) THEN 1 ELSE 0 END vac_filled_ind FROM hri_mb_rec_cand_pipln_ct apl ,hri_mb_rec_vacancy_ct vac WHERE apl.rvac_vacncy_fk = vac.rvac_vacncy_fk GROUP BY vac.time_day_vac_strt_fk ,vac.time_day_vac_end_fk ,vac.per_person_recr_fk ,vac.per_person_rmgr_fk ,vac.per_person_rsed_fk ,vac.per_person_mrgd_fk ,vac.org_organztn_fk ,vac.org_organztn_mrgd_fk ,vac.geo_location_fk ,vac.job_job_fk ,vac.grd_grade_fk ,vac.pos_position_fk ,vac.rvac_vacncy_fk ,vac.adt_business_group_id ,vac.adt_vacancy_status_code ,vac.adt_budget_type_code ,vac.adt_vacancy_category_code ,vac.number_of_openings ,vac.budget_measurement_value

View Text - HTML Formatted

SELECT VAC.TIME_DAY_VAC_STRT_FK
, VAC.TIME_DAY_VAC_END_FK
, VAC.PER_PERSON_RECR_FK PER_PERSON_RCRT_FK
, VAC.PER_PERSON_RMGR_FK
, VAC.PER_PERSON_RSED_FK
, VAC.PER_PERSON_MRGD_FK
, VAC.ORG_ORGANZTN_FK
, VAC.ORG_ORGANZTN_MRGD_FK
, VAC.GEO_LOCATION_FK
, VAC.JOB_JOB_FK
, VAC.GRD_GRADE_FK
, VAC.POS_POSITION_FK
, VAC.RVAC_VACNCY_FK
, CASE WHEN VAC.ADT_VACANCY_STATUS_CODE = 'CLOSED'
AND VAC.TIME_DAY_VAC_END_FK <= TRUNC(SYSDATE) THEN 0 ELSE 1 END VAC_OPEN_CNT
, SUM(APL.INIT_APPL_STG_IND * APL.CURRENT_RECORD_IND) INIT_APPL_STG_CNT
, SUM(APL.ASMT_STG_IND * APL.CURRENT_RECORD_IND) ASMT_STG_CNT
, SUM(APL.OFFR_EXTD_STG_IND * APL.CURRENT_RECORD_IND) OFFR_EXTD_STG_CNT
, SUM(APL.STRT_PNDG_STG_IND * APL.CURRENT_RECORD_IND) STRT_PNDG_STG_CNT
, SUM(APL.HIRE_STG_IND * APL.CURRENT_RECORD_IND) HIRE_PRE_POW1_CNT
, SUM(SIGN(APL.INIT_APPL_STG_IND + APL.ASMT_STG_IND + APL.OFFR_EXTD_STG_IND + APL.STRT_PNDG_STG_IND) * APL.CURRENT_RECORD_IND) APPL_IN_PROGRESS_CNT
, SUM(APL.APPL_STRT_EVNT_IND) APPL_STRT_EVNT_CNT
, SUM(APL.APPL_STRT_NEVNT_IND * (1 - APL.ASMT_STRT_NEVNT_IND) * APL.APPL_TERM_EVNT_IND) APPL_TERM_INIT_EVNT_CNT
, SUM(APL.APPL_STRT_NEVNT_IND * (1 - APL.ASMT_STRT_NEVNT_IND) * APL.APPL_TERM_VOL_EVNT_IND) APPL_TERM_VOL_INIT_EVNT_CNT
, SUM(APL.APPL_STRT_NEVNT_IND * (1 - APL.ASMT_STRT_NEVNT_IND) * APL.APPL_TERM_INVOL_EVNT_IND) APPL_TERM_INVOL_INIT_EVNT_CNT
, SUM(APL.ASMT_STRT_EVNT_IND) ASMT_STRT_EVNT_CNT
, SUM(APL.ASMT_STRT_NEVNT_IND * (1 - APL.OFFR_EXTD_NEVNT_IND) * APL.APPL_TERM_EVNT_IND) APPL_TERM_ASMT_EVNT_CNT
, SUM(APL.ASMT_STRT_NEVNT_IND * (1 - APL.OFFR_EXTD_NEVNT_IND) * APL.APPL_TERM_VOL_EVNT_IND) APPL_TERM_VOL_ASMT_EVNT_CNT
, SUM(APL.ASMT_STRT_NEVNT_IND * (1 - APL.OFFR_EXTD_NEVNT_IND) * APL.APPL_TERM_INVOL_EVNT_IND) APPL_TERM_INVOL_ASMT_EVNT_CNT
, SUM(APL.OFFR_EXTD_EVNT_IND) OFFR_EXTD_EVNT_CNT
, SUM(APL.OFFR_RJCT_EVNT_IND) APPL_TERM_OFFR_EVNT_CNT
, SUM(APL.OFFR_RJCT_EVNT_IND * APL.APPL_TERM_VOL_EVNT_IND) APPL_TERM_VOL_OFFR_EVNT_CNT
, SUM(APL.OFFR_RJCT_EVNT_IND * APL.APPL_TERM_INVOL_EVNT_IND) APPL_TERM_INVOL_OFFR_EVNT_CNT
, SUM(APL.OFFR_ACPT_EVNT_IND) OFFR_ACPT_EVNT_CNT
, SUM(APL.OFFR_ACPT_NEVNT_IND * (1 - APL.APPL_HIRE_NEVNT_IND) * APL.APPL_TERM_EVNT_IND) APPL_TERM_ACPT_EVNT_CNT
, SUM(APL.OFFR_ACPT_NEVNT_IND * (1 - APL.APPL_HIRE_NEVNT_IND) * APL.APPL_TERM_VOL_EVNT_IND) APPL_TERM_VOL_ACPT_EVNT_CNT
, SUM(APL.OFFR_ACPT_NEVNT_IND * (1 - APL.APPL_HIRE_NEVNT_IND) * APL.APPL_TERM_INVOL_EVNT_IND) APPL_TERM_INVOL_ACPT_EVNT_CNT
, SUM(APL.APPL_TERM_EVNT_IND) APPL_TERM_EVNT_CNT
, SUM(APL.APPL_TERM_VOL_EVNT_IND) APPL_TERM_VOL_EVNT_CNT
, SUM(APL.APPL_TERM_INVOL_EVNT_IND) APPL_TERM_INVOL_EVNT_CNT
, SUM(APL.APPL_HIRE_EVNT_IND) HIRE_APPL_EVNT_CNT
, SUM(APL.POST_HIRE_POW1_END_EVNT_IND) POST_HIRE_POW1_END_EVNT_CNT
, SUM(APL.EMP_SPRTN_EVNT_IND) PRE_POW1_END_SPRTN_EVNT_CNT
, SUM((APL.APPL_STRT_DATE - VAC.VAC_STRT_DATE) * APL.APPL_STRT_EVNT_IND) VAC_TO_APPL_STRT_EVNT_DAYS
, SUM((APL.ASMT_STRT_DATE - VAC.VAC_STRT_DATE) * APL.ASMT_STRT_EVNT_IND) VAC_TO_ASMT_STRT_EVNT_DAYS
, SUM((APL.OFFR_EXTD_DATE - VAC.VAC_STRT_DATE) * APL.OFFR_EXTD_EVNT_IND) VAC_TO_OFFR_EXTD_EVNT_DAYS
, SUM((APL.OFFR_ACPT_DATE - VAC.VAC_STRT_DATE) * APL.OFFR_ACPT_EVNT_IND) VAC_TO_OFFR_ACPT_EVNT_DAYS
, SUM((APL.OFFR_ACPT_DATE - VAC.VAC_STRT_DATE) * APL.APPL_HIRE_EVNT_IND) VAC_TO_FILL_EVNT_DAYS
, SUM((APL.HIRE_DATE - VAC.VAC_STRT_DATE) * APL.APPL_HIRE_EVNT_IND) VAC_TO_APPL_HIRE_EVNT_DAYS
, SUM((APL.HIRE_DATE - APL.OFFR_ACPT_DATE) * APL.APPL_HIRE_EVNT_IND) FILL_TO_APPL_HIRE_EVNT_DAYS
, SUM((APL.OFFR_RJCT_DATE - VAC.VAC_STRT_DATE) * APL.OFFR_RJCT_EVNT_IND) VAC_TO_TERM_OFFR_EVNT_DAYS
, SUM((APL.APPL_TERM_DATE - VAC.VAC_STRT_DATE) * APL.APPL_TERM_EVNT_IND) VAC_TO_APPL_TERM_EVNT_DAYS
, SUM((APL.ASMT_STRT_DATE - APL.APPL_STRT_DATE) * APL.ASMT_STRT_EVNT_IND) APPL_TO_ASMT_STRT_EVNT_DAYS
, SUM((APL.OFFR_EXTD_DATE - APL.APPL_STRT_DATE) * APL.OFFR_EXTD_EVNT_IND) APPL_TO_OFFR_EXTD_EVNT_DAYS
, SUM((APL.OFFR_ACPT_DATE - APL.APPL_STRT_DATE) * APL.OFFR_ACPT_EVNT_IND) APPL_TO_OFFR_ACPT_EVNT_DAYS
, SUM((APL.OFFR_ACPT_DATE - APL.APPL_STRT_DATE) * APL.APPL_HIRE_EVNT_IND) APPL_TO_FILL_EVNT_DAYS
, SUM((APL.HIRE_DATE - APL.APPL_STRT_DATE) * APL.APPL_HIRE_EVNT_IND) APPL_TO_APPL_HIRE_EVNT_DAYS
, SUM((APL.OFFR_RJCT_DATE - APL.APPL_STRT_DATE) * APL.OFFR_RJCT_EVNT_IND) APPL_TO_TERM_OFFR_EVNT_DAYS
, SUM((APL.APPL_TERM_DATE - APL.APPL_STRT_DATE) * APL.APPL_TERM_EVNT_IND) APPL_TO_APPL_TERM_EVNT_DAYS
, SUM((APL.OFFR_RJCT_DATE - APL.OFFR_EXTD_DATE) * APL.APPL_TERM_VOL_EVNT_IND) OFFR_TO_TERM_VOL_EVNT_DAYS
, SUM((APL.OFFR_RJCT_DATE - APL.OFFR_EXTD_DATE) * APL.APPL_TERM_INVOL_EVNT_IND) OFFR_TO_TERM_INVOL_EVNT_DAYS
, SUM((APL.OFFR_ACPT_DATE - APL.OFFR_EXTD_DATE) * APL.OFFR_ACPT_EVNT_IND) OFFR_TO_ACPT_EVNT_DAYS
, SUM((APL.APPL_TERM_DATE - APL.OFFR_ACPT_DATE) * APL.APPL_TERM_VOL_EVNT_IND) ACPT_TO_TERM_VOL_EVNT_DAYS
, SUM((APL.APPL_TERM_DATE - APL.OFFR_ACPT_DATE) * APL.APPL_TERM_INVOL_EVNT_IND) ACPT_TO_TERM_INVOL_EVNT_DAYS
, VAC.NUMBER_OF_OPENINGS TOTAL_OPENINGS
, SUM(APL.APPL_HIRE_EVNT_IND) FILLED_OPENINGS
, CASE WHEN VAC.ADT_BUDGET_TYPE_CODE = 'HEAD' THEN VAC.BUDGET_MEASUREMENT_VALUE ELSE TO_NUMBER(NULL) END TOTAL_HEADCOUNT
, SUM(CASE WHEN VAC.ADT_BUDGET_TYPE_CODE = 'HEAD' THEN APL.APPL_HIRE_EVNT_IND * NVL(APL.HEADCOUNT
, 0) ELSE TO_NUMBER(NULL) END) FILLED_HEADCOUNT
, CASE WHEN VAC.ADT_BUDGET_TYPE_CODE = 'FTE' THEN VAC.BUDGET_MEASUREMENT_VALUE ELSE TO_NUMBER(NULL) END TOTAL_FTE
, SUM(CASE WHEN VAC.ADT_BUDGET_TYPE_CODE = 'FTE' THEN APL.APPL_HIRE_EVNT_IND * NVL(APL.FTE
, 0) ELSE TO_NUMBER(NULL) END) FILLED_FTE
, CASE WHEN VAC.ADT_BUDGET_TYPE_CODE = 'MONEY' THEN VAC.BUDGET_MEASUREMENT_VALUE ELSE TO_NUMBER(NULL) END TOTAL_MONEY
, TO_NUMBER(NULL) FILLED_MONEY
, CASE WHEN VAC.NUMBER_OF_OPENINGS <= SUM(APL.APPL_HIRE_EVNT_IND) THEN 1 ELSE 0 END VAC_FILLED_IND
FROM HRI_MB_REC_CAND_PIPLN_CT APL
, HRI_MB_REC_VACANCY_CT VAC
WHERE APL.RVAC_VACNCY_FK = VAC.RVAC_VACNCY_FK GROUP BY VAC.TIME_DAY_VAC_STRT_FK
, VAC.TIME_DAY_VAC_END_FK
, VAC.PER_PERSON_RECR_FK
, VAC.PER_PERSON_RMGR_FK
, VAC.PER_PERSON_RSED_FK
, VAC.PER_PERSON_MRGD_FK
, VAC.ORG_ORGANZTN_FK
, VAC.ORG_ORGANZTN_MRGD_FK
, VAC.GEO_LOCATION_FK
, VAC.JOB_JOB_FK
, VAC.GRD_GRADE_FK
, VAC.POS_POSITION_FK
, VAC.RVAC_VACNCY_FK
, VAC.ADT_BUSINESS_GROUP_ID
, VAC.ADT_VACANCY_STATUS_CODE
, VAC.ADT_BUDGET_TYPE_CODE
, VAC.ADT_VACANCY_CATEGORY_CODE
, VAC.NUMBER_OF_OPENINGS
, VAC.BUDGET_MEASUREMENT_VALUE