[Home] [Help]
PACKAGE BODY: APPS.OKE_FUNDSOURCE_PVT
Source
1 package body OKE_FUNDSOURCE_PVT as
2 /* $Header: OKEVKFDB.pls 120.2.12020000.6 2012/12/12 04:58:11 skuchima ship $ */
3
4 --
5 -- Local Variables
6 --
7
8 L_USERID NUMBER := FND_GLOBAL.USER_ID;
9 L_LOGINID NUMBER := FND_GLOBAL.LOGIN_ID;
10
11
12
13 --
14 -- Private Procedures and Functions
15 --
16
17
18 --
19 -- Procedure: validate_agreement_org
20 --
21 -- Description: This procedure is used to validate agreement_org_id
22 --
23 --
24
25 PROCEDURE validate_agreement_org(p_agreement_org_id NUMBER ,
26 --p_k_header_id NUMBER ,
27 p_return_status OUT NOCOPY VARCHAR2
28 ) is
29
30 cursor c_agreement_org_id is
31 select 'x'
32 from pa_organizations_project_v p
33 where organization_id = p_agreement_org_id
34 and sysdate between decode(date_from, null, sysdate, date_from)
35 and decode(date_to, null, sysdate, date_to);
36
37 cursor c_agreement_org_id2 is
38 select 'x'
39 from hr_all_organization_units
40 where organization_id = p_agreement_org_id
41 and sysdate between decode(date_from, null, sysdate, date_from)
42 and decode(date_to, null, sysdate, date_to);
43
44 l_dummy_value VARCHAR2(1) := '?';
45 l_install VARCHAR2(1);
46
47 BEGIN
48
49 p_return_status := OKE_API.G_RET_STS_SUCCESS;
50
51 IF (p_agreement_org_id is not null) OR
52 (p_agreement_org_id <> OKE_API.G_MISS_NUM) THEN
53
54 l_install := fnd_profile.value('PA_BILLING_LICENSED');
55
56 if (nvl(l_install, 'N') = 'Y') then
57
58 OPEN c_agreement_org_id;
59 FETCH c_agreement_org_id into l_dummy_value;
60 CLOSE c_agreement_org_id;
61
62 else
63
64 OPEN c_agreement_org_id2;
65 FETCH c_agreement_org_id2 into l_dummy_value;
66 CLOSE c_agreement_org_id2;
67
68 end if;
69
70 IF (l_dummy_value = '?') THEN
71
72 OKE_API.set_message(p_app_name => G_APP_NAME ,
73 p_msg_name => 'OKE_API_INVALID_VALUE' ,
74 p_token1 => 'VALUE' ,
75 p_token1_value => 'agreement_org_id'
76 );
77
78 p_return_status := OKE_API.G_RET_STS_ERROR;
79
80 END IF;
81
82 END IF;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
87 OKE_API.set_message(p_app_name => G_APP_NAME ,
88 p_msg_name => G_UNEXPECTED_ERROR ,
89 p_token1 => G_SQLCODE_TOKEN ,
90 p_token1_value => SQLCODE ,
91 p_token2 => G_SQLERRM_TOKEN ,
92 p_token2_value => SQLERRM
93 );
94
95 IF c_agreement_org_id%ISOPEN THEN
96 CLOSE c_agreement_org_id;
97 END IF;
98
99 END validate_agreement_org;
100
101 --
102 -- Function: get_funding_source_id
103 --
104 -- Description: This function is used to get the funding source id for new record
105 --
106
107 FUNCTION get_funding_source_id RETURN NUMBER is
108
109 l_funding_source_id NUMBER;
110
111 BEGIN
112
113 select oke_k_funding_sources_s.nextval
114 into l_funding_source_id
115 from dual;
116
117 return(l_funding_source_id);
118
119 END get_funding_source_id;
120
121
122 --
123 -- Function: get_min_unit
124 --
125 -- Description: This function is used to get the minimium currency unit
126 --
127
128 FUNCTION get_min_unit(p_currency VARCHAR2) RETURN NUMBER is
129
130 l_min_unit NUMBER;
131
132 cursor c_currency is
133 select nvl(minimum_accountable_unit, power(10, -1 * precision))
134 from fnd_currencies
135 where currency_code = p_currency;
136
137 BEGIN
138
139 OPEN c_currency;
140 FETCH c_currency into l_min_unit;
141
142 IF (c_currency%NOTFOUND) THEN
143 CLOSE c_currency;
144 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
145 END IF;
146
147 CLOSE c_currency;
148 return(l_min_unit);
149
150 EXCEPTION
151 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
152 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
153
154 WHEN OTHERS THEN
155 OKE_API.set_message(p_app_name => G_APP_NAME ,
156 p_msg_name => G_UNEXPECTED_ERROR ,
157 p_token1 => G_SQLCODE_TOKEN ,
158 p_token1_value => SQLCODE ,
159 p_token2 => G_SQLERRM_TOKEN ,
160 p_token2_value => SQLERRM
161 );
162
163 IF c_currency%ISOPEN THEN
164 CLOSE c_currency;
165 END IF;
166 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
167
168 END get_min_unit;
169
170
171 --
172 -- Function: get_k_currency
173 --
174 -- Description: This function is used to get contract currency
175 --
176 --
177
178 FUNCTION get_k_currency(p_header_id NUMBER) RETURN VARCHAR2 is
179
180 l_currency_code VARCHAR2(15);
181
182 cursor c_currency is
183 select currency_code
184 from okc_k_headers_b
185 where id = p_header_id;
186
187 BEGIN
188
189 OPEN c_currency;
190 FETCH c_currency into l_currency_code;
191
192 IF (c_currency%NOTFOUND) THEN
193
194 CLOSE c_currency;
195 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
196
197 END IF;
198
199 return(l_currency_code);
200 CLOSE c_currency;
201
202 EXCEPTION
203 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
204 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
205
206 WHEN OTHERS THEN
207 OKE_API.set_message(p_app_name => G_APP_NAME ,
208 p_msg_name => G_UNEXPECTED_ERROR ,
209 p_token1 => G_SQLCODE_TOKEN ,
210 p_token1_value => SQLCODE ,
211 p_token2 => G_SQLERRM_TOKEN ,
212 p_token2_value => SQLERRM
213 );
214
215 IF c_currency%ISOPEN THEN
216 CLOSE c_currency;
217 END IF;
218 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
219
220 END get_k_currency;
221
222
223 --
224 -- Procedure: update_agreement_reference
225 --
226 -- Description: This procedure is used to update pa's agreement reference and product code columns
227 --
228 --
229
230 PROCEDURE update_agreement_reference(p_agreement_id NUMBER ,
231 p_org_id NUMBER ,
232 p_currency_code VARCHAR2 ,
233 p_funding_source_id NUMBER ,
234 p_num_update_flag VARCHAR2
235 ) is
236 --Added for bugfix 15926104
237
238 cursor c_length(b_owner varchar2) is
239 select col.data_length
240 from user_synonyms syn,
241 all_tab_columns col
242 where syn.synonym_name = 'PA_AGREEMENTS_ALL'
243 and col.owner = b_owner
244 and col.table_name = syn.table_name
245 AND col.column_name = 'AGREEMENT_NUM';
246
247 --end Added for bugfix 15926104
248
249 /* commnented out for bugfix 15926104
250 cursor c_length(b_owner varchar2) is
251 select data_length
252 from all_tab_columns
253 where table_name = 'PA_AGREEMENTS_ALL'
254 and owner = b_owner
255 and column_name = 'AGREEMENT_NUM';*/
256
257 l_length NUMBER := 0;
258 l_return_status boolean;
259 l_status varchar2(10);
260 l_industry varchar2(200);
261 l_table_owner varchar2(10);
262
263 BEGIN
264 l_return_status := FND_INSTALLATION.GET_APP_INFO(
265 application_short_name => 'PA',
266 status => l_status,
267 industry => l_industry,
268 oracle_schema => l_table_owner);
269
270 open c_length(l_table_owner);
271 fetch c_length into l_length;
272 close c_length;
273
274 update pa_agreements_all
275 set pm_product_code = G_PRODUCT_CODE,
276 -- agreement_num = decode(p_num_update_flag, 'Y', substr(agreement_num, 0, 20-1-length(p_currency_code)) || '-' || p_currency_code,
277 -- 'N', agreement_num, agreement_num),
278 agreement_num = decode(p_num_update_flag, 'Y', substr(agreement_num, 0, l_length-1-length(p_currency_code)) || '-' || p_currency_code,
279 'N', agreement_num, agreement_num),
280 --agreement_num = substr(agreement_num, 0, 20-1-length(p_currency_code)) || '-' || p_currency_code,
281 pm_agreement_reference = p_org_id || '-' || p_currency_code || '-' || p_funding_source_id
282 -- pm_agreement_reference = p_org_id || '-Y-' || p_funding_source_id
283 where agreement_id = p_agreement_id;
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 OKE_API.set_message(p_app_name => G_APP_NAME ,
288 p_msg_name => G_UNEXPECTED_ERROR ,
289 p_token1 => G_SQLCODE_TOKEN ,
290 p_token1_value => SQLCODE ,
291 p_token2 => G_SQLERRM_TOKEN ,
292 p_token2_value => SQLERRM
293 );
294
295 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
296
297 END update_agreement_reference;
298
299
300 --
301 -- Procedure: update_proj_fld_reference
302 --
303 -- Description: This procedure is used to update pa's agreement reference and product code columns
304 --
305 --
306
307 PROCEDURE update_proj_fld_reference(p_project_funding_id NUMBER ,
308 p_fund_allocation_id NUMBER
309 ) is
310
311 BEGIN
312
313 update pa_project_fundings
314 set pm_product_code = G_PRODUCT_CODE,
315 pm_funding_reference = p_fund_allocation_id || '.1'
316 where project_funding_id = p_project_funding_id;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 OKE_API.set_message(p_app_name => G_APP_NAME ,
321 p_msg_name => G_UNEXPECTED_ERROR ,
322 p_token1 => G_SQLCODE_TOKEN ,
323 p_token1_value => SQLCODE ,
324 p_token2 => G_SQLERRM_TOKEN ,
325 p_token2_value => SQLERRM
326 );
327
328 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
329
330 END update_proj_fld_reference;
331
332
333 --
334 -- Procedure: get_conversion
335 --
336 -- Description: This procedure is used to get the conversion rate
337 --
338 --
339
340 PROCEDURE get_conversion(p_from_currency VARCHAR2 ,
341 p_to_currency VARCHAR2 ,
342 p_conversion_type VARCHAR2 ,
343 p_conversion_date DATE ,
344 p_conversion_rate IN OUT NOCOPY NUMBER ,
345 p_return_status OUT NOCOPY VARCHAR2
346 ) is
347
348 l_return_status VARCHAR2(1);
349
350 BEGIN
351
352 p_return_status := OKE_API.G_RET_STS_SUCCESS;
353
354 IF (p_from_currency <> p_to_currency) THEN
355
356 IF (p_conversion_type is null) THEN
357
358 OKE_API.set_message(p_app_name => G_APP_NAME ,
359 p_msg_name => 'OKE_API_MISSING_VALUE' ,
360 p_token1 => 'VALUE' ,
361 p_token1_value => 'conversion_type'
362 );
363
364 p_return_status := OKE_API.G_RET_STS_ERROR;
365
366
367 ELSIF (p_conversion_date is null) THEN
368
369 OKE_API.set_message(p_app_name => G_APP_NAME ,
370 p_msg_name => 'OKE_API_MISSING_VALUE' ,
371 p_token1 => 'VALUE' ,
372 p_token1_value => 'conversion_date'
373 );
374
375 p_return_status := OKE_API.G_RET_STS_ERROR;
376
377 ELSE
378
379 -- syho, bug 2208979
380 IF (upper(p_conversion_type) <> 'USER') THEN
381
382 IF (p_conversion_rate is null) THEN
383
384 OKE_FUNDING_UTIL_PKG.get_conversion_rate(x_from_currency => p_from_currency ,
385 x_to_currency => p_to_currency ,
386 x_conversion_type => p_conversion_type ,
387 x_conversion_date => p_conversion_date ,
388 x_conversion_rate => p_conversion_rate ,
389 x_return_status => l_return_status
390 );
391
392 IF (l_return_status = 'N') THEN
393
394 OKE_API.set_message(p_app_name => G_APP_NAME ,
395 p_msg_name => 'OKE_FUND_NO_RATE'
396 );
397
398 p_return_status := OKE_API.G_RET_STS_ERROR;
399
400 END IF;
401
402 ELSE
403
404 OKE_API.set_message(p_app_name => G_APP_NAME ,
405 p_msg_name => 'OKE_API_INVALID_VALUE' ,
406 p_token1 => 'VALUE' ,
407 p_token1_value => 'conversion_rate'
408 );
409
410 p_return_status := OKE_API.G_RET_STS_ERROR;
411
412 END IF;
413
414 ELSIF (p_conversion_rate is null) THEN
415
416 OKE_API.set_message(p_app_name => G_APP_NAME ,
417 p_msg_name => 'OKE_API_INVALID_VALUE' ,
418 p_token1 => 'VALUE' ,
419 p_token1_value => 'conversion_rate'
420 );
421
422 p_return_status := OKE_API.G_RET_STS_ERROR;
423
424 END IF;
425
426 END IF;
427
428 ELSIF (p_conversion_type is not null) THEN
429
430 OKE_API.set_message(p_app_name => G_APP_NAME ,
431 p_msg_name => 'OKE_API_INVALID_VALUE' ,
432 p_token1 => 'VALUE' ,
433 p_token1_value => 'conversion_type'
434 );
435
436 p_return_status := OKE_API.G_RET_STS_ERROR;
437
438 ELSIF (p_conversion_date is not null) THEN
439
440 OKE_API.set_message(p_app_name => G_APP_NAME ,
441 p_msg_name => 'OKE_API_INVALID_VALUE' ,
442 p_token1 => 'VALUE' ,
443 p_token1_value => 'conversion_date'
444 );
445
446 p_return_status := OKE_API.G_RET_STS_ERROR;
447
448 ELSIF (p_conversion_rate is not null) THEN
449
450 OKE_API.set_message(p_app_name => G_APP_NAME ,
451 p_msg_name => 'OKE_API_INVALID_VALUE' ,
452 p_token1 => 'VALUE' ,
453 p_token1_value => 'conversion_rate'
454 );
455
456 p_return_status := OKE_API.G_RET_STS_ERROR;
457
458 END IF;
459
460 END get_conversion;
461
462
463 --
464 -- Procedure: validate_object_type
465 --
466 -- Description: This procedure is used to validate object_type
467 --
468 --
469
470 PROCEDURE validate_object_type(p_object_type VARCHAR2 ,
471 p_return_status OUT NOCOPY VARCHAR2
472 ) is
473 BEGIN
474
475 p_return_status := OKE_API.G_RET_STS_SUCCESS;
476
477 IF (p_object_type is null) OR
478 (p_object_type = OKE_API.G_MISS_CHAR) THEN
479
480 OKE_API.set_message(p_app_name => G_APP_NAME ,
481 p_msg_name => 'OKE_API_MISSING_VALUE' ,
482 p_token1 => 'VALUE' ,
483 p_token1_value => 'object_type'
484 );
485
486 p_return_status := OKE_API.G_RET_STS_ERROR;
487
488 ELSE
489
490 IF (upper(p_object_type) <> G_OBJECT_TYPE) THEN
491
492 OKE_API.set_message(p_app_name => G_APP_NAME ,
493 p_msg_name => 'OKE_API_INVALID_VALUE' ,
494 p_token1 => 'VALUE' ,
495 p_token1_value => 'object_type'
496 );
497
498 p_return_status := OKE_API.G_RET_STS_ERROR;
499
500 END IF;
501
502 END IF;
503
504 END validate_object_type;
505
506
507 --
508 -- Procedure: validate_amount
509 --
510 -- Description: This procedure is used to validate amount
511 --
512 --
513
514 PROCEDURE validate_amount(p_amount NUMBER ,
515 p_return_status OUT NOCOPY VARCHAR2
516 ) is
517
518 BEGIN
519
520 p_return_status := OKE_API.G_RET_STS_SUCCESS;
521
522 IF (p_amount is null) OR
523 (p_amount = OKE_API.G_MISS_NUM) THEN
524
525 OKE_API.set_message(p_app_name => G_APP_NAME ,
526 p_msg_name => 'OKE_API_MISSING_VALUE' ,
527 p_token1 => 'VALUE' ,
528 p_token1_value => 'amount'
529 );
530
531 p_return_status := OKE_API.G_RET_STS_ERROR;
532
533 ELSIF (p_amount < 0) THEN
534
535 OKE_API.set_message(p_app_name => G_APP_NAME ,
536 p_msg_name => 'OKE_FUND_NEGATIVE_AMT'
537 );
538
539 p_return_status := OKE_API.G_RET_STS_ERROR;
540
541 END IF;
542
543 END validate_amount;
544
545
546 --
547 -- Procedure: validate_funding_source_id
548 --
549 -- Description: This procedure is used to validate the funding source id
550 --
551 --
552
553 PROCEDURE validate_funding_source_id(p_funding_source_id NUMBER ,
554 p_rowid OUT NOCOPY VARCHAR2,
555 p_pool_party_id OUT NOCOPY NUMBER ,
556 p_agreement_flag OUT NOCOPY VARCHAR2
557 ) is
558 cursor c_funding_source_id is
559 select rowid, pool_party_id, agreement_flag
560 from oke_k_funding_sources
561 where funding_source_id = p_funding_source_id;
562
563 BEGIN
564
565 IF (p_funding_source_id is null) OR
566 (p_funding_source_id = OKE_API.G_MISS_NUM) THEN
567
568 OKE_API.set_message(p_app_name => G_APP_NAME ,
569 p_msg_name => 'OKE_API_MISSING_VALUE' ,
570 p_token1 => 'VALUE' ,
571 p_token1_value => 'funding_source_id'
572 );
573
574 RAISE G_EXCEPTION_HALT_VALIDATION;
575
576 END IF;
577
578 OPEN c_funding_source_id;
579 FETCH c_funding_source_id into p_rowid, p_pool_party_id, p_agreement_flag;
580
581 IF (c_funding_source_id%NOTFOUND) THEN
582
583 OKE_API.set_message(p_app_name => G_APP_NAME ,
584 p_msg_name => 'OKE_API_INVALID_VALUE' ,
585 p_token1 => 'VALUE' ,
586 p_token1_value => 'funding_source_id'
587 );
588
589 CLOSE c_funding_source_id;
590 RAISE G_EXCEPTION_HALT_VALIDATION;
591
592 END IF;
593
594 CLOSE c_funding_source_id;
595
596 EXCEPTION
597 WHEN G_EXCEPTION_HALT_VALIDATION THEN
598 raise G_EXCEPTION_HALT_VALIDATION;
599
600 WHEN OTHERS THEN
601 OKE_API.set_message(p_app_name => G_APP_NAME ,
602 p_msg_name => G_UNEXPECTED_ERROR ,
603 p_token1 => G_SQLCODE_TOKEN ,
604 p_token1_value => SQLCODE ,
605 p_token2 => G_SQLERRM_TOKEN ,
606 p_token2_value => SQLERRM
607 );
608
609 IF c_funding_source_id%ISOPEN THEN
610 CLOSE c_funding_source_id;
611 END IF;
612
613 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
614
615 END validate_funding_source_id;
616
617
618 --
619 -- Procedure: validate_object_id
620 --
621 -- Description: This procedure is used to validate object_id
622 --
623 --
624
625 PROCEDURE validate_object_id(p_object_id NUMBER ,
626 p_return_status OUT NOCOPY VARCHAR2
627 ) is
628
629 cursor c_object_id is
630 select 'x'
631 from oke_k_headers
632 where k_header_id = p_object_id;
633
634 l_dummy_value VARCHAR2(1) := '?';
635
636 BEGIN
637
638 p_return_status := OKE_API.G_RET_STS_SUCCESS;
639
640 IF (p_object_id is null) OR
641 (p_object_id = OKE_API.G_MISS_NUM) THEN
642
643 OKE_API.set_message(p_app_name => G_APP_NAME ,
644 p_msg_name => 'OKE_API_MISSING_VALUE' ,
645 p_token1 => 'VALUE' ,
646 p_token1_value => 'object_id'
647 );
648
649 p_return_status := OKE_API.G_RET_STS_ERROR;
650
651 ELSE
652
653 OPEN c_object_id;
654 FETCH c_object_id into l_dummy_value;
655 CLOSE c_object_id;
656
657 IF (l_dummy_value = '?') THEN
658
659 OKE_API.set_message(p_app_name => G_APP_NAME ,
660 p_msg_name => 'OKE_API_INVALID_VALUE' ,
661 p_token1 => 'VALUE' ,
662 p_token1_value => 'object_id'
663 );
664
665 p_return_status := OKE_API.G_RET_STS_ERROR;
666
667 END IF;
668
669 END IF;
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
674 OKE_API.set_message(p_app_name => G_APP_NAME ,
675 p_msg_name => G_UNEXPECTED_ERROR ,
676 p_token1 => G_SQLCODE_TOKEN ,
677 p_token1_value => SQLCODE ,
678 p_token2 => G_SQLERRM_TOKEN ,
679 p_token2_value => SQLERRM
680 );
681
682 IF c_object_id%ISOPEN THEN
683 CLOSE c_object_id;
684 END IF;
685
686 END validate_object_id;
687
688
689 --
690 -- Procedure: validate_currency_code
691 --
692 -- Description: This procedure is used to validate currency_code
693 --
694 --
695
696 PROCEDURE validate_currency_code(p_currency_code VARCHAR2 ,
697 p_return_status OUT NOCOPY VARCHAR2
698 ) is
699
700 cursor c_currency_code is
701 select 'x'
702 from fnd_currencies
703 where currency_code = upper(p_currency_code);
704
705 l_dummy_value VARCHAR2(1) := '?';
706
707 BEGIN
708
709 p_return_status := OKE_API.G_RET_STS_SUCCESS;
710
711 IF (p_currency_code is null) OR
712 (p_currency_code = OKE_API.G_MISS_CHAR) THEN
713
714 OKE_API.set_message(p_app_name => G_APP_NAME ,
715 p_msg_name => 'OKE_API_MISSING_VALUE' ,
716 p_token1 => 'VALUE' ,
717 p_token1_value => 'currency_code'
718 );
719
720 p_return_status := OKE_API.G_RET_STS_ERROR;
721
722 ELSE
723
724 OPEN c_currency_code;
725 FETCH c_currency_code into l_dummy_value;
726 CLOSE c_currency_code;
727
728 IF (l_dummy_value = '?') THEN
729
730 OKE_API.set_message(p_app_name => G_APP_NAME ,
731 p_msg_name => 'OKE_API_INVALID_VALUE' ,
732 p_token1 => 'VALUE' ,
733 p_token1_value => 'currency_code'
734 );
735
736 p_return_status := OKE_API.G_RET_STS_ERROR;
737
738 END IF;
739
740 END IF;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
745 OKE_API.set_message(p_app_name => G_APP_NAME ,
746 p_msg_name => G_UNEXPECTED_ERROR ,
747 p_token1 => G_SQLCODE_TOKEN ,
748 p_token1_value => SQLCODE ,
749 p_token2 => G_SQLERRM_TOKEN ,
750 p_token2_value => SQLERRM
751 );
752
753 IF c_currency_code%ISOPEN THEN
754 CLOSE c_currency_code;
755 END IF;
756
757 END validate_currency_code;
758
759
760 --
761 -- Procedure: validate_agreement_id
762 --
763 -- Description: This procedure is used to validate agreement_id and check if all projects in project
764 -- fundings are under a valid project hierarchy
765 --
766 --
767
768 PROCEDURE validate_agreement_id(p_agreement_id IN NUMBER ,
769 p_object_id IN NUMBER ,
770 p_return_status OUT NOCOPY VARCHAR2
771 ) is
772
773 cursor c_agreement_id is
774 select 'x'
775 from pa_agreements_all
776 where agreement_id = p_agreement_id
777 and nvl(pm_product_code, '-99') <> G_PRODUCT_CODE;
778
779 cursor c_master_project is
780 select project_id
781 from oke_k_headers
782 where k_header_id = p_object_id;
783
784 cursor c_project (x_project_id number) is
785 select 'x'
786 from pa_project_fundings
787 where agreement_id = p_agreement_id
788 and project_id not in
789 (select to_number(sub_project_id)
790 from pa_fin_structures_links_v
791 start with parent_project_id = x_project_id
792 connect by parent_project_id = prior sub_project_id
793 union all
794 select x_project_id
795 from dual
796 );
797
798 l_dummy_value VARCHAR2(1) := '?';
799 l_project_id NUMBER;
800
801 BEGIN
802
803 p_return_status := OKE_API.G_RET_STS_SUCCESS;
804
805 IF (p_agreement_id is null) OR
806 (p_agreement_id = OKE_API.G_MISS_NUM) THEN
807
808 OKE_API.set_message(p_app_name => G_APP_NAME ,
809 p_msg_name => 'OKE_API_MISSING_VALUE' ,
810 p_token1 => 'VALUE' ,
811 p_token1_value => 'agreement_id'
812 );
813
814 p_return_status := OKE_API.G_RET_STS_ERROR;
815
816 ELSE
817
818 OPEN c_agreement_id;
819 FETCH c_agreement_id into l_dummy_value;
820 CLOSE c_agreement_id;
821
822 IF (l_dummy_value = '?') THEN
823
824 OKE_API.set_message(p_app_name => G_APP_NAME ,
825 p_msg_name => 'OKE_API_INVALID_VALUE' ,
826 p_token1 => 'VALUE' ,
827 p_token1_value => 'agreement_id'
828 );
829
830 p_return_status := OKE_API.G_RET_STS_ERROR;
831
832 END IF;
833
834 OPEN c_master_project;
835 FETCH c_master_project into l_project_id;
836 CLOSE c_master_project;
837
838 IF (l_project_id is null) THEN
839
840 OKE_API.set_message(p_app_name => G_APP_NAME ,
841 p_msg_name => 'OKE_API_INVALID_VALUE' ,
842 p_token1 => 'VALUE' ,
843 p_token1_value => 'agreement_id'
844 );
845
846 p_return_status := OKE_API.G_RET_STS_ERROR;
847
848 ELSE
849
850 l_dummy_value := '?';
851 OPEN c_project(l_project_id);
852 FETCH c_project into l_dummy_value;
853 CLOSE c_project;
854
855 IF (l_dummy_value = 'x') THEN
856
857 OKE_API.set_message(p_app_name => G_APP_NAME ,
858 p_msg_name => 'OKE_API_INVALID_VALUE' ,
859 p_token1 => 'VALUE' ,
860 p_token1_value => 'agreement_id'
861 );
862
863 p_return_status := OKE_API.G_RET_STS_ERROR;
864
865 END IF;
866
867 END IF;
868
869 END IF;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
874 OKE_API.set_message(p_app_name => G_APP_NAME ,
875 p_msg_name => G_UNEXPECTED_ERROR ,
876 p_token1 => G_SQLCODE_TOKEN ,
877 p_token1_value => SQLCODE ,
878 p_token2 => G_SQLERRM_TOKEN ,
879 p_token2_value => SQLERRM
880 );
881
882 IF c_agreement_id%ISOPEN THEN
883 CLOSE c_agreement_id;
884 END IF;
885
886 END validate_agreement_id;
887
888
889
890 --
891 -- Procedure: lock_agreement_id
892 --
893 -- Description: This procedure is used to lock the agreement record
894 --
895 --
896
897 PROCEDURE lock_agreement_id(p_agreement_id IN NUMBER ,
898 p_return_status OUT NOCOPY VARCHAR2
899 ) is
900
901 cursor c_agreement_id is
902 select 'x'
903 from pa_agreements_all
904 where agreement_id = p_agreement_id
905 FOR UPDATE OF agreement_id NOWAIT;
906
907 l_dummy_value varchar2(1) := '?';
908
909 BEGIN
910
911 p_return_status := OKE_API.G_RET_STS_SUCCESS;
912
913 OPEN c_agreement_id;
914 FETCH c_agreement_id into l_dummy_value;
915 CLOSE c_agreement_id;
916
917 IF (l_dummy_value = '?') THEN
918
919 OKE_API.set_message(p_app_name => G_APP_NAME ,
920 p_msg_name => 'OKE_API_INVALID_VALUE' ,
921 p_token1 => 'VALUE' ,
922 p_token1_value => 'agreement_id'
923 );
924
925 p_return_status := OKE_API.G_RET_STS_ERROR;
926
927 END IF;
928
929 EXCEPTION
930 WHEN G_RESOURCE_BUSY THEN
931 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
932 OKE_API.set_message(p_app_name => G_APP_NAME ,
933 p_msg_name => 'OKE_ROW_LOCKED' ,
934 p_token1 => 'SOURCE' ,
935 p_token1_value => 'OKE_AGREEMENT_PROMPT' ,
936 p_token1_translate => OKE_API.G_TRUE ,
937 p_token2 => 'ID' ,
938 p_token2_value => p_agreement_id
939 );
940
941 IF c_agreement_id%ISOPEN THEN
942 CLOSE c_agreement_id;
943 END IF;
944
945 WHEN OTHERS THEN
946 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
947 OKE_API.set_message(p_app_name => G_APP_NAME ,
948 p_msg_name => G_UNEXPECTED_ERROR ,
949 p_token1 => G_SQLCODE_TOKEN ,
950 p_token1_value => SQLCODE ,
951 p_token2 => G_SQLERRM_TOKEN ,
952 p_token2_value => SQLERRM
953 );
954
955 IF c_agreement_id%ISOPEN THEN
956 CLOSE c_agreement_id;
957 END IF;
958
959 END lock_agreement_id;
960
961
962 --
963 -- Procedure: lock_project_funding
964 --
965 -- Description: This procedure is used to lock the project funding records
966 --
967 --
968
969 PROCEDURE lock_project_funding(p_agreement_id IN NUMBER ,
970 p_return_status OUT NOCOPY VARCHAR2
971 ) is
972
973 cursor c_project_funding is
974 select 'x'
975 from pa_project_fundings
976 where agreement_id = p_agreement_id
977 FOR UPDATE OF project_funding_id NOWAIT;
978
979 l_dummy_value varchar2(1) := '?';
980
981 BEGIN
982
983 p_return_status := OKE_API.G_RET_STS_SUCCESS;
984
985 OPEN c_project_funding;
986 FETCH c_project_funding into l_dummy_value;
987 CLOSE c_project_funding;
988
989 EXCEPTION
990 WHEN G_RESOURCE_BUSY THEN
991 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
992 OKE_API.set_message(p_app_name => G_APP_NAME ,
993 p_msg_name => 'OKE_ROW_LOCKED' ,
994 p_token1 => 'SOURCE' ,
995 p_token1_value => 'OKE_PRJ_FUNDING_PROMPT' ,
996 p_token1_translate => OKE_API.G_TRUE ,
997 p_token2 => 'ID' ,
998 p_token2_value => null
999 );
1000
1001 IF c_project_funding%ISOPEN THEN
1002 CLOSE c_project_funding;
1003 END IF;
1004
1005 WHEN OTHERS THEN
1006 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1007 OKE_API.set_message(p_app_name => G_APP_NAME ,
1008 p_msg_name => G_UNEXPECTED_ERROR ,
1009 p_token1 => G_SQLCODE_TOKEN ,
1010 p_token1_value => SQLCODE ,
1011 p_token2 => G_SQLERRM_TOKEN ,
1012 p_token2_value => SQLERRM
1013 );
1014
1015 IF c_project_funding%ISOPEN THEN
1016 CLOSE c_project_funding;
1017 END IF;
1018
1019 END lock_project_funding;
1020
1021
1022
1023 --
1024 -- Procedure: lock_agreement
1025 --
1026 -- Description: This procedure is used to lock the agreement records
1027 --
1028 --
1029
1030 PROCEDURE lock_agreement(p_agreement_id IN NUMBER
1031 ) is
1032
1033 l_return_status VARCHAR2(1);
1034
1035 BEGIN
1036
1037 lock_agreement_id(p_agreement_id => p_agreement_id ,
1038 p_return_status => l_return_status
1039 );
1040
1041 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1042
1043 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1044
1045 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1046
1047 RAISE OKE_API.G_EXCEPTION_ERROR;
1048
1049 END IF;
1050
1051 lock_project_funding(p_agreement_id => p_agreement_id ,
1052 p_return_status => l_return_status
1053 );
1054
1055 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1056
1057 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1058
1059 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1060
1061 RAISE OKE_API.G_EXCEPTION_ERROR;
1062
1063 END IF;
1064
1065 END lock_agreement;
1066
1067
1068 --
1069 -- Procedure: validate_k_party_id
1070 --
1071 -- Description: This procedure is used to validate k_party_id
1072 --
1073 --
1074
1075 PROCEDURE validate_k_party_id(p_k_header_id IN NUMBER ,
1076 p_k_party_id IN NUMBER ,
1077 p_return_status OUT NOCOPY VARCHAR2
1078 ) is
1079 cursor c_party_id is
1080 select 'x'
1081 from okc_k_party_roles_b b,
1082 okx_parties_v v
1083 where dnz_chr_id = p_k_header_id
1084 and b.object1_id1 = v.id1
1085 and b.object1_id2 = v.id2
1086 and b.rle_code = 'FUND_BY'
1087 and b.jtot_object1_code = 'OKX_PARTY'
1088 and v.id1 = p_k_party_id;
1089
1090 l_dummy_value VARCHAR2(1) := '?';
1091
1092 BEGIN
1093
1094 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1095
1096 IF (p_k_party_id is null) OR
1097 (p_k_party_id = OKE_API.G_MISS_NUM) THEN
1098
1099 OKE_API.set_message(p_app_name => G_APP_NAME ,
1100 p_msg_name => 'OKE_API_MISSING_VALUE' ,
1101 p_token1 => 'VALUE' ,
1102 p_token1_value => 'k_party_id'
1103 );
1104
1105 p_return_status := OKE_API.G_RET_STS_ERROR;
1106
1107 ELSE
1108
1109 OPEN c_party_id;
1110 FETCH c_party_id into l_dummy_value;
1111 CLOSE c_party_id;
1112
1113 IF (l_dummy_value = '?') THEN
1114
1115 OKE_API.set_message(p_app_name => G_APP_NAME ,
1116 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1117 p_token1 => 'VALUE' ,
1118 p_token1_value => 'k_party_id'
1119 );
1120
1121 p_return_status := OKE_API.G_RET_STS_ERROR;
1122
1123 END IF;
1124
1125 END IF;
1126
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1130 OKE_API.set_message(p_app_name => G_APP_NAME ,
1131 p_msg_name => G_UNEXPECTED_ERROR ,
1132 p_token1 => G_SQLCODE_TOKEN ,
1133 p_token1_value => SQLCODE ,
1134 p_token2 => G_SQLERRM_TOKEN ,
1135 p_token2_value => SQLERRM
1136 );
1137
1138 IF c_party_id%ISOPEN THEN
1139 CLOSE c_party_id;
1140 END IF;
1141
1142 END validate_k_party_id;
1143
1144
1145 --
1146 -- Procedure: validate_funding_status
1147 --
1148 -- Description: This procedure is used to validate funding_status
1149 --
1150 --
1151
1152 PROCEDURE validate_funding_status(p_funding_status VARCHAR2 ,
1153 p_return_status OUT NOCOPY VARCHAR2
1154 ) is
1155
1156 cursor c_funding_status is
1157 select 'x'
1158 from fnd_lookup_values
1159 where lookup_type = 'FUNDING_STATUS'
1160 and enabled_flag = 'Y'
1161 and language = userenv('LANG')
1162 and lookup_code = upper(p_funding_status);
1163
1164 l_dummy_value VARCHAR2(1) := '?';
1165
1166 BEGIN
1167
1168 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1169
1170 IF (p_funding_status is not null) THEN
1171
1172 OPEN c_funding_status;
1173 FETCH c_funding_status into l_dummy_value;
1174 CLOSE c_funding_status;
1175
1176 IF (l_dummy_value = '?') THEN
1177
1178 OKE_API.set_message(p_app_name => G_APP_NAME ,
1179 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1180 p_token1 => 'VALUE' ,
1181 p_token1_value => 'funding_status'
1182 );
1183
1184 p_return_status := OKE_API.G_RET_STS_ERROR;
1185
1186 END IF;
1187
1188 END IF;
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1193 OKE_API.set_message(p_app_name => G_APP_NAME ,
1194 p_msg_name => G_UNEXPECTED_ERROR ,
1195 p_token1 => G_SQLCODE_TOKEN ,
1196 p_token1_value => SQLCODE ,
1197 p_token2 => G_SQLERRM_TOKEN ,
1198 p_token2_value => SQLERRM
1199 );
1200
1201 IF c_funding_status%ISOPEN THEN
1202 CLOSE c_funding_status;
1203 END IF;
1204
1205 END validate_funding_status;
1206
1207
1208 --
1209 -- Procedure: validate_conversion_type
1210 --
1211 -- Description: This procedure is used to validate conversion_type
1212 --
1213 --
1214
1215 PROCEDURE validate_conversion_type(p_conversion_type VARCHAR2 ,
1216 p_return_status OUT NOCOPY VARCHAR2
1217 ) is
1218
1219 cursor c_type is
1220 select 'x'
1221 from gl_daily_conversion_types
1222 where UPPER(conversion_type) = UPPER(p_conversion_type);
1223
1224 l_dummy_value VARCHAR2(1) := '?';
1225
1226 BEGIN
1227
1228 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1229
1230 IF (p_conversion_type is not null) THEN
1231
1232 OPEN c_type;
1233 FETCH c_type into l_dummy_value;
1234 CLOSE c_type;
1235
1236 IF (l_dummy_value = '?') THEN
1237
1238 OKE_API.set_message(p_app_name => G_APP_NAME ,
1239 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1240 p_token1 => 'VALUE' ,
1241 p_token1_value => 'conversion_type'
1242 );
1243
1244 p_return_status := OKE_API.G_RET_STS_ERROR;
1245
1246 END IF;
1247
1248 END IF;
1249
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1253 OKE_API.set_message(p_app_name => G_APP_NAME ,
1254 p_msg_name => G_UNEXPECTED_ERROR ,
1255 p_token1 => G_SQLCODE_TOKEN ,
1256 p_token1_value => SQLCODE ,
1257 p_token2 => G_SQLERRM_TOKEN ,
1258 p_token2_value => SQLERRM
1259 );
1260
1261 IF c_type%ISOPEN THEN
1262 CLOSE c_type;
1263 END IF;
1264
1265 END validate_conversion_type;
1266
1267
1268 --
1269 -- Procedure: validate_pool_party_id
1270 --
1271 -- Description: This procedure is used to validate pool_party_id
1272 --
1273 --
1274
1275 PROCEDURE validate_pool_party_id(p_pool_party_id NUMBER ,
1276 p_return_status OUT NOCOPY VARCHAR2
1277 ) is
1278
1279 cursor c_pool_party_id is
1280 select 'x'
1281 from oke_pool_parties
1282 where pool_party_id = p_pool_party_id
1283 FOR UPDATE OF pool_party_id NOWAIT;
1284
1285 l_dummy_value VARCHAR2(1) := '?';
1286
1287 BEGIN
1288
1289 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1290
1291 IF (p_pool_party_id is not null) THEN
1292
1293 OPEN c_pool_party_id;
1294 FETCH c_pool_party_id into l_dummy_value;
1295 CLOSE c_pool_party_id;
1296
1297 IF (l_dummy_value = '?') THEN
1298
1299 OKE_API.set_message(p_app_name => G_APP_NAME ,
1300 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1301 p_token1 => 'VALUE' ,
1302 p_token1_value => 'pool_party_id'
1303 );
1304
1305 p_return_status := OKE_API.G_RET_STS_ERROR;
1306
1307 END IF;
1308
1309 END IF;
1310
1311 EXCEPTION
1312 WHEN G_RESOURCE_BUSY THEN
1313 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1314 OKE_API.set_message(p_app_name => G_APP_NAME ,
1315 p_msg_name => 'OKE_ROW_LOCKED' ,
1316 p_token1 => 'SOURCE' ,
1317 p_token1_value => 'OKE_POOL_PARTY_PROMPT' ,
1318 p_token1_translate => OKE_API.G_TRUE ,
1319 p_token2 => 'ID' ,
1320 p_token2_value => p_pool_party_id
1321 );
1322
1323 IF c_pool_party_id%ISOPEN THEN
1324 CLOSE c_pool_party_id;
1325 END IF;
1326
1327 WHEN OTHERS THEN
1328 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1329 OKE_API.set_message(p_app_name => G_APP_NAME ,
1330 p_msg_name => G_UNEXPECTED_ERROR ,
1331 p_token1 => G_SQLCODE_TOKEN ,
1332 p_token1_value => SQLCODE ,
1333 p_token2 => G_SQLERRM_TOKEN ,
1334 p_token2_value => SQLERRM
1335 );
1336
1337 IF c_pool_party_id%ISOPEN THEN
1338 CLOSE c_pool_party_id;
1339 END IF;
1340
1341 END validate_pool_party_id;
1342
1343
1344 --
1345 -- Procedure: validate_pool_party_related
1346 --
1347 -- Description: This procedure is used to validate pool party related attributes
1348 --
1349 --
1350
1351 PROCEDURE validate_pool_party_related(p_pool_party_id NUMBER ,
1352 p_party_id NUMBER ,
1353 p_funding_source_id NUMBER ,
1354 p_amount NUMBER ,
1355 p_currency_code VARCHAR2,
1356 p_start_date DATE ,
1357 p_end_date DATE ,
1358 p_return_status OUT NOCOPY VARCHAR2
1359 ) is
1360 cursor c_pool_party_id is
1361 select currency_code
1362 from oke_pool_parties
1363 where pool_party_id = p_pool_party_id
1364 and party_id = p_party_id
1365 FOR UPDATE OF pool_party_id NOWAIT;
1366
1367 l_currency_code VARCHAR2(15);
1368 l_return_status VARCHAR2(1);
1369 l_flag VARCHAR2(1);
1370
1371 BEGIN
1372
1373 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1374
1375 IF (p_pool_party_id is not null) OR
1376 (p_pool_party_id <> OKE_API.G_MISS_NUM) THEN
1377
1378 OPEN c_pool_party_id;
1379 FETCH c_pool_party_id into l_currency_code;
1380
1381 IF (c_pool_party_id%NOTFOUND) THEN
1382
1383 CLOSE c_pool_party_id;
1384 p_return_status := OKE_API.G_RET_STS_ERROR;
1385 OKE_API.set_message(p_app_name => G_APP_NAME ,
1386 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1387 p_token1 => 'VALUE' ,
1388 p_token1_value => 'pool_party_id, k_party_id'
1389 );
1390
1391 ELSE
1392
1393 IF (l_currency_code <> p_currency_code) THEN
1394
1395 p_return_status := OKE_API.G_RET_STS_ERROR;
1396 OKE_API.set_message(p_app_name => G_APP_NAME ,
1397 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1398 p_token1 => 'VALUE' ,
1399 p_token1_value => 'currency_code'
1400 );
1401 ELSE
1402
1403 IF (p_funding_source_id is not null) THEN
1404 l_flag := 'N';
1405 ELSE
1406 l_flag := 'Y';
1407 END IF;
1408
1409 OKE_FUNDING_UTIL_PKG.validate_source_pool_amount(x_first_amount => p_amount ,
1410 x_pool_party_id => p_pool_party_id ,
1411 x_source_id => p_funding_source_id ,
1412 x_new_flag => l_flag ,
1413 x_return_status => l_return_status );
1414
1415 IF (l_return_status = 'N') THEN
1416
1417 p_return_status := OKE_API.G_RET_STS_ERROR;
1418 OKE_API.set_message(p_app_name => G_APP_NAME ,
1419 p_msg_name => 'OKE_FUND_EXCEED_POOL'
1420 );
1421
1422 -- bug 3346170
1423 /* ELSE
1424
1425 OKE_FUNDING_UTIL_PKG.validate_source_pool_date(x_start_end => 'START' ,
1426 x_pool_party_id => p_pool_party_id ,
1427 x_date => p_start_date ,
1428 x_return_status => l_return_status );
1429
1430 IF (l_return_status = 'N') THEN
1431
1432 p_return_status := OKE_API.G_RET_STS_ERROR;
1433 OKE_API.set_message(p_app_name => G_APP_NAME ,
1434 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
1435 p_token1 => 'EFFECTIVE_DATE' ,
1436 p_token1_value => 'OKE_EFFECTIVE_FROM_PROMPT' ,
1437 p_token1_translate => OKE_API.G_TRUE ,
1438 p_token2 => 'OPERATOR' ,
1439 p_token2_value => 'OKE_GREATER_PROMPT' ,
1440 p_token2_translate => OKE_API.G_TRUE ,
1441 p_token3 => 'DATE_SOURCE' ,
1442 p_token3_value => 'OKE_POOL_PARTY_PROMPT' ,
1443 p_token3_translate => OKE_API.G_TRUE
1444 );
1445
1446 ELSE
1447
1448 OKE_FUNDING_UTIL_PKG.validate_source_pool_date(x_start_end => 'END' ,
1449 x_pool_party_id => p_pool_party_id ,
1450 x_date => p_end_date ,
1451 x_return_status => l_return_status );
1452
1453 IF (l_return_status = 'N') THEN
1454
1455 p_return_status := OKE_API.G_RET_STS_ERROR;
1456 OKE_API.set_message(p_app_name => G_APP_NAME ,
1457 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
1458 p_token1 => 'EFFECTIVE_DATE' ,
1459 p_token1_value => 'OKE_EFFECTIVE_TO_PROMPT' ,
1460 p_token1_translate => OKE_API.G_TRUE ,
1461 p_token2 => 'OPERATOR' ,
1462 p_token2_value => 'OKE_EARLIER_PROMPT' ,
1463 p_token2_translate => OKE_API.G_TRUE ,
1464 p_token3 => 'DATE_SOURCE' ,
1465 p_token3_value => 'OKE_POOL_PARTY_PROMPT' ,
1466 p_token3_translate => OKE_API.G_TRUE
1467 );
1468 END IF;
1469
1470 END IF;
1471 */
1472 END IF;
1473
1474 END IF;
1475
1476 CLOSE c_pool_party_id;
1477
1478 END IF;
1479
1480 END IF;
1481
1482 EXCEPTION
1483 WHEN G_RESOURCE_BUSY THEN
1484 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1485 OKE_API.set_message(p_app_name => G_APP_NAME ,
1486 p_msg_name => 'OKE_ROW_LOCKED' ,
1487 p_token1 => 'SOURCE' ,
1488 p_token1_value => 'OKE_POOL_PARTY_PROMPT' ,
1489 p_token1_translate => OKE_API.G_TRUE ,
1490 p_token2 => 'ID' ,
1491 p_token2_value => p_pool_party_id
1492 );
1493
1494 IF c_pool_party_id%ISOPEN THEN
1495 CLOSE c_pool_party_id;
1496 END IF;
1497
1498 WHEN OTHERS THEN
1499 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1500 OKE_API.set_message(p_app_name => G_APP_NAME ,
1501 p_msg_name => G_UNEXPECTED_ERROR ,
1502 p_token1 => G_SQLCODE_TOKEN ,
1503 p_token1_value => SQLCODE ,
1504 p_token2 => G_SQLERRM_TOKEN ,
1505 p_token2_value => SQLERRM
1506 );
1507
1508 IF c_pool_party_id%ISOPEN THEN
1509 CLOSE c_pool_party_id;
1510 END IF;
1511
1512 END validate_pool_party_related;
1513
1514
1515 --
1516 -- Procedure: validate_attributes
1517 --
1518 -- Description: This procedure is used to validate the attributes of funding_in_rec
1519 --
1520 --
1521
1522 PROCEDURE validate_attributes(p_funding_in_rec FUNDING_REC_IN_TYPE)
1523 is
1524
1525 l_return_status VARCHAR2(1);
1526
1527 BEGIN
1528
1529 --
1530 -- Validate Object_type
1531 --
1532
1533 validate_object_type(p_object_type => p_funding_in_rec.object_type ,
1534 p_return_status => l_return_status
1535 );
1536
1537 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1538
1539 RAISE G_EXCEPTION_HALT_VALIDATION;
1540
1541 END IF;
1542
1543 --
1544 -- Validate Object_id
1545 --
1546
1547 validate_object_id(p_object_id => p_funding_in_rec.object_id ,
1548 p_return_status => l_return_status
1549 );
1550
1551 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1552
1553 RAISE G_EXCEPTION_HALT_VALIDATION;
1554
1555 END IF;
1556
1557 --
1558 -- Validate Currency Code
1559 --
1560
1561 validate_currency_code(p_currency_code => p_funding_in_rec.currency_code ,
1562 p_return_status => l_return_status
1563 );
1564
1565 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1566
1567 RAISE G_EXCEPTION_HALT_VALIDATION;
1568
1569 END IF;
1570
1571 --
1572 -- Validate K_party_id
1573 --
1574 validate_k_party_id(p_k_header_id => p_funding_in_rec.object_id ,
1575 p_k_party_id => p_funding_in_rec.k_party_id ,
1576 p_return_status => l_return_status
1577 );
1578
1579 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1580
1581 RAISE G_EXCEPTION_HALT_VALIDATION;
1582
1583 END IF;
1584
1585 --
1586 -- Validate Amount
1587 --
1588
1589 validate_amount(p_amount => p_funding_in_rec.amount ,
1590 p_return_status => l_return_status
1591 );
1592
1593 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1594
1595 RAISE G_EXCEPTION_HALT_VALIDATION;
1596
1597 END IF;
1598
1599 --
1600 -- Validate Funding_status
1601 --
1602
1603 validate_funding_status(p_funding_status => p_funding_in_rec.funding_status ,
1604 p_return_status => l_return_status
1605 );
1606
1607 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1608
1609 RAISE G_EXCEPTION_HALT_VALIDATION;
1610
1611 END IF;
1612
1613 --
1614 -- Validate Conversion_type
1615 --
1616
1617 validate_conversion_type(p_conversion_type => p_funding_in_rec.k_conversion_type ,
1618 p_return_status => l_return_status
1619 );
1620
1621 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1622
1623 RAISE G_EXCEPTION_HALT_VALIDATION;
1624
1625 END IF;
1626
1627 --
1628 -- Validate Pool_party_id
1629 --
1630
1631 validate_pool_party_id(p_pool_party_id => p_funding_in_rec.pool_party_id ,
1632 p_return_status => l_return_status
1633 );
1634
1635 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1636
1637 RAISE G_EXCEPTION_HALT_VALIDATION;
1638
1639 END IF;
1640
1641 --
1642 -- Validate Agreement Owning Org Id
1643 --
1644 validate_agreement_org(p_agreement_org_id => p_funding_in_rec.agreement_org_id ,
1645 -- p_k_header_id => p_funding_in_rec.object_id ,
1646 p_return_status => l_return_status
1647 );
1648
1649 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1650
1651 RAISE G_EXCEPTION_HALT_VALIDATION;
1652
1653 END IF;
1654
1655 END validate_attributes;
1656
1657
1658 --
1659 -- Procedure: validate_record
1660 --
1661 -- Description: This procedure is used to validate the funding record
1662 --
1663 --
1664
1665 PROCEDURE validate_record(p_funding_in_rec IN OUT NOCOPY FUNDING_REC_IN_TYPE ,
1666 p_flag VARCHAR2
1667 ) is
1668
1669 l_return_status VARCHAR2(1);
1670 l_currency_code VARCHAR2(15);
1671 l_type VARCHAR2(20);
1672
1673 BEGIN
1674
1675 --
1676 -- Validate start and end date range
1677 --
1678
1679 OKE_FUNDING_UTIL_PKG.validate_start_end_date(x_start_date => p_funding_in_rec.start_date_active ,
1680 x_end_date => p_funding_in_rec.end_date_active ,
1681 x_return_status => l_return_status
1682 );
1683
1684 IF (l_return_status = 'N') THEN
1685
1686 OKE_API.set_message(p_app_name => G_APP_NAME ,
1687 p_msg_name => 'OKE_INVALID_EFFDATE_PAIR'
1688 );
1689
1690 RAISE G_EXCEPTION_HALT_VALIDATION;
1691
1692 END IF;
1693
1694 --
1695 -- Validate pool_party_id related attributes
1696 --
1697
1698 validate_pool_party_related(p_pool_party_id => p_funding_in_rec.pool_party_id ,
1699 p_party_id => p_funding_in_rec.k_party_id ,
1700 p_amount => p_funding_in_rec.amount ,
1701 p_currency_code => p_funding_in_rec.currency_code ,
1702 p_start_date => p_funding_in_rec.start_date_active ,
1703 p_end_date => p_funding_in_rec.end_date_active ,
1704 p_funding_source_id => p_funding_in_rec.funding_source_id ,
1705 p_return_status => l_return_status
1706 );
1707
1708 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1709
1710 RAISE G_EXCEPTION_HALT_VALIDATION;
1711
1712 END IF;
1713
1714 --
1715 -- Validate contract conversion
1716 --
1717
1718 --
1719 -- Get contract currency
1720 --
1721
1722 l_currency_code := get_k_currency(p_header_id => p_funding_in_rec.object_id);
1723
1724 -- syho, bug 2208979
1725 IF (nvl(p_flag, 'N') = 'Y') THEN
1726
1727 get_conversion(p_from_currency => p_funding_in_rec.currency_code ,
1728 p_to_currency => l_currency_code ,
1729 p_conversion_type => p_funding_in_rec.k_conversion_type ,
1730 p_conversion_date => p_funding_in_rec.k_conversion_date ,
1731 p_conversion_rate => p_funding_in_rec.k_conversion_rate ,
1732 p_return_status => l_return_status
1733 );
1734
1735 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1736
1737 RAISE G_EXCEPTION_HALT_VALIDATION;
1738
1739 END IF;
1740
1741 END IF;
1742 -- syho, bug 2208979
1743
1744 --
1745 -- Hard limit cannot be negative
1746 --
1747 IF (nvl(p_funding_in_rec.hard_limit, 0) < 0) THEN
1748
1749 OKE_API.set_message(p_app_name => G_APP_NAME ,
1750 p_msg_name => 'OKE_NEGATIVE_HARD_LIMIT'
1751 );
1752
1753 RAISE G_EXCEPTION_HALT_VALIDATION;
1754
1755 END IF;
1756
1757 IF (nvl(p_funding_in_rec.revenue_hard_limit, 0) < 0) THEN
1758
1759 OKE_API.set_message(p_app_name => G_APP_NAME ,
1760 p_msg_name => 'OKE_NEGATIVE_REVENUE_LIMIT'
1761 );
1762
1763 RAISE G_EXCEPTION_HALT_VALIDATION;
1764
1765 END IF;
1766
1767 --
1768 -- Validate hard limit against funding amount
1769 --
1770
1771 OKE_FUNDING_UTIL_PKG.validate_hard_limit(x_fund_amount => p_funding_in_rec.amount ,
1772 x_hard_limit => p_funding_in_rec.hard_limit ,
1773 x_return_status => l_return_status
1774 );
1775
1776 IF (l_return_status <> 'Y') THEN
1777
1778 OKE_API.set_message(p_app_name => G_APP_NAME ,
1779 p_msg_name => 'OKE_HARD_LIMIT_EXCEED_FUND'
1780 );
1781
1782 RAISE G_EXCEPTION_HALT_VALIDATION;
1783
1784 END IF;
1785
1786 OKE_FUNDING_UTIL_PKG.validate_hard_limit(x_fund_amount => p_funding_in_rec.amount ,
1787 x_hard_limit => p_funding_in_rec.revenue_hard_limit ,
1788 x_return_status => l_return_status
1789 );
1790
1791 IF (l_return_status <> 'Y') THEN
1792
1793 OKE_API.set_message(p_app_name => G_APP_NAME ,
1794 p_msg_name => 'OKE_REV_LIMIT_EXCEED_FUND'
1795 );
1796
1797 RAISE G_EXCEPTION_HALT_VALIDATION;
1798
1799 END IF;
1800
1801 --
1802 -- Validate hard limit w/ allocation lines
1803 --
1804
1805 IF (p_funding_in_rec.funding_source_id is not null) THEN
1806
1807 OKE_FUNDING_UTIL_PKG.validate_source_alloc_limit(x_source_id => p_funding_in_rec.funding_source_id ,
1808 x_amount => nvl(p_funding_in_rec.hard_limit, 0) ,
1809 x_revenue_amount => nvl(p_funding_in_rec.revenue_hard_limit, 0) ,
1810 x_type => l_type ,
1811 x_return_status => l_return_status
1812 );
1813
1814 IF (l_return_status = 'N') THEN
1815
1816 IF (l_type = 'INVOICE') THEN
1817
1818 OKE_API.set_message(p_app_name => G_APP_NAME ,
1819 p_msg_name => 'OKE_HARD_LIMIT_EXCEED'
1820 );
1821
1822 ELSE
1823
1824 OKE_API.set_message(p_app_name => G_APP_NAME ,
1825 p_msg_name => 'OKE_REV_LIMIT_EXCEED'
1826 );
1827
1828 END IF;
1829
1830 RAISE G_EXCEPTION_HALT_VALIDATION;
1831
1832 ELSIF (l_return_status = 'E') THEN
1833
1834 IF (l_type = 'INVOICE') THEN
1835
1836 OKE_API.set_message(p_app_name => G_APP_NAME ,
1837 p_msg_name => 'OKE_NEGATIVE_HARD_LIMIT_SUM'
1838 );
1839
1840 ELSE
1841
1842 OKE_API.set_message(p_app_name => G_APP_NAME ,
1843 p_msg_name => 'OKE_NEGATIVE_REV_LIMIT_SUM'
1844 );
1845
1846 END IF;
1847
1848 RAISE G_EXCEPTION_HALT_VALIDATION;
1849
1850 END IF;
1851
1852 --
1853 -- Validate funding amount
1854 --
1855
1856 OKE_FUNDING_UTIL_PKG.validate_source_alloc_amount(x_source_id => p_funding_in_rec.funding_source_id ,
1857 x_amount => nvl(p_funding_in_rec.amount,0) ,
1858 x_return_status => l_return_status
1859 );
1860
1861 IF (l_return_status = 'N') THEN
1862
1863 OKE_API.set_message(p_app_name => G_APP_NAME ,
1864 p_msg_name => 'OKE_FUND_AMT_EXCEED'
1865 );
1866
1867 RAISE G_EXCEPTION_HALT_VALIDATION;
1868
1869 ELSIF (l_return_status = 'E') THEN
1870
1871 OKE_API.set_message(p_app_name => G_APP_NAME ,
1872 p_msg_name => 'OKE_NEGATIVE_ALLOCATION_SUM'
1873 );
1874
1875 RAISE G_EXCEPTION_HALT_VALIDATION;
1876
1877 END IF;
1878
1879 --
1880 -- Validate the date range
1881 --
1882 -- bug 3346170
1883 /*
1884 OKE_FUNDING_UTIL_PKG.validate_source_alloc_date(x_start_end => 'START' ,
1885 x_funding_source_id => p_funding_in_rec.funding_source_id ,
1886 x_date => p_funding_in_rec.start_date_active ,
1887 x_return_status => l_return_status);
1888
1889 IF (l_return_status = 'N') THEN
1890
1891 OKE_API.set_message(p_app_name => G_APP_NAME ,
1892 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
1893 p_token1 => 'EFFECTIVE_DATE' ,
1894 p_token1_value => 'OKE_EFFECTIVE_FROM_PROMPT' ,
1895 p_token1_translate => OKE_API.G_TRUE ,
1896 p_token2 => 'OPERATOR' ,
1897 p_token2_value => 'OKE_EARLIER_PROMPT' ,
1898 p_token2_translate => OKE_API.G_TRUE ,
1899 p_token3 => 'DATE_SOURCE' ,
1900 p_token3_value => 'OKE_EARLIEST_ALLOC_PROMPT' ,
1901 p_token3_translate => OKE_API.G_TRUE
1902 );
1903
1904 RAISE G_EXCEPTION_HALT_VALIDATION;
1905
1906 END IF;
1907
1908 OKE_FUNDING_UTIL_PKG.validate_source_alloc_date(x_start_end => 'END' ,
1909 x_funding_source_id => p_funding_in_rec.funding_source_id ,
1910 x_date => p_funding_in_rec.end_date_active ,
1911 x_return_status => l_return_status);
1912
1913 IF (l_return_status = 'N') THEN
1914
1915 OKE_API.set_message(p_app_name => G_APP_NAME ,
1916 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
1917 p_token1 => 'EFFECTIVE_DATE' ,
1918 p_token1_value => 'OKE_EFFECTIVE_TO_PROMPT' ,
1919 p_token1_translate => OKE_API.G_TRUE ,
1920 p_token2 => 'OPERATOR' ,
1921 p_token2_value => 'OKE_GREATER_PROMPT' ,
1922 p_token2_translate => OKE_API.G_TRUE ,
1923 p_token3 => 'DATE_SOURCE' ,
1924 p_token3_value => 'OKE_LATEST_ALLOC_PROMPT' ,
1925 p_token3_translate => OKE_API.G_TRUE
1926 );
1927
1928 RAISE G_EXCEPTION_HALT_VALIDATION;
1929
1930 END IF;
1931 */
1932 END IF;
1933
1934 END validate_record;
1935
1936
1937 --
1938 -- Function: null_funding_out
1939 --
1940 -- Description: This function is used to set all the missing values attributes to be null
1941 --
1942 --
1943
1944 FUNCTION null_funding_out(p_funding_in_rec IN FUNDING_REC_IN_TYPE) RETURN FUNDING_REC_IN_TYPE
1945 is
1946
1947 l_funding_in_rec FUNDING_REC_IN_TYPE := p_funding_in_rec;
1948
1949 BEGIN
1950
1951 l_funding_in_rec.funding_source_id := null;
1952
1953 IF l_funding_in_rec.pool_party_id = OKE_API.G_MISS_NUM THEN
1954 l_funding_in_rec.pool_party_id := null;
1955 END IF;
1956
1957 IF l_funding_in_rec.object_type = OKE_API.G_MISS_CHAR THEN
1958 l_funding_in_rec.object_type := null;
1959 END IF;
1960
1961 IF l_funding_in_rec.object_id= OKE_API.G_MISS_NUM THEN
1962 l_funding_in_rec.object_id := null;
1963 END IF;
1964
1965 IF l_funding_in_rec.k_party_id= OKE_API.G_MISS_NUM THEN
1966 l_funding_in_rec.k_party_id := null;
1967 END IF;
1968
1969 IF l_funding_in_rec.amount= OKE_API.G_MISS_NUM THEN
1970 l_funding_in_rec.amount := null;
1971 END IF;
1972
1973 IF l_funding_in_rec.currency_code = OKE_API.G_MISS_CHAR THEN
1974 l_funding_in_rec.currency_code := null;
1975 END IF;
1976
1977 IF l_funding_in_rec.agreement_number = OKE_API.G_MISS_CHAR THEN
1978 l_funding_in_rec.agreement_number := null;
1979 END IF;
1980
1981 IF l_funding_in_rec.funding_status = OKE_API.G_MISS_CHAR THEN
1982 l_funding_in_rec.funding_status := null;
1983 END IF;
1984
1985 IF l_funding_in_rec.hard_limit = OKE_API.G_MISS_NUM THEN
1986 l_funding_in_rec.hard_limit := null;
1987 END IF;
1988
1989 IF l_funding_in_rec.k_conversion_type = OKE_API.G_MISS_CHAR THEN
1990 l_funding_in_rec.k_conversion_type := null;
1991 END IF;
1992
1993 IF l_funding_in_rec.k_conversion_date = OKE_API.G_MISS_DATE THEN
1994 l_funding_in_rec.k_conversion_date := null;
1995 END IF;
1996
1997 -- syho, bug 2208979
1998 IF l_funding_in_rec.k_conversion_rate = OKE_API.G_MISS_NUM THEN
1999 l_funding_in_rec.k_conversion_rate := null;
2000 END IF;
2001 -- syho, bug 2208979
2002
2003 IF l_funding_in_rec.start_date_active = OKE_API.G_MISS_DATE THEN
2004 l_funding_in_rec.start_date_active := null;
2005 END IF;
2006
2007 IF l_funding_in_rec.end_date_active = OKE_API.G_MISS_DATE THEN
2008 l_funding_in_rec.end_date_active := null;
2009 END IF;
2010 /*
2011 IF l_funding_in_rec.oke_desc_flex_name = OKE_API.G_MISS_CHAR THEN
2012 l_funding_in_rec.oke_desc_flex_name := null;
2013 END IF;
2014 */
2015 IF l_funding_in_rec.oke_attribute_category = OKE_API.G_MISS_CHAR THEN
2016 l_funding_in_rec.oke_attribute_category := null;
2017 END IF;
2018
2019 IF l_funding_in_rec.oke_attribute1 = OKE_API.G_MISS_CHAR THEN
2020 l_funding_in_rec.oke_attribute1 := null;
2021 END IF;
2022
2023 IF l_funding_in_rec.oke_attribute2 = OKE_API.G_MISS_CHAR THEN
2024 l_funding_in_rec.oke_attribute2 := null;
2025 END IF;
2026
2027 IF l_funding_in_rec.oke_attribute3 = OKE_API.G_MISS_CHAR THEN
2028 l_funding_in_rec.oke_attribute3 := null;
2029 END IF;
2030
2031 IF l_funding_in_rec.oke_attribute4 = OKE_API.G_MISS_CHAR THEN
2032 l_funding_in_rec.oke_attribute4 := null;
2033 END IF;
2034
2035 IF l_funding_in_rec.oke_attribute5 = OKE_API.G_MISS_CHAR THEN
2036 l_funding_in_rec.oke_attribute5 := null;
2037 END IF;
2038
2039 IF l_funding_in_rec.oke_attribute6 = OKE_API.G_MISS_CHAR THEN
2040 l_funding_in_rec.oke_attribute6 := null;
2041 END IF;
2042
2043 IF l_funding_in_rec.oke_attribute7 = OKE_API.G_MISS_CHAR THEN
2044 l_funding_in_rec.oke_attribute7 := null;
2045 END IF;
2046
2047 IF l_funding_in_rec.oke_attribute8 = OKE_API.G_MISS_CHAR THEN
2048 l_funding_in_rec.oke_attribute8 := null;
2049 END IF;
2050
2051 IF l_funding_in_rec.oke_attribute9 = OKE_API.G_MISS_CHAR THEN
2052 l_funding_in_rec.oke_attribute9 := null;
2053 END IF;
2054
2055 IF l_funding_in_rec.oke_attribute10 = OKE_API.G_MISS_CHAR THEN
2056 l_funding_in_rec.oke_attribute10 := null;
2057 END IF;
2058
2059 IF l_funding_in_rec.oke_attribute11 = OKE_API.G_MISS_CHAR THEN
2060 l_funding_in_rec.oke_attribute11 := null;
2061 END IF;
2062
2063 IF l_funding_in_rec.oke_attribute12 = OKE_API.G_MISS_CHAR THEN
2064 l_funding_in_rec.oke_attribute12 := null;
2065 END IF;
2066
2067 IF l_funding_in_rec.oke_attribute13 = OKE_API.G_MISS_CHAR THEN
2068 l_funding_in_rec.oke_attribute13 := null;
2069 END IF;
2070
2071 IF l_funding_in_rec.oke_attribute14 = OKE_API.G_MISS_CHAR THEN
2072 l_funding_in_rec.oke_attribute14 := null;
2073 END IF;
2074
2075 IF l_funding_in_rec.oke_attribute15 = OKE_API.G_MISS_CHAR THEN
2076 l_funding_in_rec.oke_attribute15 := null;
2077 END IF;
2078
2079 IF l_funding_in_rec.pa_attribute_category = OKE_API.G_MISS_CHAR THEN
2080 l_funding_in_rec.pa_attribute_category := null;
2081 END IF;
2082
2083 IF l_funding_in_rec.pa_attribute1 = OKE_API.G_MISS_CHAR THEN
2084 l_funding_in_rec.pa_attribute1 := null;
2085 END IF;
2086
2087 IF l_funding_in_rec.pa_attribute2 = OKE_API.G_MISS_CHAR THEN
2088 l_funding_in_rec.pa_attribute2 := null;
2089 END IF;
2090
2091 IF l_funding_in_rec.pa_attribute3 = OKE_API.G_MISS_CHAR THEN
2092 l_funding_in_rec.pa_attribute3 := null;
2093 END IF;
2094
2095 IF l_funding_in_rec.pa_attribute4 = OKE_API.G_MISS_CHAR THEN
2096 l_funding_in_rec.pa_attribute4 := null;
2097 END IF;
2098
2099 IF l_funding_in_rec.pa_attribute5 = OKE_API.G_MISS_CHAR THEN
2100 l_funding_in_rec.pa_attribute5 := null;
2101 END IF;
2102
2103 IF l_funding_in_rec.pa_attribute6 = OKE_API.G_MISS_CHAR THEN
2104 l_funding_in_rec.pa_attribute6 := null;
2105 END IF;
2106
2107 IF l_funding_in_rec.pa_attribute7 = OKE_API.G_MISS_CHAR THEN
2108 l_funding_in_rec.pa_attribute7 := null;
2109 END IF;
2110
2111 IF l_funding_in_rec.pa_attribute8 = OKE_API.G_MISS_CHAR THEN
2112 l_funding_in_rec.pa_attribute8 := null;
2113 END IF;
2114
2115 IF l_funding_in_rec.pa_attribute9 = OKE_API.G_MISS_CHAR THEN
2116 l_funding_in_rec.pa_attribute9 := null;
2117 END IF;
2118
2119 IF l_funding_in_rec.pa_attribute10 = OKE_API.G_MISS_CHAR THEN
2120 l_funding_in_rec.pa_attribute10 := null;
2121 END IF;
2122
2123 IF l_funding_in_rec.pa_attribute11 = OKE_API.G_MISS_CHAR THEN
2124 l_funding_in_rec.pa_attribute11 := null;
2125 END IF;
2126
2127 IF l_funding_in_rec.pa_attribute12 = OKE_API.G_MISS_CHAR THEN
2128 l_funding_in_rec.pa_attribute12 := null;
2129 END IF;
2130
2131 IF l_funding_in_rec.pa_attribute13 = OKE_API.G_MISS_CHAR THEN
2132 l_funding_in_rec.pa_attribute13 := null;
2133 END IF;
2134
2135 IF l_funding_in_rec.pa_attribute14 = OKE_API.G_MISS_CHAR THEN
2136 l_funding_in_rec.pa_attribute14 := null;
2137 END IF;
2138
2139 IF l_funding_in_rec.pa_attribute15 = OKE_API.G_MISS_CHAR THEN
2140 l_funding_in_rec.pa_attribute15 := null;
2141 END IF;
2142
2143 IF l_funding_in_rec.pa_attribute16 = OKE_API.G_MISS_CHAR THEN
2144 l_funding_in_rec.pa_attribute16 := null;
2145 END IF;
2146
2147 IF l_funding_in_rec.pa_attribute17 = OKE_API.G_MISS_CHAR THEN
2148 l_funding_in_rec.pa_attribute17 := null;
2149 END IF;
2150
2151 IF l_funding_in_rec.pa_attribute18 = OKE_API.G_MISS_CHAR THEN
2152 l_funding_in_rec.pa_attribute18 := null;
2153 END IF;
2154
2155 IF l_funding_in_rec.pa_attribute19 = OKE_API.G_MISS_CHAR THEN
2156 l_funding_in_rec.pa_attribute19 := null;
2157 END IF;
2158
2159 IF l_funding_in_rec.pa_attribute20 = OKE_API.G_MISS_CHAR THEN
2160 l_funding_in_rec.pa_attribute20 := null;
2161 END IF;
2162
2163 IF l_funding_in_rec.pa_attribute21 = OKE_API.G_MISS_CHAR THEN
2164 l_funding_in_rec.pa_attribute21 := null;
2165 END IF;
2166
2167 IF l_funding_in_rec.pa_attribute22 = OKE_API.G_MISS_CHAR THEN
2168 l_funding_in_rec.pa_attribute22 := null;
2169 END IF;
2170
2171 IF l_funding_in_rec.pa_attribute23 = OKE_API.G_MISS_CHAR THEN
2172 l_funding_in_rec.pa_attribute23 := null;
2173 END IF;
2174
2175 IF l_funding_in_rec.pa_attribute24 = OKE_API.G_MISS_CHAR THEN
2176 l_funding_in_rec.pa_attribute24 := null;
2177 END IF;
2178
2179 IF l_funding_in_rec.pa_attribute25 = OKE_API.G_MISS_CHAR THEN
2180 l_funding_in_rec.pa_attribute25 := null;
2181 END IF;
2182
2183 IF l_funding_in_rec.revenue_hard_limit = OKE_API.G_MISS_NUM THEN
2184 l_funding_in_rec.revenue_hard_limit := null;
2185 END IF;
2186
2187 IF l_funding_in_rec.agreement_org_id = OKE_API.G_MISS_NUM THEN
2188 l_funding_in_rec.agreement_org_id := null;
2189 END IF;
2190
2191 return(l_funding_in_rec);
2192
2193 END null_funding_out;
2194
2195
2196 --
2197 -- Procedure: validate_populate_rec
2198 --
2199 -- Description: This procedure is used to populate missing attributes of the record for update
2200 --
2201 --
2202
2203 PROCEDURE validate_populate_rec(p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
2204 p_funding_in_rec_out OUT NOCOPY FUNDING_REC_IN_TYPE ,
2205 --p_conversion_rate OUT NOCOPY NUMBER ,
2206 p_previous_amount OUT NOCOPY NUMBER ,
2207 p_flag OUT NOCOPY VARCHAR2
2208 ) is
2209
2210 cursor c_funding_row is
2211 select *
2212 from oke_k_funding_sources
2213 where funding_source_id = p_funding_in_rec.funding_source_id
2214 FOR UPDATE OF funding_source_id NOWAIT;
2215
2216 l_funding_row c_funding_row%ROWTYPE;
2217
2218 BEGIN
2219
2220 p_flag := 'N';
2221
2222 OPEN c_funding_row;
2223 FETCH c_funding_row into l_funding_row;
2224 CLOSE c_funding_row;
2225
2226 p_funding_in_rec_out := p_funding_in_rec;
2227
2228 IF (p_funding_in_rec_out.object_type = OKE_API.G_MISS_CHAR) THEN
2229 p_funding_in_rec_out.object_type := l_funding_row.object_type;
2230
2231 ELSIF (nvl(p_funding_in_rec_out.object_type, '-99') <> l_funding_row.object_type) THEN
2232
2233 OKE_API.set_message(p_app_name => G_APP_NAME ,
2234 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2235 p_token1 => 'VALUE' ,
2236 p_token1_value => 'object_type'
2237 );
2238
2239 RAISE G_EXCEPTION_HALT_VALIDATION;
2240
2241 END IF;
2242
2243 IF (p_funding_in_rec_out.object_id = OKE_API.G_MISS_NUM) THEN
2244 p_funding_in_rec_out.object_id := l_funding_row.object_id;
2245
2246 ELSIF (nvl(p_funding_in_rec_out.object_id, -99) <> l_funding_row.object_id) THEN
2247
2248 OKE_API.set_message(p_app_name => G_APP_NAME ,
2249 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2250 p_token1 => 'VALUE' ,
2251 p_token1_value => 'object_id'
2252 );
2253
2254 RAISE G_EXCEPTION_HALT_VALIDATION;
2255
2256 END IF;
2257
2258 IF (p_funding_in_rec_out.pool_party_id = OKE_API.G_MISS_NUM) THEN
2259 p_funding_in_rec_out.pool_party_id := l_funding_row.pool_party_id;
2260
2261 ELSIF (nvl(p_funding_in_rec_out.pool_party_id, -99) <> nvl(l_funding_row.pool_party_id, -99)) THEN
2262
2263 OKE_API.set_message(p_app_name => G_APP_NAME ,
2264 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2265 p_token1 => 'VALUE' ,
2266 p_token1_value => 'pool_party_id'
2267 );
2268
2269 RAISE G_EXCEPTION_HALT_VALIDATION;
2270
2271 END IF;
2272
2273 IF (p_funding_in_rec_out.k_party_id = OKE_API.G_MISS_NUM) THEN
2274 p_funding_in_rec_out.k_party_id := l_funding_row.k_party_id;
2275
2276 ELSIF (nvl(p_funding_in_rec_out.k_party_id, -99) <> nvl(l_funding_row.k_party_id, -99)) THEN
2277
2278 OKE_API.set_message(p_app_name => G_APP_NAME ,
2279 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2280 p_token1 => 'VALUE' ,
2281 p_token1_value => 'k_party_id'
2282 );
2283
2284 RAISE G_EXCEPTION_HALT_VALIDATION;
2285
2286 END IF;
2287
2288 IF (p_funding_in_rec_out.amount = OKE_API.G_MISS_NUM) THEN
2289 p_funding_in_rec_out.amount := l_funding_row.amount;
2290 END IF;
2291
2292 IF (p_funding_in_rec_out.hard_limit = OKE_API.G_MISS_NUM) THEN
2293 p_funding_in_rec_out.hard_limit := l_funding_row.hard_limit;
2294 END IF;
2295
2296 IF (p_funding_in_rec_out.revenue_hard_limit = OKE_API.G_MISS_NUM) THEN
2297 p_funding_in_rec_out.revenue_hard_limit := l_funding_row.revenue_hard_limit;
2298 END IF;
2299
2300 IF (p_funding_in_rec_out.agreement_org_id = OKE_API.G_MISS_NUM) THEN
2301 p_funding_in_rec_out.agreement_org_id := l_funding_row.agreement_org_id;
2302 END IF;
2303
2304 IF p_funding_in_rec_out.currency_code = OKE_API.G_MISS_CHAR THEN
2305 p_funding_in_rec_out.currency_code := l_funding_row.currency_code;
2306
2307 ELSIF (nvl(upper(p_funding_in_rec_out.currency_code), '-99') <> l_funding_row.currency_code) THEN
2308
2309 OKE_API.set_message(p_app_name => G_APP_NAME ,
2310 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2311 p_token1 => 'VALUE' ,
2312 p_token1_value => 'currency_code'
2313 );
2314
2315 RAISE G_EXCEPTION_HALT_VALIDATION;
2316
2317 END IF;
2318
2319 IF p_funding_in_rec_out.agreement_number = OKE_API.G_MISS_CHAR THEN
2320 p_funding_in_rec_out.agreement_number := l_funding_row.agreement_number;
2321
2322 ELSIF (nvl(p_funding_in_rec_out.agreement_number, '-99') <> nvl(l_funding_row.agreement_number, '-99')) THEN
2323
2324 IF (l_funding_row.agreement_flag = 'Y') THEN
2325
2326 OKE_API.set_message(p_app_name => G_APP_NAME ,
2327 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2328 p_token1 => 'VALUE' ,
2329 p_token1_value => 'agreement_number'
2330 );
2331
2332 RAISE G_EXCEPTION_HALT_VALIDATION;
2333
2334 END IF;
2335
2336 END IF;
2337
2338 IF p_funding_in_rec_out.k_conversion_type = OKE_API.G_MISS_CHAR THEN
2339 p_funding_in_rec_out.k_conversion_type := l_funding_row.k_conversion_type;
2340 END IF;
2341
2342 IF p_funding_in_rec_out.k_conversion_date = OKE_API.G_MISS_DATE THEN
2343 p_funding_in_rec_out.k_conversion_date := l_funding_row.k_conversion_date;
2344 END IF;
2345
2346 -- syho, bug 2208979
2347 IF p_funding_in_rec_out.k_conversion_rate = OKE_API.G_MISS_NUM THEN
2348 p_funding_in_rec_out.k_conversion_rate := l_funding_row.k_conversion_rate;
2349 END IF;
2350
2351 IF (upper(p_funding_in_rec_out.k_conversion_type) <> 'USER') AND
2352 (nvl(to_char(p_funding_in_rec_out.k_conversion_date, 'YYYYMMDD'), '19000101') <> nvl(to_char(l_funding_row.k_conversion_date, 'YYYYMMDD'), '19000101') OR
2353 nvl(p_funding_in_rec_out.k_conversion_type, '-99') <> nvl(l_funding_row.k_conversion_type, '-99')) AND
2354 (p_funding_in_rec_out.k_conversion_rate is not null) THEN
2355
2356 OKE_API.set_message(p_app_name => G_APP_NAME ,
2357 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2358 p_token1 => 'VALUE' ,
2359 p_token1_value => 'k_conversion_rate'
2360 );
2361
2362 RAISE G_EXCEPTION_HALT_VALIDATION;
2363
2364 END IF;
2365 -- syho, bug 2208979
2366
2367 IF (nvl(to_char(p_funding_in_rec_out.k_conversion_date, 'YYYYMMDD'), '19000101') <> nvl(to_char(l_funding_row.k_conversion_date, 'YYYYMMDD'), '19000101')) OR
2368 (nvl(p_funding_in_rec_out.k_conversion_type, '-99') <> nvl(l_funding_row.k_conversion_type, '-99')) OR
2369 (nvl(p_funding_in_rec_out.k_conversion_rate, -99) <> nvl(l_funding_row.k_conversion_rate, -99)) THEN
2370
2371 p_flag := 'Y';
2372
2373 END IF;
2374
2375 IF p_funding_in_rec_out.funding_status = OKE_API.G_MISS_CHAR THEN
2376 p_funding_in_rec_out.funding_status := l_funding_row.funding_status;
2377 END IF;
2378
2379 IF p_funding_in_rec_out.start_date_active = OKE_API.G_MISS_DATE THEN
2380 p_funding_in_rec_out.start_date_active := l_funding_row.start_date_active;
2381 END IF;
2382
2383 IF p_funding_in_rec_out.end_date_active = OKE_API.G_MISS_DATE THEN
2384 p_funding_in_rec_out.end_date_active := l_funding_row.end_date_active;
2385 END IF;
2386 /*
2387 IF p_funding_in_rec_out.oke_desc_flex_name = OKE_API.G_MISS_CHAR THEN
2388 p_funding_in_rec_out.oke_desc_flex_name := null;
2389 END IF;
2390 */
2391 IF p_funding_in_rec_out.oke_attribute_category = OKE_API.G_MISS_CHAR THEN
2392 p_funding_in_rec_out.oke_attribute_category := l_funding_row.attribute_category;
2393 END IF;
2394
2395 IF p_funding_in_rec_out.oke_attribute1 = OKE_API.G_MISS_CHAR THEN
2396 p_funding_in_rec_out.oke_attribute1 := l_funding_row.attribute1;
2397 END IF;
2398
2399 IF p_funding_in_rec_out.oke_attribute2 = OKE_API.G_MISS_CHAR THEN
2400 p_funding_in_rec_out.oke_attribute2 := l_funding_row.attribute2;
2401 END IF;
2402
2403 IF p_funding_in_rec_out.oke_attribute3 = OKE_API.G_MISS_CHAR THEN
2404 p_funding_in_rec_out.oke_attribute3 := l_funding_row.attribute3;
2405 END IF;
2406
2407 IF p_funding_in_rec_out.oke_attribute4 = OKE_API.G_MISS_CHAR THEN
2408 p_funding_in_rec_out.oke_attribute4 := l_funding_row.attribute4;
2409 END IF;
2410
2411 IF p_funding_in_rec_out.oke_attribute5 = OKE_API.G_MISS_CHAR THEN
2412 p_funding_in_rec_out.oke_attribute5 := l_funding_row.attribute5;
2413 END IF;
2414
2415 IF p_funding_in_rec_out.oke_attribute6 = OKE_API.G_MISS_CHAR THEN
2416 p_funding_in_rec_out.oke_attribute6 := l_funding_row.attribute6;
2417 END IF;
2418
2419 IF p_funding_in_rec_out.oke_attribute7 = OKE_API.G_MISS_CHAR THEN
2420 p_funding_in_rec_out.oke_attribute7 := l_funding_row.attribute7;
2421 END IF;
2422
2423 IF p_funding_in_rec_out.oke_attribute8 = OKE_API.G_MISS_CHAR THEN
2424 p_funding_in_rec_out.oke_attribute8 := l_funding_row.attribute8;
2425 END IF;
2426
2427 IF p_funding_in_rec_out.oke_attribute9 = OKE_API.G_MISS_CHAR THEN
2428 p_funding_in_rec_out.oke_attribute9 := l_funding_row.attribute9;
2429 END IF;
2430
2431 IF p_funding_in_rec_out.oke_attribute10 = OKE_API.G_MISS_CHAR THEN
2432 p_funding_in_rec_out.oke_attribute10 := l_funding_row.attribute10;
2433 END IF;
2434
2435 IF p_funding_in_rec_out.oke_attribute11 = OKE_API.G_MISS_CHAR THEN
2436 p_funding_in_rec_out.oke_attribute11 := l_funding_row.attribute11;
2437 END IF;
2438
2439 IF p_funding_in_rec_out.oke_attribute12 = OKE_API.G_MISS_CHAR THEN
2440 p_funding_in_rec_out.oke_attribute12 := l_funding_row.attribute12;
2441 END IF;
2442
2443 IF p_funding_in_rec_out.oke_attribute13 = OKE_API.G_MISS_CHAR THEN
2444 p_funding_in_rec_out.oke_attribute13 := l_funding_row.attribute13;
2445 END IF;
2446
2447 IF p_funding_in_rec_out.oke_attribute14 = OKE_API.G_MISS_CHAR THEN
2448 p_funding_in_rec_out.oke_attribute14 := l_funding_row.attribute14;
2449 END IF;
2450
2451 IF p_funding_in_rec_out.oke_attribute15 = OKE_API.G_MISS_CHAR THEN
2452 p_funding_in_rec_out.oke_attribute15 := l_funding_row.attribute15;
2453 END IF;
2454
2455 IF p_funding_in_rec_out.pa_attribute_category = OKE_API.G_MISS_CHAR THEN
2456 p_funding_in_rec_out.pa_attribute_category := l_funding_row.pa_attribute_category;
2457 END IF;
2458
2459 IF p_funding_in_rec_out.pa_attribute1 = OKE_API.G_MISS_CHAR THEN
2460 p_funding_in_rec_out.pa_attribute1 := l_funding_row.pa_attribute1;
2461 END IF;
2462
2463 IF p_funding_in_rec_out.pa_attribute2 = OKE_API.G_MISS_CHAR THEN
2464 p_funding_in_rec_out.pa_attribute2 := l_funding_row.pa_attribute2;
2465 END IF;
2466
2467 IF p_funding_in_rec_out.pa_attribute3 = OKE_API.G_MISS_CHAR THEN
2468 p_funding_in_rec_out.pa_attribute3 := l_funding_row.pa_attribute3;
2469 END IF;
2470
2471 IF p_funding_in_rec_out.pa_attribute4 = OKE_API.G_MISS_CHAR THEN
2472 p_funding_in_rec_out.pa_attribute4 := l_funding_row.pa_attribute4;
2473 END IF;
2474
2475 IF p_funding_in_rec_out.pa_attribute5 = OKE_API.G_MISS_CHAR THEN
2476 p_funding_in_rec_out.pa_attribute5 := l_funding_row.pa_attribute5;
2477 END IF;
2478
2479 IF p_funding_in_rec_out.pa_attribute6 = OKE_API.G_MISS_CHAR THEN
2480 p_funding_in_rec_out.pa_attribute6 := l_funding_row.pa_attribute6;
2481 END IF;
2482
2483 IF p_funding_in_rec_out.pa_attribute7 = OKE_API.G_MISS_CHAR THEN
2484 p_funding_in_rec_out.pa_attribute7 := l_funding_row.pa_attribute7;
2485 END IF;
2486
2487 IF p_funding_in_rec_out.pa_attribute8 = OKE_API.G_MISS_CHAR THEN
2488 p_funding_in_rec_out.pa_attribute8 := l_funding_row.pa_attribute8;
2489 END IF;
2490
2491 IF p_funding_in_rec_out.pa_attribute9 = OKE_API.G_MISS_CHAR THEN
2492 p_funding_in_rec_out.pa_attribute9 := l_funding_row.pa_attribute9;
2493 END IF;
2494
2495 IF p_funding_in_rec_out.pa_attribute10 = OKE_API.G_MISS_CHAR THEN
2496 p_funding_in_rec_out.pa_attribute10 := l_funding_row.pa_attribute10;
2497 END IF;
2498
2499 IF p_funding_in_rec_out.pa_attribute11 = OKE_API.G_MISS_CHAR THEN
2500 p_funding_in_rec_out.pa_attribute11 := l_funding_row.pa_attribute11;
2501 END IF;
2502
2503 IF p_funding_in_rec_out.pa_attribute12 = OKE_API.G_MISS_CHAR THEN
2504 p_funding_in_rec_out.pa_attribute12 := l_funding_row.pa_attribute12;
2505 END IF;
2506
2507 IF p_funding_in_rec_out.pa_attribute13 = OKE_API.G_MISS_CHAR THEN
2508 p_funding_in_rec_out.pa_attribute13 := l_funding_row.pa_attribute13;
2509 END IF;
2510
2511 IF p_funding_in_rec_out.pa_attribute14 = OKE_API.G_MISS_CHAR THEN
2512 p_funding_in_rec_out.pa_attribute14 := l_funding_row.pa_attribute14;
2513 END IF;
2514
2515 IF p_funding_in_rec_out.pa_attribute15 = OKE_API.G_MISS_CHAR THEN
2516 p_funding_in_rec_out.pa_attribute15 := l_funding_row.pa_attribute15;
2517 END IF;
2518
2519 IF p_funding_in_rec_out.pa_attribute16 = OKE_API.G_MISS_CHAR THEN
2520 p_funding_in_rec_out.pa_attribute16 := l_funding_row.pa_attribute16;
2521 END IF;
2522
2523 IF p_funding_in_rec_out.pa_attribute17 = OKE_API.G_MISS_CHAR THEN
2524 p_funding_in_rec_out.pa_attribute17 := l_funding_row.pa_attribute17;
2525 END IF;
2526
2527 IF p_funding_in_rec_out.pa_attribute18 = OKE_API.G_MISS_CHAR THEN
2528 p_funding_in_rec_out.pa_attribute18 := l_funding_row.pa_attribute18;
2529 END IF;
2530
2531 IF p_funding_in_rec_out.pa_attribute19 = OKE_API.G_MISS_CHAR THEN
2532 p_funding_in_rec_out.pa_attribute19 := l_funding_row.pa_attribute19;
2533 END IF;
2534
2535 IF p_funding_in_rec_out.pa_attribute20 = OKE_API.G_MISS_CHAR THEN
2536 p_funding_in_rec_out.pa_attribute20 := l_funding_row.pa_attribute20;
2537 END IF;
2538
2539 IF p_funding_in_rec_out.pa_attribute21 = OKE_API.G_MISS_CHAR THEN
2540 p_funding_in_rec_out.pa_attribute21 := l_funding_row.pa_attribute21;
2541 END IF;
2542
2543 IF p_funding_in_rec_out.pa_attribute22 = OKE_API.G_MISS_CHAR THEN
2544 p_funding_in_rec_out.pa_attribute22 := l_funding_row.pa_attribute22;
2545 END IF;
2546
2547 IF p_funding_in_rec_out.pa_attribute23 = OKE_API.G_MISS_CHAR THEN
2548 p_funding_in_rec_out.pa_attribute23 := l_funding_row.pa_attribute23;
2549 END IF;
2550
2551 IF p_funding_in_rec_out.pa_attribute24 = OKE_API.G_MISS_CHAR THEN
2552 p_funding_in_rec_out.pa_attribute24 := l_funding_row.pa_attribute24;
2553 END IF;
2554
2555 IF p_funding_in_rec_out.pa_attribute25 = OKE_API.G_MISS_CHAR THEN
2556 p_funding_in_rec_out.pa_attribute25 := l_funding_row.pa_attribute25;
2557 END IF;
2558
2559 p_previous_amount := l_funding_row.previous_amount;
2560 --p_previous_amount := l_funding_row.amount;
2561 -- p_conversion_rate := l_funding_row.k_conversion_rate;
2562
2563 END validate_populate_rec;
2564
2565
2566 --
2567 -- Procedure: validate_parameters
2568 --
2569 -- Description: This procedure is used to validate the pass-in parameters
2570 --
2571 --
2572
2573 PROCEDURE validate_parameters(p_pool_party_id NUMBER ,
2574 p_party_id NUMBER ,
2575 -- p_source_currency VARCHAR2 ,
2576 p_agreement_id NUMBER ,
2577 p_conversion_type VARCHAR2 ,
2578 -- p_pa_conversion_type VARCHAR2 ,
2579 p_k_header_id NUMBER
2580 ) is
2581
2582 l_return_status VARCHAR2(1);
2583
2584 BEGIN
2585
2586 --
2587 -- Validate k_header_id
2588 --
2589
2590 validate_object_id(p_object_id => p_k_header_id ,
2591 p_return_status => l_return_status
2592 );
2593
2594 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2595
2596 RAISE G_EXCEPTION_HALT_VALIDATION;
2597
2598 END IF;
2599
2600 --
2601 -- Validate currency_code
2602 --
2603 /*
2604 validate_currency_code(p_currency_code => p_source_currency ,
2605 p_return_status => l_return_status
2606 );
2607
2608 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2609
2610 RAISE G_EXCEPTION_HALT_VALIDATION;
2611
2612 END IF;
2613 */
2614 --
2615 -- Validate conversion_type
2616 --
2617
2618 validate_conversion_type(p_conversion_type => p_conversion_type ,
2619 p_return_status => l_return_status
2620 );
2621
2622 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2623
2624 RAISE G_EXCEPTION_HALT_VALIDATION;
2625
2626 END IF;
2627 /*
2628 validate_conversion_type(p_conversion_type => p_pa_conversion_type ,
2629 p_return_status => l_return_status
2630 );
2631
2632 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2633
2634 RAISE G_EXCEPTION_HALT_VALIDATION;
2635
2636 END IF;
2637 */
2638 --
2639 -- Validate k_party_id
2640 --
2641
2642 validate_k_party_id(p_k_header_id => p_k_header_id ,
2643 p_k_party_id => p_party_id ,
2644 p_return_status => l_return_status
2645 );
2646
2647 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2648
2649 RAISE G_EXCEPTION_HALT_VALIDATION;
2650
2651 END IF;
2652
2653 --
2654 -- validate pool_party_id
2655 --
2656
2657 validate_pool_party_id(p_pool_party_id => p_pool_party_id ,
2658 p_return_status => l_return_status
2659 );
2660
2661 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2662
2663 RAISE G_EXCEPTION_HALT_VALIDATION;
2664
2665 END IF;
2666
2667 --
2668 -- Validate agreement_id and check if all projects in project fundings are under a valid hierarchy
2669 --
2670
2671 validate_agreement_id(p_agreement_id => p_agreement_id ,
2672 p_object_id => p_k_header_id ,
2673 p_return_status => l_return_status
2674 );
2675
2676 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2677
2678 RAISE G_EXCEPTION_HALT_VALIDATION;
2679
2680 END IF;
2681
2682 END validate_parameters;
2683
2684
2685 --
2686 -- Procedure: validate_agreement_party
2687 --
2688 -- Description: This procedure is used to get the agreement attributes
2689 --
2690 --
2691
2692 PROCEDURE validate_agreement_party(p_pool_party_id NUMBER ,
2693 p_party_id NUMBER ,
2694 p_customer_id NUMBER ,
2695 p_expiration_date DATE ,
2696 -- p_update_flag VARCHAR2 ,
2697 p_currency_code OUT NOCOPY VARCHAR2 ,
2698 p_start_date OUT NOCOPY DATE
2699 --p_currency_code OUT VARCHAR2
2700 ) is
2701
2702 cursor c_party is
2703 select 'x'
2704 from hz_cust_accounts
2705 where cust_account_id = p_customer_id
2706 and party_id = p_party_id;
2707
2708 cursor c_pool_party is
2709 select currency_code, start_date_active
2710 from oke_pool_parties
2711 where pool_party_id = p_pool_party_id
2712 and party_id = p_party_id
2713 and ((start_date_active is null) or
2714 (p_expiration_date is not null and
2715 nvl(start_date_active, p_expiration_date) <= p_expiration_date)
2716 or (p_expiration_date is null))
2717 -- and ((end_date_active is null) or
2718 -- (p_expiration_date is not null and
2719 -- nvl(to_char(end_date_active, 'YYYYMMDD'), '19000101') >= nvl(to_char(p_expiration_date, 'YYYYMMDD'), '19000101')))
2720 -- and ((p_expiration_date is not null) and nvl(to_char(end_date_active, 'YYYYMMDD'), '19000101') >= nvl(to_char(p_expiration_date, 'YYYYMMDD'), '19000101')) or
2721 -- end_date_active is null)
2722 -- and nvl(end_date_active, nvl(p_expiration_date, sysdate)) >= nvl(p_expiration_date, sysdate)
2723 FOR UPDATE OF pool_party_id NOWAIT;
2724
2725 l_dummy_value VARCHAR2(1) := '?';
2726 -- l_pool_currency VARCHAR2(15);
2727
2728 BEGIN
2729
2730 OPEN c_party;
2731 FETCH c_party into l_dummy_value;
2732 CLOSE c_party;
2733
2734 IF (l_dummy_value = '?') THEN
2735
2736 OKE_API.set_message(p_app_name => G_APP_NAME ,
2737 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2738 p_token1 => 'VALUE' ,
2739 p_token1_value => 'party_id'
2740 );
2741
2742 RAISE G_EXCEPTION_HALT_VALIDATION;
2743
2744 END IF;
2745
2746 IF (p_pool_party_id is not null) THEN
2747
2748 OPEN c_pool_party;
2749 FETCH c_pool_party into p_currency_code, p_start_date;
2750
2751 IF (c_pool_party%NOTFOUND) THEN
2752
2753 OKE_API.set_message(p_app_name => G_APP_NAME ,
2754 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2755 p_token1 => 'VALUE' ,
2756 p_token1_value => 'pool_party_id and party_id'
2757 );
2758
2759 CLOSE c_pool_party;
2760
2761 RAISE G_EXCEPTION_HALT_VALIDATION;
2762
2763 END IF;
2764
2765 CLOSE c_pool_party;
2766
2767 END IF;
2768
2769 /*
2770 IF (p_pool_party_id is not null) THEN
2771
2772 OPEN c_pool_party;
2773 FETCH c_pool_party into l_pool_currency;
2774
2775 IF (c_pool_party%NOTFOUND) THEN
2776
2777 OKE_API.set_message(p_app_name => G_APP_NAME ,
2778 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2779 p_token1 => 'VALUE' ,
2780 p_token1_value => 'pool_party_id and party_id'
2781 );
2782
2783 CLOSE c_pool_party;
2784
2785 RAISE G_EXCEPTION_HALT_VALIDATION;
2786
2787 END IF;
2788
2789 CLOSE c_pool_party;
2790
2791 IF (l_pool_currency <> p_source_currency) THEN
2792
2793 OKE_API.set_message(p_app_name => G_APP_NAME ,
2794 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2795 p_token1 => 'VALUE' ,
2796 p_token1_value => 'pool_party_id'
2797 );
2798
2799 RAISE G_EXCEPTION_HALT_VALIDATION;
2800
2801 END IF;
2802
2803 END IF;
2804 */
2805 EXCEPTION
2806 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2807 IF c_pool_party%ISOPEN THEN
2808 CLOSE c_pool_party;
2809 END IF;
2810
2811 IF c_party%ISOPEN THEN
2812 CLOSE c_party;
2813 END IF;
2814
2815 raise G_EXCEPTION_HALT_VALIDATION;
2816
2817 WHEN OTHERS THEN
2818 OKE_API.set_message(p_app_name => G_APP_NAME ,
2819 p_msg_name => G_UNEXPECTED_ERROR ,
2820 p_token1 => G_SQLCODE_TOKEN ,
2821 p_token1_value => SQLCODE ,
2822 p_token2 => G_SQLERRM_TOKEN ,
2823 p_token2_value => SQLERRM
2824 );
2825
2826 IF c_pool_party%ISOPEN THEN
2827 CLOSE c_pool_party;
2828 END IF;
2829
2830 IF c_party%ISOPEN THEN
2831 CLOSE c_party;
2832 END IF;
2833
2834 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2835
2836 END validate_agreement_party;
2837
2838
2839
2840 --
2841 -- Public Procedures and Funtions
2842 --
2843
2844
2845 --
2846 -- Procedure create_funding
2847 --
2848 -- Description: This procedure is used to insert record in OKE_K_FUNDING_SOURCES table
2849 --
2850 -- Calling subprograms: OKE_API.start_activity
2851 -- OKE_API.end_activity
2852 -- OKE_FUNDINGSOURCE_PVT.insert_row
2853 -- null_funding_out
2854 -- validate_attributes
2855 -- validate_record
2856 --
2857
2858 PROCEDURE create_funding(p_api_version IN NUMBER ,
2859 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
2860 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
2861 p_msg_count OUT NOCOPY NUMBER ,
2862 p_msg_data OUT NOCOPY VARCHAR2 ,
2863 p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
2864 p_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
2865 p_return_status OUT NOCOPY VARCHAR2
2866 ) is
2867
2868 l_zero CONSTANT NUMBER := 0;
2869 l_api_name VARCHAR2(20) := 'create_funding';
2870 l_rowid VARCHAR2(30);
2871 l_return_status VARCHAR2(1);
2872 l_funding_in_rec FUNDING_REC_IN_TYPE;
2873 -- l_conversion_rate NUMBER;
2874
2875 BEGIN
2876
2877 --dbms_output.put_line('entering oke_fundsource_pvt.create_funding');
2878 --oke_debug.debug('entering oke_fundsource_pvt.create_funding');
2879
2880 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2881 p_funding_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
2882
2883 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2884 p_pkg_name => G_PKG_NAME ,
2885 p_init_msg_list => p_init_msg_list ,
2886 l_api_version => G_API_VERSION_NUMBER ,
2887 p_api_version => p_api_version ,
2888 p_api_type => '_PVT' ,
2889 x_return_status => p_return_status
2890 );
2891
2892 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2893
2894 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2895
2896 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2897
2898 RAISE OKE_API.G_EXCEPTION_ERROR;
2899
2900 END IF;
2901
2902 --
2903 -- Set Default Null
2904 --
2905
2906 --dbms_output.put_line('set default null values for fields');
2907 --oke_debug.debug('set default null values for fields');
2908
2909 l_funding_in_rec := null_funding_out(p_funding_in_rec => p_funding_in_rec);
2910
2911 --
2912 -- Validate Attributes
2913 --
2914
2915 -- dbms_output.put_line('validate record attributes');
2916 --oke_debug.debug('validate record attributes');
2917
2918 validate_attributes(p_funding_in_rec => l_funding_in_rec);
2919
2920 --
2921 -- Validate record
2922 --
2923
2924 --dbms_output.put_line('validate record');
2925 --oke_debug.debug('validate record');
2926
2927 validate_record(p_funding_in_rec => l_funding_in_rec,
2928 p_flag => 'Y' );
2929
2930 l_funding_in_rec.funding_source_id := get_funding_source_id;
2931 p_funding_out_rec.funding_source_id := l_funding_in_rec.funding_source_id;
2932
2933 -- dbms_output.put_line('calling oke_fundingsource_pvt.insert_row from oke_fundsource_pvt');
2934 --oke_debug.debug('calling oke_fundingsource_pvt.insert_row from oke_fundsource_pvt');
2935
2936 OKE_FUNDINGSOURCE_PVT.insert_row(X_Rowid => l_rowid ,
2937 X_Funding_Source_Id => l_funding_in_rec.funding_source_id ,
2938 X_Pool_Party_Id => l_funding_in_rec.pool_party_id ,
2939 X_K_Party_Id => l_funding_in_rec.k_party_id ,
2940 X_Object_Type => upper(l_funding_in_rec.object_type) ,
2941 X_Object_Id => l_funding_in_rec.object_id ,
2942 X_Agreement_Number => l_funding_in_rec.agreement_number ,
2943 X_Currency_Code => upper(l_funding_in_rec.currency_code) ,
2944 X_Amount => l_funding_in_rec.amount ,
2945 X_Initial_Amount => l_funding_in_rec.amount ,
2946 X_Previous_Amount => l_zero ,
2947 X_Funding_Status => upper(l_funding_in_rec.funding_status) ,
2948 X_Hard_Limit => l_funding_in_rec.hard_limit ,
2949 X_K_Conversion_Type => l_funding_in_rec.k_conversion_type ,
2950 X_K_Conversion_Date => l_funding_in_rec.k_conversion_date ,
2951 X_K_Conversion_Rate => l_funding_in_rec.k_conversion_rate ,
2952 X_Start_Date_Active => l_funding_in_rec.start_date_active ,
2953 X_End_Date_Active => l_funding_in_rec.end_date_active ,
2954 X_Last_Update_Date => sysdate ,
2955 X_Last_Updated_By => L_USERID ,
2956 X_Creation_Date => sysdate ,
2957 X_Created_By => L_USERID ,
2958 X_Last_Update_Login => L_LOGINID ,
2959 -- X_Attribute_Category => upper(l_funding_in_rec.oke_attribute_category) ,
2960 X_Attribute_Category => l_funding_in_rec.oke_attribute_category ,
2961 X_Attribute1 => l_funding_in_rec.oke_attribute1 ,
2962 X_Attribute2 => l_funding_in_rec.oke_attribute2 ,
2963 X_Attribute3 => l_funding_in_rec.oke_attribute3 ,
2964 X_Attribute4 => l_funding_in_rec.oke_attribute4 ,
2965 X_Attribute5 => l_funding_in_rec.oke_attribute5 ,
2966 X_Attribute6 => l_funding_in_rec.oke_attribute6 ,
2967 X_Attribute7 => l_funding_in_rec.oke_attribute7 ,
2968 X_Attribute8 => l_funding_in_rec.oke_attribute8 ,
2969 X_Attribute9 => l_funding_in_rec.oke_attribute9 ,
2970 X_Attribute10 => l_funding_in_rec.oke_attribute10 ,
2971 X_Attribute11 => l_funding_in_rec.oke_attribute11 ,
2972 X_Attribute12 => l_funding_in_rec.oke_attribute12 ,
2973 X_Attribute13 => l_funding_in_rec.oke_attribute13 ,
2974 X_Attribute14 => l_funding_in_rec.oke_attribute14 ,
2975 X_Attribute15 => l_funding_in_rec.oke_attribute15 ,
2976 -- X_PA_Attribute_Category => upper(l_funding_in_rec.pa_attribute_category) ,
2977 X_PA_Attribute_Category => l_funding_in_rec.pa_attribute_category ,
2978 X_PA_Attribute1 => l_funding_in_rec.pa_attribute1 ,
2979 X_PA_Attribute2 => l_funding_in_rec.pa_attribute2 ,
2980 X_PA_Attribute3 => l_funding_in_rec.pa_attribute3 ,
2981 X_PA_Attribute4 => l_funding_in_rec.pa_attribute4 ,
2982 X_PA_Attribute5 => l_funding_in_rec.pa_attribute5 ,
2983 X_PA_Attribute6 => l_funding_in_rec.pa_attribute6 ,
2984 X_PA_Attribute7 => l_funding_in_rec.pa_attribute7 ,
2985 X_PA_Attribute8 => l_funding_in_rec.pa_attribute8 ,
2986 X_PA_Attribute9 => l_funding_in_rec.pa_attribute9 ,
2987 X_PA_Attribute10 => l_funding_in_rec.pa_attribute10 ,
2988 X_PA_Attribute11 => l_funding_in_rec.pa_attribute11 ,
2989 X_PA_Attribute12 => l_funding_in_rec.pa_attribute12 ,
2990 X_PA_Attribute13 => l_funding_in_rec.pa_attribute13 ,
2991 X_PA_Attribute14 => l_funding_in_rec.pa_attribute14 ,
2992 X_PA_Attribute15 => l_funding_in_rec.pa_attribute15 ,
2993 X_PA_Attribute16 => l_funding_in_rec.pa_attribute16 ,
2994 X_PA_Attribute17 => l_funding_in_rec.pa_attribute17 ,
2995 X_PA_Attribute18 => l_funding_in_rec.pa_attribute18 ,
2996 X_PA_Attribute19 => l_funding_in_rec.pa_attribute19 ,
2997 X_PA_Attribute20 => l_funding_in_rec.pa_attribute20 ,
2998 X_PA_Attribute21 => l_funding_in_rec.pa_attribute21 ,
2999 X_PA_Attribute22 => l_funding_in_rec.pa_attribute22 ,
3000 X_PA_Attribute23 => l_funding_in_rec.pa_attribute23 ,
3001 X_PA_Attribute24 => l_funding_in_rec.pa_attribute24 ,
3002 X_PA_Attribute25 => l_funding_in_rec.pa_attribute25 ,
3003 X_Revenue_Hard_Limit => l_funding_in_rec.revenue_hard_limit ,
3004 X_Agreement_Org_id => l_funding_in_rec.agreement_org_id
3005 );
3006
3007 IF FND_API.to_boolean(p_commit) THEN
3008
3009 COMMIT WORK;
3010
3011 END IF;
3012
3013 -- dbms_output.put_line('finished oke_fundsource_pvt.create_funding');
3014 --oke_debug.debug('finished oke_fundsource_pvt.create_funding');
3015
3016 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
3017 x_msg_data => p_msg_data
3018 );
3019
3020 EXCEPTION
3021 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
3022 p_funding_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
3023 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3024 p_pkg_name => G_PKG_NAME ,
3025 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
3026 x_msg_count => p_msg_count ,
3027 x_msg_data => p_msg_data ,
3028 p_api_type => '_PVT'
3029 );
3030
3031 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3032 p_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
3033 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3034 p_pkg_name => G_PKG_NAME ,
3035 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
3036 x_msg_count => p_msg_count ,
3037 x_msg_data => p_msg_data ,
3038 p_api_type => '_PVT'
3039 );
3040
3041 WHEN OTHERS THEN
3042 p_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
3043 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3044 p_pkg_name => G_PKG_NAME ,
3045 p_exc_name => 'OTHERS' ,
3046 x_msg_count => p_msg_count ,
3047 x_msg_data => p_msg_data ,
3048 p_api_type => '_PVT'
3049 );
3050
3051 END create_funding;
3052
3053
3054 --
3055 -- Procedure: update_funding
3056 --
3057 -- Description: This procedure is used to update record in OKE_FUNDING_SOURCES table
3058 --
3059 -- Calling subprograms: OKE_API.start_activity
3060 -- OKE_API.end_activity
3061 -- OKE_FUNDINGSOURCE_PVT.update_row
3062 -- validate_funding_source_id
3063 -- validate_populate_rec
3064 -- validate_attributes
3065 -- validate_record
3066 --
3067
3068 PROCEDURE update_funding(p_api_version IN NUMBER ,
3069 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
3070 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
3071 p_msg_count OUT NOCOPY NUMBER ,
3072 p_msg_data OUT NOCOPY VARCHAR2 ,
3073 p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
3074 p_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
3075 p_return_status OUT NOCOPY VARCHAR2
3076 ) is
3077
3078 l_api_name CONSTANT VARCHAR2(40) := 'update_funding';
3079 l_return_status VARCHAR2(1);
3080 l_previous_amount NUMBER;
3081 l_funding_in_rec FUNDING_REC_IN_TYPE;
3082 -- l_conversion_rate NUMBER;
3083 --l_conversion_rate2 NUMBER;
3084 l_rowid VARCHAR2(30);
3085 l_pool_party_id NUMBER;
3086 l_flag VARCHAR2(1);
3087 l_agreement_flag VARCHAR2(1);
3088
3089 BEGIN
3090
3091 --dbms_output.put_line('entering oke_fundsource_pvt.update_funding');
3092 --oke_debug.debug('entering oke_fundsource_pvt.update_funding');
3093
3094 p_return_status := OKE_API.G_RET_STS_SUCCESS;
3095 p_funding_out_rec.funding_source_id := p_funding_in_rec.funding_source_id;
3096 p_funding_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
3097
3098 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
3099 p_pkg_name => G_PKG_NAME ,
3100 p_init_msg_list => p_init_msg_list ,
3101 l_api_version => G_API_VERSION_NUMBER ,
3102 p_api_version => p_api_version ,
3103 p_api_type => '_PVT' ,
3104 x_return_status => p_return_status
3105 );
3106
3107 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3108
3109 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3110
3111 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3112
3113 RAISE OKE_API.G_EXCEPTION_ERROR;
3114
3115 END IF;
3116
3117 --
3118 -- Validate if it is a valid funding_source_id
3119 --
3120
3121 validate_funding_source_id(p_funding_source_id => p_funding_in_rec.funding_source_id ,
3122 p_rowid => l_rowid ,
3123 p_pool_party_id => l_pool_party_id ,
3124 p_agreement_flag => l_agreement_flag
3125 );
3126
3127 --
3128 -- Validate and set the missing value for the fields
3129 --
3130
3131 validate_populate_rec(p_funding_in_rec => p_funding_in_rec ,
3132 p_funding_in_rec_out => l_funding_in_rec ,
3133 --p_conversion_rate => l_conversion_rate ,
3134 p_previous_amount => l_previous_amount ,
3135 p_flag => l_flag
3136 );
3137
3138 --
3139 -- Validate Attributes
3140 --
3141
3142 validate_attributes(p_funding_in_rec => l_funding_in_rec);
3143
3144 --
3145 -- Validate record
3146 --
3147
3148 validate_record(p_funding_in_rec => l_funding_in_rec,
3149 p_flag => l_flag );
3150 -- p_conversion_rate => l_conversion_rate2);
3151
3152 /*
3153 IF (l_flag = 'Y') THEN
3154
3155 l_conversion_rate := l_conversion_rate2;
3156
3157 END IF;
3158 */
3159 OKE_FUNDINGSOURCE_PVT.update_row(X_Funding_Source_Id => l_funding_in_rec.funding_source_id ,
3160 X_Pool_Party_Id => l_funding_in_rec.pool_party_id ,
3161 X_K_Party_Id => l_funding_in_rec.k_party_id ,
3162 X_Amount => l_funding_in_rec.amount ,
3163 X_Previous_Amount => nvl(l_previous_amount, 0) ,
3164 X_Funding_Status => upper(l_funding_in_rec.funding_status) ,
3165 X_agreement_number => l_funding_in_rec.agreement_number ,
3166 X_Hard_Limit => l_funding_in_rec.hard_limit ,
3167 X_K_Conversion_Type => l_funding_in_rec.k_conversion_type ,
3168 X_K_Conversion_Date => l_funding_in_rec.k_conversion_date ,
3169 X_K_Conversion_Rate => l_funding_in_rec.k_conversion_rate ,
3170 X_Start_Date_Active => l_funding_in_rec.start_date_active ,
3171 X_End_Date_Active => l_funding_in_rec.end_date_active ,
3172 X_Last_Update_Date => sysdate ,
3173 X_Last_Updated_By => L_USERID ,
3174 X_Last_Update_Login => L_LOGINID ,
3175 --X_Attribute_Category => upper(l_funding_in_rec.oke_attribute_category) ,
3176 X_Attribute_Category => l_funding_in_rec.oke_attribute_category ,
3177 X_Attribute1 => l_funding_in_rec.oke_attribute1 ,
3178 X_Attribute2 => l_funding_in_rec.oke_attribute2 ,
3179 X_Attribute3 => l_funding_in_rec.oke_attribute3 ,
3180 X_Attribute4 => l_funding_in_rec.oke_attribute4 ,
3181 X_Attribute5 => l_funding_in_rec.oke_attribute5 ,
3182 X_Attribute6 => l_funding_in_rec.oke_attribute6 ,
3183 X_Attribute7 => l_funding_in_rec.oke_attribute7 ,
3184 X_Attribute8 => l_funding_in_rec.oke_attribute8 ,
3185 X_Attribute9 => l_funding_in_rec.oke_attribute9 ,
3186 X_Attribute10 => l_funding_in_rec.oke_attribute10 ,
3187 X_Attribute11 => l_funding_in_rec.oke_attribute11 ,
3188 X_Attribute12 => l_funding_in_rec.oke_attribute12 ,
3189 X_Attribute13 => l_funding_in_rec.oke_attribute13 ,
3190 X_Attribute14 => l_funding_in_rec.oke_attribute14 ,
3191 X_Attribute15 => l_funding_in_rec.oke_attribute15 ,
3192 -- X_PA_Attribute_Category => upper(l_funding_in_rec.pa_attribute_category) ,
3193 X_PA_Attribute_Category => l_funding_in_rec.pa_attribute_category ,
3194 X_PA_Attribute1 => l_funding_in_rec.pa_attribute1 ,
3195 X_PA_Attribute2 => l_funding_in_rec.pa_attribute2 ,
3196 X_PA_Attribute3 => l_funding_in_rec.pa_attribute3 ,
3197 X_PA_Attribute4 => l_funding_in_rec.pa_attribute4 ,
3198 X_PA_Attribute5 => l_funding_in_rec.pa_attribute5 ,
3199 X_PA_Attribute6 => l_funding_in_rec.pa_attribute6 ,
3200 X_PA_Attribute7 => l_funding_in_rec.pa_attribute7 ,
3201 X_PA_Attribute8 => l_funding_in_rec.pa_attribute8 ,
3202 X_PA_Attribute9 => l_funding_in_rec.pa_attribute9 ,
3203 X_PA_Attribute10 => l_funding_in_rec.pa_attribute10 ,
3204 X_PA_Attribute11 => l_funding_in_rec.pa_attribute11 ,
3205 X_PA_Attribute12 => l_funding_in_rec.pa_attribute12 ,
3206 X_PA_Attribute13 => l_funding_in_rec.pa_attribute13 ,
3207 X_PA_Attribute14 => l_funding_in_rec.pa_attribute14 ,
3208 X_PA_Attribute15 => l_funding_in_rec.pa_attribute15 ,
3209 X_PA_Attribute16 => l_funding_in_rec.pa_attribute16 ,
3210 X_PA_Attribute17 => l_funding_in_rec.pa_attribute17 ,
3211 X_PA_Attribute18 => l_funding_in_rec.pa_attribute18 ,
3212 X_PA_Attribute19 => l_funding_in_rec.pa_attribute19 ,
3213 X_PA_Attribute20 => l_funding_in_rec.pa_attribute20 ,
3214 X_PA_Attribute21 => l_funding_in_rec.pa_attribute21 ,
3215 X_PA_Attribute22 => l_funding_in_rec.pa_attribute22 ,
3216 X_PA_Attribute23 => l_funding_in_rec.pa_attribute23 ,
3217 X_PA_Attribute24 => l_funding_in_rec.pa_attribute24 ,
3218 X_PA_Attribute25 => l_funding_in_rec.pa_attribute25 ,
3219 X_Revenue_Hard_Limit => l_funding_in_rec.revenue_hard_limit ,
3220 X_Agreement_Org_id => l_funding_in_rec.agreement_org_id
3221 );
3222
3223 -- dbms_output.put_line('finished oke_fundsource_pvt.update_funding w/ ' || p_return_status);
3224 --oke_debug.debug('finished oke_fundsource_pvt.update_funding w/ ' || p_return_status);
3225
3226 IF FND_API.to_boolean(p_commit) THEN
3227
3228 COMMIT WORK;
3229
3230 END IF;
3231
3232 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
3233 x_msg_data => p_msg_data
3234 );
3235
3236 EXCEPTION
3237 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
3238 p_funding_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
3239 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3240 p_pkg_name => G_PKG_NAME ,
3241 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
3242 x_msg_count => p_msg_count ,
3243 x_msg_data => p_msg_data ,
3244 p_api_type => '_PVT'
3245 );
3246
3247 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3248 p_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
3249 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3250 p_pkg_name => G_PKG_NAME ,
3251 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
3252 x_msg_count => p_msg_count ,
3253 x_msg_data => p_msg_data ,
3254 p_api_type => '_PVT'
3255 );
3256
3257 WHEN OTHERS THEN
3258 p_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
3259 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3260 p_pkg_name => G_PKG_NAME ,
3261 p_exc_name => 'OTHERS' ,
3262 x_msg_count => p_msg_count ,
3263 x_msg_data => p_msg_data ,
3264 p_api_type => '_PVT'
3265 );
3266
3267 END update_funding;
3268
3269
3270
3271 --
3272 -- Procedure: delete_funding
3273 --
3274 -- Description: This procedure is used to delete record in OKE_K_FUNDING_SOURCES table
3275 --
3276 -- Calling subprograms: OKE_API.start_activity
3277 -- OKE_API.end_activity
3278 -- OKE_FUNDINGSOURCE_PVT.delete_row
3279 -- PA_AGREEMENT_PUB.delete_agreement
3280 -- validate_funding_source_id
3281 --
3282
3283 PROCEDURE delete_funding(p_api_version IN NUMBER ,
3284 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
3285 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
3286 p_msg_count OUT NOCOPY NUMBER ,
3287 p_msg_data OUT NOCOPY VARCHAR2 ,
3288 p_funding_source_id IN NUMBER ,
3289 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
3290 p_return_status OUT NOCOPY VARCHAR2
3291 ) is
3292
3293 cursor c_agreement (p_funding_source_id NUMBER,
3294 length NUMBER) is
3295 select pm_agreement_reference, org_id
3296 from pa_agreements_all
3297 where substr(pm_agreement_reference, (-1 * (length + 1)), length + 1) = '-' || TO_CHAR(p_funding_source_id)
3298 and pm_product_code = G_PRODUCT_CODE;
3299
3300 l_agreement c_agreement%ROWTYPE;
3301 l_api_name CONSTANT VARCHAR2(40) := 'delete_funding';
3302 l_return_status VARCHAR2(1);
3303 l_agreement_flag VARCHAR2(1);
3304 l_rowid VARCHAR2(30);
3305 l_pool_party_id NUMBER;
3306 l_length NUMBER;
3307
3308 BEGIN
3309
3310 -- dbms_output.put_line('entering oke_fundsource_pvt.delete_funding');
3311 --oke_debug.debug('entering oke_fundsource_pvt.delete_funding');
3312
3313 p_return_status := OKE_API.G_RET_STS_SUCCESS;
3314
3315 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
3316 p_pkg_name => G_PKG_NAME ,
3317 p_init_msg_list => p_init_msg_list ,
3318 l_api_version => G_API_VERSION_NUMBER ,
3319 p_api_version => p_api_version ,
3320 p_api_type => '_PVT' ,
3321 x_return_status => p_return_status
3322 );
3323
3324 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3325
3326 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3327
3328 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3329
3330 RAISE OKE_API.G_EXCEPTION_ERROR;
3331
3332 END IF;
3333
3334 --
3335 -- Validate if it is a valid funding_source_id
3336 --
3337
3338 validate_funding_source_id(p_funding_source_id => p_funding_source_id ,
3339 p_rowid => l_rowid ,
3340 p_pool_party_id => l_pool_party_id ,
3341 p_agreement_flag => l_agreement_flag
3342 );
3343
3344 --
3345 -- Call OKE_FUNDINGSOURCE_PVT.delete_row to delete the row
3346 --
3347
3348 OKE_FUNDINGSOURCE_PVT.delete_row(x_rowid => l_rowid ,
3349 x_pool_party_id => l_pool_party_id
3350 );
3351
3352 --
3353 -- Delete PA agreements if exist
3354 --
3355
3356 IF (l_agreement_flag = 'Y') THEN
3357
3358 l_length := length(p_funding_source_id);
3359
3360 FOR l_agreement in c_agreement(p_funding_source_id, l_length) LOOP
3361
3362 --
3363 -- Call PA_AGREEMENT_PUB.delete_agreement to delete pa agreement
3364 --
3365
3366 --dbms_output.put_line('pm_agreement_reference '|| l_agreement.pm_agreement_reference);
3367
3368 fnd_client_info.set_org_context(l_agreement.org_id);
3369
3370 -- dbms_output.put_line('calling pa_agreement_pub.delete_agreement from oke_fundsource_pvt');
3371 --oke_debug.debug('calling pa_agreement_pub.delete_agreement from oke_fundsource_pvt');
3372
3373 PA_AGREEMENT_PUB.delete_agreement(p_api_version_number => p_api_version ,
3374 p_commit => OKE_API.G_FALSE ,
3375 p_init_msg_list => OKE_API.G_FALSE ,
3376 p_msg_count => p_msg_count ,
3377 p_msg_data => p_msg_data ,
3378 p_return_status => p_return_status ,
3379 p_pm_product_code => G_PRODUCT_CODE ,
3380 p_pm_agreement_reference => l_agreement.pm_agreement_reference ,
3381 p_agreement_id => null
3382 );
3383
3384 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3385
3386 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3387
3388 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
3389
3390 RAISE OKE_API.G_EXCEPTION_ERROR;
3391
3392 END IF;
3393
3394 END LOOP;
3395
3396 END IF;
3397
3398 --dbms_output.put_line('finished oke_fundsource_pvt.delete_funding w/ ' || p_return_status);
3399 --oke_debug.debug('finished oke_fundsource_pvt.delete_funding w/ ' || p_return_status);
3400
3401 IF FND_API.to_boolean(p_commit) THEN
3402
3403 COMMIT WORK;
3404
3405 END IF;
3406
3407 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
3408 x_msg_data => p_msg_data
3409 );
3410
3411 EXCEPTION
3412 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
3413 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3414 p_pkg_name => G_PKG_NAME ,
3415 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
3416 x_msg_count => p_msg_count ,
3417 x_msg_data => p_msg_data ,
3418 p_api_type => '_PVT'
3419 );
3420
3421 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3422 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3423 p_pkg_name => G_PKG_NAME ,
3424 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
3425 x_msg_count => p_msg_count ,
3426 x_msg_data => p_msg_data ,
3427 p_api_type => '_PVT'
3428 );
3429
3430 WHEN OTHERS THEN
3431 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3432 p_pkg_name => G_PKG_NAME ,
3433 p_exc_name => 'OTHERS' ,
3434 x_msg_count => p_msg_count ,
3435 x_msg_data => p_msg_data ,
3436 p_api_type => '_PVT'
3437 );
3438
3439 END delete_funding;
3440
3441
3442
3443 --
3444 -- Procedure: fetch_create_funding
3445 --
3446 -- Description: This procedure is used by Oracle Form to get the existing agreement record
3447 -- and create a funding record in OKE
3448 --
3449 -- Calling subprograms: OKE_API.start_activity
3450 -- OKE_API.end_activity
3451 -- OKE_FUNDSOURCE_PVT.create_funding
3452 -- OKE_ALLOCATION_PVT.add_allocation
3453 -- OKE_FUNDING_UTIL_PKG.check_single_org
3454 -- OKE_FUNDING_UTIL_PKG.update_source_flag
3455 -- OKE_FUNDING_UTIL_PKG.update_alloc_version
3456 -- validate_pool_party_id
3457 -- get_min_unit
3458 -- lock_agreement
3459 -- update_agreement_reference
3460 -- update_proj_fld_reference
3461 --
3462
3463 PROCEDURE fetch_create_funding(p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
3464 p_api_version IN NUMBER ,
3465 p_msg_count OUT NOCOPY NUMBER ,
3466 p_msg_data OUT NOCOPY VARCHAR2 ,
3467 p_commit IN VARCHAR2:= OKE_API.G_FALSE ,
3468 p_pool_party_id IN NUMBER ,
3469 p_customer_id IN NUMBER ,
3470 p_customer_number IN VARCHAR2 ,
3471 --p_pool_currency IN VARCHAR2 ,
3472 --p_source_currency IN VARCHAR2 ,
3473 p_party_id IN NUMBER ,
3474 p_agreement_id IN NUMBER ,
3475 p_org_id IN NUMBER ,
3476 p_agreement_number IN VARCHAR2 ,
3477 p_agreement_type IN VARCHAR2 ,
3478 p_amount IN NUMBER ,
3479 p_revenue_limit_flag IN VARCHAR2 ,
3480 p_agreement_currency IN VARCHAR2 ,
3481 p_expiration_date IN DATE ,
3482 p_conversion_type IN VARCHAR2 ,
3483 p_conversion_date IN DATE ,
3484 p_conversion_rate IN NUMBER ,
3485 --p_pa_conversion_type IN VARCHAR2 ,
3486 --p_pa_conversion_date IN DATE ,
3487 --p_pa_conversion_rate IN NUMBER ,
3488 p_k_header_id IN NUMBER ,
3489 p_pa_attribute_category IN VARCHAR2 ,
3490 p_pa_attribute1 IN VARCHAR2 ,
3491 p_pa_attribute2 IN VARCHAR2 ,
3492 p_pa_attribute3 IN VARCHAR2 ,
3493 p_pa_attribute4 IN VARCHAR2 ,
3494 p_pa_attribute5 IN VARCHAR2 ,
3495 p_pa_attribute6 IN VARCHAR2 ,
3496 p_pa_attribute7 IN VARCHAR2 ,
3497 p_pa_attribute8 IN VARCHAR2 ,
3498 p_pa_attribute9 IN VARCHAR2 ,
3499 p_pa_attribute10 IN VARCHAR2 ,
3500 p_pa_attribute11 IN VARCHAR2 ,
3501 p_pa_attribute12 IN VARCHAR2 ,
3502 p_pa_attribute13 IN VARCHAR2 ,
3503 p_pa_attribute14 IN VARCHAR2 ,
3504 p_pa_attribute15 IN VARCHAR2 ,
3505 p_pa_attribute16 IN VARCHAR2 ,
3506 p_pa_attribute17 IN VARCHAR2 ,
3507 p_pa_attribute18 IN VARCHAR2 ,
3508 p_pa_attribute19 IN VARCHAR2 ,
3509 p_pa_attribute20 IN VARCHAR2 ,
3510 p_pa_attribute21 IN VARCHAR2 ,
3511 p_pa_attribute22 IN VARCHAR2 ,
3512 p_pa_attribute23 IN VARCHAR2 ,
3513 p_pa_attribute24 IN VARCHAR2 ,
3514 p_pa_attribute25 IN VARCHAR2 ,
3515 p_owning_organization_id IN NUMBER ,
3516 p_invoice_limit_flag IN VARCHAR2 ,
3517 -- p_allow_currency_update IN VARCHAR2 ,
3518 -- p_functional_currency_code IN VARCHAR2 ,
3519 p_funding_source_id OUT NOCOPY NUMBER ,
3520 p_return_status OUT NOCOPY VARCHAR2
3521 ) is
3522
3523 cursor c_project_funding is
3524 select *
3525 from pa_project_fundings
3526 where agreement_id = p_agreement_id
3527 --Begin bug 15980946 change
3528 and budget_type_code in ( 'BASELINE', 'DRAFT')
3529 --End bug 15980946 change
3530 order by allocated_amount desc;
3531
3532 cursor c_pool_party_date is
3533 select start_date_active, end_date_active
3534 from oke_pool_parties
3535 where pool_party_id = p_pool_party_id;
3536
3537 cursor c_ou is
3538 select nvl(allow_funding_across_ou_flag, 'N'),
3539 currency_code
3540 from pa_implementations_all p,
3541 gl_sets_of_books g
3542 where nvl(org_id, -99) = nvl(p_org_id, -99)
3543 and g.set_of_books_id = p.set_of_books_id;
3544
3545 cursor c_across is
3546 select 'x'
3547 from pa_project_fundings f,
3548 pa_projects_all p
3549 where nvl(p.org_id, -99) <> nvl(p_org_id, -99)
3550 and f.project_id = p.project_id
3551 and f.agreement_id = p_agreement_id;
3552
3553 l_return_status VARCHAR2(1);
3554 l_api_name CONSTANT VARCHAR2(30) := 'fetch_create_funding';
3555 -- l_fund_currency VARCHAR2(15);
3556 -- l_min_unit NUMBER;
3557 -- l_allocated_total NUMBER := 0;
3558 -- l_converted_amount NUMBER;
3559 l_funding_in_rec FUNDING_REC_IN_TYPE;
3560 l_funding_out_rec FUNDING_REC_OUT_TYPE;
3561 l_allocation_in_rec OKE_ALLOCATION_PVT.ALLOCATION_REC_IN_TYPE;
3562 l_allocation_out_rec OKE_ALLOCATION_PVT.ALLOCATION_REC_OUT_TYPE;
3563 l_allocation_in_tbl OKE_ALLOCATION_PVT.ALLOCATION_IN_TBL_TYPE;
3564 i NUMBER := 0;
3565 -- l_hard_limit_total NUMBER := 0;
3566 l_project_funding c_project_funding%ROWTYPE;
3567 -- l_orig_org_id NUMBER;
3568 -- l_revenue_hard_limit_total NUMBER := 0;
3569 l_temp_sum NUMBER := 0;
3570 l_start_date DATE;
3571 l_end_date DATE;
3572 l_dummy VARCHAR2(1) := '?';
3573 l_mcb_flag VARCHAR2(1);
3574 l_org_set NUMBER;
3575 l_num_update_flag VARCHAR2(1);
3576 l_ou_currency VARCHAR2(15);
3577
3578 BEGIN
3579
3580 --oke_debug.debug('entering fetch_create_funding..');
3581
3582 p_return_status := OKE_API.G_RET_STS_SUCCESS;
3583
3584 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
3585 p_pkg_name => G_PKG_NAME ,
3586 p_init_msg_list => p_init_msg_list ,
3587 l_api_version => G_API_VERSION_NUMBER ,
3588 p_api_version => p_api_version ,
3589 p_api_type => '_PVT' ,
3590 x_return_status => p_return_status
3591 );
3592
3593 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3594
3595 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3596
3597 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3598
3599 RAISE OKE_API.G_EXCEPTION_ERROR;
3600
3601 END IF;
3602
3603 --oke_debug.debug('oke_api.start_activity finished successfully');
3604
3605 -- l_fund_currency := p_currency_code;
3606
3607 IF (p_pool_party_id is not null) THEN
3608
3609 validate_pool_party_id(p_pool_party_id => p_pool_party_id ,
3610 p_return_status => p_return_status
3611 );
3612
3613 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3614
3615 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3616
3617 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
3618
3619 RAISE OKE_API.G_EXCEPTION_ERROR;
3620
3621 END IF;
3622
3623 -- END IF;
3624
3625 OPEN c_pool_party_date;
3626 FETCH c_pool_party_date INTO l_start_date, l_end_date;
3627 IF (c_pool_party_date%NOTFOUND) THEN
3628 CLOSE c_pool_party_date;
3629 END IF;
3630 CLOSE c_pool_party_date;
3631
3632 END IF;
3633 -- bug 3346170
3634 /*
3635 IF (l_end_date is not null) AND
3636 (p_expiration_date is null or
3637 p_expiration_date > l_end_date) THEN
3638
3639 OKE_API.set_message(p_app_name => G_APP_NAME ,
3640 p_msg_name => 'OKE_AGREEMENT_END_DATE'
3641 );
3642
3643 RAISE OKE_API.G_EXCEPTION_ERROR;
3644
3645 END IF; */
3646 /*
3647 IF (p_agreement_currency <> p_source_currency) THEN
3648
3649 l_min_unit := get_min_unit(p_source_currency);
3650
3651 END IF;
3652 */
3653
3654 --
3655 -- Check funding across OU
3656 --
3657
3658 OPEN c_ou;
3659 FETCH c_ou into l_mcb_flag, l_ou_currency;
3660 CLOSE c_ou;
3661
3662 IF (l_ou_currency <> p_agreement_currency) THEN
3663
3664 l_num_update_flag := 'Y';
3665
3666 ELSE
3667
3668 l_num_update_flag := 'N';
3669
3670 END IF;
3671
3672 IF (l_mcb_flag = 'Y') THEN
3673
3674 OPEN c_across;
3675 FETCH c_across into l_dummy;
3676 CLOSE c_across;
3677
3678 END IF;
3679
3680 --
3681 -- lock agreement record
3682 --
3683
3684 lock_agreement(p_agreement_id => p_agreement_id);
3685
3686 FOR l_project_funding in c_project_funding LOOP
3687 /*
3688 IF (l_min_unit is not null) THEN
3689
3690 l_converted_amount := round(l_project_funding.allocated_amount * (1/p_pa_conversion_rate) / l_min_unit) * l_min_unit;
3691
3692 ELSE
3693
3694 l_converted_amount := l_project_funding.allocated_amount;
3695
3696 END IF; */
3697 -- oke_debug.debug('l_converted_amount ' || l_converted_amount);
3698 -- l_allocated_total := l_allocated_total + l_converted_amount;
3699
3700 --
3701 -- Same up all the positive amount and create a funding source with this amount first
3702 --
3703 IF (l_project_funding.allocated_amount >= 0) THEN
3704
3705 l_temp_sum := l_temp_sum + l_project_funding.allocated_amount;
3706
3707 END IF;
3708
3709 l_allocation_in_rec.fund_allocation_id := null ;
3710 l_allocation_in_rec.funding_source_id := null ;
3711 l_allocation_in_rec.object_id := p_k_header_id ;
3712 l_allocation_in_rec.project_id := l_project_funding.project_id ;
3713 l_allocation_in_rec.task_id := l_project_funding.task_id ;
3714 l_allocation_in_rec.agreement_id := p_agreement_id ;
3715 l_allocation_in_rec.project_funding_id := l_project_funding.project_funding_id ;
3716
3717 --
3718 -- Validate the date_allocated of project funding against pool party start date
3719 --
3720 -- bug 3346170
3721 /* IF (p_pool_party_id is not null) THEN
3722 IF (nvl(to_char(l_start_date, 'YYYYMMDD'), '19000101') > to_char(l_project_funding.date_allocated, 'YYYYMMDD')) THEN
3723
3724 OKE_API.set_message(p_app_name => G_APP_NAME ,
3725 p_msg_name => 'OKE_PA_FUND_POOL_DATE'
3726 );
3727
3728 RAISE OKE_API.G_EXCEPTION_ERROR;
3729
3730 END IF;
3731 END IF;
3732 */
3733 l_allocation_in_rec.start_date_active := l_project_funding.date_allocated ;
3734 l_allocation_in_rec.amount := l_project_funding.allocated_amount ;
3735 l_allocation_in_rec.k_line_id := null ;
3736 l_allocation_in_rec.funding_category := l_project_funding.funding_category ;
3737
3738 --
3739 -- hard limit = allocation amount if p_revenue_limit_flag = 'Y'
3740 --
3741 IF (p_invoice_limit_flag = 'Y') THEN
3742 l_allocation_in_rec.hard_limit := l_project_funding.allocated_amount ;
3743 --l_hard_limit_total := nvl(l_hard_limit_total, 0) +
3744 --l_project_funding.allocated_amount ;
3745 ELSE
3746 l_allocation_in_rec.hard_limit := null ;
3747 END IF;
3748
3749 IF (p_revenue_limit_flag = 'Y') THEN
3750 l_allocation_in_rec.revenue_hard_limit := l_project_funding.allocated_amount ;
3751 -- l_revenue_hard_limit_total := nvl(l_revenue_hard_limit_total, 0) +
3752 -- l_project_funding.allocated_amount ;
3753 ELSE
3754 l_allocation_in_rec.revenue_hard_limit := null ;
3755 END IF;
3756
3757 l_allocation_in_rec.fund_type := null ;
3758 l_allocation_in_rec.funding_status := null ;
3759 l_allocation_in_rec.end_date_active := null ;
3760 --l_allocation_in_rec.end_date_active := p_expiration_date ;
3761 l_allocation_in_rec.fiscal_year := null ;
3762 l_allocation_in_rec.reference1 := null ;
3763 l_allocation_in_rec.reference2 := null ;
3764 l_allocation_in_rec.reference3 := null ;
3765 l_allocation_in_rec.pa_conversion_type := null ;
3766 l_allocation_in_rec.pa_conversion_date := null ;
3767 l_allocation_in_rec.pa_conversion_rate := null ;
3768 /*
3769 -- syho, bug 2208979
3770 IF (upper(p_pa_conversion_type) = 'USER') THEN
3771 l_allocation_in_rec.pa_conversion_rate := p_pa_conversion_rate ;
3772 ELSE
3773 l_allocation_in_rec.pa_conversion_rate := null ;
3774 END IF;
3775 -- syho, bug 2208979
3776 */
3777 l_allocation_in_rec.oke_attribute_category := null ;
3778 l_allocation_in_rec.oke_attribute1 := null ;
3779 l_allocation_in_rec.oke_attribute2 := null ;
3780 l_allocation_in_rec.oke_attribute3 := null ;
3781 l_allocation_in_rec.oke_attribute4 := null ;
3782 l_allocation_in_rec.oke_attribute5 := null ;
3783 l_allocation_in_rec.oke_attribute6 := null ;
3784 l_allocation_in_rec.oke_attribute7 := null ;
3785 l_allocation_in_rec.oke_attribute8 := null ;
3786 l_allocation_in_rec.oke_attribute9 := null ;
3787 l_allocation_in_rec.oke_attribute10 := null ;
3788 l_allocation_in_rec.oke_attribute11 := null ;
3789 l_allocation_in_rec.oke_attribute12 := null ;
3790 l_allocation_in_rec.oke_attribute13 := null ;
3791 l_allocation_in_rec.oke_attribute14 := null ;
3792 l_allocation_in_rec.oke_attribute15 := null ;
3793
3794 l_allocation_in_rec.pa_attribute_category := l_project_funding.attribute_category ;
3795 l_allocation_in_rec.pa_attribute1 := l_project_funding.attribute1 ;
3796 l_allocation_in_rec.pa_attribute2 := l_project_funding.attribute2 ;
3797 l_allocation_in_rec.pa_attribute3 := l_project_funding.attribute3 ;
3798 l_allocation_in_rec.pa_attribute4 := l_project_funding.attribute4 ;
3799 l_allocation_in_rec.pa_attribute5 := l_project_funding.attribute5 ;
3800 l_allocation_in_rec.pa_attribute6 := l_project_funding.attribute6 ;
3801 l_allocation_in_rec.pa_attribute7 := l_project_funding.attribute7 ;
3802 l_allocation_in_rec.pa_attribute8 := l_project_funding.attribute8 ;
3803 l_allocation_in_rec.pa_attribute9 := l_project_funding.attribute9 ;
3804 l_allocation_in_rec.pa_attribute10 := l_project_funding.attribute10 ;
3805
3806 l_allocation_in_tbl(i) := l_allocation_in_rec;
3807 i := i + 1;
3808
3809 END LOOP;
3810
3811 -- oke_debug.debug('funding total = ' || l_allocated_total);
3812 /*
3813 --
3814 -- get the funding source amount
3815 --
3816
3817 IF (l_min_unit is not null) THEN
3818
3819 l_allocated_total := round(p_amount * (1/p_pa_conversion_rate) / l_min_unit) * l_min_unit;
3820
3821 ELSE
3822
3823 l_allocated_total := p_amount;
3824
3825 END IF;
3826 */
3827 --oke_debug.debug('preparing funding record');
3828
3829 l_funding_in_rec.funding_source_id := null ;
3830 l_funding_in_rec.object_type := G_OBJECT_TYPE ;
3831 l_funding_in_rec.object_id := p_k_header_id ;
3832 l_funding_in_rec.pool_party_id := p_pool_party_id ;
3833 l_funding_in_rec.k_party_id := p_party_id ;
3834 -- l_funding_in_rec.amount := l_allocated_total ;
3835 l_funding_in_rec.amount := l_temp_sum ;
3836 l_funding_in_rec.customer_id := p_customer_id ;
3837 l_funding_in_rec.customer_number := p_customer_number ;
3838
3839 --
3840 -- Hard limit = fund amount if p_revenue_limit_flag is 'Y'
3841 --
3842
3843 IF (p_invoice_limit_flag = 'Y') THEN
3844 -- l_funding_in_rec.hard_limit := l_hard_limit_total ;
3845 l_funding_in_rec.hard_limit := l_temp_sum ;
3846 ELSE
3847 l_funding_in_rec.hard_limit := null ;
3848 END IF;
3849
3850 IF (p_revenue_limit_flag = 'Y') THEN
3851 -- l_funding_in_rec.revenue_hard_limit := l_revenue_hard_limit_total ;
3852 l_funding_in_rec.revenue_hard_limit := l_temp_sum ;
3853 ELSE
3854 l_funding_in_rec.revenue_hard_limit := null ;
3855 END IF;
3856
3857 l_funding_in_rec.currency_code := p_agreement_currency ;
3858 l_funding_in_rec.k_conversion_type := p_conversion_type ;
3859 l_funding_in_rec.k_conversion_date := p_conversion_date ;
3860
3861 -- syho, bug 2208979
3862 IF (upper(p_conversion_type) = 'USER') THEN
3863 l_funding_in_rec.k_conversion_rate := p_conversion_rate ;
3864 ELSE
3865 l_funding_in_rec.k_conversion_rate := null ;
3866 END IF;
3867 -- syho, bug 2208979
3868
3869 l_funding_in_rec.end_date_active := p_expiration_date ;
3870 l_funding_in_rec.agreement_number := p_agreement_number ;
3871 l_funding_in_rec.start_date_active := l_start_date ;
3872 l_funding_in_rec.funding_status := null ;
3873 l_funding_in_rec.oke_attribute_category := null ;
3874 l_funding_in_rec.oke_attribute1 := null ;
3875 l_funding_in_rec.oke_attribute2 := null ;
3876 l_funding_in_rec.oke_attribute3 := null ;
3877 l_funding_in_rec.oke_attribute4 := null ;
3878 l_funding_in_rec.oke_attribute5 := null ;
3879 l_funding_in_rec.oke_attribute6 := null ;
3880 l_funding_in_rec.oke_attribute7 := null ;
3881 l_funding_in_rec.oke_attribute8 := null ;
3882 l_funding_in_rec.oke_attribute9 := null ;
3883 l_funding_in_rec.oke_attribute10 := null ;
3884 l_funding_in_rec.oke_attribute11 := null ;
3885 l_funding_in_rec.oke_attribute12 := null ;
3886 l_funding_in_rec.oke_attribute13 := null ;
3887 l_funding_in_rec.oke_attribute14 := null ;
3888 l_funding_in_rec.oke_attribute15 := null ;
3889 l_funding_in_rec.pa_attribute_category := p_pa_attribute_category ;
3890 l_funding_in_rec.pa_attribute1 := p_pa_attribute1 ;
3891 l_funding_in_rec.pa_attribute2 := p_pa_attribute2 ;
3892 l_funding_in_rec.pa_attribute3 := p_pa_attribute3 ;
3893 l_funding_in_rec.pa_attribute4 := p_pa_attribute4 ;
3894 l_funding_in_rec.pa_attribute5 := p_pa_attribute5 ;
3895 l_funding_in_rec.pa_attribute6 := p_pa_attribute6 ;
3896 l_funding_in_rec.pa_attribute7 := p_pa_attribute7 ;
3897 l_funding_in_rec.pa_attribute8 := p_pa_attribute8 ;
3898 l_funding_in_rec.pa_attribute9 := p_pa_attribute9 ;
3899 l_funding_in_rec.pa_attribute10 := p_pa_attribute10 ;
3900 l_funding_in_rec.pa_attribute11 := p_pa_attribute11 ;
3901 l_funding_in_rec.pa_attribute12 := p_pa_attribute12 ;
3902 l_funding_in_rec.pa_attribute13 := p_pa_attribute13 ;
3903 l_funding_in_rec.pa_attribute14 := p_pa_attribute14 ;
3904 l_funding_in_rec.pa_attribute15 := p_pa_attribute15 ;
3905 l_funding_in_rec.pa_attribute16 := p_pa_attribute16 ;
3906 l_funding_in_rec.pa_attribute17 := p_pa_attribute17 ;
3907 l_funding_in_rec.pa_attribute18 := p_pa_attribute18 ;
3908 l_funding_in_rec.pa_attribute19 := p_pa_attribute19 ;
3909 l_funding_in_rec.pa_attribute20 := p_pa_attribute20 ;
3910 l_funding_in_rec.pa_attribute21 := p_pa_attribute21 ;
3911 l_funding_in_rec.pa_attribute22 := p_pa_attribute22 ;
3912 l_funding_in_rec.pa_attribute23 := p_pa_attribute23 ;
3913 l_funding_in_rec.pa_attribute24 := p_pa_attribute24 ;
3914 l_funding_in_rec.pa_attribute25 := p_pa_attribute25 ;
3915
3916 fnd_profile.get('ORG_ID',l_org_set);
3917
3918 IF (nvl(l_org_set, -99) <> nvl(p_org_id, -99)) THEN
3919 l_funding_in_rec.agreement_org_id := null ;
3920 ELSE
3921 l_funding_in_rec.agreement_org_id := p_owning_organization_id ;
3922 END IF;
3923
3924 --oke_debug.debug('calling create_funding');
3925 create_funding(p_api_version => p_api_version ,
3926 p_init_msg_list => OKE_API.G_FALSE ,
3927 p_msg_count => p_msg_count ,
3928 p_msg_data => p_msg_data ,
3929 p_funding_in_rec => l_funding_in_rec ,
3930 p_funding_out_rec => l_funding_out_rec ,
3931 p_return_status => p_return_status
3932 );
3933 --oke_debug.debug('finished create_funding w/status ' || p_return_status);
3934
3935 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3936
3937 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3938
3939 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
3940
3941 RAISE OKE_API.G_EXCEPTION_ERROR;
3942
3943 END IF;
3944
3945 --
3946 -- Update pa agreement with new agreement reference
3947 --
3948
3949 update_agreement_reference(p_agreement_id => p_agreement_id ,
3950 p_org_id => p_org_id ,
3951 p_currency_code => p_agreement_currency ,
3952 p_funding_source_id => l_funding_out_rec.funding_source_id ,
3953 p_num_update_flag => l_num_update_flag );
3954
3955
3956 IF (l_allocation_in_tbl.COUNT > 0) THEN
3957
3958 i := l_allocation_in_tbl.FIRST;
3959
3960 LOOP
3961
3962 --oke_debug.debug('calling oke_allocation_pvt.add_allocation');
3963 l_allocation_in_rec := l_allocation_in_tbl(i);
3964 l_allocation_in_rec.funding_source_id := l_funding_out_rec.funding_source_id;
3965
3966 --oke_debug.debug('converted_amount ' || l_allocation_in_rec.amount);
3967 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
3968 p_init_msg_list => OKE_API.G_FALSE ,
3969 p_commit => OKE_API.G_FALSE ,
3970 p_msg_count => p_msg_count ,
3971 p_msg_data => p_msg_data ,
3972 p_allocation_in_rec => l_allocation_in_rec ,
3973 p_allocation_out_rec => l_allocation_out_rec ,
3974 p_return_status => p_return_status
3975 );
3976
3977 --oke_debug.debug('finished calling oke_allocation_pvt.add_allocation w/ status' || p_return_status);
3978
3979 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3980
3981 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3982
3983 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
3984
3985 RAISE OKE_API.G_EXCEPTION_ERROR;
3986
3987 END IF;
3988
3989 --
3990 -- Update project funding with new funding reference
3991 --
3992
3993 update_proj_fld_reference(p_project_funding_id => l_allocation_in_rec.project_funding_id ,
3994 p_fund_allocation_id => l_allocation_out_rec.fund_allocation_id
3995 );
3996
3997 --
3998 -- update agreement_version in OKE_K_FUND_ALLOCATIONS table
3999 --
4000
4001 --dbms_output.put_line('calling oke_funding_util.update_alloc_version');
4002 --oke_debug.debug('calling oke_funding_util.update_alloc_version');
4003
4004 OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => l_allocation_out_rec.fund_allocation_id ,
4005 x_version_add => 1 ,
4006 x_commit => OKE_API.G_FALSE
4007 );
4008
4009 EXIT WHEN (i = l_allocation_in_tbl.LAST);
4010 i := l_allocation_in_tbl.NEXT(i);
4011
4012 END LOOP;
4013
4014 END IF;
4015
4016 p_funding_source_id := l_funding_out_rec.funding_source_id;
4017 --oke_debug.debug('finished calling fetch_create_funding');
4018
4019 --
4020 -- Update the funding source amount to the correct amount
4021 --
4022 l_funding_in_rec.amount := p_amount ;
4023 IF (p_invoice_limit_flag = 'Y') THEN
4024 l_funding_in_rec.hard_limit := p_amount ;
4025 ELSE
4026 l_funding_in_rec.hard_limit := null ;
4027 END IF;
4028 IF (p_revenue_limit_flag = 'Y') THEN
4029 l_funding_in_rec.revenue_hard_limit := p_amount ;
4030 ELSE
4031 l_funding_in_rec.revenue_hard_limit := null ;
4032 END IF;
4033 --l_funding_in_rec.hard_limit := l_hard_limit_total ;
4034 -- l_funding_in_rec.revenue_hard_limit := l_revenue_hard_limit_total ;
4035 l_funding_in_rec.funding_source_id := l_funding_out_rec.funding_source_id ;
4036
4037 --oke_debug.debug('calling create_funding');
4038 update_funding(p_api_version => p_api_version ,
4039 p_init_msg_list => OKE_API.G_FALSE ,
4040 p_msg_count => p_msg_count ,
4041 p_msg_data => p_msg_data ,
4042 p_funding_in_rec => l_funding_in_rec ,
4043 p_funding_out_rec => l_funding_out_rec ,
4044 p_return_status => p_return_status
4045 );
4046 --oke_debug.debug('finished create_funding w/status ' || p_return_status);
4047
4048 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4049
4050 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4051
4052 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
4053
4054 RAISE OKE_API.G_EXCEPTION_ERROR;
4055
4056 END IF;
4057
4058 --
4059 -- Update initial amount and pa_flag
4060 --
4061 update oke_k_funding_sources
4062 set initial_amount = p_amount,
4063 funding_across_ou = decode(l_dummy, 'x', 'Y')
4064 where funding_source_id = l_funding_out_rec.funding_source_id;
4065
4066 --
4067 -- update the pa_flag to be 'Y'
4068 --
4069 update oke_k_fund_allocations
4070 set pa_flag = 'Y'
4071 where funding_source_id = l_funding_out_rec.funding_source_id;
4072
4073 --
4074 -- update the agreement_flag in OKE_FUNDING_SOURCES table
4075 --
4076
4077 OKE_FUNDING_UTIL_PKG.update_source_flag(x_funding_source_id => l_funding_out_rec.funding_source_id ,
4078 x_commit => OKE_API.G_FALSE
4079 );
4080
4081 IF FND_API.to_boolean(p_commit) THEN
4082
4083 COMMIT WORK;
4084
4085 END IF;
4086
4087 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
4088 x_msg_data => p_msg_data
4089 );
4090
4091 EXCEPTION
4092 WHEN OKE_API.G_EXCEPTION_ERROR THEN
4093 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
4094 p_pkg_name => G_PKG_NAME ,
4095 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
4096 x_msg_count => p_msg_count ,
4097 x_msg_data => p_msg_data ,
4098 p_api_type => '_PVT'
4099 );
4100
4101 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4102 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
4103 p_pkg_name => G_PKG_NAME ,
4104 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
4105 x_msg_count => p_msg_count ,
4106 x_msg_data => p_msg_data ,
4107 p_api_type => '_PVT'
4108 );
4109
4110 WHEN OTHERS THEN
4111 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
4112 p_pkg_name => G_PKG_NAME ,
4113 p_exc_name => 'OTHERS' ,
4114 x_msg_count => p_msg_count ,
4115 x_msg_data => p_msg_data ,
4116 p_api_type => '_PVT'
4117 );
4118 END fetch_create_funding;
4119
4120
4121 --
4122 -- Procedure: fetch_create_funding
4123 --
4124 -- Description: This procedure is used to get the existing agreement record
4125 -- and create a funding record in OKE
4126 --
4127 -- Calling subprograms: OKE_API.start_activity
4128 -- OKE_API.end_activity
4129 -- OKE_FUNDSOURCE_PVT.create_funding
4130 -- OKE_ALLOCATION_PVT.add_allocation
4131 -- OKE_FUNDING_UTIL_PKG.check_single_org
4132 -- OKE_FUNDING_UTIL_PKG.update_source_flag
4133 -- OKE_FUNDING_UTIL_PKG.update_alloc_version
4134 -- validate_parameters
4135 -- lock_agreement
4136 -- validate_agreement_party
4137 -- get_k_currency
4138 -- get_conversion
4139 -- get_min_unit
4140 -- create_funding
4141 -- update_agreement_reference
4142 -- update_proj_fld_reference
4143 --
4144
4145 PROCEDURE fetch_create_funding(p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
4146 p_api_version IN NUMBER ,
4147 p_msg_count OUT NOCOPY NUMBER ,
4148 p_msg_data OUT NOCOPY VARCHAR2 ,
4149 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
4150 p_pool_party_id IN NUMBER ,
4151 p_party_id IN NUMBER ,
4152 --p_source_currency IN VARCHAR2 ,
4153 p_agreement_id IN NUMBER ,
4154 p_conversion_type IN VARCHAR2 ,
4155 p_conversion_date IN DATE ,
4156 p_conversion_rate IN NUMBER ,
4157 --p_pa_conversion_type IN VARCHAR2 ,
4158 --p_pa_conversion_date IN DATE ,
4159 --p_pa_conversion_rate IN NUMBER ,
4160 p_k_header_id IN NUMBER ,
4161 p_funding_source_id OUT NOCOPY NUMBER ,
4162 p_return_status OUT NOCOPY VARCHAR2
4163 ) is
4164
4165 cursor c_project_funding is
4166 select *
4167 from pa_project_fundings
4168 where agreement_id = p_agreement_id
4169 -- Begin 15980946 change
4170 and budget_type_code in ( 'BASELINE', 'DRAFT')
4171 -- End 15980946 change
4172 order by allocated_amount desc;
4173
4174 cursor c_agreement is
4175 select *
4176 from oke_agreements_v
4177 where agreement_id = p_agreement_id;
4178
4179 cursor c_ou (x_org_id number) is
4180 select nvl(allow_funding_across_ou_flag, 'N'),
4181 currency_code
4182 from pa_implementations_all p,
4183 gl_sets_of_books g
4184 where nvl(org_id, -99) = nvl(x_org_id, -99)
4185 and g.set_of_books_id = p.set_of_books_id;
4186
4187 cursor c_across (x_org_id number) is
4188 select 'x'
4189 from pa_project_fundings f,
4190 pa_projects_all p
4191 where nvl(p.org_id, -99) <> nvl(x_org_id, -99)
4192 and f.project_id = p.project_id
4193 and f.agreement_id = p_agreement_id;
4194
4195 /*
4196 cursor c_update (x_org_id number) is
4197 select 'x'
4198 from pa_project_fundings f,
4199 pa_projects_all p
4200 where p.project_id = f.project_id
4201 and f.agreement_id = p_agreement_id
4202 and nvl(p.org_id, -99) <> nvl(x_org_id, -99);
4203 */
4204 l_return_status VARCHAR2(1);
4205 l_api_name CONSTANT VARCHAR2(30) := 'fetch_create_funding';
4206 -- l_fund_currency VARCHAR2(15);
4207 --l_min_unit NUMBER;
4208 -- l_allocated_total NUMBER := 0;
4209 -- l_converted_amount NUMBER;
4210 l_funding_in_rec FUNDING_REC_IN_TYPE;
4211 l_funding_out_rec FUNDING_REC_OUT_TYPE;
4212 l_allocation_in_rec OKE_ALLOCATION_PVT.ALLOCATION_REC_IN_TYPE;
4213 l_allocation_out_rec OKE_ALLOCATION_PVT.ALLOCATION_REC_OUT_TYPE;
4214 l_allocation_in_tbl OKE_ALLOCATION_PVT.ALLOCATION_IN_TBL_TYPE;
4215 i NUMBER := 0;
4216 l_contract_currency VARCHAR2(15);
4217 l_pool_currency VARCHAR2(15);
4218 l_conversion_rate NUMBER;
4219 --l_pa_conversion_rate NUMBER;
4220 l_agreement c_agreement%ROWTYPE;
4221 l_project_funding c_project_funding%ROWTYPE;
4222 --l_hard_limit_total NUMBER := 0;
4223 -- l_revenue_hard_limit_total NUMBER := 0;
4224 l_dummy VARCHAR2(1) := '?';
4225 -- l_update_flag VARCHAR2(1) := 'Y';
4226 l_start_date DATE;
4227 l_temp_sum NUMBER := 0;
4228 l_mcb_flag VARCHAR2(1);
4229 l_org_set NUMBER;
4230 l_num_update_flag VARCHAR2(1);
4231 l_ou_currency VARCHAR2(15);
4232
4233 BEGIN
4234
4235 --oke_debug.debug('entering fetch_create_funding..');
4236 --dbms_output.put_line('entering fetch_create_funding..');
4237
4238 p_return_status := OKE_API.G_RET_STS_SUCCESS;
4239
4240 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
4241 p_pkg_name => G_PKG_NAME ,
4242 p_init_msg_list => p_init_msg_list ,
4243 l_api_version => G_API_VERSION_NUMBER ,
4244 p_api_version => p_api_version ,
4245 p_api_type => '_PVT' ,
4246 x_return_status => p_return_status
4247 );
4248
4249 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4250
4251 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4252
4253 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4254
4255 RAISE OKE_API.G_EXCEPTION_ERROR;
4256
4257 END IF;
4258
4259 --
4260 -- check if all mandatory fields are there or not
4261 --
4262
4263 validate_parameters(p_pool_party_id => p_pool_party_id ,
4264 p_party_id => p_party_id ,
4265 --p_source_currency => p_source_currency ,
4266 p_agreement_id => p_agreement_id ,
4267 p_conversion_type => p_conversion_type ,
4268 -- p_pa_conversion_type => p_pa_conversion_type ,
4269 p_k_header_id => p_k_header_id
4270 );
4271
4272 --
4273 -- lock agreement record
4274 --
4275
4276 lock_agreement(p_agreement_id => p_agreement_id );
4277
4278 --
4279 -- get pa agreement attributes
4280 --
4281
4282 OPEN c_agreement;
4283 FETCH c_agreement into l_agreement;
4284 CLOSE c_agreement;
4285
4286 --
4287 -- Check agreement org settings
4288 --
4289 OPEN c_ou(l_agreement.org_id);
4290 FETCH c_ou into l_mcb_flag, l_ou_currency;
4291 CLOSE c_ou;
4292
4293 IF (l_ou_currency <> l_agreement.agreement_currency_code) THEN
4294
4295 l_num_update_flag := 'Y';
4296
4297 ELSE
4298
4299 l_num_update_flag := 'N';
4300
4301 END IF;
4302
4303 IF (l_mcb_flag = 'Y') THEN
4304
4305 OPEN c_across(l_agreement.org_id);
4306 FETCH c_across into l_dummy;
4307 CLOSE c_across;
4308
4309 END IF;
4310
4311 --
4312 -- Check allow changing funding source currency or not
4313 --
4314 /*
4315 OPEN c_update(l_agreement.org_id);
4316 FETCH c_update into l_dummy;
4317 CLOSE c_update;
4318
4319 IF (l_dummy <> '?') OR
4320 (l_agreement.agreement_currency_code <> l_agreement.functional_currency_code) THEN
4321
4322 l_update_flag := 'N';
4323
4324 END IF;
4325
4326 IF (l_update_flag = 'N') AND
4327 (l_agreement.agreement_currency_code <> p_source_currency) THEN
4328
4329 OKE_API.set_message(p_app_name => G_APP_NAME ,
4330 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4331 p_token1 => 'VALUE' ,
4332 p_token1_value => 'p_source_currency'
4333 );
4334
4335 RAISE OKE_API.G_EXCEPTION_ERROR;
4336
4337 END IF;
4338 */
4339 --
4340 -- validate party_id and pool_party_id
4341 --
4342
4343 validate_agreement_party(p_pool_party_id => p_pool_party_id ,
4344 p_party_id => p_party_id ,
4345 p_expiration_date => l_agreement.expiration_date ,
4346 p_customer_id => l_agreement.customer_id ,
4347 --p_update_flag => l_update_flag ,
4348 p_currency_code => l_pool_currency ,
4349 p_start_date => l_start_date
4350 );
4351
4352 --
4353 -- Check if pool party currency = agreement currency
4354 --
4355 IF (nvl(l_pool_currency, l_agreement.agreement_currency_code) <> l_agreement.agreement_currency_code) THEN
4356
4357 OKE_API.set_message(p_app_name => G_APP_NAME ,
4358 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4359 p_token1 => 'VALUE' ,
4360 p_token1_value => 'p_pool_party_id'
4361 );
4362
4363 RAISE OKE_API.G_EXCEPTION_ERROR;
4364
4365 END IF;
4366
4367 l_contract_currency := get_k_currency(p_header_id => p_k_header_id);
4368
4369 --
4370 -- validate conversion from source to contract
4371 --
4372
4373 l_conversion_rate := p_conversion_rate;
4374
4375 get_conversion(p_from_currency => l_agreement.agreement_currency_code ,
4376 p_to_currency => l_contract_currency ,
4377 p_conversion_type => p_conversion_type ,
4378 p_conversion_date => p_conversion_date ,
4379 p_conversion_rate => l_conversion_rate ,
4380 p_return_status => p_return_status
4381 );
4382
4383 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4384
4385 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4386
4387 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
4388
4389 RAISE OKE_API.G_EXCEPTION_ERROR;
4390
4391 END IF;
4392
4393 --
4394 -- validate conversion from source to agreement
4395 --
4396 /*
4397 l_pa_conversion_rate := p_pa_conversion_rate;
4398
4399 IF (l_update_flag = 'N') THEN
4400
4401 IF (p_pa_conversion_type is not null) THEN
4402
4403 OKE_API.set_message(p_app_name => G_APP_NAME ,
4404 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4405 p_token1 => 'VALUE' ,
4406 p_token1_value => 'pa_conversion_type'
4407 );
4408
4409 RAISE OKE_API.G_EXCEPTION_ERROR;
4410
4411 END IF;
4412
4413 IF (p_pa_conversion_date is not null) THEN
4414
4415 OKE_API.set_message(p_app_name => G_APP_NAME ,
4416 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4417 p_token1 => 'VALUE' ,
4418 p_token1_value => 'pa_conversion_date'
4419 );
4420
4421 RAISE OKE_API.G_EXCEPTION_ERROR;
4422
4423 END IF;
4424
4425 IF (p_pa_conversion_rate is not null) THEN
4426
4427 OKE_API.set_message(p_app_name => G_APP_NAME ,
4428 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4429 p_token1 => 'VALUE' ,
4430 p_token1_value => 'pa_conversion_rate'
4431 );
4432
4433 RAISE OKE_API.G_EXCEPTION_ERROR;
4434
4435 END IF;
4436
4437 ELSIF (p_source_currency <> l_agreement.agreement_currency_code) THEN
4438
4439 l_min_unit := get_min_unit(p_source_currency);
4440
4441 get_conversion(p_from_currency => p_source_currency ,
4442 p_to_currency => l_agreement.agreement_currency_code ,
4443 p_conversion_type => p_pa_conversion_type ,
4444 p_conversion_date => p_pa_conversion_date ,
4445 p_conversion_rate => l_pa_conversion_rate ,
4446 p_return_status => p_return_status
4447 );
4448
4449 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4450
4451 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4452
4453 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
4454
4455 RAISE OKE_API.G_EXCEPTION_ERROR;
4456
4457 END IF;
4458
4459 -- syho, 2/11/02
4460 ELSIF (l_pa_conversion_rate is not null) THEN
4461
4462 OKE_API.set_message(p_app_name => G_APP_NAME ,
4463 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4464 p_token1 => 'VALUE' ,
4465 p_token1_value => 'pa_conversion_rate'
4466 );
4467
4468 RAISE OKE_API.G_EXCEPTION_ERROR;
4469
4470 ELSIF (p_pa_conversion_type is not null) THEN
4471
4472 OKE_API.set_message(p_app_name => G_APP_NAME ,
4473 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4474 p_token1 => 'VALUE' ,
4475 p_token1_value => 'pa_conversion_type'
4476 );
4477
4478 RAISE OKE_API.G_EXCEPTION_ERROR;
4479
4480 ELSIF (p_pa_conversion_date is not null) THEN
4481
4482 OKE_API.set_message(p_app_name => G_APP_NAME ,
4483 p_msg_name => 'OKE_API_INVALID_VALUE' ,
4484 p_token1 => 'VALUE' ,
4485 p_token1_value => 'pa_conversion_date'
4486 );
4487
4488 RAISE OKE_API.G_EXCEPTION_ERROR;
4489
4490 END IF;
4491 -- syho, 2/11/02
4492 */
4493 FOR l_project_funding in c_project_funding LOOP
4494 /*
4495 IF (l_min_unit is not null) THEN
4496
4497 l_converted_amount := round(l_project_funding.allocated_amount * (1/l_pa_conversion_rate) / l_min_unit) * l_min_unit;
4498
4499 ELSE
4500
4501 l_converted_amount := l_project_funding.allocated_amount;
4502
4503 END IF; */
4504 -- oke_debug.debug('l_converted_amount ' || l_converted_amount);
4505 -- l_allocated_total := l_allocated_total + l_converted_amount;
4506
4507 --
4508 -- Same up all the positive amount and create a funding source with this amount first
4509 --
4510 IF (l_project_funding.allocated_amount >= 0) THEN
4511
4512 l_temp_sum := l_temp_sum + l_project_funding.allocated_amount;
4513
4514 END IF;
4515
4516 l_allocation_in_rec.fund_allocation_id := null ;
4517 l_allocation_in_rec.funding_source_id := null ;
4518 l_allocation_in_rec.object_id := p_k_header_id ;
4519 l_allocation_in_rec.project_id := l_project_funding.project_id ;
4520 l_allocation_in_rec.task_id := l_project_funding.task_id ;
4521 l_allocation_in_rec.agreement_id := p_agreement_id ;
4522 l_allocation_in_rec.project_funding_id := l_project_funding.project_funding_id ;
4523
4524 --
4525 -- Validate the date_allocated of project funding against pool party start date
4526 --
4527 -- bug 3346710
4528 /* IF (p_pool_party_id is not null) THEN
4529 IF (nvl(to_char(l_start_date, 'YYYYMMDD'), '19000101') > to_char(l_project_funding.date_allocated, 'YYYYMMDD')) THEN
4530
4531 OKE_API.set_message(p_app_name => G_APP_NAME ,
4532 p_msg_name => 'OKE_PA_FUND_POOL_DATE'
4533 );
4534
4535 RAISE OKE_API.G_EXCEPTION_ERROR;
4536
4537 END IF;
4538 END IF;
4539 */
4540 l_allocation_in_rec.start_date_active := l_project_funding.date_allocated ;
4541 -- l_allocation_in_rec.amount := l_converted_amount ;
4542 l_allocation_in_rec.amount := l_project_funding.allocated_amount ;
4543 l_allocation_in_rec.k_line_id := null ;
4544 l_allocation_in_rec.funding_category := l_project_funding.funding_category ;
4545
4546 --
4547 -- hard limit = allocation amount if p_revenue_limit_flag = 'Y'
4548 --
4549 IF (l_agreement.invoice_limit_flag = 'Y') THEN
4550 l_allocation_in_rec.hard_limit := l_project_funding.allocated_amount ;
4551 -- l_hard_limit_total := nvl(l_hard_limit_total, 0) +
4552 -- l_project_funding.allocated_amount ;
4553 ELSE
4554 l_allocation_in_rec.hard_limit := null ;
4555 END IF;
4556
4557 IF (l_agreement.revenue_limit_flag = 'Y') THEN
4558 l_allocation_in_rec.revenue_hard_limit := l_project_funding.allocated_amount ;
4559 -- l_revenue_hard_limit_total := nvl(l_revenue_hard_limit_total, 0) +
4560 -- l_project_funding.allocated_amount ;
4561 ELSE
4562 l_allocation_in_rec.revenue_hard_limit := null ;
4563 END IF;
4564
4565 l_allocation_in_rec.fund_type := null ;
4566 l_allocation_in_rec.funding_status := null ;
4567 l_allocation_in_rec.end_date_active := null ;
4568 --l_allocation_in_rec.end_date_active := l_agreement.expiration_date ;
4569 l_allocation_in_rec.fiscal_year := null ;
4570 l_allocation_in_rec.reference1 := null ;
4571 l_allocation_in_rec.reference2 := null ;
4572 l_allocation_in_rec.reference3 := null ;
4573 --l_allocation_in_rec.pa_conversion_type := p_pa_conversion_type ;
4574 --l_allocation_in_rec.pa_conversion_date := p_pa_conversion_date ;
4575 l_allocation_in_rec.pa_conversion_type := null ;
4576 l_allocation_in_rec.pa_conversion_date := null ;
4577 l_allocation_in_rec.pa_conversion_rate := null ;
4578
4579 /*
4580 -- syho, bug 2208979
4581 IF (upper(p_pa_conversion_type) = 'USER') THEN
4582 l_allocation_in_rec.pa_conversion_rate := l_pa_conversion_rate ;
4583 ELSE
4584 l_allocation_in_rec.pa_conversion_rate := null ;
4585 END IF;
4586 -- syho, bug 2208979
4587 */
4588 l_allocation_in_rec.oke_attribute_category := null ;
4589 l_allocation_in_rec.oke_attribute1 := null ;
4590 l_allocation_in_rec.oke_attribute2 := null ;
4591 l_allocation_in_rec.oke_attribute3 := null ;
4592 l_allocation_in_rec.oke_attribute4 := null ;
4593 l_allocation_in_rec.oke_attribute5 := null ;
4594 l_allocation_in_rec.oke_attribute6 := null ;
4595 l_allocation_in_rec.oke_attribute7 := null ;
4596 l_allocation_in_rec.oke_attribute8 := null ;
4597 l_allocation_in_rec.oke_attribute9 := null ;
4598 l_allocation_in_rec.oke_attribute10 := null ;
4599 l_allocation_in_rec.oke_attribute11 := null ;
4600 l_allocation_in_rec.oke_attribute12 := null ;
4601 l_allocation_in_rec.oke_attribute13 := null ;
4602 l_allocation_in_rec.oke_attribute14 := null ;
4603 l_allocation_in_rec.oke_attribute15 := null ;
4604
4605 l_allocation_in_rec.pa_attribute_category := l_project_funding.attribute_category ;
4606 l_allocation_in_rec.pa_attribute1 := l_project_funding.attribute1 ;
4607 l_allocation_in_rec.pa_attribute2 := l_project_funding.attribute2 ;
4608 l_allocation_in_rec.pa_attribute3 := l_project_funding.attribute3 ;
4609 l_allocation_in_rec.pa_attribute4 := l_project_funding.attribute4 ;
4610 l_allocation_in_rec.pa_attribute5 := l_project_funding.attribute5 ;
4611 l_allocation_in_rec.pa_attribute6 := l_project_funding.attribute6 ;
4612 l_allocation_in_rec.pa_attribute7 := l_project_funding.attribute7 ;
4613 l_allocation_in_rec.pa_attribute8 := l_project_funding.attribute8 ;
4614 l_allocation_in_rec.pa_attribute9 := l_project_funding.attribute9 ;
4615 l_allocation_in_rec.pa_attribute10 := l_project_funding.attribute10 ;
4616
4617 l_allocation_in_tbl(i) := l_allocation_in_rec;
4618
4619 i := i + 1;
4620
4621 END LOOP;
4622
4623 -- oke_debug.debug('funding total = ' || l_allocated_total);
4624 /*
4625 --
4626 -- check if it is a single org implementation
4627 --
4628
4629 OKE_FUNDING_UTIL_PKG.check_single_org(l_single_org_flag);
4630
4631 --
4632 -- get funding source amount
4633 --
4634
4635 IF (l_min_unit is not null) THEN
4636
4637 l_allocated_total := round(l_agreement.amount * (1/l_pa_conversion_rate) / l_min_unit) * l_min_unit;
4638
4639 ELSE
4640
4641 l_allocated_total := l_agreement.amount;
4642
4643 END IF;
4644 */
4645 --oke_debug.debug('preparing funding record');
4646
4647 l_funding_in_rec.funding_source_id := null ;
4648 l_funding_in_rec.object_type := G_OBJECT_TYPE ;
4649 l_funding_in_rec.object_id := p_k_header_id ;
4650 l_funding_in_rec.pool_party_id := p_pool_party_id ;
4651 l_funding_in_rec.k_party_id := p_party_id ;
4652 -- l_funding_in_rec.amount := l_allocated_total ;
4653 l_funding_in_rec.amount := l_temp_sum ;
4654 l_funding_in_rec.customer_id := l_agreement.customer_id ;
4655
4656 fnd_profile.get('ORG_ID',l_org_set);
4657
4658 IF (nvl(l_org_set, -99) <> nvl(l_agreement.owning_organization_id, -99)) THEN
4659 l_funding_in_rec.agreement_org_id := null ;
4660 ELSE
4661 l_funding_in_rec.agreement_org_id := l_agreement.owning_organization_id ;
4662 END IF;
4663
4664 --
4665 -- Hard limit = fund amount if revenue_limit_flag is 'Y'
4666 --
4667
4668 IF (l_agreement.invoice_limit_flag = 'Y') THEN
4669 -- l_funding_in_rec.hard_limit := l_hard_limit_total ;
4670 l_funding_in_rec.hard_limit := l_temp_sum ;
4671 ELSE
4672 l_funding_in_rec.hard_limit := null ;
4673 END IF;
4674
4675 IF (l_agreement.revenue_limit_flag = 'Y') THEN
4676 -- l_funding_in_rec.revenue_hard_limit := l_revenue_hard_limit_total ;
4677 l_funding_in_rec.revenue_hard_limit := l_temp_sum ;
4678 ELSE
4679 l_funding_in_rec.revenue_hard_limit := null ;
4680 END IF;
4681
4682 l_funding_in_rec.currency_code := l_agreement.agreement_currency_code ;
4683 l_funding_in_rec.k_conversion_type := p_conversion_type ;
4684 l_funding_in_rec.k_conversion_date := p_conversion_date ;
4685
4686 -- syho, bug 2208979
4687 IF (upper(p_conversion_type) = 'USER') THEN
4688 l_funding_in_rec.k_conversion_rate := l_conversion_rate ;
4689 ELSE
4690 l_funding_in_rec.k_conversion_rate := null ;
4691 END IF;
4692 -- syho, bug 2208979
4693
4694 l_funding_in_rec.end_date_active := l_agreement.expiration_date ;
4695 l_funding_in_rec.agreement_number := l_agreement.agreement_num ;
4696 l_funding_in_rec.start_date_active := l_start_date ;
4697 l_funding_in_rec.funding_status := null ;
4698 l_funding_in_rec.oke_attribute_category := null ;
4699 l_funding_in_rec.oke_attribute1 := null ;
4700 l_funding_in_rec.oke_attribute2 := null ;
4701 l_funding_in_rec.oke_attribute3 := null ;
4702 l_funding_in_rec.oke_attribute4 := null ;
4703 l_funding_in_rec.oke_attribute5 := null ;
4704 l_funding_in_rec.oke_attribute6 := null ;
4705 l_funding_in_rec.oke_attribute7 := null ;
4706 l_funding_in_rec.oke_attribute8 := null ;
4707 l_funding_in_rec.oke_attribute9 := null ;
4708 l_funding_in_rec.oke_attribute10 := null ;
4709 l_funding_in_rec.oke_attribute11 := null ;
4710 l_funding_in_rec.oke_attribute12 := null ;
4711 l_funding_in_rec.oke_attribute13 := null ;
4712 l_funding_in_rec.oke_attribute14 := null ;
4713 l_funding_in_rec.oke_attribute15 := null ;
4714 l_funding_in_rec.pa_attribute_category := l_agreement.attribute_category ;
4715 l_funding_in_rec.pa_attribute1 := l_agreement.attribute1 ;
4716 l_funding_in_rec.pa_attribute2 := l_agreement.attribute2 ;
4717 l_funding_in_rec.pa_attribute3 := l_agreement.attribute3 ;
4718 l_funding_in_rec.pa_attribute4 := l_agreement.attribute4 ;
4719 l_funding_in_rec.pa_attribute5 := l_agreement.attribute5 ;
4720 l_funding_in_rec.pa_attribute6 := l_agreement.attribute6 ;
4721 l_funding_in_rec.pa_attribute7 := l_agreement.attribute7 ;
4722 l_funding_in_rec.pa_attribute8 := l_agreement.attribute8 ;
4723 l_funding_in_rec.pa_attribute9 := l_agreement.attribute9 ;
4724 l_funding_in_rec.pa_attribute10 := l_agreement.attribute10 ;
4725 l_funding_in_rec.pa_attribute11 := l_agreement.attribute11 ;
4726 l_funding_in_rec.pa_attribute12 := l_agreement.attribute12 ;
4727 l_funding_in_rec.pa_attribute13 := l_agreement.attribute13 ;
4728 l_funding_in_rec.pa_attribute14 := l_agreement.attribute14 ;
4729 l_funding_in_rec.pa_attribute15 := l_agreement.attribute15 ;
4730 l_funding_in_rec.pa_attribute16 := l_agreement.attribute16 ;
4731 l_funding_in_rec.pa_attribute17 := l_agreement.attribute17 ;
4732 l_funding_in_rec.pa_attribute18 := l_agreement.attribute18 ;
4733 l_funding_in_rec.pa_attribute19 := l_agreement.attribute19 ;
4734 l_funding_in_rec.pa_attribute20 := l_agreement.attribute20 ;
4735 l_funding_in_rec.pa_attribute22 := l_agreement.attribute22 ;
4736 l_funding_in_rec.pa_attribute23 := l_agreement.attribute23 ;
4737 l_funding_in_rec.pa_attribute24 := l_agreement.attribute24 ;
4738 l_funding_in_rec.pa_attribute25 := l_agreement.attribute25 ;
4739
4740 --oke_debug.debug('calling create_funding');
4741 create_funding(p_api_version => p_api_version ,
4742 p_init_msg_list => OKE_API.G_FALSE ,
4743 p_msg_count => p_msg_count ,
4744 p_msg_data => p_msg_data ,
4745 p_funding_in_rec => l_funding_in_rec ,
4746 p_funding_out_rec => l_funding_out_rec ,
4747 p_return_status => p_return_status
4748 );
4749 --oke_debug.debug('finished create_funding w/status ' || p_return_status);
4750
4751 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4752
4753 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4754
4755 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
4756
4757 RAISE OKE_API.G_EXCEPTION_ERROR;
4758
4759 END IF;
4760
4761 --
4762 -- Update pa agreement with new agreement reference
4763 --
4764
4765 update_agreement_reference(p_agreement_id => p_agreement_id ,
4766 p_org_id => l_agreement.org_id ,
4767 p_currency_code => l_agreement.agreement_currency_code ,
4768 p_funding_source_id => l_funding_out_rec.funding_source_id ,
4769 p_num_update_flag => l_num_update_flag );
4770
4771 IF (l_allocation_in_tbl.COUNT > 0) THEN
4772
4773 i := l_allocation_in_tbl.FIRST;
4774
4775 LOOP
4776
4777 --oke_debug.debug('calling oke_allocation_pvt.add_allocation');
4778 l_allocation_in_rec := l_allocation_in_tbl(i);
4779 l_allocation_in_rec.funding_source_id := l_funding_out_rec.funding_source_id;
4780
4781 --oke_debug.debug('converted_amount ' || l_allocation_in_rec.amount);
4782 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
4783 p_init_msg_list => OKE_API.G_FALSE ,
4784 p_commit => OKE_API.G_FALSE ,
4785 p_msg_count => p_msg_count ,
4786 p_msg_data => p_msg_data ,
4787 p_allocation_in_rec => l_allocation_in_rec ,
4788 p_allocation_out_rec => l_allocation_out_rec ,
4789 p_return_status => p_return_status
4790 );
4791
4792 --oke_debug.debug('finished calling oke_allocation_pvt.add_allocation w/ status' || p_return_status);
4793
4794 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4795
4796 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4797
4798 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
4799
4800 RAISE OKE_API.G_EXCEPTION_ERROR;
4801
4802 END IF;
4803
4804 --
4805 -- Update project funding with new funding reference
4806 --
4807
4808 update_proj_fld_reference(p_project_funding_id => l_allocation_in_rec.project_funding_id ,
4809 p_fund_allocation_id => l_allocation_out_rec.fund_allocation_id
4810 );
4811
4812 --
4813 -- update agreement_version in OKE_K_FUND_ALLOCATIONS table
4814 --
4815
4816 --dbms_output.put_line('calling oke_funding_util.update_alloc_version');
4817 --oke_debug.debug('calling oke_funding_util.update_alloc_version');
4818
4819 OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => l_allocation_out_rec.fund_allocation_id ,
4820 x_version_add => 1 ,
4821 x_commit => OKE_API.G_FALSE
4822 );
4823
4824 EXIT WHEN (i = l_allocation_in_tbl.LAST);
4825 i := l_allocation_in_tbl.NEXT(i);
4826
4827 END LOOP;
4828
4829 END IF;
4830
4831 --
4832 -- Update the funding source amount to the correct amount
4833 --
4834 l_funding_in_rec.amount := l_agreement.amount ;
4835 IF (nvl(l_agreement.invoice_limit_flag, 'N') = 'Y') THEN
4836 l_funding_in_rec.hard_limit := l_agreement.amount ;
4837 ELSE
4838 l_funding_in_rec.hard_limit := null ;
4839 END IF;
4840 IF (nvl(l_agreement.revenue_limit_flag, 'N') = 'Y') THEN
4841 l_funding_in_rec.revenue_hard_limit := l_agreement.amount ;
4842 ELSE
4843 l_funding_in_rec.revenue_hard_limit := null ;
4844 END IF;
4845 -- l_funding_in_rec.hard_limit := l_hard_limit_total ;
4846 --l_funding_in_rec.revenue_hard_limit := l_revenue_hard_limit_total ;
4847 l_funding_in_rec.funding_source_id := l_funding_out_rec.funding_source_id ;
4848
4849 --oke_debug.debug('calling create_funding');
4850 update_funding(p_api_version => p_api_version ,
4851 p_init_msg_list => OKE_API.G_FALSE ,
4852 p_msg_count => p_msg_count ,
4853 p_msg_data => p_msg_data ,
4854 p_funding_in_rec => l_funding_in_rec ,
4855 p_funding_out_rec => l_funding_out_rec ,
4856 p_return_status => p_return_status
4857 );
4858 --oke_debug.debug('finished create_funding w/status ' || p_return_status);
4859
4860 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4861
4862 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4863
4864 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
4865
4866 RAISE OKE_API.G_EXCEPTION_ERROR;
4867
4868 END IF;
4869
4870 --
4871 -- Update initial amount and pa_flag
4872 --
4873 update oke_k_funding_sources
4874 set initial_amount = l_agreement.amount,
4875 funding_across_ou = decode(l_dummy, 'x', 'Y')
4876 where funding_source_id = l_funding_out_rec.funding_source_id;
4877
4878 p_funding_source_id := l_funding_out_rec.funding_source_id;
4879 --oke_debug.debug('finished calling fetch_create_funding');
4880
4881 --
4882 -- update the pa_flag to be 'Y'
4883 --
4884 update oke_k_fund_allocations
4885 set pa_flag = 'Y'
4886 where funding_source_id = l_funding_out_rec.funding_source_id;
4887
4888 --
4889 -- update the agreement_flag in OKE_FUNDING_SOURCES table
4890 --
4891
4892 OKE_FUNDING_UTIL_PKG.update_source_flag(x_funding_source_id => l_funding_out_rec.funding_source_id ,
4893 x_commit => OKE_API.G_FALSE
4894 );
4895
4896 IF FND_API.to_boolean(p_commit) THEN
4897
4898 COMMIT WORK;
4899
4900 END IF;
4901
4902 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
4903 x_msg_data => p_msg_data
4904 );
4905
4906 EXCEPTION
4907 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
4908 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
4909 p_pkg_name => G_PKG_NAME ,
4910 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
4911 x_msg_count => p_msg_count ,
4912 x_msg_data => p_msg_data ,
4913 p_api_type => '_PVT'
4914 );
4915
4916 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4917 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
4918 p_pkg_name => G_PKG_NAME ,
4919 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
4920 x_msg_count => p_msg_count ,
4921 x_msg_data => p_msg_data ,
4922 p_api_type => '_PVT'
4923 );
4924
4925 WHEN OTHERS THEN
4926 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
4927 p_pkg_name => G_PKG_NAME ,
4928 p_exc_name => 'OTHERS' ,
4929 x_msg_count => p_msg_count ,
4930 x_msg_data => p_msg_data ,
4931 p_api_type => '_PVT'
4932 );
4933
4934 END fetch_create_funding;
4935
4936
4937
4938 --
4939 -- Function: get_funding_rec
4940 --
4941 -- Description: This function initializes a record of funding_rec_in_type
4942 --
4943
4944 FUNCTION get_funding_rec RETURN FUNDING_REC_IN_TYPE is
4945 funding_in_rec FUNDING_REC_IN_TYPE;
4946 BEGIN
4947
4948 return funding_in_rec;
4949
4950 END get_funding_rec;
4951
4952 end OKE_FUNDSOURCE_PVT;