[Home] [Help]
PACKAGE BODY: APPS.OKE_ALLOCATION_PVT
Source
1 package body OKE_ALLOCATION_PVT as
2 /* $Header: OKEVFDAB.pls 120.3 2005/11/23 14:37:43 ausmani noship $ */
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;
700
701 IF (nvl(l_flag, 'N') = 'Y') THEN
702
703 OPEN c_line_project;
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 cursor c_project_h (x_project_id number) is
816 select 'x'
817 from dual
818 where p_project_id in
819 (select to_number(sub_project_id)
820 from pa_fin_structures_links_v
821 start with parent_project_id = x_project_id
822 connect by parent_project_id = prior sub_project_id
823 union all
824 select x_project_id
825 from dual
826 );
827
828 cursor c_project_h2 (x_task_id number, x_project_id number) is
829 select 'x'
830 from dual
831 where p_project_id in
832 ( select to_number(sub_project_id)
833 from pa_fin_structures_links_v
834 start with parent_project_id = x_project_id
835 and parent_task_id = x_task_id
836 connect by parent_project_id = prior sub_project_id
837 union all
838 select x_project_id
839 from dual
840 );
841
842 cursor c_intent is
843 select buy_or_sell
844 from oke_k_headers_v
845 where k_header_id = p_object_id;
846
847 cursor c_project_2 (x_line_id number) is
848 select project_id,
849 parent_line_id,
850 task_id
851 from oke_k_lines
852 where k_line_id = x_line_id;
853
854 l_dummy_value VARCHAR2(1) := '?';
855 l_project_id NUMBER;
856 l_intent VARCHAR2(1);
857 l_task_id NUMBER;
858 l_line_id NUMBER;
859 l_exist VARCHAR2(1);
860
861 BEGIN
862
863 p_return_status := OKE_API.G_RET_STS_SUCCESS;
864
865 IF (p_project_id is not null) OR
866 (p_project_id <> OKE_API.G_MISS_NUM) THEN
867
868 OPEN c_intent;
869 FETCH c_intent into l_intent;
870 CLOSE c_intent;
871
872 IF (l_intent = 'S') THEN
873
874 OPEN c_project_id;
875 FETCH c_project_id into l_dummy_value;
876 CLOSE c_project_id;
877
878 IF (l_dummy_value = '?') THEN
879
880 OKE_API.set_message(p_app_name => G_APP_NAME ,
881 p_msg_name => 'OKE_API_INVALID_VALUE' ,
882 p_token1 => 'VALUE' ,
883 p_token1_value => 'project_id'
884 );
885
886 p_return_status := OKE_API.G_RET_STS_ERROR;
887 return;
888
889 END IF;
890
891 END IF;
892
893 l_dummy_value := '?';
894
895 IF (p_k_line_id is not null) then
896 OPEN c_line_project;
897 FETCH c_line_project into l_project_id, l_task_id;
898 CLOSE c_line_project;
899
900 IF (l_project_id is null) THEN
901
902 l_line_id := p_k_line_id;
903 l_exist := 'N';
904
905 while (l_exist = 'N') loop
906
907 open c_project_2 (l_line_id);
908 l_line_id := null;
909 fetch c_project_2 into l_project_id, l_line_id, l_task_id;
910 close c_project_2;
911
912 if (l_line_id is null) or
913 (l_project_id is not null) then
914 l_exist := 'Y';
915 end if;
916
917 end loop;
918
919 END IF;
920
921 END IF;
922
923 IF (l_project_id is null) then
924 OPEN c_master_project;
925 FETCH c_master_project into l_project_id;
926 CLOSE c_master_project;
927 END IF;
928
929 IF (l_project_id is null) then
930
931 OKE_API.set_message(p_app_name => G_APP_NAME ,
932 p_msg_name => 'OKE_API_INVALID_VALUE' ,
933 p_token1 => 'VALUE' ,
934 p_token1_value => 'project_id'
935 );
936
937 p_return_status := OKE_API.G_RET_STS_ERROR;
938
939 ELSE
940
941 IF (l_task_id is not null) THEN
942
943 OPEN c_project_h2(l_task_id, l_project_id);
944 FETCH c_project_h2 into l_dummy_value;
945 CLOSE c_project_h2;
946
947 ELSE
948
949 OPEN c_project_h(l_project_id);
950 FETCH c_project_h into l_dummy_value;
951 CLOSE c_project_h;
952
953 END IF;
954
955 IF (l_dummy_value = '?') then
956
957 OKE_API.set_message(p_app_name => G_APP_NAME ,
958 p_msg_name => 'OKE_API_INVALID_VALUE' ,
959 p_token1 => 'VALUE' ,
960 p_token1_value => 'project_id'
961 );
962
963 p_return_status := OKE_API.G_RET_STS_ERROR;
964
965 END IF;
966
967 END IF;
968
969 END IF;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
974 OKE_API.set_message(p_app_name => G_APP_NAME ,
975 p_msg_name => G_UNEXPECTED_ERROR ,
976 p_token1 => G_SQLCODE_TOKEN ,
977 p_token1_value => SQLCODE ,
978 p_token2 => G_SQLERRM_TOKEN ,
979 p_token2_value => SQLERRM
980 );
981
982 IF c_project_id%ISOPEN THEN
983 CLOSE c_project_id;
984 END IF;
985
986 END validate_project_id;
987
988
989 --
990 -- Procedure: validate_task_id
991 --
992 -- Description: This procedure is used to validate task_id
993 --
994 --
995
996 PROCEDURE validate_task_id(p_task_id NUMBER ,
997 p_return_status OUT NOCOPY VARCHAR2
998 ) is
999 cursor c_task_id is
1000 select 'x'
1001 from pa_tasks
1002 where task_id = p_task_id
1003 and task_id = top_task_id;
1004
1005 l_dummy_value VARCHAR2(1) := '?';
1006
1007 BEGIN
1008
1009 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1010
1011 IF (p_task_id is not null) OR
1012 (p_task_id <> OKE_API.G_MISS_NUM) THEN
1013
1014 OPEN c_task_id;
1015 FETCH c_task_id into l_dummy_value;
1016 CLOSE c_task_id;
1017
1018 IF (l_dummy_value = '?') THEN
1019
1020 OKE_API.set_message(p_app_name => G_APP_NAME ,
1021 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1022 p_token1 => 'VALUE' ,
1023 p_token1_value => 'task_id'
1024 );
1025
1026 p_return_status := OKE_API.G_RET_STS_ERROR;
1027
1028 END IF;
1029
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1035 OKE_API.set_message(p_app_name => G_APP_NAME ,
1036 p_msg_name => G_UNEXPECTED_ERROR ,
1037 p_token1 => G_SQLCODE_TOKEN ,
1038 p_token1_value => SQLCODE ,
1039 p_token2 => G_SQLERRM_TOKEN ,
1040 p_token2_value => SQLERRM
1041 );
1042
1043 IF c_task_id%ISOPEN THEN
1044 CLOSE c_task_id;
1045 END IF;
1046
1047 END validate_task_id;
1048
1049
1050 --
1051 -- Procedure: validate_fund_type
1052 --
1053 -- Description: This procedure is used to validate fund_type
1054 --
1055 --
1056
1057 PROCEDURE validate_fund_type(p_fund_type VARCHAR2 ,
1058 p_return_status OUT NOCOPY VARCHAR2
1059 ) is
1060 cursor c_fund_type is
1061 select 'x'
1062 from fnd_lookup_values
1063 where lookup_type = 'FUND_TYPE'
1064 and language = userenv('LANG')
1065 and enabled_flag = 'Y'
1066 and lookup_code = upper(p_fund_type);
1067
1068 l_dummy_value VARCHAR2(1) := '?';
1069
1070 BEGIN
1071
1072 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1073
1074 IF (p_fund_type is not null) OR
1075 (p_fund_type <> OKE_API.G_MISS_CHAR) THEN
1076
1077 OPEN c_fund_type;
1078 FETCH c_fund_type into l_dummy_value;
1079 CLOSE c_fund_type;
1080
1081 IF (l_dummy_value = '?') THEN
1082
1083 OKE_API.set_message(p_app_name => G_APP_NAME ,
1084 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1085 p_token1 => 'VALUE' ,
1086 p_token1_value => 'fund_type'
1087 );
1088
1089 p_return_status := OKE_API.G_RET_STS_ERROR;
1090
1091 END IF;
1092
1093 END IF;
1094
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1098 OKE_API.set_message(p_app_name => G_APP_NAME ,
1099 p_msg_name => G_UNEXPECTED_ERROR ,
1100 p_token1 => G_SQLCODE_TOKEN ,
1101 p_token1_value => SQLCODE ,
1102 p_token2 => G_SQLERRM_TOKEN ,
1103 p_token2_value => SQLERRM
1104 );
1105
1106 IF c_fund_type%ISOPEN THEN
1107 CLOSE c_fund_type;
1108 END IF;
1109
1110 END validate_fund_type;
1111
1112
1113 --
1114 -- Procedure: validate_funding_status
1115 --
1116 -- Description: This procedure is used to validate funding_status
1117 --
1118 --
1119
1120 PROCEDURE validate_funding_status(p_funding_status VARCHAR2 ,
1121 p_return_status OUT NOCOPY VARCHAR2
1122 ) is
1123 cursor c_funding_status is
1124 select 'x'
1125 from fnd_lookup_values
1126 where lookup_type = 'FUNDING_STATUS'
1127 and enabled_flag = 'Y'
1128 and language = userenv('LANG')
1129 and lookup_code = upper(p_funding_status);
1130
1131 l_dummy_value VARCHAR2(1) := '?';
1132
1133 BEGIN
1134
1135 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1136
1137 IF (p_funding_status is not null) OR
1138 (p_funding_status <> OKE_API.G_MISS_CHAR) THEN
1139
1140 OPEN c_funding_status;
1141 FETCH c_funding_status into l_dummy_value;
1142 CLOSE c_funding_status;
1143
1144 IF (l_dummy_value = '?') THEN
1145
1146 OKE_API.set_message(p_app_name => G_APP_NAME ,
1147 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1148 p_token1 => 'VALUE' ,
1149 p_token1_value => 'funding_status'
1150 );
1151
1152 p_return_status := OKE_API.G_RET_STS_ERROR;
1153
1154 END IF;
1155
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1161 OKE_API.set_message(p_app_name => G_APP_NAME ,
1162 p_msg_name => G_UNEXPECTED_ERROR ,
1163 p_token1 => G_SQLCODE_TOKEN ,
1164 p_token1_value => SQLCODE ,
1165 p_token2 => G_SQLERRM_TOKEN ,
1166 p_token2_value => SQLERRM
1167 );
1168
1169 IF c_funding_status%ISOPEN THEN
1170 CLOSE c_funding_status;
1171 END IF;
1172
1173 END validate_funding_status;
1174
1175
1176 --
1177 -- Procedure: validate_funding_category
1178 --
1179 -- Description: This procedure is used to validate funding_category
1180 --
1181 --
1182
1183 PROCEDURE validate_funding_category(p_funding_category VARCHAR2 ,
1184 p_return_status OUT NOCOPY VARCHAR2
1185 ) is
1186 cursor c_funding_category is
1187 select 'x'
1188 from pa_lookups
1189 where lookup_type = 'FUNDING CATEGORY TYPE'
1190 and lookup_code = upper(p_funding_category);
1191
1192 l_dummy_value VARCHAR2(1) := '?';
1193
1194 BEGIN
1195
1196 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1197
1198 IF (p_funding_category is null) OR
1199 (p_funding_category = OKE_API.G_MISS_CHAR) THEN
1200
1201 OKE_API.set_message(p_app_name => G_APP_NAME ,
1202 p_msg_name => 'OKE_API_MISSING_VALUE' ,
1203 p_token1 => 'VALUE' ,
1204 p_token1_value => 'funding_category'
1205 );
1206
1207 p_return_status := OKE_API.G_RET_STS_ERROR;
1208
1209 ELSE
1210
1211 OPEN c_funding_category;
1212 FETCH c_funding_category into l_dummy_value;
1213 CLOSE c_funding_category;
1214
1215 IF (l_dummy_value = '?') THEN
1216
1217 OKE_API.set_message(p_app_name => G_APP_NAME ,
1218 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1219 p_token1 => 'VALUE' ,
1220 p_token1_value => 'funding_category'
1221 );
1222
1223 p_return_status := OKE_API.G_RET_STS_ERROR;
1224
1225 END IF;
1226
1227 END IF;
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1232 OKE_API.set_message(p_app_name => G_APP_NAME ,
1233 p_msg_name => G_UNEXPECTED_ERROR ,
1234 p_token1 => G_SQLCODE_TOKEN ,
1235 p_token1_value => SQLCODE ,
1236 p_token2 => G_SQLERRM_TOKEN ,
1237 p_token2_value => SQLERRM
1238 );
1239
1240 IF c_funding_category%ISOPEN THEN
1241 CLOSE c_funding_category;
1242 END IF;
1243
1244 END validate_funding_category;
1245
1246
1247 --
1248 -- Procedure: validate_conversion_type
1249 --
1250 -- Description: This procedure is used to validate conversion_type
1251 --
1252 --
1253
1254 PROCEDURE validate_conversion_type(p_conversion_type VARCHAR2 ,
1255 p_return_status OUT NOCOPY VARCHAR2
1256 ) is
1257 cursor c_conversion_type is
1258 select 'x'
1259 from gl_daily_conversion_types
1260 where UPPER(conversion_type) = UPPER(p_conversion_type);
1261
1262 l_dummy_value VARCHAR2(1) := '?';
1263
1264 BEGIN
1265
1266 p_return_status := OKE_API.G_RET_STS_SUCCESS;
1267
1268 IF (p_conversion_type is not null) OR
1269 (p_conversion_type <> OKE_API.G_MISS_CHAR) THEN
1270
1271 OPEN c_conversion_type;
1272 FETCH c_conversion_type into l_dummy_value;
1273 CLOSE c_conversion_type;
1274
1275 IF (l_dummy_value = '?') THEN
1276
1277 OKE_API.set_message(p_app_name => G_APP_NAME ,
1278 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1279 p_token1 => 'VALUE' ,
1280 p_token1_value => 'pa_conversion_type'
1281 );
1282
1283 p_return_status := OKE_API.G_RET_STS_ERROR;
1284
1285 END IF;
1286
1287 END IF;
1288
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 p_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1292 OKE_API.set_message(p_app_name => G_APP_NAME ,
1293 p_msg_name => G_UNEXPECTED_ERROR ,
1294 p_token1 => G_SQLCODE_TOKEN ,
1295 p_token1_value => SQLCODE ,
1296 p_token2 => G_SQLERRM_TOKEN ,
1297 p_token2_value => SQLERRM
1298 );
1299
1300 IF c_conversion_type%ISOPEN THEN
1301 CLOSE c_conversion_type;
1302 END IF;
1303
1304 END validate_conversion_type;
1305
1306
1307 --
1308 -- Function: null_allocation_out
1309 --
1310 -- Description: This function is used to set all the missing attribute values to be null
1311 --
1312 --
1313
1314 FUNCTION null_allocation_out(p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE)
1315 RETURN ALLOCATION_REC_IN_TYPE
1316 is
1317 l_allocation_in_rec ALLOCATION_REC_IN_TYPE := p_allocation_in_rec;
1318 BEGIN
1319
1320 l_allocation_in_rec.fund_allocation_id := null;
1321
1322 IF l_allocation_in_rec.agreement_id = OKE_API.G_MISS_NUM THEN
1323 l_allocation_in_rec.agreement_id := null;
1324 END IF;
1325
1326 IF l_allocation_in_rec.amount = OKE_API.G_MISS_NUM THEN
1327 l_allocation_in_rec.amount := null;
1328 END IF;
1329
1330 IF l_allocation_in_rec.funding_source_id = OKE_API.G_MISS_NUM THEN
1331 l_allocation_in_rec.funding_source_id := null;
1332 END IF;
1333
1334 IF l_allocation_in_rec.object_id = OKE_API.G_MISS_NUM THEN
1335 l_allocation_in_rec.object_id := null;
1336 END IF;
1337
1338 IF l_allocation_in_rec.k_line_id = OKE_API.G_MISS_NUM THEN
1339 l_allocation_in_rec.k_line_id := null;
1340 END IF;
1341
1342 IF l_allocation_in_rec.project_id = OKE_API.G_MISS_NUM THEN
1343 l_allocation_in_rec.project_id := null;
1344 END IF;
1345
1346 IF l_allocation_in_rec.task_id = OKE_API.G_MISS_NUM THEN
1347 l_allocation_in_rec.task_id := null;
1348 END IF;
1349
1350 IF l_allocation_in_rec.fund_type = OKE_API.G_MISS_CHAR THEN
1351 l_allocation_in_rec.fund_type := null;
1352 END IF;
1353
1354 IF l_allocation_in_rec.hard_limit = OKE_API.G_MISS_NUM THEN
1355 l_allocation_in_rec.hard_limit := null;
1356 END IF;
1357
1358 IF l_allocation_in_rec.funding_status = OKE_API.G_MISS_CHAR THEN
1359 l_allocation_in_rec.funding_status := null;
1360 END IF;
1361
1362 IF l_allocation_in_rec.fiscal_year = OKE_API.G_MISS_NUM THEN
1363 l_allocation_in_rec.fiscal_year := null;
1364 END IF;
1365
1366 IF l_allocation_in_rec.reference1 = OKE_API.G_MISS_CHAR THEN
1367 l_allocation_in_rec.reference1 := null;
1368 END IF;
1369
1370 IF l_allocation_in_rec.reference2 = OKE_API.G_MISS_CHAR THEN
1371 l_allocation_in_rec.reference2 := null;
1372 END IF;
1373
1374 IF l_allocation_in_rec.reference3 = OKE_API.G_MISS_CHAR THEN
1375 l_allocation_in_rec.reference3 := null;
1376 END IF;
1377
1378 IF l_allocation_in_rec.pa_conversion_type = OKE_API.G_MISS_CHAR THEN
1379 l_allocation_in_rec.pa_conversion_type := null;
1380 END IF;
1381
1382 IF l_allocation_in_rec.pa_conversion_date = OKE_API.G_MISS_DATE THEN
1383 l_allocation_in_rec.pa_conversion_date := null;
1384 END IF;
1385
1386 -- syho, bug 2208979
1387 IF l_allocation_in_rec.pa_conversion_rate = OKE_API.G_MISS_NUM THEN
1388 l_allocation_in_rec.pa_conversion_rate := null;
1389 END IF;
1390 -- syho, bug 2208979
1391
1392 IF l_allocation_in_rec.start_date_active = OKE_API.G_MISS_DATE THEN
1393 l_allocation_in_rec.start_date_active := null;
1394 END IF;
1395
1396 IF l_allocation_in_rec.end_date_active = OKE_API.G_MISS_DATE THEN
1397 l_allocation_in_rec.end_date_active := null;
1398 END IF;
1399 /*
1400 IF l_allocation_in_rec.oke_desc_flex_name = OKE_API.G_MISS_CHAR THEN
1401 l_allocation_in_rec.oke_desc_flex_name := null;
1402 END IF;
1403 */
1404 IF l_allocation_in_rec.oke_attribute_category = OKE_API.G_MISS_CHAR THEN
1405 l_allocation_in_rec.oke_attribute_category := null;
1406 END IF;
1407
1408 IF l_allocation_in_rec.oke_attribute1 = OKE_API.G_MISS_CHAR THEN
1409 l_allocation_in_rec.oke_attribute1 := null;
1410 END IF;
1411
1412 IF l_allocation_in_rec.oke_attribute2 = OKE_API.G_MISS_CHAR THEN
1413 l_allocation_in_rec.oke_attribute2 := null;
1414 END IF;
1415
1416 IF l_allocation_in_rec.oke_attribute3 = OKE_API.G_MISS_CHAR THEN
1417 l_allocation_in_rec.oke_attribute3 := null;
1418 END IF;
1419
1420 IF l_allocation_in_rec.oke_attribute4 = OKE_API.G_MISS_CHAR THEN
1421 l_allocation_in_rec.oke_attribute4 := null;
1422 END IF;
1423
1424 IF l_allocation_in_rec.oke_attribute5 = OKE_API.G_MISS_CHAR THEN
1425 l_allocation_in_rec.oke_attribute5 := null;
1426 END IF;
1427
1428 IF l_allocation_in_rec.oke_attribute6 = OKE_API.G_MISS_CHAR THEN
1429 l_allocation_in_rec.oke_attribute6 := null;
1430 END IF;
1431
1432 IF l_allocation_in_rec.oke_attribute7 = OKE_API.G_MISS_CHAR THEN
1433 l_allocation_in_rec.oke_attribute7 := null;
1434 END IF;
1435
1436 IF l_allocation_in_rec.oke_attribute8 = OKE_API.G_MISS_CHAR THEN
1437 l_allocation_in_rec.oke_attribute8 := null;
1438 END IF;
1439
1440 IF l_allocation_in_rec.oke_attribute9 = OKE_API.G_MISS_CHAR THEN
1441 l_allocation_in_rec.oke_attribute9 := null;
1442 END IF;
1443
1444 IF l_allocation_in_rec.oke_attribute10 = OKE_API.G_MISS_CHAR THEN
1445 l_allocation_in_rec.oke_attribute10 := null;
1446 END IF;
1447
1448 IF l_allocation_in_rec.oke_attribute11 = OKE_API.G_MISS_CHAR THEN
1449 l_allocation_in_rec.oke_attribute11 := null;
1450 END IF;
1451
1452 IF l_allocation_in_rec.oke_attribute12 = OKE_API.G_MISS_CHAR THEN
1453 l_allocation_in_rec.oke_attribute12 := null;
1454 END IF;
1455
1456 IF l_allocation_in_rec.oke_attribute13 = OKE_API.G_MISS_CHAR THEN
1457 l_allocation_in_rec.oke_attribute13 := null;
1458 END IF;
1459
1460 IF l_allocation_in_rec.oke_attribute14 = OKE_API.G_MISS_CHAR THEN
1461 l_allocation_in_rec.oke_attribute14 := null;
1462 END IF;
1463
1464 IF l_allocation_in_rec.oke_attribute15 = OKE_API.G_MISS_CHAR THEN
1465 l_allocation_in_rec.oke_attribute15 := null;
1466 END IF;
1467
1468 IF l_allocation_in_rec.revenue_hard_limit = OKE_API.G_MISS_NUM THEN
1469 l_allocation_in_rec.revenue_hard_limit := null;
1470 END IF;
1471
1472 IF l_allocation_in_rec.pa_attribute_category = OKE_API.G_MISS_CHAR THEN
1473 l_allocation_in_rec.pa_attribute_category := null;
1474 END IF;
1475
1476 IF l_allocation_in_rec.pa_attribute1 = OKE_API.G_MISS_CHAR THEN
1477 l_allocation_in_rec.pa_attribute1 := null;
1478 END IF;
1479
1480 IF l_allocation_in_rec.pa_attribute2 = OKE_API.G_MISS_CHAR THEN
1481 l_allocation_in_rec.pa_attribute2 := null;
1482 END IF;
1483
1484 IF l_allocation_in_rec.pa_attribute3 = OKE_API.G_MISS_CHAR THEN
1485 l_allocation_in_rec.pa_attribute3 := null;
1486 END IF;
1487
1488 IF l_allocation_in_rec.pa_attribute4 = OKE_API.G_MISS_CHAR THEN
1489 l_allocation_in_rec.pa_attribute4 := null;
1490 END IF;
1491
1492 IF l_allocation_in_rec.pa_attribute5 = OKE_API.G_MISS_CHAR THEN
1493 l_allocation_in_rec.pa_attribute5 := null;
1494 END IF;
1495
1496 IF l_allocation_in_rec.pa_attribute6 = OKE_API.G_MISS_CHAR THEN
1497 l_allocation_in_rec.pa_attribute6 := null;
1498 END IF;
1499
1500 IF l_allocation_in_rec.pa_attribute7 = OKE_API.G_MISS_CHAR THEN
1501 l_allocation_in_rec.pa_attribute7 := null;
1502 END IF;
1503
1504 IF l_allocation_in_rec.pa_attribute8 = OKE_API.G_MISS_CHAR THEN
1505 l_allocation_in_rec.pa_attribute8 := null;
1506 END IF;
1507
1508 IF l_allocation_in_rec.pa_attribute9 = OKE_API.G_MISS_CHAR THEN
1509 l_allocation_in_rec.pa_attribute9 := null;
1510 END IF;
1511
1512 IF l_allocation_in_rec.pa_attribute10 = OKE_API.G_MISS_CHAR THEN
1513 l_allocation_in_rec.pa_attribute10 := null;
1514 END IF;
1515
1516 IF l_allocation_in_rec.funding_category = OKE_API.G_MISS_CHAR THEN
1517 l_allocation_in_rec.funding_category := null;
1518 END IF;
1519
1520 return(l_allocation_in_rec);
1521
1522 END null_allocation_out;
1523
1524
1525 --
1526 -- Procedure: validate_populate_rec
1527 --
1528 -- Description: This procedure is used to set all the missing attribute values to the existing values in DB
1529 --
1530 --
1531
1532 PROCEDURE validate_populate_rec(p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
1533 p_allocation_in_rec_out OUT NOCOPY ALLOCATION_REC_IN_TYPE ,
1534 p_previous_amount OUT NOCOPY NUMBER ,
1535 -- p_conversion_rate OUT NOCOPY NUMBER ,
1536 p_flag OUT NOCOPY VARCHAR2
1537 ) is
1538
1539 cursor c_allocation_row is
1540 select *
1541 from oke_k_fund_allocations
1542 where fund_allocation_id = p_allocation_in_rec.fund_allocation_id
1543 FOR UPDATE OF fund_allocation_id NOWAIT;
1544
1545 cursor c_version is
1546 select major_version + 1
1547 from okc_k_vers_numbers
1548 where chr_id = p_allocation_in_rec.object_id;
1549
1550 l_allocation_row c_allocation_row%ROWTYPE;
1551 l_error_value VARCHAR2(50);
1552 l_version NUMBER;
1553
1554 BEGIN
1555
1556 p_flag := 'N';
1557 p_allocation_in_rec_out := p_allocation_in_rec;
1558
1559 OPEN c_version;
1560 FETCH c_version into l_version;
1561 CLOSE c_version;
1562
1563 OPEN c_allocation_row;
1564 FETCH c_allocation_row into l_allocation_row;
1565 CLOSE c_allocation_row;
1566
1567 IF p_allocation_in_rec_out.agreement_id = OKE_API.G_MISS_NUM THEN
1568 p_allocation_in_rec_out.agreement_id := null;
1569 END IF;
1570
1571 IF (p_allocation_in_rec_out.funding_source_id = OKE_API.G_MISS_NUM) THEN
1572 p_allocation_in_rec_out.funding_source_id := l_allocation_row.funding_source_id;
1573
1574 ELSIF (nvl(p_allocation_in_rec_out.funding_source_id, -99) <> l_allocation_row.funding_source_id) THEN
1575
1576 OKE_API.set_message(p_app_name => G_APP_NAME ,
1577 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1578 p_token1 => 'VALUE' ,
1579 p_token1_value => 'funding_source_id'
1580 );
1581
1582 RAISE G_EXCEPTION_HALT_VALIDATION;
1583
1584 END IF;
1585
1586 IF (p_allocation_in_rec_out.object_id = OKE_API.G_MISS_NUM) THEN
1587 p_allocation_in_rec_out.object_id := l_allocation_row.object_id;
1588
1589 ELSIF (nvl(p_allocation_in_rec_out.object_id, -99) <> l_allocation_row.object_id) THEN
1590
1591 OKE_API.set_message(p_app_name => G_APP_NAME ,
1592 p_msg_name => 'OKE_API_INVALID_VALUE' ,
1593 p_token1 => 'VALUE' ,
1594 p_token1_value => 'object_id'
1595 );
1596
1597 RAISE G_EXCEPTION_HALT_VALIDATION;
1598
1599 END IF;
1600
1601 IF (p_allocation_in_rec_out.k_line_id = OKE_API.G_MISS_NUM) THEN
1602 p_allocation_in_rec_out.k_line_id := l_allocation_row.k_line_id;
1603 END IF;
1604
1605 IF p_allocation_in_rec_out.pa_conversion_date = OKE_API.G_MISS_DATE THEN
1606 p_allocation_in_rec_out.pa_conversion_date := l_allocation_row.pa_conversion_date;
1607 END IF;
1608
1609 IF p_allocation_in_rec_out.pa_conversion_type = OKE_API.G_MISS_CHAR THEN
1610 p_allocation_in_rec_out.pa_conversion_type := l_allocation_row.pa_conversion_type;
1611 END IF;
1612
1613 IF p_allocation_in_rec_out.pa_conversion_rate = OKE_API.G_MISS_NUM THEN
1614 p_allocation_in_rec_out.pa_conversion_rate := l_allocation_row.pa_conversion_rate;
1615 END IF;
1616
1617 IF (p_allocation_in_rec_out.project_id = OKE_API.G_MISS_NUM) THEN
1618 p_allocation_in_rec_out.project_id := l_allocation_row.project_id;
1619 END IF;
1620
1621 IF (nvl(p_allocation_in_rec_out.project_id, -99) <> nvl(l_allocation_row.project_id, -99)) THEN
1622
1623 p_flag := 'Y';
1624
1625 ELSIF (nvl(p_allocation_in_rec_out.pa_conversion_type, '-99') <> nvl(l_allocation_row.pa_conversion_type, '-99')) OR
1626 (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
1627 (nvl(p_allocation_in_rec_out.pa_conversion_rate, -99) <> nvl(l_allocation_row.pa_conversion_rate, -99)) THEN
1628 /*
1629 IF (p_allocation_in_rec_out.pa_conversion_type is not null) AND
1630 (p_allocation_in_rec_out.pa_conversion_date is not null) THEN
1631 */
1632 p_flag := 'Y';
1633
1634
1635 -- END IF;
1636
1637 ELSE
1638
1639 p_flag := 'N';
1640
1641 END IF;
1642
1643 IF (p_allocation_in_rec_out.task_id = OKE_API.G_MISS_NUM) THEN
1644 p_allocation_in_rec_out.task_id := l_allocation_row.task_id;
1645 END IF;
1646
1647 --
1648 -- Check values for contract, project and task if created version = current version
1649 --
1650
1651 IF (nvl(p_allocation_in_rec_out.task_id, -99) <> nvl(l_allocation_row.task_id, -99)) AND
1652 ((nvl(l_allocation_row.created_in_version, -99) <> nvl(l_version, -99)) OR
1653 (nvl(l_allocation_row.agreement_version, 0) <> 0)) THEN
1654
1655 OKE_API.set_message(p_app_name => G_APP_NAME ,
1656 p_msg_name => 'OKE_API_NO_UPDATE' ,
1657 p_token1 => 'VALUE' ,
1658 p_token1_value => 'task_id'
1659 );
1660
1661 RAISE G_EXCEPTION_HALT_VALIDATION;
1662
1663 END IF;
1664
1665 IF (nvl(p_allocation_in_rec_out.project_id, -99) <> nvl(l_allocation_row.project_id, -99)) AND
1666 ((nvl(l_allocation_row.created_in_version, -99) <> nvl(l_version, -99)) OR
1667 (nvl(l_allocation_row.agreement_version, 0) <> 0)) THEN
1668
1669
1670 OKE_API.set_message(p_app_name => G_APP_NAME ,
1671 p_msg_name => 'OKE_API_NO_UPDATE' ,
1672 p_token1 => 'VALUE' ,
1673 p_token1_value => 'project_id'
1674 );
1675
1676 RAISE G_EXCEPTION_HALT_VALIDATION;
1677
1678 END IF;
1679
1680 IF (nvl(p_allocation_in_rec_out.k_line_id, -99) <> nvl(l_allocation_row.k_line_id, -99)) AND
1681 ((nvl(l_allocation_row.created_in_version, -99) <> nvl(l_version, -99)) OR
1682 ((nvl(l_allocation_row.agreement_version, 0) <> 0) AND (nvl(l_allocation_row.pa_flag, 'N') = 'N'))) THEN
1683
1684 OKE_API.set_message(p_app_name => G_APP_NAME ,
1685 p_msg_name => 'OKE_API_NO_UPDATE' ,
1686 p_token1 => 'VALUE' ,
1687 p_token1_value => 'k_line_id'
1688 );
1689
1690 RAISE G_EXCEPTION_HALT_VALIDATION;
1691
1692 END IF;
1693
1694 IF p_allocation_in_rec_out.start_date_active = OKE_API.G_MISS_DATE THEN
1695 p_allocation_in_rec_out.start_date_active := l_allocation_row.start_date_active;
1696 END IF;
1697
1698 IF (nvl(l_allocation_row.agreement_version, 0) <> 0) AND
1699 (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
1700
1701 OKE_API.set_message(p_app_name => G_APP_NAME ,
1702 p_msg_name => 'OKE_API_NO_UPDATE' ,
1703 p_token1 => 'VALUE' ,
1704 p_token1_value => 'start_date_active'
1705 );
1706
1707 RAISE G_EXCEPTION_HALT_VALIDATION;
1708
1709 END IF;
1710
1711 IF (p_allocation_in_rec_out.amount = OKE_API.G_MISS_NUM) THEN
1712 p_allocation_in_rec_out.amount := l_allocation_row.amount;
1713 END IF;
1714
1715 --
1716 -- Check if agreement exists
1717 --
1718 /*
1719 IF (nvl(l_allocation_row.agreement_version, 0) <> 0) THEN
1720
1721 IF (nvl(l_allocation_row.project_id, -99) <> nvl(p_allocation_in_rec_out.project_id, -99)) THEN
1722
1723 l_error_value := 'Project';
1724
1725 ELSIF (nvl(l_allocation_row.task_id, -99) <> nvl(p_allocation_in_rec_out.task_id, -99)) THEN
1726
1727 l_error_value := 'Task';
1728
1729 ELSIF (l_allocation_row.start_date_active <> p_allocation_in_rec_out.start_date_active) THEN
1730
1731 l_error_value := 'Start date active';
1732
1733 END IF;
1734
1735 IF (l_error_value is not null) THEN
1736
1737 OKE_API.set_message(p_app_name => G_APP_NAME ,
1738 p_msg_name => 'OKE_NO_FUND_CHANGE' ,
1739 p_token1 => 'FIELD' ,
1740 p_token1_value => l_error_value
1741 );
1742
1743 RAISE G_EXCEPTION_HALT_VALIDATION;
1744
1745 END IF;
1746
1747 END IF;
1748 */
1749 IF p_allocation_in_rec_out.funding_status = OKE_API.G_MISS_CHAR THEN
1750 p_allocation_in_rec_out.funding_status := l_allocation_row.funding_status;
1751 END IF;
1752
1753 IF p_allocation_in_rec_out.fund_type = OKE_API.G_MISS_CHAR THEN
1754 p_allocation_in_rec_out.fund_type := l_allocation_row.fund_type;
1755 END IF;
1756
1757 IF p_allocation_in_rec_out.end_date_active = OKE_API.G_MISS_DATE THEN
1758 p_allocation_in_rec_out.end_date_active := l_allocation_row.end_date_active;
1759 END IF;
1760
1761 IF p_allocation_in_rec_out.fiscal_year = OKE_API.G_MISS_NUM THEN
1762 p_allocation_in_rec_out.fiscal_year := l_allocation_row.fiscal_year;
1763 END IF;
1764
1765 IF (p_allocation_in_rec_out.hard_limit = OKE_API.G_MISS_NUM) THEN
1766 p_allocation_in_rec_out.hard_limit := l_allocation_row.hard_limit;
1767 END IF;
1768
1769 IF p_allocation_in_rec_out.reference1 = OKE_API.G_MISS_CHAR THEN
1770 p_allocation_in_rec_out.reference1 := l_allocation_row.reference1;
1771 END IF;
1772
1773 IF p_allocation_in_rec_out.reference2 = OKE_API.G_MISS_CHAR THEN
1774 p_allocation_in_rec_out.reference2 := l_allocation_row.reference2;
1775 END IF;
1776
1777 IF p_allocation_in_rec_out.reference3 = OKE_API.G_MISS_CHAR THEN
1778 p_allocation_in_rec_out.reference3 := l_allocation_row.reference3;
1779 END IF;
1780
1781 IF p_allocation_in_rec_out.oke_attribute_category = OKE_API.G_MISS_CHAR THEN
1782 p_allocation_in_rec_out.oke_attribute_category := l_allocation_row.attribute_category;
1783 END IF;
1784
1785 IF p_allocation_in_rec_out.oke_attribute1 = OKE_API.G_MISS_CHAR THEN
1786 p_allocation_in_rec_out.oke_attribute1 := l_allocation_row.attribute1;
1787 END IF;
1788
1789 IF p_allocation_in_rec_out.oke_attribute2 = OKE_API.G_MISS_CHAR THEN
1790 p_allocation_in_rec_out.oke_attribute2 := l_allocation_row.attribute2;
1791 END IF;
1792
1793 IF p_allocation_in_rec_out.oke_attribute3 = OKE_API.G_MISS_CHAR THEN
1794 p_allocation_in_rec_out.oke_attribute3 := l_allocation_row.attribute3;
1795 END IF;
1796
1797 IF p_allocation_in_rec_out.oke_attribute4 = OKE_API.G_MISS_CHAR THEN
1798 p_allocation_in_rec_out.oke_attribute4 := l_allocation_row.attribute4;
1799 END IF;
1800
1801 IF p_allocation_in_rec_out.oke_attribute5 = OKE_API.G_MISS_CHAR THEN
1802 p_allocation_in_rec_out.oke_attribute5 := l_allocation_row.attribute5;
1803 END IF;
1804
1805 IF p_allocation_in_rec_out.oke_attribute6 = OKE_API.G_MISS_CHAR THEN
1806 p_allocation_in_rec_out.oke_attribute6 := l_allocation_row.attribute6;
1807 END IF;
1808
1809 IF p_allocation_in_rec_out.oke_attribute7 = OKE_API.G_MISS_CHAR THEN
1810 p_allocation_in_rec_out.oke_attribute7 := l_allocation_row.attribute7;
1811 END IF;
1812
1813 IF p_allocation_in_rec_out.oke_attribute8 = OKE_API.G_MISS_CHAR THEN
1814 p_allocation_in_rec_out.oke_attribute8 := l_allocation_row.attribute8;
1815 END IF;
1816
1817 IF p_allocation_in_rec_out.oke_attribute9 = OKE_API.G_MISS_CHAR THEN
1818 p_allocation_in_rec_out.oke_attribute9 := l_allocation_row.attribute9;
1819 END IF;
1820
1821 IF p_allocation_in_rec_out.oke_attribute10 = OKE_API.G_MISS_CHAR THEN
1822 p_allocation_in_rec_out.oke_attribute10 := l_allocation_row.attribute10;
1823 END IF;
1824
1825 IF p_allocation_in_rec_out.oke_attribute11 = OKE_API.G_MISS_CHAR THEN
1826 p_allocation_in_rec_out.oke_attribute11 := l_allocation_row.attribute11;
1827 END IF;
1828
1829 IF p_allocation_in_rec_out.oke_attribute12 = OKE_API.G_MISS_CHAR THEN
1830 p_allocation_in_rec_out.oke_attribute12 := l_allocation_row.attribute12;
1831 END IF;
1832
1833 IF p_allocation_in_rec_out.oke_attribute13 = OKE_API.G_MISS_CHAR THEN
1834 p_allocation_in_rec_out.oke_attribute13 := l_allocation_row.attribute13;
1835 END IF;
1836
1837 IF p_allocation_in_rec_out.oke_attribute14 = OKE_API.G_MISS_CHAR THEN
1838 p_allocation_in_rec_out.oke_attribute14 := l_allocation_row.attribute14;
1839 END IF;
1840
1841 IF p_allocation_in_rec_out.oke_attribute15 = OKE_API.G_MISS_CHAR THEN
1842 p_allocation_in_rec_out.oke_attribute15 := l_allocation_row.attribute15;
1843 END IF;
1844
1845 IF p_allocation_in_rec_out.revenue_hard_limit = OKE_API.G_MISS_NUM THEN
1846 p_allocation_in_rec_out.revenue_hard_limit := l_allocation_row.revenue_hard_limit;
1847 END IF;
1848
1849 IF p_allocation_in_rec_out.pa_attribute_category = OKE_API.G_MISS_CHAR THEN
1850 p_allocation_in_rec_out.pa_attribute_category := l_allocation_row.pa_attribute_category;
1851 END IF;
1852
1853 IF p_allocation_in_rec_out.pa_attribute1 = OKE_API.G_MISS_CHAR THEN
1854 p_allocation_in_rec_out.pa_attribute1 := l_allocation_row.pa_attribute1;
1855 END IF;
1856
1857 IF p_allocation_in_rec_out.pa_attribute2 = OKE_API.G_MISS_CHAR THEN
1858 p_allocation_in_rec_out.pa_attribute2 := l_allocation_row.pa_attribute2;
1859 END IF;
1860
1861 IF p_allocation_in_rec_out.pa_attribute3 = OKE_API.G_MISS_CHAR THEN
1862 p_allocation_in_rec_out.pa_attribute3 := l_allocation_row.pa_attribute3;
1863 END IF;
1864
1865 IF p_allocation_in_rec_out.pa_attribute4 = OKE_API.G_MISS_CHAR THEN
1866 p_allocation_in_rec_out.pa_attribute4 := l_allocation_row.pa_attribute4;
1867 END IF;
1868
1869 IF p_allocation_in_rec_out.pa_attribute5 = OKE_API.G_MISS_CHAR THEN
1870 p_allocation_in_rec_out.pa_attribute5 := l_allocation_row.pa_attribute5;
1871 END IF;
1872
1873 IF p_allocation_in_rec_out.pa_attribute6 = OKE_API.G_MISS_CHAR THEN
1874 p_allocation_in_rec_out.pa_attribute6 := l_allocation_row.pa_attribute6;
1875 END IF;
1876
1877 IF p_allocation_in_rec_out.pa_attribute7 = OKE_API.G_MISS_CHAR THEN
1878 p_allocation_in_rec_out.pa_attribute7 := l_allocation_row.pa_attribute7;
1879 END IF;
1880
1881 IF p_allocation_in_rec_out.pa_attribute8 = OKE_API.G_MISS_CHAR THEN
1882 p_allocation_in_rec_out.pa_attribute8 := l_allocation_row.pa_attribute8;
1883 END IF;
1884
1885 IF p_allocation_in_rec_out.pa_attribute9 = OKE_API.G_MISS_CHAR THEN
1886 p_allocation_in_rec_out.pa_attribute9 := l_allocation_row.pa_attribute9;
1887 END IF;
1888
1889 IF p_allocation_in_rec_out.pa_attribute10 = OKE_API.G_MISS_CHAR THEN
1890 p_allocation_in_rec_out.pa_attribute10 := l_allocation_row.pa_attribute10;
1891 END IF;
1892
1893 IF p_allocation_in_rec_out.funding_category = OKE_API.G_MISS_CHAR THEN
1894 p_allocation_in_rec_out.funding_category := l_allocation_row.funding_category;
1895 END IF;
1896
1897 IF (nvl(l_allocation_row.agreement_version, 0) <> 0) AND
1898 (nvl(p_allocation_in_rec_out.funding_category, '-99') <> l_allocation_row.funding_category) THEN
1899
1900 OKE_API.set_message(p_app_name => G_APP_NAME ,
1901 p_msg_name => 'OKE_API_NO_UPDATE' ,
1902 p_token1 => 'VALUE' ,
1903 p_token1_value => 'funding_category'
1904 );
1905
1906 RAISE G_EXCEPTION_HALT_VALIDATION;
1907
1908 END IF;
1909
1910 --p_conversion_rate := l_allocation_row.pa_conversion_rate;
1911 p_previous_amount := l_allocation_row.previous_amount;
1912
1913 END validate_populate_rec;
1914
1915
1916 --
1917 -- Procedure: validate_attributes
1918 --
1919 -- Description: This procedure is used to validate allocation record attributes
1920 --
1921 --
1922
1923 PROCEDURE validate_attributes(p_allocation_in_rec ALLOCATION_REC_IN_TYPE) is
1924 l_return_status VARCHAR2(1);
1925 BEGIN
1926
1927 --
1928 -- Funding_Source_Id
1929 --
1930
1931 validate_funding_source_id(p_funding_source_id => p_allocation_in_rec.funding_source_id ,
1932 p_return_status => l_return_status
1933 );
1934
1935 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1936
1937 RAISE G_EXCEPTION_HALT_VALIDATION;
1938
1939 END IF;
1940
1941 --
1942 -- Object Id
1943 --
1944
1945 validate_object_id(p_object_id => p_allocation_in_rec.object_id ,
1946 p_funding_source_id => p_allocation_in_rec.funding_source_id ,
1947 p_return_status => l_return_status
1948 );
1949
1950 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1951
1952 RAISE G_EXCEPTION_HALT_VALIDATION;
1953
1954 END IF;
1955
1956 --
1957 -- Amount
1958 --
1959
1960 validate_amount(p_amount => p_allocation_in_rec.amount ,
1961 p_return_status => l_return_status
1962 );
1963
1964 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1965
1966 RAISE G_EXCEPTION_HALT_VALIDATION;
1967
1968 END IF;
1969
1970 --
1971 -- K_Line_Id
1972 --
1973
1974 validate_k_line_id(p_k_line_id => p_allocation_in_rec.k_line_id ,
1975 p_project_id => p_allocation_in_rec.project_id ,
1976 p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
1977 p_return_status => l_return_status
1978 );
1979
1980 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1981
1982 RAISE G_EXCEPTION_HALT_VALIDATION;
1983
1984 END IF;
1985
1986 --
1987 -- Project_id
1988 --
1989
1990 validate_project_id(p_project_id => p_allocation_in_rec.project_id ,
1991 p_k_line_id => p_allocation_in_rec.k_line_id ,
1992 p_funding_source_id => p_allocation_in_rec.funding_source_id ,
1993 p_object_id => p_allocation_in_rec.object_id ,
1994 p_return_status => l_return_status
1995 );
1996
1997 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
1998
1999 RAISE G_EXCEPTION_HALT_VALIDATION;
2000
2001 END IF;
2002
2003 --
2004 -- Task_id
2005 --
2006
2007 validate_task_id(p_task_id => p_allocation_in_rec.task_id ,
2008 p_return_status => l_return_status
2009 );
2010
2011 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2012
2013 RAISE G_EXCEPTION_HALT_VALIDATION;
2014
2015 END IF;
2016
2017 --
2018 -- Fund_type
2019 --
2020
2021 validate_fund_type(p_fund_type => p_allocation_in_rec.fund_type ,
2022 p_return_status => l_return_status
2023 );
2024
2025 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2026
2027 RAISE G_EXCEPTION_HALT_VALIDATION;
2028
2029 END IF;
2030
2031 --
2032 -- Funding_status
2033 --
2034
2035 validate_funding_status(p_funding_status => p_allocation_in_rec.funding_status ,
2036 p_return_status => l_return_status
2037 );
2038
2039 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2040
2041 RAISE G_EXCEPTION_HALT_VALIDATION;
2042
2043 END IF;
2044
2045 -- Conversion_type
2046 validate_conversion_type(p_conversion_type => p_allocation_in_rec.pa_conversion_type ,
2047 p_return_status => l_return_status
2048 );
2049
2050 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2051
2052 RAISE G_EXCEPTION_HALT_VALIDATION;
2053
2054 END IF;
2055
2056 --
2057 -- Funding_category
2058 --
2059
2060 validate_funding_category(p_funding_category => p_allocation_in_rec.funding_category ,
2061 p_return_status => l_return_status
2062 );
2063
2064 IF (l_return_status <> OKE_API.G_RET_STS_SUCCESS) THEN
2065
2066 RAISE G_EXCEPTION_HALT_VALIDATION;
2067
2068 END IF;
2069
2070 END validate_attributes;
2071
2072
2073 --
2074 -- Procedure: validate_record
2075 --
2076 -- Description: This procedure is used to validate allocation record
2077 --
2078 --
2079
2080 PROCEDURE validate_record(p_allocation_in_rec IN OUT NOCOPY ALLOCATION_REC_IN_TYPE ,
2081 p_validation_flag VARCHAR2 ,
2082 p_flag VARCHAR2
2083 -- p_conversion_rate OUT NOCOPY NUMBER
2084 ) is
2085
2086 l_return_status VARCHAR2(1);
2087 l_source_currency VARCHAR2(15);
2088 l_projfunc_currency VARCHAR2(15);
2089 l_type VARCHAR2(20);
2090
2091 BEGIN
2092
2093 --
2094 -- Start and End date range
2095 --
2096
2097 OKE_FUNDING_UTIL_PKG.validate_start_end_date(x_start_date => p_allocation_in_rec.start_date_active ,
2098 x_end_date => p_allocation_in_rec.end_date_active ,
2099 x_return_status => l_return_status
2100 );
2101
2102 IF (l_return_status = 'N') THEN
2103
2104 OKE_API.set_message(p_app_name => 'OKE' ,
2105 p_msg_name => 'OKE_INVALID_EFFDATE_PAIR'
2106 );
2107
2108 RAISE G_EXCEPTION_HALT_VALIDATION;
2109
2110 END IF;
2111
2112 --
2113 -- Validate if date range within source date range
2114 --
2115
2116 IF (p_validation_flag = OKE_API.G_TRUE) THEN
2117
2118 -- Start date
2119 -- bug 3345170
2120 /*
2121 OKE_FUNDING_UTIL_PKG.validate_alloc_source_date
2122 (x_start_end => 'START' ,
2123 x_funding_source_id => p_allocation_in_rec.funding_source_id ,
2124 x_date => p_allocation_in_rec.start_date_active ,
2125 x_return_status => l_return_status
2126 );
2127
2128 IF (l_return_status = 'N') THEN
2129
2130 OKE_API.set_message(p_app_name => G_APP_NAME ,
2131 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
2132 p_token1 => 'EFFECTIVE_DATE' ,
2133 p_token1_value => 'OKE_EFFECTIVE_FROM_PROMPT' ,
2134 p_token1_translate => OKE_API.G_TRUE ,
2135 p_token2 => 'OPERATOR' ,
2136 p_token2_value => 'OKE_GREATER_PROMPT' ,
2137 p_token2_translate => OKE_API.G_TRUE ,
2138 p_token3 => 'DATE_SOURCE' ,
2139 p_token3_value => 'OKE_FUNDING_SOURCE_PROMPT' ,
2140 p_token3_translate => OKE_API.G_TRUE
2141 );
2142
2143 RAISE G_EXCEPTION_HALT_VALIDATION;
2144
2145 END IF;
2146
2147 -- End date
2148
2149 OKE_FUNDING_UTIL_PKG.validate_alloc_source_date
2150 (x_start_end => 'END' ,
2151 x_funding_source_id => p_allocation_in_rec.funding_source_id ,
2152 x_date => p_allocation_in_rec.end_date_active ,
2153 x_return_status => l_return_status
2154 );
2155
2156 IF (l_return_status = 'N') THEN
2157
2158 OKE_API.set_message(p_app_name => G_APP_NAME ,
2159 p_msg_name => 'OKE_FUND_INVALID_PTY_DATE' ,
2160 p_token1 => 'EFFECTIVE_DATE' ,
2161 p_token1_value => 'OKE_EFFECTIVE_TO_PROMPT' ,
2162 p_token1_translate => OKE_API.G_TRUE ,
2163 p_token2 => 'OPERATOR' ,
2164 p_token2_value => 'OKE_EARLIER_PROMPT' ,
2165 p_token2_translate => OKE_API.G_TRUE ,
2166 p_token3 => 'DATE_SOURCE' ,
2167 p_token3_value => 'OKE_FUNDING_SOURCE_PROMPT' ,
2168 p_token3_translate => OKE_API.G_TRUE
2169 );
2170
2171 RAISE G_EXCEPTION_HALT_VALIDATION;
2172
2173 END IF;
2174 */
2175 --
2176 -- Validate if enough fund amount to be allocated
2177 --
2178
2179 --oke_debug.debug('validating if enough funding amount for funding allocation');
2180 --dbms_output.put_line('validating if enough funding amount for funding allocation');
2181
2182 OKE_FUNDING_UTIL_PKG.validate_alloc_source_amount
2183 (x_source_id => p_allocation_in_rec.funding_source_id ,
2184 x_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2185 x_amount => p_allocation_in_rec.amount ,
2186 x_return_status => l_return_status
2187 );
2188
2189 IF (l_return_status = 'N') THEN
2190
2191 OKE_API.set_message(p_app_name => 'OKE' ,
2192 p_msg_name => 'OKE_FUND_AMT_EXCEED'
2193 );
2194
2195 RAISE G_EXCEPTION_HALT_VALIDATION;
2196
2197 ELSIF (l_return_status = 'E') THEN
2198
2199 OKE_API.set_message(p_app_name => 'OKE' ,
2200 p_msg_name => 'OKE_NEGATIVE_ALLOCATION_SUM'
2201 );
2202
2203 RAISE G_EXCEPTION_HALT_VALIDATION;
2204
2205 END IF;
2206
2207 --
2208 -- Validate if enough limit amount to be allocated
2209 --
2210
2211 --oke_debug.debug('validating if enough hard limit to be allocated');
2212 --dbms_output.put_line('validating if enough hard limit to be allocated');
2213
2214 OKE_FUNDING_UTIL_PKG.validate_alloc_source_limit
2215 (x_source_id => p_allocation_in_rec.funding_source_id ,
2216 x_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2217 x_amount => nvl(p_allocation_in_rec.hard_limit, 0) ,
2218 x_revenue_amount => nvl(p_allocation_in_rec.revenue_hard_limit, 0) ,
2219 x_type => l_type ,
2220 x_return_status => l_return_status
2221 );
2222
2223 IF (l_return_status = 'N') THEN
2224
2225 IF (l_type = 'INVOICE') THEN
2226
2227 OKE_API.set_message(p_app_name => 'OKE' ,
2228 p_msg_name => 'OKE_HARD_LIMIT_EXCEED'
2229 );
2230
2231 ELSE
2232
2233 OKE_API.set_message(p_app_name => 'OKE' ,
2234 p_msg_name => 'OKE_REV_LIMIT_EXCEED'
2235 );
2236
2237 END IF;
2238
2239 RAISE G_EXCEPTION_HALT_VALIDATION;
2240
2241 ELSIF (l_return_status = 'E') THEN
2242
2243 IF (l_type = 'INVOICE') THEN
2244
2245 OKE_API.set_message(p_app_name => 'OKE' ,
2246 p_msg_name => 'OKE_NEGATIVE_HARD_LIMIT_SUM'
2247 );
2248
2249 ELSE
2250
2251 OKE_API.set_message(p_app_name => 'OKE' ,
2252 p_msg_name => 'OKE_NEGATIVE_REV_LIMIT_SUM'
2253 );
2254
2255 END IF;
2256
2257 RAISE G_EXCEPTION_HALT_VALIDATION;
2258
2259 END IF;
2260
2261 END IF;
2262
2263 --
2264 -- Validate the combination of project_id and task_id
2265 --
2266
2267 validate_project_task(p_project_id => p_allocation_in_rec.project_id ,
2268 p_task_id => p_allocation_in_rec.task_id
2269 );
2270
2271 --
2272 -- Validate the combination of object_id and k_line_id
2273 --
2274
2275 validate_header_line(p_object_id => p_allocation_in_rec.object_id ,
2276 p_k_line_id => p_allocation_in_rec.k_line_id
2277 );
2278
2279 --
2280 -- Validate PA conversion
2281 --
2282
2283 IF (p_allocation_in_rec.project_id is not null) THEN
2284
2285 l_source_currency := get_source_currency(p_allocation_in_rec.funding_source_id);
2286 get_proj_info(p_project_id => p_allocation_in_rec.project_id ,
2287 p_projfunc_currency => l_projfunc_currency
2288 );
2289
2290 IF (l_source_currency = l_projfunc_currency) THEN
2291
2292 IF (p_allocation_in_rec.pa_conversion_type is not null) THEN
2293
2294 OKE_API.set_message(p_app_name => 'OKE' ,
2295 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2296 p_token1 => 'VALUE' ,
2297 p_token1_value => 'pa_conversion_type'
2298 );
2299
2300 RAISE G_EXCEPTION_HALT_VALIDATION;
2301
2302 ELSIF (p_allocation_in_rec.pa_conversion_date is not null) THEN
2303
2304 OKE_API.set_message(p_app_name => 'OKE' ,
2305 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2306 p_token1 => 'VALUE' ,
2307 p_token1_value => 'pa_conversion_date'
2308 );
2309
2310 RAISE G_EXCEPTION_HALT_VALIDATION;
2311
2312 ELSIF (p_allocation_in_rec.pa_conversion_rate is not null) THEN
2313
2314 OKE_API.set_message(p_app_name => 'OKE' ,
2315 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2316 p_token1 => 'VALUE' ,
2317 p_token1_value => 'pa_conversion_rate'
2318 );
2319
2320 RAISE G_EXCEPTION_HALT_VALIDATION;
2321
2322 END IF;
2323
2324 ELSIF (p_allocation_in_rec.pa_conversion_type is not null) AND
2325 (p_allocation_in_rec.pa_conversion_date is not null) THEN
2326
2327 IF (upper(p_allocation_in_rec.pa_conversion_type) <> 'USER') THEN
2328
2329 IF (p_allocation_in_rec.pa_conversion_rate is null) THEN
2330
2331 IF (nvl(p_flag, 'N') = 'Y') THEN
2332
2333 OKE_FUNDING_UTIL_PKG.get_conversion_rate(x_from_currency => l_source_currency ,
2334 x_to_currency => l_projfunc_currency ,
2335 x_conversion_type => p_allocation_in_rec.pa_conversion_type ,
2336 x_conversion_date => p_allocation_in_rec.pa_conversion_date ,
2337 x_conversion_rate => p_allocation_in_rec.pa_conversion_rate ,
2338 x_return_status => l_return_status
2339 );
2340
2341 IF (l_return_status = 'N') THEN
2342
2343 OKE_API.set_message(p_app_name => G_APP_NAME ,
2344 p_msg_name => 'OKE_FUND_NO_RATE'
2345 );
2346
2347 RAISE G_EXCEPTION_HALT_VALIDATION;
2348
2349 END IF;
2350
2351 END IF;
2352
2353 ELSIF (nvl(p_flag, 'N') = 'Y') THEN
2354
2355 OKE_API.set_message(p_app_name => 'OKE' ,
2356 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2357 p_token1 => 'VALUE' ,
2358 p_token1_value => 'pa_conversion_rate'
2359 );
2360
2361 RAISE G_EXCEPTION_HALT_VALIDATION;
2362
2363 END IF;
2364
2365 END IF;
2366
2367 ELSIF (nvl(upper(p_allocation_in_rec.pa_conversion_type), '-99') <> 'USER') THEN
2368
2369 IF (p_allocation_in_rec.pa_conversion_rate is not null) THEN
2370
2371 OKE_API.set_message(p_app_name => 'OKE' ,
2372 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2373 p_token1 => 'VALUE' ,
2374 p_token1_value => 'pa_conversion_rate'
2375 );
2376
2377 RAISE G_EXCEPTION_HALT_VALIDATION;
2378
2379 END IF;
2380
2381 END IF;
2382
2383 ELSIF (p_allocation_in_rec.pa_conversion_type is not null) THEN
2384
2385 OKE_API.set_message(p_app_name => 'OKE' ,
2386 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2387 p_token1 => 'VALUE' ,
2388 p_token1_value => 'pa_conversion_type'
2389 );
2390
2391 RAISE G_EXCEPTION_HALT_VALIDATION;
2392
2393 ELSIF (p_allocation_in_rec.pa_conversion_date is not null) THEN
2394
2395 OKE_API.set_message(p_app_name => 'OKE' ,
2396 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2397 p_token1 => 'VALUE' ,
2398 p_token1_value => 'pa_conversion_date'
2399 );
2400
2401 RAISE G_EXCEPTION_HALT_VALIDATION;
2402
2403 ELSIF (p_allocation_in_rec.pa_conversion_rate is not null) THEN
2404
2405 OKE_API.set_message(p_app_name => 'OKE' ,
2406 p_msg_name => 'OKE_API_INVALID_VALUE' ,
2407 p_token1 => 'VALUE' ,
2408 p_token1_value => 'pa_conversion_rate'
2409 );
2410
2411 RAISE G_EXCEPTION_HALT_VALIDATION;
2412
2413 END IF;
2414
2415 END validate_record;
2416
2417
2418 --
2419 -- Public Procedures and Funtions
2420 --
2421
2422 --
2423 -- Procedure add_allocation
2424 --
2425 -- Description: This procedure is used to insert record in OKE_K_FUND_ALLOCATIONS table
2426 --
2427 -- Calling subprograms: OKE_API.start_activity
2428 -- OKE_API.end_activity
2429 -- OKE_FUNDINGALLOCATION_PVT.insert_row
2430 -- null_allocation_out
2431 -- validate_attributes
2432 -- validate_record
2433 --
2434
2435 PROCEDURE add_allocation(p_api_version IN NUMBER ,
2436 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
2437 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
2438 p_msg_count OUT NOCOPY NUMBER ,
2439 p_msg_data OUT NOCOPY VARCHAR2 ,
2440 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
2441 p_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE ,
2442 p_validation_flag IN VARCHAR2 := OKE_API.G_TRUE ,
2443 p_return_status OUT NOCOPY VARCHAR2
2444 ) is
2445
2446 l_return_status VARCHAR2(1);
2447 l_rowid VARCHAR2(30);
2448 l_fund_allocation_id NUMBER;
2449 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
2450 l_api_name CONSTANT VARCHAR2(30) := 'add_allocation';
2451 --l_rate NUMBER;
2452
2453 BEGIN
2454
2455 --dbms_output.put_line('entering oke_allocation_pvt.add_allocation');
2456 --oke_debug.debug('entering oke_allocation_pvt.add_allocation');
2457
2458 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2459 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
2460
2461 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2462 p_pkg_name => G_PKG_NAME ,
2463 p_init_msg_list => p_init_msg_list ,
2464 l_api_version => G_API_VERSION_NUMBER ,
2465 p_api_version => p_api_version ,
2466 p_api_type => '_PVT' ,
2467 x_return_status => p_return_status
2468 );
2469
2470 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2471
2472 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2473
2474 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2475
2476 RAISE OKE_API.G_EXCEPTION_ERROR;
2477
2478 END IF;
2479
2480 --
2481 -- Set Default Null
2482 --
2483
2484 --dbms_output.put_line('set default value as null for all fields');
2485 --oke_debug.debug('set default value as null for all fields');
2486
2487 l_allocation_in_rec := null_allocation_out(p_allocation_in_rec => p_allocation_in_rec);
2488
2489 --
2490 -- Validate Attributes
2491 --
2492
2493 --dbms_output.put_line('validate record attributes');
2494 --oke_debug.debug('validate record attributes');
2495
2496 validate_attributes(p_allocation_in_rec => l_allocation_in_rec);
2497
2498 --
2499 -- Validate record
2500 --
2501
2502 --dbms_output.put_line('validate record');
2503 --oke_debug.debug('validate record');
2504
2505 validate_record(p_allocation_in_rec => l_allocation_in_rec ,
2506 p_validation_flag => p_validation_flag ,
2507 p_flag => 'Y'
2508 --p_conversion_rate => l_rate
2509 );
2510
2511 l_fund_allocation_id := get_fund_allocation_id;
2512 p_allocation_out_rec.fund_allocation_id := l_fund_allocation_id;
2513
2514 --dbms_output.put_line('calling oke_fundingallocation_pvt.insert_row from oke_allocation_pvt');
2515 --oke_debug.debug('calling oke_fundingallocation_pvt.insert_row from oke_allocation_pvt');
2516
2517 OKE_FUNDINGALLOCATION_PVT.insert_row(X_Rowid => l_rowid ,
2518 X_Fund_Allocation_Id => l_fund_allocation_id ,
2519 X_Funding_Source_Id => l_allocation_in_rec.funding_source_id ,
2520 X_Object_Id => l_allocation_in_rec.object_id ,
2521 X_K_Line_Id => l_allocation_in_rec.k_line_id ,
2522 X_Project_Id => l_allocation_in_rec.project_id ,
2523 X_Task_Id => l_allocation_in_rec.task_id ,
2524 X_Previous_Amount => 0 ,
2525 X_Amount => l_allocation_in_rec.amount ,
2526 X_Hard_Limit => l_allocation_in_rec.hard_limit ,
2527 X_Fund_Type => upper(l_allocation_in_rec.fund_type) ,
2528 X_Funding_Status => upper(l_allocation_in_rec.funding_status) ,
2529 X_Fiscal_Year => l_allocation_in_rec.fiscal_year ,
2530 X_Reference1 => l_allocation_in_rec.reference1 ,
2531 X_Reference2 => l_allocation_in_rec.reference2 ,
2532 X_Reference3 => l_allocation_in_rec.reference3 ,
2533 X_PA_CONVERSION_TYPE => l_allocation_in_rec.PA_CONVERSION_TYPE ,
2534 X_PA_CONVERSION_DATE => l_allocation_in_rec.PA_CONVERSION_DATE ,
2535 X_PA_CONVERSION_RATE => l_allocation_in_rec.pa_conversion_rate ,
2536 X_Insert_Update_Flag => 'Y' ,
2537 X_Start_Date_Active => l_allocation_in_rec.start_date_active ,
2538 X_End_Date_Active => l_allocation_in_rec.end_date_active ,
2539 X_Last_Update_Date => sysdate ,
2540 X_Last_Updated_By => L_USERID ,
2541 X_Creation_Date => sysdate ,
2542 X_Created_By => L_USERID ,
2543 X_Last_Update_Login => L_LOGINID ,
2544 --X_Attribute_Category => upper(l_allocation_in_rec.oke_attribute_category) ,
2545 X_Attribute_Category => l_allocation_in_rec.oke_attribute_category ,
2546 X_Attribute1 => l_allocation_in_rec.oke_attribute1 ,
2547 X_Attribute2 => l_allocation_in_rec.oke_attribute2 ,
2548 X_Attribute3 => l_allocation_in_rec.oke_attribute3 ,
2549 X_Attribute4 => l_allocation_in_rec.oke_attribute4 ,
2550 X_Attribute5 => l_allocation_in_rec.oke_attribute5 ,
2551 X_Attribute6 => l_allocation_in_rec.oke_attribute6 ,
2552 X_Attribute7 => l_allocation_in_rec.oke_attribute7 ,
2553 X_Attribute8 => l_allocation_in_rec.oke_attribute8 ,
2554 X_Attribute9 => l_allocation_in_rec.oke_attribute9 ,
2555 X_Attribute10 => l_allocation_in_rec.oke_attribute10 ,
2556 X_Attribute11 => l_allocation_in_rec.oke_attribute11 ,
2557 X_Attribute12 => l_allocation_in_rec.oke_attribute12 ,
2558 X_Attribute13 => l_allocation_in_rec.oke_attribute13 ,
2559 X_Attribute14 => l_allocation_in_rec.oke_attribute14 ,
2560 X_Attribute15 => l_allocation_in_rec.oke_attribute15 ,
2561 X_Revenue_Hard_Limit => l_allocation_in_rec.revenue_hard_limit ,
2562 X_Funding_Category => upper(l_allocation_in_rec.funding_category) ,
2563 --X_PA_Attribute_Category => upper(l_allocation_in_rec.pa_attribute_category) ,
2564 X_PA_Attribute_Category => l_allocation_in_rec.pa_attribute_category ,
2565 X_PA_Attribute1 => l_allocation_in_rec.pa_attribute1 ,
2566 X_PA_Attribute2 => l_allocation_in_rec.pa_attribute2 ,
2567 X_PA_Attribute3 => l_allocation_in_rec.pa_attribute3 ,
2568 X_PA_Attribute4 => l_allocation_in_rec.pa_attribute4 ,
2569 X_PA_Attribute5 => l_allocation_in_rec.pa_attribute5 ,
2570 X_PA_Attribute6 => l_allocation_in_rec.pa_attribute6 ,
2571 X_PA_Attribute7 => l_allocation_in_rec.pa_attribute7 ,
2572 X_PA_Attribute8 => l_allocation_in_rec.pa_attribute8 ,
2573 X_PA_Attribute9 => l_allocation_in_rec.pa_attribute9 ,
2574 X_PA_Attribute10 => l_allocation_in_rec.pa_attribute10
2575 );
2576
2577 --dbms_output.put_line('finished oke_allocation_pvt.add_allocation w/ ' || p_return_status);
2578 --oke_debug.debug('finished oke_allocation_pvt.add_allocation w/ ' || p_return_status);
2579
2580 IF FND_API.to_boolean(p_commit) THEN
2581
2582 COMMIT WORK;
2583
2584 END IF;
2585
2586 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
2587 x_msg_data => p_msg_data
2588 );
2589
2590 EXCEPTION
2591 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
2592 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
2593 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2594 p_pkg_name => G_PKG_NAME ,
2595 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
2596 x_msg_count => p_msg_count ,
2597 x_msg_data => p_msg_data ,
2598 p_api_type => '_PVT'
2599 );
2600
2601 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2602 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2603 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2604 p_pkg_name => G_PKG_NAME ,
2605 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
2606 x_msg_count => p_msg_count ,
2607 x_msg_data => p_msg_data ,
2608 p_api_type => '_PVT'
2609 );
2610
2611 WHEN OTHERS THEN
2612 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2613 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2614 p_pkg_name => G_PKG_NAME ,
2615 p_exc_name => 'OTHERS' ,
2616 x_msg_count => p_msg_count ,
2617 x_msg_data => p_msg_data ,
2618 p_api_type => '_PVT'
2619 );
2620
2621 END add_allocation;
2622
2623
2624 --
2625 -- Procedure update_allocation
2626 --
2627 -- Description: This procedure is used to update record in OKE_K_FUND_ALLOCATIONS table
2628 --
2629 -- Calling subprograms: OKE_API.start_activity
2630 -- OKE_API.end_activity
2631 -- allowable_changes
2632 -- OKE_FUNDINGALLOCATION_PVT.update_allocation
2633 -- validate_fund_allocation_id
2634 -- validate_populate_rec
2635 -- validate_attributes
2636 -- validate_record
2637 --
2638
2639 PROCEDURE update_allocation(p_api_version IN NUMBER ,
2640 p_init_msg_list IN VARCHAR2 :=OKE_API.G_FALSE ,
2641 p_commit IN VARCHAR2 :=OKE_API.G_FALSE ,
2642 p_msg_count OUT NOCOPY NUMBER ,
2643 p_msg_data OUT NOCOPY VARCHAR2 ,
2644 p_allocation_in_rec IN ALLOCATION_REC_IN_TYPE ,
2645 p_allocation_out_rec OUT NOCOPY ALLOCATION_REC_OUT_TYPE ,
2646 p_validation_flag IN VARCHAR2 := OKE_API.G_TRUE ,
2647 p_return_status OUT NOCOPY VARCHAR2
2648 ) is
2649
2650 l_api_name CONSTANT VARCHAR2(30) := 'update_allocation';
2651 l_allocation_in_rec ALLOCATION_REC_IN_TYPE;
2652 l_return_status VARCHAR2(1);
2653 l_rowid VARCHAR2(30);
2654 -- l_rate NUMBER;
2655 -- l_rate2 NUMBER;
2656 l_flag VARCHAR2(1);
2657 l_version NUMBER;
2658 l_previous_amount NUMBER;
2659
2660 BEGIN
2661
2662 --dbms_output.put_line('entering oke_allocation_pvt.update_allocation');
2663 --oke_debug.debug('entering oke_allocation_pvt.update_allocation');
2664
2665 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2666 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_SUCCESS;
2667
2668 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2669 p_pkg_name => G_PKG_NAME ,
2670 p_init_msg_list => p_init_msg_list ,
2671 l_api_version => G_API_VERSION_NUMBER ,
2672 p_api_version => p_api_version ,
2673 p_api_type => '_PVT' ,
2674 x_return_status => p_return_status
2675 );
2676
2677 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2678
2679 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2680
2681 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2682
2683 RAISE OKE_API.G_EXCEPTION_ERROR;
2684
2685 END IF;
2686
2687 --
2688 -- Validate if fund_allocation_id is valid or not
2689 --
2690
2691 --dbms_output.put_line('validate fund_allocation_id');
2692 --oke_debug.debug('validate fund_allocation_id');
2693
2694 validate_fund_allocation_id(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
2695 p_rowid => l_rowid ,
2696 p_version => l_version
2697 );
2698
2699 --
2700 -- Validate and set the missing value for the fields
2701 --
2702
2703 --dbms_output.put_line('validate and populate the record');
2704 --oke_debug.debug('validate and populate the record');
2705
2706 validate_populate_rec(p_allocation_in_rec => p_allocation_in_rec ,
2707 p_allocation_in_rec_out => l_allocation_in_rec ,
2708 p_previous_amount => l_previous_amount ,
2709 --p_conversion_rate => l_rate ,
2710 p_flag => l_flag
2711 );
2712
2713 --
2714 -- Validate Attributes
2715 --
2716
2717 --dbms_output.put_line('validate allocation attributes');
2718 --oke_debug.debug('validate allocation attributes');
2719
2720 validate_attributes(p_allocation_in_rec => l_allocation_in_rec );
2721
2722 --
2723 -- Validate record
2724 --
2725
2726 --dbms_output.put_line('validate allocation record');
2727 --oke_debug.debug('validate allocation record');
2728
2729 validate_record(p_allocation_in_rec => l_allocation_in_rec ,
2730 p_validation_flag => p_validation_flag ,
2731 p_flag => l_flag
2732 -- p_conversion_rate => l_rate2
2733 );
2734 /*
2735 IF (l_flag = 'Y') THEN
2736
2737 l_rate := l_rate2;
2738
2739 END IF;
2740 */
2741 --
2742 -- Validate if record exists in PA and check changes are allowable or not
2743 --
2744 /*
2745 IF (l_version <> 0 ) THEN
2746
2747 --dbms_output.put_line('calling allowable changes');
2748 --oke_debug.debug('calling allowable changes');
2749
2750 allowable_changes(p_fund_allocation_id => l_allocation_in_rec.fund_allocation_id ,
2751 p_project_id => l_allocation_in_rec.project_id ,
2752 p_task_id => l_allocation_in_rec.task_id ,
2753 p_start_date_active => l_allocation_in_rec.start_date_active
2754 );
2755
2756 END IF;
2757 */
2758 --
2759 -- Call OKE_FUNDINGALLOCATION_PVT.update_row
2760 --
2761
2762 --dbms_output.put_line('calling oke_fundingallocation_pvt.update_row');
2763 --oke_debug.debug('calling oke_fundingallocation_pvt.update_row');
2764
2765 OKE_FUNDINGALLOCATION_PVT.update_row(X_Fund_Allocation_Id => l_allocation_in_rec.fund_allocation_id ,
2766 X_Amount => l_allocation_in_rec.amount ,
2767 X_Previous_Amount => l_previous_amount ,
2768 X_Object_id => l_allocation_in_rec.object_id ,
2769 X_k_line_id => l_allocation_in_rec.k_line_id ,
2770 X_project_id => l_allocation_in_rec.project_id ,
2771 x_task_id => l_allocation_in_rec.task_id ,
2772 X_Hard_Limit => l_allocation_in_rec.hard_limit ,
2773 X_Fund_Type => upper(l_allocation_in_rec.fund_type) ,
2774 X_Funding_Status => upper(l_allocation_in_rec.funding_status) ,
2775 X_Fiscal_Year => l_allocation_in_rec.fiscal_year ,
2776 X_Reference1 => l_allocation_in_rec.reference1 ,
2777 X_Reference2 => l_allocation_in_rec.reference2 ,
2778 X_Reference3 => l_allocation_in_rec.reference3 ,
2779 X_Pa_Conversion_Type => l_allocation_in_rec.pa_conversion_type ,
2780 X_Pa_Conversion_Date => l_allocation_in_rec.pa_conversion_date ,
2781 X_Pa_Conversion_Rate => l_allocation_in_rec.pa_conversion_rate ,
2782 X_Insert_Update_Flag => 'Y' ,
2783 X_Start_Date_Active => l_allocation_in_rec.start_date_active ,
2784 X_End_Date_Active => l_allocation_in_rec.end_date_active ,
2785 X_Last_Update_Date => sysdate ,
2786 X_Last_Updated_By => L_USERID ,
2787 X_Last_Update_Login => L_LOGINID ,
2788 --X_Attribute_Category => upper(l_allocation_in_rec.oke_attribute_category) ,
2789 X_Attribute_Category => l_allocation_in_rec.oke_attribute_category ,
2790 X_Attribute1 => l_allocation_in_rec.oke_attribute1 ,
2791 X_Attribute2 => l_allocation_in_rec.oke_attribute2 ,
2792 X_Attribute3 => l_allocation_in_rec.oke_attribute3 ,
2793 X_Attribute4 => l_allocation_in_rec.oke_attribute4 ,
2794 X_Attribute5 => l_allocation_in_rec.oke_attribute5 ,
2795 X_Attribute6 => l_allocation_in_rec.oke_attribute6 ,
2796 X_Attribute7 => l_allocation_in_rec.oke_attribute7 ,
2797 X_Attribute8 => l_allocation_in_rec.oke_attribute8 ,
2798 X_Attribute9 => l_allocation_in_rec.oke_attribute9 ,
2799 X_Attribute10 => l_allocation_in_rec.oke_attribute10 ,
2800 X_Attribute11 => l_allocation_in_rec.oke_attribute11 ,
2801 X_Attribute12 => l_allocation_in_rec.oke_attribute12 ,
2802 X_Attribute13 => l_allocation_in_rec.oke_attribute13 ,
2803 X_Attribute14 => l_allocation_in_rec.oke_attribute14 ,
2804 X_Attribute15 => l_allocation_in_rec.oke_attribute15 ,
2805 X_Revenue_Hard_Limit => l_allocation_in_rec.revenue_hard_limit ,
2806 X_Funding_Category => upper(l_allocation_in_rec.funding_category) ,
2807 --X_PA_Attribute_Category => upper(l_allocation_in_rec.pa_attribute_category) ,
2808 X_PA_Attribute_Category => l_allocation_in_rec.pa_attribute_category ,
2809 X_PA_Attribute1 => l_allocation_in_rec.pa_attribute1 ,
2810 X_PA_Attribute2 => l_allocation_in_rec.pa_attribute2 ,
2811 X_PA_Attribute3 => l_allocation_in_rec.pa_attribute3 ,
2812 X_PA_Attribute4 => l_allocation_in_rec.pa_attribute4 ,
2813 X_PA_Attribute5 => l_allocation_in_rec.pa_attribute5 ,
2814 X_PA_Attribute6 => l_allocation_in_rec.pa_attribute6 ,
2815 X_PA_Attribute7 => l_allocation_in_rec.pa_attribute7 ,
2816 X_PA_Attribute8 => l_allocation_in_rec.pa_attribute8 ,
2817 X_PA_Attribute9 => l_allocation_in_rec.pa_attribute9 ,
2818 X_PA_Attribute10 => l_allocation_in_rec.pa_attribute10
2819 );
2820
2821 IF FND_API.to_boolean(p_commit) THEN
2822
2823 COMMIT WORK;
2824
2825 END IF;
2826
2827 --dbms_output.put_line('finished oke_allocation_pvt.update_allocation w/ ' || p_return_status);
2828 --oke_debug.debug('finished oke_allocation_pvt.update_allocation w/ ' || p_return_status);
2829
2830 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
2831 x_msg_data => p_msg_data
2832 );
2833
2834 EXCEPTION
2835 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
2836 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_ERROR;
2837 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2838 p_pkg_name => G_PKG_NAME ,
2839 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
2840 x_msg_count => p_msg_count ,
2841 x_msg_data => p_msg_data ,
2842 p_api_type => '_PVT'
2843 );
2844
2845 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2846 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2847 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2848 p_pkg_name => G_PKG_NAME ,
2849 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
2850 x_msg_count => p_msg_count ,
2851 x_msg_data => p_msg_data ,
2852 p_api_type => '_PVT'
2853 );
2854
2855 WHEN OTHERS THEN
2856 p_allocation_out_rec.return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
2857 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
2858 p_pkg_name => G_PKG_NAME ,
2859 p_exc_name => 'OTHERS' ,
2860 x_msg_count => p_msg_count ,
2861 x_msg_data => p_msg_data ,
2862 p_api_type => '_PVT'
2863 );
2864
2865 END update_allocation;
2866
2867
2868 --
2869 -- Procedure delete_allocation
2870 --
2871 -- Description: This procedure is used to delete record in OKE_K_FUND_ALLOCATIONS table
2872 --
2873 -- Calling subprograms: OKE_FUNDINGALLOCATION_PVT.delete_row
2874 -- OKE_API.start_activity
2875 -- OKE_API.end_activity
2876 -- validate_fund_allocation_id
2877 -- PA_AGREEMENT_PUB.delete_funding
2878 --
2879
2880 PROCEDURE delete_allocation(p_api_version IN NUMBER ,
2881 p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
2882 p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
2883 p_msg_count OUT NOCOPY NUMBER ,
2884 p_msg_data OUT NOCOPY VARCHAR2 ,
2885 p_fund_allocation_id IN NUMBER ,
2886 -- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
2887 p_return_status OUT NOCOPY VARCHAR2
2888 ) is
2889
2890 l_api_name CONSTANT VARCHAR2(30) := 'delete_allocation';
2891 l_return_status VARCHAR2(1);
2892 -- l_agreement_flag VARCHAR2(1);
2893 l_rowid VARCHAR2(30);
2894 l_version NUMBER;
2895 -- l_funding_reference VARCHAR2(25);
2896 -- i NUMBER := 1;
2897 -- l_org_id NUMBER;
2898 l_created_ver NUMBER;
2899 l_current_ver NUMBER;
2900 l_org_id_vc VARCHAR2(10);
2901
2902 /*
2903 cursor c_org is
2904 select org_id
2905 from pa_projects_all p,
2906 oke_k_fund_allocations f
2907 where f.project_id = p.project_id
2908 and fund_allocation_id = p_fund_allocation_id;
2909 */
2910
2911 cursor c_ver is
2912 select major_version + 1,
2913 nvl(created_in_version, -99)
2914 from okc_k_vers_numbers b,
2915 oke_k_fund_allocations a
2916 where b.chr_id = a.object_id
2917 and a.fund_allocation_id = p_fund_allocation_id;
2918
2919 cursor c_proj_funding(x_length number) is
2920 select project_funding_id, org_id, pm_funding_reference
2921 from pa_project_fundings p,
2922 pa_agreements_all a
2923 where p.pm_product_code = G_PRODUCT_CODE
2924 and a.agreement_id = p.agreement_id
2925 and substr(pm_funding_reference, 1, x_length + 1) = p_fund_allocation_id || '.';
2926
2927 BEGIN
2928
2929 --dbms_output.put_line('entering oke_allocation_pvt.delete_allocation');
2930 --oke_debug.debug('entering oke_allocation_pvt.delete_allocation');
2931
2932 p_return_status := OKE_API.G_RET_STS_SUCCESS;
2933
2934 l_return_status := OKE_API.START_ACTIVITY(p_api_name => l_api_name ,
2935 p_pkg_name => G_PKG_NAME ,
2936 p_init_msg_list => p_init_msg_list ,
2937 l_api_version => G_API_VERSION_NUMBER ,
2938 p_api_version => p_api_version ,
2939 p_api_type => '_PVT' ,
2940 x_return_status => p_return_status
2941 );
2942
2943 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2944
2945 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2946
2947 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2948
2949 RAISE OKE_API.G_EXCEPTION_ERROR;
2950
2951 END IF;
2952
2953 --
2954 -- Validate if it is a valid fund_allocation_id
2955 --
2956
2957 validate_fund_allocation_id(p_fund_allocation_id => p_fund_allocation_id ,
2958 p_rowid => l_rowid ,
2959 p_version => l_version
2960 );
2961
2962 --
2963 -- 7/15/02
2964 -- Validate if the line can be deleted or not
2965 --
2966 OPEN c_ver;
2967 FETCH c_ver into l_current_ver, l_created_ver;
2968 CLOSE c_ver;
2969
2970 IF (l_current_ver <> l_created_ver) THEN
2971
2972 OKE_API.set_message(p_app_name => G_APP_NAME ,
2973 p_msg_name => 'OKE_VER_NO_ALLOCATION_DELETE'
2974 );
2975
2976 RAISE OKE_API.G_EXCEPTION_ERROR;
2977
2978 END IF;
2979 --
2980 -- End 7/15/02
2981 --
2982
2983 --
2984 -- Call OKE_FUNDINGALLOCATION_PVT.delete_row to delete the row
2985 --
2986
2987 OKE_FUNDINGALLOCATION_PVT.delete_row(x_rowid => l_rowid);
2988
2989 --
2990 -- Delete project_funding lines if they exist in PA;
2991 --
2992 /*
2993 IF l_version <> 0 THEN
2994
2995 OPEN c_org;
2996 FETCH c_org into l_org_id;
2997 CLOSE c_org;
2998
2999 END IF;
3000
3001 FOR i in 1..l_version LOOP
3002
3003 l_funding_reference := p_fund_allocation_id || '.' || i;
3004 */
3005 l_org_id_vc := oke_utils.org_id;
3006
3007 FOR l_project_funding IN c_proj_funding(length(p_fund_allocation_id)) LOOP
3008
3009 -- fnd_client_info.set_org_context(l_project_funding.org_id);
3010 mo_global.set_policy_context('S',l_project_funding.org_id);
3011
3012 PA_AGREEMENT_PUB.delete_funding(p_api_version_number => p_api_version ,
3013 p_commit => OKE_API.G_FALSE ,
3014 p_init_msg_list => OKE_API.G_FALSE ,
3015 p_msg_count => p_msg_count ,
3016 p_msg_data => p_msg_data ,
3017 p_return_status => p_return_status ,
3018 p_pm_product_code => G_PRODUCT_CODE ,
3019 p_pm_funding_reference => l_project_funding.pm_funding_reference ,
3020 p_funding_id => l_project_funding.project_funding_id ,
3021 p_check_y_n => 'Y'
3022 );
3023
3024 IF (p_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3025
3026 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3027
3028 ELSIF (p_return_status = OKE_API.G_RET_STS_ERROR) THEN
3029
3030 RAISE OKE_API.G_EXCEPTION_ERROR;
3031
3032 END IF;
3033
3034 END LOOP;
3035 mo_global.set_policy_context('S',to_number(l_org_id_vc));
3036
3037 --dbms_output.put_line('finished oke_allocation_pvt.delete_allocation w/ ' || p_return_status);
3038 --oke_debug.debug('finished oke_allocation_pvt.delete_allocation w/ ' || p_return_status);
3039
3040 IF FND_API.to_boolean(p_commit) THEN
3041
3042 COMMIT WORK;
3043
3044 END IF;
3045
3046 OKE_API.END_ACTIVITY(x_msg_count => p_msg_count ,
3047 x_msg_data => p_msg_data
3048 );
3049
3050 EXCEPTION
3051 WHEN OKE_API.G_EXCEPTION_ERROR OR G_EXCEPTION_HALT_VALIDATION THEN
3052 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3053 p_pkg_name => G_PKG_NAME ,
3054 p_exc_name => 'OKE_API.G_RET_STS_ERROR' ,
3055 x_msg_count => p_msg_count ,
3056 x_msg_data => p_msg_data ,
3057 p_api_type => '_PVT'
3058 );
3059
3060 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3061 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3062 p_pkg_name => G_PKG_NAME ,
3063 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR' ,
3064 x_msg_count => p_msg_count ,
3065 x_msg_data => p_msg_data ,
3066 p_api_type => '_PVT'
3067 );
3068
3069 WHEN OTHERS THEN
3070 p_return_status := OKE_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name ,
3071 p_pkg_name => G_PKG_NAME ,
3072 p_exc_name => 'OTHERS' ,
3073 x_msg_count => p_msg_count ,
3074 x_msg_data => p_msg_data ,
3075 p_api_type => '_PVT'
3076 );
3077
3078 END delete_allocation;
3079
3080
3081
3082 --
3083 -- Function: get_allocation_tbl
3084 --
3085 -- Description: This function is used to return a initialized ALLOCATION_IN_TBL_TYPE
3086 --
3087 -- Calling subprograms: N/A
3088 --
3089
3090 FUNCTION get_allocation_tbl RETURN ALLOCATION_IN_TBL_TYPE is
3091
3092 allocation_in_tbl ALLOCATION_IN_TBL_TYPE;
3093
3094 BEGIN
3095
3096 return allocation_in_tbl;
3097
3098 END get_allocation_tbl;
3099
3100
3101 end OKE_ALLOCATION_PVT;