1 PACKAGE BODY ecx_inbound_new as
2 --$Header: ECXINNB.pls 120.2.12000000.5 2007/07/11 12:20:19 susaha ship $
3
4 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
5 l_statement PLS_INTEGER := ecx_debug.g_statement;
6 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
7 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
8 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
9 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
10 p_current_element_pos PLS_INTEGER:=-1;
11
12 TYPE attribute_rec is RECORD
13 (
14 attribute_name varchar2(256),
15 element_tag_name varchar2(256),
16 value varchar2(4000)
17 );
18
19 TYPE attr_tbl is TABLE of attribute_rec index by BINARY_INTEGER;
20 -- Define the local variable for storing the attributes with the values **/
21 l_attr_rec attr_tbl;
22
23 TYPE l_stack is table of pls_integer index by binary_integer;
24
25 l_docLogsAttrSet boolean;
26 l_level_stack l_stack;
27 l_next_pos pls_integer;
28 node_info_stack node_info_table;
29 Load_XML_Exception Exception;
30
31 procedure clean_up_tables is
32 i_method_name varchar2(2000) := 'ecx_inbound_new.clean_up_tables';
33
34 begin
35 l_level_stack.DELETE;
36 ecx_print_local.i_tmpxml.DELETE;
37 ecx_print_local.l_node_stack.DELETE;
38 ecx_utils.g_node_tbl.DELETE;
39
40 exception
41 when others then
42 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.CLEAN_UP_TABLES');
43 if(l_unexpectedEnabled) then
44 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
45 end if;
46 raise ecx_utils.program_exit;
47 end clean_up_tables;
48
49
50 procedure get_element_value (
51 p_nodeList IN ECX_NODE_TBL_TYPE,
52 p_count IN pls_integer,
53 p_elem_name IN varchar2,
54 x_found OUT NOCOPY boolean,
55 x_elem_value OUT NOCOPY varchar2
56 ) is
57
58 i_method_name varchar2(2000) := 'ecx_inbound_new.get_element_value';
59 begin
60 x_found := false;
61 x_elem_value := null;
62
63 for i in 1..p_count loop
64 if (p_nodeList(i).name = p_elem_name) then
65 x_elem_value := p_nodeList(i).value;
66 x_found := true;
67 exit;
68 end if;
69
70 end loop;
71
72 exception
73 when others then
74 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.GET_ELEMENT_VALUE');
75 if(l_unexpectedEnabled) then
76 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
77 end if;
78 raise ecx_utils.program_exit;
79 end get_element_value;
80
81
82 function setDocLogsAttributes (
83 p_nodeList IN ECX_NODE_TBL_TYPE,
84 p_count IN pls_integer
85 ) return boolean is
86
87 i_method_name varchar2(2000) := 'ecx_inbound_new.setDocLogsAttributes';
88 cursor get_attributes
89 (
90 p_standard_id IN pls_integer
91 )
92 is
93 select attribute_name,
94 element_tag_name
95 from ecx_standard_attributes esa,
96 ecx_standards es
97 where es.standard_id = p_standard_id
98 and esa.standard_id = es.standard_id;
99
100 i_string varchar2(2000) := ' update ecx_doclogs set ';
101 i_found boolean;
102 i_update boolean;
103 i_value varchar2(4000);
104 i_single varchar2(3):= '''';
105 begin
106
107 if (l_procedureEnabled) then
108 ecx_debug.push(i_method_name);
109 end if;
110 i_found := false;
111 i_update := false;
112
113 if ecx_utils.g_standard_id is null
114 then
115 if (l_procedureEnabled) then
116 ecx_debug.pop(i_method_name);
117 end if;
118 return true;
119 end if;
120
121 /** Get all the Attributes and capture the value **/
122 for c1 in get_Attributes(p_standard_id => ecx_utils.g_standard_id)
123 loop
124 if (c1.attribute_name is not null and c1.element_tag_name is not null) then
125 get_element_value (p_nodeList, p_count, c1.element_tag_name, i_found, i_value);
126
127 if (i_found) then
128 i_update := true;
129 l_attr_rec(l_attr_rec.COUNT + 1).attribute_name := c1.attribute_name;
130 l_attr_rec(l_attr_rec.COUNT).element_tag_name := c1.element_tag_name;
131 l_attr_rec(l_attr_rec.COUNT).value := i_value;
132
133 -- Search for the attribute in the XML File
134 if(l_statementEnabled) then
135 ecx_debug.log(l_statement,l_attr_rec(l_attr_rec.COUNT).attribute_name,
136 l_attr_rec(l_attr_rec.COUNT).value,i_method_name);
137 end if;
138 i_string := i_string ||' '||l_attr_rec(l_attr_rec.COUNT).attribute_name ||
139 ' = '|| i_single||l_attr_rec(l_attr_rec.COUNT).value || i_single||' ,';
140 end if;
141 end if;
142 end loop;
143
144 if i_update
145 then
146 /** remove the last , and put the statement for the where clause **/
147 i_string := substr(i_string,1,length(i_string)-1);
148 i_string := i_string || ' where msgid = HEXTORAW('||i_single||ecx_utils.g_msgid||i_single||')';
149 if(l_statementEnabled) then
150 ecx_debug.log(l_statement,'i_string',i_string,i_method_name);
151 end if;
152 execute immediate i_string;
153 end if;
154 if (l_procedureEnabled) then
155 ecx_debug.pop(i_method_name);
156 end if;
157
158 return i_update;
159
160 exception
161 WHEN ecx_utils.program_exit then
162 if (l_procedureEnabled) then
163 ecx_debug.pop(i_method_name);
164 end if;
165 raise ecx_utils.program_exit;
166
167 when others then
168 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.setDocLogsAttributes');
169 if(l_unexpectedEnabled) then
170 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
171 end if;
172 if (l_procedureEnabled) then
173 ecx_debug.pop(i_method_name);
174 end if;
175 raise ecx_utils.program_exit;
176 end setDocLogsAttributes;
177
178
179 procedure print_stack is
180 i_method_name varchar2(2000) := 'ecx_inbound_new.print_stack';
181 begin
182 if(l_statementEnabled) then
183 ecx_debug.log(l_statement,'Level Stack Status','====',i_method_name);
184 end if;
185 for i in 1..l_level_stack.COUNT
186 loop
187 if(l_statementEnabled) then
188 ecx_debug.log(l_statement,l_level_stack(i),i_method_name);
189 end if;
190 end loop;
191 exception
192 when others then
193 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PRINT_STACK');
194 if(l_unexpectedEnabled) then
195 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
196 end if;
197 raise ecx_utils.program_exit;
198 end print_stack;
199
200
201 procedure popStack
202 (
203 p_level in pls_integer
204 ) is
205 i_method_name varchar2(2000) := 'ecx_inbound_new.popstack';
206
207 begin
208 if (l_procedureEnabled) then
209 ecx_debug.push(i_method_name);
210 end if;
211 if(l_statementEnabled) then
212 ecx_debug.log(l_statement,'p_level', p_level,i_method_name);
213 print_stack;
214 end if;
215
216 if l_level_stack.COUNT = 0
217 then
218 if(l_statementEnabled) then
219 ecx_debug.log(l_statement,'Stack Error. Nothing to pop','xxxxx',i_method_name);
220 end if;
221 if (l_procedureEnabled) then
222 ecx_debug.pop(i_method_name);
223 end if;
224 return;
225 end if;
226
227 --Post Process and then pop.
228 ecx_inbound.process_data(l_level_stack(l_level_stack.COUNT), 30, p_level);
229
230 l_level_stack.DELETE(l_level_stack.COUNT);
231
232 if(l_statementEnabled) then
233 print_stack;
234 end if;
235
236 if (l_procedureEnabled) then
237 ecx_debug.pop(i_method_name);
238 end if;
239
240 exception
241 WHEN ecx_utils.program_exit then
242 if (l_procedureEnabled) then
243 ecx_debug.pop(i_method_name);
244 end if;
245 raise ecx_utils.program_exit;
246
247 when others then
248 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.popStack');
249 if(l_unexpectedEnabled) then
250 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
251 end if;
252 if (l_procedureEnabled) then
253 ecx_debug.pop(i_method_name);
254 end if;
255 raise ecx_utils.program_exit;
256 end popStack;
257
258
259 procedure pushStack
260 (
261 i in pls_integer
262 ) is
263 i_method_name varchar2(2000) := 'ecx_inbound_new.pushstack';
264 begin
265 if (l_procedureEnabled) then
266 ecx_debug.push(i_method_name);
267 end if;
268 if(l_statementEnabled) then
269 ecx_debug.log(l_statement,i,i_method_name);
270 print_stack;
271 end if;
272
273 if (l_level_stack.COUNT = 0) or
274 (i > l_level_stack(l_level_stack.COUNT)) then
275 l_level_stack(l_level_stack.COUNT+1) := i;
276
277 if(l_statementEnabled) then
278 print_stack;
279 end if;
280
281 if (l_procedureEnabled) then
282 ecx_debug.pop(i_method_name);
283 end if;
284 return;
285 end if;
286
287 if (i = l_level_stack(l_level_stack.COUNT)) then
288 popStack(i);
289 l_level_stack(l_level_stack.COUNT+1) := i;
290 if (l_procedureEnabled) then
291 ecx_debug.pop(i_method_name);
292 end if;
293 return;
294 end if;
295
296 if (l_level_stack.COUNT <> 0) then
297 while (i <= l_level_stack(l_level_stack.COUNT)) loop
298 popStack(i);
299 exit when l_level_stack.COUNT = 0;
300 end loop;
301 end if;
302
303 -- Push the value
304 l_level_stack(l_level_stack.COUNT+1):=i;
305
306 if(l_statementEnabled) then
307 print_stack;
308 end if;
309
310 if (l_procedureEnabled) then
311 ecx_debug.pop(i_method_name);
312 end if;
313
314 exception
315 WHEN ecx_utils.program_exit then
316 if (l_procedureEnabled) then
317 ecx_debug.pop(i_method_name);
318 end if;
319 raise ecx_utils.program_exit;
320
321 when others then
322 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.pushStack');
323 if(l_unexpectedEnabled) then
324 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
325 end if;
326 if (l_procedureEnabled) then
327 ecx_debug.pop(i_method_name);
328 end if;
329 raise ecx_utils.program_exit;
330 end pushStack;
331
332
333 procedure popall is
334 i_method_name varchar2(2000) := 'ecx_inbound_new.popall';
335 begin
336 if (l_procedureEnabled) then
337 ecx_debug.push(i_method_name);
338 end if;
339
340 if(l_statementEnabled) then
341 print_stack;
342 end if;
343
344 while ( l_level_stack.COUNT > 0)
345 loop
346 popStack(0);
347 end loop;
348
349 if(l_statementEnabled) then
350 print_stack;
351 end if;
352
353 if (l_procedureEnabled) then
354 ecx_debug.pop(i_method_name);
355 end if;
356 exception
357 WHEN ecx_utils.program_exit then
358 if (l_procedureEnabled) then
359 ecx_debug.pop(i_method_name);
360 end if;
361 raise ecx_utils.program_exit;
362
363 when others then
364 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.popall');
365 if(l_unexpectedEnabled) then
366 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
367 end if;
368 if (l_procedureEnabled) then
369 ecx_debug.pop(i_method_name);
370 end if;
371 raise ecx_utils.program_exit;
372 end popall;
373
374
375 procedure get_cond_node_value(
376 p_nodeList IN ECX_NODE_TBL_TYPE,
377 p_nodeStartIndex IN pls_integer,
378 p_node_name IN Varchar2,
379 p_xmlParentIndex IN pls_integer,
380 x_node_value OUT NOCOPY Varchar2
381 ) is
382 i_method_name varchar2(2000) := 'ecx_inbound_new.get_cond_node_value';
383 begin
384 if (l_procedureEnabled) then
385 ecx_debug.push(i_method_name);
386 end if;
387 x_node_value := null;
388
389 if(l_statementEnabled) then
390 ecx_debug.log(l_statement,'p_nodeStartIndex', p_nodeStartIndex,i_method_name);
391 ecx_debug.log(l_statement, 'p_xmlParentIndex', p_xmlParentIndex,i_method_name);
392 end if;
393
394 for i in p_nodeStartIndex+1..p_nodeList.COUNT loop
395 if (p_nodeList(i).parentIndex > p_xmlParentIndex) then
396 if (p_nodeList(i).name = p_node_name) then
397 x_node_value := p_nodeList(i).value;
398 if(l_statementEnabled) then
399 ecx_debug.log(l_statement,'value', p_nodeList(i).value,i_method_name);
400 end if;
401 exit;
402 end if;
403 else
404 exit;
405 end if;
406 end loop;
407
408 if(l_statementEnabled) then
409 ecx_debug.log(l_statement, 'cond node value', x_node_value,i_method_name);
410 end if;
411 if (l_procedureEnabled) then
412 ecx_debug.pop(i_method_name);
413 end if;
414
415 exception
416 WHEN ecx_utils.program_exit then
417 if (l_procedureEnabled) then
418 ecx_debug.pop(i_method_name);
419 end if;
420 raise ecx_utils.program_exit;
421
422 when others then
423 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.GET_COND_NODE_VALUE');
424 if(l_unexpectedEnabled) then
425 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
426 end if;
427 if (l_procedureEnabled) then
428 ecx_debug.pop(i_method_name);
429 end if;
430 raise ecx_utils.program_exit;
431 end get_cond_node_value;
432
433
434 function get_node_id (
435 p_nodeList IN ECX_NODE_TBL_TYPE,
436 p_nodeListIndex IN pls_integer,
437 p_xmlParentNodeIndex IN pls_integer,
438 p_node_name IN Varchar2,
439 p_parent_node_id IN pls_integer,
440 p_parent_node_pos IN pls_integer,
441 p_occur IN pls_integer,
442 p_node_value IN varchar2,
443 x_node_pos OUT NOCOPY pls_integer
444 ) return pls_integer IS
445
446 i_method_name varchar2(2000) := 'ecx_inbound_new.get_node_id';
447
448 i pls_integer;
449 l_node_id pls_integer := -1;
450 l_cond_node_value Varchar2(4000) := null;
451 p_cond_node Varchar2(200);
452 p_cond_node_type pls_integer;
453 i_parent_node_pos pls_integer;
454
455 BEGIN
456 if (l_procedureEnabled) then
457 ecx_debug.push(i_method_name);
458 end if;
459
460 if(l_statementEnabled) then
461 ecx_debug.log(l_statement, 'p_node_name', p_node_name,i_method_name);
462 ecx_debug.log(l_statement, 'p_parent_node_id', p_parent_node_id,i_method_name);
463 ecx_debug.log(l_statement, 'p_parent_node_pos', p_parent_node_pos,i_method_name);
464 ecx_debug.log(l_statement, 'p_occur', p_occur,i_method_name);
465 ecx_debug.log(l_statement, 'p_node_value', p_node_value,i_method_name);
466 ecx_debug.log(l_statement, 'No of Elements in Source', ecx_utils.g_source.count,i_method_name);
467 end if;
468
469 x_node_pos := -1;
470 i_parent_node_pos := p_parent_node_pos;
471
472 if (p_parent_node_id >=0) then
473
474 if(p_parent_node_pos < 0) then
475 i_parent_node_pos := 0;
476 end if;
477
478 for i in i_parent_node_pos..ecx_utils.g_source.last
479 loop
480 if (ecx_utils.g_source(i).attribute_name = p_node_name) and
481 (ecx_utils.g_source(i).parent_attribute_id = p_parent_node_id)
482 then
483 p_cond_node := ecx_utils.g_source(i).cond_node;
484 p_cond_node_type := ecx_utils.g_source(i).cond_node_type;
485
486 if (p_cond_node is not null) then
487
488 if(l_statementEnabled) then
489 ecx_debug.log(l_statement,'p_cond_node', p_cond_node,i_method_name);
490 ecx_debug.log(l_statement, 'p_cond_node_type', p_cond_node_type,
491 i_method_name);
492 end if;
493 if (p_cond_node <> p_node_name) then
494 get_cond_node_value(p_nodeList,
495 p_nodeListIndex,
496 p_cond_node,
497 p_xmlParentNodeIndex,
498 l_cond_node_value);
499 else
500 l_cond_node_value := p_node_value;
501 end if;
502
503 -- find the mapping that match the condition.
504 if (l_cond_node_value = ecx_utils.g_source(i).cond_value) then
505 if ( p_parent_node_id = ecx_utils.g_source(i).parent_attribute_id )
506 then
507 x_node_pos := i;
508 l_node_id := ecx_utils.g_source(i).attribute_id;
509 exit;
510 end if;
511 end if;
512
513 -- there is no conditional mapping. This is a mapping
514 -- depends on the occurrence.
515 elsif
516 ((ecx_utils.g_source(i).occurrence is null) or
517 (p_occur = ecx_utils.g_source(i).occurrence)) then
518 x_node_pos := i;
519 l_node_id := ecx_utils.g_source(i).attribute_id;
520 exit;
521
522 elsif (l_node_id = p_parent_node_id) then
523 if(l_statementEnabled) then
524 ecx_debug.log(l_statement,'l_node_id', l_node_id,i_method_name);
525 end if;
526 x_node_pos := i;
527 l_node_id := ecx_utils.g_source(i).attribute_id;
528 exit;
529 end if;
530 end if;
531 end loop;
532 end if;
533
534 if(l_statementEnabled) then
535 ecx_debug.log(l_statement,'l_node_id', l_node_id,i_method_name);
536 ecx_debug.log(l_statement, 'x_node_pos', x_node_pos,i_method_name);
537 end if;
538 if (l_procedureEnabled) then
539 ecx_debug.pop(i_method_name);
540 end if;
541 return (l_node_id);
542
543 EXCEPTION
544 WHEN ecx_utils.program_exit then
545 if (l_procedureEnabled) then
546 ecx_debug.pop(i_method_name);
547 end if;
548 raise ecx_utils.program_exit;
549
550 when others then
551 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.GET_NODE_ID');
552 if(l_unexpectedEnabled) then
553 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
554 end if;
555 if (l_procedureEnabled) then
556 ecx_debug.pop(i_method_name);
557 end if;
558 raise ecx_utils.program_exit;
559 END get_node_id;
560
561
562 procedure process_node (
563 p_int_col_pos IN pls_integer,
564 p_value IN OUT NOCOPY varchar2,
565 p_clob_value IN Clob default null,
566 p_cdata_flag IN varchar2 default 'N') IS
567
568 i_method_name varchar2(2000) := 'ecx_inbound_new.process_node';
569
570 l_cat_id pls_integer;
571 l_return_status Varchar2(1);
572 l_msg_count pls_integer;
573 l_msg_data Varchar2(4000);
574 l_len number;
575
576 BEGIN
577 if (l_procedureEnabled) then
578 ecx_debug.push(i_method_name);
579 end if;
580
581
582 if(l_statementEnabled) then
583 ecx_debug.log(l_statement,'p_int_col_pos', p_int_col_pos,i_method_name);
584 ecx_debug.log(l_statement, 'node value', p_value,i_method_name);
585 ecx_debug.log(l_statement, 'p_cdata_flag',p_cdata_flag,i_method_name);
586 end if;
587
588 l_cat_id := ecx_utils.g_source(p_int_col_pos).xref_category_id;
589
590 if (l_cat_id is not null) then
591 ecx_code_conversion_pvt.convert_external_value
592 (
593 p_api_version_number => 1.0,
594 p_return_status => l_return_status,
595 p_msg_count => l_msg_count,
596 p_msg_data => l_msg_data,
597 p_value => p_value,
598 p_category_id => l_cat_id,
599 p_snd_tp_id => ecx_utils.g_snd_tp_id,
600 p_rec_tp_id => ecx_utils.g_rec_tp_id ,
601 p_standard_id => ecx_utils.g_standard_id
602 );
603
604 If l_return_status = 'X' OR l_return_status = 'R' Then
605 ecx_utils.g_source(p_int_col_pos).xref_retcode := 1;
606 else
607 ecx_utils.g_source(p_int_col_pos).xref_retcode := 0;
608 end if;
609
610
611 if(l_statementEnabled) then
612 ecx_debug.log(l_statement,'xref return code',
613 ecx_utils.g_source(p_int_col_pos).xref_retcode,
614 i_method_name);
615 end if;
616
617 if (l_return_status = ecx_code_conversion_pvt.G_RET_STS_ERROR) or
618 (l_return_status = ecx_code_conversion_pvt.G_RET_STS_UNEXP_ERROR) or
619 (l_return_status = ECX_CODE_CONVERSION_PVT.g_xref_not_found) then
620 if(l_statementEnabled) then
621 ecx_debug.log(l_statement, 'Code Conversion uses the original code.',
622 i_method_name);
623 end if;
624 else
625 if (l_return_status = ECX_CODE_CONVERSION_PVT.g_recv_xref_not_found) then
626 if(l_statementEnabled) then
627 ecx_debug.log(l_statement, 'Code Conversion uses the sender converted value',
628 p_value,i_method_name);
629 end if;
630 end if;
631 if(l_statementEnabled) then
632 ecx_debug.log(l_statement, 'node value', p_value,i_method_name);
633 end if;
634 end if;
635 end if;
636
637
638 if ((p_value is null and p_clob_value is null) OR (p_value is not null) ) then
639 ecx_utils.g_source(p_int_col_pos).value := p_value;
640 else
641 l_len := dbms_lob.getlength(p_clob_value);
642 if(l_len <= ecx_utils.G_CLOB_VARCHAR_LEN) Then
643 ecx_utils.g_source(p_int_col_pos).value :=dbms_lob.substr(p_clob_value,l_len,1);
644 ecx_utils.g_source(p_int_col_pos).clob_value := null ;
645 ecx_utils.g_source(p_int_col_pos).clob_length := null ;
646 else
647 ecx_utils.g_source(p_int_col_pos).clob_value := p_clob_value;
648 ecx_utils.g_source(p_int_col_pos).clob_length := l_len;
649 end if;
650 end if;
651 if (p_cdata_flag = 'Y') then
652 ecx_utils.g_source(p_int_col_pos).is_clob := p_cdata_flag;
653 end if;
654
655
656 if(l_statementEnabled) then
657 ecx_debug.log(l_statement,ecx_utils.g_source(p_int_col_pos).attribute_name ,
658 ecx_utils.g_source(p_int_col_pos).value||' '||
659 ecx_utils.g_source(p_int_col_pos).base_column_name||' '||' '||
660 p_int_col_pos,i_method_name);
661 end if;
662
663 if (l_procedureEnabled) then
664 ecx_debug.pop(i_method_name);
665 end if;
666
667 EXCEPTION
668 WHEN no_data_found then
669 if(l_unexpectedEnabled) then
670 ecx_debug.log(l_unexpected, 'Internal Column Position and Level not mapped for ',
671 p_int_col_pos,i_method_name);
672 end if;
673 if (l_procedureEnabled) then
674 ecx_debug.pop(i_method_name);
675 end if;
676
677 WHEN ecx_utils.program_exit then
678 if (l_procedureEnabled) then
679 ecx_debug.pop(i_method_name);
680 end if;
681 raise ecx_utils.program_exit;
682
683 WHEN others then
684 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PROCESS_NODE');
685 if(l_unexpectedEnabled) then
686 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
687 end if;
688 if (l_procedureEnabled) then
689 ecx_debug.pop(i_method_name);
690 end if;
691 raise ecx_utils.program_exit;
692 END process_node;
693
694
695 procedure printNodeInfoStack
696 is
697 i_method_name varchar2(2000) := 'ecx_inbound_new.printNodeInfoStack';
698 begin
699 for i in node_info_stack.first..node_info_stack.last loop
700 if(l_statementEnabled) then
701 ecx_debug.log(l_statement,'Stack('||i||')',
702 node_info_stack(i).parent_node_id||'-'||
703 node_info_stack(i).parent_node_pos||'-'||
704 node_info_stack(i).pre_child_name||'-'||
705 node_info_stack(i).parent_xml_node_indx||'-'||
706 node_info_stack(i).occur,i_method_name);
707 end if;
708 end loop;
709 exception
710 when others then
711 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.printNodeInfoStack');
712 if(l_unexpectedEnabled) then
713 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
714 end if;
715 raise ecx_utils.program_exit;
716 end printNodeInfoStack;
717
718
719 procedure pushNodeInfoStack (
720 p_parent_index IN pls_integer
721 ) is
722 i_method_name varchar2(2000) := 'ecx_inbound_new.pushNodeInfoStack';
723 l_stack_indx pls_integer;
724
725 begin
726 l_stack_indx := node_info_stack.COUNT + 1;
727 node_info_stack(l_stack_indx).parent_node_id := l_next_pos;
728 node_info_stack(l_stack_indx).occur := 1;
729 node_info_stack(l_stack_indx).parent_xml_node_indx := p_parent_index;
730
731 printNodeInfoStack;
732 exception
733 WHEN ecx_utils.program_exit then
734 raise ecx_utils.program_exit;
735
736 when others then
737 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.pushNodeInfoStack');
738 if(l_unexpectedEnabled) then
739 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
740 end if;
741 raise ecx_utils.program_exit;
742 end pushNodeInfoStack;
743
744
745 procedure popNodeInfoStack (
746 p_parent_index IN pls_integer
747 ) is
748
749 i_method_name varchar2(2000) := 'ecx_inbound_new.popNodeInfoStack';
750 l_stack_indx pls_integer;
751
752 begin
753 l_stack_indx := node_info_stack.COUNT;
754
755 -- pop all the entries until find a match one.
756 while (p_parent_index <> node_info_stack(l_stack_indx).parent_xml_node_indx) loop
757 node_info_stack.DELETE(l_stack_indx);
758 l_stack_indx := l_stack_indx -1;
759
760 if (l_stack_indx = 0) then
761 exit;
762 end if;
763
764 end loop;
765 printNodeInfoStack;
766
767 exception
768 WHEN ecx_utils.program_exit then
769 raise ecx_utils.program_exit;
770
771 when others then
772 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.popNodeInfoStack');
773 if(l_unexpectedEnabled) then
774 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
775 end if;
776 raise ecx_utils.program_exit;
777 end popNodeInfoStack;
778
779
780 /* This procedure is called by the Java LoadMap to do level processing. */
781 procedure processLevel(
782 p_nodeList IN ECX_NODE_TBL_TYPE,
783 p_level IN pls_integer,
784 p_next IN pls_integer,
785 p_count IN pls_integer,
786 x_err_msg OUT NOCOPY varchar2
787 ) is
788
789 i_method_name varchar2(2000) := 'ecx_inbound_new.processLevel';
790
791 l_node_name Varchar2(2000);
792 l_node_value Varchar2(4000);
793
794 l_node_type pls_integer;
795 l_parent_index pls_integer := -1;
796 l_node_id pls_integer := -1;
797 l_stack_indx pls_integer;
798 i pls_integer;
799 l_node_clob_value clob;
800 l_cdata_flag varchar2(1) := 'N';
801
802 begin
803 if (l_procedureEnabled) then
804 ecx_debug.push(i_method_name);
805 end if;
806 if(l_statementEnabled) then
807 ecx_debug.log(l_statement,'p_level', p_level,i_method_name);
808 ecx_debug.log(l_statement, 'p_next', p_next,i_method_name);
809 ecx_debug.log(l_statement, 'p_count', p_count,i_method_name);
810 end if;
811 if not (l_docLogsAttrSet) then
812 l_docLogsAttrSet := setDocLogsAttributes(p_nodeList, p_count);
813 end if;
814
815 pushStack(p_level);
816 ecx_inbound.process_data(p_level, 10, p_next);
817
818 for i in 1..p_count loop
819 l_node_name := p_nodeList(i).name;
820 l_node_value := p_nodeList(i).value;
821 l_parent_index := p_nodeList(i).parentIndex;
822 l_node_type := p_nodeList(i).type;
823 l_node_clob_value := p_nodeList(i).clob_value;
824 l_cdata_flag := p_nodelist(i).isCDATA;
825
826
827 if(l_statementEnabled) then
828 ecx_debug.log(l_statement,'l_node_indx', i,i_method_name);
829 ecx_debug.log(l_statement, 'l_node_name', l_node_name,i_method_name);
830 ecx_debug.log(l_statement, 'l_node_value', l_node_value,i_method_name);
831 ecx_debug.log(l_statement, 'l_node_type', l_node_type,i_method_name);
832 ecx_debug.log(l_statement, 'l_parent_index', l_parent_index,i_method_name);
833 ecx_debug.log(l_statement, 'l_node_clob_value length',
834 dbms_lob.getLength(l_node_clob_value),i_method_name);
835 ecx_debug.log(l_statement, 'l_cdata_flag is',l_cdata_flag,i_method_name);
836 end if;
837 -- compare the current node parent to the previous node parent.
838 -- if current < previous, then it means the new node is at a highter level.
839 -- if current > previous, then it means the new xml node is a child of previous.
840 -- otherwise, do nothing.
841
842 l_stack_indx := node_info_stack.COUNT;
843
844 if (l_parent_index < node_info_stack(l_stack_indx).parent_xml_node_indx) then
845 popNodeInfoStack(l_parent_index);
846 else
847 if (l_parent_index > node_info_stack(l_stack_indx).parent_xml_node_indx) then
848 pushNodeInfoStack(l_parent_index);
849 end if;
850 end if;
851
852 -- the count of the stack may have change after a pop or push.
853 l_stack_indx := node_info_stack.COUNT;
854
855 -- if node name same as the previous node
856 -- then it is a repeating node
857 if (l_node_name = node_info_stack(l_stack_indx).pre_child_name) then
858 node_info_stack(l_stack_indx).occur := node_info_stack(l_stack_indx).occur + 1;
859 elsif (l_next_pos is not null) then
860 node_info_stack(l_stack_indx).parent_node_pos := l_next_pos;
861 end if;
862
863 node_info_stack(l_stack_indx).pre_child_name := l_node_name;
864
865 if(l_node_type=1) then
866 if (l_node_name <> node_info_stack(l_stack_indx).pre_child_name) then
867 p_current_element_pos:=l_next_pos;
868 end if;
869 l_node_id := get_node_id (p_nodeList, i, l_parent_index, l_node_name,
870 node_info_stack(l_stack_indx).parent_node_id,
871 node_info_stack(l_stack_indx).parent_node_pos,
872 node_info_stack(l_stack_indx).occur,
873 l_node_value,
874 l_next_pos);
875 else
876 l_node_id := get_node_id (p_nodeList, i, l_parent_index, l_node_name,
877 node_info_stack(l_stack_indx).parent_node_id,
878 p_current_element_pos,
879 node_info_stack(l_stack_indx).occur,
880 l_node_value,
881 l_next_pos);
882 end if;
883
884 if (l_next_pos is not null) then
885 process_node (l_next_pos, l_node_value,l_node_clob_value,l_cdata_flag);
886 end if;
887
888 end loop;
889
890 ecx_inbound.process_data(p_level, 20, p_next);
891 ecx_utils.g_total_records := ecx_utils.g_total_records + 1;
892
893 if (l_procedureEnabled) then
894 ecx_debug.pop(i_method_name);
895 end if;
896
897 EXCEPTION
898 WHEN ecx_utils.program_exit then
899 clean_up_tables;
900 x_err_msg := ecx_utils.i_errbuf;
901 if (l_procedureEnabled) then
902 ecx_debug.pop(i_method_name);
903 end if;
904 -- if do a raise here, then java can't get the x_err_msg info.
905 -- setErrorInfo has already set and java needs the x_err_msg
906 -- to determine if any error occurred.
907
908 when others then
909 clean_up_tables;
910 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PROCESSLEVEL');
911 if(l_unexpectedEnabled) then
912 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
913 end if;
914 x_err_msg := ecx_utils.i_errbuf;
915 if (l_procedureEnabled) then
916 ecx_debug.pop(i_method_name);
917 end if;
918 -- same reason as above in program_exit
919 end processLevel;
920
921
922 procedure startDocument is
923
924 i_method_name varchar2(2000) := 'ecx_inbound_new.startDocument';
925 i_root_name varchar2(2000);
926
927 BEGIN
928 if (l_procedureEnabled) then
929 ecx_debug.push(i_method_name);
930 end if;
931
932 -- Initialize global variable and the Stack for the Levels
933 ecx_utils.g_previous_level := 0;
934 l_level_stack.DELETE;
935
936 /** Initialize the attr table **/
937 l_attr_rec.DELETE;
938 l_docLogsAttrSet := false;
939
940 -- initialize the node_info_stack
941 node_info_stack(1).parent_node_id := 0;
942 node_info_stack(1).parent_node_pos := 0;
943 node_info_stack(1).occur := 1;
944 node_info_stack(1).pre_child_name := null;
945 node_info_stack(1).parent_xml_node_indx := 0;
946
947 l_next_pos := 0;
948
949 -- if necessary initialize the printing
950 if (not (ecx_utils.dom_printing) and (ecx_utils.structure_printing))
951 then
952 ecx_inbound.structurePrintingSetup(i_root_name);
953 end if;
954
955 -- Execute the Stage 10 for Level 0
956 -- In-processing for Document level Source Side
957 ecx_actions.execute_stage_data(20,0,'S');
958
959 -- In-processing for Document level
960 ecx_actions.execute_stage_data(20,0,'T');
961
962 if (l_procedureEnabled) then
963 ecx_debug.pop(i_method_name);
964 end if;
965
966 EXCEPTION
967 WHEN ecx_utils.program_exit then
968 if (l_procedureEnabled) then
969 ecx_debug.pop(i_method_name);
970 end if;
971 raise ecx_utils.program_exit;
972
973 WHEN OTHERS THEN
974 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.STARTDOCUMENT');
975 if(l_unexpectedEnabled) then
976 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
977 end if;
978 if (l_procedureEnabled) then
979 ecx_debug.pop(i_method_name);
980 end if;
981 raise ecx_utils.program_exit;
982 END startDocument;
983
984
985 procedure endDocument (
986 x_xmlclob OUT NOCOPY clob,
987 x_parseXML OUT NOCOPY boolean) is
988
989 i_method_name varchar2(2000) := 'ecx_inbound_new.endDocument';
990 begin
991 if (l_procedureEnabled) then
992 ecx_debug.push(i_method_name);
993 end if;
994 popall;
995
996 if (ecx_utils.structure_printing)
997 then
998 ecx_print_local.xmlPopall(x_xmlclob);
999 ecx_util_api.parseXML(ecx_utils.g_inb_parser, x_xmlclob,
1000 x_parseXML, ecx_utils.g_xmldoc);
1001 end if;
1002
1003 -- Execute the Stage 30 for Level 0
1004 -- Post-Processing for Target
1005 ecx_actions.execute_stage_data(30,0,'T');
1006
1007 -- Post-Processing for Source
1008 ecx_actions.execute_stage_data(30,0,'S');
1009
1010 if(l_statementEnabled) then
1011 ecx_debug.log(l_statement, 'Total record processed',
1012 ecx_utils.g_total_records - 1,i_method_name);
1013 end if;
1014
1015 if (l_procedureEnabled) then
1016 ecx_debug.pop(i_method_name);
1017 end if;
1018
1019 EXCEPTION
1020 WHEN ecx_utils.program_exit then
1021 if (l_procedureEnabled) then
1022 ecx_debug.pop(i_method_name);
1023 end if;
1024 raise ecx_utils.program_exit;
1025
1026 WHEN OTHERS THEN
1027 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.ENDDOCUMENT');
1028 if(l_unexpectedEnabled) then
1029 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1030 end if;
1031 if (l_procedureEnabled) then
1032 ecx_debug.pop(i_method_name);
1033 end if;
1034 raise ecx_utils.program_exit;
1035 END endDocument;
1036
1037
1038 Function LoadXML
1039 (
1040 p_debugLevel IN number,
1041 p_payload IN clob,
1042 p_map_code IN varchar2,
1043 x_err_code OUT NOCOPY number
1044 ) return varchar2
1045 is
1046 language java name 'oracle.apps.ecx.process.LoadXML.start(int, oracle.sql.CLOB, java.lang.String, int[]) returns String';
1047
1048
1049 procedure raise_loadxml_err
1050 (
1051 p_err_code IN pls_integer,
1052 p_err_msg IN Varchar2
1053 ) is
1054
1055 i_method_name varchar2(2000) := 'ecx_inbound_new.raise_loadxml_err';
1056
1057 begin
1058 if (p_err_code = PROCESS_LEVEL_EXCEPTION) then
1059 raise ecx_utils.program_exit;
1060
1061 elsif (p_err_code = SAX_EXCEPTION) then
1062 --Since in 10g, XDB XML_PARSE_EXCEPTION is appearing as SAX_EXCEPTION
1063 --incorporating the same exception handling of XML_PARSE_EXCEPTION here,
1064 --otherwise it is appearing as user-defined exception.
1065 -- raise ecx_utils.program_exit;
1066 ecx_debug.setErrorInfo(1, 20, p_err_msg);
1067 if(l_unexpectedEnabled) then
1068 ecx_debug.log(l_unexpected,'Error Code: ' ||p_err_code,i_method_name);
1069 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1070 end if;
1071 raise ecx_utils.program_exit;
1072
1073 elsif (p_err_code = SQL_EXCEPTION) then
1074 raise Load_XML_Exception;
1075
1076 elsif (p_err_code = IO_EXCEPTION) then
1077 raise Load_XML_Exception;
1078
1079 elsif (p_err_code = XML_PARSE_EXCEPTION) then
1080 ecx_debug.setErrorInfo(1, 20, p_err_msg);
1081 if(l_unexpectedEnabled) then
1082 ecx_debug.log(l_unexpected,'Error Code: ' ||p_err_code,i_method_name);
1083 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1084 end if;
1085 raise ecx_utils.program_exit;
1086
1087 elsif (p_err_code = OTHER_EXCEPTION) then
1088 raise Load_XML_Exception;
1089 end if;
1090
1091 exception
1092 when Load_XML_Exception then
1093 ecx_debug.setErrorInfo(2, 30, p_err_msg);
1094 if(l_unexpectedEnabled) then
1095 ecx_debug.log(l_unexpected,'Error Code: ' ||p_err_code,i_method_name);
1096 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1097 end if;
1098 raise ecx_utils.program_exit;
1099
1100 when ecx_utils.program_exit then
1101 raise;
1102 end raise_loadxml_err;
1103
1104
1105 procedure process_xml_doc
1106 (
1107 p_payload IN clob,
1108 p_map_id IN pls_integer,
1109 x_xmlclob OUT NOCOPY clob,
1110 x_parseXML OUT NOCOPY boolean
1111 ) is
1112
1113 i_method_name varchar2(2000) := 'ecx_inbound_new.process_xml_doc';
1114 l_map_code Varchar2(50);
1115 l_err_code pls_integer;
1116 l_err_msg Varchar2(2000);
1117
1118 cursor get_map_code(p_map_id IN pls_integer)
1119 is
1120 select map_code
1121 from ecx_mappings
1122 where map_id = p_map_id;
1123
1124 begin
1125 if (l_procedureEnabled) then
1126 ecx_debug.push(i_method_name);
1127 end if;
1128
1129 open get_map_code(p_map_id);
1130 fetch get_map_code
1131 into l_map_code;
1132 close get_map_code;
1133 startDocument;
1134
1135
1136 l_err_msg := LoadXML(ecx_debug.g_debug_level, p_payload, l_map_code, l_err_code);
1137
1138 if (l_err_code is not null and l_err_code > 0) then
1139 raise_loadxml_err(l_err_code, l_err_msg);
1140 end if;
1141 endDocument (x_xmlclob, x_parseXML);
1142 if (l_procedureEnabled) then
1143 ecx_debug.pop(i_method_name);
1144 end if;
1145
1146 EXCEPTION
1147 WHEN ecx_utils.program_exit then
1148 clean_up_tables;
1149 if (l_procedureEnabled) then
1150 ecx_debug.pop(i_method_name);
1151 end if;
1152 raise ecx_utils.program_exit;
1153
1154 WHEN OTHERS THEN
1155 clean_up_tables;
1156 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PROCESS_XML_DOC');
1157 if(l_unexpectedEnabled) then
1158 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1159 end if;
1160 if (l_procedureEnabled) then
1161 ecx_debug.pop(i_method_name);
1162 end if;
1163 raise ecx_utils.program_exit;
1164 end process_xml_doc;
1165
1166
1167 END ecx_inbound_new;
[Home] [Help]