[Home] [Help]
PACKAGE BODY: APPS.OKE_DTS_WORKFLOW
Source
1 PACKAGE BODY OKE_DTS_WORKFLOW AS
2 /* $Header: OKEDTSWB.pls 120.3 2008/03/25 09:12:27 serukull ship $ */
3
4 Function Get_Location(P_Buy_Or_Sell Varchar2
5 , P_Direction Varchar2
6 , P_Id Number) Return Varchar2 Is
7
8 Cursor Location_C1(P_Id Number) Is
9 Select Name
10 From okx_locations_v
11 Where Id1 = P_Id;
12
13 Cursor Location_C2(P_Id Number) Is
14 Select Name
15 From okx_vendor_sites_v
16 Where Id1 = P_Id;
17
18 Cursor Location_C3(P_Id Number) Is
19 Select Name
20 From oke_cust_site_uses_v
21 Where Id1 = P_Id;
22
23 L_Location Varchar2(80);
24
25 Begin
26
27 If P_Direction = 'IN' Then
28 Open Location_C1(P_Id);
29 Fetch Location_C1 Into L_Location;
30 Close Location_C1;
31
32 Else
33 If P_Buy_Or_Sell = 'B' Then
34 Open Location_C2(P_Id);
35 Fetch Location_C2 Into L_Location;
36 Close Location_C2;
37
38 Else
39
40 Open Location_C3(P_Id);
41 Fetch Location_C3 Into L_Location;
42 Close Location_C3;
43 End If;
44 End If;
45
46 Return L_Location;
47
48 End Get_Location;
49
50 /* bug 6874204 */
51 FUNCTION check_operation_allowed (p_line_id Number) RETURN BOOLEAN IS
52
53 l_found boolean := FALSE;
54 l_sts_code varchar2(80);
55 l_value varchar2(1);
56
57 cursor c(p_id Number) IS
58 select sts_code from okc_k_lines_b
59 where id = p_id;
60
61 cursor opn_csr(p_sts_code varchar2) is
62 select 'x' from okc_assents
63 where opn_code = 'INITIATE_DELV'
64 and sts_code = p_sts_code
65 and scs_code = 'PROJECT'
66 and allowed_yn = 'Y';
67
68 BEGIN
69 open c(p_line_id);
70 fetch c into l_sts_code;
71 close c;
72
73 open opn_csr(l_sts_code);
74 fetch opn_csr into l_value;
75 l_found := opn_csr%found;
76 close opn_csr;
77
78 return l_found;
79
80 end check_operation_allowed;
81
82 PROCEDURE LAUNCH_MAIN_PROCESS
83 ( P_DELIVERABLE_ID IN NUMBER
84 , P_DTS_WF_MODE IN VARCHAR2
85 )
86 IS
87 P_API_VERSION NUMBER;
88
89 CURSOR CSR_ORG(P_ORG_ID Number) IS
90 SELECT NAME
91 FROM HR_ALL_ORGANIZATION_UNITS
92 WHERE ORGANIZATION_ID = P_ORG_ID;
93
94 CURSOR CSR_DTS(P_DELIVERABLE_ID IN NUMBER) IS
95 SELECT D.K_HEADER_ID
96 , D.DELIVERABLE_ID
97 , D.DELIVERABLE_NUM
98 , DECODE(D.ITEM_ID, NULL, NULL, I.NAME) ITEM_NUMBER
99 , B.CONTRACT_NUMBER
100 , S.LINE_NUMBER
101 , W.SOURCE_CODE
102 , W.USAGE_CODE
103 , D.DESCRIPTION
104 , H.K_TYPE
105 , W.WF_ITEM_TYPE
106 , W.WF_PROCESS
107 FROM OKE_K_DELIVERABLES_VL D
108 , OKE_WORKFLOWS W
109 , OKC_K_HEADERS_B B
110 , OKE_K_HEADERS_FULL_V H
111 , OKC_K_LINES_B S
112 , OKE_SYSTEM_ITEMS_V I
113 WHERE D.DELIVERABLE_ID = P_DELIVERABLE_ID
114 AND W.SOURCE_CODE = 'DTS'
115 AND W.USAGE_CODE =
116 DECODE(D.DIRECTION,'IN','INBOUND','OUT','OUTBOUND',NULL)
117 AND B.ID = D.K_HEADER_ID
118 AND H.K_HEADER_ID = D.K_HEADER_ID
119 AND S.ID = D.K_LINE_ID
120 AND I.ID1 (+)= D.ITEM_ID
121 AND I.ID2 (+)= D.INVENTORY_ORG_ID
122 ;
123
124 PROCEDURE LAUNCH_DTS
125 (
126 P_API_VERSION NUMBER
127 ,P_K_HEADER_ID NUMBER
128 ,P_DELIVERABLE_ID NUMBER
129 ,P_DELIVERABLE_NUM VARCHAR2
130 ,P_ITEM_NUMBER VARCHAR2
131 ,P_K_NUMBER VARCHAR2
132 ,P_LINE_NUMBER VARCHAR2
133 ,P_SOURCE_CODE VARCHAR2
134 ,P_USAGE_CODE VARCHAR2
135 ,P_DESCRIPTION VARCHAR2
136 ,P_DOC_TYPE VARCHAR2
137 ,P_DTS_WF_MODE VARCHAR2
138 ,L_WF_ITEM_TYPE VARCHAR2
139 ,L_WF_PROCESS VARCHAR2
140 )
141 IS
142 L_WF_Item_Key VARCHAR2(240);
143 L_WF_User_Key VARCHAR2(240);
144 L_org_id NUMBER;
145
146 CURSOR c_org is
147 select authoring_org_id from oke_k_headers_v where
148 k_header_id=p_k_header_id;
149 BEGIN
150
151 open c_org;
152 fetch c_org into l_org_id;
153 close c_org;
154
155 L_WF_Item_Key := P_Deliverable_ID || ':' ||
156 L_WF_PROCESS || ':' ||
157 to_char(sysdate , 'DDMONRRHH24MISS');
158
159 L_WF_User_Key := P_K_Number || ':' ||
160 P_Line_Number || ':' ||
161 P_Deliverable_Num || ':' ||
162 L_WF_PROCESS || ':' ||
163 P_Deliverable_ID || ':' ||
164 to_char(sysdate , 'DDMONRRHH24MISS');
165
166 WF_Engine.CreateProcess
167 ( ItemType => L_WF_Item_Type
168 , ItemKey => L_WF_Item_Key
169 , Process => L_WF_Process);
170
171 WF_Engine.SetItemOwner
172 ( ItemType => L_WF_Item_Type
173 , ItemKey => L_WF_Item_Key
174 , Owner => FND_GLOBAL.User_Name);
175
176 WF_Engine.SetItemUserKey
177 ( ItemType => L_WF_Item_Type
178 , ItemKey => L_WF_Item_Key
179 , UserKey => L_WF_User_Key);
180
181 --
182 -- Setting various Workflow Item Attributes
183 --
184 WF_ENGINE.SetItemAttrNumber
185 ( ItemType => L_WF_Item_Type
186 , ItemKey => L_WF_Item_Key
187 , AName => 'API_VERSION'
188 , AValue => P_API_VERSION );
189
190 WF_ENGINE.SetItemAttrNumber
191 ( ItemType => L_WF_Item_Type
192 , ItemKey => L_WF_Item_Key
193 , AName => 'ORG_ID'
194 , AValue => l_org_id );
195
196 WF_ENGINE.SetItemAttrNumber
197 ( ItemType => L_WF_Item_Type
198 , ItemKey => L_WF_Item_Key
199 , AName => 'K_HEADER_ID'
200 , AValue => P_K_HEADER_ID );
201
202 WF_ENGINE.SetItemAttrNumber
203 ( ItemType => L_WF_Item_Type
204 , ItemKey => L_WF_Item_Key
205 , AName => 'DELIVERABLE_ID'
206 , AValue => P_DELIVERABLE_ID );
207
208 WF_ENGINE.SetItemAttrText
209 ( ItemType => L_WF_Item_Type
210 , ItemKey => L_WF_Item_Key
211 , AName => 'DELIVERABLE_NUM'
212 , AValue => P_DELIVERABLE_NUM );
213
214 WF_ENGINE.SetItemAttrText
215 ( ItemType => L_WF_Item_Type
216 , ItemKey => L_WF_Item_Key
217 , AName => 'ITEM_NUM'
218 , AValue => P_ITEM_NUMBER );
219
220 WF_ENGINE.SetItemAttrText
221 ( ItemType => L_WF_Item_Type
222 , ItemKey => L_WF_Item_Key
223 , AName => 'K_NUMBER'
224 , AValue => P_K_NUMBER );
225
226 WF_ENGINE.SetItemAttrText
227 ( ItemType => L_WF_Item_Type
228 , ItemKey => L_WF_Item_Key
229 , AName => 'LINE_NUMBER'
230 , AValue => P_LINE_NUMBER );
231
232 WF_ENGINE.SetItemAttrText
233 ( ItemType => L_WF_Item_Type
234 , ItemKey => L_WF_Item_Key
235 , AName => 'SOURCE_CODE'
236 , AValue => P_SOURCE_CODE);
237
238 WF_ENGINE.SetItemAttrText
239 ( ItemType => L_WF_Item_Type
240 , ItemKey => L_WF_Item_Key
241 , AName => 'USAGE_CODE'
242 , AValue => P_USAGE_CODE);
243
244 WF_ENGINE.SetItemAttrText
245 ( ItemType => L_WF_Item_Type
246 , ItemKey => L_WF_Item_Key
247 , AName => 'DESCRIPTION'
248 , AValue => P_DESCRIPTION);
249
250 WF_ENGINE.SetItemAttrText
251 ( ItemType => L_WF_Item_Type
252 , ItemKey => L_WF_Item_Key
253 , AName => 'DOC_TYPE'
254 , AValue => P_DOC_TYPE);
255
256 WF_ENGINE.SetItemAttrText
257 ( ItemType => L_WF_Item_Type
258 , ItemKey => L_WF_Item_Key
259 , AName => 'DTS_WF_MODE'
260 , AValue => P_DTS_WF_MODE );
261
262 WF_ENGINE.SetItemAttrText
263 ( ItemType => L_WF_Item_Type
264 , ItemKey => L_WF_Item_Key
265 , AName => 'REQUESTOR'
266 , AValue => FND_GLOBAL.User_Name );
267
268 ------------------------------------------------------------------------------
269 --
270 -- Start the Workflow Process
271 --
272 WF_ENGINE.StartProcess( ItemType => L_WF_Item_Type
273 , ItemKey => L_WF_Item_Key );
274
275 update oke_k_deliverables_b
276 set wf_item_key = L_WF_Item_Key
277 where deliverable_id = P_DELIVERABLE_ID
278 ;
279
280 commit;
281 END LAUNCH_DTS;
282
283 BEGIN
284 P_API_VERSION := 1;
285
286 FOR REC_DTS IN CSR_DTS(P_DELIVERABLE_ID) LOOP
287
288 IF REC_DTS.WF_ITEM_TYPE IS NULL THEN
289 RETURN;
290 END IF;
291
292 LAUNCH_DTS
293 (
294 P_API_VERSION
295 ,REC_DTS.K_HEADER_ID
296 ,REC_DTS.DELIVERABLE_ID
297 ,REC_DTS.DELIVERABLE_NUM
298 ,REC_DTS.ITEM_NUMBER
299 ,REC_DTS.CONTRACT_NUMBER
300 ,REC_DTS.LINE_NUMBER
301 ,REC_DTS.SOURCE_CODE
302 ,REC_DTS.USAGE_CODE
303 ,REC_DTS.DESCRIPTION
304 ,REC_DTS.K_TYPE
305 ,P_DTS_WF_MODE
306 ,REC_DTS.WF_ITEM_TYPE
307 ,REC_DTS.WF_PROCESS
308 );
309
310 END LOOP;
311 END LAUNCH_MAIN_PROCESS;
312
313 PROCEDURE DUE_NTF_TO_SENT
314 ( ItemType IN VARCHAR2
315 , ItemKey IN VARCHAR2
316 , ActID IN NUMBER
317 , FuncMode IN VARCHAR2
318 , ResultOut OUT NOCOPY VARCHAR2
319 )
320 IS
321 L_K_Header_ID NUMBER;
322 L_Performer VARCHAR2(80);
323
324 L_Deliverable_ID NUMBER;
325 L_Return_Status VARCHAR2(1);
326 L_Msg_Count NUMBER;
327 L_Msg_Data VARCHAR2(2000);
328 L_Event_ID NUMBER;
329 L_Event_Num NUMBER;
330
331 L_Due_Ntf_Id NUMBER;
332 L_Source_Code VARCHAR2(30);
333 L_Usage_Code VARCHAR2(30);
334 L_Target_Date VARCHAR2(30);
335 L_Before_After VARCHAR2(30);
336 L_Duration_Days NUMBER;
337
338 CURSOR CSR_DUE_NTF(P_DUE_NTF_ID NUMBER
339 ,P_SOURCE_CODE VARCHAR2
340 ,P_USAGE_CODE VARCHAR2
341 ,P_TARGET_DATE VARCHAR2
342 ,P_DURATION_DAYS NUMBER) IS
343 SELECT ID
344 ,SOURCE_CODE
345 ,USAGE_CODE
346 ,TARGET_DATE
347 ,BEFORE_AFTER
348 ,DURATION_DAYS
349 ,RECIPIENT
350 ,ROLE_ID
351 FROM OKE_NOTIFICATIONS
352 WHERE SOURCE_CODE = P_SOURCE_CODE
353 AND USAGE_CODE = P_USAGE_CODE
354 AND TARGET_DATE = P_TARGET_DATE
355 AND BEFORE_AFTER = 'BEFORE'
356 AND (P_DUE_NTF_ID IS NULL OR DURATION_DAYS < P_DURATION_DAYS)
357 ORDER BY DURATION_DAYS DESC
358 ;
359 REC_DUE_NTF CSR_DUE_NTF%ROWTYPE;
360
361 -- CURSOR CSR_ESC(P_K_HEADER_ID Number,P_ROLE_ID Number) IS
362 -- SELECT R.NAME
363 -- FROM OKE_K_ALL_ACCESS_V A
364 -- ,WF_ROLES R
365 -- WHERE A. K_HEADER_ID = P_K_HEADER_ID
366 -- AND A.ROLE_ID = P_ROLE_ID
367 -- AND R.ORIG_SYSTEM = 'PER'
368 -- AND R.ORIG_SYSTEM_ID = A.PERSON_ID
369 -- ORDER BY DECODE(ASSIGNMENT_LEVEL,'OKE_K_HEADERS',1
370 -- ,'SITE',2
371 -- ,3)
372 -- ;
373
374 BEGIN
375
376 IF ( FuncMode = 'RUN' ) THEN
377
378 L_K_Header_ID := WF_Engine.GetItemAttrNumber
379 ( ItemType => ItemType
380 , ItemKey => ItemKey
381 , AName => 'K_HEADER_ID'
382 );
383
384 L_Deliverable_Id := WF_Engine.GetItemAttrNumber
385 ( ItemType => ItemType
386 , ItemKey => ItemKey
387 , AName => 'DELIVERABLE_ID'
388 );
389
390 L_Due_Ntf_Id := WF_Engine.GetItemAttrNumber
391 ( ItemType => ItemType
392 , ItemKey => ItemKey
393 , AName => 'DUE_NTF_ID'
394 );
395
396 L_Source_Code := WF_Engine.GetItemAttrText
397 ( ItemType => ItemType
398 , ItemKey => ItemKey
399 , AName => 'SOURCE_CODE'
400 );
401
402 L_Usage_Code := WF_Engine.GetItemAttrText
403 ( ItemType => ItemType
404 , ItemKey => ItemKey
405 , AName => 'USAGE_CODE'
406 );
407
408 L_Target_Date := WF_Engine.GetItemAttrText
409 ( ItemType => ItemType
410 , ItemKey => ItemKey
411 , AName => 'TARGET_DATE'
412 );
413
414 L_Before_After := WF_Engine.GetItemAttrText
415 ( ItemType => ItemType
416 , ItemKey => ItemKey
417 , AName => 'BEFORE_AFTER'
418 );
419
420 L_Duration_Days := WF_Engine.GetItemAttrNumber
421 ( ItemType => ItemType
422 , ItemKey => ItemKey
423 , AName => 'DURATION_DAYS'
424 );
425
426
427 OPEN CSR_DUE_NTF(L_Due_Ntf_Id,L_Source_Code,L_Usage_Code,'DELIVERY_DATE',L_Duration_Days);
428 FETCH CSR_DUE_NTF INTO REC_DUE_NTF;
429 CLOSE CSR_DUE_NTF;
430
431 IF REC_DUE_NTF.ID IS NULL THEN
432 WF_ENGINE.SetItemAttrNumber
433 ( ItemType => ItemType
434 , ItemKey => ItemKey
435 , AName => 'DUE_NTF_ID'
436 , AValue => NULL );
437
438 ResultOut := 'COMPLETE:F';
439 RETURN;
440 ELSE
441 WF_ENGINE.SetItemAttrNumber
442 ( ItemType => ItemType
443 , ItemKey => ItemKey
444 , AName => 'DUE_NTF_ID'
445 , AValue => REC_DUE_NTF.ID );
446
447 WF_ENGINE.SetItemAttrText
448 ( ItemType => ItemType
449 , ItemKey => ItemKey
450 , AName => 'SOURCE_CODE'
451 , AValue => REC_DUE_NTF.SOURCE_CODE );
452
453 WF_ENGINE.SetItemAttrText
454 ( ItemType => ItemType
455 , ItemKey => ItemKey
456 , AName => 'USAGE_CODE'
457 , AValue => REC_DUE_NTF.USAGE_CODE );
458
459 WF_ENGINE.SetItemAttrText
460 ( ItemType => ItemType
461 , ItemKey => ItemKey
462 , AName => 'TARGET_DATE'
463 , AValue => REC_DUE_NTF.TARGET_DATE );
464
465 WF_ENGINE.SetItemAttrText
466 ( ItemType => ItemType
467 , ItemKey => ItemKey
468 , AName => 'BEFORE_AFTER'
469 , AValue => REC_DUE_NTF.BEFORE_AFTER );
470
471 WF_ENGINE.SetItemAttrNumber
472 ( ItemType => ItemType
473 , ItemKey => ItemKey
474 , AName => 'DURATION_DAYS'
475 , AValue => REC_DUE_NTF.DURATION_DAYS );
476
477 WF_ENGINE.SetItemAttrText
478 ( ItemType => ItemType
479 , ItemKey => ItemKey
480 , AName => 'RECIPIENT'
481 , AValue => REC_DUE_NTF.RECIPIENT );
482
483 WF_ENGINE.SetItemAttrNumber
484 ( ItemType => ItemType
485 , ItemKey => ItemKey
486 , AName => 'ROLE_ID'
487 , AValue => REC_DUE_NTF.ROLE_ID );
488
489 IF REC_DUE_NTF.RECIPIENT='REQUESTOR' THEN
490 WF_ENGINE.SetItemAttrText
491 ( ItemType => ItemType
492 , ItemKey => ItemKey
493 , AName => 'PERFORMER'
494 , AValue => FND_GLOBAL.User_Name);
495 ELSIF REC_DUE_NTF.RECIPIENT='CONTRACT_ROLE' THEN
496 -- OPEN CSR_ESC(L_K_Header_ID,REC_DUE_NTF.ROLE_ID);
497 -- FETCH CSR_ESC INTO L_Performer;
498 -- CLOSE CSR_ESC;
499
500 L_Performer := OKE_UTILS.Retrieve_WF_Role_Name(L_K_Header_ID,REC_DUE_NTF.ROLE_ID);
501
502 WF_ENGINE.SetItemAttrText
503 ( ItemType => ItemType
504 , ItemKey => ItemKey
505 , AName => 'PERFORMER'
506 , AValue => L_Performer );
507
508 ELSE
509 WF_ENGINE.SetItemAttrText
510 ( ItemType => ItemType
511 , ItemKey => ItemKey
512 , AName => 'PERFORMER'
513 , AValue => FND_GLOBAL.User_Name);
514 END IF;
515
516 ResultOut := 'COMPLETE:T';
517 RETURN;
518 END IF;
519 END IF;
520
521 IF ( FuncMode = 'CANCEL' ) THEN
522 ResultOut := '';
523 RETURN;
524 END IF;
525
526 IF ( FuncMode = 'TIMEOUT' ) THEN
527 ResultOut := '';
528 RETURN;
529 END IF;
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 ResultOut := 'ERROR:';
534 WF_ENGINE.SetItemAttrText
535 ( ItemType => ItemType
536 , ItemKey => ItemKey
537 , AName => 'ERRORTEXT'
538 , AValue => sqlerrm );
539 WF_Core.Context
540 ( 'OKE_DTS_WORKFLOW'
541 , 'DUE_NTF_TO_SENT'
542 , ItemType
543 , ItemKey
544 , to_char(ActID)
545 , FuncMode
546 , ResultOut );
547 RAISE;
548
549 END DUE_NTF_TO_SENT;
550
551 PROCEDURE PAST_DUE_NTF_TO_SENT
552 ( ItemType IN VARCHAR2
553 , ItemKey IN VARCHAR2
554 , ActID IN NUMBER
555 , FuncMode IN VARCHAR2
556 , ResultOut OUT NOCOPY VARCHAR2
557 )
558 IS
559 L_K_Header_ID NUMBER;
560 L_Performer VARCHAR2(80);
561
562 L_Deliverable_ID NUMBER;
563 L_Return_Status VARCHAR2(1);
564 L_Msg_Count NUMBER;
565 L_Msg_Data VARCHAR2(2000);
566 L_Event_ID NUMBER;
567 L_Event_Num NUMBER;
568
569 L_Due_Ntf_Id NUMBER;
570 L_Source_Code VARCHAR2(30);
571 L_Usage_Code VARCHAR2(30);
572 L_Target_Date VARCHAR2(30);
573 L_Before_After VARCHAR2(30);
574 L_Duration_Days NUMBER;
575
576 CURSOR CSR_DUE_NTF(P_DUE_NTF_ID NUMBER
577 ,P_SOURCE_CODE VARCHAR2
578 ,P_USAGE_CODE VARCHAR2
579 ,P_TARGET_DATE VARCHAR2
580 ,P_DURATION_DAYS NUMBER) IS
581 SELECT ID
582 ,SOURCE_CODE
583 ,USAGE_CODE
584 ,TARGET_DATE
585 ,BEFORE_AFTER
586 ,DURATION_DAYS
587 ,RECIPIENT
588 ,ROLE_ID
589 FROM OKE_NOTIFICATIONS
590 WHERE SOURCE_CODE = P_SOURCE_CODE
591 AND USAGE_CODE = P_USAGE_CODE
592 AND TARGET_DATE = P_TARGET_DATE
593 AND BEFORE_AFTER = 'AFTER'
594 AND (P_DUE_NTF_ID IS NULL OR DURATION_DAYS > P_DURATION_DAYS)
595 ORDER BY DURATION_DAYS ASC
596 ;
597 REC_DUE_NTF CSR_DUE_NTF%ROWTYPE;
598
599 -- CURSOR CSR_ESC(P_K_HEADER_ID Number,P_ROLE_ID Number) IS
600 -- SELECT R.NAME
601 -- FROM OKE_K_ALL_ACCESS_V A
602 -- ,WF_ROLES R
603 -- WHERE A. K_HEADER_ID = P_K_HEADER_ID
604 -- AND A.ROLE_ID = P_ROLE_ID
605 -- AND R.ORIG_SYSTEM = 'PER'
606 -- AND R.ORIG_SYSTEM_ID = A.PERSON_ID
607 -- ORDER BY DECODE(ASSIGNMENT_LEVEL,'OKE_K_HEADERS',1
608 -- ,'SITE',2
609 -- ,3)
610 -- ;
611
612 BEGIN
613
614 IF ( FuncMode = 'RUN' ) THEN
615
616 L_K_Header_ID := WF_Engine.GetItemAttrNumber
617 ( ItemType => ItemType
618 , ItemKey => ItemKey
619 , AName => 'K_HEADER_ID'
620 );
621
622 L_Deliverable_Id := WF_Engine.GetItemAttrNumber
623 ( ItemType => ItemType
624 , ItemKey => ItemKey
625 , AName => 'DELIVERABLE_ID'
626 );
627
628 L_Due_Ntf_Id := WF_Engine.GetItemAttrNumber
629 ( ItemType => ItemType
630 , ItemKey => ItemKey
631 , AName => 'DUE_NTF_ID'
632 );
633
634 L_Source_Code := WF_Engine.GetItemAttrText
635 ( ItemType => ItemType
636 , ItemKey => ItemKey
637 , AName => 'SOURCE_CODE'
638 );
639
640 L_Usage_Code := WF_Engine.GetItemAttrText
641 ( ItemType => ItemType
642 , ItemKey => ItemKey
643 , AName => 'USAGE_CODE'
644 );
645
646 L_Target_Date := WF_Engine.GetItemAttrText
647 ( ItemType => ItemType
648 , ItemKey => ItemKey
649 , AName => 'TARGET_DATE'
650 );
651
652 L_Before_After := WF_Engine.GetItemAttrText
653 ( ItemType => ItemType
654 , ItemKey => ItemKey
655 , AName => 'BEFORE_AFTER'
656 );
657
658 L_Duration_Days := WF_Engine.GetItemAttrNumber
659 ( ItemType => ItemType
660 , ItemKey => ItemKey
661 , AName => 'DURATION_DAYS'
662 );
663
664
665 OPEN CSR_DUE_NTF(L_Due_Ntf_Id,L_Source_Code,L_Usage_Code,'DELIVERY_DATE',L_Duration_Days);
666 FETCH CSR_DUE_NTF INTO REC_DUE_NTF;
667 CLOSE CSR_DUE_NTF;
668
669 IF REC_DUE_NTF.ID IS NULL THEN
670 WF_ENGINE.SetItemAttrNumber
671 ( ItemType => ItemType
672 , ItemKey => ItemKey
673 , AName => 'DUE_NTF_ID'
674 , AValue => NULL );
675
676 ResultOut := 'COMPLETE:F';
677 RETURN;
678 ELSE
679 WF_ENGINE.SetItemAttrNumber
680 ( ItemType => ItemType
681 , ItemKey => ItemKey
682 , AName => 'DUE_NTF_ID'
683 , AValue => REC_DUE_NTF.ID );
684
685 WF_ENGINE.SetItemAttrText
686 ( ItemType => ItemType
687 , ItemKey => ItemKey
688 , AName => 'SOURCE_CODE'
689 , AValue => REC_DUE_NTF.SOURCE_CODE );
690
691 WF_ENGINE.SetItemAttrText
692 ( ItemType => ItemType
693 , ItemKey => ItemKey
694 , AName => 'USAGE_CODE'
695 , AValue => REC_DUE_NTF.USAGE_CODE );
696
697 WF_ENGINE.SetItemAttrText
698 ( ItemType => ItemType
699 , ItemKey => ItemKey
700 , AName => 'TARGET_DATE'
701 , AValue => REC_DUE_NTF.TARGET_DATE );
702
703 WF_ENGINE.SetItemAttrText
704 ( ItemType => ItemType
705 , ItemKey => ItemKey
706 , AName => 'BEFORE_AFTER'
707 , AValue => REC_DUE_NTF.BEFORE_AFTER );
708
709 WF_ENGINE.SetItemAttrNumber
710 ( ItemType => ItemType
711 , ItemKey => ItemKey
712 , AName => 'DURATION_DAYS'
713 , AValue => REC_DUE_NTF.DURATION_DAYS );
714
715 WF_ENGINE.SetItemAttrText
716 ( ItemType => ItemType
717 , ItemKey => ItemKey
718 , AName => 'RECIPIENT'
719 , AValue => REC_DUE_NTF.RECIPIENT );
720
721 WF_ENGINE.SetItemAttrNumber
722 ( ItemType => ItemType
723 , ItemKey => ItemKey
724 , AName => 'ROLE_ID'
725 , AValue => REC_DUE_NTF.ROLE_ID );
726
727 IF REC_DUE_NTF.RECIPIENT='REQUESTOR' THEN
728 WF_ENGINE.SetItemAttrText
729 ( ItemType => ItemType
730 , ItemKey => ItemKey
731 , AName => 'PERFORMER'
732 , AValue => FND_GLOBAL.User_Name);
733 ELSIF REC_DUE_NTF.RECIPIENT='CONTRACT_ROLE' THEN
734 -- OPEN CSR_ESC(L_K_Header_ID,REC_DUE_NTF.ROLE_ID);
735 -- FETCH CSR_ESC INTO L_Performer;
736 -- CLOSE CSR_ESC;
737
738 L_Performer := OKE_UTILS.Retrieve_WF_Role_Name(L_K_Header_ID,REC_DUE_NTF.ROLE_ID);
739
740 WF_ENGINE.SetItemAttrText
741 ( ItemType => ItemType
742 , ItemKey => ItemKey
743 , AName => 'PERFORMER'
744 , AValue => L_Performer );
745
746 ELSE
747 WF_ENGINE.SetItemAttrText
748 ( ItemType => ItemType
749 , ItemKey => ItemKey
750 , AName => 'PERFORMER'
751 , AValue => FND_GLOBAL.User_Name);
752 END IF;
753
754 ResultOut := 'COMPLETE:T';
755 RETURN;
756 END IF;
757 END IF;
758
759 IF ( FuncMode = 'CANCEL' ) THEN
760 ResultOut := '';
761 RETURN;
762 END IF;
763
764 IF ( FuncMode = 'TIMEOUT' ) THEN
765 ResultOut := '';
766 RETURN;
767 END IF;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 ResultOut := 'ERROR:';
772 WF_ENGINE.SetItemAttrText
773 ( ItemType => ItemType
774 , ItemKey => ItemKey
775 , AName => 'ERRORTEXT'
776 , AValue => sqlerrm );
777 WF_Core.Context
778 ( 'OKE_DTS_WORKFLOW'
779 , 'DUE_NTF_TO_SENT'
780 , ItemType
781 , ItemKey
782 , to_char(ActID)
783 , FuncMode
784 , ResultOut );
785 RAISE;
786
787 END PAST_DUE_NTF_TO_SENT;
788
789 PROCEDURE SELECT_DATE
790 ( ItemType IN VARCHAR2
791 , ItemKey IN VARCHAR2
792 , ActID IN NUMBER
793 , FuncMode IN VARCHAR2
794 , ResultOut OUT NOCOPY VARCHAR2
795 )
796 IS
797 L_Deliverable_ID NUMBER;
798 L_Return_Status VARCHAR2(1);
799 L_Msg_Count NUMBER;
800 L_Msg_Data VARCHAR2(2000);
801 L_Event_ID NUMBER;
802 L_Event_Num NUMBER;
803
804 L_Due_Ntf_Id NUMBER;
805 L_Source_Code VARCHAR2(30);
806 L_Usage_Code VARCHAR2(30);
807 L_Target_Date VARCHAR2(30);
808 L_Before_After VARCHAR2(30);
809 L_Duration_Days NUMBER;
810
811 L_Delivery_Date DATE;
812 L_Deliverable_Num VARCHAR2(240);
813 L_Description VARCHAR2(2000);
814
815 BEGIN
816 IF ( FuncMode = 'RUN' ) THEN
817
818 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
819 ( ItemType => ItemType
820 , ItemKey => ItemKey
821 , AName => 'DELIVERABLE_ID'
822 );
823
824 L_Due_Ntf_ID := WF_Engine.GetItemAttrNumber
825 ( ItemType => ItemType
826 , ItemKey => ItemKey
827 , AName => 'DUE_NTF_ID'
828 );
829
830 L_Source_Code := WF_Engine.GetItemAttrText
831 ( ItemType => ItemType
832 , ItemKey => ItemKey
833 , AName => 'SOURCE_CODE'
834 );
835
836 L_Usage_Code := WF_Engine.GetItemAttrText
837 ( ItemType => ItemType
838 , ItemKey => ItemKey
839 , AName => 'USAGE_CODE'
840 );
841
842 L_Target_date := WF_Engine.GetItemAttrText
843 ( ItemType => ItemType
844 , ItemKey => ItemKey
845 , AName => 'TARGET_DATE'
846 );
847
848 L_Before_After := WF_Engine.GetItemAttrText
849 ( ItemType => ItemType
850 , ItemKey => ItemKey
851 , AName => 'BEFORE_AFTER'
852 );
853
854 L_Duration_Days := WF_Engine.GetItemAttrNumber
855 ( ItemType => ItemType
856 , ItemKey => ItemKey
857 , AName => 'DURATION_DAYS'
858 );
859
860
861 BEGIN
862 SELECT DELIVERY_DATE
863 ,DELIVERABLE_NUM
864 ,DESCRIPTION
865 INTO L_Delivery_Date
866 ,L_Deliverable_Num
867 ,L_Description
868 FROM OKE_K_DELIVERABLES_VL
869 WHERE DELIVERABLE_ID=L_Deliverable_ID;
870 EXCEPTION
871 WHEN OTHERS THEN
872 NULL;
873 END;
874
875 IF L_Delivery_Date IS NULL THEN
876 ResultOut := 'COMPLETE:F';
877 ELSE
878 WF_ENGINE.SetItemAttrDate
879 ( ItemType => ItemType
880 , ItemKey => ItemKey
881 , AName => 'DELIVERY_DATE'
882 , AValue => L_Delivery_Date );
883
884 WF_ENGINE.SetItemAttrDate
885 ( ItemType => ItemType
886 , ItemKey => ItemKey
887 , AName => 'TARGET_DATE_VALUE'
888 , AValue => L_Delivery_Date );
889
890 WF_ENGINE.SetItemAttrText
891 ( ItemType => ItemType
892 , ItemKey => ItemKey
893 , AName => 'DELIVERABLE_NUM'
894 , AValue => L_DELIVERABLE_NUM );
895
896 WF_ENGINE.SetItemAttrText
897 ( ItemType => ItemType
898 , ItemKey => ItemKey
899 , AName => 'DESCRIPTION'
900 , AValue => L_DESCRIPTION );
901
902 IF L_Before_After = 'BEFORE' THEN
903 WF_ENGINE.SetItemAttrDate
904 ( ItemType => ItemType
905 , ItemKey => ItemKey
906 , AName => 'COMPARE_DATE'
907 , AValue => sysdate+L_Duration_Days );
908 ELSE
909 WF_ENGINE.SetItemAttrDate
910 ( ItemType => ItemType
911 , ItemKey => ItemKey
912 , AName => 'COMPARE_DATE'
913 , AValue => sysdate-L_Duration_Days );
914 END IF;
915
916 ResultOut := 'COMPLETE:T';
917 END IF;
918 RETURN;
919 END IF;
920
921 IF ( FuncMode = 'CANCEL' ) THEN
922 ResultOut := '';
923 RETURN;
924 END IF;
925
926 IF ( FuncMode = 'TIMEOUT' ) THEN
927 ResultOut := '';
928 RETURN;
929 END IF;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 ResultOut := 'ERROR:';
934 WF_ENGINE.SetItemAttrText
935 ( ItemType => ItemType
936 , ItemKey => ItemKey
937 , AName => 'ERRORTEXT'
938 , AValue => sqlerrm );
939 WF_Core.Context
940 ( 'OKE_DTS_WORKFLOW'
941 , 'SELECT_DATE'
942 , ItemType
943 , ItemKey
944 , to_char(ActID)
945 , FuncMode
946 , ResultOut );
947 RAISE;
948
949 END SELECT_DATE;
950
951 PROCEDURE READY_TO_SHIP
952 ( ItemType IN VARCHAR2
953 , ItemKey IN VARCHAR2
954 , ActID IN NUMBER
955 , FuncMode IN VARCHAR2
956 , ResultOut OUT NOCOPY VARCHAR2
957 )
958 IS
959 L_K_HEADER_ID NUMBER;
960 L_K_LINE_ID NUMBER;
961 L_Hold BOOLEAN;
962 L_return_status VARCHAR2(1);
963 L_msg_count NUMBER;
964 L_msg_data VARCHAR2(2000);
965
966 L_Deliverable_ID NUMBER;
967 L_Available_For_Ship_Flag VARCHAR2(1);
968
969 l_item_id NUMBER;
970 l_description VARCHAR2(2000);
971 l_quantity NUMBER;
972 l_uom_code VARCHAR2(10);
973 l_ship_to_location_id NUMBER;
974 l_ship_from_location_id NUMBER;
975 l_expected_date DATE;
976
977 BEGIN
978 L_Hold :=FALSE;
979
980 IF ( FuncMode = 'RUN' ) THEN
981
982 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
983 ( ItemType => ItemType
984 , ItemKey => ItemKey
985 , AName => 'DELIVERABLE_ID'
986 );
987
988 BEGIN
989 SELECT AVAILABLE_FOR_SHIP_FLAG
990 ,ITEM_ID
991 ,DESCRIPTION
992 ,QUANTITY
993 ,UOM_CODE
994 ,SHIP_TO_LOCATION_ID
995 ,SHIP_FROM_LOCATION_ID
996 ,EXPECTED_SHIPMENT_DATE
997 INTO L_Available_For_Ship_Flag
998 ,l_item_id
999 ,l_description
1000 ,l_quantity
1001 ,l_uom_code
1002 ,l_ship_to_location_id
1003 ,l_ship_from_location_id
1004 ,l_expected_date
1005 FROM OKE_K_DELIVERABLES_VL
1006 WHERE DELIVERABLE_ID=L_Deliverable_ID;
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 NULL;
1010 END;
1011
1012 -- BUG 3597451
1013 IF L_Available_For_Ship_Flag <> 'Y'
1014 OR (l_item_id is null and l_description is null)
1015 OR l_quantity is null
1016 OR l_uom_code is null
1017 OR l_ship_to_location_id is null
1018 OR l_ship_from_location_id is null
1019 OR l_expected_date is null THEN
1020
1021 ResultOut := 'COMPLETE:F';
1022 RETURN;
1023 ELSE
1024 WF_ENGINE.SetItemAttrText
1025 ( ItemType => ItemType
1026 , ItemKey => ItemKey
1027 , AName => 'ACTION'
1028 , AValue => 'SHIP' );
1029 ResultOut := 'COMPLETE:T';
1030 END IF;
1031
1032
1033 BEGIN
1034 SELECT K_HEADER_ID
1035 ,K_LINE_ID
1036 INTO L_K_HEADER_ID
1037 ,L_K_LINE_ID
1038 FROM OKE_K_DELIVERABLES_VL
1039 WHERE DELIVERABLE_ID = L_Deliverable_ID;
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 ResultOut := 'ERROR:';
1043 WF_ENGINE.SetItemAttrText
1044 ( ItemType => ItemType
1045 , ItemKey => ItemKey
1046 , AName => 'ERRORTEXT'
1047 , AValue => sqlerrm );
1048 WF_Core.Context
1049 ( 'OKE_DTS_WORKFLOW'
1050 , 'READY_TO_SHIP'
1051 , ItemType
1052 , ItemKey
1053 , to_char(ActID)
1054 , FuncMode
1055 , ResultOut );
1056 RAISE;
1057 END;
1058
1059 -- Check if deliverable on hold
1060 L_Hold := OKE_CHECK_HOLD_PKG.Is_Hold(1
1061 , 'T'
1062 , L_Return_Status
1063 , L_Msg_Count
1064 , L_Msg_Data
1065 , 'DELIVERABLE'
1066 , L_K_HEADER_ID
1067 , L_K_LINE_ID
1068 , L_Deliverable_ID);
1069 IF L_Hold THEN
1070 ResultOut := 'COMPLETE:F';
1071 RETURN;
1072 END IF;
1073
1074 IF NOT check_operation_allowed(L_K_LINE_ID) THEN
1075 ResultOut := 'COMPLETE:F';
1076 RETURN;
1077 END IF;
1078
1079 RETURN;
1080 END IF;
1081
1082 IF ( FuncMode = 'CANCEL' ) THEN
1083 ResultOut := '';
1084 RETURN;
1085 END IF;
1086
1087 IF ( FuncMode = 'TIMEOUT' ) THEN
1088 ResultOut := '';
1089 RETURN;
1090 END IF;
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 ResultOut := 'ERROR:';
1095 WF_ENGINE.SetItemAttrText
1096 ( ItemType => ItemType
1097 , ItemKey => ItemKey
1098 , AName => 'ERRORTEXT'
1099 , AValue => sqlerrm );
1100 WF_Core.Context
1101 ( 'OKE_DTS_WORKFLOW'
1102 , 'READY_TO_SHIP'
1103 , ItemType
1104 , ItemKey
1105 , to_char(ActID)
1106 , FuncMode
1107 , ResultOut );
1108 RAISE;
1109
1110 END READY_TO_SHIP;
1111
1112 PROCEDURE READY_TO_CREATE_MDS
1113 ( ItemType IN VARCHAR2
1114 , ItemKey IN VARCHAR2
1115 , ActID IN NUMBER
1116 , FuncMode IN VARCHAR2
1117 , ResultOut OUT NOCOPY VARCHAR2
1118 )
1119 IS
1120 L_K_HEADER_ID NUMBER;
1121 L_K_LINE_ID NUMBER;
1122 L_Hold BOOLEAN;
1123 L_return_status VARCHAR2(1);
1124 L_msg_count NUMBER;
1125 L_msg_data VARCHAR2(2000);
1126
1127 L_Deliverable_ID NUMBER;
1128 L_Create_Demand VARCHAR2(1);
1129
1130 l_item_id NUMBER;
1131 l_inventory_org_id NUMBER;
1132 l_ndb_schedule_designator VARCHAR2(2000);
1133 l_quantity NUMBER;
1134 l_uom_code VARCHAR2(10);
1135 l_expected_date DATE;
1136
1137
1138
1139 BEGIN
1140
1141 L_Hold := FALSE;
1142
1143 IF ( FuncMode = 'RUN' ) THEN
1144
1145 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
1146 ( ItemType => ItemType
1147 , ItemKey => ItemKey
1148 , AName => 'DELIVERABLE_ID'
1149 );
1150
1151 BEGIN
1152 SELECT CREATE_DEMAND
1153 ,ITEM_ID
1154 ,INVENTORY_ORG_ID
1155 ,NDB_SCHEDULE_DESIGNATOR
1156 ,QUANTITY
1157 ,UOM_CODE
1158 ,EXPECTED_SHIPMENT_DATE
1159 INTO L_Create_Demand
1160 ,l_item_id
1161 ,l_inventory_org_id
1162 ,l_ndb_schedule_designator
1163 ,l_quantity
1164 ,l_uom_code
1165 ,l_expected_date
1166 FROM OKE_K_DELIVERABLES_VL
1167 WHERE DELIVERABLE_ID=L_Deliverable_ID;
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 NULL;
1171 END;
1172
1173 -- BUG 3597451
1174 IF L_Create_Demand <> 'Y'
1175 OR l_item_id is null
1176 OR l_inventory_org_id is null
1177 OR l_ndb_schedule_designator is null
1178 OR l_quantity is null
1179 OR l_uom_code is null
1180 OR l_expected_date is null THEN
1181
1182 ResultOut := 'COMPLETE:F';
1183 RETURN;
1184 ELSE
1185 WF_ENGINE.SetItemAttrText
1186 ( ItemType => ItemType
1187 , ItemKey => ItemKey
1188 , AName => 'ACTION'
1189 , AValue => 'PLAN' );
1190 ResultOut := 'COMPLETE:T';
1191 END IF;
1192
1193 BEGIN
1194 SELECT K_HEADER_ID
1195 ,K_LINE_ID
1196 INTO L_K_HEADER_ID
1197 ,L_K_LINE_ID
1198 FROM OKE_K_DELIVERABLES_VL
1199 WHERE DELIVERABLE_ID = L_Deliverable_ID;
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 ResultOut := 'ERROR:';
1203 WF_ENGINE.SetItemAttrText
1204 ( ItemType => ItemType
1205 , ItemKey => ItemKey
1206 , AName => 'ERRORTEXT'
1207 , AValue => sqlerrm );
1208 WF_Core.Context
1209 ( 'OKE_DTS_WORKFLOW'
1210 , 'READY_TO_CREATE_MDS'
1211 , ItemType
1212 , ItemKey
1213 , to_char(ActID)
1214 , FuncMode
1215 , ResultOut );
1216 RAISE;
1217 END;
1218
1219 -- Check if deliverable on hold
1220 L_Hold := OKE_CHECK_HOLD_PKG.Is_Hold(1
1221 , 'T'
1222 , L_Return_Status
1223 , L_Msg_Count
1224 , L_Msg_Data
1225 , 'DELIVERABLE'
1226 , L_K_HEADER_ID
1227 , L_K_LINE_ID
1228 , L_Deliverable_ID);
1229 IF L_Hold THEN
1230 ResultOut := 'COMPLETE:F';
1231 RETURN;
1232 END IF;
1233
1234 IF NOT check_operation_allowed(L_K_LINE_ID) THEN
1235 ResultOut := 'COMPLETE:F';
1236 RETURN;
1237 END IF;
1238
1239 RETURN;
1240 END IF;
1241
1242 IF ( FuncMode = 'CANCEL' ) THEN
1243 ResultOut := '';
1244 RETURN;
1245 END IF;
1246
1247 IF ( FuncMode = 'TIMEOUT' ) THEN
1248 ResultOut := '';
1249 RETURN;
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 ResultOut := 'ERROR:';
1255 WF_ENGINE.SetItemAttrText
1256 ( ItemType => ItemType
1257 , ItemKey => ItemKey
1258 , AName => 'ERRORTEXT'
1259 , AValue => sqlerrm );
1260 WF_Core.Context
1261 ( 'OKE_DTS_WORKFLOW'
1262 , 'READY_TO_CREATE_MDS'
1263 , ItemType
1264 , ItemKey
1265 , to_char(ActID)
1266 , FuncMode
1267 , ResultOut );
1268 RAISE;
1269
1270 END READY_TO_CREATE_MDS;
1271
1272 PROCEDURE READY_TO_PROCURE
1273 ( ItemType IN VARCHAR2
1274 , ItemKey IN VARCHAR2
1275 , ActID IN NUMBER
1276 , FuncMode IN VARCHAR2
1277 , ResultOut OUT NOCOPY VARCHAR2
1278 )
1279 IS
1280 L_K_HEADER_ID NUMBER;
1281 L_K_LINE_ID NUMBER;
1282 L_Hold BOOLEAN;
1283 L_return_status VARCHAR2(1);
1284 L_msg_count NUMBER;
1285 L_msg_data VARCHAR2(2000);
1286
1287 L_Deliverable_ID NUMBER;
1288 L_Ready_To_Procure VARCHAR2(1);
1289
1290 BEGIN
1291
1292 L_Hold := FALSE;
1293
1294 IF ( FuncMode = 'RUN' ) THEN
1295
1296 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
1297 ( ItemType => ItemType
1298 , ItemKey => ItemKey
1299 , AName => 'DELIVERABLE_ID'
1300 );
1301
1302 BEGIN
1303 SELECT READY_TO_PROCURE
1304 INTO L_Ready_To_Procure
1305 FROM OKE_K_DELIVERABLES_VL
1306 WHERE DELIVERABLE_ID=L_Deliverable_ID;
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 NULL;
1310 END;
1311
1312 IF L_Ready_To_Procure <> 'Y' THEN
1313
1314 ResultOut := 'COMPLETE:F';
1315 RETURN;
1316 ELSE
1317 WF_ENGINE.SetItemAttrText
1318 ( ItemType => ItemType
1319 , ItemKey => ItemKey
1320 , AName => 'ACTION'
1321 , AValue => 'REQ' );
1322 ResultOut := 'COMPLETE:T';
1323 END IF;
1324
1325 BEGIN
1326 SELECT K_HEADER_ID
1327 ,K_LINE_ID
1328 INTO L_K_HEADER_ID
1329 ,L_K_LINE_ID
1330 FROM OKE_K_DELIVERABLES_VL
1331 WHERE DELIVERABLE_ID = L_Deliverable_ID;
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 ResultOut := 'ERROR:';
1335 WF_ENGINE.SetItemAttrText
1336 ( ItemType => ItemType
1337 , ItemKey => ItemKey
1338 , AName => 'ERRORTEXT'
1339 , AValue => sqlerrm );
1340 WF_Core.Context
1341 ( 'OKE_DTS_WORKFLOW'
1342 , 'READY_TO_PROCURE'
1343 , ItemType
1344 , ItemKey
1345 , to_char(ActID)
1346 , FuncMode
1347 , ResultOut );
1348 RAISE;
1349 END;
1350
1351 -- Check if deliverable on hold
1352 L_Hold := OKE_CHECK_HOLD_PKG.Is_Hold(1
1353 , 'T'
1354 , L_Return_Status
1355 , L_Msg_Count
1356 , L_Msg_Data
1357 , 'DELIVERABLE'
1358 , L_K_HEADER_ID
1359 , L_K_LINE_ID
1360 , L_Deliverable_ID);
1361 IF L_Hold THEN
1362 ResultOut := 'COMPLETE:F';
1363 RETURN;
1364 END IF;
1365
1366 IF NOT check_operation_allowed(L_K_LINE_ID) THEN
1367 ResultOut := 'COMPLETE:F';
1368 RETURN;
1369 END IF;
1370
1371 RETURN;
1372 END IF;
1373
1374 IF ( FuncMode = 'CANCEL' ) THEN
1375 ResultOut := '';
1376 RETURN;
1377 END IF;
1378
1379 IF ( FuncMode = 'TIMEOUT' ) THEN
1380 ResultOut := '';
1381 RETURN;
1382 END IF;
1383
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 ResultOut := 'ERROR:';
1387 WF_ENGINE.SetItemAttrText
1388 ( ItemType => ItemType
1389 , ItemKey => ItemKey
1390 , AName => 'ERRORTEXT'
1391 , AValue => sqlerrm );
1392 WF_Core.Context
1393 ( 'OKE_DTS_WORKFLOW'
1394 , 'READY_TO_PROCURE'
1395 , ItemType
1396 , ItemKey
1397 , to_char(ActID)
1398 , FuncMode
1399 , ResultOut );
1400 RAISE;
1401
1402 END READY_TO_PROCURE;
1403
1404 PROCEDURE LAUNCH_SHIP
1405 ( ItemType IN VARCHAR2
1406 , ItemKey IN VARCHAR2
1407 , ActID IN NUMBER
1408 , FuncMode IN VARCHAR2
1409 , ResultOut OUT NOCOPY VARCHAR2
1410 )
1411 IS
1412 L_Deliverable_Id Number;
1413
1414 L_Ship_To_Location_Id Number;
1415 L_Ship_To_Location Varchar2(80);
1416 L_Requestor Varchar2(80);
1417 L_WorkDate Date;
1418 L_Id Number;
1419 L_Po_Id Number;
1420 L_Delivery_Id Number;
1421
1422 L_Msg_Count Number;
1423 L_Msg_Data Varchar2(2000);
1424 L_Item Varchar2(240);
1425 L_Org Varchar2(240);
1426 L_Contract_Number Varchar2(450);
1427 L_Inventory_Org_Id Number;
1428 L_Header_Id Number;
1429 L_Line_Number Varchar2(450);
1430 L_Return_Status Varchar2(1);
1431 Debug_Counter Number ;
1432 Counter Number ;
1433
1434 Cursor Wsh_C3(P_Id Number) Is
1435 Select B.Contract_Number
1436 , B.Currency_Code
1437 , B.Buy_Or_Sell
1438 , H.Country_Of_Origin_Code
1439 , Deliverable_Id
1440 , Deliverable_Num
1441 , D.Inspection_Req_Flag
1442 , D.Item_Id
1443 , Decode(D.Item_Id, Null, Null, I.Name) Item
1444 , Decode(D.Item_Id, Null, Null, I.Description) Item_Description
1445 , D.Inventory_Org_Id
1446 , D.Project_Id
1447 , P.Segment1 Project_Number
1448 , D.Quantity
1449 , D.Expected_Shipment_Date
1450 , D.Ndb_Schedule_Designator
1451 , D.ship_to_location_id
1452 , D.Task_Id
1453 , T.Task_Number
1454 , S.Sts_Code
1455 , D.Unit_Number
1456 , D.Uom_Code
1457 , D.Dependency_Flag
1458 , D.K_Line_Id
1459 , D.Mps_Transaction_Id
1460 , D.Ship_From_Org_Id
1461 , D.Ship_To_Org_Id
1462 , D.Direction
1463 From oke_k_deliverables_b d
1464 , pa_projects_all p
1465 , pa_tasks t
1466 , oke_system_items_v i
1467 , oke_k_headers h
1468 , okc_k_headers_b b
1469 , okc_k_lines_b s
1470 Where D.Deliverable_Id = P_Id
1471 And B.Id = D.K_Header_Id
1472 And H.K_Header_Id = B.Id
1473 And D.Project_Id = P.Project_Id(+)
1474 And D.Task_Id = T.Task_Id(+)
1475 And D.Item_Id = I.Id1(+)
1476 And D.Inventory_Org_Id = I.Id2(+)
1477 And D.K_Line_Id = S.Id
1478 And D.Available_For_Ship_Flag = 'Y';
1479
1480 Wsh_Rec Wsh_C3%ROWTYPE;
1481
1482 Cursor Org_C(P_Id Number) Is
1483 Select Name
1484 From hr_all_organization_units
1485 Where Organization_Id = P_Id;
1486
1487 BEGIN
1488
1489 L_Return_Status := Oke_Api.G_Ret_Sts_Success;
1490 Debug_Counter := 0;
1491 Counter := 0;
1492
1493 IF ( FuncMode = 'RUN' ) THEN
1494
1495 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
1496 ( ItemType => ItemType
1497 , ItemKey => ItemKey
1498 , AName => 'DELIVERABLE_ID'
1499 );
1500
1501 --------------------------------------------------------------
1502 L_Header_ID := WF_Engine.GetItemAttrNumber
1503 ( ItemType => ItemType
1504 , ItemKey => ItemKey
1505 , AName => 'K_HEADER_ID'
1506 );
1507
1508 L_Contract_Number:= WF_Engine.GetItemAttrText
1509 ( ItemType => ItemType
1510 , ItemKey => ItemKey
1511 , AName => 'K_NUMBER'
1512 );
1513
1514 L_Line_Number:= WF_Engine.GetItemAttrText
1515 ( ItemType => ItemType
1516 , ItemKey => ItemKey
1517 , AName => 'LINE_NUMBER'
1518 );
1519 L_Requestor := WF_Engine.GetItemAttrText
1520 ( ItemType => ItemType
1521 , ItemKey => ItemKey
1522 , AName => 'REQUESTOR'
1523 );
1524 --------------------------------------------------------------
1525 OPEN Wsh_C3(L_Deliverable_Id);
1526 Fetch Wsh_C3 into Wsh_Rec;
1527 IF Wsh_C3%FOUND Then
1528 L_Inventory_Org_Id := OKE_DTS_ACTION_PKG.Get_Org(Wsh_Rec.Direction
1529 , Wsh_Rec.Ship_From_Org_Id
1530 , Wsh_Rec.Ship_To_Org_Id);
1531 Open Org_C(L_Inventory_Org_Id);
1532 Fetch Org_C Into L_Org;
1533 Close Org_C;
1534
1535 L_Ship_To_Location := Get_Location(Wsh_Rec.Buy_Or_Sell
1536 , Wsh_Rec.Direction
1537 , Wsh_Rec.Ship_To_Location_Id);
1538
1539
1540 OKE_DTS_INTEGRATION_PKG.Launch_Process
1541 ( P_ACTION => 'SHIP'
1542 , P_API_VERSION => 1
1543 , P_COUNTRY_OF_ORIGIN_CODE => Wsh_Rec.country_of_origin_code
1544 , P_CURRENCY_CODE => Wsh_Rec.CURRENCY_CODE
1545 , P_DELIVERABLE_ID => L_DELIVERABLE_ID
1546 , P_DELIVERABLE_NUM => Wsh_Rec.DELIVERABLE_NUM
1547 , P_INIT_MSG_LIST => 'T'
1548 , P_INSPECTION_REQED => Wsh_Rec.inspection_req_flag
1549 , P_ITEM_DESCRIPTION => Wsh_Rec.ITEM_DESCRIPTION
1550 , P_ITEM_ID => Wsh_Rec.ITEM_ID
1551 , P_ITEM_NUM => Wsh_Rec.ITEM
1552 , P_K_HEADER_ID => L_HEADER_ID
1553 , P_K_NUMBER => L_Contract_Number
1554 , P_LINE_NUMBER => L_LINE_NUMBER
1555 , P_MPS_TRANSACTION_ID => Wsh_Rec.MPS_TRANSACTION_ID
1556 , P_ORGANIZATION => L_ORG
1557 , P_ORGANIZATION_ID => l_inventory_org_id
1558 , P_PROJECT_ID => Wsh_Rec.PROJECT_ID
1559 , P_PROJECT_NUM => Wsh_Rec.PROJECT_NUMBER
1560 , P_QUANTITY => Wsh_Rec.QUANTITY
1561 , P_SCHEDULE_DATE => Wsh_Rec.expected_shipment_date
1562 , P_SCHEDULE_DESIGNATOR => Wsh_Rec.ndb_schedule_designator
1563 , P_SHIP_TO_LOCATION => L_SHIP_TO_LOCATION
1564 , P_TASK_ID => Wsh_Rec.TASK_ID
1565 , P_TASK_NUM => Wsh_Rec.TASK_NUMBER
1566 , P_UNIT_NUMBER => Wsh_Rec.UNIT_NUMBER
1567 , P_UOM_CODE => Wsh_Rec.UOM_CODE
1568 , P_WORK_DATE => L_WORKDATE
1569 , P_REQUESTOR => l_requestor
1570 );
1571
1572 End If;
1573 CLOSE Wsh_C3;
1574 --------------------------------------------------------------
1575
1576 ResultOut := 'COMPLETE:';
1577 RETURN;
1578 END IF;
1579
1580 IF ( FuncMode = 'CANCEL' ) THEN
1581 ResultOut := '';
1582 RETURN;
1583 END IF;
1584
1585 IF ( FuncMode = 'TIMEOUT' ) THEN
1586 ResultOut := '';
1587 RETURN;
1588 END IF;
1589
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 ResultOut := 'ERROR:';
1593 WF_ENGINE.SetItemAttrText
1594 ( ItemType => ItemType
1595 , ItemKey => ItemKey
1596 , AName => 'ERRORTEXT'
1597 , AValue => sqlerrm );
1598 WF_Core.Context
1599 ( 'OKE_DTS_WORKFLOW'
1600 , 'LAUNCH_SHIP'
1601 , ItemType
1602 , ItemKey
1603 , to_char(ActID)
1604 , FuncMode
1605 , ResultOut );
1606 RAISE;
1607
1608 END LAUNCH_SHIP;
1609
1610 PROCEDURE LAUNCH_PLAN
1611 ( ItemType IN VARCHAR2
1612 , ItemKey IN VARCHAR2
1613 , ActID IN NUMBER
1614 , FuncMode IN VARCHAR2
1615 , ResultOut OUT NOCOPY VARCHAR2
1616 )
1617 IS
1618 L_Deliverable_Id Number;
1619
1620 L_Ship_To_Location_Id Number;
1621 L_Ship_To_Location Varchar2(80);
1622 L_Requestor Varchar2(80);
1623 L_WorkDate Date;
1624 L_Id Number;
1625 L_Po_Id Number;
1626 L_Delivery_Id Number;
1627
1628 L_Msg_Count Number;
1629 L_Msg_Data Varchar2(2000);
1630 L_Item Varchar2(240);
1631 L_Org Varchar2(240);
1632 L_Contract_Number Varchar2(450);
1633 L_Inventory_Org_Id Number;
1634 L_Header_Id Number;
1635 L_Line_Number Varchar2(450);
1636 L_Return_Status Varchar2(1);
1637 Debug_Counter Number ;
1638 Counter Number ;
1639
1640 Cursor MDS_C3(P_Id Number) Is
1641 Select B.Contract_Number
1642 , B.Currency_Code
1643 , B.Buy_Or_Sell
1644 , H.Country_Of_Origin_Code
1645 , Deliverable_Id
1646 , Deliverable_Num
1647 , D.Inspection_Req_Flag
1648 , I.Description Item_Description
1649 , D.Item_Id
1650 , I.Name Item
1651 , D.Inventory_Org_Id
1652 , D.Project_Id
1653 , P.Segment1 Project_Number
1654 , D.Quantity
1655 , D.Expected_Shipment_Date
1656 , D.Ndb_Schedule_Designator
1657 , D.ship_to_location_id
1658 , D.Task_Id
1659 , T.Task_Number
1660 , S.Sts_Code
1661 , D.Unit_Number
1662 , D.Uom_Code
1663 , D.Dependency_Flag
1664 , D.K_Line_Id
1665 , D.Mps_Transaction_Id
1666 , D.Ship_From_Org_Id
1667 , D.Ship_To_Org_Id
1668 , D.Direction
1669 From oke_k_deliverables_b d
1670 , pa_projects_all p
1671 , pa_tasks t
1672 , oke_system_items_v i
1673 , oke_k_headers h
1674 , okc_k_headers_b b
1675 , okc_k_lines_b s
1676 Where D.Deliverable_Id = P_Id
1677 And B.Id = D.K_Header_Id
1678 And H.K_Header_Id = B.Id
1679 And D.Project_Id = P.Project_Id(+)
1680 And D.Task_Id = T.Task_Id(+)
1681 And D.Item_Id = I.Id1
1682 And D.Inventory_Org_Id = I.Id2
1683 And D.K_Line_Id = S.Id
1684 And D.Create_Demand = 'Y';
1685
1686 Mds_Rec Mds_C3%ROWTYPE;
1687
1688 Cursor Org_C(P_Id Number) Is
1689 Select Name
1690 From hr_all_organization_units
1691 Where Organization_Id = P_Id;
1692
1693 BEGIN
1694 L_Return_Status := Oke_Api.G_Ret_Sts_Success;
1695 Debug_Counter := 0;
1696 Counter := 0;
1697
1698
1699 IF ( FuncMode = 'RUN' ) THEN
1700
1701 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
1702 ( ItemType => ItemType
1703 , ItemKey => ItemKey
1704 , AName => 'DELIVERABLE_ID'
1705 );
1706 --------------------------------------------------------------
1707 L_Header_ID := WF_Engine.GetItemAttrNumber
1708 ( ItemType => ItemType
1709 , ItemKey => ItemKey
1710 , AName => 'K_HEADER_ID'
1711 );
1712
1713 L_Contract_Number:= WF_Engine.GetItemAttrText
1714 ( ItemType => ItemType
1715 , ItemKey => ItemKey
1716 , AName => 'K_NUMBER'
1717 );
1718
1719 L_Line_Number:= WF_Engine.GetItemAttrText
1720 ( ItemType => ItemType
1721 , ItemKey => ItemKey
1722 , AName => 'LINE_NUMBER'
1723 );
1724 L_Requestor := WF_Engine.GetItemAttrText
1725 ( ItemType => ItemType
1726 , ItemKey => ItemKey
1727 , AName => 'REQUESTOR'
1728 );
1729 --------------------------------------------------------------
1730 OPEN Mds_C3(L_Deliverable_Id);
1731 Fetch Mds_C3 into Mds_Rec;
1732 IF Mds_C3%FOUND Then
1733 L_Inventory_Org_Id := OKE_DTS_ACTION_PKG.Get_Org(Mds_Rec.Direction
1734 , Mds_Rec.Ship_From_Org_Id
1735 , Mds_Rec.Ship_To_Org_Id);
1736
1737 Open Org_C(L_Inventory_Org_Id);
1738 Fetch Org_C Into L_Org;
1739 Close Org_C;
1740
1741 L_Ship_To_Location := Get_Location(Mds_Rec.Buy_Or_Sell
1742 , Mds_Rec.Direction
1743 , Mds_Rec.Ship_To_Location_Id);
1744
1745
1746 OKE_DTS_INTEGRATION_PKG.Launch_Process
1747 ( P_ACTION => 'PLAN'
1748 , P_API_VERSION => 1
1749 , P_COUNTRY_OF_ORIGIN_CODE => Mds_Rec.country_of_origin_code
1750 , P_CURRENCY_CODE => Mds_Rec.CURRENCY_CODE
1751 , P_DELIVERABLE_ID => L_DELIVERABLE_ID
1752 , P_DELIVERABLE_NUM => Mds_Rec.DELIVERABLE_NUM
1753 , P_INIT_MSG_LIST => 'T'
1754 , P_INSPECTION_REQED => Mds_Rec.inspection_req_flag
1755 , P_ITEM_DESCRIPTION => Mds_Rec.ITEM_DESCRIPTION
1756 , P_ITEM_ID => Mds_Rec.ITEM_ID
1757 , P_ITEM_NUM => Mds_Rec.ITEM
1758 , P_K_HEADER_ID => L_HEADER_ID
1759 , P_K_NUMBER => L_Contract_Number
1760 , P_LINE_NUMBER => L_LINE_NUMBER
1761 , P_MPS_TRANSACTION_ID => Mds_Rec.MPS_TRANSACTION_ID
1762 , P_ORGANIZATION => L_ORG
1763 , P_ORGANIZATION_ID => l_inventory_org_id
1764 , P_PROJECT_ID => Mds_Rec.PROJECT_ID
1765 , P_PROJECT_NUM => Mds_Rec.PROJECT_NUMBER
1766 , P_QUANTITY => Mds_Rec.QUANTITY
1767 , P_SCHEDULE_DATE => Mds_Rec.expected_shipment_date
1768 , P_SCHEDULE_DESIGNATOR => Mds_Rec.ndb_schedule_designator
1769 , P_SHIP_TO_LOCATION => L_SHIP_TO_LOCATION
1770 , P_TASK_ID => Mds_Rec.TASK_ID
1771 , P_TASK_NUM => Mds_Rec.TASK_NUMBER
1772 , P_UNIT_NUMBER => Mds_Rec.UNIT_NUMBER
1773 , P_UOM_CODE => Mds_Rec.UOM_CODE
1774 , P_WORK_DATE => L_WORKDATE
1775 , P_REQUESTOR => l_requestor
1776 );
1777 End If;
1778 CLOSE Mds_C3;
1779
1780 ResultOut := 'COMPLETE:';
1781 RETURN;
1782 END IF;
1783
1784 IF ( FuncMode = 'CANCEL' ) THEN
1785 ResultOut := '';
1786 RETURN;
1787 END IF;
1788
1789 IF ( FuncMode = 'TIMEOUT' ) THEN
1790 ResultOut := '';
1791 RETURN;
1792 END IF;
1793
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 ResultOut := 'ERROR:';
1797 WF_ENGINE.SetItemAttrText
1798 ( ItemType => ItemType
1799 , ItemKey => ItemKey
1800 , AName => 'ERRORTEXT'
1801 , AValue => sqlerrm );
1802 WF_Core.Context
1803 ( 'OKE_DTS_WORKFLOW'
1804 , 'LAUNCH_PLAN'
1805 , ItemType
1806 , ItemKey
1807 , to_char(ActID)
1808 , FuncMode
1809 , ResultOut );
1810 RAISE;
1811
1812 END LAUNCH_PLAN;
1813
1814 PROCEDURE LAUNCH_REQ
1815 ( ItemType IN VARCHAR2
1816 , ItemKey IN VARCHAR2
1817 , ActID IN NUMBER
1818 , FuncMode IN VARCHAR2
1819 , ResultOut OUT NOCOPY VARCHAR2
1820 )
1821 IS
1822 L_Deliverable_Id Number;
1823
1824 L_Ship_To_Location_Id Number;
1825 L_Ship_To_Location Varchar2(80);
1826 L_Requestor Varchar2(80);
1827 L_WorkDate Date;
1828 L_Id Number;
1829 L_Po_Id Number;
1830 L_Delivery_Id Number;
1831
1832 L_Msg_Count Number;
1833 L_Msg_Data Varchar2(2000);
1834 L_Item Varchar2(240);
1835 L_Org Varchar2(240);
1836 L_Contract_Number Varchar2(450);
1837 L_Inventory_Org_Id Number;
1838 L_Header_Id Number;
1839 L_Line_Number Varchar2(450);
1840 L_Return_Status Varchar2(1);
1841 Debug_Counter Number ;
1842 Counter Number ;
1843
1844 Cursor PO_C3(P_Id Number) Is
1845 Select B.Contract_Number
1846 , B.Currency_Code
1847 , B.Buy_Or_Sell
1848 , H.Country_Of_Origin_Code
1849 , Deliverable_Id
1850 , Deliverable_Num
1851 , D.Inspection_Req_Flag
1852 , D.Item_Id
1853 , Decode(D.Item_Id, Null, Null, I.Name) Item
1854 , Decode(D.Item_Id, Null, Null, I.Description) Item_Description
1855 , D.Inventory_Org_Id
1856 , D.Project_Id
1857 , P.Segment1 Project_Number
1858 , D.Quantity
1859 , D.Expected_Shipment_Date
1860 , D.Ndb_Schedule_Designator
1861 , D.ship_to_location_id
1862 , D.Task_Id
1863 , T.Task_Number
1864 , S.Sts_Code
1865 , D.Unit_Number
1866 , D.Uom_Code
1867 , D.Dependency_Flag
1868 , D.K_Line_Id
1869 , D.Mps_Transaction_Id
1870 , D.Ship_From_Org_Id
1871 , D.Ship_To_Org_Id
1872 , D.Direction
1873 From oke_k_deliverables_b d
1874 , pa_projects_all p
1875 , pa_tasks t
1876 , oke_system_items_v i
1877 , oke_k_headers h
1878 , okc_k_headers_b b
1879 , okc_k_lines_b s
1880 Where D.Deliverable_Id = P_Id
1881 And B.Id = D.K_Header_Id
1882 And H.K_Header_Id = B.Id
1883 And D.Project_Id = P.Project_Id(+)
1884 And D.Task_Id = T.Task_Id(+)
1885 And D.Item_Id = I.Id1(+)
1886 And D.Inventory_Org_Id = I.Id2(+)
1887 And D.K_Line_Id = S.Id
1888 And D.Ready_To_Procure = 'Y';
1889
1890 Po_Rec Po_C3%ROWTYPE;
1891
1892 Cursor Org_C(P_Id Number) Is
1893 Select Name
1894 From hr_all_organization_units
1895 Where Organization_Id = P_Id;
1896
1897 BEGIN
1898 L_Return_Status := Oke_Api.G_Ret_Sts_Success;
1899 Debug_Counter := 0;
1900 Counter := 0;
1901
1902 IF ( FuncMode = 'RUN' ) THEN
1903
1904 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
1905 ( ItemType => ItemType
1906 , ItemKey => ItemKey
1907 , AName => 'DELIVERABLE_ID'
1908 );
1909 --------------------------------------------------------------
1910 L_Header_ID := WF_Engine.GetItemAttrNumber
1911 ( ItemType => ItemType
1912 , ItemKey => ItemKey
1913 , AName => 'K_HEADER_ID'
1914 );
1915
1916 L_Contract_Number:= WF_Engine.GetItemAttrText
1917 ( ItemType => ItemType
1918 , ItemKey => ItemKey
1919 , AName => 'K_NUMBER'
1920 );
1921
1922 L_Line_Number:= WF_Engine.GetItemAttrText
1923 ( ItemType => ItemType
1924 , ItemKey => ItemKey
1925 , AName => 'LINE_NUMBER'
1926 );
1927 L_Requestor := WF_Engine.GetItemAttrText
1928 ( ItemType => ItemType
1929 , ItemKey => ItemKey
1930 , AName => 'REQUESTOR'
1931 );
1932 --------------------------------------------------------------
1933 OPEN Po_C3(L_Deliverable_Id);
1934 Fetch Po_C3 into Po_Rec;
1935 IF Po_C3%FOUND Then
1936 L_Inventory_Org_Id := OKE_DTS_ACTION_PKG.Get_Org(Po_Rec.Direction
1937 , Po_Rec.Ship_From_Org_Id
1938 , Po_Rec.Ship_To_Org_Id);
1939
1940 Open Org_C(L_Inventory_Org_Id);
1941 Fetch Org_C Into L_Org;
1942 Close Org_C;
1943
1944 L_Ship_To_Location := Get_Location(Po_Rec.Buy_Or_Sell
1945 , Po_Rec.Direction
1946 , Po_Rec.Ship_To_Location_Id);
1947
1948
1949 OKE_DTS_INTEGRATION_PKG.Launch_Process
1950 ( P_ACTION => 'REQ'
1951 , P_API_VERSION => 1
1952 , P_COUNTRY_OF_ORIGIN_CODE => Po_Rec.country_of_origin_code
1953 , P_CURRENCY_CODE => Po_Rec.CURRENCY_CODE
1954 , P_DELIVERABLE_ID => L_DELIVERABLE_ID
1955 , P_DELIVERABLE_NUM => Po_Rec.DELIVERABLE_NUM
1956 , P_INIT_MSG_LIST => 'T'
1957 , P_INSPECTION_REQED => Po_Rec.inspection_req_flag
1958 , P_ITEM_DESCRIPTION => Po_Rec.ITEM_DESCRIPTION
1959 , P_ITEM_ID => Po_Rec.ITEM_ID
1960 , P_ITEM_NUM => Po_Rec.ITEM
1961 , P_K_HEADER_ID => L_HEADER_ID
1962 , P_K_NUMBER => L_Contract_Number
1963 , P_LINE_NUMBER => L_LINE_NUMBER
1964 , P_MPS_TRANSACTION_ID => Po_Rec.MPS_TRANSACTION_ID
1965 , P_ORGANIZATION => L_ORG
1966 , P_ORGANIZATION_ID => l_inventory_org_id
1967 , P_PROJECT_ID => Po_Rec.PROJECT_ID
1968 , P_PROJECT_NUM => Po_Rec.PROJECT_NUMBER
1969 , P_QUANTITY => Po_Rec.QUANTITY
1970 , P_SCHEDULE_DATE => Po_Rec.expected_shipment_date
1971 , P_SCHEDULE_DESIGNATOR => Po_Rec.ndb_schedule_designator
1972 , P_SHIP_TO_LOCATION => L_SHIP_TO_LOCATION
1973 , P_TASK_ID => Po_Rec.TASK_ID
1974 , P_TASK_NUM => Po_Rec.TASK_NUMBER
1975 , P_UNIT_NUMBER => Po_Rec.UNIT_NUMBER
1976 , P_UOM_CODE => Po_Rec.UOM_CODE
1977 , P_WORK_DATE => L_WORKDATE
1978 , P_REQUESTOR => l_requestor
1979 );
1980
1981 End If; -- Operation Check
1982 Close Po_C3;
1983
1984
1985
1986 ResultOut := 'COMPLETE:';
1987 RETURN;
1988 END IF;
1989
1990 IF ( FuncMode = 'CANCEL' ) THEN
1991 ResultOut := '';
1992 RETURN;
1993 END IF;
1994
1995 IF ( FuncMode = 'TIMEOUT' ) THEN
1996 ResultOut := '';
1997 RETURN;
1998 END IF;
1999
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 ResultOut := 'ERROR:';
2003 WF_ENGINE.SetItemAttrText
2004 ( ItemType => ItemType
2005 , ItemKey => ItemKey
2006 , AName => 'ERRORTEXT'
2007 , AValue => sqlerrm );
2008 WF_Core.Context
2009 ( 'OKE_DTS_WORKFLOW'
2010 , 'LAUNCH_REQ'
2011 , ItemType
2012 , ItemKey
2013 , to_char(ActID)
2014 , FuncMode
2015 , ResultOut );
2016 RAISE;
2017
2018 END LAUNCH_REQ;
2019
2020
2021 PROCEDURE READY_TO_COMPLETE
2022 ( ItemType IN VARCHAR2
2023 , ItemKey IN VARCHAR2
2024 , ActID IN NUMBER
2025 , FuncMode IN VARCHAR2
2026 , ResultOut OUT NOCOPY VARCHAR2
2027 )
2028 IS
2029 L_Deliverable_ID NUMBER;
2030 L_Completed_Flag VARCHAR2(1);
2031 BEGIN
2032 IF ( FuncMode = 'RUN' ) THEN
2033
2034 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
2035 ( ItemType => ItemType
2036 , ItemKey => ItemKey
2037 , AName => 'DELIVERABLE_ID'
2038 );
2039
2040 BEGIN
2041 SELECT COMPLETED_FLAG
2042 INTO L_Completed_Flag
2043 FROM OKE_K_DELIVERABLES_VL
2044 WHERE DELIVERABLE_ID=L_Deliverable_ID;
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 NULL;
2048 END;
2049
2050
2051 IF L_Completed_Flag = 'Y' THEN
2052 ResultOut := 'COMPLETE:T';
2053 ELSE
2054 ResultOut := 'COMPLETE:F';
2055 END IF;
2056 RETURN;
2057 END IF;
2058
2059 IF ( FuncMode = 'CANCEL' ) THEN
2060 ResultOut := '';
2061 RETURN;
2062 END IF;
2063
2064 IF ( FuncMode = 'TIMEOUT' ) THEN
2065 ResultOut := '';
2066 RETURN;
2067 END IF;
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 ResultOut := 'ERROR:';
2072 WF_ENGINE.SetItemAttrText
2073 ( ItemType => ItemType
2074 , ItemKey => ItemKey
2075 , AName => 'ERRORTEXT'
2076 , AValue => sqlerrm );
2077 WF_Core.Context
2078 ( 'OKE_DTS_WORKFLOW'
2079 , 'READY_TO_COMPLETE'
2080 , ItemType
2081 , ItemKey
2082 , to_char(ActID)
2083 , FuncMode
2084 , ResultOut );
2085 RAISE;
2086
2087 END READY_TO_COMPLETE;
2088
2089
2090 PROCEDURE REQ_EXISTED
2091 ( ItemType IN VARCHAR2
2092 , ItemKey IN VARCHAR2
2093 , ActID IN NUMBER
2094 , FuncMode IN VARCHAR2
2095 , ResultOut OUT NOCOPY VARCHAR2
2096 )
2097 IS
2098 L_Deliverable_ID NUMBER;
2099 L_Value NUMBER;
2100 L_Action VARCHAR2(25);
2101
2102 -- bug 3795796 Add deliver_to_location_id condition to have index key
2103 l_ship_to_location_id NUMBER;
2104
2105 CURSOR L IS
2106 SELECT SHIP_TO_LOCATION_ID
2107 FROM OKE_K_DELIVERABLES_B
2108 WHERE Deliverable_ID = L_Deliverable_ID;
2109
2110 CURSOR C IS
2111 SELECT PO_Ref_1
2112 FROM OKE_K_DELIVERABLES_B
2113 WHERE Deliverable_ID = L_Deliverable_ID
2114 AND NOT EXISTS ( SELECT 1
2115 FROM po_requisitions_interface_all
2116 WHERE deliver_to_location_id = l_ship_to_location_id
2117 AND oke_contract_deliverable_id = l_deliverable_id
2118 AND process_flag = 'ERROR' );
2119
2120 BEGIN
2121 IF ( FuncMode = 'RUN' ) THEN
2122
2123 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
2124 ( ItemType => ItemType
2125 , ItemKey => ItemKey
2126 , AName => 'DELIVERABLE_ID'
2127 );
2128
2129 OPEN L;
2130 FETCH L INTO L_Ship_To_Location_ID;
2131 CLOSE L;
2132
2133 OPEN C;
2134 FETCH C INTO L_Value;
2135 CLOSE C;
2136
2137 IF L_Value > 0 THEN
2138
2139 ResultOut := 'COMPLETE:T';
2140 ELSE
2141 ResultOut := 'COMPLETE:F';
2142 END IF;
2143 RETURN;
2144 END IF;
2145
2146 IF ( FuncMode = 'CANCEL' ) THEN
2147 ResultOut := '';
2148 RETURN;
2149 END IF;
2150
2151 IF ( FuncMode = 'TIMEOUT' ) THEN
2152 ResultOut := '';
2153 RETURN;
2154 END IF;
2155
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 ResultOut := 'ERROR:';
2159 WF_ENGINE.SetItemAttrText
2160 ( ItemType => ItemType
2161 , ItemKey => ItemKey
2162 , AName => 'ERRORTEXT'
2163 , AValue => sqlerrm );
2164 WF_Core.Context
2165 ( 'OKE_DTS_WORKFLOW'
2166 , 'REQ_EXISTED'
2167 , ItemType
2168 , ItemKey
2169 , to_char(ActID)
2170 , FuncMode
2171 , ResultOut );
2172 RAISE;
2173
2174 END REQ_EXISTED;
2175
2176 PROCEDURE SHIP_EXISTED
2177 ( ItemType IN VARCHAR2
2178 , ItemKey IN VARCHAR2
2179 , ActID IN NUMBER
2180 , FuncMode IN VARCHAR2
2181 , ResultOut OUT NOCOPY VARCHAR2
2182 )
2183 IS
2184 L_Deliverable_ID NUMBER;
2185 L_Value NUMBER;
2186 L_Action VARCHAR2(25);
2187
2188 CURSOR C IS
2189 SELECT Shipping_Request_ID
2190 FROM OKE_K_DELIVERABLES_B
2191 WHERE Deliverable_ID = L_Deliverable_ID;
2192
2193 BEGIN
2194 IF ( FuncMode = 'RUN' ) THEN
2195
2196 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
2197 ( ItemType => ItemType
2198 , ItemKey => ItemKey
2199 , AName => 'DELIVERABLE_ID'
2200 );
2201 OPEN C;
2202 FETCH C INTO L_Value;
2203 CLOSE C;
2204
2205 IF L_Value > 0 THEN
2206
2207 ResultOut := 'COMPLETE:T';
2208 ELSE
2209 ResultOut := 'COMPLETE:F';
2210 END IF;
2211 RETURN;
2212 END IF;
2213
2214 IF ( FuncMode = 'CANCEL' ) THEN
2215 ResultOut := '';
2216 RETURN;
2217 END IF;
2218
2219 IF ( FuncMode = 'TIMEOUT' ) THEN
2220 ResultOut := '';
2221 RETURN;
2222 END IF;
2223
2224 EXCEPTION
2225 WHEN OTHERS THEN
2226 ResultOut := 'ERROR:';
2227 WF_ENGINE.SetItemAttrText
2228 ( ItemType => ItemType
2229 , ItemKey => ItemKey
2230 , AName => 'ERRORTEXT'
2231 , AValue => sqlerrm );
2232 WF_Core.Context
2233 ( 'OKE_DTS_WORKFLOW'
2234 , 'SHIP_EXISTED'
2235 , ItemType
2236 , ItemKey
2237 , to_char(ActID)
2238 , FuncMode
2239 , ResultOut );
2240 RAISE;
2241
2242 END SHIP_EXISTED;
2243
2244 PROCEDURE PLAN_EXISTED
2245 ( ItemType IN VARCHAR2
2246 , ItemKey IN VARCHAR2
2247 , ActID IN NUMBER
2248 , FuncMode IN VARCHAR2
2249 , ResultOut OUT NOCOPY VARCHAR2
2250 )
2251 IS
2252 L_Deliverable_ID NUMBER;
2253 L_Value NUMBER;
2254 L_Action VARCHAR2(25);
2255
2256 CURSOR C IS
2257 SELECT Mps_Transaction_ID
2258 FROM OKE_K_DELIVERABLES_B
2259 WHERE Deliverable_ID = L_Deliverable_ID;
2260
2261 BEGIN
2262 IF ( FuncMode = 'RUN' ) THEN
2263
2264 L_Deliverable_ID := WF_Engine.GetItemAttrNumber
2265 ( ItemType => ItemType
2266 , ItemKey => ItemKey
2267 , AName => 'DELIVERABLE_ID'
2268 );
2269 OPEN C;
2270 FETCH C INTO L_Value;
2271 CLOSE C;
2272
2273 IF L_Value > 0 THEN
2274
2275 ResultOut := 'COMPLETE:T';
2276 ELSE
2277 ResultOut := 'COMPLETE:F';
2278 END IF;
2279 RETURN;
2280 END IF;
2281
2282 IF ( FuncMode = 'CANCEL' ) THEN
2283 ResultOut := '';
2284 RETURN;
2285 END IF;
2286
2287 IF ( FuncMode = 'TIMEOUT' ) THEN
2288 ResultOut := '';
2289 RETURN;
2290 END IF;
2291
2292 EXCEPTION
2293 WHEN OTHERS THEN
2294 ResultOut := 'ERROR:';
2295 WF_ENGINE.SetItemAttrText
2296 ( ItemType => ItemType
2297 , ItemKey => ItemKey
2298 , AName => 'ERRORTEXT'
2299 , AValue => sqlerrm );
2300 WF_Core.Context
2301 ( 'OKE_DTS_WORKFLOW'
2302 , 'PLAN_EXISTED'
2303 , ItemType
2304 , ItemKey
2305 , to_char(ActID)
2306 , FuncMode
2307 , ResultOut );
2308 RAISE;
2309
2310 END PLAN_EXISTED;
2311
2312
2313 PROCEDURE ABORT_PROCESS
2314 ( P_DELIVERABLE_ID IN NUMBER
2315 )
2316 IS
2317 L_WF_Item_Key VARCHAR2(240);
2318
2319 CURSOR C IS
2320 SELECT WF_ITEM_KEY
2321 FROM OKE_K_DELIVERABLES_B
2322 WHERE Deliverable_ID = P_Deliverable_ID;
2323
2324 BEGIN
2325 OPEN C;
2326 FETCH C INTO L_WF_Item_Key;
2327 CLOSE C;
2328
2329 IF L_WF_Item_Key IS NOT NULL THEN
2330 WF_ENGINE.AbortProcess('OKEDTS',L_WF_Item_Key);
2331 END IF;
2332 EXCEPTION
2333 WHEN OTHERS THEN
2334 NULL;
2335 END ABORT_PROCESS;
2336
2337 PROCEDURE PERFORMER_EXISTED
2338 ( ItemType IN VARCHAR2
2339 , ItemKey IN VARCHAR2
2340 , ActID IN NUMBER
2341 , FuncMode IN VARCHAR2
2342 , ResultOut OUT NOCOPY VARCHAR2
2343 )
2344 IS
2345 L_Performer VARCHAR2(80);
2346
2347 BEGIN
2348 IF ( FuncMode = 'RUN' ) THEN
2349
2350 L_Performer := WF_Engine.GetItemAttrText
2351 ( ItemType => ItemType
2352 , ItemKey => ItemKey
2353 , AName => 'PERFORMER'
2354 );
2355
2356 IF L_Performer is null THEN
2357 ResultOut := 'COMPLETE:F';
2358 ELSE
2359 ResultOut := 'COMPLETE:T';
2360 END IF;
2361
2362 RETURN;
2363 END IF;
2364
2365 IF ( FuncMode = 'CANCEL' ) THEN
2366 ResultOut := '';
2367 RETURN;
2368 END IF;
2369
2370 IF ( FuncMode = 'TIMEOUT' ) THEN
2371 ResultOut := '';
2372 RETURN;
2373 END IF;
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 ResultOut := 'ERROR:';
2378 WF_ENGINE.SetItemAttrText
2379 ( ItemType => ItemType
2380 , ItemKey => ItemKey
2381 , AName => 'ERRORTEXT'
2382 , AValue => sqlerrm );
2383 WF_Core.Context
2384 ( 'OKE_DTS_WORKFLOW'
2385 , 'PERFORMER_EXISTED'
2386 , ItemType
2387 , ItemKey
2388 , to_char(ActID)
2389 , FuncMode
2390 , ResultOut );
2391 RAISE;
2392
2393 END PERFORMER_EXISTED;
2394
2395 PROCEDURE ELIGIBLE_TO_SEND
2396 ( ItemType IN VARCHAR2
2397 , ItemKey IN VARCHAR2
2398 , ActID IN NUMBER
2399 , FuncMode IN VARCHAR2
2400 , ResultOut OUT NOCOPY VARCHAR2
2401 )
2402 IS
2403 L_K_Header_ID NUMBER;
2404 L_Due_Ntf_Id NUMBER;
2405 L_Performer VARCHAR2(80);
2406
2407 CURSOR CSR_DUE_NTF(P_DUE_NTF_ID NUMBER) IS
2408 SELECT ID
2409 ,SOURCE_CODE
2410 ,USAGE_CODE
2411 ,TARGET_DATE
2412 ,BEFORE_AFTER
2413 ,DURATION_DAYS
2414 ,RECIPIENT
2415 ,ROLE_ID
2416 FROM OKE_NOTIFICATIONS
2417 WHERE ID=P_DUE_NTF_ID
2418 ;
2419 REC_DUE_NTF CSR_DUE_NTF%ROWTYPE;
2420
2421 -- CURSOR CSR_ESC(P_K_HEADER_ID Number,P_ROLE_ID Number) IS
2422 -- SELECT R.NAME
2423 -- FROM OKE_K_ALL_ACCESS_V A
2424 -- ,WF_ROLES R
2425 -- WHERE A. K_HEADER_ID = P_K_HEADER_ID
2426 -- AND A.ROLE_ID = P_ROLE_ID
2427 -- AND R.ORIG_SYSTEM = 'PER'
2428 -- AND R.ORIG_SYSTEM_ID = A.PERSON_ID
2429 -- ORDER BY DECODE(ASSIGNMENT_LEVEL,'OKE_K_HEADERS',1
2430 -- ,'SITE',2
2431 -- ,3)
2432 -- ;
2433
2434 BEGIN
2435 IF ( FuncMode = 'RUN' ) THEN
2436
2437 L_K_Header_ID := WF_Engine.GetItemAttrNumber
2438 ( ItemType => ItemType
2439 , ItemKey => ItemKey
2440 , AName => 'K_HEADER_ID'
2441 );
2442
2443 L_Due_Ntf_Id := WF_Engine.GetItemAttrNumber
2444 ( ItemType => ItemType
2445 , ItemKey => ItemKey
2446 , AName => 'DUE_NTF_ID'
2447 );
2448
2449 OPEN CSR_DUE_NTF(L_Due_Ntf_Id);
2450 FETCH CSR_DUE_NTF INTO REC_DUE_NTF;
2451 CLOSE CSR_DUE_NTF;
2452
2453 IF REC_DUE_NTF.ID IS NULL THEN
2454 WF_ENGINE.SetItemAttrNumber
2455 ( ItemType => ItemType
2456 , ItemKey => ItemKey
2457 , AName => 'DUE_NTF_ID'
2458 , AValue => NULL );
2459
2460 ResultOut := 'COMPLETE:F';
2461 RETURN;
2462 ELSE
2463 WF_ENGINE.SetItemAttrNumber
2464 ( ItemType => ItemType
2465 , ItemKey => ItemKey
2466 , AName => 'DUE_NTF_ID'
2467 , AValue => REC_DUE_NTF.ID );
2468
2469 WF_ENGINE.SetItemAttrText
2470 ( ItemType => ItemType
2471 , ItemKey => ItemKey
2472 , AName => 'SOURCE_CODE'
2473 , AValue => REC_DUE_NTF.SOURCE_CODE );
2474
2475 WF_ENGINE.SetItemAttrText
2476 ( ItemType => ItemType
2477 , ItemKey => ItemKey
2478 , AName => 'USAGE_CODE'
2479 , AValue => REC_DUE_NTF.USAGE_CODE );
2480
2481 WF_ENGINE.SetItemAttrText
2482 ( ItemType => ItemType
2483 , ItemKey => ItemKey
2484 , AName => 'TARGET_DATE'
2485 , AValue => REC_DUE_NTF.TARGET_DATE );
2486
2487 WF_ENGINE.SetItemAttrText
2488 ( ItemType => ItemType
2489 , ItemKey => ItemKey
2490 , AName => 'BEFORE_AFTER'
2491 , AValue => REC_DUE_NTF.BEFORE_AFTER );
2492
2493 WF_ENGINE.SetItemAttrNumber
2494 ( ItemType => ItemType
2495 , ItemKey => ItemKey
2496 , AName => 'DURATION_DAYS'
2497 , AValue => REC_DUE_NTF.DURATION_DAYS );
2498
2499 WF_ENGINE.SetItemAttrText
2500 ( ItemType => ItemType
2501 , ItemKey => ItemKey
2502 , AName => 'RECIPIENT'
2503 , AValue => REC_DUE_NTF.RECIPIENT );
2504
2505 WF_ENGINE.SetItemAttrNumber
2506 ( ItemType => ItemType
2507 , ItemKey => ItemKey
2508 , AName => 'ROLE_ID'
2509 , AValue => REC_DUE_NTF.ROLE_ID );
2510
2511 IF REC_DUE_NTF.RECIPIENT='REQUESTOR' THEN
2512 WF_ENGINE.SetItemAttrText
2513 ( ItemType => ItemType
2514 , ItemKey => ItemKey
2515 , AName => 'PERFORMER'
2516 , AValue => FND_GLOBAL.User_Name);
2517 ELSIF REC_DUE_NTF.RECIPIENT='CONTRACT_ROLE' THEN
2518 -- OPEN CSR_ESC(L_K_Header_ID,REC_DUE_NTF.ROLE_ID);
2519 -- FETCH CSR_ESC INTO L_Performer;
2520 -- CLOSE CSR_ESC;
2521
2522 L_Performer := OKE_UTILS.Retrieve_WF_Role_Name(L_K_Header_ID,REC_DUE_NTF.ROLE_ID);
2523
2524 IF L_Performer IS NULL THEN
2525 ResultOut := 'COMPLETE:F';
2526 RETURN;
2527 ELSE
2528 WF_ENGINE.SetItemAttrText
2529 ( ItemType => ItemType
2530 , ItemKey => ItemKey
2531 , AName => 'PERFORMER'
2532 , AValue => L_Performer );
2533 END IF;
2534
2535 ELSE
2536 WF_ENGINE.SetItemAttrText
2537 ( ItemType => ItemType
2538 , ItemKey => ItemKey
2539 , AName => 'PERFORMER'
2540 , AValue => FND_GLOBAL.User_Name);
2541 END IF;
2542
2543 ResultOut := 'COMPLETE:T';
2544 RETURN;
2545 END IF;
2546 END IF;
2547
2548 IF ( FuncMode = 'CANCEL' ) THEN
2549 ResultOut := '';
2550 RETURN;
2551 END IF;
2552
2553 IF ( FuncMode = 'TIMEOUT' ) THEN
2554 ResultOut := '';
2555 RETURN;
2556 END IF;
2557
2558 EXCEPTION
2559 WHEN OTHERS THEN
2560 ResultOut := 'ERROR:';
2561 WF_ENGINE.SetItemAttrText
2562 ( ItemType => ItemType
2563 , ItemKey => ItemKey
2564 , AName => 'ERRORTEXT'
2565 , AValue => sqlerrm );
2566 WF_Core.Context
2567 ( 'OKE_DTS_WORKFLOW'
2568 , 'ELIGIBLE_TO_SEND'
2569 , ItemType
2570 , ItemKey
2571 , to_char(ActID)
2572 , FuncMode
2573 , ResultOut );
2574 RAISE;
2575
2576 END ELIGIBLE_TO_SEND;
2577
2578 PROCEDURE LESS_THAN_TARGET_DATE
2579 ( ItemType IN VARCHAR2
2580 , ItemKey IN VARCHAR2
2581 , ActID IN NUMBER
2582 , FuncMode IN VARCHAR2
2583 , ResultOut OUT NOCOPY VARCHAR2
2584 )
2585 IS
2586 L_Target_Date_Value DATE;
2587
2588 BEGIN
2589 IF ( FuncMode = 'RUN' ) THEN
2590
2591 L_Target_Date_Value := WF_Engine.GetItemAttrDate
2592 ( ItemType => ItemType
2593 , ItemKey => ItemKey
2594 , AName => 'TARGET_DATE_VALUE'
2595 );
2596
2597 IF Sysdate < L_Target_Date_Value THEN
2598 ResultOut := 'COMPLETE:T';
2599 ELSE
2600 ResultOut := 'COMPLETE:F';
2601 END IF;
2602
2603 RETURN;
2604 END IF;
2605
2606 IF ( FuncMode = 'CANCEL' ) THEN
2607 ResultOut := '';
2608 RETURN;
2609 END IF;
2610
2611 IF ( FuncMode = 'TIMEOUT' ) THEN
2612 ResultOut := '';
2613 RETURN;
2614 END IF;
2615
2616 EXCEPTION
2617 WHEN OTHERS THEN
2618 ResultOut := 'ERROR:';
2619 WF_ENGINE.SetItemAttrText
2620 ( ItemType => ItemType
2621 , ItemKey => ItemKey
2622 , AName => 'ERRORTEXT'
2623 , AValue => sqlerrm );
2624 WF_Core.Context
2625 ( 'OKE_DTS_WORKFLOW'
2626 , 'LESS_THAN_TARGET_DATE'
2627 , ItemType
2628 , ItemKey
2629 , to_char(ActID)
2630 , FuncMode
2631 , ResultOut );
2632 RAISE;
2633
2634 END LESS_THAN_TARGET_DATE;
2635
2636 END OKE_DTS_WORKFLOW;