[Home] [Help]
PACKAGE BODY: APPS.OKE_FUNDING_PUB
Source
1 package body OKE_FUNDING_PUB as
2 /* $Header: OKEPKFDB.pls 120.0.12000000.2 2007/02/19 21:20:53 ifilimon 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_funding_in_rec IN FUNDING_REC_IN_TYPE ,
406 x_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
407 p_allocation_in_tbl IN ALLOCATION_IN_TBL_TYPE ,
408 x_allocation_out_tbl OUT NOCOPY ALLOCATION_OUT_TBL_TYPE
409 ) is
410
411 l_api_name CONSTANT VARCHAR2(30) := 'create_funding';
412 i NUMBER := 0;
413 l_return_status VARCHAR2(1);
414 l_allocation_in_rec allocation_rec_in_type;
415 l_allocation_out_rec allocation_rec_out_type;
416 l_funding_in_rec funding_rec_in_type;
417
418 BEGIN
419
420 --dbms_output.put_line('entering oke_funding_pub.create_funding');
421 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.create_funding');
422
423 x_return_status := OKE_API.G_RET_STS_SUCCESS;
424 x_funding_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
425
426 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
427 p_pkg_name => G_PKG_NAME ,
428 p_init_msg_list => p_init_msg_list ,
429 l_api_version => G_API_VERSION_NUMBER ,
430 p_api_version => p_api_version ,
431 p_api_type => '_PUB' ,
432 x_return_status => x_return_status
433 );
434
435 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
436
437 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
438
439 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
440
441 RAISE OKE_API.G_EXCEPTION_ERROR;
442
443 END IF;
444
445 --
446 -- Call OKE_FUNDSOURCE_PVT.create_funding to create contract funding
447 --
448
449 --dbms_output.put_line('calling oke_fundsource_pvt.create_funding from oke_funding_pub');
450 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_fundsource_pvt.create_funding from oke_funding_pub');
451
452 OKE_FUNDSOURCE_PVT.create_funding(p_api_version => p_api_version ,
453 p_init_msg_list => OKE_API.G_FALSE ,
454 p_commit => OKE_API.G_FALSE ,
455 p_msg_count => x_msg_count ,
456 p_msg_data => x_msg_data ,
457 p_funding_in_rec => p_funding_in_rec ,
458 p_funding_out_rec => x_funding_out_rec ,
459 p_return_status => x_return_status
463
460 );
461
462 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
464 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
465
466 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
467
468 RAISE OKE_API.G_EXCEPTION_ERROR;
469
470 END IF;
471
472 l_funding_in_rec := p_funding_in_rec;
473 l_funding_in_rec.funding_source_id := x_funding_out_rec.funding_source_id;
474
475 --
476 -- Call add_allocation to create contract funding allocation
477 --
478
479 --dbms_output.put_line('calling oke_funding_pub.add_allocation');
480 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_funding_pub.add_allocation');
481
482 IF (p_allocation_in_tbl.COUNT > 0 )THEN
483
484 i := p_allocation_in_tbl.FIRST;
485
486 LOOP
487
488 l_allocation_in_rec := p_allocation_in_tbl(i);
489 l_allocation_in_rec.funding_source_id := x_funding_out_rec.funding_source_id;
490
491 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
492 p_init_msg_list => OKE_API.G_FALSE ,
493 p_commit => OKE_API.G_FALSE ,
494 p_return_status => x_return_status ,
495 p_msg_count => x_msg_count ,
496 p_msg_data => x_msg_data ,
497 p_allocation_in_rec => l_allocation_in_rec ,
498 p_allocation_out_rec => l_allocation_out_rec ,
499 p_validation_flag => OKE_API.G_FALSE
500 );
501
502 x_allocation_out_tbl(i) := l_allocation_out_rec;
503
504 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
505
506 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
507
508 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
509
510 RAISE OKE_API.G_EXCEPTION_ERROR;
511
512 END IF;
513
514 EXIT WHEN (i = p_allocation_in_tbl.LAST);
515 i := p_allocation_in_tbl.NEXT(i);
516
517 END LOOP;
518
519 END IF;
520
521 --
522 -- Call OKE_FUNDSOURCE_PVT.update_funding to validate the entire funding record
523 --
524
525 OKE_FUNDSOURCE_PVT.update_funding(p_api_version => p_api_version ,
526 p_init_msg_list => OKE_API.G_FALSE ,
527 p_commit => OKE_API.G_FALSE ,
528 p_msg_count => x_msg_count ,
529 p_msg_data => x_msg_data ,
530 p_funding_in_rec => l_funding_in_rec ,
531 p_funding_out_rec => x_funding_out_rec ,
532 p_return_status => x_return_status
533 );
534
535 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
536
537 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
538
539 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
540
541 RAISE OKE_API.G_EXCEPTION_ERROR;
542
543 END IF;
544
545 --
546 -- Check for agreement creation option
547 --
548
549 IF (FND_API.to_boolean(p_agreement_flag)) THEN
550
551 --dbms_output.put_line('calling oke_agreement_pvt.create_agreement');
552 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.create_agreement');
553
554 OKE_AGREEMENT_PVT.create_agreement(p_api_version => G_API_VERSION_NUMBER ,
555 p_init_msg_list => OKE_API.G_FALSE ,
556 p_commit => OKE_API.G_FALSE ,
557 p_msg_count => x_msg_count ,
558 p_msg_data => x_msg_data ,
559 p_agreement_type => p_agreement_type ,
560 p_funding_in_rec => l_funding_in_rec ,
561 -- p_allocation_in_tbl => p_allocation_in_tbl ,
562 p_return_status => x_return_status
563 );
564
565 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
566
567 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
568
569 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
570
571 RAISE OKE_API.G_EXCEPTION_ERROR;
572
573 END IF;
574
575 END IF;
576
577 IF FND_API.to_boolean(p_commit) THEN
578
579 COMMIT WORK;
580
581 END IF;
582
583 --dbms_output.put_line('finished oke_funding_pub.create_funding w/ ' || x_return_status);
584 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.create_funding w/ ' || x_return_status);
585
586 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
587 x_msg_data => x_msg_data
588 );
589
590 EXCEPTION
591 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
592 x_funding_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
593 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
594 p_pkg_name => G_PKG_NAME ,
595 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
596 x_msg_count => x_msg_count ,
597 x_msg_data => x_msg_data ,
598 p_api_type => '_PUB'
599 );
600
601 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
602 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
606 x_msg_count => x_msg_count ,
603 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
604 p_pkg_name => G_PKG_NAME ,
605 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
607 x_msg_data => x_msg_data ,
608 p_api_type => '_PUB'
609 );
610
611 WHEN OTHERS THEN
612 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
613 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
614 p_pkg_name => G_PKG_NAME ,
615 p_exc_name => 'OTHERS' ,
616 x_msg_count => x_msg_count ,
617 x_msg_data => x_msg_data ,
618 p_api_type => '_PUB'
619 );
620
621 END create_funding;
622
623
624 --
625 -- Procedure: update_funding
626 --
627 -- Description: This procedure is used to update contract funding and pa agreement
628 --
629 -- Calling subprograms: OKE_API.start_activity
630 -- OKE_API.end_activity
631 -- OKE_FUNDSOURCE_PVT.update_funding
632 -- OKE_API.set_message
633 -- OKE_AGREEMENT_PVT.update_agreement
634 -- OKE_AGREEMENT_PVT.create_agreement
635 -- OKE_FUNDING_UTIL_PKG.check_agreement_exist
636 -- check_update_add
637 -- OKE_ALLOCATION_PVT.add_allocation
638 -- OKE_ALLOCATION_PVT.update_allocation
639 --
640
641 PROCEDURE update_funding(p_api_version IN NUMBER ,
642 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
643 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
644 x_return_status OUT NOCOPY VARCHAR2 ,
645 x_msg_count OUT NOCOPY NUMBER ,
646 x_msg_data OUT NOCOPY VARCHAR2 ,
647 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
648 p_agreement_type IN VARCHAR2 ,
649 p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
650 x_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
651 p_allocation_in_tbl IN ALLOCATION_IN_TBL_TYPE ,
652 x_allocation_out_tbl OUT NOCOPY ALLOCATION_OUT_TBL_TYPE
653 ) is
654
655 l_api_name CONSTANT VARCHAR2(30) := 'update_funding';
656 l_return_status VARCHAR2(1);
657 l_allocation_in_rec allocation_rec_in_type;
658 l_allocation_out_rec allocation_rec_out_type;
659 i NUMBER := 0;
660 l_agreement_exist VARCHAR2(1);
661
662 BEGIN
663
664 --dbms_output.put_line('entering oke_funding_pub.update_funding');
665 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.update_funding');
666
667 x_return_status := FND_API.G_RET_STS_SUCCESS;
668 x_funding_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
669
670 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
671 p_pkg_name => G_PKG_NAME ,
672 p_init_msg_list => p_init_msg_list ,
673 l_api_version => G_API_VERSION_NUMBER ,
674 p_api_version => p_api_version ,
675 p_api_type => '_PUB' ,
676 x_return_status => x_return_status
677 );
678
679 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
680
681 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
682
683 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
684
685 RAISE OKE_API.G_EXCEPTION_ERROR;
686
687 END IF;
688
689 --
690 -- Call OKE_ALLOCATION_PVT.add_allocation and update_allocation to create/update contract funding allocation
691 --
692
693 --dbms_output.put_line('checking if add or update allocation');
694 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'checking if add or update allocation');
695
696 IF (p_allocation_in_tbl.COUNT >0 )THEN
697
698 i := p_allocation_in_tbl.FIRST;
699
700 LOOP
701
702 l_allocation_in_rec := p_allocation_in_tbl(i);
703
704 --
705 -- Check if funding source id of source = funding source id of allocation lines
706 --
707
708 IF (l_allocation_in_rec.funding_source_id <> p_funding_in_rec.funding_source_id) THEN
709
710 OKE_API.set_message(p_app_name => G_APP_NAME ,
711 p_msg_name => 'OKE_API_INVALID_VALUE' ,
712 p_token1 => 'VALUE' ,
713 p_token1_value => 'allocation.funding_source_id'
714 );
715
716 RAISE G_EXCEPTION_HALT_VALIDATION;
717
718 END IF;
719
720 IF (check_update_add(p_fund_allocation_id => l_allocation_in_rec.fund_allocation_id)) THEN
721
722 OKE_ALLOCATION_PVT.update_allocation(p_api_version => p_api_version ,
723 p_init_msg_list => OKE_API.G_FALSE ,
724 p_commit => OKE_API.G_FALSE ,
725 p_return_status => x_return_status ,
726 p_msg_count => x_msg_count ,
727 p_msg_data => x_msg_data ,
728 p_allocation_in_rec => l_allocation_in_rec ,
729 p_allocation_out_rec => l_allocation_out_rec ,
730 p_validation_flag => OKE_API.G_FALSE
731 );
732
733 ELSE
734
735 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
739 p_msg_count => x_msg_count ,
736 p_init_msg_list => OKE_API.G_FALSE ,
737 p_commit => OKE_API.G_FALSE ,
738 p_return_status => x_return_status ,
740 p_msg_data => x_msg_data ,
741 p_validation_flag => OKE_API.G_FALSE ,
742 p_allocation_in_rec => l_allocation_in_rec ,
743 p_allocation_out_rec => l_allocation_out_rec
744 );
745
746 END IF;
747
748 x_allocation_out_tbl(i) := l_allocation_out_rec;
749
750 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
751
752 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
753
754 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
755
756 RAISE OKE_API.G_EXCEPTION_ERROR;
757
758 END IF;
759
760 EXIT WHEN (i = p_allocation_in_tbl.LAST);
761 i := p_allocation_in_tbl.NEXT(i);
762
763 END LOOP;
764
765 END IF;
766
767 --
768 -- Call OKE_FUNDSOURCE_PVT.update_funding to update contract funding
769 --
770
771 --dbms_output.put_line('calling oke_fundsource_pvt.update_funding');
772 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_fundsource_pvt.update_funding');
773
774 OKE_FUNDSOURCE_PVT.update_funding(p_api_version => p_api_version ,
775 p_init_msg_list => OKE_API.G_FALSE ,
776 p_commit => OKE_API.G_FALSE ,
777 p_msg_count => x_msg_count ,
778 p_msg_data => x_msg_data ,
779 p_funding_in_rec => p_funding_in_rec ,
780 p_funding_out_rec => x_funding_out_rec ,
781 p_return_status => x_return_status );
782
783 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
784
785 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
786
787 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
788
789 RAISE OKE_API.G_EXCEPTION_ERROR;
790
791 END IF;
792
793 --
794 -- Check if agreement update is needed
795 --
796
797 IF (FND_API.to_boolean(p_agreement_flag)) THEN
798
799 OKE_FUNDING_UTIL_PKG.check_agreement_exist(x_funding_source_id => p_funding_in_rec.funding_source_id ,
800 x_return_status => l_agreement_exist
801 );
802 IF (l_agreement_exist = 'Y') THEN
803
804 --dbms_output.put_line('calling oke_agreement_pvt.update_agreement');
805 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.update_agreement');
806
807 OKE_AGREEMENT_PVT.update_agreement(p_api_version => p_api_version ,
808 p_init_msg_list => OKE_API.G_FALSE ,
809 p_commit => OKE_API.G_FALSE ,
810 p_msg_count => x_msg_count ,
811 p_msg_data => x_msg_data ,
812 p_agreement_type => p_agreement_type ,
813 p_funding_in_rec => p_funding_in_rec ,
814 -- p_allocation_in_tbl => p_allocation_in_tbl ,
815 p_return_status => x_return_status
816 );
817
818 ELSE
819
820 --dbms_output.put_line('calling oke_agreement_pvt.create_agreement');
821 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.create_agreement');
822
823 OKE_AGREEMENT_PVT.create_agreement(p_api_version => p_api_version ,
824 p_init_msg_list => OKE_API.G_FALSE ,
825 p_commit => OKE_API.G_FALSE ,
826 p_msg_count => x_msg_count ,
827 p_msg_data => x_msg_data ,
828 p_agreement_type => p_agreement_type ,
829 p_funding_in_rec => p_funding_in_rec ,
830 -- p_allocation_in_tbl => p_allocation_in_tbl ,
831 p_return_status => x_return_status
832 );
833
834 END IF;
835
836 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
837
838 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
839
840 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
841
842 RAISE OKE_API.G_EXCEPTION_ERROR;
843
844 END IF;
845
846 END IF;
847
848 IF FND_API.to_boolean(p_commit) THEN
849
850 COMMIT WORK;
851
852 END IF;
853
854 --dbms_output.put_line('finished oke_funding_pub.create_funding w/ ' || x_return_status);
855 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.create_funding w/ ' || x_return_status);
856
857 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
858 x_msg_data => x_msg_data
859 );
860
861 EXCEPTION
862 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
863 x_funding_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
864 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
865 p_pkg_name => G_PKG_NAME ,
866 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
867 x_msg_count => x_msg_count ,
868 x_msg_data => x_msg_data ,
869 p_api_type => '_PUB'
870 );
871
875 p_pkg_name => G_PKG_NAME ,
872 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
873 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
874 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
876 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
877 x_msg_count => x_msg_count ,
878 x_msg_data => x_msg_data ,
879 p_api_type => '_PUB'
880 );
881
882 WHEN OTHERS THEN
883 x_funding_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
884 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
885 p_pkg_name => G_PKG_NAME ,
886 p_exc_name => 'OTHERS' ,
887 x_msg_count => x_msg_count ,
888 x_msg_data => x_msg_data ,
889 p_api_type => '_PUB'
890 );
891 END update_funding;
892
893
894 --
895 -- Procedure: delete_funding
896 --
897 -- Description: This procedure is used to delete contract funding and pa agreement
898 --
899 -- Calling subprograms: OKE_API.start_activity
900 -- OKE_API.end_activity
901 -- OKE_FUNDSOURCE_PVT.delete_funding
902 -- OKE_FUNDING_PUB.delete_allocation
903 --
904
905 PROCEDURE delete_funding(p_api_version IN NUMBER ,
906 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
907 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
908 x_return_status OUT NOCOPY VARCHAR2 ,
909 x_msg_count OUT NOCOPY NUMBER ,
910 x_msg_data OUT NOCOPY VARCHAR2 ,
911 p_funding_source_id IN NUMBER
912 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE
913 ) is
914
915 -- l_length NUMBER;
916 -- l_temp_val VARCHAR2(1) :='?';
917
918 cursor c_fund_allocation_id (p_funding_source_id NUMBER) is
919 select fund_allocation_id
920 from oke_k_fund_allocations
921 where funding_source_id = p_funding_source_id
922 order by amount asc;
923
924 l_allocation_id c_fund_allocation_id%ROWTYPE;
925 l_api_name CONSTANT VARCHAR2(30) := 'delete_funding';
926 l_return_status VARCHAR2(1);
927
928 BEGIN
929
930 --dbms_output.put_line('entering oke_funding_pub.delete_funding');
931 -- --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.delete_funding');
932
933 x_return_status := OKE_API.G_RET_STS_SUCCESS;
934
935 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
936 p_pkg_name => G_PKG_NAME ,
937 p_init_msg_list => p_init_msg_list ,
938 l_api_version => G_API_VERSION_NUMBER ,
939 p_api_version => p_api_version ,
940 p_api_type => '_PUB' ,
941 x_return_status => x_return_status
942 );
943
944 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
945
946 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
947
948 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
949
950 RAISE OKE_API.G_EXCEPTION_ERROR;
951
952 END IF;
953
954 --
955 -- Call OKE_FUNDING_PUB to delete contract funding allocation
956 --
957
958 --dbms_output.put_line('in loop: calling delete_allocation');
959 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'in loop: calling delete_allocation');
960
961 FOR l_allocation_id IN c_fund_allocation_id(p_funding_source_id) LOOP
962
963 OKE_ALLOCATION_PVT.delete_allocation(p_api_version => p_api_version ,
964 p_init_msg_list => OKE_API.G_FALSE ,
965 p_commit => OKE_API.G_FALSE ,
966 p_return_status => x_return_status ,
967 p_msg_count => x_msg_count ,
968 p_msg_data => x_msg_data ,
969 p_fund_allocation_id => l_allocation_id.fund_allocation_id
970 );
971
972 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
973
974 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
975
976 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
977
978 RAISE OKE_API.G_EXCEPTION_ERROR;
979
980 END IF;
981
982 END LOOP;
983
984 --dbms_output.put_line('finished delete_allocation');
985 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished delete_allocation');
986
987 --
988 -- Call OKE_FUNDSOURCE_PVT.delete_funding to delete contract funding
989 --
990
991 --dbms_output.put_line('calling oke_fundsource_pvt.delete_funding');
992 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_fundsource_pvt.delete_funding');
993
994 OKE_FUNDSOURCE_PVT.delete_funding(p_api_version => p_api_version ,
995 p_commit => OKE_API.G_FALSE ,
996 p_init_msg_list => OKE_API.G_FALSE ,
997 p_msg_count => x_msg_count ,
998 p_msg_data => x_msg_data ,
999 p_funding_source_id => p_funding_source_id ,
1000 -- p_agreement_flag => p_agreement_flag ,
1001 p_return_status => x_return_status
1002 );
1003
1007
1004 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1005
1006 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1009
1010 RAISE OKE_API.G_EXCEPTION_ERROR;
1011
1012 END IF;
1013
1014 --dbms_output.put_line('finished oke_funding_pub.delete_funding w/ ' || x_return_status);
1015 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.delete_funding w/ ' || x_return_status);
1016
1017 IF FND_API.to_boolean(p_commit) THEN
1018
1019 COMMIT WORK;
1020
1021 END IF;
1022
1023 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1024 x_msg_data => x_msg_data
1025 );
1026
1027 EXCEPTION
1028 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1029 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1030 p_pkg_name => G_PKG_NAME ,
1031 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1032 x_msg_count => x_msg_count ,
1033 x_msg_data => x_msg_data ,
1034 p_api_type => '_PUB'
1035 );
1036
1037 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1038 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1039 p_pkg_name => G_PKG_NAME ,
1040 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1041 x_msg_count => x_msg_count ,
1042 x_msg_data => x_msg_data ,
1043 p_api_type => '_PUB'
1044 );
1045
1046 WHEN OTHERS THEN
1047 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1048 p_pkg_name => G_PKG_NAME ,
1049 p_exc_name => 'OTHERS' ,
1050 x_msg_count => x_msg_count ,
1051 x_msg_data => x_msg_data ,
1052 p_api_type => '_PUB'
1053 );
1054 END delete_funding;
1055
1056
1057
1058 --
1059 -- Procedure: add_allocation
1060 --
1061 -- Description: This procedure is used to create funding allocation and update/add pa funding
1062 --
1063 -- Calling subprograms: OKE_API.start_activity
1064 -- OKE_API.end_activity
1065 -- OKE_ALLOCATION_PVT.add_allocation
1066 -- OKE_AGREEMENT_PVT.add_pa_funding
1067 --
1068
1069 PROCEDURE add_allocation(p_api_version IN NUMBER ,
1070 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
1071 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
1072 x_return_status OUT NOCOPY VARCHAR2 ,
1073 x_msg_count OUT NOCOPY NUMBER ,
1074 x_msg_data OUT NOCOPY VARCHAR2 ,
1075 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
1076 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
1077 x_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE
1078 ) is
1079
1080 l_api_name CONSTANT VARCHAR2(30) := 'add_allocation';
1081 l_return_status VARCHAR2(1);
1082 l_allocation_in_rec ALLOCATION_REC_IN_TYPE := p_allocation_in_rec;
1083
1084 BEGIN
1085
1086 --dbms_output.put_line('entering oke_funding_pub.add_allocation');
1087 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.add_allocation');
1088
1089 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1090 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
1091
1092 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
1093 p_pkg_name => G_PKG_NAME ,
1094 p_init_msg_list => p_init_msg_list ,
1095 l_api_version => G_API_VERSION_NUMBER ,
1096 p_api_version => p_api_version ,
1097 p_api_type => '_PUB' ,
1098 x_return_status => x_return_status
1099 );
1100
1101 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1102
1103 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1104
1105 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1106
1107 RAISE OKE_API.G_EXCEPTION_ERROR;
1108
1109 END IF;
1110
1111 --
1112 -- Call OKE_ALLOCATION_PVT.add_allocation
1113 --
1114
1115 --dbms_output.put_line('calling oke_allocation_pvt.add_allocation from oke_funding_pub');
1116 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_allocation_pvt.add_allocation from oke_funding_pub');
1117
1118 OKE_ALLOCATION_PVT.add_allocation(p_api_version => p_api_version ,
1119 p_init_msg_list => OKE_API.G_FALSE ,
1120 p_commit => OKE_API.G_FALSE ,
1121 p_msg_count => x_msg_count ,
1122 p_msg_data => x_msg_data ,
1123 p_allocation_in_rec => p_allocation_in_rec ,
1124 p_allocation_out_rec => x_allocation_out_rec ,
1125 p_validation_flag => OKE_API.G_TRUE ,
1126 p_return_status => x_return_status
1127 );
1128
1129 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1130
1131 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1132
1133 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1134
1135 RAISE OKE_API.G_EXCEPTION_ERROR;
1136
1137 END IF;
1138
1139 --
1140 -- Set the fund_allocation_id
1141 --
1145 -- Check if agreement update is needed
1142 l_allocation_in_rec.fund_allocation_id := x_allocation_out_rec.fund_allocation_id;
1143
1144 --
1146 --
1147
1148 IF (FND_API.to_boolean(p_agreement_flag)) THEN
1149
1150 --
1151 -- Validate agreement_id
1152 --
1153 validate_agreement_id(p_agreement_id => p_allocation_in_rec.agreement_id ,
1154 p_funding_source_id => p_allocation_in_rec.funding_source_id
1155 );
1156
1157 --
1158 -- Get the allocation record
1159 --
1160 l_allocation_in_rec := get_record(p_fund_allocation_id => l_allocation_in_rec.fund_allocation_id);
1161 l_allocation_in_rec.agreement_id := p_allocation_in_rec.agreement_id;
1162
1163 --dbms_output.put_line('calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1164 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1165
1166 OKE_AGREEMENT_PVT.add_pa_funding(p_api_version => p_api_version ,
1167 p_init_msg_list => OKE_API.G_FALSE ,
1168 p_commit => OKE_API.G_FALSE ,
1169 p_msg_count => x_msg_count ,
1170 p_msg_data => x_msg_data ,
1171 p_allocation_in_rec => l_allocation_in_rec ,
1172 p_return_status => x_return_status
1173 );
1174
1175 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1176
1177 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1178
1179 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1180
1181 RAISE OKE_API.G_EXCEPTION_ERROR;
1182
1183 END IF;
1184
1185 END IF;
1186
1187 --dbms_output.put_line('finished oke_funding_pub.add_allocation w/ ' || x_return_status);
1188 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.add_allocation w/ ' || x_return_status);
1189
1190 IF FND_API.to_boolean(p_commit) THEN
1191
1192 COMMIT WORK;
1193
1194 END IF;
1195
1196 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1197 x_msg_data => x_msg_data
1198 );
1199
1200 EXCEPTION
1201 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1202 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
1203 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1204 p_pkg_name => G_PKG_NAME ,
1205 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1206 x_msg_count => x_msg_count ,
1207 x_msg_data => x_msg_data ,
1208 p_api_type => '_PUB'
1209 );
1210
1211 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1212 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1213 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1214 p_pkg_name => G_PKG_NAME ,
1215 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1216 x_msg_count => x_msg_count ,
1217 x_msg_data => x_msg_data ,
1218 p_api_type => '_PUB'
1219 );
1220
1221 WHEN OTHERS THEN
1222 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1223 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1224 p_pkg_name => G_PKG_NAME ,
1225 p_exc_name => 'OTHERS' ,
1226 x_msg_count => x_msg_count ,
1227 x_msg_data => x_msg_data ,
1228 p_api_type => '_PUB'
1229 );
1230 END add_allocation;
1231
1232
1233 --
1234 -- Procedure: update_allocation
1235 --
1236 -- Description: This procedure is used to update contract funding allocation and pa funding line
1237 --
1238 -- Calling subprograms: OKE_API.start_activity
1239 -- OKE_API.end_activity
1240 -- OKE_ALLOCATION_PVT.update_allocation
1241 -- OKE_AGREEMENT_PVT.update_pa_funding
1242 -- OKE_AGREEMENT_PVT.add_pa_funding
1243 -- check_update_add_pa
1244 --
1245
1246 PROCEDURE update_allocation(p_api_version IN NUMBER ,
1247 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
1248 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
1249 x_return_status OUT NOCOPY VARCHAR2 ,
1250 x_msg_count OUT NOCOPY NUMBER ,
1251 x_msg_data OUT NOCOPY VARCHAR2 ,
1252 p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
1253 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
1254 x_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE
1255 ) is
1256
1257 l_api_name CONSTANT VARCHAR2(30) := 'update_allocation';
1258 l_return_status VARCHAR2(1);
1259 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
1260
1261 BEGIN
1262
1263 --dbms_output.put_line('entering oke_funding_pub.update_allocation');
1264 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.update_allocation');
1265
1266 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1267 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
1268
1269 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
1273 p_api_version => p_api_version ,
1270 p_pkg_name => G_PKG_NAME ,
1271 p_init_msg_list => p_init_msg_list ,
1272 l_api_version => G_API_VERSION_NUMBER ,
1274 p_api_type => '_PUB' ,
1275 x_return_status => x_return_status
1276 );
1277
1278 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1279
1280 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1281
1282 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1283
1284 RAISE OKE_API.G_EXCEPTION_ERROR;
1285
1286 END IF;
1287
1288 x_allocation_out_rec.fund_allocation_id := p_allocation_in_rec.fund_allocation_id;
1289
1290 --
1291 -- Call OKE_ALLOCATION_PVT.update_allocation to update the allocation line
1292 --
1293
1294 --dbms_output.put_line('calling oke_allocation_pvt.update_allocation from oke_funding_pub');
1295 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_allocation_pvt.update_allocation from oke_funding_pub');
1296
1297 OKE_ALLOCATION_PVT.update_allocation(p_api_version => p_api_version ,
1298 p_init_msg_list => OKE_API.G_FALSE ,
1299 p_commit => OKE_API.G_FALSE ,
1300 p_msg_count => x_msg_count ,
1301 p_msg_data => x_msg_data ,
1302 p_allocation_in_rec => p_allocation_in_rec ,
1303 p_allocation_out_rec => x_allocation_out_rec ,
1304 p_validation_flag => OKE_API.G_TRUE ,
1305 p_return_status => x_return_status
1306 );
1307
1308 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1309
1310 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1311
1312 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1313
1314 RAISE OKE_API.G_EXCEPTION_ERROR;
1315
1316 END IF;
1317
1318 --
1319 -- Check if agreement update is needed
1320 --
1321
1322 IF (FND_API.to_boolean(p_agreement_flag)) THEN
1323
1324 --
1325 -- Validate agreement_id
1326 --
1327 validate_agreement_id(p_agreement_id => p_allocation_in_rec.agreement_id ,
1328 p_funding_source_id => p_allocation_in_rec.funding_source_id
1329 );
1330
1331 --
1332 -- Get the allocation record
1333 --
1334 l_allocation_in_rec := get_record(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id);
1335 l_allocation_in_rec.agreement_id := p_allocation_in_rec.agreement_id;
1336
1337 --dbms_output.put_line('check if it is a update or add in pa');
1338 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'check if it is a update or add in pa');
1339
1340 IF (check_update_add_pa(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id)) THEN
1341
1342 --dbms_output.put_line('calling oke_agreement_pvt.update_pa_funding from oke_funding_pub');
1343 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.update_pa_funding from oke_funding_pub');
1344
1345 OKE_AGREEMENT_PVT.update_pa_funding(p_api_version => p_api_version ,
1346 p_init_msg_list => OKE_API.G_FALSE ,
1347 p_commit => OKE_API.G_FALSE ,
1348 p_msg_count => x_msg_count ,
1349 p_msg_data => x_msg_data ,
1350 p_allocation_in_rec => l_allocation_in_rec ,
1351 p_return_status => x_return_status
1352 );
1353 ELSE
1354
1355 --dbms_output.put_line('calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1356 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_agreement_pvt.add_pa_funding from oke_funding_pub');
1357
1358 OKE_AGREEMENT_PVT.add_pa_funding(p_api_version => p_api_version ,
1359 p_init_msg_list => OKE_API.G_FALSE ,
1360 p_commit => OKE_API.G_FALSE ,
1361 p_msg_count => x_msg_count ,
1362 p_msg_data => x_msg_data ,
1363 p_allocation_in_rec => l_allocation_in_rec ,
1364 p_return_status => x_return_status
1365 );
1366
1367 END IF;
1368
1369 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1370
1371 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1372
1373 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1374
1375 RAISE OKE_API.G_EXCEPTION_ERROR;
1376
1377 END IF;
1378
1379 END IF;
1380
1381 --dbms_output.put_line('finished oke_funding_pub.update_allocation w/ ' || x_return_status);
1382 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.update_allocation w/ ' || x_return_status);
1383
1384 IF FND_API.to_boolean(p_commit) THEN
1385
1386 COMMIT WORK;
1387
1388 END IF;
1389
1390 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1391 x_msg_data => x_msg_data
1392 );
1393
1394 EXCEPTION
1395 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1396 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
1400 x_msg_count => x_msg_count ,
1397 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1398 p_pkg_name => G_PKG_NAME ,
1399 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1401 x_msg_data => x_msg_data ,
1402 p_api_type => '_PUB'
1403 );
1404
1405 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1406 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1407 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1408 p_pkg_name => G_PKG_NAME ,
1409 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1410 x_msg_count => x_msg_count ,
1411 x_msg_data => x_msg_data ,
1412 p_api_type => '_PUB'
1413 );
1414
1415 WHEN OTHERS THEN
1416 x_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1417 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1418 p_pkg_name => G_PKG_NAME ,
1419 p_exc_name => 'OTHERS' ,
1420 x_msg_count => x_msg_count ,
1421 x_msg_data => x_msg_data ,
1422 p_api_type => '_PUB'
1423 );
1424 END update_allocation;
1425
1426
1427
1428 --
1429 -- Procedure: delete_allocation
1430 --
1431 -- Description: This procedure is used to delete contract funding allocation and pa project_funding
1432 --
1433 -- Calling subprograms: OKE_API.start_activity
1434 -- OKE_API.end_activity
1435 -- OKE_ALLOCATION_PVT.delete_allocation
1436 --
1437
1438 PROCEDURE delete_allocation(p_api_version IN NUMBER ,
1439 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
1440 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
1441 x_return_status OUT NOCOPY VARCHAR2 ,
1442 x_msg_count OUT NOCOPY NUMBER ,
1443 x_msg_data OUT NOCOPY VARCHAR2 ,
1444 p_fund_allocation_id IN NUMBER
1445 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE
1446 ) is
1447
1448 l_api_name CONSTANT VARCHAR2(30) := 'delete_allocation';
1449 l_return_status VARCHAR2(1);
1450 -- l_temp_val VARCHAR2(1) := '?';
1451
1452 cursor c_source is
1453 select s.amount, s.hard_limit, s.revenue_hard_limit, s.funding_source_id
1454 from oke_k_funding_sources s,
1455 oke_k_fund_allocations f
1456 where s.funding_source_id = f.funding_source_id
1457 and f.fund_allocation_id = p_fund_allocation_id;
1458
1459 cursor c_allocation (x_funding_source_id number)is
1460 select sum(amount), sum(hard_limit), sum(revenue_hard_limit)
1461 from oke_k_fund_allocations
1462 where funding_source_id = x_funding_source_id;
1463
1464 l_funding_source_id NUMBER;
1465 l_s_amount NUMBER;
1466 l_s_hard_limit NUMBER;
1467 l_s_revenue_limit NUMBER;
1468 l_a_amount NUMBER;
1469 l_a_hard_limit NUMBER;
1470 l_a_revenue_limit NUMBER;
1471
1472 BEGIN
1473
1474 --dbms_output.put_line('entering oke_funding_pub.delete_allocation');
1475 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'entering oke_funding_pub.delete_allocation');
1476
1477 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1478
1479 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
1480 p_pkg_name => G_PKG_NAME ,
1481 p_init_msg_list => p_init_msg_list ,
1482 l_api_version => G_API_VERSION_NUMBER ,
1483 p_api_version => p_api_version ,
1484 p_api_type => '_PUB' ,
1485 x_return_status => x_return_status
1486 );
1487
1488 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1489
1490 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1491
1492 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1493
1494 RAISE OKE_API.G_EXCEPTION_ERROR;
1495
1496 END IF;
1497
1498 --
1499 -- Check if it is ok to delete allocation
1500 --
1501 OPEN c_source;
1502 FETCH c_source INTO l_s_amount, l_s_hard_limit, l_s_revenue_limit, l_funding_source_id;
1503 IF (c_source%NOTFOUND) THEN
1504
1505 CLOSE c_source;
1506 OKE_API.set_message(p_app_name => 'OKE' ,
1507 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1508 p_token1 => 'VALUE' ,
1509 p_token1_value => 'fund_allocation_id'
1510 );
1511
1512 RAISE G_EXCEPTION_HALT_VALIDATION;
1513
1514 END IF;
1515 CLOSE c_source;
1516
1517 --
1518 -- Call OKE_ALLOCATION_PVT.delete_allocation to delete funding allocation line
1519 --
1520
1521 --dbms_output.put_line('calling oke_allocation_pvt.delete_allocation from oke_funding_pub');
1522 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'calling oke_allocation_pvt.delete_allocation from oke_funding_pub');
1523
1524 OKE_ALLOCATION_PVT.delete_allocation(p_api_version => p_api_version ,
1525 p_commit => OKE_API.G_FALSE ,
1526 p_init_msg_list => OKE_API.G_FALSE ,
1530 p_return_status => x_return_status
1527 p_msg_count => x_msg_count ,
1528 p_msg_data => x_msg_data ,
1529 p_fund_allocation_id => p_fund_allocation_id ,
1531 );
1532
1533 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1534
1535 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1536
1537 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1538
1539 RAISE OKE_API.G_EXCEPTION_ERROR;
1540
1541 END IF;
1542
1543 --
1544 -- Check sum of allocations
1545 --
1546 OPEN c_allocation(l_funding_source_id);
1547 FETCH c_allocation INTO l_a_amount, l_a_hard_limit, l_a_revenue_limit;
1548 IF (c_allocation%NOTFOUND) THEN
1549
1550 l_a_amount := 0;
1551 l_a_hard_limit := 0;
1552 l_a_revenue_limit := 0;
1553
1554 END IF;
1555
1556 CLOSE c_allocation;
1557
1558 IF (l_a_amount < 0) THEN
1559
1560 OKE_API.set_message(p_app_name => 'OKE' ,
1561 p_msg_name => 'OKE_NEGATIVE_ALLOCATION_SUM'
1562 );
1563
1564 RAISE G_EXCEPTION_HALT_VALIDATION;
1565
1566 ELSIF (l_a_amount > l_s_amount) THEN
1567
1568 OKE_API.set_message(p_app_name => 'OKE' ,
1569 p_msg_name => 'OKE_FUND_AMT_EXCEED'
1570 );
1571
1572 RAISE G_EXCEPTION_HALT_VALIDATION;
1573
1574 END IF;
1575
1576 IF (l_a_hard_limit < 0) THEN
1577
1578 OKE_API.set_message(p_app_name => 'OKE' ,
1579 p_msg_name => 'OKE_NEGATIVE_HARD_LIMIT_SUM'
1580 );
1581
1582 RAISE G_EXCEPTION_HALT_VALIDATION;
1583
1584 ELSIF (l_a_hard_limit > l_s_hard_limit) THEN
1585
1586 OKE_API.set_message(p_app_name => 'OKE' ,
1587 p_msg_name => 'OKE_HARD_LIMIT_EXCEED'
1588 );
1589
1590 RAISE G_EXCEPTION_HALT_VALIDATION;
1591
1592 END IF;
1593
1594 IF (l_a_revenue_limit < 0) THEN
1595
1596 OKE_API.set_message(p_app_name => 'OKE' ,
1597 p_msg_name => 'OKE_NEGATIVE_REV_LIMIT_SUM'
1598 );
1599
1600 RAISE G_EXCEPTION_HALT_VALIDATION;
1601
1602 ELSIF (l_a_revenue_limit > l_s_revenue_limit) THEN
1603
1604 OKE_API.set_message(p_app_name => 'OKE' ,
1605 p_msg_name => 'OKE_REV_LIMIT_EXCEED'
1606 );
1607
1608 RAISE G_EXCEPTION_HALT_VALIDATION;
1609
1610 END IF;
1611
1612 --dbms_output.put_line('finished oke_funding_pub.delete_allocation w/ ' || x_return_status);
1613 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'finished oke_funding_pub.delete_allocation w/ ' || x_return_status);
1614
1615 IF FND_API.to_boolean(p_commit) THEN
1616
1617 COMMIT WORK;
1618
1619 END IF;
1620
1621 OKE_API.END_ACTIVITY(x_msg_count => x_msg_count ,
1622 x_msg_data => x_msg_data
1623 );
1624
1625 EXCEPTION
1626 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
1627 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1628 p_pkg_name => G_PKG_NAME ,
1629 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
1630 x_msg_count => x_msg_count ,
1631 x_msg_data => x_msg_data ,
1632 p_api_type => '_PUB'
1633 );
1634
1635 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1636 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1637 p_pkg_name => G_PKG_NAME ,
1638 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
1639 x_msg_count => x_msg_count ,
1640 x_msg_data => x_msg_data ,
1641 p_api_type => '_PUB'
1642 );
1643
1644 WHEN OTHERS THEN
1645 x_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
1646 p_pkg_name => G_PKG_NAME ,
1647 p_exc_name => 'OTHERS' ,
1648 x_msg_count => x_msg_count ,
1649 x_msg_data => x_msg_data ,
1650 p_api_type => '_PUB'
1651 );
1652 END delete_allocation;
1653
1654 end OKE_FUNDING_PUB;
1655