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