VIEW: APPS.HRI_OBI_MDS_WFABS_V

Source

View Text - Preformatted

SELECT abs.absence_sk_fk abs_absence_fk ,abs.effective_date time_day_fk ,abs.abs_person_id per_person_fk ,wevt.per_person_mgr_fk ,wevt.mgr_mngrsc_fk ,wevt.org_organztn_fk ,wevt.job_job_fk ,wevt.grd_grade_fk ,wevt.pos_position_fk ,wevt.geo_location_fk ,wevt.ptyp_pertyp_fk ,wevt.prfm_perfband_fk ,wevt.pow_powband_fk ,wevt.headcount ,wevt.fte ,abs.abs_drtn_days ,abs.abs_drtn_hrs ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN abs.abs_drtn_days ELSE 0 END ABS_DRTN_DAYS_PLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN 0 ELSE abs.abs_drtn_days END ABS_DRTN_DAYS_UNPLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN abs.abs_drtn_hrs ELSE 0 END ABS_DRTN_HRS_PLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN 0 ELSE abs.abs_drtn_hrs END ABS_DRTN_HRS_UNPLND ,abs.abs_start_ind ,abs.abs_end_ind ,abs.abs_blnc_ind ,abs.abs_start_ind abs_start_blnc ,(1 - abs.abs_start_ind) abs_nstart_blnc ,abs.abs_ntfctn_days_blnc ,abs.abs_ntfctn_days_blnc * abs.abs_start_ind abs_ntfctn_days_start_blnc ,abs.abs_ntfctn_days_blnc * (1 - abs.abs_start_ind) abs_ntfctn_days_nstart_blnc ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN abs.abs_start_ind ELSE 0 END ABS_STRT_BLNC_PLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN 0 ELSE abs.abs_start_ind END ABS_STRT_BLNC_UNPLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN (1 - abs.abs_start_ind) ELSE 0 END ABS_NSTRT_BLNC_PLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN 0 ELSE (1 - abs.abs_start_ind) END ABS_NSTRT_BLNC_UNPLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN abs.abs_start_ind * abs.abs_ntfctn_days_blnc ELSE 0 END ABS_NTFCTN_START_BLNC_PLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN 0 ELSE abs.abs_start_ind * abs.abs_ntfctn_days_blnc END ABS_NTFCTN_START_BLNC_UNPLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN (1 - abs.abs_start_ind) * abs.abs_ntfctn_days_blnc ELSE 0 END ABS_NTFCTN_NSTART_BLNC_PLND ,CASE WHEN abs.abs_ntfctn_days_blnc > 0 THEN 0 ELSE (1 - abs.abs_start_ind) * abs.abs_ntfctn_days_blnc END ABS_NTFCTN_NSTART_BLNC_UNPLND FROM hri_mb_utl_absnc_ct abs ,hri_mb_wrkfc_evt_ct wevt WHERE wevt.per_person_fk = abs.abs_person_id AND abs.effective_date BETWEEN wevt.time_day_evt_fk AND wevt.time_day_evt_end_fk WITH READ ONLY

View Text - HTML Formatted

SELECT ABS.ABSENCE_SK_FK ABS_ABSENCE_FK
, ABS.EFFECTIVE_DATE TIME_DAY_FK
, ABS.ABS_PERSON_ID PER_PERSON_FK
, WEVT.PER_PERSON_MGR_FK
, WEVT.MGR_MNGRSC_FK
, WEVT.ORG_ORGANZTN_FK
, WEVT.JOB_JOB_FK
, WEVT.GRD_GRADE_FK
, WEVT.POS_POSITION_FK
, WEVT.GEO_LOCATION_FK
, WEVT.PTYP_PERTYP_FK
, WEVT.PRFM_PERFBAND_FK
, WEVT.POW_POWBAND_FK
, WEVT.HEADCOUNT
, WEVT.FTE
, ABS.ABS_DRTN_DAYS
, ABS.ABS_DRTN_HRS
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN ABS.ABS_DRTN_DAYS ELSE 0 END ABS_DRTN_DAYS_PLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN 0 ELSE ABS.ABS_DRTN_DAYS END ABS_DRTN_DAYS_UNPLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN ABS.ABS_DRTN_HRS ELSE 0 END ABS_DRTN_HRS_PLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN 0 ELSE ABS.ABS_DRTN_HRS END ABS_DRTN_HRS_UNPLND
, ABS.ABS_START_IND
, ABS.ABS_END_IND
, ABS.ABS_BLNC_IND
, ABS.ABS_START_IND ABS_START_BLNC
, (1 - ABS.ABS_START_IND) ABS_NSTART_BLNC
, ABS.ABS_NTFCTN_DAYS_BLNC
, ABS.ABS_NTFCTN_DAYS_BLNC * ABS.ABS_START_IND ABS_NTFCTN_DAYS_START_BLNC
, ABS.ABS_NTFCTN_DAYS_BLNC * (1 - ABS.ABS_START_IND) ABS_NTFCTN_DAYS_NSTART_BLNC
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN ABS.ABS_START_IND ELSE 0 END ABS_STRT_BLNC_PLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN 0 ELSE ABS.ABS_START_IND END ABS_STRT_BLNC_UNPLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN (1 - ABS.ABS_START_IND) ELSE 0 END ABS_NSTRT_BLNC_PLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN 0 ELSE (1 - ABS.ABS_START_IND) END ABS_NSTRT_BLNC_UNPLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN ABS.ABS_START_IND * ABS.ABS_NTFCTN_DAYS_BLNC ELSE 0 END ABS_NTFCTN_START_BLNC_PLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN 0 ELSE ABS.ABS_START_IND * ABS.ABS_NTFCTN_DAYS_BLNC END ABS_NTFCTN_START_BLNC_UNPLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN (1 - ABS.ABS_START_IND) * ABS.ABS_NTFCTN_DAYS_BLNC ELSE 0 END ABS_NTFCTN_NSTART_BLNC_PLND
, CASE WHEN ABS.ABS_NTFCTN_DAYS_BLNC > 0 THEN 0 ELSE (1 - ABS.ABS_START_IND) * ABS.ABS_NTFCTN_DAYS_BLNC END ABS_NTFCTN_NSTART_BLNC_UNPLND
FROM HRI_MB_UTL_ABSNC_CT ABS
, HRI_MB_WRKFC_EVT_CT WEVT
WHERE WEVT.PER_PERSON_FK = ABS.ABS_PERSON_ID
AND ABS.EFFECTIVE_DATE BETWEEN WEVT.TIME_DAY_EVT_FK
AND WEVT.TIME_DAY_EVT_END_FK WITH READ ONLY