[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT -- Total Workforce KPI Report
vby.id VIEWBYID
,vby.value VIEWBY
,tab.CURR_HDC_END HRI_P_MEASURE1
,tab.COMP_HDC_END HRI_P_MEASURE2
,tab.CURR_HDC_EMP HRI_P_MEASURE3
,tab.CURR_HDC_EMP HRI_P_MEASURE4
,tab.CURR_HDC_CWK HRI_P_MEASURE5
,tab.COMP_HDC_CWK HRI_P_MEASURE6
,tab.CURR_HDC_END HRI_P_GRAND_TOTAL1
,tab.COMP_HDC_END HRI_P_GRAND_TOTAL2
,tab.CURR_HDC_EMP HRI_P_GRAND_TOTAL3
,tab.CURR_HDC_EMP HRI_P_GRAND_TOTAL4
,CURR_HDC_CWK HRI_P_GRAND_TOTAL5
,COMP_HDC_CWK HRI_P_GRAND_TOTAL6
FROM
hri_dbi_cl_per_n_v vby
,(' || l_wrkfc_fact_sql || ') tab
WHERE tab.vby_id = vby.id
AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.effective_start_date
AND vby.effective_end_date
' || l_security_clause;
/* To support selective drill across urls */
l_drill_to_function1 VARCHAR2(300);
/* use selective drill across feature */
l_drill_url1 := 'pFunctionName=HRI_P_WMV_WF_SUP&' ||
'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
'VIEW_BY_NAME=VIEW_BY_ID&' ||
'pParamIds=Y';
l_sqltext :='SELECT -- Workforce Activity by Manager Status
tots.id VIEWBYID
,tots.value VIEWBY
,tots.current_wmv_start HRI_P_MEASURE1
,tots.wmv_gain_hire HRI_P_MEASURE2
,'''|| l_drill_url2 ||''' HRI_P_DRILL_URL2
,tots.wmv_gain_place HRI_P_MEASURE3
,'''|| l_drill_url3 ||''' HRI_P_DRILL_URL3
,tots.wmv_gain_transfer HRI_P_MEASURE4
,'''|| l_drill_url4 ||''' HRI_P_DRILL_URL4
,tots.wmv_loss_term HRI_P_MEASURE5
,'''|| l_drill_url5 ||''' HRI_P_DRILL_URL5
,tots.wmv_loss_end_place HRI_P_MEASURE6
,'''|| l_drill_url6 ||''' HRI_P_DRILL_URL6
,tots.wmv_loss_transfer HRI_P_MEASURE7
,'''|| l_drill_url7 ||''' HRI_P_DRILL_URL7
,tots.current_wmv_end HRI_P_MEASURE8
,tots.wmv_net HRI_P_MEASURE9
,DECODE(tots.previous_wmv_end,
0, DECODE(tots.current_wmv_end,0,0,100),
(tots.current_wmv_end - tots.previous_wmv_end) * 100 /
tots.previous_wmv_end) HRI_P_MEASURE10
,tots.curr_total_hdc_start HRI_P_GRAND_TOTAL1
,:HRI_TOT_GAIN_HIRE HRI_P_GRAND_TOTAL2
,:HRI_TOT_GAIN_PLACE HRI_P_GRAND_TOTAL3
,:HRI_TOT_GAIN_TRANSFER HRI_P_GRAND_TOTAL4
,:HRI_TOT_LOSS_TERM HRI_P_GRAND_TOTAL5
,:HRI_TOT_LOSS_PLACE HRI_P_GRAND_TOTAL6
,:HRI_TOT_LOSS_TRANSFER HRI_P_GRAND_TOTAL7
,tots.curr_total_hdc_end HRI_P_GRAND_TOTAL8
,:HRI_TOT_NET_GAIN_LOSS HRI_P_GRAND_TOTAL9
,DECODE(tots.comp_total_hdc_end,
0, DECODE(tots.curr_total_hdc_end , 0, 0, 100),
(tots.curr_total_hdc_end - tots.comp_total_hdc_end) * 100 /
tots.comp_total_hdc_end) HRI_P_GRAND_TOTAL10
,tots.order_by HRI_P_ORDER_BY_1
,tots.suph_rollup_flag HRI_P_SUPH_RO_CA
,DECODE(tots.suph_rollup_flag,''Y'','''|| l_drill_url1 ||'''
,''N'','''|| l_drill_url8 ||''')
HRI_P_DRILL_URL8
FROM
(SELECT
per.id
,DECODE(wmv.direct_ind,
1, ''' || l_direct_reports_string || ''',
per.value) value
,to_char(wmv.direct_ind) || per.order_by order_by
,NVL(chg.Curr_hire_hdc + chg.curr_transfer_in_hdc,0) wmv_gain
,NVL(chg.curr_hire_hdc_emp,0) wmv_gain_hire
,NVL(chg.curr_hire_hdc_cwk,0) wmv_gain_place
,NVL(chg.curr_transfer_in_hdc,0) wmv_gain_transfer
,NVL(chg.curr_termination_hdc + curr_transfer_out_hdc,0)
wmv_loss
,NVL(chg.curr_termination_hdc_emp,0) wmv_loss_term
,NVL(chg.curr_termination_hdc_cwk,0) wmv_loss_end_place
,NVL(chg.curr_transfer_out_hdc,0) wmv_loss_transfer
,NVL((chg.curr_hire_hdc + chg.curr_transfer_in_hdc) -
(chg.curr_termination_hdc + chg.curr_transfer_out_hdc), 0)
wmv_net
,wmv.curr_hdc_start current_wmv_start
,wmv.curr_hdc_end current_wmv_end
,wmv.comp_hdc_start previous_wmv_start
,wmv.comp_hdc_end previous_wmv_end
,SUM(wmv.curr_hdc_end) OVER () curr_total_hdc_end
,SUM(wmv.curr_total_hdc_start) OVER () curr_total_hdc_start
,SUM(wmv.comp_total_hdc_end) OVER () comp_total_hdc_end
,DECODE(wmv.direct_ind,
1, ''N'',
'''') suph_rollup_flag
FROM
hri_dbi_cl_per_n_v per
,('|| l_wrkfc_fact_sql ||') wmv
,('|| l_wcnt_chg_fact_sql ||') chg
WHERE wmv.vby_id = chg.vby_id (+)
AND wmv.vby_id = per.id
AND &AS_OF_DATE BETWEEN per.effective_start_date
AND per.effective_end_date
AND (wmv.curr_hdc_end > 0
OR chg.curr_hire_hdc > 0
or chg.curr_transfer_in_hdc > 0
or chg.curr_transfer_out_hdc > 0
or chg.curr_termination_hdc > 0
or wmv.direct_ind = 1)
) tots
WHERE 1 = 1
' || l_security_clause || '
ORDER BY ' || l_parameter_rec.order_by;