[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_TRN_SUP_BCKT
Source
1 PACKAGE BODY hri_oltp_pmv_wmv_trn_sup_bckt AS
2 /* $Header: hrioptrp.pkb 120.3 2005/10/26 07:54:05 jrstewar noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6
7 /* function to return the sk_fk of the dimension view for the given bucket number */
8 FUNCTION convert_low_bckt_to_sk_fk (p_bucket_dim IN VARCHAR2
9 ,p_bucket_column_id IN NUMBER -- [1 .. 5]
10 ,p_wkth_wktyp_sk_fk IN VARCHAR2 -- ['EMP','CWK']
11 ) RETURN NUMBER
12 IS
13 CURSOR cur_conv_bckt IS
14 SELECT pow_band_sk_pk
15 FROM hri_cs_pow_band_ct pow
16 WHERE pow.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
17 AND pow.band_sequence =p_bucket_column_id;
18
19 l_pow_band_sk_pk NUMBER := -1;
20
21 BEGIN
22
23 OPEN cur_conv_bckt;
24 FETCH cur_conv_bckt INTO l_pow_band_sk_pk;
25 CLOSE cur_conv_bckt;
26
27 RETURN l_pow_band_sk_pk;
28
29 EXCEPTION WHEN OTHERS THEN
30 CLOSE cur_conv_bckt;
31 RETURN l_pow_band_sk_pk;
32
33 END convert_low_bckt_to_sk_fk;
34
35 PROCEDURE get_sql_bkct_perf
36 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
37 x_custom_sql OUT NOCOPY VARCHAR2,
38 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
39
40
41 l_SQLText VARCHAR2(32767) ;
42 l_custom_rec BIS_QUERY_ATTRIBUTES;
43 l_security_clause VARCHAR2(4000);
44
45 /* Parameter values */
46 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
47 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
48
49 /* drill urls */
50 l_drill_url1 VARCHAR2(1000);
51 l_drill_url2 VARCHAR2(1000);
52 l_drill_url3 VARCHAR2(1000);
53 l_drill_url5 VARCHAR2(1000);
54 l_drill_url6 VARCHAR2(1000);
55
56 /* Dynamic SQL Controls */
57 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
58 l_wrkfc_fact_sql VARCHAR2(10000);
59 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
60 l_wcnt_chg_fact_sql VARCHAR2(10000);
61
62 /* Dynamic strings */
63 l_direct_reports_string VARCHAR2(1000);
64 l_display_row_condition VARCHAR2(1000);
65 l_view_by_filter VARCHAR2(1000);
66 l_wmv_outer_join VARCHAR2(30);
67
68 /* Grand totals for terminations */
69 l_curr_term_hdc NUMBER;
70 l_curr_term_hdc_b1 NUMBER;
71 l_curr_term_hdc_b2 NUMBER;
72 l_curr_term_hdc_b3 NUMBER;
73 l_curr_term_hdc_na NUMBER;
74 l_comp_term_hdc NUMBER;
75 l_comp_term_hdc_b1 NUMBER;
76 l_comp_term_hdc_b2 NUMBER;
77 l_comp_term_hdc_b3 NUMBER;
78 l_comp_term_hdc_na NUMBER;
79
80 BEGIN
81 /* Initialize out parameters */
82 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
83 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
84
85 /* Get common parameter values */
86 hri_oltp_pmv_util_param.get_parameters_from_table
87 (p_page_parameter_tbl => p_page_parameter_tbl,
88 p_parameter_rec => l_parameter_rec,
89 p_bind_tab => l_bind_tab);
90
91 /* Get security clause for Manager based security */
92 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
93
94 /* Get direct reports string */
95 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
96
97 /* Swap the viewby column for the default sort order */
98 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
99 (p_order_by_clause => l_parameter_rec.order_by);
100
101 /* Get termination grand totals */
102 hri_bpl_dbi_calc_period.calc_sup_term_perf_pvt
103 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
104 p_from_date => l_parameter_rec.time_curr_start_date,
105 p_to_date => l_parameter_rec.time_curr_end_date,
106 p_bind_tab => l_bind_tab,
107 p_total_term => l_curr_term_hdc,
108 p_total_term_b1 => l_curr_term_hdc_b1,
109 p_total_term_b2 => l_curr_term_hdc_b2,
110 p_total_term_b3 => l_curr_term_hdc_b3,
111 p_total_term_na => l_curr_term_hdc_na);
112
113 hri_bpl_dbi_calc_period.calc_sup_term_perf_pvt
114 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
115 p_from_date => l_parameter_rec.time_comp_start_date,
116 p_to_date => l_parameter_rec.time_comp_end_date,
117 p_bind_tab => l_bind_tab,
118 p_total_term => l_comp_term_hdc,
119 p_total_term_b1 => l_comp_term_hdc_b1,
120 p_total_term_b2 => l_comp_term_hdc_b2,
121 p_total_term_b3 => l_comp_term_hdc_b3,
122 p_total_term_na => l_comp_term_hdc_na);
123
124 /* Get SQL for workforce fact */
125 l_wrkfc_fact_params.bind_format := 'PMV';
126 l_wrkfc_fact_params.include_hdc := 'Y';
127 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
128 (p_parameter_rec => l_parameter_rec,
129 p_bind_tab => l_bind_tab,
130 p_wrkfc_params => l_wrkfc_fact_params,
131 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP_BCKT.GET_SQL_BKCT_PERF');
132
133 /* Get SQL for workforce changes fact */
134 l_wcnt_chg_fact_params.bind_format := 'PMV';
135 l_wcnt_chg_fact_params.include_comp := 'Y';
136 l_wcnt_chg_fact_params.include_sep := 'Y';
137 l_wcnt_chg_fact_params.bucket_dim := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
138 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
139 (p_parameter_rec => l_parameter_rec,
140 p_bind_tab => l_bind_tab,
141 p_wcnt_chg_params => l_wcnt_chg_fact_params,
142 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP_BCKT.GET_SQL_BKCT_PERF');
143
144 /* Set the display row conditions */
145 IF (l_parameter_rec.view_by = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
146 l_parameter_rec.view_by = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' OR
147 l_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
148 /* Outer join to facts, display all rows */
149 l_wmv_outer_join := ' (+)';
150 /* Set the view by filter */
151 l_view_by_filter := hri_oltp_pmv_util_pkg.set_viewby_filter
152 (p_parameter_rec => l_parameter_rec,
153 p_bind_tab => l_bind_tab,
154 p_view_by_alias => 'cl');
155 ELSE
156 /* Only display directs row or rows with activity */
157 l_display_row_condition :=
158 ' AND (qry.curr_hdc > 0
159 OR qry.curr_termination_hdc > 0
160 OR qry.direct_ind = 1)' || g_rtn;
161 END IF;
162
163 /* bug 4202907 append cl start AND end_date filter if viewby manager */
164 IF (l_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
165 l_view_by_filter := l_view_by_filter ||
166 'AND &BIS_CURRENT_ASOF_DATE BETWEEN cl.start_date AND cl.end_date';
167 END IF;
168
169 /* Setup any drill url[s] */
170 l_drill_url1 :=
171 'DECODE(qry.direct_ind
172 ,1 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
173 'VIEW_BY_NAME=VIEW_BY_ID&' ||
174 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=1&' ||
175 'HRI_P_SUPH_RO_CA=N&' ||
176 'pParamIds=Y''
177 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
178 'VIEW_BY_NAME=VIEW_BY_ID&' ||
179 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=1&' ||
180 'pParamIds=Y'')';
181
182 l_drill_url2 :=
183 'DECODE(qry.direct_ind
184 ,1 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
185 'VIEW_BY_NAME=VIEW_BY_ID&' ||
186 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=2&' ||
187 'HRI_P_SUPH_RO_CA=N&' ||
188 'pParamIds=Y''
189 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
190 'VIEW_BY_NAME=VIEW_BY_ID&' ||
191 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=2&' ||
192 'pParamIds=Y'')';
193
194 l_drill_url3 :=
195 'DECODE(qry.direct_ind
196 ,1 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
197 'VIEW_BY_NAME=VIEW_BY_ID&' ||
198 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=3&' ||
199 'HRI_P_SUPH_RO_CA=N&' ||
200 'pParamIds=Y''
201 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
202 'VIEW_BY_NAME=VIEW_BY_ID&' ||
203 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=3&' ||
204 'pParamIds=Y'')';
205
206 l_drill_url5 :=
207 'DECODE(qry.direct_ind
208 ,1 ,''pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
209 'VIEW_BY_NAME=VIEW_BY_ID&' ||
210 'HRI_P_SUPH_RO_CA=N&' ||
211 'pParamIds=Y''
212 ,''pFunctionName=HRI_P_WMV_TRN_BCKT_PERF_PVT&' ||
213 'VIEW_BY_NAME=VIEW_BY_ID&' ||
214 'pParamIds=Y'')';
215
216 l_drill_url6 :=
217 'DECODE(qry.direct_ind
218 ,1 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
219 'VIEW_BY_NAME=VIEW_BY_ID&' ||
220 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=-5&' ||
221 'HRI_P_SUPH_RO_CA=N&' ||
222 'pParamIds=Y''
223 ,''pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
224 'VIEW_BY_NAME=VIEW_BY_ID&' ||
225 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X=-5&' ||
226 'pParamIds=Y'')';
227
228 /* Return AK Sql To PMV */
229 l_SQLText :=
230 'SELECT -- Terminations with Performance Bands
231 qry.order_by HRI_P_ORDER_BY_1
232 ,qry.vby_id VIEWBYID
233 ,qry.value VIEWBY
234 ,DECODE(qry.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
235 /* high performers current metrics */
236 ',qry.curr_termination_hdc_b3 HRI_P_MEASURE3
237 ,DECODE(qry.curr_termination_hdc,
238 0, 0,
239 (qry.curr_termination_hdc_b3 / qry.curr_termination_hdc) * 100)
240 HRI_P_MEASURE3_MP
241 ,' || l_drill_url3 || ' HRI_P_DRILL_URL3' || g_rtn ||
242 /* high performers comparison metrics */
243 ',qry.comp_termination_hdc_b3 HRI_P_MEASURE8
244 ,DECODE(qry.comp_termination_hdc,
245 0, 0,
246 (qry.comp_termination_hdc_b3 / qry.comp_termination_hdc) * 100)
247 HRI_P_MEASURE8_MP' || g_rtn ||
248 /* average performers current metrics */
249 ',qry.curr_termination_hdc_b2 HRI_P_MEASURE2
250 ,DECODE(qry.curr_termination_hdc,
251 0, 0,
252 (qry.curr_termination_hdc_b2 / qry.curr_termination_hdc) * 100)
253 HRI_P_MEASURE2_MP
254 ,' || l_drill_url2 || ' HRI_P_DRILL_URL2' || g_rtn ||
255 /* average performers comparison metrics */
256 ',qry.comp_termination_hdc_b2 HRI_P_MEASURE9
257 ,DECODE(qry.comp_termination_hdc,
258 0, 0,
259 (qry.comp_termination_hdc_b2 / qry.comp_termination_hdc) * 100)
260 HRI_P_MEASURE9_MP' || g_rtn ||
261 /* low performers current metrics */
262 ',qry.curr_termination_hdc_b1 HRI_P_MEASURE1
263 ,DECODE(qry.curr_termination_hdc,
264 0, 0,
265 (qry.curr_termination_hdc_b1 / qry.curr_termination_hdc) * 100)
266 HRI_P_MEASURE1_MP
267 ,' || l_drill_url1 || ' HRI_P_DRILL_URL1' || g_rtn ||
268 /* low performers comparison metrics */
269 ',qry.comp_termination_hdc_b1 HRI_P_MEASURE10
270 ,DECODE(qry.comp_termination_hdc,
271 0, 0,
272 (qry.comp_termination_hdc_b1 / qry.comp_termination_hdc) * 100)
273 HRI_P_MEASURE10_MP' || g_rtn ||
274 /* unassigned performers current metrics */
275 ',qry.curr_termination_hdc_na HRI_P_MEASURE6
276 ,DECODE(qry.curr_termination_hdc,
277 0, 0,
278 (qry.curr_termination_hdc_na / qry.curr_termination_hdc) * 100)
279 HRI_P_MEASURE6_MP
280 ,' || l_drill_url6 || ' HRI_P_DRILL_URL6' || g_rtn ||
281 /* unassigned performers comparison metrics */
282 ',qry.comp_termination_hdc_na HRI_P_MEASURE11
283 ,DECODE(qry.comp_termination_hdc,
284 0, 0,
285 (qry.comp_termination_hdc_na / qry.comp_termination_hdc) * 100)
286 HRI_P_MEASURE11_MP
287 ,qry.curr_termination_hdc HRI_P_MEASURE7
288 ,qry.comp_termination_hdc HRI_P_MEASURE16
289 ,DECODE(qry.comp_termination_hdc,
290 0, NULL,
291 ((qry.curr_termination_hdc - qry.comp_termination_hdc) /
292 qry.comp_termination_hdc) * 100)
293 HRI_P_MEASURE7_MP
294 ,qry.curr_total_term_hdc_b3 HRI_P_GRAND_TOTAL1
295 ,DECODE(qry.curr_total_term_hdc,
296 0, 0,
297 (qry.curr_total_term_hdc_b3 / qry.curr_total_term_hdc) * 100)
298 HRI_P_GRAND_TOTAL2
299 ,qry.comp_total_term_hdc_b3 HRI_P_GRAND_TOTAL3
300 ,DECODE(qry.comp_total_term_hdc,
301 0, 0,
302 (qry.comp_total_term_hdc_b3 / qry.comp_total_term_hdc) * 100)
303 HRI_P_GRAND_TOTAL4
304 ,qry.curr_total_term_hdc_b2 HRI_P_GRAND_TOTAL5
305 ,DECODE(qry.curr_total_term_hdc,
306 0, 0,
307 (qry.curr_total_term_hdc_b2 / qry.curr_total_term_hdc) * 100)
308 HRI_P_GRAND_TOTAL6
309 ,qry.comp_total_term_hdc_b2 HRI_P_GRAND_TOTAL7
310 ,DECODE(qry.comp_total_term_hdc,
311 0, 0,
312 (qry.comp_total_term_hdc_b2 / qry.comp_total_term_hdc) * 100)
313 HRI_P_GRAND_TOTAL8
314 ,qry.curr_total_term_hdc_b1 HRI_P_GRAND_TOTAL9
315 ,DECODE(qry.curr_total_term_hdc,
316 0, 0,
317 (qry.curr_total_term_hdc_b1 / qry.curr_total_term_hdc) * 100)
318 HRI_P_GRAND_TOTAL10
319 ,qry.comp_total_term_hdc_b1 HRI_P_GRAND_TOTAL11
320 ,DECODE(qry.comp_total_term_hdc,
321 0, 0,
322 (qry.comp_total_term_hdc_b1 / qry.comp_total_term_hdc) * 100)
323 HRI_P_GRAND_TOTAL12
324 ,qry.curr_total_term_hdc_na HRI_P_GRAND_TOTAL13
325 ,DECODE(qry.curr_total_term_hdc,
326 0, 0,
327 (qry.curr_total_term_hdc_na / qry.curr_total_term_hdc) * 100)
328 HRI_P_GRAND_TOTAL14
329 ,qry.comp_total_term_hdc_na HRI_P_GRAND_TOTAL15
330 ,DECODE(qry.comp_total_term_hdc,
331 0, 0,
332 (qry.comp_total_term_hdc_na / qry.comp_total_term_hdc) * 100)
333 HRI_P_GRAND_TOTAL16
334 ,qry.curr_total_term_hdc HRI_P_GRAND_TOTAL17
335 ,qry.comp_total_term_hdc HRI_P_GRAND_TOTAL18
336 ,DECODE(qry.comp_total_term_hdc,
337 0, 0,
338 ((qry.curr_total_term_hdc - qry.comp_total_term_hdc) /
339 qry.comp_total_term_hdc) * 100)
340 HRI_P_GRAND_TOTAL17_MP' || g_rtn ||
341 /* HRI_P_DRILL_URL5 used as drill url when viewby manager only */
342 ', '|| l_drill_url5 || ' HRI_P_DRILL_URL5
343 FROM
344 (SELECT
345 cl.id vby_id
346 ,NVL(wmv.direct_ind, 0) direct_ind
347 ,DECODE(wmv.direct_ind,
348 1, ''' || l_direct_reports_string || ''',
349 cl.value) value
350 ,to_char(NVL(wmv.direct_ind, 0)) || cl.order_by order_by
351 ,NVL(wmv.curr_hdc_end, 0) curr_hdc
352 ,NVL(SUM(wmv.curr_hdc_end) OVER (), 0) curr_total_hdc
353 ,NVL(trn.curr_separation_hdc_b3, 0) curr_termination_hdc_b3
354 ,NVL(trn.curr_separation_hdc_b2, 0) curr_termination_hdc_b2
355 ,NVL(trn.curr_separation_hdc_b1, 0) curr_termination_hdc_b1
356 ,NVL(trn.curr_separation_hdc_na, 0) curr_termination_hdc_na
357 ,NVL(trn.curr_separation_hdc, 0) curr_termination_hdc
358 ,NVL(trn.comp_separation_hdc_b3, 0) comp_termination_hdc_b3
359 ,NVL(trn.comp_separation_hdc_b2, 0) comp_termination_hdc_b2
360 ,NVL(trn.comp_separation_hdc_b1, 0) comp_termination_hdc_b1
361 ,NVL(trn.comp_separation_hdc_na, 0) comp_termination_hdc_na
362 ,NVL(trn.comp_separation_hdc, 0) comp_termination_hdc
363 ,:HRI_CURR_TERM_HDC_B3 curr_total_term_hdc_b3
364 ,:HRI_COMP_TERM_HDC_B3 comp_total_term_hdc_b3
365 ,:HRI_CURR_TERM_HDC_B2 curr_total_term_hdc_b2
366 ,:HRI_COMP_TERM_HDC_B2 comp_total_term_hdc_b2
367 ,:HRI_CURR_TERM_HDC_B1 curr_total_term_hdc_b1
368 ,:HRI_COMP_TERM_HDC_B1 comp_total_term_hdc_b1
369 ,:HRI_CURR_TERM_HDC_NA curr_total_term_hdc_na
370 ,:HRI_COMP_TERM_HDC_NA comp_total_term_hdc_na
371 ,:HRI_CURR_TERM_HDC curr_total_term_hdc
372 ,:HRI_COMP_TERM_HDC comp_total_term_hdc
373 FROM
374 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
375 (l_parameter_rec.view_by).viewby_table || ' cl
376 ,(' || l_wrkfc_fact_sql || ') wmv
377 ,(' || l_wcnt_chg_fact_sql || ') trn
378 WHERE wmv.vby_id = trn.vby_id (+)
379 AND cl.id = wmv.vby_id' || l_wmv_outer_join || g_rtn ||
380 l_view_by_filter ||
381 ' ) QRY
382 WHERE 1 = 1
383 ' || l_security_clause || g_rtn ||
384 l_display_row_condition ||
385 'ORDER BY ' || l_parameter_rec.order_by;
386
387 x_custom_sql := l_SQLText;
388
389 /* Binds Will be inserted Below */
390 l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC';
391 l_custom_rec.attribute_value := l_curr_term_hdc;
392 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
393 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
394 x_custom_output.extend;
395 x_custom_output(1) := l_custom_rec;
396
397 l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC_B1';
398 l_custom_rec.attribute_value := l_curr_term_hdc_b1;
399 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
400 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
401 x_custom_output.extend;
402 x_custom_output(2) := l_custom_rec;
403
404 l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC_B2';
405 l_custom_rec.attribute_value := l_curr_term_hdc_b2;
406 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
407 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
408 x_custom_output.extend;
409 x_custom_output(3) := l_custom_rec;
410
411 l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC_B3';
412 l_custom_rec.attribute_value := l_curr_term_hdc_b3;
413 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
414 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
415 x_custom_output.extend;
416 x_custom_output(4) := l_custom_rec;
417
418 l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC_NA';
419 l_custom_rec.attribute_value := l_curr_term_hdc_na;
420 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
421 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
422 x_custom_output.extend;
423 x_custom_output(5) := l_custom_rec;
424
425 l_custom_rec.attribute_name := ':HRI_COMP_TERM_HDC';
426 l_custom_rec.attribute_value := l_comp_term_hdc;
427 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
428 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
429 x_custom_output.extend;
430 x_custom_output(6) := l_custom_rec;
431
432 l_custom_rec.attribute_name := ':HRI_COMP_TERM_HDC_B1';
433 l_custom_rec.attribute_value := l_comp_term_hdc_b1;
434 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
435 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
436 x_custom_output.extend;
437 x_custom_output(7) := l_custom_rec;
438
439 l_custom_rec.attribute_name := ':HRI_COMP_TERM_HDC_B2';
440 l_custom_rec.attribute_value := l_comp_term_hdc_b2;
441 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
442 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
443 x_custom_output.extend;
444 x_custom_output(8) := l_custom_rec;
445
446 l_custom_rec.attribute_name := ':HRI_COMP_TERM_HDC_B3';
447 l_custom_rec.attribute_value := l_comp_term_hdc_b3;
448 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
449 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
450 x_custom_output.extend;
451 x_custom_output(9) := l_custom_rec;
452
453 l_custom_rec.attribute_name := ':HRI_COMP_TERM_HDC_NA';
454 l_custom_rec.attribute_value := l_comp_term_hdc_na;
455 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
456 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
457 x_custom_output.extend;
458 x_custom_output(10) := l_custom_rec;
459
460 END GET_SQL_BKCT_PERF;
461
462
463 /******************************************************************************/
464 /* Termination by Length of service
465 /******************************************************************************/
466 PROCEDURE GET_TRN_POW_SQL
467 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
468 x_custom_sql OUT NOCOPY VARCHAR2,
469 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
470
471 l_sqltext VARCHAR2(32767);
472 l_custom_rec BIS_QUERY_ATTRIBUTES;
473 l_security_clause VARCHAR2(4000);
474
475 /* Parameter values */
476 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
477 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
478
479 /* Dynamic SQL Controls */
480 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
481 l_wrkfc_fact_sql VARCHAR2(10000);
482 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
483 l_wcnt_chg_fact_sql VARCHAR2(10000);
484
485 /* Dynamic strings */
486 l_direct_reports_string VARCHAR2(1000);
487 l_wmv_outer_join VARCHAR2(30);
488 l_display_row_condition VARCHAR2(1000);
489 l_view_by_filter VARCHAR2(1000);
490
491 /* Drill URLs */
492 l_drill_mgr_sup VARCHAR2(500);
493 l_drill_mgr_dir VARCHAR2(500);
494 l_drill_trn_bn1_dtl VARCHAR2(500);
495 l_drill_trn_bn2_dtl VARCHAR2(500);
496 l_drill_trn_bn3_dtl VARCHAR2(500);
497 l_drill_trn_bn4_dtl VARCHAR2(500);
498 l_drill_trn_bn5_dtl VARCHAR2(500);
499
500 /* Grand totals for terminations */
501 l_term_hdc NUMBER;
502 l_term_hdc_b1 NUMBER;
503 l_term_hdc_b2 NUMBER;
504 l_term_hdc_b3 NUMBER;
505 l_term_hdc_b4 NUMBER;
506 l_term_hdc_b5 NUMBER;
507
508 BEGIN
509
510 /* Initialize out parameters */
511 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
512 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
513
514 /* Get common parameter values */
515 hri_oltp_pmv_util_param.get_parameters_from_table
516 (p_page_parameter_tbl => p_page_parameter_tbl,
517 p_parameter_rec => l_parameter_rec,
518 p_bind_tab => l_bind_tab);
519
520 /* Get security clause for Manager based security */
521 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
522
523 /* Get direct reports string */
524 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
525
526 /* Precalculate grand totals */
527 -- bug 4317575
528 hri_bpl_dbi_calc_period.calc_sup_term_low_pvt
529 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
530 p_from_date => l_parameter_rec.time_curr_start_date,
531 p_to_date => l_parameter_rec.time_curr_end_date,
532 p_bind_tab => l_bind_tab,
533 p_total_term => l_term_hdc,
534 p_total_term_b1 => l_term_hdc_b1,
535 p_total_term_b2 => l_term_hdc_b2,
536 p_total_term_b3 => l_term_hdc_b3,
537 p_total_term_b4 => l_term_hdc_b4,
538 p_total_term_b5 => l_term_hdc_b5);
539
540 /* Get SQL for workforce fact */
541 l_wrkfc_fact_params.bind_format := 'PMV';
542 l_wrkfc_fact_params.include_hdc := 'Y';
543 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
544 (p_parameter_rec => l_parameter_rec,
545 p_bind_tab => l_bind_tab,
546 p_wrkfc_params => l_wrkfc_fact_params,
547 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP_BCKT.GET_SQL_BKCT_LOW');
548
549 /* Get SQL for workforce changes fact */
550 l_wcnt_chg_fact_params.bind_format := 'PMV';
551 l_wcnt_chg_fact_params.include_sep := 'Y';
552 l_wcnt_chg_fact_params.bucket_dim := 'HRI_LOW+HRI_LOW_BAND_X';
553 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
554 (p_parameter_rec => l_parameter_rec,
555 p_bind_tab => l_bind_tab,
556 p_wcnt_chg_params => l_wcnt_chg_fact_params,
557 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP_BCKT.GET_SQL_BKCT_LOW');
558
559 /* Set drill URLs for constant drills */
560 l_drill_trn_bn1_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
561 'VIEW_BY_NAME=VIEW_BY_ID&' ||
562 'HRI_P_LOW_BAND_CN=' ||
563 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
564 ,1 -- bucket
565 ,l_parameter_rec.wkth_wktyp_sk_fk)||
566 '&' ||
567 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
568 'pParamIds=Y';
569
570 l_drill_trn_bn2_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
571 'VIEW_BY_NAME=VIEW_BY_ID&' ||
572 'HRI_P_LOW_BAND_CN=' ||
573 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
574 ,2 -- bucket
575 ,l_parameter_rec.wkth_wktyp_sk_fk)||
576 '&' ||
577 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
578 'pParamIds=Y';
579
580 l_drill_trn_bn3_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
581 'VIEW_BY_NAME=VIEW_BY_ID&' ||
582 'HRI_P_LOW_BAND_CN=' ||
583 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
584 ,3 -- bucket
585 ,l_parameter_rec.wkth_wktyp_sk_fk)||
586 '&' ||
587 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
588 'pParamIds=Y';
589
590 l_drill_trn_bn4_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
591 'VIEW_BY_NAME=VIEW_BY_ID&' ||
592 'HRI_P_LOW_BAND_CN=' ||
593 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
594 ,4 -- bucket
595 ,l_parameter_rec.wkth_wktyp_sk_fk)||
596 '&' ||
597 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
598 'pParamIds=Y';
599
600 l_drill_trn_bn5_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
601 'VIEW_BY_NAME=VIEW_BY_ID&' ||
602 'HRI_P_LOW_BAND_CN=' ||
603 convert_low_bckt_to_sk_fk('HRI_LOW+HRI_LOW_BAND_X'
604 ,5 -- bucket
605 ,l_parameter_rec.wkth_wktyp_sk_fk)||
606 '&' ||
607 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
608 'pParamIds=Y';
609
610 /* Set view by manager drill URLs */
611 IF (l_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
612 l_drill_mgr_sup := 'pFunctionName=HRI_P_WMV_TRN_BCKT_POW_PVT&' ||
613 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
614 'VIEW_BY_NAME=VIEW_BY_ID&' ||
615 'pParamIds=Y';
616 l_drill_mgr_dir := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
617 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
618 'VIEW_BY_NAME=VIEW_BY_ID&' ||
619 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
620 'pParamIds=Y';
621 END IF;
622
623 /* Set the display row conditions */
624 IF (l_parameter_rec.view_by = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
625 l_parameter_rec.view_by = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' OR
626 l_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
627 /* Outer join to facts, display all rows */
628 l_wmv_outer_join := ' (+)';
629 /* Set the view by filter */
630 l_view_by_filter := hri_oltp_pmv_util_pkg.set_viewby_filter
631 (p_parameter_rec => l_parameter_rec,
632 p_bind_tab => l_bind_tab,
633 p_view_by_alias => 'cl');
634 ELSE
635 /* Only display directs row or rows with activity */
636 l_display_row_condition :=
637 ' AND (a.curr_hdc_end > 0
638 OR a.curr_trn_tot > 0
639 OR a.direct_ind = 1)' || g_rtn;
640 END IF;
641
642 /* bug 4202907 append cl start AND end_date filter if viewby manager */
643 IF (l_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
644 l_view_by_filter := l_view_by_filter ||
645 'AND &BIS_CURRENT_ASOF_DATE BETWEEN cl.start_date AND cl.end_date';
646 END IF;
647
648 /* Swap the viewby column for the default sort order */
649 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
650 (p_order_by_clause => l_parameter_rec.order_by);
651
652 l_sqltext :=
653 'SELECT -- Terminations with Length of Service Portlet
654 a.id VIEWBYID
655 ,a.value VIEWBY
656 ,DECODE(a.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB ' || g_rtn ||
657 /* Title - Band One - under 1 year */'
658 ,a.curr_trn_bn1 HRI_P_MEASURE1
659 ,DECODE(a.curr_trn_tot,0,0
660 ,DECODE(a.curr_trn_bn1,0,0
661 ,(a.curr_trn_bn1 / a.curr_trn_tot) * 100)
662 ) HRI_P_MEASURE1_MP' || g_rtn ||
663 /* Title - Band Two - 1-3 years */'
664 ,a.curr_trn_bn2 HRI_P_MEASURE2
665 ,DECODE(a.curr_trn_tot,0,0
666 ,DECODE(a.curr_trn_bn2,0,0
667 ,(a.curr_trn_bn2 / a.curr_trn_tot) * 100)
668 ) HRI_P_MEASURE2_MP' || g_rtn ||
669 /* Title - Band Three - 3-5 years */'
670 ,a.curr_trn_bn3 HRI_P_MEASURE3
671 ,DECODE(a.curr_trn_tot,0,0
672 ,DECODE(a.curr_trn_bn3,0,0
673 ,(a.curr_trn_bn3 / a.curr_trn_tot) * 100)
674 ) HRI_P_MEASURE3_MP ' || g_rtn ||
675 /* Title - Band Four - 5-10 years */'
676 ,a.curr_trn_bn4 HRI_P_MEASURE4
677 ,DECODE(a.curr_trn_tot,0,0
678 ,DECODE(a.curr_trn_bn4,0,0
679 ,(a.curr_trn_bn4 / a.curr_trn_tot) * 100)
680 ) HRI_P_MEASURE4_MP ' || g_rtn ||
681 /* Title - Band Five - over 10 years */'
682 ,a.curr_trn_bn5 HRI_P_MEASURE5
683 ,DECODE(a.curr_trn_tot,0,0
684 ,DECODE(a.curr_trn_bn5,0,0
685 ,(a.curr_trn_bn5 / a.curr_trn_tot) * 100)
686 ) HRI_P_MEASURE5_MP ' || g_rtn ||
687 /* Total Terminations */'
688 ,a.curr_trn_tot HRI_P_MEASURE6 ' || g_rtn ||
689 /* Grand Total - Band One - under 1 year */'
690 ,a.tot_curr_trn_bn1 HRI_P_GRAND_TOTAL1
691 ,DECODE(a.tot_curr_trn_tot,0,0
692 ,DECODE(a.tot_curr_trn_bn1,0,0
693 ,(a.tot_curr_trn_bn1 / a.tot_curr_trn_tot) * 100)
694 ) HRI_P_GRAND_TOTAL1_MP' || g_rtn ||
695 /* Title - Band Two - 1-3 years */'
696 ,a.tot_curr_trn_bn2 HRI_P_GRAND_TOTAL2
697 ,DECODE(a.tot_curr_trn_tot,0,0
698 ,DECODE(a.tot_curr_trn_bn2,0,0
699 ,(a.tot_curr_trn_bn2 / a.tot_curr_trn_tot) * 100)
700 ) HRI_P_GRAND_TOTAL2_MP' || g_rtn ||
701 /* Title - Band Three - 3-5 years */'
702 ,a.tot_curr_trn_bn3 HRI_P_GRAND_TOTAL3
703 ,DECODE(a.tot_curr_trn_tot,0,0
704 ,DECODE(a.tot_curr_trn_bn3,0,0
705 ,(a.tot_curr_trn_bn3 / a.tot_curr_trn_tot) * 100)
706 ) HRI_P_GRAND_TOTAL3_MP' || g_rtn ||
707 /* Title - Band Four - 5-10 years */'
708 ,a.tot_curr_trn_bn4 HRI_P_GRAND_TOTAL4
709 ,DECODE(a.tot_curr_trn_tot,0,0
710 ,DECODE(a.tot_curr_trn_bn4,0,0
711 ,(a.tot_curr_trn_bn4 / a.tot_curr_trn_tot) * 100)
712 ) HRI_P_GRAND_TOTAL4_MP' || g_rtn ||
713 /* Title - Band Four - over 10 years */'
714 ,a.tot_curr_trn_bn5 HRI_P_GRAND_TOTAL5
715 ,DECODE(a.tot_curr_trn_tot,0,0
716 ,DECODE(a.tot_curr_trn_bn5,0,0
717 ,(a.tot_curr_trn_bn5 / a.tot_curr_trn_tot) * 100)
718 ) HRI_P_GRAND_TOTAL5_MP' || g_rtn ||
719 /* Total Terminations */'
720 ,a.tot_curr_trn_tot HRI_P_GRAND_TOTAL6 ' || g_rtn ||
721 /* Order by person name default sort order */
722 ',a.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
723 /* Whether the row is a supervisor rollup row */
724 ',DECODE(a.direct_ind , 0, '''', ''N'')
725 HRI_P_SUPH_RO_CA' || g_rtn ||
726 /* Drill URLs */
727 ',DECODE(a.direct_ind,
728 0, ''' || l_drill_mgr_sup || ''',
729 ''' || l_drill_mgr_dir || ''')
730 HRI_P_DRILL_URL1
731 ,'''||l_drill_trn_bn1_dtl ||''' HRI_P_DRILL_URL2
732 ,'''||l_drill_trn_bn2_dtl ||''' HRI_P_DRILL_URL3
733 ,'''||l_drill_trn_bn3_dtl ||''' HRI_P_DRILL_URL4
734 ,'''||l_drill_trn_bn4_dtl ||''' HRI_P_DRILL_URL5
735 ,'''||l_drill_trn_bn5_dtl ||''' HRI_P_DRILL_URL6
736 FROM
737 (SELECT
738 cl.id
739 ,DECODE(wmv.direct_ind,
740 1, ''' || l_direct_reports_string || ''',
741 cl.value) value
742 ,to_char(NVL(wmv.direct_ind, 0)) || cl.order_by order_by
743 ,NVL(wmv.direct_ind, 0) direct_ind' || g_rtn ||
744 /* Headcount */
745 ' ,NVL(wmv.curr_hdc_end, 0) curr_hdc_end' || g_rtn ||
746 /* Turnover */
747 ' ,NVL(trn.curr_separation_hdc_b1, 0) curr_trn_bn1
748 ,NVL(trn.curr_separation_hdc_b2, 0) curr_trn_bn2
749 ,NVL(trn.curr_separation_hdc_b3, 0) curr_trn_bn3
750 ,NVL(trn.curr_separation_hdc_b4, 0) curr_trn_bn4
751 ,NVL(trn.curr_separation_hdc_b5, 0) curr_trn_bn5
752 ,NVL(trn.curr_separation_hdc, 0) curr_trn_tot' || g_rtn ||
753 /* Grand Totals - Turnover */
754 ' ,:HRI_TOT_TERM_HDC_B1 tot_curr_trn_bn1
755 ,:HRI_TOT_TERM_HDC_B2 tot_curr_trn_bn2
756 ,:HRI_TOT_TERM_HDC_B3 tot_curr_trn_bn3
757 ,:HRI_TOT_TERM_HDC_B4 tot_curr_trn_bn4
758 ,:HRI_TOT_TERM_HDC_B5 tot_curr_trn_bn5
759 ,:HRI_TOT_TERM_HDC tot_curr_trn_tot
760 FROM
761 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
762 (l_parameter_rec.view_by).viewby_table || ' cl
763 ,(' || l_wrkfc_fact_sql || ') wmv
764 ,(' || l_wcnt_chg_fact_sql || ') trn
765 WHERE wmv.vby_id = trn.vby_id (+)
766 AND cl.id = wmv.vby_id ' || l_wmv_outer_join || g_rtn ||
767 l_view_by_filter ||
768 ' ) a
769 WHERE 1 = 1' || g_rtn ||
770 l_display_row_condition || g_rtn ||
771 l_security_clause || '
772 ORDER BY ' || l_parameter_rec.order_by;
773
774 x_custom_sql := l_SQLText;
775 --l_parameter_rec.debug_header || l_SQLText;
776
777 l_custom_rec.attribute_name := ':HRI_TOT_TERM_HDC';
778 l_custom_rec.attribute_value := l_term_hdc;
779 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
780 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
781 x_custom_output.extend;
782 x_custom_output(1) := l_custom_rec;
783
784 l_custom_rec.attribute_name := ':HRI_TOT_TERM_HDC_B1';
785 l_custom_rec.attribute_value := l_term_hdc_b1;
786 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
787 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
788 x_custom_output.extend;
789 x_custom_output(2) := l_custom_rec;
790
791 l_custom_rec.attribute_name := ':HRI_TOT_TERM_HDC_B2';
792 l_custom_rec.attribute_value := l_term_hdc_b2;
793 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
794 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
795 x_custom_output.extend;
796 x_custom_output(3) := l_custom_rec;
797
798 l_custom_rec.attribute_name := ':HRI_TOT_TERM_HDC_B3';
799 l_custom_rec.attribute_value := l_term_hdc_b3;
800 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
801 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
802 x_custom_output.extend;
803 x_custom_output(4) := l_custom_rec;
804
805 l_custom_rec.attribute_name := ':HRI_TOT_TERM_HDC_B4';
806 l_custom_rec.attribute_value := l_term_hdc_b4;
807 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
808 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
809 x_custom_output.extend;
810 x_custom_output(5) := l_custom_rec;
811
812 l_custom_rec.attribute_name := ':HRI_TOT_TERM_HDC_B5';
813 l_custom_rec.attribute_value := l_term_hdc_b5;
814 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
815 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
816 x_custom_output.extend;
817 x_custom_output(6) := l_custom_rec;
818
819 END GET_TRN_POW_SQL;
820 --
821 -- ----------------------------------------------------------------------
822 -- Procedure to fetch the termination ratio by performance KPI
823 -- It fetched the values for the following KPIs
824 -- 1. Termination ratio for High Band
825 -- 2. Termination ratio for High Band
826 -- 3. Termination ratio for Mid Band
827 -- 4. Termination ratio for Mid Band
828 -- 5. Termination ratio for Low Band
829 -- 6. Termination ratio for Low Band
830 -- 7. Termination ratio for NA Band
831 -- 8. Termination ratio for NA Band
832 -- ----------------------------------------------------------------------
833 --
834 PROCEDURE get_trn_perf_kpi
835 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
836 x_custom_sql OUT NOCOPY VARCHAR2,
837 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
838 --
839 -- The security clause
840 --
841 l_security_clause VARCHAR2(4000);
842 --
843 -- Page parameters
844 --
845 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
846 --
847 -- Bind values for SQL and PMV mode
848 --
849 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
850 --
851 -- Parameter values for getting the inner SQL
852 --
853 l_wcnt_chg_params hri_bpl_fact_sup_wcnt_chg_sql.WCNT_CHG_FACT_PARAM_TYPE;
854 --
855 -- Inner SQL
856 --
857 l_inn_sql VARCHAR2(32767);
858 l_custom_rec BIS_QUERY_ATTRIBUTES;
859 --
860 BEGIN
861 --
862 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
863 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
864 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
865 --
866 -- Get the parameter information from the page parameter table
867 --
868 hri_oltp_pmv_util_param.get_parameters_from_table
869 (p_page_parameter_tbl => p_page_parameter_tbl,
870 p_parameter_rec => l_parameter_rec,
871 p_bind_tab => l_bind_tab);
872 --
873 -- Set the parameters for getting the inner SQL
874 --
875 l_wcnt_chg_params.bind_format := 'PMV';
876 l_wcnt_chg_params.include_comp := 'Y';
877 l_wcnt_chg_params.include_sep := 'Y';
878 l_wcnt_chg_params.kpi_mode := 'Y';
879 l_wcnt_chg_params.bucket_dim := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
880 --
881 -- Get the inner SQL
882 --
883 l_inn_sql := HRI_OLTP_PMV_QUERY_WCNT_CHG.get_sql
884 (p_parameter_rec => l_parameter_rec,
885 p_bind_tab => l_bind_tab,
886 p_wcnt_chg_params => l_wcnt_chg_params,
887 p_calling_module => 'hri_oltp_pmv_wmv_trn_sup_bckt.get_trn_perf_kpi');
888 --
889 -- Form the SQL
890 --
891 x_custom_sql :=
892 'SELECT --Termination by Performance
893 qry.vby_id VIEWBYID
894 ,qry.vby_id VIEWBY
895 ,curr_separation_hdc_b3/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
896 HRI_P_MEASURE1
897 ,comp_separation_hdc_b3/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
898 HRI_P_MEASURE2
899 ,curr_separation_hdc_b2/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
900 HRI_P_MEASURE4
901 ,comp_separation_hdc_b2/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
902 HRI_P_MEASURE5
903 ,curr_separation_hdc_b1/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
904 HRI_P_MEASURE7
905 ,comp_separation_hdc_b1/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
906 HRI_P_MEASURE8
907 ,curr_separation_hdc_na/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
908 HRI_P_MEASURE10
909 ,comp_separation_hdc_na/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
910 HRI_P_MEASURE11
911 ,curr_separation_hdc_b3/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
912 HRI_P_GRAND_TOTAL1
913 ,comp_separation_hdc_b3/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
914 HRI_P_GRAND_TOTAL2
915 ,curr_separation_hdc_b2/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
916 HRI_P_GRAND_TOTAL4
917 ,comp_separation_hdc_b2/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
918 HRI_P_GRAND_TOTAL5
919 ,curr_separation_hdc_b1/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
920 HRI_P_GRAND_TOTAL7
921 ,comp_separation_hdc_b1/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
922 HRI_P_GRAND_TOTAL8
923 ,curr_separation_hdc_na/decode(curr_separation_hdc,0,1,curr_separation_hdc)* 100
924 HRI_P_GRAND_TOTAL10
925 ,comp_separation_hdc_na/decode(comp_separation_hdc,0,1,comp_separation_hdc)* 100
926 HRI_P_GRAND_TOTAL11
927 FROM
928 ('||l_inn_sql||') qry
929 WHERE 1=1
930 ' || l_security_clause;
931 --
932 END get_trn_perf_kpi;
933
934 END hri_oltp_pmv_wmv_trn_sup_bckt;