MATERIALIZED VIEW: APPS.HRI_MDS_SUP_WCNT_TERM_CTR_MV

Source

SELECT /*+ LEADING(snr) */ /* 12.0: bug#4526784 */ 
 mv.supervisor_person_id,
 cal.effective_end_date  effective_date,
 cal.period_type,
 cal.comparison_type,
 mv.wkth_wktyp_sk_fk,
 mv.direct_ind,
 mv.geo_area_code,
 mv.geo_country_code,
 SUM(mv.effective_date - mv.pow_start_date)  pow_days,
 SUM(mv.pow_months)  pow_months,
 SUM(mv.separation_hdc)  separation_hdc,
 SUM(CASE WHEN mv.separation_category = 'SEP_INV'
          THEN mv.separation_hdc
          ELSE 0
     END)                 sep_invol_hdc,
 SUM(CASE WHEN mv.separation_category = 'SEP_VOL'
          THEN mv.separation_hdc
          ELSE 0
     END)                 sep_vol_hdc
FROM
 hri_cl_per_snap_prds_ct snr,
 hri_cal_snpsht_wcnt_chg cal,
 hri_mdp_sup_wcnt_term_asg_mv mv
WHERE mv.supervisor_person_id = snr.id
AND mv.effective_date BETWEEN cal.effective_start_date
                      AND     cal.effective_end_date
AND cal.snapshot_date BETWEEN snr.snapshot_start_date
                      AND     snr.snapshot_end_date
AND snr.senior_manager_flag = 'Y'
GROUP BY
 mv.supervisor_person_id,
 cal.effective_end_date,
 cal.period_type,
 cal.comparison_type,
 mv.wkth_wktyp_sk_fk,
 mv.direct_ind,
 mv.geo_area_code,
 mv.geo_country_code