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

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

Line: 14


SELECT pow_band_sk_pk
FROM hri_cs_pow_band_ct pow
WHERE pow.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
AND  pow.band_sequence =p_bucket_column_id;

Line: 230


'SELECT -- Terminations with Performance Bands
 qry.order_by                 HRI_P_ORDER_BY_1
,qry.vby_id                   VIEWBYID
,qry.value                    VIEWBY
,DECODE(qry.direct_ind , 0, ''Y'', ''N'')  DRILLPIVOTVB' || g_rtn ||
/* high performers current metrics */
',qry.curr_termination_hdc_b3  HRI_P_MEASURE3
,DECODE(qry.curr_termination_hdc,
          0, 0,
        (qry.curr_termination_hdc_b3 / qry.curr_termination_hdc) * 100)
                              HRI_P_MEASURE3_MP
,' || l_drill_url3 || '       HRI_P_DRILL_URL3' || g_rtn ||
/* high performers comparison metrics */
',qry.comp_termination_hdc_b3  HRI_P_MEASURE8
,DECODE(qry.comp_termination_hdc,
          0, 0,
        (qry.comp_termination_hdc_b3 / qry.comp_termination_hdc) * 100)
                              HRI_P_MEASURE8_MP' || g_rtn ||
/* average performers current metrics */
',qry.curr_termination_hdc_b2  HRI_P_MEASURE2
,DECODE(qry.curr_termination_hdc,
          0, 0,
        (qry.curr_termination_hdc_b2 / qry.curr_termination_hdc) * 100)
                              HRI_P_MEASURE2_MP
,' || l_drill_url2 || '       HRI_P_DRILL_URL2' || g_rtn ||
/* average performers comparison metrics */
',qry.comp_termination_hdc_b2  HRI_P_MEASURE9
,DECODE(qry.comp_termination_hdc,
          0, 0,
        (qry.comp_termination_hdc_b2 / qry.comp_termination_hdc) * 100)
                              HRI_P_MEASURE9_MP' || g_rtn ||
/* low performers current metrics */
',qry.curr_termination_hdc_b1  HRI_P_MEASURE1
,DECODE(qry.curr_termination_hdc,
          0, 0,
        (qry.curr_termination_hdc_b1 / qry.curr_termination_hdc) * 100)
                              HRI_P_MEASURE1_MP
,' || l_drill_url1 || '       HRI_P_DRILL_URL1' || g_rtn ||
/* low performers comparison metrics */
',qry.comp_termination_hdc_b1  HRI_P_MEASURE10
,DECODE(qry.comp_termination_hdc,
          0, 0,
        (qry.comp_termination_hdc_b1 / qry.comp_termination_hdc) * 100)
                              HRI_P_MEASURE10_MP' || g_rtn ||
/* unassigned performers current metrics */
',qry.curr_termination_hdc_na  HRI_P_MEASURE6
,DECODE(qry.curr_termination_hdc,
          0, 0,
        (qry.curr_termination_hdc_na / qry.curr_termination_hdc) * 100)
                              HRI_P_MEASURE6_MP
,' || l_drill_url6 || '       HRI_P_DRILL_URL6' || g_rtn ||
/* unassigned performers comparison metrics */
',qry.comp_termination_hdc_na  HRI_P_MEASURE11
,DECODE(qry.comp_termination_hdc,
          0, 0,
        (qry.comp_termination_hdc_na / qry.comp_termination_hdc) * 100)
                              HRI_P_MEASURE11_MP
,qry.curr_termination_hdc     HRI_P_MEASURE7
,qry.comp_termination_hdc     HRI_P_MEASURE16
,DECODE(qry.comp_termination_hdc,
          0, NULL,
        ((qry.curr_termination_hdc - qry.comp_termination_hdc) /
          qry.comp_termination_hdc) * 100)
                              HRI_P_MEASURE7_MP
,qry.curr_total_term_hdc_b3   HRI_P_GRAND_TOTAL1
,DECODE(qry.curr_total_term_hdc,
          0, 0,
        (qry.curr_total_term_hdc_b3 / qry.curr_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL2
,qry.comp_total_term_hdc_b3   HRI_P_GRAND_TOTAL3
,DECODE(qry.comp_total_term_hdc,
          0, 0,
        (qry.comp_total_term_hdc_b3 / qry.comp_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL4
,qry.curr_total_term_hdc_b2   HRI_P_GRAND_TOTAL5
,DECODE(qry.curr_total_term_hdc,
          0, 0,
        (qry.curr_total_term_hdc_b2 / qry.curr_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL6
,qry.comp_total_term_hdc_b2   HRI_P_GRAND_TOTAL7
,DECODE(qry.comp_total_term_hdc,
          0, 0,
        (qry.comp_total_term_hdc_b2 / qry.comp_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL8
,qry.curr_total_term_hdc_b1   HRI_P_GRAND_TOTAL9
,DECODE(qry.curr_total_term_hdc,
          0, 0,
        (qry.curr_total_term_hdc_b1 / qry.curr_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL10
,qry.comp_total_term_hdc_b1   HRI_P_GRAND_TOTAL11
,DECODE(qry.comp_total_term_hdc,
          0, 0,
        (qry.comp_total_term_hdc_b1 / qry.comp_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL12
,qry.curr_total_term_hdc_na   HRI_P_GRAND_TOTAL13
,DECODE(qry.curr_total_term_hdc,
          0, 0,
        (qry.curr_total_term_hdc_na / qry.curr_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL14
,qry.comp_total_term_hdc_na   HRI_P_GRAND_TOTAL15
,DECODE(qry.comp_total_term_hdc,
          0, 0,
        (qry.comp_total_term_hdc_na / qry.comp_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL16
,qry.curr_total_term_hdc      HRI_P_GRAND_TOTAL17
,qry.comp_total_term_hdc      HRI_P_GRAND_TOTAL18
,DECODE(qry.comp_total_term_hdc,
          0, 0,
        ((qry.curr_total_term_hdc - qry.comp_total_term_hdc) /
          qry.comp_total_term_hdc) * 100)
                              HRI_P_GRAND_TOTAL17_MP' || g_rtn ||
/* HRI_P_DRILL_URL5 used as drill url when viewby manager only */
', '|| l_drill_url5 || '      HRI_P_DRILL_URL5
FROM
(SELECT
  cl.id  vby_id
 ,NVL(wmv.direct_ind, 0)  direct_ind
 ,DECODE(wmv.direct_ind,
           1, ''' || l_direct_reports_string || ''',
         cl.value)  value
 ,to_char(NVL(wmv.direct_ind, 0)) || cl.order_by  order_by
 ,NVL(wmv.curr_hdc_end, 0)  curr_hdc
 ,NVL(SUM(wmv.curr_hdc_end) OVER (), 0)  curr_total_hdc
 ,NVL(trn.curr_separation_hdc_b3, 0)  curr_termination_hdc_b3
 ,NVL(trn.curr_separation_hdc_b2, 0)  curr_termination_hdc_b2
 ,NVL(trn.curr_separation_hdc_b1, 0)  curr_termination_hdc_b1
 ,NVL(trn.curr_separation_hdc_na, 0)  curr_termination_hdc_na
 ,NVL(trn.curr_separation_hdc, 0)     curr_termination_hdc
 ,NVL(trn.comp_separation_hdc_b3, 0)  comp_termination_hdc_b3
 ,NVL(trn.comp_separation_hdc_b2, 0)  comp_termination_hdc_b2
 ,NVL(trn.comp_separation_hdc_b1, 0)  comp_termination_hdc_b1
 ,NVL(trn.comp_separation_hdc_na, 0)  comp_termination_hdc_na
 ,NVL(trn.comp_separation_hdc, 0)     comp_termination_hdc
 ,:HRI_CURR_TERM_HDC_B3  curr_total_term_hdc_b3
 ,:HRI_COMP_TERM_HDC_B3  comp_total_term_hdc_b3
 ,:HRI_CURR_TERM_HDC_B2  curr_total_term_hdc_b2
 ,:HRI_COMP_TERM_HDC_B2  comp_total_term_hdc_b2
 ,:HRI_CURR_TERM_HDC_B1  curr_total_term_hdc_b1
 ,:HRI_COMP_TERM_HDC_B1  comp_total_term_hdc_b1
 ,:HRI_CURR_TERM_HDC_NA  curr_total_term_hdc_na
 ,:HRI_COMP_TERM_HDC_NA  comp_total_term_hdc_na
 ,:HRI_CURR_TERM_HDC     curr_total_term_hdc
 ,:HRI_COMP_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_wrkfc_fact_sql || ')  wmv
,(' || l_wcnt_chg_fact_sql || ')  trn
 WHERE wmv.vby_id = trn.vby_id (+)
 AND cl.id = wmv.vby_id' || l_wmv_outer_join || g_rtn ||
  l_view_by_filter ||
' ) QRY
WHERE 1 = 1
' || l_security_clause || g_rtn ||
 l_display_row_condition ||
'ORDER BY ' || l_parameter_rec.order_by;

Line: 389


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

Line: 653


'SELECT  --  Terminations with Length of Service Portlet
 a.id                               VIEWBYID
,a.value                            VIEWBY
,DECODE(a.direct_ind , 0, ''Y'', ''N'')  DRILLPIVOTVB ' || g_rtn ||
/* Title - Band One - under 1 year */'
,a.curr_trn_bn1                     HRI_P_MEASURE1
,DECODE(a.curr_trn_tot,0,0
       ,DECODE(a.curr_trn_bn1,0,0
              ,(a.curr_trn_bn1 / a.curr_trn_tot) * 100)
        )                           HRI_P_MEASURE1_MP' || g_rtn ||
/* Title - Band Two - 1-3 years */'
,a.curr_trn_bn2                     HRI_P_MEASURE2
,DECODE(a.curr_trn_tot,0,0
       ,DECODE(a.curr_trn_bn2,0,0
              ,(a.curr_trn_bn2 / a.curr_trn_tot) * 100)
        )                           HRI_P_MEASURE2_MP' || g_rtn ||
/* Title - Band Three - 3-5 years */'
,a.curr_trn_bn3                     HRI_P_MEASURE3
,DECODE(a.curr_trn_tot,0,0
       ,DECODE(a.curr_trn_bn3,0,0
              ,(a.curr_trn_bn3 / a.curr_trn_tot) * 100)
        )                           HRI_P_MEASURE3_MP ' || g_rtn ||
/* Title - Band Four - 5-10 years */'
,a.curr_trn_bn4                     HRI_P_MEASURE4
,DECODE(a.curr_trn_tot,0,0
       ,DECODE(a.curr_trn_bn4,0,0
              ,(a.curr_trn_bn4 / a.curr_trn_tot) * 100)
        )                           HRI_P_MEASURE4_MP ' || g_rtn ||
/* Title - Band Five - over 10 years */'
,a.curr_trn_bn5                     HRI_P_MEASURE5
,DECODE(a.curr_trn_tot,0,0
       ,DECODE(a.curr_trn_bn5,0,0
              ,(a.curr_trn_bn5 / a.curr_trn_tot) * 100)
        )                           HRI_P_MEASURE5_MP  ' || g_rtn ||
/* Total Terminations */'
,a.curr_trn_tot                     HRI_P_MEASURE6 ' || g_rtn ||
/* Grand Total - Band One - under 1 year */'
,a.tot_curr_trn_bn1                 HRI_P_GRAND_TOTAL1
,DECODE(a.tot_curr_trn_tot,0,0
       ,DECODE(a.tot_curr_trn_bn1,0,0
              ,(a.tot_curr_trn_bn1 / a.tot_curr_trn_tot) * 100)
       )                            HRI_P_GRAND_TOTAL1_MP' || g_rtn ||
/* Title - Band Two - 1-3 years */'
,a.tot_curr_trn_bn2                 HRI_P_GRAND_TOTAL2
,DECODE(a.tot_curr_trn_tot,0,0
       ,DECODE(a.tot_curr_trn_bn2,0,0
              ,(a.tot_curr_trn_bn2 / a.tot_curr_trn_tot) * 100)
       )                            HRI_P_GRAND_TOTAL2_MP' || g_rtn ||
/* Title - Band Three - 3-5 years */'
,a.tot_curr_trn_bn3                 HRI_P_GRAND_TOTAL3
,DECODE(a.tot_curr_trn_tot,0,0
       ,DECODE(a.tot_curr_trn_bn3,0,0
              ,(a.tot_curr_trn_bn3 / a.tot_curr_trn_tot) * 100)
       )                            HRI_P_GRAND_TOTAL3_MP' || g_rtn ||
/* Title - Band Four - 5-10 years */'
,a.tot_curr_trn_bn4                 HRI_P_GRAND_TOTAL4
,DECODE(a.tot_curr_trn_tot,0,0
       ,DECODE(a.tot_curr_trn_bn4,0,0
              ,(a.tot_curr_trn_bn4 / a.tot_curr_trn_tot) * 100)
       )                            HRI_P_GRAND_TOTAL4_MP' || g_rtn ||
/* Title - Band Four - over 10 years */'
,a.tot_curr_trn_bn5                 HRI_P_GRAND_TOTAL5
,DECODE(a.tot_curr_trn_tot,0,0
       ,DECODE(a.tot_curr_trn_bn5,0,0
              ,(a.tot_curr_trn_bn5 / a.tot_curr_trn_tot) * 100)
       )                            HRI_P_GRAND_TOTAL5_MP' || g_rtn ||
/* Total Terminations */'
,a.tot_curr_trn_tot                 HRI_P_GRAND_TOTAL6 ' || g_rtn ||
/* Order by person name default sort order */
',a.order_by                        HRI_P_ORDER_BY_1 ' || g_rtn ||
/* Whether the row is a supervisor rollup row */
',DECODE(a.direct_ind , 0, '''', ''N'')
                                    HRI_P_SUPH_RO_CA' || g_rtn ||
/* Drill URLs */
',DECODE(a.direct_ind,
           0, ''' || l_drill_mgr_sup  || ''',
         ''' || l_drill_mgr_dir  || ''')
                                    HRI_P_DRILL_URL1
,'''||l_drill_trn_bn1_dtl ||'''     HRI_P_DRILL_URL2
,'''||l_drill_trn_bn2_dtl ||'''     HRI_P_DRILL_URL3
,'''||l_drill_trn_bn3_dtl ||'''     HRI_P_DRILL_URL4
,'''||l_drill_trn_bn4_dtl ||'''     HRI_P_DRILL_URL5
,'''||l_drill_trn_bn5_dtl ||'''     HRI_P_DRILL_URL6
FROM
(SELECT
  cl.id
 ,DECODE(wmv.direct_ind,
           1, ''' || l_direct_reports_string || ''',
         cl.value)  value
 ,to_char(NVL(wmv.direct_ind, 0)) || cl.order_by  order_by
 ,NVL(wmv.direct_ind, 0)  direct_ind' || g_rtn ||
/* Headcount */
' ,NVL(wmv.curr_hdc_end, 0)  curr_hdc_end' || g_rtn ||
/* Turnover */
' ,NVL(trn.curr_separation_hdc_b1, 0)  curr_trn_bn1
 ,NVL(trn.curr_separation_hdc_b2, 0)  curr_trn_bn2
 ,NVL(trn.curr_separation_hdc_b3, 0)  curr_trn_bn3
 ,NVL(trn.curr_separation_hdc_b4, 0)  curr_trn_bn4
 ,NVL(trn.curr_separation_hdc_b5, 0)  curr_trn_bn5
 ,NVL(trn.curr_separation_hdc, 0)     curr_trn_tot' || g_rtn ||
/* Grand Totals - Turnover */
' ,:HRI_TOT_TERM_HDC_B1                  tot_curr_trn_bn1
 ,:HRI_TOT_TERM_HDC_B2                   tot_curr_trn_bn2
 ,:HRI_TOT_TERM_HDC_B3                   tot_curr_trn_bn3
 ,:HRI_TOT_TERM_HDC_B4                   tot_curr_trn_bn4
 ,:HRI_TOT_TERM_HDC_B5                   tot_curr_trn_bn5
 ,:HRI_TOT_TERM_HDC                      tot_curr_trn_tot
 FROM
  ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
        (l_parameter_rec.view_by).viewby_table || '  cl
 ,(' || l_wrkfc_fact_sql || ')  wmv
 ,(' || l_wcnt_chg_fact_sql || ')  trn
 WHERE wmv.vby_id = trn.vby_id (+)
 AND cl.id = wmv.vby_id ' || l_wmv_outer_join || g_rtn ||
  l_view_by_filter ||
' ) a
WHERE 1 = 1' || g_rtn ||
  l_display_row_condition || g_rtn ||
  l_security_clause || '
ORDER BY ' || l_parameter_rec.order_by;

Line: 892


'SELECT --Termination by Performance
 qry.vby_id               VIEWBYID
,qry.vby_id               VIEWBY
,curr_separation_hdc_b3/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_MEASURE1
,comp_separation_hdc_b3/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_MEASURE2
,curr_separation_hdc_b2/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_MEASURE4
,comp_separation_hdc_b2/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_MEASURE5
,curr_separation_hdc_b1/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_MEASURE7
,comp_separation_hdc_b1/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_MEASURE8
,curr_separation_hdc_na/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_MEASURE10
,comp_separation_hdc_na/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_MEASURE11
,curr_separation_hdc_b3/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL1
,comp_separation_hdc_b3/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL2
,curr_separation_hdc_b2/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL4
,comp_separation_hdc_b2/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL5
,curr_separation_hdc_b1/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL7
,comp_separation_hdc_b1/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL8
,curr_separation_hdc_na/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL10
,comp_separation_hdc_na/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
                          HRI_P_GRAND_TOTAL11
FROM
('||l_inn_sql||') qry
WHERE 1=1
' || l_security_clause;