[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_UTILS
Source
1 package body pa_budget_utils as
2 -- $Header: PAXBUBUB.pls 120.11.12020000.3 2013/04/19 09:56:57 krkondur 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_cbs_element_id in number default null, --Bug 16604257
2083 x_description in varchar2,
2084 x_start_date in date,
2085 x_end_date in date,
2086 x_period_name in varchar2,
2087 x_quantity in out NOCOPY number, --File.Sql.39 bug 4440895
2088 x_unit_of_measure in varchar2,
2089 x_track_as_labor_flag in varchar2,
2090 x_raw_cost in out NOCOPY number, --File.Sql.39 bug 4440895
2091 x_burdened_cost in out NOCOPY number, --File.Sql.39 bug 4440895
2092 x_revenue in out NOCOPY number, --File.Sql.39 bug 4440895
2093 x_change_reason_code in varchar2,
2094 x_attribute_category in varchar2,
2095 x_attribute1 in varchar2,
2096 x_attribute2 in varchar2,
2097 x_attribute3 in varchar2,
2098 x_attribute4 in varchar2,
2099 x_attribute5 in varchar2,
2100 x_attribute6 in varchar2,
2101 x_attribute7 in varchar2,
2102 x_attribute8 in varchar2,
2103 x_attribute9 in varchar2,
2104 x_attribute10 in varchar2,
2105 x_attribute11 in varchar2,
2106 x_attribute12 in varchar2,
2107 x_attribute13 in varchar2,
2108 x_attribute14 in varchar2,
2109 x_attribute15 in varchar2,
2110 -- Bug Fix: 4569365. Removed MRC code.
2111 -- x_mrc_flag in varchar2, /* FPB2: MRC */
2112 x_pm_product_code in varchar2 default null,
2113 x_pm_budget_line_reference in varchar2 default null,
2114 x_quantity_source varchar2 default 'M',
2115 x_raw_cost_source varchar2 default 'M',
2116 x_burdened_cost_source varchar2 default 'M',
2117 x_revenue_source varchar2 default 'M',
2118 x_resource_assignment_id in out NOCOPY number, --File.Sql.39 bug 4440895
2119 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2120 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2121 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
2122 )
2123 is
2124 old_stack varchar2(630);
2125 x_created_by number;
2126 x_last_update_login number;
2127 v_budget_type_code varchar2(30);
2128
2129 cursor get_budget_type_code is
2130 select budget_type_code
2131 from pa_budget_versions
2132 where budget_version_id = x_budget_version_id;
2133
2134
2135 l_Projfunc_Currency_Code pa_projects_all.projfunc_currency_code%TYPE := NULL;
2136 l_Project_Currency_Code pa_projects_all.project_currency_code%TYPE := NULL;
2137 l_Txn_Currency_Code pa_projects_all.projfunc_currency_code%TYPE := NULL;
2138
2139 l_Return_Status VARCHAR2(1) :=NULL;
2140 l_Msg_Data VARCHAR2(2000) :=NULL;
2141 l_Msg_Count NUMBER := 0;
2142
2143 l_budget_line_id pa_budget_lines.budget_line_id%TYPE; /* FPB2 */
2144
2145
2146
2147 begin
2148
2149
2150 x_err_code := 0;
2151 old_stack := x_err_stack;
2152 x_err_stack := x_err_stack || '->create_line';
2153
2154 IF p_pa_debug_mode = 'Y' THEN
2155 pa_debug.set_err_stack('PA_BUDGET_UTILS.CREATE_LINE');
2156 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
2157 END IF;
2158 -- Bug Fix: 4569365. Removed MRC code.
2159 /* FPB2: MRC */
2160 /*
2161 IF x_mrc_flag IS NULL THEN
2162 l_msg_data := 'x_mrc_flag cannot be null to table handler';
2163 RAISE FND_API.G_EXC_ERROR;
2164 END IF;
2165 */
2166
2167 open get_budget_type_code;
2168 fetch get_budget_type_code into v_budget_type_code;
2169 close get_budget_type_code;
2170
2171 x_created_by := to_number(fnd_profile.value('USER_ID'));
2172 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2173
2174 -- Get the project_totals
2175 x_err_stage := 'verify if resource assignment exists <'
2176 || to_char(x_budget_version_id) || '><'
2177 || to_char(x_project_id) || '><'
2178 || to_char(x_task_id) || '><'
2179 || to_char(x_resource_list_member_id)
2180 || '>';
2181
2182 begin
2183
2184 select resource_assignment_id
2185 into x_resource_assignment_id
2186 from pa_resource_assignments
2187 where budget_version_id = x_budget_version_id
2188 and project_id = x_project_id
2189 and NVL(task_id, 0) = NVL(x_task_id, 0)
2190 and resource_list_member_id = x_resource_list_member_id
2191 AND NVL(CBS_ELEMENT_ID,-1)=NVL(X_CBS_ELEMENT_ID,-1); --Bug 16604257
2192
2193 IF P_PA_DEBUG_MODE = 'Y' THEN
2194 pa_debug.g_err_stage:= 'Resource assignment id - '||x_resource_assignment_id;
2195 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2196 END IF;
2197
2198 exception
2199 when NO_DATA_FOUND then
2200 x_err_stage := 'create new resource assignment <'
2201 || to_char(x_budget_version_id) || '><'
2202 || to_char(x_project_id) || '><'
2203 || to_char(x_task_id) || '><'
2204 || to_char(x_resource_list_member_id)
2205 || '>';
2206 IF P_PA_DEBUG_MODE = 'Y' THEN
2207 pa_debug.g_err_stage:= 'No data found';
2208 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2209 END IF;
2210
2211 select pa_resource_assignments_s.nextval
2212 into x_resource_assignment_id
2213 from sys.dual;
2214
2215 IF P_PA_DEBUG_MODE = 'Y' THEN
2216 pa_debug.g_err_stage:= 'Resource assignment id - '||x_resource_assignment_id;
2217 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2218 END IF;
2219
2220 -- create a new resource assignment
2221 insert into pa_resource_assignments
2222 (resource_assignment_id,
2223 budget_version_id,
2224 project_id,
2225 task_id,
2226 resource_list_member_id,
2227 CBS_ELEMENT_ID,
2228 last_update_date,
2229 last_updated_by,
2230 creation_date,
2231 created_by,
2232 last_update_login,
2233 unit_of_measure,
2234 track_as_labor_flag,
2235 project_assignment_id, --added the cloumn for bug 2446041
2236 RESOURCE_ASSIGNMENT_TYPE)
2237 values ( x_resource_assignment_id,
2238 x_budget_version_id,
2239 x_project_id,
2240 x_task_id,
2241 x_resource_list_member_id,
2242 X_CBS_ELEMENT_ID,--Bug 16604257
2243 SYSDATE,
2244 x_created_by,
2245 SYSDATE,
2246 x_created_by,
2247 x_last_update_login,
2248 x_unit_of_measure,
2249 x_track_as_labor_flag,
2250 -1, --added the cloumn for bug 2446041
2251 'USER_ENTERED');
2252
2253 when others then
2254 x_err_code := SQLCODE;
2255 IF P_PA_DEBUG_MODE = 'Y' THEN
2256 pa_debug.g_err_stage:= 'When others'||substr(SQLERRM,1,100);
2257 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2258 END IF;
2259 return;
2260 end ;
2261
2262 -- insert into pa_budget_lines
2263 x_err_stage := 'create new budget line <'
2264 || to_char(x_resource_assignment_id) || '><'
2265 || to_char(x_start_date, 'DD-MON-YYYY')
2266 || '>';
2267
2268 -- Fix for Bugs # 475852 and 503183
2269 -- Copy raw cost into burdened cost if budrened cost is null.
2270 -- If the resource UOM is currency and raw cost is null then
2271 -- copy value of quantity amt into raw cost and also set quantity
2272 -- amt to null.
2273
2274 if pa_budget_utils.get_budget_amount_code(v_budget_type_code) = 'C' then
2275 -- Cost Budget
2276
2277 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
2278 if x_raw_cost is null then
2279 x_raw_cost := x_quantity;
2280 end if;
2281 if x_unit_of_measure is not null then --Bug 4432032
2282 x_quantity := null;
2283 end if ;
2284 end if;
2285
2286 if x_burdened_cost is null then
2287 x_burdened_cost := x_raw_cost;
2288 end if;
2289
2290 else -- Revenue Budget
2291 if pa_budget_utils.check_currency_uom(x_unit_of_measure) = 'Y' then
2292 if x_revenue is null then
2293 x_revenue := x_quantity;
2294 end if;
2295 if x_unit_of_measure is not null then --Bug 4432032
2296 x_quantity := null;
2297 end if ;
2298 end if;
2299 end if;
2300
2301
2302 -- Get Project Currency Information for INSERT
2303 IF P_PA_DEBUG_MODE = 'Y' THEN
2304 pa_debug.g_err_stage:= 'Calling Get_Project_Currency_Info';
2305 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2306 END IF;
2307 PA_BUDGET_UTILS.Get_Project_Currency_Info
2308 (
2309 p_project_id => x_project_id
2310 , x_projfunc_currency_code => l_projfunc_currency_code
2311 , x_project_currency_code => l_project_currency_code
2312 , x_txn_currency_code => l_txn_currency_code
2313 , x_msg_count => l_msg_count
2314 , x_msg_data => l_msg_data
2315 , x_return_status => l_return_status
2316 );
2317
2318 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
2319 THEN
2320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2321
2322 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
2323 THEN
2324 RAISE FND_API.G_EXC_ERROR;
2325 END IF;
2326
2327 IF P_PA_DEBUG_MODE = 'Y' THEN
2328 pa_debug.g_err_stage:= 'l_return_status is - '||l_return_status;
2329 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2330 END IF;
2331
2332 /* FPB2 */
2333 SELECT pa_budget_lines_s.nextval
2334 INTO l_budget_line_id
2335 FROM DUAL;
2336
2337 IF P_PA_DEBUG_MODE = 'Y' THEN
2338 pa_debug.g_err_stage:= 'l_budget_line_id is - '||l_budget_line_id;
2339 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2340 END IF;
2341
2342 insert into pa_budget_lines
2343 (budget_line_id, /* FPB2 */
2344 budget_version_id, /* FPB2 */
2345 resource_assignment_id,
2346 start_date,
2347 last_update_date,
2348 last_updated_by,
2349 creation_date,
2350 created_by,
2351 last_update_login,
2352 end_date,
2353 period_name,
2354 quantity,
2355 raw_cost,
2356 burdened_cost,
2357 revenue,
2358 change_reason_code,
2359 description,
2360 attribute_category,
2361 attribute1,
2362 attribute2,
2363 attribute3,
2364 attribute4,
2365 attribute5,
2366 attribute6,
2367 attribute7,
2368 attribute8,
2369 attribute9,
2370 attribute10,
2371 attribute11,
2372 attribute12,
2373 attribute13,
2374 attribute14,
2375 attribute15,
2376 pm_product_code,
2377 pm_budget_line_reference,
2378 quantity_source,
2379 raw_cost_source,
2380 burdened_cost_source,
2381 revenue_source,
2382 projfunc_currency_code,
2383 project_currency_code,
2384 txn_currency_code
2385 )
2386 values (
2387 l_budget_line_id, /* FPB2 */
2388 x_budget_version_id, /* FPB2 */
2389 x_resource_assignment_id,
2390 x_start_date,
2391 SYSDATE,
2392 x_created_by,
2393 SYSDATE,
2394 x_created_by,
2395 x_last_update_login,
2396 x_end_date,
2397 x_period_name,
2398 x_quantity,
2399 pa_currency.round_currency_amt(x_raw_cost),
2400 pa_currency.round_currency_amt(x_burdened_cost),
2401 pa_currency.round_currency_amt(x_revenue),
2402 x_change_reason_code,
2403 x_description,
2404 x_attribute_category,
2405 x_attribute1,
2406 x_attribute2,
2407 x_attribute3,
2408 x_attribute4,
2409 x_attribute5,
2410 x_attribute6,
2411 x_attribute7,
2412 x_attribute8,
2413 x_attribute9,
2414 x_attribute10,
2415 x_attribute11,
2416 x_attribute12,
2417 x_attribute13,
2418 x_attribute14,
2419 x_attribute15,
2420 x_pm_product_code,
2421 x_pm_budget_line_reference,
2422 x_quantity_source,
2423 x_raw_cost_source,
2424 x_burdened_cost_source,
2425 x_revenue_source,
2426 l_Projfunc_currency_code,
2427 l_Project_currency_code,
2428 l_txn_currency_code
2429 );
2430 -- Bug Fix: 4569365. Removed MRC code.
2431 /* FPB2: MRC */
2432 /*
2433 IF x_mrc_flag = 'Y' THEN
2434
2435 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
2436
2437 IF P_PA_DEBUG_MODE = 'Y' THEN
2438 pa_debug.g_err_stage:= 'Calling check_mrc_install';
2439 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2440 END IF;
2441 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
2442 (x_return_status => l_return_status,
2443 x_msg_count => l_msg_count,
2444 x_msg_data => l_msg_data);
2445 IF P_PA_DEBUG_MODE = 'Y' THEN
2446 pa_debug.g_err_stage:= 'l_return_status is -'||l_return_status;
2447 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2448 pa_debug.g_err_stage:= 'l_msg_count - '||l_msg_count;
2449 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2450 pa_debug.g_err_stage:= 'l_msg_data - '||l_msg_data;
2451 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2452 END IF;
2453 END IF;
2454
2455 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
2456 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
2457 IF P_PA_DEBUG_MODE = 'Y' THEN
2458 pa_debug.g_err_stage:= 'Calling MAINTAIN_ONE_MC_BUDGET_LINE';
2459 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2460 END IF;
2461 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
2462 (p_budget_line_id => l_budget_line_id,
2463 p_budget_version_id => x_budget_version_id,
2464 p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
2465 x_return_status => l_return_status,
2466 x_msg_count => l_msg_count,
2467 x_msg_data => l_msg_data);
2468 IF P_PA_DEBUG_MODE = 'Y' THEN
2469 pa_debug.g_err_stage:= 'l_return_status is -'||l_return_status;
2470 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2471 pa_debug.g_err_stage:= 'l_msg_count - '||l_msg_count;
2472 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2473 pa_debug.g_err_stage:= 'l_msg_data - '||l_msg_data;
2474 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2475 END IF;
2476 END IF;
2477
2478 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2479 IF P_PA_DEBUG_MODE = 'Y' THEN
2480 pa_debug.g_err_stage:= 'Raising g_mrc_exception';
2481 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2482 END IF;
2483 RAISE g_mrc_exception;
2484 END IF;
2485
2486 END IF;
2487 */
2488
2489 x_err_stack := old_stack;
2490 pa_debug.reset_err_stack;
2491
2492 exception
2493 WHEN FND_API.G_EXC_ERROR
2494 THEN
2495 x_err_code := SQLCODE;
2496 FND_MSG_PUB.Add_Exc_Msg
2497 ( p_pkg_name => 'PA_BUDGET_UTILS'
2498 , p_procedure_name => 'CREATE_LINE'
2499 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2500 );
2501 IF P_PA_DEBUG_MODE = 'Y' THEN
2502 pa_debug.g_err_stage:= 'In exception of create_line -1 '||substr(SQLERRM,1,100);
2503 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL4);
2504 END IF;
2505 RETURN;
2506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2507 THEN
2508 x_err_code := SQLCODE;
2509 IF P_PA_DEBUG_MODE = 'Y' THEN
2510 pa_debug.g_err_stage:= 'In exception of create_line -2 '||substr(SQLERRM,1,100);
2511 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2512 END IF;
2513 FND_MSG_PUB.Add_Exc_Msg
2514 ( p_pkg_name => 'PA_BUDGET_UTILS'
2515 , p_procedure_name => 'CREATE_LINE'
2516 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2517 );
2518 RETURN;
2519 when others then
2520 x_err_code := SQLCODE;
2521 IF P_PA_DEBUG_MODE = 'Y' THEN
2522 pa_debug.g_err_stage:= 'In exception of create_line -3 '||substr(SQLERRM,1,100);
2523 pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2524 END IF;
2525 return;
2526
2527 end create_line;
2528
2529 ------------------------------------------------------------------------------
2530
2531 procedure summerize_project_totals (x_budget_version_id in number,
2532 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2533 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2534 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
2535 is
2536 x_created_by number;
2537 x_last_update_login number;
2538 old_stack varchar2(630);
2539 begin
2540
2541 x_err_code := 0;
2542 old_stack := x_err_stack;
2543 x_err_stack := x_err_stack || '->summerize_project_totals';
2544
2545 IF p_pa_debug_mode = 'Y' THEN
2546 pa_debug.set_err_stack('PA_BUDGET_UTILS.SUMMERIZE_PROJECT_TOTALS');
2547 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
2548 END IF;
2549
2550 x_created_by := to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id));
2551 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2552
2553 -- Get the project_totals
2554 x_err_stage := 'get project totals <' || to_char(x_budget_version_id)
2555 || '>';
2556
2557 IF P_PA_DEBUG_MODE = 'Y' THEN
2558 pa_debug.g_err_stage:= 'In summerize_project_amounts';
2559 pa_debug.write('summerize_project_totals: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2560 END IF;
2561
2562 update pa_budget_versions v
2563 set (labor_quantity,
2564 labor_unit_of_measure,
2565 raw_cost,
2566 burdened_cost,
2567 revenue,
2568 last_update_date,
2569 last_updated_by,
2570 last_update_login
2571 )
2572 =
2573 (select sum(nvl(to_number(decode(a.track_as_labor_flag,
2574 'Y', l.quantity, NULL)),0)),
2575 -- decode(a.track_as_labor_flag, 'Y', a.unit_of_measure, NULL),
2576 'HOURS', -- V4 uses HOURS as the only labor unit
2577 pa_currency.round_currency_amt(sum(nvl(l.raw_cost, 0))),
2578 pa_currency.round_currency_amt(sum(nvl(l.burdened_cost, 0))),
2579 pa_currency.round_currency_amt(sum(nvl(l.revenue, 0))),
2580 SYSDATE,
2581 x_created_by,
2582 x_last_update_login
2583 from pa_resource_assignments a,
2584 pa_budget_lines l
2585 where a.budget_version_id = x_budget_version_id /*Bug 4198840: Perf:Included this join*/
2586 and a.budget_version_id = v.budget_version_id
2587 and a.resource_assignment_id = l.resource_assignment_id
2588 )
2589 where budget_version_id = x_budget_version_id;
2590
2591 IF P_PA_DEBUG_MODE = 'Y' THEN
2592 pa_debug.g_err_stage:= 'After update';
2593 pa_debug.write('summerize_project_totals: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2594 END IF;
2595
2596 x_err_stack := old_stack;
2597 pa_debug.reset_err_stack;
2598
2599 exception
2600 when others then
2601 x_err_code := SQLCODE;
2602 IF P_PA_DEBUG_MODE = 'Y' THEN
2603 pa_debug.g_err_stage:= 'In exception of summerize_project_totals';
2604 pa_debug.write('summerize_project_totals: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2605 END IF;
2606 return;
2607
2608 end summerize_project_totals;
2609 -- =================================================
2610
2611 --Name: Verify_Budget_Rules
2612 --Type: Procedure
2613 --
2614 --Description: This procedure is called both from the Oracle Projects
2615 -- Budgets form (PAXBUEBU.fmb) when the Submit
2616 -- and Baseline buttons are pressed and the
2617 -- public Baseline_Budget api.
2618 --
2619 -- This procedure does the following:
2620 -- 1) It performs Oracle Project product specific
2621 -- validations.
2622 -- 2) It calls a client extension for additional
2623 -- client specific validations.
2624 --
2625 -- The procedure also distinguishes between
2626 -- submission edits ('SUBMIT') and
2627 -- baseline edits ('BASELINE') as determined
2628 -- by the value of the p_event parameter.
2629 --
2630 -- Most of the Oracle Project product specific code
2631 -- was copied from the pa_budget_core.baseline
2632 -- procedure. Now, the pa_budget_core.baseline
2633 -- validation calls this procedure.
2634 --
2635 --
2636 --Called subprograms: PA_Client_Extn_Budget.Verify_Budget_Rulesc
2637 --
2638 --
2639 --
2640 --History:
2641 -- 29-JUL-97 jwhite - Created
2642 -- 20-AUG-97 jwhite Added p_calling_module
2643 -- 10-SEP-97 jwhite As per latest specs, added p_warnings_only_flag
2644 -- and p_err_msg_count
2645 -- to Verify_Budget_Rules, and code
2646 -- to support multiple messaging.
2647 -- 15-JUL-99 risingh entry level code for rev budgets should be
2648 -- determined only if it is not P or T already
2649 -- bug 876456 - performance improvement of baseline procedure
2650 --
2651 -- 07-AUG-02 jwhite Adapted logic to suport the r11.5.7 model and new FP model.
2652 --
2653 -- 10-DEC-2003 bvarnasi Bug 3142016 : Selecting 0 if the amount in budget versions
2654 -- is null otherwise, the comparision fails in billing_core.
2655 --
2656 PROCEDURE Verify_Budget_Rules
2657 (p_draft_version_id IN NUMBER
2658 , p_mark_as_original IN VARCHAR2
2659 , p_event IN VARCHAR2
2660 , p_project_id IN NUMBER
2661 , p_budget_type_code IN VARCHAR2
2662 , p_resource_list_id IN NUMBER
2663 , p_project_type_class_code IN VARCHAR2
2664 , p_created_by IN NUMBER
2665 , p_calling_module IN VARCHAR2
2666 , p_fin_plan_type_id IN NUMBER DEFAULT NULL
2667 , p_version_type IN VARCHAR2 DEFAULT NULL
2668 , p_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2669 , p_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2670 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2671 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2672 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2673 )
2674
2675 IS
2676
2677 l_entry_level_code VARCHAR2(30);
2678 l_dummy NUMBER;
2679 l_budget_total NUMBER DEFAULT 0;
2680 l_old_stack VARCHAR2(630);
2681 l_funding_level VARCHAR2(2) DEFAULT NULL;
2682
2683 l_ext_warnings_only_flag VARCHAR2(1) := NULL;
2684 l_ext_err_msg_count NUMBER := 0;
2685
2686
2687 -- For FP Model
2688 l_approved_code pa_budget_types.budget_type_code%TYPE := NULL;
2689 l_Return_Status VARCHAR2(1) :=NULL;
2690 l_Msg_Data VARCHAR2(2000) :=NULL;
2691 l_Msg_Count NUMBER := 0;
2692
2693
2694
2695 BEGIN
2696
2697
2698
2699 -- Initialize OUT-parameters for Multiple Error Messaging
2700
2701 p_warnings_only_flag := 'Y';
2702 p_err_msg_count := 0;
2703
2704
2705 p_err_code := 0;
2706 l_old_stack := p_err_stack;
2707 p_err_stack := p_err_stack || '->check_budget_rules';
2708
2709 IF( PA_UTILS.GetEmpIdFromUser(p_created_by ) IS NULL) THEN
2710 p_err_code := 10;
2711 p_err_stage := 'PA_ALL_WARN_NO_EMPL_REC';
2712
2713 PA_UTILS.Add_Message
2714 ( p_app_short_name => 'PA'
2715 , p_msg_name => p_err_stage
2716 );
2717 p_warnings_only_flag := 'N';
2718
2719 END IF;
2720
2721 -- FP Model Processing, if Any -----------------------
2722
2723 IF (p_budget_type_code IS NULL)
2724 THEN
2725 -- A FP Plan is being processed. Get the l_approved_code for Subsequent Processing
2726
2727 PA_BUDGET_UTILS.Get_Version_Approved_Code
2728 (
2729 p_budget_version_id => p_draft_version_id
2730 , x_approved_code => l_approved_code
2731 , x_msg_count => l_msg_count
2732 , x_msg_data => l_msg_data
2733 , x_return_status => l_return_status
2734 );
2735
2736
2737 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
2738 THEN
2739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2740
2741 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
2742 THEN
2743
2744 RAISE FND_API.G_EXC_ERROR;
2745 END IF;
2746
2747
2748 ELSE
2749 -- A r11.5.7 Budget is being processed.
2750 l_approved_code := p_budget_type_code;
2751
2752 END IF;
2753
2754
2755 -- -----------------------------------------------------
2756
2757 IF (p_event = 'SUBMIT')
2758 THEN
2759 -- Oracle Projects Standard Submission Validation
2760 -- None currently.
2761
2762 NULL;
2763
2764 ELSE
2765
2766 -- Oracle Projects Standard Baseline Validation
2767
2768 p_err_stage := 'get draft budget info <' || to_char(p_draft_version_id)
2769 || '>';
2770
2771
2772 -- check if there is at least one project or task draft budget exists
2773 p_err_stage := 'check budget exists <' || to_char(p_draft_version_id)
2774 || '>';
2775
2776 BEGIN
2777 select 1
2778 into l_dummy
2779 from sys.dual
2780 where exists
2781 (select 1
2782 from pa_resource_assignments
2783 where budget_version_id = p_draft_version_id);
2784
2785 EXCEPTION
2786 WHEN NO_DATA_FOUND THEN
2787 p_err_code := 10;
2788 p_err_stage := 'PA_BU_NO_BUDGET';
2789 PA_UTILS.Add_Message
2790 ( p_app_short_name => 'PA'
2791 , p_msg_name => p_err_stage
2792 );
2793 p_warnings_only_flag := 'N';
2794
2795
2796 WHEN OTHERS THEN
2797 p_err_code := SQLCODE;
2798 FND_MSG_PUB.Add_Exc_Msg
2799 ( p_pkg_name => 'PA_BUDGET_UTILS'
2800 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2801 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2802 );
2803 p_warnings_only_flag := 'N';
2804 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2805 RETURN;
2806 END;
2807
2808 -- do extra check for revenue budget
2809 if ( (l_approved_code IN ('AR','ALL') )
2810 and (p_project_type_class_code = 'CONTRACT')
2811 )
2812 then
2813
2814 -- check the level of budgeting.
2815 -- Note: import budget does not have budget entry method
2816
2817 -- Fix 876456
2818
2819 if( l_entry_level_code not in ('P','T')) then
2820
2821 BEGIN
2822
2823 p_err_stage := 'check budgeting level <'
2824 || to_char(p_draft_version_id) || '>';
2825
2826 select 'T'
2827 into l_entry_level_code
2828 from sys.dual
2829 where exists
2830 (select 1
2831 from pa_resource_assignments
2832 where budget_version_id = p_draft_version_id
2833 -- and task_id is not null);
2834 -- this has been changed since pa_resource_assignments
2835 -- stores 0 if a task_id does not exist rather than null
2836 and task_id <> 0);
2837
2838 EXCEPTION
2839 WHEN NO_DATA_FOUND THEN
2840 -- budget at project level
2841 l_entry_level_code := 'P';
2842 WHEN OTHERS THEN
2843 p_err_code := SQLCODE;
2844 FND_MSG_PUB.Add_Exc_Msg
2845 ( p_pkg_name => 'PA_BUDGET_UTILS'
2846 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2847 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2848 );
2849 p_warnings_only_flag := 'N';
2850 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2851 RETURN;
2852 END;
2853
2854 end if;
2855
2856 -- get the sum of revenue budget for this p_draft_version_id
2857 select nvl(revenue,0) -- Bug 3142016
2858 into l_budget_total
2859 from pa_budget_versions
2860 where budget_version_id = p_draft_version_id;
2861
2862 -- call pa_billing_core.verify_baseline_funding to check the funding revenue
2863 pa_billing_core.verify_baseline_funding(
2864 p_project_id,
2865 p_draft_version_id,
2866 l_entry_level_code,
2867 l_budget_total, -- Removing this temporary fix. /* This is just a temporary fix for FPM testing to proceed. The real fix is being discussed */
2868 p_err_code,
2869 p_err_stage,
2870 p_err_stack);
2871
2872 -- PA_UTILS.Add_Message already addressed internally by Verify_Baseline_Funding
2873 -- Only RETURN if Oracle error. Otherwise, continue processing.
2874
2875 IF (p_err_code <> 0)
2876 THEN
2877 p_warnings_only_flag := 'N';
2878 END IF;
2879 IF (p_err_code < 0) THEN
2880 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2881 RETURN;
2882 END IF;
2883
2884 elsif ( (l_approved_code IN ('AC','ALL') )
2885 and (p_project_type_class_code <> 'CONTRACT')
2886 )
2887 then
2888
2889 NULL;
2890
2891
2892 END IF; -- of AR revenue budget
2893 END IF; -- OP Standard Validations
2894
2895 -- Client Specific Validations --------------------------------------------------
2896
2897 p_err_stage := 'Check Client Extn Verify Budget Rules <' || to_char(p_project_id )
2898 || '><'|| p_budget_type_code
2899 || '>'|| to_char(p_draft_version_id)
2900 || '>'|| p_mark_as_original
2901 || '>';
2902
2903
2904
2905 PA_CLIENT_EXTN_BUDGET.Verify_Budget_Rules
2906 (p_draft_version_id => p_draft_version_id
2907 , p_mark_as_original => p_mark_as_original
2908 , p_event => p_event
2909 , p_project_id => p_project_id
2910 , p_budget_type_code => p_budget_type_code
2911 , p_resource_list_id => p_resource_list_id
2912 , p_project_type_class_code => p_project_type_class_code
2913 , p_created_by => p_created_by
2914 , p_calling_module => p_calling_module
2915 , p_fin_plan_type_id => p_fin_plan_type_id
2916 , p_version_type => p_version_type
2917 , p_warnings_only_flag => l_ext_warnings_only_flag
2918 , p_err_msg_count => l_ext_err_msg_count
2919 , p_error_code => p_err_code
2920 , p_error_message => p_err_stage
2921 );
2922
2923
2924
2925 -- PA_UTILS.Add_Message already addressed internally by client extn
2926 -- Verify_Budget_Rules
2927 -- Only RETURN if Oracle error. Otherwise, continue processing.
2928
2929 IF (l_ext_err_msg_count > 0)
2930 THEN
2931 IF (l_ext_warnings_only_flag = 'N') THEN
2932 p_warnings_only_flag := 'N';
2933 END IF;
2934 END IF;
2935
2936 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2937 p_err_stack := l_old_stack;
2938
2939 EXCEPTION
2940 WHEN FND_API.G_EXC_ERROR
2941 THEN
2942 p_err_code := SQLCODE;
2943 FND_MSG_PUB.Add_Exc_Msg
2944 ( p_pkg_name => 'PA_BUDGET_UTILS'
2945 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2946 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2947 );
2948 p_warnings_only_flag := 'N';
2949 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2950 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2951 THEN
2952 p_err_code := SQLCODE;
2953 FND_MSG_PUB.Add_Exc_Msg
2954 ( p_pkg_name => 'PA_BUDGET_UTILS'
2955 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2956 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2957 );
2958 p_warnings_only_flag := 'N';
2959 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2960 WHEN OTHERS THEN
2961 p_err_code := SQLCODE;
2962 FND_MSG_PUB.Add_Exc_Msg
2963 ( p_pkg_name => 'PA_BUDGET_UTILS'
2964 , p_procedure_name => 'VERIFY_BUDGET_RULES'
2965 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
2966 );
2967 p_warnings_only_flag := 'N';
2968 p_err_msg_count := FND_MSG_PUB.Count_Msg;
2969 RETURN;
2970
2971 END Verify_Budget_Rules;
2972
2973
2974
2975 -- =================================================
2976 -- =================================================
2977 --Name: Baseline_Budget
2978 --Type: Procedure
2979 --
2980 --Description: This wrapper procedure is called from the Oracle Projects
2981 -- Budgets form, the Budget Approval and Budget Integration
2982 -- workflows and the AMG Baseline_Budget API.
2983 --
2984 -- This procedure does the following:
2985 -- 1) For Integration budgets,
2986 -- a. performs funds checking and reserves funds if
2987 -- applicable.
2988 -- b. baselines the integration budget
2989 -- c. baselines a corresponding Commitment control
2990 -- budget.
2991 -- d. If successful for both baselines, ties back
2992 -- to the new baselined budget version id.
2993 -- e. If not unsuccessful, rolls back the reserved funds, if any.
2994 -- 2) For other budgets, baselines the budget
2995 --
2996 --
2997 --
2998 --
2999 --Called subprograms: PA_BUDGET_CORE.Baseline
3000 --
3001 --
3002 --
3003 --History:
3004 -- 30-APR-2001 jwhite - Created
3005 --
3006 --
3007 -- 25-JUL-2005 jwhite - R12 SLA Effort
3008 -- Largely rewrote this procedure with regard
3009 -- to Budgetary Control functionality.
3010 --
3011 -- Please see the previous version for obsolete budgetary contol code.
3012 --
3013 -- 23-AUG-2005 jwhite - R12 SLA Effort, Phase II
3014 -- When Budget Integration is sucessful, add Success message
3015 -- to message stack.
3016 -- 29-Aug-2006 nkumbi - Federal Uptake Bug 5522880
3017 -- If federal profile option is enabled, BEM/Third party client extension is called to
3018 -- populate the interface tables after all the baseline and funds check processing is done.
3019 -- The baseline process is also rolled back if the BEM interface fails.
3020 --
3021 --
3022 --
3023
3024 PROCEDURE Baseline_Budget
3025 (p_draft_version_id IN NUMBER
3026 , p_project_id IN NUMBER
3027 , p_mark_as_original IN VARCHAR2
3028 , p_fck_req_flag IN VARCHAR2 DEFAULT NULL
3029 , p_verify_budget_rules IN VARCHAR2 DEFAULT 'N'
3030 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3031 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3032 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3033 )
3034
3035 IS
3036
3037 --
3038 -- Local Variable Declaration
3039
3040 l_err_code NUMBER := 0;
3041 l_err_stage VARCHAR2(120) :=NULL;
3042 l_err_stack VARCHAR2(630) :=NULL;
3043 l_old_stack VARCHAR2(630) :=NULL;
3044
3045 l_Dual_Bdgt_Cntrl_Flag VARCHAR2(1) :=NULL;
3046 l_CC_Budget_Version_id NUMBER := 0;
3047 l_gl_new_base_ver_id NUMBER := 0;
3048 l_cc_new_base_ver_id NUMBER := 0;
3049 l_gl_budget_type_code pa_budget_types.budget_type_code%TYPE :=NULL;
3050 l_cc_budget_type_code pa_budget_types.budget_type_code%TYPE :=NULL;
3051
3052 l_Return_Status VARCHAR2(1) :=NULL;
3053 l_Msg_Data VARCHAR2(2000) :=NULL;
3054 l_Msg_Count NUMBER := 0;
3055
3056 l_Return_Status2 VARCHAR2(1) :=NULL;
3057 l_Msg_Data2 VARCHAR2(2000) :=NULL;
3058 l_Msg_Count2 NUMBER := 0;
3059
3060 l_msg_index_out NUMBER := 0;
3061 l_data VARCHAR2(2000) :=NULL;
3062
3063 --R12 SLA Effort
3064 l_baseline_version_id pa_budget_versions.budget_version_id%TYPE :=NULL;
3065 l_budget_type_code pa_budget_versions.budget_type_code%TYPE :=NULL;
3066
3067 --Federal Uptake Bug 5522880
3068 l_federal_enabled VARCHAR2(1) := NULL;
3069 l_bem_failed EXCEPTION;
3070 l_pre_baseline_version_id NUMBER := NULL;
3071 l_rejection_code VARCHAR2(250) := NULL;
3072 l_interface_status VARCHAR2(10) := NULL;
3073 l_baseline_version_number NUMBER := NULL;
3074 l_rejection_reason VARCHAR2(250) := NULL;
3075
3076
3077 Begin
3078 -- Setup Environment ---------------------------------------------------
3079
3080 -- Assume Success
3081 x_return_status := FND_API.G_RET_STS_SUCCESS;
3082 x_msg_count := 0;
3083 x_msg_data := NULL;
3084
3085 -- Standard begin of API savepoint
3086
3087 SAVEPOINT baseline_budget_wrappper;
3088
3089
3090
3091 -- Integration Processing and Baseline ----------------------------------
3092
3093
3094 IF (nvl(p_fck_req_flag,'N') ) = 'Y'
3095 THEN
3096
3097 -- !!! REQUIRED: Funds Check Processing !!! -------------------
3098
3099 -- BASELINE DRAFT C-O-M-M-I-T-M-E-N-T Version ---------------------------------------
3100
3101 -- R12 SLA Effort: COMMITMENT Budget Baseline DESUPPORTED Until Further Notice
3102
3103 -- When commitment budget support is reinstated, then either a SQL or a procedure
3104 -- call will be required to populate the following parameters:
3105 --
3106 -- 1) l_Dual_Bdgt_Cntrl_Flag
3107 -- 2) l_CC_Budget_Version_id
3108
3109 --Bug 6524116
3110 begin
3111 select 'Y'
3112 into l_dual_bdgt_cntrl_flag
3113 from dual
3114 where exists
3115 (select 1
3116 from pa_budgetary_control_options a
3117 where project_id = p_project_id
3118 and external_budget_code = 'CC'
3119 and bdgt_cntrl_flag = 'Y')
3120 and exists
3121 (select 1
3122 from pa_budgetary_control_options b
3123 where project_id = p_project_id
3124 and external_budget_code = 'GL'
3125 and bdgt_cntrl_flag = 'Y');
3126 exception
3127 when no_data_found then
3128 l_Dual_Bdgt_Cntrl_Flag := 'N';
3129 end;
3130
3131 begin
3132 SELECT budget_version_id
3133 INTO l_CC_Budget_Version_id
3134 FROM pa_budget_versions bv
3135 WHERE project_id = p_project_id
3136 AND BUDGET_STATUS_CODE = 'S'
3137 AND budget_type_code =
3138 (SELECT budget_type_code
3139 FROM pa_budgetary_control_options pbco
3140 WHERE pbco.budget_type_code = bv.budget_type_code
3141 AND pbco.project_id = bv.project_id
3142 AND pbco.bdgt_cntrl_flag = 'Y'
3143 AND pbco.external_budget_code = 'CC');
3144 exception
3145 when no_data_found then
3146 l_CC_Budget_Version_id := 0;
3147 end;
3148 --END Bug 6524116
3149
3150 IF (nvl(l_Dual_Bdgt_Cntrl_Flag,'N') = 'Y'
3151 AND nvl(l_CC_Budget_Version_id,0) > 0 )
3152 THEN
3153 --dbms_output.put_line('-- Baseline Commitment Control draft budget: '||to_char(l_CC_Budget_Version_id) );
3154
3155 PA_BUDGET_CORE.Baseline(x_draft_version_id => l_CC_Budget_Version_id
3156 ,x_mark_as_original => p_mark_as_original
3157 ,x_verify_budget_rules => p_verify_budget_rules
3158 ,x_err_code => l_err_code
3159 ,x_err_stage => l_err_stage
3160 ,x_err_stack => l_err_stack
3161 );
3162
3163 IF (l_err_code <> 0)
3164 THEN
3165
3166 -- Process Baseline Error. Rollback ANY Error
3167 IF (l_err_code < 0)
3168 THEN
3169 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3170 THEN
3171 FND_MSG_PUB.add_exc_msg
3172 ( p_pkg_name => 'PA_BUDGET_UTILS'
3173 , p_procedure_name => 'BASELINE_BUDGET'
3174 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0')
3175 );
3176 END IF;
3177 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3178 ELSE
3179 -- l_err_code > 0
3180 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3181 THEN
3182 FND_MESSAGE.SET_NAME('PA','PA_BASELINE_FAILED');
3183 FND_MSG_PUB.add;
3184 END IF;
3185 l_return_status := FND_API.G_RET_STS_ERROR;
3186 END IF; -- (l_err_code < 0)
3187
3188 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3189 THEN
3190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3191 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3192 THEN
3193 RAISE FND_API.G_EXC_ERROR;
3194 END IF;
3195
3196 END IF; --(nvl(l_Dual_Bdgt_Cntrl_Flag,'N') ) = 'Y'
3197
3198 --R12 SLA Effort: Desupported Until Futher Notice
3199 END IF;--IF (nvl(l_Dual_Bdgt_Cntrl_Flag,'N') = 'Y'
3200
3201 -- BASELINE D-R-A-F-T C-O-S-T Version ---------------------------------------
3202
3203 PA_BUDGET_CORE.Baseline(x_draft_version_id => p_draft_version_id
3204 ,x_mark_as_original => p_mark_as_original
3205 ,x_verify_budget_rules => p_verify_budget_rules
3206 ,x_err_code => l_err_code
3207 ,x_err_stage => l_err_stage
3208 ,x_err_stack => l_err_stack
3209 );
3210
3211
3212 IF (l_err_code <> 0)
3213 THEN
3214
3215 -- Process Baseline Error. Rollback ANY Error
3216 IF (l_err_code < 0)
3217 THEN
3218 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3219 THEN
3220 FND_MSG_PUB.add_exc_msg
3221 ( p_pkg_name => 'PA_BUDGET_UTILS'
3222 , p_procedure_name => 'BASELINE_BUDGET'
3223 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0')
3224 );
3225 END IF;
3226 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3227 ELSE
3228 -- l_err_code > 0
3229 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3230 THEN
3231 FND_MESSAGE.SET_NAME('PA','PA_BASELINE_FAILED');
3232 FND_MSG_PUB.add;
3233 END IF;
3234 l_return_status := FND_API.G_RET_STS_ERROR;
3235 END IF; -- (l_err_code < 0)
3236
3237 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3238 THEN
3239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3240 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3241 THEN
3242 RAISE FND_API.G_EXC_ERROR;
3243 END IF;
3244
3245
3246 END IF; -- l_err_code <> 0
3247
3248 -- ------------------------------------------------------------------------
3249
3250
3251 -- RESERVE_BASELINE Budget Funds for NEW B-A-S-E-L-I-N-E Version --------------
3252
3253 -- Get the baselined budget version for the draft
3254
3255 SELECT budget_type_code
3256 INTO l_budget_type_code
3257 FROM pa_budget_versions
3258 WHERE budget_version_id = p_draft_version_id
3259 AND budget_status_code = 'S';
3260
3261 SELECT budget_version_id, version_number
3262 INTo l_baseline_version_id, l_baseline_version_number
3263 FROM pa_budget_versions
3264 WHERE budget_type_code = l_budget_type_code
3265 AND project_id = p_project_id
3266 AND budget_status_code = 'B'
3267 AND current_flag = 'Y';
3268
3269
3270 PA_BUDGET_FUND_PKG.Check_OR_Reserve_Funds
3271 (P_Project_ID => p_project_id
3272 ,P_Budget_Version_Id => l_baseline_version_id
3273 ,P_calling_Mode => 'RESERVE_BASELINE'
3274 ,X_Dual_Bdgt_Cntrl_Flag => l_Dual_Bdgt_Cntrl_Flag
3275 ,X_CC_Budget_Version_id => l_CC_Budget_Version_id
3276 ,X_Return_Status => l_Return_Status
3277 ,X_Msg_Data => l_Msg_Data
3278 ,X_Msg_Count => l_Msg_Count
3279 );
3280
3281 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3282 THEN
3283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3284 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3285 THEN
3286 RAISE FND_API.G_EXC_ERROR;
3287 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS)
3288 THEN
3289 -- R12 SLA Effort, Phase II
3290 -- Add Success message to message stack.
3291 FND_MESSAGE.SET_NAME('PA','PA_NFSUBJ_BU_INTG_SUCCESS');
3292 FND_MSG_PUB.add;
3293 END IF;
3294
3295 /*Start - Changes for Federal Uptake - I */ -- Bug 5522880
3296
3297 l_federal_enabled := NVL(FND_PROFILE.value('FV_ENABLED'), 'N');
3298
3299 If(l_federal_enabled = 'Y' AND l_budget_type_code is NOT NULL) then
3300
3301 Begin
3302 SELECT budget_version_id
3303 INTo l_pre_baseline_version_id
3304 FROM pa_budget_versions pb
3305 WHERE pb.budget_type_code = l_budget_type_code
3306 AND pb.project_id = p_project_id
3307 AND pb.budget_status_code='B'
3308 AND pb.version_number = (l_baseline_version_number - 1);
3309 Exception
3310 When no_data_found then
3311 l_pre_baseline_version_id := NULL;
3312 End;
3313
3314 PA_CLIENT_EXT_FV_BUDGET_INT.INSERT_BUDGET_LINES
3315 (p_project_id => p_project_id
3316 ,p_pre_baselined_version_id => l_pre_baseline_version_id
3317 ,p_baselined_budget_version_id => l_baseline_version_id
3318 ,x_rejection_code => l_rejection_code
3319 ,x_interface_status => l_interface_status);
3320
3321 If ((l_interface_status = 'True' or l_interface_status is NULL) and l_rejection_code is NULL) THEN
3322 NULL; --BEM/Third Party Client Extension Successful
3323 Else
3324 RAISE l_bem_failed;
3325 End if;
3326
3327 End if;
3328 /*End - Changes for Federal Uptake - I */
3329 -- ----------------------------------------------------------------------
3330
3331
3332 ELSE
3333 -- --------------------------------------------------------------------
3334 -- !!! NO funds check !!!
3335 --
3336 -- Perform Vanilla Baseline.
3337 -- --------------------------------------------------------------------
3338 --dbms_output.put_line('NO Funds Check. Call PA_BUDGET_CORE.BASELINE');
3339
3340
3341 PA_BUDGET_CORE.Baseline (x_draft_version_id => p_draft_version_id
3342 ,x_mark_as_original => p_mark_as_original
3343 ,x_verify_budget_rules => p_verify_budget_rules
3344 ,x_err_code => l_err_code
3345 ,x_err_stage => l_err_stage
3346 ,x_err_stack => l_err_stack
3347 );
3348
3349
3350 IF (l_err_code <> 0)
3351 THEN
3352 -- Process Baseline Error. Rollback ANY Error
3353
3354 IF (l_err_code < 0)
3355 THEN
3356 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3357 THEN
3358 FND_MSG_PUB.add_exc_msg
3359 ( p_pkg_name => 'PA_BUDGET_UTILS'
3360 , p_procedure_name => 'BASELINE_BUDGET'
3361 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0')
3362 );
3363 END IF;
3364 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3365 ELSE
3366 -- l_err_code > 0
3367 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3368 THEN
3369 FND_MESSAGE.SET_NAME('PA','PA_BASELINE_FAILED');
3370 FND_MSG_PUB.add;
3371 END IF;
3372 l_return_status := FND_API.G_RET_STS_ERROR;
3373 END IF; -- (l_err_code < 0)
3374
3375 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
3376 THEN
3377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3378 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
3379 THEN
3380 RAISE FND_API.G_EXC_ERROR;
3381 END IF;
3382
3383 END IF; --(l_err_code <> 0)
3384
3385 /*Start - Changes for Federal Uptake - II */
3386
3387 l_federal_enabled := NVL(FND_PROFILE.value('FV_ENABLED'), 'N');
3388
3389 Select budget_type_code
3390 into l_budget_type_code
3391 from pa_budget_versions
3392 where budget_version_id = p_draft_version_id;
3393
3394
3395 If(l_federal_enabled = 'Y' AND l_budget_type_code is NOT NULL) then
3396
3397 SELECT budget_version_id, version_number
3398 INTo l_baseline_version_id, l_baseline_version_number
3399 FROM pa_budget_versions
3400 WHERE budget_type_code = l_budget_type_code
3401 AND project_id = p_project_id
3402 AND budget_status_code='B'
3403 AND current_flag = 'Y';
3404
3405 Begin
3406 SELECT budget_version_id
3407 INTo l_pre_baseline_version_id
3408 FROM pa_budget_versions pb
3409 WHERE pb.budget_type_code = l_budget_type_code
3410 AND pb.project_id = p_project_id
3411 AND pb.budget_status_code = 'B'
3412 AND pb.version_number = (l_baseline_version_number - 1);
3413 Exception
3414 When no_data_found then
3415 l_pre_baseline_version_id := NULL;
3416 End;
3417
3418
3419 PA_CLIENT_EXT_FV_BUDGET_INT.INSERT_BUDGET_LINES
3420 (p_project_id => p_project_id
3421 ,p_pre_baselined_version_id => l_pre_baseline_version_id
3422 ,p_baselined_budget_version_id => l_baseline_version_id
3423 ,x_rejection_code => l_rejection_code
3424 ,x_interface_status => l_interface_status) ;
3425
3426 If ((l_interface_status = 'True' or l_interface_status is NULL) and l_rejection_code is NULL) THEN
3427 NULL; --BEM/Third Party Client Extension Successful
3428 Else
3429 RAISE l_bem_failed;
3430 End if;
3431
3432 End if;
3433
3434 /*End - Changes for Federal Uptake - II */
3435
3436 END IF; --(nvl(p_fck_req_flag,'N')) = 'Y'
3437
3438
3439
3440 EXCEPTION
3441 WHEN FND_API.G_EXC_ERROR
3442 THEN
3443 ROLLBACK TO baseline_budget_wrappper;
3444 x_return_status := FND_API.G_RET_STS_ERROR;
3445 --fix done for Bug 6408021
3446 FND_MSG_PUB.Count_And_Get
3447 (p_count => x_msg_count ,
3448 p_data => x_msg_data );
3449
3450
3451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3452 THEN
3453 ROLLBACK TO baseline_budget_wrappper;
3454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3455 --fix done for Bug 6408021
3456 FND_MSG_PUB.Count_And_Get
3457 (p_count => x_msg_count ,
3458 p_data => x_msg_data );
3459
3460 WHEN L_BEM_FAILED
3461 THEN
3462 ROLLBACK TO baseline_budget_wrappper;
3463 x_return_status := FND_API.G_RET_STS_ERROR;
3464 x_msg_count := 1;
3465 x_msg_data := 'PA_FV_BUDGET_INT_FAILED';
3466 Begin
3467 Select meaning
3468 into l_rejection_reason
3469 from pa_lookups
3470 where lookup_code = l_rejection_code
3471 and lookup_type = 'PA_BUD_INTERFACE_REJ_CODE';
3472 Exception
3473 When no_data_found then
3474 l_rejection_reason := Null;
3475 End;
3476 PA_UTILS.Add_Message('PA',x_msg_data, 'Rejection Reason', l_rejection_reason);
3477
3478 WHEN OTHERS
3479 THEN
3480 ROLLBACK TO baseline_budget_wrappper;
3481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3482 FND_MSG_PUB.Add_Exc_Msg
3483 ( p_pkg_name => 'PA_BUDGET_UTILS'
3484 , p_procedure_name => 'BASELINE_BUDGET'
3485 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
3486 );
3487 FND_MSG_PUB.Count_And_Get
3488 (p_count => x_msg_count
3489 , p_data => x_msg_data );
3490
3491
3492
3493
3494 END Baseline_Budget;
3495 -- =================================================
3496
3497 ---------------------------------------------------------------------------
3498 --
3499
3500 --History:
3501 -- xx-xxx-xx who? - Created
3502 --
3503 -- 13-AUG-02 jwhite - Modified for FP model:
3504 -- Added filter to pa_resource_assignments,
3505 -- RESOURCE_ASSIGNMENT_TYPE = USER_ENTERED
3506 --
3507 -- 10-Feb-05 dbora Bug 4176059: Performance Fix: FP.M-B12
3508 -- Split cursor get_totals in to four separate
3509 -- cursors for each separate planning levels
3510
3511 procedure get_project_task_totals(x_budget_version_id in number,
3512 x_task_id in number,
3513 x_quantity_total in out NOCOPY number, --File.Sql.39 bug 4440895
3514 x_raw_cost_total in out NOCOPY number, --File.Sql.39 bug 4440895
3515 x_burdened_cost_total in out NOCOPY number, --File.Sql.39 bug 4440895
3516 x_revenue_total in out NOCOPY number, --File.Sql.39 bug 4440895
3517 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3518 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3519 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
3520 is
3521
3522 /****************************************************************
3523 How to use this API:
3524 This API can be used to get the totals at the Project Level
3525 or at the task level. If x_task_id is passed as a null value then
3526 project level totals are fetched. Otherwise task level totals are
3527 fetched. For task level totals, first the task level is determined.
3528 If the task level is top or intermediate level , then the amounts
3529 are rolled from the child tasks.
3530 ******************************************************************/
3531
3532 v_rollup_flag varchar2(1);
3533 old_stack varchar2(630);
3534
3535 cursor get_rollup_level is
3536 select 'P'
3537 from dual
3538 where x_task_id is null
3539 union
3540 select 'T'
3541 from pa_tasks
3542 where x_task_id is not null
3543 and task_id = x_task_id
3544 and parent_task_id is null
3545 union
3546 select 'M'
3547 from pa_tasks
3548 where x_task_id is not null
3549 and task_id = x_task_id
3550 and parent_task_id is not null
3551 and exists (select 'X'
3552 from pa_tasks
3553 where parent_task_id = x_task_id)
3554 union
3555 select 'L'
3556 from dual
3557 where x_task_id is not null
3558 and not exists (select 'X'
3559 from pa_tasks
3560 where parent_task_id = x_task_id);
3561
3562 --Bug 4176059: Performance Fix: FP.M-B12
3563 cursor get_project_totals is
3564 select labor_quantity,
3565 raw_cost,
3566 burdened_cost,
3567 revenue
3568 from pa_budget_versions
3569 where v_rollup_flag = 'P' -- Project Level
3570 and budget_version_id = x_budget_version_id;
3571
3572 --Bug 4176059: Performance Fix: FP.M-B12
3573 cursor get_top_task_totals is
3574 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
3575 SUM(NVL(l.RAW_COST,0)),
3576 SUM(NVL(l.BURDENED_COST,0)),
3577 SUM(NVL(l.REVENUE,0))
3578 from pa_tasks t,
3579 pa_budget_lines l ,
3580 pa_resource_assignments a
3581 where v_rollup_flag = 'T' -- Top Task Level
3582 and a.budget_version_id = x_budget_version_id
3583 and a.task_id = t.task_id
3584 and t.top_task_id = x_task_id
3585 and a.resource_assignment_id = l.resource_assignment_id
3586 and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
3587
3588 --Bug 4176059: Performance Fix: FP.M-B12
3589 cursor get_mid_task_totals is
3590 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
3591 SUM(NVL(l.RAW_COST,0)),
3592 SUM(NVL(l.BURDENED_COST,0)),
3593 SUM(NVL(l.REVENUE,0))
3594 from pa_budget_lines l,
3595 pa_resource_assignments a
3596 where v_rollup_flag = 'M' -- Middle Task Level
3597 and a.budget_version_id = x_budget_version_id
3598 and a.task_id in (select task_id
3599 from pa_tasks
3600 start with task_id = x_task_id
3601 connect by prior task_id = parent_task_id)
3602 and a.resource_assignment_id = l.resource_assignment_id
3603 and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
3604
3605 --Bug 4176059: Performance Fix: FP.M-B12
3606 cursor get_lowest_task_totals is
3607 select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
3608 SUM(NVL(l.RAW_COST,0)),
3609 SUM(NVL(l.BURDENED_COST,0)),
3610 SUM(NVL(l.REVENUE,0))
3611 from pa_budget_lines l,
3612 pa_resource_assignments a
3613 where v_rollup_flag = 'L' -- Lowest Task Level
3614 and a.budget_version_id = x_budget_version_id
3615 and a.task_id = x_task_id
3616 and a.resource_assignment_id = l.resource_assignment_id
3617 and NVL(a.RESOURCE_ASSIGNMENT_TYPE, 'USER_ENTERED') = 'USER_ENTERED';
3618
3619 begin
3620 x_err_code := 0;
3621 old_stack := x_err_stack;
3622 x_err_stack := x_err_stack || '->PA_BUDGET_UTILS.get_project_task_totals';
3623
3624 open get_rollup_level;
3625 fetch get_rollup_level into v_rollup_flag;
3626 close get_rollup_level;
3627
3628 x_err_stage := x_raw_cost_total;
3629
3630 /* Bug 4176059: Performance Fix: FP.M-B12--- restructured the following code block
3631 * to open any appropriate cursor to get the totals depending upon the planning level
3632 */
3633
3634 -- if x_task_id is not passed, open the project level cursor
3635 if x_task_id is null then
3636 -- opening the project level cursor
3637 open get_project_totals;
3638
3639 fetch get_project_totals
3640 into x_quantity_total,
3641 x_raw_cost_total,
3642 x_burdened_cost_total,
3643 x_revenue_total;
3644
3645 close get_project_totals;
3646 else -- task id is passed
3647 if v_rollup_flag = 'T' then
3648 -- top task level planning
3649 open get_top_task_totals;
3650
3651 fetch get_top_task_totals
3652 into x_quantity_total,
3653 x_raw_cost_total,
3654 x_burdened_cost_total,
3655 x_revenue_total;
3656
3657 close get_top_task_totals;
3658 elsif v_rollup_flag = 'M' then
3659 -- middle task level planning
3660 open get_mid_task_totals;
3661
3662 fetch get_mid_task_totals
3663 into x_quantity_total,
3664 x_raw_cost_total,
3665 x_burdened_cost_total,
3666 x_revenue_total;
3667
3668 close get_mid_task_totals;
3669 elsif v_rollup_flag = 'L' then
3670 -- lowest task level planning
3671 open get_lowest_task_totals;
3672
3673 fetch get_lowest_task_totals
3674 into x_quantity_total,
3675 x_raw_cost_total,
3676 x_burdened_cost_total,
3677 x_revenue_total;
3678
3679 close get_lowest_task_totals;
3680 end if; -- v_rollup_flag
3681 end if; -- x_task_id null
3682
3683 x_err_stack := old_stack;
3684
3685 exception
3686 when others then
3687 x_err_code := SQLCODE;
3688 return;
3689 end;
3690
3691 ---------------------------------------------------------------------------
3692 --
3693 -- This function returns a value 'Y' if the UOM passed
3694 -- is a currency UOM. Otherwise it returns 'N'.
3695 --
3696 Function Check_Currency_Uom (x_uom_code in varchar2)
3697 return varchar2 is
3698 cursor check_uom is
3699 select currency_uom_flag
3700 from pa_currency_uom_v
3701 where uom_code = x_uom_code;
3702
3703 v_currency_uom_flag varchar2(1);
3704
3705 Begin
3706 open check_uom;
3707 fetch check_uom into v_currency_uom_flag;
3708
3709 if check_uom%notfound then
3710 close check_uom; --Bug 5350429
3711 return 'Y';
3712 else
3713 close check_uom; --Bug 5350429
3714 return nvl(v_currency_uom_flag,'Y');
3715 end if;
3716
3717 End;
3718
3719 ---------------------------------------------------------------------------
3720 --
3721 -- This function returns the value of budget amount code
3722 -- associated with the budget type. Budget Amount Code
3723 -- determines whethere its a cost or a revenue budget.
3724 --
3725 Function get_budget_amount_code (x_budget_type_code in varchar2)
3726 return varchar2 is
3727 cursor get_budget_amount_code is
3728 select budget_amount_code
3729 from pa_budget_types
3730 where budget_type_code = x_budget_type_code;
3731
3732 v_budget_amount_code PA_BUDGET_TYPES.BUDGET_AMOUNT_CODE%TYPE;
3733
3734 Begin
3735 open get_budget_amount_code ;
3736 fetch get_budget_amount_code into v_budget_amount_code;
3737 close get_budget_amount_code;
3738
3739 return v_budget_amount_code;
3740 End;
3741
3742 ---------------------------------------------------------------------------
3743
3744 -- Assigning the value of Budget Entry Level Code to a global
3745 -- variable.
3746 Procedure set_entry_level_code(x_entry_level_code in varchar2) is
3747 Begin
3748 g_entry_level_code := x_entry_level_code;
3749 End;
3750
3751 ---------------------------------------------------------------------------
3752 -- Returning the value of global variable for Budget Entry Level Code
3753 Function get_entry_level_code return varchar2 is
3754 Begin
3755 return g_entry_level_code;
3756 End;
3757
3758
3759 -- =================================================
3760
3761 --Name: Get_Version_Approved_Code
3762 --Type: Procedure
3763 --
3764 --Description: This procedure is called both from this package and other
3765 -- packages.
3766 --
3767 -- This procedure returns the following:
3768 -- 1) For the r11.5.7 model:
3769 -- AC (Approved Cost)
3770 -- AR (Approved Revenue)
3771 -- NONE (Neither Approved Cost nor Approved Revenue)
3772 -- 2) For FP versions,
3773 -- AC (Approved Cost)
3774 -- AR (Approved Revenue)
3775 -- ALL (both Approved Cost and Revenue)
3776 -- NONE (Neither Approved Cost nor Approved Revenue)
3777 --
3778 --
3779 --Called subprograms: none
3780 --
3781 --
3782 --
3783 --History:
3784 -- 07-AUG-02 jwhite - Created
3785 --
3786
3787 PROCEDURE Get_Version_Approved_Code
3788 (
3789 p_budget_version_id IN NUMBER
3790 , x_approved_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3791 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3792 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3793 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3794 )
3795 IS
3796
3797 l_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE := NULL;
3798 l_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE := NULL;
3799
3800
3801 BEGIN
3802
3803 -- Assume Success
3804 x_return_status := FND_API.G_RET_STS_SUCCESS;
3805 x_msg_count := 0;
3806 x_msg_data := NULL;
3807
3808
3809 SELECT b.approved_cost_plan_type_flag, b.approved_rev_plan_type_flag
3810 INTO l_cost_flag, l_rev_flag
3811 FROM pa_budget_versions b
3812 WHERE b.budget_version_id = p_budget_version_id;
3813
3814
3815 IF ( nvl(l_cost_flag,'N') = 'Y'
3816 AND nvl(l_rev_flag,'N') = 'Y'
3817 )
3818 THEN
3819 x_approved_code := 'ALL';
3820
3821 ELSIF ( nvl(l_cost_flag,'N') = 'Y'
3822 AND nvl(l_rev_flag,'N') = 'N'
3823 )
3824 THEN
3825 x_approved_code := 'AC';
3826
3827 ELSIF ( nvl(l_cost_flag,'N') = 'N'
3828 AND nvl(l_rev_flag,'N') = 'Y'
3829 )
3830 THEN
3831 x_approved_code := 'AR';
3832 ELSE
3833 x_approved_code := 'NONE';
3834
3835 END IF;
3836
3837
3838 EXCEPTION
3839 WHEN OTHERS
3840 THEN
3841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3842 FND_MSG_PUB.Add_Exc_Msg
3843 ( p_pkg_name => 'PA_BUDGET_UTILS'
3844 , p_procedure_name => 'GET_VERSION_APPROVED_CODE'
3845 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
3846 );
3847 FND_MSG_PUB.Count_And_Get
3848 (p_count => x_msg_count ,
3849 p_data => x_msg_data );
3850 RETURN;
3851
3852
3853 END Get_Version_Approved_Code;
3854
3855
3856 -- =================================================
3857
3858 --Name: Get_Project_Currency_Info
3859 --Type: Procedure
3860 --
3861 --Description: This procedure is called both from this package and other
3862 -- packages.
3863 --
3864 -- This procedure may be called multiple times for a given
3865 -- project. For optimal performance, this procedure stores the
3866 -- selected values into package globals. When the G_Project_Id global
3867 -- differs from the p_project_id IN-parameter, this API does a fetch for
3868 -- the new project_id.
3869 --
3870 -- The G_Project_Id global is defaulted to "-1" in the package specification.
3871 --
3872 --
3873 --
3874 --
3875 --Called subprograms: none
3876 --
3877 --
3878 --
3879 --History:
3880 -- 14-AUG-02 jwhite - Created
3881 --
3882
3883 PROCEDURE Get_Project_Currency_Info
3884 (
3885 p_project_id IN NUMBER
3886 , x_projfunc_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3887 , x_project_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3888 , x_txn_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3889 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3890 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3891 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3892 )
3893
3894 IS
3895
3896
3897 BEGIN
3898
3899
3900 -- Assume Success
3901 x_return_status := FND_API.G_RET_STS_SUCCESS;
3902 x_msg_count := 0;
3903 x_msg_data := NULL;
3904
3905
3906 -- Fetch Currency Info for New Project
3907
3908 IF (pa_budget_utils.G_project_id <> p_project_id)
3909 THEN
3910
3911 SELECT projfunc_currency_code
3912 , project_currency_code
3913 , projfunc_currency_code
3914 INTO pa_budget_utils.G_projfunc_currency_code
3915 , pa_budget_utils.G_project_currency_code
3916 , pa_budget_utils.G_txn_currency_code
3917 FROM pa_projects_all
3918 WHERE project_id = p_project_id;
3919
3920 -- Save P_project_id to Skip this Fetch for Subsequent Calls
3921 pa_budget_utils.G_project_id := p_project_id;
3922
3923 END IF;
3924
3925 x_projfunc_currency_code := pa_budget_utils.G_projfunc_currency_code;
3926 x_project_currency_code := pa_budget_utils.G_project_currency_code;
3927 x_txn_currency_code := pa_budget_utils.G_txn_currency_code;
3928
3929
3930 EXCEPTION
3931 WHEN OTHERS
3932 THEN
3933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3934 FND_MSG_PUB.Add_Exc_Msg
3935 ( p_pkg_name => 'PA_BUDGET_UTILS'
3936 , p_procedure_name => 'GET_PROJECT_CURRENCY_INFO'
3937 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
3938 );
3939 FND_MSG_PUB.Count_And_Get
3940 (p_count => x_msg_count ,
3941 p_data => x_msg_data );
3942 RETURN;
3943
3944
3945
3946 END Get_Project_Currency_Info;
3947
3948
3949 -- =================================================
3950
3951 --Name: Get_Approved_FP_Info
3952 --Type: Procedure
3953 --
3954 --Description: This procedure is called primarily from Billing packages.
3955 --
3956 -- This procedure is used to determine whether the project is
3957 -- using the new FP model or using the r11.5.7 Budgets model.
3958 --
3959 -- If using the r11.5.7 Budgets model, the functional OUT-parameters
3960 -- are returned as NULL.
3961 --
3962 --
3963 --
3964 --
3965 --
3966 --Called subprograms: none
3967 --
3968 --
3969 --
3970 --History:
3971 -- 19-AUG-02 jwhite - Created
3972 --
3973
3974 Procedure Get_Approved_FP_Info
3975 (
3976 p_project_id IN NUMBER
3977 , x_ac_plan_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3978 , x_ar_plan_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3979 , x_ac_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3980 , x_ar_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3981 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3982 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3983 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3984 )
3985
3986 IS
3987
3988
3989 l_ac_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE := NULL;
3990 l_ar_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE := NULL;
3991 l_ac_version_type pa_budget_versions.version_type%TYPE := NULL;
3992 l_ar_version_type pa_budget_versions.version_type%TYPE := NULL;
3993
3994 l_dummy VARCHAR2(1) := 'N';
3995
3996
3997
3998 BEGIN
3999
4000
4001 -- Assume Success
4002 x_return_status := FND_API.G_RET_STS_SUCCESS;
4003 x_msg_count := 0;
4004 x_msg_data := NULL;
4005
4006
4007
4008 -- Check if r11.5.7 AC/AR Budget Versions Still Exist. If yes, then
4009 -- default r11.5.7 Budgets model.
4010
4011 BEGIN
4012
4013 SELECT 'Y'
4014 INTO l_dummy
4015 FROM dual
4016 WHERE EXISTS (select '1'
4017 from pa_budget_versions v
4018 where v.project_id = p_project_id
4019 and v.budget_type_code IN ('AC','AR')
4020 );
4021
4022 EXCEPTION
4023 WHEN NO_DATA_FOUND THEN
4024 l_dummy := 'N';
4025
4026 END;
4027
4028
4029 IF (l_dummy = 'Y')
4030 THEN
4031 -- Default r11.5.7 Budgets Model
4032
4033 x_ac_plan_type_id := NULL;
4034 x_ar_plan_type_id := NULL;
4035 x_ac_version_type := NULL;
4036 x_ar_version_type := NULL;
4037 RETURN;
4038
4039 ELSE
4040
4041 -- Find FP AC and AR Plan Type Ids, If Any --------------
4042
4043
4044 BEGIN
4045 -- AC
4046 SELECT o.fin_plan_type_id, v.version_type
4047 INTO l_ac_plan_type_id, l_ac_version_type
4048 FROM pa_proj_fp_options o
4049 , pa_budget_versions v
4050 WHERE o.fin_plan_version_id = v.budget_version_id
4051 AND v.approved_cost_plan_type_flag = 'Y'
4052 AND v.current_flag = 'Y'
4053 AND v.project_id = p_project_id;
4054
4055
4056 EXCEPTION
4057 WHEN NO_DATA_FOUND THEN
4058 l_ac_plan_type_id := NULL;
4059
4060 END;
4061
4062
4063 BEGIN
4064
4065 -- AR
4066 SELECT o.fin_plan_type_id, v.version_type
4067 INTO l_ar_plan_type_id, l_ar_version_type
4068 FROM pa_proj_fp_options o
4069 , pa_budget_versions v
4070 WHERE o.fin_plan_version_id = v.budget_version_id
4071 AND v.approved_rev_plan_type_flag = 'Y'
4072 AND v.current_flag = 'Y'
4073 AND v.project_id = p_project_id;
4074
4075
4076 EXCEPTION
4077 WHEN NO_DATA_FOUND THEN
4078 l_ar_plan_type_id := NULL;
4079
4080 END;
4081
4082
4083 IF (l_ac_plan_type_id IS NULL AND l_ar_plan_type_id IS NULL)
4084 THEN
4085 -- If Both AC and AR Plan Type ids are NULL, then Default the r11.5.7 Model
4086
4087 x_ac_plan_type_id := NULL;
4088 x_ar_plan_type_id := NULL;
4089 x_ac_version_type := NULL;
4090 x_ar_version_type := NULL;
4091 RETURN;
4092
4093 ELSE
4094 -- Assume FP Model
4095
4096 x_ac_plan_type_id := l_ac_plan_type_id;
4097 x_ar_plan_type_id := l_ar_plan_type_id;
4098 x_ac_version_type := l_ac_version_type;
4099 x_ar_version_type := l_ar_version_type;
4100 RETURN;
4101
4102 END IF; -- l_ac_plan_type_id IS NULL
4103
4104 END IF; -- l_dummy = 'Y'
4105
4106
4107 EXCEPTION
4108 WHEN OTHERS
4109 THEN
4110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4111 FND_MSG_PUB.Add_Exc_Msg
4112 ( p_pkg_name => 'PA_BUDGET_UTILS'
4113 , p_procedure_name => 'GET_APPROVED_FP_INFO'
4114 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
4115 );
4116 FND_MSG_PUB.Count_And_Get
4117 (p_count => x_msg_count ,
4118 p_data => x_msg_data );
4119 RETURN;
4120
4121
4122
4123 END Get_Approved_FP_Info;
4124
4125 -----------------------------------------------------------------------------
4126
4127 --Name: check_baseline_funding
4128 --Type: Function
4129 --
4130 --Description: This function is called from Oracle Projects, Project form
4131 -- (PAXPREPR.fmb).
4132 --
4133 -- This function returns either 0 or 1 based on the following
4134 -- 1. Returns 1 if the Project has Approved Revenue Budget(AR)
4135 -- (working/submitted/baselined) has budgets that use
4136 -- categorized resource lists either in new or old budgets model.
4137 -- 2. Returns 0 in all other cases.
4138 --
4139 --
4140 --
4141 --Called subprograms: None
4142 --
4143 --
4144 --
4145 --History:
4146 -- 08-JUL-2004 rravipat Created
4147 --
4148
4149 FUNCTION check_baseline_funding( x_project_id IN NUMBER )
4150 RETURN NUMBER IS
4151
4152 dummy NUMBER := 0;
4153
4154 BEGIN
4155 SELECT 1
4156 INTO dummy
4157 FROM dual
4158 WHERE EXISTS( SELECT 1
4159 FROM pa_budget_versions pbv,
4160 pa_resource_lists prl
4161 WHERE (pbv.budget_type_code = 'AR' OR -- old model
4162 pbv.budget_type_code IS NULL AND
4163 approved_rev_plan_type_flag = 'Y') -- new model
4164 AND pbv.ci_id is null -- filter change order versions
4165 AND pbv.resource_list_id = prl.resource_list_id
4166 AND prl.uncategorized_flag <> 'Y'
4167 AND pbv.project_id = x_project_id );
4168
4169 RETURN dummy;
4170 EXCEPTION
4171
4172 WHEN NO_DATA_FOUND THEN
4173 dummy := 0;
4174 RETURN dummy;
4175
4176 END;
4177
4178 -- --------------------------------------------------------------------------------
4179
4180 --Name: Set_Prj_Policy_Context
4181 --Type: Procedure
4182 --
4183 --Description: This procedure is called primarily from the following Budget Workflow packages
4184 -- related procedures:
4185 -- a) Budget Approval Workflow
4186 -- b) Budget Integration workflow
4187 --
4188 -- This procedure does the following:
4189 -- a) Derives org_id from project_id
4190 -- b) Passes org_id to mo_global.set_policy_context
4191 --
4192 -- This procedure assumes that the project_id had been fully validated
4193 -- by the calling object. Error checking is limited to any
4194 -- WHEN OTHERS ORA error.
4195 --Other Notes:
4196 --
4197 -- I had to add x_err_code to list to accomodate historical procedure standard
4198 -- used by the Budget Approval workflow.
4199 --
4200 --
4201 --
4202 --
4203 --Called subprograms: none
4204 --
4205 --
4206 --
4207 --History:
4208 -- 19-JUL-05 jwhite - Created
4209 --
4210
4211 Procedure Set_Prj_Policy_Context
4212 (
4213 p_project_id IN NUMBER
4214 , x_msg_count OUT NOCOPY NUMBER
4215 , x_msg_data OUT NOCOPY VARCHAR2
4216 , x_return_status OUT NOCOPY VARCHAR2
4217 , x_err_code OUT NOCOPY NUMBER
4218 )
4219 IS
4220
4221 l_org_id pa_projects_all.org_id%TYPE := NULL;
4222
4223 Begin
4224
4225
4226
4227 -- Assume Success
4228 x_return_status := FND_API.G_RET_STS_SUCCESS;
4229 x_msg_count := 0;
4230 x_msg_data := NULL;
4231 x_err_code := 0;
4232
4233
4234 -- Fetch Project Org_Id
4235 -- This should NOT fail since it should have been fully validated
4236 -- by the calling object.
4237
4238 SELECT org_id
4239 INTO l_org_id
4240 FROM pa_projects_all
4241 WHERE project_id = p_project_id;
4242
4243
4244 -- Set the Operating Unit Context
4245 mo_global.set_policy_context(p_access_mode => 'S'
4246 , p_org_id => l_org_id );
4247
4248
4249
4250 EXCEPTION
4251 WHEN OTHERS THEN
4252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4253 x_err_code := SQLCODE;
4254 FND_MSG_PUB.Add_Exc_Msg
4255 ( p_pkg_name => 'PA_BUDGET_UTILS'
4256 , p_procedure_name => 'SET_PRJ_POLICY_cONTEXT'
4257 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
4258 );
4259 FND_MSG_PUB.Count_And_Get
4260 (p_count => x_msg_count ,
4261 p_data => x_msg_data );
4262 RETURN;
4263
4264
4265 END Set_Prj_Policy_Context;
4266
4267
4268
4269 END pa_budget_utils;