[Home] [Help]
PACKAGE BODY: APPS.XDP_INTERFACES
Source
1 PACKAGE BODY XDP_INTERFACES AS
2 /* $Header: XDPINTFB.pls 120.2 2006/08/09 14:38:25 dputhiye noship $ */
3
4
5 resource_busy exception;
6 pragma exception_init(resource_busy, -00054);
7 e_QTimeOut EXCEPTION;
8 PRAGMA EXCEPTION_INIT(e_QTimeOut, -25228);
9 e_QNavOut EXCEPTION;
10 PRAGMA EXCEPTION_INIT(e_QNavOut, -25237);
11
12 G_XDP_SCHEMA VARCHAR2(80);
13 G_LOCK_MSG_SUCCESS CONSTANT VARCHAR2(1) := 'S';
14 G_LOCK_MSG_FAIL CONSTANT VARCHAR2(1) := 'F';
15 G_LOCK_MSG_ERROR CONSTANT VARCHAR2(1) := 'E';
16 dbg_msg VARCHAR2(2000);
17 --
18 -- Private API which will lock and remove a message from queue
19 --
20 PROCEDURE Lock_and_Remove_Msg(
21 p_msg_id in raw,
22 p_queue_name in varchar2,
23 p_remove_flag in varchar2 DEFAULT 'Y',
24 x_user_data OUT NOCOPY SYSTEM.XDP_WF_CHANNELQ_TYPE,
25 x_lock_status OUT NOCOPY varchar2,
26 x_error OUT NOCOPY varchar2);
27
28 --
29 -- Private API which will remove a canceled order from the pending_order queue
30 --
31 PROCEDURE CANCEL_READY_ORDER
32 (p_sdp_order_id IN NUMBER,
33 p_msg_id IN RAW,
34 p_caller_name IN VARCHAR2,
35 return_code OUT NOCOPY NUMBER,
36 error_description OUT NOCOPY VARCHAR2);
37 --
38 -- Private API which will remove a canceled order from the processor queue
39 --
40
41 PROCEDURE Remove_Order_From_ProcessorQ(
42 p_sdp_order_id in number,
43 p_caller_name in varchar2,
44 return_code OUT NOCOPY number,
45 error_description OUT NOCOPY varchar2);
46
47 --
48 -- Private API which will remove a workitem from queue
49 --
50 PROCEDURE Remove_WI_From_Q(
51 p_wi_instance_id in number,
52 p_msg_id in raw,
53 p_caller_name in varchar2,
54 p_state in varchar2,
55 return_code OUT NOCOPY number,
56 error_description OUT NOCOPY varchar2);
57
58 --
59 -- Private API which will remove an FA from queue
60 --
61 PROCEDURE Remove_FA_From_Q(
62 p_fa_instance_id in number,
63 p_msg_id in raw,
64 p_caller_name in varchar2,
65 p_state in varchar2,
66 return_code OUT NOCOPY number,
67 error_description OUT NOCOPY varchar2);
68
69 --
70 -- Private API which will abort all workflow processes
71 -- for the given order
72 --
73 PROCEDURE Abort_Order_Workflows(
74 p_sdp_order_id in number,
75 return_code OUT NOCOPY number,
76 error_description OUT NOCOPY varchar2);
77
78 --
79 -- Private API which will check if all FAs are ready for cancel
80 -- for the given order
81 --
82 FUNCTION ARE_ALL_FAS_READY_FOR_CANCEL
83 ( p_order_id IN NUMBER
84 ) RETURN BOOLEAN ;
85
86 --
87 -- Function to check whether order_type is registered as Maintenance Mode type
88 --
89
90 FUNCTION IS_ORDER_TYPE_MAINT_AVAIL (
91 P_ORDER_TYPE IN VARCHAR2 )
92 RETURN BOOLEAN ;
93
94 --
95 -- Private API which will cancel order with status_code 'STANDBY'
96 --
97 PROCEDURE CANCEL_STANDBY_ORDER
98 (p_order_id IN NUMBER,
99 p_caller_name IN VARCHAR2,
100 return_code OUT NOCOPY NUMBER,
101 error_description OUT NOCOPY VARCHAR2);
102
103 --
104 -- Private API which will cancel order with status_code 'ERROR' OR 'IN PROGRESS'
105 --
106 PROCEDURE CANCEL_INPROGRESS_ORDER
107 ( p_sdp_order_id IN NUMBER,
108 p_msg_id IN RAW,
109 p_caller_name IN VARCHAR2,
110 return_code OUT NOCOPY NUMBER,
111 error_description OUT NOCOPY VARCHAR2);
112
113
114 --
115 -- Private API which will remove an FA from queue
116 --
117
118 PROCEDURE CANCEL_FA
119 (p_order_id IN NUMBER,
120 p_fa_instance_id IN NUMBER,
121 p_msg_id IN RAW,
122 p_caller_name IN VARCHAR2,
123 p_fa_wf_item_type IN VARCHAR2,
124 p_fa_wf_item_key IN VARCHAR2,
125 p_status IN VARCHAR2,
126 return_code OUT NOCOPY NUMBER,
127 error_description OUT NOCOPY VARCHAR2);
128
129
130 --
131 -- Private API which will remove a workitem from queue
132 --
133 PROCEDURE CANCEL_WORKITEM
134 (p_wi_instance_id IN NUMBER,
135 p_msg_id IN RAW,
136 p_wi_wf_item_type IN VARCHAR2,
137 p_wi_wf_item_key IN VARCHAR2,
138 p_caller_name IN VARCHAR2,
139 p_status IN VARCHAR2,
140 return_code OUT NOCOPY NUMBER,
141 error_description OUT NOCOPY VARCHAR2);
142
143 --
144 -- Private API which will update xdp_order_headers status_code
145 --
146 --
147 PROCEDURE UPDATE_XDP_ORDER_STATUS
148 (p_order_id IN NUMBER ,
149 p_status IN VARCHAR2,
150 p_caller_name IN VARCHAR2,
151 return_code OUT NOCOPY NUMBER,
152 error_description OUT NOCOPY VARCHAR2) ;
153
154 --
155 -- Private API which will update xdp_order_line_items status_code
156 --
157 --
158 PROCEDURE UPDATE_XDP_ORDER_LINE_STATUS
159 (p_order_id IN NUMBER,
160 p_lineitem_id IN NUMBER ,
161 p_status IN VARCHAR2,
162 p_caller_name IN VARCHAR2,
163 return_code OUT NOCOPY NUMBER,
164 error_description OUT NOCOPY VARCHAR2) ;
165
166 --
167 -- Private API which will update xdp_fulfill_worklist status_code
168 --
169 --
170
171 PROCEDURE UPDATE_XDP_WI_INSTANCE_STATUS
172 (p_order_id IN NUMBER,
173 p_wi_instance_id IN NUMBER ,
174 p_status IN VARCHAR2,
175 p_caller_name IN VARCHAR2,
176 return_code OUT NOCOPY NUMBER,
177 error_description OUT NOCOPY VARCHAR2) ;
178
179 --
180 -- Private API which will update xdp_fa_runtime_list status_code
181 --
182 --
183
184 PROCEDURE UPDATE_XDP_FA_INSTANCE_STATUS
185 (p_fa_instance_id IN NUMBER ,
186 p_status IN VARCHAR2,
187 p_caller_name IN VARCHAR2,
188 return_code OUT NOCOPY NUMBER,
189 error_description OUT NOCOPY VARCHAR2) ;
190
191 --
192 -- Provate API to update status or outbound messages to 'CANCELED' for canceled orders
193 --
194
195 PROCEDURE CANCEL_READY_MSGS(p_order_id IN NUMBER ,
196 x_error_code OUT NOCOPY NUMBER,
197 x_error_message OUT NOCOPY VARCHAR2) ;
198
199 --
200 -- Private API which will cancel the order
201 --
202 --
203
204 PROCEDURE CancelOrder(
205 P_SDP_ORDER_ID IN NUMBER,
206 p_caller_name IN VARCHAR2,
207 RETURN_CODE OUT NOCOPY NUMBER,
208 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2);
209
210
211 PROCEDURE Process_Old_Order(
212 P_ORDER_HEADER IN XDP_TYPES.ORDER_HEADER,
213 P_ORDER_PARAMETER IN XDP_TYPES.ORDER_PARAMETER_LIST,
214 P_ORDER_LINE_LIST IN XDP_TYPES.ORDER_LINE_LIST,
215 P_LINE_PARAMETER_LIST IN XDP_TYPES.LINE_PARAM_LIST,
216 P_execution_mode IN VARCHAR2 DEFAULT 'ASYNC',
217 SDP_ORDER_ID OUT NOCOPY NUMBER,
218 RETURN_CODE IN OUT NOCOPY NUMBER,
219 ERROR_DESCRIPTION IN OUT NOCOPY VARCHAR2);
220
221 -- ------------------------------------------
222 -- API for upstream ordering system to submit
223 -- a service activation order
224 -- -------------------------------------------
225
226 PROCEDURE Process_Order(
227 P_ORDER_HEADER IN XDP_TYPES.ORDER_HEADER,
228 P_ORDER_PARAMETER IN XDP_TYPES.ORDER_PARAMETER_LIST,
229 P_ORDER_LINE_LIST IN XDP_TYPES.ORDER_LINE_LIST,
230 P_LINE_PARAMETER_LIST IN XDP_TYPES.LINE_PARAM_LIST,
231 SDP_ORDER_ID OUT NOCOPY NUMBER,
232 RETURN_CODE IN OUT NOCOPY NUMBER,
233 ERROR_DESCRIPTION IN OUT NOCOPY VARCHAR2)
234 IS
235 lv_ORDER_HEADER XDP_TYPES.ORDER_HEADER;
236 lv_ORDER_PARAMETER XDP_TYPES.ORDER_PARAMETER_LIST;
237 lv_ORDER_LINE_LIST XDP_TYPES.ORDER_LINE_LIST;
238 lv_LINE_PARAMETER_LIST XDP_TYPES.LINE_PARAM_LIST;
239
240 -- PL/SQL Block
241 BEGIN
242 lv_order_header := P_ORDER_HEADER ;
243 lv_order_parameter := P_ORDER_PARAMETER ;
244 lv_ORDER_LINE_LIST := P_ORDER_LINE_LIST ;
245 lv_LINE_PARAMETER_LIST := P_LINE_PARAMETER_LIST ;
246 Process_Old_Order(
247 lv_ORDER_HEADER ,
248 lv_ORDER_PARAMETER ,
249 lv_ORDER_LINE_LIST ,
250 lv_LINE_PARAMETER_LIST ,
251 'ASYNC',
252 SDP_ORDER_ID,
253 RETURN_CODE ,
254 ERROR_DESCRIPTION);
255 EXCEPTION
256 WHEN OTHERS THEN
257 return_code := -191266;
258 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
259 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
260 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
261 error_description := FND_MESSAGE.GET;
262 END Process_Order;
263
264 PROCEDURE Process_DRC_Order(
265 p_workitem_id IN NUMBER,
266 p_task_parameter IN XDP_TYPES.ORDER_PARAMETER_LIST,
267 x_order_id OUT NOCOPY NUMBER,
268 x_return_code OUT NOCOPY NUMBER,
269 x_error_description OUT NOCOPY VARCHAR2)
270 IS
271 l_order_header xdp_types.order_header;
272 l_order_parameter xdp_types.order_parameter_list;
273 l_order_line_list xdp_types.order_line_list;
274 l_line_parameter_list xdp_types.line_param_list;
275
276 l_workitem_name VARCHAR2(200);
277 l_order_id NUMBER;
278 BEGIN
279
280 SELECT XDP_ORDER_HEADERS_S.NextVal
281 INTO l_order_id
282 FROM dual;
283
284 l_ORDER_HEADER.order_number := 'DRC-'||TO_CHAR(l_order_id+1);
285 l_ORDER_HEADER.provisioning_date := sysdate;
286 l_ORDER_HEADER.order_type := 'DRC';
287
288 SELECT
289 workitem_name
290 INTO
291 l_workitem_name
292 FROM
293 xdp_workitems
294 WHERE
295 workitem_id = p_workitem_id;
296
297 l_order_line_list(1).line_number := 1;
298 l_order_line_list(1).workitem_id := p_workitem_id;
299 l_order_line_list(1).line_item_name := l_workitem_name;
300 l_order_line_list(1).provisioning_date := sysdate;
301
302 IF p_task_parameter.count > 0 THEN
303 FOR l_count in 1..p_task_parameter.count LOOP
304 l_line_parameter_list(l_count).line_number := 1;
305 l_line_parameter_list(l_count).parameter_name := p_task_parameter(l_count).parameter_name;
306 l_line_parameter_list(l_count).parameter_value := p_task_parameter(l_count).parameter_value;
307 END LOOP;
308 END IF;
309
310 Process_Old_Order(
311 l_order_header ,
312 l_order_parameter ,
313 l_order_line_list ,
314 l_line_parameter_list ,
315 'SYNC',
316 x_order_id,
317 x_return_code ,
318 x_error_description);
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 x_return_code := SQLCODE;
322 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_WORKITEM_ID');
323 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
324 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
325 x_error_description := FND_MESSAGE.GET;
326 WHEN OTHERS THEN
327 x_return_code := -191266;
328 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
329 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
330 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
331 x_error_description := FND_MESSAGE.GET;
332 END Process_DRC_Order;
333
334 --
335 -- Private API check if all FAs are ready for cancel for the given order
336 --
337
338 FUNCTION ARE_ALL_FAS_READY_FOR_CANCEL
339 ( p_order_id IN NUMBER)
340 RETURN BOOLEAN IS
341
342 l_return BOOLEAN := TRUE ;
343
344 CURSOR c_fa IS
345 SELECT 'Y'
346 FROM xdp_fulfill_worklist fw,
347 xdp_fa_runtime_list fr
348 WHERE fw.order_id = p_order_id
349 AND fw.workitem_instance_id = fr.workitem_instance_id
350 AND fr.status_code IN ('IN PROGRESS');
351
352 BEGIN
353 FOR c_fa_rec IN c_fa
354 LOOP
355 l_return := FALSE ;
356 END LOOP ;
357
358 RETURN l_return ;
359
360 END ARE_ALL_FAS_READY_FOR_CANCEL;
361
362
363 -- -------------------------------------------------------------------
364 -- check to see whether order type is available during maintenance mode
365 -- -------------------------------------------------------------------
366
367 FUNCTION IS_ORDER_TYPE_MAINT_AVAIL (
368 p_order_type in varchar2 )
369 RETURN BOOLEAN IS
370 l_count NUMBER;
371
372 BEGIN
373
374 SELECT count(*)
375 INTO l_count
376 FROM FND_LOOKUP_VALUES
377 WHERE UPPER(lookup_code) = UPPER(p_order_type)
378 AND lookup_type = 'XDP_HA_ORDER_TYPES';
379
380 IF l_count < 1 THEN
381 return false;
382 ELSE
383 return true;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
389 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.IS_ORDER_TYPE_MAINT_AVAIL');
390 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
391
392 END IS_ORDER_TYPE_MAINT_AVAIL;
393
394 --
395 -- API for upstream ordering system to cancel a service activation order
396 --
397
398 PROCEDURE Cancel_Order(
399 P_ORDER_NUMBER IN VARCHAR2,
400 p_order_version IN VARCHAR2,
401 p_CALLER_NAME IN VARCHAR2 DEFAULT user,
402 RETURN_CODE OUT NOCOPY NUMBER,
403 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
404 IS
405 lv_id number;
406 BEGIN
407
408 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
409 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
410 dbg_msg := (' Being Cancel Order for Order Number : '||p_order_number||' Order Version : '||p_order_version);
411 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
412 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER', dbg_msg);
413 END IF;
414 END IF;
415 END IF;
416
417
418 BEGIN
419 IF p_order_version IS NOT NULL THEN
420 SELECT order_id
421 INTO lv_id
422 FROM xdp_order_headers
423 WHERE external_order_number = (p_order_number) and
424 external_order_version = (p_order_version);
425 ELSE
426 SELECT order_id
430 external_order_version IS NULL;
427 INTO lv_id
428 FROM xdp_order_headers
429 WHERE external_order_number = (p_order_number) and
431 END IF;
432
433 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
434 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
435 dbg_msg := ('Order Number is: '||p_order_number||' Order Version is : '||p_order_version||' Order Id is : '||lv_id);
436 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
437 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER', dbg_msg);
438 END IF;
439 END IF;
440 END IF;
441
442 EXCEPTION
443 WHEN no_data_found THEN
444 return_code := -191314;
445 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_VERSION_NOTEXISTS');
446 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
447 FND_MESSAGE.SET_TOKEN('ORDER_VERSION', p_order_version);
448 error_description := FND_MESSAGE.GET;
449 return;
450 END;
451
452 CANCEL_ORDER(
453 lv_id,
454 p_caller_name,
455 return_code,
456 error_description);
457
458 IF return_code <> 0 THEN
459 return;
460 END IF;
461
462 EXCEPTION
463 WHEN others THEN
464 return_code := -191266;
465 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
466 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
467 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
468 error_description := FND_MESSAGE.GET;
469 END CANCEL_ORDER;
470
471 --
472 -- API for upstream ordering system to cancel a service activation order
473 --
474 PROCEDURE CANCEL_ORDER
475 (P_SDP_ORDER_ID IN NUMBER,
476 p_CALLER_NAME IN VARCHAR2 DEFAULT user,
477 RETURN_CODE OUT NOCOPY NUMBER,
478 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
479 IS
480 lv_state VARCHAR2(40);
481 lv_tmp NUMBER;
482 lv_locked_q VARCHAR2(80);
483 lv_timer_ret NUMBER;
484 lv_timer_err VARCHAR2(800);
485 lv_event_ret NUMBER;
486 lv_event_err VARCHAR2(800);
487 lv_msg_ret NUMBER;
488 lv_msg_error VARCHAR2(800);
489
490 CURSOR lc_prereq_order IS
491 SELECT related_order_id
492 FROM xdp_order_relationships
493 WHERE order_id = p_sdp_order_id
494 AND order_relationship = 'IS_PREREQUISITE_OF';
495
496 lv_user VARCHAR2(80);
497
498 /* --Date: 09-AUG-06. Author: DPUTHIYE Bug#:5453523
499 --Change Description: Need to cancel SFM-OM interface flows as well.
500 --Cursor to fetch the item_keys for the SFM-OM Interface flows to be cancelled.
501 */
502 CURSOR lc_intf_flow_keys (p_order_number NUMBER) IS
503 SELECT line_number
504 FROM xdp_order_line_items xoli, xdp_order_headers xoh
505 WHERE xoli.order_id = xoh.order_id
506 AND xoh.order_source = 'OE_ORDER_HEADERS_ALL'
507 AND xoh.order_id = p_order_number;
508
509 -- Exception 'Process <item_type>/<item_key> does not exist' expected from the WF API.
510 e_no_such_process EXCEPTION;
511 PRAGMA EXCEPTION_INIT(e_no_such_process, -20002);
512 --End of fix:5453523
513
514 BEGIN
515
516 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
517 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
518 dbg_msg := (' Being Overloaded Cancel Order for Order Id : '||P_SDP_ORDER_ID);
519 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
520 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER', dbg_msg);
521 END IF;
522 END IF;
523 END IF;
524
525 IF p_caller_name IS NULL THEN
526 lv_user := user;
527 ELSE
528 lv_user := p_caller_name;
529 END IF;
530
531 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
532 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
533 dbg_msg := ('Calling CANCELORDER');
534 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
535 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_ORDER.VALIDATE_ORDER', dbg_msg);
536 END IF;
537 END IF;
538 END IF;
539
540
541 CANCElORDER (p_sdp_order_id,
542 lv_user,
543 return_code,
544 error_description);
545
546 IF return_code <> 0 THEN
547 return;
548 END IF;
549
550 /*
551 If we had successfully canceled the order,
552 we should try to cancel all the orders which
553 use this one as prerequisite. The following
554 is commented out for now because we still try
555 to finalize the requirement.
556
557 FOR lv_rel_ord_rec in lc_prereq_order
558 LOOP
559 CANCELORDER
560 (p_sdp_order_id => lv_rel_ord_rec.related_order_id,
561 p_caller_name => lv_user,
562 return_code => return_code,
563 error_description => error_description);
564
565 if return_code <> 0 then
566 return;
567 end if;
568 END LOOP;
569 */
570
571 /**
575
572 We should also clean upo the timer events.
573 Once the timer API is ready, put the following code:
574 **/
576 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
577 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
578 dbg_msg := ('Completed Order Cancelation deregistering timers');
579 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
580 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_ORDER.VALIDATE_ORDER', dbg_msg);
581 END IF;
582 END IF;
583 END IF;
584
585
586 XNP_TIMER_CORE.DEREGISTER(p_order_id => p_sdp_order_id,
587 x_error_code => lv_timer_ret,
588 x_error_message => lv_timer_err);
589 IF lv_timer_ret <> 0 then
590 lv_timer_err := ' Warning: Can not clean up timer event after '||
591 ' the order has been canceled. '||lv_timer_err;
592 END IF;
593
594
595 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
596 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
597 dbg_msg := ('Deregistering Events ');
598 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
599 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_ORDER.VALIDATE_ORDER', dbg_msg);
600 END IF;
601 END IF;
602 END IF;
603
604 /**
605 ** Clean up all the Events/Messages outstanding for the Order
606 **/
607
608 XNP_EVENT.DEREGISTER(p_order_id => p_sdp_order_id,
609 x_error_code => lv_event_ret,
610 x_error_message => lv_event_err);
611
612 IF lv_event_ret <> 0 THEN
613 lv_event_err := ' Warning: Can not clean up outstanding events or messages '||
614 ' after the order has been canceled. '||lv_event_err;
615 END IF;
616
617 IF lv_timer_ret <> 0 OR lv_event_ret <> 0 THEN
618 return_code := -20111;
619 error_description := lv_timer_err||lv_event_err;
620 END IF;
621
622 /**
623 ** Mark massages for the order in xnp_msgs table to 'CANCELED'
624 **/
625
626 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
627 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
628 dbg_msg := ('Canceling Messages ');
629 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
630 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_ORDER.VALIDATE_ORDER', dbg_msg);
631 END IF;
632 END IF;
633 END IF;
634
635 CANCEL_READY_MSGS(p_order_id => p_sdp_order_id,
636 x_error_code => lv_msg_ret,
637 x_error_message => lv_msg_error );
638
639 IF lv_msg_ret <> 0 THEN
640 lv_msg_error := 'Warning : Can not clean up outstanding messages '||
641 'after the order has been canceled. '||lv_msg_error ;
642 END IF;
643
644 IF lv_timer_ret <> 0 OR lv_event_ret <> 0 OR lv_msg_ret <> 0 THEN
645 return_code := -20111;
646 error_description := lv_timer_err||lv_event_err;
647 END IF;
648
649 /* Date: 09-AUG-06. Author: DPUTHIYE Bug#:5453523
650 ** Change Description: For orders that originate from Order Management, the SFM-OM Interface flows
651 ** must be cancelled.
652 */
653 IF((FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
654 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER')) THEN
655 dbg_msg := ('Cancelling SFM-OM Interface flows ');
656 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_ORDER', dbg_msg);
657 END IF;
658 END IF;
659
660 -- SFM-OM Interface flows have item_key = 'XDPOMINT' and item_key = <xdp_order_line_items.line_number>.
661 -- For each interface workflow process generated for this order
662 FOR l_intf_flow_key IN lc_intf_flow_keys (p_sdp_order_id)
663 LOOP
664 BEGIN --For the exceptions from the WF API call.
665 wf_engine.abortProcess('XDPOMINT', l_intf_flow_key.line_number);
666 EXCEPTION
667 WHEN e_no_such_process THEN
668 NULL; -- Ignore. The SFM-OM interface process may not have been created for some lines.
669 WHEN OTHERS THEN
670 raise; --An unexpected exception must be thrown out.
671 END;
672 END LOOP;
673 -- End of fix:5453523
674
675 EXCEPTION
676 when others then
677 return_code := -191266;
678 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
679 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
680 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
681 error_description := FND_MESSAGE.GET;
682
683 END Cancel_Order;
684
685 --
686 -- Private API to cancel a service order
687 --
688 PROCEDURE CANCELORDER(
689 p_sdp_order_id IN NUMBER,
690 p_caller_name IN VARCHAR2,
691 return_code OUT NOCOPY NUMBER,
692 error_description OUT NOCOPY VARCHAR2)
693 IS
694 lv_state VARCHAR2(40);
695 lv_msg_id RAW(16);
696 l_status VARCHAR2(40);
697 lv_mode VARCHAR2(8); -- maintenance mode profile
698 lv_order_type VARCHAR2(40);
699
703 BEGIN
700 -- Declare exception for order_type not registered
701 e_order_type_not_reg EXCEPTION;
702
704
705 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
706 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
707 dbg_msg := ('Procedure XDP_INTERFACES.CANCELORDER begins.');
708 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
710 END IF;
711 END IF;
712 END IF;
713
714 return_code := 0;
715 SAVEPOINT lv_order_tag;
716
717 SELECT status_code,
718 msgid,
719 order_type
720 INTO lv_state,
721 lv_msg_id,
722 lv_order_type
723 FROM xdp_order_headers
724 WHERE order_id = p_sdp_order_id;
725
726 --============================================================
727 -- Validate Order Type in High Availability Maintenance Mode
728 --============================================================
729 FND_PROFILE.GET('APPS_MAINTENANCE_MODE', lv_mode);
730
731 IF lv_mode = 'MAINT' THEN
732
733 IF IS_ORDER_TYPE_MAINT_AVAIL(lv_order_type) = false THEN
734 raise e_order_type_not_reg;
735 END IF;
736
737 END IF;
738 --============================================================
739
740
741 IF lv_state IN ('CANCELED','ABORTED') THEN
742
743 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
744 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
745 dbg_msg := ('Order Status is : '||lv_state);
746 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
747 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
748 END IF;
749 END IF;
750 END IF;
751
752 return_code := -191315;
753 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_CANCEL');
754 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
755 error_description := FND_MESSAGE.GET;
756 return;
757
758 ELSIF lv_state IN ('SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
759
760 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
761 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
762 dbg_msg := ('Order Status is : '||lv_state);
763 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
764 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
765 END IF;
766 END IF;
767 END IF;
768
769 return_code := -191316;
770 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_PROCESS');
771 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
772 error_description := FND_MESSAGE.GET;
773 return;
774
775 ELSIF lv_state = 'STANDBY' THEN
776
777 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
778 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
779 dbg_msg := ('Order Status is : '||lv_state||' Calling CANCEL_STANDBY_ORDER ');
780 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
781 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
782 END IF;
783 END IF;
784 END IF;
785
786 CANCEL_STANDBY_ORDER(p_sdp_order_id,
787 p_caller_name,
788 return_code,
789 error_description );
790 IF return_code <> 0 THEN
791 rollback to lv_order_tag;
792 return;
793 END IF;
794
795 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
796 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
797 dbg_msg := ('Completed CANCEL_STANDBY_ORDER ');
798 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
800 END IF;
801 END IF;
802 END IF;
803
804 ELSIF lv_state = 'READY' THEN
805
806 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
807 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
808 dbg_msg := ('Order Status is : '||lv_state||' Calling CANCEL_READY_ORDER ');
809 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
810 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
811 END IF;
812 END IF;
813 END IF;
814
815 CANCEL_READY_ORDER
816 (p_sdp_order_id => p_sdp_order_id,
817 p_msg_id => lv_msg_id,
818 p_caller_name => p_caller_name,
819 return_code => return_code,
820 error_description => error_description);
821
822 IF return_code <> 0 THEN
826
823 rollback to lv_order_tag;
824 return;
825 END IF;
827
828 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
829 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
830 dbg_msg := ('Completed CANCEL_READY_ORDER ');
831 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
832 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
833 END IF;
834 END IF;
835 END IF;
836
837 ELSIF lv_state IN ('ERROR','IN PROGRESS') THEN
838
839 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
840 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
841 dbg_msg := ('Order Status is : '||lv_state||' Calling CANCEL_INPROGRESS_ORDER ');
842 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
843 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
844 END IF;
845 END IF;
846 END IF;
847
848 CANCEL_INPROGRESS_ORDER
849 ( p_sdp_order_id => p_sdp_order_id,
850 p_msg_id => lv_msg_id,
851 p_caller_name => p_caller_name,
852 return_code => return_code,
853 error_description => error_description);
854
855 IF return_code <> 0 THEN
856 rollback to lv_order_tag;
857 return;
858 END IF;
859
860 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
861 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
862 dbg_msg := ('Completed CANCEL_INPROGRESS_ORDER ');
863 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
864 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
865 END IF;
866 END IF;
867 END IF;
868
869 ELSE
870
871 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
872 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER')) THEN
873 dbg_msg := ('Unknown Order Status ');
874 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
875 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCELORDER', dbg_msg);
876 END IF;
877 END IF;
878 END IF;
879
880 return_code := -191317;
881 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_UNKNOWN');
882 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
883 FND_MESSAGE.SET_TOKEN('STATUS', lv_state);
884 error_description := FND_MESSAGE.GET;
885 return;
886 END IF;
887 commit;
888
889 EXCEPTION
890 WHEN NO_DATA_FOUND THEN
891 rollback to lv_order_tag;
892 return_code := SQLCODE;
893 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
894 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
895 error_description := FND_MESSAGE.GET;
896
897 WHEN e_order_type_not_reg THEN
898 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_TYPE_NOT_AVAILABLE');
899 FND_MESSAGE.SET_TOKEN('ORDNUM', p_sdp_order_id);
900 error_description := FND_MESSAGE.GET;
901
902 WHEN OTHERS THEN
903 rollback to lv_order_tag;
904 return_code := -191266;
905 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
906 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
907 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
908 error_description := FND_MESSAGE.GET;
909 END CANCELORDER;
910
911 --
912 -- Private API to cancel order with 'STANDBY' status
913 --
914
915 PROCEDURE CANCEL_STANDBY_ORDER
916 (p_order_id IN NUMBER,
917 p_caller_name IN VARCHAR2,
918 return_code OUT NOCOPY NUMBER,
919 error_description OUT NOCOPY VARCHAR2) IS
920
921 l_status_code VARCHAR2(40);
922
923 BEGIN
924
925 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
926 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER')) THEN
927 dbg_msg := ('Procedure XDP_INTERFACES.CANCEL_STANDBY_ORDER begins. ');
928 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
929 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER', dbg_msg);
930 END IF;
931 END IF;
932 END IF;
933
934 SELECT status_code
935 INTO l_status_code
936 FROM xdp_order_headers
937 WHERE order_id = p_order_id
938 FOR UPDATE NOWAIT ;
939
940 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
941 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER')) THEN
942 dbg_msg := ('Aquired Lock on XDP_ORDER_HEADERS Calling UPDATE_XDP_ORDER_STATUS ');
943 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
944 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER', dbg_msg);
945 END IF;
946 END IF;
947 END IF;
948
949 -- update status_code of the XDP_ORDER_HEADERS
950
954 p_caller_name => p_caller_name,
951 UPDATE_XDP_ORDER_STATUS
952 (p_order_id => p_order_id,
953 p_status => 'CANCELED',
955 return_code => return_code,
956 error_description => error_description);
957
958 IF return_code <> 0 THEN
959 return;
960 END IF;
961
962
963 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
964 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER')) THEN
965 dbg_msg := ('Completed UPDATE_XDP_ORDER_STATUS Calling UPDATE_XDP_ORDER_LINE_STATUS ');
966 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
967 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER', dbg_msg);
968 END IF;
969 END IF;
970 END IF;
971
972 -- update status_code of the XDP_ORDER_LINE_ITEMS
973
974 UPDATE_XDP_ORDER_LINE_STATUS
975 (p_order_id => p_order_id ,
976 p_lineitem_id => null ,
977 p_status => 'CANCELED',
978 p_caller_name => p_caller_name,
979 return_code => return_code ,
980 error_description => error_description );
981
982 IF return_code <> 0 THEN
983 return;
984 END IF;
985
986
987
988 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
989 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER')) THEN
990 dbg_msg := ('Completed UPDATE_XDP_ORDER_LINE_STATUS Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
991 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
992 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER', dbg_msg);
993 END IF;
994 END IF;
995 END IF;
996
997 -- update status_code of the XDP_FULFILL_WORKLIST
998
999 UPDATE_XDP_WI_INSTANCE_STATUS
1000 (
1001 p_order_id => p_order_id ,
1002 p_wi_instance_id => null,
1003 p_status => 'CANCELED',
1004 p_caller_name => p_caller_name,
1005 return_code => return_code,
1006 error_description => error_description );
1007
1008 IF return_code <> 0 THEN
1009 return;
1010 END IF;
1011
1012
1013 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1014 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER')) THEN
1015 dbg_msg := ('Completed UPDATE_XDP_WI_INSTANCE_STATUS ');
1016 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1017 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_STANDBY_ORDER', dbg_msg);
1018 END IF;
1019 END IF;
1020 END IF;
1021
1022 EXCEPTION
1023 WHEN resource_busy OR no_data_found THEN
1024 return_code := -191318;
1025 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_REMOVE_ORDER');
1026 FND_MESSAGE.SET_TOKEN('ERROR_STRING', sqlcode||' - '||sqlerrm);
1027 error_description := FND_MESSAGE.GET;
1028 return;
1029 WHEN others THEN
1030 return_code := -191266;
1031 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1032 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
1033 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1034 error_description := FND_MESSAGE.GET;
1035 END CANCEL_STANDBY_ORDER ;
1036
1037 --
1038 -- Private API which will remove a canceled order
1039 -- from the pending_order queue
1040 --
1041 PROCEDURE CANCEL_READY_ORDER(
1042 p_sdp_order_id IN NUMBER,
1043 p_msg_id IN RAW,
1044 p_caller_name IN VARCHAR2,
1045 return_code OUT NOCOPY NUMBER,
1046 error_description OUT NOCOPY VARCHAR2) IS
1047
1048 lv_id NUMBER;
1049 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
1050 lv_lock_status VARCHAR2(1);
1051 lv_msg_id RAW(16);
1052 lv_error VARCHAR2(1000);
1053 lv_state VARCHAR2(100);
1054 e_cannot_cancel_order EXCEPTION ;
1055
1056 /* Date: 09-AUG-06. Author: DPUTHIYE Bug#:5453523
1057 ** Change Description: The %MAIN% order workflow process that also needs to be cancelled.
1058 ** must be cancelled.
1059 */
1060 l_main_wf_item_type VARCHAR2(8); --wf_items.item_type%TYPE;
1061 l_main_wf_item_key VARCHAR2(240); --wf_items.item_key%TYPE;
1062
1063 -- Exception 'Process <item_type>/<item_key> does not exist' expected from the WF API.
1064 e_no_such_process EXCEPTION;
1065 PRAGMA EXCEPTION_INIT(e_no_such_process, -20002);
1066 --End of Fix: Bug#:5453523
1067
1068 BEGIN
1069
1070 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1071 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1072 dbg_msg := ('Procedure XDP_INTERFACES.CANCEL_READY_ORDER begins.');
1073 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1077 END IF;
1074 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1075 END IF;
1076 END IF;
1078
1079 return_code := 0;
1080
1081 LOCK_AND_REMOVE_MSG(
1082 p_msg_id => cancel_ready_order.p_msg_id,
1083 p_queue_name => 'XDP_ORDER_PROC_QUEUE',
1084 x_user_data => lv_user_data,
1085 x_lock_status => lv_lock_status,
1086 x_error => lv_error);
1087
1088 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
1089
1090 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1091 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1092 dbg_msg := ('Aquired Lock on XDP_ORDER_PROC_QUEUE Calling UPDATE_XDP_ORDER_STATUS ');
1093 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1094 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1095 END IF;
1096 END IF;
1097 END IF;
1098
1099 UPDATE_XDP_ORDER_STATUS
1100 (p_order_id => p_sdp_order_id,
1101 p_status => 'CANCELED',
1102 p_caller_name => p_caller_name,
1103 return_code => return_code ,
1104 error_description => error_description );
1105
1106 IF return_code <> 0 THEN
1107 return;
1108 END IF;
1109
1110
1111 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1112 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1113 dbg_msg := ('Completed UPDATE_XDP_ORDER_STATUS Calling UPDATE_XDP_ORDER_LINE_STATUS ');
1114 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1115 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1116 END IF;
1117 END IF;
1118 END IF;
1119
1120 UPDATE_XDP_ORDER_LINE_STATUS
1121 (p_order_id => p_sdp_order_id ,
1122 p_lineitem_id => null ,
1123 p_status => 'CANCELED',
1124 p_caller_name => p_caller_name,
1125 return_code => return_code,
1126 error_description => error_description);
1127
1128 IF return_code <> 0 THEN
1129 return;
1130 END IF;
1131
1132
1133 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1134 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1135 dbg_msg := ('Completed UPDATE_XDP_ORDER_LINE_STATUS Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
1136 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1137 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1138 END IF;
1139 END IF;
1140 END IF;
1141
1142 UPDATE_XDP_WI_INSTANCE_STATUS
1143 (p_order_id => p_sdp_order_id ,
1144 p_wi_instance_id => null ,
1145 p_status => 'CANCELED',
1146 p_caller_name => p_caller_name,
1147 return_code => return_code,
1148 error_description => error_description );
1149
1150 IF return_code <> 0 THEN
1151 return;
1152 END IF;
1153
1154 /* Date: 09-AUG-06. Author: DPUTHIYE Bug#:5453523
1155 ** Change Description: Cancel also the %MAIN% workflow process (XDPPROV) for the READY ORDER
1156 ** The wf_item_type and wf_item_key are available from the order header.
1157 ** A no_data_found is not expected here.
1158 */
1159 IF((FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1160 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1161 dbg_msg := ('Cancelling the MAIN order workflow process');
1162 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1163 END IF;
1164 END IF;
1165
1166 SELECT wf_item_type, wf_item_key
1167 INTO l_main_wf_item_type, l_main_wf_item_key
1168 FROM xdp_order_headers
1169 WHERE order_id = p_sdp_order_id;
1170
1171 IF (l_main_wf_item_type IS NOT NULL AND l_main_wf_item_key IS NOT NULL) THEN
1172 BEGIN --For the exceptions from the WF API call.
1173 wf_engine.abortProcess(l_main_wf_item_type, l_main_wf_item_key);
1174 EXCEPTION
1175 WHEN e_no_such_process THEN
1176 NULL; -- Ignore. The process does not exist.
1177 WHEN OTHERS THEN
1178 raise; --An unexpected exception must be thrown out.
1179 END;
1180 END IF;
1181 --End of Fix: Bug#:5453523
1182
1183 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1184 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1188 END IF;
1185 dbg_msg := ('Completed UPDATE_XDP_WI_INSTANCE_STATUS ');
1186 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1187 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1189 END IF;
1190 END IF;
1191
1192 return;
1193
1194 ELSIF lv_lock_status = G_LOCK_MSG_FAIL THEN
1195
1196 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1197 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER')) THEN
1198 dbg_msg := ('Colud not Aquire Lock Calling CANCEL_INPROGRESS_ORDER ');
1199 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1200 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_ORDER', dbg_msg);
1201 END IF;
1202 END IF;
1203 END IF;
1204
1205 CANCEL_INPROGRESS_ORDER
1206 ( p_sdp_order_id => cancel_ready_order.p_sdp_order_id,
1207 p_msg_id => cancel_ready_order.p_msg_id ,
1208 p_caller_name => cancel_ready_order.p_caller_name,
1209 return_code => return_code,
1210 error_description => error_description);
1211
1212 IF return_code <> 0 THEN
1213 return;
1214 END IF;
1215 RETURN;
1216 ELSE
1217 RAISE e_cannot_cancel_order ;
1218 END IF;
1219
1220 EXCEPTION
1221 WHEN e_cannot_cancel_order THEN
1222 return_code := -191318;
1223 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_REMOVE_ORDER');
1224 FND_MESSAGE.SET_TOKEN('ERROR_STRING', lv_error);
1225 error_description := FND_MESSAGE.GET;
1226 return;
1227 WHEN OTHERS THEN
1228 return_code := -191266;
1229 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1230 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
1231 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1232 error_description := FND_MESSAGE.GET;
1233 END CANCEL_READY_ORDER;
1234
1235
1236 PROCEDURE CANCEL_INPROGRESS_ORDER
1237 ( p_sdp_order_id IN NUMBER,
1238 p_msg_id IN RAW,
1239 p_caller_name IN VARCHAR2,
1240 return_code OUT NOCOPY NUMBER,
1241 error_description OUT NOCOPY VARCHAR2)
1242 IS
1243
1244 lv_id NUMBER;
1245 lv_item_type VARCHAR2(80);
1246 lv_item_key VARCHAR2(300);
1247 lv_abort_wf BOOLEAN := FALSE;
1248 lv_state VARCHAR2(80);
1249 lv_in_queue VARCHAR2(1) := 'Y';
1250 lv_msg_id RAW(16);
1251 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
1252 lv_lock_status VARCHAR2(1);
1253 lv_error VARCHAR2(1000);
1254 l_fa_item_key VARCHAR2(300);
1255 l_fa_item_type VARCHAR2(80);
1256 l_fa_instance_id NUMBER ;
1257
1258 e_xdp_order_state_cancel EXCEPTION ;
1259 e_xdp_order_state_process EXCEPTION ;
1260 e_xdp_fa_state_inprogress EXCEPTION ;
1261
1262 CURSOR c_fa(l_order_id NUMBER) IS
1263 SELECT fa_instance_id,
1264 frt.msgid,
1265 frt.status_code ,
1266 frt.wf_item_type,
1267 frt.wf_item_key
1268 FROM xdp_fa_runtime_list frt,
1269 xdp_fulfill_worklist fwt
1270 WHERE fwt.order_id = l_order_id and
1271 fwt.workitem_instance_id = frt.workitem_instance_id;
1272
1273 CURSOR c_wi(l_order_id number) IS
1274 SELECT workitem_instance_id,
1275 wf_item_type,
1276 wf_item_key,
1277 status_code,
1278 msgid
1279 FROM xdp_fulfill_worklist
1280 WHERE order_id = l_order_id;
1281
1282 CURSOR c_line(l_order_id number) IS
1283 SELECT line_item_id,
1284 wf_item_key,
1285 wf_item_type,
1286 status_code
1287 FROM xdp_order_line_items
1288 WHERE order_id = l_order_id
1289 ORDER By is_package_flag;
1290
1291 BEGIN
1292
1293 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1294 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1295 dbg_msg := ('Procedure XDP_INTERFACES.CANCEL_INPROGRESS_ORDER begins.');
1296 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1297 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1298 END IF;
1299 END IF;
1300 END IF;
1301
1302 return_code := 0;
1303
1304 savepoint lv_order_tag;
1305
1306 SELECT order_id ,
1307 wf_item_type,
1308 wf_item_key,
1309 status_code,
1310 msgid
1311 INTO lv_id,
1312 lv_item_type,
1313 lv_item_key,
1314 lv_state,
1315 lv_msg_id
1316 FROM xdp_order_headers
1317 WHERE order_id = p_sdp_order_id
1318 FOR UPDATE of XDP_ORDER_HEADERS.STATUS_CODE NOWAIT;
1319
1320 -- Order has already been canceled
1321 IF lv_state IN ('CANCELED','ABORTED') THEN
1322
1323 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1327 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1324 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1325 dbg_msg := ('Order Status is : '||lv_state ||' Can not cancel Order ');
1326 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1328 END IF;
1329 END IF;
1330 END IF;
1331 raise e_xdp_order_state_cancel ;
1332
1333 -- Order has already been completed
1334
1335 ELSIF lv_state IN ('SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1336
1337 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1338 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1339 dbg_msg := ('Order Status is : '||lv_state ||' Can not cancel Order ');
1340 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1341 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1342 END IF;
1343 END IF;
1344 END IF;
1345 raise e_xdp_order_state_process ;
1346
1347 -- Order has already been started
1348 ELSIF lv_state = 'IN PROGRESS' THEN
1349 lv_abort_wf := TRUE;
1350
1351 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1352 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1353 dbg_msg := ('Order Status is : '||lv_state );
1354 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1355 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1356 END IF;
1357 END IF;
1358 END IF;
1359
1360 rollback to lv_order_tag;
1361
1362 -- release the lock for now as we are using bottom up approach
1363 ELSE
1364 rollback to lv_order_tag;
1365 END IF;
1366
1367 -- At this point the order process has been started
1368 -- We will take the bottom up approach to make all
1369 -- activities within this order to be CANCELED
1370
1371 IF ARE_ALL_FAS_READY_FOR_CANCEL(p_sdp_order_id ) THEN
1372
1373 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1374 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1375 dbg_msg := ('FAs Are ready for Cancel Starting with FA ');
1376 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1377 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1378 END IF;
1379 END IF;
1380 END IF;
1381
1382 FOR c_fa_rec in c_fa(p_sdp_order_id)
1383 LOOP
1384
1385 SELECT fr.status_code
1386 INTO lv_state
1387 FROM xdp_fa_runtime_list fr
1388 WHERE fa_instance_id = c_fa_rec.fa_instance_id
1389 FOR UPDATE OF fr.status_code NOWAIT;
1390
1391 IF lv_state IN ('CANCELED','ABORTED') THEN
1392
1393 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1394 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1395 dbg_msg := ('Status Of FA : '||c_fa_rec.fa_instance_id||' is '||lv_state);
1396 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1397 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1398 END IF;
1399 END IF;
1400 END IF;
1401 NULL;
1402
1403 ELSIF lv_state IN ('SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1404
1405 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1406 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1407 dbg_msg := ('Status Of FA : '||c_fa_rec.fa_instance_id||' is '||lv_state);
1408 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1409 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1410 END IF;
1411 END IF;
1412 END IF;
1413 NULL;
1414 ELSIF lv_state = 'IN PROGRESS' THEN
1415 l_fa_instance_id := c_fa_rec.fa_instance_id ;
1416
1417 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1418 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1419 dbg_msg := ('FA : '||c_fa_rec.fa_instance_id||' is INPROGRESS Can not cancel Order');
1420 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1421 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1422 END IF;
1423 END IF;
1424 END IF;
1425 raise e_xdp_fa_state_inprogress;
1426 -- raise can not cancel order fa in progress try again
1427 ELSE
1428 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1432 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1429 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1430 dbg_msg := ('Calling CANCEL_FA for FA : '||c_fa_rec.fa_instance_id);
1431 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1433 END IF;
1434 END IF;
1435 END IF;
1436
1437 CANCEL_FA(p_order_id => p_sdp_order_id,
1438 p_fa_instance_id => c_fa_rec.fa_instance_id,
1439 p_msg_id => c_fa_rec.msgid,
1440 p_caller_name => p_caller_name,
1441 p_fa_wf_item_type => c_fa_rec.wf_item_type,
1442 p_fa_wf_item_key => c_fa_rec.wf_item_key,
1443 p_status => lv_state,
1444 return_code => return_code,
1445 error_description => error_description);
1446
1447 if return_code <> 0 then
1448 rollback to lv_order_tag;
1449 return;
1450 end if;
1451 END IF;
1452 END LOOP;
1453
1454 FOR c_wi_rec in c_wi(p_sdp_order_id)
1455 LOOP
1456 SELECT fw.status_code
1457 INTO lv_state
1458 FROM xdp_fulfill_worklist fw
1459 WHERE workitem_instance_id = c_wi_rec.workitem_instance_id
1460 FOR UPDATE OF fw.status_code NOWAIT;
1461
1462 IF lv_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1463
1464 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1465 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1466 dbg_msg := ('Status Of WI : '||c_wi_rec.workitem_instance_id||' is '||lv_state);
1467 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1468 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1469 END IF;
1470 END IF;
1471 END IF;
1472 null;
1473 ELSE
1474
1475 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1476 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1477 dbg_msg := ('Calling CANCEL_WORKITEM for WI : ' ||c_wi_rec.workitem_instance_id);
1478 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1479 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1480 END IF;
1481 END IF;
1482 END IF;
1483
1484 CANCEL_WORKITEM
1485 (p_wi_instance_id => c_wi_rec.workitem_instance_id,
1486 p_msg_id => c_wi_rec.msgid,
1487 p_wi_wf_item_type => c_wi_rec.wf_item_type,
1488 p_wi_wf_item_key => c_wi_rec.wf_item_key,
1489 p_caller_name => p_caller_name,
1490 p_status => c_wi_rec.status_code,
1491 return_code => return_code,
1492 error_description => error_description);
1493
1494 IF return_code <> 0 THEN
1495 rollback to lv_order_tag;
1496 return;
1497 END IF;
1498 END IF;
1499
1500 END LOOP;
1501
1502 FOR c_line_rec in c_line(p_sdp_order_id)
1503 LOOP
1504
1505 SELECT status_code
1506 INTO lv_state
1507 FROM xdp_order_line_items
1508 WHERE line_item_id = c_line_rec.line_item_id
1509 FOR UPDATE OF STATUS_CODE NOWAIT;
1510
1511 IF lv_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1512 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1513 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1514 dbg_msg := ('Status Of Line : '||c_line_rec.line_item_id||' is '||lv_state);
1515 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1516 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1517 END IF;
1518 END IF;
1519 END IF;
1520 NULL ;
1521 ELSE
1522 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1523 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1524 dbg_msg := ('Calling UPDATE_XDP_ORDER_LINE_STATUS for Line : '||c_line_rec.line_item_id);
1525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1526 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1527 END IF;
1528 END IF;
1529 END IF;
1530
1534 p_status => 'CANCELED',
1531 UPDATE_XDP_ORDER_LINE_STATUS
1532 (p_order_id => null,
1533 p_lineitem_id => c_line_rec.line_item_id,
1535 p_caller_name => p_caller_name,
1536 return_code => return_code ,
1537 error_description => error_description );
1538
1539 IF return_code <> 0 THEN
1540 return;
1541 END IF;
1542 END IF;
1543 END LOOP;
1544
1545 SELECT oh.status_code --- ?? Do I need to lock it again ?
1546 INTO lv_state
1547 FROM xdp_order_headers oh
1548 WHERE oh.order_id = p_sdp_order_id
1549 FOR UPDATE OF oh.status_code NOWAIT;
1550
1551 IF lv_state IN ('CANCELED','ABORTED') then
1552 raise e_xdp_order_state_cancel ;
1553 ELSIF lv_state IN ('SUCCESS','SUCCESS_WITH_OVERRIDE') then
1554 raise e_xdp_order_state_process ;
1555 ELSE
1556 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1557 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1558 dbg_msg := ('Calling UPDATE_XDP_ORDER_STATUS' );
1559 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1560 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1561 END IF;
1562 END IF;
1563 END IF;
1564
1565
1566 UPDATE_XDP_ORDER_STATUS
1567 (p_order_id => p_sdp_order_id,
1568 p_status => 'CANCELED',
1569 p_caller_name => p_caller_name,
1570 return_code => return_code,
1571 error_description => error_description );
1572
1573 IF return_code <> 0 THEN
1574 return;
1575 END IF;
1576
1577 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1578 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER')) THEN
1579 dbg_msg := ('Completed UPDATE_XDP_ORDER_STATUS Calling ABORT_ORDER_WORKFLOWS' );
1580 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1581 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_INPROGRESS_ORDER', dbg_msg);
1582 END IF;
1583 END IF;
1584 END IF;
1585
1586
1587 ABORT_ORDER_WORKFLOWS
1588 ( p_sdp_order_id,
1589 return_code,
1590 error_description);
1591
1592 IF return_code <> 0 THEN
1593 return;
1594 END IF;
1595 END IF;
1596 ELSE
1597 raise e_xdp_order_state_process;
1598 -- raise can not cancel order
1599 END IF;
1600
1601 EXCEPTION
1602 WHEN resource_busy OR no_data_found THEN
1603 return_code := -191318;
1604 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_REMOVE_ORDER');
1605 FND_MESSAGE.SET_TOKEN('ERROR_STRING', lv_error);
1606 error_description := FND_MESSAGE.GET;
1607 return;
1608 WHEN e_xdp_order_state_process THEN
1609 return_code := -191316;
1610 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_PROCESS');
1611 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
1612 error_description := FND_MESSAGE.GET;
1613 rollback to lv_order_tag;
1614 return;
1615 WHEN e_xdp_order_state_cancel THEN
1616 return_code := -191315;
1617 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_CANCEL');
1618 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
1619 error_description := FND_MESSAGE.GET;
1620 rollback to lv_order_tag;
1621 return;
1622 WHEN e_xdp_fa_state_inprogress THEN
1623 return_code := 197010;
1624 FND_MESSAGE.SET_NAME('XDP', 'XDP_FA_STATE_INPROGRESS');
1625 FND_MESSAGE.SET_TOKEN('FA_INSTANCE_ID', l_fa_instance_id);
1626 error_description := FND_MESSAGE.GET;
1627 rollback to lv_order_tag;
1628 return;
1629 WHEN OTHERS THEN
1630 return_code := -191266;
1631 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1632 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
1633 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1634 error_description := FND_MESSAGE.GET;
1635 END CANCEL_INPROGRESS_ORDER;
1636
1637 --
1638 -- Private API which will remove a canceled order from the processor queue
1639 --
1640 PROCEDURE REMOVE_ORDER_FROM_PROCESSORQ
1641 (p_sdp_order_id IN NUMBER,
1642 p_caller_name IN VARCHAR2,
1643 return_code OUT NOCOPY NUMBER,
1644 error_description OUT NOCOPY VARCHAR2) IS
1645
1646 lv_id NUMBER;
1647 lv_item_type VARCHAR2(80);
1648 lv_item_key VARCHAR2(300);
1649 lv_abort_wf BOOLEAN := FALSE;
1650 lv_state VARCHAR2(80);
1651 lv_in_queue VARCHAR2(1) := 'Y';
1652 lv_msg_id RAW(16);
1653 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
1657 CURSOR lc_fa(l_order_id NUMBER) IS
1654 lv_lock_status VARCHAR2(1);
1655 lv_error VARCHAR2(1000);
1656
1658 SELECT fa_instance_id,
1659 frt.msgid
1660 FROM xdp_fa_runtime_list frt,
1661 xdp_fulfill_worklist fwt
1662 WHERE fwt.order_id = l_order_id and
1663 fwt.workitem_instance_id = frt.workitem_instance_id;
1664
1665 CURSOR lc_wi(l_order_id number) IS
1666 SELECT workitem_instance_id,
1667 msgid
1668 FROM xdp_fulfill_worklist
1669 WHERE order_id = l_order_id;
1670
1671 CURSOR lc_line(l_order_id number) IS
1672 SELECT line_item_id
1673 FROM xdp_order_line_items
1674 WHERE order_id = l_order_id
1675 ORDER By is_package_flag;
1676
1677 BEGIN
1678 return_code := 0;
1679
1680 savepoint lv_order_tag;
1681
1682 SELECT order_id ,
1683 wf_item_type,
1684 wf_item_key,
1685 status_code,
1686 msgid
1687 INTO lv_id,
1688 lv_item_type,
1689 lv_item_key,
1690 lv_state,
1691 lv_msg_id
1692 FROM xdp_order_headers
1693 WHERE order_id = p_sdp_order_id
1694 FOR UPDATE of XDP_ORDER_HEADERS.STATUS_CODE NOWAIT;
1695
1696 -- Order has already been canceled
1697 IF lv_state IN ('CANCELED','ABORTED') THEN
1698 return_code := -191315;
1699 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_CANCEL');
1700 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
1701 error_description := FND_MESSAGE.GET;
1702 rollback to lv_order_tag;
1703 return;
1704
1705 -- Order has already been completed
1706
1707 ELSIF lv_state IN ('SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1708 return_code := -191316;
1709 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_PROCESS');
1710 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
1711 error_description := FND_MESSAGE.GET;
1712 rollback to lv_order_tag;
1713 return;
1714
1715 -- Order has already been started
1716 ELSIF lv_state = 'IN PROGRESS' THEN
1717 lv_in_queue := 'N';
1718 lv_abort_wf := TRUE;
1719 rollback to lv_order_tag;
1720
1721 -- release the lock for now as we are using bottom up approach
1722 ELSE
1723 rollback to lv_order_tag;
1724 END IF;
1725
1726 IF lv_in_queue = 'Y' THEN
1727 Lock_and_Remove_Msg(
1728 p_msg_id => lv_msg_id,
1729 p_queue_name => 'XDP_ORDER_PROC_QUEUE',
1730 x_user_data =>lv_user_data,
1731 x_lock_status => lv_lock_status,
1732 x_error => lv_error);
1733
1734 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
1735
1736 -- No Order Workflow has been started
1737 -- Simply update the state to CANCELED
1738
1739 update xdp_order_headers
1740 set last_updated_by = FND_GLOBAL.USER_ID,
1741 last_update_date = sysdate,
1742 last_update_login = FND_GLOBAL.LOGIN_ID,
1743 status_code = 'CANCELED',
1744 canceled_by = p_caller_name,
1745 cancel_provisioning_date = sysdate
1746 where order_id = p_sdp_order_id;
1747
1748 update xdp_order_line_items
1749 set last_updated_by = FND_GLOBAL.USER_ID,
1750 last_update_date = sysdate,
1751 last_update_login = FND_GLOBAL.LOGIN_ID,
1752 status_code = 'CANCELED',
1753 canceled_by = p_caller_name,
1754 cancel_provisioning_date = sysdate
1755 where order_id = p_sdp_order_id;
1756
1757 update XDP_FULFILL_WORKLIST
1758 set last_updated_by = FND_GLOBAL.USER_ID,
1759 last_update_date = sysdate,
1760 last_update_login = FND_GLOBAL.LOGIN_ID,
1761 status_code = 'CANCELED',
1762 canceled_by = p_caller_name,
1763 cancel_provisioning_date = sysdate
1764 where order_id = p_sdp_order_id;
1765 commit;
1766 return;
1767 ELSIF lv_lock_status = G_LOCK_MSG_FAIL THEN
1768 lv_abort_wf := TRUE;
1769 ELSE
1770 return_code := -191318;
1771 /*
1772 error_description := 'Error: Can not remove order from queue. '||
1773 lv_error;
1774 */
1775 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_REMOVE_ORDER');
1776 FND_MESSAGE.SET_TOKEN('ERROR_STRING', lv_error);
1777 error_description := FND_MESSAGE.GET;
1778 return;
1779 END IF;
1780 END IF;
1781
1782 -- At this point the order process has been started
1783 -- We will take the bottom up approach to make all
1784 -- activities within this order to be CANCELED
1785
1786 FOR lv_fa_rec in lc_fa(p_sdp_order_id)
1787 loop
1788 select status_code into lv_state
1789 from xdp_fa_runtime_list
1790 where fa_instance_id = lv_fa_rec.fa_instance_id;
1791
1792 if lv_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') then
1793 null;
1794 else
1795 remove_fa_from_q(
1796 lv_fa_rec.fa_instance_id,
1797 lv_fa_rec.msgid,
1798 p_caller_name,
1799 lv_state,
1800 return_code,
1801 error_description);
1802 if return_code <> 0 then
1803 return;
1804 end if;
1805 end if;
1809 loop
1806 END LOOP;
1807
1808 FOR lv_wi_rec in lc_wi(p_sdp_order_id)
1810 select status_code into lv_state
1811 from XDP_FULFILL_WORKLIST
1812 where workitem_instance_id = lv_wi_rec.workitem_instance_id;
1813
1814 if lv_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') then
1815 null;
1816 else
1817 remove_wi_from_q(
1818 lv_wi_rec.workitem_instance_id,
1819 lv_wi_rec.msgid,
1820 p_caller_name,
1821 lv_state,
1822 return_code,
1823 error_description);
1824 if return_code <> 0 then
1825 return;
1826 end if;
1827 end if;
1828
1829 END LOOP;
1830
1831 FOR lv_line_rec in lc_line(p_sdp_order_id) loop
1832 savepoint lv_line_tag;
1833
1834 select status_code
1835 into lv_state
1836 from xdp_order_line_items
1837 where line_item_id = lv_line_rec.line_item_id
1838 for update of status_code;
1839
1840 if lv_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') then
1841 rollback to lv_line_tag;
1842 else
1843 update xdp_order_line_items
1844 set
1845 last_updated_by = FND_GLOBAL.USER_ID,
1846 last_update_date = sysdate,
1847 last_update_login = FND_GLOBAL.LOGIN_ID,
1848 status_code = 'CANCELED',
1849 canceled_by = p_caller_name,
1850 cancel_provisioning_date = sysdate
1851 where line_item_id = lv_line_rec.line_item_id;
1852 commit;
1853 end if;
1854 END LOOP;
1855
1856 savepoint lv_order_tag2;
1857
1858 select status_code
1859 into lv_state
1860 from xdp_order_headers
1861 where order_id = p_sdp_order_id
1862 FOR UPDATE of XDP_ORDER_HEADERS.STATUS_CODE ;
1863
1864 if lv_state IN ('CANCELED','ABORTED') then
1865 return_code := -191315;
1866 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_CANCEL');
1867 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
1868 error_description := FND_MESSAGE.GET;
1869 return;
1870 elsif lv_state IN ('SUCCESS','SUCCESS_WITH_OVERRIDE') then
1871 rollback to lv_order_tag2;
1872 return_code := -191316;
1873 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_STATE_PROCESS');
1874 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
1875 error_description := FND_MESSAGE.GET;
1876 return;
1877 else
1878 update xdp_order_headers
1879 set last_updated_by = FND_GLOBAL.USER_ID,
1880 last_update_date = sysdate,
1881 last_update_login = FND_GLOBAL.LOGIN_ID,
1882 status_code = 'CANCELED',
1883 canceled_by = p_caller_name,
1884 cancel_provisioning_date = sysdate
1885 where order_id = p_sdp_order_id;
1886
1887 commit;
1888
1889 Abort_Order_Workflows(
1890 p_sdp_order_id,
1891 return_code,
1892 error_description);
1893 end if;
1894
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 return_code := -191266;
1898 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1899 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
1900 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1901 error_description := FND_MESSAGE.GET;
1902 END Remove_Order_From_ProcessorQ;
1903
1904
1905 --
1906 -- Private API which will remove a workitem from queue
1907 --
1908 PROCEDURE Remove_WI_From_Q(
1909 p_wi_instance_id in number,
1910 p_msg_id in raw,
1911 p_caller_name in varchar2,
1912 p_state in varchar2,
1913 return_code OUT NOCOPY number,
1914 error_description OUT NOCOPY varchar2)
1915 IS
1916 lv_id number;
1917 lv_lock varchar2(1) := 'Y';
1918 lv_state varchar2(100);
1919 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
1920 lv_lock_status varchar2(1);
1921 lv_error varchar2(1000);
1922
1923 BEGIN
1924 return_code := 0;
1925 if p_state = 'IN PROGRESS' THEN
1926
1927 Lock_and_Remove_Msg(
1928 p_msg_id => p_msg_id,
1929 p_queue_name => 'XDP_WORKITEM_QUEUE',
1930 x_user_data =>lv_user_data,
1931 x_lock_status => lv_lock_status,
1932 x_error => lv_error);
1933
1934 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
1935 -- Great, we had lock and remove the WI from queue
1936 lv_lock := 'N';
1937
1938 update XDP_FULFILL_WORKLIST
1939 set last_updated_by = FND_GLOBAL.USER_ID,
1940 last_update_date = sysdate,
1941 last_update_login = FND_GLOBAL.LOGIN_ID,
1942 status_code = 'CANCELED',
1943 canceled_by = p_caller_name,
1944 cancel_provisioning_date = sysdate
1945 where workitem_instance_id = p_wi_instance_id;
1946
1947 commit;
1948 return;
1949 ELSE
1950 -- Now we need to the lock the worklist table
1951 -- to get the most current state of the WI
1952 lv_lock := 'Y';
1953 END IF;
1954 end if;
1955
1956 if lv_lock = 'Y' THEN
1957 savepoint lv_wi_tag;
1958
1959 SELECT status_code
1960 INTO lv_state
1961 FROM xdp_fulfill_worklist
1965 if lv_state in ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED') THEN
1962 WHERE workitem_instance_id = p_wi_instance_id
1963 FOR UPDATE of status_code;
1964
1966 rollback to lv_wi_tag;
1967 return;
1968 else
1969 update XDP_FULFILL_WORKLIST
1970 set last_updated_by = FND_GLOBAL.USER_ID,
1971 last_update_date = sysdate,
1972 last_update_login = FND_GLOBAL.LOGIN_ID,
1973 status_code = 'CANCELED',
1974 canceled_by = p_caller_name,
1975 cancel_provisioning_date = sysdate
1976 where workitem_instance_id = p_wi_instance_id;
1977 commit;
1978 end if;
1979 end if;
1980
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 return_code := -191266;
1984 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1985 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
1986 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1987 error_description := FND_MESSAGE.GET;
1988 END Remove_WI_From_Q;
1989
1990
1991 --
1992 -- Private API which will remove an FA from queue
1993 --
1994 PROCEDURE Remove_FA_From_Q(
1995 p_fa_instance_id in number,
1996 p_msg_id in raw,
1997 p_caller_name in varchar2,
1998 p_state in varchar2,
1999 return_code OUT NOCOPY number,
2000 error_description OUT NOCOPY varchar2)
2001 IS
2002 lv_id number;
2003 lv_in_fa_q varchar2(1) := 'Y';
2004 lv_item_type varchar2(80);
2005 lv_item_key varchar2(300);
2006 lv_state varchar2(100);
2007 lv_lock varchar2(1) := 'Y';
2008 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
2009 lv_lock_status varchar2(1);
2010 lv_error varchar2(1000);
2011 BEGIN
2012
2013 return_code := 0;
2014 if p_state = 'IN PROGRESS' THEN
2015 Lock_and_Remove_Msg(
2016 p_msg_id => p_msg_id,
2017 p_queue_name => 'XDP_FA_QUEUE',
2018 x_user_data =>lv_user_data,
2019 x_lock_status => lv_lock_status,
2020 x_error => lv_error);
2021
2022 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
2023 -- Great, we had lock and remove the FA from queue
2024 lv_lock := 'N';
2025
2026 update xdp_fa_runtime_list
2027 set last_updated_by = FND_GLOBAL.USER_ID,
2028 last_update_date = sysdate,
2029 last_update_login = FND_GLOBAL.LOGIN_ID,
2030 status_code = 'CANCELED',
2031 canceled_by = p_caller_name,
2032 cancel_provisioning_date = sysdate
2033 where fa_instance_id = p_fa_instance_id;
2034 commit;
2035 return;
2036 ELSE
2037 lv_in_fa_q := 'N';
2038 lv_lock := 'Y';
2039 END IF;
2040 END IF;
2041
2042 if lv_lock = 'Y' THEN
2043 savepoint lv_fa_tag;
2044
2045 select status_code,
2046 wf_item_type,
2047 wf_item_key
2048 into lv_state,
2049 lv_item_type,
2050 lv_item_key
2051 from xdp_fa_runtime_list
2052 where fa_instance_id = p_fa_instance_id
2053 for update of status_code;
2054
2055 if lv_state in ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED') THEN
2056 rollback to lv_fa_tag;
2057 return;
2058 else
2059 update xdp_fa_runtime_list
2060 set last_updated_by = FND_GLOBAL.USER_ID,
2061 last_update_date = sysdate,
2062 last_update_login = FND_GLOBAL.LOGIN_ID,
2063 status_code = 'CANCELED',
2064 canceled_by = p_caller_name,
2065 cancel_provisioning_date = sysdate
2066 where fa_instance_id = p_fa_instance_id;
2067 commit;
2068 end if;
2069 end if;
2070
2071 -- At this point the FA process should see the cancel state
2072 -- and abort accordingly
2073 -- As part of the clean up, we also try to see
2074 -- if the fa is in the adapter job queue or not
2075 IF lv_in_fa_q = 'N' THEN
2076 savepoint lv_job_tag;
2077 begin
2078 select job_id into lv_id
2079 from xdp_adapter_job_queue
2080 where
2081 wf_item_type = lv_item_type and
2082 wf_item_key = lv_item_key
2083 for update nowait ;
2084 delete from xdp_adapter_job_queue
2085 where job_id = lv_id ;
2086 commit;
2087 exception
2088 when resource_busy or no_data_found then
2089 -- this means the fa process is now running.
2090 -- Since we already updated the state, we will
2091 -- do nothing and let the process to handle
2092 -- itself.
2093 rollback to lv_job_tag;
2094 null;
2095 end;
2096 END IF;
2097
2098
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 return_code := -191266;
2102 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2103 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2104 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2105 error_description := FND_MESSAGE.GET;
2106 END Remove_FA_From_Q;
2107
2108 --
2109 -- Private API which will abort all workflow processes
2110 -- for the given order
2111 --
2112 PROCEDURE Abort_Order_Workflows(
2113 p_sdp_order_id in number,
2114 return_code OUT NOCOPY number,
2115 error_description OUT NOCOPY varchar2)
2119 item_type,
2116 IS
2117 CURSOR lc_wf_process(l_type varchar2,l_key varchar2) IS
2118 select level,
2120 item_key,
2121 DECODE(begin_date,NULL,'N','Y') is_active_flag,
2122 DECODE(end_date,NULL,'N','Y') is_completed_flag
2123 from wf_items_v
2124 start with
2125 item_type = l_type and item_key = l_key
2126 connect by parent_item_type = prior item_type and
2127 parent_item_key = prior item_key
2128 order by level desc;
2129
2130 lv_type varchar2(80);
2131 lv_key varchar2(300);
2132
2133 BEGIN
2134 return_code := 0;
2135
2136 select wf_item_type,wf_item_key
2137 into lv_type, lv_key
2138 from xdp_order_headers
2139 where order_id = p_sdp_order_id;
2140
2141
2142 FOR lv_wf_rec IN lc_wf_process(lv_type,lv_key) LOOP
2143 IF lv_wf_rec.is_active_flag = 'Y' AND
2144 lv_wf_rec.is_completed_flag = 'N' THEN
2145 begin
2146 wf_engine.abortProcess(
2147 itemtype => lv_wf_rec.item_type,
2148 itemkey => lv_wf_rec.item_key);
2149 commit;
2150 exception
2151 when others then
2152 null;
2153 end;
2154 END IF;
2155 END LOOP;
2156
2157 exception
2158 when others then
2159 return_code := -191266;
2160 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2161 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2162 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2163 error_description := FND_MESSAGE.GET;
2164
2165 END Abort_Order_Workflows;
2166
2167
2168 PROCEDURE Lock_and_Remove_Msg(
2169 p_msg_id in raw,
2170 p_queue_name in varchar2,
2171 p_remove_flag in varchar2 DEFAULT 'Y',
2172 x_user_data OUT NOCOPY SYSTEM.XDP_WF_CHANNELQ_TYPE,
2173 x_lock_status OUT NOCOPY varchar2,
2174 x_error OUT NOCOPY varchar2)
2175 IS
2176 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
2177 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
2178 lv_MsgID RAW(16);
2179 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
2180
2181 BEGIN
2182 if( p_msg_id is NULL ) then
2183 return;
2184 end if;
2185 savepoint lv_q_tag;
2186 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
2187 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
2188 lv_DequeueOptions.msgid := p_msg_id;
2189 lv_DequeueOptions.dequeue_mode := DBMS_AQ.LOCKED;
2190
2191 -- Set Dequeue time out to be 1 second
2192 lv_DequeueOptions.wait := 1;
2193 x_lock_status := G_LOCK_MSG_SUCCESS;
2194 /**
2195 BEGIN
2196 DBMS_AQ.DEQUEUE(
2197 queue_name => G_XDP_SCHEMA||'.'||p_queue_name,
2198 dequeue_options => lv_DequeueOptions,
2199 message_properties => lv_MessageProperties,
2200 payload => x_user_data,
2201 msgid => lv_MsgID);
2202 EXCEPTION
2203 WHEN e_QTimeOut or no_data_found Then
2204 x_lock_status := G_LOCK_MSG_FAIL;
2205 return;
2206 WHEN OTHERS THEN
2207 rollback to lv_q_tag;
2208 x_lock_status := G_LOCK_MSG_ERROR;
2209 x_error := SQLERRM;
2210 return;
2211 END;
2212 **/
2213 if NVL(p_remove_flag,'Y') = 'Y' THEN
2214 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
2215 DBMS_AQ.DEQUEUE(
2216 queue_name => G_XDP_SCHEMA||'.'||p_queue_name,
2217 dequeue_options => lv_DequeueOptions,
2218 message_properties => lv_MessageProperties,
2219 payload => lv_user_data,
2220 msgid => lv_MsgID);
2221 end if;
2222
2223 EXCEPTION
2224 WHEN OTHERS THEN
2225 rollback to lv_q_tag;
2226 x_lock_status := G_LOCK_MSG_ERROR;
2227 x_error := SQLERRM;
2228 END Lock_and_Remove_Msg;
2229
2230 --
2231 -- API for upstream ordering system to retrieve the order status
2232 -- information
2233 --
2234 PROCEDURE Get_Order_Status(
2235 P_SDP_ORDER_ID IN NUMBER,
2236 p_order_header OUT NOCOPY XDP_TYPES.ORDER_HEADER,
2237 P_Order_lines OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST,
2238 RETURN_CODE OUT NOCOPY NUMBER,
2239 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2240 IS
2241
2242 BEGIN
2243 return_code := 0;
2244 p_order_header := XDP_OA_UTIL.Get_Order_Header(p_sdp_order_id);
2245 p_order_lines := XDP_OA_UTIL.Get_Order_Lines(p_sdp_order_id);
2246 null;
2247 EXCEPTION
2248 WHEN NO_DATA_FOUND THEN
2249 return_code := SQLCODE;
2250 /*
2251 error_description := 'Error: Order ID '||p_sdp_order_id||' does not exist in SFM.';
2252 */
2253 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
2254 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
2255 error_description := FND_MESSAGE.GET;
2256 WHEN OTHERS THEN
2257 return_code := -191266;
2258 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2259 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2260 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2261 error_description := FND_MESSAGE.GET;
2262
2263 END Get_Order_Status;
2264
2265 /*
2266 * Overload Function
2267 * A light-weight API for upstream ordering system to retrieve
2268 * only the key order status
2272 P_SDP_ORDER_ID IN NUMBER,
2269 * information
2270 */
2271 PROCEDURE Get_Order_Status(
2273 x_status OUT NOCOPY VARCHAR2,
2274 x_state OUT NOCOPY VARCHAR2,
2275 x_completion_date OUT NOCOPY DATE,
2276 x_cancellation_date OUT NOCOPY DATE,
2277 x_RETURN_CODE OUT NOCOPY NUMBER,
2278 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2279 IS
2280
2281 BEGIN
2282 x_return_code := 0;
2283 select
2284 status_code,
2285 null,
2286 completion_date,
2287 cancel_provisioning_date
2288 into
2289 x_status,
2290 x_state ,
2291 x_completion_date,
2292 x_cancellation_date
2293 from
2294 XDP_ORDER_HEADERS
2295 WHERE
2296 order_id = p_sdp_order_id;
2297
2298 EXCEPTION
2299 WHEN NO_DATA_FOUND THEN
2300 x_return_code := SQLCODE;
2301 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
2302 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
2303 x_error_description := FND_MESSAGE.GET;
2304 WHEN OTHERS THEN
2305 x_return_code := -191266;
2306 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2307 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2308 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2309 x_error_description := FND_MESSAGE.GET;
2310 END Get_Order_Status;
2311
2312 /*
2313 * Overload Function
2314 * A light-weight API for upstream ordering system to retrieve
2315 * only the key order status
2316 * information
2317 */
2318 PROCEDURE Get_Order_Status(
2319 P_ORDER_NUMBER IN VARCHAR2,
2320 P_ORDER_VERSION IN VARCHAR2,
2321 x_status OUT NOCOPY VARCHAR2,
2322 x_state OUT NOCOPY VARCHAR2,
2323 x_completion_date OUT NOCOPY DATE,
2324 x_cancellation_date OUT NOCOPY DATE,
2325 x_RETURN_CODE OUT NOCOPY NUMBER,
2326 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2327 IS
2328
2329 BEGIN
2330 x_return_code := 0;
2331 select
2332 status_code,
2333 null,
2334 completion_date,
2335 cancel_provisioning_date
2336 into
2337 x_status,
2338 x_state,
2339 x_completion_date,
2340 x_cancellation_date
2341 from
2342 XDP_ORDER_HEADERS
2343 WHERE
2344 external_order_number = (p_order_number) and
2345 NVL(external_order_version,'-1') = NVL((p_order_version),'-1') ;
2346
2347
2348 EXCEPTION
2349 WHEN NO_DATA_FOUND THEN
2350 x_return_code := -191314;
2351 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_VERSION_NOTEXISTS');
2352 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
2353 FND_MESSAGE.SET_TOKEN('ORDER_VERSION', p_order_version);
2354 x_error_description := FND_MESSAGE.GET;
2355 WHEN OTHERS THEN
2356 x_return_code := -191266;
2357 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2358 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2359 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2360 x_error_description := FND_MESSAGE.GET;
2361 END Get_Order_Status;
2362
2363 /*
2364 * A light-weight API for upstream ordering system to retrieve
2365 * only the key line status
2366 * information
2367 */
2368 PROCEDURE Get_Line_Status(
2369 P_SDP_ORDER_ID IN NUMBER,
2370 p_line_number IN NUMBER,
2371 x_status OUT NOCOPY VARCHAR2,
2372 x_state OUT NOCOPY VARCHAR2,
2373 x_completion_date OUT NOCOPY DATE,
2374 x_cancellation_date OUT NOCOPY DATE,
2375 x_RETURN_CODE OUT NOCOPY NUMBER,
2376 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2377 IS
2378
2379 BEGIN
2380 x_return_code := 0;
2381 select
2382 status_code,
2383 null,
2384 completion_date,
2385 cancel_provisioning_date
2386 into
2387 x_status,
2388 x_state ,
2389 x_completion_date,
2390 x_cancellation_date
2391 from XDP_ORDER_LINE_ITEMS
2392 WHERE order_id = p_sdp_order_id and
2393 line_number = p_line_number ;
2394
2395
2396 EXCEPTION
2397 WHEN NO_DATA_FOUND THEN
2398 x_return_code := -191323;
2399 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_LINE_NOTEXISTS');
2400 FND_MESSAGE.SET_TOKEN('LINE_NUMBER', p_line_number);
2401 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_sdp_order_id);
2402 x_error_description := FND_MESSAGE.GET;
2403 WHEN OTHERS THEN
2404 x_return_code := -191266;
2405 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2406 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2407 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2408 x_error_description := FND_MESSAGE.GET;
2409 END Get_Line_Status;
2410
2411 /*
2412 * Overload Function
2413 * A light-weight API for upstream ordering system to retrieve
2414 * only the key line status
2415 * information
2416 */
2417 PROCEDURE Get_Line_Status(
2418 P_ORDER_NUMBER IN VARCHAR2,
2419 P_ORDER_VERSION IN VARCHAR2,
2420 p_line_number IN NUMBER,
2421 x_status OUT NOCOPY VARCHAR2,
2422 x_state OUT NOCOPY VARCHAR2,
2423 x_completion_date OUT NOCOPY DATE,
2424 x_cancellation_date OUT NOCOPY DATE,
2425 x_RETURN_CODE OUT NOCOPY NUMBER,
2429 BEGIN
2426 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2427 IS
2428
2430 x_return_code := 0;
2431 select
2432 olm.status_code,
2433 null,
2434 olm.completion_date,
2435 olm.cancel_provisioning_date
2436 into
2437 x_status,
2438 x_state ,
2439 x_completion_date,
2440 x_cancellation_date
2441 from
2442 XDP_ORDER_LINE_ITEMS olm,
2443 XDP_ORDER_HEADERS ohr
2444 WHERE
2445 olm.order_id = ohr.order_id and
2446 ohr.external_order_number = (p_order_number) and
2447 NVL(ohr.external_order_version,'-1') = NVL((p_order_version) ,'-1') and
2448 olm.line_number = p_line_number ;
2449
2450 EXCEPTION
2451 WHEN NO_DATA_FOUND THEN
2452 x_return_code := -191323;
2453 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_LINE_NOTEXISTS');
2454 FND_MESSAGE.SET_TOKEN('LINE_NUMBER', p_line_number);
2455 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
2456 x_error_description := FND_MESSAGE.GET;
2457 WHEN OTHERS THEN
2458 x_return_code := -191266;
2459 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2460 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2461 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2462 x_error_description := FND_MESSAGE.GET;
2463 END Get_Line_Status;
2464
2465 /*
2466 * Overload Function
2467 * A light-weight API for upstream ordering system to retrieve
2468 * only the key line status information
2469 * This API will return error if more than one order line
2470 * have the same line item name
2471 */
2472 PROCEDURE Get_Line_Status(
2473 P_ORDER_NUMBER IN VARCHAR2,
2474 P_ORDER_VERSION IN VARCHAR2,
2475 p_line_item_name IN VARCHAR2,
2476 x_status OUT NOCOPY VARCHAR2,
2477 x_state OUT NOCOPY VARCHAR2,
2478 x_completion_date OUT NOCOPY DATE,
2479 x_cancellation_date OUT NOCOPY DATE,
2480 x_RETURN_CODE OUT NOCOPY NUMBER,
2481 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2482 IS
2483
2484 lv_found varchar2(1) := 'N';
2485 CURSOR lc_line is
2486 select
2487 olm.status_code,
2488 olm.completion_date,
2489 olm.cancel_provisioning_date
2490 from
2491 XDP_ORDER_LINE_ITEMS olm,
2492 XDP_ORDER_HEADERS ohr
2493 WHERE
2494 olm.order_id = ohr.order_id and
2495 ohr.external_order_number = (p_order_number) and
2496 NVL(ohr.external_order_version,'-1') = NVL((p_order_version) ,'-1') and
2497 olm.line_item_name = p_line_item_name ;
2498
2499 BEGIN
2500 x_return_code := 0;
2501 FOR lv_line_rec in lc_line loop
2502 IF lv_found = 'N' THEN
2503 lv_found := 'Y';
2504 x_status := lv_line_rec.status_code;
2505 x_state := null ;
2506 x_completion_date := lv_line_rec.completion_date;
2507 x_cancellation_date := lv_line_rec.cancel_provisioning_date;
2508 ELSE
2509 x_status := NULL;
2510 x_state := null ;
2511 x_completion_date := NULL;
2512 x_cancellation_date := NULL;
2513 x_return_code := -191325;
2514 FND_MESSAGE.SET_NAME('XDP', 'XDP_LINE_NAME_MULTIMATCH');
2515 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
2516 FND_MESSAGE.SET_TOKEN('LINE_NAME', p_line_item_name);
2517 x_error_description := FND_MESSAGE.GET;
2518 return;
2519 END IF;
2520 END LOOP;
2521
2522 IF lv_found = 'N' THEN
2523 x_return_code := -191325;
2524 FND_MESSAGE.SET_NAME('XDP', 'XDP_LINE_NAME_NOTEXISTS');
2525 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
2526 FND_MESSAGE.SET_TOKEN('LINE_NAME', p_line_item_name);
2527 x_error_description := FND_MESSAGE.GET;
2528 return;
2529 END IF;
2530
2531 EXCEPTION
2532 WHEN OTHERS THEN
2533 x_return_code := -191266;
2534 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2535 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2536 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2537 x_error_description := FND_MESSAGE.GET;
2538 END Get_Line_Status;
2539
2540 --
2541 -- API for upstream ordering system to put a service activation
2542 -- order on hold
2543 --
2544 PROCEDURE Hold_Order(
2545 P_SDP_ORDER_ID IN NUMBER,
2546 RETURN_CODE OUT NOCOPY NUMBER,
2547 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2548 IS
2549 lv_state varchar2(40);
2550 lv_tmp number;
2551 resource_busy exception;
2552 pragma exception_init(resource_busy, -00054);
2553 lv_locked_q varchar2(80);
2554 BEGIN
2555 return_code := 0;
2556
2557
2558 EXCEPTION
2559 WHEN NO_DATA_FOUND THEN
2560 return_code := SQLCODE;
2561 /*
2562 error_description := 'Error: Order ID '||p_sdp_order_id||' does not exist in SFM.';
2563 */
2564 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
2565 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
2566 error_description := FND_MESSAGE.GET;
2567 WHEN OTHERS THEN
2568 return_code := -191266;
2569 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2570 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2571 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2572 error_description := FND_MESSAGE.GET;
2573 END Hold_Order;
2574
2575 --
2576 -- API for upstream ordering system to resume a service activation order
2577 -- which has been put on hold previously
2581 RETURN_CODE OUT NOCOPY NUMBER,
2578 --
2579 PROCEDURE Resume_Order(
2580 P_SDP_ORDER_ID IN NUMBER,
2582 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2583 IS
2584 lv_state varchar2(40);
2585 lv_tmp number;
2586 resource_busy exception;
2587 pragma exception_init(resource_busy, -00054);
2588 lv_locked_q varchar2(80);
2589
2590 BEGIN
2591 return_code := 0;
2592
2593
2594 EXCEPTION
2595 WHEN NO_DATA_FOUND THEN
2596 return_code := SQLCODE;
2597 /*
2598 error_description := 'Error: Order ID '||p_sdp_order_id||' does not exist in SFM.';
2599 */
2600 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
2601 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
2602 error_description := FND_MESSAGE.GET;
2603 WHEN OTHERS THEN
2604 return_code := -191266;
2605 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2606 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
2607 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2608 error_description := FND_MESSAGE.GET;
2609 END Resume_Order;
2610
2611 PROCEDURE Find_XDP_SCHEMA
2612 IS
2613 lv1 varchar2(80);
2614 lv2 varchar2(80);
2615 lv_schema varchar2(80);
2616 lv_ret BOOLEAN;
2617
2618 BEGIN
2619 lv_ret := FND_INSTALLATION.get_app_info(
2620 'XDP',
2621 lv1,
2622 lv2,
2623 lv_schema);
2624 G_XDP_SCHEMA := NVL(lv_schema,'XDP');
2625
2626 EXCEPTION
2627 WHEN OTHERS THEN
2628 G_XDP_SCHEMA := 'XDP';
2629 END Find_XDP_SCHEMA;
2630
2631 /*
2632 The following APIs are developed as part of integration with Oracle Sales for Comms.
2633 12/06/2000
2634 By Anping Wang
2635 */
2636
2637 PROCEDURE Get_Order_Param_Value(
2638 p_order_id IN NUMBER,
2639 p_parameter_name IN VARCHAR2,
2640 x_parameter_value OUT NOCOPY VARCHAR2,
2641 x_RETURN_CODE OUT NOCOPY NUMBER,
2642 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
2643 )
2644 IS
2645 BEGIN
2646 x_parameter_value := XDP_ENGINE.Get_Order_Param_Value(p_order_id,p_parameter_name);
2647 x_RETURN_CODE := 0;
2648 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 x_return_code := -191266;
2652 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2653 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Get_Order_Param_Value');
2654 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2655 x_error_description := FND_MESSAGE.GET;
2656 END Get_Order_Param_Value;
2657
2658
2659
2660 FUNCTION Get_Order_Param_List(
2661 p_order_id IN NUMBER,
2662 x_RETURN_CODE OUT NOCOPY NUMBER,
2663 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
2664 ) RETURN XDP_ENGINE.PARAMETER_LIST
2665 IS
2666 BEGIN
2667 x_return_code := 0;
2668 x_error_description := FND_API.G_RET_STS_SUCCESS;
2669 RETURN XDP_ENGINE.Get_Order_Param_List(p_order_id);
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 x_return_code := -191266;
2673 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2674 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Get_Order_Param_List');
2675 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2676 x_error_description := FND_MESSAGE.GET;
2677 END Get_Order_Param_List;
2678
2679 PROCEDURE Get_Line_Param_Value(
2680 p_order_id IN NUMBER,
2681 p_line_number IN VARCHAR2,
2682 p_parameter_name IN VARCHAR2,
2683 x_parameter_value OUT NOCOPY VARCHAR2,
2684 x_RETURN_CODE OUT NOCOPY NUMBER,
2685 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
2686 ) IS
2687 l_line_item_id Number;
2688 BEGIN
2689
2690 SELECT line_item_id
2691 INTO l_line_item_id
2692 FROM xdp_order_line_items
2693 WHERE order_id = p_order_id
2694 AND NVL(LINE_NUMBER,0) = NVL(p_line_number,0);
2695
2696 x_parameter_value := XDP_ENGINE.Get_Line_Param_Value(l_line_item_id,p_parameter_name);
2697 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
2698 x_return_code := 0;
2699 EXCEPTION
2700 WHEN OTHERS THEN
2701 x_return_code := -191266;
2702 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2703 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Get_Line_Param_Value');
2704 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2705 x_error_description := FND_MESSAGE.GET;
2706 END Get_Line_Param_Value;
2707
2708 PROCEDURE Get_Ord_Fulfillment_Status(
2709 p_order_id IN VARCHAR2,
2710 x_fulfillment_status OUT NOCOPY VARCHAR2,
2711 x_fulfillment_result OUT NOCOPY VARCHAR2,
2712 x_RETURN_CODE OUT NOCOPY NUMBER,
2713 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
2714 ) IS
2715 BEGIN
2716 BEGIN
2717 x_fulfillment_status := UPPER(XDP_ENGINE.Get_Order_Param_Value(p_order_id,'FULFILLMENT_STATUS'));
2718 EXCEPTION
2719 WHEN NO_DATA_FOUND THEN
2720 x_fulfillment_status := UPPER('Success');
2721 END;
2722 BEGIN
2723 x_fulfillment_result := XDP_ENGINE.Get_Order_Param_Value(p_order_id,'FULFILLMENT_RESULT');
2724 EXCEPTION
2725 WHEN NO_DATA_FOUND THEN
2726 x_fulfillment_result := '';
2727 END;
2728 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
2729 x_return_code := 0;
2730 EXCEPTION
2731 WHEN OTHERS THEN
2732 x_return_code := -191266;
2733 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2737 END Get_Ord_Fulfillment_Status;
2734 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Get_Ord_Fulfillment_Status');
2735 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2736 x_error_description := FND_MESSAGE.GET;
2738
2739 PROCEDURE Set_Ord_Fulfillment_Status(
2740 p_order_id IN NUMBER,
2741 p_fulfillment_status IN VARCHAR2 DEFAULT NULL,
2742 p_fulfillment_result IN VARCHAR2 DEFAULT NULL,
2743 x_RETURN_CODE OUT NOCOPY NUMBER,
2744 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
2745 )
2746 IS
2747 BEGIN
2748 IF (p_fulfillment_status IS NOT NULL) THEN
2749 XDP_ENGINE.Set_Order_Param_Value(p_order_id,'FULFILLMENT_STATUS',p_fulfillment_status);
2750 END IF;
2751
2752 IF (p_fulfillment_result IS NOT NULL) THEN
2753 XDP_ENGINE.Set_Order_Param_Value(p_order_id,'FULFILLMENT_RESULT',p_fulfillment_result);
2754 END IF;
2755 x_RETURN_CODE := 0;
2756 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
2757 EXCEPTION
2758 WHEN OTHERS THEN
2759 x_return_code := -191266;
2760 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2761 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Set_Ord_Fulfillment_Status');
2762 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2763 x_error_description := FND_MESSAGE.GET;
2764 END Set_Ord_Fulfillment_Status;
2765
2766
2767 /*
2768 From here on are the procedures used to convert the old process order api to
2769 new process order api
2770 */
2771
2772 FUNCTION TO_NEW_ORDER_HEADER(p_order_header IN XDP_TYPES.ORDER_HEADER)
2773 RETURN XDP_TYPES.SERVICE_ORDER_HEADER
2774 IS
2775 lv_order_header XDP_TYPES.SERVICE_ORDER_HEADER;
2776 BEGIN
2777 lv_order_header.order_number := p_order_header.order_number;
2778 lv_order_header.order_version := p_order_header.order_version;
2779 lv_order_header.required_fulfillment_date := p_order_header.provisioning_date;
2780 lv_order_header.priority := p_order_header.priority;
2781 lv_order_header.jeopardy_enabled_flag := p_order_header.jeopardy_enabled_flag;
2782 lv_order_header.execution_mode := 'ASYNC';
2783 lv_order_header.account_number := NULL;
2784 lv_order_header.cust_account_id :=NULL;
2785 lv_order_header.due_date := p_order_header.due_date;
2786 lv_order_header.customer_required_date := p_order_header.customer_required_date;
2787 lv_order_header.customer_name := p_order_header.customer_name;
2788 lv_order_header.customer_id := p_order_header.customer_id;
2789 lv_order_header.telephone_number := p_order_header.telephone_number;
2790
2791 lv_order_header.order_type := p_order_header.order_type;
2792
2793 lv_order_header.order_source := p_order_header.order_source;
2794 lv_order_header.org_id:= p_order_header.org_id;
2795
2796 lv_order_header.related_order_id := p_order_header.related_order_id;
2797 lv_order_header.previous_order_id := p_order_header.previous_order_id;
2798 lv_order_header.next_order_id := p_order_header.next_order_id;
2799 lv_order_header.order_ref_name := p_order_header.order_ref_name;
2800 lv_order_header.order_ref_value := p_order_header.order_ref_value;
2801 lv_order_header.order_comments := NULL;
2802 lv_order_header.order_id := p_order_header.sdp_order_id;
2803 lv_order_header.order_status:= p_order_header.order_status;
2804 lv_order_header.fulfillment_status := NULL;
2805 lv_order_header.fulfillment_result := NULL;
2806 lv_order_header.completion_date := NULL;
2807 lv_order_header.actual_fulfillment_date := NULL;
2808
2809 lv_order_header.attribute_category := NULL;
2810 lv_order_header.attribute1 := NULL;
2811 lv_order_header.attribute2 := NULL;
2812 lv_order_header.attribute3 := NULL;
2813 lv_order_header.attribute4 := NULL;
2814 lv_order_header.attribute5 := NULL;
2815 lv_order_header.attribute6 := NULL;
2816 lv_order_header.attribute7 := NULL;
2817 lv_order_header.attribute8 := NULL;
2818 lv_order_header.attribute9 := NULL;
2819 lv_order_header.attribute10 := NULL;
2820 lv_order_header.attribute11 := NULL;
2821 lv_order_header.attribute12 := NULL;
2822 lv_order_header.attribute13 := NULL;
2823 lv_order_header.attribute14 := NULL;
2824 lv_order_header.attribute15 := NULL;
2825 lv_order_header.attribute16 := NULL;
2826 lv_order_header.attribute17 := NULL;
2827 lv_order_header.attribute18 := NULL;
2828 lv_order_header.attribute19 := NULL;
2829 lv_order_header.attribute20 := NULL;
2830 RETURN lv_order_header;
2831
2832 END TO_NEW_ORDER_HEADER;
2833
2834 PROCEDURE TO_NEW_ORDER_PARAM_LIST(
2835 p_order_parameter IN XDP_TYPES.ORDER_PARAMETER_LIST,
2836 x_order_param_list IN OUT NOCOPY XDP_TYPES.SERVICE_ORDER_PARAM_LIST)
2837 IS
2838 lv_param_index BINARY_INTEGER;
2839 BEGIN
2840 IF p_order_parameter.COUNT > 0 THEN
2841 lv_param_index := p_order_parameter.first;
2842 LOOP
2843 x_order_param_list(lv_param_index).parameter_name := p_order_parameter(lv_param_index).parameter_name;
2844 x_order_param_list(lv_param_index).parameter_value := p_order_parameter(lv_param_index).parameter_value;
2845 EXIT WHEN lv_param_index = p_order_parameter.last;
2846 lv_param_index := p_order_parameter.next(lv_param_index);
2847 END LOOP;
2848 END IF;
2849 END;
2850
2851 FUNCTION TO_NEW_LINE_ITEM(
2852 p_line_item IN XDP_TYPES.LINE_ITEM,
2853 p_default_action_code IN VARCHAR2)
2854 RETURN XDP_TYPES.SERVICE_LINE_ITEM
2855 IS
2859 x_order_line_item.line_source := NULL; -- MAYA
2856 x_order_line_item XDP_TYPES.SERVICE_LINE_ITEM;
2857 BEGIN
2858 x_order_line_item.line_number := p_line_item.line_number;
2860
2861 -- Inventory item id will be one of the following values
2862 -- service_id
2863 -- package_id
2864 -- These fields are mutual exclusive in the old strucuture.
2865
2866 x_order_line_item.inventory_item_id := p_line_item.service_id;
2867 x_order_line_item.inventory_item_id :=
2868 nvl(x_order_line_item.inventory_item_id,p_line_item.package_id);
2869 -- x_order_line_item.inventory_item_id :=
2870 -- nvl(x_order_line_item.inventory_item_id,p_line_item.workitem_id);
2871
2872 /** Workitem_id has been added to the new structure **/
2873 x_order_line_item.workitem_id := p_line_item.workitem_id;
2874 x_order_line_item.service_item_name := p_line_item.line_item_name;
2875
2876 x_order_line_item.version := p_line_item.version;
2877
2878 --
2879 -- If it is an workitem, action code will be NULL
2880 -- else action code will be defaulted to default_action_code if
2881 -- p_line_item.action is NULL
2882 --
2883
2884 IF UPPER(p_line_item.is_workitem_flag) = 'Y' THEN
2885 x_order_line_item.action_code := NULL;
2886 ELSE
2887 x_order_line_item.action_code := nvl(p_line_item.action,p_default_action_code);
2888 END IF;
2889
2890 x_order_line_item.organization_code := NULL;
2891 x_order_line_item.organization_id := NULL;
2892 x_order_line_item.site_use_id := NULL;
2893 x_order_line_item.ib_source := 'NONE';
2894 x_order_line_item.ib_source_id := NULL;
2895 x_order_line_item.required_fulfillment_date := p_line_item.provisioning_date;
2896 x_order_line_item.fulfillment_required_flag := p_line_item.provisioning_required_flag;
2897 x_order_line_item.fulfillment_sequence := p_line_item.provisioning_sequence;
2898 x_order_line_item.bundle_id := p_line_item.bundle_id;
2899 x_order_line_item.bundle_sequence := p_line_item.bundle_sequence;
2900 x_order_line_item.priority := p_line_item.priority;
2901 x_order_line_item.due_date := p_line_item.due_date;
2902 x_order_line_item.jeopardy_enabled_flag := p_line_item.jeopardy_enabled_flag;
2903 x_order_line_item.customer_required_date := p_line_item.customer_required_date;
2904 x_order_line_item.starting_number := p_line_item.starting_number;
2905 x_order_line_item.ending_number := p_line_item.ending_number;
2906 x_order_line_item.line_item_id := p_line_item.line_item_id;
2907 x_order_line_item.line_status := p_line_item.line_status;
2908 x_order_line_item.completion_date := p_line_item.completion_date;
2909 x_order_line_item.actual_fulfillment_date := NULL;
2910 x_order_line_item.is_package_flag := 'N';
2911 x_order_line_item.parent_line_number := NULL;
2912 x_order_line_item.attribute_category := NULL;
2913 x_order_line_item.attribute1 := NULL;
2914 x_order_line_item.attribute2 := NULL;
2915 x_order_line_item.attribute3 := NULL;
2916 x_order_line_item.attribute4 := NULL;
2917 x_order_line_item.attribute5 := NULL;
2918 x_order_line_item.attribute6 := NULL;
2919 x_order_line_item.attribute7 := NULL;
2920 x_order_line_item.attribute8 := NULL;
2921 x_order_line_item.attribute9 := NULL;
2922 x_order_line_item.attribute10 := NULL;
2923 x_order_line_item.attribute11 := NULL;
2924 x_order_line_item.attribute12 := NULL;
2925 x_order_line_item.attribute13 := NULL;
2926 x_order_line_item.attribute14 := NULL;
2927 x_order_line_item.attribute15 := NULL;
2928 x_order_line_item.attribute16 := NULL;
2929 x_order_line_item.attribute17 := NULL;
2930 x_order_line_item.attribute18 := NULL;
2931 x_order_line_item.attribute19 := NULL;
2932 x_order_line_item.attribute20 := NULL;
2933 RETURN x_order_line_item;
2934 END;
2935
2936 PROCEDURE TO_NEW_LINE_LIST(
2937 p_line_list IN XDP_TYPES.ORDER_LINE_LIST,
2938 x_order_line_list OUT NOCOPY XDP_TYPES.SERVICE_ORDER_LINE_LIST,
2939 p_default_action_code IN VARCHAR2)
2940 IS
2941 lv_param_index BINARY_INTEGER;
2942 BEGIN
2943 IF p_line_list.COUNT > 0 THEN
2944 lv_param_index := p_line_list.first;
2945 LOOP
2946 x_order_line_list(lv_param_index) := TO_NEW_LINE_ITEM(p_line_list(lv_param_index),p_default_action_code);
2947 EXIT WHEN lv_param_index = p_line_list.last;
2948 lv_param_index := p_line_list.next(lv_param_index);
2949 END LOOP;
2950 END IF;
2951 END;
2952
2953 PROCEDURE TO_NEW_LINE_PARAM_LIST(
2954 p_line_parameter_list IN XDP_TYPES.LINE_PARAM_LIST,
2955 x_line_param_list OUT NOCOPY XDP_TYPES.SERVICE_LINE_PARAM_LIST)
2956 IS
2957 lv_param_index BINARY_INTEGER;
2958 BEGIN
2959 IF p_line_parameter_list.COUNT > 0 THEN
2960 lv_param_index := p_line_parameter_list.first;
2961 LOOP
2962 x_line_param_list(lv_param_index).line_number := p_line_parameter_list(lv_param_index).line_number;
2966
2963 x_line_param_list(lv_param_index).parameter_name := p_line_parameter_list(lv_param_index).parameter_name;
2964 x_line_param_list(lv_param_index).parameter_value := p_line_parameter_list(lv_param_index).parameter_value;
2965 x_line_param_list(lv_param_index).parameter_ref_value:= p_line_parameter_list(lv_param_index).parameter_ref_value;
2967 EXIT WHEN lv_param_index = p_line_parameter_list.last;
2968 lv_param_index := p_line_parameter_list.next(lv_param_index);
2969 END LOOP;
2970 END IF;
2971 END;
2972
2973 /* This is the wrapper over the old API */
2974
2975 PROCEDURE Process_Old_Order(
2976 P_ORDER_HEADER IN XDP_TYPES.ORDER_HEADER,
2977 P_ORDER_PARAMETER IN XDP_TYPES.ORDER_PARAMETER_LIST,
2978 P_ORDER_LINE_LIST IN XDP_TYPES.ORDER_LINE_LIST,
2979 P_LINE_PARAMETER_LIST IN XDP_TYPES.LINE_PARAM_LIST,
2980 P_execution_mode IN VARCHAR2 DEFAULT 'ASYNC',
2981 SDP_ORDER_ID OUT NOCOPY NUMBER,
2982 RETURN_CODE IN OUT NOCOPY NUMBER,
2983 ERROR_DESCRIPTION IN OUT NOCOPY VARCHAR2)
2984 IS
2985 lv_order_header XDP_TYPES.SERVICE_ORDER_HEADER;
2986 lv_order_param_list XDP_TYPES.SERVICE_ORDER_PARAM_LIST;
2987 lv_order_line_list XDP_TYPES.SERVICE_ORDER_LINE_LIST;
2988 lv_line_param_list XDP_TYPES.SERVICE_LINE_PARAM_LIST;
2989 BEGIN
2990 lv_order_header := TO_NEW_ORDER_HEADER(P_ORDER_HEADER);
2991 TO_NEW_ORDER_PARAM_LIST(P_ORDER_PARAMETER,lv_order_param_list);
2992 TO_NEW_LINE_LIST(P_ORDER_LINE_LIST,lv_order_line_list,p_order_header.order_action);
2993 TO_NEW_LINE_PARAM_LIST(P_LINE_PARAMETER_LIST,lv_line_param_list);
2994 lv_order_header.execution_mode := p_execution_mode;
2995 /*
2996 In the old structure, the order header has an action_code field which was to be used
2997 as default action code for its line itmes if the item does not
2998 have action code itself. Bearing in mind that now action code is used for
2999 two folds of meaning. When it is null, the internal API will think this
3000 call will be used
3001 */
3002
3003 XDP_ORDER.Process_Order(
3004 lv_order_header,
3005 lv_order_param_list,
3006 lv_order_line_list,
3007 lv_line_param_list,
3008 SDP_ORDER_ID,
3009 RETURN_CODE ,
3010 ERROR_DESCRIPTION );
3011
3012 EXCEPTION
3013 WHEN OTHERS THEN
3014 return_code := -191266;
3015 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3016 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.PROCESS_OLD_ORDER');
3017 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3018 error_description := FND_MESSAGE.GET;
3019 END Process_Old_Order;
3020
3021 /*
3022 The following procedures are used to retreive order information.
3023
3024
3025 This is a private procedure used by private API Get_Order_Status in
3026 this package.
3027 */
3028
3029 PROCEDURE Get_Order_Status(
3030 p_order_id IN NUMBER,
3031 x_order_status OUT NOCOPY XDP_TYPES.SERVICE_ORDER_STATUS,
3032 x_return_code OUT NOCOPY NUMBER,
3033 x_error_description OUT NOCOPY VARCHAR2)
3034 IS
3035 CURSOR lc_order_param IS
3036 SELECT Order_parameter_name,
3037 order_parameter_value
3038 FROM xdp_order_parameters
3039 WHERE order_id = p_order_id;
3040
3041 lv_param_count NUMBER := 0;
3042 BEGIN
3043 SELECT order_id,
3044 status_code,
3045 external_order_number,
3046 external_order_version,
3047 actual_provisioning_date,
3048 completion_date
3049 INTO x_order_status.order_id,
3050 x_order_status.order_status,
3051 x_order_status.order_number,
3052 x_order_status.order_version,
3053 x_order_status.actual_fulfillment_date,
3054 x_order_status.completion_date
3055 FROM XDP_ORDER_HEADERS
3056 WHERE order_id = p_order_id;
3057
3058 BEGIN
3059 x_order_status.fulfillment_status := XDP_ENGINE.Get_Order_Param_Value(p_order_id,'FULFILLMENT_STATUS');
3060 EXCEPTION
3061 WHEN NO_DATA_FOUND THEN
3062 x_order_status.fulfillment_status := NULL;
3063 END;
3064 BEGIN
3065 x_order_status.fulfillment_result := XDP_ENGINE.Get_Order_Param_Value(p_order_id,'FULFILLMENT_RESULT');
3066 EXCEPTION
3067 WHEN NO_DATA_FOUND THEN
3068 x_order_status.fulfillment_result := NULL;
3069 END;
3070 EXCEPTION
3071 WHEN NO_DATA_FOUND THEN
3072 x_return_code := SQLCODE;
3073 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
3074 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_order_id);
3075 x_error_description := FND_MESSAGE.GET;
3076 END Get_Order_Status;
3077
3078 /*
3079 This is a private procedure used by Get_Order_Details in this package
3080 */
3081
3082 PROCEDURE Get_Order_Header(
3083 p_order_id IN NUMBER,
3084 x_order_header OUT NOCOPY XDP_TYPES.SERVICE_ORDER_HEADER,
3085 x_order_param_list OUT NOCOPY XDP_TYPES.SERVICE_ORDER_PARAM_LIST,
3086 x_return_code OUT NOCOPY NUMBER,
3087 x_error_description OUT NOCOPY VARCHAR2)
3088 IS
3089 CURSOR lc_order_param IS
3090 select order_parameter_name,order_parameter_value
3091 from xdp_order_parameters
3092 where order_id = p_order_id;
3093 lv_param_count NUMBER := 0;
3094 BEGIN
3095 SELECT
3096 external_order_number,
3097 external_order_version,
3098 provisioning_date,
3099 priority,
3103 cust_account_id,
3100 jeopardy_enabled_flag,
3101 -- execution_mode,
3102 -- account_number,
3104 due_date,
3105 customer_required_date,
3106 customer_name,
3107 customer_id,
3108 telephone_number,
3109 order_type,
3110 order_source,
3111 org_id,
3112 related_order_id,
3113 previous_order_id,
3114 next_order_id,
3115 order_ref_name,
3116 order_ref_value,
3117 order_comment,
3118 order_id,
3119 status_code,
3120 completion_date,
3121 actual_provisioning_date,
3122 attribute_category,
3123 attribute1,
3124 attribute2,
3125 attribute3,
3126 attribute4,
3127 attribute5,
3128 attribute6,
3129 attribute7,
3130 attribute8,
3131 attribute9,
3132 attribute10,
3133 attribute11,
3134 attribute12,
3135 attribute13,
3136 attribute14,
3137 attribute15,
3138 attribute16,
3139 attribute17,
3140 attribute18,
3141 attribute19,
3142 attribute20
3143 INTO
3144 x_order_header.order_number,
3145 x_order_header.order_version,
3146 x_order_header.required_fulfillment_date,
3147 x_order_header.priority,
3148 x_order_header.jeopardy_enabled_flag,
3149 -- x_order_header.execution_mode,
3150 -- x_order_header.account_number,
3151 x_order_header.cust_account_id,
3152 x_order_header.due_date,
3153 x_order_header.customer_required_date,
3154 x_order_header.customer_name,
3155 x_order_header.customer_id,
3156 x_order_header.telephone_number,
3157 x_order_header.order_type,
3158 x_order_header.order_source,
3159 x_order_header.org_id,
3160 x_order_header.related_order_id,
3161 x_order_header.previous_order_id,
3162 x_order_header.next_order_id,
3163 x_order_header.order_ref_name,
3164 x_order_header.order_ref_value,
3165 x_order_header.order_comments,
3166 x_order_header.order_id,
3167 x_order_header.order_status,
3168 x_order_header.completion_date,
3169 x_order_header.actual_fulfillment_date,
3170 x_order_header.attribute_category,
3171 x_order_header.attribute1,
3172 x_order_header.attribute2,
3173 x_order_header.attribute3,
3174 x_order_header.attribute4,
3175 x_order_header.attribute5,
3176 x_order_header.attribute6,
3177 x_order_header.attribute7,
3178 x_order_header.attribute8,
3179 x_order_header.attribute9,
3180 x_order_header.attribute10,
3181 x_order_header.attribute11,
3182 x_order_header.attribute12,
3183 x_order_header.attribute13,
3184 x_order_header.attribute14,
3185 x_order_header.attribute15,
3186 x_order_header.attribute16,
3187 x_order_header.attribute17,
3188 x_order_header.attribute18,
3189 x_order_header.attribute19,
3190 x_order_header.attribute20
3191 FROM XDP_ORDER_HEADERS
3192 WHERE order_id = p_order_id;
3193 x_order_header.execution_mode := NULL;
3194 x_order_header.account_number:= NULL;
3195 x_order_header.fulfillment_status := NULL;
3196 x_order_header.fulfillment_result := NULL;
3197
3198 IF x_order_header.cust_account_id IS NOT NULL THEN
3199 SELECT account_number INTO x_order_header.account_number
3200 FROM HZ_CUST_ACCOUNTS WHERE cust_account_id = x_order_header.cust_account_id;
3201 END IF;
3202
3203 FOR lv_param_rec IN lc_order_param LOOP
3204 lv_param_count := lv_param_count + 1;
3205 x_order_param_list(lv_param_count).parameter_name := lv_param_rec.order_parameter_name;
3206 x_order_param_list(lv_param_count).parameter_value := lv_param_rec.order_parameter_value;
3207 IF (UPPER(lv_param_rec.order_parameter_name) = 'FULFILLMENT_STATUS') THEN
3208 x_order_header.fulfillment_status := lv_param_rec.order_parameter_value;
3209 END IF;
3210 IF (UPPER(lv_param_rec.order_parameter_name) = 'FULFILLMENT_RESULT') THEN
3211 x_order_header.fulfillment_result := lv_param_rec.order_parameter_value;
3212 END IF;
3213 END LOOP;
3214 EXCEPTION
3215 WHEN NO_DATA_FOUND THEN
3216 x_return_code := SQLCODE;
3217 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
3218 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_order_id);
3219 x_error_description := FND_MESSAGE.GET;
3220 END Get_Order_Header;
3221
3222 --
3223 -- This is a private api used by Get_Order_Details
3224 --
3225
3226 PROCEDURE Get_Order_Lines(
3227 p_order_id IN NUMBER,
3228 x_line_list OUT NOCOPY XDP_TYPES.SERVICE_ORDER_LINE_LIST,
3229 x_line_param_list OUT NOCOPY XDP_TYPES.SERVICE_LINE_PARAM_LIST)
3230 IS
3231 CURSOR lc_line IS
3232 SELECT
3233 line_number,
3234 line_source,
3235 inventory_item_id,
3236 line_item_name,
3237 version,
3238 line_item_action_code,
3239 organization_id,
3240 site_use_id,
3241 ib_source,
3242 ib_source_id,
3243 provisioning_date,
3247 bundle_sequence,
3244 provisioning_required_flag,
3245 line_sequence, -- not provisioning_sequence
3246 bundle_id,
3248 priority,
3249 due_date,
3250 jeopardy_enabled_flag,
3251 customer_required_date,
3252 starting_number,
3253 ending_number,
3254 line_item_id,
3255 status_code, -- not line_status
3256 completion_date,
3257 actual_provisioning_date,
3258 attribute_category,
3259 attribute1,
3260 attribute2,
3261 attribute3,
3262 attribute4,
3263 attribute5,
3264 attribute6,
3265 attribute7,
3266 attribute8,
3267 attribute9,
3268 attribute10,
3269 attribute11,
3270 attribute12,
3271 attribute13,
3272 attribute14,
3273 attribute15,
3274 attribute16,
3275 attribute17,
3276 attribute18,
3277 attribute19,
3278 attribute20
3279 FROM
3280 xdp_order_line_items
3281 WHERE
3282 order_id = p_order_id
3283 AND
3284 is_virtual_line_flag = 'N';
3285
3286 CURSOR lc_line_param(p_lineitem NUMBER) IS
3287 SELECT line_parameter_name,parameter_value,parameter_reference_value
3288 FROM xdp_order_lineitem_dets
3289 WHERE line_item_id = p_lineitem;
3290
3291 lv_count NUMBER := 0;
3292 lv_param_count NUMBER := 0;
3293 BEGIN
3294 FOR lv_line_rec IN lc_line LOOP
3295 lv_count := lv_count + 1;
3296
3297 x_line_list(lv_count).line_number := lv_line_rec.line_number;
3298 x_line_list(lv_count).line_source := lv_line_rec.line_source;
3299 x_line_list(lv_count).inventory_item_id := lv_line_rec.inventory_item_id;
3300 x_line_list(lv_count).service_item_name := lv_line_rec.line_item_name;
3301 x_line_list(lv_count).version := lv_line_rec.version;
3302 x_line_list(lv_count).action_code := lv_line_rec.line_item_action_code;
3303 x_line_list(lv_count).organization_code := null; -- lv_line_rec.organization_code;
3304 x_line_list(lv_count).organization_id := lv_line_rec.organization_id;
3305
3306 IF (lv_line_rec.organization_id IS NOT NULL) THEN
3307 SELECT organization_code into x_line_list(lv_count).organization_code
3308 FROM MTL_PARAMETERS WHERE organization_id = lv_line_rec.organization_id;
3309 END IF;
3310
3311 x_line_list(lv_count).site_use_id := lv_line_rec.site_use_id;
3312 x_line_list(lv_count).ib_source := lv_line_rec.ib_source;
3313 x_line_list(lv_count).ib_source_id := lv_line_rec.ib_source_id;
3314 x_line_list(lv_count).required_fulfillment_date := lv_line_rec.provisioning_date;
3315 x_line_list(lv_count).fulfillment_required_flag := lv_line_rec.provisioning_required_flag;
3316 x_line_list(lv_count).fulfillment_sequence := lv_line_rec.line_sequence;
3317 x_line_list(lv_count).bundle_id := lv_line_rec.bundle_id;
3318 x_line_list(lv_count).bundle_sequence := lv_line_rec.bundle_sequence;
3319 x_line_list(lv_count).priority := lv_line_rec.priority;
3320 x_line_list(lv_count).due_date := lv_line_rec.due_date;
3321 x_line_list(lv_count).jeopardy_enabled_flag := lv_line_rec.jeopardy_enabled_flag;
3322 x_line_list(lv_count).customer_required_date := lv_line_rec.customer_required_date;
3323 x_line_list(lv_count).starting_number := lv_line_rec.starting_number;
3324 x_line_list(lv_count).ending_number := lv_line_rec.ending_number;
3325 x_line_list(lv_count).line_item_id := lv_line_rec.line_item_id;
3326 x_line_list(lv_count).line_status := lv_line_rec.status_code;
3327 x_line_list(lv_count).completion_date := lv_line_rec.completion_date;
3328 x_line_list(lv_count).actual_fulfillment_date := lv_line_rec.actual_provisioning_date;
3329 x_line_list(lv_count).attribute_category := lv_line_rec.attribute_category;
3330 x_line_list(lv_count).attribute1 := lv_line_rec.attribute1;
3331 x_line_list(lv_count).attribute2 := lv_line_rec.attribute2;
3332 x_line_list(lv_count).attribute3 := lv_line_rec.attribute3;
3333 x_line_list(lv_count).attribute4 := lv_line_rec.attribute4;
3334 x_line_list(lv_count).attribute5 := lv_line_rec.attribute5;
3335 x_line_list(lv_count).attribute6 := lv_line_rec.attribute6;
3336 x_line_list(lv_count).attribute7 := lv_line_rec.attribute7;
3337 x_line_list(lv_count).attribute8 := lv_line_rec.attribute8;
3338 x_line_list(lv_count).attribute9 := lv_line_rec.attribute9;
3339 x_line_list(lv_count).attribute10 := lv_line_rec.attribute10;
3340 x_line_list(lv_count).attribute11 := lv_line_rec.attribute11;
3341 x_line_list(lv_count).attribute12 := lv_line_rec.attribute12;
3342 x_line_list(lv_count).attribute13 := lv_line_rec.attribute13;
3343 x_line_list(lv_count).attribute14 := lv_line_rec.attribute14;
3344 x_line_list(lv_count).attribute15 := lv_line_rec.attribute15;
3345 x_line_list(lv_count).attribute16 := lv_line_rec.attribute16;
3346 x_line_list(lv_count).attribute17 := lv_line_rec.attribute17;
3347 x_line_list(lv_count).attribute18 := lv_line_rec.attribute18;
3348 x_line_list(lv_count).attribute19 := lv_line_rec.attribute19;
3349 x_line_list(lv_count).attribute20 := lv_line_rec.attribute20;
3350
3351 FOR lv_param_rec IN lc_line_param(lv_line_rec.line_item_id) LOOP
3352 lv_param_count := lv_param_count + 1;
3356 x_line_param_list(lv_param_count).parameter_ref_value := lv_param_rec.parameter_reference_value;
3353 x_line_param_list(lv_param_count).line_number := lv_line_rec.line_number;
3354 x_line_param_list(lv_param_count).parameter_name := lv_param_rec.line_parameter_name;
3355 x_line_param_list(lv_param_count).parameter_value := lv_param_rec.parameter_value;
3357 END LOOP;
3358 END LOOP;
3359 END Get_Order_Lines;
3360 /*
3361 This is the private API for retrieving order status. It is used
3362 by public API to retrieve order status information.
3363 Data is stored in x_order_status
3364 */
3365 Procedure Get_Order_Status(
3366 p_order_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
3367 p_order_number IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
3368 p_order_version IN VARCHAR2 DEFAULT '1',
3369 x_order_status OUT NOCOPY XDP_TYPES.SERVICE_ORDER_STATUS,
3370 x_return_code OUT NOCOPY NUMBER,
3371 x_error_description OUT NOCOPY VARCHAR2)
3372 IS
3373 l_order_id NUMBER;
3374 BEGIN
3375 l_order_id := p_order_id;
3376
3377 IF l_order_id IS NULL THEN
3378 SELECT order_id into l_order_id
3379 FROM XDP_ORDER_HEADERS
3380 WHERE EXTERNAL_ORDER_NUMBER = (p_order_number)
3381 AND NVL(EXTERNAL_ORDER_VERSION,'-1') = NVL((p_order_version),'-1') ;
3382 END IF;
3383
3384 Get_Order_Status(l_order_id,x_order_status,x_return_code,x_error_description);
3385 EXCEPTION
3386 WHEN NO_DATA_FOUND THEN
3387 x_return_code := SQLCODE;
3388 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_VERSION_NOTEXISTS');
3389 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
3390 FND_MESSAGE.SET_TOKEN('ORDER_VERSION', p_order_version);
3391 x_error_description := FND_MESSAGE.GET;
3392 WHEN OTHERS THEN
3393 x_return_code := -191266;
3394 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3395 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.GET_ORDER_STATUS');
3396 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3397 x_error_description := FND_MESSAGE.GET;
3398 END Get_Order_Status;
3399
3400
3401 /*
3402 This is the internal API for geting order details. It is used
3403 by public API to retrieve order detail information.
3404 Data is stored in four data structures as defined in XDP_TYPES
3405 */
3406 Procedure Get_Order_Details(
3407 p_order_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
3408 p_order_number IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
3409 p_order_version IN VARCHAR2 DEFAULT '1',
3410 x_order_header OUT NOCOPY XDP_TYPES.SERVICE_ORDER_HEADER,
3411 x_order_param_list OUT NOCOPY XDP_TYPES.SERVICE_ORDER_PARAM_LIST,
3412 x_line_item_list OUT NOCOPY XDP_TYPES.SERVICE_ORDER_LINE_LIST,
3413 x_line_param_list OUT NOCOPY XDP_TYPES.SERVICE_LINE_PARAM_LIST,
3414 x_return_code OUT NOCOPY NUMBER,
3415 x_error_description OUT NOCOPY VARCHAR2)
3416 IS
3417 l_order_id NUMBER;
3418 BEGIN
3419 l_order_id := p_order_id;
3420
3421 IF l_order_id IS NULL THEN
3422 SELECT order_id into l_order_id
3423 FROM XDP_ORDER_HEADERS
3424 WHERE EXTERNAL_ORDER_NUMBER = (p_order_number)
3425 AND NVL(EXTERNAL_ORDER_VERSION,'-1') = NVL((p_order_version),'-1') ;
3426 END IF;
3427
3428 Get_Order_Header(l_order_id,x_order_header,x_order_param_list,x_return_code,x_error_description);
3429 Get_Order_Lines(l_order_id,x_line_item_list,x_line_param_list);
3430
3431 EXCEPTION
3432 WHEN NO_DATA_FOUND THEN
3433 x_return_code := SQLCODE;
3434 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_VERSION_NOTEXISTS');
3435 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', p_order_number);
3436 FND_MESSAGE.SET_TOKEN('ORDER_VERSION', p_order_version);
3437 x_error_description := FND_MESSAGE.GET;
3438 WHEN OTHERS THEN
3439 x_return_code := -191266;
3440 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3441 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.GET_ORDER_DETAILS');
3442 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3443 x_error_description := FND_MESSAGE.GET;
3444
3445 END Get_Order_Details;
3446
3447 --
3448 -- Private API which will remove an FA from queue
3449 --
3450
3451 PROCEDURE CANCEL_FA
3452 (p_order_id IN NUMBER,
3453 p_fa_instance_id IN NUMBER,
3454 p_msg_id IN RAW,
3455 p_caller_name IN VARCHAR2,
3456 p_fa_wf_item_type IN VARCHAR2,
3457 p_fa_wf_item_key IN VARCHAR2,
3458 p_status IN VARCHAR2,
3459 return_code OUT NOCOPY NUMBER,
3460 error_description OUT NOCOPY VARCHAR2)
3461 IS
3462 lv_id NUMBER;
3463 lv_in_fa_q VARCHAR2(1) := 'Y';
3464 lv_item_type VARCHAR2(80);
3465 lv_item_key VARCHAR2(300);
3466 lv_state VARCHAR2(100);
3467 lv_lock VARCHAR2(1) := 'Y';
3468 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
3469 lv_lock_status VARCHAR2(1);
3470 lv_error VARCHAR2(1000);
3471 lv_fa_instance_id NUMBER;
3472
3473 e_xdp_fa_state_success EXCEPTION ;
3474 e_xdp_fa_state_inprogress EXCEPTION ;
3475
3476 BEGIN
3477
3478 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3482 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3479 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3480 dbg_msg := ('Procedure XDP_INTERFACES.CANCEL_FA begins for FA instance : ' ||p_fa_instance_id);
3481 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3483 END IF;
3484 END IF;
3485 END IF;
3486
3487 return_code := 0;
3488 IF p_status = 'WAIT_FOR_RESOURCE' THEN
3489
3490 BEGIN
3491 SELECT fa_instance_id
3492 INTO lv_fa_instance_id
3493 FROM xdp_adapter_job_queue
3494 WHERE fa_instance_id = p_fa_instance_id
3495 AND wf_item_type = p_fa_wf_item_type
3496 AND wf_item_key = p_fa_wf_item_key
3497 FOR UPDATE NOWAIT;
3498
3499 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3500 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3501 dbg_msg := ('Aquired Lock Deleting From XDP_ADAPTER_JOB_QUEUE ');
3502 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3503 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3504 END IF;
3505 END IF;
3506 END IF;
3507
3508 DELETE from xdp_adapter_job_queue
3509 WHERE fa_instance_id = p_fa_instance_id
3510 AND wf_item_type = p_fa_wf_item_type
3511 AND wf_item_key = p_fa_wf_item_key;
3512
3513 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3514 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3515 dbg_msg := ('Deleted From XDP_ADAPTER_JOB_QUEUE Calling UPDATE_XDP_FA_INSTANCE_STATUS ');
3516 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3517 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3518 END IF;
3519 END IF;
3520 END IF;
3521
3522 UPDATE_XDP_FA_INSTANCE_STATUS
3523 (p_fa_instance_id => p_fa_instance_id,
3524 p_status => 'CANCELED',
3525 p_caller_name => p_caller_name,
3526 return_code => return_code ,
3527 error_description => error_description );
3528
3529 IF return_code <> 0 THEN
3530 return ;
3531 END IF ;
3532
3533
3534
3535 EXCEPTION
3536 WHEN resource_busy or no_data_found THEN
3537 raise e_xdp_fa_state_inprogress ;
3538 -- raise can not cancel order FA in progress
3539 END ;
3540
3541 ELSIF p_status = 'READY_FOR_RESOURCE' THEN
3542
3543 LOCK_AND_REMOVE_MSG
3544 (p_msg_id => p_msg_id,
3545 p_queue_name => 'XDP_WF_CHANNEL_Q',
3546 x_user_data =>lv_user_data,
3547 x_lock_status => lv_lock_status,
3548 x_error => lv_error);
3549
3550 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
3551
3552 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3553 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3554 dbg_msg := ('Aquired Lock on XDP_WF_CHANNEL_Q Calling UPDATE_XDP_FA_INSTANCE_STATUS');
3555 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3556 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3557 END IF;
3558 END IF;
3559 END IF;
3560
3561 UPDATE_XDP_FA_INSTANCE_STATUS
3562 (p_fa_instance_id => p_fa_instance_id,
3563 p_status => 'CANCELED',
3564 p_caller_name => p_caller_name,
3565 return_code => return_code ,
3566 error_description => error_description );
3567
3568 IF return_code <> 0 THEN
3569 return ;
3570 END IF ;
3571
3572
3573 ELSE
3574
3575 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3576 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3577 dbg_msg := ('Colud Not Aquire Lock on XDP_WF_CHANNEL_Q FA is IN PROGRESS');
3578 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3579 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3580 END IF;
3581 END IF;
3582 END IF;
3583
3584 raise e_xdp_fa_state_inprogress ;
3585 -- raise can not cancel order FA in progress try again
3586 END IF;
3587 ELSIF p_status = 'READY' THEN
3588
3589 LOCK_AND_REMOVE_MSG
3590 (p_msg_id => p_msg_id,
3594 x_error => lv_error);
3591 p_queue_name => 'XDP_FA_QUEUE',
3592 x_user_data => lv_user_data,
3593 x_lock_status => lv_lock_status,
3595
3596 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
3597
3598 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3599 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3600 dbg_msg := ('Aquired Lock on XDP_FA_QUEUE Calling UPDATE_XDP_FA_INSTANCE_STATUS ');
3601 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3602 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3603 END IF;
3604 END IF;
3605 END IF;
3606
3607 UPDATE_XDP_FA_INSTANCE_STATUS
3608 (p_fa_instance_id => p_fa_instance_id,
3609 p_status => 'CANCELED',
3610 p_caller_name => p_caller_name,
3611 return_code => return_code ,
3612 error_description => error_description );
3613
3614 IF return_code <> 0 THEN
3615 return ;
3616 END IF ;
3617
3618
3619 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3620 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3621 dbg_msg := ('Completed UPDATE_XDP_FA_INSTANCE_STATUS ');
3622 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3624 END IF;
3625 END IF;
3626 END IF;
3627
3628
3629 ELSE
3630 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3631 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3632 dbg_msg := ('Colud Not Aquire Lock on XDP_FA_QUEUE FA is IN PROGRESS');
3633 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3634 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3635 END IF;
3636 END IF;
3637 END IF;
3638
3639 raise e_xdp_fa_state_inprogress ;
3640 -- raise can not cancel order FA in progress try again
3641 END IF;
3642
3643 ELSIF p_status IN ('ERROR','STANDBY') THEN
3644
3645 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3646 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3647 dbg_msg := ('FA Status is '||p_status||' Calling UPDATE_XDP_FA_INSTANCE_STATUS ');
3648 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3649 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3650 END IF;
3651 END IF;
3652 END IF;
3653
3654 UPDATE_XDP_FA_INSTANCE_STATUS
3655 (p_fa_instance_id => p_fa_instance_id,
3656 p_status => 'CANCELED',
3657 p_caller_name => p_caller_name,
3658 return_code => return_code ,
3659 error_description => error_description );
3660
3661 IF return_code <> 0 THEN
3662 return ;
3663 END IF ;
3664
3665
3666 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3667 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA')) THEN
3668 dbg_msg := ('Completed UPDATE_XDP_FA_INSTANCE_STATUS ');
3669 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3670 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_FA', dbg_msg);
3671 END IF;
3672 END IF;
3673 END IF;
3674
3675 END IF;
3676
3677 EXCEPTION
3678 WHEN resource_busy OR no_data_found THEN
3679 return_code := -191318;
3680 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_REMOVE_ORDER');
3681 FND_MESSAGE.SET_TOKEN('ERROR_STRING', lv_error);
3682 error_description := FND_MESSAGE.GET;
3683 return;
3684 WHEN e_xdp_fa_state_inprogress THEN
3685 return_code := 197010;
3686 FND_MESSAGE.SET_NAME('XDP', 'XDP_FA_STATE_INPROGRESS');
3687 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_order_id);
3688 error_description := FND_MESSAGE.GET;
3689 return;
3690 WHEN OTHERS THEN
3691 return_code := -191266;
3692 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3693 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
3694 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3695 error_description := FND_MESSAGE.GET;
3696 END CANCEL_FA ;
3697
3698
3699 --
3700 -- Private API which will remove a workitem from queue
3704 p_msg_id IN RAW,
3701 --
3702 PROCEDURE CANCEL_WORKITEM
3703 (p_wi_instance_id IN NUMBER,
3705 p_wi_wf_item_type IN VARCHAR2,
3706 p_wi_wf_item_key IN VARCHAR2,
3707 p_caller_name IN VARCHAR2,
3708 p_status IN VARCHAR2,
3709 return_code OUT NOCOPY NUMBER,
3710 error_description OUT NOCOPY VARCHAR2)
3711 IS
3712 lv_id NUMBER;
3713 lv_lock VARCHAR2(1) := 'Y';
3714 lv_state VARCHAR2(100);
3715 lv_user_data SYSTEM.XDP_WF_CHANNELQ_TYPE;
3716 lv_lock_status VARCHAR2(1);
3717 lv_error VARCHAR2(1000);
3718
3719 e_xdp_wi_state_inprogress EXCEPTION;
3720
3721 BEGIN
3722
3723 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3724 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM')) THEN
3725 dbg_msg := ('Procedure XDP_INTERFACES.CANCEL_WORKITEM begins for WI Instance : '||p_wi_instance_id);
3726 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3727 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM', dbg_msg);
3728 END IF;
3729 END IF;
3730 END IF;
3731 return_code := 0;
3732 IF p_status IN ('STANDBY','ERROR','IN PROGRESS') THEN
3733
3734 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3735 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM')) THEN
3736 dbg_msg := ('Workitem Status is :' ||p_status||' Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
3737 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3738 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM', dbg_msg);
3739 END IF;
3740 END IF;
3741 END IF;
3742
3743 UPDATE_XDP_WI_INSTANCE_STATUS
3744 (p_order_id => null,
3745 p_wi_instance_id => p_wi_instance_id,
3746 p_status => 'CANCELED',
3747 p_caller_name => p_caller_name,
3748 return_code => return_code,
3749 error_description => error_description );
3750
3751 IF return_code <> 0 THEN
3752 return ;
3753 END IF ;
3754
3755 ELSIF p_status = 'READY' THEN
3756
3757 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3758 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM')) THEN
3759 dbg_msg := ('Workitem Status is :' ||p_status||' Removing From XDP_WORKITEM_QUEUE ');
3760 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3761 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM', dbg_msg);
3762 END IF;
3763 END IF;
3764 END IF;
3765
3766 LOCK_AND_REMOVE_MSG(
3767 p_msg_id => p_msg_id,
3768 p_queue_name => 'XDP_WORKITEM_QUEUE',
3769 x_user_data =>lv_user_data,
3770 x_lock_status => lv_lock_status,
3771 x_error => lv_error);
3772
3773
3774 IF lv_lock_status = G_LOCK_MSG_SUCCESS THEN
3775
3776 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3777 IF (FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM')) THEN
3778 dbg_msg := ('Workitem Removed From XDP_WORKITEM_QUEUE Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
3779 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3780 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'xdp.plsql.XDP_INTERFACES.CANCEL_WORKITEM', dbg_msg);
3781 END IF;
3782 END IF;
3783 END IF;
3784
3785 UPDATE_XDP_WI_INSTANCE_STATUS
3786 (p_order_id => null,
3787 p_wi_instance_id => p_wi_instance_id,
3788 p_status => 'CANCELED',
3789 p_caller_name => p_caller_name,
3790 return_code => return_code,
3791 error_description => error_description );
3792
3793 IF return_code <> 0 THEN
3794 return ;
3795 END IF ;
3796 return;
3797 ELSE
3798 raise e_xdp_wi_state_inprogress ;
3799 -- raise workitem in progress please try again
3800 END IF;
3801 END IF;
3802
3803
3804 EXCEPTION
3805 WHEN e_xdp_wi_state_inprogress THEN
3806 return_code := -191318;
3807 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_REMOVE_ORDER');
3808 FND_MESSAGE.SET_TOKEN('ERROR_STRING', lv_error);
3809 error_description := FND_MESSAGE.GET;
3810 return;
3811 WHEN OTHERS THEN
3812 return_code := -191266;
3813 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3814 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB');
3815 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3816 error_description := FND_MESSAGE.GET;
3817 END CANCEL_WORKITEM;
3818
3822 --
3819
3820 --
3821 -- Private API which will update xdp_order_headers status_code
3823 --
3824 PROCEDURE update_xdp_order_status
3825 (p_order_id IN NUMBER ,
3826 p_status IN VARCHAR2,
3827 p_caller_name IN VARCHAR2,
3828 return_code OUT NOCOPY NUMBER,
3829 error_description OUT NOCOPY VARCHAR2) IS
3830
3831 BEGIN
3832
3833 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3834 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_STATUS')) THEN
3835 dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_ORDER_STATUS begins.');
3836 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3837 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_STATUS', dbg_msg);
3838 END IF;
3839 END IF;
3840 END IF;
3841
3842 UPDATE xdp_order_headers
3843 SET last_updated_by = FND_GLOBAL.USER_ID,
3844 last_update_date = sysdate,
3845 last_update_login = FND_GLOBAL.LOGIN_ID,
3846 status_code = p_status,
3847 canceled_by = p_caller_name,
3848 cancel_provisioning_date = sysdate
3849 WHERE order_id = p_order_id ;
3850
3851 IF UPPER(p_status) = 'CANCELLED' THEN
3852 XDP_ENGINE.Set_Order_Param_Value(p_order_id,'FULFILLMENT_STATUS',p_status);
3853 END IF;
3854
3855 EXCEPTION
3856 WHEN OTHERS THEN
3857 return_code := -191266;
3858 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3859 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB. UPDATE_XDP_ORDER_STATUS');
3860 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3861 error_description := FND_MESSAGE.GET;
3862 END UPDATE_XDP_ORDER_STATUS;
3863
3864 --
3865 -- Private API which will update xdp_order_line_items status_code
3866 --
3867 --
3868
3869 PROCEDURE UPDATE_XDP_ORDER_LINE_STATUS
3870 (p_order_id IN NUMBER,
3871 p_lineitem_id IN NUMBER ,
3872 p_status IN VARCHAR2,
3873 p_caller_name IN VARCHAR2,
3874 return_code OUT NOCOPY NUMBER,
3875 error_description OUT NOCOPY VARCHAR2) IS
3876
3877 BEGIN
3878
3879 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3880 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_LINE_STATUS')) THEN
3881 dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_ORDER_LINE_STATUS begins.');
3882 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3883 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_LINE_STATUS', dbg_msg);
3884 END IF;
3885 END IF;
3886 END IF;
3887
3888 UPDATE xdp_order_line_items
3889 SET last_updated_by = FND_GLOBAL.USER_ID,
3890 last_update_date = sysdate,
3891 last_update_login = FND_GLOBAL.LOGIN_ID,
3892 status_code = p_status,
3893 canceled_by = p_caller_name,
3894 cancel_provisioning_date = sysdate
3895 WHERE order_id = NVL(p_order_id ,order_id)
3896 AND line_item_id = NVL(p_lineitem_id , line_item_id) ;
3897
3898 EXCEPTION
3899 WHEN OTHERS THEN
3900 return_code := -191266;
3901 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3902 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.UPDATE_XDP_ORDER_LINE_STATUS');
3903 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3904 error_description := FND_MESSAGE.GET;
3905 END UPDATE_XDP_ORDER_LINE_STATUS;
3906
3907 --
3908 -- Private API which will update xdp_fulfill_worklist status_code
3909 --
3910 --
3911
3912 PROCEDURE UPDATE_XDP_WI_INSTANCE_STATUS
3913 (p_order_id IN NUMBER,
3914 p_wi_instance_id IN NUMBER ,
3915 p_status IN VARCHAR2,
3916 p_caller_name IN VARCHAR2,
3917 return_code OUT NOCOPY NUMBER,
3918 error_description OUT NOCOPY VARCHAR2) IS
3919
3920 BEGIN
3921
3922 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3923 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_WI_INSTANCE_STATUS')) THEN
3924 dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_WI_INSTANCE_STATUS begins.');
3925 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3926 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_WI_INSTANCE_STATUS', dbg_msg);
3927 END IF;
3928 END IF;
3929 END IF;
3930 UPDATE xdp_fulfill_worklist
3931 SET last_updated_by = FND_GLOBAL.USER_ID,
3932 last_update_date = sysdate,
3933 last_update_login = FND_GLOBAL.LOGIN_ID,
3934 status_code = p_status,
3935 canceled_by = p_caller_name,
3936 cancel_provisioning_date = sysdate
3937 WHERE order_id = NVL(p_order_id , order_id )
3938 AND workitem_instance_id = NVL(p_wi_instance_id,workitem_instance_id);
3939
3940 EXCEPTION
3941 WHEN OTHERS THEN
3942 return_code := -191266;
3943 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3944 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.UPDATE_XDP_WI_INSTANCE_STATUS');
3945 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3946 error_description := FND_MESSAGE.GET;
3947 END UPDATE_XDP_WI_INSTANCE_STATUS;
3948
3949 --
3950 -- Private API which will update xdp_fa_runtime_list status_code
3951 --
3952 --
3953
3954 PROCEDURE UPDATE_XDP_FA_INSTANCE_STATUS
3955 (p_fa_instance_id IN NUMBER ,
3956 p_status IN VARCHAR2,
3957 p_caller_name IN VARCHAR2,
3958 return_code OUT NOCOPY NUMBER,
3959 error_description OUT NOCOPY VARCHAR2) IS
3960
3961 BEGIN
3962
3963 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3964 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_FA_INSTANCE_STATUS')) THEN
3965 dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_FA_INSTANCE_STATUS begins.');
3966 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3967 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_FA_INSTANCE_STATUS', dbg_msg);
3968 END IF;
3969 END IF;
3970 END IF;
3971 UPDATE xdp_fa_runtime_list
3972 SET last_updated_by = FND_GLOBAL.USER_ID,
3973 last_update_date = sysdate,
3974 last_update_login = FND_GLOBAL.LOGIN_ID,
3975 status_code = p_status,
3976 canceled_by = p_caller_name,
3977 cancel_provisioning_date = sysdate
3978 WHERE fa_instance_id = p_fa_instance_id;
3979
3980 EXCEPTION
3981 WHEN OTHERS THEN
3982 return_code := -191266;
3983 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
3984 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.UPDATE_XDP_FA_INSTANCE_STATUS');
3985 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
3986 error_description := FND_MESSAGE.GET;
3987 END UPDATE_XDP_FA_INSTANCE_STATUS;
3988
3989 --
3990 -- Provate API to update status or outbound messages to 'CANCELED' for canceled orders
3991 --
3992
3993 PROCEDURE CANCEL_READY_MSGS(p_order_id IN NUMBER ,
3994 x_error_code OUT NOCOPY NUMBER,
3995 x_error_message OUT NOCOPY VARCHAR2) is
3996
3997 BEGIN
3998
3999 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
4000 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_MSGS')) THEN
4001 dbg_msg := ('Procedure XDP_INTERFACES.CANCEL_READY_MSGS begins.');
4002 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4003 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.CANCEL_READY_MSGS', dbg_msg);
4004 END IF;
4005 END IF;
4006 END IF;
4007
4008 UPDATE xnp_msgs
4009 SET msg_status = 'CANCELED',
4010 last_update_date = SYSDATE
4011 WHERE order_id = p_order_id
4012 AND msg_status = 'READY';
4013
4014 EXCEPTION
4015 WHEN OTHERS THEN
4016 x_error_code := -191266;
4017 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
4018 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.CANCEL_READY_MSGS');
4019 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
4020 x_error_message := FND_MESSAGE.GET;
4021 END CANCEL_READY_MSGS ;
4022
4023
4024
4025 PROCEDURE Set_Line_Fulfillment_Status(
4026 p_line_item_id IN NUMBER,
4027 p_fulfillment_status IN VARCHAR2 DEFAULT NULL,
4028 x_RETURN_CODE OUT NOCOPY NUMBER,
4029 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
4030 )
4031 IS
4032 BEGIN
4033 IF (p_fulfillment_status IS NOT NULL) THEN
4034 XDP_ENGINE.Set_Line_Param_Value(p_line_item_id,'FULFILLMENT_STATUS',p_fulfillment_status,null);
4035 END IF;
4036
4037 x_RETURN_CODE := 0;
4038 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
4039 EXCEPTION
4040 WHEN OTHERS THEN
4041 x_return_code := -191266;
4042 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
4043 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Set_Line_Fulfillment_Status');
4044 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
4045 x_error_description := FND_MESSAGE.GET;
4046 END Set_Line_Fulfillment_Status;
4047
4048
4049 PROCEDURE Set_Line_Fulfillment_Status(
4053 x_RETURN_CODE OUT NOCOPY NUMBER,
4050 p_order_id IN NUMBER,
4051 p_line_number IN NUMBER,
4052 p_fulfillment_status IN VARCHAR2 DEFAULT NULL,
4054 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
4055 )
4056 IS
4057 l_line_item_id NUMBER:=NULL;
4058 BEGIN
4059 BEGIN
4060 SELECT line_item_id
4061 INTO l_line_item_id
4062 FROM xdp_order_line_items xoli
4063 WHERE xoli.order_id = p_order_id
4064 AND xoli.line_number = p_line_number;
4065 EXCEPTION
4066 WHEN NO_DATA_FOUND THEN
4067 null;
4068 END;
4069
4070 IF (p_fulfillment_status IS NOT NULL) THEN
4071 XDP_ENGINE.Set_Line_Param_Value(l_line_item_id,'FULFILLMENT_STATUS',p_fulfillment_status,null);
4072 END IF;
4073
4074 x_RETURN_CODE := 0;
4075 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
4076 EXCEPTION
4077 WHEN OTHERS THEN
4078 x_return_code := -191266;
4079 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
4080 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Set_Line_Fulfillment_Status');
4081 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
4082 x_error_description := FND_MESSAGE.GET;
4083 END Set_Line_Fulfillment_Status;
4084
4085
4086
4087 PROCEDURE Get_Line_Fulfillment_Status(
4088 p_line_item_id IN NUMBER,
4089 x_fulfillment_status OUT NOCOPY VARCHAR2,
4090 x_RETURN_CODE OUT NOCOPY NUMBER,
4091 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
4092 ) IS
4093 l_item_line_id NUMBER:=NULL;
4094 BEGIN
4095 BEGIN
4096 x_fulfillment_status := UPPER(XDP_ENGINE.Get_Line_Param_Value(p_line_item_id,'FULFILLMENT_STATUS'))
4097 ;
4098 EXCEPTION
4099 WHEN NO_DATA_FOUND THEN
4100 x_fulfillment_status := UPPER('Success');
4101 END;
4102
4103 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
4104 x_return_code := 0;
4105 EXCEPTION
4106 WHEN OTHERS THEN
4107 x_return_code := -191266;
4108 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
4109 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Get_Line_Fulfillment_Status');
4110 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
4111 x_error_description := FND_MESSAGE.GET;
4112 END Get_Line_Fulfillment_Status;
4113
4114
4115
4116 PROCEDURE Get_Line_Fulfillment_Status(
4117 p_order_id IN NUMBER,
4118 p_line_number IN NUMBER,
4119 x_fulfillment_status OUT NOCOPY VARCHAR2,
4120 x_RETURN_CODE OUT NOCOPY NUMBER,
4121 x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
4122 ) IS
4123 l_line_item_id number;
4124 BEGIN
4125 BEGIN
4126 SELECT line_item_id
4127 INTO l_line_item_id
4128 FROM xdp_order_line_items xoli
4129 WHERE xoli.order_id = p_order_id
4130 AND xoli.line_number = p_line_number;
4131 EXCEPTION
4132 WHEN NO_DATA_FOUND THEN
4133 null;
4134 END;
4135
4136 BEGIN
4137 x_fulfillment_status := UPPER(XDP_ENGINE.Get_Line_Param_Value(l_line_item_id,'FULFILLMENT_STATUS'))
4138 ;
4139 EXCEPTION
4140 WHEN NO_DATA_FOUND THEN
4141 x_fulfillment_status := UPPER('Success');
4142 END;
4143 x_ERROR_DESCRIPTION := FND_API.G_RET_STS_SUCCESS;
4144 x_return_code := 0;
4145 EXCEPTION
4146 WHEN OTHERS THEN
4147 x_return_code := -191266;
4148 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
4149 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.Get_Line_Fulfillment_Status');
4150 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
4151 x_error_description := FND_MESSAGE.GET;
4152 END Get_Line_Fulfillment_Status;
4153
4154 BEGIN
4155 -- Package initialization
4156 Find_XDP_SCHEMA;
4157 END XDP_INTERFACES;