[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_UTILS
Source
1 package body pa_budget_utils as
2 -- $Header: PAXBUBUB.pls 120.11 2007/11/27 06:59:01 vgovvala ship $
3
4 NO_DATA_FOUND_ERR number := 100;
5
6 -- Bug Fix: 4569365. Removed MRC code.
7 -- g_mrc_exception EXCEPTION;
8 Invalid_Arg_Exc EXCEPTION; -- Added for FPM, Tracking Bug No - 3354518.
9
10 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
11 g_module_name VARCHAR2(100) := 'pa.plsql.PA_BUDGET_UTILS';
12
13 procedure get_draft_version_id (x_project_id in number,
14 x_budget_type_code in varchar2,
15 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
16 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
17 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
18 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
19 is
20 old_stack varchar2(630);
21 begin
22
23 x_err_code := 0;
24 old_stack := x_err_stack;
25 x_err_stack := x_err_stack || '->get_draft_version_id';
26
27 x_err_stage := 'get draft budget id <' || to_char(x_project_id)
28 || '><' || x_budget_type_code || '>' ;
29
30
31
32
33 select bv.budget_version_id
34 into x_budget_version_id
35 from pa_budget_versions bv , pa_budget_types bt
36 where bv.project_id = x_project_id
37 and bv.budget_type_code = x_budget_type_code
38 and bv.budget_status_code in ('W', 'S')
39 and bv.budget_type_code = bt.budget_type_code
40 and nvl(bt.plan_type,'BUDGET') = 'BUDGET';
41
42
43
44 x_err_stack := old_stack;
45
46 exception
47 when NO_DATA_FOUND then
48 x_err_code := 10;
49 x_err_stage := 'PA_BU_CORE_NO_VERSION_ID';
50
51 when others then
52 x_err_code := SQLCODE;
53
54 end get_draft_version_id;
55
56 -----------------------------------------------------------------------------
57
58 --Name: Get_Baselined_Version_Id
59 --Type: Procedure
60 --
61 --Description: Gets the baselined budget or plan type identifier for
62 -- the passed inputs.
63 --
64 --
65 --Called subprograms: None.
66 --
67 --
68 --
69 --History:
70 -- XX-XXX-XX who? - Created
71 --
72
73
74 procedure get_baselined_version_id (x_project_id in number,
75 x_budget_type_code in varchar2,
76 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
77 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
78 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
79 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
80 is
81 old_stack varchar2(630);
82 begin
83
84 x_err_code := 0;
85 old_stack := x_err_stack;
86 x_err_stack := x_err_stack || '->get_baselined_version_id';
87
88 x_err_stage := 'get baselined budget id <' || to_char(x_project_id)
89 || '><' || x_budget_type_code || '>' ;
90
91 select bv.budget_version_id
92 into x_budget_version_id
93 from pa_budget_versions bv, pa_budget_types bt
94 where bv.project_id = x_project_id
95 and bv.budget_type_code = x_budget_type_code
96 and bv.current_flag = 'Y'
97 and bv.budget_type_code = bt.budget_type_code
98 and nvl(bt.plan_type,'BUDGET') = 'BUDGET';
99
100
101
102 x_err_stack := old_stack;
103
104 exception
105 when NO_DATA_FOUND then
106 x_err_code := 10;
107 x_err_stage := 'PA_BU_CORE_NO_VERSION_ID';
108
109 when others then
110 x_err_code := SQLCODE;
111
112 end get_baselined_version_id;
113
114 -----------------------------------------------------------------------------
115
116 procedure get_original_version_id (x_project_id in number,
117 x_budget_type_code in varchar2,
118 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
119 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
120 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
121 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
122 is
123 old_stack varchar2(630);
124 begin
125
126 x_err_code := 0;
127 old_stack := x_err_stack;
128 x_err_stack := x_err_stack || '->get_original_version_id';
129
130 x_err_stage := 'get original budget id <' || to_char(x_project_id)
131 || '><' || x_budget_type_code || '>' ;
132
133 select bv.budget_version_id
134 into x_budget_version_id
135 from pa_budget_versions bv, pa_budget_types bt
136 where bv.project_id = x_project_id
137 and bv.budget_type_code = x_budget_type_code
138 and bv.current_original_flag = 'Y'
139 and bv.budget_type_code = bt.budget_type_code
140 and nvl(bt.plan_type,'BUDGET') = 'BUDGET';
141
142 x_err_stack := old_stack;
143
144 exception
145 when NO_DATA_FOUND then
146 x_err_code := 10;
147 x_err_stage := 'PA_BU_CORE_NO_VERSION_ID';
148
149 when others then
150 x_err_code := SQLCODE;
151
152 end get_original_version_id;
153
154 -----------------------------------------------------------------------------
155
156 --Name: get_default_resource_list_id
157 --Type: Procedure
158 --
159 --Description:
160 --
161 --Notes:
162 -- This procedure is only called from the budgets form.
163 --
164 -- !!! This procedure does NOT support the FP model !!!
165 --
166 --
167 --
168 --Called subprograms: pa_budget_utils.get_baselined_version_id
169 --
170 --
171 --
172 --History:
173 -- XX-XXX-XX who? - Created
174 --
175
176
177 procedure get_default_resource_list_id (x_project_id in number,
178 x_budget_type_code in varchar2,
179 x_resource_list_id in out NOCOPY number, --File.Sql.39 bug 4440895
180 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
181 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
182 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
183 is
184 x_budget_amount_code PA_BUDGET_TYPES.BUDGET_AMOUNT_CODE%TYPE;
185 x_allow_budget_entry_flag varchar2(2);
186 x_baselined_version_id number;
187 old_stack varchar2(630);
188
189 begin
190
191 x_err_code := 0;
192 old_stack := x_err_stack;
193 x_err_stack := x_err_stack || '->get_default_resource_list_id';
194
195 -- if a baselined budget exists
196 -- then get the resource_list_id from it else get it from
197 -- pa_project_types
198
199 -- Get the baselined version
200 x_err_stage := 'get baselined budget id <' || to_char(x_project_id)
201 || '><' || x_budget_type_code || '>';
202
203
204
205
206 pa_budget_utils.get_baselined_version_id(
207 x_project_id => x_project_id,
208 x_budget_type_code => x_budget_type_code,
209 x_budget_version_id => x_baselined_version_id,
210 x_err_code => x_err_code,
211 x_err_stage => x_err_stage,
212 x_err_stack => x_err_stack
213 );
214
215
216
217 if (x_err_code = 0) then
218 -- baseliend budget exists, use it to get the resource list
219
220 select resource_list_id
221 into x_resource_list_id
222 from pa_budget_versions
223 where budget_version_id = x_baselined_version_id;
224
225 elsif (x_err_code > 0) then
226
227 -- baseline version does not exist. Get it from pa_project_type
228 x_err_code := 0;
229 x_err_stage := 'get budget amount code <' || x_budget_type_code || '>' ;
230
231 select budget_amount_code
232 into x_budget_amount_code
233 from pa_budget_types
234 where budget_type_code = x_budget_type_code;
235
236 x_err_stage := 'get default resource list id <' || to_char(x_project_id)
237 || '>' ;
238
239 if (x_budget_amount_code = 'C') then
240
241 select t.allow_cost_budget_entry_flag,
242 t.cost_budget_resource_list_id
243 into x_allow_budget_entry_flag,
244 x_resource_list_id
245 from pa_project_types t,
246 pa_projects p
247 where p.project_id = x_project_id
248 and p.project_type = t.project_type;
249
250 else
251
252 select t.allow_rev_budget_entry_flag,
253 t.rev_budget_resource_list_id
254 into x_allow_budget_entry_flag,
255 x_resource_list_id
256 from pa_project_types t,
257 pa_projects p
258 where p.project_id = x_project_id
259 and p.project_type = t.project_type;
260
261 end if;
262
263 if (x_allow_budget_entry_flag = 'N') then
264 x_err_code := 10;
265 x_err_stage := 'PA_BU_ENTRY_NOT_ALLOWED';
266 return;
267 end if;
268
269 if (x_resource_list_id is null) then
270 x_err_code := NO_DATA_FOUND_ERR;
271 x_err_stage := 'PA_BU_NO_DFLT_RESOURCE_LIST';
272 return;
273 end if;
274
275 x_err_stack := old_stack;
276 else
277 -- x_err_code < 0
278 return;
279 end if;
280
281 exception
282 when others then
283 x_err_code := SQLCODE;
284
285 end get_default_resource_list_id;
286
287 -----------------------------------------------------------------------------
288
289 procedure get_default_entry_method_code (x_project_id in number,
290 x_budget_type_code in varchar2,
291 x_budget_entry_method_code in out NOCOPY varchar2, --File.Sql.39 bug 4440895
292 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
293 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
294 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
295 is
296 x_budget_amount_code PA_BUDGET_TYPES.BUDGET_AMOUNT_CODE%TYPE;
297 x_allow_budget_entry_flag varchar2(2);
298 old_stack varchar2(630);
299
300 begin
301
302 x_err_code := 0;
303 old_stack := x_err_stack;
304 x_err_stack := x_err_stack || '->get_default_entry_method_code';
305
306 x_err_stage := 'get budget amount code <' || x_budget_type_code || '>' ;
307
308 select budget_amount_code
309 into x_budget_amount_code
310 from pa_budget_types
311 where budget_type_code = x_budget_type_code;
312
313 x_err_stage := 'get default budget entry method <'
314 || to_char(x_project_id) || '>' ;
315
316 if (x_budget_amount_code = 'C') then
317
318 select t.allow_cost_budget_entry_flag,
319 t.cost_budget_entry_method_code
320 into x_allow_budget_entry_flag,
321 x_budget_entry_method_code
322 from pa_project_types t,
323 pa_projects p
324 where p.project_id = x_project_id
325 and p.project_type = t.project_type;
326
327 else
328
329 select t.allow_rev_budget_entry_flag,
330 t.rev_budget_entry_method_code
331 into x_allow_budget_entry_flag,
332 x_budget_entry_method_code
333 from pa_project_types t,
334 pa_projects p
335 where p.project_id = x_project_id
336 and p.project_type = t.project_type;
337
338 end if;
339
340 if (x_allow_budget_entry_flag = 'N') then
341 x_err_code := 10;
342 x_err_stage := 'PA_BU_ENTRY_NOT_ALLOWED';
343 return;
344 end if;
345
346 if (x_budget_entry_method_code is null) then
347 x_err_code := NO_DATA_FOUND_ERR;
348 x_err_stage := 'PA_BU_NO_DFLT_ENTRY_METHOD';
349 return;
350 end if;
351
352 x_err_stack := old_stack;
353
354 exception
355 when others then
356 x_err_code := SQLCODE;
357
358 end get_default_entry_method_code;
359
360 -----------------------------------------------------------------------------
361
362 function get_budget_type_code (x_budget_type in varchar2)
363 return varchar2
364 is
365 x_budget_type_code varchar2(30);
366 begin
367
368 x_budget_type_code := NULL;
369
370 select budget_type_code
371 into x_budget_type_code
372 from pa_budget_types
373 where budget_type = x_budget_type;
374
375 return x_budget_type_code;
376
377 exception
378 when others then
379 return NULL;
380 end get_budget_type_code;
381
382 -----------------------------------------------------------------------------
383
384 function get_budget_entry_method_code (x_budget_entry_method in varchar2)
385 return varchar2
386 is
387 x_budget_entry_method_code varchar2(30);
388 begin
389
390 x_budget_entry_method_code := NULL;
391
392 select budget_entry_method_code
393 into x_budget_entry_method_code
394 from pa_budget_entry_methods
395 where budget_entry_method = x_budget_entry_method;
396
397 return x_budget_entry_method_code;
398
399 exception
400 when others then
401 return NULL;
402 end get_budget_entry_method_code;
403
404 -----------------------------------------------------------------------------
405
406 function get_change_reason_code (x_meaning in varchar2)
407 return varchar2
408 is
409 x_change_reason_code varchar2(30);
410 begin
411
412 x_change_reason_code := NULL;
413
414 select lookup_code
415 into x_change_reason_code
416 from pa_lookups
417 where lookup_type = 'BUDGET CHANGE REASON'
418 and meaning = x_meaning;
419
420 return x_change_reason_code;
421
422 exception
423 when others then
424 return NULL;
425 end get_change_reason_code;
426
427
428 ------------------------------------------------------------------------------
429
430 -- =================================================
431
432 --Name: check_proj_budget_exists
433 --Type: Function
434 --
435 --Description: This function is called primarily from Billing and Projects Maintenance packages
436 --
437 -- This function has been rewritten to fully support both the r11.5.7 and FP models.
438 --
439 -- New Rules for r11.5.7 and FP Models:
440 --
441 -- For x_budget_status_code = A(ny),
442 -- 1. If both the x_budget_type_code and the p_plan_type_id parameters are passed as NULL,
443 -- then the logic must first query the r11.5.7 model and then the FP model for data.
444 -- As soon as any data is detected, the logic returns control to the calling object.
445 --
446 -- 2. If one of the aforementioned IN-parameters is passed as NON-null and the other as NULL,
447 -- then the logic should only query for NON-null parameter.
448 --
449 -- 3. If both the x_budget_type_code and the p_plan_type_id parameters are passed,
450 -- then the logic must first query the r11.5.7 model and then the FP model for data.
451 -- As soon as any data is detected, the logic returns control to the calling object.
452 --
453 -- 4. If both the p_plan_type_id and the p_version_type IN-parameters are passed with
454 -- NON-null values, then the FP logic must check for the plan_type and version_type.
455 --
456 -- For x_budget_status_code = B(aseline)
457 -- 1. As per design doc, if 'AC' or 'AR' budget types passed as x_budget_type_code
458 -- AND X_FIN_PLAN_TYPE_ID IS NULL,
459 -- THEN
460 -- Use the approved_cost/rev_plan_type_flags to determine if ANY AC/AR baselined budgets
461 -- have been created for r11.5.7 and FP.
462 --
463 --
464 --
465 --
466 --
467 --
468 --
469 --Called subprograms: none
470 --
471 --
472 --
473 --History:
474 -- xx-xxx-xx who? - Created
475 --
476 -- 20-AUG-02 jwhite - Extensively rewrote this procedure to fully support both
477 -- the r11.5.7 and FP models.
478 --
479 --
480 -- 25-OCT-02 jwhite - Bug 2582612
481 -- check_proj_budget_exists procedure. Repositioned
482 -- a RETURN statement.
483 --
484
485 function check_proj_budget_exists (x_project_id in number,
486 x_budget_status_code IN varchar2,
487 x_budget_type_code IN varchar2 default NULL,
488 x_fin_plan_type_id IN NUMBER default NULL,
489 x_version_type IN VARCHAR2 default NULL
490 )
491 return number
492
493 is
494
495 dummy number := 0;
496
497 begin
498
499
500
501 -- Check for Valid Budget_Status_Code ---------------------------
502
503 IF (nvl(x_budget_status_code,'X') NOT IN ('A', 'B') )
504 THEN
505 dummy := 0;
506 RETURN dummy;
507 END IF;
508
509
510 -- Find Any Budget/Plan Type ---------------------------
511
512
513 IF (x_budget_status_code = 'A')
514 THEN
515
516
517 IF (x_budget_type_code is NULL AND x_fin_plan_type_id is NULL)
518 THEN
519
520 -- r11.5.7 Model? --------------
521 BEGIN
522
523 select 1
524 into dummy
525 from dual
526 where exists
527 (select 1
528 from pa_budget_versions bv, pa_budget_types bt
529 where bv.project_id = x_project_id
530 and bv.budget_type_code is NOT NULL -- This must be specified for r11.5.7 Budgets Model
531 and bv.budget_type_code = bt.budget_type_code
532 and nvl(bt.plan_type,'BUDGET') = 'BUDGET');
533
534 EXCEPTION
535 WHEN NO_DATA_FOUND THEN
536 dummy := 0;
537
538 END;
539
540 IF (dummy = 1)
541 THEN
542 RETURN dummy;
543 END IF;
544
545 -- FP Model? --------------
546 BEGIN
547
548 /* Changes for FP.M, Tracking Bug No - 3354518
549 Adding conditon in the where clause below to
550 check for new column use_for_workplan flag.
551 Introducing this check will ensure that the budget
552 version is used for FINPLAN and not WorkPlan.
553 So adding a join to pa_fin_plan_types_b and
554 checking status of use_for_workplan_flag */
555 select 1
556 into dummy
557 from dual
558 where exists
559 (select 1
560 from pa_budget_versions bv
561 where bv.project_id = x_project_id
562 and bv.fin_plan_type_id is NOT NULL -- Specified for FP Model
563 and nvl(bv.wp_version_flag,'N') = 'N' -- (Added for Patchset M,Tracking Bug No - 3354518)
564 );
565
566 EXCEPTION
567 WHEN NO_DATA_FOUND THEN
568 dummy := 0;
569
570 END;
571 RETURN dummy;
572
573 ELSIF (x_budget_type_code is NOT NULL
574 AND x_fin_plan_type_id IS NULL)
575 THEN
576
577 -- r11.5.7 Model?
578 BEGIN
579
580 select 1
581 into dummy
582 from dual
583 where exists
584 (select 1
585 from pa_budget_versions bv, pa_budget_types bt
586 where bv.project_id = x_project_id
587 and bv.budget_type_code = x_budget_type_code
588 and bv.budget_type_code = bt.budget_type_code
589 and nvl(bt.plan_type,'BUDGET') = 'BUDGET');
590
591 EXCEPTION
592 WHEN NO_DATA_FOUND THEN
593 dummy := 0;
594
595 END;
596 RETURN dummy;
597
598 ELSIF (x_fin_plan_type_id is NOT NULL
599 AND x_budget_type_code is NULL)
600 THEN
601
602 -- FP Model?
603 BEGIN
604
605 select 1
606 into dummy
607 from dual
608 where exists
609 (select 1
610 from pa_budget_versions bv
611 where bv.project_id = x_project_id
612 and bv.fin_plan_type_id = x_fin_plan_type_id
613 and bv.version_type = nvl(x_version_type, bv.version_type)
614 );
615
616 EXCEPTION
617 WHEN NO_DATA_FOUND THEN
618 dummy := 0;
619
620 END;
621 RETURN dummy;
622
623 ELSIF (x_budget_type_code is NOT NULL
624 AND x_fin_plan_type_id IS NOT NULL)
625 THEN
626
627 -- r11.5.7 Model?
628 BEGIN
629
630 select 1
631 into dummy
632 from dual
633 where exists
634 (select 1
635 from pa_budget_versions bv, pa_budget_types bt
636 where bv.project_id = x_project_id
637 and bv.budget_type_code = x_budget_type_code
638 and bv.budget_type_code = bt.budget_type_code
639 and nvl(bt.plan_type,'BUDGET') = 'BUDGET');
640
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 dummy := 0;
644
645 END;
646
647 IF (dummy = 1)
648 THEN
649 RETURN dummy;
650 END IF;
651
652 -- FP Model?
653 BEGIN
654
655 select 1
656 into dummy
657 from dual
658 where exists
659 (select 1
660 from pa_budget_versions bv
661 where bv.project_id = x_project_id
662 and bv.fin_plan_type_id = x_fin_plan_type_id
663 and bv.version_type = nvl(x_version_type, bv.version_type)
664 );
665
666 EXCEPTION
667 WHEN NO_DATA_FOUND THEN
668 dummy := 0;
669
670 END;
671 RETURN dummy;
672
673
674 END IF; -- IF (x_budget_type_code is NULL AND x_fin_plan_type_id is NULL)
675
676 END IF; -- if (x_budget_status_code = 'A')
677
678
679
680 -- Find BASELINED Budget/PLan Type ---------------------------
681
682 IF (x_budget_status_code = 'B')
683 THEN
684
685 -- Find a BASELINED r11.5.7 Budget or FP Plan Type -----------
686
687 -- As per design doc, if 'AC' or 'AR' budget types passed as x_budget_type_code
688 -- and x_fin_plan_type_id IS NULL,
689 -- then
690 -- use the approved_cost/rev_plan_type_flags to determine if ANY AC/AR baselined budgets
691 -- have ever been created for either r11.5.7 and/or FP.
692
693
694
695 IF ( NVL(x_budget_type_code,'X') IN ('AC','AR') )
696 THEN
697
698
699 IF (x_fin_plan_type_id IS NULL)
700 THEN
701
702 -- ANY BASELINED AC/AR r11.5.7 Budgets and/or FP Plan Types Exist?
703 BEGIN
704
705 IF (x_budget_type_code = 'AC')
706 THEN
707
708 select 1
709 into dummy
710 from dual
711 where exists
712 (select 1
713 from pa_budget_versions bv
714 where bv.project_id = x_project_id
715 and bv.approved_cost_plan_type_flag = 'Y'
716 and bv.current_flag = 'Y'
717 );
718
719
720 ELSE
721 -- Must be 'AR'
722
723
724 select 1
725 into dummy
726 from dual
727 where exists
728 (select 1
729 from pa_budget_versions bv
730 where bv.project_id = x_project_id
731 and bv.approved_rev_plan_type_flag = 'Y'
732 and bv.current_flag = 'Y'
733 );
734
735
736 END IF; --x_budget_type_code = 'AC'
737
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 dummy := 0;
741
742
743 END;
744 RETURN dummy;
745
746 ELSE
747 -- Any Baselined Approved Cost/Approved Revenue FP PLAN TYPES Exist?
748
749 BEGIN
750
751 IF (x_budget_type_code = 'AC')
752 THEN
753
754 select 1
755 into dummy
756 from dual
757 where exists
758 (select 1
759 from pa_budget_versions bv
760 where bv.project_id = x_project_id
761 and bv.fin_plan_type_id = x_fin_plan_type_id
762 and bv.version_type = nvl(x_version_type, bv.version_type)
763 and bv.approved_cost_plan_type_flag = 'Y'
764 and bV.current_flag = 'Y'
765 );
766
767 ELSE
768 -- Must be 'AR'
769
770 select 1
771 into dummy
772 from dual
773 where exists
774 (select 1
775 from pa_budget_versions bv
776 where bv.project_id = x_project_id
777 and bv.fin_plan_type_id = x_fin_plan_type_id
778 and bv.version_type = nvl(x_version_type, bv.version_type)
779 and bv.approved_rev_plan_type_flag = 'Y'
780 and bV.current_flag = 'Y'
781 );
782
783 END IF;
784
785 EXCEPTION
786 WHEN NO_DATA_FOUND THEN
787 dummy := 0;
788
789 END;
790 RETURN dummy;
791
792
793 END IF; --x_fin_plan_type_id IS NULL
794
795 ELSE
796 -- Budget Type is Something Other Than AC/AR
797
798 IF (x_budget_type_code IS NOT NULL)
799 THEN
800 -- FP usage NOT allowed here. Therefore, FP parameters are ignored.
801
802 BEGIN
803
804 select 1
805 into dummy
806 from dual
807 where exists
808 (select 1
809 from pa_budget_versions bv, pa_budget_types bt
810 where bv.project_id = x_project_id
811 and bv.budget_type_code = x_budget_type_code
812 and bv.budget_type_code = bt.budget_type_code
813 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
814 and bV.current_flag = 'Y'
815 );
816
817 EXCEPTION
818 WHEN NO_DATA_FOUND THEN
819 dummy := 0;
820
821 END;
822 RETURN dummy;
823
824 ELSE
825 -- x_budget_type_code IS NULL. Assume Get FP Model DAta
826
827
828 BEGIN
829
830 select 1
831 into dummy
832 from dual
833 where exists
834 (select 1
835 from pa_budget_versions bv
836 where bv.project_id = x_project_id
837 and bv.fin_plan_type_id = x_fin_plan_type_id
838 and bv.version_type = nvl(x_version_type, bv.version_type)
839 and bV.current_flag = 'Y'
840 );
841
842
843 EXCEPTION
844 WHEN NO_DATA_FOUND THEN
845 dummy := 0;
846
847 END;
848 RETURN dummy;
849
850 END IF; --x_budget_type_code IS NOT NULL, but NOT AC/AR
851
852 END IF; --x_budget_type_code IN ('AC','AR') )
853
854
855 END IF; -- x_budget_status_code = 'B'
856
857
858
859 RETURN dummy;
860
861
862 exception
863 when others then
864 return SQLCODE;
865
866 end check_proj_budget_exists;
867
868 ------------------------------------------------------------------------------
869 --
870 --Name: check_task_budget_exists
871 --Type: Function
872 --
873 --Description: This function is called primarily from Billing and Projects Maintenance packages
874 --
875 -- This function has been rewritten to fully support both the r11.5.7 and FP models.
876 --
877 -- New Rules for r11.5.7 and FP Models:
878 --
879 -- For x_budget_status_code = A(ny),
880 -- 1. If both the x_budget_type_code and the p_plan_type_id parameters are passed as NULL,
881 -- then the logic must first query the r11.5.7 model and then the FP model for data.
882 -- As soon as any data is detected, the logic returns control to the calling object.
883 --
884 -- 2. If one of the aforementioned IN-parameters is passed as NON-null and the other as NULL,
885 -- then the logic should only query for NON-null parameter.
886 --
887 -- 3. If both the x_budget_type_code and the p_plan_type_id parameters are passed,
888 -- then the logic must first query the r11.5.7 model and then the FP model for data.
889 -- As soon as any data is detected, the logic returns control to the calling object.
890 --
891 -- 4. If both the p_plan_type_id and the p_version_type IN-parameters are passed with
892 -- NON-null values, then the FP logic must check for the plan_type and version_type.
893 --
894 -- For x_budget_status_code = B(aseline)
895 -- 1. As per design doc, if 'AC' or 'AR' budget types passed as x_budget_type_code
896 -- AND X_FIN_PLAN_TYPE_ID IS NULL,
897 -- THEN
898 -- Use the approved_cost/rev_plan_type_flags to determine if ANY AC/AR baselined budgets
899 -- have been created for r11.5.7 and FP.
900 --
901 --
902 --
903 --
904 --
905 --
906 --
907 --Called subprograms: none
908 --
909 --
910 --
911 --History:
912 -- xx-xxx-xx who? - Created
913 --
914 -- 20-AUG-02 jwhite - Extensively rewrote this procedure to fully support both
915 -- the r11.5.7 and FP models.
916 --
917 function check_task_budget_exists (x_task_id in number,
918 x_budget_status_code IN varchar2,
919 x_budget_type_code IN varchar2 default NULL,
920 x_fin_plan_type_id IN NUMBER default NULL,
921 x_version_type IN VARCHAR2 default NULL
922 )
923 return number
924 is
925 dummy number := 0;
926 begin
927
928 -- Check for Valid Budget_Status_Code ---------------------------
929
930 IF (nvl(x_budget_status_code,'X') NOT IN ('A', 'B') )
931 THEN
932 dummy := 0;
933 RETURN dummy;
934 END IF;
935
936
937 -- Find Any Budget/Plan Type ---------------------------
938
939 IF (x_budget_status_code = 'A')
940 THEN
941
942
943 IF (x_budget_type_code is NULL AND x_fin_plan_type_id is NULL)
944 THEN
945
946 -- r11.5.7 Model? --------------
947 BEGIN
948
949 select 1
950 into dummy
951 from dual
952 where exists
953 (select 1
954 from pa_budget_versions bv
955 , pa_budget_types bt
956 , pa_resource_assignments a
957 where a.task_id = x_task_id
958 and bv.budget_version_id = a.budget_version_id
959 and bv.budget_type_code is NOT NULL -- This must be specified for r11.5.7 Budgets Model
960 and bv.budget_type_code = bt.budget_type_code
961 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
962 );
963
964 EXCEPTION
965 WHEN NO_DATA_FOUND THEN
966 dummy := 0;
967
968 END;
969
970 IF (dummy = 1)
971 THEN
972 RETURN dummy;
973 END IF;
974
975 -- FP Model? --------------
976 BEGIN
977 /* Changes for FP.M, Tracking Bug No - 3354518
978 Adding conditon in the where clause below to
979 check for new column use_for_workplan flag.
980 Introducing this check will ensure that the budget
981 version is used for FINPLAN and not WorkPlan.
982 So adding a join to pa_fin_plan_types_b and
983 checking status of use_for_workplan_flag */
984
985 select 1
986 into dummy
987 from dual
988 where exists
989 (select 1
990 from pa_budget_versions bv
991 , pa_resource_assignments a
992 where a.task_id = x_task_id
993 and bv.budget_version_id = a.budget_version_id
994 and bv.fin_plan_type_id is NOT NULL -- Specified for FP Model
995 and nvl(bv.wp_version_flag,'N') = 'N' -- (Added for Patchset M,Tracking Bug No - 3354518)
996 );
997
998 EXCEPTION
999 WHEN NO_DATA_FOUND THEN
1000 dummy := 0;
1001
1002 END;
1003 RETURN dummy;
1004
1005
1006 ELSIF (x_budget_type_code is NOT NULL
1007 AND x_fin_plan_type_id IS NULL)
1008 THEN
1009
1010 -- r11.5.7 Model?
1011 BEGIN
1012
1013 select 1
1014 into dummy
1015 from dual
1016 where exists
1017 (select 1
1018 from pa_budget_versions bv
1019 , pa_budget_types bt
1020 , pa_resource_assignments a
1021 where a.task_id = x_task_id
1022 and bv.budget_version_id = a.budget_version_id
1023 and bv.budget_type_code = x_budget_type_code
1024 and bv.budget_type_code = bt.budget_type_code
1025 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
1026 );
1027
1028 EXCEPTION
1029 WHEN NO_DATA_FOUND THEN
1030 dummy := 0;
1031
1032 END;
1033 RETURN dummy;
1034
1035 ELSIF (x_fin_plan_type_id is NOT NULL
1036 AND x_budget_type_code is NULL)
1037 THEN
1038
1039 -- FP Model?
1040 BEGIN
1041
1042 select 1
1043 into dummy
1044 from dual
1045 where exists
1046 (select 1
1047 from pa_budget_versions bv
1048 , pa_resource_assignments a
1049 where a.task_id = x_task_id
1050 and bv.budget_version_id = a.budget_version_id
1051 and bv.fin_plan_type_id = x_fin_plan_type_id
1052 and bv.version_type = nvl(x_version_type, bv.version_type)
1053 );
1054
1055 EXCEPTION
1056 WHEN NO_DATA_FOUND THEN
1057 dummy := 0;
1058
1059 END;
1060 RETURN dummy;
1061
1062 ELSIF (x_budget_type_code is NOT NULL
1063 AND x_fin_plan_type_id IS NOT NULL)
1064 THEN
1065
1066 -- r11.5.7 Model?
1067 BEGIN
1068
1069 select 1
1070 into dummy
1071 from dual
1072 where exists
1073 (select 1
1074 from pa_budget_versions bv
1075 , pa_budget_types bt
1076 , pa_resource_assignments a
1077 where a.task_id = x_task_id
1078 and bv.budget_version_id = a.budget_version_id
1079 and bv.budget_type_code = x_budget_type_code
1080 and bv.budget_type_code = bt.budget_type_code
1081 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
1082 );
1083
1084 EXCEPTION
1085 WHEN NO_DATA_FOUND THEN
1086 dummy := 0;
1087
1088 END;
1089
1090 IF (dummy = 1)
1091 THEN
1092 RETURN dummy;
1093 END IF;
1094
1095 -- FP Model?
1096 BEGIN
1097
1098 select 1
1099 into dummy
1100 from dual
1101 where exists
1102 (select 1
1103 from pa_budget_versions bv
1104 , pa_resource_assignments a
1105 where a.task_id = x_task_id
1106 and bv.budget_version_id = a.budget_version_id
1107 and bv.fin_plan_type_id = x_fin_plan_type_id
1108 and bv.version_type = nvl(x_version_type, bv.version_type)
1109 );
1110
1111 EXCEPTION
1112 WHEN NO_DATA_FOUND THEN
1113 dummy := 0;
1114
1115 END;
1116 RETURN dummy;
1117
1118
1119 END IF; -- IF (x_budget_type_code is NULL AND x_fin_plan_type_id is NULL)
1120
1121 END IF; -- if (x_budget_status_code = 'A')
1122
1123 -- Find BASELINED Budget/PLan Type ---------------------------
1124
1125
1126 IF (x_budget_status_code = 'B')
1127 THEN
1128
1129 -- Find a BASELINED r11.5.7 Budget or FP Plan Type -----------
1130
1131 -- As per design doc, if 'AC' or 'AR' budget types passed as x_budget_type_code
1132 -- and x_fin_plan_type_id IS NULL,
1133 -- then
1134 -- use the approved_cost/rev_plan_type_flags to determine if ANY AC/AR baselined budgets
1135 -- have ever been created for either r11.5.7 and/or FP.
1136
1137
1138 IF ( NVL(x_budget_type_code,'X') IN ('AC','AR') )
1139 THEN
1140
1141 IF (x_fin_plan_type_id IS NULL)
1142 THEN
1143
1144 -- ANY BASELINED AC/AR r11.5.7 Budgets and/or FP Plan Types Exist?
1145 BEGIN
1146
1147 IF (x_budget_type_code = 'AC')
1148 THEN
1149
1150 select 1
1151 into dummy
1152 from dual
1153 where exists
1154 (select 1
1155 from pa_budget_versions bv
1156 , pa_tasks t
1157 , pa_resource_assignments a
1158 where a.budget_version_id = bv.budget_version_id
1159 and a.task_id = t.task_id
1160 and t.top_task_id = x_task_id
1161 and bv.approved_cost_plan_type_flag = 'Y'
1162 and bv.current_flag = 'Y'
1163 );
1164
1165 ELSE
1166 -- Must be 'AR'
1167
1168 select 1
1169 into dummy
1170 from dual
1171 where exists
1172 (select 1
1173 from pa_budget_versions bv
1174 , pa_tasks t
1175 , pa_resource_assignments a
1176 where a.budget_version_id = bv.budget_version_id
1177 and a.task_id = t.task_id
1178 and t.top_task_id = x_task_id
1179 and bv.approved_rev_plan_type_flag = 'Y'
1180 and bv.current_flag = 'Y'
1181 );
1182
1183
1184 END IF; --x_budget_type_code = 'AC'
1185
1186 EXCEPTION
1187 WHEN NO_DATA_FOUND THEN
1188 dummy := 0;
1189
1190 END;
1191 RETURN dummy;
1192
1193 ELSE
1194 -- Any Baselined Approved Cost/Approved Revenue FP PLAN TYPES Exist?
1195
1196 BEGIN
1197
1198 IF (x_budget_type_code = 'AC')
1199 THEN
1200 /* Changes for FP.M, Tracking Bug No - 3354518.
1201 Changing reference of pa_tasks to pa_struct_task_wbs_v below */
1202
1203 /* Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks in
1204 * the following select to avoid full index scan on PA_PROJ_ELEM_VER_SCHEDULE_N1
1205 */
1206 select 1
1207 into dummy
1208 from dual
1209 where exists
1210 (select 1
1211 from pa_budget_versions bv
1212 ,pa_tasks t --Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
1213 -- , pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
1214 ,pa_resource_assignments a
1215 where a.budget_version_id = bv.budget_version_id
1216 and a.task_id = t.task_id
1217 and t.top_task_id = x_task_id
1218 and bv.fin_plan_type_id = x_fin_plan_type_id
1219 and bv.version_type = nvl(x_version_type, bv.version_type)
1220 and bv.approved_cost_plan_type_flag = 'Y'
1221 and bV.current_flag = 'Y'
1222 );
1223
1224
1225 ELSE
1226 -- Must be 'AR'
1227 /* Changes for FP.M, Tracking Bug No - 3354518.
1228 Changing reference of pa_tasks to pa_struct_task_wbs_v below */
1229
1230 /* Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks in
1231 * the following select to avoid full index scan on PA_PROJ_ELEM_VER_SCHEDULE_N1
1232 */
1233 select 1
1234 into dummy
1235 from dual
1236 where exists
1237 (select 1
1238 from pa_budget_versions bv
1239 ,pa_tasks t -- Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
1240 --, pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
1241 ,pa_resource_assignments a
1242 where a.budget_version_id = bv.budget_version_id
1243 and a.task_id = t.task_id
1244 and t.top_task_id = x_task_id
1245 and bv.fin_plan_type_id = x_fin_plan_type_id
1246 and bv.version_type = nvl(x_version_type, bv.version_type)
1247 and bv.approved_rev_plan_type_flag = 'Y'
1248 and bV.current_flag = 'Y'
1249 );
1250
1251 END IF;
1252
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 dummy := 0;
1256
1257 END;
1258 RETURN dummy;
1259
1260
1261 END IF; --x_fin_plan_type_id IS NULL
1262
1263 ELSE
1264 -- Budget Type is Something Other Than AC/AR
1265
1266 IF (x_budget_type_code IS NOT NULL)
1267 THEN
1268 -- FP usage NOT allowed here. Therefore, FP parameters are ignored.
1269
1270 BEGIN
1271
1272 select 1
1273 into dummy
1274 from dual
1275 where exists
1276 (select 1
1277 from pa_budget_versions bv
1278 , pa_budget_types bt
1279 , pa_tasks t
1280 , pa_resource_assignments a
1281 where a.budget_version_id = bv.budget_version_id
1282 and a.task_id = t.task_id
1283 and t.top_task_id = x_task_id
1284 and bv.budget_type_code = x_budget_type_code
1285 and bv.budget_type_code = bt.budget_type_code
1286 and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
1287 and bv.current_flag = 'Y'
1288 );
1289
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN
1292 dummy := 0;
1293
1294 END;
1295 RETURN dummy;
1296
1297
1298 ELSE
1299 -- x_budget_type_code IS NULL. Assume Get FP Model DAta
1300
1301 BEGIN
1302 /* Changes for FP.M, Tracking Bug No - 3354518.
1303 Changing reference of pa_tasks to pa_struct_task_wbs_v below*/
1304
1305 /* Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks in
1306 * the following select to avoid full index scan on PA_PROJ_ELEM_VER_SCHEDULE_N1
1307 */
1308
1309 select 1
1310 into dummy
1311 from dual
1312 where exists
1313 (select 1
1314 from pa_budget_versions bv
1315 ,pa_tasks t -- Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
1316 --, pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
1317 ,pa_resource_assignments a
1318 where a.budget_version_id = bv.budget_version_id
1319 and a.task_id = t.task_id
1320 and t.top_task_id = x_task_id
1321 and bv.fin_plan_type_id = x_fin_plan_type_id
1322 and bv.version_type = nvl(x_version_type, bv.version_type)
1323 and bv.current_flag = 'Y'
1324 );
1325
1326
1327 EXCEPTION
1328 WHEN NO_DATA_FOUND THEN
1329 dummy := 0;
1330
1331 END;
1332 RETURN dummy;
1333
1334 END IF; --x_budget_type_code IS NOT NULL, but NOT AC/AR
1335
1336 END IF; --x_budget_type_code IN ('AC','AR') )
1337
1338
1339 END IF; -- x_budget_status_code = 'B'
1340
1341
1342
1343 RETURN dummy;
1344
1345
1346 exception
1347 when others then
1348 return SQLCODE;
1349
1350
1351
1352
1353
1354 end check_task_budget_exists;
1355
1356 ---------------------------------------------------------------------------
1357 /* Changes for FP.M, Tracking Bug No - 3354518
1358 This API shall only be called for the old resource model so we have to
1359 introduce check in the API such that for the in parameter
1360 resource_list_member_id passed, the migration code should be null.
1361 Other permissible values of migration code is 'N' for New resource model,
1362 and 'M' for migrated.
1363 We include check such that if the migrated_code is not null for the
1364 resource_list_member_id then we raise an Invalid argument exception
1365 (Invalid_Arg_Exc EXCEPTION;).
1366 The exception is handled in the exception block below.
1367
1368 Bug 3586773 Raja May 04 2004
1369 Migrated resource lists can continue to be used via FORMS. So,
1370 modified the validation such that raise error if migration code
1371 is 'N'
1372
1373 */
1374
1375 function check_resource_member_level (x_resource_list_member_id in number,
1376 x_parent_member_id in number,
1377 x_budget_version_id in number,
1378 x_task_id in number)
1379 return number
1380 is
1381 dummy number;
1382 l_migration_code VARCHAR2(1) := NULL;
1383 begin
1384
1385 /* Changes for FPM, Tracking Bug No - 3354518 : Begins */
1386
1387 Select migration_code
1388 into l_migration_code
1389 from pa_resource_list_members
1390 where resource_list_member_id = x_resource_list_member_id;
1391
1392 if nvl(l_migration_code,'-99') = 'N' then
1393 -- Bug 3586773 if l_migration_code is not null then
1394 RAISE Invalid_Arg_Exc;
1395 end if;
1396 /* Changes for FPM, Tracking Bug No - 3354518 : Ends */
1397
1398
1399 if (x_parent_member_id = 0) then
1400
1401 select 1
1402 into dummy
1403 from sys.dual
1404 where exists
1405 (select 1
1406 from pa_resource_list_members m,
1407 pa_resource_assignments a
1408 where m.parent_member_id = x_resource_list_member_id
1409 and m.resource_list_member_id = a.resource_list_member_id
1410 and a.budget_version_id = x_budget_version_id
1411 and a.task_id = x_task_id);
1412
1413 else
1414 select 1
1415 into dummy
1416 from sys.dual
1417 where exists
1418 (select 1
1419 from pa_resource_assignments a
1420 where a.budget_version_id = x_budget_version_id
1421 and a.task_id = x_task_id
1422 and a.resource_list_member_id = x_parent_member_id);
1423
1424 end if;
1425
1426 return 1;
1427
1428 exception
1429 when NO_DATA_FOUND then
1430 return 0;
1431 /* Changes for FPM, Tracking Bug No - 3354518 : Adding
1432 Exception Handling Block for Invalid_Arg_Exc below */
1433 when Invalid_Arg_Exc then
1434 RAISE;
1435 when others then
1436 return SQLCODE;
1437
1438 end check_resource_member_level;
1439
1440 ---------------------------------------------------------------------------
1441
1442 procedure get_proj_budget_amount(
1443 x_project_id in number,
1444 x_budget_type in varchar2,
1445 x_which_version in varchar2,
1446 x_revenue_amount out NOCOPY real, --File.Sql.39 bug 4440895
1447 x_raw_cost out NOCOPY real, --File.Sql.39 bug 4440895
1448 x_burdened_cost out NOCOPY real, --File.Sql.39 bug 4440895
1449 x_labor_quantity out NOCOPY real) IS --File.Sql.39 bug 4440895
1450
1451 budget_status varchar2(30) := NULL;
1452 current_flag varchar2(30) := NULL;
1453 original_flag varchar2(30) := NULL;
1454 raw_cost REAL := 0;
1455 burdened_cost REAL := 0;
1456 labor_qty REAL := 0;
1457 revenue_amount REAL := 0;
1458
1459 BEGIN
1460
1461 if x_which_version = 'DRAFT' then
1462
1463 budget_status := 'O'; -- Non-baselined.
1464
1465 elsif x_which_version = 'CURRENT' then
1466
1467 budget_status := 'B';
1468 current_flag := 'Y';
1469
1470 else -- 'ORIGINAL'
1471
1472 budget_status := 'B';
1473 original_flag := 'Y';
1474
1475 end if;
1476
1477 SELECT nvl(SUM(nvl(b.raw_cost,0)), 0),
1478 nvl(SUM(nvl(b.burdened_cost,0)), 0),
1479 nvl(SUM(nvl(b.labor_quantity,0)), 0),
1480 nvl(SUM(nvl(b.revenue,0)), 0)
1481 INTO raw_cost,
1482 burdened_cost,
1483 labor_qty,
1484 revenue_amount
1485 FROM pa_budget_versions b
1486 WHERE b.project_id = x_project_id
1487 AND b.budget_type_code = x_budget_type
1488 AND b.budget_status_code = decode(budget_status, 'B', 'B',
1489 b.budget_status_code)
1490 AND NOT (budget_status = 'O' and b.budget_status_code = 'B')
1491 AND b.current_flag||'' = nvl(current_flag, b.current_flag)
1492 AND b.current_original_flag =
1493 nvl(original_flag, b.current_original_flag);
1494
1495 x_raw_cost := raw_cost;
1496 x_burdened_cost := burdened_cost;
1497 x_labor_quantity := labor_qty;
1498 x_revenue_amount := revenue_amount;
1499
1500 END get_proj_budget_amount;
1501
1502 ---------------------------------------------------------------------------
1503
1504 -- This procedure is copied from pb_public.get_budget_amount and will
1505 -- be modified later for general use.
1506 procedure get_task_budget_amount(
1507 x_project_id in number,
1508 x_task_id in number,
1509 x_budget_type in varchar2,
1510 x_which_version in varchar2,
1511 x_revenue_amount out NOCOPY real, --File.Sql.39 bug 4440895
1512 x_raw_cost out NOCOPY real, --File.Sql.39 bug 4440895
1513 x_burdened_cost out NOCOPY real, --File.Sql.39 bug 4440895
1514 x_labor_quantity out NOCOPY real) IS --File.Sql.39 bug 4440895
1515
1516 budget_status varchar2(30) := NULL;
1517 current_flag varchar2(30) := NULL;
1518 original_flag varchar2(30) := NULL;
1519 raw_cost REAL := 0;
1520 burdened_cost REAL := 0;
1521 labor_qty REAL := 0;
1522 revenue_amount REAL := 0;
1523
1524 BEGIN
1525
1526 if x_which_version = 'DRAFT' then
1527
1528 budget_status := 'O'; -- Non-baselined.
1529
1530 elsif x_which_version = 'CURRENT' then
1531
1532 budget_status := 'B';
1533 current_flag := 'Y';
1534
1535 else -- 'ORIGINAL'
1536
1537 budget_status := 'B';
1538 original_flag := 'Y';
1539
1540 end if;
1541
1542 SELECT nvl(SUM(nvl(l.raw_cost,0)), 0),
1543 nvl(SUM(nvl(l.burdened_cost,0)), 0),
1544 nvl(SUM(decode(a.track_as_labor_flag,'Y',nvl(l.quantity,0),0)), 0),
1545 nvl(SUM(nvl(l.revenue,0)), 0)
1546 INTO raw_cost,
1547 burdened_cost,
1548 labor_qty,
1549 revenue_amount
1550 FROM pa_budget_lines l,
1551 pa_resource_assignments a,
1552 pa_tasks t,
1553 pa_budget_versions v
1554 WHERE v.project_id = x_project_id
1555 AND v.budget_type_code = x_budget_type
1556 AND v.budget_status_code = decode(budget_status, 'B', 'B',
1557 v.budget_status_code)
1558 and NOT (budget_status = 'O' and v.budget_status_code = 'B')
1559 and v.current_flag||'' = nvl(current_flag, v.current_flag)
1560 and a.budget_version_id = v.budget_version_id
1561 and a.project_id = v.project_id
1562 and t.project_id = x_project_id
1563 and t.task_id = a.task_id
1564 and x_task_id in (t.top_task_id, t.task_id)
1565 and v.current_original_flag =
1566 nvl(original_flag, v.current_original_flag)
1567 AND l.resource_assignment_id = a.resource_assignment_id;
1568
1569 x_raw_cost := raw_cost;
1570 x_burdened_cost := burdened_cost;
1571 x_labor_quantity := labor_qty;
1572 x_revenue_amount := revenue_amount;
1573
1574 END get_task_budget_amount;
1575
1576 ---------------------------------------------------------------------------
1577
1578 procedure delete_draft (x_budget_version_id in number,
1579 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1580 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1581 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
1582 is
1583 old_stack varchar2(630);
1584 x_project_id number;
1585 x_resource_list_assgmt_id number;
1586 x_baselined_version_id number;
1587 x_budget_type_code varchar2(30);
1588 begin
1589
1590
1591 x_err_code := 0;
1592 old_stack := x_err_stack;
1593 x_err_stack := x_err_stack || '->delete_draft';
1594
1595 x_err_stage := 'get budget type <' || to_char(x_budget_version_id)
1596 || '>';
1597 --- This select is unnecessary, therefore commented
1598 /********
1599 select v.project_id,
1600 v.budget_type_code,
1601 la.resource_list_assignment_id
1602 into x_project_id,
1603 x_budget_type_code,
1604 x_resource_list_assgmt_id
1605 from pa_resource_list_assignments la,
1606 pa_budget_versions v
1607 where v.budget_version_id = x_budget_version_id
1608 and v.project_id = la.project_id
1609 and v.resource_list_id = la.resource_list_id;
1610 ********/
1611 /* Never delete resource list assignment if it is baselined
1612 -- if there is a baselined version, then do not delete resource assignment
1613
1614 pa_budget_utils.get_baselined_version_id(x_project_id,
1615 x_budget_type_code,
1616 x_baselined_version_id,
1617 x_err_code,
1618 x_err_stage,
1619 x_err_stack);
1620
1621 if (x_err_code < 0) then
1622 return;
1623 end if;
1624
1625 if (x_err_code > 0) then
1626 -- can not find a baselined version, delete the resource assignment
1627 x_err_code := 0; -- reset value
1628
1629 x_err_stage := 'delete resource assignment <'
1630 || to_char(x_resource_list_assgmt_id) || '><'
1631 || x_budget_type_code || '>';
1632
1633 -- delete resource list assignment
1634 pa_res_list_assignments.delete_rl_uses(x_resource_list_assgmt_id,
1635 x_budget_type_code,
1636 x_err_code,
1637 x_err_stage,
1638 x_err_stack);
1639
1640 if (x_err_code <> 0) then
1641 return;
1642 end if;
1643
1644 end if;
1645 */
1646
1647
1648 -- Delete all budget lines of this budget version
1649 x_err_stage := 'delete budget lines <' || to_char(x_budget_version_id)
1650 || '>';
1651
1652 for bl_rec in (select rowid
1653 from pa_budget_lines
1654 where resource_assignment_id in
1655 (select resource_assignment_id
1656 from pa_resource_assignments
1657 where budget_version_id = x_budget_version_id))
1658 loop
1659 pa_budget_lines_v_pkg.delete_row(x_rowid => bl_rec.rowid);
1660 -- Bug Fix: 4569365. Removed MRC code.
1661 -- x_mrc_flag => 'Y'); /* FPB2: Added x_mrc_flag for MRC changes */
1662
1663 end loop;
1664
1665
1666 -- Delete version
1667 x_err_stage := 'delete budget version <' || to_char(x_budget_version_id)
1668 || '>';
1669
1670 delete pa_budget_versions
1671 where budget_version_id = x_budget_version_id;
1672
1673 fnd_attached_documents2_pkg.delete_attachments('PA_BUDGET_VERSIONS',
1674 x_budget_version_id,
1675 null, null, null, null,
1676 'Y') ;
1677 x_err_stack := old_stack;
1678 exception
1679 when others then
1680 x_err_code := SQLCODE;
1681 return;
1682
1683 end delete_draft;
1684
1685 ------------------------------------------------------------------------------
1686 --Name: Create_Draft
1687 --Type: Procedure
1688 --
1689 --Description:
1690 --
1691 --Notes:
1692 -- For the FP dev effort, the decision was made to provide
1693 -- very limited FP support. Just enough to keep new FP
1694 -- queries from breaking.
1695 --
1696 -- This procedure does NOT create FP plan drafts!
1697 --
1698 -- You must use a PA_FIN_PLAN_PUB api to create_draft plans.
1699 --
1700 --
1701 --
1702 --
1703 --Called subprograms: pa_budget_utils.get_baselined_version_id
1704 --
1705 --
1706 --
1707 --History:
1708 -- XX-XXX-XX who? - Created
1709 --
1710 -- 12-AUG-02 jwhite - Minor modifications for the new FP model:
1711 -- 1) Added new FP columns, approved_cost/rev_plan_type_flags.
1712 --
1713
1714
1715
1716
1717
1718 procedure create_draft (x_project_id in number,
1719 x_budget_type_code in varchar2,
1720 x_version_name in varchar2,
1721 x_description in varchar2,
1722 x_resource_list_id in number,
1723 x_change_reason_code in varchar2,
1724 x_budget_entry_method_code in varchar2,
1725 x_attribute_category in varchar2,
1726 x_attribute1 in varchar2,
1727 x_attribute2 in varchar2,
1728 x_attribute3 in varchar2,
1729 x_attribute4 in varchar2,
1730 x_attribute5 in varchar2,
1731 x_attribute6 in varchar2,
1732 x_attribute7 in varchar2,
1733 x_attribute8 in varchar2,
1734 x_attribute9 in varchar2,
1735 x_attribute10 in varchar2,
1736 x_attribute11 in varchar2,
1737 x_attribute12 in varchar2,
1738 x_attribute13 in varchar2,
1739 x_attribute14 in varchar2,
1740 x_attribute15 in varchar2,
1741 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
1742 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1743 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1744 x_err_stack in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1745 x_pm_product_code in varchar2 default null,
1746 x_pm_budget_reference in varchar2 default null )
1747 is
1748 --
1749 old_draft_version_id number;
1750 old_stack varchar2(630);
1751 x_created_by number;
1752 x_last_update_login number;
1753 x_resource_assignment_id number;
1754 x_baselined_version_id number;
1755 x_baselined_resource_list_id number;
1756 x_resource_list_assgmt_id number;
1757 x_baselined_exists boolean;
1758
1759 begin
1760
1761 x_err_code := 0;
1762 old_stack := x_err_stack;
1763 x_err_stack := x_err_stack || '->create_draft';
1764
1765 IF p_pa_debug_mode = 'Y' THEN
1766 pa_debug.set_err_stack('PA_BUDGET_UTILS.CREATE_DRAFT');
1767 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
1768 END IF;
1769
1770 x_created_by := to_number(fnd_profile.value('USER_ID'));
1771 x_last_update_login := FND_GLOBAL.LOGIN_ID;
1772
1773 -- Get the baselined version
1774 x_err_stage := 'get baselined budget id <' || to_char(x_project_id)
1775 || '><' || x_budget_type_code || '>';
1776
1777 IF P_PA_DEBUG_MODE = 'Y' THEN
1778 pa_debug.g_err_stage:= 'Calling get baselined version id';
1779 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1780 END IF;
1781
1782 pa_budget_utils.get_baselined_version_id(
1783 x_project_id => x_project_id,
1784 x_budget_type_code => x_budget_type_code,
1785 x_budget_version_id => x_baselined_version_id,
1786 x_err_code => x_err_code,
1787 x_err_stage => x_err_stage,
1788 x_err_stack => x_err_stack
1789 );
1790
1791 IF P_PA_DEBUG_MODE = 'Y' THEN
1792 pa_debug.g_err_stage:= 'After get baselined version id';
1793 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1794 pa_debug.g_err_stage:= 'error code - '||x_err_code;
1795 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1796 pa_debug.g_err_stage:= 'error stage - '||x_err_stage;
1797 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1798 END IF;
1799 if (x_err_code > 0) then
1800
1801 -- baseline version does not exist
1802 x_baselined_exists := FALSE;
1803 x_err_code := 0;
1804
1805 elsif (x_err_code = 0) then
1806 -- baseliend budget exists, verify if resource lists are the same
1807 -- resource list used in accumulation
1808
1809 select resource_list_id
1810 into x_baselined_resource_list_id
1811 from pa_budget_versions
1812 where budget_version_id = x_baselined_version_id;
1813
1814 if (x_resource_list_id <> x_baselined_resource_list_id) then
1815 x_err_code := 10;
1816 x_err_stage := 'PA_BU_BASE_RES_LIST_EXISTS';
1817 -- PA_UTILS added for bug 2796670.
1818 PA_UTILS.Add_Message
1819 ( p_app_short_name => 'PA'
1820 , p_msg_name => x_err_stage );
1821 return;
1822 end if;
1823
1824 x_baselined_exists := TRUE;
1825
1826 else
1827 -- x_err_code < 0
1828 IF P_PA_DEBUG_MODE = 'Y' THEN
1829 pa_debug.g_err_stage:= 'Returning';
1830 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1831 END IF;
1832 return;
1833 end if;
1834
1835 IF P_PA_DEBUG_MODE = 'Y' THEN
1836 pa_debug.g_err_stage:= 'Calling get_draft_version_id';
1837 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1838 END IF;
1839
1840 pa_budget_utils.get_draft_version_id(
1841 x_project_id => x_project_id,
1842 x_budget_type_code => x_budget_type_code,
1843 x_budget_version_id => old_draft_version_id,
1844 x_err_code => x_err_code,
1845 x_err_stage => x_err_stage,
1846 x_err_stack => x_err_stack
1847 );
1848 IF P_PA_DEBUG_MODE = 'Y' THEN
1849 pa_debug.g_err_stage:= 'After call to get_draft_version_id';
1850 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1851 pa_debug.g_err_stage:= 'error code - '||x_err_code;
1852 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1853 pa_debug.g_err_stage:= 'error stage - '||x_err_stage;
1854 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1855 END IF;
1856
1857 -- if draft exist, delete it
1858 if (x_err_code = 0) then
1859 IF P_PA_DEBUG_MODE = 'Y' THEN
1860 pa_debug.g_err_stage:= 'Calling delete_draft';
1861 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1862 END IF;
1863 pa_budget_utils.delete_draft(old_draft_version_id,
1864 x_err_code,
1865 x_err_stage,
1866 x_err_stack);
1867 IF P_PA_DEBUG_MODE = 'Y' THEN
1868 pa_debug.g_err_stage:= 'After call to delete_draft';
1869 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1870 pa_debug.g_err_stage:= 'error code - '||x_err_code;
1871 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1872 pa_debug.g_err_stage:= 'error stage - '||x_err_stage;
1873 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1874 END IF;
1875 elsif (x_err_code > 0) then
1876 -- reset x_err_code
1877 x_err_code := 0;
1878
1879 else
1880 -- if oracle error, return
1881 IF P_PA_DEBUG_MODE = 'Y' THEN
1882 pa_debug.g_err_stage:= 'Returning - 1';
1883 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1884 END IF;
1885 return;
1886 end if;
1887
1888 /* Only create resource list assignment at baseline
1889 if (x_baselined_exists = FALSE) then
1890 -- create resource list assignment
1891 pa_res_list_assignments.create_rl_assgmt(x_project_id,
1892 x_resource_list_id,
1893 x_resource_list_assgmt_id,
1894 x_err_code,
1895 x_err_stage,
1896 x_err_stack);
1897
1898 -- if oracle or application error, return
1899 if (x_err_code <> 0) then
1900 return;
1901 end if;
1902
1903 -- create resource list usage
1904 pa_res_list_assignments.create_rl_uses(x_project_id,
1905 x_resource_list_assgmt_id,
1906 x_budget_type_code,
1907 x_err_code,
1908 x_err_stage,
1909 x_err_stack);
1910
1911 -- if oracle or application error, return
1912 if (x_err_code <> 0) then
1913 return;
1914 end if;
1915
1916 end if;
1917 */
1918
1919 -- Included this select to return the newly create budget version id
1920 SELECT pa_budget_versions_s.nextval
1921 INTO x_budget_version_id
1922 FROM dual;
1923 insert into pa_budget_versions(
1924 budget_version_id,
1925 project_id,
1926 budget_type_code,
1927 version_number,
1928 budget_status_code,
1929 last_update_date,
1930 last_updated_by,
1931 creation_date,
1932 created_by,
1933 last_update_login,
1934 current_flag,
1935 original_flag,
1936 current_original_flag,
1937 resource_accumulated_flag,
1938 resource_list_id,
1939 version_name,
1940 budget_entry_method_code,
1941 baselined_by_person_id,
1942 baselined_date,
1943 change_reason_code,
1944 labor_quantity,
1945 labor_unit_of_measure,
1946 raw_cost,
1947 burdened_cost,
1948 revenue,
1949 description,
1950 attribute_category,
1951 attribute1,
1952 attribute2,
1953 attribute3,
1954 attribute4,
1955 attribute5,
1956 attribute6,
1957 attribute7,
1958 attribute8,
1959 attribute9,
1960 attribute10,
1961 attribute11,
1962 attribute12,
1963 attribute13,
1964 attribute14,
1965 attribute15,
1966 pm_product_code,
1967 pm_budget_reference,
1968 wf_status_code,
1969 approved_cost_plan_type_flag,
1970 approved_rev_plan_type_flag
1971 )
1972 select
1973 x_budget_version_id,
1974 x_project_id,
1975 x_budget_type_code,
1976 1,
1977 'W',
1978 SYSDATE,
1979 x_created_by,
1980 SYSDATE,
1981 x_created_by,
1982 x_last_update_login,
1983 'N',
1984 'N',
1985 'N',
1986 'N',
1987 x_resource_list_id,
1988 x_version_name,
1989 x_budget_entry_method_code,
1990 NULL,
1991 NULL,
1992 x_change_reason_code,
1993 NULL,
1994 NULL,
1995 NULL,
1996 NULL,
1997 NULL,
1998 x_description,
1999 x_attribute_category,
2000 x_attribute1,
2001 x_attribute2,
2002 x_attribute3,
2003 x_attribute4,
2004 x_attribute5,
2005 x_attribute6,
2006 x_attribute7,
2007 x_attribute8,
2008 x_attribute9,
2009 x_attribute10,
2010 x_attribute11,
2011 x_attribute12,
2012 x_attribute13,
2013 x_attribute14,
2014 x_attribute15,
2015 x_pm_product_code,
2016 x_pm_budget_reference,
2017 NULL,
2018 decode(x_budget_type_code,'AC','Y','N'),
2019 decode(x_budget_type_code,'AR','Y','N')
2020 from sys.dual;
2021
2022 IF P_PA_DEBUG_MODE = 'Y' THEN
2023 pa_debug.g_err_stage:= 'End of pa_budget create_draft';
2024 pa_debug.write('create_draft: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2025 END IF;
2026
2027 x_err_stack := old_stack;
2028 pa_debug.reset_err_stack;
2029
2030 exception
2031 when others then
2032 x_err_code := SQLCODE;
2033 x_budget_version_id := NULL;
2034 return;
2035
2036 end create_draft;
2037
2038 ------------------------------------------------------------------------------
2039
2040 --Name: Create_Line
2041 --Type: Procedure
2042 --
2043 --Description:
2044 --
2045 --Notes:
2046 -- For the FP dev effort, the decision was made to provide
2047 -- very limited FP support. Just enough to keep new FP
2048 -- queries from breaking.
2049 --
2050 -- This procedure does NOT create FP plan lines!
2051 --
2052 -- You must use a PA_FIN_PLAN_PUB api to create FP plan lines.
2053 --
2054 --
2055 --
2056 --
2057 --Called subprograms:
2058 --
2059 --
2060 --
2061 --History:
2062 -- XX-XXX-XX who? - Created
2063 --
2064 -- 12-AUG-02 jwhite - Modifications for compliance with the new FP model:
2065 -- 1) Added call to Get_Project_Currency_Info. For the first call
2066 -- for a project, this API stores the OUT-parameters in globals
2067 -- to optimize subsequent calls.
2068 -- 2) Added RESOURCE_ASSIGNMENT_TYPE column and defualt value to
2069 -- pa_resource_assignments insert.
2070 -- 3) Added new currency columns to insert SQL.
2071 -- 4) Added exception handing and exception paragraphs for
2072 -- Get_Project_Currency_Info.
2073 --
2074 -- Also, rearranged parameter list as per coding standards.
2075 --
2076 --
2077
2078 procedure create_line (x_budget_version_id in number,
2079 x_project_id in number,
2080 x_task_id in number,
2081 x_resource_list_member_id in number,
2082 x_description in varchar2,
2083 x_start_date in date,
2084 x_end_date in date,
2085 x_period_name in varchar2,
2086 x_quantity in out NOCOPY number, --File.Sql.39 bug 4440895
2087 x_unit_of_measure in varchar2,
2088 x_track_as_labor_flag in varchar2,
2089 x_raw_cost in out NOCOPY number, --File.Sql.39 bug 4440895
2090 x_burdened_cost in out NOCOPY number, --File.Sql.39 bug 4440895
2091 x_revenue in out NOCOPY number, --File.Sql.39 bug 4440895
2092 x_change_reason_code in varchar2,
2093 x_attribute_category in varchar2,
2094 x_attribute1 in varchar2,
2095 x_attribute2 in varchar2,
2096 x_attribute3 in varchar2,
2097 x_attribute4 in varchar2,
2098 x_attribute5 in varchar2,
2099 x_attribute6 in varchar2,
2100 x_attribute7 in varchar2,
2101 x_attribute8 in varchar2,
2102 x_attribute9 in varchar2,
2103 x_attribute10 in varchar2,
2104 x_attribute11 in varchar2,
2105 x_attribute12 in varchar2,
2106 x_attribute13 in varchar2,
2107 x_attribute14 in varchar2,
2108 x_attribute15 in varchar2,
2109 -- Bug Fix: 4569365. Removed MRC code.
2110 -- x_mrc_flag in varchar2, /* FPB2: MRC */
2111 x_pm_product_code in varchar2 default null,
2112 x_pm_budget_line_reference in varchar2 default null,
2113 x_quantity_source varchar2 default 'M',
2114 x_raw_cost_source varchar2 default 'M',
2115 x_burdened_cost_source varchar2 default 'M',
2116 x_revenue_source varchar2 default 'M',
2117 x_resource_assignment_id in out NOCOPY number, --File.Sql.39 bug 4440895
2118 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2119 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2120 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
2121 )
2122 is
2123 old_stack varchar2(630);
2124 x_created_by number;
2125 x_last_update_login number;
2126 v_budget_type_code varchar2(30);
2127
2128 cursor get_budget_type_code is
2129 select budget_type_code
2130 from pa_budget_versions
2131 where budget_version_id = x_budget_version_id;
2132
2133
2134 l_Projfunc_Currency_Code pa_projects_all.projfunc_currency_code%TYPE := NULL;
2135 l_Project_Currency_Code pa_projects_all.project_currency_code%TYPE := NULL;
2136 l_Txn_Currency_Code pa_projects_all.projfunc_currency_code%TYPE := NULL;
2137
2138 l_Return_Status VARCHAR2(1) :=NULL;
2139 l_Msg_Data VARCHAR2(2000) :=NULL;
2140 l_Msg_Count NUMBER := 0;
2141
2142 l_budget_line_id pa_budget_lines.budget_line_id%TYPE; /* FPB2 */
2143
2144
2145
2146 begin
2147
2148
2149 x_err_code := 0;
2150 old_stack := x_err_stack;
2151 x_err_stack := x_err_stack || '->create_line';
2152
2153 IF p_pa_debug_mode = 'Y' THEN
2154 pa_debug.set_err_stack('PA_BUDGET_UTILS.CREATE_LINE');
2155 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
2156 END IF;
2157 -- Bug Fix: 4569365. Removed MRC code.
2158 /* FPB2: MRC */
2159 /*
2160 IF x_mrc_flag IS NULL THEN
2161 l_msg_data := 'x_mrc_flag cannot be null to table handler';
2162 RAISE FND_API.G_EXC_ERROR;
2163 END IF;
2164 */
2165
2166 open get_budget_type_code;
2167 fetch get_budget_type_code into v_budget_type_code;
2168 close get_budget_type_code;
2169
2170 x_created_by := to_number(fnd_profile.value('USER_ID'));
2171 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2172
2173 -- Get the project_totals
2174 x_err_stage := 'verify if resource assignment exists <'
2175 || to_char(x_budget_version_id) || '><'
2176 || to_char(x_project_id) || '><'
2177 || to_char(x_task_id) || '><'
2178 || to_char(x_resource_list_member_id)
2179 || '>';
2180
2181 begin
2182
2183 select resource_assignment_id
2184 into x_resource_assignment_id
2185 from pa_resource_assignments
2186 where budget_version_id = x_budget_version_id
2187 and project_id = x_project_id
2188 and NVL(task_id, 0) = NVL(x_task_id, 0)
2189 and resource_list_member_id = x_resource_list_member_id;
2190
2191 IF P_PA_DEBUG_MODE = 'Y' THEN
2192 pa_debug.g_err_stage:= 'Resource assignment id - '||x_resource_assignment_id;
2193 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2194 END IF;
2195
2196 exception
2197 when NO_DATA_FOUND then
2198 x_err_stage := 'create new resource assignment <'
2199 || to_char(x_budget_version_id) || '><'
2200 || to_char(x_project_id) || '><'
2201 || to_char(x_task_id) || '><'
2202 || to_char(x_resource_list_member_id)
2203 || '>';
2204 IF P_PA_DEBUG_MODE = 'Y' THEN
2205 pa_debug.g_err_stage:= 'No data found';
2206 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2207 END IF;
2208
2209 select pa_resource_assignments_s.nextval
2210 into x_resource_assignment_id
2211 from sys.dual;
2212
2213 IF P_PA_DEBUG_MODE = 'Y' THEN
2214 pa_debug.g_err_stage:= 'Resource assignment id - '||x_resource_assignment_id;
2215 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2216 END IF;
2217
2218 -- create a new resource assignment
2219 insert into pa_resource_assignments
2220 (resource_assignment_id,
2221 budget_version_id,
2222 project_id,
2223 task_id,
2224 resource_list_member_id,
2225 last_update_date,
2226 last_updated_by,
2227 creation_date,
2228 created_by,
2229 last_update_login,
2230 unit_of_measure,
2231 track_as_labor_flag,
2232 project_assignment_id, --added the cloumn for bug 2446041
2233 RESOURCE_ASSIGNMENT_TYPE)
2234 values ( x_resource_assignment_id,
2235 x_budget_version_id,
2236 x_project_id,
2237 x_task_id,
2238 x_resource_list_member_id,
2239 SYSDATE,
2240 x_created_by,
2241 SYSDATE,
2242 x_created_by,
2243 x_last_update_login,
2244 x_unit_of_measure,
2245 x_track_as_labor_flag,
2246 -1, --added the cloumn for bug 2446041
2247 'USER_ENTERED');
2248
2249 when others then
2250 x_err_code := SQLCODE;
2251 IF P_PA_DEBUG_MODE = 'Y' THEN
2252 pa_debug.g_err_stage:= 'When others'||substr(SQLERRM,1,100);
2253 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2254 END IF;
2255 return;
2256 end ;
2257
2258 -- insert into pa_budget_lines
2259 x_err_stage := 'create new budget line <'
2260 || to_char(x_resource_assignment_id) || '><'
2261 || to_char(x_start_date, 'DD-MON-YYYY')
2262 || '>';
2263
2264 -- Fix for Bugs # 475852 and 503183
2265 -- Copy raw cost into burdened cost if budrened cost is null.
2266 -- If the resource UOM is currency and raw cost is null then
2267 -- copy value of quantity amt into raw cost and also set quantity
2268 -- amt to null.
2269
2270 if pa_budget_utils.get_budget_amount_code(v_budget_type_code) = 'C' then
2271 -- Cost Budget
2272
2273 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
2274 if x_raw_cost is null then
2275 x_raw_cost := x_quantity;
2276 end if;
2277 if x_unit_of_measure is not null then --Bug 4432032
2278 x_quantity := null;
2279 end if ;
2280 end if;
2281
2282 if x_burdened_cost is null then
2283 x_burdened_cost := x_raw_cost;
2284 end if;
2285
2286 else -- Revenue Budget
2287 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
2288 if x_revenue is null then
2289 x_revenue := x_quantity;
2290 end if;
2291 if x_unit_of_measure is not null then --Bug 4432032
2292 x_quantity := null;
2293 end if ;
2294 end if;
2295 end if;
2296
2297
2298 -- Get Project Currency Information for INSERT
2299 IF P_PA_DEBUG_MODE = 'Y' THEN
2300 pa_debug.g_err_stage:= 'Calling Get_Project_Currency_Info';
2301 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2302 END IF;
2303 PA_BUDGET_UTILS.Get_Project_Currency_Info
2304 (
2305 p_project_id => x_project_id
2306 , x_projfunc_currency_code => l_projfunc_currency_code
2307 , x_project_currency_code => l_project_currency_code
2308 , x_txn_currency_code => l_txn_currency_code
2309 , x_msg_count => l_msg_count
2310 , x_msg_data => l_msg_data
2311 , x_return_status => l_return_status
2312 );
2313
2314 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
2315 THEN
2316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2317
2318 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
2319 THEN
2320 RAISE FND_API.G_EXC_ERROR;
2321 END IF;
2322
2323 IF P_PA_DEBUG_MODE = 'Y' THEN
2324 pa_debug.g_err_stage:= 'l_return_status is - '||l_return_status;
2325 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2326 END IF;
2327
2328 /* FPB2 */
2329 SELECT pa_budget_lines_s.nextval
2330 INTO l_budget_line_id
2331 FROM DUAL;
2332
2333 IF P_PA_DEBUG_MODE = 'Y' THEN
2334 pa_debug.g_err_stage:= 'l_budget_line_id is - '||l_budget_line_id;
2335 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2336 END IF;
2337
2338 insert into pa_budget_lines
2339 (budget_line_id, /* FPB2 */
2340 budget_version_id, /* FPB2 */
2341 resource_assignment_id,
2342 start_date,
2343 last_update_date,
2344 last_updated_by,
2345 creation_date,
2346 created_by,
2347 last_update_login,
2348 end_date,
2349 period_name,
2350 quantity,
2351 raw_cost,
2352 burdened_cost,
2353 revenue,
2354 change_reason_code,
2355 description,
2356 attribute_category,
2357 attribute1,
2358 attribute2,
2359 attribute3,
2360 attribute4,
2361 attribute5,
2362 attribute6,
2363 attribute7,
2364 attribute8,
2365 attribute9,
2366 attribute10,
2367 attribute11,
2368 attribute12,
2369 attribute13,
2370 attribute14,
2371 attribute15,
2372 pm_product_code,
2373 pm_budget_line_reference,
2374 quantity_source,
2375 raw_cost_source,
2376 burdened_cost_source,
2377 revenue_source,
2378 projfunc_currency_code,
2379 project_currency_code,
2380 txn_currency_code
2381 )
2382 values (
2383 l_budget_line_id, /* FPB2 */
2384 x_budget_version_id, /* FPB2 */
2385 x_resource_assignment_id,
2386 x_start_date,
2387 SYSDATE,
2388 x_created_by,
2389 SYSDATE,
2390 x_created_by,
2391 x_last_update_login,
2392 x_end_date,
2393 x_period_name,
2394 x_quantity,
2395 pa_currency.round_currency_amt(x_raw_cost),
2396 pa_currency.round_currency_amt(x_burdened_cost),
2397 pa_currency.round_currency_amt(x_revenue),
2398 x_change_reason_code,
2399 x_description,
2400 x_attribute_category,
2401 x_attribute1,
2402 x_attribute2,
2403 x_attribute3,
2404 x_attribute4,
2405 x_attribute5,
2406 x_attribute6,
2407 x_attribute7,
2408 x_attribute8,
2409 x_attribute9,
2410 x_attribute10,
2411 x_attribute11,
2412 x_attribute12,
2413 x_attribute13,
2414 x_attribute14,
2415 x_attribute15,
2416 x_pm_product_code,
2417 x_pm_budget_line_reference,
2418 x_quantity_source,
2419 x_raw_cost_source,
2420 x_burdened_cost_source,
2421 x_revenue_source,
2422 l_Projfunc_currency_code,
2423 l_Project_currency_code,
2424 l_txn_currency_code
2425 );
2426 -- Bug Fix: 4569365. Removed MRC code.
2427 /* FPB2: MRC */
2428 /*
2429 IF x_mrc_flag = 'Y' THEN
2430
2431 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
2432
2433 IF P_PA_DEBUG_MODE = 'Y' THEN
2434 pa_debug.g_err_stage:= 'Calling check_mrc_install';
2435 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2436 END IF;
2437 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
2438 (x_return_status => l_return_status,
2439 x_msg_count => l_msg_count,
2440 x_msg_data => l_msg_data);
2441 IF P_PA_DEBUG_MODE = 'Y' THEN
2442 pa_debug.g_err_stage:= 'l_return_status is -'||l_return_status;
2443 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2444 pa_debug.g_err_stage:= 'l_msg_count - '||l_msg_count;
2445 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2446 pa_debug.g_err_stage:= 'l_msg_data - '||l_msg_data;
2447 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2448 END IF;
2449 END IF;
2450
2451 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
2452 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
2453 IF P_PA_DEBUG_MODE = 'Y' THEN
2454 pa_debug.g_err_stage:= 'Calling MAINTAIN_ONE_MC_BUDGET_LINE';
2455 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2456 END IF;
2457 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
2458 (p_budget_line_id => l_budget_line_id,
2459 p_budget_version_id => x_budget_version_id,
2460 p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
2461 x_return_status => l_return_status,
2462 x_msg_count => l_msg_count,
2463 x_msg_data => l_msg_data);
2464 IF P_PA_DEBUG_MODE = 'Y' THEN
2465 pa_debug.g_err_stage:= 'l_return_status is -'||l_return_status;
2466 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2467 pa_debug.g_err_stage:= 'l_msg_count - '||l_msg_count;
2468 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2469 pa_debug.g_err_stage:= 'l_msg_data - '||l_msg_data;
2470 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2471 END IF;
2472 END IF;
2473
2474 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2475 IF P_PA_DEBUG_MODE = 'Y' THEN
2476 pa_debug.g_err_stage:= 'Raising g_mrc_exception';
2477 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2478 END IF;
2479 RAISE g_mrc_exception;
2480 END IF;
2481
2482 END IF;
2483 */
2484
2485 x_err_stack := old_stack;
2486 pa_debug.reset_err_stack;
2487
2488 exception
2489 WHEN FND_API.G_EXC_ERROR
2490 THEN
2491 x_err_code := SQLCODE;
2492 FND_MSG_PUB.Add_Exc_Msg
2493 ( p_pkg_name => 'PA_BUDGET_UTILS'
2494 , p_procedure_name => 'CREATE_LINE'
2495 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2496 );
2497 IF P_PA_DEBUG_MODE = 'Y' THEN
2498 pa_debug.g_err_stage:= 'In exception of create_line -1 '||substr(SQLERRM,1,100);
2499 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL4);
2500 END IF;
2501 RETURN;
2502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2503 THEN
2504 x_err_code := SQLCODE;
2505 IF P_PA_DEBUG_MODE = 'Y' THEN
2506 pa_debug.g_err_stage:= 'In exception of create_line -2 '||substr(SQLERRM,1,100);
2507 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2508 END IF;
2509 FND_MSG_PUB.Add_Exc_Msg
2510 ( p_pkg_name => 'PA_BUDGET_UTILS'
2511 , p_procedure_name => 'CREATE_LINE'
2512 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2513 );
2514 RETURN;
2515 when others then
2516 x_err_code := SQLCODE;
2517 IF P_PA_DEBUG_MODE = 'Y' THEN
2518 pa_debug.g_err_stage:= 'In exception of create_line -3 '||substr(SQLERRM,1,100);
2519 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2520 END IF;
2521 return;
2522
2523 end create_line;
2524
2525 ------------------------------------------------------------------------------
2526
2527 procedure summerize_project_totals (x_budget_version_id in number,
2528 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2529 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2530 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
2531 is
2532 x_created_by number;
2533 x_last_update_login number;
2534 old_stack varchar2(630);
2535 begin
2536
2537 x_err_code := 0;
2538 old_stack := x_err_stack;
2539 x_err_stack := x_err_stack || '->summerize_project_totals';
2540
2541 IF p_pa_debug_mode = 'Y' THEN
2542 pa_debug.set_err_stack('PA_BUDGET_UTILS.SUMMERIZE_PROJECT_TOTALS');
2543 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
2544 END IF;
2545
2546 x_created_by := to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id));
2547 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2548
2549 -- Get the project_totals
2550 x_err_stage := 'get project totals <' || to_char(x_budget_version_id)
2551 || '>';
2552
2553 IF P_PA_DEBUG_MODE = 'Y' THEN
2554 pa_debug.g_err_stage:= 'In summerize_project_amounts';
2555 pa_debug.write('summerize_project_totals: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2556 END IF;
2557
2558 update pa_budget_versions v
2559 set (labor_quantity,
2560 labor_unit_of_measure,
2561 raw_cost,
2562 burdened_cost,
2563 revenue,
2564 last_update_date,
2565 last_updated_by,
2566 last_update_login
2567 )
2568 =
2569 (select sum(nvl(to_number(decode(a.track_as_labor_flag,
2570 'Y', l.quantity, NULL)),0)),
2571 -- decode(a.track_as_labor_flag, 'Y', a.unit_of_measure, NULL),
2572 'HOURS', -- V4 uses HOURS as the only labor unit
2573 pa_currency.round_currency_amt(sum(nvl(l.raw_cost, 0))),
2574 pa_currency.round_currency_amt(sum(nvl(l.burdened_cost, 0))),
2575 pa_currency.round_currency_amt(sum(nvl(l.revenue, 0))),
2576 SYSDATE,
2577 x_created_by,
2578 x_last_update_login
2579 from pa_resource_assignments a,
2580 pa_budget_lines l
2581 where a.budget_version_id = x_budget_version_id /*Bug 4198840: Perf:Included this join*/
2582 and a.budget_version_id = v.budget_version_id
2583 and a.resource_assignment_id = l.resource_assignment_id
2584 )
2585 where budget_version_id = x_budget_version_id;
2586
2587 IF P_PA_DEBUG_MODE = 'Y' THEN
2588 pa_debug.g_err_stage:= 'After update';
2589 pa_debug.write('summerize_project_totals: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2590 END IF;
2591
2592 x_err_stack := old_stack;
2593 pa_debug.reset_err_stack;
2594
2595 exception
2596 when others then
2597 x_err_code := SQLCODE;
2598 IF P_PA_DEBUG_MODE = 'Y' THEN
2599 pa_debug.g_err_stage:= 'In exception of summerize_project_totals';
2600 pa_debug.write('summerize_project_totals: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2601 END IF;
2602 return;
2603
2604 end summerize_project_totals;
2605 -- =================================================
2606
2607 --Name: Verify_Budget_Rules
2608 --Type: Procedure
2609 --
2610 --Description: This procedure is called both from the Oracle Projects
2611 -- Budgets form (PAXBUEBU.fmb) when the Submit
2612 -- and Baseline buttons are pressed and the
2613 -- public Baseline_Budget api.
2614 --
2615 -- This procedure does the following:
2616 -- 1) It performs Oracle Project product specific
2617 -- validations.
2618 -- 2) It calls a client extension for additional
2619 -- client specific validations.
2620 --
2621 -- The procedure also distinguishes between
2622 -- submission edits ('SUBMIT') and
2623 -- baseline edits ('BASELINE') as determined
2624 -- by the value of the p_event parameter.
2625 --
2626 -- Most of the Oracle Project product specific code
2627 -- was copied from the pa_budget_core.baseline
2628 -- procedure. Now, the pa_budget_core.baseline
2629 -- validation calls this procedure.
2630 --
2631 --
2632 --Called subprograms: PA_Client_Extn_Budget.Verify_Budget_Rulesc
2633 --
2634 --
2635 --
2636 --History:
2637 -- 29-JUL-97 jwhite - Created
2638 -- 20-AUG-97 jwhite Added p_calling_module
2639 -- 10-SEP-97 jwhite As per latest specs, added p_warnings_only_flag
2640 -- and p_err_msg_count
2641 -- to Verify_Budget_Rules, and code
2642 -- to support multiple messaging.
2643 -- 15-JUL-99 risingh entry level code for rev budgets should be
2644 -- determined only if it is not P or T already
2645 -- bug 876456 - performance improvement of baseline procedure
2646 --
2647 -- 07-AUG-02 jwhite Adapted logic to suport the r11.5.7 model and new FP model.
2648 --
2649 -- 10-DEC-2003 bvarnasi Bug 3142016 : Selecting 0 if the amount in budget versions
2650 -- is null otherwise, the comparision fails in billing_core.
2651 --
2652 PROCEDURE Verify_Budget_Rules
2653 (p_draft_version_id IN NUMBER
2654 , p_mark_as_original IN VARCHAR2
2655 , p_event IN VARCHAR2
2656 , p_project_id IN NUMBER
2657 , p_budget_type_code IN VARCHAR2
2658 , p_resource_list_id IN NUMBER
2659 , p_project_type_class_code IN VARCHAR2
2660 , p_created_by IN NUMBER
2661 , p_calling_module IN VARCHAR2
2662 , p_fin_plan_type_id IN NUMBER DEFAULT NULL
2663 , p_version_type IN VARCHAR2 DEFAULT NULL
2664 , p_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2665 , p_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2666 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2667 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2668 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2669 )
2670
2671 IS
2672
2673 l_entry_level_code VARCHAR2(30);
2674 l_dummy NUMBER;
2675 l_budget_total NUMBER DEFAULT 0;
2676 l_old_stack VARCHAR2(630);
2677 l_funding_level VARCHAR2(2) DEFAULT NULL;
2678
2679 l_ext_warnings_only_flag VARCHAR2(1) := NULL;
2680 l_ext_err_msg_count NUMBER := 0;
2681
2682
2683 -- For FP Model
2684 l_approved_code pa_budget_types.budget_type_code%TYPE := NULL;
2685 l_Return_Status VARCHAR2(1) :=NULL;
2686 l_Msg_Data VARCHAR2(2000) :=NULL;
2687 l_Msg_Count NUMBER := 0;
2688
2689
2690
2691 BEGIN
2692
2693
2694
2695 -- Initialize OUT-parameters for Multiple Error Messaging
2696
2697 p_warnings_only_flag := 'Y';
2698 p_err_msg_count := 0;
2699
2700
2701 p_err_code := 0;
2702 l_old_stack := p_err_stack;
2703 p_err_stack := p_err_stack || '->check_budget_rules';
2704
2705 IF( PA_UTILS.GetEmpIdFromUser(p_created_by ) IS NULL) THEN
2706 p_err_code := 10;
2707 p_err_stage := 'PA_ALL_WARN_NO_EMPL_REC';
2708
2709 PA_UTILS.Add_Message
2710 ( p_app_short_name => 'PA'
2711 , p_msg_name => p_err_stage
2712 );
2713 p_warnings_only_flag := 'N';
2714
2715 END IF;
2716
2717 -- FP Model Processing, if Any -----------------------
2718
2719 IF (p_budget_type_code IS NULL)
2720 THEN
2721 -- A FP Plan is being processed. Get the l_approved_code for Subsequent Processing
2722
2723 PA_BUDGET_UTILS.Get_Version_Approved_Code
2724 (
2725 p_budget_version_id => p_draft_version_id
2726 , x_approved_code => l_approved_code
2727 , x_msg_count => l_msg_count
2728 , x_msg_data => l_msg_data
2729 , x_return_status => l_return_status
2730 );
2731
2732
2733 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
2734 THEN
2735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2736
2737 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
2738 THEN
2739
2740 RAISE FND_API.G_EXC_ERROR;
2741 END IF;
2742
2743
2744 ELSE
2745 -- A r11.5.7 Budget is being processed.
2746 l_approved_code := p_budget_type_code;
2747
2748 END IF;
2749
2750
2751 -- -----------------------------------------------------
2752
2753 IF (p_event = 'SUBMIT')
2754 THEN
2755 -- Oracle Projects Standard Submission Validation
2756 -- None currently.
2757
2758 NULL;
2759
2760 ELSE
2761
2762 -- Oracle Projects Standard Baseline Validation
2763
2764 p_err_stage := 'get draft budget info <' || to_char(p_draft_version_id)
2765 || '>';
2766
2767
2768 -- check if there is at least one project or task draft budget exists
2769 p_err_stage := 'check budget exists <' || to_char(p_draft_version_id)
2770 || '>';
2771
2772 BEGIN
2773 select 1
2774 into l_dummy
2775 from sys.dual
2776 where exists
2777 (select 1
2778 from pa_resource_assignments
2779 where budget_version_id = p_draft_version_id);
2780
2781 EXCEPTION
2782 WHEN NO_DATA_FOUND THEN
2783 p_err_code := 10;
2784 p_err_stage := 'PA_BU_NO_BUDGET';
2785 PA_UTILS.Add_Message
2786 ( p_app_short_name => 'PA'
2787 , p_msg_name => p_err_stage
2788 );
2789 p_warnings_only_flag := 'N';
2790
2791
2792 WHEN OTHERS THEN
2793 p_err_code := SQLCODE;
2794 FND_MSG_PUB.Add_Exc_Msg
2795 ( p_pkg_name => 'PA_BUDGET_UTILS'
2796 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2797 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2798 );
2799 p_warnings_only_flag := 'N';
2800 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2801 RETURN;
2802 END;
2803
2804 -- do extra check for revenue budget
2805 if ( (l_approved_code IN ('AR','ALL') )
2806 and (p_project_type_class_code = 'CONTRACT')
2807 )
2808 then
2809
2810 -- check the level of budgeting.
2811 -- Note: import budget does not have budget entry method
2812
2813 -- Fix 876456
2814
2815 if( l_entry_level_code not in ('P','T')) then
2816
2817 BEGIN
2818
2819 p_err_stage := 'check budgeting level <'
2820 || to_char(p_draft_version_id) || '>';
2821
2822 select 'T'
2823 into l_entry_level_code
2824 from sys.dual
2825 where exists
2826 (select 1
2827 from pa_resource_assignments
2828 where budget_version_id = p_draft_version_id
2829 -- and task_id is not null);
2830 -- this has been changed since pa_resource_assignments
2831 -- stores 0 if a task_id does not exist rather than null
2832 and task_id <> 0);
2833
2834 EXCEPTION
2835 WHEN NO_DATA_FOUND THEN
2836 -- budget at project level
2837 l_entry_level_code := 'P';
2838 WHEN OTHERS THEN
2839 p_err_code := SQLCODE;
2840 FND_MSG_PUB.Add_Exc_Msg
2841 ( p_pkg_name => 'PA_BUDGET_UTILS'
2842 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2843 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2844 );
2845 p_warnings_only_flag := 'N';
2846 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2847 RETURN;
2848 END;
2849
2850 end if;
2851
2852 -- get the sum of revenue budget for this p_draft_version_id
2853 select nvl(revenue,0) -- Bug 3142016
2854 into l_budget_total
2855 from pa_budget_versions
2856 where budget_version_id = p_draft_version_id;
2857
2858 -- call pa_billing_core.verify_baseline_funding to check the funding revenue
2859 pa_billing_core.verify_baseline_funding(
2860 p_project_id,
2861 p_draft_version_id,
2862 l_entry_level_code,
2863 l_budget_total, -- Removing this temporary fix. /* This is just a temporary fix for FPM testing to proceed. The real fix is being discussed */
2864 p_err_code,
2865 p_err_stage,
2866 p_err_stack);
2867
2868 -- PA_UTILS.Add_Message already addressed internally by Verify_Baseline_Funding
2869 -- Only RETURN if Oracle error. Otherwise, continue processing.
2870
2871 IF (p_err_code <> 0)
2872 THEN
2873 p_warnings_only_flag := 'N';
2874 END IF;
2875 IF (p_err_code < 0) THEN
2876 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2877 RETURN;
2878 END IF;
2879
2880 elsif ( (l_approved_code IN ('AC','ALL') )
2881 and (p_project_type_class_code <> 'CONTRACT')
2882 )
2883 then
2884
2885 NULL;
2886
2887
2888 END IF; -- of AR revenue budget
2889 END IF; -- OP Standard Validations
2890
2891 -- Client Specific Validations --------------------------------------------------
2892
2893 p_err_stage := 'Check Client Extn Verify Budget Rules <' || to_char(p_project_id )
2894 || '><'|| p_budget_type_code
2895 || '>'|| to_char(p_draft_version_id)
2896 || '>'|| p_mark_as_original
2897 || '>';
2898
2899
2900
2901 PA_CLIENT_EXTN_BUDGET.Verify_Budget_Rules
2902 (p_draft_version_id => p_draft_version_id
2903 , p_mark_as_original => p_mark_as_original
2904 , p_event => p_event
2905 , p_project_id => p_project_id
2906 , p_budget_type_code => p_budget_type_code
2907 , p_resource_list_id => p_resource_list_id
2908 , p_project_type_class_code => p_project_type_class_code
2909 , p_created_by => p_created_by
2910 , p_calling_module => p_calling_module
2911 , p_fin_plan_type_id => p_fin_plan_type_id
2912 , p_version_type => p_version_type
2913 , p_warnings_only_flag => l_ext_warnings_only_flag
2914 , p_err_msg_count => l_ext_err_msg_count
2915 , p_error_code => p_err_code
2916 , p_error_message => p_err_stage
2917 );
2918
2919
2920
2921 -- PA_UTILS.Add_Message already addressed internally by client extn
2922 -- Verify_Budget_Rules
2923 -- Only RETURN if Oracle error. Otherwise, continue processing.
2924
2925 IF (l_ext_err_msg_count > 0)
2926 THEN
2927 IF (l_ext_warnings_only_flag = 'N') THEN
2928 p_warnings_only_flag := 'N';
2929 END IF;
2930 END IF;
2931
2932 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2933 p_err_stack := l_old_stack;
2934
2935 EXCEPTION
2936 WHEN FND_API.G_EXC_ERROR
2937 THEN
2938 p_err_code := SQLCODE;
2939 FND_MSG_PUB.Add_Exc_Msg
2940 ( p_pkg_name => 'PA_BUDGET_UTILS'
2941 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2942 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2943 );
2944 p_warnings_only_flag := 'N';
2945 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2946 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2947 THEN
2948 p_err_code := SQLCODE;
2949 FND_MSG_PUB.Add_Exc_Msg
2950 ( p_pkg_name => 'PA_BUDGET_UTILS'
2951 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2952 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2953 );
2954 p_warnings_only_flag := 'N';
2955 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2956 WHEN OTHERS THEN
2957 p_err_code := SQLCODE;
2958 FND_MSG_PUB.Add_Exc_Msg
2959 ( p_pkg_name => 'PA_BUDGET_UTILS'
2960 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2961 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2962 );
2963 p_warnings_only_flag := 'N';
2964 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2965 RETURN;
2966
2967 END Verify_Budget_Rules;
2968
2969
2970
2971 -- =================================================
2972 -- =================================================
2973 --Name: Baseline_Budget
2974 --Type: Procedure
2975 --
2976 --Description: This wrapper procedure is called from the Oracle Projects
2977 -- Budgets form, the Budget Approval and Budget Integration
2978 -- workflows and the AMG Baseline_Budget API.
2979 --
2980 -- This procedure does the following:
2981 -- 1) For Integration budgets,
2982 -- a. performs funds checking and reserves funds if
2983 -- applicable.
2984 -- b. baselines the integration budget
2985 -- c. baselines a corresponding Commitment control
2986 -- budget.
2987 -- d. If successful for both baselines, ties back
2988 -- to the new baselined budget version id.
2989 -- e. If not unsuccessful, rolls back the reserved funds, if any.
2990 -- 2) For other budgets, baselines the budget
2991 --
2992 --
2993 --
2994 --
2995 --Called subprograms: PA_BUDGET_CORE.Baseline
2996 --
2997 --
2998 --
2999 --History:
3000 -- 30-APR-2001 jwhite - Created
3001 --
3002 --
3003 -- 25-JUL-2005 jwhite - R12 SLA Effort
3004 -- Largely rewrote this procedure with regard
3005 -- to Budgetary Control functionality.
3006 --
3007 -- Please see the previous version for obsolete budgetary contol code.
3008 --
3009 -- 23-AUG-2005 jwhite - R12 SLA Effort, Phase II
3010 -- When Budget Integration is sucessful, add Success message
3011 -- to message stack.
3012 -- 29-Aug-2006 nkumbi - Federal Uptake Bug 5522880
3013 -- If federal profile option is enabled, BEM/Third party client extension is called to
3014 -- populate the interface tables after all the baseline and funds check processing is done.
3015 -- The baseline process is also rolled back if the BEM interface fails.
3016 --
3017 --
3018 --
3019
3020 PROCEDURE Baseline_Budget
3021 (p_draft_version_id IN NUMBER
3022 , p_project_id IN NUMBER
3023 , p_mark_as_original IN VARCHAR2
3024 , p_fck_req_flag IN VARCHAR2 DEFAULT NULL
3025 , p_verify_budget_rules IN VARCHAR2 DEFAULT 'N'
3026 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3027 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3028 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3029 )
3030
3031 IS
3032
3033 --
3034 -- Local Variable Declaration
3035
3036 l_err_code NUMBER := 0;
3037 l_err_stage VARCHAR2(120) :=NULL;
3038 l_err_stack VARCHAR2(630) :=NULL;
3039 l_old_stack VARCHAR2(630) :=NULL;
3040
3041 l_Dual_Bdgt_Cntrl_Flag VARCHAR2(1) :=NULL;
3042 l_CC_Budget_Version_id NUMBER := 0;
3043 l_gl_new_base_ver_id NUMBER := 0;
3044 l_cc_new_base_ver_id NUMBER := 0;
3045 l_gl_budget_type_code pa_budget_types.budget_type_code%TYPE :=NULL;
3046 l_cc_budget_type_code pa_budget_types.budget_type_code%TYPE :=NULL;
3047
3048 l_Return_Status VARCHAR2(1) :=NULL;
3049 l_Msg_Data VARCHAR2(2000) :=NULL;
3050 l_Msg_Count NUMBER := 0;
3051
3052 l_Return_Status2 VARCHAR2(1) :=NULL;
3053 l_Msg_Data2 VARCHAR2(2000) :=NULL;
3054 l_Msg_Count2 NUMBER := 0;
3055
3056 l_msg_index_out NUMBER := 0;
3057 l_data VARCHAR2(2000) :=NULL;
3058
3059 --R12 SLA Effort
3060 l_baseline_version_id pa_budget_versions.budget_version_id%TYPE :=NULL;
3061 l_budget_type_code pa_budget_versions.budget_type_code%TYPE :=NULL;
3062
3063 --Federal Uptake Bug 5522880
3064 l_federal_enabled VARCHAR2(1) := NULL;
3065 l_bem_failed EXCEPTION;
3066 l_pre_baseline_version_id NUMBER := NULL;
3067 l_rejection_code VARCHAR2(250) := NULL;
3068 l_interface_status VARCHAR2(10) := NULL;
3069 l_baseline_version_number NUMBER := NULL;
3070 l_rejection_reason VARCHAR2(250) := NULL;
3071
3072
3073 Begin
3074 -- Setup Environment ---------------------------------------------------
3075
3076 -- Assume Success
3077 x_return_status := FND_API.G_RET_STS_SUCCESS;
3078 x_msg_count := 0;
3079 x_msg_data := NULL;
3080
3081 -- Standard begin of API savepoint
3082
3083 SAVEPOINT baseline_budget_wrappper;
3084
3085
3086
3087 -- Integration Processing and Baseline ----------------------------------
3088
3089
3090 IF (nvl(p_fck_req_flag,'N') ) = 'Y'
3091 THEN
3092
3093 -- !!! REQUIRED: Funds Check Processing !!! -------------------
3094
3095 -- BASELINE DRAFT C-O-M-M-I-T-M-E-N-T Version ---------------------------------------
3096
3097 -- R12 SLA Effort: COMMITMENT Budget Baseline DESUPPORTED Until Further Notice
3098
3099 -- When commitment budget support is reinstated, then either a SQL or a procedure
3100 -- call will be required to populate the following parameters:
3101 --
3102 -- 1) l_Dual_Bdgt_Cntrl_Flag
3103 -- 2) l_CC_Budget_Version_id
3104
3105 --Bug 6524116
3106 begin
3107 select 'Y'
3108 into l_dual_bdgt_cntrl_flag
3109 from dual
3110 where exists
3111 (select 1
3112 from pa_budgetary_control_options a
3113 where project_id = p_project_id
3114 and external_budget_code = 'CC'
3115 and bdgt_cntrl_flag = 'Y')
3116 and exists
3117 (select 1
3118 from pa_budgetary_control_options b
3119 where project_id = p_project_id
3120 and external_budget_code = 'GL'
3121 and bdgt_cntrl_flag = 'Y');
3122 exception
3123 when no_data_found then
3124 l_Dual_Bdgt_Cntrl_Flag := 'N';
3125 end;
3126
3127 begin
3128 SELECT budget_version_id
3129 INTO l_CC_Budget_Version_id
3130 FROM pa_budget_versions bv
3131 WHERE project_id = p_project_id
3132 AND BUDGET_STATUS_CODE = 'S'
3133 AND budget_type_code =
3134 (SELECT budget_type_code
3135 FROM pa_budgetary_control_options pbco
3136 WHERE pbco.budget_type_code = bv.budget_type_code
3137 AND pbco.project_id = bv.project_id
3138 AND pbco.bdgt_cntrl_flag = 'Y'
3139 AND pbco.external_budget_code = 'CC');
3140 exception
3141 when no_data_found then
3142 l_CC_Budget_Version_id := 0;
3143 end;
3144 --END Bug 6524116
3145
3146 IF (nvl(l_Dual_Bdgt_Cntrl_Flag,'N') = 'Y'
3147 AND nvl(l_CC_Budget_Version_id,0) > 0 )
3148 THEN
3149 --dbms_output.put_line('-- Baseline Commitment Control draft budget: '||to_char(l_CC_Budget_Version_id) );
3150
3151 PA_BUDGET_CORE.Baseline(x_draft_version_id => l_CC_Budget_Version_id
3152 ,x_mark_as_original => p_mark_as_original
3153 ,x_verify_budget_rules => p_verify_budget_rules
3154 ,x_err_code => l_err_code
3155 ,x_err_stage => l_err_stage
3156 ,x_err_stack => l_err_stack
3157 );
3158
3159 IF (l_err_code <> 0)
3160 THEN
3161
3162 -- Process Baseline Error. Rollback ANY Error
3163 IF (l_err_code < 0)
3164 THEN
3165 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3166 THEN
3167 FND_MSG_PUB.add_exc_msg
3168 ( p_pkg_name => 'PA_BUDGET_UTILS'
3169 , p_procedure_name => 'BASELINE_BUDGET'
3170 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0')
3171 );
3172 END IF;
3173 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3174 ELSE
3175 -- l_err_code > 0
3176 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3177 THEN
3178 FND_MESSAGE.SET_NAME('PA','PA_BASELINE_FAILED');
3179 FND_MSG_PUB.add;
3180 END IF;
3181 l_return_status := FND_API.G_RET_STS_ERROR;
3182 END IF; -- (l_err_code < 0)
3183
3184 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3185 THEN
3186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3187 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3188 THEN
3189 RAISE FND_API.G_EXC_ERROR;
3190 END IF;
3191
3192 END IF; --(nvl(l_Dual_Bdgt_Cntrl_Flag,'N') ) = 'Y'
3193
3194 --R12 SLA Effort: Desupported Until Futher Notice
3195 END IF;--IF (nvl(l_Dual_Bdgt_Cntrl_Flag,'N') = 'Y'
3196
3197 -- BASELINE D-R-A-F-T C-O-S-T Version ---------------------------------------
3198
3199 PA_BUDGET_CORE.Baseline(x_draft_version_id => p_draft_version_id
3200 ,x_mark_as_original => p_mark_as_original
3201 ,x_verify_budget_rules => p_verify_budget_rules
3202 ,x_err_code => l_err_code
3203 ,x_err_stage => l_err_stage
3204 ,x_err_stack => l_err_stack
3205 );
3206
3207
3208 IF (l_err_code <> 0)
3209 THEN
3210
3211 -- Process Baseline Error. Rollback ANY Error
3212 IF (l_err_code < 0)
3213 THEN
3214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3215 THEN
3216 FND_MSG_PUB.add_exc_msg
3217 ( p_pkg_name => 'PA_BUDGET_UTILS'
3218 , p_procedure_name => 'BASELINE_BUDGET'
3219 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0')
3220 );
3221 END IF;
3222 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3223 ELSE
3224 -- l_err_code > 0
3225 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3226 THEN
3227 FND_MESSAGE.SET_NAME('PA','PA_BASELINE_FAILED');
3228 FND_MSG_PUB.add;
3229 END IF;
3230 l_return_status := FND_API.G_RET_STS_ERROR;
3231 END IF; -- (l_err_code < 0)
3232
3233 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3234 THEN
3235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3236 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3237 THEN
3238 RAISE FND_API.G_EXC_ERROR;
3239 END IF;
3240
3241
3242 END IF; -- l_err_code <> 0
3243
3244 -- ------------------------------------------------------------------------
3245
3246
3247 -- RESERVE_BASELINE Budget Funds for NEW B-A-S-E-L-I-N-E Version --------------
3248
3249 -- Get the baselined budget version for the draft
3250
3251 SELECT budget_type_code
3252 INTO l_budget_type_code
3253 FROM pa_budget_versions
3254 WHERE budget_version_id = p_draft_version_id
3255 AND budget_status_code = 'S';
3256
3257 SELECT budget_version_id, version_number
3258 INTo l_baseline_version_id, l_baseline_version_number
3259 FROM pa_budget_versions
3260 WHERE budget_type_code = l_budget_type_code
3261 AND project_id = p_project_id
3262 AND budget_status_code = 'B'
3263 AND current_flag = 'Y';
3264
3265
3266 PA_BUDGET_FUND_PKG.Check_OR_Reserve_Funds
3267 (P_Project_ID => p_project_id
3268 ,P_Budget_Version_Id => l_baseline_version_id
3269 ,P_calling_Mode => 'RESERVE_BASELINE'
3270 ,X_Dual_Bdgt_Cntrl_Flag => l_Dual_Bdgt_Cntrl_Flag
3271 ,X_CC_Budget_Version_id => l_CC_Budget_Version_id
3272 ,X_Return_Status => l_Return_Status
3273 ,X_Msg_Data => l_Msg_Data
3274 ,X_Msg_Count => l_Msg_Count
3275 );
3276
3277 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3278 THEN
3279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3280 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3281 THEN
3282 RAISE FND_API.G_EXC_ERROR;
3283 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS)
3284 THEN
3285 -- R12 SLA Effort, Phase II
3286 -- Add Success message to message stack.
3287 FND_MESSAGE.SET_NAME('PA','PA_NFSUBJ_BU_INTG_SUCCESS');
3288 FND_MSG_PUB.add;
3289 END IF;
3290
3291 /*Start - Changes for Federal Uptake - I */ -- Bug 5522880
3292
3293 l_federal_enabled := NVL(FND_PROFILE.value('FV_ENABLED'), 'N');
3294
3295 If(l_federal_enabled = 'Y' AND l_budget_type_code is NOT NULL) then
3296
3297 Begin
3298 SELECT budget_version_id
3299 INTo l_pre_baseline_version_id
3300 FROM pa_budget_versions pb
3301 WHERE pb.budget_type_code = l_budget_type_code
3302 AND pb.project_id = p_project_id
3303 AND pb.budget_status_code='B'
3304 AND pb.version_number = (l_baseline_version_number - 1);
3305 Exception
3306 When no_data_found then
3307 l_pre_baseline_version_id := NULL;
3308 End;
3309
3310 PA_CLIENT_EXT_FV_BUDGET_INT.INSERT_BUDGET_LINES
3311 (p_project_id => p_project_id
3312 ,p_pre_baselined_version_id => l_pre_baseline_version_id
3313 ,p_baselined_budget_version_id => l_baseline_version_id
3314 ,x_rejection_code => l_rejection_code
3315 ,x_interface_status => l_interface_status);
3316
3317 If ((l_interface_status = 'True' or l_interface_status is NULL) and l_rejection_code is NULL) THEN
3318 NULL; --BEM/Third Party Client Extension Successful
3319 Else
3320 RAISE l_bem_failed;
3321 End if;
3322
3323 End if;
3324 /*End - Changes for Federal Uptake - I */
3325 -- ----------------------------------------------------------------------
3326
3327
3328 ELSE
3329 -- --------------------------------------------------------------------
3330 -- !!! NO funds check !!!
3331 --
3332 -- Perform Vanilla Baseline.
3333 -- --------------------------------------------------------------------
3334 --dbms_output.put_line('NO Funds Check. Call PA_BUDGET_CORE.BASELINE');
3335
3336
3337 PA_BUDGET_CORE.Baseline (x_draft_version_id => p_draft_version_id
3338 ,x_mark_as_original => p_mark_as_original
3339 ,x_verify_budget_rules => p_verify_budget_rules
3340 ,x_err_code => l_err_code
3341 ,x_err_stage => l_err_stage
3342 ,x_err_stack => l_err_stack
3343 );
3344
3345
3346 IF (l_err_code <> 0)
3347 THEN
3348 -- Process Baseline Error. Rollback ANY Error
3349
3350 IF (l_err_code < 0)
3351 THEN
3352 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3353 THEN
3354 FND_MSG_PUB.add_exc_msg
3355 ( p_pkg_name => 'PA_BUDGET_UTILS'
3356 , p_procedure_name => 'BASELINE_BUDGET'
3357 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0')
3358 );
3359 END IF;
3360 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3361 ELSE
3362 -- l_err_code > 0
3363 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3364 THEN
3365 FND_MESSAGE.SET_NAME('PA','PA_BASELINE_FAILED');
3366 FND_MSG_PUB.add;
3367 END IF;
3368 l_return_status := FND_API.G_RET_STS_ERROR;
3369 END IF; -- (l_err_code < 0)
3370
3371 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3372 THEN
3373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3374 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3375 THEN
3376 RAISE FND_API.G_EXC_ERROR;
3377 END IF;
3378
3379 END IF; --(l_err_code <> 0)
3380
3381 /*Start - Changes for Federal Uptake - II */
3382
3383 l_federal_enabled := NVL(FND_PROFILE.value('FV_ENABLED'), 'N');
3384
3385 Select budget_type_code
3386 into l_budget_type_code
3387 from pa_budget_versions
3388 where budget_version_id = p_draft_version_id;
3389
3390
3391 If(l_federal_enabled = 'Y' AND l_budget_type_code is NOT NULL) then
3392
3393 SELECT budget_version_id, version_number
3394 INTo l_baseline_version_id, l_baseline_version_number
3395 FROM pa_budget_versions
3396 WHERE budget_type_code = l_budget_type_code
3397 AND project_id = p_project_id
3398 AND budget_status_code='B'
3399 AND current_flag = 'Y';
3400
3401 Begin
3402 SELECT budget_version_id
3403 INTo l_pre_baseline_version_id
3404 FROM pa_budget_versions pb
3405 WHERE pb.budget_type_code = l_budget_type_code
3406 AND pb.project_id = p_project_id
3407 AND pb.budget_status_code = 'B'
3408 AND pb.version_number = (l_baseline_version_number - 1);
3409 Exception
3410 When no_data_found then
3411 l_pre_baseline_version_id := NULL;
3412 End;
3413
3414
3415 PA_CLIENT_EXT_FV_BUDGET_INT.INSERT_BUDGET_LINES
3416 (p_project_id => p_project_id
3417 ,p_pre_baselined_version_id => l_pre_baseline_version_id
3418 ,p_baselined_budget_version_id => l_baseline_version_id
3419 ,x_rejection_code => l_rejection_code
3420 ,x_interface_status => l_interface_status) ;
3421
3422 If ((l_interface_status = 'True' or l_interface_status is NULL) and l_rejection_code is NULL) THEN
3423 NULL; --BEM/Third Party Client Extension Successful
3424 Else
3425 RAISE l_bem_failed;
3426 End if;
3427
3428 End if;
3429
3430 /*End - Changes for Federal Uptake - II */
3431
3432 END IF; --(nvl(p_fck_req_flag,'N')) = 'Y'
3433
3434
3435
3436 EXCEPTION
3437 WHEN FND_API.G_EXC_ERROR
3438 THEN
3439 ROLLBACK TO baseline_budget_wrappper;
3440 x_return_status := FND_API.G_RET_STS_ERROR;
3441 --fix done for Bug 6408021
3442 FND_MSG_PUB.Count_And_Get
3443 (p_count => x_msg_count ,
3444 p_data => x_msg_data );
3445
3446
3447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3448 THEN
3449 ROLLBACK TO baseline_budget_wrappper;
3450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3451 --fix done for Bug 6408021
3452 FND_MSG_PUB.Count_And_Get
3453 (p_count => x_msg_count ,
3454 p_data => x_msg_data );
3455
3456 WHEN L_BEM_FAILED
3457 THEN
3458 ROLLBACK TO baseline_budget_wrappper;
3459 x_return_status := FND_API.G_RET_STS_ERROR;
3460 x_msg_count := 1;
3461 x_msg_data := 'PA_FV_BUDGET_INT_FAILED';
3462 Begin
3463 Select meaning
3464 into l_rejection_reason
3465 from pa_lookups
3466 where lookup_code = l_rejection_code
3467 and lookup_type = 'PA_BUD_INTERFACE_REJ_CODE';
3468 Exception
3469 When no_data_found then
3470 l_rejection_reason := Null;
3471 End;
3472 PA_UTILS.Add_Message('PA',x_msg_data, 'Rejection Reason', l_rejection_reason);
3473
3474 WHEN OTHERS
3475 THEN
3476 ROLLBACK TO baseline_budget_wrappper;
3477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3478 FND_MSG_PUB.Add_Exc_Msg
3479 ( p_pkg_name => 'PA_BUDGET_UTILS'
3480 , p_procedure_name => 'BASELINE_BUDGET'
3481 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
3482 );
3483 FND_MSG_PUB.Count_And_Get
3484 (p_count => x_msg_count
3485 , p_data => x_msg_data );
3486
3487
3488
3489
3490 END Baseline_Budget;
3491 -- =================================================
3492
3493 ---------------------------------------------------------------------------
3494 --
3495
3496 --History:
3497 -- xx-xxx-xx who? - Created
3498 --
3499 -- 13-AUG-02 jwhite - Modified for FP model:
3500 -- Added filter to pa_resource_assignments,
3501 -- RESOURCE_ASSIGNMENT_TYPE = USER_ENTERED
3502 --
3503 -- 10-Feb-05 dbora Bug 4176059: Performance Fix: FP.M-B12
3504 -- Split cursor get_totals in to four separate
3505 -- cursors for each separate planning levels
3506
3507 procedure get_project_task_totals(x_budget_version_id in number,
3508 x_task_id in number,
3509 x_quantity_total in out NOCOPY number, --File.Sql.39 bug 4440895
3510 x_raw_cost_total in out NOCOPY number, --File.Sql.39 bug 4440895
3511 x_burdened_cost_total in out NOCOPY number, --File.Sql.39 bug 4440895
3512 x_revenue_total in out NOCOPY number, --File.Sql.39 bug 4440895
3513 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3514 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3515 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
3516 is
3517
3518 /****************************************************************
3519 How to use this API:
3520 This API can be used to get the totals at the Project Level
3521 or at the task level. If x_task_id is passed as a null value then
3522 project level totals are fetched. Otherwise task level totals are
3523 fetched. For task level totals, first the task level is determined.
3524 If the task level is top or intermediate level , then the amounts
3525 are rolled from the child tasks.
3526 ******************************************************************/
3527
3528 v_rollup_flag varchar2(1);
3529 old_stack varchar2(630);
3530
3531 cursor get_rollup_level is
3532 select 'P'
3533 from dual
3534 where x_task_id is null
3535 union
3536 select 'T'
3537 from pa_tasks
3538 where x_task_id is not null
3539 and task_id = x_task_id
3540 and parent_task_id is null
3541 union
3542 select 'M'
3543 from pa_tasks
3544 where x_task_id is not null
3545 and task_id = x_task_id
3546 and parent_task_id is not null
3547 and exists (select 'X'
3548 from pa_tasks
3549 where parent_task_id = x_task_id)
3550 union
3551 select 'L'
3552 from dual
3553 where x_task_id is not null
3554 and not exists (select 'X'
3555 from pa_tasks
3556 where parent_task_id = x_task_id);
3557
3558 --Bug 4176059: Performance Fix: FP.M-B12
3559 cursor get_project_totals is
3560 select labor_quantity,
3561 raw_cost,
3562 burdened_cost,
3563 revenue
3564 from pa_budget_versions
3565 where v_rollup_flag = 'P' -- Project Level
3566 and budget_version_id = x_budget_version_id;
3567
3568 --Bug 4176059: Performance Fix: FP.M-B12
3569 cursor get_top_task_totals is
3570 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
3571 SUM(NVL(l.RAW_COST,0)),
3572 SUM(NVL(l.BURDENED_COST,0)),
3573 SUM(NVL(l.REVENUE,0))
3574 from pa_tasks t,
3575 pa_budget_lines l ,
3576 pa_resource_assignments a
3577 where v_rollup_flag = 'T' -- Top Task Level
3578 and a.budget_version_id = x_budget_version_id
3579 and a.task_id = t.task_id
3580 and t.top_task_id = x_task_id
3581 and a.resource_assignment_id = l.resource_assignment_id
3582 and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
3583
3584 --Bug 4176059: Performance Fix: FP.M-B12
3585 cursor get_mid_task_totals is
3586 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
3587 SUM(NVL(l.RAW_COST,0)),
3588 SUM(NVL(l.BURDENED_COST,0)),
3589 SUM(NVL(l.REVENUE,0))
3590 from pa_budget_lines l,
3591 pa_resource_assignments a
3592 where v_rollup_flag = 'M' -- Middle Task Level
3593 and a.budget_version_id = x_budget_version_id
3594 and a.task_id in (select task_id
3595 from pa_tasks
3596 start with task_id = x_task_id
3597 connect by prior task_id = parent_task_id)
3598 and a.resource_assignment_id = l.resource_assignment_id
3599 and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
3600
3601 --Bug 4176059: Performance Fix: FP.M-B12
3602 cursor get_lowest_task_totals is
3603 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
3604 SUM(NVL(l.RAW_COST,0)),
3605 SUM(NVL(l.BURDENED_COST,0)),
3606 SUM(NVL(l.REVENUE,0))
3607 from pa_budget_lines l,
3608 pa_resource_assignments a
3609 where v_rollup_flag = 'L' -- Lowest Task Level
3610 and a.budget_version_id = x_budget_version_id
3611 and a.task_id = x_task_id
3612 and a.resource_assignment_id = l.resource_assignment_id
3613 and NVL(a.RESOURCE_ASSIGNMENT_TYPE, 'USER_ENTERED') = 'USER_ENTERED';
3614
3615 begin
3616 x_err_code := 0;
3617 old_stack := x_err_stack;
3618 x_err_stack := x_err_stack || '->PA_BUDGET_UTILS.get_project_task_totals';
3619
3620 open get_rollup_level;
3621 fetch get_rollup_level into v_rollup_flag;
3622 close get_rollup_level;
3623
3624 x_err_stage := x_raw_cost_total;
3625
3626 /* Bug 4176059: Performance Fix: FP.M-B12--- restructured the following code block
3627 * to open any appropriate cursor to get the totals depending upon the planning level
3628 */
3629
3630 -- if x_task_id is not passed, open the project level cursor
3631 if x_task_id is null then
3632 -- opening the project level cursor
3633 open get_project_totals;
3634
3635 fetch get_project_totals
3636 into x_quantity_total,
3637 x_raw_cost_total,
3638 x_burdened_cost_total,
3639 x_revenue_total;
3640
3641 close get_project_totals;
3642 else -- task id is passed
3643 if v_rollup_flag = 'T' then
3644 -- top task level planning
3645 open get_top_task_totals;
3646
3647 fetch get_top_task_totals
3648 into x_quantity_total,
3649 x_raw_cost_total,
3650 x_burdened_cost_total,
3651 x_revenue_total;
3652
3653 close get_top_task_totals;
3654 elsif v_rollup_flag = 'M' then
3655 -- middle task level planning
3656 open get_mid_task_totals;
3657
3658 fetch get_mid_task_totals
3659 into x_quantity_total,
3660 x_raw_cost_total,
3661 x_burdened_cost_total,
3662 x_revenue_total;
3663
3664 close get_mid_task_totals;
3665 elsif v_rollup_flag = 'L' then
3666 -- lowest task level planning
3667 open get_lowest_task_totals;
3668
3669 fetch get_lowest_task_totals
3670 into x_quantity_total,
3671 x_raw_cost_total,
3672 x_burdened_cost_total,
3673 x_revenue_total;
3674
3675 close get_lowest_task_totals;
3676 end if; -- v_rollup_flag
3677 end if; -- x_task_id null
3678
3679 x_err_stack := old_stack;
3680
3681 exception
3682 when others then
3683 x_err_code := SQLCODE;
3684 return;
3685 end;
3686
3687 ---------------------------------------------------------------------------
3688 --
3689 -- This function returns a value 'Y' if the UOM passed
3690 -- is a currency UOM. Otherwise it returns 'N'.
3691 --
3692 Function Check_Currency_Uom (x_uom_code in varchar2)
3693 return varchar2 is
3694 cursor check_uom is
3695 select currency_uom_flag
3696 from pa_currency_uom_v
3697 where uom_code = x_uom_code;
3698
3699 v_currency_uom_flag varchar2(1);
3700
3701 Begin
3702 open check_uom;
3703 fetch check_uom into v_currency_uom_flag;
3704
3705 if check_uom%notfound then
3706 close check_uom; --Bug 5350429
3707 return 'Y';
3708 else
3709 close check_uom; --Bug 5350429
3710 return nvl(v_currency_uom_flag,'Y');
3711 end if;
3712
3713 End;
3714
3715 ---------------------------------------------------------------------------
3716 --
3717 -- This function returns the value of budget amount code
3718 -- associated with the budget type. Budget Amount Code
3719 -- determines whethere its a cost or a revenue budget.
3720 --
3721 Function get_budget_amount_code (x_budget_type_code in varchar2)
3722 return varchar2 is
3723 cursor get_budget_amount_code is
3724 select budget_amount_code
3725 from pa_budget_types
3726 where budget_type_code = x_budget_type_code;
3727
3728 v_budget_amount_code PA_BUDGET_TYPES.BUDGET_AMOUNT_CODE%TYPE;
3729
3730 Begin
3731 open get_budget_amount_code ;
3732 fetch get_budget_amount_code into v_budget_amount_code;
3733 close get_budget_amount_code;
3734
3735 return v_budget_amount_code;
3736 End;
3737
3738 ---------------------------------------------------------------------------
3739
3740 -- Assigning the value of Budget Entry Level Code to a global
3741 -- variable.
3742 Procedure set_entry_level_code(x_entry_level_code in varchar2) is
3743 Begin
3744 g_entry_level_code := x_entry_level_code;
3745 End;
3746
3747 ---------------------------------------------------------------------------
3748 -- Returning the value of global variable for Budget Entry Level Code
3749 Function get_entry_level_code return varchar2 is
3750 Begin
3751 return g_entry_level_code;
3752 End;
3753
3754
3755 -- =================================================
3756
3757 --Name: Get_Version_Approved_Code
3758 --Type: Procedure
3759 --
3760 --Description: This procedure is called both from this package and other
3761 -- packages.
3762 --
3763 -- This procedure returns the following:
3764 -- 1) For the r11.5.7 model:
3765 -- AC (Approved Cost)
3766 -- AR (Approved Revenue)
3767 -- NONE (Neither Approved Cost nor Approved Revenue)
3768 -- 2) For FP versions,
3769 -- AC (Approved Cost)
3770 -- AR (Approved Revenue)
3771 -- ALL (both Approved Cost and Revenue)
3772 -- NONE (Neither Approved Cost nor Approved Revenue)
3773 --
3774 --
3775 --Called subprograms: none
3776 --
3777 --
3778 --
3779 --History:
3780 -- 07-AUG-02 jwhite - Created
3781 --
3782
3783 PROCEDURE Get_Version_Approved_Code
3784 (
3785 p_budget_version_id IN NUMBER
3786 , x_approved_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3787 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3788 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3789 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3790 )
3791 IS
3792
3793 l_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE := NULL;
3794 l_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE := NULL;
3795
3796
3797 BEGIN
3798
3799 -- Assume Success
3800 x_return_status := FND_API.G_RET_STS_SUCCESS;
3801 x_msg_count := 0;
3802 x_msg_data := NULL;
3803
3804
3805 SELECT b.approved_cost_plan_type_flag, b.approved_rev_plan_type_flag
3806 INTO l_cost_flag, l_rev_flag
3807 FROM pa_budget_versions b
3808 WHERE b.budget_version_id = p_budget_version_id;
3809
3810
3811 IF ( nvl(l_cost_flag,'N') = 'Y'
3812 AND nvl(l_rev_flag,'N') = 'Y'
3813 )
3814 THEN
3815 x_approved_code := 'ALL';
3816
3817 ELSIF ( nvl(l_cost_flag,'N') = 'Y'
3818 AND nvl(l_rev_flag,'N') = 'N'
3819 )
3820 THEN
3821 x_approved_code := 'AC';
3822
3823 ELSIF ( nvl(l_cost_flag,'N') = 'N'
3824 AND nvl(l_rev_flag,'N') = 'Y'
3825 )
3826 THEN
3827 x_approved_code := 'AR';
3828 ELSE
3829 x_approved_code := 'NONE';
3830
3831 END IF;
3832
3833
3834 EXCEPTION
3835 WHEN OTHERS
3836 THEN
3837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3838 FND_MSG_PUB.Add_Exc_Msg
3839 ( p_pkg_name => 'PA_BUDGET_UTILS'
3840 , p_procedure_name => 'GET_VERSION_APPROVED_CODE'
3841 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
3842 );
3843 FND_MSG_PUB.Count_And_Get
3844 (p_count => x_msg_count ,
3845 p_data => x_msg_data );
3846 RETURN;
3847
3848
3849 END Get_Version_Approved_Code;
3850
3851
3852 -- =================================================
3853
3854 --Name: Get_Project_Currency_Info
3855 --Type: Procedure
3856 --
3857 --Description: This procedure is called both from this package and other
3858 -- packages.
3859 --
3860 -- This procedure may be called multiple times for a given
3861 -- project. For optimal performance, this procedure stores the
3862 -- selected values into package globals. When the G_Project_Id global
3863 -- differs from the p_project_id IN-parameter, this API does a fetch for
3864 -- the new project_id.
3865 --
3866 -- The G_Project_Id global is defaulted to "-1" in the package specification.
3867 --
3868 --
3869 --
3870 --
3871 --Called subprograms: none
3872 --
3873 --
3874 --
3875 --History:
3876 -- 14-AUG-02 jwhite - Created
3877 --
3878
3879 PROCEDURE Get_Project_Currency_Info
3880 (
3881 p_project_id IN NUMBER
3882 , x_projfunc_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3883 , x_project_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3884 , x_txn_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3885 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3886 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3887 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3888 )
3889
3890 IS
3891
3892
3893 BEGIN
3894
3895
3896 -- Assume Success
3897 x_return_status := FND_API.G_RET_STS_SUCCESS;
3898 x_msg_count := 0;
3899 x_msg_data := NULL;
3900
3901
3902 -- Fetch Currency Info for New Project
3903
3904 IF (pa_budget_utils.G_project_id <> p_project_id)
3905 THEN
3906
3907 SELECT projfunc_currency_code
3908 , project_currency_code
3909 , projfunc_currency_code
3910 INTO pa_budget_utils.G_projfunc_currency_code
3911 , pa_budget_utils.G_project_currency_code
3912 , pa_budget_utils.G_txn_currency_code
3913 FROM pa_projects_all
3914 WHERE project_id = p_project_id;
3915
3916 -- Save P_project_id to Skip this Fetch for Subsequent Calls
3917 pa_budget_utils.G_project_id := p_project_id;
3918
3919 END IF;
3920
3921 x_projfunc_currency_code := pa_budget_utils.G_projfunc_currency_code;
3922 x_project_currency_code := pa_budget_utils.G_project_currency_code;
3923 x_txn_currency_code := pa_budget_utils.G_txn_currency_code;
3924
3925
3926 EXCEPTION
3927 WHEN OTHERS
3928 THEN
3929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3930 FND_MSG_PUB.Add_Exc_Msg
3931 ( p_pkg_name => 'PA_BUDGET_UTILS'
3932 , p_procedure_name => 'GET_PROJECT_CURRENCY_INFO'
3933 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
3934 );
3935 FND_MSG_PUB.Count_And_Get
3936 (p_count => x_msg_count ,
3937 p_data => x_msg_data );
3938 RETURN;
3939
3940
3941
3942 END Get_Project_Currency_Info;
3943
3944
3945 -- =================================================
3946
3947 --Name: Get_Approved_FP_Info
3948 --Type: Procedure
3949 --
3950 --Description: This procedure is called primarily from Billing packages.
3951 --
3952 -- This procedure is used to determine whether the project is
3953 -- using the new FP model or using the r11.5.7 Budgets model.
3954 --
3955 -- If using the r11.5.7 Budgets model, the functional OUT-parameters
3956 -- are returned as NULL.
3957 --
3958 --
3959 --
3960 --
3961 --
3962 --Called subprograms: none
3963 --
3964 --
3965 --
3966 --History:
3967 -- 19-AUG-02 jwhite - Created
3968 --
3969
3970 Procedure Get_Approved_FP_Info
3971 (
3972 p_project_id IN NUMBER
3973 , x_ac_plan_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3974 , x_ar_plan_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3975 , x_ac_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3976 , x_ar_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3977 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3978 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3979 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3980 )
3981
3982 IS
3983
3984
3985 l_ac_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE := NULL;
3986 l_ar_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE := NULL;
3987 l_ac_version_type pa_budget_versions.version_type%TYPE := NULL;
3988 l_ar_version_type pa_budget_versions.version_type%TYPE := NULL;
3989
3990 l_dummy VARCHAR2(1) := 'N';
3991
3992
3993
3994 BEGIN
3995
3996
3997 -- Assume Success
3998 x_return_status := FND_API.G_RET_STS_SUCCESS;
3999 x_msg_count := 0;
4000 x_msg_data := NULL;
4001
4002
4003
4004 -- Check if r11.5.7 AC/AR Budget Versions Still Exist. If yes, then
4005 -- default r11.5.7 Budgets model.
4006
4007 BEGIN
4008
4009 SELECT 'Y'
4010 INTO l_dummy
4011 FROM dual
4012 WHERE EXISTS (select '1'
4013 from pa_budget_versions v
4014 where v.project_id = p_project_id
4015 and v.budget_type_code IN ('AC','AR')
4016 );
4017
4018 EXCEPTION
4019 WHEN NO_DATA_FOUND THEN
4020 l_dummy := 'N';
4021
4022 END;
4023
4024
4025 IF (l_dummy = 'Y')
4026 THEN
4027 -- Default r11.5.7 Budgets Model
4028
4029 x_ac_plan_type_id := NULL;
4030 x_ar_plan_type_id := NULL;
4031 x_ac_version_type := NULL;
4032 x_ar_version_type := NULL;
4033 RETURN;
4034
4035 ELSE
4036
4037 -- Find FP AC and AR Plan Type Ids, If Any --------------
4038
4039
4040 BEGIN
4041 -- AC
4042 SELECT o.fin_plan_type_id, v.version_type
4043 INTO l_ac_plan_type_id, l_ac_version_type
4044 FROM pa_proj_fp_options o
4045 , pa_budget_versions v
4046 WHERE o.fin_plan_version_id = v.budget_version_id
4047 AND v.approved_cost_plan_type_flag = 'Y'
4048 AND v.current_flag = 'Y'
4049 AND v.project_id = p_project_id;
4050
4051
4052 EXCEPTION
4053 WHEN NO_DATA_FOUND THEN
4054 l_ac_plan_type_id := NULL;
4055
4056 END;
4057
4058
4059 BEGIN
4060
4061 -- AR
4062 SELECT o.fin_plan_type_id, v.version_type
4063 INTO l_ar_plan_type_id, l_ar_version_type
4064 FROM pa_proj_fp_options o
4065 , pa_budget_versions v
4066 WHERE o.fin_plan_version_id = v.budget_version_id
4067 AND v.approved_rev_plan_type_flag = 'Y'
4068 AND v.current_flag = 'Y'
4069 AND v.project_id = p_project_id;
4070
4071
4072 EXCEPTION
4073 WHEN NO_DATA_FOUND THEN
4074 l_ar_plan_type_id := NULL;
4075
4076 END;
4077
4078
4079 IF (l_ac_plan_type_id IS NULL AND l_ar_plan_type_id IS NULL)
4080 THEN
4081 -- If Both AC and AR Plan Type ids are NULL, then Default the r11.5.7 Model
4082
4083 x_ac_plan_type_id := NULL;
4084 x_ar_plan_type_id := NULL;
4085 x_ac_version_type := NULL;
4086 x_ar_version_type := NULL;
4087 RETURN;
4088
4089 ELSE
4090 -- Assume FP Model
4091
4092 x_ac_plan_type_id := l_ac_plan_type_id;
4093 x_ar_plan_type_id := l_ar_plan_type_id;
4094 x_ac_version_type := l_ac_version_type;
4095 x_ar_version_type := l_ar_version_type;
4096 RETURN;
4097
4098 END IF; -- l_ac_plan_type_id IS NULL
4099
4100 END IF; -- l_dummy = 'Y'
4101
4102
4103 EXCEPTION
4104 WHEN OTHERS
4105 THEN
4106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4107 FND_MSG_PUB.Add_Exc_Msg
4108 ( p_pkg_name => 'PA_BUDGET_UTILS'
4109 , p_procedure_name => 'GET_APPROVED_FP_INFO'
4110 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
4111 );
4112 FND_MSG_PUB.Count_And_Get
4113 (p_count => x_msg_count ,
4114 p_data => x_msg_data );
4115 RETURN;
4116
4117
4118
4119 END Get_Approved_FP_Info;
4120
4121 -----------------------------------------------------------------------------
4122
4123 --Name: check_baseline_funding
4124 --Type: Function
4125 --
4126 --Description: This function is called from Oracle Projects, Project form
4127 -- (PAXPREPR.fmb).
4128 --
4129 -- This function returns either 0 or 1 based on the following
4130 -- 1. Returns 1 if the Project has Approved Revenue Budget(AR)
4131 -- (working/submitted/baselined) has budgets that use
4132 -- categorized resource lists either in new or old budgets model.
4133 -- 2. Returns 0 in all other cases.
4134 --
4135 --
4136 --
4137 --Called subprograms: None
4138 --
4139 --
4140 --
4141 --History:
4142 -- 08-JUL-2004 rravipat Created
4143 --
4144
4145 FUNCTION check_baseline_funding( x_project_id IN NUMBER )
4146 RETURN NUMBER IS
4147
4148 dummy NUMBER := 0;
4149
4150 BEGIN
4151 SELECT 1
4152 INTO dummy
4153 FROM dual
4154 WHERE EXISTS( SELECT 1
4155 FROM pa_budget_versions pbv,
4156 pa_resource_lists prl
4157 WHERE (pbv.budget_type_code = 'AR' OR -- old model
4158 pbv.budget_type_code IS NULL AND
4159 approved_rev_plan_type_flag = 'Y') -- new model
4160 AND pbv.ci_id is null -- filter change order versions
4161 AND pbv.resource_list_id = prl.resource_list_id
4162 AND prl.uncategorized_flag <> 'Y'
4163 AND pbv.project_id = x_project_id );
4164
4165 RETURN dummy;
4166 EXCEPTION
4167
4168 WHEN NO_DATA_FOUND THEN
4169 dummy := 0;
4170 RETURN dummy;
4171
4172 END;
4173
4174 -- --------------------------------------------------------------------------------
4175
4176 --Name: Set_Prj_Policy_Context
4177 --Type: Procedure
4178 --
4179 --Description: This procedure is called primarily from the following Budget Workflow packages
4180 -- related procedures:
4181 -- a) Budget Approval Workflow
4182 -- b) Budget Integration workflow
4183 --
4184 -- This procedure does the following:
4185 -- a) Derives org_id from project_id
4186 -- b) Passes org_id to mo_global.set_policy_context
4187 --
4188 -- This procedure assumes that the project_id had been fully validated
4189 -- by the calling object. Error checking is limited to any
4190 -- WHEN OTHERS ORA error.
4191 --Other Notes:
4192 --
4193 -- I had to add x_err_code to list to accomodate historical procedure standard
4194 -- used by the Budget Approval workflow.
4195 --
4196 --
4197 --
4198 --
4199 --Called subprograms: none
4200 --
4201 --
4202 --
4203 --History:
4204 -- 19-JUL-05 jwhite - Created
4205 --
4206
4207 Procedure Set_Prj_Policy_Context
4208 (
4209 p_project_id IN NUMBER
4210 , x_msg_count OUT NOCOPY NUMBER
4211 , x_msg_data OUT NOCOPY VARCHAR2
4212 , x_return_status OUT NOCOPY VARCHAR2
4213 , x_err_code OUT NOCOPY NUMBER
4214 )
4215 IS
4216
4217 l_org_id pa_projects_all.org_id%TYPE := NULL;
4218
4219 Begin
4220
4221
4222
4223 -- Assume Success
4224 x_return_status := FND_API.G_RET_STS_SUCCESS;
4225 x_msg_count := 0;
4226 x_msg_data := NULL;
4227 x_err_code := 0;
4228
4229
4230 -- Fetch Project Org_Id
4231 -- This should NOT fail since it should have been fully validated
4232 -- by the calling object.
4233
4234 SELECT org_id
4235 INTO l_org_id
4236 FROM pa_projects_all
4237 WHERE project_id = p_project_id;
4238
4239
4240 -- Set the Operating Unit Context
4241 mo_global.set_policy_context(p_access_mode => 'S'
4242 , p_org_id => l_org_id );
4243
4244
4245
4246 EXCEPTION
4247 WHEN OTHERS THEN
4248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4249 x_err_code := SQLCODE;
4250 FND_MSG_PUB.Add_Exc_Msg
4251 ( p_pkg_name => 'PA_BUDGET_UTILS'
4252 , p_procedure_name => 'SET_PRJ_POLICY_cONTEXT'
4253 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
4254 );
4255 FND_MSG_PUB.Count_And_Get
4256 (p_count => x_msg_count ,
4257 p_data => x_msg_data );
4258 RETURN;
4259
4260
4261 END Set_Prj_Policy_Context;
4262
4263
4264
4265 END pa_budget_utils;