1 PACKAGE BODY ecx_inbound_new as
2 --$Header: ECXINNB.pls 120.7 2011/03/14 09:37:12 jmaddila 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 l_temp varchar2(32767);
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 /** Fix for bug 8845448*/
644 begin
645 dbms_lob.read(p_clob_value,l_len,1,l_temp);
646 ecx_utils.g_source(p_int_col_pos).value := l_temp;
647 ecx_utils.g_source(p_int_col_pos).clob_value := null;
648 ecx_utils.g_source(p_int_col_pos).clob_length := null;
649 ecx_utils.g_source(p_int_col_pos).is_clob := 'N';
650 exception
651 when others then
652 ecx_utils.g_source(p_int_col_pos).clob_value := p_clob_value;
653 ecx_utils.g_source(p_int_col_pos).clob_length := l_len;
654 ecx_utils.g_source(p_int_col_pos).is_clob := 'Y';
655 end;
656 else
657 ecx_utils.g_source(p_int_col_pos).clob_value := p_clob_value;
658 ecx_utils.g_source(p_int_col_pos).clob_length := l_len;
659 ecx_utils.g_source(p_int_col_pos).is_clob := 'Y';
660 end if;
661 end if;
662 if (p_cdata_flag = 'Y') then
663 ecx_utils.g_source(p_int_col_pos).is_clob := p_cdata_flag;
664 end if;
665
666
667 if(l_statementEnabled) then
668 ecx_debug.log(l_statement,ecx_utils.g_source(p_int_col_pos).attribute_name ,
669 ecx_utils.g_source(p_int_col_pos).value||' '||
670 ecx_utils.g_source(p_int_col_pos).base_column_name||' '||' '||
671 p_int_col_pos,i_method_name);
672 end if;
673
674 if (l_procedureEnabled) then
675 ecx_debug.pop(i_method_name);
676 end if;
677
678 EXCEPTION
679 WHEN no_data_found then
680 if(l_unexpectedEnabled) then
681 ecx_debug.log(l_unexpected, 'Internal Column Position and Level not mapped for ',
682 p_int_col_pos,i_method_name);
683 end if;
684 if (l_procedureEnabled) then
685 ecx_debug.pop(i_method_name);
686 end if;
687
688 WHEN ecx_utils.program_exit then
689 if (l_procedureEnabled) then
690 ecx_debug.pop(i_method_name);
691 end if;
692 raise ecx_utils.program_exit;
693
694 WHEN others then
695 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PROCESS_NODE');
696 if(l_unexpectedEnabled) then
697 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
698 end if;
699 if (l_procedureEnabled) then
700 ecx_debug.pop(i_method_name);
701 end if;
702 raise ecx_utils.program_exit;
703 END process_node;
704
705
706 procedure printNodeInfoStack
707 is
708 i_method_name varchar2(2000) := 'ecx_inbound_new.printNodeInfoStack';
709 begin
710 for i in node_info_stack.first..node_info_stack.last loop
711 if(l_statementEnabled) then
712 ecx_debug.log(l_statement,'Stack('||i||')',
713 node_info_stack(i).parent_node_id||'-'||
714 node_info_stack(i).parent_node_pos||'-'||
715 node_info_stack(i).pre_child_name||'-'||
716 node_info_stack(i).parent_xml_node_indx||'-'||
717 node_info_stack(i).occur,i_method_name);
718 end if;
719 end loop;
720 exception
721 when others then
722 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.printNodeInfoStack');
723 if(l_unexpectedEnabled) then
724 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
725 end if;
726 raise ecx_utils.program_exit;
727 end printNodeInfoStack;
728
729
730 procedure pushNodeInfoStack (
731 p_parent_index IN pls_integer
732 ) is
733 i_method_name varchar2(2000) := 'ecx_inbound_new.pushNodeInfoStack';
734 l_stack_indx pls_integer;
735
736 begin
737 l_stack_indx := node_info_stack.COUNT + 1;
738 node_info_stack(l_stack_indx).parent_node_id := l_next_pos;
739 node_info_stack(l_stack_indx).occur := 1;
740 node_info_stack(l_stack_indx).parent_xml_node_indx := p_parent_index;
741
742 printNodeInfoStack;
743 exception
744 WHEN ecx_utils.program_exit then
745 raise ecx_utils.program_exit;
746
747 when others then
748 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.pushNodeInfoStack');
749 if(l_unexpectedEnabled) then
750 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
751 end if;
752 raise ecx_utils.program_exit;
753 end pushNodeInfoStack;
754
755
756 procedure popNodeInfoStack (
757 p_parent_index IN pls_integer
758 ) is
759
760 i_method_name varchar2(2000) := 'ecx_inbound_new.popNodeInfoStack';
761 l_stack_indx pls_integer;
762
763 begin
764 l_stack_indx := node_info_stack.COUNT;
765
766 -- pop all the entries until find a match one.
767 while (p_parent_index <> node_info_stack(l_stack_indx).parent_xml_node_indx) loop
768 node_info_stack.DELETE(l_stack_indx);
769 l_stack_indx := l_stack_indx -1;
770
771 if (l_stack_indx = 0) then
772 exit;
773 end if;
774
775 end loop;
776 printNodeInfoStack;
777
778 exception
779 WHEN ecx_utils.program_exit then
780 raise ecx_utils.program_exit;
781
782 when others then
783 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.popNodeInfoStack');
784 if(l_unexpectedEnabled) then
785 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
786 end if;
787 raise ecx_utils.program_exit;
788 end popNodeInfoStack;
789
790
791 /* This procedure is called by the Java LoadMap to do level processing. */
792 procedure processLevel(
793 p_nodeList IN ECX_NODE_TBL_TYPE,
794 p_level IN pls_integer,
795 p_next IN pls_integer,
796 p_count IN pls_integer,
797 x_err_msg OUT NOCOPY varchar2
798 ) is
799
800 i_method_name varchar2(2000) := 'ecx_inbound_new.processLevel';
801
802 l_node_name Varchar2(2000);
803 l_node_value Varchar2(4000);
804
805 l_node_type pls_integer;
806 l_parent_index pls_integer := -1;
807 l_node_id pls_integer := -1;
808 l_stack_indx pls_integer;
809 i pls_integer;
810 l_node_clob_value clob;
811 l_cdata_flag varchar2(1) := 'N';
812
813 begin
814 if (l_procedureEnabled) then
815 ecx_debug.push(i_method_name);
816 end if;
817 if(l_statementEnabled) then
818 ecx_debug.log(l_statement,'p_level', p_level,i_method_name);
819 ecx_debug.log(l_statement, 'p_next', p_next,i_method_name);
820 ecx_debug.log(l_statement, 'p_count', p_count,i_method_name);
821 end if;
822 if not (l_docLogsAttrSet) then
823 l_docLogsAttrSet := setDocLogsAttributes(p_nodeList, p_count);
824 end if;
825
826 pushStack(p_level);
827 ecx_inbound.process_data(p_level, 10, p_next);
828
829 for i in 1..p_count loop
830 l_node_name := p_nodeList(i).name;
831 l_node_value := p_nodeList(i).value;
832 l_parent_index := p_nodeList(i).parentIndex;
833 l_node_type := p_nodeList(i).type;
834 l_node_clob_value := p_nodeList(i).clob_value;
835 l_cdata_flag := p_nodelist(i).isCDATA;
836
837
838 if(l_statementEnabled) then
839 ecx_debug.log(l_statement,'l_node_indx', i,i_method_name);
840 ecx_debug.log(l_statement, 'l_node_name', l_node_name,i_method_name);
841 ecx_debug.log(l_statement, 'l_node_value', l_node_value,i_method_name);
842 ecx_debug.log(l_statement, 'l_node_type', l_node_type,i_method_name);
843 ecx_debug.log(l_statement, 'l_parent_index', l_parent_index,i_method_name);
844 ecx_debug.log(l_statement, 'l_node_clob_value length',
845 dbms_lob.getLength(l_node_clob_value),i_method_name);
846 ecx_debug.log(l_statement, 'l_cdata_flag is',l_cdata_flag,i_method_name);
847 end if;
848 -- compare the current node parent to the previous node parent.
849 -- if current < previous, then it means the new node is at a highter level.
850 -- if current > previous, then it means the new xml node is a child of previous.
851 -- otherwise, do nothing.
852
853 l_stack_indx := node_info_stack.COUNT;
854
855 if (l_parent_index < node_info_stack(l_stack_indx).parent_xml_node_indx) then
856 popNodeInfoStack(l_parent_index);
857 else
858 if (l_parent_index > node_info_stack(l_stack_indx).parent_xml_node_indx) then
859 pushNodeInfoStack(l_parent_index);
860 end if;
861 end if;
862
863 -- the count of the stack may have change after a pop or push.
864 l_stack_indx := node_info_stack.COUNT;
865
866 -- if node name same as the previous node
867 -- then it is a repeating node
868 if (l_node_name = node_info_stack(l_stack_indx).pre_child_name) then
869 node_info_stack(l_stack_indx).occur := node_info_stack(l_stack_indx).occur + 1;
870 elsif (l_next_pos is not null) then
871 node_info_stack(l_stack_indx).parent_node_pos := l_next_pos;
872 end if;
873
874 node_info_stack(l_stack_indx).pre_child_name := l_node_name;
875
876 if(l_node_type=1) then
877 if (l_node_name <> node_info_stack(l_stack_indx).pre_child_name) then
878 p_current_element_pos:=l_next_pos;
879 end if;
880 l_node_id := get_node_id (p_nodeList, i, l_parent_index, l_node_name,
881 node_info_stack(l_stack_indx).parent_node_id,
882 node_info_stack(l_stack_indx).parent_node_pos,
883 node_info_stack(l_stack_indx).occur,
884 l_node_value,
885 l_next_pos);
886 else
887 l_node_id := get_node_id (p_nodeList, i, l_parent_index, l_node_name,
888 node_info_stack(l_stack_indx).parent_node_id,
889 p_current_element_pos,
890 node_info_stack(l_stack_indx).occur,
891 l_node_value,
892 l_next_pos);
893 end if;
894
895 if (l_next_pos is not null) then
896 process_node (l_next_pos, l_node_value,l_node_clob_value,l_cdata_flag);
897 end if;
898
899 end loop;
900
901 ecx_inbound.process_data(p_level, 20, p_next);
902 ecx_utils.g_total_records := ecx_utils.g_total_records + 1;
903
904 if (l_procedureEnabled) then
905 ecx_debug.pop(i_method_name);
906 end if;
907
908 EXCEPTION
909 WHEN ecx_utils.program_exit then
910 clean_up_tables;
911 x_err_msg := ecx_utils.i_errbuf;
912 if (l_procedureEnabled) then
913 ecx_debug.pop(i_method_name);
914 end if;
915 -- if do a raise here, then java can't get the x_err_msg info.
916 -- setErrorInfo has already set and java needs the x_err_msg
917 -- to determine if any error occurred.
918
919 when others then
920 clean_up_tables;
921 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PROCESSLEVEL');
922 if(l_unexpectedEnabled) then
923 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
924 end if;
925 x_err_msg := ecx_utils.i_errbuf;
926 if (l_procedureEnabled) then
927 ecx_debug.pop(i_method_name);
928 end if;
929 -- same reason as above in program_exit
930 end processLevel;
931
932
933 procedure startDocument is
934
935 i_method_name varchar2(2000) := 'ecx_inbound_new.startDocument';
936 i_root_name varchar2(2000);
937
938 BEGIN
939 if (l_procedureEnabled) then
940 ecx_debug.push(i_method_name);
941 end if;
942
943 -- Initialize global variable and the Stack for the Levels
944 ecx_utils.g_previous_level := 0;
945 l_level_stack.DELETE;
946
947 /** Initialize the attr table **/
948 l_attr_rec.DELETE;
949 l_docLogsAttrSet := false;
950
951 -- initialize the node_info_stack
952 node_info_stack(1).parent_node_id := 0;
953 node_info_stack(1).parent_node_pos := 0;
954 node_info_stack(1).occur := 1;
955 node_info_stack(1).pre_child_name := null;
956 node_info_stack(1).parent_xml_node_indx := 0;
957
958 l_next_pos := 0;
959
960 -- if necessary initialize the printing
961 if (not (ecx_utils.dom_printing) and (ecx_utils.structure_printing))
962 then
963 ecx_inbound.structurePrintingSetup(i_root_name);
964 end if;
965
966 -- Execute the Stage 10 for Level 0
967 -- In-processing for Document level Source Side
968 ecx_actions.execute_stage_data(20,0,'S');
969
970 -- In-processing for Document level
971 ecx_actions.execute_stage_data(20,0,'T');
972
973 if (l_procedureEnabled) then
974 ecx_debug.pop(i_method_name);
975 end if;
976
977 EXCEPTION
978 WHEN ecx_utils.program_exit then
979 if (l_procedureEnabled) then
980 ecx_debug.pop(i_method_name);
981 end if;
982 raise ecx_utils.program_exit;
983
984 WHEN OTHERS THEN
985 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.STARTDOCUMENT');
986 if(l_unexpectedEnabled) then
987 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
988 end if;
989 if (l_procedureEnabled) then
990 ecx_debug.pop(i_method_name);
991 end if;
992 raise ecx_utils.program_exit;
993 END startDocument;
994
995
996 procedure endDocument (
997 x_xmlclob OUT NOCOPY clob,
998 x_parseXML OUT NOCOPY boolean) is
999
1000 i_method_name varchar2(2000) := 'ecx_inbound_new.endDocument';
1001 begin
1002 if (l_procedureEnabled) then
1003 ecx_debug.push(i_method_name);
1004 end if;
1005 popall;
1006
1007 if (ecx_utils.structure_printing)
1008 then
1009 ecx_print_local.xmlPopall(x_xmlclob);
1010 ecx_util_api.parseXML(ecx_utils.g_inb_parser, x_xmlclob,
1011 x_parseXML, ecx_utils.g_xmldoc);
1012 end if;
1013
1014 -- Execute the Stage 30 for Level 0
1015 -- Post-Processing for Target
1016 ecx_actions.execute_stage_data(30,0,'T');
1017
1018 -- Post-Processing for Source
1019 ecx_actions.execute_stage_data(30,0,'S');
1020
1021 if(l_statementEnabled) then
1022 ecx_debug.log(l_statement, 'Total record processed',
1023 ecx_utils.g_total_records - 1,i_method_name);
1024 end if;
1025
1026 if (l_procedureEnabled) then
1027 ecx_debug.pop(i_method_name);
1028 end if;
1029
1030 EXCEPTION
1031 WHEN ecx_utils.program_exit then
1032 if (l_procedureEnabled) then
1033 ecx_debug.pop(i_method_name);
1034 end if;
1035 raise ecx_utils.program_exit;
1036
1037 WHEN OTHERS THEN
1038 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.ENDDOCUMENT');
1039 if(l_unexpectedEnabled) then
1040 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1041 end if;
1042 if (l_procedureEnabled) then
1043 ecx_debug.pop(i_method_name);
1044 end if;
1045 raise ecx_utils.program_exit;
1046 END endDocument;
1047
1048
1049 Function LoadXML
1050 (
1051 p_debugLevel IN number,
1052 p_payload IN clob,
1053 p_map_code IN varchar2,
1054 x_err_code OUT NOCOPY number
1055 ) return varchar2
1056 is
1057 language java name 'oracle.apps.ecx.process.LoadXML.start(int, oracle.sql.CLOB, java.lang.String, int[]) returns String';
1058
1059
1060 procedure raise_loadxml_err
1061 (
1062 p_err_code IN pls_integer,
1063 p_err_msg IN Varchar2
1064 ) is
1065
1066 i_method_name varchar2(2000) := 'ecx_inbound_new.raise_loadxml_err';
1067
1068 begin
1069 if (p_err_code = PROCESS_LEVEL_EXCEPTION) then
1070 raise ecx_utils.program_exit;
1071
1072 elsif (p_err_code = SAX_EXCEPTION) then
1073 --Since in 10g, XDB XML_PARSE_EXCEPTION is appearing as SAX_EXCEPTION
1074 --incorporating the same exception handling of XML_PARSE_EXCEPTION here,
1075 --otherwise it is appearing as user-defined exception.
1076 -- raise ecx_utils.program_exit;
1077 ecx_debug.setErrorInfo(1, 20, p_err_msg);
1078 if(l_unexpectedEnabled) then
1079 ecx_debug.log(l_unexpected,'Error Code: ' ||p_err_code,i_method_name);
1080 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1081 end if;
1082 raise ecx_utils.program_exit;
1083
1084 elsif (p_err_code = SQL_EXCEPTION) then
1085 raise Load_XML_Exception;
1086
1087 elsif (p_err_code = IO_EXCEPTION) then
1088 raise Load_XML_Exception;
1089
1090 elsif (p_err_code = XML_PARSE_EXCEPTION) then
1091 ecx_debug.setErrorInfo(1, 20, p_err_msg);
1092 if(l_unexpectedEnabled) then
1093 ecx_debug.log(l_unexpected,'Error Code: ' ||p_err_code,i_method_name);
1094 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1095 end if;
1096 raise ecx_utils.program_exit;
1097
1098 elsif (p_err_code = OTHER_EXCEPTION) then
1099 raise Load_XML_Exception;
1100 end if;
1101
1102 exception
1103 when Load_XML_Exception then
1104 ecx_debug.setErrorInfo(2, 30, p_err_msg);
1105 if(l_unexpectedEnabled) then
1106 ecx_debug.log(l_unexpected,'Error Code: ' ||p_err_code,i_method_name);
1107 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1108 end if;
1109 raise ecx_utils.program_exit;
1110
1111 when ecx_utils.program_exit then
1112 raise;
1113 end raise_loadxml_err;
1114
1115
1116 procedure process_xml_doc
1117 (
1118 p_payload IN clob,
1119 p_map_id IN pls_integer,
1120 x_xmlclob OUT NOCOPY clob,
1121 x_parseXML OUT NOCOPY boolean
1122 ) is
1123
1124 i_method_name varchar2(2000) := 'ecx_inbound_new.process_xml_doc';
1125 l_map_code Varchar2(50);
1126 l_err_code pls_integer;
1127 l_err_msg Varchar2(2000);
1128
1129 cursor get_map_code(p_map_id IN pls_integer)
1130 is
1131 select map_code
1132 from ecx_mappings
1133 where map_id = p_map_id;
1134
1135 begin
1136 if (l_procedureEnabled) then
1137 ecx_debug.push(i_method_name);
1138 end if;
1139
1140 open get_map_code(p_map_id);
1141 fetch get_map_code
1142 into l_map_code;
1143 close get_map_code;
1144 startDocument;
1145
1146
1147 l_err_msg := LoadXML(ecx_debug.g_debug_level, p_payload, l_map_code, l_err_code);
1148
1149 if (l_err_code is not null and l_err_code > 0) then
1150 raise_loadxml_err(l_err_code, l_err_msg);
1151 end if;
1152 endDocument (x_xmlclob, x_parseXML);
1153 if (l_procedureEnabled) then
1154 ecx_debug.pop(i_method_name);
1155 end if;
1156
1157 EXCEPTION
1158 WHEN ecx_utils.program_exit then
1159 clean_up_tables;
1160 if (l_procedureEnabled) then
1161 ecx_debug.pop(i_method_name);
1162 end if;
1163 raise ecx_utils.program_exit;
1164
1165 WHEN OTHERS THEN
1166 clean_up_tables;
1167 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_INBOUND_NEW.PROCESS_XML_DOC');
1168 if(l_unexpectedEnabled) then
1169 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1170 end if;
1171 if (l_procedureEnabled) then
1172 ecx_debug.pop(i_method_name);
1173 end if;
1174 raise ecx_utils.program_exit;
1175 end process_xml_doc;
1176
1177
1178 END ecx_inbound_new;
[Home] [Help]