[Home] [Help]
PACKAGE BODY: APPS.XDPCORE_BUNDLE
Source
1 PACKAGE BODY XDPCORE_BUNDLE AS
2 /* $Header: XDPCORBB.pls 120.1 2005/06/15 22:11:52 appldev $ */
3
4 /****
5 All Private Procedures for the Package
6 ****/
7
8 Function HandleOtherWFFuncmode (funcmode in varchar2) return varchar2;
9
10 Procedure InitializeBundle(itemtype in varchar2,
11 itemkey in varchar2);
12
13 Function AreAllBundlesDone (itemtype in varchar2,
14 itemkey in varchar2) return varchar2;
15
16 Function ResolveIndDepBundles (itemtype in varchar2,
17 itemkey in varchar2) return varchar2;
18
19 Procedure LaunchBundleProcesses(itemtype in varchar2,
20 itemkey in varchar2);
21
22 Procedure LaunchBundleProcessSeq(itemtype in varchar2,
23 itemkey in varchar2);
24
25 Procedure LaunchLineForBundleProcess(itemtype in varchar2,
26 itemkey in varchar2);
27
28 Function LaunchAllIndBundles(itemtype in varchar2,
29 itemkey in varchar2) return varchar2;
30
31 Procedure InitializeDepBundleProcess(itemtype in varchar2,
32 itemkey in varchar2);
33
34
35 Procedure LaunchBundleProcess(itemtype in varchar2,
36 itemkey in varchar2);
37
38
39 Function ResolveIndDepLinesForBun (itemtype in varchar2,
40 itemkey in varchar2) return varchar2;
41
42 type RowidArrayType is table of rowid index by binary_integer;
43
44
45 /***********************************************
46 * END of Private Procedures/Function Definitions
47 ************************************************/
48
49 -- LAUNCH_BUNDLE_PROCESSES
50 -- Resultout
51 -- Activity Performed - Activity was completed without any errors
52 --
53 -- Your Description here:
54
55 Procedure LAUNCH_BUNDLE_PROCESSES (itemtype in varchar2,
56 itemkey in varchar2,
57 actid in number,
58 funcmode in varchar2,
59 resultout OUT NOCOPY varchar2 ) IS
60
61 x_Progress VARCHAR2(2000);
62
63
64 BEGIN
65 -- RUN mode - normal process execution
66 --
67 IF (funcmode = 'RUN') THEN
68 LaunchBundleProcesses(itemtype, itemkey);
69 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
70 return;
71 ELSE
72 resultout := HandleOtherWFFuncmode(funcmode);
73 return;
74 END IF;
75
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 wf_core.context('XDPCORE_BUNDLE', 'LAUNCH_BUNDLE_PROCESSES', itemtype, itemkey, to_char(actid), funcmode);
80 raise;
81 END LAUNCH_BUNDLE_PROCESSES;
82
83
84 -- LAUNCH_BUNDLE_PROCESS_SEQ
85 -- Resultout
86 -- Activity Performed - Activity was completed without any errors
87 --
88 -- Your Description here:
89
90 Procedure LAUNCH_BUNDLE_PROCESS_SEQ (itemtype in varchar2,
91 itemkey in varchar2,
92 actid in number,
93 funcmode in varchar2,
94 resultout OUT NOCOPY varchar2 ) IS
95
96
97 x_Progress VARCHAR2(2000);
98
99 BEGIN
100 -- RUN mode - normal process execution
101 --
102 IF (funcmode = 'RUN') THEN
103 LaunchBundleProcessSeq(itemtype, itemkey);
104 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
105 return;
106 ELSE
107 resultout := HandleOtherWFFuncmode(funcmode);
108 return;
109 END IF;
110
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 wf_core.context('XDPCORE_BUNDLE', 'LAUNCH_BUNDLE_PROCESS_SEQ', itemtype, itemkey, to_char(actid), funcmode);
115 raise;
116 END LAUNCH_BUNDLE_PROCESS_SEQ;
117
118
119
120
121
122
123 -- LAUNCH_LINE_FOR_BUNDLE_PROCESS
124 -- Resultout
125 -- Activity Performed - Activity was completed without any errors
126 --
127 -- Your Description here:
128
129 Procedure LAUNCH_LINE_FOR_BUNDLE_PROCESS (itemtype in varchar2,
130 itemkey in varchar2,
131 actid in number,
132 funcmode in varchar2,
133 resultout OUT NOCOPY varchar2 ) IS
134
135
136 x_Progress VARCHAR2(2000);
137
138 BEGIN
139 -- RUN mode - normal process execution
140 --
141 IF (funcmode = 'RUN') THEN
142 LaunchLineForBundleProcess(itemtype, itemkey);
143 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
144 return;
145 ELSE
146 resultout := HandleOtherWFFuncmode(funcmode);
147 return;
148 END IF;
149
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 wf_core.context('XDPCORE_BUNDLE', 'LAUNCH_LINE_FOR_BUNDLE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
154 raise;
155 END LAUNCH_LINE_FOR_BUNDLE_PROCESS;
156
157
158
159 -- ARE_ALL_BUNDLES_DONE
160 -- Resultout
161 -- Activity Performed - Activity was completed without any errors
162 --
163 -- Your Description here:
164
165 Procedure ARE_ALL_BUNDLES_DONE (itemtype in varchar2,
166 itemkey in varchar2,
167 actid in number,
168 funcmode in varchar2,
169 resultout OUT NOCOPY varchar2 ) IS
170 l_result varchar2(10);
171 x_Progress VARCHAR2(2000);
172
173 BEGIN
174 -- RUN mode - normal process execution
175 --
176 IF (funcmode = 'RUN') THEN
177 l_result := AreAllBundlesDone(itemtype, itemkey);
178 resultout := 'COMPLETE:' || l_result;
179 return;
180 ELSE
181 resultout := HandleOtherWFFuncmode(funcmode);
182 return;
183 END IF;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 wf_core.context('XDPCORE_BUNDLE', 'ARE_ALL_BUNDLES_DONE', itemtype, itemkey, to_char(actid), funcmode);
188 raise;
189 END ARE_ALL_BUNDLES_DONE;
190
191
192 PROCEDURE UPDATE_BUNDLE_STATUS(p_bundle_id IN NUMBER,
193 p_order_id IN NUMBER,
194 p_status_code IN VARCHAR2,
195 p_itemtype IN VARCHAR2,
196 p_itemkey IN VARCHAR2) IS
197 PRAGMA AUTONOMOUS_TRANSACTION ;
198 x_Progress VARCHAR2(2000);
199
200 BEGIN
201 UPDATE xdp_order_bundles
202 SET status = p_status_code ,
203 last_update_date = sysdate ,
204 last_updated_by = fnd_global.user_id ,
205 last_update_login = fnd_global.login_id
206 WHERE order_id = p_order_id
207 AND bundle_id = p_bundle_id ;
208 COMMIT;
209
210 EXCEPTION
211 WHEN others THEN
212 x_Progress := 'XDPCORE_BUNDLE.UPDATE_BUNDLE_STATUS. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 2000);
213 wf_core.context('XDPCORE_BUNDLE', 'UPDATE_BUNDLE_STATUS', p_itemtype, p_itemkey, null, x_Progress);
214 rollback;
215 raise;
216 END UPDATE_BUNDLE_STATUS ;
217
218 -- INITIALIZE_BUNDLE
219 -- Resultout
220 -- Activity Performed - Activity was completed without any errors
221 --
222 -- Your Description here:
223
224 Procedure INITIALIZE_BUNDLE (itemtype in varchar2,
225 itemkey in varchar2,
226 actid in number,
227 funcmode in varchar2,
228 resultout OUT NOCOPY varchar2 ) IS
229
230
231 x_Progress VARCHAR2(2000);
232
233 BEGIN
234 -- RUN mode - normal process execution
235 --
236 IF (funcmode = 'RUN') THEN
237 InitializeBundle(itemtype, itemkey);
238 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
239 return;
240 ELSE
241 resultout := HandleOtherWFFuncmode(funcmode);
242 return;
243 END IF;
244
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 wf_core.context('XDPCORE_BUNDLE', 'INITIALIZE_BUNDLE', itemtype, itemkey, to_char(actid), funcmode);
249 raise;
250 END INITIALIZE_BUNDLE;
251
252 Procedure RESOLVE_IND_DEP_BUNDLES (itemtype in varchar2,
253 itemkey in varchar2,
254 actid in number,
255 funcmode in varchar2,
256 resultout OUT NOCOPY varchar2 ) IS
257
258
259 x_Progress VARCHAR2(2000);
260 l_result varchar2(30);
261
262 BEGIN
263 -- RUN mode - normal process execution
264 --
265 IF (funcmode = 'RUN') THEN
266 l_result := ResolveIndDepBundles(itemtype, itemkey);
267 resultout := 'COMPLETE:' || l_result;
268 return;
269 ELSE
270 resultout := HandleOtherWFFuncmode(funcmode);
271 return;
272 END IF;
273
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 wf_core.context('XDPCORE_BUNDLE', 'RESOLVE_IND_DEP_BUNDLES', itemtype, itemkey, to_char(actid), funcmode);
278 raise;
279 END RESOLVE_IND_DEP_BUNDLES;
280
281 Procedure LAUNCH_ALL_IND_BUNDLES (itemtype in varchar2,
282 itemkey in varchar2,
283 actid in number,
284 funcmode in varchar2,
285 resultout OUT NOCOPY varchar2) IS
286
287 x_Progress VARCHAR2(2000);
288 l_result varchar2(1) := 'N';
289
290 BEGIN
291 -- RUN mode - normal process execution
292 --
293 IF (funcmode = 'RUN') THEN
294 l_result := LaunchAllIndBundles(itemtype, itemkey);
295 resultout := 'COMPLETE:' || l_result;
296 return;
297 ELSE
298 resultout := HandleOtherWFFuncmode(funcmode);
299 return;
300 END IF;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 wf_core.context('XDPCORE_BUNDLE', 'LAUNCH_ALL_IND_BUNDLES', itemtype, itemkey, to_char(actid), funcmode);
305 raise;
306
307 END LAUNCH_ALL_IND_BUNDLES;
308
309
310 Procedure INITIALIZE_DEP_BUNDLE_PROCESS (itemtype in varchar2,
311 itemkey in varchar2,
312 actid in number,
313 funcmode in varchar2,
314 resultout OUT NOCOPY varchar2) IS
315
316 x_Progress VARCHAR2(2000);
317 l_result varchar2(1);
318
319 BEGIN
320 -- RUN mode - normal process execution
321 --
322 IF (funcmode = 'RUN') THEN
323 InitializeDepBundleProcess(itemtype, itemkey);
324 resultout := 'COMPLETE';
325 return;
326 ELSE
327 resultout := HandleOtherWFFuncmode(funcmode);
328 return;
329 END IF;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 wf_core.context('XDPCORE_BUNDLE', 'INITIALIZE_DEP_BUNDLE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
334 raise;
335 END INITIALIZE_DEP_BUNDLE_PROCESS;
336
337
338
339 Procedure LAUNCH_BUNDLE_PROCESS (itemtype in varchar2,
340 itemkey in varchar2,
341 actid in number,
342 funcmode in varchar2,
343 resultout OUT NOCOPY varchar2) IS
344
345 x_Progress VARCHAR2(2000);
346 l_result varchar2(1);
347
348 BEGIN
349 -- RUN mode - normal process execution
350 --
351 IF (funcmode = 'RUN') THEN
352 LaunchBundleProcess(itemtype, itemkey);
353 resultout := 'COMPLETE';
354 return;
355 ELSE
356 resultout := HandleOtherWFFuncmode(funcmode);
357 return;
358 END IF;
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 wf_core.context('XDPCORE_BUNDLE', 'LAUNCH_BUNDLE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
363 raise;
364 END LAUNCH_BUNDLE_PROCESS;
365
366
367 Procedure RESOLVE_IND_DEP_LINES_FOR_BUN (itemtype in varchar2,
368 itemkey in varchar2,
369 actid in number,
370 funcmode in varchar2,
371 resultout OUT NOCOPY varchar2) IS
372
373 x_Progress VARCHAR2(2000);
374 l_result varchar2(20);
375
376 BEGIN
377 -- RUN mode - normal process execution
378 --
379 IF (funcmode = 'RUN') THEN
380 l_result := ResolveIndDepLinesForBun(itemtype, itemkey);
381 resultout := 'COMPLETE:' || l_result;
382 return;
383 ELSE
384 resultout := HandleOtherWFFuncmode(funcmode);
385 return;
386 END IF;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 wf_core.context('XDPCORE_BUNDLE', 'RESOLVE_IND_DEP_LINES_FOR_BUN', itemtype, itemkey, to_char(actid), funcmode);
391 raise;
392 END RESOLVE_IND_DEP_LINES_FOR_BUN;
393
394
395
396
397 /****
398 All the Private Functions
399 ****/
400
401 Function HandleOtherWFFuncmode( funcmode in varchar2) return varchar2
402 is
403 resultout varchar2(30);
404 x_Progress VARCHAR2(2000);
405
406 begin
407
408 IF (funcmode = 'CANCEL') THEN
409 resultout := 'COMPLETE';
410 END IF;
411
412 IF (funcmode = 'RESPOND') THEN
413 resultout := 'COMPLETE';
414 END IF;
415
416 IF (funcmode = 'FORWARD') THEN
417 resultout := 'COMPLETE';
418 END IF;
419
420 IF (funcmode = 'TRANSFER') THEN
421 resultout := 'COMPLETE';
422 END IF;
423
424 IF (funcmode = 'TIMEOUT') THEN
425 resultout := 'COMPLETE';
426 END IF;
427
428 IF (funcmode = 'others') THEN
429 resultout := 'COMPLETE';
430 END IF;
431
432
433 return resultout;
434
435 end;
436
437
438
439 Function AreAllBundlesDone (itemtype in varchar2,
440 itemkey in varchar2) return varchar2
441 is
442 cursor c_BundleSeq( OrderID number, BundleSeq number) is
443 select BUNDLE_ID
444 from XDP_ORDER_LINE_ITEMS
445 where ORDER_ID = OrderID
446 and STATUS_CODE = 'READY'
447 and BUNDLE_SEQUENCE = (select min(BUNDLE_SEQUENCE)
448 from XDP_ORDER_LINE_ITEMS
449 where ORDER_ID = OrderID
450 and STATUS_CODE = 'READY'
451 and BUNDLE_SEQUENCE > BundleSeq);
452
453 l_OrderID number;
454 l_BundleID number;
455 l_PrevBundleSeq number;
456
457 e_NoBundlesFoundException exception;
458 x_Progress VARCHAR2(2000);
459
460 begin
461
462 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => AreAllBundlesDone.itemtype,
463 itemkey => AreAllBundlesDone.itemkey,
464 aname => 'ORDER_ID');
465
466 l_PrevBundleSeq := wf_engine.GetItemAttrNumber(itemtype => AreAllBundlesDone.itemtype,
467 itemkey => AreAllBundlesDone.itemkey,
468 aname => 'CURRENT_BUNDLE_SEQUENCE');
469
470
471 if c_BundleSeq%ISOPEN then
472 close c_BundleSeq;
473 end if;
474
475 open c_BundleSeq(l_OrderID, l_PrevBundleSeq);
476
477 Fetch c_BundleSeq into l_BundleID;
478
479 if c_BundleSeq%NOTFOUND then
480 /* No more Bundles to be done */
481 close c_BundleSeq;
482 return ('Y');
483 else
484 /* There are more Bundles to be done */
485 close c_BundleSeq;
486
487
488 return ('N');
489
490 end if;
491
492 if c_BundleSeq%ISOPEN then
493 close c_BundleSeq;
494 end if;
495
496
497 exception
498 when e_NoBundlesFoundException then
499 if c_BundleSeq%ISOPEN then
500 close c_BundleSeq;
501 end if;
502
503 wf_core.context('XDPCORE_BUNDLE', 'AreAllBundlesDone', itemtype, itemkey, null, x_Progress);
504 raise;
505
506 when others then
507 if c_BundleSeq%ISOPEN then
508 close c_BundleSeq;
509 end if;
510
511 x_Progress := 'XDPCORE_BUNDLE.AreAllBundlesDone. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 2000);
512 wf_core.context('XDPCORE_BUNDLE', 'AreAllBundlesDone', itemtype, itemkey, null, x_Progress);
513 raise;
514 end AreAllBundlesDone;
515
516
517
518
519 Procedure LaunchBundleProcesses (itemtype in varchar2,
520 itemkey in varchar2)
521 is
522 l_tempKey varchar2(240);
523 l_BundleID number;
524 l_OrderID number;
525 l_Counter number := 0;
526 i number;
527 l_CurrentBundleSequence number;
528
529 cursor c_GetIndBundle(OrderID number) is
530 select distinct(NVL(BUNDLE_ID, -1))
531 from XDP_ORDER_LINE_ITEMS
532 where ORDER_ID = OrderID
533 and STATUS_CODE = 'READY'
534 and (BUNDLE_SEQUENCE is null) OR (BUNDLE_SEQUENCE = 0);
535
536 cursor c_GetDepBundle(OrderID number) is
537 select BUNDLE_ID
538 from XDP_ORDER_LINE_ITEMS
539 where ORDER_ID = OrderID
540 and STATUS_CODE = 'READY'
541 and BUNDLE_ID IS NOT NULL
542 and BUNDLE_SEQUENCE > 0;
543
544 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
545 t_ChildKeys t_ChildKeyTable;
546
547 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
548 t_ChildTypes t_ChildTypeTable;
549
550
551 e_InvalidConfigException exception;
552 e_AddAttributeException exception;
553 x_Progress VARCHAR2(2000);
554
555 ErrCode number;
556 ErrStr varchar2(1996);
557
558 begin
559
560 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchBundleProcesses.itemtype,
561 itemkey => LaunchBundleProcesses.itemkey,
562 aname => 'ORDER_ID');
563
564 open c_GetIndBundle(l_OrderID);
565
566 LOOP
567 Fetch c_GetIndBundle into l_BundleID;
568
569 EXIT when c_GetIndBundle%NOTFOUND;
570
571 -- dbms_output.put_line('Got Ind bundle id: ' || l_BundleID);
572
573 l_Counter := l_Counter + 1;
574
575 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
576 l_tempKey := to_char(l_OrderID) || '-BUNDLE-IND-' || l_tempKey;
577
578 t_ChildTypes(l_Counter) := 'XDPPROV';
579 t_ChildKeys(l_Counter) := l_tempKey;
580
581
582 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
583 itemkey => t_ChildKeys(l_Counter),
584 process => 'INDEPENDENT_BUNDLE_PROCESS');
585
586 wf_engine.setItemParent(itemtype => t_ChildTypes(l_Counter),
587 itemkey => t_ChildKeys(l_Counter),
588 parent_itemtype => itemtype,
589 parent_itemkey => itemkey,
590 parent_context => 'WAITFORFLOW-BUNDLE-DEP');
591
592 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
593 itemkey => t_ChildKeys(l_Counter),
594 aname => 'ORDER_ID',
595 avalue => l_OrderID);
596
597 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
598 itemkey => t_ChildKeys(l_Counter),
599 AttrName => 'BUNDLE_ID',
600 AttrValue => l_BundleID,
601 ErrCode => ErrCode,
602 ErrStr => ErrStr);
603
604 if ErrCode <> 0 then
605 x_progress := 'In XDPCORE_WI.LaunchBundleProcesses. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
606 raise e_AddAttributeException;
607 end if;
608
609
610 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
611 itemkey => t_ChildKeys(l_Counter),
612 AttrName => 'INDEPENDENT_BUNDLE_FLAG',
613 AttrValue => 'TRUE',
614 ErrCode => ErrCode,
615 ErrStr => ErrStr);
616
617 if ErrCode <> 0 then
618 x_progress := 'In XDPCORE_WI.LaunchBundleProcesses. Error when adding Item Attribute INDEPENDENT_BUNDLE_FLAG. Error: ' || substr(ErrStr,1,1500);
619 raise e_AddAttributeException;
620 end if;
621
622 END LOOP;
623
624 close c_GetIndBundle;
625
626
627 /* Dependent Bundle Sequencing Processing */
628
629 if c_GetDepBundle%ISOPEN then
630 close c_GetDepBundle;
631 end if;
632
633 open c_GetDepBundle(l_OrderID);
634 Fetch c_GetDepBundle into l_BundleID;
635
636 -- dbms_output.put_line('Got Dep bundle id: ' || l_BundleID);
637
638 if c_GetDepBundle%NOTFOUND and l_Counter = 0 then
639 /* Some thing wrong */
640 close c_GetDepBundle;
641 x_Progress := 'XDPCORE_BUNDLE.LaunchBundleProcesses. No Bundles Detected for OrderID: ' || l_OrderID;
642 RAISE e_InvalidConfigException;
643 elsif c_GetDepBundle%FOUND then
644 l_Counter := l_Counter + 1;
645
646 t_ChildTypes(l_Counter) := 'XDPPROV';
647
648 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
649 l_tempKey := to_char(l_OrderID) || '-BUNDLE-' || to_char(l_BundleID) || '-' || l_tempKey;
650
651 t_ChildKeys(l_Counter) := l_tempKey;
652
653 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
654 itemkey => t_ChildKeys(l_Counter),
655 process => 'DEPENDENT_BUNDLE_PROCESS');
656
657 wf_engine.setItemParent(itemtype => t_ChildTypes(l_Counter),
658 itemkey => t_ChildKeys(l_Counter),
659 parent_itemtype => itemtype,
660 parent_itemkey => itemkey,
661 parent_context => null);
662
663 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
664 itemkey => t_ChildKeys(l_Counter),
665 aname => 'ORDER_ID',
666 avalue => l_OrderID);
667
668 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
669 itemkey => t_ChildKeys(l_Counter),
670 AttrName => 'BUNDLE_ID',
671 AttrValue => l_BundleID,
672 ErrCode => ErrCode,
673 ErrStr => ErrStr);
674
675 if ErrCode <> 0 then
676 x_progress := 'In XDPCORE_WI.LaunchBundleProcesses. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
677 raise e_AddAttributeException;
678 end if;
679
680 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
681 itemkey => t_ChildKeys(l_Counter),
682 AttrName => 'INDEPENDENT_BUNDLE_FLAG',
683 AttrValue => 'FALSE',
684 ErrCode => ErrCode,
685 ErrStr => ErrStr);
686
687 if ErrCode <> 0 then
688 x_progress := 'In XDPCORE_WI.LaunchBundleProcesses. Error when adding Item Attribute INDEPENDENT_BUNDLE_FLAG. Error: ' || substr(ErrStr,1,1500);
689 raise e_AddAttributeException;
690 end if;
691
692 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
693 itemkey => t_ChildKeys(l_Counter),
694 AttrName => 'CURRENT_BUNDLE_SEQUENCE',
695 AttrValue => 0,
696 ErrCode => ErrCode,
697 ErrStr => ErrStr);
698
699 if ErrCode <> 0 then
700 x_progress := 'In XDPCORE_WI.LaunchBundleProcesses. Error when adding Item Attribute CURRENT_BUNDLE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
701 raise e_AddAttributeException;
702 end if;
703
704
705 close c_GetDepBundle;
706 end if;
707
708 /* Launch all the Child Processes */
709 for i in 1..l_Counter loop
710
711 -- dbms_output.put_line('launching Bundles: ' || t_ChildKeys(i));
712 wf_engine.StartProcess(itemtype => t_ChildTypes(i),
713 itemkey => t_ChildKeys(i));
714 end loop;
715
716 exception
717 when e_AddAttributeException then
718 if c_GetDepBundle%ISOPEN then
719 close c_GetDepBundle;
720 end if;
721
722 if c_GetIndBundle%ISOPEN then
723 close c_GetIndBundle;
724 end if;
725
726 wf_core.context('XDPCORE_BUNDLE', 'LaunchBundleProcesses', itemtype, itemkey, null, x_Progress);
727 raise;
728
729 when e_InvalidConfigException then
730 if c_GetDepBundle%ISOPEN then
731 close c_GetDepBundle;
732 end if;
733
734 if c_GetIndBundle%ISOPEN then
735 close c_GetIndBundle;
736 end if;
737
738 wf_core.context('XDPCORE_BUNDLE', 'LaunchBundleProcesses', itemtype, itemkey, null, x_Progress);
739 raise;
740
741 when others then
742 if c_GetDepBundle%ISOPEN then
743 close c_GetDepBundle;
744 end if;
745
746 if c_GetIndBundle%ISOPEN then
747 close c_GetIndBundle;
748 end if;
749
750 x_Progress := 'XDPCORE_BUNDLE.LaunchBundleProcesses. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 2000);
751 wf_core.context('XDPCORE_BUNDLE', 'LaunchBundleProcesses', itemtype, itemkey, null, x_Progress);
752 raise;
753 end LaunchBundleProcesses;
754
755
756 Procedure LaunchLineForBundleProcess (itemtype in varchar2,
757 itemkey in varchar2)
758 is
759 l_OrderID number;
760 l_BundleID number;
761 l_Counter number := 0;
762 l_LineItemID number;
763
764 l_IndBundleFlag varchar2(10);
765 l_tempKey varchar2(240);
766 l_LineMaster varchar2(40);
767 l_PackageFlag varchar(1);
768
769 cursor c_GetIndLinesNullBundle(OrderID number) is
770 select LINE_ITEM_ID, IS_PACKAGE_FLAG
771 from XDP_ORDER_LINE_ITEMS
772 where ORDER_ID = OrderID
773 and IS_VIRTUAL_LINE_FLAG = 'N'
774 and STATUS_CODE = 'READY'
775 and BUNDLE_ID is null
776 and LINE_SEQUENCE = 0;
777
778 cursor c_GetDepLinesNullBundle(OrderID number) is
779 select LINE_ITEM_ID, IS_PACKAGE_FLAG
780 from XDP_ORDER_LINE_ITEMS
781 where ORDER_ID = OrderID
782 and IS_VIRTUAL_LINE_FLAG = 'N'
783 and STATUS_CODE = 'READY'
784 and BUNDLE_ID is null
785 and LINE_SEQUENCE > 0;
786
787
788 cursor c_GetIndLinesForBundle(OrderID number, BundleID number) is
789 select LINE_ITEM_ID, IS_PACKAGE_FLAG
790 from XDP_ORDER_LINE_ITEMS
791 where ORDER_ID = OrderID
792 and IS_VIRTUAL_LINE_FLAG = 'N'
793 and STATUS_CODE = 'READY'
794 and BUNDLE_ID = BundleID
795 and LINE_SEQUENCE = 0;
796
797 cursor c_GetDepLinesForBundle(OrderID number, BundleID number) is
798 select LINE_ITEM_ID, IS_PACKAGE_FLAG
799 from XDP_ORDER_LINE_ITEMS
800 where ORDER_ID = OrderID
801 and IS_VIRTUAL_LINE_FLAG = 'N'
802 and STATUS_CODE = 'READY'
803 and BUNDLE_ID = BundleID
804 and LINE_SEQUENCE > 0;
805
806
807 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
808 t_ChildKeys t_ChildKeyTable;
809
810 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
811 t_ChildTypes t_ChildTypeTable;
812
813 e_NoLinesFoundException exception;
814 e_AddAttributeException exception;
815
816 x_Progress VARCHAR2(2000);
817
818 ErrCode number;
819 ErrStr varchar2(1996);
820
821 begin
822 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchLineForBundleProcess.itemtype,
823 itemkey => LaunchLineForBundleProcess.itemkey,
824 aname => 'ORDER_ID');
825
826 l_BundleID := wf_engine.GetItemAttrNumber(itemtype => LaunchLineForBundleProcess.itemtype,
827 itemkey => LaunchLineForBundleProcess.itemkey,
828 aname => 'BUNDLE_ID');
829
830 l_IndBundleFlag := wf_engine.GetItemAttrText(itemtype => LaunchLineForBundleProcess.itemtype,
831 itemkey => LaunchLineForBundleProcess.itemkey,
832 aname => 'INDEPENDENT_BUNDLE_FLAG');
833
834
835
836 if l_IndBundleFlag = 'TRUE' then
837 l_LineMaster := 'WAITFORFLOW-IND_BUNDLE';
838 else
839 l_LineMaster := 'WAITFORFLOW-DEP_BUNDLE';
840 end if;
841
842
843 if l_BundleID is null or l_BundleID = -1 then
844 /* Need to process all the line items as one bundle */
845
846 /*
847 ** The Bundle is for all the line items with a bundle id of 0.
848 ** The Line items for this also can be sequenced as independent or
849 ** Dependent.
850 **/
851
852 /* Get all the independent lines */
853
854 open c_GetIndLinesNullBundle(l_OrderID);
855 LOOP
856 Fetch c_GetIndLinesNullBundle into l_LineItemID, l_PackageFlag;
857 EXIT when c_GetIndLinesNullBundle%NOTFOUND;
858
859 l_Counter := l_Counter + 1;
860
861 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
862 l_tempKey := to_char(l_OrderID) || '-BUNDLE_LINE-' || to_char(l_LineItemID) || l_tempKey;
863
864 t_ChildTypes(l_Counter) := 'XDPPROV';
865 t_ChildKeys(l_Counter) := l_tempKey;
866
867
868 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
869 itemkey => t_ChildKeys(l_Counter),
870 process => 'LINE_PROCESSING_PROCESS');
871
872 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
873 itemkey => t_ChildKeys(l_Counter),
874 parent_itemtype => LaunchLineForBundleProcess.itemtype,
875 parent_itemkey => LaunchLineForBundleProcess.itemkey,
876 parent_context => null);
877
878 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
879 itemkey => t_ChildKeys(l_Counter),
880 aname => 'ORDER_ID',
881 avalue => l_OrderID);
882
883 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
884 itemkey => t_ChildKeys(l_Counter),
885 aname => 'LINE_ITEM_ID',
886 avalue => l_LineItemID);
887
888 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
889 itemkey => t_ChildKeys(l_Counter),
890 AttrName => 'IS_PACKAGE_FLAG',
891 AttrValue => l_PackageFlag,
892 ErrCode => ErrCode,
893 ErrStr => ErrStr);
894
895 if ErrCode <> 0 then
896 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
897 raise e_AddAttributeException;
898 end if;
899
900
901 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
902 itemkey => t_ChildKeys(l_Counter),
903 AttrName => 'LINE_PROCESSING_CALLER',
904 AttrValue => 'BUNDLE',
905 ErrCode => ErrCode,
906 ErrStr => ErrStr);
907
908 if ErrCode <> 0 then
909 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
910 raise e_AddAttributeException;
911 end if;
912
913
914 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
915 itemkey => t_ChildKeys(l_Counter),
916 AttrName => 'BUNDLE_ID',
917 AttrValue => l_BundleID,
918 ErrCode => ErrCode,
919 ErrStr => ErrStr);
920
921 if ErrCode <> 0 then
922 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
923 raise e_AddAttributeException;
924 end if;
925
926 END LOOP;
927 close c_GetIndLinesNullBundle;
928
929 /* Launch One Dependent Line Item Processing Process */
930 open c_GetDepLinesNullBundle(l_OrderID);
931
932 Fetch c_GetDepLinesNullBundle into l_LineItemID, l_PackageFlag;
933 if c_GetDepLinesNullBundle%FOUND then
934 /**
935 ** Add the new line item id to the list if wf's to be started
936 ** and Create an instance if the dependent line item process
937 **/
938 l_Counter := l_Counter + 1;
939
940 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
941 l_tempKey := to_char(l_OrderID) || '-BUNDLE_LINE-' || to_char(l_LineItemID) || l_tempKey;
942
943 t_ChildTypes(l_Counter) := 'XDPPROV';
944 t_ChildKeys(l_Counter) := l_tempKey;
945
946
947 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
948 itemkey => t_ChildKeys(l_Counter),
949 process => 'LINE_SEQ_PROCESSING');
950
951 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
952 itemkey => t_ChildKeys(l_Counter),
953 parent_itemtype => LaunchLineForBundleProcess.itemtype,
954 parent_itemkey => LaunchLineForBundleProcess.itemkey,
955 parent_context => null);
956
957 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
958 itemkey => t_ChildKeys(l_Counter),
959 aname => 'ORDER_ID',
960 avalue => l_OrderID);
961
962 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
963 itemkey => t_ChildKeys(l_Counter),
964 aname => 'LINE_ITEM_ID',
965 avalue => l_LineItemID);
966
967 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
968 itemkey => t_ChildKeys(l_Counter),
969 AttrName => 'CURRENT_LINE_SEQUENCE',
970 AttrValue => 0,
971 ErrCode => ErrCode,
972 ErrStr => ErrStr);
973
974 if ErrCode <> 0 then
975 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
976 raise e_AddAttributeException;
977 end if;
978
979 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
980 itemkey => t_ChildKeys(l_Counter),
981 AttrName => 'IS_PACKAGE_FLAG',
982 AttrValue => l_PackageFlag,
983 ErrCode => ErrCode,
984 ErrStr => ErrStr);
985
986 if ErrCode <> 0 then
987 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
988 raise e_AddAttributeException;
989 end if;
990
991
992 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
993 itemkey => t_ChildKeys(l_Counter),
994 AttrName => 'LINE_PROCESSING_CALLER',
995 AttrValue => 'BUNDLE',
996 ErrCode => ErrCode,
997 ErrStr => ErrStr);
998
999 if ErrCode <> 0 then
1000 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1001 raise e_AddAttributeException;
1002 end if;
1003
1004 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1005 itemkey => t_ChildKeys(l_Counter),
1006 AttrName => 'BUNDLE_ID',
1007 AttrValue => l_BundleID,
1008 ErrCode => ErrCode,
1009 ErrStr => ErrStr);
1010
1011 if ErrCode <> 0 then
1012 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1013 raise e_AddAttributeException;
1014 end if;
1015
1016 end if;
1017
1018 close c_GetDepLinesNullBundle;
1019
1020
1021 elsif l_BundleID > 0 then
1022 /* Process the lines for a current Bundle ID */
1023 /*
1024 ** The Bundle is for all the line items with a non 0 bundle id.
1025 ** The Line items for this also can be sequenced as independent or
1026 ** Dependent.
1027 **/
1028
1029 /* Get all the independent lines */
1030
1031 open c_GetIndLinesForBundle(l_OrderID, l_BundleID);
1032 LOOP
1033 Fetch c_GetIndLinesForBundle into l_LineItemID, l_PackageFlag;
1034 EXIT when c_GetIndLinesForBundle%NOTFOUND;
1035
1036 l_Counter := l_Counter + 1;
1037
1038 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1039 l_tempKey := to_char(l_OrderID) || '-BUNDLE- ' || to_char(l_BundleID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1040
1041 t_ChildTypes(l_Counter) := 'XDPPROV';
1042 t_ChildKeys(l_Counter) := l_tempKey;
1043
1044
1045 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
1046 itemkey => t_ChildKeys(l_Counter),
1047 process => 'LINE_PROCESSING_PROCESS');
1048
1049 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
1050 itemkey => t_ChildKeys(l_Counter),
1051 parent_itemtype => LaunchLineForBundleProcess.itemtype,
1052 parent_itemkey => LaunchLineForBundleProcess.itemkey,
1053 parent_context => null);
1054
1055 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1056 itemkey => t_ChildKeys(l_Counter),
1057 aname => 'ORDER_ID',
1058 avalue => l_OrderID);
1059
1060 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1061 itemkey => t_ChildKeys(l_Counter),
1062 aname => 'LINE_ITEM_ID',
1063 avalue => l_LineItemID);
1064
1065 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1066 itemkey => t_ChildKeys(l_Counter),
1067 AttrName => 'IS_PACKAGE_FLAG',
1068 AttrValue => l_PackageFlag,
1069 ErrCode => ErrCode,
1070 ErrStr => ErrStr);
1071
1072 if ErrCode <> 0 then
1073 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
1074 raise e_AddAttributeException;
1075 end if;
1076
1077 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1078 itemkey => t_ChildKeys(l_Counter),
1079 AttrName => 'BUNDLE_ID',
1080 AttrValue => l_BundleID,
1081 ErrCode => ErrCode,
1082 ErrStr => ErrStr);
1083
1084 if ErrCode <> 0 then
1085 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1086 raise e_AddAttributeException;
1087 end if;
1088
1089 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1090 itemkey => t_ChildKeys(l_Counter),
1091 AttrName => 'LINE_PROCESSING_CALLER',
1092 AttrValue => 'BUNDLE',
1093 ErrCode => ErrCode,
1094 ErrStr => ErrStr);
1095
1096 if ErrCode <> 0 then
1097 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1098 raise e_AddAttributeException;
1099 end if;
1100
1101
1102
1103 END LOOP;
1104 close c_GetIndLinesForBundle;
1105
1106
1107 /* Launch One Dependent Line Item Processing Process */
1108 open c_GetDepLinesForBundle(l_OrderID, l_BundleID);
1109
1110 Fetch c_GetDepLinesForBundle into l_LineItemID, l_PackageFlag;
1111 if c_GetDepLinesForBundle%FOUND then
1112 /**
1113 ** Add the new line item id to the list if wf's to be started
1114 ** and Create an instance if the dependent line item process
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) || '-BUNDLE_LINE-' || to_char(l_LineItemID) || l_tempKey;
1120
1121 t_ChildTypes(l_Counter) := 'XDPPROV';
1122 t_ChildKeys(l_Counter) := l_tempKey;
1123
1124 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
1125 itemkey => t_ChildKeys(l_Counter),
1126 process => 'LINE_SEQ_PROCESSING');
1127
1128 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
1129 itemkey => t_ChildKeys(l_Counter),
1130 parent_itemtype => LaunchLineForBundleProcess.itemtype,
1131 parent_itemkey => LaunchLineForBundleProcess.itemkey,
1132 parent_context => null);
1133
1134 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1135 itemkey => t_ChildKeys(l_Counter),
1136 aname => 'ORDER_ID',
1137 avalue => l_OrderID);
1138
1139 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1140 itemkey => t_ChildKeys(l_Counter),
1141 aname => 'LINE_ITEM_ID',
1142 avalue => l_LineItemID);
1143
1144 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1145 itemkey => t_ChildKeys(l_Counter),
1146 AttrName => 'CURRENT_LINE_SEQUENCE',
1147 AttrValue => 0,
1148 ErrCode => ErrCode,
1149 ErrStr => ErrStr);
1150
1151 if ErrCode <> 0 then
1152 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1153 raise e_AddAttributeException;
1154 end if;
1155
1156
1157 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1158 itemkey => t_ChildKeys(l_Counter),
1159 AttrName => 'IS_PACKAGE_FLAG',
1160 AttrValue => l_PackageFlag,
1161 ErrCode => ErrCode,
1162 ErrStr => ErrStr);
1163
1164 if ErrCode <> 0 then
1165 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
1166 raise e_AddAttributeException;
1167 end if;
1168
1169 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1170 itemkey => t_ChildKeys(l_Counter),
1171 AttrName => 'BUNDLE_ID',
1172 AttrValue => l_BundleID,
1173 ErrCode => ErrCode,
1174 ErrStr => ErrStr);
1175
1176 if ErrCode <> 0 then
1177 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1178 raise e_AddAttributeException;
1179 end if;
1180
1181 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1182 itemkey => t_ChildKeys(l_Counter),
1183 AttrName => 'LINE_PROCESSING_CALLER',
1184 AttrValue => 'BUNDLE',
1185 ErrCode => ErrCode,
1186 ErrStr => ErrStr);
1187
1188 if ErrCode <> 0 then
1189 x_progress := 'In XDPCORE_WI.LaunchLineForBundleProcess. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1190 raise e_AddAttributeException;
1191 end if;
1192
1193
1194
1195 end if;
1196
1197 close c_GetDepLinesForBundle;
1198 else
1199 /* Some thing Wrong */
1200 -- RAISE; ??
1201 null;
1202 end if;
1203
1204
1205
1206 if l_Counter = 0 then
1207 RAISE e_NoLinesFoundException;
1208 end if;
1209
1210
1211 /* Now start the workflow process */
1212 FOR i in 1..l_Counter LOOP
1213
1214 wf_engine.StartProcess(t_ChildTypes(i),
1215 t_ChildKeys(i));
1216
1217 END LOOP;
1218
1219 exception
1220 when others then
1221 wf_core.context('XDPCORE_BUNDLE', 'LaunchLineForBundleProcess', itemtype, itemkey, null, x_Progress);
1222 raise;
1223 end LaunchLineForBundleProcess;
1224
1225
1226
1227
1228 Procedure LaunchBundleProcessSeq (itemtype in varchar2,
1229 itemkey in varchar2)
1230
1231 is
1232 cursor c_BundleSeq( OrderID number, BundleSeq number) is
1233 select DISTINCT(BUNDLE_ID), BUNDLE_SEQUENCE
1234 from XDP_ORDER_LINE_ITEMS
1235 where ORDER_ID = OrderID
1236 and STATUS_CODE = 'READY'
1237 and BUNDLE_ID >= 0
1238 and BUNDLE_SEQUENCE = (select min(BUNDLE_SEQUENCE)
1239 from XDP_ORDER_LINE_ITEMS
1240 where ORDER_ID = OrderID
1241 and STATUS_CODE = 'READY'
1242 and BUNDLE_ID >= 0
1243 and BUNDLE_SEQUENCE > BundleSeq);
1244
1245 l_OrderID number;
1246 l_BundleID number;
1247 l_LineItemID number;
1248 l_PrevBundleSeq number;
1249 l_CurrentBundleSeq number;
1250 l_Counter number := 0;
1251
1252 l_PackageFlag varchar2(1);
1253 l_tempKey varchar2(240);
1254
1255 cursor c_GetIndLinesForBundle(OrderID number, BundleID number) is
1256 select LINE_ITEM_ID, IS_PACKAGE_FLAG
1257 from XDP_ORDER_LINE_ITEMS
1258 where ORDER_ID = OrderID
1259 and IS_VIRTUAL_LINE_FLAG = 'N'
1260 and STATUS_CODE = 'READY'
1261 and BUNDLE_ID = BundleID
1262 and LINE_SEQUENCE = 0;
1263
1264 cursor c_GetDepLinesForBundle(OrderID number, BundleID number) is
1265 select LINE_ITEM_ID, IS_PACKAGE_FLAG
1266 from XDP_ORDER_LINE_ITEMS
1267 where ORDER_ID = OrderID
1268 and IS_VIRTUAL_LINE_FLAG = 'N'
1269 and STATUS_CODE = 'READY'
1270 and BUNDLE_ID = BundleID
1271 and LINE_SEQUENCE > 0;
1272
1273
1274 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1275 t_ChildKeys t_ChildKeyTable;
1276
1277 TYPE t_ChildTypeTable is table of varchar2(10) INDEX BY BINARY_INTEGER;
1278 t_ChildTypes t_ChildTypeTable;
1279
1280 e_NoBundlesFoundException exception;
1281 e_AddAttributeException exception;
1282
1283 x_Progress VARCHAR2(2000);
1284
1285 ErrCode number;
1286 ErrStr varchar2(1996);
1287 begin
1288 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchBundleProcessSeq.itemtype,
1289 itemkey => LaunchBundleProcessSeq.itemkey,
1290 aname => 'ORDER_ID');
1291
1292 l_PrevBundleSeq := wf_engine.GetItemAttrNumber(itemtype => LaunchBundleProcessSeq.itemtype,
1293 itemkey => LaunchBundleProcessSeq.itemkey,
1294 aname => 'CURRENT_BUNDLE_SEQUENCE');
1295
1296 open c_BundleSeq(l_OrderID, l_PrevBundleSeq);
1297 LOOP
1298
1299 Fetch c_BundleSeq into l_BundleID, l_CurrentBundleSeq;
1300 EXIT when c_BundleSeq%NOTFOUND;
1301
1302 -- dbms_output.put_line('Bundle line item for bundle: ' || l_BundleID);
1303
1304 open c_GetIndLinesForBundle(l_OrderID, l_BundleID);
1305 LOOP
1306 Fetch c_GetIndLinesForBundle into l_LineItemID, l_PackageFlag;
1307 EXIT when c_GetIndLinesForBundle%NOTFOUND;
1308
1309 -- dbms_output.put_line('Bundle: ' || l_BundleID || ' Line: ' || l_LineItemID);
1310 l_Counter := l_Counter + 1;
1311
1312 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1313 l_tempKey := to_char(l_OrderID) || '-BUNDLE- ' || to_char(l_BundleID) || '-LINE-' || to_char(l_LineItemID) || l_tempKey;
1314
1315 t_ChildTypes(l_Counter) := 'XDPPROV';
1316 t_ChildKeys(l_Counter) := l_tempKey;
1317
1318
1319 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
1320 itemkey => t_ChildKeys(l_Counter),
1321 process => 'LINE_PROCESSING_PROCESS');
1322
1323 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
1324 itemkey => t_ChildKeys(l_Counter),
1325 parent_itemtype => LaunchBundleProcessSeq.itemtype,
1326 parent_itemkey => LaunchBundleProcessSeq.itemkey,
1327 parent_context => null);
1328
1329 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1330 itemkey => t_ChildKeys(l_Counter),
1331 aname => 'ORDER_ID',
1332 avalue => l_OrderID);
1333
1334 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1335 itemkey => t_ChildKeys(l_Counter),
1336 aname => 'LINE_ITEM_ID',
1337 avalue => l_LineItemID);
1338
1339 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1340 itemkey => t_ChildKeys(l_Counter),
1341 AttrName => 'IS_PACKAGE_FLAG',
1342 AttrValue => l_PackageFlag,
1343 ErrCode => ErrCode,
1344 ErrStr => ErrStr);
1345
1346 if ErrCode <> 0 then
1347 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
1348 raise e_AddAttributeException;
1349 end if;
1350
1351 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1352 itemkey => t_ChildKeys(l_Counter),
1353 AttrName => 'BUNDLE_ID',
1354 AttrValue => l_BundleID,
1355 ErrCode => ErrCode,
1356 ErrStr => ErrStr);
1357
1358 if ErrCode <> 0 then
1359 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1360 raise e_AddAttributeException;
1361 end if;
1362
1363 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1364 itemkey => t_ChildKeys(l_Counter),
1365 AttrName => 'LINE_PROCESSING_CALLER',
1366 AttrValue => 'BUNDLE',
1367 ErrCode => ErrCode,
1368 ErrStr => ErrStr);
1369
1370 if ErrCode <> 0 then
1371 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1372 raise e_AddAttributeException;
1373 end if;
1374
1375
1376 END LOOP;
1377 close c_GetIndLinesForBundle;
1378
1379
1380 /* Launch One Dependent Line Item Processing Process */
1381 open c_GetDepLinesForBundle(l_OrderID, l_BundleID);
1382
1383 Fetch c_GetDepLinesForBundle into l_LineItemID, l_PackageFlag;
1384 if c_GetDepLinesForBundle%FOUND then
1385 /**
1386 ** Add the new line item id to the list if wf's to be started
1387 ** and Create an instance if the dependent line item process
1388 **/
1389 l_Counter := l_Counter + 1;
1390
1391 -- dbms_output.put_line('Bundle: ' || l_BundleID || ' Line: ' || l_LineItemID);
1392
1393 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1394 l_tempKey := to_char(l_OrderID) || '-BUNDLE_LINE-' || to_char(l_LineItemID) || l_tempKey;
1395
1396 t_ChildTypes(l_Counter) := 'XDPPROV';
1397 t_ChildKeys(l_Counter) := l_tempKey;
1398
1399 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
1400 itemkey => t_ChildKeys(l_Counter),
1401 process => 'LINE_SEQ_PROCESSING');
1402
1403 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
1404 itemkey => t_ChildKeys(l_Counter),
1405 parent_itemtype => LaunchBundleProcessSeq.itemtype,
1406 parent_itemkey => LaunchBundleProcessSeq.itemkey,
1407 parent_context => null);
1408
1409 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1410 itemkey => t_ChildKeys(l_Counter),
1411 aname => 'ORDER_ID',
1412 avalue => l_OrderID);
1413
1414 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
1415 itemkey => t_ChildKeys(l_Counter),
1416 aname => 'LINE_ITEM_ID',
1417 avalue => l_LineItemID);
1418
1419 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1420 itemkey => t_ChildKeys(l_Counter),
1421 AttrName => 'CURRENT_LINE_SEQUENCE',
1422 AttrValue => 0,
1423 ErrCode => ErrCode,
1424 ErrStr => ErrStr);
1425
1426 if ErrCode <> 0 then
1427 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute CURRENT_LINE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1428 raise e_AddAttributeException;
1429 end if;
1430
1431 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1432 itemkey => t_ChildKeys(l_Counter),
1433 AttrName => 'IS_PACKAGE_FLAG',
1434 AttrValue => l_PackageFlag,
1435 ErrCode => ErrCode,
1436 ErrStr => ErrStr);
1437
1438 if ErrCode <> 0 then
1439 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute IS_PACKAGE_FLAG. Error: ' || substr(ErrStr,1,1500);
1440 raise e_AddAttributeException;
1441 end if;
1442
1443 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
1444 itemkey => t_ChildKeys(l_Counter),
1445 AttrName => 'BUNDLE_ID',
1446 AttrValue => l_BundleID,
1447 ErrCode => ErrCode,
1448 ErrStr => ErrStr);
1449
1450 if ErrCode <> 0 then
1451 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1452 raise e_AddAttributeException;
1453 end if;
1454
1455 XDPCORE.CheckNAddItemAttrText (itemtype => t_ChildTypes(l_Counter),
1456 itemkey => t_ChildKeys(l_Counter),
1457 AttrName => 'LINE_PROCESSING_CALLER',
1458 AttrValue => 'BUNDLE',
1459 ErrCode => ErrCode,
1460 ErrStr => ErrStr);
1461
1462 if ErrCode <> 0 then
1463 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1464 raise e_AddAttributeException;
1465 end if;
1466
1467
1468 end if;
1469
1470 close c_GetDepLinesForBundle;
1471
1472 END LOOP;
1473
1474 if l_Counter = 0 and l_CurrentBundleSeq = 0 then
1475 RAISE e_NoBundlesFoundException;
1476 else
1477
1478 XDPCORE.CheckNAddItemAttrNumber (itemtype => LaunchBundleProcessSeq.itemtype,
1479 itemkey => LaunchBundleProcessSeq.itemkey,
1480 AttrName => 'CURRENT_BUNDLE_SEQUENCE',
1481 AttrValue => l_CurrentBundleSeq,
1482 ErrCode => ErrCode,
1483 ErrStr => ErrStr);
1484
1485 if ErrCode <> 0 then
1486 x_progress := 'In XDPCORE_WI.LaunchBundleProcessSeq. Error when adding Item Attribute CURRENT_BUNDLE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1487 raise e_AddAttributeException;
1488 end if;
1489
1490
1491 FOR i in 1..l_Counter LOOP
1492 wf_engine.StartProcess(t_ChildTypes(i),
1493 t_ChildKeys(i));
1494 END LOOP;
1495
1496 end if;
1497
1498 exception
1499 when others then
1500 wf_core.context('XDPCORE_BUNDLE', 'LaunchBundleProcessSeq', itemtype, itemkey, null, x_Progress);
1501 raise;
1502 end LaunchBundleProcessSeq;
1503
1504
1505 Procedure InitializeBundle (itemtype in varchar2,
1506 itemkey in varchar2) IS
1507
1508 l_BundleID number;
1509 l_OrderID number;
1510
1511 ErrCode number;
1512 ErrStr varchar2(1996);
1513
1514 x_Progress VARCHAR2(2000);
1515 e_AddAttributeException EXCEPTION;
1516
1517 begin
1518
1519 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => InitializeBundle.itemtype,
1520 itemkey => InitializeBundle.itemkey,
1521 aname => 'ORDER_ID');
1522
1523 l_BundleID := wf_engine.GetItemAttrNumber(itemtype => InitializeBundle.itemtype,
1524 itemkey => InitializeBundle.itemkey,
1525 aname => 'BUNDLE_ID');
1526
1527 -- if l_BundleID > 0 then
1528
1529 UPDATE_BUNDLE_STATUS(p_bundle_id => l_BundleID,
1530 p_order_id => l_OrderID,
1531 p_status_code => 'IN PROGRESS',
1532 p_itemtype => InitializeBundle.itemtype,
1533 p_itemkey => InitializeBundle.itemkey) ;
1534 -- end if;
1535 XDPCORE.CheckNAddItemAttrText (itemtype => 'XDPPROV',
1536 itemkey => InitializeBundle.itemkey,
1537 AttrName => 'LINE_PROCESSING_CALLER',
1538 AttrValue => 'BUNDLE',
1539 ErrCode => ErrCode,
1540 ErrStr => ErrStr);
1541 if ErrCode <> 0 then
1542 x_progress := 'In XDPCORE_BUNDLE.InitializeBundle. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1543 raise e_AddAttributeException;
1544 end if;
1545
1546
1547 exception
1548 when others then
1549 wf_core.context('XDPCORE_BUNDLE', 'InitializeBundle', itemtype, itemkey, null, x_Progress);
1550 raise;
1551 end InitializeBundle;
1552
1553 Function ResolveIndDepBundles (itemtype in varchar2,
1554 itemkey in varchar2) return varchar2
1555 IS
1556
1557 l_OrderID NUMBER;
1558
1559 l_IndFound number := 0;
1560 l_DepFound number := 0;
1561
1562 cursor c_GetIndBundles(OrderID number) is
1563 select 'Y'
1564 from XDP_ORDER_LINE_ITEMS
1565 where ORDER_ID = OrderID
1566 and STATUS_CODE = 'READY'
1567 and ( (BUNDLE_SEQUENCE is null) OR (BUNDLE_SEQUENCE = 0));
1568
1569 cursor c_GetDepBundles(OrderID number) is
1570 select 'Y'
1571 from XDP_ORDER_LINE_ITEMS
1572 where ORDER_ID = OrderID
1573 and STATUS_CODE = 'READY'
1574 and BUNDLE_ID IS NOT NULL
1575 and BUNDLE_SEQUENCE > 0;
1576
1577 BEGIN
1578
1579 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepBundles.itemtype,
1580 itemkey => ResolveIndDepBundles.itemkey,
1581 aname => 'ORDER_ID');
1582
1583
1584 FOR lv_BundleRec in c_GetIndBundles( l_OrderID ) LOOP
1585 l_IndFound := 1;
1586 EXIT;
1587 END LOOP;
1588
1589 FOR lv_BundleRec in c_GetDepBundles( l_OrderID ) LOOP
1590 l_DepFound := 1;
1591 EXIT;
1592 END LOOP;
1593
1594
1595 if( l_IndFound = 1 AND l_DepFound = 1 ) THEN
1596 RETURN 'BOTH';
1597 elsif( l_IndFound = 1) THEN
1598 RETURN 'INDEPENDENT';
1599 elsif( l_DepFound = 1 ) THEN
1600 RETURN 'DEPENDENT';
1601 end if;
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 wf_core.context('XDPCORE_BUNDLE', 'ResolveIndDepBundles', itemtype, itemkey );
1606 RAISE;
1607 END ResolveIndDepBundles;
1608
1609
1610 Function LaunchAllIndBundles(itemtype in varchar2,
1611 itemkey in varchar2) return varchar2
1612 IS
1613 l_tempKey varchar2(240);
1614 l_OrderID number;
1615 l_BundleID number;
1616 l_Counter number := 0;
1617
1618 l_result varchar2(1) := 'N';
1619
1620 cursor c_GetIndBundles(OrderID number) is
1621 select distinct(NVL(BUNDLE_ID, -1)) BUNDLE_ID
1622 from XDP_ORDER_LINE_ITEMS
1623 where ORDER_ID = OrderID
1624 and STATUS_CODE = 'READY'
1625 and ((BUNDLE_SEQUENCE is null) OR (BUNDLE_SEQUENCE = 0));
1626
1627 TYPE t_ChildKeyTable is table of varchar2(240) INDEX BY BINARY_INTEGER;
1628 t_ChildKeys t_ChildKeyTable;
1629
1630 e_InvalidConfigException exception;
1631 e_AddAttributeException exception;
1632 x_Progress VARCHAR2(2000);
1633
1634 ErrCode number;
1635 ErrStr varchar2(1996);
1636
1637
1638 BEGIN
1639
1640 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndBundles.itemtype,
1641 itemkey => LaunchAllIndBundles.itemkey,
1642 aname => 'ORDER_ID');
1643
1644
1645
1646 FOR lv_BundleRec in c_GetIndBundles( l_OrderID ) LOOP
1647 l_result := 'Y';
1648 l_Counter := l_Counter + 1;
1649
1650 l_BundleID := lv_BundleRec.BUNDLE_ID;
1651 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1652 l_tempKey := to_char(l_OrderID) || '-BUNDLE-' || l_tempKey;
1653
1654 t_ChildKeys(l_Counter) := l_tempKey;
1655
1656 wf_engine.CreateProcess(itemtype => 'XDPPROV',
1657 itemkey => t_ChildKeys(l_Counter),
1658 process => 'BUNDLE_PROCESS');
1659
1660 wf_engine.setItemParent(itemtype => 'XDPPROV',
1661 itemkey => t_ChildKeys(l_Counter),
1662 parent_itemtype => itemtype,
1663 parent_itemkey => itemkey,
1664 parent_context => 'WAITFORFLOW-BUNDLE-IND');
1665
1666 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1667 itemkey => t_ChildKeys(l_Counter),
1668 aname => 'MASTER_TO_CONTINUE',
1669 avalue => 'WAITFORFLOW-BUNDLE-IND');
1670
1671 wf_engine.SetItemAttrNumber(itemtype => 'XDPPROV',
1672 itemkey => t_ChildKeys(l_Counter),
1673 aname => 'ORDER_ID',
1674 avalue => l_OrderID);
1675
1676 XDPCORE.CheckNAddItemAttrNumber (itemtype =>'XDPPROV',
1677 itemkey => t_ChildKeys(l_Counter),
1678 AttrName => 'BUNDLE_ID',
1679 AttrValue => l_BundleID,
1680 ErrCode => ErrCode,
1681 ErrStr => ErrStr);
1682
1683 if ErrCode <> 0 then
1684 x_progress := 'In XDPCORE_BUNDLE.LaunchAllIndBundles. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1685 raise e_AddAttributeException;
1686 end if;
1687 END LOOP;
1688
1689 for i in 1..l_Counter loop
1690
1691 wf_engine.StartProcess(itemtype => 'XDPPROV', itemkey => t_ChildKeys(i));
1692 end loop;
1693
1694 return l_result;
1695
1696 EXCEPTION
1697 WHEN OTHERS THEN
1698 wf_core.context('XDPCORE_BUNDLE', 'LaunchAllIndBundles', itemtype, itemkey, null, x_progress );
1699 RAISE;
1700 END LaunchAllIndBundles;
1701
1702 Procedure InitializeDepBundleProcess(itemtype in varchar2,
1703 itemkey in varchar2)
1704 IS
1705
1706 e_AddAttributeException exception;
1707 x_Progress VARCHAR2(2000);
1708
1709 ErrCode number;
1710 ErrStr varchar2(1996);
1711
1712 BEGIN
1713 XDPCORE.CheckNAddItemAttrNumber (itemtype => 'XDPPROV',
1714 itemkey => InitializeDepBundleProcess.itemkey,
1715 AttrName => 'CURRENT_BUNDLE_SEQUENCE',
1716 AttrValue => 0,
1717 ErrCode => ErrCode,
1718 ErrStr => ErrStr);
1719
1720 if ErrCode <> 0 then
1721 x_progress := 'In XDPCORE_BUNDLE.CheckNAddItemAttrNumber. Error when adding Item Attribute CURRENT_BUNDLE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1722 raise e_AddAttributeException;
1723 end if;
1724
1725
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 wf_core.context('XDPCORE_BUNDLE', 'InitializeDepBundleProcess', itemtype, itemkey, null, x_progress );
1729 RAISE;
1730 END InitializeDepBundleProcess;
1731
1732 Procedure LaunchBundleProcess(itemtype in varchar2,
1733 itemkey in varchar2)
1734 IS
1735
1736 l_OrderID number;
1737 l_BundleID number;
1738 l_LineItemID number;
1739 l_PrevBundleSeq number;
1740 l_CurrentBundleSeq number;
1741
1742 l_PackageFlag varchar2(1);
1743 l_tempKey varchar2(240);
1744
1745 cursor c_BundleSeq( OrderID number, BundleSeq number) is
1746 select DISTINCT(BUNDLE_ID) BUNDLE_ID, BUNDLE_SEQUENCE
1747 from XDP_ORDER_LINE_ITEMS
1748 where ORDER_ID = OrderID
1749 and STATUS_CODE = 'READY'
1750 and BUNDLE_ID >= 0
1751 and BUNDLE_SEQUENCE = (select min(BUNDLE_SEQUENCE)
1752 from XDP_ORDER_LINE_ITEMS
1753 where ORDER_ID = OrderID
1754 and STATUS_CODE = 'READY'
1755 and BUNDLE_ID >= 0
1756 and BUNDLE_SEQUENCE > BundleSeq);
1757
1758
1759 e_AddAttributeException exception;
1760 x_Progress VARCHAR2(2000);
1761
1762 ErrCode number;
1763 ErrStr varchar2(1996);
1764
1765
1766
1767 BEGIN
1768
1769
1770 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchBundleProcess.itemtype,
1771 itemkey => LaunchBundleProcess.itemkey,
1772 aname => 'ORDER_ID');
1773
1774 l_PrevBundleSeq := wf_engine.GetItemAttrNumber(itemtype => LaunchBundleProcess.itemtype,
1775 itemkey => LaunchBundleProcess.itemkey,
1776 aname => 'CURRENT_BUNDLE_SEQUENCE');
1777
1778
1779 FOR lv_BundelRec in c_BundleSeq( l_OrderID, l_PrevBundleSeq ) LOOP
1780 l_BundleID := lv_BundelRec.BUNDLE_ID;
1781 l_CurrentBundleSeq := lv_BundelRec.BUNDLE_SEQUENCE;
1782
1783 select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
1784 l_tempKey := to_char(l_OrderID) || '-BUNDLE-' || to_char(l_LineItemID) || l_tempKey;
1785
1786 wf_engine.CreateProcess(itemtype => 'XDPPROV',
1787 itemkey => l_tempKey,
1788 process => 'BUNDLE_PROCESS');
1789
1790 wf_engine.setItemParent(itemtype => 'XDPPROV',
1791 itemkey => l_tempKey,
1792 parent_itemtype => itemtype,
1793 parent_itemkey => itemkey,
1794 parent_context => 'WAITFORFLOW-BUNDLE-DEP');
1795
1796 wf_engine.SetItemAttrNumber(itemtype => 'XDPPROV',
1797 itemkey => l_tempKey,
1798 aname => 'ORDER_ID',
1799 avalue => l_OrderID);
1800
1801 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
1802 itemkey => l_tempKey,
1803 aname => 'MASTER_TO_CONTINUE',
1804 avalue => 'WAITFORFLOW-BUNDLE-DEP');
1805
1806 XDPCORE.CheckNAddItemAttrNumber (itemtype =>'XDPPROV',
1807 itemkey => l_tempKey,
1808 AttrName => 'BUNDLE_ID',
1809 AttrValue => l_BundleID,
1810 ErrCode => ErrCode,
1811 ErrStr => ErrStr);
1812
1813 if ErrCode <> 0 then
1814 x_progress := 'In XDPCORE_BUNDLE.LaunchBundleProcess. Error when adding Item Attribute BUNDLE_ID. Error: ' || substr(ErrStr,1,1500);
1815 raise e_AddAttributeException;
1816 end if;
1817
1818 XDPCORE.CheckNAddItemAttrText (itemtype => 'XDPPROV',
1819 itemkey => l_tempKey,
1820 AttrName => 'LINE_PROCESSING_CALLER',
1821 AttrValue => 'BUNDLE',
1822 ErrCode => ErrCode,
1823 ErrStr => ErrStr);
1824
1825
1826 if ErrCode <> 0 then
1827 x_progress := 'In XDPCORE_BUNDLE.LaunchBundleProcess. Error when adding Item Attribute LINE_PROCESSING_CALLER. Error: ' || substr(ErrStr,1,1500);
1828 raise e_AddAttributeException;
1829 end if;
1830
1831 --Start the process...
1832 wf_engine.StartProcess('XDPPROV', l_tempKey );
1833
1834 END LOOP;
1835
1836 XDPCORE.CheckNAddItemAttrNumber (itemtype => LaunchBundleProcess.itemtype,
1837 itemkey => LaunchBundleProcess.itemkey,
1838 AttrName => 'CURRENT_BUNDLE_SEQUENCE',
1839 AttrValue => l_CurrentBundleSeq,
1840 ErrCode => ErrCode,
1841 ErrStr => ErrStr);
1842
1843 if ErrCode <> 0 then
1844 x_progress := 'In XDPCORE_BUNDLE.LaunchBundleProcess. Error when adding Item Attribute CURRENT_BUNDLE_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1845 raise e_AddAttributeException;
1846 end if;
1847
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 wf_core.context('XDPCORE_BUNDLE', 'LaunchBundleProcess', itemtype, itemkey, null, x_progress );
1851 RAISE;
1852 END LaunchBundleProcess;
1853
1854 Function ResolveIndDepLinesForBun (itemtype in varchar2,
1855 itemkey in varchar2) return varchar2
1856 is
1857 l_OrderID number;
1858 l_BundleID number;
1859
1860 l_IndFound number := 0;
1861 l_DepFound number := 0;
1862
1863 x_Progress VARCHAR2(2000);
1864
1865 cursor c_GetIndLinesNullBundle(OrderID number) is
1866 select 'Y'
1867 from XDP_ORDER_LINE_ITEMS
1868 where ORDER_ID = OrderID
1869 and IS_VIRTUAL_LINE_FLAG = 'N'
1870 and STATUS_CODE = 'READY'
1871 and BUNDLE_ID is null
1872 and LINE_SEQUENCE = 0;
1873
1874
1875 cursor c_GetDepLinesNullBundle(OrderID number) is
1876 select 'Y'
1877 from XDP_ORDER_LINE_ITEMS
1878 where ORDER_ID = OrderID
1879 and IS_VIRTUAL_LINE_FLAG = 'N'
1880 and STATUS_CODE = 'READY'
1881 and BUNDLE_ID is null
1882 and LINE_SEQUENCE > 0;
1883
1884 cursor c_GetIndLinesForBundle(OrderID number, BundleID number) is
1885 select 'Y'
1886 from XDP_ORDER_LINE_ITEMS
1887 where ORDER_ID = OrderID
1888 and IS_VIRTUAL_LINE_FLAG = 'N'
1889 and STATUS_CODE = 'READY'
1890 and BUNDLE_ID = BundleID
1891 and LINE_SEQUENCE = 0;
1892
1893
1894 cursor c_GetDepLinesForBundle(OrderID number, BundleID number) is
1895 select 'Y'
1896 from XDP_ORDER_LINE_ITEMS
1897 where ORDER_ID = OrderID
1898 and IS_VIRTUAL_LINE_FLAG = 'N'
1899 and STATUS_CODE = 'READY'
1900 and BUNDLE_ID = BundleID
1901 and LINE_SEQUENCE > 0;
1902
1903
1904 begin
1905
1906 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepLinesForBun.itemtype,
1907 itemkey => ResolveIndDepLinesForBun.itemkey,
1908 aname => 'ORDER_ID');
1909
1910 l_BundleID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepLinesForBun.itemtype,
1911 itemkey => ResolveIndDepLinesForBun.itemkey,
1912 aname => 'BUNDLE_ID');
1913
1914 if l_BundleID is null or l_BundleID = -1 then
1915
1916 FOR lv_LineRec in c_GetIndLinesNullBundle( l_OrderID ) LOOP
1917 l_IndFound := 1;
1918 EXIT;
1919 END LOOP;
1920
1921 FOR lv_LineRec in c_GetDepLinesNullBundle( l_OrderID ) LOOP
1922 l_DepFound := 1;
1923 EXIT;
1924 END LOOP;
1925
1926 elsif l_BundleID > 0 then
1927
1928 FOR lv_LineRec in c_GetIndLinesForBundle( l_OrderID, l_BundleID ) LOOP
1929 l_IndFound := 1;
1930 EXIT;
1931 END LOOP;
1932
1933 FOR lv_LineRec in c_GetDepLinesForBundle( l_OrderID, l_BundleID ) LOOP
1934 l_DepFound := 1;
1935 EXIT;
1936 END LOOP;
1937 end if;
1938
1939
1940 if( l_IndFound = 1 AND l_DepFound = 1 ) THEN
1941 RETURN 'BOTH';
1942 elsif( l_IndFound = 1) THEN
1943 RETURN 'INDEPENDENT';
1944 elsif( l_DepFound = 1 ) THEN
1945 RETURN 'DEPENDENT';
1946 end if;
1947
1948
1949 exception
1950
1951 when others then
1952
1953 x_Progress := 'XDPCORE_BUNDLE.ResolveIndDepLinesForBun. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1200);
1954 wf_core.context('XDPCORE_BUNDLE', 'ResolveIndDepLinesForBun', itemtype, itemkey, null, x_Progress);
1955 raise;
1956 end ResolveIndDepLinesForBun;
1957
1958 End XDPCORE_BUNDLE;