[Home] [Help]
PACKAGE BODY: APPS.OKE_ALLOCATION_PVT
Source
1 package body OKE_ALLOCATION_PVT as
2 /* $Header: OKEVFDAB.pls 120.3.12020000.2 2012/12/01 03:52:04 skuchima ship $ */
3
4 --
5 -- Local Variables
6 --
7
8 L_USERID NUMBER := FND_GLOBAL.USER_ID;
9 L_LOGINID NUMBER := FND_GLOBAL.LOGIN_ID;
10
11
12
13
14 --
15 -- Private Procedures and Functions
16 --
17
18
19 --
20 -- Function: get_fund_allocation_id
21 --
22 -- Description: This function is used to get fund_allocation_id
23 --
24 --
25
26 FUNCTION get_fund_allocation_id RETURN NUMBER is
27
28 l_funding_allocation_id NUMBER;
29
30 BEGIN
31
32 select oke_k_fund_allocations_s.nextval
33 into l_funding_allocation_id
34 from dual;
35
36 return(l_funding_allocation_id);
37
38 END get_fund_allocation_id;
39
40
41 --
42 -- Function: get_source_currency
43 --
44 -- Description: This function is used to get funding source currency
45 --
46 --
47
48 FUNCTION get_source_currency(p_funding_source_id NUMBER)
49 RETURN VARCHAR2 is
50
51 l_currency VARCHAR2(15);
52
53 cursor c_currency is
54 select currency_code
55 from oke_k_funding_sources
56 where funding_source_id = p_funding_source_id;
57
58 BEGIN
59
60 OPEN c_currency;
61 FETCH c_currency into l_currency;
62
63 IF (c_currency%NOTFOUND) THEN
64
65 CLOSE c_currency;
66 RAISE G_EXCEPTION_HALT_VALIDATION;
67
68 END IF;
69
70 CLOSE c_currency;
71
72 return(l_currency);
73
74 EXCEPTION
75 WHEN G_EXCEPTION_HALT_VALIDATION THEN
76 RAISE G_EXCEPTION_HALT_VALIDATION;
77
78 WHEN OTHERS THEN
79 OKE_API.set_message(p_app_name => G_APP_NAME ,
80 p_msg_name => G_UNEXPECTED_ERROR ,
81 p_token1 => G_SQLCODE_TOKEN ,
82 p_token1_value => SQLCODE ,
83 p_token2 => G_SQLERRM_TOKEN ,
84 p_token2_value => SQLERRM
85 );
86
87 IF c_currency%ISOPEN THEN
88 CLOSE c_currency;
89 END IF;
90
91 RAISE G_EXCEPTION_HALT_VALIDATION;
92
93 END get_source_currency;
94
95
96 --
97 -- Function: get_proj_info
98 --
99 -- Description: This function is used to get project information
100 --
101 --
102
103 PROCEDURE get_proj_info(p_project_id NUMBER ,
104 p_projfunc_currency OUT NOCOPY VARCHAR2 ) is
105
106 cursor c_currency is
107 select p.projfunc_currency_code
108 from pa_projects_all p
109 where project_id = p_project_id;
110
111 BEGIN
112
113 OPEN c_currency;
114 FETCH c_currency into p_projfunc_currency;
115
116 IF (c_currency%NOTFOUND) THEN
117
118 CLOSE c_currency;
119 RAISE G_EXCEPTION_HALT_VALIDATION;
120
121 END IF;
122
123 CLOSE c_currency;
124
125 EXCEPTION
126 WHEN G_EXCEPTION_HALT_VALIDATION THEN
127 RAISE G_EXCEPTION_HALT_VALIDATION;
128
129 WHEN OTHERS THEN
130 OKE_API.set_message(p_app_name => G_APP_NAME ,
131 p_msg_name => G_UNEXPECTED_ERROR ,
132 p_token1 => G_SQLCODE_TOKEN ,
133 p_token1_value => SQLCODE ,
134 p_token2 => G_SQLERRM_TOKEN ,
135 p_token2_value => SQLERRM
136 );
137
138 IF c_currency%ISOPEN THEN
139 CLOSE c_currency;
140 END IF;
141
142 RAISE G_EXCEPTION_HALT_VALIDATION;
143
144 END get_proj_info;
145
146
147 --
148 -- Procedure: allowable_changes
149 --
150 -- Description: This procedure is used to check if changes are allowed
151 --
152 --
153 /*
154 PROCEDURE allowable_changes(p_fund_allocation_id NUMBER ,
155 p_project_id NUMBER ,
156 p_task_id NUMBER ,
157 p_start_date_active DATE
158 ) is
159
160 cursor c_allocation is
161 select project_id,
162 task_id,
163 start_date_active
164 from oke_k_fund_allocations
165 where fund_allocation_id = p_fund_allocation_id;
166
167 l_allocation c_allocation%ROWTYPE;
168 l_field VARCHAR2(30);
169
170 BEGIN
171
172 OPEN c_allocation;
173 FETCH c_allocation into l_allocation;
174 CLOSE c_allocation;
175
176 IF (l_allocation.project_id <> p_project_id) then
177
178 l_field := 'Project';
179
180 ELSIF (l_allocation.task_id <> p_task_id) then
181
182 l_field := 'Task';
183
184 ELSIF (l_allocation.start_date_active <> p_start_date_active) then
185
186 l_field := 'Start Date Active';
187
188 END IF;
189
190 IF (l_field is not null) THEN
191
192 OKE_API.set_message(p_app_name => G_APP_NAME ,
193 p_msg_name => 'OKE_NO_FUND_CHANGE' ,
194 p_token1 => 'FILED' ,
195 p_token1_value => l_field
196 );
197
198 END IF;
199
200 END allowable_changes;
201 */
202
203
204 --
205 -- Procedure: validate_amount
206 --
207 -- Description: This procedure is used to validate amount
208 --
209 --
210
211 PROCEDURE validate_amount(p_amount NUMBER ,
212 p_return_status OUT NOCOPY VARCHAR2
213 ) is
214
215 BEGIN
216
217 p_return_status := OKE_API.G_RET_STS_SUCCESS;
218
219 IF (p_amount is null) OR
220 (p_amount = OKE_API.G_MISS_NUM) THEN
221
222 OKE_API.set_message(p_app_name => G_APP_NAME ,
223 p_msg_name => 'OKE_API_MISSING_VALUE' ,
224 p_token1 => 'VALUE' ,
225 p_token1_value => 'amount'
226 );
227
228 p_return_status := OKE_API.G_RET_STS_ERROR;
229
230 END IF;
231
232 END validate_amount;
233
234
235 --
236 -- Procedure: validate_project_task
237 --
238 -- Description: This procedure is used to validate project_id and task_id relationship
239 --
240 --
241
242 PROCEDURE validate_project_task(p_project_id NUMBER ,
243 p_task_id NUMBER
244 ) is
245
246 cursor c_project_task is
247 select 'x'
248 from pa_tasks
249 where task_id = p_task_id
250 and top_task_id = p_task_id
251 and project_id = p_project_id;
252
253 l_dummy_value VARCHAR2(1) := '?';
254
255 BEGIN
256
257 IF (p_project_id is not null) THEN
258
259 IF (p_task_id is not null) THEN
260
261 OPEN c_project_task;
262 FETCH c_project_task into l_dummy_value;
263 CLOSE c_project_task;
264
265 IF (l_dummy_value = '?') THEN
266
267 OKE_API.set_message(p_app_name => G_APP_NAME ,
268 p_msg_name => 'OKE_API_INVALID_VALUE' ,
269 p_token1 => 'VALUE' ,
270 p_token1_value => 'task_id and project_id'
271 );
272
273 RAISE G_EXCEPTION_HALT_VALIDATION;
274
275 END IF;
276
277 END IF;
278
279 ElSIF (p_task_id is not null) THEN
280
281 OKE_API.set_message(p_app_name => G_APP_NAME ,
282 p_msg_name => 'OKE_API_INVALID_VALUE' ,
283 p_token1 => 'VALUE' ,
284 p_token1_value => 'task_id'
285 );
286
287 RAISE G_EXCEPTION_HALT_VALIDATION;
288
289 END IF;
290
291 EXCEPTION
292 WHEN G_EXCEPTION_HALT_VALIDATION THEN
293 RAISE G_EXCEPTION_HALT_VALIDATION;
294
295 WHEN OTHERS THEN
296 OKE_API.set_message(p_app_name => G_APP_NAME ,
297 p_msg_name => G_UNEXPECTED_ERROR ,
298 p_token1 => G_SQLCODE_TOKEN ,
299 p_token1_value => SQLCODE ,
300 p_token2 => G_SQLERRM_TOKEN ,
301 p_token2_value => SQLERRM
302 );
303
304 IF c_project_task%ISOPEN THEN
305 CLOSE c_project_task;
306 END IF;
307
308 END validate_project_task;
309
310
311 --
312 -- Procedure: validate_header_line
313 --
314 -- Description: This procedure is used to validate object_id and k_line_id relationship
315 --
316 --
317
318 PROCEDURE validate_header_line(p_object_id NUMBER ,
319 p_k_line_id NUMBER
320 ) is
321
322 cursor c_header_line is
323 select 'x'
324 from okc_k_lines_b
325 where id = p_k_line_id
326 and dnz_chr_id = p_object_id;
327
328 l_dummy_value VARCHAR2(1) := '?';
329 BEGIN
330
331 IF (p_k_line_id is not null) THEN
332
333 OPEN c_header_line;
334 FETCH c_header_line into l_dummy_value;
335 CLOSE c_header_line;
336
337 IF (l_dummy_value = '?') THEN
338
339 OKE_API.set_message(p_app_name => G_APP_NAME ,
340 p_msg_name => 'OKE_API_INVALID_VALUE' ,
341 p_token1 => 'VALUE' ,
342 p_token1_value => 'k_line_id and object_id'
343 );
344
345 RAISE G_EXCEPTION_HALT_VALIDATION;
346
347 END IF;
348
349 END IF;
350
351 EXCEPTION
352 WHEN G_EXCEPTION_HALT_VALIDATION THEN
353 RAISE G_EXCEPTION_HALT_VALIDATION;
354
355 WHEN OTHERS THEN
356 OKE_API.set_message(p_app_name => G_APP_NAME ,
357 p_msg_name => G_UNEXPECTED_ERROR ,
358 p_token1 => G_SQLCODE_TOKEN ,
359 p_token1_value => SQLCODE ,
360 p_token2 => G_SQLERRM_TOKEN ,
361 p_token2_value => SQLERRM
362 );
363
364 IF c_header_line%ISOPEN THEN
365 CLOSE c_header_line;
366 END IF;
367
368 END validate_header_line;
369
370
371 --
372 -- Procedure: validate_fund_allocation_id
373 --
374 -- Description: This procedure is used to validate fund_allocation_id
375 --
376 --
377
378 PROCEDURE validate_fund_allocation_id(p_fund_allocation_id NUMBER ,
379 p_rowid OUT NOCOPY VARCHAR2 ,
380 p_version OUT NOCOPY NUMBER
381 ) is
382 cursor c_fund_allocation_id is
383 select rowid, nvl(agreement_version, 0)
384 from oke_k_fund_allocations
385 where fund_allocation_id = p_fund_allocation_id;
386
387 BEGIN
388
389 IF (p_fund_allocation_id is null) OR
390 (p_fund_allocation_id = OKE_API.G_MISS_NUM) THEN
391
392 OKE_API.set_message(p_app_name => G_APP_NAME ,
393 p_msg_name => 'OKE_API_MISSING_VALUE' ,
394 p_token1 => 'VALUE' ,
395 p_token1_value => 'fund_allocation_id'
396 );
397
398 RAISE G_EXCEPTION_HALT_VALIDATION;
399
400 END IF;
401
402 OPEN c_fund_allocation_id;
403 FETCH c_fund_allocation_id into p_rowid, p_version;
404
405 IF (c_fund_allocation_id%NOTFOUND) THEN
406
407 CLOSE c_fund_allocation_id;
408
409 OKE_API.set_message(p_app_name => G_APP_NAME ,
410 p_msg_name => 'OKE_API_INVALID_VALUE' ,
411 p_token1 => 'VALUE' ,
412 p_token1_value => 'fund_allocation_id'
413 );
414
415 RAISE G_EXCEPTION_HALT_VALIDATION;
416
417 END IF;
418
419 CLOSE c_fund_allocation_id;
420
421 EXCEPTION
422 WHEN G_EXCEPTION_HALT_VALIDATION THEN
423 raise G_EXCEPTION_HALT_VALIDATION;
424
425 WHEN OTHERS THEN
426 OKE_API.set_message(p_app_name => G_APP_NAME ,
427 p_msg_name => G_UNEXPECTED_ERROR ,
428 p_token1 => G_SQLCODE_TOKEN ,
429 p_token1_value => SQLCODE ,
430 p_token2 => G_SQLERRM_TOKEN ,
431 p_token2_value => SQLERRM
432 );
433
434 IF c_fund_allocation_id%ISOPEN THEN
435 CLOSE c_fund_allocation_id;
436 END IF;
437
438 RAISE G_EXCEPTION_HALT_VALIDATION;
439
440 END validate_fund_allocation_id;
441
442
443 --
444 -- Procedure: validate_object_id
445 --
446 -- Description: This procedure is used to validate object_id
447 --
448 --
449
450 PROCEDURE validate_object_id(p_object_id NUMBER ,
451 p_funding_source_id NUMBER ,
452 p_return_status OUT NOCOPY VARCHAR2
453 ) is
454 cursor c_object_id is
455 select 'x'
456 from oke_k_funding_sources
457 where object_id = p_object_id
458 and funding_source_id = p_funding_source_id;
459
460 l_dummy_value VARCHAR2(1) := '?';
461
462 BEGIN
463
464 p_return_status := OKE_API.G_RET_STS_SUCCESS;
465
466 IF (p_object_id is null) OR
467 (p_object_id = OKE_API.G_MISS_NUM) THEN
468
469 OKE_API.set_message(p_app_name => G_APP_NAME ,
470 p_msg_name => 'OKE_API_MISSING_VALUE' ,
471 p_token1 => 'VALUE' ,
472 p_token1_value => 'object_id'
473 );
474
475 p_return_status := OKE_API.G_RET_STS_ERROR;
476
477 ELSE
478
479 OPEN c_object_id;
480 FETCH c_object_id into l_dummy_value;
481 CLOSE c_object_id;
482
483 IF (l_dummy_value = '?') THEN
484
485 OKE_API.set_message(p_app_name => G_APP_NAME ,
486 p_msg_name => 'OKE_API_INVALID_VALUE' ,
487 p_token1 => 'VALUE' ,
488 p_token1_value => 'object_id'
489 );
490
491 p_return_status := OKE_API.G_RET_STS_ERROR;
492
493 END IF;
494
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
500 OKE_API.set_message(p_app_name => G_APP_NAME ,
501 p_msg_name => G_UNEXPECTED_ERROR ,
502 p_token1 => G_SQLCODE_TOKEN ,
503 p_token1_value => SQLCODE ,
504 p_token2 => G_SQLERRM_TOKEN ,
505 p_token2_value => SQLERRM
506 );
507
508 IF c_object_id%ISOPEN THEN
509 CLOSE c_object_id;
510 END IF;
511
512 END validate_object_id;
513
514
515 --
516 -- Procedure: validate_funding_source_id
517 --
518 -- Description: This procedure is used to validate funding_source_id
519 --
520 --
521
522 PROCEDURE validate_funding_source_id(p_funding_source_id NUMBER ,
523 p_return_status OUT NOCOPY VARCHAR2
524 ) is
525 cursor c_funding_source_id is
526 select 'x'
527 from oke_k_funding_sources
528 where funding_source_id = p_funding_source_id
529 FOR UPDATE OF funding_source_id NOWAIT;
530
531 l_dummy_value VARCHAR2(1) := '?';
532
533 BEGIN
534
535 --oke_debug.debug('validate_funding_source_id : funding_source_id ' || p_funding_source_id);
536 --dbms_output.put_line('validate_funding_source_id : funding_source_id ' || p_funding_source_id);
537
538 p_return_status := OKE_API.G_RET_STS_SUCCESS;
539
540 IF (p_funding_source_id is null) OR
541 (p_funding_source_id = OKE_API.G_MISS_NUM) THEN
542
543 OKE_API.set_message(p_app_name => G_APP_NAME ,
544 p_msg_name => 'OKE_API_MISSING_VALUE' ,
545 p_token1 => 'VALUE' ,
546 p_token1_value => 'funding_source_id'
547 );
548
549 p_return_status := OKE_API.G_RET_STS_ERROR;
550
551 ELSE
552
553 OPEN c_funding_source_id;
554 FETCH c_funding_source_id into l_dummy_value;
555 CLOSE c_funding_source_id;
556
557 --oke_debug.debug('validate_funding_source_id : l_dummy_value ' || l_dummy_value);
558 --dbms_output.put_line('validate_funding_source_id : l_dummy_value ' || l_dummy_value);
559
560 IF (l_dummy_value = '?') THEN
561
562 OKE_API.set_message(p_app_name => G_APP_NAME ,
563 p_msg_name => 'OKE_API_INVALID_VALUE' ,
564 p_token1 => 'VALUE' ,
565 p_token1_value => 'funding_source_id'
566 );
567
568 p_return_status := OKE_API.G_RET_STS_ERROR;
569
570 END IF;
571
572 END IF;
573
574 EXCEPTION
575 WHEN G_RESOURCE_BUSY THEN
576 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
577 OKE_API.set_message(p_app_name => G_APP_NAME ,
578 p_msg_name => 'OKE_ROW_LOCKED' ,
579 p_token1 => 'SOURCE' ,
580 p_token1_value => 'OKE_FUNDING_SOURCE_PROMPT' ,
581 p_token1_translate => OKE_API.G_TRUE ,
582 p_token2 => 'ID' ,
583 p_token2_value => p_funding_source_id
584 );
585
586 IF c_funding_source_id%ISOPEN THEN
587 CLOSE c_funding_source_id;
588 END IF;
589
590 WHEN OTHERS THEN
591 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
592 OKE_API.set_message(p_app_name => G_APP_NAME ,
593 p_msg_name => G_UNEXPECTED_ERROR ,
594 p_token1 => G_SQLCODE_TOKEN ,
595 p_token1_value => SQLCODE ,
596 p_token2 => G_SQLERRM_TOKEN ,
597 p_token2_value => SQLERRM
598 );
599
600 IF c_funding_source_id%ISOPEN THEN
601 CLOSE c_funding_source_id;
602 END IF;
603
604 END validate_funding_source_id;
605
606
607 --
608 -- Procedure: validate_k_line_id
609 --
610 -- Description: This procedure is used to validate k_line_id
611 --
612 --
613
614 PROCEDURE validate_k_line_id(p_k_line_id NUMBER ,
615 p_project_id NUMBER ,
616 p_fund_allocation_id NUMBER ,
617 p_return_status OUT NOCOPY VARCHAR2
618 ) is
619 cursor c_line_id is
620 select 'x'
621 from oke_k_lines
622 where k_line_id = p_k_line_id;
623
624 cursor c_header is
625 select pa_flag
626 from oke_k_fund_allocations
627 where fund_allocation_id = p_fund_allocation_id;
628
629 cursor c_line_project is
630 select project_id, task_id
631 from oke_k_lines
632 where k_line_id = p_k_line_id;
633
634 cursor c_valid_line (x_project_id number) is
635 select 'x'
636 from dual
637 where p_project_id in
638 (select to_number(sub_project_id)
639 from pa_fin_structures_links_v
640 start with parent_project_id = x_project_id
641 connect by parent_project_id = prior sub_project_id
642 );
643
644 cursor c_valid_line2 (x_task_id number,x_project_id number) is
645 select 'x'
646 from dual
647 where p_project_id in
648 ( select to_number(sub_project_id)
649 from pa_fin_structures_links_v
650 START WITH (parent_project_id, parent_task_id)
651 IN (SELECT x_project_id, task_id FROM pa_tasks
652 WHERE project_id = x_project_id
653 AND top_task_id = nvl(x_task_id, top_task_id))
654 connect by parent_project_id = prior sub_project_id);
655
656 cursor c_line (x_line_id number) is
657 select project_id,
658 parent_line_id,
659 task_id
660 from oke_k_lines
661 where k_line_id = x_line_id;
662
663 l_dummy_value VARCHAR2(1) := '?';
664 l_flag VARCHAR2(1);
665 l_line_project NUMBER;
666 l_master_project NUMBER;
667 l_exist VARCHAR2(1) := 'N';
668 l_valid_project NUMBER;
669 l_line_id NUMBER;
670 l_line_task NUMBER;
671
672 BEGIN
673
674 p_return_status := OKE_API.G_RET_STS_SUCCESS;
675
676 IF (p_k_line_id is not null) OR
677 (p_k_line_id <> OKE_API.G_MISS_NUM) THEN
678
679 OPEN c_line_id;
680 FETCH c_line_id into l_dummy_value;
681 CLOSE c_line_id;
682
683 IF (l_dummy_value = '?') THEN
684
685 OKE_API.set_message(p_app_name => G_APP_NAME ,
686 p_msg_name => 'OKE_API_INVALID_VALUE' ,
687 p_token1 => 'VALUE' ,
688 p_token1_value => 'k_line_id'
689 );
690
691 p_return_status := OKE_API.G_RET_STS_ERROR;
692
693 END IF;
694
695 IF (p_fund_allocation_id is not null) THEN
696
697 OPEN c_header;
698 FETCH c_header into l_flag;
699 CLOSE c_header;
703 OPEN c_line_project;
700
701 IF (nvl(l_flag, 'N') = 'Y') THEN
702
704 FETCH c_line_project into l_line_project, l_line_task;
705 CLOSE c_line_project;
706
707 IF (l_line_project is null) THEN
708
709 l_line_id := p_k_line_id;
710 WHILE (l_exist = 'N') LOOP
711 OPEN c_line(l_line_id);
712 FETCH c_line into l_line_project, l_line_id, l_line_task;
713 CLOSE c_line;
714
715 IF (l_line_project is not null) OR
716 (l_line_id is null) THEN
717 l_exist := 'Y';
718 END IF;
719 END LOOP;
720 END IF;
721
722 IF (l_line_project is not null) THEN
723 IF (l_line_project <> p_project_id) THEN
724 IF (l_line_task is not null) THEN
725
726 OPEN c_valid_line2(l_line_task,l_line_project);
727 FETCH c_valid_line2 into l_dummy_value;
728 CLOSE c_valid_line2;
729
730 ELSE
731
732 OPEN c_valid_line(l_line_project);
733 FETCH c_valid_line into l_dummy_value;
734 CLOSE c_valid_line;
735
736 END IF;
737
738 IF (l_dummy_value = '?') THEN
739 OKE_API.set_message(p_app_name => G_APP_NAME ,
740 p_msg_name => 'OKE_API_INVALID_VALUE' ,
741 p_token1 => 'VALUE' ,
742 p_token1_value => 'k_line_id'
743 );
744
745 p_return_status := OKE_API.G_RET_STS_ERROR;
746 END IF;
747 END IF;
748 END IF;
749
750 END IF;
751
752 END IF;
753
754 END IF;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
759 OKE_API.set_message(p_app_name => G_APP_NAME ,
760 p_msg_name => G_UNEXPECTED_ERROR ,
761 p_token1 => G_SQLCODE_TOKEN ,
762 p_token1_value => SQLCODE ,
763 p_token2 => G_SQLERRM_TOKEN ,
764 p_token2_value => SQLERRM
765 );
766
767 IF c_line_id%ISOPEN THEN
768 CLOSE c_line_id;
769 END IF;
770
771 END validate_k_line_id;
772
773
774 --
775 -- Procedure: validate_project_id
776 --
777 -- Description: This procedure is used to validate project_id
778 --
779 --
780
781 PROCEDURE validate_project_id(p_project_id NUMBER,
782 p_k_line_id NUMBER,
783 p_funding_source_id NUMBER,
784 p_object_id NUMBER,
785 p_return_status OUT NOCOPY VARCHAR2
786 ) is
787 cursor c_project_id is
788 select 'x'
789 from pa_project_customers p,
790 pa_projects_all a,
791 oke_k_funding_sources f,
792 pa_project_types_all l,
793 hz_cust_accounts h
794 where p.project_id = p_project_id
795 and nvl(a.template_flag, '-99') <> 'Y'
796 and f.funding_source_id = p_funding_source_id
797 and p.customer_id = h.cust_account_id
798 and h.party_id = f.k_party_id
799 and a.project_id = p.project_id
800 and a.project_type = l.project_type
801 and l.project_type_class_code = 'CONTRACT';
802
803 cursor c_line_project is
804 select project_id,
805 task_id
806 from oke_k_lines_v
807 where header_id = p_object_id
808 and k_line_id = p_k_line_id;
809
810 cursor c_master_project is
811 select project_id
812 from oke_k_headers
813 where k_header_id = p_object_id;
814
815 /*
816 cursor c_project_h (x_project_id number) is
817 select 'x'
818 from dual
819 where p_project_id in
820 (select to_number(sub_project_id)
821 from pa_fin_structures_links_v
822 start with parent_project_id = x_project_id
823 connect by parent_project_id = prior sub_project_id
824 union all
825 select x_project_id
826 from dual
827 );
828
829 */
830
831 /*Modified the above cursor for the bug#15919161 */
832
833 cursor c_project_h (x_project_id number) is
834 select 'x'
835 from dual
836 where x_project_id in
837 (select a.parent_project_id
838 from pa_fin_structures_links_v a
839 start with sub_project_id = p_project_id
840 connect by sub_project_id = prior parent_project_id
841 union all
842 select p_project_id
843 from dual
844 );
845
846 /*
847 cursor c_project_h2 (x_task_id number, x_project_id number) is
848 select 'x'
849 from dual
850 where p_project_id in
851 ( select to_number(sub_project_id)
852 from pa_fin_structures_links_v
853 start with parent_project_id = x_project_id
854 and parent_task_id = x_task_id
855 connect by parent_project_id = prior sub_project_id
856 union all
857 select x_project_id
858 from dual
859 );
860 */
861
862 /*Modified the above cursor for the bug#15919161 */
863
864 cursor c_project_h2 (x_task_id number, x_project_id number) is
865 select 'x'
866 from dual
867 where (x_project_id,x_task_id) in
868 ( select a.parent_project_id,a.parent_task_id
869 from pa_fin_structures_links_v a
870 start with sub_project_id = p_project_id
871 connect by sub_project_id = prior parent_project_id
872 union all
873 select p_project_id,x_task_id
874 from dual
875 );
876
877 cursor c_intent is
878 select buy_or_sell
879 from oke_k_headers_v
880 where k_header_id = p_object_id;
881
882 cursor c_project_2 (x_line_id number) is
883 select project_id,
884 parent_line_id,
885 task_id
886 from oke_k_lines
887 where k_line_id = x_line_id;
888
889 l_dummy_value VARCHAR2(1) := '?';
890 l_project_id NUMBER;
891 l_intent VARCHAR2(1);
892 l_task_id NUMBER;
893 l_line_id NUMBER;
894 l_exist VARCHAR2(1);
895
896 BEGIN
897
898 p_return_status := OKE_API.G_RET_STS_SUCCESS;
899
900 IF (p_project_id is not null) OR
901 (p_project_id <> OKE_API.G_MISS_NUM) THEN
902
903 OPEN c_intent;
904 FETCH c_intent into l_intent;
905 CLOSE c_intent;
906
907 IF (l_intent = 'S') THEN
908
909 OPEN c_project_id;
910 FETCH c_project_id into l_dummy_value;
911 CLOSE c_project_id;
912
913 IF (l_dummy_value = '?') THEN
914
915 OKE_API.set_message(p_app_name => G_APP_NAME ,
916 p_msg_name => 'OKE_API_INVALID_VALUE' ,
917 p_token1 => 'VALUE' ,
918 p_token1_value => 'project_id'
919 );
920
921 p_return_status := OKE_API.G_RET_STS_ERROR;
922 return;
923
924 END IF;
925
926 END IF;
927
928 l_dummy_value := '?';
929
930 IF (p_k_line_id is not null) then
931 OPEN c_line_project;
932 FETCH c_line_project into l_project_id, l_task_id;
933 CLOSE c_line_project;
934
935 IF (l_project_id is null) THEN
936
937 l_line_id := p_k_line_id;
938 l_exist := 'N';
939
940 while (l_exist = 'N') loop
941
942 open c_project_2 (l_line_id);
943 l_line_id := null;
944 fetch c_project_2 into l_project_id, l_line_id, l_task_id;
945 close c_project_2;
946
947 if (l_line_id is null) or
948 (l_project_id is not null) then
949 l_exist := 'Y';
950 end if;
951
952 end loop;
953
954 END IF;
955
956 END IF;
957
958 IF (l_project_id is null) then
959 OPEN c_master_project;
960 FETCH c_master_project into l_project_id;
961 CLOSE c_master_project;
962 END IF;
963
964 IF (l_project_id is null) then
965
966 OKE_API.set_message(p_app_name => G_APP_NAME ,
967 p_msg_name => 'OKE_API_INVALID_VALUE' ,
968 p_token1 => 'VALUE' ,
969 p_token1_value => 'project_id'
970 );
971
972 p_return_status := OKE_API.G_RET_STS_ERROR;
973
974 ELSE
975
976 IF (l_task_id is not null) THEN
977
978 OPEN c_project_h2(l_task_id, l_project_id);
979 FETCH c_project_h2 into l_dummy_value;
980 CLOSE c_project_h2;
981
982 ELSE
983
984 OPEN c_project_h(l_project_id);
985 FETCH c_project_h into l_dummy_value;
986 CLOSE c_project_h;
987
988 END IF;
989
990 IF (l_dummy_value = '?') then
991
992 OKE_API.set_message(p_app_name => G_APP_NAME ,
993 p_msg_name => 'OKE_API_INVALID_VALUE' ,
994 p_token1 => 'VALUE' ,
995 p_token1_value => 'project_id'
996 );
997
998 p_return_status := OKE_API.G_RET_STS_ERROR;
999
1000 END IF;
1001
1002 END IF;
1003
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1009 OKE_API.set_message(p_app_name => G_APP_NAME ,
1010 p_msg_name => G_UNEXPECTED_ERROR ,
1011 p_token1 => G_SQLCODE_TOKEN ,
1012 p_token1_value => SQLCODE ,
1013 p_token2 => G_SQLERRM_TOKEN ,
1014 p_token2_value => SQLERRM
1015 );
1016
1017 IF c_project_id%ISOPEN THEN
1018 CLOSE c_project_id;
1019 END IF;
1020
1021 END validate_project_id;
1022
1023
1024 --
1025 -- Procedure: validate_task_id
1026 --
1027 -- Description: This procedure is used to validate task_id
1028 --
1029 --
1030
1031 PROCEDURE validate_task_id(p_task_id NUMBER ,
1032 p_return_status OUT NOCOPY VARCHAR2
1033 ) is
1034 cursor c_task_id is
1035 select 'x'
1036 from pa_tasks
1037 where task_id = p_task_id
1038 and task_id = top_task_id;
1039
1040 l_dummy_value VARCHAR2(1) := '?';
1041
1042 BEGIN
1043
1044 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1045
1046 IF (p_task_id is not null) OR
1047 (p_task_id <> OKE_API.G_MISS_NUM) THEN
1048
1049 OPEN c_task_id;
1050 FETCH c_task_id into l_dummy_value;
1051 CLOSE c_task_id;
1052
1053 IF (l_dummy_value = '?') THEN
1054
1055 OKE_API.set_message(p_app_name => G_APP_NAME ,
1056 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1057 p_token1 => 'VALUE' ,
1058 p_token1_value => 'task_id'
1059 );
1060
1061 p_return_status := OKE_API.G_RET_STS_ERROR;
1062
1063 END IF;
1064
1065 END IF;
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1070 OKE_API.set_message(p_app_name => G_APP_NAME ,
1071 p_msg_name => G_UNEXPECTED_ERROR ,
1072 p_token1 => G_SQLCODE_TOKEN ,
1073 p_token1_value => SQLCODE ,
1074 p_token2 => G_SQLERRM_TOKEN ,
1075 p_token2_value => SQLERRM
1076 );
1077
1078 IF c_task_id%ISOPEN THEN
1079 CLOSE c_task_id;
1080 END IF;
1081
1082 END validate_task_id;
1083
1084
1085 --
1086 -- Procedure: validate_fund_type
1087 --
1088 -- Description: This procedure is used to validate fund_type
1089 --
1090 --
1091
1092 PROCEDURE validate_fund_type(p_fund_type VARCHAR2 ,
1093 p_return_status OUT NOCOPY VARCHAR2
1094 ) is
1095 cursor c_fund_type is
1096 select 'x'
1097 from fnd_lookup_values
1098 where lookup_type = 'FUND_TYPE'
1099 and language = userenv('LANG')
1100 and enabled_flag = 'Y'
1101 and lookup_code = upper(p_fund_type);
1102
1103 l_dummy_value VARCHAR2(1) := '?';
1104
1105 BEGIN
1106
1107 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1108
1109 IF (p_fund_type is not null) OR
1110 (p_fund_type <> OKE_API.G_MISS_CHAR) THEN
1111
1112 OPEN c_fund_type;
1113 FETCH c_fund_type into l_dummy_value;
1114 CLOSE c_fund_type;
1115
1116 IF (l_dummy_value = '?') THEN
1117
1118 OKE_API.set_message(p_app_name => G_APP_NAME ,
1119 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1120 p_token1 => 'VALUE' ,
1121 p_token1_value => 'fund_type'
1122 );
1123
1124 p_return_status := OKE_API.G_RET_STS_ERROR;
1125
1126 END IF;
1127
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1133 OKE_API.set_message(p_app_name => G_APP_NAME ,
1134 p_msg_name => G_UNEXPECTED_ERROR ,
1135 p_token1 => G_SQLCODE_TOKEN ,
1136 p_token1_value => SQLCODE ,
1137 p_token2 => G_SQLERRM_TOKEN ,
1138 p_token2_value => SQLERRM
1139 );
1140
1141 IF c_fund_type%ISOPEN THEN
1142 CLOSE c_fund_type;
1143 END IF;
1144
1145 END validate_fund_type;
1146
1147
1148 --
1149 -- Procedure: validate_funding_status
1150 --
1151 -- Description: This procedure is used to validate funding_status
1152 --
1153 --
1154
1155 PROCEDURE validate_funding_status(p_funding_status VARCHAR2 ,
1156 p_return_status OUT NOCOPY VARCHAR2
1157 ) is
1158 cursor c_funding_status is
1159 select 'x'
1160 from fnd_lookup_values
1161 where lookup_type = 'FUNDING_STATUS'
1162 and enabled_flag = 'Y'
1163 and language = userenv('LANG')
1164 and lookup_code = upper(p_funding_status);
1165
1166 l_dummy_value VARCHAR2(1) := '?';
1167
1168 BEGIN
1169
1170 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1171
1172 IF (p_funding_status is not null) OR
1173 (p_funding_status <> OKE_API.G_MISS_CHAR) THEN
1174
1175 OPEN c_funding_status;
1176 FETCH c_funding_status into l_dummy_value;
1177 CLOSE c_funding_status;
1178
1179 IF (l_dummy_value = '?') THEN
1180
1181 OKE_API.set_message(p_app_name => G_APP_NAME ,
1182 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1183 p_token1 => 'VALUE' ,
1184 p_token1_value => 'funding_status'
1185 );
1186
1187 p_return_status := OKE_API.G_RET_STS_ERROR;
1188
1189 END IF;
1190
1191 END IF;
1192
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1196 OKE_API.set_message(p_app_name => G_APP_NAME ,
1197 p_msg_name => G_UNEXPECTED_ERROR ,
1198 p_token1 => G_SQLCODE_TOKEN ,
1199 p_token1_value => SQLCODE ,
1200 p_token2 => G_SQLERRM_TOKEN ,
1201 p_token2_value => SQLERRM
1202 );
1203
1204 IF c_funding_status%ISOPEN THEN
1205 CLOSE c_funding_status;
1206 END IF;
1207
1208 END validate_funding_status;
1209
1210
1211 --
1212 -- Procedure: validate_funding_category
1213 --
1214 -- Description: This procedure is used to validate funding_category
1215 --
1216 --
1217
1218 PROCEDURE validate_funding_category(p_funding_category VARCHAR2 ,
1219 p_return_status OUT NOCOPY VARCHAR2
1220 ) is
1221 cursor c_funding_category is
1222 select 'x'
1223 from pa_lookups
1224 where lookup_type = 'FUNDING CATEGORY TYPE'
1225 and lookup_code = upper(p_funding_category);
1226
1227 l_dummy_value VARCHAR2(1) := '?';
1228
1229 BEGIN
1230
1231 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1232
1233 IF (p_funding_category is null) OR
1234 (p_funding_category = OKE_API.G_MISS_CHAR) THEN
1235
1236 OKE_API.set_message(p_app_name => G_APP_NAME ,
1237 p_msg_name => 'OKE_API_MISSING_VALUE' ,
1238 p_token1 => 'VALUE' ,
1239 p_token1_value => 'funding_category'
1240 );
1241
1242 p_return_status := OKE_API.G_RET_STS_ERROR;
1243
1244 ELSE
1245
1246 OPEN c_funding_category;
1247 FETCH c_funding_category into l_dummy_value;
1248 CLOSE c_funding_category;
1249
1250 IF (l_dummy_value = '?') THEN
1251
1252 OKE_API.set_message(p_app_name => G_APP_NAME ,
1253 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1254 p_token1 => 'VALUE' ,
1255 p_token1_value => 'funding_category'
1256 );
1257
1258 p_return_status := OKE_API.G_RET_STS_ERROR;
1259
1260 END IF;
1261
1262 END IF;
1263
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1267 OKE_API.set_message(p_app_name => G_APP_NAME ,
1268 p_msg_name => G_UNEXPECTED_ERROR ,
1269 p_token1 => G_SQLCODE_TOKEN ,
1270 p_token1_value => SQLCODE ,
1271 p_token2 => G_SQLERRM_TOKEN ,
1272 p_token2_value => SQLERRM
1273 );
1274
1275 IF c_funding_category%ISOPEN THEN
1276 CLOSE c_funding_category;
1277 END IF;
1278
1279 END validate_funding_category;
1280
1281
1282 --
1283 -- Procedure: validate_conversion_type
1284 --
1285 -- Description: This procedure is used to validate conversion_type
1286 --
1287 --
1288
1289 PROCEDURE validate_conversion_type(p_conversion_type VARCHAR2 ,
1290 p_return_status OUT NOCOPY VARCHAR2
1291 ) is
1292 cursor c_conversion_type is
1293 select 'x'
1294 from gl_daily_conversion_types
1295 where UPPER(conversion_type) = UPPER(p_conversion_type);
1296
1297 l_dummy_value VARCHAR2(1) := '?';
1298
1299 BEGIN
1300
1301 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1302
1303 IF (p_conversion_type is not null) OR
1304 (p_conversion_type <> OKE_API.G_MISS_CHAR) THEN
1305
1306 OPEN c_conversion_type;
1307 FETCH c_conversion_type into l_dummy_value;
1308 CLOSE c_conversion_type;
1309
1313 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1310 IF (l_dummy_value = '?') THEN
1311
1312 OKE_API.set_message(p_app_name => G_APP_NAME ,
1314 p_token1 => 'VALUE' ,
1315 p_token1_value => 'pa_conversion_type'
1316 );
1317
1318 p_return_status := OKE_API.G_RET_STS_ERROR;
1319
1320 END IF;
1321
1322 END IF;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1327 OKE_API.set_message(p_app_name => G_APP_NAME ,
1328 p_msg_name => G_UNEXPECTED_ERROR ,
1329 p_token1 => G_SQLCODE_TOKEN ,
1330 p_token1_value => SQLCODE ,
1331 p_token2 => G_SQLERRM_TOKEN ,
1332 p_token2_value => SQLERRM
1333 );
1334
1335 IF c_conversion_type%ISOPEN THEN
1336 CLOSE c_conversion_type;
1337 END IF;
1338
1339 END validate_conversion_type;
1340
1341
1342 --
1343 -- Function: null_allocation_out
1344 --
1345 -- Description: This function is used to set all the missing attribute values to be null
1346 --
1347 --
1348
1349 FUNCTION null_allocation_out(p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE)
1350 RETURN ALLOCATION_REC_IN_TYPE
1351 is
1352 l_allocation_in_rec ALLOCATION_REC_IN_TYPE := p_allocation_in_rec;
1353 BEGIN
1354
1355 l_allocation_in_rec.fund_allocation_id := null;
1356
1357 IF l_allocation_in_rec.agreement_id = OKE_API.G_MISS_NUM THEN
1358 l_allocation_in_rec.agreement_id := null;
1359 END IF;
1360
1361 IF l_allocation_in_rec.amount = OKE_API.G_MISS_NUM THEN
1362 l_allocation_in_rec.amount := null;
1363 END IF;
1364
1365 IF l_allocation_in_rec.funding_source_id = OKE_API.G_MISS_NUM THEN
1366 l_allocation_in_rec.funding_source_id := null;
1367 END IF;
1368
1369 IF l_allocation_in_rec.object_id = OKE_API.G_MISS_NUM THEN
1370 l_allocation_in_rec.object_id := null;
1371 END IF;
1372
1373 IF l_allocation_in_rec.k_line_id = OKE_API.G_MISS_NUM THEN
1374 l_allocation_in_rec.k_line_id := null;
1375 END IF;
1376
1377 IF l_allocation_in_rec.project_id = OKE_API.G_MISS_NUM THEN
1378 l_allocation_in_rec.project_id := null;
1379 END IF;
1380
1381 IF l_allocation_in_rec.task_id = OKE_API.G_MISS_NUM THEN
1382 l_allocation_in_rec.task_id := null;
1383 END IF;
1384
1385 IF l_allocation_in_rec.fund_type = OKE_API.G_MISS_CHAR THEN
1386 l_allocation_in_rec.fund_type := null;
1387 END IF;
1388
1389 IF l_allocation_in_rec.hard_limit = OKE_API.G_MISS_NUM THEN
1390 l_allocation_in_rec.hard_limit := null;
1391 END IF;
1392
1393 IF l_allocation_in_rec.funding_status = OKE_API.G_MISS_CHAR THEN
1394 l_allocation_in_rec.funding_status := null;
1395 END IF;
1396
1397 IF l_allocation_in_rec.fiscal_year = OKE_API.G_MISS_NUM THEN
1398 l_allocation_in_rec.fiscal_year := null;
1399 END IF;
1400
1401 IF l_allocation_in_rec.reference1 = OKE_API.G_MISS_CHAR THEN
1402 l_allocation_in_rec.reference1 := null;
1403 END IF;
1404
1405 IF l_allocation_in_rec.reference2 = OKE_API.G_MISS_CHAR THEN
1406 l_allocation_in_rec.reference2 := null;
1407 END IF;
1408
1409 IF l_allocation_in_rec.reference3 = OKE_API.G_MISS_CHAR THEN
1410 l_allocation_in_rec.reference3 := null;
1411 END IF;
1412
1413 IF l_allocation_in_rec.pa_conversion_type = OKE_API.G_MISS_CHAR THEN
1414 l_allocation_in_rec.pa_conversion_type := null;
1415 END IF;
1416
1417 IF l_allocation_in_rec.pa_conversion_date = OKE_API.G_MISS_DATE THEN
1418 l_allocation_in_rec.pa_conversion_date := null;
1419 END IF;
1420
1421 -- syho, bug 2208979
1422 IF l_allocation_in_rec.pa_conversion_rate = OKE_API.G_MISS_NUM THEN
1423 l_allocation_in_rec.pa_conversion_rate := null;
1424 END IF;
1425 -- syho, bug 2208979
1426
1427 IF l_allocation_in_rec.start_date_active = OKE_API.G_MISS_DATE THEN
1428 l_allocation_in_rec.start_date_active := null;
1429 END IF;
1430
1431 IF l_allocation_in_rec.end_date_active = OKE_API.G_MISS_DATE THEN
1432 l_allocation_in_rec.end_date_active := null;
1433 END IF;
1434 /*
1435 IF l_allocation_in_rec.oke_desc_flex_name = OKE_API.G_MISS_CHAR THEN
1436 l_allocation_in_rec.oke_desc_flex_name := null;
1437 END IF;
1438 */
1439 IF l_allocation_in_rec.oke_attribute_category = OKE_API.G_MISS_CHAR THEN
1440 l_allocation_in_rec.oke_attribute_category := null;
1441 END IF;
1442
1443 IF l_allocation_in_rec.oke_attribute1 = OKE_API.G_MISS_CHAR THEN
1444 l_allocation_in_rec.oke_attribute1 := null;
1445 END IF;
1446
1447 IF l_allocation_in_rec.oke_attribute2 = OKE_API.G_MISS_CHAR THEN
1448 l_allocation_in_rec.oke_attribute2 := null;
1449 END IF;
1450
1451 IF l_allocation_in_rec.oke_attribute3 = OKE_API.G_MISS_CHAR THEN
1452 l_allocation_in_rec.oke_attribute3 := null;
1453 END IF;
1454
1455 IF l_allocation_in_rec.oke_attribute4 = OKE_API.G_MISS_CHAR THEN
1456 l_allocation_in_rec.oke_attribute4 := null;
1457 END IF;
1458
1459 IF l_allocation_in_rec.oke_attribute5 = OKE_API.G_MISS_CHAR THEN
1460 l_allocation_in_rec.oke_attribute5 := null;
1461 END IF;
1462
1463 IF l_allocation_in_rec.oke_attribute6 = OKE_API.G_MISS_CHAR THEN
1464 l_allocation_in_rec.oke_attribute6 := null;
1465 END IF;
1466
1467 IF l_allocation_in_rec.oke_attribute7 = OKE_API.G_MISS_CHAR THEN
1468 l_allocation_in_rec.oke_attribute7 := null;
1469 END IF;
1470
1471 IF l_allocation_in_rec.oke_attribute8 = OKE_API.G_MISS_CHAR THEN
1472 l_allocation_in_rec.oke_attribute8 := null;
1473 END IF;
1474
1475 IF l_allocation_in_rec.oke_attribute9 = OKE_API.G_MISS_CHAR THEN
1476 l_allocation_in_rec.oke_attribute9 := null;
1477 END IF;
1478
1479 IF l_allocation_in_rec.oke_attribute10 = OKE_API.G_MISS_CHAR THEN
1480 l_allocation_in_rec.oke_attribute10 := null;
1481 END IF;
1482
1483 IF l_allocation_in_rec.oke_attribute11 = OKE_API.G_MISS_CHAR THEN
1484 l_allocation_in_rec.oke_attribute11 := null;
1485 END IF;
1486
1487 IF l_allocation_in_rec.oke_attribute12 = OKE_API.G_MISS_CHAR THEN
1488 l_allocation_in_rec.oke_attribute12 := null;
1489 END IF;
1490
1491 IF l_allocation_in_rec.oke_attribute13 = OKE_API.G_MISS_CHAR THEN
1492 l_allocation_in_rec.oke_attribute13 := null;
1493 END IF;
1494
1495 IF l_allocation_in_rec.oke_attribute14 = OKE_API.G_MISS_CHAR THEN
1496 l_allocation_in_rec.oke_attribute14 := null;
1497 END IF;
1498
1499 IF l_allocation_in_rec.oke_attribute15 = OKE_API.G_MISS_CHAR THEN
1500 l_allocation_in_rec.oke_attribute15 := null;
1501 END IF;
1502
1503 IF l_allocation_in_rec.revenue_hard_limit = OKE_API.G_MISS_NUM THEN
1504 l_allocation_in_rec.revenue_hard_limit := null;
1505 END IF;
1506
1507 IF l_allocation_in_rec.pa_attribute_category = OKE_API.G_MISS_CHAR THEN
1508 l_allocation_in_rec.pa_attribute_category := null;
1509 END IF;
1510
1511 IF l_allocation_in_rec.pa_attribute1 = OKE_API.G_MISS_CHAR THEN
1512 l_allocation_in_rec.pa_attribute1 := null;
1513 END IF;
1514
1515 IF l_allocation_in_rec.pa_attribute2 = OKE_API.G_MISS_CHAR THEN
1516 l_allocation_in_rec.pa_attribute2 := null;
1517 END IF;
1518
1519 IF l_allocation_in_rec.pa_attribute3 = OKE_API.G_MISS_CHAR THEN
1520 l_allocation_in_rec.pa_attribute3 := null;
1521 END IF;
1522
1523 IF l_allocation_in_rec.pa_attribute4 = OKE_API.G_MISS_CHAR THEN
1524 l_allocation_in_rec.pa_attribute4 := null;
1525 END IF;
1526
1527 IF l_allocation_in_rec.pa_attribute5 = OKE_API.G_MISS_CHAR THEN
1528 l_allocation_in_rec.pa_attribute5 := null;
1529 END IF;
1530
1531 IF l_allocation_in_rec.pa_attribute6 = OKE_API.G_MISS_CHAR THEN
1532 l_allocation_in_rec.pa_attribute6 := null;
1533 END IF;
1534
1535 IF l_allocation_in_rec.pa_attribute7 = OKE_API.G_MISS_CHAR THEN
1536 l_allocation_in_rec.pa_attribute7 := null;
1537 END IF;
1538
1539 IF l_allocation_in_rec.pa_attribute8 = OKE_API.G_MISS_CHAR THEN
1540 l_allocation_in_rec.pa_attribute8 := null;
1541 END IF;
1542
1543 IF l_allocation_in_rec.pa_attribute9 = OKE_API.G_MISS_CHAR THEN
1544 l_allocation_in_rec.pa_attribute9 := null;
1545 END IF;
1546
1547 IF l_allocation_in_rec.pa_attribute10 = OKE_API.G_MISS_CHAR THEN
1548 l_allocation_in_rec.pa_attribute10 := null;
1549 END IF;
1550
1551 IF l_allocation_in_rec.funding_category = OKE_API.G_MISS_CHAR THEN
1552 l_allocation_in_rec.funding_category := null;
1553 END IF;
1554
1555 return(l_allocation_in_rec);
1556
1557 END null_allocation_out;
1558
1559
1560 --
1561 -- Procedure: validate_populate_rec
1562 --
1563 -- Description: This procedure is used to set all the missing attribute values to the existing values in DB
1564 --
1565 --
1566
1567 PROCEDURE validate_populate_rec(p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
1568 p_allocation_in_rec_out OUT NOCOPY ALLOCATION_REC_IN_TYPE ,
1569 p_previous_amount OUT NOCOPY NUMBER ,
1570 -- p_conversion_rate OUT NOCOPY NUMBER ,
1571 p_flag OUT NOCOPY VARCHAR2
1572 ) is
1573
1574 cursor c_allocation_row is
1575 select *
1576 from oke_k_fund_allocations
1577 where fund_allocation_id = p_allocation_in_rec.fund_allocation_id
1578 FOR UPDATE OF fund_allocation_id NOWAIT;
1579
1580 cursor c_version is
1581 select major_version + 1
1582 from okc_k_vers_numbers
1583 where chr_id = p_allocation_in_rec.object_id;
1584
1585 l_allocation_row c_allocation_row%ROWTYPE;
1586 l_error_value VARCHAR2(50);
1587 l_version NUMBER;
1588
1589 BEGIN
1590
1591 p_flag := 'N';
1592 p_allocation_in_rec_out := p_allocation_in_rec;
1593
1594 OPEN c_version;
1595 FETCH c_version into l_version;
1596 CLOSE c_version;
1597
1598 OPEN c_allocation_row;
1599 FETCH c_allocation_row into l_allocation_row;
1600 CLOSE c_allocation_row;
1604 END IF;
1601
1602 IF p_allocation_in_rec_out.agreement_id = OKE_API.G_MISS_NUM THEN
1603 p_allocation_in_rec_out.agreement_id := null;
1605
1606 IF (p_allocation_in_rec_out.funding_source_id = OKE_API.G_MISS_NUM) THEN
1607 p_allocation_in_rec_out.funding_source_id := l_allocation_row.funding_source_id;
1608
1609 ELSIF (nvl(p_allocation_in_rec_out.funding_source_id, -99) <> l_allocation_row.funding_source_id) THEN
1610
1611 OKE_API.set_message(p_app_name => G_APP_NAME ,
1612 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1613 p_token1 => 'VALUE' ,
1614 p_token1_value => 'funding_source_id'
1615 );
1616
1617 RAISE G_EXCEPTION_HALT_VALIDATION;
1618
1619 END IF;
1620
1621 IF (p_allocation_in_rec_out.object_id = OKE_API.G_MISS_NUM) THEN
1622 p_allocation_in_rec_out.object_id := l_allocation_row.object_id;
1623
1624 ELSIF (nvl(p_allocation_in_rec_out.object_id, -99) <> l_allocation_row.object_id) THEN
1625
1626 OKE_API.set_message(p_app_name => G_APP_NAME ,
1627 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1628 p_token1 => 'VALUE' ,
1629 p_token1_value => 'object_id'
1630 );
1631
1632 RAISE G_EXCEPTION_HALT_VALIDATION;
1633
1634 END IF;
1635
1636 IF (p_allocation_in_rec_out.k_line_id = OKE_API.G_MISS_NUM) THEN
1637 p_allocation_in_rec_out.k_line_id := l_allocation_row.k_line_id;
1638 END IF;
1639
1640 IF p_allocation_in_rec_out.pa_conversion_date = OKE_API.G_MISS_DATE THEN
1641 p_allocation_in_rec_out.pa_conversion_date := l_allocation_row.pa_conversion_date;
1642 END IF;
1643
1644 IF p_allocation_in_rec_out.pa_conversion_type = OKE_API.G_MISS_CHAR THEN
1645 p_allocation_in_rec_out.pa_conversion_type := l_allocation_row.pa_conversion_type;
1646 END IF;
1647
1648 IF p_allocation_in_rec_out.pa_conversion_rate = OKE_API.G_MISS_NUM THEN
1649 p_allocation_in_rec_out.pa_conversion_rate := l_allocation_row.pa_conversion_rate;
1650 END IF;
1651
1652 IF (p_allocation_in_rec_out.project_id = OKE_API.G_MISS_NUM) THEN
1653 p_allocation_in_rec_out.project_id := l_allocation_row.project_id;
1654 END IF;
1655
1656 IF (nvl(p_allocation_in_rec_out.project_id, -99) <> nvl(l_allocation_row.project_id, -99)) THEN
1657
1658 p_flag := 'Y';
1659
1660 ELSIF (nvl(p_allocation_in_rec_out.pa_conversion_type, '-99') <> nvl(l_allocation_row.pa_conversion_type, '-99')) OR
1661 (nvl(to_char(p_allocation_in_rec_out.pa_conversion_date, 'YYYYMMDD'), '19000101') <> nvl(to_char(l_allocation_row.pa_conversion_date, 'YYYYMMDD'), '19000101')) OR
1662 (nvl(p_allocation_in_rec_out.pa_conversion_rate, -99) <> nvl(l_allocation_row.pa_conversion_rate, -99)) THEN
1663 /*
1664 IF (p_allocation_in_rec_out.pa_conversion_type is not null) AND
1665 (p_allocation_in_rec_out.pa_conversion_date is not null) THEN
1666 */
1667 p_flag := 'Y';
1668
1669
1670 -- END IF;
1671
1672 ELSE
1673
1674 p_flag := 'N';
1675
1676 END IF;
1677
1678 IF (p_allocation_in_rec_out.task_id = OKE_API.G_MISS_NUM) THEN
1679 p_allocation_in_rec_out.task_id := l_allocation_row.task_id;
1680 END IF;
1681
1682 --
1683 -- Check values for contract, project and task if created version = current version
1684 --
1685
1686 IF (nvl(p_allocation_in_rec_out.task_id, -99) <> nvl(l_allocation_row.task_id, -99)) AND
1687 ((nvl(l_allocation_row.created_in_version, -99) <> nvl(l_version, -99)) OR
1688 (nvl(l_allocation_row.agreement_version, 0) <> 0)) THEN
1689
1690 OKE_API.set_message(p_app_name => G_APP_NAME ,
1691 p_msg_name => 'OKE_API_NO_UPDATE' ,
1692 p_token1 => 'VALUE' ,
1693 p_token1_value => 'task_id'
1694 );
1695
1696 RAISE G_EXCEPTION_HALT_VALIDATION;
1697
1698 END IF;
1699
1700 IF (nvl(p_allocation_in_rec_out.project_id, -99) <> nvl(l_allocation_row.project_id, -99)) AND
1701 ((nvl(l_allocation_row.created_in_version, -99) <> nvl(l_version, -99)) OR
1702 (nvl(l_allocation_row.agreement_version, 0) <> 0)) THEN
1703
1704
1705 OKE_API.set_message(p_app_name => G_APP_NAME ,
1706 p_msg_name => 'OKE_API_NO_UPDATE' ,
1707 p_token1 => 'VALUE' ,
1708 p_token1_value => 'project_id'
1709 );
1710
1711 RAISE G_EXCEPTION_HALT_VALIDATION;
1712
1713 END IF;
1714
1715 IF (nvl(p_allocation_in_rec_out.k_line_id, -99) <> nvl(l_allocation_row.k_line_id, -99)) AND
1716 ((nvl(l_allocation_row.created_in_version, -99) <> nvl(l_version, -99)) OR
1717 ((nvl(l_allocation_row.agreement_version, 0) <> 0) AND (nvl(l_allocation_row.pa_flag, 'N') = 'N'))) THEN
1718
1719 OKE_API.set_message(p_app_name => G_APP_NAME ,
1720 p_msg_name => 'OKE_API_NO_UPDATE' ,
1721 p_token1 => 'VALUE' ,
1722 p_token1_value => 'k_line_id'
1723 );
1724
1725 RAISE G_EXCEPTION_HALT_VALIDATION;
1726
1727 END IF;
1728
1729 IF p_allocation_in_rec_out.start_date_active = OKE_API.G_MISS_DATE THEN
1730 p_allocation_in_rec_out.start_date_active := l_allocation_row.start_date_active;
1731 END IF;
1732
1733 IF (nvl(l_allocation_row.agreement_version, 0) <> 0) AND
1734 (nvl(to_char(p_allocation_in_rec_out.start_date_active, 'YYYYMMDD'), '19000101') <> nvl(to_char(l_allocation_row.start_date_active, 'YYYYMMDD'), '19000101')) THEN
1735
1736 OKE_API.set_message(p_app_name => G_APP_NAME ,
1737 p_msg_name => 'OKE_API_NO_UPDATE' ,
1738 p_token1 => 'VALUE' ,
1739 p_token1_value => 'start_date_active'
1740 );
1741
1742 RAISE G_EXCEPTION_HALT_VALIDATION;
1743
1744 END IF;
1745
1746 IF (p_allocation_in_rec_out.amount = OKE_API.G_MISS_NUM) THEN
1747 p_allocation_in_rec_out.amount := l_allocation_row.amount;
1748 END IF;
1749
1750 --
1751 -- Check if agreement exists
1752 --
1753 /*
1754 IF (nvl(l_allocation_row.agreement_version, 0) <> 0) THEN
1755
1756 IF (nvl(l_allocation_row.project_id, -99) <> nvl(p_allocation_in_rec_out.project_id, -99)) THEN
1757
1758 l_error_value := 'Project';
1759
1760 ELSIF (nvl(l_allocation_row.task_id, -99) <> nvl(p_allocation_in_rec_out.task_id, -99)) THEN
1761
1762 l_error_value := 'Task';
1763
1764 ELSIF (l_allocation_row.start_date_active <> p_allocation_in_rec_out.start_date_active) THEN
1765
1766 l_error_value := 'Start date active';
1767
1768 END IF;
1769
1770 IF (l_error_value is not null) THEN
1771
1772 OKE_API.set_message(p_app_name => G_APP_NAME ,
1773 p_msg_name => 'OKE_NO_FUND_CHANGE' ,
1774 p_token1 => 'FIELD' ,
1775 p_token1_value => l_error_value
1776 );
1777
1778 RAISE G_EXCEPTION_HALT_VALIDATION;
1779
1780 END IF;
1781
1782 END IF;
1783 */
1784 IF p_allocation_in_rec_out.funding_status = OKE_API.G_MISS_CHAR THEN
1785 p_allocation_in_rec_out.funding_status := l_allocation_row.funding_status;
1786 END IF;
1787
1788 IF p_allocation_in_rec_out.fund_type = OKE_API.G_MISS_CHAR THEN
1789 p_allocation_in_rec_out.fund_type := l_allocation_row.fund_type;
1790 END IF;
1791
1792 IF p_allocation_in_rec_out.end_date_active = OKE_API.G_MISS_DATE THEN
1793 p_allocation_in_rec_out.end_date_active := l_allocation_row.end_date_active;
1794 END IF;
1795
1796 IF p_allocation_in_rec_out.fiscal_year = OKE_API.G_MISS_NUM THEN
1797 p_allocation_in_rec_out.fiscal_year := l_allocation_row.fiscal_year;
1798 END IF;
1799
1800 IF (p_allocation_in_rec_out.hard_limit = OKE_API.G_MISS_NUM) THEN
1801 p_allocation_in_rec_out.hard_limit := l_allocation_row.hard_limit;
1802 END IF;
1803
1804 IF p_allocation_in_rec_out.reference1 = OKE_API.G_MISS_CHAR THEN
1805 p_allocation_in_rec_out.reference1 := l_allocation_row.reference1;
1806 END IF;
1807
1808 IF p_allocation_in_rec_out.reference2 = OKE_API.G_MISS_CHAR THEN
1809 p_allocation_in_rec_out.reference2 := l_allocation_row.reference2;
1810 END IF;
1811
1812 IF p_allocation_in_rec_out.reference3 = OKE_API.G_MISS_CHAR THEN
1813 p_allocation_in_rec_out.reference3 := l_allocation_row.reference3;
1814 END IF;
1815
1816 IF p_allocation_in_rec_out.oke_attribute_category = OKE_API.G_MISS_CHAR THEN
1817 p_allocation_in_rec_out.oke_attribute_category := l_allocation_row.attribute_category;
1818 END IF;
1819
1820 IF p_allocation_in_rec_out.oke_attribute1 = OKE_API.G_MISS_CHAR THEN
1821 p_allocation_in_rec_out.oke_attribute1 := l_allocation_row.attribute1;
1822 END IF;
1823
1824 IF p_allocation_in_rec_out.oke_attribute2 = OKE_API.G_MISS_CHAR THEN
1825 p_allocation_in_rec_out.oke_attribute2 := l_allocation_row.attribute2;
1826 END IF;
1827
1828 IF p_allocation_in_rec_out.oke_attribute3 = OKE_API.G_MISS_CHAR THEN
1829 p_allocation_in_rec_out.oke_attribute3 := l_allocation_row.attribute3;
1830 END IF;
1831
1832 IF p_allocation_in_rec_out.oke_attribute4 = OKE_API.G_MISS_CHAR THEN
1833 p_allocation_in_rec_out.oke_attribute4 := l_allocation_row.attribute4;
1834 END IF;
1835
1836 IF p_allocation_in_rec_out.oke_attribute5 = OKE_API.G_MISS_CHAR THEN
1837 p_allocation_in_rec_out.oke_attribute5 := l_allocation_row.attribute5;
1838 END IF;
1839
1840 IF p_allocation_in_rec_out.oke_attribute6 = OKE_API.G_MISS_CHAR THEN
1841 p_allocation_in_rec_out.oke_attribute6 := l_allocation_row.attribute6;
1842 END IF;
1843
1844 IF p_allocation_in_rec_out.oke_attribute7 = OKE_API.G_MISS_CHAR THEN
1845 p_allocation_in_rec_out.oke_attribute7 := l_allocation_row.attribute7;
1846 END IF;
1847
1848 IF p_allocation_in_rec_out.oke_attribute8 = OKE_API.G_MISS_CHAR THEN
1849 p_allocation_in_rec_out.oke_attribute8 := l_allocation_row.attribute8;
1850 END IF;
1851
1852 IF p_allocation_in_rec_out.oke_attribute9 = OKE_API.G_MISS_CHAR THEN
1853 p_allocation_in_rec_out.oke_attribute9 := l_allocation_row.attribute9;
1854 END IF;
1855
1856 IF p_allocation_in_rec_out.oke_attribute10 = OKE_API.G_MISS_CHAR THEN
1857 p_allocation_in_rec_out.oke_attribute10 := l_allocation_row.attribute10;
1858 END IF;
1859
1860 IF p_allocation_in_rec_out.oke_attribute11 = OKE_API.G_MISS_CHAR THEN
1861 p_allocation_in_rec_out.oke_attribute11 := l_allocation_row.attribute11;
1862 END IF;
1863
1864 IF p_allocation_in_rec_out.oke_attribute12 = OKE_API.G_MISS_CHAR THEN
1865 p_allocation_in_rec_out.oke_attribute12 := l_allocation_row.attribute12;
1866 END IF;
1867
1868 IF p_allocation_in_rec_out.oke_attribute13 = OKE_API.G_MISS_CHAR THEN
1869 p_allocation_in_rec_out.oke_attribute13 := l_allocation_row.attribute13;
1870 END IF;
1871
1872 IF p_allocation_in_rec_out.oke_attribute14 = OKE_API.G_MISS_CHAR THEN
1873 p_allocation_in_rec_out.oke_attribute14 := l_allocation_row.attribute14;
1874 END IF;
1875
1876 IF p_allocation_in_rec_out.oke_attribute15 = OKE_API.G_MISS_CHAR THEN
1877 p_allocation_in_rec_out.oke_attribute15 := l_allocation_row.attribute15;
1878 END IF;
1879
1880 IF p_allocation_in_rec_out.revenue_hard_limit = OKE_API.G_MISS_NUM THEN
1881 p_allocation_in_rec_out.revenue_hard_limit := l_allocation_row.revenue_hard_limit;
1882 END IF;
1883
1884 IF p_allocation_in_rec_out.pa_attribute_category = OKE_API.G_MISS_CHAR THEN
1885 p_allocation_in_rec_out.pa_attribute_category := l_allocation_row.pa_attribute_category;
1886 END IF;
1887
1888 IF p_allocation_in_rec_out.pa_attribute1 = OKE_API.G_MISS_CHAR THEN
1889 p_allocation_in_rec_out.pa_attribute1 := l_allocation_row.pa_attribute1;
1890 END IF;
1891
1892 IF p_allocation_in_rec_out.pa_attribute2 = OKE_API.G_MISS_CHAR THEN
1893 p_allocation_in_rec_out.pa_attribute2 := l_allocation_row.pa_attribute2;
1894 END IF;
1895
1896 IF p_allocation_in_rec_out.pa_attribute3 = OKE_API.G_MISS_CHAR THEN
1897 p_allocation_in_rec_out.pa_attribute3 := l_allocation_row.pa_attribute3;
1898 END IF;
1899
1900 IF p_allocation_in_rec_out.pa_attribute4 = OKE_API.G_MISS_CHAR THEN
1901 p_allocation_in_rec_out.pa_attribute4 := l_allocation_row.pa_attribute4;
1902 END IF;
1903
1904 IF p_allocation_in_rec_out.pa_attribute5 = OKE_API.G_MISS_CHAR THEN
1905 p_allocation_in_rec_out.pa_attribute5 := l_allocation_row.pa_attribute5;
1906 END IF;
1907
1908 IF p_allocation_in_rec_out.pa_attribute6 = OKE_API.G_MISS_CHAR THEN
1909 p_allocation_in_rec_out.pa_attribute6 := l_allocation_row.pa_attribute6;
1910 END IF;
1911
1912 IF p_allocation_in_rec_out.pa_attribute7 = OKE_API.G_MISS_CHAR THEN
1913 p_allocation_in_rec_out.pa_attribute7 := l_allocation_row.pa_attribute7;
1914 END IF;
1915
1916 IF p_allocation_in_rec_out.pa_attribute8 = OKE_API.G_MISS_CHAR THEN
1917 p_allocation_in_rec_out.pa_attribute8 := l_allocation_row.pa_attribute8;
1918 END IF;
1919
1920 IF p_allocation_in_rec_out.pa_attribute9 = OKE_API.G_MISS_CHAR THEN
1921 p_allocation_in_rec_out.pa_attribute9 := l_allocation_row.pa_attribute9;
1922 END IF;
1923
1924 IF p_allocation_in_rec_out.pa_attribute10 = OKE_API.G_MISS_CHAR THEN
1925 p_allocation_in_rec_out.pa_attribute10 := l_allocation_row.pa_attribute10;
1926 END IF;
1927
1928 IF p_allocation_in_rec_out.funding_category = OKE_API.G_MISS_CHAR THEN
1929 p_allocation_in_rec_out.funding_category := l_allocation_row.funding_category;
1930 END IF;
1931
1932 IF (nvl(l_allocation_row.agreement_version, 0) <> 0) AND
1933 (nvl(p_allocation_in_rec_out.funding_category, '-99') <> l_allocation_row.funding_category) THEN
1934
1935 OKE_API.set_message(p_app_name => G_APP_NAME ,
1936 p_msg_name => 'OKE_API_NO_UPDATE' ,
1937 p_token1 => 'VALUE' ,
1938 p_token1_value => 'funding_category'
1939 );
1940
1941 RAISE G_EXCEPTION_HALT_VALIDATION;
1942
1943 END IF;
1944
1945 --p_conversion_rate := l_allocation_row.pa_conversion_rate;
1946 p_previous_amount := l_allocation_row.previous_amount;
1947
1948 END validate_populate_rec;
1949
1950
1951 --
1952 -- Procedure: validate_attributes
1953 --
1954 -- Description: This procedure is used to validate allocation record attributes
1955 --
1956 --
1957
1958 PROCEDURE validate_attributes(p_allocation_in_rec ALLOCATION_REC_IN_TYPE) is
1959 l_return_status VARCHAR2(1);
1960 BEGIN
1961
1962 --
1963 -- Funding_Source_Id
1964 --
1965
1969
1966 validate_funding_source_id(p_funding_source_id => p_allocation_in_rec.funding_source_id ,
1967 p_return_status => l_return_status
1968 );
1970 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1971
1972 RAISE G_EXCEPTION_HALT_VALIDATION;
1973
1974 END IF;
1975
1976 --
1977 -- Object Id
1978 --
1979
1980 validate_object_id(p_object_id => p_allocation_in_rec.object_id ,
1981 p_funding_source_id => p_allocation_in_rec.funding_source_id ,
1982 p_return_status => l_return_status
1983 );
1984
1985 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1986
1987 RAISE G_EXCEPTION_HALT_VALIDATION;
1988
1989 END IF;
1990
1991 --
1992 -- Amount
1993 --
1994
1995 validate_amount(p_amount => p_allocation_in_rec.amount ,
1996 p_return_status => l_return_status
1997 );
1998
1999 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2000
2001 RAISE G_EXCEPTION_HALT_VALIDATION;
2002
2003 END IF;
2004
2005 --
2006 -- K_Line_Id
2007 --
2008
2009 validate_k_line_id(p_k_line_id => p_allocation_in_rec.k_line_id ,
2010 p_project_id => p_allocation_in_rec.project_id ,
2011 p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2012 p_return_status => l_return_status
2013 );
2014
2015 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2016
2017 RAISE G_EXCEPTION_HALT_VALIDATION;
2018
2019 END IF;
2020
2021 --
2022 -- Project_id
2023 --
2024
2025 validate_project_id(p_project_id => p_allocation_in_rec.project_id ,
2026 p_k_line_id => p_allocation_in_rec.k_line_id ,
2027 p_funding_source_id => p_allocation_in_rec.funding_source_id ,
2028 p_object_id => p_allocation_in_rec.object_id ,
2029 p_return_status => l_return_status
2030 );
2031
2032 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2033
2034 RAISE G_EXCEPTION_HALT_VALIDATION;
2035
2036 END IF;
2037
2038 --
2039 -- Task_id
2040 --
2041
2042 validate_task_id(p_task_id => p_allocation_in_rec.task_id ,
2043 p_return_status => l_return_status
2044 );
2045
2046 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2047
2048 RAISE G_EXCEPTION_HALT_VALIDATION;
2049
2050 END IF;
2051
2052 --
2053 -- Fund_type
2054 --
2055
2056 validate_fund_type(p_fund_type => p_allocation_in_rec.fund_type ,
2057 p_return_status => l_return_status
2058 );
2059
2060 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2061
2062 RAISE G_EXCEPTION_HALT_VALIDATION;
2063
2064 END IF;
2065
2066 --
2067 -- Funding_status
2068 --
2069
2070 validate_funding_status(p_funding_status => p_allocation_in_rec.funding_status ,
2071 p_return_status => l_return_status
2072 );
2073
2074 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2075
2076 RAISE G_EXCEPTION_HALT_VALIDATION;
2077
2078 END IF;
2079
2080 -- Conversion_type
2081 validate_conversion_type(p_conversion_type => p_allocation_in_rec.pa_conversion_type ,
2082 p_return_status => l_return_status
2083 );
2084
2085 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2086
2087 RAISE G_EXCEPTION_HALT_VALIDATION;
2088
2089 END IF;
2090
2091 --
2092 -- Funding_category
2093 --
2094
2095 validate_funding_category(p_funding_category => p_allocation_in_rec.funding_category ,
2096 p_return_status => l_return_status
2097 );
2098
2099 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2100
2101 RAISE G_EXCEPTION_HALT_VALIDATION;
2102
2103 END IF;
2104
2105 END validate_attributes;
2106
2107
2108 --
2109 -- Procedure: validate_record
2110 --
2111 -- Description: This procedure is used to validate allocation record
2112 --
2113 --
2114
2115 PROCEDURE validate_record(p_allocation_in_rec IN OUT NOCOPY ALLOCATION_REC_IN_TYPE ,
2116 p_validation_flag VARCHAR2 ,
2117 p_flag VARCHAR2
2118 -- p_conversion_rate OUT NOCOPY NUMBER
2119 ) is
2120
2121 l_return_status VARCHAR2(1);
2122 l_source_currency VARCHAR2(15);
2123 l_projfunc_currency VARCHAR2(15);
2124 l_type VARCHAR2(20);
2125
2126 BEGIN
2127
2128 --
2129 -- Start and End date range
2130 --
2131
2132 OKE_FUNDING_UTIL_PKG.validate_start_end_date(x_start_date => p_allocation_in_rec.start_date_active ,
2133 x_end_date => p_allocation_in_rec.end_date_active ,
2134 x_return_status => l_return_status
2135 );
2136
2137 IF (l_return_status = 'N') THEN
2138
2139 OKE_API.set_message(p_app_name => 'OKE' ,
2140 p_msg_name => 'OKE_INVALID_EFFDATE_PAIR'
2141 );
2142
2143 RAISE G_EXCEPTION_HALT_VALIDATION;
2144
2145 END IF;
2146
2147 --
2148 -- Validate if date range within source date range
2149 --
2150
2151 IF (p_validation_flag = OKE_API.G_TRUE) THEN
2152
2153 -- Start date
2154 -- bug 3345170
2155 /*
2159 x_date => p_allocation_in_rec.start_date_active ,
2156 OKE_FUNDING_UTIL_PKG.validate_alloc_source_date
2157 (x_start_end => 'START' ,
2158 x_funding_source_id => p_allocation_in_rec.funding_source_id ,
2160 x_return_status => l_return_status
2161 );
2162
2163 IF (l_return_status = 'N') THEN
2164
2165 OKE_API.set_message(p_app_name => G_APP_NAME ,
2166 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
2167 p_token1 => 'EFFECTIVE_DATE' ,
2168 p_token1_value => 'OKE_EFFECTIVE_FROM_PROMPT' ,
2169 p_token1_translate => OKE_API.G_TRUE ,
2170 p_token2 => 'OPERATOR' ,
2171 p_token2_value => 'OKE_GREATER_PROMPT' ,
2172 p_token2_translate => OKE_API.G_TRUE ,
2173 p_token3 => 'DATE_SOURCE' ,
2174 p_token3_value => 'OKE_FUNDING_SOURCE_PROMPT' ,
2175 p_token3_translate => OKE_API.G_TRUE
2176 );
2177
2178 RAISE G_EXCEPTION_HALT_VALIDATION;
2179
2180 END IF;
2181
2182 -- End date
2183
2184 OKE_FUNDING_UTIL_PKG.validate_alloc_source_date
2185 (x_start_end => 'END' ,
2186 x_funding_source_id => p_allocation_in_rec.funding_source_id ,
2187 x_date => p_allocation_in_rec.end_date_active ,
2188 x_return_status => l_return_status
2189 );
2190
2191 IF (l_return_status = 'N') THEN
2192
2193 OKE_API.set_message(p_app_name => G_APP_NAME ,
2194 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
2195 p_token1 => 'EFFECTIVE_DATE' ,
2196 p_token1_value => 'OKE_EFFECTIVE_TO_PROMPT' ,
2197 p_token1_translate => OKE_API.G_TRUE ,
2198 p_token2 => 'OPERATOR' ,
2199 p_token2_value => 'OKE_EARLIER_PROMPT' ,
2200 p_token2_translate => OKE_API.G_TRUE ,
2201 p_token3 => 'DATE_SOURCE' ,
2202 p_token3_value => 'OKE_FUNDING_SOURCE_PROMPT' ,
2203 p_token3_translate => OKE_API.G_TRUE
2204 );
2205
2206 RAISE G_EXCEPTION_HALT_VALIDATION;
2207
2208 END IF;
2209 */
2210 --
2211 -- Validate if enough fund amount to be allocated
2212 --
2213
2214 --oke_debug.debug('validating if enough funding amount for funding allocation');
2215 --dbms_output.put_line('validating if enough funding amount for funding allocation');
2216
2217 OKE_FUNDING_UTIL_PKG.validate_alloc_source_amount
2218 (x_source_id => p_allocation_in_rec.funding_source_id ,
2219 x_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2220 x_amount => p_allocation_in_rec.amount ,
2221 x_return_status => l_return_status
2222 );
2223
2224 IF (l_return_status = 'N') THEN
2225
2226 OKE_API.set_message(p_app_name => 'OKE' ,
2227 p_msg_name => 'OKE_FUND_AMT_EXCEED'
2228 );
2229
2230 RAISE G_EXCEPTION_HALT_VALIDATION;
2231
2232 ELSIF (l_return_status = 'E') THEN
2233
2234 OKE_API.set_message(p_app_name => 'OKE' ,
2235 p_msg_name => 'OKE_NEGATIVE_ALLOCATION_SUM'
2236 );
2237
2238 RAISE G_EXCEPTION_HALT_VALIDATION;
2239
2240 END IF;
2241
2242 --
2243 -- Validate if enough limit amount to be allocated
2244 --
2245
2246 --oke_debug.debug('validating if enough hard limit to be allocated');
2247 --dbms_output.put_line('validating if enough hard limit to be allocated');
2248
2249 OKE_FUNDING_UTIL_PKG.validate_alloc_source_limit
2250 (x_source_id => p_allocation_in_rec.funding_source_id ,
2251 x_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2252 x_amount => nvl(p_allocation_in_rec.hard_limit, 0) ,
2253 x_revenue_amount => nvl(p_allocation_in_rec.revenue_hard_limit, 0) ,
2254 x_type => l_type ,
2255 x_return_status => l_return_status
2256 );
2257
2258 IF (l_return_status = 'N') THEN
2259
2260 IF (l_type = 'INVOICE') THEN
2261
2262 OKE_API.set_message(p_app_name => 'OKE' ,
2263 p_msg_name => 'OKE_HARD_LIMIT_EXCEED'
2264 );
2265
2266 ELSE
2267
2268 OKE_API.set_message(p_app_name => 'OKE' ,
2269 p_msg_name => 'OKE_REV_LIMIT_EXCEED'
2270 );
2271
2272 END IF;
2273
2274 RAISE G_EXCEPTION_HALT_VALIDATION;
2275
2276 ELSIF (l_return_status = 'E') THEN
2277
2278 IF (l_type = 'INVOICE') THEN
2279
2280 OKE_API.set_message(p_app_name => 'OKE' ,
2281 p_msg_name => 'OKE_NEGATIVE_HARD_LIMIT_SUM'
2282 );
2283
2284 ELSE
2285
2286 OKE_API.set_message(p_app_name => 'OKE' ,
2287 p_msg_name => 'OKE_NEGATIVE_REV_LIMIT_SUM'
2288 );
2289
2290 END IF;
2291
2292 RAISE G_EXCEPTION_HALT_VALIDATION;
2293
2294 END IF;
2295
2296 END IF;
2297
2298 --
2299 -- Validate the combination of project_id and task_id
2300 --
2301
2302 validate_project_task(p_project_id => p_allocation_in_rec.project_id ,
2303 p_task_id => p_allocation_in_rec.task_id
2304 );
2305
2306 --
2307 -- Validate the combination of object_id and k_line_id
2308 --
2309
2313
2310 validate_header_line(p_object_id => p_allocation_in_rec.object_id ,
2311 p_k_line_id => p_allocation_in_rec.k_line_id
2312 );
2314 --
2315 -- Validate PA conversion
2316 --
2317
2318 IF (p_allocation_in_rec.project_id is not null) THEN
2319
2320 l_source_currency := get_source_currency(p_allocation_in_rec.funding_source_id);
2321 get_proj_info(p_project_id => p_allocation_in_rec.project_id ,
2322 p_projfunc_currency => l_projfunc_currency
2323 );
2324
2325 IF (l_source_currency = l_projfunc_currency) THEN
2326
2327 IF (p_allocation_in_rec.pa_conversion_type is not null) THEN
2328
2329 OKE_API.set_message(p_app_name => 'OKE' ,
2330 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2331 p_token1 => 'VALUE' ,
2332 p_token1_value => 'pa_conversion_type'
2333 );
2334
2335 RAISE G_EXCEPTION_HALT_VALIDATION;
2336
2337 ELSIF (p_allocation_in_rec.pa_conversion_date is not null) THEN
2338
2339 OKE_API.set_message(p_app_name => 'OKE' ,
2340 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2341 p_token1 => 'VALUE' ,
2342 p_token1_value => 'pa_conversion_date'
2343 );
2344
2345 RAISE G_EXCEPTION_HALT_VALIDATION;
2346
2347 ELSIF (p_allocation_in_rec.pa_conversion_rate is not null) THEN
2348
2349 OKE_API.set_message(p_app_name => 'OKE' ,
2350 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2351 p_token1 => 'VALUE' ,
2352 p_token1_value => 'pa_conversion_rate'
2353 );
2354
2355 RAISE G_EXCEPTION_HALT_VALIDATION;
2356
2357 END IF;
2358
2359 ELSIF (p_allocation_in_rec.pa_conversion_type is not null) AND
2360 (p_allocation_in_rec.pa_conversion_date is not null) THEN
2361
2362 IF (upper(p_allocation_in_rec.pa_conversion_type) <> 'USER') THEN
2363
2364 IF (p_allocation_in_rec.pa_conversion_rate is null) THEN
2365
2366 IF (nvl(p_flag, 'N') = 'Y') THEN
2367
2368 OKE_FUNDING_UTIL_PKG.get_conversion_rate(x_from_currency => l_source_currency ,
2369 x_to_currency => l_projfunc_currency ,
2370 x_conversion_type => p_allocation_in_rec.pa_conversion_type ,
2371 x_conversion_date => p_allocation_in_rec.pa_conversion_date ,
2372 x_conversion_rate => p_allocation_in_rec.pa_conversion_rate ,
2373 x_return_status => l_return_status
2374 );
2375
2376 IF (l_return_status = 'N') THEN
2377
2378 OKE_API.set_message(p_app_name => G_APP_NAME ,
2379 p_msg_name => 'OKE_FUND_NO_RATE'
2380 );
2381
2382 RAISE G_EXCEPTION_HALT_VALIDATION;
2383
2384 END IF;
2385
2386 END IF;
2387
2388 ELSIF (nvl(p_flag, 'N') = 'Y') THEN
2389
2390 OKE_API.set_message(p_app_name => 'OKE' ,
2391 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2392 p_token1 => 'VALUE' ,
2393 p_token1_value => 'pa_conversion_rate'
2394 );
2395
2396 RAISE G_EXCEPTION_HALT_VALIDATION;
2397
2398 END IF;
2399
2400 END IF;
2401
2402 ELSIF (nvl(upper(p_allocation_in_rec.pa_conversion_type), '-99') <> 'USER') THEN
2403
2404 IF (p_allocation_in_rec.pa_conversion_rate is not null) THEN
2405
2406 OKE_API.set_message(p_app_name => 'OKE' ,
2407 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2408 p_token1 => 'VALUE' ,
2409 p_token1_value => 'pa_conversion_rate'
2410 );
2411
2412 RAISE G_EXCEPTION_HALT_VALIDATION;
2413
2414 END IF;
2415
2416 END IF;
2417
2418 ELSIF (p_allocation_in_rec.pa_conversion_type is not null) THEN
2419
2420 OKE_API.set_message(p_app_name => 'OKE' ,
2421 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2422 p_token1 => 'VALUE' ,
2423 p_token1_value => 'pa_conversion_type'
2424 );
2425
2426 RAISE G_EXCEPTION_HALT_VALIDATION;
2427
2428 ELSIF (p_allocation_in_rec.pa_conversion_date is not null) THEN
2429
2430 OKE_API.set_message(p_app_name => 'OKE' ,
2431 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2432 p_token1 => 'VALUE' ,
2433 p_token1_value => 'pa_conversion_date'
2434 );
2435
2436 RAISE G_EXCEPTION_HALT_VALIDATION;
2437
2438 ELSIF (p_allocation_in_rec.pa_conversion_rate is not null) THEN
2439
2440 OKE_API.set_message(p_app_name => 'OKE' ,
2441 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2442 p_token1 => 'VALUE' ,
2443 p_token1_value => 'pa_conversion_rate'
2444 );
2445
2446 RAISE G_EXCEPTION_HALT_VALIDATION;
2447
2448 END IF;
2449
2450 END validate_record;
2451
2452
2453 --
2454 -- Public Procedures and Funtions
2455 --
2456
2457 --
2458 -- Procedure add_allocation
2459 --
2460 -- Description: This procedure is used to insert record in OKE_K_FUND_ALLOCATIONS table
2461 --
2462 -- Calling subprograms: OKE_API.start_activity
2463 -- OKE_API.end_activity
2464 -- OKE_FUNDINGALLOCATION_PVT.insert_row
2465 -- null_allocation_out
2466 -- validate_attributes
2467 -- validate_record
2468 --
2469
2470 PROCEDURE add_allocation(p_api_version IN NUMBER ,
2471 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
2472 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
2473 p_msg_count OUT NOCOPY NUMBER ,
2474 p_msg_data OUT NOCOPY VARCHAR2 ,
2475 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
2476 p_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE ,
2477 p_validation_flag IN VARCHAR2 := OKE_API.G_TRUE ,
2478 p_return_status OUT NOCOPY VARCHAR2
2479 ) is
2480
2481 l_return_status VARCHAR2(1);
2482 l_rowid VARCHAR2(30);
2483 l_fund_allocation_id NUMBER;
2484 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
2485 l_api_name CONSTANT VARCHAR2(30) := 'add_allocation';
2486 --l_rate NUMBER;
2487
2488 BEGIN
2489
2490 --dbms_output.put_line('entering oke_allocation_pvt.add_allocation');
2491 --oke_debug.debug('entering oke_allocation_pvt.add_allocation');
2492
2493 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2494 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
2495
2496 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2497 p_pkg_name => G_PKG_NAME ,
2498 p_init_msg_list => p_init_msg_list ,
2499 l_api_version => G_API_VERSION_NUMBER ,
2500 p_api_version => p_api_version ,
2501 p_api_type => '_PVT' ,
2502 x_return_status => p_return_status
2503 );
2504
2505 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2506
2507 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2508
2509 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2510
2511 RAISE OKE_API.G_EXCEPTION_ERROR;
2512
2513 END IF;
2514
2515 --
2516 -- Set Default Null
2517 --
2518
2519 --dbms_output.put_line('set default value as null for all fields');
2520 --oke_debug.debug('set default value as null for all fields');
2521
2522 l_allocation_in_rec := null_allocation_out(p_allocation_in_rec => p_allocation_in_rec);
2523
2524 --
2525 -- Validate Attributes
2526 --
2527
2528 --dbms_output.put_line('validate record attributes');
2529 --oke_debug.debug('validate record attributes');
2530
2531 validate_attributes(p_allocation_in_rec => l_allocation_in_rec);
2532
2533 --
2534 -- Validate record
2535 --
2536
2537 --dbms_output.put_line('validate record');
2538 --oke_debug.debug('validate record');
2539
2540 validate_record(p_allocation_in_rec => l_allocation_in_rec ,
2541 p_validation_flag => p_validation_flag ,
2542 p_flag => 'Y'
2543 --p_conversion_rate => l_rate
2544 );
2545
2546 l_fund_allocation_id := get_fund_allocation_id;
2547 p_allocation_out_rec.fund_allocation_id := l_fund_allocation_id;
2548
2549 --dbms_output.put_line('calling oke_fundingallocation_pvt.insert_row from oke_allocation_pvt');
2550 --oke_debug.debug('calling oke_fundingallocation_pvt.insert_row from oke_allocation_pvt');
2551
2552 OKE_FUNDINGALLOCATION_PVT.insert_row(X_Rowid => l_rowid ,
2553 X_Fund_Allocation_Id => l_fund_allocation_id ,
2554 X_Funding_Source_Id => l_allocation_in_rec.funding_source_id ,
2555 X_Object_Id => l_allocation_in_rec.object_id ,
2556 X_K_Line_Id => l_allocation_in_rec.k_line_id ,
2557 X_Project_Id => l_allocation_in_rec.project_id ,
2558 X_Task_Id => l_allocation_in_rec.task_id ,
2559 X_Previous_Amount => 0 ,
2560 X_Amount => l_allocation_in_rec.amount ,
2561 X_Hard_Limit => l_allocation_in_rec.hard_limit ,
2562 X_Fund_Type => upper(l_allocation_in_rec.fund_type) ,
2563 X_Funding_Status => upper(l_allocation_in_rec.funding_status) ,
2564 X_Fiscal_Year => l_allocation_in_rec.fiscal_year ,
2565 X_Reference1 => l_allocation_in_rec.reference1 ,
2566 X_Reference2 => l_allocation_in_rec.reference2 ,
2567 X_Reference3 => l_allocation_in_rec.reference3 ,
2568 X_PA_CONVERSION_TYPE => l_allocation_in_rec.PA_CONVERSION_TYPE ,
2572 X_Start_Date_Active => l_allocation_in_rec.start_date_active ,
2569 X_PA_CONVERSION_DATE => l_allocation_in_rec.PA_CONVERSION_DATE ,
2570 X_PA_CONVERSION_RATE => l_allocation_in_rec.pa_conversion_rate ,
2571 X_Insert_Update_Flag => 'Y' ,
2573 X_End_Date_Active => l_allocation_in_rec.end_date_active ,
2574 X_Last_Update_Date => sysdate ,
2575 X_Last_Updated_By => L_USERID ,
2576 X_Creation_Date => sysdate ,
2577 X_Created_By => L_USERID ,
2578 X_Last_Update_Login => L_LOGINID ,
2579 --X_Attribute_Category => upper(l_allocation_in_rec.oke_attribute_category) ,
2580 X_Attribute_Category => l_allocation_in_rec.oke_attribute_category ,
2581 X_Attribute1 => l_allocation_in_rec.oke_attribute1 ,
2582 X_Attribute2 => l_allocation_in_rec.oke_attribute2 ,
2583 X_Attribute3 => l_allocation_in_rec.oke_attribute3 ,
2584 X_Attribute4 => l_allocation_in_rec.oke_attribute4 ,
2585 X_Attribute5 => l_allocation_in_rec.oke_attribute5 ,
2586 X_Attribute6 => l_allocation_in_rec.oke_attribute6 ,
2587 X_Attribute7 => l_allocation_in_rec.oke_attribute7 ,
2588 X_Attribute8 => l_allocation_in_rec.oke_attribute8 ,
2589 X_Attribute9 => l_allocation_in_rec.oke_attribute9 ,
2590 X_Attribute10 => l_allocation_in_rec.oke_attribute10 ,
2591 X_Attribute11 => l_allocation_in_rec.oke_attribute11 ,
2592 X_Attribute12 => l_allocation_in_rec.oke_attribute12 ,
2593 X_Attribute13 => l_allocation_in_rec.oke_attribute13 ,
2594 X_Attribute14 => l_allocation_in_rec.oke_attribute14 ,
2595 X_Attribute15 => l_allocation_in_rec.oke_attribute15 ,
2596 X_Revenue_Hard_Limit => l_allocation_in_rec.revenue_hard_limit ,
2597 X_Funding_Category => upper(l_allocation_in_rec.funding_category) ,
2598 --X_PA_Attribute_Category => upper(l_allocation_in_rec.pa_attribute_category) ,
2599 X_PA_Attribute_Category => l_allocation_in_rec.pa_attribute_category ,
2600 X_PA_Attribute1 => l_allocation_in_rec.pa_attribute1 ,
2601 X_PA_Attribute2 => l_allocation_in_rec.pa_attribute2 ,
2602 X_PA_Attribute3 => l_allocation_in_rec.pa_attribute3 ,
2603 X_PA_Attribute4 => l_allocation_in_rec.pa_attribute4 ,
2604 X_PA_Attribute5 => l_allocation_in_rec.pa_attribute5 ,
2605 X_PA_Attribute6 => l_allocation_in_rec.pa_attribute6 ,
2606 X_PA_Attribute7 => l_allocation_in_rec.pa_attribute7 ,
2607 X_PA_Attribute8 => l_allocation_in_rec.pa_attribute8 ,
2608 X_PA_Attribute9 => l_allocation_in_rec.pa_attribute9 ,
2609 X_PA_Attribute10 => l_allocation_in_rec.pa_attribute10
2610 );
2611
2612 --dbms_output.put_line('finished oke_allocation_pvt.add_allocation w/ ' || p_return_status);
2613 --oke_debug.debug('finished oke_allocation_pvt.add_allocation w/ ' || p_return_status);
2614
2615 IF FND_API.to_boolean(p_commit) THEN
2616
2617 COMMIT WORK;
2618
2619 END IF;
2620
2621 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
2622 x_msg_data => p_msg_data
2623 );
2624
2625 EXCEPTION
2626 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
2627 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
2628 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2629 p_pkg_name => G_PKG_NAME ,
2630 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
2631 x_msg_count => p_msg_count ,
2632 x_msg_data => p_msg_data ,
2633 p_api_type => '_PVT'
2634 );
2635
2636 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2637 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2638 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2639 p_pkg_name => G_PKG_NAME ,
2640 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
2641 x_msg_count => p_msg_count ,
2642 x_msg_data => p_msg_data ,
2643 p_api_type => '_PVT'
2644 );
2645
2646 WHEN OTHERS THEN
2647 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2648 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2649 p_pkg_name => G_PKG_NAME ,
2650 p_exc_name => 'OTHERS' ,
2651 x_msg_count => p_msg_count ,
2652 x_msg_data => p_msg_data ,
2653 p_api_type => '_PVT'
2654 );
2655
2656 END add_allocation;
2657
2658
2659 --
2660 -- Procedure update_allocation
2661 --
2662 -- Description: This procedure is used to update record in OKE_K_FUND_ALLOCATIONS table
2663 --
2664 -- Calling subprograms: OKE_API.start_activity
2665 -- OKE_API.end_activity
2666 -- allowable_changes
2670 -- validate_attributes
2667 -- OKE_FUNDINGALLOCATION_PVT.update_allocation
2668 -- validate_fund_allocation_id
2669 -- validate_populate_rec
2671 -- validate_record
2672 --
2673
2674 PROCEDURE update_allocation(p_api_version IN NUMBER ,
2675 p_init_msg_list IN VARCHAR2 :=OKE_API.G_FALSE ,
2676 p_commit IN VARCHAR2 :=OKE_API.G_FALSE ,
2677 p_msg_count OUT NOCOPY NUMBER ,
2678 p_msg_data OUT NOCOPY VARCHAR2 ,
2679 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
2680 p_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE ,
2681 p_validation_flag IN VARCHAR2 := OKE_API.G_TRUE ,
2682 p_return_status OUT NOCOPY VARCHAR2
2683 ) is
2684
2685 l_api_name CONSTANT VARCHAR2(30) := 'update_allocation';
2686 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
2687 l_return_status VARCHAR2(1);
2688 l_rowid VARCHAR2(30);
2689 -- l_rate NUMBER;
2690 -- l_rate2 NUMBER;
2691 l_flag VARCHAR2(1);
2692 l_version NUMBER;
2693 l_previous_amount NUMBER;
2694
2695 BEGIN
2696
2697 --dbms_output.put_line('entering oke_allocation_pvt.update_allocation');
2698 --oke_debug.debug('entering oke_allocation_pvt.update_allocation');
2699
2700 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2701 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
2702
2703 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2704 p_pkg_name => G_PKG_NAME ,
2705 p_init_msg_list => p_init_msg_list ,
2706 l_api_version => G_API_VERSION_NUMBER ,
2707 p_api_version => p_api_version ,
2708 p_api_type => '_PVT' ,
2709 x_return_status => p_return_status
2710 );
2711
2712 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2713
2714 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2715
2716 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2717
2718 RAISE OKE_API.G_EXCEPTION_ERROR;
2719
2720 END IF;
2721
2722 --
2723 -- Validate if fund_allocation_id is valid or not
2724 --
2725
2726 --dbms_output.put_line('validate fund_allocation_id');
2727 --oke_debug.debug('validate fund_allocation_id');
2728
2729 validate_fund_allocation_id(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2730 p_rowid => l_rowid ,
2731 p_version => l_version
2732 );
2733
2734 --
2735 -- Validate and set the missing value for the fields
2736 --
2737
2738 --dbms_output.put_line('validate and populate the record');
2739 --oke_debug.debug('validate and populate the record');
2740
2741 validate_populate_rec(p_allocation_in_rec => p_allocation_in_rec ,
2742 p_allocation_in_rec_out => l_allocation_in_rec ,
2743 p_previous_amount => l_previous_amount ,
2744 --p_conversion_rate => l_rate ,
2745 p_flag => l_flag
2746 );
2747
2748 --
2749 -- Validate Attributes
2750 --
2751
2752 --dbms_output.put_line('validate allocation attributes');
2753 --oke_debug.debug('validate allocation attributes');
2754
2755 validate_attributes(p_allocation_in_rec => l_allocation_in_rec );
2756
2757 --
2758 -- Validate record
2759 --
2760
2761 --dbms_output.put_line('validate allocation record');
2762 --oke_debug.debug('validate allocation record');
2763
2764 validate_record(p_allocation_in_rec => l_allocation_in_rec ,
2765 p_validation_flag => p_validation_flag ,
2766 p_flag => l_flag
2767 -- p_conversion_rate => l_rate2
2768 );
2769 /*
2770 IF (l_flag = 'Y') THEN
2771
2772 l_rate := l_rate2;
2773
2774 END IF;
2775 */
2776 --
2777 -- Validate if record exists in PA and check changes are allowable or not
2778 --
2779 /*
2780 IF (l_version <> 0 ) THEN
2781
2782 --dbms_output.put_line('calling allowable changes');
2783 --oke_debug.debug('calling allowable changes');
2784
2785 allowable_changes(p_fund_allocation_id => l_allocation_in_rec.fund_allocation_id ,
2786 p_project_id => l_allocation_in_rec.project_id ,
2787 p_task_id => l_allocation_in_rec.task_id ,
2788 p_start_date_active => l_allocation_in_rec.start_date_active
2789 );
2790
2791 END IF;
2792 */
2793 --
2794 -- Call OKE_FUNDINGALLOCATION_PVT.update_row
2795 --
2796
2797 --dbms_output.put_line('calling oke_fundingallocation_pvt.update_row');
2798 --oke_debug.debug('calling oke_fundingallocation_pvt.update_row');
2799
2800 OKE_FUNDINGALLOCATION_PVT.update_row(X_Fund_Allocation_Id => l_allocation_in_rec.fund_allocation_id ,
2801 X_Amount => l_allocation_in_rec.amount ,
2802 X_Previous_Amount => l_previous_amount ,
2803 X_Object_id => l_allocation_in_rec.object_id ,
2804 X_k_line_id => l_allocation_in_rec.k_line_id ,
2805 X_project_id => l_allocation_in_rec.project_id ,
2806 x_task_id => l_allocation_in_rec.task_id ,
2807 X_Hard_Limit => l_allocation_in_rec.hard_limit ,
2808 X_Fund_Type => upper(l_allocation_in_rec.fund_type) ,
2809 X_Funding_Status => upper(l_allocation_in_rec.funding_status) ,
2810 X_Fiscal_Year => l_allocation_in_rec.fiscal_year ,
2811 X_Reference1 => l_allocation_in_rec.reference1 ,
2812 X_Reference2 => l_allocation_in_rec.reference2 ,
2813 X_Reference3 => l_allocation_in_rec.reference3 ,
2817 X_Insert_Update_Flag => 'Y' ,
2814 X_Pa_Conversion_Type => l_allocation_in_rec.pa_conversion_type ,
2815 X_Pa_Conversion_Date => l_allocation_in_rec.pa_conversion_date ,
2816 X_Pa_Conversion_Rate => l_allocation_in_rec.pa_conversion_rate ,
2818 X_Start_Date_Active => l_allocation_in_rec.start_date_active ,
2819 X_End_Date_Active => l_allocation_in_rec.end_date_active ,
2820 X_Last_Update_Date => sysdate ,
2821 X_Last_Updated_By => L_USERID ,
2822 X_Last_Update_Login => L_LOGINID ,
2823 --X_Attribute_Category => upper(l_allocation_in_rec.oke_attribute_category) ,
2824 X_Attribute_Category => l_allocation_in_rec.oke_attribute_category ,
2825 X_Attribute1 => l_allocation_in_rec.oke_attribute1 ,
2826 X_Attribute2 => l_allocation_in_rec.oke_attribute2 ,
2827 X_Attribute3 => l_allocation_in_rec.oke_attribute3 ,
2828 X_Attribute4 => l_allocation_in_rec.oke_attribute4 ,
2829 X_Attribute5 => l_allocation_in_rec.oke_attribute5 ,
2830 X_Attribute6 => l_allocation_in_rec.oke_attribute6 ,
2831 X_Attribute7 => l_allocation_in_rec.oke_attribute7 ,
2832 X_Attribute8 => l_allocation_in_rec.oke_attribute8 ,
2833 X_Attribute9 => l_allocation_in_rec.oke_attribute9 ,
2834 X_Attribute10 => l_allocation_in_rec.oke_attribute10 ,
2835 X_Attribute11 => l_allocation_in_rec.oke_attribute11 ,
2836 X_Attribute12 => l_allocation_in_rec.oke_attribute12 ,
2837 X_Attribute13 => l_allocation_in_rec.oke_attribute13 ,
2838 X_Attribute14 => l_allocation_in_rec.oke_attribute14 ,
2839 X_Attribute15 => l_allocation_in_rec.oke_attribute15 ,
2840 X_Revenue_Hard_Limit => l_allocation_in_rec.revenue_hard_limit ,
2841 X_Funding_Category => upper(l_allocation_in_rec.funding_category) ,
2842 --X_PA_Attribute_Category => upper(l_allocation_in_rec.pa_attribute_category) ,
2843 X_PA_Attribute_Category => l_allocation_in_rec.pa_attribute_category ,
2844 X_PA_Attribute1 => l_allocation_in_rec.pa_attribute1 ,
2845 X_PA_Attribute2 => l_allocation_in_rec.pa_attribute2 ,
2846 X_PA_Attribute3 => l_allocation_in_rec.pa_attribute3 ,
2847 X_PA_Attribute4 => l_allocation_in_rec.pa_attribute4 ,
2848 X_PA_Attribute5 => l_allocation_in_rec.pa_attribute5 ,
2849 X_PA_Attribute6 => l_allocation_in_rec.pa_attribute6 ,
2850 X_PA_Attribute7 => l_allocation_in_rec.pa_attribute7 ,
2851 X_PA_Attribute8 => l_allocation_in_rec.pa_attribute8 ,
2852 X_PA_Attribute9 => l_allocation_in_rec.pa_attribute9 ,
2853 X_PA_Attribute10 => l_allocation_in_rec.pa_attribute10
2854 );
2855
2856 IF FND_API.to_boolean(p_commit) THEN
2857
2858 COMMIT WORK;
2859
2860 END IF;
2861
2862 --dbms_output.put_line('finished oke_allocation_pvt.update_allocation w/ ' || p_return_status);
2863 --oke_debug.debug('finished oke_allocation_pvt.update_allocation w/ ' || p_return_status);
2864
2865 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
2866 x_msg_data => p_msg_data
2867 );
2868
2869 EXCEPTION
2870 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
2871 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
2872 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2873 p_pkg_name => G_PKG_NAME ,
2874 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
2875 x_msg_count => p_msg_count ,
2876 x_msg_data => p_msg_data ,
2877 p_api_type => '_PVT'
2878 );
2879
2880 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2881 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2882 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2883 p_pkg_name => G_PKG_NAME ,
2884 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
2885 x_msg_count => p_msg_count ,
2886 x_msg_data => p_msg_data ,
2887 p_api_type => '_PVT'
2888 );
2889
2890 WHEN OTHERS THEN
2891 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2892 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2893 p_pkg_name => G_PKG_NAME ,
2894 p_exc_name => 'OTHERS' ,
2895 x_msg_count => p_msg_count ,
2896 x_msg_data => p_msg_data ,
2897 p_api_type => '_PVT'
2898 );
2899
2900 END update_allocation;
2901
2902
2903 --
2904 -- Procedure delete_allocation
2905 --
2909 -- OKE_API.start_activity
2906 -- Description: This procedure is used to delete record in OKE_K_FUND_ALLOCATIONS table
2907 --
2908 -- Calling subprograms: OKE_FUNDINGALLOCATION_PVT.delete_row
2910 -- OKE_API.end_activity
2911 -- validate_fund_allocation_id
2912 -- PA_AGREEMENT_PUB.delete_funding
2913 --
2914
2915 PROCEDURE delete_allocation(p_api_version IN NUMBER ,
2916 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
2917 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
2918 p_msg_count OUT NOCOPY NUMBER ,
2919 p_msg_data OUT NOCOPY VARCHAR2 ,
2920 p_fund_allocation_id IN NUMBER ,
2921 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
2922 p_return_status OUT NOCOPY VARCHAR2
2923 ) is
2924
2925 l_api_name CONSTANT VARCHAR2(30) := 'delete_allocation';
2926 l_return_status VARCHAR2(1);
2927 -- l_agreement_flag VARCHAR2(1);
2928 l_rowid VARCHAR2(30);
2929 l_version NUMBER;
2930 -- l_funding_reference VARCHAR2(25);
2931 -- i NUMBER := 1;
2932 -- l_org_id NUMBER;
2933 l_created_ver NUMBER;
2934 l_current_ver NUMBER;
2935 l_org_id_vc VARCHAR2(10);
2936
2937 /*
2938 cursor c_org is
2939 select org_id
2940 from pa_projects_all p,
2941 oke_k_fund_allocations f
2942 where f.project_id = p.project_id
2943 and fund_allocation_id = p_fund_allocation_id;
2944 */
2945
2946 cursor c_ver is
2947 select major_version + 1,
2948 nvl(created_in_version, -99)
2949 from okc_k_vers_numbers b,
2950 oke_k_fund_allocations a
2951 where b.chr_id = a.object_id
2952 and a.fund_allocation_id = p_fund_allocation_id;
2953
2954 cursor c_proj_funding(x_length number) is
2955 select project_funding_id, org_id, pm_funding_reference
2956 from pa_project_fundings p,
2957 pa_agreements_all a
2958 where p.pm_product_code = G_PRODUCT_CODE
2959 and a.agreement_id = p.agreement_id
2960 and substr(pm_funding_reference, 1, x_length + 1) = p_fund_allocation_id || '.';
2961
2962 BEGIN
2963
2964 --dbms_output.put_line('entering oke_allocation_pvt.delete_allocation');
2965 --oke_debug.debug('entering oke_allocation_pvt.delete_allocation');
2966
2967 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2968
2969 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2970 p_pkg_name => G_PKG_NAME ,
2971 p_init_msg_list => p_init_msg_list ,
2972 l_api_version => G_API_VERSION_NUMBER ,
2973 p_api_version => p_api_version ,
2974 p_api_type => '_PVT' ,
2975 x_return_status => p_return_status
2976 );
2977
2978 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2979
2980 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2981
2982 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2983
2984 RAISE OKE_API.G_EXCEPTION_ERROR;
2985
2986 END IF;
2987
2988 --
2989 -- Validate if it is a valid fund_allocation_id
2990 --
2991
2992 validate_fund_allocation_id(p_fund_allocation_id => p_fund_allocation_id ,
2993 p_rowid => l_rowid ,
2994 p_version => l_version
2995 );
2996
2997 --
2998 -- 7/15/02
2999 -- Validate if the line can be deleted or not
3000 --
3001 OPEN c_ver;
3002 FETCH c_ver into l_current_ver, l_created_ver;
3003 CLOSE c_ver;
3004
3005 IF (l_current_ver <> l_created_ver) THEN
3006
3007 OKE_API.set_message(p_app_name => G_APP_NAME ,
3008 p_msg_name => 'OKE_VER_NO_ALLOCATION_DELETE'
3009 );
3010
3011 RAISE OKE_API.G_EXCEPTION_ERROR;
3012
3013 END IF;
3014 --
3015 -- End 7/15/02
3016 --
3017
3018 --
3019 -- Call OKE_FUNDINGALLOCATION_PVT.delete_row to delete the row
3020 --
3021
3022 OKE_FUNDINGALLOCATION_PVT.delete_row(x_rowid => l_rowid);
3023
3024 --
3025 -- Delete project_funding lines if they exist in PA;
3026 --
3027 /*
3028 IF l_version <> 0 THEN
3029
3030 OPEN c_org;
3031 FETCH c_org into l_org_id;
3032 CLOSE c_org;
3033
3034 END IF;
3035
3036 FOR i in 1..l_version LOOP
3037
3038 l_funding_reference := p_fund_allocation_id || '.' || i;
3039 */
3040 l_org_id_vc := oke_utils.org_id;
3041
3042 FOR l_project_funding IN c_proj_funding(length(p_fund_allocation_id)) LOOP
3043
3044 -- fnd_client_info.set_org_context(l_project_funding.org_id);
3045 mo_global.set_policy_context('S',l_project_funding.org_id);
3046
3047 PA_AGREEMENT_PUB.delete_funding(p_api_version_number => p_api_version ,
3048 p_commit => OKE_API.G_FALSE ,
3049 p_init_msg_list => OKE_API.G_FALSE ,
3050 p_msg_count => p_msg_count ,
3051 p_msg_data => p_msg_data ,
3052 p_return_status => p_return_status ,
3053 p_pm_product_code => G_PRODUCT_CODE ,
3054 p_pm_funding_reference => l_project_funding.pm_funding_reference ,
3055 p_funding_id => l_project_funding.project_funding_id ,
3056 p_check_y_n => 'Y'
3057 );
3058
3059 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3060
3061 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3062
3063 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
3064
3065 RAISE OKE_API.G_EXCEPTION_ERROR;
3066
3067 END IF;
3068
3069 END LOOP;
3070 mo_global.set_policy_context('S',to_number(l_org_id_vc));
3071
3072 --dbms_output.put_line('finished oke_allocation_pvt.delete_allocation w/ ' || p_return_status);
3073 --oke_debug.debug('finished oke_allocation_pvt.delete_allocation w/ ' || p_return_status);
3074
3075 IF FND_API.to_boolean(p_commit) THEN
3076
3077 COMMIT WORK;
3078
3079 END IF;
3080
3081 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
3082 x_msg_data => p_msg_data
3083 );
3084
3085 EXCEPTION
3086 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
3087 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3088 p_pkg_name => G_PKG_NAME ,
3089 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
3090 x_msg_count => p_msg_count ,
3091 x_msg_data => p_msg_data ,
3092 p_api_type => '_PVT'
3093 );
3094
3095 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3096 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3097 p_pkg_name => G_PKG_NAME ,
3098 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
3099 x_msg_count => p_msg_count ,
3100 x_msg_data => p_msg_data ,
3101 p_api_type => '_PVT'
3102 );
3103
3104 WHEN OTHERS THEN
3105 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3106 p_pkg_name => G_PKG_NAME ,
3107 p_exc_name => 'OTHERS' ,
3108 x_msg_count => p_msg_count ,
3109 x_msg_data => p_msg_data ,
3110 p_api_type => '_PVT'
3111 );
3112
3113 END delete_allocation;
3114
3115
3116
3117 --
3118 -- Function: get_allocation_tbl
3119 --
3120 -- Description: This function is used to return a initialized ALLOCATION_IN_TBL_TYPE
3121 --
3122 -- Calling subprograms: N/A
3123 --
3124
3125 FUNCTION get_allocation_tbl RETURN ALLOCATION_IN_TBL_TYPE is
3126
3127 allocation_in_tbl ALLOCATION_IN_TBL_TYPE;
3128
3129 BEGIN
3130
3131 return allocation_in_tbl;
3132
3133 END get_allocation_tbl;
3134
3135
3136 end OKE_ALLOCATION_PVT;