[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SUP_BCKT
Source
1 PACKAGE BODY hri_oltp_pmv_wmv_sup_bckt AS
2 /* $Header: hriophrp.pkb 120.5 2005/10/26 07:53:52 jrstewar noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6
7 TYPE dynamic_sql_rec_type IS RECORD
8 (viewby_condition VARCHAR2(1000),
9 wrkfc_outer_join VARCHAR2(5),
10 drill_mgr_sup VARCHAR2(1000),
11 drill_to_detail VARCHAR2(1000),
12 display_row_condition VARCHAR2(1000),
13 order_by VARCHAR2(1000),
14 wkth_wktyp_sk_fk VARCHAR2(10),
15 drill_url2 VARCHAR2(1000),
16 drill_url3 VARCHAR2(1000),
17 drill_url4 VARCHAR2(1000),
18 drill_url5 VARCHAR2(1000),
19 drill_url6 VARCHAR2(1000),
20 drill_url7 VARCHAR2(1000),
21 drill_url8 VARCHAR2(1000),
22 drill_url9 VARCHAR2(1000),
23 drill_url10 VARCHAR2(1000)
24 );
25
26
27 /* function to return the sk_fk of the dimension view for the given bucket number */
28 FUNCTION convert_low_bckt_to_sk_fk (p_bucket_dim IN VARCHAR2
29 ,p_bucket_column_id IN NUMBER -- [1 .. 5]
30 ,p_wkth_wktyp_sk_fk IN VARCHAR2 -- ['EMP','CWK']
31 ) RETURN NUMBER
32 IS
33 CURSOR cur_conv_bckt IS
34 SELECT pow_band_sk_pk
35 FROM hri_cs_pow_band_ct pow
36 WHERE pow.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
37 AND pow.band_sequence =p_bucket_column_id;
38
39 l_pow_band_sk_pk NUMBER := -1;
40
41 BEGIN
42
43 OPEN cur_conv_bckt;
44 FETCH cur_conv_bckt INTO l_pow_band_sk_pk;
45 CLOSE cur_conv_bckt;
46
47 RETURN l_pow_band_sk_pk;
48
49 EXCEPTION WHEN OTHERS THEN
50 CLOSE cur_conv_bckt;
51 RETURN l_pow_band_sk_pk;
52
53 END convert_low_bckt_to_sk_fk;
54
55
56 /* Dynamically changes report SQL depending on parameters */
57 PROCEDURE set_dynamic_sql
58 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
59 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
60 p_bucket_dim IN VARCHAR2,
61 p_dynsql_rec OUT NOCOPY dynamic_sql_rec_type) IS
62
63 l_drill_to_function VARCHAR2(40);
64 l_drill_to_function_direct
65 VARCHAR2(40);
66 l_wkth_wktyp_sk_fk VARCHAR2(10);
67 l_parameter_count PLS_INTEGER;
68 l_parameter_name VARCHAR2(80);
69
70 BEGIN
71
72 /* Set the order by */
73 p_dynsql_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
74 (p_order_by_clause => p_parameter_rec.order_by);
75
76 /* View by Person */
77 /******************/
78 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
79
80 IF (p_parameter_rec.bis_region_code = 'HRI_P_WMV_BCKT_LOW' OR
81 p_parameter_rec.bis_region_code = 'HRI_P_WMV_BCKT_LOW_PVT') THEN
82 l_drill_to_function := 'HRI_P_WMV_BCKT_LOW_PVT';
83 l_drill_to_function_direct := 'HRI_P_WMV_SAL_SUP_DTL';
84 ELSIF (p_parameter_rec.bis_region_code = 'HRI_P_WMV_BCKT_PERF' OR
85 p_parameter_rec.bis_region_code = 'HRI_P_WMV_BCKT_PERF_PVT') THEN
86 l_drill_to_function := 'HRI_P_WMV_BCKT_PERF_PVT';
87 l_drill_to_function_direct := 'HRI_P_WMV_SAL_SUP_DTL';
88 ELSIF (p_parameter_rec.bis_region_code = 'HRI_P_WMV_C_BCKT_LOP_SUP' OR
89 p_parameter_rec.bis_region_code = 'HRI_P_WMV_C_BCKT_LOP_PVT') THEN
90 l_drill_to_function := 'HRI_P_WMV_C_BCKT_LOP_PVT';
91 l_drill_to_function_direct := 'HRI_P_WMV_C_SUP_DTL';
92 END IF;
93
94 /* Set drill URLs */
95 p_dynsql_rec.drill_mgr_sup := 'pFunctionName=' || l_drill_to_function || '&' ||
96 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
97 'VIEW_BY_NAME=VIEW_BY_ID&' ||
98 'pParamIds=Y';
99
100 p_dynsql_rec.drill_to_detail := 'pFunctionName=' || l_drill_to_function_direct || '&' ||
101 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
102 'VIEW_BY_NAME=VIEW_BY_ID&' ||
103 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
104 'pParamIds=Y';
105
106 p_dynsql_rec.viewby_condition :=
107 ' AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date' || g_rtn;
108
109 END IF;
110
111 /* Set the display row conditions */
112 IF (p_parameter_rec.view_by = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
113 p_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
114
115 /* If view by is performance or length of work display all cl view rows */
116 /* regardless of whether there is any headcount or turnover */
117 p_dynsql_rec.wrkfc_outer_join := '(+)';
118
119 /* Filter if a view by parameter is set */
120 p_dynsql_rec.viewby_condition := hri_oltp_pmv_util_pkg.set_viewby_filter
121 (p_parameter_rec => p_parameter_rec,
122 p_bind_tab => p_bind_tab,
123 p_view_by_alias => 'vby');
124
125 ELSE
126
127 /* Only display rows with current headcount */
128 p_dynsql_rec.display_row_condition :=
129 'AND (qry.curr_hdc > 0
130 OR qry.direct_ind = 1)' || g_rtn;
131
132 END IF;
133
134 /* format the dynamic drill urls for certain regions only */
135 IF ( p_parameter_rec.bis_region_code = 'HRI_P_WMV_BCKT_LOW_PVT'
136 or p_parameter_rec.bis_region_code = 'HRI_P_WMV_C_BCKT_LOP_PVT') THEN
137
138 /* set the corresponding drill to detail region code function */
139 IF p_parameter_rec.bis_region_code = 'HRI_P_WMV_BCKT_LOW_PVT' THEN
140 l_drill_to_function_direct := 'HRI_P_WMV_SAL_SUP_DTL';
141 ELSIF p_parameter_rec.bis_region_code = 'HRI_P_WMV_C_BCKT_LOP_PVT' THEN
142 l_drill_to_function_direct := 'HRI_P_WMV_C_SUP_DTL';
143 END IF;
144
145 /* format the additional common dynamic drill urls */
146 p_dynsql_rec.drill_url2 := 'pFunctionName='||l_drill_to_function_direct ||'&'||
147 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&'||
148 'VIEW_BY_NAME=VIEW_BY_ID&'||
149 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&'||
150 'HRI_LOW+HRI_LOW_BAND_X='||
151 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
152 ,1 -- bucket
153 ,p_parameter_rec.wkth_wktyp_sk_fk)
154 ||'&'||
155 'pParamIds=Y';
156 p_dynsql_rec.drill_url3 := 'pFunctionName='||l_drill_to_function_direct ||'&'||
157 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&'||
158 'VIEW_BY_NAME=VIEW_BY_ID&'||
159 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&'||
160 'HRI_LOW+HRI_LOW_BAND_X='||
161 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
162 ,2 -- bucket
163 ,p_parameter_rec.wkth_wktyp_sk_fk)
164 ||'&'||
165 'pParamIds=Y';
166 p_dynsql_rec.drill_url4 := 'pFunctionName='||l_drill_to_function_direct ||'&'||
167 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&'||
168 'VIEW_BY_NAME=VIEW_BY_ID&'||
169 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&'||
170 'HRI_LOW+HRI_LOW_BAND_X='||
171 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
172 ,3 -- bucket
173 ,p_parameter_rec.wkth_wktyp_sk_fk)
174 ||'&'||
175 'pParamIds=Y';
176 p_dynsql_rec.drill_url5 := 'pFunctionName='||l_drill_to_function_direct ||'&'||
177 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&'||
178 'VIEW_BY_NAME=VIEW_BY_ID&'||
179 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&'||
180 'HRI_LOW+HRI_LOW_BAND_X='||
181 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
182 ,4 -- bucket
183 ,p_parameter_rec.wkth_wktyp_sk_fk)
184 ||'&'||
185 'pParamIds=Y';
186 p_dynsql_rec.drill_url6 := 'pFunctionName='||l_drill_to_function_direct ||'&'||
187 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&'||
188 'VIEW_BY_NAME=VIEW_BY_ID&'||
189 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&'||
190 'HRI_LOW+HRI_LOW_BAND_X='||
191 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
192 ,5 -- bucket
193 ,p_parameter_rec.wkth_wktyp_sk_fk)
194 ||'&'||
195 'pParamIds=Y';
196 END IF;
197
198
199 END set_dynamic_sql;
200
201
202 /* Entry point for Headcount for Performance Band SQL */
203 PROCEDURE get_sql_bckt_perf
204 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
205 x_custom_sql OUT NOCOPY VARCHAR2,
206 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
207
208 /* Return information */
209 l_sqltext VARCHAR2(32767);
210 l_custom_rec BIS_QUERY_ATTRIBUTES;
211
212 /* Dynamic SQL support */
213 l_dynsql_rec dynamic_sql_rec_type;
214 l_security_clause VARCHAR2(4000);
215 l_direct_reports_string VARCHAR2(100);
216
217 /* Dynamic SQL Controls */
218 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
219 l_wrkfc_fact_sql VARCHAR2(10000);
220
221 /* Parameter values */
222 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
223 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
224
225 BEGIN
226
227 /* Initialize out parameters */
228 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
229 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
230
231
232 /* Get common parameter values */
233 hri_oltp_pmv_util_param.get_parameters_from_table
234 (p_page_parameter_tbl => p_page_parameter_tbl,
235 p_parameter_rec => l_parameter_rec,
236 p_bind_tab => l_bind_tab);
237
238 /* Set the dynamic sql portion */
239 set_dynamic_sql(p_parameter_rec => l_parameter_rec,
240 p_bind_tab => l_bind_tab,
241 p_bucket_dim => 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X',
242 p_dynsql_rec => l_dynsql_rec);
243
244 /* Get security clause for Manager based security */
245 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
246
247 /* Get direct reports string */
248 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
249
250 /* Get SQL for workforce fact */
251 l_wrkfc_fact_params.bind_format := 'PMV';
252 l_wrkfc_fact_params.include_comp := 'Y';
253 l_wrkfc_fact_params.include_hdc := 'Y';
254 l_wrkfc_fact_params.bucket_dim := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
255 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
256 (p_parameter_rec => l_parameter_rec,
257 p_bind_tab => l_bind_tab,
258 p_wrkfc_params => l_wrkfc_fact_params,
259 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_BCKT.GET_SQL_BCKT_PERF');
260
261 l_sqltext :=
262 'SELECT -- Employee Headcount Ratio for Perf Band
263 qry.id VIEWBYID
264 ,qry.value VIEWBY
265 ,DECODE(qry.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
266 /* Band 3 - High Performers */
267 ',qry.curr_hdc_b3 HRI_P_MEASURE3
268 ,DECODE(qry.curr_hdc, 0, 0,
269 (qry.curr_hdc_b3/qry.curr_hdc)*100)
270 HRI_P_MEASURE3_MP
271 ,qry.comp_hdc_b3 HRI_P_MEASURE8
272 ,DECODE(qry.comp_hdc_b3, 0, to_number(NULL),
273 (qry.curr_hdc_b3 - qry.comp_hdc_b3) * 100
274 / qry.comp_hdc_b3) HRI_P_MEASURE8_MP' || g_rtn ||
275 /* Band 2 - Average Performers */
276 ',qry.curr_hdc_b2 HRI_P_MEASURE2
277 ,DECODE(qry.curr_hdc, 0, 0,
278 (qry.curr_hdc_b2/qry.curr_hdc)*100)
279 HRI_P_MEASURE2_MP
280 ,qry.comp_hdc_b2 HRI_P_MEASURE9
281 ,DECODE(qry.comp_hdc_b2, 0, to_number(NULL),
282 (qry.curr_hdc_b2 - qry.comp_hdc_b2) * 100
283 / qry.comp_hdc_b2) HRI_P_MEASURE9_MP' || g_rtn ||
284 /* Band 1 - Low Performers */
285 ',qry.curr_hdc_b1 HRI_P_MEASURE1
286 ,DECODE(qry.curr_hdc, 0, 0,
287 (qry.curr_hdc_b1/qry.curr_hdc)*100)
288 HRI_P_MEASURE1_MP
289 ,qry.comp_hdc_b1 HRI_P_MEASURE10
290 ,DECODE(qry.comp_hdc_b1, 0, to_number(NULL),
291 (qry.curr_hdc_b1 - qry.comp_hdc_b1) * 100
292 / qry.comp_hdc_b1) HRI_P_MEASURE10_MP' || g_rtn ||
293 /* Unassigned band - no performance rating */
294 ',qry.curr_hdc_na HRI_P_MEASURE6
295 ,DECODE(qry.curr_hdc_na, 0, 0,
296 (qry.curr_hdc_na/qry.curr_hdc)*100)
297 HRI_P_MEASURE6_MP
298 ,qry.comp_hdc_na HRI_P_MEASURE11
299 ,DECODE(qry.comp_hdc_na, 0, to_number(NULL),
300 (qry.curr_hdc_na - qry.comp_hdc_na) * 100
301 / qry.comp_hdc_na) HRI_P_MEASURE11_MP' || g_rtn ||
302 /* Row totals - Across all performance bands */
303 ',qry.curr_hdc HRI_P_MEASURE7
304 ,qry.comp_hdc HRI_P_MEASURE16' || g_rtn ||
305 /* Order by */
306 ',qry.order_by HRI_P_ORDER_BY_1' || g_rtn ||
307 /* Grand Totals */
308 ',DECODE(qry.total_curr_hdc, 0, 0,
309 (qry.total_curr_hdc_b3/qry.total_curr_hdc) * 100)
310 HRI_P_GRAND_TOTAL1
311 ,DECODE(qry.total_curr_hdc, 0, 0,
312 (qry.total_curr_hdc_b2/qry.total_curr_hdc) * 100)
313 HRI_P_GRAND_TOTAL2
314 ,DECODE(qry.total_curr_hdc, 0, 0,
315 (qry.total_curr_hdc_b1/qry.total_curr_hdc) * 100)
316 HRI_P_GRAND_TOTAL3
317 ,DECODE(qry.total_curr_hdc, 0, 0,
318 (qry.total_curr_hdc_na/qry.total_curr_hdc) * 100)
319 HRI_P_GRAND_TOTAL4
320 ,qry.total_comp_hdc_b3 HRI_P_GRAND_TOTAL5
321 ,DECODE(qry.total_comp_hdc_b3, 0, 0,
322 (qry.total_curr_hdc_b3 - qry.total_comp_hdc_b3) * 100/qry.total_comp_hdc_b3)
323 HRI_P_GRAND_TOTAL6
324 ,qry.total_comp_hdc_b2 HRI_P_GRAND_TOTAL7
325 ,DECODE(qry.total_comp_hdc_b2, 0, 0,
326 (qry.total_curr_hdc_b2 - qry.total_comp_hdc_b2) * 100/qry.total_comp_hdc_b2)
327 HRI_P_GRAND_TOTAL8
328 ,qry.total_comp_hdc_b1 HRI_P_GRAND_TOTAL9
329 ,DECODE(qry.total_comp_hdc_b1, 0, 0,
330 (qry.total_curr_hdc_b1 - qry.total_comp_hdc_b1) * 100/qry.total_comp_hdc_b1)
331 HRI_P_GRAND_TOTAL10
332 ,qry.total_comp_hdc_na HRI_P_GRAND_TOTAL11
333 ,DECODE(qry.total_comp_hdc_na, 0, 0,
334 (qry.total_curr_hdc_na - qry.total_comp_hdc_na) * 100/qry.total_comp_hdc_na)
335 HRI_P_GRAND_TOTAL12
336 ,qry.total_comp_hdc HRI_P_GRAND_TOTAL13
337 ,DECODE(qry.direct_ind,0,'''',''N'') HRI_P_SUPH_RO_CA' || g_rtn ||
338 /* Drill URLs */
339 ', DECODE(qry.direct_ind,
340 0, ''' || l_dynsql_rec.drill_mgr_sup || ''',
341 ''' || l_dynsql_rec.drill_to_detail || ''') HRI_P_DRILL_URL1
342 FROM
343 (SELECT
344 /* View by */
345 vby.id
346 ,DECODE(wmv.direct_ind,
347 1, ''' || l_direct_reports_string || ''',
348 vby.value) value
349 ,to_char(NVL(wmv.direct_ind, 0)) || vby.order_by order_by
350 ,NVL(wmv.curr_hdc_b1, 0) curr_hdc_b1
351 ,NVL(wmv.curr_hdc_b2, 0) curr_hdc_b2
352 ,NVL(wmv.curr_hdc_b3, 0) curr_hdc_b3
353 ,NVL(wmv.curr_hdc_na, 0) curr_hdc_na
354 ,NVL(wmv.curr_hdc_end, 0) curr_hdc
355 ,NVL(wmv.comp_hdc_b1, 0) comp_hdc_b1
356 ,NVL(wmv.comp_hdc_b2, 0) comp_hdc_b2
357 ,NVL(wmv.comp_hdc_b3, 0) comp_hdc_b3
358 ,NVL(wmv.comp_hdc_na, 0) comp_hdc_na
359 ,NVL(wmv.comp_hdc_end, 0) comp_hdc
360 ,NVL(SUM(wmv.curr_hdc_b1) OVER (), 0) total_curr_hdc_b1
361 ,NVL(SUM(wmv.curr_hdc_b2) OVER (), 0) total_curr_hdc_b2
362 ,NVL(SUM(wmv.curr_hdc_b3) OVER (), 0) total_curr_hdc_b3
363 ,NVL(SUM(wmv.curr_hdc_na) OVER (), 0) total_curr_hdc_na
364 ,NVL(SUM(wmv.curr_hdc_end) OVER (), 0) total_curr_hdc
365 ,NVL(SUM(wmv.comp_total_hdc_b1) OVER (), 0) total_comp_hdc_b1
366 ,NVL(SUM(wmv.comp_total_hdc_b2) OVER (), 0) total_comp_hdc_b2
367 ,NVL(SUM(wmv.comp_total_hdc_b3) OVER (), 0) total_comp_hdc_b3
368 ,NVL(SUM(wmv.comp_total_hdc_na) OVER (), 0) total_comp_hdc_na
369 ,NVL(SUM(wmv.comp_total_hdc_end) OVER (), 0) total_comp_hdc
370 ,NVL(wmv.direct_ind, 0) direct_ind
371 FROM
372 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
373 (l_parameter_rec.view_by).viewby_table || ' vby
374 ,(' || l_wrkfc_fact_sql || ') wmv
375 WHERE wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id' || g_rtn ||
376 l_dynsql_rec.viewby_condition ||
377 ' ) qry
378 WHERE 1 = 1' || g_rtn ||
379 l_dynsql_rec.display_row_condition ||
380 l_security_clause || '
381 ORDER BY ' || l_dynsql_rec.order_by;
382
383 x_custom_sql := l_SQLText;
384
385 END get_sql_bckt_perf;
386
387 /* Entry point for Headcount for Period of Work Band SQL */
388 PROCEDURE get_sql_bckt_low
389 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
390 x_custom_sql OUT NOCOPY VARCHAR2,
391 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
392
393 /* Return information */
394 l_sqltext VARCHAR2(32767);
395 l_custom_rec BIS_QUERY_ATTRIBUTES;
396
397 /* Dynamic SQL support */
398 l_dynsql_rec dynamic_sql_rec_type;
399 l_security_clause VARCHAR2(4000);
400 l_direct_reports_string VARCHAR2(100);
401
402 /* Parameter values */
403 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
404 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
405
406 /* Dynamic SQL Controls */
407 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
408 l_wrkfc_fact_sql VARCHAR2(10000);
409
410 /* Drill URLs for column drills on Headcount figure */
411 l_drill_url2 VARCHAR2(1000) := '''''';
412 l_drill_url3 VARCHAR2(1000) := '''''';
413 l_drill_url4 VARCHAR2(1000) := '''''';
414 l_drill_url5 VARCHAR2(1000) := '''''';
415 l_drill_url6 VARCHAR2(1000) := '''''';
416
417 BEGIN
418
419 /* Initialize out parameters */
420 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
421 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
422 /* Get common parameter values */
423 hri_oltp_pmv_util_param.get_parameters_from_table
424 (p_page_parameter_tbl => p_page_parameter_tbl,
425 p_parameter_rec => l_parameter_rec,
426 p_bind_tab => l_bind_tab);
427
428 set_dynamic_sql(p_parameter_rec => l_parameter_rec,
429 p_bind_tab => l_bind_tab,
430 p_bucket_dim => 'HRI_LOW+HRI_LOW_BAND_X',
431 p_dynsql_rec => l_dynsql_rec);
432
433 /* set the dynamic drill url AK region items */
434 l_drill_url2 := l_dynsql_rec.drill_url2;
435 l_drill_url3 := l_dynsql_rec.drill_url3;
436 l_drill_url4 := l_dynsql_rec.drill_url4;
437 l_drill_url5 := l_dynsql_rec.drill_url5;
438 l_drill_url6 := l_dynsql_rec.drill_url6;
439
440 /* Get security clause for Manager based security */
441 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
442
443 /* Get direct reports string */
444 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
445
446 /* Get SQL for workforce fact */
447 l_wrkfc_fact_params.bind_format := 'PMV';
448 l_wrkfc_fact_params.include_comp := 'Y';
449 l_wrkfc_fact_params.include_hdc := 'Y';
450 l_wrkfc_fact_params.bucket_dim := 'HRI_LOW+HRI_LOW_BAND_X';
451 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
452 (p_parameter_rec => l_parameter_rec,
453 p_bind_tab => l_bind_tab,
454 p_wrkfc_params => l_wrkfc_fact_params,
455 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_BCKT.GET_SQL_BCKT_LOW');
456
457 l_sqltext :=
458 'SELECT -- Headcount Ratio for LOW Band (Generic)
459 qry.id VIEWBYID
460 ,qry.value VIEWBY
461 ,DECODE(qry.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
462 /* Band 1: 0 - 1 Years */
463 ',qry.curr_hdc_b1 HRI_P_MEASURE1
464 ,'''||l_drill_url2||''' HRI_P_DRILL_URL2
465 ,DECODE(qry.curr_hdc, 0, 0,
466 (qry.curr_hdc_b1/qry.curr_hdc)*100)
467 HRI_P_MEASURE1_MP
468 ,qry.comp_hdc_b1 HRI_P_MEASURE2
469 ,DECODE(qry.comp_hdc_b1, 0, to_number(NULL),
470 (qry.curr_hdc_b1 - qry.comp_hdc_b1) * 100
471 / qry.comp_hdc_b1) HRI_P_MEASURE2_MP' || g_rtn ||
472 /* Band 2: 1 - 3 Years */
473 ',qry.curr_hdc_b2 HRI_P_MEASURE3
474 ,'''||l_drill_url3||''' HRI_P_DRILL_URL3
475 ,DECODE(qry.curr_hdc, 0, 0,
476 (qry.curr_hdc_b2/qry.curr_hdc)*100)
477 HRI_P_MEASURE3_MP
478 ,qry.comp_hdc_b2 HRI_P_MEASURE4
479 ,DECODE(qry.comp_hdc_b2, 0, to_number(NULL),
480 (qry.curr_hdc_b2 - qry.comp_hdc_b2) * 100
481 / qry.comp_hdc_b2) HRI_P_MEASURE4_MP' || g_rtn ||
482 /* Band 3: 3 - 5 Years */
483 ',qry.curr_hdc_b3 HRI_P_MEASURE5
484 ,'''||l_drill_url4||''' HRI_P_DRILL_URL4
485 ,DECODE(qry.curr_hdc, 0, 0,
486 (qry.curr_hdc_b3/qry.curr_hdc)*100)
487 HRI_P_MEASURE5_MP
488 ,qry.comp_hdc_b3 HRI_P_MEASURE6
489 ,DECODE(qry.comp_hdc_b3, 0, to_number(NULL),
490 (qry.curr_hdc_b3 - qry.comp_hdc_b3) * 100
491 / qry.comp_hdc_b3) HRI_P_MEASURE6_MP' || g_rtn ||
492 /* Band 4: 5 - 10 Years */
493 ',qry.curr_hdc_b4 HRI_P_MEASURE7
494 ,'''||l_drill_url5||''' HRI_P_DRILL_URL5
495 ,DECODE(qry.curr_hdc_b4, 0, 0,
496 (qry.curr_hdc_b4/qry.curr_hdc)*100)
497 HRI_P_MEASURE7_MP
498 ,qry.comp_hdc_b4 HRI_P_MEASURE8
499 ,DECODE(qry.comp_hdc_b4, 0, to_number(NULL),
500 (qry.curr_hdc_b4 - qry.comp_hdc_b4) * 100
501 / qry.comp_hdc_b4) HRI_P_MEASURE8_MP' || g_rtn ||
502 /* Band 5: 10 + Years */
503 ',qry.curr_hdc_b5 HRI_P_MEASURE9
504 ,'''||l_drill_url6||''' HRI_P_DRILL_URL6
505 ,DECODE(qry.curr_hdc_b5, 0, 0,
506 (qry.curr_hdc_b5/qry.curr_hdc)*100)
507 HRI_P_MEASURE9_MP
508 ,qry.comp_hdc_b5 HRI_P_MEASURE10
509 ,DECODE(qry.comp_hdc_b5, 0, to_number(NULL),
510 (qry.curr_hdc_b5 - qry.comp_hdc_b5) * 100
511 / qry.comp_hdc_b5) HRI_P_MEASURE10_MP' || g_rtn ||
512 /* Row totals - Across all performance bands */
513 ',qry.curr_hdc HRI_P_MEASURE11
514 ,qry.comp_hdc HRI_P_MEASURE12' || g_rtn ||
515 /* Order by */
516 ',qry.order_by HRI_P_ORDER_BY_1' || g_rtn ||
517 /* Grand Totals */
518 ',DECODE(qry.total_curr_hdc, 0, 0,
519 (qry.total_curr_hdc_b1/qry.total_curr_hdc) * 100)
520 HRI_P_GRAND_TOTAL1
521 ,DECODE(qry.total_curr_hdc, 0, 0,
522 (qry.total_curr_hdc_b2/qry.total_curr_hdc) * 100)
523 HRI_P_GRAND_TOTAL2
524 ,DECODE(qry.total_curr_hdc, 0, 0,
525 (qry.total_curr_hdc_b3/qry.total_curr_hdc) * 100)
526 HRI_P_GRAND_TOTAL3
527 ,DECODE(qry.total_curr_hdc, 0, 0,
528 (qry.total_curr_hdc_b4/qry.total_curr_hdc) * 100)
529 HRI_P_GRAND_TOTAL4
530 ,DECODE(qry.total_curr_hdc, 0, 0,
531 (qry.total_curr_hdc_b5/qry.total_curr_hdc) * 100)
532 HRI_P_GRAND_TOTAL5
533 ,qry.total_comp_hdc_b1 HRI_P_GRAND_TOTAL6
534 ,DECODE(qry.total_comp_hdc_b1, 0, 0,
535 (qry.total_curr_hdc_b1 - qry.total_comp_hdc_b1) * 100/qry.total_comp_hdc_b1)
536 HRI_P_GRAND_TOTAL7
537 ,qry.total_comp_hdc_b2 HRI_P_GRAND_TOTAL8
538 ,DECODE(qry.total_comp_hdc_b2, 0, 0,
539 (qry.total_curr_hdc_b2 - qry.total_comp_hdc_b2) * 100/qry.total_comp_hdc_b2)
540 HRI_P_GRAND_TOTAL9
541 ,qry.total_comp_hdc_b3 HRI_P_GRAND_TOTAL10
542 ,DECODE(qry.total_comp_hdc_b3, 0, 0,
543 (qry.total_curr_hdc_b3 - qry.total_comp_hdc_b3) * 100/qry.total_comp_hdc_b3)
544 HRI_P_GRAND_TOTAL11
545 ,qry.total_comp_hdc_b4 HRI_P_GRAND_TOTAL12
546 ,DECODE(qry.total_comp_hdc_b4, 0, 0,
547 (qry.total_curr_hdc_b4 - qry.total_comp_hdc_b4) * 100/qry.total_comp_hdc_b4)
548 HRI_P_GRAND_TOTAL13
549 ,qry.total_comp_hdc_b5 HRI_P_GRAND_TOTAL14
550 ,DECODE(qry.total_comp_hdc_b5, 0, 0,
551 (qry.total_curr_hdc_b5 - qry.total_comp_hdc_b5) * 100/qry.total_comp_hdc_b5)
552 HRI_P_GRAND_TOTAL15
553 ,qry.total_comp_hdc HRI_P_GRAND_TOTAL16
554 ,DECODE(qry.direct_ind,0,'''',''N'') HRI_P_SUPH_RO_CA' || g_rtn ||
555 /* Drill URLs */
556 ', DECODE(qry.direct_ind,
557 0, ''' || l_dynsql_rec.drill_mgr_sup || ''',
558 ''' || l_dynsql_rec.drill_to_detail || ''') HRI_P_DRILL_URL1
559 FROM
560 (SELECT
561 /* View by */
562 vby.id
563 ,DECODE(wmv.direct_ind,
564 1, ''' || l_direct_reports_string || ''',
565 vby.value) value
566 ,to_char(NVL(wmv.direct_ind, 0)) || vby.order_by order_by
567 ,NVL(wmv.curr_hdc_b1, 0) curr_hdc_b1
568 ,NVL(wmv.curr_hdc_b2, 0) curr_hdc_b2
569 ,NVL(wmv.curr_hdc_b3, 0) curr_hdc_b3
570 ,NVL(wmv.curr_hdc_b4, 0) curr_hdc_b4
571 ,NVL(wmv.curr_hdc_b5, 0) curr_hdc_b5
572 ,NVL(wmv.curr_hdc_end, 0) curr_hdc
573 ,NVL(wmv.comp_hdc_b1, 0) comp_hdc_b1
574 ,NVL(wmv.comp_hdc_b2, 0) comp_hdc_b2
575 ,NVL(wmv.comp_hdc_b3, 0) comp_hdc_b3
576 ,NVL(wmv.comp_hdc_b4, 0) comp_hdc_b4
577 ,NVL(wmv.comp_hdc_b5, 0) comp_hdc_b5
578 ,NVL(wmv.comp_hdc_end, 0) comp_hdc
579 ,SUM(wmv.curr_hdc_b1) OVER () total_curr_hdc_b1
580 ,SUM(wmv.curr_hdc_b2) OVER () total_curr_hdc_b2
581 ,SUM(wmv.curr_hdc_b3) OVER () total_curr_hdc_b3
582 ,SUM(wmv.curr_hdc_b4) OVER () total_curr_hdc_b4
583 ,SUM(wmv.curr_hdc_b5) OVER () total_curr_hdc_b5
584 ,SUM(wmv.curr_hdc_end) OVER () total_curr_hdc
585 ,SUM(wmv.comp_total_hdc_b1) OVER () total_comp_hdc_b1
586 ,SUM(wmv.comp_total_hdc_b2) OVER () total_comp_hdc_b2
587 ,SUM(wmv.comp_total_hdc_b3) OVER () total_comp_hdc_b3
588 ,SUM(wmv.comp_total_hdc_b4) OVER () total_comp_hdc_b4
589 ,SUM(wmv.comp_total_hdc_b5) OVER () total_comp_hdc_b5
590 ,SUM(wmv.comp_total_hdc_end) OVER () total_comp_hdc
591 ,NVL(wmv.direct_ind, 0) direct_ind
592 FROM
593 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
594 (l_parameter_rec.view_by).viewby_table || ' vby,
595 (' || l_wrkfc_fact_sql || ') wmv
596 WHERE wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id' || g_rtn ||
597 l_dynsql_rec.viewby_condition ||
598 ' ) qry
599 WHERE 1 = 1' || g_rtn ||
600 l_dynsql_rec.display_row_condition ||
601 l_security_clause || '
602 ORDER BY ' || l_dynsql_rec.order_by;
603
604 x_custom_sql := l_SQLText;
605
606 END get_sql_bckt_low;
607 --
608 -- ----------------------------------------------------------------------
609 -- Procedure to fetch the headcount by performance KPI
610 -- It fetched the values for the following KPIs
611 -- 1. Headcount for High Band
612 -- 2. Previous Headcount for High Band
613 -- 3. Headcount for Mid Band
614 -- 4. Previous Headcount for Mid Band
615 -- 5. Headcount for Low Band
616 -- 6. Previous Headcount for Low Band
617 -- 7. Headcount for NA Band
618 -- 8. Previous Headcount for NA Band
619 -- ----------------------------------------------------------------------
620 --
621 PROCEDURE get_wmv_perf_kpi
622 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
623 x_custom_sql OUT NOCOPY VARCHAR2,
624 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
625 --
626 -- The security clause
627 --
628 l_security_clause VARCHAR2(4000);
629 --
630 -- Page parameters
631 --
632 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
633 --
634 -- Bind values for SQL and PMV mode
635 --
636 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
637 --
638 -- Parameter values for getting the inner SQL
639 --
640 l_wrkfc_params hri_bpl_fact_sup_wrkfc_sql.WRKFC_FACT_PARAM_TYPE;
641 --
642 -- Inner SQL
643 --
644 l_inn_sql VARCHAR2(32767);
645 l_custom_rec BIS_QUERY_ATTRIBUTES;
646 --
647 BEGIN
648 --
649 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
650 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
651 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
652 --
653 -- Get the parameter information from the page parameter table
654 --
655 hri_oltp_pmv_util_param.get_parameters_from_table
656 (p_page_parameter_tbl => p_page_parameter_tbl,
657 p_parameter_rec => l_parameter_rec,
658 p_bind_tab => l_bind_tab);
659 --
660 --
661 -- Set the parameters for getting the inner SQL
662 --
663 l_wrkfc_params.bind_format := 'PMV';
664 l_wrkfc_params.include_comp := 'Y';
665 l_wrkfc_params.include_hdc := 'Y';
666 l_wrkfc_params.kpi_mode := 'Y';
667 l_wrkfc_params.bucket_dim := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
668 --
669 -- Get the inner SQL
670 --
671 l_inn_sql := HRI_OLTP_PMV_QUERY_WRKFC.get_sql
672 (p_parameter_rec => l_parameter_rec,
673 p_bind_tab => l_bind_tab,
674 p_wrkfc_params => l_wrkfc_params,
675 p_calling_module => 'hri_oltp_pmv_wmv_sup_bckt.GET_WMV_PERF_KPI');
676 --
677 -- Form the SQL
678 --
679 x_custom_sql :=
680 'SELECT -- Headcount by Performance KPI
681 qry.vby_id VIEWBYID
682 ,qry.vby_id VIEWBY
683 ,curr_hdc_b3 HRI_P_MEASURE1
684 ,comp_hdc_b3 HRI_P_MEASURE2
685 ,curr_hdc_b2 HRI_P_MEASURE4
686 ,comp_hdc_b2 HRI_P_MEASURE5
687 ,curr_hdc_b1 HRI_P_MEASURE7
688 ,comp_hdc_b1 HRI_P_MEASURE8
689 ,curr_hdc_na HRI_P_MEASURE10
690 ,comp_hdc_na HRI_P_MEASURE11
691 ,curr_hdc_b3 HRI_P_GRAND_TOTAL1
692 ,comp_hdc_b3 HRI_P_GRAND_TOTAL2
693 ,curr_hdc_b2 HRI_P_GRAND_TOTAL4
694 ,comp_hdc_b2 HRI_P_GRAND_TOTAL5
695 ,curr_hdc_b1 HRI_P_GRAND_TOTAL7
696 ,comp_hdc_b1 HRI_P_GRAND_TOTAL8
697 ,curr_hdc_na HRI_P_GRAND_TOTAL10
698 ,comp_hdc_na HRI_P_GRAND_TOTAL11
699 FROM
700 ('||l_inn_sql||') qry
701 WHERE 1=1
702 ' || l_security_clause;
703 --
704 END get_wmv_perf_kpi;
705
706
707 END hri_oltp_pmv_wmv_sup_bckt;