[Home] [Help]
PACKAGE BODY: APPS.HR_BIS_ORG_PERF
Source
1 PACKAGE BODY HR_BIS_ORG_PERF AS
2 /* $Header: hrbisorg.pkb 115.9 2002/04/17 03:43:52 pkm ship $ */
3 --
4 function get_start(p_organization_id NUMBER) return NUMBER is
5 begin
6 return(OrgPerfData(p_organization_id).start_val);
7 exception
8 when others then
9 return (0);
10 end;
11 --
12 function get_end(p_organization_id NUMBER) return NUMBER is
13 begin
14 return(OrgPerfData(p_organization_id).end_val);
15 exception
16 when others then
17 return (0);
18 end;
19 --
20 function get_increase(p_organization_id NUMBER) return NUMBER is
21 begin
22 return(OrgPerfData(p_organization_id).end_val- OrgPerfData(p_organization_id).start_val);
23 exception
24 when others then
25 return (0);
26 end;
27 --
28 function get_pct_increase(p_organization_id NUMBER) return NUMBER is
29 begin
30 return(100*(OrgPerfData(p_organization_id).end_val- OrgPerfData(p_organization_id).start_val)/ OrgPerfData(p_organization_id).start_val);
31 exception
32 when zero_divide then
33 return (0);
34 when others then
35 return (0);
36 end;
37 --
38 function get_gains(p_organization_id NUMBER) return NUMBER is
39 begin
40 return(OrgPerfData(p_organization_id).gains);
41 exception
42 when others then
43 return (0);
44 end;
45 --
46 function get_ended(p_organization_id NUMBER) return NUMBER is
47 begin
48 return(OrgPerfData(p_organization_id).ended);
49 exception
50 when others then
51 return (0);
52 end;
53 --
54 function get_transfered_out(p_organization_id NUMBER) return NUMBER is
55 begin
56 return(OrgPerfData(p_organization_id).transfered_out);
57 exception
58 when others then
59 return (0);
60 end;
61 --
62 function get_suspended(p_organization_id NUMBER) return NUMBER is
63 begin
64 return(OrgPerfData(p_organization_id).suspended);
65 exception
66 when others then
67 return (0);
68 end;
69 --
70 function get_sep_reason(p_organization_id NUMBER) return NUMBER is
71 begin
72 return(OrgPerfData(p_organization_id).sep_reason);
73 exception
74 when others then
75 return (0);
76 end;
77 --
78 function get_others(p_organization_id NUMBER) return NUMBER is
79 begin
80 return(OrgPerfData(p_organization_id).others);
81 exception
82 when others then
83 return (0);
84 end;
85 --
86 function get_sep_pct_increase(p_organization_id NUMBER) return NUMBER is
87 begin
88 return(100*OrgPerfData(p_organization_id).sep_reason/ OrgPerfData(p_organization_id).start_val);
89 exception
90 when zero_divide then
91 return (0);
92 when others then
93 return (0);
94 end;
95 --
96 procedure populate_manpower_table
97 ( p_org_param_id IN NUMBER
98 , p_budget_metric IN VARCHAR2
99 , p_business_group_id IN NUMBER
100 , p_top_org IN NUMBER
101 , p_start_date IN DATE
102 , p_end_date IN DATE )
103 is
104
105 cursor get_assignment
106 ( cp_org_param_id NUMBER
107 , cp_eff_date DATE )
108 is
109 select opl.organization_id_group organization_id -- S.Bhattal, 19/07/99
110 , asg.assignment_id
111 from per_assignment_status_types ast
112 , per_assignments_f asg
113 , hri_org_param_list opl
114 where opl.org_param_id = cp_org_param_id
115 and opl.organization_id_child = asg.organization_id
116 and cp_eff_date between asg.effective_start_date and
117 asg.effective_end_date
118 and asg.assignment_type = 'E'
119 and asg.assignment_status_type_id = ast.assignment_status_type_id
120 and ast.per_system_status = 'ACTIVE_ASSIGN';
121
122 cursor get_organizations
123 ( cp_org_param_id NUMBER )
124 is
125 select org.organization_id
126 from hr_organization_units org
127 , hri_org_param_list opl
128 where opl.org_param_id = cp_org_param_id
129 and opl.organization_id_child = org.organization_id
130 group by org.organization_id; -- S.Bhattal, 19/07/99
131
132 cursor c_get_bgt_formula
133 ( cp_business_group_id NUMBER )
134 is
135 select formula_id
136 from ff_formulas_f
137 where cp_business_group_id = business_group_id
138 and trunc(sysdate) between effective_start_date and effective_end_date
139 and formula_name = 'BUDGET_'||p_budget_metric;
140
141 cursor c_get_tmplt_formula is
142 select formula_id
143 from ff_formulas_f
144 where business_group_id is null
145 and trunc(sysdate) between effective_start_date and effective_end_date
146 and formula_name = 'TEMPLATE_'||p_budget_metric;
147
148 l_formula_id NUMBER;
149 l_manpower_start NUMBER;
150 l_manpower_end NUMBER;
151
152 begin
153 -- Populate the data table with zeros
154 for org_rec in get_organizations
155 ( p_org_param_id )
156 loop
157 OrgPerfData(org_rec.organization_id).start_val := 0;
158 OrgPerfData(org_rec.organization_id).end_val := 0;
159 end loop;
160
161 -- Look for the budget formula
162
163 open c_get_bgt_formula (p_business_group_id);
164 fetch c_get_bgt_formula into l_formula_id;
165
166 if (c_get_bgt_formula%notfound)
167 then
168 close c_get_bgt_formula;
169
170 -- If the budget formula does not exist, look for the template formula
171 open c_get_tmplt_formula;
172 fetch c_get_tmplt_formula into l_formula_id;
173
174 if (c_get_tmplt_formula%notfound)
175 then
176 close c_get_tmplt_formula;
177
178 -- Set to null so that we can calculate values differently later
179 l_formula_id := null;
180 else
181 close c_get_tmplt_formula;
182 end if;
183
184 else
185 close c_get_bgt_formula;
186 end if;
187
188 /****************************************
189 * Modified code starts here *
190 * S.Bhattal, 08-JUL-99, version 110.7 *
191 ****************************************/
192
193 for ass_rec in get_assignment
194 ( p_org_param_id
195 , p_start_date )
196 loop
197
198 l_manpower_start := HrFastAnswers.GetBudgetValue
199 ( p_budget_metric_formula_id => l_formula_id
200 , p_budget_metric => p_budget_metric
201 , p_assignment_id => ass_rec.assignment_id
202 , p_effective_date => p_start_date
203 , p_session_date => sysdate );
204
205 OrgPerfData(ass_rec.organization_id).start_val :=
206 OrgPerfData(ass_rec.organization_id).start_val + nvl(l_manpower_start,0);
207
208 end loop;
209
210 for ass_rec in get_assignment
211 ( p_org_param_id
212 , p_end_date )
213 loop
214
215 l_manpower_end := HrFastAnswers.GetBudgetValue
216 ( p_budget_metric_formula_id => l_formula_id
217 , p_budget_metric => p_budget_metric
218 , p_assignment_id => ass_rec.assignment_id
219 , p_effective_date => p_end_date
220 , p_session_date => sysdate );
221
222 OrgPerfData(ass_rec.organization_id).end_val :=
223 OrgPerfData(ass_rec.organization_id).end_val + nvl(l_manpower_end,0);
224
225 end loop;
226
227 end populate_manpower_table;
228
229 /* checks if an assignment is active and returns TRUE if it is */
230 /* part of bug fix 1747233 */
231 function check_asg_is_active(p_assignment_id number, p_effective_date date) return BOOLEAN
232 is
233
234 /* return a row if the assignment is active on cp_effective_date */
235 cursor cur_check_asg_active(cp_assignment_id number, cp_effective_date date) is
236 select 1
237 from per_all_assignments_f asg
238 where asg.assignment_id = cp_assignment_id
239 and cp_effective_date between asg.effective_start_date and asg.effective_end_date;
240
241 l_check_asg number;
242
243 begin
244
245 if (p_assignment_id is null) or (p_effective_date is null) then
246 return (FALSE);
247 end if;
248
249 open cur_check_asg_active(p_assignment_id , p_effective_date );
250 fetch cur_check_asg_active into l_check_asg;
251
252 if (cur_check_asg_active%rowcount = 0) or (cur_check_asg_active%notfound) then
253 close cur_check_asg_active;
254 return (FALSE);
255 end if;
256
257 close cur_check_asg_active;
258 return (TRUE);
259
260 exception
261 when others then
262 close cur_check_asg_active;
263 return(FALSE);
264
265 end check_asg_is_active;
266
267
268
269 procedure populate_separations_table
270 ( p_org_param_id IN NUMBER
271 , p_budget_metric IN VARCHAR2
272 , p_business_group_id IN NUMBER
273 , p_top_org IN NUMBER
274 , p_start_date IN DATE
275 , p_end_date IN DATE
276 , p_leaving_reason IN VARCHAR2)
277 is
278 cursor get_assignment
279 ( cp_org_param_id NUMBER
280 , cp_start_date DATE
281 , cp_end_date DATE
282 , cp_org_id NUMBER )
283 is
284 select asg.organization_id
285 , asg.assignment_id
286 , 1 no_change
287 , 0 gain
288 , 0 loss
289 from per_assignment_status_types ast
290 , per_assignments_f asg
291 , hr_all_organization_units org
292 , hri_org_param_list opl
293 where opl.org_param_id = cp_org_param_id
294 and opl.organization_id_group = cp_org_id
295 and opl.organization_id_child = asg.organization_id
296 and org.organization_id = asg.organization_id
297 and cp_end_date between asg.effective_start_date and asg.effective_end_date
298 and asg.assignment_type = 'E'
299 and asg.assignment_status_type_id = ast.assignment_status_type_id
300 and ast.per_system_status = 'ACTIVE_ASSIGN'
301 and exists (
302 select 1
303 from per_assignment_status_types ast2
304 , per_assignments_f asg2
305 , hri_org_param_list opl2
306 where opl2.org_param_id = cp_org_param_id
307 and opl2.organization_id_group = cp_org_id
308 and opl2.organization_id_child = asg2.organization_id
309 and asg2.assignment_id = asg.assignment_id
310 and asg2.assignment_status_type_id = ast2.assignment_status_type_id
311 and asg2.assignment_type = 'E'
312 and ast2.per_system_status = 'ACTIVE_ASSIGN'
313 and cp_start_date between asg2.effective_start_date and asg2.effective_end_date )
314 UNION
315 select asg.organization_id
316 , asg.assignment_id
317 , 0 no_change
318 , 1 gain
319 , 0 loss
320 from per_assignment_status_types ast
321 , per_assignments_f asg
322 , hr_all_organization_units org
323 , hri_org_param_list opl
324 where opl.org_param_id = cp_org_param_id
325 and opl.organization_id_group = cp_org_id
326 and opl.organization_id_child = asg.organization_id
327 and org.organization_id = asg.organization_id
328 and asg.assignment_type = 'E'
329 and cp_end_date between asg.effective_start_date and asg.effective_end_date
330 and asg.assignment_status_type_id = ast.assignment_status_type_id
331 and ast.per_system_status = 'ACTIVE_ASSIGN'
332 and not exists (
333 select 1
334 from per_assignment_status_types ast2
335 , per_assignments_f asg2
336 , hri_org_param_list opl2
337 where opl2.org_param_id = cp_org_param_id
338 and opl2.organization_id_group = cp_org_id
339 and opl2.organization_id_child = asg2.organization_id
340 and asg2.assignment_id=asg.assignment_id
341 and asg2.assignment_status_type_id = ast2.assignment_status_type_id
342 and asg2.assignment_type = 'E'
343 and ast2.per_system_status = 'ACTIVE_ASSIGN'
344 and cp_start_date between asg2.effective_start_date and asg2.effective_end_date)
345 UNION
346 select asg.organization_id
347 , asg.assignment_id
348 , 0 no_change
349 , 0 gain
350 , 1 loss
351 from per_assignment_status_types ast
352 , per_assignments_f asg
353 , hr_all_organization_units org
354 , hri_org_param_list opl
355 where opl.org_param_id = cp_org_param_id
356 and opl.organization_id_group = cp_org_id
357 and opl.organization_id_child = asg.organization_id
358 and org.organization_id = asg.organization_id
359 and asg.assignment_type = 'E'
360 and cp_start_date between asg.effective_start_date and asg.effective_end_date
361 and asg.assignment_status_type_id = ast.assignment_status_type_id
362 and ast.per_system_status = 'ACTIVE_ASSIGN'
363 and not exists (
364 select 1
365 from per_assignment_status_types ast2
366 , per_assignments_f asg2
367 , hri_org_param_list opl2
368 where opl2.org_param_id = cp_org_param_id
369 and opl2.organization_id_group = cp_org_id
370 and opl2.organization_id_child = asg2.organization_id
371 and asg2.assignment_id=asg.assignment_id
372 and asg2.assignment_status_type_id = ast2.assignment_status_type_id
373 and asg2.assignment_type = 'E'
374 and ast2.per_system_status = 'ACTIVE_ASSIGN'
375 and cp_end_date between asg2.effective_start_date and asg2.effective_end_date);
376
377 cursor get_organizations
378 ( cp_org_param_id NUMBER )
379 is
380 select org.organization_id
381 from hr_organization_units org
382 , hri_org_param_list opl
383 where opl.org_param_id = cp_org_param_id
384 and opl.organization_id_child = org.organization_id
385 group by org.organization_id; -- S.Bhattal, 19/07/99
386
387 cursor c_get_bgt_formula
388 ( cp_business_group_id NUMBER )
389 is
390 select formula_id
391 from ff_formulas_f
392 where cp_business_group_id = business_group_id
393 and trunc(sysdate) between effective_start_date and effective_end_date
394 and formula_name = 'BUDGET_'||p_budget_metric;
395
396 cursor c_get_tmplt_formula
397 is
398 select formula_id
399 from ff_formulas_f
400 where business_group_id is null
401 and trunc(sysdate) between effective_start_date and effective_end_date
402 and formula_name = 'TEMPLATE_'||p_budget_metric;
403
404 l_formula_id NUMBER;
405 l_manpower_start NUMBER :=0;
406 l_manpower_end NUMBER :=0;
407 l_assignment_category VARCHAR2(80);
408 l_leaving_reason VARCHAR2(80);
409 l_service_band VARCHAR2(80);
410 l_start NUMBER;
411 l_end NUMBER;
412 l_gains NUMBER;
413 l_ended NUMBER;
414 l_suspended NUMBER;
415 l_transfered NUMBER;
416 l_separated NUMBER;
417 l_other NUMBER;
418
419 begin
420 -- Populate the data table with zeros
421 for org_rec in get_organizations
422 ( p_org_param_id )
423 loop
424 OrgPerfData(org_rec.organization_id).start_val := 0;
425 OrgPerfData(org_rec.organization_id).end_val := 0;
426 OrgPerfData(org_rec.organization_id).gains := 0;
427 OrgPerfData(org_rec.organization_id).ended := 0;
428 OrgPerfData(org_rec.organization_id).suspended := 0;
429 OrgPerfData(org_rec.organization_id).transfered_out := 0;
430 OrgPerfData(org_rec.organization_id).sep_reason := 0;
431 OrgPerfData(org_rec.organization_id).others := 0;
432 end loop;
433
434 -- Look for the budget formula
435
436 open c_get_bgt_formula (p_business_group_id);
437 fetch c_get_bgt_formula into l_formula_id;
438
439 if (c_get_bgt_formula%notfound)
440 then
441 close c_get_bgt_formula;
442
443 -- If the budget formula does not exist, look for the template formula
444
445 open c_get_tmplt_formula;
446 fetch c_get_tmplt_formula into l_formula_id;
447
448 if (c_get_tmplt_formula%notfound)
449 then
450 close c_get_tmplt_formula;
451
452 -- set to null so that we can calculate values differently later
453 l_formula_id := null;
454 else
455 close c_get_tmplt_formula;
456 end if;
457 else
458 close c_get_bgt_formula;
459 end if;
460
461 /****************************************
462 * Modified code starts here *
463 * S.Bhattal, 08-JUL-99, version 110.7 *
464 ****************************************/
465
466 for org_rec in get_organizations
467 ( p_org_param_id )
468 loop
469
470 for ass_rec in get_assignment
471 ( p_org_param_id
472 , p_start_date
473 , p_end_date
474 , org_rec.organization_id)
475 loop
476
477 /* bug fix 1747233 03-MAY-2001 */
478 /* only call the fast formula if ass_rec.assignment_id exists on p_start_date */
479 if check_asg_is_active(ass_rec.assignment_id, p_start_date) then
480
481 l_manpower_start := nvl( HrFastAnswers.GetBudgetValue
482 ( p_budget_metric_formula_id => l_formula_id
483 , p_budget_metric => p_budget_metric
484 , p_assignment_id => ass_rec.assignment_id
485 , p_effective_date => p_start_date
486 , p_session_date => sysdate ), 0 );
487
488 end if;
489
490 /* bug fix 1747233 03-MAY-2001 */
491 /* only call the fast formula if ass_rec.assignment_id exists on p_end_date */
492 if check_asg_is_active(ass_rec.assignment_id, p_end_date) then
493
494 l_manpower_end := nvl( HrFastAnswers.GetBudgetValue
495 ( p_budget_metric_formula_id => l_formula_id
496 , p_budget_metric => p_budget_metric
497 , p_assignment_id => ass_rec.assignment_id
498 , p_effective_date => p_end_date
499 , p_session_date => sysdate ), 0 );
500
501 end if;
502
503 OrgPerfData(org_rec.organization_id).start_val :=
504 OrgPerfData(org_rec.organization_id).start_val +
505 (ass_rec.no_change + ass_rec.loss) * l_manpower_start;
506
507 OrgPerfData(org_rec.organization_id).end_val :=
508 OrgPerfData(org_rec.organization_id).end_val +
509 (ass_rec.no_change + ass_rec.gain) * l_manpower_end;
510
511 if (ass_rec.gain = 1)
512 then
513 OrgPerfData(org_rec.organization_id).gains :=
514 OrgPerfData(org_rec.organization_id).gains + l_manpower_end;
515
516 elsif (ass_rec.loss = 1)
517 then
518 HRFastAnswers.GetAssignmentCategory
519 ( p_org_param_id
520 , ass_rec.assignment_id
521 , p_start_date+1
522 , p_end_date
523 , org_rec.organization_id
524 , 'OUT'
525 , l_assignment_category
526 , l_leaving_reason
527 , l_service_band );
528
529 if (l_assignment_category = 'ENDED')
530 then
531 OrgPerfData(org_rec.organization_id).ended :=
532 OrgPerfData(org_rec.organization_id).ended + l_manpower_start;
533
534 elsif (l_assignment_category = 'TRANSFER_OUT')
535 then
536 OrgPerfData(org_rec.organization_id).transfered_out :=
537 OrgPerfData(org_rec.organization_id).transfered_out + l_manpower_start;
538
539 elsif (l_assignment_category = 'SUSPENDED')
540 then
541 OrgPerfData(org_rec.organization_id).suspended :=
542 OrgPerfData(org_rec.organization_id).suspended + l_manpower_start;
543
544 elsif (l_assignment_category = 'SEPARATED')
545 then
546 if (l_leaving_reason = p_leaving_reason or p_leaving_reason='BIS_ALL')
547 then
548 OrgPerfData(org_rec.organization_id).sep_reason :=
549 OrgPerfData(org_rec.organization_id).sep_reason + l_manpower_start;
550 else
551 OrgPerfData(org_rec.organization_id).others :=
552 OrgPerfData(org_rec.organization_id).others + l_manpower_start;
553 end if;
554
555 end if;
556
557 end if;
558 end loop;
559 end loop;
560
561 end populate_separations_table;
562
563
564 procedure populate_budget_table
565 ( p_budget_id IN NUMBER
566 , p_business_group_id IN NUMBER
567 , p_report_date IN DATE)
568 is
569 -- The subquery in the following cursor originally had DISTINCT
570 -- Removed by BDG on 28/04/1999
571 cursor get_assignment
572 ( cp_budget_id NUMBER
573 , cp_report_date DATE )
574 is
575 select asg.organization_id
576 , asg.assignment_id
577 from per_assignment_status_types ast
578 , per_assignments_f asg
579 where cp_report_date between asg.effective_start_date and asg.effective_end_date
580 and asg.assignment_type = 'E'
581 and ast.assignment_status_type_id = asg.assignment_status_type_id
582 and ast.per_system_status = 'ACTIVE_ASSIGN'
583 and asg.organization_id in (
584 select be.organization_id
585 from per_budget_values bval
586 , per_budget_elements be
587 , per_budget_versions bver
588 , per_time_periods tp
589 , per_budgets_v bud
590 where bud.budget_id = cp_budget_id
591 and bud.budget_id = bver.budget_id
592 and sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
593 and be.budget_version_id = bver.budget_version_id
594 and be.budget_element_id = bval.budget_element_id
595 and tp.time_period_id = bval.time_period_id
596 and cp_report_date between tp.start_date and tp.end_date );
597
598 cursor get_organizations
599 ( cp_budget_id NUMBER
600 , cp_report_date DATE )
601 is
602 select distinct be.organization_id
603 from per_budget_values bval
604 , per_budget_elements be
605 , per_budget_versions bver
606 , per_time_periods tp
607 , per_budgets_v bud
608 where bud.budget_id = cp_budget_id
609 and bud.budget_id = bver.budget_id
610 and sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
611 and be.budget_version_id = bver.budget_version_id
612 and be.budget_element_id = bval.budget_element_id
613 and tp.time_period_id = bval.time_period_id
614 and cp_report_date between tp.start_date and tp.end_date
615 -- bug 2324688
616 and be.organization_id is not null;
617
618 cursor get_budget_values
619 ( cp_budget_id NUMBER
620 , cp_report_date DATE )
621 is
622 select sum(bval.value) budget_value
623 , be.organization_id
624 from per_budget_values bval
625 , per_budget_elements be
626 , per_budget_versions bver
627 , per_time_periods tp
628 , per_budgets_v bud
629 where bud.budget_id = cp_budget_id
630 and bud.budget_id = bver.budget_id
631 and sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
632 and be.budget_version_id = bver.budget_version_id
633 and be.budget_element_id = bval.budget_element_id
634 and tp.time_period_id = bval.time_period_id
635 and be.organization_id is not null
636 and cp_report_date between tp.start_date and tp.end_date
637 group by be.organization_id;
638
639 cursor c_get_bgt_formula
640 ( cp_business_group_id NUMBER
641 , cp_budget_metric VARCHAR2 )
642 is
643 select formula_id
644 from ff_formulas_f
645 where cp_business_group_id = business_group_id
646 and trunc(sysdate) between effective_start_date and effective_end_date
647 and formula_name = 'BUDGET_'||cp_budget_metric;
648
649 cursor c_get_tmplt_formula
650 ( cp_budget_metric VARCHAR2 )
651 is
652 select formula_id
653 from ff_formulas_f
654 where business_group_id is null
655 and trunc(sysdate) between effective_start_date and effective_end_date
656 and formula_name = 'TEMPLATE_'||cp_budget_metric;
657
658 l_formula_id NUMBER;
659 l_manpower_start NUMBER;
660 l_manpower_end NUMBER;
661 l_budget_metric VARCHAR2(80);
662
663 begin
664 -- Populate the data table with zeros
665 for org_rec in get_organizations
666 ( p_budget_id
667 , p_report_date )
668 loop
669 OrgPerfData(org_rec.organization_id).start_val := 0;
670 OrgPerfData(org_rec.organization_id).end_val := 0;
671 end loop;
672
673 select unit
674 into l_budget_metric
675 from per_budgets
676 where budget_id = p_budget_id;
677
678 -- Look for the budget formula
679 open c_get_bgt_formula (p_business_group_id, l_budget_metric);
680 fetch c_get_bgt_formula into l_formula_id;
681
682 if (c_get_bgt_formula%notfound)
683 then
684 close c_get_bgt_formula;
685
686 -- if the budget formula does not exist, look for the template formula
687
688 open c_get_tmplt_formula (l_budget_metric);
689 fetch c_get_tmplt_formula into l_formula_id;
690
691 if (c_get_tmplt_formula%notfound)
692 then
693
694 -- set to null so that we can calculate values differently later
695 close c_get_tmplt_formula;
696 l_formula_id := null;
697 else
698 close c_get_tmplt_formula;
699 end if;
700 else
701 close c_get_bgt_formula;
702 end if;
703
704 /****************************************
705 * Modified code starts here *
706 * S.Bhattal, 08-JUL-99, version 110.7 *
707 ****************************************/
708
709 for ass_rec in get_assignment
710 ( p_budget_id
711 , p_report_date )
712 loop
713
714 l_manpower_start := HrFastAnswers.GetBudgetValue
715 ( p_budget_metric_formula_id => l_formula_id
716 , p_budget_metric => l_budget_metric
717 , p_assignment_id => ass_rec.assignment_id
718 , p_effective_date => p_report_date
719 , p_session_date => sysdate );
720
721 OrgPerfData(ass_rec.organization_id).start_val :=
722 OrgPerfData(ass_rec.organization_id).start_val + nvl(l_manpower_start,0);
723
724 end loop;
725
726 /****************************************
727 * Modified code ends here *
728 * S.Bhattal, 08-JUL-99, version 110.7 *
729 ****************************************/
730
731 -- Get the actuals
732
733 for bgt_rec in get_budget_values
734 ( p_budget_id
735 , p_report_date )
736 loop
737 OrgPerfData(bgt_rec.organization_id).end_val :=
738 OrgPerfData(bgt_rec.organization_id).end_val + bgt_rec.budget_value;
739 end loop;
740
741 end populate_budget_table;
742
743 -- cbridge, 25/10/2000, pqh budget reports changes
744 procedure populate_pqh_budget_table
745 ( p_budget_id IN NUMBER
746 , p_business_group_id IN NUMBER
747 , p_budget_metric IN VARCHAR2
748 , p_budget_unit IN NUMBER
749 , p_report_date IN DATE)
750 is
751 cursor get_assignment
752 ( cp_budget_id NUMBER
753 , cp_report_date DATE )
754 is
755 select asg.organization_id
756 , asg.assignment_id
757 from per_assignment_status_types ast
758 , per_assignments_f asg
759 where cp_report_date between asg.effective_start_date and asg.effective_end_date
760 and asg.assignment_type = 'E'
761 and ast.assignment_status_type_id = asg.assignment_status_type_id
762 and ast.per_system_status = 'ACTIVE_ASSIGN'
763 and asg.organization_id in (
764 select distinct bdet.organization_id
765 from pqh_budgets bud
766 , pqh_budget_versions bver
767 , pqh_budget_details bdet
768 , pqh_budget_periods bper
769 , per_shared_types pst1
770 , per_time_periods ptp
771 where bud.budget_id = cp_budget_id
772 and bud.budget_id = bver.budget_id
773 and sysdate between bver.date_from and nvl(bver.date_to, sysdate +1)
774 and bver.budget_version_id = bdet.budget_version_id
775 and bdet.budget_detail_id = bper.budget_detail_id
776 and bper.start_time_period_id = ptp.time_period_id
777 and bdet.organization_id is not null
778 and cp_report_date between ptp.start_date and ptp.end_date);
779
780 cursor get_organizations
781 ( cp_budget_id NUMBER
782 , cp_report_date DATE )
783 is
784 select distinct bdet.organization_id
785 from pqh_budgets bud
786 , pqh_budget_versions bver
787 , pqh_budget_details bdet
788 , pqh_budget_periods bper
789 , per_shared_types pst1
790 , per_time_periods ptp
791 where bud.budget_id = cp_budget_id
792 and bud.budget_id = bver.budget_id
793 and sysdate between bver.date_from and nvl(bver.date_to, sysdate +1)
794 and bver.budget_version_id = bdet.budget_version_id
795 and bdet.budget_detail_id = bper.budget_detail_id
796 and bper.start_time_period_id = ptp.time_period_id
797 and bdet.organization_id is not null
798 and cp_report_date between ptp.start_date and ptp.end_date;
799
800 cursor get_budget_values
801 ( cp_budget_id NUMBER
802 , cp_report_date DATE )
803 is
804 select SUM(bper.budget_unit1_value) budget_value1
805 , SUM(bper.budget_unit2_value) budget_value2
806 , SUM(bper.budget_unit3_value) budget_value3
807 , bdet.organization_id
808 from pqh_budgets bud
809 , pqh_budget_versions bver
810 , pqh_budget_details bdet
811 , pqh_budget_periods bper
812 , per_shared_types pst1
813 , per_shared_types pst2
814 , per_shared_types pst3
815 , per_time_periods ptp
816 where bud.budget_id = cp_budget_id
817 and bud.budget_unit1_id = pst1.shared_type_id
818 and bud.budget_unit2_id = pst2.shared_type_id (+)
819 and bud.budget_unit3_id = pst3.shared_type_id (+)
820 and bud.budget_id = bver.budget_id
821 and sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
822 and bver.budget_version_id = bdet.budget_version_id
823 and bdet.budget_detail_id = bper.budget_detail_id
824 and bper.start_time_period_id = ptp.time_period_id
825 and bdet.organization_id is not null
826 and cp_report_date between ptp.start_date and ptp.end_date
827 group by bdet.organization_id;
828
829
830 cursor c_get_bgt_formula
831 ( cp_business_group_id NUMBER
832 , cp_budget_metric VARCHAR2 )
833 is
834 select formula_id
835 from ff_formulas_f
836 where cp_business_group_id = business_group_id
837 and trunc(sysdate) between effective_start_date and effective_end_date
838 and formula_name = 'BUDGET_'||cp_budget_metric;
839
840 cursor c_get_tmplt_formula
841 ( cp_budget_metric VARCHAR2 )
842 is
843 select formula_id
844 from ff_formulas_f
845 where business_group_id is null
846 and trunc(sysdate) between effective_start_date and effective_end_date
847 and formula_name = 'TEMPLATE_'||cp_budget_metric;
848
849
850 l_formula_id NUMBER;
851 l_manpower_start NUMBER;
852 l_manpower_end NUMBER;
853 l_budget_metric VARCHAR2(80);
854 l_error varchar2(100) := '1';
855
856 begin
857
858
859
860 -- Populate the data table with zeros
861 for org_rec in get_organizations
862 ( p_budget_id
863 , p_report_date )
864 loop
865 OrgPerfData(org_rec.organization_id).start_val := 0;
866 OrgPerfData(org_rec.organization_id).end_val := 0;
867 htp.comment(OrgPerfData(org_rec.organization_id).end_val);
868 end loop;
869
870
871 l_budget_metric := p_budget_metric;
872
873 l_error := '2';
874
875 -- Look for the budget formula
876 open c_get_bgt_formula (p_business_group_id, l_budget_metric);
877 fetch c_get_bgt_formula into l_formula_id;
878
879 if (c_get_bgt_formula%notfound)
880 then
881 close c_get_bgt_formula;
882
883 -- if the budget formula does not exist, look for the template formula
884
885 open c_get_tmplt_formula (l_budget_metric);
886 fetch c_get_tmplt_formula into l_formula_id;
887
888 if (c_get_tmplt_formula%notfound)
889 then
890
891 -- set to null so that we can calculate values differently later
892 close c_get_tmplt_formula;
893 l_formula_id := null;
894 else
895 close c_get_tmplt_formula;
896 end if;
897 else
898 close c_get_bgt_formula;
899 end if;
900
901 /****************************************
902 * Modified code starts here *
903 * S.Bhattal, 08-JUL-99, version 110.7 *
904 ****************************************/
905
906 l_error := '3';
907
908 for ass_rec in get_assignment
909 ( p_budget_id
910 , p_report_date )
911 loop
912
913 l_error := '3.1 check if fastformula exist and compiled';
914
915 --hrfastanswers.checkfastformulacompiled(l_formula_id, l_budget_metric);
916
917 l_error := '3.1, l_budget_metric= '|| l_budget_metric || ' l_formula_id = ' || l_formula_id;
918
919 l_manpower_start := HrFastAnswers.GetBudgetValue
920 ( p_budget_metric_formula_id => l_formula_id
921 , p_budget_metric => l_budget_metric
922 , p_assignment_id => ass_rec.assignment_id
923 , p_effective_date => p_report_date
924 , p_session_date => sysdate );
925
926 l_error := '3.2, ass_rec.organization_id='|| ass_rec.organization_id;
927
928 OrgPerfData(ass_rec.organization_id).start_val :=
929 OrgPerfData(ass_rec.organization_id).start_val + nvl(l_manpower_start,0);
930
931 l_error := '3.3, ass_rec.organization_id='|| ass_rec.organization_id;
932
933 end loop;
934
935 l_error := '4';
936
937 -- Get the actuals
938
939 for bgt_rec in get_budget_values
940 ( p_budget_id
941 , p_report_date )
942 loop
943 if p_budget_unit = 1 then
944 OrgPerfData(bgt_rec.organization_id).end_val :=
945 OrgPerfData(bgt_rec.organization_id).end_val + bgt_rec.budget_value1;
946 elsif p_budget_unit = 2 then
947 OrgPerfData(bgt_rec.organization_id).end_val :=
948 OrgPerfData(bgt_rec.organization_id).end_val + bgt_rec.budget_value2;
949 elsif p_budget_unit = 3 then
950 OrgPerfData(bgt_rec.organization_id).end_val :=
951 OrgPerfData(bgt_rec.organization_id).end_val + bgt_rec.budget_value3;
952 else
953 raise no_data_found;
954 end if;
955
956 end loop;
957
958 l_error := '5';
959
960 end populate_pqh_budget_table;
961
962
963 END HR_BIS_ORG_PERF;