[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_UTILS_PUB
Source
1 PACKAGE BODY OKE_DELIVERABLE_UTILS_PUB AS
2 /* $Header: OKEPDUTB.pls 120.1 2006/09/08 23:00:09 sasethi noship $ */
3
4 --
5 -- Private Global Variables
6 --
7 G_Yes CONSTANT VARCHAR2(80) := 'Y';
8 G_No CONSTANT VARCHAR2(80) := 'N';
9 G_PKG_Name CONSTANT VARCHAR2(30) := 'OKE_DELIVERABLE_UTILS_PUB';
10 G_API_Type CONSTANT VARCHAR2(30) := 'PROCEDURE';
11
12
13 --
14 -- Name : MDS_Initiated_Yn
15 -- Pre-reqs : N/A
16 -- Function : This function returns result to indicate whether certain
17 -- Action has been executed
18 --
19 --
20 -- Parameters :
21 -- IN : P_Action_ID Deliverable action ID
22 -- OUT : None
23 --
24 -- Returns : VARCHAR2
25 --
26
27 FUNCTION MDS_Initiated_Yn ( P_Action_ID NUMBER)
28 RETURN VARCHAR2 IS
29
30 CURSOR c IS
31 SELECT reference2
32 FROM oke_deliverable_actions
33 WHERE pa_action_id = p_action_id
34 AND action_type = 'WSH';
35
36 L_Ref_2 NUMBER;
37
38 BEGIN
39
40 IF P_Action_ID > 0 THEN
41 OPEN c;
42 FETCH c INTO L_Ref_2;
43 CLOSE c;
44
45 IF L_Ref_2 > 0 THEN
46 RETURN G_Yes;
47 ELSE
48 RETURN G_No;
49 END IF;
50 ELSE
51 RETURN ( NULL ) ;
52 END IF;
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 RETURN G_No;
57 WHEN OTHERS THEN
58 RETURN ( NULL );
59
60 END MDS_Initiated_Yn;
61
62 --
63 -- Name : WSH_Initiated_Yn
64 -- Pre-reqs : N/A
65 -- Function : This function returns result to indicate whether shipping
66 -- Action has been executed
67 --
68 --
69 -- Parameters :
70 -- IN : P_Action_ID Deliverable action ID
71 -- OUT : None
72 --
73 -- Returns : VARCHAR2
74 --
75
76 FUNCTION WSH_Initiated_Yn ( P_Action_ID NUMBER)
77 RETURN VARCHAR2 IS
78
79 CURSOR c IS
80 SELECT reference1
81 FROM oke_deliverable_actions
82 WHERE pa_action_id = p_action_id
83 AND action_type = 'WSH';
84
85 L_Ref_1 NUMBER;
86
87 BEGIN
88
89 IF P_Action_ID > 0 THEN
90 OPEN c;
91 FETCH c INTO L_Ref_1;
92 CLOSE c;
93
94 IF L_Ref_1 > 0 THEN
95 RETURN G_Yes;
96 ELSE
97 RETURN G_No;
98 END IF;
99 ELSE
100 RETURN ( NULL ) ;
101 END IF;
102
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 RETURN G_No;
106 WHEN OTHERS THEN
107 RETURN ( NULL );
108
109 END Wsh_Initiated_Yn;
110
114 -- Function : This function returns result to indicate whether procure
111 --
112 -- Name : REQ_Initiated_Yn
113 -- Pre-reqs : N/A
115 -- Action has been executed
116 --
117 --
118 -- Parameters :
119 -- IN : P_Action_ID Deliverable action ID
120 -- OUT : None
121 --
122 -- Returns : VARCHAR2
123 --
124
125 FUNCTION REQ_Initiated_Yn ( P_Action_ID NUMBER)
126 RETURN VARCHAR2 IS
127
128 L_Ref_1 NUMBER;
129 L_Action_ID NUMBER;
130 L_Project_ID NUMBER;
131 L_Flag VARCHAR2(1) := G_NO;
132
133 CURSOR c IS
134 SELECT a.reference1, a.action_id, d.project_id
135 FROM oke_deliverable_actions a, oke_deliverables_b d
136 WHERE a.pa_action_id = p_action_id AND action_type = 'REQ'
137 AND d.deliverable_id = a.deliverable_id
138 ;
139
140 CURSOR d IS
141 SELECT Decode(process_flag, 'ERROR', G_NO, G_YES)
142 FROM po_requisitions_interface_all
143 WHERE oke_contract_deliverable_id = l_action_id AND batch_id = l_ref_1
144 ;
145
146 CURSOR r IS
147 SELECT G_YES
148 FROM oke_deliverable_requisitions_v
149 WHERE ACTION_ID = l_action_id and project_id = l_project_id
150 ;
151
152 BEGIN
153
154 IF P_Action_ID > 0 THEN
155 OPEN c;
156 FETCH c INTO L_Ref_1, L_Action_ID, l_project_id;
157 CLOSE c;
158
159 IF L_Ref_1 > 0 THEN
160 OPEN d;
161 FETCH d INTO L_Flag;
162 IF d%NOTFOUND THEN
163 OPEN r;
164 FETCH r INTO L_Flag;
165 CLOSE r;
166 END IF;
167 CLOSE d;
168 END IF;
169 RETURN l_flag;
170 ELSE
171 RETURN ( NULL ) ;
172 END IF;
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 RETURN G_No;
177 WHEN OTHERS THEN
178 RETURN ( NULL );
179
180 END Req_Initiated_Yn;
181
182 --
183 -- Name : Item_Defined_Yn
184 -- Pre-reqs : N/A
185 -- Function : This function returns result to indicate whether item
186 -- has been defined for the action
187 --
188 --
189 -- Parameters :
190 -- IN : P_Action_ID Deliverable action ID
191 -- OUT : None
192 --
193 -- Returns : VARCHAR2
194 --
195
196 FUNCTION Item_Defined_Yn ( P_Deliverable_ID NUMBER)
197 RETURN VARCHAR2 IS
198
199 L_Dummy NUMBER;
200
201 CURSOR c IS
202 SELECT 1
203 FROM oke_deliverables_b
204 WHERE source_code = 'PA'
205 AND source_deliverable_id = p_deliverable_id
206 AND item_id > 0;
207
208 BEGIN
209 OPEN c;
210 FETCH c INTO L_Dummy;
211 IF c%NOTFOUND THEN
212 CLOSE c;
213 RETURN 'N';
214 END IF;
215
216 CLOSE c;
217 RETURN 'Y';
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 RETURN 'N';
222
223 END Item_Defined_Yn;
224
225
226 --
227 -- Name : Ready_To_Ship_Yn
228 -- Pre-reqs : N/A
229 -- Function : This function returns result to indicate whether ready_to_ship
230 -- has been checked for the action
231 --
232 --
233 -- Parameters :
234 -- IN : P_Action_ID Deliverable action ID
235 -- OUT : None
236 --
237 -- Returns : VARCHAR2
238 --
239
240 FUNCTION Ready_To_Ship_Yn ( P_Action_ID NUMBER)
241 RETURN VARCHAR2 IS
242
243 CURSOR c IS
244 SELECT NVL(ready_flag, 'N')
245 FROM oke_deliverable_actions
246 WHERE pa_action_id = p_action_id
247 AND action_type = 'WSH';
248
249 L_Value VARCHAR2(1);
250
251 BEGIN
252
253 IF P_Action_ID > 0 THEN
254 OPEN C;
255 FETCH C INTO L_Value;
256 CLOSE C;
257
258 RETURN L_Value;
259 ELSE
260 RETURN ( NULL );
261 END IF;
262
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265 RETURN G_No;
266 WHEN OTHERS THEN
267 RETURN ( NULL );
268
269 END Ready_To_Ship_Yn;
270
271 --
272 -- Name : Ready_To_Procure_Yn
273 -- Pre-reqs : N/A
274 -- Function : This function returns result to indicate whether ready_to_procure
275 -- has been checked for the action
276 --
277 --
278 -- Parameters :
279 -- IN : P_Action_ID Deliverable action ID
280 -- OUT : None
281 --
282 -- Returns : VARCHAR2
283 --
284
285 FUNCTION Ready_To_Procure_Yn ( P_Action_ID NUMBER)
286 RETURN VARCHAR2 IS
287 CURSOR c IS
288 SELECT NVL(ready_flag, 'N'), reference1
289 FROM oke_deliverable_actions
290 WHERE pa_action_id = p_action_id
291 AND action_type = 'REQ';
292
293 CURSOR d IS
294 SELECT 1
295 FROM dual
296 WHERE EXISTS ( SELECT 1
297 FROM po_requisitions_interface_all
298 WHERE oke_contract_deliverable_id = p_action_id
299 AND process_flag = 'ERROR');
300
301 L_Value VARCHAR2(1);
302 L_Flag NUMBER;
303 L_Ref NUMBER;
304
305 BEGIN
306
307 IF P_Action_ID > 0 THEN
308 OPEN C;
309 FETCH C INTO L_Value, L_Ref;
310 CLOSE C;
311
312 IF L_Value = 'Y' AND L_Ref > 0 THEN
316
313 OPEN d;
314 FETCH d INTO L_Flag;
315 CLOSE d;
317 IF L_Flag <> 1 THEN
318 L_Value := 'N';
319 END IF;
320 END IF;
321
322 RETURN L_Value;
323 ELSE
324 RETURN ( NULL );
325 END IF;
326
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 RETURN G_No;
330 WHEN OTHERS THEN
331 RETURN ( NULL );
332
333 END Ready_To_Procure_Yn;
334
335
336 --
337 -- Name : Item_Shippable_Yn
338 -- Pre-reqs : N/A
339 -- Function : This function returns result to indicate whether item
340 -- is shippable
341 --
342 --
343 -- Parameters :
344 -- IN : P_Deliverable_ID Deliverable ID
345 -- OUT : None
346 --
347 -- Returns : VARCHAR2
348 --
349
350 FUNCTION Item_Shippable_Yn ( P_Deliverable_ID NUMBER)
351 RETURN VARCHAR2 IS
352
353 CURSOR c IS
354 SELECT NVL(shippable_item_flag, 'N')
355 FROM oke_system_items_v
356 WHERE id1 = (
357 SELECT item_id
358 FROM oke_deliverables_b
359 WHERE source_code = 'PA'
360 AND source_deliverable_id = p_deliverable_id );
361
362 L_Value VARCHAR2(1);
363
364 BEGIN
365
366 IF P_Deliverable_ID > 0 THEN
367 OPEN C;
368 FETCH C INTO L_Value;
369 CLOSE C;
370
371 RETURN L_Value;
372 ELSE
373 RETURN ( NULL );
374 END IF;
375
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 RETURN G_No;
379 WHEN OTHERS THEN
380 RETURN ( NULL );
381
382 END Item_Shippable_Yn;
383
384
385 --
386 -- Name : Item_Billable_Yn
387 -- Pre-reqs : N/A
388 -- Function : This function returns result to indicate whether item
389 -- is billable
390 --
391 --
392 -- Parameters :
393 -- IN : P_Deliverable_ID Deliverable ID
394 -- OUT : None
395 --
396 -- Returns : VARCHAR2
397 --
398
399 FUNCTION Item_Billable_Yn ( P_Deliverable_ID NUMBER)
400 RETURN VARCHAR2 IS
401
402 CURSOR c IS
403 SELECT NVL(invoiceable_item_flag, 'N')
404 FROM oke_system_items_v
405 WHERE id1 = (
406 SELECT item_id
407 FROM oke_deliverables_b
408 WHERE source_code = 'PA'
409 AND source_deliverable_id = p_deliverable_id );
410
411 L_Value VARCHAR2(1);
412
413 BEGIN
414
415 IF P_Deliverable_ID > 0 THEN
416 OPEN C;
417 FETCH C INTO L_Value;
418 CLOSE C;
419
420 RETURN L_Value;
421 ELSE
422 RETURN ( NULL );
423 END IF;
424
425 EXCEPTION
426 WHEN NO_DATA_FOUND THEN
427 RETURN G_No;
428 WHEN OTHERS THEN
429 RETURN ( NULL );
430
431 END Item_Billable_Yn;
432
433 --
434 -- Name : Item_Purchasable_Yn
435 -- Pre-reqs : N/A
436 -- Function : This function returns result to indicate whether item
437 -- is purchasable
438 --
439 --
440 -- Parameters :
441 -- IN : P_Deliverable_ID Deliverable ID
442 -- OUT : None
443 --
444 -- Returns : VARCHAR2
445 --
446
447 FUNCTION Item_Purchasable_Yn ( P_Deliverable_ID NUMBER)
448 RETURN VARCHAR2 IS
449
450 CURSOR c IS
451 SELECT NVL(purchasing_enabled_flag, 'N')
452 FROM oke_system_items_v
453 WHERE id1 = (
454 SELECT item_id
455 FROM oke_deliverables_b
456 WHERE source_code = 'PA'
457 AND source_deliverable_id = p_deliverable_id );
458
459 L_Value VARCHAR2(1);
460
461 BEGIN
462
463 IF P_Deliverable_ID > 0 THEN
464 OPEN C;
465 FETCH C INTO L_Value;
466 CLOSE C;
467
468 RETURN L_Value;
469 ELSE
470 RETURN ( NULL );
471 END IF;
472
473 EXCEPTION
474 WHEN NO_DATA_FOUND THEN
475 RETURN G_No;
476 WHEN OTHERS THEN
477 RETURN ( NULL );
478
479 END Item_purchasable_Yn;
480
481 FUNCTION Action_Deletable_Yn ( P_Action_ID NUMBER )
482 RETURN VARCHAR2 IS
483
484 Dummy NUMBER;
485
486 BEGIN
487
488 SELECT 1
489 INTO dummy
490 FROM oke_deliverable_actions
491 WHERE pa_action_id = p_action_id
492 AND reference1 > 0
493 AND NOT EXISTS ( SELECT 1
494 FROM po_requisitions_interface_all
495 WHERE oke_contract_deliverable_id = p_action_id
496 AND process_flag = 'ERROR' );
497
498 RETURN G_No;
499
500 EXCEPTION
501 WHEN NO_DATA_FOUND THEN
502 RETURN G_Yes;
503 WHEN OTHERS THEN
504 RETURN G_No;
505 END Action_Deletable_Yn;
506
507 PROCEDURE Copy_Item ( P_Source_Project_ID NUMBER
508 , P_Target_Project_ID NUMBER
509 , P_Source_Deliverable_ID NUMBER
510 , P_Target_Deliverable_ID NUMBER
511 , P_Target_Deliverable_Number VARCHAR2
512 , P_Copy_Item_Details_Flag VARCHAR2
513 , X_Return_Status OUT NOCOPY VARCHAR2
514 , X_Msg_Count OUT NOCOPY NUMBER
515 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
516
517 L_API_Name CONSTANT VARCHAR2(30) := 'COPY_ITEM';
518 L_Init_Msg_List CONSTANT VARCHAR2(1) := 'T';
519 L_Return_Status VARCHAR2(1);
523 L_Deliverable_ID NUMBER;
520 L_ID NUMBER;
521 L_Name VARCHAR2(120);
522 L_Number VARCHAR2(120);
524 L_Deliverable_Description oke_deliverables_tl.description%TYPE;
525 L_Deliverable_Comments oke_deliverables_tl.comments%TYPE;
526
527 CURSOR c IS
528 SELECT deliverable_id, description, comments
529 FROM oke_deliverables_vl
530 WHERE source_code = 'PA'
531 AND source_header_id = p_source_project_id
532 AND source_deliverable_id = p_source_deliverable_id;
533
534
535 BEGIN
536
537 L_Return_Status := OKE_API.Start_Activity (
538 P_Api_Name => L_Api_Name,
539 P_Init_Msg_List => L_Init_Msg_List,
540 P_API_Type => G_API_Type,
541 X_Return_Status => X_Return_Status );
542 IF ( L_Return_Status = OKE_API.G_RET_STS_ERROR ) THEN
543 RAISE OKE_API.G_Exception_Error;
544 ELSIF ( L_Return_Status = OKE_API.G_RET_STS_UNEXP_ERROR ) THEN
545 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
546 END IF;
547
548 IF P_Source_Project_ID IS NULL
549 OR P_Target_Project_ID IS NULL
550 OR P_Source_Deliverable_ID IS NULL
551 OR P_Target_Deliverable_ID IS NULL THEN
552
553 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_REQ_PARAMETER');
554 FND_MSG_PUB.Add;
555 RAISE OKE_API.G_Exception_Error;
556
557 END IF;
558
559 SELECT oke_k_deliverables_s.nextval
560 INTO l_id
561 FROM DUAL;
562
563 OPEN c;
564 FETCH c INTO L_Deliverable_ID, L_Deliverable_Description, L_Deliverable_Comments;
565 CLOSE c;
566
567 IF NVL( P_Copy_Item_Details_Flag, 'N' ) = 'Y' THEN
568
569 INSERT INTO oke_deliverables_b (
570 deliverable_id
571 , creation_date
572 , created_by
573 , last_updated_by
574 , last_update_date
575 , last_update_login
576 , deliverable_number
577 , source_code
578 , source_header_id
579 , source_line_id
580 , source_deliverable_id
581 , project_id
582 , delivery_date
583 , item_id
584 , currency_code
585 , inventory_org_id
586 , unit_price
587 , uom_code
588 , quantity
589 , unit_number )
590 SELECT l_id
591 , sysdate
592 , fnd_global.user_id
593 , fnd_global.user_id
594 , sysdate
595 , fnd_global.login_id
596 , p_target_deliverable_number
597 , 'PA'
598 , p_target_project_id
599 , null
600 , p_target_deliverable_id
601 , p_target_project_id
602 , delivery_date
603 , item_id
604 , currency_code
605 , inventory_org_id
606 , unit_price
607 , uom_code
608 , quantity
609 , unit_number
610 FROM oke_deliverables_b
611 WHERE source_code = 'PA'
612 AND source_header_id = p_source_project_id
613 AND source_deliverable_id = p_source_deliverable_id;
614
615 ELSE
616
617 INSERT INTO oke_deliverables_b (
618 deliverable_id
619 , creation_date
620 , created_by
621 , last_updated_by
622 , last_update_date
623 , last_update_login
624 , deliverable_number
625 , source_code
626 , source_header_id
627 , source_line_id
628 , source_deliverable_id
629 , project_id
630 , delivery_date)
631 SELECT l_id
632 , sysdate
633 , fnd_global.user_id
634 , fnd_global.user_id
635 , sysdate
636 , fnd_global.login_id
637 , p_target_deliverable_number
638 , 'PA'
639 , p_target_project_id
640 , null
641 , p_target_deliverable_id
642 , p_target_project_id
643 , delivery_date
644 FROM oke_deliverables_b
645 WHERE source_code = 'PA'
646 AND source_header_id = p_source_project_id
647 AND source_deliverable_id = p_source_deliverable_id;
648 END IF;
649
650 INSERT INTO oke_deliverables_tl (
651 deliverable_id
652 , language
653 , creation_date
654 , created_by
655 , last_update_date
656 , last_updated_by
657 , last_update_login
658 , source_lang
659 , description
660 , comments )
661 SELECT l_id
662 , l.language_code
663 , sysdate
664 , fnd_global.user_id
665 , sysdate
666 , fnd_global.user_id
667 , fnd_global.login_id
668 , oke_utils.get_userenv_lang
669 , L_Deliverable_Description
670 , L_Deliverable_Comments
671 FROM fnd_languages l
672 WHERE L.INSTALLED_FLAG in ('I', 'B')
673 AND not exists
674 (select NULL
675 from OKE_DELIVERABLES_TL T
676 where T.DELIVERABLE_ID = l_id
677 and T.LANGUAGE = L.LANGUAGE_CODE);
678
679
680 OKE_API.End_Activity ( X_Msg_Count => X_Msg_Count
681 , X_Msg_Data => X_Msg_Data );
682
683 EXCEPTION
684 when OKE_API.G_EXCEPTION_ERROR then
685 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
686 p_api_name => l_api_name,
687 p_pkg_name => g_pkg_name,
688 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
689 x_msg_count => x_msg_count,
690 x_msg_data => x_msg_data,
691 p_api_type => g_api_type);
692
693 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
694 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
695 p_api_name => l_api_name,
696 p_pkg_name => g_pkg_name,
697 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
698 x_msg_count => x_msg_count,
699 x_msg_data => x_msg_data,
700 p_api_type => g_api_type);
701
702 when OTHERS then
706 p_exc_name => 'OTHERS',
703 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
704 p_api_name => l_api_name,
705 p_pkg_name => g_pkg_name,
707 x_msg_count => x_msg_count,
708 x_msg_data => x_msg_data,
709 p_api_type => g_api_type);
710
711 END Copy_Item;
712
713 PROCEDURE Copy_Action ( P_Source_Project_ID NUMBER
714 , P_Target_Project_ID NUMBER
715 , P_Source_Deliverable_ID NUMBER
716 , P_Target_Deliverable_ID NUMBER
717 , P_Source_Action_ID NUMBER
718 , P_Target_Action_ID NUMBER
719 , P_Target_Action_Name VARCHAR2
720 , P_Target_Action_Date DATE
721 , X_Return_Status OUT NOCOPY VARCHAR2
722 , X_Msg_Count OUT NOCOPY NUMBER
723 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
724
725 L_API_Name CONSTANT VARCHAR2(30) := 'COPY_ACTION';
726 L_Init_Msg_List CONSTANT VARCHAR2(1) := 'T';
727 L_Return_Status VARCHAR2(1);
728 L_Deliverable_ID NUMBER;
729 l_currency_code VARCHAR2(30);
730 l_inventory_org_id NUMBER;
731 l_unit_price NUMBER;
732 l_uom_code VARCHAR2(30);
733 l_quantity NUMBER;
734 l_Item_Based_YN VARCHAR2(1);
735
736 CURSOR c IS
737 SELECT deliverable_id, currency_code, inventory_org_id,
738 unit_price, uom_code, quantity,
739 PA_DELIVERABLE_UTILS.IS_DLVR_ITEM_BASED(p_target_deliverable_id) Item_Based_YN
740 FROM oke_deliverables_b
741 WHERE source_code = 'PA'
742 AND source_header_id = p_target_project_id
743 AND source_deliverable_id = p_target_deliverable_id;
744
745 BEGIN
746
747 L_Return_Status := OKE_API.Start_Activity (
748 P_Api_Name => L_Api_Name,
749 P_Init_Msg_List => L_Init_Msg_List,
750 P_API_Type => G_API_Type,
751 X_Return_Status => X_Return_Status );
752 IF ( L_Return_Status = OKE_API.G_RET_STS_ERROR ) THEN
753 RAISE OKE_API.G_Exception_Error;
754 ELSIF ( L_Return_Status = OKE_API.G_RET_STS_UNEXP_ERROR ) THEN
755 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
756 END IF;
757
758 IF P_Source_Project_ID IS NULL
759 OR P_Target_Project_ID IS NULL
760 OR P_Source_Deliverable_ID IS NULL
761 OR P_Target_Deliverable_ID IS NULL
762 OR P_Source_Action_ID IS NULL
763 OR P_Target_Action_ID IS NULL THEN
764
765 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_REQ_PARAMETER');
766 FND_MSG_PUB.Add;
767 RAISE OKE_API.G_Exception_Error;
768
769 END IF;
770
771 OPEN c;
772 FETCH c INTO L_Deliverable_ID, l_currency_code, l_inventory_org_id,
773 l_unit_price, l_uom_code, l_quantity, l_Item_Based_YN;
774 CLOSE C;
775
776 INSERT INTO oke_deliverable_actions (
777 action_id
778 , creation_date
779 , created_by
780 , last_update_date
781 , last_updated_by
782 , last_update_login
783 , action_type
784 , action_name
785 , pa_action_id
786 , deliverable_id
787 , ship_to_org_id
788 , ship_to_location_id
789 , ship_from_org_id
790 , ship_from_location_id
791 , inspection_req_flag
792 , expected_date
793 , schedule_designator
794 , volume
795 , volume_uom_code
796 , weight
797 , weight_uom_code
798 , expenditure_organization_id
799 , expenditure_type
800 , expenditure_item_date
801 , destination_type_code
802 , rate_type
803 , rate_date
804 , exchange_rate
805 , requisition_line_type_id
806 , po_category_id
807 , unit_price
808 , currency_code
809 , quantity
810 , uom_code)
811 SELECT oke_k_deliverables_s.nextval
812 , sysdate
813 , fnd_global.user_id
814 , sysdate
815 , fnd_global.user_id
816 , fnd_global.login_id
817 , action_type
818 , p_target_action_name
819 , p_target_action_id
820 , l_deliverable_id
821
822 , Decode( action_type||','||l_Item_Based_YN,
823 'REQ,Y', l_inventory_org_id, ship_to_org_id ) ship_to_org_id
824 , Decode( action_type||','||l_Item_Based_YN||','||Nvl(l_inventory_org_id,''),
825 'REQ,Y,', NULL, ship_to_location_id ) ship_to_location_id
826
827 , Decode( action_type||','||l_Item_Based_YN,
828 'WSH,Y', l_inventory_org_id, ship_from_org_id ) ship_from_org_id
829 , Decode( action_type||','||l_Item_Based_YN||','||Nvl(l_inventory_org_id,''),
830 'WSH,Y,', NULL, ship_from_location_id ) ship_from_location_id
831
832 , inspection_req_flag
833 , p_target_action_date
834 , Decode( l_inventory_org_id, NULL, NULL, schedule_designator) schedule_designator
835 , volume
836 , volume_uom_code
837 , weight
838 , weight_uom_code
839 , expenditure_organization_id
840 , expenditure_type
841 , expenditure_item_date
842 , destination_type_code
843 , rate_type
844 , rate_date
845 , exchange_rate
846 , requisition_line_type_id
847 , po_category_id
848 , Decode( l_Item_Based_YN, 'Y', l_unit_price, unit_price)
849 , Decode( l_Item_Based_YN, 'Y', l_currency_code, currency_code)
850 , Decode( l_Item_Based_YN, 'Y', l_quantity, quantity)
851 , Decode( l_Item_Based_YN, 'Y', l_uom_code, uom_code)
852 FROM oke_deliverable_actions
853 WHERE pa_action_id = p_source_action_id;
854
855 OKE_API.End_Activity ( X_Msg_Count => X_Msg_Count
856 , X_Msg_Data => X_Msg_Data );
857
858 EXCEPTION
859 when OKE_API.G_EXCEPTION_ERROR then
860 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
861 p_api_name => l_api_name,
862 p_pkg_name => g_pkg_name,
863 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
867
864 x_msg_count => x_msg_count,
865 x_msg_data => x_msg_data,
866 p_api_type => g_api_type);
868 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
869 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
870 p_api_name => l_api_name,
871 p_pkg_name => g_pkg_name,
872 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
873 x_msg_count => x_msg_count,
874 x_msg_data => x_msg_data,
875 p_api_type => g_api_type);
876
877 when OTHERS then
878 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
879 p_api_name => l_api_name,
880 p_pkg_name => g_pkg_name,
881 p_exc_name => 'OTHERS',
882 x_msg_count => x_msg_count,
883 x_msg_data => x_msg_data,
884 p_api_type => g_api_type);
885
886 END Copy_Action;
887
888 PROCEDURE Delete_Action ( P_Action_ID NUMBER -- PA_ACTION_ID
889 , X_Return_Status OUT NOCOPY VARCHAR2
890 , X_Msg_Count OUT NOCOPY NUMBER
891 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
892
893 L_API_Name CONSTANT VARCHAR2(30) := 'DELETE_ACTION';
894 L_Init_Msg_List CONSTANT VARCHAR2(1) := 'T';
895 L_Return_Status VARCHAR2(1);
896
897 BEGIN
898
899 L_Return_Status := OKE_API.Start_Activity (
900 P_Api_Name => L_Api_Name,
901 P_Init_Msg_List => L_Init_Msg_List,
902 P_API_Type => G_API_Type,
903 X_Return_Status => X_Return_Status );
904 IF ( L_Return_Status = OKE_API.G_RET_STS_ERROR ) THEN
905 RAISE OKE_API.G_Exception_Error;
906 ELSIF ( L_Return_Status = OKE_API.G_RET_STS_UNEXP_ERROR ) THEN
907 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
908 END IF;
909
910 OKE_DELIVERABLE_ACTIONS_PKG.Delete_Action ( P_Action_ID );
911
912 X_Return_Status := L_Return_Status;
913 OKE_API.End_Activity ( X_Msg_Count => X_Msg_Count
914 , X_Msg_Data => X_Msg_Data );
915 EXCEPTION
916 when OKE_API.G_EXCEPTION_ERROR then
917 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
918 p_api_name => l_api_name,
919 p_pkg_name => g_pkg_name,
920 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
921 x_msg_count => x_msg_count,
922 x_msg_data => x_msg_data,
923 p_api_type => g_api_type);
924
925 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
926 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
927 p_api_name => l_api_name,
928 p_pkg_name => g_pkg_name,
929 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
930 x_msg_count => x_msg_count,
931 x_msg_data => x_msg_data,
932 p_api_type => g_api_type);
933
934 when OTHERS then
935 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
936 p_api_name => l_api_name,
937 p_pkg_name => g_pkg_name,
938 p_exc_name => 'OTHERS',
939 x_msg_count => x_msg_count,
940 x_msg_data => x_msg_data,
941 p_api_type => g_api_type);
942
943 END Delete_Action;
944
945 PROCEDURE Delete_Demand ( P_Action_ID NUMBER -- OKE_ACTION_ID
946 , X_Return_Status OUT NOCOPY VARCHAR2
947 , X_Msg_Count OUT NOCOPY NUMBER
948 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
949
950 L_API_Name CONSTANT VARCHAR2(30) := 'DELETE_DEMAND';
951 L_Init_Msg_List CONSTANT VARCHAR2(1) := 'T';
952 L_Return_Status VARCHAR2(1);
953
954 BEGIN
955
956 L_Return_Status := OKE_API.Start_Activity (
957 P_Api_Name => L_Api_Name,
958 P_Init_Msg_List => L_Init_Msg_List,
959 P_API_Type => G_API_Type,
960 X_Return_Status => X_Return_Status );
961 IF ( L_Return_Status = OKE_API.G_RET_STS_ERROR ) THEN
962 RAISE OKE_API.G_Exception_Error;
963 ELSIF ( L_Return_Status = OKE_API.G_RET_STS_UNEXP_ERROR ) THEN
964 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
965 END IF;
966
967 OKE_DELIVERABLE_ACTIONS_PKG.Delete_Row ( P_Action_ID );
968
969 X_Return_Status := L_Return_Status;
970 OKE_API.End_Activity ( X_Msg_Count => X_Msg_Count
971 , X_Msg_Data => X_Msg_Data );
972 EXCEPTION
973 when OKE_API.G_EXCEPTION_ERROR then
974 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
975 p_api_name => l_api_name,
976 p_pkg_name => g_pkg_name,
977 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_api_type => g_api_type);
981
982 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
983 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
984 p_api_name => l_api_name,
985 p_pkg_name => g_pkg_name,
986 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
987 x_msg_count => x_msg_count,
988 x_msg_data => x_msg_data,
989 p_api_type => g_api_type);
990
991 when OTHERS then
992 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
993 p_api_name => l_api_name,
994 p_pkg_name => g_pkg_name,
995 p_exc_name => 'OTHERS',
996 x_msg_count => x_msg_count,
997 x_msg_data => x_msg_data,
998 p_api_type => g_api_type);
999
1000 END Delete_Demand;
1001
1002 PROCEDURE Delete_Deliverable ( P_Deliverable_ID NUMBER
1003 , X_Return_Status OUT NOCOPY VARCHAR2
1004 , X_Msg_Count OUT NOCOPY NUMBER
1005 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
1006
1007 L_API_Name CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';
1008 L_Init_Msg_List CONSTANT VARCHAR2(1) := 'T';
1009 L_Return_Status VARCHAR2(1);
1010
1011 BEGIN
1012
1013 L_Return_Status := OKE_API.Start_Activity (
1014 P_Api_Name => L_Api_Name,
1015 P_Init_Msg_List => L_Init_Msg_List,
1016 P_API_Type => G_API_Type,
1017 X_Return_Status => X_Return_Status );
1018 IF ( L_Return_Status = OKE_API.G_RET_STS_ERROR ) THEN
1019 RAISE OKE_API.G_Exception_Error;
1020 ELSIF ( L_Return_Status = OKE_API.G_RET_STS_UNEXP_ERROR ) THEN
1024 OKE_DELIVERABLE_ACTIONS_PKG.Delete_Deliverable ( P_Deliverable_ID );
1021 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1022 END IF;
1023
1025
1026 X_Return_Status := L_Return_Status;
1027 OKE_API.End_Activity ( X_Msg_Count => X_Msg_Count
1028 , X_Msg_Data => X_Msg_Data );
1029 EXCEPTION
1030 when OKE_API.G_EXCEPTION_ERROR then
1031 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1032 p_api_name => l_api_name,
1033 p_pkg_name => g_pkg_name,
1034 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1035 x_msg_count => x_msg_count,
1036 x_msg_data => x_msg_data,
1037 p_api_type => g_api_type);
1038
1039 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1040 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1041 p_api_name => l_api_name,
1042 p_pkg_name => g_pkg_name,
1043 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1044 x_msg_count => x_msg_count,
1045 x_msg_data => x_msg_data,
1046 p_api_type => g_api_type);
1047
1048 when OTHERS then
1049 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1050 p_api_name => l_api_name,
1051 p_pkg_name => g_pkg_name,
1052 p_exc_name => 'OTHERS',
1053 x_msg_count => x_msg_count,
1054 x_msg_data => x_msg_data,
1055 p_api_type => g_api_type);
1056
1057 END Delete_Deliverable;
1058
1059 FUNCTION Unit_Price ( P_Item_ID NUMBER
1060 , P_Org_ID NUMBER ) RETURN NUMBER IS
1061
1062 L_Return_Status VARCHAR2(1);
1063 L_Msg_Count NUMBER;
1064 L_Msg_Data VARCHAR2(2000);
1065 L_Unit_Price NUMBER;
1066
1067 cursor c is
1068 select list_price_per_unit
1069 from mtl_system_items_b
1070 where organization_id = p_org_id
1071 and inventory_item_id = p_item_id;
1072
1073
1074 BEGIN
1075
1076 /* CST_ItemResourceCosts_GRP.Get_ItemCost (
1077 p_api_version => 1,
1078 p_init_msg_list => FND_API.G_FALSE,
1079 p_commit => FND_API.G_FALSE,
1080 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1081 x_return_status => L_Return_Status,
1082 x_msg_count => L_Msg_Count,
1083 x_msg_data => L_Msg_Data,
1084 p_item_id => P_Item_ID,
1085 p_organization_id => P_Org_ID,
1086 p_cost_source => 3,
1087 p_cost_type_id => 0,
1088 x_item_cost => L_Unit_Price ); */
1089
1090 open c;
1091 fetch c into l_unit_price;
1092 close c;
1093
1094 RETURN L_Unit_Price;
1095
1096 END;
1097
1098 FUNCTION Currency_Code ( P_Item_ID NUMBER, P_Org_ID NUMBER )
1099 RETURN VARCHAR2 IS
1100
1101 BEGIN
1102 RETURN 'USD';
1103
1104 END;
1105
1106 PROCEDURE Batch_MDS ( P_Project_ID NUMBER
1107 , P_Task_ID NUMBER
1108 , P_Init_Msg_List VARCHAR2
1109 , X_Return_Status OUT NOCOPY VARCHAR2
1110 , X_Msg_Count OUT NOCOPY NUMBER
1111 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
1112
1113 CURSOR c1 IS
1114 SELECT a.action_id
1115 FROM oke_deliverables_b b
1116 , oke_deliverable_actions a
1117 WHERE b.deliverable_id = a.deliverable_id
1118 AND a.reference2 is null
1119 AND b.item_id > 0
1120 AND b.quantity > 0
1121 AND b.inventory_org_id > 0
1122 AND b.uom_code IS NOT NULL
1123 AND a.schedule_designator IS NOT NULL
1124 AND a.action_type = 'WSH'
1125 AND a.expected_date >= sysdate;
1126
1127 c1rec c1%ROWTYPE;
1128
1129 CURSOR c2 IS
1130 SELECT a.action_id
1131 FROM oke_deliverables_b b
1132 , oke_deliverable_actions a
1133 WHERE b.deliverable_id = a.deliverable_id
1134 AND a.reference2 is null
1135 AND b.project_id = p_project_id
1136 AND b.item_id > 0
1137 AND b.quantity > 0
1138 AND b.inventory_org_id > 0
1139 AND b.uom_code IS NOT NULL
1140 AND a.schedule_designator IS NOT NULL
1141 AND a.action_type = 'WSH'
1142 AND a.expected_date >= sysdate;
1143
1144 c2rec c1%ROWTYPE;
1145
1146 CURSOR c3 IS
1147 SELECT a.action_id
1148 FROM oke_deliverables_b b
1149 , oke_deliverable_actions a
1150 WHERE b.deliverable_id = a.deliverable_id
1151 AND a.reference2 is null
1152 AND a.task_id = p_task_id
1153 AND b.item_id > 0
1154 AND b.quantity > 0
1155 AND b.inventory_org_id > 0
1156 AND b.uom_code IS NOT NULL
1157 AND a.schedule_designator IS NOT NULL
1158 AND a.action_type = 'WSH'
1159 AND a.expected_date >= sysdate;
1160
1161 c3rec c1%ROWTYPE;
1162
1163 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
1164 L_API_Version CONSTANT NUMBER := 1;
1165 L_API_Name CONSTANT VARCHAR2(30) := 'BATCH_DEMAND';
1166 L_ID NUMBER;
1167 L_Msg_Count NUMBER;
1168 L_Msg_Data VARCHAR2(2000);
1169
1170
1171 BEGIN
1172
1173 L_Return_Status := OKE_API.Start_Activity ( L_Api_Name
1174 , P_Init_Msg_List
1175 , '_PKG'
1176 , X_Return_Status );
1177
1178 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1179 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1180 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1181 RAISE OKE_API.G_EXCEPTION_ERROR;
1182 END IF;
1183
1184 IF P_Project_ID IS NULL THEN
1185 FOR c1rec IN c1 LOOP
1186
1187 OKE_DELIVERABLE_ACTIONS_PKG.Create_Demand ( c1rec.action_id
1188 , 'F'
1189 , L_ID
1190 , L_Return_Status
1191 , L_Msg_Count
1192 , L_Msg_Data );
1193 IF L_Return_Status <> 'S' THEN
1194 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1198 FOR c2rec IN c2 LOOP
1195 END IF;
1196 END LOOP;
1197 ELSIF P_Project_ID > 0 AND P_Task_ID IS NULL THEN
1199 OKE_DELIVERABLE_ACTIONS_PKG.Create_Demand ( c2rec.action_id
1200 , 'F'
1201 , L_ID
1202 , L_Return_Status
1203 , L_Msg_Count
1204 , L_Msg_Data );
1205 IF L_Return_Status <> 'S' THEN
1206 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1207 END IF;
1208 END LOOP;
1209 ELSIF P_Task_ID > 0 THEN
1210 FOR c3rec IN c3 LOOP
1211 OKE_DELIVERABLE_ACTIONS_PKG.Create_Demand ( c3rec.action_id
1212 , 'F'
1213 , L_ID
1214 , L_Return_Status
1215 , L_Msg_Count
1216 , L_Msg_Data );
1217 IF L_Return_Status <> 'S' THEN
1218 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1219 END IF;
1220 END LOOP;
1221 END IF;
1222
1223 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1224 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1225 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1226 RAISE OKE_API.G_EXCEPTION_ERROR;
1227 END IF;
1228
1229 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1230
1231 EXCEPTION
1232 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1233
1234 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1235 (
1236 l_api_name,
1237 G_PKG_NAME,
1238 'OKE_API.G_RET_STS_ERROR',
1239 x_msg_count,
1240 x_msg_data,
1241 '_PKG');
1242 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1243 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1244 (
1245 l_api_name,
1246 G_PKG_NAME,
1247 'OKE_API.G_RET_STS_UNEXP_ERROR',
1248 x_msg_count,
1249 x_msg_data,
1250 '_PKG');
1251
1252 WHEN OTHERS THEN
1253 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1254 (
1255 l_api_name,
1256 G_PKG_NAME,
1257 'OTHERS',
1258 x_msg_count,
1259 x_msg_data,
1260 '_PKG');
1261 END Batch_MDS;
1262
1263 PROCEDURE Batch_Req ( P_Project_ID NUMBER
1264 , P_Task_ID NUMBER
1265 , P_Init_Msg_List VARCHAR2
1266 , X_Return_Status OUT NOCOPY VARCHAR2
1267 , X_Msg_Count OUT NOCOPY NUMBER
1268 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
1269
1270 CURSOR c1 IS
1271 SELECT a.action_id
1272 FROM oke_deliverables_b b
1273 , oke_deliverable_actions a
1274 WHERE b.deliverable_id = a.deliverable_id
1275 AND b.source_code = 'PA'
1276 AND ( a.reference1 is null OR EXISTS (
1277 SELECT 1
1278 FROM po_requisitions_interface_all
1279 WHERE oke_contract_deliverable_id > 0
1280 AND process_flag = 'ERROR'
1281 AND batch_id = a.reference1 ))
1282 AND NVL ( a.ready_flag, 'N' ) = 'Y'
1283 AND a.action_type = 'REQ'
1284 AND a.expected_date >= sysdate;
1285
1286 c1rec c1%ROWTYPE;
1287
1288 CURSOR c2 IS
1289 SELECT a.action_id
1290 FROM oke_deliverables_b b
1291 , oke_deliverable_actions a
1292 WHERE b.deliverable_id = a.deliverable_id
1293 AND b.source_code = 'PA'
1294 AND b.source_header_id = p_project_id
1295 AND ( a.reference1 is null OR EXISTS (
1296 SELECT 1
1297 FROM po_requisitions_interface_all
1298 WHERE oke_contract_deliverable_id > 0
1299 AND process_flag = 'ERROR'
1300 AND batch_id = a.reference1 ))
1301 AND NVL ( a.ready_flag, 'N' ) = 'Y'
1302 AND a.action_type = 'REQ'
1303 AND a.expected_date >= sysdate;
1304
1305 c2rec c1%ROWTYPE;
1306
1307 CURSOR c3 IS
1308 SELECT a.action_id
1309 FROM oke_deliverables_b b
1310 , oke_deliverable_actions a
1311 WHERE b.deliverable_id = a.deliverable_id
1312 AND b.source_code = 'PA'
1313 AND b.source_header_id = p_project_id
1314 AND ( a.reference1 is null OR EXISTS (
1315 SELECT 1
1316 FROM po_requisitions_interface_all
1317 WHERE oke_contract_deliverable_id > 0
1318 AND process_flag = 'ERROR'
1319 AND batch_id = a.reference1 ))
1320 AND NVL ( a.ready_flag, 'N' ) = 'Y'
1321 AND a.action_type = 'REQ'
1322 AND a.task_id = p_task_id
1323 AND a.expected_date >= sysdate;
1324
1325 c3rec c1%ROWTYPE;
1326
1327 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
1328 L_API_Version CONSTANT NUMBER := 1;
1329 L_API_Name CONSTANT VARCHAR2(30) := 'BATCH_REQ';
1330 L_ID NUMBER;
1331 L_Msg_Count NUMBER;
1332 L_Msg_Data VARCHAR2(2000);
1333
1334
1335 BEGIN
1336
1337 L_Return_Status := OKE_API.Start_Activity ( L_Api_Name
1338 , P_Init_Msg_List
1339 , '_PKG'
1340 , X_Return_Status );
1341
1342 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1343 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1344 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1345 RAISE OKE_API.G_EXCEPTION_ERROR;
1346 END IF;
1347
1348 IF P_Project_ID IS NULL THEN
1349 FOR c1rec IN c1 LOOP
1350
1351 OKE_DELIVERABLE_ACTIONS_PKG.Create_Requisition ( c1rec.action_id
1352 , 'T'
1353 , L_ID
1354 , L_Return_Status
1355 , L_Msg_Count
1356 , L_Msg_Data );
1357 IF L_Return_Status <> 'S' THEN
1358 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1359 END IF;
1360 END LOOP;
1361 ELSIF P_Project_ID > 0 AND P_Task_ID IS NULL THEN
1362 FOR c2rec IN c2 LOOP
1363 OKE_DELIVERABLE_ACTIONS_PKG.Create_Requisition ( c2rec.action_id
1364 , 'T'
1365 , L_ID
1366 , L_Return_Status
1367 , L_Msg_Count
1368 , L_Msg_Data );
1369 IF L_Return_Status <> 'S' THEN
1373 ELSIF P_Task_ID > 0 THEN
1370 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1371 END IF;
1372 END LOOP;
1374 FOR c3rec IN c3 LOOP
1375 OKE_DELIVERABLE_ACTIONS_PKG.Create_Requisition ( c3rec.action_id
1376 , 'T'
1377 , L_ID
1378 , L_Return_Status
1379 , L_Msg_Count
1380 , L_Msg_Data );
1381 IF L_Return_Status <> 'S' THEN
1382 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1383 END IF;
1384 END LOOP;
1385 END IF;
1386
1387 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1388 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1389 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1390 RAISE OKE_API.G_EXCEPTION_ERROR;
1391 END IF;
1392
1393 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1394
1395 EXCEPTION
1396 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1397
1398 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1399 (
1400 l_api_name,
1401 G_PKG_NAME,
1402 'OKE_API.G_RET_STS_ERROR',
1403 x_msg_count,
1404 x_msg_data,
1405 '_PKG');
1406 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1407 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1408 (
1409 l_api_name,
1410 G_PKG_NAME,
1411 'OKE_API.G_RET_STS_UNEXP_ERROR',
1412 x_msg_count,
1413 x_msg_data,
1414 '_PKG');
1415
1416 WHEN OTHERS THEN
1417 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1418 (
1419 l_api_name,
1420 G_PKG_NAME,
1421 'OTHERS',
1422 x_msg_count,
1423 x_msg_data,
1424 '_PKG');
1425
1426 END Batch_REQ;
1427
1428 PROCEDURE Batch_Wsh ( P_Project_ID NUMBER
1429 , P_Task_ID NUMBER
1430 , P_Init_Msg_List VARCHAR2
1431 , X_Return_Status OUT NOCOPY VARCHAR2
1432 , X_Msg_Count OUT NOCOPY NUMBER
1433 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
1434
1435
1436 CURSOR c1 IS
1437 SELECT a.action_id
1438 FROM oke_deliverables_b b
1439 , oke_deliverable_actions a
1440 WHERE b.deliverable_id = a.deliverable_id
1441 AND b.source_code = 'PA'
1442 AND a.reference1 is null
1443 AND NVL ( a.ready_flag, 'N' ) = 'Y'
1444 AND a.action_type = 'WSH'
1445 AND a.expected_date >= sysdate;
1446
1447 c1rec c1%ROWTYPE;
1448
1449 CURSOR c2 IS
1450 SELECT a.action_id
1451 FROM oke_deliverables_b b
1452 , oke_deliverable_actions a
1453 WHERE b.deliverable_id = a.deliverable_id
1454 AND b.source_code = 'PA'
1455 AND b.source_header_id = p_project_id
1456 AND a.reference1 is null
1457 AND NVL ( a.ready_flag, 'N' ) = 'Y'
1458 AND a.action_type = 'WSH'
1459 AND a.expected_date >= sysdate;
1460
1461 c2rec c1%ROWTYPE;
1462
1463 CURSOR c3 IS
1464 SELECT a.action_id
1465 FROM oke_deliverables_b b
1466 , oke_deliverable_actions a
1467 WHERE b.deliverable_id = a.deliverable_id
1468 AND b.source_code = 'PA'
1469 AND b.source_header_id = p_project_id
1470 AND a.reference1 is null
1471 AND NVL ( a.ready_flag, 'N' ) = 'Y'
1472 AND a.action_type = 'WSH'
1473 AND a.task_id = p_task_id
1474 AND a.expected_date >= sysdate;
1475
1476 c3rec c1%ROWTYPE;
1477
1478 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
1479 L_API_Version CONSTANT NUMBER := 1;
1480 L_API_Name CONSTANT VARCHAR2(30) := 'BATCH_WSH';
1481 L_ID NUMBER;
1482 L_Msg_Count NUMBER;
1483 L_Msg_Data VARCHAR2(2000);
1484
1485
1486 BEGIN
1487
1488 L_Return_Status := OKE_API.Start_Activity ( L_Api_Name
1489 , P_Init_Msg_List
1490 , '_PKG'
1491 , X_Return_Status );
1492
1493 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1494 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1495 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1496 RAISE OKE_API.G_EXCEPTION_ERROR;
1497 END IF;
1498
1499 IF P_Project_ID IS NULL THEN
1500 FOR c1rec IN c1 LOOP
1501
1502 OKE_DELIVERABLE_ACTIONS_PKG.Create_Shipment ( c1rec.action_id
1503 , 'F'
1504 , L_ID
1505 , L_Return_Status
1506 , L_Msg_Count
1507 , L_Msg_Data );
1508 IF L_Return_Status <> 'S' THEN
1509 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1510 END IF;
1511 END LOOP;
1512 ELSIF P_Project_ID > 0 AND P_Task_ID IS NULL THEN
1513 FOR c2rec IN c2 LOOP
1514 OKE_DELIVERABLE_ACTIONS_PKG.Create_Shipment ( c2rec.action_id
1515 , 'F'
1516 , L_ID
1517 , L_Return_Status
1518 , L_Msg_Count
1519 , L_Msg_Data );
1520 IF L_Return_Status <> 'S' THEN
1521 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1522 END IF;
1523 END LOOP;
1524 ELSIF P_Task_ID > 0 THEN
1525 FOR c3rec IN c3 LOOP
1526 OKE_DELIVERABLE_ACTIONS_PKG.Create_Shipment ( c3rec.action_id
1527 , 'F'
1528 , L_ID
1529 , L_Return_Status
1530 , L_Msg_Count
1531 , L_Msg_Data );
1532 IF L_Return_Status <> 'S' THEN
1533 OKE_ACTION_VALIDATIONS_PKG.Add_Msg ( L_ID, L_Msg_Data );
1534 END IF;
1535 END LOOP;
1536 END IF;
1537
1538 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1539 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1540 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1541 RAISE OKE_API.G_EXCEPTION_ERROR;
1542 END IF;
1543
1544 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1545
1546 EXCEPTION
1550 (
1547 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1548
1549 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1551 l_api_name,
1552 G_PKG_NAME,
1553 'OKE_API.G_RET_STS_ERROR',
1554 x_msg_count,
1555 x_msg_data,
1556 '_PKG');
1557 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1558 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1559 (
1560 l_api_name,
1561 G_PKG_NAME,
1562 'OKE_API.G_RET_STS_UNEXP_ERROR',
1563 x_msg_count,
1564 x_msg_data,
1565 '_PKG');
1566
1567 WHEN OTHERS THEN
1568 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1569 (
1570 l_api_name,
1571 G_PKG_NAME,
1572 'OTHERS',
1573 x_msg_count,
1574 x_msg_data,
1575 '_PKG');
1576
1577 END Batch_WSH;
1578
1579 FUNCTION Item_Exist_Yn ( P_Deliverable_ID NUMBER ) RETURN VARCHAR2 IS
1580
1581 L_Dummy NUMBER;
1582
1583 CURSOR c IS
1584 SELECT 1
1585 FROM oke_deliverables_b
1586 WHERE source_code = 'PA'
1587 AND source_deliverable_id = p_deliverable_id
1588 AND item_id > 0;
1589
1590 BEGIN
1591 OPEN c;
1592 FETCH c INTO L_Dummy;
1593 IF c%NOTFOUND THEN
1594 CLOSE c;
1595 RETURN 'N';
1596 END IF;
1597
1598 CLOSE c;
1599 RETURN 'Y';
1600
1601 EXCEPTION
1602 WHEN OTHERS THEN
1603 RETURN 'N';
1604 END Item_Exist_Yn;
1605
1606 PROCEDURE Default_Action ( P_Source_Code VARCHAR2
1607 , P_Action_Type VARCHAR2
1608 , P_Source_Action_Name VARCHAR2
1609 , P_Source_Deliverable_ID NUMBER
1610 , P_Source_Action_ID NUMBER
1611 , P_Action_Date DATE ) IS
1612
1613 L_ID NUMBER;
1614 L_Deliverable_ID NUMBER;
1615
1616 CURSOR c IS
1617 SELECT DELIVERABLE_ID
1618 FROM oke_deliverables_b
1619 WHERE SOURCE_CODE = P_SOURCE_CODE
1620 AND SOURCE_DELIVERABLE_ID = P_SOURCE_DELIVERABLE_ID;
1621
1622 BEGIN
1623
1624 SELECT oke_k_deliverables_s.NEXTVAL INTO L_ID FROM DUAL;
1625
1626 OPEN c;
1627 FETCH c INTO L_Deliverable_ID;
1628 CLOSE c;
1629
1630 INSERT INTO oke_deliverable_actions (
1631 ACTION_ID
1632 , CREATION_DATE
1633 , CREATED_BY
1634 , LAST_UPDATE_DATE
1635 , LAST_UPDATED_BY
1636 , LAST_UPDATE_LOGIN
1637 , ACTION_TYPE
1638 , ACTION_NAME
1639 , DELIVERABLE_ID
1640 , PA_ACTION_ID
1641 , EXPECTED_DATE
1642 , destination_type_code
1643 ) VALUES ( L_ID
1644 , SYSDATE
1645 , FND_GLOBAL.USER_ID
1646 , SYSDATE
1647 , FND_GLOBAL.USER_ID
1648 , FND_GLOBAL.LOGIN_ID
1649 , P_ACTION_TYPE
1650 , P_SOURCE_ACTION_NAME
1651 , L_DELIVERABLE_ID
1652 , P_SOURCE_ACTION_ID
1653 , P_ACTION_DATE
1654 , Decode( P_ACTION_TYPE, 'REQ', 'EXPENSE', NULL)
1655 );
1656 EXCEPTION
1657 WHEN OTHERS THEN
1658 RAISE;
1659 END;
1660
1661 FUNCTION Task_Used_In_Wsh ( P_Task_ID NUMBER ) RETURN VARCHAR2 IS
1662
1663 CURSOR C IS
1664 SELECT G_Yes
1665 FROM oke_deliverable_actions
1666 WHERE task_id = p_task_id
1667 AND action_type = 'WSH'
1668 -- AND reference1 > 0 -- bug# 4007769 commented out
1669 ;
1670
1671 L_Dummy VARCHAR2(1);
1672
1673 BEGIN
1674
1675 IF P_Task_ID > 0 THEN
1676 OPEN C;
1677 FETCH C INTO L_Dummy;
1678 CLOSE C;
1679 RETURN Nvl(L_Dummy,G_No);
1680 ELSE
1681 RETURN null;
1682 END IF;
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 RAISE;
1686 END Task_Used_In_Wsh;
1687
1688 FUNCTION Task_Used_In_Req ( P_Task_ID NUMBER ) RETURN VARCHAR2 IS
1689
1690 CURSOR c IS
1691 SELECT G_Yes
1692 FROM oke_deliverable_actions
1693 WHERE task_id = p_task_id
1694 AND action_type = 'REQ'
1695 -- AND reference1 > 0 -- bug# 4007769 commented out
1696 ;
1697
1698 L_Dummy VARCHAR2(1);
1699
1700 BEGIN
1701
1702 IF P_Task_ID > 0 THEN
1703 OPEN C;
1704 FETCH C INTO L_Dummy;
1705 CLOSE C;
1706 RETURN Nvl(L_Dummy,G_No);
1707 ELSE
1708 RETURN null;
1709 END IF;
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 RAISE;
1713 END;
1714
1715 END;
1716