[Home] [Help]
PACKAGE BODY: APPS.OKE_FUNDING_UTIL_PKG
Source
1 package body OKE_FUNDING_UTIL_PKG as
2 /* $Header: OKEFUTLB.pls 120.0 2005/05/25 17:37:54 appldev noship $ */
3
4
5 --
6 -- Procedure : validate_source_pool_amount
7 --
8 -- Purpose : check if there is enough funding from the pool party to be allocated
9 --
10 -- Parameters :
11 -- (in) x_first_amount number amount
12 -- x_source_id number funding_source_id
13 -- x_pool_party_id number pool_party_id
14 -- x_new_flag varchar2 new funding source record
15 -- Y : new funding source
16 --
17 -- (out) x_return_status varchar2 return status
18 -- Y : valid
19 -- N : invalid
20 --
21
22 PROCEDURE validate_source_pool_amount(x_first_amount number ,
23 x_source_id number ,
24 x_pool_party_id number ,
25 x_new_flag varchar2 ,
26 x_return_status OUT NOCOPY varchar2 ) is
27 cursor c_pool_party is
28 select amount, available_amount
29 from oke_pool_parties
30 where pool_party_id = x_pool_party_id;
31
32 cursor c_source is
33 select amount
34 from oke_k_funding_sources
35 where funding_source_id = x_source_id;
36
37 l_amount number;
38 l_available_amount number;
39 --l_allocated_amount number;
40 l_delta number;
41 l_orig_amount number;
42
43 begin
44
45 OPEN c_pool_party;
46 FETCH c_pool_party INTO l_amount, l_available_amount;
47 CLOSE c_pool_party;
48
49 if (x_new_flag = 'Y') then
50
51 l_delta := x_first_amount;
52
53 else
54
55 OPEN c_source;
56 FETCH c_source INTO l_orig_amount;
57 CLOSE c_source;
58
59 -- l_allocated_amount := l_amount - l_available_amount;
60 l_delta := x_first_amount - l_orig_amount;
61
62 end if;
63
64 if (l_delta <= l_available_amount) then
65
66 -- if (l_amount >= l_available_amount - l_delta) then
67
68 x_return_status := 'Y';
69
70 -- else
71
72 -- x_return_status := 'N';
73
74 -- end if;
75
76 else
77
78 x_return_status := 'N';
79
80 end if;
81
82 exception
83 when NO_DATA_FOUND then
84 x_return_status := 'N';
85 if (c_source%ISOPEN) then
86 close c_source;
87 elsif (c_pool_party%ISOPEN) then
88 close c_pool_party;
89 end if;
90
91 end validate_source_pool_amount;
92
93
94
95 --
96 -- Procedure : validate_source_pool_date
97 --
98 -- Purpose : check if
99 -- 1) funding source start date assocated w/ the pool party >= pool party start date
100 -- 2) funding source end date associated w/ the pool party <= pool party end date
101 --
102 -- Parameters :
103 -- (in) x_start_end varchar2 date validation choice
104 -- START : start date
105 -- END : end date
106 -- x_pool_party_id number pool party id
107 -- x_date date date to be validated
108 --
109 -- (out) x_return_status varchar2 return status
110 -- Y : valid
111 -- N : invalid
112 --
113
114 PROCEDURE validate_source_pool_date(x_start_end varchar2 ,
115 x_pool_party_id number ,
116 x_date date ,
117 x_return_status OUT NOCOPY varchar2 ) is
118
119 cursor c_start_date is
120 select nvl(start_date_active, x_date)
121 from oke_pool_parties
122 where pool_party_id = x_pool_party_id;
123
124 cursor c_end_date is
125 select nvl(end_date_active, x_date)
126 from oke_pool_parties
127 where pool_party_id = x_pool_party_id;
128
129 compare_date date;
130
131 begin
132
133 if (x_start_end = 'START') then
134
135 open c_start_date;
136 fetch c_start_date into compare_date;
137
138 if (c_start_date%notfound) then
139 close c_start_date;
140 raise no_data_found;
141 end if;
142
143 if (x_date >= compare_date) or
144 (x_date is null and compare_date is null) then
145
146 x_return_status := 'Y';
147
148 else
149
150 x_return_status := 'N';
151
152 end if;
153
154 close c_start_date;
155
156 else
157
158 open c_end_date;
159 fetch c_end_date into compare_date;
160
161 if (c_end_date%notfound) then
162 close c_end_date;
163 raise no_data_found;
164 end if;
165
166 if (x_date <= compare_date) or
167 (x_date is null and compare_date is null) then
168
169 x_return_status := 'Y';
170
171 else
172
173 x_return_status := 'N';
174
175 end if;
176
177 close c_end_date;
178
179 end if;
180
181 exception
182 when NO_DATA_FOUND then
183 x_return_status := 'N';
184
185 end validate_source_pool_date;
186
187
188
189 --
190 -- Procedure : validate_alloc_source_amount
191 --
192 -- Purpose : check if the new funding source amount >= sum of its allocations
193 --
194 -- Parameters :
195 -- (in) x_source_id number funding source id
196 -- x_allocation_id number funding allocation id
197 -- x_amount number allocation amount
198 --
199 -- (out) x_return_status varchar2 return status
200 -- Y : valid
201 -- N : invalid
202 --
203
204 PROCEDURE validate_alloc_source_amount(x_source_id number ,
205 x_allocation_id number ,
206 x_amount number ,
207 x_return_status OUT NOCOPY varchar2 ) is
208
209 cursor c_alloc is
210 select nvl(sum(amount), 0)
211 from oke_k_fund_allocations
212 where funding_source_id = x_source_id;
213
214 cursor c_source is
215 select amount
216 from oke_k_funding_sources
217 where funding_source_id = x_source_id;
218
219 cursor c_existing is
220 select amount
221 from oke_k_fund_allocations
222 where fund_allocation_id = x_allocation_id;
223
224 source_amount number;
225 alloc_amount number := 0;
226 original_amount number;
227 diff_amount number := 0;
228 final_amount number := 0;
229
230 begin
231
232 if (x_allocation_id is not null) then
233
234 open c_existing;
235 fetch c_existing into original_amount;
236
237 if (c_existing%notfound) then
238 close c_existing;
239 raise no_data_found;
240 end if;
241
242 diff_amount := x_amount - original_amount;
243 final_amount := diff_amount;
244
245 close c_existing;
246
247 else
248 final_amount := x_amount;
249
250 end if;
251
252 open c_alloc;
253 fetch c_alloc into alloc_amount;
254
255 if (c_alloc%notfound) then
256 close c_alloc;
257 end if;
258
259 open c_source;
260 fetch c_source into source_amount;
261
262 if (c_source%notfound) then
263 close c_source;
264 raise no_data_found;
265 end if;
266
267 if ((nvl(alloc_amount, 0) + nvl(final_amount, 0)) < 0) then
268
269 x_return_status := 'E';
270
271 elsif (source_amount >= (alloc_amount + final_amount)) then
272
273 x_return_status := 'Y';
274
275 else
276
277 x_return_status := 'N';
278
279 end if;
280
281 close c_alloc;
282 close c_source;
283
284 end validate_alloc_source_amount;
285
286
287
288 --
289 -- Procedure : validate_alloc_source_limit
290 --
291 -- Purpose : check if
292 -- w/ allocation_id passed in :
293 -- there is enough funding source hard limit to be allocated for the newly allocated
294 -- hard limit
295 --
296 -- w/o allocaiton_id passed in
297 -- the new funding source hard limit is >= sum of its hard limit allocations
298 --
299 -- Parameters :
300 -- (in) x_source_id number funding source id
301 -- x_allocation_id number funding allocation id (optional)
302 -- x_amount number limit amount
303 -- x_revenue_amount number revenue hard limit
304 --
305 -- (out) x_type varchar2 hard limit type (INVOICE/REVENUE)
306 -- x_return_status varchar2 return status
307 -- Y : valid
308 -- N : invalid
309 --
310
311 PROCEDURE validate_alloc_source_limit(x_source_id number ,
312 x_allocation_id number ,
313 x_amount number ,
314 x_revenue_amount number ,
315 x_type OUT NOCOPY varchar2 ,
316 x_return_status OUT NOCOPY varchar2 ) is
317
318
319 cursor c_alloc is
320 select nvl(sum(hard_limit), 0), nvl(sum(revenue_hard_limit), 0)
321 from oke_k_fund_allocations
322 where funding_source_id = x_source_id;
323
324 cursor c_source is
325 select nvl(hard_limit, 0), nvl(revenue_hard_limit, 0)
326 from oke_k_funding_sources
327 where funding_source_id = x_source_id;
328
329 cursor c_existing is
330 select nvl(hard_limit, 0), nvl(revenue_hard_limit, 0)
331 from oke_k_fund_allocations
332 where fund_allocation_id = x_allocation_id;
333
334 source_hl_amount number;
335 source_rhl_amount number;
336 alloc_hl_amount number := 0;
337 alloc_rhl_amount number := 0;
338 orig_hl_amt number;
339 orig_rhl_amt number;
340 diff_hl_amount number := 0;
341 diff_rhl_amount number := 0;
342 final_hl_amount number := 0;
343 final_rhl_amount number := 0;
344
345 begin
346
347 x_type := null;
348
349 if (x_allocation_id is not null) then
350
351 open c_existing;
352 fetch c_existing into orig_hl_amt, orig_rhl_amt;
353
354 if (c_existing%notfound) then
355 close c_existing;
356 raise no_data_found;
357 end if;
358
359 diff_hl_amount := x_amount - orig_hl_amt;
360 diff_rhl_amount := x_revenue_amount - orig_rhl_amt;
361 final_hl_amount := diff_hl_amount;
362 final_rhl_amount := diff_rhl_amount;
363 close c_existing;
364
365 else
366 final_hl_amount := x_amount;
367 final_rhl_amount := x_revenue_amount;
368
369 end if;
370
371 open c_alloc;
372 fetch c_alloc into alloc_hl_amount, alloc_rhl_amount;
373
374 if (c_alloc%notfound) then
375 close c_alloc;
376 end if;
377
378 open c_source;
379 fetch c_source into source_hl_amount, source_rhl_amount;
380
381 if (c_source%notfound) then
382 close c_source;
383 raise no_data_found;
384 end if;
385
386 if ((nvl(alloc_hl_amount, 0) + nvl(final_hl_amount, 0)) < 0) then
387
388 x_return_status := 'E';
389 x_type := 'INVOICE';
390
391 elsif ((nvl(alloc_rhl_amount, 0) + nvl(final_rhl_amount, 0)) < 0) then
392
393 x_return_status := 'E';
394 x_type := 'REVENUE';
395
396 elsif (source_hl_amount >= (alloc_hl_amount + final_hl_amount)) then
397
398 if (source_rhl_amount >= (alloc_rhl_amount + final_rhl_amount)) then
399
400 x_return_status := 'Y';
401
402 else
403
404 x_return_status := 'N';
405 x_type := 'REVENUE';
406
407 end if;
408
409 else
410
411 x_return_status := 'N';
412 x_type := 'INVOICE';
413
414 end if;
415
416 close c_alloc;
417 close c_source;
418
419 end validate_alloc_source_limit;
420
421
422
423 --
424 -- Procedure : validate_pool_party_date
425 --
426 -- Purpose : check if
427 -- 1) pool party start date <= the earliest funding source start date associated w/ the pool party
428 -- 2) pool party end >= the latest funding source end date associated w/ the pool party
429 --
430 -- Parameters :
431 -- (in) x_start_end varchar2 date validation choice
432 -- START : start date
433 -- END : end date
434 -- x_pool_party_id number pool party id
435 -- x_date date date to be validated
436 --
437 -- (out) x_return_status varchar2 return status
438 -- Y : valid
439 -- N : invalid
440 --
441
442 PROCEDURE validate_pool_party_date(x_start_end varchar2 ,
443 x_pool_party_id number ,
444 x_date date ,
445 x_return_status OUT NOCOPY varchar2 ) is
446
447 cursor c_exist is
448 select 'x'
449 from oke_k_funding_sources
450 where pool_party_id = x_pool_party_id;
451
452 cursor c_start_date is
453 select nvl(min(start_date_active), add_months(x_date, -1))
454 from oke_k_funding_sources
455 where pool_party_id = x_pool_party_id;
456
457 cursor c_end_date is
458 select nvl(max(end_date_active), add_months(x_date, 1))
459 from oke_k_funding_sources
460 where pool_party_id = x_pool_party_id;
461
462 compare_date date;
463 l_dummy_value varchar2(1) := '?';
464
465 begin
466
467 open c_exist;
468 fetch c_exist into l_dummy_value;
469 close c_exist;
470
471 if (l_dummy_value <> '?') then
472
473 if (x_start_end = 'START') then
474
475 open c_start_date;
476 fetch c_start_date into compare_date;
477
478 if (x_date <= compare_date) then
479
480 x_return_status := 'Y';
481
482 else
483
484 x_return_status := 'N';
485
486 end if;
487
488 close c_start_date;
489
490 else
491
492 open c_end_date;
493 fetch c_end_date into compare_date;
494
495 if (x_date >= compare_date) then
496
497 x_return_status := 'Y';
498
499 else
500
501 x_return_status := 'N';
502
503 end if;
504
505 close c_end_date;
506
507 end if;
508
509 else
510
511 x_return_status := 'Y';
512
513 end if;
514
515 end validate_pool_party_date;
516
517
518
519 --
520 -- Function : allocation_exist
521 --
522 -- Purpose : check if funding has been allocated for particular funding pool party or not
523 --
524 -- Parameters : x_pool_party id number pool party id
525 --
526 -- Return : Y -- allocation exists
527 -- values N -- no allocation exists
528 --
529
530 FUNCTION allocation_exist(x_pool_party_id number) return varchar2 is
531
532 l_exist varchar2(1);
533 l_count number;
534
535 begin
536
537 select count(1)
538 into l_count
539 from oke_k_funding_sources
540 where pool_party_id = x_pool_party_id;
541
542 if l_count > 0 then
543
544 l_exist := 'Y';
545
546 else
547
548 l_exist := 'N';
549
550 end if;
551
552 return(l_exist);
553
554 exception
555 when OTHERS then
556 return('N');
557
558 end allocation_exist;
559
560
561
562 --
563 -- Procedure : validate_pool_party_amount
564 --
565 -- Purpose : check if the new pool party amount >= the allocated amount
566 --
567 -- Parameters :
568 -- (in) x_pool_party_id number pool party id
569 -- x_amount number new funding amount
570 --
571 -- (out) x_allocated_amount number calculated allocated amount
572 -- x_return_status varchar2 return status
573 -- Y : valid
574 -- N : invalid
575 --
576
577 PROCEDURE validate_pool_party_amount(x_pool_party_id number ,
578 x_amount number ,
579 x_allocated_amount OUT NOCOPY number ,
580 x_return_status OUT NOCOPY varchar2 ) is
581
582 cursor c_record is
583 select amount, available_amount
584 from oke_pool_parties
585 where pool_party_id = x_pool_party_id;
586
587 l_amount number;
588 l_available_amount number;
589
590 begin
591
592 open c_record;
593 fetch c_record into l_amount, l_available_amount;
594
595 if (c_record%notfound) then
596 close c_record;
597 raise no_data_found;
598 end if;
599
600 x_allocated_amount := l_amount - l_available_amount;
601
602 if (x_amount >= x_allocated_amount) then
603
604 x_return_status := 'Y';
605
606 else
607
608 x_return_status := 'N';
609
610 end if;
611
612 close c_record;
613
614 end;
615
616
617
618 --
619 -- Procedure : validate_source_alloc_date
620 --
621 -- Purpose : check if
622 -- 1) funding source start date <= the earliest funding allocation start date
623 -- 2) funding source end date >= the latest funding allocation end date
624 --
625 -- Parameters :
626 -- (in) x_start_end varchar2 date validation choice
627 -- START : start date
628 -- END : end date
629 -- x_funding_source_id number funding source id
630 -- x_date date date to be validated
631 --
632 -- (out) x_return_status varchar2 return status
633 -- Y : valid
634 -- N : invalid
635 --
636
637 PROCEDURE validate_source_alloc_date(x_start_end varchar2 ,
638 x_funding_source_id number ,
639 x_date date ,
640 x_return_status OUT NOCOPY varchar2 ) is
641
642 cursor c_start_allocation is
643 select min(nvl(start_date_active, add_months(x_date, -1)))
644 from oke_k_fund_allocations
645 where funding_source_id = x_funding_source_id;
646
647 cursor c_end_allocation is
648 select max(nvl(end_date_active, add_months(x_date, 1)))
649 from oke_k_fund_allocations
650 where funding_source_id = x_funding_source_id;
651
652 cursor c_scsr is
653 select start_date_active
654 from oke_k_fund_allocations
655 where funding_source_id = x_funding_source_id;
656
657 cursor c_ecsr is
658 select end_date_active
659 from oke_k_fund_allocations
660 where funding_source_id = x_funding_source_id;
661
662 l_date date;
663 compare_date date;
664
665 begin
666
667 if (x_start_end = 'START') then
668
669 open c_scsr;
670 fetch c_scsr into l_date;
671
672 if c_scsr%NOTFOUND then
673 close c_scsr;
674 raise no_data_found;
675 end if;
676 close c_scsr;
677
678 open c_start_allocation;
679 fetch c_start_allocation into compare_date;
680
681 if (x_date is null) or
682 (nvl(x_date, compare_date) <= compare_date) then
683
684 x_return_status := 'Y';
685
686 else
687
688 x_return_status := 'N';
689
690 end if;
691
692 close c_start_allocation;
693
694 else
695
696 open c_ecsr;
697 fetch c_ecsr into l_date;
698
699 if c_ecsr%NOTFOUND then
700 close c_ecsr;
701 raise no_data_found;
702 end if;
703 close c_ecsr;
704
705 open c_end_allocation;
706 fetch c_end_allocation into compare_date;
707
708 if (x_date is null) or
709 (nvl(x_date, compare_date) >= compare_date) then
710
711 x_return_status := 'Y';
712
713 else
714
715 x_return_status := 'N';
716
717 end if;
718
719 close c_end_allocation;
720
721 end if;
722
723 exception
724 when NO_DATA_FOUND then
725 x_return_status := 'Y';
726
727 end validate_source_alloc_date;
728
729
730
731 --
732 -- Procedure : validate_alloc_source_date
733 --
734 -- Purpose : check if
735 -- 1) funding allocation start date >= funding source start date
736 -- 2) funding allocation end date <= funding source end date
737 --
738 -- Parameters :
739 -- (in) x_start_end varchar2 date validation choice
740 -- START : start date
741 -- END : end date
742 -- x_funding_source_id number funding source id
743 -- x_date date date to be validated
744 --
745 -- (out) x_return_status varchar2 return status
746 -- Y : valid
747 -- N : invalid
748 --
749
750 PROCEDURE validate_alloc_source_date(x_start_end varchar2 ,
751 x_funding_source_id number ,
752 x_date date ,
753 x_return_status OUT NOCOPY varchar2 ) is
754
755 cursor c_start_date is
756 select nvl(start_date_active, x_date)
757 from oke_k_funding_sources
758 where funding_source_id = x_funding_source_id;
759
760 cursor c_end_date is
761 select nvl(end_date_active, x_date)
762 from oke_k_funding_sources
763 where funding_source_id = x_funding_source_id;
764
765 compare_date date;
766
767 begin
768
769 if (x_start_end = 'START') then
770
771 open c_start_date;
772 fetch c_start_date into compare_date;
773
774 if (c_start_date%notfound) then
775 close c_start_date;
776 raise no_data_found;
777 end if;
778
779 if (to_char(x_date, 'YYYY/MM/DD') >= to_char(compare_date, 'YYYY/MM/DD')) or
780 (to_char(x_date, 'YYYY/MM/DD') is null and to_char(compare_date, 'YYYY/MM/DD') is null) then
781
782 x_return_status := 'Y';
783
784 else
785
786 x_return_status := 'N';
787
788 end if;
789
790 close c_start_date;
791
792 else
793
794 open c_end_date;
795 fetch c_end_date into compare_date;
796
797 if (c_end_date%notfound) then
798 close c_end_date;
799 raise no_data_found;
800 end if;
801
802 if (to_char(x_date, 'YYYY/MM/DD') <= to_char(compare_date, 'YYYY/MM/DD')) or
803 (to_char(x_date, 'YYYY/MM/DD') is null and to_char(compare_date, 'YYYY/MM/DD') is null) then
804
805 x_return_status := 'Y';
806
807 else
808
809 x_return_status := 'N';
810
811 end if;
812
813 close c_end_date;
814
815 end if;
816
817 exception
818 when NO_DATA_FOUND then
819 x_return_status := 'N';
820
821 end validate_alloc_source_date;
822
823
824
825 --
826 -- Procedure : multi_customer
827 --
828 -- Purpose : find out how many customers associated with particular project
829 --
830 -- Parameters :
831 -- (in) x_project_id number project id
832 --
833 -- (out) x_count number number of customers
834 -- x_project_number varchar2 project number
835 --
836
837 PROCEDURE multi_customer(x_project_id number ,
838 x_project_number OUT NOCOPY varchar2,
839 x_count OUT NOCOPY number ) is
840
841 cursor c_cust is
842 select count(p.project_id),
843 p.segment1
844 from pa_project_customers c,
845 pa_projects_all p
846 where p.project_id = x_project_id
847 and p.project_id = c.project_id
848 and c.customer_bill_split <> 0
849 group by p.project_id, p.segment1;
850
851 begin
852
853 open c_cust;
854 fetch c_cust into x_count, x_project_number;
855
856 if (c_cust%notfound) then
857 close c_cust;
858 raise no_data_found;
859 end if;
860
861 end multi_customer;
862
863
864
865 --
866 -- Procedure : save_user_profile
867 --
868 -- Purpose : save user profile on the preference of showing funding wizard or not
869 --
870 -- Parameters :
871 -- (in) x_profile_name varchar2 profile name
872 -- x_value varchar2 profile value
873 --
874
875 PROCEDURE save_user_profile(x_profile_name varchar2,
876 x_value varchar2) is
877
878 status boolean;
879
880 begin
881
882 status := fnd_profile.save_user(x_profile_name, x_value);
883 commit;
884
885 end save_user_profile;
886
887
888
889
890 --
891 -- Procedure : validate_start_end_date
892 --
893 -- Purpose : check if start date <= end date
894 --
895 -- Parameters :
896 -- (in) x_start_date date start date
897 -- x_end_date date end date
898 --
899 -- (out) x_return_status varchar2 return status
900 -- Y : valid
901 -- N : not valid
902 --
903
904 PROCEDURE validate_start_end_date(x_start_date date ,
905 x_end_date date ,
906 x_return_status OUT NOCOPY varchar2 ) is
907
908 BEGIN
909
910 if (X_Start_Date is not null) and
911 (X_End_Date is not null) and
912 (X_Start_Date > X_End_Date) then
913
914 X_Return_Status := 'N';
915
916 else
917
918 X_Return_Status := 'Y';
919
920 end if;
921
922 END validate_start_end_date;
923
924
925
926 --
927 -- Procedure : validate_source_alloc_amount
928 --
929 -- Purpose : validate if funding source amount >= sum(funding allocations)
930 --
931 -- Parameters :
932 -- (in) x_source_id number funding source id
933 -- x_amount number amount
934 --
935 -- (out) x_return_status varchar2 return status
936 -- Y : valid
937 -- N : not valid
938 --
939
940 PROCEDURE validate_source_alloc_amount(x_source_id number ,
941 x_amount number ,
942 x_return_status OUT NOCOPY varchar2 ) is
943
944 cursor c_alloc is
945 select nvl(sum(amount), 0)
946 from oke_k_fund_allocations
947 where funding_source_id = x_source_id;
948
949 l_alloc number;
950
951 BEGIN
952
953 OPEN c_alloc;
954 FETCH c_alloc into l_alloc;
955
956 IF (c_alloc%NOTFOUND) THEN
957
958 l_alloc := 0;
959
960 END IF;
961
962 IF (l_alloc < 0) THEN
963
964 x_return_status := 'E';
965
966 ELSIF (x_amount >= l_alloc) THEN
967
968 x_return_status := 'Y';
969
970 ELSE
971
972 x_return_status := 'N';
973
974 END IF;
975
976 END validate_source_alloc_amount;
977
978
979 --
980 -- Procedure : validate_hard_limit
981 --
982 -- Purpose : validate if hard limit <= funding amount
983 --
984 -- Parameters :
985 -- (in) x_fund_amount number funding amount
986 -- x_hard_limit number hard limit
987 --
988 -- (out) x_return_status varchar2 return status
989 -- Y : valid
990 -- N : not valid
991 --
992
993 PROCEDURE validate_hard_limit(x_fund_amount number ,
994 x_hard_limit number ,
995 x_return_status OUT NOCOPY varchar2 ) is
996 BEGIN
997
998 IF (nvl(x_fund_amount, 0) >= nvl(x_hard_limit, 0)) THEN
999
1000 x_return_status := 'Y';
1001
1002 ELSE
1003
1004 x_return_status := 'N';
1005
1006 END IF;
1007
1008 END validate_hard_limit;
1009
1010
1011 --
1012 -- Procedure : validate_source_alloc_limit
1013 --
1014 -- Purpose : check if funding source invoice/revenue hard limit >= sum(funding allocations invoice/revenue hard limit)
1015 -- (for MCB change)
1016 --
1017 -- Parameters :
1018 -- (in) x_source_id number funding source id
1019 -- x_amount number limit amount
1020 -- x_revenue_amount number revenue hard limit amount
1021 --
1022 -- (out) x_type varchar2 hard limit type
1023 -- x_return_status varchar2 return status
1024 -- Y : valid
1025 -- N : invalid
1026 --
1027
1028 PROCEDURE validate_source_alloc_limit(x_source_id number ,
1029 x_amount number ,
1030 x_revenue_amount number ,
1031 x_type OUT NOCOPY varchar2 ,
1032 x_return_status OUT NOCOPY varchar2 ) is
1033
1034 cursor c_alloc is
1035 select nvl(sum(hard_limit), 0), nvl(sum(revenue_hard_limit), 0)
1036 from oke_k_fund_allocations
1037 where funding_source_id = x_source_id;
1038
1039 l_alloc number;
1040 l_revenue_alloc number;
1041
1042 BEGIN
1043
1044 x_type := null;
1045
1046 OPEN c_alloc;
1047 FETCH c_alloc into l_alloc, l_revenue_alloc;
1048
1049 IF (c_alloc%NOTFOUND) THEN
1050
1051 l_alloc := 0;
1052 l_revenue_alloc := 0;
1053
1054 END IF;
1055
1056 IF (nvl(l_alloc, 0) < 0) THEN
1057
1058 x_return_status := 'E';
1059 x_type := 'INVOICE';
1060
1061 ELSIF (nvl(l_revenue_alloc, 0) < 0) THEN
1062
1063 x_return_status := 'E';
1064 x_type := 'REVENUE';
1065
1066 ELSIF (nvl(x_amount, 0) >= l_alloc) THEN
1067
1068 IF (nvl(x_revenue_amount, 0) >= l_revenue_alloc) THEN
1069
1070 x_return_status := 'Y';
1071
1072 ELSE
1073
1074 x_return_status := 'N';
1075 x_type := 'REVENUE';
1076
1077 END IF;
1078
1079 ELSE
1080
1081 x_return_status := 'N';
1082 x_type := 'INVOICE';
1083
1084 END IF;
1085
1086 END validate_source_alloc_limit;
1087
1088
1089
1090 --
1091 -- Procedure : get_conversion_rate
1092 --
1093 -- Purpose : get the conversion rate for the particular conversion type and date
1094 --
1095 -- Parameters :
1096 -- (in) x_from_currency varchar2 conversion from currency
1097 -- x_to_currency varchar2 conversion to currency
1098 -- x_conversion_type varchar2 conversion type
1099 -- x_conversion_date date conversion date
1100 --
1101 -- (out) x_conversion_date number conversion rate
1102 -- x_return_status varchar2 return status
1103 -- Y : exist
1104 -- N : not exist
1105 --
1106
1107 PROCEDURE get_conversion_rate(x_from_currency varchar2 ,
1108 x_to_currency varchar2 ,
1109 x_conversion_type varchar2 ,
1110 x_conversion_date date ,
1111 x_conversion_rate OUT NOCOPY number ,
1112 x_return_status OUT NOCOPY varchar2
1113 ) is
1114
1115 numerator number;
1116 denominator number;
1117 amount number := 0;
1118 converted_amount number := 0;
1119
1120 BEGIN
1121
1122 GL_CURRENCY_API.CONVERT_AMOUNT(x_from_currency => x_from_currency ,
1123 x_to_currency => x_to_currency ,
1124 x_conversion_date => x_conversion_date ,
1125 x_conversion_type => x_conversion_type ,
1126 x_amount => amount ,
1127 x_converted_amount => converted_amount ,
1128 x_denominator => denominator ,
1129 x_numerator => numerator ,
1130 x_rate => x_conversion_rate
1131 );
1132 x_return_status := 'Y';
1133
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 x_return_status := 'N';
1137
1138 END get_conversion_rate;
1139
1140
1141
1142 --
1143 -- PROCEDURE : check_agreement_exist
1144 --
1145 -- Purpose : check if agreement exist for the funding source
1146 --
1147 -- Parameters :
1148 -- (in) x_funding_source_id number funding_source_id
1149 --
1150 -- (out) x_return_status varchar2 return status
1151 -- Y : exist
1152 -- N : not exist
1153 --
1154
1155 PROCEDURE check_agreement_exist(x_funding_source_id number ,
1156 x_return_status OUT NOCOPY varchar2 ) is
1157
1158 cursor c_exist is
1159 select nvl(agreement_flag, 'N')
1160 from oke_k_funding_sources
1161 where funding_source_id = x_funding_source_id;
1162
1163 begin
1164
1165 open c_exist;
1166 fetch c_exist into x_return_status;
1167
1168 if (c_exist%NOTFOUND) then
1169 x_return_status := 'N';
1170 end if;
1171
1172 close c_exist;
1173
1174 end check_agreement_exist;
1175
1176
1177
1178 --
1179 -- Function : get_project_currency
1180 --
1181 -- Purpose : get the project currency
1182 --
1183 -- Parameters :
1184 -- (in) x_project_id number project_id
1185 --
1186
1187 FUNCTION get_project_currency(x_project_id number) return varchar2
1188 is
1189 cursor c_currency is
1190 select currency_code
1191 from pa_projects_all p,
1192 pa_implementations_all i,
1193 gl_sets_of_books g
1194 where p.project_id = x_project_id
1195 and nvl(p.org_id, -99) = nvl(i.org_id, -99)
1196 and i.set_of_books_id = g.set_of_books_id;
1197
1198 l_currency varchar2(15);
1199
1200 BEGIN
1201
1202 open c_currency;
1203 fetch c_currency into l_currency;
1204
1205 if (c_currency%NOTFOUND) then
1206 close c_currency;
1207 return(null);
1208 end if;
1209 close c_currency;
1210 return(l_currency);
1211
1212 END get_project_currency;
1213
1214
1215
1216 --
1217 -- Function : get_owned_by
1218 --
1219 -- Purpose : get the owned_by_person_id
1220 --
1221 -- Parameters :
1222 -- (in) x_user_id number user id
1223 --
1224
1225 FUNCTION get_owned_by(x_user_id number) return number is
1226
1227 cursor c_owned is
1228 select employee_id
1229 from fnd_user
1230 where user_id = x_user_id;
1231
1232 l_emp_id number;
1233
1234 BEGIN
1235
1236 OPEN c_owned;
1237 FETCH c_owned into l_emp_id;
1238 IF (c_owned%NOTFOUND) THEN
1239
1240 l_emp_id := null;
1241
1242 END IF;
1243
1244 CLOSE c_owned;
1245 return(l_emp_id);
1246
1247 END get_owned_by;
1248
1249
1250 --
1251 -- PROCEDURE : get_agreement_info
1252 --
1253 -- Purpose : get existing agreement_type, customer_id for the existing funding_source_id
1254 --
1255 -- Parameters :
1256 -- (in) x_funding_source_id number funding_source_id
1257 --
1258 -- (out) x_agreement_type varchar2 agreement_type
1259 -- x_customer_id number customer_id
1260 -- x_return_status varchar2 return status
1261 -- Y : exist
1262 -- N : not exist
1263 --
1264
1265 PROCEDURE get_agreement_info(x_funding_source_id number ,
1266 x_agreement_type OUT NOCOPY varchar2 ,
1267 x_customer_id OUT NOCOPY number ,
1268 x_return_status OUT NOCOPY varchar2
1269 ) is
1270
1271 CURSOR c_agreement IS
1272 SELECT agreement_type, customer_id
1273 FROM pa_agreements_all
1274 WHERE pm_product_code = OKE_FUNDING_PUB.G_PRODUCT_CODE
1275 AND pm_agreement_reference LIKE '%-' || to_char(x_funding_source_id);
1276
1277 begin
1278
1279 OPEN c_agreement;
1280 FETCH c_agreement into x_agreement_type, x_customer_id;
1281
1282 if (c_agreement%NOTFOUND) then
1283 close c_agreement;
1284 x_return_status := 'N';
1285 raise no_data_found;
1286 end if;
1287
1288 close c_agreement;
1289 x_return_status := 'Y';
1290
1291 end get_agreement_info;
1292
1293
1294 --
1295 -- Procedure : update_alloc_version
1296 --
1297 -- Description : This procedure is used to update agreement_version and insert_update_flag of OKE_K_FUND_ALLOCATIONS table
1298 --
1299 -- Parameters :
1300 -- (in) x_fund_allocation_id number fund_allocation_id
1301 -- x_version_add number version increment
1302 -- x_commit varchar2 commit flag
1303 --
1304
1305 PROCEDURE update_alloc_version(x_fund_allocation_id IN NUMBER,
1306 x_version_add IN NUMBER,
1307 x_commit IN VARCHAR2 := OKE_API.G_FALSE
1308 ) is
1309
1310 begin
1311
1312 update oke_k_fund_allocations
1313 set agreement_version = nvl(agreement_version, 0) + x_version_add,
1314 insert_update_flag = null
1315 where fund_allocation_id = x_fund_allocation_id;
1316
1317 if FND_API.to_boolean(x_commit) then
1318
1319 commit work;
1320
1321 end if;
1322
1323 end update_alloc_version;
1324
1325
1326
1327 --
1328 -- Procedure : update_source_flag
1329 --
1330 -- Description : This procedure is used to update agreement_flag of OKE_K_FUNDING_SOURCES table
1331 --
1332 -- Parameters :
1333 -- (in) x_funding_source_id number funding_source_id
1334 -- x_commit varchar2 commit flag
1335 --
1336
1337 PROCEDURE update_source_flag(x_funding_source_id IN NUMBER,
1338 x_commit IN VARCHAR2 := OKE_API.G_FALSE
1339 ) is
1340 l_flag VARCHAR2(1) := 'N';
1341 CURSOR c_agr IS
1342 SELECT 'Y'
1343 FROM pa_agreements_all
1344 WHERE pm_product_code = OKE_FUNDING_PUB.G_PRODUCT_CODE
1345 AND pm_agreement_reference LIKE '%-' || to_char(x_funding_source_id);
1346
1347 BEGIN
1348 OPEN c_agr;
1349 FETCH c_agr INTO l_flag;
1350 CLOSE c_agr;
1351
1352 UPDATE oke_k_funding_sources
1353 SET agreement_flag = l_flag
1354 WHERE funding_source_id = x_funding_source_id;
1355
1356 IF FND_API.to_boolean(x_commit) THEN
1357
1358 COMMIT WORK;
1359
1360 END IF;
1361
1362 END update_source_flag;
1363
1364
1365
1366 --
1367 -- Procedure : funding_mode
1368 --
1369 -- Description : This procedure is used to check the funding mode is vaild or not
1370 --
1371 -- Parameters :
1372 -- (in) x_proj_sum_tbl proj_sum_tbl_type allocation amount by project
1373 -- x_task_sum_tbl task_sum_tbl_type allocation amount by task
1374 --
1375 -- (out) x_funding_level_tbl funding_level_tbl_type funding level by project
1376 -- x_return_status varchar2 return_status
1377 -- S: successful
1378 -- E: error
1379 -- x_project_err varchar2 project number with funding mode error
1380 --
1381
1382 PROCEDURE funding_mode(x_proj_sum_tbl IN PROJ_SUM_TBL_TYPE,
1383 x_task_sum_tbl IN TASK_SUM_TBL_TYPE,
1384 x_funding_level_tbl OUT NOCOPY FUNDING_LEVEL_TBL_TYPE,
1385 x_return_status OUT NOCOPY VARCHAR2,
1386 x_project_err OUT NOCOPY VARCHAR2
1387 ) is
1388
1389 i number;
1390 j number;
1391 exist_flag varchar2(1);
1392
1393 begin
1394
1395 x_return_status := 'S';
1396
1397 if (x_proj_sum_tbl.COUNT > 0) then
1398
1399 i := x_proj_sum_tbl.FIRST;
1400
1401 loop
1402
1403 if (x_proj_sum_tbl(i).amount <> 0) then
1404
1405 if (x_task_sum_tbl.COUNT > 0) then
1406
1407 j := x_task_sum_tbl.FIRST;
1408
1409 loop
1410
1411 if (x_task_sum_tbl(j).project_id = i) and
1412 (x_task_sum_tbl(j).amount <> 0) then
1413
1414 x_return_status := 'E';
1415 x_project_err := x_task_sum_tbl(j).project_number;
1416 exit;
1417
1418 end if;
1419
1420 exit when (j = x_task_sum_tbl.LAST);
1421 j := x_task_sum_tbl.NEXT(j);
1422
1423 end loop;
1424
1425 end if;
1426
1427 if (x_return_status <> 'E') then
1428
1429 x_funding_level_tbl(i).project_id := x_proj_sum_tbl(i).project_id;
1430 x_funding_level_tbl(i).funding_level := 'P';
1431
1432 end if;
1433
1434 elsif (x_proj_sum_tbl(i).amount = 0) then
1435
1436 x_funding_level_tbl(i).funding_level := 'T';
1437 x_funding_level_tbl(i).project_id := x_proj_sum_tbl(i).project_id;
1438
1439 end if;
1440
1441 exit when (i = x_proj_sum_tbl.LAST);
1442 i := x_proj_sum_tbl.NEXT(i);
1443
1444 end loop;
1445
1446 end if;
1447
1448 if (x_task_sum_tbl.COUNT > 0) THEN
1449
1450 i := x_task_sum_tbl.FIRST;
1451
1452 loop
1453
1454 if (x_funding_level_tbl.COUNT > 0) then
1455
1456 j := x_funding_level_tbl.FIRST;
1457 exist_flag := 'N';
1458
1459 loop
1460
1461 -- bug 3006791, start
1462 --if (x_funding_level_tbl(j).project_id = i) then
1463 if (x_funding_level_tbl(j).project_id = x_task_sum_tbl(i).project_id) then
1464 -- bug 3006791, end
1465
1466 exist_flag := 'Y';
1467
1468 end if;
1469 exit when (j = x_funding_level_tbl.LAST or exist_flag = 'Y');
1470 j := x_funding_level_tbl.NEXT(j);
1471
1472 end loop;
1473
1474 if (exist_flag <> 'Y') then
1475
1476 x_funding_level_tbl(x_task_sum_tbl(i).project_id).funding_level := 'T';
1477 x_funding_level_tbl(x_task_sum_tbl(i).project_id).project_id := x_task_sum_tbl(i).project_id;
1478
1479 end if;
1480
1481 else
1482
1483 x_funding_level_tbl(x_task_sum_tbl(i).project_id).funding_level := 'T';
1484 x_funding_level_tbl(x_task_sum_tbl(i).project_id).project_id := x_task_sum_tbl(i).project_id;
1485
1486 end if;
1487
1488 exit when (i = x_task_sum_tbl.LAST);
1489 i := x_task_sum_tbl.NEXT(i);
1490
1491 end loop;
1492
1493 end if;
1494
1495 end funding_mode;
1496
1497
1498
1499 --
1500 -- Procedure : get_converted_amount
1501 --
1502 -- Description : This function is used to calculate the allocated amount
1503 --
1504 -- Parameters :
1505 -- (in) x_funding_source_id number funding_source_id
1506 -- x_project_id number project_id
1507 -- x_project_number varchar2 project number
1508 -- x_amount number original amount
1509 -- x_conversion_type varchar2 currency conversion type
1510 -- x_conversion_date date currency conversion date
1511 -- x_conversion_rate number currency conversion rate
1512 --
1513 -- (out) x_converted_amount number converted amount
1514 -- x_return_status varchar2 return status
1515 -- S: successful
1516 -- E: error
1517 -- U: unexpected error
1518 --
1519
1520 PROCEDURE get_converted_amount(x_funding_source_id IN NUMBER ,
1521 x_project_id IN NUMBER ,
1522 x_project_number IN VARCHAR2 ,
1523 x_amount IN NUMBER ,
1524 -- x_org_id IN NUMBER ,
1525 x_conversion_type IN VARCHAR2 ,
1526 x_conversion_date IN DATE ,
1527 x_conversion_rate IN NUMBER ,
1528 x_converted_amount OUT NOCOPY NUMBER ,
1529 x_return_status OUT NOCOPY VARCHAR2
1530 ) is
1531
1532 cursor c_currency is
1533 select currency_code
1534 from oke_k_funding_sources
1535 where funding_source_id = x_funding_source_id;
1536
1537 cursor c_min_unit is
1538 select nvl(minimum_accountable_unit, power(10, -1 * precision)),
1539 p.projfunc_currency_code
1540 from pa_projects_all p,
1541 fnd_currencies f
1542 where project_id = x_project_id
1543 and f.currency_code = p.projfunc_currency_code;
1544
1545 l_currency VARCHAR2(15);
1546 l_min_unit NUMBER;
1547 l_project_currency VARCHAR2(15);
1548
1549 begin
1550
1551 x_return_status := 'S';
1552
1553 OPEN c_currency;
1554 FETCH c_currency into l_currency;
1555 CLOSE c_currency;
1556
1557 OPEN c_min_unit;
1558 FETCH c_min_unit into l_min_unit, l_project_currency;
1559 CLOSE c_min_unit;
1560
1561 get_calculate_amount(x_conversion_type => x_conversion_type ,
1562 x_conversion_date => x_conversion_date ,
1563 x_conversion_rate => x_conversion_rate ,
1564 x_org_amount => x_amount ,
1565 x_min_unit => l_min_unit ,
1566 x_fund_currency => l_currency ,
1567 x_project_currency => l_project_currency ,
1568 x_amount => x_converted_amount ,
1569 x_return_status => x_return_status
1570 );
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 OKE_API.set_message(p_app_name => G_APP_NAME ,
1575 p_msg_name => G_UNEXPECTED_ERROR ,
1576 p_token1 => G_SQLCODE_TOKEN ,
1577 p_token1_value => SQLCODE ,
1578 p_token2 => G_SQLERRM_TOKEN ,
1579 p_token2_value => SQLERRM
1580 );
1581
1582 IF c_min_unit%ISOPEN THEN
1583 CLOSE c_min_unit;
1584 END IF;
1585
1586 IF c_currency%ISOPEN THEN
1587 CLOSE c_currency;
1588 END IF;
1589
1590 x_return_status := 'U';
1591
1592 end get_converted_amount;
1593
1594
1595
1596 --
1597 -- Procedure : get_calculate_amount
1598 --
1599 -- Description : This procedure is used to get the converted amount
1600 --
1601 -- Parameters :
1602 -- (in) x_conversion_type varchar2 currency conversion type
1603 -- x_conversion_date date currency conversion date
1604 -- x_conversion_rate number currency conversion rate
1605 -- x_org_amount number original amount
1606 -- x_min_unit number minimum amount unit of the currency
1607 -- x_fund_currency varchar2 funding source currency
1608 -- x_project_currency varchar2 project currency
1609 --
1610 -- (out) x_amount number converted amount
1611 -- x_return_status varchar2 return status
1612 -- S: successful
1613 -- E: error
1614 -- U: unexpected error
1615 --
1616
1617 PROCEDURE get_calculate_amount(x_conversion_type VARCHAR2 ,
1618 x_conversion_date DATE ,
1619 x_conversion_rate NUMBER ,
1620 x_org_amount NUMBER ,
1621 x_min_unit NUMBER ,
1622 x_fund_currency VARCHAR2 ,
1623 x_project_currency VARCHAR2 ,
1624 x_amount OUT NOCOPY NUMBER ,
1625 x_return_status OUT NOCOPY VARCHAR2
1626 ) is
1627
1628 MISSING_ERROR EXCEPTION;
1629
1630 begin
1631
1632 x_return_status := 'S';
1633
1634 IF (x_project_currency <> x_fund_currency) THEN
1635
1636 IF (x_conversion_type is null) THEN
1637
1638 OKE_API.set_message(p_app_name => G_APP_NAME ,
1639 p_msg_name => 'OKE_API_MISSING_VALUE' ,
1640 p_token1 => 'VALUE' ,
1641 p_token1_value => 'pa_conversion_type'
1642 );
1643
1644 RAISE MISSING_ERROR;
1645
1646 END IF;
1647
1648 IF (x_conversion_date is null) THEN
1649
1650 OKE_API.set_message(p_app_name => G_APP_NAME ,
1651 p_msg_name => 'OKE_API_MISSING_VALUE' ,
1652 p_token1 => 'VALUE' ,
1653 p_token1_value => 'pa_conversion_date'
1654 );
1655
1656 RAISE MISSING_ERROR;
1657
1658 END IF;
1659
1660 IF (upper(x_conversion_type) = 'USER') AND
1661 (x_conversion_rate is null) THEN
1662
1663 OKE_API.set_message(p_app_name => G_APP_NAME ,
1664 p_msg_name => 'OKE_API_MISSING_VALUE' ,
1665 p_token1 => 'VALUE' ,
1666 p_token1_value => 'pa_conversion_rate'
1667 );
1668
1669 RAISE MISSING_ERROR;
1670
1671 END IF;
1672
1673 END IF;
1674
1675 IF (x_conversion_rate is null) THEN
1676
1677 x_amount := x_org_amount;
1678
1679 ELSE
1680
1681 x_amount := round(x_org_amount * x_conversion_rate / x_min_unit) * x_min_unit;
1682
1683 END IF;
1684
1685 EXCEPTION
1686 WHEN MISSING_ERROR THEN
1687 x_return_status := 'E';
1688
1689 WHEN OTHERS THEN
1690 OKE_API.set_message(p_app_name => G_APP_NAME ,
1691 p_msg_name => G_UNEXPECTED_ERROR ,
1692 p_token1 => G_SQLCODE_TOKEN ,
1693 p_token1_value => SQLCODE ,
1694 p_token2 => G_SQLERRM_TOKEN ,
1695 p_token2_value => SQLERRM
1696 );
1697
1698 x_return_status := 'U';
1699
1700 end get_calculate_amount;
1701
1702 end OKE_FUNDING_UTIL_PKG;