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_TRN_SUP SQL Statements

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

Line: 147


'SELECT    -- Turnover Portlet
 a.id                VIEWBYID
,a.value             VIEWBY
,a.value             HRI_P_PER_SUP_LNAME_CN
,a.order_by          HRI_P_ORDER_BY_1
,DECODE(a.direct_ind,
   1, ''' || l_drill_url2 || ''',
 ''' || l_drill_url || ''') HRI_P_DRILL_URL1
,a.anl_factor * 100 * a.curr_term_vol_hdc / a.curr_trn_div
                   HRI_P_WMV_TRN_SEP_VOL_ANL_MV
,a.anl_factor * 100 * a.curr_term_invol_hdc / a.curr_trn_div
                   HRI_P_WMV_TRN_SEP_INV_ANL_MV
,a.anl_factor * 100 * a.curr_termination_hdc / a.curr_trn_div
                   HRI_P_WMV_TRN_ANL_SUM_MV
,a.anl_factor * 100 * a.comp_termination_hdc / a.comp_trn_div
                   HRI_P_WMV_TRN_ANL_SUM_PREV_MV
,a.anl_factor * 100 * a.comp_term_vol_hdc / a.comp_trn_div
                   HRI_P_MEASURE1
,a.anl_factor * 100 * a.comp_term_invol_hdc / a.comp_trn_div
                   HRI_P_MEASURE2
,a.anl_factor * 100 * (a.curr_termination_hdc / a.curr_trn_div -
                       a.comp_termination_hdc / a.comp_trn_div)
                   HRI_P_WMV_CHNG_PCT_SUM_MV' || g_rtn ||
/* Grand total of Annualized Current Period Voluntary turnover */
',a.anl_factor * 100 * a.total_curr_trn_vol / a.total_curr_trn_div
                   HRI_P_GRAND_TOTAL1' || g_rtn ||
/* Grand total of Annualized Current Period Involuntary turnover */
',a.anl_factor * 100 * a.total_curr_trn_inv / a.total_curr_trn_div
                   HRI_P_GRAND_TOTAL2' || g_rtn ||
/* Grand total of Annualized Current Period Total turnover */
',a.anl_factor * 100 * a.total_curr_trn_tot / a.total_curr_trn_div
                   HRI_P_GRAND_TOTAL3' || g_rtn ||
/* Grand total of Annualized Prior Period Total turnover */
',a.anl_factor * 100 * a.total_comp_trn_tot / a.total_comp_trn_div
                   HRI_P_GRAND_TOTAL4' || g_rtn ||
/* Grand total of Annualized Turnover Change Percentage */
',a.anl_factor * 100 * (a.total_curr_trn_tot / total_curr_trn_div -
                        a.total_comp_trn_tot / total_comp_trn_div)
                   HRI_P_GRAND_TOTAL5' || g_rtn ||
/* Grand total of Annualized Prior Period Voluntary turnover */
',a.anl_factor * 100 * a.total_comp_trn_vol / a.total_comp_trn_div
                   HRI_P_GRAND_TOTAL6' || g_rtn ||
/* Grand total of Annualized Prior Period Involuntary turnover */
',a.anl_factor * 100 * a.total_comp_trn_inv / a.total_comp_trn_div
                   HRI_P_GRAND_TOTAL7
FROM
(SELECT
  tots.* ' || g_rtn ||
/* Headcount change */
' ,DECODE(tots.comp_hdc_end,
    0, 0,
  100 * (tots.curr_hdc_end - tots.comp_hdc_end) / tots.comp_hdc_end)
      hdc_change_pct' || g_rtn ||
/* Terminations Factor */
' ,DECODE(tots.curr_hdc_trn,
    0, DECODE(tots.curr_termination_hdc, 0 , 1, tots.curr_termination_hdc),
  tots.curr_hdc_trn)  curr_trn_div
 ,DECODE(tots.comp_hdc_trn,
    0, DECODE(tots.comp_termination_hdc, 0 , 1, tots.comp_termination_hdc),
  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
   vby.id
  ,DECODE(wmv.direct_ind,
            1, ''' || l_direct_reports_string || ''',
          vby.value)  value
  ,to_char(wmv.direct_ind) || vby.order_by  order_by' || g_rtn ||
/* Indicators */
'  ,wmv.direct_ind' || g_rtn ||
/* Headcount */
'  ,wmv.curr_hdc_end
 ,wmv.comp_hdc_end' || g_rtn ||
/* Headcount for turnover calculation */
'  ,' || l_col_curr_trn_hdc || '  curr_hdc_trn
  ,'  || l_col_comp_trn_hdc || '  comp_hdc_trn' || g_rtn ||
/* Turnover */
'  ,NVL(trn.curr_sep_vol_hdc, 0)     curr_term_vol_hdc
  ,NVL(trn.curr_sep_invol_hdc, 0)   curr_term_invol_hdc
  ,NVL(trn.curr_separation_hdc, 0)  curr_termination_hdc
  ,NVL(trn.comp_sep_vol_hdc, 0)     comp_term_vol_hdc
  ,NVL(trn.comp_sep_invol_hdc, 0)   comp_term_invol_hdc
  ,NVL(trn.comp_separation_hdc, 0)  comp_termination_hdc' || g_rtn ||
/* Grand Totals - Headcount */
'  ,SUM(wmv.curr_hdc_end) OVER ()  curr_total_hdc_end
  ,SUM(wmv.comp_total_hdc_end) OVER ()  comp_total_hdc_end' || g_rtn ||
/* Grand Totals - Headcount for turnover calculation */
'  ,SUM(' || l_col_curr_tot_trn_hdc || ') OVER ()  total_curr_hdc_trn
  ,SUM('  || l_col_comp_tot_trn_hdc || ') OVER ()  total_comp_hdc_trn' || g_rtn ||
/* Grand Totals - Turnover */
'  ,:HRI_CURR_TERM_VOL                        total_curr_trn_vol
  ,:HRI_CURR_TERM_INVOL                      total_curr_trn_inv
  ,:HRI_CURR_TERM_INVOL + :HRI_CURR_TERM_VOL total_curr_trn_tot
  ,:HRI_COMP_TERM_VOL                        total_comp_trn_vol
  ,:HRI_COMP_TERM_INVOL                      total_comp_trn_inv
  ,:HRI_COMP_TERM_VOL + :HRI_COMP_TERM_INVOL total_comp_trn_tot
  FROM
   hri_dbi_cl_per_n_v  vby
  ,(' || l_wrkfc_fact_sql    || ')  wmv
  ,(' || l_wcnt_chg_fact_sql || ')  trn
  WHERE wmv.vby_id = trn.vby_id (+)
  AND wmv.vby_id = vby.id
  AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date
 ) tots
 WHERE (tots.curr_hdc_end > 0
     OR tots.comp_termination_hdc > 0
     OR tots.curr_termination_hdc > 0
     OR tots.direct_ind = 1)
) a
WHERE 1 = 1
' || l_security_clause || '
&ORDER_BY_CLAUSE';

Line: 332


/* selective drill across urls */
  l_drill_url1            VARCHAR2(300);

Line: 356


/* define the selective drill across urls */
  l_drill_url1 := 'pFunctionName=HRI_P_WMV_TRN_SUP_PVT&' ||
                  'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
                  'VIEW_BY_NAME=VIEW_BY_ID&' ||
                  'pParamIds=Y';

Line: 459


'SELECT  -- Turnover Status
 a.id                       VIEWBYID
,a.value                    VIEWBY ' || g_rtn ||
/* Order by default person sort name */
',a.order_by                HRI_P_ORDER_BY_1 ' || g_rtn ||
',a.value                   HRI_P_CHAR1_GA' || g_rtn ||
',DECODE(a.direct_ind,
           1, ''' || l_drill_url5 || ''',
         ''' || l_drill_url1 || ''') HRI_P_DRILL_URL1' || g_rtn ||
/* WMV value at current period start */
',a.curr_hdc_start          HRI_P_MEASURE1 ' || g_rtn ||
/* WMV value at current period end */
',a.curr_hdc_end            HRI_P_MEASURE2 ' || g_rtn ||
/* Voluntary separations */
',a.curr_term_vol_hdc       HRI_P_MEASURE3 ' || g_rtn ||
','''|| l_drill_url2 || ''' HRI_P_DRILL_URL2' || g_rtn ||
/* Annualized voluntary separations as a percentage of calculated WMV */
',a.anl_factor * 100 * a.curr_term_vol_hdc / a.curr_trn_div
                            HRI_P_MEASURE3_MP ' || g_rtn ||
/* Involuntary separations */
',a.curr_term_invol_hdc     HRI_P_MEASURE4 ' || g_rtn ||
','''|| l_drill_url3 || ''' HRI_P_DRILL_URL3' || g_rtn ||
/* Annualized involuntary separations as a percentage of calculated WMV */
',a.anl_factor * 100 * a.curr_term_invol_hdc / a.curr_trn_div
                            HRI_P_MEASURE4_MP ' || g_rtn ||
/* Total separations */
',a.curr_termination_hdc    HRI_P_MEASURE5 ' || g_rtn ||
','''|| l_drill_url4 || ''' HRI_P_DRILL_URL4' || g_rtn ||
/* Total annualized separations as a percentage of calculated WMV */
',a.anl_factor * 100 * a.curr_termination_hdc / a.curr_trn_div
                            HRI_P_MEASURE5_MP ' || g_rtn ||
/* Grand total of Start Headcount as of start date for a top supervisor_id */
',a.curr_total_hdc_start    HRI_P_GRAND_TOTAL1 ' || g_rtn ||
/* Grand total of End Headcount as of end date for a top supervisor_id */
',a.curr_total_hdc_end      HRI_P_GRAND_TOTAL2 ' || g_rtn ||
/* Grand total of Vol Headcount as of end date for a top supervisor_id  */
',a.total_curr_trn_vol      HRI_P_GRAND_TOTAL3 ' || g_rtn ||
/* Grand total of Vol Headcount Percent as of end date for a top supervisor_id  */
',a.anl_factor * 100 * a.total_curr_trn_vol / a.total_curr_trn_div
                            HRI_P_GRAND_TOTAL4 ' || g_rtn ||
/* Grand total of Invol Headcount as of end date for a top supervisor_id  */
',a.total_curr_trn_inv      HRI_P_GRAND_TOTAL5 ' || g_rtn ||
/* Grand total of Invol Headcount Percent as of end date for a top supervisor_id  */
',a.anl_factor * 100 * a.total_curr_trn_inv / a.total_curr_trn_div
                            HRI_P_GRAND_TOTAL6 ' || g_rtn ||
/* Grand total of vol and invol Headcount as of end date for a top supervisor_id  */
',a.total_curr_trn_tot      HRI_P_GRAND_TOTAL7 ' || g_rtn ||
/* Grand total of vol and Invol Headcount Percent as of end date for a top supervisor_id  */
',a.anl_factor * 100 * a.total_curr_trn_tot / a.total_curr_trn_div
                            HRI_P_GRAND_TOTAL8 ' || g_rtn ||
/* Whether the row is a rolled up supervisor (Y) or direct report (N) */
',DECODE(a.direct_ind,
           1, ''N'',
         '''')             HRI_P_SUPH_RO_CA
FROM
(SELECT
  tots.* ' || g_rtn ||
/* Terminations Factor */
' ,DECODE(tots.curr_hdc_trn,
    0, DECODE(tots.curr_termination_hdc, 0 , 1, tots.curr_termination_hdc),
  tots.curr_hdc_trn)  curr_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
 FROM
 (SELECT
   vby.id
  ,DECODE(wmv.direct_ind,
            1, ''' || l_direct_reports_string || ''',
          vby.value)  value
  ,to_char(wmv.direct_ind) || vby.order_by  order_by' || g_rtn ||
/* Indicators */
'  ,wmv.direct_ind' || g_rtn ||
/* Headcount */
'  ,wmv.curr_hdc_end
   ,wmv.curr_hdc_start' || g_rtn ||
/* Headcount for turnover calculation */
'  ,' || l_col_curr_trn_hdc || '  curr_hdc_trn' || g_rtn ||
/* Turnover */
'  ,NVL(trn.curr_sep_vol_hdc, 0)     curr_term_vol_hdc
  ,NVL(trn.curr_sep_invol_hdc, 0)   curr_term_invol_hdc
  ,NVL(trn.curr_separation_hdc, 0)  curr_termination_hdc' || g_rtn ||
/* Grand Totals - Headcount */
'  ,SUM(wmv.curr_hdc_end)   OVER ()  curr_total_hdc_end
  ,SUM(wmv.curr_total_hdc_start) OVER ()  curr_total_hdc_start' || g_rtn ||
/* Grand Totals - Headcount for turnover calculation */
'  ,SUM(' || l_col_curr_tot_trn_hdc || ') OVER ()  total_curr_hdc_trn' || g_rtn ||
/* Grand Totals - Turnover */
'  ,:HRI_CURR_TERM_VOL                        total_curr_trn_vol
  ,:HRI_CURR_TERM_INVOL                      total_curr_trn_inv
  ,:HRI_CURR_TERM_INVOL + :HRI_CURR_TERM_VOL total_curr_trn_tot
  FROM
   hri_dbi_cl_per_n_v  vby
  ,(' || l_wrkfc_fact_sql    || ')  wmv
  ,(' || l_wcnt_chg_fact_sql || ')  trn
  WHERE wmv.vby_id = trn.vby_id (+)
  AND wmv.vby_id = vby.id
  AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date
 ) tots
 WHERE (tots.curr_hdc_end > 0
     OR tots.curr_hdc_start > 0
     OR tots.curr_term_vol_hdc > 0
     OR tots.curr_term_invol_hdc > 0
     OR tots.direct_ind = 1)
) a
WHERE 1 = 1
' || l_security_clause || '
ORDER BY ' || l_parameter_rec.order_by;

Line: 771


'SELECT -- Terminations Status
 a.order_by        HRI_P_ORDER_BY_1
,a.vby_id          VIEWBYID
,DECODE(a.direct_ind , 0, ''Y'', ''N'')  DRILLPIVOTVB
,DECODE(a.direct_ind,
          1, ''' || l_vb_drill_mgr_dir || ''',
        ''' || l_vb_sup_drill_url || ''')  HRI_P_DRILL_URL2
,a.vby_value       VIEWBY
,a.curr_termination_hdc         HRI_P_MEASURE1
,DECODE(a.direct_ind,
          1, ''' || l_dir_drill_url || ''',
        ''' || l_sup_drill_url || ''')  HRI_P_DRILL_URL1
,a.comp_termination_hdc         HRI_P_MEASURE2
,DECODE(a.comp_termination_hdc,
          0, DECODE(a.curr_termination_hdc, 0, 0, 100),
        (a.curr_termination_hdc - a.comp_termination_hdc) * 100 /
        a.comp_termination_hdc)  HRI_P_MEASURE1_MP
,a.curr_total_term_hdc     HRI_P_GRAND_TOTAL1
,a.comp_total_term_hdc     HRI_P_GRAND_TOTAL2
,DECODE(a.comp_total_term_hdc,
          0, DECODE(a.curr_total_term_hdc, 0, 0, 100),
        (a.curr_total_term_hdc - a.comp_total_term_hdc) * 100 /
         a.comp_total_term_hdc)  HRI_P_GRAND_TOTAL1_MP
FROM' || g_rtn;

Line: 802


'(SELECT
  cl.id  vby_id
 ,DECODE(wmv.direct_ind,
           1, ''' || l_direct_reports_string || ''',
         cl.value)  vby_value
 ,NVL(wmv.direct_ind, 0)  direct_ind
 ,to_char(NVL(wmv.direct_ind, 0)) || cl.order_by  order_by
 ,NVL(wmv.curr_hdc_end, 0)  curr_hdc_end
 ,NVL(trn.curr_separation_hdc, 0)  curr_termination_hdc
 ,NVL(trn.comp_separation_hdc, 0)  comp_termination_hdc
 ,:HRI_CURR_TERM_HDC     curr_total_term_hdc
 ,:HRI_PREV_TERM_HDC     comp_total_term_hdc
 FROM
 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
       (l_parameter_rec.view_by).viewby_table || '  cl
 ,(' || l_wcnt_chg_fact_sql || ')  trn
 ,(' || l_wrkfc_fact_sql || ')  wmv
 WHERE cl.id = wmv.vby_id' || l_outer_join || '
 AND wmv.vby_id = trn.vby_id (+) ' || g_rtn ||
 l_view_by_filter ||
') a
WHERE 1 = 1
' || l_security_clause || g_rtn ||
 l_termination_count_filter ||
'ORDER BY ' || l_parameter_rec.order_by;

Line: 831


'(SELECT
  cl.id  vby_id
 ,DECODE(trn.direct_ind,
           1, ''' || l_direct_reports_string || ''',
         cl.value)  vby_value
 ,NVL(trn.direct_ind, 0)  direct_ind
 ,to_char(NVL(trn.direct_ind, 0)) || cl.order_by  order_by
 ,NVL(trn.curr_separation_hdc, 0)  curr_termination_hdc
 ,NVL(trn.comp_separation_hdc, 0)  comp_termination_hdc
 ,:HRI_CURR_TERM_HDC     curr_total_term_hdc
 ,:HRI_PREV_TERM_HDC     comp_total_term_hdc
 FROM
 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
       (l_parameter_rec.view_by).viewby_table || '  cl
 ,(' || l_wcnt_chg_fact_sql || ')  trn
 WHERE cl.id = trn.vby_id ' || l_outer_join || g_rtn ||
 l_view_by_filter ||
') a
WHERE 1 = 1
' || l_security_clause || g_rtn ||
 l_termination_count_filter ||
'ORDER BY ' || l_parameter_rec.order_by;

Line: 858


/* Binds Will be inserted Below */
  l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC';

Line: 943


SELECT -- Terminations KPI
 qry.vby_id        VIEWBYID
,qry.vby_id        VIEWBY
,qry.curr_separation_hdc   HRI_P_MEASURE1
,qry.comp_separation_hdc   HRI_P_MEASURE2
,DECODE(qry.curr_separation_hdc,
          0, 0,
        qry.curr_low_months / (12 * qry.curr_separation_hdc))
                   HRI_P_MEASURE4
,DECODE(qry.comp_separation_hdc,
          0, 0,
        qry.comp_low_months / (12 * qry.comp_separation_hdc))
                   HRI_P_MEASURE5
,qry.curr_separation_hdc  HRI_P_GRAND_TOTAL1
,qry.comp_separation_hdc  HRI_P_GRAND_TOTAL2
,DECODE(qry.curr_separation_hdc,
          0, 0,
        qry.curr_low_months / (12 * qry.curr_separation_hdc))
                   HRI_P_GRAND_TOTAL4
,DECODE(qry.comp_separation_hdc,
          0, 0,
        qry.comp_low_months / (12 * qry.comp_separation_hdc))
                   HRI_P_GRAND_TOTAL5
FROM
 ('||l_inn_sql||') qry
WHERE 1=1
' || l_security_clause;

Line: 1095


SELECT -- Terminations by Separation KPI
 a.vby_id   VIEWBYID
,a.vby_id   VIEWBY
,a.anl_factor * 100 * a.curr_separation_hdc / a.curr_trn_div  HRI_P_MEASURE1
,a.anl_factor * 100 * a.comp_separation_hdc / a.comp_trn_div  HRI_P_MEASURE2
,a.anl_factor * 100 * a.curr_sep_vol_hdc / a.curr_trn_div     HRI_P_MEASURE4
,a.anl_factor * 100 * a.comp_sep_vol_hdc / a.comp_trn_div     HRI_P_MEASURE5
,a.anl_factor * 100 * a.curr_sep_invol_hdc / a.curr_trn_div   HRI_P_MEASURE7
,a.anl_factor * 100 * a.comp_sep_invol_hdc / a.comp_trn_div   HRI_P_MEASURE8
,a.anl_factor * 100 * a.curr_separation_hdc / a.curr_trn_div  HRI_P_GRAND_TOTAL1
,a.anl_factor * 100 * a.comp_separation_hdc / a.comp_trn_div  HRI_P_GRAND_TOTAL2
,a.anl_factor * 100 * a.curr_sep_vol_hdc / a.curr_trn_div     HRI_P_GRAND_TOTAL4
,a.anl_factor * 100 * a.comp_sep_vol_hdc / a.comp_trn_div     HRI_P_GRAND_TOTAL5
,a.anl_factor * 100 * a.curr_sep_invol_hdc / a.curr_trn_div   HRI_P_GRAND_TOTAL7
,a.anl_factor * 100 * a.comp_sep_invol_hdc / a.comp_trn_div   HRI_P_GRAND_TOTAL8
FROM
 (SELECT
   hdc.vby_id
  ,NVL(trn.curr_separation_hdc, 0)  curr_separation_hdc
  ,NVL(trn.curr_sep_invol_hdc, 0)   curr_sep_invol_hdc
  ,NVL(trn.curr_sep_vol_hdc, 0)     curr_sep_vol_hdc
  ,NVL(trn.comp_separation_hdc, 0)  comp_separation_hdc
  ,NVL(trn.comp_sep_invol_hdc, 0)   comp_sep_invol_hdc
  ,NVL(trn.comp_sep_vol_hdc, 0)     comp_sep_vol_hdc
  ,DECODE(' || l_curr_hdc_end_col || ',
    0, DECODE(trn.curr_separation_hdc, 0 , 1, trn.curr_separation_hdc),
  ' || l_curr_hdc_end_col || ')     curr_trn_div
  ,DECODE(' || l_comp_hdc_end_col || ',
    0, DECODE(trn.comp_separation_hdc, 0 , 1, trn.comp_separation_hdc),
  ' || l_comp_hdc_end_col || ')     comp_trn_div
  ,:HRI_ANL_FACTOR                  anl_factor
  FROM
   ('||l_trn_sql||') trn
  ,('||l_hdc_sql||') hdc
  WHERE hdc.vby_id = trn.vby_id (+)
 ) a
WHERE 1 = 1
' || l_security_clause;