[Home] [Help]
PACKAGE BODY: APPS.XNP_MSG_BUILDER
Source
1 PACKAGE BODY XNP_MSG_BUILDER AS
2 /* $Header: XNPMBLPB.pls 120.1 2005/06/18 00:23:09 appldev $ */
3
4 g_message_code VARCHAR2(40) ;
5 g_excep_section VARCHAR2(16000) ;
6 g_decl_section VARCHAR2(16000);
7 g_create_body VARCHAR2(32767);
8 g_mandatory_check BOOLEAN := FALSE ;
9 g_mandatory_list VARCHAR2(4000) ;
10 g_loop_counter NUMBER := 1 ;
11
12 g_event_indr XNP_MSG_TYPES_B.msg_type%TYPE ;
13 g_dtd_url XNP_MSG_TYPES_B.dtd_url%TYPE ;
14 g_msg_priority XNP_MSG_TYPES_B.priority%TYPE ;
15 g_validate_logic XNP_MSG_TYPES_B.validate_logic%TYPE ;
16 g_process_logic XNP_MSG_TYPES_B.in_process_logic%TYPE ;
17 g_out_process_logic XNP_MSG_TYPES_B.out_process_logic%TYPE ;
18 g_dflt_process_logic XNP_MSG_TYPES_B.default_process_logic%TYPE ;
19 g_queue_name XNP_MSG_TYPES_B.queue_name%TYPE ;
20 g_temp_tab XDP_TYPES.VARCHAR2_4000_TAB ;
21
22 CURSOR get_msg_type_data IS
23 SELECT msg_type,
24 priority,
25 in_process_logic,
26 out_process_logic,
27 default_process_logic,
28 validate_logic,
29 queue_name,
30 dtd_url
31 FROM XNP_MSG_TYPES_B
32 WHERE msg_code = g_message_code ;
33
34 CURSOR get_compilation_errors ( l_proc_name VARCHAR2 )IS
35 SELECT text FROM user_errors
36 WHERE name = UPPER( l_proc_name ) ;
37
38 CURSOR get_parameter_data IS
39 SELECT name,
40 element_datatype,
41 mandatory_flag,
42 NVL ( element_default_value, 'NP_NULL' ) element_default_value ,
43 parameter_sequence
44 FROM xnp_msg_elements
45 WHERE msg_code = g_message_code
46 AND parameter_flag = 'Y'
47 ORDER BY parameter_sequence ;
48
49
50 g_white_space CONSTANT CHAR := ' ' ;
51
52 g_new_line CONSTANT VARCHAR2(10) := convert(fnd_global.local_chr(10),
53 substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
54 'WE8ISO8859P1') ;
55
56 g_comma CONSTANT CHAR := ',' ;
57 g_delimeter CONSTANT CHAR := '%' ;
58 g_np_prefix CONSTANT VARCHAR2(10) := 'XNP$' ;
59
60 -- forward declarations
61
62 PROCEDURE cr_start_body(
63 x_start_body OUT NOCOPY VARCHAR2
64 );
65
66
67 PROCEDURE cr_start_signature(
68 x_start_sig OUT NOCOPY VARCHAR2
69 );
70
71 PROCEDURE cr_dflt_process_body(
72 x_dflt_process OUT NOCOPY VARCHAR2
73 );
74
75 PROCEDURE cr_process_body(
76 x_process_body OUT NOCOPY VARCHAR2
77 );
78
79 PROCEDURE cr_process_signature(
80 x_process_sig OUT NOCOPY VARCHAR2
81 );
82
83 PROCEDURE cr_dflt_process_sig(
84 x_dflt_process_sig OUT NOCOPY VARCHAR2
85 );
86
87 PROCEDURE cr_validate_signature(
88 x_validate_sig OUT NOCOPY VARCHAR2
89 );
90
91 PROCEDURE compile_spec(
92 p_pkg_name IN VARCHAR2
93 ,x_error_code OUT NOCOPY NUMBER
94 ,x_error_message OUT NOCOPY VARCHAR2
95 );
96
97 PROCEDURE compile_body(
98 p_pkg_name IN VARCHAR2
99 ,x_error_code OUT NOCOPY NUMBER
100 ,x_error_message OUT NOCOPY VARCHAR2
101 );
102
103 PROCEDURE cr_pkg_body(
104 x_error_code OUT NOCOPY NUMBER
105 ,x_error_message OUT NOCOPY VARCHAR2
106 ,x_package_body OUT NOCOPY VARCHAR2
107 );
108
109 PROCEDURE cr_pkg_spec(
110 x_error_code OUT NOCOPY NUMBER
111 ,x_error_message OUT NOCOPY VARCHAR2
112 ,x_package_spec OUT NOCOPY VARCHAR2
113 ) ;
114
115 PROCEDURE cr_publish_body(
116 x_publish_body OUT NOCOPY VARCHAR2
117 );
118
119 PROCEDURE cr_create_body(
120 x_create_body OUT NOCOPY VARCHAR2
121 );
122
123 PROCEDURE cr_publish_signature(
124 x_publish_sig OUT NOCOPY VARCHAR2
125 );
126
127 PROCEDURE cr_create_signature(
128 x_create_sig OUT NOCOPY VARCHAR2
129 ) ;
130
131 PROCEDURE cr_send_signature(
132 x_send_sig OUT NOCOPY VARCHAR2
133 );
134
135 PROCEDURE cr_send_body(
136 x_send_body OUT NOCOPY VARCHAR2
137 );
138
139 PROCEDURE generate_create_body(
140 p_element IN VARCHAR2
141 ,p_element_type IN VARCHAR2
142 ,p_mandatory_flag IN VARCHAR2
143 ,p_source_type IN VARCHAR2
144 ,p_data_source IN VARCHAR2
145 ,p_source_ref IN VARCHAR2
146 ,p_cardinality IN VARCHAR2
147 ,p_parameter_flag IN VARCHAR2
148 );
149
150 PROCEDURE bld_msgevt(
151 x_error_code OUT NOCOPY NUMBER
152 ,x_error_message OUT NOCOPY VARCHAR2
153 ,x_package_spec OUT NOCOPY VARCHAR2
154 ,x_package_body OUT NOCOPY VARCHAR2
155 ,x_synonym OUT NOCOPY VARCHAR2
156 ) ;
157
158 ----------------------------------------------------------------------------
159 --Copy Message Definition---------------------------------------------------
160 PROCEDURE Create_msg_type(p_old_msg_code IN VARCHAR2,
161 p_new_msg_code IN VARCHAR2,
162 p_new_disp_name IN VARCHAR2,
163 p_return_code OUT NOCOPY NUMBER,
164 p_error_description OUT NOCOPY VARCHAR2);
165
166 PROCEDURE Create_msg_element(p_old_msg_code IN VARCHAR2,
167 p_new_msg_code IN VARCHAR2,
168 p_return_code OUT NOCOPY NUMBER,
169 p_error_description OUT NOCOPY VARCHAR2);
170
171 PROCEDURE COPY_MESG_STR(p_old_msg_code IN VARCHAR2,
172 p_new_msg_code IN VARCHAR2,
173 p_element_name IN VARCHAR2,
174 p_element_id IN NUMBER,
175 p_return_code OUT NOCOPY NUMBER,
176 p_error_description OUT NOCOPY VARCHAR2) ;
177
178 PROCEDURE CREATE_MSG_STR(P_PARENT_ELEMENT_ID IN NUMBER,
179 P_CHILD_ELEMENT_ID IN NUMBER,
180 P_MSG_CODE IN VARCHAR2,
181 P_SEQUENCE_IN_PARENT IN NUMBER ,
182 P_CARDINALITY IN VARCHAR2,
183 P_DATA_SOURCE IN VARCHAR2,
184 P_DATA_SOURCE_TYPE IN VARCHAR2,
185 P_DATA_SOURCE_REFERENCE IN VARCHAR2,
186 P_ELEMENT_POSITION IN NUMBER,
187 P_ELEMENT_ALIGNMENT IN VARCHAR2,
188 P_PADDING IN VARCHAR2,
189 p_return_code OUT NOCOPY NUMBER,
190 p_error_description OUT NOCOPY VARCHAR2) ;
191
192
193
194 FUNCTION get_new_child_element_id (p_new_msg_code VARCHAR2,
195 p_child_element_name VARCHAR2
196 ) RETURN NUMBER ;
197
198 FUNCTION get_new_parent_element_id (p_new_msg_code VARCHAR2,
199 p_element_name VARCHAR2) RETURN NUMBER ;
200
201 ---------------------------------------------------------------------------------
202 ---Copy Message definition----------------------------------------------------------
203
204
205 FUNCTION get_new_child_element_id
206 (p_new_msg_code VARCHAR2,
207 p_child_element_name VARCHAR2) RETURN NUMBER IS
208
209 lv_element_id NUMBER ;
210
211 BEGIN
212 SELECT msg_element_id
213 INTO lv_element_id
214 FROM xnp_msg_elements
215 WHERE name = p_child_element_name
216 AND msg_code = p_new_msg_code;
217
218 RETURN lv_element_id ;
219
220 EXCEPTION
221
222 WHEN OTHERS THEN
223 ROLLBACK TO Mesg;
224 RAISE;
225 END get_new_child_element_id;
226
227
228
229 FUNCTION get_new_parent_element_id
230 (p_new_msg_code VARCHAR2,
231 p_element_name VARCHAR2) RETURN NUMBER IS
232
233 lv_element_id NUMBER ;
234
235 BEGIN
236
237 SELECT msg_element_id
238 INTO lv_element_id
239 FROM xnp_msg_elements
240 WHERE name = p_element_name
241 AND msg_code = p_new_msg_code;
242
243 RETURN lv_element_id ;
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 ROLLBACK TO Mesg;
248 RAISE;
249 END get_new_parent_element_id;
250
251
252 PROCEDURE CopyMesg(
253 p_old_msg_code IN VARCHAR2,
254 p_new_msg_code IN VARCHAR2,
255 p_new_disp_name IN VARCHAR2,
256 p_return_code OUT NOCOPY NUMBER,
257 p_error_description OUT NOCOPY VARCHAR2)
258
259 IS
260 l_return_code NUMBER :=0;
261 l_error_description VARCHAR2(32767);
262
263 BEGIN
264 SAVEPOINT Mesg;
265 p_return_code :=0;
266 Create_msg_type(p_old_msg_code => CopyMesg.p_old_msg_code,
267 p_new_msg_code => CopyMesg.p_new_msg_code,
268 p_new_disp_name => CopyMesg.p_new_disp_name,
269 p_return_code => l_return_code ,
270 p_error_description => l_error_description );
271
272 Create_msg_element(p_old_msg_code => CopyMesg.p_old_msg_code,
273 p_new_msg_code => CopyMesg.p_new_msg_code,
274 p_return_code => l_return_code ,
275 p_error_description => l_error_description );
276 EXCEPTION WHEN OTHERS
277 THEN
278 ROLLBACK to Mesg;
279 p_return_code := l_return_code ;
280 p_error_description := l_error_description ;
281
282 END CopyMesg;
283
284 PROCEDURE Create_msg_type(p_old_msg_code IN VARCHAR2,
285 p_new_msg_code IN VARCHAR2,
286 p_new_disp_name IN VARCHAR2,
287 p_return_code OUT NOCOPY NUMBER,
288 p_error_description OUT NOCOPY VARCHAR2)
289
290 IS
291 l_rowid rowid;
292 lv_element_name VARCHAR2(3000);
293
294
295 CURSOR c_mesg_type IS
296 SELECT MSG_TYPE,
297 STATUS,
298 PRIORITY,
299 QUEUE_NAME,
300 PROTECTED_FLAG,
301 ROLE_NAME,
302 LAST_COMPILED_DATE,
303 VALIDATE_LOGIC,
304 IN_PROCESS_LOGIC,
305 OUT_PROCESS_LOGIC,
306 DEFAULT_PROCESS_LOGIC,
307 DTD_URL,
308 DISPLAY_NAME,
309 DESCRIPTION
310 FROM XNP_MSG_TYPES_VL
311 WHERE MSG_CODE = p_old_msg_code;
312
313
314
315
316 BEGIN
317 -- Insert into Mesg_types_b,mesg_types_tl table
318 --
319 p_return_code:=0;
320 FOR c_mesg_rec IN c_mesg_type
321 LOOP
322 XNP_MSG_TYPES_PKG.INSERT_ROW
323 (
324 X_ROWID => l_rowid,
325 X_MSG_CODE => p_new_msg_code,
326 X_MSG_TYPE => c_mesg_rec.msg_type,
327 X_STATUS => 'UNCOMPILED',
328 X_PRIORITY => c_mesg_rec.PRIORITY,
329 X_QUEUE_NAME => c_mesg_rec.QUEUE_NAME,
330 X_PROTECTED_FLAG => c_mesg_rec.PROTECTED_FLAG,
331 X_ROLE_NAME => c_mesg_rec.ROLE_NAME,
332 X_LAST_COMPILED_DATE => sysdate,
333 X_VALIDATE_LOGIC => c_mesg_rec.VALIDATE_LOGIC ,
334 X_IN_PROCESS_LOGIC => c_mesg_rec.IN_PROCESS_LOGIC ,
335 X_OUT_PROCESS_LOGIC => c_mesg_rec.OUT_PROCESS_LOGIC,
336 X_DEFAULT_PROCESS_LOGIC => c_mesg_rec.DEFAULT_PROCESS_LOGIC,
337 X_DTD_URL => c_mesg_rec.DTD_URL,
338 X_DISPLAY_NAME => p_new_disp_name ,--c_mesg_rec.DISPLAY_NAME,
339 X_DESCRIPTION => c_mesg_rec.DESCRIPTION,
340 X_CREATION_DATE => SYSDATE,
341 X_CREATED_BY => FND_GLOBAL.USER_ID,
342 X_LAST_UPDATE_DATE => SYSDATE,
343 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
344 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
345 END LOOP;
346 EXCEPTION
347 WHEN OTHERS THEN
348 p_return_code:=SQLCODE;
349 p_error_description:=SQLERRM;
350 END Create_msg_type;
351
352 PROCEDURE Create_msg_element(p_old_msg_code IN VARCHAR2,
353 p_new_msg_code IN VARCHAR2,
354 p_return_code OUT NOCOPY NUMBER,
355 p_error_description OUT NOCOPY VARCHAR2)
356 IS
357 l_return_code NUMBER:=0;
358 l_error_description VARCHAR2(2000);
359 l_new_parent_element_id NUMBER ;
360 l_new_child_element_id NUMBER ;
361 lv_element_name VARCHAR2(40);
362
363 CURSOR c_msg_element IS
364 SELECT NAME,
365 MANDATORY_FLAG,
366 PARAMETER_FLAG,
367 ELEMENT_DATATYPE,
368 ELEMENT_DEFAULT_VALUE,
369 DATA_LENGTH,
370 PARAMETER_SEQUENCE,
371 MSG_ELEMENT_ID --- Added for msg_structure
372
373 FROM XNP_MSG_ELEMENTS
374 WHERE MSG_CODE= p_old_msg_code;
375
376 CURSOR c_old_master is
377 select str.SEQUENCE_IN_PARENT,
378 str.CARDINALITY,
379 str.DATA_SOURCE,
380 str.DATA_SOURCE_TYPE,
381 str.DATA_SOURCE_REFERENCE,
382 str.ELEMENT_POSITION,
383 str.ELEMENT_ALIGNMENT,
384 str.PADDING
385 from xnp_msg_structures str,
386 xnp_msg_elements elm
387 where str.parent_element_id = elm.msg_element_id
388 AND elm.msg_code=p_old_msg_code
389 AND elm.NAME='MESSAGE' ;
390
391 BEGIN
392 p_return_code:=0;
393 For c_msg_element_rec IN c_msg_element
394 LOOP
395 IF c_msg_element_rec.NAME = p_old_msg_code THEN
396 lv_element_name :=p_new_msg_code;
397 ELSE
398 lv_element_name :=c_msg_element_rec.NAME;
399 END IF;
400
401 INSERT INTO XNP_MSG_ELEMENTS
402 (
403 MSG_ELEMENT_ID ,
404 MSG_CODE ,
405 NAME ,
406 MANDATORY_FLAG ,
407 PARAMETER_FLAG ,
408 ELEMENT_DATATYPE ,
409 ELEMENT_DEFAULT_VALUE ,
410 DATA_LENGTH ,
411 PARAMETER_SEQUENCE ,
412 CREATED_BY ,
413 CREATION_DATE ,
414 LAST_UPDATE_DATE ,
415 LAST_UPDATED_BY ,
416 LAST_UPDATE_LOGIN )
417 VALUES(
418 XNP_MSG_ELEMENTS_S.NEXTVAL,
419 p_new_msg_code,
420 lv_element_name,
421 c_msg_element_rec.MANDATORY_FLAG,
422 c_msg_element_rec.PARAMETER_FLAG,
423 c_msg_element_rec.ELEMENT_DATATYPE,
424 c_msg_element_rec.ELEMENT_DEFAULT_VALUE,
425 c_msg_element_rec.DATA_LENGTH,
426 c_msg_element_rec.PARAMETER_SEQUENCE,
427 FND_GLOBAL.USER_ID,
428 SYSDATE,
429 SYSDATE,
430 FND_GLOBAL.USER_ID,
431 FND_GLOBAL.LOGIN_ID
432 );
433 END LOOP;
434 ---Create a structure for "MESSAGE" element and element named as new message code
435
436 FOR c_old_master_rec IN c_old_master
437 LOOP
438 l_new_child_element_id := get_new_child_element_id (p_new_msg_code=> p_new_msg_code,
439 p_child_element_name => p_new_msg_code) ;
440
441 l_new_parent_element_id := get_new_parent_element_id (p_new_msg_code,
442 'MESSAGE') ;
443
444 CREATE_MSG_STR(P_PARENT_ELEMENT_ID => l_new_parent_element_id ,
445 P_CHILD_ELEMENT_ID => l_new_child_element_id ,
446 P_MSG_CODE => p_new_msg_code ,
447 P_SEQUENCE_IN_PARENT => c_old_master_rec.SEQUENCE_IN_PARENT ,
448 P_CARDINALITY => c_old_master_rec.CARDINALITY ,
449 P_DATA_SOURCE => c_old_master_rec.DATA_SOURCE ,
450 P_DATA_SOURCE_TYPE => c_old_master_rec.DATA_SOURCE_TYPE ,
451 P_DATA_SOURCE_REFERENCE => c_old_master_rec.DATA_SOURCE_REFERENCE ,
452 P_ELEMENT_POSITION => c_old_master_rec.ELEMENT_POSITION ,
453 P_ELEMENT_ALIGNMENT => c_old_master_rec.ELEMENT_ALIGNMENT ,
454 P_PADDING => c_old_master_rec.PADDING ,
455 P_RETURN_CODE => l_return_code,
456 P_ERROR_DESCRIPTION => l_error_description);
457 END LOOP;
458
459
460 ---Create a structures for all other child elements for the message
461
462 l_new_child_element_id := get_new_child_element_id
463 (p_new_msg_code=> p_old_msg_code,
464 p_child_element_name => p_old_msg_code);
465
466 COPY_MESG_STR(p_old_msg_code => p_old_msg_code,
467 p_new_msg_code => p_new_msg_code,
468 p_element_name => p_new_msg_code,
469 p_element_id => l_new_child_element_id,
470 p_return_code => l_return_code,
471 p_error_description =>l_error_description);
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 p_return_code:=SQLCODE;
476 p_error_description:=SQLERRM;
477
478 END Create_msg_element;
479
480
481
482
483 PROCEDURE COPY_MESG_STR(p_old_msg_code IN VARCHAR2,
484 p_new_msg_code IN VARCHAR2,
485 p_element_name IN VARCHAR2,
486 p_element_id IN NUMBER,
487 p_return_code OUT NOCOPY NUMBER,
488 p_error_description OUT NOCOPY VARCHAR2)
489
490 IS
491 l_new_parent_element_id NUMBER ;
492 l_new_child_element_id NUMBER ;
493 l_return_code NUMBER:=0;
494 l_error_description VARCHAR2(4000);
495
496 -- this cursor is reqd to fetch the detail of 'Message' and
497 -- old_message_code in msg_structures.
498
499
500 Cursor c_old_child is
501 SELECT str.child_element_id,
502 str.SEQUENCE_IN_PARENT,
503 str.CARDINALITY,
504 str.DATA_SOURCE,
505 str.DATA_SOURCE_TYPE,
506 str.DATA_SOURCE_REFERENCE,
507 str.ELEMENT_POSITION,
508 str.ELEMENT_ALIGNMENT,
509 str.PADDING,
510 elm.NAME
511 FROM xnp_msg_structures str,
512 xnp_msg_elements elm
513 WHERE str.msg_code = p_old_msg_code
514 AND str.parent_element_id = p_element_id
515 AND str.child_element_id = elm.msg_element_id;
516
517 BEGIN
518 p_return_code:=0;
519 for c_old_child_rec in c_old_child
520 LOOP
521
522
523 if c_old_child_rec.NAME = COPY_MESG_STR.p_old_msg_code then
524 l_new_child_element_id := get_new_child_element_id(p_new_msg_code=>COPY_MESG_STR.p_new_msg_code,
525 p_child_element_name => COPY_MESG_STR.p_new_msg_code);
526 else
527 l_new_child_element_id := get_new_child_element_id(p_new_msg_code=>COPY_MESG_STR.p_new_msg_code,
528 p_child_element_name => c_old_child_rec.name);
529 end if ;
530
531 l_new_parent_element_id := get_new_parent_element_id(p_new_msg_code => COPY_MESG_STR.p_new_msg_code,
532 p_element_name => COPY_MESG_STR.p_element_name);
533
534 CREATE_MSG_STR(P_PARENT_ELEMENT_ID => l_new_parent_element_id ,
535 P_CHILD_ELEMENT_ID => l_new_child_element_id ,
536 P_MSG_CODE => p_new_msg_code ,
537 P_SEQUENCE_IN_PARENT => c_old_child_rec.SEQUENCE_IN_PARENT ,
538 P_CARDINALITY => c_old_child_rec.CARDINALITY ,
539 P_DATA_SOURCE => c_old_child_rec.DATA_SOURCE ,
540 P_DATA_SOURCE_TYPE => c_old_child_rec.DATA_SOURCE_TYPE ,
541 P_DATA_SOURCE_REFERENCE => c_old_child_rec.DATA_SOURCE_REFERENCE ,
542 P_ELEMENT_POSITION => c_old_child_rec.ELEMENT_POSITION ,
543 P_ELEMENT_ALIGNMENT => c_old_child_rec.ELEMENT_ALIGNMENT ,
544 P_PADDING => c_old_child_rec.PADDING,
545 P_RETURN_CODE =>l_return_code,
546 P_ERROR_DESCRIPTION =>l_error_description );
547
548
549 COPY_MESG_STR(p_old_msg_code => p_old_msg_code ,
550 p_new_msg_code => p_new_msg_code,
551 p_element_name => c_old_child_rec.name,
552 p_element_id => c_old_child_rec.child_element_id,
553 p_return_code => l_return_code,
554 p_error_description =>l_error_description);
555
556 END LOOP;
557 EXCEPTION
558 WHEN OTHERS THEN
559 p_return_code:=SQLCODE;
560 p_error_description:=SQLERRM;
561 END COPY_MESG_STR ;
562
563
564 /******** PROCEDURE CREATE_MSG_STR **************/
565
566
567 PROCEDURE CREATE_MSG_STR(P_PARENT_ELEMENT_ID IN NUMBER,
568 P_CHILD_ELEMENT_ID IN NUMBER,
569 P_MSG_CODE IN VARCHAR2,
570 P_SEQUENCE_IN_PARENT IN NUMBER ,
571 P_CARDINALITY IN VARCHAR2,
572 P_DATA_SOURCE IN VARCHAR2,
573 P_DATA_SOURCE_TYPE IN VARCHAR2,
574 P_DATA_SOURCE_REFERENCE IN VARCHAR2,
575 P_ELEMENT_POSITION IN NUMBER,
576 P_ELEMENT_ALIGNMENT IN VARCHAR2,
577 P_PADDING IN VARCHAR2,
578 P_RETURN_CODE OUT NOCOPY NUMBER,
579 P_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
580 IS
581
582 BEGIN
583
584 p_return_code:=0;
585 INSERT INTO XNP_MSG_STRUCTURES
586 (STRUCTURE_ID ,
587 PARENT_ELEMENT_ID ,
588 CHILD_ELEMENT_ID ,
589 MSG_CODE ,
590 SEQUENCE_IN_PARENT ,
591 CARDINALITY ,
592 DATA_SOURCE ,
593 DATA_SOURCE_TYPE ,
594 DATA_SOURCE_REFERENCE ,
595 ELEMENT_POSITION ,
596 ELEMENT_ALIGNMENT ,
597 PADDING ,
598 CREATED_BY ,
599 CREATION_DATE ,
600 LAST_UPDATED_BY ,
601 LAST_UPDATE_DATE ,
602 LAST_UPDATE_LOGIN )
603 VALUES( XNP_MSG_STRUCTURES_S.NEXTVAL,
604 P_PARENT_ELEMENT_ID ,
605 P_CHILD_ELEMENT_ID ,
606 P_MSG_CODE ,
607 P_SEQUENCE_IN_PARENT ,
608 P_CARDINALITY ,
609 P_DATA_SOURCE ,
610 P_DATA_SOURCE_TYPE ,
611 P_DATA_SOURCE_REFERENCE ,
612 P_ELEMENT_POSITION ,
613 P_ELEMENT_ALIGNMENT ,
614 P_PADDING ,
615 FND_GLOBAL.USER_ID ,
616 SYSDATE ,
617 FND_GLOBAL.USER_ID ,
618 SYSDATE ,
619 FND_GLOBAL.LOGIN_ID );
620
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 p_return_code:=SQLCODE;
625 p_error_description:=SQLERRM;
626
627 END CREATE_MSG_STR;
628
629 ------------------------------------------------------------------------------
630
631 ----------------------------------------------------------------------------
632 ----------------------------------------------------------------------------
633 ----- Procedure: CR_DFLT_PROCESS_BODY()
634 ----- Purpose: generates code for procedure DEFAULT_PROCESS()
635 ----------------------------------------------------------------------------
636 ----------------------------------------------------------------------------
637
638 PROCEDURE cr_dflt_process_body(
639 x_dflt_process OUT NOCOPY VARCHAR2
640 )
641 IS
642 l_default_sig VARCHAR2(16000) ;
643
644 BEGIN
645
646 cr_dflt_process_sig ( l_default_sig ) ;
647
648 l_default_sig := l_default_sig || ' IS' || g_new_line ;
649 x_dflt_process := 'BEGIN' || g_new_line || g_new_line ;
650 x_dflt_process := x_dflt_process || 'NULL ;' || g_new_line
651 || g_new_line ;
652 x_dflt_process := x_dflt_process || g_dflt_process_logic
653 || g_new_line || g_new_line ;
654 x_dflt_process := x_dflt_process || 'END ;' ;
655 x_dflt_process := l_default_sig || x_dflt_process ;
656
657 END cr_dflt_process_body;
658
659 ----------------------------------------------------------------------------
660 ----------------------------------------------------------------------------
661 ----- Procedure: CR_PROCESS_BODY()
662 ----- Purpose: generates code for procedure PROCESS()
663 ----------------------------------------------------------------------------
664 ----------------------------------------------------------------------------
665
666 PROCEDURE cr_process_body(
667 x_process_body OUT NOCOPY VARCHAR2
668 )
669 IS
670 l_process_sig VARCHAR2(16000) ;
671
672 BEGIN
673
674 cr_process_signature ( l_process_sig ) ;
675
676 l_process_sig := l_process_sig || ' IS' || g_new_line ;
677 x_process_body := 'BEGIN' || g_new_line || g_new_line ;
678 x_process_body := x_process_body || 'NULL ;' || g_new_line
679 || g_new_line ;
680 x_process_body := x_process_body || g_process_logic
681 || g_new_line || g_new_line ;
682 x_process_body := x_process_body || 'END ;'||g_new_line ;
683 x_process_body := l_process_sig || x_process_body ;
684
685 END cr_process_body;
686
687 ---------------------------------------------------------------------------
688 ---------------------------------------------------------------------------
689 ----- Procedure: CR_VALIDATE_BODY()
690 ----- Purpose: generates code for procedure VALIDATE()
691 ---------------------------------------------------------------------------
692 ---------------------------------------------------------------------------
693
694 PROCEDURE cr_validate_body(
695 x_validate_body OUT NOCOPY VARCHAR2
696 )
697 IS
698
699 l_validate_sig VARCHAR2(16000) ;
700
701 BEGIN
702
703 cr_validate_signature ( l_validate_sig ) ;
704
705 l_validate_sig := l_validate_sig || ' IS' || g_new_line ;
706 x_validate_body := 'BEGIN' || g_new_line || g_new_line ;
707 x_validate_body := x_validate_body || 'NULL ;' || g_new_line
708 || g_new_line ;
709 x_validate_body := x_validate_body || g_validate_logic
710 || g_new_line || g_new_line ;
711 x_validate_body := x_validate_body || 'END ;'||g_new_line ;
712 x_validate_body := l_validate_sig || x_validate_body ;
713
714 END cr_validate_body;
715
716 ----------------------------------------------------------------------------
717 ----------------------------------------------------------------------------
718 ----- Procedure: CR_PROCESS_SIGNATURE()
719 ----- Purpose: Creates the signature for procedure PROCESS().
720 ----------------------------------------------------------------------------
721 ----------------------------------------------------------------------------
722
723 PROCEDURE cr_process_signature(
724 x_process_sig OUT NOCOPY VARCHAR2
725 )
726 IS
727 BEGIN
728
729 x_process_sig := x_process_sig || 'PROCEDURE PROCESS ( '
730 || ' p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE,'
731 || g_new_line
732 || ' p_msg_text IN VARCHAR2,' || g_new_line
733 || ' x_error_code OUT NUMBER,' || g_new_line
734 || ' x_error_message OUT VARCHAR2,' || g_new_line
735 || ' p_process_reference IN VARCHAR2 DEFAULT NULL )' ;
736
737 END cr_process_signature;
738
739 ----------------------------------------------------------------------------
740 ----------------------------------------------------------------------------
741 ----- Procedure: CR_DFLT_PROCESS_SIG()
742 ----- Purpose: Creates the signature for procedure DEFAULT_PROCESS().
743 ----------------------------------------------------------------------------
744 ----------------------------------------------------------------------------
745
746 PROCEDURE cr_dflt_process_sig(
747 x_dflt_process_sig OUT NOCOPY VARCHAR2
748 )
749 IS
750 BEGIN
751
752 x_dflt_process_sig := x_dflt_process_sig
753 || 'PROCEDURE DEFAULT_PROCESS ( '
754 || ' p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE,'
755 || g_new_line
756 || ' p_msg_text IN VARCHAR2,' || g_new_line
757 || ' x_error_code OUT NUMBER,' || g_new_line
758 || ' x_error_message OUT VARCHAR2 ) ' ;
759
760 END cr_dflt_process_sig;
761
762 ----------------------------------------------------------------------------
763 ----------------------------------------------------------------------------
764 ----- Procedure: CR_VALIDATE_SIGNATURE()
765 ----- Purpose: Creates the signature for procedure VALIDATE().
766 ----------------------------------------------------------------------------
767 ----------------------------------------------------------------------------
768
769 PROCEDURE cr_validate_signature(
770 x_validate_sig OUT NOCOPY VARCHAR2
771 )
772 IS
773 BEGIN
774
775 x_validate_sig := x_validate_sig || 'PROCEDURE VALIDATE ( '
776 || ' p_msg_header IN OUT XNP_MESSAGE.MSG_HEADER_REC_TYPE,'
777 || g_new_line
778 || ' p_msg_text IN VARCHAR2,' || g_new_line
779 || ' x_error_code OUT NUMBER,' || g_new_line
780 || ' x_error_message OUT VARCHAR2 ) ' ;
781
782 END cr_validate_signature;
783
784 ----------------------------------------------------------------------------
785 ----------------------------------------------------------------------------
786 ----- Procedure: COMPILE()
787 ----- Purpose: Public interface for building a message.
788 ----------------------------------------------------------------------------
789 ----------------------------------------------------------------------------
790
791 PROCEDURE compile(
792 p_msg_code IN VARCHAR2
793 ,x_error_code OUT NOCOPY NUMBER
794 ,x_error_message OUT NOCOPY VARCHAR2
795 ,x_package_spec OUT NOCOPY VARCHAR2
796 ,x_package_body OUT NOCOPY VARCHAR2
797 ,x_synonym OUT NOCOPY VARCHAR2
798 )
799 IS
800
801 l_sysdate DATE ;
802 l_flag VARCHAR2(1);
803
804 CURSOR update_msg_type IS
805 SELECT 'X' FROM xnp_msg_types_b
806 WHERE msg_code = p_msg_code
807 FOR UPDATE OF status , last_compiled_date;
808
809 BEGIN
810
811 x_error_code := 0 ;
812 x_error_message := NULL ;
813
814 /** VBhatia -- 05/02/2002 **/
815 SELECT protected_flag
816 INTO l_flag
817 FROM xnp_msg_types_b
818 WHERE msg_code = p_msg_code;
819
820 IF l_flag <> 'Y' THEN /** -- end -- **/
821
822 xnp_msg_schema.validate(p_msg_code => p_msg_code,
823 x_error_code => x_error_code,
824 x_error_message => x_error_message ) ;
825
826 IF (x_error_code <> 0) THEN
827 RETURN;
828 END IF;
829
830 g_message_code := p_msg_code ;
831
832 OPEN get_msg_type_data ;
833
834 FETCH get_msg_type_data INTO
835 g_event_indr,
836 g_msg_priority,
837 g_process_logic,
838 g_out_process_logic,
839 g_dflt_process_logic,
840 g_validate_logic,
841 g_queue_name,
842 g_dtd_url ;
843
844 CLOSE get_msg_type_data ;
845
846 bld_msgevt(
847 x_error_code
848 ,x_error_message
849 ,x_package_spec
850 ,x_package_body
851 ,x_synonym ) ;
852
853 l_sysdate := SYSDATE ;
854
855 END IF;
856
857 IF ( x_error_code = 0 ) THEN
858 FOR cur_rec IN update_msg_type LOOP
859
860 UPDATE xnp_msg_types_b
861 SET status='COMPILED',
862 last_compiled_date = l_sysdate
863 WHERE CURRENT OF update_msg_type ;
864
865 END LOOP ;
866 END IF ;
867
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 IF ( get_msg_type_data%ISOPEN ) THEN
872 CLOSE get_msg_type_data ;
873 END IF ;
874 IF ( update_msg_type%ISOPEN ) THEN
875 CLOSE update_msg_type ;
876 END IF ;
877 x_error_code := SQLCODE;
878 x_error_message := SQLERRM;
879
880 END compile;
881
882 ---------------------------------------------------------------------------
883 ---------------------------------------------------------------------------
884 ----- Procedure: COMPILE_SPEC()
885 ----- Purpose: Compiles the package specification.
886 ---------------------------------------------------------------------------
887 ---------------------------------------------------------------------------
888
889 PROCEDURE compile_spec(
890 p_pkg_name IN VARCHAR2
891 ,x_error_code OUT NOCOPY NUMBER
892 ,x_error_message OUT NOCOPY VARCHAR2
893 )
894 IS
895
896 BEGIN
897
898 x_error_code := 0 ;
899 x_error_message := NULL ;
900
901 xdp_utilities.create_pkg( p_pkg_name => p_pkg_name
902 ,p_pkg_type => 'PACKAGE'
903 ,p_application_short_name => 'XNP'
904 ,x_error_code => x_error_code
905 ,x_error_message => x_error_message) ;
906
907 END compile_spec;
908
909 ---------------------------------------------------------------------------
910 ---------------------------------------------------------------------------
911 ----- Procedure: COMPILE_BODY()
912 ----- Purpose: Compiles the package body.
913 ---------------------------------------------------------------------------
914 ---------------------------------------------------------------------------
915
916 PROCEDURE compile_body(
917 p_pkg_name IN VARCHAR2
918 ,x_error_code OUT NOCOPY NUMBER
919 ,x_error_message OUT NOCOPY VARCHAR2
920 )
921 IS
922
923 BEGIN
924
925 x_error_code := 0 ;
926 x_error_message := NULL ;
927
928 xdp_utilities.create_pkg(
929 p_pkg_name => p_pkg_name
930 ,p_pkg_type => 'PACKAGE BODY'
931 ,p_application_short_name => 'XNP'
932 ,x_error_code => x_error_code
933 ,x_error_message => x_error_message) ;
934
935 END compile_body;
936
937 ---------------------------------------------------------------------------
938 ---------------------------------------------------------------------------
939 ----- Procedure: CR_PKG_BODY()
940 ----- Purpose: Creates the package body.
941 ---------------------------------------------------------------------------
942 ---------------------------------------------------------------------------
943
944 PROCEDURE cr_pkg_body(
945 x_error_code OUT NOCOPY NUMBER
946 ,x_error_message OUT NOCOPY VARCHAR2
947 ,x_package_body OUT NOCOPY VARCHAR2
948 )
949 IS
950
951 l_pkg_name VARCHAR2(64) ;
952 l_package_body VARCHAR2(32767) ;
953 l_create_body VARCHAR2(32767) ;
954 l_publish_body VARCHAR2(32767) ;
955 l_send_body VARCHAR2(32767) ;
956 l_process_body VARCHAR2(32767) ;
957 l_dflt_process_body VARCHAR2(32767) ;
958 l_validate_body VARCHAR2(32767) ;
959 l_header_body VARCHAR2(32767) ;
960 l_start_body VARCHAR2(32767) ;
961 l_pkg_body VARCHAR2(32767) ;
962 l_body_len NUMBER := 0 ;
963 l_msg_len NUMBER := 0 ;
964 l_pkg_msg VARCHAR2(2000) := NULL;
965 BEGIN
966
967 x_error_code := 0 ;
968 x_error_message := NULL ;
969 x_package_body := NULL ;
970
971 xdp_utilities.initialize_pkg;
972
973 -- The following statement has been commented out, replaced with the one follows
974 -- l_pkg_name := g_pkg_prefix || g_message_code || g_pkg_suffix;
975 -- By Anping Wang, bug refer. 1650015
976 -- 02/19/2001
977
978 l_pkg_name := XNP_MESSAGE.g_pkg_prefix || g_message_code || XNP_MESSAGE.g_pkg_suffix;
979
980 xdp_utilities.build_pkg('CREATE OR REPLACE PACKAGE BODY ' || l_pkg_name || ' AS '
981 || g_new_line || g_new_line ) ;
982
983 x_package_body := x_package_body
984 || '/**************************************************************'
985 || g_new_line
986 || ' Copyright (c)1999 Oracle Corporation, Redwood Shores, CA, USA'
987 || g_new_line || ' All Rights Reserved' || g_new_line
988 || ' PACKAGE: ' || l_pkg_name || g_new_line
989 || ' CREATED: ' || TO_CHAR( sysdate, 'DD-MON-YYYY' ) || g_new_line
990 || ' BY: ' || 'Oracle Service Fulfillment Manager iMessage Studio' || g_new_line
991 || '****************************************************************/'
992 || g_new_line || g_new_line ;
993
994 cr_create_body( l_create_body ) ;
995
996 IF (g_event_indr <> 'TIMER') THEN
997 cr_publish_body( l_publish_body ) ;
998 xdp_utilities.build_pkg(l_publish_body||g_new_line || g_new_line);
999 cr_send_body( l_send_body ) ;
1000 xdp_utilities.build_pkg(l_send_body||g_new_line || g_new_line);
1001 END IF;
1002
1003 cr_process_body( l_process_body ) ;
1004 xdp_utilities.build_pkg(l_process_body||g_new_line || g_new_line);
1005
1006 cr_dflt_process_body( l_dflt_process_body ) ;
1007 xdp_utilities.build_pkg(l_dflt_process_body||g_new_line || g_new_line);
1008
1009 cr_validate_body( l_validate_body ) ;
1010 xdp_utilities.build_pkg(l_validate_body||g_new_line || g_new_line);
1011
1012 IF (g_event_indr = 'TIMER') THEN
1013 cr_start_body (l_start_body) ;
1014 xdp_utilities.build_pkg(l_start_body||g_new_line );
1015 END IF ;
1016
1017 xdp_utilities.build_pkg(' '||g_new_line || ' END ' ||l_pkg_name ||';');
1018
1019 FOR i IN 1..xdp_utilities.g_message_list.COUNT
1020 LOOP
1021 l_body_len := LENGTH(x_package_body);
1022 l_msg_len := LENGTH(xdp_utilities.g_message_list(i));
1023
1024 IF ( l_body_len + l_msg_len ) < 31800 THEN
1025 x_package_body := x_package_body||g_new_line||xdp_utilities.g_message_list(i) ;
1026 ELSE
1027 x_package_body := x_package_body||g_new_line||substr(xdp_utilities.g_message_list(i),1,(31800 - l_body_len)) ;
1028 fnd_message.set_name('XNP','XNP_INCOMPLETE_PKG_DEFINITION');
1029 fnd_message.set_token('MSG_CODE',g_message_code);
1030 l_pkg_msg := fnd_message.get ;
1031
1032 EXIT;
1033 END IF ;
1034 END LOOP ;
1035 x_package_body := x_package_body ||g_new_line || g_new_line|| l_pkg_msg ;
1036
1037 compile_body (l_pkg_name,
1038 x_error_code,
1039 x_error_message ) ;
1040
1041
1042 END cr_pkg_body;
1043
1044 ---------------------------------------------------------------------------
1045 ---------------------------------------------------------------------------
1046 ----- Procedure: CR_PKG_SPEC()
1047 ----- Purpose: Creates the package specification.
1048 ---------------------------------------------------------------------------
1049 ---------------------------------------------------------------------------
1050
1051 PROCEDURE cr_pkg_spec(
1052 x_error_code OUT NOCOPY NUMBER
1053 ,x_error_message OUT NOCOPY VARCHAR2
1054 ,x_package_spec OUT NOCOPY VARCHAR2
1055 )
1056 IS
1057
1058 l_pkg_name VARCHAR2(64) ;
1059 l_package_spec VARCHAR2(32767) ;
1060 l_publish_sig VARCHAR2(16000) ;
1061 l_send_sig VARCHAR2(16000) ;
1062 l_create_sig VARCHAR2(16000) ;
1063 l_validate_sig VARCHAR2(16000) ;
1064 l_process_sig VARCHAR2(16000) ;
1065 l_dflt_process_sig VARCHAR2(16000) ;
1066 l_timer_start_sig VARCHAR2(16000) ;
1067 l_pkg_spec VARCHAR2(32767);
1068 l_spec_len NUMBER;
1069 l_msg_len NUMBER;
1070 l_pkg_msg VARCHAR2(2000) := NULL;
1071
1072 BEGIN
1073
1074 x_error_code := 0 ;
1075 x_error_message := NULL ;
1076 x_package_spec := NULL ;
1077 --
1078 -- The following statement has been commented out, replaced with the one follows
1079 -- l_pkg_name := g_pkg_prefix || g_message_code || g_pkg_suffix;
1080 -- By Anping Wang, bug refer. 1650015
1081 -- 02/19/2001
1082 --
1083 xdp_utilities.initialize_pkg;
1084
1085 l_pkg_name := XNP_MESSAGE.g_pkg_prefix || g_message_code || XNP_MESSAGE.g_pkg_suffix ;
1086
1087
1088 x_package_spec := x_package_spec
1089 || '/**************************************************************'
1090 || g_new_line
1091 || ' Copyright (c)1999 Oracle Corporation, Redwood Shores, CA, USA'
1092 || g_new_line || ' All Rights Reserved' || g_new_line
1093 || ' PACKAGE: ' || l_pkg_name || g_new_line
1094 || ' CREATED: ' || TO_CHAR( sysdate, 'DD-MON-YYYY' ) || g_new_line
1095 || ' BY: ' || 'Oracle MessageBuilder' || g_new_line
1096 || '****************************************************************/'
1097 || g_new_line || g_new_line ;
1098
1099 xdp_utilities.build_pkg('CREATE OR REPLACE PACKAGE ' || l_pkg_name || ' AUTHID CURRENT_USER AS '
1100 || g_new_line || g_new_line ) ;
1101
1102 cr_create_signature ( l_create_sig ) ;
1103
1104 l_create_sig := l_create_sig || ';' || g_new_line ;
1105
1106 IF (g_event_indr <> 'TIMER') THEN
1107
1108 cr_publish_signature ( l_publish_sig ) ;
1109 l_publish_sig := l_publish_sig || ';' || g_new_line ;
1110 cr_send_signature ( l_send_sig ) ;
1111 l_send_sig := l_send_sig || ';' || g_new_line ;
1112
1113 END IF;
1114
1115 cr_process_signature ( l_process_sig ) ;
1116 l_process_sig := l_process_sig || ';' || g_new_line ;
1117
1118 cr_dflt_process_sig ( l_dflt_process_sig ) ;
1119 l_dflt_process_sig := l_dflt_process_sig || ';' || g_new_line ;
1120
1121 cr_validate_signature ( l_validate_sig ) ;
1122 l_validate_sig := l_validate_sig || ';' || g_new_line ;
1123
1124 IF (g_event_indr = 'TIMER') THEN
1125 cr_start_signature (l_timer_start_sig) ;
1126 l_timer_start_sig := l_timer_start_sig || ';' || g_new_line ;
1127 END IF;
1128
1129 /* Adding all individual signatures to the tabe of records - xdp_utilities.g_message_list */
1130
1131 xdp_utilities.build_pkg(l_create_sig||g_new_line ||g_new_line);
1132 xdp_utilities.build_pkg(l_publish_sig||g_new_line ||g_new_line);
1133 xdp_utilities.build_pkg(l_send_sig||g_new_line ||g_new_line);
1134 xdp_utilities.build_pkg(l_process_sig||g_new_line ||g_new_line);
1135 xdp_utilities.build_pkg(l_dflt_process_sig||g_new_line ||g_new_line);
1136 xdp_utilities.build_pkg(l_validate_sig||g_new_line ||g_new_line);
1137 xdp_utilities.build_pkg(l_timer_start_sig||g_new_line ||g_new_line);
1138 xdp_utilities.build_pkg(' '||g_new_line ||g_new_line|| ' END ' ||l_pkg_name ||';'||g_new_line ||g_new_line);
1139
1140 FOR j IN 1..xdp_utilities.g_message_list.COUNT
1141 LOOP
1142 l_spec_len := LENGTH(x_package_spec);
1143 l_msg_len := LENGTH(xdp_utilities.g_message_list(j));
1144
1145 IF (l_spec_len + l_msg_len ) < 31800 THEN
1146 x_package_spec := x_package_spec||g_new_line||xdp_utilities.g_message_list(j) ;
1147 ELSE
1148 x_package_spec := x_package_spec||g_new_line||substr(xdp_utilities.g_message_list(j),1,(31800 - l_spec_len));
1149 fnd_message.set_name('XNP','XNP_INCOMPLETE_PKG_DEFINITION');
1150 fnd_message.set_token('MSG_CODE',g_message_code);
1151 l_pkg_msg := fnd_message.get ;
1152 EXIT;
1153 END IF ;
1154 END LOOP ;
1155
1156 x_package_spec := x_package_spec ||g_new_line || g_new_line|| l_pkg_msg ;
1157
1158 compile_spec ( l_pkg_name,
1159 x_error_code,
1160 x_error_message ) ;
1161
1162 END cr_pkg_spec;
1163
1164 ---------------------------------------------------------------------------
1165 ----- Procedure: CR_PUBLISH_BODY()
1166 ----- Purpose: Creates the body for procedure PUBLISH().
1167 ---------------------------------------------------------------------------
1168
1169 PROCEDURE cr_publish_body(
1170 x_publish_body OUT NOCOPY VARCHAR2
1171 )
1172 IS
1173
1174 BEGIN
1175
1176 cr_publish_signature ( x_publish_body ) ;
1177 x_publish_body := x_publish_body || ' IS' || g_new_line || g_new_line ;
1178
1179 x_publish_body := x_publish_body
1180 || ' e_NO_DESTINATION EXCEPTION ;' || g_new_line ;
1181
1182 x_publish_body := x_publish_body
1183 || ' l_recipient_list VARCHAR2 (2000) ;' || g_new_line ;
1184
1185 x_publish_body := x_publish_body
1186 || ' l_consumer_list VARCHAR2 (4000) ;' || g_new_line ;
1187
1188 x_publish_body := x_publish_body
1189 || ' l_queue_name VARCHAR2 (2000) ;' || g_new_line ;
1190
1191 x_publish_body := x_publish_body
1192 || ' l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;' || g_new_line ;
1193
1194 x_publish_body := x_publish_body
1195 || ' l_msg_text VARCHAR2(32767) ;' || g_new_line ;
1196
1197 x_publish_body := x_publish_body
1198 || 'BEGIN' || g_new_line || g_new_line ;
1199
1200 x_publish_body := x_publish_body
1201 || ' x_error_code := 0 ;' || g_new_line
1202 || ' x_error_message := NULL ;' || g_new_line || g_new_line ;
1203
1204 -- BUG # 1500177
1205 -- commented code is replaced by the new code bellow.
1206 -- Now the correct consumer_name is obtained if passed as null
1207
1208 x_publish_body := x_publish_body || g_new_line
1209 || '/* check if the consumer list is NULL */'
1210 || g_new_line || g_new_line ;
1211
1212 x_publish_body := x_publish_body || g_new_line
1213 || ' l_consumer_list := p_consumer_list ;'
1214 || g_new_line || g_new_line ;
1215
1216 x_publish_body := x_publish_body || g_new_line
1217 || ' IF (l_consumer_list IS NULL) THEN' || g_new_line
1218 || ' XNP_MESSAGE.GET_SUBSCRIBER_LIST( ' || ''''
1219 || g_message_code || ''', '
1220 || 'l_consumer_list );' || g_new_line
1221 || ' END IF;' || g_new_line || g_new_line ;
1222
1223
1224 x_publish_body := x_publish_body || g_new_line
1225 || ' l_recipient_list := p_recipient_list ;'
1226 || g_new_line || g_new_line ;
1227
1228
1229 x_publish_body := x_publish_body
1230 || 'l_queue_name := ' || '''' || g_queue_name || '''' || ';'
1231 || g_new_line ;
1232
1233 x_publish_body := x_publish_body || g_new_line
1234 || '/* create the XML message */' || g_new_line || g_new_line ;
1235
1236 x_publish_body := x_publish_body
1237 || ' ' || 'CREATE_MSG (' || g_new_line ;
1238
1239 FOR current_rec IN get_parameter_data
1240 LOOP
1241 x_publish_body := x_publish_body || ' ' || g_np_prefix
1242 || current_rec.name || '=>' || g_np_prefix || current_rec.name ;
1243 x_publish_body := x_publish_body || g_comma || g_new_line ;
1244
1245 END LOOP ;
1246
1247 x_publish_body := x_publish_body
1248 || ' x_msg_header=>l_msg_header,' || g_new_line
1249 || ' x_msg_text=>l_msg_text,' || g_new_line
1250 || ' x_error_code=>x_error_code,' || g_new_line
1251 || ' x_error_message=>x_error_message,' || g_new_line
1252 || ' p_sender_name=>p_sender_name,' || g_new_line
1253 || ' p_recipient_list=>l_recipient_list,' || g_new_line
1254 || ' p_version=>p_version,' || g_new_line
1255 || ' p_reference_id=>p_reference_id,' || g_new_line
1256 || ' p_opp_reference_id=>p_opp_reference_id,' || g_new_line
1257 || ' p_order_id=>p_order_id,' || g_new_line
1258 || ' p_wi_instance_id=>p_wi_instance_id,' || g_new_line
1259 || ' p_fa_instance_id=>p_fa_instance_id ) ;' || g_new_line
1260 || g_new_line ;
1261
1262 x_publish_body := x_publish_body
1263 || ' x_message_id := l_msg_header.message_id ;' || g_new_line ;
1264
1265 x_publish_body := x_publish_body || g_new_line||g_new_line
1266 || '/* enqueue the XML message for delivery */' || g_new_line
1267 || g_new_line ;
1268
1269
1270 IF (g_event_indr = 'MSG') THEN
1271 IF (UPPER(g_queue_name) = 'XNP_OUT_MSG_Q')
1272 THEN
1273
1274 -- BUG # 1500177
1275 -- added fe_name parameter to be passed while calling push()
1276 -- It is passed with the value from xnp_standard.fe_value.
1277 -- The package variable is populated by xnp_standard before call
1278 -- to <message>.send() is made.
1279
1280 x_publish_body := x_publish_body
1281 || ' IF (x_error_code = 0) THEN' || g_new_line
1282 || ' XNP_MESSAGE.push ( ' || g_new_line
1283 || ' p_msg_header => l_msg_header, ' || g_new_line
1284 || ' p_body_text => l_msg_text, ' || g_new_line
1285 || ' p_queue_name => xnp_event.c_outbound_msg_q, '
1286 || g_new_line
1287 || ' p_recipient_list => l_consumer_list, '
1288 || g_new_line
1289 || ' p_fe_name => xnp_standard.fe_name, '
1290 || g_new_line
1291 -- || ' p_correlation_id => TO_CHAR(x_message_id), '
1292 || ' p_correlation_id => l_msg_header.message_code, '
1293 || g_new_line
1294 || ' p_priority=>''' || g_msg_priority || ''','
1295 || g_new_line ;
1296 ELSIF (UPPER(g_queue_name) = 'XNP_IN_MSG_Q') THEN
1297 x_publish_body := x_publish_body
1298 || ' IF (x_error_code = 0) THEN' || g_new_line||g_new_line
1299 || ' XNP_MESSAGE.push ( ' || g_new_line
1300 || ' p_msg_header => l_msg_header, ' || g_new_line
1301 || ' p_body_text => l_msg_text, ' || g_new_line
1302 || ' p_queue_name => xnp_event.c_inbound_msg_q, '
1303 || g_new_line
1304 -- || ' p_correlation_id => ' || ''''
1305 -- || 'MSG_SERVER' || '''' || ', '
1306 || ' p_correlation_id => l_msg_header.message_code, '
1307 || g_new_line
1308 || ' p_priority=>''' || g_msg_priority || ''', '
1309 || g_new_line ;
1310 ELSIF (UPPER(g_queue_name) = 'XNP_IN_EVT_Q') THEN
1311 x_publish_body := x_publish_body
1312 || ' IF (x_error_code = 0) THEN' || g_new_line||g_new_line
1313 || ' XNP_MESSAGE.push ( ' || g_new_line
1314 || ' p_msg_header => l_msg_header, ' || g_new_line
1315 || ' p_body_text => l_msg_text, ' || g_new_line
1316 || ' p_queue_name => xnp_event.c_internal_evt_q, '
1317 || g_new_line
1318 -- || ' p_correlation_id => TO_CHAR(x_message_id), '
1319 || ' p_correlation_id => l_msg_header.message_code, '
1320 -- || 'MSG_SERVER' || '''' || ', '
1321 || g_new_line
1322 || ' p_priority=>''' || g_msg_priority || ''', '
1323 || g_new_line ;
1324 ELSIF (UPPER(g_queue_name) = 'XNP_IN_TMR_Q') THEN
1325 x_publish_body := x_publish_body
1326 || ' IF (x_error_code = 0) THEN' || g_new_line||g_new_line
1327 || ' XNP_MESSAGE.push ( ' || g_new_line
1328 || ' p_msg_header => l_msg_header, ' || g_new_line
1329 || ' p_body_text => l_msg_text, ' || g_new_line
1330 || ' p_queue_name => xnp_event.c_timer_q, '
1331 || g_new_line
1332 -- || ' p_correlation_id => TO_CHAR(x_message_id), '
1333 || ' p_correlation_id => l_msg_header.message_code, '
1334 -- || 'MSG_SERVER' || '''' || ', '
1335 || g_new_line
1336 || ' p_priority=>''' || g_msg_priority || ''', '
1337 || g_new_line ;
1338 ELSE x_publish_body := x_publish_body
1339 || ' IF (x_error_code = 0) THEN' || g_new_line||g_new_line
1340 || ' XNP_MESSAGE.PUSH_WF( ' || g_new_line
1341 || ' p_msg_header => l_msg_header, ' || g_new_line
1342 || ' p_body_text => l_msg_text, ' || g_new_line
1343 || ' p_queue_name => ''' || g_queue_name || ''', '
1344 || g_new_line
1345 -- || ' p_correlation_id => TO_CHAR(x_message_id), '
1346 || ' p_correlation_id => l_msg_header.message_code, '
1347 -- || 'MSG_SERVER' || '''' || ', '
1348 || g_new_line
1349 || ' p_priority=>''' || g_msg_priority || ''', '
1350 || g_new_line ;
1351
1352 END IF ;
1353
1354
1355 IF (g_ack_reqd_flag = 'Y') THEN
1356 x_publish_body := x_publish_body
1357 || ' p_commit_mode => XNP_MESSAGE.C_IMMEDIATE ); '
1358 || g_new_line ;
1359 ELSE
1360 x_publish_body := x_publish_body
1361 || ' p_commit_mode => XNP_MESSAGE.C_ON_COMMIT ); '
1362 || g_new_line ;
1363 END IF ;
1364
1365 x_publish_body := x_publish_body || g_new_line
1366 || '/* out processing logic */' || g_new_line || g_new_line ;
1367
1368 x_publish_body := x_publish_body || g_new_line
1369 || g_out_process_logic || g_new_line
1370 || ' END IF ;' || g_new_line ||g_new_line;
1371
1372 ---- In case of EVENTS the EVENT will be published to both
1373 ---- the Internal and External event queues
1374
1375 ELSE
1376 IF UPPER(g_queue_name) like 'XNP%' THEN
1377
1378 x_publish_body := x_publish_body
1379 || ' IF (x_error_code = 0) THEN' || g_new_line||g_new_line
1380 || ' XNP_MESSAGE.push ( ' || g_new_line
1381 || ' p_msg_header => l_msg_header, ' || g_new_line
1382 || ' p_body_text => l_msg_text, ' || g_new_line
1383 || ' p_queue_name => xnp_event.c_internal_evt_q, '
1384 || g_new_line
1385 -- || ' p_correlation_id => ' || '''' || 'MSG_SERVER'
1386 -- || '''' || ', '
1387 || ' p_correlation_id => l_msg_header.message_code, '
1388 || g_new_line
1389 || ' p_priority=>''' || g_msg_priority || ''', '
1390 || g_new_line ;
1391
1392 ELSE
1393 x_publish_body := x_publish_body
1394 || ' IF (x_error_code = 0) THEN' || g_new_line||g_new_line
1395 || ' XNP_MESSAGE.PUSH_WF( ' || g_new_line
1396 || ' p_msg_header => l_msg_header, ' || g_new_line
1397 || ' p_body_text => l_msg_text, ' || g_new_line
1398 || ' p_queue_name => ''' || g_queue_name || ''', '
1399 || g_new_line
1400 || ' p_correlation_id => l_msg_header.message_code, '
1401 || g_new_line
1402 || ' p_priority=>''' || g_msg_priority || ''', '
1403 || g_new_line ;
1404
1405 END IF;
1406
1407 IF (g_ack_reqd_flag = 'Y') THEN
1408 x_publish_body := x_publish_body
1409 || ' p_commit_mode => XNP_MESSAGE.C_IMMEDIATE ); '
1410 || g_new_line ;
1411 ELSE
1412 x_publish_body := x_publish_body
1413 || ' p_commit_mode => XNP_MESSAGE.C_ON_COMMIT ); '
1414 || g_new_line ;
1415 END IF ;
1416
1417 x_publish_body := x_publish_body
1418 || ' IF (l_consumer_list IS NOT NULL) THEN' || g_new_line
1419 || ' XNP_MESSAGE.GET_SEQUENCE(l_msg_header.message_id) ;'
1420 || g_new_line ||g_new_line
1421 || ' l_msg_header.direction_indr := '||''''||'O'||''''||';'
1422 || g_new_line ||g_new_line
1423 || ' XNP_MESSAGE.push ( ' || g_new_line
1424 || ' p_msg_header => l_msg_header, ' || g_new_line
1425 || ' p_body_text => l_msg_text, ' || g_new_line
1426 || ' p_queue_name => xnp_event.c_outbound_msg_q, '
1427 || g_new_line
1428 || ' p_recipient_list => l_consumer_list, ' || g_new_line
1429 -- || ' p_correlation_id => TO_CHAR(x_message_id), '
1430 || ' p_correlation_id => l_msg_header.message_code, '
1431 || g_new_line
1432 || ' p_priority=>''' || g_msg_priority || ''' ) ; '
1433 || g_new_line
1434 || ' END IF ;' || g_new_line ||g_new_line;
1435
1436 x_publish_body := x_publish_body || g_new_line||g_new_line
1437 || '/* out processing logic */' || g_new_line || g_new_line ;
1438
1439 x_publish_body := x_publish_body || g_new_line
1440 || g_out_process_logic || g_new_line
1441 || ' END IF ;' || g_new_line||g_new_line ;
1442
1443 END IF ;
1444
1445 x_publish_body := x_publish_body
1446 || 'EXCEPTION' || g_new_line||g_new_line
1447 || ' WHEN e_NO_DESTINATION THEN' || g_new_line
1448 || ' x_error_code := XNP_ERRORS.G_NO_DESTINATION ;' || g_new_line
1449 || ' WHEN OTHERS THEN' || g_new_line
1450 || ' x_error_code := SQLCODE ;' || g_new_line
1451 || ' x_error_message := SQLERRM ;' || g_new_line ;
1452
1453 x_publish_body := x_publish_body
1454 || 'END ;'||g_new_line ;
1455
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 IF (get_parameter_data%ISOPEN) THEN
1459 close get_parameter_data ;
1460 END IF ;
1461 RAISE ;
1462 END cr_publish_body;
1463
1464 ---------------------------------------------------------------------------
1465 ----- Procedure: CR_PUBLISH_SIGNATURE()
1466 ----- Purpose: Creates the signature for procedure PUBLISH().
1467 ---------------------------------------------------------------------------
1468
1469 PROCEDURE cr_publish_signature(
1470 x_publish_sig OUT NOCOPY VARCHAR2
1471 )
1472 IS
1473
1474 l_quote char ;
1475
1476 BEGIN
1477
1478 x_publish_sig := x_publish_sig || 'PROCEDURE PUBLISH ( ' ;
1479
1480 FOR current_rec IN get_parameter_data
1481 LOOP
1482 x_publish_sig := x_publish_sig || ' ' || g_np_prefix
1483 || current_rec.name || ' ' || current_rec.element_datatype ;
1484
1485 IF (current_rec.element_datatype = 'NUMBER') THEN
1486 l_quote := '' ;
1487 ELSE
1488 l_quote := '''' ;
1489 END IF ;
1490
1491 IF (current_rec.element_default_value <> 'NP_NULL') THEN
1492 x_publish_sig := x_publish_sig || ' := ' || l_quote
1493 || current_rec.element_default_value || l_quote ;
1494 END IF ;
1495
1496 IF ((current_rec.mandatory_flag = 'N')
1497 AND (current_rec.element_default_value = 'NP_NULL')) THEN
1498 x_publish_sig := x_publish_sig || ' DEFAULT NULL' ;
1499 END IF ;
1500
1501 x_publish_sig := x_publish_sig || g_comma || g_new_line ;
1502
1503 END LOOP ;
1504
1505 x_publish_sig := x_publish_sig
1506 || ' x_message_id OUT NUMBER,' || g_new_line
1507 || ' x_error_code OUT NUMBER,' || g_new_line
1508 || ' x_error_message OUT VARCHAR2, ' || g_new_line
1509 || ' p_consumer_list IN VARCHAR2 DEFAULT NULL, ' || g_new_line
1510 || ' p_sender_name IN VARCHAR2 DEFAULT NULL, ' || g_new_line
1511 || ' p_recipient_list IN VARCHAR2 DEFAULT NULL,' || g_new_line
1512 || ' p_version IN NUMBER DEFAULT 1,' || g_new_line
1513 || ' p_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
1514 || ' p_opp_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
1515 || ' p_order_id IN NUMBER DEFAULT NULL,' || g_new_line
1516 || ' p_wi_instance_id IN NUMBER DEFAULT NULL,' || g_new_line
1517 || ' p_fa_instance_id IN NUMBER DEFAULT NULL ) ' ;
1518
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 IF (get_parameter_data%ISOPEN) THEN
1522 close get_parameter_data ;
1523 END IF ;
1524 RAISE ;
1525 END cr_publish_signature;
1526
1527 ---------------------------------------------------------------------------
1528 ---------------------------------------------------------------------------
1529 ----- Procedure: CR_CREATE_SIGNATURE()
1530 ----- Purpose: Creates the signature for procedure CREATE().
1531 ---------------------------------------------------------------------------
1532 ---------------------------------------------------------------------------
1533
1534 PROCEDURE cr_create_signature(
1535 x_create_sig OUT NOCOPY VARCHAR2
1536 )
1537 IS
1538
1539 l_quote char ;
1540
1541 BEGIN
1542
1543 x_create_sig := x_create_sig || 'PROCEDURE CREATE_MSG ( ' ;
1544
1545 FOR current_rec IN get_parameter_data
1546 LOOP
1547 x_create_sig := x_create_sig || ' ' || g_np_prefix
1548 || current_rec.name || ' ' || current_rec.element_datatype ;
1549
1550 IF (current_rec.element_datatype = 'NUMBER') THEN
1551 l_quote := '' ;
1552 ELSE
1553 l_quote := '''' ;
1554 END IF ;
1555
1556 IF (current_rec.element_default_value <> 'NP_NULL') THEN
1557 x_create_sig := x_create_sig || ' := ' || l_quote
1558 || current_rec.element_default_value || l_quote ;
1559 END IF ;
1560
1561 IF ((current_rec.mandatory_flag = 'N')
1562 AND (current_rec.element_default_value = 'NP_NULL')) THEN
1563 x_create_sig := x_create_sig || ' DEFAULT NULL' ;
1564 END IF ;
1565
1566 x_create_sig := x_create_sig || g_comma || g_new_line ;
1567
1568 END LOOP ;
1569
1570 x_create_sig := x_create_sig
1571 || ' x_msg_header OUT XNP_MESSAGE.MSG_HEADER_REC_TYPE,' || g_new_line
1572 || ' x_msg_text OUT VARCHAR2,' || g_new_line
1573 || ' x_error_code OUT NUMBER,' || g_new_line
1574 || ' x_error_message OUT VARCHAR2,' || g_new_line
1575 || ' p_sender_name IN VARCHAR2 DEFAULT NULL,' || g_new_line
1576 || ' p_recipient_list IN VARCHAR2 DEFAULT NULL,' || g_new_line
1577 || ' p_version IN NUMBER DEFAULT 1,' || g_new_line
1578 || ' p_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
1579 || ' p_opp_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
1580 || ' p_order_id IN NUMBER DEFAULT NULL,' || g_new_line
1581 || ' p_wi_instance_id IN NUMBER DEFAULT NULL,' || g_new_line
1582 || ' p_fa_instance_id IN NUMBER DEFAULT NULL,'|| g_new_line
1583 || ' p_delay IN NUMBER DEFAULT NULL,'|| g_new_line
1584 || ' p_interval IN NUMBER DEFAULT NULL ) ' ;
1585
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 IF (get_parameter_data%ISOPEN) THEN
1589 close get_parameter_data ;
1590 END IF ;
1591 RAISE ;
1592 END CR_CREATE_SIGNATURE;
1593
1594
1595 ----------------------------------------------------------------------------
1596 ----------------------------------------------------------------------------
1597 ----- Procedure: CR_SEND_SIGNATURE()
1598 ----- Purpose: Creates the signature for procedure SEND().
1599 ----------------------------------------------------------------------------
1600 ----------------------------------------------------------------------------
1601
1602 PROCEDURE cr_send_signature(
1603 x_send_sig OUT NOCOPY VARCHAR2
1604 )
1605 IS
1606
1607 l_quote char ;
1608
1609 BEGIN
1610
1611
1612 x_send_sig := x_send_sig || 'PROCEDURE SEND ( ' ;
1613
1614 FOR current_rec IN get_parameter_data
1615 LOOP
1616 x_send_sig := x_send_sig || ' ' || g_np_prefix
1617 || current_rec.name || ' ' || current_rec.element_datatype ;
1618
1619 IF (current_rec.element_datatype = 'NUMBER') THEN
1620 l_quote := '' ;
1621 ELSE
1622 l_quote := '''' ;
1623 END IF ;
1624
1625 IF (current_rec.element_default_value <> 'NP_NULL') THEN
1626 x_send_sig := x_send_sig || ' DEFAULT ' || l_quote
1627 || current_rec.element_default_value || l_quote ;
1628 END IF ;
1629
1630
1631 IF ((current_rec.mandatory_flag = 'N')
1632 AND (current_rec.element_default_value = 'NP_NULL')) THEN
1633 x_send_sig := x_send_sig || ' DEFAULT NULL' ;
1634 END IF ;
1635
1636 x_send_sig := x_send_sig || g_comma || g_new_line ;
1637
1638 END LOOP ;
1639
1640 x_send_sig := x_send_sig
1641 || ' x_message_id OUT NUMBER,' || g_new_line
1642 || ' x_error_code OUT NUMBER,' || g_new_line
1643 || ' x_error_message OUT VARCHAR2, ' || g_new_line
1644 || ' p_consumer_name IN VARCHAR2, ' || g_new_line
1645 || ' p_sender_name IN VARCHAR2 DEFAULT NULL, ' || g_new_line
1646 || ' p_recipient_name IN VARCHAR2 DEFAULT NULL, ' || g_new_line
1647 || ' p_version IN NUMBER DEFAULT 1, ' || g_new_line
1648 || ' p_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
1649 || ' p_opp_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
1650 || ' p_order_id IN NUMBER DEFAULT NULL,' || g_new_line
1651 || ' p_wi_instance_id IN NUMBER DEFAULT NULL,' || g_new_line
1652 || ' p_fa_instance_id IN NUMBER DEFAULT NULL ) ' ;
1653
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 IF (get_parameter_data%ISOPEN) THEN
1657 close get_parameter_data ;
1658 END IF ;
1659 RAISE ;
1660
1661 END cr_send_signature;
1662
1663 ----------------------------------------------------------------------------
1664 ----------------------------------------------------------------------------
1665 ----- Procedure: generate_create_body()
1666 ----- Purpose: Recursively generates code for procedure PUBLISH().
1667 ----------------------------------------------------------------------------
1668 ----------------------------------------------------------------------------
1669
1670 PROCEDURE generate_create_body(
1671 P_ELEMENT IN VARCHAR2
1672 ,P_ELEMENT_TYPE IN VARCHAR2
1673 ,P_MANDATORY_FLAG IN VARCHAR2
1674 ,P_SOURCE_TYPE IN VARCHAR2
1675 ,P_DATA_SOURCE IN VARCHAR2
1676 ,P_SOURCE_REF IN VARCHAR2
1677 ,P_CARDINALITY IN VARCHAR2
1678 ,P_PARAMETER_FLAG IN VARCHAR2
1679 )
1680 IS
1681
1682 l_child XNP_MSG_ELEMENTS.name%TYPE ;
1683 l_datatype XNP_MSG_ELEMENTS.element_datatype%TYPE ;
1684 l_mandatory_indr XNP_MSG_ELEMENTS.mandatory_flag%TYPE ;
1685 l_data_source_type XNP_MSG_STRUCTURES.data_source_type%TYPE ;
1686 l_data_source XNP_MSG_STRUCTURES.data_source%TYPE ;
1687 l_data_source_ref XNP_MSG_STRUCTURES.data_source_reference%TYPE ;
1688 l_cardinality XNP_MSG_STRUCTURES.cardinality%TYPE ;
1689 l_parameter_flag XNP_MSG_ELEMENTS.parameter_flag%TYPE ;
1690 l_loop_counter NUMBER := 0 ;
1691
1692 CURSOR get_children ( parent_name IN VARCHAR2 ) IS
1693 SELECT MET.name,
1694 MET.element_datatype,
1695 MET.mandatory_flag,
1696 MSE.data_source_type,
1697 MSE.data_source,
1698 MSE.data_source_reference,
1699 MSE.cardinality,
1700 MET.parameter_flag
1701 FROM xnp_msg_elements MET, xnp_msg_structures MSE
1702 WHERE MSE.msg_code = g_message_code
1703 AND MSE.child_element_id = MET.msg_element_id
1704 AND MSE.parent_element_id = (
1705 SELECT msg_element_id FROM xnp_msg_elements MET1
1706 WHERE MET1.name = parent_name
1707 AND MET1.msg_code = g_message_code )
1708 ORDER BY MSE.sequence_in_parent ;
1709
1710 BEGIN
1711
1712 -----
1713 -- check the source type,if the source type is SQL declare a cursor in the
1714 -- declaration section. SQL source types can be defined at an intermediate
1715 -- or leaf-level node. A procedure source type is only defined at leaf level.
1716 -----
1717
1718 IF (p_parameter_flag <> 'Y') THEN
1719
1720 IF ((g_event_indr = 'TIMER') AND
1721 (p_element <> g_message_code)) THEN
1722
1723 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF (p_' || p_element || ' IS NULL) THEN' ;
1724 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' NULL; ' || g_new_line ;
1725
1726 END IF ;
1727
1728 IF ( p_source_type = 'SQL' ) THEN
1729
1730 l_loop_counter := g_loop_counter ;
1731 g_loop_counter := g_loop_counter + 1 ;
1732
1733 g_mandatory_list := '' ;
1734 g_mandatory_check := FALSE ;
1735
1736 g_decl_section := g_decl_section
1737 || ' l_loop_index_' || TO_CHAR (l_loop_counter)
1738 ||' NUMBER ;' || g_new_line ;
1739
1740 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_loop_index_' || TO_CHAR(l_loop_counter)
1741 || ' := 0 ;'
1742 || g_new_line ;
1743
1744 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' FOR ' || g_np_prefix || p_element || ' IN ('
1745 || p_data_source || ')'
1746 || g_new_line ;
1747 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' LOOP '||g_new_line ;
1748
1749 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_loop_index_' || TO_CHAR(l_loop_counter)
1750 || ' := l_loop_index_'
1751 || TO_CHAR(l_loop_counter)|| ' + 1 ;'
1752 || g_new_line ;
1753
1754 END IF ;
1755
1756 -----
1757 -- get the children, if no children found we are at the leaf level.
1758 -- Note that the open and fetch is only done for ORACLE to set
1759 -- the %NOTFOUND cursor variable.
1760 -----
1761
1762 OPEN get_children ( p_element ) ;
1763 FETCH get_children INTO
1764 l_child,
1765 l_datatype,
1766 l_mandatory_indr,
1767 l_data_source_type,
1768 l_data_source,
1769 l_data_source_ref,
1770 l_cardinality,
1771 l_parameter_flag ;
1772
1773 IF ( get_children%NOTFOUND ) THEN
1774 -----
1775 -- check if the element has a procedure type source, if true generate code
1776 -- to execute the function.
1777 -----
1778 -----
1779 -- if the source type is NULL and the data source is NULL, it means that
1780 -- the data reference is referring to a data source defined at a previous
1781 -- level.
1782 -----
1783 IF (( p_source_type IS NULL ) AND
1784 ( p_data_source IS NULL )) THEN
1785
1786 -- check if the leaf is mandatory and generate appropriate code
1787
1788 IF ( p_mandatory_flag = 'Y' ) THEN
1789
1790 g_mandatory_check := TRUE ;
1791 g_mandatory_list := g_mandatory_list || ', '
1792 || p_element ;
1793
1794 IF ( p_source_ref IS NOT NULL ) THEN
1795 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF ( ' || p_source_ref
1796 || ' IS NULL) THEN' ;
1797 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
1798 ||', ' ||'''' || 'XNP_MISSING_MANDATORY_ATTR'||''''
1799 ||' );' ;
1800 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'ATTRIBUTE'
1801 ||''''||','||''''||p_element||''''||' ) ;' ;
1802 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
1803 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' RAISE e_MISSING_MANDATORY_DATA ;' ;
1804 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END IF ;'
1805 || g_new_line ;
1806 END IF ;
1807 END IF ;
1808
1809 IF (p_source_ref IS NOT NULL) THEN
1810
1811 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
1812 || '''' || p_element || ''', '
1813 || p_source_ref || ' ) ;' ;
1814 END IF ;
1815 ELSE
1816 -----
1817 -- source type, datasource and reference could be defined at the leaf level.
1818 -- check if it is of SQL source type and write the data element accordingly.
1819 -----
1820 IF ( p_source_type = 'SQL' ) THEN
1821
1822 IF ( p_mandatory_flag = 'Y' ) THEN
1823 g_mandatory_check := TRUE ;
1824 g_mandatory_list := g_mandatory_list || ', '
1825 || p_element ;
1826
1827 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF ( ' || p_source_ref
1828 || ' IS NULL) THEN' ;
1829 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
1830 ||', ' ||'''' || 'XNP_MISSING_MANDATORY_ATTR'||''''
1831 ||' );' ;
1832 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'ATTRIBUTE'
1833 ||''''||','||''''||p_element||''''||' ) ;' ;
1834 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
1835 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' RAISE e_MISSING_MANDATORY_DATA ;';
1836 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END IF ;'
1837 || g_new_line ;
1838 END IF ;
1839
1840 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
1841 || ''''|| p_element || ''', '
1842 || p_source_ref || ' ) ;' ;
1843 -----
1844 -- it is not a SQL type, it can be a PROCEDURE, or an ORDER or a WORK_ITEM
1845 -- or a FA type, so declare a variable and assign a value to it accordingly.
1846 -----
1847 ELSE
1848 IF ( p_element_type = 'VARCHAR2') THEN
1849 g_decl_section := g_decl_section || g_new_line
1850 || ' ' || g_np_prefix || p_element || ' '
1851 || p_element_type || '( 16000)' || ' ;'
1852 || g_new_line ;
1853 ELSE
1854 g_decl_section := g_decl_section
1855 || g_new_line
1856 || ' ' || g_np_prefix || p_element || ' '
1857 || p_element_type || ' ;' || g_new_line ;
1858 END IF ;
1859
1860 IF ( p_source_type = 'PROCEDURE' ) THEN
1861
1862 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element
1863 || ' := '|| p_source_ref || ' ;'
1864 || g_new_line ;
1865
1866 END IF ;
1867
1868 -- Added code for XML Function
1869
1870 IF (p_source_type = 'XMLFN') THEN
1871
1872 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element||
1873 ' := '|| p_source_ref || ' ;'||
1874 g_new_line ;
1875 END IF ;
1876
1877 --
1878
1879 IF ( p_source_type = 'ORDER' ) THEN
1880
1881 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' BEGIN'|| g_new_line;
1882
1883 -----
1884 -- if no data reference is specified use the element name as a tag to
1885 -- look up, else use the reference as the tag.
1886 -----
1887 IF (p_source_ref IS NULL) THEN
1888
1889 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix
1890 || p_element|| ' := '
1891 || 'XDP_ENGINE.get_order_param_value ( p_order_id, ';
1892 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_element || ''' );' ;
1893 ELSE
1894 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix
1895 || p_element || ' := '
1896 || 'XDP_ENGINE.get_order_param_value ( p_order_id, ';
1897 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_source_ref || ''' );' ;
1898 END IF ;
1899
1900 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXCEPTION WHEN NO_DATA_FOUND THEN' ;
1901 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
1902 ||', ' ||'''' || 'XNP_ORDER_DATA_NOT_FOUND'||''''
1903 ||' );' ;
1904 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'PARAMETER'
1905 ||''''||','||''''||p_element||''''||' ) ;' ;
1906 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
1907 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END;'|| g_new_line ;
1908 END IF ;
1909
1910 IF ( p_source_type = 'SDP_WI' ) THEN
1911
1912 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' BEGIN'|| g_new_line ;
1913
1914 IF (p_source_ref IS NULL) THEN
1915 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element|| ' := '
1916 || 'XDP_ENGINE.get_workitem_param_value ( p_wi_instance_id, ';
1917 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_element || ''' );' ;
1918 ELSE
1919 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element|| ' := '
1920 || 'XDP_ENGINE.get_workitem_param_value ( p_wi_instance_id, ';
1921 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_source_ref || ''' );';
1922 END IF ;
1923
1924 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXCEPTION WHEN NO_DATA_FOUND THEN' || g_new_line ;
1925 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
1926 ||', ' ||'''' || 'XNP_WI_DATA_NOT_FOUND'||''''
1927 ||' );' ;
1928 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'PARAMETER'
1929 ||''''||','||''''||p_element||''''||' ) ;' ;
1930 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
1931 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END;'|| g_new_line ;
1932 END IF ;
1933
1934 IF ( p_source_type = 'SDP_FA' ) THEN
1935
1936 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' BEGIN'|| g_new_line ;
1937
1938 IF (p_source_ref IS NULL) THEN
1939 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element|| ' := '
1940 || 'XDP_ENGINE.get_fa_param_value ( p_fa_instance_id, ';
1941 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_element || ''' );' ;
1942 ELSE
1943 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element|| ' := '
1944 || 'XDP_ENGINE.get_fa_param_value ( p_fa_instance_id, ';
1945 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_source_ref || ''' );';
1946 END IF ;
1947
1948 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXCEPTION WHEN NO_DATA_FOUND THEN' || g_new_line ;
1949 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
1950 ||', ' ||'''' || 'XNP_FA_DATA_NOT_FOUND'||''''
1951 ||' );' ;
1952 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'PARAMETER'
1953 ||''''||','||''''||p_element||''''||' ) ;' ;
1954 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
1955 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END;'|| g_new_line ;
1956 END IF ;
1957
1958 IF ( p_mandatory_flag = 'Y' ) THEN
1959 g_mandatory_check := TRUE ;
1960 g_mandatory_list := g_mandatory_list || ', '
1961 || p_element ;
1962
1963 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF ( ' || g_np_prefix || p_element|| ' IS NULL) THEN' ;
1964 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
1965 ||', ' ||'''' || 'XNP_MISSING_MANDATORY_ATTR'||''''
1966 ||' );' ;
1967 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'ATTRIBUTE'
1968 ||''''||','||''''||p_element||''''||' ) ;' ;
1969 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
1970 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' RAISE e_MISSING_MANDATORY_DATA ;';
1971 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END IF ;' || g_new_line ;
1972 END IF ;
1973
1974 -- Modified code for XML Function
1975
1976 IF p_source_type ='XMLFN' THEN
1977 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_element (' || ''''
1978 || p_element|| ''', ' || g_np_prefix
1979 || p_element|| ' ) ;';
1980 ELSE
1981 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( ' || ''''
1982 || p_element|| ''', ' || g_np_prefix
1983 || p_element|| ' ) ;';
1984 END IF ;
1985 END IF ;
1986 END IF ;
1987
1988 ELSE
1989
1990 -- children found, close the cursor, reopen and start recursion
1991
1992 CLOSE get_children ;
1993
1994 IF ((p_element <> g_message_code) AND ( p_element_type <> 'DUMMY' )) THEN
1995 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.begin_segment ( ' || ''''|| p_element
1996 || '''' || ' ) ;' ;
1997 END IF ;
1998
1999 IF ((p_source_ref IS NOT NULL) AND (p_source_type IS NULL)) THEN
2000 g_temp_tab(g_temp_tab.COUNT + 1 ) := 'xnp_xml_utils.append('||p_source_ref || ');';
2001 END IF ;
2002
2003 IF ((p_source_ref IS NOT NULL) AND (p_source_type = 'ORDER')) THEN
2004
2005 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' BEGIN'|| g_new_line ;
2006
2007 IF (p_source_ref IS NULL) THEN
2008
2009 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element || ' := '
2010 || 'XDP_ENGINE.get_order_param_value ( p_order_id, ';
2011 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_element || ''' );' ;
2012 ELSE
2013 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element || ' := '
2014 || 'XDP_ENGINE.get_order_param_value ( p_order_id, '
2015 || g_new_line ;
2016 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_source_ref || ''' );' ;
2017 END IF ;
2018
2019 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXCEPTION WHEN NO_DATA_FOUND THEN' || g_new_line ;
2020 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
2021 ||', ' ||'''' || 'XNP_ORDER_DATA_NOT_FOUND'||''''
2022 ||' );' ;
2023 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'PARAMETER'
2024 ||''''||','||''''||p_element||''''||' ) ;' ;
2025 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
2026 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END;'|| g_new_line ;
2027 END IF ;
2028
2029 IF ((p_source_ref IS NOT NULL) AND
2030 (p_source_type = 'SDP_WI')) THEN
2031
2032 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' BEGIN'|| g_new_line ;
2033
2034 IF (p_source_ref IS NULL) THEN
2035 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element || ' := '
2036 || 'XDP_ENGINE.get_workitem_param_value ( p_wi_instance_id, ';
2037 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_element || ''' );' ;
2038 ELSE
2039
2040 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element || ' := '
2041 || 'XDP_ENGINE.get_workitem_param_value ( p_wi_instance_id, ';
2042 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_source_ref || ''' );' ;
2043 END IF ;
2044
2045 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXCEPTION WHEN NO_DATA_FOUND THEN' || g_new_line ;
2046 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
2047 ||', ' ||'''' || 'XNP_WI_DATA_NOT_FOUND'||''''
2048 ||' );' ;
2049 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'PARAMETER'
2050 ||''''||','||''''||p_element||''''||' ) ;' ;
2051 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
2052 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END;'|| g_new_line ;
2053 END IF ;
2054
2055 IF ((p_source_ref IS NOT NULL) AND (p_source_type = 'SDP_FA')) THEN
2056
2057 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' BEGIN' || g_new_line ;
2058
2059 IF (p_source_ref IS NULL) THEN
2060 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element || ' := ';
2061 g_temp_tab(g_temp_tab.COUNT + 1 ) := 'XDP_ENGINE.get_fa_param_value ( p_fa_instance_id, ';
2062 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' '''|| p_element || ''' );' ;
2063 ELSE
2064 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ' || g_np_prefix || p_element || ' := ';
2065 g_temp_tab(g_temp_tab.COUNT + 1 ) := 'XDP_ENGINE.get_fa_param_value ( p_fa_instance_id, '
2066 || ' ''' || p_source_ref || ''' );' ;
2067 END IF;
2068
2069 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXCEPTION WHEN NO_DATA_FOUND THEN' || g_new_line ;
2070 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
2071 ||', ' ||'''' || 'XNP_FA_DATA_NOT_FOUND'||''''
2072 ||' );' ;
2073 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'PARAMETER'
2074 ||''''||','||''''||p_element||''''||' ) ;' ;
2075 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
2076 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END;' || g_new_line ;
2077 END IF ;
2078
2079 IF ((p_source_ref IS NOT NULL) AND
2080 ((p_source_type = 'ORDER') OR
2081 (p_source_type = 'SDP_WI') OR
2082 (p_source_type = 'SDP_FA'))) THEN
2083
2084 g_decl_section := g_decl_section || g_new_line
2085 || ' ' || g_np_prefix || p_element || ' '
2086 || p_element_type || '( 16000)' || ' ;' || g_new_line ;
2087
2088 IF (p_mandatory_flag = 'Y') THEN
2089 g_mandatory_check := TRUE ;
2090 g_mandatory_list := g_mandatory_list
2091 || 'Missing Mandatory Attribute - '
2092 || p_element || ''' ;' || g_new_line
2093 || ' RAISE e_MISSING_MANDATORY_DATA ;'
2094 || g_new_line
2095 || ' END IF ;' || g_new_line ;
2096 END IF;
2097
2098 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( ' || ''''
2099 || p_element|| ''', ' || g_np_prefix
2100 || p_element || ' ) ;';
2101 END IF ;
2102
2103 FOR my_child IN get_children ( p_element ) LOOP
2104
2105 generate_create_body (my_child.name,
2106 my_child.element_datatype,
2107 my_child.mandatory_flag,
2108 my_child.data_source_type,
2109 my_child.data_source,
2110 my_child.data_source_reference,
2111 my_child.cardinality,
2112 my_child.parameter_flag ) ;
2113
2114 END LOOP ;
2115
2116 IF ((p_element <> g_message_code) AND (p_element_type <> 'DUMMY')) THEN
2117 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.end_segment ( ' || ''''|| p_element || '''' || ' ) ;';
2118 END IF ;
2119
2120 END IF ;
2121
2122 IF ( p_source_type = 'SQL' ) THEN
2123
2124 IF ( p_cardinality = 'ONE_ONLY' ) THEN
2125 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' EXIT ;' || g_new_line ;
2126 END IF ;
2127
2128 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END LOOP ;' || g_new_line ;
2129
2130 IF (g_mandatory_check = TRUE) THEN
2131 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF ( l_loop_index_' || TO_CHAR(l_loop_counter)|| ' = 0 ) THEN' ;
2132 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||'''' || 'XNP'|| ''''
2133 ||', ' ||'''' || 'XNP_MISSING_MANDATORY_DATA'||''''
2134 ||' );' ;
2135 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token( '||''''||'ELEMENT'
2136 ||''''||','||''''||g_mandatory_list||''''||' ) ;' ;
2137 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
2138 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' RAISE e_MISSING_MANDATORY_DATA ;' ;
2139 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END IF ;' || g_new_line ;
2140 END IF ;
2141 END IF ;
2142
2143 IF ((g_event_indr = 'TIMER') AND
2144 (p_element <> g_message_code)) THEN
2145
2146 g_temp_tab(g_temp_tab.COUNT + 1 ) := 'ELSE' ;
2147 g_temp_tab(g_temp_tab.COUNT + 1 ) :=' xnp_xml_utils.write_element(' || '''' || p_element|| ''','
2148 ||' p_' || p_element || ');' ;
2149 g_temp_tab(g_temp_tab.COUNT + 1 ) := 'END IF;' || g_new_line ;
2150 END IF;
2151
2152 -- Not a Parameter, so no recursive call
2153
2154 ELSE
2155 IF ( p_mandatory_flag = 'Y' ) THEN
2156 g_mandatory_check := TRUE ;
2157 g_mandatory_list := g_mandatory_list || ', '
2158 || p_element ;
2159
2160 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF ( ' || g_np_prefix || p_element|| ' IS NULL) THEN' ;
2161 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message :=' || ''''|| 'Missing Mandatory Attribute - '
2162 || p_element || ''' ;' ;
2163 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_name(' ||''''||'XNP'||''''||','
2164 ||''''||'XNP_MISSING_MANDATORY_ATTR'||''''||');' ;
2165 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' fnd_message.set_token(' ||''''||'ATTRIBUTE'||''''||','
2166 ||''''||p_element||''''||' ) ;';
2167 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := fnd_message.get ; ' ;
2168 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' RAISE e_MISSING_MANDATORY_DATA ;' ;
2169 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END IF ;' || g_new_line ;
2170 END IF ;
2171 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( ' || '''' || p_element || ''', '
2172 || g_np_prefix || p_element || ' ) ;' ;
2173 END IF ;
2174
2175 -- End parameter check
2176
2177 IF ( get_children%ISOPEN ) THEN
2178 CLOSE get_children ;
2179 END IF ;
2180
2181 EXCEPTION
2182 WHEN OTHERS THEN
2183
2184 IF (get_children%ISOPEN) THEN
2185 close get_children ;
2186 END IF ;
2187 RAISE ;
2188
2189 END generate_create_body;
2190
2191
2192 ----------------------------------------------------------------------------
2193 ----------------------------------------------------------------------------
2194 ----- Procedure: BLD_MSGEVT()
2195 ----- Purpose: Starts the package construction.
2196 ----------------------------------------------------------------------------
2197 ----------------------------------------------------------------------------
2198
2199 PROCEDURE BLD_MSGEVT(
2200 x_error_code OUT NOCOPY NUMBER
2201 ,x_error_message OUT NOCOPY VARCHAR2
2202 ,x_package_spec OUT NOCOPY VARCHAR2
2203 ,x_package_body OUT NOCOPY VARCHAR2
2204 ,x_synonym OUT NOCOPY VARCHAR2
2205 )
2206 IS
2207
2208 l_quote CHAR ;
2209 l_cursor NUMBER ;
2210 l_sql_text VARCHAR2(512) ;
2211 l_ret BOOLEAN ;
2212 l_schema VARCHAR2(1024) ;
2213 l_industry VARCHAR2(1024) ;
2214 l_status VARCHAR2(1024) ;
2215
2216 BEGIN
2217
2218 x_error_code := 0 ;
2219 x_error_message := NULL ;
2220 x_package_spec := NULL ;
2221 x_package_body := NULL ;
2222 x_synonym := NULL ;
2223
2224 cr_pkg_spec ( x_error_code,
2225 x_error_message,
2226 x_package_spec ) ;
2227
2228 IF (( x_error_code = 0 ) AND
2229 ( x_error_message IS NULL )) THEN
2230
2231 cr_pkg_body ( x_error_code,
2232 x_error_message,
2233 x_package_body ) ;
2234
2235 l_ret := FND_INSTALLATION.GET_APP_INFO(
2236 application_short_name=>'FND'
2237 ,status=>l_status
2238 ,industry=>l_industry
2239 ,oracle_schema=>l_schema
2240 );
2241
2242 IF (( x_error_code = 0 ) AND
2243 ( x_error_message IS NULL )) THEN
2244
2245 BEGIN
2246
2247 l_sql_text := 'DROP SYNONYM ' || g_message_code ;
2248 x_synonym := l_sql_text ;
2249
2250 AD_DDL.DO_DDL(
2251 applsys_schema=>l_schema
2252 ,application_short_name=>'XNP'
2253 ,statement_type=>ad_ddl.drop_synonym
2254 ,statement=>l_sql_text
2255 ,object_name=>g_message_code
2256 );
2257
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260 NULL;
2261
2262 END;
2263 --
2264 -- The following statement has been commented out, replaced with the one follows
2265 -- By Anping Wang, bug refer. 1650015
2266 -- 02/19/2001
2267 -- l_sql_text := 'CREATE SYNONYM ' || g_message_code
2268 -- || ' FOR ' || g_pkg_prefix || g_message_code
2269 -- || g_pkg_suffix ;
2270 --
2271 l_sql_text := 'CREATE SYNONYM ' || g_message_code
2272 || ' FOR ' || XNP_MESSAGE.g_pkg_prefix || g_message_code
2273 || XNP_MESSAGE.g_pkg_suffix ;
2274
2275 x_synonym := l_sql_text ;
2276
2277 AD_DDL.DO_DDL(
2278 applsys_schema=>l_schema
2279 ,application_short_name=>'XNP'
2280 ,statement_type=>ad_ddl.create_synonym
2281 ,statement=>l_sql_text
2282 ,object_name=>g_message_code
2283 );
2284
2285 END IF ;
2286
2287 END IF ;
2288
2289 EXCEPTION
2290 WHEN OTHERS THEN
2291
2292 x_error_code := SQLCODE ;
2293 x_error_message := SQLERRM ;
2294
2295 END bld_msgevt;
2296
2297 ----------------------------------------------------------------------------
2298 ----- Procedure: CR_SEND_BODY()
2299 ----- Purpose: Creates the body for procedure SEND().
2300 ----------------------------------------------------------------------------
2301
2302 PROCEDURE cr_send_body(
2303 X_SEND_BODY OUT NOCOPY VARCHAR2
2304 )
2305 IS
2306 l_send_sig VARCHAR2(16000) ;
2307
2308 BEGIN
2309
2310 cr_send_signature ( l_send_sig ) ;
2311
2312 x_send_body := l_send_sig || ' IS' || g_new_line || g_new_line ;
2313
2314 x_send_body := x_send_body
2315 || 'l_recipient_name VARCHAR2(80);' || g_new_line ;
2316
2317 IF (UPPER(g_queue_name) = 'XNP_OUT_MSG_Q') THEN
2318
2319 x_send_body := x_send_body
2320 || 'l_ack_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;'
2321 || g_new_line
2322 || 'l_ack_code VARCHAR2(40);' || g_new_line
2323 || 'l_error_code NUMBER ;'
2324 || 'l_error_message VARCHAR2(512);' || g_new_line
2325 || 'l_ack_msg VARCHAR2(32767) ;' || g_new_line ;
2326
2327 END IF ;
2328
2329 x_send_body := x_send_body || g_new_line
2330 || 'BEGIN' || g_new_line || g_new_line ;
2331
2332 x_send_body := x_send_body
2333 || ' x_error_code := 0;' || g_new_line
2334 || ' x_error_message := NULL ;' || g_new_line || g_new_line ;
2335
2336 x_send_body := x_send_body
2337 || ' l_recipient_name := p_recipient_name ;' || g_new_line
2338 || ' IF (l_recipient_name IS NULL) THEN' || g_new_line
2339 || ' l_recipient_name := p_consumer_name ;' || g_new_line
2340 || ' END IF;' || g_new_line ;
2341
2342 x_send_body := x_send_body
2343 || ' ' || 'PUBLISH (' || g_new_line ;
2344
2345 FOR current_rec IN get_parameter_data
2346 LOOP
2347 x_send_body := x_send_body || ' ' || g_np_prefix
2348 || current_rec.name
2349 || '=>' || g_np_prefix || current_rec.name ;
2350
2351 x_send_body := x_send_body || g_comma || g_new_line ;
2352
2353 END LOOP ;
2354
2355 x_send_body := x_send_body
2356 || ' x_message_id=>x_message_id,' || g_new_line
2357 || ' x_error_code=>x_error_code,' || g_new_line
2358 || ' x_error_message=>x_error_message,' || g_new_line
2359 || ' p_consumer_list=>p_consumer_name,' || g_new_line
2360 || ' p_sender_name=>p_sender_name,' || g_new_line
2361 || ' p_recipient_list=>l_recipient_name,' || g_new_line
2362 || ' p_version=>p_version,' || g_new_line
2363 || ' p_reference_id=>p_reference_id,' || g_new_line
2364 || ' p_opp_reference_id=>p_opp_reference_id,' || g_new_line
2365 || ' p_order_id=>p_order_id,' || g_new_line
2366 || ' p_wi_instance_id=>p_wi_instance_id,' || g_new_line
2367 || ' p_fa_instance_id=>p_fa_instance_id ) ;' || g_new_line ;
2368
2369 IF (g_ack_reqd_flag = 'Y') THEN
2370
2371 x_send_body := x_send_body || g_new_line
2372 || '/****' || g_new_line
2373 || ' Get an ACK back for the out going message,' || g_new_line
2374 || ' Remove the message if the ACK is not received'
2375 || g_new_line
2376 || ' within the speicified time. ' || g_new_line
2377 || '****/' || g_new_line || g_new_line ;
2378
2379 x_send_body := x_send_body
2380 || ' IF (x_error_code = 0) THEN' || g_new_line || g_new_line
2381 || ' XNP_MESSAGE.POP ( '
2382 || ' p_queue_name => xnp_event.c_inbound_msg_q, '
2383 || g_new_line
2384 || ' x_msg_header => l_ack_header,' || g_new_line
2385 || ' x_body_text => l_ack_msg,' || g_new_line
2386 || ' x_error_code => x_error_code,' || g_new_line
2387 || ' x_error_message => x_error_message,' || g_new_line
2388 -- || ' p_correlation_id => TO_CHAR(x_message_id) ) ;'
2389 || ' p_correlation_id => l_msg_header.message_code, '
2390 || g_new_line || g_new_line
2391 || ' IF (x_error_code <> XNP_ERRORS.G_DEQUEUE_TIMEOUT )THEN'
2392 || g_new_line
2393 || ' XNP_XML_UTILS.DECODE(l_ack_msg, ' || ''''
2394 || 'CODE' || ''',' || 'l_ack_code) ;'
2395 || g_new_line
2396 || ' XNP_XML_UTILS.DECODE(l_ack_msg, ' || ''''
2397 || 'DESCRIPTION' || ''',' || 'x_error_message) ;'
2398 || g_new_line
2399 || ' x_error_code := TO_NUMBER(l_ack_code) ;' || g_new_line
2400 || ' ELSE' || g_new_line
2401 || ' XNP_MESSAGE.UPDATE_STATUS(x_message_id, '
2402 || '''' || 'TIMEOUT' || ''');' || g_new_line
2403 || ' END IF ;' || g_new_line || g_new_line
2404 || ' END IF;' || g_new_line || g_new_line ;
2405
2406 END IF ;
2407
2408 x_send_body := x_send_body
2409 || 'END ;' ;
2410
2411 EXCEPTION
2412 WHEN OTHERS THEN
2413 IF (get_parameter_data%ISOPEN) THEN
2414 CLOSE get_parameter_data ;
2415 END IF ;
2416 RAISE ;
2417
2418 END cr_send_body ;
2419
2420 ----------------------------------------------------------------------------
2421 ----- Procedure: CR_START_SIGNATURE()
2422 ----- Purpose: Creates the signature for procedure START().
2423 ----- Applicable only to timers.
2424 ----------------------------------------------------------------------------
2425
2426 PROCEDURE cr_start_signature(
2427 x_start_sig OUT NOCOPY VARCHAR2
2428 )
2429 IS
2430
2431 l_quote char ;
2432
2433 BEGIN
2434
2435 x_start_sig := x_start_sig || 'PROCEDURE FIRE ( ' ;
2436
2437 x_start_sig := x_start_sig
2438 || ' x_timer_id OUT NUMBER,' || g_new_line
2439 || ' x_timer_contents OUT VARCHAR2,' || g_new_line
2440 || ' x_error_code OUT NUMBER,' || g_new_line
2441 || ' x_error_message OUT VARCHAR2,' || g_new_line
2442 || ' p_sender_name IN VARCHAR2 DEFAULT NULL,' || g_new_line
2443 || ' p_recipient_list IN VARCHAR2 DEFAULT NULL,' || g_new_line
2444 || ' p_version IN NUMBER DEFAULT 1,' || g_new_line
2445 || ' p_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
2446 || ' p_opp_reference_id IN VARCHAR2 DEFAULT NULL,' || g_new_line
2447 || ' p_order_id IN NUMBER DEFAULT NULL,' || g_new_line
2448 || ' p_wi_instance_id IN NUMBER DEFAULT NULL,' || g_new_line
2449 || ' p_fa_instance_id IN NUMBER DEFAULT NULL ) ' ;
2450
2451 END cr_start_signature;
2452
2453 ----------------------------------------------------------------------------
2454 ----- Procedure: CR_START_BODY()
2455 ----- Purpose: generates code for procedure VALIDATE()
2456 ----------------------------------------------------------------------------
2457
2458 PROCEDURE cr_start_body(
2459 X_START_BODY OUT NOCOPY VARCHAR2
2460 )
2461 IS
2462
2463 l_start_sig VARCHAR2(16000) ;
2464 l_start_body VARCHAR2(32767) ;
2465
2466 BEGIN
2467
2468 cr_start_signature ( l_start_sig ) ;
2469
2470 l_start_body := l_start_sig ||
2471 '
2472 IS
2473
2474 l_msg_header xnp_message.msg_header_rec_type ;
2475 l_msg_text VARCHAR2(32767);
2476 ' || g_new_line || g_new_line;
2477
2478 l_start_body := l_start_body ||
2479 '
2480 BEGIN
2481 x_error_code := 0;
2482 x_error_message := NULL;
2483
2484 CREATE_MSG (x_msg_header => l_msg_header,
2485 x_msg_text => l_msg_text,
2486 x_error_code => x_error_code,
2487 x_error_message => x_error_message,
2488 p_sender_name => p_sender_name,
2489 p_recipient_list => p_recipient_list,
2490 p_version => p_version,
2491 p_reference_id => p_reference_id,
2492 p_opp_reference_id => p_reference_id,
2493 p_order_id => p_order_id,
2494 p_wi_instance_id => p_wi_instance_id,
2495 p_fa_instance_id => p_fa_instance_id );
2496
2497 IF (x_error_code = 0) THEN
2498 xnp_timer.start_timer(l_msg_header,
2499 l_msg_text,
2500 x_error_code,
2501 x_error_message );
2502 x_timer_id := l_msg_header.message_id ;
2503 x_timer_contents := l_msg_text;
2504 END IF;
2505
2506 END ;
2507 ' || g_new_line ;
2508
2509 x_start_body := l_start_body ;
2510
2511 END cr_start_body;
2512
2513 ---------------------------------------------------------------------------
2514 ----- Procedure: CR_CREATE_BODY()
2515 ----- Purpose: Creates the body for procedure CREATE().
2516 ---------------------------------------------------------------------------
2517
2518 PROCEDURE cr_create_body(
2519 x_create_body OUT NOCOPY VARCHAR2
2520 )
2521 IS
2522 l_create_sig VARCHAR2(16000) ;
2523
2524 l_child XNP_MSG_ELEMENTS.name%TYPE ;
2525 l_datatype XNP_MSG_ELEMENTS.element_datatype%TYPE ;
2526 l_mandatory_indr XNP_MSG_ELEMENTS.mandatory_flag%TYPE ;
2527 l_data_source_type XNP_MSG_STRUCTURES.data_source_type%TYPE ;
2528 l_data_source XNP_MSG_STRUCTURES.data_source%TYPE ;
2529 l_data_source_ref XNP_MSG_STRUCTURES.data_source_reference%TYPE ;
2530 l_cardinality XNP_MSG_STRUCTURES.cardinality%TYPE ;
2531 l_parameter_flag XNP_MSG_ELEMENTS.parameter_flag%TYPE ;
2532 l_fnd_message VARCHAR2(4000) ;
2533 l_msg_type VARCHAR2(10) ;
2534
2535 CURSOR get_msg_type IS
2536 SELECT msg_type
2537 FROM xnp_msg_types_b
2538 WHERE msg_code = g_message_code ;
2539
2540 CURSOR get_children ( parent_name IN VARCHAR2 ) IS
2541 SELECT MET.name,
2542 MET.element_datatype,
2543 MET.mandatory_flag,
2544 MSE.data_source_type,
2545 MSE.data_source,
2546 MSE.data_source_reference,
2547 MSE.cardinality,
2548 MET.parameter_flag
2549 FROM xnp_msg_elements MET,
2550 xnp_msg_structures MSE
2551 WHERE MSE.msg_code = g_message_code
2552 AND MSE.child_element_id = MET.msg_element_id
2553 AND MSE.parent_element_id = ( SELECT msg_element_id
2554 FROM xnp_msg_elements MET1
2555 WHERE MET1.name = parent_name
2556 AND MET1.msg_code = g_message_code )
2557 ORDER BY MSE.sequence_in_parent ;
2558
2559 e_INVALID_MSG_CODE EXCEPTION ;
2560
2561 BEGIN
2562 g_temp_tab.DELETE;
2563 g_create_body := NULL ;
2564 g_decl_section := NULL ;
2565 g_excep_section := NULL ;
2566 g_mandatory_list := NULL ;
2567
2568 OPEN get_msg_type ;
2569 FETCH get_msg_type INTO l_msg_type ;
2570
2571 IF (get_msg_type%NOTFOUND ) THEN
2572 RAISE e_INVALID_MSG_CODE ;
2573 END IF ;
2574 CLOSE get_msg_type ;
2575
2576 OPEN get_children ( 'MESSAGE' ) ;
2577 FETCH get_children INTO
2578 l_child,
2579 l_datatype,
2580 l_mandatory_indr,
2581 l_data_source_type,
2582 l_data_source,
2583 l_data_source_ref,
2584 l_cardinality,
2585 l_parameter_flag ;
2586
2587 IF ( get_children%NOTFOUND ) THEN
2588 CLOSE get_children ;
2589 RAISE e_INVALID_MSG_CODE ;
2590 END IF ;
2591
2592 CLOSE get_children ;
2593
2594 cr_create_signature ( l_create_sig ) ;
2595 l_create_sig := l_create_sig || ' IS' || g_new_line ;
2596
2597 g_excep_section := g_excep_section || g_new_line
2598 || '/* handle exceptions */' || g_new_line || ' EXCEPTION' || g_new_line ;
2599
2600 g_decl_section := g_decl_section
2601 || ' e_MISSING_MANDATORY_DATA EXCEPTION ;' || g_new_line ;
2602 g_decl_section := g_decl_section
2603 || ' e_NO_DESTINATION EXCEPTION ;' || g_new_line ;
2604
2605 g_decl_section := g_decl_section
2606 || ' l_xml_body VARCHAR2(32767) ;' || g_new_line ;
2607
2608 g_decl_section := g_decl_section
2609 || ' l_xml_doc VARCHAR2(32767) ;' || g_new_line ;
2610
2611 g_decl_section := g_decl_section
2612 || ' l_xml_header VARCHAR2(32767) ;' || g_new_line ;
2613
2614 g_decl_section := g_decl_section
2615 || ' l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;' || g_new_line ;
2616
2617
2618 /* Build create body and add it to the global table of records */
2619
2620 g_temp_tab(g_temp_tab.COUNT + 1 ) := 'BEGIN' ;
2621
2622 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_code := 0 ;' ;
2623 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_error_message := NULL ;' ;
2624
2625 -- Header Start
2626
2627 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.initialize_doc ( ) ;' || g_new_line;
2628
2629 -- construct XML message header
2630
2631 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/*construct the XML header */' || g_new_line ;
2632 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/* retreive the next message ID */' || g_new_line ;
2633
2634 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_MESSAGE.get_sequence ( l_msg_header.message_id ) ;'
2635 || g_new_line;
2636 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' IF (p_reference_id IS NULL) THEN' ;
2637 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.reference_id := l_msg_header.message_id ;';
2638 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' ELSE' ;
2639 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.reference_id := p_reference_id ;';
2640 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' END IF ;' ;
2641
2642 -- Append parameters to header
2643
2644 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/* append header parameters to make header */'
2645 || g_new_line ;
2646
2647 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_element ( '|| ''''
2648 || 'MESSAGE_ID' || ''','
2649 || 'l_msg_header.message_id'
2650 || ' ) ;' ;
2651
2652 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2653 || '''' || 'REFERENCE_ID' || ''','
2654 || 'l_msg_header.reference_id'
2655 || ' ) ;' ;
2656
2657 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.opp_reference_id := p_opp_reference_id ;';
2658
2659 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2660 || '''' || 'OPP_REFERENCE_ID' || ''','
2661 || 'l_msg_header.opp_reference_id ) ;' ;
2662
2663 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.message_code := ' || ''''
2664 || g_message_code || ''' ;' ;
2665
2666 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2667 || '''' || 'MESSAGE_CODE' || ''','
2668 || 'l_msg_header.message_code' || ' ) ;' ;
2669
2670 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.version := p_version ;' ;
2671
2672 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2673 || '''' || 'VERSION' || ''','
2674 || 'l_msg_header.version' || ' ) ;' ;
2675
2676 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.creation_date := SYSDATE ;' ;
2677 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.recipient_name := p_recipient_list ;' ;
2678
2679 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_element ( '
2680 || '''' || 'CREATION_DATE'
2681 || ''','|| 'l_msg_header.creation_date' || ' ) ;' ;
2682
2683 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.sender_name := p_sender_name ;' ;
2684
2685 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2686 || '''' || 'SENDER_NAME' || ''','
2687 || 'l_msg_header.sender_name' || ' ) ;' ;
2688
2689 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2690 || '''' || 'RECIPIENT_NAME' || ''','
2691 || 'l_msg_header.recipient_name' || ' ) ;' ;
2692
2693 IF (g_event_indr = 'MSG') THEN
2694 IF (UPPER(g_queue_name) = UPPER(XNP_EVENT.CC_OUTBOUND_MSG_Q)) THEN
2695 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.direction_indr := '
2696 || ''''|| 'O' || ''' ;' ;
2697 ELSE
2698 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.direction_indr := '
2699 || ''''|| 'I' || ''' ;' ;
2700 END IF ;
2701 ELSE
2702 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.direction_indr := '
2703 || ''''|| 'E' || ''' ;' ;
2704 END IF ;
2705
2706 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.order_id := p_order_id ;' ;
2707 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.wi_instance_id := p_wi_instance_id ;' ;
2708 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' l_msg_header.fa_instance_id := p_fa_instance_id ;'
2709 ||g_new_line ;
2710
2711 FOR current_rec IN get_parameter_data
2712 LOOP
2713 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_leaf_element ( '
2714 || '''' || current_rec.name || ''', '
2715 || g_np_prefix || current_rec.name
2716 || ' );' ;
2717
2718 END LOOP ;
2719
2720
2721 -- End Header
2722 -- Get Header for out parameter assignment
2723
2724 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/* retreieve the XML header */' || g_new_line ;
2725
2726 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.get_document ( l_xml_header ) ;'
2727 || g_new_line ;
2728
2729 ---- Start complete xml doc.
2730 ---- Intialize Doc. Write header and start creating body.
2731
2732 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/* append the XML headerto message */'
2733 || g_new_line ;
2734
2735 IF (g_dtd_url IS NOT NULL) THEN
2736
2737 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.initialize_doc ( '
2738 || '''' || g_message_code || ''', '|| ''''
2739 || g_dtd_url
2740 || ''');' ;
2741
2742 ELSE
2743 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.initialize_doc ( ) ;' ;
2744 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.xml_decl ;' ;
2745
2746 END IF ;
2747
2748 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.begin_segment ( '
2749 || ''''|| 'MESSAGE'
2750 || ''') ;' ;
2751
2752 IF l_msg_type NOT IN ('MSG_NOHEAD','EVT_NOHEAD') THEN
2753 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.write_element( '
2754 || ''''|| 'HEADER'
2755 || ''', l_xml_header );' ;
2756 END IF ;
2757
2758 --Body Start
2759
2760 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/* construct the message body */' || g_new_line ;
2761
2762 IF l_datatype <> 'DUMMY' THEN
2763 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.begin_segment ( '
2764 || '''' || l_child || '''' || ' ) ;'
2765 || g_new_line ;
2766 END IF ;
2767
2768 generate_create_body ( l_child,
2769 l_datatype,
2770 l_mandatory_indr,
2771 l_data_source_type,
2772 l_data_source,
2773 l_data_source_ref,
2774 l_cardinality,
2775 l_parameter_flag ) ;
2776
2777 IF l_datatype <> 'DUMMY' THEN
2778 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.end_segment ( '
2779 || ''''|| l_child || ''''
2780 || ' ) ;' ;
2781 END IF ;
2782
2783 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.end_segment ( ' || ''''
2784 || 'MESSAGE' || ''') ;' ;
2785 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' XNP_XML_UTILS.get_document( l_xml_doc ) ;'
2786 || g_new_line ;
2787 g_temp_tab(g_temp_tab.COUNT + 1 ) := '/* assign the header and msg text to output parameters */'
2788 || g_new_line ;
2789 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_msg_header := l_msg_header ;' ;
2790 g_temp_tab(g_temp_tab.COUNT + 1 ) := ' x_msg_text := l_xml_doc ;' || g_new_line ;
2791
2792 g_excep_section := g_excep_section
2793 || ' WHEN e_MISSING_MANDATORY_DATA THEN' || g_new_line
2794 || ' x_error_code := XNP_ERRORS.G_MISSING_MANDATORY_DATA ;'
2795 || g_new_line
2796 || ' WHEN OTHERS THEN' || g_new_line
2797 || ' x_error_code := SQLCODE ;' || g_new_line
2798 || ' x_error_message := ' || '''' || g_message_code
2799 || '.create_msg()::'''
2800 || ' || SQLERRM ;' || g_new_line;
2801
2802 g_excep_section := g_excep_section || 'END ;'||g_new_line || g_new_line ;
2803
2804 /* Add create signature , excep section , decl section to the table of records */
2805
2806 xdp_utilities.build_pkg(l_create_sig||g_new_line || g_new_line);
2807 xdp_utilities.build_pkg(g_decl_section||g_new_line || g_new_line);
2808
2809 FOR i IN 1..g_temp_tab.COUNT
2810 LOOP
2811 xdp_utilities.build_pkg(g_temp_tab(i));
2812 END LOOP;
2813
2814 xdp_utilities.build_pkg(g_excep_section);
2815
2816 EXCEPTION
2817 WHEN e_INVALID_MSG_CODE THEN
2818 IF ( get_children%ISOPEN ) THEN
2819 CLOSE get_children ;
2820 END IF ;
2821 FND_MESSAGE.set_name ('XNP', 'NO_MSG_STRUCTURE') ;
2822 l_fnd_message := FND_MESSAGE.get ;
2823 RAISE_APPLICATION_ERROR (
2824 XNP_ERRORS.G_NO_MSG_STRUCTURE, l_fnd_message) ;
2825 WHEN OTHERS THEN
2826 IF (get_parameter_data%ISOPEN) THEN
2827 CLOSE get_parameter_data ;
2828 END IF ;
2829 IF ( get_children%ISOPEN ) THEN
2830 CLOSE get_children ;
2831 END IF ;
2832 RAISE ;
2833
2834 END cr_create_body;
2835 -------------------------------
2836 -- Package initialization code
2837 ------------------------------
2838
2839 BEGIN
2840
2841 DECLARE
2842 l_ack_reqd_flag VARCHAR2(2) := NULL;
2843
2844 BEGIN
2845 FND_PROFILE.GET( NAME => 'ACK_REQD_FLAG',
2846 VAL => l_ack_reqd_flag ) ;
2847 IF (l_ack_reqd_flag IS NOT NULL) THEN
2848 g_ack_reqd_flag := l_ack_reqd_flag ;
2849 END IF ;
2850
2851 END ;
2852
2853 END xnp_msg_builder;