[Home] [Help]
PACKAGE BODY: APPS.XNP_EVENT
Source
1 PACKAGE BODY xnp_event AS
2 /* $Header: XNPEVTPB.pls 120.1 2005/06/09 02:55:11 appldev $ */
3
4 PROCEDURE deliver(
5 p_msg_id IN NUMBER
6 ,p_callback_proc VARCHAR2
7 ,p_process_reference VARCHAR2
8 ,x_error_code OUT NOCOPY NUMBER
9 ,x_error_message OUT NOCOPY VARCHAR2
10 ) ;
11
12 e_procedure_exec_error EXCEPTION ;
13
14 g_new_line CHAR := fnd_global.local_chr(10) ;
15
16
17 /***************************************************************************
18 ***** Procedure: UNSUBSCRIBE()
19 ***** Purpose: Deregisters a registered callback.
20 ****************************************************************************/
21
22 PROCEDURE unsubscribe (
23 p_cb_event_id IN NUMBER
24 ,p_process_reference IN VARCHAR2
25 ,p_close_flag IN VARCHAR2 DEFAULT 'Y'
26 )
27 IS
28 BEGIN
29
30 -- adabholk 03/01 - Performance fix
31 -- Cursors replaced by explicit updates
32
33 IF (p_close_flag = 'Y') THEN
34
35 UPDATE xnp_callback_events
36 SET status = 'CLOSED',
37 callback_timestamp = sysdate
38 WHERE callback_event_id = p_cb_event_id;
39
40 END IF ;
41
42 UPDATE xnp_callback_events
43 SET status = 'EXPIRED',
44 callback_timestamp = sysdate
45 WHERE process_reference = p_process_reference
46 AND status = 'WAITING'
47 AND close_reqd_flag = 'Y';
48
49 END unsubscribe;
50
51
52 /***************************************************************************
53 ***** Procedure: DEREGISTER()
54 ***** Purpose: Deregisters a registered callback.
55 ****************************************************************************/
56
57 PROCEDURE deregister(
58 p_msg_code IN VARCHAR2
59 ,p_reference_id IN VARCHAR2
60 ) IS
61
62 BEGIN
63
64 -- adabholk 03/01 - Performance fix
65 -- Cursors replaced by explicit updates
66
67 UPDATE xnp_callback_events
68 SET status = 'EXPIRED',
69 callback_timestamp = sysdate
70 WHERE reference_id = p_reference_id
71 AND msg_code = p_msg_code;
72 END ;
73
74
75 /***************************************************************************
76 ***** Procedure: DEREGISTER()
77 ***** Purpose: Deregisters a callback procedure given the ORDER ID.
78 ****************************************************************************/
79 PROCEDURE deregister(
80 p_order_id IN NUMBER
81 ,x_error_code OUT NOCOPY NUMBER
82 ,x_error_message OUT NOCOPY VARCHAR2
83 )
84 IS
85 BEGIN
86 -- adabholk 03/01 - Performance fix
87 -- Cursors replaced by explicit updates
88
89 UPDATE xnp_callback_events
90 SET status = 'EXPIRED',
91 callback_timestamp = sysdate
92 WHERE order_id = p_order_id;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 x_error_code := SQLCODE;
97 x_error_message := SQLERRM;
98 END ;
99
100 /***************************************************************************
101 ***** Procedure: DEREGISTER_FOR_WORKITEM()
102 ***** Purpose: Deregisters a callback procedure given the WORKITEM_INSTNACE_ID.
103 ****************************************************************************/
104 PROCEDURE deregister_for_workitem
105 (
106 p_workitem_instance_id IN NUMBER
107 ,x_error_code OUT NOCOPY NUMBER
108 ,x_error_message OUT NOCOPY VARCHAR2
109 )
110 IS
111 BEGIN
112 UPDATE xnp_callback_events
113 SET status = 'EXPIRED',
114 callback_timestamp = sysdate
115 WHERE wi_instance_id = p_workitem_instance_id
116 AND status = 'WAITING';
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 x_error_code := SQLCODE;
121 x_error_message := SQLERRM;
122 END ;
123
124
125
126
127 /***************************************************************************
128 ***** Procedure: SUBSCRIBE()
129 ***** Purpose: SUBSCRIBEs a callback. Callbacks can be registered
130 ***** for any of the immediate responses from the peer
131 ***** system. The response will be delivered to the
132 ***** registered application on receiving it.
133 ****************************************************************************/
134
135 PROCEDURE subscribe (
136 p_msg_code IN VARCHAR2
137 ,p_reference_id IN VARCHAR2
138 ,p_process_reference IN VARCHAR2
139 ,p_procedure_name IN VARCHAR2
140 ,p_callback_type IN VARCHAR2
141 ,p_close_reqd_flag IN VARCHAR2 DEFAULT 'Y'
142 ,p_order_id IN NUMBER DEFAULT NULL
143 ,p_wi_instance_id IN NUMBER DEFAULT NULL
144 ,p_fa_instance_id IN NUMBER DEFAULT NULL
145 )
146 IS
147 BEGIN
148
149 INSERT INTO xnp_callback_events (
150 callback_event_id,
151 reference_id,
152 msg_code,
153 status,
154 process_reference,
155 registered_timestamp,
156 callback_type,
157 callback_proc_name,
158 close_reqd_flag,
159 order_id,
160 wi_instance_id,
161 fa_instance_id,
162 created_by,
163 creation_date,
164 last_updated_by,
165 last_update_date )
166 VALUES (
167 XNP_CALLBACK_EVENTS_S.nextval,
168 p_reference_id,
169 p_msg_code,
170 'WAITING',
171 p_process_reference,
172 SYSDATE,
173 p_callback_type,
174 p_procedure_name,
175 p_close_reqd_flag,
176 p_order_id,
177 p_wi_instance_id,
178 p_fa_instance_id,
179 fnd_global.user_id,
180 SYSDATE,
181 fnd_global.user_id,
182 SYSDATE );
183
184 END subscribe ;
185
186 /***************************************************************************
187 ***** Procedure: PROCESS()
188 ***** Purpose: Checks if a message has arrived and delivers it to all
189 ***** registered applications. Returns a timeout error if
190 ***** no message is received in the specified interval.
191 ****************************************************************************/
192
193 PROCEDURE process(
194 p_queue_name IN VARCHAR2
195 )
196 IS
197
198 l_error_code NUMBER ;
199 l_error_message VARCHAR2(4000) ;
200
201 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
202
203 l_msg_text VARCHAR2(32767) ;
204 l_num_apps NUMBER := 0;
205 l_operation VARCHAR2(4000) := NULL ;
206 l_description VARCHAR2(4000) := NULL ;
207 l_fnd_message VARCHAR2(4000) := NULL ;
208
209 l_order_id NUMBER;
210 l_wi_instance_id NUMBER;
211 l_fa_instance_id NUMBER ;
212
213 l_fatal_error BOOLEAN := FALSE;
214 l_queue_name VARCHAR2(40);
215 l_dummy NUMBER := 0;
216
217 -- Varrajar 3/2/2001
218 -- Peformance Fix Phase 4. Avoid the FTS on the Callback Events for default
219 -- subscribers by having a default "-1" value instead of NULL
220
221 CURSOR registered_apps ( l_reference_id IN VARCHAR2,
222 l_msg_code IN VARCHAR2 )
223 IS
224 SELECT callback_event_id,
225 callback_proc_name,
226 process_reference,
227 close_reqd_flag,
228 order_id,
229 wi_instance_id,
230 fa_instance_id
231 FROM XNP_CALLBACK_EVENTS
232 WHERE reference_id in (l_reference_id, '-1')
233 AND msg_code = l_msg_code
234 AND status = 'WAITING' ;
235
236 e_MSG_VALIDATION_ERROR EXCEPTION ;
237 e_MSG_PROCESSING_ERROR EXCEPTION ;
238 e_MSG_DELIVERY_ERROR EXCEPTION ;
239 e_DEFAULT_PROCESSING_ERROR EXCEPTION ;
240
241 BEGIN
242 BEGIN
243
244 l_error_code := 0 ;
245 l_error_message := NULL ;
246
247 xnp_message.pop (p_queue_name => p_queue_name,
248 x_msg_header => l_msg_header,
249 x_body_text => l_msg_text,
250 x_error_code => l_error_code,
251 x_error_message => l_error_message,
252 p_correlation_id => 'MSG_SERVER' ) ;
253
254 SAVEPOINT after_pop ;
255
256 IF ( l_error_code = XNP_ERRORS.G_DEQUEUE_TIMEOUT ) THEN
257 DBMS_LOCK.sleep(5) ;
258 RETURN ;
259 END IF ;
260
261 xnp_message.validate ( l_msg_header,
262 l_msg_text,
263 l_error_code,
264 l_error_message ) ;
265
266 IF (l_error_code <> 0) THEN
267 RAISE e_MSG_VALIDATION_ERROR ;
268 END IF ;
269
270 -- Process and deliver message to all registered applications
271
272 FOR app IN registered_apps ( l_msg_header.reference_id,
273 l_msg_header.message_code ) LOOP
274
275 l_order_id := app.order_id;
276 l_wi_instance_id := app.wi_instance_id;
277 l_fa_instance_id := app.fa_instance_id ;
278 l_msg_header.order_id := app.order_id;
279 l_msg_header.wi_instance_id := app.wi_instance_id;
280 l_msg_header.fa_instance_id := app.fa_instance_id ;
281
282 l_num_apps := l_num_apps + 1 ;
283
284 SAVEPOINT deliver_msg ;
285
286 BEGIN
287
288 xnp_message.process ( l_msg_header,
289 l_msg_text,
290 app.process_reference,
291 l_error_code,
292 l_error_message ) ;
293
294 IF (l_error_code <> 0) THEN
295 RAISE e_MSG_PROCESSING_ERROR ;
296 END IF ;
297
298 xnp_event.unsubscribe ( app.callback_event_id,
299 app.process_reference, app.close_reqd_flag ) ;
300
301 deliver ( l_msg_header.message_id,
302 app.callback_proc_name,
303 app.process_reference,
304 l_error_code,
305 l_error_message ) ;
306
307 IF (l_error_code <> 0) THEN
308 RAISE e_MSG_DELIVERY_ERROR ;
309 END IF ;
310
311
312 xnp_message.update_status(
313 p_msg_id=>l_msg_header.message_id,
314 p_status=>'PROCESSED',
315 p_order_id=>l_order_id,
316 p_wi_instance_id=>l_wi_instance_id,
317 p_fa_instance_id=>l_fa_instance_id) ;
318
319 EXCEPTION
320 WHEN e_MSG_DELIVERY_ERROR THEN
321 ROLLBACK to deliver_msg ;
322
323 fnd_message.set_name ('XNP', 'MSG_DELIVERY_ERROR') ;
324 fnd_message.set_token ('CALLBACK',
325 app.callback_proc_name);
326 fnd_message.set_token ('ERROR_CODE',
327 TO_CHAR(l_error_code)) ;
328 fnd_message.set_token ('ERROR_MESSAGE',
329 l_error_message) ;
330
331 l_fnd_message:= fnd_message.get ;
332
333 xnp_message.update_status(
334 p_msg_id=>l_msg_header.message_id,
335 p_status=>'FAILED',
336 p_error_desc=>l_fnd_message,
337 p_order_id=>l_order_id,
338 p_wi_instance_id=>l_wi_instance_id,
339 p_fa_instance_id=>l_fa_instance_id) ;
340
341
342 WHEN e_MSG_PROCESSING_ERROR THEN
343 ROLLBACK to deliver_msg ;
344
345 fnd_message.set_name ('XNP', 'MSG_PROCESSING_ERROR') ;
346 fnd_message.set_token ('MSG_CODE',
347 l_msg_header.message_code) ;
348 fnd_message.set_token ('ERROR_CODE',
349 TO_CHAR(l_error_code)) ;
350 fnd_message.set_token ('ERROR_MESSAGE',
351 l_error_message) ;
352 l_fnd_message := fnd_message.get ;
353
354 xnp_message.update_status(
355 p_msg_id=>l_msg_header.message_id,
356 p_status=>'FAILED',
357 p_error_desc=>l_fnd_message,
358 p_order_id=>l_order_id,
359 p_wi_instance_id=>l_wi_instance_id,
360 p_fa_instance_id=>l_fa_instance_id) ;
361
362 WHEN OTHERS THEN
363
364 ROLLBACK ;
365
366 l_fatal_error := TRUE;
367 fnd_message.set_name ('XNP', 'MSG_SERVER_EXCEPTION') ;
368 fnd_message.set_token ('MSG_ID',
369 l_msg_header.message_id) ;
370 fnd_message.set_token ('ERROR_CODE', SQLCODE) ;
371 fnd_message.set_token ('ERROR_MESSAGE', SQLERRM) ;
372 l_fnd_message:= FND_MESSAGE.get ;
373
374 xnp_message.update_statuS(
375 p_msg_id=>l_msg_header.message_id,
376 p_status=>'FAILED',
377 p_error_desc=>l_fnd_message,
378 p_order_id=>l_order_id,
379 p_wi_instance_id=>l_wi_instance_id,
380 p_fa_instance_id=>l_fa_instance_id) ;
381 END ;
382
383 COMMIT ;
384
385 END LOOP ;
386
387 -- If no apps have registered, execute the default processing logic
388
389 IF ( l_num_apps = 0 ) THEN
390
391 xnp_message.default_process( l_msg_header,
392 l_msg_text,
393 l_error_code,
394 l_error_message ) ;
395
396 IF ( l_error_code <> 0 ) THEN
397 RAISE e_DEFAULT_PROCESSING_ERROR ;
398 END IF ;
399
400 xnp_message.update_status(
401 p_msg_id=>l_msg_header.message_id,
402 p_status=>'PROCESSED',
403 p_order_id=>l_order_id,
404 p_wi_instance_id=>l_wi_instance_id,
405 p_fa_instance_id=>l_fa_instance_id) ;
406 COMMIT ;
407
408 END IF ;
409
410
411 EXCEPTION
412 WHEN e_MSG_VALIDATION_ERROR THEN
413 ROLLBACK to after_pop;
414 fnd_message.set_name ('XNP',
415 'MSG_VALIDATION_ERROR') ;
416 fnd_message.set_token ('MSG_CODE',
417 l_msg_header.message_code) ;
418 fnd_message.set_token ('ERROR_CODE',
419 TO_CHAR(l_error_code)) ;
420 fnd_message.set_token ('ERROR_MESSAGE',
421 l_error_message) ;
422 l_fnd_message:= FND_MESSAGE.get ;
423
424
425 xnp_message.update_status(
426 p_msg_id=>l_msg_header.message_id,
427 p_status=>'FAILED',
428 p_error_desc=>l_fnd_message,
429 p_order_id=>l_order_id,
430 p_wi_instance_id=>l_wi_instance_id,
431 p_fa_instance_id=>l_fa_instance_id) ;
432
433 WHEN e_DEFAULT_PROCESSING_ERROR THEN
434 ROLLBACK to after_pop;
435 fnd_message.set_name ('XNP',
436 'DFLT_PROCESSING_ERROR') ;
437 fnd_message.set_token ('MSG_CODE',
438 l_msg_header.message_code) ;
439 fnd_message.set_token ('ERROR_CODE',
440 TO_CHAR(l_error_code)) ;
441 fnd_message.set_token ('ERROR_MESSAGE',
442 l_error_message) ;
443 l_fnd_message:= FND_MESSAGE.get ;
444
445 xnp_message.update_status(
446 p_msg_id=>l_msg_header.message_id,
447 p_status=>'FAILED',
448 p_error_desc=>l_fnd_message,
449 p_order_id=>l_order_id,
450 p_wi_instance_id=>l_wi_instance_id,
451 p_fa_instance_id=>l_fa_instance_id) ;
452
453 WHEN OTHERS THEN
454
455 ROLLBACK ;
456 l_fatal_error := TRUE;
457 fnd_message.set_name ('XNP',
458 'MSG_SERVER_EXCEPTION') ;
459 fnd_message.set_token ('MSG_ID',
460 l_msg_header.message_id) ;
461 fnd_message.set_token ('ERROR_CODE', SQLCODE) ;
462 fnd_message.set_token ('ERROR_MESSAGE',
463 SQLERRM) ;
464 l_fnd_message:= FND_MESSAGE.get ;
465
466 xnp_message.update_status(
467 p_msg_id=>l_msg_header.message_id,
468 p_status=>'FAILED',
469 p_error_desc=>l_fnd_message,
470 p_order_id=>l_order_id,
471 p_wi_instance_id=>l_wi_instance_id,
472 p_fa_instance_id=>l_fa_instance_id) ;
473 END;
474 COMMIT;
475
476 if l_fatal_error then
477 l_dummy := instr(p_queue_name,'.');
478 if l_dummy = 0 then
479 l_queue_name := p_queue_name;
480 else
481 l_queue_name := substr(p_queue_name, l_dummy +1, length(p_queue_name) - l_dummy);
482 end if;
483
484 xdp_aq_utilities.handle_dq_exception(
485 p_MESSAGE_ID => 'FF',
486 p_WF_ITEM_TYPE => null,
487 p_WF_ITEM_KEY => null,
488 p_CALLER_NAME => 'XNP_EVENT.PROCESS' ,
489 p_CALLBACK_TEXT => NULL ,
490 p_Q_NAME => l_queue_name,
491 p_ERROR_DESCRIPTION => 'Can not dequeue: ' || SQLERRM);
492
493 raise_application_error(-20530,'Can not dequeue: ' || SQLERRM);
494 end if;
495
496 END process;
497
498 /***************************************************************************
499 ***** Procedure: PROCESS()
500 ***** Purpose: Checks if a message has arrived and delivers it to all
501 ***** registered applications. Returns a timeout error if
502 ***** no message is received in the specified interval.
503 ****************************************************************************/
504
505 PROCEDURE process(
506 p_queue_name IN VARCHAR2,
507 p_correlation_id IN VARCHAR2,
508 x_queue_timed_out OUT NOCOPY VARCHAR2 )
509 IS
510
511 l_error_code NUMBER ;
512 l_error_message VARCHAR2(4000) ;
513
514 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
515
516 l_msg_text VARCHAR2(32767) ;
517 l_num_apps NUMBER := 0;
518 l_operation VARCHAR2(4000) := NULL ;
519 l_description VARCHAR2(4000) := NULL ;
520 l_fnd_message VARCHAR2(4000) := NULL ;
521
522 l_order_id NUMBER;
523 l_wi_instance_id NUMBER;
524 l_fa_instance_id NUMBER ;
525
526 CURSOR registered_apps ( l_reference_id IN VARCHAR2,
527 l_msg_code IN VARCHAR2 )
528 IS
529 SELECT callback_event_id,
530 callback_proc_name,
531 process_reference,
532 close_reqd_flag,
533 order_id,
534 wi_instance_id,
535 fa_instance_id
536 FROM XNP_CALLBACK_EVENTS
537 WHERE reference_id in (l_reference_id, '-1')
538 -- WHERE ((reference_id = l_reference_id) OR (reference_id IS NULL))
539 AND msg_code = l_msg_code
540 AND status = 'WAITING' ;
541
542 e_MSG_VALIDATION_ERROR EXCEPTION ;
543 e_MSG_PROCESSING_ERROR EXCEPTION ;
544 e_MSG_DELIVERY_ERROR EXCEPTION ;
545 e_DEFAULT_PROCESSING_ERROR EXCEPTION ;
546
547 BEGIN
548 BEGIN
549
550 l_error_code := 0 ;
551 l_error_message := NULL ;
552
553 xnp_message.pop (p_queue_name => p_queue_name,
554 x_msg_header => l_msg_header,
555 x_body_text => l_msg_text,
556 x_error_code => l_error_code,
557 x_error_message => l_error_message,
558 p_correlation_id => process.p_correlation_id ) ;
559
560 SAVEPOINT after_pop ;
561
562 IF ( l_error_code = XNP_ERRORS.G_DEQUEUE_TIMEOUT ) THEN
563 -- DBMS_LOCK.sleep(5) ;
564 x_queue_timed_out := 'Y';
565 RETURN ;
566 END IF ;
567
568 xnp_message.validate ( l_msg_header,
569 l_msg_text,
570 l_error_code,
571 l_error_message ) ;
572
573 IF (l_error_code <> 0) THEN
574 RAISE e_MSG_VALIDATION_ERROR ;
575 END IF ;
576
577 -- Process and deliver message to all registered applications
578
579 FOR app IN registered_apps ( l_msg_header.reference_id,
580 l_msg_header.message_code ) LOOP
581
582 l_order_id := app.order_id;
583 l_wi_instance_id := app.wi_instance_id;
584 l_fa_instance_id := app.fa_instance_id ;
585 l_msg_header.order_id := app.order_id;
586 l_msg_header.wi_instance_id := app.wi_instance_id;
587 l_msg_header.fa_instance_id := app.fa_instance_id ;
588
589 l_num_apps := l_num_apps + 1 ;
590
591 SAVEPOINT deliver_msg ;
592
593 BEGIN
594
595 xnp_message.process ( l_msg_header,
596 l_msg_text,
597 app.process_reference,
598 l_error_code,
599 l_error_message ) ;
600
601 IF (l_error_code <> 0) THEN
602 RAISE e_MSG_PROCESSING_ERROR ;
603 END IF ;
604
605 xnp_event.unsubscribe ( app.callback_event_id,
606 app.process_reference, app.close_reqd_flag ) ;
607
608 deliver ( l_msg_header.message_id,
609 app.callback_proc_name,
610 app.process_reference,
611 l_error_code,
612 l_error_message ) ;
613
614 IF (l_error_code <> 0) THEN
615 RAISE e_MSG_DELIVERY_ERROR ;
616 END IF ;
617
618
619 xnp_message.update_status(
620 p_msg_id=>l_msg_header.message_id,
621 p_status=>'PROCESSED',
622 p_order_id=>l_order_id,
623 p_wi_instance_id=>l_wi_instance_id,
624 p_fa_instance_id=>l_fa_instance_id) ;
625
626 EXCEPTION
627 WHEN e_MSG_DELIVERY_ERROR THEN
628 ROLLBACK to deliver_msg ;
629
630 fnd_message.set_name ('XNP', 'MSG_DELIVERY_ERROR') ;
631 fnd_message.set_token ('CALLBACK',
632 app.callback_proc_name);
633 fnd_message.set_token ('ERROR_CODE',
634 TO_CHAR(l_error_code)) ;
635 fnd_message.set_token ('ERROR_MESSAGE',
636 l_error_message) ;
637
638 l_fnd_message:= fnd_message.get ;
639
640 xnp_message.update_status(
641 p_msg_id=>l_msg_header.message_id,
642 p_status=>'FAILED',
643 p_error_desc=>l_fnd_message,
644 p_order_id=>l_order_id,
645 p_wi_instance_id=>l_wi_instance_id,
646 p_fa_instance_id=>l_fa_instance_id) ;
647
648
649 WHEN e_MSG_PROCESSING_ERROR THEN
650 ROLLBACK to deliver_msg ;
651
652 fnd_message.set_name ('XNP', 'MSG_PROCESSING_ERROR') ;
653 fnd_message.set_token ('MSG_CODE',
654 l_msg_header.message_code) ;
655 fnd_message.set_token ('ERROR_CODE',
656 TO_CHAR(l_error_code)) ;
657 fnd_message.set_token ('ERROR_MESSAGE',
658 l_error_message) ;
659 l_fnd_message := fnd_message.get ;
660
661 xnp_message.update_status(
662 p_msg_id=>l_msg_header.message_id,
663 p_status=>'FAILED',
664 p_error_desc=>l_fnd_message,
665 p_order_id=>l_order_id,
666 p_wi_instance_id=>l_wi_instance_id,
667 p_fa_instance_id=>l_fa_instance_id) ;
668
669 WHEN OTHERS THEN
670
671 ROLLBACK ;
672 fnd_message.set_name ('XNP', 'MSG_SERVER_EXCEPTION') ;
673 fnd_message.set_token ('MSG_ID',
674 l_msg_header.message_id) ;
675 fnd_message.set_token ('ERROR_CODE', SQLCODE) ;
676 fnd_message.set_token ('ERROR_MESSAGE', SQLERRM) ;
677 l_fnd_message:= FND_MESSAGE.get ;
678
679 xnp_message.update_statuS(
680 p_msg_id=>l_msg_header.message_id,
681 p_status=>'FAILED',
682 p_error_desc=>l_fnd_message,
683 p_order_id=>l_order_id,
684 p_wi_instance_id=>l_wi_instance_id,
685 p_fa_instance_id=>l_fa_instance_id) ;
686 END ;
687
688 COMMIT ;
689
690 END LOOP ;
691
692 -- If no apps have registered, execute the default processing logic
693
694 IF ( l_num_apps = 0 ) THEN
695
696 xnp_message.default_process( l_msg_header,
697 l_msg_text,
698 l_error_code,
699 l_error_message ) ;
700
701 IF ( l_error_code <> 0 ) THEN
702 RAISE e_DEFAULT_PROCESSING_ERROR ;
703 END IF ;
704
705 xnp_message.update_status(
706 p_msg_id=>l_msg_header.message_id,
707 p_status=>'PROCESSED',
708 p_order_id=>l_order_id,
709 p_wi_instance_id=>l_wi_instance_id,
710 p_fa_instance_id=>l_fa_instance_id) ;
711 COMMIT ;
712
713 END IF ;
714
715
716 EXCEPTION
717 WHEN e_MSG_VALIDATION_ERROR THEN
718 ROLLBACK to after_pop;
719 fnd_message.set_name ('XNP',
720 'MSG_VALIDATION_ERROR') ;
721 fnd_message.set_token ('MSG_CODE',
722 l_msg_header.message_code) ;
723 fnd_message.set_token ('ERROR_CODE',
724 TO_CHAR(l_error_code)) ;
725 fnd_message.set_token ('ERROR_MESSAGE',
726 l_error_message) ;
727 l_fnd_message:= FND_MESSAGE.get ;
728
729
730 xnp_message.update_status(
731 p_msg_id=>l_msg_header.message_id,
732 p_status=>'FAILED',
733 p_error_desc=>l_fnd_message,
734 p_order_id=>l_order_id,
735 p_wi_instance_id=>l_wi_instance_id,
736 p_fa_instance_id=>l_fa_instance_id) ;
737
738 WHEN e_DEFAULT_PROCESSING_ERROR THEN
739 ROLLBACK to after_pop;
740 fnd_message.set_name ('XNP',
741 'DFLT_PROCESSING_ERROR') ;
742 fnd_message.set_token ('MSG_CODE',
743 l_msg_header.message_code) ;
744 fnd_message.set_token ('ERROR_CODE',
745 TO_CHAR(l_error_code)) ;
746 fnd_message.set_token ('ERROR_MESSAGE',
747 l_error_message) ;
748 l_fnd_message:= FND_MESSAGE.get ;
749
750 xnp_message.update_status(
751 p_msg_id=>l_msg_header.message_id,
752 p_status=>'FAILED',
753 p_error_desc=>l_fnd_message,
754 p_order_id=>l_order_id,
755 p_wi_instance_id=>l_wi_instance_id,
756 p_fa_instance_id=>l_fa_instance_id) ;
757
758 WHEN OTHERS THEN
759
760 ROLLBACK ;
761 fnd_message.set_name ('XNP',
762 'MSG_SERVER_EXCEPTION') ;
763 fnd_message.set_token ('MSG_ID',
764 l_msg_header.message_id) ;
765 fnd_message.set_token ('ERROR_CODE', SQLCODE) ;
766 fnd_message.set_token ('ERROR_MESSAGE',
767 SQLERRM) ;
768 l_fnd_message:= FND_MESSAGE.get ;
769
770 xnp_message.update_status(
771 p_msg_id=>l_msg_header.message_id,
772 p_status=>'FAILED',
773 p_error_desc=>l_fnd_message,
774 p_order_id=>l_order_id,
775 p_wi_instance_id=>l_wi_instance_id,
776 p_fa_instance_id=>l_fa_instance_id) ;
777 END;
778 COMMIT;
779
780 END process;
781 /***************************************************************************
782 ***** Procedure: DELIVER()
783 ***** Purpose: Delivers the message to the callback procedure.
784 ****************************************************************************/
785
786 PROCEDURE deliver(
787 p_msg_id IN NUMBER
788 ,p_callback_proc VARCHAR2
789 ,p_process_reference VARCHAR2
790 ,x_error_code OUT NOCOPY NUMBER
791 ,x_error_message OUT NOCOPY VARCHAR2
792 )
793 IS
794
795 l_cursor NUMBER ;
796 l_sql VARCHAR2(4000) ;
797 l_num_rows NUMBER ;
798 l_error_code NUMBER ;
799 l_error_message VARCHAR2(4000) ;
800
801 BEGIN
802
803 l_sql := 'BEGIN' || g_new_line
804 || p_callback_proc || '( :message_id, :process_reference, '
805 || ':error_code, :error_message ) ;' || g_new_line
806 || 'END ;' ;
807
808 EXECUTE IMMEDIATE l_sql USING
809 p_msg_id
810 ,p_process_reference
811 ,OUT l_error_code
812 ,OUT l_error_message ;
813
814 x_error_code := l_error_code ;
815 x_error_message := l_error_message ;
816
817 END deliver;
818
819 /***************************************************************************
820 ***** Procedure: SUBSCRIBE_FOR_ACKS()
821 ***** Purpose: SUBSCRIBEs a callback for all the expected ACK messages
822 ***** from the remote system. Callbacks can be registered
823 ***** for any of the immediate responses from the peer
824 ***** system. The response will be delivered to the
825 ***** registered application on receiving it.
826 ****************************************************************************/
827
828 PROCEDURE subscribe_for_acks(
829 p_message_type IN VARCHAR2
830 ,p_reference_id IN VARCHAR2
831 ,p_process_reference IN VARCHAR2
832 ,x_error_code OUT NOCOPY NUMBER
833 ,x_error_message OUT NOCOPY VARCHAR2
834 ,p_order_id IN NUMBER DEFAULT NULL
835 ,p_wi_instance_id IN NUMBER DEFAULT NULL
836 ,p_fa_instance_id IN NUMBER DEFAULT NULL
837 )
838 IS
839 l_ack_code XNP_MSG_TYPES_B.MSG_CODE%TYPE ;
840
841 CURSOR c_Acks IS
842 SELECT ack_msg_code FROM XNP_MSG_ACKS
843 WHERE source_msg_code = p_message_type;
844
845 BEGIN
846
847 x_error_code := 0;
848 x_error_message := NULL ;
849
850 FOR cur_rec IN c_Acks LOOP
851
852 xnp_event.subscribe(
853 p_msg_code=>cur_rec.ack_msg_code
854 ,p_reference_id=>p_reference_id
855 ,p_process_reference=>p_process_reference
856 ,p_procedure_name=>'XNP_EVENT.RESUME_WORKFLOW'
857 ,p_callback_type=>'PL/SQL'
858 ,p_close_reqd_flag => 'Y'
859 ,p_order_id=>p_order_id
860 ,p_wi_instance_id=>p_wi_instance_id
861 ,p_fa_instance_id=>p_fa_instance_id
862 );
863
864 END LOOP;
865
866 EXCEPTION
867 WHEN OTHERS THEN
868 x_error_code := SQLCODE;
869 x_error_message := SQLERRM;
870 END;
871
872 /***************************************************************************
873 ***** Procedure: RESUME_WORKFLOW()
874 ***** Purpose: Resumes the workflow instance.
875 ****************************************************************************/
876
877 PROCEDURE resume_workflow (
878 p_message_id IN NUMBER
879 ,p_process_reference IN VARCHAR2
880 ,x_error_code OUT NOCOPY NUMBER
881 ,x_error_message OUT NOCOPY VARCHAR2
882 )
883 IS
884
885 l_wf_activity VARCHAR2(256) ;
886 l_wf_type VARCHAR2(256) ;
887 l_wf_key VARCHAR2(256) ;
888 l_fnd_message VARCHAR2(4000) ;
889
890 -- Should change to CLOB later
891
892 l_msg_text VARCHAR2(32767) ;
893
894 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
895 l_sdp_result_code VARCHAR2(40) := NULL;
896 e_workflow_parameters_invalid EXCEPTION ;
897
898 BEGIN
899
900 x_error_code := 0 ;
901 x_error_message := NULL ;
902
903 -- Derive the workflow itemtype, itemkey and activity
904
905 xnp_utils.get_wf_instance( p_process_reference, l_wf_type,
906 l_wf_key, l_wf_activity ) ;
907
908 IF( (l_wf_key IS NULL) OR (l_wf_type IS NULL) OR
909 (l_wf_activity IS NULL) )
910 THEN
911 raise e_workflow_parameters_invalid;
912 END IF;
913
914 -- Set the MESSAGE_BODY item attribute
915
916 -- adabholk 03/2001
917 -- performance fix
918 -- new get() replaces two get calls
919
920 xnp_message.get(
921 p_msg_id => p_message_id
922 ,x_msg_header => l_msg_header
923 ,x_msg_text => l_msg_text);
924
925 /*
926 -- xnp_message.get_header(p_message_id, l_msg_header) ;
927 --
928 -- xnp_message.get (p_message_id, l_msg_text) ;
929 --
930 */
931
932 /* Resume the workflow */
933
934 xnp_xml_utils.decode (l_msg_text,
935 'SDP_RESULT_CODE',
936 l_sdp_result_code) ;
937
938 IF (l_sdp_result_code IS NULL) THEN
939
940 wf_engine.completeactivity(
941 l_wf_type
942 ,l_wf_key
943 ,l_wf_activity
944 ,l_msg_header.message_code
945 ) ;
946 ELSE
947 wf_engine.completeactivity(
948 l_wf_type
949 ,l_wf_key
950 ,l_wf_activity
951 ,l_sdp_result_code
952 ) ;
953 END IF ;
954
955 EXCEPTION
956 WHEN e_workflow_parameters_invalid THEN
957 fnd_message.set_name ('XNP', 'INVALID_WF_SPECIFIED') ;
958 fnd_message.set_token ('MSG_ID', l_msg_header.message_id) ;
959 fnd_message.set_token ('P_REFERENCE', p_process_reference) ;
960 l_fnd_message := FND_MESSAGE.get ;
961
962 xnp_message.update_status(p_message_id,
963 'FAILED',
964 l_fnd_message) ;
965
966 xnp_message.update_status(p_msg_id=>l_msg_header.message_id,
967 p_status=>'FAILED',
968 p_error_desc=>l_fnd_message) ;
969
970 x_error_code := xnp_errors.g_invalid_workflow ;
971 x_error_message := l_fnd_message ;
972
973 WHEN OTHERS THEN
974 x_error_code := SQLCODE ;
975 x_error_message := SQLERRM ;
976
977 END resume_workflow ;
978
979 PROCEDURE sync_n_resume_wf (
980 p_message_id IN NUMBER
981 ,p_process_reference IN VARCHAR2
982 ,x_error_code OUT NOCOPY NUMBER
983 ,x_error_message OUT NOCOPY VARCHAR2
984 )
985 IS
986 l_wf_activity VARCHAR2(256) ;
987 l_wf_type VARCHAR2(256) ;
988 l_wf_key VARCHAR2(256) ;
989 l_actid NUMBER;
990 l_fa_instance_id NUMBER;
991
992 CURSOR c_get_fa_instance_id IS
993 SELECT fa_instance_id
994 FROM xnp_msgs
995 WHERE msg_id = p_message_id;
996
997 BEGIN
998
999 -- Derive the workflow itemtype, itemkey and activity
1000 xnp_utils.get_wf_instance( p_process_reference, l_wf_type,
1001 l_wf_key, l_wf_activity ) ;
1002
1003 --get the activity id..
1004 l_actid := wf_process_activity.ActiveInstanceId(l_wf_type, l_wf_key,
1005 l_wf_activity, wf_engine.eng_notified);
1006
1007 FOR lv_rec IN c_get_fa_instance_id LOOP
1008 l_fa_instance_id := lv_rec.fa_instance_id;
1009
1010 --STEP3 : upload FA parameters..
1011 xnp_wf_standard.uploadFAParams( l_wf_type, l_wf_key, l_actid, l_fa_instance_id);
1012
1013 --STEP3 : download WI parameters..
1014 xnp_wf_standard.downloadWIParams( l_wf_type, l_wf_key );
1015
1016 END LOOP;
1017 --Resume the workflow after synching the parameters..
1018 resume_workflow ( p_message_id, p_process_reference, x_error_code, x_error_message );
1019
1020 END sync_n_resume_wf;
1021
1022
1023 /***************************************************************************
1024 ***** Procedure: RESTART_ACTIVITY()
1025 ***** Purpose: Restarts the specified workflow activity.
1026 ****************************************************************************/
1027
1028 PROCEDURE restart_activity (
1029 p_message_id IN NUMBER
1030 ,p_process_reference IN VARCHAR2
1031 ,x_error_code OUT NOCOPY NUMBER
1032 ,x_error_message OUT NOCOPY VARCHAR2
1033 )
1034 IS
1035
1036 l_wf_activity VARCHAR2(256) ;
1037 l_wf_type VARCHAR2(256) ;
1038 l_wf_key VARCHAR2(256) ;
1039 l_fnd_message VARCHAR2(4000) := NULL;
1040 l_msg_text VARCHAR2(32767) ;
1041 l_msg_header xnp_message.msg_header_rec_type ;
1042 l_sdp_result_code VARCHAR2(3) := NULL;
1043
1044 l_result VARCHAR2(200) := NULL;
1045
1046 e_workflow_parameters_invalid EXCEPTION ;
1047
1048
1049 BEGIN
1050
1051 x_error_code := 0 ;
1052 x_error_message := NULL ;
1053 l_fnd_message := NULL ;
1054
1055 xnp_utils.get_wf_instance( p_process_reference, l_wf_type,
1056 l_wf_key, l_wf_activity ) ;
1057
1058 IF( (l_wf_key IS NULL) OR (l_wf_type IS NULL) OR
1059 (l_wf_activity IS NULL) )
1060 THEN
1061 raise e_workflow_parameters_invalid;
1062 END IF;
1063
1064 wf_engine.setitemattrnumber(
1065 l_wf_type,
1066 l_wf_key,
1067 'MSG_ID',
1068 l_msg_header.message_id ) ;
1069
1070 wf_engine.handleerror (
1071 l_wf_type
1072 ,l_wf_key
1073 ,l_wf_activity
1074 ,'RETRY'
1075 ,l_result
1076 ) ;
1077
1078 EXCEPTION
1079 WHEN e_workflow_parameters_invalid THEN
1080
1081 fnd_message.set_name ('XNP', 'INVALID_WF_SPECIFIED') ;
1082 fnd_message.set_token ('MSG_ID',
1083 l_msg_header.message_id) ;
1084 fnd_message.set_token ('P_REFERENCE',
1085 p_process_reference) ;
1086 l_fnd_message := FND_MESSAGE.get ;
1087
1088 xnp_message.update_status(
1089 p_msg_id=>l_msg_header.message_id,
1090 p_status=>'FAILED',
1091 p_error_desc=>l_fnd_message);
1092
1093 x_error_code := XNP_ERRORS.G_INVALID_WORKFLOW ;
1094 x_error_message := l_fnd_message ;
1095
1096 WHEN OTHERS THEN
1097 x_error_code := SQLCODE ;
1098 x_error_message := SQLERRM ;
1099
1100 END restart_activity ;
1101
1102
1103
1104 /***************************************************************************
1105 ***** Procedure: PROCESS_IN_MSG()
1106 ***** Purpose: Wrapper procedure for Inbound message dequer.
1107 ****************************************************************************/
1108 PROCEDURE process_in_msg
1109 IS
1110 l_inmsg_q_state VARCHAR2(1024) ;
1111 BEGIN
1112
1113 LOOP
1114 l_inmsg_q_state := xdp_aq_utilities.get_queue_state(
1115 cc_inbound_msg_q) ;
1116
1117 IF ((l_inmsg_q_state = 'SHUTDOWN')OR
1118 (l_inmsg_q_state = 'DISABLED')) THEN
1119 EXIT ;
1120 END IF ;
1121
1122 process (c_inbound_msg_q) ;
1123
1124 END LOOP;
1125
1126 END process_in_msg ;
1127
1128 /***************************************************************************
1129 ***** Procedure: PROCESS_IN_MSG()
1130 ***** Purpose: Wrapper procedure for Inbound message dequer.
1131 ****************************************************************************/
1132 PROCEDURE process_in_msg (p_message_wait_timeout IN NUMBER DEFAULT 1,
1133 p_correlation_id IN VARCHAR2,
1134 x_message_key OUT NOCOPY VARCHAR2,
1135 x_queue_timed_out OUT NOCOPY VARCHAR2 )
1136
1137 IS
1138 BEGIN
1139
1140 process (c_inbound_msg_q, p_correlation_id, x_queue_timed_out) ;
1141
1142 END process_in_msg ;
1143
1144
1145 /***************************************************************************
1146 ***** Procedure: PROCESS_IN_EVT()
1147 ***** Purpose: Wrapper procedure for internal event dequer.
1148 ****************************************************************************/
1149 PROCEDURE process_in_evt
1150 IS
1151 l_inevt_q_state VARCHAR2(1024) ;
1152 BEGIN
1153
1154 LOOP
1155 l_inevt_q_state := xdp_aq_utilities.get_queue_state(
1156 cc_internal_evt_q) ;
1157
1158 IF ((l_inevt_q_state = 'SHUTDOWN')OR
1159 (l_inevt_q_state = 'DISABLED')) THEN
1160 EXIT ;
1161 END IF ;
1162
1163 process (c_internal_evt_q) ;
1164
1165 END LOOP;
1166
1167 END process_in_evt ;
1168
1169 /***************************************************************************
1170 ***** Procedure: PROCESS_IN_EVT()
1171 ***** Purpose: Wrapper procedure for internal event dequer.
1172 ****************************************************************************/
1173 PROCEDURE process_in_evt (p_message_wait_timeout IN NUMBER DEFAULT 1,
1174 p_correlation_id IN VARCHAR2,
1175 x_message_key OUT NOCOPY VARCHAR2,
1176 x_queue_timed_out OUT NOCOPY VARCHAR2 )
1177 IS
1178 BEGIN
1179
1180 process (c_internal_evt_q, p_correlation_id, x_queue_timed_out) ;
1181
1182 END process_in_evt ;
1183
1184
1185 --Package initialization code
1186
1187 BEGIN
1188
1189 DECLARE
1190 l_ret BOOLEAN;
1191 l_status VARCHAR2(80);
1192 l_industry VARCHAR2(80);
1193 l_schema VARCHAR2(80) ;
1194 BEGIN
1195
1196 l_ret := FND_INSTALLATION.GET_APP_INFO(
1197 application_short_name=>'XNP'
1198 ,status=>l_status
1199 ,industry=>l_industry
1200 ,oracle_schema=>l_schema
1201 );
1202
1203 IF (l_schema IS NULL) THEN
1204 l_schema := 'XNP' ;
1205 END IF;
1206
1207 c_inbound_msg_q := l_schema || '.' || cc_inbound_msg_q ;
1208 c_outbound_msg_q := l_schema || '.' || cc_outbound_msg_q ;
1209 c_internal_evt_q := l_schema || '.' || cc_internal_evt_q ;
1210 c_timer_q := l_schema || '.' || cc_timer_q ;
1211
1212 END ;
1213
1214 END xnp_event;