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.

APPS.HRI_OLTP_PMV_WMV_SAL_SUP_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 77


SELECT
 tab.order_by         HRI_P_ORDER_BY_1
,ctr.value            HRI_P_MEASURE1
,tab.wmv_curr         HRI_P_WMV_SUM_MV
,tab.wmv_prev         HRI_P_WMV_SUM_PREV_MV
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.wmv_prev',
        p_current_col  => 'tab.wmv_curr') || '
                                HRI_P_WMV_CHNG_PCT_SUM_MV
,tab.sal_curr         HRI_P_MEASURE2
,tab.sal_prev         HRI_P_MEASURE3
,tab.avg_sal          HRI_P_MEASURE4
,NVL(tab.avg_sal_prev,0)        HRI_P_MEASURE5
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.avg_sal_prev',
        p_current_col  => 'tab.avg_sal') || '
                                HRI_P_MEASURE6
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col =>
'DECODE(tab.tot_wmv_prev, 0, to_number(null), tab.tot_sal_prev / tab.tot_wmv_prev)',
        p_current_col  =>
'DECODE(tab.tot_wmv_curr, 0, to_number(null), tab.tot_sal_curr / tab.tot_wmv_curr)') || '
                      HRI_P_GRAND_TOTAL1
,DECODE(tab.tot_wmv_curr,
          0, to_number(null),
        tab.tot_sal_curr/tab.tot_wmv_curr)
                      HRI_P_GRAND_TOTAL2
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.tot_wmv_prev',
        p_current_col  => 'tab.tot_wmv_curr') || '
                      HRI_P_GRAND_TOTAL3
,tab.country_code     HRI_P_GEO_CTY_CN
,'''||l_drill_url||'''
                      HRI_P_CHAR1_GA
FROM
 hri_dbi_cl_geo_country_v   ctr
,(SELECT
   bc.vby_id              country_code
  ,-bc.curr_hdc_end       order_by
  ,bc.curr_hdc_end        wmv_curr
  ,bc.comp_hdc_end        wmv_prev
  ,bc.curr_sal_end        sal_curr
  ,DECODE(bc.curr_hdc_end,
            0, to_number(null),
          bc.curr_sal_end / bc.curr_hdc_end)
                          avg_sal
  ,bc.comp_sal_end        sal_prev
  ,DECODE(bc.comp_hdc_end,
            0, to_number(null),
          bc.comp_sal_end / bc.comp_hdc_end)
                          avg_sal_prev
  ,SUM(bc.curr_hdc_end) OVER ()  tot_wmv_curr
  ,SUM(bc.curr_sal_end) OVER ()  tot_sal_curr
  ,SUM(bc.comp_total_hdc_end) OVER ()  tot_wmv_prev
  ,SUM(bc.comp_total_sal_end) OVER ()  tot_sal_prev
  FROM
   (' || l_wrkfc_fact_sql || ')   bc
  WHERE (bc.curr_hdc_end > 0
      OR bc.comp_hdc_end > 0
      OR bc.curr_sal_end > 0
      OR bc.comp_sal_end > 0)
 ) tab
WHERE tab.country_code = ctr.id
' || l_security_clause || '
ORDER BY
 HRI_P_ORDER_BY_1
,HRI_P_MEASURE4
,HRI_P_MEASURE1';

Line: 233


SELECT
 tab.order_by       HRI_P_ORDER_BY_1
,rgn.value          HRI_P_MEASURE1
,tab.wmv_curr       HRI_P_WMV_SUM_MV
,tab.wmv_prev       HRI_P_WMV_SUM_PREV_MV
,tab.sal_curr       HRI_P_MEASURE2
,tab.sal_prev       HRI_P_MEASURE3
,tab.avg_sal        HRI_P_MEASURE4 ' || g_rtn ||
/* Test whether tot_wmv_prev or tot_sal_prev is zero */
',DECODE(DECODE(tab.tot_wmv_prev, 0, 0,
                  tab.tot_sal_prev / tab.tot_wmv_prev), ' || g_rtn ||
/* If either is zero then return 0 if the total salary is zero otherwise 100 */
'              0, DECODE(tab.tot_sal_curr, 0, 0, 100), ' || g_rtn ||
/* Otherwise if tot_sal_prev <> 0 and tot_wmv_prev <> 0 */
'            ((DECODE(tab.tot_wmv_curr, 0, 0,
                      (tab.tot_sal_curr / tab.tot_wmv_curr)
                     ) -
               (tab.tot_sal_prev/tab.tot_wmv_prev)
              ) /
              (tab.tot_sal_prev/tab.tot_wmv_prev)
             ) * 100)   HRI_P_GRAND_TOTAL1
,DECODE(tab.tot_wmv_curr,
          0, tab.tot_sal_curr,
        tab.tot_sal_curr / tab.tot_wmv_curr)
                        HRI_P_GRAND_TOTAL2
,DECODE(tab.tot_wmv_prev, 0, 100,
        (tab.tot_wmv_curr - tab.tot_wmv_prev) * 100 / tab.tot_wmv_prev)
                        HRI_P_GRAND_TOTAL3
,tab.region_code        HRI_P_GEO_REG_CN
,tab.country_code       HRI_P_CHAR2_GA
,'''||l_drill_url||'''
                    HRI_P_CHAR1_GA
FROM
 hri_dbi_cl_geo_region_v  rgn
,(SELECT
   bc.vby_id              region_code
  ,:HRI_COUNTRY_CODE      country_code
  ,-bc.curr_hdc_end       order_by
  ,bc.curr_hdc_end        wmv_curr
  ,bc.comp_hdc_end        wmv_prev
  ,bc.curr_sal_end        sal_curr
  ,DECODE(bc.curr_hdc_end,
            0, bc.curr_sal_end,
          bc.curr_sal_end / bc.curr_hdc_end)
                          avg_sal
  ,bc.comp_sal_end        sal_prev
  ,SUM(bc.curr_hdc_end) OVER ()  tot_wmv_curr
  ,SUM(bc.curr_sal_end) OVER ()  tot_sal_curr
  ,SUM(bc.comp_total_hdc_end) OVER ()  tot_wmv_prev
  ,SUM(bc.comp_total_sal_end) OVER ()  tot_sal_prev
  FROM
   (' || l_wrkfc_fact_sql || ')   bc
  WHERE (bc.curr_hdc_end > 0
      OR bc.comp_hdc_end > 0
      OR bc.curr_sal_end > 0
      OR bc.comp_sal_end > 0)
 ) tab
WHERE tab.region_code = rgn.id
' || l_security_clause || '
ORDER BY
 HRI_P_ORDER_BY_1
,HRI_P_MEASURE4
,HRI_P_MEASURE1';

Line: 404


SELECT
 tab.order_by           HRI_P_ORDER_BY_1
,cty.value              HRI_P_MEASURE1
,tab.wmv_curr,0)        HRI_P_WMV_SUM_MV
,tab.wmv_prev,0)        HRI_P_WMV_SUM_PREV_MV
,tab.sal_curr,0)        HRI_P_MEASURE2
,tab.sal_prev,0)        HRI_P_MEASURE3
,tab.avg_sal            HRI_P_MEASURE4 ' || g_rtn ||
/* Test whether tot_wmv_prev or tot_sal_prev is zero */
',DECODE(DECODE(tab.tot_wmv_prev, 0, 0,
                tab.tot_sal_prev / tab.tot_wmv_prev), ' || g_rtn ||
/* If either is zero then return 0 if the total salary is zero otherwise 100 */
'              0, DECODE(tab.tot_sal_curr, 0, 0, 100), ' || g_rtn ||
/* Otherwise if tot_sal_prev <> 0 and tot_wmv_prev <> 0 */
'            ((DECODE(tab.tot_wmv_curr, 0, 0,
                      (tab.tot_sal_curr / tab.tot_wmv_curr)
                     ) -
               (tab.tot_sal_prev / tab.tot_wmv_prev)
              ) /
              (tab.tot_sal_prev / tab.tot_wmv_prev)
             ) * 100)   HRI_P_GRAND_TOTAL1
,DECODE(tab.tot_wmv_curr,
          0, tab.tot_sal_curr,
        tab.tot_sal_curr/tab.tot_wmv_curr)
                        HRI_P_GRAND_TOTAL2
,DECODE(tab.tot_wmv_prev, 0, 100,
        ((tab.tot_wmv_curr - tab.tot_wmv_prev) * 100 / tab.tot_wmv_prev)
                        HRI_P_GRAND_TOTAL3
,tab.country_code       HRI_P_CHAR4_GA
,tab.region_code        HRI_P_CHAR3_GA
,cty.id                 HRI_P_GEO_CIT_CN
,'''||l_drill_url||'''  HRI_P_CHAR1_GA
FROM(
 hri_dbi_cl_geo_city_v  cty
,(SELECT
   bc.vby_id              city_cid
  ,:HRI_COUNTRY_CODE      country_code
  ,:HRI_REGION_CODE       region_code
  ,-bc.curr_hdc_end       order_by
  ,bc.curr_hdc_end        wmv_curr
  ,bc.comp_hdc_end        wmv_prev
  ,bc.curr_sal_end        sal_curr
  ,DECODE(bc.curr_hdc_end,
            0, bc.curr_sal_end,
          bc.curr_sal_end / bc.curr_hdc_end)
                          avg_sal
  ,bc.comp_sal_end        sal_prev
  ,SUM(bc.curr_hdc_end) OVER ()  tot_wmv_curr
  ,SUM(bc.curr_sal_end) OVER ()  tot_sal_curr
  ,SUM(bc.comp_total_hdc_end) OVER ()  tot_wmv_prev
  ,SUM(bc.comp_total_sal_end) OVER ()  tot_sal_prev
  FROM
   (' || l_wrkfc_fact_sql || ')   bc
  WHERE (bc.curr_hdc_end > 0
      OR bc.comp_hdc_end > 0
      OR bc.curr_sal_end > 0
      OR bc.comp_sal_end > 0)
 ) tab
WHERE tab.city_cid = cty.id
' || l_security_clause || '
ORDER BY
 HRI_P_ORDER_BY_1
,HRI_P_MEASURE4
,HRI_P_MEASURE1';

Line: 557


'SELECT -- Salary by Job Function Status
 tab.order_by            HRI_P_ORDER_BY_1
,jfn.value               HRI_P_JOB_FAMILY_CN
,tab.wmv_curr            HRI_P_WMV_SUM_MV
,tab.sal_curr            HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.sal_prev',
        p_current_col  => 'tab.sal_curr') || '
                         HRI_P_MEASURE5
,tab.avg_sal_curr        HRI_P_MEASURE1
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.avg_sal_prev',
        p_current_col  => 'tab.avg_sal_curr') || '
                         HRI_P_MEASURE2
,tab.avg_sal_prev        HRI_P_MEASURE3
,tab.tot_sal_prev        HRI_P_MEASURE4
,tab.tot_wmv_curr        HRI_P_GRAND_TOTAL1
,tab.tot_sal_curr        HRI_P_GRAND_TOTAL2
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.tot_sal_prev',
        p_current_col  => 'tab.tot_sal_curr') || '
                         HRI_P_GRAND_TOTAL7
,DECODE(tab.tot_wmv_curr, 0, to_number(null),
        tab.tot_sal_curr / tab.tot_wmv_curr)
                         HRI_P_GRAND_TOTAL3
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col =>
 'DECODE(tab.tot_wmv_prev, 0, 0, tab.tot_sal_prev/tab.tot_wmv_prev)',
        p_current_col  =>
'DECODE(SUM(tab.wmv_curr) over(), 0, to_number(null),
 (SUM(tab.sal_curr) over() / SUM(tab.wmv_curr) over()))') || '
                         HRI_P_GRAND_TOTAL4
,decode(tab.tot_wmv_prev,
          0, to_number(null),
        tab.tot_sal_prev/tab.tot_wmv_prev)
                         HRI_P_GRAND_TOTAL5
,tab.tot_sal_prev        HRI_P_GRAND_TOTAL6
,tab.job_fnctn_code      HRI_P_CHAR1_GA
FROM
 hri_cl_job_function_v  jfn
,(SELECT
   bc.vby_id              job_fnctn_code
  ,-bc.curr_hdc_end       order_by
  ,bc.curr_hdc_end        wmv_curr
  ,bc.comp_hdc_end        wmv_prev
  ,bc.curr_sal_end        sal_curr
  ,DECODE(bc.curr_hdc_end,
            0, to_number(null),
          bc.curr_sal_end / bc.curr_hdc_end)
                          avg_sal_curr
  ,DECODE(bc.comp_hdc_end,
            0, to_number(null),
          bc.comp_sal_end / bc.comp_hdc_end)
                          avg_sal_prev
  ,bc.comp_sal_end        sal_prev
  ,SUM(bc.curr_hdc_end) OVER ()  tot_wmv_curr
  ,SUM(bc.curr_sal_end) OVER ()  tot_sal_curr
  ,SUM(bc.comp_total_hdc_end) OVER ()  tot_wmv_prev
  ,SUM(bc.comp_total_sal_end) OVER ()  tot_sal_prev
  FROM
   (' || l_wrkfc_fact_sql || ')   bc
  WHERE (bc.curr_hdc_end > 0
      OR bc.comp_hdc_end > 0
      OR bc.curr_sal_end > 0
      OR bc.comp_sal_end > 0)
 ) tab
WHERE tab.job_fnctn_code = jfn.id
' || l_security_clause || '
&ORDER_BY_CLAUSE';

Line: 719


'SELECT -- Salary by Job Family Status
 tab.order_by         HRI_P_ORDER_BY_1
,jfm.value            HRI_P_JOB_LVL2_CN
,tab.wmv_curr         HRI_P_WMV_SUM_MV
,tab.sal_curr         HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.sal_prev',
        p_current_col  => 'tab.sal_curr') || '
                      HRI_P_MEASURE5
,tab.avg_sal_curr     HRI_P_MEASURE1
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.avg_sal_prev',
        p_current_col  => 'tab.avg_sal_curr') || '
                      HRI_P_MEASURE2
,tab.avg_sal_prev     HRI_P_MEASURE3
,tab.tot_sal_prev     HRI_P_MEASURE4
,tab.tot_wmv_curr     HRI_P_GRAND_TOTAL1
,tab.tot_sal_curr     HRI_P_GRAND_TOTAL2
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.tot_sal_prev',
        p_current_col  => 'tab.tot_sal_curr') || '
                      HRI_P_GRAND_TOTAL7
,DECODE(tab.tot_wmv_curr, 0, to_number(null),
        tab.tot_sal_curr / tab.tot_wmv_curr)
                      HRI_P_GRAND_TOTAL3
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col =>
 'DECODE(tab.tot_wmv_prev, 0, to_number(null), tab.tot_sal_prev/tab.tot_wmv_prev)',
        p_current_col  =>
'DECODE(SUM(tab.wmv_curr) over(), 0, to_number(null),
 (SUM(tab.sal_curr) over() / SUM(tab.wmv_curr) over()))') || '
                      HRI_P_GRAND_TOTAL4
,decode(tab.tot_wmv_prev,
          0, to_number(null),
        tab.tot_sal_prev/tab.tot_wmv_prev)
                      HRI_P_GRAND_TOTAL5
,tab.tot_sal_prev     HRI_P_GRAND_TOTAL6
,:HRI_JOB_FUNCTION    HRI_P_CHAR1_GA
,tab.job_fmly_code    HRI_P_CHAR2_GA
FROM
 hri_cl_job_family_v  jfm
,(SELECT
   bc.vby_id              job_fmly_code
  ,-bc.curr_hdc_end       order_by
  ,bc.curr_hdc_end        wmv_curr
  ,bc.comp_hdc_end        wmv_prev
  ,bc.curr_sal_end        sal_curr
  ,DECODE(bc.curr_hdc_end,
            0, to_number(null),
          bc.curr_sal_end / bc.curr_hdc_end)
                          avg_sal_curr
  ,DECODE(bc.comp_hdc_end,
            0, to_number(null),
          bc.comp_sal_end / bc.comp_hdc_end)
                          avg_sal_prev
  ,bc.comp_sal_end        sal_prev
  ,SUM(bc.curr_hdc_end) OVER ()  tot_wmv_curr
  ,SUM(bc.curr_sal_end) OVER ()  tot_sal_curr
  ,SUM(bc.comp_total_hdc_end) OVER ()  tot_wmv_prev
  ,SUM(bc.comp_total_sal_end) OVER ()  tot_sal_prev
  FROM
   (' || l_wrkfc_fact_sql || ')   bc
  WHERE (bc.curr_hdc_end > 0
      OR bc.comp_hdc_end > 0
      OR bc.curr_sal_end > 0
      OR bc.comp_sal_end > 0)
 ) tab
WHERE tab.job_fmly_code = jfm.id
' || l_security_clause || '
&ORDER_BY_CLAUSE';