eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.HRI_MDS_SUP_WMV_SUP_MV

Source


SELECT /*+ INDEX(comp_end HRI_MDP_SUP_WMV_SUP_MV_U1) INDEX(curr_start HRI_MDP_SUP_WMV_SUP_MV_U1) INDEX(comp_start HRI_MDP_SUP_WMV_SUP_MV_U1) */
 tab.supervisor_person_id
,tab.curr_end_date                   effective_date
,tab.wkth_wktyp_sk_fk
,tab.period_type
,tab.comparison_type
,tab.total_headcount                        curr_total_hdc_end
,tab.direct_headcount                       curr_direct_hdc_end
,tab.total_primary_asg_cnt                  curr_total_pasg_cnt_end
,tab.direct_primary_asg_cnt                 curr_direct_pasg_cnt_end
,NVL(comp_end.total_headcount, 0)           comp_total_hdc_end
,NVL(comp_end.direct_headcount, 0)          comp_direct_hdc_end
,NVL(comp_end.total_primary_asg_cnt,0)      comp_total_pasg_cnt_end
,NVL(comp_end.direct_primary_asg_cnt,0)     comp_direct_pasg_cnt_end
,NVL(curr_start.total_headcount, 0)         curr_total_hdc_start
,NVL(curr_start.direct_headcount, 0)        curr_direct_hdc_start
,NVL(comp_start.total_headcount, 0)         comp_total_hdc_start
,NVL(comp_start.direct_headcount, 0)        comp_direct_hdc_start
,tab.total_primary_asg_pow                  curr_total_pow_end
,tab.direct_primary_asg_pow                 curr_direct_pow_end
,NVL(comp_end.total_primary_asg_pow  + (comp_end.total_primary_asg_cnt *
                  (tab.comp_end_date - comp_end.effective_start_date)), 0)
                                            comp_total_pow_end
,NVL(comp_end.direct_primary_asg_pow + (comp_end.direct_primary_asg_cnt *
                  (tab.comp_end_date - comp_end.effective_start_date)), 0)
                                            comp_direct_pow_end
,tab.total_extn_asg_cnt                     curr_extn_asg_cnt_end
,tab.total_primary_extn_pow                 curr_total_pow_extn_end
,tab.direct_primary_extn_pow                curr_direct_pow_extn_end
,comp_end.total_extn_asg_cnt                comp_extn_asg_cnt_end
,NVL(comp_end.total_primary_extn_pow + (comp_end.total_extn_asg_cnt *
                  (tab.comp_end_date - comp_end.effective_start_date)), 0)
                                            comp_total_pow_extn_end
,NVL(comp_end.direct_primary_extn_pow, 0)   comp_direct_pow_extn_end
FROM
 hri_mdp_sup_wcnt_sup_mv comp_end
,hri_mdp_sup_wcnt_sup_mv curr_start
,hri_mdp_sup_wcnt_sup_mv comp_start
,(SELECT /*+ INDEX(curr_end HRI_MDP_SUP_WMV_SUP_MV_U1) USE_NL(snr cal curr_end) */
   curr_end.supervisor_person_id
  ,cal.curr_start_date
  ,cal.curr_end_date
  ,cal.comp_start_date
  ,cal.comp_end_date
  ,curr_end.wkth_wktyp_sk_fk
  ,cal.period_type
  ,cal.comparison_type
  ,curr_end.total_headcount
  ,curr_end.direct_headcount
  ,curr_end.total_primary_asg_cnt
  ,curr_end.direct_primary_asg_cnt
  ,curr_end.total_primary_asg_pow + (curr_end.total_primary_asg_cnt *
              (cal.curr_end_date - curr_end.effective_start_date))
            total_primary_asg_pow
  ,curr_end.direct_primary_asg_pow + (curr_end.direct_primary_asg_cnt *
              (cal.curr_end_date - curr_end.effective_start_date))
            direct_primary_asg_pow
  ,curr_end.total_extn_asg_cnt
  ,curr_end.total_primary_extn_pow + (curr_end.total_extn_asg_cnt *
              (cal.curr_end_date - curr_end.effective_start_date))
            total_primary_extn_pow
  ,curr_end.direct_primary_extn_pow
  FROM
   hri_cl_per_snap_prds_ct snr
  ,hri_cal_snpsht_wrkfc cal
  ,hri_mdp_sup_wcnt_sup_mv curr_end
  WHERE curr_end.supervisor_person_id = snr.id
  AND cal.curr_end_date BETWEEN curr_end.effective_start_date
                        AND     curr_end.effective_end_date
  AND cal.snapshot_date BETWEEN snr.snapshot_start_date
                        AND     snr.snapshot_end_date
  AND (cal.comparison_type <> 'TREND' OR snr.senior_manager_flag = 'Y')
)  tab
WHERE 1=1
AND tab.supervisor_person_id = curr_start.supervisor_person_id (+)
AND tab.wkth_wktyp_sk_fk = curr_start.wkth_wktyp_sk_fk (+)
AND tab.curr_start_date - 1 BETWEEN curr_start.effective_start_date (+)
                            AND     curr_start.effective_end_date (+)
AND tab.supervisor_person_id = comp_end.supervisor_person_id (+)
AND tab.wkth_wktyp_sk_fk = comp_end.wkth_wktyp_sk_fk (+)
AND tab.comp_end_date BETWEEN comp_end.effective_start_date (+)
                      AND     comp_end.effective_end_date (+)
AND tab.supervisor_person_id = comp_start.supervisor_person_id (+)
AND tab.wkth_wktyp_sk_fk = comp_start.wkth_wktyp_sk_fk (+)
AND tab.comp_start_date - 1 BETWEEN comp_start.effective_start_date (+)
                            AND     comp_start.effective_end_date (+)