[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
'SELECT -- Employee Headcount Ratio for Perf Band
qry.id VIEWBYID
,qry.value VIEWBY
,DECODE(qry.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
/* Band 3 - High Performers */
',qry.curr_hdc_b3 HRI_P_MEASURE3
,DECODE(qry.curr_hdc, 0, 0,
(qry.curr_hdc_b3/qry.curr_hdc)*100)
HRI_P_MEASURE3_MP
,qry.comp_hdc_b3 HRI_P_MEASURE8
,DECODE(qry.comp_hdc_b3, 0, to_number(NULL),
(qry.curr_hdc_b3 - qry.comp_hdc_b3) * 100
/ qry.comp_hdc_b3) HRI_P_MEASURE8_MP' || g_rtn ||
/* Band 2 - Average Performers */
',qry.curr_hdc_b2 HRI_P_MEASURE2
,DECODE(qry.curr_hdc, 0, 0,
(qry.curr_hdc_b2/qry.curr_hdc)*100)
HRI_P_MEASURE2_MP
,qry.comp_hdc_b2 HRI_P_MEASURE9
,DECODE(qry.comp_hdc_b2, 0, to_number(NULL),
(qry.curr_hdc_b2 - qry.comp_hdc_b2) * 100
/ qry.comp_hdc_b2) HRI_P_MEASURE9_MP' || g_rtn ||
/* Band 1 - Low Performers */
',qry.curr_hdc_b1 HRI_P_MEASURE1
,DECODE(qry.curr_hdc, 0, 0,
(qry.curr_hdc_b1/qry.curr_hdc)*100)
HRI_P_MEASURE1_MP
,qry.comp_hdc_b1 HRI_P_MEASURE10
,DECODE(qry.comp_hdc_b1, 0, to_number(NULL),
(qry.curr_hdc_b1 - qry.comp_hdc_b1) * 100
/ qry.comp_hdc_b1) HRI_P_MEASURE10_MP' || g_rtn ||
/* Unassigned band - no performance rating */
',qry.curr_hdc_na HRI_P_MEASURE6
,DECODE(qry.curr_hdc_na, 0, 0,
(qry.curr_hdc_na/qry.curr_hdc)*100)
HRI_P_MEASURE6_MP
,qry.comp_hdc_na HRI_P_MEASURE11
,DECODE(qry.comp_hdc_na, 0, to_number(NULL),
(qry.curr_hdc_na - qry.comp_hdc_na) * 100
/ qry.comp_hdc_na) HRI_P_MEASURE11_MP' || g_rtn ||
/* Row totals - Across all performance bands */
',qry.curr_hdc HRI_P_MEASURE7
,qry.comp_hdc HRI_P_MEASURE16' || g_rtn ||
/* Order by */
',qry.order_by HRI_P_ORDER_BY_1' || g_rtn ||
/* Grand Totals */
',DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b3/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL1
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b2/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL2
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b1/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL3
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_na/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL4
,qry.total_comp_hdc_b3 HRI_P_GRAND_TOTAL5
,DECODE(qry.total_comp_hdc_b3, 0, 0,
(qry.total_curr_hdc_b3 - qry.total_comp_hdc_b3) * 100/qry.total_comp_hdc_b3)
HRI_P_GRAND_TOTAL6
,qry.total_comp_hdc_b2 HRI_P_GRAND_TOTAL7
,DECODE(qry.total_comp_hdc_b2, 0, 0,
(qry.total_curr_hdc_b2 - qry.total_comp_hdc_b2) * 100/qry.total_comp_hdc_b2)
HRI_P_GRAND_TOTAL8
,qry.total_comp_hdc_b1 HRI_P_GRAND_TOTAL9
,DECODE(qry.total_comp_hdc_b1, 0, 0,
(qry.total_curr_hdc_b1 - qry.total_comp_hdc_b1) * 100/qry.total_comp_hdc_b1)
HRI_P_GRAND_TOTAL10
,qry.total_comp_hdc_na HRI_P_GRAND_TOTAL11
,DECODE(qry.total_comp_hdc_na, 0, 0,
(qry.total_curr_hdc_na - qry.total_comp_hdc_na) * 100/qry.total_comp_hdc_na)
HRI_P_GRAND_TOTAL12
,qry.total_comp_hdc HRI_P_GRAND_TOTAL13
,DECODE(qry.direct_ind,0,'''',''N'') HRI_P_SUPH_RO_CA' || g_rtn ||
/* Drill URLs */
', DECODE(qry.direct_ind,
0, ''' || l_dynsql_rec.drill_mgr_sup || ''',
''' || l_dynsql_rec.drill_to_detail || ''') HRI_P_DRILL_URL1
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
,NVL(wmv.curr_hdc_b1, 0) curr_hdc_b1
,NVL(wmv.curr_hdc_b2, 0) curr_hdc_b2
,NVL(wmv.curr_hdc_b3, 0) curr_hdc_b3
,NVL(wmv.curr_hdc_na, 0) curr_hdc_na
,NVL(wmv.curr_hdc_end, 0) curr_hdc
,NVL(wmv.comp_hdc_b1, 0) comp_hdc_b1
,NVL(wmv.comp_hdc_b2, 0) comp_hdc_b2
,NVL(wmv.comp_hdc_b3, 0) comp_hdc_b3
,NVL(wmv.comp_hdc_na, 0) comp_hdc_na
,NVL(wmv.comp_hdc_end, 0) comp_hdc
,NVL(SUM(wmv.curr_hdc_b1) OVER (), 0) total_curr_hdc_b1
,NVL(SUM(wmv.curr_hdc_b2) OVER (), 0) total_curr_hdc_b2
,NVL(SUM(wmv.curr_hdc_b3) OVER (), 0) total_curr_hdc_b3
,NVL(SUM(wmv.curr_hdc_na) OVER (), 0) total_curr_hdc_na
,NVL(SUM(wmv.curr_hdc_end) OVER (), 0) total_curr_hdc
,NVL(SUM(wmv.comp_total_hdc_b1) OVER (), 0) total_comp_hdc_b1
,NVL(SUM(wmv.comp_total_hdc_b2) OVER (), 0) total_comp_hdc_b2
,NVL(SUM(wmv.comp_total_hdc_b3) OVER (), 0) total_comp_hdc_b3
,NVL(SUM(wmv.comp_total_hdc_na) OVER (), 0) total_comp_hdc_na
,NVL(SUM(wmv.comp_total_hdc_end) OVER (), 0) total_comp_hdc
,NVL(wmv.direct_ind, 0) direct_ind
FROM
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(l_parameter_rec.view_by).viewby_table || ' vby
,(' || l_wrkfc_fact_sql || ') wmv
WHERE wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id' || g_rtn ||
l_dynsql_rec.viewby_condition ||
' ) qry
WHERE 1 = 1' || g_rtn ||
l_dynsql_rec.display_row_condition ||
l_security_clause || '
ORDER BY ' || l_dynsql_rec.order_by;
'SELECT -- Headcount Ratio for LOW Band (Generic)
qry.id VIEWBYID
,qry.value VIEWBY
,DECODE(qry.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
/* Band 1: 0 - 1 Years */
',qry.curr_hdc_b1 HRI_P_MEASURE1
,'''||l_drill_url2||''' HRI_P_DRILL_URL2
,DECODE(qry.curr_hdc, 0, 0,
(qry.curr_hdc_b1/qry.curr_hdc)*100)
HRI_P_MEASURE1_MP
,qry.comp_hdc_b1 HRI_P_MEASURE2
,DECODE(qry.comp_hdc_b1, 0, to_number(NULL),
(qry.curr_hdc_b1 - qry.comp_hdc_b1) * 100
/ qry.comp_hdc_b1) HRI_P_MEASURE2_MP' || g_rtn ||
/* Band 2: 1 - 3 Years */
',qry.curr_hdc_b2 HRI_P_MEASURE3
,'''||l_drill_url3||''' HRI_P_DRILL_URL3
,DECODE(qry.curr_hdc, 0, 0,
(qry.curr_hdc_b2/qry.curr_hdc)*100)
HRI_P_MEASURE3_MP
,qry.comp_hdc_b2 HRI_P_MEASURE4
,DECODE(qry.comp_hdc_b2, 0, to_number(NULL),
(qry.curr_hdc_b2 - qry.comp_hdc_b2) * 100
/ qry.comp_hdc_b2) HRI_P_MEASURE4_MP' || g_rtn ||
/* Band 3: 3 - 5 Years */
',qry.curr_hdc_b3 HRI_P_MEASURE5
,'''||l_drill_url4||''' HRI_P_DRILL_URL4
,DECODE(qry.curr_hdc, 0, 0,
(qry.curr_hdc_b3/qry.curr_hdc)*100)
HRI_P_MEASURE5_MP
,qry.comp_hdc_b3 HRI_P_MEASURE6
,DECODE(qry.comp_hdc_b3, 0, to_number(NULL),
(qry.curr_hdc_b3 - qry.comp_hdc_b3) * 100
/ qry.comp_hdc_b3) HRI_P_MEASURE6_MP' || g_rtn ||
/* Band 4: 5 - 10 Years */
',qry.curr_hdc_b4 HRI_P_MEASURE7
,'''||l_drill_url5||''' HRI_P_DRILL_URL5
,DECODE(qry.curr_hdc_b4, 0, 0,
(qry.curr_hdc_b4/qry.curr_hdc)*100)
HRI_P_MEASURE7_MP
,qry.comp_hdc_b4 HRI_P_MEASURE8
,DECODE(qry.comp_hdc_b4, 0, to_number(NULL),
(qry.curr_hdc_b4 - qry.comp_hdc_b4) * 100
/ qry.comp_hdc_b4) HRI_P_MEASURE8_MP' || g_rtn ||
/* Band 5: 10 + Years */
',qry.curr_hdc_b5 HRI_P_MEASURE9
,'''||l_drill_url6||''' HRI_P_DRILL_URL6
,DECODE(qry.curr_hdc_b5, 0, 0,
(qry.curr_hdc_b5/qry.curr_hdc)*100)
HRI_P_MEASURE9_MP
,qry.comp_hdc_b5 HRI_P_MEASURE10
,DECODE(qry.comp_hdc_b5, 0, to_number(NULL),
(qry.curr_hdc_b5 - qry.comp_hdc_b5) * 100
/ qry.comp_hdc_b5) HRI_P_MEASURE10_MP' || g_rtn ||
/* Row totals - Across all performance bands */
',qry.curr_hdc HRI_P_MEASURE11
,qry.comp_hdc HRI_P_MEASURE12' || g_rtn ||
/* Order by */
',qry.order_by HRI_P_ORDER_BY_1' || g_rtn ||
/* Grand Totals */
',DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b1/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL1
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b2/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL2
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b3/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL3
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b4/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL4
,DECODE(qry.total_curr_hdc, 0, 0,
(qry.total_curr_hdc_b5/qry.total_curr_hdc) * 100)
HRI_P_GRAND_TOTAL5
,qry.total_comp_hdc_b1 HRI_P_GRAND_TOTAL6
,DECODE(qry.total_comp_hdc_b1, 0, 0,
(qry.total_curr_hdc_b1 - qry.total_comp_hdc_b1) * 100/qry.total_comp_hdc_b1)
HRI_P_GRAND_TOTAL7
,qry.total_comp_hdc_b2 HRI_P_GRAND_TOTAL8
,DECODE(qry.total_comp_hdc_b2, 0, 0,
(qry.total_curr_hdc_b2 - qry.total_comp_hdc_b2) * 100/qry.total_comp_hdc_b2)
HRI_P_GRAND_TOTAL9
,qry.total_comp_hdc_b3 HRI_P_GRAND_TOTAL10
,DECODE(qry.total_comp_hdc_b3, 0, 0,
(qry.total_curr_hdc_b3 - qry.total_comp_hdc_b3) * 100/qry.total_comp_hdc_b3)
HRI_P_GRAND_TOTAL11
,qry.total_comp_hdc_b4 HRI_P_GRAND_TOTAL12
,DECODE(qry.total_comp_hdc_b4, 0, 0,
(qry.total_curr_hdc_b4 - qry.total_comp_hdc_b4) * 100/qry.total_comp_hdc_b4)
HRI_P_GRAND_TOTAL13
,qry.total_comp_hdc_b5 HRI_P_GRAND_TOTAL14
,DECODE(qry.total_comp_hdc_b5, 0, 0,
(qry.total_curr_hdc_b5 - qry.total_comp_hdc_b5) * 100/qry.total_comp_hdc_b5)
HRI_P_GRAND_TOTAL15
,qry.total_comp_hdc HRI_P_GRAND_TOTAL16
,DECODE(qry.direct_ind,0,'''',''N'') HRI_P_SUPH_RO_CA' || g_rtn ||
/* Drill URLs */
', DECODE(qry.direct_ind,
0, ''' || l_dynsql_rec.drill_mgr_sup || ''',
''' || l_dynsql_rec.drill_to_detail || ''') HRI_P_DRILL_URL1
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
,NVL(wmv.curr_hdc_b1, 0) curr_hdc_b1
,NVL(wmv.curr_hdc_b2, 0) curr_hdc_b2
,NVL(wmv.curr_hdc_b3, 0) curr_hdc_b3
,NVL(wmv.curr_hdc_b4, 0) curr_hdc_b4
,NVL(wmv.curr_hdc_b5, 0) curr_hdc_b5
,NVL(wmv.curr_hdc_end, 0) curr_hdc
,NVL(wmv.comp_hdc_b1, 0) comp_hdc_b1
,NVL(wmv.comp_hdc_b2, 0) comp_hdc_b2
,NVL(wmv.comp_hdc_b3, 0) comp_hdc_b3
,NVL(wmv.comp_hdc_b4, 0) comp_hdc_b4
,NVL(wmv.comp_hdc_b5, 0) comp_hdc_b5
,NVL(wmv.comp_hdc_end, 0) comp_hdc
,SUM(wmv.curr_hdc_b1) OVER () total_curr_hdc_b1
,SUM(wmv.curr_hdc_b2) OVER () total_curr_hdc_b2
,SUM(wmv.curr_hdc_b3) OVER () total_curr_hdc_b3
,SUM(wmv.curr_hdc_b4) OVER () total_curr_hdc_b4
,SUM(wmv.curr_hdc_b5) OVER () total_curr_hdc_b5
,SUM(wmv.curr_hdc_end) OVER () total_curr_hdc
,SUM(wmv.comp_total_hdc_b1) OVER () total_comp_hdc_b1
,SUM(wmv.comp_total_hdc_b2) OVER () total_comp_hdc_b2
,SUM(wmv.comp_total_hdc_b3) OVER () total_comp_hdc_b3
,SUM(wmv.comp_total_hdc_b4) OVER () total_comp_hdc_b4
,SUM(wmv.comp_total_hdc_b5) OVER () total_comp_hdc_b5
,SUM(wmv.comp_total_hdc_end) OVER () total_comp_hdc
,NVL(wmv.direct_ind, 0) direct_ind
FROM
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(l_parameter_rec.view_by).viewby_table || ' vby,
(' || l_wrkfc_fact_sql || ') wmv
WHERE wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id' || g_rtn ||
l_dynsql_rec.viewby_condition ||
' ) qry
WHERE 1 = 1' || g_rtn ||
l_dynsql_rec.display_row_condition ||
l_security_clause || '
ORDER BY ' || l_dynsql_rec.order_by;
'SELECT -- Headcount by Performance KPI
qry.vby_id VIEWBYID
,qry.vby_id VIEWBY
,curr_hdc_b3 HRI_P_MEASURE1
,comp_hdc_b3 HRI_P_MEASURE2
,curr_hdc_b2 HRI_P_MEASURE4
,comp_hdc_b2 HRI_P_MEASURE5
,curr_hdc_b1 HRI_P_MEASURE7
,comp_hdc_b1 HRI_P_MEASURE8
,curr_hdc_na HRI_P_MEASURE10
,comp_hdc_na HRI_P_MEASURE11
,curr_hdc_b3 HRI_P_GRAND_TOTAL1
,comp_hdc_b3 HRI_P_GRAND_TOTAL2
,curr_hdc_b2 HRI_P_GRAND_TOTAL4
,comp_hdc_b2 HRI_P_GRAND_TOTAL5
,curr_hdc_b1 HRI_P_GRAND_TOTAL7
,comp_hdc_b1 HRI_P_GRAND_TOTAL8
,curr_hdc_na HRI_P_GRAND_TOTAL10
,comp_hdc_na HRI_P_GRAND_TOTAL11
FROM
('||l_inn_sql||') qry
WHERE 1=1
' || l_security_clause;