[Home] [Help]
PACKAGE BODY: APPS.XNP_MESSAGE
Source
1 PACKAGE BODY xnp_message AS
2 /* $Header: XNPMSGPB.pls 120.2 2006/02/24 17:57:16 sacsharm noship $ */
3
4 e_procedure_exec_error EXCEPTION ;
5 -- g_schema_set NUMBER := 0;
6 -- g_schema VARCHAR2(1024) ;
7 g_fnd_schema VARCHAR2(1024) ;
8 g_xnp_schema VARCHAR2(1024) ;
9 g_logdir VARCHAR2(100);
10 g_logdate DATE;
11 g_APPS_MAINTENANCE_MODE VARCHAR2(10);
12
13 g_new_line CONSTANT VARCHAR2(10) := convert(fnd_global.local_chr(10),
14 substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
15 'WE8ISO8859P1') ;
16
17 PROCEDURE decode_xnp_msgs (
18 p_msg_header IN OUT NOCOPY XNP_MESSAGE.MSG_HEADER_REC_TYPE,
19 p_body_text IN VARCHAR2
20 ) ;
21
22 PROCEDURE check_run_time_data(
23 p_msg_code IN VARCHAR2
24 ,x_error_code OUT NOCOPY NUMBER
25 ,x_error_message OUT NOCOPY VARCHAR2
26 );
27
28 PROCEDURE drop_packages(
29 p_msg_code IN VARCHAR2
30 ,x_error_code OUT NOCOPY NUMBER
31 ,x_error_message OUT NOCOPY VARCHAR2
32 );
33
34
35 /***************************************************************************
36 ***** Procedure: GET_HEADER()
37 ***** Purpose: Retrieves the header of a message.
38 ****************************************************************************/
39
40 PROCEDURE get_header(
41 P_MSG_ID IN NUMBER
42 ,X_MSG_HEADER OUT NOCOPY MSG_HEADER_REC_TYPE
43 )
44 IS
45
46
47 CURSOR get_message ( message_id IN XNP_MSGS.MSG_ID%TYPE ) IS
48 SELECT msg_id,
49 msg_code,
50 reference_id,
51 opp_reference_id,
52 msg_creation_date,
53 sender_name,
54 recipient_name,
55 msg_version,
56 order_id,
57 wi_instance_id,
58 fa_instance_id
59 FROM xnp_msgs
60 WHERE msg_id = message_id ;
61
62 BEGIN
63
64 OPEN get_message ( p_msg_id ) ;
65
66 FETCH get_message INTO
67 x_msg_header.message_id,
68 x_msg_header.message_code,
69 x_msg_header.reference_id,
70 x_msg_header.opp_reference_id,
71 x_msg_header.creation_date,
72 x_msg_header.sender_name,
73 x_msg_header.recipient_name,
74 x_msg_header.version,
75 x_msg_header.order_id,
76 x_msg_header.wi_instance_id,
77 x_msg_header.fa_instance_id ;
78
79 CLOSE get_message ;
80
81 END get_header;
82
83 /***************************************************************************
84 ***** Procedure: PROCESS()
85 ***** Purpose: Executes the processing logic for the message .
86 ****************************************************************************/
87
88 PROCEDURE process(
89 p_msg_header IN MSG_HEADER_REC_TYPE
90 ,P_MSG_TEXT IN VARCHAR2
91 ,P_PROCESS_REFERENCE IN VARCHAR2
92 ,X_ERROR_CODE OUT NOCOPY NUMBER
93 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
94 )
95 IS
96
97 l_cursor NUMBER ;
98 l_sql VARCHAR2(16000) ;
99 l_num_rows NUMBER ;
100
101 BEGIN
102
103 x_error_code := 0 ;
104 x_error_message := NULL ;
105
106 l_sql:= 'BEGIN
107 DECLARE
108 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
109 BEGIN
110 l_msg_header.message_id := :message_id ;
111 l_msg_header.message_code := :message_code ;
112 l_msg_header.reference_id := :reference_id ;
113 l_msg_header.opp_reference_id := :opp_ref_id ;
114 l_msg_header.creation_date := :creation_date;
115 l_msg_header.sender_name := :sender_name ;
116 l_msg_header.recipient_name := :recipient_name ;
117 l_msg_header.version := :version;
118 l_msg_header.direction_indr := :direction_indr ;
119 l_msg_header.order_id := :order_id ;
120 l_msg_header.wi_instance_id := :wi_instance_id ;
121 l_msg_header.fa_instance_id := :fa_instance_id ; '
122 || g_new_line ;
123
124 l_sql := l_sql || g_new_line || ' '
125 || g_pkg_prefix || p_msg_header.message_code
126 || g_pkg_suffix || '.' || 'PROCESS(' || g_new_line
127 || ' l_msg_header, ' || g_new_line
128 || ' :l_msg_body, ' || g_new_line
129 || ' :error_code, ' || g_new_line
130 || ' :error_message, ' || g_new_line
131 || ' :l_process_ref ) ; ' || g_new_line
132 || ' END ;' || g_new_line
133 || 'END ;' ;
134
135
136 EXECUTE IMMEDIATE l_sql USING
137 IN p_msg_header.message_id
138 ,IN p_msg_header.message_code
139 ,IN p_msg_header.reference_id
140 ,IN p_msg_header.opp_reference_id
141 ,IN p_msg_header.creation_date
142 ,IN p_msg_header.sender_name
143 ,IN p_msg_header.recipient_name
144 ,IN p_msg_header.version
145 ,IN p_msg_header.direction_indr
146 ,IN p_msg_header.order_id
147 ,IN p_msg_header.wi_instance_id
148 ,IN p_msg_header.fa_instance_id
149 ,IN p_msg_text
150 ,OUT x_error_code
151 ,OUT x_error_message
152 ,IN p_process_reference ;
153
154 END process;
155
156
157 /***************************************************************************
158 ***** Procedure: DEFAULT_PROCESS()
159 ***** Purpose: Executes the processing logic for the message .
160 ****************************************************************************/
161
162 PROCEDURE default_process(
163 p_msg_header IN MSG_HEADER_REC_TYPE
164 ,p_msg_text IN VARCHAR2
165 ,x_error_code OUT NOCOPY NUMBER
166 ,x_error_message OUT NOCOPY VARCHAR2
167
168 )
169 IS
170
171 l_cursor NUMBER ;
172 l_sql VARCHAR2(16000) ;
173 l_num_rows NUMBER ;
174
175 BEGIN
176
177
178 l_sql:= 'BEGIN
179 DECLARE
180 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
181 BEGIN
182 l_msg_header.message_id := :message_id ;
183 l_msg_header.message_code := :message_code ;
184 l_msg_header.reference_id := :reference_id ;
185 l_msg_header.opp_reference_id := :opp_ref_id ;
186 l_msg_header.creation_date := :creation_date;
187 l_msg_header.sender_name := :sender_name ;
188 l_msg_header.recipient_name := :recipient_name ;
189 l_msg_header.version := :version;
190 l_msg_header.direction_indr := :direction_indr ;
191 l_msg_header.order_id := :order_id ;
192 l_msg_header.wi_instance_id := :wi_instance_id ;
193 l_msg_header.fa_instance_id := :fa_instance_id ; ' || g_new_line ;
194
195 l_sql := l_sql || g_new_line || ' '
196 || g_pkg_prefix || p_msg_header.message_code
197 || g_pkg_suffix || '.' || 'DEFAULT_PROCESS(' || g_new_line
198 || ' l_msg_header, ' || g_new_line
199 || ' :l_msg_body, ' || g_new_line
200 || ' :error_code, ' || g_new_line
201 || ' :error_message ) ; ' || g_new_line
202 || ' END ;' || g_new_line
203 || 'END ;' ;
204
205
206 EXECUTE IMMEDIATE l_sql USING
207 IN p_msg_header.message_id
208 ,IN p_msg_header.message_code
209 ,IN p_msg_header.reference_id
210 ,IN p_msg_header.opp_reference_id
211 ,IN p_msg_header.creation_date
212 ,IN p_msg_header.sender_name
213 ,IN p_msg_header.recipient_name
214 ,IN p_msg_header.version
215 ,IN p_msg_header.direction_indr
216 ,IN p_msg_header.order_id
217 ,IN p_msg_header.wi_instance_id
218 ,IN p_msg_header.fa_instance_id
219 ,IN p_msg_text
220 ,OUT x_error_code
221 ,OUT x_error_message ;
222
223 END default_process;
224
225 /***************************************************************************
226 ***** Procedure: VALIDATE()
227 ***** Purpose: Validates a message using the customer's validation logic.
228 ****************************************************************************/
229
230 PROCEDURE validate(
231 p_msg_header IN OUT NOCOPY msg_header_rec_type
232 ,p_msg_text IN VARCHAR2
233 ,x_error_code OUT NOCOPY NUMBER
234 ,x_error_message OUT NOCOPY VARCHAR2
235 )
236 IS
237
238 l_cursor NUMBER ;
239 l_sql VARCHAR2(16000) ;
240 l_num_rows NUMBER ;
241
242 BEGIN
243
244
245 l_sql:= 'BEGIN
246 DECLARE
247 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
248 BEGIN
249 l_msg_header.message_id := :message_id ;
250 l_msg_header.message_code := :message_code ;
251 l_msg_header.reference_id := :reference_id ;
252 l_msg_header.opp_reference_id := :opp_ref_id ;
253 l_msg_header.creation_date := :creation_date;
254 l_msg_header.sender_name := :sender_name ;
255 l_msg_header.recipient_name := :recipient_name ;
256 l_msg_header.version := :version;
257 l_msg_header.direction_indr := :direction_indr ;
258 l_msg_header.order_id := :order_id ;
259 l_msg_header.wi_instance_id := :wi_instance_id ;
260 l_msg_header.fa_instance_id := :fa_instance_id ; '
261 || g_new_line ;
262
263 l_sql := l_sql || g_new_line || ' '
264 || g_pkg_prefix || p_msg_header.message_code
265 || g_pkg_suffix || '.' || 'VALIDATE(' || g_new_line
266 || ' l_msg_header, ' || g_new_line
267 || ' :l_msg_body, ' || g_new_line
268 || ' :error_code, ' || g_new_line
269 || ' :error_message ) ; ' || g_new_line
270 ||
271 '
272 :message_id := l_msg_header.message_id;
273 :message_code := l_msg_header.message_code;
274 :reference_id := l_msg_header.reference_id;
275 :opp_ref_id := l_msg_header.opp_reference_id;
276 :creation_date := l_msg_header.creation_date;
277 :sender_name := l_msg_header.sender_name;
278 :recipient_name := l_msg_header.recipient_name;
279 :version := l_msg_header.version;
280 :direction_indr := l_msg_header.direction_indr;
281 :order_id := l_msg_header.order_id;
282 :wi_instance_id := l_msg_header.wi_instance_id;
283 :fa_instance_id := l_msg_header.fa_instance_id;
284 '
285 || ' END ;' || g_new_line
286 || 'END ;' ;
287
288
289 EXECUTE IMMEDIATE l_sql USING
290 IN OUT p_msg_header.message_id
291 ,IN OUT p_msg_header.message_code
292 ,IN OUT p_msg_header.reference_id
293 ,IN OUT p_msg_header.opp_reference_id
294 ,IN OUT p_msg_header.creation_date
295 ,IN OUT p_msg_header.sender_name
296 ,IN OUT p_msg_header.recipient_name
297 ,IN OUT p_msg_header.version
298 ,IN OUT p_msg_header.direction_indr
299 ,IN OUT p_msg_header.order_id
300 ,IN OUT p_msg_header.wi_instance_id
301 ,IN OUT p_msg_header.fa_instance_id
302 ,IN p_msg_text
303 ,OUT x_error_code
304 ,OUT x_error_message ;
305
306 END validate;
307
308 /***************************************************************************
309 ***** Procedure: GET_SEQUENCE()
310 ***** Purpose: Gets the next sequence ID for the message .
311 ****************************************************************************/
312
313 PROCEDURE get_sequence(
314 x_msg_id OUT NOCOPY NUMBER
315 )
316 IS
317 BEGIN
318
319 SELECT ( XNP_MSGS_S.nextval ) INTO x_msg_id FROM DUAL ;
320
321 END get_sequence;
322
323 /***************************************************************************
324 ***** Procedure: GET()
325 ***** Purpose: Overloaded to retrieve the header and the message
326 ***** adabholk 03/2001
327 ***** performance fix
328 ****************************************************************************/
329
330 PROCEDURE get(
331 P_MSG_ID IN NUMBER
332 ,X_MSG_HEADER OUT NOCOPY MSG_HEADER_REC_TYPE
333 ,X_MSG_TEXT OUT NOCOPY VARCHAR2
334 )
335 IS
336
337 l_lob_loc CLOB;
338 l_amount_to_read INTEGER;
339 l_rec_found BOOLEAN := false;
340
341 CURSOR get_message (message_id IN XNP_MSGS.MSG_ID%TYPE ) IS
342 SELECT msg_id,
343 msg_code,
344 reference_id,
345 opp_reference_id,
346 msg_creation_date,
347 sender_name,
348 recipient_name,
349 msg_version,
350 order_id,
351 wi_instance_id,
352 fa_instance_id,
353 body_text
354 FROM xnp_msgs
355 WHERE msg_id = message_id ;
356
357 BEGIN
358
359 FOR rec IN get_message(p_msg_id)
360 LOOP
361 x_msg_header.message_id := rec.msg_id;
362 x_msg_header.message_code := rec.msg_code;
363 x_msg_header.reference_id := rec.reference_id;
364 x_msg_header.opp_reference_id := rec.opp_reference_id;
365 x_msg_header.creation_date := rec.msg_creation_date;
366 x_msg_header.sender_name := rec.sender_name;
367 x_msg_header.recipient_name := rec.recipient_name;
368 x_msg_header.version := rec.msg_version;
369 x_msg_header.order_id := rec.order_id;
370 x_msg_header.wi_instance_id := rec.wi_instance_id;
371 x_msg_header.fa_instance_id := rec.fa_instance_id;
372
373 l_lob_loc := rec.body_text;
374
375 l_rec_found := TRUE;
376
377 exit;
378
379 END LOOP;
380
381 IF l_rec_found THEN
382
383 l_amount_to_read := DBMS_LOB.GETLENGTH(l_lob_loc);
384
385 DBMS_LOB.READ(lob_loc => l_lob_loc,
386 amount => l_amount_to_read,
387 offset => 1,
388 buffer => x_msg_text );
389 END IF;
390 END get;
391
392 /***************************************************************************
393 ***** Procedure: GET()
394 ***** Purpose: Gets a message for the given message ID .
395 ****************************************************************************/
396
397 PROCEDURE get(
398 p_msg_id IN NUMBER
399 ,x_msg_text OUT NOCOPY VARCHAR2
400 )
401 IS
402
403 l_lob_loc CLOB ;
404 l_amount_to_read INTEGER;
405
406 CURSOR get_lob_loc ( message_id IN XNP_MSGS.MSG_ID%TYPE ) IS
407 SELECT body_text from xnp_msgs
408 WHERE msg_id = message_id ;
409
410 BEGIN
411
412 OPEN get_lob_loc ( p_msg_id ) ;
413
414 FETCH get_lob_loc INTO l_lob_loc ;
415
416 IF (get_lob_loc%NOTFOUND) THEN
417 CLOSE get_lob_loc ;
418 x_msg_text := NULL;
419 RETURN;
420 END IF;
421
422 CLOSE get_lob_loc ;
423
424 l_amount_to_read := DBMS_LOB.GETLENGTH(l_lob_loc) ;
425
426 DBMS_LOB.READ(lob_loc => l_lob_loc,
427 amount => l_amount_to_read,
428 offset => 1,
429 buffer => x_msg_text ) ;
430 END get;
431
432 /***************************************************************************
433 ***** Procedure: PUSH()
434 ***** Purpose: Inserts a message into XNP_MSGS table
435 ***** and enqueues the message on a specified Queue .
436 ****************************************************************************/
437
438 PROCEDURE push(
439 p_msg_header IN msg_header_rec_type
440 ,p_body_text IN VARCHAR2
441 ,p_queue_name IN VARCHAR2
442 ,p_recipient_list IN VARCHAR2 DEFAULT NULL
443 ,p_correlation_id IN VARCHAR2 DEFAULT NULL
444 ,p_priority IN INTEGER DEFAULT 1
445 ,p_commit_mode IN NUMBER DEFAULT c_on_commit
446 ,p_delay IN NUMBER DEFAULT DBMS_AQ.NO_DELAY
447 ,p_fe_name IN VARCHAR2 DEFAULT NULL
448 ,p_adapter_name IN VARCHAR2 DEFAULT NULL
449 )
450 IS
451
452 l_message SYSTEM.XNP_MESSAGE_TYPE ;
453 my_enqueue_options dbms_aq.enqueue_options_t ;
454 message_properties dbms_aq.message_properties_t ;
455 message_handle RAW(16) ;
456 recipients dbms_aq.aq$_recipient_list_t ;
457
458 l_recipient_name VARCHAR2(80) ;
459 l_recipient_count INTEGER ;
460 l_initial_pos INTEGER ;
461 l_delimeter_pos INTEGER ;
462
463 l_lob_loc CLOB ;
464 l_correlation_id VARCHAR2(1024) ;
465
466 l_adapter_name XNP_MSGS.ADAPTER_NAME%TYPE;
467 l_fe_name XNP_MSGS.FE_NAME%TYPE;
468
469 l_msg_header xnp_message.msg_header_rec_type ;
470 l_temp_fe NUMBER;
471 l_temp_adapter NUMBER;
472 BEGIN
473
474 l_msg_header := p_msg_header ;
475 --
476 -- The following block is added to stop l_adapter_name or l_fe_name from
477 -- being longer than 40 chars
478 -- Anpwang 04/24/2001
479 --
480 IF p_recipient_list IS NOT NULL THEN
481 -- get the possition of the first comma
482 l_temp_fe := INSTR(p_recipient_list,',')-1;
483 l_temp_adapter := l_temp_fe;
484 -- if no comma, then default to the length to the width of their database column
485 IF(l_temp_fe < 0) THEN
486 l_temp_fe := 40;
487 l_temp_adapter := 40;
488 END IF;
489 -- In case it is still longer than its database column
490 IF(l_temp_fe > 40) THEN
491 l_temp_fe := 40;
492 END IF;
493 -- In case it is still longer than its database column
494 IF(l_temp_adapter > 40 ) THEN
495 l_temp_adapter := 40;
496 END IF;
497
498 END IF;
499
500 IF (p_adapter_name IS NULL) THEN
501 -- trucate to the maximun of its database column width
502 l_adapter_name := SUBSTR(p_recipient_list,1,l_temp_adapter);
503 ELSE
504 l_adapter_name := p_adapter_name;
505 END IF;
506
507 IF (p_fe_name IS NULL) THEN
508 -- trucate to the maximun of its database column width
509 l_fe_name := SUBSTR(p_recipient_list,1,l_temp_fe);
510 ELSE
511 l_fe_name := p_fe_name;
512 END IF;
513
514 -- end of change by Anpwang 04/24/2001
515
516
517 -- adabholk 03/2001 performance
518 -- We do not send control messages through AQ
519 -- so the following code has been simplified
520
521 -- IF (l_msg_header.message_code = 'CONTROL') THEN
522 -- l_correlation_id := 'CONTROL' ;
523 -- ELSE
524 -- l_correlation_id := p_correlation_id ;
525 -- END IF ;
526
527
528 -- adabholk 07/2001 (1156)
529 -- to support specialization nowonwards correlation id
530 -- can only be MESSAGE_CODE
531 -- Ideally this should be followed by all the clients of
532 -- this procedure.
533 -- The hard coding at this place enforces this.
534 -- l_correlation_id := p_correlation_id ;
535
536 l_correlation_id := l_msg_header.message_code ;
537
538
539 IF ( l_msg_header.message_id IS NULL ) THEN
540 XNP_MESSAGE.get_sequence(l_msg_header.message_id) ;
541 END IF ;
542
543 IF (((l_msg_header.direction_indr = 'I') OR
544 (l_msg_header.direction_indr IS NULL)) AND
545 (xdp_utilities.g_message_list.COUNT < 0)
546 ) THEN
547 decode_xnp_msgs(l_msg_header, p_body_text) ;
548 END IF ;
549
550 INSERT into xnp_msgs (
551 msg_id,
552 msg_code,
553 direction_indicator,
554 reference_id,
555 opp_reference_id,
556 fe_name,
557 msg_creation_date,
558 sender_name,
559 recipient_name,
560 msg_status,
561 msg_version,
562 order_id,
563 wi_instance_id,
564 fa_instance_id,
565 adapter_name,
566 body_text,
567 created_by,
568 creation_date,
569 last_updated_by,
570 last_update_date )
571 VALUES (
572 l_msg_header.message_id,
573 l_msg_header.message_code,
574 l_msg_header.direction_indr,
575 l_msg_header.reference_id,
576 l_msg_header.opp_reference_id,
577 l_fe_name,
578 sysdate,
579 l_msg_header.sender_name,
580 l_msg_header.recipient_name,
581 'READY',
582 l_msg_header.version,
583 l_msg_header.order_id,
584 l_msg_header.wi_instance_id,
585 l_msg_header.fa_instance_id,
586 l_adapter_name,
587 empty_clob(),
588 fnd_global.user_id,
589 SYSDATE,
590 fnd_global.user_id,
591 SYSDATE )
592 RETURNING body_text INTO l_lob_loc ;
593
594 IF xdp_utilities.g_message_list.COUNT > 0 THEN
595
596 FOR i IN 1..xdp_utilities.g_message_list.COUNT
597 LOOP
598
599 DBMS_LOB.WRITEAPPEND (lob_loc => l_lob_loc,
600 amount =>LENGTH(xdp_utilities.g_message_list(i)),
601 buffer =>xdp_utilities.g_message_list(i));
602 END LOOP ;
603 ELSE
604 DBMS_LOB.WRITE (lob_loc => l_lob_loc,
605 amount => LENGTH(p_body_text),
606 offset => 1,
607 buffer => p_body_text) ;
608 END IF ;
609
610 --
611 -- For incoming messages, decode header fields from the XML Message
612 -- and populate the denormalized columns in XNP_MSGS table.
613 --
614
615 IF (p_commit_mode = C_IMMEDIATE) THEN
616 COMMIT ;
617 END IF ;
618
619 IF (p_priority IS NOT NULL) THEN
620 message_properties.priority := p_priority ;
621 END IF ;
622
623 IF (p_delay IS NOT NULL) THEN
624 message_properties.delay := p_delay ;
625 END IF;
626
627 l_message := SYSTEM.xnp_message_type( l_msg_header.message_id ) ;
628
629 --
630 -- check if there is a recipient, if there is no recipient, simply enqueue the
631 -- message on the specified queue
632 --
633
634 -- Use the correlation ID if one is specified
635
636 IF ( l_correlation_id is NOT NULL ) THEN
637 message_properties.correlation := l_correlation_id ;
638 END IF ;
639
640 --
641 -- Check if there are recipients, if there is no recipient, simply enqueue the
642 -- message on the specified queue
643 --
644
645 IF (p_recipient_list IS NOT NULL ) THEN
646 l_recipient_count := 1 ;
647 l_initial_pos := 1 ;
648
649 LOOP
650
651 l_delimeter_pos := INSTR ( p_recipient_list, ',',
652 l_initial_pos ) ;
653
654 IF ( l_delimeter_pos = 0 ) THEN
655
656 /* vbhatia -- 05/16/2002 */
657 /* Populating recipients with the last of the */
658 /* recipient list values */
659 IF( l_recipient_count > 1 ) THEN
660 l_recipient_name := SUBSTR( p_recipient_list,
661 l_initial_pos );
662 END IF;
663
664 EXIT ;
665 END IF ;
666
667 l_recipient_name := SUBSTR ( p_recipient_list,
668 l_initial_pos, l_delimeter_pos - l_initial_pos ) ;
669
670 recipients (l_recipient_count) := sys.aq$_agent (
671 l_recipient_name,NULL, NULL ) ;
672
673 l_initial_pos := l_delimeter_pos + 1 ;
674 l_recipient_count := l_recipient_count + 1 ;
675
676 END LOOP ;
677
678 /* vbhatia -- 05/16/2002 */
679 /* Only if there is only one recipient */
680 IF (l_delimeter_pos = 0 AND l_recipient_count = 1) THEN
681 l_recipient_name := p_recipient_list ;
682 END IF ;
683
684 recipients (l_recipient_count) := sys.aq$_agent ( l_recipient_name,
685 NULL, NULL ) ;
686
687 message_properties.recipient_list := recipients ;
688
689 END IF ;
690
691 IF (p_commit_mode = C_IMMEDIATE) THEN
692 my_enqueue_options.visibility := DBMS_AQ.IMMEDIATE ;
693 ELSE
694 my_enqueue_options.visibility := DBMS_AQ.ON_COMMIT ;
695 END IF ;
696
697
698 /* smoolcha removed hard coded strings for bug 3537148 */
699
700 -- IF p_queue_name = 'XNP.XNP_IN_EVT_Q' THEN
701 -- message_properties.exception_queue := 'XNP.XNP_IN_EVT_EXCEPTION_Q' ;
702 -- ELSIF p_queue_name = 'XNP.XNP_IN_MSG_Q' THEN
703 -- message_properties.exception_queue := 'XNP.XNP_IN_MSG_EXCEPTION_Q' ;
704 -- ELSIF p_queue_name = 'XNP.XNP_IN_TMR_Q' THEN
705 -- message_properties.exception_queue := 'XNP.XNP_IN_TMR_EXCEPTION_Q' ;
706 -- ELSIF p_queue_name = 'XNP.XNP_OUT_MSG_Q' THEN
707 -- message_properties.exception_queue := 'XNP.XNP_OUT_MSG_EXCEPTION_Q' ;
708 -- END IF ;
709
710 IF instr(p_queue_name,'XNP_IN_EVT_Q') > 0 THEN
711 message_properties.exception_queue := g_xnp_schema || '.XNP_IN_EVT_EXCEPTION_Q' ;
712 ELSIF instr(p_queue_name ,'XNP_IN_MSG_Q') > 0 THEN
713 message_properties.exception_queue := g_xnp_schema || '.XNP_IN_MSG_EXCEPTION_Q' ;
714 ELSIF instr(p_queue_name,'XNP_IN_TMR_Q') > 0 THEN
715 message_properties.exception_queue := g_xnp_schema || '.XNP_IN_TMR_EXCEPTION_Q' ;
716 ELSIF instr(p_queue_name,'XNP_OUT_MSG_Q') > 0 THEN
717 message_properties.exception_queue := g_xnp_schema || '.XNP_OUT_MSG_EXCEPTION_Q' ;
718 END IF ;
719
720 DBMS_AQ.ENQUEUE (
721 queue_name => p_queue_name ,
722 enqueue_options => my_enqueue_options,
723 message_properties => message_properties,
724 payload => l_message,
725 msgid => message_handle ) ;
726
727 END push;
728
729 /***************************************************************************
730 ***** Procedure: PUSH()
731 ***** Purpose: Inserts a message into XNP_MSGS table
732 ***** and enqueues the message on a specified Queue .
733 ****************************************************************************/
734
735 PROCEDURE push(
736 p_message_id IN NUMBER
737 ,p_message_code IN VARCHAR2
738 ,p_reference_id IN VARCHAR2
739 ,p_opp_reference_id IN VARCHAR2
740 ,p_direction_indr IN VARCHAR2
741 ,p_creation_date IN DATE
742 ,p_sender_name IN VARCHAR2
743 ,p_recipient_name IN VARCHAR2
744 ,p_version OUT NOCOPY VARCHAR2
745 ,p_order_id IN NUMBER
746 ,p_wi_instance_id IN NUMBER
747 ,p_fa_instance_id IN NUMBER
748 ,p_body_text IN VARCHAR2
749 ,p_queue_name IN VARCHAR2
750 ,p_recipient_list IN VARCHAR2 DEFAULT NULL
751 ,p_correlation_id IN VARCHAR2 DEFAULT NULL
752 ,p_priority IN INTEGER DEFAULT 1
753 ,p_commit_mode IN NUMBER DEFAULT c_on_commit
754 )
755 IS
756 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
757
758 BEGIN
759
760 l_msg_header.message_id := p_message_id ;
761
762 IF p_message_code is null THEN
763 xnp_xml_utils.decode(p_body_text,'MESSAGE_CODE',
764 l_msg_header.message_code) ;
765 ELSE
766 l_msg_header.message_code := p_message_code ;
767 END IF ;
768
769 l_msg_header.reference_id := p_reference_id ;
770 l_msg_header.opp_reference_id := p_opp_reference_id ;
771 l_msg_header.direction_indr := p_direction_indr ;
772 l_msg_header.creation_date := p_creation_date ;
773 l_msg_header.sender_name := p_sender_name ;
774 l_msg_header.recipient_name := p_recipient_name ;
775 l_msg_header.version := p_version ;
776 l_msg_header.order_id := p_order_id ;
777 l_msg_header.wi_instance_id := p_wi_instance_id ;
778 l_msg_header.fa_instance_id := p_fa_instance_id ;
779
780 push( l_msg_header,
781 p_body_text,
782 p_queue_name,
783 p_recipient_list,
784 p_correlation_id,
785 p_priority,
786 p_commit_mode) ;
787
788 END push ;
789
790 /***************************************************************************
791 ***** Procedure: POP()
792 ***** Purpose: Retrieves a message from the specified message Q .
793 ****************************************************************************/
794
795 PROCEDURE POP(
796 p_queue_name IN VARCHAR2
797 ,x_msg_header OUT NOCOPY MSG_HEADER_REC_TYPE
798 ,x_body_text OUT NOCOPY VARCHAR2
799 ,x_error_code OUT NOCOPY NUMBER
800 ,x_error_message OUT NOCOPY VARCHAR2
801 ,p_consumer_name IN VARCHAR2 DEFAULT NULL
802 ,p_correlation_id IN VARCHAR2 DEFAULT NULL
803 ,p_commit_mode IN NUMBER DEFAULT C_ON_COMMIT
804 ,p_msg_id IN RAW DEFAULT NULL
805 )
806 IS
807
808 l_msg_status VARCHAR2(40) ;
809 l_message SYSTEM.XNP_MESSAGE_TYPE ;
810 my_dequeue_options dbms_aq.dequeue_options_t ;
811 message_properties dbms_aq.message_properties_t ;
812 message_handle RAW(16) ;
813 l_timeout NUMBER ;
814 e_q_time_out EXCEPTION ;
815 l_fnd_message VARCHAR2(4000) ;
816
817 l_count NUMBER;
818
819 PRAGMA EXCEPTION_INIT ( e_q_time_out, -25228 ) ;
820
821 BEGIN
822
823 x_error_code := 0 ;
824 x_error_message := NULL ;
825
826
827 IF ( POP_TIMEOUT <> 0 ) THEN
828 my_dequeue_options.wait := POP_TIMEOUT ;
829 END IF ;
830
831 my_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE ;
832
833 --
834 --setting the consumer name would only dequeue messages
835 --destined for that consumer.
836 --
837
838 IF ( p_consumer_name IS NOT NULL ) THEN
839 my_dequeue_options.consumer_name := p_consumer_name ;
840 END IF ;
841
842 -- Use the correlation ID if one is specified
843
844 IF ( p_correlation_id IS NOT NULL ) THEN
845 my_dequeue_options.correlation := p_correlation_id ;
846 END IF ;
847
848 -- Dequeue by Message Id if one is specified
849
850 IF ( p_msg_id IS NOT NULL ) THEN
851 my_dequeue_options.msgid := p_msg_id ;
852 END IF ;
853
854 my_dequeue_options.DEQUEUE_MODE := DBMS_AQ.REMOVE ;
855
856 IF (p_commit_mode = C_IMMEDIATE) THEN
857 my_dequeue_options.visibility := DBMS_AQ.IMMEDIATE ;
858 ELSE
859 my_dequeue_options.visibility := DBMS_AQ.ON_COMMIT ;
860 END IF ;
861
862 /* Loop till the FIRST 'READY' message is obtained */
863 -- bellsouth 1482985
864 -- process 'READY' or 'PROCESSED' messaged
865 -- First consumer would change the staus to PROCESSED
866 -- In case of multiple consumers we want to pick up this
867 -- 'PROCESSED' message also.
868
869 LOOP
870
871 DBMS_AQ.DEQUEUE (
872 queue_name => p_queue_name ,
873 dequeue_options => my_dequeue_options,
874 message_properties => message_properties,
875 payload => l_message,
876 msgid => message_handle ) ;
877
878 BEGIN
879 XDP_AQ_UTILITIES.SET_CONTEXT( l_message.message_id, 'MESSAGE_OBJECT');
880
881 IF(p_queue_name = xnp_event.c_inbound_msg_q) THEN
882
883 IF(g_APPS_MAINTENANCE_MODE = 'MAINT') THEN
884
885 SELECT count(*)
886 INTO l_count
887 FROM xnp_timer_registry
888 WHERE timer_id = l_message.message_id;
889
890 IF(l_count = 0) THEN
891 COMMIT;
892 fnd_file.put_line(fnd_file.log,
893 'SET_CONTEXT: Do not process further for
894 Message Object '||l_message.message_id);
895 RAISE stop_processing;
896 END IF;
897
898 END IF;
899
900 END IF;
901
902 EXCEPTION
903 WHEN stop_processing THEN
904 x_error_code := XNP_ERRORS.G_DEQUEUE_TIMEOUT;
905 return;
906 END;
907
908 x_msg_header.message_id := l_message.message_id ;
909
910 get_status (l_message.message_id, l_msg_status) ;
911
912 IF ( l_msg_status = 'READY' OR l_msg_status = 'PROCESSED' ) THEN
913
914 -- adabholk 03/2001
915 -- performance fix
916 -- new get() replaces two get calls
917
918 xnp_message.get(
919 p_msg_id => l_message.message_id
920 ,x_msg_header => x_msg_header
921 ,x_msg_text => x_body_text);
922 /*
923 --
924 -- get_header (
925 -- l_message.message_id ,
926 -- x_msg_header ) ;
927 --
928 -- get ( l_message.message_id, x_body_text ) ;
929 */
930 EXIT ;
931
932 END IF ;
933
934 END LOOP ;
935
936 EXCEPTION
937 WHEN e_q_time_out THEN
938 x_error_code := XNP_ERRORS.G_DEQUEUE_TIMEOUT ;
939
940 FND_MESSAGE.set_name ('XNP', 'DEQUEUE_TIMEOUT') ;
941 l_fnd_message := FND_MESSAGE.get ;
942
943 x_error_message := l_fnd_message ;
944
945 END pop;
946
947 /***************************************************************************
948 ***** Procedure: GET_SUBSCRIBER_LIST()
949 ***** Purpose: Gets a comma separated subscriber list.
950 ****************************************************************************/
951
952 PROCEDURE get_subscriber_list(
953 p_msg_code IN VARCHAR2
954 ,x_subscriber_list OUT NOCOPY VARCHAR2
955 )
956 IS
957 CURSOR get_subscribers (message_code IN VARCHAR2) IS
958 SELECT xnp_utils.get_adapter_using_fe(
959 FET.fulfillment_element_name) adapter_name
960 FROM xdp_fes FET,
961 xnp_event_subscribers ESS
962 WHERE FET.FE_ID = ESS.FE_ID
963 AND ESS.msg_code = message_code ;
964
965 l_subscriber_count INTEGER ;
966
967 BEGIN
968
969 x_subscriber_list := NULL ;
970 l_subscriber_count := 1 ;
971
972 FOR subscriber in get_subscribers(p_msg_code) LOOP
973
974 IF (get_subscribers%NOTFOUND) THEN
975 EXIT ;
976 END IF ;
977
978 IF ( l_subscriber_count > 1 ) THEN
979 x_subscriber_list:= x_subscriber_list|| ',' ;
980 END IF ;
981
982 x_subscriber_list:= x_subscriber_list
983 || subscriber.adapter_name ;
984
985 l_subscriber_count := l_subscriber_count + 1 ;
986
987 END LOOP ;
988
989 END get_subscriber_list ;
990
991 /***************************************************************************
992 ***** Procedure: POP()
993 ***** Purpose: Overloaded to retrieve a message from the
994 ***** specified message Q .
995 ****************************************************************************/
996
997 PROCEDURE POP(
998 p_queue_name IN VARCHAR2
999 ,x_message_id OUT NOCOPY NUMBER
1000 ,x_message_code OUT NOCOPY VARCHAR2
1001 ,x_reference_id OUT NOCOPY VARCHAR2
1002 ,x_opp_reference_id OUT NOCOPY VARCHAR2
1003 ,x_body_text OUT NOCOPY VARCHAR2
1004 ,x_creation_date OUT NOCOPY DATE
1005 ,x_sender_name OUT NOCOPY VARCHAR2
1006 ,x_recipient_name OUT NOCOPY VARCHAR2
1007 ,x_version OUT NOCOPY VARCHAR2
1008 ,x_order_id OUT NOCOPY NUMBER
1009 ,x_wi_instance_id OUT NOCOPY NUMBER
1010 ,x_fa_instance_id OUT NOCOPY NUMBER
1011 ,x_error_code OUT NOCOPY NUMBER
1012 ,x_error_message OUT NOCOPY VARCHAR2
1013 ,p_consumer_name IN VARCHAR2 DEFAULT NULL
1014 ,p_correlation_id IN VARCHAR2 DEFAULT NULL
1015 )
1016 IS
1017
1018 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
1019
1020 BEGIN
1021
1022 x_error_code := 0 ;
1023 x_error_message := 'NOTFOUND' ;
1024
1025 pop (
1026 p_queue_name,
1027 l_msg_header,
1028 x_body_text,
1029 x_error_code,
1030 x_error_message,
1031 p_consumer_name,
1032 p_correlation_id,
1033 C_ON_COMMIT ) ;
1034 -- Changed on 110100
1035 -- As we do not want pop to happen immediately anytime
1036 -- c_immediate ) ;
1037
1038 IF (x_body_text IS NULL) THEN
1039 x_body_text := 'NOTFOUND' ;
1040 END IF ;
1041
1042 IF (l_msg_header.message_id IS NULL) THEN
1043 x_message_id:= 0 ;
1044 ELSE
1045 x_message_id := l_msg_header.message_id ;
1046 END IF ;
1047
1048 IF (l_msg_header.message_code IS NULL) THEN
1049 x_message_code := 'NOTFOUND' ;
1050 ELSE
1051 x_message_code := l_msg_header.message_code ;
1052 END IF ;
1053
1054 IF (l_msg_header.reference_id IS NULL) THEN
1055 x_reference_id := 'NOTFOUND' ;
1056 ELSE
1057 x_reference_id := l_msg_header.reference_id ;
1058 END IF ;
1059
1060 IF (l_msg_header.opp_reference_id IS NULL) THEN
1061 x_opp_reference_id := 'NOTFOUND' ;
1062 ELSE
1063 x_opp_reference_id := l_msg_header.opp_reference_id ;
1064 END IF ;
1065
1066 IF (l_msg_header.creation_date IS NULL) THEN
1067 x_creation_date := SYSDATE;
1068 ELSE
1069 x_creation_date := l_msg_header.creation_date ;
1070 END IF ;
1071
1072 IF (l_msg_header.sender_name IS NULL) THEN
1073 x_sender_name := 'NOTFOUND' ;
1074 ELSE
1075 x_sender_name := l_msg_header.sender_name ;
1076 END IF ;
1077
1078 IF (l_msg_header.recipient_name IS NULL) THEN
1079 x_recipient_name := 'NOTFOUND' ;
1080 ELSE
1081 x_recipient_name := l_msg_header.recipient_name ;
1082 END IF ;
1083
1084 IF (l_msg_header.version IS NULL) THEN
1085 x_version:= 0;
1086 ELSE
1087 x_version := l_msg_header.version ;
1088 END IF ;
1089
1090 IF (l_msg_header.order_id IS NULL) THEN
1091 x_order_id := 0;
1092 ELSE
1093 x_order_id := l_msg_header.order_id ;
1094 END IF ;
1095
1096 IF (l_msg_header.wi_instance_id IS NULL) THEN
1097 x_wi_instance_id := 0;
1098 ELSE
1099 x_wi_instance_id := l_msg_header.wi_instance_id ;
1100 END IF ;
1101
1102 IF (l_msg_header.fa_instance_id IS NULL) THEN
1103 x_fa_instance_id := 0;
1104 ELSE
1105 x_fa_instance_id := l_msg_header.fa_instance_id ;
1106 END IF ;
1107
1108 END pop;
1109
1110 /***************************************************************************
1111 ***** Procedure: UPDATE_STATUS()
1112 ***** Purpose: Updates message status.
1113 ****************************************************************************/
1114
1115 PROCEDURE update_status(
1116 p_msg_id IN NUMBER
1117 ,p_status IN VARCHAR2
1118 ,p_error_desc IN VARCHAR2 DEFAULT NULL
1119 ,p_order_id IN NUMBER DEFAULT NULL
1120 ,p_wi_instance_id IN NUMBER DEFAULT NULL
1121 ,p_fa_instance_id IN NUMBER DEFAULT NULL
1122 )
1123 IS
1124
1125 BEGIN
1126
1127 -- mviswana 11/2001
1128 -- bug fix # 1882340 to populate send_rcv_date
1129
1130 IF (p_order_id IS NULL) THEN
1131 UPDATE XNP_MSGS SET msg_status = p_status,
1132 send_rcv_date = SYSDATE,
1133 last_update_date = SYSDATE,
1134 description = description || p_error_desc
1135 WHERE msg_id = p_msg_id ;
1136 ELSE
1137 UPDATE XNP_MSGS SET msg_status = p_status,
1138 send_rcv_date = SYSDATE,
1139 last_update_date = SYSDATE,
1140 description = description || p_error_desc,
1141 order_id = p_order_id,
1142 wi_instance_id = p_wi_instance_id,
1143 fa_instance_id = p_fa_instance_id
1144 WHERE msg_id = p_msg_id ;
1145 END IF;
1146
1147 /* Notify Fallout Management Center of Failures */
1148
1149 IF (p_status = 'FAILED') THEN
1150 notify_fmc(p_msg_id, p_error_desc) ;
1151 END IF ;
1152
1153 END update_status ;
1154
1155 /***************************************************************************
1156 ***** Procedure: GET_STATUS()
1157 ***** Purpose: Gets the Message Status for a given msg ID.
1158 ****************************************************************************/
1159
1160 PROCEDURE get_status(
1161 p_msg_id IN NUMBER
1162 ,x_status OUT NOCOPY VARCHAR2
1163 )
1164 IS
1165
1166 CURSOR get_msg_status IS
1167 SELECT msg_status FROM XNP_MSGS
1168 WHERE msg_id = p_msg_id ;
1169
1170 BEGIN
1171
1172 OPEN get_msg_status ;
1173 FETCH get_msg_status INTO x_status ;
1174 CLOSE get_msg_status ;
1175
1176 END get_status ;
1177
1178 /**********************************************************************************
1179 ****
1180 ***********************************************************************************/
1181
1182 PROCEDURE xnp_mte_insert_element (
1183 p_msg_code IN VARCHAR2
1184 ,p_msg_type IN VARCHAR2
1185 ) IS
1186
1187 L_PARENT_ID NUMBER;
1188 L_CHILD_ID NUMBER;
1189 L_GRANDCHILD_ID NUMBER;
1190 L_STRUCTURE_ID NUMBER;
1191 BEGIN
1192
1193 --
1194 -- CREATING DEFAULT ELEMENTS FOR A MESSAGE
1195 --
1196 -- Inserting Message Type into Elements
1197
1198 -- adabholk 03/2001
1199 -- performance changes
1200 -- Use of RETURNING
1201
1202 INSERT INTO XNP_MSG_ELEMENTS(
1203 MSG_ELEMENT_ID
1204 ,MSG_CODE
1205 ,NAME
1206 ,ELEMENT_DATATYPE
1207 ,MANDATORY_FLAG
1208 ,PARAMETER_FLAG
1209 ,CREATED_BY
1210 ,CREATION_DATE
1211 ,LAST_UPDATED_BY
1212 ,LAST_UPDATE_DATE
1213 ,LAST_UPDATE_LOGIN)
1214 VALUES(
1215 XNP_MSG_ELEMENTS_S.NEXTVAL
1216 ,P_MSG_CODE
1217 ,'MESSAGE' -- Always insert type to be 'Message' - even though it can be an event or a timer
1218 ,'VARCHAR2'
1219 ,'Y'
1220 ,'N'
1221 ,FND_GLOBAL.USER_ID
1222 ,SYSDATE
1223 ,FND_GLOBAL.USER_ID
1224 ,SYSDATE
1225 ,FND_GLOBAL.LOGIN_ID)
1226 RETURNING MSG_ELEMENT_ID INTO L_PARENT_ID;
1227
1228 -- Inserting Message Name into Elements
1229
1230 -- adabholk 03/2001
1231 -- performance changes
1232 -- Use of RETURNING
1233
1234 INSERT INTO XNP_MSG_ELEMENTS(
1235 MSG_ELEMENT_ID
1236 ,MSG_CODE
1237 ,NAME
1238 ,ELEMENT_DATATYPE
1239 ,MANDATORY_FLAG
1240 ,PARAMETER_FLAG
1241 ,CREATED_BY
1242 ,CREATION_DATE
1243 ,LAST_UPDATED_BY
1244 ,LAST_UPDATE_DATE
1245 ,LAST_UPDATE_LOGIN
1246 )
1247 VALUES(
1248 XNP_MSG_ELEMENTS_S.NEXTVAL
1249 ,P_MSG_CODE
1250 ,P_MSG_CODE
1251 ,'VARCHAR2'
1252 ,'Y'
1253 ,'N'
1254 ,FND_GLOBAL.USER_ID
1255 ,SYSDATE
1256 ,FND_GLOBAL.USER_ID
1257 ,SYSDATE
1258 ,FND_GLOBAL.LOGIN_ID
1259 )
1260 RETURNING MSG_ELEMENT_ID INTO L_CHILD_ID;
1261
1262 -- Inserting Message Name into Structures with 'Message' as parent
1263
1264 -- adabholk 03/2001
1265 -- performance changes
1266 -- Use of RETURNING
1267
1268 INSERT INTO XNP_MSG_STRUCTURES(
1269 STRUCTURE_ID
1270 ,PARENT_ELEMENT_ID
1271 ,CHILD_ELEMENT_ID
1272 ,MSG_CODE
1273 ,CREATED_BY
1274 ,CREATION_DATE
1275 ,LAST_UPDATED_BY
1276 ,LAST_UPDATE_DATE
1277 ,LAST_UPDATE_LOGIN
1278 )
1279 VALUES(
1280 XNP_MSG_STRUCTURES_S.NEXTVAL
1281 ,L_PARENT_ID
1282 ,L_CHILD_ID
1283 ,P_MSG_CODE
1284 ,FND_GLOBAL.USER_ID
1285 ,SYSDATE
1286 ,FND_GLOBAL.USER_ID
1287 ,SYSDATE
1288 ,FND_GLOBAL.LOGIN_ID
1289 )
1290 RETURNING STRUCTURE_ID INTO L_STRUCTURE_ID;
1291
1292 IF P_MSG_TYPE = 'TIMER' THEN
1293
1294 -- Inserting Delay into Elements
1295
1296 -- adabholk 03/2001
1297 -- performance changes
1298 -- Use of RETURNING
1299
1300 INSERT INTO XNP_MSG_ELEMENTS(
1301 MSG_ELEMENT_ID
1302 ,MSG_CODE
1303 ,NAME
1304 ,ELEMENT_DATATYPE
1305 ,ELEMENT_DEFAULT_VALUE
1306 ,MANDATORY_FLAG
1307 ,PARAMETER_FLAG
1308 ,CREATED_BY
1309 ,CREATION_DATE
1310 ,LAST_UPDATED_BY
1311 ,LAST_UPDATE_DATE
1312 ,LAST_UPDATE_LOGIN
1313 )
1314 VALUES(
1315 XNP_MSG_ELEMENTS_S.NEXTVAL
1316 ,P_MSG_CODE
1317 ,'DELAY'
1318 ,'NUMBER'
1319 ,'0'
1320 ,'Y'
1321 ,'N'
1322 ,FND_GLOBAL.USER_ID
1323 ,SYSDATE
1324 ,FND_GLOBAL.USER_ID
1325 ,SYSDATE
1326 ,FND_GLOBAL.LOGIN_ID
1327 )
1328 RETURNING MSG_ELEMENT_ID INTO L_GRANDCHILD_ID;
1329
1330 -- Inserting Delay into Structures with Message Name as parent
1331 -- adabholk 03/2001
1332 -- performance changes
1333 -- Use of RETURNING
1334
1335 INSERT INTO XNP_MSG_STRUCTURES(
1336 STRUCTURE_ID
1337 ,PARENT_ELEMENT_ID
1338 ,CHILD_ELEMENT_ID
1339 ,MSG_CODE
1340 ,CREATED_BY
1341 ,CREATION_DATE
1342 ,LAST_UPDATED_BY
1343 ,LAST_UPDATE_DATE
1344 ,LAST_UPDATE_LOGIN
1345 )
1346 VALUES(
1347 XNP_MSG_STRUCTURES_S.NEXTVAL
1348 ,L_CHILD_ID
1349 ,L_GRANDCHILD_ID
1350 ,P_MSG_CODE
1351 ,FND_GLOBAL.USER_ID
1352 ,SYSDATE
1353 ,FND_GLOBAL.USER_ID
1354 ,SYSDATE
1355 ,FND_GLOBAL.LOGIN_ID
1356 )
1357 RETURNING STRUCTURE_ID INTO L_STRUCTURE_ID;
1358
1359 -- Inserting Interval into Elements
1360
1361 -- adabholk 03/2001
1362 -- performance changes
1363 -- Use of RETURNING
1364
1365 INSERT INTO XNP_MSG_ELEMENTS(
1366 MSG_ELEMENT_ID
1367 ,MSG_CODE
1368 ,NAME
1369 ,ELEMENT_DATATYPE
1370 ,ELEMENT_DEFAULT_VALUE
1371 ,MANDATORY_FLAG
1372 ,PARAMETER_FLAG
1373 ,CREATED_BY
1374 ,CREATION_DATE
1375 ,LAST_UPDATED_BY
1376 ,LAST_UPDATE_DATE
1377 ,LAST_UPDATE_LOGIN
1378 )
1379 VALUES(
1380 XNP_MSG_ELEMENTS_S.NEXTVAL
1381 ,P_MSG_CODE
1382 ,'INTERVAL'
1383 ,'NUMBER'
1384 ,'0'
1385 ,'Y'
1386 ,'N'
1387 ,FND_GLOBAL.USER_ID
1388 ,SYSDATE
1389 ,FND_GLOBAL.USER_ID
1390 ,SYSDATE
1391 ,FND_GLOBAL.LOGIN_ID
1392 )
1393 RETURNING MSG_ELEMENT_ID INTO L_GRANDCHILD_ID;
1394
1395 -- Inserting Interval into Structures with Message Name as parent
1396 -- adabholk 03/2001
1397 -- performance changes
1398 -- Use of RETURNING
1399
1400 INSERT INTO XNP_MSG_STRUCTURES(
1401 STRUCTURE_ID
1402 ,PARENT_ELEMENT_ID
1403 ,CHILD_ELEMENT_ID
1404 ,MSG_CODE
1405 ,CREATED_BY
1406 ,CREATION_DATE
1407 ,LAST_UPDATED_BY
1408 ,LAST_UPDATE_DATE
1409 ,LAST_UPDATE_LOGIN
1410 )
1411 VALUES(
1412 XNP_MSG_STRUCTURES_S.NEXTVAL
1413 ,L_CHILD_ID
1414 ,L_GRANDCHILD_ID
1415 ,P_MSG_CODE
1416 ,FND_GLOBAL.USER_ID
1417 ,SYSDATE
1418 ,FND_GLOBAL.USER_ID
1419 ,SYSDATE
1420 ,FND_GLOBAL.LOGIN_ID
1421 )
1422 RETURNING STRUCTURE_ID INTO L_STRUCTURE_ID;
1423
1424 END IF; -- IF TIMER
1425
1426 END xnp_mte_insert_element ;
1427
1428 /*************************************************************************
1429 PROCEDURE : FIX()
1430 PURPOSE : Re-enqueues a message on the Inbound Message queue for processing
1431 by the Message Server. FIX() can only be used for Inbound Messages.
1432 It also updates the status to ready and clears any error message.
1433 **************************************************************************/
1434
1435 PROCEDURE fix (
1436 P_MSG_ID IN NUMBER
1437 )
1438 IS
1439 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
1440 l_msg_text VARCHAR2(32767) ;
1441
1442 l_message SYSTEM.XNP_MESSAGE_TYPE ;
1443 l_msg_id XNP_MSGS.MSG_ID%TYPE ;
1444 my_enqueue_options dbms_aq.enqueue_options_t ;
1445 message_properties dbms_aq.message_properties_t ;
1446 message_handle RAW(16) ;
1447
1448
1449 l_feedback VARCHAR2(4000) := NULL ;
1450
1451 BEGIN
1452
1453 select msg_code into l_msg_header.message_code from xnp_msgs where msg_id = p_msg_id;
1454
1455 UPDATE xnp_msgs SET msg_status = 'READY',
1456 last_update_date = SYSDATE,
1457 description = NULL
1458 WHERE msg_id = p_msg_id ;
1459
1460 message_properties.priority := 3 ;
1461 -- changed for specialization support adabholk 07/2001
1462 -- message_properties.correlation := 'MSG_SERVER' ;
1463 message_properties.correlation := l_msg_header.message_code;
1464 my_enqueue_options.visibility := DBMS_AQ.ON_COMMIT ;
1465
1466 l_message := SYSTEM.xnp_message_type(p_msg_id) ;
1467
1468 DBMS_AQ.ENQUEUE (
1469 queue_name => XNP_EVENT.C_INBOUND_MSG_Q ,
1470 enqueue_options => my_enqueue_options,
1471 message_properties => message_properties,
1472 payload => l_message,
1473 msgid => message_handle ) ;
1474
1475 COMMIT ;
1476
1477
1478 -- Now this is not supported from HTML so commented out.
1479
1480 -- FND_MESSAGE.set_name ('XNP', 'MSG_FIX_FEEDBACK') ;
1481 -- l_feedback := FND_MESSAGE.get ;
1482 --
1483 -- htp.htmlopen;
1484 -- htp.bodyopen;
1485 -- htp.p(l_feedback) ;
1486 -- htp.bodyclose;
1487 -- htp.htmlclose;
1488
1489 END fix ;
1490
1491 /*************************************************************************
1492 PROCEDURE : populate_xnp_msgs()
1493 PURPOSE : Decodes the header fields in the newly arrived XML message
1494 and populates the columns in XNP_MSGS table
1495 **************************************************************************/
1496
1497 PROCEDURE decode_xnp_msgs (
1498 p_msg_header IN OUT NOCOPY xnp_message.msg_header_rec_type,
1499 p_body_text IN VARCHAR2
1500 ) IS
1501 l_creation_date VARCHAR2(512) ;
1502 l_header VARCHAR2(4000) ;
1503
1504 BEGIN
1505
1506 -- adabholk 03/2001
1507 -- performance fix
1508 -- First get the header and then decode the header
1509 -- to get the other fields.
1510
1511 xnp_xml_utils.decode (p_body_text, 'HEADER', l_header) ;
1512
1513 xnp_xml_utils.decode (l_header, 'OPP_REFERENCE_ID',
1514 p_msg_header.reference_id) ;
1515
1516 -- get their reference ID for tracking and accounting
1517
1518 xnp_xml_utils.decode (l_header, 'REFERENCE_ID',
1519 p_msg_header.opp_reference_id) ;
1520
1521 xnp_xml_utils.decode (l_header, 'MESSAGE_CODE',
1522 p_msg_header.message_code) ;
1523
1524 xnp_xml_utils.decode (l_header, 'VERSION',
1525 p_msg_header.version ) ;
1526
1527 xnp_xml_utils.decode (l_header, 'CREATION_DATE',
1528 l_creation_date) ;
1529
1530 p_msg_header.creation_date := xnp_utils.canonical_to_date(
1531 l_creation_date) ;
1532
1533 xnp_xml_utils.decode (l_header, 'SENDER_NAME',
1534 p_msg_header.sender_name) ;
1535
1536 xnp_xml_utils.decode (l_header, 'RECIPIENT_NAME',
1537 p_msg_header.recipient_name) ;
1538
1539 END decode_xnp_msgs ;
1540
1541
1542 /***************************************************************************
1543 ***** Procedure: NOTIFY_FMC()
1544 ***** Purpose: Notifies the FMC of Message Processing Failure.
1545 ***** Description: Starts a Workflow to notify the FMC. The FMC waits
1546 ***** for a
1547 ***** response from an FMC user.
1548 ****************************************************************************/
1549
1550 PROCEDURE notify_fmc(
1551 P_MSG_ID IN NUMBER
1552 ,P_ERROR_DESC IN VARCHAR2
1553 )
1554 IS
1555 l_item_type VARCHAR2(1024) ;
1556 l_item_key VARCHAR2(4000) ;
1557 l_performer VARCHAR2(1024) ;
1558 l_role_name VARCHAR2(1024) ;
1559 l_msg_id NUMBER ;
1560
1561 CURSOR get_performer_name IS
1562 SELECT NVL(xms.role_name,'FND_RESP535:21704')
1563 FROM xnp_msg_types_b xms, xnp_msgs xmg
1564 WHERE xmg.msg_id = p_msg_id
1565 AND xms.msg_code = xmg.msg_code;
1566
1567 BEGIN
1568
1569
1570 -- The Performer Name for the Message is already in the correct format
1571 -- To send the notification. Thus the translation from Responsibility_key
1572 -- to performer is not required.
1573
1574 OPEN get_performer_name ;
1575 FETCH get_performer_name INTO l_performer ;
1576 CLOSE get_performer_name ;
1577
1578 -- Notification performer is defaulted to 'NP_SYSADMIN'
1579 -- Bug 1658346
1580 -- Changed this to FND_RESP534:21689
1581 -- Changed notification performer to FND_RESP535:21704 (OP_SYSADMIN) rnyberg 03/08/02
1582 IF l_performer IS NULL OR l_performer IN ('NP_SYSADMIN','OP_SYSADMIN') THEN
1583 l_performer := 'FND_RESP535:21704';
1584 END IF;
1585
1586 -- l_performer := xdp_utilities.get_wf_notifrecipient(l_role_name) ;
1587
1588 l_item_type := 'XDPWFSTD' ;
1589
1590 xnp_message.get_sequence(l_msg_id) ;
1591
1592 l_item_key := 'MESSAGE_' || TO_CHAR(p_msg_id) ||
1593 TO_CHAR(l_msg_id) ;
1594
1595 wf_core.context('XDP_WF_STANDARD',
1596 'MSG_FAILURE_NOTIFICATION',
1597 l_item_type,
1598 l_item_key) ;
1599
1600 wf_engine.createprocess(l_item_type,
1601 l_item_key,
1602 'MSG_FAILURE_NOTIFICATION') ;
1603
1604 wf_engine.SetItemAttrNumber(
1605 ItemType=>l_item_type,
1606 itemkey=>l_item_key,
1607 aname=>'MSG_ID',
1608 avalue=>p_msg_id);
1609
1610 wf_engine.SetItemAttrText(
1611 ItemType=>l_item_type,
1612 ItemKey=>l_item_key,
1613 aname=>'MSG_ERROR',
1614 avalue=>p_error_desc);
1615
1616 wf_engine.SetItemAttrText(
1617 ItemType=>l_item_type,
1618 ItemKey=>l_item_key,
1619 aname=>'MSG_HANDLING_ROLE',
1620 avalue=>l_performer);
1621
1622 wf_engine.startprocess(l_item_type,
1623 l_item_key ) ;
1624
1625 END notify_fmc ;
1626
1627 /*******************************************************************/
1628
1629 PROCEDURE push(
1630 P_QNAME IN VARCHAR2
1631 ,P_MSG_TEXT IN VARCHAR2
1632 ,P_FE_NAME IN VARCHAR2
1633 ,P_ADAPTER_NAME IN VARCHAR2
1634 ,X_ERROR_CODE OUT NOCOPY NUMBER
1635 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1636 ,P_COMMIT_MODE IN NUMBER DEFAULT C_IMMEDIATE)
1637 IS
1638 l_msg_id NUMBER;
1639 BEGIN
1640 push(p_qname,
1641 p_msg_text,
1642 p_fe_name,
1643 p_adapter_name,
1644 l_msg_id,
1645 x_error_code,
1646 x_error_message,
1647 p_commit_mode);
1648 END push;
1649 --
1650 -- This push is identical to the original push for an adapter, but returns message id to the caller
1651 --
1652 PROCEDURE push(
1653 P_QNAME IN VARCHAR2
1654 ,P_MSG_TEXT IN VARCHAR2
1655 ,P_FE_NAME IN VARCHAR2
1656 ,P_ADAPTER_NAME IN VARCHAR2
1657 ,X_MSG_ID OUT NOCOPY NUMBER
1658 ,X_ERROR_CODE OUT NOCOPY NUMBER
1659 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1660 ,P_COMMIT_MODE IN NUMBER DEFAULT C_IMMEDIATE)
1661 IS
1662 l_msg_header MSG_HEADER_REC_TYPE ;
1663 l_queue_name VARCHAR2(1024) ;
1664 l_status VARCHAR2(1024) ;
1665 l_industry VARCHAR2(1024) ;
1666 l_ret BOOLEAN ;
1667 BEGIN
1668 x_error_code := 0 ;
1669 x_error_message := 'NO_ERRORS' ;
1670
1671 l_queue_name := g_xnp_schema || '.' || p_qname ;
1672
1673 xnp_xml_utils.decode(p_msg_text, 'MESSAGE_CODE',
1674 l_msg_header.message_code) ;
1675
1676 IF (l_msg_header.message_code IS NULL) THEN
1677 x_error_code := xnp_errors.g_invalid_msg_code ;
1678 x_error_message := 'Message Code or Name Required' ;
1679 return ;
1680 END IF ;
1681
1682 xnp_message.get_sequence(l_msg_header.message_id) ;
1683 l_msg_header.direction_indr := 'I' ;
1684 xnp_xml_utils.decode(p_msg_text, 'REFERENCE_ID',
1685 l_msg_header.reference_id) ;
1686 xnp_xml_utils.decode(p_msg_text, 'OPP_REFERENCE_ID',
1687 l_msg_header.opp_reference_id) ;
1688 l_msg_header.creation_date := SYSDATE ;
1689 xnp_xml_utils.decode(p_msg_text, 'SENDER_NAME',
1690 l_msg_header.sender_name) ;
1691 xnp_xml_utils.decode(p_msg_text, 'RECIPIENT_NAME',
1692 l_msg_header.recipient_name) ;
1693 xnp_xml_utils.decode(p_msg_text, 'VERSION',
1694 l_msg_header.version) ;
1695
1696 x_msg_id := l_msg_header.message_id;
1697
1698 push( p_msg_header => l_msg_header,
1699 p_body_text => p_msg_text,
1700 p_queue_name => l_queue_name,
1701 p_correlation_id => l_msg_header.message_code,
1702 p_priority => 1,
1703 p_commit_mode => p_commit_mode,
1704 p_fe_name => p_fe_name,
1705 p_adapter_name => p_adapter_name) ;
1706 EXCEPTION
1707 WHEN OTHERS THEN
1708 x_error_code := SQLCODE ;
1709 x_error_message := SQLERRM ;
1710
1711 END push;
1712
1713 /**************************************************************************
1714 ****
1715 ****
1716 **************************************************************************/
1717
1718 PROCEDURE pop(
1719 p_queue_name VARCHAR2
1720 ,p_consumer_name VARCHAR2
1721 ,x_msg_text OUT NOCOPY VARCHAR2
1722 ,x_error_code OUT NOCOPY NUMBER
1723 ,x_error_msg OUT NOCOPY VARCHAR2
1724 ,p_timeout IN NUMBER DEFAULT 1)
1725 IS
1726
1727 l_msg_header MSG_HEADER_REC_TYPE ;
1728 l_queue_name VARCHAR2(1024) ;
1729 l_status VARCHAR2(1024) ;
1730 l_industry VARCHAR2(1024) ;
1731 l_ret BOOLEAN ;
1732
1733 BEGIN
1734
1735 x_error_code := 0;
1736 x_error_msg := 'NO_ERRORS' ;
1737
1738 POP_TIMEOUT := p_timeout;
1739
1740 /*
1741 adabholk 03/2001
1742 moved to initialization block to set g_xnp_schema
1743
1744 IF (g_schema_set = 0) THEN
1745
1746 l_ret := FND_INSTALLATION.GET_APP_INFO(
1747 application_short_name=>'XNP'
1748 ,status=>l_status
1749 ,industry=>l_industry
1750 ,oracle_schema=>g_schema
1751 );
1752
1753 g_schema_set := 1 ;
1754
1755 END IF;
1756 */
1757
1758 l_queue_name := g_xnp_schema || '.' || p_queue_name ;
1759
1760 pop(p_queue_name=>l_queue_name,
1761 x_msg_header=>l_msg_header,
1762 x_body_text=>x_msg_text,
1763 x_error_code=>x_error_code,
1764 x_error_message=>x_error_msg,
1765 p_consumer_name=>p_consumer_name ) ;
1766
1767 END pop ;
1768
1769 /**************************************************************************
1770 ****
1771 ****
1772 **************************************************************************/
1773
1774 PROCEDURE delete (
1775 p_msg_code IN VARCHAR2
1776 )
1777 IS
1778 l_error_code NUMBER := 0;
1779 l_error_message VARCHAR2(2000) := NULL;
1780 BEGIN
1781
1782 BEGIN
1783
1784 check_run_time_data(p_msg_code,
1785 l_error_code,
1786 l_error_message);
1787
1788 IF (l_error_code <> 0) THEN
1789 fnd_message.set_name('XNP','XNP_RUNTIME_DATA_EXISTS');
1790 app_exception.raise_exception;
1791 END IF;
1792
1793 DELETE FROM xnp_msg_structures
1794 WHERE msg_code = p_msg_code;
1795
1796 DELETE FROM xnp_msg_elements
1797 WHERE MSG_CODE = p_msg_code;
1798
1799 DELETE FROM xnp_msg_acks
1800 WHERE source_msg_code = p_msg_code
1801 OR ack_msg_code = p_msg_code;
1802
1803 DELETE FROM xnp_timer_publishers
1804 WHERE timer_message_code = p_msg_code
1805 OR source_message_code = p_msg_code;
1806
1807 DELETE FROM xnp_event_subscribers
1808 WHERE msg_code = p_msg_code;
1809
1810 BEGIN
1811 xnp_msg_types_pkg.delete_row(p_msg_code) ;
1812 drop_packages(p_msg_code,
1813 l_error_code,
1814 l_error_message);
1815
1816 EXCEPTION
1817 WHEN NO_DATA_FOUND THEN
1818 NULL;
1819 END;
1820 END ;
1821
1822
1823 END delete;
1824
1825 /**************************************************************************
1826 ****
1827 ****
1828 **************************************************************************/
1829
1830 PROCEDURE check_run_time_data(
1831 p_msg_code IN VARCHAR2
1832 ,x_error_code OUT NOCOPY NUMBER
1833 ,x_error_message OUT NOCOPY VARCHAR2
1834 )
1835 IS
1836 l_count NUMBER ;
1837
1838 BEGIN
1839
1840
1841 -- adabholk 03/2001
1842 -- performance
1843 -- completely re-written to avoid full table scans due to aggregate functions
1844 --
1845 -- rnyberg 04/10/2001
1846 -- added NO_DATA_FOUND handling to fix bug 2026233 CANNOT DELETE A MESSAGE
1847 --
1848
1849 BEGIN
1850 l_count := 0;
1851 SELECT 1
1852 INTO l_count
1853 FROM DUAL
1854 WHERE EXISTS
1855 (SELECT 1
1856 FROM XNP_CALLBACK_EVENTS
1857 WHERE msg_code = p_msg_code);
1858
1859 x_error_code := xnp_errors.g_run_time_data_exists ;
1860 FND_MESSAGE.set_name ('XNP', 'XNP_RUNTIME_DATA_EXISTS') ;
1861 x_error_message := FND_MESSAGE.get ;
1862 RETURN ;
1863 EXCEPTION
1864 WHEN NO_DATA_FOUND THEN
1865 NULL;
1866 END;
1867
1868 BEGIN
1869 l_count := 0;
1870
1871 SELECT 1
1872 INTO l_count
1873 FROM DUAL
1874 WHERE EXISTS
1875 (SELECT 1
1876 FROM XNP_MSGS
1877 WHERE msg_code=p_msg_code);
1878
1879 x_error_code := xnp_errors.g_run_time_data_exists ;
1880 FND_MESSAGE.set_name ('XNP', 'XNP_RUN_TIME_DATA_EXISTS') ;
1881 x_error_message := FND_MESSAGE.get ;
1882 RETURN ;
1883 EXCEPTION
1884 WHEN NO_DATA_FOUND THEN
1885 NULL;
1886 END;
1887
1888 BEGIN
1889 l_count := 0;
1890 SELECT 1
1891 INTO l_count
1892 FROM DUAL
1893 WHERE EXISTS
1894 (SELECT 1
1895 FROM XNP_TIMER_REGISTRY
1896 WHERE p_msg_code=timer_message_code);
1897
1898 x_error_code := xnp_errors.g_run_time_data_exists ;
1899 FND_MESSAGE.set_name ('XNP', 'XNP_RUN_TIME_DATA_EXISTS') ;
1900 x_error_message := FND_MESSAGE.get ;
1901 RETURN ;
1902 EXCEPTION
1903 WHEN NO_DATA_FOUND THEN
1904 NULL;
1905 END;
1906 x_error_code := 0;
1907 /*
1908 l_count := 0;
1909
1910 SELECT count(*) INTO l_count
1911 FROM XNP_CALLBACK_EVENTS
1912 WHERE msg_code=p_msg_code;
1913
1914 IF (l_count <> 0) THEN
1915 x_error_code := xnp_errors.g_run_time_data_exists ;
1916 FND_MESSAGE.set_name ('XNP', 'XNP_RUNTIME_DATA_EXISTS') ;
1917 x_error_message := FND_MESSAGE.get ;
1918 RETURN ;
1919 END IF;
1920
1921 SELECT count(*) INTO l_count
1922 FROM XNP_MSGS
1923 WHERE msg_code=p_msg_code;
1924
1925 IF (l_count <> 0) THEN
1926 x_error_code := xnp_errors.g_run_time_data_exists ;
1927 FND_MESSAGE.set_name ('XNP', 'XNP_RUN_TIME_DATA_EXISTS') ;
1928 x_error_message := FND_MESSAGE.get ;
1929 RETURN ;
1930 END IF;
1931
1932 SELECT count(*) INTO l_count
1933 FROM XNP_TIMER_REGISTRY
1934 WHERE p_msg_code=timer_message_code;
1935
1936 IF (l_count <> 0) THEN
1937 x_error_code := xnp_errors.g_run_time_data_exists ;
1938 FND_MESSAGE.set_name ('XNP', 'RUN_TIME_DATA_EXISTS') ;
1939 x_error_message := FND_MESSAGE.get ;
1940 RETURN ;
1941 END IF;
1942 */
1943
1944 END check_run_time_data ;
1945
1946 /**************************************************************************
1947 ****
1948 ****
1949 **************************************************************************/
1950
1951 PROCEDURE drop_packages(
1952 p_msg_code IN VARCHAR2
1953 ,x_error_code OUT NOCOPY NUMBER
1954 ,x_error_message OUT NOCOPY VARCHAR2
1955 )
1956 IS
1957 l_sql_text VARCHAR2(16000) ;
1958 l_schema VARCHAR2(1024) ;
1959 l_status VARCHAR2(1024) ;
1960 l_industry VARCHAR2(1024) ;
1961 l_ret BOOLEAN ;
1962
1963
1964 BEGIN
1965
1966
1967 /*
1968 adabholk 03/2001
1969 moved to initialization block to set g_fnd_schema
1970
1971 l_ret := FND_INSTALLATION.GET_APP_INFO(
1972 application_short_name=>'FND'
1973 ,status=>l_status
1974 ,industry=>l_industry
1975 ,oracle_schema=>l_schema
1976 );
1977 */
1978
1979 l_sql_text := 'DROP PACKAGE ' || g_pkg_prefix ||
1980 p_msg_code || g_pkg_suffix ;
1981
1982 -- 04/10/2001. rnyberg added exception to handle that package might
1983 -- not exist to fix bug 2026233
1984 DECLARE
1985 e_object_does_not_exist EXCEPTION;
1986 PRAGMA EXCEPTION_INIT(e_object_does_not_exist, -4043);
1987 BEGIN
1988 AD_DDL.DO_DDL(
1989 applsys_schema=>g_fnd_schema
1990 ,application_short_name=>'XNP'
1991 ,statement_type=>ad_ddl.drop_package
1992 ,statement=>l_sql_text
1993 ,object_name=>g_pkg_prefix ||p_msg_code|| g_pkg_suffix
1994 );
1995 EXCEPTION
1996 WHEN e_object_does_not_exist THEN
1997 NULL;
1998 END;
1999
2000
2001
2002
2003 END drop_packages ;
2004
2005
2006 /***************************************************************************
2007 ***** Procedure: PUSH_CLOB()
2008 ***** Purpose: Enqueues the message and message body on a specified Queue .
2009 ****************************************************************************/
2010
2011 PROCEDURE PUSH_WF(
2012 p_msg_header IN msg_header_rec_type ,
2013 p_body_text IN VARCHAR2 ,
2014 p_queue_name IN VARCHAR2 ,
2015 p_correlation_id IN VARCHAR2 DEFAULT NULL ,
2016 p_priority IN INTEGER DEFAULT 1 ,
2017 p_commit_mode IN NUMBER DEFAULT c_on_commit ,
2018 p_delay IN NUMBER DEFAULT DBMS_AQ.NO_DELAY ) IS
2019
2020 l_event WF_EVENT_T ;
2021 l_event_key VARCHAR2(30) := '1';
2022 my_enqueue_options dbms_aq.enqueue_options_t ;
2023 message_properties dbms_aq.message_properties_t ;
2024 message_handle RAW(16) ;
2025 recipients dbms_aq.aq$_recipient_list_t ;
2026
2027 l_recipient_name VARCHAR2(80) ;
2028 l_recipient_count INTEGER ;
2029
2030 l_lob_loc CLOB ;
2031 l_correlation_id VARCHAR2(1024) ;
2032
2033 l_msg_header xnp_message.msg_header_rec_type ;
2034 x_error_message varchar2(4000);
2035 x_error_code number;
2036
2037 BEGIN
2038
2039 l_msg_header := p_msg_header ;
2040 l_correlation_id := l_msg_header.message_code ;
2041 message_properties.priority := p_priority;
2042
2043 IF ( l_msg_header.message_id IS NULL ) THEN
2044 XNP_MESSAGE.get_sequence(l_msg_header.message_id) ;
2045 END IF ;
2046
2047 -- Use the correlation ID if one is specified
2048
2049 IF ( l_correlation_id is NOT NULL ) THEN
2050 message_properties.correlation := l_correlation_id ;
2051 END IF ;
2052
2053 IF (p_commit_mode = C_IMMEDIATE) THEN
2054 my_enqueue_options.visibility := DBMS_AQ.IMMEDIATE ;
2055 ELSE
2056 my_enqueue_options.visibility := DBMS_AQ.ON_COMMIT ;
2057 END IF ;
2058
2059 XDP_UTILITIES.WRITE_TABLE_TO_CLOB(p_source_table => xdp_utilities.g_message_list,
2060 p_dest_clob => xdp_utilities.g_clob,
2061 x_error_code => x_error_code ,
2062 x_error_description => x_error_message );
2063 XNP_XML_UTILS.INITIALIZE_DOC;
2064
2065 WF_EVENT_T.INITIALIZE(l_event);
2066
2067 l_event.PRIORITY := p_priority;
2068 l_event.SEND_DATE := sysdate ;
2069 l_event.CORRELATION_ID := l_correlation_id ;
2070 l_event.EVENT_NAME := l_msg_header.message_code;
2071 l_event.EVENT_KEY := l_event_key ;
2072 l_event.EVENT_DATA := xdp_utilities.g_clob ;
2073
2074 DBMS_AQ.ENQUEUE (
2075 queue_name => p_queue_name ,
2076 enqueue_options => my_enqueue_options,
2077 message_properties => message_properties,
2078 payload => l_event,
2079 msgid => message_handle ) ;
2080
2081 DBMS_LOB.FREETEMPORARY(XDP_UTILITIES.G_CLOB);
2082
2083 END PUSH_WF;
2084 --Package initialization code
2085
2086 BEGIN
2087
2088 DECLARE
2089 l_pop_timeout VARCHAR2(40) := NULL;
2090 l_ret BOOLEAN;
2091 l_status VARCHAR2(1024) ;
2092 l_industry VARCHAR2(1024) ;
2093 BEGIN
2094 FND_PROFILE.GET( NAME => 'POP_TIMEOUT',
2095 VAL => l_pop_timeout ) ;
2096 IF (l_pop_timeout IS NULL) THEN
2097 POP_TIMEOUT := 5 ;
2098 ELSE
2099 POP_TIMEOUT := TO_NUMBER(l_pop_timeout) ;
2100 END IF ;
2101
2102 l_ret := FND_INSTALLATION.GET_APP_INFO(
2103 application_short_name=>'FND'
2104 ,status=>l_status
2105 ,industry=>l_industry
2106 ,oracle_schema=>g_fnd_schema
2107 );
2108 l_ret := FND_INSTALLATION.GET_APP_INFO(
2109 application_short_name=>'XNP'
2110 ,status=>l_status
2111 ,industry=>l_industry
2112 ,oracle_schema=>g_xnp_schema
2113 );
2114 END ;
2115
2116 -- Get APPS_MAINTENANCE_MODE parameter for High Availability
2117 FND_PROFILE.GET('APPS_MAINTENANCE_MODE', g_APPS_MAINTENANCE_MODE);
2118
2119 IF(g_APPS_MAINTENANCE_MODE = 'MAINT') THEN
2120 /**** Set Log and Output File Names and Directory ****/
2121
2122 SELECT nvl(substr(value,1,instr(value,',')-1),value)
2123 INTO g_logdir
2124 FROM v$parameter
2125 WHERE name = 'utl_file_dir';
2126
2127 select sysdate into g_logdate from dual;
2128
2129 fnd_file.put_names('XNPMSGPB'||to_char(g_logdate, 'YYYYMMDDHHMISS')||'.log',
2130 'XNPMSGPB'||to_char(g_logdate, 'YYYYMMDDHHMISS')||'.out',
2131 g_logdir);
2132 END IF;
2133
2134 END xnp_message;
2135