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.
[Home] [Help]
MATERIALIZED VIEW: APPS.HRI_MDP_SUP_WRKFC_CJER_MV

Source


SELECT /* 12.0: bug#4526784 */ 
 tab1.supervisor_person_id
,tab1.effective_start_date
,tab1.effective_end_date
,tab1.anl_slry_currency
,tab1.geo_area_code
,tab1.geo_country_code
,tab1.job_fmly_code
,tab1.job_fnctn_code
,tab1.pow_band_sk_fk
,tab1.perf_band
,tab1.primary_job_role_code
,tab1.wkth_wktyp_sk_fk
,tab1.wkth_wktyp_code
,tab1.total_anl_slry
,tab1.total_headcount
,tab1.direct_anl_slry
,tab1.direct_headcount
,tab1.anl_slry_adjust
,tab1.headcount_adjust
,tab1.dr_anl_slry_adjust
,tab1.dr_headcount_adjust
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.anl_slry_currency,
                        dim_dlt.geo_area_code,
                        dim_dlt.geo_country_code,
                        dim_dlt.job_fmly_code,
                        dim_dlt.job_fnctn_code,
                        dim_dlt.pow_band_sk_fk,
                        dim_dlt.perf_band,
                        dim_dlt.wkth_wktyp_sk_fk,
                      dim_dlt.primary_job_role_code
           ORDER BY dim_dlt.effective_date)) - 1,
     to_date('31-12-4712','dd-mm-yyyy'))       effective_end_date
 ,dim_dlt.anl_slry_currency       anl_slry_currency
 ,dim_dlt.geo_area_code           geo_area_code
 ,dim_dlt.geo_country_code        geo_country_code
 ,dim_dlt.job_fmly_code           job_fmly_code
 ,dim_dlt.job_fnctn_code          job_fnctn_code
 ,dim_dlt.pow_band_sk_fk          pow_band_sk_fk
 ,dim_dlt.perf_band               perf_band
 ,dim_dlt.wkth_wktyp_sk_fk        wkth_wktyp_sk_fk
 ,dim_dlt.wkth_wktyp_code         wkth_wktyp_code
 ,dim_dlt.primary_job_role_code   primary_job_role_code
 ,SUM(dim_dlt.anl_slry_adjust) OVER
        (PARTITION BY dim_dlt.supervisor_person_id,
                      dim_dlt.anl_slry_currency,
                      dim_dlt.geo_area_code,
                      dim_dlt.geo_country_code,
                      dim_dlt.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.pow_band_sk_fk,
                      dim_dlt.perf_band,
                      dim_dlt.wkth_wktyp_sk_fk,
                      dim_dlt.primary_job_role_code
         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.anl_slry_currency,
                      dim_dlt.geo_area_code,
                      dim_dlt.geo_country_code,
                      dim_dlt.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.pow_band_sk_fk,
                      dim_dlt.perf_band,
                      dim_dlt.wkth_wktyp_sk_fk,
                      dim_dlt.primary_job_role_code
         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.anl_slry_currency,
                      dim_dlt.geo_area_code,
                      dim_dlt.geo_country_code,
                      dim_dlt.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.pow_band_sk_fk,
                      dim_dlt.perf_band,
                      dim_dlt.wkth_wktyp_sk_fk,
                      dim_dlt.primary_job_role_code
         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.anl_slry_currency,
                      dim_dlt.geo_area_code,
                      dim_dlt.geo_country_code,
                      dim_dlt.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.pow_band_sk_fk,
                      dim_dlt.perf_band,
                      dim_dlt.wkth_wktyp_sk_fk,
                      dim_dlt.primary_job_role_code
         ORDER BY dim_dlt.effective_date
         RANGE UNBOUNDED PRECEDING)
                                AS direct_headcount
 ,dim_dlt.anl_slry_adjust
 ,dim_dlt.headcount_adjust
 ,dim_dlt.dr_anl_slry_adjust
 ,dim_dlt.dr_headcount_adjust
 FROM
   (SELECT
     dlt.supervisor_person_id                 supervisor_person_id
    ,dlt.effective_date                       effective_date
    ,dlt.anl_slry_currency                    anl_slry_currency
    ,dlt.geo_area_code                        geo_area_code
    ,dlt.geo_country_code                     geo_country_code
    ,dlt.job_fmly_code                        job_fmly_code
    ,dlt.job_fnctn_code                       job_fnctn_code
    ,dlt.pow_band_sk_fk                       pow_band_sk_fk
    ,dlt.perf_band                            perf_band
    ,dlt.primary_job_role_code                primary_job_role_code
    ,dlt.wkth_wktyp_sk_fk                     wkth_wktyp_sk_fk
    ,dlt.wkth_wktyp_code                      wkth_wktyp_code
    ,SUM(dlt.anl_slry_adjust)                 anl_slry_adjust
    ,SUM(dlt.headcount_adjust)                headcount_adjust
    ,SUM(dlt.dr_anl_slry_adjust)              dr_anl_slry_adjust
    ,SUM(dlt.dr_headcount_adjust)             dr_headcount_adjust
    FROM hri_map_sup_wrkfc     dlt
    GROUP BY
     dlt.supervisor_person_id
    ,dlt.effective_date
    ,dlt.anl_slry_currency
    ,dlt.job_fmly_code
    ,dlt.job_fnctn_code
    ,dlt.geo_area_code
    ,dlt.geo_country_code
    ,dlt.primary_job_role_code
    ,dlt.wkth_wktyp_sk_fk
    ,dlt.wkth_wktyp_code
    ,dlt.pow_band_sk_fk
    ,dlt.perf_band)    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)
 )                       tab1