[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_LBRCST_ORGMGR
Source
1 PACKAGE BODY HRI_OLTP_PMV_LBRCST_ORGMGR AS
2 /* $Header: hrirplom.pkb 120.15 2006/03/14 20:12:44 rlpatil noship $ */
3
4 l_currency VARCHAR2(10);
5 l_rateType VARCHAR2(10);
6
7
8 -- ----------------------------------------------------
9 -- This procedure frames the Query for the Report Labor Cost Distribution By Organization.
10 -- ----------------------------------------------------
11
12 PROCEDURE GET_ORG_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
13 x_custom_sql OUT NOCOPY VARCHAR2,
14 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
15
16 l_sqltext VARCHAR2(32767);
17 l_custom_rec BIS_QUERY_ATTRIBUTES;
18
19 /* Parameter values */
20 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
21 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
22
23
24 BEGIN
25
26
27 /* Initialize out parameters */
28
29 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
30 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
31
32 /* Get common parameter values */
33 hri_oltp_pmv_util_param.get_parameters_from_table
34 (p_page_parameter_tbl => p_page_parameter_tbl,
35 p_parameter_rec => l_parameter_rec,
36 p_bind_tab => l_bind_tab);
37
38
39 l_currency := l_parameter_rec.currency_code;
40
41 IF l_currency =bis_common_parameters.get_currency_code THEN
42 l_rateType:=bis_common_parameters.get_rate_type;
43 ELSIF l_currency =bis_common_parameters.get_secondary_currency_code THEN
44 l_rateType:=bis_common_parameters.get_secondary_rate_type;
45 END IF;
46
47
48
49 /* Build query */
50
51 l_sqltext :=
52 'SELECT -- Labor Cost Distribution By Organization
53 ID HRI_P_CHAR2_GA
54 ,name HRI_P_CHAR1_GA
55 ,budgeted_amount HRI_P_MEASURE1
56 ,committed_amount HRI_P_MEASURE2
57 ,actual_amount HRI_P_MEASURE3
58 ,total HRI_P_MEASURE4
59 ,total HRI_P_MEASURE7
60 ,available HRI_P_MEASURE5
61 ,((available - prev_available)*100/decode(prev_available,0,1,prev_available)) HRI_P_MEASURE6
62 ,SUM(budgeted_amount) OVER () HRI_P_GRAND_TOTAL1
63 ,SUM(committed_amount) OVER () HRI_P_GRAND_TOTAL2
64 ,SUM(actual_amount) OVER () HRI_P_GRAND_TOTAL3
65 ,SUM(total) OVER () HRI_P_GRAND_TOTAL4
66 ,SUM(available) OVER () HRI_P_GRAND_TOTAL5
67 ,((SUM(available) OVER() - SUM(prev_available) OVER())*100/decode(SUM(prev_available) OVER(),0,1,SUM(prev_available) OVER())) HRI_P_GRAND_TOTAL6
68 ,1 HRI_P_ORDER_BY_1
69 FROM
70 (
71 SELECT
72 ORGANIZATION_ID id
73 ,hr_general.decode_organization(ORGANIZATION_ID) name
74 ,NVL(SUM(budgeted_amount), 0) budgeted_amount
75 ,SUM(actual_amount) actual_amount
76 ,SUM(committed_amount) committed_amount
77 ,SUM(actual_amount) + SUM(committed_amount) total
78 ,SUM(budgeted_amount) - NVL((SUM(actual_amount) + SUM(committed_amount)),0) available
79 ,SUM(prev_available) prev_available
80 FROM
81 (
82 (SELECT ORGANIZATION_ID,
83 null BUDGETED_AMOUNT,
84 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
85 (CURRENCY_CODE,
86 '''||l_currency||''',
87 &BIS_CURRENT_ASOF_DATE,
88 SUM(ACTUAL_VALUE),
89 '''||l_rateType||''') ACTUAL_AMOUNT,
90 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
91 (CURRENCY_CODE,
92 '''||l_currency||''',
93 &BIS_CURRENT_ASOF_DATE,
94 SUM(COMMITMENT_VALUE),
95 '''||l_rateType||''') COMMITTED_AMOUNT,
96 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
97 (CURRENCY_CODE,
98 '''||l_currency||''',
99 &BIS_CURRENT_ASOF_DATE,
100 HRI_OLTP_PMV_LBRCST_ORGMGR.CALC_PREV_VALUE_ORG
101 (&HRI_PERSON+HRI_PER_USRDR_H
102 ,ORGANIZATION_ID
103 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
104 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
105 ,''AVAIL''
106 ),
107 '''||l_rateType||''') PREV_AVAILABLE
108 FROM HRI_MDP_CMNTS_ACTLS_ORG_MV
109 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
110 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
111 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
112 GROUP BY ORGANIZATION_ID,
113 CURRENCY_CODE )
114 UNION ALL
115 (SELECT ORGANIZATION_ID,
116 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
117 (CURRENCY_CODE,
118 '''||l_currency||''',
119 &BIS_CURRENT_ASOF_DATE,
120 SUM(BUDGET_VALUE),
121 '''||l_rateType||''') BUDGETED_AMOUNT,
122 null ACTUAL_AMOUNT,
123 null COMMITTED_AMOUNT,
124 null PREV_AVAILABLE
125 FROM HRI_MDP_BDGTS_LBRCST_ORG_MV
126 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
127 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
128 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
129 GROUP BY ORGANIZATION_ID,
130 CURRENCY_CODE )
131 )
132 GROUP BY ORGANIZATION_ID
133 )
134 &ORDER_BY_CLAUSE';
135
136
137 x_custom_sql := l_SQLText;
138
139
140
141 END GET_ORG_SQL;
142
143
144 -- ----------------------------------------------------
145 -- This procedure calculates the previous period Budgeted, Commitment and actual Labor Cost values for
146 -- the Report Labor Cost Distribution By Organization.
147 -- ----------------------------------------------------
148
149 FUNCTION CALC_PREV_VALUE_ORG(p_ORGMGR_ID NUMBER,
150 p_organization_id NUMBER,
151 p_effective_start_date DATE,
152 p_effective_end_date DATE,
153 p_type VARCHAR2)
154 RETURN NUMBER IS
155
156 CURSOR prev_tot_budget IS
157 SELECT SUM(bdg.BUDGET_VALUE) budgeted_amount
158 FROM HRI_MDP_BDGTS_LBRCST_ORG_MV bdg
159 WHERE bdg.ORGMGR_ID = p_ORGMGR_ID
160 AND bdg.EFFECTIVE_START_DATE <= p_effective_end_date
161 AND bdg.EFFECTIVE_END_DATE >= p_effective_start_date
162 AND bdg.organization_id = p_organization_id ;
163
164 CURSOR prev_tot_actual_cmmt IS
165 SELECT SUM(act.ACTUAL_VALUE),
166 SUM(act.COMMITMENT_VALUE)
167 FROM HRI_MDP_CMNTS_ACTLS_ORG_MV act
168 WHERE act.ORGMGR_ID = p_ORGMGR_ID
169 AND act.EFFECTIVE_START_DATE <= p_effective_end_date
170 AND act.EFFECTIVE_END_DATE >= p_effective_start_date
171 AND act.organization_id = p_organization_id;
172
173 l_budgeted_amount number;
174 l_actual_amount number;
175 l_committed_amount number;
176 l_available number := 0;
177
178 BEGIN
179 OPEN prev_tot_budget;
180 FETCH prev_tot_budget INTO l_budgeted_amount;
181 CLOSE prev_tot_budget;
182
183 OPEN prev_tot_actual_cmmt;
184 FETCH prev_tot_actual_cmmt into l_actual_amount,l_committed_amount;
185 CLOSE prev_tot_actual_cmmt;
186
187 l_available := nvl(l_budgeted_amount,0) - (nvl(l_actual_amount,0) + nvl(l_committed_amount,0));
188
189 IF (p_type = 'AVAIL') THEN
190 return l_available;
191 ELSIF (p_type = 'ACTUAL') THEN
192 return l_actual_amount;
193 ELSIF (p_type = 'CMMT') THEN
194 return l_committed_amount;
195 ELSIF (p_type = 'BDGT') THEN
196 return l_budgeted_amount;
197 ELSE
198 return 0;
199 END IF;
200
201 END CALC_PREV_VALUE_ORG;
202
203
204 -- ----------------------------------------------------
205 -- This procedure frames the Query for Labor Cost KPI.
206 -- ----------------------------------------------------
207
208 PROCEDURE GET_KPI_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
209 x_custom_sql OUT NOCOPY VARCHAR2,
210 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
211
212 l_sqltext VARCHAR2(32767);
213 l_custom_rec BIS_QUERY_ATTRIBUTES;
214
215 /* Parameter values */
216 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
217 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
218
219
220 BEGIN
221
222
223 /* Initialize out parameters */
224
225 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
226 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
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 l_currency:= l_parameter_rec.currency_code;
235
236 IF l_currency =bis_common_parameters.get_currency_code THEN
237 l_rateType:=bis_common_parameters.get_rate_type;
238 ELSIF l_currency =bis_common_parameters.get_secondary_currency_code THEN
239 l_rateType:=bis_common_parameters.get_secondary_rate_type;
240 END IF;
241
242
243 /* Build query */
244
245 l_sqltext :=
246
247 'SELECT -- Labor Cost KPI
248 ID VIEWBYID
249 ,value VIEWBY
250 ,c1 HRI_P_MEASURE1
251 ,c2 HRI_P_MEASURE2
252 ,c3 HRI_P_MEASURE3
253 ,c4 HRI_P_MEASURE4
254 ,c5 HRI_P_MEASURE5
255 ,c6 HRI_P_MEASURE6
256 ,c7 HRI_P_MEASURE7
257 ,c8 HRI_P_MEASURE8
258 ,c9 HRI_P_MEASURE9
259 ,sum(c2) over() HRI_P_GRAND_TOTAL1
260 ,sum(c7) over() HRI_P_GRAND_TOTAL2
261 ,SUM(c1) over() HRI_P_GRAND_TOTAL3
262 ,sum(c6) over() HRI_P_GRAND_TOTAL4
263 ,sum(c3) over() HRI_P_GRAND_TOTAL5
264 ,sum(c8) over() HRI_P_GRAND_TOTAL6
265 ,SUM(c5) over() HRI_P_GRAND_TOTAL7
266 ,sum(c9) over() HRI_P_GRAND_TOTAL8
267 FROM
268 (
269 SELECT tab.ORGMGR_ID ID
270 ,per.value
271 ,tab.budgeted_amount c1
272 ,tab.actual_amount c2
273 ,tab.committed_amount c3
274 ,(tab.actual_amount + tab.committed_amount) c4
275 ,tab.budgeted_amount - (tab.actual_amount + tab.committed_amount) c5
276 ,tab.prev_budgeted c6
277 ,tab.prev_actual c7
278 ,tab.prev_commited c8
279 ,tab.prev_budgeted - (tab.prev_actual + tab.prev_commited ) c9
280 FROM
281 (
282 (
283 SELECT ORGMGR_ID,
284 SUM(ACTUAL_AMOUNT) ACTUAL_AMOUNT,
285 SUM(COMMITTED_AMOUNT) COMMITTED_AMOUNT ,
286 SUM(PREV_ACTUAL) PREV_ACTUAL,
287 SUM(PREV_COMMITED) PREV_COMMITED,
288 SUM(BUDGETED_AMOUNT) BUDGETED_AMOUNT,
289 SUM(PREV_BUDGETED) PREV_BUDGETED
290 FROM
291 (
295 null PREV_BUDGETED,
292 (
293 SELECT ORGMGR_ID,
294 null BUDGETED_AMOUNT,
296 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
297 (CURRENCY_CODE,
298 '''||l_currency||''',
299 &BIS_CURRENT_ASOF_DATE,
300 SUM(ACTUAL_VALUE),
301 '''||l_rateType||''') ACTUAL_AMOUNT,
302 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
303 (CURRENCY_CODE,
304 '''||l_currency||''',
305 &BIS_CURRENT_ASOF_DATE,
306 SUM(COMMITMENT_VALUE),
307 '''||l_rateType||''') COMMITTED_AMOUNT,
308 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
309 (CURRENCY_CODE,
310 '''||l_currency||''',
311 &BIS_CURRENT_ASOF_DATE,
312 HRI_OLTP_PMV_LBRCST_ORGMGR.GET_KPI_MGR_TOTALS
313 (&HRI_PERSON+HRI_PER_USRDR_H
314 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
315 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
316 ,''ACTUAL''
317 ) ,
318 '''||l_rateType||''') PREV_ACTUAL,
319 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
320 (CURRENCY_CODE,
321 '''||l_currency||''',
322 &BIS_CURRENT_ASOF_DATE,
323 HRI_OLTP_PMV_LBRCST_ORGMGR.GET_KPI_MGR_TOTALS
324 (&HRI_PERSON+HRI_PER_USRDR_H
325 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
326 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
327 ,''CMMT''
328 ) ,
329 '''||l_rateType||''') PREV_COMMITED
330 FROM HRI_MDP_CMNTS_ACTLS_ORG_MV
331 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
332 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
333 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
334 AND ORGANIZATION_ID IN (SELECT SUB_ORGANIZATION_ID
335 FROM HRI_CS_SUPH_ORGMGR_CT
336 WHERE SUP_PERSON_ID=&HRI_PERSON+HRI_PER_USRDR_H
337 AND SUB_PERSON_ID = SUP_PERSON_ID
338 AND SUB_RELATIVE_LEVEL = 0
339 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
340 GROUP BY ORGMGR_ID,
341 CURRENCY_CODE )
342 UNION ALL
343 (SELECT ORGMGR_ID,
344 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
345 (CURRENCY_CODE,
346 '''||l_currency||''',
347 &BIS_CURRENT_ASOF_DATE,
348 SUM(BUDGET_VALUE),
349 '''||l_rateType||''') budgeted_amount,
350 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
351 (CURRENCY_CODE,
352 '''||l_currency||''',
353 &BIS_CURRENT_ASOF_DATE,
354 HRI_OLTP_PMV_LBRCST_ORGMGR.GET_KPI_MGR_TOTALS
355 (&HRI_PERSON+HRI_PER_USRDR_H
356 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
357 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
358 ,''BDGT''
359 ),
360 '''||l_rateType||''') prev_budgeted,
361 null ACTUAL_AMOUNT,
362 null COMMITTED_AMOUNT,
363 null PREV_ACTUAL,
364 null PREV_COMMITED
365
366 FROM HRI_MDP_BDGTS_LBRCST_ORG_MV
367 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
368 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
369 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
370 AND ORGANIZATION_ID IN (SELECT SUB_ORGANIZATION_ID
371 FROM HRI_CS_SUPH_ORGMGR_CT
372 WHERE SUP_PERSON_ID=&HRI_PERSON+HRI_PER_USRDR_H
373 AND SUB_PERSON_ID = SUP_PERSON_ID
374 AND SUB_RELATIVE_LEVEL = 0
375 AND &BIS_CURRENT_ASOF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
376
377 GROUP BY ORGMGR_ID,
378 CURRENCY_CODE )
379
380 )
381 GROUP BY ORGMGR_ID
382 )
383 UNION ALL
384 (
385 SELECT ORGMGR_ID,
386 SUM(ACTUAL_AMOUNT) ACTUAL_AMOUNT,
387 SUM(COMMITTED_AMOUNT) COMMITTED_AMOUNT ,
388 SUM(PREV_ACTUAL) PREV_ACTUAL,
389 SUM(PREV_COMMITED) PREV_COMMITED,
390 SUM(BUDGETED_AMOUNT) BUDGETED_AMOUNT,
391 SUM(PREV_BUDGETED) PREV_BUDGETED
392 FROM
393 (
394 (
395 SELECT ORGMGR_ID,
396 null BUDGETED_AMOUNT,
397 null PREV_BUDGETED,
398 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
399 (CURRENCY_CODE,
400 '''||l_currency||''',
401 &BIS_CURRENT_ASOF_DATE,
402 SUM(ACTUAL_VALUE),
403 '''||l_rateType||''') ACTUAL_AMOUNT,
404 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
405 (CURRENCY_CODE,
406 '''||l_currency||''',
407 &BIS_CURRENT_ASOF_DATE,
408 SUM(COMMITMENT_VALUE),
409 '''||l_rateType||''') COMMITTED_AMOUNT,
410 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
411 (CURRENCY_CODE,
412 '''||l_currency||''',
413 &BIS_CURRENT_ASOF_DATE,
417 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
414 HRI_OLTP_PMV_LBRCST_ORGMGR.GET_KPI_TOTALS
415 (&HRI_PERSON+HRI_PER_USRDR_H
416 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
418 ,''ACTUAL''
419 ) ,
420 '''||l_rateType||''') PREV_ACTUAL,
421 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
422 (CURRENCY_CODE,
423 '''||l_currency||''',
424 &BIS_CURRENT_ASOF_DATE,
425 HRI_OLTP_PMV_LBRCST_ORGMGR.GET_KPI_TOTALS
426 (&HRI_PERSON+HRI_PER_USRDR_H
427 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
428 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
429 ,''CMMT''
430 ) ,
431 '''||l_rateType||''') PREV_COMMITED
432 FROM HRI_MDP_CMNTS_ACTLS_MV
433 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)
434 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
435 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
436 GROUP BY ORGMGR_ID,
437 CURRENCY_CODE )
438 UNION ALL
439 (SELECT ORGMGR_ID,
440 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
441 (CURRENCY_CODE,
442 '''||l_currency||''',
443 &BIS_CURRENT_ASOF_DATE,
444 SUM(BUDGET_VALUE),
445 '''||l_rateType||''') BUDGETED_AMOUNT,
446 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
447 (CURRENCY_CODE,
448 '''||l_currency||''',
449 &BIS_CURRENT_ASOF_DATE,
450 HRI_OLTP_PMV_LBRCST_ORGMGR.GET_KPI_TOTALS
451 (&HRI_PERSON+HRI_PER_USRDR_H
452 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
453 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
454 ,''BDGT''
455 ),
456 '''||l_rateType||''') PREV_BUDGETED,
457 null ACTUAL_AMOUNT,
458 null COMMITTED_AMOUNT,
459 null PREV_ACTUAL,
460 null PREV_COMMITED
461 FROM HRI_MDP_BDGTS_LBRCST_MV
462 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)
463 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
464 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
465 GROUP BY ORGMGR_ID,
466 CURRENCY_CODE )
467 )
468 GROUP BY ORGMGR_ID
469 )
470 ) tab,
471 HRI_DBI_CL_PER_N_V per
472 WHERE tab.orgmgr_id = per.ID
473 AND &BIS_CURRENT_ASOF_DATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE
474 )';
475
476
477 x_custom_sql := l_SQLText;
478
479 END GET_KPI_SQL;
480
481
482 -- ----------------------------------------------------
483 -- This procedure calculates the Budgeted, Commitment and actual Labor Cost Grand Totals for
484 -- the Labor Cost KPI.
485 -- ----------------------------------------------------
486
487 FUNCTION GET_KPI_TOTALS(p_ORGMGR_ID NUMBER,
488 p_effective_start_date DATE,
489 p_effective_end_date DATE,
490 p_type VARCHAR2
491 )
492 RETURN NUMBER IS
493
494 CURSOR Budget IS
495 SELECT SUM(bdg.BUDGET_VALUE) budgeted_amount
496 FROM HRI_MDP_BDGTS_LBRCST_MV bdg
497 WHERE bdg.ORGMGR_ID = p_ORGMGR_ID
498 AND bdg.EFFECTIVE_START_DATE <= p_effective_end_date
499 AND bdg.EFFECTIVE_END_DATE >= p_effective_start_date;
500
501 CURSOR actls_cmnts IS
502 SELECT SUM(act.ACTUAL_VALUE),
503 SUM(act.COMMITMENT_VALUE)
504 FROM HRI_MDP_CMNTS_ACTLS_MV act
505 WHERE act.ORGMGR_ID = p_ORGMGR_ID
506 AND act.EFFECTIVE_START_DATE <= p_effective_end_date
507 AND act.EFFECTIVE_END_DATE >= p_effective_start_date;
508
509 l_budgeted_amount number;
510 l_actual_amount number;
511 l_committed_amount number;
512 l_available number := 0;
513
514 BEGIN
515
516 OPEN Budget;
517 FETCH Budget INTO l_budgeted_amount;
518 CLOSE Budget;
519
520 OPEN actls_cmnts;
521 FETCH actls_cmnts into l_actual_amount,l_committed_amount;
522 CLOSE actls_cmnts;
523
524 l_available := nvl(l_budgeted_amount,0) - (nvl(l_actual_amount,0) + nvl(l_committed_amount,0));
525
526 IF (p_type = 'AVAIL') THEN
527 return l_available;
528 ELSIF (p_type = 'ACTUAL') THEN
529 return l_actual_amount;
530 ELSIF (p_type = 'CMMT') THEN
531 return l_committed_amount;
532 ELSIF (p_type = 'BDGT') THEN
533 return l_budgeted_amount;
534 ELSE
535 return 0;
536 END IF;
537
538 END GET_KPI_TOTALS;
539
540
541 -- ----------------------------------------------------
542 -- This procedure calculates the Budgeted, Commitment and actual Labor Cost Grand Totals of
543 -- the organizations directly owned by the Manager for the Labor Cost KPI.
544 -- ----------------------------------------------------
545
546 FUNCTION GET_KPI_MGR_TOTALS(p_ORGMGR_ID NUMBER,
550 )
547 p_effective_start_date DATE,
548 p_effective_end_date DATE,
549 p_type VARCHAR2
551 RETURN NUMBER IS
552
553 CURSOR Budget IS
554 SELECT SUM(bdg.BUDGET_VALUE) budgeted_amount
555 FROM HRI_MDP_BDGTS_LBRCST_ORG_MV bdg
556 WHERE bdg.ORGMGR_ID = p_ORGMGR_ID
557 AND bdg.EFFECTIVE_START_DATE <= p_effective_end_date
558 AND bdg.EFFECTIVE_END_DATE >= p_effective_start_date
559 AND ORGANIZATION_ID IN (SELECT SUB_ORGANIZATION_ID
560 FROM HRI_CS_SUPH_ORGMGR_CT
561 WHERE SUP_PERSON_ID=p_ORGMGR_ID
562 AND SUB_PERSON_ID = SUP_PERSON_ID
563 AND SUB_RELATIVE_LEVEL = 0
564 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE);
565
566
567 CURSOR actls_cmnts IS
568 SELECT SUM(act.ACTUAL_VALUE),
569 SUM(act.COMMITMENT_VALUE)
570 FROM HRI_MDP_CMNTS_ACTLS_ORG_MV act
571 WHERE act.ORGMGR_ID = p_ORGMGR_ID
572 AND act.EFFECTIVE_START_DATE <= p_effective_end_date
573 AND act.EFFECTIVE_END_DATE >= p_effective_start_date
574 AND ORGANIZATION_ID IN (SELECT SUB_ORGANIZATION_ID
575 FROM HRI_CS_SUPH_ORGMGR_CT
576 WHERE SUP_PERSON_ID=p_ORGMGR_ID
577 AND SUB_PERSON_ID = SUP_PERSON_ID
578 AND SUB_RELATIVE_LEVEL = 0
579 AND p_effective_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE);
580 l_budgeted_amount number;
581 l_actual_amount number;
582 l_committed_amount number;
583 l_available number := 0;
584
585 BEGIN
586
587 OPEN Budget;
588 FETCH Budget INTO l_budgeted_amount;
589 CLOSE Budget;
590
591 OPEN actls_cmnts;
592 FETCH actls_cmnts into l_actual_amount,l_committed_amount;
593 CLOSE actls_cmnts;
594
595 l_available := nvl(l_budgeted_amount,0) - (nvl(l_actual_amount,0) + nvl(l_committed_amount,0));
596
597 IF (p_type = 'AVAIL') THEN
598 return l_available;
599 ELSIF (p_type = 'ACTUAL') THEN
600 return l_actual_amount;
601 ELSIF (p_type = 'CMMT') THEN
602 return l_committed_amount;
603 ELSIF (p_type = 'BDGT') THEN
604 return l_budgeted_amount;
605 ELSE
606 return 0;
607 END IF;
608
609 END GET_KPI_MGR_TOTALS;
610
611
612 -- ----------------------------------------------------
613 -- This procedure calculates the previous period Budgeted, Commitment and actual Labor Cost values for
614 -- the Report Labor Cost Distribution By Position.
615 -- ----------------------------------------------------
616
617 FUNCTION CALC_PREV_VALUE_POS(p_ORGMGR_ID NUMBER,
618 p_organization_id NUMBER,
619 p_effective_start_date DATE,
620 p_effective_end_date DATE,
621 p_position_id NUMBER )
622 RETURN NUMBER IS
623
624 CURSOR prev_tot_budget IS
625 SELECT SUM(bdg.BUDGET_VALUE) budgeted_amount
626 FROM HRI_MDP_BDGTS_LBRCST_ORGMGR_CT bdg
627 WHERE bdg.ORGMGR_ID = p_ORGMGR_ID
628 AND bdg.organization_id = decode(p_organization_id,0,bdg.ORGANIZATION_ID,p_organization_id)
629 AND bdg.position_id = p_position_id
630 AND bdg.EFFECTIVE_START_DATE <= p_effective_end_date
631 AND bdg.EFFECTIVE_END_DATE >= p_effective_start_date;
632
633 CURSOR prev_tot_actual_cmmt IS
634 SELECT SUM(act.ACTUAL_VALUE),
635 SUM(act.COMMITMENT_VALUE)
636 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT act
637 WHERE act.ORGMGR_ID = p_ORGMGR_ID
638 AND act.organization_id = decode(p_organization_id,0,act.ORGANIZATION_ID,p_organization_id)
639 AND act.position_id = p_position_id
640 AND act.EFFECTIVE_START_DATE <= p_effective_end_date
641 AND act.EFFECTIVE_END_DATE >= p_effective_start_date;
642
643 l_budgeted_amount number;
644 l_actual_amount number;
645 l_committed_amount number;
646 l_available number := 0;
647
648 BEGIN
649 OPEN prev_tot_budget;
650 FETCH prev_tot_budget INTO l_budgeted_amount;
651 CLOSE prev_tot_budget;
652
653 OPEN prev_tot_actual_cmmt;
654 FETCH prev_tot_actual_cmmt INTO l_actual_amount,l_committed_amount;
655 CLOSE prev_tot_actual_cmmt;
656
657
658 l_available := nvl(l_budgeted_amount,0) - (nvl(l_actual_amount,0) + nvl(l_committed_amount,0));
659
660 RETURN l_available;
661
662 END CALC_PREV_VALUE_POS;
663
664
665 -- ----------------------------------------------------
666 -- This procedure frames the Query for the Report Labor Cost Distribution By Position.
667 -- ----------------------------------------------------
668
669 PROCEDURE GET_POS_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
670 x_custom_sql OUT NOCOPY VARCHAR2,
671 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
672
673 l_sqltext VARCHAR2(32767);
674 l_custom_rec BIS_QUERY_ATTRIBUTES;
675
676 /* Parameter values */
677 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
678 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
679
680
681 BEGIN
682
683 /* Initialize out parameters */
684
685 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
686 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
687
688 /* Get common parameter values */
689 /* hri_oltp_pmv_util_param.get_parameters_from_table
690 (p_page_parameter_tbl => p_page_parameter_tbl,
691 p_parameter_rec => l_parameter_rec,
695 (p_page_parameter_tbl => p_page_parameter_tbl,
692 p_bind_tab => l_bind_tab); */
693
694 hri_oltp_pmv_util_param.get_parameters_from_table
696 p_parameter_rec => l_parameter_rec,
697 p_bind_tab => l_bind_tab);
698
699 l_currency:= l_parameter_rec.currency_code;
700
701 IF l_currency =bis_common_parameters.get_currency_code THEN
702 l_rateType:=bis_common_parameters.get_rate_type;
703 ELSIF l_currency =bis_common_parameters.get_secondary_currency_code THEN
704 l_rateType:=bis_common_parameters.get_secondary_rate_type;
705 END IF;
706
707 l_sqltext :=
708 'SELECT -- Labor Cost Distribution By Position
709 pos.name HRI_P_CHAR1_GA
710 ,tab.budgeted_amount HRI_P_MEASURE1
711 ,tab.committed_amount HRI_P_MEASURE2
712 ,tab.actual_amount HRI_P_MEASURE3
713 ,tab.total HRI_P_MEASURE4
714 ,tab.total HRI_P_MEASURE7
715 ,tab.available HRI_P_MEASURE5
716 ,(tab.available - tab.prev_available)*100/decode(tab.prev_available,0,1,tab.prev_available) HRI_P_MEASURE6
717 ,SUM(tab.budgeted_amount) OVER () HRI_P_GRAND_TOTAL1
718 ,SUM(tab.committed_amount) OVER () HRI_P_GRAND_TOTAL2
719 ,SUM(tab.actual_amount) OVER () HRI_P_GRAND_TOTAL3
720 ,SUM(tab.total) OVER () HRI_P_GRAND_TOTAL4
721 ,SUM(tab.available) OVER () HRI_P_GRAND_TOTAL5
722 ,SUM((tab.available - tab.prev_available)*100/decode(tab.prev_available,0,1,tab.prev_available) ) OVER () HRI_P_GRAND_TOTAL6
723 ,tab.id HRI_P_CHAR3_GA
724 ,1 HRI_P_ORDER_BY_1
725 FROM
726 (
727 SELECT
728 POSITION_ID id
729 ,SUM(budgeted_amount) budgeted_amount
730 ,SUM(actual_amount) actual_amount
731 ,SUM(committed_amount) committed_amount
732 ,SUM(actual_amount) + SUM(committed_amount) total
733 ,SUM(budgeted_amount) - NVL((SUM(actual_amount) + SUM(committed_amount)),0) available
734 ,SUM(prev_available) prev_available
735 FROM
736 (
737 (SELECT POSITION_ID,
738 null BUDGETED_AMOUNT,
739 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
740 (CURRENCY_CODE,
741 '''||l_currency||''',
742 &BIS_CURRENT_ASOF_DATE,
743 SUM(ACTUAL_VALUE),
744 '''||l_rateType||''') ACTUAL_AMOUNT,
745 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
746 (CURRENCY_CODE,
747 '''||l_currency||''',
748 &BIS_CURRENT_ASOF_DATE,
749 SUM(COMMITMENT_VALUE),
750 '''||l_rateType||''') COMMITTED_AMOUNT,
751 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
752 (CURRENCY_CODE,
753 '''||l_currency||''',
754 &BIS_CURRENT_ASOF_DATE,
755 HRI_OLTP_PMV_LBRCST_ORGMGR.CALC_PREV_VALUE_POS
756 (&HRI_PERSON+HRI_PER_USRDR_H
757 ,&HRI_P_CHAR2_GA
758 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
759 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
760 ,POSITION_ID
761 ),
762 '''||l_rateType||''') PREV_AVAILABLE
763 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT
764 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
765 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
766 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
767 AND ORGANIZATION_ID = decode(&HRI_P_CHAR2_GA,0,ORGANIZATION_ID,&HRI_P_CHAR2_GA)
768 GROUP BY POSITION_ID,
769 CURRENCY_CODE )
770 UNION ALL
771 (SELECT POSITION_ID,
772 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
773 (CURRENCY_CODE,
774 '''||l_currency||''',
775 &BIS_CURRENT_ASOF_DATE,
776 SUM(BUDGET_VALUE),
777 '''||l_rateType||''') BUDGETED_AMOUNT,
778 null ACTUAL_AMOUNT,
779 null COMMITTED_AMOUNT,
780 null PREV_AVAILABLE
781 FROM HRI_MDP_BDGTS_LBRCST_ORGMGR_CT
782 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
783 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
784 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
785 AND ORGANIZATION_ID = decode(&HRI_P_CHAR2_GA,0,ORGANIZATION_ID,&HRI_P_CHAR2_GA)
786 GROUP BY POSITION_ID,
787 CURRENCY_CODE )
788 )
789 GROUP BY POSITION_ID
790 )tab,
791 HR_ALL_POSITIONS_F pos
792 WHERE pos.POSITION_ID = tab.ID
793 AND &BIS_CURRENT_ASOF_DATE BETWEEN pos.EFFECTIVE_START_DATE AND pos.EFFECTIVE_END_DATE
794 &ORDER_BY_CLAUSE';
795
796 x_custom_sql := l_SQLText;
797
798 END GET_POS_SQL;
799
800
801 -- ----------------------------------------------------
802 -- This procedure frames the Query for the Report Position Occupancy Report.
803 -- ----------------------------------------------------
804
805 PROCEDURE GET_POS_DTL_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
809 l_lnk_profile_chk NUMBER;
806 x_custom_sql OUT NOCOPY VARCHAR2,
807 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
808
810 l_lnk_emp_name VARCHAR2(255);
811 l_sqltext VARCHAR2(32767);
812 l_custom_rec BIS_QUERY_ATTRIBUTES;
813
814 /* Parameter values */
815 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
816 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
817
818 BEGIN
819
820 /* Initialize out parameters */
821
822 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
823 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
824
825
826 /* Get common parameter values */
827 hri_oltp_pmv_util_param.get_parameters_from_table
828 (p_page_parameter_tbl => p_page_parameter_tbl,
829 p_parameter_rec => l_parameter_rec,
830 p_bind_tab => l_bind_tab);
831
832 l_currency:= l_parameter_rec.currency_code;
833
834 IF l_currency =bis_common_parameters.get_currency_code THEN
835 l_rateType:=bis_common_parameters.get_rate_type;
836 ELSIF l_currency =bis_common_parameters.get_secondary_currency_code THEN
837 l_rateType:=bis_common_parameters.get_secondary_rate_type;
838 END IF;
839
840 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
841 ,p_bind_tab => l_bind_tab);
842
843 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE) ) THEN
844 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_CHAR9_GA&OAPB=FII_HR_BRAND_TEXT';
845 ELSE
846 l_lnk_emp_name := '';
847 END IF ;
848
849 /* Build query */
850 l_sqltext :=
851 'SELECT -- Position Occupancy Report
852 a.value HRI_P_CHAR1_GA
853 ,a.organization HRI_P_MEASURE1
854 ,a.job HRI_P_MEASURE2
855 ,a.position HRI_P_MEASURE3
856 ,a.grade HRI_P_MEASURE4
857 ,a.committed_amount HRI_P_MEASURE5
858 ,a.actual_amount HRI_P_MEASURE6
859 ,a.actual_amount + a.committed_amount HRI_P_MEASURE7
860 ,SUM(a.committed_amount) OVER () HRI_P_GRAND_TOTAL1
861 ,SUM(a.actual_amount) OVER () HRI_P_GRAND_TOTAL2
862 ,SUM(a.actual_amount + a.committed_amount) OVER () HRI_P_GRAND_TOTAL3
863 ,a.id HRI_P_CHAR9_GA
864 ,'''||l_lnk_emp_name||''' HRI_P_DRILL_URL1
865 ,a.order_by HRI_P_ORDER_BY_1
866 FROM
867 (SELECT
868 paf.value
869 ,paf.id
870 ,1 order_by
871 ,hr_general.decode_organization(act.organization_id) organization
872 ,pos.name position
873 , HR_GENERAL.DECODE_JOB(act.job_id) job
874 , HR_GENERAL.DECODE_GRADE(act.grade_id) grade
875 , HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
876 (act.CURRENCY_CODE,
877 '''||l_currency||''',
878 &BIS_CURRENT_ASOF_DATE,
879 SUM(act.ACTUAL_VALUE),
880 '''||l_rateType||''') actual_amount
881 ,HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
882 (act.CURRENCY_CODE,
883 '''||l_currency||''',
884 &BIS_CURRENT_ASOF_DATE,
885 SUM(act.COMMITMENT_VALUE),
886 '''||l_rateType||''') committed_amount
887 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT act,
888 PER_ALL_ASSIGNMENTS_F paa,
889 HRI_CL_PER_V paf,
890 HR_ALL_POSITIONS_F pos
891 WHERE act.ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
892 AND act.EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
893 AND act.EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
894 AND act.ORGANIZATION_ID = decode(&HRI_P_CHAR2_GA,0,act.ORGANIZATION_ID,&HRI_P_CHAR2_GA)
895 AND act.POSITION_ID = decode(&HRI_P_CHAR3_GA,0,act.POSITION_ID,&HRI_P_CHAR3_GA)
896 AND paf.ID = paa.PERSON_ID
897 AND paa.ASSIGNMENT_ID = act.ASSIGNMENT_ID
898 AND pos.POSITION_ID = act.POSITION_ID
899 AND &BIS_CURRENT_ASOF_DATE BETWEEN pos.EFFECTIVE_START_DATE AND pos.EFFECTIVE_END_DATE
900 AND &BIS_CURRENT_ASOF_DATE BETWEEN paa.EFFECTIVE_START_DATE AND paa.EFFECTIVE_END_DATE
901 AND &BIS_CURRENT_ASOF_DATE BETWEEN paf.EFFECTIVE_START_DATE AND paf.EFFECTIVE_END_DATE
902 GROUP BY act.ASSIGNMENT_ID,
903 paf.value,
904 paf.id,
905 pos.name,
906 act.CURRENCY_CODE,
907 act.ORGANIZATION_ID,
908 act.JOB_ID,
909 act.GRADE_ID,
910 1
911 ) a
912 WHERE 1=1 &ORDER_BY_CLAUSE';
913
914 x_custom_sql := l_SQLText;
915
916 END GET_POS_DTL_SQL;
917
918
919 -- ----------------------------------------------------
920 -- This procedure calculates the previous period Budgeted, Commitment and actual Labor Cost values for
921 -- the Report Labor Cost Distribution By Element.
922 -- ----------------------------------------------------
923
924 FUNCTION CALC_PREV_VALUE_ELE(p_ORGMGR_ID NUMBER,
925 p_organization_id NUMBER,
926 p_position_id NUMBER,
927 p_effective_start_date DATE,
928 p_effective_end_date DATE,
932 CURSOR prev_tot_budget IS
929 p_element_type_id NUMBER)
930 RETURN NUMBER IS
931
933 SELECT SUM(bdg.BUDGET_VALUE) budgeted_amount
934 FROM HRI_MDP_BDGTS_LBRCST_ORGMGR_CT bdg
935 WHERE bdg.ORGMGR_ID = p_ORGMGR_ID
936 AND bdg.ORGANIZATION_ID = decode(p_organization_id,0,bdg.ORGANIZATION_ID,p_organization_id)
937 AND bdg.POSITION_ID = decode(p_position_id,0,bdg.POSITION_ID,p_position_id)
938 AND bdg.ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
939 AND bdg.EFFECTIVE_START_DATE <= p_effective_end_date
940 AND bdg.EFFECTIVE_END_DATE >= p_effective_start_date;
941
942
943 CURSOR prev_tot_actual_cmmt IS
944 SELECT SUM(act.ACTUAL_VALUE),
945 SUM(act.COMMITMENT_VALUE)
946 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT act
947 WHERE act.ORGMGR_ID = p_ORGMGR_ID
948 AND act.ORGANIZATION_ID = decode(p_organization_id,0,act.ORGANIZATION_ID,p_organization_id)
949 AND act.POSITION_ID = decode(p_position_id,0,act.POSITION_ID,p_position_id)
950 AND act.ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
951 AND act.EFFECTIVE_START_DATE <= p_effective_end_date
952 AND act.EFFECTIVE_END_DATE >= p_effective_start_date;
953
954 l_budgeted_amount number;
955 l_actual_amount number;
956 l_committed_amount number;
957 l_available number := 0;
958
959 BEGIN
960 OPEN prev_tot_budget;
961 FETCH prev_tot_budget INTO l_budgeted_amount;
962 CLOSE prev_tot_budget;
963
964 OPEN prev_tot_actual_cmmt;
965 FETCH prev_tot_actual_cmmt INTO l_actual_amount,l_committed_amount;
966 CLOSE prev_tot_actual_cmmt;
967
968
969 l_available := nvl(l_budgeted_amount,0) - (nvl(l_actual_amount,0) + nvl(l_committed_amount,0));
970
971
972 RETURN l_available;
973
974 END CALC_PREV_VALUE_ELE;
975
976
977 -- ----------------------------------------------------
978 -- This procedure frames the Query for the Report Labor Cost Distribution By Element.
979 -- ----------------------------------------------------
980
981 PROCEDURE GET_ELE_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
982 x_custom_sql OUT NOCOPY VARCHAR2,
983 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
984
985 l_sqltext VARCHAR2(32767);
986 l_custom_rec BIS_QUERY_ATTRIBUTES;
987
988 /* Parameter values */
989 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
990 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
991
992 BEGIN
993
994 /* Initialize out parameters */
995
996 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
997 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
998
999 /* Get common parameter values */
1000 hri_oltp_pmv_util_param.get_parameters_from_table
1001 (p_page_parameter_tbl => p_page_parameter_tbl,
1002 p_parameter_rec => l_parameter_rec,
1003 p_bind_tab => l_bind_tab);
1004
1005 l_currency:= l_parameter_rec.currency_code;
1006
1007 IF l_currency =bis_common_parameters.get_currency_code THEN
1008 l_rateType:=bis_common_parameters.get_rate_type;
1009 ELSIF l_currency =bis_common_parameters.get_secondary_currency_code THEN
1010 l_rateType:=bis_common_parameters.get_secondary_rate_type;
1011 END IF;
1012
1013 l_sqltext :=
1014
1015 'SELECT -- Labor Cost Distribution By Element
1016 tab.id HRI_P_CHAR3_GA
1017 ,ele.element_name HRI_P_CHAR1_GA
1018 ,tab.budgeted_amount HRI_P_MEASURE1
1019 ,tab.committed_amount HRI_P_MEASURE2
1020 ,tab.actual_amount HRI_P_MEASURE3
1021 ,tab.total HRI_P_MEASURE4
1022 ,tab.total HRI_P_MEASURE7
1023 ,tab.available HRI_P_MEASURE5
1024 ,((tab.available - tab.prev_available)*100/(decode(tab.prev_available,0,1,tab.prev_available))) HRI_P_MEASURE6
1025 ,SUM(tab.budgeted_amount) OVER () HRI_P_GRAND_TOTAL1
1026 ,SUM(tab.committed_amount) OVER () HRI_P_GRAND_TOTAL2
1027 ,SUM(tab.actual_amount) OVER () HRI_P_GRAND_TOTAL3
1028 ,SUM(tab.total) OVER () HRI_P_GRAND_TOTAL4
1029 ,SUM(tab.available) OVER () HRI_P_GRAND_TOTAL5
1030 ,SUM(((tab.available - tab.prev_available)*100/(decode(tab.prev_available,0,1,tab.prev_available))) ) OVER () HRI_P_GRAND_TOTAL6
1031 ,tab.order_by HRI_P_ORDER_BY_1
1032 FROM
1033 (
1034 SELECT
1035 ELEMENT_TYPE_ID id
1036 ,1 order_by
1037 ,SUM(budgeted_amount) budgeted_amount
1038 ,SUM(actual_amount) actual_amount
1039 ,SUM(committed_amount) committed_amount
1040 ,SUM(actual_amount) + SUM(committed_amount) total
1041 ,SUM(budgeted_amount) - NVL((SUM(actual_amount) + SUM(committed_amount)),0) available
1042 ,SUM(prev_available) prev_available
1043 FROM
1044 (
1045 (SELECT ELEMENT_TYPE_ID,
1046 null BUDGETED_AMOUNT,
1047 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1048 (CURRENCY_CODE,
1052 '''||l_rateType||''') ACTUAL_AMOUNT,
1049 '''||l_currency||''',
1050 &BIS_CURRENT_ASOF_DATE,
1051 SUM(ACTUAL_VALUE),
1053 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1054 (CURRENCY_CODE,
1055 '''||l_currency||''',
1056 &BIS_CURRENT_ASOF_DATE,
1057 SUM(COMMITMENT_VALUE),
1058 '''||l_rateType||''') COMMITTED_AMOUNT,
1059 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1060 (CURRENCY_CODE,
1061 '''||l_currency||''',
1062 &BIS_CURRENT_ASOF_DATE,
1063 HRI_OLTP_PMV_LBRCST_ORGMGR.CALC_PREV_VALUE_ELE
1064 (&HRI_PERSON+HRI_PER_USRDR_H
1065 ,&HRI_P_CHAR2_GA
1066 ,&HRI_P_CHAR4_GA
1067 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
1068 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
1069 ,ELEMENT_TYPE_ID
1070 ) ,
1071 '''||l_rateType||''') PREV_AVAILABLE
1072 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT
1073 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
1074 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
1075 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
1076 AND ORGANIZATION_ID = decode(&HRI_P_CHAR2_GA,0,ORGANIZATION_ID,&HRI_P_CHAR2_GA)
1077 AND POSITION_ID = decode(&HRI_P_CHAR4_GA,0,POSITION_ID,&HRI_P_CHAR4_GA)
1078 GROUP BY ELEMENT_TYPE_ID,
1079 CURRENCY_CODE )
1080 UNION ALL
1081 (SELECT ELEMENT_TYPE_ID,
1082 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1083 (CURRENCY_CODE,
1084 '''||l_currency||''',
1085 &BIS_CURRENT_ASOF_DATE,
1086 SUM(BUDGET_VALUE),
1087 '''||l_rateType||''') BUDGETED_AMOUNT,
1088 null ACTUAL_AMOUNT,
1089 null COMMITTED_AMOUNT,
1090 null PREV_AVAILABLE
1091 FROM HRI_MDP_BDGTS_LBRCST_ORGMGR_CT
1092 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
1093 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
1094 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
1095 AND ORGANIZATION_ID = decode(&HRI_P_CHAR2_GA,0,ORGANIZATION_ID,&HRI_P_CHAR2_GA)
1096 AND POSITION_ID = decode(&HRI_P_CHAR4_GA,0,POSITION_ID,&HRI_P_CHAR4_GA)
1097 GROUP BY ELEMENT_TYPE_ID,
1098 CURRENCY_CODE )
1099 )
1100 GROUP BY ELEMENT_TYPE_ID
1101 )tab,
1102 PAY_ELEMENT_TYPES_F ele
1103 WHERE ele.ELEMENT_TYPE_ID = tab.ID
1104 AND &BIS_CURRENT_ASOF_DATE BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
1105 &ORDER_BY_CLAUSE';
1106
1107
1108
1109 x_custom_sql := l_SQLText;
1110
1111 END GET_ELE_SQL;
1112
1113
1114 -- ----------------------------------------------------
1115 -- This procedure calculates the previous period Budgeted, Commitment and actual Labor Cost values for
1116 -- the Report Labor Cost Distribution By Funding Source.
1117 -- ----------------------------------------------------
1118
1119 FUNCTION CALC_PREV_VALUE_FSC(p_ORGMGR_ID NUMBER,
1120 p_organization_id NUMBER,
1121 p_position_id NUMBER,
1122 p_effective_start_date DATE,
1123 p_effective_end_date DATE,
1124 p_element_type_id NUMBER,
1125 p_cost_allocation_keyflex_id NUMBER)
1126 RETURN NUMBER IS
1127
1128 CURSOR prev_tot_budget IS
1129 SELECT SUM(bdg.BUDGET_VALUE) budgeted_amount
1130 FROM HRI_MDP_BDGTS_LBRCST_ORGMGR_CT bdg
1131 WHERE bdg.ORGMGR_ID = p_ORGMGR_ID
1132 AND bdg.ORGANIZATION_ID = decode(p_organization_id,0,bdg.ORGANIZATION_ID,p_organization_id)
1133 AND bdg.POSITION_ID = decode(p_position_id,0,bdg.POSITION_ID,p_position_id)
1134 AND bdg.ELEMENT_TYPE_ID = decode(p_element_type_id,0,bdg.ELEMENT_TYPE_ID,p_element_type_id)
1135 AND bdg.COST_ALLOCATION_KEYFLEX_ID = p_cost_allocation_keyflex_id
1136 AND bdg.EFFECTIVE_START_DATE <= p_effective_end_date
1137 AND bdg.EFFECTIVE_END_DATE >= p_effective_start_date;
1138
1139 CURSOR prev_tot_actual_cmmt IS
1140 SELECT SUM(act.ACTUAL_VALUE),
1141 SUM(act.COMMITMENT_VALUE)
1142 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT act
1143 WHERE act.ORGMGR_ID = p_ORGMGR_ID
1144 AND act.ORGANIZATION_ID = decode(p_organization_id,0,act.ORGANIZATION_ID,p_organization_id)
1145 AND act.POSITION_ID = decode(p_position_id,0,act.POSITION_ID,p_position_id)
1146 AND act.ELEMENT_TYPE_ID = decode(p_element_type_id,0,act.ELEMENT_TYPE_ID,p_element_type_id)
1147 AND act.COST_ALLOCATION_KEYFLEX_ID = p_cost_allocation_keyflex_id
1148 AND act.EFFECTIVE_START_DATE <= p_effective_end_date
1149 AND act.EFFECTIVE_END_DATE >= p_effective_start_date;
1150
1151 l_budgeted_amount number;
1152 l_actual_amount number;
1153 l_committed_amount number;
1154 l_available number := 0;
1155
1156 BEGIN
1157 OPEN prev_tot_budget;
1158 FETCH prev_tot_budget INTO l_budgeted_amount;
1159 CLOSE prev_tot_budget;
1160
1161 OPEN prev_tot_actual_cmmt;
1162 FETCH prev_tot_actual_cmmt INTO l_actual_amount,l_committed_amount;
1163 CLOSE prev_tot_actual_cmmt;
1164
1165 l_available := nvl(l_budgeted_amount,0) - (nvl(l_actual_amount,0) + nvl(l_committed_amount,0));
1166
1167 RETURN l_available;
1168 END CALC_PREV_VALUE_FSC;
1169
1170
1174
1171 -- ----------------------------------------------------
1172 -- This procedure frames the Query for the Report Labor Cost Distribution By Funding Source.
1173 -- ----------------------------------------------------
1175 PROCEDURE GET_FSC_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1176 x_custom_sql OUT NOCOPY VARCHAR2,
1177 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1178
1179 l_sqltext VARCHAR2(32767);
1180 l_custom_rec BIS_QUERY_ATTRIBUTES;
1181
1182 /* Parameter values */
1183 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
1184 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
1185
1186
1187 BEGIN
1188
1189 /* Initialize out parameters */
1190
1191 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1192 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1193
1194
1195 /* Get common parameter values */
1196 hri_oltp_pmv_util_param.get_parameters_from_table
1197 (p_page_parameter_tbl => p_page_parameter_tbl,
1198 p_parameter_rec => l_parameter_rec,
1199 p_bind_tab => l_bind_tab);
1200
1201
1202 l_currency:= l_parameter_rec.currency_code;
1203
1204 IF l_currency =bis_common_parameters.get_currency_code THEN
1205 l_rateType:=bis_common_parameters.get_rate_type;
1206 ELSIF l_currency =bis_common_parameters.get_secondary_currency_code THEN
1207 l_rateType:=bis_common_parameters.get_secondary_rate_type;
1208 END IF;
1209
1210
1211 l_sqltext :=
1212 'SELECT -- Labor Cost Distribution By Funding Source
1213 pck.concatenated_segments HRI_P_CHAR1_GA
1214 ,tab.budgeted_amount HRI_P_MEASURE1
1215 ,tab.committed_amount HRI_P_MEASURE2
1216 ,tab.actual_amount HRI_P_MEASURE3
1217 ,tab.total HRI_P_MEASURE4
1218 ,tab.total HRI_P_MEASURE7
1219 ,tab.available HRI_P_MEASURE5
1220 ,((tab.available - tab.prev_available)*100/(decode(tab.prev_available,0,1,tab.prev_available))) HRI_P_MEASURE6
1221 ,SUM(tab.budgeted_amount) OVER () HRI_P_GRAND_TOTAL1
1222 ,SUM(tab.committed_amount) OVER () HRI_P_GRAND_TOTAL2
1223 ,SUM(tab.actual_amount) OVER () HRI_P_GRAND_TOTAL3
1224 ,SUM(tab.total) OVER () HRI_P_GRAND_TOTAL4
1225 ,SUM(tab.available) OVER () HRI_P_GRAND_TOTAL5
1226 ,SUM( ((tab.available - tab.prev_available)*100/(decode(tab.prev_available,0,1,tab.prev_available))) ) OVER () HRI_P_GRAND_TOTAL6
1227 ,1 HRI_P_ORDER_BY_1
1228 FROM
1229 (
1230 SELECT
1231 COST_ALLOCATION_KEYFLEX_ID id
1232 ,SUM(budgeted_amount) budgeted_amount
1233 ,SUM(actual_amount) actual_amount
1234 ,SUM(committed_amount) committed_amount
1235 ,SUM(actual_amount) + SUM(committed_amount) total
1236 ,SUM(budgeted_amount) - NVL((SUM(actual_amount) + SUM(committed_amount)),0) available
1237 ,SUM(prev_available) prev_available
1238 FROM
1239 (
1240 (SELECT COST_ALLOCATION_KEYFLEX_ID,
1241 null BUDGETED_AMOUNT,
1242 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1243 (CURRENCY_CODE,
1244 '''||l_currency||''',
1245 &BIS_CURRENT_ASOF_DATE,
1246 SUM(ACTUAL_VALUE),
1247 '''||l_rateType||''') ACTUAL_AMOUNT,
1248 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1249 (CURRENCY_CODE,
1250 '''||l_currency||''',
1251 &BIS_CURRENT_ASOF_DATE,
1252 SUM(COMMITMENT_VALUE),
1253 '''||l_rateType||''') COMMITTED_AMOUNT,
1254 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1255 (CURRENCY_CODE,
1256 '''||l_currency||''',
1257 &BIS_CURRENT_ASOF_DATE,
1258 HRI_OLTP_PMV_LBRCST_ORGMGR.CALC_PREV_VALUE_FSC
1259 (&HRI_PERSON+HRI_PER_USRDR_H
1260 ,&HRI_P_CHAR2_GA
1261 ,&HRI_P_CHAR4_GA
1262 ,&BIS_PREVIOUS_EFFECTIVE_START_DATE
1263 ,&BIS_PREVIOUS_EFFECTIVE_END_DATE
1264 ,&HRI_P_CHAR3_GA
1265 ,COST_ALLOCATION_KEYFLEX_ID
1266 ) ,
1267 '''||l_rateType||''') PREV_AVAILABLE
1268 FROM HRI_MDP_CMNTS_ACTLS_ORGMGR_CT
1269 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
1270 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
1271 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
1272 AND ORGANIZATION_ID = DECODE(&HRI_P_CHAR2_GA,0,ORGANIZATION_ID,&HRI_P_CHAR2_GA)
1273 AND POSITION_ID = DECODE(&HRI_P_CHAR4_GA,0,POSITION_ID,&HRI_P_CHAR4_GA)
1274 AND ELEMENT_TYPE_ID = DECODE(&HRI_P_CHAR3_GA,0,ELEMENT_TYPE_ID,&HRI_P_CHAR3_GA)
1275 GROUP BY COST_ALLOCATION_KEYFLEX_ID,
1276 CURRENCY_CODE )
1277 UNION ALL
1278 (SELECT COST_ALLOCATION_KEYFLEX_ID,
1279 HRI_OLTP_VIEW_CURRENCY.CONVERT_CURRENCY_AMOUNT
1280 (CURRENCY_CODE,
1281 '''||l_currency||''',
1282 &BIS_CURRENT_ASOF_DATE,
1283 SUM(BUDGET_VALUE),
1284 '''||l_rateType||''') BUDGETED_AMOUNT,
1285 null ACTUAL_AMOUNT,
1286 null COMMITTED_AMOUNT,
1287 null PREV_AVAILABLE
1291 AND EFFECTIVE_END_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE
1288 FROM HRI_MDP_BDGTS_LBRCST_ORGMGR_CT
1289 WHERE ORGMGR_ID = &HRI_PERSON+HRI_PER_USRDR_H
1290 AND EFFECTIVE_START_DATE <= &BIS_CURRENT_EFFECTIVE_END_DATE
1292 AND ORGANIZATION_ID = DECODE(&HRI_P_CHAR2_GA,0,ORGANIZATION_ID,&HRI_P_CHAR2_GA)
1293 AND POSITION_ID = DECODE(&HRI_P_CHAR4_GA,0,POSITION_ID,&HRI_P_CHAR4_GA)
1294 AND ELEMENT_TYPE_ID = DECODE(&HRI_P_CHAR3_GA,0,ELEMENT_TYPE_ID,&HRI_P_CHAR3_GA)
1295 GROUP BY COST_ALLOCATION_KEYFLEX_ID,
1296 CURRENCY_CODE )
1297 )
1298 GROUP BY COST_ALLOCATION_KEYFLEX_ID
1299 )tab,
1300 pay_cost_allocation_keyflex pck
1301 WHERE pck.COST_ALLOCATION_KEYFLEX_ID = tab.ID
1302 &ORDER_BY_CLAUSE';
1303
1304 x_custom_sql := l_SQLText;
1305
1306 END GET_FSC_SQL;
1307
1308 END HRI_OLTP_PMV_LBRCST_ORGMGR;
1309