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_MDP_SUP_WRKFC_RGN_MV

Source


SELECT /* 12.0: bug#4526784 */ 
 tab.supervisor_person_id
,tab.effective_start_date
,tab.effective_end_date
,tab.geo_country_code
,tab.geo_region_code
,tab.anl_slry_currency
,tab.total_anl_slry
,tab.total_headcount
,tab.direct_anl_slry
,tab.direct_headcount
FROM
 (SELECT
   dim_dlt.supervisor_person_id    supervisor_person_id
  ,dim_dlt.effective_date          effective_start_date
  ,NVL((LEAD(dim_dlt.effective_date, 1) OVER
          (PARTITION BY dim_dlt.supervisor_person_id,
                        dim_dlt.geo_country_code,
                        dim_dlt.geo_region_code,
                        dim_dlt.anl_slry_currency
           ORDER BY dim_dlt.effective_date)) - 1,
     hr_general.end_of_time)       effective_end_date
  ,dim_dlt.geo_country_code
  ,dim_dlt.geo_region_code
  ,dim_dlt.anl_slry_currency       anl_slry_currency
  ,SUM(dim_dlt.anl_slry_adjust) OVER
        (PARTITION BY dim_dlt.supervisor_person_id,
                      dim_dlt.geo_country_code,
                      dim_dlt.geo_region_code,
                      dim_dlt.anl_slry_currency
         ORDER BY dim_dlt.effective_date
         RANGE UNBOUNDED PRECEDING)
                                AS total_anl_slry
  ,SUM(dim_dlt.headcount_adjust) OVER
        (PARTITION BY dim_dlt.supervisor_person_id,
                      dim_dlt.geo_country_code,
                      dim_dlt.geo_region_code,
                      dim_dlt.anl_slry_currency
         ORDER BY dim_dlt.effective_date
         RANGE UNBOUNDED PRECEDING)
                                AS total_headcount
  ,SUM(dim_dlt.dr_anl_slry_adjust) OVER
        (PARTITION BY dim_dlt.supervisor_person_id,
                      dim_dlt.geo_country_code,
                      dim_dlt.geo_region_code,
                      dim_dlt.anl_slry_currency
         ORDER BY dim_dlt.effective_date
         RANGE UNBOUNDED PRECEDING)
                                AS direct_anl_slry
  ,SUM(dim_dlt.dr_headcount_adjust) OVER
        (PARTITION BY dim_dlt.supervisor_person_id,
                      dim_dlt.geo_country_code,
                      dim_dlt.geo_region_code,
                      dim_dlt.anl_slry_currency
         ORDER BY dim_dlt.effective_date
         RANGE UNBOUNDED PRECEDING)
                                AS direct_headcount
  FROM
   (SELECT
     dlt.supervisor_person_id      supervisor_person_id
    ,dlt.effective_date            effective_date
    ,dlt.geo_country_code          geo_country_code
    ,dlt.geo_region_code           geo_region_code
    ,dlt.anl_slry_currency         anl_slry_currency
    ,SUM(dlt.anl_slry_adjust)      anl_slry_adjust
    ,SUM(dlt.headcount_adjust)     headcount_adjust
    ,SUM(dr_anl_slry_adjust)       dr_anl_slry_adjust
    ,SUM(dr_headcount_adjust)      dr_headcount_adjust
    FROM hri_map_sup_wrkfc  dlt
    GROUP BY
     dlt.supervisor_person_id
    ,dlt.effective_date
    ,dlt.geo_country_code
    ,dlt.geo_region_code
    ,dlt.anl_slry_currency)    dim_dlt
  WHERE (dim_dlt.anl_slry_adjust <> 0
     OR  dim_dlt.headcount_adjust <> 0
     OR  dim_dlt.dr_anl_slry_adjust <> 0
     OR  dim_dlt.dr_headcount_adjust <> 0)
 )                       tab