[Home] [Help]
PACKAGE BODY: APPS.PA_AGREEMENT_CORE
Source
1 package body PA_AGREEMENT_CORE as
2 /*$Header: PAAFAGCB.pls 120.4 2007/02/07 10:45:25 rgandhi ship $*/
3
4 /*============================================================================+
5 | Name : check_multi_customers
6 | Type : FUNCTION
7 | Description : This function will return 'Y' IF the Project has
8 | Multiple-Customers
9 | Called subprograms: none
10 | History :
11 | 15-MAY-2000 Created Nikhil Mishra.
12 +============================================================================*/
13
14 FUNCTION check_multi_customers
15 ( p_project_id IN NUMBER
16 ) RETURN VARCHAR2
17 IS
18 multi_flag varchar2(1);
19 BEGIN
20 SELECT 'Y' into multi_flag
21 FROM PA_PROJECT_CUSTOMERS
22 WHERE PROJECT_ID = p_project_id
23 AND CUSTOMER_BILL_SPLIT NOT IN (100, 0)
24 HAVING COUNT(CUSTOMER_ID) > 1;
25
26 IF (multi_flag = 'Y') THEN
27 return 'Y';
28 ELSE
29 return 'N';
30 END IF;
31
32 exception
33 when NO_DATA_FOUND THEN return 'N';
34 when OTHERS THEN return 'N';
35 END check_multi_customers;
36
37
38 /*============================================================================+
39 | Name : check_contribution
40 | Type : FUNCTION
41 | Description : This function will return null
42 | Called subprograms: none
43 | History :
44 | 15-MAY-2000 Created Nikhil Mishra.
45 +============================================================================*/
46
47 FUNCTION check_contribution
48 ( p_agreement_id IN NUMBER
49 ) return varchar2
50 IS
51 BEGIN
52 return null;
53 END check_contribution;
54
55
56 /*============================================================================+
57 | Name : check_valid_customer
58 | Type : FUNCTION
59 | Description : This function will return various values.
60 | "N" - user is not a registered employee and he is not
61 | allowed to create agreement.
62 | Message is PA_ALL_WARN_NO_EMPL_REC
63 | "Y" - Valid
64 | Called subprograms: PA_UTILS.GetEmpIdFromUser
65 | History :
66 | 15-MAY-2000 Created Nikhil Mishra.
67 +============================================================================*/
68
69 FUNCTION check_valid_customer
70 ( p_customer_id IN NUMBER
71 ) RETURN VARCHAR2
72 is
73 cust_exists number;
74 BEGIN
75 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.CHECK_VALID_CUSTOMER');
76 -- dbms_output.put_line('Customer_id: '||nvl(to_char(p_customer_id),'NULL'));
77 -- Ensure that user is a registered EMPLOYEE.
78 IF PA_UTILS.GetEmpIdFromUser(to_number(fnd_profile.value('USER_ID')))
79 IS NULL THEN
80 RETURN 'N';
81 -- fnd_message.set_name ('PA', 'PA_ALL_WARN_NO_EMPL_REC');
82 ELSE
83 select 1 into cust_exists from dual where exists (
84 Select customer_name, customer_id, customer_number
85 from pa_customers_v where status = 'A' and
86 customer_id = p_customer_id);
87 IF cust_exists = 1
88 THEN RETURN 'Y';
89 END IF;
90 END IF;
91 EXCEPTION
92 when Others THEN return 'N';
93 END Check_valid_customer;
94
95
96 /*============================================================================+
97 | Name : check_valid_type
98 | Type : FUNCTION
99 | Description : This function will return 'Y' IF the agreement type is
100 | valid else N
101 | Called subprograms: none
102 | History :
103 | 15-MAY-2000 Created Nikhil Mishra.
104 +============================================================================*/
105
106 FUNCTION check_valid_type
107 ( p_agreement_type IN VARCHAR2
108 ) RETURN VARCHAR2 is
109 type_exists number;
110 BEGIN
111 Select 1 into type_exists
112 From Dual
113 Where Exists (
114 select 0
115 from pa_agreement_types atp, ra_terms rt
116 where atp.term_id = rt.term_id(+)
117 and trunc(sysdate) between trunc(atp.start_date_active)
118 and trunc(nvl(atp.end_date_active,sysdate))
119 and atp.agreement_type = p_agreement_type);
120 IF type_exists = 1
121 THEN RETURN 'Y';
122 END IF;
123
124 EXCEPTION
125 When Others THEN RETURN 'N';
126 END Check_valid_type;
127
128 --
129 --Name: check_invoice_exists
130 --Type: Function
131 --Description: Will return Y IF invoices exists for given agreement ELSE return N
132 --Called subprograms:None
133 --
134 --History:
135 -- 05-MAY-2000 Created Adwait Marathe.
136 --
137
138 /*============================================================================+
139 | Name : check_invoice_exists
140 | Type : FUNCTION
141 | Description : This function will return 'Y' IF invoices exists ELSE N
142 | Called subprograms: none
143 | History :
144 | 15-MAY-2000 Created Nikhil Mishra.
145 +============================================================================*/
146
147 FUNCTION check_invoice_exists
148 ( p_agreement_id IN NUMBER
149 ) RETURN VARCHAR2
150 IS
151 invoice_exists number;
152 BEGIN
153 select 1 into invoice_exists
154 from pa_draft_invoices_all
155 where agreement_id = p_agreement_id
156 and rownum=1;
157
158 IF invoice_exists = 1 THEN
159 RETURN 'Y';
160 END IF;
161
162 exception
163 when OTHERS THEN RETURN 'N';
164 END Check_invoice_exists;
165
166 /*============================================================================+
167 | Name : check_valid_term_id
168 | Type : FUNCTION
169 | Description : This function will return 'Y' IF term id is valid ELSE N
170 | Called subprograms: none
171 | History :
172 | 15-MAY-2000 Created Nikhil Mishra.
173 +============================================================================*/
174
175 FUNCTION check_valid_term_id
176 (p_term_id IN NUMBER
177 ) RETURN VARCHAR2
178 IS
179 term_exists number;
180 BEGIN
181 Select 1
182 into Term_exists
183 From Dual
184 Where Exists (
185 select 0
186 from ra_terms
187 where trunc(sysdate) between start_date_active
188 and nvl(end_date_active, trunc(sysdate))
189 and term_id = p_term_id);
190
191 IF Term_Exists = 1
192 THEN RETURN 'Y';
193 END IF;
194 EXCEPTION
195 When Others THEN RETURN 'N';
196 END Check_valid_term_id;
197
198 /*============================================================================+
199 | Name : check_valid_owned_by_person_id
200 | Type : FUNCTION
201 | Description : This function will return 'Y' IF person_id is valid ELSE N
202 | Called subprograms: none
203 | History :
204 | 15-MAY-2000 Created Nikhil Mishra.
205 +============================================================================*/
206
207 FUNCTION check_valid_owned_by_person_id
208 ( p_owned_by_person_id IN NUMBER
209 ) RETURN VARCHAR2
210 IS
211 person_id_exists number;
212 BEGIN
213 select 1 into Person_Id_Exists
214 from Dual Where Exists (
215 select 0 from pa_employees
216 where person_id = p_owned_by_person_id);
217 IF Person_Id_Exists = 1
218 THEN RETURN 'Y';
219 END IF;
220
221 EXCEPTION
222 When Others THEN RETURN 'N';
223 END Check_valid_owned_by_person_id;
224
225
226 /*============================================================================+
227 | Name : check_unique_agreement
228 | Type : FUNCTION
229 | Description : This function will return 'Y' IF the combination of
230 | Agreement_Number, Agreement_type, Customer is unique
231 | ELSE will return 'N' Message is PA_BU_AGRMNT_NOT_UNIQUE
232 | Called subprograms: none
233 | History :
234 | 15-MAY-2000 Created Nikhil Mishra.
235 +============================================================================*/
236
237 FUNCTION check_unique_agreement
238 ( p_agreement_num IN VARCHAR2
239 ,p_agreement_type IN VARCHAR2
240 ,p_customer_id IN NUMBER
241 ) RETURN VARCHAR2
242 IS
243 not_unique number;
244
245 BEGIN
246 select 1 into not_unique
247 from pa_agreements p
248 where p.customer_id = p_customer_id
249 and p.agreement_num = p_agreement_num
250 and p.agreement_type = p_agreement_type;
251
252 IF (not_unique = 1) THEN
253 RETURN 'N';
254 END IF;
255
256 exception
257 when NO_DATA_FOUND THEN RETURN 'Y';
258 when OTHERS THEN RETURN 'N';
259
260 END Check_unique_agreement;
261
262 --
263 --Name: check_valid_agreement_ref
264 --Type: Function
265 --Description: This function will return 'Y' IF the Agreement_reference
266 -- is valid
267 -- ELSE will return 'N'
268 --
269 --Called subprograms: none
270 --
271 --
272 --History:
273 -- 15-MAY-2000 Created Nikhil Mishra
274 --
275
276 FUNCTION check_valid_agreement_ref
277 (p_agreement_reference IN VARCHAR2
278 )
279 RETURN VARCHAR2
280 IS
281 CURSOR c IS
282 SELECT 1
283 FROM PA_AGREEMENTS_ALL A
284 WHERE A.pm_agreement_reference = p_agreement_reference;
285 l_row c%ROWTYPE;
286 BEGIN
287 -- dbms_output.put_line('PA_AGREEMENT_CORE.CHECK_VALID_AGREEMENT_REF');
288 OPEN C;
289 FETCH C INTO l_row;
290 IF C%FOUND THEN
291 RETURN 'Y';
292 ELSE
293 RETURN 'N';
294 END IF;
295 CLOSE C;
296 END check_valid_agreement_ref;
297
298 --
299 --Name: check_valid_agreement_id
300 --Type: Function
301 --Description: This function will return 'Y' IF the Agreement_Id
302 -- is valid
303 -- ELSE will return 'N'
304 --
305 --Called subprograms: none
306 --
307 --
308 --History:
309 -- 15-MAY-2000 Created Nikhil Mishra
310 --
311
312 FUNCTION check_valid_agreement_id
313 (p_agreement_id IN NUMBER
314 )
315 RETURN VARCHAR2
316 IS
317 CURSOR c IS
318 SELECT 1
319 FROM PA_AGREEMENTS_ALL A
320 WHERE A.agreement_id = p_agreement_id;
321 l_row c%ROWTYPE;
322 BEGIN
323 OPEN C;
324 FETCH C INTO l_row;
325 IF C%FOUND THEN
326 RETURN 'Y';
327 ELSE
328 RETURN 'N';
329 END IF;
330 CLOSE C;
331 END check_valid_agreement_id;
332
333
334 --
335 --Name: check_valid_funding_ref
336 --Type: Function
337 --Description: This function will return 'Y' IF the funding_reference
338 -- is valid
339 -- ELSE will return 'N'
340 --
341 --Called subprograms: none
342 --
343 --
344 --History:
345 -- 15-MAY-2000 Created Nikhil Mishra
346 --
347
348 FUNCTION check_valid_funding_ref
349 (p_funding_reference IN VARCHAR2
350 ,p_agreement_id IN NUMBER
351 )
352 RETURN VARCHAR2
353 IS
354 CURSOR c IS
355 SELECT 1
356 FROM PA_PROJECT_FUNDINGS F
357 WHERE F.pm_funding_reference = p_funding_reference
358 AND F.agreement_id = p_agreement_id ;
359 l_row c%ROWTYPE;
360 BEGIN
361 -- dbms_output.put_line(' Inside: PA_AGREEMENT_CORE.CHECK_VALID_FUNDING_REF');
362 OPEN C;
363 FETCH C INTO l_row;
364 IF C%FOUND THEN
365 RETURN 'Y';
366 ELSE
367 RETURN 'N';
368 END IF;
369 CLOSE C;
370 END check_valid_funding_ref;
371
372 --
373 --Name: check_valid_funding_id
374 --Type: Function
375 --Description: This function will return 'Y' IF the funding_Id
376 -- is valid
377 -- ELSE will return 'N'
378 --
379 --Called subprograms: none
380 --
381 --
382 --History:
383 -- 15-MAY-2000 Created Nikhil Mishra
384 --
385
386 FUNCTION check_valid_funding_id
387 (p_agreement_id IN NUMBER
388 ,p_funding_id IN NUMBER
389 )
390 RETURN VARCHAR2
391 IS
392 CURSOR c IS
393 SELECT 1
394 FROM PA_PROJECT_FUNDINGS F
395 WHERE F.project_funding_id = p_funding_id
396 AND F.agreement_id = p_agreement_id ;
397 l_row c%ROWTYPE;
398 BEGIN
399 OPEN C;
400 FETCH C INTO l_row;
401 IF C%FOUND THEN
402 RETURN 'Y';
403 ELSE
404 RETURN 'N';
405 END IF;
406 CLOSE C;
407 END check_valid_funding_id;
408
409 --
410 --Name: validate_agreement_amount
411 --Type: Function
412 --Description: This function will return 'Y' IF the Agreement amount enetered
413 -- is valid i.e. the amount entered should always be greater than
414 -- total baselined and unbaselined amount for that agreement_id;
415 -- IF returning 'N' indicating invalid amount THEN message is
416 -- PA_BU_AMOUNT_NOT_UPDATEABLE
417 --
418 --Called subprograms: none
419 --
420 --
421 --History:
422 -- 05-MAY-2000 Created Adwait Marathe.
423 --
424
425
426 FUNCTION validate_agreement_amount
427 ( p_agreement_id IN NUMBER
428 ,p_amount IN NUMBER
429 ) RETURN VARCHAR2
430 IS
431 l_tot_baselined_amt number;
432 l_tot_unbaselined_amt number;
433 BEGIN
434 Select nvl(sum(total_baselined_amount),0),
435 nvl(sum(total_unbaselined_amount),0)
436 into l_tot_baselined_amt,
437 l_tot_unbaselined_amt
438 From Pa_Summary_Project_Fundings
439 where Agreement_id = p_Agreement_id;
440 IF (nvl(p_amount, 0) <
441 nvl(l_tot_unbaselined_amt, 0) +
442 nvl(l_tot_baselined_amt, 0)) THEN
443 RETURN 'N';
444 END IF;
445 Exception When No_Data_Found THEN RETURN 'Y';
446 When Others THEN RETURN 'Y';
447 END Validate_agreement_amount;
448
449 --
450 --Name: check_add_update
451 --Type: FUNCTION
452 --Description: This function will return 'U' if update is required or 'A' if insert is required .
453 --
454 --Called subprograms: none
455 --
456 --
457 --History:
458 -- 15-MAY-2000 Created Nikhil Mishra.
459 --
460
461 FUNCTION check_add_update
462 ( p_funding_id IN NUMBER
463 ,p_funding_reference IN VARCHAR2
464 )
465 RETURN VARCHAR2
466 IS
467
468 CURSOR c1
469 IS
470 SELECT f.project_funding_id
471 FROM PA_PROJECT_FUNDINGS f
472 WHERE f.project_funding_id = p_funding_id;
473
474 CURSOR c2
475 IS
476 SELECT f.project_funding_id
477 FROM PA_PROJECT_FUNDINGS f
478 WHERE f.pm_funding_reference = p_funding_reference;
479
480 l_fund_rec1 c1%ROWTYPE;
481 l_fund_rec2 c2%ROWTYPE;
482
483 BEGIN
484 IF p_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
485 OPEN c1;
486 FETCH c1 INTO l_fund_rec1;
487 IF c1%FOUND THEN
488 RETURN 'U';
489 ELSE
490 RETURN 'A';
491 END IF;
492 CLOSE c1;
493 ELSIF p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
494 OPEN c2;
495 FETCH c2 INTO l_fund_rec2;
496 IF c2%FOUND THEN
497 RETURN 'U';
498 ELSE
499 RETURN 'A';
500 END IF;
501 CLOSE c2;
502 END IF;
503
504 END check_add_update;
505
506 --
507 --Name: get_agreement_id
508 --Type: FUNCTION
509 --Description: This procedure will get the corresponding agreement_id for the funding_id or funding _reference given
510 --
511 --Called subprograms: none
512 --
513 --
514 --History:
515 -- 15-MAY-2000 Created Nikhil Mishra.
516 --
517
518 FUNCTION get_agreement_id
519 ( p_funding_id IN NUMBER
520 ,p_funding_reference IN VARCHAR2
521 )
522 RETURN NUMBER
523 IS
524
525 CURSOR c1
526 IS
527 SELECT f.agreement_id
528 FROM PA_PROJECT_FUNDINGS f
529 WHERE f.project_funding_id = p_funding_id;
530
531 CURSOR c2
532 IS
533 SELECT f.agreement_id
534 FROM PA_PROJECT_FUNDINGS f
535 WHERE f.pm_funding_reference = p_funding_reference;
536
537 l_fund_rec1 c1%ROWTYPE;
538 l_fund_rec2 c2%ROWTYPE;
539 --Nikhil changed c1 to c2 and c2 to c1
540 BEGIN
541 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.GET_AGREEMENT_ID');
542 -- dbms_output.put_line('p_funding_id = '||nvl(to_char(p_funding_id),'NULL'));
543 -- dbms_output.put_line('p_funding_reference ='||nvl(p_funding_reference,'NULL'));
544 IF p_funding_reference is NOT NULL
545 -- OR (p_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
546 THEN
547 -- dbms_output.put_line('Funding Id is available: '||nvl(to_char(p_funding_id),'NULL'));
548 OPEN c2;
549 FETCH c2 INTO l_fund_rec2;
550 IF c2%FOUND THEN
551 -- dbms_output.put_line('Returning'||nvl(to_char(l_fund_rec2.agreement_id),'NULL'));
552 RETURN l_fund_rec2.agreement_id;
553 ELSE
554 -- dbms_output.put_line('NO VALUES WHY???');
555 NULL; -- Fix bug#1581381
556 END IF;
557 CLOSE c2;
558 ELSIF p_funding_id is NOT NULL
559 -- OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
560 THEN
561 -- dbms_output.put_line('Funding ref is available: '||nvl(p_funding_reference,'NULL'));
562 OPEN c1;
563 FETCH c1 INTO l_fund_rec1;
564 IF c1%FOUND THEN
565 -- dbms_output.put_line('Returning'||nvl(to_char(l_fund_rec1.agreement_id),'NULL'));
566 RETURN l_fund_rec1.agreement_id;
567 END IF;
568 CLOSE c2;
569 END IF;
570 /* -- dbms_output.put_line('Returning'||'RAJ');
571 exception
572 when others then
573 -- dbms_output.put_line(SQLERRM);
574 */
575 END get_agreement_id;
576
577
578 --
579 --Name: get_project_id
580 --Type: FUNCTION
581 --Description: This procedure will get the corresponding project_id for the funding_id or funding _reference given
582 --
583 --Called subprograms: none
584 --
585 --
586 --History:
587 -- 15-MAY-2000 Created Nikhil Mishra.
588 --
589
590 FUNCTION get_project_id
591 ( p_funding_id IN NUMBER
592 ,p_funding_reference IN VARCHAR2
593 )
594 RETURN NUMBER
595 IS
596
597 CURSOR c1
598 IS
599 SELECT f.project_id
600 FROM PA_PROJECT_FUNDINGS f
601 WHERE f.project_funding_id = p_funding_id;
602
603 CURSOR c2
604 IS
605 SELECT f.project_id
606 FROM PA_PROJECT_FUNDINGS f
607 WHERE f.pm_funding_reference = p_funding_reference;
608
609 l_fund_rec1 c1%ROWTYPE;
610 l_fund_rec2 c2%ROWTYPE;
611
612 -- Nikhil changed c1 to c2
613
614 BEGIN
615 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.GET_PROJECT_ID');
616 IF p_funding_reference IS NOT NULL
617 -- OR p_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
618 THEN
619 OPEN c2;
620 FETCH c2 INTO l_fund_rec2;
621 IF c2%FOUND THEN
622 RETURN l_fund_rec2.project_id;
623 END IF;
624 CLOSE c2;
625 ELSIF p_funding_id is NOT NULL
626 -- OR p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
627 THEN
628 OPEN c1;
629 FETCH c1 INTO l_fund_rec1;
630 IF c1%FOUND THEN
631 RETURN l_fund_rec1.project_id;
632 END IF;
633 CLOSE c1;
634 END IF;
635
636 END get_project_id;
637
638
639 --
640 --Name: get_task_id
641 --Type: FUNCTION
642 --Description: This function will get the corresponding task_id for the funding_id or funding _reference given
643 --
644 --Called subprograms: none
645 --
646 --
647 --History:
648 -- 15-MAY-2000 Created Nikhil Mishra.
649 --
650
651 FUNCTION get_task_id
652 ( p_funding_id IN NUMBER
653 ,p_funding_reference IN VARCHAR2
654 )
655 RETURN NUMBER
656 IS
657
658 CURSOR c1
659 IS
660 SELECT f.task_id
661 FROM PA_PROJECT_FUNDINGS f
662 WHERE f.project_funding_id = p_funding_id;
663
664 CURSOR c2
665 IS
666 SELECT f.task_id
667 FROM PA_PROJECT_FUNDINGS f
668 WHERE f.pm_funding_reference = p_funding_reference;
669
670 l_fund_rec1 c1%ROWTYPE;
671 l_fund_rec2 c2%ROWTYPE;
672
673 BEGIN
674 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.GET_TASK_ID');
675 IF p_funding_reference IS NOT NULL
676 -- OR p_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
677 THEN
678 OPEN c2;
679 FETCH c2 INTO l_fund_rec2;
680 IF c2%FOUND THEN
681 RETURN l_fund_rec2.task_id;
682 END IF;
683 CLOSE c2;
684 ELSIF p_funding_id IS NOT NULL
685 -- OR p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
686 THEN
687 OPEN c1;
688 FETCH c1 INTO l_fund_rec1;
689 IF c1%FOUND THEN
690 RETURN l_fund_rec1.task_id;
691 END IF;
692 CLOSE c1;
693 END IF;
694
695 END get_task_id;
696
697 --
698 --Name: get_customer_id
699 --Type: FUNCTION
700 --Description: This procedure will get the corresponding customer_id for the funding_id or funding_reference given
701 --
702 --Called subprograms: none
703 --
704 --
705 --History:
706 -- 15-MAY-2000 Created Nikhil Mishra.
707 --
708
709
710 FUNCTION get_customer_id
711 ( p_funding_id IN NUMBER
712 ,p_funding_reference IN VARCHAR2
713 )
714 RETURN NUMBER
715 IS
716
717 CURSOR c1
718 IS
719 SELECT A.customer_id
720 FROM PA_AGREEMENTS_ALL A
721 WHERE A.agreement_id = (SELECT f.agreement_id
722 FROM PA_PROJECT_FUNDINGS f
723 WHERE f.project_funding_id = p_funding_id);
724
725 CURSOR c2
726 IS
727 SELECT A.customer_id
728 FROM PA_AGREEMENTS_ALL A
729 WHERE A.agreement_id = (SELECT f.agreement_id
730 FROM PA_PROJECT_FUNDINGS f
731 WHERE f.pm_funding_reference = p_funding_reference);
732
733 l_fund_rec1 c1%ROWTYPE;
734 l_fund_rec2 c2%ROWTYPE;
735
736 BEGIN
737 /** Giving higher precedence to funding_id over funding_reference to determine customer_id bug 2434153 **/
738 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.GET_CUSTOMER_ID');
739 IF p_funding_id IS NOT NULL
740 AND (p_funding_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
741 THEN
742 OPEN c1;
743 FETCH c1 INTO l_fund_rec1;
744 IF c1%FOUND THEN
745 -- dbms_output.put_line('Returning Customer Id: '||nvl(to_char(l_fund_rec1.customer_id),'NULL'));
746 RETURN l_fund_rec1.customer_id;
747 END IF;
748 CLOSE c1;
749 ELSIF p_funding_reference IS NOT NULL
750 AND (p_funding_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
751 THEN
752 OPEN c2;
753 FETCH c2 INTO l_fund_rec2;
754 IF c2%FOUND THEN
755 -- dbms_output.put_line('Returning Customer Id: '||nvl(to_char(l_fund_rec2.customer_id),'NULL'));
756 RETURN l_fund_rec2.customer_id;
757 END IF;
758 CLOSE c2;
759 END IF;
760
761 END get_customer_id;
762
763
764 /*============================================================================+
765 | Name : create_agreement
766 | Type : PROCEDURE
767 | Description : This procedure will insert one row in to PA_AGREEMENTS_ALL
768 | History :
769 | 15-MAY-2000 Created Nikhil Mishra.
770 | 07-SEP-2001 Modified Srividya
771 | Added all new columns used in MCB2
772 +============================================================================*/
773 PROCEDURE create_agreement(
774 p_Rowid IN OUT NOCOPY VARCHAR2,/*File.sql.39*/
775 p_Agreement_Id IN OUT NOCOPY NUMBER,/*File.sql.39*/
776 p_Customer_Id IN NUMBER,
777 p_Agreement_Num IN VARCHAR2,
778 p_Agreement_Type IN VARCHAR2,
779 p_Last_Update_Date IN DATE,
780 p_Last_Updated_By IN NUMBER,
781 p_Creation_Date IN DATE,
782 p_Created_By IN NUMBER,
783 p_Last_Update_Login IN NUMBER,
784 p_Owned_By_Person_Id IN NUMBER,
785 p_Term_Id IN NUMBER,
786 p_Revenue_Limit_Flag IN VARCHAR2,
787 p_Amount IN NUMBER,
788 p_Description IN VARCHAR2,
789 p_Expiration_Date IN DATE,
790 p_Attribute_Category IN VARCHAR2,
791 p_Attribute1 IN VARCHAR2,
792 p_Attribute2 IN VARCHAR2,
793 p_Attribute3 IN VARCHAR2,
794 p_Attribute4 IN VARCHAR2,
795 p_Attribute5 IN VARCHAR2,
796 p_Attribute6 IN VARCHAR2,
797 p_Attribute7 IN VARCHAR2,
798 p_Attribute8 IN VARCHAR2,
799 p_Attribute9 IN VARCHAR2,
800 p_Attribute10 IN VARCHAR2,
801 p_Template_Flag IN VARCHAR2,
802 p_pm_agreement_reference IN VARCHAR2,
803 p_pm_product_code IN VARCHAR2,
804 p_agreement_currency_code IN VARCHAR2 DEFAULT NULL,
805 p_owning_organization_id IN NUMBER DEFAULT NULL,
806 p_invoice_limit_flag IN VARCHAR2 DEFAULT NULL,
807 /*Federal*/
808 p_customer_order_number IN VARCHAR2 DEFAULT NULL,
809 p_advance_required IN VARCHAR2 DEFAULT NULL,
810 p_start_date IN DATE DEFAULT NULL,
811 p_billing_sequence IN NUMBER DEFAULT NULL,
812 p_line_of_account IN VARCHAR2 DEFAULT NULL,
813 p_Attribute11 IN VARCHAR2 DEFAULT NULL,
814 p_Attribute12 IN VARCHAR2 DEFAULT NULL,
815 p_Attribute13 IN VARCHAR2 DEFAULT NULL,
816 p_Attribute14 IN VARCHAR2 DEFAULT NULL,
817 p_Attribute15 IN VARCHAR2 DEFAULT NULL,
818 p_Attribute16 IN VARCHAR2 DEFAULT NULL,
819 p_Attribute17 IN VARCHAR2 DEFAULT NULL,
820 p_Attribute18 IN VARCHAR2 DEFAULT NULL,
821 p_Attribute19 IN VARCHAR2 DEFAULT NULL,
822 p_Attribute20 IN VARCHAR2 DEFAULT NULL,
823 p_Attribute21 IN VARCHAR2 DEFAULT NULL,
824 p_Attribute22 IN VARCHAR2 DEFAULT NULL,
825 p_Attribute23 IN VARCHAR2 DEFAULT NULL,
826 p_Attribute24 IN VARCHAR2 DEFAULT NULL,
827 p_Attribute25 IN VARCHAR2 DEFAULT NULL
828 )
829 is
830
831 l_agreement_currency_code VARCHAR2(30);
832 l_invoice_limit_flag VARCHAR2(1);
833 l_org_id Number; /* Shared Services*/
834 BEGIN
835 -- dbms_output.put_line('In CORE - create_agreement');
836 if p_agreement_currency_code is null then
837 l_agreement_currency_code := pa_currency.get_currency_code;
838 else
839 l_agreement_currency_code := p_agreement_currency_code;
840 end if;
841 if p_invoice_limit_flag is null then
842 l_invoice_limit_flag := p_revenue_limit_flag;
843 else
844 l_invoice_limit_flag := p_invoice_limit_flag;
845 end if;
846
847 /* Shared Services. Checking get_current_org_id has value*/
848 l_org_id := mo_global.get_current_org_id;
849
850 if l_org_id is null then
851 raise no_data_found;
852 end if;
853 /* End of change for Shared Services*/
854
855 pa_agreements_pkg.insert_row(
856 x_rowid => p_rowid,
857 x_agreement_id => p_agreement_id,
858 x_customer_id => p_customer_id,
859 x_agreement_num => p_agreement_num,
860 x_agreement_type => p_agreement_type,
861 x_last_update_date => p_last_update_date,
862 x_last_updated_by => p_last_updated_by,
863 x_creation_date => p_creation_date,
864 x_created_by => p_created_by,
865 x_last_update_login => p_last_update_login,
866 x_owned_by_person_id => p_owned_by_person_id,
867 x_term_id => p_term_id,
868 x_revenue_limit_flag => p_revenue_limit_flag,
869 x_amount => p_amount,
870 x_description => p_description,
871 x_expiration_date => p_expiration_date,
872 x_attribute_category => p_attribute_category,
873 x_attribute1 => p_attribute1,
874 x_attribute2 => p_attribute2,
875 x_attribute3 => p_attribute3,
876 x_attribute4 => p_attribute4,
877 x_attribute5 => p_attribute5,
878 x_attribute6 => p_attribute6,
879 x_attribute7 => p_attribute7,
880 x_attribute8 => p_attribute8,
881 x_attribute9 => p_attribute9,
882 x_attribute10 => p_attribute10,
883 x_template_flag => p_template_flag,
884 x_pm_agreement_reference => p_pm_agreement_reference,
885 x_pm_product_code => p_pm_product_code,
886 x_owning_organization_id => p_owning_organization_id,
887 x_agreement_currency_code => l_agreement_currency_code,
888 x_invoice_limit_flag => l_invoice_limit_flag,
889 x_org_id => l_org_id,
890 /*Federal*/
891 x_customer_order_number => p_customer_order_number,
892 x_advance_required => p_advance_required,
893 x_start_date => p_start_date,
894 x_billing_sequence => p_billing_sequence,
895 x_line_of_account => p_line_of_account,
896 x_attribute11 => p_attribute11,
897 x_attribute12 => p_attribute12,
898 x_attribute13 => p_attribute13,
899 x_attribute14 => p_attribute14,
900 x_attribute15 => p_attribute15,
901 x_attribute16 => p_attribute16,
902 x_attribute17 => p_attribute17,
903 x_attribute18 => p_attribute18,
904 x_attribute19 => p_attribute19,
905 x_attribute20 => p_attribute20,
906 x_attribute21 => p_attribute21,
907 x_attribute22 => p_attribute22,
908 x_attribute23 => p_attribute23,
909 x_attribute24 => p_attribute24,
910 x_attribute25 => p_attribute25);/* Shared Services*/
911
912 /* Added Below for File.sql.39*/
913 EXCEPTION
914 WHEN OTHERS THEN
915 p_rowid := NULL;
916 raise;
917 END Create_agreement;
918
919 /*============================================================================+
920 | Name : update_agreement
921 | Type : PROCEDURE
922 | Description : This procedure will update one row in to PA_AGREEMENTS_ALL
923 | History :
924 | 15-MAY-2000 Created Nikhil Mishra.
925 | 07-SEP-2001 Modified Srividya
926 | Added all new columns used in MCB2
927 +============================================================================*/
928 PROCEDURE update_agreement(
929 p_Agreement_Id IN NUMBER,
930 p_Customer_Id IN NUMBER,
931 p_Agreement_Num IN VARCHAR2,
932 p_Agreement_Type IN VARCHAR2,
933 p_Last_Update_Date IN DATE,
934 p_Last_Updated_By IN NUMBER,
935 p_Last_Update_Login IN NUMBER,
936 p_Owned_By_Person_Id IN NUMBER,
937 p_Term_Id IN NUMBER,
938 p_Revenue_Limit_Flag IN VARCHAR2,
939 p_Amount IN NUMBER,
940 p_Description IN VARCHAR2,
941 p_Expiration_Date IN DATE,
942 p_Attribute_Category IN VARCHAR2,
943 p_Attribute1 IN VARCHAR2,
944 p_Attribute2 IN VARCHAR2,
945 p_Attribute3 IN VARCHAR2,
946 p_Attribute4 IN VARCHAR2,
947 p_Attribute5 IN VARCHAR2,
948 p_Attribute6 IN VARCHAR2,
949 p_Attribute7 IN VARCHAR2,
950 p_Attribute8 IN VARCHAR2,
951 p_Attribute9 IN VARCHAR2,
952 p_Attribute10 IN VARCHAR2,
953 p_Template_Flag IN VARCHAR2,
954 p_pm_agreement_reference IN VARCHAR2,
955 p_pm_product_code IN VARCHAR2,
956 p_agreement_currency_code IN VARCHAR2 DEFAULT NULL,
957 p_owning_organization_id IN NUMBER DEFAULT NULL,
958 p_invoice_limit_flag IN VARCHAR2 DEFAULT NULL,
959 /*Federal*/
960 p_customer_order_number IN VARCHAR2 DEFAULT NULL,
961 p_advance_required IN VARCHAR2 DEFAULT NULL,
962 p_start_date IN DATE DEFAULT NULL,
963 p_billing_sequence IN NUMBER DEFAULT NULL,
964 p_line_of_account IN VARCHAR2 DEFAULT NULL,
965 p_Attribute11 IN VARCHAR2 DEFAULT NULL,
966 p_Attribute12 IN VARCHAR2 DEFAULT NULL,
967 p_Attribute13 IN VARCHAR2 DEFAULT NULL,
968 p_Attribute14 IN VARCHAR2 DEFAULT NULL,
969 p_Attribute15 IN VARCHAR2 DEFAULT NULL,
970 p_Attribute16 IN VARCHAR2 DEFAULT NULL,
971 p_Attribute17 IN VARCHAR2 DEFAULT NULL,
972 p_Attribute18 IN VARCHAR2 DEFAULT NULL,
973 p_Attribute19 IN VARCHAR2 DEFAULT NULL,
974 p_Attribute20 IN VARCHAR2 DEFAULT NULL,
975 p_Attribute21 IN VARCHAR2 DEFAULT NULL,
976 p_Attribute22 IN VARCHAR2 DEFAULT NULL,
977 p_Attribute23 IN VARCHAR2 DEFAULT NULL,
978 p_Attribute24 IN VARCHAR2 DEFAULT NULL,
979 p_Attribute25 IN VARCHAR2 DEFAULT NULL)
980 is
981 CURSOR C IS
982 SELECT rowid,agreement_currency_code,
983 owning_organization_id, invoice_limit_flag
984 FROM PA_AGREEMENTS_ALL
985 WHERE agreement_id = p_agreement_id;
986 agr_rec C%ROWTYPE;
987
988 l_agreement_currency_code VARCHAR2(30);
989 l_invoice_limit_flag VARCHAR2(1);
990 l_owning_organization_id NUMBER;
991
992 BEGIN
993 -- dbms_output.put_line('Calling: pa_agreement_core.update_agreement');
994 OPEN C;
995 FETCH C INTO agr_rec;
996 IF C%FOUND THEN
997 IF p_agreement_currency_code is null then
998 l_agreement_currency_code := agr_rec.agreement_currency_code;
999 else
1000 l_agreement_currency_code := p_agreement_currency_code;
1001 end if;
1002 if p_invoice_limit_flag is null then
1003 l_invoice_limit_flag := agr_rec.invoice_limit_flag;
1004 else
1005 l_invoice_limit_flag := p_invoice_limit_flag;
1006 end if;
1007
1008 pa_agreements_pkg.update_row(
1009 x_rowid => agr_rec.rowid,
1010 x_agreement_id => p_agreement_id,
1011 x_customer_id => p_customer_id,
1012 x_agreement_num => p_agreement_num,
1013 x_agreement_type => p_agreement_type,
1014 x_last_update_date => p_last_update_date,
1015 x_last_updated_by => p_last_updated_by,
1016 x_last_update_login => p_last_update_login,
1017 x_owned_by_person_id => p_owned_by_person_id,
1018 x_term_id => p_term_id,
1019 x_revenue_limit_flag => p_revenue_limit_flag,
1020 x_amount => p_amount,
1021 x_description => p_description,
1022 x_expiration_date => p_expiration_date,
1023 x_attribute_category => p_attribute_category,
1024 x_attribute1 => p_attribute1,
1025 x_attribute2 => p_attribute2,
1026 x_attribute3 => p_attribute3,
1027 x_attribute4 => p_attribute4,
1028 x_attribute5 => p_attribute5,
1029 x_attribute6 => p_attribute6,
1030 x_attribute7 => p_attribute7,
1031 x_attribute8 => p_attribute8,
1032 x_attribute9 => p_attribute9,
1033 x_attribute10 => p_attribute10,
1034 x_template_flag => p_template_flag,
1035 x_pm_agreement_reference => p_pm_agreement_reference,
1036 x_pm_product_code => p_pm_product_code,
1037 x_owning_organization_id => p_owning_organization_id,
1038 x_agreement_currency_code => l_agreement_currency_code,
1039 x_invoice_limit_flag => l_invoice_limit_flag,
1040 /*Federal*/
1041 x_customer_order_number => p_customer_order_number,
1042 x_advance_required => p_advance_required,
1043 x_start_date => p_start_date,
1044 x_billing_sequence => p_billing_sequence,
1045 x_line_of_account => p_line_of_account,
1046 x_attribute11 => p_attribute11,
1047 x_attribute12 => p_attribute12,
1048 x_attribute13 => p_attribute13,
1049 x_attribute14 => p_attribute14,
1050 x_attribute15 => p_attribute15,
1051 x_attribute16 => p_attribute16,
1052 x_attribute17 => p_attribute17,
1053 x_attribute18 => p_attribute18,
1054 x_attribute19 => p_attribute19,
1055 x_attribute20 => p_attribute20,
1056 x_attribute21 => p_attribute21,
1057 x_attribute22 => p_attribute22,
1058 x_attribute23 => p_attribute23,
1059 x_attribute24 => p_attribute24,
1060 x_attribute25 => p_attribute25);
1061 END IF;
1062 CLOSE C;
1063 END update_agreement;
1064
1065 /*============================================================================+
1066 | Name : delete_agreement
1067 | Type : PROCEDURE
1068 | Description : This procedure will delete one row from PA_AGREEMENTS_ALL
1069 | History :
1070 | 15-MAY-2000 Created Nikhil Mishra.
1071 +============================================================================*/
1072 procedure delete_agreement(p_agreement_id IN NUMBER)
1073 is
1074 CURSOR C IS
1075 SELECT rowid
1076 FROM PA_AGREEMENTS
1077 WHERE agreement_id = p_agreement_id;
1078 agr_row_id VARCHAR2(2000);
1079 BEGIN
1080 OPEN C;
1081 FETCH C INTO agr_row_id;
1082 IF C%FOUND THEN
1083 pa_agreements_pkg.delete_row(agr_row_id);
1084 END IF;
1085 CLOSE C;
1086 END delete_agreement;
1087
1088
1089 /*============================================================================+
1090 | Name : lock
1091 | Type : PROCEDURE
1092 | Description : This procedure will lock one row in to PA_AGREEMENTS_ALL
1093 | History :
1094 | 15-MAY-2000 Created Nikhil Mishra.
1095 | 07-SEP-2001 Modified Srividya
1096 | Added all new columns used in MCB2
1097 +============================================================================*/
1098
1099 PROCEDURE Lock_agreement(p_Agreement_Id IN NUMBER)
1100 is
1101 CURSOR C IS
1102 SELECT rowid,
1103 agreement_id,
1104 customer_id,
1105 agreement_num,
1106 agreement_type,
1107 owned_by_person_id,
1108 term_id,
1109 revenue_limit_flag ,
1110 amount,
1111 description,
1112 expiration_date,
1113 attribute_category,
1114 attribute1,
1115 attribute2,
1116 attribute3,
1117 attribute4,
1118 attribute5,
1119 attribute6,
1120 attribute7,
1121 attribute8 ,
1122 attribute9,
1123 attribute10,
1124 template_flag,
1125 pm_agreement_reference,
1126 pm_product_code,
1127 owning_organization_id,
1128 agreement_currency_code,
1129 invoice_limit_flag,
1130 /*Federal*/
1131 customer_order_number,
1132 advance_required,
1133 start_date,
1134 billing_sequence,
1135 line_of_account,
1136 attribute11,
1137 attribute12,
1138 attribute13,
1139 attribute14,
1140 attribute15,
1141 attribute16,
1142 attribute17,
1143 attribute18 ,
1144 attribute19,
1145 attribute20,
1146 attribute21,
1147 attribute22,
1148 attribute23,
1149 attribute24,
1150 attribute25
1151 FROM PA_AGREEMENTS
1152 WHERE agreement_id = p_agreement_id;
1153 agr_rec C%ROWTYPE;
1154 BEGIN
1155 OPEN C;
1156 FETCH C INTO agr_rec;
1157 IF C%FOUND THEN
1158 pa_agreements_pkg.lock_row (
1159 x_rowid => agr_rec.rowid,
1160 x_agreement_id => agr_rec.agreement_id,
1161 x_customer_id => agr_rec.customer_id,
1162 x_agreement_num => agr_rec.agreement_num,
1163 x_agreement_type => agr_rec.agreement_type,
1164 x_owned_by_person_id => agr_rec.owned_by_person_id,
1165 x_term_id => agr_rec.term_id,
1166 x_revenue_limit_flag => agr_rec.revenue_limit_flag,
1167 x_amount => agr_rec.amount,
1168 x_description => agr_rec.description,
1169 x_expiration_date => agr_rec.expiration_date,
1170 x_attribute_category => agr_rec.attribute_category,
1171 x_attribute1 => agr_rec.attribute1,
1172 x_attribute2 => agr_rec.attribute2,
1173 x_attribute3 => agr_rec.attribute3,
1174 x_attribute4 => agr_rec.attribute4,
1175 x_attribute5 => agr_rec.attribute5,
1176 x_attribute6 => agr_rec.attribute6,
1177 x_attribute7 => agr_rec.attribute7,
1178 x_attribute8 => agr_rec.attribute8,
1179 x_attribute9 => agr_rec.attribute9,
1180 x_attribute10 => agr_rec.attribute10,
1181 x_template_flag => agr_rec.template_flag,
1182 x_pm_agreement_reference => agr_rec.pm_agreement_reference,
1183 x_pm_product_code => agr_rec.pm_product_code,
1184 x_owning_organization_id => agr_rec.owning_organization_id,
1185 x_agreement_currency_code => agr_rec.agreement_currency_code,
1186 x_invoice_limit_flag => agr_rec.invoice_limit_flag,
1187 /*Federal*/
1188 x_customer_order_number => agr_rec.customer_order_number,
1189 x_advance_required => agr_rec.advance_required,
1190 x_start_date => agr_rec.start_date,
1191 x_billing_sequence => agr_rec.billing_sequence,
1192 x_line_of_account => agr_rec.line_of_account,
1193 x_attribute11 => agr_rec.attribute11,
1194 x_attribute12 => agr_rec.attribute12,
1195 x_attribute13 => agr_rec.attribute13,
1196 x_attribute14 => agr_rec.attribute14,
1197 x_attribute15 => agr_rec.attribute15,
1198 x_attribute16 => agr_rec.attribute16,
1199 x_attribute17 => agr_rec.attribute17,
1200 x_attribute18 => agr_rec.attribute18,
1201 x_attribute19 => agr_rec.attribute19,
1202 x_attribute20 => agr_rec.attribute20,
1203 x_attribute21 => agr_rec.attribute21,
1204 x_attribute22 => agr_rec.attribute22,
1205 x_attribute23 => agr_rec.attribute23,
1206 x_attribute24 => agr_rec.attribute24,
1207 x_attribute25 => agr_rec.attribute25);
1208 END IF;
1209 END lock_agreement;
1210
1211 /*============================================================================+
1212 | Name : check_revenue_limit
1213 | Type : FUNCTION
1214 | Description : This function
1215 | History :
1216 | 15-MAY-2000 Created Nikhil Mishra.
1217 | 07-SEP-2001 Modified Srividya
1218 | changed the select to check only for accrued amount
1219 +============================================================================*/
1220
1221 FUNCTION check_revenue_limit
1222 ( p_agreement_id IN NUMBER
1223 )
1224 RETURN VARCHAR2
1225 IS
1226 l_check_limit NUMBER;
1227 BEGIN
1228 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.CHECK_REVENUE_LIMIT');
1229 /*
1230 SELECT MIN(SIGN((f1.total_baselined_amount+f1.total_unbaselined_amount)
1231 -GREATEST(NVL(f1.total_accrued_amount,0), NVL(f1.total_billed_amount, 0))))
1232 INTO check_limit
1233 FROM pa_summary_project_fundings f1
1234 WHERE f1.agreement_id = p_agreement_id;
1235 */
1236 /* commented and rewritten for bug 2744993
1237 SELECT MIN(SIGN((f1.total_baselined_amount+f1.total_unbaselined_amount)
1238 - NVL(f1.total_accrued_amount, 0)))
1239 */
1240 SELECT MIN(SIGN((f1.projfunc_baselined_amount+f1.projfunc_unbaselined_amount)
1241 - NVL(f1.projfunc_accrued_amount, 0)))
1242 INTO l_check_limit
1243 FROM pa_summary_project_fundings f1
1244 WHERE f1.agreement_id = p_agreement_id;
1245
1246 IF l_check_limit < 0 then
1247 -- dbms_output.put_line('Returning N');
1248 RETURN 'N';
1249 ELSE
1250 -- dbms_output.put_line('Returning Y');
1251 RETURN 'Y';
1252 END IF;
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 -- dbms_output.put_line('Returning N');
1256 RETURN 'N';
1257 END check_revenue_limit;
1258
1259
1260 --Name: check_budget_type
1261 --Type: FUNCTION
1262 --Description: This function will return 'Y' IF the Project has budget_type_code as 'DRAFT'
1263 -- ELSE will return 'N'
1264 --
1265 --Called subprograms: None
1266 --
1267 --
1268 --History:
1269 -- 15-MAY-2000 Created Nikhil Mishra.
1270 --
1271
1272 FUNCTION check_budget_type
1273 ( p_funding_id IN NUMBER
1274 )
1275 RETURN VARCHAR2
1276 IS
1277 budget_type_code VARCHAR2(20);
1278 BEGIN
1279 -- dbms_output.put_line('Inside" PA_AGREEMENT_CORE.CHECK_BUDGET_TYPE');
1280 SELECT budget_type_code
1281 INTO budget_type_code
1282 FROM pa_project_fundings f1
1283 WHERE f1.project_funding_id = p_funding_id;
1284
1285 IF budget_type_code = 'DRAFT' then
1286 RETURN 'Y';
1287 ELSE
1288 RETURN 'N';
1289 END IF;
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN
1292 RETURN 'N';
1293 END check_budget_type;
1294
1295 /*============================================================================+
1296 | Name: get_agr_curr_code
1297 | Type: FUNCTION
1298 | Description: This function will return agreement_currency_code for
1299 | the agreement_id
1300 | Created for MCB2
1301 +============================================================================*/
1302 FUNCTION get_agr_curr_code (p_agreement_id IN NUMBER)
1303 RETURN VARCHAR2 IS
1304 l_currency_code VARCHAR2(30);
1305 BEGIN
1306 SELECT agreement_currency_code INTO l_currency_code
1307 FROM pa_agreements_all
1308 WHERE agreement_id = p_agreement_id;
1309 RETURN (l_currency_code);
1310 EXCEPTION
1311 WHEN NO_DATA_FOUND THEN
1312 RETURN 'ERROR';
1313 END get_agr_curr_code;
1314
1315 /*============================================================================+
1316 | Name: check_valid_owning_orgn_id
1317 | Type: FUNCTION
1318 | Description: This function will return "Y" if owning organization id
1319 | is valid else N
1320 | Created for MCB2
1321 +============================================================================*/
1322 FUNCTION check_valid_owning_orgn_id (
1323 p_owning_organization_id IN NUMBER)
1324 RETURN VARCHAR2 IS
1325
1326 l_valid_flag VARCHAR2(1);
1327 BEGIN
1328 SELECT 'Y' INTO l_valid_flag
1329 FROM pa_organizations_project_v
1330 WHERE organization_id = p_owning_organization_id
1331 AND SYSDATE BETWEEN DECODE (date_from, NULL, SYSDATE, date_from)
1332 AND DECODE(date_to, NULL, SYSDATE,date_to);
1333 RETURN 'Y';
1334 EXCEPTION
1335 WHEN NO_DATA_FOUND THEN
1336 RETURN 'N';
1337 END check_valid_owning_orgn_id;
1338
1339 /*============================================================================+
1340 | Name: check_valid_agr_curr_code
1341 | Type: FUNCTION
1342 | Description: This function will return "Y" if agreement currency
1343 | code is valid
1344 | Created for MCB2
1345 +============================================================================*/
1346 FUNCTION check_valid_agr_curr_code (
1347 p_agreement_currency_code IN VARCHAR2)
1348 RETURN VARCHAR2 IS
1349
1350 l_valid_flag VARCHAR2(1);
1351 l_multi_currency_billing_flag VARCHAR2(1);
1352 l_share_bill_rates_across_ou VARCHAR2(1);
1353 l_allow_funding_across_ou VARCHAR2(1);
1354 l_default_exchange_rate_type VARCHAR2(30);
1355 l_functional_currency VARCHAR2(30);
1356 l_return_status VARCHAR2(30);
1357 l_msg_count NUMBER;
1358 l_msg_data VARCHAR2(100);
1359 l_competence_match_wt NUMBER;
1360 l_availability_match_wt NUMBER;
1361 l_job_level_match_wt NUMBER;
1362
1363 BEGIN
1364
1365 /* Bug#4403200 - Replace the View fnd_currencies_vl with table fnd_currencies
1366 for performance issue */
1367
1368 SELECT 'Y' INTO l_valid_flag
1369 FROM fnd_currencies
1370 WHERE currency_code = p_agreement_currency_code
1371 AND SYSDATE BETWEEN DECODE (start_date_active, NULL, SYSDATE,
1372 start_date_active)
1373 AND DECODE(end_date_active, NULL, SYSDATE,end_date_active);
1374
1375 if l_valid_flag = 'Y' then
1376
1377 pa_multi_currency_billing.get_imp_defaults(
1378 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
1379 x_share_bill_rates_across_ou => l_share_bill_rates_across_ou,
1380 x_allow_funding_across_ou => l_allow_funding_across_ou,
1381 x_default_exchange_rate_type => l_default_exchange_rate_type,
1382 x_functional_currency => l_functional_currency,
1383 x_competence_match_wt => l_competence_match_wt,
1384 x_availability_match_wt => l_availability_match_wt,
1385 x_job_level_match_wt => l_job_level_match_wt,
1386 x_return_status => l_return_status,
1387 x_msg_count => l_msg_count,
1388 x_msg_data => l_msg_data);
1389
1390 if (l_multi_currency_billing_flag = 'N' AND
1391 p_agreement_currency_code <> l_functional_currency) THEN
1392 RETURN 'N';
1393 ELSE
1394 RETURN 'Y';
1395 END IF;
1396 ELSE
1397 RETURN 'N';
1398 END IF;
1399
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 RETURN 'N';
1403 END check_valid_agr_curr_code;
1404
1405 /*============================================================================+
1406 | Name: check_invoice_limit
1407 | Type: FUNCTION
1408 | Description: This function will return "Y"
1409 | else N
1410 +============================================================================*/
1411 FUNCTION check_invoice_limit
1412 ( p_agreement_id IN NUMBER
1413 )
1414 RETURN VARCHAR2
1415 IS
1416 l_check_limit NUMBER;
1417 BEGIN
1418 -- dbms_output.put_line('Inside: PA_AGREEMENT_CORE.CHECK_INVOICE_LIMIT');
1419 /* commented and rewritten for bug 2744993
1420 SELECT MIN(SIGN((f1.total_baselined_amount+f1.total_unbaselined_amount)
1421 - NVL(f1.total_billed_amount, 0)))
1422 */
1423 SELECT MIN(SIGN((f1.invproc_baselined_amount+f1.invproc_unbaselined_amount)
1424 - NVL(f1.invproc_billed_amount, 0)))
1425 INTO l_check_limit
1426 FROM pa_summary_project_fundings f1
1427 WHERE f1.agreement_id = p_agreement_id;
1428
1429 IF l_check_limit < 0 then
1430 -- dbms_output.put_line('Returning N');
1431 RETURN 'N';
1432 ELSE
1433 -- dbms_output.put_line('Returning Y');
1434 RETURN 'Y';
1435 END IF;
1436 EXCEPTION
1437 WHEN NO_DATA_FOUND THEN
1438 -- dbms_output.put_line('Returning N');
1439 RETURN 'N';
1440 END check_invoice_limit;
1441
1442 END PA_AGREEMENT_CORE;
1443