[Home] [Help]
PACKAGE BODY: APPS.XDPCORE_FA
Source
1 PACKAGE BODY XDPCORE_FA AS
2 /* $Header: XDPCORFB.pls 120.1 2005/06/08 23:44:29 appldev $ */
3
4
5 /****
6 All Private Procedures for the Package
7 ****/
8
9 type RowidArrayType is table of rowid index by binary_integer;
10
11 Function HandleOtherWFFuncmode (funcmode in varchar2) return varchar2;
12
13 Function InitializeFAList(itemtype in varchar2,
14 itemkey in varchar2) return varchar2;
15
16 Function AreAllFAsDone (itemtype in varchar2,
17 itemkey in varchar2) return varchar2;
18
19 Procedure LaunchFAProvisioningProcess (itemtype in varchar2,
20 itemkey in varchar2);
21
22 Procedure LaunchFAProcess (itemtype in varchar2,
23 itemkey in varchar2);
24
25 Procedure LaunchFAProcessSeq (itemtype in varchar2,
26 itemkey in varchar2);
27
28 Procedure InitializeFA(itemtype in varchar2,
29 itemkey in varchar2);
30
31 Function GetFe(itemtype in varchar2,
32 itemkey in varchar2) return varchar2;
33
34 Procedure IsFEPreDefined(FAInstanceID in number,
35 ConfigError OUT NOCOPY varchar2,
36 FEID OUT NOCOPY number);
37
38 Function GetLocateFEProc (FAInstanceID in number) return varchar2;
39
40 Function GetFPProc (FAInstanceID in number, FeTypeID in number,
41 FeSwGeneric in varchar2, AdapterType in varchar2)
42 return varchar2;
43
44 Function IsChannelAvailable(itemtype in varchar2,
45 itemkey in varchar2,
46 CODFlag in varchar2,
47 AdapterStatus in varchar2) return varchar2;
48
49 Function IsAnyChannelAvailable(itemtype in varchar2,
50 itemkey in varchar2) return varchar2;
51
52 Function VerifyChannel(itemtype in varchar2,
53 itemkey in varchar2) return varchar2;
54
55 Function ProvisionFE(itemtype in varchar2,
56 itemkey in varchar2) return varchar2;
57
58 Procedure ExamineErrorCodes(errcode in number,
59 FAInstanceID in number,
60 AbortFAFlag OUT NOCOPY boolean,
61 action OUT NOCOPY varchar2);
62
63 Procedure ReleaseFEChannel(itemtype in varchar2,
64 itemkey in varchar2);
65
66 Procedure StopFAProcessing(itemtype in varchar2,
67 itemkey in varchar2,
68 actid in number);
69
70 Procedure ResetChannel(itemtype in varchar2,
71 itemkey in varchar2);
72
73 Function ResolveIndDepFAs (itemtype in varchar2,
74 itemkey in varchar2) return varchar2;
75
76 Function LaunchAllIndFAs(itemtype in varchar2,
77 itemkey in varchar2) return varchar2;
78
79 Procedure InitializeDepFAProcess(itemtype in varchar2,
80 itemkey in varchar2);
81
82 Function GetFaCaller(itemtype in varchar2,
83 itemkey in varchar2,
84 actid in number) return varchar2;
85
86 Procedure FindAdminRequestAndPublish (ChannelName in varchar2,
87 RequestFound OUT NOCOPY BOOLEAN,
88 Request OUT NOCOPY varchar2);
89
90 Function GetResubmissionJobID (itemtype in varchar2,
91 itemkey in varchar2) return number;
92
93 Procedure GetWorkitemFAMappingProc(WIInstanceID in number,
94 MappingProcFound OUT NOCOPY varchar2,
95 MappingProc OUT NOCOPY varchar2 );
96
97 Procedure PopulateFAs (WIInstanceID in number);
98
99 Function ConnectOnDemand (itemtype in varchar2,
100 itemkey in varchar2) return varchar2;
101
102 Function IsThresholdExceeded (p_fp_name in varchar2) return varchar2;
103
104 Function ErrorDuringRetry (itemtype in varchar2,
105 itemkey in varchar2) return varchar2;
106
107 Function IsThresholdReached (p_fp_name in varchar2) return varchar2;
108
109 Procedure ResetSystemHold (p_fp_name in varchar2);
110
111 Procedure DisconnectOnDemand( ChannelName in varchar2,
112 FeName in varchar2,
113 ErrCode OUT NOCOPY number,
114 ErrStr OUT NOCOPY varchar2);
115
116 Procedure SendAdapterErrorNotif(itemtype in varchar2,
117 itemkey in varchar2,
118 ChannelName in varchar2,
119 FEName in varchar2,
120 ErrorDescription in varchar2);
121
122 Procedure OverrideFE ( p_faInstanceID IN VARCHAR2,
123 p_FEName IN VARCHAR2,
124 resultout OUT NOCOPY varchar2 );
125
126 /*
127 skilaru : modified the signature to accept fa status and system hold status
128 as part of fixing the bug # 1945013.
129 NOTE: System hold status will be 'Y' for fa status SYSTEM_HOLD hold status
130 and 'N' for the rest of the fa statuses.
131 */
132 Procedure EnqueueFPQueue(itemtype in varchar2,
133 itemkey in varchar2,
134 p_system_hold in varchar2 DEFAULT 'N')
135 is
136 l_FeID number;
137
138 l_OrderID number;
139 l_WIInstanceID number;
140 l_FAInstanceID number;
141 l_ResubmissionJobID number;
142
143 l_ChannelUsageCode varchar2(40) := 'NORMAL';
144
145 l_ReProcessEnqTime DATE;
146 l_ReProcessFlag varchar2(5);
147 lv_fa_status varchar2(80);
148
149 x_Progress VARCHAR2(2000);
150
151 begin
152 l_FeID := wf_engine.GetItemAttrNumber(itemtype => EnqueueFPQueue.itemtype,
153 itemkey => EnqueueFPQueue.itemkey,
154 aname => 'FE_ID');
155
156 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => EnqueueFPQueue.itemtype,
157 itemkey => EnqueueFPQueue.itemkey,
158 aname => 'ORDER_ID');
159
160 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => EnqueueFPQueue.itemtype,
161 itemkey => EnqueueFPQueue.itemkey,
162 aname => 'WORKITEM_INSTANCE_ID');
163
164 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => EnqueueFPQueue.itemtype,
165 itemkey => EnqueueFPQueue.itemkey,
166 aname => 'FA_INSTANCE_ID');
167
168 l_ReProcessFlag := wf_engine.GetItemAttrText(itemtype => EnqueueFPQueue.itemtype,
169 itemkey => EnqueueFPQueue.itemkey,
170 aname => 'RE_PROCESS_FLAG');
171
172
173 l_ResubmissionJobID := GetResubmissionJobID(itemtype => EnqueueFPQueue.itemtype,
174 itemkey => EnqueueFPQueue.itemkey);
175
176 SavePoint EnqueueAbortFA;
177
178 if l_ResubmissionJobID <> 0 then
179 l_ChannelUsageCode := 'RESUBMISSION';
180 end if;
181
182 /*
183 ** If the Re-Process flag is set to 'Y' then there was a problem with the Dequeueing
184 ** of the Order for a previous channel. The enqueue of the order into the adapter job queue
185 ** should have the enqueue time or the previous enqueeu time into the Channel Queue. THis is
186 ** set by the Channel Queue Dequeuer. Also the flag needs to be RESET/
187 **/
188
189 if l_ReProcessFlag = 'Y' then
190
191 /* Reset the flag */
192 wf_engine.SetItemAttrText(itemtype => EnqueueFPQueue.itemtype,
193 itemkey => EnqueueFPQueue.itemkey,
194 aname => 'RE_PROCESS_FLAG',
195 avalue => 'N');
196
197 l_ReProcessEnqTime := wf_engine.GetItemAttrDate(itemtype => EnqueueFPQueue.itemtype,
198 itemkey => EnqueueFPQueue.itemkey,
199 aname => 'RE_PROCESS_ENQ_TIME');
200 else
201 l_ReProcessEnqTime := SYSDATE;
202 end if;
203
204
205 insert into XDP_ADAPTER_JOB_QUEUE (
206 JOB_ID,
207 FE_ID,
208 ORDER_ID,
209 WORKITEM_INSTANCE_ID,
210 FA_INSTANCE_ID,
211 QUEUED_ON,
212 WF_ITEM_TYPE,
213 CHANNEL_USAGE_CODE,
214 WF_ITEM_KEY,
215 SYSTEM_HOLD,
216 created_by,
217 creation_date,
218 last_updated_by,
219 last_update_date,
220 last_update_login
221 )
222 values (XDP_ADAPTER_JOB_QUEUE_S.NEXTVAL,
223 l_FeID,
224 l_OrderID,
225 l_WIInstanceID,
226 l_FAInstanceID,
227 NVL(l_ReProcessEnqTime, SYSDATE),
228 EnqueueFPQueue.itemtype,
229 l_ChannelUsageCode,
230 EnqueueFPQueue.itemkey,
231 p_system_hold,
232 FND_GLOBAL.USER_ID,
233 sysdate,
234 FND_GLOBAL.USER_ID,
235 sysdate,
236 FND_GLOBAL.LOGIN_ID);
237
238
239
240 IF p_system_hold = 'Y' THEN
241 lv_fa_status := XDP_UTILITIES.g_system_hold;
242 ELSE
243 lv_fa_status := XDP_UTILITIES.g_wait_for_resource;
244 END IF;
245 -- Change FA Instance status...
246
247 UPDATE xdp_fa_runtime_list
248 SET status_code = lv_fa_status
249 WHERE fa_instance_id = l_fainstanceid ;
250
251 /* Check if the FA is aborted. If so abort the process immediately */
252 if IsFAAborted(l_FAInstanceID) = TRUE then
253 rollback to EnqueueAbortFA;
254 wf_engine.abortprocess(itemtype => EnqueueFPQueue.itemtype,
255 itemkey => EnqueueFPQueue.itemkey);
256
257 return;
258 end if;
259
260 exception
261 when others then
262 x_Progress := 'XDPCORE_FA.EnqueueFPQueue. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
263 wf_core.context('XDPCORE_FA', 'EnqueueFPQueue', itemtype, itemkey, null, x_Progress);
264 raise;
265 end EnqueueFPQueue;
266
267
268 PROCEDURE UPDATE_FA_STATUS(p_fa_instance_id IN NUMBER,
269 p_status_code IN VARCHAR2,
270 p_itemtype IN VARCHAR2,
271 p_itemkey IN VARCHAR2 ) ;
272
273 /***********************************************
274 * END of Private Procedures/Function Definitions
275 ************************************************/
276
277
278 -- INITIALIZE_FA_LIST
279 -- Resultout
280 -- Activity Performed - Activity was completed without any errors
281 --
282
283 Procedure INITIALIZE_FA_LIST (itemtype in varchar2,
284 itemkey in varchar2,
285 actid in number,
286 funcmode in varchar2,
287 resultout OUT NOCOPY varchar2 ) IS
288
289 x_Progress VARCHAR2(2000);
290
291 l_result varchar2(15);
292 e_InitFAFailed exception;
293
294 BEGIN
295 -- RUN mode - normal process execution
296 --
297 savepoint InitFA;
298 IF (funcmode = 'RUN') THEN
299 l_result := InitializeFAList(itemtype, itemkey);
300 resultout := 'COMPLETE:'||l_result;
301
302 IF l_result = 'FAILURE' THEN
303 raise e_InitFAFailed;
304 END IF;
305
306 return;
307 ELSE
308 resultout := HandleOtherWFFuncmode(funcmode);
309 return;
310 END IF;
311
312
313 EXCEPTION
314 WHEN e_InitFAFailed THEN
315 Rollback to InitFA;
316 XDPCORE.context('XDPCORE_FA', 'INITIALIZE_FA_LIST', itemtype, itemkey );
317
318 --Log the error at the top level call
319 XDPCORE_ERROR.LOG_SESSION_ERROR( 'BUSINESS' );
320 return;
321
322 WHEN OTHERS THEN
323 wf_core.context('XDPCORE_FA', 'INITIALIZE_FA_LIST', itemtype, itemkey, to_char(actid), funcmode);
324 raise;
325 END INITIALIZE_FA_LIST;
326
327
328 -- ARE_ALL_FAS_DONE
329 -- Resultout
330 -- Activity Performed - Activity was completed without any errors
331 --
332
333 Procedure ARE_ALL_FAS_DONE (itemtype in varchar2,
334 itemkey in varchar2,
335 actid in number,
336 funcmode in varchar2,
337 resultout OUT NOCOPY varchar2 ) IS
338 l_result varchar2(10);
339 x_Progress VARCHAR2(2000);
340
341 BEGIN
342 -- RUN mode - normal process execution
343 --
344 IF (funcmode = 'RUN') THEN
345 l_result := AreAllFAsDone(itemtype, itemkey);
346 resultout := 'COMPLETE:' || l_result;
347 return;
348 ELSE
349 resultout := HandleOtherWFFuncmode(funcmode);
350 return;
351 END IF;
352
353
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 wf_core.context('XDPCORE_FA', 'ARE_ALL_FAS_DONE', itemtype, itemkey, to_char(actid), funcmode);
358 raise;
359 END ARE_ALL_FAS_DONE;
360
361
362 -- LAUNCH_FA_PROVISIONING_PROCESS
363 -- Resultout
364 -- Activity Performed - Activity was completed without any errors
365 --
366
367 Procedure LAUNCH_FA_PROVISIONING_PROCESS (itemtype in varchar2,
368 itemkey in varchar2,
369 actid in number,
370 funcmode in varchar2,
371 resultout OUT NOCOPY varchar2 ) IS
372
373 x_Progress VARCHAR2(2000);
374
375 BEGIN
376 -- RUN mode - normal process execution
377 --
378 IF (funcmode = 'RUN') THEN
379 LaunchFAProvisioningProcess(itemtype, itemkey);
380 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
381 return;
382 ELSE
383 resultout := HandleOtherWFFuncmode(funcmode);
384 return;
385 END IF;
386
387 EXCEPTION
388 WHEN OTHERS THEN
389 wf_core.context('XDPCORE_FA', 'LAUNCH_FA_PROVISIONING_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
390 raise;
391 END LAUNCH_FA_PROVISIONING_PROCESS;
392
393
394
395
396 -- LAUNCH_FA_PROCESS
397 -- Resultout
398 -- Activity Performed - Activity was completed without any errors
399 --
400
401 Procedure LAUNCH_FA_PROCESS (itemtype in varchar2,
402 itemkey in varchar2,
403 actid in number,
404 funcmode in varchar2,
405 resultout OUT NOCOPY varchar2 ) IS
406
407 x_Progress VARCHAR2(2000);
408
409 BEGIN
410 -- RUN mode - normal process execution
411 --
412 IF (funcmode = 'RUN') THEN
413 LaunchFAProcess(itemtype, itemkey);
414 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
415 return;
416 ELSE
417 resultout := HandleOtherWFFuncmode(funcmode);
418 return;
419 END IF;
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 wf_core.context('XDPCORE_FA', 'LAUNCH_FA_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
424 raise;
425 END LAUNCH_FA_PROCESS;
426
427
428 -- LAUNCH_FA_PROCESS_SEQ
429 -- Resultout
430 -- Activity Performed - Activity was completed without any errors
431 --
432 -- Your Description here:
433
434 Procedure LAUNCH_FA_PROCESS_SEQ (itemtype in varchar2,
435 itemkey in varchar2,
436 actid in number,
437 funcmode in varchar2,
438 resultout OUT NOCOPY varchar2 ) IS
439
440 x_Progress VARCHAR2(2000);
441
442 BEGIN
443 -- RUN mode - normal process execution
444 --
445 IF (funcmode = 'RUN') THEN
446 LaunchFAProcessSeq(itemtype, itemkey);
447 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
448 return;
449 ELSE
450 resultout := HandleOtherWFFuncmode(funcmode);
451 return;
452 END IF;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 wf_core.context('XDPCORE_FA', 'LAUNCH_FA_PROCESS_SEQ', itemtype, itemkey, to_char(actid), funcmode);
457 raise;
458 END LAUNCH_FA_PROCESS_SEQ;
459
460
461
462 -- GET_FE
463 -- Resultout
464 -- Activity Performed - Activity was completed without any errors
465 --
466 -- Your Description here:
467
468 Procedure GET_FE (itemtype in varchar2,
469 itemkey in varchar2,
470 actid in number,
471 funcmode in varchar2,
472 resultout OUT NOCOPY varchar2 ) IS
473
474 x_Progress VARCHAR2(2000);
475 l_result varchar2(15);
476 e_GetFEFailed exception;
477
478 BEGIN
479 -- RUN mode - normal process execution
480 --
481 Savepoint get_FE;
482 IF (funcmode = 'RUN') THEN
483 l_result := GetFe(itemtype, itemkey);
484 resultout := 'COMPLETE:' || l_result;
485 IF l_result = 'FAILURE' THEN
486 raise e_GetFEFailed;
487 END IF;
488 return;
489 ELSE
490 resultout := HandleOtherWFFuncmode(funcmode);
491 return;
492 END IF;
493
494 EXCEPTION
495
496 WHEN e_GetFEFailed THEN
497 Rollback to get_FE;
498 XDPCORE.context('XDPCORE_FA', 'GET_FE', itemtype, itemkey );
499
500 --Log the error at the top level call
501 XDPCORE_ERROR.LOG_SESSION_ERROR( 'BUSINESS' );
502 return;
503
504 WHEN OTHERS THEN
505 wf_core.context('XDPCORE_FA', 'GET_FE', itemtype, itemkey, to_char(actid), funcmode);
506 raise;
507 END GET_FE;
508
509
510 -- INITIALIZE_FA
511 -- Resultout
512 -- Activity Performed - Activity was completed without any errors
513 --
514 -- Your Description here:
515
516 Procedure INITIALIZE_FA (itemtype in varchar2,
517 itemkey in varchar2,
518 actid in number,
519 funcmode in varchar2,
520 resultout OUT NOCOPY varchar2 ) IS
521
522
523 x_Progress VARCHAR2(2000);
524
525 BEGIN
526 -- RUN mode - normal process execution
527 --
528 IF (funcmode = 'RUN') THEN
529 InitializeFA(itemtype, itemkey);
530 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
531 return;
532 ELSE
533 resultout := HandleOtherWFFuncmode(funcmode);
534 return;
535 END IF;
536
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 wf_core.context('XDPCORE_FA', 'INITIALIZE_FA', itemtype, itemkey, to_char(actid), funcmode);
541 raise;
542 END INITIALIZE_FA;
543
544
545
546 -- ENQUEUE_FP_QUEUE
547 -- Resultout
548 -- Activity Performed - Activity was completed without any errors
549 --
550 -- Your Description here:
551
552
553 Procedure ENQUEUE_FP_QUEUE (itemtype in varchar2,
554 itemkey in varchar2,
555 actid in number,
556 funcmode in varchar2,
557 resultout OUT NOCOPY varchar2 ) IS
558
559 x_Progress VARCHAR2(2000);
560
561 BEGIN
562 -- RUN mode - normal process execution
563 --
564 IF (funcmode = 'RUN') THEN
565 EnqueueFPQueue( itemtype => ENQUEUE_FP_QUEUE.itemtype,
566 itemkey => ENQUEUE_FP_QUEUE.itemkey);
567
568 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
569 return;
570 ELSE
571 resultout := HandleOtherWFFuncmode(funcmode);
572 return;
573 END IF;
574
575 EXCEPTION
576 WHEN OTHERS THEN
577 wf_core.context('XDPCORE_FA', 'ENQUEUE_FP_QUEUE', itemtype, itemkey, to_char(actid), funcmode);
578 raise;
579 END ENQUEUE_FP_QUEUE;
580
581
582 Procedure ENQUEUE_FP_HOLD (itemtype in varchar2,
583 itemkey in varchar2,
584 actid in number,
585 funcmode in varchar2,
586 resultout OUT NOCOPY varchar2 ) IS
587
588 x_Progress VARCHAR2(2000);
589
590 BEGIN
591 -- RUN mode - normal process execution
592 --
593 IF (funcmode = 'RUN') THEN
594 EnqueueFPQueue(itemtype, itemkey, 'Y');
595 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
596 return;
597 ELSE
598 resultout := HandleOtherWFFuncmode(funcmode);
599 return;
600 END IF;
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 wf_core.context('XDPCORE_FA', 'ENQUEUE_FP_HOLD', itemtype, itemkey, to_char(actid), funcmode);
605 raise;
606 END ENQUEUE_FP_HOLD;
607
608
609 -- PROVISION_FE
610 -- Resultout
611 -- Activity Performed - Activity was completed without any errors
612 --
613 -- Your Description here:
614
615 Procedure PROVISION_FE (itemtype in varchar2,
616 itemkey in varchar2,
617 actid in number,
618 funcmode in varchar2,
619 resultout OUT NOCOPY varchar2 ) IS
620
621 l_result varchar2(20);
622 x_Progress VARCHAR2(2000);
623
624 BEGIN
625 -- RUN mode - normal process execution
626 --
627 IF (funcmode = 'RUN') THEN
628 l_result := ProvisionFE(itemtype, itemkey);
629 resultout := 'COMPLETE:' || l_result;
630 return;
631 ELSE
632 resultout := HandleOtherWFFuncmode(funcmode);
633 return;
634 END IF;
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 wf_core.context('XDPCORE_FA', 'PROVISION_FE', itemtype, itemkey, to_char(actid), funcmode);
639 raise;
640 END PROVISION_FE;
641
642
643 -- GET_FA_CALLER
644 -- Resultout
645 -- Activity Performed - Activity was completed without any errors
646 --
647 -- Your Description here:
648
649
650 Procedure GET_FA_CALLER (itemtype in varchar2,
651 itemkey in varchar2,
652 actid in number,
653 funcmode in varchar2,
654 resultout OUT NOCOPY varchar2 )
655 is
656
657 l_result varchar2(80);
658 x_Progress VARCHAR2(2000);
659
660 BEGIN
661 -- RUN mode - normal process execution
662 --
663 IF (funcmode = 'RUN') THEN
664 l_result := GetFaCaller(itemtype, itemkey, actid);
665 resultout := 'COMPLETE:' || l_result;
666 return;
667 ELSE
668 resultout := HandleOtherWFFuncmode(funcmode);
669 return;
670 END IF;
671
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 wf_core.context('XDPCORE_FA', 'GET_FA_CALLER', itemtype, itemkey, to_char(actid), funcmode);
676 raise;
677 END GET_FA_CALLER;
678
679
680
681 -- RELEASE_FE_CHANNEL
682 -- Resultout
683 -- Activity Performed - Activity was completed without any errors
684 --
685 -- Your Description here:
686
687 Procedure RELEASE_FE_CHANNEL (itemtype in varchar2,
688 itemkey in varchar2,
689 actid in number,
690 funcmode in varchar2,
691 resultout OUT NOCOPY varchar2 ) IS
692
693 x_Progress VARCHAR2(2000);
694
695 BEGIN
696 -- RUN mode - normal process execution
697 --
698 IF (funcmode = 'RUN') THEN
699 ReleaseFEChannel(itemtype, itemkey);
700 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
701 return;
702 ELSE
703 resultout := HandleOtherWFFuncmode(funcmode);
704 return;
705 END IF;
706
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 wf_core.context('XDPCORE_FA', 'RELEASE_FE_CHANNEL', itemtype, itemkey, to_char(actid), funcmode);
711 raise;
712 END RELEASE_FE_CHANNEL;
713
714
715
716 -- STOP_FA_PROCESSING
717 -- Resultout
718 -- Activity Performed - Activity was completed without any errors
719 --
720 -- Your Description here:
721
722 Procedure STOP_FA_PROCESSING (itemtype in varchar2,
723 itemkey in varchar2,
724 actid in number,
725 funcmode in varchar2,
726 resultout OUT NOCOPY varchar2 ) IS
727
728 x_Progress VARCHAR2(2000);
729
730 BEGIN
731 -- RUN mode - normal process execution
732 --
733 IF (funcmode = 'RUN') THEN
734 StopFAProcessing(itemtype, itemkey, actid);
735 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
736 return;
737 ELSE
738 resultout := HandleOtherWFFuncmode(funcmode);
739 return;
740 END IF;
741
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 wf_core.context('XDPCORE_FA', 'STOP_FA_PROCESSING', itemtype, itemkey, to_char(actid), funcmode);
746 raise;
747 END STOP_FA_PROCESSING;
748
749
750 -- IS_CHANNEL_AVAILABLE
751 -- Resultout
752 -- Activity Performed - Activity was completed without any errors
753 --
754 -- Your Description here:
755
756 Procedure IS_CHANNEL_AVAILABLE (itemtype in varchar2,
757 itemkey in varchar2,
758 actid in number,
759 funcmode in varchar2,
760 resultout OUT NOCOPY varchar2 ) IS
761 l_result varchar2(80);
762 x_Progress VARCHAR2(2000);
763
764 BEGIN
765 -- RUN mode - normal process execution
766 --
767 IF (funcmode = 'RUN') THEN
768 l_result := IsChannelAvailable(itemtype, itemkey, 'N', 'IDLE');
769 resultout := 'COMPLETE:' || l_result;
770 return;
771 ELSE
772 resultout := HandleOtherWFFuncmode(funcmode);
773 return;
774 END IF;
775
776
777 EXCEPTION
778 WHEN OTHERS THEN
779 wf_core.context('XDPCORE_FA', 'IS_CHANNEL_AVAILABLE', itemtype, itemkey, to_char(actid), funcmode);
780 raise;
781 END IS_CHANNEL_AVAILABLE;
782
783 Procedure IS_ANY_CHANNEL_AVAILABLE (itemtype in varchar2,
784 itemkey in varchar2,
785 actid in number,
786 funcmode in varchar2,
787 resultout OUT NOCOPY varchar2 ) IS
788 l_result varchar2(80);
789 x_Progress VARCHAR2(2000);
790
791 BEGIN
792 -- RUN mode - normal process execution
793 --
794 IF (funcmode = 'RUN') THEN
795 l_result := IsAnyChannelAvailable(itemtype, itemkey);
796 resultout := 'COMPLETE:' || l_result;
797 return;
798 ELSE
799 resultout := HandleOtherWFFuncmode(funcmode);
800 return;
801 END IF;
802
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 wf_core.context('XDPCORE_FA', 'IS_ANY_CHANNEL_AVAILABLE', itemtype, itemkey, to_char(actid), funcmode);
807 raise;
808 END IS_ANY_CHANNEL_AVAILABLE;
809
810
811 -- IS_COD_CHANNEL_AVAILABLE
812 -- Resultout
813 -- Yes/No - Yes if a Connect-On-Demand Channel is available
814 -- No if No such channel is configured or not available
815 -- to be used at this time
816 --
817 -- Your Description here:
818
819 Procedure IS_COD_CHANNEL_AVAILABLE (itemtype in varchar2,
820 itemkey in varchar2,
821 actid in number,
822 funcmode in varchar2,
823 resultout OUT NOCOPY varchar2 ) IS
824 l_result varchar2(80);
825 x_Progress VARCHAR2(2000);
826
827 BEGIN
828 -- RUN mode - normal process execution
829 --
830 IF (funcmode = 'RUN') THEN
831 l_result := IsChannelAvailable(itemtype, itemkey, 'Y', 'DISCONNECTED');
832 resultout := 'COMPLETE:' || l_result;
833 return;
834 ELSE
835 resultout := HandleOtherWFFuncmode(funcmode);
836 return;
837 END IF;
838
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 wf_core.context('XDPCORE_FA', 'IS_COD_CHANNEL_AVAILABLE', itemtype, itemkey, to_char(actid), funcmode);
843 raise;
844 END IS_COD_CHANNEL_AVAILABLE;
845
846
847
848 -- CONNECT_ON_DEMAND
849 -- Resultout
850 -- Success/Failure - Success if the Adapter Connected Successfully
851 -- Failure if the Connect Procedure fails
852
853 Procedure CONNECT_ON_DEMAND (itemtype in varchar2,
854 itemkey in varchar2,
855 actid in number,
856 funcmode in varchar2,
857 resultout OUT NOCOPY varchar2) IS
858 l_result varchar2(10);
859 x_Progress VARCHAR2(2000);
860
861 BEGIN
862 -- RUN mode - normal process execution
863 --
864 IF (funcmode = 'RUN') THEN
865 l_result := ConnectOnDemand(itemtype, itemkey);
866 resultout := 'COMPLETE:' || l_result;
867 return;
868 ELSE
869 resultout := HandleOtherWFFuncmode(funcmode);
870 return;
871 END IF;
872
873 EXCEPTION
874 WHEN OTHERS THEN
875 wf_core.context('XDPCORE_FA', 'CONNECT_ON_DEMAND', itemtype, itemkey, to_char(actid), funcmode);
876 raise;
877 END CONNECT_ON_DEMAND;
878
879
880 -- WAIT_IN_FP_QUEUE
881 -- Resultout
882 -- Activity Performed - Activity was completed without any errors
883 --
884 -- Your Description here:
885
886 Procedure WAIT_IN_FP_QUEUE (itemtype in varchar2,
887 itemkey in varchar2,
888 actid in number,
889 funcmode in varchar2,
890 resultout OUT NOCOPY varchar2 ) IS
891
892 x_Progress VARCHAR2(2000);
893
894 BEGIN
895 -- RUN mode - normal process execution
896 --
897 IF (funcmode = 'RUN') THEN
898 -- <your procedure here>
899 resultout := 'COMPLETE:<result>';
900 return;
901 ELSE
902 resultout := HandleOtherWFFuncmode(funcmode);
903 return;
904 END IF;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 wf_core.context('XDPCORE_FA', 'WAIT_IN_FP_QUEUE', itemtype, itemkey, to_char(actid), funcmode);
909 raise;
910 END WAIT_IN_FP_QUEUE;
911
912
913
914 -- VERIFY_CHANNEL
915 -- Resultout
916 -- Activity Performed - Activity was completed without any errors
917 --
918 -- Your Description here:
919
920 Procedure VERIFY_CHANNEL (itemtype in varchar2,
921 itemkey in varchar2,
922 actid in number,
923 funcmode in varchar2,
924 resultout OUT NOCOPY varchar2 ) IS
925
926 x_Progress VARCHAR2(2000);
927 l_Result varchar2(30);
928 BEGIN
929 -- RUN mode - normal process execution
930 --
931 IF (funcmode = 'RUN') THEN
932 l_Result := VerifyChannel(itemtype, itemkey);
933 resultout := 'COMPLETE:' || l_Result;
934 return;
935 ELSE
936 resultout := HandleOtherWFFuncmode(funcmode);
937 return;
938 END IF;
939
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 wf_core.context('XDPCORE_FA', 'VERIFY_CHANNEL', itemtype, itemkey, to_char(actid), funcmode);
944 raise;
945 END VERIFY_CHANNEL;
946
947
948
949 -- RESET_CHANNEL
950 -- Resultout
951 -- Activity Performed - Activity was completed without any errors
952 --
953 -- Your Description here:
954
955 Procedure RESET_CHANNEL (itemtype in varchar2,
956 itemkey in varchar2,
957 actid in number,
958 funcmode in varchar2,
959 resultout OUT NOCOPY varchar2 ) IS
960
961 x_Progress VARCHAR2(2000);
962
963 BEGIN
964 -- RUN mode - normal process execution
965 --
966 IF (funcmode = 'RUN') THEN
967 ResetChannel(itemtype, itemkey);
968 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
969 return;
970 ELSE
971 resultout := HandleOtherWFFuncmode(funcmode);
972 return;
973 END IF;
974
975
976 EXCEPTION
977 WHEN OTHERS THEN
978 wf_core.context('XDPCORE_FA', 'RESET_CHANNEL', itemtype, itemkey, to_char(actid), funcmode);
979 raise;
980 END RESET_CHANNEL;
981
982
983 Procedure RESOLVE_IND_DEP_FAS (itemtype in varchar2,
984 itemkey in varchar2,
985 actid in number,
986 funcmode in varchar2,
987 resultout OUT NOCOPY varchar2 ) IS
988
989 x_Progress VARCHAR2(2000);
990 l_Result VARCHAR2(15);
991
992 BEGIN
993 -- RUN mode - normal process execution
994 --
995 IF (funcmode = 'RUN') THEN
996 l_Result := ResolveIndDepFAs(itemtype, itemkey);
997 resultout := 'COMPLETE:' || l_Result;
998 return;
999 ELSE
1000 resultout := HandleOtherWFFuncmode(funcmode);
1001 return;
1002 END IF;
1003
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 wf_core.context('XDPCORE_FA', 'RESOLVE_IND_DEP_FAS', itemtype, itemkey, to_char(actid), funcmode);
1008 raise;
1009 END RESOLVE_IND_DEP_FAS;
1010
1011 Procedure LAUNCH_ALL_IND_FAS (itemtype in varchar2,
1012 itemkey in varchar2,
1013 actid in number,
1014 funcmode in varchar2,
1015 resultout OUT NOCOPY varchar2 ) IS
1016
1017 x_Progress VARCHAR2(2000);
1018
1019 l_result varchar2(1) := 'N';
1020 BEGIN
1021 -- RUN mode - normal process execution
1022 --
1023 IF (funcmode = 'RUN') THEN
1024 l_result := LaunchAllIndFAs(itemtype, itemkey);
1025 resultout := 'COMPLETE:' || l_result;
1026 return;
1027 ELSE
1028 resultout := HandleOtherWFFuncmode(funcmode);
1029 return;
1030 END IF;
1031
1032
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 wf_core.context('XDPCORE_FA', 'LAUNCH_ALL_IND_FAS', itemtype, itemkey, to_char(actid), funcmode);
1036 raise;
1037 END LAUNCH_ALL_IND_FAS;
1038
1039 Procedure INITIALIZE_DEP_FA_PROCESS (itemtype in varchar2,
1040 itemkey in varchar2,
1041 actid in number,
1042 funcmode in varchar2,
1043 resultout OUT NOCOPY varchar2 ) IS
1044
1045 x_Progress VARCHAR2(2000);
1046
1047 BEGIN
1048 -- RUN mode - normal process execution
1049 --
1050 IF (funcmode = 'RUN') THEN
1051 InitializeDepFAProcess(itemtype, itemkey);
1052 resultout := 'COMPLETE';
1053 return;
1054 ELSE
1055 resultout := HandleOtherWFFuncmode(funcmode);
1056 return;
1057 END IF;
1058
1059
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 wf_core.context('XDPCORE_FA', 'INITIALIZE_DEP_FA_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
1063 raise;
1064 END INITIALIZE_DEP_FA_PROCESS;
1065
1066
1067 Procedure IS_THRESHOLD_EXCEEDED (itemtype in varchar2,
1068 itemkey in varchar2,
1069 actid in number,
1070 funcmode in varchar2,
1071 resultout OUT NOCOPY varchar2 ) IS
1072
1073 x_Progress VARCHAR2(2000);
1074 l_result varchar2(1) := 'N';
1075 lv_fp_name varchar2(80);
1076 ErrCode NUMBER;
1077 ErrStr varchar2(1000);
1078 BEGIN
1079 -- RUN mode - normal process execution
1080 --
1081 IF (funcmode = 'RUN') THEN
1082 lv_fp_name := wf_engine.getItemAttrText( itemtype => IS_THRESHOLD_EXCEEDED.itemtype,
1083 itemkey => IS_THRESHOLD_EXCEEDED.itemkey,
1084 aname => g_fp_name );
1085 l_result := IsThresholdExceeded(lv_fp_name);
1086 -- set the channel name attribute.. so that dequeuing fe ready queue will not fail..
1087 IF ( l_result = 'Y' ) THEN
1088 XDPCORE.CheckNAddItemAttrText (itemtype => IS_THRESHOLD_EXCEEDED.itemtype,
1089 itemkey => IS_THRESHOLD_EXCEEDED.itemkey,
1090 AttrName => 'CHANNEL_NAME',
1091 AttrValue => NULL,
1092 ErrCode => ErrCode,
1093 ErrStr => ErrStr);
1094 END IF;
1095
1096 resultout := 'COMPLETE:' || l_result;
1097 return;
1098 ELSE
1099 resultout := HandleOtherWFFuncmode(funcmode);
1100 return;
1101 END IF;
1102
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 wf_core.context('XDPCORE_FA', 'IS_THRESHOLD_EXCEEDED', itemtype, itemkey, to_char(actid), funcmode);
1107 raise;
1108 END IS_THRESHOLD_EXCEEDED;
1109
1110
1111
1112 Procedure ERROR_DURING_RETRY (itemtype in varchar2,
1113 itemkey in varchar2,
1114 actid in number,
1115 funcmode in varchar2,
1116 resultout OUT NOCOPY varchar2 ) IS
1117
1118 x_Progress VARCHAR2(2000);
1119 l_result varchar2(1) := 'N';
1120 BEGIN
1121 -- RUN mode - normal process execution
1122 --
1123 IF (funcmode = 'RUN') THEN
1124 l_result := ErrorDuringRetry(itemtype, itemkey);
1125 resultout := 'COMPLETE:' || l_result;
1126 return;
1127 ELSE
1128 resultout := HandleOtherWFFuncmode(funcmode);
1129 return;
1130 END IF;
1131
1132
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 wf_core.context('XDPCORE_FA', 'ERROR_DURING_RETRY', itemtype, itemkey, to_char(actid), funcmode);
1136 raise;
1137 END ERROR_DURING_RETRY;
1138
1139
1140 Procedure IS_THRESHOLD_REACHED (itemtype in varchar2,
1141 itemkey in varchar2,
1142 actid in number,
1143 funcmode in varchar2,
1144 resultout OUT NOCOPY varchar2 ) IS
1145
1146 x_Progress VARCHAR2(2000);
1147 l_result varchar2(40);
1148 lv_fp_name varchar2(80);
1149 BEGIN
1150 -- RUN mode - normal process execution
1151 --
1152 IF (funcmode = 'RUN') THEN
1153 lv_fp_name := wf_engine.getItemAttrText( itemtype => IS_THRESHOLD_REACHED.itemtype,
1154 itemkey => IS_THRESHOLD_REACHED.itemkey,
1155 aname => g_fp_name );
1156
1157
1158 l_result := IsThresholdReached(lv_fp_name);
1159 resultout := 'COMPLETE:' || l_result;
1160 return;
1161 ELSE
1162 resultout := HandleOtherWFFuncmode(funcmode);
1163 return;
1164 END IF;
1165
1166
1167 EXCEPTION
1168 WHEN OTHERS THEN
1169 wf_core.context('XDPCORE_FA', 'IS_THRESHOLD_REACHED', itemtype, itemkey, to_char(actid), funcmode);
1170 raise;
1171 END IS_THRESHOLD_REACHED;
1172
1173
1174 Procedure RESET_SYSTEM_HOLD (itemtype in varchar2,
1175 itemkey in varchar2,
1176 actid in number,
1177 funcmode in varchar2,
1178 resultout OUT NOCOPY varchar2 ) IS
1179
1180 lv_fp_name varchar2(80);
1181 x_Progress VARCHAR2(2000);
1182 BEGIN
1183 -- RUN mode - normal process execution
1184 --
1185 IF (funcmode = 'RUN') THEN
1186 lv_fp_name := wf_engine.getItemAttrText( itemtype => RESET_SYSTEM_HOLD.itemtype,
1187 itemkey => RESET_SYSTEM_HOLD.itemkey,
1188 aname => g_fp_name );
1189 ResetSystemHold( lv_fp_name);
1190 resultout := 'COMPLETE';
1191 return;
1192 ELSE
1193 resultout := HandleOtherWFFuncmode(funcmode);
1194 return;
1195 END IF;
1196
1197
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 wf_core.context('XDPCORE_FA', 'RESET_SYSTEM_HOLD', itemtype, itemkey, to_char(actid), funcmode);
1201 raise;
1202 END RESET_SYSTEM_HOLD;
1203
1204 Procedure OVERRIDE_FE (itemtype in varchar2,
1205 itemkey in varchar2,
1206 actid in number,
1207 funcmode in varchar2,
1208 resultout OUT NOCOPY varchar2) IS
1209 l_nid NUMBER;
1210 l_faInstanceID NUMBER;
1211 l_newFeName VARCHAR2(100);
1212 BEGIN
1213 -- RUN mode - normal process execution
1214 --
1215 IF (funcmode = 'RESPOND') THEN
1216 l_nid := WF_ENGINE.context_nid;
1217 l_newFeName := wf_notification.GetAttrText( l_nid, 'FE_NAME');
1218
1219 -- User havent entered any overriding FE Name..
1220 IF l_newFeName IS NOT NULL THEN
1221 l_faInstanceID := wf_engine.getItemAttrNumber( itemtype, itemkey, 'FA_INSTANCE_ID' );
1222 OverrideFE( l_faInstanceID, l_newFeName, resultout );
1223 END IF;
1224
1225 ELSIF (funcmode = 'RUN') THEN
1226
1227 -- skilaru 05/30/2002
1228 -- This function is a post-notification function so it
1229 -- will be called in RUN mode after being called in RESPOND mode
1230 -- do nothing..
1231
1232 IF XDPCORE.is_business_error = 'Y' THEN
1233 resultout := 'COMPLETE:FE_NOT_FOUND';
1234 END IF;
1235
1236 ELSE
1237 resultout := HandleOtherWFFuncmode(funcmode);
1238 return;
1239 END IF;
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 wf_core.context('XDPCORE_FA', 'OVERRIDE_FE', itemtype, itemkey, to_char(actid), funcmode);
1243 raise;
1244 END OVERRIDE_FE;
1245
1246 Procedure AUTO_RETRY_ENABLED (itemtype in varchar2,
1247 itemkey in varchar2,
1248 actid in number,
1249 funcmode in varchar2,
1250 resultout OUT NOCOPY varchar2 ) IS
1251 l_autoRetryEnabled VARCHAR2(1);
1252 BEGIN
1253 IF( funcmode = 'RUN' ) THEN
1254
1255 l_autoRetryEnabled := XDP_MACROS.AUTO_RETRY_ENABLED;
1256
1257 if ( l_autoRetryEnabled = 'Y' ) THEN
1258 resultout := 'COMPLETE:Y';
1259 ELSE
1260 resultout := 'COMPLETE:N';
1261 END IF;
1262 ELSE
1263 resultout := HandleOtherWFFuncmode(funcmode);
1264 return;
1265 END IF;
1266
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 wf_core.context('XDPCORE_FA', 'AUTO_RETRY_ENABLED', itemtype, itemkey, to_char(actid), funcmode);
1270 raise;
1271 END AUTO_RETRY_ENABLED;
1272
1273 /****
1274 All the Private Functions
1275 ****/
1276
1277 Function HandleOtherWFFuncmode( funcmode in varchar2) return varchar2
1278 is
1279 resultout varchar2(30);
1280 x_Progress VARCHAR2(2000);
1281
1282 begin
1283
1284 IF (funcmode = 'CANCEL') THEN
1285 resultout := 'COMPLETE';
1286 END IF;
1287
1288 IF (funcmode = 'RESPOND') THEN
1289 resultout := 'COMPLETE';
1290 END IF;
1291
1292 IF (funcmode = 'FORWARD') THEN
1293 resultout := 'COMPLETE';
1294 END IF;
1295
1296 IF (funcmode = 'TRANSFER') THEN
1297 resultout := 'COMPLETE';
1298 END IF;
1299
1300 IF (funcmode = 'TIMEOUT') THEN
1301 resultout := 'COMPLETE';
1302 END IF;
1303
1304 IF (funcmode = 'others') THEN
1305 resultout := 'COMPLETE';
1306 END IF;
1307
1308
1309 return resultout;
1310
1311 end;
1312
1313 Function InitializeFAList (itemtype in varchar2,
1314 itemkey in varchar2) return varchar2
1315 is
1316
1317 l_WIInstanceID number;
1318 l_OrderID number;
1319 l_LineItemID number;
1320 l_FAListProc varchar2(80);
1321 l_wi_disp_name varchar2(80);
1322 l_message_params varchar2(2000);
1323 l_FAInstanceID number;
1324 l_ErrCode number;
1325 l_ProcFound varchar2(1) := 'N';
1326 l_ProvSeq number;
1327 l_FAsfound number := 0;
1328
1329 l_ErrStr varchar2(4000);
1330
1331 e_ProcExecException exception;
1332 e_UnHandledException exception;
1333 e_CallFAMapProcException exception;
1334 e_AddFAToWIException exception;
1335 e_NoFAsFound exception;
1336 e_business_error exception;
1337 x_Progress VARCHAR2(2000);
1338 l_result varchar2(15) := 'SUCCESS';
1339
1340 begin
1341
1342 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => InitializeFAList.itemtype,
1343 itemkey => InitializeFAList.itemkey,
1344 aname => 'WORKITEM_INSTANCE_ID');
1345
1346 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => InitializeFAList.itemtype,
1347 itemkey => InitializeFAList.itemkey,
1348 aname => 'ORDER_ID');
1349
1350 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => InitializeFAList.itemtype,
1351 itemkey => InitializeFAList.itemkey,
1352 aname => 'LINE_ITEM_ID');
1353
1354 GetWorkitemFAMappingProc(l_WIInstanceID, l_ProcFound, l_FAListProc);
1355
1356 if l_ProcFound = 'N' then
1357 /* No Procedure has been declared. Hence normal process of executing workitems */
1358
1359 /* Populate all the FA's in the FA_RUNTIME_LIST Table */
1360
1361 begin
1362 PopulateFAs(l_WIInstanceID);
1363 l_FAsfound := 1;
1364 exception
1365 when others then
1366 x_Progress := 'XDPCORE_FA.InitializeFAList. Exception when Adding FA to Workitem. Order: ' || l_OrderID || ' WorkitemInstanceID: ' || l_WIInstanceID || 'Error: ' || SUBSTR(SQLERRM,1,200);
1367 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1368 l_result := 'FAILURE';
1369 end;
1370
1371 else
1372 /*
1373 * Found a FA runtime mapping procedure.
1374 * Need to execute it dynamically to populate all the FA's
1375 */
1376
1377 XDP_UTILITIES.CallFAMapProc (P_PROCEDURE_NAME => l_FAListProc,
1378 P_ORDER_ID => l_OrderID,
1379 P_LINE_ITEM_ID => l_LineItemID,
1380 P_WI_INSTANCE_ID => l_WIInstanceID,
1381 P_RETURN_CODE => l_ErrCode,
1382 P_ERROR_DESCRIPTION => l_ErrStr);
1383
1384 if l_ErrCode <> 0 then
1385 l_wi_disp_name := XDPCORE_WI.get_display_name( l_WIInstanceID );
1386
1387 -- build the token string for xdp_errors_log..
1388 l_message_params := 'WI='||l_wi_disp_name||'#XDP#';
1389
1390 -- set the business error...
1391 XDPCORE.error_context( 'WI', l_WIInstanceID, 'XDP_UNABLE_TO_RESOLVE_FAS', l_message_params );
1392
1393
1394 x_Progress := l_ErrStr || 'XDPCORE_FA.InitializeFAList. Error when Executing Procedure: ' || l_FAListProc || ' to dynamically get the FA List for Order: ' || l_OrderID;
1395 x_Progress := x_Progress || ' WorkitemInstanceID: ' || l_WIInstanceID || ' Error: ' || SUBSTR(l_ErrStr, 1, 1500);
1396 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1397 l_result := 'FAILURE';
1398 end if;
1399 l_FAsfound := 1;
1400
1401 end if;
1402
1403 -- If FAs not found then
1404 if l_FAsfound = 0 then
1405 -- if the error is not set then
1406 -- HINT: Fa parameter evaluation might fail..
1407 IF XDPCORE.is_business_error <> 'Y' THEN
1408 l_wi_disp_name := XDPCORE_WI.get_display_name( l_WIInstanceID );
1409
1410 -- build the token string for xdp_errors_log..
1411 l_message_params := 'WI='||l_wi_disp_name||'#XDP#';
1412
1413 -- set the business error...
1414 XDPCORE.error_context( 'WI', l_WIInstanceID, 'XDP_UNABLE_TO_RESOLVE_FAS', l_message_params );
1415 END IF;
1416 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1417 l_result := 'FAILURE';
1418 end if;
1419 return l_result;
1420 exception
1421 when e_ProcExecException then
1422 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1423 wf_core.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1424 raise;
1425
1426 when e_UnHandledException then
1427 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1428 wf_core.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1429 raise;
1430
1431 when e_CallFAMapProcException then
1432 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1433 wf_core.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1434 raise;
1435
1436 when e_AddFAToWIException then
1437 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1438 wf_core.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1439 raise;
1440
1441 when others then
1442 x_Progress := 'XDPCORE_FA.InitializeFAList. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1443 xdpcore.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1444 wf_core.context('XDPCORE_FA', 'InitializeFAList', itemtype, itemkey, null, x_Progress);
1445 raise;
1446 end InitializeFAList;
1447
1448
1449 Function AreAllFAsDone (itemtype in varchar2,
1450 itemkey in varchar2) return varchar2
1451
1452 is
1453 cursor c_FaList (wi_id number, prov_seq number) is
1454 select FA_INSTANCE_ID, PROVISIONING_SEQUENCE
1455 from XDP_FA_RUNTIME_LIST
1456 where WORKITEM_INSTANCE_ID = wi_id
1457 and STATUS_CODE = 'STANDBY'
1458 and PROVISIONING_SEQUENCE = (
1459 select MIN(PROVISIONING_SEQUENCE)
1460 from XDP_FA_RUNTIME_LIST
1461 where WORKITEM_INSTANCE_ID = wi_id
1462 and STATUS_CODE = 'STANDBY'
1463 and PROVISIONING_SEQUENCE > prov_seq);
1464
1465 l_WIInstanceID number;
1466 l_PrevFASeq number;
1467
1468 e_NoFAsFoundException exception;
1469 x_Progress VARCHAR2(2000);
1470
1471 begin
1472
1473 l_WIInstanceID := WF_engine.GetItemAttrNumber(itemtype => AreAllFAsDone.itemtype,
1474 itemkey => AreAllFAsDone.itemkey,
1475 aname => 'WORKITEM_INSTANCE_ID');
1476
1477
1478 l_PrevFASeq := wf_engine.GetItemAttrNumber(itemtype => AreAllFAsDone.itemtype,
1479 itemkey => AreAllFAsDone.itemkey,
1480 aname => 'CURRENT_FA_SEQUENCE');
1481
1482
1483 if c_FaList%ISOPEN then
1484 Close c_FaList;
1485 end if;
1486
1487 open c_FaList(l_WIInstanceID, l_PrevFASeq);
1488
1489 Fetch c_FaList into l_WIInstanceID, l_PrevFASeq;
1490
1491 if c_FaList%NOTFOUND then
1492 /* No more FA's to be done */
1493 return ('Y');
1494 else
1495 /* There are more FA's to be done */
1496 return ('N');
1497 end if;
1498
1499 exception
1500 when e_NoFAsFoundException then
1501 if c_FaList%ISOPEN then
1502 Close c_FaList;
1503 end if;
1504
1505 wf_core.context('XDPCORE_FA', 'AreAllFAsDone', itemtype, itemkey, null, x_Progress);
1506 raise;
1507
1508 when others then
1509 if c_FaList%ISOPEN then
1510 Close c_FaList;
1511 end if;
1512
1513 x_Progress := 'XDPCORE_FA.AreAllFAsDone. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1514 wf_core.context('XDPCORE_FA', 'AreAllFAsDone', itemtype, itemkey, null, x_Progress);
1515 raise;
1516 end AreAllFAsDone;
1517
1518
1519 Procedure LaunchFAProvisioningProcess (itemtype in varchar2,
1520 itemkey in varchar2)
1521 is
1522
1523 l_WIInstanceID number;
1524 l_FAID number;
1525 l_FAInstanceID number;
1526 l_OrderID number;
1527 l_priority number;
1528 l_ErrCode number;
1529 l_ErrDescription varchar2(800);
1530
1531 l_FAItemType varchar2(10);
1532 l_FAItemKey varchar2(240);
1533 l_tempKey varchar2(240);
1534
1535 e_CreatFAWFException exception;
1536 e_AddFAtoQException exception;
1537
1538 x_Progress VARCHAR2(2000);
1539
1540 begin
1541
1542 l_OrderID := WF_engine.GetItemAttrNumber(itemtype => LaunchFAProvisioningProcess.itemtype,
1543 itemkey => LaunchFAProvisioningProcess.itemkey,
1544 aname => 'ORDER_ID');
1545
1546 l_WIInstanceID := WF_engine.GetItemAttrNumber(itemtype => LaunchFAProvisioningProcess.itemtype,
1547 itemkey => LaunchFAProvisioningProcess.itemkey,
1548 aname => 'WORKITEM_INSTANCE_ID');
1549
1550 l_FAInstanceID := WF_engine.GetItemAttrNumber(itemtype => LaunchFAProvisioningProcess.itemtype,
1551 itemkey => LaunchFAProvisioningProcess.itemkey,
1552 aname => 'FA_INSTANCE_ID');
1553
1554 l_Priority := WF_engine.GetItemAttrNumber(itemtype => LaunchFAProvisioningProcess.itemtype,
1555 itemkey => LaunchFAProvisioningProcess.itemkey,
1556 aname => 'FA_PRIORITY');
1557
1558
1559
1560
1561 CreateFAProcess(itemtype, itemkey, l_FAInstanceID, l_WIInstanceID,
1562 l_OrderID, 'INTERNAL','WAITFORFLOW-IND_FA',
1563 l_FAItemType, l_FAItemKey,
1564 l_ErrCode, l_ErrDescription);
1565
1566 if l_ErrCode <> 0 then
1567 x_Progress := 'XDPCORE_FA.LaunchFAProvisioningProcess. Error when creating FA Process for Order: '
1568 || l_OrderID || ' Workitem InstabceID: ' || l_WIInstanceID || ' FAInstanceID: ' || l_FAInstanceID
1569 || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1570 RAISE e_CreatFAWFException;
1571 end if;
1572
1573
1574 /* Update the XDP_FA_RUNTIME_LIST table with the User defined Workitem Item Type and Item Key */
1575
1576 update XDP_FA_RUNTIME_LIST
1577 set WF_ITEM_TYPE = l_FAItemType,
1578 WF_ITEM_KEY = l_FAItemKey,
1579 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1580 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1581 where FA_INSTANCE_ID = l_FAInstanceID;
1582
1583 /* Enqueue the FA into the FA Queue */
1584
1585 XDP_AQ_UTILITIES.Add_FA_ToQ ( P_ORDER_ID => l_OrderID,
1586 P_WI_INSTANCE_ID => l_WIInstanceID,
1587 P_FA_INSTANCE_ID => l_FAInstanceID,
1588 P_WF_ITEM_TYPE => l_FAItemType,
1589 P_WF_ITEM_KEY => l_FAItemKey,
1590 P_PRIORITY => l_Priority,
1591 P_RETURN_CODE => l_ErrCode,
1592 P_ERROR_DESCRIPTION => l_ErrDescription);
1593
1594 if l_ErrCode <> 0 then
1595 x_Progress := 'XDPCORE_FA.LaunchFAProvisioningProcess. Error when Ading FA to Queue for for Order: '
1596 || l_OrderID || ' Workitem InstabceID: ' || l_WIInstanceID || ' FAInstanceID: ' || l_FAInstanceID
1597 || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
1598
1599 RAISE e_AddFAtoQException;
1600 end if;
1601
1602 exception
1603 when e_CreatFAWFException then
1604 wf_core.context('XDPCORE_FA', 'LaunchFAProvisioningProcess', itemtype, itemkey, null, x_Progress);
1605 raise;
1606
1607 when e_AddFAtoQException then
1608 wf_core.context('XDPCORE_FA', 'LaunchFAProvisioningProcess', itemtype, itemkey, null, x_Progress);
1609 raise;
1610
1611 when others then
1612 x_Progress := 'XDPCORE_FA.LaunchFAProvisioningProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1613 wf_core.context('XDPCORE_FA', 'LaunchFAProvisioningProcess', itemtype, itemkey, null, x_Progress);
1614 raise;
1615 end LaunchFAProvisioningProcess;
1616
1617
1618
1619
1620 Procedure InitializeFA (itemtype in varchar2,
1621 itemkey in varchar2) IS
1622
1623 l_FAInstanceID number;
1624 l_ResubmissionJobID number;
1625
1626 e_InvalidConfigException exception;
1627 x_Progress VARCHAR2(2000);
1628
1629 begin
1630
1631 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => InitializeFA.itemtype,
1632 itemkey => InitializeFA.itemkey,
1633 aname => 'FA_INSTANCE_ID');
1634
1635 l_ResubmissionJobID := GetResubmissionJobID(itemtype => InitializeFA.itemtype,
1636 itemkey => InitializeFA.itemkey);
1637
1638
1639 /* if the resubmission job is not zero the parent of the fa process is actually the
1640 ** ORU process. THe Execute FA activity does not set FA Master currently. Hence this
1641 ** Logic.
1642 */
1643
1644 SavePoint InitializeAbortFA;
1645
1646 if l_FAInstanceID is null then
1647 RAISE e_InvalidConfigException;
1648 else
1649
1650 /* Update the XDP_FA_RUNTIME table with the status of the FA, the state of processing,
1651 * FA WF item type and item key
1652 */
1653
1654 UPDATE_FA_STATUS( p_fa_instance_id => l_FAInstanceID ,
1655 p_status_code => 'IN PROGRESS',
1656 p_itemtype => InitializeFA.itemtype,
1657 p_itemkey => InitializeFA.itemkey );
1658
1659 end if;
1660
1661 /* Check if the FA is aborted. If so abort the process immediately */
1662 if IsFAAborted(l_FAInstanceID) = TRUE then
1663 rollback to InitializeAbortFA;
1664 wf_engine.abortprocess(itemtype => InitializeFA.itemtype,
1665 itemkey => InitializeFA.itemkey);
1666
1667 return;
1668 end if;
1669 exception
1670 when others then
1671 x_Progress := 'XDPCORE_FA.InitializeFA. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1672 wf_core.context('XDPCORE_FA', 'InitializeFA', itemtype, itemkey, null, x_Progress);
1673 raise;
1674 end InitializeFA;
1675
1676
1677
1678 Function GetFe (itemtype in varchar2,
1679 itemkey in varchar2) return varchar2
1680
1681 is
1682
1683 l_ConfigError varchar2(1);
1684 l_FeName varchar2(40);
1685 l_LocateFEProc varchar2(40);
1686 l_FeTypeID number;
1687 l_FeType varchar2(40);
1688 l_FeID number;
1689 l_FeSWGeneric varchar2(40);
1690 l_FAInstanceID number;
1691 l_WIInstanceID number;
1692 l_OrderID number;
1693 l_LineItemID number;
1694 l_AdapterType varchar2(40);
1695 l_FAID number;
1696 l_FaProvProc varchar2(40);
1697 l_routing_proc_disp_name varchar2(100);
1698 l_ErrCode number;
1699 l_ErrStr varchar2(4000);
1700 l_fa_disp_name varchar2(200);
1701 l_result varchar2(15) := 'SUCCESS';
1702
1703 l_message_params varchar2(2000);
1704
1705 ErrCode number;
1706 ErrStr varchar2(4000);
1707
1708 e_InvalidConfigException exception;
1709 e_InvalidFEConfigException exception;
1710 e_CallFeRoutingProcException exception;
1711 e_ProcExecException exception;
1712 e_GetFeConfigException exception;
1713 e_AddAttributeException exception;
1714
1715 x_Progress VARCHAR2(2000);
1716
1717 cursor c_UpdateFEID (p_FAInstanceID number) is
1718 select 'Update FE ID'
1719 from XDP_FA_RUNTIME_LIST
1720 where FA_INSTANCE_ID = p_FAInstanceID
1721 for update of FE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN;
1722
1723 begin
1724
1725 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => GetFE.itemtype,
1726 itemkey => GetFE.itemkey,
1727 aname => 'FA_INSTANCE_ID');
1728
1729 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => GetFE.itemtype,
1730 itemkey => GetFE.itemkey,
1731 aname => 'WORKITEM_INSTANCE_ID');
1732
1733 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => GetFE.itemtype,
1734 itemkey => GetFE.itemkey,
1735 aname => 'ORDER_ID');
1736
1737 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => GetFE.itemtype,
1738 itemkey => GetFE.itemkey,
1739 aname => 'LINE_ITEM_ID');
1740
1741 SavePoint GetFEAbortFA;
1742
1743 IsFEPreDefined(l_FAInstanceID, l_ConfigError, l_FeID);
1744
1745 if l_ConfigError = 'Y' then
1746 l_fa_disp_name := get_display_name( l_FAInstanceID );
1747 -- build the token string for xdp_errors_log..
1748 l_message_params := 'FA='||l_fa_disp_name||'#XDP#';
1749
1750 -- set the business error...
1751 XDPCORE.error_context( 'FA', l_FAInstanceID, 'XDP_FE_NOT_FOUND', l_message_params );
1752
1753 x_Progress := 'XDPCORE_FA.GetFE. No entries for FA InstanceID: ' || l_FAInstanceID || ' found. Check Order: ' || l_OrderID || ' Workitem InstanceID : ' || l_WIInstanceID;
1754
1755 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1756 return 'FAILURE';
1757 end if;
1758
1759 if l_FeID is not null then
1760 /* Already got the FE_ID need the FE Name and FE information
1761 /* Got the FEID now get the FE Name. Fetype, FE SW/Generic etc... */
1762
1763 begin
1764
1765 XDP_ENGINE.GET_FE_CONFIGINFO (l_FeID,
1766 l_FeName,
1767 l_FetypeID,
1768 l_FeType,
1769 l_FeSWGeneric,
1770 l_AdapterType);
1771 exception
1772 when others then
1773 x_Progress := 'XDPCORE_FA.GeFe. Exception when Getting FE Config Info for FEID: ' || l_FeID || ' FA InstanceID: ' || l_FAInstanceID || ' Order: ' || l_OrderID || ' Workitem InstanceID : ' || l_WIInstanceID || ' Error: ' || SUBSTR(SQLERRM,1,500);
1774
1775 RAISE e_GetFeConfigException;
1776 end;
1777
1778 else
1779
1780 /* FE NAME IS NOT ALREADY DEFINED */
1781 /* Get the LOCATE FE Procedure and execute to determine the FE Name */
1782
1783 l_LocateFEProc := GetLocateFEProc(l_FAInstanceID);
1784
1785 if l_LocateFEProc IS NULL then
1786 x_Progress := 'XDPCORE_FA.GeFe. Could not locate te FE Routing Procedure for FA InstanceID: ' || l_FAInstanceID || ' Order: ' || l_OrderID || ' Workitem InstanceID : ' || l_WIInstanceID;
1787
1788 RAISE e_InvalidConfigException;
1789 end if;
1790
1791 /*
1792 dynamically execute the Locate FE procedure and get the FE into l_FeName
1793 */
1794
1795 XDP_UTILITIES.CallFERoutingProc ( P_PROCEDURE_NAME => l_LocateFEProc,
1796 P_ORDER_ID => l_OrderID,
1797 P_LINE_ITEM_ID => l_LineItemID,
1798 P_WI_INSTANCE_ID => l_WIInstanceID,
1799 P_FA_INSTANCE_ID => l_FAInstanceID,
1800 P_FE_NAME => l_FeName,
1801 P_RETURN_CODE => l_ErrCode,
1802 P_ERROR_DESCRIPTION => l_ErrStr);
1803
1804 if l_ErrCode <> 0 then
1805 l_fa_disp_name := get_display_name( l_FAInstanceID );
1806
1807 -- build the token string for xdp_errors_log..
1808 l_message_params := 'FA='||l_fa_disp_name||'#XDP#';
1809
1810 -- set the business error...
1811 XDPCORE.error_context( 'FA', l_WIInstanceID, 'XDP_EXCEP_IN_FE_RT_PROC', l_message_params );
1812
1813 xdpcore.context('XDPCORE_FA', 'InitializeFAList', 'FA', l_FAInstanceID, null, l_ErrStr);
1814 return 'FAILURE';
1815 end if;
1816
1817 /* Got the FE Name. Now get the FetypeID, FEID, SW/Generic */
1818
1819 begin
1820 XDP_ENGINE.GET_FE_CONFIGINFO( l_FeName,
1821 l_FeID,
1822 l_FetypeID,
1823 l_FeType,
1824 l_FeSWGeneric,
1825 l_AdapterType);
1826 exception
1827 when others then
1828
1829 l_fa_disp_name := get_display_name( l_FAInstanceID );
1830
1831 SELECT display_name into l_routing_proc_disp_name FROM XDP_PROC_BODY_VL
1832 WHERE proc_name = l_LocateFEProc;
1833
1834 -- build the token string for xdp_errors_log..
1835 l_message_params := 'FA='||l_fa_disp_name|| '#XDP#FE=' || l_FeName ||'#XDP#ROUT_PROC='||l_routing_proc_disp_name||'#XDP#';
1836
1837 -- set the business error...
1838 XDPCORE.error_context( 'FE', l_FeID, 'XDP_ROUT_PROC_RETND_INVALID_FE', l_message_params );
1839 x_Progress := 'XDPCORE_FA.GeFe. Exception when Getting FE Config Info for FE: ' || l_FeName || ' FA InstanceID: ' || l_FAInstanceID || ' Order: ' || l_OrderID || ' Workitem InstanceID : ' || l_WIInstanceID || ' Error: ' || SUBSTR(SQLERRM,1,500);
1840 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1841 return 'FAILURE';
1842 end;
1843
1844 FOR v_UpdateFEID in c_UpdateFEID(l_FAInstanceID) LOOP
1845 update XDP_FA_RUNTIME_LIST set FE_ID = l_FeID,
1846 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1847 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1848 where current of c_UpdateFEID;
1849 END LOOP;
1850
1851 end if;
1852
1853 /* Got the FE Name now set the wf item attribute */
1854 XDPCORE.CheckNAddItemAttrText (itemtype => GetFE.itemtype,
1855 itemkey => GetFE.itemkey,
1856 AttrName => 'FE_NAME',
1857 AttrValue => l_FeName,
1858 ErrCode => ErrCode,
1859 ErrStr => ErrStr);
1860
1861 if ErrCode <> 0 then
1862 x_progress := 'In XDPCORE_FA.GetFE. Error when adding FE_NAME attribute. ';
1863 raise e_AddAttributeException;
1864 end if;
1865
1866 XDPCORE.CheckNAddItemAttrNumber (itemtype => GetFE.itemtype,
1867 itemkey => GetFE.itemkey,
1868 AttrName => 'FE_ID',
1869 AttrValue => l_FeID,
1870 ErrCode => ErrCode,
1871 ErrStr => ErrStr);
1872
1873 if ErrCode <> 0 then
1874 x_progress := 'In XDPCORE_FA.GetFE. Error when adding FE_ID attribute. ';
1875 raise e_AddAttributeException;
1876 end if;
1877
1878 l_FaProvProc := GetFPProc (l_FAInstanceID, l_FeTypeID,
1879 l_FeSwGeneric, l_AdapterType) ;
1880 if l_FaProvProc is NULL then
1881 -- get FA display name..
1882 l_fa_disp_name := get_display_name( l_FAInstanceID );
1883 -- build the token string for xdp_errors_log..
1884 l_message_params := 'FA='||l_fa_disp_name||'#XDP#';
1885
1886 -- set the business error...
1887 XDPCORE.error_context( 'FA', l_FAInstanceID, 'XDP_FE_FP_NOT_FOUND', l_message_params );
1888
1889 x_Progress := 'XDPCORE_FA.GetFe. Could not find and FP for FA InstanceID: ' || l_FAInstanceID || ' Order: ' || l_OrderID || ' Workitem InstanceID : ' || l_WIInstanceID;
1890
1891 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1892 return 'FAILURE';
1893 end if;
1894
1895 -- skilaru 04/05/02
1896 -- fa runtime list with the FP name ( fix for the bug # 1945013 )
1897 update XDP_FA_RUNTIME_LIST set PROC_NAME = l_FaProvProc,
1898 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1899 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1900 where fa_instance_id = l_FAInstanceID;
1901
1902 XDPCORE.CheckNAddItemAttrText (itemtype => GetFE.itemtype,
1903 itemkey => GetFE.itemkey,
1904 AttrName => 'FP_NAME',
1905 AttrValue => l_FaProvProc,
1906 ErrCode => ErrCode,
1907 ErrStr => ErrStr);
1908
1909 if ErrCode <> 0 then
1910 x_progress := 'In XDPCORE_FA.GetFE. Error when adding FP_NAME attribute. ';
1911 raise e_AddAttributeException;
1912 end if;
1913
1914
1915 /* Check if the FA is aborted. If so abort the process immediately */
1916 if IsFAAborted(l_FAInstanceID) = TRUE then
1917 rollback to GetFEAbortFA;
1918 wf_engine.abortprocess(itemtype => GetFE.itemtype,
1919 itemkey => GetFE.itemkey);
1920 -- are we still doing faaborted???? skilaru 03/06/02
1921 return l_result;
1922 end if;
1923
1924 return l_result;
1925
1926 exception
1927 when e_InvalidConfigException then
1928 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1929 wf_core.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1930 raise;
1931
1932 when e_InvalidFEConfigException then
1933 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1934 wf_core.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1935
1936 when e_CallFeRoutingProcException then
1937 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1938 wf_core.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1939 raise;
1940
1941 when e_ProcExecException then
1942 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1943 wf_core.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1944 raise;
1945
1946 when e_GetFeConfigException then
1947 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1948 wf_core.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1949 raise;
1950
1951 when others then
1952 x_Progress := 'XDPCORE_FA.GetFe. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
1953 xdpcore.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1954 wf_core.context('XDPCORE_FA', 'GetFe', itemtype, itemkey, null, x_Progress);
1955 raise;
1956 end GetFe;
1957
1958
1959
1960 Function IsChannelAvailable (itemtype in varchar2,
1961 itemkey in varchar2,
1962 CODFlag in varchar2,
1963 AdapterStatus in varchar2) return varchar2
1964
1965 is
1966
1967 e_AddAttributeException exception;
1968
1969 l_dummy number;
1970
1971 l_FAInstanceID number;
1972 l_GotChannelFlag varchar2(1) := 'N';
1973 l_ChannelName varchar2(40);
1974 l_ChannelUsageCode varchar2(40) := 'NORMAL';
1975 l_FeName varchar2(40);
1976 l_FeID number;
1977 l_ResubmissionJobID number;
1978 l_Counter number := 0;
1979
1980 l_FPFuncMode varchar2(20);
1981 l_AdapterConnectMode varchar2(40);
1982
1983 x_Progress VARCHAR2(2000);
1984
1985 ErrCode number;
1986 ErrStr varchar2(1996);
1987 l_Status varchar2(1) := 'N';
1988
1989 begin
1990
1991 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => IsChannelAvailable.itemtype,
1992 itemkey => IsChannelAvailable.itemkey,
1993 aname => 'FA_INSTANCE_ID');
1994
1995 l_FPFuncMode := wf_engine.GetItemAttrText(itemtype => IsChannelAvailable.itemtype,
1996 itemkey => IsChannelAvailable.itemkey,
1997 aname => 'FP_FUNC_MODE');
1998
1999 SavePoint GetChannelAbortFA;
2000
2001 if l_FPFuncMode <> 'RUN' then
2002 /* Reset the Func Mode */
2003 wf_engine.SetItemAttrText(itemtype => IsChannelAvailable.itemtype,
2004 itemkey => IsChannelAvailable.itemkey,
2005 aname => 'FP_FUNC_MODE',
2006 avalue => 'RUN');
2007 end if;
2008
2009 l_FeID := wf_engine.GetItemAttrNumber(itemtype => IsChannelAvailable.itemtype,
2010 itemkey => IsChannelAvailable.itemkey,
2011 aname => 'FE_ID');
2012
2013 XDPCORE.CheckNAddItemAttrText (itemtype => IsChannelAvailable.itemtype,
2014 itemkey => IsChannelAvailable.itemkey,
2015 AttrName => 'CHANNEL_NAME',
2016 AttrValue => NULL,
2017 ErrCode => ErrCode,
2018 ErrStr => ErrStr);
2019
2020 if ErrCode <> 0 then
2021 x_progress := 'In XDPCORE_FA.IsChannelAvailable. Error when adding CHANNEL_NAME attribute. ';
2022 raise e_AddAttributeException;
2023 end if;
2024
2025 /* If the Resubmission JobID is non zero the fa must look for "RESUBMISSION" type
2026 ** of adapters.
2027 */
2028 l_ResubmissionJobID := GetResubmissionJobID(itemtype => IsChannelAvailable.itemtype,
2029 itemkey => IsChannelAvailable.itemkey);
2030
2031
2032 if l_ResubmissionJobID <> 0 then
2033 l_ChannelUsageCode := 'RESUBMISSION';
2034 end if;
2035
2036 SearchAndLockChannel( l_FEID ,
2037 l_ChannelUsageCode ,
2038 CODFlag,
2039 AdapterStatus ,
2040 l_GotChannelFlag ,
2041 l_ChannelName );
2042
2043
2044 if l_GotChannelFlag = 'Y' then
2045
2046
2047 if CODFlag = 'N' then
2048
2049 --skilaru 05/17/2002
2050 --Lock the channel only if it is pipe based adapter...
2051
2052 if XDP_ADAPTER_CORE_DB.checkLockRequired( l_ChannelName ) then
2053 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2054 p_ChannelName => l_ChannelName,
2055 p_Status => XDP_ADAPTER.pv_statusInUse,
2056 p_WFItemType => IsChannelAvailable.itemtype,
2057 p_WFItemKey => IsChannelAvailable.itemkey);
2058 end if;
2059 end if;
2060
2061 XDPCORE.CheckNAddItemAttrText (itemtype => IsChannelAvailable.itemtype,
2062 itemkey => IsChannelAvailable.itemkey,
2063 AttrName => 'CHANNEL_NAME',
2064 AttrValue => l_ChannelName,
2065 ErrCode => ErrCode,
2066 ErrStr => ErrStr);
2067
2068 if ErrCode <> 0 then
2069 x_progress := 'In XDPCORE_FA.IsChannelAvailable. Error when adding CHANNEL_NAME attribute. ';
2070 raise e_AddAttributeException;
2071 end if;
2072
2073 /* Check if the FA is aborted. If so abort the process immediately */
2074 if IsFAAborted(l_FAInstanceID) = TRUE then
2075 rollback to GetChannelAbortFA;
2076 wf_engine.abortprocess(itemtype => IsChannelAvailable.itemtype,
2077 itemkey => IsChannelAvailable.itemkey);
2078
2079 return wf_engine.eng_force;
2080 end if;
2081
2082 return 'Y';
2083 else
2084 /* Check if the FA is aborted. If so abort the process immediately */
2085 if IsFAAborted(l_FAInstanceID) = TRUE then
2086 rollback to GetChannelAbortFA;
2087 wf_engine.abortprocess(itemtype => IsChannelAvailable.itemtype,
2088 itemkey => IsChannelAvailable.itemkey);
2089
2090 return wf_engine.eng_force;
2091 end if;
2092 return 'N';
2093 end if;
2094
2095 exception
2096 when others then
2097 x_Progress := 'XDPCORE_FA.IsChannelAvailable. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2098 wf_core.context('XDPCORE_FA', 'IsChannelAvailable', itemtype, itemkey, null, x_Progress);
2099 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => l_ChannelName);
2100 raise;
2101 end IsChannelAvailable;
2102
2103
2104 Function VerifyChannel (itemtype in varchar2,
2105 itemkey in varchar2) return varchar2
2106 is
2107 l_ChannelName varchar2(40);
2108 l_AdapterStatus varchar2(40);
2109 l_FAInstanceID number;
2110 l_ResubmissionJOBID number;
2111 l_ErrCode number;
2112 l_ErrStr varchar2(4000);
2113
2114 x_Progress VARCHAR2(2000);
2115
2116 l_Result varchar2(30) := 'CONTINUE';
2117 l_Status varchar2(1);
2118 l_channellock boolean := FALSE ;
2119 l_isLockRequired boolean;
2120 l_channelstatus varchar2(1) ;
2121
2122 cursor c_CheckChannel(ChannelName varchar2) is
2123 select adapter_status
2124 from XDP_ADAPTER_REG
2125 where channel_name = ChannelName;
2126
2127 resource_busy exception;
2128 pragma exception_init(resource_busy, -00054);
2129
2130 e_HandOverChannelException exception;
2131 begin
2132
2133 l_ChannelName := wf_engine.GetItemAttrText(itemtype => VerifyChannel.itemtype,
2134 itemkey => VerifyChannel.itemkey,
2135 aname => 'CHANNEL_NAME');
2136
2137 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => VerifyChannel.itemtype,
2138 itemkey => VerifyChannel.itemkey,
2139 aname => 'FA_INSTANCE_ID');
2140
2141
2142 /* If the Channel Name does not exist then the dequeuer has started processing
2143 ** and the current Channel is not valid any more. The Output is to Reprocess.
2144 ** Also set the Re-Process flag to 'Y' so that When the order is being enqueued, the enqueue time
2145 ** is the time the order was enqueued in the Channel Queue. This value is set by the channel
2146 ** Dequer.
2147 **/
2148
2149 -- 11.5.6 On-wards.
2150 -- Instead of row level lock. Check for a DB lock
2151 open c_CheckChannel(l_ChannelName);
2152
2153 if C_CheckChannel%NOTFOUND then
2154 close c_CheckChannel;
2155 return 'RE_PROCESS';
2156 end if;
2157 -- skilaru 05/16/2002
2158 -- ER# 2013681 Lock the channel only if it is of application mode PIPE..
2159
2160 l_isLockRequired := XDP_ADAPTER_CORE_DB.checkLockRequired( l_ChannelName );
2161 IF( l_isLockRequired ) THEN
2162
2163 l_Status := XDP_ADAPTER_CORE_DB.ObtainAdapterLock_FA
2164 (p_ChannelName => l_ChannelName);
2165
2166 END IF;
2167
2168 if l_Status = 'N' AND l_isLockRequired then
2169 l_Result := 'RE_PROCESS';
2170 else
2171 l_channellock := TRUE ;
2172 end if;
2173
2174 if l_Result <> 'RE_PROCESS' then
2175 Fetch c_CheckChannel into l_AdapterStatus;
2176 if l_AdapterStatus in ('BUSY', 'STARTING UP', 'DISCONNECTED', 'SUSPENDED', 'SESSION_LOST') then
2177 /* Raja: Added on 8/31/1999. Verify Channel activity should also update the channel status */
2178 /* Update the Adapter Status to be BUSY */
2179
2180 -- Also why are we checking for above statuses?
2181
2182 if XDP_ADAPTER_CORE_DB.Verify_Adapter (l_ChannelName) then
2183 l_Result := 'CONTINUE';
2184 else
2185 -- Release the Lock
2186 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (l_ChannelName);
2187 l_Result := 'RE_PROCESS';
2188 END IF;
2189
2190 -- skilaru 05/16/2002 ER# 2013681
2191 -- If we dont lock the channel then the status of the adapter is
2192 -- always Running. Dont update to In Use
2193
2194 IF( l_isLockRequired ) THEN
2195
2196 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2197 p_ChannelName => l_ChannelName,
2198 p_Status => XDP_ADAPTER.pv_statusInUse,
2199 p_WFItemType => VerifyChannel.itemtype,
2200 p_WFItemKey => VerifyChannel.itemkey);
2201
2202 END IF;
2203 l_Result := 'CONTINUE';
2204 else
2205 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (l_ChannelName);
2206 l_Result := 'RE_PROCESS';
2207 end if;
2208 end if;
2209
2210 if l_Result = 'RE_PROCESS' then
2211 wf_engine.SetItemAttrText(itemtype => VerifyChannel.itemtype,
2212 itemkey => VerifyChannel.itemkey,
2213 aname => 'RE_PROCESS_FLAG',
2214 avalue => 'Y');
2215 else
2216 l_Result := 'CONTINUE';
2217 end if;
2218
2219 if c_CheckChannel%ISOPEN then
2220 close c_CheckChannel;
2221 end if;
2222
2223 /* Check if the FA is aborted. If so abort the process immediately */
2224 if IsFAAborted(l_FAInstanceID) = TRUE then
2225 /*
2226 ** Call the HAND Over Process which releases the channel to the next
2227 ** waiting wf process
2228 */
2229
2230 HandOverChannel(l_ChannelName, 0, NULL, 'FA', l_ErrCode, l_ErrStr);
2231
2232 if l_ErrCode <> 0 then
2233 RAISE e_HandOverChannelException;
2234 end if;
2235
2236 wf_engine.AbortProcess(itemtype => VerifyChannel.itemtype,
2237 itemkey => VerifyChannel.itemkey);
2238
2239 return wf_engine.eng_force;
2240 else
2241 return l_Result;
2242 end if;
2243
2244 exception
2245 when others then
2246 if c_CheckChannel%ISOPEN then
2247 close c_CheckChannel;
2248 end if;
2249
2250 IF l_channellock THEN
2251 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => l_ChannelName);
2252 END IF ;
2253
2254 Rollback to VerifyChannel;
2255
2256 x_Progress := 'XDPCORE_FA.VerifyChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2257 wf_core.context('XDPCORE_FA', 'VerifyChannel', itemtype, itemkey, null, x_Progress);
2258 raise;
2259 end VerifyChannel;
2260
2261
2262
2263
2264 Function ProvisionFE (itemtype in varchar2,
2265 itemkey in varchar2) return varchar2
2266
2267 is
2268 l_OrderID number;
2269 l_WIInstanceID number;
2270 l_FAInstanceID number;
2271 l_LineItemID number;
2272 l_ResubmissionJobID number;
2273 l_ErrCode number;
2274 l_ErrActionCode number;
2275 ErrCode number;
2276 ErrStr varchar2(1996);
2277
2278
2279
2280 l_ProcessMode varchar2(40);
2281 l_ChannelName varchar2(40);
2282 l_FeName varchar2(40);
2283 l_FPName varchar2(40);
2284 l_FeID number;
2285 l_Result varchar2(20);
2286 l_ErrAction varchar2(40);
2287 l_AdapterStatus varchar2(40);
2288 l_ChannelUsageCode varchar2(40) := 'NORMAL';
2289
2290 l_FPFuncMode varchar2(20);
2291 l_ResumeFAFlag varchar2(20);
2292 l_AbortFAFlag boolean := FALSE;
2293 l_SuspendFAFlag varchar2(20);
2294 l_HandOverChannelFlag varchar2(10);
2295 l_ErrStr varchar2(32767);
2296
2297 e_HandOverChannelException exception;
2298 e_AddAttributeException exception;
2299
2300 x_Progress VARCHAR2(2000);
2301 x_parameters VARCHAR2(4000);
2302
2303 cursor c_UpdateFAStatus(FAInstanceID number)is
2304 select 'Update FA Status'
2305 from XDP_FA_RUNTIME_LIST
2306 where FA_INSTANCE_ID = FAInstanceID
2307 for update of STATUS_CODE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN;
2308
2309 l_Status varchar2(1);
2310 begin
2311
2312 l_FPFuncMode := wf_engine.GetItemAttrText(itemtype => ProvisionFE.itemtype,
2313 itemkey => ProvisionFE.itemkey,
2314 aname => 'FP_FUNC_MODE');
2315
2316
2317 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => ProvisionFE.itemtype,
2318 itemkey => ProvisionFE.itemkey,
2319 aname => 'FA_INSTANCE_ID');
2320
2321
2322 /* If the FP is Retried as part of an exception which occured previously
2323 ** from the FMC, then the FP should go through the regular path of
2324 ** Getting the Channel etc..
2325 */
2326
2327 if l_FPFuncMode <> 'RUN' then
2328 /* Check if the FA is aborted. If so abort the process immediately */
2329 if IsFAAborted(l_FAInstanceID) = TRUE then
2330 wf_engine.abortprocess(itemtype => ProvisionFE.itemtype,
2331 itemkey => ProvisionFE.itemkey);
2332
2333 return wf_engine.eng_force;
2334 else
2335 return 'ERROR_RETRY';
2336 end if;
2337
2338 end if;
2339
2340
2341 l_ChannelName := wf_engine.GetItemAttrText(itemtype => ProvisionFE.itemtype,
2342 itemkey => ProvisionFE.itemkey,
2343 aname => 'CHANNEL_NAME');
2344
2345 l_FeID := wf_engine.GetItemAttrNumber(itemtype => ProvisionFE.itemtype,
2346 itemkey => ProvisionFE.itemkey,
2347 aname => 'FE_ID');
2348
2349 /* If the AbortFlag is set to True then before aborting the process the Channel being used
2350 ** must be handed over.
2351 */
2352
2353 /* Check if the FA is aborted. If so abort the process immediately */
2354 if IsFAAborted(l_FAInstanceID) = TRUE then
2355 if l_HandOverChannelFlag = 'TRUE' then
2356 /* Call the HAND Over Process which releases the channel to the next waiting wf process */
2357
2358
2359 l_ResubmissionJobID := GetResubmissionJobID(itemtype => ProvisionFE.itemtype,
2360 itemkey => ProvisionFE.itemkey);
2361
2362 if l_ResubmissionJobID <>0 then
2363 l_ChannelUsageCode := 'RESUBMISSION';
2364 end if;
2365
2366 HandOverChannel(l_ChannelName, l_FeID, l_ChannelUsageCode, 'FA', l_ErrCode, l_ErrStr);
2367
2368 if l_ErrCode <> 0 then
2369 RAISE e_HandOverChannelException;
2370 end if;
2371 end if;
2372
2373 wf_engine.AbortProcess(itemtype => ProvisionFE.itemtype,
2374 itemkey => ProvisionFE.itemkey);
2375
2376 return wf_engine.eng_force;
2377 end if;
2378
2379
2380
2381 l_FeName := wf_engine.GetItemAttrText(itemtype => ProvisionFE.itemtype,
2382 itemkey => ProvisionFE.itemkey,
2383 aname => 'FE_NAME');
2384
2385 l_FPName := wf_engine.GetItemAttrText(itemtype => ProvisionFE.itemtype,
2386 itemkey => ProvisionFE.itemkey,
2387 aname => 'FP_NAME');
2388
2389 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ProvisionFE.itemtype,
2390 itemkey => ProvisionFE.itemkey,
2391 aname => 'ORDER_ID');
2392
2393 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => ProvisionFE.itemtype,
2394 itemkey => ProvisionFE.itemkey,
2395 aname => 'WORKITEM_INSTANCE_ID');
2396
2397 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => ProvisionFE.itemtype,
2398 itemkey => ProvisionFE.itemkey,
2399 aname => 'LINE_ITEM_ID');
2400
2401 /* Need to dynamically execute the FP with te above parameters */
2402
2403
2404 XDP_UTILITIES.CALLFULFILLMENTPROC (P_PROCEDURE_NAME => l_FPName,
2405 P_ORDER_ID => l_OrderID,
2406 P_LINE_ITEM_ID => l_LineItemID,
2407 P_WI_INSTANCE_ID => l_WIInstanceID,
2408 P_FA_INSTANCE_ID => l_FAInstanceID,
2409 P_CHANNEL_NAME => l_ChannelName,
2410 P_FE_NAME => l_FeName,
2411 P_FA_ITEM_TYPE => ProvisionFE.itemtype,
2412 P_FA_ITEM_KEY => ProvisionFE.itemkey,
2413 P_RETURN_CODE => l_ErrCode,
2414 P_ERROR_DESCRIPTION => l_ErrStr);
2415
2416 select adapter_status into l_AdapterStatus
2417 from XDP_ADAPTER_REG
2418 where channel_name = l_ChannelName;
2419
2420
2421 --skilaru 05/19/2002
2422 --NON-PIPE based adapters are always available never be BUSY..
2423 if NOT XDP_ADAPTER_CORE_DB.checkLockRequired( l_ChannelName ) then
2424 wf_engine.SetItemAttrText(itemtype => ProvisionFe.itemtype,
2425 itemkey => ProvisionFe.itemkey,
2426 aname => 'HANDOVER_CHANNEL_FLAG',
2427 avalue => 'TRUE');
2428 elsif l_AdapterStatus <> 'BUSY' then
2429 wf_engine.SetItemAttrText(itemtype => ProvisionFe.itemtype,
2430 itemkey => ProvisionFe.itemkey,
2431 aname => 'HANDOVER_CHANNEL_FLAG',
2432 avalue => 'FALSE');
2433 else
2434 wf_engine.SetItemAttrText(itemtype => ProvisionFe.itemtype,
2435 itemkey => ProvisionFe.itemkey,
2436 aname => 'HANDOVER_CHANNEL_FLAG',
2437 avalue => 'TRUE');
2438 end if;
2439
2440
2441 if l_ErrCode <> 0 then
2442 xdpcore.context('XDPCORE_FA', 'ProvisionFe', 'FE', l_FeName);
2443 ExamineErrorCodes(l_ErrCode, l_FAInstanceID, l_AbortFAFlag, l_ErrAction);
2444 if l_ErrAction = 'PROVISIONING_ERROR' then
2445 l_result := 'FAILURE';
2446
2447 -- This error is due to the NOTIFY_ERROR Macro call...
2448 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2449 itemkey => ProvisionFE.itemkey,
2450 AttrName => 'FE_ERROR_TYPE',
2451 AttrValue => 'NOTIFY_ERROR',
2452 ErrCode => ErrCode,
2453 ErrStr => ErrStr);
2454
2455 /* Need to Set the Error Description */
2456 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2457 itemkey => ProvisionFE.itemkey,
2458 AttrName => 'FE_ERROR_DESCRIPTION',
2459 AttrValue => substr(l_ErrStr,1,2000),
2460 ErrCode => ErrCode,
2461 ErrStr => ErrStr);
2462
2463 if ErrCode <> 0 then
2464 x_progress := 'In XDPCORE_FA.ProvisionFE. Error when adding FE_ERROR_DESCRIPTION attribute. ';
2465 raise e_AddAttributeException;
2466 end if;
2467
2468 elsif l_ErrAction = 'NE_SESSION_LOST' then
2469 l_result := 'SESSION_LOST';
2470
2471 -- This error is due to the NOTIFY_ERROR Macro call...
2472 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2473 itemkey => ProvisionFE.itemkey,
2474 AttrName => 'FE_ERROR_TYPE',
2475 AttrValue => 'SESSION_LOST',
2476 ErrCode => ErrCode,
2477 ErrStr => ErrStr);
2478
2479 /* Need to Set the Error Description */
2480 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2481 itemkey => ProvisionFE.itemkey,
2482 AttrName => 'FE_ERROR_DESCRIPTION',
2483 AttrValue => substr(l_ErrStr,1,2000),
2484 ErrCode => ErrCode,
2485 ErrStr => ErrStr);
2486
2487 if ErrCode <> 0 then
2488 x_progress := 'In XDPCORE_FA.ProvisionFE. Error when adding FE_ERROR_DESCRIPTION attribute. ';
2489 raise e_AddAttributeException;
2490 end if;
2491
2492 elsif l_ErrAction = 'NE_TIMED_OUT' then
2493 l_result := 'SUCCESS';
2494
2495 /* Need to Set the Error Description */
2496 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2497 itemkey => ProvisionFE.itemkey,
2498 AttrName => 'FE_ERROR_DESCRIPTION',
2499 AttrValue => substr(l_ErrStr,1,2000),
2500 ErrCode => ErrCode,
2501 ErrStr => ErrStr);
2502
2503 if ErrCode <> 0 then
2504 x_progress := 'In XDPCORE_FA.ProvisionFE. Error when adding FE_ERROR_DESCRIPTION attribute. ';
2505 raise e_AddAttributeException;
2506 end if;
2507 elsif l_ErrAction = 'DYNAMIC_EXEC_ERROR' then
2508
2509 x_ErrorID := -1;
2510
2511 /* bug fix 1945013
2512 dont set the adapte status to error..
2513
2514 x_parameters := 'PROC_NAME='||l_FPName||'#XDP#'||
2515 'ERROR_STRING='||substr(l_ErrStr,1,1500)||'#XDP#';
2516
2517 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2518 p_ChannelName => l_ChannelName,
2519 p_Status => XDP_ADAPTER.pv_statusError,
2520 p_ErrorMsg => 'XDP_DYNAMIC_PROC_EXEC_ERROR',
2521 p_ErrorMsgParams => x_parameters
2522 );
2523
2524 COMMIT;
2525 */
2526
2527 /* Need to Set the Error Description */
2528 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2529 itemkey => ProvisionFE.itemkey,
2530 AttrName => 'FE_ERROR_DESCRIPTION',
2531 AttrValue => substr(l_ErrStr,1,2000),
2532 ErrCode => ErrCode,
2533 ErrStr => ErrStr);
2534
2535 l_result := 'EXCEPTION';
2536
2537 elsif l_ErrAction = 'INTERNAL_SDP_ERROR' then
2538
2539
2540 x_ErrorID := -1;
2541
2542 /* bug fix 1945013
2543 dont set the adapte status to error..
2544
2545 x_parameters := 'PROC_NAME='||l_FPName||'#XDP#'||
2546 'CHANNEL_NAME='||l_ChannelName||'#XDP#'||
2547 'FE_NAME='||l_FeName||'#XDP#'||
2548 'ERROR_STRING='||substr(l_ErrStr,1,1500)||'#XDP#';
2549
2550
2551
2552 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2553 p_ChannelName => l_ChannelName,
2554 p_Status => XDP_ADAPTER.pv_statusError,
2555 p_ErrorMsg => 'XDP_INTERNAL_CHANNEL_ERROR',
2556 p_ErrorMsgParams => x_parameters
2557 );
2558
2559 COMMIT;
2560 */
2561
2562 /* Need to Set the Error Description */
2563 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2564 itemkey => ProvisionFE.itemkey,
2565 AttrName => 'FE_ERROR_DESCRIPTION',
2566 AttrValue => substr(l_ErrStr,1,2000),
2567 ErrCode => ErrCode,
2568 ErrStr => ErrStr);
2569
2570 l_result := 'EXCEPTION';
2571
2572 elsif l_ErrAction = 'CHANGE_CONNECTION_ERROR' then
2573
2574 /* bug fix 1945013
2575 dont set the adapte status to error..
2576
2577 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2578 p_ChannelName => l_ChannelName,
2579 p_Status => XDP_ADAPTER.pv_statusDisconnected
2580 );
2581
2582 COMMIT;
2583 */
2584
2585 wf_engine.SetItemAttrText(itemtype => ProvisionFE.itemtype,
2586 itemkey => ProvisionFE.itemkey,
2587 aname => 'HANDOVER_CHANNEL_FLAG',
2588 avalue => 'FALSE');
2589
2590 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2591 itemkey => ProvisionFE.itemkey,
2592 AttrName => 'FE_ERROR_DESCRIPTION',
2593 AttrValue => substr(l_ErrStr,1,2000),
2594 ErrCode => ErrCode,
2595 ErrStr => ErrStr);
2596
2597 if ErrCode <> 0 then
2598 x_progress := 'In XDPCORE_FA.ProvisionFE. Error when adding FE_ERROR_DESCRIPTION attribute. ';
2599 raise e_AddAttributeException;
2600 end if;
2601 l_result := 'FAILURE';
2602
2603 elsif l_ErrAction = 'UNHANDLED_EXCEPTION' then
2604
2605
2606 x_ErrorID := -1;
2607
2608 /* bug fix 1945013
2609 dont set the adapte status to error..
2610
2611 x_parameters := 'PROC_NAME='||l_FPName||'#XDP#'||
2612 'ERROR_STRING='||substr(l_ErrStr,1,1500)||'#XDP#';
2613
2614 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2615 p_ChannelName => l_ChannelName,
2616 p_Status => XDP_ADAPTER.pv_statusError,
2617 p_ErrorMsg => 'XDP_UNHANDLED_FP_EXEC_ERROR',
2618 p_ErrorMsgParams => x_parameters
2619 );
2620
2621 COMMIT;
2622
2623 */
2624
2625 /* Need to Set the Error Description */
2626 XDPCORE.CheckNAddItemAttrText (itemtype => ProvisionFE.itemtype,
2627 itemkey => ProvisionFE.itemkey,
2628 AttrName => 'FE_ERROR_DESCRIPTION',
2629 AttrValue => substr(l_ErrStr,1,2000),
2630 ErrCode => ErrCode,
2631 ErrStr => ErrStr);
2632
2633
2634 l_result := 'EXCEPTION';
2635 end if;
2636 else
2637
2638 l_Result := 'SUCCESS';
2639 end if;
2640
2641 /* Check the FA Abort Flag and set the FA Execution Status accordingly */
2642 if (l_AbortFAFlag = TRUE) OR (IsFAAborted(l_FAInstanceID) = TRUE) then
2643 if l_HandOverChannelFlag = 'TRUE' then
2644 /* Call the HAND Over Process which releases the channel to the next waiting wf process */
2645
2646
2647 l_ResubmissionJobID := GetResubmissionJobID(itemtype => ProvisionFE.itemtype,
2648 itemkey => ProvisionFE.itemkey);
2649
2650 if l_ResubmissionJobID <>0 then
2651 l_ChannelUsageCode := 'RESUBMISSION';
2652 end if;
2653
2654 HandOverChannel(l_ChannelName, l_FeID, l_ChannelUsageCode, 'FA', l_ErrCode, l_ErrStr);
2655
2656 if l_ErrCode <> 0 then
2657 RAISE e_HandOverChannelException;
2658 end if;
2659 end if;
2660
2661 /* Set the status of Execution */
2662 FOR v_UpdateFAStatus in c_UpdateFAStatus(l_FAInstanceID) LOOP
2663 update XDP_FA_RUNTIME_LIST set STATUS_CODE = l_Result,
2664 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2665 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2666 where current of c_UpdateFAStatus;
2667 END LOOP;
2668
2669
2670 wf_engine.AbortProcess(itemtype => ProvisionFE.itemtype,
2671 itemkey => ProvisionFE.itemkey);
2672
2673 return wf_engine.eng_force;
2674 else
2675 return l_Result;
2676 end if;
2677
2678 exception
2679 when e_HandOverChannelException then
2680
2681 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
2682 (p_ChannelName => l_ChannelName);
2683
2684 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
2685 raise;
2686
2687 when others then
2688
2689 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
2690 (p_ChannelName => l_ChannelName);
2691
2692 /* Set the FP_FUNC_MODE to be RETRY so that when the user retries the activity it goes
2693 ** back to the regular get Channel Process
2694 */
2695
2696 wf_engine.SetItemAttrText(itemtype => ProvisionFE.itemtype,
2697 itemkey => ProvisionFE.itemkey,
2698 aname => 'FP_FUNC_MODE',
2699 avalue => 'RETRY');
2700
2701 COMMIT;
2702 x_Progress := 'XDPCORE_FA.ProvisionFE. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2703 wf_core.context('XDPCORE_FA', 'ProvisionFE', itemtype, itemkey, null, x_Progress);
2704 raise;
2705
2706 end ProvisionFE;
2707
2708
2709 Procedure ExamineErrorCodes (errcode in number,
2710 FAInstanceID in number,
2711 AbortFAFlag OUT NOCOPY boolean,
2712 action OUT NOCOPY varchar2)
2713
2714 is
2715
2716 x_Progress VARCHAR2(2000);
2717
2718 begin
2719
2720 /* Check if the FA is aborted. If so abort the process immediately */
2721 if IsFAAborted(FAInstanceID) = TRUE then
2722 AbortFAFlag := TRUE;
2723 end if;
2724
2725 if (errcode = -666) OR (errcode = -20050) or (errcode = -20051) then
2726 action := 'PROVISIONING_ERROR';
2727 return;
2728 elsif errcode >= -20300 and errcode <= -20101 then
2729 action := 'INTERNAL_SDP_ERROR';
2730 return;
2731 elsif (errcode >= -20503 and errcode <= -20501) OR
2732 errcode = -20003 then
2733 action := 'DYNAMIC_EXEC_ERROR';
2734 return;
2735 elsif errcode >= -20035 and errcode <= -20031 then
2736 action := 'CHANGE_CONNECTION_ERROR';
2737 return;
2738 elsif errcode = -20610 then
2739 action := 'NE_SESSION_LOST';
2740 elsif errcode = -20620 then
2741 action := 'NE_TIMED_OUT';
2742 else
2743 action := 'UNHANDLED_EXCEPTION';
2744 return;
2745 end if;
2746
2747
2748 exception
2749 when others then
2750 x_Progress := 'XDPCORE_FA.ExamineErrorCodes. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2751 wf_core.context('XDPCORE_FA', 'ExamineErrorCodes', null, null, x_Progress,null);
2752 raise;
2753 end ExamineErrorCodes;
2754
2755
2756
2757 Procedure ReleaseFEChannel (itemtype in varchar2,
2758 itemkey in varchar2)
2759 is
2760 l_ChannelName varchar2(40);
2761 l_FeName varchar2(40);
2762 l_HandOverChannelFlag varchar2(40);
2763 l_ErrStr varchar2(4000);
2764
2765 l_ErrCode number;
2766 l_OrderID number;
2767 l_FeID number;
2768 l_WIInstanceID number;
2769 l_FAInstanceID number;
2770 l_ChannelUsageCode varchar2(40) := 'NORMAL';
2771 l_ResubmissionJobID number;
2772
2773 l_AdminRequest varchar2(40);
2774 l_RequestFlag BOOLEAN;
2775 l_Status varchar2(1);
2776
2777 e_HandOverChannelException exception;
2778
2779 x_Progress VARCHAR2(2000);
2780
2781 begin
2782
2783 l_ChannelName := wf_engine.GetItemAttrText(itemtype => ReleaseFEChannel.itemtype,
2784 itemkey => ReleaseFEChannel.itemkey,
2785 aname => 'CHANNEL_NAME');
2786
2787 l_FeID := wf_engine.GetItemAttrNumber(itemtype => ReleaseFEChannel.itemtype,
2788 itemkey => ReleaseFEChannel.itemkey,
2789 aname => 'FE_ID');
2790
2791 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ReleaseFEChannel.itemtype,
2792 itemkey => ReleaseFEChannel.itemkey,
2793 aname => 'ORDER_ID');
2794
2795 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => ReleaseFEChannel.itemtype,
2796 itemkey => ReleaseFEChannel.itemkey,
2797 aname => 'WORKITEM_INSTANCE_ID');
2798
2799 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => ReleaseFEChannel.itemtype,
2800 itemkey => ReleaseFEChannel.itemkey,
2801 aname => 'FA_INSTANCE_ID');
2802
2803
2804 l_ResubmissionJobID := GetResubmissionJobID(itemtype => ReleaseFEChannel.itemtype,
2805 itemkey => ReleaseFEChannel.itemkey);
2806
2807 l_HandOverChannelFlag := wf_engine.GetItemAttrText(itemtype => ReleaseFEChannel.itemtype,
2808 itemkey => ReleaseFEChannel.itemkey,
2809 aname => 'HANDOVER_CHANNEL_FLAG');
2810
2811 if l_ResubmissionJobID <> 0 then
2812 l_ChannelUsageCode := 'RESUBMISSION';
2813 end if;
2814
2815 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
2816 (p_ChannelName => l_ChannelName);
2817
2818 if l_HandOverChannelFlag = 'TRUE' then
2819 /* Call the HAND Over Process which releases the channel to the next waiting wf process */
2820
2821 HandOverChannel(l_ChannelName, l_FeID, l_ChannelUsageCode, 'FA', l_ErrCode, l_ErrStr);
2822
2823 if l_ErrCode <> 0 then
2824 RAISE e_HandOverChannelException;
2825 end if;
2826 end if;
2827 wf_engine.SetItemAttrText(itemtype => ReleaseFEChannel.itemtype,
2828 itemkey => ReleaseFEChannel.itemkey,
2829 aname => 'HANDOVER_CHANNEL_FLAG',
2830 avalue => 'TRUE');
2831
2832 COMMIT;
2833
2834 /* Check if the FA is aborted. If so abort the process immediately */
2835 if IsFAAborted(l_FAInstanceID) = TRUE then
2836 wf_engine.abortprocess(itemtype => ReleaseFEChannel.itemtype,
2837 itemkey => ReleaseFEChannel.itemkey);
2838
2839 return;
2840 end if;
2841
2842 exception
2843 when others then
2844 x_Progress := 'XDPCORE_FA.ReleaseFEChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2845 wf_core.context('XDPCORE_FA', 'ReleaseFEChannel', itemtype, itemkey, null, x_Progress);
2846 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => l_ChannelName);
2847 raise;
2848 end ReleaseFEChannel;
2849
2850
2851
2852 Procedure StopFAProcessing (itemtype in varchar2,
2853 itemkey in varchar2,
2854 actid in number)
2855 is
2856 l_FAInstanceID number;
2857 x_Progress VARCHAR2(2000);
2858
2859 begin
2860
2861 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => StopFAProcessing.itemtype,
2862 itemkey => StopFAProcessing.itemkey,
2863 aname => 'FA_INSTANCE_ID');
2864
2865
2866
2867 if l_FAInstanceID is null then
2868 /* SOME THING IS WRONG !!! */
2869 /* RAISE ???? */
2870 null;
2871 else
2872
2873
2874
2875 /* DO I ABORT the Processing ??
2876 */
2877 /* Stop the FA Processing.. Hence mark the status of the FA to be ABORTED
2878 update XDP_FA_RUNTIME_LIST
2879 set STATUS_CODE = 'ABORTED'
2880 where FA_INSTANCE_ID = l_FAInstanceID;
2881 */
2882
2883
2884 null;
2885 end if;
2886
2887
2888
2889
2890 exception
2891 when others then
2892 x_Progress := 'XDPCORE_FA.StopFAProcessing. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2893 wf_core.context('XDPCORE_FA', 'StopFAProcessing', itemtype, itemkey, to_char(actid),null);
2894 raise;
2895 end StopFAProcessing;
2896
2897
2898
2899 Procedure ResetChannel (itemtype in varchar2,
2900 itemkey in varchar2)
2901 is
2902
2903 x_Progress VARCHAR2(2000);
2904
2905 begin
2906
2907 /*
2908 * 1. Reset the channel to NULL
2909 * 2. Set the mode to be 'RETRY'
2910 */
2911
2912
2913
2914
2915 wf_engine.SetItemAttrText(itemtype => ResetChannel.itemtype,
2916 itemkey => ResetChannel.itemkey,
2917 aname => 'CHANNEL_NAME',
2918 avalue => null);
2919
2920 exception
2921 when others then
2922 x_Progress := 'XDPCORE_FA.ResetChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2923 wf_core.context('XDPCORE_FA', 'ResetChannel', itemtype, itemkey, null, x_Progress);
2924 raise;
2925 end ResetChannel;
2926
2927
2928
2929
2930
2931 Function GetFaCaller (itemtype in varchar2,
2932 itemkey in varchar2,
2933 actid in number) return varchar2
2934
2935 is
2936
2937 l_FaCaller varchar2(40);
2938 x_Progress VARCHAR2(2000);
2939
2940 begin
2941
2942 l_FaCaller := wf_engine.GetItemAttrText(itemtype => GetFaCaller.itemtype,
2943 itemkey => GetFaCaller.itemkey,
2944 aname => 'FA_CALLER');
2945
2946
2947
2948
2949 return (l_FaCaller);
2950
2951 exception
2952 when others then
2953 x_Progress := 'XDPCORE_FA.GetFaCaller. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2954 wf_core.context('XDPCORE_FA', 'GetFaCaller', itemtype, itemkey, to_char(actid),null);
2955 raise;
2956 end GetFaCaller;
2957
2958
2959
2960 Procedure CreateFAProcess (parentitemtype in varchar2,
2961 parentitemkey in varchar2,
2962 FAInstanceID in number,
2963 WIInstanceID in number,
2964 OrderID in number,
2965 FaCaller in varchar2 DEFAULT 'EXTERNAL',
2966 FaMaster in varchar2,
2967 FaItemtype OUT NOCOPY varchar2,
2968 FaItemkey OUT NOCOPY varchar2,
2969 ErrCode OUT NOCOPY number,
2970 ErrStr OUT NOCOPY varchar2)
2971
2972 is
2973
2974 l_tempKey varchar2(240);
2975 l_LineItemID number;
2976
2977 x_Progress VARCHAR2(2000);
2978
2979 l_NameArray Wf_Engine.NameTabTyp;
2980 l_ValueNumArray Wf_Engine.NumTabTyp;
2981 begin
2982 FaItemtype := 'XDPPROV';
2983 ErrCode := 0;
2984 ErrStr := null;
2985
2986 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => parentitemtype,
2987 itemkey => parentitemkey,
2988 aname => 'LINE_ITEM_ID');
2989
2990 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
2991 FaItemkey := to_char(OrderID) || '-WI-' || to_char(WIInstanceID) || '-FA' || l_tempKey;
2992
2993
2994 -- Create Process and Bulk Set Item Attribute
2995 XDPCORE.CreateNAddAttrNParentLabel(itemtype => FaItemtype,
2996 itemkey => FaItemkey,
2997 processname => 'FA_PROCESS',
2998 parentitemtype => parentitemtype,
2999 parentitemkey => parentitemkey,
3000 waitflowlabel => FaMaster,
3001 OrderID => OrderID,
3002 LineitemID => l_LineItemID,
3003 WIInstanceID => WIInstanceID,
3004 FAInstanceID => FAInstanceID);
3005
3006 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
3007 itemkey => FaItemkey,
3008 aname => 'MASTER_TO_CONTINUE',
3009 avalue => FaMaster);
3010
3011 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3012 itemkey => FaItemkey,
3013 AttrName => 'FA_CALLER',
3014 AttrValue => FaCaller,
3015 ErrCode => ErrCode,
3016 ErrStr => ErrStr);
3017
3018 if ErrCode <> 0 then
3019 return;
3020 end if;
3021
3022 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3023 itemkey => FaItemkey,
3024 AttrName => 'HANDOVER_CHANNEL_FLAG',
3025 AttrValue => 'TRUE',
3026 ErrCode => ErrCode,
3027 ErrStr => ErrStr);
3028
3029 if ErrCode <> 0 then
3030 return;
3031 end if;
3032
3033 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3034 itemkey => FaItemkey,
3035 AttrName => 'FP_FUNC_MODE',
3036 AttrValue => 'RUN',
3037 ErrCode => ErrCode,
3038 ErrStr => ErrStr);
3039
3040 if ErrCode <> 0 then
3041 return;
3042 end if;
3043
3044 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3045 itemkey => FaItemkey,
3046 AttrName => 'RE_PROCESS_FLAG',
3047 AttrValue => 'N',
3048 ErrCode => ErrCode,
3049 ErrStr => ErrStr);
3050
3051 if ErrCode <> 0 then
3052 return;
3053 end if;
3054
3055
3056 XDPCORE.CheckNAddItemAttrDate (itemtype => FaItemtype,
3057 itemkey => FaItemkey,
3058 AttrName => 'RE_PROCESS_ENQ_TIME',
3059 AttrValue => SYSDATE,
3060 ErrCode => ErrCode,
3061 ErrStr => ErrStr);
3062
3063 if ErrCode <> 0 then
3064 return;
3065 end if;
3066
3067 exception
3068 when others then
3069 ErrCode := SQLCODE;
3070 ErrStr := SUBSTR(SQLERRM, 1, 800);
3071 end CreateFAProcess;
3072
3073
3074 Procedure CreateFAProcess(parentitemtype in varchar2,
3075 parentitemkey in varchar2,
3076 FAInstanceID in number,
3077 WIInstanceID in number,
3078 OrderID in number,
3079 LineItemID in number,
3080 ResubmissionJobID in number,
3081 FaCaller in varchar2 DEFAULT 'EXTERNAL',
3082 FaMaster in varchar2,
3083 FaItemtype OUT NOCOPY varchar2,
3084 FaItemkey OUT NOCOPY varchar2,
3085 ErrCode OUT NOCOPY number,
3086 ErrStr OUT NOCOPY varchar2)
3087
3088 is
3089
3090 l_tempKey varchar2(240);
3091
3092 x_Progress VARCHAR2(2000);
3093
3094 l_NameArray Wf_Engine.NameTabTyp;
3095 l_ValueNumArray Wf_Engine.NumTabTyp;
3096
3097 begin
3098 FaItemtype := 'XDPPROV';
3099 ErrCode := 0;
3100 ErrStr := null;
3101
3102 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
3103 FaItemkey := to_char(OrderID) || '-WI-' || to_char(WIInstanceID) || '-FA' || l_tempKey;
3104
3105
3106
3107 -- Bulk Set Item Attribute
3108 XDPCORE.CreateAndAddAttrNum(itemtype => FaItemtype,
3109 itemkey => FaItemkey,
3110 processname => 'FA_PROCESS',
3111 parentitemtype => parentitemtype,
3112 parentitemkey => parentitemkey,
3113 OrderID => OrderID,
3114 LineitemID => LineItemID,
3115 WIInstanceID => WIInstanceID,
3116 FAInstanceID => FAInstanceID);
3117
3118
3119 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
3120 itemkey => FaItemkey,
3121 aname => 'MASTER_TO_CONTINUE',
3122 avalue => FaMaster);
3123
3124 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3125 itemkey => FaItemkey,
3126 AttrName => 'FA_CALLER',
3127 AttrValue => FaCaller,
3128 ErrCode => ErrCode,
3129 ErrStr => ErrStr);
3130
3131 if ErrCode <> 0 then
3132 return;
3133 end if;
3134
3135 XDPCORE.CheckNAddItemAttrNumber (itemtype => FaItemtype,
3136 itemkey => FaItemkey,
3137 AttrName => 'RESUBMISSION_JOB_ID',
3138 AttrValue => ResubmissionJobID,
3139 ErrCode => ErrCode,
3140 ErrStr => ErrStr);
3141
3142 if ErrCode <> 0 then
3143 return;
3144 end if;
3145
3146 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3147 itemkey => FaItemkey,
3148 AttrName => 'HANDOVER_CHANNEL_FLAG',
3149 AttrValue => 'TRUE',
3150 ErrCode => ErrCode,
3151 ErrStr => ErrStr);
3152
3153 if ErrCode <> 0 then
3154 return;
3155 end if;
3156
3157 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3158 itemkey => FaItemkey,
3159 AttrName => 'FP_FUNC_MODE',
3160 AttrValue => 'RUN',
3161 ErrCode => ErrCode,
3162 ErrStr => ErrStr);
3163
3164 if ErrCode <> 0 then
3165 return;
3166 end if;
3167
3168 XDPCORE.CheckNAddItemAttrText (itemtype => FaItemtype,
3169 itemkey => FaItemkey,
3170 AttrName => 'RE_PROCESS_FLAG',
3171 AttrValue => 'N',
3172 ErrCode => ErrCode,
3173 ErrStr => ErrStr);
3174
3175 if ErrCode <> 0 then
3176 return;
3177 end if;
3178
3179
3180 XDPCORE.CheckNAddItemAttrDate (itemtype => FaItemtype,
3181 itemkey => FaItemkey,
3182 AttrName => 'RE_PROCESS_ENQ_TIME',
3183 AttrValue => SYSDATE,
3184 ErrCode => ErrCode,
3185 ErrStr => ErrStr);
3186
3187 if ErrCode <> 0 then
3188 return;
3189 end if;
3190
3191 exception
3192 when others then
3193 ErrCode := SQLCODE;
3194 ErrStr := SUBSTR(SQLERRM, 1, 800);
3195 end CreateFAProcess;
3196
3197
3198 Function GetResubmissionJobID (itemtype in varchar2,
3199 itemkey in varchar2) return number is
3200 l_JobID number;
3201 e_UnhandledException exception;
3202 begin
3203 begin
3204 l_JobID := wf_engine.GetItemAttrNumber(itemtype => GetResubmissionJobID.itemtype,
3205 itemkey => GetResubmissionJobID.itemkey,
3206 aname => 'RESUBMISSION_JOB_ID');
3207
3208 exception
3209 when others then
3210 if sqlcode = -20002 then
3211 l_JobID := 0;
3212 wf_core.clear;
3213 else
3214 raise e_UnhandledException;
3215 end if;
3216 end;
3217
3218 return l_JobID;
3219
3220 End GetResubmissionJobID;
3221
3222
3223
3224 Procedure LaunchFAProcess (itemtype in varchar2,
3225 itemkey in varchar2)
3226
3227 is
3228 l_OrderID number;
3229 l_WIInstanceID number;
3230 l_LineItemID number;
3231 l_Counter number := 0;
3232 l_FAInstanceID number;
3233 l_FAID number;
3234 l_Priority number;
3235
3236 l_ErrCode number;
3237 l_ErrStr varchar2(1996);
3238
3239 l_tempKey varchar2(240);
3240
3241 cursor c_GetIndFA (WIInstanceID number) is
3242 select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
3243 from XDP_FA_RUNTIME_LIST
3244 where WORKITEM_INSTANCE_ID = WIInstanceID
3245 and STATUS_CODE = 'STANDBY'
3246 and (PROVISIONING_SEQUENCE IS NULL or PROVISIONING_SEQUENCE = 0) ;
3247
3248 cursor c_GetDepFA (WIInstanceID number) is
3249 select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
3250 from XDP_FA_RUNTIME_LIST
3251 where WORKITEM_INSTANCE_ID = WIInstanceID
3252 and STATUS_CODE = 'STANDBY'
3253 and PROVISIONING_SEQUENCE > 0;
3254
3255 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
3256 t_ChildKeys t_ChildKeyTable;
3257
3258 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
3259 t_ChildTypes t_ChildTypeTable;
3260
3261
3262 e_NoFAsFoundException exception;
3263 e_AddAttributeException exception;
3264
3265 x_Progress VARCHAR2(2000);
3266
3267 begin
3268
3269 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchFAProcess.itemtype,
3270 itemkey => LaunchFAProcess.itemkey,
3271 aname => 'ORDER_ID');
3272
3273 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => LaunchFAProcess.itemtype,
3274 itemkey => LaunchFAProcess.itemkey,
3275 aname => 'WORKITEM_INSTANCE_ID');
3276
3277 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchFAProcess.itemtype,
3278 itemkey => LaunchFAProcess.itemkey,
3279 aname => 'LINE_ITEM_ID');
3280
3281 /* Launch all the Independent FA Processes */
3282
3283 if c_GetIndFA%ISOPEN then
3284 close c_GetIndFA;
3285 end if;
3286
3287 open c_GetIndFA(l_WIInstanceID);
3288
3289 LOOP
3290
3291 Fetch c_GetIndFA into l_FAInstanceID, l_FAID, l_Priority;
3292 EXIT when c_GetIndFA%NOTFOUND;
3293
3294 l_Counter := l_Counter + 1;
3295
3296 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
3297 l_tempKey := to_char(l_OrderID) || '-WI-' || to_char(l_WIInstanceID) || '-INDFA-' || l_tempKey;
3298
3299 t_ChildTypes(l_Counter) := 'XDPPROV';
3300 t_ChildKeys(l_Counter) := l_tempKey;
3301
3302 -- Create Process and Bulk Set Item Attribute
3303 XDPCORE.CreateAndAddAttrNum(itemtype => t_ChildTypes(l_Counter),
3304 itemkey => t_ChildKeys(l_Counter),
3305 processname => 'INDEPENDENT_FA_PROCESS',
3306 parentitemtype => LaunchFAProcess.itemtype,
3307 parentitemkey => LaunchFAProcess.itemkey,
3308 OrderID => l_OrderID,
3309 LineitemID => l_LineItemID,
3310 WIInstanceID => l_WIInstanceID,
3311 FAInstanceID => l_FAInstanceID);
3312
3313
3314 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
3315 itemkey => t_ChildKeys(l_Counter),
3316 AttrName => 'FA_PRIORITY',
3317 AttrValue => to_char(l_Priority),
3318 ErrCode => l_ErrCode,
3319 ErrStr => l_ErrStr);
3320
3321 if l_ErrCode <> 0 then
3322 x_progress := 'XDPCORE_FA.LaunchFAProcess. Error when adding item attribute FA_PRIORITY. Order ID: ' || l_OrderID || ' Workitem Instance ID: ' || l_WIInstanceID || ' Line Item ID: ' || l_LineItemID;
3323
3324 raise e_AddAttributeException;
3325 end if;
3326
3327
3328 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
3329 itemkey => t_ChildKeys(l_Counter),
3330 AttrName => 'CURRENT_FA_SEQUENCE',
3331 AttrValue => 0,
3332 ErrCode => l_ErrCode,
3333 ErrStr => l_ErrStr);
3334
3335 if l_ErrCode <> 0 then
3336 x_progress := 'XDPCORE_FA.LaunchFAProcess. Error when adding item attribute CURRENT_FA_SEQUENCE. Order ID: ' || l_OrderID || ' Workitem Instance ID: ' || l_WIInstanceID || ' Line Item ID: ' || l_LineItemID;
3337
3338 raise e_AddAttributeException;
3339 end if;
3340
3341 END LOOP;
3342
3343 close c_GetIndFA;
3344
3345 /* Now Create the processes for ONE Dependent FA process */
3346
3347 if c_GetDepFA%ISOPEN then
3348 close c_GetDepFA;
3349 end if;
3350
3351 open c_GetDepFA(l_WIInstanceID);
3352 Fetch c_GetDepFA into l_FAInstanceID, l_FAID, l_Priority;
3353
3354 if c_GetDepFA%FOUND then
3355 l_Counter := l_Counter + 1;
3356
3357 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
3358 l_tempKey := to_char(l_OrderID) || '-WI-' || to_char(l_WIInstanceID) || '-DEPFA-' || l_tempKey;
3359
3360 t_ChildTypes(l_Counter) := 'XDPPROV';
3361 t_ChildKeys(l_Counter) := l_tempKey;
3362
3363
3364 -- Create Process and Bulk Set Item Attribute
3365 XDPCORE.CreateAndAddAttrNum(itemtype => t_ChildTypes(l_Counter),
3366 itemkey => t_ChildKeys(l_Counter),
3367 processname => 'DEPENDENT_FA_PROCESS',
3368 parentitemtype => LaunchFAProcess.itemtype,
3369 parentitemkey => LaunchFAProcess.itemkey,
3370 OrderID => l_OrderID,
3371 LineitemID => l_LineItemID,
3372 WIInstanceID => l_WIInstanceID,
3373 FAInstanceID => l_FAInstanceID);
3374
3375
3376 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
3377 itemkey => t_ChildKeys(l_Counter),
3378 AttrName => 'FA_PRIORITY',
3379 AttrValue => to_char(l_Priority),
3380 ErrCode => l_ErrCode,
3381 ErrStr => l_ErrStr);
3382
3383 if l_ErrCode <> 0 then
3384 x_progress := 'XDPCORE_FA.LaunchFAProcess. Error when adding item attribute FA_PRIORITY. Order ID: ' || l_OrderID || ' Workitem Instance ID: ' || l_WIInstanceID || ' Line Item ID: ' || l_LineItemID;
3385
3386 raise e_AddAttributeException;
3387 end if;
3388
3389
3390 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
3391 itemkey => t_ChildKeys(l_Counter),
3392 AttrName => 'CURRENT_FA_SEQUENCE',
3393 AttrValue => 0,
3394 ErrCode => l_ErrCode,
3395 ErrStr => l_ErrStr);
3396
3397 if l_ErrCode <> 0 then
3398 x_progress := 'XDPCORE_FA.LaunchFAProcess. Error when adding item attribute CURRENT_FA_SEQUENCE. Order ID: ' || l_OrderID || ' Workitem Instance ID: ' || l_WIInstanceID || ' Line Item ID: ' || l_LineItemID;
3399
3400 raise e_AddAttributeException;
3401 end if;
3402
3403 end if;
3404
3405 if l_Counter = 0 then
3406 x_Progress := 'XDPCORE_FA.LaunchFAProcess. No FA''s found to be processed for Order: ' || l_OrderID || ' WorkitemInstanceID: ' || l_WIInstanceID;
3407 RAISE e_NoFAsFoundException;
3408 end if;
3409
3410 /* Launch all the WOrkflows */
3411 FOR i in 1..l_Counter LOOP
3412 wf_engine.StartProcess(t_ChildTypes(i),
3413 t_ChildKeys(i));
3414 END LOOP;
3415
3416
3417 exception
3418 when e_AddAttributeException then
3419 if c_GetIndFA%ISOPEN then
3420 close c_GetIndFA;
3421 end if;
3422
3423 if c_GetDepFA%ISOPEN then
3424 close c_GetDepFA;
3425 end if;
3426
3427 wf_core.context('XDPCORE_FA', 'LaunchFAProcess', itemtype, itemkey, null, x_Progress);
3428 raise;
3429
3430 when e_NoFAsFoundException then
3431 if c_GetIndFA%ISOPEN then
3432 close c_GetIndFA;
3433 end if;
3434
3435 if c_GetDepFA%ISOPEN then
3436 close c_GetDepFA;
3437 end if;
3438
3439 wf_core.context('XDPCORE_FA', 'LaunchFAProcess', itemtype, itemkey, null, x_Progress);
3440 raise;
3441
3442 when others then
3443 if c_GetIndFA%ISOPEN then
3444 close c_GetIndFA;
3445 end if;
3446
3447 if c_GetDepFA%ISOPEN then
3448 close c_GetDepFA;
3449 end if;
3450
3451 x_Progress := 'XDPCORE_FA.LaunchFAProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
3452 wf_core.context('XDPCORE_FA', 'LaunchFAProcess', itemtype, itemkey, null, x_Progress);
3453 raise;
3454 end LaunchFAProcess;
3455
3456
3457
3458
3459 Procedure LaunchFAProcessSeq (itemtype in varchar2,
3460 itemkey in varchar2)
3461
3462 is
3463
3464 l_Counter number;
3465 l_tempKey varchar2(240);
3466 l_CurrentFASeq number;
3467 l_PrevFASeq number;
3468 l_WIInstanceID number;
3469 l_FAID number;
3470 l_FAInstanceID number;
3471 l_OrderID number;
3472 l_priority number;
3473 l_ErrCode number;
3474 l_ErrDescription varchar2(1996);
3475
3476 cursor c_FAList (wi_id number, prov_seq number) is
3477 select FULFILLMENT_ACTION_ID, FA_INSTANCE_ID, PROVISIONING_SEQUENCE, PRIORITY
3478 from XDP_FA_RUNTIME_LIST
3479 where WORKITEM_INSTANCE_ID = wi_id
3480 and PROVISIONING_SEQUENCE = (
3481 select MIN(PROVISIONING_SEQUENCE)
3482 from XDP_FA_RUNTIME_LIST
3483 where WORKITEM_INSTANCE_ID = wi_id
3484 and PROVISIONING_SEQUENCE > prov_seq);
3485
3486 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
3487 t_ChildKeys t_ChildKeyTable;
3488
3489 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
3490 t_ChildTypes t_ChildTypeTable;
3491
3492 TYPE t_IdTable is table of number INDEX BY BINARY_INTEGER;
3493 t_FaIDList t_IdTable;
3494 t_PriorityList t_IdTable;
3495
3496 e_NoFAsFoundException exception;
3497 e_AddFAtoQException exception;
3498 e_AddAttributeException exception;
3499 e_CreateFAProcessException exception;
3500
3501 x_Progress VARCHAR2(2000);
3502
3503
3504 begin
3505
3506 l_Counter := 0;
3507
3508 l_OrderID := WF_engine.GetItemAttrNumber(itemtype => LaunchFAProcessSeq.itemtype,
3509 itemkey => LaunchFAProcessSeq.itemkey,
3510 aname => 'ORDER_ID');
3511
3512 l_WIInstanceID := WF_engine.GetItemAttrNumber(itemtype => LaunchFAProcessSeq.itemtype,
3513 itemkey => LaunchFAProcessSeq.itemkey,
3514 aname => 'WORKITEM_INSTANCE_ID');
3515
3516
3517 l_PrevFASeq := wf_engine.GetItemAttrNumber(itemtype => LaunchFAProcessSeq.itemtype,
3518 itemkey => LaunchFAProcessSeq.itemkey,
3519 aname => 'CURRENT_FA_SEQUENCE');
3520
3521 if c_FAList%ISOPEN then
3522 close c_FAList ;
3523 end if;
3524
3525 open c_FAList(l_WIInstanceID, l_PrevFASeq);
3526
3527 LOOP
3528 Fetch c_FAList into l_FAID, l_FAInstanceID, l_CurrentFASeq, l_Priority;
3529
3530 EXIT when c_FAList%NOTFOUND;
3531
3532 l_Counter := l_Counter + 1;
3533
3534 CreateFAProcess(itemtype, itemkey, l_FAInstanceID, l_WIInstanceID,
3535 l_OrderID, 'INTERNAL','WAITFORFLOW-FA-DEP',
3536 t_ChildTypes(l_Counter), t_childKeys(l_Counter),
3537 l_ErrCode, l_ErrDescription);
3538
3539 if l_ErrCode <> 0 then
3540 x_Progress := 'XDPCORE_FA.LaunchFAProcessSeq. Error when creating FA Process for Order: ' || l_OrderID || ' WorkitemInstanceID: ' || l_WIInstanceID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
3541 RAISE e_CreateFAProcessException;
3542 end if;
3543
3544 t_FaIdList(l_Counter) := l_FAInstanceID;
3545 t_PriorityList(l_Counter) := l_Priority;
3546
3547 END LOOP;
3548
3549 if c_FAList%ISOPEN then
3550 close c_FAList ;
3551 end if;
3552
3553
3554 if l_Counter = 0 and l_CurrentFASeq = 0 then
3555 x_Progress := 'XDPCORE_FA.LaunchFAProcess. No FA''s found to be processed for Order: ' || l_OrderID || ' WorkitemInstanceID: ' || l_WIInstanceID;
3556 RAISE e_NoFAsFoundException;
3557
3558 else
3559
3560 XDPCORE.CheckNAddItemAttrNumber (itemtype => itemtype,
3561 itemkey => itemkey,
3562 AttrName => 'CURRENT_FA_SEQUENCE',
3563 AttrValue => l_CurrentFASeq,
3564 ErrCode => l_ErrCode,
3565 ErrStr => l_ErrDescription);
3566
3567 if l_ErrCode <> 0 then
3568 x_progress := 'XDPCORE_FA.LaunchFAProcessSeq. Error when adding item attribute CURRENT_FA_SEQUENCE.';
3569 raise e_AddAttributeException;
3570 end if;
3571
3572 /* Update the XDP_FA_RUNTIME_LIST table with the User defined Workitem Item Type and Item Key */
3573
3574 update XDP_FA_RUNTIME_LIST
3575 set WF_ITEM_TYPE = t_ChildTypes(l_Counter),
3576 WF_ITEM_KEY = t_childKeys(l_Counter),
3577 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3578 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
3579 where FA_INSTANCE_ID = l_FAInstanceID;
3580
3581 FOR i in 1..l_Counter LOOP
3582 /* Enqueue all the FA's into the FA Queue */
3583
3584 XDP_AQ_UTILITIES.Add_FA_ToQ ( P_ORDER_ID => l_OrderID,
3585 P_WI_INSTANCE_ID => l_WIInstanceID,
3586 P_FA_INSTANCE_ID => t_FaIdList(i),
3587 P_WF_ITEM_TYPE => t_ChildTypes(i),
3588 P_WF_ITEM_KEY => t_childKeys(i),
3589 P_PRIORITY => t_PriorityList(i),
3590 P_RETURN_CODE => l_ErrCode,
3591 P_ERROR_DESCRIPTION => l_ErrDescription);
3592
3593 if l_ErrCode <> 0 then
3594 x_Progress := 'XDPCORE_FA.LaunchFAProcessSeq. Error When Adding FA: ' || t_FaIdList(i) || ' to Queue for Order: ' || l_OrderID || ' WorkitemInstanceID: ' || l_WIInstanceID || ' Error: ' || SUBSTR(l_ErrDescription, 1, 1500);
3595
3596 RAISE e_AddFAtoQException;
3597 end if;
3598
3599 END LOOP;
3600
3601 end if;
3602
3603
3604 exception
3605 when e_AddAttributeException then
3606 if c_FAList%ISOPEN then
3607 close c_FAList ;
3608 end if;
3609
3610 wf_core.context('XDPCORE_FA', 'LaunchFAProcessSeq', itemtype, itemkey, null, x_Progress);
3611 raise;
3612
3613 when e_NoFAsFoundException then
3614 if c_FAList%ISOPEN then
3615 close c_FAList ;
3616 end if;
3617
3618 wf_core.context('XDPCORE_FA', 'LaunchFAProcessSeq', itemtype, itemkey, null, x_Progress);
3619 raise;
3620
3621 when e_AddFAtoQException then
3622 if c_FAList%ISOPEN then
3623 close c_FAList ;
3624 end if;
3625
3626 wf_core.context('XDPCORE_FA', 'LaunchFAProcessSeq', itemtype, itemkey, null, x_Progress);
3627 raise;
3628
3629 when e_CreateFAProcessException then
3630 if c_FAList%ISOPEN then
3631 close c_FAList ;
3632 end if;
3633
3634 wf_core.context('XDPCORE_FA', 'LaunchFAProcessSeq', itemtype, itemkey, null, x_Progress);
3635 raise;
3636
3637 when others then
3638 if c_FAList%ISOPEN then
3639 close c_FAList ;
3640 end if;
3641
3642 x_Progress := 'XDPCORE_FA.LaunchFAProcessSeq. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
3643 wf_core.context('XDPCORE_FA', 'LaunchFAProcessSeq', itemtype, itemkey, null, x_Progress);
3644 raise;
3645 end LaunchFAProcessSeq;
3646
3647 -- This version of HandOver is used When the HandOver is called from the Adapter
3648 -- The Adapter calls this as part of the Initialization
3649
3650 Procedure HandOverChannel (p_ChannelName in varchar2,
3651 p_FeName in varchar2,
3652 p_ErrCode OUT NOCOPY number,
3653 p_ErrStr OUT NOCOPY varchar2)
3654 is
3655
3656 cursor c_AdapterJobQueue(p_FeID number, p_ChannelUsageCode varchar2) is
3657 select ROWID
3658 from XDP_ADAPTER_JOB_QUEUE
3659 where FE_ID = p_FeID
3660 and channel_usage_code = p_ChannelUsageCode
3661 and SYSTEM_HOLD = 'N'
3662 order by QUEUED_ON ASC;
3663 -- order by JOB_ID ASC;
3664
3665 resource_busy exception;
3666 pragma exception_init(resource_busy, -00054);
3667
3668 invalid_rowid exception;
3669 pragma exception_init(invalid_rowid, -01410);
3670 l_channellock BOOLEAN := FALSE ;
3671 l_channelstatus varchar2(1);
3672
3673 e_UnhandledException exception;
3674 e_UpdateAdapterRegException Exception;
3675 e_HandOverChannelException Exception;
3676
3677 idarr RowidArrayType;
3678 arrsize number;
3679 eligible boolean;
3680
3681 l_New_itemtype VARCHAR2(8);
3682 l_New_itemkey VARCHAR2(240);
3683 l_rowID VARCHAR2(80);
3684 l_FeName varchar2(80);
3685
3686 l_ChannelUsageCode VARCHAR2(40);
3687 l_Counter number := 0;
3688
3689 l_OrderID number;
3690 l_WIInstanceID number;
3691 l_FAInstanceID number;
3692 i NUMBER;
3693 l_FeID number;
3694 l_isLockRequired boolean;
3695
3696 l_ErrCode number;
3697 l_ErrStr varchar2(2000);
3698
3699 l_Status varchar2(1);
3700 x_Progress VARCHAR2(2000);
3701 x_parameters VARCHAR2(4000);
3702
3703 cursor c_GetFEid is
3704 select fe_id
3705 from xdp_fes
3706 where fulfillment_element_name = p_FeName;
3707
3708 cursor c_GetUsageCode is
3709 select USAGE_CODE
3710 from xdp_adapter_reg
3711 where channel_name = p_ChannelName;
3712
3713 BEGIN
3714
3715 p_ErrCode := 0;
3716
3717 -- Get FE ID first..
3718 for v_GetFeId in c_GetFEid loop
3719 l_FeID := v_GetFeId.fe_id;
3720 end loop;
3721
3722 -- Get the Channel Usage Code
3723 for v_GetUsageCode in c_GetUsageCode loop
3724 l_ChannelUsageCode := v_GetUsageCode.usage_code;
3725 end loop;
3726
3727 -- 11.5.6 On wards
3728 -- First Release the Lock. as of 11.5.6 the FA Fulfillment process will not
3729 -- Check for any waiting Adapter Admin Requests. The admin request will be a DBMS
3730 -- JOB and the job will wait on the Named Channel Lock.
3731 -- So when the lock is released the dbms_job will acquire the lock and perform the
3732 -- ADMIN operation.
3733 -- The actual handover process will try to re-acquire the lock again. If there are
3734 -- no DBMS Jobs waiting then the channel handover will be performed. Else the hand over
3735 -- will not be performed.
3736 --
3737 --
3738
3739 -- 11.5.6 On wards
3740 -- Try to acquire the lock
3741 --
3742 -- skilaru 05/16/2002
3743 -- ER# 2013681 Lock the channel only if it is of application mode PIPE..
3744
3745 l_isLockRequired := XDP_ADAPTER_CORE_DB.checkLockRequired( HandOverChannel.p_ChannelName );
3746
3747 IF( l_isLockRequired ) THEN
3748
3749 l_Status := XDP_ADAPTER_CORE_DB.ObtainAdapterLock_FA
3750 (p_ChannelName => HandOverChannel.p_ChannelName);
3751 END IF;
3752
3753 if l_Status = 'N' AND l_isLockRequired then
3754 -- Could not Acquire Lock. An admin job is being performed.
3755 -- Just exit.
3756 return;
3757 else l_channellock := TRUE ;
3758
3759 end if;
3760
3761
3762 /* Check if there are any jobs for this particular NE to be Done */
3763
3764 /* First Fetch All the Eligible ROW ID's into a table.
3765 * Then try to obtain a lock on one of these (select for update with no wait)
3766 * When u get a lock process it.
3767 */
3768
3769 arrsize := 0;
3770
3771 /* Fetch all the rows */
3772
3773 OPEN c_AdapterJobQueue(l_FeID, l_ChannelUsageCode);
3774 LOOP
3775
3776 FETCH c_AdapterJobQueue into l_rowID;
3777 EXIT WHEN c_AdapterJobQueue%NOTFOUND;
3778
3779 arrsize := arrsize + 1;
3780 idarr(arrsize) := l_rowID;
3781 END LOOP;
3782
3783 IF c_AdapterJobQueue%ISOPEN THEN
3784 close c_AdapterJobQueue;
3785 END IF;
3786
3787 /* Got all the rows Process them */
3788 If arrsize > 0 THEN -- {
3789
3790 for i in 1 .. arrsize loop -- {
3791
3792 /*
3793 * Try to get a lock on each of the fetched rows. If possible
3794 * Process them. If you cannot get the lock go to the next row
3795 */
3796 BEGIN
3797 select WF_ITEM_TYPE, WF_ITEM_KEY, ROWID, ORDER_ID, WORKITEM_INSTANCE_ID, FA_INSTANCE_ID
3798 into l_New_itemtype, l_New_itemkey, l_rowID, l_OrderID, l_WIInstanceID, l_FAInstanceID
3799 from XDP_ADAPTER_JOB_QUEUE
3800 where ROWID = idarr(i)
3801 for update NOWAIT;
3802
3803 /* Check if the FA Waiting has been CANCELED
3804 * If so the FA must actually be deleted from the adapter job queue
3805 */
3806
3807 if IsFAAborted(l_FAInstanceID) = FALSE then
3808 eligible := TRUE;
3809 l_Counter := l_Counter + 1;
3810 GOTO l_EndofLoop;
3811 else
3812 eligible := FALSE;
3813
3814 delete from XDP_ADAPTER_JOB_QUEUE
3815 where ROWID = l_rowid;
3816 end if;
3817
3818 EXCEPTION
3819 WHEN resource_busy or no_data_found THEN
3820 /* Could not obtain a lock */
3821 eligible := FALSE;
3822 WHEN invalid_rowid then
3823 /* By the time the list of FA's was selected into the array. Some other dqer
3824 * had obtained a lock and already done the hand over process
3825 */
3826 eligible := FALSE;
3827 WHEN OTHERS THEN
3828 RAISE;
3829 END;
3830
3831 END LOOP; -- }
3832
3833 <<l_EndofLoop>>
3834 IF (eligible) THEN -- {
3835 BEGIN
3836
3837 SAVEPOINT HandOverPoint;
3838
3839 delete from XDP_ADAPTER_JOB_QUEUE
3840 where ROWID = l_rowid;
3841
3842 /* Need to Enqueue in the AQ */
3843
3844 -- skilaru 05/16/2002 ER# 2013681
3845 -- If we dont lock the channel then the status of the adapter is
3846 -- always Running. Dont update to In Use
3847
3848 IF( l_isLockRequired ) THEN
3849
3850 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
3851 p_ChannelName => p_ChannelName,
3852 p_Status => XDP_ADAPTER.pv_statusInUse
3853 );
3854
3855 END IF;
3856
3857 XDP_AQ_UTILITIES.HANDOVER_CHANNEL( p_ChannelName,
3858 p_FeName,
3859 l_New_itemtype,
3860 l_New_itemkey,
3861 'WAIT_IN_FP_QUEUE',
3862 l_OrderID,
3863 l_WIInstanceID,
3864 l_FAInstanceID,
3865 p_ErrCode,
3866 p_ErrStr);
3867
3868
3869 if p_ErrCode <> 0 then
3870 x_Progress := 'XDPCORE_FA.HandOverChannel. Exception when handing Over Channel: ' || p_ChannelName
3871 || ' for FE: ' || p_FeName || ' to itemtype: ' || l_New_itemtype || ' with itemkey: '
3872 || l_New_itemkey || ' Current Order: ' || l_OrderID || ' WIInstanceID: ' || l_WIInstanceID
3873 || ' FA InstanceID: ' || l_FAInstanceID || ' Error: ' || SUBSTR(p_ErrStr, 1, 1000);
3874
3875 RAISE e_HandOverChannelException;
3876 end if;
3877
3878 EXCEPTION
3879 WHEN OTHERS THEN
3880 /** Raja: Added on 05-Oct-1999 **/
3881 rollback to HandOverPoint;
3882
3883 x_ErrorID := -1;
3884
3885 x_parameters := 'PROC_NAME='||'HAND_OVER'||'#XDP#'||
3886 'CHANNEL_NAME='||p_ChannelName||'#XDP#'||
3887 'FE_NAME='||p_FeName||'#XDP#'||
3888 'ERROR_STRING='||substr(x_progress,1,1500)||'#XDP#';
3889
3890
3891 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
3892 p_ChannelName => p_ChannelName,
3893 p_Status => XDP_ADAPTER.pv_statusError,
3894 p_ErrorMsg => 'XDP_INTERNAL_CHANNEL_ERROR',
3895 p_ErrorMsgParams => x_parameters
3896 );
3897
3898 RAISE e_UnhandledException;
3899 END;
3900
3901 END IF; -- }
3902 END IF; -- arrasize -- }
3903 -- ELSE -- arrasize > 0 {
3904
3905 if l_Counter = 0 or arrsize = 0 then -- {
3906 /* No jobs found */
3907
3908 BEGIN
3909
3910 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
3911 p_ChannelName => p_ChannelName,
3912 p_Status => XDP_ADAPTER.pv_statusRunning
3913 );
3914
3915
3916 EXCEPTION
3917 WHEN OTHERS THEN
3918 x_Progress := 'XDPCORE_FA.HandOverChannel. Exception when updating the Adapter Registration to be IDLE for Channel: '
3919 || p_ChannelName || ' for FE: ' || l_FeID || ' Current Order: ' || l_OrderID || ' WIInstanceID: '
3920 || l_WIInstanceID || ' FA InstanceID: ' || l_FAInstanceID || ' Error: ' || SUBSTR(SQLERRM,1,500);
3921
3922 RAISE e_UpdateAdapterRegException;
3923 END;
3924
3925 END IF; -- }
3926
3927 IF c_AdapterJobQueue%ISOPEN THEN
3928 Close c_AdapterJobQueue;
3929 END IF;
3930
3931
3932 p_ErrCode := 0;
3933 p_ErrStr := 'No Errors';
3934
3935 -- 11.5.6 On-Wards..
3936 -- Release Lock...
3937
3938 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
3939 (p_ChannelName => HandOverChannel.p_ChannelName);
3940
3941 exception
3942 when e_UnhandledException then
3943 IF c_AdapterJobQueue%ISOPEN THEN
3944 close c_AdapterJobQueue;
3945 END IF;
3946 IF l_channellock THEN
3947 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => HandOverChannel.p_ChannelName);
3948 END IF ;
3949
3950 x_Progress := 'XDPCORE_FA.HandOverChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
3951 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
3952 raise;
3953
3954 when e_UpdateAdapterRegException then
3955 IF c_AdapterJobQueue%ISOPEN THEN
3956 close c_AdapterJobQueue;
3957 END IF;
3958
3959 IF l_channellock THEN
3960 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => HandOverChannel.p_ChannelName);
3961 END IF ;
3962
3963 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
3964 raise;
3965
3966 when e_HandOverChannelException then
3967 IF c_AdapterJobQueue%ISOPEN THEN
3968 close c_AdapterJobQueue;
3969 END IF;
3970
3971 IF l_channellock THEN
3972 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => HandOverChannel.p_ChannelName);
3973 END IF ;
3974
3975 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
3976 raise;
3977
3978 when others then
3979 IF c_AdapterJobQueue%ISOPEN THEN
3980 close c_AdapterJobQueue;
3981 END IF;
3982
3983 IF l_channellock THEN
3984 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => HandOverChannel.p_ChannelName);
3985 END IF ;
3986
3987 x_Progress := 'XDPCORE_FA.HandOverChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
3988 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
3989 raise;
3990 end HandOverChannel;
3991
3992
3993 -- This version of HandOver is used from within the FA Workflow Execution
3994 -- There is a release of the Channel Before any other operations are performed
3995 -- on the channel.
3996
3997 Procedure HandOverChannel (ChannelName in varchar2,
3998 FeID in number,
3999 ChannelUsageCode in varchar2,
4000 Caller in varchar2,
4001 ErrCode OUT NOCOPY number,
4002 ErrStr OUT NOCOPY varchar2)
4003 is
4004
4005 cursor c_AdapterJobQueue(p_FeID number, p_ChannelUsageCode varchar2) is
4006 select ROWID
4007 from XDP_ADAPTER_JOB_QUEUE
4008 where FE_ID = p_FeID
4009 and channel_usage_code = p_ChannelUsageCode
4010 and SYSTEM_HOLD = 'N'
4011 order by QUEUED_ON ASC;
4012 --order by JOB_ID ASC;
4013
4014 resource_busy exception;
4015 pragma exception_init(resource_busy, -00054);
4016
4017 invalid_rowid exception;
4018 pragma exception_init(invalid_rowid, -01410);
4019
4020 e_UnhandledException exception;
4021 e_UpdateAdapterRegException Exception;
4022 e_HandOverChannelException Exception;
4023
4024 idarr RowidArrayType;
4025 arrsize number;
4026 eligible boolean;
4027 l_channellock BOOLEAN := FALSE ;
4028 l_channelstatus varchar2(1) ;
4029
4030 l_New_itemtype VARCHAR2(8);
4031 l_New_itemkey VARCHAR2(240);
4032 l_rowID VARCHAR2(80);
4033 l_FeName varchar2(80);
4034
4035 l_ChannelUsageCode VARCHAR2(40);
4036 l_Counter number := 0;
4037
4038 l_OrderID number;
4039 l_WIInstanceID number;
4040 l_FAInstanceID number;
4041 i NUMBER;
4042 l_FeID number;
4043 l_isLockRequired boolean;
4044
4045
4046 l_ErrCode number;
4047 l_ErrStr varchar2(2000);
4048
4049 l_Status varchar2(1);
4050
4051 x_Progress VARCHAR2(2000);
4052 x_parameters VARCHAR2(4000);
4053
4054
4055 BEGIN
4056
4057 ErrCode := 0;
4058
4059
4060 -- 11.5.6 On wards
4061 -- First Release the Lock. as of 11.5.6 the FA Fulfillment process will not
4062 -- Check for any waiting Adapter Admin Requests. The admin request will be a DBMS
4063 -- JOB and the job will wait on the Named Channel Lock.
4064 -- So when the lock is released the dbms_job will acquire the lock and perform the
4065 -- ADMIN operation.
4066 -- The actual handover process will try to re-acquire the lock again. If there are
4067 -- no DBMS Jobs waiting then the channel handover will be performed. Else the hand over
4068 -- will not be performed.
4069 --
4070 --
4071
4072 -- TO DO ERROR HANDLING!!
4073 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
4074 (p_ChannelName => HandOverChannel.ChannelName);
4075
4076
4077 /* This part of the Code is to support the Adapter's hand over process.
4078 ** The adapter does not have the FE_ID in its context.
4079 ** If and interactive adapter detects a session lost it may automatically try to
4080 ** Re connect to the FE. If it successfully reconnects the adapter should do the normal
4081 ** handover process. As the Adapter does not have the FE ID in its context it passes a 0
4082 ** to this procedure.
4083 ** Upon detectinf a 0 the fe_id associated with the channel name is found and the handover
4084 ** process is carried out.
4085 */
4086
4087 if FeID = 0 then
4088 select fe_id, usage_code
4089 into l_FeID, l_ChannelUsageCode
4090 from XDP_ADAPTER_REG
4091 where channel_name = ChannelName;
4092 else
4093 l_FeID := FeID;
4094 l_ChannelUsageCode := ChannelUsageCode;
4095 end if;
4096
4097
4098 -- 11.5.6 On wards
4099 -- Try to re-acquire the lock
4100 --
4101 -- skilaru 05/16/2002
4102 -- ER# 2013681 Lock the channel only if it is of application mode PIPE..
4103
4104 l_isLockRequired := XDP_ADAPTER_CORE_DB.checkLockRequired( HandOverChannel.ChannelName );
4105
4106 IF( l_isLockRequired ) THEN
4107
4108 l_Status := XDP_ADAPTER_CORE_DB.ObtainAdapterLock_FA
4109 (p_ChannelName => HandOverChannel.ChannelName);
4110 END IF;
4111
4112 if l_Status = 'N' then
4113 -- Could not Acquire Lock. An admin job is being performed.
4114 -- Just exit.
4115 return;
4116 else
4117 l_channellock := TRUE;
4118 end if;
4119
4120
4121 /* Check if there are any jobs for this particular NE to be Done */
4122
4123 /* First Fetch All the Eligible ROW ID's into a table.
4124 * Then try to obtain a lock on one of these (select for update with no wait)
4125 * When u get a lock process it.
4126 */
4127
4128 arrsize := 0;
4129
4130 /* Fetch all the rows */
4131
4132 OPEN c_AdapterJobQueue(l_FeID, l_ChannelUsageCode);
4133 LOOP
4134
4135 FETCH c_AdapterJobQueue into l_rowID;
4136 EXIT WHEN c_AdapterJobQueue%NOTFOUND;
4137
4138 arrsize := arrsize + 1;
4139 idarr(arrsize) := l_rowID;
4140 END LOOP;
4141
4142 IF c_AdapterJobQueue%ISOPEN THEN
4143 close c_AdapterJobQueue;
4144 END IF;
4145
4146 /* Got all the rows Process them */
4147 If arrsize > 0 THEN -- {
4148
4149 for i in 1 .. arrsize loop -- {
4150
4151 /*
4152 * Try to get a lock on each of the fetched rows. If possible
4153 * Process them. If you cannot get the lock go to the next row
4154 */
4155 BEGIN
4156 select WF_ITEM_TYPE, WF_ITEM_KEY, ROWID, ORDER_ID, WORKITEM_INSTANCE_ID, FA_INSTANCE_ID
4157 into l_New_itemtype, l_New_itemkey, l_rowID, l_OrderID, l_WIInstanceID, l_FAInstanceID
4158 from XDP_ADAPTER_JOB_QUEUE
4159 where ROWID = idarr(i)
4160 for update NOWAIT;
4161
4162 /* Check if the FA Waiting has been CANCELED
4163 * If so the FA must actually be deleted from the adapter job queue
4164 */
4165
4166 if IsFAAborted(l_FAInstanceID) = FALSE then
4167 eligible := TRUE;
4168 l_Counter := l_Counter + 1;
4169 GOTO l_EndofLoop;
4170 else
4171 eligible := FALSE;
4172
4173 delete from XDP_ADAPTER_JOB_QUEUE
4174 where ROWID = l_rowid;
4175 end if;
4176
4177 EXCEPTION
4178 WHEN resource_busy or no_data_found THEN
4179 /* Could not obtain a lock */
4180 eligible := FALSE;
4181 WHEN invalid_rowid then
4182 /* By the time the list of FA's was selected into the array. Some other dqer
4183 * had obtained a lock and already done the hand over process
4184 */
4185 eligible := FALSE;
4186 WHEN OTHERS THEN
4187 RAISE;
4188 END;
4189
4190 END LOOP; -- }
4191
4192 <<l_EndofLoop>>
4193 IF (eligible) THEN -- {
4194 BEGIN
4195
4196 SAVEPOINT HandOverPoint;
4197
4198 delete from XDP_ADAPTER_JOB_QUEUE
4199 where ROWID = l_rowid;
4200
4201 /* Need to Enqueue in the AQ */
4202
4203 select FULFILLMENT_ELEMENT_NAME into l_FeName
4204 from XDP_FES
4205 where FE_ID = l_FeID;
4206
4207 if Caller = 'ADMIN' then
4208
4209 -- skilaru 05/16/2002 ER# 2013681
4210 -- If we dont lock the channel then the status of the adapter is
4211 -- always Running. Dont update to In Use
4212
4213 IF( l_isLockRequired ) THEN
4214
4215 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
4216 p_ChannelName => ChannelName,
4217 p_Status => XDP_ADAPTER.pv_statusInUse
4218 );
4219 END IF;
4220
4221 end if;
4222
4223 XDP_AQ_UTILITIES.HANDOVER_CHANNEL( ChannelName,
4224 l_FeName,
4225 l_New_itemtype,
4226 l_New_itemkey,
4227 'WAIT_IN_FP_QUEUE',
4228 l_OrderID,
4229 l_WIInstanceID,
4230 l_FAInstanceID,
4231 ErrCode,
4232 ErrStr);
4233
4234
4235 if ErrCode <> 0 then
4236 x_Progress := 'XDPCORE_FA.HandOverChannel. Exception when handing Over Channel: ' || ChannelName
4237 || ' for FE: ' || l_FeID || ' to itemtype: ' || l_New_itemtype || ' with itemkey: '
4238 || l_New_itemkey || ' Current Order: ' || l_OrderID || ' WIInstanceID: ' || l_WIInstanceID
4239 || ' FA InstanceID: ' || l_FAInstanceID || ' Error: ' || SUBSTR(ErrStr, 1, 1000);
4240
4241 RAISE e_HandOverChannelException;
4242 end if;
4243
4244 EXCEPTION
4245 WHEN OTHERS THEN
4246 /** Raja: Added on 05-Oct-1999 **/
4247 rollback to HandOverPoint;
4248
4249 x_ErrorID := -1;
4250
4251 x_parameters := 'PROC_NAME='||'HAND_OVER'||'#XDP#'||
4252 'CHANNEL_NAME='||ChannelName||'#XDP#'||
4253 'FE_NAME='||l_FeName||'#XDP#'||
4254 'ERROR_STRING='||substr(x_progress,1,1500)||'#XDP#';
4255
4256
4257 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
4258 p_ChannelName => ChannelName,
4259 p_Status => XDP_ADAPTER.pv_statusError,
4260 p_ErrorMsg => 'XDP_INTERNAL_CHANNEL_ERROR',
4261 p_ErrorMsgParams => x_parameters
4262 );
4263
4264 RAISE e_UnhandledException;
4265 END;
4266
4267 END IF; -- }
4268 END IF; -- arrasize -- }
4269 -- ELSE -- arrasize > 0 {
4270
4271 if l_Counter = 0 or arrsize = 0 then -- {
4272 /* No jobs found */
4273
4274 BEGIN
4275
4276 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
4277 p_ChannelName => ChannelName,
4278 p_Status => XDP_ADAPTER.pv_statusRunning
4279 );
4280
4281
4282 EXCEPTION
4283 WHEN OTHERS THEN
4284 x_Progress := 'XDPCORE_FA.HandOverChannel. Exception when updating the Adapter Registration to be IDLE for Channel: '
4285 || ChannelName || ' for FE: ' || l_FeID || ' Current Order: ' || l_OrderID || ' WIInstanceID: '
4286 || l_WIInstanceID || ' FA InstanceID: ' || l_FAInstanceID || ' Error: ' || SUBSTR(SQLERRM,1,500);
4287
4288 RAISE e_UpdateAdapterRegException;
4289 END;
4290
4291 END IF; -- }
4292
4293 IF c_AdapterJobQueue%ISOPEN THEN
4294 Close c_AdapterJobQueue;
4295 END IF;
4296
4297
4298 ErrCode := 0;
4299 ErrStr := 'No Errors';
4300
4301 -- 11.5.6 On-Wards..
4302 -- Release Lock...
4303
4304 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
4305 (p_ChannelName => HandOverChannel.ChannelName);
4306
4307 exception
4308 when e_UnhandledException then
4309 IF c_AdapterJobQueue%ISOPEN THEN
4310 close c_AdapterJobQueue;
4311 END IF;
4312
4313 IF l_channellock THEN
4314 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock(p_ChannelName => HandOverChannel.ChannelName);
4315 END IF ;
4316
4317 x_Progress := 'XDPCORE_FA.HandOverChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
4318 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
4319 raise;
4320
4321 when e_UpdateAdapterRegException then
4322 IF c_AdapterJobQueue%ISOPEN THEN
4323 close c_AdapterJobQueue;
4324 END IF;
4325
4326 IF l_channellock THEN
4327 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock(p_ChannelName => HandOverChannel.ChannelName);
4328 END IF ;
4329
4330 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
4331 raise;
4332
4333 when e_HandOverChannelException then
4334 IF c_AdapterJobQueue%ISOPEN THEN
4335 close c_AdapterJobQueue;
4336 END IF;
4337
4338 IF l_channellock THEN
4339 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock(p_ChannelName => HandOverChannel.ChannelName);
4340 END IF ;
4341
4342 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
4343 raise;
4344
4345 when others then
4346 IF c_AdapterJobQueue%ISOPEN THEN
4347 close c_AdapterJobQueue;
4348 END IF;
4349
4350 IF l_channellock THEN
4351 l_channelstatus := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock(p_ChannelName => HandOverChannel.ChannelName);
4352 END IF ;
4353
4354 x_Progress := 'XDPCORE_FA.HandOverChannel. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
4355 wf_core.context('XDPCORE_FA', 'HandOverChannel', null, null, x_Progress,null);
4356 raise;
4357 end HandOverChannel;
4358
4359 Procedure FindAdminRequestAndPublish (ChannelName in varchar2,
4360 RequestFound OUT NOCOPY BOOLEAN,
4361 Request OUT NOCOPY varchar2)
4362
4363 is
4364 cursor c_AdapterAdmin(ChannelName varchar2) is
4365 select REQUEST_ID, REQUEST_TYPE
4366 from XDP_ADAPTER_ADMIN_REQS
4367 where CHANNEL_NAME = ChannelName and
4368 REQUEST_STATUS = 'PENDING' and
4369 REQUEST_TYPE IN ('SUSPEND', 'DISCONNECT', 'SHUTDOWN') and
4370 REQUEST_DATE = (
4371 select MIN(REQUEST_DATE)
4372 from XDP_ADAPTER_ADMIN_REQS
4373 where CHANNEL_NAME = ChannelName and
4374 REQUEST_STATUS = 'PENDING' and
4375 REQUEST_TYPE IN ('SUSPEND', 'DISCONNECT', 'SHUTDOWN') and
4376 REQUEST_DATE <= SYSDATE );
4377
4378 l_RequestID number;
4379 l_ErrCode number;
4380 l_dummy number;
4381
4382 l_ErrDescription varchar2(4000);
4383 x_Progress VARCHAR2(2000);
4384
4385 e_SendMessageException exception;
4386 begin
4387
4388 Request := 'FALSE';
4389
4390 if c_AdapterAdmin%ISOPEN then
4391 close c_AdapterAdmin;
4392 end if;
4393
4394
4395 open c_AdapterAdmin(ChannelName);
4396 Fetch c_AdapterAdmin into l_RequestID, Request;
4397
4398 if c_AdapterAdmin%NOTFOUND then
4399 /* No Jobs Found */
4400 close c_AdapterAdmin;
4401
4402 RequestFound := FALSE;
4403 return;
4404 else
4405 if c_AdapterAdmin%FOUND then
4406 /* Job Found. Publish an Event to Execute the task */
4407 close c_AdapterAdmin;
4408
4409 /* Construct The CallbackProcedure */
4410
4411 XNP_ADAPTER_ADMIN_U.Publish(XNP$REQUEST_TYPE => Request,
4412 XNP$REQUEST_ID => to_char(l_RequestID),
4413 X_MESSAGE_ID => l_dummy,
4414 X_ERROR_CODE => l_ErrCode,
4415 X_ERROR_MESSAGE =>l_ErrDescription,
4416 P_CONSUMER_LIST => null,
4417 P_SENDER_NAME => null,
4418 P_RECIPIENT_LIST => null,
4419 P_VERSION => null,
4420 P_REFERENCE_ID => null,
4421 P_OPP_REFERENCE_ID => null,
4422 P_ORDER_ID => null,
4423 P_WI_INSTANCE_ID => null,
4424 P_FA_INSTANCE_ID=> null);
4425
4426 if l_ErrCode <> 0 then
4427 x_progress := 'Error when trying to send ADAPTER_ADMIN Message. Code: ' ||
4428 to_char(l_ErrCode) || ' Error String: ' ||
4429 substr(l_ErrDescription, 1, 1000);
4430 Raise e_SendMessageException;
4431 end if;
4432 RequestFound := TRUE;
4433 return;
4434 end if;
4435 end if;
4436
4437 exception
4438 when e_SendMessageException then
4439 if c_AdapterAdmin%ISOPEN then
4440 close c_AdapterAdmin;
4441 end if;
4442
4443 wf_core.context('XDPCORE_FA', 'FindAdminRequestAndPublish', null, null, null, x_progress);
4444 raise;
4445 when others then
4446 if c_AdapterAdmin%ISOPEN then
4447 close c_AdapterAdmin;
4448 end if;
4449
4450 x_Progress := 'XDPCORE_FA.FindAdminRequestAndPublish. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
4451 wf_core.context('XDPCORE_FA', 'FindAdminRequestAndPublish', null, null, x_Progress,null);
4452 raise;
4453 end FindAdminRequestAndPublish;
4454
4455
4456 Procedure GetWorkitemFAMappingProc(WIInstanceID in number,
4457 MappingProcFound OUT NOCOPY varchar2,
4458 MappingProc OUT NOCOPY varchar2 )
4459 is
4460 begin
4461 MappingProcFound := 'N';
4462 begin
4463 select XDW.FA_EXEC_MAP_PROC
4464 into MappingProc
4465 from XDP_WORKITEMS XDW, XDP_FULFILL_WORKLIST XFW
4466 where XFW.WORKITEM_INSTANCE_ID = WIInstanceID
4467 and XDW.FA_EXEC_MAP_PROC is not null
4468 and XFW.WORKITEM_ID = XDW.WORKITEM_ID;
4469
4470 MappingProcFound := 'Y';
4471 exception
4472 when no_data_found then
4473 MappingProcFound := 'N';
4474 MappingProc := NULL;
4475 when others then
4476 RAISE ;
4477 end;
4478 exception
4479 when others then
4480 raise;
4481 end GetWorkitemFAMappingProc;
4482
4483
4484 Procedure PopulateFAs (WIInstanceID in number)
4485 is
4486 cursor c_GetFaList (wi_id number) is
4487 select XWF.FULFILLMENT_ACTION_ID, XWF.PROVISIONING_SEQ
4488 from XDP_WI_FA_MAPPING XWF, XDP_FULFILL_WORKLIST XFW
4489 where XFW.WORKITEM_INSTANCE_ID = wi_id
4490 and XFW.WORKITEM_ID = XWF.WORKITEM_ID;
4491
4492 l_FAInstanceID number;
4493 l_FAsFound number := 0;
4494 lv_wi_name varchar2(100);
4495 lv_message_params varchar2(500);
4496 e_NoFAsFound exception;
4497 begin
4498
4499 if c_GetFaList%ISOPEN then
4500 close c_GetFaList;
4501 end if;
4502
4503 /* Populate all the FA's in the FA_RUNTIME_LIST Table */
4504 For v_GetFaList in c_GetFaList(WIInstanceID) LOOP
4505 BEGIN
4506 -- skilaru 04/30/2002 bug # 2349463
4507 -- modified the call to not pass NULL values and use defaults instead
4508 l_FAInstanceID :=
4509 XDP_ENG_UTIL.Add_FA_ToWI ( P_WI_INSTANCE_ID => WIInstanceID,
4510 P_FULFILLMENT_ACTION_ID => v_GetFaList.FULFILLMENT_ACTION_ID,
4511 P_PROVISIONING_SEQ => v_GetFaList.PROVISIONING_SEQ);
4512 EXCEPTION
4513 WHEN others THEN
4514 raise;
4515 END;
4516 l_FAsFound := 1;
4517 END LOOP;
4518
4519 if l_FAsFound = 0 then
4520 raise e_NoFAsFound;
4521 end if;
4522 exception
4523 when others then
4524 xdpcore.context( 'XDPCORE_FA', 'PopulateFAs', 'WI',WIInstanceID);
4525 if c_GetFaList%ISOPEN then
4526 close c_GetFaList;
4527 end if;
4528 raise;
4529 end PopulateFAs;
4530
4531 Function GetLocateFEProc (FAInstanceID in number) return varchar2
4532 is
4533
4534 cursor c_GetLocateFEProc(FaID number) is
4535 select XFA.FE_ROUTING_PROC
4536 from XDP_FULFILL_ACTIONS XFA, XDP_FA_RUNTIME_LIST XFL
4537 where XFL.FA_INSTANCE_ID = FaID
4538 and XFA.FULFILLMENT_ACTION_ID = XFL.FULFILLMENT_ACTION_ID;
4539
4540 l_LocateFEProc varchar2(80);
4541
4542 begin
4543 if c_GetLocateFEProc%ISOPEN then
4544 close c_GetLocateFEProc;
4545 end if;
4546
4547 open c_GetLocateFEProc(FAInstanceID);
4548 Fetch c_GetLocateFEProc into l_LocateFEProc;
4549
4550 if c_GetLocateFEProc%NOTFOUND then
4551 l_LocateFEProc := NULL;
4552 end if;
4553 close c_GetLocateFEProc;
4554
4555 return (l_LocateFEProc);
4556
4557 exception
4558 when others then
4559 if c_GetLocateFEProc%ISOPEN then
4560 close c_GetLocateFEProc;
4561 end if;
4562 raise;
4563 end GetLocateFEProc;
4564
4565 Function GetFPProc (FAInstanceID in number, FeTypeID in number,
4566 FeSwGeneric in varchar2, AdapterType in varchar2)
4567 return varchar2
4568 is
4569 cursor c_GetFP(FaID number, FeTypeID number, FeSWGeneric varchar2, AdapterType varchar2) is
4570 select XFP.FULFILLMENT_PROC
4571 from XDP_FA_FULFILLMENT_PROC XFP, XDP_FA_RUNTIME_LIST XFL, XDP_FE_SW_GEN_LOOKUP XFS
4572 where XFL.FA_INSTANCE_ID = FaID
4573 and XFP.FULFILLMENT_ACTION_ID = XFL.FULFILLMENT_ACTION_ID
4574 and XFP.FE_SW_GEN_LOOKUP_ID = XFS.FE_SW_GEN_LOOKUP_ID
4575 and XFS.FETYPE_ID = FeTypeID
4576 and XFS.SW_GENERIC = FeSWGeneric
4577 and XFS.ADAPTER_TYPE = AdapterType;
4578
4579 l_FaProvProc varchar2(80);
4580 begin
4581
4582 /* Get the FP Name */
4583 if c_GetFP%ISOPEN then
4584 close c_GetFP;
4585 end if;
4586
4587 open c_GetFP(FAInstanceID, FeTypeID, FeSwGeneric, AdapterType);
4588
4589 Fetch c_GetFP into l_FaProvProc;
4590 if c_GetFP%NOTFOUND then
4591 l_FaProvProc := NULL;
4592 end if;
4593
4594 close c_GetFP;
4595
4596 return(l_FaProvProc);
4597 exception
4598 when others then
4599 if c_GetFP%ISOPEN then
4600 close c_GetFP;
4601 end if;
4602 raise;
4603 end GetFPProc;
4604
4605 Procedure IsFEPreDefined(FAInstanceID in number,
4606 ConfigError OUT NOCOPY varchar2,
4607 FEID OUT NOCOPY number)
4608 is
4609 cursor c_CheckFeID (FaID number) is
4610 select NVL(FE_ID, null)
4611 from XDP_FA_RUNTIME_LIST
4612 where FA_INSTANCE_ID = FaID;
4613 begin
4614 ConfigError := 'N';
4615
4616 if c_CheckFeID%ISOPEN then
4617 close c_CheckFeID;
4618 end if;
4619
4620 open c_CheckFeID(FAInstanceID);
4621 Fetch c_CheckFeID into FeID;
4622
4623 if c_CheckFeID%NOTFOUND then
4624 ConfigError := 'Y';
4625 end if;
4626
4627
4628 exception
4629 when others then
4630 if c_CheckFeID%ISOPEN then
4631 close c_CheckFeID;
4632 end if;
4633
4634 raise;
4635 end IsFePreDefined;
4636
4637 Procedure SearchAndLockChannel( p_FEID in number,
4638 p_ChannelUsageCode in varchar2,
4639 p_CODFlag in varchar2,
4640 p_AdapterStatus in varchar2,
4641 x_LockedFlag OUT NOCOPY varchar2,
4642 x_ChannelName OUT NOCOPY varchar2)
4643
4644 is
4645
4646 cursor c_GetChannel(feid number, ChannelUsageCode varchar2,
4647 AdapterStatus varchar2) is
4648 select ROWID, channel_name
4649 from XDP_ADAPTER_REG
4650 where FE_ID = feid
4651 and ADAPTER_STATUS = AdapterStatus
4652 and USAGE_CODE = ChannelUsageCode
4653 order by NVL(SEQ_IN_FE, 10);
4654
4655 cursor c_GetCODChannel(feid number, ChannelUsageCode varchar2,
4656 AdapterStatus varchar2, CODFlag varchar2) is
4657 select ROWID, channel_name
4658 from XDP_ADAPTER_REG
4659 where FE_ID = feid
4660 and ADAPTER_STATUS = AdapterStatus
4661 and NVL(CONNECT_ON_DEMAND_FLAG, 'N') = CODFlag
4662 and USAGE_CODE = ChannelUsageCode
4663 order by NVL(SEQ_IN_FE, 10);
4664
4665 resource_busy exception;
4666 pragma exception_init(resource_busy, -00054);
4667
4668 l_RowID ROWID;
4669 l_RowIDArray RowidArrayType;
4670
4671 l_GotChannelFlag varchar2(1) := 'N';
4672 l_Counter number := 0;
4673
4674 l_Status varchar2(1) := 'N';
4675 begin
4676
4677 x_ChannelName := NULL;
4678 x_LockedFlag := 'N';
4679
4680 if p_CODFlag = 'N' then
4681 FOR v_GetChannel in c_GetChannel(p_FeID, p_ChannelUsageCode,
4682 p_AdapterStatus) LOOP
4683
4684 -- skilaru 05/16/2002
4685 -- ER# 2013681 Lock the channel only if it is of application mode PIPE..
4686
4687 IF( XDP_ADAPTER_CORE_DB.checkLockRequired( v_GetChannel.channel_name ) ) THEN
4688 l_Status := XDP_ADAPTER_CORE_DB.ObtainAdapterLock_FA(v_GetChannel.channel_name);
4689 ELSE
4690 -- skilaru 05/16/2002
4691 -- IF it is NON-PIPE based channel we dont need a lock at all..
4692 -- so mimic as if we acuired a lock..
4693 x_LockedFlag := 'Y';
4694 x_ChannelName := v_GetChannel.channel_name;
4695 return;
4696 END IF;
4697
4698 if l_Status = 'Y' then
4699
4700 x_LockedFlag := 'Y';
4701 x_ChannelName := v_GetChannel.channel_name;
4702
4703 if XDP_ADAPTER_CORE_DB.Verify_Adapter (v_GetChannel.channel_name) then
4704 return;
4705 else
4706 -- Release the Lock
4707 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
4708 (p_ChannelName => v_GetChannel.channel_name);
4709 x_LockedFlag := 'N';
4710 x_ChannelName := NULL;
4711 END IF;
4712 else
4713 x_LockedFlag := 'N';
4714 x_ChannelName := NULL;
4715 --return;
4716 end if;
4717
4718 END LOOP;
4719
4720 elsif p_CODFlag = 'Y' then
4721 FOR v_GetCODChannel in c_GetCODChannel(p_FeID, p_ChannelUsageCode,
4722 p_AdapterStatus, p_CODFlag) LOOP
4723
4724 -- skilaru 05/16/2002
4725 -- ER# 2013681 Lock the channel only if it is of application mode PIPE..
4726
4727 IF( XDP_ADAPTER_CORE_DB.checkLockRequired( v_GetCODChannel.channel_name ) ) THEN
4728
4729 l_Status := XDP_ADAPTER_CORE_DB.ObtainAdapterLock_FA(v_GetCODChannel.channel_name);
4730 ELSE
4731 -- skilaru 05/16/2002
4732 -- IF it is NON-PIPE based channel we dont need a lock at all..
4733 -- so mimic as if we acuired a lock..
4734 x_LockedFlag := 'Y';
4735 x_ChannelName := v_GetCODChannel.channel_name;
4736 return;
4737 END IF;
4738
4739 if l_Status = 'Y' then
4740
4741 x_LockedFlag := 'Y';
4742 x_ChannelName := v_GetCODChannel.channel_name;
4743
4744 if XDP_ADAPTER_CORE_DB.Verify_Adapter (v_GetCODChannel.channel_name) then
4745 return;
4746 else
4747 -- Release the Lock
4748 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock
4749 (p_ChannelName => v_GetCODChannel.channel_name);
4750 x_LockedFlag := 'N';
4751 x_ChannelName := NULL;
4752 END IF;
4753
4754 else
4755 x_LockedFlag := 'N';
4756 x_ChannelName := NULL;
4757 --return;
4758 end if;
4759
4760 END LOOP;
4761
4762 end if;
4763
4764 x_LockedFlag := 'N';
4765 x_ChannelName := NULL;
4766
4767 exception
4768 when others then
4769 if c_GetChannel%ISOPEN then
4770 close c_GetChannel;
4771 end if;
4772
4773 IF x_LockedFlag = 'Y' and x_ChannelName is not null THEN
4774 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => x_ChannelName);
4775 END IF ;
4776 raise;
4777 end SearchAndLockChannel;
4778
4779
4780 Function IsFAAborted(FAInstanceID in number) return boolean
4781
4782 is
4783 l_Status boolean := FALSE;
4784 l_FAState varchar2(40);
4785 l_WIState varchar2(40);
4786 l_WIInstanceID number;
4787
4788
4789 cursor c_GetFAState(p_FAInstanceID number) is
4790 select status_code ,workitem_instance_id from
4791 xdp_fa_runtime_list
4792 where fa_instance_id = p_FAInstanceID
4793 for update;
4794
4795 cursor c_GetWIState(p_WIInstanceID number) is
4796 select status_code from
4797 XDP_FULFILL_WORKLIST
4798 where workitem_instance_id = p_WIInstanceID
4799 for update;
4800
4801 e_NoFAFoundException exception;
4802 e_NoWIFoundException exception;
4803 begin
4804
4805 Savepoint GetFAState;
4806
4807 open c_GetFAState(FAInstanceID);
4808 Fetch c_GetFAState into l_FAState, l_WIInstanceID;
4809
4810 if c_GetFAState%NOTFOUND then
4811 rollback to GetFAState;
4812 raise e_NoFAFoundException;
4813 end if;
4814
4815 close c_GetFAState;
4816
4817 if l_FAState IN ('CANCELED','ABORTED') then
4818 l_Status := TRUE;
4819 rollback to GetFAState;
4820 return l_Status;
4821 else
4822 open c_GetWIState(l_WIInstanceID);
4823 Fetch c_GetWIState into l_WIState;
4824
4825 if c_GetWIState%NOTFOUND then
4826 rollback to GetFAState;
4827 raise e_NoWIFoundException;
4828 end if;
4829
4830 close c_GetWIState;
4831
4832 if l_WIState IN ('CANCELED','ABORTED') then
4833 l_Status := TRUE;
4834 rollback to GetFAState;
4835 return l_Status;
4836 end if;
4837
4838 end if;
4839
4840 rollback to GetFAState;
4841 l_Status := FALSE;
4842 return l_Status;
4843
4844
4845 exception
4846 when others then
4847 if c_GetWIState%ISOPEN then
4848 close c_GetWIState;
4849 end if;
4850 if c_GetFAState%ISOPEN then
4851 close c_GetFAState;
4852 end if;
4853 rollback to GetFAState;
4854 raise;
4855 end IsFAAborted;
4856
4857
4858 Function ConnectOnDemand(itemtype in varchar2,
4859 itemkey in varchar2) return varchar2
4860 is
4861
4862 l_ChannelName varchar2(40);
4863 l_AdapterStatus varchar2(80);
4864 l_FeName varchar2(80);
4865 l_ConnectProc varchar2(80);
4866 l_DisconnectProc varchar2(80);
4867
4868 l_Status varchar2(1);
4869
4870 ErrCode number;
4871 ErrStr varchar2(2000);
4872 l_ErrCode number;
4873 l_ErrStr varchar2(2000);
4874 l_Result varchar2(20);
4875
4876 x_progress varchar2(2000);
4877 x_parameters varchar2(4000);
4878
4879
4880 begin
4881 BEGIN
4882 l_ChannelName := wf_engine.GetItemAttrText(itemtype => ConnectOnDemand.itemtype,
4883 itemkey => ConnectOnDemand.itemkey,
4884 aname => 'CHANNEL_NAME');
4885
4886 l_FeName := wf_engine.GetItemAttrText(itemtype => ConnectOnDemand.itemtype,
4887 itemkey => ConnectOnDemand.itemkey,
4888 aname => 'FE_NAME');
4889
4890 ErrStr := ConnectOnDemand(l_channelName,l_ErrCode,l_ErrStr);
4891
4892 if(l_ErrCode <> 0) THEN
4893 l_Result := 'FAILURE';
4894 else
4895 l_Result := 'SUCCESS';
4896 end if;
4897
4898 EXCEPTION
4899 WHEN OTHERS THEN
4900 l_Result := 'FAILURE';
4901 END;
4902
4903 if l_Result = 'FAILURE' then
4904 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => l_ChannelName);
4905 end if;
4906
4907 return (l_Result);
4908 end ConnectOnDemand;
4909
4910 Function ConnectOnDemand(
4911 p_Channel_Name in varchar2,
4912 x_return_code OUT NOCOPY number,
4913 x_error_description OUT NOCOPY varchar2
4914 ) return varchar2
4915 IS
4916
4917 l_Result varchar2(20);
4918 x_progress varchar2(2000);
4919 x_parameters varchar2(4000);
4920 l_Status varchar2(1) := 'N';
4921 l_Status1 varchar2(4000);
4922
4923 BEGIN
4924 BEGIN
4925 -- Need to perform Connect On Demand Feature
4926 begin
4927 x_return_code := 0;
4928
4929 -- Updated sacsharm
4930 -- Adapter business object API should not be used as it locks/releases lock
4931 -- does Handover, etc.
4932
4933 if (XDP_ADAPTER_CORE_DB.Is_Adapter_Implemented (p_ChannelName=>p_Channel_Name)) then
4934
4935 XDP_ADAPTER_CORE.ProcessControlCommand(p_ChannelName => p_Channel_Name,
4936 p_Operation => XDP_ADAPTER.pv_opConnect,
4937 p_Status => l_Status1,
4938 p_ErrorMessage => x_error_description);
4939
4940 if l_Status1 <> XDP_ADAPTER_CORE.pv_AdapterResponseSuccess then
4941 x_return_code := XDP_ADAPTER.pv_retAdapterOpFailed;
4942 end if;
4943 END IF;
4944
4945 exception
4946 when others then
4947 x_return_code := SQLCODE;
4948 x_error_description := SQLERRM;
4949 end;
4950
4951 if x_return_code <> 0 then
4952 x_progress := 'Error when executing connect Error: ' || SUBSTR(x_error_description,1, 1500);
4953 begin
4954
4955 x_parameters := 'ERROR_CODE='||to_char(x_return_code)||'#XDP#'||
4956 'ERROR_DESC='||substr(x_error_description,1,1500)||'#XDP#';
4957
4958 -- Changed sacsharm Use autonomous transaction instead
4959
4960 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
4961 p_ChannelName => p_Channel_Name,
4962 -- p_Status => XDP_ADAPTER.pv_statusDisconnected,
4963 p_Status => XDP_ADAPTER.pv_statusError,
4964 p_ErrorMsg => 'INTERNAL_ERROR',
4965 p_ErrorMsgParams => x_parameters
4966 );
4967
4968 exception
4969 when others then
4970 x_error_description := SQLERRM;
4971 end;
4972 l_Result := 'N';
4973 else
4974 -- Update The Adapter Status to be BUSY
4975
4976 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
4977 p_ChannelName => p_Channel_Name,
4978 p_Status => XDP_ADAPTER.pv_statusInUse
4979 );
4980
4981 l_Result := 'Y';
4982 end if;
4983 EXCEPTION
4984 WHEN OTHERS THEN
4985 x_return_code := SQLCODE;
4986 x_error_description := SQLERRM;
4987 l_Result := 'N';
4988 END;
4989 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock (p_ChannelName => p_Channel_Name);
4990 return (l_Result);
4991 end ConnectOnDemand;
4992
4993
4994 Procedure DisconnectOnDemand( ChannelName in varchar2,
4995 FeName in varchar2,
4996 ErrCode OUT NOCOPY number,
4997 ErrStr OUT NOCOPY varchar2)
4998 is
4999 l_ConnectProc varchar2(80);
5000 l_DisconnectProc varchar2(80);
5001 begin
5002
5003 XDP_ENGINE.GET_FE_CONNECTIONPROC( FeName,
5004 l_ConnectProc,
5005 l_DisconnectProc);
5006
5007 XDP_UTILITIES.Call_NEConnection_Proc(l_DisConnectProc,
5008 FeName,
5009 ChannelName,
5010 ErrCode,
5011 ErrStr);
5012
5013
5014 end DisconnectOnDemand;
5015
5016
5017 Procedure SendAdapterErrorNotif(itemtype in varchar2,
5018 itemkey in varchar2,
5019 ChannelName in varchar2,
5020 FEName in varchar2,
5021 ErrorDescription in varchar2)
5022 is
5023 l_item_type varchar2(8);
5024 l_item_key varchar2(240);
5025
5026 l_order_id number;
5027 begin
5028
5029 l_item_type := 'XDPWFSTD' ;
5030 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_item_key from dual;
5031
5032 l_order_id := wf_engine.GetItemAttrNumber(
5033 itemtype => SendAdapterErrorNotif.itemtype,
5034 itemkey => SendAdapterErrorNotif.itemkey,
5035 aname => 'ORDER_ID');
5036
5037 l_item_key := l_order_id || '-' || ChannelName || l_item_key;
5038
5039
5040 wf_core.context('XDP_WF_STANDARD',
5041 'ADAPTER_ERROR',
5042 l_item_type,
5043 l_item_key) ;
5044
5045 wf_engine.createprocess(l_item_type,
5046 l_item_key,
5047 'ADAPTER_ERROR_NOTIFICATION') ;
5048
5049 wf_engine.SetItemAttrText(
5050 ItemType=>l_item_type,
5051 ItemKey=>l_item_key,
5052 aname=>'FE_NAME',
5053 avalue=>FEName);
5054
5055 wf_engine.SetItemAttrText(
5056 ItemType=>l_item_type,
5057 ItemKey=>l_item_key,
5058 aname=>'CHANNEL_NAME',
5059 avalue=>ChannelName);
5060
5061 wf_engine.SetItemAttrText(
5062 ItemType=>l_item_type,
5063 ItemKey=>l_item_key,
5064 aname=>'DESCRIPTION',
5065 avalue=>ErrorDescription);
5066
5067 wf_engine.startprocess(l_item_type,
5068 l_item_key ) ;
5069
5070 end SendAdapterErrorNotif;
5071
5072 PROCEDURE UPDATE_FA_STATUS(p_fa_instance_id IN NUMBER,
5073 p_status_code IN VARCHAR2,
5074 p_itemtype IN VARCHAR2,
5075 p_itemkey IN VARCHAR2 ) IS
5076 PRAGMA AUTONOMOUS_TRANSACTION;
5077 x_progress VARCHAR2(2000) ;
5078 BEGIN
5079 UPDATE xdp_fa_runtime_list
5080 SET status_code = p_status_code ,
5081 wf_item_type = p_itemtype ,
5082 wf_item_key = p_itemkey ,
5083 last_update_date = sysdate ,
5084 last_updated_by = fnd_global.user_id ,
5085 last_update_login = fnd_global.login_id
5086 WHERE fa_instance_id = p_fa_instance_id ;
5087
5088 COMMIT;
5089
5090 EXCEPTION
5091 WHEN others THEN
5092 x_Progress := 'XDPCORE_FA.UPDATE_FA_STATUS. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
5093 wf_core.context('XDPCORE_FA', 'UPDATE_FA_STATUS', p_itemtype, p_itemkey, null, x_Progress);
5094 rollback;
5095 raise;
5096 END UPDATE_FA_STATUS ;
5097
5098 Function ResolveIndDepFAs (itemtype in varchar2,
5099 itemkey in varchar2) return varchar2
5100 is
5101
5102 l_WIInstanceID number;
5103
5104 l_IndFound number := 0;
5105 l_DepFound number := 0;
5106
5107 cursor c_GetIndFAs (WIInstanceID number) is
5108 select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
5109 from XDP_FA_RUNTIME_LIST
5110 where WORKITEM_INSTANCE_ID = WIInstanceID
5111 and STATUS_CODE = 'STANDBY'
5112 and (PROVISIONING_SEQUENCE IS NULL or PROVISIONING_SEQUENCE = 0) ;
5113
5114 cursor c_GetDepFAs (WIInstanceID number) is
5115 select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
5116 from XDP_FA_RUNTIME_LIST
5117 where WORKITEM_INSTANCE_ID = WIInstanceID
5118 and STATUS_CODE = 'STANDBY'
5119 and PROVISIONING_SEQUENCE > 0;
5120
5121 x_progress varchar2(2000);
5122
5123 BEGIN
5124
5125 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepFAs.itemtype,
5126 itemkey => ResolveIndDepFAs.itemkey,
5127 aname => 'WORKITEM_INSTANCE_ID');
5128
5129 FOR lv_FARec in c_GetIndFAs( l_WIInstanceID ) LOOP
5130 l_IndFound := 1;
5131 EXIT;
5132 END LOOP;
5133
5134 FOR lv_FARec in c_GetDepFAs( l_WIInstanceID ) LOOP
5135 l_DepFound := 1;
5136 EXIT;
5137 END LOOP;
5138
5139 if( l_IndFound = 1 AND l_DepFound = 1 ) THEN
5140 RETURN 'BOTH';
5141 elsif( l_IndFound = 1) THEN
5142 RETURN 'INDEPENDENT';
5143 elsif( l_DepFound = 1 ) THEN
5144 RETURN 'DEPENDENT';
5145 end if;
5146
5147
5148 exception
5149 when others then
5150
5151 x_Progress := 'XDPCORE_FA.ResolveIndDepFAs. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
5152 wf_core.context('XDPCORE_FA', 'ResolveIndDepFAs', itemtype, itemkey, null, x_Progress);
5153 raise;
5154 end ResolveIndDepFAs;
5155
5156 Function LaunchAllIndFAs(itemtype in varchar2,
5157 itemkey in varchar2) return varchar2
5158 IS
5159 l_OrderID number;
5160 l_WIInstanceID number;
5161 l_LineItemID number;
5162 l_Counter number := 0;
5163 l_FAInstanceID number;
5164 l_FAID number;
5165 l_Priority number;
5166 l_FAItemType varchar2(10);
5167 l_FAItemKey varchar2(240);
5168
5169 l_result varchar2(1) := 'N';
5170
5171 l_ErrCode number;
5172 l_ErrStr varchar2(1996);
5173
5174 l_tempKey varchar2(240);
5175
5176 cursor c_GetIndFAs (WIInstanceID number) is
5177 select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
5178 from XDP_FA_RUNTIME_LIST
5179 where WORKITEM_INSTANCE_ID = WIInstanceID
5180 and STATUS_CODE = 'STANDBY'
5181 and (PROVISIONING_SEQUENCE IS null or PROVISIONING_SEQUENCE = 0) ;
5182
5183 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
5184 t_ChildKeys t_ChildKeyTable;
5185
5186 e_NoFAsFoundException exception;
5187 e_AddAttributeException exception;
5188 e_CreatFAWFException exception;
5189 e_AddFAtoQException exception;
5190
5191 x_Progress VARCHAR2(2000);
5192
5193 BEGIN
5194
5195 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndFAs.itemtype,
5196 itemkey => LaunchAllIndFAs.itemkey,
5197 aname => 'ORDER_ID');
5198
5199 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndFAs.itemtype,
5200 itemkey => LaunchAllIndFAs.itemkey,
5201 aname => 'WORKITEM_INSTANCE_ID');
5202
5203 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndFAs.itemtype,
5204 itemkey => LaunchAllIndFAs.itemkey,
5205 aname => 'LINE_ITEM_ID');
5206
5207
5208 FOR lv_FARec in c_GetIndFAs( l_WIInstanceID ) LOOP
5209 l_result := 'Y';
5210 l_Counter := l_Counter + 1;
5211
5212 l_FAInstanceID := lv_FARec.FA_INSTANCE_ID;
5213 l_Priority := lv_FARec.PRIORITY;
5214
5215
5216 CreateFAProcess('XDPPROV', LaunchAllIndFAs.itemkey, l_FAInstanceID, l_WIInstanceID,
5217 l_OrderID, 'INTERNAL','WAITFORFLOW-FA-IND',
5218 l_FAItemType, l_FAItemKey,
5219 l_ErrCode, l_ErrStr);
5220
5221
5222 if l_ErrCode <> 0 then
5223 x_Progress := 'XDPCORE_FA.LaunchAllIndFAs. Error when creating FA Process for Order: '
5224 || l_OrderID || ' Workitem InstanceID: ' || l_WIInstanceID || ' FAInstanceID: ' || l_FAInstanceID
5225 || ' Error: ' || SUBSTR(l_ErrStr, 1, 1500);
5226 RAISE e_CreatFAWFException;
5227 end if;
5228
5229 /* Update the XDP_FA_RUNTIME_LIST table with the User defined Workitem Item Type and Item Key */
5230
5231 update XDP_FA_RUNTIME_LIST
5232 set WF_ITEM_TYPE = l_FAItemType,
5233 WF_ITEM_KEY = l_FAItemKey,
5234 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
5235 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
5236 where FA_INSTANCE_ID = l_FAInstanceID;
5237
5238 /* Enqueue the FA into the FA Queue */
5239
5240 XDP_AQ_UTILITIES.Add_FA_ToQ ( P_ORDER_ID => l_OrderID,
5241 P_WI_INSTANCE_ID => l_WIInstanceID,
5242 P_FA_INSTANCE_ID => l_FAInstanceID,
5243 P_WF_ITEM_TYPE => l_FAItemType,
5244 P_WF_ITEM_KEY => l_FAItemKey,
5245 P_PRIORITY => l_Priority,
5246 P_RETURN_CODE => l_ErrCode,
5247 P_ERROR_DESCRIPTION => l_ErrStr);
5248
5249 if l_ErrCode <> 0 then
5250 x_Progress := 'XDPCORE_FA.LaunchFAProvisioningProcess. Error when Ading FA to Queue for for Order: '
5251 || l_OrderID || ' Workitem InstabceID: ' || l_WIInstanceID || ' FAInstanceID: ' || l_FAInstanceID
5252 || ' Error: ' || SUBSTR(l_ErrStr, 1, 1500);
5253
5254 RAISE e_AddFAtoQException;
5255 end if;
5256
5257 END LOOP;
5258
5259 return l_result;
5260
5261 exception
5262 when others then
5263 x_Progress := 'XDPCORE_FA.LaunchAllIndFAs. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
5264 wf_core.context('XDPCORE_FA', 'LaunchAllIndFAs', itemtype, itemkey, null, x_Progress);
5265 raise;
5266
5267 END LaunchAllIndFAs;
5268
5269 Procedure InitializeDepFAProcess(itemtype in varchar2,
5270 itemkey in varchar2)
5271 IS
5272 l_ErrCode number;
5273 l_ErrStr varchar2(1996);
5274 x_progress varchar2(1996);
5275
5276 BEGIN
5277
5278 XDPCORE.CheckNAddItemAttrNumber (itemtype => 'XDPPROV',
5279 itemkey => InitializeDepFAProcess.itemkey,
5280 AttrName => 'CURRENT_FA_SEQUENCE',
5281 AttrValue => 0,
5282 ErrCode => l_ErrCode,
5283 ErrStr => l_ErrStr);
5284
5285
5286 EXCEPTION
5287 when others then
5288 x_Progress := 'XDPCORE_FA.InitializeDepFAProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
5289 wf_core.context('XDPCORE_FA', 'InitializeDepFAProcess', itemtype, itemkey, null, x_Progress);
5290 raise;
5291
5292 END InitializeDepFAProcess;
5293
5294 Function IsAnyChannelAvailable(itemtype in varchar2,
5295 itemkey in varchar2) return varchar2
5296 IS
5297 x_Progress varchar2(2000);
5298 l_result varchar2(40);
5299 BEGIN
5300 -- check whether regular channel is available...
5301 IF( IsChannelAvailable(itemtype, itemkey, 'N', 'IDLE') = 'Y' ) THEN
5302 RETURN 'AVAILABLE';
5303 -- check whether COD channel is available...
5304 ELSIF ( IsChannelAvailable(itemtype, itemkey, 'Y', 'DISCONNECTED') = 'Y' ) THEN
5305 RETURN 'ON_DEMAND_AVAILABLE';
5306 ELSE
5307 RETURN 'NOT_AVAILABLE';
5308 END IF;
5309
5310 EXCEPTION
5311 when others then
5312 x_Progress := 'XDPCORE_FA.IsAnyChannelAvailable. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
5313 wf_core.context('XDPCORE_FA', 'IsAnyChannelAvailable', itemtype, itemkey, null, x_Progress);
5314 raise;
5315
5316 END;
5317
5318 Function get_display_name( p_FAInstanceID IN NUMBER) return varchar2
5319 IS
5320 l_display_name varchar2(100);
5321
5322 BEGIN
5323 SELECT display_name into l_display_name
5324 FROM xdp_fulfill_actions_vl fas
5325 WHERE fas.fulfillment_action_id IN ( SELECT lst.fulfillment_action_id
5326 FROM xdp_fa_runtime_list lst
5327 WHERE fa_instance_id = p_FAInstanceID );
5328 return l_display_name;
5329
5330 EXCEPTION
5331 WHEN others THEN
5332 wf_core.context('XDPCORE_FA', 'get_display_name', null, null, null,p_FAInstanceID );
5333 raise;
5334 END get_display_name;
5335
5336 Function IsThresholdExceeded (p_fp_name in varchar2) return varchar2 IS
5337 lv_error_threshold_str VARCHAR2(40);
5338 lv_revalidated_flag VARCHAR2(1);
5339 lv_error_threshold NUMBER;
5340 lv_error_count NUMBER;
5341 lv_result VARCHAR2(1) := 'N';
5342
5343 --Lock the row so that no body else can read it..
5344 CURSOR cur_error_count IS
5345 SELECT error_count
5346 FROM xdp_error_count
5347 WHERE object_key = p_fp_name
5348 AND object_type = XDP_UTILITIES.g_fp_object_type FOR UPDATE;
5349
5350 BEGIN
5351
5352 -- get the threshold..
5353 IF( fnd_profile.defined( g_xdp_fp_error_threshold) ) THEN
5354 fnd_profile.get( g_xdp_fp_error_threshold, lv_error_threshold_str );
5355 END IF;
5356
5357 lv_error_threshold := TO_NUMBER( lv_error_threshold_str );
5358
5359 SAVEPOINT readErrorCount;
5360
5361 FOR lv_rec IN cur_error_count LOOP
5362 lv_error_count := lv_rec.error_count;
5363
5364 IF (lv_error_count > lv_error_threshold) THEN
5365 lv_result := 'Y';
5366 END IF;
5367 -- for code readability sake.. we should always have only one record
5368 EXIT;
5369 END LOOP;
5370
5371 -- rollback to release the lock..
5372 ROLLBACK TO readErrorCount;
5373
5374 return lv_result;
5375
5376 EXCEPTION
5377 WHEN others THEN
5378 -- rollback to release the lock..
5379 ROLLBACK TO readErrorCount;
5380 raise;
5381 END IsThresholdExceeded;
5382
5383
5384 Function ErrorDuringRetry (itemtype in varchar2,
5385 itemkey in varchar2) return varchar2
5386 IS
5387
5388 lv_fp_in_error VARCHAR2(1) := 'N';
5389 lv_result VARCHAR2(1);
5390 ErrStr VARCHAR2(2000);
5391 ErrCode NUMBER;
5392 BEGIN
5393
5394 BEGIN
5395 lv_fp_in_error := wf_engine.getItemAttrText( itemtype => ErrorDuringRetry.itemtype,
5396 itemkey => ErrorDuringRetry.itemkey,
5397 aname => g_fp_in_error );
5398 -- This FAs FP errored out already.. so this is a retry on that error..
5399
5400 EXCEPTION
5401 -- If 'FP_IN_ERROR' not defined then this is the first time this FAs FP errored out..
5402 WHEN others THEN
5403 -- set the wf attribute..
5404 XDPCORE.CheckNAddItemAttrText (itemtype => ErrorDuringRetry.itemtype,
5405 itemkey => ErrorDuringRetry.itemkey,
5406 AttrName => g_fp_in_error,
5407 AttrValue => 'Y',
5408 ErrCode => ErrCode,
5409 ErrStr => ErrStr);
5410
5411 END;
5412
5413 RETURN lv_fp_in_error;
5414
5415 EXCEPTION
5416 WHEN others THEN
5417 wf_core.context('XDPCORE_FA', 'ErrorDuringRetry', itemtype, itemkey );
5418 raise;
5419 END ErrorDuringRetry;
5420
5421
5422 Function IsThresholdReached (p_fp_name in varchar2) return varchar2 IS
5423 lv_error_count NUMBER;
5424 lv_no_data_found NUMBER := 0;
5425 lv_error_threshold NUMBER;
5426 lv_error_threshold_str VARCHAR2(40);
5427 lv_result VARCHAR2(30);
5428
5429 --Lock the row so that no body else can read it..
5430 CURSOR cur_error_count IS
5431 SELECT error_count
5432 FROM xdp_error_count
5433 WHERE object_key = p_fp_name
5434 AND object_type = XDP_UTILITIES.g_fp_object_type FOR UPDATE;
5435
5436 BEGIN
5437
5438 -- get the threshold..
5439 IF( fnd_profile.defined( g_xdp_fp_error_threshold) ) THEN
5440 fnd_profile.get( g_xdp_fp_error_threshold, lv_error_threshold_str );
5441 END IF;
5442
5443 lv_error_threshold := TO_NUMBER( lv_error_threshold_str );
5444
5445
5446 FOR lv_rec IN cur_error_count LOOP
5447 lv_error_count := lv_rec.error_count;
5448 lv_no_data_found := 1;
5449
5450
5451 IF lv_error_count = lv_error_threshold THEN
5452 lv_result := 'THRESHOLD_REACHED';
5453 ELSIF lv_error_count < lv_error_threshold THEN
5454 lv_result := 'THRESHOLD_NOT_REACHED';
5455 ELSIF lv_error_count > lv_error_threshold THEN
5456 lv_result := 'THRESHOLD_EXCEEDED';
5457 --If users make the threshold intentionally 0 or NULL then
5458 --we dont provide the System hold functionality...
5459 --It works as if we havent introduced this status..
5460 ELSIF ( lv_error_threshold = 0 ) OR ( lv_error_threshold IS NULL ) THEN
5461 lv_result := 'THRESHOLD_NOT_REACHED';
5462 END IF;
5463
5464 -- we have to update the error count in any case..
5465
5466 lv_error_count := lv_error_count + 1;
5467
5468 UPDATE xdp_error_count
5469 SET error_count = lv_error_count,
5470 last_updated_by = FND_GLOBAL.USER_ID,
5471 last_update_date = sysdate,
5472 last_update_login = FND_GLOBAL.USER_ID
5473 WHERE CURRENT OF cur_error_count;
5474 -- for code readability sake.. we should always have only one record
5475 EXIT;
5476 END LOOP;
5477
5478
5479 -- If there is no row for this fp_name then the FP has never gone into error..
5480 -- So insert the first record with error count 1..
5481 IF lv_no_data_found = 0 THEN
5482 INSERT INTO XDP_ERROR_COUNT(
5483 object_type,
5484 object_key,
5485 error_count,
5486 created_by,
5487 creation_date,
5488 last_updated_by,
5489 last_update_date,
5490 last_update_login
5491 )
5492 VALUES (
5493 XDP_UTILITIES.g_fp_object_type,
5494 p_fp_name,
5495 1,
5496 FND_GLOBAL.USER_ID,
5497 sysdate,
5498 FND_GLOBAL.USER_ID,
5499 sysdate,
5500 FND_GLOBAL.LOGIN_ID);
5501 --This is the first error while executing the FP..
5502 lv_result := 'THRESHOLD_NOT_REACHED';
5503
5504 END IF;
5505
5506 RETURN lv_result;
5507
5508 EXCEPTION
5509 WHEN others THEN
5510 raise;
5511 END IsThresholdReached;
5512
5513
5514 Procedure ResetSystemHold (p_fp_name in varchar2) IS
5515 PRAGMA AUTONOMOUS_TRANSACTION;
5516
5517 lv_error_count NUMBER;
5518 lv_error_threshold NUMBER;
5519 lv_error_threshold_str VARCHAR2(40);
5520 lv_revalidated_flag VARCHAR2(1);
5521
5522 --Lock the row so that no body else can read it..
5523 CURSOR cur_error_count IS
5524 SELECT error_count
5525 FROM xdp_error_count
5526 WHERE object_key = p_fp_name
5527 AND object_type = XDP_UTILITIES.g_fp_object_type FOR UPDATE;
5528
5529 BEGIN
5530 -- get the threshold..
5531 IF( fnd_profile.defined( g_xdp_fp_error_threshold) ) THEN
5532 fnd_profile.get( g_xdp_fp_error_threshold, lv_error_threshold_str );
5533 END IF;
5534
5535 lv_error_threshold := TO_NUMBER( lv_error_threshold_str );
5536
5537
5538 FOR lv_rec IN cur_error_count LOOP
5539 lv_error_count := lv_rec.error_count;
5540
5541 -- on success of any FA first check whether there are any FAs waiting
5542 -- with System Hold status 'Y' then update them to 'N' so that they
5543 -- will be picked up in Handover Process..
5544 IF (lv_error_count > lv_error_threshold) THEN
5545 -- reset System Hold to 'N' so that adapter will pick it up..
5546 UPDATE xdp_adapter_job_queue
5547 SET system_hold = 'N',
5548 last_updated_by = FND_GLOBAL.USER_ID,
5549 last_update_date = sysdate,
5550 last_update_login = FND_GLOBAL.USER_ID
5551 WHERE fa_instance_id in ( SELECT fa_instance_id
5552 FROM xdp_fa_runtime_list
5553 WHERE proc_name = p_fp_name
5554 AND status_code = XDP_UTILITIES.g_system_hold );
5555 UPDATE xdp_fa_runtime_list
5556 SET status_code = XDP_UTILITIES.g_wait_for_resource,
5557 last_updated_by = FND_GLOBAL.USER_ID,
5558 last_update_date = sysdate,
5559 last_update_login = FND_GLOBAL.USER_ID
5560 WHERE proc_name = p_fp_name
5561 AND status_code = XDP_UTILITIES.g_system_hold;
5562 END IF;
5563
5564 -- finally set the error count to Zero..
5565 UPDATE xdp_error_count
5566 SET error_count = 0,
5567 last_updated_by = FND_GLOBAL.USER_ID,
5568 last_update_date = sysdate,
5569 last_update_login = FND_GLOBAL.USER_ID
5570 WHERE CURRENT OF cur_error_count;
5571 -- for code readability sake.. we should always have only one record
5572 EXIT;
5573 END LOOP;
5574
5575 -- commit the autonomous transaction to release the lock..
5576 COMMIT;
5577
5578 EXCEPTION
5579 WHEN others THEN
5580 -- rollback the autonomous transaction to release the lock..
5581 ROLLBACK;
5582 raise;
5583 END ResetSystemHold;
5584
5585 -- skilaru 05/29/2002.
5586 -- This procedure has to be in an autonomous transaction
5587 -- in order to avoid dead lock while updating the fa status
5588 -- in the next activity..
5589
5590 Procedure OverrideFE ( p_faInstanceID IN VARCHAR2,
5591 p_FEName IN VARCHAR2,
5592 resultout OUT NOCOPY varchar2 )
5593 IS PRAGMA AUTONOMOUS_TRANSACTION;
5594
5595 l_faDispName VARCHAR2(200);
5596 l_feFound BOOLEAN := FALSE;
5597 l_messageParams varchar2(2000);
5598 x_Progress varchar2(2000);
5599
5600 CURSOR c_getFEID IS
5601 SELECT fe_id
5602 FROM XDP_FES
5603 WHERE fulfillment_element_name = p_FEName;
5604
5605 BEGIN
5606
5607 FOR lv_rec IN c_getFEID LOOP
5608 l_feFound := TRUE;
5609 UPDATE xdp_fa_runtime_list
5610 SET fe_id = lv_rec.fe_id
5611 WHERE fa_instance_id = p_faInstanceID;
5612 -- commit autonomous transaction..
5613 COMMIT;
5614 END LOOP;
5615
5616 IF NOT l_feFound THEN
5617 l_faDispName := get_display_name( p_FAInstanceID );
5618 l_messageParams := 'FA='||l_faDispName|| '#XDP#FE=' || p_FEName ||'#XDP#';
5619
5620 -- set the business error...
5621 XDPCORE.error_context( 'FA', p_faInstanceID, 'XDP_USER_ENTERED_INVALID_FE', l_messageParams );
5622 xdpcore.context('XDPCORE_FA', 'OverrideFE', 'FA', p_faInstanceID);
5623 resultout := 'COMPLETE:FE_NOT_FOUND';
5624 END IF;
5625
5626 EXCEPTION
5627 WHEN others THEN
5628 raise;
5629 END OverrideFE;
5630
5631
5632
5633 End XDPCORE_FA;