VIEW: APPS.HRI_MD_REC_VAC_CLSD_STTS_V

Source

View Text - Preformatted

SELECT 1 row_indicator , vac.number_of_openings openings , vac.budget_measurement_value openings_wmv , COUNT( wge.assignment_to_id ) openings_filled , COUNT( DECODE(wge.emp_indicator,1,wge.assignment_to_id, NULL)) openings_filled_emp , COUNT( DECODE(wge.cwk_indicator,1,wge.assignment_to_id, NULL)) openings_filled_cwk , SUM(HRI_OLTP_VIEW_WRKFRC.calc_abv( wge.assignment_to_id, wge.business_group_to_id, vac.budget_measurement_type_code, wge.measure_calc_date)) openings_filled_wmv , SUM(DECODE(emp_indicator,1, HRI_OLTP_VIEW_WRKFRC.calc_abv( wge.assignment_to_id, wge.business_group_to_id, vac.budget_measurement_type_code, wge.measure_calc_date),0)) openings_filled_wmv_emp , SUM(DECODE(cwk_indicator,1, HRI_OLTP_VIEW_WRKFRC.calc_abv( wge.assignment_to_id, wge.business_group_to_id, vac.budget_measurement_type_code, wge.measure_calc_date),0)) openings_filled_wmv_cwk , vac.date_from vacancy_open_date , NVL( vac.date_to , hr_general.end_of_time ) vacancy_close_date , vac.business_group_id vacancy_business_group_id , vac.organization_id vacancy_organization_id , vac.location_id vacancy_location_id , vac.job_id vacancy_job_id , vac.grade_id vacancy_grade_id , vac.position_id vacancy_position_id , vac.vacancy_id vacancy_id , vac.vacancy_category_code vacancy_category_code , vac.vacancy_status_code vacancy_status_code , vac.budget_measurement_type_code vacancy_wmt_code , MAX(GREATEST( wge.last_change_date , vac.last_updated)) last_change_date , CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD( 'PER_ALL_VACANCIES' , vac.organization_id , vac.position_id , vac.manager_id , vac.security_method_code ) = 'TRUE' ) THEN 'Y' ELSE 'N' END hr_security_flag_code , CASE WHEN vac.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,vac.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code FROM hri_mb_vacancy_v vac , hri_mb_wac_gain_entrprs_v wge WHERE vac.vacancy_status_code IN ('CLOSED','CLO') AND wge.vacancy_id (+) = vac.vacancy_id AND wge.gain_hire_indicator (+) = 1 GROUP BY vac.budget_measurement_type_code , vac.budget_measurement_value , vac.business_group_id , vac.date_from , vac.date_to , vac.grade_id , vac.job_id , vac.last_updated , vac.location_id , vac.number_of_openings , vac.organization_id , vac.manager_id , vac.security_method_code , vac.position_id , vac.vacancy_category_code , vac.vacancy_id , vac.vacancy_status_code WITH READ ONLY

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, VAC.NUMBER_OF_OPENINGS OPENINGS
, VAC.BUDGET_MEASUREMENT_VALUE OPENINGS_WMV
, COUNT( WGE.ASSIGNMENT_TO_ID ) OPENINGS_FILLED
, COUNT( DECODE(WGE.EMP_INDICATOR
, 1
, WGE.ASSIGNMENT_TO_ID
, NULL)) OPENINGS_FILLED_EMP
, COUNT( DECODE(WGE.CWK_INDICATOR
, 1
, WGE.ASSIGNMENT_TO_ID
, NULL)) OPENINGS_FILLED_CWK
, SUM(HRI_OLTP_VIEW_WRKFRC.CALC_ABV( WGE.ASSIGNMENT_TO_ID
, WGE.BUSINESS_GROUP_TO_ID
, VAC.BUDGET_MEASUREMENT_TYPE_CODE
, WGE.MEASURE_CALC_DATE)) OPENINGS_FILLED_WMV
, SUM(DECODE(EMP_INDICATOR
, 1
, HRI_OLTP_VIEW_WRKFRC.CALC_ABV( WGE.ASSIGNMENT_TO_ID
, WGE.BUSINESS_GROUP_TO_ID
, VAC.BUDGET_MEASUREMENT_TYPE_CODE
, WGE.MEASURE_CALC_DATE)
, 0)) OPENINGS_FILLED_WMV_EMP
, SUM(DECODE(CWK_INDICATOR
, 1
, HRI_OLTP_VIEW_WRKFRC.CALC_ABV( WGE.ASSIGNMENT_TO_ID
, WGE.BUSINESS_GROUP_TO_ID
, VAC.BUDGET_MEASUREMENT_TYPE_CODE
, WGE.MEASURE_CALC_DATE)
, 0)) OPENINGS_FILLED_WMV_CWK
, VAC.DATE_FROM VACANCY_OPEN_DATE
, NVL( VAC.DATE_TO
, HR_GENERAL.END_OF_TIME ) VACANCY_CLOSE_DATE
, VAC.BUSINESS_GROUP_ID VACANCY_BUSINESS_GROUP_ID
, VAC.ORGANIZATION_ID VACANCY_ORGANIZATION_ID
, VAC.LOCATION_ID VACANCY_LOCATION_ID
, VAC.JOB_ID VACANCY_JOB_ID
, VAC.GRADE_ID VACANCY_GRADE_ID
, VAC.POSITION_ID VACANCY_POSITION_ID
, VAC.VACANCY_ID VACANCY_ID
, VAC.VACANCY_CATEGORY_CODE VACANCY_CATEGORY_CODE
, VAC.VACANCY_STATUS_CODE VACANCY_STATUS_CODE
, VAC.BUDGET_MEASUREMENT_TYPE_CODE VACANCY_WMT_CODE
, MAX(GREATEST( WGE.LAST_CHANGE_DATE
, VAC.LAST_UPDATED)) LAST_CHANGE_DATE
, CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD( 'PER_ALL_VACANCIES'
, VAC.ORGANIZATION_ID
, VAC.POSITION_ID
, VAC.MANAGER_ID
, VAC.SECURITY_METHOD_CODE ) = 'TRUE' ) THEN 'Y' ELSE 'N' END HR_SECURITY_FLAG_CODE
, CASE WHEN VAC.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, VAC.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM HRI_MB_VACANCY_V VAC
, HRI_MB_WAC_GAIN_ENTRPRS_V WGE
WHERE VAC.VACANCY_STATUS_CODE IN ('CLOSED'
, 'CLO')
AND WGE.VACANCY_ID (+) = VAC.VACANCY_ID
AND WGE.GAIN_HIRE_INDICATOR (+) = 1 GROUP BY VAC.BUDGET_MEASUREMENT_TYPE_CODE
, VAC.BUDGET_MEASUREMENT_VALUE
, VAC.BUSINESS_GROUP_ID
, VAC.DATE_FROM
, VAC.DATE_TO
, VAC.GRADE_ID
, VAC.JOB_ID
, VAC.LAST_UPDATED
, VAC.LOCATION_ID
, VAC.NUMBER_OF_OPENINGS
, VAC.ORGANIZATION_ID
, VAC.MANAGER_ID
, VAC.SECURITY_METHOD_CODE
, VAC.POSITION_ID
, VAC.VACANCY_CATEGORY_CODE
, VAC.VACANCY_ID
, VAC.VACANCY_STATUS_CODE WITH READ ONLY