[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT -- Workforce Turnover Summary Portlet new code
a.id VIEWBYID
,a.value VIEWBY
,DECODE(a.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
/* Title - Headcount */'
,NVL(a.comp_hdc_end,0) HRI_P_MEASURE1
,NVL(a.curr_hdc_end,0) HRI_P_MEASURE2
,DECODE(a.curr_total_hdc_end, 0, 0,
(100 * a.curr_hdc_end) / a.curr_total_hdc_end) ' ||
' HRI_P_MEASURE3' || g_rtn ||
/* Title - Voluntary Terminations */'
,a.curr_term_vol_hdc HRI_P_MEASURE4
,a.anl_factor * 100 * a.curr_term_vol_hdc / a.curr_trn_div
HRI_P_MEASURE5
,a.anl_factor * 100 * (a.curr_term_vol_hdc / a.curr_trn_div -
a.comp_term_vol_hdc / a.comp_trn_div)
HRI_P_MEASURE6 ' || g_rtn ||
/* Title - InVoluntary Terminations */'
,a.curr_term_invol_hdc HRI_P_MEASURE7
,a.anl_factor * 100 * a.curr_term_invol_hdc / a.curr_trn_div
HRI_P_MEASURE8
,a.anl_factor * 100 * (a.curr_term_invol_hdc / a.curr_trn_div -
a.comp_term_invol_hdc / a.comp_trn_div)
HRI_P_MEASURE9' || g_rtn ||
/* Title - Total Terminations */'
,a.curr_termination_hdc HRI_P_MEASURE10
,a.anl_factor * 100 * a.curr_termination_hdc / a.curr_trn_div
HRI_P_MEASURE11
,a.anl_factor * 100 * (a.curr_termination_hdc / a.curr_trn_div -
a.comp_termination_hdc / a.comp_trn_div)
HRI_P_MEASURE12' || g_rtn ||
/* Title - Grand Total Headcount */'
,a.comp_total_hdc_end HRI_P_GRAND_TOTAL1
,a.curr_total_hdc_end HRI_P_GRAND_TOTAL2
,100 HRI_P_GRAND_TOTAL3' || g_rtn ||
/* Title - Grand Total Voluntary Terminations */'
,a.curr_total_trn_vol HRI_P_GRAND_TOTAL4
,a.anl_factor * 100 * a.curr_total_trn_vol / curr_total_trn_div
HRI_P_GRAND_TOTAL5
,a.anl_factor * 100 * (a.curr_total_trn_vol / curr_total_trn_div -
a.comp_total_trn_vol / comp_total_trn_div)
HRI_P_GRAND_TOTAL6' || g_rtn ||
/* Title - Grand Total InVoluntary Terminations */'
,a.curr_total_trn_inv HRI_P_GRAND_TOTAL7
,a.anl_factor * 100 * a.curr_total_trn_inv / curr_total_trn_div
HRI_P_GRAND_TOTAL8
,a.anl_factor * 100 * (a.curr_total_trn_inv / curr_total_trn_div -
a.comp_total_trn_inv / comp_total_trn_div)
HRI_P_GRAND_TOTAL9' || g_rtn ||
/* Title - Grand Total Total Terminations */'
,a.curr_total_trn_tot HRI_P_GRAND_TOTAL10
,a.anl_factor * 100 * a.curr_total_trn_tot / curr_total_trn_div
HRI_P_GRAND_TOTAL11
,a.anl_factor * 100 * (a.curr_total_trn_tot / curr_total_trn_div -
a.comp_total_trn_tot / comp_total_trn_div)
HRI_P_GRAND_TOTAL12' || 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_dynsql_rec.drill_mgr_sup || ''',
1, ''' || l_dynsql_rec.drill_mgr_dir || ''',
'''') HRI_P_DRILL_URL1
,''' || l_dynsql_rec.drill_trn_vol_dtl || '''
HRI_P_DRILL_URL2
,''' || l_dynsql_rec.drill_trn_inv_dtl || '''
HRI_P_DRILL_URL3
,''' || l_dynsql_rec.drill_trn_tot_dtl || '''
HRI_P_DRILL_URL4
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.curr_total_hdc_trn,
0, DECODE(tots.curr_total_trn_tot, 0 , 1, tots.curr_total_trn_tot),
tots.curr_total_hdc_trn) curr_total_trn_div
,DECODE(tots.comp_total_hdc_trn,
0, DECODE(tots.comp_total_trn_tot, 0 , 1, tots.comp_total_trn_tot),
tots.comp_total_hdc_trn) comp_total_trn_div
FROM
(SELECT
/* View by */
vby.id
,DECODE(wmv.direct_ind,
1, ''' || l_direct_reports_string || ''',
vby.value) value
,to_char(NVL(wmv.direct_ind, 0)) || vby.order_by order_by' || g_rtn ||
/* Indicators */
' ,NVL(wmv.direct_ind, 0) direct_ind' || g_rtn ||
/* Headcount */
' ,NVL(wmv.curr_hdc_end, 0) curr_hdc_end
,NVL(wmv.comp_hdc_end, 0) comp_hdc_end' || g_rtn ||
/* Headcount for turnover calculation */
' ,' || l_dynsql_rec.hdc_trn_col_curr || ' curr_hdc_trn
,' || l_dynsql_rec.hdc_trn_col_comp || ' 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 */
' ,NVL(SUM(wmv.curr_hdc_end) OVER (), 0) curr_total_hdc_end
,NVL(SUM(comp_total_hdc_end) OVER (), 0) comp_total_hdc_end' || g_rtn ||
/* Grand Totals - Headcount for turnover calculation */
' ,NVL(SUM(' || l_dynsql_rec.hdc_trn_col_curr_tot ||
') OVER (), 0) curr_total_hdc_trn
,NVL(SUM(' || l_dynsql_rec.hdc_trn_col_comp_tot ||
') OVER (), 0) comp_total_hdc_trn' || g_rtn ||
/* Grand Totals - Turnover */
' ,:HRI_CURR_TERM_VOL curr_total_trn_vol
,:HRI_CURR_TERM_INVOL curr_total_trn_inv
,:HRI_CURR_TERM_INVOL + :HRI_CURR_TERM_VOL curr_total_trn_tot
,:HRI_COMP_TERM_VOL comp_total_trn_vol
,:HRI_COMP_TERM_INVOL comp_total_trn_inv
,:HRI_COMP_TERM_VOL + :HRI_COMP_TERM_INVOL comp_total_trn_tot
FROM
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(l_parameter_rec.view_by).viewby_table || ' vby' || g_rtn
|| ',( ' || l_wrkfc_fact_sql || ' ) wmv' || g_rtn
|| ',( ' || l_wcnt_chg_fact_sql || ' ) trn' || g_rtn
|| 'WHERE wmv.vby_id = trn.vby_id (+)
AND wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id ' || g_rtn ||
l_dynsql_rec.view_by_filter ||
' ) tots
) a
WHERE 1 = 1 ' || g_rtn ||
l_dynsql_rec.display_row_condition ||
l_security_clause || '
ORDER BY ' || l_parameter_rec.order_by;