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