[Home] [Help]
PACKAGE BODY: APPS.XDPCORE_WI
Source
1 PACKAGE BODY XDPCORE_WI AS
2 /* $Header: XDPCORWB.pls 120.3 2005/07/10 23:47:32 appldev noship $ */
3
4
5 /****
6 All Private Procedures for the Package
7 ****/
8
9 Function HandleOtherWFFuncmode (funcmode in varchar2) return varchar2;
10
11 Function AreAllWIsDone (itemtype in varchar2,
12 itemkey in varchar2) return varchar2;
13
14 Function evaluateWIParams(itemtype in varchar2,
15 itemkey in varchar2) return varchar2;
16
17 Function evaluateAllWIsParams(itemtype in varchar2,
18 itemkey in varchar2) return varchar2;
19
20 Function ContinueWorkitem (itemtype in varchar2,
21 itemkey in varchar2) return varchar2;
22
23 Function LaunchAllIndependentWIs (itemtype in varchar2,
24 itemkey in varchar2) return varchar2;
25
26 Procedure InitDepWIProcess (itemtype in varchar2,
27 itemkey in varchar2);
28 Procedure LaunchWorkitemProcess(itemtype in varchar2,
29 itemkey in varchar2);
30
31 Procedure LaunchIndWorkitemProcess (itemtype in varchar2,
32 itemkey in varchar2,
33 p_OrderID in NUMBER,
34 p_LineItemID in NUMBER,
35 p_WorkitemID in NUMBER,
36 p_WIInstanceID in NUMBER,
37 p_Priority in NUMBER);
38
39 Procedure LaunchWIServiceProcess(itemtype in varchar2,
40 itemkey in varchar2);
41
42 Procedure LaunchWISeqProcess(itemtype in varchar2,
43 itemkey in varchar2);
44
45 Procedure OverrideParamValue( p_WIInstanceID IN NUMBER,
46 p_parameterName IN VARCHAR2,
47 p_newParamValue IN VARCHAR2,
48 p_evaluate IN VARCHAR2);
49
50 Procedure CheckIfWorkItemIsaWorkflow(workitem_ID in number,
51 wf_flag OUT NOCOPY varchar2,
52 user_item_type OUT NOCOPY varchar2,
53 user_item_key_prefix OUT NOCOPY varchar2,
54 user_wf_process_name OUT NOCOPY varchar2,
55 user_wf_proc OUT NOCOPY varchar2,
56 fa_exec_map_proc OUT NOCOPY varchar2);
57
58 Procedure InitializeWorkitemProcess(itemtype in varchar2,
59 itemkey in varchar2);
60
61
62 Function IsWIAborted(WIInstanceID in number) return boolean;
63
64 type RowidArrayType is table of rowid index by binary_integer;
65
66 Procedure GetWIParamOnStart(itemtype in varchar2,
67 itemkey in varchar2);
68
69 Function ResolveIndDepWIs(itemtype in varchar2,
70 itemkey in varchar2) return varchar2;
71
72 PROCEDURE UPDATE_WORKITEM_STATUS(p_workitem_instance_id IN NUMBER,
73 p_status_code IN VARCHAR2,
74 p_itemtype IN VARCHAR2,
75 p_itemkey IN VARCHAR2) ;
76
77 FUNCTION GetWIProvisioningDate(p_workitem_instance_id IN NUMBER) RETURN DATE;
78
79 /***********************************************
80 * END of Private Procedures/Function Definitions
81 ************************************************/
82
83
84
85 -- INITIALIZE_WORKITEM_PROCESS
86 -- Resultout
87 -- Activity Performed - Activity was completed without any errors
88 --
89 -- Your Description here:
90
91 Procedure INITIALIZE_WORKITEM_PROCESS (itemtype in varchar2,
92 itemkey in varchar2,
93 actid in number,
94 funcmode in varchar2,
95 resultout OUT NOCOPY varchar2 ) IS
96
97 x_Progress VARCHAR2(2000);
98
99 BEGIN
100 -- RUN mode - normal process execution
101 --
102 IF (funcmode = 'RUN') THEN
103 InitializeWorkitemProcess(itemtype, itemkey);
104 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
105 return;
106 ELSE
107 resultout := HandleOtherWFFuncmode(funcmode);
108 return;
109 END IF;
110
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 wf_core.context('XDPCORE_WI', 'INITIALIZE_WORKITEM_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
115 raise;
116 END INITIALIZE_WORKITEM_PROCESS;
117
118
119 -- CONTINUE_WORKITEM
120 -- Resultout
121 -- Activity Performed - Activity was completed without any errors
122 --
123 -- Your Description here:
124
125 Procedure CONTINUE_WORKITEM (itemtype in varchar2,
126 itemkey in varchar2,
127 actid in number,
128 funcmode in varchar2,
129 resultout OUT NOCOPY varchar2 ) IS
130 l_Result varchar2(1);
131 x_Progress VARCHAR2(2000);
132
133 BEGIN
134 -- RUN mode - normal process execution
135 --
136 IF (funcmode = 'RUN') THEN
137 l_Result := ContinueWorkitem(itemtype, itemkey);
138 resultout := 'COMPLETE:' || l_Result;
139 return;
140 ELSE
141 resultout := HandleOtherWFFuncmode(funcmode);
142 return;
143 END IF;
144
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 wf_core.context('XDPCORE_WI', 'CONTINUE_WORKITEM', itemtype, itemkey, to_char(actid), funcmode);
149 raise;
150 END CONTINUE_WORKITEM;
151
152
153
154 -- ARE_ALL_WIS_DONE
155 -- Resultout
156 -- Activity Performed - Activity was completed without any errors
157 --
158 -- Your Description here:
159
160 Procedure ARE_ALL_WIS_DONE (itemtype in varchar2,
161 itemkey in varchar2,
162 actid in number,
163 funcmode in varchar2,
164 resultout OUT NOCOPY varchar2 ) IS
165
166 l_result varchar2(10);
167
168 x_Progress VARCHAR2(2000);
169
170 BEGIN
171 -- RUN mode - normal process execution
172 --
173 IF (funcmode = 'RUN') THEN
174 l_result := AreAllWIsDone(itemtype, itemkey);
175 resultout := 'COMPLETE:' || l_result;
176 return;
177 ELSE
178 resultout := HandleOtherWFFuncmode(funcmode);
179 return;
180 END IF;
181
182
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 wf_core.context('XDPCORE_WI', 'ARE_ALL_WIS_DONE', itemtype, itemkey, to_char(actid), funcmode);
187 raise;
188 END ARE_ALL_WIS_DONE;
189
190
191 Procedure EVALUATE_WI_PARAMS (itemtype in varchar2,
192 itemkey in varchar2,
193 actid in number,
194 funcmode in varchar2,
195 resultout OUT NOCOPY varchar2) IS
196
197 l_result varchar2(10);
198 x_Progress VARCHAR2(2000);
199
200 BEGIN
201 -- RUN mode - normal process execution
202 --
203 IF (funcmode = 'RUN') THEN
204 l_result := evaluateWIParams(itemtype, itemkey);
205 resultout := 'COMPLETE:' || l_result;
206 return;
207 ELSE
208 resultout := HandleOtherWFFuncmode(funcmode);
209 return;
210 END IF;
211
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 wf_core.context('XDPCORE_WI', 'EVALUATE_WI_PARAMS', itemtype, itemkey, to_char(actid), funcmode);
216 raise;
217 END EVALUATE_WI_PARAMS;
218
219 Procedure EVALUATE_ALL_WIS_PARAMS (itemtype in varchar2,
220 itemkey in varchar2,
221 actid in number,
222 funcmode in varchar2,
223 resultout OUT NOCOPY varchar2) IS
224
225 l_result varchar2(10);
226 x_Progress VARCHAR2(2000);
227 l_evalFailed exception;
228
229 BEGIN
230 -- RUN mode - normal process execution
231 --
232 IF (funcmode = 'RUN') THEN
233 l_result := evaluateAllWIsParams(itemtype, itemkey);
234 IF ( l_result = 'FAILURE' ) THEN
235 -- on failure do a raise and find out the business error..
236 RAISE l_evalFailed;
237 END IF;
238 resultout := 'COMPLETE:' || l_result;
239 return;
240 ELSE
241 resultout := HandleOtherWFFuncmode(funcmode);
242 return;
243 END IF;
244
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF XDPCORE.is_business_error = 'Y' THEN
249 XDPCORE.context('XDPCORE_WI', 'EVALUATE_ALL_WIS_PARAMS', itemtype, itemkey );
250
251 --Log the error at the top level call
252 XDPCORE_ERROR.LOG_SESSION_ERROR( 'BUSINESS' );
253 --Set the workitem instance id of which evaluation failed..
254 --we cant set this in the lower calls where the error is occurred
255 --as we are doing a roll back on error...
256 wf_engine.SetItemAttrNumber(itemtype => EVALUATE_ALL_WIS_PARAMS.itemtype,
257 itemkey => EVALUATE_ALL_WIS_PARAMS.itemkey,
258 aname => 'WORKITEM_INSTANCE_ID',
259 avalue => g_WIInstance_ID_in_Error);
260
261
262 resultout := 'COMPLETE:FAILURE';
263 return;
264 END IF;
265 wf_core.context('XDPCORE_WI', 'EVALUATE_ALL_WIS_PARAMS', itemtype, itemkey, to_char(actid), funcmode);
266 RAISE;
267 END EVALUATE_ALL_WIS_PARAMS;
268
269
270
271 Procedure LAUNCH_WORKITEM_PROCESS (itemtype in varchar2,
272 itemkey in varchar2,
273 actid in number,
274 funcmode in varchar2,
275 resultout OUT NOCOPY varchar2 ) IS
276
277 x_Progress VARCHAR2(2000);
278
279 BEGIN
280 -- RUN mode - normal process execution
281 --
282 IF (funcmode = 'RUN') THEN
283
284 LaunchWorkitemProcess(itemtype, itemkey);
285 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
286 return;
287 ELSE
288 resultout := HandleOtherWFFuncmode(funcmode);
289 return;
290 END IF;
291
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 wf_core.context('XDPCORE_WI', 'LAUNCH_WORKITEM_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
296 raise;
297 END LAUNCH_WORKITEM_PROCESS;
298
299
300
301 Procedure INITIALIZE_DEP_WI_PROCESS (itemtype in varchar2,
302 itemkey in varchar2,
303 actid in number,
304 funcmode in varchar2,
305 resultout OUT NOCOPY varchar2 ) IS
306
307 x_Progress VARCHAR2(2000);
308
309 BEGIN
310 -- RUN mode - normal process execution
311 --
312 IF (funcmode = 'RUN') THEN
313
314 InitDepWIProcess (itemtype, itemkey);
315 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
316 return;
317 ELSE
318 resultout := HandleOtherWFFuncmode(funcmode);
319 return;
320 END IF;
321
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 wf_core.context('XDPCORE_WI', 'INITIALIZE_DEP_WI_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
326 raise;
327 END INITIALIZE_DEP_WI_PROCESS;
328
329
330
331
332 Procedure LAUNCH_WI_SEQ_PROCESS (itemtype in varchar2,
333 itemkey in varchar2,
334 actid in number,
335 funcmode in varchar2,
336 resultout OUT NOCOPY varchar2 ) IS
337
338 x_Progress VARCHAR2(2000);
339
340 BEGIN
341 -- RUN mode - normal process execution
342 --
343 IF (funcmode = 'RUN') THEN
344
345 LaunchWISeqProcess(itemtype, itemkey);
346 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
347 return;
348 ELSE
349 resultout := HandleOtherWFFuncmode(funcmode);
350 return;
351 END IF;
352
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 wf_core.context('XDPCORE_WI', 'LAUNCH_WI_SEQ_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
357 raise;
358 END LAUNCH_WI_SEQ_PROCESS;
359
360
361
362 Procedure LAUNCH_ALL_IND_WIS (itemtype in varchar2,
363 itemkey in varchar2,
364 actid in number,
365 funcmode in varchar2,
366 resultout OUT NOCOPY varchar2 ) IS
367
368 x_Progress VARCHAR2(2000);
369 l_result varchar2(1) := 'N';
370
371 BEGIN
372 -- RUN mode - normal process execution
373 --
374 IF (funcmode = 'RUN') THEN
375
376 l_result := LaunchAllIndependentWIs(itemtype, itemkey);
377 resultout := 'COMPLETE:' || l_result;
378 return;
379 ELSE
380 resultout := HandleOtherWFFuncmode(funcmode);
381 return;
382 END IF;
383
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 xdpcore.context('XDPCORE_WI', 'LAUNCH_ALL_IND_WIS', itemtype, itemkey, to_char(actid), funcmode);
388 wf_core.context('XDPCORE_WI', 'LAUNCH_ALL_IND_WIS', itemtype, itemkey, to_char(actid), funcmode);
389 raise;
390
391 END LAUNCH_ALL_IND_WIS;
392
393 Procedure LAUNCH_WI_SERVICE_PROCESS (itemtype in varchar2,
394 itemkey in varchar2,
395 actid in number,
396 funcmode in varchar2,
397 resultout OUT NOCOPY varchar2 ) IS
398
399 x_Progress VARCHAR2(2000);
400
401 BEGIN
402 -- RUN mode - normal process execution
403 --
404 IF (funcmode = 'RUN') THEN
405
406 LaunchWIServiceProcess(itemtype, itemkey);
407 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
408 return;
409 ELSE
410 resultout := HandleOtherWFFuncmode(funcmode);
411 return;
412 END IF;
413
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 wf_core.context('XDPCORE_WI', 'LAUNCH_WI_SERVICE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
418 raise;
419 END LAUNCH_WI_SERVICE_PROCESS;
420
421
422
423
424 -- INITIALIZE_WI_LIST
425 -- Resultout
426 -- Activity Performed - Activity was completed without any errors
427 --
428 -- Your Description here:
429
430 Procedure INITIALIZE_WI_LIST (itemtype in varchar2,
431 itemkey in varchar2,
432 actid in number,
433 funcmode in varchar2,
434 resultout OUT NOCOPY varchar2 ) IS
435
436 x_Progress VARCHAR2(2000);
437
438 BEGIN
439 -- RUN mode - normal process execution
440 --
441 IF (funcmode = 'RUN') THEN
442 -- <your procedure here>
443 resultout := 'COMPLETE:<result>';
444 return;
445 ELSE
446 resultout := HandleOtherWFFuncmode(funcmode);
447 return;
448 END IF;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 wf_core.context('XDPCORE_WI', 'INITIALIZE_WI_LIST', itemtype, itemkey, to_char(actid), funcmode);
453 raise;
454 END INITIALIZE_WI_LIST;
455
456
457
458 -- ARE_ALL_WIS_DONE
459 -- Resultout
460 -- Activity Performed - Activity was completed without any errors
461 --
462 -- Your Description here:
463
464
465 Procedure GET_ONSTART_WI_PARAMS (itemtype in varchar2,
466 itemkey in varchar2,
467 actid in number,
468 funcmode in varchar2,
469 resultout OUT NOCOPY varchar2 ) IS
470
471 l_result varchar2(10);
472
473 x_Progress VARCHAR2(2000);
474
475 BEGIN
476 -- RUN mode - normal process execution
477 --
478 IF (funcmode = 'RUN') THEN
479 GetWIParamOnStart(itemtype, itemkey);
480 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
481 return;
482 ELSE
483 resultout := HandleOtherWFFuncmode(funcmode);
484 return;
485 END IF;
486
487
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 wf_core.context('XDPCORE_WI', 'ARE_ALL_WIS_DONE', itemtype, itemkey, to_char(actid), funcmode);
492 raise;
493 END GET_ONSTART_WI_PARAMS;
494
495
496
497 Procedure RESOLVE_IND_DEP_WIS (itemtype in varchar2,
498 itemkey in varchar2,
499 actid in number,
500 funcmode in varchar2,
501 resultout OUT NOCOPY varchar2 ) IS
502
503 l_result varchar2(15);
504
505 x_Progress VARCHAR2(2000);
506
507 BEGIN
508 -- RUN mode - normal process execution
509 --
510 IF (funcmode = 'RUN') THEN
511 l_Result := ResolveIndDepWIs(itemtype, itemkey);
512 resultout := 'COMPLETE:' || l_Result;
513 return;
514 ELSE
515 resultout := HandleOtherWFFuncmode(funcmode);
516 return;
517 END IF;
518
519
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 wf_core.context('XDPCORE_WI', 'RESOLVE_IND_DEP_WIS', itemtype, itemkey, to_char(actid), funcmode);
524 raise;
525 END RESOLVE_IND_DEP_WIS;
526
527 Procedure OVERRIDE_WI_PARAM (itemtype in varchar2,
528 itemkey in varchar2,
529 actid in number,
530 funcmode in varchar2,
531 resultout OUT NOCOPY varchar2 ) IS
532 l_nid NUMBER;
533 l_WIInstanceID NUMBER;
534 l_newParamValue VARCHAR2(100);
535 l_paramName VARCHAR2(100);
536 l_evaluate VARCHAR2(10);
537
538 BEGIN
539
540 IF (funcmode = 'RESPOND') THEN
541 l_nid := WF_ENGINE.context_nid;
542 l_newParamValue := wf_notification.GetAttrText( l_nid, 'NEW_PARAM_VALUE');
543
544 -- User havent entered any overriding parameter value..
545 IF l_newParamValue IS NOT NULL THEN
546 l_WIInstanceID := wf_engine.getItemAttrNumber( itemtype, itemkey, 'WORKITEM_INSTANCE_ID' );
547 l_paramName := wf_engine.getItemAttrText( itemtype, itemkey, 'WI_PARAMETER_NAME' );
548 l_evaluate := wf_notification.GetAttrText( l_nid, 'EVALUATE');
549
550 OverrideParamValue( l_WIInstanceID, l_paramName, l_newParamValue, l_evaluate);
551 END IF;
552
553 END IF;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 wf_core.context('XDPCORE_WI', 'OVERRIDE_WI_PARAM', itemtype, itemkey, to_char(actid), funcmode);
558 raise;
559 END OVERRIDE_WI_PARAM;
560
561 /****
562 All the Private Functions
563 ****/
564
565 Function HandleOtherWFFuncmode( funcmode in varchar2) return varchar2
566 is
567 resultout varchar2(30);
568 x_Progress VARCHAR2(2000);
569
570 begin
571
572 IF (funcmode = 'CANCEL') THEN
573 resultout := 'COMPLETE';
574 END IF;
575
576 IF (funcmode = 'RESPOND') THEN
577 resultout := 'COMPLETE';
578 END IF;
579
580 IF (funcmode = 'FORWARD') THEN
581 resultout := 'COMPLETE';
582 END IF;
583
584 IF (funcmode = 'TRANSFER') THEN
585 resultout := 'COMPLETE';
586 END IF;
587
588 IF (funcmode = 'TIMEOUT') THEN
589 resultout := 'COMPLETE';
590 END IF;
591
592 IF (funcmode = 'others') THEN
593 resultout := 'COMPLETE';
594 END IF;
595
596
597 return resultout;
598
599 end;
600
601 Function evaluateWIParams(itemtype in varchar2,
602 itemkey in varchar2) return varchar2
603 is
604
605 x_Progress VARCHAR2(2000);
606 l_WIInstanceID NUMBER;
607 l_workitemID NUMBER;
608 l_WorkItemName VARCHAR2(50);
609 l_return_flag varchar2(1) := 'Y';
610
611 l_ErrCode number;
612 l_ErrStr varchar2(1996);
613
614 begin
615
616 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => evaluateWIParams.itemtype,
617 itemkey => evaluateWIParams.itemkey,
618 aname => 'WORKITEM_INSTANCE_ID');
619 BEGIN
620 xdp_engine.EvaluateWIParamsOnStart(l_WIInstanceID );
621 EXCEPTION
622 WHEN OTHERS THEN
623 -- Evaluation procedure for this WI failed..
624 SELECT workitem_id INTO l_workitemID FROM xdp_fulfill_worklist WHERE workitem_instance_id = l_WIInstanceID;
625 SELECT workitem_name INTO l_WorkItemName from xdp_workitems WHERE workitem_id = l_workitemID;
626
627 XDPCORE.CheckNAddItemAttrText ( itemtype => evaluateWIParams.itemtype,
628 itemkey => evaluateWIParams.itemkey,
629 AttrName => 'WORKITEM_NAME',
630 AttrValue => l_WorkItemName,
631 ErrCode => l_ErrCode,
632 ErrStr => l_ErrStr);
633 l_return_flag := 'N';
634
635 END;
636 RETURN l_return_flag;
637
638 exception
639 when others then
640 x_Progress := 'XDPCORE_WI.GetWIParamOnStart. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
641 wf_core.context('XDPCORE_WI', 'GetWIParamOnStart', itemtype, itemkey, null, x_Progress);
642 raise;
643 END evaluateWIParams;
644
645
646
647 Function evaluateAllWIsParams(itemtype in varchar2,
648 itemkey in varchar2) return varchar2
649 is
650 l_LineItemID number;
651 l_OrderID number;
652 l_WIInstanceID number;
653 l_WorkitemID number;
654 l_WIsFound number := 0;
655 l_return_flag varchar2(10) := 'SUCCESS';
656 l_tempKey varchar2(240);
657 l_WorkItemName varchar2(40);
658
659
660 cursor c_GetWIList (OrderID number, LineItemID number) is
661 select WORKITEM_INSTANCE_ID
662 from XDP_FULFILL_WORKLIST
663 where ORDER_ID = Order_ID
664 and LINE_ITEM_ID = LineItemID
665 and status_code = 'STANDBY'
666 ORDER BY WI_SEQUENCE;
667
668
669 e_NoWorkitemsFoundException exception;
670 x_Progress VARCHAR2(2000);
671
672 l_ErrCode number;
673 l_ErrStr varchar2(1996);
674
675 cursor c_getVals ( cv_wiid number ) is
676 select parameter_name, parameter_value
677 From xdp_worklist_details
678 where workitem_instance_id = cv_wiid;
679
680 begin
681
682 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => evaluateAllWIsParams.itemtype,
683 itemkey => evaluateAllWIsParams.itemkey,
684 aname => 'ORDER_ID');
685
686 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => evaluateAllWIsParams.itemtype,
687 itemkey => evaluateAllWIsParams.itemkey,
688 aname => 'LINE_ITEM_ID');
689
690 /* Evaluate parameters for all INDEPENDENT workitems */
691 FOR lv_WI_rec in c_GetWIList( l_OrderID, l_LineItemID ) LOOP
692 l_WIsFound := 1;
693 BEGIN
694 l_WIInstanceID := lv_WI_rec.WORKITEM_INSTANCE_ID;
695 xdp_engine.EvaluateWIParamsOnStart(l_WIInstanceID );
696
697 EXCEPTION
698 when others then
699 l_return_flag := 'FAILURE';
700 EXIT;
701 END;
702 END LOOP;
703
704 IF l_return_flag = 'FAILURE' THEN
705 --set the error workitem so that we can access at higer level..
706 g_WIInstance_ID_in_Error := l_WIInstanceID;
707 --Failed to execute set the context and return..
708 XDPCORE.context('XDPCORE_WI', 'evaluateAllWIsParams', 'WI', l_WIInstanceID );
709 RETURN l_return_flag;
710 END IF;
711
712 IF l_WIsFound = 0 THEN
713 x_Progress := 'XDPCORE_WI.evaluateAllWIsParams. Found No workitems to be processed for Order: ' || l_OrderID || ' LineItemID: ' || l_LineItemID;
714 RAISE e_NoWorkitemsFoundException;
715 END IF;
716 -- Evaluation for all WI paramers are successfull...
717 RETURN l_return_flag;
718
719 EXCEPTION
720
721 WHEN e_NoWorkitemsFoundException THEN
722 XDPCORE.context('XDPCORE_WI', 'evaluateAllWIsParams', 'LINE', l_LineItemID, x_Progress );
723 wf_core.context('XDPCORE_WI', 'evaluateAllWIsParams', itemtype, itemkey, null, x_Progress);
724 RAISE;
725
726 WHEN others THEN
727 x_Progress := 'XDPCORE_WI.evaluateAllWIsParams. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
728 XDPCORE.context('XDPCORE_WI', 'evaluateAllWIsParams', 'LINE', l_LineItemID );
729 wf_core.context('XDPCORE_WI', 'evaluateAllWIsParams', itemtype, itemkey, null, x_Progress);
730 raise;
731 END evaluateAllWIsParams;
732
733
734
735 Function AreAllWIsDone (itemtype in varchar2,
736 itemkey in varchar2) return varchar2
737 is
738 l_PrevSequence number;
739 l_OrderID number;
740 l_LineItemID number;
741 l_WorkitemID number;
742 l_FulfillmentSeq number;
743
744 l_Priority number;
745 l_WIInstanceID number;
746
747 e_NoWorkitemsFoundException exception;
748 x_Progress VARCHAR2(2000);
749
750 begin
751
752 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => AreAllWIsDone.itemtype,
753 itemkey => AreAllWIsDone.itemkey,
754 aname => 'ORDER_ID');
755
756 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => AreAllWIsDone.itemtype,
757 itemkey => AreAllWIsDone.itemkey,
758 aname => 'LINE_ITEM_ID');
759
760 l_PrevSequence := wf_engine.GetItemAttrNumber(itemtype => AreAllWIsDone.itemtype,
761 itemkey => AreAllWIsDone.itemkey,
762 aname => 'CURRENT_WI_SEQUENCE');
763
764 if c_WIList%ISOPEN then
765 Close c_WIList;
766 end if;
767
768
769 open c_WIList(l_OrderID, l_LineItemID, l_PrevSequence);
770
771 Fetch c_WIList into l_WIInstanceID, l_WorkitemID, l_Priority, l_PrevSequence;
772
773 if c_WIList%NOTFOUND then
774 /* No more WI's to be done */
775 close c_WIList;
776 return ('Y');
777 else
778 /* There are more Workitem's to be done */
779
780 close c_WIList;
781 return ('N');
782 end if;
783
784
785 if c_WIList%ISOPEN then
786 Close c_WIList;
787 end if;
788
789 exception
790 when e_NoWorkitemsFoundException then
791 if c_WIList%ISOPEN then
792 Close c_WIList;
793 end if;
794
795 wf_core.context('XDPCORE_WI', 'AreAllWIsDone', itemtype, itemkey, null, x_Progress);
796 raise;
797 when others then
798 if c_WIList%ISOPEN then
799 Close c_WIList;
800 end if;
801
802 x_Progress := 'XDPCORE_WI.AreAllWIsDone. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
803 wf_core.context('XDPCORE_WI', 'AreAllWIsDone', itemtype, itemkey, null, x_Progress);
804 raise;
805 end AreAllWIsDone;
806
807
808
809 Procedure LaunchWorkitemProcess (itemtype in varchar2,
810 itemkey in varchar2)
811 is
812 l_OrderID number;
813 l_LineItemID number;
814 l_WIInstanceID number;
815 l_Priority number;
816 l_WorkitemID number;
817 l_ErrCode number;
818
819 l_wiwf varchar2(40);
820 l_UserItemType varchar2(10);
821 l_UserItemKey varchar2(240);
822 l_UserItemKeyPrefix varchar2(240);
823 l_UserWIProc varchar2(40);
824 l_UserWFProcess varchar2(40);
825 l_ErrDescription varchar2(800);
826 l_WFItemType varchar2(10);
827 l_WFItemKey varchar2(240);
828 l_FAMapProc varchar2(40);
829
830
831 e_CallWIWfProcException exception;
832 e_NullWIWfProcException exception;
833 e_CallWIMapProcException exception;
834 e_InvalidWITypeException exception;
835 e_InvalidUserWFConfigException exception;
836 e_UserWFCreateException exception;
837 e_WIWFCreateException exception;
838 e_AddWItoQException exception;
839 e_AddWFItemException exception;
840
841
842 x_Progress VARCHAR2(2000);
843
844 begin
845
846 l_OrderID := wf_engine.GetItemAttrNUmber(itemtype => LaunchWorkitemProcess.itemtype,
847 itemkey => LaunchWorkitemProcess.itemkey,
848 aname => 'ORDER_ID');
849
850 l_LineItemID := wf_engine.GetItemAttrNUmber(itemtype => LaunchWorkitemProcess.itemtype,
851 itemkey => LaunchWorkitemProcess.itemkey,
852 aname => 'LINE_ITEM_ID');
853
854 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => LaunchWorkitemProcess.itemtype,
855 itemkey => LaunchWorkitemProcess.itemkey,
856 aname => 'WORKITEM_INSTANCE_ID');
857
858 l_WorkitemID := wf_engine.GetItemAttrNumber(itemtype => LaunchWorkitemProcess.itemtype,
859 itemkey => LaunchWorkitemProcess.itemkey,
860 aname => 'WORKITEM_ID');
861
862 l_Priority := wf_engine.GetItemAttrNumber(itemtype => LaunchWorkitemProcess.itemtype,
863 itemkey => LaunchWorkitemProcess.itemkey,
864 aname => 'WI_PRIORITY');
865
866
867
868 CheckIfWorkItemIsaWorkflow(l_WorkitemID, l_wiwf, l_UserItemType,
869 l_UserItemKeyPrefix, l_UserWFProcess, l_UserWIProc, l_FAMapProc);
870
871
872 /* The workitem can be of 4 types:
873 1. Workflow with the procedure specified.
874 2. Workflow with the itemtype, key profix and the process name specified
875 3. Dynamic FA Mapping
876 4. Statis FA Mapping
877 */
878
879 if l_wiwf = 'WORKFLOW_PROC' then
880 /* The Workitem is a WOrkflow Process */
881
882 if l_UserWIProc is null then
883 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Could Not Find User Defined Workflow Procedure for Workitem: '|| l_WorkitemID || ' Order: ' || l_OrderID;
884 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID|| ' LineItemID: ' || l_LineItemID || ' Check Workitem Configuration';
885 RAISE e_NullWIWfProcException;
886 end if;
887
888
889 /* The user has defined a procedure for the workitem*/
890 /* Execute the procedure dynamically and get the item type and item key */
891 /* The Create Process for the Workflo SHOULD be done in the Procedure */
892
893 XDP_UTILITIES.CallWIWorkflowProc ( P_PROCEDURE_NAME => l_UserWIProc,
894 P_ORDER_ID => l_OrderID,
895 P_LINE_ITEM_ID => l_LineItemID,
896 P_WI_INSTANCE_ID => l_WIInstanceID,
897 P_WF_ITEM_TYPE => l_WFItemType,
898 P_WF_ITEM_KEY => l_WFItemKey,
899 P_WF_PROCESS_NAME => l_UserWFProcess,
900 P_RETURN_CODE => l_ErrCode,
901 P_ERROR_DESCRIPTION => l_ErrDescription);
902
903 if l_ErrCode <> 0 then
904 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Executing The user-defined Workflow Procedure: '|| l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID;
905 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '|| SUBSTR(l_ErrDescription, 1, 1500);
906 RAISE e_CallWIWfProcException;
907 end if;
908
909 /*
910 Raja: 04-Nov-1999
911 Must set the parent child relation ships
912 */
913
914 wf_engine.SetItemParent(itemtype => l_WFItemType,
915 itemkey => l_WFItemKey,
916 parent_itemtype => LaunchWorkitemProcess.itemtype,
917 parent_itemkey => LaunchWorkitemProcess.itemkey,
918 parent_context => null);
919
920 XDPCORE.CheckNAddItemAttrNumber (itemtype => l_WFItemType,
921 itemkey => l_WFItemKey,
922 AttrName => 'LINE_ITEM_ID',
923 AttrValue => l_LineItemID,
924 ErrCode => l_ErrCode,
925 ErrStr => l_ErrDescription);
926
927 if l_ErrCode <> 0 then
928 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Adding LINE_ITEM_ID attri bute for the user-defined Workflow Procedure: ' || l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID;
929 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '|| SUBSTR(l_ErrDescription, 1, 1500);
930 RAISE e_AddWFItemException;
931 end if;
932
933 -- Raja: 09/14/2000
934 -- Set the WF Item Attributes WORKITEM_INSTANCE_ID, ORDER_ID
935 -- Found by Burnsy
936
937 XDPCORE.CheckNAddItemAttrNumber (itemtype => l_WFItemType,
938 itemkey => l_WFItemKey,
939 AttrName => 'WORKITEM_INSTANCE_ID',
940 AttrValue => l_WIInstanceID,
941 ErrCode => l_ErrCode,
942 ErrStr => l_ErrDescription);
943
944 if l_ErrCode <> 0 then
945 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Adding WORKITEM_INSTANCE_ID attribute for the user-defined Workflow Procedure: '|| l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID;
946 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
947 RAISE e_AddWFItemException;
948 end if;
949
950 XDPCORE.CheckNAddItemAttrNumber (itemtype => l_WFItemType,
951 itemkey => l_WFItemKey,
952 AttrName => 'ORDER_ID',
953 AttrValue => l_OrderID,
954 ErrCode => l_ErrCode,
955 ErrStr => l_ErrDescription);
956
957 if l_ErrCode <> 0 then
958 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Adding ORDER_ID attribute for the user-defined Workflow Procedure: ' || l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID;
959 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
960 RAISE e_AddWFItemException;
961 end if;
962
963 -- /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
964 /*
965 update XDP_FULFILL_WORKLIST
966 set WF_ITEM_TYPE = l_WFItemType,
967 WF_ITEM_KEY = l_WFItemKey,
968 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
969 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
970 where WORKITEM_INSTANCE_ID = l_WIInstanceID;
971 */
972 elsif l_wiwf = 'WORKFLOW' then
973 if (l_UserItemKeyPrefix is null) OR (l_UserItemType is null) OR
974 (l_UserWFProcess is null) then
975 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Invalid user-defined Workitem Workfl ow Configuration. Itemtype: ' || l_UserItemType || ' ItemKey Prefix: ' || l_UserItemKeyPrefix;
976 x_Progress := x_Progress || ' WF Proce ss: ' || l_UserWFProcess || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID;
977 RAISE e_InvalidUserWFConfigException;
978 end if;
979
980 /* The user has defined the item type, process name and has given the itemkey
981 prefix SFM should start the process for them */
982
983 l_WFItemType := l_UserItemType;
984
985
986 CreateWorkitemProcess('USER', itemtype, itemkey, l_WFItemType,
987 l_UserItemKeyPrefix, l_WFItemKey,
988 l_UserWFProcess,l_OrderID, l_LineItemID, l_WorkitemID, l_WIInstanceID,
989 'WAITFORFLOW-IND_WI',
990 l_ErrCode, l_ErrDescription);
991
992 if l_ErrCode <> 0 then
993 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when creating the Workflow Process for user-defined workflow. Itemtype: ' || l_UserItemType || ' ItemKey Prefix: ' || l_UserItemKeyPrefix || ' WF Proce ss: ';
994 x_Progress := x_Progress || l_UserWFProcess || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '|| SUBSTR(l_ErrDescription, 1, 1500);
995 RAISE e_UserWFCreateException;
996 end if;
997
998 -- /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
999 /*
1000 update XDP_FULFILL_WORKLIST
1001 set WF_ITEM_TYPE = l_WFItemType,
1002 WF_ITEM_KEY = l_WFItemKey,
1003 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1004 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1005 where WORKITEM_INSTANCE_ID = l_WIInstanceID;
1006 */
1007
1008 elsif l_wiwf = 'DYNAMIC' then
1009 /* The Workitem List is dynamic */
1010 l_WFItemType := 'XDPPROV';
1011
1012 CreateWorkitemProcess('XDP', itemtype, itemkey, l_WFItemType,
1013 null, l_WFItemKey,
1014 'WORKITEM_PROCESS',l_OrderID, l_LineItemID, l_WorkitemID, l_WIInstanceID,
1015 'WAITFORFLOW-IND_WI',
1016 l_ErrCode, l_ErrDescription);
1017
1018 if l_ErrCode <> 0 then
1019 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when creating the WORKITEM_PROCESS Workflow Process for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID;
1020 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1021 RAISE e_WIWFCreateException;
1022 end if;
1023
1024
1025 elsif l_wiwf = 'STATIC' then
1026 /* The workitem is a regular FA list process */
1027
1028 l_WFItemType := 'XDPPROV';
1029
1030 CreateWorkitemProcess('XDP', itemtype, itemkey, l_WFItemType,
1031 null, l_WFItemKey,
1032 'WORKITEM_PROCESS',l_OrderID, l_LineItemID, l_WorkitemID, l_WIInstanceID,
1033 'WAITFORFLOW-IND_WI',
1034 l_ErrCode, l_ErrDescription);
1035
1036 if l_ErrCode <> 0 then
1037 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when creating the WORKITEM_PRO CESS Workflow Process for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID;
1038 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1039 RAISE e_WIWFCreateException;
1040 end if;
1041
1042 else
1043 /* Invalid Configuration again */
1044 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Invalid Workitem type: ' || l_wiwf || ' for workitemID: ' || l_WorkitemID || ' Order: ' || l_OrderID;
1045 x_Progress := x_Progress || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1046 RAISE e_InvalidWITypeException;
1047
1048 end if;
1049
1050 /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
1051
1052 update XDP_FULFILL_WORKLIST
1053 set WF_ITEM_TYPE = l_WFItemType,
1054 WF_ITEM_KEY = l_WFItemKey,
1055 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1056 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1057 where WORKITEM_INSTANCE_ID = l_WIInstanceID;
1058
1059
1060 /* Enqueue all the Wormitems in the Workitem Queue */
1061
1062 XDP_AQ_UTILITIES.ADD_WORKITEM_TOQ ( P_ORDER_ID => l_OrderID,
1063 P_WI_INSTANCE_ID => l_WIInstanceID,
1064 P_PROV_DATE => GetWIProvisioningDate(l_WIInstanceID),
1065 P_WF_ITEM_TYPE => l_WFItemType,
1066 P_WF_ITEM_KEY => l_WFItemKey,
1067 P_PRIORITY => l_Priority,
1068 P_RETURN_CODE => l_ErrCode,
1069 P_ERROR_DESCRIPTION => l_ErrDescription);
1070
1071 if l_ErrCode <> 0 then
1072 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error When Adding Workitem to Queue . Itemtype : ' || l_WFItemType || ' Itemkey: ' || l_WFItemKey || ' for workitemID: ' || l_WorkitemID;
1073 x_Progress := x_Progress || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1074 RAISE e_AddWItoQException;
1075 end if;
1076
1077 exception
1078 when e_CallWIWfProcException then
1079 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1080 raise;
1081
1082 when e_NullWIWfProcException then
1083 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1084 raise;
1085
1086 when e_CallWIMapProcException then
1087 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1088 raise;
1089
1090 when e_InvalidWITypeException then
1091 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1092 raise;
1093
1094 when e_InvalidUserWFConfigException then
1095 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1096 raise;
1097
1098 when e_UserWFCreateException then
1099 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1100 raise;
1101
1102 when e_WIWFCreateException then
1103 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1104 raise;
1105
1106 when e_AddWItoQException then
1107 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1108 raise;
1109
1110 when e_AddWFItemException then
1111 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1112 raise;
1113
1114 when others then
1115 wf_core.context('XDPCORE_WI', 'LaunchWorkitemProcess', itemtype, itemkey, null, x_Progress);
1116 raise;
1117 end LaunchWorkitemProcess;
1118
1119 Function LaunchAllIndependentWIs (itemtype in varchar2,
1120 itemkey in varchar2) return varchar2
1121 is
1122 l_OrderID number;
1123 l_LineItemID number;
1124 l_WIInstanceID number;
1125 l_WorkitemID number;
1126 l_Priority number;
1127
1128 l_result varchar2(1) := 'N';
1129
1130 cursor c_GetIndWIList (OrderID number, LineItemID number) is
1131 select WORKITEM_INSTANCE_ID, WORKITEM_ID, PRIORITY
1132 from XDP_FULFILL_WORKLIST
1133 where ORDER_ID = Order_ID
1134 and LINE_ITEM_ID = LineItemID
1135 and status_code = 'STANDBY'
1136 and WI_SEQUENCE = 0;
1137
1138 e_NoWorkitemsFoundException exception;
1139 x_Progress VARCHAR2(2000);
1140
1141 ErrCode number;
1142 ErrStr varchar2(1996);
1143
1144 begin
1145 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndependentWIs.itemtype,
1146 itemkey => LaunchAllIndependentWIs.itemkey,
1147 aname => 'ORDER_ID');
1148
1149 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndependentWIs.itemtype,
1150 itemkey => LaunchAllIndependentWIs.itemkey,
1151 aname => 'LINE_ITEM_ID');
1152
1153 /* Create WF Processes for all the independent Workitems */
1154
1155 FOR lv_WIRec in c_GetIndWIList(l_OrderID, l_LineItemID) LOOP
1156 l_result := 'Y';
1157 l_WIInstanceID := lv_WIRec.workitem_instance_id;
1158 l_WorkitemID := lv_WIRec.workitem_id;
1159 l_Priority := lv_WIRec.priority;
1160 LaunchIndWorkitemProcess( itemtype => LaunchAllIndependentWIs.itemtype,
1161 itemkey => LaunchAllIndependentWIs.itemkey,
1162 p_OrderID => l_OrderID ,
1163 p_LineItemID => l_LineItemID,
1164 p_WorkitemID => l_WorkitemID,
1165 p_WIInstanceID => l_WIInstanceID,
1166 p_Priority => l_Priority);
1167 END LOOP;
1168
1169 return l_result;
1170
1171 exception
1172 when others then
1173 x_Progress := 'XDPCORE_WI.LaunchAllIndependentWIs. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1174 xdpcore.context('XDPCORE_WI', 'LaunchAllIndependentWIs', 'LINE', l_LineItemID,x_Progress);
1175 wf_core.context('XDPCORE_WI', 'LaunchAllIndependentWIs', itemtype, itemkey, null, x_Progress);
1176 raise;
1177 end LaunchAllIndependentWIs;
1178
1179 Procedure LaunchIndWorkitemProcess (itemtype in varchar2,
1180 itemkey in varchar2,
1181 p_OrderID in NUMBER,
1182 p_LineItemID in NUMBER,
1183 p_WorkitemID in NUMBER,
1184 p_WIInstanceID in NUMBER,
1185 p_Priority in NUMBER)
1186
1187
1188 is
1189
1190 l_ErrCode number;
1191
1192 l_wiwf varchar2(40);
1193 l_UserItemType varchar2(10);
1194 l_UserItemKey varchar2(240);
1195 l_UserItemKeyPrefix varchar2(240);
1196 l_UserWIProc varchar2(40);
1197 l_UserWFProcess varchar2(40);
1198 l_ErrDescription varchar2(800);
1199 l_WFItemType varchar2(10);
1200 l_WFItemKey varchar2(240);
1201 l_FAMapProc varchar2(40);
1202
1203 e_CallWIWfProcException exception;
1204 e_NullWIWfProcException exception;
1205 e_CallWIMapProcException exception;
1206 e_InvalidWITypeException exception;
1207 e_InvalidUserWFConfigException exception;
1208 e_UserWFCreateException exception;
1209 e_WIWFCreateException exception;
1210 e_AddWItoQException exception;
1211 e_AddWFItemException exception;
1212
1213 x_Progress VARCHAR2(2000);
1214
1215 begin
1216
1217 CheckIfWorkItemIsaWorkflow(p_WorkitemID, l_wiwf, l_UserItemType, l_UserItemKeyPrefix,
1218 l_UserWFProcess, l_UserWIProc, l_FAMapProc);
1219
1220
1221 /* The workitem can be of 4 types:
1222 1. Workflow with the procedure specified.
1223 2. Workflow with the itemtype, key profix and the process name specified
1224 3. Dynamic FA Mapping
1225 4. Statis FA Mapping
1226 */
1227
1228 IF l_wiwf = 'WORKFLOW_PROC' THEN /* The Workitem is a WOrkflow Process */
1229 IF l_UserWIProc IS NULL THEN
1230 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Could Not Find User Defined Workflow Procedure for Workitem: '|| p_WorkitemID || ' Order: ' || p_OrderID;
1231 x_Progress := x_Progress || ' Workitem InstanceID: ' || p_WIInstanceID || 'LineItemID: ' || p_LineItemID || ' Check Workitem Configuration';
1232 RAISE e_NullWIWfProcException;
1233 end if;
1234
1235 /* The user has defined a procedure for the workitem*/
1236 /* Execute the procedure dynamically and get the item type and item key */
1237 /* The Create Process for the Workflo SHOULD be done in the Procedure */
1238
1239 XDP_UTILITIES.CallWIWorkflowProc ( P_PROCEDURE_NAME => l_UserWIProc,
1240 P_ORDER_ID => p_OrderID,
1241 P_LINE_ITEM_ID => p_LineItemID,
1242 P_WI_INSTANCE_ID => p_WIInstanceID,
1243 P_WF_ITEM_TYPE => l_WFItemType,
1244 P_WF_ITEM_KEY => l_WFItemKey,
1245 P_WF_PROCESS_NAME => l_UserWFProcess,
1246 P_RETURN_CODE => l_ErrCode,
1247 P_ERROR_DESCRIPTION => l_ErrDescription);
1248
1249 if l_ErrCode <> 0 then
1250 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when Executing The user-defined Workflow Procedure: ' || l_UserWIProc || ' for Workitem: ' || p_WorkitemID;
1251 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1252 RAISE e_CallWIWfProcException;
1253 end if;
1254
1255 /*
1256 Raja: 04-Nov-1999
1257 Must set the parent child relation ships
1258 */
1259
1260 wf_engine.SetItemParent(itemtype => l_WFItemType,
1261 itemkey => l_WFItemKey,
1262 parent_itemtype => LaunchIndWorkitemProcess .itemtype,
1263 parent_itemkey => LaunchIndWorkitemProcess .itemkey,
1264 parent_context => null);
1265
1266 XDPCORE.CheckNAddItemAttrNumber (itemtype => l_WFItemType,
1267 itemkey => l_WFItemKey,
1268 AttrName => 'LINE_ITEM_ID',
1269 AttrValue => p_LineItemID,
1270 ErrCode => l_ErrCode,
1271 ErrStr => l_ErrDescription);
1272
1273 if l_ErrCode <> 0 then
1274 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when Adding LINE_ITEM_ID attribute for the user-defined Workflow Procedure: ' || l_UserWIProc || ' for Workitem: ' || p_WorkitemID;
1275 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1276 RAISE e_AddWFItemException;
1277 end if;
1278
1279 -- Raja: 09/14/2000
1280 -- Set the WF Item Attributes WORKITEM_INSTANCE_ID, ORDER_ID
1281 -- Found by Burnsy
1282
1283 XDPCORE.CheckNAddItemAttrNumber (itemtype => l_WFItemType,
1284 itemkey => l_WFItemKey,
1285 AttrName => 'WORKITEM_INSTANCE_ID',
1286 AttrValue => p_WIInstanceID,
1287 ErrCode => l_ErrCode,
1288 ErrStr => l_ErrDescription);
1289
1290 if l_ErrCode <> 0 then
1291 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when Adding WORKITEM_INSTANCE_ID attribute for the user-defined Workflow Procedure: ' || l_UserWIProc || ' for Workitem: ' || p_WorkitemID;
1292 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1293 RAISE e_AddWFItemException;
1294 end if;
1295
1296 XDPCORE.CheckNAddItemAttrNumber (itemtype => l_WFItemType,
1297 itemkey => l_WFItemKey,
1298 AttrName => 'ORDER_ID',
1299 AttrValue => p_OrderID,
1300 ErrCode => l_ErrCode,
1301 ErrStr => l_ErrDescription);
1302
1303 if l_ErrCode <> 0 then
1304 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when Adding ORDER_ID attribute for the user-defined Workflow Procedure: ' || l_UserWIProc || ' for Workitem: ' || p_WorkitemID;
1305 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1306 RAISE e_AddWFItemException;
1307 end if;
1308
1309 -- /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
1310 /*
1311 update XDP_FULFILL_WORKLIST
1312 set WF_ITEM_TYPE = l_WFItemType,
1313 WF_ITEM_KEY = l_WFItemKey,
1314 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1315 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1316 where WORKITEM_INSTANCE_ID = p_WIInstanceID;
1317 */
1318 elsif l_wiwf = 'WORKFLOW' then
1319 if (l_UserItemKeyPrefix is null) OR (l_UserItemType is null) OR
1320 (l_UserWFProcess is null) then
1321 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Invalid user-defined Workitem Workflow Configuration. Itemtype: ' || l_UserItemType || ' ItemKey Prefix: ' || l_UserItemKeyPrefix || ' WF Process: ';
1322 x_Progress := x_Progress || l_UserWFProcess || ' for Workitem: ' || p_WorkitemID || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID;
1323 RAISE e_InvalidUserWFConfigException;
1324 end if;
1325
1326 /* The user has defined the item type, process name and has given the itemkey
1327 prefix SFM should start the process for them */
1328
1329 l_WFItemType := l_UserItemType;
1330
1331
1332 CreateWorkitemProcess('USER', itemtype, itemkey, l_WFItemType,
1333 l_UserItemKeyPrefix, l_WFItemKey,
1334 l_UserWFProcess,p_OrderID, p_LineItemID,
1335 p_WorkitemID, p_WIInstanceID,
1336 'WAITFORFLOW-IND_WI',
1337 l_ErrCode, l_ErrDescription);
1338
1339 if l_ErrCode <> 0 then
1340 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when creating the
1341 Workflow Process for user-defined workflow. Itemtype: ' || l_UserItemType || ' ItemKey Prefix: ' || l_UserItemKeyPrefix || ' WF Process: ' || l_UserWFProcess || ' for Workitem: ' || p_WorkitemID;
1342 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1343 RAISE e_UserWFCreateException;
1344 end if;
1345
1346 -- /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
1347 /*
1348 update XDP_FULFILL_WORKLIST
1349 set WF_ITEM_TYPE = l_WFItemType,
1350 WF_ITEM_KEY = l_WFItemKey,
1351 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1352 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1353 where WORKITEM_INSTANCE_ID = p_WIInstanceID;
1354 */
1355
1356 elsif l_wiwf = 'DYNAMIC' then
1357 /* The Workitem List is dynamic */
1358 l_WFItemType := 'XDPPROV';
1359
1360 CreateWorkitemProcess('XDP', itemtype, itemkey, l_WFItemType, null, l_WFItemKey,
1361 'WORKITEM_PROCESS',p_OrderID, p_LineItemID,
1362 p_WorkitemID, p_WIInstanceID, 'WAITFORFLOW-IND_WI',
1363 l_ErrCode, l_ErrDescription);
1364
1365 if l_ErrCode <> 0 then
1366 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when creating the
1367 WORKITEM_PROCESS Workflow Process for Workitem: ' || p_WorkitemID;
1368 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1369 RAISE e_WIWFCreateException;
1370 end if;
1371
1372
1373 elsif l_wiwf = 'STATIC' then
1374 /* The workitem is a regular FA list process */
1375
1376 l_WFItemType := 'XDPPROV';
1377
1378 CreateWorkitemProcess('XDP', itemtype, itemkey, l_WFItemType, null, l_WFItemKey,
1379 'WORKITEM_PROCESS',p_OrderID, p_LineItemID, p_WorkitemID, p_WIInstanceID,
1380 'WAITFORFLOW-IND_WI', l_ErrCode, l_ErrDescription);
1381
1382 if l_ErrCode <> 0 then
1383 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error when creating the WORKITEM_PROCESS Workflow Process for Workitem: ' || p_WorkitemID || ' Order: ' || p_OrderID;
1384 x_Progress := x_Progress || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1385 RAISE e_WIWFCreateException;
1386 end if;
1387
1388 else
1389 /* Invalid Configuration again */
1390 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Invalid Workitem type: ' ||
1391 l_wiwf || ' for workitemID: ' || p_WorkitemID || ' Order: ' || p_OrderID;
1392 x_Progress := x_Progress || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1393 RAISE e_InvalidWITypeException;
1394
1395 end if;
1396
1397
1398 /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
1399
1400 update XDP_FULFILL_WORKLIST
1401 set WF_ITEM_TYPE = l_WFItemType,
1402 WF_ITEM_KEY = l_WFItemKey,
1403 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1404 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1405 where WORKITEM_INSTANCE_ID = p_WIInstanceID;
1406
1407 /* Enqueue all the Wormitems in the Workitem Queue */
1408
1409 XDP_AQ_UTILITIES.ADD_WORKITEM_TOQ ( P_ORDER_ID => p_OrderID,
1410 P_WI_INSTANCE_ID => p_WIInstanceID,
1411 P_PROV_DATE => GetWIProvisioningDate(p_WIInstanceID),
1412 P_WF_ITEM_TYPE => l_WFItemType,
1413 P_WF_ITEM_KEY => l_WFItemKey,
1414 P_PRIORITY => p_Priority,
1415 P_RETURN_CODE => l_ErrCode,
1416 P_ERROR_DESCRIPTION => l_ErrDescription);
1417
1418 if l_ErrCode <> 0 then
1419 x_Progress := 'XDPCORE_WI.LaunchIndWorkitemProcess . Error When Adding Workitem to Queue. Itemtype : ' || l_WFItemType || ' Itemkey: ' || l_WFItemKey || ' for workitemID: ' || p_WorkitemID;
1420 x_Progress := x_Progress || ' Order: ' || p_OrderID || ' Workitem InstanceID: ' || p_WIInstanceID || ' LineItemID: ' || p_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1421 RAISE e_AddWItoQException;
1422 end if;
1423 EXCEPTION
1424 WHEN others THEN
1425 wf_core.context('XDPCORE_WI', 'LaunchIndWorkitemProcess ', itemtype, itemkey, null, x_Progress);
1426 raise;
1427 end LaunchIndWorkitemProcess ;
1428
1429
1430
1431 Procedure CheckIfWorkItemIsaWorkflow(workitem_ID in number,
1432 wf_flag OUT NOCOPY varchar2,
1433 user_item_type OUT NOCOPY varchar2,
1434 user_item_key_prefix OUT NOCOPY varchar2,
1435 user_wf_process_name OUT NOCOPY varchar2,
1436 user_wf_proc OUT NOCOPY varchar2,
1437 fa_exec_map_proc OUT NOCOPY varchar2)
1438 is
1439 e_CheckWIException exception;
1440 x_Progress VARCHAR2(2000);
1441
1442 cursor c_CheckWI is
1443 select WI_TYPE_CODE, NVL(USER_WF_ITEM_TYPE,null) ITEM_TYPE,
1444 NVL(USER_WF_ITEM_KEY_PREFIX, null) ITEM_KEY_PREFIX ,
1445 NVL(USER_WF_PROCESS_NAME, null) PROCESS_NAME,
1446 NVL(WF_EXEC_PROC, null) WF_EXEC_PROC,
1447 NVL(FA_EXEC_MAP_PROC, null) FA_EXEC_MAP_PROC
1448 from XDP_WORKITEMS
1449 where WORKITEM_ID = CheckIfWorkItemIsaWorkflow.workitem_ID;
1450
1451 begin
1452
1453 for v_CheckWI in c_CheckWI loop
1454 wf_flag := v_CheckWI.WI_TYPE_CODE;
1455 user_item_type := v_CheckWI.ITEM_TYPE;
1456 user_item_key_prefix := v_CheckWI.ITEM_KEY_PREFIX;
1457 user_wf_process_name := v_CheckWI.PROCESS_NAME;
1458 user_wf_proc := v_CheckWI.WF_EXEC_PROC;
1459 fa_exec_map_proc := v_CheckWI.FA_EXEC_MAP_PROC;
1460 end loop;
1461
1462 exception
1463 when others then
1464 x_Progress := 'XDPCORE_WI.CheckIfWorkItemIsaWorkflow. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1465 wf_core.context('XDPCORE_WI', 'CheckIfWorkItemIsaWorkflow', null, null, null,null);
1466 raise;
1467 end CheckIfWorkItemIsaWorkflow;
1468
1469
1470
1471 Function ContinueWorkitem (itemtype in varchar2,
1472 itemkey in varchar2) return varchar2 is
1473 l_OrderID number;
1474 l_Continue varchar2(1) := 'N';
1475
1476 x_Progress VARCHAR2(2000);
1477
1478 CURSOR c_ContinueWI(p_OrderID number) is
1479 select 'Y' yahoo
1480 from dual
1481 where exists (select WORKITEM_INSTANCE_ID
1482 from XDP_FULFILL_WORKLIST
1483 where ORDER_ID = p_OrderID
1484 and status_code in ('READY','IN PROGRESS') );
1485 begin
1486 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ContinueWorkitem.itemtype,
1487 itemkey => ContinueWorkitem.itemkey,
1488 aname => 'ORDER_ID');
1489
1490 for v_ContinueWI in c_ContinueWI(l_OrderID) loop
1491 l_Continue := v_ContinueWI.yahoo;
1492 exit;
1493 end loop;
1494 /*
1495 begin
1496 select 'Y' into l_Continue from dual
1497 where exists (select WORKITEM_INSTANCE_ID
1498 from XDP_FULFILL_WORKLIST
1499 where ORDER_ID = l_OrderID
1500 and status_code = 'READY' );
1501
1502 exception
1503 when no_data_found then
1504 l_Continue := 'N';
1505 when others then
1506 RAISE;
1507 end;
1508 */
1509 return (l_Continue);
1510
1511 exception
1512 when others then
1513 x_Progress := 'XDPCORE_WI.ContinueWorkitem. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1514 wf_core.context('XDPCORE_WI', 'ContinueWorkitem',itemtype, itemkey, null, null);
1515 raise;
1516 end ContinueWorkitem;
1517
1518 Procedure InitializeWorkitemProcess(itemtype in varchar2,
1519 itemkey in varchar2) IS
1520
1521 l_WIInstanceID number;
1522
1523 e_InvalidConfigException exception;
1524
1525 x_Progress VARCHAR2(2000);
1526
1527 begin
1528
1529 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => InitializeWorkitemProcess.itemtype,
1530 itemkey => InitializeWorkitemProcess.itemkey,
1531 aname => 'WORKITEM_INSTANCE_ID');
1532
1533 if l_WIInstanceID is null then
1534 RAISE e_InvalidConfigException;
1535 else
1536 /* Update the STATUS of the Workitems to be processed */
1537
1538 UPDATE_WORKITEM_STATUS(p_workitem_instance_id => l_WIInstanceID,
1539 p_status_code => 'IN PROGRESS',
1540 p_itemtype => InitializeWorkitemProcess.itemtype,
1541 p_itemkey => InitializeWorkitemProcess.itemkey );
1542 end if;
1543
1544 exception
1545 when others then
1546 x_Progress := 'XDPCORE_WI.InitializeWorkitemProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1547 wf_core.context('XDPCORE_WI', 'InitializeWorkitemProcess', itemtype, itemkey, null, x_Progress);
1548 raise;
1549 end InitializeWorkitemProcess;
1550
1551
1552
1553 Procedure CreateWorkitemProcess (wftype in varchar2,
1554 parentitemtype in varchar2,
1555 parentitemkey in varchar2,
1556 itemtype in varchar2,
1557 itemkeyPrefix in varchar2,
1558 itemkey OUT NOCOPY varchar2,
1559 workflowprocessname in varchar2,
1560 OrderID in number,
1561 LineItemID in number,
1562 WorkitemID in number,
1563 WIInstanceID in number,
1564 WFMaster in varchar2,
1565 ErrCode OUT NOCOPY number,
1566 ErrStr OUT NOCOPY varchar2)
1567 is
1568
1569 l_tempKey varchar2(240);
1570
1571
1572 x_Progress VARCHAR2(2000);
1573
1574 begin
1575
1576 SELECT to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1577
1578 if itemkeyPrefix is null then
1579 itemkey := to_char(OrderID) || '-WI-' || l_tempKey;
1580 else
1581 itemkey := to_char(OrderID) || '-WI-' || itemkeyPrefix || l_tempKey;
1582 end if;
1583
1584 -- Create Process and Bulk Set Item Attribute
1585 XDPCORE.CreateAndAddAttrNum(itemtype => Itemtype,
1586 itemkey => Itemkey,
1587 processname => workflowprocessname,
1588 parentitemtype => parentitemtype,
1589 parentitemkey => parentitemkey,
1590 OrderID => OrderID,
1591 LineitemID => LineItemID,
1592 WIInstanceID => WIInstanceID,
1593 FAInstanceID => null);
1594
1595 if wftype = 'XDP' then
1596
1597 XDPCORE.CheckNAddItemAttrNumber (itemtype => itemtype,
1598 itemkey => itemkey,
1599 AttrName => 'WORKITEM_ID',
1600 AttrValue => WorkitemID,
1601 ErrCode => ErrCode,
1602 ErrStr => ErrStr);
1603
1604 if ErrCode <> 0 then
1605 return;
1606 end if;
1607
1608
1609 end if;
1610
1611 if wftype = 'XDP' then
1612 null;
1613 else
1614 XDPCORE.CheckNAddItemAttrNumber (itemtype => itemtype,
1615 itemkey => itemkey,
1616 AttrName => 'LINE_ITEM_ID',
1617 AttrValue => LineItemID,
1618 ErrCode => ErrCode,
1619 ErrStr => ErrStr);
1620 end if;
1621
1622
1623 exception
1624 when others then
1625 ErrCode := SQLCODE;
1626 ErrStr := SUBSTR(SQLERRM, 1, 800);
1627 end CreateWorkitemProcess;
1628
1629 Procedure InitDepWIProcess (itemtype in varchar2,
1630 itemkey in varchar2)
1631 is
1632
1633 ErrCode number;
1634 ErrStr varchar2(1996);
1635
1636 e_AddAttributeException exception;
1637 x_Progress VARCHAR2(2000);
1638
1639 begin
1640
1641 XDPCORE.CheckNAddItemAttrNumber (itemtype => InitDepWIProcess.itemtype,
1642 itemkey => InitDepWIProcess.itemkey,
1643 AttrName => 'CURRENT_WI_SEQUENCE',
1644 AttrValue => 0,
1645 ErrCode => ErrCode,
1646 ErrStr => ErrStr);
1647
1648 if ErrCode <> 0 then
1649 x_progress := 'In XDPCORE_WI.LaunchWIServiceProcess. Error when adding Item Attribute CURRENT_WI_SEQUENCE. Error:' || substr(ErrStr,1,1500);
1650 raise e_AddAttributeException;
1651 end if;
1652
1653
1654 EXCEPTION
1655 when others then
1656 x_Progress := 'XDPCORE_WI.InitDepWIProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1657 xdpcore.context('XDPCORE_WI', 'InitDepWIProcess', itemtype, itemkey,x_Progress);
1658 wf_core.context('XDPCORE_WI', 'InitDepWIProcess', itemtype, itemkey, null, x_Progress);
1659 raise;
1660 end InitDepWIProcess;
1661
1662
1663
1664
1665 Procedure LaunchWIServiceProcess (itemtype in varchar2,
1666 itemkey in varchar2)
1667 is
1668 l_OrderID number;
1669 l_LineItemID number;
1670 l_WIInstanceID number;
1671 l_WorkitemID number;
1672 l_Counter number := 0;
1673 l_Priority number;
1674
1675 l_tempKey varchar2(240);
1676
1677
1678 cursor c_GetIndWIList (OrderID number, LineItemID number) is
1679 select WORKITEM_INSTANCE_ID, WORKITEM_ID, PRIORITY
1680 from XDP_FULFILL_WORKLIST
1681 where ORDER_ID = Order_ID
1682 and LINE_ITEM_ID = LineItemID
1683 and status_code = 'STANDBY'
1684 and WI_SEQUENCE = 0;
1685
1686 cursor c_GetDepWIList (OrderID number, LineItemID number) is
1687 select WORKITEM_INSTANCE_ID
1688 from XDP_FULFILL_WORKLIST
1689 where ORDER_ID = Order_ID
1690 and LINE_ITEM_ID = LineItemID
1691 and status_code = 'STANDBY'
1692 and WI_SEQUENCE > 0;
1693
1694
1695
1696 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1697 t_ChildKeys t_ChildKeyTable;
1698
1699 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
1700 t_ChildTypes t_ChildTypeTable;
1701
1702 e_NoWorkitemsFoundException exception;
1703 e_AddAttributeException exception;
1704 x_Progress VARCHAR2(2000);
1705
1706 ErrCode number;
1707 ErrStr varchar2(1996);
1708
1709 begin
1710
1711 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchWIServiceProcess.itemtype,
1712 itemkey => LaunchWIServiceProcess.itemkey,
1713 aname => 'ORDER_ID');
1714
1715 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchWIServiceProcess.itemtype,
1716 itemkey => LaunchWIServiceProcess.itemkey,
1717 aname => 'LINE_ITEM_ID');
1718
1719 if c_GetIndWIList%ISOPEN then
1720 close c_GetIndWIList;
1721 end if;
1722
1723 /* Create WF Processes for all the independent Workitems */
1724
1725 open c_GetIndWIList(l_OrderID, l_LineItemID);
1726 LOOP
1727 Fetch c_GetIndWIList into l_WIInstanceID, l_WorkitemID, l_Priority;
1728 EXIT when c_GetIndWIList%NOTFOUND;
1729
1730 l_Counter := l_Counter + 1;
1731
1732 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1733 l_tempKey := to_char(l_OrderID) || '-WI-' || to_char(l_WIInstanceID) || l_tempKey;
1734
1735 t_ChildTypes(l_Counter) := 'XDPPROV';
1736 t_ChildKeys(l_Counter) := l_tempKey;
1737
1738 -- Create Process and Bulk Set Item Attribute
1739 XDPCORE.CreateAndAddAttrNum(itemtype => t_ChildTypes(l_Counter),
1740 itemkey => t_ChildKeys(l_Counter),
1741 processname => 'INDEPENDENT_WI_PROCESS',
1742 parentitemtype => LaunchWIServiceProcess.itemtype,
1743 parentitemkey => LaunchWIServiceProcess.itemkey,
1744 OrderID => l_OrderID,
1745 LineitemID => l_LineItemID,
1746 WIInstanceID => l_WIInstanceID,
1747 FAInstanceID => null);
1748
1749
1750 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1751 itemkey => t_ChildKeys(l_Counter),
1752 AttrName => 'WORKITEM_ID',
1753 AttrValue => l_WorkitemID,
1754 ErrCode => ErrCode,
1755 ErrStr => ErrStr);
1756
1757 if ErrCode <> 0 then
1758 x_progress := 'In XDPCORE_WI.LaunchWIServiceProcess. Error when adding Item Attribute WORKITEM_ID. Error:
1759 ' || substr(ErrStr,1,1500);
1760 raise e_AddAttributeException;
1761 end if;
1762
1763
1764 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1765 itemkey => t_ChildKeys(l_Counter),
1766 AttrName => 'WI_PRIORITY',
1767 AttrValue => to_char(l_Priority),
1768 ErrCode => ErrCode,
1769 ErrStr => ErrStr);
1770
1771 if ErrCode <> 0 then
1772 x_progress := 'In XDPCORE_WI.LaunchWIServiceProcess. Error when adding Item Attribute WI_PRIORITY. Error:
1773 ' || substr(ErrStr,1,1500);
1774 raise e_AddAttributeException;
1775 end if;
1776 END LOOP;
1777
1778 close c_GetIndWIList;
1779
1780 /* Create ONE workflow Process for processing all the depeneden WOrkitems */
1781
1782 if c_GetDepWIList%ISOPEN then
1783 close c_GetDepWIList;
1784 end if;
1785
1786 open c_GetDepWIList(l_OrderID, l_LineItemID);
1787 Fetch c_GetDepWIList into l_WIInstanceID;
1788
1789 if c_GetDepWIList%FOUND then
1790
1791 l_Counter := l_Counter + 1;
1792
1793 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1794 l_tempKey := to_char(l_OrderID) || '-WI-' || to_char(l_WIInstanceID) || l_tempKey;
1795
1796 t_ChildTypes(l_Counter) := 'XDPPROV';
1797 t_ChildKeys(l_Counter) := l_tempKey;
1798
1799 -- Create Process and Bulk Set Item Attribute
1800 XDPCORE.CreateAndAddAttrNum(itemtype => t_ChildTypes(l_Counter),
1801 itemkey => t_ChildKeys(l_Counter),
1802 processname => 'DEPENDENT_WI_PROCESS',
1803 parentitemtype => LaunchWIServiceProcess.itemtype,
1804 parentitemkey => LaunchWIServiceProcess.itemkey,
1805 OrderID => l_OrderID,
1806 LineitemID => l_LineItemID,
1807 WIInstanceID => l_WIInstanceID,
1808 FAInstanceID => null);
1809
1810 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1811 itemkey => t_ChildKeys(l_Counter),
1812 AttrName => 'CURRENT_WI_SEQUENCE',
1813 AttrValue => 0,
1814 ErrCode => ErrCode,
1815 ErrStr => ErrStr);
1816
1817 if ErrCode <> 0 then
1818 x_progress := 'In XDPCORE_WI.LaunchWIServiceProcess. Error when adding Item Attribute CURRENT_WI_SEQUENCE. Error:
1819 ' || substr(ErrStr,1,1500);
1820 raise e_AddAttributeException;
1821 end if;
1822
1823 end if;
1824
1825 close c_GetDepWIList;
1826
1827 if l_Counter = 0 then
1828 x_Progress := 'XDPCORE_WI.LaunchWIServiceProcess. Found No workitems to be processed for Order: ' || l_OrderID || ' LineItemID: ' || l_LineItemID;
1829 RAISE e_NoWorkitemsFoundException;
1830 else
1831
1832 FOR i in 1..l_Counter LOOP
1833 wf_engine.StartProcess(itemtype => t_ChildTypes(i),
1834 itemkey => t_ChildKeys(i));
1835 END LOOP;
1836
1837 end if;
1838
1839 exception
1840 when e_AddAttributeException then
1841 if c_GetIndWIList%ISOPEN then
1842 close c_GetIndWIList;
1843 end if;
1844
1845 if c_GetDepWIList%ISOPEN then
1846 close c_GetDepWIList;
1847 end if;
1848
1849 wf_core.context('XDPCORE_WI', 'LaunchWIServiceProcess', itemtype, itemkey, null, x_Progress);
1850 raise;
1851
1852 when e_NoWorkitemsFoundException then
1853 if c_GetIndWIList%ISOPEN then
1854 close c_GetIndWIList;
1855 end if;
1856
1857 if c_GetDepWIList%ISOPEN then
1858 close c_GetDepWIList;
1859 end if;
1860
1861 wf_core.context('XDPCORE_WI', 'LaunchWIServiceProcess', itemtype, itemkey, null, x_Progress);
1862 raise;
1863
1864 when others then
1865 if c_GetIndWIList%ISOPEN then
1866 close c_GetIndWIList;
1867 end if;
1868
1869 if c_GetDepWIList%ISOPEN then
1870 close c_GetDepWIList;
1871 end if;
1872
1873 x_Progress := 'XDPCORE_WI.LaunchWIServiceProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1874 wf_core.context('XDPCORE_WI', 'LaunchWIServiceProcess', itemtype, itemkey, null, x_Progress);
1875 raise;
1876 end LaunchWIServiceProcess;
1877
1878
1879
1880 Procedure LaunchWISeqProcess (itemtype in varchar2,
1881 itemkey in varchar2)
1882 is
1883
1884 l_PrevSequence number;
1885 l_CurrentSequence number;
1886 l_OrderID number;
1887 l_LineItemID number;
1888 l_Priority number;
1889 l_WorkitemID number;
1890 l_WIInstanceID number;
1891 l_Counter number := 0;
1892 l_ErrCode number;
1893
1894 l_wiwf varchar2(40);
1895 l_UserItemType varchar2(10);
1896 l_UserItemKey varchar2(240);
1897 l_UserItemKeyPrefix varchar2(240);
1898 l_UserWIProc varchar2(40);
1899 l_UserWFProcess varchar2(40);
1900 l_ErrDescription varchar2(800);
1901 l_FAMapProc varchar2(40);
1902
1903
1904 l_tempKey varchar2(240);
1905
1906
1907 e_NoWIsFoundException exception;
1908 e_UserWFCreateException exception;
1909 e_WIWFCreateException exception;
1910 e_NullWIWfProcException exception;
1911 e_CallWIWFProcException exception;
1912 e_InvalidUserWFConfigException exception;
1913 e_CallWIMapProcException exception;
1914 e_AddWItoQException exception;
1915 e_InvalidWITypeException exception;
1916 e_AddWFItemException exception;
1917 e_AddAttributeException exception;
1918
1919 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1920 t_ChildKeys t_ChildKeyTable;
1921
1922 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
1923 t_ChildTypes t_ChildTypeTable;
1924
1925 TYPE t_IdTable is table of number INDEX BY BINARY_INTEGER;
1926 t_WIIDList t_IdTable;
1927 t_PriorityList t_IdTable;
1928
1929 x_Progress VARCHAR2(2000);
1930
1931 ErrCode number;
1932 ErrStr varchar2(1996);
1933
1934 begin
1935
1936 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchWISeqProcess.itemtype,
1937 itemkey => LaunchWISeqProcess.itemkey,
1938 aname => 'ORDER_ID');
1939
1940 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchWISeqProcess.itemtype,
1941 itemkey => LaunchWISeqProcess.itemkey,
1942 aname => 'LINE_ITEM_ID');
1943
1944 l_PrevSequence := wf_engine.GetItemAttrNumber(itemtype => LaunchWISeqProcess.itemtype,
1945 itemkey => LaunchWISeqProcess.itemkey,
1946 aname => 'CURRENT_WI_SEQUENCE');
1947
1948 if c_WIList%ISOPEN then
1949 close c_WIList;
1950 end if;
1951
1952 open c_WIList(l_OrderID, l_LineItemID, l_PrevSequence);
1953
1954 LOOP
1955 Fetch c_WIList into l_WIInstanceID, l_WorkitemID, l_Priority, l_CurrentSequence;
1956 EXIT when c_WIList%NOTFOUND;
1957
1958 l_Counter := l_Counter + 1;
1959
1960 t_WIIDList(l_Counter) := l_WIInstanceID;
1961 t_PriorityList(l_Counter) := l_Priority;
1962
1963 CheckIfWorkItemIsaWorkflow(l_WorkitemID, l_wiwf, l_UserItemType,
1964 l_UserItemKeyPrefix, l_UserWFProcess, l_UserWIProc, l_FAMapProc);
1965
1966 /* The workitem can be of 4 types:
1967 1. Workflow with the procedure specified.
1968 2. Workflow with the itemtype, key profix and the process name specified
1969 3. Dynamic FA Mapping
1970 4. Statis FA Mapping
1971 */
1972
1973 if l_wiwf = 'WORKFLOW_PROC' then
1974 /* The Workitem is a WOrkflow Process */
1975
1976 if l_UserWIProc is null then
1977 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Could Not Find User DefinedWorkflow Procedure for Workitem: '
1978 || l_WorkitemID || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID
1979 || ' LineItemID: ' || l_LineItemID || ' Check WorkitemConfiguration';
1980
1981 RAISE e_NullWIWfProcException;
1982 end if;
1983
1984 /* The user has defined a procedure for the workitem*/
1985 /* Execute the procedure dynamically and get the item type and item key */
1986 /* The Create Process for the Workflo SHOULD be done in the Procedure */
1987
1988 XDP_UTILITIES.CallWIWorkflowProc ( P_PROCEDURE_NAME => l_UserWIProc,
1989 P_ORDER_ID => l_OrderID,
1990 P_LINE_ITEM_ID => l_LineItemID,
1991 P_WI_INSTANCE_ID => l_WIInstanceID,
1992 P_WF_ITEM_TYPE => t_ChildTypes(l_Counter),
1993 P_WF_ITEM_KEY => t_ChildKeys(l_Counter),
1994 P_WF_PROCESS_NAME => l_UserWFProcess,
1995 P_RETURN_CODE => l_ErrCode,
1996 P_ERROR_DESCRIPTION => l_ErrDescription);
1997
1998 if l_ErrCode <> 0 then
1999 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Error when Executing The user-defined Workflow Procedure: '
2000 || l_UserWIProc || ' for Workitem: ' || l_WorkitemID || 'Order: ' || l_OrderID
2001 || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '
2002 || SUBSTR(l_ErrDescription, 1, 1500);
2003
2004 RAISE e_CallWIWFProcException;
2005 end if;
2006
2007 /*
2008 Raja: 04-Nov-1999
2009 Must set the parent child relation ships
2010 */
2011
2012 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
2013 itemkey => t_ChildKeys(l_Counter),
2014 parent_itemtype => LaunchWISeqProcess.itemtype,
2015 parent_itemkey => LaunchWISeqProcess.itemkey,
2016 parent_context => null);
2017
2018 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
2019 itemkey => t_ChildKeys(l_Counter),
2020 AttrName => 'LINE_ITEM_ID',
2021 AttrValue => l_LineItemID,
2022 ErrCode => l_ErrCode,
2023 ErrStr => l_ErrDescription);
2024
2025 if l_ErrCode <> 0 then
2026 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Adding LINE_ITEM_ID attribute for the user-defined Workflow Procedure: '
2027 || l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID
2028 || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '
2029 || SUBSTR(l_ErrDescription, 1, 1500);
2030 RAISE e_AddWFItemException;
2031 end if;
2032
2033
2034 -- Raja: 09/14/2000
2035 -- Set the WF Item Attributes WORKITEM_INSTANCE_ID, ORDER_ID
2036 -- Found by Burnsy
2037
2038 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
2039 itemkey => t_ChildKeys(l_Counter),
2040 AttrName => 'WORKITEM_INSTANCE_ID',
2041 AttrValue => l_WIInstanceID,
2042 ErrCode => l_ErrCode,
2043 ErrStr => l_ErrDescription);
2044
2045 if l_ErrCode <> 0 then
2046 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Adding WORKITEM_INSTANCE_ID attribute for the user-defined Workflow Procedure: '
2047 || l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID
2048 || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '
2049 || SUBSTR(l_ErrDescription, 1, 1500);
2050 RAISE e_AddWFItemException;
2051 end if;
2052
2053 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
2054 itemkey => t_ChildKeys(l_Counter),
2055 AttrName => 'ORDER_ID',
2056 AttrValue => l_OrderID,
2057 ErrCode => l_ErrCode,
2058 ErrStr => l_ErrDescription);
2059
2060 if l_ErrCode <> 0 then
2061 x_Progress := 'XDPCORE_WI.LaunchWorkitemProcess. Error when Adding ORDER_ID attribute for the user-defined Workflow Procedure: '
2062 || l_UserWIProc || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID
2063 || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '
2064 || SUBSTR(l_ErrDescription, 1, 1500);
2065 RAISE e_AddWFItemException;
2066 end if;
2067
2068 -- /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
2069 /*
2070 update XDP_FULFILL_WORKLIST
2071 set WF_ITEM_TYPE = t_ChildTypes(l_Counter),
2072 WF_ITEM_KEY = t_ChildKeys(l_Counter),
2073 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2074 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2075 where WORKITEM_INSTANCE_ID = l_WIInstanceID;
2076 */
2077 elsif l_wiwf = 'WORKFLOW' then
2078
2079 if (l_UserItemKeyPrefix is null) OR (l_UserItemType is null) OR
2080 (l_UserWFProcess is null) then
2081 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Invalid user-defined Workitem Workflow Configuration. Itemtype: '
2082 || l_UserItemType || ' ItemKey Prefix: ' || l_UserItemKeyPrefix || ' WF Process: '
2083 || l_UserWFProcess || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID
2084 || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID;
2085
2086 RAISE e_InvalidUserWFConfigException;
2087 end if;
2088
2089
2090 /* The user has defined the item type, process name and has given the itemkey
2091 ** prefix SFM should start the process for them
2092 */
2093
2094 t_ChildTypes(l_Counter) := l_UserItemType;
2095
2096 CreateWorkitemProcess('USER', itemtype, itemkey, t_ChildTypes(l_Counter),
2097 l_UserItemKeyPrefix, t_ChildKeys(l_Counter),
2098 l_UserWFProcess,l_OrderID, l_LineItemID, l_WorkitemID, l_WIInstanceID,
2099 'WAITFORFLOW-DEP_WI',
2100 l_ErrCode, l_ErrDescription);
2101
2102 if l_ErrCode <> 0 then
2103 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Error when creating the Workflow Process for user-defined workflow. Itemtype: '
2104 || l_UserItemType || ' ItemKey Prefix: ' || l_UserItemKeyPrefix || ' WF Process: '
2105 || l_UserWFProcess || ' for Workitem: ' || l_WorkitemID || ' Order: ' || l_OrderID
2106 || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: ' || l_LineItemID || ' Error: '
2107 || SUBSTR(l_ErrDescription, 1, 1500);
2108
2109 RAISE e_UserWFCreateException;
2110 end if;
2111
2112
2113 -- /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
2114 /*
2115 update XDP_FULFILL_WORKLIST
2116 set WF_ITEM_TYPE = t_ChildTypes(l_Counter),
2117 WF_ITEM_KEY = t_ChildKeys(l_Counter),
2118 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2119 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2120 where WORKITEM_INSTANCE_ID = l_WIInstanceID;
2121 */
2122 elsif l_wiwf = 'DYNAMIC' then
2123 /* The Workitem List is dynamic */
2124
2125 t_ChildTypes(l_Counter) := 'XDPPROV';
2126
2127 CreateWorkitemProcess('XDP', itemtype, itemkey, t_ChildTypes(l_Counter),
2128 null, t_ChildKeys(l_Counter),
2129 'WORKITEM_PROCESS',l_OrderID, l_LineItemID, l_WorkitemID, l_WIInstanceID,
2130 'WAITFORFLOW-DEP_WI',
2131 l_ErrCode, l_ErrDescription);
2132
2133 if l_ErrCode <> 0 then
2134 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Error when creating the WORKITEM_PROCESS Workflow Process for Workitem: '
2135 || l_WorkitemID || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID
2136 || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
2137
2138 RAISE e_WIWFCreateException;
2139 end if;
2140
2141 elsif l_wiwf = 'STATIC' then
2142 /* The workitem is a regular FA list process */
2143 t_ChildTypes(l_Counter) := 'XDPPROV';
2144
2145 CreateWorkitemProcess('XDP', itemtype, itemkey, t_ChildTypes(l_Counter),
2146 null, t_ChildKeys(l_Counter),
2147 'WORKITEM_PROCESS',l_OrderID, l_LineItemID, l_WorkitemID, l_WIInstanceID,
2148 'WAITFORFLOW-DEP_WI',
2149 l_ErrCode, l_ErrDescription);
2150
2151 if l_ErrCode <> 0 then
2152 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Error when creating the WORKITEM_PROCESS Workflow Process for Workitem: '
2153 || l_WorkitemID || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID
2154 || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
2155
2156 RAISE e_WIWFCreateException;
2157 end if;
2158
2159 else
2160 /* Invalid Configuration again */
2161 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Invalid Workitem type: ' || l_wiwf || ' for workitemID: '
2162 || l_WorkitemID || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID
2163 || ' LineItemID: ' || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
2164
2165 RAISE e_InvalidWITypeException;
2166 end if;
2167
2168
2169 END LOOP;
2170
2171 close c_WIList;
2172
2173 if l_Counter = 0 and l_CurrentSequence = 0 then
2174 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. No Workitems found to be processed for Order: ' || l_OrderID || ' LineItemID: ' || l_LineItemID;
2175
2176 RAISE e_NoWIsFoundException;
2177 else
2178
2179 XDPCORE.CheckNAddItemAttrNumber (itemtype => LaunchWISeqProcess.itemtype,
2180 itemkey => LaunchWISeqProcess.itemkey,
2181 AttrName => 'CURRENT_WI_SEQUENCE',
2182 AttrValue => l_CurrentSequence,
2183 ErrCode => ErrCode,
2184 ErrStr => ErrStr);
2185
2186 if ErrCode <> 0 then
2187 x_progress := 'In XDPCORE_WI.LaunchWISeqProcess. Error when adding Item Attribute CURRENT_WI_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
2188 raise e_AddAttributeException;
2189 end if;
2190
2191 /* Update the XDP_FULFILL_WORKLIST table with the User defined Workitem Item Type and Item Key */
2192
2193 update XDP_FULFILL_WORKLIST
2194 set WF_ITEM_TYPE = t_ChildTypes(l_Counter),
2195 WF_ITEM_KEY = t_ChildKeys(l_Counter),
2196 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2197 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2198 where WORKITEM_INSTANCE_ID = l_WIInstanceID;
2199
2200 /* Enqueue all the Wormitems in the Workitem Queue */
2201 FOR i in 1..l_Counter LOOP
2202
2203 XDP_AQ_UTILITIES.ADD_WORKITEM_TOQ ( P_ORDER_ID => l_OrderID,
2204 P_WI_INSTANCE_ID => t_WIIDList(i),
2205 P_PROV_DATE => GetWIProvisioningDate(t_WIIDList(i)),
2206 P_WF_ITEM_TYPE => t_ChildTypes(i),
2207 P_WF_ITEM_KEY => t_ChildKeys(i),
2208 P_PRIORITY => t_PriorityList(i),
2209 P_RETURN_CODE => l_ErrCode,
2210 P_ERROR_DESCRIPTION => l_ErrDescription);
2211
2212 if l_ErrCode <> 0 then
2213 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Error When Adding Workitemto Queue. Itemtype : '
2214 || t_ChildTypes(i) || ' Itemkey: ' || t_ChildKeys(i) || ' for workitemID: ' || l_WorkitemID
2215 || ' Order: ' || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID || ' LineItemID: '
2216 || l_LineItemID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
2217
2218 RAISE e_AddWItoQException;
2219 end if;
2220
2221 END LOOP;
2222 end if;
2223
2224 exception
2225 when e_AddAttributeException then
2226 if c_WIList%ISOPEN then
2227 close c_WIList;
2228 end if;
2229
2230 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2231 raise;
2232
2233 when e_NoWIsFoundException then
2234 if c_WIList%ISOPEN then
2235 close c_WIList;
2236 end if;
2237
2238 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2239 raise;
2240
2241 when e_CallWIWfProcException then
2242 if c_WIList%ISOPEN then
2243 close c_WIList;
2244 end if;
2245
2246 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2247 raise;
2248
2249 when e_NullWIWfProcException then
2250 if c_WIList%ISOPEN then
2251 close c_WIList;
2252 end if;
2253
2254 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2255 raise;
2256
2257 when e_CallWIMapProcException then
2258 if c_WIList%ISOPEN then
2259 close c_WIList;
2260 end if;
2261
2262 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2263 raise;
2264
2265 when e_InvalidWITypeException then
2266 if c_WIList%ISOPEN then
2267 close c_WIList;
2268 end if;
2269
2270 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2271 raise;
2272
2273 when e_InvalidUserWFConfigException then
2274 if c_WIList%ISOPEN then
2275 close c_WIList;
2276 end if;
2277
2278 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2279 raise;
2280
2281 when e_UserWFCreateException then
2282 if c_WIList%ISOPEN then
2283 close c_WIList;
2284 end if;
2285
2286 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2287 raise;
2288
2289 when e_WIWFCreateException then
2290 if c_WIList%ISOPEN then
2291 close c_WIList;
2292 end if;
2293
2294 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2295 raise;
2296
2297 when e_AddWItoQException then
2298 if c_WIList%ISOPEN then
2299 close c_WIList;
2300 end if;
2301
2302 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2303 raise;
2304
2305 when e_AddWFItemException then
2306 if c_WIList%ISOPEN then
2307 close c_WIList;
2308 end if;
2309
2310 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2311 raise;
2312
2313 when others then
2314 x_Progress := 'XDPCORE_WI.LaunchWISeqProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2315 wf_core.context('XDPCORE_WI', 'LaunchWISeqProcess', itemtype, itemkey, null, x_Progress);
2316 raise;
2317 end LaunchWISeqProcess;
2318
2319
2320
2321
2322 Procedure GetWIParamOnStart(itemtype in varchar2,
2323 itemkey in varchar2)
2324 is
2325
2326 x_Progress VARCHAR2(2000);
2327 l_WIInstanceID NUMBER;
2328
2329 begin
2330
2331 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => GetWIParamOnStart.itemtype,
2332 itemkey => GetWIParamOnStart.itemkey,
2333 aname => 'WORKITEM_INSTANCE_ID');
2334
2335 xdp_engine.EvaluateWIParamsOnStart(l_WIInstanceID );
2336
2337
2338 exception
2339 when others then
2340 x_Progress := 'XDPCORE_WI.GetWIParamOnStart. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2341 wf_core.context('XDPCORE_WI', 'GetWIParamOnStart', itemtype, itemkey, null, x_Progress);
2342 raise;
2343 end GetWIParamOnStart;
2344
2345
2346 Function IsWIAborted(WIInstanceID in number) return boolean
2347
2348 is
2349 l_Status boolean := FALSE;
2350 l_WIState varchar2(40);
2351 l_LineState varchar2(40);
2352 l_LineItemID number;
2353
2354
2355 cursor c_GetWIState(p_WIInstanceID number) is
2356 select status_code, line_item_id from
2357 XDP_FULFILL_WORKLIST
2358 where workitem_instance_id = p_WIInstanceID
2359 for update;
2360
2361 cursor c_GetLineState(p_LineItemID number) is
2362 select status_code from
2363 xdp_order_line_items
2364 where line_item_id = p_LineItemID
2365 for update;
2366
2367 e_NoLinesFoundException exception;
2368 e_NoWIFoundException exception;
2369 begin
2370
2371 Savepoint GetWIState;
2372
2373 open c_GetWIState(WIInstanceID);
2374 Fetch c_GetWIState into l_WIState, l_LineItemID;
2375
2376 if c_GetWIState%NOTFOUND then
2377 rollback to GetWIState;
2378 raise e_NoWIFoundException;
2379 end if;
2380
2381 close c_GetWIState;
2382
2383 if l_WIState IN ('CANCELED','ABORTED') then
2384 l_Status := TRUE;
2385 rollback to GetWIState;
2386 return l_Status;
2387 else
2388 open c_GetLineState(l_LineItemID);
2389 Fetch c_GetLineState into l_LineState;
2390
2391 if c_GetLineState%NOTFOUND then
2392 rollback to GetWIState;
2393 raise e_NoLinesFoundException;
2394 end if;
2395
2396 close c_GetLineState;
2397
2398 if l_LineState IN ('CANCELED','ABORTED') then
2399 l_Status := TRUE;
2400 rollback to GetWIState;
2401 return l_Status;
2402 end if;
2403
2404 end if;
2405
2406 rollback to GetWIState;
2407 l_Status := FALSE;
2408 return l_Status;
2409
2410
2411 exception
2412 when others then
2413 if c_GetWIState%ISOPEN then
2414 close c_GetWIState;
2415 end if;
2416 if c_GetLineState%ISOPEN then
2417 close c_GetLineState;
2418 end if;
2419 rollback to GetWIState;
2420 raise;
2421 end IsWIAborted;
2422
2423 Procedure EvaluateWIParamsOnStart(WIInstanceID in number)
2424 is
2425 begin
2426
2427 null;
2428
2429 end EvaluateWIParamsOnStart;
2430
2431 PROCEDURE UPDATE_WORKITEM_STATUS(p_workitem_instance_id IN NUMBER,
2432 p_status_code IN VARCHAR2,
2433 p_itemtype IN VARCHAR2,
2434 p_itemkey IN VARCHAR2) IS
2435 PRAGMA AUTONOMOUS_TRANSACTION ;
2436 x_progress VARCHAR2(20000);
2437 BEGIN
2438
2439 UPDATE xdp_fulfill_worklist
2440 SET status_code = p_status_code ,
2441 wf_item_type = p_itemtype ,
2442 wf_item_key = p_itemkey ,
2443 last_update_date = sysdate,
2444 last_updated_by = fnd_global.user_id,
2445 last_update_login = fnd_global.login_id
2446 WHERE workitem_instance_id = p_workitem_instance_id;
2447
2448 COMMIT;
2449
2450 EXCEPTION
2451 WHEN others THEN
2452 x_Progress := 'XDPCORE_WI.UPDATE_WORKITEM_STATUS. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2453 wf_core.context('XDPCORE_WI', 'UPDATE_WORKITEM_STATUS', p_itemtype, p_itemkey, null, x_Progress);
2454 rollback;
2455 raise;
2456
2457 END UPDATE_WORKITEM_STATUS ;
2458
2459 FUNCTION GetWIProvisioningDate(p_workitem_instance_id IN NUMBER)
2460 RETURN DATE IS
2461
2462 l_prov_date DATE ;
2463 x_progress VARCHAR2(2000);
2464
2465 BEGIN
2466 SELECT provisioning_date
2467 INTO l_prov_date
2468 FROM xdp_fulfill_worklist
2469 WHERE workitem_instance_id = p_workitem_instance_id ;
2470
2471 RETURN l_prov_date;
2472
2473
2474 EXCEPTION
2475 WHEN others THEN
2476 x_Progress := 'XDPCORE_WI.GetWIProvisioningDate. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2477 wf_core.context('XDPCORE_WI', 'GetWIProvisioningDate', null, null, null, x_Progress);
2478 raise;
2479 END GetWIProvisioningDate ;
2480
2481 Function GET_WI_RESPONSIBILITY (itemtype in varchar2,
2482 itemkey in varchar2 ) return varchar2
2483 IS
2484 l_wi_responsiblilty VARCHAR2(100);
2485 l_WIInstanceID NUMBER;
2486 l_WorkitemID NUMBER;
2487 x_progress varchar2(2000);
2488 BEGIN
2489
2490 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => GET_WI_RESPONSIBILITY.itemtype,
2491 itemkey => GET_WI_RESPONSIBILITY.itemkey,
2492 aname => 'WORKITEM_INSTANCE_ID');
2493 BEGIN
2494 SELECT wis.role_name INTO l_wi_responsiblilty
2495 FROM xdp_workitems wis
2496 WHERE wis.workitem_id in ( select lst.workitem_id
2497 from xdp_fulfill_worklist lst
2498 WHERE lst.workitem_instance_id = l_WIInstanceID);
2499
2500 EXCEPTION
2501
2502 WHEN no_data_found THEN
2503 l_wi_responsiblilty := NULL;
2504
2505 END;
2506 return l_wi_responsiblilty;
2507
2508 EXCEPTION
2509 WHEN others THEN
2510 x_Progress := 'XDPCORE_WI.GET_WI_RESPONSIBILITY. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2511 xdpcore.context('XDPCORE_WI', 'GET_WI_RESPONSIBILITY', 'WI', l_WIInstanceID, x_Progress);
2512 wf_core.context('XDPCORE_WI', 'GET_WI_RESPONSIBILITY', 'WI', l_WIInstanceID, null, x_Progress);
2513 raise;
2514 END GET_WI_RESPONSIBILITY;
2515
2516 Function ResolveIndDepWIs(itemtype in varchar2,
2517 itemkey in varchar2) return varchar2
2518 IS
2519
2520 l_OrderID number;
2521 l_LineItemID number;
2522 l_IndFound number := 0;
2523 l_DepFound number := 0;
2524
2525 cursor c_GetIndWIList (OrderID number, LineItemID number) is
2526 select 'Y'
2527 from XDP_FULFILL_WORKLIST
2528 where ORDER_ID = Order_ID
2529 and LINE_ITEM_ID = LineItemID
2530 and status_code = 'STANDBY'
2531 and WI_SEQUENCE = 0;
2532
2533 cursor c_GetDepWIList (OrderID number, LineItemID number) is
2534 select 'Y'
2535 from XDP_FULFILL_WORKLIST
2536 where ORDER_ID = Order_ID
2537 and LINE_ITEM_ID = LineItemID
2538 and status_code = 'STANDBY'
2539 and WI_SEQUENCE > 0;
2540
2541
2542 e_NoWorkitemsFoundException exception;
2543 x_Progress VARCHAR2(2000);
2544
2545 ErrCode number;
2546 ErrStr varchar2(1996);
2547
2548 BEGIN
2549
2550 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepWIs.itemtype,
2551 itemkey => ResolveIndDepWIs.itemkey,
2552 aname => 'ORDER_ID');
2553
2554 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepWIs.itemtype,
2555 itemkey => ResolveIndDepWIs.itemkey,
2556 aname => 'LINE_ITEM_ID');
2557
2558
2559 FOR c_WIRec in c_GetIndWIList(l_OrderID, l_LineItemID ) LOOP
2560 l_IndFound := 1;
2561 EXIT;
2562 END LOOP;
2563
2564 FOR c_WIRec in c_GetDepWIList(l_OrderID, l_LineItemID ) LOOP
2565 l_DepFound := 1;
2566 EXIT;
2567 END LOOP;
2568
2569 if( l_IndFound = 1 AND l_DepFound = 1 ) THEN
2570 RETURN 'BOTH';
2571 elsif( l_IndFound = 1) THEN
2572 RETURN 'INDEPENDENT';
2573 elsif( l_DepFound = 1 ) THEN
2574 RETURN 'DEPENDENT';
2575 end if;
2576
2577
2578 EXCEPTION
2579 WHEN others THEN
2580 x_Progress := 'XDPCORE_WI. ResolveIndDepWIs. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2581 wf_core.context('XDPCORE_WI', ' ResolveIndDepWIs', null, null, null, x_Progress);
2582 raise;
2583
2584 END ResolveIndDepWIs;
2585
2586 Function get_display_name( p_WIInstanceID IN NUMBER) return varchar2
2587 IS
2588 l_display_name varchar2(100);
2589
2590 BEGIN
2591 SELECT display_name into l_display_name
2592 FROM XDP_WORKITEMS_VL wis
2593 WHERE wis.workitem_id IN ( SELECT lst.workitem_id
2594 FROM xdp_fulfill_worklist lst
2595 WHERE workitem_instance_id = p_WIInstanceID );
2596 return l_display_name;
2597
2598 EXCEPTION
2599 WHEN others THEN
2600 wf_core.context('XDPCORE_WI', 'get_display_name', null, null, null,p_WIInstanceID );
2601 raise;
2602 END get_display_name;
2603
2604 Procedure OverrideParamValue( p_WIInstanceID IN NUMBER,
2605 p_parameterName IN VARCHAR2,
2606 p_newParamValue IN VARCHAR2,
2607 p_evaluate IN VARCHAR2)
2608 IS PRAGMA AUTONOMOUS_TRANSACTION;
2609 BEGIN
2610
2611 -- should we update parameter_ref_value????
2612 IF( p_evaluate = 'NO' ) THEN
2613 UPDATE xdp_worklist_details
2614 SET parameter_value = p_newParamValue,
2615 is_value_evaluated = 'Y',
2616 modified_flag = 'Y',
2617 last_updated_by = FND_GLOBAL.USER_ID,
2618 last_update_date = sysdate,
2619 last_update_login = FND_GLOBAL.LOGIN_ID
2620 WHERE workitem_instance_id = p_WIInstanceID
2621 AND parameter_name = p_parameterName;
2622 ELSE
2623 UPDATE xdp_worklist_details
2624 SET parameter_value = p_newParamValue,
2625 modified_flag = 'Y',
2626 last_updated_by = FND_GLOBAL.USER_ID,
2627 last_update_date = sysdate,
2628 last_update_login = FND_GLOBAL.LOGIN_ID
2629 WHERE workitem_instance_id = p_WIInstanceID
2630 AND parameter_name = p_parameterName;
2631 END IF;
2632
2633 -- Commit the autonomous transaction..
2634 COMMIT;
2635
2636 EXCEPTION
2637 WHEN others THEN
2638 -- Rollback the autonomous transaction..
2639 rollback;
2640 wf_core.context('XDPCORE_WI', ' OverrideParamValue', null, null, null,p_WIInstanceID );
2641 raise;
2642 END OverrideParamValue;
2643
2644 End XDPCORE_WI;