[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_HDCNT_ORGMGR
Source
1 PACKAGE BODY HRI_OLTP_PMV_HDCNT_ORGMGR AS
2 /* $Header: hriophom.pkb 120.12 2006/03/14 20:13:23 rlpatil noship $ */
3
4 -- ----------------------------------------------------
5 -- This procedure frames the Query for the Headcount KPI.
6 -- ----------------------------------------------------
7
8
9 PROCEDURE GET_KPI_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
10 x_custom_sql OUT NOCOPY VARCHAR2,
11 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
12
13 l_sqltext VARCHAR2(32767);
14 l_custom_rec BIS_QUERY_ATTRIBUTES;
15
16 /* Parameter values */
17 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
18 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
19
20
21 BEGIN
22
23
24 /* Initialize out parameters */
25
26 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
27 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
28
29
30 /* Get common parameter values */
31 hri_oltp_pmv_util_param.get_parameters_from_table
32 (p_page_parameter_tbl => p_page_parameter_tbl,
33 p_parameter_rec => l_parameter_rec,
34 p_bind_tab => l_bind_tab);
35
36 /* Build query */
37 l_sqltext :=
38
39 'SELECT -- Headcount KPI
40 ID VIEWBYID
41 ,value VIEWBY
42 ,budgeted_amount HRI_P_MEASURE1
43 ,actual_amount HRI_P_MEASURE2
44 ,(budgeted_amount - actual_amount) HRI_P_MEASURE5
45 ,prev_budgeted HRI_P_MEASURE6
46 ,prev_actual HRI_P_MEASURE7
47 ,(prev_budgeted - prev_actual) HRI_P_MEASURE9
48 ,sum(actual_amount) over() HRI_P_GRAND_TOTAL1
49 ,sum(prev_actual) over() HRI_P_GRAND_TOTAL2
50 ,sum(budgeted_amount) over() HRI_P_GRAND_TOTAL3
51 ,sum(prev_budgeted) over() HRI_P_GRAND_TOTAL4
52 ,sum((budgeted_amount - actual_amount)) over() HRI_P_GRAND_TOTAL7
53 ,sum((prev_budgeted - prev_actual)) over() HRI_P_GRAND_TOTAL8
54 FROM
55 (
56 SELECT tab.ORGMGR_ID ID
57 ,per.value
58 ,NVL(tab.budgeted_amount,0) BUDGETED_AMOUNT
59 ,NVL(tab.actual_amount,0) ACTUAL_AMOUNT
60 ,NVL(tab.prev_budgeted,0) PREV_BUDGETED
61 ,NVL(tab.prev_actual,0) PREV_ACTUAL
62 FROM
63 (
64 (
65 SELECT ORGMGR_ID,
66 SUM(ACTUAL_AMOUNT) ACTUAL_AMOUNT,
67 SUM(PREV_ACTUAL) PREV_ACTUAL,
68 SUM(BUDGETED_AMOUNT) BUDGETED_AMOUNT,
69 SUM(PREV_BUDGETED) PREV_BUDGETED
70 FROM
71 (
72 (
73 SELECT ORGMGR_ID,
74 null BUDGETED_AMOUNT,
75 null PREV_BUDGETED,
76 sum(DIRECT_HEADCOUNT) ACTUAL_AMOUNT,
77 HRI_OLTP_PMV_HDCNT_ORGMGR.GET_KPI_MGR_TOTALS
78 (&HRI_PERSON+HRI_PER_USRDR_H
79 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
80 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
81 ,''ACTUAL''
82 ) PREV_ACTUAL
83 FROM HRI_MDP_WRKFC_MV
84 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
85 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
86 GROUP BY ORGMGR_ID )
87 UNION ALL
88 (SELECT ORGMGR_ID,
89 SUM(HEADCOUNT_VALUE) BUDGETED_AMOUNT,
90 HRI_OLTP_PMV_HDCNT_ORGMGR.GET_KPI_MGR_TOTALS
91 (&HRI_PERSON+HRI_PER_USRDR_H
92 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
93 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
94 ,''BDGT''
95 ) PREV_BUDGETED,
96 null ACTUAL_AMOUNT,
97 null PREV_ACTUAL
98 FROM
99 (SELECT ORGMGR_ID,
100 ORGANIZATION_ID,
101 POSITION_ID,
102 CASE WHEN BUDGET_AGGREGATE = ''ACCUMULATE'' THEN SUM(HEADCOUNT_VALUE)
103 WHEN BUDGET_AGGREGATE = ''AVERAGE'' THEN AVG(HEADCOUNT_VALUE)
104 WHEN BUDGET_AGGREGATE = ''MAXIMUM'' THEN MAX(HEADCOUNT_VALUE)
105 ELSE SUM(HEADCOUNT_VALUE)
106 END HEADCOUNT_VALUE
107 FROM HRI_MDP_BDGTS_HDCNT_ORGMGR_CT
108 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
109 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
110 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
111 AND ORGANIZATION_ID IN (SELECT SUB_ORGANIZATION_ID
112 FROM HRI_CS_SUPH_ORGMGR_CT
113 WHERE SUP_PERSON_ID=&HRI_PERSON+HRI_PER_USRDR_H
114 AND SUB_PERSON_ID = SUP_PERSON_ID
115 AND SUB_RELATIVE_LEVEL = 0
116 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
117
118 GROUP BY ORGMGR_ID,
119 ORGANIZATION_ID,
120 POSITION_ID,
121 BUDGET_AGGREGATE )
122 GROUP BY ORGMGR_ID
123 )
124 )
125 GROUP BY ORGMGR_ID
126 )
127 UNION ALL
128 (
129 SELECT ORGMGR_ID,
130 SUM(ACTUAL_AMOUNT) ACTUAL_AMOUNT,
131 SUM(PREV_ACTUAL) PREV_ACTUAL,
132 SUM(BUDGETED_AMOUNT) BUDGETED_AMOUNT,
133 SUM(PREV_BUDGETED) PREV_BUDGETED
134 FROM
135 (
136 (
137 SELECT ORGMGR_ID,
138 null BUDGETED_AMOUNT,
139 null PREV_BUDGETED,
140 sum(TOTAL_HEADCOUNT) ACTUAL_AMOUNT,
141 HRI_OLTP_PMV_HDCNT_ORGMGR.GET_KPI_TOTALS
142 (ORGMGR_ID
143 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
144 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
145 ,''ACTUAL''
146 ) PREV_ACTUAL
147 FROM HRI_MDP_WRKFC_ORGMGR_MV
148 WHERE ORGMGR_ID IN (SELECT SUB_PERSON_ID FROM HRI_CS_SUPH_ORGMGR_CT WHERE SUP_PERSON_ID = &HRI_PERSON+HRI_PER_USRDR_H AND SUB_RELATIVE_LEVEL =1 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
149 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
150 GROUP BY ORGMGR_ID)
151 UNION ALL
152 (SELECT ORGMGR_ID,
153 SUM(HEADCOUNT_VALUE) BUDGETED_AMOUNT,
154 HRI_OLTP_PMV_HDCNT_ORGMGR.GET_KPI_TOTALS
155 (ORGMGR_ID
156 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
157 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
158 ,''BDGT''
159 ) PREV_BUDGETED,
160 null ACTUAL_AMOUNT,
161 null PREV_ACTUAL
162 FROM
163 (SELECT ORGMGR_ID,
164 ORGANIZATION_ID,
165 POSITION_ID,
166 CASE WHEN BUDGET_AGGREGATE = ''ACCUMULATE'' THEN SUM(HEADCOUNT_VALUE)
167 WHEN BUDGET_AGGREGATE = ''AVERAGE'' THEN AVG(HEADCOUNT_VALUE)
168 WHEN BUDGET_AGGREGATE = ''MAXIMUM'' THEN MAX(HEADCOUNT_VALUE)
169 ELSE SUM(HEADCOUNT_VALUE)
170 END HEADCOUNT_VALUE
171 FROM HRI_MDP_BDGTS_HDCNT_ORGMGR_CT
172 WHERE ORGMGR_ID IN (SELECT SUB_PERSON_ID FROM HRI_CS_SUPH_ORGMGR_CT WHERE SUP_PERSON_ID = &HRI_PERSON+HRI_PER_USRDR_H AND SUB_RELATIVE_LEVEL =1 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
173 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
174 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
175 GROUP BY ORGMGR_ID,
176 ORGANIZATION_ID,
177 POSITION_ID,
178 BUDGET_AGGREGATE )
179 GROUP BY ORGMGR_ID
180 )
181 )
182 GROUP BY ORGMGR_ID
183 )
184 ) tab,
185 HRI_DBI_CL_PER_N_V per
186 WHERE tab.ORGMGR_ID = per.ID
187 AND &BIS_CURRENT_ASOF_DATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE
188 )';
189
190 x_custom_sql := l_SQLText;
191
192 END GET_KPI_SQL;
193
194 -- ----------------------------------------------------
195 -- This procedure frames the Query for the Report Headcount Distribution By Organization.
196 -- ----------------------------------------------------
197
198 PROCEDURE GET_ORG_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
199 x_custom_sql OUT NOCOPY VARCHAR2,
200 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
201
202 l_sqltext VARCHAR2(32767);
203 l_custom_rec BIS_QUERY_ATTRIBUTES;
204 l_trend_table VARCHAR2(4000);
205 l_previous_periods NUMBER;
206 l_projection_periods NUMBER;
207
208 /* Parameter values */
209 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
210 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
211
212 /* Pre-calculations */
213 l_period_ago_total_sal NUMBER;
214 l_period_ago_wmv_count NUMBER;
215 l_period_ago_dr_count NUMBER;
216 l_period_ago_dr_sal NUMBER;
217 l_tot_wmv_start NUMBER;
218
219
220 BEGIN
221
222 /* Initialize out parameters */
223
224 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
225 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
226
227
228 /* Get common parameter values */
229 hri_oltp_pmv_util_param.get_parameters_from_table
230 (p_page_parameter_tbl => p_page_parameter_tbl,
231 p_parameter_rec => l_parameter_rec,
232 p_bind_tab => l_bind_tab);
233
234
235 HRI_OLTP_PMV_QUERY_TIME.GET_TIME_CLAUSE
236 (p_projection_type => 'N'
237 ,p_page_period_type => l_parameter_rec.page_period_type
238 ,p_page_comp_type => l_parameter_rec.time_comparison_type
239 ,o_trend_table => l_trend_table
240 ,o_previous_periods => l_previous_periods
241 ,o_projection_periods => l_projection_periods );
242
243
244
245 /* Build query */
246 l_sqltext :=
247 'SELECT -- Headcount Distribution By Organization
248 name HRI_P_ORDER_BY_1
249 ,name HRI_P_CHAR1_GA
250 ,budgeted_amount HRI_P_MEASURE1
251 ,actual_amount HRI_P_MEASURE3
252 ,budgeted_amount- actual_amount HRI_P_MEASURE5
253 ,((( budgeted_amount- actual_amount) - prev_available)/decode( prev_available,0,1, prev_available))*100 HRI_P_MEASURE6
254 ,sum( budgeted_amount) over() HRI_P_GRAND_TOTAL1
255 ,sum( actual_amount) over() HRI_P_GRAND_TOTAL2
256 ,sum( budgeted_amount- actual_amount) over() HRI_P_GRAND_TOTAL3
257 ,((sum( budgeted_amount- actual_amount) over() - sum( prev_available) over() )/DECODE(sum( prev_available) over(),0,1,sum( prev_available) over()))*100 HRI_P_GRAND_TOTAL4
258 FROM
259 (
260 SELECT ORGANIZATION_ID ID,
261 HR_GENERAL.DECODE_ORGANIZATION(ORGANIZATION_ID) NAME,
262 NVL(SUM(BUDGETED_AMOUNT),0) BUDGETED_AMOUNT,
263 NVL(SUM(ACTUAL_AMOUNT),0) ACTUAL_AMOUNT,
264 HRI_OLTP_PMV_HDCNT_ORGMGR.CALC_PREV_VALUE
265 (&HRI_PERSON+HRI_PER_USRDR_H
266 ,ORGANIZATION_ID
267 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
268 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
269 ,''AVAIL''
270 ) PREV_AVAILABLE
271 FROM
272 (
273 (SELECT ORGANIZATION_ID,
274 null BUDGETED_AMOUNT,
275 TOTAL_HEADCOUNT ACTUAL_AMOUNT
276 FROM HRI_MDP_WRKFC_ORGMGR_MV
277 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
278 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
279 UNION ALL
280 SELECT ORGANIZATION_ID,
281 SUM(BUDGETED_AMOUNT) BUDGETED_AMOUNT,
282 null ACTUAL_AMOUNT
283 FROM
284 (
285 SELECT ORGANIZATION_ID,
286 POSITION_ID,
287 CASE WHEN BUDGET_AGGREGATE = ''ACCUMULATE'' THEN SUM(HEADCOUNT_VALUE)
288 WHEN BUDGET_AGGREGATE = ''AVERAGE'' THEN AVG(HEADCOUNT_VALUE)
289 WHEN BUDGET_AGGREGATE = ''MAXIMUM'' THEN MAX(HEADCOUNT_VALUE)
290 ELSE SUM(HEADCOUNT_VALUE)
291 END BUDGETED_AMOUNT
292 FROM HRI_MDP_BDGTS_HDCNT_ORGMGR_CT
293 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
294 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
295 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
296 GROUP BY ORGANIZATION_ID,
297 POSITION_ID,
301 )
298 BUDGET_AGGREGATE
299 )
300 GROUP BY ORGANIZATION_ID
302 GROUP BY ORGANIZATION_ID
303 )
304 &ORDER_BY_CLAUSE';
305
306
307 x_custom_sql := l_SQLText;
308
309 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
310 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
311 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
312 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
313 x_custom_output.extend;
314 x_custom_output(1) := l_custom_rec;
315
316 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
317 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
318 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
319 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
320 x_custom_output.extend;
321 x_custom_output(2) := l_custom_rec;
322
323 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
324 l_custom_rec.attribute_value := l_previous_periods;
325 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
326 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
327 x_custom_output.extend;
328 x_custom_output(3) := l_custom_rec;
329
330 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
331 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
332 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
333 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
334 x_custom_output.extend;
335 x_custom_output(4) := l_custom_rec;
336
337 l_custom_rec.attribute_name := ':GLOBAL_RATE';
338 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
339 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
340 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
341 x_custom_output.extend;
342 x_custom_output(5) := l_custom_rec;
343
344 l_custom_rec.attribute_name := ':HRI_TOT_WMV_PREV';
345 l_custom_rec.attribute_value := l_period_ago_wmv_count;
346 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
347 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
348 x_custom_output.extend;
349 x_custom_output(6) := l_custom_rec;
350
351 l_custom_rec.attribute_name := ':HRI_TOT_WMV_START';
352 l_custom_rec.attribute_value := l_tot_wmv_start;
353 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
354 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
355 x_custom_output.extend;
356 x_custom_output(7) := l_custom_rec;
357
358 END GET_ORG_SQL;
359
360
361 -- ----------------------------------------------------
362 -- This procedure calculates the Budgeted, Commitment and actual Headcount Grand Totals for
363 -- the Headcount KPI.
364 -- ----------------------------------------------------
365
366 FUNCTION GET_KPI_TOTALS(p_ORGMGR_ID NUMBER,
367 p_effective_start_date DATE,
368 p_effective_end_date DATE,
369 p_type VARCHAR2
370 )
371 RETURN NUMBER IS
372
373 CURSOR Budget IS
374 SELECT SUM(BUDGETED) BUDGETED
375 FROM
376 (SELECT ORGANIZATION_ID,
377 POSITION_ID,
378 NVL(CASE WHEN BUDGET_AGGREGATE = 'ACCUMULATE' THEN SUM(HEADCOUNT_VALUE)
379 WHEN BUDGET_AGGREGATE = 'AVERAGE' THEN AVG(HEADCOUNT_VALUE)
380 WHEN BUDGET_AGGREGATE = 'MAXIMUM' THEN MAX(HEADCOUNT_VALUE)
381 ELSE SUM(HEADCOUNT_VALUE)
382 END,0) budgeted
383 FROM HRI_MDP_BDGTS_HDCNT_ORGMGR_CT
384 WHERE ORGMGR_ID = p_ORGMGR_ID
385 AND EFFECTIVE_START_DATE <= p_effective_end_date
386 AND EFFECTIVE_END_DATE >= p_effective_start_date
387 GROUP BY BUDGET_AGGREGATE,
388 ORGANIZATION_ID,
389 POSITION_ID) ;
390
391 CURSOR actls IS
392 SELECT NVL(TOTAL_HEADCOUNT,0) actual
393 FROM HRI_MDP_WRKFC_ORGMGR_MV
394 WHERE ORGMGR_ID = p_ORGMGR_ID
395 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
396
397 l_budgeted_amount number;
398 l_actual_amount number;
399 l_available number := 0;
400
401 BEGIN
402
403 OPEN Budget;
404 FETCH Budget INTO l_budgeted_amount;
405 CLOSE Budget;
406
407 OPEN actls;
408 FETCH actls into l_actual_amount;
409 CLOSE actls;
410
411 l_available := l_budgeted_amount - l_actual_amount;
412
413 IF (p_type = 'AVAIL') THEN
414 return l_available;
415 ELSIF (p_type = 'ACTUAL') THEN
416 return l_actual_amount;
417 ELSIF (p_type = 'BDGT') THEN
418 return l_budgeted_amount;
419 ELSE
420 return 0;
421 END IF;
422
423 END GET_KPI_TOTALS;
424
425
426 -- ----------------------------------------------------
427 -- This procedure calculates the Budgeted, Commitment and actual Headcount Grand Totals of
428 -- the organizations directly owned by the Manager for the Headcount KPI.
429 -- ----------------------------------------------------
430
431 FUNCTION GET_KPI_MGR_TOTALS(p_ORGMGR_ID NUMBER,
432 p_effective_start_date DATE,
433 p_effective_end_date DATE,
434 p_type VARCHAR2
435 )
436 RETURN NUMBER IS
437
438 CURSOR Budget IS
439 SELECT SUM(BUDGETED) BUDGETED
440 FROM
441 (SELECT ORGANIZATION_ID,
442 POSITION_ID,
443 NVL(CASE WHEN BUDGET_AGGREGATE = 'ACCUMULATE' THEN SUM(HEADCOUNT_VALUE)
444 WHEN BUDGET_AGGREGATE = 'AVERAGE' THEN AVG(HEADCOUNT_VALUE)
445 WHEN BUDGET_AGGREGATE = 'MAXIMUM' THEN MAX(HEADCOUNT_VALUE)
446 ELSE SUM(HEADCOUNT_VALUE)
447 END,0) budgeted
448 FROM HRI_MDP_BDGTS_HDCNT_ORGMGR_CT
449 WHERE ORGMGR_ID = p_ORGMGR_ID
450 AND EFFECTIVE_START_DATE <= p_effective_end_date
451 AND EFFECTIVE_END_DATE >= p_effective_start_date
452 AND ORGANIZATION_ID IN(SELECT SUB_ORGANIZATION_ID
453 FROM HRI_CS_SUPH_ORGMGR_CT
454 WHERE SUP_PERSON_ID=p_ORGMGR_ID
455 AND SUB_PERSON_ID = SUP_PERSON_ID
456 AND SUB_RELATIVE_LEVEL = 0
457 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
458 GROUP BY BUDGET_AGGREGATE,
459 ORGANIZATION_ID,
460 POSITION_ID );
461
462 CURSOR actls IS
463 SELECT NVL(TOTAL_HEADCOUNT,0) actual
464 FROM HRI_MDP_WRKFC_ORGMGR_MV
465 WHERE ORGMGR_ID = p_ORGMGR_ID
466 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
467 AND ORGANIZATION_ID IN(SELECT SUB_ORGANIZATION_ID
468 FROM HRI_CS_SUPH_ORGMGR_CT
469 WHERE SUP_PERSON_ID=p_ORGMGR_ID
470 AND SUB_PERSON_ID = SUP_PERSON_ID
471 AND SUB_RELATIVE_LEVEL = 0
472 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE);
473 l_budgeted_amount number;
474 l_actual_amount number;
475 l_available number := 0;
476
477 BEGIN
478
479 OPEN Budget;
480 FETCH Budget INTO l_budgeted_amount;
481 CLOSE Budget;
482
483 OPEN actls;
484 FETCH actls into l_actual_amount;
485 CLOSE actls;
486
487 l_available := l_budgeted_amount - l_actual_amount;
488
489 IF (p_type = 'AVAIL') THEN
490 return l_available;
491 ELSIF (p_type = 'ACTUAL') THEN
492 return l_actual_amount;
493 ELSIF (p_type = 'BDGT') THEN
494 return l_budgeted_amount;
495 ELSE
496 return 0;
497 END IF;
498
499 END GET_KPI_MGR_TOTALS;
500
501
502
503
504
505 -- ----------------------------------------------------
506 -- This procedure calculates the previous period Budgeted and actual Headcount values for
507 -- the Report Headcount Distribution By Organization.
508 -- ----------------------------------------------------
509
510 FUNCTION CALC_PREV_VALUE(p_supervisor_id NUMBER,
511 p_organization_id NUMBER,
512 p_effective_start_date DATE,
513 p_effective_end_date DATE,
514 p_type VARCHAR2)
515 RETURN NUMBER IS
516
517 CURSOR Budget IS
518 SELECT SUM(BUDGETED) BUDGETED
519 FROM
520 (SELECT POSITION_ID,
521 NVL(CASE WHEN BUDGET_AGGREGATE = 'ACCUMULATE' THEN SUM(HEADCOUNT_VALUE)
522 WHEN BUDGET_AGGREGATE = 'AVERAGE' THEN AVG(HEADCOUNT_VALUE)
523 WHEN BUDGET_AGGREGATE = 'MAXIMUM' THEN MAX(HEADCOUNT_VALUE)
524 ELSE SUM(HEADCOUNT_VALUE)
525 END,0) BUDGETED
526 FROM HRI_MDP_BDGTS_HDCNT_ORGMGR_CT
527 WHERE ORGMGR_ID = p_supervisor_id
528 AND EFFECTIVE_START_DATE <= p_effective_end_date
529 AND EFFECTIVE_END_DATE >= p_effective_start_date
530 AND ORGANIZATION_ID = p_organization_id
531 GROUP BY POSITION_ID,
532 BUDGET_AGGREGATE );
533
534 CURSOR actls IS
535 SELECT NVL(TOTAL_HEADCOUNT,0) actual
536 FROM HRI_MDP_WRKFC_ORGMGR_MV
537 WHERE ORGMGR_ID = p_supervisor_id
538 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
539 AND ORGANIZATION_ID = p_organization_id ;
540
541 l_budgeted_amount number;
542 l_actual_amount number;
543 l_available number := 0;
544
545 BEGIN
546
547 OPEN Budget;
548 FETCH Budget INTO l_budgeted_amount;
549 CLOSE Budget;
550
551 OPEN actls;
552 FETCH actls into l_actual_amount;
553 CLOSE actls;
554
555 l_available := l_budgeted_amount - l_actual_amount;
556
557 IF (p_type = 'AVAIL') THEN
558 return l_available;
559 ELSIF (p_type = 'ACTUAL') THEN
560 return l_actual_amount;
561 ELSIF (p_type = 'BDGT') THEN
562 return l_budgeted_amount;
563 ELSE
564 return 0;
565 END IF;
566
567 END CALC_PREV_VALUE;
568
569 END HRI_OLTP_PMV_HDCNT_ORGMGR;
570