[Home] [Help]
PACKAGE BODY: APPS.XDP_ORDER_SYNC
Source
1 PACKAGE BODY XDP_ORDER_SYNC AS
2 /* $Header: XDPSORDB.pls 120.1 2005/06/09 00:30:45 appldev $ */
3
4 PROCEDURE Execute_LineItem_Sync(
5 p_order_id in NUMBER,
6 p_lineItem_id in NUMBER,
7 x_return_code OUT NOCOPY NUMBER,
8 x_error_description OUT NOCOPY VARCHAR2);
9
10 PROCEDURE Execute_Workitem_Sync(
11 p_order_id in NUMBER,
12 p_lineitem_id in NUMBER,
13 p_workitem_instance_id in NUMBER,
14 x_return_code OUT NOCOPY NUMBER,
15 x_error_description OUT NOCOPY VARCHAR2);
16
17 PROCEDURE Execute_FA_Sync(
18 p_fa_instance_id in NUMBER,
19 x_return_code OUT NOCOPY NUMBER,
20 x_error_description OUT NOCOPY VARCHAR2);
21
22
23 PROCEDURE UPDATE_XDP_ORDER_STATUS(p_status IN VARCHAR2,
24 p_order_id IN NUMBER) IS
25 x_progress VARCHAR2(2000);
26
27 BEGIN
28 IF p_status IN ('SUCCESS_WITH_OVERRIDE','ABORTED','SUCCESS') THEN
29
30 UPDATE xdp_order_headers
31 SET status_code = p_status ,
32 completion_date = sysdate ,
33 last_update_date = sysdate,
34 last_updated_by = fnd_global.user_id,
35 last_update_login = fnd_global.login_id
36 WHERE order_id = p_order_id ;
37 ELSE
38 UPDATE xdp_order_headers
39 SET status_code = p_status ,
40 last_update_date = sysdate,
41 last_updated_by = fnd_global.user_id,
42 last_update_login = fnd_global.login_id
43 WHERE order_id = p_order_id ;
44
45 END IF ;
46
47 EXCEPTION
48 WHEN others THEN
49 RAISE ;
50 END UPDATE_XDP_ORDER_STATUS;
51
52
53
54 PROCEDURE UPDATE_XDP_ORDER_LINE_STATUS(p_status IN VARCHAR2,
55 p_line_item_id IN NUMBER)IS
56 x_progress VARCHAR2(2000);
57
58
59 BEGIN
60 IF p_status IN ('IN PROGRESS','ERROR') THEN
61
62 UPDATE xdp_order_line_items
63 SET status_code = p_status ,
64 last_update_date = sysdate,
65 last_updated_by = fnd_global.user_id,
66 last_update_login = fnd_global.login_id
67 WHERE line_item_id = p_line_item_id ;
68 ELSE
69 UPDATE xdp_order_line_items
70 SET status_code = p_status ,
71 completion_date = sysdate ,
72 last_update_date = sysdate,
73 last_updated_by = fnd_global.user_id,
74 last_update_login = fnd_global.login_id
75 WHERE line_item_id = p_line_item_id ;
76 END IF ;
77
78
79 EXCEPTION
80 WHEN others THEN
81 RAISE ;
82 END UPDATE_XDP_ORDER_LINE_STATUS;
83
84
85
86 PROCEDURE UPDATE_XDP_WORKITEM_STATUS(p_status IN VARCHAR2,
87 p_workitem_instance_id IN NUMBER)IS
88 x_progress VARCHAR2(2000);
89
90
91 BEGIN
92 IF p_status IN ('IN PROGRESS','ERROR') THEN
93
94 UPDATE xdp_fulfill_worklist
95 SET status_code = p_status ,
96 last_update_date = sysdate,
97 last_updated_by = fnd_global.user_id,
98 last_update_login = fnd_global.login_id
99 WHERE workitem_instance_id = p_workitem_instance_id ;
100 ELSE
101 UPDATE xdp_fulfill_worklist
102 SET status_code = p_status ,
103 completion_date = sysdate ,
104 last_update_date = sysdate,
105 last_updated_by = fnd_global.user_id,
106 last_update_login = fnd_global.login_id
107 WHERE workitem_instance_id = p_workitem_instance_id ;
108 END IF ;
109
110
111 EXCEPTION
112 WHEN others THEN
113 RAISE ;
114 END UPDATE_XDP_WORKITEM_STATUS;
115
116
117
118 PROCEDURE UPDATE_XDP_FA_STATUS(p_status IN VARCHAR2,
119 p_fa_instance_id IN NUMBER)IS
120 x_progress VARCHAR2(2000);
121
122 BEGIN
123 IF p_status IN ('IN PROGRESS','ERROR') THEN
124
125 UPDATE xdp_fa_runtime_list
126 SET status_code = p_status ,
127 last_update_date = sysdate,
128 last_updated_by = fnd_global.user_id,
129 last_update_login = fnd_global.login_id
130 WHERE fa_instance_id = p_fa_instance_id ;
131 ELSE
132 UPDATE xdp_fa_runtime_list
133 SET status_code = p_status ,
134 completion_date = sysdate ,
135 last_update_date = sysdate,
136 last_updated_by = fnd_global.user_id,
137 last_update_login = fnd_global.login_id
138 WHERE fa_instance_id = p_fa_instance_id ;
139
140 END IF ;
141
142
143 EXCEPTION
144 WHEN others THEN
145 RAISE ;
146 END UPDATE_XDP_FA_STATUS;
147
148
149 PROCEDURE Execute_Order_SYNC(
150 p_Order_ID IN NUMBER,
151 x_return_code OUT NOCOPY NUMBER,
152 x_error_description OUT NOCOPY VARCHAR2)
153 IS
154 l_done VARCHAR2(1);
155 l_ret_code NUMBER := 0;
156 l_ret_str VARCHAR2(2000);
157 e_exec_order_failure EXCEPTION;
158 BEGIN
159 x_return_code := 0;
160 --
161 -- Initialize Order
162 --
163 Update_XDP_Order_Status('IN_PROGRESS',p_Order_ID);
164
165 -- Execute lineitems in sequence
166 FOR c_lineitem IN
167 (SELECT line_item_id FROM xdp_order_line_items
168 WHERE order_id=p_order_id ORDER BY line_sequence)
169 LOOP
170 Execute_LineItem_Sync(
171 p_order_id => p_order_id,
172 p_lineitem_id => c_lineitem.line_item_id,
173 x_return_code => x_return_code,
174 x_error_description =>x_error_description);
175 IF(x_return_code <> 0) THEN
176 RAISE e_exec_order_failure;
177 END IF;
178 END LOOP;
179
180 -- See if any line has error status
181 BEGIN
182 SELECT 'N' INTO l_done
183 FROM dual
184 WHERE EXISTS( SELECT 'x' FROM
185 xdp_order_line_items
186 WHERE order_id = p_order_id AND
187 status_code = 'ERROR');
188 EXCEPTION
189 WHEN no_data_found THEN
190 l_done := 'Y';
191 END;
192
193 IF l_done = 'Y' THEN
194 Update_XDP_Order_Status('SUCCESS',p_Order_ID);
195 ELSE
196 Update_XDP_Order_Status('ERROR',p_Order_ID);
197 END IF;
198
199 -- Tell the world we have done the order, failure or success
200 XNP_STANDARD.PUBLISH_EVENT(
201 p_ORDER_ID => p_order_id,
202 p_WORKITEM_INSTANCE_ID => NULL,
203 p_FA_INSTANCE_ID => NULL,
204 p_EVENT_TYPE => 'XDP_ORDER_DONE',
205 p_PARAM_LIST => NULL,
206 p_CALLBACK_REF_ID => p_order_id,
207 x_error_code => l_ret_code,
208 x_error_message => l_ret_str
209 );
210 EXCEPTION
211 WHEN e_exec_order_failure THEN
212 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
213 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'XDP_ORDER_SYNC.Execute_Order_SYNC', SQLERRM);
214 END IF;
215 Update_XDP_Order_Status( 'ERROR',p_Order_ID);
216
217 WHEN OTHERS THEN
218 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
219 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'XDP_ORDER_SYNC.Execute_Order_SYNC', SQLERRM);
220 END IF;
221 x_return_code := SQLCODE;
222 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
223 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDP_ORDER_SYNC.Execute_Order_SYNC');
224 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
225 x_error_description := FND_MESSAGE.GET;
226 Update_XDP_Order_Status( 'ERROR',p_Order_ID);
227
228 END Execute_Order_SYNC;
229
230 /*
231 execute lineitem in a synchronous mode
232 */
233 PROCEDURE Execute_LineItem_Sync(
234 p_order_id in NUMBER,
235 p_lineitem_id in NUMBER,
236 x_return_code OUT NOCOPY NUMBER,
237 x_error_description OUT NOCOPY VARCHAR2)
238 IS
239 lv_done VARCHAR2(1);
240 lv_ib_error_code NUMBER;
241 e_exec_line_failure EXCEPTION;
242 lv_ib_err_desc varchar2(2000);
243
244 BEGIN
245 x_return_code := 0;
246
247 -- Initialize LineItem for processing
248 UPDATE_XDP_ORDER_LINE_STATUS('IN PROGRESS',p_lineItem_id);
249
250
251 -- Execute all workitems in sequence
252 FOR c_workitem_instance in
253 (SELECT workitem_instance_id FROM xdp_fulfill_worklist
254 WHERE line_item_id=p_lineItem_id order by wi_sequence)
255 LOOP
256 Execute_Workitem_Sync(
257 p_order_id => p_order_id,
258 p_lineitem_id =>p_lineitem_id,
259 p_workitem_instance_id => c_workitem_instance.workitem_instance_id,
260 x_return_code => x_return_code,
261 x_error_description =>x_error_description);
262
263 IF (x_return_code <> 0 ) THEN
264 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
265 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_LineItem_Sync', 'Execute_Workitem_Sync '||'returns error');
266 END IF;
267 RAISE e_exec_line_failure;
268 END IF;
269
270 END LOOP;
271
272 -- Check if any workitem was in error status
273 BEGIN
274 SELECT 'N' INTO lv_done FROM dual
275 WHERE EXISTS( SELECT 'x' FROM
276 XDP_FULFILL_WORKLIST
277 WHERE line_item_id = p_lineItem_id AND
278 status_code = 'ERROR');
279 EXCEPTION
280 WHEN no_data_found THEN
281 lv_done := 'Y';
282 END;
283
284 IF lv_done = 'Y' THEN
285 UPDATE_XDP_ORDER_LINE_STATUS('SUCCESS',p_lineItem_id);
286 ELSE
287 UPDATE_XDP_ORDER_LINE_STATUS('ERROR',p_lineItem_id);
288 END IF;
289 XDP_INSTALL_BASE.UPDATE_IB(p_order_id,p_lineItem_id,lv_ib_error_code,lv_ib_err_desc);
290 EXCEPTION
291 WHEN e_exec_line_failure THEN
292 UPDATE_XDP_ORDER_LINE_STATUS('ERROR',p_lineItem_id);
293 WHEN OTHERS THEN
294 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
295 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_LineItem_Sync', 'unexpected exception occurred.');
296 END IF;
297 x_return_code := -191266;
298 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
299 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDP_ORDER_SYNC.Execute_LineItem_Sync');
300 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
301 x_error_description := FND_MESSAGE.GET;
302 UPDATE_XDP_ORDER_LINE_STATUS('ERROR',p_lineItem_id);
303 END Execute_LineItem_Sync;
304
305 PROCEDURE Execute_Workitem_Sync(
306 p_order_id in NUMBER,
307 p_lineitem_id in NUMBER,
308 p_workitem_instance_id in NUMBER,
309 x_return_code OUT NOCOPY NUMBER,
310 x_error_description OUT NOCOPY VARCHAR2)
311 IS
312 CURSOR lc_fa_map is
313 SELECT
314 wfg.fulfillment_action_id,
315 wfg.provisioning_seq
316 FROM
317 xdp_wi_fa_mapping wfg,
318 XDP_FULFILL_WORKLIST fwt
319 WHERE
320 fwt.workitem_id = wfg.workitem_id and
321 fwt.workitem_instance_id = p_workitem_instance_id
322 ORDER BY wfg.provisioning_seq;
323
324 CURSOR lc_fa_list IS
325 SELECT fa_instance_id
326 FROM xdp_fa_runtime_list
327 WHERE
328 workitem_instance_id = p_workitem_instance_id
329 ORDER BY provisioning_sequence;
330
331 lv_proc VARCHAR2(80);
332 lv_type VARCHAR2(40);
333 lv_wi VARCHAR2(80);
334 lv_workitem_id NUMBER;
335
336 lv_fa_id NUMBER;
337 lv_fa_fail BOOLEAN := FALSE;
338
339 lv_user_item_type varchar2(10);
340 lv_user_item_key varchar2(240);
341 lv_user_item_key_prefix varchar2(240);
342 lv_user_WI_proc varchar2(40);
343 lv_user_WF_process varchar2(40);
344
345 x_parameters VARCHAR2(4000);
346
347 e_exec_wi_failure EXCEPTION;
348 l_status_code VARCHAR2(32);
349 BEGIN
350 x_return_code := 0;
351
352 -- Initialize worktime
353 UPDATE_XDP_WORKITEM_STATUS('IN PROGRESS',p_workitem_instance_id);
354
355 -- Evaluate procedure if there is one for this workitem
356 xdp_engine.EvaluateWIParamsOnStart(p_workitem_instance_id);
357
358 -- Get workitem configuration data
359 SELECT
360 wim.wi_type_code,
361 wim.fa_exec_map_proc,
362 wim.workitem_name,
363 wim.workitem_id,
364 wim.user_wf_item_type,
365 wim.user_wf_item_key_prefix,
366 wim.user_wf_process_name,
367 wim.wf_exec_proc
368 INTO
369 lv_type,
370 lv_proc,
371 lv_wi,
372 lv_workitem_id,
373 lv_user_item_type,
374 lv_user_item_key_prefix,
375 lv_user_wf_process,
376 lv_user_wi_proc
377 FROM
378 xdp_workitems wim,
379 xdp_fulfill_worklist fwt
380 WHERE
381 wim.workitem_id = fwt.workitem_id AND
382 fwt.workitem_instance_id = p_workitem_instance_id;
383
384 IF lv_type = 'STATIC' THEN
385 -- populate xdp_fa_runtime_list
386 FOR lv_fa_rec IN lc_fa_map LOOP
387 lv_fa_id := XDP_ENG_UTIL.Add_FA_toWI(
388 p_wi_instance_id => p_workitem_instance_id,
389 p_fulfillment_action_id => lv_fa_rec.fulfillment_action_id);
390 END LOOP;
391 ELSIF lv_type = 'DYNAMIC' then
392 -- populate xdp_fa_runtime_list with its dynamic mapping procedure
393 IF lv_proc IS NOT NULL THEN
394 XDP_UTILITIES.CallFAMapProc(
395 p_procedure_name => lv_proc,
396 p_order_id => p_order_id,
397 p_line_item_id => p_lineitem_id,
398 p_wi_instance_id => p_workitem_instance_id,
399 p_return_code => x_return_code,
400 p_error_description => x_error_description);
401
402 IF x_return_code <> 0 THEN
403 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
404 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Error returned '
405 ||x_return_code
406 ||' from XDP_ERRORS_PKG.Set_Message');
407 END IF;
408 RAISE e_exec_wi_failure;
409 END IF;
410 ELSE
411 FND_MESSAGE.SET_NAME('XDP', 'XDP_FA_EXEC_MAP_PROC_NOT_EXIST');
412 x_error_description := FND_MESSAGE.GET;
413 x_return_code := -191156;
414 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
415 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', x_error_description);
416 END IF;
417 RAISE e_exec_wi_failure;
418 END IF;
419 ELSIF lv_type = 'WORKFLOW_PROC' THEN
420 IF lv_user_wi_proc is null THEN
421 FND_MESSAGE.SET_NAME('XDP', 'XDP_WF_PROC_NOT_EXIST');
422 x_error_description := FND_MESSAGE.GET;
423 x_return_code := -191266;
424 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
425 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', x_error_description);
426 END IF;
427 RAISE e_exec_wi_failure;
428 END IF;
429
430 -- Call user defined workflow procedure, which should call wf createprocess
431 -- and return process_name, item_type and item_name.
432
433 XDP_UTILITIES.CallWIWorkflowProc (
434 P_PROCEDURE_NAME => lv_user_wi_proc,
435 P_ORDER_ID => p_order_id,
436 P_LINE_ITEM_ID => p_lineitem_id,
437 P_WI_INSTANCE_ID => p_workitem_instance_id,
438 P_WF_ITEM_TYPE => lv_user_item_type,
439 P_WF_ITEM_KEY => lv_user_item_key,
440 P_WF_PROCESS_NAME => lv_user_wf_process,
441 P_RETURN_CODE => x_return_code,
442 P_ERROR_DESCRIPTION => x_error_description);
443
444 IF x_return_code <> 0 THEN
445 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
446 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Error '
447 ||x_return_code
448 ||' returned from XDP_UTILITIES.CallWIWorkflowProc');
449 END IF;
450 RAISE e_exec_wi_failure;
451 END IF;
452
453 -- in case of the process does not have line_item_id as attribute
454 XDPCORE.CheckNAddItemAttrNumber (
455 itemtype => lv_user_item_type,
456 itemkey => lv_user_item_key,
457 AttrName => 'LINE_ITEM_ID',
458 AttrValue => p_lineitem_id,
459 ErrCode => x_return_code,
460 ErrStr => x_error_description);
461
462 IF x_return_code <> 0 THEN
463 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
464 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Error '
465 ||x_return_code
466 ||' returned from XDPCORE.CheckNAddItemAttrNumber - LINE_ITEM_ID');
467 END IF;
468 RAISE e_exec_wi_failure;
469 END IF;
470
471 -- in case of the process does not have WORKITEM_INSTANCE_ID as attribute
472 XDPCORE.CheckNAddItemAttrNumber (
473 itemtype => lv_user_item_type,
474 itemkey => lv_user_item_key,
475 AttrName => 'WORKITEM_INSTANCE_ID',
476 AttrValue => p_workitem_instance_id,
477 ErrCode => x_return_code,
478 ErrStr => x_error_description);
479
480 IF x_return_code <> 0 THEN
481 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
482 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Error '
483 ||x_return_code
484 ||' returned from XDPCORE.CheckNAddItemAttrNumber - WORKITEM_INSTANCE_ID');
485 END IF;
486 RAISE e_exec_wi_failure;
487 END IF;
488
489 -- in case of the process does not have ORDER_ID as attribute
490 XDPCORE.CheckNAddItemAttrNumber (
491 itemtype => lv_user_item_type,
492 itemkey => lv_user_item_key,
493 AttrName => 'ORDER_ID',
494 AttrValue => p_order_id,
495 ErrCode => x_return_code,
496 ErrStr => x_error_description);
497
498 IF x_return_code <> 0 THEN
499 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
500 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Error '
501 ||x_return_code
502 ||' returned from XDPCORE.CheckNAddItemAttrNumber - ORDER_ID');
503 END IF;
504 RAISE e_exec_wi_failure;
505 END IF;
506
507 UPDATE XDP_FULFILL_WORKLIST
508 SET WF_ITEM_TYPE = lv_user_item_type,
509 WF_ITEM_KEY = lv_user_item_key,
510 LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
511 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
512 WHERE WORKITEM_INSTANCE_ID = p_workitem_instance_id;
513
514 --Start the process
515 WF_ENGINE.StartProcess(lv_user_item_type,lv_user_item_key);
516 -- bypass the execution of FA List
517 GOTO UpdateStatus;
518 ELSIF lv_type = 'WORKFLOW' THEN
519 IF (lv_user_item_type IS NULL) OR
520 (lv_user_wf_process IS NULL) THEN
521
522 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
523 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDP_ORDER_SYNC.Execute_Workitem_Sync');
524 x_error_description :=
525 'XDP_ORDER_SYNC.execute_workitem_sync. process_name or item_type not specified for a '
526 || 'defined Workflow of workitem: '
527 || lv_workitem_id ;
528
529 FND_MESSAGE.SET_TOKEN('ERROR_STRING', x_error_description);
530 x_error_description := FND_MESSAGE.GET;
531 x_return_code := -191266;
532
533
534 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
535 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Some required fields for WORKFLOW are null.');
536 END IF;
537 -- dbms_output.put_line('XDP_ORDER_SYNC.Execute_Workitem_Sync 8' || x_return_code);
538 RAISE e_exec_wi_failure;
539 END IF;
540
541 -- Create user defined workflow process
542 XDPCORE.CreateAndAddAttrNum(
543 itemtype => lv_user_item_type,
544 itemkey => wf_engine.eng_synch,
545 processname => lv_user_wf_process,
546 parentitemtype => NULL,
547 parentitemkey => NULL,
548 OrderID => p_order_id,
549 LineitemID => p_lineitem_id,
550 WIInstanceID => p_workitem_instance_id,
551 FAInstanceID => null);
552
553 update XDP_FULFILL_WORKLIST
554 set WF_ITEM_TYPE = lv_user_item_type,
555 WF_ITEM_KEY = lv_user_item_key,
556 LAST_UPDATE_DATE = sysdate,
557 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
558 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
559 where
560 WORKITEM_INSTANCE_ID = p_workitem_instance_id;
561 -- Start the process
562 WF_ENGINE.StartProcess(lv_user_item_type,wf_engine.eng_synch);
563 -- bypass the execution of FA List
564 GOTO UpdateStatus;
565 ELSE
566 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_CONFIG_ERROR');
567 FND_MESSAGE.SET_TOKEN('WORK_ITEM_NAME', lv_wi);
568 x_error_description := FND_MESSAGE.GET;
569 x_return_code := 191272;
570 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
571 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync ', x_error_description);
572 END IF;
573 -- dbms_output.put_line('XDP_ORDER_SYNC.Execute_Workitem_Sync 9' || x_return_code);
574 RAISE e_exec_wi_failure;
575 END IF;
576
577 -- Execute all FAs
578 FOR lv_fa_rec2 IN lc_fa_list LOOP
579 execute_fa_sync(
580 p_fa_instance_id => lv_fa_rec2.fa_instance_id,
581 x_return_code => x_return_code,
582 x_error_description => x_error_description);
583 IF x_return_code <> 0 THEN
584 -- Continue even one FA fails,
585 -- but the status for this workitem will be marked as error
586 -- dbms_output.put_line('XDP_ORDER_SYNC.Execute_Workitem_Sync 10 ' || x_return_code);
587 RAISE e_exec_wi_failure;
588 NULL;
589 END IF;
590 END LOOP;
591
592 <<UpdateStatus>>
593 IF (XDPSTATUS.IS_WI_IN_ERROR(p_workitem_instance_id)) THEN
594 x_return_code := 0;
595 l_status_code := 'ERROR';
596 ELSE
597 x_return_code := 0;
598 l_status_code :='SUCCESS';
599 END IF;
600
601 UPDATE_XDP_WORKITEM_STATUS(l_status_code,p_workitem_instance_id);
602
603 EXCEPTION
604 WHEN e_exec_wi_failure THEN
605 -- dbms_output.put_line('XDP_ORDER_SYNC.Execute_Workitem_Sync ' || x_return_code);
606 x_parameters := 'ERROR_STRING='||x_error_description||'#XDP#';
607 XDP_ERRORS_PKG.Set_Message(
608 p_object_type => 'WORKITEM',
609 p_object_key => p_workitem_instance_id,
610 p_message_name => 'XDP_WI_PROV_ERROR',
611 p_message_parameters => x_parameters);
612
613 UPDATE_XDP_WORKITEM_STATUS('ERROR',p_workitem_instance_id);
614
615 WHEN OTHERS THEN
616 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
617 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_WI_Sync', 'Unexpected exception '||sqlcode);
618 END IF;
619 x_return_code := sqlcode;
620 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
621 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDP_ORDER_SYNC.Execute_Workitem_Sync');
622 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
623 x_error_description := FND_MESSAGE.GET ||' - '||lv_wi;
624
625 x_parameters := 'ERROR_STRING='||x_error_description||'#XDP#';
626 XDP_ERRORS_PKG.Set_Message(p_object_type => 'WORKITEM',
627 p_object_key => p_workitem_instance_id,
628 p_message_name => 'XDP_WI_PROV_ERROR',
629 p_message_parameters => x_parameters);
630
631 UPDATE_XDP_WORKITEM_STATUS('ERROR',p_workitem_instance_id);
632
633 END Execute_Workitem_Sync;
634 /*
635 execute FA in a synchronous mode
636 */
637 PROCEDURE Execute_FA_Sync(
638 p_fa_instance_id IN NUMBER,
639 x_return_code OUT NOCOPY NUMBER,
640 x_error_description OUT NOCOPY VARCHAR2)
641 IS
642 lv_fe_name VARCHAR2(80);
643 lv_fe_id NUMBER;
644 lv_fa_id NUMBER;
645 lv_fetype_id NUMBER;
646 lv_fetype VARCHAR2(80);
647 lv_sw_generic VARCHAR2(80);
648 lv_adapter VARCHAR2(80);
649 lv_proc VARCHAR2(80);
650 lv_channel VARCHAR2(80);
651 lv_order_id NUMBER;
652 lv_wi_instance_id NUMBER;
653 lv_ret NUMBER;
654 lv_str VARCHAR2(300);
655 lv_ref_id NUMBER := 0;
656
657 lv_locked_flag varchar2(1) := 'N';
658
659 CURSOR lc_channels(l_fe_id NUMBER) IS
660 SELECT channel_name,adapter_status
661 FROM XDP_ADAPTER_REG
662 WHERE adapter_status = 'IDLE' AND
663 usage_code = 'TEST' AND
664 fe_id = l_fe_id;
665
666 lv_line_item_id NUMBER;
667
668 e_exec_fa_failure EXCEPTION;
669 x_parameters VARCHAR2(4000);
670
671 BEGIN
672 x_return_code := 0;
673
674 -- Initialization
675 UPDATE_XDP_FA_STATUS('IN PROGRESS',p_fa_instance_id);
676
677 -- Get the configuration data for the fa
678 SELECT
679 fe_routing_proc,
680 frt.workitem_instance_id,
681 fwt.order_id,
682 frt.fulfillment_action_id,
683 fwt.line_item_id
684 INTO
685 lv_proc,
686 lv_wi_instance_id,
687 lv_order_id,
688 lv_fa_id,
689 lv_line_item_id
690 FROM XDP_FULFILL_ACTIONS fan,
691 xdp_fa_runtime_list frt,
692 XDP_FULFILL_WORKLIST fwt
693 WHERE
694 fan.fulfillment_action_id = frt.fulfillment_action_id and
695 fwt.workitem_instance_id = frt.workitem_instance_id and
696 frt.fa_instance_id = p_fa_instance_id;
697
698 -- Get routing procedure, which will be used to get FE
699
700 XDP_UTILITIES.CallFERoutingProc(
701 p_procedure_name => lv_proc,
702 p_order_id => lv_order_id,
703 p_line_item_id => lv_line_item_id,
704 p_wi_instance_id => lv_wi_instance_id,
705 p_fa_instance_id => p_fa_instance_id,
706 p_fe_name => lv_fe_name,
707 p_return_code => x_return_code,
708 p_error_description => x_error_description);
709
710 IF x_return_code <> 0 THEN
711 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
712 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_FA_Sync', 'Error '
713 ||x_return_code
714 ||' returned when call XDP_UTILITIES.CallFERoutingProc');
715 END IF;
716 RAISE e_exec_fa_failure;
717 ELSE
718 -- get configuration for the FE
719 XDP_ENGINE.Get_FE_ConfigInfo(
720 lv_fe_name,
721 lv_fe_id,
722 lv_fetype_id,
723 lv_fetype,
724 lv_sw_generic,
725 lv_adapter);
726
727 SELECT fulfillment_proc
728 INTO
729 lv_proc
730 FROM xdp_fa_fulfillment_proc ffp,
731 xdp_fe_sw_gen_lookup fsp
732 WHERE
733 ffp.fulfillment_action_id = lv_fa_id AND
734 ffp.fe_sw_gen_lookup_id = fsp.fe_sw_gen_lookup_id AND
735 fsp.fetype_id = lv_fetype_id AND
736 fsp.sw_generic = lv_sw_generic AND
737 fsp.adapter_type = lv_adapter;
738
739 -- Try to obtain an available channel,
740 XDPCORE_FA.SearchAndLockChannel(lv_fe_id,'TEST','N',
741 XDP_ADAPTER.pv_statusRunning,lv_locked_flag,lv_channel);
742 IF lv_locked_flag = 'N' THEN --COD
743 -- if fails, try an adapter that can be started on demand
744 XDPCORE_FA.SearchAndLockChannel(
745 lv_fe_id,
746 'TEST',
747 'Y',
748 XDP_ADAPTER.pv_statusDisconnected,
749 lv_locked_flag,
750 lv_channel);
751
752 IF(lv_locked_flag = 'Y') THEN
753 -- if success, the connect the adapter, and lock the channel
754 lv_locked_flag := XDPCORE_FA.ConnectOnDemand(lv_channel,x_return_code,x_error_description);
755 END IF;
756 END IF;
757
758 IF lv_locked_flag = 'N' THEN -- try a different type of adapter
759 XDPCORE_FA.SearchAndLockChannel(lv_fe_id,'NORMAL','N',
760 XDP_ADAPTER.pv_statusRunning,lv_locked_flag,lv_channel);
761 IF lv_locked_flag = 'N' THEN --COD
762 -- if fails, try an adapter that can be started on demand
763 XDPCORE_FA.SearchAndLockChannel(lv_fe_id,
764 'NORMAL',
765 'Y',
766 XDP_ADAPTER.pv_statusDisconnected,
767 lv_locked_flag,lv_channel);
768 IF(lv_locked_flag = 'Y') THEN
769 -- if success, the connect the adapter, and lock the channel
770 lv_locked_flag := XDPCORE_FA.ConnectOnDemand(lv_channel,x_return_code,x_error_description);
771 END IF;
772 END IF;
773 END IF;
774
775 IF lv_locked_flag = 'N' THEN
776 -- if still fails, get out
777 x_return_code := -191142;
778 FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_START_ADAPTER');
779 FND_MESSAGE.SET_TOKEN('CHANNEL_NAME', lv_channel);
780 FND_MESSAGE.SET_TOKEN('ADAPTER_NAME', lv_adapter);
781 FND_MESSAGE.SET_TOKEN('ERROR_STRING', x_error_description);
782 FND_MESSAGE.SET_TOKEN('FE_NAME', lv_fe_name);
783 x_error_description := FND_MESSAGE.GET;
784
785 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
786 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
787 'XDP_ORDER_SYNC.Execute_FA_Sync',
788 x_error_description);
789 END IF;
790 RAISE e_exec_fa_failure;
791 END IF;
792
793 -- Execute the fulfillment procedure with the given channel
794 XDP_UTILITIES.CallFulfillmentProc(
795 p_procedure_name => lv_proc,
796 p_order_id => lv_order_id,
797 p_line_item_id => lv_line_item_id,
798 p_wi_instance_id => lv_wi_instance_id,
799 p_fa_instance_id => p_fa_instance_id,
800 p_channel_name => lv_channel,
801 p_fe_name =>lv_fe_name,
802 p_fa_item_type => NULL,
803 p_fa_item_key => NULL,
804 p_return_code => x_return_code,
805 p_error_description => x_error_description);
806
807 -- Release the channel
808 XDPCORE_FA.HandOverChannel (
809 lv_channel,
810 lv_fe_id,
811 'TEST',
812 'FA',
813 lv_ret,
814 lv_str);
815
816 IF x_return_code <> 0 THEN
817 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
818 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_FA_Sync', 'Error '
819 ||x_error_description
820 ||' returned when call XDP_UTILITIES.CallFulfillmentProc.');
821 END IF;
822 RAISE e_exec_fa_failure;
823 ELSE
824 -- Set FA Status
825 UPDATE_XDP_FA_STATUS('SUCCESS',p_fa_instance_id);
826 END IF;
827 END IF;
828 EXCEPTION
829 WHEN e_exec_fa_failure THEN
830 x_parameters := 'ERROR_STRING='||x_error_description||'#XDP#';
831 x_error_description := x_error_description ||' - '||lv_fe_name;
832
833 XDP_ERRORS_PKG.Set_Message(
834 p_object_type => 'FA',
835 p_object_key => p_fa_instance_id,
836 p_message_name => 'XDP_FA_PROV_ERROR',
837 p_message_parameters => x_parameters);
838
839 UPDATE_XDP_FA_STATUS('ERROR',p_fa_instance_id);
840
841 WHEN OTHERS THEN
842 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
843 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'XDP_ORDER_SYNC.Execute_FA_Sync', 'Unexpected exception '||SQLCODE);
844 END IF;
845 x_return_code := SQLCODE;
846 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
847 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDP_ORDER_SYNC.Execute_FA_Sync');
848 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
849
850 x_error_description := FND_MESSAGE.GET ||' - '||lv_fe_name;
851
852 x_parameters := 'ERROR_STRING='||SQLERRM||'#XDP#';
853 XDP_ERRORS_PKG.Set_Message(
854 p_object_type => 'FA',
855 p_object_key => p_fa_instance_id,
856 p_message_name => 'XDP_FA_PROV_ERROR',
857 p_message_parameters => x_parameters);
858
859 UPDATE_XDP_FA_STATUS('ERROR',p_fa_instance_id);
860
861 END Execute_FA_Sync;
862
863 End XDP_ORDER_SYNC;
864