[Home] [Help]
MATERIALIZED VIEW: APPS.HRI_MDP_SUP_ABSNC_CAT_MV
Source
SELECT
fact.supervisor_person_id
,fact.effective_date
,fact.absence_category_code
,0 direct_record_ind
,SUM(fact.abs_drtn_days) abs_drtn_days
,SUM(fact.abs_drtn_hrs) abs_drtn_hrs
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_drtn_days
ELSE 0
END) abs_drtn_days_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_drtn_days
END) abs_drtn_days_unplnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_drtn_hrs
ELSE 0
END) abs_drtn_hrs_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_drtn_hrs
END) abs_drtn_hrs_unplnd
,SUM(fact.abs_start_blnc) abs_start_blnc
,SUM(fact.abs_nstart_blnc) abs_nstart_blnc
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_start_blnc
ELSE 0
END) abs_start_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_start_blnc
END) abs_start_blnc_unplnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_nstart_blnc
ELSE 0
END) abs_nstart_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_nstart_blnc
END) abs_nstart_blnc_unplnd
,SUM(fact.abs_ntfctn_days_start_blnc) abs_ntfctn_days_start_blnc
,SUM(fact.abs_ntfctn_days_nstart_blnc) abs_ntfctn_days_nstart_blnc
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_ntfctn_days_start_blnc
ELSE 0
END) abs_ntfctn_start_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_ntfctn_days_start_blnc
END) abs_ntfctn_start_blnc_unplnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_ntfctn_days_nstart_blnc
ELSE 0
END) abs_ntfctn_nstart_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_ntfctn_days_nstart_blnc
END) abs_ntfctn_nstart_blnc_unplnd
FROM
hri_mdp_sup_absnc_occ_ct fact
GROUP BY
fact.supervisor_person_id
,fact.effective_date
,fact.absence_category_code
UNION ALL
SELECT
fact.supervisor_person_id
,fact.effective_date
,fact.absence_category_code
,1 direct_record_ind
,SUM(fact.abs_drtn_days) abs_drtn_days
,SUM(fact.abs_drtn_hrs) abs_drtn_hrs
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_drtn_days
ELSE 0
END) abs_drtn_days_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_drtn_days
END) abs_drtn_days_unplnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_drtn_hrs
ELSE 0
END) abs_drtn_hrs_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_drtn_hrs
END) abs_drtn_hrs_unplnd
,SUM(fact.abs_start_blnc) abs_start_blnc
,SUM(fact.abs_nstart_blnc) abs_nstart_blnc
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_start_blnc
ELSE 0
END) abs_start_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_start_blnc
END) abs_start_blnc_unplnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_nstart_blnc
ELSE 0
END) abs_nstart_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_nstart_blnc
END) abs_nstart_blnc_unplnd
,SUM(fact.abs_ntfctn_days_start_blnc) abs_ntfctn_days_start_blnc
,SUM(fact.abs_ntfctn_days_nstart_blnc) abs_ntfctn_days_nstart_blnc
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_ntfctn_days_start_blnc
ELSE 0
END) abs_ntfctn_start_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_ntfctn_days_start_blnc
END) abs_ntfctn_start_blnc_unplnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN fact.abs_ntfctn_days_nstart_blnc
ELSE 0
END) abs_ntfctn_nstart_blnc_plnd
,SUM(CASE WHEN fact.abs_ntfctn_days_start_blnc > 0 OR
fact.abs_ntfctn_days_nstart_blnc > 0
THEN 0
ELSE fact.abs_ntfctn_days_nstart_blnc
END) abs_ntfctn_nstart_blnc_unplnd
FROM
hri_mdp_sup_absnc_occ_ct fact
WHERE fact.direct_ind = 1
GROUP BY
fact.supervisor_person_id
,fact.effective_date
,fact.absence_category_code