1 PACKAGE BODY XDPSTATUS AS
2 /* $Header: XDPSTATB.pls 120.1 2005/06/09 00:32:17 appldev $ */
3
4
5 /****
6 All Private Procedures for the Package
7 ****/
8
9 Procedure SetOrderStatus(itemtype in varchar2,
10 itemkey in varchar2);
11
12 Procedure SendOrderStatus(itemtype in varchar2,
13 itemkey in varchar2,
14 actid in number);
15
16 Procedure SetBundleStatus(itemtype in varchar2,
17 itemkey in varchar2);
18
19 Procedure SetLineStatus(itemtype in varchar2,
20 itemkey in varchar2);
21
22 Procedure SendLineStatus(itemtype in varchar2,
23 itemkey in varchar2,
24 actid in number);
25
26 Procedure SetPackageStatus(itemtype in varchar2,
27 itemkey in varchar2);
28
29 /***
30 Procedure SetWorkitemStatus(itemtype in varchar2,
31 itemkey in varchar2);
32 ***/
33
34 Procedure SetWIStatusSuccess(itemtype in varchar2,
35 itemkey in varchar2);
36
37 Procedure SaveWorkitem(itemtype in varchar2,
38 itemkey in varchar2,
39 actid in number);
40
41 Procedure SendWorkitemStatus(itemtype in varchar2,
42 itemkey in varchar2,
43 actid in number);
44
45 Procedure SetFAStatus(itemtype in varchar2,
46 itemkey in varchar2,
47 actid in number);
48
49 Procedure SetFeExecStatus(itemtype in varchar2,
50 itemkey in varchar2);
51
52 Procedure SendFeProvStatus(itemtype in varchar2,
53 itemkey in varchar2,
54 actid in number);
55
56 Function GetResubmissionJobID (itemtype in varchar2,
57 itemkey in varchar2) return number;
58
59 Procedure UpdateFAStatus(faid in number, status in varchar2, provmode in varchar2);
60
61 PROCEDURE SetErrorStatus(itemtype IN VARCHAR2,
62 itemkey IN VARCHAR2,
63 actid IN NUMBER);
64
65 PROCEDURE SetNodeWIStatus(itemtype IN VARCHAR2,
66 itemkey IN VARCHAR2,
67 actid IN NUMBER);
68
69
70 PROCEDURE SetNodeLineStatus(itemtype IN VARCHAR2,
71 itemkey IN VARCHAR2,
72 actid IN NUMBER);
73
74 /***********************************************
75 * END of Private Procedures/Function Definitions
76 ************************************************/
77
78
79
80 -- SET_ERROR_STATUS
81 -- Resultout
82 -- Activity Performed - Activity was completed without any errors
83 --
84 -- Your Description here:
85
86 Procedure SET_ERROR_STATUS (itemtype in varchar2,
87 itemkey in varchar2,
88 actid in number,
89 funcmode in varchar2,
90 resultout OUT NOCOPY varchar2 ) IS
91
92 BEGIN
93 -- RUN mode - normal process execution
94 --
95 IF (funcmode = 'RUN') THEN
96 SetErrorStatus(itemtype, itemkey,actid);
97 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
98 END IF;
99 -- CANCEL mode - activity 'compensation'
100 --
101 -- This is in the event that the activity must be undone
102 -- for example when a process is reset to an earlier point
103 -- due to a loop back.
104 --
105
106 IF (funcmode = 'CANCEL') THEN
107 resultout := 'COMPLETE';
108 return;
109 END IF;
110
111 IF (funcmode = 'RESPOND') THEN
112 resultout := 'COMPLETE';
113 return;
114 END IF;
115
116 IF (funcmode = 'FORWARD') THEN
117 resultout := 'COMPLETE';
118 return;
119 END IF;
120
121 IF (funcmode = 'TRANSFER') THEN
122 return;
123 END IF;
124
125 IF (funcmode = 'TIMEOUT') THEN
126 resultout := 'COMPLETE';
127 return;
128 END IF;
129
130 IF (funcmode = 'others') THEN
131 resultout := ' ';
132 return;
133 END IF;
134
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 wf_core.context('XDPSTATUS', 'SET_ERROR_STATUS', itemtype, itemkey, to_char(actid), funcmode);
139 raise;
140 END SET_ERROR_STATUS;
141
142 -- SET_ORDER_STATUS
143 -- Resultout
144 -- Activity Performed - Activity was completed without any errors
145 --
146 -- Your Description here:
147
148 Procedure SET_ORDER_STATUS (itemtype in varchar2,
149 itemkey in varchar2,
150 actid in number,
151 funcmode in varchar2,
152 resultout OUT NOCOPY varchar2 ) IS
153
154 BEGIN
155 -- RUN mode - normal process execution
156 --
157 IF (funcmode = 'RUN') THEN
158 SetOrderStatus(itemtype, itemkey);
159 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
160 END IF;
161 -- CANCEL mode - activity 'compensation'
162 --
163 -- This is in the event that the activity must be undone
164 -- for example when a process is reset to an earlier point
165 -- due to a loop back.
166 --
167
168 IF (funcmode = 'CANCEL') THEN
169 resultout := 'COMPLETE';
170 return;
171 END IF;
172
173 IF (funcmode = 'RESPOND') THEN
174 resultout := 'COMPLETE';
175 return;
176 END IF;
177
178 IF (funcmode = 'FORWARD') THEN
179 resultout := 'COMPLETE';
180 return;
181 END IF;
182
183 IF (funcmode = 'TRANSFER') THEN
184 return;
185 END IF;
186
187 IF (funcmode = 'TIMEOUT') THEN
188 resultout := 'COMPLETE';
189 return;
190 END IF;
191
192 IF (funcmode = 'others') THEN
193 resultout := ' ';
194 return;
195 END IF;
196
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 wf_core.context('XDPSTATUS', 'SET_ORDER_STATUS', itemtype, itemkey, to_char(actid), funcmode);
201 raise;
202 END SET_ORDER_STATUS;
203
204
205 -- SEND_ORDER_STATUS
206 -- Resultout
207 -- Activity Performed - Activity was completed without any errors
208 --
209 -- Your Description here:
210
211 Procedure SEND_ORDER_STATUS (itemtype in varchar2,
212 itemkey in varchar2,
213 actid in number,
214 funcmode in varchar2,
215 resultout OUT NOCOPY varchar2 ) IS
216
217 BEGIN
218 -- RUN mode - normal process execution
219 --
220 IF (funcmode = 'RUN') THEN
221 SendOrderStatus(itemtype, itemkey, actid);
222 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
223 END IF;
224 -- CANCEL mode - activity 'compensation'
225 --
226 -- This is in the event that the activity must be undone
227 -- for example when a process is reset to an earlier point
228 -- due to a loop back.
229 --
230
231 IF (funcmode = 'CANCEL') THEN
232 resultout := 'COMPLETE';
233 return;
234 END IF;
235
236 IF (funcmode = 'RESPOND') THEN
237 resultout := 'COMPLETE';
238 return;
239 END IF;
240
241 IF (funcmode = 'FORWARD') THEN
242 resultout := 'COMPLETE';
243 return;
244 END IF;
245
246 IF (funcmode = 'TRANSFER') THEN
247 resultout := 'COMPLETE';
248 return;
249 END IF;
250
251 IF (funcmode = 'TIMEOUT') THEN
252 resultout := 'COMPLETE';
253 return;
254 END IF;
255
256 IF (funcmode = 'others') THEN
257 resultout := ' ';
258 return;
259 END IF;
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 wf_core.context('XDPSTATUS', 'SEND_ORDER_STATUS', itemtype, itemkey, to_char(actid), funcmode);
264 raise;
265 END SEND_ORDER_STATUS;
266
267
268
269 -- SEND_LINE_STATUS
270 -- Resultout
271 -- Activity Performed - Activity was completed without any errors
272 --
273 -- Your Description here:
274
275 Procedure SEND_LINE_STATUS (itemtype in varchar2,
276 itemkey in varchar2,
277 actid in number,
278 funcmode in varchar2,
279 resultout OUT NOCOPY varchar2 ) IS
280
281 BEGIN
282 -- RUN mode - normal process execution
283 --
284 IF (funcmode = 'RUN') THEN
285 SendLineStatus(itemtype, itemkey, actid);
286 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
287 END IF;
288 -- CANCEL mode - activity 'compensation'
289 --
290 -- This is in the event that the activity must be undone
291 -- for example when a process is reset to an earlier point
292 -- due to a loop back.
293 --
294
295 IF (funcmode = 'CANCEL') THEN
296 resultout := 'COMPLETE';
297 return;
298 END IF;
299
300 IF (funcmode = 'RESPOND') THEN
301 resultout := 'COMPLETE';
302 return;
303 END IF;
304
305 IF (funcmode = 'FORWARD') THEN
306 resultout := 'COMPLETE';
307 return;
308 END IF;
309
310 IF (funcmode = 'TRANSFER') THEN
311 resultout := 'COMPLETE';
312 return;
313 END IF;
314
315 IF (funcmode = 'TIMEOUT') THEN
316 resultout := 'COMPLETE';
317 return;
318 END IF;
319
320 IF (funcmode = 'others') THEN
321 resultout := ' ';
322 return;
323 END IF;
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 wf_core.context('XDPSTATUS', 'SEND_LINE_STATUS', itemtype, itemkey, to_char(actid), funcmode);
328 raise;
329 END SEND_LINE_STATUS;
330
331
332
333 -- SET_BUNDLE_STATUS
334 -- Resultout
335 -- Activity Performed - Activity was completed without any errors
336 --
337 -- Your Description here:
338
339 Procedure SET_BUNDLE_STATUS (itemtype in varchar2,
340 itemkey in varchar2,
341 actid in number,
342 funcmode in varchar2,
343 resultout OUT NOCOPY varchar2 ) IS
344
345 BEGIN
346 -- RUN mode - normal process execution
347 --
348 IF (funcmode = 'RUN') THEN
349 SetBundleStatus(itemtype, itemkey);
350 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
351 END IF;
352 -- CANCEL mode - activity 'compensation'
353 --
354 -- This is in the event that the activity must be undone
355 -- for example when a process is reset to an earlier point
356 -- due to a loop back.
357 --
358
359 IF (funcmode = 'CANCEL') THEN
360 resultout := 'COMPLETE';
361 return;
362 END IF;
363
364 IF (funcmode = 'RESPOND') THEN
365 resultout := 'COMPLETE';
366 return;
367 END IF;
368
369 IF (funcmode = 'FORWARD') THEN
370 resultout := 'COMPLETE';
371 return;
372 END IF;
373
374 IF (funcmode = 'TRANSFER') THEN
375 return;
376 END IF;
377
378 IF (funcmode = 'TIMEOUT') THEN
379 resultout := 'COMPLETE';
380 return;
381 END IF;
382
383 IF (funcmode = 'others') THEN
384 resultout := ' ';
385 return;
386 END IF;
387
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 wf_core.context('XDPSTATUS', 'SET_BUNDLE_STATUS', itemtype, itemkey, to_char(actid), funcmode);
392 raise;
393 END SET_BUNDLE_STATUS;
394
395
396 -- SET_LINE_STATUS
397 -- Resultout
398 -- Activity Performed - Activity was completed without any errors
399 --
400 -- Your Description here:
401
402 Procedure SET_LINE_STATUS (itemtype in varchar2,
403 itemkey in varchar2,
404 actid in number,
405 funcmode in varchar2,
406 resultout OUT NOCOPY varchar2 ) IS
407
408 BEGIN
409 -- RUN mode - normal process execution
410 --
411 IF (funcmode = 'RUN') THEN
412 SetLineStatus(itemtype, itemkey);
413 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
414 END IF;
415 -- CANCEL mode - activity 'compensation'
416 --
417 -- This is in the event that the activity must be undone
418 -- for example when a process is reset to an earlier point
419 -- due to a loop back.
420 --
421
422 IF (funcmode = 'CANCEL') THEN
423 resultout := 'COMPLETE';
424 return;
425 END IF;
426
427 IF (funcmode = 'RESPOND') THEN
428 resultout := 'COMPLETE';
429 return;
430 END IF;
431
432 IF (funcmode = 'FORWARD') THEN
433 resultout := 'COMPLETE';
434 return;
435 END IF;
436
437 IF (funcmode = 'TRANSFER') THEN
438 return;
439 END IF;
440
441 IF (funcmode = 'TIMEOUT') THEN
442 resultout := 'COMPLETE';
443 return;
444 END IF;
445
446 IF (funcmode = 'others') THEN
447 resultout := ' ';
448 return;
449 END IF;
450
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 wf_core.context('XDPSTATUS', 'SET_LINE_STATUS', itemtype, itemkey, to_char(actid), funcmode);
455 raise;
456 END SET_LINE_STATUS;
457
458
459
460
461 -- SET_PACKAGE_STATUS
462 -- Resultout
463 -- Activity Performed - Activity was completed without any errors
464 --
465 -- Your Description here:
466
467 Procedure SET_PACKAGE_STATUS (itemtype in varchar2,
468 itemkey in varchar2,
469 actid in number,
470 funcmode in varchar2,
471 resultout OUT NOCOPY varchar2 ) IS
472
473 BEGIN
474 -- RUN mode - normal process execution
475 --
476 IF (funcmode = 'RUN') THEN
477 SetPackageStatus(itemtype, itemkey);
478 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
479 END IF;
480 -- CANCEL mode - activity 'compensation'
481 --
482 -- This is in the event that the activity must be undone
483 -- for example when a process is reset to an earlier point
484 -- due to a loop back.
485 --
486
487 IF (funcmode = 'CANCEL') THEN
488 resultout := 'COMPLETE';
489 return;
490 END IF;
491
492 IF (funcmode = 'RESPOND') THEN
493 resultout := 'COMPLETE';
494 return;
495 END IF;
496
497 IF (funcmode = 'FORWARD') THEN
498 resultout := 'COMPLETE';
499 return;
500 END IF;
501
502 IF (funcmode = 'TRANSFER') THEN
503 return;
504 END IF;
505
506 IF (funcmode = 'TIMEOUT') THEN
507 resultout := 'COMPLETE';
508 return;
509 END IF;
510
511 IF (funcmode = 'others') THEN
512 resultout := ' ';
513 return;
514 END IF;
515
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 wf_core.context('XDPSTATUS', 'SET_PACKAGE_STATUS', itemtype, itemkey, to_char(actid), funcmode);
520 raise;
521 END SET_PACKAGE_STATUS;
522
523
524
525
526 -- l_OrderID
527 -- Resultout
528 -- Activity Performed - Activity was completed without any errors
529 --
530 -- Your Description here:
531
532 Procedure SET_WORKITEM_STATUS (itemtype in varchar2,
533 itemkey in varchar2,
534 actid in number,
535 funcmode in varchar2,
536 resultout OUT NOCOPY varchar2 ) IS
537
538 BEGIN
539 -- RUN mode - normal process execution
540 --
541 IF (funcmode = 'RUN') THEN
542 SetWorkitemStatus(itemtype, itemkey);
543 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
544 END IF;
545 -- CANCEL mode - activity 'compensation'
546 --
547 -- This is in the event that the activity must be undone
548 -- for example when a process is reset to an earlier point
549 -- due to a loop back.
550 --
551
552
553 IF (funcmode = 'RESPOND') THEN
554 resultout := 'COMPLETE';
555 return;
556 END IF;
557
558 IF (funcmode = 'FORWARD') THEN
559 resultout := 'COMPLETE';
560 return;
561 END IF;
562
563 IF (funcmode = 'TRANSFER') THEN
564 resultout := 'COMPLETE';
565 return;
566 END IF;
567
568 IF (funcmode = 'TIMEOUT') THEN
569 resultout := 'COMPLETE';
570 return;
571 END IF;
572
573 IF (funcmode = 'others') THEN
574 resultout := ' ';
575 return;
576 END IF;
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 wf_core.context('XDPSTATUS', 'SET_WORKITEM_STATUS', itemtype, itemkey, to_char(actid), funcmode);
581 raise;
582 END SET_WORKITEM_STATUS;
583
584
585 -- SET_WI_STATUS_SUCCESS
586 -- Resultout
587 -- Activity Performed - Activity was completed without any errors
588 --
589 -- Your Description here:
590
591 Procedure SET_WI_STATUS_SUCCESS (itemtype in varchar2,
592 itemkey in varchar2,
593 actid in number,
594 funcmode in varchar2,
595 resultout OUT NOCOPY varchar2 ) IS
596
597 BEGIN
598 -- RUN mode - normal process execution
599 --
600 IF (funcmode = 'RUN') THEN
601 SetWIStatusSuccess(itemtype, itemkey);
602 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
603 END IF;
604 -- CANCEL mode - activity 'compensation'
605 --
606 -- This is in the event that the activity must be undone
607 -- for example when a process is reset to an earlier point
608 -- due to a loop back.
609 --
610
611
612 IF (funcmode = 'RESPOND') THEN
613 resultout := 'COMPLETE';
614 return;
615 END IF;
616
617 IF (funcmode = 'FORWARD') THEN
618 resultout := 'COMPLETE';
619 return;
620 END IF;
621
622 IF (funcmode = 'TRANSFER') THEN
623 resultout := 'COMPLETE';
624 return;
625 END IF;
626
627 IF (funcmode = 'TIMEOUT') THEN
628 resultout := 'COMPLETE';
629 return;
630 END IF;
631
632 IF (funcmode = 'others') THEN
633 resultout := ' ';
634 return;
635 END IF;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 wf_core.context('XDPSTATUS', 'SET_WI_STATUS_SUCCESS', itemtype, itemkey, to_char(actid), funcmode);
640 raise;
641 END SET_WI_STATUS_SUCCESS;
642
643
644
645
646 -- SEND_WORKITEM_STATUS
647 -- Resultout
648 -- Activity Performed - Activity was completed without any errors
649 --
650 -- Your Description here:
651
652 Procedure SEND_WORKITEM_STATUS (itemtype in varchar2,
653 itemkey in varchar2,
654 actid in number,
655 funcmode in varchar2,
656 resultout OUT NOCOPY varchar2 ) IS
657 BEGIN
658 -- RUN mode - normal process execution
659 --
660 IF (funcmode = 'RUN') THEN
661 SendWorkitemStatus(itemtype, itemkey, actid);
662 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
663
664 END IF;
665 -- CANCEL mode - activity 'compensation'
666 --
667 -- This is in the event that the activity must be undone
668 -- for example when a process is reset to an earlier point
669 -- due to a loop back.
670 --
671
672 IF (funcmode = 'CANCEL') THEN
673 resultout := 'COMPLETE';
674 return;
675 END IF;
676
677 IF (funcmode = 'RESPOND') THEN
678 resultout := 'COMPLETE';
679 return;
680 END IF;
681
682 IF (funcmode = 'FORWARD') THEN
683 resultout := 'COMPLETE';
684 return;
685 END IF;
686
687 IF (funcmode = 'TRANSFER') THEN
688 resultout := 'COMPLETE';
689 return;
690 END IF;
691
692 IF (funcmode = 'TIMEOUT') THEN
693 resultout := 'COMPLETE';
694 return;
695 END IF;
696
697 IF (funcmode = 'others') THEN
698 resultout := ' ';
699 return;
700 END IF;
701
702 EXCEPTION
703 WHEN OTHERS THEN
704 wf_core.context('XDPSTATUS', 'SEND_WORKITEM_STATUS', itemtype, itemkey, to_char(actid), funcmode);
705 raise;
706 END SEND_WORKITEM_STATUS;
707
708
709 -- SAVE_WORKITEM
710 -- Resultout
711 -- Activity Performed - Activity was completed without any errors
712 --
713 -- Your Description here:
714
715 Procedure SAVE_WORKITEM (itemtype in varchar2,
716 itemkey in varchar2,
717 actid in number,
718 funcmode in varchar2,
719 resultout OUT NOCOPY varchar2 ) IS
720
721 BEGIN
722 -- RUN mode - normal process execution
723 --
724 IF (funcmode = 'RUN') THEN
725 SaveWorkitem(itemtype, itemkey, actid);
726 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
727 END IF;
728 -- CANCEL mode - activity 'compensation'
729 --
730 -- This is in the event that the activity must be undone
731 -- for example when a process is reset to an earlier point
732 -- due to a loop back.
733 --
734
735
736 IF (funcmode = 'RESPOND') THEN
737 resultout := 'COMPLETE';
738 return;
739 END IF;
740
741 IF (funcmode = 'FORWARD') THEN
742 resultout := 'COMPLETE';
743 return;
744 END IF;
745
746 IF (funcmode = 'TRANSFER') THEN
747 resultout := 'COMPLETE';
748 return;
749 END IF;
750
751 IF (funcmode = 'TIMEOUT') THEN
752 resultout := 'COMPLETE';
753 return;
754 END IF;
755
756 IF (funcmode = 'others') THEN
757 resultout := ' ';
758 return;
759 END IF;
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 wf_core.context('XDPSTATUS', 'SAVE_WORKITEM', itemtype, itemkey, to_char(actid), funcmode);
764 raise;
765 END SAVE_WORKITEM;
766
767
768 -- SET_FA_STATUS
769 -- Resultout
770 -- Activity Performed - Activity was completed without any errors
771 --
772 -- Your Description here:
773
774 Procedure SET_FA_STATUS (itemtype in varchar2,
775 itemkey in varchar2,
776 actid in number,
777 funcmode in varchar2,
778 resultout OUT NOCOPY varchar2 ) IS
779
780 BEGIN
781 -- RUN mode - normal process execution
782 --
783 IF (funcmode = 'RUN') THEN
784 SetFAStatus(itemtype, itemkey, actid);
785 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
786 END IF;
787 -- CANCEL mode - activity 'compensation'
788 --
789 -- This is in the event that the activity must be undone
790 -- for example when a process is reset to an earlier point
791 -- due to a loop back.
792 --
793
794 IF (funcmode = 'CANCEL') THEN
795 resultout := 'COMPLETE';
796 return;
797 END IF;
798
799 IF (funcmode = 'RESPOND') THEN
800 resultout := 'COMPLETE';
801 return;
802 END IF;
803
804 IF (funcmode = 'FORWARD') THEN
805 resultout := 'COMPLETE';
806 return;
807 END IF;
808
809 IF (funcmode = 'TRANSFER') THEN
810 resultout := 'COMPLETE';
811 return;
812 END IF;
813
814 IF (funcmode = 'TIMEOUT') THEN
815 resultout := 'COMPLETE';
816 return;
817 END IF;
818
819 IF (funcmode = 'others') THEN
820 resultout := ' ';
821 return;
822 END IF;
823
824 EXCEPTION
825 WHEN OTHERS THEN
826 wf_core.context('XDPSTATUS', 'SET_FA_STATUS', itemtype, itemkey, to_char(actid), funcmode);
827 raise;
828 END SET_FA_STATUS;
829
830
831
832 -- SEND_FE_PROV_STATUS
833 -- Resultout
834 -- Activity Performed - Activity was completed without any errors
835 --
836 -- Your Description here:
837
838 Procedure SEND_FE_PROV_STATUS (itemtype in varchar2,
839 itemkey in varchar2,
840 actid in number,
841 funcmode in varchar2,
842 resultout OUT NOCOPY varchar2 ) IS
843
844 BEGIN
845 -- RUN mode - normal process execution
846 --
847 IF (funcmode = 'RUN') THEN
848 SendFeProvStatus(itemtype, itemkey, actid);
849 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
850 END IF;
851 -- CANCEL mode - activity 'compensation'
852 --
853 -- This is in the event that the activity must be undone
854 -- for example when a process is reset to an earlier point
855 -- due to a loop back.
856 --
857
858 IF (funcmode = 'CANCEL') THEN
859 resultout := 'COMPLETE';
860 return;
861 END IF;
862
863 IF (funcmode = 'RESPOND') THEN
864 resultout := 'COMPLETE';
865 return;
866 END IF;
867
868 IF (funcmode = 'FORWARD') THEN
869 resultout := 'COMPLETE';
870 return;
871 END IF;
872
873 IF (funcmode = 'TRANSFER') THEN
874 resultout := 'COMPLETE';
875 return;
876 END IF;
877
878 IF (funcmode = 'TIMEOUT') THEN
879 resultout := 'COMPLETE';
880 return;
881 END IF;
882
883 IF (funcmode = 'others') THEN
884 resultout := ' ';
885 return;
886 END IF;
887
888 EXCEPTION
889 WHEN OTHERS THEN
890 wf_core.context('XDPSTATUS', 'SEND_FE_PROV_STATUS', itemtype, itemkey, to_char(actid), funcmode);
891 raise;
892 END SEND_FE_PROV_STATUS;
893
894
895
896 -- SET_FE_EXEC_STATUS
897 -- Resultout
898 -- Activity Performed - Activity was completed without any errors
899 --
900 -- Your Description here:
901
902 Procedure SET_FE_EXEC_STATUS (itemtype in varchar2,
903 itemkey in varchar2,
904 actid in number,
905 funcmode in varchar2,
906 resultout OUT NOCOPY varchar2 ) IS
907
908 BEGIN
909 -- RUN mode - normal process execution
910 --
911 IF (funcmode = 'RUN') THEN
912 SetFeExecStatus(itemtype, itemkey);
913 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
914 END IF;
915 -- CANCEL mode - activity 'compensation'
916 --
917 -- This is in the event that the activity must be undone
918 -- for example when a process is reset to an earlier point
919 -- due to a loop back.
920 --
921
922 IF (funcmode = 'CANCEL') THEN
923 resultout := 'COMPLETE';
924 return;
925 END IF;
926
927 IF (funcmode = 'RESPOND') THEN
928 resultout := 'COMPLETE';
929 return;
930 END IF;
931
932 IF (funcmode = 'FORWARD') THEN
933 resultout := 'COMPLETE';
934 return;
935 END IF;
936
937 IF (funcmode = 'TRANSFER') THEN
938 resultout := 'COMPLETE';
939 return;
940 END IF;
941
942 IF (funcmode = 'TIMEOUT') THEN
943 resultout := 'COMPLETE';
944 return;
945 END IF;
946
947 IF (funcmode = 'others') THEN
948 resultout := ' ';
949 return;
950 END IF;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 wf_core.context('XDPSTATUS', 'SET_FE_EXEC_STATUS', itemtype, itemkey, to_char(actid), funcmode);
955 raise;
956 END SET_FE_EXEC_STATUS;
957
958
959 -- SET_FE_PROV_STATE
960 -- Resultout
961 -- Activity Performed - Activity was completed without any errors
962 --
963 -- Your Description here:
964
965 Procedure SET_FE_PROV_STATE (itemtype in varchar2,
966 itemkey in varchar2,
967 actid in number,
968 funcmode in varchar2,
969 resultout OUT NOCOPY varchar2 ) IS
970
971 BEGIN
972 -- RUN mode - normal process execution
973 --
974 IF (funcmode = 'RUN') THEN
975 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
976 END IF;
977 -- CANCEL mode - activity 'compensation'
978 --
979 -- This is in the event that the activity must be undone
980 -- for example when a process is reset to an earlier point
981 -- due to a loop back.
982 --
983
984
985 IF (funcmode = 'RESPOND') THEN
986 resultout := 'COMPLETE';
987 return;
988 END IF;
989
990 IF (funcmode = 'FORWARD') THEN
991 resultout := 'COMPLETE';
992 return;
993 END IF;
994
995 IF (funcmode = 'TRANSFER') THEN
996 resultout := 'COMPLETE';
997 return;
998 END IF;
999
1000 IF (funcmode = 'TIMEOUT') THEN
1001 resultout := 'COMPLETE';
1002 return;
1003 END IF;
1004
1005 IF (funcmode = 'others') THEN
1006 resultout := ' ';
1007 return;
1008 END IF;
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 wf_core.context('XDPSTATUS', 'SET_FE_PROV_STATE', itemtype, itemkey, to_char(actid), funcmode);
1013 raise;
1014 END SET_FE_PROV_STATE;
1015
1016 --This procedure sets the Line item status to the status
1017 --that is assigned to the node in the workflow..
1018 Procedure SET_NODE_LINE_STATUS (itemtype in varchar2,
1019 itemkey in varchar2,
1020 actid in number,
1021 funcmode in varchar2,
1022 resultout OUT NOCOPY varchar2 ) IS
1023
1024 BEGIN
1025 -- RUN mode - normal process execution
1026 --
1027 IF (funcmode = 'RUN') THEN
1028 SetNodeLineStatus(itemtype, itemkey,actid);
1029 resultout := 'COMPLETE';
1030 ELSE
1031 resultout := 'COMPLETE';
1032 return;
1033 END IF;
1034
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 wf_core.context('XDPSTATUS', 'SET_NODE_LINE_STATUS', itemtype, itemkey, to_char(actid), funcmode);
1039 raise;
1040 END SET_NODE_LINE_STATUS;
1041 --This procedure sets the work item status to the status
1042 --that is assigned to the node in the workflow..
1043 Procedure SET_NODE_WI_STATUS (itemtype in varchar2,
1044 itemkey in varchar2,
1045 actid in number,
1046 funcmode in varchar2,
1047 resultout OUT NOCOPY varchar2 ) IS
1048
1049 BEGIN
1050 -- RUN mode - normal process execution
1051 --
1052 IF (funcmode = 'RUN') THEN
1053 SetNodeWIStatus(itemtype, itemkey,actid);
1054 resultout := 'COMPLETE';
1055 ELSE
1056 resultout := 'COMPLETE';
1057 return;
1058 END IF;
1059
1060
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 wf_core.context('XDPSTATUS', 'SET_NODE_WI_STATUS', itemtype, itemkey, to_char(actid), funcmode);
1064 raise;
1065 END SET_NODE_WI_STATUS;
1066
1067
1068
1069
1070 Function GetResubmissionJobID (itemtype in varchar2,
1071 itemkey in varchar2) return number is
1072 l_JobID number;
1073 e_UnhandledException exception;
1074 begin
1075 begin
1076 l_JobID := wf_engine.GetItemAttrNumber(itemtype => GetResubmissionJobID.itemtype,
1077 itemkey => GetResubmissionJobID.itemkey,
1078 aname => 'RESUBMISSION_JOB_ID');
1079
1080 exception
1081 when others then
1082 if sqlcode = -20002 then
1083 l_JobID := 0;
1084 wf_core.clear;
1085 else
1086 raise e_UnhandledException;
1087 end if;
1088 end;
1089
1090 return l_JobID;
1091
1092 End GetResubmissionJobID;
1093
1094 PROCEDURE SetErrorStatus(itemtype IN VARCHAR2,
1095 itemkey IN VARCHAR2,
1096 actid IN NUMBER)
1097 IS
1098
1099 l_error_itemtype VARCHAR2(240);
1100 l_error_itemkey VARCHAR2(240);
1101 l_OrderID NUMBER;
1102 l_lineitemId NUMBER ;
1103 l_WIInstanceID NUMBER ;
1104 l_FAInstanceID NUMBER ;
1105 l_order_count NUMBER;
1106 l_line_count NUMBER;
1107 l_pkg_count NUMBER;
1108 l_workitem_count NUMBER;
1109 l_fa_count NUMBER;
1110 l_status VARCHAR2(40);
1111 l_url VARCHAR2(1000);
1112
1113 BEGIN
1114 l_error_itemtype := WF_ENGINE.GETITEMATTRTEXT(itemtype => SetErrorStatus.itemtype,
1115 itemkey => SetErrorStatus.itemkey,
1116 aname => 'ERROR_ITEM_TYPE');
1117
1118 l_error_itemkey := WF_ENGINE.GETITEMATTRTEXT(itemtype => SetErrorStatus.itemtype,
1119 itemkey => SetErrorStatus.itemkey,
1120 aname => 'ERROR_ITEM_KEY');
1121
1122 l_status := WF_ENGINE.GetActivityattrtext(itemtype =>SetErrorStatus.itemtype,
1123 itemkey =>SetErrorStatus.itemkey,
1124 actid =>SetErrorStatus.actid,
1125 aname =>'STATUS');
1126
1127 l_order_count := instr(l_error_itemkey,'MAIN') ;
1128 l_line_count := instr(l_error_itemkey,'SVC') ;
1129 l_pkg_count := instr(l_error_itemkey,'LINE');
1130 l_workitem_count := instr(l_error_itemkey,'WI') ;
1131 l_fa_count := instr(l_error_itemkey,'FA') ;
1132
1133 IF l_order_count > 0 THEN
1134 l_OrderID := WF_ENGINE.GETITEMATTRNUMBER(itemtype => l_error_itemtype,
1135 itemkey => l_error_itemkey,
1136 aname => 'ORDER_ID');
1137 UPDATE_XDP_ORDER_STATUS(l_status,l_orderID );
1138
1139 ELSIF (l_line_count > 0 OR l_pkg_count > 0 ) THEN
1140 l_lineitemId := WF_ENGINE.GETITEMATTRNUMBER(itemtype => l_error_itemtype,
1141 itemkey => l_error_itemkey,
1142 aname => 'LINE_ITEM_ID');
1143 UPDATE_XDP_ORDER_LINE_STATUS(l_status,l_lineitemID);
1144
1145 ELSIF (l_workitem_count > 0 AND l_fa_count = 0) THEN
1146
1147 l_OrderID := WF_ENGINE.GETITEMATTRNUMBER(itemtype => l_error_itemtype,
1148 itemkey => l_error_itemkey,
1149 aname => 'ORDER_ID');
1150
1151 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => l_error_itemtype,
1152 itemkey => l_error_itemkey,
1153 aname => 'WORKITEM_INSTANCE_ID');
1154
1155 UPDATE_XDP_WORKITEM_STATUS(l_status,l_WIInstanceID);
1156
1157 XDP_NOTIFICATIONS.Get_WI_Update_URL(l_WIInstanceID,
1158 l_OrderID,
1159 itemtype,
1160 itemKey,
1161 l_url);
1162 --10/04/2002 HBCHUNG
1163 --setting Notification URL
1164 wf_engine.SetItemAttrText(itemtype => itemtype,
1165 itemkey => itemkey,
1166 aname => 'ERROR_WI_UPDATE_URL',
1167 avalue => l_url);
1168
1169
1170
1171 ELSIF l_fa_count > 0 THEN
1172 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => l_error_itemtype,
1173 itemkey => l_error_itemkey,
1174 aname => 'FA_INSTANCE_ID');
1175 UPDATE_XDP_FA_STATUS(l_status,l_FAInstanceID);
1176 END IF;
1177
1178 EXCEPTION
1179 WHEN others THEN
1180 wf_core.context('XDPSTATUS', 'SetErrorStatus', itemtype, itemkey, null,null);
1181 RAISE;
1182 END SetErrorStatus;
1183
1184 PROCEDURE SETORDERSTATUS(itemtype IN VARCHAR2,
1185 itemkey IN VARCHAR2)
1186 IS
1187 l_OrderID NUMBER;
1188 l_WorkitemStatus VARCHAR2(30);
1189 l_Status VARCHAR2(30);
1190 l_AbortCount NUMBER := 0;
1191 l_ErrorCount NUMBER := 0;
1192 l_overridecount NUMBER := 0;
1193 l_Counter NUMBER := 0;
1194 l_status_code VARCHAR2(30);
1195
1196 CURSOR c_getlines(orderid NUMBER) IS
1197 SELECT status_code
1198 FROM xdp_order_line_items
1199 WHERE order_id = orderid ;
1200
1201 BEGIN
1202
1203 l_OrderID := WF_ENGINE.GETITEMATTRNUMBER(itemtype => SetOrderStatus.itemtype,
1204 itemkey => SetOrderStatus.itemkey,
1205 aname => 'ORDER_ID');
1206
1207
1208 IF c_GetLines%ISOPEN THEN
1209 CLOSE c_GetLines;
1210 END IF;
1211
1212
1213 OPEN c_GetLines(l_OrderID);
1214
1215 LOOP
1216 FETCH c_GetLines INTO l_Status;
1217 EXIT WHEN c_GetLines%NOTFOUND;
1218
1219 l_Counter := l_Counter + 1;
1220
1221 IF l_Status = 'ERROR' THEN
1222 l_ErrorCount := l_ErrorCount + 1;
1223 END IF;
1224
1225 IF l_Status = 'ABORTED' THEN
1226 l_AbortCount := l_AbortCount + 1;
1227 END IF;
1228
1229 IF l_Status = 'SUCCESS_WITH_OVERRIDE' THEN
1230 l_overridecount := l_overridecount + 1;
1231 END IF;
1232
1233 END LOOP;
1234
1235 IF l_ErrorCount > 0 THEN
1236
1237 UPDATE_XDP_ORDER_STATUS('ERROR' , l_orderid );
1238
1239 ELSIF l_overridecount > 0 THEN
1240
1241 UPDATE_XDP_ORDER_STATUS('SUCCESS_WITH_OVERRIDE' , l_orderid );
1242
1243 ELSIF l_AbortCount > 0 AND l_Counter = l_AbortCount THEN
1244
1245 UPDATE_XDP_ORDER_STATUS('ABORTED' , l_orderid );
1246 XDP_ENGINE.Set_Order_Param_Value(l_orderid,'FULFILLMENT_STATUS','ABORTED');
1247 ELSE
1248 UPDATE_XDP_ORDER_STATUS('SUCCESS' , l_orderid );
1249
1250 END IF;
1251
1252 COMMIT;
1253 EXCEPTION
1254 WHEN others THEN
1255 wf_core.context('XDPSTATUS', 'SetOrderStatus', itemtype, itemkey, null,null);
1256 RAISE;
1257 END SetOrderStatus;
1258
1259
1260
1261 PROCEDURE SendOrderStatus(itemtype in varchar2,
1262 itemkey in varchar2,
1263 actid in number)
1264 is
1265
1266 l_OrderID number;
1267
1268 l_Message varchar2(80);
1269 l_SendFlag varchar2(10);
1270
1271 begin
1272
1273 l_SendFlag := wf_engine.GetActivityAttrText( itemtype => SendOrderStatus.itemtype,
1274 itemkey => SendOrderStatus.itemkey,
1275 actid => SendOrderStatus.actid,
1276 aname => 'SEND_FLAG');
1277
1278 /* Only if the SEND_FLAG is set to 'Y' will the message be sent */
1279
1280 if l_SendFlag = 'Y' then
1281
1282 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => SendOrderStatus.itemtype,
1283 itemkey => SendOrderStatus.itemkey,
1284 aname => 'ORDER_ID');
1285
1286 /* Enqueue into the Outboud Queue with the appropriate message */
1287 l_Message := wf_engine.GetActivityAttrText( itemtype => SendOrderStatus.itemtype,
1288 itemkey => SendOrderStatus.itemkey,
1289 actid => SendOrderStatus.actid,
1290 aname => 'MESSAGE');
1291
1292 if l_Message is null then
1293 l_Message := 'ORDER_STATUS';
1294 end if;
1295
1296 /* Enqueue into the Outbound queue */
1297
1298 /*
1299 SDP_AQ_UTILITIES.Enqeuee....
1300 */
1301
1302 end if;
1303
1304 exception
1305 when others then
1306 wf_core.context('XDPSTATUS', 'SendOrderStatus', itemtype, itemkey, to_char(actid),null);
1307 raise;
1308 end SendOrderStatus;
1309
1310
1311
1312
1313
1314 PROCEDURE SETWORKITEMSTATUS(itemtype IN VARCHAR2,
1315 itemkey IN VARCHAR2)
1316 IS
1317 l_WIInstanceID NUMBER;
1318 l_dummy NUMBER;
1319 l_Counter NUMBER := 0;
1320 l_ErrorCount NUMBER := 0;
1321 l_AbortCount NUMBER := 0;
1322 l_RunningCount NUMBER := 0;
1323 l_overridecount NUMBER := 0;
1324 l_status_code VARCHAR2(30);
1325 l_Status VARCHAR2(30);
1326 l_FAInstanceID NUMBER;
1327
1328
1329 CURSOR c_GetFA(WIInstanceID NUMBER) IS
1330 SELECT STATUS_CODE,
1331 FA_INSTANCE_ID
1332 FROM XDP_FA_RUNTIME_LIST
1333 WHERE WORKITEM_INSTANCE_ID = WIInstanceID ;
1334
1335 BEGIN
1336
1337 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SetWorkitemStatus.itemtype,
1338 itemkey => SetWorkitemStatus.itemkey,
1339 aname => 'WORKITEM_INSTANCE_ID');
1340
1341
1342 IF c_GetFA%ISOPEN THEN
1343 CLOSE c_GetFA;
1344 END IF;
1345
1346 OPEN c_GetFA(l_WIInstanceID);
1347
1348 LOOP
1349 FETCH c_GetFA
1350 INTO l_Status, l_FAInstanceID ;
1351 EXIT WHEN c_GetFA%NOTFOUND;
1352 l_Counter := l_Counter + 1;
1353 IF l_Status = 'ERROR' THEN
1354 l_ErrorCount := l_ErrorCount + 1;
1355 END IF;
1356
1357 IF l_Status = 'ABORTED' THEN
1358 l_AbortCount := l_AbortCount + 1;
1359 END IF;
1360
1361 IF l_Status = 'SUCCESS_WITH_OVERRIDE' THEN
1362 l_overridecount := l_overridecount + 1;
1363 END IF;
1364
1365 END LOOP;
1366
1367 CLOSE c_GetFA;
1368
1369 IF l_ErrorCount > 0 then
1370
1371 UPDATE_XDP_WORKITEM_STATUS('ERROR' , l_WIInstanceid );
1372
1373 ELSIF l_overridecount > 0 then
1374
1375 UPDATE_XDP_WORKITEM_STATUS('SUCCESS_WITH_OVERRIDE' , l_WIInstanceid );
1376
1377 ELSIF l_AbortCount > 0 AND l_AbortCount = l_Counter then
1378
1379 UPDATE_XDP_WORKITEM_STATUS('ABORTED' , l_WIInstanceid );
1380
1381 ELSE
1382 UPDATE_XDP_WORKITEM_STATUS('SUCCESS' , l_WIInstanceid );
1383 END IF;
1384
1385 EXCEPTION
1386 WHEN others THEN
1387 wf_core.context('XDPSTATUS', 'SetWorkitemStatus', itemtype, itemkey, null,null);
1388 RAISE;
1389 END SETWORKITEMSTATUS;
1390
1391
1392
1393 PROCEDURE SetWIStatusSuccess (itemtype IN VARCHAR2,
1394 itemkey IN VARCHAR2)
1395 IS
1396 l_WIInstanceID NUMBER;
1397 BEGIN
1398
1399 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SetWIStatusSuccess.itemtype,
1400 itemkey => SetWIStatusSuccess.itemkey,
1401 aname => 'WORKITEM_INSTANCE_ID');
1402
1403 UPDATE_XDP_WORKITEM_STATUS('SUCCESS' , l_WIInstanceID );
1404
1405 EXCEPTION
1406 WHEN others THEN
1407 wf_core.context('XDPSTATUS', 'SetWIStatusSuccess', itemtype, itemkey, null,null);
1408 RAISE;
1409 END SetWIStatusSuccess;
1410
1411
1412
1413
1414 Procedure SendWorkitemStatus(itemtype in varchar2,
1415 itemkey in varchar2,
1416 actid in number)
1417 is
1418
1419 l_OrderID number;
1420 l_WorkitemStatus varchar2(80);
1421 l_WIInstanceID number;
1422
1423 l_Message varchar2(80);
1424 l_SendFlag varchar2(10);
1425
1426 begin
1427
1428 l_SendFlag := wf_engine.GetActivityAttrText( itemtype => SendWorkitemStatus.itemtype,
1429 itemkey => SendWorkitemStatus.itemkey,
1430 actid => SendWorkitemStatus.actid,
1431 aname => 'SEND_FLAG');
1432
1433
1434 /* Only if the SEND_FLAG is 'Y' will the message be sent */
1435
1436 if l_SendFlag = 'Y' then
1437
1438 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => SendWorkitemStatus.itemtype,
1439 itemkey => SendWorkitemStatus.itemkey,
1440 aname => 'ORDER_ID');
1441
1442 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SendWorkitemStatus.itemtype,
1443 itemkey => SendWorkitemStatus.itemkey,
1444 aname => 'WORKITEM_INSTANCE_ID');
1445
1446
1447 /* Enqueue into the Outboud Queue with the appropriate message */
1448 l_Message := wf_engine.GetActivityAttrText( itemtype => SendWorkitemStatus.itemtype,
1449 itemkey => SendWorkitemStatus.itemkey,
1450 actid => SendWorkitemStatus.actid,
1451 aname => 'MESSAGE');
1452
1453 if l_Message is null then
1454 l_Message := 'WORKITEM_STATUS';
1455 end if;
1456
1457 /* Enqueue into the Outbound queue */
1458
1459 /*
1460 SDP_AQ_UTILITIES.Enqeuee....
1461 */
1462
1463 end if;
1464
1465
1466
1467 exception
1468 when others then
1469 wf_core.context('XDPSTATUS', 'SendWorkitemStatus', itemtype, itemkey, to_char(actid),null);
1470 raise;
1471 end SendWorkitemStatus;
1472
1473
1474
1475
1476 PROCEDURE SetFAStatus(itemtype IN VARCHAR2,
1477 itemkey IN VARCHAR2,
1478 actid IN NUMBER)
1479 IS
1480
1481 l_FAStatus VARCHAR2(80);
1482 l_FAInstanceID NUMBER;
1483 l_WIInstanceID NUMBER;
1484 l_OrderID NUMBER;
1485 l_LineItemID NUMBER;
1486 l_ResubmissionJobID NUMBER;
1487 ErrCode NUMBER;
1488 ErrStr VARCHAR(2000);
1489 l_Event VARCHAR2(80);
1490 l_ProvMode VARCHAR2(80);
1491 x_progress VARCHAR2(2000);
1492
1493 e_SetFAStatusException EXCEPTION;
1494
1495 BEGIN
1496
1497 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => SetFAStatus.itemtype,
1498 itemkey => SetFAStatus.itemkey,
1499 aname => 'FA_INSTANCE_ID');
1500
1501 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => SetFAStatus.itemtype,
1502 itemkey => SetFAStatus.itemkey,
1503 aname => 'ORDER_ID');
1504
1505 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SetFAStatus.itemtype,
1506 itemkey => SetFAStatus.itemkey,
1507 aname => 'WORKITEM_INSTANCE_ID');
1508
1509 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => SetFAStatus.itemtype,
1510 itemkey => SetFAStatus.itemkey,
1511 aname => 'LINE_ITEM_ID');
1512
1513 l_FAStatus := wf_engine.GetActivityAttrText( itemtype => SetFAStatus.itemtype,
1514 itemkey => SetFAStatus.itemkey,
1515 actid => SetFAStatus.actid,
1516 aname => 'FA_STATUS');
1517
1518 l_ResubmissionJobID := GetResubmissionJobID(itemtype => SetFAStatus.itemtype,
1519 itemkey => SetFAStatus.itemkey);
1520
1521 l_Event := wf_engine.GetActivityAttrText( itemtype => SetFAStatus.itemtype,
1522 itemkey => SetFAStatus.itemkey,
1523 actid => SetFAStatus.actid,
1524 aname => 'NOTIF_RESPONSE');
1525
1526 /* Check if the FA is aborted. If so set the status of execution and abort yourself*/
1527
1528 IF XDPCORE_FA.IsFAAborted(l_FAInstanceID) = TRUE THEN
1529
1530 UPDATE_XDP_FA_STATUS(l_FAStatus,l_FAInstanceID);
1531
1532 wf_engine.AbortProcess(itemtype => SetFAStatus.itemtype,
1533 itemkey => SetFAStatus.itemkey);
1534
1535 return;
1536 else
1537 /*
1538 ** If the Processing of the FA is Order Resubmission Only the FA Status is to be updated
1539 ** The complete Order status/state logic is to be ignored
1540 */
1541
1542 IF l_ResubmissionJobID <>0 THEN
1543 SetFActionStatus(l_OrderID, l_LineItemID, l_WIInstanceID, l_FAInstanceID, 'FA_RE_EXECUTION', l_Event, 'FA_'||l_FAStatus, SetFAStatus.itemtype,SetFAStatus.itemkey,ErrCode, ErrStr);
1544 ELSE
1545 SetFActionStatus(l_OrderID, l_LineItemID, l_WIInstanceID, l_FAInstanceID, 'FA_EXECUTION', l_Event, 'FA_'||l_FAStatus, SetFAStatus.itemtype,SetFAStatus.itemkey,ErrCode, ErrStr);
1546 END IF;
1547
1548 IF ErrCode <>0 THEN
1549 x_progress := 'In XDP_STATUS.SetFActionStatus. Error when updating status. Error: ' || SUBSTR(ErrStr, 1, 1500);
1550 RAISE e_SetFAStatusException;
1551 END IF;
1552
1553 END If;
1554
1555
1556 EXCEPTION
1557 WHEN e_SetFAStatusException THEN
1558 wf_core.context('XDPSTATUS', 'SetFAStatus', itemtype, itemkey, to_char(actid), x_progress);
1559 RAISE;
1560 WHEN others THEN
1561 wf_core.context('XDPSTATUS', 'SetFAStatus', itemtype, itemkey, to_char(actid),null);
1562 RAISE;
1563 END SetFAStatus;
1564
1565
1566
1567 Procedure SetFeExecStatus(itemtype in varchar2,
1568 itemkey in varchar2)
1569 is
1570 begin
1571
1572 null;
1573
1574 exception
1575 when others then
1576 wf_core.context('XDPSTATUS', 'SetFeExecStatus', itemtype, itemkey, null,null);
1577 raise;
1578 end SetFeExecStatus;
1579
1580
1581
1582
1583 Procedure SendFeProvStatus(itemtype in varchar2,
1584 itemkey in varchar2,
1585 actid in number)
1586
1587 is
1588
1589 l_OrderID number;
1590 l_WIInstanceID number;
1591 l_FAInstanceID number;
1592 l_dummy1 number;
1593
1594 l_FaCaller varchar2(10);
1595 l_ErrCode number;
1596 l_ErrDescription varchar2(800);
1597
1598 l_Message varchar2(80);
1599 l_SendFlag varchar2(10);
1600 l_FeExecStatus varchar2(40);
1601 l_FAStatus varchar2(40);
1602
1603 cursor cur_fa_status( cp_FAInstanceID in VARCHAR2 ) IS
1604 SELECT status_code
1605 FROM xdp_fa_runtime_list
1606 WHERE fa_instance_id = cp_FAInstanceID;
1607
1608 e_SendMessageException exception;
1609
1610 x_progress varchar2(2000);
1611
1612 begin
1613
1614 l_FAInstanceID := wf_engine.GetItemAttrNumber(itemtype => SendFeProvStatus.itemtype,
1615 itemkey => SendFeProvStatus.itemkey,
1616 aname => 'FA_INSTANCE_ID');
1617
1618 --From now on we will get this from runtime table.
1619 --Before we are doing it using Activity Attribute..
1620 FOR lv_fa_rec in cur_fa_status( l_FAInstanceID ) LOOP
1621 l_FeExecStatus := lv_fa_rec.STATUS_CODE;
1622 END LOOP;
1623
1624 -- We reach this node only when the FA is success or
1625 -- Aborted. In Success with override case we have to publish
1626 -- this as SUCCESS case only, then only the user defined
1627 -- workflows will go thru as there will be either
1628 -- SUCCESS or ABORTED transitions..
1629 IF l_FeExecStatus <> 'ABORTED' THEN
1630 l_FeExecStatus := 'SUCCESS';
1631 END IF;
1632
1633 /* Check if the FA is aborted. If so set the status of execution and abort yourself*/
1634
1635 if XDPCORE_FA.IsFAAborted(l_FAInstanceID) = TRUE then
1636
1637 UPDATE_XDP_FA_STATUS(l_FeExecStatus,l_FAInstanceID);
1638
1639 wf_engine.AbortProcess(itemtype => SendFeProvStatus.itemtype,
1640 itemkey => SendFeProvStatus.itemkey);
1641
1642 else
1643
1644 l_SendFlag := wf_engine.GetActivityAttrText( itemtype => SendFeProvStatus.itemtype,
1645 itemkey => SendFeProvStatus.itemkey,
1646 actid => SendFeProvStatus.actid,
1647 aname => 'SEND_FLAG');
1648
1649 /* Only if the Send Flag has been set to be 'Y' the message will be sent */
1650 if l_SendFlag = 'Y' then
1651
1652 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SendFeProvStatus.itemtype,
1653 itemkey => SendFeProvStatus.itemkey,
1654 aname => 'WORKITEM_INSTANCE_ID');
1655
1656
1657
1658 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => SendFeProvStatus.itemtype,
1659 itemkey => SendFeProvStatus.itemkey,
1660 aname => 'ORDER_ID');
1661
1662
1663 l_Message := wf_engine.GetActivityAttrText( itemtype => SendFeProvStatus.itemtype,
1664 itemkey => SendFeProvStatus.itemkey,
1665 actid => SendFeProvStatus.actid,
1666 aname => 'MESSAGE');
1667
1668 if l_Message is null then
1669 l_Message := 'FE_EXEC_DONE';
1670 end if;
1671
1672 -- if defined publish the status that user wants..
1673 -- bug fix 2617807. In case user is not publishing the FA_STATUS(ER#2350281)
1674 -- catch 1403 and ignore..
1675 BEGIN
1676 l_FAStatus := xdp_engine.get_fa_param_value( l_FAInstanceID, 'FA_STATUS' );
1677 EXCEPTION
1678 WHEN NO_DATA_FOUND THEN
1679 NULL;
1680 WHEN OTHERS THEN
1681 RAISE;
1682 END;
1683 IF( l_FAStatus is NOT NULL ) THEN
1684 l_FeExecStatus := l_FAStatus;
1685 END IF;
1686
1687 /* Enqueue into the Message into event manager queue */
1688
1689 XNP_FA_DONE_U.Publish(XNP$SDP_RESULT_CODE => l_FeExecStatus,
1690 X_MESSAGE_ID => l_dummy1,
1691 X_ERROR_CODE => l_ErrCode,
1692 X_ERROR_MESSAGE =>l_ErrDescription,
1693 P_CONSUMER_LIST => null,
1694 P_SENDER_NAME => null,
1695 P_RECIPIENT_LIST => null,
1696 P_VERSION => null,
1697 P_REFERENCE_ID => l_FAInstanceID,
1698 P_OPP_REFERENCE_ID => null,
1699 P_ORDER_ID => l_OrderID,
1700 P_WI_INSTANCE_ID => l_WIInstanceID,
1701 P_FA_INSTANCE_ID=> l_FAInstanceID);
1702
1703 if l_ErrCode <> 0 then
1704 x_progress := 'Error when trying to send FA_DONE Message. Code: ' ||
1705 to_char(l_ErrCode) || ' Error String: ' ||
1706 substr(l_ErrDescription, 1, 1000);
1707 Raise e_SendMessageException;
1708 end if;
1709
1710 end if;
1711 end if;
1712
1713 exception
1714 when e_SendMessageException then
1715 wf_core.context('XDPSTATUS', 'SendFeProvStatus', itemtype, itemkey, to_char(actid), x_progress);
1716 raise;
1717 when others then
1718 wf_core.context('XDPSTATUS', 'SendFeProvStatus', itemtype, itemkey, to_char(actid),null);
1719 raise;
1720 end SendFeProvStatus;
1721
1722
1723 PROCEDURE UpdateFAStatus( faid IN NUMBER,
1724 status IN VARCHAR2,
1725 provmode IN VARCHAR2)
1726 IS
1727 l_status_code VARCHAR2(30);
1728
1729 BEGIN
1730 IF status = 'SUCCESS' THEN
1731
1732 /*
1733 * FA Execution is successful
1734 */
1735
1736 /* The FA was retried and then success ful hence the status should be SUCCESS_WITH_OVERRIDE */
1737
1738 IF provmode = 'RETRY' THEN
1739
1740 UPDATE_XDP_FA_STATUS('SUCCESS_WITH_OVERRIDE' , faid) ;
1741 ELSE
1742 UPDATE_XDP_FA_STATUS('SUCCESS' , faid) ;
1743 END IF;
1744
1745 ELSE
1746 UPDATE_XDP_FA_STATUS('SUCCESS' , faid) ;
1747
1748 END IF;
1749
1750 EXCEPTION
1751 WHEN others THEN
1752 wf_core.context('XDPSTATUS', 'UpdateFAStatus', null, null, null,null);
1753 RAISE;
1754 END UpdateFAStatus;
1755
1756
1757 Procedure SendLineStatus(itemtype in varchar2,
1758 itemkey in varchar2,
1759 actid in number)
1760 is
1761
1762 begin
1763 null;
1764
1765 exception
1766 when others then
1767 wf_core.context('XDPSTATUS', 'SendLineStatus', null, null, null,null);
1768 raise;
1769 end SendLineStatus;
1770
1771
1772 Procedure SetBundleStatus (itemtype in varchar2,
1773 itemkey in varchar2)
1774 is
1775
1776 begin
1777 null;
1778
1779 exception
1780 when others then
1781 wf_core.context('XDPSTATUS', 'SetBundleStatus', null, null, null,null);
1782 raise;
1783 end SetBundleStatus;
1784
1785
1786 PROCEDURE SetLineStatus (itemtype IN VARCHAR2,
1787 itemkey IN VARCHAR2)
1788 IS
1789
1790 l_LineItemID NUMBER;
1791 l_WIInstanceID NUMBER;
1792 l_OrderID NUMBER;
1793 l_dummy NUMBER;
1794 l_ErrorCount NUMBER := 0;
1795 l_AbortCount NUMBER := 0;
1796 l_Counter NUMBER := 0;
1797 l_overridecount NUMBER := 0;
1798 l_Status VARCHAR2(40);
1799 l_Status_code VARCHAR2(40);
1800
1801
1802 CURSOR c_GetWI(OrderID number, LineItemID number) IS
1803 SELECT STATUS_CODE,
1804 WORKITEM_INSTANCE_ID
1805 FROM XDP_FULFILL_WORKLIST
1806 WHERE ORDER_ID = OrderID
1807 AND LINE_ITEM_ID = LineItemID ;
1808
1809 BEGIN
1810
1811 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => SetLineStatus.itemtype,
1812 itemkey => SetLineStatus.itemkey,
1813 aname => 'ORDER_ID');
1814
1815 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => SetLineStatus.itemtype,
1816 itemkey => SetLineStatus.itemkey,
1817 aname => 'LINE_ITEM_ID');
1818
1819 IF c_GetWI%ISOPEN THEN
1820 CLOSE c_GetWI;
1821 END IF;
1822
1823 OPEN c_GetWI(l_OrderID, l_LineItemID);
1824
1825 LOOP
1826 FETCH c_GetWI INTO l_Status, l_WIInstanceID;
1827 EXIT WHEN c_GetWI%NOTFOUND;
1828
1829 l_Counter := l_Counter + 1;
1830
1831 IF l_Status = 'ERROR' THEN
1832 l_ErrorCount := l_ErrorCount + 1;
1833 END IF;
1834
1835 IF l_status = 'SUCCESS_WITH_OVERRIDE' THEN
1836 l_overridecount := l_overridecount + 1 ;
1837 END IF ;
1838
1839 IF l_Status = 'ABORTED' THEN
1840 l_AbortCount := l_AbortCount + 1;
1841 END IF;
1842
1843 END LOOP;
1844
1845 CLOSE c_GetWI;
1846
1847 IF l_ErrorCount > 0 THEN
1848 UPDATE_XDP_ORDER_LINE_STATUS('ERROR' , l_lineitemid );
1849
1850 ELSIF l_AbortCount > 0 AND l_Counter = l_AbortCount THEN
1851 UPDATE_XDP_ORDER_LINE_STATUS('ABORTED' , l_lineitemid );
1852
1853 ELSIF l_overridecount > 0 THEN
1854 UPDATE_XDP_ORDER_LINE_STATUS('SUCCESS_WITH_OVERRIDE' , l_lineitemid );
1855
1856 ELSE
1857 UPDATE_XDP_ORDER_LINE_STATUS('SUCCESS' , l_lineitemid );
1858
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN others THEN
1863 wf_core.context('XDPSTATUS', 'SetLineStatus', null, null, null,null);
1864 RAISE;
1865 END SetLineStatus;
1866
1867
1868
1869 Procedure SetPackageStatus (itemtype in varchar2,
1870 itemkey in varchar2)
1871 is
1872
1873 begin
1874 null;
1875
1876 exception
1877 when others then
1878 wf_core.context('XDPSTATUS', 'SetPackageStatus', null, null, null,null);
1879 raise;
1880 end SetPackageStatus;
1881
1882
1883 Procedure SaveWorkitem (itemtype in varchar2,
1884 itemkey in varchar2,
1885 actid in number)
1886 IS
1887 l_WIInstanceID number;
1888
1889 l_Status varchar2(40);
1890
1891 BEGIN
1892
1893
1894 l_Status := wf_engine.GetActivityAttrText( itemtype => SaveWorkitem.itemtype,
1895 itemkey => SaveWorkitem.itemkey,
1896 actid => SaveWorkitem.actid,
1897 aname => 'WORKITEM_STATUS');
1898
1899 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SaveWorkitem.itemtype,
1900 itemkey => SaveWorkitem.itemkey,
1901 aname => 'WORKITEM_INSTANCE_ID');
1902
1903 UPDATE_XDP_WORKITEM_STATUS(l_Status, l_WIINstanceID);
1904
1905 EXCEPTION
1906 WHEN others THEN
1907 wf_core.context('XDPSTATUS', 'SaveWorkitem', itemtype, itemkey, to_char(actid), null);
1908 raise;
1909 END SaveWorkitem;
1910
1911
1912 Procedure SetFActionStatus (OrderID IN NUMBER,
1913 LineItemID IN NUMBER,
1914 WorkitemInstanceID IN NUMBER,
1915 FAInstanceID IN NUMBER,
1916 Caller IN VARCHAR2,
1917 Event IN VARCHAR2,
1918 Status IN VARCHAR2,
1919 ItemType IN VARCHAR2,
1920 Itemkey IN VARCHAR2,
1921 ErrCode OUT NOCOPY NUMBER,
1922 ErrStr OUT NOCOPY VARCHAR2)
1923 IS
1924 x_progress VARCHAR2(2000);
1925
1926 BEGIN
1927
1928 IF Caller = 'FA_EXECUTION' THEN
1929
1930 IF Status = 'FA_ERROR' AND Event IS null THEN
1931 /* Set the Status of the FA just to be Error */
1932 SetStatusForFA(FAInstanceID, 'ERROR', null);
1933 -- SetStatusForWI(WorkitemInstanceID, 'ERROR', null);
1934 -- SetStatusForLine(OrderID, WorkitemInstanceID, LineItemID, 'ERROR', null);
1935 -- SetStatusForOrder(OrderID, 'ERROR', null);
1936 ELSIF Event = 'CONTINUE_FA_PROCESSING' THEN
1937 /* The Caller had done a Continue from the FMC */
1938 SetStatusForFA(FAInstanceID, 'SUCCESS_WITH_OVERRIDE', 'CONTINUE_FA_PROCESSING');
1939 -- SetStatusForWI(WorkitemInstanceID, 'SUCCESS', null);
1940 -- SetStatusForLine(OrderID, WorkitemInstanceID, LineItemID, 'SUCCESS', null);
1941 -- SetStatusForOrder(OrderID, 'SUCCESS', null);
1942 ELSIF Event = 'STOP_FA_PROCESSING' THEN
1943 /* User selected Abort FA Processing from FMC */
1944 SetStatusForFA(FAInstanceID, 'ABORTED', 'STOP_FA_PROCESSING');
1945 -- SetStatusForWI(WorkitemInstanceID, 'SUCCESS', null);
1946 -- SetStatusForLine(OrderID, WorkitemInstanceID, LineItemID, 'SUCCESS', null);
1947 -- SetStatusForOrder(OrderID, 'SUCCESS', null);
1948 ELSIF Status = 'FA_SUCCESS' THEN
1949 SetStatusForFA(FAInstanceID, 'SUCCESS', null);
1950 -- SetStatusForWI(WorkitemInstanceID, 'SUCCESS', null);
1951 -- SetStatusForLine(OrderID, WorkitemInstanceID, LineItemID, 'SUCCESS', null);
1952 -- SetStatusForOrder(OrderID, 'SUCCESS', null);
1953 ELSIF Status = 'FA_IN_PROGRESS' THEN
1954 SetStatusForFA(FAInstanceID,'IN_PROGRESS',null);
1955 ELSIF Status = 'FA_SYSTEM_HOLD' THEN
1956 SetStatusForFA(FAInstanceID,'SYSTEM_HOLD',null);
1957 END IF;
1958 ELSIF Caller = 'FA_RE_EXECUTION' THEN
1959 IF Status = 'FA_ERROR' AND Event IS null THEN
1960 /* Set the Status of the FA just to be Error */
1961 SetStatusForFA(FAInstanceID, 'ERROR', null);
1962 ELSIF Event = 'CONTINUE_FA_PROCESSING' THEN
1963 /* The Caller had done a Continue from the FMC */
1964 SetStatusForFA(FAInstanceID, 'SUCCESS_WITH_OVERRIDE', 'CONTINUE_FA_PROCESSING');
1965 ELSIF Event = 'STOP_FA_PROCESSING' THEN
1966 /* User selected Abort FA Processing from FMC */
1967 SetStatusForFA(FAInstanceID, 'ABORTED', 'STOP_FA_PROCESSING');
1968 ELSIF Status = 'FA_SUCCESS' THEN
1969 SetStatusForFA(FAInstanceID, 'SUCCESS', null);
1970 -- SetStatusForWI(WorkitemInstanceID, 'SUCCESS', null);
1971 -- SetStatusForLine(OrderID, WorkitemInstanceID, LineItemID, 'SUCCESS', null);
1972 -- SetStatusForOrder(OrderID, 'SUCCESS', null);
1973 ELSIF Status = 'FA_IN_PROGRESS' THEN
1974 SetStatusForFA(FAInstanceID,'IN_PROGRESS',null);
1975 ELSIF Status = 'FA_SYSTEM_HOLD' THEN
1976 SetStatusForFA(FAInstanceID,'SYSTEM_HOLD',null);
1977 END IF;
1978 ELSIF Caller = 'API_EXEC' THEN
1979 null;
1980 END IF;
1981
1982 EXCEPTION
1983 WHEN others THEN
1984 x_progress := 'In XDPSTATUS.SetFActionStatus. Unhandled Exception. Error: ' || SUBSTR(SQLERRM, 1, 1500);
1985 wf_core.context('XDPSTATUS', 'SetFActionStatus',itemtype, itemkey, null, x_progress);
1986 RAISE;
1987 END SetFActionStatus;
1988
1989
1990
1991
1992
1993
1994 PROCEDURE SetStatusForFA (FAInstanceID IN NUMBER,
1995 Status IN VARCHAR2,
1996 Event IN VARCHAR2)
1997 IS
1998
1999 l_FAStatus VARCHAR2(40);
2000 l_FAState VARCHAR2(40);
2001 l_fA_status_code VARCHAR2(40);
2002
2003 CURSOR c_GetFaStat IS
2004 SELECT Status_code
2005 FROM XDP_FA_RUNTIME_LIST
2006 WHERE FA_INSTANCE_ID = FAInstanceID;
2007 -- FOR UPDATE;
2008
2009 x_progress VARCHAR2(2000);
2010
2011 e_InvalidConfigException EXCEPTION;
2012
2013 BEGIN
2014
2015 OPEN c_GetFaStat;
2016 FETCH c_GetFaStat INTO l_FAStatus;
2017
2018 IF c_GetFAStat%NOTFOUND THEN
2019 RAISE e_InvalidConfigException;
2020 END IF;
2021
2022 IF Event is null AND Status = 'ERROR' THEN
2023
2024 UPDATE_XDP_FA_STATUS(status , FAInstanceId );
2025
2026 ELSIF Event IS NULL AND Status = 'SUCCESS' THEN
2027 IF l_FAStatus = 'ERROR' THEN
2028
2029 UPDATE_XDP_FA_STATUS('SUCCESS_WITH_OVERRIDE' , FAInstanceId );
2030
2031 ELSE
2032 UPDATE_XDP_FA_STATUS('SUCCESS' , FAInstanceId );
2033
2034 END IF;
2035
2036 ELSIF Event = 'CONTINUE_FA_PROCESSING' THEN
2037 UPDATE_XDP_FA_STATUS(status , FAInstanceId );
2038
2039 ELSIF Event = 'STOP_FA_PROCESSING' THEN
2040 UPDATE_XDP_FA_STATUS(status , FAInstanceId );
2041 ELSIF Status = 'IN_PROGRESS' THEN
2042 UPDATE_XDP_FA_STATUS('IN PROGRESS' , FAInstanceId );
2043 ELSIF Status = 'FA_SYSTEM_HOLD' THEN
2044 UPDATE_XDP_FA_STATUS('SYSTEM_HOLD' , FAInstanceId );
2045 END IF;
2046
2047 CLOSE c_GetFAStat;
2048
2049 EXCEPTION
2050 WHEN others THEN
2051 x_progress := 'In XDPSTATUS.SetStatusForFA. Unhandled Exception. Error: ' || SUBSTR(SQLERRM, 1,1500);
2052 wf_core.context('XDPSTATUS', 'SetStatusForFA', null, null, null, x_progress);
2053 RAISE;
2054 END SetStatusForFA;
2055
2056 PROCEDURE SetStatusForWI (WorkitemInstanceID IN NUMBER,
2057 Status IN VARCHAR2,
2058 Event IN VARCHAR2)
2059
2060 IS
2061 CURSOR c_GetWIStat IS
2062 SELECT Status_code
2063 FROM XDP_FULFILL_WORKLIST
2064 WHERE WORKITEM_INSTANCE_ID = WorkitemInstanceID;
2065 -- FOR UPDATE;
2066
2067 CURSOR c_GetFA IS
2068 SELECT 1
2069 FROM XDP_FA_RUNTIME_LIST
2070 WHERE WORKITEM_INSTANCE_ID = WorkitemInstanceID
2071 AND STATUS_CODE = 'ERROR' ;
2072
2073
2074 l_WIStatus VARCHAR2(40);
2075 l_WIState VARCHAR2(40);
2076 l_FAStatus VARCHAR2(40);
2077
2078
2079 l_Counter NUMBER := 0 ;
2080 l_overridecounter NUMBER := 0 ;
2081 x_progress VARCHAR2(2000);
2082
2083 e_InvalidConfigException EXCEPTION;
2084
2085 BEGIN
2086
2087 OPEN c_GetWIStat;
2088 FETCH c_GetWIStat into l_WIStatus;
2089
2090 IF c_GetWIStat%NOTFOUND then
2091 RAISE e_InvalidConfigException;
2092 END IF;
2093
2094
2095 IF Event IS NULL AND status = 'ERROR' THEN
2096
2097 UPDATE_XDP_WORKITEM_STATUS(Status,WorkitemInstanceID);
2098
2099 ELSIF Event IS NULL AND Status = 'SUCCESS' THEN
2100
2101 FOR v_GetFA in c_GetFA
2102 LOOP
2103 l_Counter := l_Counter + 1;
2104 END LOOP;
2105
2106 IF l_Counter = 0 THEN
2107
2108 UPDATE_XDP_WORKITEM_STATUS('IN PROGRESS',workiteminstanceid);
2109
2110 END IF;
2111
2112 END IF;
2113
2114 CLOSE c_GetWIStat;
2115
2116 EXCEPTION
2117 WHEN others THEN
2118 IF c_GetWIStat%ISOPEN THEN
2119 CLOSE c_GetWIStat;
2120 END IF;
2121 wf_core.context('XDPSTATUS', 'SetStatusForWI', null, null, null, x_progress);
2122 RAISE;
2123 END SetStatusForWI;
2124
2125 PROCEDURE SetStatusForLine ( OrderID IN NUMBER,
2126 WorkitemInstanceID IN NUMBER,
2127 LineItemID IN NUMBER,
2128 Status IN VARCHAR2,
2129 Event IN VARCHAR2)
2130
2131 IS
2132 l_LineItemID NUMBER;
2133 CURSOR c_GetLineStat(LineID number) IS
2134 SELECT Status_code,
2135 IS_VIRTUAL_LINE_FLAG,
2136 NVL(BUNDLE_ID,-999)
2137 FROM XDP_ORDER_LINE_ITEMS
2138 WHERE LINE_ITEM_ID = LineID;
2139 -- FOR UPDATE;
2140
2141 CURSOR c_GetLineID IS
2142 SELECT line_item_id
2143 FROM XDP_FULFILL_WORKLIST
2144 WHERE WOrkitem_instance_id = WorkitemInstanceID;
2145
2146 CURSOR c_GetWI(LineID number) IS
2147 SELECT STATUS_CODE,
2148 WORKITEM_INSTANCE_ID
2149 FROM XDP_FULFILL_WORKLIST
2150 WHERE LINE_ITEM_ID = LineID
2151 AND STATUS_CODE = 'ERROR';
2152
2153 CURSOR c_GetPackagedLines(VirtualLine number) IS
2154 SELECT RELATED_LINE_ITEM_ID
2155 FROM XDP_LINE_RELATIONSHIPS
2156 WHERE LINE_ITEM_ID = VirtualLine
2157 AND LINE_RELATIONSHIP = 'IS_PART_OF_PACKAGE';
2158
2159 CURSOR c_UpdatePackage(PackageLineID number) IS
2160 SELECT 1
2161 FROM XDP_ORDER_LINE_ITEMS
2162 WHERE LINE_ITEM_ID = PackageLineID;
2163 -- FOR UPDATE;
2164
2165 CURSOR c_GetPackagedLineStatus(VirtualLine number) IS
2166 SELECT XLR.RELATED_LINE_ITEM_ID,
2167 XOL.STATUS_CODE
2168 FROM XDP_LINE_RELATIONSHIPS XLR,
2169 XDP_ORDER_LINE_ITEMS XOL
2170 WHERE XLR.RELATED_LINE_ITEM_ID = (
2171 SELECT RELATED_LINE_ITEM_ID
2172 FROM XDP_LINE_RELATIONSHIPS
2173 WHERE LINE_ITEM_ID = VirtualLine)
2174 AND XLR.LINE_ITEM_ID = XOL.LINE_ITEM_ID
2175 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y';
2176
2177 CURSOR c_GetBundledLines (LineItemID number, BundleID number) is
2178 SELECT LINE_ITEM_ID
2179 FROM XDP_ORDER_LINE_ITEMS
2180 WHERE LINE_ITEM_ID = LineItemID
2181 AND BUNDLE_ID = BundleID
2182 AND STATUS_CODE = 'ERROR';
2183
2184 CURSOR C_UpdateBundleStatus(BundleID number) IS
2185 SELECT 1
2186 FROM XDP_ORDER_BUNDLES
2187 WHERE ORDER_ID = OrderID
2188 AND BUNDLE_ID = BundleID;
2189 -- FOR UPDATE;
2190
2191
2192 l_LineStatus VARCHAR2(40);
2193 l_LineState VARCHAR2(40);
2194 l_Counter NUMBER := 0;
2195 l_BundleID NUMBER;
2196 l_RelatedLineID NUMBER := 0;
2197 l_PackageStatus VARCHAR2(40);
2198 l_PkgCount NUMBER := 0;
2199 l_BundleCount NUMBEr := 0;
2200 l_IsVirtualLine VARCHAR2(2);
2201
2202 x_progress VARCHAR2(2000);
2203
2204 e_InvalidConfigException exception;
2205 begin
2206
2207 if LineItemID is null then
2208 Open c_GetLineID;
2209 Fetch c_GetLineID into l_LineItemID;
2210 close c_GetLineID;
2211 else
2212 l_LineItemID := LineItemID;
2213 end if;
2214
2215
2216 Open c_GetLineStat(l_LineItemID);
2217 Fetch c_GetLineStat
2218 INTO l_LineStatus,
2219 l_IsVirtualLine,
2220 l_BundleID;
2221
2222 if c_GetLineStat%NOTFOUND then
2223 Raise e_InvalidConfigException;
2224 end if;
2225
2226
2227 if Event is null and Status = 'ERROR' then
2228
2229 UPDATE_XDP_ORDER_LINE_STATUS(Status,l_LineItemID);
2230
2231 if l_IsVirtualLine = 'Y' then
2232
2233 FOR v_GetPackagedLine in c_GetPackagedLines(l_LineItemID) LOOP
2234 FOR v_UpdatePackageLine in c_UpdatePackage(v_GetPackagedLine.RELATED_LINE_ITEM_ID) LOOP
2235
2236 UPDATE_XDP_ORDER_LINE_STATUS(Status,v_GetPackagedLine.RELATED_LINE_ITEM_ID);
2237
2238 END LOOP;
2239 END LOOP;
2240 end if;
2241
2242 if l_BundleID <> -999 then
2243
2244 UPDATE_XDP_ORDER_BUNDLE_STATUS('ERROR',OrderID,l_BundleID);
2245
2246 end if;
2247
2248
2249 elsif Event is null and Status = 'SUCCESS' then
2250 FOR v_GetWI in c_GetWI(l_LineItemID) LOOP
2251 l_Counter := l_Counter + 1;
2252 END LOOP;
2253
2254 if l_Counter = 0 then
2255
2256 UPDATE_XDP_ORDER_LINE_STATUS('IN PROGRESS',l_LineItemID);
2257
2258 end if;
2259
2260 if l_IsVirtualLine = 'Y' then
2261 FOR v_GetPackagedLineStatus in c_GetPackagedLineStatus(l_LineItemID) LOOP
2262 l_RelatedLineID := v_GetPackagedLineStatus.RELATED_LINE_ITEM_ID;
2263 if v_GetPackagedLineStatus.STATUS_CODE = 'ERROR' THEN
2264 l_PkgCount := l_PkgCount + 1;
2265 end if;
2266 END LOOP;
2267
2268 if l_PkgCount = 0 then
2269
2270 UPDATE_XDP_ORDER_LINE_STATUS('IN PROGRESS',l_RelatedLineID);
2271 end if;
2272 if l_BundleID <> -999 then
2273 FOR v_GetBundlesLines in c_GetBundledLines(l_LineItemID, l_BundleID) LOOP
2274 l_BundleCount := l_BundleCount + 1;
2275 END LOOP;
2276
2277 if l_PkgCount = 0 then
2278
2279 UPDATE_XDP_ORDER_BUNDLE_STATUS('IN PROGRESS',OrderID,l_BundleID);
2280
2281 end if;
2282 end if;
2283 end if;
2284 end if;
2285
2286 close c_GetLineStat;
2287
2288 exception
2289 when others then
2290 if c_GetLineStat%ISOPEN then
2291 close c_GetLineStat;
2292 end if;
2293
2294 if c_GetLineID%ISOPEN then
2295 close c_GetLineID;
2296 end if;
2297
2298 wf_core.context('XDPSTATUS', 'SetStatusForLine', null, null, null, x_progress);
2299 raise;
2300 end SetStatusForLine;
2301
2302
2303 Procedure SetStatusForOrder (OrderID in number,
2304 Status in varchar2,
2305 Event in varchar2)
2306
2307 is
2308 cursor c_GetOrderStat is
2309 select Status_code
2310 from XDP_ORDER_HEADERS
2311 where ORDER_ID = OrderID;
2312 -- for update;
2313
2314 cursor c_GetLines is
2315 select 1
2316 from XDP_ORDER_LINE_ITEMS
2317 where ORDER_ID = OrderID
2318 and STATUS_CODE = 'ERROR';
2319
2320 l_OrderStatus varchar2(40);
2321 l_OrderState varchar2(40);
2322 l_Counter number := 0;
2323
2324 x_progress varchar2(2000);
2325
2326 e_InvalidConfigException exception;
2327 begin
2328
2329 Open c_GetOrderStat;
2330 Fetch c_GetOrderStat
2331 INTO l_OrderStatus;
2332 if c_GetOrderStat%NOTFOUND then
2333 Raise e_InvalidConfigException;
2334 end if;
2335
2336
2337 if Event is null and Status = 'ERROR' then
2338
2339 UPDATE_XDP_ORDER_STATUS(Status,orderID) ;
2340
2341 elsif Event is null and Status = 'SUCCESS' then
2342 FOR v_GetLines in c_GetLines LOOP
2343 l_Counter := l_Counter + 1;
2344 END LOOP;
2345
2346 if l_Counter = 0 then
2347
2348 UPDATE_XDP_ORDER_STATUS('IN PROGRESS',orderID) ;
2349 end if;
2350 end if;
2351
2352 close c_GetOrderStat;
2353
2354 exception
2355 when others then
2356 if c_GetOrderStat%ISOPEN then
2357 close c_GetOrderStat;
2358 end if;
2359
2360 wf_core.context('XDPSTATUS', 'SetStatusForOrder', null, null, null, x_progress);
2361 raise;
2362 end SetStatusForOrder;
2363
2364
2365 PROCEDURE UPDATE_XDP_ORDER_BUNDLE_STATUS(p_status IN VARCHAR2,
2366 p_order_id IN NUMBER,
2367 p_bundle_id IN NUMBER) IS
2368 PRAGMA AUTONOMOUS_TRANSACTION ;
2369 x_progress VARCHAR2(2000);
2370
2371 BEGIN
2372 UPDATE xdp_order_bundles
2373 SET status = p_status,
2374 last_update_date = sysdate,
2375 last_updated_by = fnd_global.user_id,
2376 last_update_login = fnd_global.login_id
2377 WHERE order_id = p_order_id
2378 AND bundle_id = p_bundle_id ;
2379 COMMIT;
2380
2381 EXCEPTION
2382 WHEN others THEN
2383 wf_core.context('XDPSTATUS', 'UPDATE_XDP_ORDER_STATUS', null, null, null, x_progress);
2384 ROLLBACK;
2385 RAISE ;
2386
2387 END UPDATE_XDP_ORDER_BUNDLE_STATUS ;
2388
2389
2390
2391 PROCEDURE UPDATE_XDP_ORDER_STATUS(p_status IN VARCHAR2,
2392 p_order_id IN NUMBER) IS
2393 PRAGMA AUTONOMOUS_TRANSACTION ;
2394 x_progress VARCHAR2(2000);
2395
2396 BEGIN
2397 IF p_status IN ('SUCCESS_WITH_OVERRIDE','ABORTED','SUCCESS') THEN
2398
2399 UPDATE xdp_order_headers
2400 SET status_code = p_status ,
2401 completion_date = sysdate ,
2402 last_update_date = sysdate,
2403 last_updated_by = fnd_global.user_id,
2404 last_update_login = fnd_global.login_id
2405 WHERE order_id = p_order_id ;
2406 ELSE
2407 UPDATE xdp_order_headers
2408 SET status_code = p_status ,
2409 last_update_date = sysdate,
2410 last_updated_by = fnd_global.user_id,
2411 last_update_login = fnd_global.login_id
2412 WHERE order_id = p_order_id ;
2413
2414 END IF ;
2415
2416 COMMIT;
2417
2418 EXCEPTION
2419 WHEN others THEN
2420 wf_core.context('XDPSTATUS', 'UPDATE_XDP_ORDER_STATUS', null, null, null, x_progress);
2421 ROLLBACK;
2422 RAISE ;
2423 END UPDATE_XDP_ORDER_STATUS;
2424
2425
2426
2427 PROCEDURE UPDATE_XDP_ORDER_LINE_STATUS(p_status IN VARCHAR2,
2428 p_line_item_id IN NUMBER)IS
2429 PRAGMA AUTONOMOUS_TRANSACTION ;
2430 x_progress VARCHAR2(2000);
2431
2432
2433 BEGIN
2434 IF p_status IN ('IN PROGRESS','ERROR') THEN
2435
2436 UPDATE xdp_order_line_items
2437 SET status_code = p_status ,
2438 last_update_date = sysdate,
2439 last_updated_by = fnd_global.user_id,
2440 last_update_login = fnd_global.login_id
2441 WHERE line_item_id = p_line_item_id ;
2442 ELSE
2443 UPDATE xdp_order_line_items
2444 SET status_code = p_status ,
2445 completion_date = sysdate ,
2446 last_update_date = sysdate,
2447 last_updated_by = fnd_global.user_id,
2448 last_update_login = fnd_global.login_id
2449 WHERE line_item_id = p_line_item_id ;
2450 END IF ;
2451
2452 COMMIT;
2453
2454 EXCEPTION
2455 WHEN others THEN
2456 wf_core.context('XDPSTATUS', 'UPDATE_XDP_ORDER_LINE_STATUS', null, null, null, x_progress);
2457 ROLLBACK;
2458 RAISE ;
2459 END UPDATE_XDP_ORDER_LINE_STATUS;
2460
2461
2462
2463 PROCEDURE UPDATE_XDP_WORKITEM_STATUS(p_status IN VARCHAR2,
2464 p_workitem_instance_id IN NUMBER)IS
2465 PRAGMA AUTONOMOUS_TRANSACTION ;
2466 x_progress VARCHAR2(2000);
2467
2468
2469 BEGIN
2470 IF p_status IN ('IN PROGRESS','ERROR') THEN
2471
2472 UPDATE xdp_fulfill_worklist
2473 SET status_code = p_status ,
2474 last_update_date = sysdate,
2475 last_updated_by = fnd_global.user_id,
2476 last_update_login = fnd_global.login_id
2477 WHERE workitem_instance_id = p_workitem_instance_id ;
2478 ELSE
2479 UPDATE xdp_fulfill_worklist
2480 SET status_code = p_status ,
2481 completion_date = sysdate ,
2482 last_update_date = sysdate,
2483 last_updated_by = fnd_global.user_id,
2484 last_update_login = fnd_global.login_id
2485 WHERE workitem_instance_id = p_workitem_instance_id ;
2486 END IF ;
2487
2488 COMMIT;
2489
2490 EXCEPTION
2491 WHEN others THEN
2492 wf_core.context('XDPSTATUS', 'UPDATE_XDP_WORKITEM_STATUS', null, null, null, x_progress);
2493 ROLLBACK;
2494 RAISE ;
2495 END UPDATE_XDP_WORKITEM_STATUS;
2496
2497
2498
2499 PROCEDURE UPDATE_XDP_FA_STATUS(p_status IN VARCHAR2,
2500 p_fa_instance_id IN NUMBER)IS
2501 PRAGMA AUTONOMOUS_TRANSACTION ;
2502 x_progress VARCHAR2(2000);
2503
2504
2505 BEGIN
2506 IF p_status IN ('IN PROGRESS','ERROR') THEN
2507
2508 UPDATE xdp_fa_runtime_list
2509 SET status_code = p_status ,
2510 last_update_date = sysdate,
2511 last_updated_by = fnd_global.user_id,
2512 last_update_login = fnd_global.login_id
2513 WHERE fa_instance_id = p_fa_instance_id ;
2514 ELSE
2515 UPDATE xdp_fa_runtime_list
2516 SET status_code = p_status ,
2517 completion_date = sysdate ,
2518 last_update_date = sysdate,
2519 last_updated_by = fnd_global.user_id,
2520 last_update_login = fnd_global.login_id
2521 WHERE fa_instance_id = p_fa_instance_id ;
2522
2523 END IF ;
2524
2525 COMMIT;
2526
2527 EXCEPTION
2528 WHEN others THEN
2529 wf_core.context('XDPSTATUS', 'UPDATE_XDP_FA_STATUS', null, null, null, x_progress);
2530 ROLLBACK;
2531 RAISE ;
2532 END UPDATE_XDP_FA_STATUS;
2533
2534
2535 FUNCTION IS_ORDER_IN_ERROR (p_order_id IN NUMBER)
2536 RETURN BOOLEAN IS
2537
2538 l_status BOOLEAN := FALSE ;
2539
2540 CURSOR c_order IS
2541 SELECT 'Y'
2542 FROM xdp_order_headers oh
2543 WHERE order_id = p_order_id
2544 AND (EXISTS (SELECT 'Y'
2545 FROM xdp_order_line_items oli
2546 WHERE oli.order_id = oh.ordeR_id
2547 AND oli.status_code = 'ERROR' ) OR
2548 EXISTS (SELECT 'Y'
2549 FROM xdp_fulfill_worklist fw
2550 WHERE fw.order_id = oh.order_id
2551 AND fw.status_code = 'ERROR') OR
2552 EXISTS (SELECT 'Y'
2553 FROM xdp_fulfill_worklist fw,
2554 xdp_fa_runtime_list fr
2555 WHERE fw.workitem_instance_id = fr.workitem_instance_id
2556 AND fw.order_id = oh.order_id
2557 AND fr.status_code = 'ERROR')) ;
2558
2559
2560 BEGIN
2561 FOR c_order_rec IN c_order
2562 LOOP
2563 l_status := TRUE;
2564 END LOOP ;
2565
2566 RETURN l_status ;
2567 END IS_ORDER_IN_ERROR ;
2568
2569
2570 FUNCTION IS_LINE_IN_ERROR(p_lineitem_id IN NUMBER)
2571 RETURN BOOLEAN IS
2572
2573 l_status BOOLEAN := FALSE ;
2574
2575 CURSOR c_lines IS
2576 SELECT 'Y' ,
2577 is_package_flag ,
2578 is_virtual_line_flag,
2579 line_item_id,
2580 ib_source,
2581 ib_source_id
2582 FROM xdp_order_line_items
2583 WHERE line_item_id = p_lineitem_id ;
2584
2585 CURSOR c_order_line IS
2586 SELECT 'Y'
2587 FROM xdp_order_line_items oli
2588 WHERE line_item_id = p_lineitem_id
2589 AND ( EXISTS (SELECT 'Y'
2590 FROM xdp_fulfill_worklist fw
2591 WHERE fw.line_item_id = oli.line_item_id
2592 AND fw.status_code = 'ERROR') OR
2593 EXISTS (SELECT 'Y'
2594 FROM xdp_fulfill_worklist fw,
2595 xdp_fa_runtime_list fr
2596 WHERE fw.workitem_instance_id = fr.workitem_instance_id
2597 AND fw.line_item_id = oli.line_item_id
2598 AND fr.status_code = 'ERROR')) ;
2599
2600 CURSOR c_order_pkg_line(lv_line_item_id IN NUMBER) IS
2601 SELECT 'Y'
2602 FROM xdp_line_relationships lr
2603 WHERE lr.related_line_item_id = lv_line_item_id
2604 AND (EXISTS (SELECT 'Y'
2605 FROM xdp_fulfill_worklist fw
2606 WHERE fw.line_item_id = lr.line_item_id
2607 AND fw.status_code = 'ERROR') OR
2608 EXISTS (SELECT 'Y'
2609 FROM xdp_fulfill_worklist fw,
2610 xdp_fa_runtime_list fr
2611 WHERE fw.workitem_instance_id = fr.workitem_instance_id
2612 AND fw.line_item_id = lr.line_item_id
2613 AND fr.status_code = 'ERROR')) ;
2614 BEGIN
2615 FOR c_lines_rec IN c_lines
2616 LOOP
2617 IF ((c_lines_rec.IS_PACKAGE_FLAG = 'Y') OR
2618 (c_lines_rec.ib_source IN('CSI','TXN') AND c_lines_rec.ib_source_id IS NULL )
2619 ) THEN
2620
2621 FOR c_order_pkg_line_rec IN c_order_pkg_line(c_lines_rec.line_item_id)
2622 LOOP
2623 l_status := TRUE ;
2624 END LOOP ;
2625
2626 ELSE
2627 FOR c_order_line_rec IN c_order_line
2628 LOOP
2629 l_status := TRUE ;
2630 END LOOP ;
2631
2632 END IF ;
2633
2634 END LOOP ;
2635
2636 RETURN l_status ;
2637
2638 END IS_LINE_IN_ERROR ;
2639
2640 FUNCTION IS_WI_IN_ERROR(p_WIInstance_id IN NUMBER)
2641 RETURN BOOLEAN IS
2642
2643 l_status BOOLEAN := FALSE ;
2644
2645 CURSOR c_order_line IS
2646 SELECT 'Y'
2647 FROM xdp_fulfill_worklist fw
2648 WHERE workitem_instance_id = p_WIInstance_id
2649 AND EXISTS (SELECT 'Y'
2650 FROM xdp_fa_runtime_list fr
2651 WHERE fr.workitem_instance_id = fw.workitem_instance_id
2652 AND fr.status_code = 'ERROR') ;
2653
2654 BEGIN
2655 FOR c_order_line_rec IN c_order_line
2656 LOOP
2657 l_status := TRUE ;
2658 END LOOP ;
2659
2660 RETURN l_status ;
2661
2662 END IS_WI_IN_ERROR ;
2663
2664
2665 FUNCTION IS_FA_IN_ERROR(p_FAInstance_id IN NUMBER)
2666 RETURN BOOLEAN IS
2667
2668 l_status BOOLEAN := FALSE ;
2669
2670 CURSOR c_fa IS
2671 SELECT 'Y'
2672 FROM xdp_fa_runtime_list fr
2673 WHERE fr.fa_instance_id = p_FAInstance_id
2674 AND fr.status_code = 'ERROR' ;
2675
2676 BEGIN
2677 FOR c_fa_rec IN c_fa
2678 LOOP
2679 l_status := TRUE ;
2680 END LOOP ;
2681
2682 RETURN l_status ;
2683
2684 END IS_FA_IN_ERROR ;
2685
2686 PROCEDURE SetNodeWIStatus(itemtype IN VARCHAR2,
2687 itemkey IN VARCHAR2,
2688 actid IN NUMBER)
2689 IS
2690
2691 l_WIInstanceID NUMBER ;
2692 l_status VARCHAR2(40);
2693
2694 BEGIN
2695 l_status := WF_ENGINE.GetActivityattrtext(itemtype =>SetNodeWIStatus.itemtype,
2696 itemkey =>SetNodeWIStatus.itemkey,
2697 actid =>SetNodeWIStatus.actid,
2698 aname =>'STATUS');
2699
2700 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => SetNodeWIStatus.itemtype,
2701 itemkey => SetNodeWIStatus.itemkey,
2702 aname => 'WORKITEM_INSTANCE_ID');
2703 UPDATE_XDP_WORKITEM_STATUS(l_status,l_WIInstanceID);
2704
2705
2706 EXCEPTION
2707 WHEN others THEN
2708 wf_core.context('XDPSTATUS', 'SetNodeWIStatus', itemtype, itemkey, null,null);
2709 RAISE;
2710 END SetNodeWIStatus;
2711
2712 PROCEDURE SetNodeLineStatus(itemtype IN VARCHAR2,
2713 itemkey IN VARCHAR2,
2714 actid IN NUMBER)
2715 IS
2716
2717 l_LineItemID NUMBER;
2718 l_status VARCHAR2(40);
2719
2720 BEGIN
2721
2722 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => SetNodeLineStatus.itemtype,
2723 itemkey => SetNodeLineStatus.itemkey,
2724 aname => 'LINE_ITEM_ID');
2725 l_status := WF_ENGINE.GetActivityattrtext(itemtype =>SetNodeLineStatus.itemtype,
2726 itemkey =>SetNodeLineStatus.itemkey,
2727 actid =>SetNodeLineStatus.actid,
2728 aname =>'STATUS');
2729
2730
2731 UPDATE_XDP_ORDER_LINE_STATUS(l_status , l_LineItemID );
2732
2733
2734 EXCEPTION
2735 WHEN others THEN
2736 wf_core.context('XDPSTATUS', 'SetNodeLineStatus', itemtype, itemkey, null,null);
2737 RAISE;
2738
2739
2740 END SetNodeLineStatus;
2741
2742 FUNCTION GET_WI_STATUS(p_WIInstance_id IN NUMBER)
2743 RETURN VARCHAR2 IS
2744 cursor cur_wi_status is
2745 SELECT status_code
2746 FROM xdp_fulfill_worklist
2747 WHERE workitem_instance_id = p_WIInstance_id;
2748
2749 cursor cur_fa_status is
2750 SELECT status_code
2751 FROM xdp_fa_runtime_list
2752 WHERE workitem_instance_id = p_WIInstance_id;
2753
2754 l_status VARCHAR2(200);
2755 l_system_hold BOOLEAN := FALSE;
2756 l_error BOOLEAN := FALSE;
2757
2758 BEGIN
2759 FOR lv_rec IN cur_wi_status LOOP
2760 l_status := lv_rec.STATUS_CODE;
2761
2762 --Check whether the WI itself is in error..
2763 IF l_status = 'ERROR' THEN
2764 l_error := TRUE;
2765 EXIT;
2766 ELSE
2767 --Check whether any FAs are in error..
2768 FOR lv_fa_rec IN cur_fa_status LOOP
2769 l_status := lv_fa_rec.status_code;
2770 IF l_status = 'ERROR' THEN
2771 l_error := TRUE;
2772 EXIT;
2773 ELSIF l_status = 'SYSTEM_HOLD' THEN
2774 l_system_hold := TRUE;
2775 EXIT;
2776 END IF;
2777 END LOOP;
2778 END IF;
2779 END LOOP ;
2780
2781 IF l_error THEN
2782 RETURN 'ERROR';
2783 ELSIF l_system_hold THEN
2784 RETURN 'SYSTEM_HOLD';
2785 ELSE
2786 RETURN l_status;
2787 END IF;
2788 END GET_WI_STATUS;
2789
2790 FUNCTION GET_LINE_STATUS(p_line_item_id IN NUMBER)
2791 RETURN VARCHAR2 IS
2792 --cursor to figure out whether a line is package or not..
2793 CURSOR c_lines IS
2794 SELECT is_package_flag , is_virtual_line_flag,
2795 line_item_id, status_code,
2796 ib_source, ib_source_id
2797 FROM xdp_order_line_items
2798 WHERE line_item_id = p_line_item_id;
2799
2800 CURSOR c_pkg_child_lines( cv_line_item_id IN NUMBER) IS
2801 SELECT xoli.line_item_id, xoli.status_code
2802 FROM xdp_line_relationships lr, xdp_order_line_items xoli
2803 WHERE lr.related_line_item_id = cv_line_item_id
2804 AND lr.line_item_id = xoli.line_item_id;
2805
2806 CURSOR c_line_status (cv_line_item_id IN NUMBER) IS
2807 SELECT workitem_instance_id
2808 FROM xdp_fulfill_worklist
2809 WHERE line_item_id = cv_line_item_id;
2810
2811 l_line_status VARCHAR2(200);
2812 l_status VARCHAR2(200);
2813 l_system_hold BOOLEAN := FALSE;
2814 l_error BOOLEAN := FALSE;
2815
2816 BEGIN
2817 FOR lv_rec_is_pkg IN c_lines LOOP
2818 IF ((lv_rec_is_pkg.IS_PACKAGE_FLAG = 'Y') OR
2819 (lv_rec_is_pkg.ib_source IN('CSI','TXN') AND
2820 lv_rec_is_pkg.ib_source_id IS NULL )) THEN
2821
2822 --Check whether the package itself is in error...
2823 l_line_status := lv_rec_is_pkg.status_code;
2824
2825 IF l_line_status = 'ERROR' THEN
2826 l_error := TRUE;
2827 EXIT;
2828 END IF;
2829
2830 FOR lv_rec IN c_pkg_child_lines( lv_rec_is_pkg.line_item_id ) LOOP
2831 -- check whether the child line itself is in error..
2832 IF lv_rec.status_code = 'ERROR' THEN
2833 l_error := TRUE;
2834 EXIT;
2835 END IF;
2836
2837 -- check all the child lines
2838 FOR lv_child_line_rec IN c_line_status( lv_rec.line_item_id ) LOOP
2839 l_status := get_wi_status( lv_child_line_rec.workitem_instance_id );
2840 --IF any WI is in error exit..
2841 IF l_status = 'ERROR' THEN
2842 l_error := TRUE;
2843 EXIT;
2844 ELSIF l_status = 'SYSTEM_HOLD' THEN
2845 l_system_hold := TRUE;
2846 END IF;
2847 END LOOP;
2848 END LOOP;
2849 ELSE
2850 --Check whether the line itself is in error...
2851 l_line_status := lv_rec_is_pkg.status_code;
2852
2853 IF l_line_status = 'ERROR' THEN
2854 l_error := TRUE;
2855 EXIT;
2856 END IF;
2857
2858 -- Browse thru all WIs
2859 FOR lv_rec IN c_line_status( p_line_item_id ) LOOP
2860 l_status := get_wi_status( lv_rec.workitem_instance_id );
2861 IF l_status = 'ERROR' THEN
2862 l_error := TRUE;
2863 EXIT;
2864 ELSIF l_status = 'SYSTEM_HOLD' THEN
2865 l_system_hold := TRUE;
2866 END IF;
2867 END LOOP;
2868 END IF;
2869 END LOOP;
2870
2871 IF l_error THEN
2872 RETURN 'ERROR';
2873 ELSIF l_system_hold THEN
2874 RETURN 'SYSTEM_HOLD';
2875 ELSE
2876 RETURN l_line_status;
2877 END IF;
2878
2879 END GET_LINE_STATUS;
2880
2881 FUNCTION GET_ORDER_STATUS(p_order_id IN NUMBER)
2882 RETURN VARCHAR2 IS
2883
2884 CURSOR cur_order IS
2885 SELECT status_code
2886 FROM xdp_order_headers
2887 WHERE order_id = p_order_id;
2888
2889 CURSOR cur_lines IS
2890 SELECT status_code, line_item_id
2891 FROM xdp_order_line_items
2892 WHERE order_id = p_order_id;
2893
2894 lv_status VARCHAR2(200);
2895 l_system_hold BOOLEAN := FALSE;
2896 l_error BOOLEAN := FALSE;
2897
2898 BEGIN
2899 FOR lv_rec IN cur_order LOOP
2900 lv_status := lv_rec.status_code;
2901 IF( lv_status = 'ERROR' ) THEN
2902 l_error := TRUE;
2903 EXIT;
2904 ELSE
2905 FOR lv_line_rec IN cur_lines LOOP
2906 lv_status := get_line_status(lv_line_rec.line_item_id);
2907 IF( lv_status = 'ERROR' ) THEN
2908 l_error := TRUE;
2909 EXIT;
2910 ELSIF lv_status = 'SYSTEM_HOLD' THEN
2911 l_system_hold := TRUE;
2912 END IF;
2913 END LOOP;
2914 END IF;
2915 END LOOP;
2916
2917 IF l_error THEN
2918 RETURN 'ERROR';
2919 ELSIF l_system_hold THEN
2920 RETURN 'SYSTEM_HOLD';
2921 ELSE
2922 RETURN lv_status;
2923 END IF;
2924
2925
2926 END GET_ORDER_STATUS;
2927
2928
2929 End XDPSTATUS;
[Home] [Help]