[Home] [Help]
PACKAGE BODY: APPS.GMS_BUDGET_UTILS
Source
1 package body gms_budget_utils as
2 /* $Header: gmsbubub.pls 120.5 2006/04/11 23:00:34 cmishra ship $ */
3
4 NO_DATA_FOUND_ERR number := 100;
5
6 --Bug 2587078 : To check on, whether to print debug messages in log file or not
7 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
8
9
10 procedure get_draft_version_id (x_project_id in number,
11 x_award_id in number,
12 x_budget_type_code in varchar2,
13 x_budget_version_id in out NOCOPY number,
14 x_err_code in out NOCOPY number,
15 x_err_stage in out NOCOPY varchar2,
16 x_err_stack in out NOCOPY varchar2)
17 is
18 old_stack varchar2(630);
19 begin
20
21 x_err_code := 0;
22 old_stack := x_err_stack;
23 x_err_stack := x_err_stack || '->get_draft_version_id';
24
25 x_err_stage := 'get draft budget id <' || to_char(x_project_id)
26 || '><' || x_budget_type_code || '>' ;
27
28 select budget_version_id
29 into x_budget_version_id
30 from gms_budget_versions
31 where project_id = x_project_id
32 and award_id = x_award_id
33 and budget_type_code = x_budget_type_code
34 and budget_status_code in ('W', 'S');
35
36 x_err_stack := old_stack;
37
38 exception
39 when NO_DATA_FOUND then
40 gms_error_pkg.gms_message(x_err_name => 'GMS_BU_CORE_NO_VERSION_ID',
41 x_err_code => x_err_code,
42 x_err_buff => x_err_stage);
43
44 when others then
45 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
46 x_token_name1 => 'SQLCODE',
47 x_token_val1 => sqlcode,
48 x_token_name2 => 'SQLERRM',
49 x_token_val2 => sqlerrm,
50 x_err_code => x_err_code,
51 x_err_buff => x_err_stage);
52
53 end get_draft_version_id;
54
55 -----------------------------------------------------------------------------
56
57 procedure get_baselined_version_id (x_project_id in number,
58 x_award_id in number,
59 x_budget_type_code in varchar2,
60 x_budget_version_id in out NOCOPY number,
61 x_err_code in out NOCOPY number,
62 x_err_stage in out NOCOPY varchar2,
63 x_err_stack in out NOCOPY varchar2)
64 is
65 old_stack varchar2(630);
66 begin
67
68 IF L_DEBUG = 'Y' THEN
69 gms_error_pkg.gms_debug('*** Start of GMS_BUDGET_UTILS.GET_BASELINED_VERSION_ID ***','C');
70 END IF;
71
72 x_err_code := 0;
73 old_stack := x_err_stack;
74 x_err_stack := x_err_stack || '->get_baselined_version_id';
75
76 x_err_stage := 'GMS_BUDGET_UTILS.GET_BASELINED_VERSION_ID- get baselined budget id <' || to_char(x_project_id)
77 || '><' || x_budget_type_code || '>' ;
78
79 select budget_version_id
80 into x_budget_version_id
81 from gms_budget_versions
82 where project_id = x_project_id
83 and award_id = x_award_id
84 and budget_type_code = x_budget_type_code
85 and current_flag = 'Y';
86
87 x_err_stack := old_stack;
88
89 IF L_DEBUG = 'Y' THEN
90 gms_error_pkg.gms_debug('*** End of GMS_BUDGET_UTILS.GET_BASELINED_VERSION_ID ***','C');
91 END IF;
92
93 exception
94 when NO_DATA_FOUND then
95 x_err_stage:= 'GMS_BUDGET_UTILS.GET_BASELINED_VERSION_ID- In NO_DATA_FOUND exception';
96 gms_error_pkg.gms_message(x_err_name => 'GMS_BU_CORE_NO_VERSION_ID',
97 x_err_code => x_err_code,
98 x_err_buff => x_err_stage);
99
100 when others then
101 x_err_stage:= 'GMS_BUDGET_UTILS.GET_BASELINED_VERSION_ID- In others exception';
102 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
103 x_token_name1 => 'SQLCODE',
104 x_token_val1 => sqlcode,
105 x_token_name2 => 'SQLERRM',
106 x_token_val2 => sqlerrm,
107 x_err_code => x_err_code,
108 x_err_buff => x_err_stage);
109
110 end get_baselined_version_id;
111
112 -----------------------------------------------------------------------------
113
114 procedure get_original_version_id (x_project_id in number,
115 x_award_id in number,
116 x_budget_type_code in varchar2,
117 x_budget_version_id in out NOCOPY number,
118 x_err_code in out NOCOPY number,
119 x_err_stage in out NOCOPY varchar2,
120 x_err_stack in out NOCOPY varchar2)
121 is
122 old_stack varchar2(630);
123 begin
124
125 x_err_code := 0;
126 old_stack := x_err_stack;
127 x_err_stack := x_err_stack || '->get_original_version_id';
128
129 x_err_stage := 'get original budget id <' || to_char(x_project_id)
130 || '><' || x_award_id || '>' ;
131
132 select budget_version_id
133 into x_budget_version_id
134 from gms_budget_versions
135 where project_id = x_project_id
136 and award_id = x_award_id
137 and budget_type_code = x_budget_type_code
138 and current_original_flag = 'Y';
139
140 x_err_stack := old_stack;
141
142 exception
143 when NO_DATA_FOUND then
144 gms_error_pkg.gms_message(x_err_name => 'GMS_BU_CORE_NO_VERSION_ID',
145 x_err_code => x_err_code,
146 x_err_buff => x_err_stage);
147
148 when others then
149 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
150 x_token_name1 => 'SQLCODE',
151 x_token_val1 => sqlcode,
152 x_token_name2 => 'SQLERRM',
153 x_token_val2 => sqlerrm,
154 x_err_code => x_err_code,
155 x_err_buff => x_err_stage);
156
157 end get_original_version_id;
158
159 -----------------------------------------------------------------------------
160
161 procedure get_default_resource_list_id (x_project_id in number,
162 x_award_id in number,
163 x_budget_type_code in varchar2,
164 x_resource_list_id in out NOCOPY number,
165 x_err_code in out NOCOPY number,
166 x_err_stage in out NOCOPY varchar2,
167 x_err_stack in out NOCOPY varchar2)
168 is
169 x_budget_amount_code varchar2(2);
170 x_allow_budget_entry_flag varchar2(2);
171 x_baselined_version_id number;
172 old_stack varchar2(630);
173
174 begin
175
176 x_err_code := 0;
177 old_stack := x_err_stack;
178 x_err_stack := x_err_stack || '->get_default_resource_list_id';
179
180 -- if a baselined budget exists
181 -- then get the resource_list_id from the baselined budget else get it from
182 -- pa_project_types
183
184 -- Get the baselined version
185 x_err_stage := 'get baselined budget id <' || to_char(x_project_id)
186 || '><' || x_award_id || '>';
187
188 gms_budget_utils.get_baselined_version_id(x_project_id,
189 x_award_id,
190 x_budget_type_code,
191 x_baselined_version_id,
192 x_err_code,
193 x_err_stage,
194 x_err_stack
195 );
196
197 if (x_err_code = 0) then
198 -- baselined budget exists, use it to get the resource list
199
200 select resource_list_id
201 into x_resource_list_id
202 from gms_budget_versions
203 where budget_version_id = x_baselined_version_id;
204
205 elsif (x_err_code > 0) then
206
207 -- baseline version does not exist. Get it from pa_project_type
208 x_err_code := 0;
209 x_err_stage := 'get budget amount code <' || x_budget_type_code || '>' ;
210
211 select budget_amount_code
212 into x_budget_amount_code
213 from pa_budget_types
214 where budget_type_code = x_budget_type_code;
215
216 x_err_stage := 'get default resource list id <' || to_char(x_project_id)
217 || '>' ;
218
219 if (x_budget_amount_code = 'C') then
220
221 select t.allow_cost_budget_entry_flag,
222 t.cost_budget_resource_list_id
223 into x_allow_budget_entry_flag,
224 x_resource_list_id
225 from pa_project_types t,
226 pa_projects p
227 where p.project_id = x_project_id
228 and p.project_type = t.project_type;
229
230 else
231
232 select t.allow_rev_budget_entry_flag,
233 t.rev_budget_resource_list_id
234 into x_allow_budget_entry_flag,
235 x_resource_list_id
236 from pa_project_types t,
237 pa_projects p
238 where p.project_id = x_project_id
239 and p.project_type = t.project_type;
240
241 end if;
242
243 if (x_allow_budget_entry_flag = 'N') then
244
245 gms_error_pkg.gms_message(x_err_name => 'GMS_BU_ENTRY_NOT_ALLOWED',
246 x_err_code => x_err_code,
247 x_err_buff => x_err_stage);
248
249 APP_EXCEPTION.RAISE_EXCEPTION;
250
251 end if;
252
253 if (x_resource_list_id is null) then
254 gms_error_pkg.gms_message(x_err_name => 'GMS_BU_NO_DFLT_RESOURCE_LIST',
255 x_err_code => x_err_code,
256 x_err_buff => x_err_stage);
257
258 APP_EXCEPTION.RAISE_EXCEPTION;
259
260 /** -- jjj - x_err_code = 100 ???
261 x_err_code := NO_DATA_FOUND_ERR;
262 x_err_stage := 'GMS_BU_NO_DFLT_RESOURCE_LIST';
263 return;
264 **/
265 end if;
266
267 x_err_stack := old_stack;
268 else
269 -- x_err_code < 0
270 return;
271 end if;
272
273 exception
274 when others
275 then
276 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
277 x_token_name1 => 'SQLCODE',
278 x_token_val1 => sqlcode,
279 x_token_name2 => 'SQLERRM',
280 x_token_val2 => sqlerrm,
281 x_err_code => x_err_code,
282 x_err_buff => x_err_stage);
283
284 end get_default_resource_list_id;
285
286 -----------------------------------------------------------------------------
287
288 procedure get_default_entry_method_code (x_project_id in number,
289 x_budget_type_code in varchar2,
290 x_budget_entry_method_code in out NOCOPY varchar2,
291 x_err_code in out NOCOPY number,
292 x_err_stage in out NOCOPY varchar2,
293 x_err_stack in out NOCOPY varchar2)
294 is
295 x_budget_amount_code varchar2(2);
296 x_allow_budget_entry_flag varchar2(2);
297 old_stack varchar2(630);
298
299 begin
300
301 x_err_code := 0;
302 old_stack := x_err_stack;
303 x_err_stack := x_err_stack || '->get_default_entry_method_code';
304
305 x_err_stage := 'get budget amount code <' || x_budget_type_code || '>' ;
306
307 select budget_amount_code
308 into x_budget_amount_code
309 from pa_budget_types
310 where budget_type_code = x_budget_type_code;
311
312 x_err_stage := 'get default budget entry method <'
313 || to_char(x_project_id) || '>' ;
314
315 if (x_budget_amount_code = 'C') then
316
317 select t.allow_cost_budget_entry_flag,
318 t.cost_budget_entry_method_code
319 into x_allow_budget_entry_flag,
320 x_budget_entry_method_code
321 from pa_project_types t,
322 pa_projects p
323 where p.project_id = x_project_id
324 and p.project_type = t.project_type;
325
326 else
327
328 select t.allow_rev_budget_entry_flag,
329 t.rev_budget_entry_method_code
330 into x_allow_budget_entry_flag,
331 x_budget_entry_method_code
332 from pa_project_types t,
333 pa_projects p
334 where p.project_id = x_project_id
335 and p.project_type = t.project_type;
336
337 end if;
338
339 if (x_allow_budget_entry_flag = 'N') then
340 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_ENTRY_NOT_ALLOWED',
341 x_err_code => x_err_code,
342 x_err_buff => x_err_stage);
343
344 APP_EXCEPTION.RAISE_EXCEPTION;
345 end if;
346
347 if (x_budget_entry_method_code is null) then
348 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_DFLT_ENTRY_METHOD',
349 x_err_code => x_err_code,
350 x_err_buff => x_err_stage);
351
352 APP_EXCEPTION.RAISE_EXCEPTION;
353
354 /** - jjj - x_err_code = 100 ????
355 x_err_code := NO_DATA_FOUND_ERR;
356 x_err_stage := 'GMS_BU_NO_DFLT_ENTRY_METHOD';
357 return;
358 **/
359
360 end if;
361
362 x_err_stack := old_stack;
363
364 exception
365 when others then
366 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
367 x_token_name1 => 'SQLCODE',
368 x_token_val1 => sqlcode,
369 x_token_name2 => 'SQLERRM',
370 x_token_val2 => sqlerrm,
371 x_err_code => x_err_code,
372 x_err_buff => x_err_stage);
373
374 end get_default_entry_method_code;
375
376 -----------------------------------------------------------------------------
377
378 function get_budget_type_code (x_budget_type in varchar2)
379 return varchar2
380 is
381 x_budget_type_code varchar2(30);
382 begin
383
384 x_budget_type_code := NULL;
385
386 select budget_type_code
387 into x_budget_type_code
388 from pa_budget_types
389 where budget_type = x_budget_type;
390
391 return x_budget_type_code;
392
393 exception
394 when others then
395 return NULL;
396 end get_budget_type_code;
397
398 -----------------------------------------------------------------------------
399
400 function get_budget_entry_method_code (x_budget_entry_method in varchar2)
401 return varchar2
402 is
403 x_budget_entry_method_code varchar2(30);
404 begin
405
406 x_budget_entry_method_code := NULL;
407
408 select budget_entry_method_code
409 into x_budget_entry_method_code
410 from pa_budget_entry_methods
411 where budget_entry_method = x_budget_entry_method;
412
413 return x_budget_entry_method_code;
414
415 exception
416 when others then
417 return NULL;
418 end get_budget_entry_method_code;
419
420 -----------------------------------------------------------------------------
421
422 function get_change_reason_code (x_meaning in varchar2)
423 return varchar2
424 is
425 x_change_reason_code varchar2(30);
426 begin
427
428 x_change_reason_code := NULL;
429
430 select lookup_code
431 into x_change_reason_code
432 from pa_lookups
433 where lookup_type = 'BUDGET CHANGE REASON'
434 and meaning = x_meaning;
435
436 return x_change_reason_code;
437
438 exception
439 when others then
440 return NULL;
441 end get_change_reason_code;
442
443
444 ------------------------------------------------------------------------------
445
446 function check_proj_budget_exists (x_project_id in number,
447 x_award_id in number,
448 x_budget_status_code varchar2,
449 x_budget_type_code varchar2)
450 return number
451 is
452 dummy number;
453 begin
454
455 if (x_budget_status_code = 'A') then
456 select 1
457 into dummy
458 from sys.dual
459 where exists
460 (select 1
461 from gms_budget_versions
462 where project_id = x_project_id
463 and award_id = x_award_id
464 and budget_type_code =
465 nvl(x_budget_type_code, budget_type_code));
466 return 1;
467
468 elsif (x_budget_status_code = 'B') then
469 select 1
470 into dummy
471 from sys.dual
472 where exists
473 (select 1
474 from gms_budget_versions
475 where project_id = x_project_id
476 and award_id = x_award_id
477 and budget_type_code =
478 nvl(x_budget_type_code, budget_type_code)
479 and budget_status_code = 'B');
480 return 1;
481
482 else
483 return 0;
484 end if;
485
486
487 exception
488 when NO_DATA_FOUND then
489 return 0;
490
491 when others then
492 return SQLCODE;
493
494 end check_proj_budget_exists;
495
496 ------------------------------------------------------------------------------
497
498 function check_task_budget_exists (x_task_id in number,
499 x_award_id in number,
500 x_budget_status_code varchar2,
501 x_budget_type_code varchar2)
502 return number
503 is
504 dummy number;
505 begin
506
507 if (x_budget_status_code = 'A') then
508 select 1
509 into dummy
510 from sys.dual
511 where exists
512 (select 1
513 from gms_budget_versions v,
514 gms_resource_assignments a
515 where a.task_id = x_task_id
516 and v.award_id = x_award_id
517 and v.budget_version_id = a.budget_version_id
518 and v.budget_type_code =
519 nvl(x_budget_type_code, v.budget_type_code));
520 return 1;
521
522 elsif (x_budget_status_code = 'B') then
523 select 1
524 into dummy
525 from sys.dual
526 where exists
527 (select 1
528 from gms_budget_versions v,
529 pa_tasks t,
530 gms_resource_assignments a
531 where a.budget_version_id = v.budget_version_id
532 and v.budget_status_code = 'B'
533 and a.task_id = t.task_id
534 and t.top_task_id = x_task_id
535 and v.award_id = x_award_id
536 and v.budget_type_code =
537 nvl(x_budget_type_code, v.budget_type_code));
538 return 1;
539
540 else
541 return 0;
542 end if;
543
544
545 exception
546 when NO_DATA_FOUND then
547 return 0;
548
549 when others then
550 return SQLCODE;
551
552 end check_task_budget_exists;
553
554 ---------------------------------------------------------------------------
555
556 function check_resource_member_level (x_resource_list_member_id in number,
557 x_parent_member_id in number,
558 x_budget_version_id in number,
559 x_task_id in number)
560 return number
561 is
562 dummy number;
563 begin
564
565 if (x_parent_member_id = 0) then
566
567 select 1
568 into dummy
569 from sys.dual
570 where exists
571 (select 1
572 from pa_resource_list_members m,
573 gms_resource_assignments a
574 where m.parent_member_id = x_resource_list_member_id
575 and m.resource_list_member_id = a.resource_list_member_id
576 and a.budget_version_id = x_budget_version_id
577 and a.task_id = x_task_id);
578
579 else
580 select 1
581 into dummy
582 from sys.dual
583 where exists
584 (select 1
585 from gms_resource_assignments a
586 where a.budget_version_id = x_budget_version_id
587 and a.task_id = x_task_id
588 and a.resource_list_member_id = x_parent_member_id);
589
590 end if;
591
592 return 1;
593
594 exception
595 when NO_DATA_FOUND then
596 return 0;
597
598 when others then
599 return SQLCODE;
600
601 end check_resource_member_level;
602
603 ---------------------------------------------------------------------------
604 /* commented out for Bug 2601648
605 Procedure check_overlapping_dates ( x_budget_version_id NUMBER,
606 x_resource_name IN OUT NOCOPY VARCHAR2,
607 x_err_code IN OUT NOCOPY NUMBER) is
608 v_temp varchar2(1);
609 cursor c is
610 select a.resource_name
611 from gms_budget_lines_v a, gms_budget_lines_v b
612 where a.budget_version_id = x_budget_version_id
613 and b.budget_version_id = x_budget_version_id
614 and a.task_id||null = b.task_id||null
615 -- and a.resource_list_member_id = b.resource_list_member_id Bug 2601648
616 and a.row_id <> b.row_id
617 and ((a.start_date
618 between b.start_date + 1 -- Bug 2601648 Added + 1
619 and nvl(b.end_date,a.start_date +1))
620 or (a.end_date
621 between b.start_date
622 and nvl(b.end_date - 1,b.end_date+1)) -- Bug 2601648 Added - 1
623 or (b.start_date
624 between a.start_date + 1 -- Bug 2601648 Added + 1
625 and nvl(a.end_date,b.start_date+1))
626 );
627 BEGIN
628 open c;
629 fetch c into x_resource_name;
630 if c%found then
631 x_err_code :=1;
632 else
633 x_err_code :=0;
634 end if;
635 close c;
636 EXCEPTION
637 when others then
638 x_err_code :=sqlcode;
639 END check_overlapping_dates;
640 */
641 ---------------------------------------------------------------------------
642 -- select changed for Bug 2601648
643 Procedure check_overlapping_dates ( x_budget_version_id NUMBER,
644 x_resource_name IN OUT NOCOPY VARCHAR2,
645 x_err_code IN OUT NOCOPY NUMBER) is
646 v_temp varchar2(1);
647 l_resource_list_member_id number;
648 cursor c is
649 select a1.resource_list_member_id
650 from gms_resource_assignments a1,
651 gms_budget_lines a2,
652 gms_resource_assignments b1,
653 gms_budget_lines b2
654 where a1.resource_assignment_id = a2.resource_assignment_id
655 and b1.resource_assignment_id = b2.resource_assignment_id
656 and a1.budget_version_id = b1.budget_version_id
657 and a1.budget_version_id = x_budget_version_id
658 and not (a1.rowid = b1.rowid and a2.rowid = b2.rowid)
659 and b2.end_date >= a2.start_date
660 and b2.start_date <= a2.end_date
661 and not (a2.start_date = b2.start_date and a2.end_date = b2.end_date);
662
663 BEGIN
664 open c;
665 fetch c into l_resource_list_member_id;
666
667 if c%found then
668 select alias
669 into x_resource_name
670 from pa_resource_list_members
671 where resource_list_member_id = l_resource_list_member_id;
672 x_err_code :=1;
673 else
674 x_err_code :=0;
675 end if;
676 close c;
677 EXCEPTION
678 when others then
679 x_err_code :=sqlcode;
680 END check_overlapping_dates;
681
682 ---------------------------------------------------------------------------
683 procedure get_proj_budget_amount(
684 x_project_id in number,
685 x_award_id in number,
686 x_budget_type in varchar2,
687 x_which_version in varchar2,
688 x_revenue_amount out NOCOPY real,
689 x_raw_cost out NOCOPY real,
690 x_burdened_cost out NOCOPY real,
691 x_labor_quantity out NOCOPY real) IS
692
693 budget_status varchar2(30) := NULL;
694 current_flag varchar2(30) := NULL;
695 original_flag varchar2(30) := NULL;
696 raw_cost REAL := 0;
697 burdened_cost REAL := 0;
698 labor_qty REAL := 0;
699 revenue_amount REAL := 0;
700
701 BEGIN
702
703 if x_which_version = 'DRAFT' then
704
705 budget_status := 'O'; -- Non-baselined.
706
707 elsif x_which_version = 'CURRENT' then
708
709 budget_status := 'B';
710 current_flag := 'Y';
711
712 else -- 'ORIGINAL'
713
714 budget_status := 'B';
715 original_flag := 'Y';
716
717 end if;
718
719 SELECT nvl(SUM(nvl(b.raw_cost,0)), 0),
720 nvl(SUM(nvl(b.burdened_cost,0)), 0),
721 nvl(SUM(nvl(b.labor_quantity,0)), 0),
722 nvl(SUM(nvl(b.revenue,0)), 0)
723 INTO raw_cost,
724 burdened_cost,
725 labor_qty,
726 revenue_amount
727 FROM gms_budget_versions b
728 WHERE b.project_id = x_project_id
729 AND b.award_id = x_award_id
730 AND b.budget_type_code = x_budget_type
731 AND b.budget_status_code = decode(budget_status, 'B', 'B',
732 b.budget_status_code)
733 AND NOT (budget_status = 'O' and b.budget_status_code = 'B')
734 AND b.current_flag||'' = nvl(current_flag, b.current_flag)
735 AND b.current_original_flag =
736 nvl(original_flag, b.current_original_flag);
737
738 x_raw_cost := raw_cost;
739 x_burdened_cost := burdened_cost;
740 x_labor_quantity := labor_qty;
741 x_revenue_amount := revenue_amount;
742
743 END get_proj_budget_amount;
744
745 ---------------------------------------------------------------------------
746
747 -- This procedure is copied from pb_public.get_budget_amount and will
748 -- be modified later for general use.
749 procedure get_task_budget_amount(
750 x_project_id in number,
751 x_task_id in number,
752 x_award_id in number,
753 x_budget_type in varchar2,
754 x_which_version in varchar2,
755 x_revenue_amount out NOCOPY real,
756 x_raw_cost out NOCOPY real,
757 x_burdened_cost out NOCOPY real,
758 x_labor_quantity out NOCOPY real) IS
759
760 budget_status varchar2(30) := NULL;
761 current_flag varchar2(30) := NULL;
762 original_flag varchar2(30) := NULL;
763 raw_cost REAL := 0;
764 burdened_cost REAL := 0;
765 labor_qty REAL := 0;
766 revenue_amount REAL := 0;
767
768 BEGIN
769
770 if x_which_version = 'DRAFT' then
771
772 budget_status := 'O'; -- Non-baselined.
773
774 elsif x_which_version = 'CURRENT' then
775
776 budget_status := 'B';
777 current_flag := 'Y';
778
779 else -- 'ORIGINAL'
780
781 budget_status := 'B';
782 original_flag := 'Y';
783
784 end if;
785
786 SELECT nvl(SUM(nvl(l.raw_cost,0)), 0),
787 nvl(SUM(nvl(l.burdened_cost,0)), 0),
788 nvl(SUM(decode(a.track_as_labor_flag,'Y',nvl(l.quantity,0),0)), 0),
789 nvl(SUM(nvl(l.revenue,0)), 0)
790 INTO raw_cost,
791 burdened_cost,
792 labor_qty,
793 revenue_amount
794 FROM gms_budget_lines l,
795 gms_resource_assignments a,
796 pa_tasks t,
797 gms_budget_versions v
798 WHERE v.project_id = x_project_id
799 AND v.award_id = x_award_id
800 AND v.budget_type_code = x_budget_type
801 AND v.budget_status_code = decode(budget_status, 'B', 'B',
802 v.budget_status_code)
803 and NOT (budget_status = 'O' and v.budget_status_code = 'B')
804 and v.current_flag||'' = nvl(current_flag, v.current_flag)
805 and a.budget_version_id = v.budget_version_id
806 and a.project_id = v.project_id
807 and t.project_id = x_project_id
808 and t.task_id = a.task_id
809 and x_task_id in (t.top_task_id, t.task_id)
810 and v.current_original_flag =
811 nvl(original_flag, v.current_original_flag)
812 AND l.resource_assignment_id = a.resource_assignment_id;
813
814 x_raw_cost := raw_cost;
815 x_burdened_cost := burdened_cost;
816 x_labor_quantity := labor_qty;
817 x_revenue_amount := revenue_amount;
818
819 END get_task_budget_amount;
820
821 --------------------------------------------------------------------------
822
823 --Name: Verify_Budget_Rules
824 --Type: Procedure
825 --
826 --Description: This procedure is called both from the Oracle Projects
827 -- Budgets form (GMSBUEBU.fmb) when the Submit
828 -- and Baseline buttons are pressed and the
829 -- public Baseline_Budget api.
830 --
831 -- This procedure does the following:
832 -- 1) It performs Oracle Project product specific
833 -- validations.
834 -- 2) It calls a client extension for additional
835 -- client specific validations.
836 --
837 -- The procedure also distinguishes between
838 -- submission edits ('SUBMIT') and
839 -- baseline edits ('BASELINE') as determined
840 -- by the value of the p_event parameter.
841 --
842 -- Most of the Oracle Project product specific code
843 -- was copied from the gms_budget_core.baseline
844 -- procedure. Now, the gms_budget_core.baseline
845 -- validation calls this procedure.
846 --
847 --
848 --Called subprograms: GMS_Client_Extn_Budget.Verify_Budget_Rulesc
849 --
850 --
851 --
852 --History:
853 --
854 --
855 PROCEDURE Verify_Budget_Rules
856 (p_draft_version_id IN NUMBER
857 , p_mark_as_original IN VARCHAR2
858 , p_event IN VARCHAR2
859 , p_project_id IN NUMBER
860 , p_award_id IN NUMBER
861 , p_budget_type_code IN VARCHAR2
862 , p_resource_list_id IN NUMBER
863 , p_project_type_class_code IN VARCHAR2
864 , p_created_by IN NUMBER
865 , p_calling_module IN VARCHAR2
866 , p_warnings_only_flag OUT NOCOPY VARCHAR2
867 , p_err_msg_count OUT NOCOPY NUMBER
868 , p_err_code IN OUT NOCOPY NUMBER
869 , p_err_stage IN OUT NOCOPY VARCHAR2
870 , p_err_stack IN OUT NOCOPY VARCHAR2
871 )
872
873 IS
874 --
875 l_entry_level_code VARCHAR2(30);
876 l_dummy NUMBER;
877 l_budget_total NUMBER DEFAULT 0;
878 l_old_stack VARCHAR2(630);
879 l_funding_level VARCHAR2(2) DEFAULT NULL;
880
881 l_ext_warnings_only_flag VARCHAR2(1) := NULL;
882 l_ext_err_msg_count NUMBER := 0;
883
884
885 BEGIN
886 -- dbms_output.put_line('GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES - Inside');
887 IF L_DEBUG = 'Y' THEN
888 gms_error_pkg.gms_debug('*** Start of GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES ***','C');
889 gms_error_pkg.gms_debug('GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- p_event : '||p_event,'C');
890 END IF;
891
892 -- Initialize OUT-parameters for multiple error messaging
893
894 p_warnings_only_flag := 'Y';
895 p_err_msg_count := 0;
896 ----------------------------------------------------------------------
897
898 p_err_code := 0;
899 l_old_stack := p_err_stack;
900 p_err_stack := p_err_stack || '->check_budget_rules';
901
902 IF( PA_UTILS.GetEmpIdFromUser(p_created_by ) IS NULL) THEN
903 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- Error occurred while validating employee info';
904 gms_error_pkg.gms_message( x_err_name => 'GMS_ALL_WARN_NO_EMPL_REC',
905 x_err_code => p_err_code,
906 x_err_buff => p_err_stage);
907
908 APP_EXCEPTION.RAISE_EXCEPTION;
909
910 /*
911 PA_UTILS.Add_Message
912 ( p_app_short_name => 'GMS'
913 , p_msg_name => p_err_stage
914 );
915 */
916 p_warnings_only_flag := 'N';
917
918 END IF;
919
920 IF (p_event = 'SUBMIT')
921 THEN
922
923 -- GMS Standard SUBMIT validation - None currently
924 NULL;
925 ELSE
926
927 -- GMS Standard BASELINE validation.
928
929 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- get draft budget info <' || to_char(p_draft_version_id)
930 || '>';
931
932
933 -- check if there is at least one project or task draft budget exists
934
935 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- check budget exists <' || to_char(p_draft_version_id)
936 || '>';
937
938 BEGIN
939 select 1
940 into l_dummy
941 from sys.dual
942 where exists
943 (select 1
944 from gms_resource_assignments
945 where budget_version_id = p_draft_version_id);
946
947 EXCEPTION
948 WHEN NO_DATA_FOUND THEN
949 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- In NO_DATA_FOUND exception';
950 gms_error_pkg.gms_message( x_err_name => 'GMS_NO_BUDGET_LINES', -- 'GMS_BU_NO_BUDGET', Bug 2587078
951 x_err_code => p_err_code,
952 x_err_buff => p_err_stage);
953
954 /* PA_UTILS.Add_Message
955 ( p_app_short_name => 'GMS'
956 , p_msg_name => p_err_stage
957 );
958 */
959
960 p_warnings_only_flag := 'N';
961
962
963 WHEN OTHERS
964 THEN
965 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- In OTHERS exception';
966 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
967 x_token_name1 => 'SQLCODE',
968 x_token_val1 => sqlcode,
969 x_token_name2 => 'SQLERRM',
970 x_token_val2 => sqlerrm,
971 x_err_code => p_err_code,
972 x_err_buff => p_err_stage);
973
974 p_warnings_only_flag := 'N';
975 p_err_msg_count := FND_MSG_PUB.Count_Msg; -- jjj - ????
976
977 /* p_err_code := SQLCODE;
978 FND_MSG_PUB.Add_Exc_Msg
979 ( p_pkg_name => 'GMS_BUDGET_UTILS'
980 , p_procedure_name => 'VERIFY_BUDGET_RULES'
981 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
982 );
983 p_err_msg_count := FND_MSG_PUB.Count_Msg;
984
985 RETURN;
986 */
987 END;
988
989 END IF; -- OP Standard Validations
990
991 -- Client Specific Validations --------------------------------------------------
992
993 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- Check Client Extn Verify Budget Rules <' || to_char(p_project_id )
994 || '><'|| p_budget_type_code
995 || '>'|| to_char(p_draft_version_id)
996 || '>'|| p_mark_as_original
997 || '>';
998
999 -- dbms_output.put_line('Call Client Extn VERIFY_BUDGET_RULES');
1000 GMS_CLIENT_EXTN_BUDGET.Verify_Budget_Rules
1001 (p_draft_version_id => p_draft_version_id
1002 , p_mark_as_original => p_mark_as_original
1003 , p_event => p_event
1004 , p_project_id => p_project_id
1005 , p_budget_type_code => p_budget_type_code
1006 , p_resource_list_id => p_resource_list_id
1007 , p_project_type_class_code => p_project_type_class_code
1008 , p_created_by => p_created_by
1009 , p_calling_module => p_calling_module
1010 , p_warnings_only_flag => l_ext_warnings_only_flag
1011 , p_err_msg_count => l_ext_err_msg_count
1012 , p_error_code => p_err_code
1013 , p_error_message => p_err_stage
1014 );
1015
1016 -- dbms_output.put_line('Return from Client Extn VERIFY_BUDGET_RULES');
1017
1018 -- PA_UTILS.Add_Message already addressed internally by client extn
1019 -- Verify_Budget_Rules
1020 -- Only RETURN if Oracle error. Otherwise, continue processing.
1021
1022 IF (l_ext_err_msg_count > 0)
1023 THEN
1024 IF L_DEBUG = 'Y' THEN
1025 gms_error_pkg.gms_debug('GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- GMS_CLIENT_EXTN_BUDGET.Verify_Budget_Rules returned failure status','C');
1026 END IF;
1027
1028 IF (l_ext_warnings_only_flag = 'N') THEN
1029 p_warnings_only_flag := 'N';
1030 END IF;
1031 END IF;
1032
1033 p_err_msg_count := FND_MSG_PUB.Count_Msg;
1034 p_err_stack := l_old_stack;
1035
1036 IF L_DEBUG = 'Y' THEN
1037 gms_error_pkg.gms_debug('*** End of GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES ***','C');
1038 END IF;
1039
1040 EXCEPTION
1041 WHEN OTHERS
1042 THEN
1043
1044 p_err_stage := 'GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES- In others exception';
1045 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
1046 x_token_name1 => 'SQLCODE',
1047 x_token_val1 => sqlcode,
1048 x_token_name2 => 'SQLERRM',
1049 x_token_val2 => sqlerrm,
1050 x_err_code => p_err_code,
1051 x_err_buff => p_err_stage);
1052
1053 p_warnings_only_flag := 'N';
1054 p_err_msg_count := FND_MSG_PUB.Count_Msg;
1055
1056 /* p_err_code := SQLCODE;
1057 FND_MSG_PUB.Add_Exc_Msg
1058 ( p_pkg_name => 'GMS_BUDGET_UTILS'
1059 , p_procedure_name => 'VERIFY_BUDGET_RULES'
1060 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
1061 );
1062 p_warnings_only_flag := 'N';
1063 p_err_msg_count := FND_MSG_PUB.Count_Msg;
1064 RETURN;
1065 */
1066
1067 END Verify_Budget_Rules;
1068
1069 ---------------------------------------------------------------------------
1070
1071 procedure get_project_task_totals(x_budget_version_id in number,
1072 x_task_id in number,
1073 x_quantity_total in out NOCOPY number,
1074 x_raw_cost_total in out NOCOPY number,
1075 x_burdened_cost_total in out NOCOPY number,
1076 x_revenue_total in out NOCOPY number,
1077 x_err_code in out NOCOPY number,
1078 x_err_stage in out NOCOPY varchar2,
1079 x_err_stack in out NOCOPY varchar2) is
1080 /****************************************************************
1081 How to use this API:
1082 This API can be used to get the totals at the Project Level
1083 or at the task level. If x_task_id is passed as a null value then
1084 project level totals are fetched. Otherwise task level totals are
1085 fetched. For task level totals, first the task level is determined.
1086 If the task level is top or intermediate level , then the amounts
1087 are rolled from the child tasks.
1088 ******************************************************************/
1089
1090 v_rollup_flag varchar2(1);
1091 old_stack varchar2(630);
1092
1093 cursor get_rollup_level is
1094 select 'P'
1095 from dual
1096 where x_task_id is null
1097 union
1098 select 'T'
1099 from pa_tasks
1100 where x_task_id is not null
1101 and task_id = x_task_id
1102 and parent_task_id is null
1103 union
1104 select 'M'
1105 from pa_tasks
1106 where x_task_id is not null
1107 and task_id = x_task_id
1108 and parent_task_id is not null
1109 and exists (select 'X'
1110 from pa_tasks
1111 where parent_task_id = x_task_id)
1112 union
1113 select 'L'
1114 from dual
1115 where x_task_id is not null
1116 and not exists (select 'X'
1117 from pa_tasks
1118 where parent_task_id = x_task_id);
1119
1120 cursor get_totals is
1121 select labor_quantity,
1122 raw_cost,
1123 burdened_cost,
1124 revenue
1125 from gms_budget_versions
1126 where v_rollup_flag = 'P' -- Project Level
1127 and budget_version_id = x_budget_version_id
1128 union
1129 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
1130 SUM(NVL(l.RAW_COST,0)),
1131 SUM(NVL(l.BURDENED_COST,0)),
1132 SUM(NVL(l.REVENUE,0))
1133 from pa_tasks t,
1134 gms_budget_lines l ,
1135 gms_resource_assignments a
1136 where v_rollup_flag = 'T' -- Top Task Level
1137 and a.budget_version_id = x_budget_version_id
1138 and a.task_id = t.task_id
1139 and t.top_task_id = x_task_id
1140 and a.resource_assignment_id = l.resource_assignment_id
1141 union
1142 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
1143 SUM(NVL(l.RAW_COST,0)),
1144 SUM(NVL(l.BURDENED_COST,0)),
1145 SUM(NVL(l.REVENUE,0))
1146 from gms_budget_lines l,
1147 gms_resource_assignments a
1148 where v_rollup_flag = 'M' -- Middle Task Level
1149 and a.budget_version_id = x_budget_version_id
1150 and a.task_id in (select task_id
1151 from pa_tasks
1152 start with task_id = x_task_id
1153 connect by prior task_id = parent_task_id)
1154 and a.resource_assignment_id = l.resource_assignment_id
1155 union
1156 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
1157 SUM(NVL(l.RAW_COST,0)),
1158 SUM(NVL(l.BURDENED_COST,0)),
1159 SUM(NVL(l.REVENUE,0))
1160 from gms_budget_lines l,
1161 gms_resource_assignments a
1162 where v_rollup_flag = 'L' -- Lowest Task Level
1163 and a.budget_version_id = x_budget_version_id
1164 and a.task_id = x_task_id
1165 and a.resource_assignment_id = l.resource_assignment_id;
1166
1167 begin
1168 x_err_code := 0;
1169 old_stack := x_err_stack;
1170 x_err_stack := x_err_stack || '->GMS_BUDGET_UTILS.get_project_task_totals';
1171
1172 open get_rollup_level;
1173 fetch get_rollup_level into v_rollup_flag;
1174 close get_rollup_level;
1175 x_err_stage := x_raw_cost_total;
1176 open get_totals;
1177 fetch get_totals into
1178 x_quantity_total,
1179 x_raw_cost_total,
1180 x_burdened_cost_total,
1181 x_revenue_total;
1182 close get_totals;
1183 x_err_stack := old_stack;
1184
1185 exception
1186 when others then
1187 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
1188 x_token_name1 => 'SQLCODE',
1189 x_token_val1 => sqlcode,
1190 x_token_name2 => 'SQLERRM',
1191 x_token_val2 => sqlerrm,
1192 x_err_code => x_err_code,
1193 x_err_buff => x_err_stage);
1194
1195 -- x_err_code := SQLCODE;
1196 -- return;
1197 end;
1198
1199 ---------------------------------------------------------------------------
1200 --
1201 -- This function returns a value 'Y' if the UOM passed
1202 -- is a currency UOM. Otherwise it returns 'N'.
1203 --
1204 Function Check_Currency_Uom (x_uom_code in varchar2)
1205 return varchar2 is
1206 cursor check_uom is
1207 select currency_uom_flag
1208 from pa_currency_uom_v
1209 where uom_code = x_uom_code;
1210
1211 v_currency_uom_flag varchar2(1);
1212
1213 Begin
1214 open check_uom;
1215 fetch check_uom into v_currency_uom_flag;
1216 if check_uom%notfound then
1217 return 'Y';
1218 else
1219 return nvl(v_currency_uom_flag,'Y');
1220 end if;
1221 close check_uom;
1222
1223 End;
1224
1225 ---------------------------------------------------------------------------
1226 --
1227 -- This function returns the value of budget amount code
1228 -- associated with the budget type. Budget Amount Code
1229 -- determines whethere its a cost or a revenue budget.
1230 --
1231 Function get_budget_amount_code (x_budget_type_code in varchar2)
1232 return varchar2 is
1233 cursor get_budget_amount_code is
1234 select budget_amount_code
1235 from pa_budget_types
1236 where budget_type_code = x_budget_type_code;
1237
1238 v_budget_amount_code varchar2(1);
1239
1240 Begin
1241 open get_budget_amount_code ;
1242 fetch get_budget_amount_code into v_budget_amount_code;
1243 close get_budget_amount_code;
1244
1245 return v_budget_amount_code;
1246 End;
1247
1248 ---------------------------------------------------------------------------
1249
1250 -- Assigning the value of Budget Entry Level Code to a global
1251 -- variable.
1252 Procedure set_entry_level_code(x_entry_level_code in varchar2) is
1253 Begin
1254 g_entry_level_code := x_entry_level_code;
1255 End;
1256
1257 ---------------------------------------------------------------------------
1258 -- Returning the value of global variable for Budget Entry Level Code
1259 Function get_entry_level_code return varchar2 is
1260 Begin
1261 return g_entry_level_code;
1262 End;
1263 ----------------------------------------------------------------------------------------
1264 --Name: get_valid_period_dates
1265 --Type: Procedure
1266 --Description: This procedure can be used to get the valid begin and end date
1267 -- for a budget line
1268 --
1269 --
1270 --Called subprograms:
1271 --
1272 --
1273 --History:
1274 --
1275
1276 PROCEDURE get_valid_period_dates
1277 ( x_err_code OUT NOCOPY NUMBER
1278 ,x_err_stage OUT NOCOPY VARCHAR2
1279 ,p_project_id IN NUMBER
1280 ,p_task_id IN NUMBER
1281 ,p_award_id IN NUMBER -- Added For bug 2200867
1282 ,p_time_phased_type_code IN VARCHAR2
1283 ,p_entry_level_code IN VARCHAR2
1284 ,p_period_name_in IN VARCHAR2
1285 ,p_budget_start_date_in IN DATE
1286 ,p_budget_end_date_in IN DATE
1287 ,p_period_name_out OUT NOCOPY VARCHAR2
1288 ,p_budget_start_date_out OUT NOCOPY DATE
1289 ,p_budget_end_date_out OUT NOCOPY DATE )
1290
1291 IS
1292
1293 CURSOR l_budget_periods_csr
1294 (p_period_name VARCHAR2
1295 ,p_period_type_code VARCHAR2 )
1296 IS
1297 SELECT period_start_date
1298 , period_end_date
1299 FROM pa_budget_periods_v
1300 WHERE period_name = p_period_name
1301 AND period_type_code = p_period_type_code;
1302
1303 CURSOR l_period_name_csr
1304 (p_start_date DATE
1305 ,p_end_date DATE
1306 ,p_period_type_code VARCHAR2 )
1307 IS
1308 SELECT period_name
1309 FROM pa_budget_periods_v
1310 WHERE period_type_code = p_period_type_code
1311 AND period_start_date = p_start_date
1312 AND period_end_date = p_end_date;
1313
1314
1315 CURSOR l_project_dates_csr
1316 ( p_project_id NUMBER )
1317 IS
1318 SELECT start_date
1319 , completion_date
1320 FROM pa_projects
1321 WHERE project_id = p_project_id;
1322
1323 CURSOR l_task_dates_csr
1324 ( p_task_id NUMBER )
1325 IS
1326 SELECT start_date
1327 , completion_date
1328 FROM pa_tasks
1329 WHERE task_id = p_task_id;
1330
1331 --Added For Bug 2200867
1332 CURSOR l_award_dates_csr
1333 (p_award_id NUMBER)
1334 IS
1335 SELECT nvl(preaward_date,start_date_active), -- Added preaward_date for Bug:2266731
1336 end_date_active
1337 FROM gms_awards
1338 WHERE award_id = p_award_id;
1339
1340 l_api_name CONSTANT VARCHAR2(30) := 'get_valid_period_dates';
1341 l_task_start_date DATE;
1342 l_task_end_date DATE;
1343 l_project_start_date DATE;
1344 l_project_end_date DATE;
1345 l_budget_start_date DATE;
1346 l_budget_end_date DATE;
1347 l_period_name VARCHAR2(20);
1348
1349 BEGIN
1350 x_err_code := 0;
1351
1352 IF L_DEBUG = 'Y' THEN
1353 gms_error_pkg.gms_debug('*** Start of GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES ***','C');
1354 END IF;
1355
1356 -- Standard begin of API savepoint
1357
1358 SAVEPOINT get_valid_period_dates_pvt;
1359
1360 -- The following IF statement is added for Bug:2362968 (GMS_BUDGET_PUB.ADD_BUDGET_LINE ACCEPTING INVALID DATES)
1361
1362 IF p_budget_start_date_in > p_budget_end_date_in THEN
1363 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occured while validating the dates';
1364 gms_error_pkg.gms_message(x_err_name => 'GMS_SU_INVALID_DATES',
1365 x_err_code => x_err_code,
1366 x_err_buff => x_err_stage);
1367
1368 APP_EXCEPTION.RAISE_EXCEPTION;
1369
1370 END IF;
1371
1372 -- check business rules related to timephasing
1373 -- P = PA period, G = GL period, R = Date Range
1374
1375 IF p_time_phased_type_code = 'P'
1376 OR p_time_phased_type_code = 'G'
1377 THEN
1378
1379 -- dbms_output.put_line('Time phased code: '||p_time_phased_type_code);
1380 -- dbms_output.put_line('Period name : '||p_period_name_in);
1381
1382 IF p_period_name_in IS NULL
1383 OR p_period_name_in = GMS_BUDGET_PUB.G_PA_MISS_CHAR
1384 THEN
1385
1386 IF p_budget_start_date_in IS NULL
1387 OR p_budget_start_date_in = GMS_BUDGET_PUB.G_PA_MISS_DATE
1388 OR p_budget_end_date_in IS NULL
1389 OR p_budget_end_date_in = GMS_BUDGET_PUB.G_PA_MISS_DATE
1390 THEN
1391 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- error occurred while calculating dates for Time phase = PA/GL';
1392 gms_error_pkg.gms_message( x_err_name => 'GMS_BUDGET_DATES_MISSING',
1393 x_err_code => x_err_code,
1394 x_err_buff => x_err_stage);
1395
1396 APP_EXCEPTION.RAISE_EXCEPTION;
1397 ELSE
1398
1399 --try to get the period name related to those dates
1400
1401 OPEN l_period_name_csr( p_budget_start_date_in
1402 ,p_budget_end_date_in
1403 ,p_time_phased_type_code );
1404
1405 FETCH l_period_name_csr INTO l_period_name;
1406
1407 -- dbms_output.put_line('Period name: '||l_period_name);
1408
1409 IF l_period_name_csr%NOTFOUND
1410 THEN
1411 CLOSE l_period_name_csr;
1412 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occurred while calculating dates for Time phase = PA/GL';
1413 gms_error_pkg.gms_message( x_err_name => 'GMS_BUDGET_DATES_INVALID',
1414 x_err_code => x_err_code,
1415 x_err_buff => x_err_stage);
1416
1417 APP_EXCEPTION.RAISE_EXCEPTION;
1418 END IF;
1419
1420 CLOSE l_period_name_csr;
1421
1422 p_budget_start_date_out := p_budget_start_date_in;
1423 p_budget_end_date_out := p_budget_end_date_in;
1424 p_period_name_out := l_period_name;
1425 END IF;
1426
1427 ELSE
1428
1429 --get the related start and end dates
1430 OPEN l_budget_periods_csr
1431 ( p_period_name_in
1432 , p_time_phased_type_code );
1433
1434
1435 FETCH l_budget_periods_csr
1436 INTO l_budget_start_date, l_budget_end_date;
1437
1438 IF l_budget_periods_csr%NOTFOUND
1439 THEN
1440 CLOSE l_budget_periods_csr;
1441 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occurred while calculating dates for Time phase = PA/GL';
1442 gms_error_pkg.gms_message( x_err_name => 'GMS_BUDGET_PERIOD_IS_INVALID',
1443 x_err_code => x_err_code,
1444 x_err_buff => x_err_stage);
1445
1446 APP_EXCEPTION.RAISE_EXCEPTION;
1447 END IF;
1448
1449 CLOSE l_budget_periods_csr;
1450
1451 p_budget_start_date_out := l_budget_start_date;
1452 p_budget_end_date_out := l_budget_end_date;
1453 p_period_name_out := p_period_name_in;
1454
1455 END IF; --is period_name_in missing
1456
1457 ELSIF p_time_phased_type_code = 'R'
1458 THEN
1459
1460 --validation of incoming dates
1461
1462 IF p_budget_start_date_in = GMS_BUDGET_PUB.G_PA_MISS_DATE
1463 OR p_budget_start_date_in IS NULL
1464 THEN
1465 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occurred while calculating dates for Time phase = R';
1466 gms_error_pkg.gms_message( x_err_name => 'GMS_BUDGET_DATES_MISSING' ,-- 'GMS_START_DATE_IS_MISSING', Bug 2587078
1467 x_err_code => x_err_code,
1468 x_err_buff => x_err_stage);
1469
1470 APP_EXCEPTION.RAISE_EXCEPTION;
1471 END IF;
1472
1473 IF p_budget_end_date_in = GMS_BUDGET_PUB.G_PA_MISS_DATE
1474 OR p_budget_end_date_in IS NULL
1475 THEN
1476 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occurred while calculating dates for Time phase = R';
1477 gms_error_pkg.gms_message( x_err_name => 'GMS_BUDGET_DATES_MISSING', -- 'GMS_END_DATE_IS_MISSING', Bug 2587078
1478 x_err_code => x_err_code,
1479 x_err_buff => x_err_stage);
1480
1481 APP_EXCEPTION.RAISE_EXCEPTION;
1482 END IF;
1483
1484
1485 -- For entry methods specified as 'date range', start and end dates were not being returned.
1486 --
1487 p_budget_start_date_out := p_budget_start_date_in;
1488 p_budget_end_date_out := p_budget_end_date_in;
1489 p_period_name_out := p_period_name_in;
1490 -- -------------------------------------------------------------------------------------------------------------
1491
1492
1493 ELSE --time_phased_type_code = 'N'
1494
1495 --Modifications for Bug 2200867
1496 OPEN l_award_dates_csr(p_award_id);
1497 FETCH l_award_dates_csr INTO l_budget_start_date, l_budget_end_date;
1498 CLOSE l_award_dates_csr;
1499
1500 IF p_entry_level_code = 'P'
1501 THEN
1502
1503 OPEN l_project_dates_csr(p_project_id);
1504 FETCH l_project_dates_csr INTO l_project_start_date, l_project_end_date;
1505 CLOSE l_project_dates_csr;
1506
1507 IF l_project_start_date IS NULL
1508 THEN
1509 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occurred while calculating dates for Time phase = N and entry level code ='||p_entry_level_code;
1510 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_TASK_PROJ_DATE' , --'GMS_PROJ_START_DATE_MISS1', 2587078
1511 x_err_code => x_err_code,
1512 x_err_buff => x_err_stage);
1513
1514 APP_EXCEPTION.RAISE_EXCEPTION;
1515 --Modifications for Bug 2200867
1516 END IF;
1517 /************Commenting the code for Bug 2200867
1518 ELSIF l_project_end_date IS NULL
1519 THEN
1520
1521 gms_error_pkg.gms_message( x_err_name => 'GMS_PROJ_END_DATE_MISS1',
1522 x_err_code => x_err_code,
1523 x_err_buff => x_err_stage);
1524
1525 APP_EXCEPTION.RAISE_EXCEPTION;
1526
1527 ELSE
1528 ************************/
1529 -------Modiifed the p_budget_start_date_out and p_budget_end_date For bug 2200867-----------
1530 p_budget_start_date_out := greatest(l_budget_start_date,l_project_start_date);
1531 p_budget_end_date_out := least(l_budget_end_date,nvl(l_project_end_date,l_budget_end_date));
1532 -----End of Changes----------
1533 p_period_name_out := p_period_name_in;
1534
1535 -- END IF; Moved the END IF up
1536
1537 ELSIF p_entry_level_code IN ('T','M','L')
1538 THEN
1539
1540 OPEN l_task_dates_csr(p_task_id);
1541 FETCH l_task_dates_csr INTO l_task_start_date, l_task_end_date;
1542 CLOSE l_task_dates_csr;
1543
1544 IF l_task_start_date IS NULL
1545 OR l_task_end_date IS NULL
1546 THEN
1547 OPEN l_project_dates_csr(p_project_id);
1548 FETCH l_project_dates_csr INTO l_project_start_date, l_project_end_date;
1549 CLOSE l_project_dates_csr;
1550 END IF; -- Moved the End If Up as part of Bug 2200867
1551
1552 IF l_task_start_date IS NULL --implies that task_end_date is null too!!
1553 THEN
1554
1555 IF l_project_start_date IS NULL --implies that project end date is null too
1556 THEN
1557 x_err_stage := 'GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- Error occurred while calculating dates for Time phase = N and entry level code ='||p_entry_level_code;
1558 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_TASK_PROJ_DATE' , -- 'GMS_PROJ_START_DATE_MISS2', 2587078
1559 x_err_code => x_err_code,
1560 x_err_buff => x_err_stage);
1561
1562 APP_EXCEPTION.RAISE_EXCEPTION;
1563
1564 ---------Modifications for Bug 2200867-------------------
1565 ELSE
1566 l_budget_start_date := greatest(l_budget_start_date,l_project_start_date);
1567 l_budget_end_date := least(l_budget_end_date,nvl(l_project_end_date,l_budget_end_date));
1568 END IF;
1569 ELSE --Task start date is not null
1570 l_budget_start_date:= greatest(l_budget_start_date,l_task_start_date);
1571
1572 IF l_task_end_date IS NULL THEN
1573 l_budget_end_date:=least(l_budget_end_date,nvl(l_project_end_date,l_budget_end_date));
1574 ELSE
1575 l_budget_end_date:=least(l_budget_end_date,l_task_end_date);
1576 END IF;
1577 END IF;
1578 p_budget_start_date_out := l_budget_start_date;
1579 p_budget_end_date_out := l_budget_end_date;
1580 p_period_name_out := p_period_name_in;
1581 /*****************Commenting for Bug 2200867
1582 ELSIF l_project_end_date IS NULL
1583 THEN
1584 gms_error_pkg.gms_message( x_err_name => 'GMS_PROJ_END_DATE_MISS2',
1585 x_err_code => x_err_code,
1586 x_err_buff => x_err_stage);
1587
1588 APP_EXCEPTION.RAISE_EXCEPTION;
1589
1590 ELSE
1591
1592 p_budget_start_date_out := l_project_start_date;
1593 p_budget_end_date_out := l_project_end_date;
1594 p_period_name_out := p_period_name_in;
1595
1596 END IF;
1597
1598 ELSIF l_task_start_date IS NOT NULL
1599 AND l_task_end_date IS NULL
1600 THEN
1601
1602 IF l_project_end_date IS NULL
1603 THEN
1604
1605 gms_error_pkg.gms_message( x_err_name => 'GMS_PROJ_END_DATE_MISS3',
1606 x_err_code => x_err_code,
1607 x_err_buff => x_err_stage);
1608
1609 APP_EXCEPTION.RAISE_EXCEPTION;
1610
1611 ELSE
1612 p_budget_start_date_out := l_task_start_date;
1613 p_budget_end_date_out := l_project_end_date;
1614 p_period_name_out := p_period_name_in;
1615
1616 END IF;
1617
1618 END IF;
1619
1620 ELSE
1621 p_budget_start_date_out := l_task_start_date;
1622 p_budget_end_date_out := l_task_end_date;
1623 p_period_name_out := p_period_name_in;
1624
1625 END IF;
1626 ********************************/
1627
1628 END IF; --entry level code
1629
1630 END IF; --time phased type code
1631
1632 /**
1633 -- Commented out NOCOPY the Exception section as a part of Bug:2362968 (GMS_BUDGET_PUB.ADD_BUDGET_LINE ACCEPTING INVALID DATES)
1634 -- since there is no necessity to rollback anything here and also commenting this out NOCOPY will cause the actual error message
1635 -- to show up during any error condition.
1636
1637 EXCEPTION
1638 WHEN OTHERS
1639 THEN
1640 IF L_DEBUG = 'Y' THEN
1641 gms_error_pkg.gms_debug('GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES- In OTHERS exception','C');
1642 END IF;
1643
1644 ROLLBACK TO get_valid_period_dates_pvt;
1645
1646 x_err_code := 10;
1647 x_err_stage := 'GMS_GET_PERIOD_DATE_FAIL';
1648 **/
1649
1650 IF L_DEBUG = 'Y' THEN
1651 gms_error_pkg.gms_debug('*** End of GMS_BUDGET_UTILS.GET_VALID_PERIOD_DATES ***','C');
1652 END IF;
1653
1654 END get_valid_period_dates;
1655
1656
1657 ----------------------------------------------------------------------------------------
1658 --Name: check_entry_method_flags
1659 --Type: Procedure
1660 --Description: This procedure can be used to check whether it is allowed to pass
1661 -- cost quantity, raw_cost, burdened_cost, revenue and revenue quantity.
1662 --
1663 --
1664 --Called subprograms:
1665 --
1666 --
1667 --
1668 --History:
1669 --
1670
1671 PROCEDURE check_entry_method_flags
1672 ( x_err_code OUT NOCOPY NUMBER
1673 ,x_err_stage OUT NOCOPY VARCHAR2
1674 ,p_budget_amount_code IN VARCHAR2
1675 ,p_budget_entry_method_code IN VARCHAR2
1676 ,p_quantity IN VARCHAR2
1677 ,p_raw_cost IN VARCHAR2
1678 ,p_burdened_cost IN VARCHAR2)
1679 IS
1680
1681 CURSOR l_budget_entry_method_csr
1682 (p_budget_entry_method_code pa_budget_entry_methods.budget_entry_method_code%type )
1683 IS
1684 SELECT cost_quantity_flag
1685 , raw_cost_flag
1686 , burdened_cost_flag
1687 , rev_quantity_flag
1688 FROM pa_budget_entry_methods
1689 WHERE budget_entry_method_code = p_budget_entry_method_code
1690 AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
1691
1692 l_api_name CONSTANT VARCHAR2(30) := 'check_entry_method_flags';
1693
1694 l_cost_quantity_flag VARCHAR2(1);
1695 l_raw_cost_flag VARCHAR2(1);
1696 l_burdened_cost_flag VARCHAR2(1);
1697 l_rev_quantity_flag VARCHAR2(1);
1698
1699
1700 BEGIN
1701
1702 x_err_code := 0;
1703
1704 -- Standard begin of API savepoint
1705
1706 SAVEPOINT check_entry_method_flags_pvt;
1707
1708 -- dbms_output.put_line('In check_entry_method_flags');
1709
1710 OPEN l_budget_entry_method_csr(p_budget_entry_method_code);
1711 FETCH l_budget_entry_method_csr INTO l_cost_quantity_flag
1712 ,l_raw_cost_flag
1713 ,l_burdened_cost_flag
1714 ,l_rev_quantity_flag;
1715
1716 CLOSE l_budget_entry_method_csr;
1717
1718 -- checking on mandatory flags
1719
1720 IF p_budget_amount_code = 'C' --COST BUDGET
1721 THEN
1722 IF l_cost_quantity_flag = 'N'
1723 AND ( p_quantity <> GMS_BUDGET_PUB.G_PA_MISS_NUM
1724 AND p_quantity IS NOT NULL )
1725 THEN
1726 gms_error_pkg.gms_message( x_err_name => 'GMS_COST_QTY_NOT_ALLOWED',
1727 x_err_code => x_err_code,
1728 x_err_buff => x_err_stage);
1729
1730 APP_EXCEPTION.RAISE_EXCEPTION;
1731 ELSIF l_raw_cost_flag = 'N'
1732 AND ( p_raw_cost <> GMS_BUDGET_PUB.G_PA_MISS_NUM
1733 AND p_raw_cost IS NOT NULL )
1734 THEN
1735 gms_error_pkg.gms_message( x_err_name => 'GMS_RAW_COST_NOT_ALLOWED',
1736 x_err_code => x_err_code,
1737 x_err_buff => x_err_stage);
1738
1739 APP_EXCEPTION.RAISE_EXCEPTION;
1740
1741 ELSIF l_burdened_cost_flag = 'N'
1742 AND ( p_burdened_cost <> GMS_BUDGET_PUB.G_PA_MISS_NUM
1743 AND p_burdened_cost IS NOT NULL )
1744 THEN
1745 gms_error_pkg.gms_message( x_err_name => 'GMS_BURD_COST_NOT_ALLOWED',
1746 x_err_code => x_err_code,
1747 x_err_buff => x_err_stage);
1748
1749 APP_EXCEPTION.RAISE_EXCEPTION;
1750 END IF;
1751
1752 END IF;
1753
1754 EXCEPTION
1755 WHEN OTHERS
1756 THEN
1757
1758 ROLLBACK TO check_entry_method_flags_pvt;
1759 /**
1760 x_err_code := 10;
1761 x_err_stage := 'GMS_CHK_ENTRY_METHOD_FLG_FAIL';
1762 return;
1763 **/
1764
1765 END check_entry_method_flags;
1766
1767 Procedure set_cross_bg_profile is
1768 begin
1769 fnd_profile.put('HR_CROSS_BUSINESS_GROUP', 'N');
1770 end set_cross_bg_profile;
1771
1772 -- --------------------------------------------------------------------------------
1773
1774 --Name: Set_Award_Policy_Context
1775 --Type: Procedure
1776 --
1777 --Description: This procedure is called primarily from the following Budget Workflow packages
1778 -- related procedures:
1779 -- a) Budget Approval Workflow
1780 -- b) Budget Integration workflow
1781 --
1782 -- This procedure does the following:
1783 -- a) Derives org_id from project_id
1784 -- b) Passes org_id to mo_global.set_policy_context
1785 --
1786 --Other Notes:
1787 --
1788 --
1789 --
1790 --
1791 --
1792 --Called subprograms: none
1793 --
1794 --
1795
1796 Procedure Set_Award_Policy_Context
1797 (
1798 p_award_id IN NUMBER
1799 , x_msg_count OUT NOCOPY NUMBER
1800 , x_msg_data OUT NOCOPY VARCHAR2
1801 , x_return_status OUT NOCOPY VARCHAR2
1802 , x_err_code OUT NOCOPY NUMBER
1803 )
1804 IS
1805
1806 l_org_id gms_awards_all.org_id%TYPE := NULL;
1807
1808 Begin
1809 -- Assume Success
1810 x_return_status := FND_API.G_RET_STS_SUCCESS;
1811 x_msg_count := 0;
1812 x_msg_data := NULL;
1813 x_err_code := 0;
1814
1815
1816 -- Fetch Award Org_Id
1817 -- This should NOT fail since it should have been fully validated
1818 -- by the calling object.
1819
1820 SELECT org_id
1821 INTO l_org_id
1822 FROM gms_awards_all
1823 WHERE award_id = p_award_id;
1824
1825 -- Set the Operating Unit Context
1826 mo_global.set_policy_context(p_access_mode => 'S'
1827 , p_org_id => l_org_id );
1828
1829
1830
1831 EXCEPTION
1832 WHEN OTHERS THEN
1833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1834 x_err_code := SQLCODE;
1835 FND_MSG_PUB.Add_Exc_Msg
1836 ( p_pkg_name => 'GMS_BUDGET_UTILS'
1837 , p_procedure_name => 'SET_AWARD_POLICY_cONTEXT'
1838 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
1839 );
1840 FND_MSG_PUB.Count_And_Get
1841 (p_count => x_msg_count ,
1842 p_data => x_msg_data );
1843 RETURN;
1844
1845
1846 END Set_Award_Policy_Context;
1847
1848 -- Bug 5045636 : Created the procedure get_task_number to fetch the task_name for a particular task_id.
1849
1850 FUNCTION get_task_number(P_task_Id IN NUMBER) RETURN VARCHAR2 IS
1851
1852 CURSOR c_task_name IS
1853 SELECT task_name
1854 FROM pa_tasks
1855 WHERE task_id = P_task_Id;
1856
1857 Begin
1858 If p_task_id = g_task_id then
1859 RETURN g_task_number ;
1860 Else
1861 g_task_id := p_task_id;
1862 OPEN c_task_name;
1863 FETCH c_task_name INTO g_task_number;
1864 CLOSE c_task_name;
1865 RETURN g_task_number;
1866 end if;
1867
1868 End GET_TASK_NUMBER;
1869
1870
1871 END gms_budget_utils;