APPS.HRI_OLTP_PMV_WMV_TRN_CTR SQL Statements

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

Line: 75

'SELECT -- Terminations by Top 4 Country Trend
 qry.period_as_of_date     VIEWBYID
,qry.period_as_of_date     VIEWBY
,qry.period_order          HRI_P_ORDER_BY_1
,qry.period_sep_hdc_ctr1   HRI_P_MEASURE1
,DECODE(qry.period_sep_hdc_ctr1,
   0, 0,
 qry.period_sep_hdc_ctr1 * :ANL_FACTOR * 100 /
 DECODE(qry.period_hdc_trn_ctr1,
          0, qry.period_sep_hdc_ctr1,
        qry.period_hdc_trn_ctr1))  HRI_P_MEASURE1_MP
,qry.period_sep_hdc_ctr2   HRI_P_MEASURE2
,DECODE(qry.period_sep_hdc_ctr2,
   0, 0,
 qry.period_sep_hdc_ctr2 * :ANL_FACTOR * 100 /
 DECODE(qry.period_hdc_trn_ctr2,
          0, qry.period_sep_hdc_ctr2,
        qry.period_hdc_trn_ctr2))  HRI_P_MEASURE2_MP
,qry.period_sep_hdc_ctr3   HRI_P_MEASURE3
,DECODE(qry.period_sep_hdc_ctr3,
   0, 0,
 qry.period_sep_hdc_ctr3 * :ANL_FACTOR * 100 /
 DECODE(qry.period_hdc_trn_ctr3,
          0, qry.period_sep_hdc_ctr3,
        qry.period_hdc_trn_ctr3))  HRI_P_MEASURE3_MP
,qry.period_sep_hdc_ctr4   HRI_P_MEASURE4
,DECODE(qry.period_sep_hdc_ctr4,
   0, 0,
 qry.period_sep_hdc_ctr4 * :ANL_FACTOR * 100 /
 DECODE(qry.period_hdc_trn_ctr4,
          0, qry.period_sep_hdc_ctr4,
        qry.period_hdc_trn_ctr4))  HRI_P_MEASURE4_MP
,to_char(qry.period_as_of_date,''DD/MM/YYYY'')          HRI_P_CHAR2_GA
FROM
 (' || l_trend_sql || ')  qry
WHERE 1=1
' || l_security_clause || '
ORDER BY
  period_order';

Line: 201

/* Headcount for turnover calc method selected */
  l_hdc_trn_col_curr        VARCHAR(250);

Line: 277

'SELECT
 -- Annualized Turnover By Top x Countries
 grp.vby_id               VIEWBYID
,DECODE(grp.vby_id, ''NA_OTHERS'', ''' || l_others_string || ''',
        vby.value)        VIEWBY
,DECODE(grp.vby_id, ''NA_OTHERS'', ''' || l_others_string || ''',
        vby.value)        HRI_P_CHAR1_GA '|| g_rtn ||
/* Title - Headcount */
',grp.curr_hdc_end         HRI_P_MEASURE1 '|| g_rtn ||
/* Title - Voluntary  */
',grp.curr_trn_vol         HRI_P_MEASURE2
,:HRI_ANL_FACTOR * 100 * grp.curr_trn_vol / grp.curr_trn_div
                          HRI_P_MEASURE3 '|| g_rtn ||
/* Title -  InVoluntary */
',grp.curr_trn_inv         HRI_P_MEASURE4
,:HRI_ANL_FACTOR * 100 * grp.curr_trn_inv / grp.curr_trn_div
                          HRI_P_MEASURE5 '|| g_rtn ||
/* Title - Total Terms */
',grp.curr_trn_tot         HRI_P_MEASURE6
,:HRI_ANL_FACTOR * 100 * grp.curr_trn_tot / grp.curr_trn_div
                          HRI_P_MEASURE7 '|| g_rtn ||
/* Title - Grand Total Headcount */
',grp.total_curr_hdc_end   HRI_P_GRAND_TOTAL1 '|| g_rtn ||
/* Title - Grand Total VOL */
',grp.total_curr_trn_vol   HRI_P_GRAND_TOTAL2
,:HRI_ANL_FACTOR * 100 * grp.total_curr_trn_vol / grp.total_curr_trn_div
                          HRI_P_GRAND_TOTAL3 '|| g_rtn ||
/* Title - Grand Total INVOL */
',grp.total_curr_trn_inv   HRI_P_GRAND_TOTAL4
,:HRI_ANL_FACTOR * 100 * grp.total_curr_trn_inv / grp.total_curr_trn_div
                          HRI_P_GRAND_TOTAL5 '|| g_rtn ||
/* Title - Grand Total TOTAL TERMS */
',grp.total_curr_trn_tot   HRI_P_GRAND_TOTAL6
,:HRI_ANL_FACTOR * 100 * grp.total_curr_trn_tot / grp.total_curr_trn_div
                          HRI_P_GRAND_TOTAL7
,'''||l_drill_url||'''
                          HRI_P_DRILL_URL1
FROM
 hri_dbi_cl_geo_country_v  vby,
 (SELECT' || g_rtn ||
/* Bug 4068969 - added country_code and fixed order_by and vby_id */
'   DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - a.rnk),
            -1, :HRI_NO_COUNTRIES_TO_SHOW + 1,
          a.rnk)       order_by
  ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - a.rnk),
            -1, ''NA_OTHERS'',
          a.vby_id)            vby_id
  ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - a.rnk),
            -1, ''NA_EDW'',
          a.vby_id)            country_code
  ,SUM(a.curr_hdc_end)         curr_hdc_end
  ,SUM(a.total_curr_hdc_end)   total_curr_hdc_end
  ,SUM(a.curr_trn_vol)         curr_trn_vol
  ,SUM(a.comp_trn_vol)         comp_trn_vol
  ,SUM(a.total_curr_trn_vol)   total_curr_trn_vol
  ,SUM(a.total_comp_trn_vol)   total_comp_trn_vol
  ,SUM(a.curr_trn_inv)         curr_trn_inv
  ,SUM(a.comp_trn_inv)         comp_trn_inv
  ,SUM(a.total_curr_trn_inv)   total_curr_trn_inv
  ,SUM(a.total_comp_trn_inv)   total_comp_trn_inv
  ,SUM(a.curr_trn_tot)         curr_trn_tot
  ,SUM(a.comp_trn_tot)         comp_trn_tot
  ,SUM(a.total_curr_trn_tot)   total_curr_trn_tot
  ,SUM(a.total_comp_trn_tot)   total_comp_trn_tot
  ,SUM(a.curr_trn_div)         curr_trn_div
  ,SUM(a.comp_trn_div)         comp_trn_div
  ,SUM(a.total_curr_trn_div)   total_curr_trn_div
  ,SUM(a.total_comp_trn_div)   total_comp_trn_div
  FROM
   (SELECT
     tots.*' || g_rtn ||
/* Bug 4068969 - Ensured ranking function is unique */
'    ,RANK() OVER (ORDER BY
      tots.curr_hdc_end DESC NULLS LAST,
      tots.vby_id)   rnk ' || g_rtn ||
/* Terminations Factor */'
    ,DECODE(tots.curr_hdc_trn,
       0, DECODE(tots.curr_trn_tot, 0 , 1, tots.curr_trn_tot),
     tots.curr_hdc_trn)         curr_trn_div
    ,DECODE(tots.comp_hdc_trn,
       0, DECODE(tots.comp_trn_tot, 0 , 1, tots.comp_trn_tot),
     tots.comp_hdc_trn)         comp_trn_div
    ,:HRI_ANL_FACTOR            anl_factor ' || g_rtn ||
/* Grand Totals - Terminations */ '
    ,DECODE(tots.total_curr_hdc_trn,
       0, DECODE(tots.total_curr_trn_tot, 0 , 1, tots.total_curr_trn_tot),
     tots.total_curr_hdc_trn)   total_curr_trn_div
    ,DECODE(tots.total_comp_hdc_trn,
       0, DECODE(tots.total_comp_trn_tot, 0 , 1, tots.total_comp_trn_tot),
     tots.total_comp_hdc_trn)   total_comp_trn_div
    FROM
     (SELECT
/* View by */
       wmv.vby_id ' || g_rtn ||
/* Headcount */'
      ,wmv.curr_hdc_end
      ,wmv.comp_hdc_end
      ,DECODE(wmv.comp_hdc_end,
         0, 0,
       100 * (wmv.curr_hdc_end - wmv.comp_hdc_end) /
       wmv.comp_hdc_end)  hdc_change_pct ' || g_rtn ||
/* Headcount for turnover calculation */ '
      ,' || l_hdc_trn_col_curr || '       curr_hdc_trn
      ,' || l_hdc_trn_col_comp || '       comp_hdc_trn' || g_rtn ||
/* Turnover */'
      ,NVL(trn.curr_sep_vol_hdc, 0)  curr_trn_vol
      ,NVL(trn.curr_sep_invol_hdc, 0)  curr_trn_inv
      ,NVL(trn.curr_separation_hdc, 0)  curr_trn_tot
      ,NVL(trn.comp_sep_vol_hdc, 0)  comp_trn_vol
      ,NVL(trn.comp_sep_invol_hdc, 0)  comp_trn_inv
      ,NVL(trn.comp_separation_hdc, 0)  comp_trn_tot ' || g_rtn ||
/* Grand Totals - Headcount */ '
      ,SUM(wmv.curr_hdc_end) OVER ()  total_curr_hdc_end
      ,SUM(wmv.comp_hdc_end) OVER ()  total_comp_hdc_end ' || g_rtn ||
/* Grand Totals - Headcount for turnover calculation */ '
       ,SUM(' || l_hdc_trn_col_curr || ') OVER ()  total_curr_hdc_trn
       ,SUM(' || l_hdc_trn_col_comp || ') OVER ()  total_comp_hdc_trn ' || g_rtn ||
/* Grand Totals - Turnover */'
      ,NVL(SUM(trn.curr_sep_vol_hdc) OVER (), 0)  total_curr_trn_vol
      ,NVL(SUM(trn.curr_sep_invol_hdc) OVER (), 0)  total_curr_trn_inv
      ,NVL(SUM(trn.curr_separation_hdc) OVER (), 0)  total_curr_trn_tot
      ,NVL(SUM(trn.comp_sep_vol_hdc) OVER (), 0)  total_comp_trn_vol
      ,NVL(SUM(trn.comp_sep_invol_hdc) OVER (), 0)  total_comp_trn_inv
      ,NVL(SUM(trn.comp_separation_hdc) OVER (), 0)  total_comp_trn_tot
      FROM
         ( ' || l_wcnt_chg_fact_sql || ' ) trn' || g_rtn
     || ',( ' || l_wrkfc_fact_sql    || ' ) wmv' || g_rtn
     || 'WHERE wmv.vby_id = trn.vby_id (+)
     ) tots
   ) a
  WHERE 1 = 1
  AND (a.curr_hdc_end > 0
    OR a.curr_trn_vol > 0
    OR a.curr_trn_inv > 0
    OR a.comp_trn_vol > 0
    OR a.comp_trn_inv > 0)
  GROUP BY
   DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - a.rnk),
            -1, :HRI_NO_COUNTRIES_TO_SHOW + 1,
          a.rnk)
  ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
            -1, ''NA_OTHERS'',
          a.vby_id)
  ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
            -1, ''NA_EDW'',
          a.vby_id)
 ) grp
WHERE grp.country_code = vby.id
' || l_security_clause || '
ORDER BY grp.order_by';

Line: 430

/* Binds Will be inserted Below */

  x_custom_sql := l_SQLText;