[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SAL_SUP_PVT
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_SAL_SUP_PVT AS
2 /* $Header: hriopwsp.pkb 120.6 2005/11/30 06:05:39 cbridge noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 --
8 --****************************************************************************
9 --* RE-ARCH
10 --* AK SQL For Headcount and Salary by Country Status *
11 --* AK Region : HRI_P_WMV_SAL_CTR_SUP_PVT *
12 --****************************************************************************
13 --
14 PROCEDURE get_sql_ctr2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
15 ,x_custom_sql OUT NOCOPY VARCHAR2
16 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
17 IS
18
19 l_SQLText VARCHAR2(32000);
20 l_security_clause VARCHAR2(4000);
21 l_custom_rec BIS_QUERY_ATTRIBUTES ;
22
23 /* Dynamic SQL Controls */
24 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
25 l_wrkfc_fact_sql VARCHAR2(10000);
26
27 /* Pre-calculations */
28 l_drill_url VARCHAR2(1000);
29
30 /* Parameter values */
31 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
32 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
33 l_debug_header VARCHAR(550);
34
35 BEGIN
36 /* Initialize out parameters */
37 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
38 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
39
40 /* Get security clause for Manager based security */
41 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
42
43 /* Get common parameter values */
44 hri_oltp_pmv_util_param.get_parameters_from_table
45 (p_page_parameter_tbl => p_page_parameter_tbl,
46 p_parameter_rec => l_parameter_rec,
47 p_bind_tab => l_bind_tab);
48
49 /* Set l_drill_url to null to turnoff drill */
50 l_drill_url := '' ; -- bug 3696662, turned off drill url
51
52 -- 'pFunctionName=HRI_P_WMV_SAL_RGN_SUP&' ||
53 -- 'HRI_P_GEO_CTY_CN=HRI_P_GEO_CTY_CN&' ||
54 -- 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
55 -- 'pParamIds=Y';
56
57 /* Set order by */
58 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
59 (p_order_by_clause => l_parameter_rec.order_by);
60
61 /* Force view by to country */
62 l_parameter_rec.view_by := 'GEOGRAPHY+COUNTRY';
63
64 /* Get SQL for workforce fact */
65 l_wrkfc_fact_params.bind_format := 'PMV';
66 l_wrkfc_fact_params.include_comp := 'Y';
67 l_wrkfc_fact_params.include_hdc := 'Y';
68 l_wrkfc_fact_params.include_sal := 'Y';
69 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
70 (p_parameter_rec => l_parameter_rec,
71 p_bind_tab => l_bind_tab,
72 p_wrkfc_params => l_wrkfc_fact_params,
73 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_PVT.GET_SQL_CTR2');
74
75 l_SQLText :=
76 ' -- Headcount and Salary by Country Status
77 SELECT
78 tab.order_by HRI_P_ORDER_BY_1
79 ,ctr.value HRI_P_MEASURE1
80 ,tab.wmv_curr HRI_P_WMV_SUM_MV
81 ,tab.wmv_prev HRI_P_WMV_SUM_PREV_MV
82 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
83 (p_previous_col => 'tab.wmv_prev',
84 p_current_col => 'tab.wmv_curr') || '
85 HRI_P_WMV_CHNG_PCT_SUM_MV
86 ,tab.sal_curr HRI_P_MEASURE2
87 ,tab.sal_prev HRI_P_MEASURE3
88 ,tab.avg_sal HRI_P_MEASURE4
89 ,NVL(tab.avg_sal_prev,0) HRI_P_MEASURE5
90 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
91 (p_previous_col => 'tab.avg_sal_prev',
92 p_current_col => 'tab.avg_sal') || '
93 HRI_P_MEASURE6
94 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
95 (p_previous_col =>
96 'DECODE(tab.tot_wmv_prev, 0, to_number(null), tab.tot_sal_prev / tab.tot_wmv_prev)',
97 p_current_col =>
98 'DECODE(tab.tot_wmv_curr, 0, to_number(null), tab.tot_sal_curr / tab.tot_wmv_curr)') || '
99 HRI_P_GRAND_TOTAL1
100 ,DECODE(tab.tot_wmv_curr,
101 0, to_number(null),
102 tab.tot_sal_curr/tab.tot_wmv_curr)
103 HRI_P_GRAND_TOTAL2
104 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
105 (p_previous_col => 'tab.tot_wmv_prev',
106 p_current_col => 'tab.tot_wmv_curr') || '
107 HRI_P_GRAND_TOTAL3
108 ,tab.country_code HRI_P_GEO_CTY_CN
109 ,'''||l_drill_url||'''
110 HRI_P_CHAR1_GA
111 FROM
112 hri_dbi_cl_geo_country_v ctr
113 ,(SELECT
114 bc.vby_id country_code
115 ,-bc.curr_hdc_end order_by
116 ,bc.curr_hdc_end wmv_curr
117 ,bc.comp_hdc_end wmv_prev
118 ,bc.curr_sal_end sal_curr
119 ,DECODE(bc.curr_hdc_end,
120 0, to_number(null),
121 bc.curr_sal_end / bc.curr_hdc_end)
122 avg_sal
123 ,bc.comp_sal_end sal_prev
124 ,DECODE(bc.comp_hdc_end,
125 0, to_number(null),
126 bc.comp_sal_end / bc.comp_hdc_end)
127 avg_sal_prev
128 ,SUM(bc.curr_hdc_end) OVER () tot_wmv_curr
129 ,SUM(bc.curr_sal_end) OVER () tot_sal_curr
130 ,SUM(bc.comp_total_hdc_end) OVER () tot_wmv_prev
131 ,SUM(bc.comp_total_sal_end) OVER () tot_sal_prev
132 FROM
133 (' || l_wrkfc_fact_sql || ') bc
134 WHERE (bc.curr_hdc_end > 0
135 OR bc.comp_hdc_end > 0
136 OR bc.curr_sal_end > 0
137 OR bc.comp_sal_end > 0)
138 ) tab
139 WHERE tab.country_code = ctr.id
140 ' || l_security_clause || '
141 ORDER BY
142 HRI_P_ORDER_BY_1
143 ,HRI_P_MEASURE4
144 ,HRI_P_MEASURE1';
145
146 x_custom_sql := l_SQLText ;
147
148 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
149 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
150 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
151 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
152 x_custom_output.extend;
153 x_custom_output(1) := l_custom_rec;
154
155 l_custom_rec.attribute_name := ':GLOBAL_RATE';
156 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
157 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
158 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
159 x_custom_output.extend;
160 x_custom_output(2) := l_custom_rec;
161
162 END get_sql_ctr2;
163 --
164 --****************************************************************************
165 --* RE-ARCH
166 --* AK SQL For Headcount and Salary by Region *
167 --* AK Region : HRI_P_WMV_SAL_RGN_SUP *
168 --****************************************************************************
169 --
170 PROCEDURE get_sql_rgn2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
171 ,x_custom_sql OUT NOCOPY VARCHAR2
172 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
173 IS
174
175 l_SQLText VARCHAR2(32000);
176 l_security_clause VARCHAR2(4000);
177 l_custom_rec BIS_QUERY_ATTRIBUTES ;
178
179 /* Dynamic SQL Controls */
180 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
181 l_wrkfc_fact_sql VARCHAR2(10000);
182
183 /* Pre-calculations */
184 l_drill_url VARCHAR2(1000);
185 l_country_code VARCHAR2(1000);
186
187 /* Parameter values */
188 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
189 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
190 l_debug_header VARCHAR(550);
191
192 BEGIN
193 /* Initialize out parameters */
194 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
195 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
196
197 /* Get security clause for Manager based security */
198 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
199
200 /* Get common parameter values */
201 hri_oltp_pmv_util_param.get_parameters_from_table
202 (p_page_parameter_tbl => p_page_parameter_tbl,
203 p_parameter_rec => l_parameter_rec,
204 p_bind_tab => l_bind_tab);
205
206 /* Set l_drill_url to null to turnoff drill Drilling to Rearch Report*/
207 l_drill_url := 'pFunctionName=HRI_P_WMV_SAL_CIT_SUP&'||
208 'HRI_P_GEO_REG_CN=HRI_P_GEO_REG_CN&'||
209 'HRI_P_CHAR2_GA=HRI_P_CHAR2_GA&'||
210 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&pParamIds=Y';
211
212 /* Force view by to region */
213 l_parameter_rec.view_by := 'GEOGRAPHY+REGION';
214
215 /* Get the country code */
216 l_country_code := ltrim(rtrim(l_bind_tab('GEOGRAPHY+COUNTRY').sql_bind_string
217 ,'''')
218 ,'''');
219
220 /* Get SQL for workforce fact */
221 l_wrkfc_fact_params.bind_format := 'PMV';
222 l_wrkfc_fact_params.include_comp := 'Y';
223 l_wrkfc_fact_params.include_hdc := 'Y';
224 l_wrkfc_fact_params.include_sal := 'Y';
225 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
226 (p_parameter_rec => l_parameter_rec,
227 p_bind_tab => l_bind_tab,
228 p_wrkfc_params => l_wrkfc_fact_params,
229 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_PVT.GET_SQL_RGN2');
230
231 l_SQLText :=
232 ' -- Headcount and Salary by Region Status
233 SELECT
234 tab.order_by HRI_P_ORDER_BY_1
235 ,rgn.value HRI_P_MEASURE1
236 ,tab.wmv_curr HRI_P_WMV_SUM_MV
237 ,tab.wmv_prev HRI_P_WMV_SUM_PREV_MV
238 ,tab.sal_curr HRI_P_MEASURE2
239 ,tab.sal_prev HRI_P_MEASURE3
240 ,tab.avg_sal HRI_P_MEASURE4 ' || g_rtn ||
241 /* Test whether tot_wmv_prev or tot_sal_prev is zero */
242 ',DECODE(DECODE(tab.tot_wmv_prev, 0, 0,
243 tab.tot_sal_prev / tab.tot_wmv_prev), ' || g_rtn ||
244 /* If either is zero then return 0 if the total salary is zero otherwise 100 */
245 ' 0, DECODE(tab.tot_sal_curr, 0, 0, 100), ' || g_rtn ||
246 /* Otherwise if tot_sal_prev <> 0 and tot_wmv_prev <> 0 */
247 ' ((DECODE(tab.tot_wmv_curr, 0, 0,
248 (tab.tot_sal_curr / tab.tot_wmv_curr)
249 ) -
250 (tab.tot_sal_prev/tab.tot_wmv_prev)
251 ) /
252 (tab.tot_sal_prev/tab.tot_wmv_prev)
253 ) * 100) HRI_P_GRAND_TOTAL1
254 ,DECODE(tab.tot_wmv_curr,
255 0, tab.tot_sal_curr,
256 tab.tot_sal_curr / tab.tot_wmv_curr)
257 HRI_P_GRAND_TOTAL2
258 ,DECODE(tab.tot_wmv_prev, 0, 100,
259 (tab.tot_wmv_curr - tab.tot_wmv_prev) * 100 / tab.tot_wmv_prev)
260 HRI_P_GRAND_TOTAL3
261 ,tab.region_code HRI_P_GEO_REG_CN
262 ,tab.country_code HRI_P_CHAR2_GA
263 ,'''||l_drill_url||'''
264 HRI_P_CHAR1_GA
265 FROM
266 hri_dbi_cl_geo_region_v rgn
267 ,(SELECT
268 bc.vby_id region_code
269 ,:HRI_COUNTRY_CODE country_code
270 ,-bc.curr_hdc_end order_by
271 ,bc.curr_hdc_end wmv_curr
272 ,bc.comp_hdc_end wmv_prev
273 ,bc.curr_sal_end sal_curr
274 ,DECODE(bc.curr_hdc_end,
275 0, bc.curr_sal_end,
276 bc.curr_sal_end / bc.curr_hdc_end)
277 avg_sal
278 ,bc.comp_sal_end sal_prev
279 ,SUM(bc.curr_hdc_end) OVER () tot_wmv_curr
280 ,SUM(bc.curr_sal_end) OVER () tot_sal_curr
281 ,SUM(bc.comp_total_hdc_end) OVER () tot_wmv_prev
282 ,SUM(bc.comp_total_sal_end) OVER () tot_sal_prev
283 FROM
284 (' || l_wrkfc_fact_sql || ') bc
285 WHERE (bc.curr_hdc_end > 0
286 OR bc.comp_hdc_end > 0
287 OR bc.curr_sal_end > 0
288 OR bc.comp_sal_end > 0)
289 ) tab
290 WHERE tab.region_code = rgn.id
291 ' || l_security_clause || '
292 ORDER BY
293 HRI_P_ORDER_BY_1
294 ,HRI_P_MEASURE4
295 ,HRI_P_MEASURE1';
296
297 x_custom_sql := l_SQLText ;
298
299 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
300 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
301 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
302 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
303 x_custom_output.extend;
304 x_custom_output(1) := l_custom_rec;
305
306 l_custom_rec.attribute_name := ':GLOBAL_RATE';
307 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
308 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
309 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
310 x_custom_output.extend;
311 x_custom_output(2) := l_custom_rec;
312
313 l_custom_rec.attribute_name := ':HRI_COUNTRY_CODE';
314 l_custom_rec.attribute_value := l_country_code;
315 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
316 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
317 x_custom_output.extend;
318 x_custom_output(3) := l_custom_rec;
319
320 END get_sql_rgn2;
321 --
322 --****************************************************************************
323 --* RE-ARCH *
324 --* AK SQL For Headcount and Salary by City *
325 --* AK City : HRI_P_WMV_SAL_CIT_SUP *
326 --****************************************************************************
327 --
328 PROCEDURE get_sql_cit2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
329 ,x_custom_sql OUT NOCOPY VARCHAR2
330 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
331 )
332 IS
333
334 l_SQLText VARCHAR2(32000);
335 l_security_clause VARCHAR2(4000);
336 l_custom_rec BIS_QUERY_ATTRIBUTES ;
337
338 /* Dynamic SQL Controls */
339 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
340 l_wrkfc_fact_sql VARCHAR2(10000);
341
342 /* Pre-calculations */
343 l_drill_url VARCHAR2(1000);
344
345 /* Parameter values */
346 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
347 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
348 l_debug_header VARCHAR(550);
349 l_country_code VARCHAR2(240);
350 l_region_code VARCHAR(240);
351
352 BEGIN
353 /* Initialize out parameters */
354 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
355 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
356
357 /* Get security clause for Manager based security */
358 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
359
360 /* Get common parameter values */
361 hri_oltp_pmv_util_param.get_parameters_from_table
362 (p_page_parameter_tbl => p_page_parameter_tbl,
363 p_parameter_rec => l_parameter_rec,
364 p_bind_tab => l_bind_tab);
365
366 /* Get parameter values for geography */
367 l_region_code := ltrim(rtrim(l_bind_tab('GEOGRAPHY+REGION').sql_bind_string
368 ,'''')
369 ,'''');
370
371 l_country_code := ltrim(rtrim(l_bind_tab('GEOGRAPHY+COUNTRY').sql_bind_string
372 ,'''')
373 ,'''');
374
375 /* bug 3330395 */
376 IF (l_region_code = '-1') THEN
377 l_region_code := 'NA_EDW';
378 END IF; /* bug 3330395 */
379
380 /* Set l_drill_url to null to turnoff drill Drilling to Rearch Report*/
381 l_drill_url := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&'||
382 'HRI_P_GEO_CIT_CN=HRI_P_GEO_CIT_CN&'||
383 'HRI_P_GEO_REG_CN=HRI_P_CHAR3_GA&'||
384 'HRI_P_CHAR4_GA=HRI_P_CHAR4_GA&'||
385 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&pParamIds=Y';
386
387 /* Force city view by */
388 l_parameter_rec.view_by := 'GEOGRAPHY+CITY';
389
390 /* Get SQL for workforce fact */
391 l_wrkfc_fact_params.bind_format := 'PMV';
392 l_wrkfc_fact_params.include_comp := 'Y';
393 l_wrkfc_fact_params.include_hdc := 'Y';
394 l_wrkfc_fact_params.include_sal := 'Y';
395 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
396 (p_parameter_rec => l_parameter_rec,
397 p_bind_tab => l_bind_tab,
398 p_wrkfc_params => l_wrkfc_fact_params,
399 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_PVT.GET_SQL_CIT2');
400
401 /* Format SQL Query for report */
402 l_SQLText :=
403 ' -- Headcount and Salary by City Status
404 SELECT
405 tab.order_by HRI_P_ORDER_BY_1
406 ,cty.value HRI_P_MEASURE1
407 ,tab.wmv_curr,0) HRI_P_WMV_SUM_MV
408 ,tab.wmv_prev,0) HRI_P_WMV_SUM_PREV_MV
409 ,tab.sal_curr,0) HRI_P_MEASURE2
410 ,tab.sal_prev,0) HRI_P_MEASURE3
411 ,tab.avg_sal HRI_P_MEASURE4 ' || g_rtn ||
412 /* Test whether tot_wmv_prev or tot_sal_prev is zero */
413 ',DECODE(DECODE(tab.tot_wmv_prev, 0, 0,
414 tab.tot_sal_prev / tab.tot_wmv_prev), ' || g_rtn ||
415 /* If either is zero then return 0 if the total salary is zero otherwise 100 */
416 ' 0, DECODE(tab.tot_sal_curr, 0, 0, 100), ' || g_rtn ||
417 /* Otherwise if tot_sal_prev <> 0 and tot_wmv_prev <> 0 */
418 ' ((DECODE(tab.tot_wmv_curr, 0, 0,
419 (tab.tot_sal_curr / tab.tot_wmv_curr)
420 ) -
421 (tab.tot_sal_prev / tab.tot_wmv_prev)
422 ) /
423 (tab.tot_sal_prev / tab.tot_wmv_prev)
424 ) * 100) HRI_P_GRAND_TOTAL1
425 ,DECODE(tab.tot_wmv_curr,
426 0, tab.tot_sal_curr,
427 tab.tot_sal_curr/tab.tot_wmv_curr)
428 HRI_P_GRAND_TOTAL2
429 ,DECODE(tab.tot_wmv_prev, 0, 100,
430 ((tab.tot_wmv_curr - tab.tot_wmv_prev) * 100 / tab.tot_wmv_prev)
431 HRI_P_GRAND_TOTAL3
432 ,tab.country_code HRI_P_CHAR4_GA
433 ,tab.region_code HRI_P_CHAR3_GA
434 ,cty.id HRI_P_GEO_CIT_CN
435 ,'''||l_drill_url||''' HRI_P_CHAR1_GA
436 FROM(
437 hri_dbi_cl_geo_city_v cty
438 ,(SELECT
439 bc.vby_id city_cid
440 ,:HRI_COUNTRY_CODE country_code
441 ,:HRI_REGION_CODE region_code
442 ,-bc.curr_hdc_end order_by
443 ,bc.curr_hdc_end wmv_curr
444 ,bc.comp_hdc_end wmv_prev
445 ,bc.curr_sal_end sal_curr
446 ,DECODE(bc.curr_hdc_end,
447 0, bc.curr_sal_end,
448 bc.curr_sal_end / bc.curr_hdc_end)
449 avg_sal
450 ,bc.comp_sal_end sal_prev
451 ,SUM(bc.curr_hdc_end) OVER () tot_wmv_curr
452 ,SUM(bc.curr_sal_end) OVER () tot_sal_curr
453 ,SUM(bc.comp_total_hdc_end) OVER () tot_wmv_prev
454 ,SUM(bc.comp_total_sal_end) OVER () tot_sal_prev
455 FROM
456 (' || l_wrkfc_fact_sql || ') bc
457 WHERE (bc.curr_hdc_end > 0
458 OR bc.comp_hdc_end > 0
459 OR bc.curr_sal_end > 0
460 OR bc.comp_sal_end > 0)
461 ) tab
462 WHERE tab.city_cid = cty.id
463 ' || l_security_clause || '
464 ORDER BY
465 HRI_P_ORDER_BY_1
466 ,HRI_P_MEASURE4
467 ,HRI_P_MEASURE1';
468
469 x_custom_sql := l_SQLText ;
470
471 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
472 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
473 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
474 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
475 x_custom_output.extend;
476 x_custom_output(1) := l_custom_rec;
477
478 l_custom_rec.attribute_name := ':GLOBAL_RATE';
479 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
480 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
481 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
482 x_custom_output.extend;
483 x_custom_output(2) := l_custom_rec;
484
485 l_custom_rec.attribute_name := ':HRI_COUNTRY_CODE';
486 l_custom_rec.attribute_value := l_country_code;
487 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
488 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
489 x_custom_output.extend;
490 x_custom_output(3) := l_custom_rec;
491
492 l_custom_rec.attribute_name := ':HRI_REGION_CODE';
493 l_custom_rec.attribute_value := l_region_code;
494 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
495 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
496 x_custom_output.extend;
497 x_custom_output(4) := l_custom_rec;
498
499 END get_sql_cit2;
500
501 --
502 --****************************************************************************
503 --* RE-ARCH *
504 --* AK SQL For Headcount and Salary by Job Function *
505 --* AK City : HRI_P_WMV_SAL_JFM_SUP *
506 --****************************************************************************
507 --
508 PROCEDURE get_sql_jfm2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
509 x_custom_sql OUT NOCOPY VARCHAR2,
510 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
511
512 l_sqltext VARCHAR2(32767) ;
513 l_security_clause VARCHAR2(4000);
514 l_custom_rec BIS_QUERY_ATTRIBUTES;
515
516 /* Parameter values */
517 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
518 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
519
520 /* Dynamic SQL Controls */
521 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
522 l_wrkfc_fact_sql VARCHAR2(10000);
523
524 /* Pre-calculations */
525
526 BEGIN
527
528 /* Initialize out parameters */
529 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
530 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
531
532 /* Get security clause for Manager based security */
533 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
534
535 /* Get common parameter values */
536 hri_oltp_pmv_util_param.get_parameters_from_table
537 (p_page_parameter_tbl => p_page_parameter_tbl,
538 p_parameter_rec => l_parameter_rec,
539 p_bind_tab => l_bind_tab);
540
541 /* Force view by to job level 1 */
542 l_parameter_rec.view_by := 'JOB+JOB_FUNCTION';
543
544 /* Get SQL for workforce fact */
545 l_wrkfc_fact_params.bind_format := 'PMV';
546 l_wrkfc_fact_params.include_comp := 'Y';
547 l_wrkfc_fact_params.include_hdc := 'Y';
548 l_wrkfc_fact_params.include_sal := 'Y';
549 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
550 (p_parameter_rec => l_parameter_rec,
551 p_bind_tab => l_bind_tab,
552 p_wrkfc_params => l_wrkfc_fact_params,
553 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_PVT.GET_SQL_JFM2');
554
555 /* Format SQL Query for report */
556 l_sqltext :=
557 'SELECT -- Salary by Job Function Status
558 tab.order_by HRI_P_ORDER_BY_1
559 ,jfn.value HRI_P_JOB_FAMILY_CN
560 ,tab.wmv_curr HRI_P_WMV_SUM_MV
561 ,tab.sal_curr HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
562 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
563 (p_previous_col => 'tab.sal_prev',
564 p_current_col => 'tab.sal_curr') || '
565 HRI_P_MEASURE5
566 ,tab.avg_sal_curr HRI_P_MEASURE1
567 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
568 (p_previous_col => 'tab.avg_sal_prev',
569 p_current_col => 'tab.avg_sal_curr') || '
570 HRI_P_MEASURE2
571 ,tab.avg_sal_prev HRI_P_MEASURE3
572 ,tab.tot_sal_prev HRI_P_MEASURE4
573 ,tab.tot_wmv_curr HRI_P_GRAND_TOTAL1
574 ,tab.tot_sal_curr HRI_P_GRAND_TOTAL2
575 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
576 (p_previous_col => 'tab.tot_sal_prev',
577 p_current_col => 'tab.tot_sal_curr') || '
578 HRI_P_GRAND_TOTAL7
579 ,DECODE(tab.tot_wmv_curr, 0, to_number(null),
580 tab.tot_sal_curr / tab.tot_wmv_curr)
581 HRI_P_GRAND_TOTAL3
582 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
583 (p_previous_col =>
584 'DECODE(tab.tot_wmv_prev, 0, 0, tab.tot_sal_prev/tab.tot_wmv_prev)',
585 p_current_col =>
586 'DECODE(SUM(tab.wmv_curr) over(), 0, to_number(null),
587 (SUM(tab.sal_curr) over() / SUM(tab.wmv_curr) over()))') || '
588 HRI_P_GRAND_TOTAL4
589 ,decode(tab.tot_wmv_prev,
590 0, to_number(null),
591 tab.tot_sal_prev/tab.tot_wmv_prev)
592 HRI_P_GRAND_TOTAL5
593 ,tab.tot_sal_prev HRI_P_GRAND_TOTAL6
594 ,tab.job_fnctn_code HRI_P_CHAR1_GA
595 FROM
596 hri_cl_job_function_v jfn
597 ,(SELECT
598 bc.vby_id job_fnctn_code
599 ,-bc.curr_hdc_end order_by
600 ,bc.curr_hdc_end wmv_curr
601 ,bc.comp_hdc_end wmv_prev
602 ,bc.curr_sal_end sal_curr
603 ,DECODE(bc.curr_hdc_end,
604 0, to_number(null),
605 bc.curr_sal_end / bc.curr_hdc_end)
606 avg_sal_curr
607 ,DECODE(bc.comp_hdc_end,
608 0, to_number(null),
609 bc.comp_sal_end / bc.comp_hdc_end)
610 avg_sal_prev
611 ,bc.comp_sal_end sal_prev
612 ,SUM(bc.curr_hdc_end) OVER () tot_wmv_curr
613 ,SUM(bc.curr_sal_end) OVER () tot_sal_curr
614 ,SUM(bc.comp_total_hdc_end) OVER () tot_wmv_prev
615 ,SUM(bc.comp_total_sal_end) OVER () tot_sal_prev
616 FROM
617 (' || l_wrkfc_fact_sql || ') bc
618 WHERE (bc.curr_hdc_end > 0
619 OR bc.comp_hdc_end > 0
620 OR bc.curr_sal_end > 0
621 OR bc.comp_sal_end > 0)
622 ) tab
623 WHERE tab.job_fnctn_code = jfn.id
624 ' || l_security_clause || '
625 &ORDER_BY_CLAUSE';
626
627 x_custom_sql := l_SQLText;
628
629 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
630 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
631 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
632 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
633 x_custom_output.extend;
634 x_custom_output(1) := l_custom_rec;
635
636 l_custom_rec.attribute_name := ':GLOBAL_RATE';
637 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
638 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
639 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
640 x_custom_output.extend;
641 x_custom_output(2) := l_custom_rec;
642
643 END get_sql_jfm2;
644
645 --
646 --****************************************************************************
647 --* RE-ARCH *
648 --* AK SQL For Headcount and Salary by Job Family *
649 --* AK City : HRI_P_WMV_SAL_JFMFN_SUP *
650 --****************************************************************************
651 --
652 PROCEDURE get_sql_jfmfn2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
653 x_custom_sql OUT NOCOPY VARCHAR2,
654 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
655
656 l_sqltext VARCHAR2(32767) ;
657 l_security_clause VARCHAR2(4000);
658 l_job_family_condition VARCHAR2(100);
659
660 l_custom_rec BIS_QUERY_ATTRIBUTES;
661
662 /* Parameter values */
663 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
664 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
665
666 /* Dynamic SQL Controls */
667 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
668 l_wrkfc_fact_sql VARCHAR2(10000);
669
670 /* Pre-calculations */
671
672 l_job_function_code VARCHAR2(240);
673
674 BEGIN
675
676 /* Initialize out parameters */
677 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
678 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
679
680 /* Get security clause for Manager based security */
681 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
682
683 /* Get common parameter values */
684 hri_oltp_pmv_util_param.get_parameters_from_table
685 (p_page_parameter_tbl => p_page_parameter_tbl,
686 p_parameter_rec => l_parameter_rec,
687 p_bind_tab => l_bind_tab);
688
689 /* Get the job level 1 parameter, if set */
690 BEGIN
691 l_job_function_code := ltrim(rtrim(l_bind_tab('JOB+JOB_FUNCTION').sql_bind_string
692 ,'''')
693 ,'''');
694 EXCEPTION WHEN OTHERS THEN
695 null;
696 END;
697
698 /* Set job family value for Unassigned */
699 IF (l_job_function_code = '-1') THEN
700 l_job_function_code := 'NA_EDW';
701 END IF;
702
703 /* Force view by to job level 2 */
704 l_parameter_rec.view_by := 'JOB+JOB_FAMILY';
705
706 /* Get SQL for workforce fact */
707 l_wrkfc_fact_params.bind_format := 'PMV';
708 l_wrkfc_fact_params.include_comp := 'Y';
709 l_wrkfc_fact_params.include_hdc := 'Y';
710 l_wrkfc_fact_params.include_sal := 'Y';
711 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
712 (p_parameter_rec => l_parameter_rec,
713 p_bind_tab => l_bind_tab,
714 p_wrkfc_params => l_wrkfc_fact_params,
715 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP_PVT.GET_SQL_JFMFN2');
716
717 /* Format SQL Query for report */
718 l_sqltext :=
719 'SELECT -- Salary by Job Family Status
720 tab.order_by HRI_P_ORDER_BY_1
721 ,jfm.value HRI_P_JOB_LVL2_CN
722 ,tab.wmv_curr HRI_P_WMV_SUM_MV
723 ,tab.sal_curr HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
724 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
725 (p_previous_col => 'tab.sal_prev',
726 p_current_col => 'tab.sal_curr') || '
727 HRI_P_MEASURE5
728 ,tab.avg_sal_curr HRI_P_MEASURE1
729 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
730 (p_previous_col => 'tab.avg_sal_prev',
731 p_current_col => 'tab.avg_sal_curr') || '
732 HRI_P_MEASURE2
733 ,tab.avg_sal_prev HRI_P_MEASURE3
734 ,tab.tot_sal_prev HRI_P_MEASURE4
735 ,tab.tot_wmv_curr HRI_P_GRAND_TOTAL1
736 ,tab.tot_sal_curr HRI_P_GRAND_TOTAL2
737 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
738 (p_previous_col => 'tab.tot_sal_prev',
739 p_current_col => 'tab.tot_sal_curr') || '
740 HRI_P_GRAND_TOTAL7
741 ,DECODE(tab.tot_wmv_curr, 0, to_number(null),
742 tab.tot_sal_curr / tab.tot_wmv_curr)
743 HRI_P_GRAND_TOTAL3
744 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
745 (p_previous_col =>
746 'DECODE(tab.tot_wmv_prev, 0, to_number(null), tab.tot_sal_prev/tab.tot_wmv_prev)',
747 p_current_col =>
748 'DECODE(SUM(tab.wmv_curr) over(), 0, to_number(null),
749 (SUM(tab.sal_curr) over() / SUM(tab.wmv_curr) over()))') || '
750 HRI_P_GRAND_TOTAL4
751 ,decode(tab.tot_wmv_prev,
752 0, to_number(null),
753 tab.tot_sal_prev/tab.tot_wmv_prev)
754 HRI_P_GRAND_TOTAL5
755 ,tab.tot_sal_prev HRI_P_GRAND_TOTAL6
756 ,:HRI_JOB_FUNCTION HRI_P_CHAR1_GA
757 ,tab.job_fmly_code HRI_P_CHAR2_GA
758 FROM
759 hri_cl_job_family_v jfm
760 ,(SELECT
761 bc.vby_id job_fmly_code
762 ,-bc.curr_hdc_end order_by
763 ,bc.curr_hdc_end wmv_curr
764 ,bc.comp_hdc_end wmv_prev
765 ,bc.curr_sal_end sal_curr
766 ,DECODE(bc.curr_hdc_end,
767 0, to_number(null),
768 bc.curr_sal_end / bc.curr_hdc_end)
769 avg_sal_curr
770 ,DECODE(bc.comp_hdc_end,
771 0, to_number(null),
772 bc.comp_sal_end / bc.comp_hdc_end)
773 avg_sal_prev
774 ,bc.comp_sal_end sal_prev
775 ,SUM(bc.curr_hdc_end) OVER () tot_wmv_curr
776 ,SUM(bc.curr_sal_end) OVER () tot_sal_curr
777 ,SUM(bc.comp_total_hdc_end) OVER () tot_wmv_prev
778 ,SUM(bc.comp_total_sal_end) OVER () tot_sal_prev
779 FROM
780 (' || l_wrkfc_fact_sql || ') bc
781 WHERE (bc.curr_hdc_end > 0
782 OR bc.comp_hdc_end > 0
783 OR bc.curr_sal_end > 0
784 OR bc.comp_sal_end > 0)
785 ) tab
786 WHERE tab.job_fmly_code = jfm.id
787 ' || l_security_clause || '
788 &ORDER_BY_CLAUSE';
789
790 x_custom_sql := l_SQLText;
791
792 l_custom_rec.attribute_name := ':HRI_JOB_FUNCTION';
793 l_custom_rec.attribute_value := l_job_function_code;
794 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
795 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
796 x_custom_output.extend;
797 x_custom_output(1) := l_custom_rec;
798
799 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
800 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
801 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
802 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
803 x_custom_output.extend;
804 x_custom_output(2) := l_custom_rec;
805
806 l_custom_rec.attribute_name := ':GLOBAL_RATE';
807 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
808 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
809 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
810 x_custom_output.extend;
811 x_custom_output(3) := l_custom_rec;
812
813 END get_sql_jfmfn2;
814
815 END HRI_OLTP_PMV_WMV_SAL_SUP_PVT;