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_JOB_MV

Source


SELECT /* 12.0: bug#4526784 */ 
 tab.supervisor_person_id
,tab.effective_start_date
,tab.effective_end_date
,tab.job_fmly_code
,tab.job_fnctn_code
,tab.job_id
,tab.wkth_wktyp_sk_fk
,tab.anl_slry_currency
,tab.total_anl_slry
,tab.total_headcount
,tab.direct_anl_slry
,tab.direct_headcount
,tab.anl_slry_adjust
,tab.headcount_adjust
,tab.dr_anl_slry_adjust
,tab.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.job_fmly_code,
                        dim_dlt.job_fnctn_code,
                        dim_dlt.job_id,
                        dim_dlt.anl_slry_currency
           ORDER BY dim_dlt.effective_date)) - 1,
     to_date('31-12-4712','dd-mm-yyyy'))       effective_end_date
  ,dim_dlt.job_fmly_code
  ,dim_dlt.job_fnctn_code
  ,dim_dlt.job_id
  ,dim_dlt.wkth_wktyp_sk_fk
  ,dim_dlt.anl_slry_currency       anl_slry_currency
  ,SUM(dim_dlt.anl_slry_adjust) OVER
        (PARTITION BY dim_dlt.supervisor_person_id,
                      dim_dlt.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.job_id,
                      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.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.job_id,
                      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.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.job_id,
                      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.job_fmly_code,
                      dim_dlt.job_fnctn_code,
                      dim_dlt.job_id,
                      dim_dlt.anl_slry_currency
         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.job_fmly_code             job_fmly_code
    ,dlt.job_fnctn_code            job_fnctn_code
    ,dlt.job_id                    job_id
    ,dlt.wkth_wktyp_sk_fk          wkth_wktyp_sk_fk
    ,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
    WHERE dlt.wkth_wktyp_code = 'EMP'
    GROUP BY
     dlt.supervisor_person_id
    ,dlt.effective_date
    ,dlt.job_fmly_code
    ,dlt.job_fnctn_code
    ,dlt.job_id
    ,dlt.wkth_wktyp_sk_fk
    ,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