[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* To support selective drill across urls */
l_drill_to_function1 VARCHAR2(300);
/* use selective drill across feature */
l_drill_url1 := 'pFunctionName=' || l_drill_to_function1 || '&' ||
'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
'VIEW_BY_NAME=VIEW_BY_ID&' ||
'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
'pParamIds=Y';
'SELECT -- Headcount Portlet
tots.id VIEWBYID
,tots.value VIEWBY ' || g_rtn ||
',DECODE(tots.suph_rollup_flag,
''N'', ''' || l_drill_url6 || ''',
''' || l_drill_url5 || ''') HRI_P_DRILL_URL5' || g_rtn ||
',tots.value HRI_P_CHAR1_GA' || g_rtn ||
/* WMV value at Start */
',tots.current_wmv_start HRI_P_MEASURE7 ' || g_rtn ||
/* WMV gained through hires */
',tots.wmv_gain_hire HRI_P_MEASURE3 ' || g_rtn ||
',''' || l_drill_url1 ||''' HRI_P_DRILL_URL1 ' || g_rtn ||
/* WMV gained through transfers in */
',tots.wmv_gain_transfer HRI_P_MEASURE4 ' || g_rtn ||
',''' || l_drill_url2 ||''' HRI_P_DRILL_URL2 ' || g_rtn ||
/* WMV lost through terminations */
',tots.wmv_loss_term HRI_P_MEASURE5 ' || g_rtn ||
',''' || l_drill_url3 ||''' HRI_P_DRILL_URL3 ' || g_rtn ||
/* WMV lost through transfers out */
',tots.wmv_loss_transfer HRI_P_MEASURE6 ' || g_rtn ||
',''' || l_drill_url4 ||''' HRI_P_DRILL_URL4 ' || g_rtn ||
/* Current WMV value */
',tots.current_wmv_end HRI_P_MEASURE2 ' || g_rtn ||
/* Net change in WMV value */
',tots.wmv_net HRI_P_WMV_CHNG_NET_SUM_MV
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
(p_previous_col => 'tots.previous_wmv_end',
p_current_col => 'tots.current_wmv_end') || '
HRI_P_WMV_CHNG_PCT_SUM_MV
,tots.comp_total_hdc_end HRI_P_WMV_SUM_PREV_MV
,tots.comp_total_hdc_end HRI_P_MEASURE1
,tots.wmv_gain_hire HRI_P_MEASURE8
,tots.wmv_loss_term HRI_P_MEASURE9
,tots.curr_total_hdc_end HRI_P_GRAND_TOTAL1
,:HRI_TOT_GAIN_HIRE HRI_P_GRAND_TOTAL5
,:HRI_TOT_GAIN_TRANSFER HRI_P_GRAND_TOTAL6
,:HRI_TOT_LOSS_TERM HRI_P_GRAND_TOTAL7
,:HRI_TOT_LOSS_TRANSFER HRI_P_GRAND_TOTAL8
,tots.curr_total_hdc_start HRI_P_GRAND_TOTAL2
,:HRI_TOT_NET_GAIN_LOSS HRI_P_GRAND_TOTAL3
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
(p_previous_col => 'tots.comp_total_hdc_end',
p_current_col => 'tots.curr_total_hdc_end') || '
HRI_P_GRAND_TOTAL4
,tots.comp_total_hdc_end HRI_P_GRAND_TOTAL9 ' || g_rtn ||
/* Order by person name default sort order */
',tots.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
/* Whether the row is a supervisor rollup row */
',tots.suph_rollup_flag HRI_P_SUPH_RO_CA
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,0) wmv_gain_hire
,NVL(chg.curr_transfer_in_hdc,0) wmv_gain_transfer
,NVL(chg.curr_termination_hdc + chg.curr_transfer_out_hdc,0) wmv_loss
,NVL(chg.curr_termination_hdc,0) wmv_loss_term
,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_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 &BIS_CURRENT_ASOF_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;
'SELECT -- Headcount KPI
qry.vby_id VIEWBYID
,qry.vby_id VIEWBY
,qry.curr_hdc_end HRI_P_MEASURE1
,qry.comp_hdc_end HRI_P_MEASURE2
,DECODE(qry.curr_pasg_cnt_end,0,0,qry.curr_low_end/(365*qry.curr_pasg_cnt_end))
HRI_P_MEASURE4
,DECODE(qry.comp_pasg_cnt_end,0,0,qry.comp_low_end/(365*qry.comp_pasg_cnt_end))
HRI_P_MEASURE5
,qry.curr_hdc_end HRI_P_GRAND_TOTAL1
,qry.comp_hdc_end HRI_P_GRAND_TOTAL2
,DECODE(qry.curr_pasg_cnt_end,0,0,qry.curr_low_end/(365*qry.curr_pasg_cnt_end))
HRI_P_GRAND_TOTAL4
,DECODE(qry.comp_pasg_cnt_end,0,0,qry.comp_low_end/(365*qry.comp_pasg_cnt_end))
HRI_P_GRAND_TOTAL5
FROM
('||l_trend_sql||') qry
WHERE 1=1
' || l_security_clause;
'SELECT -- Contingent Worker Headcount KPI
qry.vby_id VIEWBYID
,qry.vby_id VIEWBY
,qry.curr_hdc_cwk HRI_P_MEASURE1
,qry.comp_hdc_cwk HRI_P_MEASURE2
,DECODE(qry.curr_pasg_cnt_cwk,0,0,qry.curr_low_cwk/(:MONTHS_MULTIPLIER*qry.curr_pasg_cnt_cwk))
HRI_P_MEASURE4
,DECODE(qry.comp_pasg_cnt_cwk,0,0,qry.comp_low_cwk/(:MONTHS_MULTIPLIER*qry.comp_pasg_cnt_cwk))
HRI_P_MEASURE5
,qry.curr_hdc_cwk HRI_P_GRAND_TOTAL1
,qry.comp_hdc_cwk HRI_P_GRAND_TOTAL2
,DECODE(qry.curr_pasg_cnt_cwk,0,0,qry.curr_low_cwk/(:MONTHS_MULTIPLIER*qry.curr_pasg_cnt_cwk))
HRI_P_GRAND_TOTAL4
,DECODE(qry.comp_pasg_cnt_cwk,0,0,qry.comp_low_cwk/(:MONTHS_MULTIPLIER*qry.comp_pasg_cnt_cwk))
HRI_P_GRAND_TOTAL5
FROM
('||l_wrkfc_fact_sql||') qry
WHERE 1=1' || g_rtn
||l_security_clause;
'SELECT -- Contingent Worker Activity KPI
wmv.vby_id VIEWBYID
,wmv.vby_id VIEWBY
,wmv.curr_hdc_cwk HRI_P_MEASURE1
,wmv.comp_hdc_cwk HRI_P_MEASURE2
,NVL(atvty.curr_hire_hdc_cwk,0) HRI_P_MEASURE4
,NVL(atvty.comp_hire_hdc_cwk,0) HRI_P_MEASURE5
,NVL(atvty.curr_termination_hdc_cwk,0) HRI_P_MEASURE7
,NVL(atvty.comp_termination_hdc_cwk,0) HRI_P_MEASURE8
,wmv.curr_hdc_cwk HRI_P_GRAND_TOTAL1
,wmv.comp_hdc_cwk HRI_P_GRAND_TOTAL2
,NVL(atvty.curr_hire_hdc_cwk,0) HRI_P_GRAND_TOTAL4
,NVL(atvty.comp_hire_hdc_cwk,0) HRI_P_GRAND_TOTAL5
,NVL(atvty.curr_termination_hdc_cwk,0) HRI_P_GRAND_TOTAL7
,NVL(atvty.comp_termination_hdc_cwk,0) HRI_P_GRAND_TOTAL8
FROM
('||l_wcnt_chg_fact_sql||') atvty ' || g_rtn
||',('||l_wrkfc_fact_sql||') wmv
WHERE 1=1
AND wmv.vby_id = atvty.vby_id (+)' || g_rtn
||l_security_clause;