[Home] [Help]
PACKAGE BODY: APPS.XNP_WF_STANDARD
Source
1 PACKAGE BODY XNP_WF_STANDARD AS
2 /* $Header: XNPWFACB.pls 120.0 2005/05/30 11:48:18 appldev noship $ */
3
4
5 -- Copies Item attributes defined in workflow to the equivalant
6 -- workitem parameters in xdp_worklist_details.
7
8 Procedure downloadFAParams( itemtype IN VARCHAR2,
9 itemkey IN VARCHAR2,
10 actid IN NUMBER,
11 p_FAInstanceID IN NUMBER );
12
13 --------------------------------------------------------------------
14 -- Called when: The itemtype is initiated
15 -- Description:
16 -- Sets the SFM workitem and order context information
17 -- into the package global variables
18 -- g_ORDER_ID and g_WORKITEM_INSTANCE_ID
19 ------------------------------------------------------------------
20 PROCEDURE SET_SDP_CONTEXT
21 (ITEMTYPE IN VARCHAR2
22 ,ITEMKEY IN VARCHAR2
23 ,ACTID IN NUMBER
24 ,COMMAND IN VARCHAR2
25 ,RESULT OUT NOCOPY VARCHAR2
26 )
27 IS
28 x_progress VARCHAR2(2000) := NULL;
29 BEGIN
30
31 --
32 -- RUN mode - normal process execution
33 --
34 IF (command = 'RUN') THEN
35 result := '';
36 return;
37 END IF;
38
39 --
40 -- SET_CTX mode - set process context information
41 --
42 IF (command = 'SET_CTX') THEN
43
44 -- set the context information
45 XNP_WF_STANDARD.g_WORKITEM_INSTANCE_ID :=
46 wf_engine.GetItemAttrNumber
47 (itemtype => itemtype
48 ,itemkey => itemkey
49 ,aname => 'WORKITEM_INSTANCE_ID'
50 );
51
52 XNP_WF_STANDARD.g_ORDER_ID :=
53 wf_engine.GetItemAttrNumber
54 (itemtype => itemtype
55 ,itemkey => itemkey
56 ,aname => 'ORDER_ID'
57 );
58
59 -- Completion
60 result := '';
61 RETURN;
62
63 ELSIF (command = 'SET_ORD') THEN
64
65 -- set the context information
66
67 XNP_WF_STANDARD.g_ORDER_ID :=
68 wf_engine.GetItemAttrNumber
69 (itemtype => itemtype
70 ,itemkey => itemkey
71 ,aname => 'ORDER_ID'
72 );
73
74 -- Completion
75 result := '';
76 RETURN;
77
78
79 ELSIF (command = 'SET_WI') THEN
80
81 -- set the context information
82
83 XNP_WF_STANDARD.g_WORKITEM_INSTANCE_ID :=
84 wf_engine.GetItemAttrNumber
85 (itemtype => itemtype
86 ,itemkey => itemkey
87 ,aname => 'WORKITEM_INSTANCE_ID'
88 );
89
90 -- Completion
91 result := '';
92 RETURN;
93
94 ELSIF (command = 'SET_FA') THEN
95
96 -- set the context information
97
98 XNP_WF_STANDARD.g_FA_INSTANCE_ID :=
99 wf_engine.GetItemAttrNumber
100 (itemtype => itemtype
101 ,itemkey => itemkey
102 ,aname => 'FA_INSTANCE_ID'
103 );
104
105 -- Completion
106 result := '';
107 RETURN;
108 ELSIF (command = 'SET_ORD_WI') THEN
109
110 -- set the context information
111
112 XNP_WF_STANDARD.g_ORDER_ID :=
113 wf_engine.GetItemAttrNumber
114 (itemtype => itemtype
115 ,itemkey => itemkey
116 ,aname => 'ORDER_ID'
117 );
118
119 XNP_WF_STANDARD.g_WORKITEM_INSTANCE_ID :=
120 wf_engine.GetItemAttrNumber
121 (itemtype => itemtype
122 ,itemkey => itemkey
123 ,aname => 'WORKITEM_INSTANCE_ID'
124 );
125
126 -- Completion
127 result := '';
128 RETURN;
129 ELSIF (command = 'SET_ORD_FA') THEN
130
131 -- set the context information
132
133 XNP_WF_STANDARD.g_ORDER_ID :=
134 wf_engine.GetItemAttrNumber
135 (itemtype => itemtype
136 ,itemkey => itemkey
137 ,aname => 'ORDER_ID'
138 );
139
140 XNP_WF_STANDARD.g_FA_INSTANCE_ID :=
141 wf_engine.GetItemAttrNumber
142 (itemtype => itemtype
143 ,itemkey => itemkey
144 ,aname => 'FA_INSTANCE_ID'
145 );
146
147 -- Completion
148 result := '';
149 RETURN;
150 ELSIF (command = 'SET_WI_FA') THEN
151
152 -- set the context information
153
154 XNP_WF_STANDARD.g_WORKITEM_INSTANCE_ID :=
155 wf_engine.GetItemAttrNumber
156 (itemtype => itemtype
157 ,itemkey => itemkey
158 ,aname => 'WORKITEM_INSTANCE_ID'
159 );
160
161 XNP_WF_STANDARD.g_FA_INSTANCE_ID :=
162 wf_engine.GetItemAttrNumber
163 (itemtype => itemtype
164 ,itemkey => itemkey
165 ,aname => 'FA_INSTANCE_ID'
166 );
167
168 -- Completion
169 result := '';
170 RETURN;
171 ELSIF (command = 'SET_ORD_WI_FA') THEN
172
173 -- set the context information
174
175 XNP_WF_STANDARD.g_ORDER_ID :=
176 wf_engine.GetItemAttrNumber
177 (itemtype => itemtype
178 ,itemkey => itemkey
179 ,aname => 'ORDER_ID'
180 );
181
182 XNP_WF_STANDARD.g_WORKITEM_INSTANCE_ID :=
183 wf_engine.GetItemAttrNumber
184 (itemtype => itemtype
185 ,itemkey => itemkey
186 ,aname => 'WORKITEM_INSTANCE_ID'
187 );
188
189 XNP_WF_STANDARD.g_FA_INSTANCE_ID :=
190 wf_engine.GetItemAttrNumber
191 (itemtype => itemtype
192 ,itemkey => itemkey
193 ,aname => 'FA_INSTANCE_ID'
194 );
195
196 -- Completion
197 result := '';
198 RETURN;
199
200 END IF;
201 --
202 -- TEST_CTX mode
203 --
204 IF (command = 'TEST_CTX' ) THEN
205
206 -- set the context information
207
208 XNP_WF_STANDARD.g_WORKITEM_INSTANCE_ID :=
209 wf_engine.GetItemAttrNumber
210 (itemtype => itemtype
211 ,itemkey => itemkey
212 ,aname => 'WORKITEM_INSTANCE_ID'
213 );
214
215 XNP_WF_STANDARD.g_ORDER_ID :=
216 wf_engine.GetItemAttrNumber
217 (itemtype => itemtype
218 ,itemkey => itemkey
219 ,aname => 'ORDER_ID'
220 );
221
222 result := 'TRUE';
223 return;
224 END IF;
225
226 -- For other execution modes: return null
227 result := '';
228 return;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232
233 ------------------------------------------------------------------
234 -- Record this function call in the error
235 -- system in case of an exception
236 ------------------------------------------------------------------
237 fnd_message.set_name('XNP','STD_ERROR');
238 fnd_message.set_token(
239 'ERROR_LOCN','XNP_WF_STANDARD.SET_SDP_CONTEXT');
240 fnd_message.set_token('ERROR_TEXT',
241 ':'||to_char(SQLCODE)||':'||SQLERRM);
242 x_progress := fnd_message.get;
243 wf_core.context(
244 'XNP_WF_STANDARD'
245 , 'SET_SDP_CONTEXT'
246 , itemtype
247 , itemkey
248 , to_char(actid)
249 , command
250 , x_progress);
251
252 RAISE;
253
254 END SET_SDP_CONTEXT;
255
256 --------------------------------------------------------------------
257 -- Called when: there is a Create Ported Number
258 -- request from NRC
259 -- Description:
260 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
261 -- Calls XNP_STANDARD.SMS_CREATE_PORTED_NUMBER
262 ------------------------------------------------------------------
263 PROCEDURE SMS_CREATE_PORTED_NUMBER
264 (ITEMTYPE IN VARCHAR2
265 ,ITEMKEY IN VARCHAR2
266 ,ACTID IN NUMBER
267 ,FUNCMODE IN VARCHAR2
268 ,RESULTOUT OUT NOCOPY VARCHAR2
269 )
270 IS
271
272 l_ORDER_ID NUMBER;
273 l_LINEITEM_ID NUMBER;
274 l_WORKITEM_INSTANCE_ID NUMBER;
275 l_FA_INSTANCE_ID NUMBER;
276 l_error_code NUMBER := 0;
277 l_error_message VARCHAR2(2000);
278 x_progress VARCHAR2(2000) := NULL;
279 e_SMS_CREATE_PORTED_NUMBER EXCEPTION;
280
281 BEGIN
282
283 --
284 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
285 --
286 SET_SDP_CONTEXT
287 (ITEMTYPE
288 ,ITEMKEY
289 ,ACTID
290 ,'SET_ORD_WI'
291 ,RESULTOUT
292 );
293
294 --
295 -- RUN mode - normal process execution
296 --
297 IF (funcmode = 'RUN') THEN
298
299 -- The run code
300
301 l_ORDER_ID := g_ORDER_ID;
302 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
303
304 XNP_STANDARD.SMS_CREATE_PORTED_NUMBER
305 (l_ORDER_ID ,
306 l_LINEITEM_ID ,
307 l_WORKITEM_INSTANCE_ID,
308 l_FA_INSTANCE_ID,
309 l_error_code,
310 l_error_message
311 );
312
313 IF l_error_code <> 0 THEN
314 raise e_SMS_CREATE_PORTED_NUMBER;
315 END IF;
316 -- Completion
317 resultout := 'COMPLETE';
318 RETURN;
319 END IF;
320 --
321 -- CANCEL mode
322 --
323 -- This is in the event that the activity must be undone.
324 --
325 IF (funcmode = 'CANCEL' ) THEN
326 -- The cancel code
327
328 null;
329 -- no result needed
330 resultout := 'COMPLETE';
331 return;
332 END IF;
333
334 -- For other execution modes: return null
335
336 resultout := '';
337 return;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341
342 ------------------------------------------------------------------
343 -- Record this function call in the error
344 -- system in case of an exception
345 ------------------------------------------------------------------
346 IF (l_error_code <> 0) THEN
347 x_progress := to_char(l_error_code)||':'||l_error_message;
348 ELSE
349 x_progress := to_char(SQLCODE)||':'||SQLERRM;
350 END IF;
351
352 XNP_UTILS.NOTIFY_ERROR
353 (P_PKG_NAME => 'XNP_WF_STANDARD'
354 ,P_PROC_NAME => 'SMS_CREATE_PORTED_NUMBER'
355 ,P_MSG_NAME => 'SMS_CREATE_PORTED_NUMBER_ERR'
356 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
357 ,P_TOK1 => 'ERROR_TEXT'
358 ,P_VAL1 => x_progress
359 );
360
361 RAISE;
362
363 END SMS_CREATE_PORTED_NUMBER;
364
365 --------------------------------------------------------------------
366 -- Called when: there is a Delete Ported Number request
367 -- from NRC
368 -- Description:
369 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
370 -- Calls XNP_STANDARD.SMS_DELETE_PORTED_NUMBER
371 ------------------------------------------------------------------
372 PROCEDURE SMS_DELETE_PORTED_NUMBER
373 (ITEMTYPE IN VARCHAR2
374 ,ITEMKEY IN VARCHAR2
375 ,ACTID IN NUMBER
376 ,FUNCMODE IN VARCHAR2
377 ,RESULTOUT OUT NOCOPY VARCHAR2
378 )
379 IS
380 l_WORKITEM_INSTANCE_ID NUMBER;
381 l_error_code NUMBER := 0;
382 l_error_message VARCHAR2(2000);
383 x_progress VARCHAR2(2000);
384 e_SMS_DELETE_PORTED_NUMBER EXCEPTION;
385
386 BEGIN
387
388 --
389 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
390 --
391 SET_SDP_CONTEXT
392 (ITEMTYPE
393 ,ITEMKEY
394 ,ACTID
395 ,'SET_WI'
396 ,RESULTOUT
397 );
398
399 --
400 -- RUN mode - normal process execution
401 --
402 IF (funcmode = 'RUN') THEN
403
404 -- The run code
405
406 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
407
408 XNP_STANDARD.SMS_DELETE_PORTED_NUMBER
409 (l_WORKITEM_INSTANCE_ID
410 ,l_error_code
411 ,l_error_message
412 );
413
414 IF l_error_code <> 0 THEN
415 raise e_SMS_DELETE_PORTED_NUMBER;
416 END IF;
417 -- Completion
418 resultout := 'COMPLETE';
419 RETURN;
420 END IF;
421 --
422 -- CANCEL mode
423 --
424 -- This is in the event that the activity must be undone.
425 --
426 IF (funcmode = 'CANCEL' ) THEN
427 -- The cancel code
428
429 null;
430 -- no result needed
431 resultout := 'COMPLETE';
432 return;
433 END IF;
434
435 -- For other execution modes: return null
436
437 resultout := '';
438 return;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 ------------------------------------------------------------------
443 -- Record this function call in the error
444 -- system in case of an exception
445 ------------------------------------------------------------------
446 IF (l_error_code <> 0) THEN
447 x_progress := to_char(l_error_code)||':'||l_error_message;
448 ELSE
449 x_progress := to_char(SQLCODE)||':'||SQLERRM;
450 END IF;
451
452 XNP_UTILS.NOTIFY_ERROR
453 (P_PKG_NAME => 'XNP_WF_STANDARD'
454 ,P_PROC_NAME => 'SMS_DELETE_PORTED_NUMBER'
455 ,P_MSG_NAME => 'SMS_DELETE_PORTED_NUMBER_ERR'
456 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
457 ,P_TOK1 => 'ERROR_TEXT'
458 ,P_VAL1 => x_progress
459 );
460
461 RAISE;
462
463 END SMS_DELETE_PORTED_NUMBER;
464
465 --------------------------------------------------------------------
466 -- Called when: response to OMS's portin req to peer +B1
467 -- Description:
468 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
469 -- Calls XNP_STANDARD.SOA_UPDATE_CUTOFF_DATE
470 ------------------------------------------------------------------
471 PROCEDURE SOA_UPDATE_CUTOFF_DATE
472 (ITEMTYPE IN VARCHAR2
473 ,ITEMKEY IN VARCHAR2
474 ,ACTID IN NUMBER
475 ,FUNCMODE IN VARCHAR2
476 ,RESULTOUT OUT NOCOPY VARCHAR2
477 )
478 IS
479 l_ORDER_ID NUMBER;
480 l_LINEITEM_ID NUMBER;
481 l_WORKITEM_INSTANCE_ID NUMBER;
482 l_FA_INSTANCE_ID NUMBER;
483 l_error_code NUMBER := 0;
484 l_error_message VARCHAR2(2000);
485 l_STATUS_CHANGE_CAUSE_CODE VARCHAR2(512);
486 l_NEW_STATUS_TYPE_CODE VARCHAR2(80);
487 l_CUR_STATUS_TYPE_CODE VARCHAR2(80);
488 x_progress VARCHAR2(2000);
489 e_SOA_UPDATE_CUTOFF_DATE EXCEPTION;
490
491 BEGIN
492
493 --
494 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
495 --
496 SET_SDP_CONTEXT
497 (ITEMTYPE
498 ,ITEMKEY
499 ,ACTID
500 ,'SET_ORD_WI'
501 ,RESULTOUT
502 );
503
504 --
505 -- RUN mode - normal process execution
506 --
507 IF (funcmode = 'RUN') THEN
508
509 -- The run code
510
511 l_ORDER_ID := g_ORDER_ID;
512 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
513
514 l_CUR_STATUS_TYPE_CODE :=
515 wf_engine.GetActivityAttrText (itemtype => itemtype,
516 itemkey => itemkey,
517 actid => actid,
518 aname => 'CUR_STATUS_TYPE_CODE');
519
520 XNP_STANDARD.SOA_UPDATE_CUTOFF_DATE
521 (l_ORDER_ID ,
522 L_LINEITEM_ID,
523 l_WORKITEM_INSTANCE_ID,
524 l_FA_INSTANCE_ID,
525 l_CUR_STATUS_TYPE_CODE,
526 l_error_code,
527 l_error_message
528 );
529
530 IF l_error_code <> 0 THEN
531 raise e_SOA_UPDATE_CUTOFF_DATE;
532 END IF;
533 -- Completion
534 resultout := 'COMPLETE';
535 RETURN;
536 END IF;
537 --
538 -- CANCEL mode
539 --
540 -- This is in the event that the activity must be undone.
541 --
542 IF (funcmode = 'CANCEL' ) THEN
543 -- The cancel code
544
545 null;
546 -- no result needed
547 resultout := 'COMPLETE';
548 return;
549 END IF;
550
551 -- For other execution modes: return null
552
553 resultout := '';
554 return;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 ------------------------------------------------------------------
559 -- Record this function call in the error
560 -- system in case of an exception
561 ------------------------------------------------------------------
562
563 IF (l_error_code <> 0) THEN
564 x_progress := to_char(l_error_code)||':'||l_error_message;
565 ELSE
566 x_progress := to_char(SQLCODE)||':'||SQLERRM;
567 END IF;
568
569 XNP_UTILS.NOTIFY_ERROR
570 (P_PKG_NAME => 'XNP_WF_STANDARD'
571 ,P_PROC_NAME => 'SOA_UPDATE_CUTOFF_DATE'
572 ,P_MSG_NAME => 'SOA_UPDATE_CUTOFF_DATE_ERR'
573 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
574 ,P_TOK1 => 'ERROR_TEXT'
575 ,P_VAL1 => x_progress
576 );
577
578 RAISE;
579 END SOA_UPDATE_CUTOFF_DATE;
580
581
582 ------------------------------------------------------------------
583 -- Called when: When there is a Porting Order from OMS
584 -- and the
585 -- Description:
586 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
587 -- Calls XNP_STANDARD.SOA_CREATE_PORTING_ORDER
588 ------------------------------------------------------------------
589 PROCEDURE SOA_CREATE_PORTING_ORDER
590 (ITEMTYPE IN VARCHAR2
591 ,ITEMKEY IN VARCHAR2
592 ,ACTID IN NUMBER
593 ,FUNCMODE IN VARCHAR2
594 ,RESULTOUT OUT NOCOPY VARCHAR2
595 )
596 IS
597 l_ORDER_ID NUMBER;
598 l_LINEITEM_ID NUMBER;
599 l_WORKITEM_INSTANCE_ID NUMBER;
600 l_FA_INSTANCE_ID NUMBER;
601 l_error_code NUMBER := 0;
602 l_error_message VARCHAR2(2000);
603 l_SP_ROLE VARCHAR2(80);
604 x_progress VARCHAR2(2000);
605 e_SOA_CREATE_PORTING_ORDER EXCEPTION;
606
607 BEGIN
608 --
609 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
610 --
611 SET_SDP_CONTEXT
612 (ITEMTYPE
613 ,ITEMKEY
614 ,ACTID
615 ,'SET_ORD_WI'
616 ,RESULTOUT
617 );
618
619 --
620 -- RUN mode - normal process execution
621 --
622 IF (funcmode = 'RUN') THEN
623
624 -- The run code
625
626 l_ORDER_ID := g_ORDER_ID;
627 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
628
629 -- Get the context this workflow is operating in
630 -- i.e. DONOR or RECIPIENT
631 l_SP_ROLE :=
632 wf_engine.GetActivityAttrText (itemtype => itemtype,
633 itemkey => itemkey,
634 actid => actid,
635 aname => 'SP_ROLE');
636 IF (l_sp_role IS NULL) THEN
637 raise e_SOA_CREATE_PORTING_ORDER;
638 END IF;
639
640 XNP_STANDARD.SOA_CREATE_PORTING_ORDER
641 (l_ORDER_ID,
642 l_LINEITEM_ID,
643 l_WORKITEM_INSTANCE_ID,
644 l_FA_INSTANCE_ID,
645 l_SP_ROLE,
646 l_error_code,
647 l_error_message
648 );
649
650 IF l_error_code <> 0 THEN
651 raise e_SOA_CREATE_PORTING_ORDER;
652 END IF;
653 -- Completion
654 resultout := 'COMPLETE';
655 RETURN;
656 END IF;
657 --
658 -- CANCEL mode
659 --
660 -- This is in the event that the activity must be undone.
661 --
662 IF (funcmode = 'CANCEL' ) THEN
663 -- The cancel code
664
665 null;
666 -- no result needed
667 resultout := 'COMPLETE';
668 return;
669 END IF;
670
671 -- For other execution modes: return null
672
673 resultout := '';
674 return;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 ------------------------------------------------------------------
679 -- Record this function call in the error
680 -- system in case of an exception
681 ------------------------------------------------------------------
682
683
684 IF (l_error_code <> 0) THEN
685 x_progress := to_char(l_error_code)||':'||l_error_message;
686 ELSE
687 x_progress := to_char(SQLCODE)||':'||SQLERRM;
688 END IF;
689
690 XNP_UTILS.NOTIFY_ERROR
691 (P_PKG_NAME => 'XNP_WF_STANDARD'
692 ,P_PROC_NAME => 'SOA_CREATE_PORTING_ORDER'
693 ,P_MSG_NAME => 'SOA_CREATE_PORTING_ORDER_ERR'
694 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
695 ,P_TOK1 => 'ERROR_TEXT'
696 ,P_VAL1 => x_progress
697 );
698
699 RAISE;
700 END SOA_CREATE_PORTING_ORDER;
701
702
703 ------------------------------------------------------------------
704 -- Called when: need to update the SV status according
705 -- to the activity parameter SV_STATUS
706 -- Description:
707 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
708 -- Calls XNP_STANDARD.SOA_UPDATE_SV_STATUS
709 ------------------------------------------------------------------
710 PROCEDURE SOA_UPDATE_SV_STATUS
711 (ITEMTYPE IN VARCHAR2
712 ,ITEMKEY IN VARCHAR2
713 ,ACTID IN NUMBER
714 ,FUNCMODE IN VARCHAR2
715 ,RESULTOUT OUT NOCOPY VARCHAR2
716 )
717 IS
718 l_ORDER_ID NUMBER;
719 l_LINEITEM_ID NUMBER;
720 l_WORKITEM_INSTANCE_ID NUMBER;
721 l_FA_INSTANCE_ID NUMBER;
722 l_NEW_STATUS_TYPE_CODE VARCHAR2(40);
723 --l_CUR_STATUS_TYPE_CODE VARCHAR2(40);
724 l_STATUS_CHANGE_CAUSE_CODE VARCHAR2(40);
725 l_error_code NUMBER := 0;
726 l_error_message VARCHAR2(2000);
727 x_progress VARCHAR2(2000);
728 e_SOA_UPDATE_SV_STATUS EXCEPTION;
729
730 BEGIN
731
732 --
733 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
734 --
735
736 SET_SDP_CONTEXT
737 (ITEMTYPE
738 ,ITEMKEY
739 ,ACTID
740 ,'SET_ORD_WI'
741 ,RESULTOUT
742 );
743
744 --
745 -- RUN mode - normal process execution
746 --
747 IF (funcmode = 'RUN') THEN
748
749 -- The run code
750
751 l_ORDER_ID := g_ORDER_ID;
752 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
753
754 l_NEW_STATUS_TYPE_CODE :=
755 wf_engine.GetActivityAttrText (itemtype => itemtype,
756 itemkey => itemkey,
757 actid => actid,
758 aname => 'NEW_STATUS_TYPE_CODE');
759
760 l_STATUS_CHANGE_CAUSE_CODE :=
761 wf_engine.GetActivityAttrText (itemtype => itemtype,
762 itemkey => itemkey,
763 actid => actid,
764 aname => 'STATUS_CHANGE_CAUSE_CODE');
765
766 XNP_STANDARD.SOA_UPDATE_SV_STATUS
767 (p_ORDER_ID => l_ORDER_ID ,
768 p_LINEITEM_ID => l_LINEITEM_ID,
769 p_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID,
770 p_FA_INSTANCE_ID => l_FA_INSTANCE_ID,
771 p_NEW_STATUS_TYPE_CODE => l_NEW_STATUS_TYPE_CODE,
772 p_STATUS_CHANGE_CAUSE_CODE => l_STATUS_CHANGE_CAUSE_CODE,
773 x_ERROR_CODE => l_error_code,
774 x_ERROR_MESSAGE => l_error_message
775 );
776
777 IF l_error_code <> 0 THEN
778 raise e_SOA_UPDATE_SV_STATUS;
779 END IF;
780 -- Completion
781 resultout := 'COMPLETE';
782 RETURN;
783 END IF;
784 --
785 -- CANCEL mode
786 --
787 -- This is in the event that the activity must be undone.
788 --
789 IF (funcmode = 'CANCEL' ) THEN
790 -- The cancel code
791
792 null;
793 -- no result needed
794 resultout := 'COMPLETE';
795 return;
796 END IF;
797
798 -- For other execution modes: return null
799
800 resultout := '';
801 return;
802
803 EXCEPTION
804 WHEN OTHERS THEN
805 ------------------------------------------------------------------
806 -- Record this function call in the error
807 -- system in case of an exception
808 ------------------------------------------------------------------
809
810
811 IF (l_error_code <> 0) THEN
812 x_progress := to_char(l_error_code)||':'||l_error_message;
813 ELSE
814 x_progress := to_char(SQLCODE)||':'||SQLERRM;
815 END IF;
816
817 XNP_UTILS.NOTIFY_ERROR
818 (P_PKG_NAME => 'XNP_WF_STANDARD'
819 ,P_PROC_NAME => 'SOA_UPDATE_SV_STATUS'
820 ,P_MSG_NAME => 'SOA_UPDATE_SV_STATUS_ERR'
821 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
822 ,P_TOK1 => 'ERROR_TEXT'
823 ,P_VAL1 => x_progress
824 );
825
826 RAISE;
827 END SOA_UPDATE_SV_STATUS;
828
829
830 --------------------------------------------------------------------
831 -- Called when: donor needs to check if initial donor
832 -- for the TN range
833 -- Description:
834 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
835 -- Calls XNP_STANDARD.SOA_CHECK_IF_INITIAL_DONOR
836 -- Sets the RESULTOUT based on the result
837 ------------------------------------------------------------------
838 PROCEDURE DETERMINE_SP_ROLE
839 (ITEMTYPE IN VARCHAR2
840 ,ITEMKEY IN VARCHAR2
841 ,ACTID IN NUMBER
842 ,FUNCMODE IN VARCHAR2
843 ,RESULTOUT OUT NOCOPY VARCHAR2
844 )
845 IS
846 l_WORKITEM_INSTANCE_ID NUMBER;
847 l_error_code NUMBER := 0;
848 l_error_message VARCHAR2(2000);
849 l_check_status VARCHAR2(1);
850 l_SP_ROLE VARCHAR2(80);
851 x_progress VARCHAR2(2000);
852 e_DETERMINE_SP_ROLE EXCEPTION;
853 BEGIN
854
855 --
856 -- RUN mode - normal process execution
857 --
858 IF (funcmode = 'RUN') THEN
859
860 -- The run code
861
862 SET_SDP_CONTEXT
863 (ITEMTYPE
864 ,ITEMKEY
865 ,ACTID
866 ,'SET_WI'
867 ,RESULTOUT
868 );
869
870 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
871
872 -- Check to see if initial donor
873
874 XNP_STANDARD.DETERMINE_SP_ROLE
875 (l_WORKITEM_INSTANCE_ID
876 ,l_SP_ROLE
877 ,l_error_code
878 ,l_error_message
879 );
880
881 IF l_error_code <> 0
882 THEN
883 raise e_DETERMINE_SP_ROLE;
884 END IF;
885
886 -- Completion: with sp role
887 -- role could be DONOR, ORIG_DONOR or RECIPIENT
888 resultout := 'COMPLETE:'||l_SP_ROLE;
889 RETURN;
890 END IF;
891 --
892 -- CANCEL mode
893 --
894 -- This is in the event that the activity must be undone.
895 --
896 IF (funcmode = 'CANCEL' ) THEN
897 -- The cancel code
898
899 null;
900 -- no result needed
901 resultout := 'COMPLETE';
902 return;
903 END IF;
904
905 -- For other execution modes: return null
906
907 resultout := '';
908 return;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 ------------------------------------------------------------------
913 -- Record this function call in the error
914 -- system in case of an exception
915 ------------------------------------------------------------------
916
917
918 IF (l_error_code <> 0) THEN
919 x_progress := to_char(l_error_code)||':'||l_error_message;
920 ELSE
921 x_progress := to_char(SQLCODE)||':'||SQLERRM;
922 END IF;
923
924 XNP_UTILS.NOTIFY_ERROR
925 (P_PKG_NAME => 'XNP_WF_STANDARD'
926 ,P_PROC_NAME => 'DETERMINE_SP_ROLE'
927 ,P_MSG_NAME => 'DETERMINE_SP_ROLE_ERR'
928 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
929 ,P_TOK1 => 'ERROR_TEXT'
930 ,P_VAL1 => x_progress
931 );
932
933 RAISE;
934 END DETERMINE_SP_ROLE;
935
936
937 PROCEDURE SUBSCRIBE_FOR_EVENT
938 (ITEMTYPE IN VARCHAR2
939 ,ITEMKEY IN VARCHAR2
940 ,ACTID IN NUMBER
941 ,FUNCMODE IN VARCHAR2
942 ,RESULTOUT OUT NOCOPY VARCHAR2
943 )
944 IS
945
946 l_WORKITEM_INSTANCE_ID NUMBER;
947 l_ORDER_ID NUMBER;
948 --l_FA_INSTANCE_ID NUMBER := 0;
949 l_FA_INSTANCE_ID NUMBER := NULL;
950 l_error_code NUMBER := 0;
951 l_REFERENCE_ID NUMBER;
952 l_error_message VARCHAR2(2000);
953 l_MESSAGE_TYPE VARCHAR2(80);
954 l_CALLBACK_REF_ID VARCHAR2(1024);
955 l_activity_name VARCHAR2(80);
956 l_tmp_callback_ref_id VARCHAR2(2000) := NULL;
957 l_process_reference VARCHAR2(2000);
958 x_progress VARCHAR2(2000);
959 e_SUBSCRIBE_FOR_EVENT EXCEPTION;
960
961 BEGIN
962
963 --
964 -- RUN mode - normal process execution
965 --
966 IF (funcmode = 'RUN') THEN
967
968 -- The run code
969
970 --
971 -- THE ABOVE LINE IS WRAPPED INTO A EXCEPTION BLOCK AS SHOW BELOW IN CASE
972 -- THE CALLER WORKITEM TYPE DOES NOT HAVE TO HAVE ITEM ATTRIBUTE WORKITEM_INSTANCE_ID
973 --
974
975 BEGIN
976
977
978 SET_SDP_CONTEXT
979 (ITEMTYPE
980 ,ITEMKEY
981 ,ACTID
982 ,'SET_ORD_WI'
983 ,RESULTOUT
984 );
985
986 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
987 l_ORDER_ID := g_ORDER_ID;
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 l_WORKITEM_INSTANCE_ID := NULL;
992 wf_core.clear;
993 END;
994
995 BEGIN
996
997 SET_SDP_CONTEXT
998 (ITEMTYPE
999 ,ITEMKEY
1000 ,ACTID
1001 ,'SET_FA'
1002 ,RESULTOUT
1003 );
1004
1005 l_FA_INSTANCE_ID := g_FA_INSTANCE_ID;
1006
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 l_FA_INSTANCE_ID := NULL;
1010 wf_core.clear;
1011 END;
1012
1013 -- Get the callback reference id
1014
1015 XNP_UTILS.CHECK_TO_GET_REF_ID
1016 (p_itemtype => itemtype
1017 ,p_itemkey => itemkey
1018 ,p_actid => actid
1019 ,p_workitem_instance_id => l_workitem_instance_id
1020 ,x_reference_id => l_callback_ref_id
1021 );
1022
1023 -- If the Reference_ID is null make it -1
1024
1025 if l_callback_ref_id is null then
1026 l_callback_ref_id := -1;
1027 end if;
1028
1029 l_MESSAGE_TYPE :=
1030 wf_engine.GetActivityAttrText
1031 (itemtype => itemtype
1032 ,itemkey => itemkey
1033 ,actid => actid
1034 ,aname => 'EVENT_TYPE'
1035 );
1036
1037 l_ACTIVITY_NAME := wf_engine.GETACTIVITYLABEL(actid);
1038 l_PROCESS_REFERENCE :=
1039 itemtype||':'||itemkey||':'||l_activity_name;
1040
1041 ------------------------------------------------------------------
1042 -- Subscribe for this event
1043 ------------------------------------------------------------------
1044 XNP_STANDARD.SUBSCRIBE_FOR_EVENT
1045 (p_MESSAGE_TYPE=>l_MESSAGE_TYPE
1046 ,p_WORKITEM_INSTANCE_ID=>l_WORKITEM_INSTANCE_ID
1047 ,p_CALLBACK_REF_ID=>l_CALLBACK_REF_ID
1048 ,p_PROCESS_REFERENCE=>l_PROCESS_REFERENCE
1049 ,p_ORDER_ID=>l_ORDER_ID
1050 ,p_FA_INSTANCE_ID=>l_FA_INSTANCE_ID
1051 ,x_ERROR_CODE=>l_ERROR_CODE
1052 ,x_ERROR_MESSAGE=>l_ERROR_MESSAGE
1053 );
1054
1055 IF l_error_code <> 0
1056 THEN
1057 raise e_SUBSCRIBE_FOR_EVENT;
1058 END IF;
1059 -- Completion
1060 -- Once Publish is ready
1061 resultout := 'NOTIFIED';
1062 RETURN;
1063 END IF;
1064 --
1065 -- CANCEL mode
1066 --
1067 -- This is in the event that the activity must be undone.
1068 --
1069 IF (funcmode = 'CANCEL' ) THEN
1070 -- The cancel code
1071
1072 null;
1073 -- no result needed
1074 resultout := 'COMPLETE';
1075 return;
1076 END IF;
1077
1078 -- For other execution modes: return null
1079
1080 resultout := '';
1081 return;
1082
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 ------------------------------------------------------------------
1086 -- Record this function call in the error
1087 -- system in case of an exception
1088 ------------------------------------------------------------------
1089
1090
1091 IF (l_error_code <> 0) THEN
1092 x_progress := to_char(l_error_code)||':'||l_error_message;
1093 ELSE
1094 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1095 END IF;
1096
1097 XNP_UTILS.NOTIFY_ERROR
1098 (P_PKG_NAME => 'XNP_WF_STANDARD'
1099 ,P_PROC_NAME => 'SUBSCRIBE_FOR_EVENT'
1100 ,P_MSG_NAME => 'SUBSCRIBE_FOR_EVENT_ERR'
1101 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1102 ,P_TOK1 => 'ERROR_TEXT'
1103 ,P_VAL1 => x_progress
1104 );
1105
1106 RAISE;
1107 END SUBSCRIBE_FOR_EVENT;
1108
1109
1110 ------------------------------------------------------------------
1111 -- Registers for Acks corr to sent message
1112 -- Calls XNP_STANDARD SUBSCRIBE_FOR_ACKS
1113 ------------------------------------------------------------------
1114 PROCEDURE SUBSCRIBE_FOR_ACKS
1115 (ITEMTYPE IN VARCHAR2
1116 ,ITEMKEY IN VARCHAR2
1117 ,ACTID IN NUMBER
1118 ,FUNCMODE IN VARCHAR2
1119 ,RESULTOUT OUT NOCOPY VARCHAR2
1120 )
1121 IS
1122 l_WORKITEM_INSTANCE_ID NUMBER;
1123 l_ORDER_ID NUMBER;
1124 l_FA_INSTANCE_ID NUMBER;
1125 l_REFERENCE_ID NUMBER;
1126 l_EVENT_TYPE VARCHAR2(80);
1127 l_tmp NUMBER := 0;
1128 l_CALLBACK_REF_ID VARCHAR2(1024) := NULL;
1129 l_activity_name VARCHAR2(80);
1130 l_process_reference VARCHAR2(2000);
1131 x_progress VARCHAR2(2000);
1132 l_error_code NUMBER := 0;
1133 l_error_message VARCHAR2(2000);
1134 e_SUBSCRIBE_FOR_ACKS EXCEPTION;
1135
1136 BEGIN
1137
1138 --
1139 -- RUN mode - normal process execution
1140 --
1141 IF (funcmode = 'RUN') THEN
1142
1143 -- The run code
1144
1145
1146 SET_SDP_CONTEXT
1147 (ITEMTYPE
1148 ,ITEMKEY
1149 ,ACTID
1150 ,'SET_ORD_WI'
1151 ,RESULTOUT
1152 );
1153
1154 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
1155 l_ORDER_ID := g_ORDER_ID;
1156
1157 BEGIN
1158
1159 SET_SDP_CONTEXT
1160 (ITEMTYPE
1161 ,ITEMKEY
1162 ,ACTID
1163 ,'SET_FA'
1164 ,RESULTOUT
1165 );
1166
1167 l_FA_INSTANCE_ID := g_FA_INSTANCE_ID;
1168
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 l_FA_INSTANCE_ID := NULL;
1172 wf_core.clear;
1173 END;
1174
1175 -- Get the callback reference id
1176 XNP_UTILS.CHECK_TO_GET_REF_ID
1177 (p_itemtype => itemtype
1178 ,p_itemkey => itemkey
1179 ,p_actid => actid
1180 ,p_workitem_instance_id => l_workitem_instance_id
1181 ,x_reference_id => l_callback_ref_id
1182 );
1183
1184 -- If the Reference_ID is null make it -1
1185
1186 if l_callback_ref_id is null then
1187 l_callback_ref_id := -1;
1188 end if;
1189
1190 l_EVENT_TYPE :=
1191 wf_engine.GetActivityAttrText
1192 (itemtype => itemtype
1193 ,itemkey => itemkey
1194 ,actid => actid
1195 ,aname => 'EVENT_TYPE'
1196 );
1197
1198 l_ACTIVITY_NAME := wf_engine.GETACTIVITYLABEL(actid);
1199 l_PROCESS_REFERENCE :=
1200 itemtype||':'||itemkey||':'||l_activity_name;
1201
1202
1203 XNP_EVENT.SUBSCRIBE_FOR_ACKS
1204 (P_MESSAGE_TYPE=>l_EVENT_TYPE
1205 ,P_REFERENCE_ID =>l_CALLBACK_REF_ID
1206 ,P_PROCESS_REFERENCE=>l_PROCESS_REFERENCE
1207 ,X_ERROR_CODE=>l_error_code
1208 ,X_ERROR_MESSAGE=>l_error_message
1209 ,P_ORDER_ID=>l_ORDER_ID
1210 ,P_WI_INSTANCE_ID=>l_WORKITEM_INSTANCE_ID
1211 ,P_FA_INSTANCE_ID=>l_FA_INSTANCE_ID
1212 );
1213
1214 IF l_error_code <> 0
1215 THEN
1216 raise e_SUBSCRIBE_FOR_ACKS;
1217 END IF;
1218 -- Go to notified state to be woken up later
1219 resultout := 'NOTIFIED';
1220 RETURN;
1221 END IF;
1222 --
1223 -- CANCEL mode
1224 --
1225 -- This is in the event that the activity must be undone.
1226 --
1227 IF (funcmode = 'CANCEL' ) THEN
1228 -- The cancel code
1229
1230 null;
1231 -- no result needed
1232 resultout := 'COMPLETE';
1233 return;
1234 END IF;
1235
1236 -- For other execution modes: return null
1237
1238 resultout := '';
1239 return;
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 ------------------------------------------------------------------
1244 -- Record this function call in the error
1245 -- system in case of an exception
1246 ------------------------------------------------------------------
1247
1248 IF (l_error_code <> 0) THEN
1249 x_progress := to_char(l_error_code)||':'||l_error_message;
1250 ELSE
1251 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1252 END IF;
1253
1254 XNP_UTILS.NOTIFY_ERROR
1255 (P_PKG_NAME => 'XNP_WF_STANDARD'
1256 ,P_PROC_NAME => 'SUBSCRIBE_FOR_ACKS'
1257 ,P_MSG_NAME => 'SUBSCRIBE_FOR_ACKS_ERR'
1258 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1259 ,P_TOK1 => 'ERROR_TEXT'
1260 ,P_VAL1 => x_progress
1261 );
1262
1263 RAISE;
1264 END SUBSCRIBE_FOR_ACKS;
1265
1266 ------------------------------------------------------------------
1267 -- Prepares the notification to be sent to the target
1268 -- Uses the activity attr DOC_PROC_NAME and sets
1269 -- the Item Attribute DOC_REFERENCE
1270 ------------------------------------------------------------------
1271 PROCEDURE PREPARE_NOTIFICATION
1272 (ITEMTYPE IN VARCHAR2
1273 ,ITEMKEY IN VARCHAR2
1274 ,ACTID IN NUMBER
1275 ,FUNCMODE IN VARCHAR2
1276 ,RESULTOUT OUT NOCOPY VARCHAR2
1277 )
1278 IS
1279 l_WORKITEM_INSTANCE_ID NUMBER;
1280 l_DOC_PROC_NAME VARCHAR2(512);
1281 l_MESSAGE_ID NUMBER;
1282 x_progress VARCHAR2(2000);
1283 l_error_code NUMBER := 0;
1284 l_error_message VARCHAR2(2000);
1285 e_PREPARE_NOTIFICATION EXCEPTION;
1286 BEGIN
1287
1288 --
1289 -- RUN mode - normal process execution
1290 --
1291 IF (funcmode = 'RUN') THEN
1292
1293 -- The run code
1294
1295 l_DOC_PROC_NAME :=
1296 wf_engine.GetActivityAttrText
1297 (itemtype => itemtype
1298 ,itemkey => itemkey
1299 ,actid => actid
1300 ,aname => 'DOC_PROC_NAME'
1301 );
1302
1303 -- Modified avalue from FND_RESP534:21690 to FND_RESP535:21704, rnyberg 03/08/2002
1304 wf_engine.SetItemAttrText
1305 (itemtype => itemtype
1306 ,itemkey => itemkey
1307 ,aname => 'CUST_CARE_ADMIN'
1308 ,avalue => 'FND_RESP535:21704'
1309 -- ,avalue => xdp_utilities.get_wf_notifrecipient('NP_CUST_CARE_ADMIN')
1310 );
1311
1312 -- Modified avalue from FND_RESP534:21690 to FND_RESP535:21704, rnyberg 03/08/2002
1313 wf_engine.SetItemAttrText
1314 (itemtype => itemtype
1315 ,itemkey => itemkey
1316 ,aname => 'SYS_ADMIN'
1317 ,avalue => 'FND_RESP535:21704'
1318 -- ,avalue => xdp_utilities.get_wf_notifrecipient('NP_SYSADMIN')
1319 );
1320
1321 ------------------------------------------------------------------
1322 -- Set the document procedure and the document id
1323 -- into the Item Attribute DOC_REFERENCE
1324 -- The document id is the WORKITEM_INSTANCE_ID
1325 -- or ITEMTYPE:ITEMKEY
1326 ------------------------------------------------------------------
1327 IF (l_DOC_PROC_NAME = 'SERVICE_PROCESSING_ERROR')
1328 OR (l_DOC_PROC_NAME = 'NO_ACK_RECEIVED')
1329 THEN
1330
1331 l_MESSAGE_ID :=
1332 wf_engine.GetItemAttrNumber
1333 (itemtype => itemtype
1334 ,itemkey => itemkey
1335 ,aname => 'MSG_ID'
1336 );
1337
1338 wf_engine.SetItemAttrText
1339 (itemtype => itemtype
1340 ,itemkey => itemkey
1341 ,aname => 'DOC_REFERENCE'
1342 ,avalue => 'PLSQL:XNP_DOCUMENTS.'||l_DOC_PROC_NAME
1343 ||'/'||to_char(l_MESSAGE_ID)
1344 );
1345 ELSE
1346
1347
1348 SET_SDP_CONTEXT
1349 (ITEMTYPE
1350 ,ITEMKEY
1351 ,ACTID
1352 ,'SET_WI'
1353 ,RESULTOUT
1354 );
1355
1356 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
1357
1358 wf_engine.SetItemAttrText
1359 (itemtype => itemtype
1360 ,itemkey => itemkey
1361 ,aname => 'DOC_REFERENCE'
1362 ,avalue => 'PLSQL:XNP_DOCUMENTS.'||l_DOC_PROC_NAME
1363 ||'/'||to_char(l_WORKITEM_INSTANCE_ID)
1364 );
1365 END IF;
1366
1367 resultout := 'COMPLETE';
1368 RETURN;
1369 END IF;
1370 --
1371 -- CANCEL mode
1372 --
1373 -- This is in the event that the activity must be undone.
1374 --
1375 IF (funcmode = 'CANCEL' ) THEN
1376 -- The cancel code
1377
1378 null;
1379 -- no result needed
1380 resultout := 'COMPLETE';
1381 return;
1382 END IF;
1383
1384 -- For other execution modes: return null
1385
1386 resultout := '';
1387 return;
1388
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 ------------------------------------------------------------------
1392 -- Record this function call in the error
1393 -- system in case of an exception
1394 ------------------------------------------------------------------
1395
1396 IF (l_error_code <> 0) THEN
1397 x_progress := to_char(l_error_code)||':'||l_error_message;
1398 ELSE
1399 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1400 END IF;
1401
1402 XNP_UTILS.NOTIFY_ERROR
1403 (P_PKG_NAME => 'XNP_WF_STANDARD'
1404 ,P_PROC_NAME => 'PREPARE_NOTIFICATION'
1405 ,P_MSG_NAME => 'PREPARE_NOTIFICATION_ERR'
1406 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1407 ,P_TOK1 => 'ERROR_TEXT'
1408 ,P_VAL1 => x_progress
1409 );
1410
1411
1412 RAISE;
1413
1414 END PREPARE_NOTIFICATION;
1415
1416 --------------------------------------------------------------------
1417 --
1418 -- Called when: FA execution to talk to a network element is
1419 -- to be done
1420 -- Description:
1421 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
1422 -- Given the FA name as an activity parameter, it gets the
1423 -- correct FA procedure to be called and executes it
1424 -- dynamically
1425 ------------------------------------------------------------------
1426 PROCEDURE EXECUTE_FA
1427 (ITEMTYPE IN VARCHAR2
1428 ,ITEMKEY IN VARCHAR2
1429 ,ACTID IN NUMBER
1430 ,FUNCMODE IN VARCHAR2
1431 ,RESULTOUT OUT NOCOPY VARCHAR2
1432 )
1433 IS
1434 l_FA_NAME VARCHAR2(200);
1435 l_WORKITEM_INSTANCE_ID NUMBER;
1436 l_ORDER_ID NUMBER;
1437 l_FA_INSTANCE NUMBER := NULL;
1438 l_ERROR_CODE NUMBER := 0;
1439 l_ERROR_MESSAGE VARCHAR2(2000);
1440 --l_FA_INSTANCE_ID NUMBER := 0;
1441 l_FA_INSTANCE_ID NUMBER := NULL;
1442 l_FE_NAME VARCHAR2(80) := NULL;
1443 l_activity_name VARCHAR2(2000);
1444 l_process_reference VARCHAR2(2000);
1445 e_EXECUTE_FA EXCEPTION;
1446 x_progress VARCHAR2(2000);
1447
1448 BEGIN
1449
1450 -- Get the fa name to be used
1451 l_error_code := 0;
1452 l_error_message := 'SUCCESS';
1453
1454 --
1455 -- RUN mode - normal process execution
1456 --
1457 IF (funcmode = 'RUN') THEN
1458
1459 -- The run code
1460
1461
1462 SET_SDP_CONTEXT
1463 (ITEMTYPE
1464 ,ITEMKEY
1465 ,ACTID
1466 ,'SET_ORD_WI'
1467 ,RESULTOUT
1468 );
1469
1470 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
1471 l_ORDER_ID := g_ORDER_ID ;
1472
1473
1474 l_FA_NAME :=
1475 wf_engine.GetActivityAttrText
1476 (itemtype => itemtype
1477 ,itemkey => itemkey
1478 ,actid => actid
1479 ,aname => 'FA_NAME'
1480 );
1481
1482 -- Get the FE NAME to provision
1483 l_FE_NAME :=
1484 wf_engine.GetActivityAttrText
1485 (itemtype => itemtype
1486 ,itemkey => itemkey
1487 ,actid => actid
1488 ,aname => 'FE_NAME'
1489 );
1490
1491 ------------------------------------------------------------------
1492 -- Add the FA to the workitem and get the FA instance id
1493 ------------------------------------------------------------------
1494
1495 l_FA_INSTANCE_ID := XDP_ENG_UTIL.ADD_FA_TOWI(l_WORKITEM_INSTANCE_ID,
1496 l_FA_NAME,
1497 l_FE_NAME);
1498
1499 XDP_ENG_UTIL.EXECUTE_FA
1500 (l_ORDER_ID
1501 ,l_WORKITEM_INSTANCE_ID
1502 ,l_FA_INSTANCE_ID
1503 ,itemtype
1504 ,itemkey
1505 ,l_ERROR_CODE
1506 ,l_ERROR_MESSAGE
1507 );
1508
1509 IF l_error_code <> 0
1510 THEN
1511 raise e_EXECUTE_FA;
1512 END IF;
1513
1514 -- SUBSCRIBE for FA_DONE
1515 -- with the FA_INSTANCE_ID
1516 -- and let SFM resume workflow
1517
1518 l_ACTIVITY_NAME := wf_engine.GETACTIVITYLABEL(actid);
1519 l_PROCESS_REFERENCE :=
1520 itemtype||':'||itemkey||':'||l_ACTIVITY_NAME;
1521
1522 XNP_EVENT.SUBSCRIBE
1523 (P_MSG_CODE=>'FA_DONE' -- Message type to expected
1524 ,P_REFERENCE_ID=>l_FA_INSTANCE_ID -- Reference id
1525 ,P_PROCESS_REFERENCE=>l_PROCESS_REFERENCE -- workflow id
1526 ,P_PROCEDURE_NAME=>'XNP_EVENT.RESUME_WORKFLOW' -- callback proc
1527 ,P_CALLBACK_TYPE=>'PL/SQL' -- callback proc type
1528 ,P_CLOSE_REQD_FLAG => 'Y'
1529 ,P_ORDER_ID=>l_order_id
1530 ,P_WI_INSTANCE_ID=>l_workitem_instance_id
1531 ,P_FA_INSTANCE_ID=>l_FA_INSTANCE_ID
1532 );
1533
1534 -- Got to Notified state until SFM wakes you up
1535 resultout := 'NOTIFIED';
1536 RETURN;
1537 END IF;
1538 --
1539 -- CANCEL mode
1540 --
1541 -- This is in the event that the activity must be undone.
1542 --
1543 IF (funcmode = 'CANCEL' ) THEN
1544 -- The cancel code
1545
1546 null;
1547 -- no result needed
1548 resultout := 'COMPLETE';
1549 return;
1550 END IF;
1551
1552 -- For other execution modes: return null
1553
1554 resultout := '';
1555 return;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 ------------------------------------------------------------------
1560 -- Record this function call in the error
1561 -- system in case of an exception
1562 ------------------------------------------------------------------
1563
1564
1565 IF (l_error_code <> 0) THEN
1566 x_progress := to_char(l_error_code)||':'||l_error_message;
1567 ELSE
1568 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1569 END IF;
1570
1571 XNP_UTILS.NOTIFY_ERROR
1572 (P_PKG_NAME => 'XNP_WF_STANDARD'
1573 ,P_PROC_NAME => 'EXECUTE_FA'
1574 ,P_MSG_NAME => 'EXECUTE_FA_ERR'
1575 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1576 ,P_TOK1 => 'ERROR_TEXT'
1577 ,P_VAL1 => x_progress
1578 );
1579
1580
1581 RAISE;
1582
1583 END EXECUTE_FA;
1584
1585 PROCEDURE EXECUTE_FA_N_SYNC_WI_PAR
1586 (ITEMTYPE IN VARCHAR2
1587 ,ITEMKEY IN VARCHAR2
1588 ,ACTID IN NUMBER
1589 ,FUNCMODE IN VARCHAR2
1590 ,RESULTOUT OUT NOCOPY VARCHAR2
1591 )
1592 IS
1593
1594 l_ORDER_ID NUMBER;
1595 l_WORKITEM_INSTANCE_ID NUMBER;
1596 l_FA_NAME VARCHAR2(200);
1597 l_FE_NAME VARCHAR2(80) := NULL;
1598 l_FA_INSTANCE_ID NUMBER := NULL;
1599 l_param_name VARCHAR2(40);
1600 l_param_val VARCHAR2(4000);
1601 l_ERROR_CODE NUMBER := 0;
1602 l_ERROR_MESSAGE VARCHAR2(2000);
1603 l_activity_name VARCHAR2(2000);
1604 l_process_reference VARCHAR2(2000);
1605 lv_activity_label VARCHAR2(100);
1606 lv_colun_pos NUMBER;
1607
1608
1609 CURSOR c_get_params (cv_wi_instance_id NUMBER)IS
1610 SELECT parameter_name, parameter_value, parameter_ref_value
1611 FROM xdp_worklist_details
1612 WHERE workitem_instance_id = cv_wi_instance_id;
1613
1614 e_EXECUTE_FA EXCEPTION;
1615 x_progress VARCHAR2(2000);
1616
1617 BEGIN
1618
1619 IF (funcmode = 'RUN') THEN
1620
1621 l_WORKITEM_INSTANCE_ID := wf_engine.getItemAttrNumber (itemtype => EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1622 itemkey => EXECUTE_FA_N_SYNC_WI_PAR.itemkey,
1623 aname => 'WORKITEM_INSTANCE_ID');
1624
1625 FOR lv_rec in c_get_params( l_WORKITEM_INSTANCE_ID ) LOOP
1626 l_param_name := lv_rec.parameter_name;
1627 l_param_val := lv_rec.parameter_value;
1628
1629 BEGIN
1630 -- Get the data type of the item attribute..
1631 wf_engine.setItemAttrText(itemtype => EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1632 itemkey => EXECUTE_FA_N_SYNC_WI_PAR.itemkey,
1633 aname => l_param_name,
1634 avalue => l_param_val);
1635
1636 EXCEPTION
1637 WHEN others THEN
1638 -- skilaru 05/20/2002
1639 -- User defined workflow didnt have this item attribute defined..
1640 -- Kick off Default error process? or send a dynamic notification?
1641 RAISE;
1642 END;
1643 END LOOP;
1644 l_ORDER_ID := wf_engine.getItemAttrText (itemtype => EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1645 itemkey => EXECUTE_FA_N_SYNC_WI_PAR.itemkey ,
1646 aname => 'ORDER_ID');
1647
1648 l_FA_NAME := wf_engine.getActivityAttrText (itemtype => EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1649 itemkey => EXECUTE_FA_N_SYNC_WI_PAR.itemkey ,
1650 actid => actid,
1651 aname => 'FA_NAME');
1652
1653 l_FE_NAME := wf_engine.getActivityAttrText (itemtype => EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1654 itemkey => EXECUTE_FA_N_SYNC_WI_PAR.itemkey,
1655 actid => actid,
1656 aname => 'FE_NAME');
1657
1658
1659 -- Add the FA to the workitem and get the FA instance id
1660 l_FA_INSTANCE_ID := XDP_ENG_UTIL.ADD_FA_TOWI(l_WORKITEM_INSTANCE_ID,
1661 l_FA_NAME,
1662 l_FE_NAME);
1663
1664
1665 -- STEP2: Download Work Item params from workflow to the xdp_fa_details table..
1666 downloadFAParams( EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1667 EXECUTE_FA_N_SYNC_WI_PAR.itemkey,
1668 EXECUTE_FA_N_SYNC_WI_PAR.actid,
1669 l_FA_INSTANCE_ID );
1670
1671
1672 XDP_ENG_UTIL.EXECUTE_FA (l_ORDER_ID,
1673 l_WORKITEM_INSTANCE_ID,
1674 l_FA_INSTANCE_ID,
1675 EXECUTE_FA_N_SYNC_WI_PAR.itemtype,
1676 EXECUTE_FA_N_SYNC_WI_PAR.itemkey,
1677 l_ERROR_CODE,
1678 l_ERROR_MESSAGE);
1679
1680 IF l_error_code <> 0
1681 THEN
1682 raise e_EXECUTE_FA;
1683 END IF;
1684
1685 -- SUBSCRIBE for FA_DONE
1686 -- with the FA_INSTANCE_ID
1687 -- and let SFM resume workflow
1688
1689 l_ACTIVITY_NAME := wf_engine.GETACTIVITYLABEL(actid);
1690 l_PROCESS_REFERENCE :=
1691 itemtype||':'||itemkey||':'||l_ACTIVITY_NAME;
1692
1693 XNP_EVENT.SUBSCRIBE
1694 (P_MSG_CODE=>'FA_DONE' -- Message type to expected
1695 ,P_REFERENCE_ID=>l_FA_INSTANCE_ID -- Reference id
1696 ,P_PROCESS_REFERENCE=>l_PROCESS_REFERENCE -- workflow id
1697 ,P_PROCEDURE_NAME=>'XNP_EVENT.SYNC_N_RESUME_WF' -- callback proc
1698 ,P_CALLBACK_TYPE=>'PL/SQL' -- callback proc type
1699 ,P_CLOSE_REQD_FLAG => 'Y'
1700 ,P_ORDER_ID=>l_order_id
1701 ,P_WI_INSTANCE_ID=>l_workitem_instance_id
1702 ,P_FA_INSTANCE_ID=>l_FA_INSTANCE_ID
1703 );
1704
1705 -- Got to Notified state until SFM wakes you up
1706 resultout := 'NOTIFIED';
1707 RETURN;
1708 ELSIF (funcmode = 'CANCEL' ) THEN
1709 resultout := 'COMPLETE';
1710 return;
1711 END IF;
1712
1713
1714 resultout := '';
1715 return;
1716
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 ------------------------------------------------------------------
1720 -- Record this function call in the error
1721 -- system in case of an exception
1722 ------------------------------------------------------------------
1723
1724
1725 IF (l_error_code <> 0) THEN
1726 x_progress := to_char(l_error_code)||':'||l_error_message;
1727 ELSE
1728 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1729 END IF;
1730
1731 XNP_UTILS.NOTIFY_ERROR
1732 (P_PKG_NAME => 'XNP_WF_STANDARD'
1733 ,P_PROC_NAME => 'EXECUTE_FA'
1734 ,P_MSG_NAME => 'EXECUTE_FA_ERR'
1735 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1736 ,P_TOK1 => 'ERROR_TEXT'
1737 ,P_VAL1 => x_progress
1738 );
1739
1740
1741 RAISE;
1742
1743 END EXECUTE_FA_N_SYNC_WI_PAR;
1744
1745
1746 --------------------------------------------------------------------
1747 -- Description:
1748 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
1749 -- calls XNP_STANDARD.SOA_CHECK_NOTIFY_DIR_SVS
1750 -- Completes the path based on the result
1751 ------------------------------------------------------------------
1752 PROCEDURE SOA_CHECK_NOTIFY_DIR_SVS
1753 (ITEMTYPE IN VARCHAR2
1754 ,ITEMKEY IN VARCHAR2
1755 ,ACTID IN NUMBER
1756 ,FUNCMODE IN VARCHAR2
1757 ,RESULTOUT OUT NOCOPY VARCHAR2
1758 )
1759 IS
1760 x_progress VARCHAR2(2000);
1761 l_WORKITEM_INSTANCE_ID NUMBER;
1762 l_error_code NUMBER := 0;
1763 l_error_message VARCHAR2(2000);
1764 l_check_status VARCHAR2(1);
1765 e_SOA_CHECK_NOTIFY_DIR_SVS EXCEPTION;
1766
1767 BEGIN
1768
1769 --
1770 -- RUN mode - normal process execution
1771 --
1772 IF (funcmode = 'RUN') THEN
1773
1774 -- The run code
1775
1776 SET_SDP_CONTEXT
1777 (ITEMTYPE
1778 ,ITEMKEY
1779 ,ACTID
1780 ,'SET_WI'
1781 ,RESULTOUT
1782 );
1783
1784 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
1785
1786 -- Get the value of to flag
1787
1788 XNP_STANDARD.SOA_CHECK_NOTIFY_DIR_SVS
1789 (l_WORKITEM_INSTANCE_ID
1790 ,l_check_status
1791 ,l_error_code
1792 ,l_error_message
1793 );
1794
1795 IF l_error_code <> 0
1796 THEN
1797 raise e_SOA_CHECK_NOTIFY_DIR_SVS;
1798 END IF;
1799
1800 -- Completion: If check status is true the traces
1801 -- the 'YES' path else trace the 'NO' path
1802 IF l_check_status = 'Y'
1803 THEN
1804 resultout := 'COMPLETE:Y';
1805 ELSE
1806 resultout := 'COMPLETE:N';
1807 END IF;
1808
1809 RETURN;
1810 END IF;
1811 --
1812 -- CANCEL mode
1813 --
1814 -- This is in the event that the activity must be undone.
1815 --
1816 IF (funcmode = 'CANCEL' ) THEN
1817 -- The cancel code
1818
1819 null;
1820 -- no result needed
1821 resultout := 'COMPLETE';
1822 return;
1823 END IF;
1824
1825 -- For other execution modes: return null
1826
1827 resultout := '';
1828 return;
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 ------------------------------------------------------------------
1833 -- Record this function call in the error
1834 -- system in case of an exception
1835 ------------------------------------------------------------------
1836
1837 IF (l_error_code <> 0) THEN
1838 x_progress := to_char(l_error_code)||':'||l_error_message;
1839 ELSE
1840 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1841 END IF;
1842
1843 XNP_UTILS.NOTIFY_ERROR
1844 (P_PKG_NAME => 'XNP_WF_STANDARD'
1845 ,P_PROC_NAME => 'SOA_CHECK_NOTIFY_DIR_SVS'
1846 ,P_MSG_NAME => 'SOA_CHECK_NOTIFY_DIR_SVS_ERR'
1847 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1848 ,P_TOK1 => 'ERROR_TEXT'
1849 ,P_VAL1 => x_progress
1850 );
1851
1852 RAISE;
1853
1854 END SOA_CHECK_NOTIFY_DIR_SVS;
1855
1856
1857
1858 --------------------------------------------------------------------
1859 -- Called at: Recipient side when charging info is recd
1860 -- Description:
1861 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
1862 -- calls XNP_STANDARD.UPDATE_CHARGING_INFO
1863 ------------------------------------------------------------------
1864 PROCEDURE SOA_UPDATE_CHARGING_INFO
1865 (ITEMTYPE IN VARCHAR2
1866 ,ITEMKEY IN VARCHAR2
1867 ,ACTID IN NUMBER
1868 ,FUNCMODE IN VARCHAR2
1869 ,RESULTOUT OUT NOCOPY VARCHAR2
1870 )
1871 IS
1872 x_progress VARCHAR2(2000);
1873 l_ORDER_ID NUMBER;
1874 l_LINEITEM_ID NUMBER;
1875 l_WORKITEM_INSTANCE_ID NUMBER;
1876 l_FA_INSTANCE_ID NUMBER;
1877 l_error_code NUMBER := 0;
1878 l_error_message VARCHAR2(2000);
1879 l_CUR_STATUS_TYPE_CODE VARCHAR2(80);
1880 e_UPDATE_CHARGING_INFO EXCEPTION;
1881
1882 BEGIN
1883
1884 --
1885 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
1886 --
1887 SET_SDP_CONTEXT
1888 (ITEMTYPE
1889 ,ITEMKEY
1890 ,ACTID
1891 ,'SET_ORD_WI'
1892 ,RESULTOUT
1893 );
1894
1895 --
1896 -- RUN mode - normal process execution
1897 --
1898 IF (funcmode = 'RUN') THEN
1899
1900 -- The run code
1901
1902 l_ORDER_ID := g_ORDER_ID;
1903 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
1904
1905 -- Update the charging info
1906 XNP_STANDARD.SOA_UPDATE_CHARGING_INFO
1907 (l_ORDER_ID ,
1908 l_LINEITEM_ID,
1909 l_WORKITEM_INSTANCE_ID,
1910 l_FA_INSTANCE_ID,
1911 l_CUR_STATUS_TYPE_CODE,
1912 l_error_code,
1913 l_error_message
1914 );
1915
1916 IF l_error_code <> 0 THEN
1917 raise e_UPDATE_CHARGING_INFO;
1918 END IF;
1919
1920 resultout := 'COMPLETE';
1921
1922 RETURN;
1923 END IF;
1924 --
1925 -- CANCEL mode
1926 --
1927 -- This is in the event that the activity must be undone.
1928 --
1929 IF (funcmode = 'CANCEL' ) THEN
1930 -- The cancel code
1931
1932 null;
1933 -- no result needed
1934 resultout := 'COMPLETE';
1935 return;
1936 END IF;
1937
1938 -- For other execution modes: return null
1939
1940 resultout := '';
1941 return;
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 ------------------------------------------------------------------
1946 -- Record this function call in the error
1947 -- system in case of an exception
1948 ------------------------------------------------------------------
1949
1950
1951 IF (l_error_code <> 0) THEN
1952 x_progress := to_char(l_error_code)||':'||l_error_message;
1953 ELSE
1954 x_progress := to_char(SQLCODE)||':'||SQLERRM;
1955 END IF;
1956
1957 XNP_UTILS.NOTIFY_ERROR
1958 (P_PKG_NAME => 'XNP_WF_STANDARD'
1959 ,P_PROC_NAME => 'SOA_UPDATE_CHARGING_INFO'
1960 ,P_MSG_NAME => 'SOA_UPDATE_CHARGING_INFO_ERR'
1961 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
1962 ,P_TOK1 => 'ERROR_TEXT'
1963 ,P_VAL1 => x_progress
1964 );
1965
1966 RAISE;
1967
1968 END SOA_UPDATE_CHARGING_INFO;
1969
1970 --------------------------------------------------------------------
1971 -- Called during provisioning phase
1972 -- Description:
1973 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
1974 -- Gets Workitem parameters STARTING_NUMBER, ENDING_NUMBER
1975 -- For each of the FEs executes a fulfillment action
1976 ------------------------------------------------------------------
1977 PROCEDURE SMS_PROVISION_NES
1978 (ITEMTYPE IN VARCHAR2
1979 ,ITEMKEY IN VARCHAR2
1980 ,ACTID IN NUMBER
1981 ,FUNCMODE IN VARCHAR2
1982 ,RESULTOUT OUT NOCOPY VARCHAR2
1983 )
1984 IS
1985 x_progress VARCHAR2(2000);
1986 l_ORDER_ID NUMBER;
1987 l_LINEITEM_ID NUMBER;
1988 l_WORKITEM_INSTANCE_ID NUMBER;
1989 l_FA_INSTANCE_ID NUMBER := NULL;
1990 L_ERROR_CODE NUMBER := 0;
1991 L_ERROR_MESSAGE VARCHAR2(2000);
1992 L_ACTIVITY_NAME VARCHAR2(240):=NULL;
1993 L_PROCESS_REFERENCE VARCHAR2(512) := null;
1994 l_FEATURE_TYPE VARCHAR2(80) := NULL;
1995 l_STARTING_NUMBER VARCHAR2(80) := NULL;
1996 l_ENDING_NUMBER VARCHAR2(80) := NULL;
1997 l_NUMBER_RANGE_ID NUMBER := 0;
1998 l_count NUMBER := 1;
1999
2000 CURSOR c_ALL_FEs IS
2001 SELECT SNR.fe_id
2002 FROM xnp_served_num_ranges SNR
2003 WHERE SNR.feature_type = l_feature_type
2004 AND SNR.number_range_id = l_number_range_id;
2005
2006 e_SMS_PROVISION_NES EXCEPTION;
2007
2008 BEGIN
2009
2010 --
2011 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
2012 --
2013 SET_SDP_CONTEXT
2014 (ITEMTYPE
2015 ,ITEMKEY
2016 ,ACTID
2017 ,'SET_ORD_WI'
2018 ,RESULTOUT
2019 );
2020
2021 --
2022 -- RUN mode - normal process execution
2023 --
2024 IF (funcmode = 'RUN') THEN
2025
2026 -- The run code
2027
2028 l_ORDER_ID := g_ORDER_ID;
2029 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
2030
2031 l_FEATURE_TYPE :=
2032 wf_engine.GetActivityAttrText (itemtype => itemtype,
2033 itemkey => itemkey,
2034 actid => actid,
2035 aname => 'FEATURE_TYPE');
2036
2037 l_STARTING_NUMBER :=
2038 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2039 (l_WORKITEM_INSTANCE_ID
2040 ,'STARTING_NUMBER'
2041 );
2042
2043 l_ENDING_NUMBER :=
2044 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2045 (l_WORKITEM_INSTANCE_ID
2046 ,'ENDING_NUMBER'
2047 );
2048
2049 -- Determine the number range id
2050
2051 XNP_CORE.GET_NUMBER_RANGE_ID
2052 (l_STARTING_NUMBER
2053 ,l_ENDING_NUMBER
2054 ,l_NUMBER_RANGE_ID
2055 ,l_ERROR_CODE
2056 ,l_ERROR_MESSAGE
2057 );
2058
2059 IF l_error_code <> 0 THEN
2060 raise e_SMS_PROVISION_NES;
2061 END IF;
2062
2063
2064 ------------------------------------------------------------------
2065 -- Record this function call in the error
2066 -- Set the necessary item attributes before starting
2067 -- i.e. FE_NAME
2068 ------------------------------------------------------------------
2069
2070 -- get the fe list to provision
2071 FOR l_tmp_fe IN c_ALL_FEs
2072
2073 LOOP
2074 IF XDP_ENGINE.IS_FE_VALID(l_tmp_fe.fe_id) THEN
2075
2076 -- Insert the FE MAP for the FE to be provisioned
2077
2078 /***Changed Call to proc to solve Bug # 2104648 -- 11/21/2001 mviswana***/
2079
2080 XNP_CORE.SMS_INSERT_FE_MAP
2081 (p_ORDER_ID => l_ORDER_ID,
2082 p_LINEITEM_ID => l_LINEITEM_ID,
2083 p_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID,
2084 p_FA_INSTANCE_ID => l_FA_INSTANCE_ID,
2085 p_STARTING_NUMBER => to_number(l_STARTING_NUMBER),
2086 p_ENDING_NUMBER => to_number(l_ENDING_NUMBER),
2087 p_FE_ID => l_TMP_FE.FE_ID,
2088 p_FEATURE_TYPE => l_FEATURE_TYPE,
2089 x_ERROR_CODE => l_ERROR_CODE,
2090 x_ERROR_MESSAGE => l_ERROR_MESSAGE
2091 );
2092
2093 IF l_ERROR_CODE <> 0 THEN
2094 raise e_SMS_PROVISION_NES;
2095 END IF;
2096
2097 ------------------------------------------------------------------
2098 -- Add the FA to the workitem and get the FA instance id
2099 ------------------------------------------------------------------
2100 l_FA_INSTANCE_ID :=
2101 XDP_ENG_UTIL.ADD_FA_TOWI
2102 (l_WORKITEM_INSTANCE_ID
2103 ,'PROVISION_'||l_FEATURE_TYPE -- the FA
2104 ,l_TMP_FE.FE_ID
2105 );
2106
2107 -- Call fa exection procedure
2108
2109 xdp_eng_util.execute_fa
2110 (p_order_id => l_order_id
2111 ,p_wi_instance_id => l_workitem_instance_id
2112 ,p_fa_instance_id => l_fa_instance_id
2113 ,p_wi_item_type => itemtype
2114 ,p_wi_item_key => itemkey
2115 ,p_return_code => l_error_code
2116 ,p_error_description => l_error_message
2117 ,p_fa_caller => 'INTERNAL'
2118 );
2119
2120 IF l_error_code <> 0 THEN
2121 raise e_SMS_PROVISION_NES;
2122 END IF;
2123
2124 -- SUBSCRIBE for FA_DONE
2125 -- with the FA_INSTANCE_ID
2126 -- and let SFM resume workflow
2127 -- append FEATURE_TYPE and FE_ID to the process reference
2128 l_PROCESS_REFERENCE :=
2129 itemtype||':'||itemkey||':'||'PROV:'||l_FEATURE_TYPE||':'||to_char(l_tmp_fe.FE_ID);
2130
2131 XNP_EVENT.SUBSCRIBE
2132 (P_MSG_CODE => 'FA_DONE' -- Message type to expected
2133 ,P_REFERENCE_ID => l_FA_INSTANCE_ID -- Reference id
2134 ,P_PROCESS_REFERENCE => l_PROCESS_REFERENCE -- workflow id
2135 ,P_PROCEDURE_NAME => 'XNP_FA_CB.PROCESS_FA_DONE' -- callback proc
2136 ,P_CALLBACK_TYPE => 'PL/SQL' -- callback proc type
2137 ,P_CLOSE_REQD_FLAG => 'Y'
2138 ,P_ORDER_ID => l_order_id
2139 ,P_WI_INSTANCE_ID => l_workitem_instance_id
2140 ,P_FA_INSTANCE_ID => l_FA_INSTANCE_ID
2141 );
2142
2143 ELSE null;
2144 END IF;
2145
2146 END LOOP;
2147
2148
2149 resultout := 'COMPLETE';
2150
2151 RETURN;
2152
2153 END IF;
2154 --
2155 -- CANCEL mode
2156 --
2157 -- This is in the event that the activity must be undone.
2158 --
2159 IF (funcmode = 'CANCEL' ) THEN
2160 -- The cancel code
2161
2162 null;
2163 -- no result needed
2164 resultout := 'COMPLETE';
2165 return;
2166 END IF;
2167
2168 -- For other execution modes: return null
2169
2170 resultout := '';
2171 return;
2172
2173 EXCEPTION
2174 WHEN OTHERS THEN
2175 ------------------------------------------------------------------
2176 -- Record this function call in the error
2177 -- system in case of an exception
2178 ------------------------------------------------------------------
2179
2180 IF (l_error_code <> 0) THEN
2181 x_progress := to_char(l_error_code)||':'||l_error_message;
2182 ELSE
2183 x_progress := to_char(SQLCODE)||':'||SQLERRM;
2184 END IF;
2185
2186 XNP_UTILS.NOTIFY_ERROR
2187 (P_PKG_NAME => 'XNP_WF_STANDARD'
2188 ,P_PROC_NAME => 'SMS_PROVISION_NES'
2189 ,P_MSG_NAME => 'SMS_PROVISION_NES_ERR'
2190 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2191 ,P_TOK1 => 'ERROR_TEXT'
2192 ,P_VAL1 => x_progress
2193 );
2194
2195 RAISE;
2196
2197 END SMS_PROVISION_NES;
2198
2199
2200 --------------------------------------------------------------------
2201 -- Called when: Checks the order/inquiry response
2202 -- Description:
2203 -- calls XNP_STANDARD.SOA_CHECK_ORDER_STATUS
2204 -- and completes the activityn based on the result
2205 ------------------------------------------------------------------
2206
2207 PROCEDURE SOA_CHECK_ORDER_STATUS
2208 (ITEMTYPE IN VARCHAR2
2209 ,ITEMKEY IN VARCHAR2
2210 ,ACTID IN NUMBER
2211 ,FUNCMODE IN VARCHAR2
2212 ,RESULTOUT OUT NOCOPY VARCHAR2
2213 )
2214 IS
2215 x_progress VARCHAR2(2000);
2216 l_WORKITEM_INSTANCE_ID NUMBER;
2217 l_error_code NUMBER := 0;
2218 l_error_message VARCHAR2(2000);
2219 l_ORDER_STATUS VARCHAR2(40);
2220 e_SOA_CHECK_ORDER_STATUS EXCEPTION;
2221 BEGIN
2222
2223 --
2224 -- RUN mode - normal process execution
2225 --
2226 IF (funcmode = 'RUN') THEN
2227
2228 -- The run code
2229
2230
2231 SET_SDP_CONTEXT
2232 (ITEMTYPE
2233 ,ITEMKEY
2234 ,ACTID
2235 ,'SET_WI'
2236 ,RESULTOUT
2237 );
2238
2239 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
2240
2241 -- Get the order status value
2242 XNP_STANDARD.SOA_CHECK_ORDER_STATUS
2243 (l_WORKITEM_INSTANCE_ID
2244 ,l_ORDER_STATUS
2245 ,l_error_code
2246 ,l_error_message
2247 );
2248
2249 IF l_error_code <> 0 THEN
2250 raise e_SOA_CHECK_ORDER_STATUS;
2251 END IF;
2252
2253 -- Completion: If check status is true the traces
2254 -- the 'YES' path else trace the 'NO' path
2255 IF l_ORDER_STATUS = 'Y' THEN
2256 resultout := 'COMPLETE:Y';
2257 ELSE
2258 resultout := 'COMPLETE:N';
2259 END IF;
2260
2261 RETURN;
2262 END IF;
2263 --
2264 -- CANCEL mode
2265 --
2266 -- This is in the event that the activity must be undone.
2267 --
2268 IF (funcmode = 'CANCEL' ) THEN
2269 -- The cancel code
2270
2271 null;
2272 -- no result needed
2273 resultout := 'COMPLETE';
2274 return;
2275 END IF;
2276
2277 -- For other execution modes: return null
2278
2279 resultout := '';
2280 return;
2281
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284 ------------------------------------------------------------------
2285 -- Record this function call in the error
2286 -- system in case of an exception
2287 ------------------------------------------------------------------
2288
2289
2290 IF (l_error_code <> 0) THEN
2291 x_progress := to_char(l_error_code)||':'||l_error_message;
2292 ELSE
2293 x_progress := to_char(SQLCODE)||':'||SQLERRM;
2294 END IF;
2295
2296 XNP_UTILS.NOTIFY_ERROR
2297 (P_PKG_NAME => 'XNP_WF_STANDARD'
2298 ,P_PROC_NAME => 'SOA_CHECK_ORDER_STATUS'
2299 ,P_MSG_NAME => 'SOA_CHECK_ORDER_STATUS_ERR'
2300 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2301 ,P_TOK1 => 'ERROR_TEXT'
2302 ,P_VAL1 => x_progress
2303 );
2304
2305
2306 RAISE;
2307
2308 END ;
2309
2310 ------------------------------------------------------------------
2311 -- Called to publish a single business event
2312 -- The recipients of this event should have
2313 -- already subscribed for it incase of
2314 -- internal events
2315 -- Gets the activity attribute EVENT_TYPE, PARAM_LIST
2316 ------------------------------------------------------------------
2317 PROCEDURE PUBLISH_EVENT
2318 (ITEMTYPE IN VARCHAR2
2319 ,ITEMKEY IN VARCHAR2
2320 ,ACTID IN NUMBER
2321 ,FUNCMODE IN VARCHAR2
2322 ,RESULTOUT OUT NOCOPY VARCHAR2
2323 )
2324 IS
2325 x_progress VARCHAR2(2000);
2326 l_WORKITEM_INSTANCE_ID NUMBER;
2327 l_FA_INSTANCE_ID NUMBER;
2328 l_ORDER_ID NUMBER;
2329 l_REFERENCE_ID NUMBER;
2330 l_EVENT_TYPE VARCHAR2(80);
2331 l_PARAM_LIST VARCHAR2(2000);
2332 l_CALLBACK_REF_ID VARCHAR2(1024);
2333 l_tmp_param_list VARCHAR2(2000) := NULL;
2334 l_tmp_callback_ref_id VARCHAR2(2000) := NULL;
2335 l_error_code NUMBER := 0;
2336 l_error_message VARCHAR2(2000);
2337 e_PUBLISH_EVENT EXCEPTION;
2338 BEGIN
2339
2340 --
2341 -- RUN mode - normal process execution
2342 --
2343
2344 IF (funcmode = 'RUN') THEN
2345
2346 -- The run code
2347
2348
2349 SET_SDP_CONTEXT
2350 (ITEMTYPE
2351 ,ITEMKEY
2352 ,ACTID
2353 ,'SET_ORD_WI'
2354 ,RESULTOUT
2355 );
2356
2357 l_ORDER_ID := g_ORDER_ID;
2358 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
2359
2360 BEGIN
2361
2362 SET_SDP_CONTEXT
2363 (ITEMTYPE
2364 ,ITEMKEY
2365 ,ACTID
2366 ,'SET_FA'
2367 ,RESULTOUT
2368 );
2369
2370 l_FA_INSTANCE_ID := g_FA_INSTANCE_ID;
2371
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 l_FA_INSTANCE_ID := NULL;
2375 wf_core.clear;
2376 END;
2377
2378 -- Get the event type to publish
2379
2380 l_EVENT_TYPE :=
2381 wf_engine.GetActivityAttrText
2382 (itemtype => itemtype
2383 ,itemkey => itemkey
2384 ,actid => actid
2385 ,aname => 'EVENT_TYPE'
2386 );
2387
2388 -- Get the names of the parameters for PUBLISH
2389
2390 l_tmp_param_list :=
2391 wf_engine.GetActivityAttrText
2392 (itemtype => itemtype
2393 ,itemkey => itemkey
2394 ,actid => actid
2395 ,aname => 'PARAM_LIST'
2396 );
2397
2398 l_PARAM_LIST := replace(l_tmp_param_list,' ','');
2399
2400
2401 -- Get the callback reference id
2402
2403 XNP_UTILS.CHECK_TO_GET_REF_ID
2404 (p_itemtype => itemtype
2405 ,p_itemkey => itemkey
2406 ,p_actid => actid
2407 ,p_workitem_instance_id => l_workitem_instance_id
2408 ,x_reference_id => l_callback_ref_id
2409 );
2410
2411 -- If the Reference_ID is null make it -1
2412
2413 if l_callback_ref_id is null then
2414 l_callback_ref_id := -1;
2415 end if;
2416
2417 -- Invoke publish event
2418
2419 XNP_STANDARD.PUBLISH_EVENT
2420 (p_ORDER_ID => l_ORDER_ID
2421 ,p_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2422 ,p_FA_INSTANCE_ID => l_FA_INSTANCE_ID
2423 ,p_EVENT_TYPE => l_EVENT_TYPE
2424 ,p_PARAM_LIST => l_PARAM_LIST
2425 ,p_CALLBACK_REF_ID => l_CALLBACK_REF_ID
2426 ,x_error_code => l_error_code
2427 ,x_error_message => l_error_message
2428 );
2429
2430 IF l_error_code <> 0
2431 THEN
2432 raise e_PUBLISH_EVENT;
2433 END IF;
2434
2435 resultout := 'COMPLETE';
2436
2437 RETURN;
2438 END IF;
2439 --
2440 -- CANCEL mode
2441 --
2442 -- This is in the event that the activity must be undone.
2443 --
2444 IF (funcmode = 'CANCEL' ) THEN
2445 -- The cancel code
2446
2447 null;
2448 -- no result needed
2449 resultout := 'COMPLETE';
2450 return;
2451 END IF;
2452
2453 -- For other execution modes: return null
2454
2455 resultout := '';
2456 return;
2457
2458 EXCEPTION
2459 WHEN OTHERS THEN
2460 ------------------------------------------------------------------
2461 -- Record this function call in the error
2462 -- system in case of an exception
2463 ------------------------------------------------------------------
2464
2465
2466
2467 IF (l_error_code <> 0) THEN
2468 x_progress := to_char(l_error_code)||':'||l_error_message;
2469 ELSE
2470 x_progress := to_char(SQLCODE)||':'||SQLERRM;
2471 END IF;
2472
2473 XNP_UTILS.NOTIFY_ERROR
2474 (P_PKG_NAME => 'XNP_WF_STANDARD'
2475 ,P_PROC_NAME => 'PUBLISH_EVENT'
2476 ,P_MSG_NAME => 'PUBLISH_EVENT_ERR'
2477 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2478 ,P_TOK1 => 'ERROR_TEXT'
2479 ,P_VAL1 => x_progress
2480 );
2481
2482
2483 RAISE;
2484
2485 END PUBLISH_EVENT;
2486
2487
2488
2489 --------------------------------------------------------------------
2490 -- Checks if the records in the given status
2491 -- and if 'Y' then completes the 'YES' path
2492 -- else completes the 'NO' path
2493 --
2494 ------------------------------------------------------------------
2495 PROCEDURE CHECK_SOA_STATUS_EXISTS
2496 (ITEMTYPE IN VARCHAR2
2497 ,ITEMKEY IN VARCHAR2
2498 ,ACTID IN NUMBER
2499 ,FUNCMODE IN VARCHAR2
2500 ,RESULTOUT OUT NOCOPY VARCHAR2
2501 )
2502
2503 IS
2504 x_progress VARCHAR2(2000);
2505 l_WORKITEM_INSTANCE_ID NUMBER;
2506 l_error_code NUMBER := 0;
2507 l_error_message VARCHAR2(2000);
2508 l_CHECK_STATUS VARCHAR2(5);
2509 l_STATUS_TYPE_CODE VARCHAR2(80);
2510 e_CHECK_SOA_STATUS_EXISTS EXCEPTION;
2511 BEGIN
2512
2513 --
2514 -- RUN mode - normal process execution
2515 --
2516 IF (funcmode = 'RUN') THEN
2517
2518 SET_SDP_CONTEXT
2519 (ITEMTYPE
2520 ,ITEMKEY
2521 ,ACTID
2522 ,'SET_WI'
2523 ,RESULTOUT
2524 );
2525
2526 -- The run code
2527
2528 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
2529
2530 l_STATUS_TYPE_CODE :=
2531 wf_engine.GetActivityAttrText (itemtype => itemtype,
2532 itemkey => itemkey,
2533 actid => actid,
2534 aname => 'STATUS_TYPE_CODE');
2535
2536 -- Get the order status value
2537 XNP_STANDARD.CHECK_SOA_STATUS_EXISTS
2538 (l_WORKITEM_INSTANCE_ID
2539 ,l_STATUS_TYPE_CODE
2540 ,l_CHECK_STATUS
2541 ,l_error_code
2542 ,l_error_message
2543 );
2544
2545 IF l_error_code <> 0
2546 THEN
2547 raise e_CHECK_SOA_STATUS_EXISTS;
2548 END IF;
2549
2550 -- Completion: If check status is true the traces
2551 -- the 'YES' path else trace the 'NO' path
2552 IF l_CHECK_STATUS = 'Y'
2553 THEN
2554 resultout := 'COMPLETE:Y';
2555 ELSE
2556 resultout := 'COMPLETE:N';
2557 END IF;
2558
2559 RETURN;
2560 END IF;
2561 --
2562 -- CANCEL mode
2563 --
2564 -- This is in the event that the activity must be undone.
2565 --
2566 IF (funcmode = 'CANCEL' ) THEN
2567 -- The cancel code
2568
2569 null;
2570 -- no result needed
2571 resultout := 'COMPLETE';
2572 return;
2573 END IF;
2574
2575 -- For other execution modes: return null
2576
2577 resultout := '';
2578 return;
2579
2580 EXCEPTION
2581 WHEN OTHERS THEN
2582 ------------------------------------------------------------------
2583 -- Record this function call in the error
2584 -- system in case of an exception
2585 ------------------------------------------------------------------
2586
2587
2588 IF (l_error_code <> 0) THEN
2589 x_progress := to_char(l_error_code)||':'||l_error_message;
2590 ELSE
2591 x_progress := to_char(SQLCODE)||':'||SQLERRM;
2592 END IF;
2593
2594 XNP_UTILS.NOTIFY_ERROR
2595 (P_PKG_NAME => 'XNP_WF_STANDARD'
2596 ,P_PROC_NAME => 'CHECK_SOA_STATUS_EXISTS'
2597 ,P_MSG_NAME => 'CHECK_SOA_STATUS_EXISTS_ERR'
2598 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2599 ,P_TOK1 => 'ERROR_TEXT'
2600 ,P_VAL1 => x_progress
2601 );
2602
2603
2604 RAISE;
2605
2606 END CHECK_SOA_STATUS_EXISTS;
2607
2608 --------------------------------------------------------------------
2609 -- Sets the ORDER_RESULT to the value passed
2610 -- in the activity attribute ORDER_STATUS
2611 -- This value will be embeded in the message
2612 -- sent to the other side to drive the workflow
2613 ------------------------------------------------------------------
2614 PROCEDURE SET_ORDER_RESULT
2615 (ITEMTYPE IN VARCHAR2
2616 ,ITEMKEY IN VARCHAR2
2617 ,ACTID IN NUMBER
2618 ,FUNCMODE IN VARCHAR2
2619 ,RESULTOUT OUT NOCOPY VARCHAR2
2620 )
2621 IS
2622 x_progress VARCHAR2(2000);
2623 l_WORKITEM_INSTANCE_ID NUMBER;
2624 l_error_code NUMBER := 0;
2625 l_error_message VARCHAR2(2000);
2626 l_ORDER_STATUS VARCHAR2(80) := NULL; -- SUCCESS or FAILURE
2627 l_STATUS_TYPE_CODE VARCHAR2(80) := NULL;
2628 l_ORDER_REJECT_CODE VARCHAR2(80) := NULL;
2629 l_ORDER_REJECT_EXPLN VARCHAR2(512) := NULL;
2630 e_SET_ORDER_RESULT EXCEPTION;
2631 BEGIN
2632
2633 --
2634 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
2635 --
2636 SET_SDP_CONTEXT
2637 (ITEMTYPE
2638 ,ITEMKEY
2639 ,ACTID
2640 ,'SET_ORD_WI'
2641 ,RESULTOUT
2642 );
2643
2644 --
2645 -- RUN mode - normal process execution
2646 --
2647 IF (funcmode = 'RUN') THEN
2648
2649 -- The run code
2650
2651 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
2652 l_ORDER_STATUS := g_ORDER_ID;
2653
2654
2655 -- Set the order result value
2656 XNP_STANDARD.SET_ORDER_RESULT
2657 (l_WORKITEM_INSTANCE_ID
2658 ,l_ORDER_STATUS
2659 ,l_ORDER_REJECT_CODE
2660 ,l_ORDER_REJECT_EXPLN
2661 ,l_error_code
2662 ,l_error_message
2663 );
2664
2665 IF l_error_code <> 0
2666 THEN
2667 raise e_SET_ORDER_RESULT;
2668 END IF;
2669
2670 resultout := 'COMPLETE';
2671
2672 RETURN;
2673 END IF;
2674 --
2675 -- CANCEL mode
2676 --
2677 -- This is in the event that the activity must be undone.
2678 --
2679 IF (funcmode = 'CANCEL' ) THEN
2680 -- The cancel code
2681
2682 null;
2683 -- no result needed
2684 resultout := 'COMPLETE';
2685 return;
2686 END IF;
2687
2688 -- For other execution modes: return null
2689
2690 resultout := '';
2691 return;
2692
2693 EXCEPTION
2694 WHEN OTHERS THEN
2695 ------------------------------------------------------------------
2696 -- Record this function call in the error
2697 -- system in case of an exception
2698 ------------------------------------------------------------------
2699
2700 IF (l_error_code <> 0) THEN
2701 x_progress := to_char(l_error_code)||':'||l_error_message;
2702 ELSE
2703 x_progress := to_char(SQLCODE)||':'||SQLERRM;
2704 END IF;
2705
2706 XNP_UTILS.NOTIFY_ERROR
2707 (P_PKG_NAME => 'XNP_WF_STANDARD'
2708 ,P_PROC_NAME => 'SET_ORDER_RESULT'
2709 ,P_MSG_NAME => 'SET_ORDER_RESULT_ERR'
2710 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2711 ,P_TOK1 => 'ERROR_TEXT'
2712 ,P_VAL1 => x_progress
2713 );
2714
2715
2716 RAISE;
2717
2718 END SET_ORDER_RESULT;
2719
2720 --------------------------------------------------------------------
2721 -- Checks if this is a subsequent porting
2722 -- request and returns Y/N accordingly
2723 ------------------------------------------------------------------
2724 PROCEDURE SOA_IS_SUBSEQUENT_PORT
2725 (ITEMTYPE IN VARCHAR2
2726 ,ITEMKEY IN VARCHAR2
2727 ,ACTID IN NUMBER
2728 ,FUNCMODE IN VARCHAR2
2729 ,RESULTOUT OUT NOCOPY VARCHAR2
2730 )
2731 IS
2732 x_progress VARCHAR2(2000);
2733 l_WORKITEM_INSTANCE_ID NUMBER;
2734 l_error_code NUMBER := 0;
2735 l_error_message VARCHAR2(2000);
2736 l_CHECK_STATUS VARCHAR2(5);
2737 l_STATUS_TYPE_CODE VARCHAR2(80);
2738 e_SOA_IS_SUBSEQUENT_PORT EXCEPTION;
2739 BEGIN
2740
2741 --
2742 -- RUN mode - normal process execution
2743 --
2744 IF (funcmode = 'RUN') THEN
2745
2746 SET_SDP_CONTEXT
2747 (ITEMTYPE
2748 ,ITEMKEY
2749 ,ACTID
2750 ,'SET_WI'
2751 ,RESULTOUT
2752 );
2753
2754 -- The run code
2755
2756 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
2757
2758 XNP_STANDARD.SOA_IS_SUBSEQUENT_PORT
2759 (l_WORKITEM_INSTANCE_ID
2760 ,l_CHECK_STATUS
2761 ,l_error_code
2762 ,l_error_message
2763 );
2764
2765 IF l_CHECK_STATUS = 'Y' THEN
2766 resultout := 'COMPLETE:Y';
2767 ELSE
2768 resultout := 'COMPLETE:N';
2769 END IF;
2770
2771 RETURN;
2772 END IF;
2773 --
2774 -- CANCEL mode
2775 --
2776 -- This is in the event that the activity must be undone.
2777 --
2778 IF (funcmode = 'CANCEL' ) THEN
2779 -- The cancel code
2780
2781 null;
2782 -- no result needed
2783 resultout := 'COMPLETE';
2784 return;
2785 END IF;
2786
2787 -- For other execution modes: return null
2788
2789 resultout := '';
2790 return;
2791
2792 EXCEPTION
2793 WHEN OTHERS THEN
2794 ------------------------------------------------------------------
2795 -- Record this function call in the error
2796 -- system in case of an exception
2797 ------------------------------------------------------------------
2798
2799
2800 IF (l_error_code <> 0) THEN
2801 x_progress := to_char(l_error_code)||':'||l_error_message;
2802 ELSE
2803 x_progress := to_char(SQLCODE)||':'||SQLERRM;
2804 END IF;
2805
2806 XNP_UTILS.NOTIFY_ERROR
2807 (P_PKG_NAME => 'XNP_WF_STANDARD'
2808 ,P_PROC_NAME => 'SOA_IS_SUBSEQUENT_PORT'
2809 ,P_MSG_NAME => 'SOA_IS_SUBSEQUENT_PORT_ERR'
2810 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
2811 ,P_TOK1 => 'ERROR_TEXT'
2812 ,P_VAL1 => x_progress
2813 );
2814
2815
2816 RAISE;
2817
2818 END SOA_IS_SUBSEQUENT_PORT;
2819
2820 PROCEDURE SEND_MESSAGE
2821 (ITEMTYPE IN VARCHAR2
2822 ,ITEMKEY IN VARCHAR2
2823 ,ACTID IN NUMBER
2824 ,FUNCMODE IN VARCHAR2
2825 ,RESULTOUT OUT NOCOPY VARCHAR2
2826 )
2827 IS
2828 x_progress VARCHAR2(2000);
2829 -- l_WORKITEM_INSTANCE_ID NUMBER := 0;
2830 l_WORKITEM_INSTANCE_ID NUMBER := NULL;
2831 --l_FA_INSTANCE_ID NUMBER := 0;
2832 l_FA_INSTANCE_ID NUMBER := NULL;
2833 l_PORTING_ID VARCHAR2(80) := 0;
2834 l_ORDER_ID NUMBER;
2835 l_REFERENCE_ID NUMBER;
2836 l_EVENT_TYPE VARCHAR2(80);
2837 l_PARAM_LIST VARCHAR2(1024);
2838 l_CONSUMER VARCHAR2(512) := NULL;
2839 l_RECEIVER VARCHAR2(512) := NULL;
2840 l_CALLBACK_REF_ID VARCHAR2(80) := NULL;
2841 l_CALLBACK_REF_ID_NAME VARCHAR2(80) := NULL;
2842 l_VERSION NUMBER := 1;
2843 l_error_code NUMBER := 0;
2844 l_error_message VARCHAR2(2000);
2845 l_ACTIVITY_LABEL VARCHAR2(2000) := NULL;
2846 l_PROCESS_REFERENCE VARCHAR2(2000) := NULL;
2847 l_str VARCHAR2(2000) := NULL;
2848 l_start_pos NUMBER := 0;
2849 l_end_pos NUMBER := 0;
2850 l_version_label VARCHAR2(200) := NULL;
2851 l_tmp_param_list VARCHAR2(2000) := NULL;
2852 l_tmp_callback_ref_id VARCHAR2(2000) := NULL;
2853 l_tmp NUMBER := 0;
2854 e_SEND_MESSAGE EXCEPTION;
2855 BEGIN
2856
2857 --
2858 -- RUN mode - normal process execution
2859 --
2860
2861 IF (funcmode = 'RUN') THEN
2862
2863 -- The run code
2864
2865
2866 SET_SDP_CONTEXT
2867 (ITEMTYPE
2868 ,ITEMKEY
2869 ,ACTID
2870 ,'SET_ORD_WI'
2871 ,RESULTOUT
2872 );
2873
2874 l_ORDER_ID := g_ORDER_ID ;
2875 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
2876
2877 BEGIN
2878
2879 SET_SDP_CONTEXT
2880 (ITEMTYPE
2881 ,ITEMKEY
2882 ,ACTID
2883 ,'SET_FA'
2884 ,RESULTOUT
2885 );
2886
2887 l_FA_INSTANCE_ID := g_FA_INSTANCE_ID;
2888
2889 EXCEPTION
2890 WHEN OTHERS THEN
2891 l_FA_INSTANCE_ID := NULL;
2892 wf_core.clear;
2893 END;
2894
2895
2896 -- Get the event type to publish
2897 l_EVENT_TYPE :=
2898 wf_engine.GetActivityAttrText
2899 (itemtype => itemtype
2900 ,itemkey => itemkey
2901 ,actid => actid
2902 ,aname => 'EVENT_TYPE'
2903 );
2904
2905 -- Get the names of the parameters for SEND_MESSAGE
2906 l_tmp_param_list :=
2907 wf_engine.GetActivityAttrText
2908 (itemtype => itemtype
2909 ,itemkey => itemkey
2910 ,actid => actid
2911 ,aname => 'PARAM_LIST'
2912 );
2913
2914 l_PARAM_LIST := replace(l_tmp_param_list,' ','');
2915
2916 l_CONSUMER :=
2917 wf_engine.GetActivityAttrText
2918 (itemtype => itemtype
2919 ,itemkey => itemkey
2920 ,actid => actid
2921 ,aname => 'CONSUMER'
2922 );
2923
2924 l_RECEIVER :=
2925 wf_engine.GetActivityAttrText
2926 (itemtype => itemtype
2927 ,itemkey => itemkey
2928 ,actid => actid
2929 ,aname => 'RECEIVER'
2930 );
2931
2932 l_ACTIVITY_LABEL := wf_engine.GETACTIVITYLABEL(actid);
2933
2934
2935 -- Create a name for the version label
2936 l_version_label := l_ACTIVITY_LABEL;
2937
2938 l_start_pos := l_end_pos + 1;
2939 l_end_pos := INSTR(l_ACTIVITY_LABEL, ':',l_start_pos,1);
2940
2941 l_start_pos := l_end_pos + 1;
2942 l_end_pos := LENGTH(l_ACTIVITY_LABEL) + 1;
2943
2944 l_version_label := SUBSTR
2945 (l_ACTIVITY_LABEL
2946 , l_start_pos
2947 , (l_end_pos - l_start_pos));
2948
2949
2950 -- Check if the version number is present else add
2951 BEGIN
2952
2953 l_VERSION :=
2954 wf_engine.GetItemAttrNumber
2955 (itemtype => itemtype
2956 ,itemkey => itemkey
2957 ,aname => l_version_label||'_VER'
2958 );
2959
2960 EXCEPTION
2961 WHEN OTHERS THEN
2962 -- Item attr doesn't exist yet, so create it
2963 IF ( WF_CORE.ERROR_NAME = 'WFENG_ITEM_ATTR')
2964 THEN
2965
2966 -- Clear the error buffers
2967 wf_core.clear;
2968
2969 WF_ENGINE.AddItemAttr
2970 (itemtype => itemtype
2971 ,itemkey => itemkey
2972 ,aname => l_version_label||'_VER'
2973 );
2974
2975 l_VERSION := 1; -- Initializing
2976
2977 -- Set the value
2978 wf_engine.SetItemAttrNumber
2979 (itemtype => itemtype
2980 ,itemkey => itemkey
2981 ,aname => l_version_label||'_VER'
2982 ,avalue => l_VERSION
2983 );
2984 wf_core.clear;
2985 ELSE
2986 RAISE;
2987 END IF;
2988 END;
2989
2990 -- Get the callback reference id
2991 XNP_UTILS.CHECK_TO_GET_REF_ID
2992 (p_itemtype => itemtype
2993 ,p_itemkey => itemkey
2994 ,p_actid => actid
2995 ,p_workitem_instance_id => l_workitem_instance_id
2996 ,x_reference_id => l_callback_ref_id
2997 );
2998
2999 -- If the Reference_ID is null make it -1
3000
3001 if l_callback_ref_id is null then
3002 l_callback_ref_id := -1;
3003 end if;
3004
3005 ------------------------------------------------------------------
3006 -- If send succeedes then increment version number
3007 ------------------------------------------------------------------
3008 XNP_STANDARD.SEND_MESSAGE
3009 (l_ORDER_ID
3010 ,l_WORKITEM_INSTANCE_ID
3011 ,l_FA_INSTANCE_ID
3012 ,l_EVENT_TYPE
3013 ,l_PARAM_LIST
3014 ,l_CALLBACK_REF_ID -- Reference id
3015 ,l_CONSUMER
3016 ,l_RECEIVER
3017 ,l_VERSION
3018 ,l_error_code
3019 ,l_error_message
3020 );
3021
3022 IF l_error_code <> 0
3023 THEN
3024 raise e_SEND_MESSAGE;
3025 ELSE
3026 -- Increment the version number and set it
3027 l_VERSION := l_VERSION + 1;
3028 wf_engine.SetItemAttrNumber
3029 (itemtype => itemtype
3030 ,itemkey => itemkey
3031 ,aname => l_version_label||'_VER'
3032 ,avalue => l_VERSION
3033 );
3034 END IF;
3035
3036 resultout := 'COMPLETE';
3037
3038 RETURN;
3039 END IF;
3040 --
3041 -- CANCEL mode
3042 --
3043 -- This is in the event that the activity must be undone.
3044 --
3045 IF (funcmode = 'CANCEL' ) THEN
3046 -- The cancel code
3047
3048 null;
3049 -- no result needed
3050 resultout := 'COMPLETE';
3051 return;
3052 END IF;
3053
3054 -- For other execution modes: return null
3055
3056 resultout := '';
3057 return;
3058
3059 EXCEPTION
3060
3061 WHEN OTHERS THEN
3062 ------------------------------------------------------------------
3063 -- Record this function call in the error
3064 -- system in case of an exception
3065 ------------------------------------------------------------------
3066
3067
3068
3069 IF (l_error_code <> 0) THEN
3070 x_progress := to_char(l_error_code)||':'||l_error_message;
3071 ELSE
3072 x_progress := to_char(SQLCODE)||':'||SQLERRM;
3073 END IF;
3074
3075 XNP_UTILS.NOTIFY_ERROR
3076 (P_PKG_NAME => 'XNP_WF_STANDARD'
3077 ,P_PROC_NAME => 'SEND_MESSAGE'
3078 ,P_MSG_NAME => 'SEND_MESSAGE_ERR'
3079 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
3080 ,P_TOK1 => 'ERROR_TEXT'
3081 ,P_VAL1 => x_progress
3082 );
3083
3084
3085 RAISE;
3086
3087 END SEND_MESSAGE;
3088
3089
3090 --------------------------------------------------------------------
3091 -- Called during deprovisioning phase
3092 --
3093 -- Description:
3094 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
3095 -- Gets the Activity Attribute FEATURE_TYPE
3096 -- Gets the workitem parameters STARTING_NUMBER, ENDING_NUMBER
3097 -- For each of the FEs executes and FA and subscribes for FA_DONE
3098 ------------------------------------------------------------------
3099 PROCEDURE SMS_DEPROVISION_NES
3100 (ITEMTYPE IN VARCHAR2
3101 ,ITEMKEY IN VARCHAR2
3102 ,ACTID IN NUMBER
3103 ,FUNCMODE IN VARCHAR2
3104 ,RESULTOUT OUT NOCOPY VARCHAR2
3105 )
3106 IS
3107 x_progress VARCHAR2(2000);
3108 l_FEATURE_TYPE VARCHAR2(80) := NULL;
3109 --l_FA_INSTANCE_ID NUMBER := 0;
3110 l_FA_INSTANCE_ID NUMBER := NULL;
3111 l_process_reference varchar2(512) := null;
3112 l_activity_name varchar2(240) := null;
3113 l_ORDER_ID NUMBER;
3114 l_WORKITEM_INSTANCE_ID NUMBER;
3115 l_error_code NUMBER := 0;
3116 l_error_message VARCHAR2(2000);
3117 l_STARTING_NUMBER VARCHAR2(80) := NULL;
3118 l_FE_NAME VARCHAR2(200) := NULL;
3119 l_ENDING_NUMBER VARCHAR2(80) := NULL;
3120
3121 CURSOR c_ALL_FEs IS
3122 SELECT FE_ID
3123 FROM XNP_SV_SMS_FE_MAPS
3124 WHERE SV_SMS_ID IN
3125 (SELECT SV_SMS_ID FROM XNP_SV_SMS
3126 WHERE SUBSCRIPTION_TN
3127 BETWEEN l_STARTING_NUMBER AND l_ENDING_NUMBER
3128 )
3129 AND FEATURE_TYPE=l_FEATURE_TYPE
3130 ;
3131
3132 e_SMS_DEPROVISION_NES EXCEPTION;
3133
3134 BEGIN
3135
3136 --
3137 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
3138 --
3139 SET_SDP_CONTEXT
3140 (ITEMTYPE
3141 ,ITEMKEY
3142 ,ACTID
3143 ,'SET_ORD_WI'
3144 ,RESULTOUT
3145 );
3146
3147 --
3148 -- RUN mode - normal process execution
3149 --
3150 IF (funcmode = 'RUN') THEN
3151
3152 -- The run code
3153
3154 l_ORDER_ID := g_ORDER_ID;
3155 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
3156
3157 l_FEATURE_TYPE :=
3158 wf_engine.GetActivityAttrText
3159 (itemtype => itemtype
3160 ,itemkey => itemkey
3161 ,actid => actid
3162 ,aname => 'FEATURE_TYPE'
3163 );
3164
3165 l_STARTING_NUMBER :=
3166 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3167 (l_WORKITEM_INSTANCE_ID
3168 ,'STARTING_NUMBER'
3169 );
3170
3171 l_ENDING_NUMBER :=
3172 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3173 (l_WORKITEM_INSTANCE_ID
3174 ,'ENDING_NUMBER'
3175 );
3176
3177
3178 ------------------------------------------------------------------
3179 -- For each of the fe execute an FA
3180 -- for the deprovisioning and subscribe for FA_DONE
3181 ------------------------------------------------------------------
3182
3183 FOR l_tmp_fe IN c_ALL_FEs LOOP
3184
3185 /*****
3186 XNP_UTILS.GET_FE_NAME
3187 (p_FE_ID=>l_TMP_FE.FE_ID
3188 ,x_FE_NAME=>l_FE_NAME
3189 ,x_ERROR_CODE=>l_ERROR_CODE
3190 ,x_ERROR_MESSAGE=>l_ERROR_MESSAGE
3191 );
3192 IF l_error_code <> 0
3193 THEN
3194 raise e_SMS_DEPROVISION_NES;
3195 END IF;
3196 ****/
3197 ------------------------------------------------------------------
3198 -- Add the FA to the workitem and get the FA instance id
3199 ------------------------------------------------------------------
3200 l_FA_INSTANCE_ID :=
3201 XDP_ENG_UTIL.ADD_FA_TOWI
3202 (l_WORKITEM_INSTANCE_ID
3203 ,'DEPROVISION_'||l_FEATURE_TYPE -- the FA
3204 ,l_tmp_fe.fe_id
3205 );
3206
3207
3208 -- Call fa exection procedure
3209 xdp_eng_util.execute_fa
3210 (p_order_id=>l_order_id
3211 ,p_wi_instance_id=>l_workitem_instance_id
3212 ,p_fa_instance_id=>l_fa_instance_id
3213 ,p_wi_item_type=>itemtype
3214 ,p_wi_item_key=>itemkey
3215 ,p_return_code=>l_error_code
3216 ,p_error_description=>l_error_message
3217 ,p_fa_caller=>'INTERNAL'
3218 );
3219
3220 IF l_error_code <> 0
3221 THEN
3222 raise e_SMS_DEPROVISION_NES;
3223 END IF;
3224
3225 -- SUBSCRIBE for FA_DONE
3226 -- with the FA_INSTANCE_ID
3227 -- and let SFM resume workflow
3228 -- append FEATURE_TYPE and FE_ID to the process reference
3229 l_PROCESS_REFERENCE :=
3230 itemtype||':'||itemkey||':'||'DEPROV:'||l_FEATURE_TYPE||':'||to_char(l_tmp_fe.FE_ID);
3231
3232 XNP_EVENT.SUBSCRIBE
3233 (P_MSG_CODE=>'FA_DONE' -- Message type to expected
3234 ,P_REFERENCE_ID=>l_FA_INSTANCE_ID -- Reference id
3235 ,P_PROCESS_REFERENCE=>l_PROCESS_REFERENCE -- workflow id
3236 ,P_PROCEDURE_NAME=>'XNP_FA_CB.PROCESS_FA_DONE' -- callback proc
3237 ,P_CALLBACK_TYPE=>'PL/SQL' -- callback proc type
3238 ,P_CLOSE_REQD_FLAG => 'Y'
3239 ,P_ORDER_ID=>l_order_id
3240 ,P_WI_INSTANCE_ID=>l_workitem_instance_id
3241 ,P_FA_INSTANCE_ID=>l_FA_INSTANCE_ID
3242 );
3243
3244 END LOOP;
3245
3246 resultout := 'COMPLETE';
3247
3248 RETURN;
3249
3250 END IF;
3251 --
3252 -- CANCEL mode
3253 --
3254 -- This is in the event that the activity must be undone.
3255 --
3256 IF (funcmode = 'CANCEL' ) THEN
3257 -- The cancel code
3258
3259 null;
3260 -- no result needed
3261 resultout := 'COMPLETE';
3262 return;
3263 END IF;
3264
3265 -- For other execution modes: return null
3266
3267 resultout := '';
3268 return;
3269
3270 EXCEPTION
3271 WHEN OTHERS THEN
3272 ------------------------------------------------------------------
3273 -- Record this function call in the error
3274 -- system in case of an exception
3275 ------------------------------------------------------------------
3276
3277
3278 IF (l_error_code <> 0) THEN
3279 x_progress := to_char(l_error_code)||':'||l_error_message;
3280 ELSE
3281 x_progress := to_char(SQLCODE)||':'||SQLERRM;
3282 END IF;
3283
3284 XNP_UTILS.NOTIFY_ERROR
3285 (P_PKG_NAME => 'XNP_WF_STANDARD'
3286 ,P_PROC_NAME => 'SMS_DEPROVISION_NES'
3287 ,P_MSG_NAME => 'SMS_DEPROVISION_NES_ERR'
3288 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
3289 ,P_TOK1 => 'ERROR_TEXT'
3290 ,P_VAL1 => x_progress
3291 );
3292
3293 RAISE;
3294
3295 END SMS_DEPROVISION_NES;
3296
3297 --------------------------------------------------------------------
3298 -- Called when: During provisioning phase of the order
3299 -- Called by:
3300 -- Description:
3301 -- Deletes mapping rows from the SMS sv id and the fe id
3302 -- for the feature type
3303 -- Gets values of the item attributes workitem instance id
3304 -- and fe id and calls XNP_STANDARD.SMS_DELETE_FE_MAP
3305 ------------------------------------------------------------------
3306 PROCEDURE SMS_DELETE_FE_MAP
3307 (ITEMTYPE IN VARCHAR2
3308 ,ITEMKEY IN VARCHAR2
3309 ,ACTID IN NUMBER
3310 ,FUNCMODE IN VARCHAR2
3311 ,RESULTOUT OUT NOCOPY VARCHAR2
3312 )
3313 IS
3314 x_progress VARCHAR2(2000);
3315 l_WORKITEM_INSTANCE_ID NUMBER;
3316 l_error_code NUMBER := 0;
3317 l_error_message VARCHAR2(2000);
3318 l_FE_ID NUMBER;
3319 l_FEATURE_TYPE VARCHAR2(80) := NULL;
3320 e_SMS_DELETE_FE_MAP EXCEPTION;
3321 BEGIN
3322
3323 --
3324 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
3325 --
3326 SET_SDP_CONTEXT
3327 (ITEMTYPE
3328 ,ITEMKEY
3329 ,ACTID
3330 ,'SET_WI'
3331 ,RESULTOUT
3332 );
3333
3334 --
3335 -- RUN mode - normal process execution
3336 --
3337 IF (funcmode = 'RUN') THEN
3338
3339 -- The run code
3340
3341 -- Get the time out duration and set at Item attribute
3342
3343 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
3344
3345 l_FE_ID :=
3346 wf_engine.GetItemAttrNumber (itemtype => itemtype,
3347 itemkey => itemkey,
3348 aname => 'FE_ID');
3349
3350 l_FEATURE_TYPE :=
3351 wf_engine.GetActivityAttrText (itemtype => itemtype,
3352 itemkey => itemkey,
3353 actid => actid,
3354 aname => 'FEATURE_TYPE');
3355
3356 -- Delete the fe map
3357 XNP_STANDARD.SMS_DELETE_FE_MAP
3358 (l_WORKITEM_INSTANCE_ID
3359 ,l_FE_ID
3360 ,l_FEATURE_TYPE
3361 ,l_error_code
3362 ,l_error_message
3363 );
3364
3365 IF l_error_code <> 0
3366 THEN
3367 raise e_SMS_DELETE_FE_MAP;
3368 END IF;
3369
3370 resultout := 'COMPLETE';
3371
3372 RETURN;
3373 END IF;
3374 --
3375 -- CANCEL mode
3376 --
3377 -- This is in the event that the activity must be undone.
3378 --
3379 IF (funcmode = 'CANCEL' ) THEN
3380 -- The cancel code
3381
3382 null;
3383 -- no result needed
3384 resultout := 'COMPLETE';
3385 return;
3386 END IF;
3387
3388 -- For other execution modes: return null
3389
3390 resultout := '';
3391 return;
3392
3393 EXCEPTION
3394 WHEN OTHERS THEN
3395 ------------------------------------------------------------------
3396 -- Record this function call in the error
3397 -- system in case of an exception
3398 ------------------------------------------------------------------
3399
3400
3401 IF (l_error_code <> 0) THEN
3402 x_progress := to_char(l_error_code)||':'||l_error_message;
3403 ELSE
3404 x_progress := to_char(SQLCODE)||':'||SQLERRM;
3405 END IF;
3406
3407 XNP_UTILS.NOTIFY_ERROR
3408 (P_PKG_NAME => 'XNP_WF_STANDARD'
3409 ,P_PROC_NAME => 'SMS_DELETE_FE_MAP'
3410 ,P_MSG_NAME => 'SMS_DELETE_FE_MAP_ERR'
3411 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
3412 ,P_TOK1 => 'ERROR_TEXT'
3413 ,P_VAL1 => x_progress
3414 );
3415
3416 RAISE;
3417
3418 END SMS_DELETE_FE_MAP;
3419
3420 --------------------------------------------------------------------
3421 -- Description: Checks if there exists a
3422 -- SV for the given TN range in that phase
3423 -- and in for the given SP Role i.e. as donor
3424 -- or recipient
3425 -- calls XNP_STANDARD.CHECK_PHASE_FOR_ROLE
3426 -- Completes activity with 'Y' or 'N'
3427 ------------------------------------------------------------------
3428 PROCEDURE CHECK_PHASE_FOR_ROLE
3429 (ITEMTYPE IN VARCHAR2
3430 ,ITEMKEY IN VARCHAR2
3431 ,ACTID IN NUMBER
3432 ,FUNCMODE IN VARCHAR2
3433 ,RESULTOUT OUT NOCOPY VARCHAR2
3434 )
3435 IS
3436 x_progress VARCHAR2(2000);
3437 l_CHECK_STATUS VARCHAR2(1);
3438 l_WORKITEM_INSTANCE_ID NUMBER;
3439 l_SP_ROLE VARCHAR2(80);
3440 l_PHASE_INDICATOR VARCHAR2(80);
3441 l_ERROR_CODE NUMBER := 0;
3442 l_ERROR_MESSAGE VARCHAR2(2000);
3443 e_CHECK_PHASE_FOR_ROLE EXCEPTION;
3444 BEGIN
3445
3446 --
3447 -- RUN mode - normal process execution
3448 --
3449 IF (funcmode = 'RUN') THEN
3450
3451 -- The run code
3452
3453 SET_SDP_CONTEXT
3454 (ITEMTYPE
3455 ,ITEMKEY
3456 ,ACTID
3457 ,'SET_WI'
3458 ,RESULTOUT
3459 );
3460
3461 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
3462
3463 -- Get the context this workflow is operating in
3464 -- i.e. DONOR or RECIPIENT
3465 l_SP_ROLE :=
3466 wf_engine.GetActivityAttrText (itemtype => itemtype,
3467 itemkey => itemkey,
3468 actid => actid,
3469 aname => 'SP_ROLE');
3470
3471 if (l_sp_role IS NULL) then
3472 raise e_CHECK_PHASE_FOR_ROLE;
3473 end if;
3474
3475 -- Get the Phase that the SV should be in
3476 l_PHASE_INDICATOR :=
3477 wf_engine.GetActivityAttrText
3478 (itemtype => itemtype
3479 ,itemkey => itemkey
3480 ,actid => actid
3481 ,aname => 'PHASE'
3482 );
3483
3484 -- Check if an SV exists in that phase
3485 XNP_STANDARD.CHECK_PHASE_FOR_ROLE
3486 (l_WORKITEM_INSTANCE_ID
3487 ,l_SP_ROLE
3488 ,l_PHASE_INDICATOR
3489 ,l_CHECK_STATUS
3490 ,l_error_code
3491 ,l_error_message
3492 );
3493
3494 IF l_error_code <> 0 THEN
3495 -- if error then disallow
3496 raise e_CHECK_PHASE_FOR_ROLE;
3497 END IF;
3498
3499
3500 -- Completion: If check status is 'Y'
3501 -- the 'YES' path else trace the 'NO' path
3502
3503 IF l_check_status = 'Y' THEN
3504 resultout := 'COMPLETE:Y';
3505 ELSE
3506 resultout := 'COMPLETE:N';
3507 END IF;
3508 RETURN;
3509
3510 END IF;
3511 --
3512 -- CANCEL mode
3513 --
3514 -- This is in the event that the activity must be undone.
3515 --
3516 IF (funcmode = 'CANCEL' ) THEN
3517 -- The cancel code
3518
3519 null;
3520 -- no result needed
3521 resultout := 'COMPLETE';
3522 return;
3523 END IF;
3524
3525 -- For other execution modes: return null
3526
3527 resultout := '';
3528 return;
3529
3530 EXCEPTION
3531 WHEN OTHERS THEN
3532 ------------------------------------------------------------------
3533 -- Record this function call in the error
3534 -- system in case of an exception
3535 ------------------------------------------------------------------
3536
3537
3538
3539 IF (l_error_code <> 0) THEN
3540 x_progress := to_char(l_error_code)||':'||l_error_message;
3541 ELSE
3542 x_progress := to_char(SQLCODE)||':'||SQLERRM;
3543 END IF;
3544
3545 XNP_UTILS.NOTIFY_ERROR
3546 (P_PKG_NAME => 'XNP_WF_STANDARD'
3547 ,P_PROC_NAME => 'CHECK_PHASE_FOR_ROLE'
3548 ,P_MSG_NAME => 'CHECK_PHASE_FOR_ROLE_ERR'
3549 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
3550 ,P_TOK1 => 'ERROR_TEXT'
3551 ,P_VAL1 => x_progress
3552 );
3553
3554 RAISE;
3555
3556 END CHECK_PHASE_FOR_ROLE;
3557
3558 ------------------------------------------------------------------
3559 -- Updates the status of the provisioning FE map
3560 -- to the given status for the given FE
3561 -- The value of the FE id is an item attribute
3562 -- and the value of the status is an activity attr
3563 ------------------------------------------------------------------
3564 PROCEDURE SMS_UPDATE_FE_MAP_STATUS
3565 (ITEMTYPE IN VARCHAR2
3566 ,ITEMKEY IN VARCHAR2
3567 ,ACTID IN NUMBER
3568 ,FUNCMODE IN VARCHAR2
3569 ,RESULTOUT OUT NOCOPY VARCHAR2
3570 )
3571 IS
3572 l_ORDER_ID NUMBER;
3573 l_LINEITEM_ID NUMBER;
3574 l_WORKITEM_INSTANCE_ID NUMBER := NULL;
3575 l_FA_INSTANCE_ID NUMBER;
3576 l_PROV_STATUS VARCHAR2(80) := NULL;
3577 l_FE_ID NUMBER := 0;
3578 l_FEATURE_TYPE VARCHAR2(80) := NULL;
3579 e_SMS_UPDATE_FE_MAP_STATUS EXCEPTION;
3580 X_PROGRESS VARCHAR2(2000);
3581 l_ERROR_CODE NUMBER:=0;
3582 l_ERROR_MESSAGE VARCHAR2(2000):=0;
3583
3584 BEGIN
3585
3586 --
3587 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
3588 --
3589 SET_SDP_CONTEXT
3590 (ITEMTYPE
3591 ,ITEMKEY
3592 ,ACTID
3593 ,'SET_ORD_WI'
3594 ,RESULTOUT
3595 );
3596
3597 --
3598 -- RUN mode - normal process execution
3599 --
3600 IF (funcmode = 'RUN') THEN
3601
3602 -- The run code
3603
3604 l_ORDER_ID := g_ORDER_ID;
3605 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
3606
3607 l_FE_ID :=
3608 wf_engine.GetItemAttrNumber
3609 (itemtype => itemtype,
3610 itemkey => itemkey,
3611 aname => 'FE_ID'
3612 );
3613
3614 l_FEATURE_TYPE :=
3615 wf_engine.GetItemAttrText
3616 (itemtype => itemtype,
3617 itemkey => itemkey,
3618 aname => 'FEATURE_TYPE'
3619 );
3620
3621 l_PROV_STATUS :=
3622 wf_engine.GetActivityAttrText (itemtype => itemtype,
3623 itemkey => itemkey,
3624 actid => actid,
3625 aname => 'PROV_STATUS');
3626
3627 XNP_STANDARD.SMS_UPDATE_FE_MAP_STATUS
3628 (l_ORDER_ID ,
3629 l_LINEITEM_ID,
3630 l_WORKITEM_INSTANCE_ID,
3631 l_FA_INSTANCE_ID,
3632 l_FEATURE_TYPE,
3633 l_FE_ID,
3634 l_PROV_STATUS,
3635 l_error_code,
3636 l_error_message
3637 );
3638
3639 IF l_error_code <> 0
3640 THEN
3641 raise e_SMS_UPDATE_FE_MAP_STATUS;
3642 END IF;
3643 -- Completion
3644 resultout := 'COMPLETE';
3645 RETURN;
3646 END IF;
3647 --
3648 -- CANCEL mode
3649 --
3650 -- This is in the event that the activity must be undone.
3651 --
3652 IF (funcmode = 'CANCEL' ) THEN
3653 -- The cancel code
3654
3655 null;
3656 -- no result needed
3657 resultout := 'COMPLETE';
3658 return;
3659 END IF;
3660
3661 -- For other execution modes: return null
3662
3663 resultout := '';
3664 return;
3665
3666 EXCEPTION
3667 WHEN OTHERS THEN
3668 ------------------------------------------------------------------
3669 -- Record this function call in the error
3670 -- system in case of an exception
3671 ------------------------------------------------------------------
3672
3673
3674 IF (l_error_code <> 0) THEN
3675 x_progress := to_char(l_error_code)||':'||l_error_message;
3676 ELSE
3677 x_progress := to_char(SQLCODE)||':'||SQLERRM;
3678 END IF;
3679
3680 XNP_UTILS.NOTIFY_ERROR
3681 (P_PKG_NAME => 'XNP_WF_STANDARD'
3682 ,P_PROC_NAME => 'SMS_UPDATE_FE_MAP_STATUS'
3683 ,P_MSG_NAME => 'SMS_UPDATE_FE_MAP_STATUS_ERR'
3684 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
3685 ,P_TOK1 => 'ERROR_TEXT'
3686 ,P_VAL1 => x_progress
3687 );
3688
3689 RAISE;
3690 END SMS_UPDATE_FE_MAP_STATUS;
3691
3692 PROCEDURE REJECT_MESSAGE
3693 (ITEMTYPE IN VARCHAR2
3694 ,ITEMKEY IN VARCHAR2
3695 ,ACTID IN NUMBER
3696 ,FUNCMODE IN VARCHAR2
3697 ,RESULTOUT OUT NOCOPY VARCHAR2
3698 )
3699 IS
3700 l_MSG_ID NUMBER := 0;
3701 l_COMMENT VARCHAR2(4000) := NULL;
3702 e_REJECT_MESSAGE EXCEPTION;
3703 x_progress VARCHAR2(2000);
3704 BEGIN
3705
3706 --
3707 -- RUN mode - normal process execution
3708 --
3709 IF (funcmode = 'RUN') THEN
3710
3711 -- The run code
3712
3713 l_MSG_ID :=
3714 wf_engine.GetItemAttrNumber
3715 (itemtype => itemtype,
3716 itemkey => itemkey,
3717 aname => 'MSG_ID'
3718 );
3719
3720 l_COMMENT :=
3721 wf_engine.GetItemAttrText
3722 (itemtype => itemtype,
3723 itemkey => itemkey,
3724 aname => 'COMMENT'
3725 );
3726
3727 XNP_MESSAGE.UPDATE_STATUS
3728 (l_MSG_ID
3729 ,'REJECTED'
3730 ,l_COMMENT
3731 );
3732
3733 -- Completion
3734 resultout := 'COMPLETE';
3735 RETURN;
3736 END IF;
3737 --
3738 -- CANCEL mode
3739 --
3740 -- This is in the event that the activity must be undone.
3741 --
3742 IF (funcmode = 'CANCEL' ) THEN
3743 -- The cancel code
3744
3745 null;
3746 -- no result needed
3747 resultout := 'COMPLETE';
3748 return;
3749 END IF;
3750
3751 -- For other execution modes: return null
3752
3753 resultout := '';
3754 return;
3755
3756 EXCEPTION
3757 WHEN OTHERS THEN
3758 ------------------------------------------------------------------
3759 -- Record this function call in the error
3760 -- system in case of an exception
3761 ------------------------------------------------------------------
3762
3763 fnd_message.set_name('XNP','STD_ERROR');
3764 fnd_message.set_token(
3765 'ERROR_LOCN','XNP_WF_STANDARD.REJECT_MESSAGE');
3766 fnd_message.set_token('ERROR_TEXT',
3767 ':'||to_char(SQLCODE)||':'||SQLERRM);
3768 x_progress := fnd_message.get;
3769 wf_core.context(
3770 'XNP_WF_STANDARD'
3771 , 'REJECT_MESSAGE'
3772 ,itemtype
3773 , itemkey
3774 , to_char(actid)
3775 , funcmode
3776 ,x_progress);
3777
3778 RAISE;
3779 END REJECT_MESSAGE;
3780
3781
3782 PROCEDURE RETRY_MESSAGE
3783 (ITEMTYPE IN VARCHAR2
3784 ,ITEMKEY IN VARCHAR2
3785 ,ACTID IN NUMBER
3786 ,FUNCMODE IN VARCHAR2
3787 ,RESULTOUT OUT NOCOPY VARCHAR2
3788 )
3789 IS
3790 l_MSG_ID NUMBER := 0;
3791 l_COMMENT VARCHAR2(4000) := NULL;
3792 e_RETRY_MESSAGE EXCEPTION;
3793 x_progress VARCHAR2(2000);
3794 BEGIN
3795
3796 --
3797 -- RUN mode - normal process execution
3798 --
3799 IF (funcmode = 'RUN') THEN
3800
3801 -- The run code
3802
3803 l_MSG_ID :=
3804 wf_engine.GetItemAttrNumber
3805 (itemtype => itemtype,
3806 itemkey => itemkey,
3807 aname => 'MSG_ID'
3808 );
3809
3810 XNP_MESSAGE.FIX
3811 (l_MSG_ID
3812 );
3813
3814 -- Completion
3815 resultout := 'COMPLETE';
3816 RETURN;
3817 END IF;
3818 --
3819 -- CANCEL mode
3820 --
3821 -- This is in the event that the activity must be undone.
3822 --
3823 IF (funcmode = 'CANCEL' ) THEN
3824 -- The cancel code
3825
3826 null;
3827 -- no result needed
3828 resultout := 'COMPLETE';
3829 return;
3830 END IF;
3831
3832 -- For other execution modes: return null
3833
3834 resultout := '';
3835 return;
3836
3837 EXCEPTION
3838 WHEN OTHERS THEN
3839 ------------------------------------------------------------------
3840 -- Record this function call in the error
3841 -- system in case of an exception
3842 ------------------------------------------------------------------
3843
3844 fnd_message.set_name('XNP','STD_ERROR');
3845 fnd_message.set_token(
3846 'ERROR_LOCN','XNP_WF_STANDARD.RETRY_MESSAGE');
3847 fnd_message.set_token('ERROR_TEXT',
3848 ':'||to_char(SQLCODE)||':'||SQLERRM);
3849 x_progress := fnd_message.get;
3850 wf_core.context(
3851 'XNP_WF_STANDARD'
3852 , 'RETRY_MESSAGE'
3853 ,itemtype
3854 , itemkey
3855 , to_char(actid)
3856 , funcmode
3857 ,x_progress);
3858
3859 RAISE;
3860 END RETRY_MESSAGE;
3861
3862
3863 Procedure WAITFORFLOW (itemtype in varchar2,
3864 itemkey in varchar2,
3865 actid in number,
3866 funcmode in varchar2,
3867 resultout out NOCOPY varchar2)
3868 is
3869 l_ActLabel varchar2(240);
3870 l_ColonLoc number := 0;
3871 e_InvalidLabelException EXCEPTION;
3872 BEGIN
3873
3874 IF funcmode = 'RUN' THEN
3875 l_ActLabel := wf_engine.GetActivityLabel(actid);
3876 l_ColonLoc := INSTR(l_ActLabel,':');
3877 if l_ColonLoc > 0 then
3878 l_ActLabel := SUBSTR(l_ActLabel,l_ColonLoc + 1, LENGTH(l_ActLabel));
3879
3880 -------------------------------------------------
3881 -- Call the WAITFORFLOW API
3882 -------------------------------------------------
3883 XDP_UTILITIES.WAITFORFLOW(itemtype, itemkey, l_ActLabel);
3884
3885 --Set status to notified
3886 resultout := 'NOTIFIED';
3887
3888 return;
3889
3890 elsif l_ActLabel is not null then
3891 null;
3892 else
3893 RAISE e_InvalidLabelException;
3894 end if;
3895
3896
3897 END IF;
3898
3899 IF (funcmode = 'CANCEL') THEN
3900 resultout := 'COMPLETE';
3901 END IF;
3902
3903 IF (funcmode = 'RESPOND') THEN
3904 resultout := 'COMPLETE';
3905 END IF;
3906
3907 IF (funcmode = 'FORWARD') THEN
3908 resultout := 'COMPLETE';
3909 END IF;
3910
3911 IF (funcmode = 'TRANSFER') THEN
3912 resultout := 'COMPLETE';
3913 END IF;
3914
3915 IF (funcmode = 'TIMEOUT') THEN
3916 resultout := 'COMPLETE';
3917 END IF;
3918
3919 IF (funcmode = 'others') THEN
3920 resultout := 'COMPLETE';
3921 END IF;
3922
3923 EXCEPTION
3924 WHEN OTHERS THEN
3925 wf_core.context('XNP_WF_STANDARD','WAITFORFLOW', itemtype, itemkey, to_char(actid), SUBSTR(SQLERRM,1,1500));
3926 raise;
3927 END WAITFORFLOW;
3928
3929
3930
3931
3932 Procedure CONTINUEFLOW (itemtype in varchar2,
3933 itemkey in varchar2,
3934 actid in number,
3935 funcmode in varchar2,
3936 resultout out NOCOPY varchar2)
3937
3938 is
3939 BEGIN
3940 IF (funcmode = 'RUN' ) THEN
3941 XDP_UTILITIES.CONTINUEFLOW(itemtype, itemkey);
3942 resultout := 'COMPLETE';
3943 return;
3944 END IF;
3945
3946 IF (funcmode = 'CANCEL') THEN
3947 resultout := 'COMPLETE';
3948 END IF;
3949
3950 IF (funcmode = 'RESPOND') THEN
3951 resultout := 'COMPLETE';
3952 END IF;
3953
3954 IF (funcmode = 'FORWARD') THEN
3955 resultout := 'COMPLETE';
3956 END IF;
3957
3958 IF (funcmode = 'TRANSFER') THEN
3959 resultout := 'COMPLETE';
3960 END IF;
3961
3962 IF (funcmode = 'TIMEOUT') THEN
3963 resultout := 'COMPLETE';
3964 END IF;
3965
3966 IF (funcmode = 'others') THEN
3967 resultout := 'COMPLETE';
3968 END IF;
3969
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 wf_core.context('XNP_WF_STANDARD','CONTINUEFLOW', itemtype, itemkey, to_char(actid), SUBSTR(SQLERRM,1,1500));
3973 raise;
3974 END CONTINUEFLOW;
3975
3976
3977 --------------------------------------------------------------------
3978 -- Sets the Locked flag to the given value
3979 -- for the enties in xnp_sv_soa for the given
3980 -- PORTING_ID workitem paramter.
3981 -- Values: 'Y' or 'N'
3982 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
3983 -- Activity Attr : Gets the value of Activity Attribute FLAG_VALUE
3984 -- Workitem Paramters : PORTING_ID
3985 -- Calls XNP_CORE.SOA_SET_LOCKED_FLAG
3986 --
3987 ------------------------------------------------------------------
3988 PROCEDURE SOA_SET_LOCKED_FLAG
3989 (ITEMTYPE IN VARCHAR2
3990 ,ITEMKEY IN VARCHAR2
3991 ,ACTID IN NUMBER
3992 ,FUNCMODE IN VARCHAR2
3993 ,RESULTOUT OUT NOCOPY VARCHAR2
3994 )
3995 IS
3996 l_ORDER_ID NUMBER;
3997 l_LINEITEM_ID NUMBER;
3998 l_WORKITEM_INSTANCE_ID NUMBER;
3999 l_FA_INSTANCE_ID NUMBER;
4000 l_error_code NUMBER := 0;
4001 l_error_message VARCHAR2(2000);
4002 l_porting_id VARCHAR2(80);
4003 l_flag_value VARCHAR2(1);
4004 l_sp_name VARCHAR2(80) := null;
4005 l_local_sp_id NUMBER := 0;
4006 x_progress VARCHAR2(2000);
4007 e_SOA_SET_LOCKED_FLAG EXCEPTION;
4008
4009 BEGIN
4010
4011 --
4012 -- RUN mode - normal process execution
4013 --
4014 IF (funcmode = 'RUN') THEN
4015
4016 -- The run code
4017
4018 SET_SDP_CONTEXT
4019 (ITEMTYPE
4020 ,ITEMKEY
4021 ,ACTID
4022 ,'SET_ORD_WI'
4023 ,RESULTOUT
4024 );
4025
4026
4027 l_ORDER_ID := g_WORKITEM_INSTANCE_ID;
4028 l_WORKITEM_INSTANCE_ID := g_ORDER_ID;
4029
4030 l_PORTING_ID :=
4031 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4032 (l_WORKITEM_INSTANCE_ID
4033 ,'PORTING_ID'
4034 );
4035
4036 l_sp_name :=
4037 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4038 (l_WORKITEM_INSTANCE_ID
4039 ,'SP_NAME'
4040 );
4041
4042 l_flag_value :=
4043 wf_engine.GetActivityAttrText (itemtype => itemtype,
4044 itemkey => itemkey,
4045 actid => actid,
4046 aname => 'FLAG_VALUE');
4047
4048 -- Set the locked flag
4049
4050 XNP_CORE.SOA_SET_LOCKED_FLAG
4051 (p_order_id => l_order_id ,
4052 p_lineitem_id => l_lineitem_id,
4053 p_workitem_instance_id => l_workitem_instance_id,
4054 p_fa_instance_id => l_fa_instance_id,
4055 p_porting_id =>l_porting_id,
4056 p_local_sp_id =>l_local_sp_id,
4057 p_locked_flag =>l_flag_value,
4058 x_error_code =>l_error_code,
4059 x_error_message =>l_error_message
4060 );
4061
4062 IF l_error_code <> 0 THEN
4063 raise e_SOA_SET_LOCKED_FLAG;
4064 END IF;
4065
4066 -- Completion
4067 resultout := 'COMPLETE';
4068 RETURN;
4069 END IF;
4070 --
4071 -- CANCEL mode
4072 --
4073 -- This is in the event that the activity must be undone.
4074 --
4075 IF (funcmode = 'CANCEL' ) THEN
4076 -- The cancel code
4077
4078 null;
4079 -- no result needed
4080 resultout := 'COMPLETE';
4081 return;
4082 END IF;
4083
4084 -- For other execution modes: return null
4085
4086 resultout := '';
4087 return;
4088
4089 EXCEPTION
4090 WHEN OTHERS THEN
4091 ------------------------------------------------------------------
4092 -- Record this function call in the error
4093 -- system in case of an exception
4094 ------------------------------------------------------------------
4095
4096
4097 IF (l_error_code <> 0) THEN
4098 x_progress := to_char(l_error_code)||':'||l_error_message;
4099 ELSE
4100 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4101 END IF;
4102
4103 XNP_UTILS.NOTIFY_ERROR
4104 (P_PKG_NAME => 'XNP_WF_STANDARD'
4105 ,P_PROC_NAME => 'SOA_SET_LOCKED_FLAG'
4106 ,P_MSG_NAME => 'SOA_SET_LOCKED_FLAG_ERR'
4107 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4108 ,P_TOK1 => 'ERROR_TEXT'
4109 ,P_VAL1 => x_progress
4110 );
4111
4112 RAISE;
4113 END SOA_SET_LOCKED_FLAG;
4114
4115
4116 --------------------------------------------------------------------
4117 -- Gets the Locked flag for the given
4118 -- PORTING_ID workitem paramter.
4119 -- The activity is completed with the flag value
4120 -- Values: 'Y' or 'N'
4121 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
4122 -- Workitem Paramters : PORTING_ID
4123 -- Calls XNP_CORE.SOA_GET_LOCKED_FLAG
4124 --
4125 ------------------------------------------------------------------
4126 PROCEDURE SOA_GET_LOCKED_FLAG
4127 (ITEMTYPE IN VARCHAR2
4128 ,ITEMKEY IN VARCHAR2
4129 ,ACTID IN NUMBER
4130 ,FUNCMODE IN VARCHAR2
4131 ,RESULTOUT OUT NOCOPY VARCHAR2
4132 )
4133 IS
4134 l_WORKITEM_INSTANCE_ID NUMBER;
4135 l_error_code NUMBER := 0;
4136 l_error_message VARCHAR2(2000);
4137 l_porting_id VARCHAR2(80);
4138 l_sp_name VARCHAR2(80);
4139 l_local_sp_id number := 0;
4140 l_flag_value VARCHAR2(1);
4141 x_progress VARCHAR2(2000);
4142 e_SOA_GET_LOCKED_FLAG EXCEPTION;
4143 BEGIN
4144
4145 --
4146 -- RUN mode - normal process execution
4147 --
4148 IF (funcmode = 'RUN') THEN
4149
4150 -- The run code
4151
4152 SET_SDP_CONTEXT
4153 (ITEMTYPE
4154 ,ITEMKEY
4155 ,ACTID
4156 ,'SET_WI'
4157 ,RESULTOUT
4158 );
4159
4160 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
4161
4162 l_PORTING_ID :=
4163 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4164 (l_WORKITEM_INSTANCE_ID
4165 ,'PORTING_ID'
4166 );
4167
4168 l_sp_name :=
4169 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4170 (l_WORKITEM_INSTANCE_ID
4171 ,'SP_NAME'
4172 );
4173
4174 -- Get the locked flag
4175 XNP_CORE.SOA_GET_LOCKED_FLAG
4176 (p_porting_id=>l_porting_id
4177 ,p_local_sp_id=>l_local_sp_id
4178 ,x_locked_flag=>l_flag_value
4179 ,x_error_code=>l_error_code
4180 ,x_error_message=>l_error_message
4181 );
4182
4183 IF l_error_code <> 0 THEN
4184 raise e_SOA_GET_LOCKED_FLAG;
4185 END IF;
4186
4187 -- Completion
4188 resultout := 'COMPLETE:'||l_flag_value;
4189 RETURN;
4190 END IF;
4191 --
4192 -- CANCEL mode
4193 --
4194 -- This is in the event that the activity must be undone.
4195 --
4196 IF (funcmode = 'CANCEL' ) THEN
4197 -- The cancel code
4198
4199 null;
4200 -- no result needed
4201 resultout := 'COMPLETE';
4202 return;
4203 END IF;
4204
4205 -- For other execution modes: return null
4206
4207 resultout := '';
4208 return;
4209
4210 EXCEPTION
4211 WHEN OTHERS THEN
4212 ------------------------------------------------------------------
4213 -- Record this function call in the error
4214 -- system in case of an exception
4215 ------------------------------------------------------------------
4216
4217
4218 IF (l_error_code <> 0) THEN
4219 x_progress := to_char(l_error_code)||':'||l_error_message;
4220 ELSE
4221 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4222 END IF;
4223
4224 XNP_UTILS.NOTIFY_ERROR
4225 (P_PKG_NAME => 'XNP_WF_STANDARD'
4226 ,P_PROC_NAME => 'SOA_GET_LOCKED_FLAG'
4227 ,P_MSG_NAME => 'SOA_GET_LOCKED_FLAG_ERR'
4228 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4229 ,P_TOK1 => 'ERROR_TEXT'
4230 ,P_VAL1 => x_progress
4231 );
4232
4233 RAISE;
4234 END SOA_GET_LOCKED_FLAG;
4235
4236 --------------------------------------------------------------------
4237 -- Checks if the STATUS_TYPE_CODE from xnp_sv_soa for the
4238 -- given PORTING_ID aka object_reference is same as
4239 -- given status type code (in STATUS_TO_COMPARE_WITH)
4240 -- Returns: 'T' if statuses match, 'F' if they don't
4241 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
4242 -- Activity Attribute : STATUS_TO_COMPARE_WITH
4243 -- Workitem Paramters : PORTING_ID
4244 -- Calls XNP_CORE.SOA_CHECK_SV_STATUS
4245 --
4246 ------------------------------------------------------------------
4247 PROCEDURE SOA_CHECK_SV_STATUS
4248 (ITEMTYPE IN VARCHAR2
4249 ,ITEMKEY IN VARCHAR2
4250 ,ACTID IN NUMBER
4251 ,FUNCMODE IN VARCHAR2
4252 ,RESULTOUT OUT NOCOPY VARCHAR2
4253 )
4254 IS
4255 l_WORKITEM_INSTANCE_ID NUMBER;
4256 l_error_code NUMBER := 0;
4257 l_error_message VARCHAR2(2000);
4258 l_porting_id VARCHAR2(80);
4259 l_status_matched VARCHAR2(1) := 'T';
4260 l_STATUS_TO_COMPARE_WITH VARCHAR2(80);
4261 x_progress VARCHAR2(2000);
4262 l_sp_name varchar2(80) := null;
4263 l_local_sp_id number := 0;
4264 e_SOA_CHECK_SV_STATUS EXCEPTION;
4265 BEGIN
4266
4267 --
4268 -- RUN mode - normal process execution
4269 --
4270 IF (funcmode = 'RUN') THEN
4271
4272 -- The run code
4273
4274 SET_SDP_CONTEXT
4275 (ITEMTYPE
4276 ,ITEMKEY
4277 ,ACTID
4278 ,'SET_WI'
4279 ,RESULTOUT
4280 );
4281
4282 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
4283
4284 l_PORTING_ID :=
4285 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4286 (l_WORKITEM_INSTANCE_ID
4287 ,'PORTING_ID'
4288 );
4289
4290 l_STATUS_TO_COMPARE_WITH :=
4291 wf_engine.GetActivityAttrText
4292 (itemtype => itemtype
4293 ,itemkey => itemkey
4294 ,actid => actid
4295 ,aname => 'STATUS_TO_COMPARE_WITH'
4296 );
4297
4298
4299 l_sp_name :=
4300 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4301 (l_WORKITEM_INSTANCE_ID
4302 ,'SP_NAME'
4303 );
4304
4305 -- Check to see if Statuses matech
4306 XNP_CORE.SOA_CHECK_SV_STATUS
4307 (p_porting_id=>l_porting_id
4308 ,p_local_sp_id=>l_local_sp_id
4309 ,p_STATUS_TYPE_CODE=>l_STATUS_TO_COMPARE_WITH
4310 ,x_STATUS_MATCHED_FLAG=>l_status_matched
4311 ,x_error_code=>l_error_code
4312 ,x_error_message=>l_error_message
4313 );
4314
4315 IF l_error_code <> 0
4316 THEN
4317 raise e_SOA_CHECK_SV_STATUS;
4318 END IF;
4319
4320 -- Completion
4321 resultout := 'COMPLETE:'||l_status_matched;
4322 RETURN;
4323 END IF;
4324 --
4325 -- CANCEL mode
4326 --
4327 -- This is in the event that the activity must be undone.
4328 --
4329 IF (funcmode = 'CANCEL' ) THEN
4330 -- The cancel code
4331
4332 null;
4333 -- no result needed
4334 resultout := 'COMPLETE';
4335 return;
4336 END IF;
4337
4338 -- For other execution modes: return null
4339
4340 resultout := '';
4341 return;
4342
4343 EXCEPTION
4344 WHEN OTHERS THEN
4345 ------------------------------------------------------------------
4346 -- Record this function call in the error
4347 -- system in case of an exception
4348 ------------------------------------------------------------------
4349
4350
4351 IF (l_error_code <> 0) THEN
4352 x_progress := to_char(l_error_code)||':'||l_error_message;
4353 ELSE
4354 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4355 END IF;
4356
4357 XNP_UTILS.NOTIFY_ERROR
4358 (P_PKG_NAME => 'XNP_WF_STANDARD'
4359 ,P_PROC_NAME => 'SOA_CHECK_SV_STATUS'
4360 ,P_MSG_NAME => 'SOA_CHECK_SV_STATUS_ERR'
4361 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4362 ,P_TOK1 => 'ERROR_TEXT'
4363 ,P_VAL1 => x_progress
4364 );
4365
4366 RAISE;
4367 END SOA_CHECK_SV_STATUS;
4368
4369 --------------------------------------------------------------------
4370 -- Gets the Status for the porting record for the
4371 -- PORTING_ID
4372 -- Workitem Parameter: PORTING_ID
4373 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
4374 -- Workitem Paramters : PORTING_ID
4375 -- Calls XNP_CORE.SOA_GET_SV_STATUS
4376 --
4377 ------------------------------------------------------------------
4378 PROCEDURE SOA_GET_SV_STATUS
4379 (ITEMTYPE IN VARCHAR2
4380 ,ITEMKEY IN VARCHAR2
4381 ,ACTID IN NUMBER
4382 ,FUNCMODE IN VARCHAR2
4383 ,RESULTOUT OUT NOCOPY VARCHAR2
4384 )
4385 IS
4386 l_WORKITEM_INSTANCE_ID NUMBER;
4387 l_error_code NUMBER := 0;
4388 l_error_message VARCHAR2(2000);
4389 l_porting_id VARCHAR2(80);
4390 l_SV_STATUS VARCHAR2(80);
4391 l_sp_name varchar2(80) := null;
4392 l_local_sp_id number := 0;
4393 x_progress VARCHAR2(2000);
4394 e_SOA_GET_SV_STATUS EXCEPTION;
4395 BEGIN
4396
4397 --
4398 -- RUN mode - normal process execution
4399 --
4400 IF (funcmode = 'RUN') THEN
4401
4402 -- The run code
4403
4404 SET_SDP_CONTEXT
4405 (ITEMTYPE
4406 ,ITEMKEY
4407 ,ACTID
4408 ,'SET_WI'
4409 ,RESULTOUT
4410 );
4411
4412 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
4413
4414 l_PORTING_ID :=
4415 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4416 (l_WORKITEM_INSTANCE_ID
4417 ,'PORTING_ID'
4418 );
4419
4420
4421 l_sp_name :=
4422 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4423 (l_WORKITEM_INSTANCE_ID
4424 ,'SP_NAME'
4425 );
4426
4427 -- Check to see if Statuses matech
4428 XNP_CORE.SOA_GET_SV_STATUS
4429 (p_porting_id=>l_porting_id
4430 ,p_local_sp_id=>l_local_sp_id
4431 ,x_SV_STATUS=>l_sv_status
4432 ,x_error_code=>l_error_code
4433 ,x_error_message=>l_error_message
4434 );
4435
4436 IF l_error_code <> 0 THEN
4437 raise e_SOA_GET_SV_STATUS;
4438 END IF;
4439
4440 -- Completion
4441 resultout := 'COMPLETE:'||l_sv_status;
4442 RETURN;
4443 END IF;
4444 --
4445 -- CANCEL mode
4446 --
4447 -- This is in the event that the activity must be undone.
4448 --
4449 IF (funcmode = 'CANCEL' ) THEN
4450 -- The cancel code
4451
4452 null;
4453 -- no result needed
4454 resultout := 'COMPLETE';
4455 return;
4456 END IF;
4457
4458 -- For other execution modes: return null
4459
4460 resultout := '';
4461 return;
4462
4463 EXCEPTION
4464 WHEN OTHERS THEN
4465 ------------------------------------------------------------------
4466 -- Record this function call in the error
4467 -- system in case of an exception
4468 ------------------------------------------------------------------
4469
4470 IF (l_error_code <> 0) THEN
4471 x_progress := to_char(l_error_code)||':'||l_error_message;
4472 ELSE
4473 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4474 END IF;
4475
4476 XNP_UTILS.NOTIFY_ERROR
4477 (P_PKG_NAME => 'XNP_WF_STANDARD'
4478 ,P_PROC_NAME => 'SOA_GET_SV_STATUS'
4479 ,P_MSG_NAME => 'SOA_GET_SV_STATUS_ERR'
4480 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4481 ,P_TOK1 => 'ERROR_TEXT'
4482 ,P_VAL1 => x_progress
4483 );
4484
4485 RAISE;
4486 END SOA_GET_SV_STATUS;
4487
4488
4489 --------------------------------------------------------------------
4490 -- Called to modify provisioning
4491 --
4492 -- Description:
4493 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
4494 -- Gets the Activity Attribute FEATURE_TYPE
4495 -- Gets the workitem parameters STARTING_NUMBER, ENDING_NUMBER
4496 -- For each of the FEs executes and FA and subscribes for FA_DONE
4497 ------------------------------------------------------------------
4498 PROCEDURE SMS_MODIFY_NES
4499 (ITEMTYPE IN VARCHAR2
4500 ,ITEMKEY IN VARCHAR2
4501 ,ACTID IN NUMBER
4502 ,FUNCMODE IN VARCHAR2
4503 ,RESULTOUT OUT NOCOPY VARCHAR2
4504 )
4505 IS
4506 x_progress VARCHAR2(2000);
4507 l_FEATURE_TYPE VARCHAR2(80) := NULL;
4508 --l_FA_INSTANCE_ID NUMBER := 0;
4509 l_FA_INSTANCE_ID NUMBER := NULL;
4510 l_process_reference varchar2(512) := null;
4511 l_activity_name varchar2(240) := null;
4512 l_ORDER_ID NUMBER;
4513 l_WORKITEM_INSTANCE_ID NUMBER;
4514 l_error_code NUMBER := 0;
4515 l_error_message VARCHAR2(2000);
4516 l_STARTING_NUMBER VARCHAR2(80) := NULL;
4517 l_FE_NAME VARCHAR2(200) := NULL;
4518 l_ENDING_NUMBER VARCHAR2(80) := NULL;
4519
4520 CURSOR c_ALL_FEs IS
4521 SELECT FE_ID
4522 FROM XNP_SV_SMS_FE_MAPS
4523 WHERE SV_SMS_ID IN
4524 (SELECT SV_SMS_ID FROM XNP_SV_SMS
4525 WHERE SUBSCRIPTION_TN
4526 BETWEEN l_STARTING_NUMBER AND l_ENDING_NUMBER
4527 )
4528 AND FEATURE_TYPE=l_FEATURE_TYPE
4529 ;
4530
4531 e_SMS_MODIFY_NES EXCEPTION;
4532
4533 BEGIN
4534
4535 --
4536 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
4537 --
4538 SET_SDP_CONTEXT
4539 (ITEMTYPE
4540 ,ITEMKEY
4541 ,ACTID
4542 ,'SET_ORD_WI'
4543 ,RESULTOUT
4544 );
4545
4546 --
4547 -- RUN mode - normal process execution
4548 --
4549 IF (funcmode = 'RUN') THEN
4550
4551 -- The run code
4552
4553 l_ORDER_ID := g_ORDER_ID;
4554 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
4555
4556 l_FEATURE_TYPE :=
4557 wf_engine.GetActivityAttrText
4558 (itemtype => itemtype
4559 ,itemkey => itemkey
4560 ,actid => actid
4561 ,aname => 'FEATURE_TYPE'
4562 );
4563
4564 l_STARTING_NUMBER :=
4565 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4566 (l_WORKITEM_INSTANCE_ID
4567 ,'STARTING_NUMBER'
4568 );
4569
4570 l_ENDING_NUMBER :=
4571 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4572 (l_WORKITEM_INSTANCE_ID
4573 ,'ENDING_NUMBER'
4574 );
4575
4576
4577 ------------------------------------------------------------------
4578 -- For each of the fe execute an FA
4579 -- for the fe modify and subscribe for FA_DONE
4580 ------------------------------------------------------------------
4581
4582 FOR l_tmp_fe IN c_ALL_FEs LOOP
4583 /***** Marked out to use overloaded xdp_eng_util.ad_fa_towi with fe_id itself.
4584 XNP_UTILS.GET_FE_NAME
4585 (p_FE_ID=>l_TMP_FE.FE_ID
4586 ,x_FE_NAME=>l_FE_NAME
4587 ,x_ERROR_CODE=>l_ERROR_CODE
4588 ,x_ERROR_MESSAGE=>l_ERROR_MESSAGE
4589 );
4590 IF l_error_code <> 0
4591 THEN
4592 raise e_SMS_MODIFY_NES;
4593 END IF;
4594 *****/
4595 ------------------------------------------------------------------
4596 -- Add the FA to the workitem and get the FA instance id
4597 ------------------------------------------------------------------
4598 l_FA_INSTANCE_ID :=
4599 XDP_ENG_UTIL.ADD_FA_TOWI
4600 (l_WORKITEM_INSTANCE_ID
4601 ,'MODIFY_'||l_FEATURE_TYPE -- the FA
4602 ,l_tmp_fe.fe_id
4603 );
4604
4605 -- Call fa exection procedure
4606 xdp_eng_util.execute_fa
4607 (p_order_id=>l_order_id
4608 ,p_wi_instance_id=>l_workitem_instance_id
4609 ,p_fa_instance_id=>l_fa_instance_id
4610 ,p_wi_item_type=>itemtype
4611 ,p_wi_item_key=>itemkey
4612 ,p_return_code=>l_error_code
4613 ,p_error_description=>l_error_message
4614 ,p_fa_caller=>'INTERNAL'
4615 );
4616
4617 IF l_error_code <> 0 THEN
4618 raise e_SMS_MODIFY_NES;
4619 END IF;
4620
4621 -- SUBSCRIBE for FA_DONE
4622 -- with the FA_INSTANCE_ID
4623 -- and let SFM resume workflow
4624 -- append FEATURE_TYPE and FE_ID to the process reference
4625 l_PROCESS_REFERENCE :=
4626 itemtype||':'||itemkey||':'||'MOD:'||l_FEATURE_TYPE||':'||to_char(l_tmp_fe.FE_ID);
4627
4628 XNP_EVENT.SUBSCRIBE
4629 (P_MSG_CODE=>'FA_DONE' -- Message type to expected
4630 ,P_REFERENCE_ID=>l_FA_INSTANCE_ID -- Reference id
4631 ,P_PROCESS_REFERENCE=>l_PROCESS_REFERENCE -- workflow id
4632 ,P_PROCEDURE_NAME=>'XNP_FA_CB.PROCESS_FA_DONE' -- callback proc
4633 ,P_CALLBACK_TYPE=>'PL/SQL' -- callback proc type
4634 ,P_CLOSE_REQD_FLAG => 'Y'
4635 ,P_ORDER_ID=>l_order_id
4636 ,P_WI_INSTANCE_ID=>l_workitem_instance_id
4637 ,P_FA_INSTANCE_ID=>l_FA_INSTANCE_ID
4638 );
4639
4640 END LOOP;
4641
4642 resultout := 'COMPLETE';
4643
4644 RETURN;
4645
4646 END IF;
4647 --
4648 -- CANCEL mode
4649 --
4650 -- This is in the event that the activity must be undone.
4651 --
4652 IF (funcmode = 'CANCEL' ) THEN
4653 -- The cancel code
4654
4655 null;
4656 -- no result needed
4657 resultout := 'COMPLETE';
4658 return;
4659 END IF;
4660
4661 -- For other execution modes: return null
4662
4663 resultout := '';
4664 return;
4665
4666 EXCEPTION
4667 WHEN OTHERS THEN
4668 ------------------------------------------------------------------
4669 -- Record this function call in the error
4670 -- system in case of an exception
4671 ------------------------------------------------------------------
4672
4673
4674 IF (l_error_code <> 0) THEN
4675 x_progress := to_char(l_error_code)||':'||l_error_message;
4676 ELSE
4677 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4678 END IF;
4679
4680 XNP_UTILS.NOTIFY_ERROR
4681 (P_PKG_NAME => 'XNP_WF_STANDARD'
4682 ,P_PROC_NAME => 'SMS_MODIFY_NES'
4683 ,P_MSG_NAME => 'SMS_MODIFY_NES_ERR'
4684 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4685 ,P_TOK1 => 'ERROR_TEXT'
4686 ,P_VAL1 => x_progress
4687 );
4688
4689 RAISE;
4690
4691 END SMS_MODIFY_NES;
4692
4693 --------------------------------------------------------------------
4694 -- Called when: there is a Modify Ported Number
4695 -- request from NRC
4696 -- Description:
4697 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
4698 -- Calls XNP_STANDARD.SMS_MODIFY_PORTED_NUMBER
4699 ------------------------------------------------------------------
4700 PROCEDURE SMS_MODIFY_PORTED_NUMBER
4701 (ITEMTYPE IN VARCHAR2
4702 ,ITEMKEY IN VARCHAR2
4703 ,ACTID IN NUMBER
4704 ,FUNCMODE IN VARCHAR2
4705 ,RESULTOUT OUT NOCOPY VARCHAR2
4706 )
4707 IS
4708 l_ORDER_ID NUMBER;
4709 l_LINEITEM_ID NUMBER;
4710 l_WORKITEM_INSTANCE_ID NUMBER;
4711 l_FA_INSTANCE_ID NUMBER;
4712 l_error_code NUMBER := 0;
4713 l_error_message VARCHAR2(2000);
4714 x_progress VARCHAR2(2000) := NULL;
4715 e_SMS_MODIFY_PORTED_NUMBER EXCEPTION;
4716
4717 BEGIN
4718
4719 --
4720 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
4721 --
4722 SET_SDP_CONTEXT
4723 (ITEMTYPE
4724 ,ITEMKEY
4725 ,ACTID
4726 ,'SET_ORD_WI'
4727 ,RESULTOUT
4728 );
4729
4730 --
4731 -- RUN mode - normal process execution
4732 --
4733 IF (funcmode = 'RUN') THEN
4734
4735 l_ORDER_ID := g_ORDER_ID;
4736 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
4737
4738 -- The run code
4739
4740 XNP_STANDARD.SMS_MODIFY_PORTED_NUMBER
4741 (l_ORDER_ID ,
4742 l_LINEITEM_ID,
4743 l_WORKITEM_INSTANCE_ID,
4744 l_FA_INSTANCE_ID,
4745 l_error_code,
4746 l_error_message
4747 );
4748
4749 IF l_error_code <> 0 THEN
4750 raise e_SMS_MODIFY_PORTED_NUMBER;
4751 END IF;
4752 -- Completion
4753 resultout := 'COMPLETE';
4754 RETURN;
4755 END IF;
4756 --
4757 -- CANCEL mode
4758 --
4759 -- This is in the event that the activity must be undone.
4760 --
4761 IF (funcmode = 'CANCEL' ) THEN
4762 -- The cancel code
4763
4764 null;
4765 -- no result needed
4766 resultout := 'COMPLETE';
4767 return;
4768 END IF;
4769
4770 -- For other execution modes: return null
4771
4772 resultout := '';
4773 return;
4774
4775 EXCEPTION
4776 WHEN OTHERS THEN
4777
4778 ------------------------------------------------------------------
4779 -- Record this function call in the error
4780 -- system in case of an exception
4781 ------------------------------------------------------------------
4782 IF (l_error_code <> 0) THEN
4783 x_progress := to_char(l_error_code)||':'||l_error_message;
4784 ELSE
4785 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4786 END IF;
4787
4788 XNP_UTILS.NOTIFY_ERROR
4789 (P_PKG_NAME => 'XNP_WF_STANDARD'
4790 ,P_PROC_NAME => 'SMS_MODIFY_PORTED_NUMBER'
4791 ,P_MSG_NAME => 'SMS_MODIFY_PORTED_NUMBER_ERR'
4792 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4793 ,P_TOK1 => 'ERROR_TEXT'
4794 ,P_VAL1 => x_progress
4795 );
4796
4797 RAISE;
4798
4799 END SMS_MODIFY_PORTED_NUMBER;
4800
4801 --------------------------------------------------------------------
4802 -- Updates the NEW_SP_DUE_DATE for the given porting record
4803 -- given the PORTING_ID
4804 -- Workitem Parameter: PORTING_ID
4805 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
4806 -- Workitem Paramters : PORTING_ID
4807 -- Calls XNP_CORE.SOA_UPDATE_NEW_SP_DUE_DATE
4808 --
4809 ------------------------------------------------------------------
4810
4811 PROCEDURE SOA_UPDATE_NEW_SP_DUE_DATE
4812 (ITEMTYPE IN VARCHAR2
4813 ,ITEMKEY IN VARCHAR2
4814 ,ACTID IN NUMBER
4815 ,FUNCMODE IN VARCHAR2
4816 ,RESULTOUT OUT NOCOPY VARCHAR2
4817 )
4818 IS
4819 l_ORDER_ID NUMBER;
4820 l_LINEITEM_ID NUMBER;
4821 l_WORKITEM_INSTANCE_ID NUMBER;
4822 l_FA_INSTANCE_ID NUMBER;
4823 l_error_code NUMBER := 0;
4824 l_error_message VARCHAR2(2000);
4825 l_porting_id VARCHAR2(80);
4826 l_NEW_SP_DUE_DATE VARCHAR2(80);
4827 x_progress VARCHAR2(2000);
4828 l_sp_name VARCHAR2(80) := null;
4829 l_local_sp_id NUMBER := 0;
4830 e_SOA_UPDATE_NEW_SP_DUE_DATE EXCEPTION;
4831
4832 BEGIN
4833 --
4834 -- RUN mode - normal process execution
4835 --
4836 IF (funcmode = 'RUN') THEN
4837 -- The run code
4838
4839 SET_SDP_CONTEXT
4840 (ITEMTYPE
4841 ,ITEMKEY
4842 ,ACTID
4843 ,'SET_ORD_WI'
4844 ,RESULTOUT
4845 );
4846
4847 l_ORDER_ID := g_ORDER_ID;
4848 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
4849
4850 l_PORTING_ID :=
4851 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4852 (l_WORKITEM_INSTANCE_ID
4853 ,'PORTING_ID'
4854 );
4855
4856 l_NEW_SP_DUE_DATE :=
4857 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4858 (l_WORKITEM_INSTANCE_ID
4859 ,'NEW_SP_DUE_DATE'
4860 );
4861
4862 l_sp_name :=
4863 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4864 (l_WORKITEM_INSTANCE_ID
4865 ,'SP_NAME'
4866 );
4867
4868 -- Set the duedate
4869 XNP_CORE.SOA_UPDATE_NEW_SP_DUE_DATE
4870 (p_order_id => l_order_id ,
4871 p_lineitem_id => l_lineitem_id,
4872 p_workitem_instance_id => l_workitem_instance_id,
4873 p_fa_instance_id => l_fa_instance_id,
4874 p_porting_id => l_porting_id,
4875 p_local_sp_id => l_local_sp_id,
4876 p_NEW_SP_DUE_DATE => xnp_utils.canonical_to_date(l_NEW_SP_DUE_DATE),
4877 x_error_code => l_error_code,
4878 x_error_message => l_error_message
4879 );
4880
4881 IF l_error_code <> 0 THEN
4882 raise e_SOA_UPDATE_NEW_SP_DUE_DATE;
4883 END IF;
4884
4885 -- Completion
4886 resultout := 'COMPLETE';
4887 RETURN;
4888 END IF;
4889
4890 --
4891 -- CANCEL mode
4892 --
4893
4894 -- This is in the event that the activity must be undone.
4895 --
4896
4897 IF (funcmode = 'CANCEL' ) THEN
4898 -- The cancel code
4899 null;
4900 -- no result needed
4901 resultout := 'COMPLETE';
4902 return;
4903 END IF;
4904
4905 -- For other execution modes: return null
4906 resultout := '';
4907 return;
4908
4909 EXCEPTION
4910 WHEN OTHERS THEN
4911 ------------------------------------------------------------------
4912 -- Record this function call in the error
4913 -- system in case of an exception
4914 ------------------------------------------------------------------
4915 IF (l_error_code <> 0) THEN
4916 x_progress := to_char(l_error_code)||':'||l_error_message;
4917 ELSE
4918 x_progress := to_char(SQLCODE)||':'||SQLERRM;
4919 END IF;
4920 XNP_UTILS.NOTIFY_ERROR
4921 (P_PKG_NAME => 'XNP_WF_STANDARD'
4922 ,P_PROC_NAME => 'SOA_UPDATE_NEW_SP_DUE_DATE'
4923 ,P_MSG_NAME => 'SOA_UPDATE_NEW_SP_DUE_DATE_ERR'
4924 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
4925 ,P_TOK1 => 'ERROR_TEXT'
4926 ,P_VAL1 => x_progress
4927 );
4928 RAISE;
4929
4930 END SOA_UPDATE_NEW_SP_DUE_DATE;
4931
4932
4933 --------------------------------------------------------------------
4934 -- Updates the OLD_SP_DUE_DATE for the given porting record
4935 -- given the PORTING_ID
4936 -- Workitem Parameter: PORTING_ID
4937 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
4938 -- Workitem Paramters : PORTING_ID
4939 -- Calls XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE
4940 --
4941 ------------------------------------------------------------------
4942
4943 PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
4944 (ITEMTYPE IN VARCHAR2
4945 ,ITEMKEY IN VARCHAR2
4946 ,ACTID IN NUMBER
4947 ,FUNCMODE IN VARCHAR2
4948 ,RESULTOUT OUT NOCOPY VARCHAR2
4949 )
4950 IS
4951 l_ORDER_ID NUMBER;
4952 l_LINEITEM_ID NUMBER;
4953 l_WORKITEM_INSTANCE_ID NUMBER;
4954 l_FA_INSTANCE_ID NUMBER;
4955 l_error_code NUMBER := 0;
4956 l_error_message VARCHAR2(2000);
4957 l_porting_id VARCHAR2(80);
4958 l_OLD_SP_DUE_DATE VARCHAR2(80);
4959 l_sp_name VARCHAR2(80) := null;
4960 l_local_sp_id number := 0;
4961 x_progress VARCHAR2(2000);
4962 e_SOA_UPDATE_OLD_SP_DUE_DATE EXCEPTION;
4963
4964 BEGIN
4965 --
4966 -- RUN mode - normal process execution
4967 --
4968 IF (funcmode = 'RUN') THEN
4969 -- The run code
4970
4971 SET_SDP_CONTEXT
4972 (ITEMTYPE
4973 ,ITEMKEY
4974 ,ACTID
4975 ,'SET_ORD_WI'
4976 ,RESULTOUT
4977 );
4978
4979 l_ORDER_ID := g_ORDER_ID;
4980 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
4981
4982 l_PORTING_ID :=
4983 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4984 (l_WORKITEM_INSTANCE_ID
4985 ,'PORTING_ID'
4986 );
4987
4988 l_OLD_SP_DUE_DATE :=
4989 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4990 (l_WORKITEM_INSTANCE_ID
4991 ,'OLD_SP_DUE_DATE'
4992 );
4993
4994
4995 l_sp_name :=
4996 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
4997 (l_WORKITEM_INSTANCE_ID
4998 ,'SP_NAME'
4999 );
5000
5001 -- Set the duedate
5002 XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE
5003 (p_order_id => l_order_id ,
5004 p_lineitem_id => l_lineitem_id ,
5005 p_workitem_instance_id => l_workitem_instance_id ,
5006 p_fa_instance_id => l_fa_instance_id ,
5007 p_porting_id => l_porting_id,
5008 p_local_sp_id => l_local_sp_id,
5009 p_OLD_SP_DUE_DATE => xnp_utils.canonical_to_date(l_OLD_SP_DUE_DATE),
5010 x_error_code => l_error_code,
5011 x_error_message => l_error_message
5012 );
5013
5014 IF l_error_code <> 0 THEN
5015 raise e_SOA_UPDATE_OLD_SP_DUE_DATE;
5016 END IF;
5017
5018 -- Completion
5019 resultout := 'COMPLETE';
5020 RETURN;
5021 END IF;
5022
5023 --
5024 -- CANCEL mode
5025 --
5026
5027 -- This is in the event that the activity must be undone.
5028 --
5029
5030 IF (funcmode = 'CANCEL' ) THEN
5031 -- The cancel code
5032 null;
5033 -- no result needed
5034 resultout := 'COMPLETE';
5035 return;
5036 END IF;
5037
5038 -- For other execution modes: return null
5039 resultout := '';
5040 return;
5041
5042 EXCEPTION
5043 WHEN OTHERS THEN
5044 ------------------------------------------------------------------
5045 -- Record this function call in the error
5046 -- system in case of an exception
5047 ------------------------------------------------------------------
5048 IF (l_error_code <> 0) THEN
5049 x_progress := to_char(l_error_code)||':'||l_error_message;
5050 ELSE
5051 x_progress := to_char(SQLCODE)||':'||SQLERRM;
5052 END IF;
5053 XNP_UTILS.NOTIFY_ERROR
5054 (P_PKG_NAME => 'XNP_WF_STANDARD'
5055 ,P_PROC_NAME => 'SOA_UPDATE_OLD_SP_DUE_DATE'
5056 ,P_MSG_NAME => 'SOA_UPDATE_OLD_SP_DUE_DATE_ERR'
5057 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
5058 ,P_TOK1 => 'ERROR_TEXT'
5059 ,P_VAL1 => x_progress
5060 );
5061 RAISE;
5062
5063 END SOA_UPDATE_OLD_SP_DUE_DATE;
5064
5065 ------------------------------------------------------------------
5066 -- Description: Procedure to check if there
5067 -- exists a Porting record in the given status
5068 -- for this TN range and beloging to the
5069 -- with the given DONOR's SP ID
5070 -- Completes with 'Y' or 'N'
5071 -- Activity Attributes: STATUS_TO_COMPARE_WITH
5072 -- Mandatory WI Params: STARTING_NUMBER,ENDING_NUMBER,DONOR_SP_ID
5073 ------------------------------------------------------------------
5074 PROCEDURE SOA_CHECK_DON_STATUS_EXISTS
5075 (ITEMTYPE IN VARCHAR2
5076 ,ITEMKEY IN VARCHAR2
5077 ,ACTID IN NUMBER
5078 ,FUNCMODE IN VARCHAR2
5079 ,RESULTOUT OUT NOCOPY VARCHAR2
5080 )
5081 IS
5082 l_WORKITEM_INSTANCE_ID NUMBER;
5083 l_error_code NUMBER := 0;
5084 l_error_message VARCHAR2(2000);
5085 x_CHECK_STATUS VARCHAR2(1);
5086 l_STATUS_TO_COMPARE_WITH varchar2(80);
5087 x_progress VARCHAR2(2000);
5088 e_SOA_CHECK_DON_STATUS_EXISTS EXCEPTION;
5089 BEGIN
5090 --
5091 -- RUN mode - normal process execution
5092 --
5093 IF (funcmode = 'RUN') THEN
5094 -- The run code
5095
5096 SET_SDP_CONTEXT
5097 (ITEMTYPE
5098 ,ITEMKEY
5099 ,ACTID
5100 ,'SET_WI'
5101 ,RESULTOUT
5102 );
5103
5104 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
5105
5106 l_STATUS_TO_COMPARE_WITH :=
5107 wf_engine.GetActivityAttrText
5108 (itemtype => itemtype
5109 ,itemkey => itemkey
5110 ,actid => actid
5111 ,aname => 'STATUS_TO_COMPARE_WITH'
5112 );
5113
5114 XNP_STANDARD.SOA_CHECK_DON_STATUS_EXISTS
5115 (p_WORKITEM_INSTANCE_ID =>l_workitem_instance_id
5116 ,p_status_to_check_with =>l_status_to_compare_with
5117 ,x_CHECK_STATUS =>x_check_status
5118 ,x_ERROR_CODE =>l_error_code
5119 ,x_ERROR_MESSAGE =>l_error_message
5120 );
5121
5122 IF l_error_code <> 0 THEN
5123 raise e_SOA_CHECK_DON_STATUS_EXISTS;
5124 END IF;
5125
5126 -- Completion
5127 if (x_check_status = 'Y') then
5128 resultout := 'COMPLETE:T';
5129 else
5130 resultout := 'COMPLETE:F';
5131 end if;
5132
5133 RETURN;
5134 END IF;
5135
5136 --
5137 -- CANCEL mode
5138 --
5139
5140 -- This is in the event that the activity must be undone.
5141 --
5142
5143 IF (funcmode = 'CANCEL' ) THEN
5144 -- The cancel code
5145 null;
5146 -- no result needed
5147 resultout := 'COMPLETE';
5148 return;
5149 END IF;
5150
5151 -- For other execution modes: return null
5152 resultout := '';
5153 return;
5154
5155 EXCEPTION
5156 WHEN OTHERS THEN
5157 ------------------------------------------------------------------
5158 -- Record this function call in the error
5159 -- system in case of an exception
5160 ------------------------------------------------------------------
5161 IF (l_error_code <> 0) THEN
5162 x_progress := to_char(l_error_code)||':'||l_error_message;
5163 ELSE
5164 x_progress := to_char(SQLCODE)||':'||SQLERRM;
5165 END IF;
5166 XNP_UTILS.NOTIFY_ERROR
5167 (P_PKG_NAME => 'XNP_WF_STANDARD'
5168 ,P_PROC_NAME => 'SOA_CHECK_DON_STATUS_EXISTS'
5169 ,P_MSG_NAME => 'SOA_CHK_DON_STATUS_EXISTS_ERR'
5170 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
5171 ,P_TOK1 => 'ERROR_TEXT'
5172 ,P_VAL1 => x_progress
5173 );
5174 RAISE;
5175
5176 END SOA_CHECK_DON_STATUS_EXISTS;
5177
5178 ------------------------------------------------------------------
5179 -- Description: Procedure to check if there
5180 -- exists a Porting record in the given status
5181 -- for this TN range and beloging to the
5182 -- with the given RECIPIENT's SP ID
5183 -- Completes with 'Y' or 'N'
5184 ------------------------------------------------------------------
5185 PROCEDURE SOA_CHECK_REC_STATUS_EXISTS
5186 (ITEMTYPE IN VARCHAR2
5187 ,ITEMKEY IN VARCHAR2
5188 ,ACTID IN NUMBER
5189 ,FUNCMODE IN VARCHAR2
5190 ,RESULTOUT OUT NOCOPY VARCHAR2
5191 )
5192 IS
5193 l_WORKITEM_INSTANCE_ID NUMBER;
5194 l_error_code NUMBER := 0;
5195 l_error_message VARCHAR2(2000);
5196 x_CHECK_STATUS VARCHAR2(1);
5197 l_STATUS_TO_COMPARE_WITH varchar2(80);
5198 x_progress VARCHAR2(2000);
5199 e_SOA_CHECK_REC_STATUS_EXISTS EXCEPTION;
5200 BEGIN
5201 --
5202 -- RUN mode - normal process execution
5203 --
5204 IF (funcmode = 'RUN') THEN
5205 -- The run code
5206
5207 SET_SDP_CONTEXT
5208 (ITEMTYPE
5209 ,ITEMKEY
5210 ,ACTID
5211 ,'SET_WI'
5212 ,RESULTOUT
5213 );
5214
5215 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
5216
5217 l_STATUS_TO_COMPARE_WITH :=
5218 wf_engine.GetActivityAttrText
5219 (itemtype => itemtype
5220 ,itemkey => itemkey
5221 ,actid => actid
5222 ,aname => 'STATUS_TO_COMPARE_WITH'
5223 );
5224
5225 XNP_STANDARD.SOA_CHECK_REC_STATUS_EXISTS
5226 (p_WORKITEM_INSTANCE_ID =>l_workitem_instance_id
5227 ,p_status_to_check_with =>l_status_to_compare_with
5228 ,x_CHECK_STATUS =>x_check_status
5229 ,x_ERROR_CODE =>l_error_code
5230 ,x_ERROR_MESSAGE =>l_error_message
5231 );
5232
5233 IF l_error_code <> 0 THEN
5234 raise e_SOA_CHECK_REC_STATUS_EXISTS;
5235 END IF;
5236
5237 -- Completion
5238 if (x_check_status = 'Y') then
5239 resultout := 'COMPLETE:T';
5240 else
5241 resultout := 'COMPLETE:F';
5242 end if;
5243
5244 RETURN;
5245 END IF;
5246
5247 --
5248 -- CANCEL mode
5249 --
5250
5251 -- This is in the event that the activity must be undone.
5252 --
5253
5254 IF (funcmode = 'CANCEL' ) THEN
5255 -- The cancel code
5256 null;
5257 -- no result needed
5258 resultout := 'COMPLETE';
5259 return;
5260 END IF;
5261
5262 -- For other execution modes: return null
5263 resultout := '';
5264 return;
5265
5266 EXCEPTION
5267 WHEN OTHERS THEN
5268 ------------------------------------------------------------------
5269 -- Record this function call in the error
5270 -- system in case of an exception
5271 ------------------------------------------------------------------
5272 IF (l_error_code <> 0) THEN
5273 x_progress := to_char(l_error_code)||':'||l_error_message;
5274 ELSE
5275 x_progress := to_char(SQLCODE)||':'||SQLERRM;
5276 END IF;
5277 XNP_UTILS.NOTIFY_ERROR
5278 (P_PKG_NAME => 'XNP_WF_STANDARD'
5279 ,P_PROC_NAME => 'SOA_CHECK_REC_STATUS_EXISTS'
5280 ,P_MSG_NAME => 'SOA_CHK_REC_STATUS_EXISTS_ERR'
5281 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
5282 ,P_TOK1 => 'ERROR_TEXT'
5283 ,P_VAL1 => x_progress
5284 );
5285 RAISE;
5286
5287 END SOA_CHECK_REC_STATUS_EXISTS;
5288
5289 ------------------------------------------------------------------
5290 -- Called when: need to update the SV status according
5291 -- to the activity parameter SV_STATUS
5292 -- Gets the Item Attributes WORKITEM_INSTANCE
5293 -- Calls XNP_CORE.SOA_UPDATE_SV_STATUS
5294 -- Description: Procedure to update the status of
5295 -- the Porting Order Records to the new status
5296 -- for the given PORTING_ID
5297 -- (a.k.a OBJECT_REFERENCE) and
5298 -- belonging to the (local) SP ID.
5299 ------------------------------------------------------------------
5300 PROCEDURE SOA_UPD_PORTING_ID_STATUS
5301 (ITEMTYPE IN VARCHAR2
5302 ,ITEMKEY IN VARCHAR2
5303 ,ACTID IN NUMBER
5304 ,FUNCMODE IN VARCHAR2
5305 ,RESULTOUT OUT NOCOPY VARCHAR2
5306 )
5307 IS
5308 l_ORDER_ID NUMBER;
5309 l_LINEITEM_ID NUMBER;
5310 l_WORKITEM_INSTANCE_ID NUMBER;
5311 l_FA_INSTANCE_ID NUMBER;
5312 l_NEW_STATUS_TYPE_CODE VARCHAR2(40);
5313 l_STATUS_CHANGE_CAUSE_CODE VARCHAR2(40);
5314 l_error_code NUMBER := 0;
5315 l_error_message VARCHAR2(2000);
5316 x_progress VARCHAR2(2000);
5317 e_SOA_UPD_PORTING_ID_STATUS EXCEPTION;
5318
5319 BEGIN
5320
5321 --
5322 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
5323 --
5324 SET_SDP_CONTEXT
5325 (ITEMTYPE
5326 ,ITEMKEY
5327 ,ACTID
5328 ,'SET_ORD_WI'
5329 ,RESULTOUT
5330 );
5331
5332 --
5333 -- RUN mode - normal process execution
5334 --
5335 IF (funcmode = 'RUN') THEN
5336
5337 -- The run code
5338
5339 l_ORDER_ID := g_ORDER_ID;
5340 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
5341
5342 l_NEW_STATUS_TYPE_CODE :=
5343 wf_engine.GetActivityAttrText (itemtype => itemtype,
5344 itemkey => itemkey,
5345 actid => actid,
5346 aname => 'NEW_STATUS_TYPE_CODE');
5347
5348 l_STATUS_CHANGE_CAUSE_CODE :=
5349 wf_engine.GetActivityAttrText (itemtype => itemtype,
5350 itemkey => itemkey,
5351 actid => actid,
5352 aname => 'STATUS_CHANGE_CAUSE_CODE');
5353
5354 XNP_STANDARD.SOA_UPDATE_SV_STATUS
5355 (p_ORDER_ID => l_ORDER_ID ,
5356 p_LINEITEM_ID => l_LINEITEM_ID ,
5357 p_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID,
5358 p_FA_INSTANCE_ID => l_FA_INSTANCE_ID ,
5359 p_NEW_STATUS_TYPE_CODE => l_NEW_STATUS_TYPE_CODE,
5360 p_STATUS_CHANGE_CAUSE_CODE => l_STATUS_CHANGE_CAUSE_CODE,
5361 x_ERROR_CODE => l_error_code,
5362 x_ERROR_MESSAGE => l_error_message
5363 );
5364
5365 IF l_error_code <> 0 THEN
5366 raise e_SOA_UPD_PORTING_ID_STATUS;
5367 END IF;
5368 -- Completion
5369 resultout := 'COMPLETE';
5370 RETURN;
5371 END IF;
5372 --
5373 -- CANCEL mode
5374 --
5375 -- This is in the event that the activity must be undone.
5376 --
5377 IF (funcmode = 'CANCEL' ) THEN
5378 -- The cancel code
5379
5380 null;
5381 -- no result needed
5382 resultout := 'COMPLETE';
5383 return;
5384 END IF;
5385
5386 -- For other execution modes: return null
5387
5388 resultout := '';
5389 return;
5390
5391 EXCEPTION
5392 WHEN OTHERS THEN
5393 ------------------------------------------------------------------
5394 -- Record this function call in the error
5395 -- system in case of an exception
5396 ------------------------------------------------------------------
5397
5398
5399 IF (l_error_code <> 0) THEN
5400 x_progress := to_char(l_error_code)||':'||l_error_message;
5401 ELSE
5402 x_progress := to_char(SQLCODE)||':'||SQLERRM;
5403 END IF;
5404
5405 XNP_UTILS.NOTIFY_ERROR
5406 (P_PKG_NAME => 'XNP_WF_STANDARD'
5407 ,P_PROC_NAME => 'SOA_UPD_PORTING_ID_STATUS'
5408 ,P_MSG_NAME => 'SOA_UPD_PORTING_ID_STATUS_ERR'
5409 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
5410 ,P_TOK1 => 'ERROR_TEXT'
5411 ,P_VAL1 => x_progress
5412 );
5413
5414 RAISE;
5415 END SOA_UPD_PORTING_ID_STATUS;
5416
5417 --------------------------------------------------------------------
5418 -- Sets the flag to the given value
5419 -- for the enties in xnp_sv_soa for the given
5420 -- PORTING_ID workitem paramter and FLAG_NAME
5421 -- Values: 'Y' or 'N'
5422 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
5423 -- Activity Attr : Gets the value of Activity Attribute FLAG_VALUE
5424 -- Gets the value of Activity Attribute FLAG_NAME
5425 -- Workitem Paramters : PORTING_ID, SP_NAME
5426 -- Calls the core function to set the corresponding
5427 -- flag value
5428 ------------------------------------------------------------------
5429 PROCEDURE SOA_SET_FLAG_VALUE
5430 (ITEMTYPE IN VARCHAR2
5431 ,ITEMKEY IN VARCHAR2
5432 ,ACTID IN NUMBER
5433 ,FUNCMODE IN VARCHAR2
5434 ,RESULTOUT OUT NOCOPY VARCHAR2
5435 )
5436 IS
5437 l_ORDER_ID NUMBER;
5438 l_LINEITEM_ID NUMBER;
5439 l_WORKITEM_INSTANCE_ID NUMBER;
5440 l_FA_INSTANCE_ID NUMBER;
5441 l_error_code NUMBER := 0;
5442 l_error_message VARCHAR2(2000):= null;
5443 l_porting_id VARCHAR2(80):= null;
5444 l_flag_value VARCHAR2(1):= null;
5445 l_flag_name VARCHAR2(40):= null;
5446 l_sp_name VARCHAR2(80) := null;
5447 l_local_sp_id NUMBER := 0;
5448 x_progress VARCHAR2(2000):= null;
5449 e_SOA_SET_FLAG_VALUE EXCEPTION;
5450
5451 BEGIN
5452
5453 --
5454 -- RUN mode - normal process execution
5455 --
5456 IF (funcmode = 'RUN') THEN
5457
5458 -- The run code
5459
5460 SET_SDP_CONTEXT
5461 (ITEMTYPE
5462 ,ITEMKEY
5463 ,ACTID
5464 ,'SET_ORD_WI'
5465 ,RESULTOUT
5466 );
5467
5468 l_ORDER_ID := g_ORDER_ID;
5469 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
5470
5471 l_PORTING_ID :=
5472 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5473 (l_WORKITEM_INSTANCE_ID
5474 ,'PORTING_ID'
5475 );
5476
5477 l_sp_name :=
5478 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5479 (l_WORKITEM_INSTANCE_ID
5480 ,'SP_NAME'
5481 );
5482
5483 l_flag_value :=
5484 wf_engine.GetActivityAttrText (itemtype => itemtype,
5485 itemkey => itemkey,
5486 actid => actid,
5487 aname => 'FLAG_VALUE');
5488
5489 l_flag_name :=
5490 wf_engine.GetActivityAttrText
5491 (itemtype => itemtype,
5492 itemkey => itemkey,
5493 actid => actid,
5494 aname => 'FLAG_NAME'
5495 );
5496
5497 if (l_flag_name = 'LOCKED_FLAG') then
5498 -- Set the locked flag
5499
5500 XNP_CORE.SOA_SET_LOCKED_FLAG
5501 (p_order_id => l_order_id ,
5502 p_lineitem_id => l_lineitem_id,
5503 p_workitem_instance_id => l_workitem_instance_id,
5504 p_fa_instance_id => l_fa_instance_id,
5505 p_porting_id => l_porting_id,
5506 p_local_sp_id => l_local_sp_id,
5507 p_locked_flag => l_flag_value,
5508 x_error_code => l_error_code,
5509 x_error_message => l_error_message
5510 );
5511 elsif (l_flag_name = 'NEW_SP_AUTHORIZATION_FLAG') then
5512 XNP_CORE.SOA_UPDATE_NEW_SP_AUTH_FLAG
5513 (p_order_id => l_order_id ,
5514 p_lineitem_id => l_lineitem_id,
5515 p_workitem_instance_id => l_workitem_instance_id,
5516 p_fa_instance_id => l_fa_instance_id,
5517 p_porting_id => l_porting_id,
5518 p_local_sp_id => l_local_sp_id,
5519 p_new_sp_authorization_flag => l_flag_value,
5520 x_error_code => l_error_code,
5521 x_error_message => l_error_message
5522 );
5523 elsif (l_flag_name = 'OLD_SP_AUTHORIZATION_FLAG') then
5524 XNP_CORE.SOA_UPDATE_OLD_SP_AUTH_FLAG
5525 (p_order_id => l_order_id ,
5526 p_lineitem_id => l_lineitem_id,
5527 p_workitem_instance_id => l_workitem_instance_id,
5528 p_fa_instance_id => l_fa_instance_id,
5529 p_porting_id => l_porting_id,
5530 p_local_sp_id => l_local_sp_id,
5531 p_old_sp_authorization_flag => l_flag_value,
5532 x_error_code => l_error_code,
5533 x_error_message => l_error_message
5534 );
5535 elsif (l_flag_name = 'BLOCKED_FLAG') then
5536 -- Set the blocked flag
5537 XNP_CORE.SOA_SET_BLOCKED_FLAG
5538 (p_order_id => l_order_id ,
5539 p_lineitem_id => l_lineitem_id,
5540 p_workitem_instance_id => l_workitem_instance_id,
5541 p_fa_instance_id => l_fa_instance_id,
5542 p_porting_id => l_porting_id,
5543 p_local_sp_id => l_local_sp_id,
5544 p_blocked_flag => l_flag_value,
5545 x_error_code => l_error_code,
5546 x_error_message => l_error_message
5547 );
5548 elsif (l_flag_name = 'CONCURRENCE_FLAG') then
5549 -- Set the concurrence flag
5550
5551 XNP_CORE.SOA_SET_CONCURRENCE_FLAG
5552 (p_order_id => l_order_id ,
5553 p_lineitem_id => l_lineitem_id,
5554 p_workitem_instance_id => l_workitem_instance_id,
5555 p_fa_instance_id => l_fa_instance_id,
5556 p_porting_id => l_porting_id,
5557 p_local_sp_id => l_local_sp_id,
5558 p_concurrence_flag => l_flag_value,
5559 x_error_code => l_error_code,
5560 x_error_message => l_error_message
5561 );
5562 else
5563 null; -- Ignore it
5564 end if;
5565
5566 IF l_error_code <> 0 THEN
5567 raise e_SOA_SET_FLAG_VALUE;
5568 END IF;
5569
5570 -- Completion
5571 resultout := 'COMPLETE';
5572 RETURN;
5573 END IF;
5574 --
5575 -- CANCEL mode
5576 --
5577 -- This is in the event that the activity must be undone.
5578 --
5579 IF (funcmode = 'CANCEL' ) THEN
5580 -- The cancel code
5581
5582 null;
5583 -- no result needed
5584 resultout := 'COMPLETE';
5585 return;
5586 END IF;
5587
5588 -- For other execution modes: return null
5589
5590 resultout := '';
5591 return;
5592
5593 EXCEPTION
5594 WHEN OTHERS THEN
5595 ------------------------------------------------------------------
5596 -- Record this function call in the error
5597 -- system in case of an exception
5598 ------------------------------------------------------------------
5599 IF (l_error_code <> 0) THEN
5600 x_progress := to_char(l_error_code)||':'||l_error_message;
5601 ELSE
5602 x_progress := to_char(SQLCODE)||':'||SQLERRM;
5603 END IF;
5604
5605 XNP_UTILS.NOTIFY_ERROR
5606 (P_PKG_NAME => 'XNP_WF_STANDARD'
5607 ,P_PROC_NAME => 'SOA_SET_FLAG_VALUE'
5608 ,P_MSG_NAME => 'SOA_SET_FLAG_VALUE_ERR'
5609 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
5610 ,P_TOK1 => 'ERROR_TEXT'
5611 ,P_VAL1 => x_progress
5612 ,P_TOK2 => 'FLAG_NAME'
5613 ,P_VAL2 => l_flag_name
5614 );
5615
5616 RAISE;
5617 END SOA_SET_FLAG_VALUE;
5618
5619
5620 --------------------------------------------------------------------
5621 -- Gets the Locked flag for the given
5622 -- PORTING_ID workitem paramter.
5623 -- The activity is completed with the flag value
5624 -- Values: 'Y' or 'N'
5625 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
5626 -- Workitem Paramters : PORTING_ID,SP_NAME
5627 -- Calls the core funtion to get the corresponding flag value
5628 ------------------------------------------------------------------
5629 PROCEDURE SOA_GET_FLAG_VALUE
5630 (ITEMTYPE IN VARCHAR2
5631 ,ITEMKEY IN VARCHAR2
5632 ,ACTID IN NUMBER
5633 ,FUNCMODE IN VARCHAR2
5634 ,RESULTOUT OUT NOCOPY VARCHAR2
5635 )
5636 IS
5637 l_WORKITEM_INSTANCE_ID NUMBER;
5638 l_error_code NUMBER := 0;
5639 l_error_message VARCHAR2(2000);
5640 l_porting_id VARCHAR2(80);
5641 l_flag_value VARCHAR2(1):='N';
5642 l_flag_name VARCHAR2(40);
5643 l_SP_NAME VARCHAR2(40) := NULL;
5644 l_local_sp_id NUMBER := 0;
5645 x_progress VARCHAR2(2000);
5646 e_SOA_GET_FLAG_VALUE EXCEPTION;
5647 BEGIN
5648
5649 --
5650 -- RUN mode - normal process execution
5651 --
5652 IF (funcmode = 'RUN') THEN
5653
5654 -- The run code
5655
5656 SET_SDP_CONTEXT
5657 (ITEMTYPE
5658 ,ITEMKEY
5659 ,ACTID
5660 ,'SET_WI'
5661 ,RESULTOUT
5662 );
5663
5664 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
5665
5666 l_PORTING_ID :=
5667 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5668 (l_WORKITEM_INSTANCE_ID
5669 ,'PORTING_ID'
5670 );
5671
5672 l_sp_name :=
5673 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5674 (l_WORKITEM_INSTANCE_ID
5675 ,'SP_NAME'
5676 );
5677
5678 l_flag_name :=
5679 wf_engine.GetActivityAttrText
5680 (itemtype => itemtype,
5681 itemkey => itemkey,
5682 actid => actid,
5683 aname => 'FLAG_NAME'
5684 );
5685
5686 if (l_flag_name = 'LOCKED_FLAG') then
5687 -- Get the locked flag
5688 XNP_CORE.SOA_GET_LOCKED_FLAG
5689 (p_porting_id=>l_porting_id
5690 ,p_local_sp_id=>l_local_sp_id
5691 ,x_locked_flag=>l_flag_value
5692 ,x_error_code=>l_error_code
5693 ,x_error_message=>l_error_message
5694 );
5695 elsif (l_flag_name = 'BLOCKED_FLAG') then
5696 -- Get the blocked flag
5697 XNP_CORE.SOA_GET_BLOCKED_FLAG
5698 (p_porting_id=>l_porting_id
5699 ,p_local_sp_id=>l_local_sp_id
5700 ,x_blocked_flag=>l_flag_value
5701 ,x_error_code=>l_error_code
5702 ,x_error_message=>l_error_message
5703 );
5704 elsif (l_flag_name = 'NEW_SP_AUTHORIZATION_FLAG') then
5705 -- Get the new_sp_auth flag
5706 XNP_CORE.SOA_GET_NEW_SP_AUTH_FLAG
5707 (p_porting_id=>l_porting_id
5708 ,p_local_sp_id=>l_local_sp_id
5709 ,x_new_sp_auth_flag=>l_flag_value
5710 ,x_error_code=>l_error_code
5711 ,x_error_message=>l_error_message
5712 );
5713 elsif (l_flag_name = 'OLD_SP_AUTHORIZATION_FLAG') then
5714 -- Get the old_sp_auth flag
5715 XNP_CORE.SOA_GET_OLD_SP_AUTH_FLAG
5716 (p_porting_id=>l_porting_id
5717 ,p_local_sp_id=>l_local_sp_id
5718 ,x_old_sp_auth_flag=>l_flag_value
5719 ,x_error_code=>l_error_code
5720 ,x_error_message=>l_error_message
5721 );
5722 elsif (l_flag_name = 'CONCURRENCE_FLAG') then
5723 -- Get the concurrence flag
5724 XNP_CORE.SOA_GET_CONCURRENCE_FLAG
5725 (p_porting_id=>l_porting_id
5726 ,p_local_sp_id=>l_local_sp_id
5727 ,x_concurrence_flag=>l_flag_value
5728 ,x_error_code=>l_error_code
5729 ,x_error_message=>l_error_message
5730 );
5731 else
5732 raise e_SOA_GET_FLAG_VALUE;
5733 end if;
5734
5735
5736 IF l_error_code <> 0 THEN
5737 raise e_SOA_GET_FLAG_VALUE;
5738 END IF;
5739
5740 -- Completion
5741 resultout := 'COMPLETE:'||l_flag_value;
5742 RETURN;
5743 END IF;
5744 --
5745 -- CANCEL mode
5746 --
5747 -- This is in the event that the activity must be undone.
5748 --
5749 IF (funcmode = 'CANCEL' ) THEN
5750 -- The cancel code
5751
5752 null;
5753 -- no result needed
5754 resultout := 'COMPLETE';
5755 return;
5756 END IF;
5757
5758 -- For other execution modes: return null
5759
5760 resultout := '';
5761 return;
5762
5763 EXCEPTION
5764 WHEN OTHERS THEN
5765 ------------------------------------------------------------------
5766 -- Record this function call in the error
5767 -- system in case of an exception
5768 ------------------------------------------------------------------
5769
5770
5771 IF (l_error_code <> 0) THEN
5772 x_progress := to_char(l_error_code)||':'||l_error_message;
5773 ELSE
5774 x_progress := to_char(SQLCODE)||':'||SQLERRM;
5775 END IF;
5776
5777 XNP_UTILS.NOTIFY_ERROR
5778 (P_PKG_NAME => 'XNP_WF_STANDARD'
5779 ,P_PROC_NAME => 'SOA_GET_FLAG_VALUE'
5780 ,P_MSG_NAME => 'SOA_GET_FLAG_VALUE_ERR'
5781 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
5782 ,P_TOK1 => 'ERROR_TEXT'
5783 ,P_VAL1 => x_progress
5784 ,P_TOK2 => 'FLAG_NAME'
5785 ,P_VAL2 => l_flag_name
5786 );
5787
5788 RAISE;
5789 END SOA_GET_FLAG_VALUE;
5790
5791 --------------------------------------------------------------------
5792 -- Updates the DATE for the given porting record
5793 -- given the PORTING_ID. The date to update i.e.
5794 -- NEW_SP_DUE_DATE, OLD_SP_DUE_DATE,ACTIVATION_DUE_DATE,etc
5795 --
5796 -- Workitem Parameter: PORTING_ID
5797 -- Item Attr: Gets the Item Attributes WORKITEM_INSTANCE
5798 -- Workitem Paramters : PORTING_ID
5799 -- Calls XNP_CORE.<function to update the right date>
5800 --
5801 ------------------------------------------------------------------
5802 PROCEDURE SOA_UPDATE_DATE
5803 (ITEMTYPE IN VARCHAR2
5804 ,ITEMKEY IN VARCHAR2
5805 ,ACTID IN NUMBER
5806 ,FUNCMODE IN VARCHAR2
5807 ,RESULTOUT OUT NOCOPY VARCHAR2
5808 )
5809 IS
5810 l_ORDER_ID NUMBER;
5811 l_LINEITEM_ID NUMBER;
5812 l_WORKITEM_INSTANCE_ID NUMBER;
5813 l_FA_INSTANCE_ID NUMBER;
5814 l_error_code NUMBER := 0;
5815 l_error_message VARCHAR2(2000);
5816 l_porting_id VARCHAR2(80);
5817 l_date_value VARCHAR2(200);
5818 l_date_name VARCHAR2(40);
5819 l_sp_name VARCHAR2(80) := null;
5820 l_local_sp_id NUMBER := 0;
5821 x_progress VARCHAR2(2000);
5822 e_SOA_UPDATE_DATE EXCEPTION;
5823 BEGIN
5824
5825 --
5826 -- RUN mode - normal process execution
5827 --
5828 IF (funcmode = 'RUN') THEN
5829
5830 -- The run code
5831
5832 SET_SDP_CONTEXT
5833 (ITEMTYPE
5834 ,ITEMKEY
5835 ,ACTID
5836 ,'SET_ORD_WI'
5837 ,RESULTOUT
5838 );
5839
5840 l_ORDER_ID := g_ORDER_ID;
5841 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
5842
5843 l_PORTING_ID :=
5844 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5845 (l_WORKITEM_INSTANCE_ID
5846 ,'PORTING_ID'
5847 );
5848
5849 l_sp_name :=
5850 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5851 (l_WORKITEM_INSTANCE_ID
5852 ,'SP_NAME'
5853 );
5854
5855 l_date_name :=
5856 wf_engine.GetActivityAttrText
5857 (itemtype => itemtype,
5858 itemkey => itemkey,
5859 actid => actid,
5860 aname => 'DATE_NAME'
5861 );
5862
5863 if (l_date_name = 'NEW_SP_DUE_DATE') then
5864
5865 l_date_value :=
5866 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5867 (l_WORKITEM_INSTANCE_ID
5868 ,'NEW_SP_DUE_DATE'
5869 );
5870
5871 XNP_CORE.SOA_UPDATE_NEW_SP_DUE_DATE
5872 (p_order_id =>l_order_id,
5873 p_lineitem_id =>l_lineitem_id,
5874 p_workitem_instance_id =>l_workitem_instance_id ,
5875 p_fa_instance_id =>l_fa_instance_id,
5876 p_porting_id =>l_porting_id,
5877 p_local_sp_id =>l_local_sp_id,
5878 p_new_sp_due_date =>xnp_utils.canonical_to_date(l_date_value),
5879 x_error_code =>l_error_code,
5880 x_error_message =>l_error_message
5881 );
5882
5883 elsif (l_date_name = 'OLD_SP_DUE_DATE') then
5884
5885 l_date_value :=
5886 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5887 (l_WORKITEM_INSTANCE_ID
5888 ,'OLD_SP_DUE_DATE'
5889 );
5890
5891 XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE
5892 (p_order_id =>l_order_id,
5893 p_lineitem_id =>l_lineitem_id,
5894 p_workitem_instance_id =>l_workitem_instance_id ,
5895 p_fa_instance_id =>l_fa_instance_id,
5896 p_porting_id =>l_porting_id,
5897 p_local_sp_id =>l_local_sp_id,
5898 p_OLD_SP_DUE_DATE =>xnp_utils.canonical_to_date(l_date_value),
5899 x_error_code =>l_error_code,
5900 x_error_message =>l_error_message
5901 );
5902
5903 elsif (l_date_name = 'ACTIVATION_DUE_DATE') then
5904
5905 l_date_value :=
5906 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5907 (l_WORKITEM_INSTANCE_ID
5908 ,'ACTIVATION_DUE_DATE'
5909 );
5910
5911 XNP_CORE.SOA_UPDATE_ACTIVATION_DUE_DATE
5912 (p_order_id =>l_order_id,
5913 p_lineitem_id =>l_lineitem_id,
5914 p_workitem_instance_id =>l_workitem_instance_id ,
5915 p_fa_instance_id =>l_fa_instance_id,
5916 p_porting_id =>l_porting_id,
5917 p_local_sp_id =>l_local_sp_id,
5918 p_ACTIVATION_DUE_DATE =>xnp_utils.canonical_to_date(l_date_value),
5919 x_error_code =>l_error_code,
5920 x_error_message =>l_error_message
5921 );
5922
5923 elsif (l_date_name = 'DISCONNECT_DUE_DATE') then
5924
5925 l_date_value :=
5926 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5927 (l_WORKITEM_INSTANCE_ID
5928 ,'DISCONNECT_DUE_DATE'
5929 );
5930
5931 XNP_CORE.SOA_UPDATE_DISCONN_DUE_DATE
5932 (p_order_id =>l_order_id,
5933 p_lineitem_id =>l_lineitem_id,
5934 p_workitem_instance_id =>l_workitem_instance_id ,
5935 p_fa_instance_id =>l_fa_instance_id,
5936 p_porting_id =>l_porting_id,
5937 p_disconnect_due_date =>xnp_utils.canonical_to_date(l_date_value),
5938 x_error_code =>l_error_code,
5939 x_error_message =>l_error_message
5940 );
5941
5942 elsif (l_date_name = 'EFFECTIVE_RELEASE_DUE_DATE') then
5943
5944 l_date_value :=
5945 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5946 (l_WORKITEM_INSTANCE_ID
5947 ,'EFFECTIVE_RELEASE_DUE_DATE'
5948 );
5949
5950 XNP_CORE.SOA_UPDATE_EFFECT_REL_DUE_DATE
5951 (p_order_id =>l_order_id,
5952 p_lineitem_id =>l_lineitem_id,
5953 p_workitem_instance_id =>l_workitem_instance_id ,
5954 p_fa_instance_id =>l_fa_instance_id,
5955 p_porting_id =>l_porting_id,
5956 p_effective_release_due_date =>xnp_utils.canonical_to_date(l_date_value),
5957 x_error_code =>l_error_code,
5958 x_error_message =>l_error_message
5959 );
5960
5961 elsif (l_date_name = 'NUMBER_RETURNED_DUE_DATE') then
5962
5963 l_date_value :=
5964 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
5965 (l_WORKITEM_INSTANCE_ID
5966 ,'NUMBER_RETURNED_DUE_DATE'
5967 );
5968
5969 XNP_CORE.SOA_UPDATE_NUM_RETURN_DUE_DATE
5970 (p_order_id =>l_order_id,
5971 p_lineitem_id =>l_lineitem_id,
5972 p_workitem_instance_id =>l_workitem_instance_id ,
5973 p_fa_instance_id =>l_fa_instance_id,
5974 p_porting_id =>l_porting_id,
5975 p_number_returned_due_date =>xnp_utils.canonical_to_date(l_date_value),
5976 x_error_code =>l_error_code,
5977 x_error_message =>l_error_message
5978 );
5979 else
5980 null; -- Ignore it
5981 end if;
5982
5983 IF l_error_code <> 0 THEN
5984 raise e_SOA_UPDATE_DATE;
5985 END IF;
5986
5987 -- Completion
5988 resultout := 'COMPLETE';
5989 RETURN;
5990 END IF;
5991 --
5992 -- CANCEL mode
5993 --
5994 -- This is in the event that the activity must be undone.
5995 --
5996 IF (funcmode = 'CANCEL' ) THEN
5997 -- The cancel code
5998
5999 null;
6000 -- no result needed
6001 resultout := 'COMPLETE';
6002 return;
6003 END IF;
6004
6005 -- For other execution modes: return null
6006
6007 resultout := '';
6008 return;
6009
6010 EXCEPTION
6011 WHEN OTHERS THEN
6012 ------------------------------------------------------------------
6013 -- Record this function call in the error
6014 -- system in case of an exception
6015 ------------------------------------------------------------------
6016 IF (l_error_code <> 0) THEN
6017 x_progress := to_char(l_error_code)||':'||l_error_message;
6018 ELSE
6019 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6020 END IF;
6021
6022 XNP_UTILS.NOTIFY_ERROR
6023 (P_PKG_NAME => 'XNP_WF_STANDARD'
6024 ,P_PROC_NAME => 'SOA_UPDATE_DATE'
6025 ,P_MSG_NAME => 'SOA_UPDATE_DATE_ERR'
6026 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6027 ,P_TOK1 => 'ERROR_TEXT'
6028 ,P_VAL1 => x_progress
6029 ,P_TOK2 => 'DATE_NAME'
6030 ,P_VAL2 => l_date_name
6031 );
6032
6033 RAISE;
6034 END SOA_UPDATE_DATE;
6035
6036
6037 --------------------------------------------------------------------
6038 -- Description:
6039 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
6040 -- calls XNP_CORE.CHECK_IF_SP_ASSIGNED
6041 -- Completes the path based on the result
6042 -- Mandatory WI params: STARTING_NUMBER,ENDING_NUMBER,DONOR_SP_ID
6043 ------------------------------------------------------------------
6044 PROCEDURE CHECK_IF_DONOR_CAN_PORT_OUT
6045 (ITEMTYPE IN VARCHAR2
6046 ,ITEMKEY IN VARCHAR2
6047 ,ACTID IN NUMBER
6048 ,FUNCMODE IN VARCHAR2
6049 ,RESULTOUT OUT NOCOPY VARCHAR2
6050 )
6051 IS
6052 x_progress VARCHAR2(2000);
6053 l_WORKITEM_INSTANCE_ID NUMBER;
6054 l_starting_number varchar2(80) := null;
6055 l_ending_number varchar2(80) := null;
6056 l_donor_sp_name varchar2(80) := null;
6057 l_donor_sp_id NUMBER := 0;
6058 l_error_code NUMBER := 0;
6059 l_error_message VARCHAR2(2000);
6060 l_check_status VARCHAR2(1);
6061 e_CHECK_IF_DONOR_CAN_PORT_OUT EXCEPTION;
6062 BEGIN
6063
6064 --
6065 -- RUN mode - normal process execution
6066 --
6067 IF (funcmode = 'RUN') THEN
6068
6069 -- The run code
6070
6071 SET_SDP_CONTEXT
6072 (ITEMTYPE
6073 ,ITEMKEY
6074 ,ACTID
6075 ,'SET_WI'
6076 ,RESULTOUT
6077 );
6078
6079 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6080
6081 l_STARTING_NUMBER :=
6082 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6083 (l_WORKITEM_INSTANCE_ID
6084 ,'STARTING_NUMBER'
6085 );
6086
6087 l_ENDING_NUMBER :=
6088 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6089 (l_WORKITEM_INSTANCE_ID
6090 ,'ENDING_NUMBER'
6091 );
6092
6093 l_DONOR_SP_NAME :=
6094 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6095 (l_WORKITEM_INSTANCE_ID
6096 ,'DONOR_SP_ID'
6097 );
6098
6099 XNP_CORE.GET_SP_ID
6100 (p_SP_NAME=>l_DONOR_SP_NAME
6101 ,x_SP_ID=>l_donor_sp_id
6102 ,x_ERROR_CODE=>l_error_code
6103 ,x_ERROR_MESSAGE=>l_error_message
6104 );
6105
6106 IF l_error_code <> 0 THEN
6107 raise e_CHECK_IF_DONOR_CAN_PORT_OUT;
6108 END IF;
6109
6110 -- Check this is the SP which has provisioned the
6111 -- entire number range or is the assigned sp id
6112
6113 XNP_CORE.CHECK_IF_SP_ASSIGNED
6114 (p_STARTING_NUMBER =>l_starting_number
6115 ,p_ENDING_NUMBER =>l_ending_number
6116 ,p_SP_ID =>l_donor_sp_id
6117 ,x_CHECK_IF_ASSIGNED=>l_check_status
6118 ,x_ERROR_CODE =>l_error_code
6119 ,x_ERROR_MESSAGE =>l_error_message
6120 );
6121
6122 IF l_error_code <> 0 THEN
6123 raise e_CHECK_IF_DONOR_CAN_PORT_OUT;
6124 END IF;
6125
6126 -- Completion: If check status is true the traces
6127 -- the 'YES' path else trace the 'NO' path
6128 IF l_check_status = 'Y' THEN
6129 resultout := 'COMPLETE:T';
6130 ELSE
6131 resultout := 'COMPLETE:F';
6132 END IF;
6133
6134 RETURN;
6135 END IF;
6136 --
6137 -- CANCEL mode
6138 --
6139 -- This is in the event that the activity must be undone.
6140 --
6141 IF (funcmode = 'CANCEL' ) THEN
6142 -- The cancel code
6143
6144 null;
6145 -- no result needed
6146 resultout := 'COMPLETE';
6147 return;
6148 END IF;
6149
6150 -- For other execution modes: return null
6151
6152 resultout := '';
6153 return;
6154
6155 EXCEPTION
6156 WHEN OTHERS THEN
6157 ------------------------------------------------------------------
6158 -- Record this function call in the error
6159 -- system in case of an exception
6160 ------------------------------------------------------------------
6161
6162 IF (l_error_code <> 0) THEN
6163 x_progress := to_char(l_error_code)||':'||l_error_message;
6164 ELSE
6165 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6166 END IF;
6167
6168 XNP_UTILS.NOTIFY_ERROR
6169 (P_PKG_NAME => 'XNP_WF_STANDARD'
6170 ,P_PROC_NAME => 'CHECK_IF_DONOR_CAN_PORT_OUT'
6171 ,P_MSG_NAME => 'CHECK_IF_SP_CAN_PORT_OUT_ERR'
6172 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6173 ,P_TOK1 => 'ERROR_TEXT'
6174 ,P_VAL1 => x_progress
6175 );
6176
6177 RAISE;
6178
6179 END CHECK_IF_DONOR_CAN_PORT_OUT;
6180
6181 --------------------------------------------------------------------
6182 -- Description:
6183 -- Checks if the DONOR_SP_ID (WI param) is the Initial donor
6184 -- Gets the Item Attributes WORKITEM_INSTANCE
6185 -- calls XNP_CORE.SOA_CHECK_IF_INITIAL_DONOR
6186 -- Completes the path based on the result
6187 -- Mandatory WI params: STARTING_NUMBER,ENDING_NUMBER,DONOR_SP_ID
6188 ------------------------------------------------------------------
6189 PROCEDURE CHECK_IF_DON_IS_INITIAL_DON
6190 (ITEMTYPE IN VARCHAR2
6191 ,ITEMKEY IN VARCHAR2
6192 ,ACTID IN NUMBER
6193 ,FUNCMODE IN VARCHAR2
6194 ,RESULTOUT OUT NOCOPY VARCHAR2
6195 )
6196 IS
6197 x_progress VARCHAR2(2000);
6198 l_WORKITEM_INSTANCE_ID NUMBER;
6199 l_starting_number varchar2(80) := null;
6200 l_ending_number varchar2(80) := null;
6201 l_donor_sp_name varchar2(80) := null;
6202 l_donor_sp_id NUMBER := 0;
6203 l_error_code NUMBER := 0;
6204 l_error_message VARCHAR2(2000);
6205 l_check_status VARCHAR2(1);
6206 e_CHECK_IF_DON_IS_INITIAL_DON EXCEPTION;
6207 BEGIN
6208
6209 --
6210 -- RUN mode - normal process execution
6211 --
6212 IF (funcmode = 'RUN') THEN
6213
6214 -- The run code
6215
6216 SET_SDP_CONTEXT
6217 (ITEMTYPE
6218 ,ITEMKEY
6219 ,ACTID
6220 ,'SET_WI'
6221 ,RESULTOUT
6222 );
6223
6224 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6225
6226 l_STARTING_NUMBER :=
6227 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6228 (l_WORKITEM_INSTANCE_ID
6229 ,'STARTING_NUMBER'
6230 );
6231
6232 l_ENDING_NUMBER :=
6233 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6234 (l_WORKITEM_INSTANCE_ID
6235 ,'ENDING_NUMBER'
6236 );
6237
6238 l_DONOR_SP_NAME :=
6239 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6240 (l_WORKITEM_INSTANCE_ID
6241 ,'DONOR_SP_ID'
6242 );
6243
6244 XNP_CORE.GET_SP_ID
6245 (p_SP_NAME =>l_donor_sp_name
6246 ,x_SP_ID =>l_donor_sp_id
6247 ,x_ERROR_CODE =>l_error_code
6248 ,x_ERROR_MESSAGE=>l_error_message
6249 );
6250 IF l_error_code <> 0
6251 THEN
6252 raise e_CHECK_IF_DON_IS_INITIAL_DON;
6253 END IF;
6254
6255 -- check if the given donor is the initial donor entire
6256 -- number range
6257 XNP_CORE.SOA_CHECK_IF_INITIAL_DONOR
6258 (p_DONOR_SP_ID =>l_donor_sp_id
6259 ,p_STARTING_NUMBER=>l_starting_number
6260 ,p_ENDING_NUMBER =>l_ending_number
6261 ,x_CHECK_STATUS =>l_check_status
6262 ,x_ERROR_CODE =>l_error_code
6263 ,x_ERROR_MESSAGE =>l_error_message
6264 );
6265 IF l_error_code <> 0
6266 THEN
6267 raise e_CHECK_IF_DON_IS_INITIAL_DON;
6268 END IF;
6269
6270 -- Completion: If check status is 'Y' then traces
6271 -- the 'T' path else trace the 'F' path
6272 if (l_check_status = 'Y') then
6273 resultout := 'COMPLETE:T';
6274 else
6275 resultout := 'COMPLETE:F';
6276 end if;
6277
6278 RETURN;
6279 END IF;
6280 --
6281 -- CANCEL mode
6282 --
6283 -- This is in the event that the activity must be undone.
6284 --
6285 IF (funcmode = 'CANCEL' ) THEN
6286 -- The cancel code
6287
6288 null;
6289 -- no result needed
6290 resultout := 'COMPLETE';
6291 return;
6292 END IF;
6293
6294 -- For other execution modes: return null
6295
6296 resultout := '';
6297 return;
6298
6299 EXCEPTION
6300 WHEN OTHERS THEN
6301 ------------------------------------------------------------------
6302 -- Record this function call in the error
6303 -- system in case of an exception
6304 ------------------------------------------------------------------
6305
6306 IF (l_error_code <> 0) THEN
6307 x_progress := to_char(l_error_code)||':'||l_error_message;
6308 ELSE
6309 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6310 END IF;
6311
6312 XNP_UTILS.NOTIFY_ERROR
6313 (P_PKG_NAME => 'XNP_WF_STANDARD'
6314 ,P_PROC_NAME => 'CHECK_IF_DON_IS_INITIAL_DON'
6315 ,P_MSG_NAME => 'CHECK_IF_DON_IS_INIT_DON_ERR'
6316 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6317 ,P_TOK1 => 'ERROR_TEXT'
6318 ,P_VAL1 => x_progress
6319 ,P_TOK2 => 'DONOR_SP_ID'
6320 ,P_VAL2 => l_donor_sp_name
6321 );
6322
6323 RAISE;
6324
6325 END CHECK_IF_DON_IS_INITIAL_DON;
6326
6327 PROCEDURE SOA_UPDATE_NOTES_INFO
6328 (ITEMTYPE IN VARCHAR2
6329 ,ITEMKEY IN VARCHAR2
6330 ,ACTID IN NUMBER
6331 ,FUNCMODE IN VARCHAR2
6332 ,RESULTOUT OUT NOCOPY VARCHAR2
6333 )
6334 IS
6335 l_ORDER_ID NUMBER;
6336 l_LINEITEM_ID NUMBER;
6337 l_WORKITEM_INSTANCE_ID NUMBER;
6338 l_FA_INSTANCE_ID NUMBER;
6339 x_progress VARCHAR2(2000);
6340 l_error_code NUMBER := 0;
6341 l_error_message VARCHAR2(2000);
6342 e_SOA_UPDATE_NOTES_INFO EXCEPTION;
6343
6344 BEGIN
6345
6346 --
6347 -- RUN mode - normal process execution
6348 --
6349 IF (funcmode = 'RUN') THEN
6350
6351 -- The run code
6352
6353 SET_SDP_CONTEXT
6354 (ITEMTYPE
6355 ,ITEMKEY
6356 ,ACTID
6357 ,'SET_ORD_WI'
6358 ,RESULTOUT
6359 );
6360
6361 l_ORDER_ID := g_ORDER_ID;
6362 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6363
6364 XNP_STANDARD.SOA_UPDATE_NOTES_INFO
6365 (l_order_id,
6366 l_lineitem_id,
6367 l_WORKITEM_INSTANCE_ID,
6368 l_fa_instance_id,
6369 l_error_code,
6370 l_error_message
6371 );
6372
6373 IF l_error_code <> 0 THEN
6374 raise e_SOA_UPDATE_NOTES_INFO;
6375 END IF;
6376
6377 resultout := 'COMPLETE';
6378 RETURN;
6379 END IF;
6380 --
6381 -- CANCEL mode
6382 --
6383 -- This is in the event that the activity must be undone.
6384 --
6385 IF (funcmode = 'CANCEL' ) THEN
6386 -- The cancel code
6387
6388 null;
6389 -- no result needed
6390 resultout := 'COMPLETE';
6391 return;
6392 END IF;
6393
6394 -- For other execution modes: return null
6395
6396 resultout := '';
6397 return;
6398
6399 EXCEPTION
6400 WHEN OTHERS THEN
6401 ------------------------------------------------------------------
6402 -- Record this function call in the error
6403 -- system in case of an exception
6404 ------------------------------------------------------------------
6405
6406
6407 IF (l_error_code <> 0) THEN
6408 x_progress := to_char(l_error_code)||':'||l_error_message;
6409 ELSE
6410 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6411 END IF;
6412
6413 XNP_UTILS.NOTIFY_ERROR
6414 (P_PKG_NAME => 'XNP_WF_STANDARD'
6415 ,P_PROC_NAME => 'SOA_UPDATE_NOTES_INFO'
6416 ,P_MSG_NAME => 'SOA_UPDATE_NOTES_INFO_ERR'
6417 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6418 ,P_TOK1 => 'ERROR_TEXT'
6419 ,P_VAL1 => x_progress
6420 );
6421
6422 RAISE;
6423
6424 END SOA_UPDATE_NOTES_INFO;
6425
6426
6427 PROCEDURE SOA_UPDATE_NETWORK_INFO
6428 (ITEMTYPE IN VARCHAR2
6429 ,ITEMKEY IN VARCHAR2
6430 ,ACTID IN NUMBER
6431 ,FUNCMODE IN VARCHAR2
6432 ,RESULTOUT OUT NOCOPY VARCHAR2
6433 )
6434 IS
6435 l_ORDER_ID NUMBER;
6436 l_LINEITEM_ID NUMBER;
6437 l_WORKITEM_INSTANCE_ID NUMBER;
6438 l_FA_INSTANCE_ID NUMBER;
6439 x_progress VARCHAR2(2000);
6440 l_error_code NUMBER := 0;
6441 l_error_message VARCHAR2(2000);
6442 e_SOA_UPDATE_NETWORK_INFO EXCEPTION;
6443
6444 BEGIN
6445
6446 --
6447 -- RUN mode - normal process execution
6448 --
6449 IF (funcmode = 'RUN') THEN
6450
6451 -- The run code
6452
6453 SET_SDP_CONTEXT
6454 (ITEMTYPE
6455 ,ITEMKEY
6456 ,ACTID
6457 ,'SET_ORD_WI'
6458 ,RESULTOUT
6459 );
6460
6461 l_ORDER_ID := g_ORDER_ID;
6462 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6463
6464 XNP_STANDARD.SOA_UPDATE_NETWORK_INFO
6465 (l_order_id,
6466 l_lineitem_id,
6467 l_WORKITEM_INSTANCE_ID,
6468 l_fa_instance_id,
6469 l_error_code,
6470 l_error_message
6471 );
6472
6473 IF l_error_code <> 0 THEN
6474 raise e_SOA_UPDATE_NETWORK_INFO;
6475 END IF;
6476
6477 resultout := 'COMPLETE';
6478 RETURN;
6479 END IF;
6480 --
6481 -- CANCEL mode
6482 --
6483 -- This is in the event that the activity must be undone.
6484 --
6485 IF (funcmode = 'CANCEL' ) THEN
6486 -- The cancel code
6487
6488 null;
6489 -- no result needed
6490 resultout := 'COMPLETE';
6491 return;
6492 END IF;
6493
6494 -- For other execution modes: return null
6495
6496 resultout := '';
6497 return;
6498
6499 EXCEPTION
6500 WHEN OTHERS THEN
6501 ------------------------------------------------------------------
6502 -- Record this function call in the error
6503 -- system in case of an exception
6504 ------------------------------------------------------------------
6505
6506
6507 IF (l_error_code <> 0) THEN
6508 x_progress := to_char(l_error_code)||':'||l_error_message;
6509 ELSE
6510 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6511 END IF;
6512
6513 XNP_UTILS.NOTIFY_ERROR
6514 (P_PKG_NAME => 'XNP_WF_STANDARD'
6515 ,P_PROC_NAME => 'SOA_UPDATE_NETWORK_INFO'
6516 ,P_MSG_NAME => 'SOA_UPDATE_NETWORK_INFO_ERR'
6517 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6518 ,P_TOK1 => 'ERROR_TEXT'
6519 ,P_VAL1 => x_progress
6520 );
6521
6522
6523 RAISE;
6524
6525 END SOA_UPDATE_NETWORK_INFO;
6526
6527
6528 PROCEDURE SOA_UPDATE_CUSTOMER_INFO
6529 (ITEMTYPE IN VARCHAR2
6530 ,ITEMKEY IN VARCHAR2
6531 ,ACTID IN NUMBER
6532 ,FUNCMODE IN VARCHAR2
6533 ,RESULTOUT OUT NOCOPY VARCHAR2
6534 )
6535 IS
6536 l_ORDER_ID NUMBER;
6537 l_LINEITEM_ID NUMBER;
6538 l_WORKITEM_INSTANCE_ID NUMBER;
6539 l_FA_INSTANCE_ID NUMBER;
6540 x_progress VARCHAR2(2000);
6541 l_error_code NUMBER := 0;
6542 l_error_message VARCHAR2(2000);
6543 e_SOA_UPDATE_CUSTOMER_INFO EXCEPTION;
6544
6545 BEGIN
6546
6547 --
6548 -- RUN mode - normal process execution
6549 --
6550 IF (funcmode = 'RUN') THEN
6551
6552 -- The run code
6553
6554 SET_SDP_CONTEXT
6555 (ITEMTYPE
6556 ,ITEMKEY
6557 ,ACTID
6558 ,'SET_ORD_WI'
6559 ,RESULTOUT
6560 );
6561
6562 l_ORDER_ID := g_ORDER_ID;
6563 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6564
6565 XNP_STANDARD.SOA_UPDATE_CUSTOMER_INFO
6566 (l_order_id,
6567 l_lineitem_id,
6568 l_workitem_instance_id,
6569 l_fa_instance_id,
6570 l_error_code,
6571 l_error_message
6572 );
6573
6574 IF l_error_code <> 0 THEN
6575 raise e_SOA_UPDATE_CUSTOMER_INFO;
6576 END IF;
6577
6578 resultout := 'COMPLETE';
6579 RETURN;
6580 END IF;
6581 --
6582 -- CANCEL mode
6583 --
6584 -- This is in the event that the activity must be undone.
6585 --
6586 IF (funcmode = 'CANCEL' ) THEN
6587 -- The cancel code
6588
6589 null;
6590 -- no result needed
6591 resultout := 'COMPLETE';
6592 return;
6593 END IF;
6594
6595 -- For other execution modes: return null
6596
6597 resultout := '';
6598 return;
6599
6600 EXCEPTION
6601 WHEN OTHERS THEN
6602 ------------------------------------------------------------------
6603 -- Record this function call in the error
6604 -- system in case of an exception
6605 ------------------------------------------------------------------
6606
6607
6608 IF (l_error_code <> 0) THEN
6609 x_progress := to_char(l_error_code)||':'||l_error_message;
6610 ELSE
6611 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6612 END IF;
6613
6614 XNP_UTILS.NOTIFY_ERROR
6615 (P_PKG_NAME => 'XNP_WF_STANDARD'
6616 ,P_PROC_NAME => 'SOA_UPDATE_CUSTOMER_INFO'
6617 ,P_MSG_NAME => 'SOA_UPDATE_CUSTOMER_INFO_ERR'
6618 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6619 ,P_TOK1 => 'ERROR_TEXT'
6620 ,P_VAL1 => x_progress
6621 );
6622
6623 RAISE;
6624
6625 END SOA_UPDATE_CUSTOMER_INFO;
6626
6627
6628 PROCEDURE PREPARE_CUSTOM_NOTIFN
6629 (ITEMTYPE IN VARCHAR2
6630 ,ITEMKEY IN VARCHAR2
6631 ,ACTID IN NUMBER
6632 ,FUNCMODE IN VARCHAR2
6633 ,RESULTOUT OUT NOCOPY VARCHAR2
6634 )
6635 IS
6636 x_progress VARCHAR2(2000);
6637 l_WORKITEM_INSTANCE_ID NUMBER;
6638 l_ORDER_ID NUMBER;
6639 l_FA_INSTANCE_ID NUMBER;
6640 l_body varchar2(2000) := null;
6641 l_subject varchar2(2000) := null;
6642 l_error_code NUMBER := 0;
6643 l_error_message VARCHAR2(2000);
6644 l_notfn_msg_name varchar2(200) := null;
6645 e_PREPARE_CUSTOM_NOTFN EXCEPTION;
6646
6647 BEGIN
6648
6649 SET_SDP_CONTEXT
6650 (ITEMTYPE
6651 ,ITEMKEY
6652 ,ACTID
6653 ,'SET_ORD_WI'
6654 ,RESULTOUT
6655 );
6656
6657 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6658 l_ORDER_ID := g_ORDER_ID;
6659
6660 ------------------------------------------------------------------
6661 -- Get the activity attribute NOTIFN_MSG_NAME
6662 -- Get the string corresponding to this FND message
6663 -- parse to see the tokens in it. Substitute the tokens
6664 -- with the value
6665 ------------------------------------------------------------------
6666 l_notfn_msg_name :=
6667 wf_engine.GetActivityAttrText
6668 (itemtype => itemtype,
6669 itemkey => itemkey,
6670 actid => actid,
6671 aname => 'NOTIFN_MSG_NAME'
6672 );
6673
6674 xnp_utils.get_interpreted_notification
6675 (p_workitem_instance_id => l_workitem_instance_id
6676 ,p_mls_message_name => l_notfn_msg_name
6677 ,x_subject => l_subject
6678 ,x_body => l_body
6679 ,x_error_code => l_error_code
6680 ,x_error_message => l_error_message
6681 );
6682
6683 if (l_error_code <> 0) then
6684 raise e_PREPARE_CUSTOM_NOTFN;
6685 end if;
6686
6687 ------------------------------------------------------------------
6688 -- Set the item attributes MSG_SUBJECT, MSG_BODY
6689 ------------------------------------------------------------------
6690
6691 wf_engine.SetItemAttrText
6692 (itemtype => itemtype
6693 ,itemkey => itemkey
6694 ,aname => 'MSG_SUBJECT'
6695 ,avalue => l_subject
6696 );
6697
6698 wf_engine.SetItemAttrText
6699 (itemtype => itemtype
6700 ,itemkey => itemkey
6701 ,aname => 'MSG_BODY'
6702 ,avalue => l_body
6703 );
6704
6705 resultout := 'COMPLETE';
6706 RETURN;
6707
6708 EXCEPTION
6709
6710 WHEN OTHERS THEN
6711 ------------------------------------------------------------------
6712 -- Record this function call in the error
6713 -- system in case of an exception
6714 ------------------------------------------------------------------
6715
6716
6717 IF (l_error_code <> 0) THEN
6718 x_progress := to_char(l_error_code)||':'||l_error_message
6719 ;
6720 ELSE
6721 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6722 END IF;
6723
6724 XNP_UTILS.NOTIFY_ERROR
6725 (P_PKG_NAME => 'XNP_WF_STANDARD'
6726 ,P_PROC_NAME => 'PREPARE_CUSTOM_NOTFN'
6727 ,P_MSG_NAME => 'PREPARE_CUSTOM_NOTFN_ERR'
6728 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6729 ,P_TOK1 => 'ERROR_TEXT'
6730 ,P_VAL1 => x_progress
6731 );
6732
6733
6734 RAISE;
6735
6736 END PREPARE_CUSTOM_NOTIFN;
6737
6738
6739
6740 ------------------------------------------------------------------
6741 -- Called when: need to update the SV status according
6742 -- to the activity parameter SV_STATUS
6743 -- Description:
6744 -- Gets the Item Attributes WORKITEM_INSTANCE, ORDER_ID
6745 -- Calls XNP_STANDARD.SOA_UPDATE_SV_STATUS
6746 ------------------------------------------------------------------
6747 PROCEDURE SOA_UPDATE_CUR_SV_STATUS
6748 (ITEMTYPE IN VARCHAR2
6749 ,ITEMKEY IN VARCHAR2
6750 ,ACTID IN NUMBER
6751 ,FUNCMODE IN VARCHAR2
6752 ,RESULTOUT OUT NOCOPY VARCHAR2
6753 )
6754 IS
6755 l_ORDER_ID NUMBER;
6756 l_LINEITEM_ID NUMBER;
6757 l_WORKITEM_INSTANCE_ID NUMBER;
6758 l_FA_INSTANCE_ID NUMBER;
6759 l_NEW_STATUS_TYPE_CODE VARCHAR2(40);
6760 l_CUR_STATUS_TYPE_CODE VARCHAR2(40);
6761 l_STATUS_CHANGE_CAUSE_CODE VARCHAR2(40);
6762 l_error_code NUMBER := 0;
6763 l_error_message VARCHAR2(2000);
6764 x_progress VARCHAR2(2000);
6765 e_SOA_UPDATE_CUR_SV_STATUS EXCEPTION;
6766
6767 BEGIN
6768 --
6769 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
6770 --
6771 SET_SDP_CONTEXT
6772 (ITEMTYPE
6773 ,ITEMKEY
6774 ,ACTID
6775 ,'SET_ORD_WI'
6776 ,RESULTOUT
6777 );
6778
6779 --
6780 -- RUN mode - normal process execution
6781 --
6782 IF (funcmode = 'RUN') THEN
6783
6784 -- The run code
6785
6786 l_ORDER_ID := g_ORDER_ID;
6787 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID ;
6788
6789 l_CUR_STATUS_TYPE_CODE :=
6790 wf_engine.GetActivityAttrText (itemtype => itemtype,
6791 itemkey => itemkey,
6792 actid => actid,
6793 aname => 'CUR_STATUS_TYPE_CODE');
6794
6795 l_NEW_STATUS_TYPE_CODE :=
6796 wf_engine.GetActivityAttrText (itemtype => itemtype,
6797 itemkey => itemkey,
6798 actid => actid,
6799 aname => 'NEW_STATUS_TYPE_CODE');
6800
6801 l_STATUS_CHANGE_CAUSE_CODE :=
6802 wf_engine.GetActivityAttrText (itemtype => itemtype,
6803 itemkey => itemkey,
6804 actid => actid,
6805 aname => 'STATUS_CHANGE_CAUSE_CODE');
6806
6807 XNP_STANDARD.SOA_UPDATE_SV_STATUS
6808 (p_ORDER_ID => L_ORDER_ID,
6809 p_LINEITEM_ID =>l_LINEITEM_ID,
6810 p_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID,
6811 p_FA_INSTANCE_ID => l_FA_INSTANCE_ID,
6812 p_CUR_STATUS_TYPE_CODE => l_CUR_STATUS_TYPE_CODE,
6813 p_NEW_STATUS_TYPE_CODE => l_NEW_STATUS_TYPE_CODE,
6814 p_STATUS_CHANGE_CAUSE_CODE => l_STATUS_CHANGE_CAUSE_CODE,
6815 x_ERROR_CODE => l_error_code,
6816 x_ERROR_MESSAGE => l_error_message
6817 );
6818
6819 IF l_error_code <> 0 THEN
6820 raise e_SOA_UPDATE_CUR_SV_STATUS;
6821 END IF;
6822 -- Completion
6823 resultout := 'COMPLETE';
6824 RETURN;
6825 END IF;
6826 --
6827 -- CANCEL mode
6828 --
6829 -- This is in the event that the activity must be undone.
6830 --
6831 IF (funcmode = 'CANCEL' ) THEN
6832 -- The cancel code
6833
6834 null;
6835 -- no result needed
6836 resultout := 'COMPLETE';
6837 return;
6838 END IF;
6839
6840 -- For other execution modes: return null
6841
6842 resultout := '';
6843 return;
6844
6845 EXCEPTION
6846 WHEN OTHERS THEN
6847 ------------------------------------------------------------------
6848 -- Record this function call in the error
6849 -- system in case of an exception
6850 ------------------------------------------------------------------
6851
6852
6853 IF (l_error_code <> 0) THEN
6854 x_progress := to_char(l_error_code)||':'||l_error_message;
6855 ELSE
6856 x_progress := to_char(SQLCODE)||':'||SQLERRM;
6857 END IF;
6858
6859 XNP_UTILS.NOTIFY_ERROR
6860 (P_PKG_NAME => 'XNP_WF_STANDARD'
6861 ,P_PROC_NAME => 'SOA_UPDATE_CUR_SV_STATUS'
6862 ,P_MSG_NAME => 'SOA_UPDATE_SV_STATUS_ERR'
6863 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6864 ,P_TOK1 => 'ERROR_TEXT'
6865 ,P_VAL1 => x_progress
6866 );
6867
6868 RAISE;
6869 END SOA_UPDATE_CUR_SV_STATUS;
6870
6871 PROCEDURE SMS_UPDATE_PROV_DONE_DATE
6872 (ITEMTYPE IN VARCHAR2
6873 ,ITEMKEY IN VARCHAR2
6874 ,ACTID IN NUMBER
6875 ,FUNCMODE IN VARCHAR2
6876 ,RESULTOUT OUT NOCOPY VARCHAR2
6877 )
6878 IS
6879 l_ORDER_ID NUMBER;
6880 l_LINEITEM_ID NUMBER;
6881 l_WORKITEM_INSTANCE_ID NUMBER;
6882 l_FA_INSTANCE_ID NUMBER;
6883 l_STARTING_NUMBER VARCHAR2(80) := NULL;
6884 l_ENDING_NUMBER VARCHAR2(80) := NULL;
6885 l_error_code NUMBER := 0;
6886 l_error_message VARCHAR2(2000);
6887 x_progress VARCHAR2(2000);
6888 e_SMS_UPDATE_PROV_DONE_DATE exception;
6889
6890 BEGIN
6891 --
6892 -- RUN mode - normal process execution
6893 --
6894 IF (funcmode = 'RUN') THEN
6895
6896 -- The run code
6897
6898 SET_SDP_CONTEXT
6899 (ITEMTYPE
6900 ,ITEMKEY
6901 ,ACTID
6902 ,'SET_ORD_WI'
6903 ,RESULTOUT
6904 );
6905
6906 l_ORDER_ID := g_ORDER_ID;
6907 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
6908
6909 l_STARTING_NUMBER :=
6910 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6911 (l_WORKITEM_INSTANCE_ID
6912 ,'STARTING_NUMBER'
6913 );
6914
6915 l_ENDING_NUMBER :=
6916 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
6917 (l_WORKITEM_INSTANCE_ID
6918 ,'ENDING_NUMBER'
6919 );
6920
6921 XNP_CORE.SMS_UPDATE_PROV_DONE_DATE
6922 (p_order_id => l_order_id,
6923 p_lineitem_id => l_lineitem_id,
6924 p_workitem_instance_id => l_workitem_instance_id,
6925 p_fa_instance_id => l_fa_instance_id,
6926 p_starting_number => l_starting_number,
6927 p_ending_number => l_ending_number,
6928 x_error_code => l_error_code,
6929 x_error_message => l_error_message
6930 );
6931
6932 IF (l_error_code <> 0) THEN
6933 raise e_SMS_UPDATE_PROV_DONE_DATE;
6934 END IF;
6935
6936 -- Completion
6937 resultout := 'COMPLETE';
6938 RETURN;
6939
6940 END IF;
6941
6942 --
6943 -- CANCEL mode
6944 --
6945 -- This is in the event that the activity must be undone.
6946 --
6947 IF (funcmode = 'CANCEL' ) THEN
6948 -- The cancel code
6949 null;
6950 -- no result needed
6951 resultout := 'COMPLETE';
6952 return;
6953 END IF;
6954
6955 -- For other execution modes: return null
6956
6957 resultout := '';
6958 return;
6959
6960 EXCEPTION
6961 WHEN OTHERS THEN
6962 ------------------------------------------------------------------
6963 -- Record this function call in the error
6964 -- system in case of an exception
6965 ------------------------------------------------------------------
6966
6967 x_progress := to_char(l_error_code)||':'||l_error_message;
6968
6969
6970 XNP_UTILS.NOTIFY_ERROR
6971 (P_PKG_NAME => 'XNP_WF_STANDARD'
6972 ,P_PROC_NAME => 'SMS_UPDATE_PROV_DONE_DATE'
6973 ,P_MSG_NAME => 'SMS_UPDATE_PROV_DONE_DATE_ERR'
6974 ,P_WORKITEM_INSTANCE_ID => l_WORKITEM_INSTANCE_ID
6975 ,P_TOK1 => 'ERROR_TEXT'
6976 ,P_VAL1 => x_progress
6977 );
6978
6979 RAISE;
6980
6981 END SMS_UPDATE_PROV_DONE_DATE;
6982 --
6983 --Runtime Validation for NP Workitem
6984 --
6985 PROCEDURE RUNTIME_VALIDATION
6986 (ITEMTYPE IN VARCHAR2
6987 ,ITEMKEY IN VARCHAR2
6988 ,ACTID IN NUMBER
6989 ,FUNCMODE IN VARCHAR2
6990 ,RESULTOUT OUT NOCOPY VARCHAR2
6991 )
6992 IS
6993 l_WORKITEM_INSTANCE_ID NUMBER;
6994 l_ORDER_ID NUMBER;
6995 l_LINE_ITEM_ID NUMBER;
6996 l_error_code NUMBER := 0;
6997 l_error_message VARCHAR2(2000);
6998 x_progress VARCHAR2(2000):= NULL;
6999 l_ErrCode NUMBER:=0;
7000 l_ErrStr VARCHAR2(2000):=NULL;
7001 e_Add_Item_Attr EXCEPTION;
7002
7003 BEGIN
7004
7005 --
7006 -- Call SET_SDP_CTX to set values for WI_ID and ORDER_ID
7007 --
7008 SET_SDP_CONTEXT
7009 (ITEMTYPE
7010 ,ITEMKEY
7011 ,ACTID
7012 ,'SET_ORD_WI'
7013 ,RESULTOUT
7014 );
7015
7016 --
7017 --
7018 -- RUN mode - normal process execution
7019 --
7020 IF (funcmode = 'RUN') THEN
7021
7022 -- The run code
7023
7024 l_WORKITEM_INSTANCE_ID := g_WORKITEM_INSTANCE_ID;
7025 l_ORDER_ID := g_ORDER_ID;
7026
7027 l_LINE_ITEM_ID:=
7028 wf_engine.GetItemAttrNumber (itemtype => itemtype,
7029 itemkey => itemkey,
7030 aname => 'LINE_ITEM_ID');
7031
7032 XNP_STANDARD.RUNTIME_VALIDATION
7033 (p_ORDER_ID => l_ORDER_ID
7034 ,p_LINE_ITEM_ID => l_lIne_Item_ID
7035 ,p_WORKITEM_INSTANCE_ID =>l_WORKITEM_INSTANCE_ID
7036 ,x_ERROR_CODE => l_error_code
7037 ,x_ERROR_MESSAGE => l_error_message
7038 );
7039
7040 IF l_error_code <> 0
7041 THEN
7042
7043 resultout:= 'FAILURE';
7044 XDPCORE.CheckNAddItemAttrNumber(itemtype=> itemtype,
7045 itemkey => itemkey,
7046 AttrName => 'RVU_ERROR_CODE',
7047 Attrvalue =>l_error_code,
7048 ErrCode =>l_ErrCode,
7049 ErrStr =>l_ErrStr);
7050
7051 IF l_ErrCode<>0 THEN
7052 raise e_Add_Item_Attr;
7053 END IF;
7054
7055 XDPCORE.CheckNAddItemAttrText( itemtype=> itemtype,
7056 itemkey => itemkey,
7057 AttrName => 'RVU_ERROR_MESSAGE',
7058 Attrvalue =>l_error_message,
7059 ErrCode =>l_ErrCode,
7060 ErrStr =>l_ErrStr);
7061
7062 IF l_ErrCode<>0 THEN
7063 raise e_Add_Item_Attr;
7064 END IF;
7065
7066 wf_core.context('XNP_WF_STANDARD', 'RUNTIME_VALIDATION', itemtype,
7067 itemkey, null, l_error_message);
7068
7069 ELSE
7070 -- Completion
7071 resultout := 'SUCCESS';
7072 END IF;
7073
7074 END IF;
7075
7076 EXCEPTION
7077 WHEN OTHERS THEN
7078
7079 ------------------------------------------------------------------
7080 -- Record this function call in the error
7081 -- system in case of an exception
7082 ------------------------------------------------------------------
7083 IF (l_error_code <> 0) THEN
7084 x_progress := to_char(SQLCODE)||
7085 to_char(l_error_code)||':'||l_error_message;
7086 ELSE
7087 x_progress := to_char(SQLCODE)||':'||SQLERRM;
7088 END IF;
7089
7090
7091
7092 IF l_ErrCode <>0 THEN
7093 x_progress:=to_char(l_ErrCode)||':'||l_ErrStr||
7094 to_char(l_error_code)||':'||l_error_message;
7095 ELSE
7096 x_progress := to_char(SQLCODE)||':'||SQLERRM;
7097 END IF;
7098
7099
7100 wf_core.context('XNP_WF_STANDARD', 'RUNTIME_VALIDATION', itemtype,
7101 itemkey,null, x_progress);
7102 raise;
7103
7104 END RUNTIME_VALIDATION;
7105
7106
7107 --------------------------------------------------------------------
7108 -- Description:
7109 -- Calls when neet to sync item parameter values with their corresponding work items.
7110 -- Gets the Item Attributes LINE_ITEM_ID
7111 -- Calls XDP_ENGINE.XDP_SYNC_LINE_ITEM_PV
7112 --
7113 ------------------------------------------------------------------
7114
7115 Procedure SYNC_LI_PARAMETER_VALUES (itemtype in varchar2,
7116 itemkey in varchar2,
7117 actid in number,
7118 funcmode in varchar2,
7119 resultout out NOCOPY varchar2 ) IS
7120 l_line_item_id Number;
7121 l_rtn_code number;
7122 l_rtn_status VARCHAR2(2000);
7123 x_progress VARCHAR2(2000);
7124 BEGIN
7125 --
7126 -- RUN mode - normal process execution
7127 --
7128 IF (funcmode = 'RUN') THEN
7129 l_line_item_id := wf_engine.GetItemAttrNumber(itemtype => SYNC_LI_PARAMETER_VALUES.itemtype,
7130 itemkey => SYNC_LI_PARAMETER_VALUES.itemkey,
7131 aname => 'LINE_ITEM_ID');
7132 IF (l_line_item_id IS NOT NULL) THEN
7133 XDP_ENGINE.XDP_SYNC_LINE_ITEM_PV(l_line_item_id,l_rtn_code, l_rtn_status);
7134 END IF;
7135 IF l_rtn_code = 0 THEN
7136 resultout := 'COMPLETE:SUCCESS';
7137 ELSE
7138 resultout := 'COMPLETE:FAILURE';
7139 END IF;
7140 END IF;
7141 -- CANCEL mode - activity 'compensation'
7142 --
7143 -- This is in the event that the activity must be undone
7144 -- for example when a process is reset to an earlier point
7145
7146 -- due to a loop back.
7147 --
7148
7149 IF (funcmode = 'CANCEL') THEN
7150 resultout := 'COMPLETE';
7151 return;
7152 END IF;
7153
7154 IF (funcmode = 'RESPOND') THEN
7155 resultout := 'COMPLETE';
7156 return;
7157 END IF;
7158
7159 IF (funcmode = 'FORWARD') THEN
7160 resultout := 'COMPLETE';
7161 return;
7162 END IF;
7163
7164 IF (funcmode = 'TRANSFER') THEN
7165 return;
7166 END IF;
7167
7168 IF (funcmode = 'TIMEOUT') THEN
7169
7170 resultout := 'COMPLETE';
7171 return;
7172 END IF;
7173
7174 IF (funcmode = 'others') THEN
7175 resultout := ' ';
7176 return;
7177 END IF;
7178
7179
7180 EXCEPTION
7181 WHEN OTHERS THEN
7182
7183 ------------------------------------------------------------------
7184 -- Record this function call in the error
7185 -- system in case of an exception
7186 ------------------------------------------------------------------
7187 x_progress := to_char(SQLCODE)||':'||SQLERRM;
7188
7189 wf_core.context('XNP_WF_STANDARD', 'SYNC_LI_PARAMETER_VALUES', itemtype,
7190 itemkey,null, x_progress);
7191 raise;
7192 END SYNC_LI_PARAMETER_VALUES;
7193
7194 --
7195 -- GET_ORD_FULFILLMENT_STATUS
7196 -- For workflow function to retrieve order fulfillment status
7197 -- should be called from a workflow function.
7198 -- return order fulfillment status in resultout
7199 -- this value is set in fulfillment procedures
7200 -- 12/06/2000
7201 -- Anping Wang
7202 --
7203
7204 Procedure GET_ORD_FULFILLMENT_STATUS (itemtype in varchar2,
7205 itemkey in varchar2,
7206 actid in number,
7207 funcmode in varchar2,
7208 resultout out NOCOPY varchar2 ) IS
7209
7210 l_status VARCHAR2(256);
7211 l_OrderID NUMBER;
7212 l_result VARCHAR2(2000);
7213 l_return_status VARCHAR2(256);
7214 l_code NUMBER;
7215 x_progress VARCHAR2(256);
7216 BEGIN
7217 --
7218 -- RUN mode - normal process execution
7219 --
7220 IF (funcmode = 'RUN') THEN
7221 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => GET_ORD_FULFILLMENT_STATUS.itemtype,
7222 itemkey => GET_ORD_FULFILLMENT_STATUS.itemkey,
7223 aname => 'ORDER_ID');
7224
7225 XDP_INTERFACES.GET_ORD_FULFILLMENT_STATUS(l_OrderID,l_status,l_result,l_code,l_return_status);
7226 resultout := l_status;
7227
7228 IF (l_code = 0) THEN
7229 XDPCORE.CheckNAddItemAttrText(ItemType => ItemType,
7230 ItemKey => ItemKey,
7231 AttrName => 'FULFILLMENT_RESULT',
7232 AttrValue => l_result,
7233 ErrCode => l_code,
7234 ErrStr => l_return_status);
7235 XDPCORE.CheckNAddItemAttrText(ItemType => ItemType,
7236 ItemKey => ItemKey,
7237 AttrName => 'FULFILLMENT_STATUS',
7238 AttrValue => l_status,
7239 ErrCode => l_code,
7240 ErrStr => l_return_status);
7241 END IF;
7242 END IF;
7243 -- CANCEL mode - activity 'compensation'
7244 --
7245 -- This is in the event that the activity must be undone
7246 -- for example when a process is reset to an earlier point
7247
7248 -- due to a loop back.
7249 --
7250 IF (funcmode = 'CANCEL') THEN
7251 resultout := 'COMPLETE';
7252 return;
7253 END IF;
7254
7255 IF (funcmode = 'RESPOND') THEN
7256 resultout := 'COMPLETE';
7257 return;
7258 END IF;
7259
7260 IF (funcmode = 'FORWARD') THEN
7261 resultout := 'COMPLETE';
7262 return;
7263 END IF;
7264
7265 IF (funcmode = 'TRANSFER') THEN
7266 return;
7267 END IF;
7268
7269 IF (funcmode = 'TIMEOUT') THEN
7270
7271 resultout := 'COMPLETE';
7272 return;
7273 END IF;
7274
7275 IF (funcmode = 'others') THEN
7276 resultout := ' ';
7277 return;
7278 END IF;
7279
7280 EXCEPTION
7281 WHEN OTHERS THEN
7282
7283 ------------------------------------------------------------------
7284 -- Record this function call in the error
7285 -- system in case of an exception
7286 ------------------------------------------------------------------
7287 x_progress := to_char(SQLCODE)||':'||SQLERRM;
7288
7289 wf_core.context('XNP_WF_STANDARD', 'GET_ORD_FULFILLMENT_STATUS', itemtype,
7290 itemkey,null, x_progress);
7291 raise;
7292 END GET_ORD_FULFILLMENT_STATUS;
7293
7294 --
7295 -- SET_ORD_FULFILLMENT_STATUS
7296 -- For workflow function to set order fulfillment status
7297 -- should be called from a workflow function.
7298 -- return complete in resultout
7299 -- 12/06/2000
7300 -- Anping Wang
7301 --
7302
7303 Procedure SET_ORD_FULFILLMENT_STATUS (itemtype in varchar2,
7304 itemkey in varchar2,
7305 actid in number,
7306 funcmode in varchar2,
7307 resultout out NOCOPY varchar2 ) IS
7308
7309 l_status VARCHAR2(256);
7310 l_result VARCHAR2(2000);
7311 l_code NUMBER;
7312 l_OrderID NUMBER;
7313 l_return_status VARCHAR2(256);
7314 x_progress VARCHAR2(256);
7315 BEGIN
7316 --
7317 -- RUN mode - normal process execution
7318 --
7319 IF (funcmode = 'RUN') THEN
7320 l_OrderID := wf_engine.GetItemAttrNumber(itemtype => SET_ORD_FULFILLMENT_STATUS.itemtype,
7321 itemkey => SET_ORD_FULFILLMENT_STATUS.itemkey,
7322 aname => 'ORDER_ID');
7323 BEGIN
7324 l_status :=
7325 wf_engine.GetActivityAttrText (itemtype => itemtype,
7326 itemkey => itemkey,
7327 actid => actid,
7328 aname => 'FULFILLMENT_STATUS');
7329 l_result :=
7330 wf_engine.GetActivityAttrText (itemtype => itemtype,
7331 itemkey => itemkey,
7332 actid => actid,
7333 aname => 'FULFILLMENT_RESULT');
7334 EXCEPTION
7335 WHEN OTHERS THEN
7336 l_status := nvl(l_status,'Unknown status');
7337 l_result := nvl(l_result,'Unknown results');
7338 END;
7339
7340 XDP_INTERFACES.SET_ORD_FULFILLMENT_STATUS(l_OrderID,l_status,l_result,l_code,l_return_status);
7341 resultout := l_status;
7342 END IF;
7343 -- CANCEL mode - activity 'compensation'
7344 --
7345 -- This is in the event that the activity must be undone
7346 -- for example when a process is reset to an earlier point
7347
7348 -- due to a loop back.
7349 --
7350 IF (funcmode = 'CANCEL') THEN
7351 resultout := 'COMPLETE';
7352 return;
7353 END IF;
7354
7355 IF (funcmode = 'RESPOND') THEN
7356 resultout := 'COMPLETE';
7357 return;
7358 END IF;
7359
7360 IF (funcmode = 'FORWARD') THEN
7361 resultout := 'COMPLETE';
7362 return;
7363 END IF;
7364
7365 IF (funcmode = 'TRANSFER') THEN
7366 return;
7367 END IF;
7368
7369 IF (funcmode = 'TIMEOUT') THEN
7370
7371 resultout := 'COMPLETE';
7372 return;
7373 END IF;
7374
7375 IF (funcmode = 'others') THEN
7376 resultout := ' ';
7377 return;
7378 END IF;
7379
7380 EXCEPTION
7381 WHEN OTHERS THEN
7382
7383 ------------------------------------------------------------------
7384 -- Record this function call in the error
7385 -- system in case of an exception
7386 ------------------------------------------------------------------
7387 x_progress := to_char(SQLCODE)||':'||SQLERRM;
7388
7389 wf_core.context('XNP_WF_STANDARD', 'SET_ORD_FULFILLMENT_STATUS', itemtype,
7390 itemkey,null, x_progress);
7391 raise;
7392 END SET_ORD_FULFILLMENT_STATUS;
7393
7394 --
7395 -- SET_WI_FULFILLMENT_STATUS
7396 -- For workflow function to set order fulfillment status
7397 -- should be called from a workflow function.
7398 -- return complete in resultout
7399 -- 12/06/2000
7400 -- Anping Wang
7401 --
7402
7403 Procedure SET_WI_FULFILLMENT_STATUS (itemtype in varchar2,
7404 itemkey in varchar2,
7405 actid in number,
7406 funcmode in varchar2,
7407 resultout out NOCOPY varchar2 ) IS
7408
7409 l_status VARCHAR2(256);
7410 l_result VARCHAR2(2000);
7411 l_code NUMBER;
7412 l_return_status VARCHAR2(256);
7413 x_progress VARCHAR2(256);
7414 l_wi_instance_id number;
7415 BEGIN
7416 --
7417 -- RUN mode - normal process execution
7418 --
7419 IF (funcmode = 'RUN') THEN
7420 l_wi_instance_id := wf_engine.GetItemAttrNumber(itemtype => SET_WI_FULFILLMENT_STATUS.itemtype,
7421 itemkey => SET_WI_FULFILLMENT_STATUS.itemkey,
7422 aname => 'WORKITEM_INSTANCE_ID');
7423
7424 BEGIN
7425 l_status :=
7426 wf_engine.GetActivityAttrText (itemtype => itemtype,
7427 itemkey => itemkey,
7428 actid => actid,
7429 aname => 'FULFILLMENT_STATUS');
7430 l_result :=
7431 wf_engine.GetActivityAttrText (itemtype => itemtype,
7432 itemkey => itemkey,
7433 actid => actid,
7434 aname => 'FULFILLMENT_RESULT');
7435 EXCEPTION
7436 WHEN OTHERS THEN
7437 l_status := nvl(l_status,'Unknown status');
7438 l_result := nvl(l_result,'Unknown results');
7439 END;
7440
7441 BEGIN
7442 XDP_ENGINE.SET_WORKITEM_PARAM_VALUE(l_wi_instance_id,'FULFILLMENT_STATUS',l_status,NULL);
7443 XDP_ENGINE.SET_WORKITEM_PARAM_VALUE(l_wi_instance_id,'FULFILLMENT_RESULT',l_result,NULL);
7444 resultout := 'COMPLETE:SUCCESS';
7445 EXCEPTION
7446 WHEN OTHERS THEN
7447 resultout := 'COMPLETE:FAILURE';
7448 END;
7449 END IF;
7450 -- CANCEL mode - activity 'compensation'
7451 --
7452 -- This is in the event that the activity must be undone
7453 -- for example when a process is reset to an earlier point
7454
7455 -- due to a loop back.
7456 --
7457 IF (funcmode = 'CANCEL') THEN
7458 resultout := 'COMPLETE';
7459 return;
7460 END IF;
7461
7462 IF (funcmode = 'RESPOND') THEN
7463 resultout := 'COMPLETE';
7464 return;
7465 END IF;
7466
7467 IF (funcmode = 'FORWARD') THEN
7468 resultout := 'COMPLETE';
7469 return;
7470 END IF;
7471
7472 IF (funcmode = 'TRANSFER') THEN
7473 return;
7474 END IF;
7475
7476 IF (funcmode = 'TIMEOUT') THEN
7477
7478 resultout := 'COMPLETE';
7479 return;
7480 END IF;
7481
7482 IF (funcmode = 'others') THEN
7483 resultout := ' ';
7484 return;
7485 END IF;
7486
7487 EXCEPTION
7488 WHEN OTHERS THEN
7489
7490 ------------------------------------------------------------------
7491 -- Record this function call in the error
7492 -- system in case of an exception
7493 ------------------------------------------------------------------
7494 x_progress := to_char(SQLCODE)||':'||SQLERRM;
7495
7496
7497 wf_core.context('XNP_WF_STANDARD', 'SET_WI_FULFILLMENT_STATUS', itemtype,
7498 itemkey,null, x_progress);
7499 raise;
7500 END SET_WI_FULFILLMENT_STATUS;
7501
7502 -- Bug Fix 1790288
7503 -- When the order finishes all the waiting timers and events for the order
7504 -- must be expired/removed
7505 -- Raja 05/31/2001
7506 Procedure DEREGISTER_ALL (itemtype in varchar2,
7507 itemkey in varchar2,
7508 actid in number,
7509 funcmode in varchar2,
7510 resultout out NOCOPY varchar2 )
7511
7512 IS
7513 x_progress VARCHAR2(2000);
7514 l_order_id NUMBER;
7515 l_error_code NUMBER := 0;
7516 l_error_message VARCHAR2(2000);
7517 e_DEREGISTER EXCEPTION;
7518
7519 BEGIN
7520
7521 --
7522 -- RUN mode - normal process execution
7523 --
7524
7525 IF (funcmode = 'RUN') THEN
7526
7527 l_order_id :=
7528 wf_engine.GetItemAttrNumber
7529 (itemtype => itemtype
7530 ,itemkey => itemkey
7531 ,aname => 'ORDER_ID'
7532 );
7533
7534 -- The run code
7535
7536 -- Get the event type to publish
7537
7538 XNP_STANDARD.DEREGISTER_ALL
7539 (
7540 p_order_id => l_order_id
7541 ,x_error_code => l_error_code
7542 ,x_error_message => l_error_message
7543 );
7544
7545 IF l_error_code <> 0
7546 THEN
7547 raise e_DEREGISTER;
7548 END IF;
7549
7550 -- Completion
7551 resultout := 'COMPLETE';
7552 return;
7553
7554 END IF;
7555 --
7556 -- CANCEL mode
7557 --
7558 -- This is in the event that the activity must be undone.
7559 --
7560 IF (funcmode = 'CANCEL' ) THEN
7561 -- The cancel code
7562
7563 null;
7564 -- no result needed
7565 resultout := 'COMPLETE';
7566 return;
7567 END IF;
7568
7569 -- For other execution modes: return null
7570
7571 resultout := '';
7572 return;
7573
7574 EXCEPTION
7575 WHEN OTHERS THEN
7576 /* Record this function call in the error
7577 * system in case of an exception
7578 */
7579 fnd_message.set_name('XNP','STD_ERROR');
7580 fnd_message.set_token(
7581 'ERROR_LOCN','XNP_WF_TIMERS.DEREGISTER');
7582 if(l_error_code <> 0) then
7583 fnd_message.set_token('ERROR_TEXT',
7584 ':'||to_char(l_error_code)||':'||l_error_message);
7585 else
7586 fnd_message.set_token('ERROR_TEXT',
7587 ':'||to_char(SQLCODE)||':'||SQLERRM);
7588 end if;
7589 x_progress := fnd_message.get;
7590 wf_core.context(
7591 'XNP_WF_STANDARD'
7592 , 'DEREGISTERALL'
7593 , itemtype
7594 , itemkey
7595 , to_char(actid)
7596 , funcmode
7597 , x_progress);
7598
7599 RAISE;
7600
7601 END DEREGISTER_ALL;
7602
7603
7604 Procedure downloadWIParams(itemtype in varchar2, itemkey in varchar2) IS
7605
7606 CURSOR c_get_params (cv_wi_instance_id NUMBER)IS
7607 SELECT parameter_name
7608 FROM xdp_worklist_details
7609 WHERE workitem_instance_id = cv_wi_instance_id
7610 FOR UPDATE;
7611
7612
7613 l_param_name VARCHAR2(40);
7614 l_param_val VARCHAR2(4000);
7615 l_atype VARCHAR2(400);
7616 l_sub_type VARCHAR2(400);
7617 l_format VARCHAR2(400);
7618 l_WIInstanceID number;
7619
7620 BEGIN
7621
7622 l_WIInstanceID := wf_engine.GetItemAttrNumber(itemtype => downloadWIParams.itemtype,
7623 itemkey => downloadWIParams.itemkey,
7624 aname => 'WORKITEM_INSTANCE_ID');
7625
7626 FOR lv_rec in c_get_params( l_WIInstanceID ) LOOP
7627 l_param_name := lv_rec.parameter_name;
7628
7629 BEGIN
7630 l_param_val := wf_engine.GetItemAttrText(itemtype => downloadWIParams.itemtype,
7631 itemkey => downloadWIParams.itemkey,
7632 aname => l_param_name);
7633 UPDATE xdp_worklist_details
7634 SET parameter_value = l_param_val,
7635 LAST_UPDATE_DATE = sysdate,
7636 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
7637 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
7638 WHERE current of c_get_params;
7639 EXCEPTION
7640 WHEN others THEN
7641 -- skilaru 05/20/2002
7642 -- User defined workflow didnt have this item attribute defined..
7643 NULL;
7644 END;
7645 END LOOP;
7646
7647
7648 EXCEPTION
7649 WHEN others THEN
7650 wf_core.context('XNP_WF_STANDARD', 'downloadWIParams', null, null, null, l_WIInstanceID );
7651 RAISE;
7652
7653 END downloadWIParams;
7654
7655 Procedure downloadFAParams( itemtype IN VARCHAR2,
7656 itemkey IN VARCHAR2,
7657 actid IN NUMBER,
7658 p_FAInstanceID IN NUMBER ) IS
7659
7660 l_param_name VARCHAR2(40);
7661 l_item_attrib_name VARCHAR2(40);
7662 l_param_value VARCHAR2(4000);
7663
7664 CURSOR c_get_fa_params( cv_fa_instance_id NUMBER ) IS
7665 SELECT parameter_name
7666 FROM xdp_fa_details
7667 WHERE fa_instance_id = cv_fa_instance_id
7668 FOR UPDATE;
7669
7670 BEGIN
7671
7672 FOR lv_rec in c_get_fa_params( p_FAInstanceID ) LOOP
7673 l_param_name := lv_rec.parameter_name;
7674 BEGIN
7675 --skilaru 05/22/2002
7676 --Assumption is users will only use Item Attributes to set the Activity Attributes..
7677 --If we allow users to use type CONSTANT for Activity Attribute then we should
7678 --resolve the type(whether CONSTANT or Item Attribute) first before getting the value..
7679
7680 --get Item attribute name..
7681 l_item_attrib_name := wf_engine.GetActivityAttrText(itemtype => downloadFAParams.itemtype,
7682 itemkey => downloadFAParams.itemkey,
7683 actid => downloadFAParams.actid,
7684 aname => l_param_name);
7685 --get Item attribute value..
7686 l_param_value := wf_engine.GetItemAttrText(itemtype => downloadFAParams.itemtype,
7687 itemkey => downloadFAParams.itemkey,
7688 aname => l_item_attrib_name);
7689 UPDATE xdp_fa_details
7690 SET parameter_value = l_param_value,
7691 LAST_UPDATE_DATE = sysdate,
7692 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
7693 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
7694 WHERE current of c_get_fa_params;
7695
7696 EXCEPTION
7697 WHEN others THEN
7698 -- skilaru 05/20/2002
7699 -- User havent used this activity attribute..
7700 NULL;
7701 END;
7702 END LOOP;
7703
7704 EXCEPTION
7705 WHEN others THEN
7706 wf_core.context('XNP_WF_STANDARD', 'downloadFAParams', null, null, null, p_FAInstanceID );
7707 RAISE;
7708 END downloadFAParams;
7709
7710
7711 Procedure uploadFAParams( itemtype IN VARCHAR2,
7712 itemkey IN VARCHAR2,
7713 actid IN NUMBER,
7714 p_FAInstanceID IN NUMBER ) IS
7715
7716 l_param_name VARCHAR2(40);
7717 l_item_attrib_name VARCHAR2(40);
7718 l_param_value VARCHAR2(4000);
7719
7720 CURSOR c_get_fa_params( cv_fa_instance_id NUMBER ) IS
7721 SELECT parameter_name, parameter_value
7722 FROM xdp_fa_details
7723 WHERE fa_instance_id = cv_fa_instance_id;
7724
7725 BEGIN
7726
7727 FOR lv_rec in c_get_fa_params( p_FAInstanceID ) LOOP
7728 l_param_name := lv_rec.parameter_name;
7729 BEGIN
7730 --skilaru 05/22/2002
7731 --Assumption is users will only use Item Attributes to set the Activity Attributes..
7732 --If we allow users to use type CONSTANT for Activity Attribute then we should
7733 --resolve the type(whether CONSTANT or Item Attribute) first before getting the value..
7734
7735 --get Item attribute name..
7736 l_item_attrib_name := wf_engine.GetActivityAttrText(itemtype => uploadFAParams.itemtype,
7737 itemkey => uploadFAParams.itemkey,
7738 actid => uploadFAParams.actid,
7739 aname => l_param_name);
7740 l_param_value := lv_rec.parameter_value;
7741
7742 --set Item attribute value..
7743 wf_engine.setItemAttrText(itemtype => uploadFAParams.itemtype,
7744 itemkey => uploadFAParams.itemkey,
7745 aname => l_item_attrib_name,
7746 avalue => l_param_value );
7747 EXCEPTION
7748 WHEN others THEN
7749 -- skilaru 05/20/2002
7750 -- User havent used this activity attribute..
7751 NULL;
7752 END;
7753 END LOOP;
7754
7755 EXCEPTION
7756 WHEN others THEN
7757 wf_core.context('XNP_WF_STANDARD', 'uploadFAParams', null, null, null, p_FAInstanceID );
7758 RAISE;
7759 END uploadFAParams;
7760
7761
7762 END XNP_WF_STANDARD;
7763