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_WRKFC_CTR_MV

Source


SELECT /*+ USE_HASH(curr_start comp_start comp_end) */ /* 12.0: bug#4526784 */ 
 tab.supervisor_person_id
,tab.curr_end_date                   effective_date
,tab.period_type
,tab.comparison_type
,tab.geo_area_code
,tab.geo_country_code
,tab.wkth_wktyp_sk_fk
,tab.anl_slry_currency
,tab.total_anl_slry                  curr_total_anl_slry_end
,tab.total_headcount                 curr_total_hdc_end
,tab.direct_anl_slry                 curr_direct_anl_slry_end
,tab.direct_headcount                curr_direct_hdc_end
,NVL(comp_end.total_anl_slry , 0)    comp_total_anl_slry_end
,NVL(comp_end.total_headcount, 0)    comp_total_hdc_end
,NVL(comp_end.direct_anl_slry, 0)    comp_direct_anl_slry_end
,NVL(comp_end.direct_headcount, 0)   comp_direct_hdc_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
FROM
 hri_mdp_sup_wrkfc_ctr_mv comp_end
,hri_mdp_sup_wrkfc_ctr_mv curr_start
,hri_mdp_sup_wrkfc_ctr_mv comp_start
,(SELECT /*+ LEADING(snr) */
   curr_end.supervisor_person_id
  ,cal.curr_start_date
  ,cal.curr_end_date
  ,cal.comp_start_date
  ,cal.comp_end_date
  ,cal.period_type
  ,cal.comparison_type
  ,curr_end.geo_area_code
  ,curr_end.geo_country_code
  ,curr_end.wkth_wktyp_sk_fk
  ,curr_end.anl_slry_currency
  ,curr_end.total_anl_slry
  ,curr_end.total_headcount
  ,curr_end.direct_anl_slry
  ,curr_end.direct_headcount
  FROM
   hri_cl_per_snap_prds_ct snr
  ,hri_cal_snpsht_wrkfc cal
  ,hri_mdp_sup_wrkfc_ctr_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 snr.senior_manager_flag = 'Y'
)  tab
WHERE 1=1
AND tab.supervisor_person_id = curr_start.supervisor_person_id (+)
AND tab.geo_country_code = curr_start.geo_country_code (+)
AND tab.anl_slry_currency = curr_start.anl_slry_currency (+)
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.geo_country_code = comp_end.geo_country_code (+)
AND tab.anl_slry_currency = comp_end.anl_slry_currency (+)
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.geo_country_code = comp_start.geo_country_code (+)
AND tab.anl_slry_currency = comp_start.anl_slry_currency (+)
AND tab.comp_start_date - 1 BETWEEN comp_start.effective_start_date (+)
                            AND     comp_start.effective_end_date (+)