[Home] [Help]
PACKAGE BODY: APPS.XDPCORE_PKG
Source
1 PACKAGE BODY XDPCORE_PKG AS
2 /* $Header: XDPCORPB.pls 120.1 2005/06/15 22:40:00 appldev $ */
3
4
5 /****
6 All Private Procedures for the Package
7 ****/
8
9 FUNCTION HandleOtherWFFuncmode (funcmode IN VARCHAR2) RETURN VARCHAR2;
10
11 PROCEDURE InitializePackageService(itemtype IN VARCHAR2,
12 itemkey IN VARCHAR2);
13
14 FUNCTION AreAllServicesInPkgDone (itemtype IN VARCHAR2,
15 itemkey IN VARCHAR2) RETURN VARCHAR2;
16
17 PROCEDURE LaunchServiceForPkgProcess(itemtype IN VARCHAR2,
18 itemkey IN VARCHAR2);
19
20 PROCEDURE LaunchServiceInPackage(itemtype IN VARCHAR2,
21 itemkey IN VARCHAR2);
22
23 FUNCTION ResolveIndDepPkgs (itemtype IN VARCHAR2,
24 itemkey IN VARCHAR2) RETURN VARCHAR2;
25
26 Function LaunchAllIndServices(itemtype IN VARCHAR2,
27 itemkey IN VARCHAR2) return varchar2;
28
29 PROCEDURE InitializeDepServiceProcess(itemtype IN VARCHAR2,
30 itemkey IN VARCHAR2);
31
32 TYPE RowidArrayType IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
33
34 PROCEDURE UPDATE_PACKAGESERVICE_STATUS (p_line_item_id IN NUMBER,
35 p_status_code IN VARCHAR2,
36 p_itemtype IN VARCHAR2,
37 p_itemkey IN VARCHAR2) ;
38
39
40 /***********************************************
41 * END of Private Procedures/Function Definitions
42 ************************************************/
43
44 -- INITIALIZE_PACKAGE_SERVICE
45 -- Resultout
46 -- Activity Performed - Activity was completed without any errors
47 --
48 -- Your Description here:
49
50 PROCEDURE INITIALIZE_PACKAGE_SERVICE (itemtype IN VARCHAR2,
51 itemkey IN VARCHAR2,
52 actid IN NUMBER,
53 funcmode IN VARCHAR2,
54 resultout OUT NOCOPY VARCHAR2 ) IS
55
56 x_Progress VARCHAR2(2000);
57
58 BEGIN
59
60 -- RUN mode - normal process execution
61 --
62 IF (funcmode = 'RUN') THEN
63 InitializePackageService(itemtype, itemkey);
64 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
65 return;
66 ELSE
67 resultout := HandleOtherWFFuncmode(funcmode);
68 return;
69 END IF;
70
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 wf_core.context('XDPCORE_PKG', 'INITIALIZE_PACKAGE_SERVICE', itemtype, itemkey, to_char(actid), funcmode);
75 raise;
76 END INITIALIZE_PACKAGE_SERVICE;
77
78
79
80
81
82 -- LAUNCH_SERVICE_FOR_PKG_PROCESS
83 -- Resultout
84 -- Activity Performed - Activity was completed without any errors
85 --
86 -- Your Description here:
87
88 PROCEDURE LAUNCH_SERVICE_FOR_PKG_PROCESS (itemtype IN VARCHAR2,
89 itemkey IN VARCHAR2,
90 actid IN NUMBER,
91 funcmode IN VARCHAR2,
92 resultout OUT NOCOPY VARCHAR2 ) IS
93
94 x_Progress VARCHAR2(2000);
95
96 BEGIN
97
98 -- RUN mode - normal process execution
99 --
100 IF (funcmode = 'RUN') THEN
101 LaunchServiceForPkgProcess(itemtype, itemkey);
102 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
103 return;
104 ELSE
105 resultout := HandleOtherWFFuncmode(funcmode);
106 return;
107 END IF;
108
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 wf_core.context('XDPCORE_PKG', 'LAUNCH_SERVICE_FOR_PKG_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
113 raise;
114 END LAUNCH_SERVICE_FOR_PKG_PROCESS;
115
116
117
118
119 -- LAUNCH_SERVICE_IN_PACKAGE
120 -- Resultout
121 -- Activity Performed - Activity was completed without any errors
122 --
123 -- Your Description here:
124
125 PROCEDURE LAUNCH_SERVICE_IN_PACKAGE (itemtype IN VARCHAR2,
126 itemkey IN VARCHAR2,
127 actid IN NUMBER,
128 funcmode IN VARCHAR2,
129 resultout OUT NOCOPY VARCHAR2 ) IS
130
131 x_Progress VARCHAR2(2000);
132
133 BEGIN
134
135 -- RUN mode - normal process execution
136 --
137 IF (funcmode = 'RUN') THEN
138 LaunchServiceInPackage(itemtype, itemkey);
139 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
140 return;
141 ELSE
142 resultout := HandleOtherWFFuncmode(funcmode);
143 return;
144 END IF;
145
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 wf_core.context('XDPCORE_PKG', 'LAUNCH_SERVICE_IN_PACKAGE', itemtype, itemkey, to_char(actid), funcmode);
150 raise;
151 END LAUNCH_SERVICE_IN_PACKAGE;
152
153
154
155
156
157
158 -- ARE_ALL_SERVICES_IN_PKG_DONE
159 -- Resultout
160 -- Activity Performed - Activity was completed without any errors
161 --
162 -- Your Description here:
163
164 PROCEDURE ARE_ALL_SERVICES_IN_PKG_DONE (itemtype IN VARCHAR2,
165 itemkey IN VARCHAR2,
166 actid IN NUMBER,
167 funcmode IN VARCHAR2,
168 resultout OUT NOCOPY VARCHAR2 ) IS
169 l_result VARCHAR2(10);
170 x_Progress VARCHAR2(2000);
171
172 BEGIN
173
174 -- RUN mode - normal process execution
175 --
176 IF (funcmode = 'RUN') THEN
177 l_result := AreAllServicesInPkgDone(itemtype, itemkey);
178 resultout := 'COMPLETE:' || l_result;
179 return;
180 ELSE
181 resultout := HandleOtherWFFuncmode(funcmode);
182 return;
183 END IF;
184
185
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 wf_core.context('XDPCORE_PKG', 'ARE_ALL_SERVICES_IN_PKG_DONE', itemtype, itemkey, to_char(actid), funcmode);
190 raise;
191 END ARE_ALL_SERVICES_IN_PKG_DONE;
192
193
194 PROCEDURE UPDATE_PACKAGESERVICE_STATUS (p_line_item_id IN NUMBER,
195 p_status_code IN VARCHAR2,
196 p_itemtype IN VARCHAR2,
197 p_itemkey IN VARCHAR2) IS
198 PRAGMA AUTONOMOUS_TRANSACTION ;
199
200 x_Progress VARCHAR2(2000);
201
202 BEGIN
203 UPDATE xdp_order_line_items
204 SET status_code = p_status_code,
205 last_update_date = sysdate,
206 last_updated_by = fnd_global.user_id,
207 last_update_login = fnd_global.login_id
208 WHERE line_item_id = p_line_item_id ;
209 COMMIT;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 x_Progress := 'XDPCORE_PKG.UPDATE_PACKAGESERVICE_STATUS. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1500);
214 wf_core.context('XDPCORE_PKG', 'UPDATE_PACKAGESERVICE_STATUS', p_itemtype, p_itemkey, null,null);
215 rollback;
216 raise;
217 END UPDATE_PACKAGESERVICE_STATUS;
218
219
220 PROCEDURE RESOLVE_IND_DEP_PKGS (itemtype IN VARCHAR2,
221 itemkey IN VARCHAR2,
222 actid IN NUMBER,
223 funcmode IN VARCHAR2,
224 resultout OUT NOCOPY VARCHAR2 ) IS
225 l_result VARCHAR2(15);
226 x_Progress VARCHAR2(2000);
227
228 BEGIN
229
230 -- RUN mode - normal process execution
231 --
232 IF (funcmode = 'RUN') THEN
233 l_result := ResolveIndDepPkgs(itemtype, itemkey);
234 resultout := 'COMPLETE:' || l_result;
235 return;
236 ELSE
237 resultout := HandleOtherWFFuncmode(funcmode);
238 return;
239 END IF;
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 wf_core.context('XDPCORE_PKG', 'RESOLVE_IND_DEP_PKGS', itemtype, itemkey, to_char(actid), funcmode);
244 raise;
245 END RESOLVE_IND_DEP_PKGS;
246
247 PROCEDURE LAUNCH_ALL_IND_SERVICES (itemtype IN VARCHAR2,
248 itemkey IN VARCHAR2,
249 actid IN NUMBER,
250 funcmode IN VARCHAR2,
251 resultout OUT NOCOPY VARCHAR2 ) IS
252 x_Progress VARCHAR2(2000);
253 l_result varchar2(1):= 'N';
254
255 BEGIN
256
257 -- RUN mode - normal process execution
258 --
259 IF (funcmode = 'RUN') THEN
260 l_result := LaunchAllIndServices(itemtype, itemkey);
261 resultout := 'COMPLETE:' || l_result;
262 return;
263 ELSE
264 resultout := HandleOtherWFFuncmode(funcmode);
265 return;
266 END IF;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 wf_core.context('XDPCORE_PKG', 'LAUNCH_ALL_IND_SERVICES', itemtype, itemkey, to_char(actid), funcmode);
271 raise;
272 END LAUNCH_ALL_IND_SERVICES;
273
274 PROCEDURE INITIALIZE_DEP_SERVICE_PROCESS (itemtype IN VARCHAR2,
275 itemkey IN VARCHAR2,
276 actid IN NUMBER,
277 funcmode IN VARCHAR2,
278 resultout OUT NOCOPY VARCHAR2 ) IS
279 x_Progress VARCHAR2(2000);
280
281 BEGIN
282
283 -- RUN mode - normal process execution
284 --
285 IF (funcmode = 'RUN') THEN
286 InitializeDepServiceProcess(itemtype, itemkey);
287 resultout := 'COMPLETE';
288 return;
289 ELSE
290 resultout := HandleOtherWFFuncmode(funcmode);
291 return;
292 END IF;
293
294 EXCEPTION
295 WHEN OTHERS THEN
296 wf_core.context('XDPCORE_PKG', 'INITIALIZE_DEP_SERVICE_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
297 raise;
298 END INITIALIZE_DEP_SERVICE_PROCESS;
299
300 /****
301 All the Private Functions
302 ****/
303
304 FUNCTION HandleOtherWFFuncmode( funcmode IN VARCHAR2) RETURN VARCHAR2
305 IS
306 resultout VARCHAR2(30);
307 x_Progress VARCHAR2(2000);
308
309 BEGIN
310
311 IF (funcmode = 'CANCEL') THEN
312 resultout := 'COMPLETE';
313 END IF;
314
315 IF (funcmode = 'RESPOND') THEN
316 resultout := 'COMPLETE';
317 END IF;
318
319 IF (funcmode = 'FORWARD') THEN
320 resultout := 'COMPLETE';
321 END IF;
322
323 IF (funcmode = 'TRANSFER') THEN
324 resultout := 'COMPLETE';
325 END IF;
326
327 IF (funcmode = 'TIMEOUT') THEN
328 resultout := 'COMPLETE';
329 END IF;
330
331 IF (funcmode = 'others') THEN
332 resultout := 'COMPLETE';
333 END IF;
334
335
336 return resultout;
337
338 END;
339
340
341 PROCEDURE LaunchServiceForPkgProcess (itemtype IN VARCHAR2,
342 itemkey IN VARCHAR2)
343 IS
344 l_OrderID NUMBER;
345 l_LineItemID NUMBER;
346 l_SrvLineItemID NUMBER;
347 l_Counter NUMBER := 0;
348 l_tempKey VARCHAR2(240);
349
350 TYPE t_ChildKeyTable IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
351 t_ChildKeys t_ChildKeyTable;
352
353 TYPE t_ChildTypeTable IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
354 t_ChildTypes t_ChildTypeTable;
355
356 CURSOR c_GetIndServices (LineItemID NUMBER) IS
357 SELECT XOL.LINE_ITEM_ID
358 FROM XDP_LINE_RELATIONSHIPS XLR,
359 XDP_ORDER_LINE_ITEMS XOL
360 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
361 AND XLR.LINE_RELATIONSHIP IN ('IS_PART_OF_PACKAGE','IS_PART_OF_IB_EXPLOSION')
362 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
363 AND XOL.STATUS_CODE = 'READY'
364 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
365 AND (SEQ_IN_PACKAGE IS NULL OR SEQ_IN_PACKAGE = 0);
366
367 CURSOR c_GetDepServices (LineItemID NUMBER) IS
368 SELECT XOL.LINE_ITEM_ID
369 FROM XDP_LINE_RELATIONSHIPS XLR, XDP_ORDER_LINE_ITEMS XOL
370 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
371 AND XLR.LINE_RELATIONSHIP IN ('IS_PART_OF_PACKAGE','IS_PART_OF_IB_EXPLOSION')
372 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
373 AND XOL.STATUS_CODE = 'READY'
374 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
375 AND SEQ_IN_PACKAGE > 0;
376
377 e_NoSvcInPkgFoundException EXCEPTION;
378 e_AddAttributeException EXCEPTION;
379
380 x_Progress VARCHAR2(2000);
381 ErrCode NUMBER;
382 ErrStr VARCHAR2(1996);
383
384 BEGIN
385
386 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
387 itemkey => itemkey,
388 aname => 'ORDER_ID');
389
390 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'LINE_ITEM_ID');
393
394
395 IF c_GetIndServices%ISOPEN THEN
396 CLOSE c_GetIndServices;
397 END IF;
398
399 /* Launch INdependent Service Worlflows for all the independent services of the package */
400
401 OPEN c_GetIndServices(l_LineItemID);
402
403 LOOP
404 FETCH c_GetIndServices INTO l_SrvLineItemID;
405 EXIT WHEN c_GetIndServices%NOTFOUND;
406
407 l_Counter := l_Counter + 1;
408
409 SELECT to_char(XDP_WF_ITEMKEY_S.NEXTVAL) INTO l_tempKey FROM dual;
410 l_tempKey := to_char(l_OrderID) || '-PKG-' || to_char(l_SrvLineItemID) || '-SVC-' || to_char(l_SrvLineItemID) || '-' || l_tempKey;
411
412
413 t_ChildTypes(l_Counter) := 'XDPPROV';
414 t_ChildKeys(l_Counter) := l_tempKey;
415
416 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
417 itemkey => t_ChildKeys(l_Counter),
418 process => 'SERVICE_PROCESS');
419
420 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
421 itemkey => t_ChildKeys(l_Counter),
422 parent_itemtype => itemtype,
423 parent_itemkey => itemkey,
424 parent_context => null);
425
426 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
427 itemkey => t_ChildKeys(l_Counter),
428 aname => 'ORDER_ID',
429 avalue => l_OrderID);
430
431 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
432 itemkey => t_ChildKeys(l_Counter),
433 aname => 'LINE_ITEM_ID',
434 avalue => l_SrvLineItemID);
435
436 END LOOP;
437
438 close c_GetIndServices;
439
440 /* Launch ONE Dependent Serivice Process for any Dependent service in the package */
441
442
443 IF c_GetDepServices%ISOPEN THEN
444 CLOSE c_GetDepServices;
445 END IF;
446
447 OPEN c_GetDepServices(l_LineItemID);
448
449 FETCH c_GetDepServices INTO l_SrvLineItemID;
450
451 IF c_GetDepServices%FOUND THEN
452 l_Counter := l_Counter + 1;
453
454 SELECT to_char(XDP_WF_ITEMKEY_S.NEXTVAL) INTO l_tempKey FROM dual;
455 l_tempKey := to_char(l_OrderID) || '-PKG-' || to_char(l_SrvLineItemID) || '-SVC-' || to_char(l_SrvLineItemID) || '-' || l_tempKey;
456
457
458 t_ChildTypes(l_Counter) := 'XDPPROV';
459 t_ChildKeys(l_Counter) := l_tempKey;
460
461 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
462 itemkey => t_ChildKeys(l_Counter),
463 process => 'DEPENDENT_SERVICE_PROCESS');
464
465 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
466 itemkey => t_ChildKeys(l_Counter),
467 parent_itemtype => itemtype,
468 parent_itemkey => itemkey,
469 parent_context => null);
470
471 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
472 itemkey => t_ChildKeys(l_Counter),
473 aname => 'ORDER_ID',
474 avalue => l_OrderID);
475
476 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
477 itemkey => t_ChildKeys(l_Counter),
478 aname => 'LINE_ITEM_ID',
479 avalue => l_LineItemID);
480
481 XDPCORE.CheckNAddItemAttrNumber (itemtype => t_ChildTypes(l_Counter),
482 itemkey => t_ChildKeys(l_Counter),
483 AttrName => 'CURRENT_SRV_IN_PKG_SEQUENCE',
484 AttrValue => 0,
485 ErrCode => ErrCode,
486 ErrStr => ErrStr);
487
488 IF ErrCode <> 0 THEN
489 x_progress := 'In XDPCORE_WI.LaunchWIServiceForPkgProcess. Error when adding Item Attribute CURRENT_SRV_IN_PKG_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
490 raise e_AddAttributeException;
491 END IF;
492
493 END IF;
494
495 close c_GetDepServices;
496
497 IF l_Counter = 0 THEN
498 x_Progress := 'XDPCORE_PKG.LaunchServiceForPkgProcess. No Services found to be processed for Order: ' || l_OrderID || ' LineItemID: ' || l_LineItemID;
499 RAISE e_NoSvcInPkgFoundException;
500 END IF;
501
502 /* Start the WF Process */
503 FOR i in 1..l_Counter LOOP
504 wf_engine.StartProcess(t_ChildTypes(i),
505 t_ChildKeys(i));
506 END LOOP;
507
508
509
510 EXCEPTION
511 WHEN e_AddAttributeException then
512 IF c_GetIndServices%ISOPEN THEN
513 CLOSE c_GetIndServices;
514 END IF;
515
516 IF c_GetDepServices%ISOPEN THEN
517 CLOSE c_GetDepServices;
518 END IF;
519
520 wf_core.context('XDPCORE_PKG', 'LaunchServiceForPkgProcess', itemtype, itemkey, null,null);
521 raise;
522
523 WHEN e_NoSvcInPkgFoundException THEN
524 IF c_GetIndServices%ISOPEN THEN
525 CLOSE c_GetIndServices;
526 END IF;
527
528 IF c_GetDepServices%ISOPEN THEN
529 CLOSE c_GetDepServices;
530 END IF;
531
532 wf_core.context('XDPCORE_PKG', 'LaunchServiceForPkgProcess', itemtype, itemkey, null,null);
533 raise;
534
535 WHEN others THEN
536 IF c_GetIndServices%ISOPEN THEN
537 CLOSE c_GetIndServices;
538 END IF;
539
540 IF c_GetDepServices%ISOPEN THEN
541 CLOSE c_GetDepServices;
542 END IF;
543
544 x_Progress := 'XDPCORE_PKG.LaunchServiceForPkgProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1500);
545 wf_core.context('XDPCORE_PKG', 'LaunchServiceForPkgProcess', itemtype, itemkey, null,null);
546 raise;
547 END LaunchServiceForPkgProcess;
548
549
550
551 FUNCTION AreAllServicesInPkgDone (itemtype IN VARCHAR2,
552 itemkey IN VARCHAR2) RETURN VARCHAR2
553 IS
554 l_OrderID NUMBER;
555 l_LineItemID NUMBER;
556 l_SvcLineItemID NUMBER;
557 l_PrevSequence NUMBER;
558
559 CURSOR c_GetSrcSeq (LineItemID number, OrderID number, Seq number) is
560 SELECT XOl.LINE_ITEM_ID, XOL.SEQ_IN_PACKAGE
561 FROM XDP_ORDER_LINE_ITEMS XOL, XDP_LINE_RELATIONSHIPS XLR
562 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
563 AND XLR.LINE_RELATIONSHIP = 'IS_PART_OF_PACKAGE'
564 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
565 AND XOL.STATUS_CODE = 'READY'
566 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
567 AND XOL.SEQ_IN_PACKAGE = (
568 SELECT MIN(XOL1.SEQ_IN_PACKAGE)
569 FROM XDP_ORDER_LINE_ITEMS XOL1, XDP_LINE_RELATIONSHIPS XLR1
570 WHERE XLR1.RELATED_LINE_ITEM_ID = LineItemID
571 AND XLR1.LINE_RELATIONSHIP = 'IS_PART_OF_PACKAGE'
572 AND XOL1.LINE_ITEM_ID = XLR1.LINE_ITEM_ID
573 AND XOL1.STATUS_CODE = 'READY'
574 AND XOL1.IS_VIRTUAL_LINE_FLAG = 'Y'
575 AND XOL1.SEQ_IN_PACKAGE > Seq);
576
577 e_NoServicesFoundException EXCEPTION;
578 x_Progress VARCHAR2(2000);
579
580 BEGIN
581
582 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
583 itemkey => itemkey,
584 aname => 'ORDER_ID');
585
586 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
587 itemkey => itemkey,
588 aname => 'LINE_ITEM_ID');
589
590 l_PrevSequence := wf_engine.GetItemAttrNumber(itemtype => itemtype,
591 itemkey => itemkey,
592 aname => 'CURRENT_SRV_IN_PKG_SEQUENCE');
593
594 IF c_GetSrcSeq%ISOPEN THEN
595 CLOSE c_GetSrcSeq;
596 END IF;
597
598
599 OPEN c_GetSrcSeq(l_LineItemID, l_OrderID, l_PrevSequence);
600
601 FETCH c_GetSrcSeq INTO l_SvcLineItemID, l_PrevSequence;
602
603 IF c_GetSrcSeq%NOTFOUND THEN
604 /* No more Services in package to be done */
605 CLOSE c_GetSrcSeq;
606 return 'Y';
607 ELSE
608 /* There are more Services to be done */
609 CLOSE c_GetSrcSeq;
610 return 'N';
611 END IF;
612
613 IF c_GetSrcSeq%ISOPEN THEN
614 CLOSE c_GetSrcSeq;
615 END IF;
616
617 EXCEPTION
618 WHEN e_NoServicesFoundException then
619 IF c_GetSrcSeq%ISOPEN THEN
620 CLOSE c_GetSrcSeq;
621 END IF;
622
623 wf_core.context('XDPCORE_PKG', 'AreAllServicesInPkgDone', itemtype, itemkey, null,null);
624 raise;
625
626 WHEN Others THEN
627 IF c_GetSrcSeq%ISOPEN THEN
628 CLOSE c_GetSrcSeq;
629 END IF;
630
631 x_Progress := 'XDPCORE_PKG.AreAllServicesInPkgDone. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1500);
632 wf_core.context('XDPCORE_PKG', 'AreAllServicesInPkgDone', itemtype, itemkey, null,null);
633 raise;
634 END AreAllServicesInPkgDone;
635
636
637
638 PROCEDURE LaunchServiceInPackage (itemtype IN VARCHAR2,
639 itemkey IN VARCHAR2)
640
641 IS
642
643 l_OrderID NUMBER;
644 l_LineItemID NUMBER;
645 l_SvcLineItemID NUMBER;
646 l_PrevSequence NUMBER;
647 l_CurrentSequence NUMBER;
648 l_Counter NUMBER := 0;
649 l_tempKey VARCHAR2(240);
650
651 CURSOR c_GetSrcSeq (LineItemID number, OrderID number, Seq number) is
652 SELECT XOl.LINE_ITEM_ID, XOL.SEQ_IN_PACKAGE
653 FROM XDP_ORDER_LINE_ITEMS XOL,
654 XDP_LINE_RELATIONSHIPS XLR
655 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
656 AND XLR.LINE_RELATIONSHIP = 'IS_PART_OF_PACKAGE'
657 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
658 AND XOL.STATUS_CODE = 'READY'
659 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
660 AND XOL.SEQ_IN_PACKAGE = (
661 SELECT MIN(XOL1.SEQ_IN_PACKAGE)
662 FROM XDP_ORDER_LINE_ITEMS XOL1,
663 XDP_LINE_RELATIONSHIPS XLR1
664 WHERE XLR1.RELATED_LINE_ITEM_ID = LineItemID
665 AND XLR1.LINE_RELATIONSHIP = 'IS_PART_OF_PACKAGE'
666 AND XOL1.LINE_ITEM_ID = XLR1.LINE_ITEM_ID
667 AND XOL1.STATUS_CODE = 'READY'
668 AND XOL1.IS_VIRTUAL_LINE_FLAG = 'Y'
669 AND XOL1.SEQ_IN_PACKAGE > Seq);
670
671
672 TYPE t_ChildKeyTable IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
673 t_ChildKeys t_ChildKeyTable;
674
675 TYPE t_ChildTypeTable IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
676 t_ChildTypes t_ChildTypeTable;
677
678 e_NoSvcInPkgFoundException EXCEPTION;
679 e_AddAttributeException EXCEPTION;
680 x_Progress VARCHAR2(2000);
681 ErrCode NUMBER;
682 ErrStr VARCHAR2(1996);
683
684
685 BEGIN
686
687 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'ORDER_ID');
690
691 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
692 itemkey => itemkey,
693 aname => 'LINE_ITEM_ID');
694
695 l_PrevSequence := wf_engine.GetItemAttrNumber(itemtype => itemtype,
696 itemkey => itemkey,
697 aname => 'CURRENT_SRV_IN_PKG_SEQUENCE');
698
699 IF c_GetSrcSeq%ISOPEN THEN
700 CLOSE c_GetSrcSeq;
701 END IF;
702
703
704 OPEN c_GetSrcSeq(l_LineItemID, l_OrderID, l_PrevSequence);
705
706 LOOP
707
708 FETCH c_GetSrcSeq into l_SvcLineItemID, l_CurrentSequence;
709 EXIT WHEN c_GetSrcSeq%NOTFOUND;
710
711 l_Counter := l_Counter + 1;
712
713 SELECT to_char(XDP_WF_ITEMKEY_S.NEXTVAL) INTO l_tempKey FROM dual;
714 l_tempKey := to_char(l_OrderID) || '-PKG-' || to_char(l_LineItemID) || '-SVC-' || to_char(l_LineItemID) || '-' || l_tempKey;
715
716 t_ChildTypes(l_Counter) := 'XDPPROV';
717 t_ChildKeys(l_Counter) := l_tempKey;
718
719 wf_engine.CreateProcess(itemtype => t_ChildTypes(l_Counter),
720 itemkey => t_ChildKeys(l_Counter),
721 process => 'SERVICE_PROCESS');
722
723 wf_engine.SetItemParent(itemtype => t_ChildTypes(l_Counter),
724 itemkey => t_ChildKeys(l_Counter),
725 parent_itemtype => itemtype,
726 parent_itemkey => itemkey,
727 parent_context => 'WAITFORFLOW-SERVICE-DEP');
728
729 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
730 itemkey => l_tempKey,
731 aname => 'MASTER_TO_CONTINUE',
732 avalue => 'WAITFORFLOW-SERVICE-DEP');
733
734 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
735 itemkey => t_ChildKeys(l_Counter),
736 aname => 'ORDER_ID',
737 avalue => l_OrderID);
738
739 wf_engine.SetItemAttrNumber(itemtype => t_ChildTypes(l_Counter),
740 itemkey => t_ChildKeys(l_Counter),
741 aname => 'LINE_ITEM_ID',
742 avalue => l_SvcLineItemID);
743
744 END LOOP;
745
746 CLOSE c_GetSrcSeq;
747
748 IF l_Counter = 0 and l_CurrentSequence = 0 THEN
749 x_Progress := 'XDPCORE_PKG.LaunchServiceInPackage. No Services found to be processed for Order: ' || l_OrderID || ' LineItemID (Package): ' || l_LineItemID;
750 RAISE e_NoSvcInPkgFoundException;
751 ELSE
752 XDPCORE.CheckNAddItemAttrNumber (itemtype => itemtype,
753 itemkey => itemkey,
754 AttrName => 'CURRENT_SRV_IN_PKG_SEQUENCE',
755 AttrValue => l_CurrentSequence,
756 ErrCode => ErrCode,
757 ErrStr => ErrStr);
758
759 IF ErrCode <> 0 THEN
760 x_progress := 'In XDPCORE_WI.LaunchWIServiceInPackage. Error when adding Item Attribute CURRENT_SRV_IN_PKG_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
761 raise e_AddAttributeException;
762 END IF;
763
764 /* Launch the Service Process */
765
766 FOR i in 1..l_Counter LOOP
767
768 wf_engine.StartProcess(itemtype => t_ChildTypes(i),
769 itemkey => t_ChildKeys(i));
770
771 END LOOP;
772
773 END IF;
774
775
776 EXCEPTION
777 WHEN e_AddAttributeException then
778 IF c_GetSrcSeq%ISOPEN THEN
779 CLOSE c_GetSrcSeq;
780 END IF;
781
782 wf_core.context('XDPCORE_PKG', 'LaunchServiceInPackage', itemtype, itemkey, null,null);
783 raise;
784
785 WHEN e_NoSvcInPkgFoundException THEN
786 IF c_GetSrcSeq%ISOPEN THEN
787 CLOSE c_GetSrcSeq;
788 END IF;
789
790 wf_core.context('XDPCORE_PKG', 'LaunchServiceInPackage', itemtype, itemkey, null,null);
791 raise;
792
793 WHEN others THEN
794 IF c_GetSrcSeq%ISOPEN THEN
795 CLOSE c_GetSrcSeq;
796 END IF;
797
798 x_Progress := 'XDPCORE_PKG.LaunchServiceInPackage. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1500);
799 wf_core.context('XDPCORE_PKG', 'LaunchServiceInPackage', itemtype, itemkey, null,null);
800 raise;
801 END LaunchServiceInPackage;
802
803 PROCEDURE InitializePackageService (itemtype IN VARCHAR2,
804 itemkey IN VARCHAR2) IS
805
806 l_OrderID NUMBER;
807 l_LineItemID NUMBER;
808 x_Progress VARCHAR2(2000);
809
810 BEGIN
811
812 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'ORDER_ID');
815
816 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'LINE_ITEM_ID');
819
820 IF l_OrderID is not null and l_LineItemID is not null THEN
821
822 UPDATE_PACKAGESERVICE_STATUS (p_line_item_id => l_LineItemID,
823 p_status_code => 'IN PROGRESS',
824 p_itemtype => itemtype,
825 p_itemkey => itemkey);
826 END IF;
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 x_Progress := 'XDPCORE_PKG.InitializePackageService. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1500);
831 wf_core.context('XDPCORE_PKG', 'InitializePackageService', itemtype, itemkey, null,null);
832 raise;
833 END initializePackageService;
834
835
836 FUNCTION ResolveIndDepPkgs (itemtype IN VARCHAR2,
837 itemkey IN VARCHAR2) RETURN VARCHAR2
838 IS
839 l_IndFound number := 0;
840 l_DepFound number := 0;
841 l_LineItemID number;
842
843
844 CURSOR c_GetIndServices (LineItemID NUMBER) IS
845 SELECT 'Y'
846 FROM XDP_LINE_RELATIONSHIPS XLR,
847 XDP_ORDER_LINE_ITEMS XOL
848 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
849 AND XLR.LINE_RELATIONSHIP IN ('IS_PART_OF_PACKAGE','IS_PART_OF_IB_EXPLOSION')
850 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
851 AND XOL.STATUS_CODE = 'READY'
852 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
853 AND (SEQ_IN_PACKAGE IS NULL OR SEQ_IN_PACKAGE = 0);
854
855 CURSOR c_GetDepServices (LineItemID NUMBER) IS
856 SELECT 'Y'
857 FROM XDP_LINE_RELATIONSHIPS XLR, XDP_ORDER_LINE_ITEMS XOL
858 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
859 AND XLR.LINE_RELATIONSHIP IN ('IS_PART_OF_PACKAGE','IS_PART_OF_IB_EXPLOSION')
860 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
861 AND XOL.STATUS_CODE = 'READY'
862 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
863 AND SEQ_IN_PACKAGE > 0;
864
865 x_Progress VARCHAR2(2000);
866
867 BEGIN
868
869 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => ResolveIndDepPkgs.itemtype,
870 itemkey => ResolveIndDepPkgs.itemkey,
871 aname => 'LINE_ITEM_ID');
872
873 FOR c_PkgRec in c_GetIndServices( l_LineItemID ) LOOP
874 l_IndFound := 1;
875 EXIT;
876 END LOOP;
877
878 FOR c_PkgRec in c_GetDepServices( l_LineItemID ) LOOP
879 l_DepFound := 1;
880 EXIT;
881 END LOOP;
882
883 if( l_IndFound = 1 AND l_DepFound = 1 ) THEN
884 RETURN 'BOTH';
885 elsif( l_IndFound = 1) THEN
886 RETURN 'INDEPENDENT';
887 elsif( l_DepFound = 1 ) THEN
888 RETURN 'DEPENDENT';
889 end if;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 x_Progress := 'XDPCORE_PKG.ResolveIndDepPkgs. Unhandled Exception: ' || SUBSTR(SQLERRM, 1, 1500);
894 wf_core.context('XDPCORE_PKG', 'ResolveIndDepPkgs', itemtype, itemkey, null,null);
895 raise;
896
897 END ResolveIndDepPkgs;
898
899 Function LaunchAllIndServices(itemtype IN VARCHAR2,
900 itemkey IN VARCHAR2) return varchar2
901 IS
902 l_OrderID NUMBER;
903 l_LineItemID NUMBER;
904 l_SrvLineItemID NUMBER;
905 l_Counter NUMBER := 0;
906 l_tempKey VARCHAR2(240);
907
908 l_result varchar2(1):= 'N';
909
910 TYPE t_ChildKeyTable IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
911 t_ChildKeys t_ChildKeyTable;
912
913 CURSOR c_GetIndServices (LineItemID NUMBER) IS
914 SELECT XOL.LINE_ITEM_ID
915 FROM XDP_LINE_RELATIONSHIPS XLR,
916 XDP_ORDER_LINE_ITEMS XOL
917 WHERE XLR.RELATED_LINE_ITEM_ID = LineItemID
918 AND XLR.LINE_RELATIONSHIP IN ('IS_PART_OF_PACKAGE','IS_PART_OF_IB_EXPLOSION')
919 AND XOL.LINE_ITEM_ID = XLR.LINE_ITEM_ID
920 AND XOL.STATUS_CODE = 'READY'
921 AND XOL.IS_VIRTUAL_LINE_FLAG = 'Y'
922 AND (SEQ_IN_PACKAGE IS NULL OR SEQ_IN_PACKAGE = 0);
923
924 e_NoSvcInPkgFoundException EXCEPTION;
925 e_AddAttributeException EXCEPTION;
926
927 x_Progress VARCHAR2(2000);
928 ErrCode NUMBER;
929 ErrStr VARCHAR2(1996);
930
931 BEGIN
932
933 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndServices.itemtype,
934 itemkey => LaunchAllIndServices.itemkey,
935 aname => 'ORDER_ID');
936
937
938 l_LineItemID := wf_engine.GetItemAttrNumber(itemtype => LaunchAllIndServices.itemtype,
939 itemkey => LaunchAllIndServices.itemkey,
940 aname => 'LINE_ITEM_ID');
941
942
943 FOR c_PkgRec in c_GetIndServices( l_LineItemID ) LOOP
944 l_result := 'Y';
945 l_SrvLineItemID := c_PkgRec.LINE_ITEM_ID;
946 l_Counter := l_Counter + 1;
947
948 SELECT to_char(XDP_WF_ITEMKEY_S.NEXTVAL) INTO l_tempKey FROM dual;
949 l_tempKey := to_char(l_OrderID) || '-PKG-' || to_char(l_SrvLineItemID) || '-SVC-' || to_char(l_SrvLineItemID) || '-' || l_tempKey;
950
951 t_ChildKeys(l_Counter) := l_tempKey;
952
953 wf_engine.CreateProcess(itemtype => 'XDPPROV',
954 itemkey => t_ChildKeys(l_Counter),
955 process => 'SERVICE_PROCESS');
956
957 wf_engine.SetItemParent(itemtype => 'XDPPROV',
958 itemkey => t_ChildKeys(l_Counter),
959 parent_itemtype => itemtype,
960 parent_itemkey => itemkey,
961 parent_context => 'WAITFORFLOW-SERVICE-IND');
962
963 wf_engine.SetItemAttrText(itemtype => 'XDPPROV',
964 itemkey => t_ChildKeys(l_Counter),
965 aname => 'MASTER_TO_CONTINUE',
966 avalue => 'WAITFORFLOW-SERVICE-IND');
967
968 wf_engine.SetItemAttrNumber(itemtype => 'XDPPROV',
969 itemkey => t_ChildKeys(l_Counter),
970 aname => 'ORDER_ID',
971 avalue => l_OrderID);
972
973 wf_engine.SetItemAttrNumber(itemtype => 'XDPPROV',
974 itemkey => t_ChildKeys(l_Counter),
975 aname => 'LINE_ITEM_ID',
976 avalue => l_SrvLineItemID);
977
978
979
980 END LOOP;
981
982 FOR i in 1..l_Counter LOOP
983 wf_engine.StartProcess('XDPPROV', t_ChildKeys(i));
984 END LOOP;
985
986 return l_result;
987 EXCEPTION
988 WHEN OTHERS THEN
989 x_Progress := 'XDPCORE_PKG.ResolveIndDepPkgs. Unhandled Exception: ' || SUBSTR(SQLERRM, 1 , 1500);
990 wf_core.context('XDPCORE_PKG', 'ResolveIndDepPkgs', itemtype, itemkey, null,null);
991 raise;
992
993 END LaunchAllIndServices;
994
995 PROCEDURE InitializeDepServiceProcess(itemtype IN VARCHAR2,
996 itemkey IN VARCHAR2)
997 IS
998 ErrCode number;
999 ErrStr varchar2(2000);
1000 x_progress varchar2(2000);
1001
1002 e_AddAttributeException exception;
1003
1004 BEGIN
1005 XDPCORE.CheckNAddItemAttrNumber (itemtype =>InitializeDepServiceProcess.itemtype,
1006 itemkey =>InitializeDepServiceProcess.itemkey,
1007 AttrName => 'CURRENT_SRV_IN_PKG_SEQUENCE',
1008 AttrValue => 0,
1009 ErrCode => ErrCode,
1010 ErrStr => ErrStr);
1011
1012 IF ErrCode <> 0 THEN
1013 x_progress := 'In XDPCORE_PKG.InitializeDepServiceProcess. Error when adding Item Attribute CURRENT_SRV_IN_PKG_SEQUENCE. Error: ' || substr(ErrStr,1,1500);
1014 raise e_AddAttributeException;
1015 END IF;
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 x_Progress := 'XDPCORE_PKG.InitializeDepServiceProcess. Unhandled Exception: ' || SUBSTR(SQLERRM, 1 , 1500);
1020 wf_core.context('XDPCORE_PKG', 'InitializeDepServiceProcess', itemtype, itemkey, null,null);
1021 raise;
1022
1023 END InitializeDepServiceProcess;
1024
1025 END XDPCORE_PKG;