[Home] [Help]
PACKAGE BODY: APPS.XDP_AQ_UTILITIES
Source
1 PACKAGE BODY XDP_AQ_UTILITIES AS
2 /* $Header: XDPAQUTB.pls 120.1 2005/06/08 23:41:20 appldev $ */
3
4 /** Private Functions **/
5
6 FUNCTION GetWIProvisioningDate(p_workitem_instance_id IN NUMBER) RETURN DATE;
7
8 FUNCTION IS_AVAILABLE( object_id IN NUMBER
9 ,object_type IN VARCHAR2 )
10 RETURN BOOLEAN;
11
12 /** End of Private Functions **/
13
14 invalid_rowid exception;
15 pragma exception_init(invalid_rowid, -01410);
16
17 TYPE ROWID_TABLE is TABLE OF ROWID
18 index by BINARY_INTEGER;
19
20 G_XDP_SCHEMA VARCHAR2(80);
21 G_XNP_SCHEMA VARCHAR2(80);
22 G_DQ_COUNT NUMBER := 1000 ;
23
24 g_logdir VARCHAR2(100);
25 g_logdate DATE;
26 g_APPS_MAINTENANCE_MODE VARCHAR2(10);
27 --
28 -- Start an AQ to handle pipe handover between WFs
29 --
30 PROCEDURE Start_WF_AQ(
31 p_return_code OUT NOCOPY NUMBER,
32 p_error_description OUT NOCOPY VARCHAR2)
33 IS
34 CURSOR lc_aq IS
35 select
36 internal_q_name,
37 queue_table_name,
38 payload_type,
39 exception_queue_name,
40 NVL(max_retries,0) max_retries,
41 is_aq_flag
42 from
43 xdp_dq_configuration;
44
45 BEGIN
46
47 p_return_code := 0;
48 FOR lv_aq_rec IN lc_aq LOOP
49 IF lv_aq_rec.is_aq_flag = 'Y' then
50 Start_WF_AQ(
51 p_queue_name => lv_aq_rec.internal_q_name,
52 p_queue_table => lv_aq_rec.queue_table_name,
53 p_payload => lv_aq_rec.payload_type,
54 p_expq_name => lv_aq_rec.exception_queue_name,
55 p_max_retries => lv_aq_rec.max_retries,
56 p_return_code => p_return_code,
57 p_error_description => p_error_description);
58 END IF;
59 IF p_return_code <> 0 THEN
60 exit;
61 END IF;
62
63 ENABLE_SDP_AQ(
64 lv_aq_rec.internal_q_name,
65 p_return_code,
66 p_error_description);
67
68 IF p_return_code <> 0 THEN
69 exit;
70 END IF;
71
72 END LOOP;
73
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 p_return_code := SQLCODE;
78 p_error_description := SQLERRM;
79 END Start_WF_AQ;
80
81 --
82 -- Start an AQ
83 --
84 PROCEDURE Start_WF_AQ(
85 p_queue_name IN varchar2,
86 p_queue_table IN varchar2,
87 p_payload IN varchar2,
88 p_expq_name IN varchar2 DEFAULT NULL,
89 p_max_retries IN number default 0,
90 p_return_code OUT NOCOPY NUMBER,
91 p_error_description OUT NOCOPY VARCHAR2)
92 IS
93 lv_storage varchar2(80) := 'INITRANS 3 MAXTRANS 6';
94 BEGIN
95 p_return_code := 0;
96
97 /* Create queue table */
98 BEGIN
99 DBMS_AQADM.CREATE_QUEUE_TABLE(
100 queue_table => p_queue_table,
101 queue_payload_type => p_payload,
102 sort_list => 'priority,enq_time',
103 storage_clause => lv_storage);
104 Exception
105 when others then
106 /* ignore queue table exists error*/
107 if SQLCODE <> -24001 then
108 raise;
109 end if;
110 End;
111
112 -- Create the queue
113 BEGIN
114 DBMS_AQADM.CREATE_QUEUE(
115 queue_name => p_queue_name,
116 queue_table => p_queue_table,
117 max_retries => p_max_retries);
118 Exception
119 when others then
120 /* ignore queue exists error*/
121 if SQLCODE <> -24006 then
122 raise;
123 end if;
124 End;
125
126 -- Enable enqueue and dequeue operations for SimpleQ.
127 DBMS_AQADM.START_QUEUE(p_queue_name);
128
129 -- Create an exception queue
130 IF p_expq_name IS NOT NULL THEN
131 BEGIN
132 DBMS_AQADM.CREATE_QUEUE(
133 queue_name => p_expq_name,
134 queue_table => p_queue_table,
135 queue_type => DBMS_AQADM.EXCEPTION_QUEUE,
136 comment => 'Exception queue for '||p_queue_name);
137 Exception
138 when others then
139 /* ignore queue exists error*/
140 if SQLCODE <> -24006 then
141 raise;
142 end if;
143 End;
144
145 /* Enable dequeue operations for exceptionQ.*/
146 DBMS_AQADM.START_QUEUE(p_expq_name,FALSE,TRUE);
147 END IF;
148
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 p_return_code := SQLCODE;
153 p_error_description := SQLERRM;
154
155 END START_WF_AQ;
156
157 --
158 -- Stop the WF AQs
159 --
160 PROCEDURE STOP_WF_AQ(
161 p_return_code OUT NOCOPY NUMBER,
162 p_error_description OUT NOCOPY VARCHAR2)
163 IS
164 CURSOR lc_aq IS
165 select
166 internal_q_name
167 from
168 xdp_dq_configuration
169 where
170 is_aq_flag = 'Y';
171
172 BEGIN
173
174 p_return_code := 0;
175 FOR lv_aq_rec IN lc_aq LOOP
176 DBMS_AQADM.STOP_QUEUE(queue_name => lv_aq_rec.internal_q_name);
177 END LOOP;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 p_return_code := SQLCODE;
182 p_error_description := SQLERRM;
183 END STOP_WF_AQ;
184 --
185 -- Drop the WF AQs
186 --
187 PROCEDURE DROP_WF_AQ(
188 p_return_code OUT NOCOPY NUMBER,
189 p_error_description OUT NOCOPY VARCHAR2)
190 IS
191 CURSOR lc_aq IS
192 select
193 distinct queue_table_name
194 from
195 xdp_dq_configuration
196 where
197 is_aq_flag = 'Y';
198
199 BEGIN
200
201 p_return_code := 0;
202 FOR lv_aq_rec IN lc_aq LOOP
203 DBMS_AQADM.DROP_QUEUE_TABLE(
204 queue_table => lv_aq_rec.queue_table_name,
205 force => TRUE);
206
207 END LOOP;
208 commit;
209 EXCEPTION
210 WHEN OTHERS THEN
211 p_return_code := SQLCODE;
212 p_error_description := SQLERRM;
213 END DROP_WF_AQ;
214
215 --
216 -- Add order to pending queue
217 --
218 /*************** Commented out by SPUSEGAO as pending order Queue has been removed
219
220 PROCEDURE Pending_Order_EQ(
221 p_order_id IN NUMBER,
222 p_prov_date IN DATE,
223 p_priority IN NUMBER DEFAULT 100,
224 p_return_code OUT NOCOPY NUMBER,
225 p_error_description OUT NOCOPY VARCHAR2)
226 IS
227 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
228 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
229 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
230 lv_MsgID RAW(16);
231 lv_date date := sysdate;
232
233 BEGIN
234
235 p_return_code := 0;
236 lv_wf_object := SYSTEM.XDP_WF_CHANNELQ_TYPE(
237 NULL,
238 NULL,
239 NULL,
240 NULL,
241 NULL,
242 p_order_id,
243 NULL,
244 NULL);
245
246 -- Enqueue it with the commit on a seperate transaction.
247 lv_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
248 lv_MessageProperties.exception_queue := 'XDP_PENDING_ORDER_EXPQ';
249 lv_MessageProperties.priority := NVL(p_priority,100);
250 if p_prov_date > lv_date then
251 lv_MessageProperties.delay :=
252 (p_prov_date - lv_date)*24*60*60;
253 else
254 lv_MessageProperties.delay := 0;
255 end if;
256
257 DBMS_AQ.ENQUEUE(
258 queue_name => G_XDP_SCHEMA||'.'||'XDP_PENDING_ORDER_QUEUE',
259 enqueue_options => lv_EnqueueOptions,
260 message_properties => lv_MessageProperties,
261 payload =>lv_wf_object,
262 msgid => lv_MsgID);
263
264 update XDP_ORDER_HEADERS
265 set
266 last_updated_by = FND_GLOBAL.USER_ID,
267 last_update_date = sysdate,
268 last_update_login = FND_GLOBAL.LOGIN_ID,
269 STATE = 'WAIT',
270 MSGID = lv_MsgID
271 where
272 order_id = p_order_id;
273
274 update XDP_ORDER_LINE_ITEMS
275 set
276 last_updated_by = FND_GLOBAL.USER_ID,
277 last_update_date = sysdate,
278 last_update_login = FND_GLOBAL.LOGIN_ID,
279 STATE = 'WAIT'
280 where
281 order_id = p_order_id and
282 state = 'PREPROCESS';
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 p_return_code := SQLCODE;
287 p_error_description := SQLERRM;
288 END Pending_Order_EQ;
289
290 ******************************/
291
292 --
293 -- Dequeue an order from the pending order queue
294 --
295 PROCEDURE Pending_Order_DQ
296 IS
297 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
298 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
299 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
300 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
301 lv_MsgID RAW(16);
302 lv_return_code NUMBER;
303 lv_count2 NUMBER;
304 lv_order_id NUMBER;
305 lv_priority NUMBER;
306 lv_error_description VARCHAR2(2000);
307 lv_queue_state varchar2(200);
308 lv_state varchar2(80);
309 lv_state2 varchar2(80);
310 lv_item_type varchar2(80);
311 lv_item_key varchar2(300);
312 lv_ret number;
313 lv_err varchar2(800);
314 lv_prov_date date;
315
316 cursor c_GetPendingOrders is
317 select msgid
318 from xdp_pending_order_qtab;
319
320 BEGIN
321
322 for v_GetPendingOrders in c_GetPendingOrders LOOP
323 -- while 1=1 LOOP
324 -- Check queue state
325 lv_queue_state := Get_Queue_State('XDP_PENDING_ORDER_QUEUE');
326
327 IF lv_queue_state = 'ENABLED' THEN -- proceed
328 savepoint pending_q_tag;
329 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
330 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
331 lv_DequeueOptions.dequeue_mode := DBMS_AQ.LOCKED;
332 lv_DequeueOptions.MSGID := NULL;
333 lv_DequeueOptions.MSGID := v_GetPendingOrders.msgid;
334
335 BEGIN
336 -- Set Dequeue time out to be 1 second
337 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
338 DBMS_AQ.DEQUEUE(
339 queue_name => G_XDP_SCHEMA||'.'||'XDP_PENDING_ORDER_QUEUE',
340 dequeue_options => lv_DequeueOptions,
341 message_properties => lv_MessageProperties,
342 payload => lv_wf_object,
343 msgid => lv_MsgID);
344 EXCEPTION
345 WHEN e_QTimeOut Then
346 null;
347 GOTO l_continue_loop;
348 WHEN OTHERS THEN
349 rollback to pending_q_tag;
350 handle_dq_exception(
351 p_MESSAGE_ID => lv_MSGID ,
352 p_WF_ITEM_TYPE => null,
353 p_WF_ITEM_KEY => null,
354 p_CALLER_NAME => 'Pending_Order_DQ',
355 p_CALLBACK_TEXT => NULL ,
356 p_Q_NAME => 'XDP_PENDING_ORDER_QUEUE',
357 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
358 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
359 END;
360
361 select state,previous_order_id,priority, provisioning_date
362 into lv_state,lv_order_id,lv_priority, lv_prov_date
363 from xdp_order_headers
364 where
365 order_id = lv_wf_object.order_id;
366
367 if lv_state = 'SUSPENDED' THEN
368 -- Update the msg delay to new high number
369 null;
370 rollback to pending_q_tag;
371 elsif lv_state IN ('WAIT','HOLD') THEN
372 if lv_order_id is not null then
373 begin
374 select state into lv_state2
375 from xdp_order_headers
376 where order_id = lv_order_id;
377 exception
378 when no_data_found then
379 lv_state2 := 'CANCELED';
380 end;
381 else
382 lv_state2 := 'CANCELED';
383 end if;
384 if lv_state2 not in( 'COMPLETED','CANCELED') THEN
385 rollback to pending_q_tag;
386 ELSE -- No dependency found
387 BEGIN
388 XDPCORE.CreateOrderProcess(
389 lv_wf_object.order_id,
390 lv_item_type,
391 lv_item_key);
392 EXCEPTION
393 when others then
394 rollback to pending_q_tag;
395 handle_dq_exception(
396 p_MESSAGE_ID => lv_MSGID ,
397 p_WF_ITEM_TYPE => null,
398 p_WF_ITEM_KEY => null,
399 p_CALLER_NAME => 'Pending_Order_DQ',
400 p_CALLBACK_TEXT => NULL ,
401 p_Q_NAME => 'XDP_PENDING_ORDER_QUEUE',
402 p_ERROR_DESCRIPTION => 'Can not create workflow: ' || SQLERRM);
403 raise_application_error(-20530,
404 'Can not create workflow: ' || SQLERRM);
405 END;
406
407 Add_OrderToProcessorQ(
408 lv_wf_object.order_id,
409 null,
410 lv_priority,
411 lv_prov_date,
412 lv_item_type,
413 lv_item_key);
414
415 IF lv_ret <> 0 THEN
416 ROLLBACK to pending_q_tag;
417 handle_dq_exception(
418 p_MESSAGE_ID => lv_MSGID ,
419 p_WF_ITEM_TYPE => null,
420 p_WF_ITEM_KEY => null,
421 p_CALLER_NAME => 'Pending_Order_DQ',
422 p_CALLBACK_TEXT => NULL ,
423 p_Q_NAME => 'XDP_PENDING_ORDER_QUEUE',
424 p_ERROR_DESCRIPTION => lv_err);
425 raise_application_error(-20530,lv_err);
426 ELSE
427 lv_DequeueOptions.msgid := lv_MsgID;
428 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
429 DBMS_AQ.DEQUEUE(
430 queue_name => G_XDP_SCHEMA||'.'||'XDP_PENDING_ORDER_QUEUE',
431 dequeue_options => lv_DequeueOptions,
432 message_properties => lv_MessageProperties,
433 payload => lv_tmp,
434 msgid => lv_MsgID);
435 COMMIT;
436 END IF;
437 END IF;
438 END IF;
439
440 ELSIF lv_queue_state = 'SUSPENDED' THEN -- notify dequeuer to sleep
441 dbms_lock.sleep(3);
442 ELSIF lv_queue_state = 'SHUTDOWN' THEN -- notify dequeuer to exit
443 return;
444 ELSIF lv_queue_state = 'DISABLED' THEN -- notify dequeuer to exit
445 dbms_lock.sleep(3);
446 ELSIF lv_queue_state = 'DATA_NOT_FOUND' THEN -- notify dequeuer to exit
447 return;
448 ELSE -- notify dequeuer to exit
449 return;
450 END IF;
451 <<l_continue_loop>>
452 null;
453
454 END LOOP;
455
456 END Pending_Order_DQ;
457
458
459 /*************** Commented out by SPUSEGAO as pending order Queue has been removed
460 --
461 -- Dequeue an order from the pending order queue
462 --
463 PROCEDURE Pending_Order_DQ (p_message_wait_timeout IN NUMBER DEFAULT 1,
464 p_correlation_id IN VARCHAR2,
465 x_message_key OUT NOCOPY VARCHAR2,
466 x_queue_timed_out OUT NOCOPY VARCHAR2 )
467 IS
468 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
469 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
470 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
471 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
472 lv_MsgID RAW(16);
473 lv_return_code NUMBER;
474 lv_count2 NUMBER;
475 lv_order_id NUMBER;
476 lv_priority NUMBER;
477 lv_error_description VARCHAR2(2000);
478 lv_queue_state varchar2(200);
479 lv_state varchar2(80);
480 lv_state2 varchar2(80);
481 lv_item_type varchar2(80);
482 lv_item_key varchar2(300);
483 lv_ret number;
484 lv_err varchar2(800);
485 lv_prov_date date;
486
487 BEGIN
488
489 savepoint pending_q_tag;
490 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
491 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
492 lv_DequeueOptions.dequeue_mode := DBMS_AQ.LOCKED;
493 lv_DequeueOptions.MSGID := NULL;
494 lv_DequeueOptions.correlation := p_correlation_id;
495
496 BEGIN
497 -- Set Dequeue time out
498 lv_DequeueOptions.wait := p_message_wait_timeout;
499 DBMS_AQ.DEQUEUE(
500 queue_name => G_XDP_SCHEMA||'.'||'XDP_PENDING_ORDER_QUEUE',
501 dequeue_options => lv_DequeueOptions,
502 message_properties => lv_MessageProperties,
503 payload => lv_wf_object,
504 msgid => lv_MsgID);
505 EXCEPTION
506 WHEN e_QTimeOut Then
507 -- raise e_NothingToDequeueException;
508 x_queue_timed_out := 'Y';
509 return;
510 WHEN OTHERS THEN
511 rollback to pending_q_tag;
512 handle_dq_exception(
513 p_MESSAGE_ID => lv_MSGID ,
514 p_WF_ITEM_TYPE => null,
515 p_WF_ITEM_KEY => null,
516 p_CALLER_NAME => 'Pending_Order_DQ',
517 p_CALLBACK_TEXT => NULL ,
518 p_Q_NAME => 'XDP_PENDING_ORDER_QUEUE',
519 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
520 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
521 END;
522
523 -- Set the Context to the Sender name of the Message
524 -- SetContext(lv_MessageProperties.sender_id.name)
525
526 select state,previous_order_id,priority, provisioning_date
527 into lv_state,lv_order_id,lv_priority,lv_prov_date
528 from xdp_order_headers
529 where
530 order_id = lv_wf_object.order_id;
531
532 if lv_state = 'SUSPENDED' THEN
533 --- Update the msg delay to new high number
534 null;
535 rollback to pending_q_tag;
536 elsif lv_state IN ('WAIT','HOLD') THEN
537 if lv_order_id is not null then
538 begin
539 select state into lv_state2
540 from xdp_order_headers
541 where order_id = lv_order_id;
542 exception
543 when no_data_found then
544 lv_state2 := 'CANCELED';
545 end;
546 else
547 lv_state2 := 'CANCELED';
548 end if;
549 if lv_state2 not in( 'COMPLETED','CANCELED') THEN
550 rollback to pending_q_tag;
551 ELSE ---- No dependency found
552 BEGIN
553 XDPCORE.CreateOrderProcess(
554 lv_wf_object.order_id,
555 lv_item_type,
556 lv_item_key);
557 exception
558 when others then
559 rollback to pending_q_tag;
560 handle_dq_exception(
561 p_MESSAGE_ID => lv_MSGID ,
562 p_WF_ITEM_TYPE => null,
563 p_WF_ITEM_KEY => null,
564 p_CALLER_NAME => 'Pending_Order_DQ',
565 p_CALLBACK_TEXT => NULL ,
566 p_Q_NAME => 'XDP_PENDING_ORDER_QUEUE',
567 p_ERROR_DESCRIPTION => 'Can not create workflow: ' || SQLERRM);
568 raise_application_error(-20530,
569 'Can not create workflow: ' || SQLERRM);
570 end;
571
572 Add_OrderToProcessorQ(
573 lv_wf_object.order_id,
574 null,
575 lv_priority,
576 lv_prov_date,
577 lv_item_type,
578 lv_item_key);
579
580 IF lv_ret <> 0 THEN
581 ROLLBACK to pending_q_tag;
582 handle_dq_exception(
583 p_MESSAGE_ID => lv_MSGID ,
584 p_WF_ITEM_TYPE => null,
585 p_WF_ITEM_KEY => null,
586 p_CALLER_NAME => 'Pending_Order_DQ',
587 p_CALLBACK_TEXT => NULL ,
588 p_Q_NAME => 'XDP_PENDING_ORDER_QUEUE',
589 p_ERROR_DESCRIPTION => lv_err);
590 raise_application_error(-20530,lv_err);
591 ELSE
592 lv_DequeueOptions.msgid := lv_MsgID;
593 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
594 DBMS_AQ.DEQUEUE(
595 queue_name => G_XDP_SCHEMA||'.'||'XDP_PENDING_ORDER_QUEUE',
596 dequeue_options => lv_DequeueOptions,
597 message_properties => lv_MessageProperties,
598 payload => lv_tmp,
599 msgid => lv_MsgID);
600 COMMIT;
601 END IF;
602 END IF;
603 END IF;
604
605 EXCEPTION
606 WHEN e_NothingToDequeueException then
607 x_queue_timed_out := 'Y';
608 WHEN OTHERS THEN
609 RAISE;
610 END Pending_Order_DQ;
611
612 ******************************/
613
614 --
615 -- Add order to order processor queue
616 --
617 PROCEDURE Add_OrderToProcessorQ(
618 p_order_id IN NUMBER ,
619 p_order_type in varchar2 default null,
620 p_priority IN NUMBER DEFAULT 100,
621 p_prov_date IN DATE default sysdate,
622 p_wf_item_type IN varchar2,
623 p_wf_item_key IN Varchar2)
624
625 IS
626 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
627 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
628 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
629 lv_MsgID RAW(16);
630
631 lv_date date := sysdate;
632
633 BEGIN
634
635 lv_wf_object := SYSTEM.XDP_WF_CHANNELQ_TYPE(
636 NULL,
637 NULL,
638 p_wf_item_type,
639 p_wf_item_key,
640 NULL,
641 p_order_id,
642 NULL,
643 NULL);
644
645 -- Enqueue it with the commit on a seperate transaction.
646 lv_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
647 lv_MessageProperties.exception_queue := G_XDP_SCHEMA||'.'||'XDP_ORDER_PROCESSOR_EXPQ';
648 lv_MessageProperties.priority := NVL(p_priority,100);
649
650 if p_prov_date > lv_date then
651 lv_MessageProperties.delay :=
652 (p_prov_date - lv_date)*24*60*60;
653 else
654 lv_MessageProperties.delay := 0;
655 end if;
656
657 -- Set the Correlation ID Message Property
658 lv_MessageProperties.correlation := p_order_type;
659
660 DBMS_AQ.ENQUEUE(
661 queue_name => G_XDP_SCHEMA||'.'||'XDP_ORDER_PROC_QUEUE',
662 enqueue_options => lv_EnqueueOptions,
663 message_properties => lv_MessageProperties,
664 payload =>lv_wf_object,
665 msgid => lv_MsgID);
666
667
668 update XDP_ORDER_HEADERS
669 set last_updated_by = FND_GLOBAL.USER_ID,
670 last_update_date = sysdate,
671 last_update_login = FND_GLOBAL.LOGIN_ID,
672 status_code = 'READY',
673 MSGID = lv_MsgID
674 where order_id = p_order_id;
675
676 update XDP_ORDER_LINE_ITEMS
677 set last_updated_by = FND_GLOBAL.USER_ID,
678 last_update_date = sysdate,
679 last_update_login = FND_GLOBAL.LOGIN_ID,
680 status_code = 'READY'
681 where order_id = p_order_id
682 and status_code = 'STANDBY';
683
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 xdp_utilities.generic_error('XDP_AQ_UTILITIES.Add_OrdertoProcessorQ'
688 ,xdp_order.G_external_order_reference
689 ,sqlcode
690 ,sqlerrm);
691 END Add_OrderToProcessorQ;
692
693 /**** Commented as this code is executed by c dequeuers 07/25/2001
694
695 -- Dequeue from order processor queue
696 --
697 PROCEDURE Start_OrderProcessor_Workflow
698 IS
699 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
700 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
701 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
702 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
703 lv_MsgID RAW(16);
704 lv_return_code NUMBER;
705 lv_count2 NUMBER;
706 lv_error_description VARCHAR2(2000);
707 lv_queue_state varchar2(200);
708 lv_state varchar2(80);
709 lv_state2 varchar2(80);
710
711 BEGIN
712
713 while 1=1 loop
714 -- Check queue state
715 lv_queue_state := Get_Queue_State('XDP_ORDER_PROC_QUEUE');
716 IF lv_queue_state = 'ENABLED' THEN -- proceed
717 savepoint order_q_tag;
718
719 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
720 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
721 lv_DequeueOptions.dequeue_mode := DBMS_AQ.LOCKED;
722 lv_DequeueOptions.MSGID := NULL;
723
724 BEGIN
725 -- Set Dequeue time out to be 1 second
726 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
727
728 DBMS_AQ.DEQUEUE(
729 queue_name => G_XDP_SCHEMA||'.'||'XDP_ORDER_PROC_QUEUE',
730 dequeue_options => lv_DequeueOptions,
731 message_properties => lv_MessageProperties,
732 payload => lv_wf_object,
733 msgid => lv_MsgID);
734 EXCEPTION
735 WHEN e_QTimeOut Then
736 null;
737 GOTO l_continue_loop;
738 WHEN OTHERS THEN
739 rollback to order_q_tag;
740 handle_dq_exception(
741 p_MESSAGE_ID => lv_MSGID ,
742 p_WF_ITEM_TYPE => null,
743 p_WF_ITEM_KEY => null,
744 p_CALLER_NAME => 'Start_OrderProcessor_Workflow',
745 p_CALLBACK_TEXT => NULL ,
746 p_Q_NAME => 'XDP_ORDER_PROC_QUEUE',
747 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
748 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
749 END;
750
751 SELECT status_code
752 INTO lv_state
753 FROM xdp_order_headers
754 WHERE order_id = lv_wf_object.order_id;
755
756 -- if lv_state = 'SUSPENDED' THEN
757 -- -- Update the msg delay to new high number
758 -- null;
759 -- rollback to order_q_tag;
760
761 if lv_state IN ('READY') THEN
762 BEGIN
763 lv_DequeueOptions.msgid := lv_MsgID;
764 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
765
766 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_ORDER_PROC_QUEUE',
767 dequeue_options => lv_DequeueOptions,
768 message_properties => lv_MessageProperties,
769 payload => lv_tmp,
770 msgid => lv_MsgID);
771
772 WF_ENGINE.StartProcess(
773 lv_wf_object.wf_item_type,
774 lv_wf_object.wf_item_key);
775 COMMIT;
776 exception
777 when others then
778 rollback to order_q_tag;
779 handle_dq_exception( p_MESSAGE_ID => lv_MSGID ,
780 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
781 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
782 p_CALLER_NAME => 'Start_OrderProcessor_Workflow',
783 p_CALLBACK_TEXT => NULL ,
784 p_Q_NAME => 'XDP_ORDER_PROC_QUEUE',
785 p_ERROR_DESCRIPTION => 'Can not start workflow: ' || SQLERRM);
786 raise_application_error(-20530,
787 'Can not create workflow: ' || SQLERRM);
788 end;
789 else
790 rollback to order_q_tag;
791
792 end if;
793
794 ELSIF lv_queue_state = 'SUSPENDED' THEN --otify dequeuer to sleep
795 dbms_lock.sleep(3);
796 ELSIF lv_queue_state = 'SHUTDOWN' THEN -- notify dequeuer to exit
797 return;
798 ELSIF lv_queue_state = 'DISABLED' THEN -- notify dequeuer to exit
799 dbms_lock.sleep(3);
800 ELSIF lv_queue_state = 'DATA_NOT_FOUND' THEN -- notify dequeuer to exit
801 return;
802 ELSE -- notify dequeuer to exit
803 return;
804 END IF;
805
806 <<l_continue_loop>>
807 null;
808 END LOOP;
809
810 END Start_OrderProcessor_Workflow;
811
812 **************/
813
814 PROCEDURE Start_OrderProcessor_workflow (p_message_wait_timeout IN NUMBER DEFAULT 1,
815 p_correlation_id IN VARCHAR2,
816 x_message_key OUT NOCOPY VARCHAR2,
817 x_queue_timed_out OUT NOCOPY VARCHAR2 )
818
819 IS
820 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
821 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
822 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
823 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
824 lv_MsgID RAW(16);
825 lv_return_code NUMBER;
826 lv_error_description VARCHAR2(2000);
827
828 BEGIN
829
830 savepoint order_q_tag;
831 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
832 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
833 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE;
834 lv_DequeueOptions.MSGID := NULL;
835 lv_DequeueOptions.correlation := p_correlation_id;
836
837 BEGIN
838 -- Set Dequeue time out to be 1 second
839 lv_DequeueOptions.wait := p_message_wait_timeout;
840
841 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_ORDER_PROC_QUEUE',
842 dequeue_options => lv_DequeueOptions,
843 message_properties => lv_MessageProperties,
844 payload => lv_wf_object,
845 msgid => lv_MsgID);
846
847 EXCEPTION
848 WHEN e_QTimeOut Then
849 x_queue_timed_out := 'Y';
850 return;
851 WHEN OTHERS THEN
852 rollback to order_q_tag;
853 handle_dq_exception(
854 p_MESSAGE_ID => lv_MSGID ,
855 p_WF_ITEM_TYPE => null,
856 p_WF_ITEM_KEY => null,
857 p_CALLER_NAME => 'Start_OrderProcessor_Workflow',
858 p_CALLBACK_TEXT => NULL ,
859 p_Q_NAME => 'XDP_ORDER_PROC_QUEUE',
860 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
861
862 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
863 END;
864
865 BEGIN
866 SET_CONTEXT( lv_wf_object.order_id, 'ORDER_OBJECT');
867
868 EXCEPTION
869 WHEN stop_processing THEN
870 x_queue_timed_out := 'Y';
871 return;
872 END;
873
874 BEGIN
875
876 wf_engine.startprocess( lv_wf_object.wf_item_type,
877 lv_wf_object.wf_item_key);
878
879 COMMIT;
880 EXCEPTION
881 WHEN OTHERS THEN
882 rollback to order_q_tag;
883 handle_dq_exception(
884 p_MESSAGE_ID => lv_MSGID ,
885 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
886 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
887 p_CALLER_NAME => 'Start_OrderProcessor_Workflow',
888 p_CALLBACK_TEXT => NULL ,
889 p_Q_NAME => 'XDP_ORDER_PROC_QUEUE',
890 p_ERROR_DESCRIPTION => 'Can not start workflow: ' || SQLERRM);
891
892 raise_application_error(-20530,'Can not create workflow: '||SQLERRM);
893 END;
894
895 EXCEPTION
896 WHEN e_NothingToDequeueException then
897 x_queue_timed_out := 'Y';
898 WHEN OTHERS THEN
899 RAISE;
900 END Start_OrderProcessor_workflow;
901 --
902 -- Allow API to start workitem WF through enqueue
903 --
904 PROCEDURE Add_WorkItem_ToQ(
905 p_order_id IN NUMBER,
906 p_wi_instance_id IN NUMBER,
907 p_prov_date IN DATE,
908 p_wf_item_type IN VARCHAR2 ,
909 p_wf_item_key VARCHAR2,
910 p_priority number DEFAULT 100,
911 p_return_code OUT NOCOPY NUMBER,
912 p_error_description OUT NOCOPY VARCHAR2)
913
914 IS
915 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
916 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
917 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
918 lv_MsgID RAW(16);
919 lv_date date := sysdate;
920
921 -- Remove Later??
922 lv_workitem_name varchar2(40);
923 cursor c_GetWorkitemName is
924 select xw.workitem_name workitem_name
925 from xdp_workitems xw,
926 xdp_fulfill_worklist xfw
927 where xfw.workitem_instance_id = p_wi_instance_id
928 and xfw.workitem_id = xw.workitem_id;
929
930 BEGIN
931
932 p_return_code := 0;
933 lv_wf_object := SYSTEM.XDP_WF_CHANNELQ_TYPE(
934 NULL,
935 NULL,
936 p_wf_item_type,
937 p_wf_item_key,
938 NULL,
939 p_order_id,
940 p_wi_instance_id,
941 NULL);
942
943 -- Get Work Item for Correlation ID
944 for v_GetWorkitemName in c_GetWorkitemName loop
945 lv_workitem_name := v_GetWorkitemName.workitem_name;
946 end loop;
947
948 -- Set the Correlation ID Message Property
949 lv_MessageProperties.correlation := lv_workitem_name;
950
951 -- Enqueue it with the commit on a seperate transaction.
952 lv_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
953 lv_MessageProperties.exception_queue := G_XDP_SCHEMA||'.'||'XDP_WORKITEM_EXPQ';
954 lv_MessageProperties.priority := NVL(p_priority,100);
955
956 if p_prov_date > lv_date then
957 lv_MessageProperties.delay := (p_prov_date - lv_date)*24*60*60;
958 else
959 lv_MessageProperties.delay := 0;
960 end if;
961
962 DBMS_AQ.ENQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WORKITEM_QUEUE',
963 enqueue_options => lv_EnqueueOptions,
964 message_properties => lv_MessageProperties,
965 payload =>lv_wf_object,
966 msgid => lv_MsgID);
967
968 update XDP_FULFILL_WORKLIST
969 set last_updated_by = FND_GLOBAL.USER_ID,
970 last_update_date = sysdate,
971 last_update_login = FND_GLOBAL.LOGIN_ID,
972 STATUS_CODE = 'READY',
973 MSGID = lv_MsgID
974 where workitem_instance_id = p_wi_instance_id;
975
976 EXCEPTION
977 WHEN OTHERS THEN
978 p_return_code := SQLCODE;
979 p_error_description := SQLERRM;
980 END Add_WorkItem_ToQ;
981
982 /***** Commented out as this code is executed by C dequeuer
983
984 -- Dequeue from workitem queue
985 --
986 Procedure Start_Workitem_Workflow
987 IS
988 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
989 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
990 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
991 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
992 lv_MsgID RAW(16);
993 lv_return_code NUMBER;
994 lv_count2 NUMBER;
995 lv_error_description VARCHAR2(2000);
996 lv_queue_state varchar2(200);
997 lv_state varchar2(200);
998
999 BEGIN
1000
1001 while 1=1 loop
1002 lv_queue_state := Get_Queue_State('XDP_WORKITEM_QUEUE');
1003 IF lv_queue_state = 'ENABLED' THEN -- proceed
1004 savepoint workitem_q_tag;
1005
1006 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1007 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1008 lv_DequeueOptions.dequeue_mode := DBMS_AQ.LOCKED;
1009 lv_DequeueOptions.MSGID := NULL;
1010
1011 BEGIN
1012 -- Set Dequeue time out to be 1 second
1013 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
1014
1015 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WORKITEM_QUEUE',
1016 dequeue_options => lv_DequeueOptions,
1017 message_properties => lv_MessageProperties,
1018 payload => lv_wf_object,
1019 msgid => lv_MsgID);
1020 EXCEPTION
1021 WHEN e_QTimeOut Then
1022 null;
1023 GOTO l_continue_loop;
1024 WHEN OTHERS THEN
1025 rollback to workitem_q_tag;
1026 handle_dq_exception( p_MESSAGE_ID => lv_MSGID ,
1027 p_WF_ITEM_TYPE => null,
1028 p_WF_ITEM_KEY => null,
1029 p_CALLER_NAME => 'Start_WORKITEM_Workflow',
1030 p_CALLBACK_TEXT => NULL ,
1031 p_Q_NAME => 'XDP_WORKITEM_QUEUE',
1032 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1033
1034 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1035 END;
1036
1037 select status_code
1038 into lv_state
1039 from xdp_fulfill_worklist
1040 where
1041 workitem_instance_id = lv_wf_object.workitem_instance_id;
1042
1043 -- if lv_state = 'SUSPENDED' THEN
1044 -- -- Update the msg delay to new high number
1045 -- null;
1046 -- rollback to workitem_q_tag;
1047 if lv_state IN ('READY') THEN
1048 BEGIN
1049 lv_DequeueOptions.msgid := lv_MsgID;
1050 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
1051
1052 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WORKITEM_QUEUE',
1053 dequeue_options => lv_DequeueOptions,
1054 message_properties => lv_MessageProperties,
1055 payload => lv_tmp,
1056 msgid => lv_MsgID);
1057
1058 update XDP_FULFILL_WORKLIST
1059 set last_updated_by = FND_GLOBAL.USER_ID,
1060 last_update_date = sysdate,
1061 last_update_login = FND_GLOBAL.LOGIN_ID,
1062 STATUS_CODE = 'IN PROGRESS'
1063 where workitem_instance_id = lv_wf_object.workitem_instance_id;
1064
1065 WF_ENGINE.StartProcess(
1066 lv_wf_object.wf_item_type,
1067 lv_wf_object.wf_item_key);
1068 COMMIT;
1069 exception
1070 when others then
1071 rollback to workitem_q_tag;
1072 handle_dq_exception( p_MESSAGE_ID => lv_MSGID ,
1073 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1074 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1075 p_CALLER_NAME => 'Start_WORKITEM_Workflow',
1076 p_CALLBACK_TEXT => NULL ,
1077 p_Q_NAME => 'XDP_WORKITEM_QUEUE',
1078 p_ERROR_DESCRIPTION => 'Can not start workflow: ' || SQLERRM);
1079
1080 raise_application_error(-20530, 'Can not start workflow: ' || SQLERRM);
1081 end;
1082 else
1083 rollback to workitem_q_tag;
1084 end if;
1085
1086 ELSIF lv_queue_state = 'SUSPENDED' THEN -- Notify dequeuer to sleep
1087 dbms_lock.sleep(3);
1088 ELSIF lv_queue_state = 'SHUTDOWN' THEN -- notify dequeuer to exit
1089 return;
1090 ELSIF lv_queue_state = 'DISABLED' THEN -- notify dequeuer to exit
1091 dbms_lock.sleep(3);
1092 ELSIF lv_queue_state = 'DATA_NOT_FOUND' THEN -- notify dequeuer to exit
1093 return;
1094 ELSE -- notify dequeuer to exit
1095 return;
1096 END IF;
1097
1098 <<l_continue_loop>>
1099 null;
1100 END LOOP;
1101
1102 END Start_Workitem_Workflow;
1103
1104 ********/
1105
1106 PROCEDURE Start_Workitem_Workflow (p_message_wait_timeout IN NUMBER DEFAULT 1,
1107 p_correlation_id IN VARCHAR2,
1108 x_message_key OUT NOCOPY VARCHAR2,
1109 x_queue_timed_out OUT NOCOPY VARCHAR2 )
1110 IS
1111 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1112 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
1113 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
1114 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1115 lv_MsgID RAW(16);
1116
1117 BEGIN
1118
1119 savepoint workitem_q_tag;
1120 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1121 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1122 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE;
1123 lv_DequeueOptions.MSGID := NULL;
1124 lv_DequeueOptions.correlation := p_correlation_id;
1125
1126 BEGIN
1127 -- Set Dequeue time out to be 1 second
1128 lv_DequeueOptions.wait := p_message_wait_timeout;
1129 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WORKITEM_QUEUE',
1130 dequeue_options => lv_DequeueOptions,
1131 message_properties => lv_MessageProperties,
1132 payload => lv_wf_object,
1133 msgid => lv_MsgID);
1134 EXCEPTION
1135 WHEN e_QTimeOut Then
1136 x_queue_timed_out := 'Y';
1137 return;
1138 WHEN OTHERS THEN
1139 rollback to workitem_q_tag;
1140 handle_dq_exception( p_MESSAGE_ID => lv_MSGID ,
1141 p_WF_ITEM_TYPE => null,
1142 p_WF_ITEM_KEY => null,
1143 p_CALLER_NAME => 'Start_WORKITEM_Workflow',
1144 p_CALLBACK_TEXT => NULL ,
1145 p_Q_NAME => 'XDP_WORKITEM_QUEUE',
1146 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1147
1148 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1149
1150 END;
1151
1152 BEGIN
1153 SET_CONTEXT( lv_wf_object.order_id, 'ORDER_OBJECT');
1154
1155 EXCEPTION
1156 WHEN stop_processing THEN
1157 x_queue_timed_out := 'Y';
1158 return;
1159 END;
1160
1161 BEGIN
1162
1163 if lv_wf_object.wf_item_type <> 'XDPPROV' then
1164 XDPSTATUS.UPDATE_XDP_WORKITEM_STATUS(
1165 p_status => 'IN PROGRESS',
1166 p_workitem_instance_id => lv_wf_object.workitem_instance_id);
1167 end if;
1168
1169 -- Commented out as this initialization is done in Work Item workflow by spusegao on 08/07/01
1170 --
1171 -- update XDP_FULFILL_WORKLIST
1172 -- set last_updated_by = FND_GLOBAL.USER_ID,
1173 -- last_update_date = sysdate,
1174 -- last_update_login = FND_GLOBAL.LOGIN_ID,
1175 -- STATUS_CODE = 'IN PROGRESS'
1176 -- where workitem_instance_id = lv_wf_object.workitem_instance_id;
1177
1178 -- Commented out as this evaluation id done in workitem workflow by spusegao on 08/07/01
1179 -- xdp_engine.EvaluateWIParamsOnStart(lv_wf_object.workitem_instance_id);
1180
1181 WF_ENGINE.StartProcess(
1182 lv_wf_object.wf_item_type,
1183 lv_wf_object.wf_item_key);
1184 COMMIT;
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187 rollback to workitem_q_tag;
1188 handle_dq_exception(
1189 p_MESSAGE_ID => lv_MSGID ,
1190 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1191 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1192 p_CALLER_NAME => 'Start_WORKITEM_Workflow',
1193 p_CALLBACK_TEXT => NULL ,
1194 p_Q_NAME => 'XDP_WORKITEM_QUEUE',
1195 p_ERROR_DESCRIPTION => 'Can not start workflow: ' || SQLERRM);
1196
1197 raise_application_error(-20530,'Can not start workflow: '|| SQLERRM);
1198 END;
1199
1200
1201 EXCEPTION
1202 WHEN e_NothingToDequeueException then
1203 x_queue_timed_out := 'Y';
1204 WHEN OTHERS THEN
1205 RAISE;
1206 END Start_Workitem_Workflow;
1207 --
1208 -- Allow workitem workflow to register a FA through eq
1209 --
1210 PROCEDURE Add_FA_ToQ(
1211 p_order_id IN NUMBER,
1212 p_wi_instance_id IN NUMBER,
1213 p_fa_instance_id IN number,
1214 p_wf_item_type in VARCHAR2 ,
1215 p_wf_item_key in VARCHAR2,
1216 p_priority in number default 100,
1217 p_return_code OUT NOCOPY NUMBER,
1218 p_error_description OUT NOCOPY VARCHAR2)
1219 IS
1220 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1221 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
1222 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1223 lv_MsgID RAW(16);
1224 lv_date date := sysdate;
1225
1226 -- Remove Later??
1227 lv_fa_name varchar2(40);
1228 cursor c_GetFAName is
1229 select xf.fulfillment_action fulfillment_action
1230 from xdp_fulfill_actions xf,
1231 xdp_fa_runtime_list xfr
1232 where xfr.fa_instance_id = p_fa_instance_id
1233 and xfr.fulfillment_action_id = xf.fulfillment_action_id;
1234
1235 BEGIN
1236 p_return_code := 0;
1237
1238 lv_wf_object := SYSTEM.XDP_WF_CHANNELQ_TYPE
1239 ( NULL,
1240 NULL,
1241 p_wf_item_type,
1242 p_wf_item_key,
1243 NULL,
1244 p_order_id,
1245 p_wi_instance_id,
1246 p_fa_instance_id);
1247
1248 -- Get Work Item for Correlation ID
1249 for v_GetFAName in c_GetFAName loop
1250 lv_fa_name := v_GetFAName.fulfillment_action;
1251 end loop;
1252
1253 -- Set the Correlation ID Message Property
1254 lv_MessageProperties.correlation := lv_fa_name;
1255
1256 -- Enqueue it with the commit on a seperate transaction.
1257 lv_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
1258 lv_MessageProperties.exception_queue := G_XDP_SCHEMA||'.'||'XDP_FA_EXPQ';
1259 lv_MessageProperties.priority := NVL(p_priority,100);
1260
1261 DBMS_AQ.ENQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_FA_QUEUE',
1262 enqueue_options => lv_EnqueueOptions,
1263 message_properties => lv_MessageProperties,
1264 payload =>lv_wf_object,
1265 msgid => lv_MsgID);
1266
1267 update XDP_FA_RUNTIME_LIST
1268 set last_updated_by = FND_GLOBAL.USER_ID,
1269 last_update_date = sysdate,
1270 last_update_login = FND_GLOBAL.LOGIN_ID,
1271 STATUS_CODE = 'READY',
1272 MSGID = lv_MsgID
1273 where fa_instance_id = p_fa_instance_id;
1274
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 p_return_code := SQLCODE;
1278 p_error_description := SQLERRM;
1279 END Add_FA_ToQ;
1280
1281
1282
1283 /***** Commented out as this code is executed by C dequeuer
1284 --
1285 -- Used by API to start FA workflow
1286 -- through dequeue
1287 --
1288 PROCEDURE Start_FA_Workflow
1289 IS
1290 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1291 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
1292 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
1293 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1294 lv_MsgID RAW(16);
1295 lv_return_code NUMBER;
1296 lv_count2 NUMBER;
1297 lv_error_description VARCHAR2(2000);
1298 lv_queue_state varchar2(200);
1299 lv_state varchar2(200);
1300
1301 BEGIN
1302
1303 while 1=1 loop
1304 lv_queue_state := Get_Queue_State('XDP_FA_QUEUE');
1305 IF lv_queue_state = 'ENABLED' THEN -- proceed
1306 savepoint fa_q_tag;
1307 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1308 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1309 lv_DequeueOptions.dequeue_mode := DBMS_AQ.LOCKED;
1310 lv_DequeueOptions.MSGID := NULL;
1311
1312 BEGIN
1313 -- Set Dequeue time out to be 1 second
1314 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
1315
1316 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_FA_QUEUE',
1317 dequeue_options => lv_DequeueOptions,
1318 message_properties => lv_MessageProperties,
1319 payload => lv_wf_object,
1320 msgid => lv_MsgID);
1321
1322 EXCEPTION
1323 WHEN e_QTimeOut Then
1324 null;
1325 GOTO l_continue_loop;
1326 WHEN OTHERS THEN
1327 rollback to pending_q_tag;
1328 handle_dq_exception( p_MESSAGE_ID => lv_MSGID ,
1329 p_WF_ITEM_TYPE => null,
1330 p_WF_ITEM_KEY => null,
1331 p_CALLER_NAME => 'Start_FA_Workflow',
1332 p_CALLBACK_TEXT => NULL ,
1333 p_Q_NAME => 'XDP_FA_QUEUE',
1334 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1335
1336 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1337 END;
1338
1339 SELECT status_code
1340 INTO lv_state
1341 FROM xdp_fa_runtime_list
1342 WHERE fa_instance_id = lv_wf_object.fa_instance_id;
1343
1344 -- if lv_state = 'SUSPENDED' THEN
1345 -- -- Update the msg delay to new high number
1346 -- null;
1347 -- rollback to fa_q_tag;
1348 if lv_state = 'READY' THEN
1349 BEGIN
1350 lv_DequeueOptions.msgid := lv_MsgID;
1351 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
1352
1353 DBMS_AQ.DEQUEUE(queue_name => G_XDP_SCHEMA||'.'||'XDP_FA_QUEUE',
1354 dequeue_options => lv_DequeueOptions,
1355 message_properties => lv_MessageProperties,
1356 payload => lv_tmp,
1357 msgid => lv_MsgID);
1358
1359 WF_ENGINE.StartProcess(
1360 lv_wf_object.wf_item_type,
1361 lv_wf_object.wf_item_key);
1362 COMMIT;
1363 EXCEPTION
1364 when others then
1365 rollback to fa_q_tag;
1366 handle_dq_exception( p_MESSAGE_ID => lv_MSGID ,
1367 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1368 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1369 p_CALLER_NAME => 'Start_FA_Workflow',
1370 p_CALLBACK_TEXT => NULL ,
1371 p_Q_NAME => 'XDP_FA_QUEUE',
1372 p_ERROR_DESCRIPTION => 'Can not start workflow: ' || SQLERRM);
1373 raise_application_error(-20530,'Can not start workflow: ' || SQLERRM);
1374 END;
1375 else
1376 rollback to fa_q_tag;
1377 end if;
1378
1379 ELSIF lv_queue_state = 'SUSPENDED' THEN -- notify dequeuer to sleep
1380 dbms_lock.sleep(3);
1381 ELSIF lv_queue_state = 'SHUTDOWN' THEN -- notify dequeuer to exit
1382 return;
1383 ELSIF lv_queue_state = 'DISABLED' THEN -- notify dequeuer to exit
1384 dbms_lock.sleep(3);
1385 ELSIF lv_queue_state = 'DATA_NOT_FOUND' THEN -- notify dequeuer to exit
1386 return;
1387 ELSE -- notify dequeuer to exit
1388 return;
1389 END IF;
1390
1391 <<l_continue_loop>>
1392 null;
1393 END LOOP;
1394
1395 END Start_FA_Workflow;
1396
1397 ******/
1398
1399
1400 PROCEDURE Start_FA_Workflow ( p_message_wait_timeout IN NUMBER DEFAULT 1,
1401 p_correlation_id IN VARCHAR2,
1402 x_message_key OUT NOCOPY VARCHAR2,
1403 x_queue_timed_out OUT NOCOPY VARCHAR2 )
1404 IS
1405 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1406 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
1407 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
1408 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1409 lv_MsgID RAW(16);
1410
1411 BEGIN
1412
1413 savepoint fa_q_tag;
1414 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1415 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1416 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE;
1417 lv_DequeueOptions.MSGID := NULL;
1418 lv_DequeueOptions.correlation := p_correlation_id;
1419
1420 BEGIN
1421 -- Set Dequeue time out to be 1 second
1422 lv_DequeueOptions.wait := p_message_wait_timeout;
1423
1424 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_FA_QUEUE',
1425 dequeue_options => lv_DequeueOptions,
1426 message_properties => lv_MessageProperties,
1427 payload => lv_wf_object,
1428 msgid => lv_MsgID);
1429 EXCEPTION
1430 WHEN e_QTimeOut THEN
1431 x_queue_timed_out := 'Y';
1432 return;
1433 WHEN OTHERS THEN
1434 rollback to pending_q_tag;
1435 handle_dq_exception(
1436 p_MESSAGE_ID => lv_MSGID ,
1437 p_WF_ITEM_TYPE => null,
1438 p_WF_ITEM_KEY => null,
1439 p_CALLER_NAME => 'Start_FA_Workflow',
1440 p_CALLBACK_TEXT => NULL ,
1441 p_Q_NAME => 'XDP_FA_QUEUE',
1442 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1443
1444 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1445 END;
1446
1447 BEGIN
1448 SET_CONTEXT( lv_wf_object.order_id, 'ORDER_OBJECT');
1449
1450 EXCEPTION
1451 WHEN stop_processing THEN
1452 x_queue_timed_out := 'Y';
1453 return;
1454 END;
1455
1456
1457 BEGIN
1458
1459 WF_ENGINE.StartProcess( lv_wf_object.wf_item_type,
1460 lv_wf_object.wf_item_key);
1461
1462 COMMIT;
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 rollback to fa_q_tag;
1466 handle_dq_exception(
1467 p_MESSAGE_ID => lv_MSGID ,
1468 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1469 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1470 p_CALLER_NAME => 'Start_FA_Workflow',
1471 p_CALLBACK_TEXT => NULL ,
1472 p_Q_NAME => 'XDP_FA_QUEUE',
1473 p_ERROR_DESCRIPTION => 'Can not start workflow: ' || SQLERRM);
1474
1475 raise_application_error(-20530, 'Can not start workflow: ' || SQLERRM);
1476 END;
1477
1478 EXCEPTION
1479 WHEN e_NothingToDequeueException then
1480 x_queue_timed_out := 'Y';
1481 WHEN OTHERS THEN
1482 RAISE;
1483 END Start_FA_Workflow;
1484 --
1485 -- Allow WF to pass the pipe to next WF through enqueue
1486 --
1487 PROCEDURE HANDOVER_CHANNEL(
1488 p_channel_name IN VARCHAR2,
1489 p_fe_name IN VARCHAR2,
1490 p_wf_item_type IN VARCHAR2,
1491 p_wf_item_key IN VARCHAR2,
1492 p_wf_activity IN Varchar2 Default NULL,
1493 p_order_id IN number,
1494 p_wi_instance_id IN number,
1495 p_fa_instance_id IN number,
1496 p_return_code OUT NOCOPY NUMBER,
1497 p_error_description OUT NOCOPY VARCHAR2) IS
1498
1499 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1500 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
1501 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1502 lv_MsgID RAW(16);
1503 lv_jobNum NUMBER;
1504
1505 BEGIN
1506
1507 p_return_code := 0;
1508 lv_wf_object := SYSTEM.XDP_WF_CHANNELQ_TYPE(
1509 p_channel_name,
1510 p_fe_name,
1511 p_wf_item_type,
1512 p_wf_item_key,
1513 p_wf_activity,
1514 p_order_id,
1515 p_wi_instance_id,
1516 p_fa_instance_id);
1517
1518 -- Enqueue it with the commit on a seperate transaction.
1519 lv_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
1520 lv_MessageProperties.exception_queue := G_XDP_SCHEMA||'.'||'XDP_Channel_Exception_Q';
1521 if p_fe_name is not null then
1522 lv_MessageProperties.correlation := XDP_ADAPTER_CORE_DB.pv_InstanceName || ':' || p_fe_name;
1523 else
1524 lv_MessageProperties.correlation := XDP_ADAPTER_CORE_DB.pv_InstanceName;
1525 end if;
1526 DBMS_AQ.ENQUEUE(
1527 queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_CHANNEL_Q',
1528 enqueue_options => lv_EnqueueOptions,
1529 message_properties => lv_MessageProperties,
1530 payload =>lv_wf_object,
1531 msgid => lv_MsgID);
1532
1533 -- Change status of the FA Instance to 'READY_FOR_RESOURCE'
1534
1535 UPDATE xdp_fa_runtime_list
1536 SET status_code = 'READY_FOR_RESOURCE' ,
1537 msgid = lv_MsgID
1538 WHERE fa_instance_id = p_fa_instance_id ;
1539
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 p_return_code := SQLCODE;
1544 p_error_description := SQLERRM;
1545 END HANDOVER_Channel;
1546
1547
1548 /***** Commented out as this code is executed by C dequeuer
1549 --
1550 -- Used by DB job to resume a WF with the new pipe
1551 -- through dequeue
1552 --
1553 PROCEDURE Resume_Next_WF IS
1554
1555 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1556 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
1557 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1558 lv_MsgID RAW(16);
1559 lv_return_code NUMBER;
1560 lv_error_description VARCHAR2(2000);
1561 lv_queue_state varchar2(200);
1562 lv_fa_state varchar2(80);
1563 lv_wi_state varchar2(80);
1564
1565 BEGIN
1566
1567 WHILE 1 = 1 LOOP
1568 BEGIN
1569 lv_queue_state := Get_Queue_State('XDP_WF_CHANNEL_Q');
1570 IF lv_queue_state = 'ENABLED' THEN ---- proceed
1571 BEGIN
1572 SAVEPOINT resume_wf1;
1573 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1574 lv_DequeueOptions.navigation := DBMS_AQ.NEXT_MESSAGE;
1575
1576 -- Set Dequeue time out to be 1 second
1577 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
1578 BEGIN
1579
1580 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_Channel_Q',
1581 dequeue_options => lv_DequeueOptions,
1582 message_properties => lv_MessageProperties,
1583 payload => lv_wf_object,
1584 msgid => lv_MsgID);
1585
1586 EXCEPTION
1587 When e_QNavOut Then
1588 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1589 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1590
1591 -- Set Dequeue time out to be 1 second
1592 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
1593 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_Channel_Q',
1594 dequeue_options => lv_DequeueOptions,
1595 message_properties => lv_MessageProperties,
1596 payload => lv_wf_object,
1597 msgid => lv_MsgID);
1598 END;
1599 EXCEPTION
1600 WHEN e_QTimeOut Then
1601 GOTO l_continue_loop;
1602 WHEN OTHERS THEN
1603 rollback to resume_wf1;
1604 handle_dq_exception(
1605 p_MESSAGE_ID => NULL ,
1606 p_WF_ITEM_TYPE => NULL,
1607 p_WF_ITEM_KEY => NULL,
1608 p_CALLER_NAME => 'Resume_Next_WF',
1609 p_CALLBACK_TEXT => NULL ,
1610 p_Q_NAME => 'XDP_WF_Channel_Q',
1611 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1612 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1613 END;
1614
1615 SELECT frt.status_code,
1616 fwt.status_code
1617 INTO lv_fa_state,
1618 lv_wi_state
1619 FROM xdp_fa_runtime_list frt,
1620 xdp_fulfill_worklist fwt
1621 WHERE fa_instance_id = lv_wf_object.fa_instance_id
1622 AND frt.workitem_instance_id = fwt.workitem_instance_id;
1623
1624 IF (lv_fa_state <> 'CANCELED' OR
1625 lv_fa_state <> 'ABORTED' ) THEN
1626 -- And resume work flow.
1627 SDP_Resume_WF(lv_wf_object.channel_name,
1628 lv_wf_object.wf_item_type,
1629 lv_wf_object.wf_item_key,
1630 lv_wf_object.wf_activity_name,
1631 lv_MessageProperties.enqueue_time,
1632 lv_return_code,
1633 lv_error_description);
1634
1635 IF lv_return_code <> 0 THEN
1636 ROLLBACK to resume_wf1;
1637 handle_dq_exception( p_MESSAGE_ID => lv_MsgID ,
1638 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1639 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1640 p_CALLER_NAME => 'Resume_Next_WF',
1641 p_CALLBACK_TEXT => NULL ,
1642 p_Q_NAME => 'XDP_WF_Channel_Q',
1643 p_ERROR_DESCRIPTION => lv_error_description);
1644 raise_application_error(-20530,lv_error_description);
1645 END IF;
1646 ELSE
1647 -- Handover the channel to the next one
1648 null;
1649 XDPCORE_FA.HandOverChannel (
1650 lv_wf_object.channel_name,
1651 0,
1652 NULL,
1653 'ADMIN',
1654 lv_return_code,
1655 lv_error_description);
1656 END IF;
1657 commit;
1658
1659 ELSIF lv_queue_state = 'SUSPENDED' THEN -- notify dequeuer to sleep
1660 dbms_lock.sleep(3);
1661 ELSIF lv_queue_state = 'SHUTDOWN' THEN -- notify dequeuer to exit
1662 return;
1663 ELSIF lv_queue_state = 'DISABLED' THEN -- notify dequeuer to exit
1664 dbms_lock.sleep(3);
1665 ELSIF lv_queue_state = 'DATA_NOT_FOUND' THEN -- notify dequeuer to exit
1666 return;
1667 ELSE -- notify dequeuer to exit
1668 return;
1669 END IF;
1670
1671 <<l_continue_loop>>
1672 null;
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 raise;
1676 END;
1677 END LOOP; --- END of infinite loop
1678
1679 END Resume_Next_WF;
1680
1681 ********/
1682
1683 PROCEDURE Resume_Next_WF (p_message_wait_timeout IN NUMBER DEFAULT 1,
1684 p_correlation_id IN VARCHAR2,
1685 x_message_key OUT NOCOPY VARCHAR2,
1686 x_queue_timed_out OUT NOCOPY VARCHAR2 )
1687 IS
1688 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
1689 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
1690 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1691 lv_MsgID RAW(16);
1692
1693 lv_fa_state varchar2(40);
1694 lv_return_code NUMBER;
1695 lv_error_description VARCHAR2(2000);
1696
1697 lv_high_avail BOOLEAN;
1698
1699 BEGIN
1700
1701 savepoint resume_wf;
1702 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1703 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1704 if p_correlation_id is not null then
1705 lv_DequeueOptions.correlation := XDP_ADAPTER_CORE_DB.pv_InstanceName || ':' || p_correlation_id;
1706 else
1707 lv_DequeueOptions.correlation := XDP_ADAPTER_CORE_DB.pv_InstanceName || '%';
1708 end if;
1709 lv_DequeueOptions.wait := p_message_wait_timeout;
1710 lv_high_avail := false;
1711
1712 BEGIN
1713 DBMS_AQ.DEQUEUE(
1714 queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_Channel_Q',
1715 dequeue_options => lv_DequeueOptions,
1716 message_properties => lv_MessageProperties,
1717 payload => lv_wf_object,
1718 msgid => lv_MsgID);
1719 EXCEPTION
1720 WHEN e_QTimeOut Then
1721 x_queue_timed_out := 'Y';
1722 return;
1723 WHEN OTHERS THEN
1724 rollback to resume_wf;
1725 handle_dq_exception(
1726 p_MESSAGE_ID => NULL ,
1727 p_WF_ITEM_TYPE => NULL,
1728 p_WF_ITEM_KEY => NULL,
1729 p_CALLER_NAME => 'Resume_Next_WF',
1730 p_CALLBACK_TEXT => NULL ,
1731 p_Q_NAME => 'XDP_WF_Channel_Q',
1732 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1733
1734 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1735 END;
1736
1737 BEGIN
1738 SET_CONTEXT( lv_wf_object.order_id, 'ORDER_OBJECT');
1739
1740 EXCEPTION
1741 WHEN stop_processing THEN
1742 x_queue_timed_out := 'Y';
1743 lv_high_avail := true;
1744 END;
1745
1746 SELECT frt.status_code
1747 INTO lv_fa_state
1748 FROM xdp_fa_runtime_list frt
1749 WHERE fa_instance_id = lv_wf_object.fa_instance_id;
1750
1751 IF (lv_fa_state <> 'CANCELED' AND lv_fa_state <> 'ABORTED' AND lv_high_avail <> TRUE) THEN
1752 -- And resume work flow.
1753 SDP_Resume_WF( lv_wf_object.channel_name,
1754 lv_wf_object.wf_item_type,
1755 lv_wf_object.wf_item_key,
1756 lv_wf_object.wf_activity_name,
1757 lv_MessageProperties.enqueue_time,
1758 lv_return_code,
1759 lv_error_description);
1760
1761 IF lv_return_code <> 0 THEN
1762 ROLLBACK to resume_wf;
1763 handle_dq_exception(
1764 p_MESSAGE_ID => lv_MsgID ,
1765 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1766 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1767 p_CALLER_NAME => 'Resume_Next_WF',
1768 p_CALLBACK_TEXT => NULL ,
1769 p_Q_NAME => 'XDP_WF_Channel_Q',
1770 p_ERROR_DESCRIPTION => lv_error_description);
1771
1772 raise_application_error(-20530,lv_error_description);
1773 END IF;
1774 ELSE
1775 /* Handover the channel to the next one */
1776 XDPCORE_FA.HandOverChannel (
1777 lv_wf_object.channel_name,
1778 0,
1779 NULL,
1780 'ADMIN',
1781 lv_return_code,
1782 lv_error_description);
1783 END IF;
1784
1785
1786 COMMIT;
1787
1788
1789 EXCEPTION
1790 WHEN e_NothingToDequeueException then
1791 x_queue_timed_out := 'Y';
1792 WHEN OTHERS THEN
1793 RAISE;
1794 END Resume_Next_WF;
1795
1796
1797 -- PL/SQL Block
1798
1799 PROCEDURE SDP_RESUME_WF
1800 (p_pipe_name IN VARCHAR2
1801 ,p_wf_item_type IN VARCHAR2
1802 ,p_wf_item_key IN VARCHAR2
1803 ,p_wf_activity IN VARCHAR2
1804 ,p_enq_time IN DATE
1805 ,P_RETURN_CODE OUT NOCOPY NUMBER
1806 ,P_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
1807 )
1808 IS
1809
1810 -- PL/SQL Block
1811
1812 BEGIN
1813 p_return_code := 0;
1814
1815 wf_engine.SetItemAttrText(itemtype => p_wf_item_type,
1816 itemkey => p_wf_item_key,
1817 aname => 'CHANNEL_NAME',
1818 avalue => p_pipe_name);
1819
1820 /*********
1821
1822 wf_engine.SetItemAttrDate(itemtype => p_wf_item_type,
1823 itemkey => p_wf_item_key,
1824 aname => 'RE_PROCESS_ENQ_TIME',
1825 avalue => p_enq_time);
1826 ********/
1827
1828 wf_engine.CompleteActivity(itemtype => p_wf_item_type,
1829 itemkey => p_wf_item_key,
1830 activity => p_wf_activity,
1831 result => 'RESUME_PROVISIONING');
1832 COMMIT;
1833
1834 EXCEPTION
1835 WHEN OTHERS THEN
1836 p_return_code := SQLCODE;
1837 p_error_description := SQLERRM;
1838 END SDP_RESUME_WF;
1839
1840 --
1841 -- Allow workflow to register a notification event through eq
1842 --
1843 PROCEDURE Resume_WF_EQ(
1844 p_event_id number,
1845 p_wf_item_type VARCHAR2 ,
1846 p_wf_item_key VARCHAR2,
1847 p_wf_activity VARCHAR2,
1848 p_callback VARCHAR2,
1849 p_priority number default 100,
1850 p_return_code OUT NOCOPY NUMBER,
1851 p_error_description OUT NOCOPY VARCHAR2)
1852 IS
1853 lv_wf_object SYSTEM.XDP_WF_RESUMEQ_TYPE;
1854 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
1855 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1856 lv_MsgID RAW(16);
1857 lv_jobNum NUMBER;
1858
1859 BEGIN
1860
1861 p_return_code := 0;
1862 lv_wf_object := SYSTEM.XDP_WF_RESUMEQ_TYPE(
1863 event_id => p_event_id,
1864 callback => p_callback,
1865 wf_item_type => p_wf_item_type,
1866 wf_item_key => p_wf_item_key,
1867 wf_activity_name => p_wf_activity,
1868 error_description => NULL);
1869
1870 -- Enqueue it with the commit on a seperate transaction.
1871 lv_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
1872 lv_MessageProperties.exception_queue := G_XDP_SCHEMA||'.'||'XDP_WF_SEQUENCING_ExpQ';
1873 lv_MessageProperties.priority := p_priority;
1874
1875 DBMS_AQ.ENQUEUE(
1876 queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_SEQUENCING_Q',
1877 enqueue_options => lv_EnqueueOptions,
1878 message_properties => lv_MessageProperties,
1879 payload =>lv_wf_object,
1880 msgid => lv_MsgID);
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 p_return_code := SQLCODE;
1885 p_error_description := SQLERRM;
1886 END Resume_WF_EQ;
1887
1888 --
1889 -- Used by API to notify the parent workflow to resume
1890 -- through dequeue
1891 --
1892 PROCEDURE Resume_Parent_Workflow
1893 IS
1894 lv_wf_object SYSTEM.XDP_WF_RESUMEQ_TYPE;
1895 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
1896 lv_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
1897 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
1898 lv_MsgID RAW(16);
1899 lv_return_code NUMBER;
1900 lv_error_description VARCHAR2(2000);
1901 lv_queue_state varchar2(200);
1902 BEGIN
1903
1904 while 1=1 loop
1905 -- Check queue state
1906 lv_queue_state := Get_Queue_State('XDP_WF_SEQUENCING_Q');
1907
1908 IF lv_queue_state = 'ENABLED' THEN /*proceed*/
1909 BEGIN
1910 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1911 lv_DequeueOptions.navigation := DBMS_AQ.NEXT_MESSAGE;
1912
1913 -- Set Dequeue time out to be 2 second
1914 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
1915 BEGIN
1916 DBMS_AQ.DEQUEUE(
1917 queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_SEQUENCING_Q',
1918 dequeue_options => lv_DequeueOptions,
1919 message_properties => lv_MessageProperties,
1920 payload => lv_wf_object,
1921 msgid => lv_MsgID);
1922 EXCEPTION
1923 WHEN e_QNavOut THEN
1924 -- Dequeue it with the commit on a seperate transaction.
1925 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
1926 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
1927
1928 -- Set Dequeue time out to be 2 second
1929 lv_DequeueOptions.wait := xnp_message.POP_TIMEOUT;
1930 DBMS_AQ.DEQUEUE(
1931 queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_SEQUENCING_Q',
1932 dequeue_options => lv_DequeueOptions,
1933 message_properties => lv_MessageProperties,
1934 payload => lv_wf_object,
1935 msgid => lv_MsgID);
1936 END;
1937 EXCEPTION
1938 WHEN e_QTimeOut Then
1939 null;
1940 GOTO l_continue_loop;
1941 WHEN OTHERS THEN
1942 rollback;
1943 handle_dq_exception(
1944 p_MESSAGE_ID => NULL ,
1945 p_WF_ITEM_TYPE => NULL,
1946 p_WF_ITEM_KEY => NULL,
1947 p_CALLER_NAME => 'Resume_Parent_Workflow',
1948 p_CALLBACK_TEXT => NULL ,
1949 p_Q_NAME => 'XDP_WF_SEQUENCING_Q',
1950 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
1951 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
1952 END;
1953
1954 /*XDP_Utilities.Execute_Any_DDL(
1955 p_ddl_block => lv_wf_object.callback,
1956 return_code => lv_return_code,
1957 error_description => lv_error_description);
1958
1959 IF lv_return_code <> 0 THEN
1960 rollback;
1961 handle_dq_exception(
1962 p_MESSAGE_ID => lv_MsgID ,
1963 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1964 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1965 p_CALLER_NAME => 'Resume_Parent_Workflow',
1966 p_CALLBACK_TEXT => lv_wf_object.callback ,
1967 p_Q_NAME => 'XDP_WF_SEQUENCING_Q',
1968 p_ERROR_DESCRIPTION => lv_error_description);
1969 raise_application_error(-20530,lv_error_description);
1970 END IF;*/
1971
1972 BEGIN
1973 wf_engine.CompleteActivity( itemtype => lv_wf_object.wf_item_type,
1974 itemkey => lv_wf_object.wf_item_key,
1975 activity => lv_wf_object.wf_activity_name,
1976 result => 'RESUME_PROVISIONING');
1977 commit;
1978
1979 exception
1980 when others then
1981 rollback;
1982 handle_dq_exception(
1983 p_MESSAGE_ID => lv_MsgID ,
1984 p_WF_ITEM_TYPE => lv_wf_object.wf_item_type,
1985 p_WF_ITEM_KEY => lv_wf_object.wf_item_key,
1986 p_CALLER_NAME => 'Resume_Parent_Workflow',
1987 p_CALLBACK_TEXT => lv_wf_object.callback ,
1988 p_Q_NAME => 'XDP_WF_SEQUENCING_Q',
1989 p_ERROR_DESCRIPTION => SQLERRM);
1990 raise_application_error(-20530,SQLERRM);
1991 END;
1992
1993 ELSIF lv_queue_state = 'SUSPENDED' THEN /* notify dequeuer to sleep */
1994 dbms_lock.sleep(3);
1995 ELSIF lv_queue_state = 'SHUTDOWN' THEN /* notify dequeuer to exit */
1996 return;
1997 ELSIF lv_queue_state = 'DISABLED' THEN /* notify dequeuer to exit */
1998 dbms_lock.sleep(3);
1999 ELSIF lv_queue_state = 'DATA_NOT_FOUND' THEN /* notify dequeuer to exit */
2000 return;
2001 ELSE /* notify dequeuer to exit */
2002 return;
2003 END IF;
2004
2005 <<l_continue_loop>>
2006 null;
2007 END LOOP;
2008
2009
2010 END Resume_Parent_Workflow;
2011
2012
2013 --
2014 -- Interface with the OSS System with the order information and its type
2015 --
2016 PROCEDURE InterfaceWithOSS (
2017 p_OrderID NUMBER,
2018 p_ObjectType VARCHAR2,
2019 p_ReturnCode OUT NOCOPY NUMBER,
2020 p_ErrorDescription OUT NOCOPY VARCHAR2) IS
2021
2022 l_OrderObj SYSTEM.XDP_ORDER_OBJ;
2023 l_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
2024 l_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
2025 l_MsgID RAW(16);
2026
2027 BEGIN
2028
2029 l_OrderObj := SYSTEM.XDP_ORDER_OBJ(
2030 p_OrderID,
2031 p_ObjectType);
2032
2033 l_EnqueueOptions.visibility := DBMS_AQ.ON_COMMIT;
2034 l_MessageProperties.exception_queue := G_XDP_SCHEMA||'.'||'XDP_ORDER_OBJ_Excep_Q';
2035
2036
2037 DBMS_AQ.ENQUEUE (
2038 queue_name => G_XDP_SCHEMA||'.'||'XDP_ORDER_OBJ_Q',
2039 enqueue_options => l_EnqueueOptions,
2040 message_properties => l_MessageProperties,
2041 payload => l_OrderObj,
2042 msgid => l_MsgID);
2043
2044 EXCEPTION
2045 WHEN OTHERS THEN
2046 p_ReturnCode := SQLCODE;
2047 p_ErrorDescription := SUBSTR(SQLERRM,1,200);
2048 END InterfaceWithOSS;
2049
2050 --
2051 -- Get the current state of the given queue
2052 --
2053 FUNCTION Get_Queue_State(
2054 p_queue_name IN VARCHAR2)
2055 RETURN VARCHAR2
2056 IS
2057 lv_q_state varchar2(200);
2058 BEGIN
2059
2060 SELECT state
2061 INTO lv_q_state
2062 FROM XDP_dq_configuration
2063 WHERE internal_q_name = p_queue_name;
2064
2065 RETURN lv_q_state;
2066
2067 exception
2068 when NO_DATA_FOUND THEN
2069 lv_q_state := 'NO_DATA_FOUND';
2070 rollback to my_get_state;
2071 return lv_q_state;
2072 when others then
2073 lv_q_state := SQLERRM;
2074 rollback to my_get_state;
2075 return lv_q_state;
2076
2077 END Get_Queue_State;
2078
2079 --
2080 -- Disable a given queue, if queue_name is not supplied,
2081 -- all queues will be disabled
2082 --
2083 PROCEDURE DISABLE_SDP_AQ(
2084 p_queue_name IN VARCHAR2,
2085 p_return_code OUT NOCOPY NUMBER,
2086 p_error_description OUT NOCOPY VARCHAR2)
2087 IS
2088 lv_exists varchar2(1);
2089 CURSOR lc_q IS
2090 select rowid
2091 from xdp_dq_configuration;
2092
2093 BEGIN
2094
2095 p_return_code := 0;
2096 if p_queue_name is not null then
2097 update XDP_dq_configuration
2098 set last_updated_by = FND_GLOBAL.USER_ID,
2099 last_update_date = sysdate,
2100 last_update_login = FND_GLOBAL.LOGIN_ID,
2101 state = 'DISABLED'
2102 where internal_q_name = UPPER(p_queue_name);
2103 else
2104 FOR lv_rec in lc_q loop
2105 update XDP_dq_configuration
2106 set state = 'DISABLED',
2107 last_updated_by = FND_GLOBAL.USER_ID,
2108 last_update_date = sysdate,
2109 last_update_login = FND_GLOBAL.LOGIN_ID
2110 where rowid = lv_rec.rowid;
2111 COMMIT;
2112 END LOOP;
2113 end if;
2114
2115 EXCEPTION
2116 WHEN OTHERS THEN
2117 p_Return_Code := SQLCODE;
2118 p_Error_Description := SUBSTR(SQLERRM,1,200);
2119 END DISABLE_SDP_AQ;
2120
2121
2122 --
2123 -- Enable a given queue, if queue_name is not supplied,
2124 -- all queues will be enabled
2125 --
2126 PROCEDURE ENABLE_SDP_AQ(
2127 p_queue_name IN VARCHAR2,
2128 p_return_code OUT NOCOPY NUMBER,
2129 p_error_description OUT NOCOPY VARCHAR2)
2130 IS
2131 lv_exists varchar2(1);
2132 CURSOR lc_q IS
2133 select rowid
2134 from xdp_dq_configuration;
2135
2136 BEGIN
2137
2138 p_return_code := 0;
2139 if p_queue_name is not null then
2140 update XDP_dq_configuration
2141 set state = 'ENABLED',
2142 last_updated_by = FND_GLOBAL.USER_ID,
2143 last_update_date = sysdate,
2144 last_update_login = FND_GLOBAL.LOGIN_ID
2145 where internal_q_name = UPPER(p_queue_name);
2146 else
2147 FOR lv_rec IN lc_q LOOP
2148 update XDP_dq_configuration
2149 set state = 'ENABLED',
2150 last_updated_by = FND_GLOBAL.USER_ID,
2151 last_update_date = sysdate,
2152 last_update_login = FND_GLOBAL.LOGIN_ID
2153 where rowid = lv_rec.rowid;
2154 COMMIT;
2155 END LOOP;
2156 end if;
2157
2158 EXCEPTION
2159 WHEN OTHERS THEN
2160 p_Return_Code := SQLCODE;
2161 p_Error_Description := SUBSTR(SQLERRM,1,200);
2162 END ENABLE_SDP_AQ;
2163
2164
2165 --
2166 -- Shut down a given SFM queue, if queue_name is not supplied,
2167 -- all queues will be shutdown
2168 --
2169 PROCEDURE SHUTDOWN_SDP_AQ(
2170 p_queue_name IN VARCHAR2,
2171 p_return_code OUT NOCOPY NUMBER,
2172 p_error_description OUT NOCOPY VARCHAR2)
2173 IS
2174 CURSOR lc_q IS
2175 select rowid
2176 from xdp_dq_configuration;
2177
2178 BEGIN
2179 p_return_code := 0;
2180 if p_queue_name is not null then
2181 update XDP_dq_configuration
2182 set state = 'SHUTDOWN',
2183 last_updated_by = FND_GLOBAL.USER_ID,
2184 last_update_date = sysdate,
2185 last_update_login = FND_GLOBAL.LOGIN_ID
2186 where internal_q_name = UPPER(p_queue_name);
2187 else
2188 FOR lv_rec IN lc_q LOOP
2189 update XDP_dq_configuration
2190 set state = 'SHUTDOWN',
2191 last_updated_by = FND_GLOBAL.USER_ID,
2192 last_update_date = sysdate,
2193 last_update_login = FND_GLOBAL.LOGIN_ID
2194 where rowid = lv_rec.rowid;
2195 COMMIT;
2196 END LOOP;
2197 end if;
2198
2199
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 p_Return_Code := SQLCODE;
2203 p_Error_Description := SUBSTR(SQLERRM,1,200);
2204 END SHUTDOWN_SDP_AQ;
2205
2206 --
2207 -- Log the dequeue exceptions for the dequeuer
2208 -- and disable the queue
2209 --
2210 PROCEDURE HANDLE_DQ_Exception(
2211 p_MESSAGE_ID IN RAW,
2212 p_WF_ITEM_TYPE IN VARCHAR2 DEFAULT NULL,
2213 p_WF_ITEM_KEY IN VARCHAR2 DEFAULT NULL,
2214 p_CALLER_NAME IN VARCHAR2,
2215 p_CALLBACK_TEXT IN VARCHAR2 DEFAULT NULL,
2216 p_Q_NAME IN VARCHAR2,
2217 p_ERROR_DESCRIPTION IN VARCHAR2,
2218 p_ERROR_TIME IN DATE DEFAULT sysdate )
2219
2220 IS
2221
2222 lv_ret number;
2223 lv_err varchar2(300);
2224 -- lv_MessageList XDP_TYPES.MESSAGE_TOKEN_LIST;
2225 x_parameters varchar2(4000);
2226 lv_ref_id number := 0;
2227 BEGIN
2228
2229 null;
2230 -- Commented out - sacsharm - 11.5.6 changes
2231 -- lv_MessageList(1).MESSAGE_TOKEN_NAME := 'DQ_ERROR';
2232 -- lv_MessageList(1).MESSAGE_TOKEN_VALUE := p_error_description;
2233 -- XDP_ERRORS_PKG.Set_Message(p_message_name => 'XDP_DQ_ERROR',
2234 -- p_message_ref_id => lv_ref_id,
2235 -- p_message_param_list => lv_MessageList,
2236 -- p_appl_name => 'XDP',
2237 -- p_sql_code => lv_ret,
2238 -- p_sql_desc => lv_err);
2239 -- Commented out - adabholk - 11.5.6 changes
2240 -- We may remove this procedure completely . Needs to be evaluated.
2241 --
2242 -- x_parameters := 'DQ_ERROR='||p_error_description||'#XDP#';
2243 -- XDP_ERRORS_PKG.Set_Message(p_object_type => 'QUEUE',
2244 -- p_object_key => p_q_name,
2245 -- p_message_name => 'XDP_DQ_ERROR',
2246 -- p_message_parameters => x_parameters);
2247 --
2248 -- insert into XDP_dq_exceptions(
2249 -- created_by,
2250 -- creation_date,
2251 -- last_updated_by,
2252 -- last_update_date,
2253 -- last_update_login,
2254 -- MESSAGE_ID,
2255 -- WF_ITEM_TYPE,
2256 -- WF_ITEM_KEY,
2257 -- CALLER_NAME ,
2258 -- CALLBACK_TEXT ,
2259 -- Q_NAME ,
2260 -- error_ref_id)
2261 -- values(
2262 -- FND_GLOBAL.USER_ID,
2263 -- sysdate,
2264 -- FND_GLOBAL.USER_ID,
2265 -- sysdate,
2266 -- FND_GLOBAL.LOGIN_ID,
2267 -- p_MESSAGE_ID ,
2268 -- p_WF_ITEM_TYPE,
2269 -- p_WF_ITEM_KEY,
2270 -- p_CALLER_NAME ,
2271 -- p_CALLBACK_TEXT ,
2272 -- p_Q_NAME ,
2273 -- lv_ref_id);
2274 --
2275 -- DISABLE_SDP_AQ( p_q_name ,
2276 -- lv_ret,
2277 -- lv_err);
2278 --
2279 -- COMMIT;
2280 --
2281 -- if lv_ret <> 0 THEN
2282 -- raise_application_error(-20530,lv_err,TRUE);
2283 -- end if;
2284 --
2285 -- exception
2286 -- when others then
2287 -- raise;
2288 END Handle_DQ_Exception;
2289
2290 Procedure LogCommandAuditTrail (FAInstanceID in number,
2291 FeName in varchar2,
2292 FeType in varchar2,
2293 SW_Generic in varchar2,
2294 CommandSent in varchar2,
2295 SentDate in DATE,
2296 Response in varchar2,
2297 ResponseLong in CLOB,
2298 RespDate in DATE,
2299 ProcName in varchar2)
2300 is
2301
2302 PRAGMA AUTONOMOUS_TRANSACTION;
2303
2304 l_response_id number;
2305 l_FeName varchar2(80);
2306 l_FeType varchar2(80);
2307 l_SW_Generic varchar2(80);
2308 l_CommandSent varchar2(32767);
2309 l_Response varchar2(32767);
2310 begin
2311
2312 IF FeName IS NULL then
2313 l_FeName := 'GOT NULL FE NAME';
2314 ELSE
2315 l_FeName := FeName;
2316 end IF;
2317
2318
2319 IF FeType IS NULL then
2320 l_FeType := 'GOT NULL FE TYPE';
2321 ELSE
2322 l_FeType := FeType;
2323 end IF;
2324
2325 IF SW_Generic IS NULL then
2326 l_SW_Generic := 'GOT NULL SW_GENERIC';
2327 ELSE
2328 l_SW_Generic := SW_Generic;
2329 end IF;
2330
2331 IF CommandSent IS NULL then
2332 l_CommandSent := 'GOT NULL Command to be sent';
2333 ELSE
2334 l_CommandSent := CommandSent;
2335 end IF;
2336
2337 IF Response IS NULL then
2338 l_Response := 'GOT NULL Response from the NE';
2339 ELSE
2340 l_Response := substr(Response,1,3999);
2341 end IF;
2342
2343 /*
2344 * Insert into the audit trail table.
2345 */
2346 INSERT INTO XDP_FE_CMD_AUD_TRAILS (
2347 created_by,
2348 creation_date,
2349 last_updated_by,
2350 last_update_date,
2351 last_update_login,
2352 fa_instance_id,
2353 fe_command_seq,
2354 fulfillment_element_name,
2355 fulfillment_element_type,
2356 sw_generic,
2357 command_sent,
2358 command_sent_date,
2359 response,
2360 response_long,
2361 response_date,
2362 provisioning_procedure)
2363 VALUES (
2364 FND_GLOBAL.USER_ID,
2365 sysdate,
2366 FND_GLOBAL.USER_ID,
2367 sysdate,
2368 FND_GLOBAL.LOGIN_ID,
2369 FAInstanceID,
2370 XDP_FE_CMD_AUD_TRAILS_S.NEXTVAL,
2371 l_FeName,
2372 l_FeType,
2373 l_Sw_Generic,
2374 l_CommandSent,
2375 SentDate,
2376 l_Response,
2377 ResponseLong,
2378 RespDate,
2379 ProcName);
2380
2381 commit;
2382
2383 exception
2384 when others then
2385 xdp_utilities.generic_error('XDP_AQ_UTILITIES.LogCommandAuditTrail',
2386 'FA Instance: ' || FAInstanceID,
2387 sqlcode,
2388 sqlerrm);
2389 END LogCommandAuditTrail;
2390
2391
2392 Procedure LogCommandAuditTrail (FAInstanceID in number,
2393 FeName in varchar2,
2394 FeType in varchar2,
2395 SW_Generic in varchar2,
2396 CommandSent in varchar2,
2397 SentDate in DATE,
2398 Response in varchar2,
2399 RespDate in DATE,
2400 ProcName in varchar2)
2401 is
2402
2403 PRAGMA AUTONOMOUS_TRANSACTION;
2404
2405 l_response_id number;
2406 l_FeName varchar2(80);
2407 l_FeType varchar2(80);
2408 l_SW_Generic varchar2(80);
2409 l_CommandSent varchar2(32767);
2410 l_Response varchar2(32767);
2411 l_ResponseLong varchar2(32767);
2412 begin
2413
2414 IF FeName IS NULL then
2415 l_FeName := 'GOT NULL FE NAME';
2416 ELSE
2417 l_FeName := FeName;
2418 end IF;
2419
2420
2421 IF FeType IS NULL then
2422 l_FeType := 'GOT NULL FE TYPE';
2423 ELSE
2424 l_FeType := FeType;
2425 end IF;
2426
2427 IF SW_Generic IS NULL then
2428 l_SW_Generic := 'GOT NULL SW_GENERIC';
2429 ELSE
2430 l_SW_Generic := SW_Generic;
2431 end IF;
2432
2433 IF CommandSent IS NULL then
2434 l_CommandSent := 'GOT NULL Command to be sent';
2435 ELSE
2436 l_CommandSent := CommandSent;
2437 end IF;
2438
2439 IF Response IS NULL then
2440 l_Response := 'GOT NULL Response from the NE';
2441 l_ResponseLong := 'GOT NULL Response from the NE';
2442 ELSE
2443 l_Response := substr(Response,1,3999);
2444 l_ResponseLong := substr(Response,1,32766);
2445 end IF;
2446
2447 /*
2448 * Insert into the audit trail table.
2449 */
2450 INSERT INTO XDP_FE_CMD_AUD_TRAILS (
2451 created_by,
2452 creation_date,
2453 last_updated_by,
2454 last_update_date,
2455 last_update_login,
2456 fa_instance_id,
2457 fe_command_seq,
2458 fulfillment_element_name,
2459 fulfillment_element_type,
2460 sw_generic,
2461 command_sent,
2462 command_sent_date,
2463 response,
2464 response_long,
2465 response_date,
2466 provisioning_procedure)
2467 VALUES (
2468 FND_GLOBAL.USER_ID,
2469 sysdate,
2470 FND_GLOBAL.USER_ID,
2471 sysdate,
2472 FND_GLOBAL.LOGIN_ID,
2473 FAInstanceID,
2474 XDP_FE_CMD_AUD_TRAILS_S.NEXTVAL,
2475 l_FeName,
2476 l_FeType,
2477 l_Sw_Generic,
2478 l_CommandSent,
2479 SentDate,
2480 l_Response,
2481 l_ResponseLong,
2482 RespDate,
2483 ProcName);
2484
2485 commit;
2486
2487 exception
2488 when others then
2489 xdp_utilities.generic_error('XDP_AQ_UTILITIES.LogCommandAuditTrail',
2490 'FA Instance: ' || FAInstanceID,
2491 sqlcode,
2492 sqlerrm);
2493 END LogCommandAuditTrail;
2494
2495 PROCEDURE Find_XDP_SCHEMA
2496 IS
2497 lv1 varchar2(80);
2498 lv2 varchar2(80);
2499 lv_schema varchar2(80);
2500 lv_ret BOOLEAN;
2501
2502 BEGIN
2503 lv_ret := FND_INSTALLATION.get_app_info(
2504 'XDP',
2505 lv1,
2506 lv2,
2507 lv_schema);
2508 G_XDP_SCHEMA := NVL(lv_schema,'XDP');
2509
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 G_XDP_SCHEMA := 'XDP';
2513 END Find_XDP_SCHEMA;
2514
2515 PROCEDURE Find_XNP_SCHEMA
2516 IS
2517 lv1 varchar2(80);
2518 lv2 varchar2(80);
2519 lv_schema varchar2(80);
2520 lv_ret BOOLEAN;
2521
2522 BEGIN
2523 lv_ret := FND_INSTALLATION.get_app_info(
2524 'XNP',
2525 lv1,
2526 lv2,
2527 lv_schema);
2528 G_XNP_SCHEMA := NVL(lv_schema,'XNP');
2529
2530 EXCEPTION
2531 WHEN OTHERS THEN
2532 G_XNP_SCHEMA := 'XNP';
2533 END Find_XNP_SCHEMA;
2534
2535 /*
2536 * Dequeue from Event Queue
2537 */
2538 PROCEDURE DQ_XNP_EVT_Q( p_return_code OUT NOCOPY NUMBER,
2539 p_error_description OUT NOCOPY VARCHAR2) AS
2540
2541 l_time_out VARCHAR2(20) ;
2542 l_message_key VARCHAR2(2000) ;
2543 l_dq_count NUMBER ;
2544
2545 BEGIN
2546 p_return_code := 0 ;
2547 p_error_description := null ;
2548 l_dq_count := 0 ;
2549
2550 FOR i IN 1..G_DQ_COUNT
2551
2552 LOOP
2553 XNP_EVENT.PROCESS_IN_EVT( p_message_wait_timeout => g_msg_wait_timeout,
2554 p_correlation_id => null,
2555 x_message_key => l_message_key,
2556 x_queue_timed_out => l_time_out );
2557
2558 IF l_time_out = 'Y' THEN
2559 EXIT ;
2560 END IF ;
2561
2562 l_dq_count := l_dq_count + 1 ;
2563 DBMS_LOCK.SLEEP(g_sleep_time);
2564
2565 END LOOP ;
2566
2567 fnd_file.put_line(fnd_file.output,'Total Number of Events Dequeued : '||l_dq_count );
2568
2569
2570 EXCEPTION
2571 WHEN others THEN
2572 p_return_code := sqlcode ;
2573 p_error_description := sqlerrm ;
2574 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2575 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2576
2577 END DQ_XNP_EVT_Q ;
2578
2579 /*
2580 * Dequeue from Inbound Message Queue
2581 */
2582 PROCEDURE DQ_XNP_IN_MSG_Q( p_return_code OUT NOCOPY NUMBER,
2583 p_error_description OUT NOCOPY VARCHAR2) AS
2584
2585 l_time_out VARCHAR2(20) ;
2586 l_message_key VARCHAR2(2000) ;
2587 l_dq_count NUMBER ;
2588
2589 BEGIN
2590 p_return_code := 0 ;
2591 p_error_description := null ;
2592 l_dq_count := 0 ;
2593
2594 FOR i IN 1..G_DQ_COUNT
2595
2596 LOOP
2597 XNP_EVENT.PROCESS_IN_MSG( p_message_wait_timeout => g_msg_wait_timeout,
2598 p_correlation_id => null,
2599 x_message_key => l_message_key,
2600 x_queue_timed_out => l_time_out );
2601
2602 IF l_time_out = 'Y' THEN
2603 EXIT ;
2604 END IF ;
2605
2606 l_dq_count := l_dq_count + 1 ;
2607 DBMS_LOCK.SLEEP(g_sleep_time);
2608
2609 END LOOP ;
2610
2611 fnd_file.put_line(fnd_file.output,'Total Number of Messages Dequeued : '||l_dq_count );
2612
2613
2614 EXCEPTION
2615 WHEN others THEN
2616 p_return_code := sqlcode ;
2617 p_error_description := sqlerrm ;
2618 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2619 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2620
2621 END DQ_XNP_IN_MSG_Q ;
2622
2623 /*
2624 * Dequeue from Timer Queue
2625 */
2626 PROCEDURE DQ_XNP_IN_TMR_Q( p_return_code OUT NOCOPY NUMBER,
2627 p_error_description OUT NOCOPY VARCHAR2) AS
2628
2629 l_time_out VARCHAR2(20) ;
2630 l_message_key VARCHAR2(2000) ;
2631 l_dq_count NUMBER ;
2632
2633 BEGIN
2634 p_return_code := 0 ;
2635 p_error_description := null ;
2636 l_dq_count := 0 ;
2637
2638 FOR i IN 1..G_DQ_COUNT
2639
2640 LOOP
2641 XNP_TIMER_MGR.PROCESS_IN_TMR( p_message_wait_timeout => g_msg_wait_timeout,
2642 p_correlation_id => null,
2643 x_message_key => l_message_key,
2644 x_queue_timed_out => l_time_out );
2645
2646 IF l_time_out = 'Y' THEN
2647 EXIT ;
2648 END IF ;
2649
2650 l_dq_count := l_dq_count + 1 ;
2651 DBMS_LOCK.SLEEP(g_sleep_time);
2652
2653 END LOOP ;
2654 fnd_file.put_line(fnd_file.output,'Total Number of Timers Dequeued: '||l_dq_count );
2655
2656 EXCEPTION
2657 WHEN others THEN
2658 p_return_code := sqlcode ;
2659 p_error_description := sqlerrm ;
2660 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2661 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2662
2663 END DQ_XNP_IN_TMR_Q ;
2664
2665 /*
2666 * Dequeue from Order Queue
2667 */
2668 PROCEDURE DQ_XDP_ORDER_PROC_QUEUE( p_return_code OUT NOCOPY NUMBER,
2669 p_error_description OUT NOCOPY VARCHAR2) AS
2670
2671 l_time_out VARCHAR2(20) ;
2672 l_message_key VARCHAR2(2000) ;
2673 l_dq_count NUMBER ;
2674
2675 BEGIN
2676 p_return_code := 0 ;
2677 p_error_description := null ;
2678 l_dq_count := 0 ;
2679
2680 FOR i IN 1..G_DQ_COUNT
2681
2682 LOOP
2683 XDP_AQ_UTILITIES.START_ORDERPROCESSOR_WORKFLOW( p_message_wait_timeout => g_msg_wait_timeout,
2684 p_correlation_id => null,
2685 x_message_key => l_message_key,
2686 x_queue_timed_out => l_time_out );
2687
2688 IF l_time_out = 'Y' THEN
2689 EXIT ;
2690 END IF ;
2691
2692 l_dq_count := l_dq_count + 1 ;
2693 DBMS_LOCK.SLEEP(g_sleep_time);
2694
2695 END LOOP ;
2696 fnd_file.put_line(fnd_file.output,'Total Number of Orders Dequeued : '||l_dq_count );
2697
2698 EXCEPTION
2699 WHEN others THEN
2700 p_return_code := sqlcode ;
2701 p_error_description := sqlerrm ;
2702 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2703 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2704
2705 END DQ_XDP_ORDER_PROC_QUEUE;
2706
2707 /*
2708 * Dequeue from Fulfillment Action Queue
2709 */
2710 PROCEDURE DQ_XDP_FA_QUEUE( p_return_code OUT NOCOPY NUMBER,
2711 p_error_description OUT NOCOPY VARCHAR2) AS
2712
2713 l_time_out VARCHAR2(20) ;
2714 l_message_key VARCHAR2(2000) ;
2715 l_dq_count NUMBER ;
2716
2717 BEGIN
2718 p_return_code := 0 ;
2719 p_error_description := null ;
2720 l_dq_count := 0 ;
2721
2722 FOR i IN 1..G_DQ_COUNT
2723
2724 LOOP
2725 XDP_AQ_UTILITIES.START_FA_WORKFLOW( p_message_wait_timeout => g_msg_wait_timeout,
2726 p_correlation_id => null,
2727 x_message_key => l_message_key,
2728 x_queue_timed_out => l_time_out );
2729
2730 IF l_time_out = 'Y' THEN
2731 EXIT ;
2732 END IF ;
2733
2734 l_dq_count := l_dq_count + 1 ;
2735 DBMS_LOCK.SLEEP(g_sleep_time);
2736
2737 END LOOP ;
2738 fnd_file.put_line(fnd_file.output,'Total Number of Fulfillment Actions Dequeued : '||l_dq_count );
2739
2740 EXCEPTION
2741 WHEN others THEN
2742 p_return_code := sqlcode ;
2743 p_error_description := sqlerrm ;
2744 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2745 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2746
2747 END DQ_XDP_FA_QUEUE;
2748
2749 /*
2750 * Dequeue from Fulfillment Actions Ready Queue
2751 */
2752 PROCEDURE DQ_XDP_WF_CHANNEL_Q( p_return_code OUT NOCOPY NUMBER,
2753 p_error_description OUT NOCOPY VARCHAR2) AS
2754
2755 l_time_out VARCHAR2(20) ;
2756 l_message_key VARCHAR2(2000) ;
2757 l_dq_count NUMBER ;
2758
2759 BEGIN
2760 p_return_code := 0 ;
2761 p_error_description := null ;
2762 l_dq_count := 0 ;
2763
2764 FOR i IN 1..G_DQ_COUNT
2765
2766 LOOP
2767 XDP_AQ_UTILITIES.RESUME_NEXT_WF( p_message_wait_timeout => g_msg_wait_timeout,
2768 p_correlation_id => null,
2769 x_message_key => l_message_key,
2770 x_queue_timed_out => l_time_out );
2771
2772 -- remember to uncomment this out VBhatia
2773
2774 IF l_time_out = 'Y' THEN
2775 EXIT ;
2776 END IF ;
2777
2778 l_dq_count := l_dq_count + 1 ;
2779 DBMS_LOCK.SLEEP(g_sleep_time);
2780
2781 END LOOP ;
2782 fnd_file.put_line(fnd_file.output,'Total Number FulFillment Element Ready Queue Dequeued : '||l_dq_count );
2783
2784 EXCEPTION
2785 WHEN others THEN
2786 p_return_code := sqlcode ;
2787 p_error_description := sqlerrm ;
2788 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2789 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2790
2791 END DQ_XDP_WF_CHANNEL_Q;
2792
2793 /*
2794 * Dequeue from Work Item Queue
2795 */
2796 PROCEDURE DQ_XDP_WORKITEM_QUEUE( p_return_code OUT NOCOPY NUMBER,
2797 p_error_description OUT NOCOPY VARCHAR2) AS
2798
2799 l_time_out VARCHAR2(20) ;
2800 l_message_key VARCHAR2(2000) ;
2801 l_dq_count NUMBER ;
2802
2803 BEGIN
2804 p_return_code := 0 ;
2805 p_error_description := null ;
2806 l_dq_count := 0 ;
2807
2808 FOR i IN 1..G_DQ_COUNT
2809
2810 LOOP
2811 XDP_AQ_UTILITIES.START_WORKITEM_WORKFLOW( p_message_wait_timeout => g_msg_wait_timeout,
2812 p_correlation_id => null,
2813 x_message_key => l_message_key,
2814 x_queue_timed_out => l_time_out );
2815
2816 IF l_time_out = 'Y' THEN
2817 EXIT ;
2818 END IF ;
2819
2820 l_dq_count := l_dq_count + 1 ;
2821 DBMS_LOCK.SLEEP(g_sleep_time);
2822
2823 END LOOP ;
2824 fnd_file.put_line(fnd_file.output,'Total Number of Workitems Dequeued : '||l_dq_count );
2825
2826 EXCEPTION
2827 WHEN others THEN
2828 p_return_code := sqlcode ;
2829 p_error_description := sqlerrm ;
2830 fnd_file.put_line(fnd_file.log,'Error Code : ' ||p_return_code);
2831 fnd_file.put_line(fnd_file.log,'Error Message : ' ||p_error_description);
2832
2833 END DQ_XDP_WORKITEM_QUEUE;
2834
2835
2836 /*
2837 * This procedure is called from a concurrent program - it reads entries from the Exception Queue
2838 * and re-enqueues them into the Normal Queue
2839 */
2840 PROCEDURE DQ_EXCP_REENQ( p_return_code OUT NOCOPY NUMBER,
2841 p_error_description OUT NOCOPY VARCHAR2 )
2842
2843 IS
2844
2845 BEGIN
2846
2847 p_return_code := 0 ;
2848 p_error_description := null ;
2849
2850 /* Order Queue */
2851 BEGIN
2852
2853 DQ_XDP_ORDER_PROC_REENQ( p_message_wait_timeout => g_msg_wait_timeout,
2854 p_correlation_id => null );
2855
2856 EXCEPTION
2857 WHEN others THEN
2858 p_return_code := sqlcode ;
2859 p_error_description := sqlerrm ;
2860 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XDP_ORDER_PROC_REENQ');
2861 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2862 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2863
2864 END;
2865
2866 /* Work Item */
2867 BEGIN
2868
2869 DQ_XDP_WORKITEM_REENQ( p_message_wait_timeout => g_msg_wait_timeout,
2870 p_correlation_id => null );
2871
2872 EXCEPTION
2873 WHEN others THEN
2874 p_return_code := p_return_code ||':'|| sqlcode ;
2875 p_error_description := p_error_description ||':'|| sqlerrm ;
2876 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XDP_WORKITEM_REENQ');
2877 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2878 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2879 END;
2880
2881 /* Fulfillment Actions */
2882 BEGIN
2883
2884 DQ_XDP_FA_REENQ( p_message_wait_timeout => g_msg_wait_timeout,
2885 p_correlation_id => null );
2886
2887 EXCEPTION
2888 WHEN others THEN
2889 p_return_code := p_return_code ||':'|| sqlcode ;
2890 p_error_description := p_error_description ||':'|| sqlerrm ;
2891 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XDP_FA_REENQ');
2892 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2893 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2894 END;
2895
2896 /* Fulfillment Actions Ready */
2897 BEGIN
2898
2899 DQ_XDP_WF_CHANNEL_REENQ( p_message_wait_timeout => g_msg_wait_timeout,
2900 p_correlation_id => null );
2901
2902 EXCEPTION
2903 WHEN others THEN
2904 p_return_code := p_return_code ||':'|| sqlcode ;
2905 p_error_description := p_error_description ||':'|| sqlerrm ;
2906 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XDP_WF_CHANNEL_REENQ');
2907 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2908 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2909 END;
2910
2911 /* Inbound Message */
2912 BEGIN
2913
2914 DQ_XNP_IN_MSG_REENQ( p_message_wait_timeout => g_msg_wait_timeout );
2915
2916 EXCEPTION
2917 WHEN others THEN
2918 p_return_code := p_return_code ||':'|| sqlcode ;
2919 p_error_description := p_error_description ||':'|| sqlerrm ;
2920 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XNP_IN_MSG_REENQ');
2921 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2922 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2923 END;
2924
2925 /* Internal Event */
2926 BEGIN
2927
2928 DQ_XNP_IN_EVT_REENQ( p_message_wait_timeout => g_msg_wait_timeout );
2929
2930 EXCEPTION
2931 WHEN others THEN
2932 p_return_code := p_return_code ||':'|| sqlcode ;
2933 p_error_description := p_error_description ||':'|| sqlerrm ;
2934 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XNP_IN_EVT_REENQ');
2935 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2936 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2937 END;
2938
2939 /* Timer */
2940 BEGIN
2941
2942 DQ_XNP_IN_TMR_REENQ( p_message_wait_timeout => g_msg_wait_timeout );
2943
2944 EXCEPTION
2945 WHEN others THEN
2946 p_return_code := p_return_code ||':'|| sqlcode ;
2947 p_error_description := p_error_description ||':'|| sqlerrm ;
2948 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XNP_IN_TMR_REENQ');
2949 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2950 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2951 END;
2952
2953 /* Outbound */
2954 BEGIN
2955
2956 DQ_XNP_OUT_MSG_REENQ( p_message_wait_timeout => g_msg_wait_timeout );
2957
2958 EXCEPTION
2959 WHEN others THEN
2960 p_return_code := p_return_code ||':'|| sqlcode ;
2961 p_error_description := p_error_description ||':'|| sqlerrm ;
2962 fnd_file.put_line(fnd_file.log,'Error in DQ_EXCP_REENQ: DQ_XNP_OUT_MSG_REENQ');
2963 fnd_file.put_line(fnd_file.log,'Error Code : ' ||sqlcode);
2964 fnd_file.put_line(fnd_file.log,'Error Message : ' ||sqlerrm);
2965 END;
2966
2967 END DQ_EXCP_REENQ;
2968
2969
2970 PROCEDURE DQ_XDP_ORDER_PROC_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
2971 p_correlation_id IN VARCHAR2 )
2972
2973 IS
2974
2975 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
2976 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
2977 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
2978 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
2979 lv_MsgID RAW(16);
2980 lv_return_code NUMBER;
2981 lv_error_description VARCHAR2(2000);
2982 l_dq_count NUMBER ;
2983 x_queue_timed_out VARCHAR2(1);
2984
2985 BEGIN
2986
2987 savepoint order_q_tag;
2988 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
2989 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
2990 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE;
2991 lv_DequeueOptions.MSGID := NULL;
2992 lv_DequeueOptions.correlation := p_correlation_id;
2993
2994 -- Set Dequeue time out to be 1 second
2995 lv_DequeueOptions.wait := p_message_wait_timeout;
2996 x_queue_timed_out := 'N';
2997 l_dq_count := 0;
2998
2999 FOR i IN 1..G_DQ_COUNT
3000 LOOP
3001
3002 BEGIN
3003 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_ORDER_PROCESSOR_EXPQ',
3004 dequeue_options => lv_DequeueOptions,
3005 message_properties => lv_MessageProperties,
3006 payload => lv_wf_object,
3007 msgid => lv_MsgID);
3008 EXCEPTION
3009 WHEN e_QTimeOut THEN
3010 x_queue_timed_out := 'Y';
3011 END;
3012
3013 IF x_queue_timed_out = 'Y' THEN
3014 EXIT ;
3015
3016 ELSE
3017
3018 Add_OrderToProcessorQ( p_order_id => lv_wf_object.order_id ,
3019 p_wf_item_type => lv_wf_object.WF_ITEM_TYPE ,
3020 p_wf_item_key => lv_wf_object.WF_ITEM_KEY );
3021 END IF ;
3022
3023 l_dq_count := l_dq_count + 1 ;
3024 DBMS_LOCK.SLEEP(g_sleep_time);
3025
3026 END LOOP ;
3027 fnd_file.put_line(fnd_file.output,'Total Number of Orders Dequeued from Order Exception Queue : '||l_dq_count );
3028
3029 EXCEPTION
3030 WHEN OTHERS THEN
3031 ROLLBACK TO order_q_tag;
3032 RAISE;
3033
3034 END DQ_XDP_ORDER_PROC_REENQ;
3035
3036
3037 PROCEDURE DQ_XDP_FA_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
3038 p_correlation_id IN VARCHAR2 )
3039
3040 IS
3041
3042 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
3043 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
3044 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
3045 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
3046 lv_MsgID RAW(16);
3047 lv_return_code NUMBER;
3048 lv_error_description VARCHAR2(2000);
3049 l_dq_count NUMBER ;
3050 x_queue_timed_out VARCHAR2(1);
3051
3052 BEGIN
3053
3054 savepoint fa_q_tag;
3055 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
3056 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
3057 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE;
3058 lv_DequeueOptions.MSGID := NULL;
3059 lv_DequeueOptions.correlation := p_correlation_id;
3060
3061 -- Set Dequeue time out to be 1 second
3062 lv_DequeueOptions.wait := p_message_wait_timeout;
3063 x_queue_timed_out := 'N';
3064 l_dq_count := 0;
3065
3066 FOR i IN 1..G_DQ_COUNT
3067 LOOP
3068
3069 BEGIN
3070 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_FA_EXPQ',
3071 dequeue_options => lv_DequeueOptions,
3072 message_properties => lv_MessageProperties,
3073 payload => lv_wf_object,
3074 msgid => lv_MsgID);
3075 EXCEPTION
3076 WHEN e_QTimeOut THEN
3077 x_queue_timed_out := 'Y';
3078 END;
3079
3080 IF x_queue_timed_out = 'Y' THEN
3081 EXIT ;
3082
3083 ELSE
3084 Add_FA_ToQ( p_order_id => lv_wf_object.order_id ,
3085 p_wi_instance_id => lv_wf_object.WORKITEM_INSTANCE_ID ,
3086 p_fa_instance_id => lv_wf_object.FA_INSTANCE_ID ,
3087 p_wf_item_type => lv_wf_object.WF_ITEM_TYPE ,
3088 p_wf_item_key => lv_wf_object.WF_ITEM_KEY ,
3089 p_return_code => lv_return_code ,
3090 p_error_description => lv_error_description );
3091
3092 IF( lv_return_code <> 0 ) THEN
3093 fnd_file.put_line(fnd_file.log, 'Could not ReEnqueue Fulfillment Action - Error: ' ||lv_error_description);
3094 ROLLBACK TO fa_q_tag;
3095 RETURN;
3096 END IF;
3097
3098 END IF ;
3099 l_dq_count := l_dq_count + 1 ;
3100 DBMS_LOCK.SLEEP(g_sleep_time);
3101
3102 END LOOP ;
3103 fnd_file.put_line(fnd_file.output,'Total Number of Fulfillment Actions Dequeued from Fulfillment Actions Exception Queue : '||l_dq_count );
3104
3105 EXCEPTION
3106 WHEN OTHERS THEN
3107 ROLLBACK TO fa_q_tag;
3108 RAISE;
3109
3110 END DQ_XDP_FA_REENQ;
3111
3112
3113 PROCEDURE DQ_XDP_WORKITEM_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
3114 p_correlation_id IN VARCHAR2 )
3115
3116 IS
3117
3118 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
3119 lv_tmp SYSTEM.XDP_WF_CHANNELQ_TYPE;
3120 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
3121 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
3122 lv_MsgID RAW(16);
3123 lv_return_code NUMBER;
3124 lv_error_description VARCHAR2(2000);
3125 l_dq_count NUMBER ;
3126 x_queue_timed_out VARCHAR2(1);
3127
3128 BEGIN
3129
3130 savepoint workitem_q_tag;
3131 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
3132 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
3133 lv_DequeueOptions.dequeue_mode := DBMS_AQ.REMOVE;
3134 lv_DequeueOptions.MSGID := NULL;
3135 lv_DequeueOptions.correlation := p_correlation_id;
3136
3137 -- Set Dequeue time out to be 1 second
3138 lv_DequeueOptions.wait := p_message_wait_timeout;
3139 x_queue_timed_out := 'N';
3140 l_dq_count := 0;
3141
3142 FOR i IN 1..G_DQ_COUNT
3143 LOOP
3144
3145 BEGIN
3146 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WORKITEM_EXPQ',
3147 dequeue_options => lv_DequeueOptions,
3148 message_properties => lv_MessageProperties,
3149 payload => lv_wf_object,
3150 msgid => lv_MsgID);
3151 EXCEPTION
3152 WHEN e_QTimeOut THEN
3153 x_queue_timed_out := 'Y';
3154 END;
3155
3156 IF x_queue_timed_out = 'Y' THEN
3157 EXIT ;
3158
3159 ELSE
3160 Add_WorkItem_ToQ( p_order_id => lv_wf_object.order_id ,
3161 p_wi_instance_id => lv_wf_object.WORKITEM_INSTANCE_ID ,
3162 p_prov_date => GetWIProvisioningDate( lv_wf_object.WORKITEM_INSTANCE_ID ) ,
3163 p_wf_item_type => lv_wf_object.WF_ITEM_TYPE ,
3164 p_wf_item_key => lv_wf_object.WF_ITEM_KEY ,
3165 p_return_code => lv_return_code ,
3166 p_error_description => lv_error_description );
3167
3168 IF( lv_return_code <> 0 ) THEN
3169 fnd_file.put_line(fnd_file.log, 'Could not ReEnqueue WorkItem - Error: ' ||lv_error_description);
3170 ROLLBACK TO workitem_q_tag;
3171 RETURN;
3172 END IF;
3173
3174 END IF ;
3175 l_dq_count := l_dq_count + 1 ;
3176 DBMS_LOCK.SLEEP(g_sleep_time);
3177
3178 END LOOP ;
3179 fnd_file.put_line(fnd_file.output,'Total Number of WorkItems dequeued from WorkItem Exception Queue : '||l_dq_count );
3180
3181 EXCEPTION
3182 WHEN OTHERS THEN
3183 ROLLBACK TO workitem_q_tag;
3184 RAISE;
3185
3186 END DQ_XDP_WORKITEM_REENQ;
3187
3188 FUNCTION GetWIProvisioningDate(p_workitem_instance_id IN NUMBER)
3189 RETURN DATE IS
3190
3191 l_prov_date DATE ;
3192 x_progress VARCHAR2(2000);
3193
3194 BEGIN
3195 SELECT provisioning_date
3196 INTO l_prov_date
3197 FROM xdp_fulfill_worklist
3198 WHERE workitem_instance_id = p_workitem_instance_id ;
3199 RETURN l_prov_date;
3200 EXCEPTION
3201 WHEN OTHERS THEN
3202 fnd_file.put_line(fnd_file.log,'Could not get the provisioning date for WorkItem Instance Id');
3203 RAISE;
3204
3205 END GetWIProvisioningDate ;
3206
3207
3208 PROCEDURE DQ_XDP_WF_CHANNEL_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
3209 p_correlation_id IN VARCHAR2 )
3210
3211 IS
3212
3213 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
3214 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
3215 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
3216 lv_MsgID RAW(16);
3217 lv_return_code NUMBER;
3218 lv_error_description VARCHAR2(2000);
3219 l_dq_count NUMBER ;
3220 x_queue_timed_out VARCHAR2(1);
3221
3222 BEGIN
3223
3224 savepoint resume_wf;
3225 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
3226 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
3227 lv_DequeueOptions.correlation := p_correlation_id;
3228
3229 -- Set Dequeue time out to be 1 second
3230 lv_DequeueOptions.wait := p_message_wait_timeout;
3231 x_queue_timed_out := 'N';
3232 l_dq_count := 0;
3233
3234 FOR i IN 1..G_DQ_COUNT
3235 LOOP
3236
3237 BEGIN
3238 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_CHANNEL_EXCEPTION_Q',
3239 dequeue_options => lv_DequeueOptions,
3240 message_properties => lv_MessageProperties,
3241 payload => lv_wf_object,
3242 msgid => lv_MsgID);
3243 EXCEPTION
3244 WHEN e_QTimeOut THEN
3245 x_queue_timed_out := 'Y';
3246 END;
3247
3248 IF x_queue_timed_out = 'Y' THEN
3249 EXIT ;
3250
3251 ELSE
3252 HANDOVER_CHANNEL( p_channel_name => lv_wf_object.CHANNEL_NAME ,
3253 p_fe_name => lv_wf_object.FE_NAME ,
3254 p_wf_item_type => lv_wf_object.WF_ITEM_TYPE ,
3255 p_wf_item_key => lv_wf_object.WF_ITEM_KEY ,
3256 p_wf_activity => lv_wf_object.WF_ACTIVITY_NAME ,
3257 p_order_id => lv_wf_object.ORDER_ID ,
3258 p_wi_instance_id => lv_wf_object.WORKITEM_INSTANCE_ID ,
3259 p_fa_instance_id => lv_wf_object.FA_INSTANCE_ID ,
3260 p_return_code => lv_return_code ,
3261 p_error_description => lv_error_description );
3262
3263 IF( lv_return_code <> 0 ) THEN
3264 fnd_file.put_line(fnd_file.log, 'Could not ReEnqueue from FE Ready - Error: ' ||lv_error_description);
3265 ROLLBACK TO resume_wf;
3266 RETURN;
3267 END IF;
3268
3269 END IF ;
3270 l_dq_count := l_dq_count + 1 ;
3271 DBMS_LOCK.SLEEP(g_sleep_time);
3272
3273 END LOOP ;
3274 fnd_file.put_line(fnd_file.output,'Total Number dequeued from FE Ready Exception Queue : '||l_dq_count );
3275
3276 EXCEPTION
3277 WHEN OTHERS THEN
3278 ROLLBACK TO resume_wf;
3279 RAISE;
3280
3281 END DQ_XDP_WF_CHANNEL_REENQ;
3282
3283
3284 PROCEDURE DQ_XNP_IN_MSG_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 )
3285
3286 IS
3287
3288 l_dq_count NUMBER ;
3289 l_error_code NUMBER;
3290 l_error_message VARCHAR2(4000);
3291 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
3292 l_msg_text VARCHAR2(32767);
3293 l_body_text VARCHAR2(32767);
3294 x_queue_timed_out VARCHAR2(1);
3295
3296 BEGIN
3297
3298 savepoint before_msg_pop;
3299
3300 x_queue_timed_out := 'N';
3301 l_dq_count := 0;
3302 l_error_code := 0;
3303 l_error_message := NULL;
3304
3305 FOR i IN 1..G_DQ_COUNT
3306 LOOP
3307
3308 XNP_MESSAGE.POP( p_queue_name => G_XNP_SCHEMA ||'.'||'XNP_IN_MSG_EXCEPTION_Q',
3309 x_msg_header => l_msg_header,
3310 x_body_text => l_body_text,
3311 x_error_code => l_error_code,
3312 x_error_message => l_error_message
3313 );
3314
3315 IF ( l_error_code = XNP_ERRORS.G_DEQUEUE_TIMEOUT ) THEN
3316 x_queue_timed_out := 'Y';
3317 END IF ;
3318
3319 IF x_queue_timed_out = 'Y' THEN
3320 EXIT ;
3321
3322 ELSE
3323
3324 ReENQUEUE( p_msg_header => l_msg_header ,
3325 p_body_text => l_body_text ,
3326 p_queue_name => xnp_event.c_inbound_msg_q ,
3327 p_correlation_id => l_msg_header.message_code ,
3328 p_commit_mode => xnp_message.c_on_commit );
3329
3330 END IF ;
3331
3332 l_dq_count := l_dq_count + 1 ;
3333 DBMS_LOCK.SLEEP(g_sleep_time);
3334
3335 END LOOP ;
3336
3337 fnd_file.put_line(fnd_file.output,'Total Number dequeued from Inbound Messages Exception Queue : '||l_dq_count );
3338
3339 EXCEPTION
3340 WHEN OTHERS THEN
3341 fnd_file.put_line(fnd_file.output,' Could not Re-enqueue from Inbound Messages Exception Queue: '||SQLCODE||':'||SQLERRM);
3342 ROLLBACK TO before_msg_pop;
3343 RAISE;
3344
3345 END DQ_XNP_IN_MSG_REENQ;
3346
3347
3348 PROCEDURE DQ_XNP_IN_EVT_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 )
3349
3350 IS
3351
3352 l_dq_count NUMBER ;
3353 l_error_code NUMBER;
3354 l_error_message VARCHAR2(4000);
3355 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
3356 l_msg_text VARCHAR2(32767);
3357 l_body_text VARCHAR2(32767);
3358 x_queue_timed_out VARCHAR2(1);
3359
3360 BEGIN
3361
3362 savepoint before_evt_pop;
3363
3364 x_queue_timed_out := 'N';
3365 l_dq_count := 0;
3366 l_error_code := 0;
3367 l_error_message := NULL;
3368
3369 FOR i IN 1..G_DQ_COUNT
3370 LOOP
3371
3372 XNP_MESSAGE.POP( p_queue_name => G_XNP_SCHEMA ||'.'||'XNP_IN_EVT_EXCEPTION_Q',
3373 x_msg_header => l_msg_header,
3374 x_body_text => l_body_text,
3375 x_error_code => l_error_code,
3376 x_error_message => l_error_message
3377 );
3378
3379 IF ( l_error_code = XNP_ERRORS.G_DEQUEUE_TIMEOUT ) THEN
3380 x_queue_timed_out := 'Y';
3381 END IF ;
3382
3383 IF x_queue_timed_out = 'Y' THEN
3384 EXIT ;
3385
3386 ELSE
3387
3388 ReENQUEUE( p_msg_header => l_msg_header ,
3389 p_body_text => l_body_text ,
3390 p_queue_name => xnp_event.c_internal_evt_q ,
3391 p_correlation_id => l_msg_header.message_code ,
3392 p_commit_mode => xnp_message.c_on_commit );
3393
3394 END IF ;
3395 l_dq_count := l_dq_count + 1 ;
3396 DBMS_LOCK.SLEEP(g_sleep_time);
3397
3398 END LOOP ;
3399 fnd_file.put_line(fnd_file.output,'Total Number dequeued from Events Exception Queue : '||l_dq_count );
3400
3401 EXCEPTION
3402 WHEN OTHERS THEN
3403 fnd_file.put_line(fnd_file.output,' Could not Re-enqueue from Events Exception Queue: '||SQLCODE||':'||SQLERRM);
3404 ROLLBACK TO before_evt_pop;
3405 RAISE;
3406
3407 END DQ_XNP_IN_EVT_REENQ;
3408
3409
3410 PROCEDURE DQ_XNP_IN_TMR_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 )
3411
3412 IS
3413
3414 l_dq_count NUMBER ;
3415 l_error_code NUMBER;
3416 l_error_message VARCHAR2(4000);
3417 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
3418 l_msg_text VARCHAR2(32767);
3419 l_body_text VARCHAR2(32767);
3420 x_queue_timed_out VARCHAR2(1);
3421
3422 BEGIN
3423
3424 savepoint before_tmr_pop;
3425
3426 x_queue_timed_out := 'N';
3427 l_dq_count := 0;
3428 l_error_code := 0;
3429 l_error_message := NULL;
3430
3431 FOR i IN 1..G_DQ_COUNT
3432 LOOP
3433
3434 XNP_MESSAGE.POP( p_queue_name => G_XNP_SCHEMA ||'.'||'XNP_IN_TMR_EXCEPTION_Q',
3435 x_msg_header => l_msg_header,
3436 x_body_text => l_body_text,
3437 x_error_code => l_error_code,
3438 x_error_message => l_error_message
3439 );
3440
3441 IF ( l_error_code = XNP_ERRORS.G_DEQUEUE_TIMEOUT ) THEN
3442 x_queue_timed_out := 'Y';
3443 END IF ;
3444
3445 IF x_queue_timed_out = 'Y' THEN
3446 EXIT ;
3447
3448 ELSE
3449
3450 ReENQUEUE( p_msg_header => l_msg_header ,
3451 p_body_text => l_body_text ,
3452 p_queue_name => xnp_event.c_timer_q ,
3453 p_priority => 1 ,
3454 p_commit_mode => xnp_message.c_on_commit);
3455
3456 END IF ;
3457 l_dq_count := l_dq_count + 1 ;
3458 DBMS_LOCK.SLEEP(g_sleep_time);
3459
3460 END LOOP ;
3461 fnd_file.put_line(fnd_file.output,'Total Number dequeued from Timer Exception Queue : '||l_dq_count );
3462
3463 EXCEPTION
3464 WHEN OTHERS THEN
3465 fnd_file.put_line(fnd_file.output,' Could not Re-enqueue from Timer Exception Queue: '||SQLCODE||':'||SQLERRM);
3466 ROLLBACK TO before_tmr_pop;
3467 RAISE;
3468
3469 END DQ_XNP_IN_TMR_REENQ;
3470
3471
3472 PROCEDURE DQ_XNP_OUT_MSG_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 )
3473
3474 IS
3475
3476 l_dq_count NUMBER ;
3477 x_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
3478 x_msg_text VARCHAR2(32767);
3479 x_body_text VARCHAR2(32767);
3480 x_queue_timed_out VARCHAR2(1);
3481 l_msg_status VARCHAR2(40) ;
3482 l_message SYSTEM.XNP_MESSAGE_TYPE ;
3483 my_dequeue_options dbms_aq.dequeue_options_t ;
3484 message_properties dbms_aq.message_properties_t ;
3485 message_handle RAW(16) ;
3486 dq_time_out EXCEPTION;
3487
3488 PRAGMA EXCEPTION_INIT ( dq_time_out, -25228 );
3489
3490 BEGIN
3491
3492 savepoint before_out_msg_pop;
3493
3494 x_queue_timed_out := 'N';
3495 l_dq_count := 0;
3496
3497 my_dequeue_options.wait := p_message_wait_timeout;
3498 my_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
3499 my_dequeue_options.consumer_name := NULL;
3500 my_dequeue_options.correlation := NULL;
3501 my_dequeue_options.visibility := DBMS_AQ.ON_COMMIT;
3502
3503 FOR i IN 1..G_DQ_COUNT
3504 LOOP
3505
3506 BEGIN
3507
3508 my_dequeue_options.dequeue_mode := DBMS_AQ.LOCKED;
3509 my_dequeue_options.msgid := NULL;
3510
3511 LOOP
3512
3513 DBMS_AQ.DEQUEUE ( queue_name => G_XNP_SCHEMA ||'.'||'XNP_OUT_MSG_EXCEPTION_Q',
3514 dequeue_options => my_dequeue_options,
3515 message_properties => message_properties,
3516 payload => l_message,
3517 msgid => message_handle );
3518
3519 x_msg_header.message_id := l_message.message_id;
3520 xnp_message.get_status(l_message.message_id, l_msg_status);
3521
3522 IF ( l_msg_status = 'READY' OR l_msg_status = 'PROCESSED' ) THEN
3523
3524 xnp_message.get(
3525 p_msg_id => l_message.message_id
3526 ,x_msg_header => x_msg_header
3527 ,x_msg_text => x_body_text);
3528
3529 EXIT;
3530
3531 END IF;
3532
3533 END LOOP;
3534
3535 EXCEPTION
3536 WHEN dq_time_out THEN
3537 x_queue_timed_out := 'Y';
3538 END;
3539 IF x_queue_timed_out = 'Y' THEN
3540 EXIT ;
3541
3542 ELSE
3543
3544 ReENQUEUE( p_msg_header => x_msg_header ,
3545 p_body_text => x_body_text ,
3546 p_queue_name => xnp_event.c_outbound_msg_q ,
3547 p_correlation_id => x_msg_header.message_code ,
3548 p_commit_mode => xnp_message.c_on_commit);
3549
3550 END IF ;
3551 my_dequeue_options.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
3552 my_dequeue_options.msgid := message_handle;
3553
3554 DBMS_AQ.DEQUEUE ( queue_name => G_XNP_SCHEMA||'.'||'XNP_OUT_MSG_EXCEPTION_Q',
3555 dequeue_options => my_dequeue_options,
3556 message_properties => message_properties,
3557 payload => l_message,
3558 msgid => message_handle );
3559
3560 l_dq_count := l_dq_count + 1 ;
3561 DBMS_LOCK.SLEEP(g_sleep_time);
3562
3563 END LOOP ;
3564
3565 fnd_file.put_line(fnd_file.output,'Total Number dequeued from Outbound Msg Exception Queue : '||l_dq_count );
3566
3567 EXCEPTION
3568 WHEN OTHERS THEN
3569 fnd_file.put_line(fnd_file.output,'Could not Re-enqueue from Outbound Msg Exception Queue: '||SQLCODE||':'||SQLERRM);
3570 ROLLBACK TO before_out_msg_pop;
3571 RAISE;
3572
3573 END DQ_XNP_OUT_MSG_REENQ;
3574
3575
3576 PROCEDURE ReENQUEUE( p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE
3577 ,p_body_text IN VARCHAR2
3578 ,p_queue_name IN VARCHAR2
3579 ,p_correlation_id IN VARCHAR2 DEFAULT NULL
3580 ,p_priority IN INTEGER DEFAULT 1
3581 ,p_commit_mode IN NUMBER DEFAULT XNP_MESSAGE.C_ON_COMMIT
3582 ,p_delay IN NUMBER DEFAULT DBMS_AQ.NO_DELAY
3583 )
3584
3585 IS
3586
3587 l_message SYSTEM.XNP_MESSAGE_TYPE ;
3588 my_enqueue_options dbms_aq.enqueue_options_t ;
3589 message_properties dbms_aq.message_properties_t ;
3590 message_handle RAW(16) ;
3591 recipients dbms_aq.aq$_recipient_list_t ;
3592
3593 l_recipient_name VARCHAR2(80) ;
3594 l_recipient_count INTEGER ;
3595 l_initial_pos INTEGER ;
3596 l_delimeter_pos INTEGER ;
3597
3598 l_correlation_id VARCHAR2(1024) ;
3599 l_msg_header xnp_message.msg_header_rec_type ;
3600
3601 CURSOR get_consumer_name(l_msg system.xnp_message_type) IS
3602 SELECT consumer_name
3603 FROM AQ$XNP_OUT_MSG_QTAB a
3604 WHERE a.user_data = l_msg
3605 AND a.queue = 'XNP_OUT_MSG_EXCEPTION_Q';
3606
3607 BEGIN
3608
3609 l_msg_header := p_msg_header ;
3610 l_correlation_id := l_msg_header.message_code ;
3611
3612 UPDATE xnp_msgs
3613 SET msg_status = 'READY',
3614 last_update_date = SYSDATE
3615 WHERE msg_id = l_msg_header.message_id;
3616
3617 IF (p_commit_mode = XNP_MESSAGE.C_IMMEDIATE) THEN
3618 COMMIT ;
3619 END IF ;
3620
3621 IF (p_priority IS NOT NULL) THEN
3622 message_properties.priority := p_priority ;
3623 END IF ;
3624
3625 IF (p_delay IS NOT NULL) THEN
3626 message_properties.delay := p_delay ;
3627 END IF;
3628
3629 l_message := SYSTEM.xnp_message_type( l_msg_header.message_id ) ;
3630
3631 -- Use the correlation ID if one is specified
3632
3633 IF ( l_correlation_id is NOT NULL ) THEN
3634 message_properties.correlation := l_correlation_id ;
3635 END IF ;
3636
3637 --
3638 -- Check if there are recipients, if there is no recipient, simply enqueue the
3639 -- message on the specified queue
3640 --
3641
3642 IF( p_queue_name = xnp_event.c_outbound_msg_q ) THEN
3643
3644 l_recipient_count := 1;
3645
3646 FOR rec in get_consumer_name(l_message)
3647
3648 LOOP
3649
3650 l_recipient_name := rec.consumer_name;
3651 recipients (l_recipient_count) := sys.aq$_agent ( l_recipient_name,
3652 NULL, NULL ) ;
3653 l_recipient_count := l_recipient_count + 1;
3654
3655 END LOOP;
3656
3657 message_properties.recipient_list := recipients ;
3658
3659 END IF;
3660
3661 IF (p_commit_mode = XNP_MESSAGE.C_IMMEDIATE) THEN
3662 my_enqueue_options.visibility := DBMS_AQ.IMMEDIATE ;
3663 ELSE
3664 my_enqueue_options.visibility := DBMS_AQ.ON_COMMIT ;
3665 END IF ;
3666 /* Smoolcha Fixed bug 3537144 Hard coded schema name */
3667 -- IF p_queue_name = 'XNP.XNP_IN_EVT_Q' THEN
3668 -- message_properties.exception_queue := 'XNP.XNP_IN_EVT_EXCEPTION_Q' ;
3669 -- ELSIF p_queue_name = 'XNP.XNP_IN_MSG_Q' THEN
3670 -- message_properties.exception_queue := 'XNP.XNP_IN_MSG_EXCEPTION_Q' ;
3671 -- ELSIF p_queue_name = 'XNP.XNP_IN_TMR_Q' THEN
3672 -- message_properties.exception_queue := 'XNP.XNP_IN_TMR_EXCEPTION_Q' ;
3673 -- ELSIF p_queue_name = 'XNP.XNP_OUT_MSG_Q' THEN
3674 -- message_properties.exception_queue := 'XNP.XNP_OUT_MSG_EXCEPTION_Q' ;
3675 -- END IF ;
3676
3677 IF instr(p_queue_name,'XNP_IN_EVT_Q') > 0 THEN
3678 message_properties.exception_queue := G_XNP_SCHEMA || '.XNP_IN_EVT_EXCEPTION_Q' ;
3679 ELSIF instr(p_queue_name,'XNP_IN_MSG_Q') > 0 THEN
3680 message_properties.exception_queue := G_XNP_SCHEMA || '.XNP_IN_MSG_EXCEPTION_Q' ;
3681 ELSIF instr(p_queue_name,'XNP_IN_TMR_Q') > 0 THEN
3682 message_properties.exception_queue := G_XNP_SCHEMA || '.XNP_IN_TMR_EXCEPTION_Q' ;
3683 ELSIF instr(p_queue_name,'XNP_OUT_MSG_Q') > 0 THEN
3684 message_properties.exception_queue := G_XNP_SCHEMA || '.XNP_OUT_MSG_EXCEPTION_Q' ;
3685 END IF ;
3686
3687 DBMS_AQ.ENQUEUE (
3688 queue_name => p_queue_name ,
3689 enqueue_options => my_enqueue_options,
3690 message_properties => message_properties,
3691 payload => l_message,
3692 msgid => message_handle ) ;
3693
3694 END ReENQUEUE;
3695
3696 /* Sets the context of an entity - message or order to determine whether */
3697 /* its highly available or not - VBhatia 06/14/2002 */
3698
3699 PROCEDURE SET_CONTEXT( object_id IN NUMBER
3700 ,object_key IN VARCHAR2 )
3701
3702 IS
3703
3704 BEGIN
3705 IF IS_AVAILABLE(object_id, object_key) = false THEN
3706 COMMIT;
3707 fnd_file.put_line(fnd_file.log,
3708 'SET_CONTEXT: Do not process further for '||object_key||' '||object_id);
3709 RAISE stop_processing;
3710 END IF;
3711
3712 END SET_CONTEXT;
3713
3714 /* Finds out if a message or an order is meant to be highly available or not */
3715 /* VBhatia 06/14/2002 */
3716
3717 FUNCTION IS_AVAILABLE( object_id IN NUMBER
3718 ,object_type IN VARCHAR2 )
3719 RETURN BOOLEAN IS
3720
3721 l_order_id number;
3722 l_count number;
3723
3724 BEGIN
3725
3726 l_order_id := object_id;
3727
3728 IF g_APPS_MAINTENANCE_MODE <> 'MAINT' THEN
3729 RETURN true;
3730
3731 ELSE IF object_type = 'MESSAGE_OBJECT' THEN
3732
3733 SELECT order_id
3734 INTO l_order_id
3735 FROM xnp_msgs
3736 WHERE msg_id = object_id;
3737
3738 IF l_order_id IS NULL THEN
3739 RETURN true; --Message is independent, therefore process it
3740 END IF;
3741 END IF;
3742 END IF;
3743
3744 SELECT count(*)
3745 INTO l_count
3746 FROM fnd_lookup_values
3747 WHERE UPPER(lookup_code) = ( SELECT UPPER(order_type)
3748 FROM xdp_order_headers
3749 WHERE order_id = l_order_id )
3750 AND lookup_type = 'XDP_HA_ORDER_TYPES';
3751
3752 IF l_count < 1 THEN
3753 RETURN false;
3754 ELSE
3755 RETURN true;
3756 END IF;
3757
3758 END IS_AVAILABLE;
3759
3760 /*
3761 Procedure to move messages from FE Ready queue back to Wait-for-channel table.
3762 This is called from admin script incase instances and added / dropped in RAC env.
3763 */
3764 PROCEDURE DQ_XDP_WF_CHANNEL_REPROCESS
3765
3766 IS
3767 lv_wf_object SYSTEM.XDP_WF_CHANNELQ_TYPE;
3768 lv_DequeueOptions DBMS_AQ.DEQUEUE_OPTIONS_T;
3769 lv_MessageProperties DBMS_AQ.MESSAGE_PROPERTIES_T;
3770 lv_MsgID RAW(16);
3771 lv_queue_timed_out VARCHAR2(1);
3772
3773 l_ReProcessEnqTime DATE;
3774 l_FeID number;
3775
3776
3777 BEGIN
3778
3779 lv_DequeueOptions.visibility := DBMS_AQ.ON_COMMIT;
3780 lv_DequeueOptions.navigation := DBMS_AQ.FIRST_MESSAGE;
3781 lv_DequeueOptions.correlation := null;
3782
3783 -- Set Dequeue time out to be 1 second
3784 lv_DequeueOptions.wait := 1;
3785 lv_queue_timed_out := 'N';
3786
3787 select nvl(min(queued_on), sysdate)-1 into l_ReProcessEnqTime from xdp_adapter_job_queue;
3788
3789 WHILE 1=1 LOOP
3790
3791 BEGIN
3792 DBMS_AQ.DEQUEUE( queue_name => G_XDP_SCHEMA||'.'||'XDP_WF_CHANNEL_Q',
3793 dequeue_options => lv_DequeueOptions,
3794 message_properties => lv_MessageProperties,
3795 payload => lv_wf_object,
3796 msgid => lv_MsgID);
3797 EXCEPTION
3798 WHEN e_QTimeOut THEN
3799 lv_queue_timed_out := 'Y';
3800 END;
3801
3802 IF lv_queue_timed_out = 'Y' THEN
3803 EXIT ;
3804
3805 ELSE
3806 lv_DequeueOptions.navigation := DBMS_AQ.NEXT_MESSAGE;
3807
3808 select FE_ID into l_FeID from xdp_fes
3809 where fulfillment_element_name = lv_wf_object.FE_NAME;
3810
3811 insert into XDP_ADAPTER_JOB_QUEUE (
3812 JOB_ID,
3813 FE_ID,
3814 ORDER_ID,
3815 WORKITEM_INSTANCE_ID,
3816 FA_INSTANCE_ID,
3817 QUEUED_ON,
3818 WF_ITEM_TYPE,
3819 CHANNEL_USAGE_CODE,
3820 WF_ITEM_KEY,
3821 SYSTEM_HOLD,
3822 created_by,
3823 creation_date,
3824 last_updated_by,
3825 last_update_date,
3826 last_update_login
3827 )
3828 values (XDP_ADAPTER_JOB_QUEUE_S.NEXTVAL,
3829 l_FeID,
3830 lv_wf_object.ORDER_ID,
3831 lv_wf_object.WORKITEM_INSTANCE_ID,
3832 lv_wf_object.FA_INSTANCE_ID,
3833 l_ReProcessEnqTime,
3834 lv_wf_object.WF_ITEM_TYPE,
3835 'NORMAL',
3836 lv_wf_object.WF_ITEM_KEY,
3837 'N',
3838 FND_GLOBAL.USER_ID,
3839 sysdate,
3840 FND_GLOBAL.USER_ID,
3841 sysdate,
3842 FND_GLOBAL.LOGIN_ID);
3843
3844 l_ReProcessEnqTime := l_ReProcessEnqTime + (1 / (24*60));
3845 END IF ;
3846
3847 END LOOP ;
3848
3849 END DQ_XDP_WF_CHANNEL_REPROCESS;
3850
3851 BEGIN
3852 -- Package initialization
3853 Find_XDP_SCHEMA;
3854 Find_XNP_SCHEMA;
3855
3856 -- Get APPS_MAINTENANCE_MODE parameter for High Availability
3857 FND_PROFILE.GET('APPS_MAINTENANCE_MODE', g_APPS_MAINTENANCE_MODE);
3858
3859 IF g_APPS_MAINTENANCE_MODE = 'MAINT' THEN
3860 /**** Set Log and Output File Names and Directory ****/
3861
3862 SELECT nvl(substr(value,1,instr(value,',')-1),value)
3863 INTO g_logdir
3864 FROM v$parameter
3865 WHERE name = 'utl_file_dir';
3866
3867 select sysdate into g_logdate from dual;
3868
3869 fnd_file.put_names('XDPAQUTB'||to_char(g_logdate, 'YYYYMMDDHHMISS')||'.log',
3870 'XDPAQUTB'||to_char(g_logdate, 'YYYYMMDDHHMISS')||'.out',
3871 g_logdir);
3872 END IF;
3873
3874 END XDP_AQ_UTILITIES;