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