[Home] [Help]
PACKAGE BODY: APPS.XDPCORE_LINE
Source
1 PACKAGE BODY XDPCORE_LINE AS
2 /* $Header: XDPCORLB.pls 120.1 2005/06/08 23:47:15 appldev $ */
3
4
5 /****
6 All Private Procedures for the Package
7 ****/
8
9 Function HandleOtherWFFuncmode (funcmode in varchar2) return varchar2;
10
11 Procedure InitializeLine(itemtype in varchar2,
12 itemkey in varchar2);
13
14 Function AreAllLinesDone (itemtype in varchar2,
15 itemkey in varchar2) return varchar2;
16
17 Procedure LaunchLineForOrderProcess(itemtype in varchar2,
18 itemkey in varchar2);
19
20 Procedure LaunchLineItemProcess(itemtype in varchar2,
21 itemkey in varchar2);
22
23 Procedure LaunchLineItemProcessForBundle(itemtype in varchar2,
24 itemkey in varchar2);
25
26 Procedure LaunchLineItemProcessForOrder(itemtype in varchar2,
27 itemkey in varchar2);
28
29 Procedure LaunchServiceProcess(itemtype in varchar2,
30 itemkey in varchar2);
31
32 Function AreAllLinesDoneForBundleCaller(itemtype in varchar2,
33 itemkey in varchar2) return varchar2;
34
35 Function AreAllLinesDoneForOrderCaller(itemtype in varchar2,
36 itemkey in varchar2) return varchar2;
37
38 Function IsLineaPackage(itemtype in varchar2,
39 itemkey in varchar2) return varchar2;
40
41 Function LaunchAllIndLines(itemtype in varchar2,
42 itemkey in varchar2) return varchar2;
43
44 Function LaunchAllIndLinesForOrder (itemtype in varchar2,
45 itemkey in varchar2) return varchar2;
46
47 Function LaunchAllIndLinesForBundle (itemtype in varchar2,
48 itemkey in varchar2) return varchar2;
49
50 Procedure AddLineAttributes(itemtype in varchar2,
51 itemkey in varchar2,
52 Module in varchar2,
53 IsPackageFlag in varchar2,
54 ibsource in varchar2,
55 ibsourceid in number,
56 LineProcessCaller in varchar2,
57 CurrentLineSequence in number,
58 errcode OUT NOCOPY number,
59 errstr OUT NOCOPY varchar2);
60
61 Procedure UpdateInstallBase(itemtype in varchar2,
62 itemkey in varchar2
63 );
64
65 Procedure InitializeDepLineProcess(itemtype in varchar2,
66 itemkey in varchar2 );
67
68 Function ResolveIndDepLines (itemtype in varchar2,
69 itemkey in varchar2) return varchar2;
70
71 Function IsSerPartPackage (itemtype in varchar2,
72 itemkey in varchar2) return varchar2;
73
74 PROCEDURE UPDATDE_ORDER_LINE(p_line_item_id IN NUMBER ,
75 p_status_code IN VARCHAR2,
76 p_itemtype IN VARCHAR2,
77 p_itemkey IN VARCHAR2) ;
78
79
80
81 PROCEDURE PublishXDPLineDone(itemtype IN VARCHAR2,
82 itemkey IN VARCHAR2,
83 actid IN NUMBER,
84 resultout IN VARCHAR2);
85
86
87 type RowidArrayType is table of rowid index by binary_integer;
88
89
90
91 /***********************************************
92 * END of Private Procedures/Function Definitions
93 ************************************************/
94
95
96 -- LAUNCH_LINEITEM_PROCESS
97 -- Resultout
98 -- Activity Performed - Activity was completed without any errors
99 --
100 -- Your Description here:
101
102 Procedure LAUNCH_LINEITEM_PROCESS (itemtype in varchar2,
103 itemkey in varchar2,
104 actid in number,
105 funcmode in varchar2,
106 resultout OUT NOCOPY varchar2 ) IS
107
108 x_Progress VARCHAR2(2000);
109
110 BEGIN
111 -- RUN mode - normal process execution
112 --
113 IF (funcmode = 'RUN') THEN
114 LaunchLineItemProcess(itemtype, itemkey);
115 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
116 return;
117 ELSE
118 resultout := HandleOtherWFFuncmode(funcmode);
119 return;
120 END IF;
121
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 wf_core.context('XDPCORE_LINE', 'LAUNCH_LINEITEM_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
126 raise;
127 END LAUNCH_LINEITEM_PROCESS;
128
129
130
131
132 -- INITIALIZE_LINE
133 -- Resultout
134 -- Activity Performed - Activity was completed without any errors
135 --
136 -- Your Description here:
137
138 Procedure INITIALIZE_LINE (itemtype in varchar2,
139 itemkey in varchar2,
140 actid in number,
141 funcmode in varchar2,
142 resultout OUT NOCOPY varchar2 ) IS
143
144 x_Progress VARCHAR2(2000);
145
146 BEGIN
147 -- RUN mode - normal process execution
148 --
149 IF (funcmode = 'RUN') THEN
150 InitializeLine(itemtype, itemkey);
151 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
152 return;
153 ELSE
154 resultout := HandleOtherWFFuncmode(funcmode);
155 return;
156 END IF;
157
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 wf_core.context('XDPCORE_LINE', 'INITIALIZE_LINE', itemtype, itemkey, to_char(actid), funcmode);
162 raise;
163 END INITIALIZE_LINE;
164
165
166
167
168 -- IS_LINE_A_PACKAGE
169 -- Resultout
170 -- Activity Performed - Activity was completed without any errors
171 --
172 -- Your Description here:
173
174 Procedure IS_LINE_A_PACKAGE (itemtype in varchar2,
175 itemkey in varchar2,
176 actid in number,
177 funcmode in varchar2,
178 resultout OUT NOCOPY varchar2 ) IS
179
180 l_result varchar2(10);
181 x_Progress VARCHAR2(2000);
182
183 BEGIN
184 -- RUN mode - normal process execution
185 --
186 IF (funcmode = 'RUN') THEN
187 l_result := IsLineaPackage(itemtype, itemkey);
188 resultout := 'COMPLETE:' || l_result;
189 return;
190 ELSE
191 resultout := HandleOtherWFFuncmode(funcmode);
192 return;
193 END IF;
194
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 wf_core.context('XDPCORE_LINE', 'IS_LINE_A_PACKAGE', itemtype, itemkey, to_char(actid), funcmode);
199 raise;
200 END IS_LINE_A_PACKAGE;
201
202
203
204 -- LAUNCH_LINE_FOR_ORDER_PROCESS
205 -- Resultout
206 -- Activity Performed - Activity was completed without any errors
207 --
208 -- Your Description here:
209
210 Procedure LAUNCH_LINE_FOR_ORDER_PROCESS (itemtype in varchar2,
211 itemkey in varchar2,
212 actid in number,
213 funcmode in varchar2,
214 resultout OUT NOCOPY varchar2 ) IS
215
216 x_Progress VARCHAR2(2000);
217
218 BEGIN
219 -- RUN mode - normal process execution
220 --
221 IF (funcmode = 'RUN') THEN
222 LaunchLineForOrderProcess(itemtype, itemkey);
223 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
224 return;
225 ELSE
226 resultout := HandleOtherWFFuncmode(funcmode);
227 return;
228 END IF;
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 wf_core.context('XDPCORE_LINE', 'LAUNCH_LINE_FOR_ORDER_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
234 raise;
235 END LAUNCH_LINE_FOR_ORDER_PROCESS;
236
237
238
239
240 -- LAUNCH_SERVICE_PROCESS
241 -- Resultout
242 -- Activity Performed - Activity was completed without any errors
243 --
244 -- Your Description here:
245
246 Procedure LAUNCH_SERVICE_PROCESS (itemtype in varchar2,
247 itemkey in varchar2,
248 actid in number,
249 funcmode in varchar2,
250 resultout OUT NOCOPY varchar2 ) IS
251
252 x_Progress VARCHAR2(2000);
253
254 BEGIN
255 -- RUN mode - normal process execution
256 --
257 IF (funcmode = 'RUN') THEN
258 LaunchServiceProcess(itemtype, itemkey);
259 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
260 return;
261 ELSE
262 resultout := HandleOtherWFFuncmode(funcmode);
263 return;
264 END IF;
265
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 wf_core.context('XDPCORE_LINE', 'LAUNCH_SERVICE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
270 raise;
271 END LAUNCH_SERVICE_PROCESS;
272
273
274
275
276
277 -- ARE_ALL_LINES_DONE
278 -- Resultout
279 -- Activity Performed - Activity was completed without any errors
280 --
281 -- Your Description here:
282
283 Procedure ARE_ALL_LINES_DONE (itemtype in varchar2,
284 itemkey in varchar2,
285 actid in number,
286 funcmode in varchar2,
287 resultout OUT NOCOPY varchar2 ) IS
288 l_result varchar2(10);
289 x_Progress VARCHAR2(2000);
290
291 BEGIN
292 -- RUN mode - normal process execution
293 --
294 IF (funcmode = 'RUN') THEN
295 l_result := AreAllLinesDone(itemtype, itemkey);
296 resultout := 'COMPLETE:' || l_result;
297 return;
298 ELSE
299 resultout := HandleOtherWFFuncmode(funcmode);
300 return;
301 END IF;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 wf_core.context('XDPCORE_LINE', 'ARE_ALL_LINES_DONE', itemtype, itemkey, to_char(actid), funcmode);
306 raise;
307 END ARE_ALL_LINES_DONE;
308
309
310
311 Procedure UPDATE_INSTALL_BASE (itemtype in varchar2,
312 itemkey in varchar2,
313 actid in number,
314 funcmode in varchar2,
315 resultout OUT NOCOPY varchar2) IS
316
317 l_result varchar2(10);
318 x_Progress VARCHAR2(2000);
319
320 BEGIN
321 -- RUN mode - normal process execution
322 --
323 IF (funcmode = 'RUN') THEN
324 UpdateInstallBase(itemtype, itemkey);
325 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
326 return;
327 ELSE
328 resultout := HandleOtherWFFuncmode(funcmode);
329 return;
330 END IF;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 wf_core.context('XDPCORE_LINE', 'UPDATE_INSTALL_BASE', itemtype, itemkey, to_char(actid), funcmode);
335 raise;
336 END UPDATE_INSTALL_BASE;
337
338 Procedure LAUNCH_ALL_IND_LINES (itemtype in varchar2,
339 itemkey in varchar2,
340 actid in number,
341 funcmode in varchar2,
342 resultout OUT NOCOPY varchar2) IS
343
344 x_Progress VARCHAR2(2000);
345 l_result varchar2(1);
346
347 BEGIN
348 -- RUN mode - normal process execution
349 --
350 IF (funcmode = 'RUN') THEN
351 l_result := LaunchAllIndLines(itemtype, itemkey);
352 resultout := 'COMPLETE:' || l_result;
353 return;
354 ELSE
355 resultout := HandleOtherWFFuncmode(funcmode);
356 return;
357 END IF;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 wf_core.context('XDPCORE_LINE', 'LAUNCH_ALL_IND_LINES', itemtype, itemkey, to_char(actid), funcmode);
362 raise;
363 END LAUNCH_ALL_IND_LINES;
364
365 Procedure INITIALIZE_DEP_LINE_PROCESS (itemtype in varchar2,
366 itemkey in varchar2,
367 actid in number,
368 funcmode in varchar2,
369 resultout OUT NOCOPY varchar2) IS
370
371 x_Progress VARCHAR2(2000);
372 l_result varchar2(1);
373
374 BEGIN
375 -- RUN mode - normal process execution
376 --
377 IF (funcmode = 'RUN') THEN
378 InitializeDepLineProcess(itemtype, itemkey);
379 resultout := 'COMPLETE';
380 return;
381 ELSE
382 resultout := HandleOtherWFFuncmode(funcmode);
383 return;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 wf_core.context('XDPCORE_LINE', 'INITIALIZE_DEP_LINE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
389 raise;
390 END INITIALIZE_DEP_LINE_PROCESS;
391
392 Procedure RESOLVE_IND_DEP_LINES (itemtype in varchar2,
393 itemkey in varchar2,
394 actid in number,
395 funcmode in varchar2,
396 resultout OUT NOCOPY varchar2) IS
397
398 x_Progress VARCHAR2(2000);
399 l_result varchar2(20);
400
401 BEGIN
402 -- RUN mode - normal process execution
403 --
404 IF (funcmode = 'RUN') THEN
405 l_result := ResolveIndDepLines(itemtype, itemkey);
406 resultout := 'COMPLETE:' || l_result;
407 return;
408 ELSE
409 resultout := HandleOtherWFFuncmode(funcmode);
410 return;
411 END IF;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 wf_core.context('XDPCORE_LINE', 'RESOLVE_IND_DEP_LINES', itemtype, itemkey, to_char(actid), funcmode);
416 raise;
417 END RESOLVE_IND_DEP_LINES;
418
419 Procedure IS_SER_PART_PACKAGE (itemtype in varchar2,
420 itemkey in varchar2,
421 actid in number,
422 funcmode in varchar2,
423 resultout OUT NOCOPY varchar2) IS
424
425 x_Progress VARCHAR2(2000);
426 l_result varchar2(20);
427
428 BEGIN
429 -- RUN mode - normal process execution
430 --
431 IF (funcmode = 'RUN') THEN
432 l_result := IsSerPartPackage(itemtype, itemkey);
433 resultout := 'COMPLETE:' || l_result;
434 return;
435 ELSE
436 resultout := HandleOtherWFFuncmode(funcmode);
437 return;
438 END IF;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 wf_core.context('XDPCORE_LINE', 'IS_SER_PART_PACKAGE', itemtype, itemkey, to_char(actid), funcmode);
443 raise;
444 END IS_SER_PART_PACKAGE;
445
446
447
448 -- **************** PUBLISH_XDP_LINE_DONE *********************
449
450 PROCEDURE PUBLISH_XDP_LINE_DONE
451 (itemtype IN VARCHAR2,
452 itemkey IN VARCHAR2,
453 actid IN NUMBER,
454 funcmode IN VARCHAR2,
455 resultout OUT NOCOPY VARCHAR2) IS
456
457 x_progress VARCHAR2(4000);
458 l_resultout VARCHAR2(240);
459
460 BEGIN
461
462 IF (funcmode = 'RUN') THEN
463 PublishXDPLineDone(itemtype, itemkey,actid,l_resultout);
464 resultout := l_resultout ;
465 return;
466 ELSE
467 resultout := HandleOtherWFFuncmode(funcmode);
468 return;
469 END IF;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 wf_core.context('XDPCORE_LINE', 'LINE_FULFILLMENT_DONE', itemtype, itemkey, to_char(actid), funcmode);
474 raise;
475 END PUBLISH_XDP_LINE_DONE ;
476
477
478
479 /****
480 All the Private Functions
481 ****/
482
483 Function HandleOtherWFFuncmode( funcmode in varchar2) return varchar2
484 is
485 resultout varchar2(30);
486 x_Progress VARCHAR2(2000);
487
488 begin
489
490 IF (funcmode = 'CANCEL') THEN
491 resultout := 'COMPLETE';
492 END IF;
493
494 IF (funcmode = 'RESPOND') THEN
495 resultout := 'COMPLETE';
496 END IF;
497
498 IF (funcmode = 'FORWARD') THEN
499 resultout := 'COMPLETE';
500 END IF;
501
502 IF (funcmode = 'TRANSFER') THEN
503 resultout := 'COMPLETE';
504 END IF;
505
506 IF (funcmode = 'TIMEOUT') THEN
507 resultout := 'COMPLETE';
508 END IF;
509
510 IF (funcmode = 'others') THEN
511 resultout := 'COMPLETE';
512 END IF;
513
514
515 return resultout;
516
517 end;
518
519
520
521 Function AreAllLinesDone (itemtype in varchar2,
522 itemkey in varchar2) return varchar2
523 is
524
525 l_LineCaller varchar2(40);
526 l_result varchar2(10);
527
528 x_Progress VARCHAR2(2000);
529
530 begin
531
532 l_LineCaller := wf_engine.GetItemAttrText(itemtype => AreAllLinesDone.itemtype,
533 itemkey => AreAllLinesDone.itemkey,
534 aname => 'LINE_PROCESSING_CALLER');
535
536 if l_LineCaller = 'BUNDLE' then
537 l_result := AreAllLinesDoneForBundleCaller(itemtype => AreAllLinesDone.itemtype,
538 itemkey => AreAllLinesDone.itemkey);
539 else
540 l_result := AreAllLinesDoneForOrderCaller(itemtype => AreAllLinesDone.itemtype,
541 itemkey => AreAllLinesDone.itemkey);
542
543 end if;
544
545 return l_result;
546
547 exception
548 when others then
549 x_Progress := 'XDPCORE_LINE.AreAllLinesDone. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
550 wf_core.context('XDPCORE_LINE', 'AreAllLinesDone', itemtype, itemkey, null, x_Progress);
551 raise;
552 end AreAllLinesDone;
553
554
555
556 Function AreAllLinesDoneForBundleCaller (itemtype in varchar2,
557 itemkey in varchar2) return varchar2
558 is
559
560 l_OrderID number;
561 l_BundleID number;
562 l_LineItemID number;
563 l_CurrentLineSeq number;
564
565 l_LineNumber number;
566 l_PackageFlag varchar2(1);
567 l_ib_source varchar2(20);
568 l_ib_source_id number;
569
570 l_LineName varchar2(40);
571
572 e_InvalidConfigException exception;
573 x_Progress VARCHAR2(2000);
574
575 begin
576
577 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => AreAllLinesDoneForBundleCaller.itemtype,
578 itemkey => AreAllLinesDoneForBundleCaller.itemkey,
579 aname => 'ORDER_ID');
580
581 l_BundleID := wf_engine.GetItemAttrNumber(itemtype => AreAllLinesDoneForBundleCaller.itemtype,
582 itemkey => AreAllLinesDoneForBundleCaller.itemkey,
583 aname => 'BUNDLE_ID');
584
585 l_CurrentLineSeq := wf_engine.GetItemAttrNumber(itemtype => AreAllLinesDoneForBundleCaller.itemtype,
586 itemkey => AreAllLinesDoneForBundleCaller.itemkey,
587 aname => 'CURRENT_LINE_SEQUENCE');
588
589
590 /* If the Caller is a bundle and the bundle ID is null then process all the line items for
591 ** that order with a bundleid of null
592 **/
593
594 if l_BundleID is null or l_BundleID = -1 then
595 if c_LineSeqNullBundle%ISOPEN then
596 close c_LineSeqNullBundle;
597 end if;
598
599 open c_LineSeqNullBundle(l_OrderID, l_CurrentLineSeq);
600 Fetch c_LineSeqNullBundle
601 into l_LineItemID, l_LineNumber,l_LineName, l_PackageFlag, l_CurrentLineSeq , l_ib_source , l_ib_source_id ;
602
603 if c_LineSeqNullBundle%NOTFOUND then
604 /* No more Lines's for the current bundle to be done */
605 close c_LineSeqNullBundle;
606 return ('Y');
607 else
608 /* There are more lines for the current bundle to be done */
609 close c_LineSeqNullBundle;
610 return ('N');
611
612 end if;
613
614 if c_LineSeqNullBundle%ISOPEN then
615 close c_LineSeqNullBundle;
616 end if;
617
618 else
619 /* The Bundle caller is for a specific bundle id */
620 if c_LineSeqForBundle%ISOPEN then
621 close c_LineSeqForBundle;
622 end if;
623
624 open c_LineSeqForBundle(l_OrderID, l_BundleID, l_CurrentLineSeq);
625 -- Fetch c_LineSeqForBundle into l_LineItemID, l_CurrentLineSeq;
626 Fetch c_LineSeqForBundle
627 into l_LineItemID, l_LineNumber,l_LineName, l_PackageFlag, l_CurrentLineSeq , l_ib_source , l_ib_source_id ;
628
629 if c_LineSeqForBundle%NOTFOUND then
630 /* No more Lines's for the current bundle to be done */
631 close c_LineSeqForBundle;
632 return ('Y');
633 else
634 /* There are more lines for the current bundle to be done */
635 close c_LineSeqForBundle;
636 return ('N');
637 end if;
638
639 if c_LineSeqForBundle%ISOPEN then
640 close c_LineSeqForBundle;
641 end if;
642
643 end if;
644
645 exception
646 when e_InvalidConfigException then
647 if c_LineSeqNullBundle%ISOPEN then
648 close c_LineSeqNullBundle;
649 end if;
650
651 if c_LineSeqForBundle%ISOPEN then
652 close c_LineSeqForBundle;
653 end if;
654
655 wf_core.context('XDPCORE_LINE', 'AreAllLinesDoneForBundleCaller', itemtype, itemkey, null, x_Progress);
656 raise;
657 when others then
658 if c_LineSeqNullBundle%ISOPEN then
659 close c_LineSeqNullBundle;
660 end if;
661
662 if c_LineSeqForBundle%ISOPEN then
663 close c_LineSeqForBundle;
664 end if;
665
666 x_Progress := 'XDPCORE_LINE.AreAllLinesDoneForBundleCaller. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
667 wf_core.context('XDPCORE_LINE', 'AreAllLinesDoneForBundleCaller', itemtype, itemkey, null, x_Progress);
668 raise;
669 end AreAllLinesDoneForBundleCaller;
670
671
672
673 Function AreAllLinesDoneForOrderCaller (itemtype in varchar2,
674 itemkey in varchar2) return varchar2
675 is
676
677 l_CurrentLineSeq number;
678 l_OrderID number;
679 l_LineSeq number;
680 l_LineItemID number;
681
682 l_CurrentDBSeq number;
683 l_LineNumber number;
684 l_PackageFlag varchar2(1);
685 l_ib_source varchar2(30);
686 l_ib_source_id number;
687 l_LineName varchar2(40);
688
689 e_NoLinesFoundException exception;
690 x_Progress VARCHAR2(2000);
691
692 begin
693
694 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => AreAllLinesDoneForOrderCaller.itemtype,
695 itemkey => AreAllLinesDoneForOrderCaller.itemkey,
696 aname => 'ORDER_ID');
697
698 l_CurrentLineSeq := wf_engine.GetItemAttrNumber(itemtype => AreAllLinesDoneForOrderCaller.itemtype,
699 itemkey => AreAllLinesDoneForOrderCaller.itemkey,
700 aname => 'CURRENT_LINE_SEQUENCE');
701
702 if c_LineSeq%ISOPEN then
703 close c_LineSeq;
704 end if;
705
706
707
708 open c_LineSeq(l_OrderID, l_CurrentLineSeq);
709 Fetch c_LineSeq
710 into l_LineItemID, l_LineNumber,l_LineName, l_PackageFlag, l_CurrentDBSeq,l_ib_source,l_ib_source_id;
711
712 if c_LineSeq%NOTFOUND then
713 /* No more Lines's for the current bundle to be done */
714 close c_LineSeq;
715 return ('Y');
716 else
717 /* There are more lines for the current bundle to be done */
718 close c_LineSeq;
719 return ('N');
720 end if;
721
722 if c_LineSeq%ISOPEN then
723 close c_LineSeq;
724 end if;
725
726 exception
727 when e_NoLinesFoundException then
728 if c_LineSeq%ISOPEN then
729 close c_LineSeq;
730 end if;
731
732 wf_core.context('XDPCORE_LINE', 'AreAllLinesDoneForOrderCaller', itemtype, itemkey, null, x_Progress);
733 raise;
734
735 when others then
736 if c_LineSeq%ISOPEN then
737 close c_LineSeq;
738 end if;
739
740 x_Progress := 'XDPCORE_LINE.AreAllLinesDoneForOrderCaller. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
741 wf_core.context('XDPCORE_LINE', 'AreAllLinesDoneForOrderCaller', itemtype, itemkey, null, x_Progress);
742 raise;
743 end AreAllLinesDoneForOrderCaller;
744
745
746
747 Procedure LaunchLineItemProcess (itemtype in varchar2,
748 itemkey in varchar2)
749 is
750 l_LineCaller varchar2(40);
751 x_Progress VARCHAR2(2000);
752
753 begin
754
755 l_LineCaller := wf_engine.GetItemAttrText(itemtype => LaunchLineItemProcess.itemtype,
756 itemkey => LaunchLineItemProcess.itemkey,
757 aname => 'LINE_PROCESSING_CALLER');
758
759 if l_LineCaller = 'BUNDLE' then
760 LaunchLineItemProcessForBundle(itemtype => LaunchLineItemProcess.itemtype,
761 itemkey => LaunchLineItemProcess.itemkey);
762 else
763 LaunchLineItemProcessForOrder(itemtype => LaunchLineItemProcess.itemtype,
764 itemkey => LaunchLineItemProcess.itemkey);
765
766 end if;
767
768
769 exception
770 when others then
771 x_Progress := 'XDPCORE_LINE.LaunchLineItemProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
772 wf_core.context('XDPCORE_LINE', 'LaunchLineItemProcess', itemtype, itemkey, null, x_Progress);
773 raise;
774 end LaunchLineItemProcess;
775
776
777
778 Procedure LaunchLineItemProcessForBundle (itemtype in varchar2,
779 itemkey in varchar2)
780 is
781 l_BundleID number;
782 l_OrderID number;
783 l_CurrentLineSeq number;
784 l_PrevLineSeq number;
785 l_LineItemID number;
786 l_Counter number := 0;
787 l_LineNumber number;
788 l_PackageFlag varchar2(1);
789 l_ib_source varchar2(20);
790 l_ib_source_id number ;
791
792 l_tempKey varchar2(240);
793 l_LineName varchar2(40);
794
795 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
796 t_ChildKeys t_ChildKeyTable;
797
798 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
799 t_ChildTypes t_ChildTypeTable;
800
801 TYPE t_IDTable is table of number INDEX BY BINARY_INTEGER;
802 t_WiIDList t_IDTable;
803 t_PriorityList t_IDTable;
804
805 e_InvalidConfigException exception;
806 e_AddAttributeException exception;
807 x_Progress VARCHAR2(2000);
808
809 ErrCode number;
810 ErrStr varchar2(1996);
811
812 begin
813
814 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchLineItemProcessForBundle.itemtype,
815 itemkey => LaunchLineItemProcessForBundle.itemkey,
816 aname => 'ORDER_ID');
817
818 l_BundleID := wf_engine.GetItemAttrNumber(itemtype => LaunchLineItemProcessForBundle.itemtype,
819 itemkey => LaunchLineItemProcessForBundle.itemkey,
820 aname => 'BUNDLE_ID');
821
822 l_PrevLineSeq := wf_engine.GetItemAttrNumber(itemtype => LaunchLineItemProcessForBundle.itemtype,
823 itemkey => LaunchLineItemProcessForBundle.itemkey,
824 aname => 'CURRENT_LINE_SEQUENCE');
825
826
827 if c_LineSeqNullBundle%ISOPEN then
828 close c_LineSeqNullBundle;
829 end if;
830
831
832 if l_BundleID is null or l_BundleID = -1 then
833 Open c_LineSeqNullBundle(l_OrderID, l_PrevLineSeq);
834
835 LOOP
836 Fetch c_LineSeqNullBundle
837 into l_LineItemID, l_LineNumber,l_LineName, l_PackageFlag, l_CurrentLineSeq, l_ib_source,l_ib_source_id;
838
839 EXIT when c_LineSeqNullBundle%NOTFOUND;
840
841 l_Counter := l_Counter + 1;
842
843 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
844 l_tempkey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
845
846 t_ChildTypes(l_Counter) := 'XDPPROV';
847 t_ChildKeys(l_Counter) := l_tempKey;
848
849 -- Create Process and Bulk Set Item Attribute
850 XDPCORE.CreateNAddAttrNParentLabel
851 (itemtype => t_ChildTypes(l_Counter),
852 itemkey => t_ChildKeys(l_Counter),
853 processname => 'LINE_PROCESSING_PROCESS',
854 parentitemtype => LaunchLineItemProcessForBundle.itemtype,
855 parentitemkey => LaunchLineItemProcessForBundle.itemkey,
856 waitflowlabel => 'WAITFORFLOW-LINE-DEP',
857 OrderID => l_OrderID,
858 LineitemID => l_LineItemID,
859 WIInstanceID => null,
860 FAInstanceID => null);
861
862 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
863 itemkey => l_tempKey,
864 aname => 'MASTER_TO_CONTINUE',
865 avalue => 'WAITFORFLOW-LINE-DEP');
866
867 AddLineAttributes(itemtype => t_ChildTypes(l_Counter),
868 itemkey => t_ChildKeys(l_Counter),
869 Module => 'XDPCORE_WI.LaunchLinetItemProcessForBundle',
870 IsPackageFlag => l_PackageFlag,
871 ibsource => l_ib_source,
872 ibsourceid => l_ib_source_id,
873 LineProcessCaller => 'ORDER',
874 CurrentLineSequence => null,
875 errcode => ErrCode,
876 errstr => ErrStr);
877
878 if ErrCode <> 0 then
879 x_progress := ErrStr;
880 raise e_AddAttributeException;
881 end if;
882
883
884 END LOOP;
885
886 close c_LineSeqNullBundle;
887
888 if l_Counter = 0 and l_CurrentLineSeq = 0 then
889 x_Progress := 'XDPCORE_LINE.LaunchLineItemProcessForBundle. Could not find any Lines for Independet Bundle for OrderID: ' || l_OrderID;
890 RAISE e_InvalidConfigException;
891 else
892
893 XDPCORE.CheckNAddItemAttrNumber (itemtype => LaunchLineItemProcessForBundle.itemtype,
894 itemkey => LaunchLineItemProcessForBundle.itemkey,
895 AttrName => 'CURRENT_LINE_SEQUENCE',
896 AttrValue => l_CurrentLineSeq,
897 ErrCode => ErrCode,
898 ErrStr => ErrStr);
899
900 if ErrCode <> 0 then
901 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForBundle. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
902 raise e_AddAttributeException;
903 end if;
904
905 /* Launch the Line Item Fulfillment Process */
906 FOR i in 1..l_Counter LOOP
907 wf_engine.StartProcess(t_ChildTypes(i),
908 t_ChildKeys(i));
909
910 END LOOP;
911
912 end if;
913
914 else
915
916 if c_LineSeqForBundle%ISOPEN then
917 close c_LineSeqForBundle;
918 end if;
919
920 Open c_LineSeqForBundle(l_OrderID, l_BundleID, l_PrevLineSeq);
921
922 LOOP
923 Fetch c_LineSeqForBundle
924 into l_LineItemID, l_LineNumber,l_LineName, l_PackageFlag, l_CurrentLineSeq, l_ib_source , l_ib_source_id ;
925
926 EXIT when c_LineSeqForBundle%NOTFOUND;
927
928 l_Counter := l_Counter + 1;
929
930 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
931 l_tempkey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
932
933 t_ChildTypes(l_Counter) := 'XDPPROV';
934 t_ChildKeys(l_Counter) := l_tempKey;
935
936
937 -- Create Process and Bulk Set Item Attribute
938 XDPCORE.CreateNAddAttrNParentLabel(itemtype => t_ChildTypes(l_Counter),
939 itemkey => t_ChildKeys(l_Counter),
940 processname => 'LINE_PROCESSING_PROCESS',
941 parentitemtype => LaunchLineItemProcessForBundle.itemtype,
942 parentitemkey => LaunchLineItemProcessForBundle.itemkey,
943 waitflowlabel => 'WAITFORFLOW-LINE-DEP',
944 OrderID => l_OrderID,
945 LineitemID => l_LineItemID,
946 WIInstanceID => null,
947 FAInstanceID => null);
948
949 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
950 itemkey => l_tempKey,
951 aname => 'MASTER_TO_CONTINUE',
952 avalue => 'WAITFORFLOW-LINE-DEP');
953
954 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
955 itemkey => t_ChildKeys(l_Counter),
956 AttrName => 'IS_PACKAGE_FLAG',
957 AttrValue => l_PackageFlag,
958 ErrCode => ErrCode,
959 ErrStr => ErrStr);
960
961 if ErrCode <> 0 then
962 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForBundle. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
963 raise e_AddAttributeException;
964 end if;
965
966 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
967 itemkey => t_ChildKeys(l_Counter),
968 AttrName => 'IB_SOURCE',
969 AttrValue => l_ib_source,
970 ErrCode => ErrCode,
971 ErrStr => ErrStr);
972
973 if ErrCode <> 0 then
974 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForBundle. Error when adding Item Attribute IB_SOURCE. Error: ' || substr(ErrStr,1,1500);
975 raise e_AddAttributeException;
976 end if;
977
978 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
979 itemkey => t_ChildKeys(l_Counter),
980 AttrName => 'IB_SOURCE_ID',
981 AttrValue => l_ib_source_id,
982 ErrCode => ErrCode,
983 ErrStr => ErrStr);
984
985 if ErrCode <> 0 then
986 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForBundle. Error when adding Item Attribute IB_SOURCE_ID. Error: ' || substr(ErrStr,1,1500);
987 raise e_AddAttributeException;
988 end if;
989 END LOOP;
990
991 close c_LineSeqForBundle;
992
993 if l_Counter = 0 and l_CurrentLineSeq = 0 then
994 x_Progress := 'XDPCORE_LINE.LaunchLineItemProcessForBundle. Could not find any Lines for BundleID: ' || l_BundleID || ' OrderID: ' || l_OrderID;
995 RAISE e_InvalidConfigException;
996 else
997
998 XDPCORE.CheckNAddItemAttrNumber (itemtype => LaunchLineItemProcessForBundle.itemtype,
999 itemkey => LaunchLineItemProcessForBundle.itemkey,
1000 AttrName => 'CURRENT_LINE_SEQUENCE',
1001 AttrValue => l_CurrentLineSeq,
1002 ErrCode => ErrCode,
1003 ErrStr => ErrStr);
1004
1005 if ErrCode <> 0 then
1006 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForBundle. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1007 raise e_AddAttributeException;
1008 end if;
1009
1010 /* Launch the Line Item Fulfillment Process */
1011 FOR i in 1..l_Counter LOOP
1012 wf_engine.StartProcess(t_ChildTypes(i),
1013 t_ChildKeys(i));
1014 END LOOP;
1015
1016 end if;
1017 end if;
1018
1019 exception
1020 when e_AddAttributeException then
1021 if c_LineSeqNullBundle%ISOPEN then
1022 close c_LineSeqNullBundle;
1023 end if;
1024
1025 if c_LineSeqForBundle%ISOPEN then
1026 close c_LineSeqForBundle;
1027 end if;
1028
1029 wf_core.context('XDPCORE_WI', 'LaunchLineItemProcessForBundle', itemtype, itemkey, null, x_Progress);
1030 raise;
1031
1032 when e_InvalidConfigException then
1033 if c_LineSeqNullBundle%ISOPEN then
1034 close c_LineSeqNullBundle;
1035 end if;
1036
1037 if c_LineSeqForBundle%ISOPEN then
1038 close c_LineSeqForBundle;
1039 end if;
1040
1041 wf_core.context('XDPCORE_LINE', 'LaunchLineItemProcessForBundle', itemtype, itemkey, null, x_Progress);
1042 raise;
1043
1044 when others then
1045 if c_LineSeqNullBundle%ISOPEN then
1046 close c_LineSeqNullBundle;
1047 end if;
1048
1049 if c_LineSeqForBundle%ISOPEN then
1050 close c_LineSeqForBundle;
1051 end if;
1052
1053 x_Progress := 'XDPCORE_LINE.LaunchLineItemProcessForBundle. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1054 wf_core.context('XDPCORE_LINE', 'LaunchLineItemProcessForBundle', itemtype, itemkey, null, x_Progress);
1055 raise;
1056 end LaunchLineItemProcessForBundle;
1057
1058
1059
1060 Procedure LaunchLineItemProcessForOrder (itemtype in varchar2,
1061 itemkey in varchar2)
1062 is
1063 l_OrderID number;
1064 l_CurrentLineSeq number;
1065 l_PrevLineSeq number;
1066 l_LineItemID number;
1067 l_Counter number := 0;
1068 l_LineNumber number;
1069 l_PackageFlag varchar2(1);
1070 l_ib_source varchar2(20);
1071 l_ib_source_id number;
1072
1073 l_tempKey varchar2(240);
1074 l_LineName varchar2(40);
1075
1076 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1077 t_ChildKeys t_ChildKeyTable;
1078
1079 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
1080 t_ChildTypes t_ChildTypeTable;
1081
1082 TYPE t_IDTable is table of number INDEX BY BINARY_INTEGER;
1083 t_WiIDList t_IDTable;
1084 t_PriorityList t_IDTable;
1085
1086 e_InvalidConfigException exception;
1087 e_AddAttributeException exception;
1088
1089 x_Progress VARCHAR2(2000);
1090 ErrCode number;
1091 ErrStr varchar2(1996);
1092
1093 begin
1094
1095 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchLineItemProcessForOrder.itemtype,
1096 itemkey => LaunchLineItemProcessForOrder.itemkey,
1097 aname => 'ORDER_ID');
1098
1099 l_PrevLineSeq := wf_engine.GetItemAttrNumber(itemtype => LaunchLineItemProcessForOrder.itemtype,
1100 itemkey => LaunchLineItemProcessForOrder.itemkey,
1101 aname => 'CURRENT_LINE_SEQUENCE');
1102
1103
1104 if c_LineSeq%ISOPEN then
1105 close c_LineSeq;
1106 end if;
1107
1108 Open c_LineSeq(l_OrderID, l_PrevLineSeq);
1109
1110 LOOP
1111 Fetch c_LineSeq into l_LineItemID, l_LineNumber,l_LineName, l_PackageFlag,
1112 l_CurrentLineSeq,l_ib_source,l_ib_source_id;
1113
1114 EXIT when c_LineSeq%NOTFOUND;
1115
1116 l_Counter := l_Counter + 1;
1117
1118 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1119 l_tempkey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1120
1121 t_ChildTypes(l_Counter) := 'XDPPROV';
1122 t_ChildKeys(l_Counter) := l_tempKey;
1123
1124 -- Create Process and Bulk Set Item Attribute
1125 XDPCORE.CreateNAddAttrNParentLabel(itemtype => t_ChildTypes(l_Counter),
1126 itemkey => t_ChildKeys(l_Counter),
1127 processname => 'LINE_PROCESSING_PROCESS',
1128 parentitemtype => LaunchLineItemProcessForOrder.itemtype,
1129 parentitemkey => LaunchLineItemProcessForOrder.itemkey,
1130 waitflowlabel => 'WAITFORFLOW-LINE-DEP',
1131 OrderID => l_OrderID,
1132 LineitemID => l_LineItemID,
1133 WIInstanceID => null,
1134 FAInstanceID => null);
1135
1136
1137 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1138 itemkey => l_tempKey,
1139 aname => 'MASTER_TO_CONTINUE',
1140 avalue => 'WAITFORFLOW-LINE-DEP');
1141
1142 -- Adding IS_PACKAGE_FLAG to the itemattrlist
1143
1144 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1145 itemkey => t_ChildKeys(l_Counter),
1146 AttrName => 'IS_PACKAGE_FLAG',
1147 AttrValue => l_PackageFlag,
1148 ErrCode => ErrCode,
1149 ErrStr => ErrStr);
1150
1151 if ErrCode <> 0 then
1152 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForOrder. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
1153 raise e_AddAttributeException;
1154 end if;
1155
1156 -- Adding IB_SOURCE to the itemattrlist
1157
1158 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1159 itemkey => t_ChildKeys(l_Counter),
1160 AttrName => 'IB_SOURCE',
1161 AttrValue => l_ib_source,
1162 ErrCode => ErrCode,
1163 ErrStr => ErrStr);
1164
1165 if ErrCode <> 0 then
1166 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForOrder. Error when adding Item Attribute IB_SOURCE. Error: ' || substr(ErrStr,1,1500);
1167 raise e_AddAttributeException;
1168 end if;
1169
1170 -- Adding IB_SOURCE_ID to the itemattrlist
1171
1172 XDPCORE.CheckNAddItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1173 itemkey => t_ChildKeys(l_Counter),
1174 AttrName => 'IB_SOURCE_ID',
1175 AttrValue => l_ib_source_id,
1176 ErrCode => ErrCode,
1177 ErrStr => ErrStr);
1178
1179 if ErrCode <> 0 then
1180 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForOrder. Error when adding Item Attribute IB_SOURCE_ID. Error: ' || substr(ErrStr,1,1500);
1181 raise e_AddAttributeException;
1182 end if;
1183
1184 END LOOP;
1185
1186 close c_LineSeq;
1187
1188 if l_Counter = 0 and l_CurrentLineSeq = 0 then
1189 x_Progress := 'XDPCORE_LINE.LaunchLineItemProcessForOrder. Could not find any Lines for OrderID: ' || l_OrderID || ' Current Line Seqeuence: ' || l_PrevLineSeq;
1190 RAISE e_InvalidConfigException;
1191 else
1192 XDPCORE.CheckNAddItemAttrNumber (itemtype => LaunchLineItemProcessForOrder.itemtype,
1193 itemkey => LaunchLineItemProcessForOrder.itemkey,
1194 AttrName => 'CURRENT_LINE_SEQUENCE',
1195 AttrValue => l_CurrentLineSeq,
1196 ErrCode => ErrCode,
1197 ErrStr => ErrStr);
1198
1199 if ErrCode <> 0 then
1200 x_progress := 'In XDPCORE_WI.LaunchLinetItemProcessForOrder. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1201 raise e_AddAttributeException;
1202 end if;
1203
1204
1205 /* Launch the Line Item Fulfillment Process */
1206 FOR i in 1..l_Counter LOOP
1207 wf_engine.StartProcess(t_ChildTypes(i),
1208 t_ChildKeys(i));
1209
1210 END LOOP;
1211
1212 end if;
1213
1214
1215 exception
1216 when e_AddAttributeException then
1217 if c_LineSeq%ISOPEN then
1218 close c_LineSeq;
1219 end if;
1220
1221 wf_core.context('XDPCORE_WI', 'LaunchLineItemProcessForOrder', itemtype, itemkey, null, x_Progress);
1222 raise;
1223
1224 when e_InvalidConfigException then
1225 if c_LineSeq%ISOPEN then
1226 close c_LineSeq;
1227 end if;
1228
1229 wf_core.context('XDPCORE_LINE', 'LaunchLineItemProcessForOrder', itemtype, itemkey, null, x_Progress);
1230 raise;
1231
1232 when others then
1233 if c_LineSeq%ISOPEN then
1234 close c_LineSeq;
1235 end if;
1236
1237 x_Progress := 'XDPCORE_LINE.LaunchLineItemProcessForOrder. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1238 wf_core.context('XDPCORE_LINE', 'LaunchLineItemProcessForOrder', itemtype, itemkey, null, x_Progress);
1239 raise;
1240 end LaunchLineItemProcessForOrder;
1241
1242
1243
1244 Procedure LaunchLineForOrderProcess (itemtype in varchar2,
1245 itemkey in varchar2)
1246 is
1247 l_OrderID number;
1248 l_Counter number := 0;
1249 l_LineItemID number;
1250 l_Priority number;
1251
1252 l_IsPackageFlag varchar2(10);
1253 l_tempKey varchar2(240);
1254 l_LineMaster varchar2(40);
1255 l_ib_source varchar2(20) ;
1256 l_ib_source_id number ;
1257
1258 e_NoLinesFoundException exception;
1259 e_AddAttributeException exception;
1260
1261 cursor c_GetIndLines(OrderID number) is
1262 select LINE_ITEM_ID, IS_PACKAGE_FLAG, PRIORITY ,IB_SOURCE, NVL(IB_SOURCE_ID,-999) IB_SOURCE_ID
1263 from XDP_ORDER_LINE_ITEMS
1264 where ORDER_ID = OrderID
1265 and IS_VIRTUAL_LINE_FLAG = 'N'
1266 and STATUS_CODE = 'READY'
1267 and LINE_SEQUENCE = 0;
1268
1269 cursor c_GetDepLines(OrderID number) is
1270 select LINE_ITEM_ID
1271 from XDP_ORDER_LINE_ITEMS
1272 where ORDER_ID = OrderID
1273 and IS_VIRTUAL_LINE_FLAG = 'N'
1274 and STATUS_CODE = 'READY'
1275 and LINE_SEQUENCE > 0;
1276
1277
1278 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1279 t_ChildKeys t_ChildKeyTable;
1280
1281 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
1282 t_ChildTypes t_ChildTypeTable;
1283
1284 x_Progress VARCHAR2(2000);
1285
1286 ErrCode number;
1287 ErrStr varchar2(1996);
1288
1289 begin
1290
1291 l_Counter := 0;
1292
1293 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchLineForOrderProcess.itemtype,
1294 itemkey => LaunchLineForOrderProcess.itemkey,
1295 aname => 'ORDER_ID');
1296
1297 l_LineMaster := 'WAITFORFLOW-ORDER_LINE';
1298
1299
1300 if c_GetIndLines%ISOPEN then
1301 close c_GetIndLines;
1302 end if;
1303
1304 /* Get all the independent lines */
1305
1306 open c_GetIndLines(l_OrderID);
1307 LOOP
1308 Fetch c_GetIndLines into l_LineItemID, l_IsPackageFlag, l_Priority,l_ib_source,l_ib_source_id;
1309 EXIT when c_GetIndLines%NOTFOUND;
1310
1311 l_Counter := l_Counter + 1;
1312 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1313 l_tempKey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1314
1315 t_ChildTypes(l_Counter) := 'XDPPROV';
1316 t_ChildKeys(l_Counter) := l_tempKey;
1317
1318 -- Create Process and Bulk Set Item Attribute
1319 XDPCORE.CreateAndAddAttrNum(itemtype => t_ChildTypes(l_Counter),
1320 itemkey => t_ChildKeys(l_Counter),
1321 processname => 'LINE_PROCESSING_PROCESS',
1322 parentitemtype => LaunchLineForOrderProcess.itemtype,
1323 parentitemkey => LaunchLineForOrderProcess.itemkey,
1324 OrderID => l_OrderID,
1325 LineitemID => l_LineItemID,
1326 WIInstanceID => null,
1327 FAInstanceID => null);
1328
1329 AddLineAttributes(itemtype => t_ChildTypes(l_Counter),
1330 itemkey => t_ChildKeys(l_Counter),
1331 Module => 'XDPCORE_WI.LaunchLinetItemForOrderProcess',
1332 IsPackageFlag => l_IsPackageFlag,
1333 ibsource => l_ib_source,
1334 ibsourceid => l_ib_source_id,
1335 LineProcessCaller => 'ORDER',
1336 CurrentLineSequence => null,
1337 errcode => ErrCode,
1338 errstr => ErrStr);
1339
1340
1341 if ErrCode <> 0 then
1342 x_progress := ErrStr;
1343 raise e_AddAttributeException;
1344 end if;
1345
1346 END LOOP;
1347 close c_GetIndLines;
1348
1349 if c_GetDepLines%ISOPEN then
1350 close c_GetDepLines;
1351 end if;
1352
1353 /* Launch One Dependent Line Item Processing Process */
1354 open c_GetDepLines(l_OrderID);
1355
1356 Fetch c_GetDepLines into l_LineItemID;
1357 if c_GetDepLines%FOUND then
1358 /**
1359 ** Add the new line item id to the list if wf's to be started
1360 ** and Create an instance if the dependent line item process
1361 **/
1362 l_Counter := l_Counter + 1;
1363
1364 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1365 l_tempKey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1366
1367 t_ChildTypes(l_Counter) := 'XDPPROV';
1368 t_ChildKeys(l_Counter) := l_tempKey;
1369
1370 -- Create Process and Bulk Set Item Attribute
1371 XDPCORE.CreateAndAddAttrNum(itemtype => t_ChildTypes(l_Counter),
1372 itemkey => t_ChildKeys(l_Counter),
1373 processname => 'LINE_SEQ_PROCESSING',
1374 parentitemtype => LaunchLineForOrderProcess.itemtype,
1375 parentitemkey => LaunchLineForOrderProcess.itemkey,
1376 OrderID => l_OrderID,
1377 LineitemID => l_LineItemID,
1378 WIInstanceID => null,
1379 FAInstanceID => null);
1380
1381 AddLineAttributes(itemtype => t_ChildTypes(l_Counter),
1382 itemkey => t_ChildKeys(l_Counter),
1383 Module => 'XDPCORE_WI.LaunchLinetItemForOrderProcess',
1384 IsPackageFlag => null,
1385 ibsource => null,
1386 ibsourceid => null,
1387 LineProcessCaller => 'ORDER',
1388 CurrentLineSequence => 0,
1389 errcode => ErrCode,
1390 errstr => ErrStr);
1391
1392 if ErrCode <> 0 then
1393 x_progress := ErrStr;
1394 raise e_AddAttributeException;
1395 end if;
1396
1397 end if;
1398
1399
1400 close c_GetDepLines;
1401
1402 if l_Counter = 0 then
1403 x_Progress := 'XDPCORE_LINE.LaunchLineForOrderProcess. Found No lines to be processed for OrderID: ' || l_OrderID;
1404 RAISE e_NoLinesFoundException;
1405 end if;
1406
1407
1408 /* Now start the workflow process */
1409 FOR i in 1..l_Counter LOOP
1410
1411 wf_engine.StartProcess(t_ChildTypes(i),
1412 t_ChildKeys(i));
1413 END LOOP;
1414
1415 exception
1416 when e_AddAttributeException then
1417 if c_GetIndLines%ISOPEN then
1418 close c_GetIndLines;
1419 end if;
1420
1421 if c_GetDepLines%ISOPEN then
1422 close c_GetDepLines;
1423 end if;
1424
1425 wf_core.context('XDPCORE_LINE', 'LaunchLineForOrderProcess', itemtype, itemkey, null, x_Progress);
1426 raise;
1427
1428 when e_NoLinesFoundException then
1429 if c_GetIndLines%ISOPEN then
1430 close c_GetIndLines;
1431 end if;
1432
1433 if c_GetDepLines%ISOPEN then
1434 close c_GetDepLines;
1435 end if;
1436
1437 wf_core.context('XDPCORE_LINE', 'LaunchLineForOrderProcess', itemtype, itemkey, null, x_Progress);
1438 raise;
1439
1440 when others then
1441 if c_GetIndLines%ISOPEN then
1442 close c_GetIndLines;
1443 end if;
1444
1445 if c_GetDepLines%ISOPEN then
1446 close c_GetDepLines;
1447 end if;
1448
1449 x_Progress := 'XDPCORE_LINE.LaunchLineForOrderProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1450 wf_core.context('XDPCORE_LINE', 'LaunchLineForOrderProcess', itemtype, itemkey, null, x_Progress);
1451 raise;
1452 end LaunchLineForOrderProcess;
1453
1454 Function LaunchAllIndLines (itemtype in varchar2,
1455 itemkey in varchar2) return varchar2
1456 is
1457 l_LineCaller varchar2(40);
1458 x_Progress VARCHAR2(2000);
1459 l_result varchar2(1) := 'N';
1460
1461 begin
1462
1463 l_LineCaller := wf_engine.GetItemAttrText(itemtype =>LaunchAllIndLines.itemtype,
1464 itemkey =>LaunchAllIndLines.itemkey,
1465 aname => 'LINE_PROCESSING_CALLER');
1466
1467 if l_LineCaller = 'BUNDLE' then
1468 l_result := LaunchAllIndLinesForBundle(itemtype =>LaunchAllIndLines.itemtype,
1469 itemkey => LaunchAllIndLines.itemkey);
1470 else
1471 l_result := LaunchAllIndLinesForOrder(itemtype =>LaunchAllIndLines.itemtype,
1472 itemkey =>LaunchAllIndLines.itemkey);
1473
1474 end if;
1475
1476 return l_result;
1477
1478 end LaunchAllIndLines;
1479
1480 Function LaunchAllIndLinesForOrder (itemtype in varchar2,
1481 itemkey in varchar2) return varchar2
1482 is
1483 l_OrderID number;
1484 l_Counter number := 0;
1485 l_LineItemID number;
1486 l_Priority number;
1487 l_tempKey varchar2(240);
1488 l_IsPackageFlag varchar2(10);
1489 l_ib_source varchar2(20) ;
1490 l_ib_source_id number ;
1491
1492
1493 l_result varchar2(1) := 'N';
1494
1495 e_NoLinesFoundException exception;
1496 e_AddAttributeException exception;
1497
1498 cursor c_GetIndLines( OrderID number ) is
1499 select LINE_ITEM_ID, IS_PACKAGE_FLAG, PRIORITY ,IB_SOURCE, NVL(IB_SOURCE_ID,-999) IB_SOURCE_ID
1500 from XDP_ORDER_LINE_ITEMS
1501 where ORDER_ID = OrderID
1502 and IS_VIRTUAL_LINE_FLAG = 'N'
1503 and STATUS_CODE = 'READY'
1504 and LINE_SEQUENCE = 0;
1505
1506 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1507 t_ChildKeys t_ChildKeyTable;
1508
1509 x_Progress VARCHAR2(2000);
1510
1511 ErrCode number;
1512 ErrStr varchar2(1996);
1513
1514 begin
1515
1516
1517 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndLinesForOrder.itemtype,
1518 itemkey => LaunchAllIndLinesForOrder.itemkey,
1519 aname => 'ORDER_ID');
1520
1521
1522
1523 /* Get all the independent lines */
1524 FOR lv_LineRec in c_GetIndLines( l_OrderID ) LOOP
1525 l_result := 'Y';
1526 l_Counter := l_Counter + 1;
1527 l_LineItemID := lv_LineRec.LINE_ITEM_ID;
1528 l_IsPackageFlag := lv_LineRec.IS_PACKAGE_FLAG;
1529 l_Priority := lv_LineRec.PRIORITY;
1530 l_ib_source := lv_LineRec.IB_SOURCE;
1531 l_ib_source_id := lv_LineRec.IB_SOURCE_ID;
1532
1533 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1534 l_tempKey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1535
1536 t_ChildKeys(l_Counter) := l_tempKey;
1537
1538 -- Create Process and Bulk Set Item Attribute
1539 XDPCORE.CreateNAddAttrNParentLabel(itemtype => 'XDPPROV',
1540 itemkey => t_ChildKeys(l_Counter),
1541 processname => 'LINE_PROCESSING_PROCESS',
1542 parentitemtype => LaunchAllIndLinesForOrder.itemtype,
1543 parentitemkey => LaunchAllIndLinesForOrder.itemkey,
1544 waitflowlabel => 'WAITFORFLOW-LINE-IND',
1545 OrderID => l_OrderID,
1546 LineitemID => l_LineItemID,
1547 WIInstanceID => null,
1548 FAInstanceID => null);
1549
1550 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1551 itemkey => t_ChildKeys(l_Counter),
1552 aname => 'MASTER_TO_CONTINUE',
1553 avalue => 'WAITFORFLOW-LINE-IND');
1554
1555
1556 AddLineAttributes(itemtype => 'XDPPROV',
1557 itemkey => t_ChildKeys(l_Counter),
1558 Module => 'XDPCORE_WI.LaunchLinetItemForOrderProcess',
1559 IsPackageFlag => l_IsPackageFlag,
1560 ibsource => l_ib_source,
1561 ibsourceid => l_ib_source_id,
1562 LineProcessCaller => 'ORDER',
1563 CurrentLineSequence => null,
1564 errcode => ErrCode,
1565 errstr => ErrStr);
1566
1567
1568 if ErrCode <> 0 then
1569 x_progress := ErrStr;
1570 raise e_AddAttributeException;
1571 end if;
1572
1573 END LOOP;
1574
1575 /* Now start the workflow process */
1576
1577 FOR i in 1..l_Counter LOOP
1578 wf_engine.StartProcess( 'XDPPROV', t_ChildKeys(i));
1579 END LOOP;
1580
1581 return l_result;
1582
1583 exception
1584 when e_AddAttributeException then
1585
1586 xdpcore.context('XDPCORE_LINE', 'LaunchAllIndLinesForOrder', itemtype, itemkey, null, x_Progress);
1587 wf_core.context('XDPCORE_LINE', 'LaunchAllIndLinesForOrder', itemtype, itemkey, null, x_Progress);
1588 raise;
1589
1590 when e_NoLinesFoundException then
1591
1592 xdpcore.context('XDPCORE_LINE', 'LaunchAllIndLinesForOrder', itemtype, itemkey, null, x_Progress);
1593 wf_core.context('XDPCORE_LINE', 'LaunchAllIndLinesForOrder', itemtype, itemkey, null, x_Progress);
1594 raise;
1595
1596 when others then
1597
1598 x_Progress := 'XDPCORE_LINE.LaunchAllIndLinesForOrder. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1599 wf_core.context('XDPCORE_LINE', 'LaunchAllIndLinesForOrder', itemtype, itemkey, null, x_Progress);
1600 raise;
1601 end LaunchAllIndLinesForOrder;
1602
1603
1604 Function LaunchAllIndLinesForBundle (itemtype in varchar2,
1605 itemkey in varchar2) return varchar2
1606 is
1607 l_BundleID number;
1608 l_OrderID number;
1609 l_Counter number := 0;
1610 l_LineItemID number;
1611 l_Priority number;
1612 l_tempKey varchar2(240);
1613 l_IsPackageFlag varchar2(10);
1614 l_ib_source varchar2(20) ;
1615 l_ib_source_id number ;
1616
1617 l_result varchar2(1):= 'N';
1618
1619 e_NoLinesFoundException exception;
1620 e_AddAttributeException exception;
1621
1622 cursor c_GetIndLines(OrderID number, BundleID number) is
1623 select LINE_ITEM_ID, IS_PACKAGE_FLAG, PRIORITY ,IB_SOURCE, NVL(IB_SOURCE_ID,-999) IB_SOURCE_ID
1624 from XDP_ORDER_LINE_ITEMS
1625 where ORDER_ID = OrderID
1626 and IS_VIRTUAL_LINE_FLAG = 'N'
1627 and STATUS_CODE = 'READY'
1628 and LINE_SEQUENCE = 0
1629 and BUNDLE_ID = BundleID;
1630
1631 cursor c_GetIndLinesNullBundle(OrderID number) is
1632 select LINE_ITEM_ID, IS_PACKAGE_FLAG, PRIORITY ,IB_SOURCE, NVL(IB_SOURCE_ID,-999) IB_SOURCE_ID
1633 from XDP_ORDER_LINE_ITEMS
1634 where ORDER_ID = OrderID
1635 and IS_VIRTUAL_LINE_FLAG = 'N'
1636 and STATUS_CODE = 'READY'
1637 and LINE_SEQUENCE = 0
1638 and ((BUNDLE_ID is null) or (BUNDLE_ID = 0 ) );
1639
1640 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1641 t_ChildKeys t_ChildKeyTable;
1642
1643 x_Progress VARCHAR2(2000);
1644
1645 ErrCode number;
1646 ErrStr varchar2(1996);
1647
1648 begin
1649
1650
1651 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndLinesForBundle.itemtype,
1652 itemkey => LaunchAllIndLinesForBundle.itemkey,
1653 aname => 'ORDER_ID');
1654
1655 l_BundleID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndLinesForBundle.itemtype,
1656 itemkey => LaunchAllIndLinesForBundle.itemkey,
1657 aname => 'BUNDLE_ID');
1658
1659
1660 /* Get all the independent lines */
1661
1662 if l_BundleID is null or l_BundleID = -1 or l_BundleID = 0 then
1663
1664 FOR lv_LineRec in c_GetIndLinesNullBundle( l_OrderID ) LOOP
1665 l_result := 'Y';
1666 l_Counter := l_Counter + 1;
1667 l_LineItemID := lv_LineRec.LINE_ITEM_ID;
1668 l_IsPackageFlag := lv_LineRec.IS_PACKAGE_FLAG;
1669 l_Priority := lv_LineRec.PRIORITY;
1670 l_ib_source := lv_LineRec.IB_SOURCE;
1671 l_ib_source_id := lv_LineRec.IB_SOURCE_ID;
1672
1673 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1674 l_tempKey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1675
1676 t_ChildKeys(l_Counter) := l_tempKey;
1677
1678 -- Create Process and Bulk Set Item Attribute
1679 XDPCORE.CreateNAddAttrNParentLabel(itemtype => 'XDPPROV',
1680 itemkey => t_ChildKeys(l_Counter),
1681 processname => 'LINE_PROCESSING_PROCESS',
1682 parentitemtype => LaunchAllIndLinesForBundle.itemtype,
1683 parentitemkey => LaunchAllIndLinesForBundle.itemkey,
1684 waitflowlabel => 'WAITFORFLOW-LINE-IND',
1685 OrderID => l_OrderID,
1686 LineitemID => l_LineItemID,
1687 WIInstanceID => null,
1688 FAInstanceID => null);
1689
1690 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1691 itemkey => t_ChildKeys(l_Counter),
1692 aname => 'MASTER_TO_CONTINUE',
1693 avalue => 'WAITFORFLOW-LINE-IND');
1694
1695 AddLineAttributes(itemtype => 'XDPPROV',
1696 itemkey => t_ChildKeys(l_Counter),
1697 Module => 'XDPCORE_LINE.LaunchAllIndLinesForBundle',
1698 IsPackageFlag => l_IsPackageFlag,
1699 ibsource => l_ib_source,
1700 ibsourceid => l_ib_source_id,
1701 LineProcessCaller => 'BUNDLE',
1702 CurrentLineSequence => null,
1703 errcode => ErrCode,
1704 errstr => ErrStr);
1705
1706
1707 if ErrCode <> 0 then
1708 x_progress := ErrStr;
1709 raise e_AddAttributeException;
1710 end if;
1711 END LOOP;
1712
1713 elsif l_BundleID > 0 then
1714
1715 FOR lv_LineRec in c_GetIndLines( l_OrderID, l_BundleID ) LOOP
1716 l_result := 'Y';
1717 l_Counter := l_Counter + 1;
1718 l_LineItemID := lv_LineRec.LINE_ITEM_ID;
1719 l_IsPackageFlag := lv_LineRec.IS_PACKAGE_FLAG;
1720 l_Priority := lv_LineRec.PRIORITY;
1721 l_ib_source := lv_LineRec.IB_SOURCE;
1722 l_ib_source_id := lv_LineRec.IB_SOURCE_ID;
1723
1724 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1725 l_tempKey := to_char(l_OrderID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1726
1727 t_ChildKeys(l_Counter) := l_tempKey;
1728
1729 -- Create Process and Bulk Set Item Attribute
1730 XDPCORE.CreateNAddAttrNParentLabel(itemtype => 'XDPPROV',
1731 itemkey => t_ChildKeys(l_Counter),
1732 processname => 'LINE_PROCESSING_PROCESS',
1733 parentitemtype => LaunchAllIndLinesForBundle.itemtype,
1734 parentitemkey => LaunchAllIndLinesForBundle.itemkey,
1735 waitflowlabel => 'WAITFORFLOW-LINE-IND',
1736 OrderID => l_OrderID,
1737 LineitemID => l_LineItemID,
1738 WIInstanceID => null,
1739 FAInstanceID => null);
1740
1741 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1742 itemkey => t_ChildKeys(l_Counter),
1743 aname => 'MASTER_TO_CONTINUE',
1744 avalue => 'WAITFORFLOW-LINE-IND');
1745
1746
1747 AddLineAttributes(itemtype => 'XDPPROV',
1748 itemkey => t_ChildKeys(l_Counter),
1749 Module => 'XDPCORE_LINE.LaunchAllIndLinesForBundle',
1750 IsPackageFlag => l_IsPackageFlag,
1751 ibsource => l_ib_source,
1752 ibsourceid => l_ib_source_id,
1753 LineProcessCaller => 'BUNDLE',
1754 CurrentLineSequence => null,
1755 errcode => ErrCode,
1756 errstr => ErrStr);
1757
1758
1759 if ErrCode <> 0 then
1760 x_progress := ErrStr;
1761 raise e_AddAttributeException;
1762 end if;
1763
1764 END LOOP;
1765
1766 end if;
1767 /* Now start the workflow process */
1768
1769 FOR i in 1..l_Counter LOOP
1770 wf_engine.StartProcess( 'XDPPROV', t_ChildKeys(i));
1771 END LOOP;
1772
1773 return L_result;
1774
1775 exception
1776 when e_AddAttributeException then
1777
1778 xdpcore.context('XDPCORE_LINE', 'LaunchAllIndLinesForBundle', itemtype, itemkey, null, x_Progress);
1779 wf_core.context('XDPCORE_LINE', 'LaunchAllIndLinesForBundle', itemtype, itemkey, null, x_Progress);
1780 raise;
1781
1782 when e_NoLinesFoundException then
1783
1784 xdpcore.context('XDPCORE_LINE', 'LaunchAllIndLinesForBundle', itemtype, itemkey, null, x_Progress);
1785 wf_core.context('XDPCORE_LINE', 'LaunchAllIndLinesForBundle', itemtype, itemkey, null, x_Progress);
1786 raise;
1787
1788 when others then
1789
1790 x_Progress := 'XDPCORE_LINE.LaunchAllIndLinesForBundle. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1791 wf_core.context('XDPCORE_LINE', 'LaunchAllIndLinesForBundle', itemtype, itemkey, null, x_Progress);
1792 raise;
1793 end LaunchAllIndLinesForBundle;
1794
1795
1796
1797 Function IsLineaPackage (itemtype in varchar2,
1798 itemkey in varchar2) return varchar2
1799 is
1800 l_PackageFlag varchar2(5);
1801 x_Progress VARCHAR2(2000);
1802 l_ib_source_id number;
1803 l_ib_source varchar2(20) ;
1804 l_resultout varchar2(5) ;
1805
1806 begin
1807
1808 l_PackageFlag := wf_engine.GetItemAttrText(itemtype => IsLineaPackage.itemtype,
1809 itemkey => IsLineaPackage.itemkey,
1810 aname => 'IS_PACKAGE_FLAG');
1811
1812 l_ib_source := wf_engine.GetItemAttrText(itemtype => IsLineaPackage.itemtype,
1813 itemkey => IsLineaPackage.itemkey,
1814 aname => 'IB_SOURCE');
1815
1816 l_ib_source_id := wf_engine.GetItemAttrNumber(itemtype => IsLineaPackage.itemtype,
1817 itemkey => IsLineaPackage.itemkey,
1818 aname => 'IB_SOURCE_ID');
1819
1820 IF ((l_PackageFlag = 'Y') OR
1821 (l_ib_source IN('CSI','TXN') AND l_ib_source_id = -999 )
1822 ) THEN
1823 l_resultout := 'Y' ;
1824 ELSIF ((l_PackageFlag = 'N' AND l_ib_source ='NONE') OR
1825 (l_ib_source IN('CSI','TXN') AND l_ib_source_id <> -999 )
1826 ) THEN
1827 l_resultout := 'N' ;
1828 END IF;
1829
1830 return l_resultout;
1831
1832 EXCEPTION
1833 WHEN others THEN
1834 x_Progress := 'XDPCORE_LINE.IsLineaPackage. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1835 wf_core.context('XDPCORE_LINE', 'IsLineaPackage', itemtype, itemkey, null, x_Progress);
1836 raise;
1837 end IsLineaPackage;
1838
1839
1840
1841 Procedure LaunchServiceProcess (itemtype in varchar2,
1842 itemkey in varchar2)
1843 is
1844 l_OrderID number;
1845 l_LineItemID number;
1846
1847 l_tempKey varchar2(240);
1848
1849 x_Progress VARCHAR2(2000);
1850
1851 begin
1852
1853 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchServiceProcess.itemtype,
1854 itemkey => LaunchServiceProcess.itemkey,
1855 aname => 'ORDER_ID');
1856
1857 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchServiceProcess.itemtype,
1858 itemkey => LaunchServiceProcess.itemkey,
1859 aname => 'LINE_ITEM_ID');
1860
1861
1862 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1863 l_tempKey := to_char(l_OrderID) || '-SVC-' || to_char(l_LineItemID) || l_tempKey;
1864
1865 -- Create Process and Bulk Set Item Attribute
1866 XDPCORE.CreateNAddAttrNParentLabel(itemtype => 'XDPPROV',
1867 itemkey => l_tempKey,
1868 processname => 'SERVICE_PROCESS',
1869 parentitemtype => LaunchServiceProcess.itemtype,
1870 parentitemkey => LaunchServiceProcess.itemkey,
1871 WaitflowLabel => 'WAITFORFLOW-SERVICE',
1872 OrderID => l_OrderID,
1873 LineitemID => l_LineItemID,
1874 WIInstanceID => null,
1875 FAInstanceID => null);
1876
1877 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1878 itemkey => l_tempKey,
1879 aname => 'MASTER_TO_CONTINUE',
1880 avalue => 'WAITFORFLOW-SERVICE');
1881
1882
1883 wf_engine.StartProcess(itemtype => 'XDPPROV',
1884 itemkey => l_tempKey);
1885
1886
1887 exception
1888 when others then
1889 x_Progress := 'XDPCORE_LINE.LaunchServiceProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1890 wf_core.context('XDPCORE_LINE', 'LaunchServiceProcess', itemtype, itemkey, null, x_Progress);
1891 raise;
1892 end LaunchServiceProcess;
1893
1894
1895
1896 Procedure InitializeLine (itemtype in varchar2,
1897 itemkey in varchar2) IS
1898
1899 l_OrderID number;
1900 l_LineItemID number;
1901
1902 x_Progress VARCHAR2(2000);
1903
1904 begin
1905
1906 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => InitializeLine.itemtype,
1907 itemkey => InitializeLine.itemkey,
1908 aname => 'ORDER_ID');
1909
1910 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => InitializeLine.itemtype,
1911 itemkey => InitializeLine.itemkey,
1912 aname => 'LINE_ITEM_ID');
1913
1914
1915 if l_OrderID is not null and l_LineItemID is not null then
1916
1917 UPDATDE_ORDER_LINE(p_line_item_id => l_LineItemID ,
1918 p_status_code => 'IN PROGRESS',
1919 p_itemtype => InitializeLine.itemtype,
1920 p_itemkey => InitializeLine.itemkey );
1921
1922 end if;
1923 exception
1924 when others then
1925 x_Progress := 'XDPCORE_LINE.InitializeLine. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1926 wf_core.context('XDPCORE_LINE', 'InitializeLine', itemtype, itemkey, null, x_Progress);
1927 raise;
1928 end InitializeLine;
1929
1930 PROCEDURE UPDATDE_ORDER_LINE(p_line_item_id IN NUMBER ,
1931 p_status_code IN VARCHAR2,
1932 p_itemtype IN VARCHAR2,
1933 p_itemkey IN VARCHAR2) IS
1934 PRAGMA AUTONOMOUS_TRANSACTION ;
1935
1936 x_Progress VARCHAR2(2000);
1937
1938 BEGIN
1939
1940 UPDATE xdp_order_line_items
1941 SET status_code = p_status_code ,
1942 wf_item_type = p_itemtype,
1943 wf_item_key = p_itemkey,
1944 last_update_date = sysdate ,
1945 last_updated_by = fnd_global.user_id ,
1946 last_update_login = fnd_global.login_id
1947 WHERE line_item_id = p_line_item_id ;
1948
1949 COMMIT;
1950
1951 EXCEPTION
1952 WHEN others THEN
1953 x_Progress := 'XDPCORE_LINE.UPDATDE_ORDER_LINE. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1954 wf_core.context('XDPCORE_LINE', 'UPDATDE_ORDER_LINE', p_itemtype, p_itemkey, null, x_Progress);
1955 ROLLBACK;
1956 raise;
1957 END UPDATDE_ORDER_LINE ;
1958
1959
1960 Procedure AddLineAttributes(itemtype in varchar2,
1961 itemkey in varchar2,
1962 Module in varchar2,
1963 IsPackageFlag in varchar2,
1964 ibsource in varchar2,
1965 ibsourceid in number,
1966 LineProcessCaller in varchar2,
1967 CurrentLineSequence in number,
1968 ErrCode OUT NOCOPY number,
1969 ErrStr OUT NOCOPY varchar2)
1970 is
1971
1972 begin
1973
1974 ErrCode := 0;
1975 ErrStr := NULL;
1976
1977 if IsPackageFlag is not null then
1978 XDPCORE.CheckNAddItemAttrText (itemtype => AddLineAttributes.itemtype,
1979 itemkey => AddLineAttributes.itemkey,
1980 AttrName => 'IS_PACKAGE_FLAG',
1981 AttrValue => IsPackageFlag,
1982 ErrCode => ErrCode,
1983 ErrStr => ErrStr);
1984
1985 if ErrCode <> 0 then
1986 ErrStr := 'In ' || Module || '. Error when adding Item Attribute ' ||
1987 'IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
1988 return;
1989 end if;
1990 end if;
1991
1992 if ibsource is not null then
1993 XDPCORE.CheckNAddItemAttrText (itemtype => AddLineAttributes.itemtype,
1994 itemkey => AddLineAttributes.itemkey,
1995 AttrName => 'IB_SOURCE',
1996 AttrValue => ibsource,
1997 ErrCode => ErrCode,
1998 ErrStr => ErrStr);
1999
2000 if ErrCode <> 0 then
2001 ErrStr := 'In ' || Module || '. Error when adding Item Attribute ' ||
2002 'IB_SOURCE. Error: ' || substr(ErrStr,1,1500);
2003 return;
2004 end if;
2005 end if;
2006 if ibsourceid is not null then
2007 XDPCORE.CheckNAddItemAttrNumber (itemtype => AddLineAttributes.itemtype,
2008 itemkey => AddLineAttributes.itemkey,
2009 AttrName => 'IB_SOURCE_ID',
2010 AttrValue => ibsourceid,
2011 ErrCode => ErrCode,
2012 ErrStr => ErrStr);
2013
2014 if ErrCode <> 0 then
2015 ErrStr := 'In ' || Module || '. Error when adding Item Attribute ' ||
2016 'IB_SOURCE_ID. Error: ' || substr(ErrStr,1,1500);
2017 return;
2018 end if;
2019 end if;
2020
2021 if LineProcessCaller is not null then
2022 XDPCORE.CheckNAddItemAttrText (itemtype => AddLineAttributes.itemtype,
2023 itemkey => AddLineAttributes.itemkey,
2024 AttrName => 'LINE_PROCESSING_CALLER',
2025 AttrValue => LineProcessCaller,
2026 ErrCode => ErrCode,
2027 ErrStr => ErrStr);
2028
2029 if ErrCode <> 0 then
2030 ErrStr := 'In ' || Module || '. Error when adding Item Attribute ' ||
2031 'LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
2032 return;
2033 end if;
2034 end if;
2035
2036
2037 if CurrentLineSequence is not null then
2038 XDPCORE.CheckNAddItemAttrNumber (itemtype => AddLineAttributes.itemtype,
2039 itemkey => AddLineAttributes.itemkey,
2040 AttrName => 'CURRENT_LINE_SEQUENCE',
2041 AttrValue => CurrentLineSequence,
2042 ErrCode => ErrCode,
2043 ErrStr => ErrStr);
2044
2045 if ErrCode <> 0 then
2046 ErrStr := 'In ' || Module || '. Error when adding Item Attribute ' ||
2047 'CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
2048 return;
2049 end if;
2050 end if;
2051
2052 exception
2053 when others then
2054 errcode := SQLCODE;
2055 errstr := 'Unhandled Exception when adding Line Attributes: ' || substr(SQLERRM,1,500);
2056 end AddLineAttributes;
2057
2058
2059 Procedure UpdateInstallBase(itemtype in varchar2,
2060 itemkey in varchar2)
2061 is
2062 l_OrderID number;
2063 l_LineItemID number;
2064 l_errCode number := 0;
2065 l_errStr varchar2(1996):= NULL;
2066 l_error_description varchar2(2000);
2067
2068
2069 begin
2070
2071 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => UpdateInstallBase.itemtype,
2072 itemkey => UpdateInstallBase.itemkey,
2073 aname => 'ORDER_ID');
2074
2075 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => UpdateInstallBase.itemtype,
2076 itemkey => UpdateInstallBase.itemkey,
2077 aname => 'LINE_ITEM_ID');
2078
2079 if l_OrderID is not null and
2080 l_LineItemID is not null then
2081
2082 XDP_INSTALL_BASE.UPDATE_IB(p_order_id => l_OrderID,
2083 p_line_id => l_LineItemID,
2084 p_error_code => l_errcode,
2085 p_error_description => l_error_description);
2086 end if;
2087
2088 if l_errCode <> 0 then
2089 l_errStr := ' Error when adding Item Attribute ' ||
2090 'UpdateInstallBase. Error: ' || substr(l_error_description,1,1500);
2091 return;
2092 end if;
2093
2094 END UpdateInstallBase;
2095
2096 Procedure InitializeDepLineProcess(itemtype in varchar2,
2097 itemkey in varchar2 ) IS
2098 ErrCode number;
2099 ErrStr varchar2(1996);
2100
2101 e_AddAttributeException exception;
2102 x_Progress VARCHAR2(2000);
2103
2104 begin
2105
2106 XDPCORE.CheckNAddItemAttrNumber (itemtype => InitializeDepLineProcess.itemtype,
2107 itemkey => InitializeDepLineProcess.itemkey,
2108 AttrName => 'CURRENT_LINE_SEQUENCE',
2109 AttrValue => 0,
2110 ErrCode => ErrCode,
2111 ErrStr => ErrStr);
2112
2113 if ErrCode <> 0 then
2114 x_progress := 'In XDPCORE_LINE.InitializeDepLineProcess. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error:' || substr(ErrStr,1,1500);
2115 raise e_AddAttributeException;
2116 end if;
2117
2118
2119 EXCEPTION
2120 when others then
2121 x_Progress := 'XDPCORE_LINE.InitializeDepLineProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
2122 xdpcore.context('XDPCORE_LINE', 'InitializeDepLineProcess', itemtype, itemkey,x_Progress);
2123 wf_core.context('XDPCORE_LINE', 'InitializeDepLineProcess', itemtype, itemkey, null, x_Progress);
2124 raise;
2125
2126
2127 END InitializeDepLineProcess;
2128
2129
2130 Function ResolveIndDepLines (itemtype in varchar2,
2131 itemkey in varchar2) return varchar2
2132 is
2133 l_OrderID number;
2134
2135 l_IndFound number := 0;
2136 l_DepFound number := 0;
2137
2138 x_Progress VARCHAR2(2000);
2139
2140 cursor c_GetIndLines(OrderID number) is
2141 select 'Y'
2142 from XDP_ORDER_LINE_ITEMS
2143 where ORDER_ID = OrderID
2144 and IS_VIRTUAL_LINE_FLAG = 'N'
2145 and STATUS_CODE = 'READY'
2146 and LINE_SEQUENCE = 0;
2147
2148 cursor c_GetDepLines(OrderID number) is
2149 select 'Y'
2150 from XDP_ORDER_LINE_ITEMS
2151 where ORDER_ID = OrderID
2152 and IS_VIRTUAL_LINE_FLAG = 'N'
2153 and STATUS_CODE = 'READY'
2154 and LINE_SEQUENCE > 0;
2155
2156 begin
2157
2158 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepLines.itemtype,
2159 itemkey => ResolveIndDepLines.itemkey,
2160 aname => 'ORDER_ID');
2161
2162 FOR lv_LineRec in c_GetIndLines( l_OrderID ) LOOP
2163 l_IndFound := 1;
2164 EXIT;
2165 END LOOP;
2166
2167 FOR lv_LineRec in c_GetDepLines( l_OrderID ) LOOP
2168 l_DepFound := 1;
2169 EXIT;
2170 END LOOP;
2171
2172 if( l_IndFound = 1 AND l_DepFound = 1 ) THEN
2173 RETURN 'BOTH';
2174 elsif( l_IndFound = 1) THEN
2175 RETURN 'INDEPENDENT';
2176 elsif( l_DepFound = 1 ) THEN
2177 RETURN 'DEPENDENT';
2178 end if;
2179
2180
2181 exception
2182 when others then
2183
2184 x_Progress := 'XDPCORE_LINE.ResolveIndDepLines. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
2185 wf_core.context('XDPCORE_LINE', 'ResolveIndDepLines', itemtype, itemkey, null, x_Progress);
2186 raise;
2187 end ResolveIndDepLines;
2188
2189
2190 Function IsSerPartPackage (itemtype in varchar2,
2191 itemkey in varchar2) return varchar2
2192 is
2193 l_LineItemID NUMBER;
2194 l_flag VARCHAR2(1);
2195 x_Progress VARCHAR2(2000);
2196
2197 begin
2198
2199 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => IsSerPartPackage.itemtype,
2200 itemkey => IsSerPartPackage.itemkey,
2201 aname => 'LINE_ITEM_ID');
2202 SELECT is_virtual_line_flag INTO l_flag
2203 FROM xdp_order_line_items
2204 WHERE line_item_id = l_LineItemID;
2205
2206 return l_flag;
2207 exception
2208 when others then
2209
2210 x_Progress := 'XDPCORE_LINE.IsSerPartPackage. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
2211 wf_core.context('XDPCORE_LINE', 'IsSerPartPackage', itemtype, itemkey, null, x_Progress);
2212 raise;
2213 end IsSerPartPackage;
2214
2215
2216 -- **************** PublishXDPLineDone *********************
2217
2218 PROCEDURE PublishXDPLineDone(itemtype IN VARCHAR2,
2219 itemkey IN VARCHAR2,
2220 actid IN NUMBER,
2221 resultout IN VARCHAR2) IS
2222
2223 l_line_number NUMBER;
2224 l_line_item_id NUMBER;
2225 l_message_id NUMBER ;
2226 l_error_code NUMBER ;
2227 l_error_message VARCHAR2(4000);
2228 x_progress VARCHAR2(4000);
2229 l_order_id NUMBER;
2230 e_publish_exception EXCEPTION ;
2231
2232 BEGIN
2233
2234 l_line_item_id := WF_ENGINE.GetItemAttrNumber
2235 (itemtype => PublishXDPLineDone.itemtype ,
2236 itemkey => PublishXDPLineDone.itemkey ,
2237 aname => 'LINE_ITEM_ID' );
2238
2239 l_order_id := WF_ENGINE.GetItemAttrNumber
2240 (itemtype => PublishXDPLineDone.itemtype ,
2241 itemkey => PublishXDPLineDone.itemkey ,
2242 aname => 'ORDER_ID' );
2243
2244 SELECT line_number
2245 INTO l_line_number
2246 FROM xdp_order_line_items
2247 WHERE line_item_id = l_line_item_id ;
2248
2249
2250 XNP_XDP_LINE_DONE_U.PUBLISH
2251 (XNP$LINE_ITEM_ID => l_line_item_id ,
2252 P_REFERENCE_ID => l_line_number ,
2253 P_ORDER_ID => l_order_id ,
2254 X_MESSAGE_ID => l_message_id ,
2255 X_ERROR_CODE => l_error_code ,
2256 X_ERROR_MESSAGE => l_error_message );
2257
2258 IF l_error_code <> 0 THEN
2259 x_progress := 'In XDPCORE_LINE.PublishXDPLineDone. Error while publishing XDP_LINE_DONE . Error :- ' ||l_error_message ;
2260 RAISE e_publish_exception ;
2261 END IF ;
2262
2263 EXCEPTION
2264 WHEN e_publish_exception THEN
2265 wf_core.context('XDPCORE_LINE', 'PublishXDPLineDone', itemtype, itemkey, null, x_Progress);
2266 raise;
2267 WHEN others THEN
2268 wf_core.context('XDPCORE_LINE', 'PublishXDPLineDone',itemtype,itemkey,actid,null);
2269 raise;
2270 END PublishXDPLineDone ;
2271
2272 End XDPCORE_LINE;