[Home] [Help]
PACKAGE BODY: APPS.PA_FUNDING_CORE
Source
1 PACKAGE BODY pa_funding_core AS
2 /* $Header: PAXBIPFB.pls 120.4.12020000.2 2012/10/05 08:46:00 paljain 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 l_allocated_amount NUMBER; --Bug 14666538
860 BEGIN
861
862 --dbms_output.put_line('Inside: pa_funding_core.create_funding');
863
864 --dbms_output.put_line( p_Project_Funding_Id);
865 --dbms_output.put_line( p_Last_Update_Date );
866 --dbms_output.put_line( p_Last_Updated_By);
867 --dbms_output.put_line( p_Creation_Date);
868 --dbms_output.put_line( p_Created_By);
869 --dbms_output.put_line( p_Last_Update_Login);
870 --dbms_output.put_line( p_Agreement_Id);
871 --dbms_output.put_line( p_Project_Id);
872 --dbms_output.put_line(nvl(to_char(p_Task_Id),'NULL'));
873 --dbms_output.put_line( p_Allocated_Amount);
874 --dbms_output.put_line( p_Date_Allocated);
875
876 --dbms_output.put_line('Inside: pa_funding_core.create_funding inserting row');
877
878 x_err_code := 0;
879 x_err_msg := NULL;
880
881 l_project_rate_type := p_project_rate_type;
882 l_project_rate_date := p_project_rate_date;
883 l_project_exchange_rate := p_project_exchange_rate;
884 l_projfunc_rate_type := p_projfunc_rate_type;
885 l_projfunc_rate_date := p_projfunc_rate_date;
886 l_projfunc_exchange_rate := p_projfunc_exchange_rate;
887
888 /* Bug 14666538 */
889
890 l_allocated_amount := p_allocated_amount;
891 l_funding_currency_code := pa_agreement_utils.get_agr_curr_code(p_agreement_id);
892 l_allocated_amount := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_allocated_amount,l_funding_currency_code);
893
894 /* Bug 14666538 */
895
896
897 get_MCB2_attributes (
898 p_project_id => p_project_id,
899 p_agreement_id => p_agreement_id,
900 p_date_allocated => p_date_allocated,
901 p_allocated_amount => l_allocated_amount,
902 p_funding_currency_code => l_funding_currency_code,
903 p_project_currency_code => l_project_currency_code,
904 p_project_rate_type => l_project_rate_type,
905 p_project_rate_date => l_project_rate_date,
906 p_project_exchange_rate => l_project_exchange_rate,
907 p_project_allocated_amount => l_project_allocated_amount,
908 p_projfunc_currency_code => l_projfunc_currency_code,
909 p_projfunc_rate_type => l_projfunc_rate_type,
910 p_projfunc_rate_date => l_projfunc_rate_date,
911 p_projfunc_exchange_rate => l_projfunc_exchange_rate,
912 p_projfunc_allocated_amount => l_projfunc_allocated_amount,
913 p_invproc_currency_code => l_invproc_currency_code,
914 p_invproc_rate_type => l_invproc_rate_type,
915 p_invproc_rate_date => l_invproc_rate_date,
916 p_invproc_exchange_rate => l_invproc_exchange_rate,
917 p_invproc_allocated_amount => l_invproc_allocated_amount,
918 p_revproc_currency_code => l_revproc_currency_code,
919 p_revproc_rate_type => l_revproc_rate_type,
920 p_revproc_rate_date => l_revproc_rate_date,
921 p_revproc_exchange_rate => l_revproc_exchange_rate,
922 p_revproc_allocated_amount => l_revproc_allocated_amount,
923 p_validate_parameters => 'Y',
924 x_err_code => l_err_code,
925 x_err_msg => l_err_msg
926 );
927
928 x_err_code := l_err_code;
929 x_err_msg := l_err_msg;
930
931 if x_err_code = 0 then
932
933 pa_project_fundings_pkg.insert_row(
934 x_rowid => p_rowid,
935 x_project_funding_id => p_project_funding_id,
936 x_last_update_date => p_last_update_date,
937 x_last_updated_by => p_last_updated_by,
938 x_creation_date => p_creation_date,
939 x_created_by => p_created_by,
940 x_last_update_login => p_last_update_login,
941 x_agreement_id => p_agreement_id,
942 x_project_id => p_project_id,
943 x_task_id => p_task_id,
944 x_budget_type_code => p_budget_type_code,
945 x_allocated_amount => l_allocated_amount,
946 x_date_allocated => p_date_allocated,
947 X_Control_Item_ID => p_Control_Item_ID, -- FP_M changes
948 x_attribute_category => p_attribute_category,
949 x_attribute1 => p_attribute1,
950 x_attribute2 => p_attribute2,
951 x_attribute3 => p_attribute3,
952 x_attribute4 => p_attribute4,
953 x_attribute5 => p_attribute5,
954 x_attribute6 => p_attribute6,
955 x_attribute7 => p_attribute7,
956 x_attribute8 => p_attribute8,
957 x_attribute9 => p_attribute9,
958 x_attribute10 => p_attribute10,
959 x_pm_funding_reference => p_pm_funding_reference,
960 x_pm_product_code => p_pm_product_code,
961 x_funding_currency_code => l_funding_currency_code,
962 x_project_currency_code => l_project_currency_code,
963 x_project_rate_type => l_project_rate_type,
964 x_project_rate_date => l_project_rate_date,
965 x_project_exchange_rate => l_project_exchange_rate,
966 x_project_allocated_amount => l_project_allocated_amount,
967 x_projfunc_currency_code => l_projfunc_currency_code,
968 x_projfunc_rate_type => l_projfunc_rate_type,
969 x_projfunc_rate_date => l_projfunc_rate_date,
970 x_projfunc_exchange_rate => l_projfunc_exchange_rate,
971 x_projfunc_allocated_amount => l_projfunc_allocated_amount,
972 x_invproc_currency_code => l_invproc_currency_code,
973 x_invproc_rate_type => l_invproc_rate_type,
974 x_invproc_rate_date => l_invproc_rate_date,
975 x_invproc_exchange_rate => l_invproc_exchange_rate,
976 x_invproc_allocated_amount => l_invproc_allocated_amount,
977 x_revproc_currency_code => l_revproc_currency_code,
978 x_revproc_rate_type => l_revproc_rate_type,
979 x_revproc_rate_date => l_revproc_rate_date,
980 x_revproc_exchange_rate => l_revproc_exchange_rate,
981 x_revproc_allocated_amount => l_revproc_allocated_amount,
982 x_funding_category => p_funding_category /* For Bug2244796 */
983 );
984
985 end if;
986
987 --dbms_output.put_line('Done: create_funding');
988 -- summary_funding.insert_row;
989 EXCEPTION
990
991 WHEN OTHERS THEN
992 --dbms_output.put_line(SQLERRM);
993 -- Null added by johnson P
994 x_err_code := SQLCODE;
995 x_err_msg := SQLERRM;
996 p_Project_Funding_Id := l_Project_Funding_Id;
997
998 END create_funding;
999
1000 --
1001 --Name: update_funding
1002 --Type: PROCEDURE
1003 --Description: This procedure is used to create a funding record in PA_PROJECT_FUNDINGS
1004 --Called subprograms: pa_project_fundings_pkg.update_row
1005 --
1006 --
1007 --
1008 --History:
1009 -- 05-MAY-2000 Created Adwait Marathe.
1010 -- 15-MAY-2000 Created Nikhil Mishra.
1011
1012 PROCEDURE Update_funding(
1013 p_Project_Funding_Id IN NUMBER,
1014 p_Last_Update_Date IN DATE,
1015 p_Last_Updated_By IN NUMBER,
1016 p_Last_Update_Login IN NUMBER,
1017 p_Agreement_Id IN NUMBER,
1018 p_Project_Id IN NUMBER,
1019 p_Task_id IN NUMBER,
1020 p_Budget_Type_Code IN VARCHAR2,
1021 p_Allocated_Amount IN NUMBER,
1022 p_Date_Allocated IN DATE,
1023 p_Attribute_Category IN VARCHAR2,
1024 p_Attribute1 IN VARCHAR2,
1025 p_Attribute2 IN VARCHAR2,
1026 p_Attribute3 IN VARCHAR2,
1027 p_Attribute4 IN VARCHAR2,
1028 p_Attribute5 IN VARCHAR2,
1029 p_Attribute6 IN VARCHAR2,
1030 p_Attribute7 IN VARCHAR2,
1031 p_Attribute8 IN VARCHAR2,
1032 p_Attribute9 IN VARCHAR2,
1033 p_Attribute10 IN VARCHAR2,
1034 p_pm_funding_reference IN VARCHAR2,
1035 p_pm_product_code IN VARCHAR2,
1036 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
1037 p_project_rate_date IN DATE DEFAULT NULL,
1038 p_project_exchange_rate IN NUMBER DEFAULT NULL,
1039 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
1040 p_projfunc_rate_date IN DATE DEFAULT NULL,
1041 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
1042 x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
1043 x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
1044 p_funding_category IN VARCHAR2 /* Bug 2244796 */
1045
1046 )
1047
1048 IS
1049 CURSOR C IS
1050 SELECT
1051 rowid
1052 FROM PA_PROJECT_FUNDINGS
1053 WHERE project_funding_id = p_Project_Funding_Id;
1054 fun_rec C%ROWTYPE;
1055 l_funding_currency_code VARCHAR2(15);
1056 l_project_currency_code VARCHAR2(15);
1057 l_project_rate_type VARCHAR2(30);
1058 l_project_rate_date DATE;
1059 l_project_exchange_rate NUMBER;
1060 l_project_allocated_amount NUMBER;
1061 l_projfunc_currency_code VARCHAR2(15);
1062 l_projfunc_rate_type VARCHAR2(30);
1063 l_projfunc_rate_date DATE;
1064 l_projfunc_exchange_rate NUMBER;
1065 l_projfunc_allocated_amount NUMBER;
1066 l_invproc_currency_code VARCHAR2(15);
1067 l_invproc_rate_type VARCHAR2(30);
1068 l_invproc_rate_date DATE;
1069 l_invproc_exchange_rate NUMBER;
1070 l_invproc_allocated_amount NUMBER;
1071 l_revproc_currency_code VARCHAR2(15);
1072 l_revproc_rate_type VARCHAR2(30);
1073 l_revproc_rate_date DATE;
1074 l_revproc_exchange_rate NUMBER;
1075 l_revproc_allocated_amount NUMBER;
1076 l_allocated_amount NUMBER; --Bug 14666538
1077
1078 l_err_msg VARCHAR2(150);
1079 l_err_code NUMBER;
1080 BEGIN
1081 x_err_code := 0;
1082 x_err_msg := NULL;
1083
1084 OPEN C;
1085 FETCH C INTO fun_rec;
1086 IF C%FOUND THEN
1087
1088 l_project_rate_type := p_project_rate_type;
1089 l_project_rate_date := p_project_rate_date;
1090 l_project_exchange_rate := p_project_exchange_rate;
1091 l_projfunc_rate_type := p_projfunc_rate_type;
1092 l_projfunc_rate_date := p_projfunc_rate_date;
1093 l_projfunc_exchange_rate := p_projfunc_exchange_rate;
1094
1095 /* Bug 14666538 */
1096
1097 l_allocated_amount := p_allocated_amount;
1098 l_funding_currency_code := pa_agreement_utils.get_agr_curr_code(p_agreement_id);
1099 l_allocated_amount := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_allocated_amount,l_funding_currency_code);
1100
1101 /* Bug 14666538 */
1102
1103 get_MCB2_attributes (
1104 p_project_id => p_project_id,
1105 p_agreement_id => p_agreement_id,
1106 p_date_allocated => p_date_allocated,
1107 p_allocated_amount => l_allocated_amount,
1108 p_funding_currency_code => l_funding_currency_code,
1109 p_project_currency_code => l_project_currency_code,
1110 p_project_rate_type => l_project_rate_type,
1111 p_project_rate_date => l_project_rate_date,
1112 p_project_exchange_rate => l_project_exchange_rate,
1113 p_project_allocated_amount => l_project_allocated_amount,
1114 p_projfunc_currency_code => l_projfunc_currency_code,
1115 p_projfunc_rate_type => l_projfunc_rate_type,
1116 p_projfunc_rate_date => l_projfunc_rate_date,
1117 p_projfunc_exchange_rate => l_projfunc_exchange_rate,
1118 p_projfunc_allocated_amount => l_projfunc_allocated_amount,
1119 p_invproc_currency_code => l_invproc_currency_code,
1120 p_invproc_rate_type => l_invproc_rate_type,
1121 p_invproc_rate_date => l_invproc_rate_date,
1122 p_invproc_exchange_rate => l_invproc_exchange_rate,
1123 p_invproc_allocated_amount => l_invproc_allocated_amount,
1124 p_revproc_currency_code => l_revproc_currency_code,
1125 p_revproc_rate_type => l_revproc_rate_type,
1126 p_revproc_rate_date => l_revproc_rate_date,
1127 p_revproc_exchange_rate => l_revproc_exchange_rate,
1128 p_revproc_allocated_amount => l_revproc_allocated_amount,
1129 p_validate_parameters => 'Y',
1130 x_err_code => l_err_code,
1131 x_err_msg => l_err_msg
1132 );
1133 x_err_code := l_err_code;
1134 x_err_msg := l_err_msg;
1135
1136 if x_err_code = 0 then
1137 pa_project_fundings_pkg.update_row(
1138 x_rowid => fun_rec.rowid,
1139 x_project_funding_id => p_project_funding_id,
1140 x_last_update_date => p_last_update_date,
1141 x_last_updated_by => p_last_updated_by,
1142 x_last_update_login => p_last_update_login,
1143 x_agreement_id => p_agreement_id,
1144 x_project_id => p_project_id,
1145 x_task_id => p_task_id,
1146 x_budget_type_code => p_budget_type_code,
1147 x_allocated_amount => l_allocated_amount,
1148 x_date_allocated => p_date_allocated,
1149 x_attribute_category => p_attribute_category,
1150 x_attribute1 => p_attribute1,
1151 x_attribute2 => p_attribute2,
1152 x_attribute3 => p_attribute3,
1153 x_attribute4 => p_attribute4,
1154 x_attribute5 => p_attribute5,
1155 x_attribute6 => p_attribute6,
1156 x_attribute7 => p_attribute7,
1157 x_attribute8 => p_attribute8,
1158 x_attribute9 => p_attribute9,
1159 x_attribute10 => p_attribute10,
1160 x_pm_funding_reference => p_pm_funding_reference,
1161 x_pm_product_code => p_pm_product_code,
1162 x_funding_currency_code => l_funding_currency_code,
1163 x_project_currency_code => l_project_currency_code,
1164 x_project_rate_type => l_project_rate_type,
1165 x_project_rate_date => l_project_rate_date,
1166 x_project_exchange_rate => l_project_exchange_rate,
1167 x_project_allocated_amount => l_project_allocated_amount,
1168 x_projfunc_currency_code => l_projfunc_currency_code,
1169 x_projfunc_rate_type => l_projfunc_rate_type,
1170 x_projfunc_rate_date => l_projfunc_rate_date,
1171 x_projfunc_exchange_rate => l_projfunc_exchange_rate,
1172 x_projfunc_allocated_amount => l_projfunc_allocated_amount,
1173 x_invproc_currency_code => l_invproc_currency_code,
1174 x_invproc_rate_type => l_invproc_rate_type,
1175 x_invproc_rate_date => l_invproc_rate_date,
1176 x_invproc_exchange_rate => l_invproc_exchange_rate,
1177 x_invproc_allocated_amount => l_invproc_allocated_amount,
1178 x_revproc_currency_code => l_revproc_currency_code,
1179 x_revproc_rate_type => l_revproc_rate_type,
1180 x_revproc_rate_date => l_revproc_rate_date,
1181 x_revproc_exchange_rate => l_revproc_exchange_rate,
1182 x_revproc_allocated_amount => l_revproc_allocated_amount,
1183 x_funding_category => p_funding_category /* Bug 2244796 */
1184 );
1185
1186 END IF;
1187
1188 END IF;
1189 CLOSE C;
1190 -- summary_funding.update_row;
1191
1192 EXCEPTION
1193
1194 WHEN OTHERS THEN
1195 --dbms_output.put_line(SQLERRM);
1196 x_err_code := SQLCODE;
1197 x_err_msg := SQLERRM;
1198
1199
1200 END update_funding;
1201
1202 --
1203 --Name: delete_funding
1204 --Type: PROCEDURE
1205 --Description: This procedure is used to delete a funding record in PA_PROJECT_FUNDINGS
1206 --Called subprograms: pa_project_fundings_pkg.delete_row
1207 --
1208 --
1209 --
1210 --History:
1211 -- 05-MAY-2000 Created Adwait Marathe.
1212 -- 15-MAY-2000 Created Nikhil Mishra
1213
1214 PROCEDURE Delete_funding(p_project_funding_id IN NUMBER)
1215 is
1216 CURSOR C IS
1217 SELECT rowid
1218 FROM PA_PROJECT_FUNDINGS
1219 WHERE project_funding_id = p_project_funding_id;
1220 fun_row_id VARCHAR2(2000);
1221 BEGIN
1222 OPEN C;
1223 FETCH C INTO fun_row_id;
1224 IF C%FOUND THEN
1225 --dbms_output.put_line('yes');
1226 pa_project_fundings_pkg.delete_row(fun_row_id);
1227 END IF;
1228 CLOSE C;
1229 END delete_funding;
1230 --
1231 --Name: lock_funding
1232 --Type: PROCEDURE
1233 --Description: This procedure is used to lock a funding record in PA_PROJECT_FUNDINGS
1234 --Called subprograms: pa_project_fundings_pkg.lock_row
1235 --
1236 --
1237 --
1238 --History:
1239 -- 05-MAY-2000 Created Adwait Marathe.
1240 -- 15-MAY-2000 Created Nikhil Mishra
1241
1242
1243 PROCEDURE Lock_funding
1244 (p_Project_Funding_Id IN NUMBER)
1245 is
1246 CURSOR C IS
1247 SELECT rowid,
1248 project_funding_id,
1249 agreement_id,
1250 project_id,
1251 task_id,
1252 budget_type_code,
1253 allocated_amount,
1254 date_allocated,
1255 attribute_category,
1256 attribute1,
1257 attribute2,
1258 attribute3,
1259 attribute4,
1260 attribute5,
1261 attribute6,
1262 attribute7,
1263 attribute8,
1264 attribute9,
1265 attribute10,
1266 pm_funding_reference,
1267 pm_product_code,
1268 funding_currency_code, project_currency_code, project_rate_type,
1269 project_rate_date, project_exchange_rate, project_allocated_amount,
1270 projfunc_currency_code, projfunc_rate_type, projfunc_rate_date,
1271 projfunc_exchange_rate, projfunc_allocated_amount,
1272 funding_category /* For Bug2244796 */
1273 FROM PA_PROJECT_FUNDINGS
1274 WHERE project_funding_id = p_Project_Funding_Id;
1275 fun_rec C%ROWTYPE;
1276 BEGIN
1277 OPEN C;
1278 FETCH C INTO fun_rec;
1279 IF C%FOUND THEN
1280 pa_project_fundings_pkg.lock_row(
1281 fun_rec.rowid,
1282 fun_rec.project_funding_id,
1283 fun_rec.agreement_id,
1284 fun_rec.project_id,
1285 fun_rec.task_id,
1286 fun_rec.budget_type_code,
1287 fun_rec.allocated_amount,
1288 fun_rec.date_allocated,
1289 fun_rec.attribute_category,
1290 fun_rec.attribute1,
1291 fun_rec.attribute2,
1292 fun_rec.attribute3,
1293 fun_rec.attribute4,
1294 fun_rec.attribute5,
1295 fun_rec.attribute6,
1296 fun_rec.attribute7,
1297 fun_rec.attribute8,
1298 fun_rec.attribute9,
1299 fun_rec.attribute10,
1300 fun_rec.pm_funding_reference,
1301 fun_rec.pm_product_code,
1302 fun_rec.funding_currency_code,
1303 fun_rec.project_currency_code,
1304 fun_rec.project_rate_type,
1305 fun_rec.project_rate_date,
1306 fun_rec.project_exchange_rate,
1307 fun_rec.project_allocated_amount,
1308 fun_rec.projfunc_currency_code,
1309 fun_rec.projfunc_rate_type,
1310 fun_rec.projfunc_rate_date,
1311 fun_rec.projfunc_exchange_rate,
1312 fun_rec.projfunc_allocated_amount,
1313 fun_rec.funding_category /* For Bug2244796 */
1314 );
1315 END IF;
1316 CLOSE C;
1317 END lock_funding;
1318 --
1319 --Name: summary_funding_insert_row
1320 --Type: Procedure
1321 --Description: This procedure inserts row(s) in to PA_SUMMARY_PROJECT_FUNDINGS.
1322 --
1323 --Called subprograms: summary_fundings_update_row
1324 --
1325 --
1326 --
1327 --History:
1328 -- 15-MAY-2000 Created Nikhil Mishra.
1329 -- 21-AUG-2000 Modified Srividya.
1330 -- Added all columns corresponding to MCB2
1331 --
1332
1333 PROCEDURE summary_funding_insert_row
1334 (p_agreement_id IN NUMBER
1335 ,p_project_id IN NUMBER
1336 ,p_task_id IN NUMBER
1337 ,p_login_id IN VARCHAR2
1338 ,p_user_id IN VARCHAR2
1339 ,p_budget_type_code IN VARCHAR2
1340 )
1341 IS
1342 BEGIN
1343 declare
1344 cursor c1 is
1345 select 1
1346 from pa_summary_project_fundings
1347 where project_id = p_project_id
1348 and agreement_id = p_agreement_id
1349 and nvl(task_id, 0) = nvl(p_task_id, 0);
1350 dummy number;
1351
1352 BEGIN
1353
1354 open c1;
1355 fetch c1 into dummy;
1356
1357 IF (c1%found) THEN
1358 pa_funding_core.summary_funding_update_row ( p_agreement_id
1359 ,p_project_id
1360 ,p_task_id
1361 ,p_login_id
1362 ,p_user_id
1363 ,p_budget_type_code
1364 );
1365
1366
1367 ELSE
1368
1369 INSERT INTO PA_SUMMARY_PROJECT_FUNDINGS
1370 (AGREEMENT_ID, PROJECT_ID, TASK_ID,
1371 TOTAL_BASELINED_AMOUNT, TOTAL_UNBASELINED_AMOUNT,
1372 TOTAL_ACCRUED_AMOUNT, TOTAL_BILLED_AMOUNT,
1373 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1374 CREATION_DATE, CREATED_BY, FUNDING_CURRENCY_CODE,
1375 PROJECT_CURRENCY_CODE, PROJECT_BASELINED_AMOUNT,
1376 PROJECT_UNBASELINED_AMOUNT, PROJECT_ACCRUED_AMOUNT,
1377 PROJECT_BILLED_AMOUNT,
1378 PROJFUNC_CURRENCY_CODE, PROJFUNC_BASELINED_AMOUNT,
1379 PROJFUNC_UNBASELINED_AMOUNT, PROJFUNC_ACCRUED_AMOUNT,
1380 PROJFUNC_BILLED_AMOUNT,
1381 INVPROC_CURRENCY_CODE, INVPROC_BASELINED_AMOUNT,
1382 INVPROC_UNBASELINED_AMOUNT,
1383 INVPROC_BILLED_AMOUNT,
1384 REVPROC_CURRENCY_CODE, REVPROC_BASELINED_AMOUNT,
1385 REVPROC_UNBASELINED_AMOUNT, REVPROC_ACCRUED_AMOUNT)
1386 SELECT AGREEMENT_ID,PROJECT_ID,TASK_ID,
1387 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1388 NVL(ALLOCATED_AMOUNT,0))),0),
1389 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1390 NVL(ALLOCATED_AMOUNT,0))),0),
1391 0, 0, p_login_id, trunc(SYSDATE), p_user_id,
1392 trunc(SYSDATE), p_user_id, FUNDING_CURRENCY_CODE,
1393 PROJECT_CURRENCY_CODE,
1394 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1395 NVL(PROJECT_ALLOCATED_AMOUNT,0))),0),
1396 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1397 NVL(PROJECT_ALLOCATED_AMOUNT,0))),0),
1398 0, 0,
1399 PROJFUNC_CURRENCY_CODE,
1400 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1401 NVL(PROJFUNC_ALLOCATED_AMOUNT,0))),0),
1402 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1403 NVL(PROJFUNC_ALLOCATED_AMOUNT,0))),0),
1404 0, 0,
1405 INVPROC_CURRENCY_CODE,
1406 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1407 NVL(INVPROC_ALLOCATED_AMOUNT,0))),0),
1408 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1409 NVL(INVPROC_ALLOCATED_AMOUNT,0))),0),
1410 0,
1411 REVPROC_CURRENCY_CODE,
1412 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
1413 NVL(REVPROC_ALLOCATED_AMOUNT,0))),0),
1414 NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
1415 NVL(REVPROC_ALLOCATED_AMOUNT,0))),0),
1416 0
1417 FROM PA_PROJECT_FUNDINGS
1418 WHERE BUDGET_TYPE_CODE IN ('BASELINE','DRAFT')
1419 AND PROJECT_ID = p_project_id
1420 AND AGREEMENT_ID = p_agreement_id
1421 AND NVL(TASK_ID,0) = NVL(p_task_id,0)
1422 AND NOT EXISTS
1423 (select NULL from PA_SUMMARY_PROJECT_FUNDINGS S
1424 WHERE s.PROJECT_ID = p_project_id
1425 AND s.AGREEMENT_ID = p_agreement_id
1426 AND NVL(s.TASK_ID,0) = NVL(p_task_id,0))
1427 GROUP BY AGREEMENT_ID,PROJECT_ID,TASK_ID,FUNDING_CURRENCY_CODE,
1428 PROJECT_CURRENCY_CODE, PROJFUNC_CURRENCY_CODE,
1429 INVPROC_CURRENCY_CODE, REVPROC_CURRENCY_CODE
1430 ;
1431 END IF;
1432 close c1;
1433 exception
1434 WHEN NO_DATA_FOUND THEN
1435 pa_funding_core.summary_funding_update_row ( p_agreement_id
1436 ,p_project_id
1437 ,p_task_id
1438 ,p_login_id
1439 ,p_user_id
1440 ,p_budget_type_code
1441 );
1442 WHEN OTHERS THEN
1443 raise ;
1444
1445 end;
1446 END summary_funding_insert_row;
1447
1448
1449 --
1450 --Name: summary_fundings_update_row
1451 --Type: Procedure
1452 --Description: This procedure updates row(s) in to PA_SUMMARY_PROJECT_FUNDINGS.
1453 --
1454 --Called subprograms: pa_agreement_utils.summary_fundings_insert_row
1455 --
1456 --
1457 --
1458 --History:
1459 -- 15-MAY-2000 Created Nikhil Mishra.
1460 -- 21-AUG-2000 Modified Srividya.
1461 -- Added all columns corresponding to MCB2
1462 --
1463 PROCEDURE summary_funding_update_row
1464 (p_agreement_id IN NUMBER
1465 ,p_project_id IN NUMBER
1466 ,p_task_id IN NUMBER
1467 ,p_login_id IN VARCHAR2
1468 ,p_user_id IN VARCHAR2
1469 ,p_budget_type_code IN VARCHAR2
1470 )
1471 IS
1472 BEGIN
1473 --dbms_output.put_line('Inside: PA_AGREEMENT_CORE.SUMMARY_FUNDING_UPDATE_ROW');
1474 IF (p_budget_type_code = 'DRAFT') THEN
1475
1476 UPDATE PA_SUMMARY_PROJECT_FUNDINGS S
1477 SET (S.TOTAL_UNBASELINED_AMOUNT, S.PROJECT_UNBASELINED_AMOUNT,
1478 S.PROJFUNC_UNBASELINED_AMOUNT,
1479 S.INVPROC_UNBASELINED_AMOUNT, S.REVPROC_UNBASELINED_AMOUNT) =
1480 (SELECT SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1481 'DRAFT',nvl(F.ALLOCATED_AMOUNT,0))),
1482 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1483 'DRAFT',nvl(F.PROJECT_ALLOCATED_AMOUNT,0))),
1484 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1485 'DRAFT',nvl(F.PROJFUNC_ALLOCATED_AMOUNT,0))),
1486 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1487 'DRAFT',nvl(F.INVPROC_ALLOCATED_AMOUNT,0))),
1488 SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
1489 'DRAFT',nvl(F.REVPROC_ALLOCATED_AMOUNT,0)))
1490 FROM PA_PROJECT_FUNDINGS F
1491 WHERE F.PROJECT_ID = S.PROJECT_ID
1492 AND F.AGREEMENT_ID = S.AGREEMENT_ID
1493 AND NVL(F.TASK_ID,0) = NVL(S.TASK_ID,0)
1494 GROUP BY F.AGREEMENT_ID, F.PROJECT_ID, F.TASK_ID)
1495 WHERE S.AGREEMENT_ID = p_agreement_id
1496 AND S.PROJECT_ID = p_project_id
1497 AND NVL(S.TASK_ID,0) = NVL(p_task_id,0);
1498
1499 IF (SQL%NOTFOUND) THEN
1500
1501 pa_funding_core.summary_funding_insert_row ( p_agreement_id
1502 ,p_project_id
1503 ,p_task_id
1504 ,p_login_id
1505 ,p_user_id
1506 ,p_budget_type_code
1507 );
1508
1509 END IF;
1510 END IF;
1511
1512 END summary_funding_update_row;
1513
1514 --
1515 --Name: summary_funding_delete_row
1516 --Type: Procedure
1517 --Description: This procedure deletes row(s) in to PA_SUMMARY_PROJECT_FUNDINGS.
1518 --
1519 --Called subprograms: summary_fundings_insert_row
1520 --
1521 --
1522 --
1523 --History:
1524 -- 15-MAY-2000 Created Nikhil Mishra.
1525 --
1526
1527 PROCEDURE summary_funding_delete_row
1528 (p_agreement_id IN NUMBER
1529 ,p_project_id IN NUMBER
1530 ,p_task_id IN NUMBER
1531 ,p_login_id IN VARCHAR2
1532 ,p_user_id IN VARCHAR2
1533 ,p_budget_type_code IN VARCHAR2
1534 )
1535 IS
1536 BEGIN
1537 declare
1538 dummy number;
1539
1540 cursor d1 is
1541 select 1
1542 from pa_project_fundings
1543 where project_id = p_project_id
1544 and nvl(task_id, 0) = nvl(p_task_id, 0)
1545 and agreement_id = p_agreement_id;
1546 BEGIN
1547 open d1;
1548 fetch d1 into dummy;
1549 IF d1%found THEN
1550 pa_funding_core.summary_funding_update_row ( p_agreement_id
1551 ,p_project_id
1552 ,p_task_id
1553 ,p_login_id
1554 ,p_user_id
1555 ,p_budget_type_code
1556 );
1557
1558 ELSE
1559 DELETE FROM PA_SUMMARY_PROJECT_FUNDINGS S
1560 WHERE S.PROJECT_ID = p_project_id
1561 AND S.AGREEMENT_ID = p_agreement_id
1562 AND NVL(S.TASK_ID,0) = NVL(p_task_id,0);
1563 END IF;
1564 close d1;
1565 end;
1566
1567 exception
1568 WHEN OTHERS THEN
1569 raise ;
1570 END summary_funding_delete_row;
1571
1572 /*============================================================================+
1573 | Name : check_valid_exch_rate
1574 | Type: : FUNCTION
1575 | Description : This function will return
1576 | "T" - if rate type is invalid
1577 | "R" - if rate is invalid
1578 | "Y" - if both are valid
1579 | Created for MCB2
1580 +============================================================================*/
1581
1582
1583 FUNCTION check_valid_exch_rate (
1584 p_funding_currency_code IN VARCHAR2,
1585 p_to_currency_code IN VARCHAR2,
1586 p_exchange_rate_type IN VARCHAR2,
1587 p_exchange_rate IN NUMBER,
1588 p_exchange_rate_date IN DATE) RETURN VARCHAR2 IS
1589
1590 l_valid_rate_type VARCHAR2(1);
1591
1592 BEGIN
1593
1594
1595 SELECT 'Y' INTO l_valid_rate_type
1596 from pa_conversion_types_v
1597 /* WHERE user_conversion_type = p_exchange_rate_type Commented for bug 5478703 */
1598 WHERE conversion_type = p_exchange_rate_type /* Added for bug 5478703 */
1599 AND ( (p_exchange_rate_type = 'User'
1600 AND pa_multi_currency.is_user_rate_type_allowed(
1601 p_funding_currency_code,
1602 p_to_currency_code,
1603 p_exchange_rate_date )= 'Y')
1604 OR p_exchange_rate_type <> 'User');
1605
1606 /*
1607 SELECT 'Y' INTO l_valid_rate_type
1608 from pa_conversion_types_v
1609 WHERE user_conversion_type = p_exchange_rate_type;
1610 */
1611 IF p_exchange_rate_type = 'User' THEN
1612 IF p_exchange_rate is NULL then
1613 return 'R';
1614 END IF;
1615 END IF;
1616
1617 RETURN ('Y');
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 RETURN 'T';
1621
1622 END check_valid_exch_rate;
1623 /*============================================================================+
1624 | Name : get_MCB2_attributes
1625 | Type: : PROCEDURE
1626 | Description : This function will derive all MCB2 computed values based on
1627 | input parameters
1628 | Created for MCB2
1629 +============================================================================*/
1630
1631 PROCEDURE get_MCB2_attributes (
1632 p_project_id IN NUMBER,
1633 p_agreement_id IN NUMBER,
1634 p_date_allocated IN DATE,
1635 p_allocated_amount IN NUMBER,
1636 p_funding_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1637 p_project_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1638 p_project_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1639 p_project_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1640 p_project_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1641 p_project_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1642 p_projfunc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1643 p_projfunc_rate_type IN OUT NOCOPY VARCHAR2, /*file.sql.39*/
1644 p_projfunc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1645 p_projfunc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1646 p_projfunc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1647 p_invproc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1648 p_invproc_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1649 p_invproc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1650 p_invproc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1651 p_invproc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1652 p_revproc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1653 p_revproc_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
1654 p_revproc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
1655 p_revproc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
1656 p_revproc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
1657 p_validate_parameters IN VARCHAR2 DEFAULT 'N',
1658 x_err_code OUT NOCOPY NUMBER,/*file.sql.39*/
1659 x_err_msg OUT NOCOPY VARCHAR2/*file.sql.39*/
1660 ) is
1661
1662 l_multi_currency_billing_flag VARCHAR2(1);
1663 l_baseline_funding_flag VARCHAR2(1);
1664 l_funding_rate_date_code VARCHAR2(30);
1665 l_funding_rate_type VARCHAR2(30);
1666 l_funding_rate_date DATE;
1667 l_funding_exchange_rate NUMBER;
1668
1669 l_project_rate_date_code VARCHAR2(30);
1670 l_project_rate_type VARCHAR2(30);
1671 l_project_rate_date DATE;
1672 l_project_exchange_rate NUMBER;
1673 l_project_allocated_amount NUMBER;
1674
1675 l_projfunc_rate_date_code VARCHAR2(30);
1676 l_projfunc_rate_type VARCHAR2(30);
1677 l_projfunc_rate_date DATE;
1678 l_projfunc_exchange_rate NUMBER;
1679 l_projfunc_allocated_amount NUMBER;
1680
1681 l_invproc_currency_type VARCHAR2(30);
1682 l_invproc_rate_type VARCHAR2(30);
1683 l_invproc_rate_date DATE;
1684 l_invproc_exchange_rate NUMBER;
1685 l_invproc_allocated_amount NUMBER;
1686
1687 l_revproc_rate_type VARCHAR2(30);
1688 l_revproc_rate_date DATE;
1689 l_revproc_exchange_rate NUMBER;
1690 l_revproc_allocated_amount NUMBER;
1691
1692 l_return_status VARCHAR2(50);
1693 l_msg_count NUMBER;
1694 l_msg_data VARCHAR2(250);
1695
1696 l_denominator NUMBER;
1697 l_numerator NUMBER;
1698 l_validate VARCHAR2(1) := 'N';
1699 l_is_rate_type_valid VARCHAR2(1) := 'Y';
1700
1701
1702 l_err_code NUMBER;
1703 l_err_msg VARCHAR2(150);
1704
1705 l_mult_funding_flag VARCHAR2(1);
1706
1707 /*File.sql.39 . np variables are nocopy dummy variables define to
1708 revert the changes for any failure*/
1709 np_p_funding_currency_code VARCHAR2(50) := p_funding_currency_code;
1710 np_p_project_currency_code VARCHAR2(50) := p_project_currency_code;
1711 np_p_project_rate_type VARCHAR2(50) := p_project_rate_type;
1712 np_p_project_rate_date DATE := p_project_rate_date;
1713 np_p_project_exchange_rate NUMBER := p_project_exchange_rate;
1714 np_p_project_allocated_amount NUMBER := p_project_allocated_amount;
1715 np_p_projfunc_currency_code VARCHAR2(50) := p_projfunc_currency_code;
1716 np_p_projfunc_rate_type VARCHAR2(50) := p_projfunc_rate_type;
1717 np_p_projfunc_rate_date DATE := p_projfunc_rate_date;
1718 np_p_projfunc_exchange_rate NUMBER := p_projfunc_exchange_rate;
1719 np_p_projfunc_allocated_amount NUMBER := p_projfunc_allocated_amount;
1720 np_p_invproc_currency_code VARCHAR2(50) := p_invproc_currency_code;
1721 np_p_invproc_rate_type VARCHAR2(50) := p_invproc_rate_type;
1722 np_p_invproc_rate_date DATE := p_invproc_rate_date;
1723 np_p_invproc_exchange_rate NUMBER := p_invproc_exchange_rate;
1724 np_p_invproc_allocated_amount NUMBER := p_invproc_allocated_amount;
1725 np_p_revproc_currency_code VARCHAR2(50) := p_revproc_currency_code;
1726 np_p_revproc_rate_type VARCHAR2(50) := p_revproc_rate_type;
1727 np_p_revproc_rate_date DATE := p_revproc_rate_date;
1728 np_p_revproc_exchange_rate NUMBER := p_revproc_exchange_rate;
1729 np_p_revproc_allocated_amount NUMBER := p_revproc_allocated_amount;
1730 BEGIN
1731
1732 x_err_code := 0;
1733 x_err_msg := NULL;
1734 p_funding_currency_code :=
1735 pa_agreement_utils.get_agr_curr_code(p_agreement_id);
1736
1737 /* Added for bug 5478703 */
1738 IF p_project_rate_type is not null
1739 THEN
1740 SELECT conversion_type
1741 INTO p_project_rate_type
1742 FROM pa_conversion_types_v
1743 WHERE user_conversion_type = p_project_rate_type
1744 or conversion_type = p_project_rate_type;
1745 END IF;
1746
1747 IF p_projfunc_rate_type is not null
1748 THEN
1749 SELECT conversion_type
1750 INTO p_projfunc_rate_type
1751 FROM pa_conversion_types_v
1752 WHERE user_conversion_type = p_projfunc_rate_type
1753 or conversion_type = p_projfunc_rate_type;
1754 END IF;
1755 /* bug 5478703 - Code change Ends here*/
1756
1757 pa_multi_currency_billing.get_project_defaults(
1758 p_project_id => p_project_id,
1759 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
1760 x_baseline_funding_flag => l_baseline_funding_flag,
1761 x_revproc_currency_code => p_revproc_currency_code,
1762 x_invproc_currency_type => l_invproc_currency_type,
1763 x_invproc_currency_code => p_invproc_currency_code,
1764 x_project_currency_code => p_project_currency_code,
1765 x_project_bil_rate_date_code => l_project_rate_date_code,
1766 x_project_bil_rate_type => l_project_rate_type,
1767 x_project_bil_rate_date => l_project_rate_date,
1768 x_project_bil_exchange_rate => l_project_exchange_rate,
1769 x_projfunc_currency_code => p_projfunc_currency_code,
1770 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
1771 x_projfunc_bil_rate_type => l_projfunc_rate_type,
1772 x_projfunc_bil_rate_date => l_projfunc_rate_date,
1773 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
1774 x_funding_rate_date_code => l_funding_rate_date_code,
1775 x_funding_rate_type => l_funding_rate_type,
1776 x_funding_rate_date => l_funding_rate_date,
1777 x_funding_exchange_rate => l_funding_exchange_rate,
1778 x_return_status => l_return_status,
1779 x_msg_count => l_msg_count,
1780 x_msg_data => l_msg_data);
1781
1782 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1783
1784 x_err_code := 30;
1785 x_err_msg := l_msg_data;
1786
1787 end if;
1788
1789 /* Source and destination currency are same so null out attributes and copy amount */
1790
1791 if x_err_code = 0 then
1792
1793 if l_invproc_currency_type = 'FUNDING_CURRENCY' THEN
1794
1795 if p_invproc_currency_code is null then
1796
1797 p_invproc_currency_code := p_funding_currency_code;
1798
1799 end if;
1800
1801 l_mult_funding_flag := 'N';
1802
1803 BEGIN
1804
1805 SELECT 'Y' into l_mult_funding_flag
1806 FROM dual
1807 WHERE exists ( select null
1808 FROM PA_SUMMARY_PROJECT_FUNDINGS spf
1809 WHERE spf.project_id = p_project_id
1810 AND spf.funding_currency_code <> p_funding_currency_code
1811 AND spf.total_baselined_amount <> 0
1812 AND spf.total_unbaselined_amount <> 0);
1813
1814 EXCEPTION
1815
1816 when no_data_found then
1817
1818 l_mult_funding_flag := 'N';
1819
1820 END;
1821
1822
1823 if l_mult_funding_flag = 'Y' then
1824
1825 x_err_code := 30;
1826 x_err_msg := 'PA_MULTPLE_FUNDING_CURR';
1827
1828 end if;
1829
1830 end if;
1831
1832 end if;
1833
1834 if x_err_code = 0 then
1835
1836 if p_funding_currency_code = p_project_currency_code then
1837 p_project_rate_type := null;
1838 p_project_rate_date := null;
1839 p_project_exchange_rate := null;
1840 p_project_allocated_amount := p_allocated_amount;
1841
1842 else
1843
1844 if p_validate_parameters = 'Y' and p_project_rate_type is not null then
1845
1846 l_validate := 'Y';
1847
1848 end if;
1849
1850 p_project_rate_type := nvl(p_project_rate_type, l_project_rate_type);
1851 p_project_exchange_rate := nvl(p_project_exchange_rate,
1852 l_project_exchange_rate);
1853 if p_project_rate_date is null then
1854
1855 if l_project_rate_date_code = 'FIXED_DATE' then
1856 p_project_rate_date := l_project_rate_date;
1857 else
1858 p_project_rate_date := p_date_allocated;
1859 end if;
1860 end if;
1861
1862 if l_validate = 'Y' then
1863
1864 l_is_rate_type_valid := check_valid_exch_rate (
1865 p_funding_currency_code => p_funding_currency_code,
1866 p_to_currency_code => p_project_currency_code,
1867 p_exchange_rate_type => p_project_rate_type,
1868 p_exchange_rate_date => p_project_rate_date,
1869 p_exchange_rate => p_project_exchange_rate);
1870
1871 if l_is_rate_type_valid = 'R' then
1872
1873 x_err_code := '30';
1874 x_err_msg := 'PA_EXCH_RATE_NULL_PC';
1875
1876 elsif l_is_rate_type_valid = 'T' then
1877 x_err_code := '30';
1878 x_err_msg := 'PA_INVALID_RATE_TYPE_PC';
1879
1880 end if;
1881
1882 end if;
1883
1884 if x_err_code = 0 then
1885
1886 pa_multi_currency.convert_amount (
1887 p_from_currency => p_funding_currency_code,
1888 p_to_currency => p_project_currency_code,
1889 p_conversion_date => p_project_rate_date,
1890 p_conversion_type => p_project_rate_type,
1891 p_handle_exception_flag => 'Y',
1892 p_amount => p_allocated_amount,
1893 p_user_validate_flag => 'Y',
1894 p_converted_amount => p_project_allocated_amount,
1895 p_denominator => l_denominator,
1896 p_numerator => l_numerator,
1897 p_rate => p_project_exchange_rate,
1898 x_status => l_return_status);
1899
1900 if l_return_status is not null then
1901
1902 x_err_code := 30;
1903 /* Bug 2341576 - Prepended the _FC in the following message */
1904 x_err_msg := l_return_status || '_FC_PC';
1905
1906 end if;
1907
1908 end if;
1909
1910 end if;
1911
1912 end if;
1913
1914 if x_err_code = 0 then
1915
1916 if p_funding_currency_code = p_projfunc_currency_code then
1917 p_projfunc_rate_type := null;
1918 p_projfunc_rate_date := null;
1919 p_projfunc_exchange_rate := null;
1920 p_projfunc_allocated_amount := p_allocated_amount;
1921
1922 else
1923 if p_validate_parameters = 'Y' and p_projfunc_rate_type is not null then
1924
1925 l_validate := 'Y';
1926
1927 else
1928
1929 l_validate := 'N';
1930
1931 end if;
1932
1933 p_projfunc_rate_type := nvl(p_projfunc_rate_type,
1934 l_projfunc_rate_type);
1935 p_projfunc_exchange_rate := nvl(p_projfunc_exchange_rate,
1936 l_projfunc_exchange_rate);
1937 if p_projfunc_rate_date is null then
1938 if l_projfunc_rate_date_code = 'FIXED_DATE' then
1939 p_projfunc_rate_date := l_projfunc_rate_date;
1940 else
1941 p_projfunc_rate_date := p_date_allocated;
1942 end if;
1943 end if;
1944 if l_validate = 'Y' then
1945
1946 l_is_rate_type_valid := check_valid_exch_rate (
1947 p_funding_currency_code => p_funding_currency_code,
1948 p_to_currency_code => p_projfunc_currency_code,
1949 p_exchange_rate_type => p_projfunc_rate_type,
1950 p_exchange_rate_date => p_projfunc_rate_date,
1951 p_exchange_rate => p_projfunc_exchange_rate);
1952
1953 if l_is_rate_type_valid = 'R' then
1954
1955 x_err_code := '30';
1956 x_err_msg := 'PA_EXCH_RATE_NULL_PF';
1957
1958 elsif l_is_rate_type_valid = 'T' then
1959 x_err_code := '30';
1960 x_err_msg := 'PA_INVALID_RATE_TYPE_PF';
1961
1962 end if;
1963
1964 end if;
1965
1966 if x_err_code = 0 then
1967
1968 pa_multi_currency.convert_amount (
1969 p_from_currency => p_funding_currency_code,
1970 p_to_currency => p_projfunc_currency_code,
1971 p_conversion_date => p_projfunc_rate_date,
1972 p_conversion_type => p_projfunc_rate_type,
1973 p_handle_exception_flag => 'Y',
1974 p_amount => p_allocated_amount,
1975 p_user_validate_flag => 'Y',
1976 p_converted_amount => p_projfunc_allocated_amount,
1977 p_denominator => l_denominator,
1978 p_numerator => l_numerator,
1979 p_rate => p_projfunc_exchange_rate,
1980 x_status => l_return_status);
1981
1982 if l_return_status is not null then
1983
1984 x_err_code := 30;
1985 /* Bug 2341576 - Prepended the _FC in the following message */
1986 x_err_msg := l_return_status || '_FC_PF';
1987
1988 end if;
1989
1990 end if;
1991
1992 end if;
1993
1994 end if;
1995
1996 if x_err_code = 0 then
1997
1998 if p_funding_currency_code = p_invproc_currency_code then
1999 p_invproc_rate_type := null;
2000 p_invproc_rate_date := null;
2001 p_invproc_exchange_rate := null;
2002 p_invproc_allocated_amount := p_allocated_amount;
2003 elsif p_invproc_currency_code = p_project_currency_code then
2004 p_invproc_rate_type := p_project_rate_type;
2005 p_invproc_rate_date := p_project_rate_date;
2006 p_invproc_exchange_rate := p_project_exchange_rate;
2007 p_invproc_allocated_amount := p_project_allocated_amount;
2008 elsif p_invproc_currency_code = p_projfunc_currency_code then
2009 p_invproc_rate_type := p_projfunc_rate_type;
2010 p_invproc_rate_date := p_projfunc_rate_date;
2011 p_invproc_exchange_rate := p_projfunc_exchange_rate;
2012 p_invproc_allocated_amount := p_projfunc_allocated_amount;
2013 end if;
2014
2015 if p_funding_currency_code = p_revproc_currency_code then
2016 p_revproc_rate_type := null;
2017 p_revproc_rate_date := null;
2018 p_revproc_exchange_rate := null;
2019 p_revproc_allocated_amount := p_allocated_amount;
2020 elsif p_revproc_currency_code = p_project_currency_code then
2021 p_revproc_rate_type := p_project_rate_type;
2022 p_revproc_rate_date := p_project_rate_date;
2023 p_revproc_exchange_rate := p_project_exchange_rate;
2024 p_revproc_allocated_amount := p_project_allocated_amount;
2025 elsif p_revproc_currency_code = p_projfunc_currency_code then
2026 p_revproc_rate_type := p_projfunc_rate_type;
2027 p_revproc_rate_date := p_projfunc_rate_date;
2028 p_revproc_exchange_rate := p_projfunc_exchange_rate;
2029 p_revproc_allocated_amount := p_projfunc_allocated_amount;
2030 end if;
2031
2032 end if;
2033
2034 EXCEPTION
2035
2036 WHEN OTHERS THEN
2037 p_funding_currency_code := np_p_funding_currency_code;
2038 p_project_currency_code := np_p_project_currency_code;
2039 p_project_rate_type := np_p_project_rate_type;
2040 p_project_rate_date := np_p_project_rate_date;
2041 p_project_exchange_rate := np_p_project_exchange_rate;
2042 p_project_allocated_amount := np_p_project_allocated_amount;
2043 p_projfunc_currency_code := np_p_projfunc_currency_code;
2044 p_projfunc_rate_type := np_p_projfunc_rate_type;
2045 p_projfunc_rate_date := np_p_projfunc_rate_date;
2046 p_projfunc_exchange_rate := np_p_projfunc_exchange_rate;
2047 p_projfunc_allocated_amount := np_p_projfunc_allocated_amount;
2048 p_invproc_currency_code := np_p_invproc_currency_code;
2049 p_invproc_rate_type := np_p_invproc_rate_type;
2050 p_invproc_rate_date := np_p_invproc_rate_date;
2051 p_invproc_exchange_rate := np_p_invproc_exchange_rate;
2052 p_invproc_allocated_amount := np_p_invproc_allocated_amount;
2053 p_revproc_currency_code := np_p_revproc_currency_code;
2054 p_revproc_rate_type := np_p_revproc_rate_type;
2055 p_revproc_rate_date := np_p_revproc_rate_date;
2056 p_revproc_exchange_rate := np_p_revproc_exchange_rate;
2057 p_revproc_allocated_amount := np_p_revproc_allocated_amount;
2058
2059 x_err_code := SQLCODE;
2060 x_err_msg := SQLERRM;
2061
2062 END GET_MCB2_ATTRIBUTES;
2063
2064
2065 /*This is added for finplan impact on billing*/
2066 FUNCTION check_proj_task_lvl_funding_fp
2067 ( p_agreement_id IN NUMBER
2068 ,p_project_id IN NUMBER
2069 ,p_task_id IN NUMBER
2070 ) RETURN VARCHAR2
2071 is
2072 p_proposed_fund_level varchar2(1);
2073 l_return_status varchar2(2000);
2074 l_msg_count number;
2075 l_msg_data varchar2(2000);
2076 x_return_status varchar2(2000);
2077 BEGIN
2078 IF (p_task_id is null) then
2079 p_proposed_fund_level :='P';
2080 Pa_Fp_Control_Items_Utils.isFundingLevelChangeAllowed(
2081 p_project_id =>p_project_id,
2082 p_proposed_fund_level =>p_proposed_fund_level,
2083 x_return_status =>l_return_status,
2084 x_msg_count =>l_msg_count,
2085 x_msg_data =>l_msg_data);
2086 if (x_return_status=FND_API.G_RET_STS_ERROR)
2087 then return('A');
2088 end if;
2089 else
2090 p_proposed_fund_level :='T';
2091 Pa_Fp_Control_Items_Utils.isFundingLevelChangeAllowed(
2092 p_project_id =>p_project_id,
2093 p_proposed_fund_level =>p_proposed_fund_level,
2094 x_return_status =>l_return_status,
2095 x_msg_count =>l_msg_count,
2096 x_msg_data =>l_msg_data);
2097
2098 if (x_return_status=FND_API.G_RET_STS_ERROR)
2099 then return('A');
2100 end if;
2101 end if;
2102 return ('Y');
2103 end check_proj_task_lvl_funding_fp;
2104
2105 -- API for creating funding lines for Control Items changes
2106 PROCEDURE create_funding_CO(
2107 p_Rowid IN OUT NOCOPY VARCHAR2,/*FILE.sql.39*/
2108 p_Project_Funding_Id IN OUT NOCOPY NUMBER,/*FILE.sql.39*/
2109 p_Last_Update_Date IN DATE,
2110 p_Last_Updated_By IN NUMBER,
2111 p_Creation_Date IN DATE,
2112 p_Created_By IN NUMBER,
2113 p_Last_Update_Login IN NUMBER,
2114 p_Agreement_Id IN NUMBER,
2115 p_Project_Id IN NUMBER,
2116 p_Task_id IN NUMBER,
2117 p_Budget_Type_Code IN VARCHAR2,
2118 p_Allocated_Amount IN NUMBER,
2119 p_Date_Allocated IN DATE,
2120 P_Funding_Currency_Code IN VARCHAR2, -- FP_M CI changes
2121 p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M changes
2122 p_Attribute_Category IN VARCHAR2,
2123 p_Attribute1 IN VARCHAR2,
2124 p_Attribute2 IN VARCHAR2,
2125 p_Attribute3 IN VARCHAR2,
2126 p_Attribute4 IN VARCHAR2,
2127 p_Attribute5 IN VARCHAR2,
2128 p_Attribute6 IN VARCHAR2,
2129 p_Attribute7 IN VARCHAR2,
2130 p_Attribute8 IN VARCHAR2,
2131 p_Attribute9 IN VARCHAR2,
2132 p_Attribute10 IN VARCHAR2,
2133 p_pm_funding_reference IN VARCHAR2,
2134 p_pm_product_code IN VARCHAR2,
2135 p_Project_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
2136 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
2137 p_project_rate_date IN DATE DEFAULT NULL,
2138 p_project_exchange_rate IN NUMBER DEFAULT NULL,
2139 p_Projfunc_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
2140 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
2141 p_projfunc_rate_date IN DATE DEFAULT NULL,
2142 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
2143 x_err_code OUT NOCOPY NUMBER,/*FILE.sql.39*/
2144 x_err_msg OUT NOCOPY VARCHAR2,/*FILE.sql.39*/
2145 p_funding_category IN VARCHAR2 /* Bug 2244796 */
2146 )
2147 IS
2148 l_Project_Funding_Id NUMBER := p_Project_Funding_Id;
2149 l_err_msg VARCHAR2(150);
2150 l_err_code NUMBER;
2151 l_funding_currency_code VARCHAR2(15);
2152
2153 l_project_currency_code VARCHAR2(15);
2154 l_project_rate_type VARCHAR2(30);
2155 l_project_rate_date DATE;
2156 l_project_exchange_rate NUMBER;
2157 l_project_allocated_amount NUMBER;
2158
2159 l_projfunc_currency_code VARCHAR2(15);
2160 l_projfunc_rate_type VARCHAR2(30);
2161 l_projfunc_rate_date DATE;
2162 l_projfunc_exchange_rate NUMBER;
2163 l_projfunc_allocated_amount NUMBER;
2164
2165 l_invproc_currency_Type VARCHAR2(30);
2166 l_invproc_currency_code VARCHAR2(15);
2167 l_invproc_rate_type VARCHAR2(30);
2168 l_invproc_rate_date DATE;
2169 l_invproc_exchange_rate NUMBER;
2170 l_invproc_allocated_amount NUMBER;
2171
2172 l_revproc_currency_code VARCHAR2(15);
2173 l_revproc_rate_type VARCHAR2(30);
2174 l_revproc_rate_date DATE;
2175 l_revproc_exchange_rate NUMBER;
2176 l_revproc_allocated_amount NUMBER;
2177 BEGIN
2178
2179 --dbms_output.put_line('Inside: pa_funding_core.create_funding_CO');
2180
2181 Select Invproc_Currency_Type, Project_Currency_Code, ProjFunc_Currency_Code
2182 INTO l_Invproc_Currency_Type, l_Project_Currency_Code, l_ProjFunc_Currency_Code
2183 FROM PA_Projects
2184 Where Project_ID = P_Project_ID;
2185
2186 IF l_Invproc_Currency_Type = 'PROJECT_CURRENCY' THEN
2187 l_Invproc_Currency_Code := l_Project_Currency_Code;
2188 l_Invproc_Rate_Type := p_Project_Rate_Type;
2189 l_Invproc_Rate_Date := p_Project_Rate_Date;
2190 l_Invproc_Exchange_Rate := p_Project_Exchange_Rate;
2191 l_Invproc_Allocated_Amount := p_Project_Allocated_Amount;
2192 Elsif l_Invproc_Currency_Type = 'PROJFUNC_CURRENCY' THEN
2193 l_Invproc_Currency_Code := l_Projfunc_Currency_Code;
2194 l_Invproc_Rate_Type := p_Projfunc_Rate_Type;
2195 l_Invproc_Rate_Date := p_Projfunc_Rate_Date;
2196 l_Invproc_Exchange_Rate := p_Projfunc_Exchange_Rate;
2197 l_Invproc_Allocated_Amount := p_Projfunc_Allocated_Amount;
2198 Elsif l_Invproc_Currency_Type = 'FUNDING_CURRENCY' THEN
2199 l_Invproc_Currency_Code := p_Funding_Currency_Code;
2200 l_Invproc_Rate_Type := NULL;
2201 l_Invproc_Rate_Date := NULL;
2202 l_Invproc_Exchange_Rate := NULL;
2203 l_Invproc_Allocated_Amount := p_Allocated_Amount;
2204 END IF;
2205
2206 pa_project_fundings_pkg.insert_row(
2207 x_rowid => p_rowid,
2208 x_project_funding_id => p_project_funding_id,
2209 x_last_update_date => p_last_update_date,
2210 x_last_updated_by => p_last_updated_by,
2211 x_creation_date => p_creation_date,
2212 x_created_by => p_created_by,
2213 x_last_update_login => p_last_update_login,
2214 x_agreement_id => p_agreement_id,
2215 x_project_id => p_project_id,
2216 x_task_id => p_task_id,
2217 x_budget_type_code => p_budget_type_code,
2218 x_allocated_amount => p_allocated_amount,
2219 x_date_allocated => p_date_allocated,
2220 X_Control_Item_ID => p_Control_Item_ID, -- FP_M changes
2221 x_attribute_category => p_attribute_category,
2222 x_attribute1 => p_attribute1,
2223 x_attribute2 => p_attribute2,
2224 x_attribute3 => p_attribute3,
2225 x_attribute4 => p_attribute4,
2226 x_attribute5 => p_attribute5,
2227 x_attribute6 => p_attribute6,
2228 x_attribute7 => p_attribute7,
2229 x_attribute8 => p_attribute8,
2230 x_attribute9 => p_attribute9,
2231 x_attribute10 => p_attribute10,
2232 x_pm_funding_reference => p_pm_funding_reference,
2233 x_pm_product_code => p_pm_product_code,
2234 x_funding_currency_code => p_funding_currency_code,
2235 x_project_currency_code => l_project_currency_code,
2236 x_project_rate_type => p_project_rate_type,
2237 x_project_rate_date => p_project_rate_date,
2238 x_project_exchange_rate => p_project_exchange_rate,
2239 x_project_allocated_amount => p_project_allocated_amount,
2240 x_projfunc_currency_code => l_projfunc_currency_code,
2241 x_projfunc_rate_type => p_projfunc_rate_type,
2242 x_projfunc_rate_date => p_projfunc_rate_date,
2243 x_projfunc_exchange_rate => p_projfunc_exchange_rate,
2244 x_projfunc_allocated_amount => p_projfunc_allocated_amount,
2245 x_invproc_currency_code => l_invproc_currency_code,
2246 x_invproc_rate_type => l_invproc_rate_type,
2247 x_invproc_rate_date => l_invproc_rate_date,
2248 x_invproc_exchange_rate => l_invproc_exchange_rate,
2249 x_invproc_allocated_amount => l_invproc_allocated_amount,
2250 x_revproc_currency_code => l_projfunc_currency_code,
2251 x_revproc_rate_type => p_projfunc_rate_type,
2252 x_revproc_rate_date => p_projfunc_rate_date,
2253 x_revproc_exchange_rate => p_projfunc_exchange_rate,
2254 x_revproc_allocated_amount => p_projfunc_allocated_amount,
2255 x_funding_category => p_funding_category /* For Bug2244796 */
2256 );
2257
2258 --dbms_output.put_line('Done: create_funding_CO');
2259 EXCEPTION
2260
2261 WHEN OTHERS THEN
2262 x_err_code := SQLCODE;
2263 x_err_msg := SQLERRM;
2264 p_Project_Funding_Id := l_Project_Funding_Id;
2265 END create_funding_CO;
2266
2267 END PA_FUNDING_CORE;