[Home] [Help]
PACKAGE BODY: APPS.PA_FUNDING_CORE
Source
1 PACKAGE BODY pa_funding_core AS
2 /* $Header: PAXBIPFB.pls 120.4 2007/02/07 10:46:35 rgandhi ship $ */
3
4 -- ==========================================================================
5 -- Get_funding is used to get funding amount for a specified
6 -- project id, task id, budget type code
7 -- ==========================================================================
8 FUNCTION Get_Funding( p_project_id IN NUMBER,
9 p_task_id IN NUMBER DEFAULT NULL,
10 p_budget_type IN VARCHAR2) RETURN NUMBER IS
11 l_amount NUMBER:=0;
12 BEGIN
13
14 SELECT NVL(SUM(allocated_amount),0)
15 INTO l_amount
16 FROM pa_project_fundings
17 WHERE project_id = p_project_id
18 AND NVL(task_id,-99) = NVL(p_task_id ,-99)
19 AND RTRIM(budget_type_code) = RTRIM(p_budget_type);
20
21 RETURN(l_amount);
22
23 END Get_Funding;
24
25 --
26 --Name: check_fund_allocated
27 --Type: Function
28 --Description: This function will return 'Y' IF funds have been allocated to the
29 -- passed agreement ELSE will return 'N'
30 --
31 --
32 --Called subprograms: none
33 --
34 --
35 --
36 --History:
37 -- 16-APR-2000 Created Adwait Marathe.
38 --
39
40 FUNCTION check_fund_allocated
41 ( p_agreement_id IN NUMBER
42 ) RETURN VARCHAR2
43 IS
44 dummy number;
45 l_total_unbaselined_amount number;
46 l_total_baselined_amount number;
47
48 BEGIN
49 --dbms_output.put_line('Inside:PA_FUNDING_CORE.CHECK_FUND_ALLOCATED');
50 --dbms_output.put_line('Agreement_id = '||nvl(to_char(p_agreement_id),'NULL'));
51 Select sum(total_unbaselined_amount) , sum(total_baselined_amount)
52 Into l_total_unbaselined_amount, l_total_baselined_amount
53 From Pa_summary_project_fundings
54 Where agreement_id = p_agreement_id;
55 IF (nvl(l_total_unbaselined_amount, 0) = 0 AND
56 nvl(l_total_baselined_amount, 0) = 0) THEN
57 BEGIN
58 --dbms_output.put_line('NO MONEY!!!');
59 select 1
60 into dummy
61 from dual
62 where exists ( select 1
63 from pa_project_fundings
64 where agreement_id = p_agreement_id);
65
66 IF dummy = 1 THEN
67 return 'Y';
68 END IF;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 --dbms_output.put_line('NO RECORD!!!');
72 return 'N';
73 END;
74 ELSE
75 return 'Y';
76 END IF;
77 END check_fund_allocated;
78
79
80 --
81 --Name: check_accrued_billed_baselined
82 --Type: Function
83 --Description: This function will return 'Y'
84 -- Total amount of funds allocated is less than amount accrued or billed.
85 -- ELSE will return 'N' for given Projet_id, agreement_id, task_id and fund amount.
86 --
87 --Called subprograms: none
88 --
89 --History:
90 -- 16-APR-2000 Created Adwait Marathe.
91
92 FUNCTION check_accrued_billed_baselined
93 ( p_agreement_id IN NUMBER
94 ,p_project_id IN NUMBER
95 ,p_task_id IN NUMBER
96 ,p_amount IN NUMBER
97 ) RETURN VARCHAR2
98 IS
99 cursor c1 is
100 Select sign(p_amount +
101 greatest(nvl(total_accrued_amount, 0),
102 nvl(total_billed_amount, 0),
103 nvl(total_baselined_amount, 0) +
104 nvl(total_unbaselined_amount, 0)))
105 From pa_summary_project_fundings
106 Where project_id = p_project_id
107 And agreement_id = p_agreement_id
108 And nvl(task_id, 0) = nvl(p_task_id, 0);
109 mflag number;
110 BEGIN
111 open c1;
112 fetch c1 into mflag;
113
114 IF (c1%found) THEN
115 IF (mflag < 0) THEN
116 RETURN 'N';
117 END IF;
118 END IF;
119 close c1;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 RETURN 'Y';
123 WHEN OTHERS THEN
124 RETURN 'N' ;
125 END check_accrued_billed_baselined;
126
127
128
129 --
130 --Name: check_valid_project
131 --Type: Function
132 --Description: This function will return 'Y'
133 -- IF the project is a valid project ELSE will return 'N'
134 --
135 --Called subprograms: none
136 --
137 --History:
138 -- 16-APR-2000 Created Adwait Marathe.
139 -- 07-sep-2001 Modified for MCB2
140 -- Added new param p_project_id and used pa_proj_fund_valid_v
141
142 FUNCTION check_valid_project
143 (p_customer_id IN NUMBER,
144 p_project_id IN NUMBER,
145 p_agreement_id IN NUMBER /*Federal*/
146 ) RETURN VARCHAR2
147 IS
148 /*
149 Cursor c1 is
150 Select 1
151 From Pa_lookups lk, Pa_projects_all P , pa_project_types pt, pa_project_customers c
152 where (decode(p.template_flag, 'Y', 'Y', pa_security.allow_query(p.project_id)) = 'Y'
153 and decode(p.template_flag, 'Y', 'Y', pa_security.allow_update(p.project_id)) = 'Y' )
154 and pt.project_type = p.project_type and p.project_id = c.project_id
155 and pa_project_stus_utils.is_project_status_closed(p.project_status_code) = 'N'
156 and c.customer_id = p_customer_id and lk.lookup_type(+) = 'ALLOWABLE FUNDING LEVEL'
157 and lk.lookup_code(+) = pt.allowable_funding_level_code;
158 commented for mcb2 change */
159 Cursor c1 is select 1 from pa_proj_fund_valid_v
160 where project_id = p_project_id
161 and customer_id = p_customer_id
162 AND project_type_class_code = 'CONTRACT'; /* Added for bug 3017733 */
163
164 project_exists number;
165
166 l_return_status VARCHAR2(1):='N';
167 l_count NUMBER;
168 l_proj_type VARCHAR2(30);
169 l_proj_type1 VARCHAR2(30);
170
171 BEGIN
172 --dbms_output.put_line('Inside: PA_FUNDING_CORE.CHECK_VALID_PROJECT');
173 --dbms_output.put_line('Customer_id: '||nvl(to_char(p_customer_id),'NULL'));
174
175 Open c1;
176 fetch c1 into project_exists;
177
178 IF (c1%found) THEN
179 l_return_status := 'Y';
180 ELSE l_return_status := 'N';
181 END IF;
182 close c1;
183
184
185 /*Added for federal*/
186
187 IF (l_return_status ='Y') THEN
188
189 SELECT count(*)
190 INTO l_count
191 FROM pa_agreements
192 WHERE agreement_id = p_agreement_id
193 AND advance_amount >0;
194
195 IF (l_count >0) THEN
196
197 SELECT project_type
198 INTO l_proj_type
199 FROM pa_projects
200 WHERE project_id = p_project_id;
201
202 SELECT project_type
203 INTO l_proj_type1
204 FROM pa_project_types
205 WHERE project_type = l_proj_type
206 AND nvl(cc_prvdr_flag,'N') ='N';
207 END IF;
208
209 l_return_status := 'Y';
210 END IF;
211
212 return l_return_status;
213
214 EXCEPTION
215 WHEN no_data_found THEN return 'N';
216 END check_valid_project;
217
218 --
219 --Name: get_funding_id
220 --Type: FUNCTION
221 --Description: This function will get the corresponding function_id for the funding_id or funding _reference given
222 -- the corresponding funding reference.
223 --
224 --Called subprograms: none
225 --
226 --
227 --History:
228 -- 15-MAY-2000 Created Nikhil Mishra.
229 --
230
231 FUNCTION get_funding_id
232 ( p_funding_reference IN VARCHAR2
233 )
234 RETURN NUMBER
235 IS
236
237 CURSOR c1
238 IS
239 SELECT f.project_funding_id
240 FROM PA_PROJECT_FUNDINGS f
241 WHERE f.pm_funding_reference = p_funding_reference;
242
243 l_fund_rec1 c1%ROWTYPE;
244
245 BEGIN
246 --dbms_output.put_line('Inside: PA_FUNDING_CORE.GET_FUNDING_ID');
247 IF p_funding_reference is NOT NULL
248 OR (p_funding_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
249 THEN
250 OPEN c1;
251 FETCH c1 INTO l_fund_rec1;
252 IF c1%FOUND THEN
253 RETURN l_fund_rec1.project_funding_id;
254 END IF;
255 CLOSE c1;
256 END IF;
257
258 END get_funding_id;
259
260 --
261 --Name: check_valid_task
262 --Type: Function
263 --Description: This function will return 'Y'
264 -- IF the task is a valid task for project_id passed ELSE will return 'N'
265 --
266 --Called subprograms: none
267 --
268 --History:
269 -- 16-APR-2000 Created Adwait Marathe.
270
271
272
273 FUNCTION check_valid_task
274 ( p_project_id IN NUMBER
275 ,p_task_id IN NUMBER
276 ) RETURN VARCHAR2
277 IS
278 task_exists number;
279 BEGIN
280 --dbms_output.put_line('Inside: PA_FUNDING_CORE.CHECK_VALID_TASK');
281 --dbms_output.put_line('p_task_id:'||nvl(to_char(p_task_id),'NULL'));
282 IF p_task_id is not null THEN
283 --dbms_output.put_line('Task id is not null');
284 Select 1
285 Into task_exists
286 From Dual
287 Where exists(
288 select task_name, task_number, task_id
289 from pa_tasks_top_v
290 where project_id = p_project_id
291 and task_id = p_task_id );
292 --dbms_output.put_line('Returning Y');
293 RETURN 'Y';
294 END IF;
295
296 EXCEPTION
297 When no_data_found THEN
298 --dbms_output.put_line('Returning N');
299 RETURN 'N';
300 END check_valid_task;
301
302
303
304 --
305 --Name: CHECK_PROJECT_TEMPLATE
306 --Type: Function
307 --Description: This function will return 'Y'
308 -- IF the project is a template rpoject ELSE will return 'N'
309 --
310 --Called subprograms: none
311 --
312 --History:
313 -- 16-APR-2000 Created Adwait Marathe.
314
315
316
317 FUNCTION CHECK_PROJECT_TEMPLATE
318 (p_project_id IN NUMBER
319 ) RETURN VARCHAR2
320 IS
321 l_template_flag pa_projects_all.template_flag%type;
322 BEGIN
323 Select nvl(template_flag,'N')
324 into l_template_flag
325 From pa_projects_all
326 Where project_id = p_project_id;
327 RETURN l_template_flag;
328 EXCEPTION
329 When Others THEN RETURN 'N';
330 END CHECK_PROJECT_TEMPLATE;
331
332 --
333 --Name: check_task_fund_allowed
334 --Type: Function
335 --Description: This function will return 'Y'
336 -- IF the task level funding is allowed for the project ELSE will return 'N'
337 --
338 --Called subprograms: none
339 --
340 --History:
341 -- 16-APR-2000 Created Adwait Marathe.
342
343
344 FUNCTION check_task_fund_allowed
345 ( p_project_id IN NUMBER
346 ) RETURN VARCHAR2
347 IS
348 l_ALLOWABLE_FUNDING_LEVEL_CODE pa_project_types_all.ALLOWABLE_FUNDING_LEVEL_CODE%type;
349
350 BEGIN
351 Select ALLOWABLE_FUNDING_LEVEL_CODE
352 Into l_ALLOWABLE_FUNDING_LEVEL_CODE
353 From pa_project_types_all pt, pa_projects_all p
354 Where p.project_id=p_project_id
355 And p.project_type=pt.project_type
356 AND p.org_id = pt.org_id; /*Bug5374298 Removed NVL join on org_id*/
357
358 IF l_ALLOWABLE_FUNDING_LEVEL_CODE = 'T'
359 Then RETURN 'Y';
360 ELSE RETURN 'N';
361 END IF;
362 END check_task_fund_allowed;
363
364 --
365 --Name: check_task_fund_allowed
366 --Type: Function
367 --Description: This function will return 'Y'
368 -- IF there is not task level evenrs defined ELSE will return 'N'
369 --
370 --Called subprograms: none
371 --
372 --History:
373 -- 16-APR-2000 Created Adwait Marathe.
374
375 FUNCTION check_project_fund_allowed
376 ( p_project_id IN NUMBER
377 ,p_task_id In NUMBER
378 ) RETURN VARCHAR2
379 IS
380 Proj_ev_exists number;
381 BEGIN
382 IF p_task_id is not null THEN
383 select 1
384 into proj_ev_exists
385 from pa_events
386 where project_id = p_project_id
387 and task_Id IS NULL
388 and rownum = 1;
389
390 IF (proj_ev_exists = 1) THEN
391 RETURN 'Y';
392 ELSE
393 RETURN 'N';
394 END IF;
395 END IF;
396 END check_project_fund_allowed;
397
398 --
399 --Name: Validate_Level_Change
400 --Type: Function
401 --Description: This function will return 'Y' IF the funding level change is a valid one.
402 -- ELSE will return 'N'
403 -- this can be dome by checking
404 -- (1) Any expenditure item is revenue distributed ?
405 -- (2) Any event is revenue distributed?
406 -- (3) Any event is billed ??
407 --
408 --Called subprograms: pa_events_pkg.Is_Event_Billed
409 --
410 --
411 --
412 --History:
413 -- 05-MAY-2000 Created Adwait Marathe.
414 --
415
416 FUNCTION validate_level_change
417 ( p_project_id IN NUMBER
418 ,p_task_id IN NUMBER
419 )
420 RETURN varchar2 IS
421
422 V_valid_level_change Varchar2(1);
423 v_billed_flag varchar2(1);
424
425 BEGIN
426
427 BEGIN
428 -- Any expenditure item is revenue distributed ?
429 /* Commented for Bug 3457824
430 select 'N' into v_valid_level_change from dual
431 where exists
432 (select T.project_id
433 from pa_expenditure_items E, pa_tasks T
434 where T.project_id = p_project_id
435 and E.task_id = T.task_id
436 and E.revenue_distributed_flag <> 'N');
437 */
438
439 /* Code Added for Bug 3457824 starts here */
440
441 select 'N' into v_valid_level_change from dual
442 where exists
443 (select E.project_id
444 from pa_expenditure_items E
445 where E.project_id = p_project_id
446 and E.revenue_distributed_flag <> 'N');
447
448 /* Code Added for Bug 3457824 ends here */
449
450 EXCEPTION
451 WHEN NO_DATA_FOUND THEN
452 V_valid_level_change := 'Y';
453
454 END;
455
456
457 IF v_valid_level_change = 'Y' THEN
458
459 BEGIN
460 -- Any event is revenue distributed?
461
462 SELECT 'N'
463 INTO v_valid_level_change
464 FROM DUAL
465 WHERE EXISTS (
466 SELECT project_id FROM PA_EVENTS
467 WHERE project_id = p_project_id AND
468 revenue_distributed_flag ='Y');
469
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 v_valid_level_change := 'Y';
473
474 END;
475
476 END IF;
477
478 IF v_valid_level_change ='Y' THEN
479
480 -- Any event is billed ??
481
482 BEGIN
483
484 FOR evt_rec IN( SELECT project_id, task_id, event_num, bill_amount
485 FROM pa_events
486 WHERE project_id = p_project_id AND
487 revenue_distributed_flag ='N' AND
488 bill_amount <> 0 ) LOOP
489
490 v_billed_flag := pa_events_pkg.Is_Event_Billed(
491 evt_rec.project_id,
492 evt_rec.task_id,
493 evt_rec.event_num,
494 evt_rec.bill_amount);
495 IF v_billed_flag ='Y' THEN
496 v_valid_level_change := 'N';
497 exit;
498 END IF;
499
500 END LOOP;
501
502 EXCEPTION
503 WHEN NO_DATA_FOUND THEN
504 v_valid_level_change := 'Y';
505
506 END;
507
508 END IF;
509
510 RETURN V_valid_level_change;
511
512 END validate_level_change;
513
514
515 --
516 --Name: check_level_change
517 --Type: Function
518 --Description: This function will return 'Y' IF the funding level has been changed.
519 -- and the chenged level is a valis one. this can be done by
520 -- calling validate_level_change
521 --
522 --Called subprograms: validate_level_change
523 --
524 --
525 --
526 --History:
527 -- 05-MAY-2000 Created Adwait Marathe.
528 --
529
530
531 FUNCTION check_level_change
532 (p_agreement_id IN NUMBER
533 ,p_project_id IN NUMBER
534 ,p_task_id IN NUMBER
535 )
536 RETURN VARCHAR2
537 IS
538 V_task_id Number := 0;
539 V_Is_level_Valid Varchar2(1);
540
541 BEGIN
542 V_task_id := NULL;
543 SELECT task_id INTO V_task_id
544 FROM pa_project_fundings
545 WHERE project_funding_id =
546 (SELECT max(project_funding_id)
547 FROM pa_project_fundings
548 WHERE project_id = p_project_id
549 AND agreement_id = p_agreement_id);
550
551 IF (((V_task_id IS NULL) AND (p_task_id IS NULL)) OR
552 ((nvl(V_task_id,0) <> 0) AND (nvl(p_task_id,0) <> 0))) THEN
553 V_Is_level_Valid := 'Y';
554 ELSE
555 V_Is_level_Valid := validate_level_change(p_project_id,p_task_id);
556 END IF;
557 RETURN (V_Is_level_Valid);
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 V_Is_level_Valid := 'Y';
561 RETURN (V_Is_level_Valid);
562 END check_level_change;
563
564 --
565 --Name: check_proj_agr_fund_ok
566 --Type: Function
567 --Description: This function will return 'Y' if it is ok to fund a project from the
568 -- given agreement else 'N'
569 --Called subprograms: None
570 --
571 --
572 --History:
573 -- 24-JAN-2003 Created Puneet Rastogi.
574 --
575 /* added function bug 2756047 */
576 FUNCTION check_proj_agr_fund_ok
577 ( p_agreement_id IN NUMBER
578 ,p_project_id IN NUMBER
579 ) RETURN VARCHAR2
580 is
581
582 Is_agr_fund_ok varchar2(1) := 'N';
583
584 BEGIN
585 --dbms_output.put_line('Inside: PA_FUNDING_CORE.CHECK_PROJ_AGR_FUND_OK');
586 SELECT 'Y'
587 INTO Is_agr_fund_ok
588 FROM PA_PROJECTS_ALL P, PA_AGREEMENTS_ALL A
589 WHERE P.PROJECT_ID = p_project_id
590 AND A.AGREEMENT_ID = p_agreement_id
591 AND (P.multi_currency_billing_flag = 'Y'
592 OR (p.multi_currency_billing_flag = 'N'
593 AND p.projfunc_currency_code = a.agreement_currency_code))
594 AND not exists ( SELECT null
595 FROM PA_SUMMARY_PROJECT_FUNDINGS spf
596 WHERE spf.project_id = p.project_id
597 AND p.invproc_currency_type = 'FUNDING_CURRENCY'
598 AND spf.funding_currency_code <> a.agreement_currency_code
599 AND (spf.total_baselined_amount <> 0
600 OR spf.total_unbaselined_amount <> 0))
601 AND (nvl(p.template_flag,'N') = 'N'
602 OR ( p.template_flag = 'Y'
603 AND not exists ( select null
604 FROM PA_SUMMARY_PROJECT_FUNDINGS spf
605 where spf.project_id=p.project_id
606 and spf.agreement_id <> a.agreement_id))
607 )
608 AND (nvl(a.template_flag,'N') = 'N'
609 OR ( a.template_flag = 'Y'
610 AND not exists ( select null
611 FROM PA_SUMMARY_PROJECT_FUNDINGS spf
612 where spf.project_id <> p.project_id
613 and spf.agreement_id = a.agreement_id))
614 ) ;
615
616 --dbms_output.put_line('Outside: PA_FUNDING_CORE.CHECK_PROJ_AGR_FUND_OK');
617 RETURN 'Y';
618
619 EXCEPTION WHEN NO_DATA_FOUND THEN
620 --dbms_output.put_line('Outside:Exception PA_FUNDING_CORE.CHECK_PROJ_AGR_FUND_OK');
621 RETURN 'N';
622 END check_proj_agr_fund_ok;
623
624
625 --
626 --Name: check_proj_task_lvl_funding
627 --Type: Function
628 --Description: This function will return variour values. the interpretation of those
629 -- is as follows
630 -- "A" IF user is entering Project Level Funding WHEN task level funding exists
631 -- Or IF the revenue have been distributed. Message is PA_PROJ_FUND_NO_TASK_TRANS
632 -- "P" IF user in entering task level funding WHEN project level funding exists
633 -- Message is PA_BU_PROJECT_ALLOC_ONLY
634 -- "T" IF user is allocating funding at Project level WHEN Top task level
635 -- funding exists. Message is PA_BU_TASK_ALLOC_ONLY
636 -- "B" IF user change to task-level funding WHEN project-level events exist,
637 -- or IF Revenue has been distributed. Message is PA_TASK_FUND_NO_PROJ_TRANS
638 --Called subprograms: check_level_change
639 --
640 --
641 --
642 --History:
643 -- 05-MAY-2000 Created Adwait Marathe.
644 --
645
646
647 FUNCTION check_proj_task_lvl_funding
648 ( p_agreement_id IN NUMBER
649 ,p_project_id IN NUMBER
650 ,p_task_id IN NUMBER
651 ) RETURN VARCHAR2
652 is
653
654 dummy_amount number;
655 Is_level_change_valid varchar2(1);
656 BEGIN
657 --dbms_output.put_line('Inside: PA_FUNDING_CORE.CHECK_PROJ_TASK_LVL_FUNDING');
658
659 Is_level_change_valid := check_level_change(p_agreement_id,p_project_id,p_task_id);
660
661 IF p_task_id is null THEN
662
663 -- Project Level Funding Entered. Check for task level Funding
664 IF (Is_level_change_valid = 'N') THEN
665 --dbms_output.put_line('Returning A');
666 RETURN 'A';
667 -- control.app_error('ERROR', 'PA_PROJ_FUND_NO_TASK_TRANS');
668 END IF;
669
670
671 SELECT nvl(max(sum(nvl(allocated_amount,0))),0)
672 INTO dummy_amount
673 FROM PA_PROJECT_FUNDINGS P
674 WHERE P.PROJECT_ID = p_project_id
675 AND TASK_ID IS NOT NULL
676 AND BUDGET_TYPE_CODE IN ('BASELINE', 'DRAFT')
677 GROUP BY TASK_ID;
678
679 IF dummy_amount > 0 THEN
680 --dbms_output.put_line('Returning T');
681 RETURN 'T';
682 -- control.app_error('ERROR','PA_BU_TASK_ALLOC_ONLY');
683 END IF;
684
685 ELSE
686
687 -- Top Task Level Funding Entered. Check for project level Funding
688 IF (Is_level_change_valid = 'N') THEN
689 --dbms_output.put_line('Returning B');
690 RETURN 'B';
691 -- control.app_error('ERROR', 'PA_TASK_FUND_NO_PROJ_TRANS');
692 END IF;
693
694 SELECT NVL(SUM(ALLOCATED_AMOUNT), 0)
695 INTO dummy_amount
696 FROM PA_PROJECT_FUNDINGS P
697 WHERE P.PROJECT_ID = p_project_id
698 AND P.TASK_ID IS NULL
699 AND P.BUDGET_TYPE_CODE IN ('BASELINE', 'DRAFT');
700
701 IF dummy_amount > 0 THEN
702 --dbms_output.put_line('Returning P');
703 RETURN 'P';
704 -- control.app_error('ERROR','PA_BU_TASK_ALLOC_ONLY');
705 END IF;
706
707 END IF;
708 RETURN 'Y';
709 --dbms_output.put_line('Outside: PA_FUNDING_CORE.CHECK_PROJ_TASK_LVL_FUNDING');
710 END check_proj_task_lvl_funding;
711
712 --
713 --Name: check_project_type
714 --Type: Function
715 --Description: This function will return variour values. the interpretation of those
716 -- is as follows
717 -- "Y" IF the project type is CONTRACT
718 -- "N" IF the project type is not CONTRACT
719 --Called subprograms: N/A
720 --
721 --
722 --
723 --History:
724 -- 05-MAY-2000 Created Adwait Marathe.
725 --
726
727 FUNCTION check_project_type
728 (p_project_id IN NUMBER
729 ) RETURN VARCHAR2
730 IS
731 l_class_code pa_project_types_all.project_type_class_code%type;
732 BEGIN
733
734 Select pt.project_type_class_code
735 Into l_class_code
736 From pa_projects_all p, pa_project_types_all pt
737 where p.project_type = pt.project_type
738 AND p.org_id = pt.org_id /*Bug5374298 Removed NVL join on org_id*/
739 and p.project_id = p_project_id;
740
741 IF l_class_code = 'CONTRACT'
742 Then RETURN 'Y';
743 ELSE RETURN 'N';
744 END IF;
745 EXCEPTION
746 When Others Then RETURN 'N';
747 END check_project_type;
748
749 --
750 --Name: check_budget_type
751 --Type: Function
752 --Description: This function will return 'Y'IF the budget type is DRAFT else 'N'
753 --
754 --Called subprograms: none
755 --
756 --History:
757 -- 16-APR-2000 Created Adwait Marathe.
758 FUNCTION check_budget_type
759 (p_funding_id IN NUMBER
760 ) RETURN VARCHAR2
761 IS
762 l_budget_code pa_project_fundings.budget_type_code%type;
763 BEGIN
764
765 Select pf.budget_type_code
766 Into l_budget_code
767 From pa_project_fundings pf
768 where pf.project_funding_id = p_funding_id;
769
770 IF l_budget_code = 'DRAFT'
771 THEN RETURN 'Y';
772 ELSE RETURN 'N';
773 END IF;
774 EXCEPTION
775 When Others Then RETURN 'N';
776 END check_budget_type;
777
778 --
779 --Name: create_funding
780 --Type: PROCEDURE
781 --Description: This procedure is used to create a funding record in PA_PROJECT_FUNDINGS
782 --Called subprograms: pa_project_fundings_pkg.insert_row
783 --
784 --
785 --
786 --History:
787 -- 05-MAY-2000 Created Adwait Marathe.
788 -- 15-MAY-2000 Created Nikhil Mishra
789 -- 07-SEP-2001 Modified Srividya Sivaraman
790 -- Added all new columns corresponding to MCB2
791
792 PROCEDURE create_funding(
793 p_Rowid IN OUT NOCOPY VARCHAR2,/*File.sql.39*/
794 p_Project_Funding_Id IN OUT NOCOPY NUMBER,/*File.sql.39*/
795 p_Last_Update_Date IN DATE,
796 p_Last_Updated_By IN NUMBER,
797 p_Creation_Date IN DATE,
798 p_Created_By IN NUMBER,
799 p_Last_Update_Login IN NUMBER,
800 p_Agreement_Id IN NUMBER,
801 p_Project_Id IN NUMBER,
802 p_Task_id IN NUMBER,
803 p_Budget_Type_Code IN VARCHAR2,
804 p_Allocated_Amount IN NUMBER,
805 p_Date_Allocated IN DATE,
806 p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M changes
807 p_Attribute_Category IN VARCHAR2,
808 p_Attribute1 IN VARCHAR2,
809 p_Attribute2 IN VARCHAR2,
810 p_Attribute3 IN VARCHAR2,
811 p_Attribute4 IN VARCHAR2,
812 p_Attribute5 IN VARCHAR2,
813 p_Attribute6 IN VARCHAR2,
814 p_Attribute7 IN VARCHAR2,
815 p_Attribute8 IN VARCHAR2,
816 p_Attribute9 IN VARCHAR2,
817 p_Attribute10 IN VARCHAR2,
818 p_pm_funding_reference IN VARCHAR2,
819 p_pm_product_code IN VARCHAR2,
820 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
821 p_project_rate_date IN DATE DEFAULT NULL,
822 p_project_exchange_rate IN NUMBER DEFAULT NULL,
823 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
824 p_projfunc_rate_date IN DATE DEFAULT NULL,
825 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
826 x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
827 x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
828 p_funding_category IN VARCHAR2 /* Bug 2244796 */
829 )
830 IS
831 l_Project_Funding_Id NUMBER := p_Project_Funding_Id;
832 l_err_msg VARCHAR2(150);
833 l_err_code NUMBER;
834 l_funding_currency_code VARCHAR2(15);
835
836 l_project_currency_code VARCHAR2(15);
837 l_project_rate_type VARCHAR2(30);
838 l_project_rate_date DATE;
839 l_project_exchange_rate NUMBER;
840 l_project_allocated_amount NUMBER;
841
842 l_projfunc_currency_code VARCHAR2(15);
843 l_projfunc_rate_type VARCHAR2(30);
844 l_projfunc_rate_date DATE;
845 l_projfunc_exchange_rate NUMBER;
846 l_projfunc_allocated_amount NUMBER;
847
848 l_invproc_currency_code VARCHAR2(15);
849 l_invproc_rate_type VARCHAR2(30);
850 l_invproc_rate_date DATE;
851 l_invproc_exchange_rate NUMBER;
852 l_invproc_allocated_amount NUMBER;
853
854 l_revproc_currency_code VARCHAR2(15);
855 l_revproc_rate_type VARCHAR2(30);
856 l_revproc_rate_date DATE;
857 l_revproc_exchange_rate NUMBER;
858 l_revproc_allocated_amount NUMBER;
859 BEGIN
860
861 --dbms_output.put_line('Inside: pa_funding_core.create_funding');
862
863 --dbms_output.put_line( p_Project_Funding_Id);
864 --dbms_output.put_line( p_Last_Update_Date );
865 --dbms_output.put_line( p_Last_Updated_By);
866 --dbms_output.put_line( p_Creation_Date);
867 --dbms_output.put_line( p_Created_By);
868 --dbms_output.put_line( p_Last_Update_Login);
869 --dbms_output.put_line( p_Agreement_Id);
870 --dbms_output.put_line( p_Project_Id);
871 --dbms_output.put_line(nvl(to_char(p_Task_Id),'NULL'));
872 --dbms_output.put_line( p_Allocated_Amount);
873 --dbms_output.put_line( p_Date_Allocated);
874
875 --dbms_output.put_line('Inside: pa_funding_core.create_funding inserting row');
876
877 x_err_code := 0;
878 x_err_msg := NULL;
879
880 l_project_rate_type := p_project_rate_type;
881 l_project_rate_date := p_project_rate_date;
882 l_project_exchange_rate := p_project_exchange_rate;
883 l_projfunc_rate_type := p_projfunc_rate_type;
884 l_projfunc_rate_date := p_projfunc_rate_date;
885 l_projfunc_exchange_rate := p_projfunc_exchange_rate;
886
887
888 get_MCB2_attributes (
889 p_project_id => p_project_id,
890 p_agreement_id => p_agreement_id,
891 p_date_allocated => p_date_allocated,
892 p_allocated_amount => p_allocated_amount,
893 p_funding_currency_code => l_funding_currency_code,
894 p_project_currency_code => l_project_currency_code,
895 p_project_rate_type => l_project_rate_type,
896 p_project_rate_date => l_project_rate_date,
897 p_project_exchange_rate => l_project_exchange_rate,
898 p_project_allocated_amount => l_project_allocated_amount,
899 p_projfunc_currency_code => l_projfunc_currency_code,
900 p_projfunc_rate_type => l_projfunc_rate_type,
901 p_projfunc_rate_date => l_projfunc_rate_date,
902 p_projfunc_exchange_rate => l_projfunc_exchange_rate,
903 p_projfunc_allocated_amount => l_projfunc_allocated_amount,
904 p_invproc_currency_code => l_invproc_currency_code,
905 p_invproc_rate_type => l_invproc_rate_type,
906 p_invproc_rate_date => l_invproc_rate_date,
907 p_invproc_exchange_rate => l_invproc_exchange_rate,
908 p_invproc_allocated_amount => l_invproc_allocated_amount,
909 p_revproc_currency_code => l_revproc_currency_code,
910 p_revproc_rate_type => l_revproc_rate_type,
911 p_revproc_rate_date => l_revproc_rate_date,
912 p_revproc_exchange_rate => l_revproc_exchange_rate,
913 p_revproc_allocated_amount => l_revproc_allocated_amount,
914 p_validate_parameters => 'Y',
915 x_err_code => l_err_code,
916 x_err_msg => l_err_msg
917 );
918
919 x_err_code := l_err_code;
920 x_err_msg := l_err_msg;
921
922 if x_err_code = 0 then
923
924 pa_project_fundings_pkg.insert_row(
925 x_rowid => p_rowid,
926 x_project_funding_id => p_project_funding_id,
927 x_last_update_date => p_last_update_date,
928 x_last_updated_by => p_last_updated_by,
929 x_creation_date => p_creation_date,
930 x_created_by => p_created_by,
931 x_last_update_login => p_last_update_login,
932 x_agreement_id => p_agreement_id,
933 x_project_id => p_project_id,
934 x_task_id => p_task_id,
935 x_budget_type_code => p_budget_type_code,
936 x_allocated_amount => p_allocated_amount,
937 x_date_allocated => p_date_allocated,
938 X_Control_Item_ID => p_Control_Item_ID, -- FP_M changes
939 x_attribute_category => p_attribute_category,
940 x_attribute1 => p_attribute1,
941 x_attribute2 => p_attribute2,
942 x_attribute3 => p_attribute3,
943 x_attribute4 => p_attribute4,
944 x_attribute5 => p_attribute5,
945 x_attribute6 => p_attribute6,
946 x_attribute7 => p_attribute7,
947 x_attribute8 => p_attribute8,
948 x_attribute9 => p_attribute9,
949 x_attribute10 => p_attribute10,
950 x_pm_funding_reference => p_pm_funding_reference,
951 x_pm_product_code => p_pm_product_code,
952 x_funding_currency_code => l_funding_currency_code,
953 x_project_currency_code => l_project_currency_code,
954 x_project_rate_type => l_project_rate_type,
955 x_project_rate_date => l_project_rate_date,
956 x_project_exchange_rate => l_project_exchange_rate,
957 x_project_allocated_amount => l_project_allocated_amount,
958 x_projfunc_currency_code => l_projfunc_currency_code,
959 x_projfunc_rate_type => l_projfunc_rate_type,
960 x_projfunc_rate_date => l_projfunc_rate_date,
961 x_projfunc_exchange_rate => l_projfunc_exchange_rate,
962 x_projfunc_allocated_amount => l_projfunc_allocated_amount,
963 x_invproc_currency_code => l_invproc_currency_code,
964 x_invproc_rate_type => l_invproc_rate_type,
965 x_invproc_rate_date => l_invproc_rate_date,
966 x_invproc_exchange_rate => l_invproc_exchange_rate,
967 x_invproc_allocated_amount => l_invproc_allocated_amount,
968 x_revproc_currency_code => l_revproc_currency_code,
969 x_revproc_rate_type => l_revproc_rate_type,
970 x_revproc_rate_date => l_revproc_rate_date,
971 x_revproc_exchange_rate => l_revproc_exchange_rate,
972 x_revproc_allocated_amount => l_revproc_allocated_amount,
973 x_funding_category => p_funding_category /* For Bug2244796 */
974 );
975
976 end if;
977
978 --dbms_output.put_line('Done: create_funding');
979 -- summary_funding.insert_row;
980 EXCEPTION
981
982 WHEN OTHERS THEN
983 --dbms_output.put_line(SQLERRM);
984 -- Null added by johnson P
985 x_err_code := SQLCODE;
986 x_err_msg := SQLERRM;
987 p_Project_Funding_Id := l_Project_Funding_Id;
988
989 END create_funding;
990
991 --
992 --Name: update_funding
993 --Type: PROCEDURE
994 --Description: This procedure is used to create a funding record in PA_PROJECT_FUNDINGS
995 --Called subprograms: pa_project_fundings_pkg.update_row
996 --
997 --
998 --
999 --History:
1000 -- 05-MAY-2000 Created Adwait Marathe.
1001 -- 15-MAY-2000 Created Nikhil Mishra.
1002
1003 PROCEDURE Update_funding(
1004 p_Project_Funding_Id IN NUMBER,
1005 p_Last_Update_Date IN DATE,
1006 p_Last_Updated_By IN NUMBER,
1007 p_Last_Update_Login IN NUMBER,
1008 p_Agreement_Id IN NUMBER,
1009 p_Project_Id IN NUMBER,
1010 p_Task_id IN NUMBER,
1011 p_Budget_Type_Code IN VARCHAR2,
1012 p_Allocated_Amount IN NUMBER,
1013 p_Date_Allocated IN DATE,
1014 p_Attribute_Category IN VARCHAR2,
1015 p_Attribute1 IN VARCHAR2,
1016 p_Attribute2 IN VARCHAR2,
1017 p_Attribute3 IN VARCHAR2,
1018 p_Attribute4 IN VARCHAR2,
1019 p_Attribute5 IN VARCHAR2,
1020 p_Attribute6 IN VARCHAR2,
1021 p_Attribute7 IN VARCHAR2,
1022 p_Attribute8 IN VARCHAR2,
1023 p_Attribute9 IN VARCHAR2,
1024 p_Attribute10 IN VARCHAR2,
1025 p_pm_funding_reference IN VARCHAR2,
1026 p_pm_product_code IN VARCHAR2,
1027 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
1028 p_project_rate_date IN DATE DEFAULT NULL,
1029 p_project_exchange_rate IN NUMBER DEFAULT NULL,
1030 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
1031 p_projfunc_rate_date IN DATE DEFAULT NULL,
1032 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
1033 x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
1034 x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
1035 p_funding_category IN VARCHAR2 /* Bug 2244796 */
1036
1037 )
1038
1039 IS
1040 CURSOR C IS
1041 SELECT
1042 rowid
1043 FROM PA_PROJECT_FUNDINGS
1044 WHERE project_funding_id = p_Project_Funding_Id;
1045 fun_rec C%ROWTYPE;
1046 l_funding_currency_code VARCHAR2(15);
1047 l_project_currency_code VARCHAR2(15);
1048 l_project_rate_type VARCHAR2(30);
1049 l_project_rate_date DATE;
1050 l_project_exchange_rate NUMBER;
1051 l_project_allocated_amount NUMBER;
1052 l_projfunc_currency_code VARCHAR2(15);
1053 l_projfunc_rate_type VARCHAR2(30);
1054 l_projfunc_rate_date DATE;
1055 l_projfunc_exchange_rate NUMBER;
1056 l_projfunc_allocated_amount NUMBER;
1057 l_invproc_currency_code VARCHAR2(15);
1058 l_invproc_rate_type VARCHAR2(30);
1059 l_invproc_rate_date DATE;
1060 l_invproc_exchange_rate NUMBER;
1061 l_invproc_allocated_amount NUMBER;
1062 l_revproc_currency_code VARCHAR2(15);
1063 l_revproc_rate_type VARCHAR2(30);
1064 l_revproc_rate_date DATE;
1065 l_revproc_exchange_rate NUMBER;
1066 l_revproc_allocated_amount NUMBER;
1067
1068 l_err_msg VARCHAR2(150);
1069 l_err_code NUMBER;
1070 BEGIN
1071 x_err_code := 0;
1072 x_err_msg := NULL;
1073
1074 OPEN C;
1075 FETCH C INTO fun_rec;
1076 IF C%FOUND THEN
1077
1078 l_project_rate_type := p_project_rate_type;
1079 l_project_rate_date := p_project_rate_date;
1080 l_project_exchange_rate := p_project_exchange_rate;
1081 l_projfunc_rate_type := p_projfunc_rate_type;
1082 l_projfunc_rate_date := p_projfunc_rate_date;
1083 l_projfunc_exchange_rate := p_projfunc_exchange_rate;
1084
1085 get_MCB2_attributes (
1086 p_project_id => p_project_id,
1087 p_agreement_id => p_agreement_id,
1088 p_date_allocated => p_date_allocated,
1089 p_allocated_amount => p_allocated_amount,
1090 p_funding_currency_code => l_funding_currency_code,
1091 p_project_currency_code => l_project_currency_code,
1092 p_project_rate_type => l_project_rate_type,
1093 p_project_rate_date => l_project_rate_date,
1094 p_project_exchange_rate => l_project_exchange_rate,
1095 p_project_allocated_amount => l_project_allocated_amount,
1096 p_projfunc_currency_code => l_projfunc_currency_code,
1097 p_projfunc_rate_type => l_projfunc_rate_type,
1098 p_projfunc_rate_date => l_projfunc_rate_date,
1099 p_projfunc_exchange_rate => l_projfunc_exchange_rate,
1100 p_projfunc_allocated_amount => l_projfunc_allocated_amount,
1101 p_invproc_currency_code => l_invproc_currency_code,
1102 p_invproc_rate_type => l_invproc_rate_type,
1103 p_invproc_rate_date => l_invproc_rate_date,
1104 p_invproc_exchange_rate => l_invproc_exchange_rate,
1105 p_invproc_allocated_amount => l_invproc_allocated_amount,
1106 p_revproc_currency_code => l_revproc_currency_code,
1107 p_revproc_rate_type => l_revproc_rate_type,
1108 p_revproc_rate_date => l_revproc_rate_date,
1109 p_revproc_exchange_rate => l_revproc_exchange_rate,
1110 p_revproc_allocated_amount => l_revproc_allocated_amount,
1111 p_validate_parameters => 'Y',
1112 x_err_code => l_err_code,
1113 x_err_msg => l_err_msg
1114 );
1115 x_err_code := l_err_code;
1116 x_err_msg := l_err_msg;
1117
1118 if x_err_code = 0 then
1119 pa_project_fundings_pkg.update_row(
1120 x_rowid => fun_rec.rowid,
1121 x_project_funding_id => p_project_funding_id,
1122 x_last_update_date => p_last_update_date,
1123 x_last_updated_by => p_last_updated_by,
1124 x_last_update_login => p_last_update_login,
1125 x_agreement_id => p_agreement_id,
1126 x_project_id => p_project_id,
1127 x_task_id => p_task_id,
1128 x_budget_type_code => p_budget_type_code,
1129 x_allocated_amount => p_allocated_amount,
1130 x_date_allocated => p_date_allocated,
1131 x_attribute_category => p_attribute_category,
1132 x_attribute1 => p_attribute1,
1133 x_attribute2 => p_attribute2,
1134 x_attribute3 => p_attribute3,
1135 x_attribute4 => p_attribute4,
1136 x_attribute5 => p_attribute5,
1137 x_attribute6 => p_attribute6,
1138 x_attribute7 => p_attribute7,
1139 x_attribute8 => p_attribute8,
1140 x_attribute9 => p_attribute9,
1141 x_attribute10 => p_attribute10,
1142 x_pm_funding_reference => p_pm_funding_reference,
1143 x_pm_product_code => p_pm_product_code,
1144 x_funding_currency_code => l_funding_currency_code,
1145 x_project_currency_code => l_project_currency_code,
1146 x_project_rate_type => l_project_rate_type,
1147 x_project_rate_date => l_project_rate_date,
1148 x_project_exchange_rate => l_project_exchange_rate,
1149 x_project_allocated_amount => l_project_allocated_amount,
1150 x_projfunc_currency_code => l_projfunc_currency_code,
1151 x_projfunc_rate_type => l_projfunc_rate_type,
1152 x_projfunc_rate_date => l_projfunc_rate_date,
1153 x_projfunc_exchange_rate => l_projfunc_exchange_rate,
1154 x_projfunc_allocated_amount => l_projfunc_allocated_amount,
1155 x_invproc_currency_code => l_invproc_currency_code,
1156 x_invproc_rate_type => l_invproc_rate_type,
1157 x_invproc_rate_date => l_invproc_rate_date,
1158 x_invproc_exchange_rate => l_invproc_exchange_rate,
1159 x_invproc_allocated_amount => l_invproc_allocated_amount,
1160 x_revproc_currency_code => l_revproc_currency_code,
1161 x_revproc_rate_type => l_revproc_rate_type,
1162 x_revproc_rate_date => l_revproc_rate_date,
1163 x_revproc_exchange_rate => l_revproc_exchange_rate,
1164 x_revproc_allocated_amount => l_revproc_allocated_amount,
1165 x_funding_category => p_funding_category /* Bug 2244796 */
1166 );
1167
1168 END IF;
1169
1170 END IF;
1171 CLOSE C;
1172 -- summary_funding.update_row;
1173
1174 EXCEPTION
1175
1176 WHEN OTHERS THEN
1177 --dbms_output.put_line(SQLERRM);
1178 x_err_code := SQLCODE;
1179 x_err_msg := SQLERRM;
1180
1181
1182 END update_funding;
1183
1184 --
1185 --Name: delete_funding
1186 --Type: PROCEDURE
1187 --Description: This procedure is used to delete a funding record in PA_PROJECT_FUNDINGS
1188 --Called subprograms: pa_project_fundings_pkg.delete_row
1189 --
1190 --
1191 --
1192 --History:
1193 -- 05-MAY-2000 Created Adwait Marathe.
1194 -- 15-MAY-2000 Created Nikhil Mishra
1195
1196 PROCEDURE Delete_funding(p_project_funding_id IN NUMBER)
1197 is
1198 CURSOR C IS
1199 SELECT rowid
1200 FROM PA_PROJECT_FUNDINGS
1201 WHERE project_funding_id = p_project_funding_id;
1202 fun_row_id VARCHAR2(2000);
1203 BEGIN
1204 OPEN C;
1205 FETCH C INTO fun_row_id;
1206 IF C%FOUND THEN
1207 --dbms_output.put_line('yes');
1208 pa_project_fundings_pkg.delete_row(fun_row_id);
1209 END IF;
1210 CLOSE C;
1211 END delete_funding;
1212 --
1213 --Name: lock_funding
1214 --Type: PROCEDURE
1215 --Description: This procedure is used to lock a funding record in PA_PROJECT_FUNDINGS
1216 --Called subprograms: pa_project_fundings_pkg.lock_row
1217 --
1218 --
1219 --
1220 --History:
1221 -- 05-MAY-2000 Created Adwait Marathe.
1222 -- 15-MAY-2000 Created Nikhil Mishra
1223
1224
1225 PROCEDURE Lock_funding
1226 (p_Project_Funding_Id IN NUMBER)
1227 is
1228 CURSOR C IS
1229 SELECT rowid,
1230 project_funding_id,
1231 agreement_id,
1232 project_id,
1233 task_id,
1234 budget_type_code,
1235 allocated_amount,
1236 date_allocated,
1237 attribute_category,
1238 attribute1,
1239 attribute2,
1240 attribute3,
1241 attribute4,
1242 attribute5,
1243 attribute6,
1244 attribute7,
1245 attribute8,
1246 attribute9,
1247 attribute10,
1248 pm_funding_reference,
1249 pm_product_code,
1250 funding_currency_code, project_currency_code, project_rate_type,
1251 project_rate_date, project_exchange_rate, project_allocated_amount,
1252 projfunc_currency_code, projfunc_rate_type, projfunc_rate_date,
1253 projfunc_exchange_rate, projfunc_allocated_amount,
1254 funding_category /* For Bug2244796 */
1255 FROM PA_PROJECT_FUNDINGS
1256 WHERE project_funding_id = p_Project_Funding_Id;
1257 fun_rec C%ROWTYPE;
1258 BEGIN
1259 OPEN C;
1260 FETCH C INTO fun_rec;
1261 IF C%FOUND THEN
1262 pa_project_fundings_pkg.lock_row(
1263 fun_rec.rowid,
1264 fun_rec.project_funding_id,
1265 fun_rec.agreement_id,
1266 fun_rec.project_id,
1267 fun_rec.task_id,
1268 fun_rec.budget_type_code,
1269 fun_rec.allocated_amount,
1270 fun_rec.date_allocated,
1271 fun_rec.attribute_category,
1272 fun_rec.attribute1,
1273 fun_rec.attribute2,
1274 fun_rec.attribute3,
1275 fun_rec.attribute4,
1276 fun_rec.attribute5,
1277 fun_rec.attribute6,
1278 fun_rec.attribute7,
1279 fun_rec.attribute8,
1280 fun_rec.attribute9,
1281 fun_rec.attribute10,
1282 fun_rec.pm_funding_reference,
1283 fun_rec.pm_product_code,
1284 fun_rec.funding_currency_code,
1285 fun_rec.project_currency_code,
1286 fun_rec.project_rate_type,
1287 fun_rec.project_rate_date,
1288 fun_rec.project_exchange_rate,
1289 fun_rec.project_allocated_amount,
1290 fun_rec.projfunc_currency_code,
1291 fun_rec.projfunc_rate_type,
1292 fun_rec.projfunc_rate_date,
1293 fun_rec.projfunc_exchange_rate,
1294 fun_rec.projfunc_allocated_amount,
1295 fun_rec.funding_category /* For Bug2244796 */
1296 );
1297 END IF;
1298 CLOSE C;
1299 END lock_funding;
1300 --
1301 --Name: summary_funding_insert_row
1302 --Type: Procedure
1303 --Description: This procedure inserts row(s) in to PA_SUMMARY_PROJECT_FUNDINGS.
1304 --
1305 --Called subprograms: summary_fundings_update_row
1306 --
1307 --
1308 --
1309 --History:
1310 -- 15-MAY-2000 Created Nikhil Mishra.
1311 -- 21-AUG-2000 Modified Srividya.
1312 -- Added all columns corresponding to MCB2
1313 --
1314
1315 PROCEDURE summary_funding_insert_row
1316 (p_agreement_id IN NUMBER
1317 ,p_project_id IN NUMBER
1318 ,p_task_id IN NUMBER
1319 ,p_login_id IN VARCHAR2
1320 ,p_user_id IN VARCHAR2
1321 ,p_budget_type_code IN VARCHAR2
1322 )
1323 IS
1324 BEGIN
1325 declare
1326 cursor c1 is
1327 select 1
1328 from pa_summary_project_fundings
1329 where project_id = p_project_id
1330 and agreement_id = p_agreement_id
1331 and nvl(task_id, 0) = nvl(p_task_id, 0);
1332 dummy number;
1333
1334 BEGIN
1335
1336 open c1;
1337 fetch c1 into dummy;
1338
1339 IF (c1%found) THEN
1340 pa_funding_core.summary_funding_update_row ( p_agreement_id
1341 ,p_project_id
1342 ,p_task_id
1343 ,p_login_id
1344 ,p_user_id
1345 ,p_budget_type_code
1346 );
1347
1348
1349 ELSE
1350
1351 INSERT INTO PA_SUMMARY_PROJECT_FUNDINGS
1352 (AGREEMENT_ID, PROJECT_ID, TASK_ID,
1353 TOTAL_BASELINED_AMOUNT, TOTAL_UNBASELINED_AMOUNT,
1354 TOTAL_ACCRUED_AMOUNT, TOTAL_BILLED_AMOUNT,
1355 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1356 CREATION_DATE, CREATED_BY, FUNDING_CURRENCY_CODE,
1357 PROJECT_CURRENCY_CODE, PROJECT_BASELINED_AMOUNT,
1358 PROJECT_UNBASELINED_AMOUNT, PROJECT_ACCRUED_AMOUNT,
1359 PROJECT_BILLED_AMOUNT,
1360 PROJFUNC_CURRENCY_CODE, PROJFUNC_BASELINED_AMOUNT,
1361 PROJFUNC_UNBASELINED_AMOUNT, PROJFUNC_ACCRUED_AMOUNT,
1362 PROJFUNC_BILLED_AMOUNT,
1363 INVPROC_CURRENCY_CODE, INVPROC_BASELINED_AMOUNT,
1364 INVPROC_UNBASELINED_AMOUNT,
1365 INVPROC_BILLED_AMOUNT,
1366 REVPROC_CURRENCY_CODE, REVPROC_BASELINED_AMOUNT,
1367 REVPROC_UNBASELINED_AMOUNT, REVPROC_ACCRUED_AMOUNT)
1368 SELECT AGREEMENT_ID,PROJECT_ID,TASK_ID,
1369 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1370 NVL(ALLOCATED_AMOUNT,0))),0),
1371 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1372 NVL(ALLOCATED_AMOUNT,0))),0),
1373 0, 0, p_login_id, trunc(SYSDATE), p_user_id,
1374 trunc(SYSDATE), p_user_id, FUNDING_CURRENCY_CODE,
1375 PROJECT_CURRENCY_CODE,
1376 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1377 NVL(PROJECT_ALLOCATED_AMOUNT,0))),0),
1378 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1379 NVL(PROJECT_ALLOCATED_AMOUNT,0))),0),
1380 0, 0,
1381 PROJFUNC_CURRENCY_CODE,
1382 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1383 NVL(PROJFUNC_ALLOCATED_AMOUNT,0))),0),
1384 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1385 NVL(PROJFUNC_ALLOCATED_AMOUNT,0))),0),
1386 0, 0,
1387 INVPROC_CURRENCY_CODE,
1388 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1389 NVL(INVPROC_ALLOCATED_AMOUNT,0))),0),
1390 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1391 NVL(INVPROC_ALLOCATED_AMOUNT,0))),0),
1392 0,
1393 REVPROC_CURRENCY_CODE,
1394 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1395 NVL(REVPROC_ALLOCATED_AMOUNT,0))),0),
1396 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1397 NVL(REVPROC_ALLOCATED_AMOUNT,0))),0),
1398 0
1399 FROM PA_PROJECT_FUNDINGS
1400 WHERE BUDGET_TYPE_CODE IN ('BASELINE','DRAFT')
1401 AND PROJECT_ID = p_project_id
1402 AND AGREEMENT_ID = p_agreement_id
1403 AND NVL(TASK_ID,0) = NVL(p_task_id,0)
1404 AND NOT EXISTS
1405 (select NULL from PA_SUMMARY_PROJECT_FUNDINGS S
1406 WHERE s.PROJECT_ID = p_project_id
1407 AND s.AGREEMENT_ID = p_agreement_id
1408 AND NVL(s.TASK_ID,0) = NVL(p_task_id,0))
1409 GROUP BY AGREEMENT_ID,PROJECT_ID,TASK_ID,FUNDING_CURRENCY_CODE,
1410 PROJECT_CURRENCY_CODE, PROJFUNC_CURRENCY_CODE,
1411 INVPROC_CURRENCY_CODE, REVPROC_CURRENCY_CODE
1412 ;
1413 END IF;
1414 close c1;
1415 exception
1416 WHEN NO_DATA_FOUND THEN
1417 pa_funding_core.summary_funding_update_row ( p_agreement_id
1418 ,p_project_id
1419 ,p_task_id
1420 ,p_login_id
1421 ,p_user_id
1422 ,p_budget_type_code
1423 );
1424 WHEN OTHERS THEN
1425 raise ;
1426
1427 end;
1428 END summary_funding_insert_row;
1429
1430
1431 --
1432 --Name: summary_fundings_update_row
1433 --Type: Procedure
1434 --Description: This procedure updates row(s) in to PA_SUMMARY_PROJECT_FUNDINGS.
1435 --
1436 --Called subprograms: pa_agreement_utils.summary_fundings_insert_row
1437 --
1438 --
1439 --
1440 --History:
1441 -- 15-MAY-2000 Created Nikhil Mishra.
1442 -- 21-AUG-2000 Modified Srividya.
1443 -- Added all columns corresponding to MCB2
1444 --
1445 PROCEDURE summary_funding_update_row
1446 (p_agreement_id IN NUMBER
1447 ,p_project_id IN NUMBER
1448 ,p_task_id IN NUMBER
1449 ,p_login_id IN VARCHAR2
1450 ,p_user_id IN VARCHAR2
1451 ,p_budget_type_code IN VARCHAR2
1452 )
1453 IS
1454 BEGIN
1455 --dbms_output.put_line('Inside: PA_AGREEMENT_CORE.SUMMARY_FUNDING_UPDATE_ROW');
1456 IF (p_budget_type_code = 'DRAFT') THEN
1457
1458 UPDATE PA_SUMMARY_PROJECT_FUNDINGS S
1459 SET (S.TOTAL_UNBASELINED_AMOUNT, S.PROJECT_UNBASELINED_AMOUNT,
1460 S.PROJFUNC_UNBASELINED_AMOUNT,
1461 S.INVPROC_UNBASELINED_AMOUNT, S.REVPROC_UNBASELINED_AMOUNT) =
1462 (SELECT SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1463 'DRAFT',nvl(F.ALLOCATED_AMOUNT,0))),
1464 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1465 'DRAFT',nvl(F.PROJECT_ALLOCATED_AMOUNT,0))),
1466 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1467 'DRAFT',nvl(F.PROJFUNC_ALLOCATED_AMOUNT,0))),
1468 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1469 'DRAFT',nvl(F.INVPROC_ALLOCATED_AMOUNT,0))),
1470 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1471 'DRAFT',nvl(F.REVPROC_ALLOCATED_AMOUNT,0)))
1472 FROM PA_PROJECT_FUNDINGS F
1473 WHERE F.PROJECT_ID = S.PROJECT_ID
1474 AND F.AGREEMENT_ID = S.AGREEMENT_ID
1475 AND NVL(F.TASK_ID,0) = NVL(S.TASK_ID,0)
1476 GROUP BY F.AGREEMENT_ID, F.PROJECT_ID, F.TASK_ID)
1477 WHERE S.AGREEMENT_ID = p_agreement_id
1478 AND S.PROJECT_ID = p_project_id
1479 AND NVL(S.TASK_ID,0) = NVL(p_task_id,0);
1480
1481 IF (SQL%NOTFOUND) THEN
1482
1483 pa_funding_core.summary_funding_insert_row ( p_agreement_id
1484 ,p_project_id
1485 ,p_task_id
1486 ,p_login_id
1487 ,p_user_id
1488 ,p_budget_type_code
1489 );
1490
1491 END IF;
1492 END IF;
1493
1494 END summary_funding_update_row;
1495
1496 --
1497 --Name: summary_funding_delete_row
1498 --Type: Procedure
1499 --Description: This procedure deletes row(s) in to PA_SUMMARY_PROJECT_FUNDINGS.
1500 --
1501 --Called subprograms: summary_fundings_insert_row
1502 --
1503 --
1504 --
1505 --History:
1506 -- 15-MAY-2000 Created Nikhil Mishra.
1507 --
1508
1509 PROCEDURE summary_funding_delete_row
1510 (p_agreement_id IN NUMBER
1511 ,p_project_id IN NUMBER
1512 ,p_task_id IN NUMBER
1513 ,p_login_id IN VARCHAR2
1514 ,p_user_id IN VARCHAR2
1515 ,p_budget_type_code IN VARCHAR2
1516 )
1517 IS
1518 BEGIN
1519 declare
1520 dummy number;
1521
1522 cursor d1 is
1523 select 1
1524 from pa_project_fundings
1525 where project_id = p_project_id
1526 and nvl(task_id, 0) = nvl(p_task_id, 0)
1527 and agreement_id = p_agreement_id;
1528 BEGIN
1529 open d1;
1530 fetch d1 into dummy;
1531 IF d1%found THEN
1532 pa_funding_core.summary_funding_update_row ( p_agreement_id
1533 ,p_project_id
1534 ,p_task_id
1535 ,p_login_id
1536 ,p_user_id
1537 ,p_budget_type_code
1538 );
1539
1540 ELSE
1541 DELETE FROM PA_SUMMARY_PROJECT_FUNDINGS S
1542 WHERE S.PROJECT_ID = p_project_id
1543 AND S.AGREEMENT_ID = p_agreement_id
1544 AND NVL(S.TASK_ID,0) = NVL(p_task_id,0);
1545 END IF;
1546 close d1;
1547 end;
1548
1549 exception
1550 WHEN OTHERS THEN
1551 raise ;
1552 END summary_funding_delete_row;
1553
1554 /*============================================================================+
1555 | Name : check_valid_exch_rate
1556 | Type: : FUNCTION
1557 | Description : This function will return
1558 | "T" - if rate type is invalid
1559 | "R" - if rate is invalid
1560 | "Y" - if both are valid
1561 | Created for MCB2
1562 +============================================================================*/
1563
1564
1565 FUNCTION check_valid_exch_rate (
1566 p_funding_currency_code IN VARCHAR2,
1567 p_to_currency_code IN VARCHAR2,
1568 p_exchange_rate_type IN VARCHAR2,
1569 p_exchange_rate IN NUMBER,
1570 p_exchange_rate_date IN DATE) RETURN VARCHAR2 IS
1571
1572 l_valid_rate_type VARCHAR2(1);
1573
1574 BEGIN
1575
1576
1577 SELECT 'Y' INTO l_valid_rate_type
1578 from pa_conversion_types_v
1579 /* WHERE user_conversion_type = p_exchange_rate_type Commented for bug 5478703 */
1580 WHERE conversion_type = p_exchange_rate_type /* Added for bug 5478703 */
1581 AND ( (p_exchange_rate_type = 'User'
1582 AND pa_multi_currency.is_user_rate_type_allowed(
1583 p_funding_currency_code,
1584 p_to_currency_code,
1585 p_exchange_rate_date )= 'Y')
1586 OR p_exchange_rate_type <> 'User');
1587
1588 /*
1589 SELECT 'Y' INTO l_valid_rate_type
1590 from pa_conversion_types_v
1591 WHERE user_conversion_type = p_exchange_rate_type;
1592 */
1593 IF p_exchange_rate_type = 'User' THEN
1594 IF p_exchange_rate is NULL then
1595 return 'R';
1596 END IF;
1597 END IF;
1598
1599 RETURN ('Y');
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 RETURN 'T';
1603
1604 END check_valid_exch_rate;
1605 /*============================================================================+
1606 | Name : get_MCB2_attributes
1607 | Type: : PROCEDURE
1608 | Description : This function will derive all MCB2 computed values based on
1609 | input parameters
1610 | Created for MCB2
1611 +============================================================================*/
1612
1613 PROCEDURE get_MCB2_attributes (
1614 p_project_id IN NUMBER,
1615 p_agreement_id IN NUMBER,
1616 p_date_allocated IN DATE,
1617 p_allocated_amount IN NUMBER,
1618 p_funding_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1619 p_project_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1620 p_project_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1621 p_project_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1622 p_project_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1623 p_project_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1624 p_projfunc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1625 p_projfunc_rate_type IN OUT NOCOPY VARCHAR2, /*file.sql.39*/
1626 p_projfunc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1627 p_projfunc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1628 p_projfunc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1629 p_invproc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1630 p_invproc_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1631 p_invproc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1632 p_invproc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1633 p_invproc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1634 p_revproc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1635 p_revproc_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1636 p_revproc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1637 p_revproc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1638 p_revproc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1639 p_validate_parameters IN VARCHAR2 DEFAULT 'N',
1640 x_err_code OUT NOCOPY NUMBER,/*file.sql.39*/
1641 x_err_msg OUT NOCOPY VARCHAR2/*file.sql.39*/
1642 ) is
1643
1644 l_multi_currency_billing_flag VARCHAR2(1);
1645 l_baseline_funding_flag VARCHAR2(1);
1646 l_funding_rate_date_code VARCHAR2(30);
1647 l_funding_rate_type VARCHAR2(30);
1648 l_funding_rate_date DATE;
1649 l_funding_exchange_rate NUMBER;
1650
1651 l_project_rate_date_code VARCHAR2(30);
1652 l_project_rate_type VARCHAR2(30);
1653 l_project_rate_date DATE;
1654 l_project_exchange_rate NUMBER;
1655 l_project_allocated_amount NUMBER;
1656
1657 l_projfunc_rate_date_code VARCHAR2(30);
1658 l_projfunc_rate_type VARCHAR2(30);
1659 l_projfunc_rate_date DATE;
1660 l_projfunc_exchange_rate NUMBER;
1661 l_projfunc_allocated_amount NUMBER;
1662
1663 l_invproc_currency_type VARCHAR2(30);
1664 l_invproc_rate_type VARCHAR2(30);
1665 l_invproc_rate_date DATE;
1666 l_invproc_exchange_rate NUMBER;
1667 l_invproc_allocated_amount NUMBER;
1668
1669 l_revproc_rate_type VARCHAR2(30);
1670 l_revproc_rate_date DATE;
1671 l_revproc_exchange_rate NUMBER;
1672 l_revproc_allocated_amount NUMBER;
1673
1674 l_return_status VARCHAR2(50);
1675 l_msg_count NUMBER;
1676 l_msg_data VARCHAR2(250);
1677
1678 l_denominator NUMBER;
1679 l_numerator NUMBER;
1680 l_validate VARCHAR2(1) := 'N';
1681 l_is_rate_type_valid VARCHAR2(1) := 'Y';
1682
1683
1684 l_err_code NUMBER;
1685 l_err_msg VARCHAR2(150);
1686
1687 l_mult_funding_flag VARCHAR2(1);
1688
1689 /*File.sql.39 . np variables are nocopy dummy variables define to
1690 revert the changes for any failure*/
1691 np_p_funding_currency_code VARCHAR2(50) := p_funding_currency_code;
1692 np_p_project_currency_code VARCHAR2(50) := p_project_currency_code;
1693 np_p_project_rate_type VARCHAR2(50) := p_project_rate_type;
1694 np_p_project_rate_date DATE := p_project_rate_date;
1695 np_p_project_exchange_rate NUMBER := p_project_exchange_rate;
1696 np_p_project_allocated_amount NUMBER := p_project_allocated_amount;
1697 np_p_projfunc_currency_code VARCHAR2(50) := p_projfunc_currency_code;
1698 np_p_projfunc_rate_type VARCHAR2(50) := p_projfunc_rate_type;
1699 np_p_projfunc_rate_date DATE := p_projfunc_rate_date;
1700 np_p_projfunc_exchange_rate NUMBER := p_projfunc_exchange_rate;
1701 np_p_projfunc_allocated_amount NUMBER := p_projfunc_allocated_amount;
1702 np_p_invproc_currency_code VARCHAR2(50) := p_invproc_currency_code;
1703 np_p_invproc_rate_type VARCHAR2(50) := p_invproc_rate_type;
1704 np_p_invproc_rate_date DATE := p_invproc_rate_date;
1705 np_p_invproc_exchange_rate NUMBER := p_invproc_exchange_rate;
1706 np_p_invproc_allocated_amount NUMBER := p_invproc_allocated_amount;
1707 np_p_revproc_currency_code VARCHAR2(50) := p_revproc_currency_code;
1708 np_p_revproc_rate_type VARCHAR2(50) := p_revproc_rate_type;
1709 np_p_revproc_rate_date DATE := p_revproc_rate_date;
1710 np_p_revproc_exchange_rate NUMBER := p_revproc_exchange_rate;
1711 np_p_revproc_allocated_amount NUMBER := p_revproc_allocated_amount;
1712 BEGIN
1713
1714 x_err_code := 0;
1715 x_err_msg := NULL;
1716 p_funding_currency_code :=
1717 pa_agreement_utils.get_agr_curr_code(p_agreement_id);
1718
1719 /* Added for bug 5478703 */
1720 IF p_project_rate_type is not null
1721 THEN
1722 SELECT conversion_type
1723 INTO p_project_rate_type
1724 FROM pa_conversion_types_v
1725 WHERE user_conversion_type = p_project_rate_type
1726 or conversion_type = p_project_rate_type;
1727 END IF;
1728
1729 IF p_projfunc_rate_type is not null
1730 THEN
1731 SELECT conversion_type
1732 INTO p_projfunc_rate_type
1733 FROM pa_conversion_types_v
1734 WHERE user_conversion_type = p_projfunc_rate_type
1735 or conversion_type = p_projfunc_rate_type;
1736 END IF;
1737 /* bug 5478703 - Code change Ends here*/
1738
1739 pa_multi_currency_billing.get_project_defaults(
1740 p_project_id => p_project_id,
1741 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
1742 x_baseline_funding_flag => l_baseline_funding_flag,
1743 x_revproc_currency_code => p_revproc_currency_code,
1744 x_invproc_currency_type => l_invproc_currency_type,
1745 x_invproc_currency_code => p_invproc_currency_code,
1746 x_project_currency_code => p_project_currency_code,
1747 x_project_bil_rate_date_code => l_project_rate_date_code,
1748 x_project_bil_rate_type => l_project_rate_type,
1749 x_project_bil_rate_date => l_project_rate_date,
1750 x_project_bil_exchange_rate => l_project_exchange_rate,
1751 x_projfunc_currency_code => p_projfunc_currency_code,
1752 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
1753 x_projfunc_bil_rate_type => l_projfunc_rate_type,
1754 x_projfunc_bil_rate_date => l_projfunc_rate_date,
1755 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
1756 x_funding_rate_date_code => l_funding_rate_date_code,
1757 x_funding_rate_type => l_funding_rate_type,
1758 x_funding_rate_date => l_funding_rate_date,
1759 x_funding_exchange_rate => l_funding_exchange_rate,
1760 x_return_status => l_return_status,
1761 x_msg_count => l_msg_count,
1762 x_msg_data => l_msg_data);
1763
1764 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1765
1766 x_err_code := 30;
1767 x_err_msg := l_msg_data;
1768
1769 end if;
1770
1771 /* Source and destination currency are same so null out attributes and copy amount */
1772
1773 if x_err_code = 0 then
1774
1775 if l_invproc_currency_type = 'FUNDING_CURRENCY' THEN
1776
1777 if p_invproc_currency_code is null then
1778
1779 p_invproc_currency_code := p_funding_currency_code;
1780
1781 end if;
1782
1783 l_mult_funding_flag := 'N';
1784
1785 BEGIN
1786
1787 SELECT 'Y' into l_mult_funding_flag
1788 FROM dual
1789 WHERE exists ( select null
1790 FROM PA_SUMMARY_PROJECT_FUNDINGS spf
1791 WHERE spf.project_id = p_project_id
1792 AND spf.funding_currency_code <> p_funding_currency_code
1793 AND spf.total_baselined_amount <> 0
1794 AND spf.total_unbaselined_amount <> 0);
1795
1796 EXCEPTION
1797
1798 when no_data_found then
1799
1800 l_mult_funding_flag := 'N';
1801
1802 END;
1803
1804
1805 if l_mult_funding_flag = 'Y' then
1806
1807 x_err_code := 30;
1808 x_err_msg := 'PA_MULTPLE_FUNDING_CURR';
1809
1810 end if;
1811
1812 end if;
1813
1814 end if;
1815
1816 if x_err_code = 0 then
1817
1818 if p_funding_currency_code = p_project_currency_code then
1819 p_project_rate_type := null;
1820 p_project_rate_date := null;
1821 p_project_exchange_rate := null;
1822 p_project_allocated_amount := p_allocated_amount;
1823
1824 else
1825
1826 if p_validate_parameters = 'Y' and p_project_rate_type is not null then
1827
1828 l_validate := 'Y';
1829
1830 end if;
1831
1832 p_project_rate_type := nvl(p_project_rate_type, l_project_rate_type);
1833 p_project_exchange_rate := nvl(p_project_exchange_rate,
1834 l_project_exchange_rate);
1835 if p_project_rate_date is null then
1836
1837 if l_project_rate_date_code = 'FIXED_DATE' then
1838 p_project_rate_date := l_project_rate_date;
1839 else
1840 p_project_rate_date := p_date_allocated;
1841 end if;
1842 end if;
1843
1844 if l_validate = 'Y' then
1845
1846 l_is_rate_type_valid := check_valid_exch_rate (
1847 p_funding_currency_code => p_funding_currency_code,
1848 p_to_currency_code => p_project_currency_code,
1849 p_exchange_rate_type => p_project_rate_type,
1850 p_exchange_rate_date => p_project_rate_date,
1851 p_exchange_rate => p_project_exchange_rate);
1852
1853 if l_is_rate_type_valid = 'R' then
1854
1855 x_err_code := '30';
1856 x_err_msg := 'PA_EXCH_RATE_NULL_PC';
1857
1858 elsif l_is_rate_type_valid = 'T' then
1859 x_err_code := '30';
1860 x_err_msg := 'PA_INVALID_RATE_TYPE_PC';
1861
1862 end if;
1863
1864 end if;
1865
1866 if x_err_code = 0 then
1867
1868 pa_multi_currency.convert_amount (
1869 p_from_currency => p_funding_currency_code,
1870 p_to_currency => p_project_currency_code,
1871 p_conversion_date => p_project_rate_date,
1872 p_conversion_type => p_project_rate_type,
1873 p_handle_exception_flag => 'Y',
1874 p_amount => p_allocated_amount,
1875 p_user_validate_flag => 'Y',
1876 p_converted_amount => p_project_allocated_amount,
1877 p_denominator => l_denominator,
1878 p_numerator => l_numerator,
1879 p_rate => p_project_exchange_rate,
1880 x_status => l_return_status);
1881
1882 if l_return_status is not null then
1883
1884 x_err_code := 30;
1885 /* Bug 2341576 - Prepended the _FC in the following message */
1886 x_err_msg := l_return_status || '_FC_PC';
1887
1888 end if;
1889
1890 end if;
1891
1892 end if;
1893
1894 end if;
1895
1896 if x_err_code = 0 then
1897
1898 if p_funding_currency_code = p_projfunc_currency_code then
1899 p_projfunc_rate_type := null;
1900 p_projfunc_rate_date := null;
1901 p_projfunc_exchange_rate := null;
1902 p_projfunc_allocated_amount := p_allocated_amount;
1903
1904 else
1905 if p_validate_parameters = 'Y' and p_projfunc_rate_type is not null then
1906
1907 l_validate := 'Y';
1908
1909 else
1910
1911 l_validate := 'N';
1912
1913 end if;
1914
1915 p_projfunc_rate_type := nvl(p_projfunc_rate_type,
1916 l_projfunc_rate_type);
1917 p_projfunc_exchange_rate := nvl(p_projfunc_exchange_rate,
1918 l_projfunc_exchange_rate);
1919 if p_projfunc_rate_date is null then
1920 if l_projfunc_rate_date_code = 'FIXED_DATE' then
1921 p_projfunc_rate_date := l_projfunc_rate_date;
1922 else
1923 p_projfunc_rate_date := p_date_allocated;
1924 end if;
1925 end if;
1926 if l_validate = 'Y' then
1927
1928 l_is_rate_type_valid := check_valid_exch_rate (
1929 p_funding_currency_code => p_funding_currency_code,
1930 p_to_currency_code => p_projfunc_currency_code,
1931 p_exchange_rate_type => p_projfunc_rate_type,
1932 p_exchange_rate_date => p_projfunc_rate_date,
1933 p_exchange_rate => p_projfunc_exchange_rate);
1934
1935 if l_is_rate_type_valid = 'R' then
1936
1937 x_err_code := '30';
1938 x_err_msg := 'PA_EXCH_RATE_NULL_PF';
1939
1940 elsif l_is_rate_type_valid = 'T' then
1941 x_err_code := '30';
1942 x_err_msg := 'PA_INVALID_RATE_TYPE_PF';
1943
1944 end if;
1945
1946 end if;
1947
1948 if x_err_code = 0 then
1949
1950 pa_multi_currency.convert_amount (
1951 p_from_currency => p_funding_currency_code,
1952 p_to_currency => p_projfunc_currency_code,
1953 p_conversion_date => p_projfunc_rate_date,
1954 p_conversion_type => p_projfunc_rate_type,
1955 p_handle_exception_flag => 'Y',
1956 p_amount => p_allocated_amount,
1957 p_user_validate_flag => 'Y',
1958 p_converted_amount => p_projfunc_allocated_amount,
1959 p_denominator => l_denominator,
1960 p_numerator => l_numerator,
1961 p_rate => p_projfunc_exchange_rate,
1962 x_status => l_return_status);
1963
1964 if l_return_status is not null then
1965
1966 x_err_code := 30;
1967 /* Bug 2341576 - Prepended the _FC in the following message */
1968 x_err_msg := l_return_status || '_FC_PF';
1969
1970 end if;
1971
1972 end if;
1973
1974 end if;
1975
1976 end if;
1977
1978 if x_err_code = 0 then
1979
1980 if p_funding_currency_code = p_invproc_currency_code then
1981 p_invproc_rate_type := null;
1982 p_invproc_rate_date := null;
1983 p_invproc_exchange_rate := null;
1984 p_invproc_allocated_amount := p_allocated_amount;
1985 elsif p_invproc_currency_code = p_project_currency_code then
1986 p_invproc_rate_type := p_project_rate_type;
1987 p_invproc_rate_date := p_project_rate_date;
1988 p_invproc_exchange_rate := p_project_exchange_rate;
1989 p_invproc_allocated_amount := p_project_allocated_amount;
1990 elsif p_invproc_currency_code = p_projfunc_currency_code then
1991 p_invproc_rate_type := p_projfunc_rate_type;
1992 p_invproc_rate_date := p_projfunc_rate_date;
1993 p_invproc_exchange_rate := p_projfunc_exchange_rate;
1994 p_invproc_allocated_amount := p_projfunc_allocated_amount;
1995 end if;
1996
1997 if p_funding_currency_code = p_revproc_currency_code then
1998 p_revproc_rate_type := null;
1999 p_revproc_rate_date := null;
2000 p_revproc_exchange_rate := null;
2001 p_revproc_allocated_amount := p_allocated_amount;
2002 elsif p_revproc_currency_code = p_project_currency_code then
2003 p_revproc_rate_type := p_project_rate_type;
2004 p_revproc_rate_date := p_project_rate_date;
2005 p_revproc_exchange_rate := p_project_exchange_rate;
2006 p_revproc_allocated_amount := p_project_allocated_amount;
2007 elsif p_revproc_currency_code = p_projfunc_currency_code then
2008 p_revproc_rate_type := p_projfunc_rate_type;
2009 p_revproc_rate_date := p_projfunc_rate_date;
2010 p_revproc_exchange_rate := p_projfunc_exchange_rate;
2011 p_revproc_allocated_amount := p_projfunc_allocated_amount;
2012 end if;
2013
2014 end if;
2015
2016 EXCEPTION
2017
2018 WHEN OTHERS THEN
2019 p_funding_currency_code := np_p_funding_currency_code;
2020 p_project_currency_code := np_p_project_currency_code;
2021 p_project_rate_type := np_p_project_rate_type;
2022 p_project_rate_date := np_p_project_rate_date;
2023 p_project_exchange_rate := np_p_project_exchange_rate;
2024 p_project_allocated_amount := np_p_project_allocated_amount;
2025 p_projfunc_currency_code := np_p_projfunc_currency_code;
2026 p_projfunc_rate_type := np_p_projfunc_rate_type;
2027 p_projfunc_rate_date := np_p_projfunc_rate_date;
2028 p_projfunc_exchange_rate := np_p_projfunc_exchange_rate;
2029 p_projfunc_allocated_amount := np_p_projfunc_allocated_amount;
2030 p_invproc_currency_code := np_p_invproc_currency_code;
2031 p_invproc_rate_type := np_p_invproc_rate_type;
2032 p_invproc_rate_date := np_p_invproc_rate_date;
2033 p_invproc_exchange_rate := np_p_invproc_exchange_rate;
2034 p_invproc_allocated_amount := np_p_invproc_allocated_amount;
2035 p_revproc_currency_code := np_p_revproc_currency_code;
2036 p_revproc_rate_type := np_p_revproc_rate_type;
2037 p_revproc_rate_date := np_p_revproc_rate_date;
2038 p_revproc_exchange_rate := np_p_revproc_exchange_rate;
2039 p_revproc_allocated_amount := np_p_revproc_allocated_amount;
2040
2041 x_err_code := SQLCODE;
2042 x_err_msg := SQLERRM;
2043
2044 END GET_MCB2_ATTRIBUTES;
2045
2046
2047 /*This is added for finplan impact on billing*/
2048 FUNCTION check_proj_task_lvl_funding_fp
2049 ( p_agreement_id IN NUMBER
2050 ,p_project_id IN NUMBER
2051 ,p_task_id IN NUMBER
2052 ) RETURN VARCHAR2
2053 is
2054 p_proposed_fund_level varchar2(1);
2055 l_return_status varchar2(2000);
2056 l_msg_count number;
2057 l_msg_data varchar2(2000);
2058 x_return_status varchar2(2000);
2059 BEGIN
2060 IF (p_task_id is null) then
2061 p_proposed_fund_level :='P';
2062 Pa_Fp_Control_Items_Utils.isFundingLevelChangeAllowed(
2063 p_project_id =>p_project_id,
2064 p_proposed_fund_level =>p_proposed_fund_level,
2065 x_return_status =>l_return_status,
2066 x_msg_count =>l_msg_count,
2067 x_msg_data =>l_msg_data);
2068 if (x_return_status=FND_API.G_RET_STS_ERROR)
2069 then return('A');
2070 end if;
2071 else
2072 p_proposed_fund_level :='T';
2073 Pa_Fp_Control_Items_Utils.isFundingLevelChangeAllowed(
2074 p_project_id =>p_project_id,
2075 p_proposed_fund_level =>p_proposed_fund_level,
2076 x_return_status =>l_return_status,
2077 x_msg_count =>l_msg_count,
2078 x_msg_data =>l_msg_data);
2079
2080 if (x_return_status=FND_API.G_RET_STS_ERROR)
2081 then return('A');
2082 end if;
2083 end if;
2084 return ('Y');
2085 end check_proj_task_lvl_funding_fp;
2086
2087 -- API for creating funding lines for Control Items changes
2088 PROCEDURE create_funding_CO(
2089 p_Rowid IN OUT NOCOPY VARCHAR2,/*FILE.sql.39*/
2090 p_Project_Funding_Id IN OUT NOCOPY NUMBER,/*FILE.sql.39*/
2091 p_Last_Update_Date IN DATE,
2092 p_Last_Updated_By IN NUMBER,
2093 p_Creation_Date IN DATE,
2094 p_Created_By IN NUMBER,
2095 p_Last_Update_Login IN NUMBER,
2096 p_Agreement_Id IN NUMBER,
2097 p_Project_Id IN NUMBER,
2098 p_Task_id IN NUMBER,
2099 p_Budget_Type_Code IN VARCHAR2,
2100 p_Allocated_Amount IN NUMBER,
2101 p_Date_Allocated IN DATE,
2102 P_Funding_Currency_Code IN VARCHAR2, -- FP_M CI changes
2103 p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M changes
2104 p_Attribute_Category IN VARCHAR2,
2105 p_Attribute1 IN VARCHAR2,
2106 p_Attribute2 IN VARCHAR2,
2107 p_Attribute3 IN VARCHAR2,
2108 p_Attribute4 IN VARCHAR2,
2109 p_Attribute5 IN VARCHAR2,
2110 p_Attribute6 IN VARCHAR2,
2111 p_Attribute7 IN VARCHAR2,
2112 p_Attribute8 IN VARCHAR2,
2113 p_Attribute9 IN VARCHAR2,
2114 p_Attribute10 IN VARCHAR2,
2115 p_pm_funding_reference IN VARCHAR2,
2116 p_pm_product_code IN VARCHAR2,
2117 p_Project_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
2118 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
2119 p_project_rate_date IN DATE DEFAULT NULL,
2120 p_project_exchange_rate IN NUMBER DEFAULT NULL,
2121 p_Projfunc_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
2122 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
2123 p_projfunc_rate_date IN DATE DEFAULT NULL,
2124 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
2125 x_err_code OUT NOCOPY NUMBER,/*FILE.sql.39*/
2126 x_err_msg OUT NOCOPY VARCHAR2,/*FILE.sql.39*/
2127 p_funding_category IN VARCHAR2 /* Bug 2244796 */
2128 )
2129 IS
2130 l_Project_Funding_Id NUMBER := p_Project_Funding_Id;
2131 l_err_msg VARCHAR2(150);
2132 l_err_code NUMBER;
2133 l_funding_currency_code VARCHAR2(15);
2134
2135 l_project_currency_code VARCHAR2(15);
2136 l_project_rate_type VARCHAR2(30);
2137 l_project_rate_date DATE;
2138 l_project_exchange_rate NUMBER;
2139 l_project_allocated_amount NUMBER;
2140
2141 l_projfunc_currency_code VARCHAR2(15);
2142 l_projfunc_rate_type VARCHAR2(30);
2143 l_projfunc_rate_date DATE;
2144 l_projfunc_exchange_rate NUMBER;
2145 l_projfunc_allocated_amount NUMBER;
2146
2147 l_invproc_currency_Type VARCHAR2(30);
2148 l_invproc_currency_code VARCHAR2(15);
2149 l_invproc_rate_type VARCHAR2(30);
2150 l_invproc_rate_date DATE;
2151 l_invproc_exchange_rate NUMBER;
2152 l_invproc_allocated_amount NUMBER;
2153
2154 l_revproc_currency_code VARCHAR2(15);
2155 l_revproc_rate_type VARCHAR2(30);
2156 l_revproc_rate_date DATE;
2157 l_revproc_exchange_rate NUMBER;
2158 l_revproc_allocated_amount NUMBER;
2159 BEGIN
2160
2161 --dbms_output.put_line('Inside: pa_funding_core.create_funding_CO');
2162
2163 Select Invproc_Currency_Type, Project_Currency_Code, ProjFunc_Currency_Code
2164 INTO l_Invproc_Currency_Type, l_Project_Currency_Code, l_ProjFunc_Currency_Code
2165 FROM PA_Projects
2166 Where Project_ID = P_Project_ID;
2167
2168 IF l_Invproc_Currency_Type = 'PROJECT_CURRENCY' THEN
2169 l_Invproc_Currency_Code := l_Project_Currency_Code;
2170 l_Invproc_Rate_Type := p_Project_Rate_Type;
2171 l_Invproc_Rate_Date := p_Project_Rate_Date;
2172 l_Invproc_Exchange_Rate := p_Project_Exchange_Rate;
2173 l_Invproc_Allocated_Amount := p_Project_Allocated_Amount;
2174 Elsif l_Invproc_Currency_Type = 'PROJFUNC_CURRENCY' THEN
2175 l_Invproc_Currency_Code := l_Projfunc_Currency_Code;
2176 l_Invproc_Rate_Type := p_Projfunc_Rate_Type;
2177 l_Invproc_Rate_Date := p_Projfunc_Rate_Date;
2178 l_Invproc_Exchange_Rate := p_Projfunc_Exchange_Rate;
2179 l_Invproc_Allocated_Amount := p_Projfunc_Allocated_Amount;
2180 Elsif l_Invproc_Currency_Type = 'FUNDING_CURRENCY' THEN
2181 l_Invproc_Currency_Code := p_Funding_Currency_Code;
2182 l_Invproc_Rate_Type := NULL;
2183 l_Invproc_Rate_Date := NULL;
2184 l_Invproc_Exchange_Rate := NULL;
2185 l_Invproc_Allocated_Amount := p_Allocated_Amount;
2186 END IF;
2187
2188 pa_project_fundings_pkg.insert_row(
2189 x_rowid => p_rowid,
2190 x_project_funding_id => p_project_funding_id,
2191 x_last_update_date => p_last_update_date,
2192 x_last_updated_by => p_last_updated_by,
2193 x_creation_date => p_creation_date,
2194 x_created_by => p_created_by,
2195 x_last_update_login => p_last_update_login,
2196 x_agreement_id => p_agreement_id,
2197 x_project_id => p_project_id,
2198 x_task_id => p_task_id,
2199 x_budget_type_code => p_budget_type_code,
2200 x_allocated_amount => p_allocated_amount,
2201 x_date_allocated => p_date_allocated,
2202 X_Control_Item_ID => p_Control_Item_ID, -- FP_M changes
2203 x_attribute_category => p_attribute_category,
2204 x_attribute1 => p_attribute1,
2205 x_attribute2 => p_attribute2,
2206 x_attribute3 => p_attribute3,
2207 x_attribute4 => p_attribute4,
2208 x_attribute5 => p_attribute5,
2209 x_attribute6 => p_attribute6,
2210 x_attribute7 => p_attribute7,
2211 x_attribute8 => p_attribute8,
2212 x_attribute9 => p_attribute9,
2213 x_attribute10 => p_attribute10,
2214 x_pm_funding_reference => p_pm_funding_reference,
2215 x_pm_product_code => p_pm_product_code,
2216 x_funding_currency_code => p_funding_currency_code,
2217 x_project_currency_code => l_project_currency_code,
2218 x_project_rate_type => p_project_rate_type,
2219 x_project_rate_date => p_project_rate_date,
2220 x_project_exchange_rate => p_project_exchange_rate,
2221 x_project_allocated_amount => p_project_allocated_amount,
2222 x_projfunc_currency_code => l_projfunc_currency_code,
2223 x_projfunc_rate_type => p_projfunc_rate_type,
2224 x_projfunc_rate_date => p_projfunc_rate_date,
2225 x_projfunc_exchange_rate => p_projfunc_exchange_rate,
2226 x_projfunc_allocated_amount => p_projfunc_allocated_amount,
2227 x_invproc_currency_code => l_invproc_currency_code,
2228 x_invproc_rate_type => l_invproc_rate_type,
2229 x_invproc_rate_date => l_invproc_rate_date,
2230 x_invproc_exchange_rate => l_invproc_exchange_rate,
2231 x_invproc_allocated_amount => l_invproc_allocated_amount,
2232 x_revproc_currency_code => l_projfunc_currency_code,
2233 x_revproc_rate_type => p_projfunc_rate_type,
2234 x_revproc_rate_date => p_projfunc_rate_date,
2235 x_revproc_exchange_rate => p_projfunc_exchange_rate,
2236 x_revproc_allocated_amount => p_projfunc_allocated_amount,
2237 x_funding_category => p_funding_category /* For Bug2244796 */
2238 );
2239
2240 --dbms_output.put_line('Done: create_funding_CO');
2241 EXCEPTION
2242
2243 WHEN OTHERS THEN
2244 x_err_code := SQLCODE;
2245 x_err_msg := SQLERRM;
2246 p_Project_Funding_Id := l_Project_Funding_Id;
2247 END create_funding_CO;
2248
2249 END PA_FUNDING_CORE;