[Home] [Help]
PACKAGE BODY: APPS.OKE_FUNDING_PUB
Source
1 package body OKE_FUNDING_PUB as
2 /* $Header: OKEPKFDB.pls 120.1.12020000.2 2012/09/25 06:54:01 skuchima ship $ */
3
4 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_funding_pub.';
5
6 --
7 -- Private Procedures and Functions
8 --
9
10 --
11 -- Procedure: check_update_add_pa
12 --
13 -- Description: This procedure is used to check if update/insert is needed for project funding
14 --
15 --
16
17 FUNCTION check_update_add_pa(p_fund_allocation_id NUMBER) RETURN BOOLEAN is
18
19 cursor c_exist is
20 select 'Y'
21 from oke_k_fund_allocations
22 where fund_allocation_id = p_fund_allocation_id
23 and agreement_version is not null;
24
25 l_dummy_value VARCHAR2(1) := '?';
26
27 BEGIN
28
29 OPEN c_exist;
30 FETCH c_exist into l_dummy_value;
31 CLOSE c_exist;
32
33 IF (l_dummy_value = '?') THEN
34
35 return(FALSE);
36
37 ELSE
38
39 return(TRUE);
40
41 END IF;
42
43 CLOSE c_exist;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 OKE_API.set_message(p_app_name => G_APP_NAME ,
48 p_msg_name => G_UNEXPECTED_ERROR ,
49 p_token1 => G_SQLCODE_TOKEN ,
50 p_token1_value => SQLCODE ,
51 p_token2 => G_SQLERRM_TOKEN ,
52 p_token2_value => SQLERRM
53 );
54
55 IF (c_exist%ISOPEN) THEN
56 CLOSE c_exist;
57 END IF;
58
59 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
60
61 END check_update_add_pa;
62
63
64 --
65 -- Procedure: check_update_add
66 --
67 -- Description: This procedure is used to check if update/add is needed for allocation
68 --
69 --
70
71 FUNCTION check_update_add(p_fund_allocation_id NUMBER) RETURN BOOLEAN is
72
73 cursor c_update is
74 select 'x'
75 from oke_k_fund_allocations
76 where fund_allocation_id = p_fund_allocation_id;
77
78 l_dummy_value VARCHAR2(1) := '?';
79
80 BEGIN
81
82 OPEN c_update;
83 FETCH c_update into l_dummy_value;
84 CLOSE c_update;
85
86 IF (l_dummy_value = '?') THEN
87
88 return(FALSE);
89
90 ELSE
91
92 return(TRUE);
93
94 END IF;
95
96 CLOSE c_update;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 OKE_API.set_message(p_app_name => G_APP_NAME ,
101 p_msg_name => G_UNEXPECTED_ERROR ,
102 p_token1 => G_SQLCODE_TOKEN ,
103 p_token1_value => SQLCODE ,
104 p_token2 => G_SQLERRM_TOKEN ,
105 p_token2_value => SQLERRM
106 );
107
108 IF (c_update%ISOPEN) THEN
109 CLOSE c_update;
110 END IF;
111
112 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
113
114 END check_update_add;
115
116
117 --
118 -- Procedure: validate_agreement_id
119 --
120 -- Description: This procedure is used to validate the agreement_id
121 --
122 --
123
124 PROCEDURE validate_agreement_id(p_agreement_id NUMBER ,
125 p_funding_source_id NUMBER
126 ) is
127
128 cursor c_agreement(x_length number) is
129 select 'x'
130 from pa_agreements_all
131 where agreement_id = p_agreement_id
132 and pm_product_code = G_PRODUCT_CODE
133 and substr(pm_agreement_reference, -1 * x_length, x_length) = '-'|| p_funding_source_id;
134
135 l_dummy_value VARCHAR2(1) := '?';
136
137 BEGIN
138
139 IF (p_agreement_id is null) OR
140 (p_agreement_id = OKE_API.G_MISS_NUM) THEN
141
142 OKE_API.set_message(p_app_name => G_APP_NAME ,
143 p_msg_name => 'OKE_API_MISSING_VALUE' ,
144 p_token1 => 'VALUE' ,
145 p_token1_value => 'agreement_id'
146 );
147
148 RAISE OKE_API.G_EXCEPTION_ERROR;
149
150 END IF;
151
152 OPEN c_agreement((LENGTH(p_funding_source_id) + 1));
153 FETCH c_agreement into l_dummy_value;
154 CLOSE c_agreement;
155
156 IF (l_dummy_value = '?') THEN
157
158 OKE_API.set_message(p_app_name => G_APP_NAME ,
159 p_msg_name => 'OKE_API_INVALID_VALUE' ,
160 p_token1 => 'VALUE' ,
161 p_token1_value => 'agreement_id'
162 );
163
164 RAISE OKE_API.G_EXCEPTION_ERROR;
165
166 END IF;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 OKE_API.set_message(p_app_name => G_APP_NAME ,
171 p_msg_name => G_UNEXPECTED_ERROR ,
172 p_token1 => G_SQLCODE_TOKEN ,
173 p_token1_value => SQLCODE ,
174 p_token2 => G_SQLERRM_TOKEN ,
175 p_token2_value => SQLERRM
176 );
177
178 IF (c_agreement%ISOPEN) THEN
179 CLOSE c_agreement;
180 END IF;
181
182 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
183
184 END validate_agreement_id;
185
186
187 --
188 -- Procedure: get_record
189 --
190 -- Description: This procedure is used to retrieve the existing fund allocation record
191 --
192 --
193
194 FUNCTION get_record(p_fund_allocation_id NUMBER) RETURN ALLOCATION_REC_IN_TYPE is
195
196 cursor c_allocation is
197 select fund_allocation_id ,
198 funding_source_id ,
199 project_id ,
200 task_id ,
201 amount ,
202 start_date_active ,
203 pa_conversion_type ,
204 pa_conversion_date ,
205 pa_conversion_rate,
206 funding_category
207 from oke_k_fund_allocations
208 where fund_allocation_id = p_fund_allocation_id;
209
210 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
211
212 BEGIN
213
214 OPEN c_allocation;
215 FETCH c_allocation into l_allocation_in_rec.fund_allocation_id ,
216 l_allocation_in_rec.funding_source_id ,
217 l_allocation_in_rec.project_id ,
218 l_allocation_in_rec.task_id ,
219 l_allocation_in_rec.amount ,
220 l_allocation_in_rec.start_date_active ,
221 l_allocation_in_rec.pa_conversion_type ,
222 l_allocation_in_rec.pa_conversion_date ,
223 l_allocation_in_rec.pa_conversion_rate ,
224 l_allocation_in_rec.funding_category;
225 CLOSE c_allocation;
226
227 RETURN (l_allocation_in_rec);
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 OKE_API.set_message(p_app_name => G_APP_NAME ,
232 p_msg_name => G_UNEXPECTED_ERROR ,
233 p_token1 => G_SQLCODE_TOKEN ,
234 p_token1_value => SQLCODE ,
235 p_token2 => G_SQLERRM_TOKEN ,
236 p_token2_value => SQLERRM
237 );
238
239 IF (c_allocation%ISOPEN) THEN
240 CLOSE c_allocation;
241 END IF;
242
243 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
244
245 END get_record;
246
247
248
249
250
251 --
252 -- Public Procedures and Functions
253 --
254
255
256
257 --
258 -- Procedure: create_pa_oke_funding
259 --
260 -- Description: This procedure is used to create contract funding and pa agreement
261 --
262 -- Calling subprograms: OKE_API.start_activity
263 -- OKE_API.end_activity
264 -- OKE_FUNDSOURCE_PVT.fetch_create_funding
265 --
266
267 PROCEDURE create_pa_oke_funding(p_api_version IN NUMBER ,
268 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
269 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
270 x_return_status OUT NOCOPY VARCHAR2 ,
271 x_msg_count OUT NOCOPY NUMBER ,
272 x_msg_data OUT NOCOPY VARCHAR2 ,
273 x_funding_source_id OUT NOCOPY NUMBER ,
274 --p_source_currency IN VARCHAR2 ,
275 p_agreement_id IN NUMBER ,
276 p_party_id IN NUMBER ,
277 p_pool_party_id IN NUMBER ,
278 p_object_id IN NUMBER ,
279 --p_pa_conversion_type IN VARCHAR2 ,
280 --p_pa_conversion_date IN DATE ,
281 -- p_pa_conversion_rate IN NUMBER ,
282 p_oke_conversion_type IN VARCHAR2 ,
283 p_oke_conversion_date IN DATE ,
284 p_oke_conversion_rate IN NUMBER
285 ) is
286
287 l_api_name CONSTANT VARCHAR2(30) := 'create_pa_oke_funding';
288 l_return_status VARCHAR2(1);
289
290 BEGIN
291
292 x_return_status := OKE_API.G_RET_STS_SUCCESS;
293
294 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
295 p_pkg_name => G_PKG_NAME ,
296 p_init_msg_list => p_init_msg_list ,
297 l_api_version => G_API_VERSION_NUMBER ,
298 p_api_version => p_api_version ,
299 p_api_type => '_PUB' ,
300 x_return_status => x_return_status
301 );
302
303 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
304
305 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
306
307 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
308
309 RAISE OKE_API.G_EXCEPTION_ERROR;
310
311 END IF;
312
313 OKE_FUNDSOURCE_PVT.fetch_create_funding(p_init_msg_list => OKE_API.G_FALSE ,
314 p_api_version => p_api_version ,
315 p_msg_count => x_msg_count ,
316 p_msg_data => x_msg_data ,
317 p_commit => OKE_API.G_FALSE ,
318 p_pool_party_id => p_pool_party_id ,
319 p_party_id => p_party_id ,
320 --p_source_currency => p_source_currency ,
321 p_agreement_id => p_agreement_id ,
322 p_conversion_type => p_oke_conversion_type ,
323 p_conversion_date => p_oke_conversion_date ,
324 p_conversion_rate => p_oke_conversion_rate ,
325 --p_pa_conversion_type => p_pa_conversion_type ,
326 --p_pa_conversion_date => p_pa_conversion_date ,
327 --p_pa_conversion_rate => p_pa_conversion_rate ,
328 p_k_header_id => p_object_id ,
329 p_funding_source_id => x_funding_source_id ,
330 p_return_status => x_return_status
331 );
332
333 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
334
335 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
336
337 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
338
339 RAISE OKE_API.G_EXCEPTION_ERROR;
340
341 END IF;
342
343 IF FND_API.to_boolean(p_commit) THEN
344
345 COMMIT WORK;
346
347 END IF;
348
349 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
350 x_msg_data => x_msg_data
351 );
352
353 EXCEPTION
354 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
355 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
356 p_pkg_name => G_PKG_NAME ,
357 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
358 x_msg_count => x_msg_count ,
359 x_msg_data => x_msg_data ,
360 p_api_type => '_PUB'
361 );
362
363 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
364 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
365 p_pkg_name => G_PKG_NAME ,
366 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
367 x_msg_count => x_msg_count ,
368 x_msg_data => x_msg_data ,
369 p_api_type => '_PUB'
370 );
371
372 WHEN OTHERS THEN
373 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
374 p_pkg_name => G_PKG_NAME ,
375 p_exc_name => 'OTHERS' ,
376 x_msg_count => x_msg_count ,
377 x_msg_data => x_msg_data ,
378 p_api_type => '_PUB'
379 );
380 END create_pa_oke_funding;
381
382
383
384 --
385 -- Procedure: create_funding
386 --
387 -- Description: This procedure is used to create contract funding and pa agreement
388 --
389 -- Calling subprograms: OKE_API.start_activity
390 -- OKE_API.end_activity
391 -- OKE_FUNDSOURCE_PVT.create_funding
392 -- OKE_ALLOCATION_PVT.add_allocation
393 -- OKE_FUNDSOURCE_PVT.update_funding
394 -- OKE_AGREEMENT_PVT.create_agreement
395 --
396
397 PROCEDURE create_funding(p_api_version IN NUMBER ,
398 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
399 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
400 x_return_status OUT NOCOPY VARCHAR2 ,
401 x_msg_count OUT NOCOPY NUMBER ,
402 x_msg_data OUT NOCOPY VARCHAR2 ,
403 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
404 p_agreement_type IN VARCHAR2 ,
405 p_receivables_term_id IN NUMBER DEFAULT NULL, /*skuchima 14344021 */
406 p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
407 x_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
408 p_allocation_in_tbl IN ALLOCATION_IN_TBL_TYPE ,
409 x_allocation_out_tbl OUT NOCOPY ALLOCATION_OUT_TBL_TYPE
410 ) is
411
412 l_api_name CONSTANT VARCHAR2(30) := 'create_funding';
413 i NUMBER := 0;
414 l_return_status VARCHAR2(1);
415 l_allocation_in_rec allocation_rec_in_type;
416 l_allocation_out_rec allocation_rec_out_type;
417 l_funding_in_rec funding_rec_in_type;
418
422 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.create_funding');
419 BEGIN
420
421 --dbms_output.put_line('entering oke_funding_pub.create_funding');
423
424 x_return_status := OKE_API.G_RET_STS_SUCCESS;
425 x_funding_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
426
427 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
428 p_pkg_name => G_PKG_NAME ,
429 p_init_msg_list => p_init_msg_list ,
430 l_api_version => G_API_VERSION_NUMBER ,
431 p_api_version => p_api_version ,
432 p_api_type => '_PUB' ,
433 x_return_status => x_return_status
434 );
435
436 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
437
438 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
439
440 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
441
442 RAISE OKE_API.G_EXCEPTION_ERROR;
443
444 END IF;
445
446 --
447 -- Call OKE_FUNDSOURCE_PVT.create_funding to create contract funding
448 --
449
450 --dbms_output.put_line('calling oke_fundsource_pvt.create_funding from oke_funding_pub');
451 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_fundsource_pvt.create_funding from oke_funding_pub');
452
453 OKE_FUNDSOURCE_PVT.create_funding(p_api_version => p_api_version ,
454 p_init_msg_list => OKE_API.G_FALSE ,
455 p_commit => OKE_API.G_FALSE ,
456 p_msg_count => x_msg_count ,
457 p_msg_data => x_msg_data ,
458 p_funding_in_rec => p_funding_in_rec ,
459 p_funding_out_rec => x_funding_out_rec ,
460 p_return_status => x_return_status
461 );
462
463 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
464
465 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
466
467 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
468
469 RAISE OKE_API.G_EXCEPTION_ERROR;
470
471 END IF;
472
473 l_funding_in_rec := p_funding_in_rec;
474 l_funding_in_rec.funding_source_id := x_funding_out_rec.funding_source_id;
475
476 --
477 -- Call add_allocation to create contract funding allocation
478 --
479
480 --dbms_output.put_line('calling oke_funding_pub.add_allocation');
481 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_funding_pub.add_allocation');
482
483 IF (p_allocation_in_tbl.COUNT > 0 )THEN
484
485 i := p_allocation_in_tbl.FIRST;
486
487 LOOP
488
489 l_allocation_in_rec := p_allocation_in_tbl(i);
490 l_allocation_in_rec.funding_source_id := x_funding_out_rec.funding_source_id;
491
492 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
493 p_init_msg_list => OKE_API.G_FALSE ,
494 p_commit => OKE_API.G_FALSE ,
495 p_return_status => x_return_status ,
496 p_msg_count => x_msg_count ,
497 p_msg_data => x_msg_data ,
498 p_allocation_in_rec => l_allocation_in_rec ,
499 p_allocation_out_rec => l_allocation_out_rec ,
500 p_validation_flag => OKE_API.G_FALSE
501 );
502
503 x_allocation_out_tbl(i) := l_allocation_out_rec;
504
505 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
506
507 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
508
509 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
510
511 RAISE OKE_API.G_EXCEPTION_ERROR;
512
513 END IF;
514
515 EXIT WHEN (i = p_allocation_in_tbl.LAST);
516 i := p_allocation_in_tbl.NEXT(i);
517
518 END LOOP;
519
520 END IF;
521
522 --
523 -- Call OKE_FUNDSOURCE_PVT.update_funding to validate the entire funding record
524 --
525
526 OKE_FUNDSOURCE_PVT.update_funding(p_api_version => p_api_version ,
527 p_init_msg_list => OKE_API.G_FALSE ,
528 p_commit => OKE_API.G_FALSE ,
529 p_msg_count => x_msg_count ,
530 p_msg_data => x_msg_data ,
531 p_funding_in_rec => l_funding_in_rec ,
532 p_funding_out_rec => x_funding_out_rec ,
533 p_return_status => x_return_status
534 );
535
536 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
537
538 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
539
540 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
541
542 RAISE OKE_API.G_EXCEPTION_ERROR;
543
544 END IF;
545
546 --
547 -- Check for agreement creation option
548 --
549
550 IF (FND_API.to_boolean(p_agreement_flag)) THEN
551
552 --dbms_output.put_line('calling oke_agreement_pvt.create_agreement');
553 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.create_agreement');
554
555 OKE_AGREEMENT_PVT.create_agreement(p_api_version => G_API_VERSION_NUMBER ,
556 p_init_msg_list => OKE_API.G_FALSE ,
557 p_commit => OKE_API.G_FALSE ,
558 p_msg_count => x_msg_count ,
559 p_msg_data => x_msg_data ,
560 p_agreement_type => p_agreement_type ,
561 p_funding_in_rec => l_funding_in_rec ,
562 -- p_allocation_in_tbl => p_allocation_in_tbl ,
563 p_return_status => x_return_status ,
564 p_receivables_term_id => p_receivables_term_id
565 );
566
567 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
568
569 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
570
571 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
572
573 RAISE OKE_API.G_EXCEPTION_ERROR;
574
575 END IF;
576
577 END IF;
578
579 IF FND_API.to_boolean(p_commit) THEN
580
581 COMMIT WORK;
582
583 END IF;
584
585 --dbms_output.put_line('finished oke_funding_pub.create_funding w/ ' || x_return_status);
586 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.create_funding w/ ' || x_return_status);
587
588 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
589 x_msg_data => x_msg_data
590 );
591
592 EXCEPTION
593 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
594 x_funding_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
595 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
596 p_pkg_name => G_PKG_NAME ,
597 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
598 x_msg_count => x_msg_count ,
599 x_msg_data => x_msg_data ,
600 p_api_type => '_PUB'
601 );
602
603 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
604 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
605 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
606 p_pkg_name => G_PKG_NAME ,
607 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
608 x_msg_count => x_msg_count ,
609 x_msg_data => x_msg_data ,
610 p_api_type => '_PUB'
611 );
612
613 WHEN OTHERS THEN
614 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
615 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
616 p_pkg_name => G_PKG_NAME ,
617 p_exc_name => 'OTHERS' ,
618 x_msg_count => x_msg_count ,
619 x_msg_data => x_msg_data ,
620 p_api_type => '_PUB'
621 );
622
623 END create_funding;
624
625
626 --
627 -- Procedure: update_funding
628 --
629 -- Description: This procedure is used to update contract funding and pa agreement
630 --
631 -- Calling subprograms: OKE_API.start_activity
632 -- OKE_API.end_activity
633 -- OKE_FUNDSOURCE_PVT.update_funding
634 -- OKE_API.set_message
635 -- OKE_AGREEMENT_PVT.update_agreement
636 -- OKE_AGREEMENT_PVT.create_agreement
637 -- OKE_FUNDING_UTIL_PKG.check_agreement_exist
638 -- check_update_add
639 -- OKE_ALLOCATION_PVT.add_allocation
640 -- OKE_ALLOCATION_PVT.update_allocation
641 --
642
643 PROCEDURE update_funding(p_api_version IN NUMBER ,
644 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
645 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
646 x_return_status OUT NOCOPY VARCHAR2 ,
647 x_msg_count OUT NOCOPY NUMBER ,
648 x_msg_data OUT NOCOPY VARCHAR2 ,
649 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
650 p_agreement_type IN VARCHAR2 ,
651 p_receivables_term_id IN NUMBER DEFAULT NULL,
652 p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
653 x_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
654 p_allocation_in_tbl IN ALLOCATION_IN_TBL_TYPE ,
655 x_allocation_out_tbl OUT NOCOPY ALLOCATION_OUT_TBL_TYPE
656 ) is
657
658 l_api_name CONSTANT VARCHAR2(30) := 'update_funding';
659 l_return_status VARCHAR2(1);
660 l_allocation_in_rec allocation_rec_in_type;
661 l_allocation_out_rec allocation_rec_out_type;
662 i NUMBER := 0;
663 l_agreement_exist VARCHAR2(1);
664
665 cursor get_term is
666 select term_id
667 from pa_agreements_all where pm_product_code = 'OKE'
668 and pm_agreement_reference LIKE '%-' || to_char(p_funding_in_rec.funding_source_id);
669
670 l_receivables_term_id number;
671 BEGIN
672
673 --dbms_output.put_line('entering oke_funding_pub.update_funding');
674 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.update_funding');
675
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677 x_funding_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
678
679 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
680 p_pkg_name => G_PKG_NAME ,
681 p_init_msg_list => p_init_msg_list ,
682 l_api_version => G_API_VERSION_NUMBER ,
683 p_api_version => p_api_version ,
684 p_api_type => '_PUB' ,
685 x_return_status => x_return_status
686 );
687
688 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
689
690 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
691
692 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
693
694 RAISE OKE_API.G_EXCEPTION_ERROR;
695
696 END IF;
697
698 --
699 -- Call OKE_ALLOCATION_PVT.add_allocation and update_allocation to create/update contract funding allocation
700 --
701
702 --dbms_output.put_line('checking if add or update allocation');
703 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'checking if add or update allocation');
704
705 IF (p_allocation_in_tbl.COUNT >0 )THEN
706
707 i := p_allocation_in_tbl.FIRST;
708
709 LOOP
710
711 l_allocation_in_rec := p_allocation_in_tbl(i);
712
713 --
714 -- Check if funding source id of source = funding source id of allocation lines
715 --
716
717 IF (l_allocation_in_rec.funding_source_id <> p_funding_in_rec.funding_source_id) THEN
718
719 OKE_API.set_message(p_app_name => G_APP_NAME ,
720 p_msg_name => 'OKE_API_INVALID_VALUE' ,
721 p_token1 => 'VALUE' ,
722 p_token1_value => 'allocation.funding_source_id'
723 );
724
725 RAISE G_EXCEPTION_HALT_VALIDATION;
726
727 END IF;
728
729 IF (check_update_add(p_fund_allocation_id => l_allocation_in_rec.fund_allocation_id)) THEN
730
731 OKE_ALLOCATION_PVT.update_allocation(p_api_version => p_api_version ,
732 p_init_msg_list => OKE_API.G_FALSE ,
733 p_commit => OKE_API.G_FALSE ,
734 p_return_status => x_return_status ,
735 p_msg_count => x_msg_count ,
736 p_msg_data => x_msg_data ,
737 p_allocation_in_rec => l_allocation_in_rec ,
738 p_allocation_out_rec => l_allocation_out_rec ,
739 p_validation_flag => OKE_API.G_FALSE
740 );
741
742 ELSE
743
744 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
745 p_init_msg_list => OKE_API.G_FALSE ,
746 p_commit => OKE_API.G_FALSE ,
747 p_return_status => x_return_status ,
748 p_msg_count => x_msg_count ,
749 p_msg_data => x_msg_data ,
750 p_validation_flag => OKE_API.G_FALSE ,
751 p_allocation_in_rec => l_allocation_in_rec ,
752 p_allocation_out_rec => l_allocation_out_rec
753 );
754
755 END IF;
756
757 x_allocation_out_tbl(i) := l_allocation_out_rec;
758
759 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
760
761 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
762
763 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
764
765 RAISE OKE_API.G_EXCEPTION_ERROR;
766
767 END IF;
768
769 EXIT WHEN (i = p_allocation_in_tbl.LAST);
770 i := p_allocation_in_tbl.NEXT(i);
771
772 END LOOP;
773
774 END IF;
775
776 --
777 -- Call OKE_FUNDSOURCE_PVT.update_funding to update contract funding
778 --
779
780 --dbms_output.put_line('calling oke_fundsource_pvt.update_funding');
781 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_fundsource_pvt.update_funding');
782
783 OKE_FUNDSOURCE_PVT.update_funding(p_api_version => p_api_version ,
784 p_init_msg_list => OKE_API.G_FALSE ,
785 p_commit => OKE_API.G_FALSE ,
786 p_msg_count => x_msg_count ,
787 p_msg_data => x_msg_data ,
788 p_funding_in_rec => p_funding_in_rec ,
789 p_funding_out_rec => x_funding_out_rec ,
790 p_return_status => x_return_status );
791
792 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
793
794 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
795
796 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
797
798 RAISE OKE_API.G_EXCEPTION_ERROR;
799
800 END IF;
801
802 --
803 -- Check if agreement update is needed
804 --
805
806 IF (FND_API.to_boolean(p_agreement_flag)) THEN
807
808 OKE_FUNDING_UTIL_PKG.check_agreement_exist(x_funding_source_id => p_funding_in_rec.funding_source_id ,
809 x_return_status => l_agreement_exist
810 );
811 IF (l_agreement_exist = 'Y') THEN
812
813 --dbms_output.put_line('calling oke_agreement_pvt.update_agreement');
814 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.update_agreement');
815
816 open get_term;
817 fetch get_term into l_receivables_term_id;
818 close get_term;
819
820 OKE_AGREEMENT_PVT.update_agreement(p_api_version => p_api_version ,
821 p_init_msg_list => OKE_API.G_FALSE ,
822 p_commit => OKE_API.G_FALSE ,
823 p_msg_count => x_msg_count ,
824 p_msg_data => x_msg_data ,
825 p_agreement_type => p_agreement_type ,
826 p_funding_in_rec => p_funding_in_rec ,
827 -- p_allocation_in_tbl => p_allocation_in_tbl ,
828 p_return_status => x_return_status ,
829 p_receivables_term_id => l_receivables_term_id
830 );
831
832 ELSE
833
834 --dbms_output.put_line('calling oke_agreement_pvt.create_agreement');
835 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.create_agreement');
836
837 OKE_AGREEMENT_PVT.create_agreement(p_api_version => p_api_version ,
838 p_init_msg_list => OKE_API.G_FALSE ,
839 p_commit => OKE_API.G_FALSE ,
840 p_msg_count => x_msg_count ,
841 p_msg_data => x_msg_data ,
842 p_agreement_type => p_agreement_type ,
843 p_funding_in_rec => p_funding_in_rec ,
844 -- p_allocation_in_tbl => p_allocation_in_tbl ,
845 p_return_status => x_return_status ,
846 p_receivables_term_id => p_receivables_term_id
847 );
848
849 END IF;
850
851 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
852
853 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
854
855 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
856
857 RAISE OKE_API.G_EXCEPTION_ERROR;
858
859 END IF;
860
861 END IF;
862
863 IF FND_API.to_boolean(p_commit) THEN
864
865 COMMIT WORK;
866
867 END IF;
868
869 --dbms_output.put_line('finished oke_funding_pub.create_funding w/ ' || x_return_status);
870 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.create_funding w/ ' || x_return_status);
871
872 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
873 x_msg_data => x_msg_data
874 );
875
876 EXCEPTION
880 p_pkg_name => G_PKG_NAME ,
877 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
878 x_funding_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
879 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
881 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
882 x_msg_count => x_msg_count ,
883 x_msg_data => x_msg_data ,
884 p_api_type => '_PUB'
885 );
886
887 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
888 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
889 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
890 p_pkg_name => G_PKG_NAME ,
891 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
892 x_msg_count => x_msg_count ,
893 x_msg_data => x_msg_data ,
894 p_api_type => '_PUB'
895 );
896
897 WHEN OTHERS THEN
898 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
899 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
900 p_pkg_name => G_PKG_NAME ,
901 p_exc_name => 'OTHERS' ,
902 x_msg_count => x_msg_count ,
903 x_msg_data => x_msg_data ,
904 p_api_type => '_PUB'
905 );
906 END update_funding;
907
908
909 --
910 -- Procedure: delete_funding
911 --
912 -- Description: This procedure is used to delete contract funding and pa agreement
913 --
914 -- Calling subprograms: OKE_API.start_activity
915 -- OKE_API.end_activity
916 -- OKE_FUNDSOURCE_PVT.delete_funding
917 -- OKE_FUNDING_PUB.delete_allocation
918 --
919
920 PROCEDURE delete_funding(p_api_version IN NUMBER ,
921 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
922 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
923 x_return_status OUT NOCOPY VARCHAR2 ,
924 x_msg_count OUT NOCOPY NUMBER ,
925 x_msg_data OUT NOCOPY VARCHAR2 ,
926 p_funding_source_id IN NUMBER
927 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE
928 ) is
929
930 -- l_length NUMBER;
931 -- l_temp_val VARCHAR2(1) :='?';
932
933 cursor c_fund_allocation_id (p_funding_source_id NUMBER) is
934 select fund_allocation_id
935 from oke_k_fund_allocations
936 where funding_source_id = p_funding_source_id
937 order by amount asc;
938
939 l_allocation_id c_fund_allocation_id%ROWTYPE;
940 l_api_name CONSTANT VARCHAR2(30) := 'delete_funding';
941 l_return_status VARCHAR2(1);
942
943 BEGIN
944
945 --dbms_output.put_line('entering oke_funding_pub.delete_funding');
946 -- --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.delete_funding');
947
948 x_return_status := OKE_API.G_RET_STS_SUCCESS;
949
950 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
951 p_pkg_name => G_PKG_NAME ,
952 p_init_msg_list => p_init_msg_list ,
953 l_api_version => G_API_VERSION_NUMBER ,
954 p_api_version => p_api_version ,
955 p_api_type => '_PUB' ,
956 x_return_status => x_return_status
957 );
958
959 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
960
961 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
962
963 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
964
965 RAISE OKE_API.G_EXCEPTION_ERROR;
966
967 END IF;
968
969 --
970 -- Call OKE_FUNDING_PUB to delete contract funding allocation
971 --
972
973 --dbms_output.put_line('in loop: calling delete_allocation');
974 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'in loop: calling delete_allocation');
975
976 FOR l_allocation_id IN c_fund_allocation_id(p_funding_source_id) LOOP
977
978 OKE_ALLOCATION_PVT.delete_allocation(p_api_version => p_api_version ,
979 p_init_msg_list => OKE_API.G_FALSE ,
980 p_commit => OKE_API.G_FALSE ,
981 p_return_status => x_return_status ,
982 p_msg_count => x_msg_count ,
983 p_msg_data => x_msg_data ,
984 p_fund_allocation_id => l_allocation_id.fund_allocation_id
985 );
986
987 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
988
989 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
990
991 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
992
993 RAISE OKE_API.G_EXCEPTION_ERROR;
994
995 END IF;
996
997 END LOOP;
998
999 --dbms_output.put_line('finished delete_allocation');
1000 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished delete_allocation');
1001
1002 --
1003 -- Call OKE_FUNDSOURCE_PVT.delete_funding to delete contract funding
1004 --
1005
1006 --dbms_output.put_line('calling oke_fundsource_pvt.delete_funding');
1007 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_fundsource_pvt.delete_funding');
1008
1009 OKE_FUNDSOURCE_PVT.delete_funding(p_api_version => p_api_version ,
1010 p_commit => OKE_API.G_FALSE ,
1011 p_init_msg_list => OKE_API.G_FALSE ,
1012 p_msg_count => x_msg_count ,
1013 p_msg_data => x_msg_data ,
1017 );
1014 p_funding_source_id => p_funding_source_id ,
1015 -- p_agreement_flag => p_agreement_flag ,
1016 p_return_status => x_return_status
1018
1019 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1020
1021 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1022
1023 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1024
1025 RAISE OKE_API.G_EXCEPTION_ERROR;
1026
1027 END IF;
1028
1029 --dbms_output.put_line('finished oke_funding_pub.delete_funding w/ ' || x_return_status);
1030 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.delete_funding w/ ' || x_return_status);
1031
1032 IF FND_API.to_boolean(p_commit) THEN
1033
1034 COMMIT WORK;
1035
1036 END IF;
1037
1038 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1039 x_msg_data => x_msg_data
1040 );
1041
1042 EXCEPTION
1043 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1044 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1045 p_pkg_name => G_PKG_NAME ,
1046 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1047 x_msg_count => x_msg_count ,
1048 x_msg_data => x_msg_data ,
1049 p_api_type => '_PUB'
1050 );
1051
1052 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1053 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1054 p_pkg_name => G_PKG_NAME ,
1055 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1056 x_msg_count => x_msg_count ,
1057 x_msg_data => x_msg_data ,
1058 p_api_type => '_PUB'
1059 );
1060
1061 WHEN OTHERS THEN
1062 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1063 p_pkg_name => G_PKG_NAME ,
1064 p_exc_name => 'OTHERS' ,
1065 x_msg_count => x_msg_count ,
1066 x_msg_data => x_msg_data ,
1067 p_api_type => '_PUB'
1068 );
1069 END delete_funding;
1070
1071
1072
1073 --
1074 -- Procedure: add_allocation
1075 --
1076 -- Description: This procedure is used to create funding allocation and update/add pa funding
1077 --
1078 -- Calling subprograms: OKE_API.start_activity
1079 -- OKE_API.end_activity
1080 -- OKE_ALLOCATION_PVT.add_allocation
1081 -- OKE_AGREEMENT_PVT.add_pa_funding
1082 --
1083
1084 PROCEDURE add_allocation(p_api_version IN NUMBER ,
1085 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
1086 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
1087 x_return_status OUT NOCOPY VARCHAR2 ,
1088 x_msg_count OUT NOCOPY NUMBER ,
1089 x_msg_data OUT NOCOPY VARCHAR2 ,
1090 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
1091 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
1092 x_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE
1093 ) is
1094
1095 l_api_name CONSTANT VARCHAR2(30) := 'add_allocation';
1096 l_return_status VARCHAR2(1);
1097 l_allocation_in_rec ALLOCATION_REC_IN_TYPE := p_allocation_in_rec;
1098
1099 BEGIN
1100
1101 --dbms_output.put_line('entering oke_funding_pub.add_allocation');
1102 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.add_allocation');
1103
1104 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1105 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
1106
1107 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
1108 p_pkg_name => G_PKG_NAME ,
1109 p_init_msg_list => p_init_msg_list ,
1110 l_api_version => G_API_VERSION_NUMBER ,
1111 p_api_version => p_api_version ,
1112 p_api_type => '_PUB' ,
1113 x_return_status => x_return_status
1114 );
1115
1116 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1117
1118 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1119
1120 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1121
1122 RAISE OKE_API.G_EXCEPTION_ERROR;
1123
1124 END IF;
1125
1126 --
1127 -- Call OKE_ALLOCATION_PVT.add_allocation
1128 --
1129
1130 --dbms_output.put_line('calling oke_allocation_pvt.add_allocation from oke_funding_pub');
1131 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_allocation_pvt.add_allocation from oke_funding_pub');
1132
1133 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
1134 p_init_msg_list => OKE_API.G_FALSE ,
1135 p_commit => OKE_API.G_FALSE ,
1136 p_msg_count => x_msg_count ,
1137 p_msg_data => x_msg_data ,
1138 p_allocation_in_rec => p_allocation_in_rec ,
1139 p_allocation_out_rec => x_allocation_out_rec ,
1140 p_validation_flag => OKE_API.G_TRUE ,
1141 p_return_status => x_return_status
1142 );
1143
1144 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1145
1146 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1147
1148 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1149
1150 RAISE OKE_API.G_EXCEPTION_ERROR;
1151
1152 END IF;
1153
1154 --
1155 -- Set the fund_allocation_id
1156 --
1157 l_allocation_in_rec.fund_allocation_id := x_allocation_out_rec.fund_allocation_id;
1158
1159 --
1160 -- Check if agreement update is needed
1164
1161 --
1162
1163 IF (FND_API.to_boolean(p_agreement_flag)) THEN
1165 --
1166 -- Validate agreement_id
1167 --
1168 validate_agreement_id(p_agreement_id => p_allocation_in_rec.agreement_id ,
1169 p_funding_source_id => p_allocation_in_rec.funding_source_id
1170 );
1171
1172 --
1173 -- Get the allocation record
1174 --
1175 l_allocation_in_rec := get_record(p_fund_allocation_id => l_allocation_in_rec.fund_allocation_id);
1176 l_allocation_in_rec.agreement_id := p_allocation_in_rec.agreement_id;
1177
1178 --dbms_output.put_line('calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1179 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1180
1181 OKE_AGREEMENT_PVT.add_pa_funding(p_api_version => p_api_version ,
1182 p_init_msg_list => OKE_API.G_FALSE ,
1183 p_commit => OKE_API.G_FALSE ,
1184 p_msg_count => x_msg_count ,
1185 p_msg_data => x_msg_data ,
1186 p_allocation_in_rec => l_allocation_in_rec ,
1187 p_return_status => x_return_status
1188 );
1189
1190 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1191
1192 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1193
1194 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1195
1196 RAISE OKE_API.G_EXCEPTION_ERROR;
1197
1198 END IF;
1199
1200 END IF;
1201
1202 --dbms_output.put_line('finished oke_funding_pub.add_allocation w/ ' || x_return_status);
1203 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.add_allocation w/ ' || x_return_status);
1204
1205 IF FND_API.to_boolean(p_commit) THEN
1206
1207 COMMIT WORK;
1208
1209 END IF;
1210
1211 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1212 x_msg_data => x_msg_data
1213 );
1214
1215 EXCEPTION
1216 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1217 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
1218 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1219 p_pkg_name => G_PKG_NAME ,
1220 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1221 x_msg_count => x_msg_count ,
1222 x_msg_data => x_msg_data ,
1223 p_api_type => '_PUB'
1224 );
1225
1226 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1227 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1228 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1229 p_pkg_name => G_PKG_NAME ,
1230 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1231 x_msg_count => x_msg_count ,
1232 x_msg_data => x_msg_data ,
1233 p_api_type => '_PUB'
1234 );
1235
1236 WHEN OTHERS THEN
1237 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1238 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1239 p_pkg_name => G_PKG_NAME ,
1240 p_exc_name => 'OTHERS' ,
1241 x_msg_count => x_msg_count ,
1242 x_msg_data => x_msg_data ,
1243 p_api_type => '_PUB'
1244 );
1245 END add_allocation;
1246
1247
1248 --
1249 -- Procedure: update_allocation
1250 --
1251 -- Description: This procedure is used to update contract funding allocation and pa funding line
1252 --
1253 -- Calling subprograms: OKE_API.start_activity
1254 -- OKE_API.end_activity
1255 -- OKE_ALLOCATION_PVT.update_allocation
1256 -- OKE_AGREEMENT_PVT.update_pa_funding
1257 -- OKE_AGREEMENT_PVT.add_pa_funding
1258 -- check_update_add_pa
1259 --
1260
1261 PROCEDURE update_allocation(p_api_version IN NUMBER ,
1262 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
1263 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
1264 x_return_status OUT NOCOPY VARCHAR2 ,
1265 x_msg_count OUT NOCOPY NUMBER ,
1266 x_msg_data OUT NOCOPY VARCHAR2 ,
1267 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
1268 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
1269 x_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE
1270 ) is
1271
1272 l_api_name CONSTANT VARCHAR2(30) := 'update_allocation';
1273 l_return_status VARCHAR2(1);
1274 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
1275
1276 BEGIN
1277
1278 --dbms_output.put_line('entering oke_funding_pub.update_allocation');
1279 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.update_allocation');
1280
1281 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1282 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
1283
1284 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
1285 p_pkg_name => G_PKG_NAME ,
1286 p_init_msg_list => p_init_msg_list ,
1287 l_api_version => G_API_VERSION_NUMBER ,
1288 p_api_version => p_api_version ,
1289 p_api_type => '_PUB' ,
1290 x_return_status => x_return_status
1291 );
1292
1293 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1294
1295 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1296
1297 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1298
1302
1299 RAISE OKE_API.G_EXCEPTION_ERROR;
1300
1301 END IF;
1303 x_allocation_out_rec.fund_allocation_id := p_allocation_in_rec.fund_allocation_id;
1304
1305 --
1306 -- Call OKE_ALLOCATION_PVT.update_allocation to update the allocation line
1307 --
1308
1309 --dbms_output.put_line('calling oke_allocation_pvt.update_allocation from oke_funding_pub');
1310 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_allocation_pvt.update_allocation from oke_funding_pub');
1311
1312 OKE_ALLOCATION_PVT.update_allocation(p_api_version => p_api_version ,
1313 p_init_msg_list => OKE_API.G_FALSE ,
1314 p_commit => OKE_API.G_FALSE ,
1315 p_msg_count => x_msg_count ,
1316 p_msg_data => x_msg_data ,
1317 p_allocation_in_rec => p_allocation_in_rec ,
1318 p_allocation_out_rec => x_allocation_out_rec ,
1319 p_validation_flag => OKE_API.G_TRUE ,
1320 p_return_status => x_return_status
1321 );
1322
1323 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1324
1325 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1326
1327 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1328
1329 RAISE OKE_API.G_EXCEPTION_ERROR;
1330
1331 END IF;
1332
1333 --
1334 -- Check if agreement update is needed
1335 --
1336
1337 IF (FND_API.to_boolean(p_agreement_flag)) THEN
1338
1339 --
1340 -- Validate agreement_id
1341 --
1342 validate_agreement_id(p_agreement_id => p_allocation_in_rec.agreement_id ,
1343 p_funding_source_id => p_allocation_in_rec.funding_source_id
1344 );
1345
1346 --
1347 -- Get the allocation record
1348 --
1349 l_allocation_in_rec := get_record(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id);
1350 l_allocation_in_rec.agreement_id := p_allocation_in_rec.agreement_id;
1351
1352 --dbms_output.put_line('check if it is a update or add in pa');
1353 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'check if it is a update or add in pa');
1354
1355 IF (check_update_add_pa(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id)) THEN
1356
1357 --dbms_output.put_line('calling oke_agreement_pvt.update_pa_funding from oke_funding_pub');
1358 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.update_pa_funding from oke_funding_pub');
1359
1360 OKE_AGREEMENT_PVT.update_pa_funding(p_api_version => p_api_version ,
1361 p_init_msg_list => OKE_API.G_FALSE ,
1362 p_commit => OKE_API.G_FALSE ,
1363 p_msg_count => x_msg_count ,
1364 p_msg_data => x_msg_data ,
1365 p_allocation_in_rec => l_allocation_in_rec ,
1366 p_return_status => x_return_status
1367 );
1368 ELSE
1369
1370 --dbms_output.put_line('calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1371 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1372
1373 OKE_AGREEMENT_PVT.add_pa_funding(p_api_version => p_api_version ,
1374 p_init_msg_list => OKE_API.G_FALSE ,
1375 p_commit => OKE_API.G_FALSE ,
1376 p_msg_count => x_msg_count ,
1377 p_msg_data => x_msg_data ,
1378 p_allocation_in_rec => l_allocation_in_rec ,
1379 p_return_status => x_return_status
1380 );
1381
1382 END IF;
1383
1384 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1385
1386 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1387
1388 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1389
1390 RAISE OKE_API.G_EXCEPTION_ERROR;
1391
1392 END IF;
1393
1394 END IF;
1395
1396 --dbms_output.put_line('finished oke_funding_pub.update_allocation w/ ' || x_return_status);
1397 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.update_allocation w/ ' || x_return_status);
1398
1399 IF FND_API.to_boolean(p_commit) THEN
1400
1401 COMMIT WORK;
1402
1403 END IF;
1404
1405 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1406 x_msg_data => x_msg_data
1407 );
1408
1409 EXCEPTION
1410 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1411 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
1412 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1413 p_pkg_name => G_PKG_NAME ,
1414 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1415 x_msg_count => x_msg_count ,
1416 x_msg_data => x_msg_data ,
1417 p_api_type => '_PUB'
1418 );
1419
1420 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1421 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1422 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1423 p_pkg_name => G_PKG_NAME ,
1424 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1425 x_msg_count => x_msg_count ,
1426 x_msg_data => x_msg_data ,
1427 p_api_type => '_PUB'
1428 );
1429
1430 WHEN OTHERS THEN
1431 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1435 x_msg_count => x_msg_count ,
1432 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1433 p_pkg_name => G_PKG_NAME ,
1434 p_exc_name => 'OTHERS' ,
1436 x_msg_data => x_msg_data ,
1437 p_api_type => '_PUB'
1438 );
1439 END update_allocation;
1440
1441
1442
1443 --
1444 -- Procedure: delete_allocation
1445 --
1446 -- Description: This procedure is used to delete contract funding allocation and pa project_funding
1447 --
1448 -- Calling subprograms: OKE_API.start_activity
1449 -- OKE_API.end_activity
1450 -- OKE_ALLOCATION_PVT.delete_allocation
1451 --
1452
1453 PROCEDURE delete_allocation(p_api_version IN NUMBER ,
1454 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
1455 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
1456 x_return_status OUT NOCOPY VARCHAR2 ,
1457 x_msg_count OUT NOCOPY NUMBER ,
1458 x_msg_data OUT NOCOPY VARCHAR2 ,
1459 p_fund_allocation_id IN NUMBER
1460 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE
1461 ) is
1462
1463 l_api_name CONSTANT VARCHAR2(30) := 'delete_allocation';
1464 l_return_status VARCHAR2(1);
1465 -- l_temp_val VARCHAR2(1) := '?';
1466
1467 cursor c_source is
1468 select s.amount, s.hard_limit, s.revenue_hard_limit, s.funding_source_id
1469 from oke_k_funding_sources s,
1470 oke_k_fund_allocations f
1471 where s.funding_source_id = f.funding_source_id
1472 and f.fund_allocation_id = p_fund_allocation_id;
1473
1474 cursor c_allocation (x_funding_source_id number)is
1475 select sum(amount), sum(hard_limit), sum(revenue_hard_limit)
1476 from oke_k_fund_allocations
1477 where funding_source_id = x_funding_source_id;
1478
1479 l_funding_source_id NUMBER;
1480 l_s_amount NUMBER;
1481 l_s_hard_limit NUMBER;
1482 l_s_revenue_limit NUMBER;
1483 l_a_amount NUMBER;
1484 l_a_hard_limit NUMBER;
1485 l_a_revenue_limit NUMBER;
1486
1487 BEGIN
1488
1489 --dbms_output.put_line('entering oke_funding_pub.delete_allocation');
1490 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.delete_allocation');
1491
1492 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1493
1494 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
1495 p_pkg_name => G_PKG_NAME ,
1496 p_init_msg_list => p_init_msg_list ,
1497 l_api_version => G_API_VERSION_NUMBER ,
1498 p_api_version => p_api_version ,
1499 p_api_type => '_PUB' ,
1500 x_return_status => x_return_status
1501 );
1502
1503 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1504
1505 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1506
1507 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1508
1509 RAISE OKE_API.G_EXCEPTION_ERROR;
1510
1511 END IF;
1512
1513 --
1514 -- Check if it is ok to delete allocation
1515 --
1516 OPEN c_source;
1517 FETCH c_source INTO l_s_amount, l_s_hard_limit, l_s_revenue_limit, l_funding_source_id;
1518 IF (c_source%NOTFOUND) THEN
1519
1520 CLOSE c_source;
1521 OKE_API.set_message(p_app_name => 'OKE' ,
1522 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1523 p_token1 => 'VALUE' ,
1524 p_token1_value => 'fund_allocation_id'
1525 );
1526
1527 RAISE G_EXCEPTION_HALT_VALIDATION;
1528
1529 END IF;
1530 CLOSE c_source;
1531
1532 --
1533 -- Call OKE_ALLOCATION_PVT.delete_allocation to delete funding allocation line
1534 --
1535
1536 --dbms_output.put_line('calling oke_allocation_pvt.delete_allocation from oke_funding_pub');
1537 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_allocation_pvt.delete_allocation from oke_funding_pub');
1538
1539 OKE_ALLOCATION_PVT.delete_allocation(p_api_version => p_api_version ,
1540 p_commit => OKE_API.G_FALSE ,
1541 p_init_msg_list => OKE_API.G_FALSE ,
1542 p_msg_count => x_msg_count ,
1543 p_msg_data => x_msg_data ,
1544 p_fund_allocation_id => p_fund_allocation_id ,
1545 p_return_status => x_return_status
1546 );
1547
1548 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1549
1550 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1551
1552 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1553
1554 RAISE OKE_API.G_EXCEPTION_ERROR;
1555
1556 END IF;
1557
1558 --
1559 -- Check sum of allocations
1560 --
1561 OPEN c_allocation(l_funding_source_id);
1562 FETCH c_allocation INTO l_a_amount, l_a_hard_limit, l_a_revenue_limit;
1563 IF (c_allocation%NOTFOUND) THEN
1564
1565 l_a_amount := 0;
1566 l_a_hard_limit := 0;
1567 l_a_revenue_limit := 0;
1568
1569 END IF;
1570
1571 CLOSE c_allocation;
1572
1573 IF (l_a_amount < 0) THEN
1574
1575 OKE_API.set_message(p_app_name => 'OKE' ,
1576 p_msg_name => 'OKE_NEGATIVE_ALLOCATION_SUM'
1577 );
1578
1579 RAISE G_EXCEPTION_HALT_VALIDATION;
1580
1581 ELSIF (l_a_amount > l_s_amount) THEN
1582
1583 OKE_API.set_message(p_app_name => 'OKE' ,
1584 p_msg_name => 'OKE_FUND_AMT_EXCEED'
1585 );
1586
1587 RAISE G_EXCEPTION_HALT_VALIDATION;
1588
1589 END IF;
1590
1591 IF (l_a_hard_limit < 0) THEN
1592
1593 OKE_API.set_message(p_app_name => 'OKE' ,
1594 p_msg_name => 'OKE_NEGATIVE_HARD_LIMIT_SUM'
1595 );
1596
1597 RAISE G_EXCEPTION_HALT_VALIDATION;
1598
1599 ELSIF (l_a_hard_limit > l_s_hard_limit) THEN
1600
1601 OKE_API.set_message(p_app_name => 'OKE' ,
1602 p_msg_name => 'OKE_HARD_LIMIT_EXCEED'
1603 );
1604
1605 RAISE G_EXCEPTION_HALT_VALIDATION;
1606
1607 END IF;
1608
1609 IF (l_a_revenue_limit < 0) THEN
1610
1611 OKE_API.set_message(p_app_name => 'OKE' ,
1612 p_msg_name => 'OKE_NEGATIVE_REV_LIMIT_SUM'
1613 );
1614
1615 RAISE G_EXCEPTION_HALT_VALIDATION;
1616
1617 ELSIF (l_a_revenue_limit > l_s_revenue_limit) THEN
1618
1619 OKE_API.set_message(p_app_name => 'OKE' ,
1620 p_msg_name => 'OKE_REV_LIMIT_EXCEED'
1621 );
1622
1623 RAISE G_EXCEPTION_HALT_VALIDATION;
1624
1625 END IF;
1626
1627 --dbms_output.put_line('finished oke_funding_pub.delete_allocation w/ ' || x_return_status);
1628 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.delete_allocation w/ ' || x_return_status);
1629
1630 IF FND_API.to_boolean(p_commit) THEN
1631
1632 COMMIT WORK;
1633
1634 END IF;
1635
1636 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1637 x_msg_data => x_msg_data
1638 );
1639
1640 EXCEPTION
1641 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1642 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1643 p_pkg_name => G_PKG_NAME ,
1644 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1645 x_msg_count => x_msg_count ,
1646 x_msg_data => x_msg_data ,
1647 p_api_type => '_PUB'
1648 );
1649
1650 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1651 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1652 p_pkg_name => G_PKG_NAME ,
1653 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1654 x_msg_count => x_msg_count ,
1655 x_msg_data => x_msg_data ,
1656 p_api_type => '_PUB'
1657 );
1658
1659 WHEN OTHERS THEN
1660 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1661 p_pkg_name => G_PKG_NAME ,
1662 p_exc_name => 'OTHERS' ,
1663 x_msg_count => x_msg_count ,
1664 x_msg_data => x_msg_data ,
1665 p_api_type => '_PUB'
1666 );
1667 END delete_allocation;
1668
1669 end OKE_FUNDING_PUB;
1670