[Home] [Help]
PACKAGE BODY: APPS.XDPCORE_OM
Source
1 PACKAGE BODY XDPCORE_OM AS
2 /* $Header: XDPCORMB.pls 120.4 2006/07/05 05:31:04 dputhiye ship $ */
3
4
5 /**** Global Variables ****/
6
7 g_order_header XDP_TYPES.SERVICE_ORDER_HEADER ;
8 g_order_line_list XDP_TYPES.SERVICE_ORDER_LINE_LIST ;
9
10 /**** All Private Procedures for the Package ****/
11
12 PROCEDURE CreateFulfillmentOrder(itemtype IN VARCHAR2,
13 itemkey IN VARCHAR2,
14 actid IN NUMBER,
15 resultout OUT NOCOPY VARCHAR2) ;
16
17 PROCEDURE GetHeaderDetails(p_header_id IN NUMBER ,
18 x_return_code OUT NOCOPY NUMBER ,
19 x_error_description OUT NOCOPY VARCHAR2 );
20
21 PROCEDURE GetLineDetails(p_header_id IN NUMBER ,
22 x_return_code OUT NOCOPY NUMBER ,
23 x_error_description OUT NOCOPY VARCHAR2 );
24
25 PROCEDURE WaitForFulfillment(itemtype IN VARCHAR2,
26 itemkey IN VARCHAR2,
27 actid IN NUMBER ,
28 resultout OUT NOCOPY VARCHAR2);
29
30 PROCEDURE PublishXDPFulfillDone(itemtype IN VARCHAR2,
31 itemkey IN VARCHAR2) ;
32
33 PROCEDURE IsFulfillmentCompleted(itemtype IN VARCHAR2,
34 itemkey IN VARCHAR2,
35 actid IN NUMBER,
36 resultout OUT NOCOPY VARCHAR2);
37
38 PROCEDURE ProvisionLine(itemtype IN VARCHAR2,
39 itemkey IN VARCHAR2,
40 actid IN NUMBER,
41 resultout OUT NOCOPY VARCHAR2);
42
43 PROCEDURE UpdateTxnDetails
44 (itemtype IN VARCHAR2,
45 itemkey IN VARCHAR2,
46 actid IN NUMBER,
47 resultout OUT NOCOPY VARCHAR2) ;
48
49 PROCEDURE PublishXDPFulfillStart(itemtype IN VARCHAR2,
50 itemkey IN VARCHAR2) ;
51
52
53 -- **************** CREATE_FULFILLMENT_ORDER *********************
54
55
56 PROCEDURE CREATE_FULFILLMENT_ORDER
57 (itemtype IN VARCHAR2,
58 itemkey IN VARCHAR2,
59 actid IN NUMBER,
60 funcmode IN VARCHAR2,
61 resultout OUT NOCOPY VARCHAR2) IS
62
63 x_progress VARCHAR2(4000);
64 l_resultout VARCHAR2(240);
65
66 BEGIN
67
68 -- RUN mode - normal process execution
69 --
70 IF (funcmode = 'RUN') THEN
71 CreateFulfillmentOrder(itemtype, itemkey, actid, l_resultout);
72 resultout := l_resultout ;
73 return;
74 ELSE
75 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
76 return;
77 END IF;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 wf_core.context('XDPCORE_OM', 'CREATE_FULFILLMENT_ORDER', itemtype, itemkey, to_char(actid), funcmode);
82 raise;
83
84 END CREATE_FULFILLMENT_ORDER ;
85
86 -- **************** WAIT_FOR_FULFILLMENT *********************
87
88 PROCEDURE WAIT_FOR_FULFILLMENT
89 (itemtype IN VARCHAR2,
90 itemkey IN VARCHAR2,
91 actid IN NUMBER,
92 funcmode IN VARCHAR2,
93 resultout OUT NOCOPY VARCHAR2) IS
94
95 x_progress VARCHAR2(4000);
96 l_resultout VARCHAR2(240);
97
98 BEGIN
99
100 -- RUN mode - normal process execution
101 --
102 IF (funcmode = 'RUN') THEN
103 WaitForFulfillment(itemtype, itemkey, actid, l_resultout);
104 resultout := l_resultout ;
105 return;
106 ELSE
107 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
108 return;
109 END IF;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 wf_core.context('XDPCORE_OM', 'WAIT_FOR_FULFILLMENT', itemtype, itemkey, to_char(actid), funcmode);
114 raise;
115
116 END WAIT_FOR_FULFILLMENT ;
117
118
119 -- **************** IS_PROVISIONING_REQD *********************
120
121 PROCEDURE IS_FULFILLMENT_COMPLETED
122 (itemtype IN VARCHAR2,
123 itemkey IN VARCHAR2,
124 actid IN NUMBER,
125 funcmode IN VARCHAR2,
126 resultout OUT NOCOPY VARCHAR2) IS
127
128 x_progress VARCHAR2(4000);
129 l_resultout VARCHAR2(240);
130
131 BEGIN
132
133
134 -- RUN mode - normal process execution
135 --
136 IF (funcmode = 'RUN') THEN
137 IsFulfillmentCompleted(itemtype, itemkey, actid , l_resultout);
138 resultout := 'COMPLETE' ;
139 return;
140 ELSE
141 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
142 return;
143 END IF;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 wf_core.context('XDPCORE_OM', 'IS_PROVISIONING_REQD', itemtype, itemkey, to_char(actid), funcmode);
148 raise;
149
150 END IS_FULFILLMENT_COMPLETED ;
151
152 -- **************** PROVISION_LINE *********************
153
154
155 PROCEDURE PROVISION_LINE
156 (itemtype IN VARCHAR2,
157 itemkey IN VARCHAR2,
158 actid IN NUMBER,
159 funcmode IN VARCHAR2,
160 resultout OUT NOCOPY VARCHAR2) IS
161
162 x_progress VARCHAR2(4000);
163 l_resultout VARCHAR2(240);
164
165
166 BEGIN
167
168 IF (funcmode = 'RUN') THEN
169 ProvisionLine(itemtype, itemkey,actid,resultout);
170 resultout := resultout ;
171 return;
172 ELSE
173 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
174 return;
175 END IF;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 wf_core.context('XDPCORE_OM', 'PROVISION_LINE', itemtype, itemkey, to_char(actid), funcmode);
180 raise;
181
182 END PROVISION_LINE ;
183
184
185 -- **************** LINE_FULFILLMENT_DONE *********************
186
187 PROCEDURE LINE_FULFILLMENT_DONE
188 (itemtype IN VARCHAR2,
189 itemkey IN VARCHAR2,
190 actid IN NUMBER,
191 funcmode IN VARCHAR2,
192 resultout OUT NOCOPY VARCHAR2) IS
193
194 x_progress VARCHAR2(4000);
195 l_resultout VARCHAR2(240);
196
197
198 BEGIN
199
200 IF (funcmode = 'RUN') THEN
201 PublishXDPFulfillDone(itemtype, itemkey);
202 resultout := 'COMPLETE:ACTIVITY_PERFORMED';
203 return;
204 ELSE
205 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
206 return;
207 END IF;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 wf_core.context('XDPCORE_OM', 'LINE_FULFILLMENT_DONE', itemtype, itemkey, to_char(actid), funcmode);
212 raise;
213
214 END LINE_FULFILLMENT_DONE ;
215
216
217 -- **************** UPDATE_TXN_DETAILS *********************
218
219 PROCEDURE UPDATE_TXN_DETAILS
220 (itemtype IN VARCHAR2,
221 itemkey IN VARCHAR2,
222 actid IN NUMBER,
223 funcmode IN VARCHAR2,
224 resultout OUT NOCOPY VARCHAR2) IS
225
226 x_progress VARCHAR2(4000);
227 l_resultout VARCHAR2(240);
228
229
230 BEGIN
231
232 IF (funcmode = 'RUN') THEN
233
234 UpdateTxnDetails
235 (itemtype => update_txn_details.itemtype,
236 itemkey => update_txn_details.itemkey,
237 actid => update_txn_details.actid ,
238 resultout => l_resultout );
239 resultout := l_resultout ; -- 'COMPLETE' ;
240 return;
241 ELSE
242 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
243 return;
244 END IF;
245
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 wf_core.context('XDPCORE_OM', 'UPDATE_TXN_DETAILS', itemtype, itemkey, to_char(actid), funcmode);
250 raise;
251
252 END UPDATE_TXN_DETAILS;
253
254
255 -- **************** UPDATE_OM_LINE_STATUS *********************
256
257 PROCEDURE UPDATE_OM_LINE_STATUS
258 (itemtype IN VARCHAR2,
259 itemkey IN VARCHAR2,
260 actid IN NUMBER,
261 funcmode IN VARCHAR2,
262 resultout OUT NOCOPY VARCHAR2) IS
263
264 x_progress VARCHAR2(4000);
265 l_resultout VARCHAR2(240);
266 l_line_id NUMBER := to_number(itemkey);
267 e_exception EXCEPTION;
268 l_org_id NUMBER;
269
270 BEGIN
271
272 IF (funcmode = 'RUN') THEN
273
274 -- Update OM Line Flow Status to 'Provisioning Succeeded'
275
276
277 l_org_id := wf_engine.GetItemAttrNumber( itemtype => update_om_line_status.itemtype,
278 itemkey => update_om_line_status.itemkey,
279 aname => 'ORG_ID');
280
281 -- Date: 15 FEB 2006. Author: DPUTHIYE BUG#: 5023342
282 -- Change description: Replaced the call to set_client_info with MO_GLOBAL.set_policy_context(..)
283 -- Other files impacted: None.
284
285 --dbms_application_info.set_client_info(l_org_id);
286 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
287
288 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
289 (p_line_id => l_line_id,
290 p_flow_status_code => 'PROV_SUCCESS',
291 x_return_status => l_resultout );
292
293 IF l_resultout = 'S' THEN
294 resultout := 'COMPLETE';
295 return;
296 ELSE
297 x_progress := 'Error While OM Order Line Status for Line : (Line ID = '||l_line_id||' - ' ||sqlerrm||' )' ;
298 RAISE e_exception ;
299 END IF ;
300
301
302 ELSE
303 resultout := xdp_om_util.HandleOtherWFFuncmode(funcmode);
304 return;
305 END IF;
306
307
308
309 EXCEPTION
310 WHEN e_exception THEN
311 wf_core.context('XDPCORE_OM', 'UPDATE_OM_LINE_STATUS', itemtype, itemkey, to_char(actid), x_progress);
312 raise;
313 WHEN OTHERS THEN
314 x_progress := SQLCODE||' - ' ||SQLERRM ;
315 wf_core.context('XDPCORE_OM', 'UPDATE_OM_LINE_STATUS', itemtype, itemkey, to_char(actid), x_progress);
316 raise;
317
318 END UPDATE_OM_LINE_STATUS ;
319
320
321 -- **************** START_FULFILLMENT_PROCESS *********************
322
323
324 PROCEDURE START_FULFILLMENT_PROCESS
325 (p_MESSAGE_ID IN NUMBER ,
326 p_PROCESS_REFERENCE IN VARCHAR2 ,
327 x_ERROR_CODE OUT NOCOPY NUMBER ,
328 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2) IS
329
330 l_msg_text VARCHAR2(32767) ;
331 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
332 l_sdp_result_code VARCHAR2(20) := NULL;
333 l_item_type VARCHAR2(256) := 'XDPOMINT';
334 l_item_key VARCHAR2(256) ;
335 l_notification_role VARCHAR2(256) ;
336 l_line_id VARCHAR2(40) ;
337 x_progress VARCHAR2(4000);
338 l_org_id NUMBER ;
339 l_order_number NUMBER ;
340
341 BEGIN
342
343 savepoint start_fulfillment ;
344
345 x_error_code := 0 ;
346 x_error_message := NULL ;
347
348 XNP_MESSAGE.GET(p_msg_id => p_message_id,
349 x_msg_header => l_msg_header,
350 x_msg_text => l_msg_text);
351
352 XNP_XML_UTILS.DECODE (l_msg_text,'LINE_ID',l_line_id);
353
354 l_item_key := l_line_id ;
355
356 SELECT NVL(l.org_id,h.org_id), h.order_number
357 INTO l_org_id,l_order_number
358 FROM oe_order_lines_all l,
359 oe_order_headers_all h
360 WHERE l.line_id = to_number(l_line_id)
361 AND l.header_id = h.header_id ;
362
363
364 WF_ENGINE.CREATEPROCESS(itemtype => l_item_type,
365 itemkey => l_item_key ,
366 process => 'XDP_OM_INTERFACE');
367
368 WF_ENGINE.SetItemAttrNumber(itemtype => l_item_type ,
369 itemkey => l_item_key ,
370 aname => 'LINE_ID',
371 avalue => l_item_key );
372
373 WF_ENGINE.SetItemAttrNumber(itemtype => l_item_type ,
374 itemkey => l_item_key ,
375 aname => 'ORG_ID',
376 avalue => l_org_id );
377
378 WF_ENGINE.SetItemAttrNumber(itemtype => l_item_type ,
379 itemkey => l_item_key ,
380 aname => 'ORDER_NUMBER',
381 avalue => l_order_number );
382
383 WF_ENGINE.StartProcess(l_item_type, l_item_key);
384
385 EXCEPTION
386 WHEN others THEN
387 x_progress := SQLCODE||' - ' ||SQLERRM ;
388 wf_core.context('XDPCORE_OM','START_FULFILLMENT_PROCESS',l_item_type, l_item_key,null,x_progress);
389 rollback to start_fulfillment ;
390 RAISE;
391 END START_FULFILLMENT_PROCESS ;
392
393
394 -- **************** ALL PRIVATE PROCEDURES *********************
395
396 -- **************** CreateFulfillmentOrder *********************
397
398 PROCEDURE CreateFulfillmentOrder(itemtype IN VARCHAR2,
399 itemkey IN VARCHAR2,
400 actid IN NUMBER,
401 resultout OUT NOCOPY VARCHAR2) IS
402
403 x_Progress VARCHAR2(4000);
404 l_resultout VARCHAR2(4000) ;
405 l_header_id NUMBER ;
406 l_return_code NUMBER ;
407 l_error_description VARCHAR2(4000);
408 l_sfm_order_id NUMBER;
409 l_order_param_list XDP_TYPES.SERVICE_ORDER_PARAM_LIST ;
410 l_line_param_list XDP_TYPES.SERVICE_LINE_PARAM_LIST ;
411 l_msg_list VARCHAR2(20);
412 l_msg_count NUMBER;
413 l_return_status VARCHAR2(20) := 'S';
414 e_exception EXCEPTION;
415
416 -- l_plsql_stmt VARCHAR2(32000) := null;
417
418 CURSOR c_lines(p_header_id NUMBER) IS
419 SELECT line_id ,
420 flow_status_code
421 FROM oe_order_lines_all l
422 WHERE l.header_id = p_header_id ;
423
424 BEGIN
425
426 --- Get header information and create fulfillment order header --
427
428 g_order_header := null ;
429 g_order_line_list.DELETE ;
430
431 l_return_code := 0 ;
432 l_error_description := null ;
433
434 l_header_id := to_number (itemkey);
435
436 GetHeaderDetails(p_header_id => l_header_id ,
437 x_return_code => l_return_code ,
438 x_error_description => l_error_description );
439
440 IF l_return_code <> 0 THEN
441 FND_MESSAGE.SET_NAME('XDP','XDP_UNABLE_TO_CREATE_SFM_ORDER');
442 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_return_code);
443 FND_MESSAGE.SET_TOKEN('ERROR_DESC',l_error_description);
444 OE_STANDARD_WF.Set_Msg_Context(actid);
445 OE_MSG_PUB.Add;
446
447 resultout := 'INCOMPLETE' ;
448 return;
449 END IF ;
450
451 GetLineDetails(p_header_id => l_header_id ,
452 x_return_code => l_return_code ,
453 x_error_description => l_error_description );
454
455 IF l_return_code <> 0 THEN
456 FND_MESSAGE.SET_NAME('XDP','XDP_UNABLE_TO_CREATE_SFM_ORDER');
457 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_return_code);
458 FND_MESSAGE.SET_TOKEN('ERROR_DESC',l_error_description);
459 OE_STANDARD_WF.Set_Msg_Context(actid);
460 OE_MSG_PUB.Add;
461
462 resultout := 'INCOMPLETE' ;
463 return;
464 END IF ;
465
466 IF g_order_line_list.COUNT > 0 THEN
467
468 XDP_INTERFACES_PUB.PROCESS_ORDER (P_API_VERSION => 11,
469 P_INIT_MSG_LIST => l_msg_list,
470 P_COMMIT => FND_API.G_FALSE,
471 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
472 P_ORDER_HEADER => g_order_header,
473 P_ORDER_PARAM_LIST => l_order_param_list,
474 P_ORDER_LINE_LIST => g_order_line_list,
475 P_LINE_PARAM_LIST => l_line_param_list,
476 X_RETURN_STATUS => l_return_status,
477 X_MSG_COUNT => l_msg_count,
478 X_MSG_DATA => l_error_description,
479 X_ERROR_CODE => l_return_code,
480 X_ORDER_ID => l_sfm_order_id);
481 IF l_return_status = 'S' THEN
482 -- FND_MESSAGE.SET_NAME('XDP','XDP_CREATED_SFM_ORDER');
483 -- FND_MESSAGE.SET_TOKEN('ORDER_NUMBER',g_order_header.order_number||'('||g_order_header.order_version||')');
484 -- FND_MESSAGE.SET_TOKEN('ORDER_ID',l_sfm_order_id );
485 -- OE_STANDARD_WF.Set_Msg_Context(actid);
486 -- OE_MSG_PUB.Add;
487
488 -- Update line status to 'PROV_REQUEST' if the line status is 'PROV_FAILED'
489
490 FOR c_lines_rec IN c_lines(l_header_id )
491 LOOP
492 IF C_lines_rec.flow_status_code = 'PROV_FAILED' THEN
493
494 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
495 (p_line_id => C_lines_rec.line_id,
496 p_flow_status_code => 'PROV_REQUEST',
497 x_return_status => l_resultout );
498
499 END IF ;
500
501 IF l_resultout <> 'S' THEN
502 x_progress := 'Error While updating OM Order Line Flow status ';
503 RAISE e_exception ;
504 END IF ;
505
506 END LOOP ;
507
508 resultout := 'COMPLETE' ;
509 ELSE
510 -- Update line status to 'PROV_REQUEST' if the line status is 'PROV_FAILED'
511
512 FOR c_lines_rec IN c_lines(l_header_id )
513 LOOP
514 IF C_lines_rec.flow_status_code = 'PROV_REQUEST' THEN
515
516 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
517 (p_line_id => C_lines_rec.line_id,
518 p_flow_status_code => 'PROV_FAILED',
519 x_return_status => l_resultout );
520
521 END IF;
522
523 IF l_resultout <> 'S' THEN
524 x_progress := 'Error While updating OM Order Line Flow status ';
525 RAISE e_exception ;
526 END IF ;
527
528 END LOOP ;
529
530 -- Set massage stack
531
532 FND_MESSAGE.SET_NAME('XDP','XDP_UNABLE_TO_CREATE_SFM_ORDER');
533 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_return_code);
534 FND_MESSAGE.SET_TOKEN('ERROR_DESC',l_error_description);
535 OE_STANDARD_WF.Set_Msg_Context(actid);
536 OE_MSG_PUB.Add;
537
538 resultout := 'INCOMPLETE' ;
539 return;
540 END IF ;
541
542 -- Date:30 JUN 05 BUG#:4410080 (FP Fix for: 4383953)
543 -- Change: Added the following else clause.
544 -- Other files impacted: None.
545 ELSE --g_order_line_list.COUNT = 0
546 --Return complete, even if there are no provisionable items in order.
547 resultout := 'COMPLETE' ;
548 END IF ;
549
550 EXCEPTION
551 WHEN e_exception THEN
552 wf_core.context('XDPCORE_OM', 'CreateFulfillmentOrder',itemtype,itemkey,actid,x_progress);
553 raise ;
554 WHEN OTHERS THEN
555 wf_core.context('XDPCORE_OM', 'CreateFulfillmentOrder', itemtype, itemkey,null,null);
556 raise;
557 END CreateFulfillmentOrder ;
558
559 -- **************** GetHeaderDetails *********************
560
561 PROCEDURE GetHeaderDetails(p_header_id IN NUMBER ,
562 x_return_code OUT NOCOPY NUMBER ,
563 x_error_description OUT NOCOPY VARCHAR2 ) IS
564
565 CURSOR c_header IS
566 SELECT order_number ,
567 version_number ,
568 request_date ,
569 expiration_date ,
570 header_id ,
571 sold_to_contact_id,
572 sold_to_org_id ,
573 org_id
574 FROM oe_order_headers_all
575 WHERE header_id = p_header_id ;
576 BEGIN
577 x_return_code := 0 ;
578 x_error_description := null;
579
580 FOR c_header_rec IN c_header
581 LOOP
582 g_order_header.required_fulfillment_date := NVL(c_header_rec.request_date,sysdate) ;
583 g_order_header.priority := 100 ;
584 g_order_header.jeopardy_enabled_flag := 'N';
585 g_order_header.execution_mode := 'ASYNC' ;
586 g_order_header.due_date := c_header_rec.expiration_date;
587 g_order_header.customer_required_date := c_header_rec.request_date ;
588 g_order_header.order_source := 'OE_ORDER_HEADERS_ALL' ;
589 g_order_header.order_ref_name := 'SALES' ;
590 g_order_header.order_ref_value := c_header_rec.header_id ;
591 g_order_header.order_number := c_header_rec.order_number;
592 g_order_header.order_version := c_header_rec.version_number;
593 g_order_header.cust_account_id := c_header_rec.sold_to_org_id;
594 g_order_header.org_id := c_header_rec.org_id ;
595
596 END LOOP ;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 x_return_code := sqlcode ;
601 x_error_description := sqlerrm ;
602 END GetHeaderDetails ;
603
604
605 -- **************** GetLineDetails *********************
606
607 PROCEDURE GetLineDetails(p_header_id IN NUMBER ,
608 x_return_code OUT NOCOPY NUMBER ,
609 x_error_description OUT NOCOPY VARCHAR2 ) IS
610
611 CURSOR c_top_lines IS
612 SELECT l.line_id ,
613 l.line_number ,
614 l.header_id ,
615 l.line_type_id ,
616 l.ship_from_org_id ,
617 l.schedule_ship_date,
618 l.promise_date ,
619 l.request_date ,
620 l.top_model_line_id ,
621 l.link_to_line_id ,
622 l.inventory_item_id ,
623 l.configuration_id,
624 l.config_header_id,
625 l.config_rev_nbr,
626 l.sort_order
627 FROM oe_order_lines_all l
628 WHERE l.header_id = p_header_id
629 AND l.link_to_line_id IS NULL
630 ORDER BY l.line_number ;
631
632 CURSOR c_child_lines(p_top_line_id NUMBER) IS
633 SELECT l.line_id ,
634 l.line_number ,
635 l.header_id ,
636 l.line_type_id ,
637 l.ship_from_org_id ,
638 l.schedule_ship_date,
639 l.promise_date ,
640 l.request_date ,
641 l.top_model_line_id ,
642 l.link_to_line_id ,
643 l.inventory_item_id ,
644 l.configuration_id,
645 l.config_header_id,
646 l.config_rev_nbr,
647 l.sort_order
648 FROM oe_order_lines_all l
649 WHERE l.header_id = p_header_id
650 AND l.top_model_line_id = p_top_line_id
651 AND l.link_to_line_id IS NOT NULL
652 AND l.line_id <> p_top_line_id
653 ORDER BY l.sort_order ;
654
655
656 l_init_seq NUMBER := 10 ;
657 l_next_seq NUMBER := 0 ;
658 l_seq NUMBER := 0;
659 l_prev_sort_order OE_ORDER_LINES_ALL.SORT_ORDER%TYPE ;
660 l_line_count NUMBER := 0;
661 l_line_type VARCHAR2(40) ;
662
663 BEGIN
664 x_return_code := 0;
665 x_error_description := null;
666
667 FOR c_top_rec IN c_top_lines
668 LOOP
669 IF xdp_om_util.Is_Activation_Reqd(p_line_id => c_top_rec.line_id) THEN
670
671 -- The top line is not a package and needs activation.
672
673 g_order_line_list(l_line_count).line_number := c_top_rec.line_id ;
674 g_order_line_list(l_line_count).line_source := 'OE_ORDER_LINES_ALL' ;
675 g_order_line_list(l_line_count).inventory_item_id := c_top_rec.inventory_item_id ;
676 g_order_line_list(l_line_count).action_code := c_top_rec.line_type_id ;
677 g_order_line_list(l_line_count).organization_id := c_top_rec.ship_from_org_id ;
678 g_order_line_list(l_line_count).ib_source := 'TXN' ;
679 g_order_line_list(l_line_count).ib_source_id := c_top_rec.line_id ;
680 g_order_line_list(l_line_count).required_fulfillment_date := NVL(TRUNC(c_top_rec.schedule_ship_date),c_top_rec.request_date) ;
681 g_order_line_list(l_line_count).fulfillment_required_flag := 'Y' ;
682 g_order_line_list(l_line_count).is_package_flag := 'N' ;
683 g_order_line_list(l_line_count).fulfillment_sequence := l_init_seq ;
684 g_order_line_list(l_line_count).priority := 100 ;
685 g_order_line_list(l_line_count).due_date := c_top_rec.promise_date ;
686 g_order_line_list(l_line_count).jeopardy_enabled_flag := 'N' ;
687 g_order_line_list(l_line_count).customer_required_date := c_top_rec.request_date ;
688 g_order_line_list(l_line_count).is_virtual_line_flag := 'N' ;
689
690 l_line_count := l_line_count + 1 ;
691 l_next_seq := l_init_seq ;
692
693 END IF ;
694
695 l_prev_sort_order := c_top_rec.sort_order;
696
697 FOR c_child_rec IN c_child_lines(c_top_rec.line_id)
698 LOOP
699
700 IF xdp_om_util.Is_Activation_Reqd(p_line_id => c_child_rec.line_id) THEN
701
702 IF l_prev_sort_order <> c_child_rec.sort_order THEN
703 l_next_seq := l_next_seq + 10;
704 l_prev_sort_order := c_child_rec.sort_order ;
705 END IF ;
706
707 g_order_line_list(l_line_count).line_number := c_child_rec.line_id ;
708 g_order_line_list(l_line_count).line_source := 'OE_ORDER_LINES_ALL' ;
709 g_order_line_list(l_line_count).inventory_item_id := c_child_rec.inventory_item_id ;
710 g_order_line_list(l_line_count).action_code := c_child_rec.line_type_id ;
711 g_order_line_list(l_line_count).organization_id := c_child_rec.ship_from_org_id ;
712 g_order_line_list(l_line_count).ib_source := 'TXN' ;
713 g_order_line_list(l_line_count).ib_source_id := c_child_rec.line_id ;
714 g_order_line_list(l_line_count).required_fulfillment_date := NVL(TRUNC(c_child_rec.schedule_ship_date),c_child_rec.request_date) ;
715 g_order_line_list(l_line_count).fulfillment_required_flag := 'Y' ;
716 g_order_line_list(l_line_count).is_package_flag := 'N' ;
717 g_order_line_list(l_line_count).fulfillment_sequence := l_next_seq ;
718 g_order_line_list(l_line_count).priority := 100 ;
719 g_order_line_list(l_line_count).due_date := c_child_rec.promise_date ;
720 g_order_line_list(l_line_count).jeopardy_enabled_flag := 'N' ;
721 g_order_line_list(l_line_count).customer_required_date := c_child_rec.request_date ;
722 g_order_line_list(l_line_count).is_virtual_line_flag := 'N' ;
723
724 l_line_count := l_line_count + 1 ;
725 END IF ;
726
727 END LOOP ;
728 END LOOP ;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 x_return_code := 0;
733 x_error_description := null;
734 END GetLineDetails ;
735
736
737 -- **************** WaitForFulfillment *********************
738
739
740 PROCEDURE WaitForFulfillment(itemtype IN VARCHAR2,
741 itemkey IN VARCHAR2,
742 actid IN NUMBER ,
743 resultout OUT NOCOPY VARCHAR2) IS
744
745 l_line_id NUMBER := to_number(itemkey);
746 l_ordeR_id NUMBER := to_number(null) ;
747 l_itemtype VARCHAR2(240) := 'XDPOMINT';
748 l_itemkey VARCHAR2(240) := itemkey;
749 l_resultout VARCHAR2(240) ;
750 l_flow_status_code VARCHAR2(30) := 'PROV_REQUEST';
751 e_exception EXCEPTION;
752 x_progress VARCHAR2(4000);
753 l_org_id NUMBER ;
754
755 BEGIN
756
757 IF XDP_OM_UTIL.IS_ACTIVATION_REQD(p_line_id => l_line_id) THEN
758
759
760 -- Publish SFM Fulfillment Start Event to start an SFM - OM Interface WF Process.
761
762 l_org_id := wf_engine.GetItemAttrNumber( itemtype => WaitForFulfillment.itemtype,
763 itemkey => WaitForFulfillment.itemkey,
764 aname => 'ORG_ID');
765
766 -- Date: 15 FEB 2006. Author: DPUTHIYE BUG#: 5023342
767 -- Change description: Replaced the call to set_client_info with MO_GLOBAL.set_policy_context(..)
768 -- Since this activity will be invoked from OEOL flows, current org is checked before setting it.
769 -- Other files impacted: None.
770
771 --dbms_application_info.set_client_info(l_org_id);
772 IF ( NVL(MO_GLOBAL.get_current_org_id, -99) <> l_org_id) THEN
773 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
774 END IF;
775
776 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
777 (p_line_id => l_line_id,
778 p_flow_status_code => 'PROV_REQUEST',
779 x_return_status => l_resultout );
780
781
782 IF l_resultout <> 'S' THEN
783 x_progress := 'Error While updating OM Order Line Flow status ';
784 RAISE e_exception ;
785 END IF ;
786
787 -- Publish XDP_FULFILL_START event to create SFM-OM Interface WF process as a part of it's sbscription .
788
789 PublishXDPFulfillStart(itemtype => WaitForFulfillment.itemtype,
790 itemkey => WaitForFulfillment.itemkey );
791
792 -- Subscribe to Fulfillment Done Event with reference Id as OM order line_id
793
794 XDP_OM_UTIL.SUBSCRIBE_SRV_FULFILLMENT_DONE
795 (itemtype => itemtype ,
796 itemkey => itemkey ,
797 actid => actid ,
798 resultout=> l_resultout) ;
799
800 resultout := l_resultout ;
801
802 ELSE resultout := 'COMPLETE' ;
803 END IF ;
804
805 EXCEPTION
806 WHEN e_exception THEN
807 wf_core.context('XDPCORE_OM', 'WaitForFulfillment',itemtype,itemkey,actid,x_progress);
808 raise ;
809 WHEN others THEN
810 x_progress := sqlcode ||' - ' ||sqlerrm ;
811 wf_core.context('XDPCORE_OM', 'WaitForFulfillment',itemtype,itemkey,actid,x_progress );
812 raise ;
813 END WaitForFulfillment ;
814
815 -- **************** PublishXDPFulfillDone *********************
816
817 PROCEDURE PublishXDPFulfillDone(itemtype IN VARCHAR2,
818 itemkey IN VARCHAR2) IS
819
820 l_line_id NUMBER ;
821 l_message_id NUMBER ;
822 l_error_code NUMBER ;
823 l_error_message VARCHAR2(4000);
824 x_progress VARCHAR2(4000);
825
826 e_publish_exception EXCEPTION ;
827
828 BEGIN
829
830 l_line_id := to_number(itemkey) ;
831
832 XNP_XDP_FULFILL_DONE_U.PUBLISH
833 (P_REFERENCE_ID => l_line_id ,
834 X_MESSAGE_ID => l_message_id ,
835 X_ERROR_CODE => l_error_code ,
836 X_ERROR_MESSAGE => l_error_message );
837
838 IF l_error_code <> 0 THEN
839 x_progress := 'In XDPCORE_OM.PublishXDPFulfillDone. Error while publishing XDP_FULFILL_DONE . Error :- ' ||l_error_message ;
840 RAISE e_publish_exception ;
841 END IF ;
842
843 EXCEPTION
844 WHEN e_publish_exception THEN
845 wf_core.context('XDPCORE_OM', 'PublishXDPFulfillDone', itemtype, itemkey, null, x_Progress);
846 raise;
847 WHEN OTHERS THEN
848 x_progress := sqlcode|| ' - '||sqlerrm ;
849 wf_core.context('XDPCORE_OM', 'PublishXDPFulfillDone', itemtype, itemkey,null,x_progress);
850 raise;
851 END PublishXDPFulfillDone;
852
853
854 -- **************** ProvisionLine *********************
855
856 PROCEDURE ProvisionLine(itemtype IN VARCHAR2,
857 itemkey IN VARCHAR2,
858 actid IN NUMBER,
859 resultout OUT NOCOPY VARCHAR2) IS
860
861 l_line_id NUMBER := to_number(itemkey);
862 l_resultout VARCHAR2(40) := null;
863 x_progress VARCHAR2(4000);
864 l_org_id NUMBER;
865
866 BEGIN
867 -- Check if the line provisioning is already completed
868
869 IsFulfillmentCompleted
870 (itemtype => ProvisionLine.itemtype,
871 itemkey => ProvisionLine.itemkey ,
872 actid => ProvisionLine.actid ,
873 resultout => l_resultout ) ;
874
875 IF l_resultout = 'LINE_COMPLETE' THEN
876 l_resultout := 'COMPLETE' ;
877 ELSE
878 IF l_resultout = 'LINE_NOT_FOUND' THEN
879
880 -- Update OM Line Flow Status to 'Provisioning Failed'
881
882 l_org_id := wf_engine.GetItemAttrNumber( itemtype => ProvisionLine.itemtype,
883 itemkey => ProvisionLine.itemkey,
884 aname => 'ORG_ID');
885
886 dbms_application_info.set_client_info(l_org_id);
887
888 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
889 (p_line_id => l_line_id,
890 p_flow_status_code => 'PROV_FAILED',
891 x_return_status => l_resultout );
892 END IF ;
893
894 -- Subscribe to XDP_LINE_DONE event to be published by SFM order line
895
896 XDP_OM_UTIL.SUBSCRIBE_XDP_LINE_DONE
897 (itemtype => ProvisionLine.itemtype,
898 itemkey => ProvisionLine.itemkey ,
899 actid => ProvisionLine.actid ,
900 resultout => l_resultout );
901 END IF ;
902
903 resultout := l_resultout ;
904
905 EXCEPTION
906 WHEN others THEN
907 x_progress := sqlcode|| ' - '||sqlerrm ;
908 wf_core.context('XDPCORE_OM', 'ProvisionLine',itemtype,itemkey,actid,x_progress);
909 raise;
910 END ProvisionLine ;
911
912
913 -- **************** IsFulfillmentCompleted *********************
914
915 PROCEDURE IsFulfillmentCompleted(itemtype IN VARCHAR2,
916 itemkey IN VARCHAR2,
917 actid IN NUMBER,
918 resultout OUT NOCOPY VARCHAR2) IS
919
920 l_line_id NUMBER := to_number(itemkey);
921 l_order_number NUMBER ;
922 l_order_version VARCHAR2(30);
923 l_header_id NUMBER;
924 l_line_status VARCHAR2(40);
925 l_resultout VARCHAR2(40);
926 l_line_item_id NUMBER;
927 l_order_id NUMBER;
928 x_progress VARCHAR2(4000) ;
929
930 BEGIN
931
932 SELECT h.order_number ,
933 NVL(h.version_number,'1.0') ,
934 h.header_id
935 INTO l_order_number ,
936 l_order_version ,
937 l_header_id
938 FROM oe_order_lines_all l ,
939 oe_order_headers_all h
940 WHERE l.line_id = l_line_id
941 AND l.header_id = h.header_id ;
942
943 BEGIN
944
945 SELECT l.status_code,
946 l.line_item_id,
947 l.order_id
948 INTO l_line_status ,
949 l_line_item_id,
950 l_order_id
951 FROM xdp_order_headers h,
952 xdp_order_line_items l
953 WHERE h.external_order_number = to_char(l_order_number)
954 AND h.external_order_version = l_order_version
955 AND h.order_ref_name = 'SALES'
956 AND h.order_id = l.order_id
957 AND l.line_number = l_line_id ;
958 EXCEPTION
959 WHEN no_data_found THEN
960 l_resultout := 'LINE_NOT_FOUND';
961 END ;
962
963
964 IF l_line_status IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELLED') THEN
965 l_resultout := 'LINE_COMPLETE';
966 ELSE
967 l_resultout := 'LINE_INCOMPLETE';
968 END IF;
969
970 IF l_line_item_id IS NOT NULL THEN
971
972 WF_ENGINE.SetItemAttrNumber(itemtype => IsFulfillmentCompleted.itemtype ,
973 itemkey => IsFulfillmentCompleted.itemkey ,
974 aname => 'LINE_ITEM_ID',
975 avalue => l_line_item_id );
976 END IF ;
977
978 IF l_order_id IS NOT NULL THEN
979
980 WF_ENGINE.SetItemAttrNumber(itemtype => IsFulfillmentCompleted.itemtype ,
981 itemkey => IsFulfillmentCompleted.itemkey ,
982 aname => 'ORDER_ID',
983 avalue => l_order_id );
984 END IF ;
985
986 resultout := l_resultout ;
987
988 EXCEPTION
989 WHEN others THEN
990 x_progress := sqlcode || ' - '||sqlerrm ;
991 wf_core.context('XDPCORE_OM', 'IsFulfillmentCompleted',itemtype,itemkey,actid,x_progress);
992 raise;
993
994 END IsFulfillmentCompleted ;
995
996 -- **************** UpdateTxnDetails *********************
997
998 PROCEDURE UpdateTxnDetails
999 (itemtype IN VARCHAR2,
1000 itemkey IN VARCHAR2,
1001 actid IN NUMBER ,
1002 resultout OUT NOCOPY VARCHAR2) IS
1003
1004 l_line_id NUMBER := to_number(itemkey);
1005 l_order_number NUMBER ;
1006 l_order_version NUMBER ;
1007 l_header_id NUMBER ;
1008 l_order_id NUMBER;
1009 l_line_item_id NUMBER;
1010 l_errCode NUMBER := 0;
1011 l_errStr VARCHAR2(1996) := NULL;
1012 e_exception EXCEPTION ;
1013 x_progress VARCHAR2(2000) ;
1014 l_error_description VARCHAR2(2000);
1015 l_resultout VARCHAR2(2000);
1016 l_org_id NUMBER;
1017
1018 BEGIN
1019
1020 l_order_id := wf_engine.GetItemAttrNumber(itemtype => UpdateTxnDetails.itemtype,
1021 itemkey => UpdateTxnDetails.itemkey,
1022 aname => 'ORDER_ID');
1023
1024 l_line_item_id := wf_engine.GetItemAttrNumber(itemtype => UpdateTxnDetails.itemtype,
1025 itemkey => UpdateTxnDetails.itemkey,
1026 aname => 'LINE_ITEM_ID');
1027
1028
1029 IF l_order_id IS NOT NULL AND
1030 l_line_item_id IS NOT NULL THEN
1031
1032 XDP_INSTALL_BASE.UPDATE_IB(p_order_id => l_order_id,
1033 p_line_id => l_line_item_id,
1034 p_error_code => l_errcode,
1035 p_error_description => l_error_description);
1036 ELSE
1037
1038 SELECT h.order_number ,
1039 NVL(h.version_number,'1.0') ,
1040 h.header_id
1041 INTO l_order_number ,
1042 l_order_version ,
1043 l_header_id
1044 FROM oe_order_lines_all l ,
1045 oe_order_headers_all h
1046 WHERE l.line_id = l_line_id
1047 AND l.header_id = h.header_id ;
1048
1049 BEGIN
1050 SELECT l.line_item_id,
1051 l.order_id
1052 INTO l_line_item_id,
1053 l_order_id
1054 FROM xdp_order_headers h,
1055 xdp_order_line_items l
1056 --Date: 05-JUL-2006, Author: DPUTHIYE, Bug#5370624/5222928
1057 --Description: Implicit conversion of l_order_number and l_order_version to number fails.
1058 --Dependencies: None.
1059 -- WHERE h.external_order_number = l_order_number
1060 -- AND h.external_order_version = l_order_version
1061 WHERE h.external_order_number = to_char(l_order_number)
1062 AND h.external_order_version = to_char(l_order_version)
1063 AND h.order_ref_name = 'SALES'
1064 AND h.order_id = l.order_id
1065 AND l.line_number = l_line_id ;
1066
1067 EXCEPTION
1068 WHEN no_data_found THEN
1069 x_progress := 'Could not find specified Fulfillment Order in SFM. Order Number : '|| l_order_number ||' Order Version : '||l_order_version ;
1070 RAISE e_exception ;
1071 END ;
1072
1073 XDP_INSTALL_BASE.UPDATE_IB(p_order_id => l_order_id,
1074 p_line_id => l_line_item_id,
1075 p_error_code => l_errcode,
1076 p_error_description => l_error_description);
1077
1078 END IF;
1079
1080 IF l_errCode <> 0 then
1081 x_progress := ' Error while updating TXN details for Item Attribute ( Line Item Id = '|| l_line_item_id ||')'||
1082 'UpdateTxnDetails. Error: ' || substr(sqlerrm,1,1500);
1083
1084 WF_ENGINE.SetItemAttrNumber(itemtype => UpdateTxnDetails.itemtype ,
1085 itemkey => UpdateTxnDetails.itemkey ,
1086 aname => 'NOTIF_ERROR_CODE',
1087 avalue => l_errcode );
1088 --Date: 05-JUL-2006, Author: DPUTHIYE, Bug#5370624/5222928
1089 --Description: 'NOTIF_ERROR_MESSAGE' is a text attribute.
1090 --WF_ENGINE.SetItemAttrNumber(itemtype => UpdateTxnDetails.itemtype ,
1091 WF_ENGINE.SetItemAttrText(itemtype => UpdateTxnDetails.itemtype ,
1092 itemkey => UpdateTxnDetails.itemkey ,
1093 aname => 'NOTIF_ERROR_MESSAGE',
1094 avalue => l_error_description);
1095
1096
1097 -- Update OM Line Flow Status to 'Provisioning Failed'
1098
1099 l_org_id := wf_engine.GetItemAttrNumber( itemtype => UpdateTxnDetails.itemtype,
1100 itemkey => UpdateTxnDetails.itemkey,
1101 aname => 'ORG_ID');
1102
1103 dbms_application_info.set_client_info(l_org_id);
1104
1105 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
1106 (p_line_id => l_line_id,
1107 p_flow_status_code => 'PROV_FAILED',
1108 x_return_status => l_resultout );
1109
1110 resultout := 'INCOMPLETE' ;
1111 ELSE
1112
1113 -- Update OM Line Flow Status to 'Provisioning Requested'
1114
1115 l_org_id := wf_engine.GetItemAttrNumber( itemtype => UpdateTxnDetails.itemtype,
1116 itemkey => UpdateTxnDetails.itemkey,
1117 aname => 'ORG_ID');
1118
1119 dbms_application_info.set_client_info(l_org_id);
1120
1121 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
1122 (p_line_id => l_line_id,
1123 p_flow_status_code => 'PROV_REQUEST',
1124 x_return_status => l_resultout );
1125
1126 resultout := 'COMPLETE' ;
1127 -- RAISE e_exception;
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN e_exception THEN
1132 wf_core.context('XDPCORE_OM', 'UpdateTxnDetails',itemtype,itemkey,actid,x_progress);
1133 raise ;
1134 WHEN others THEN
1135 x_progress := sqlcode || ' - '||sqlerrm ;
1136 wf_core.context('XDPCORE_OM', 'UpdateTxnDetails',itemtype,itemkey,actid,x_progress);
1137 raise ;
1138 END UpdateTxnDetails ;
1139
1140 -- **************** PublishXDPFulfillmentStart *********************
1141
1142 PROCEDURE PublishXDPFulfillStart(itemtype IN VARCHAR2,
1143 itemkey IN VARCHAR2) IS
1144
1145 l_line_id NUMBER ;
1146 l_message_id NUMBER ;
1147 l_error_code NUMBER ;
1148 l_error_message VARCHAR2(4000);
1149 x_Progress VARCHAR2(4000);
1150 e_publish_exception EXCEPTION ;
1151
1152 BEGIN
1153
1154 l_line_id := to_number(itemkey) ;
1155
1156 XNP_XDP_FULFILL_START_U.PUBLISH
1157 (XNP$LINE_ID => l_line_id ,
1158 P_REFERENCE_ID => l_line_id ,
1159 X_MESSAGE_ID => l_message_id ,
1160 X_ERROR_CODE => l_error_code ,
1161 X_ERROR_MESSAGE => l_error_message );
1162
1163 IF l_error_code <> 0 THEN
1164 x_progress := 'In XDPCORE_OM.PublishXDPFulfillStart. Error while publishing XDP_FULFILL_START . Error :- ' ||l_error_message ;
1165 RAISE e_publish_exception ;
1166 END IF ;
1167
1168 EXCEPTION
1169 WHEN e_publish_exception THEN
1170 wf_core.context('XDPCORE_OM', 'PublishXDPFulfillStart', itemtype, itemkey, null, x_Progress);
1171 raise;
1172 WHEN OTHERS THEN
1173 x_progress := sqlcode || ' - '||sqlerrm ;
1174 wf_core.context('XDPCORE_OM', 'PublishXDPFulfillStart', itemtype, itemkey,null,x_progress);
1175 raise;
1176 END PublishXDPFulfillStart;
1177
1178
1179 End XDPCORE_OM;