[Home] [Help]
PACKAGE BODY: APPS.ECX_UTILS
Source
1 package body ecx_utils as
2 -- $Header: ECXUTILB.pls 120.11 2008/05/02 19:03:17 cpeixoto ship $
3 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
4 l_statement PLS_INTEGER := ecx_debug.g_statement;
5 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
6 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
7 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
8 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
9 DELETE_DOCTYPE_PROC_NAME CONSTANT varchar2(50) := 'ECX_ACTIONS.DELETE_DOCTYPE';
10 /**
11 Prepares the Select statement for the ec_views on the base Applications tables.
12 **/
13 procedure select_clause
14 (
15 i_level IN pls_integer,
16 i_Where_string OUT NOCOPY VARCHAR2
17 ) IS
18
19 i_method_name varchar2(2000) := 'ecx_utils.select_clause';
20 cSelect_stmt VARCHAR2(32000) := 'SELECT ';
21 cFrom_stmt VARCHAR2(100) := ' FROM ';
22 cWhere_stmt VARCHAR2(80) := ' WHERE 1=1 ';
23
24 cTO_CHAR VARCHAR2(20) := 'TO_CHAR(';
25 cDATE VARCHAR2(40) := ',''YYYYMMDD HH24MISS'')';
26 cWord1 VARCHAR2(20) := ' ';
27 cWord2 VARCHAR2(40) := ' ';
28
29 iRow_count pls_integer := ecx_utils.g_source.COUNT;
30 i pls_integer;
31 BEGIN
32 if (l_procedureEnabled) then
33 ecx_debug.push(i_method_name);
34 end if;
35
36 if(l_statementEnabled) then
37 ecx_debug.log(l_statement, 'i_level',i_level,i_method_name);
38 end if;
39
40 i := ecx_utils.g_source_levels(i_level).file_start_pos;
41 loop
42 if (ecx_utils.g_source(i).external_level = i_level) then
43 -- **************************************
44 -- apply appropriate data conversion
45 -- convert everything to VARCHAR2
46 -- **************************************
47
48 if 12 = ecx_utils.g_source(i).data_type Then
49 cWord1 := cTO_CHAR;
50 cWord2 := cDATE;
51
52 elsif 2 = ecx_utils.g_source(i).data_type Then
53 cWord1 := cTO_CHAR;
54 cWord2 := ')';
55 else
56 cWord1 := NULL;
57 cWord2 := NULL;
58 END if;
59
60 -- build SELECT statement
61 cSelect_stmt := cSelect_stmt || ' ' || cWord1 ||
62 nvl(ecx_utils.g_source(i).base_column_Name,'NULL') || cWord2 || ',';
63 end if;
64 exit when i= ecx_utils.g_source_levels(i_level).file_end_pos;
65 i := ecx_utils.g_source.next(i);
66 End Loop;
67
68 -- build FROM, WHERE statements
69
70 cFrom_stmt := cFrom_Stmt||' '||ecx_utils.g_source_levels(i_level).base_table_name;
71
72 cSelect_stmt := RTRIM(cSelect_stmt, ',');
73 i_Where_string := cSelect_stmt||' '||cFrom_stmt||' '||cWhere_Stmt;
74
75 if(l_statementEnabled) then
76 ecx_debug.log(l_statement,'i_Where_String',i_Where_String,i_method_name);
77 end if;
78
79 if (l_procedureEnabled) then
80 ecx_debug.pop(i_method_name);
81 end if;
82 exception
83 when others then
84 if(l_unexpectedEnabled) then
85 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
86 'PROGRESS_LEVEL','ECX_UTILS.SELect_CLAUSE');
87 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
88 'ERROR_MESSAGE',SQLERRM);
89 end if;
90 ecx_utils.error_type := 30;
91 ecx_utils.i_ret_code :=2;
92 raise ecx_utils.PROGRAM_EXIT;
93 END select_clause;
94
95 /**
96 Loads the Objects required by the Outbound transaction. This includes
97 1. Select statement on the ec_views.
98 3. Parses and loads Custom Procedures into memory table.
99 4. Loads mappings required by these procedures into memory tables.
100 **/
101 procedure load_objects
102 (
103 i_map_id in pls_integer
104 )
105 is
106 i_method_name varchar2(2000) := 'ecx_utils.load_objects';
107 i_counter pls_integer :=0;
108 k pls_integer;
109 i_root_element ecx_objects.root_element%TYPE;
110 i_fullpath ecx_objects.fullpath%TYPE;
111 i_dtd_path varchar2(200);
112 l_clob clob;
113
114 begin
115 if (l_procedureEnabled) then
116 ecx_debug.push(i_method_name);
117 end if;
118
119 if (ecx_utils.g_source_levels.COUNT >0) then
120 for i in ecx_utils.g_source_levels.first..ecx_utils.g_source_levels.last
121 LOOP
122 if (i <> 0)
123 then
124 select_clause
125 (
126 i,
127 ecx_utils.g_source_levels(i).sql_stmt
128 );
129
130
131 /** Call Append clause for all the levels **/
132 ecx_actions.append_clause_for_view(10,i);
133
134 /** No Need to clause. Is getting called in the Push and Pop of the level **/
135 /* Execute Pre Processing STage
136 ecx_actions.execute_stage_data
137 (
138 10,
139 i,
140 'S'
141 );
142 **/
143
144
145 if(l_statementEnabled) then
146 ecx_debug.log(l_statement,ecx_utils.g_source_levels(i).sql_stmt,i_method_name);
147 end if;
148
149 -- Open Cursor For Each level and store the handles in the PL/SQL table.
150 ecx_utils.g_source_levels(i).Cursor_handle := dbms_sql.open_cursor;
151
152 if(l_statementEnabled) then
153 ecx_debug.log(l_statement,'Cursor handle',
154 ecx_utils.g_source_levels(i).Cursor_handle,i_method_name);
155 end if;
156
157 -- Parse the Select Statement for Each level
158 BEGIN
159 dbms_sql.parse (
160 ecx_utils.g_source_levels(i).cursor_handle,
161 ecx_utils.g_source_levels(i).sql_stmt,
162 dbms_sql.native
163 );
164 EXCEPTION
165 WHEN OTHERS THEN
166 ecx_error_handling_pvt.print_parse_error
167 (
168 dbms_sql.last_error_position,
169 ecx_utils.g_source_levels(i).sql_stmt
170 );
171
172
173 if(l_statementEnabled) then
174 ecx_debug.log(l_statement,'ECX','ECX_PROGRAM_ERROR',i_method_name,
175 'PROGRESS_LEVEL','ECX_UTILS.LOAD_OBJECTS');
176 ecx_debug.log(l_statement,'ECX','ECX_PARSE_VIEW_ERROR',i_method_name,'LEVEL',i);
177 ecx_debug.log(l_statement, 'ECX', SQLERRM || ' - ECX_UTILS.LOAD_OBJECTS',i_method_name);
178 end if;
179 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_UTILS.LOAD_OBJECTS');
180 raise ecx_utils.PROGRAM_EXIT;
181 END;
182
183 i_counter :=0;
184 -- Define Columns for Each Level
185 k := ecx_utils.g_source_levels(i).file_start_pos;
186 LOOP
187 if (ecx_utils.g_source(k).external_level = i) then
188 i_counter := i_counter + 1;
189 /** Change required for Clob Support -- 2263729 ***/
190 if ecx_utils.g_source(k).data_type <> 112 Then
191 dbms_sql.define_column
192 (
193 ecx_utils.g_source_levels(i).Cursor_Handle,
194 i_counter,
195 ecx_utils.g_source_levels(i).sql_stmt,
196 ecx_utils.G_VARCHAR_LEN
197 );
198 else
199 dbms_sql.define_column
200 (
201 ecx_utils.g_source_levels(i).Cursor_Handle,
202 i_counter,
203 l_clob
204 );
205 end if;
206 end if;
207 exit when k = ecx_utils.g_source_levels(i).file_end_pos;
208 k := ecx_utils.g_source.next(k);
209 END LOOP;
210 end if;
211
212 END LOOP;
213 end if;
214 if (l_procedureEnabled) then
215 ecx_debug.pop(i_method_name);
216 end if;
217 EXCEPTION
218 WHEN ecx_utils.PROGRAM_EXIT then
219 raise ecx_utils.program_exit;
220 WHEN OTHERS THEN
221 if(l_unexpectedEnabled) then
222 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
223 'PROGRESS_LEVEL',
224 'ECX_UTILS.LOAD_OBJECTS');
225 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
226 'ERROR_MESSAGE',SQLERRM);
227 ecx_debug.log(l_unexpected, 'ECX', SQLERRM || ' - ECX_UTILS.LOAD_OBJECTS',i_method_name);
228 end if;
229 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_UTILS.LOAD_OBJECTS');
230 raise ecx_utils.PROGRAM_EXIT;
231 end load_objects;
232
233 procedure load_procedure_mappings (
234 i_map_id IN pls_integer) IS
235
236 i_method_name varchar2(2000) := 'ecx_utils.load_procedure_mappings';
237 cursor proc_mapping (
238 p_map_id pls_integer) IS
239 select epm.transtage_id transtage_id,
240 upper(etsd.custom_procedure_name) procedure_name,
241 upper(epm.parameter_name) parameter_name,
242 epm.action_type action_type,
243 epm.variable_level variable_level,
244 epm.variable_name variable_name,
245 epm.variable_pos variable_pos,
246 epm.data_type data_type,
247 nvl(epm.variable_direction,'S') variable_direction,
248 epm.variable_value variable_constant
249 from ecx_proc_mappings epm,
250 ecx_tran_stage_data etsd
251 where etsd.transtage_id = epm.transtage_id
252 and etsd.map_id = p_map_id
253 and epm.map_id = p_map_id
254 order by epm.transtage_id,procmap_id;
255
256 i pls_integer := 0;
257
258 BEGIN
259 if (l_procedureEnabled) then
260 ecx_debug.push(i_method_name);
261 end if;
262 if(l_statementEnabled) then
263 ecx_debug.log(l_statement,'i_map_id',i_map_id,i_method_name);
264 end if;
265 ecx_utils.g_procedure_mappings.DELETE;
266
267 for proc_map in proc_mapping (i_map_id) loop
268 i := i + 1;
269 ecx_utils.g_procedure_mappings(i).transtage_id := proc_map.transtage_id;
270 ecx_utils.g_procedure_mappings(i).procedure_name := proc_map.procedure_name;
271 ecx_utils.g_procedure_mappings(i).parameter_name := proc_map.parameter_name;
272 ecx_utils.g_procedure_mappings(i).action_type := proc_map.action_type;
273 ecx_utils.g_procedure_mappings(i).variable_level := proc_map.variable_level;
274 ecx_utils.g_procedure_mappings(i).variable_name := proc_map.variable_name;
275 ecx_utils.g_procedure_mappings(i).variable_pos := proc_map.variable_pos;
276 ecx_utils.g_procedure_mappings(i).variable_direction := proc_map.variable_direction;
277 ecx_utils.g_procedure_mappings(i).data_type := proc_map.data_type;
278 ecx_utils.g_procedure_mappings(i).variable_constant := proc_map.variable_constant;
279
280 if(l_statementEnabled) then
281 ecx_debug.log(l_statement, ecx_utils.g_procedure_mappings(i).tranStage_id ||'|'||
282 ecx_utils.g_procedure_mappings(i).procedure_name||'|'||
283 ecx_utils.g_procedure_mappings(i).parameter_name||'|'||
284 ecx_utils.g_procedure_mappings(i).action_type||'|'||
285 ecx_utils.g_procedure_mappings(i).variable_level||'|'||
286 ecx_utils.g_procedure_mappings(i).variable_name||'|'||
287 ecx_utils.g_procedure_mappings(i).variable_pos ||'|'||
288 ecx_utils.g_procedure_mappings(i).variable_direction ||'|'||
289 ecx_utils.g_procedure_mappings(i).data_type||'|'||
290 ecx_utils.g_procedure_mappings(i).variable_constant,i_method_name);
291 end if;
292 end loop;
293
294 if (l_procedureEnabled) then
295 ecx_debug.pop(i_method_name);
296 end if;
297
298 EXCEPTION
299 WHEN PROGRAM_EXIT then
300
301 if (l_procedureEnabled) then
302 ecx_debug.pop(i_method_name);
303 end if;
304 raise;
305
306 WHEN OTHERS THEN
307 if(l_unexpectedEnabled) then
308 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR','PROGRESS_LEVEL',i_method_name,
309 'ecx_utils.LOAD_PROCEDURE_MAPPINGS');
310 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
311 ecx_debug.log(l_unexpected, 'ECX',
312 ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.LOAD_PROCEDURE_MAPPINGS: ',
313 i_method_name);
314 end if;
315 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM ||
316 ' - ecx_utils.LOAD_PROCEDURE_MAPPINGS: ');
317 if (l_procedureEnabled) then
318 ecx_debug.pop(i_method_name);
319 end if;
320 raise PROGRAM_EXIT;
321 end load_procedure_mappings;
322
323 procedure load_procedure_definitions (
324 i_map_id IN pls_integer) IS
325
326 i_method_name varchar2(2000) := 'ecx_utils.load_procedure_definitions';
327 cursor proc_definition is
328 select transtage_id,
329 custom_procedure_name
330 from ecx_tran_stage_data etsd
331 where etsd.map_id = i_map_id
332 and action_type = 1050;
333
334 l_transtage_id pls_integer;
335 l_proc_name Varchar2(80);
336
337 BEGIN
338 if (l_procedureEnabled) then
339 ecx_debug.push(i_method_name);
340 end if;
341
342 ecx_utils.g_procedure_list.DELETE;
343 -- load all the procedure definitions and build the cursor
344 for get_proc_def in proc_definition loop
345 l_transtage_id := get_proc_def.transtage_id;
346 l_proc_name := upper(get_proc_def.custom_procedure_name);
347
348 ecx_utils.g_procedure_list(l_transtage_id).procedure_name := l_proc_name;
349 build_procedure_call (l_transtage_id, l_proc_name,
350 ecx_utils.g_procedure_list(l_transtage_id).cursor_handle);
351 end loop;
352
353 if (l_procedureEnabled) then
354 ecx_debug.pop(i_method_name);
355 end if;
356
357 EXCEPTION
358 WHEN PROGRAM_EXIT then
359 if (l_procedureEnabled) then
360 ecx_debug.pop(i_method_name);
361 end if;
362 raise;
363
364 WHEN OTHERS THEN
365 if(l_unexpectedEnabled) then
366 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
367 'PROGRESS_LEVEL',
368 'ecx_utils.LOAD_PROCEDURE_DEFINITIONS');
369 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
370 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.LOAD_PROCEDURE_DEFINITIONS:',
371 i_method_name);
372 end if;
373 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM ||
374 ' - ecx_utils.LOAD_PROCEDURE_DEFINITIONS: ');
375 if (l_procedureEnabled) then
376 ecx_debug.pop(i_method_name);
377 end if;
378 raise PROGRAM_EXIT;
379
380 END;
381
382
383 procedure build_procedure_call (
384 p_transtage_id IN pls_integer,
385 p_procedure_name IN Varchar2,
386 x_proc_cursor OUT NOCOPY pls_integer) IS
387
388 i_method_name varchar2(2000) := 'ecx_utils.build_procedure_call';
389 error_position pls_integer;
390 l_proc_call Varchar2(32000);
391 l_first_param Boolean := True;
392 parse_error EXCEPTION;
393
394 BEGIN
395 if (l_procedureEnabled) then
396 ecx_debug.push(i_method_name);
397 end if;
398 if(l_statementEnabled) then
399 ecx_debug.log(l_statement,'p_transtage_id',p_transtage_id,i_method_name);
400 ecx_debug.log(l_statement,'p_procedure_name',p_procedure_name,i_method_name);
401 end if;
402 l_proc_call := 'BEGIN ' || p_procedure_name || '(';
403
404 if (ecx_utils.g_procedure_mappings.count <> 0)
405 then
406 for i in ecx_utils.g_procedure_mappings.first..ecx_utils.g_procedure_mappings.last
407 loop
408 if (ecx_utils.g_procedure_mappings(i).transtage_id = p_transtage_id) and
409 (ecx_utils.g_procedure_mappings(i).procedure_name = p_procedure_name) then
410
411 if not (l_first_param) then
412 l_proc_call := l_proc_call || ', ';
413 else
414 l_first_param := false;
415 end if;
416
417 l_proc_call := l_proc_call || ecx_utils.g_procedure_mappings(i).parameter_name ||
418 ' => :' || ecx_utils.g_procedure_mappings(i).parameter_name;
419 end if;
420 end loop;
421 end if;
422
423 l_proc_call := l_proc_call || '); END;';
424 ecx_utils.g_procedure_list(p_transtage_id).procedure_call := l_proc_call;
425
426 for i in ecx_utils.g_procedure_list.first..ecx_utils.g_procedure_list.last-1
427 loop
428 if(ecx_utils.g_procedure_list.EXISTS(i) and ecx_utils.g_procedure_list(i).procedure_call = l_proc_call) then
429 x_proc_cursor := ecx_utils.g_procedure_list(i).cursor_handle;
430 if(l_statementEnabled) then
431 ecx_debug.log(l_statement, 'proc_call', l_proc_call,i_method_name);
432 ecx_debug.log(l_statement,'x_proc_cursor',x_proc_cursor,i_method_name);
433 end if;
434 if (l_procedureEnabled) then
435 ecx_debug.pop(i_method_name);
436 end if;
437 return;
438 end if;
439 end loop;
440 if(l_statementEnabled) then
441 ecx_debug.log(l_statement, 'proc_call', l_proc_call,i_method_name);
442 end if;
443 x_proc_cursor := dbms_sql.open_cursor;
444 BEGIN
445 dbms_sql.parse (x_proc_cursor, l_proc_call, dbms_sql.native);
446 EXCEPTION
447 when others then
448 raise parse_error;
449 END;
450 if(l_statementEnabled) then
451 ecx_debug.log(l_statement,'x_proc_cursor',x_proc_cursor,i_method_name);
452 end if;
453 if (l_procedureEnabled) then
454 ecx_debug.pop(i_method_name);
455 end if;
456 EXCEPTION
457 WHEN PARSE_ERROR then
458 error_position := dbms_sql.last_error_position;
459 ecx_error_handling_pvt.print_parse_error (error_position, l_proc_call);
460 if(l_unexpectedEnabled) then
461 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
462 'PROGRESS_LEVEL','ecx_utils.BUILD_PROCEDURE_CALL');
463 ecx_debug.log(l_unexpected, 'ECX', 'ECX_PARSE_ERROR', i_method_name);
464 end if;
465 ecx_debug.setErrorInfo(1, 25, 'ECX_PARSE_ERROR');
466 if (l_procedureEnabled) then
467 ecx_debug.pop(i_method_name);
468 end if;
469 raise program_exit;
470
471 WHEN PROGRAM_EXIT then
472 if (l_procedureEnabled) then
473 ecx_debug.pop(i_method_name);
474 end if;
475 raise;
476
477 WHEN OTHERS THEN
478 if(l_statementEnabled) then
479 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
480 'PROGRESS_LEVEL','ecx_utils.BUILD_PROCEDURE_CALL');
481 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
482 'ERROR_MESSAGE',SQLERRM);
483 ecx_debug.log(l_unexpected, 'ECX',
484 ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.BUILD_PROCEDURE_CALL: ',
485 i_method_name);
486 end if;
487 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.BUILD_PROCEDURE_CALL: ');
488
489 if (l_procedureEnabled) then
490 ecx_debug.pop(i_method_name);
491 end if;
492 raise PROGRAM_EXIT;
493
494 END build_procedure_call;
495
496 procedure print_nodes
497 (
498 i in pls_integer,
499 g_source IN dtd_node_tbl
500 )
501 is
502 i_method_name varchar2(2000) := 'ecx_utils.print_nodes';
503 begin
504 if (g_source.EXISTS(i))
505 then
506
507 if(l_statementEnabled) then
508 ecx_debug.log(l_statement, i || '|'||
509 g_source(i).attribute_id || '|' ||
510 g_source(i).parent_attribute_id || '|' ||
511 g_source(i).external_level || '|' ||
512 g_source(i).attribute_name || '|' ||
513 g_source(i).occurrence || '|' ||
514 g_source(i).map_attribute_id || '|' ||
515 g_source(i).attribute_type || '|' ||
516 g_source(i).default_value || '|' ||
517 g_source(i).data_type || '|' ||
518 g_source(i).cond_node || '|' ||
519 g_source(i).cond_node_type || '|' ||
520 g_source(i).cond_value || '|' ||
521 g_source(i).parent_node_map_id || '|' ||
522 g_source(i).has_attributes || '|' ||
523 g_source(i).leaf_node,i_method_name);
524 end if;
525 end if;
526 end print_nodes;
527
528 procedure load_attributes
529 (
530 i_map_id IN pls_integer,
531 i_level_id IN pls_integer,
532 i_object_level IN pls_integer,
533 g_tbl IN OUT NOCOPY dtd_node_tbl,
534 g_level IN OUT NOCOPY level_tbl
535 ) is
536 i_method_name varchar2(2000) := 'ecx_utils.load_attributes';
537
538 /* TYPE object_record is RECORD
539 (
540 attribute_id ecx_object_attributes.attribute_id%TYPE,
541 attribute_name ecx_object_attributes.attribute_name%TYPE,
542 parent_attribute_id ecx_object_attributes.parent_attribute_id%TYPE,
543 base_column_name varchar2(500),--ecx_object_attributes.object_column_flag%TYPE,
544 xref_category_id ecx_object_attributes.xref_category_id%TYPE,
545 attribute_type ecx_object_attributes.attribute_type%TYPE,
546 default_value ecx_object_attributes.default_value%TYPE,
547 data_type ecx_object_attributes.data_type%TYPE,
548 has_attributes ecx_object_attributes.has_attributes%TYPE,
549 leaf_node ecx_object_attributes.leaf_node%TYPE,
550 occurrence ecx_object_attributes.occurrence%TYPE,
551 cond_value ecx_object_attributes.cond_value%TYPE,
552 cond_node ecx_object_attributes.cond_node%TYPE,
553 cond_node_type ecx_object_attributes.cond_node_type%TYPE,
554 source_attribute_id ecx_attribute_mappings.SOURCE_ATTRIBUTE_ID%TYPE
555 );
556
557
558 TYPE object_rec_table is TABLE of object_record index by BINARY_INTEGER;
559 obj_rec_table object_rec_table;
560 */
561
562 TYPE t_attribute_id is TABLE of ecx_object_attributes.attribute_id%TYPE;
563 TYPE t_attribute_name is TABLE of ecx_object_attributes.attribute_name%TYPE;
564 TYPE t_parent_attribute_id is TABLE of ecx_object_attributes.parent_attribute_id%TYPE;
565 TYPE t_base_column_name is TABLE of varchar2(500);--ecx_object_attributes.object_column_flag%TYPE,
566 TYPE t_xref_category_id is TABLE of ecx_object_attributes.xref_category_id%TYPE;
567 TYPE t_attribute_type is TABLE of ecx_object_attributes.attribute_type%TYPE;
568 TYPE t_default_value is TABLE of ecx_object_attributes.default_value%TYPE;
569 TYPE t_data_type is TABLE of ecx_object_attributes.data_type%TYPE;
570 TYPE t_has_attributes is TABLE of ecx_object_attributes.has_attributes%TYPE;
571 TYPE t_leaf_node is TABLE of ecx_object_attributes.leaf_node%TYPE;
572 TYPE t_occurrence is TABLE of ecx_object_attributes.occurrence%TYPE;
573 TYPE t_cond_value is TABLE of ecx_object_attributes.cond_value%TYPE;
574 TYPE t_cond_node is TABLE of ecx_object_attributes.cond_node%TYPE;
575 TYPE t_cond_node_type is TABLE of ecx_object_attributes.cond_node_type%TYPE;
576 TYPE t_source_attribute_id is TABLE of ecx_attribute_mappings.SOURCE_ATTRIBUTE_ID%TYPE;
577 TYPE t_required_flag is TABLE of ecx_object_attributes.required_flag%TYPE;
578
579 v_attribute_id t_attribute_id;
580 v_attribute_name t_attribute_name;
581 v_parent_attribute_id t_parent_attribute_id;
582 v_base_column_name t_base_column_name;
583 v_xref_category_id t_xref_category_id;
584 v_attribute_type t_attribute_type;
585 v_default_value t_default_value;
586 v_data_type t_data_type;
587 v_has_attributes t_has_attributes;
588 v_leaf_node t_leaf_node;
589 v_occurrence t_occurrence;
590 v_cond_value t_cond_value;
591 v_cond_node t_cond_node;
592 v_cond_node_type t_cond_node_type;
593 v_source_attribute_id t_source_attribute_id;
594 v_required_flag t_required_flag;
595
596 TYPE dtd_map is RECORD
597 (
598 source_attribute_id ecx_attribute_mappings.source_attribute_id%TYPE
599 );
600
601
602 TYPE dtd_map_table is TABLE of dtd_map index by BINARY_INTEGER;
603
604 dtd_map_tbl dtd_map_table;
605
606
607 /** Cursor for Loading Attributes **/
608 /*
609 cursor object_rec
610 (
611 p_map_id IN pls_integer,
612 p_level_id IN pls_integer
613 ) IS
614 select attribute_id,
615 attribute_name,
616 parent_attribute_id,
617 decode(object_column_flag,'Y',attribute_name,null) base_column_name,
618 xref_category_id,
619 attribute_type,
620 default_value,
621 data_type,
622 has_attributes,
623 leaf_node,
624 occurrence,
625 cond_value,
626 cond_node,
627 cond_node_type
628 from ecx_object_attributes eoa
629 where eoa.map_id = p_map_id
630 and eoa.objectlevel_id = p_level_id
631 order by attribute_id;
632
633 cursor dtd_map (
634 p_map_id IN pls_integer,
635 p_attribute_id IN pls_integer ) IS
636 select source_attribute_id
637 from ecx_attribute_mappings eam
638 where eam.target_attribute_id = p_attribute_id
639 and eam.map_id = p_map_id;
640 */
641 l_dtd_id pls_integer;
642 l_map_type Varchar2(20);
643 i pls_integer := 0;
644 j pls_integer;
645 k pls_integer := 1;
646 no_seed_data EXCEPTION;
647
648 begin
649 if (l_procedureEnabled) then
650 ecx_debug.push(i_method_name);
651 end if;
652 if(l_statementEnabled) then
653 ecx_debug.log(l_statement,'i_map_id', i_map_id,i_method_name);
654 ecx_debug.log(l_statement,'i_level_id',i_level_id,i_method_name);
655 ecx_debug.log(l_statement,'i_object_level',i_object_level,i_method_name);
656 end if;
657 select
658 a.attribute_id,
659 a.attribute_name,
660 a.parent_attribute_id,
661 decode(a.object_column_flag,'Y',attribute_name,null) base_column_name,
662 a.xref_category_id,
663 a.attribute_type,
664 a.default_value,
665 a.data_type,
666 a.has_attributes,
667 a.leaf_node,
668 a.required_flag,
669 a.occurrence,
670 a.cond_value,
671 a.cond_node,
672 a.cond_node_type,
673 m.SOURCE_ATTRIBUTE_ID source_attribute_id
674
675 bulk collect into
676 -- obj_rec_table
677
678 v_attribute_id,
679 v_attribute_name,
680 v_parent_attribute_id,
681 v_base_column_name,
682 v_xref_category_id ,
683 v_attribute_type,
684 v_default_value,
685 v_data_type,
686 v_has_attributes,
687 v_leaf_node,
688 v_required_flag,
689 v_occurrence,
690 v_cond_value,
691 v_cond_node,
692 v_cond_node_type,
693 v_source_attribute_id
694
695 from ecx_object_attributes a,
696 ecx_attribute_mappings m
697 where a.map_id = i_map_id
698 and a.objectlevel_id = i_level_id
699 and m.map_id (+) = i_map_id
700 and m.TARGET_ATTRIBUTE_ID (+) = a.ATTRIBUTE_ID
701 order by a.attribute_id;
702
703
704 -- if obj_rec_table.COUNT > 0 then
705 if v_attribute_id.COUNT > 0 then
706 for c1 in v_attribute_id.FIRST..v_attribute_id.LAST
707 -- for c1 in object_rec(i_map_id,i_level_id)
708 loop
709 i := v_attribute_id(c1);
710 g_tbl(i).attribute_id := v_attribute_id(c1);
711 g_tbl(i).attribute_name := v_attribute_name(c1);
712 g_tbl(i).parent_attribute_id := v_parent_attribute_id(c1);
713 g_tbl(i).attribute_type := v_attribute_type(c1);
714 g_tbl(i).default_value := v_default_value(c1);
715 g_tbl(i).data_type := v_data_type(c1);
716 g_tbl(i).external_level := i_object_level;
717 g_tbl(i).internal_level := i_object_level;
718 g_tbl(i).has_attributes := v_has_attributes(c1);
719 g_tbl(i).leaf_node := v_leaf_node(c1);
720 g_tbl(i).base_column_name := v_base_column_name(c1);
721 g_tbl(i).xref_category_id := v_xref_category_id(c1);
722 g_tbl(i).cond_node_type := v_cond_node_type(c1);
723 g_tbl(i).cond_node := v_cond_node(c1);
724 g_tbl(i).cond_value := v_cond_value(c1);
725 g_tbl(i).occurrence := v_occurrence(c1);
726 g_tbl(i).map_attribute_id := v_source_attribute_id(c1);
727 g_tbl(i).required_flag := v_required_flag(c1);
728
729 /** Find the Start element for this Level **/
730 --if i_object_level <> 0
731 --then
732 --if (g_tbl(i).attribute_name = g_level(i_object_level).start_element) then
733 --g_level(i_object_level).dtd_node_index := i;
734 --ecx_debug.log(3, 'dtd_node_index', g_level(i_object_level).dtd_node_index || ' at level' || i_object_level);
735 --end if;
736
737 -- select source_attribute_id
738 -- bulk collect into dtd_map_tbl
739 -- from ecx_attribute_mappings eam
740 -- where eam.target_attribute_id = obj_rec_table(c1).attribute_id
741 -- and eam.map_id = i_map_id;
742 -- if dtd_map_tbl.COUNT > 1 then
743 -- dbms_output.put_line('dtd_map_tbl.COUNT=' || dtd_map_tbl.COUNT);
744 -- end if;
745 --
746 -- if dtd_map_tbl.COUNT > 0 then
747 -- for c2 in dtd_map_tbl.FIRST..dtd_map_tbl.LAST
748 -- loop
749 -- g_tbl(i).map_attribute_id := dtd_map_tbl(c2).source_attribute_id;
750 -- end loop;
751 -- end if;
752 -- for c2 in dtd_map (
753 -- i_map_id,
754 -- obj_rec_table(c1).attribute_id
755 -- )
756 -- loop
757 -- g_tbl(i).map_attribute_id := c2.source_attribute_id;
758 -- end loop;
759
760 if i_object_level = 0
761 then
762 g_level(i_object_level).file_start_pos := 0;
763 else
764 if (g_level(i_object_level).file_start_pos = 0) then
765 g_level(i_object_level).file_start_pos := i;
766 end if;
767 end if;
768 end loop;
769 end if;
770
771 if (g_tbl.COUNT <> 0)
772 then
773 g_level(i_object_level).file_end_pos := i;
774 -- for i in g_level(i_object_level).file_start_pos..g_level(i_object_level).file_end_pos
775 -- loop
776 -- print_nodes(i,g_tbl);
777 -- end loop;
778 end if;
779
780 if (l_procedureEnabled) then
781 ecx_debug.pop(i_method_name);
782 end if;
783 exception
784 WHEN PROGRAM_EXIT then
785 if (l_procedureEnabled) then
786 ecx_debug.pop(i_method_name);
787 end if;
788 raise;
789 WHEN OTHERS THEN
790 if(l_unexpectedEnabled) then
791 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
792 'PROGRESS_LEVEL',
793 'ECX_UTILS.LOAD_ATTRIBUTES');
794 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
795 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.LOAD_ATTRIBUTES: ',
796 i_method_name);
797 end if;
798 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.LOAD_ATTRIBUTES: ');
799 if (l_procedureEnabled) then
800 ecx_debug.pop(i_method_name);
801 end if;
802 raise PROGRAM_EXIT;
803 end LOAD_ATTRIBUTES;
804
805 /* This procedure is called by the Inbound/Outbound Engine to Load Source and the Target */
806 procedure load_dtd_nodes(
807 i_map_id IN pls_integer,
808 i_level_id IN pls_integer,
809 i_object_level IN pls_integer,
810 i_source IN boolean) IS
811
812 i_method_name varchar2(2000) := 'ecx_utils.load_dtd_nodes';
813 l_dtd_id pls_integer;
814 l_map_type Varchar2(20);
815 i pls_integer := 0;
816 j pls_integer;
817 k pls_integer := 1;
818 no_seed_data EXCEPTION;
819
820 BEGIN
821 if (l_procedureEnabled) then
822 ecx_debug.push(i_method_name);
823 end if;
824 if(l_statementEnabled) then
825 ecx_debug.log(l_statement,'i_map_id', i_map_id,i_method_name);
826 ecx_debug.log(l_statement,'i_level_id',i_level_id,i_method_name);
827 ecx_debug.log(l_statement,'i_object_level',i_object_level,i_method_name);
828 ecx_debug.log(l_statement,'i_source',i_source,i_method_name);
829 end if;
830
831 if ( i_source )
832 then
833 load_attributes(i_map_id,i_level_id,i_object_level,ecx_utils.g_source,ecx_utils.g_source_levels);
834 else
835 load_attributes(i_map_id,i_level_id,i_object_level,ecx_utils.g_target,ecx_utils.g_target_levels);
836 end if;
837
838 if (l_procedureEnabled) then
839 ecx_debug.pop(i_method_name);
840 end if;
841
842 EXCEPTION
843 WHEN NO_SEED_DATA then
844 ecx_debug.setErrorInfo(1, 30, 'ECX_SEED_DATA_NOT_FOUND', 'MAP_ID', i_map_id);
845
846 if(l_unexpectedEnabled) then
847 ecx_debug.log(l_unexpected, 'ECX', 'ECX_SEED_DATA_FOUND',i_method_name,'MAP_ID', i_map_id);
848 end if;
849 if (l_procedureEnabled) then
850 ecx_debug.pop(i_method_name);
851 end if;
852 raise PROGRAM_EXIT;
853
854 WHEN PROGRAM_EXIT then
855 ecx_debug.pop('ECX_UTILS.LOAD_DTD_NODES');
856 raise;
857 WHEN OTHERS THEN
858 if(l_unexpectedEnabled) then
859 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
860 'PROGRESS_LEVEL',
861 'ECX_UTILS.LOAD_DTD_NODES');
862 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.LOAD_DTD_NODES',
863 i_method_name);
864 end if;
865 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.LOAD_DTD_NODES');
866
867 if (l_procedureEnabled) then
868 ecx_debug.pop(i_method_name);
869 end if;
870 raise PROGRAM_EXIT;
871 END LOAD_DTD_NODES;
872
873 procedure createDummyData(
874 root_level_found IN OUT NOCOPY Boolean)
875 is
876
877
878 /* Source Level 0 cursor */
879 cursor get_level_zero (
880 p_map_id IN pls_integer) IS
881 select objectlevel_id,
882 object_level,
883 object_level_name,
884 parent_level
885 from ecx_object_levels eol,
886 ecx_mappings em
887 where eol.map_id = p_map_id
888 and eol.map_id = em.map_id
889 and eol.object_id = 1
890 and eol.object_level = 0
891 order by objectlevel_id;
892
893 begin
894
895 -- we need load dummy data for g_source, g_target, g_source_levels
896 --dummy data for g_source_levels can be gotten from ecx_object_levels
897 for c_level_zero in get_level_zero(p_map_id=>g_map_id)
898 loop
899 -- should return only one match
900 ecx_utils.g_source_levels(0).level := c_level_zero.object_level;
901 ecx_utils.g_source_levels(0).parent_level := c_level_zero.parent_level;
902 ecx_utils.g_source_levels(0).start_element := c_level_zero.object_level_name;
903 ecx_utils.g_source_levels(0).base_table_name := c_level_zero.object_level_name;
904 ecx_utils.g_source_levels(0).sql_stmt := NULL;
905 ecx_utils.g_source_levels(0).cursor_handle := null;
906 ecx_utils.g_source_levels(0).file_start_pos := 0;
907 ecx_utils.g_source_levels(0).file_end_pos := 0;
908 ecx_utils.g_source_levels(0).dtd_node_index := 0;
909 end loop;
910
911 ecx_utils.g_source(0).attribute_id := 0;
912 ecx_utils.g_source(0).attribute_name := ecx_utils.g_source_levels(0).start_element;
913 ecx_utils.g_source(0).parent_attribute_id := 0;
914 ecx_utils.g_source(0).attribute_type := 1;
915 ecx_utils.g_source(0).default_value := null;
916 ecx_utils.g_source(0).data_type := null;
917 ecx_utils.g_source(0).external_level := 0;
918 ecx_utils.g_source(0).internal_level := 0;
919 ecx_utils.g_source(0).has_attributes := 0;
920 ecx_utils.g_source(0).leaf_node := 0;
921
922 -- load all level 0 attributes
923 load_dtd_nodes( g_map_id,0, 0,true);
924
925 ecx_utils.g_target(0).attribute_id := 0;
926 ecx_utils.g_target(0).attribute_name := ecx_utils.g_target_levels(0).start_element;
927 ecx_utils.g_target(0).parent_attribute_id := 0;
928 ecx_utils.g_target(0).attribute_type := 1;
929 ecx_utils.g_target(0).default_value := null;
930 ecx_utils.g_target(0).data_type := null;
931 ecx_utils.g_target(0).external_level := 0;
932 ecx_utils.g_target(0).internal_level := 0;
933 ecx_utils.g_target(0).has_attributes := 0;
934 ecx_utils.g_target(0).leaf_node := 0;
935
936 ecx_utils.g_target_source_levels(0).source_level := 0;
937 ecx_utils.g_target_source_levels(0).target_level := 0;
938
939 root_level_found := TRUE;
940 end createDummyData;
941
942
943 procedure load_mappings (
944 i_map_id in pls_integer) IS
945
946 i_method_name varchar2(2000) := 'ecx_utils.load_mappings';
947
948 /* TYPE target_record
949 is RECORD (target_level_id ecx_object_levels.objectlevel_id%TYPE,
950 target_level ecx_object_levels.object_level%TYPE,
951 target_level_name ecx_object_levels.object_level_name%TYPE,
952 parent_level ecx_object_levels.parent_level%TYPE);
953
954 TYPE target_record_table is TABLE of target_record index by BINARY_INTEGER;
955 targ_rec_table target_record_table;
956 */
957 TYPE t_target_level_id is TABLE of ecx_object_levels.objectlevel_id%TYPE;
958 TYPE t_target_level is TABLE of ecx_object_levels.object_level%TYPE;
959 TYPE t_target_level_name is TABLE of ecx_object_levels.object_level_name%TYPE;
960 TYPE t_parent_level is TABLE of ecx_object_levels.parent_level%TYPE;
961
962 v_target_level_id t_target_level_id;
963 v_target_level t_target_level;
964 v_target_level_name t_target_level_name;
965 v_parent_level t_parent_level;
966
967
968 /*
969 ** This cursor loads the details of the Target level
970 */
971 /* cursor target_rec (
972 p_map_id IN pls_integer) IS
973 select objectlevel_id target_level_id,
974 object_level target_level,
975 object_level_name target_level_name,
976 parent_level
977 from ecx_object_levels eol,
978 ecx_mappings em
979 where eol.map_id = p_map_id
980 and eol.map_id = em.map_id
981 and eol.object_id = em.object_id_target
982 order by target_level;
983 */
984 /*
985
986 TYPE target_source_record
987 is RECORD (source_level ecx_object_levels.object_level%TYPE,
988 level_mapping_id ecx_level_mappings.level_mapping_id%TYPE,
989 source_level_id ecx_object_levels.objectlevel_id%TYPE,
990 source_element_id ecx_level_mappings.source_element_id%TYPE,
991 target_element_id ecx_level_mappings.target_element_id%TYPE
992 );
993
994 TYPE target_source_record_table is TABLE of target_source_record index by BINARY_INTEGER;
995 targ_src_rec_table target_source_record_table;
996 */
997 TYPE t_source_level is TABLE of ecx_object_levels.object_level%TYPE;
998 TYPE t_level_mapping_id is TABLE of ecx_level_mappings.level_mapping_id%TYPE;
999 TYPE t_source_level_id is TABLE of ecx_object_levels.objectlevel_id%TYPE;
1000 TYPE t_source_element_id is TABLE of ecx_level_mappings.source_element_id%TYPE;
1001 TYPE t_target_element_id is TABLE of ecx_level_mappings.target_element_id%TYPE;
1002
1003
1004 v_source_level t_source_level;
1005 v_level_mapping_id t_level_mapping_id;
1006 v_source_level_id t_source_level_id;
1007 v_source_element_id t_source_element_id;
1008 v_target_element_id t_target_element_id;
1009
1010 /*
1011 ** This cursor loads the details of the relationship between
1012 ** target and source level
1013 */
1014 /*
1015 cursor target_source_rec (
1016 p_map_id IN pls_integer,
1017 p_target_id IN pls_integer) IS
1018 select object_level source_level,
1019 level_mapping_id ,
1020 objectlevel_id source_level_id,
1021 source_element_id,
1022 target_element_id
1023 from ecx_level_mappings elm,
1024 ecx_object_levels eol
1025 where elm.target_level_id = p_target_id
1026 and elm.map_id = p_map_id
1027 and elm.map_id = eol.map_id
1028 and elm.source_level_id = eol.objectlevel_id;
1029 */
1030 /* Source Level Details Cursor */
1031 cursor source_rec (
1032 p_map_id IN pls_integer,
1033 p_source_level_id IN pls_integer) IS
1034 select object_level source_level,
1035 object_level_name source_level_name,
1036 parent_level,
1037 objectlevel_id
1038 from ecx_object_levels
1039 where objectlevel_id = p_source_level_id
1040 and map_id = p_map_id;
1041
1042
1043 j_count pls_integer := 0;
1044 int_loaded Boolean := False;
1045 int_ext_loaded Boolean := False;
1046 cur_ext_level pls_integer := 0;
1047 pre_int_level pls_integer := 0;
1048 cur_int_level pls_integer := 0;
1049 l_level_mapping_id pls_integer := 0;
1050 i_object_id_source pls_integer;
1051 i_object_id_target pls_integer;
1052 first_time Boolean := true;
1053 root_level_found Boolean := false;
1054
1055 BEGIN
1056 if (l_procedureEnabled) then
1057 ecx_debug.push(i_method_name);
1058 end if;
1059 if(l_statementEnabled) then
1060 ecx_debug.log(l_statement,'i_map_id',i_map_id,i_method_name);
1061 end if;
1062
1063 ecx_utils.g_source_levels.DELETE;
1064 ecx_utils.g_target_levels.DELETE;
1065 ecx_utils.g_target_source_levels.DELETE;
1066 ecx_utils.g_target.DELETE;
1067 ecx_utils.g_source.DELETE;
1068 if(l_statementEnabled) then
1069 ecx_debug.log(l_statement,'ECX','ECX_LOADING_LEVELS',i_method_name,
1070 'LEVEL','Loading Target levels');
1071 end if;
1072 -- Loading Target level information.
1073
1074 select objectlevel_id target_level_id,
1075 object_level target_level,
1076 object_level_name target_level_name,
1077 parent_level
1078 bulk collect into
1079 -- targ_rec_table
1080
1081 v_target_level_id,
1082 v_target_level,
1083 v_target_level_name,
1084 v_parent_level
1085
1086 from ecx_object_levels eol,
1087 ecx_mappings em
1088 where eol.map_id = i_map_id
1089 and eol.map_id = em.map_id
1090 and eol.object_id = em.object_id_target
1091 order by target_level;
1092
1093 -- if targ_rec_table.COUNT > 0 then
1094 -- for j in targ_rec_table.FIRST..targ_rec_table.LAST
1095 if v_target_level_id.COUNT > 0 then
1096 for j in v_target_level_id.FIRST..v_target_level_id.LAST
1097 -- for c1 in target_rec ( p_map_id => i_map_id )
1098 loop
1099 -- ecx_debug.log(3,'Target Level Id',targ_rec_table(j).target_level_id||' Target Level =>'||targ_rec_table(j).target_level);
1100 cur_ext_level := v_target_level(j);
1101 ecx_utils.g_target_levels(cur_ext_level).level := cur_ext_level;
1102 ecx_utils.g_target_levels(cur_ext_level).level := v_parent_level(j);
1103 ecx_utils.g_target_levels(cur_ext_level).start_element := v_target_level_name(j);
1104 ecx_utils.g_target_levels(cur_ext_level).base_table_name := v_target_level_name(j);
1105 ecx_utils.g_target_levels(cur_ext_level).sql_stmt := NULL;
1106 ecx_utils.g_target_levels(cur_ext_level).cursor_handle := null;
1107 ecx_utils.g_target_levels(cur_ext_level).file_start_pos := 0;
1108 ecx_utils.g_target_levels(cur_ext_level).file_end_pos := 0;
1109 ecx_utils.g_target_levels(cur_ext_level).dtd_node_index := 0;
1110 if(l_statementEnabled) then
1111 ecx_debug.log(l_statement, 'External Level ('|| cur_ext_level||')',
1112 ecx_utils.g_target_levels(cur_ext_level).level,i_method_name);
1113 ecx_debug.log(l_statement, 'External Level ('||cur_ext_level||') Object Name: '
1114 || 'start_element: ' || ecx_utils.g_target_levels (cur_ext_level).start_element,
1115 i_method_name);
1116 end if;
1117
1118 /** Load the Target Object Attributes **/
1119 load_dtd_nodes( i_map_id,v_target_level_id(j),v_target_level(j),false);
1120
1121 -- ecx_debug.log(3,'ECX','ECX_LOADING_LEVELS','LEVEL','Loading Levels Matrices');
1122 int_ext_loaded := False;
1123
1124 -- Loading target and source information.
1125 -- ecx_debug.log(3, 'Target Level Id', targ_rec_table(j).target_level_id );
1126 if(l_statementEnabled) then
1127 ecx_debug.log(l_statement,'ECX','ECX_LOADING_LEVELS','LEVEL','Loading Levels Matrices', i_method_name);
1128 ecx_debug.log(l_statement, 'Target Level Id', v_target_level_id(j) , i_method_name);
1129 end if;
1130
1131 select object_level source_level,
1132 level_mapping_id ,
1133 objectlevel_id source_level_id,
1134 source_element_id,
1135 target_element_id
1136 bulk collect into
1137 -- targ_src_rec_table
1138 v_source_level,
1139 v_level_mapping_id,
1140 v_source_level_id,
1141 v_source_element_id,
1142 v_target_element_id
1143
1144 from ecx_level_mappings elm,
1145 ecx_object_levels eol
1146 where elm.target_level_id = v_target_level_id(j)
1147 and elm.map_id = i_map_id
1148 and elm.map_id = eol.map_id
1149 and elm.source_level_id = eol.objectlevel_id;
1150
1151 -- if targ_src_rec_table.COUNT > 0 then
1152 -- for k in targ_src_rec_table.FIRST..targ_src_rec_table.LAST
1153 if v_source_level.COUNT > 0 then
1154 for k in v_source_level.FIRST..v_source_level.LAST
1155 -- for c2 in target_source_rec (
1156 -- p_map_id => i_map_id,
1157 -- p_target_id => targ_rec_table(j).target_level_id)
1158 loop
1159
1160 int_ext_loaded := True;
1161 cur_int_level := v_source_level(k);
1162
1163 -- check if Zero level mapping is present in the map. If not, then we need to
1164 -- load the dummy data for this
1165 if (cur_ext_level = 0 AND cur_int_level = 0)
1166 then
1167 --set the boolean for the root element
1168 root_level_found := TRUE;
1169 end if;
1170
1171 -- get the correct index for level_mapping
1172 -- if the map has level 0 mapping information then startthe index from 0
1173 -- else start the index from 1
1174 if(v_target_level(j) = 0 AND (first_time))
1175 then
1176 --this is the new 0 level mapping, so start j_count from 0
1177 j_count := 0;
1178 elsif(v_target_level(j) = 1 AND (first_time))
1179 then
1180 --this is the old 1 level mapping, so start j_count from 1
1181 j_count := 1;
1182 end if;
1183 ecx_utils.g_target_source_levels(j_count).source_level := cur_int_level;
1184 ecx_utils.g_target_source_levels(j_count).target_level := cur_ext_level;
1185 if(l_statementEnabled) then
1186 ecx_debug.log(l_statement,'Source and target Node Index ',v_source_element_id(k)||' '||v_target_element_id(k), i_method_name);
1187 end if;
1188 j_count := j_count + 1;
1189
1190 /** Update the Target Node Index **/
1191 ecx_utils.g_target_levels(cur_ext_level).dtd_node_index := v_target_element_id(k);
1192 if(l_statementEnabled) then
1193 ecx_debug.log(l_statement, 'Target Node_index', g_target_levels(cur_ext_level).dtd_node_index ||
1194 ' at level' || cur_ext_level, i_method_name);
1195 ecx_debug.log(l_statement, 'Internal Level '|| cur_int_level||' External Level '
1196 || cur_ext_level, i_method_name);
1197
1198 ecx_debug.log(l_statement,'Source Level Id ' ,v_source_level_id(k), i_method_name);
1199 -- Loading Internal Level information.
1200 ecx_debug.log(l_statement, 'ECX', 'ECX_LOADING_LEVELS', i_method_name,
1201 'LEVEL', 'Loading Source Levels');
1202 end if;
1203 -- If the current interface level is the same as previous one,
1204 -- then the interface info for the current level has been loaded.
1205 -- The only exception for this is the root element loading, so check
1206 -- the flag for root element
1207 if ((first_time) OR (cur_int_level <> pre_int_level)) then
1208 int_loaded := False;
1209 /* Load the Internal Level */
1210 for c3 in source_rec ( i_map_id, v_source_level_id(k))
1211 loop
1212 int_loaded := True;
1213 ecx_utils.g_source_levels(cur_int_level).level := cur_int_level;
1214 ecx_utils.g_source_levels(cur_int_level).base_table_name := c3.source_level_name;
1215 ecx_utils.g_source_levels(cur_int_level).start_element := c3.source_level_name;
1216 ecx_utils.g_source_levels(cur_int_level).parent_level := c3.parent_level;
1217 ecx_utils.g_source_levels(cur_int_level).sql_stmt := null;
1218 ecx_utils.g_source_levels(cur_int_level).cursor_handle := null;
1219 ecx_utils.g_source_levels(cur_int_level).rows_processed := 0;
1220 ecx_utils.g_source_levels(cur_int_level).file_start_pos := 0;
1221 ecx_utils.g_source_levels(cur_int_level).file_end_pos := 0;
1222 ecx_utils.g_source_levels(cur_int_level).dtd_node_index := v_source_element_id(k);
1223 if(l_statementEnabled) then
1224 ecx_debug.log(l_statement, 'Source Node_index', g_source_levels(cur_int_level).dtd_node_index ||
1225 ' at level' || cur_int_level,i_method_name);
1226
1227 ecx_debug.log(l_statement, 'Internal Level ('||cur_int_level||') Object Name: '
1228 || ecx_utils.g_source_levels (cur_int_level).base_table_name
1229 || ' Parent Level '
1230 || ecx_utils.g_source_levels(cur_int_level).parent_level,i_method_name);
1231 end if;
1232
1233 --END IF;
1234 /** Load the Source Object Attributes **/
1235 load_dtd_nodes( i_map_id,c3.objectlevel_id,cur_int_level,true);
1236 end loop; -- interface_rec
1237
1238 if not (int_loaded) then
1239 raise no_seed_data;
1240 end if;
1241 pre_int_level := cur_int_level;
1242 first_time := false;
1243 end if;
1244
1245 end loop; -- interface_external_rec
1246 end if;
1247 -- call the appropriate function to do the initialization of dummy
1248 -- data for maps that don't have level 0 mapping information
1249 if (not root_level_found)
1250 then
1251 createDummyData(root_level_found);
1252 end if;
1253
1254 if(l_statementEnabled) then
1255 if not (int_ext_loaded) then
1256 ecx_debug.log(l_statement, 'Seed Data is missing for map_id', i_map_id,i_method_name);
1257 end if;
1258 end if;
1259
1260 end loop; -- external_rec
1261 end if;
1262 -- This is only to print out the file_start/end_pos.
1263 if(l_statementEnabled) then
1264 if (ecx_utils.g_source_levels.count <> 0)
1265 then
1266 for i in ecx_utils.g_source_levels.first..ecx_utils.g_source_levels.last
1267 loop
1268 ecx_debug.log(l_statement, 'ECX', 'ECX_INT_FILE_START',i_method_name, 'LEVEL', i,
1269 'POSITION', ecx_utils.g_source_levels(i).file_start_pos);
1270 ecx_debug.log(l_statement, 'ECX', 'ECX_INT_FILE_END',i_method_name, 'LEVEL', i,
1271 'POSITION', ecx_utils.g_source_levels(i).file_end_pos);
1272 end loop;
1273 end if;
1274 if (ecx_utils.g_target_levels.count <> 0)
1275 then
1276 for i in ecx_utils.g_target_levels.first..ecx_utils.g_target_levels.last
1277 loop
1278 ecx_debug.log(l_statement, 'ECX', 'ECX_EXT_FILE_START',i_method_name, 'LEVEL', i,
1279 'POSITION', ecx_utils.g_target_levels(i).file_start_pos);
1280 ecx_debug.log(l_statement, 'ECX', 'ECX_EXT_FILE_END',i_method_name, 'LEVEL', i,
1281 'POSITION', ecx_utils.g_target_levels(i).file_end_pos);
1282 end loop;
1283 end if;
1284 end if;
1285
1286 if (ecx_utils.g_source_levels.count <> 0 )
1287 then
1288 for i in ecx_utils.g_source_levels.first..ecx_utils.g_source_levels.last
1289 loop
1290 /** Reset all of the range variables. **/
1291 /** These assume that there are fewer than a million levels. **/
1292 -- This is the beginning of a potential expansion range
1293 g_source_levels(i).first_target_level := 1000000;
1294
1295 -- The end of a potential expansion range. If mapping is 1:1,
1296 -- same as first_target_level.
1297 g_source_levels(i).last_target_level := -1;
1298
1299 -- The beginning of a potential collapsing range
1300 g_source_levels(i).first_source_level:= 1000000;
1301
1302 -- The end of a potential collapsing range. If mapping is 1:1,
1303 -- same as first_source_level and i.
1304 g_source_levels(i).last_source_level := -1;
1305
1306 /** Retrieve the mapping ranges. **/
1307 if (ecx_utils.g_target_source_levels.count <> 0)
1308 then
1309 for k in ecx_utils.g_target_source_levels.first..ecx_utils.g_target_source_levels.last
1310 loop
1311 if ecx_utils.g_target_source_levels(k).source_level = i
1312 then
1313 /** These don't assume that the table is in order **/
1314 if ecx_utils.g_target_source_levels(k).target_level <
1315 g_source_levels(i).first_target_level
1316 then
1317 g_source_levels(i).first_target_level := ecx_utils.g_target_source_levels(k).target_level;
1318 /** The source_level could be wrong if this is a collapsing, but if so,
1319 it will be fixed in the next loop. **/
1320 g_source_levels(i).first_source_level := ecx_utils.g_target_source_levels(k).source_level;
1321 end if;
1322 if ecx_utils.g_target_source_levels(k).target_level > g_source_levels(i).last_target_level
1323 then
1324 g_source_levels(i).last_target_level := ecx_utils.g_target_source_levels(k).target_level;
1325 g_source_levels(i).last_source_level := ecx_utils.g_target_source_levels(k).source_level;
1326 end if;
1327 end if;
1328 end loop;
1329 end if;
1330
1331 /** Now that we've checked for expansion, check for collapsing. **/
1332 if g_source_levels(i).first_target_level = g_source_levels(i).last_target_level
1333 then
1334 if (ecx_utils.g_target_source_levels.count <> 0)
1335 then
1336 for k in ecx_utils.g_target_source_levels.first..ecx_utils.g_target_source_levels.last
1337 loop
1338 if ecx_utils.g_target_source_levels(k).target_level = g_source_levels(i).first_target_level
1339 then
1340 if ecx_utils.g_target_source_levels(k).source_level < g_source_levels(i).first_source_level
1341 then
1342 g_source_levels(i).first_source_level := ecx_utils.g_target_source_levels(k).source_level;
1343 end if;
1344 if ecx_utils.g_target_source_levels(k).source_level > g_source_levels(i).last_source_level
1345 then
1346 g_source_levels(i).last_source_level := ecx_utils.g_target_source_levels(k).source_level;
1347 end if;
1348 end if;
1349 end loop;
1350 end if;
1351 end if;
1352
1353 if(l_statementEnabled) then
1354 ecx_debug.log(l_statement,'Source '||i, 'SOURCE FIRST:'||g_source_levels(i).first_source_level||
1355 'SOURCE LAST:'||g_source_levels(i).last_source_level||
1356 'TARGET FIRST:'||g_source_levels(i).first_target_level||
1357 'TARGET LAST:'||g_source_levels(i).last_target_level,
1358 i_method_name
1359 );
1360 end if;
1361 end loop; --- end of source and target first and last level
1362 end if;
1363
1364 load_procedure_mappings (i_map_id);
1365 load_procedure_definitions (i_map_id);
1366
1367 if (l_procedureEnabled) then
1368 ecx_debug.pop(i_method_name);
1369 end if;
1370
1371 EXCEPTION
1372 WHEN NO_SEED_DATA then
1373 ecx_debug.setErrorInfo(1, 30, 'ECX_SEED_DATA_NOT_FOUND', 'MAP_ID', i_map_id);
1374 if(l_unexpectedEnabled) then
1375 ecx_debug.log(l_unexpected, 'ECX', 'ECX_SEED_DATA_NOT_FOUND',i_method_name, 'MAP_ID', i_map_id);
1376 end if;
1377 if (l_procedureEnabled) then
1378 ecx_debug.pop(i_method_name);
1379 end if;
1380 raise PROGRAM_EXIT;
1381
1382 WHEN PROGRAM_EXIT then
1383 if (l_procedureEnabled) then
1384 ecx_debug.pop(i_method_name);
1385 end if;
1386 raise;
1387
1388 WHEN OTHERS THEN
1389 if(l_unexpectedEnabled) then
1390 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1391 'ecx_utils.LOAD_MAPPINGS');
1392 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1393 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.LOAD_MAPPINGS: ',
1394 i_method_name);
1395 end if;
1396 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.LOAD_MAPPINGS: ');
1397 if (l_procedureEnabled) then
1398 ecx_debug.pop(i_method_name);
1399 end if;
1400 raise PROGRAM_EXIT;
1401
1402 end load_mappings;
1403
1404
1405 /*
1406 ** This procedure gets the major and minor version defined in the map
1407 ** and verifies it against the database ECX_VERSION. If these versions
1408 ** are not compatible it returns false.
1409 */
1410 procedure check_version (
1411 i_map_id IN pls_integer,
1412 i_result OUT NOCOPY boolean,
1413 i_ret_msg OUT NOCOPY varchar2 ) IS
1414
1415 i_method_name varchar2(2000) := 'ecx_utils.check_version';
1416 i_major_version pls_integer;
1417 i_minor_version pls_integer;
1418 i_eng_major_version pls_integer;
1419 i_eng_minor_version pls_integer;
1420 i_eng_version varchar2(2000);
1421
1422 begin
1423 if (l_procedureEnabled) then
1424 ecx_debug.push(i_method_name);
1425 end if;
1426 -- get the version from the map
1427 begin
1428 select em.ecx_major_version,
1429 em.ecx_minor_version
1430 into i_major_version,
1431 i_minor_version
1432 from ecx_mappings em
1433 where em.map_id = i_map_id;
1434 exception
1435 when others then
1436 if(l_unexpectedEnabled) then
1437 ecx_debug.log(l_unexpected, SQLERRM,i_method_name);
1438 end if;
1439 i_ret_msg := SQLERRM;
1440 i_result := false;
1441 if (l_procedureEnabled) then
1442 ecx_debug.pop(i_method_name);
1443 end if;
1444 return;
1445 end;
1446
1447 if (i_major_version is null AND i_minor_version is null)
1448 then
1449 if(l_statementEnabled) then
1450 ecx_debug.log(l_statement, 'ECX', 'ECX_MAJOR_MINOR_VERSION_NULL',i_method_name);
1451 end if;
1452 i_major_version := 2;
1453 i_minor_version := 6;
1454 end if;
1455
1456 if(l_statementEnabled) then
1457 ecx_debug.log(l_statement, 'i_major_version', i_major_version,i_method_name);
1458 ecx_debug.log(l_statement, 'i_minor_version', i_minor_version,i_method_name);
1459 end if;
1460
1461 -- get the engine version
1462 begin
1463 select text
1464 into i_eng_version
1465 from wf_resources
1466 where name = 'ECX_VERSION'
1467 and type = 'WFTKN'
1468 and language = 'US';
1469 exception
1470 when no_data_found then
1471 ecx_debug.setErrorInfo(1, 30, 'ECX_WF_RESCRS_VER_NOT_FOUND');
1472 if(l_unexpectedEnabled) then
1473 ecx_debug.log(l_unexpected, 'ECX', 'ECX_WF_RESCRS_VER_NOT_FOUND', i_method_name);
1474 end if;
1475 i_result := false;
1476 if (l_procedureEnabled) then
1477 ecx_debug.pop(i_method_name);
1478 end if;
1479 return;
1480 when others then
1481 ecx_debug.setErrorInfo(2, 30, SQLERRM);
1482 if(l_unexpectedEnabled) then
1483 ecx_debug.log(l_unexpected, 'ECX', SQLERRM,i_method_name);
1484 end if;
1485 i_result := false;
1486 if (l_procedureEnabled) then
1487 ecx_debug.pop(i_method_name);
1488 end if;
1489 return;
1490 end;
1491
1492 if(l_statementEnabled) then
1493 ecx_debug.log(l_statement, 'i_eng_version', i_eng_version,i_method_name);
1494 end if;
1495
1496 -- compare the map_version against the engine version
1497 i_eng_major_version := to_number(substr(i_eng_version, 1, 1));
1498 i_eng_minor_version := to_number(substr(i_eng_version, 3, 1));
1499
1500 if(l_statementEnabled) then
1501 ecx_debug.log(l_statement, 'i_eng_major_version', i_eng_major_version,i_method_name);
1502 ecx_debug.log(l_statement, 'i_eng_minor_version', i_eng_minor_version,i_method_name);
1503 end if;
1504 if (i_major_version = i_eng_major_version)
1505 then
1506 if (i_minor_version <= i_eng_minor_version)
1507 then
1508 i_ret_msg := null;
1509 i_result := true;
1510 if (l_procedureEnabled) then
1511 ecx_debug.pop(i_method_name);
1512 end if;
1513 return;
1514 else
1515 ecx_debug.setErrorInfo(1, 30, 'ECX_VERSION_MISMATCH',
1516 'i_version', i_major_version || '.' || i_minor_version,
1517 'i_eng_version', i_eng_version);
1518 if(l_statementEnabled) then
1519 ecx_debug.log(l_statement, 'ECX', 'ECX_VERSION_MISMATCH',i_method_name,
1520 'i_version', i_major_version || '.' || i_minor_version,
1521 'i_eng_version', i_eng_version);
1522 end if;
1523 i_result := false;
1524 if (l_procedureEnabled) then
1525 ecx_debug.pop(i_method_name);
1526 end if;
1527 return;
1528 end if;
1529 else
1530 ecx_debug.setErrorInfo(1, 30, 'ECX_VERSION_MISMATCH',
1531 'i_version', i_major_version || '.' || i_minor_version,
1532 'i_eng_version', i_eng_version);
1533 if(l_statementEnabled) then
1534 ecx_debug.log(l_statement, 'ECX', 'ECX_VERSION_MISMATCH', i_method_name,
1535 'i_version', i_major_version || '.' || i_minor_version,
1536 'i_eng_version', i_eng_version);
1537 end if;
1538 i_result := false;
1539 if (l_procedureEnabled) then
1540 ecx_debug.pop(i_method_name);
1541 end if;
1542 return;
1543 end if;
1544 exception
1545 when others then
1546 if(l_unexpectedEnabled) then
1547 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1548 'ecx_utils.CHECK_VERSION');
1549 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
1550 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.CHECK_VERSION',i_method_name);
1551 end if;
1552 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.CHECK_VERSION');
1553 if (l_procedureEnabled) then
1554 ecx_debug.pop(i_method_name);
1555 end if;
1556 raise ecx_utils.program_exit;
1557 end check_version;
1558
1559
1560 /*
1561 ** This procedure is called the Inbound and the Outbound Execution Engine
1562 ** It initializes and loads the info necessary for the engines
1563 */
1564 procedure initialize (
1565 i_map_id IN pls_integer,
1566 x_same_map OUT NOCOPY Boolean) IS
1567
1568 i_method_name varchar2(2000) := 'ecx_utils.initialize';
1569 /* Cursor to get the Root Element and Path to the DTD */
1570 cursor get_dtd (
1571 p_map_id IN pls_integer ,
1572 p_object_id IN pls_integer) IS
1573 select eobj.root_element,
1574 eobj.fullpath,
1575 eobj.runtime_location
1576 from ecx_objects eobj
1577 where eobj.object_id = p_object_id
1578 and eobj.map_id = p_map_id;
1579
1580 cursor getDtdClob
1581 (
1582 p_root_element in varchar2,
1583 p_filename in varchar2,
1584 p_location in varchar2
1585 )
1586 is
1587 select payload
1588 from ecx_dtds
1589 where root_element = p_root_element
1590 and filename = p_filename
1591 and ( version = p_location or p_location is null );
1592
1593 i_dtdpayload CLOB;
1594 l_dtd_path Varchar2(200);
1595 l_fullpath ecx_objects.fullpath%TYPE;
1596 l_root_element ecx_objects.root_element%TYPE;
1597 l_doctype xmlDOM.DOMDocumentType;
1598 l_runtime_location varchar2(200);
1599
1600 t_dtdpayload CLOB;
1601 t_fullpath ecx_objects.fullpath%TYPE;
1602 t_root_element ecx_objects.root_element%TYPE;
1603 t_doctype xmlDOM.DOMDocumentType;
1604 t_runtime_location varchar2(200);
1605 i_tar_obj_type varchar2(200);
1606
1607 no_dtd_exception EXCEPTION;
1608 l_direction varchar2(5);
1609 l_object_id number;
1610 i_string varchar2(2000);
1611 i_result boolean;
1612 i_ret_msg varchar2(2000);
1613
1614 i_map_code varchar2(32);
1615
1616 BEGIN
1617
1618 /* Assign local variables with the ecx_debug global variables*/
1619 l_procedure := ecx_debug.g_procedure;
1620 l_statement := ecx_debug.g_statement;
1621 l_unexpected := ecx_debug.g_unexpected;
1622 l_procedureEnabled := ecx_debug.g_procedureEnabled;
1623 l_statementEnabled := ecx_debug.g_statementEnabled;
1624 l_unexpectedEnabled := ecx_debug.g_unexpectedEnabled;
1625
1626 if (l_procedureEnabled) then
1627 ecx_debug.push(i_method_name);
1628 end if;
1629
1630 if(l_statementEnabled) then
1631 ecx_debug.log(l_statement,'i_map_id ',i_map_id,i_method_name);
1632 end if;
1633
1634 i_ret_code := 0;
1635 i_errbuf := null;
1636 ecx_utils.i_errparams := null;
1637 ecx_utils.g_total_records := 0;
1638 ecx_utils.g_current_level := 0;
1639 ecx_utils.g_previous_level := 0;
1640 g_source_object_id := null;
1641 g_target_object_id := null;
1642 ecx_print_local.last_printed := -1;
1643
1644 -- check the version
1645 ecx_utils.check_version(i_map_id, i_result, i_ret_msg);
1646 if (not i_result)
1647 then
1648 if(l_statementEnabled) then
1649 ecx_debug.log(l_statement, 'ECX', 'ECX_PROGRAM_ERROR', i_method_name, 'PROGRESS_LEVEL',
1650 'ecx_utils.INITIALIZE');
1651 ecx_debug.log(l_statement, 'ECX','ECX_ERROR_MESSAGE', i_method_name,'ERROR_MESSAGE', i_ret_msg);
1652 end if;
1653 raise ecx_utils.program_exit;
1654 end if;
1655 /* Find the map code for bug 1939677 */
1656 select map_code into i_map_code from ecx_mappings where map_id=i_map_id;
1657
1658 /* Try to find if it is an Inbound or Outbound Txn */
1659 begin
1660 SELECT object_id_source,
1661 object_id_target
1662 INTO g_source_object_id,
1663 g_target_object_id
1664 FROM ecx_mappings em
1665 WHERE em.map_id = i_map_id;
1666 exception
1667 when others then
1668 ecx_debug.setErrorInfo(1, 30, 'ECX_MAPPINGS_NOT_FOUND', 'MAP_ID', i_map_id);
1669 if(l_unexpectedEnabled) then
1670 ecx_debug.log(l_unexpected,'ECX', 'ECX_MAPPINGS_NOT_FOUND',i_method_name,
1671 'MAP_ID', i_map_id);
1672 end if;
1673 raise ecx_utils.program_exit;
1674 end;
1675
1676 -- needed for root element support
1677 ecx_print_local.first_time_printing := TRUE;
1678 ecx_utils.g_xml_frag.DELETE;
1679 --for bug 5609625
1680 --if (nvl(ecx_utils.g_map_id,0) <> i_map_id) then
1681
1682 ecx_utils.structure_printing := FALSE;
1683 ecx_utils.dom_printing := FALSE;
1684
1685 -- if ( ecx_utils.g_parser.id = -1 )
1686 -- then
1687 ecx_utils.g_parser := xmlParser.NewParser;
1688 --else
1689 --xmlParser.freeParser(ecx_utils.g_parser);
1690 --ecx_utils.g_parser := xmlParser.NewParser;
1691 -- end if;
1692
1693 --if ( ecx_utils.g_inb_parser.id not in (-1) )
1694 -- then
1695 -- xmlParser.freeParser(ecx_utils.g_inb_parser);
1696 -- end if;
1697 ---end of 5609625
1698 ecx_utils.getLogDirectory;
1699
1700 if(l_statementEnabled) then
1701 ecx_debug.log(l_statement, 'ecx_UTL_DTD_DIR', ecx_utils.g_logdir,i_method_name);
1702 end if;
1703 -- xmlparser.setbaseDir (ecx_utils.g_parser, l_dtd_path);
1704
1705 if ( g_direction = 'IN' )
1706 then
1707 -- get the source details
1708 open get_dtd ( i_map_id,g_source_object_id);
1709 fetch get_dtd into l_root_element, l_fullpath,l_runtime_location;
1710 if get_dtd%NOTFOUND then
1711 close get_dtd;
1712 raise no_dtd_exception;
1713 end if;
1714 close get_dtd;
1715
1716 if(l_statementEnabled) then
1717 ecx_debug.log(l_statement, 'l_root_element', l_root_element,i_method_name);
1718 ecx_debug.log(l_statement, 'l_fullpath', l_fullpath,i_method_name);
1719 ecx_debug.log(l_statement, 'l_runtime_location', l_runtime_location,i_method_name);
1720 end if;
1721 -- check if it is a pure Inbound case
1722 SELECT object_type
1723 INTO i_tar_obj_type
1724 FROM ecx_objects
1725 WHERE map_id = i_map_id
1726 AND object_id = 2;
1727
1728 if (i_tar_obj_type not in ('DB'))
1729 then
1730 if(l_statementEnabled) then
1731 ecx_debug.log(l_statement, 'DTD/XML on Target',i_method_name);
1732 end if;
1733 -- get the target details
1734 open get_dtd ( i_map_id,g_target_object_id);
1735 fetch get_dtd into t_root_element, t_fullpath,t_runtime_location;
1736 if get_dtd%NOTFOUND then
1737 close get_dtd;
1738 raise no_dtd_exception;
1739 end if;
1740 close get_dtd;
1741 if(l_statementEnabled) then
1742 ecx_debug.log(l_statement, 't_root_element', t_root_element,i_method_name);
1743 ecx_debug.log(l_statement, 't_fullpath', t_fullpath,i_method_name);
1744 ecx_debug.log(l_statement, 't_runtime_location', t_runtime_location,i_method_name);
1745 end if;
1746 -- check if data transformation or structure transformation
1747 if ((t_fullpath = l_fullpath) AND
1748 (t_root_element = l_root_element))
1749 then
1750 if(l_statementEnabled) then
1751 ecx_debug.log(l_statement, 'Source and Target DTDs are same',i_method_name);
1752 end if;
1753 ecx_utils.dom_printing := FALSE;
1754 ecx_utils.structure_printing := FALSE;
1755
1756 -- check for routing information
1757 if ((ecx_utils.g_routing_id not in (0)) AND
1758 (ecx_utils.g_rec_tp_id is not null))
1759 then
1760 -- this is a pass through transaction. Need to update
1761 -- the DOM
1762 ecx_utils.dom_printing := TRUE;
1763 ecx_utils.structure_printing := FALSE;
1764 --for 5609625
1765 -- initialize the DS needed for this
1766 --ecx_utils.g_node_tbl.DELETE;
1767 --end of 5609625
1768 end if;
1769 else
1770 -- DTDs are different, so this is structure transformation
1771 if(l_statementEnabled) then
1772 ecx_debug.log(l_statement,'Source and Target DTDs are different',i_method_name);
1773 end if;
1774 -- initialize the target parser
1775 ecx_utils.g_inb_parser := xmlparser.NewParser;
1776
1777 ecx_utils.dom_printing := FALSE;
1778 ecx_utils.structure_printing := TRUE;
1779 end if;
1780 end if;
1781 else
1782 open get_dtd (i_map_id,g_target_object_id);
1783 fetch get_dtd into l_root_element, l_fullpath,l_runtime_location;
1784
1785 if get_dtd%NOTFOUND then
1786 raise no_dtd_exception;
1787 end if;
1788
1789 if(l_statementEnabled) then
1790 ecx_debug.log(l_statement, 'l_root_element', l_root_element,i_method_name);
1791 ecx_debug.log(l_statement, 'l_fullpath', l_fullpath,i_method_name);
1792 ecx_debug.log(l_statement, 'l_runtime_location', l_runtime_location,i_method_name);
1793 end if;
1794 end if;
1795 if(l_statementEnabled) then
1796 ecx_debug.log(l_statement, 'Direction', g_direction,i_method_name);
1797 ecx_debug.log(l_statement, 'SOurce Object', to_char(g_source_object_id),i_method_name);
1798 ecx_debug.log(l_statement, 'Taregt Object', to_char(g_target_object_id),i_method_name);
1799 ecx_debug.log(l_statement, 'dom_printing', dom_printing,i_method_name);
1800 ecx_debug.log(l_statement, 'structure_printing', structure_printing,i_method_name);
1801 end if;
1802
1803 -- Get the DTD Clob . If not Found , continue .
1804 if ( l_root_element is not null and l_fullpath is not null)
1805 then
1806 -- DTD was specified on the Map to be used. We will check and
1807 -- make sure it is required.
1808 -- if not found , abort the process.
1809 open getDtdClob(l_root_element,l_fullpath,l_runtime_location);
1810 fetch getDtdClob into i_dtdpayload;
1811 if getDtdCLOB%NOTFOUND
1812 then
1813 close getDtdClob;
1814 raise no_dtd_exception;
1815 end if;
1816 close getDtdClob;
1817
1818 xmlparser.parseDTDCLOB(ecx_utils.g_parser,i_dtdpayload,l_root_element);
1819
1820 xmlparser.setValidationMode (ecx_utils.g_parser,true);
1821 l_doctype := xmlparser.getDocType (ecx_utils.g_parser);
1822 xmlparser.setDocType (ecx_utils.g_parser, l_doctype);
1823
1824 else
1825 --- DTD not specified in the Map. Optional. let us proceed without the DTD.
1826 xmlparser.setValidationMode (ecx_utils.g_parser, false);
1827 end if;
1828
1829 -- check if we need to load the target's clob
1830 if (i_tar_obj_type not in ('DB') AND (structure_printing))
1831 then
1832 -- Get the target DTD Clob . If not Found , continue .
1833 if ( t_root_element is not null and t_fullpath is not null)
1834 then
1835 -- DTD was specified on the Map to be used. We will check and
1836 -- make sure it is required.
1837 -- if not found , abort the process.
1838 open getDtdClob(t_root_element,t_fullpath,t_runtime_location);
1839 fetch getDtdClob into t_dtdpayload;
1840 if getDtdCLOB%NOTFOUND
1841 then
1842 close getDtdClob;
1843 raise no_dtd_exception;
1844 end if;
1845 close getDtdClob;
1846
1847 xmlparser.parseDTDCLOB(ecx_utils.g_inb_parser,t_dtdpayload,t_root_element);
1848 xmlparser.setValidationMode (ecx_utils.g_inb_parser, true);
1849 t_doctype := xmlparser.getDocType (ecx_utils.g_inb_parser);
1850 xmlparser.setDocType (ecx_utils.g_inb_parser, t_doctype);
1851 else
1852 --- DTD not specified in the Map. Optional. let us proceed without the DTD.
1853 xmlparser.setValidationMode (ecx_utils.g_inb_parser, false);
1854 end if;
1855 end if;
1856 --for bug 5609625
1857 if (nvl(ecx_utils.g_map_id,0) <> i_map_id) then
1858 x_same_map := False;
1859 ecx_utils.g_map_id := i_map_id;
1860 if(ecx_utils.dom_printing = TRUE and
1861 ecx_utils.structure_printing = FALSE)
1862 then
1863 -- initialize the DS needed for this
1864 ecx_utils.g_node_tbl.DELETE;
1865 end if;
1866 --end of 5609625
1867 ecx_utils.close_process;
1868 x_same_map := False;
1869 ecx_utils.g_map_id := i_map_id;
1870
1871
1872 /**
1873 Get all the Dynamic Inbound Staging data. The data is retrieved from the
1874 table ( ecx_tran_stage_data ) and kept in the Local Pl/SQL table. Since the
1875 data is in PL/SQL memory , no further lookups in the table are required.
1876 This helps in improving the perfromance , as un-necessary selects are saved.
1877 **/
1878 get_tran_stage_data (i_map_id);
1879
1880 load_mappings (i_map_id);
1881
1882 /**
1883 Execute the Dynamic Stage data for Stage = 10.
1884 This builds the stack table.
1885 **/
1886 /* Pre Processing for Document */
1887 ecx_actions.execute_stage_data (10,0,'S');
1888 ecx_actions.execute_stage_data (10,0,'T');
1889
1890 /**
1891 Save the PL/SQL table with default values. This will be used
1892 by all the documents.
1893 **/
1894 ecx_utils.g_empty_source := ecx_utils.g_source;
1895 ecx_utils.g_empty_target := ecx_utils.g_target;
1896
1897 else
1898
1899 x_same_map := True;
1900 ecx_actions.execute_stage_data (10,0,'S');
1901 ecx_actions.execute_stage_data (10,0,'T');
1902 ecx_utils.g_source := ecx_utils.g_empty_source;
1903 ecx_utils.g_target := ecx_utils.g_empty_target;
1904 end if;
1905
1906 --- Gets the Company name for the XML Gateway Server.
1907
1908 --- Check for the Installation Type ( Standalone or Embedded );
1909 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
1910
1911 if ecx_utils.g_install_mode = 'EMBEDDED'
1912 then
1913 i_string := 'begin
1914 fnd_profile.get('||'''ECX_OAG_LOGICALID'''||',ecx_utils.g_company_name);
1915 end;';
1916 execute immediate i_string ;
1917 else
1918 ecx_utils.g_company_name := wf_core.translate('ECX_OAG_LOGICALID');
1919 end if;
1920
1921 if (l_procedureEnabled) then
1922 ecx_debug.pop(i_method_name);
1923 end if;
1924
1925 EXCEPTION
1926 WHEN NO_DTD_EXCEPTION then
1927 ecx_debug.setErrorInfo(1, 30, 'ECX_DTD_NOT_FOUND', 'MAP_CODE', i_map_code);
1928 if(l_unexpectedEnabled) then
1929 ecx_debug.log(l_unexpected,'ECX', 'ECX_DTD_NOT_FOUND', i_method_name,'MAP_CODE', i_map_code);
1930 end if;
1931 if get_dtd%ISOPEN
1932 then
1933 close get_dtd;
1934 end if;
1935 ecx_utils.g_map_id := -1;
1936 ecx_utils.g_node_tbl.DELETE;
1937 raise program_exit;
1938
1939 WHEN PROGRAM_EXIT then
1940 ecx_utils.g_map_id := -1;
1941 ecx_utils.g_node_tbl.DELETE;
1942 if (l_procedureEnabled) then
1943 ecx_debug.pop(i_method_name);
1944 end if;
1945 raise;
1946
1947 WHEN OTHERS THEN
1948 if(l_unexpectedEnabled) then
1949 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name, 'PROGRESS_LEVEL',
1950 'ecx_utils.INITIALIZE');
1951 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1952 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.INITIALIZE: ',
1953 i_method_name);
1954 end if;
1955 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.INITIALIZE: ');
1956 ecx_utils.g_map_id := -1;
1957 ecx_utils.g_node_tbl.DELETE;
1958 if (l_procedureEnabled) then
1959 ecx_debug.pop(i_method_name);
1960 end if;
1961 raise PROGRAM_EXIT;
1962
1963 end initialize;
1964
1965 procedure close_process IS
1966
1967 i_method_name varchar2(2000) := 'ecx_utils.close_process';
1968 i pls_integer;
1969 j pls_integer;
1970
1971 -- bug 6922998
1972 -- to store the cursor handles already closed. Storing in the index column for better performance.
1973 type tclosed_cursors is table of number index by pls_integer;
1974 closed_cursors tclosed_cursors;
1975
1976 BEGIN
1977 if (l_procedureEnabled) then
1978 ecx_debug.push(i_method_name);
1979 end if;
1980
1981 -- close all open cursors.
1982 if (g_source_levels.count <> 0)
1983 then
1984 for i in g_source_levels.first..g_source_levels.last
1985 loop
1986 if (g_source_levels(i).cursor_handle is not null ) and
1987 (not closed_cursors.EXISTS( g_source_levels(i).cursor_handle) )
1988 then
1989 closed_cursors( g_source_levels(i).cursor_handle ) := -1 ;
1990 dbms_sql.close_cursor(g_source_levels(i).cursor_handle);
1991 end if;
1992 end loop;
1993 end if;
1994
1995 if(g_target_levels.count <> 0)
1996 then
1997 for i in g_target_levels.first..g_target_levels.last
1998 loop
1999 if (g_target_levels(i).cursor_handle is not null ) and
2000 (not closed_cursors.EXISTS (g_target_levels(i).cursor_handle) )
2001 then
2002 closed_cursors( g_target_levels(i).cursor_handle ) := -1;
2003 dbms_sql.close_cursor(g_target_levels(i).cursor_handle);
2004 end if;
2005 end loop;
2006 end if;
2007
2008 IF (ecx_utils.g_procedure_list.count > 0) THEN
2009 j := ecx_utils.g_procedure_list.first;
2010 WHILE j IS NOT NULL LOOP
2011 if (ecx_utils.g_procedure_list(j).cursor_handle > 0) and
2012 (ecx_utils.g_procedure_list(j).cursor_handle is not null) and
2013 (not closed_cursors.EXISTS (ecx_utils.g_procedure_list(j).cursor_handle) )
2014 then
2015 closed_cursors( ecx_utils.g_procedure_list(j).cursor_handle ) := -1;
2016 dbms_sql.close_cursor(ecx_utils.g_procedure_list(j).cursor_handle);
2017 end if;
2018 j := ecx_utils.g_procedure_list.NEXT(j);
2019 end loop;
2020 END IF;
2021
2022 if (l_procedureEnabled) then
2023 ecx_debug.pop(i_method_name);
2024 end if;
2025
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 if(l_unexpectedEnabled) then
2029 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
2030 'ecx_utils.CLOSE_PROCESS');
2031 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2032 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.CLOSE_PROCESS: ',
2033 i_method_name);
2034 end if;
2035 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ecx_utils.CLOSE_PROCESS: ');
2036 raise PROGRAM_EXIT;
2037
2038 end close_process;
2039
2040 /**
2041 Retrieves the Data from the ecx_TRAN_STAGE_DATA for a given Stage and Level.
2042 This will retrieve all the actions both on the source and the target
2043 **/
2044 procedure get_tran_stage_data (
2045 i_map_id IN pls_integer)
2046 IS
2047
2048 i_method_name varchar2(2000) := 'ecx_utils.get_tran_stage_data';
2049
2050 /* TYPE stage_data_record is RECORD (
2051 transtage_id ecx_tran_stage_data.transtage_id%TYPE,
2052 object_level ecx_object_levels.object_level%TYPE,
2053 objectlevel_id ecx_tran_stage_data.objectlevel_id%TYPE,
2054 stage ecx_tran_stage_data.stage%TYPE,
2055 object_direction ecx_tran_stage_data.object_direction%TYPE,
2056 seq_number ecx_tran_stage_data.seq_number%TYPE,
2057 action_type ecx_tran_stage_data.action_type%TYPE,
2058 variable_level ecx_tran_stage_data.variable_level%TYPE,
2059 variable_name ecx_tran_stage_data.variable_name%TYPE,
2060 variable_direction ecx_tran_stage_data.variable_direction%TYPE,
2061 variable_value ecx_tran_stage_data.variable_value%TYPE,
2062 default_value ecx_tran_stage_data.default_value%TYPE,
2063 sequence_name ecx_tran_stage_data.sequence_name%TYPE,
2064 custom_procedure_name ecx_tran_stage_data.custom_procedure_name%TYPE,
2065 data_type ecx_tran_stage_data.data_type%TYPE,
2066 function_name ecx_tran_stage_data.function_name%TYPE,
2067 where_clause ecx_tran_stage_data.where_clause%TYPE,
2068 variable_pos ecx_tran_stage_data.variable_pos%TYPE,
2069 cond_logical_operator ecx_tran_stage_data.cond_logical_operator%TYPE,
2070 cond_operator1 ecx_tran_stage_data.cond_operator1%TYPE,
2071 cond_var1_level ecx_tran_stage_data.cond_var1_level%TYPE,
2072 cond_var1_name ecx_tran_stage_data.cond_var1_name%TYPE,
2073 cond_var1_pos ecx_tran_stage_data.cond_var1_pos%TYPE,
2074 cond_var1_direction ecx_tran_stage_data.cond_var1_direction%TYPE,
2075 cond_var1_constant ecx_tran_stage_data.cond_var1_constant%TYPE,
2076 cond_val1_level ecx_tran_stage_data.cond_val1_level%TYPE,
2077 cond_val1_name ecx_tran_stage_data.cond_val1_name%TYPE,
2078 cond_val1_pos ecx_tran_stage_data.cond_val1_pos%TYPE,
2079 cond_val1_direction ecx_tran_stage_data.cond_val1_direction%TYPE,
2080 cond_val1_constant ecx_tran_stage_data.cond_val1_constant%TYPE,
2081 cond_operator2 ecx_tran_stage_data.cond_operator2%TYPE,
2082 cond_var2_level ecx_tran_stage_data.cond_var2_level%TYPE,
2083 cond_var2_name ecx_tran_stage_data.cond_var2_name%TYPE,
2084 cond_var2_pos ecx_tran_stage_data.cond_var2_pos%TYPE,
2085 cond_var2_direction ecx_tran_stage_data.cond_var2_direction%TYPE,
2086 cond_var2_constant ecx_tran_stage_data.cond_var2_constant%TYPE,
2087 cond_val2_level ecx_tran_stage_data.cond_val2_level%TYPE,
2088 cond_val2_name ecx_tran_stage_data.cond_val2_name%TYPE,
2089 cond_val2_pos ecx_tran_stage_data.cond_val2_pos%TYPE,
2090 cond_val2_direction ecx_tran_stage_data.cond_val2_direction%TYPE,
2091 cond_val2_constant ecx_tran_stage_data.cond_val2_constant%TYPE,
2092 operand1_level ecx_tran_stage_data.operand1_level%TYPE,
2093 operand1_name ecx_tran_stage_data.operand1_name%TYPE,
2094 operand1_pos ecx_tran_stage_data.operand1_pos%TYPE,
2095 operand1_direction ecx_tran_stage_data.operand1_direction%TYPE,
2096 operand1_constant ecx_tran_stage_data.operand1_constant%TYPE,
2097 operand1_len ecx_tran_stage_data.operand1_len%TYPE,
2098 operand1_start_pos ecx_tran_stage_data.operand1_start_pos%TYPE,
2099 operand2_level ecx_tran_stage_data.operand2_level%TYPE,
2100 operand2_name ecx_tran_stage_data.operand2_name%TYPE,
2101 operand2_pos ecx_tran_stage_data.operand2_pos%TYPE,
2102 operand2_direction ecx_tran_stage_data.operand2_direction%TYPE,
2103 operand2_constant ecx_tran_stage_data.operand2_constant%TYPE,
2104 operand3_level ecx_tran_stage_data.operand3_level%TYPE,
2105 operand3_name ecx_tran_stage_data.operand3_name%TYPE,
2106 operand3_pos ecx_tran_stage_data.operand3_pos%TYPE,
2107 operand3_direction ecx_tran_stage_data.operand3_direction%TYPE,
2108 operand3_constant ecx_tran_stage_data.operand3_constant%TYPE,
2109 operand4_level ecx_tran_stage_data.operand4_level%TYPE,
2110 operand4_name ecx_tran_stage_data.operand4_name%TYPE,
2111 operand4_pos ecx_tran_stage_data.operand4_pos%TYPE,
2112 operand4_direction ecx_tran_stage_data.operand4_direction%TYPE,
2113 operand4_constant ecx_tran_stage_data.operand4_constant%TYPE,
2114 operand5_level ecx_tran_stage_data.operand5_level%TYPE,
2115 operand5_name ecx_tran_stage_data.operand5_name%TYPE,
2116 operand5_pos ecx_tran_stage_data.operand5_pos%TYPE,
2117 operand5_direction ecx_tran_stage_data.operand5_direction%TYPE,
2118 operand5_constant ecx_tran_stage_data.operand5_constant%TYPE,
2119 operand6_level ecx_tran_stage_data.operand6_level%TYPE,
2120 operand6_name ecx_tran_stage_data.operand6_name%TYPE,
2121 operand6_pos ecx_tran_stage_data.operand6_pos%TYPE,
2122 operand6_direction ecx_tran_stage_data.operand6_direction%TYPE,
2123 operand6_constant ecx_tran_stage_data.operand6_constant%TYPE);
2124
2125 TYPE stage_data_table is TABLE of stage_data_record index by BINARY_INTEGER;
2126 i_stage_data stage_data_table;
2127 */
2128 TYPE t_transtage_id is TABLE of ecx_tran_stage_data.transtage_id%TYPE;
2129 TYPE t_object_level is TABLE of ecx_object_levels.object_level%TYPE;
2130 TYPE t_objectlevel_id is TABLE of ecx_tran_stage_data.objectlevel_id%TYPE;
2131 TYPE t_stage is TABLE of ecx_tran_stage_data.stage%TYPE;
2132 TYPE t_object_direction is TABLE of ecx_tran_stage_data.object_direction%TYPE;
2133 TYPE t_seq_number is TABLE of ecx_tran_stage_data.seq_number%TYPE;
2134 TYPE t_action_type is TABLE of ecx_tran_stage_data.action_type%TYPE;
2135 TYPE t_variable_level is TABLE of ecx_tran_stage_data.variable_level%TYPE;
2136 TYPE t_variable_name is TABLE of ecx_tran_stage_data.variable_name%TYPE;
2137 TYPE t_variable_direction is TABLE of ecx_tran_stage_data.variable_direction%TYPE;
2138 TYPE t_variable_value is TABLE of ecx_tran_stage_data.variable_value%TYPE;
2139 TYPE t_default_value is TABLE of ecx_tran_stage_data.default_value%TYPE;
2140 TYPE t_sequence_name is TABLE of ecx_tran_stage_data.sequence_name%TYPE;
2141 TYPE t_custom_procedure_name is TABLE of ecx_tran_stage_data.custom_procedure_name%TYPE;
2142 TYPE t_data_type is TABLE of ecx_tran_stage_data.data_type%TYPE;
2143 TYPE t_function_name is TABLE of ecx_tran_stage_data.function_name%TYPE;
2144 TYPE t_where_clause is TABLE of ecx_tran_stage_data.where_clause%TYPE;
2145 TYPE t_variable_pos is TABLE of ecx_tran_stage_data.variable_pos%TYPE;
2146 TYPE t_cond_logical_operator is TABLE of ecx_tran_stage_data.cond_logical_operator%TYPE;
2147 TYPE t_cond_operator1 is TABLE of ecx_tran_stage_data.cond_operator1%TYPE;
2148 TYPE t_cond_var1_level is TABLE of ecx_tran_stage_data.cond_var1_level%TYPE;
2149 TYPE t_cond_var1_name is TABLE of ecx_tran_stage_data.cond_var1_name%TYPE;
2150 TYPE t_cond_var1_pos is TABLE of ecx_tran_stage_data.cond_var1_pos%TYPE;
2151 TYPE t_cond_var1_direction is TABLE of ecx_tran_stage_data.cond_var1_direction%TYPE;
2152 TYPE t_cond_var1_constant is TABLE of ecx_tran_stage_data.cond_var1_constant%TYPE;
2153 TYPE t_cond_val1_level is TABLE of ecx_tran_stage_data.cond_val1_level%TYPE;
2154 TYPE t_cond_val1_name is TABLE of ecx_tran_stage_data.cond_val1_name%TYPE;
2155 TYPE t_cond_val1_pos is TABLE of ecx_tran_stage_data.cond_val1_pos%TYPE;
2156 TYPE t_cond_val1_direction is TABLE of ecx_tran_stage_data.cond_val1_direction%TYPE;
2157 TYPE t_cond_val1_constant is TABLE of ecx_tran_stage_data.cond_val1_constant%TYPE;
2158 TYPE t_cond_operator2 is TABLE of ecx_tran_stage_data.cond_operator2%TYPE;
2159 TYPE t_cond_var2_level is TABLE of ecx_tran_stage_data.cond_var2_level%TYPE;
2160 TYPE t_cond_var2_name is TABLE of ecx_tran_stage_data.cond_var2_name%TYPE;
2161 TYPE t_cond_var2_pos is TABLE of ecx_tran_stage_data.cond_var2_pos%TYPE;
2162 TYPE t_cond_var2_direction is TABLE of ecx_tran_stage_data.cond_var2_direction%TYPE;
2163 TYPE t_cond_var2_constant is TABLE of ecx_tran_stage_data.cond_var2_constant%TYPE;
2164 TYPE t_cond_val2_level is TABLE of ecx_tran_stage_data.cond_val2_level%TYPE;
2165 TYPE t_cond_val2_name is TABLE of ecx_tran_stage_data.cond_val2_name%TYPE;
2166 TYPE t_cond_val2_pos is TABLE of ecx_tran_stage_data.cond_val2_pos%TYPE;
2167 TYPE t_cond_val2_direction is TABLE of ecx_tran_stage_data.cond_val2_direction%TYPE;
2168 TYPE t_cond_val2_constant is TABLE of ecx_tran_stage_data.cond_val2_constant%TYPE;
2169 TYPE t_operand1_level is TABLE of ecx_tran_stage_data.operand1_level%TYPE;
2170 TYPE t_operand1_name is TABLE of ecx_tran_stage_data.operand1_name%TYPE;
2171 TYPE t_operand1_pos is TABLE of ecx_tran_stage_data.operand1_pos%TYPE;
2172 TYPE t_operand1_direction is TABLE of ecx_tran_stage_data.operand1_direction%TYPE;
2173 TYPE t_operand1_constant is TABLE of ecx_tran_stage_data.operand1_constant%TYPE;
2174 TYPE t_operand1_len is TABLE of ecx_tran_stage_data.operand1_len%TYPE;
2175 TYPE t_operand1_start_pos is TABLE of ecx_tran_stage_data.operand1_start_pos%TYPE;
2176 TYPE t_operand2_level is TABLE of ecx_tran_stage_data.operand2_level%TYPE;
2177 TYPE t_operand2_name is TABLE of ecx_tran_stage_data.operand2_name%TYPE;
2178 TYPE t_operand2_pos is TABLE of ecx_tran_stage_data.operand2_pos%TYPE;
2179 TYPE t_operand2_direction is TABLE of ecx_tran_stage_data.operand2_direction%TYPE;
2180 TYPE t_operand2_constant is TABLE of ecx_tran_stage_data.operand2_constant%TYPE;
2181 TYPE t_operand3_level is TABLE of ecx_tran_stage_data.operand3_level%TYPE;
2182 TYPE t_operand3_name is TABLE of ecx_tran_stage_data.operand3_name%TYPE;
2183 TYPE t_operand3_pos is TABLE of ecx_tran_stage_data.operand3_pos%TYPE;
2184 TYPE t_operand3_direction is TABLE of ecx_tran_stage_data.operand3_direction%TYPE;
2185 TYPE t_operand3_constant is TABLE of ecx_tran_stage_data.operand3_constant%TYPE;
2186 TYPE t_operand4_level is TABLE of ecx_tran_stage_data.operand4_level%TYPE;
2187 TYPE t_operand4_name is TABLE of ecx_tran_stage_data.operand4_name%TYPE;
2188 TYPE t_operand4_pos is TABLE of ecx_tran_stage_data.operand4_pos%TYPE;
2189 TYPE t_operand4_direction is TABLE of ecx_tran_stage_data.operand4_direction%TYPE;
2190 TYPE t_operand4_constant is TABLE of ecx_tran_stage_data.operand4_constant%TYPE;
2191 TYPE t_operand5_level is TABLE of ecx_tran_stage_data.operand5_level%TYPE;
2192 TYPE t_operand5_name is TABLE of ecx_tran_stage_data.operand5_name%TYPE;
2193 TYPE t_operand5_pos is TABLE of ecx_tran_stage_data.operand5_pos%TYPE;
2194 TYPE t_operand5_direction is TABLE of ecx_tran_stage_data.operand5_direction%TYPE;
2195 TYPE t_operand5_constant is TABLE of ecx_tran_stage_data.operand5_constant%TYPE;
2196 TYPE t_operand6_level is TABLE of ecx_tran_stage_data.operand6_level%TYPE;
2197 TYPE t_operand6_name is TABLE of ecx_tran_stage_data.operand6_name%TYPE;
2198 TYPE t_operand6_pos is TABLE of ecx_tran_stage_data.operand6_pos%TYPE;
2199 TYPE t_operand6_direction is TABLE of ecx_tran_stage_data.operand6_direction%TYPE;
2200 TYPE t_operand6_constant is TABLE of ecx_tran_stage_data.operand6_constant%TYPE;
2201
2202
2203 -- temp_stage_rec stage_data_record;
2204 temp_util_rec ecx_utils.stage_rec;
2205
2206 v_transtage_id t_transtage_id;
2207 v_object_level t_object_level;
2208 v_objectlevel_id t_objectlevel_id;
2209 v_stage t_stage;
2210 v_object_direction t_object_direction;
2211 v_seq_number t_seq_number;
2212 v_action_type t_action_type;
2213 v_variable_level t_variable_level;
2214 v_variable_name t_variable_name;
2215 v_variable_direction t_variable_direction;
2216 v_variable_value t_variable_value;
2217 v_default_value t_default_value;
2218 v_sequence_name t_sequence_name;
2219 v_custom_procedure_name t_custom_procedure_name;
2220 v_data_type t_data_type;
2221 v_function_name t_function_name;
2222 v_where_clause t_where_clause;
2223 v_variable_pos t_variable_pos;
2224 v_cond_logical_operator t_cond_logical_operator;
2225 v_cond_operator1 t_cond_operator1;
2226 v_cond_var1_level t_cond_var1_level;
2227 v_cond_var1_name t_cond_var1_name;
2228 v_cond_var1_pos t_cond_var1_pos;
2229 v_cond_var1_direction t_cond_var1_direction;
2230 v_cond_var1_constant t_cond_var1_constant;
2231 v_cond_val1_level t_cond_val1_level;
2232 v_cond_val1_name t_cond_val1_name;
2233 v_cond_val1_pos t_cond_val1_pos;
2234 v_cond_val1_direction t_cond_val1_direction;
2235 v_cond_val1_constant t_cond_val1_constant;
2236 v_cond_operator2 t_cond_operator2;
2237 v_cond_var2_level t_cond_var2_level;
2238 v_cond_var2_name t_cond_var2_name;
2239 v_cond_var2_pos t_cond_var2_pos;
2240 v_cond_var2_direction t_cond_var2_direction;
2241 v_cond_var2_constant t_cond_var2_constant;
2242 v_cond_val2_level t_cond_val2_level;
2243 v_cond_val2_name t_cond_val2_name;
2244 v_cond_val2_pos t_cond_val2_pos;
2245 v_cond_val2_direction t_cond_val2_direction;
2246 v_cond_val2_constant t_cond_val2_constant;
2247 v_operand1_level t_operand1_level;
2248 v_operand1_name t_operand1_name;
2249 v_operand1_pos t_operand1_pos;
2250 v_operand1_direction t_operand1_direction;
2251 v_operand1_constant t_operand1_constant;
2252 v_operand1_len t_operand1_len;
2253 v_operand1_start_pos t_operand1_start_pos;
2254 v_operand2_level t_operand2_level;
2255 v_operand2_name t_operand2_name;
2256 v_operand2_pos t_operand2_pos;
2257 v_operand2_direction t_operand2_direction;
2258 v_operand2_constant t_operand2_constant;
2259 v_operand3_level t_operand3_level;
2260 v_operand3_name t_operand3_name;
2261 v_operand3_pos t_operand3_pos;
2262 v_operand3_direction t_operand3_direction;
2263 v_operand3_constant t_operand3_constant;
2264 v_operand4_level t_operand4_level;
2265 v_operand4_name t_operand4_name;
2266 v_operand4_pos t_operand4_pos;
2267 v_operand4_direction t_operand4_direction;
2268 v_operand4_constant t_operand4_constant;
2269 v_operand5_level t_operand5_level;
2270 v_operand5_name t_operand5_name;
2271 v_operand5_pos t_operand5_pos;
2272 v_operand5_direction t_operand5_direction;
2273 v_operand5_constant t_operand5_constant;
2274 v_operand6_level t_operand6_level;
2275 v_operand6_name t_operand6_name;
2276 v_operand6_pos t_operand6_pos;
2277 v_operand6_direction t_operand6_direction;
2278 v_operand6_constant t_operand6_constant;
2279
2280
2281
2282 /* cursor stage_data (
2283 p_map_id IN pls_integer) IS
2284 select transtage_id,
2285 object_level ,
2286 a.objectlevel_id,
2287 stage,
2288 nvl(object_direction,'S') object_direction,
2289 seq_number,
2290 action_type,
2291 variable_level,
2292 variable_name,
2293 nvl(variable_direction,'S') variable_direction,
2294 variable_value,
2295 default_value,
2296 upper(sequence_name) sequence_name,
2297 upper(custom_procedure_name) custom_procedure_name,
2298 data_type,
2299 upper(function_name) function_name,
2300 where_clause,
2301 variable_pos,
2302 upper(cond_logical_operator) cond_logical_operator,
2303 upper(cond_operator1) cond_operator1,
2304 cond_var1_level,
2305 cond_var1_name,
2306 cond_var1_pos,
2307 upper(cond_var1_direction) cond_var1_direction,
2308 cond_var1_constant,
2309 cond_val1_level,
2310 cond_val1_name,
2311 cond_val1_pos,
2312 upper(cond_val1_direction) cond_val1_direction,
2313 cond_val1_constant,
2314 upper(cond_operator2) cond_operator2,
2315 cond_var2_level,
2316 cond_var2_name,
2317 cond_var2_pos,
2318 upper(cond_var2_direction) cond_var2_direction,
2319 cond_var2_constant,
2320 cond_val2_level,
2321 cond_val2_name,
2322 cond_val2_pos,
2323 upper(cond_val2_direction) cond_val2_direction,
2324 cond_val2_constant,
2325 operand1_level,
2326 operand1_name,
2327 operand1_pos,
2328 operand1_direction,
2329 operand1_constant,
2330 operand1_len,
2331 operand1_start_pos,
2332 operand2_level,
2333 operand2_name,
2334 operand2_pos,
2335 operand2_direction,
2336 operand2_constant,
2337 operand3_level,
2338 operand3_name,
2339 operand3_pos,
2340 operand3_direction,
2341 operand3_constant,
2342 operand4_level,
2343 operand4_name,
2344 operand4_pos,
2345 operand4_direction,
2346 operand4_constant,
2347 operand5_level,
2348 operand5_name,
2349 operand5_pos,
2350 operand5_direction,
2351 operand5_constant,
2352 operand6_level,
2353 operand6_name,
2354 operand6_pos,
2355 operand6_direction,
2356 operand6_constant
2357 from ecx_tran_stage_data a,
2358 ecx_object_levels b
2359 where a.map_id = p_map_id
2360 and a.map_id = b.map_id
2361 and a.objectlevel_id = b.objectlevel_id
2362 and action_type <> 10
2363 order by stage,object_level,action_pos,seq_number;
2364 */
2365
2366 i_counter pls_integer := 0;
2367 BEGIN
2368 ecx_utils.g_delete_doctype := false;
2369 if (l_procedureEnabled) then
2370 ecx_debug.push(i_method_name);
2371 end if;
2372 ecx_utils.g_stage_data.DELETE;
2373
2374 select
2375 transtage_id,
2376 object_level ,
2377 a.objectlevel_id,
2378 stage,
2379 nvl(object_direction,'S') object_direction,
2380 seq_number,
2381 action_type,
2382 variable_level,
2383 variable_name,
2384 nvl(variable_direction,'S') variable_direction,
2385 variable_value,
2386 default_value,
2387 upper(sequence_name) sequence_name,
2388 upper(custom_procedure_name) custom_procedure_name,
2389 data_type,
2390 upper(function_name) function_name,
2391 where_clause,
2392 variable_pos,
2393 upper(cond_logical_operator) cond_logical_operator,
2394 upper(cond_operator1) cond_operator1,
2395 cond_var1_level,
2396 cond_var1_name,
2397 cond_var1_pos,
2398 upper(cond_var1_direction) cond_var1_direction,
2399 cond_var1_constant,
2400 cond_val1_level,
2401 cond_val1_name,
2402 cond_val1_pos,
2403 upper(cond_val1_direction) cond_val1_direction,
2404 cond_val1_constant,
2405 upper(cond_operator2) cond_operator2,
2406 cond_var2_level,
2407 cond_var2_name,
2408 cond_var2_pos,
2409 upper(cond_var2_direction) cond_var2_direction,
2410 cond_var2_constant,
2411 cond_val2_level,
2412 cond_val2_name,
2413 cond_val2_pos,
2414 upper(cond_val2_direction) cond_val2_direction,
2415 cond_val2_constant,
2416 operand1_level,
2417 operand1_name,
2418 operand1_pos,
2419 operand1_direction,
2420 operand1_constant,
2421 operand1_len,
2422 operand1_start_pos,
2423 operand2_level,
2424 operand2_name,
2425 operand2_pos,
2426 operand2_direction,
2427 operand2_constant,
2428 operand3_level,
2429 operand3_name,
2430 operand3_pos,
2431 operand3_direction,
2432 operand3_constant,
2433 operand4_level,
2434 operand4_name,
2435 operand4_pos,
2436 operand4_direction,
2437 operand4_constant,
2438 operand5_level,
2439 operand5_name,
2440 operand5_pos,
2441 operand5_direction,
2442 operand5_constant,
2443 operand6_level,
2444 operand6_name,
2445 operand6_pos,
2446 operand6_direction,
2447 operand6_constant
2448 -- bulk collect into ecx_utils.g_stage_data
2449 bulk collect into
2450 -- i_stage_data
2451 v_transtage_id,
2452 v_object_level,
2453 v_objectlevel_id,
2454 v_stage,
2455 v_object_direction,
2456 v_seq_number,
2457 v_action_type,
2458 v_variable_level,
2459 v_variable_name,
2460 v_variable_direction,
2461 v_variable_value,
2462 v_default_value,
2463 v_sequence_name,
2464 v_custom_procedure_name,
2465 v_data_type,
2466 v_function_name,
2467 v_where_clause,
2468 v_variable_pos,
2469 v_cond_logical_operator,
2470 v_cond_operator1,
2471 v_cond_var1_level,
2472 v_cond_var1_name,
2473 v_cond_var1_pos,
2474 v_cond_var1_direction,
2475 v_cond_var1_constant,
2476 v_cond_val1_level,
2477 v_cond_val1_name,
2478 v_cond_val1_pos,
2479 v_cond_val1_direction,
2480 v_cond_val1_constant,
2481 v_cond_operator2,
2482 v_cond_var2_level,
2483 v_cond_var2_name,
2484 v_cond_var2_pos,
2485 v_cond_var2_direction,
2486 v_cond_var2_constant,
2487 v_cond_val2_level,
2488 v_cond_val2_name,
2489 v_cond_val2_pos,
2490 v_cond_val2_direction,
2491 v_cond_val2_constant,
2492 v_operand1_level,
2493 v_operand1_name,
2494 v_operand1_pos,
2495 v_operand1_direction,
2496 v_operand1_constant,
2497 v_operand1_len,
2498 v_operand1_start_pos,
2499 v_operand2_level,
2500 v_operand2_name,
2501 v_operand2_pos,
2502 v_operand2_direction,
2503 v_operand2_constant,
2504 v_operand3_level,
2505 v_operand3_name,
2506 v_operand3_pos,
2507 v_operand3_direction,
2508 v_operand3_constant,
2509 v_operand4_level,
2510 v_operand4_name,
2511 v_operand4_pos,
2512 v_operand4_direction,
2513 v_operand4_constant,
2514 v_operand5_level,
2515 v_operand5_name,
2516 v_operand5_pos,
2517 v_operand5_direction,
2518 v_operand5_constant,
2519 v_operand6_level,
2520 v_operand6_name,
2521 v_operand6_pos,
2522 v_operand6_direction,
2523 v_operand6_constant
2524
2525
2526 from ecx_tran_stage_data a,
2527 ecx_object_levels b
2528 where a.map_id = i_map_id
2529 and a.map_id = b.map_id
2530 and a.objectlevel_id = b.objectlevel_id
2531 and action_type <> 10
2532 order by stage,object_level,action_pos,seq_number;
2533
2534
2535 if (v_transtage_id.COUNT > 0) then
2536 for i in v_transtage_id.FIRST..v_transtage_id.LAST
2537 loop
2538 -- temp_stage_rec := i_stage_data(i);
2539 temp_util_rec.transtage_id := v_transtage_id(i);
2540 temp_util_rec.level := v_object_level(i);
2541 temp_util_rec.stage := v_stage(i);
2542 temp_util_rec.object_direction := v_object_direction(i);
2543 temp_util_rec.seq_number := v_seq_number(i);
2544 temp_util_rec.action_type := v_action_type(i);
2545 temp_util_rec.variable_level := v_variable_level(i);
2546 temp_util_rec.variable_name := v_variable_name(i);
2547 temp_util_rec.variable_pos := v_variable_pos(i);
2548 temp_util_rec.variable_direction := v_variable_direction(i);
2549 temp_util_rec.variable_value := v_variable_value(i);
2550 temp_util_rec.default_value := v_default_value(i);
2551 temp_util_rec.sequence_name := v_sequence_name(i);
2552 temp_util_rec.custom_procedure_name := v_custom_procedure_name(i);
2553 temp_util_rec.data_type := v_data_type(i);
2554 temp_util_rec.function_name := v_function_name(i);
2555 temp_util_rec.clause := v_where_clause(i);
2556 temp_util_rec.cond_logical_operator := v_cond_logical_operator(i);
2557 temp_util_rec.cond_operator1 := v_cond_operator1(i);
2558 temp_util_rec.cond_var1_level := v_cond_var1_level(i);
2559 temp_util_rec.cond_var1_name := v_cond_var1_name(i);
2560 temp_util_rec.cond_var1_pos := v_cond_var1_pos(i);
2561 temp_util_rec.cond_var1_direction := v_cond_var1_direction(i);
2562 temp_util_rec.cond_var1_constant := v_cond_var1_constant(i);
2563 temp_util_rec.cond_val1_level := v_cond_val1_level(i);
2564 temp_util_rec.cond_val1_name := v_cond_val1_name(i);
2565 temp_util_rec.cond_val1_pos := v_cond_val1_pos(i);
2566 temp_util_rec.cond_val1_direction := v_cond_val1_direction(i);
2567 temp_util_rec.cond_val1_constant := v_cond_val1_constant(i);
2568 temp_util_rec.cond_operator2 := v_cond_operator2(i);
2569 temp_util_rec.cond_var2_level := v_cond_var2_level(i);
2570 temp_util_rec.cond_var2_name := v_cond_var2_name(i);
2571 temp_util_rec.cond_var2_pos := v_cond_var2_pos(i);
2572 temp_util_rec.cond_var2_direction := v_cond_var2_direction(i);
2573 temp_util_rec.cond_var2_constant := v_cond_var2_constant(i);
2574 temp_util_rec.cond_val2_level := v_cond_val2_level(i);
2575 temp_util_rec.cond_val2_name := v_cond_val2_name(i);
2576 temp_util_rec.cond_val2_pos := v_cond_val2_pos(i);
2577 temp_util_rec.cond_val2_direction := v_cond_val2_direction(i);
2578 temp_util_rec.cond_val2_constant := v_cond_val2_constant(i);
2579 temp_util_rec.operand1_level := v_operand1_level(i);
2580 temp_util_rec.operand1_name := v_operand1_name(i);
2581 temp_util_rec.operand1_pos := v_operand1_pos(i);
2582 temp_util_rec.operand1_direction := v_operand1_direction(i);
2583 temp_util_rec.operand1_constant := v_operand1_constant(i);
2584 temp_util_rec.operand1_len := v_operand1_len(i);
2585 temp_util_rec.operand1_start_pos := v_operand1_start_pos(i);
2586 temp_util_rec.operand2_level := v_operand2_level(i);
2587 temp_util_rec.operand2_name := v_operand2_name(i);
2588 temp_util_rec.operand2_pos := v_operand2_pos(i);
2589 temp_util_rec.operand2_direction := v_operand2_direction(i);
2590 temp_util_rec.operand2_constant := v_operand2_constant(i);
2591 temp_util_rec.operand3_level := v_operand3_level(i);
2592 temp_util_rec.operand3_name := v_operand3_name(i);
2593 temp_util_rec.operand3_pos := v_operand3_pos(i);
2594 temp_util_rec.operand3_direction := v_operand3_direction(i);
2595 temp_util_rec.operand3_constant := v_operand3_constant(i);
2596 temp_util_rec.operand4_level := v_operand4_level(i);
2597 temp_util_rec.operand4_name := v_operand4_name(i);
2598 temp_util_rec.operand4_pos := v_operand4_pos(i);
2599 temp_util_rec.operand4_direction := v_operand4_direction(i);
2600 temp_util_rec.operand4_constant := v_operand4_constant(i);
2601 temp_util_rec.operand5_level := v_operand5_level(i);
2602 temp_util_rec.operand5_name := v_operand5_name(i);
2603 temp_util_rec.operand5_pos := v_operand5_pos(i);
2604 temp_util_rec.operand5_direction := v_operand5_direction(i);
2605 temp_util_rec.operand5_constant := v_operand5_constant(i);
2606 temp_util_rec.operand6_level := v_operand6_level(i);
2607 temp_util_rec.operand6_name := v_operand6_name(i);
2608 temp_util_rec.operand6_pos := v_operand6_pos(i);
2609 temp_util_rec.operand6_direction := v_operand6_direction(i);
2610 temp_util_rec.operand6_constant := v_operand6_constant(i);
2611
2612 IF (instr(temp_util_rec.custom_procedure_name, DELETE_DOCTYPE_PROC_NAME) > 0) THEN
2613 g_delete_doctype := true;
2614 if(l_statementEnabled) then
2615 ecx_debug.log(l_statement, 'g_delete_doctype = true',i_method_name);
2616 end if;
2617 END IF;
2618
2619
2620 if(l_statementEnabled) then
2621 ecx_debug.log(l_statement,temp_util_rec.transtage_id||'|'||
2622 temp_util_rec.level||'|'||
2623 temp_util_rec.stage||'|'||
2624 temp_util_rec.object_direction||'|'||
2625 temp_util_rec.seq_number||'|'||
2626 temp_util_rec.action_type||'|'||
2627 temp_util_rec.variable_level||'|'||
2628 temp_util_rec.variable_name||'|'||
2629 temp_util_rec.variable_pos||'|'||
2630 temp_util_rec.variable_direction||'|'||
2631 temp_util_rec.variable_value||'|'||
2632 temp_util_rec.default_value||'|'||
2633 temp_util_rec.custom_procedure_name||'|'||
2634 temp_util_rec.data_type||'|'||
2635 temp_util_rec.function_name||'|'||
2636 temp_util_rec.clause||'|'||
2637 temp_util_rec.cond_logical_operator||'|'||
2638 temp_util_rec.cond_operator1||'|'||
2639 temp_util_rec.cond_var1_level||'|'||
2640 temp_util_rec.cond_var1_name||'|'||
2641 temp_util_rec.cond_var1_pos||'|'||
2642 temp_util_rec.cond_var1_direction||'|'||
2643 temp_util_rec.cond_var1_constant||'|'||
2644 temp_util_rec.cond_val1_level||'|'||
2645 temp_util_rec.cond_val1_name||'|'||
2646 temp_util_rec.cond_val1_pos||'|'||
2647 temp_util_rec.cond_val1_direction||'|'||
2648 temp_util_rec.cond_val1_constant||'|'||
2649 temp_util_rec.cond_operator2||'|'||
2650 temp_util_rec.cond_var2_level||'|'||
2651 temp_util_rec.cond_var2_name||'|'||
2652 temp_util_rec.cond_var2_pos||'|'||
2653 temp_util_rec.cond_var2_direction||'|'||
2654 temp_util_rec.cond_var2_constant||'|'||
2655 temp_util_rec.cond_val2_level||'|'||
2656 temp_util_rec.cond_val2_name||'|'||
2657 temp_util_rec.cond_val2_pos||'|'||
2658 temp_util_rec.cond_val2_direction||'|'||
2659 temp_util_rec.cond_val2_constant||'|'||
2660 temp_util_rec.operand1_level||'|'||
2661 temp_util_rec.operand1_name||'|'||
2662 temp_util_rec.operand1_pos||'|'||
2663 temp_util_rec.operand1_direction||'|'||
2664 temp_util_rec.operand1_constant||'|'||
2665 temp_util_rec.operand1_start_pos||'|'||
2666 temp_util_rec.operand1_len||'|'||
2667 temp_util_rec.operand2_level||'|'||
2668 temp_util_rec.operand2_name||'|'||
2669 temp_util_rec.operand2_pos||'|'||
2670 temp_util_rec.operand2_direction||'|'||
2671 temp_util_rec.operand2_constant||'|'||
2672 temp_util_rec.operand3_level||'|'||
2673 temp_util_rec.operand3_name||'|'||
2674 temp_util_rec.operand3_pos||'|'||
2675 temp_util_rec.operand3_direction||'|'||
2676 temp_util_rec.operand3_constant||'|'||
2677 temp_util_rec.operand4_level||'|'||
2678 temp_util_rec.operand4_name||'|'||
2679 temp_util_rec.operand4_pos||'|'||
2680 temp_util_rec.operand4_direction||'|'||
2681 temp_util_rec.operand4_constant||'|'||
2682 temp_util_rec.operand5_level||'|'||
2683 temp_util_rec.operand5_name||'|'||
2684 temp_util_rec.operand5_pos||'|'||
2685 temp_util_rec.operand5_direction||'|'||
2686 temp_util_rec.operand5_constant||'|'||
2687 temp_util_rec.operand6_level||'|'||
2688 temp_util_rec.operand6_name||'|'||
2689 temp_util_rec.operand6_pos||'|'||
2690 temp_util_rec.operand6_direction||'|'||
2691 temp_util_rec.operand6_constant, i_method_name
2692 );
2693 end if;
2694 ecx_utils.g_stage_data(i_counter) := temp_util_rec;
2695
2696 i_counter := i_counter + 1;
2697 end loop;
2698 end if;
2699
2700
2701 /* for get_stage_data in stage_data (
2702 p_map_id => i_map_id) loop
2703
2704 ecx_utils.g_stage_data(i_counter).transtage_id := get_stage_data.transtage_id;
2705 ecx_utils.g_stage_data(i_counter).level := get_stage_data.object_level;
2706 ecx_utils.g_stage_data(i_counter).stage := get_stage_data.stage;
2707 ecx_utils.g_stage_data(i_counter).object_direction := get_stage_data.object_direction;
2708 ecx_utils.g_stage_data(i_counter).seq_number := get_stage_data.seq_number;
2709 ecx_utils.g_stage_data(i_counter).action_type := get_stage_data.action_type;
2710 ecx_utils.g_stage_data(i_counter).variable_level := get_stage_data.variable_level;
2711 ecx_utils.g_stage_data(i_counter).variable_name := get_stage_data.variable_name;
2712 ecx_utils.g_stage_data(i_counter).variable_pos := get_stage_data.variable_pos;
2713 ecx_utils.g_stage_data(i_counter).variable_direction := get_stage_data.variable_direction;
2714 ecx_utils.g_stage_data(i_counter).variable_value := get_stage_data.variable_value;
2715 ecx_utils.g_stage_data(i_counter).default_value := get_stage_data.default_value;
2716 ecx_utils.g_stage_data(i_counter).sequence_name := get_stage_data.sequence_name;
2717 ecx_utils.g_stage_data(i_counter).custom_procedure_name := get_stage_data.custom_procedure_name;
2718 ecx_utils.g_stage_data(i_counter).data_type := get_stage_data.data_type;
2719 ecx_utils.g_stage_data(i_counter).function_name := get_stage_data.function_name;
2720 ecx_utils.g_stage_data(i_counter).clause := get_stage_data.where_clause;
2721 ecx_utils.g_stage_data(i_counter).cond_logical_operator := get_stage_data.cond_logical_operator;
2722 ecx_utils.g_stage_data(i_counter).cond_operator1 := get_stage_data.cond_operator1;
2723 ecx_utils.g_stage_data(i_counter).cond_var1_level := get_stage_data.cond_var1_level;
2724 ecx_utils.g_stage_data(i_counter).cond_var1_name := get_stage_data.cond_var1_name;
2725 ecx_utils.g_stage_data(i_counter).cond_var1_pos := get_stage_data.cond_var1_pos;
2726 ecx_utils.g_stage_data(i_counter).cond_var1_direction := get_stage_data.cond_var1_direction;
2727 ecx_utils.g_stage_data(i_counter).cond_var1_constant := get_stage_data.cond_var1_constant;
2728 ecx_utils.g_stage_data(i_counter).cond_val1_level := get_stage_data.cond_val1_level;
2729 ecx_utils.g_stage_data(i_counter).cond_val1_name := get_stage_data.cond_val1_name;
2730 ecx_utils.g_stage_data(i_counter).cond_val1_pos := get_stage_data.cond_val1_pos;
2731 ecx_utils.g_stage_data(i_counter).cond_val1_direction := get_stage_data.cond_val1_direction;
2732 ecx_utils.g_stage_data(i_counter).cond_val1_constant := get_stage_data.cond_val1_constant;
2733 ecx_utils.g_stage_data(i_counter).cond_operator2 := get_stage_data.cond_operator2;
2734 ecx_utils.g_stage_data(i_counter).cond_var2_level := get_stage_data.cond_var2_level;
2735 ecx_utils.g_stage_data(i_counter).cond_var2_name := get_stage_data.cond_var2_name;
2736 ecx_utils.g_stage_data(i_counter).cond_var2_pos := get_stage_data.cond_var2_pos;
2737 ecx_utils.g_stage_data(i_counter).cond_var2_direction := get_stage_data.cond_var2_direction;
2738 ecx_utils.g_stage_data(i_counter).cond_var2_constant := get_stage_data.cond_var2_constant;
2739 ecx_utils.g_stage_data(i_counter).cond_val2_level := get_stage_data.cond_val2_level;
2740 ecx_utils.g_stage_data(i_counter).cond_val2_name := get_stage_data.cond_val2_name;
2741 ecx_utils.g_stage_data(i_counter).cond_val2_pos := get_stage_data.cond_val2_pos;
2742 ecx_utils.g_stage_data(i_counter).cond_val2_direction := get_stage_data.cond_val2_direction;
2743 ecx_utils.g_stage_data(i_counter).cond_val2_constant := get_stage_data.cond_val2_constant;
2744 ecx_utils.g_stage_data(i_counter).operand1_level := get_stage_data.operand1_level;
2745 ecx_utils.g_stage_data(i_counter).operand1_name := get_stage_data.operand1_name;
2746 ecx_utils.g_stage_data(i_counter).operand1_pos := get_stage_data.operand1_pos;
2747 ecx_utils.g_stage_data(i_counter).operand1_direction := get_stage_data.operand1_direction;
2748 ecx_utils.g_stage_data(i_counter).operand1_constant := get_stage_data.operand1_constant;
2749 ecx_utils.g_stage_data(i_counter).operand1_len := get_stage_data.operand1_len;
2750 ecx_utils.g_stage_data(i_counter).operand1_start_pos := get_stage_data.operand1_start_pos;
2751 ecx_utils.g_stage_data(i_counter).operand2_level := get_stage_data.operand2_level;
2752 ecx_utils.g_stage_data(i_counter).operand2_name := get_stage_data.operand2_name;
2753 ecx_utils.g_stage_data(i_counter).operand2_pos := get_stage_data.operand2_pos;
2754 ecx_utils.g_stage_data(i_counter).operand2_direction := get_stage_data.operand2_direction;
2755 ecx_utils.g_stage_data(i_counter).operand2_constant := get_stage_data.operand2_constant;
2756 ecx_utils.g_stage_data(i_counter).operand3_level := get_stage_data.operand3_level;
2757 ecx_utils.g_stage_data(i_counter).operand3_name := get_stage_data.operand3_name;
2758 ecx_utils.g_stage_data(i_counter).operand3_pos := get_stage_data.operand3_pos;
2759 ecx_utils.g_stage_data(i_counter).operand3_direction := get_stage_data.operand3_direction;
2760 ecx_utils.g_stage_data(i_counter).operand3_constant := get_stage_data.operand3_constant;
2761 ecx_utils.g_stage_data(i_counter).operand4_level := get_stage_data.operand4_level;
2762 ecx_utils.g_stage_data(i_counter).operand4_name := get_stage_data.operand4_name;
2763 ecx_utils.g_stage_data(i_counter).operand4_pos := get_stage_data.operand4_pos;
2764 ecx_utils.g_stage_data(i_counter).operand4_direction := get_stage_data.operand4_direction;
2765 ecx_utils.g_stage_data(i_counter).operand4_constant := get_stage_data.operand4_constant;
2766 ecx_utils.g_stage_data(i_counter).operand5_level := get_stage_data.operand5_level;
2767 ecx_utils.g_stage_data(i_counter).operand5_name := get_stage_data.operand5_name;
2768 ecx_utils.g_stage_data(i_counter).operand5_pos := get_stage_data.operand5_pos;
2769 ecx_utils.g_stage_data(i_counter).operand5_direction := get_stage_data.operand5_direction;
2770 ecx_utils.g_stage_data(i_counter).operand5_constant := get_stage_data.operand5_constant;
2771 ecx_utils.g_stage_data(i_counter).operand6_level := get_stage_data.operand6_level;
2772 ecx_utils.g_stage_data(i_counter).operand6_name := get_stage_data.operand6_name;
2773 ecx_utils.g_stage_data(i_counter).operand6_pos := get_stage_data.operand6_pos;
2774 ecx_utils.g_stage_data(i_counter).operand6_direction := get_stage_data.operand6_direction;
2775 ecx_utils.g_stage_data(i_counter).operand6_constant := get_stage_data.operand6_constant;
2776
2777 if(l_statementEnabled) then
2778 ecx_debug.log(l_statement,
2779 ecx_utils.g_stage_data(i_counter).transtage_id||'|'||
2780 ecx_utils.g_stage_data(i_counter).level||'|'||
2781 ecx_utils.g_stage_data(i_counter).stage||'|'||
2782 ecx_utils.g_stage_data(i_counter).object_direction||'|'||
2783 ecx_utils.g_stage_data(i_counter).seq_number||'|'||
2784 ecx_utils.g_stage_data(i_counter).action_type||'|'||
2785 ecx_utils.g_stage_data(i_counter).variable_level||'|'||
2786 ecx_utils.g_stage_data(i_counter).variable_name||'|'||
2787 ecx_utils.g_stage_data(i_counter).variable_pos||'|'||
2788 ecx_utils.g_stage_data(i_counter).variable_direction||'|'||
2789 ecx_utils.g_stage_data(i_counter).variable_value||'|'||
2790 ecx_utils.g_stage_data(i_counter).default_value||'|'||
2791 ecx_utils.g_stage_data(i_counter).custom_procedure_name||'|'||
2792 ecx_utils.g_stage_data(i_counter).data_type||'|'||
2793 ecx_utils.g_stage_data(i_counter).function_name||'|'||
2794 ecx_utils.g_stage_data(i_counter).clause||'|'||
2795 ecx_utils.g_stage_data(i_counter).cond_logical_operator||'|'||
2796 ecx_utils.g_stage_data(i_counter).cond_operator1||'|'||
2797 ecx_utils.g_stage_data(i_counter).cond_var1_level||'|'||
2798 ecx_utils.g_stage_data(i_counter).cond_var1_name||'|'||
2799 ecx_utils.g_stage_data(i_counter).cond_var1_pos||'|'||
2800 ecx_utils.g_stage_data(i_counter).cond_var1_direction||'|'||
2801 ecx_utils.g_stage_data(i_counter).cond_var1_constant||'|'||
2802 ecx_utils.g_stage_data(i_counter).cond_val1_level||'|'||
2803 ecx_utils.g_stage_data(i_counter).cond_val1_name||'|'||
2804 ecx_utils.g_stage_data(i_counter).cond_val1_pos||'|'||
2805 ecx_utils.g_stage_data(i_counter).cond_val1_direction||'|'||
2806 ecx_utils.g_stage_data(i_counter).cond_val1_constant||'|'||
2807 ecx_utils.g_stage_data(i_counter).cond_operator2||'|'||
2808 ecx_utils.g_stage_data(i_counter).cond_var2_level||'|'||
2809 ecx_utils.g_stage_data(i_counter).cond_var2_name||'|'||
2810 ecx_utils.g_stage_data(i_counter).cond_var2_pos||'|'||
2811 ecx_utils.g_stage_data(i_counter).cond_var2_direction||'|'||
2812 ecx_utils.g_stage_data(i_counter).cond_var2_constant||'|'||
2813 ecx_utils.g_stage_data(i_counter).cond_val2_level||'|'||
2814 ecx_utils.g_stage_data(i_counter).cond_val2_name||'|'||
2815 ecx_utils.g_stage_data(i_counter).cond_val2_pos||'|'||
2816 ecx_utils.g_stage_data(i_counter).cond_val2_direction||'|'||
2817 ecx_utils.g_stage_data(i_counter).cond_val2_constant||'|'||
2818 ecx_utils.g_stage_data(i_counter).operand1_level||'|'||
2819 ecx_utils.g_stage_data(i_counter).operand1_name||'|'||
2820 ecx_utils.g_stage_data(i_counter).operand1_pos||'|'||
2821 ecx_utils.g_stage_data(i_counter).operand1_direction||'|'||
2822 ecx_utils.g_stage_data(i_counter).operand1_constant||'|'||
2823 ecx_utils.g_stage_data(i_counter).operand1_start_pos||'|'||
2824 ecx_utils.g_stage_data(i_counter).operand1_len||'|'||
2825 ecx_utils.g_stage_data(i_counter).operand2_level||'|'||
2826 ecx_utils.g_stage_data(i_counter).operand2_name||'|'||
2827 ecx_utils.g_stage_data(i_counter).operand2_pos||'|'||
2828 ecx_utils.g_stage_data(i_counter).operand2_direction||'|'||
2829 ecx_utils.g_stage_data(i_counter).operand2_constant||'|'||
2830 ecx_utils.g_stage_data(i_counter).operand3_level||'|'||
2831 ecx_utils.g_stage_data(i_counter).operand3_name||'|'||
2832 ecx_utils.g_stage_data(i_counter).operand3_pos||'|'||
2833 ecx_utils.g_stage_data(i_counter).operand3_direction||'|'||
2834 ecx_utils.g_stage_data(i_counter).operand3_constant||'|'||
2835 ecx_utils.g_stage_data(i_counter).operand4_level||'|'||
2836 ecx_utils.g_stage_data(i_counter).operand4_name||'|'||
2837 ecx_utils.g_stage_data(i_counter).operand4_pos||'|'||
2838 ecx_utils.g_stage_data(i_counter).operand4_direction||'|'||
2839 ecx_utils.g_stage_data(i_counter).operand4_constant||'|'||
2840 ecx_utils.g_stage_data(i_counter).operand5_level||'|'||
2841 ecx_utils.g_stage_data(i_counter).operand5_name||'|'||
2842 ecx_utils.g_stage_data(i_counter).operand5_pos||'|'||
2843 ecx_utils.g_stage_data(i_counter).operand5_direction||'|'||
2844 ecx_utils.g_stage_data(i_counter).operand5_constant||'|'||
2845 ecx_utils.g_stage_data(i_counter).operand6_level||'|'||
2846 ecx_utils.g_stage_data(i_counter).operand6_name||'|'||
2847 ecx_utils.g_stage_data(i_counter).operand6_pos||'|'||
2848 ecx_utils.g_stage_data(i_counter).operand6_direction||'|'||
2849 ecx_utils.g_stage_data(i_counter).operand6_constant,
2850 i_method_name
2851 );
2852 end if;
2853 i_counter := i_counter + 1;
2854 end loop;
2855 */
2856 if (l_procedureEnabled) then
2857 ecx_debug.pop(i_method_name);
2858 end if;
2859
2860 EXCEPTION
2861 WHEN ecx_utils.PROGRAM_EXIT then
2862 if (l_procedureEnabled) then
2863 ecx_debug.pop(i_method_name);
2864 end if;
2865 raise;
2866
2867 WHEN OTHERS THEN
2868 if(l_unexpectedEnabled) then
2869 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name, 'PROGRESS_LEVEL',
2870 'ecx_UTILS.GET_TRAN_STAGE_DATA');
2871 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', i_method_name,'ERROR_MESSAGE', SQLERRM);
2872 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.GET_TRAN_STAGE_DATA: ',
2873 i_method_name);
2874 end if;
2875 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.GET_TRAN_STAGE_DATA: ');
2876 if (l_procedureEnabled) then
2877 ecx_debug.pop(i_method_name);
2878 end if;
2879 raise ecx_utils.PROGRAM_EXIT;
2880
2881 END get_tran_stage_data;
2882
2883 procedure getLogDirectory
2884 is
2885 i_method_name varchar2(2000) := 'ecx_utils.getLogDirectory';
2886 i_string varchar2(2000);
2887 begin
2888 --- Check for the Installation Type ( Standalone or Embedded );
2889 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
2890
2891 if ecx_utils.g_install_mode = 'EMBEDDED'
2892 then
2893 i_string := 'begin
2894 fnd_profile.get('||'''ECX_UTL_LOG_DIR'''||',ecx_utils.g_logdir);
2895 end;';
2896 execute immediate i_string ;
2897 else
2898 ecx_utils.g_logdir := wf_core.translate('ECX_UTL_LOG_DIR');
2899 end if;
2900
2901
2902 /* Remove the additional '/' at the end of the profile option if present
2903 or else it will cause error in the ecx_debug.print_log */
2904
2905 If (ecx_utils.g_logdir is not null) then
2906 if (substr(ecx_utils.g_logdir,-1,1) = '/') then
2907 ecx_utils.g_logdir := substr(ecx_utils.g_logdir,1,
2908 length(ecx_utils.g_logdir)-1);
2909 end if;
2910 End If;
2911 if(l_statementEnabled) then
2912 ecx_debug.log(l_statement, 'ecx_utils.g_logdir',ecx_utils.g_logdir,i_method_name);
2913 end if;
2914
2915 exception
2916 when others then
2917 raise ecx_utils.program_exit;
2918 end getLogDirectory;
2919
2920 /*
2921 Gets the file separator from the SystemProperty class
2922 */
2923 Function GetFileSeparator
2924 return varchar2
2925 is language java name 'oracle.apps.ecx.util.SystemProperty.getFileSeparator() returns java.lang.String';
2926
2927
2928 /*
2929 Gets the line separator from the SystemProperty class
2930 */
2931 Function GetLineSeparator
2932 return varchar2
2933 is language java name 'oracle.apps.ecx.util.SystemProperty.getLineSeparator() returns java.lang.String';
2934
2935 Procedure set_error(
2936 p_error_type in pls_integer,
2937 p_error_code in pls_integer,
2938 p_error_msg in varchar2,
2939 p_token1 in varchar2,
2940 p_value1 in varchar2,
2941 p_token2 in varchar2,
2942 p_value2 in varchar2,
2943 p_token3 in varchar2,
2944 p_value3 in varchar2,
2945 p_token4 in varchar2,
2946 p_value4 in varchar2,
2947 p_token5 in varchar2,
2948 p_value5 in varchar2,
2949 p_token6 in varchar2,
2950 p_value6 in varchar2,
2951 p_token7 in varchar2,
2952 p_value7 in varchar2,
2953 p_token8 in varchar2,
2954 p_value8 in varchar2,
2955 p_token9 in varchar2,
2956 p_value9 in varchar2,
2957 p_token10 in varchar2,
2958 p_value10 in varchar2
2959 )
2960 is
2961 i_method_name varchar2(2000) := 'ecx_utils.set_error';
2962 begin
2963
2964 if (l_procedureEnabled) then
2965 ecx_debug.push(i_method_name);
2966 end if;
2967 ecx_debug.setErrorInfo(p_error_code => p_error_code,
2968 p_error_type => p_error_type,
2969 p_errmsg_name=> p_error_msg,
2970 p_token1 => p_token1,
2971 p_value1 => p_value1,
2972 p_token2 => p_token2,
2973 p_value2 => p_value2,
2974 p_token3 => p_token3,
2975 p_value3 => p_value3,
2976 p_token4 => p_token4,
2977 p_value4 => p_value4,
2978 p_token5 => p_token5,
2979 p_value5 => p_value5,
2980 p_token6 => p_token6,
2981 p_value6 => p_value6,
2982 p_token7 => p_token7,
2983 p_value7 => p_value7,
2984 p_token8 => p_token8,
2985 p_value8 => p_value8,
2986 p_token9 => p_token9,
2987 p_value9 => p_value9,
2988 p_token10 => p_token10,
2989 p_value10 => p_value10);
2990
2991 if (l_procedureEnabled) then
2992 ecx_debug.pop(i_method_name);
2993 end if;
2994 exception
2995 when others then
2996 if(l_unexpectedEnabled) then
2997 ecx_debug.log(l_unexpected, 'ECX', 'ECX_PROGRAM_ERROR', i_method_name, 'PROGRESS_LEVEL',
2998 'ECX_UTILS.SET_ERROR',i_method_name);
2999 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM,i_method_name);
3000 ecx_debug.log(l_unexpected, 'ECX', ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.SET_ERROR: ',
3001 i_method_name);
3002 end if;
3003 ecx_debug.setErrorInfo(2, 30, ecx_utils.i_errbuf || SQLERRM || ' - ECX_UTILS.SET_ERROR: ');
3004 if (l_procedureEnabled) then
3005 ecx_debug.pop(i_method_name);
3006 end if;
3007 raise ecx_utils.PROGRAM_EXIT;
3008 end set_error;
3009
3010 procedure convertPartyTypeToCode(
3011 p_party_type IN Varchar2,
3012 x_party_type OUT NOCOPY Varchar2) is
3013 i_method_name varchar2(2000) := 'ecx_utils.convertPartyTypeToCode';
3014 begin
3015 if (p_party_type is not null) then
3016 x_party_type := UPPER(p_party_type);
3017 if (x_party_type = 'BANK' or x_party_type = 'B') then
3018 x_party_type := 'B';
3019 elsif (x_party_type = 'CUSTOMER' or x_party_type = 'C') then
3020 x_party_type := 'C';
3021 elsif (x_party_type = 'INTERNAL' or x_party_type = 'I') then
3022 x_party_type := 'I';
3023 elsif (x_party_type = 'SUPPLIER' or x_party_type = 'S') then
3024 x_party_type := 'S';
3025 elsif (x_party_type = 'EXCHANGE' or x_party_type = 'E') then
3026 x_party_type := 'E';
3027 elsif (x_party_type = 'CARRIER') then
3028 x_party_type := 'CARRIER';
3029 else
3030 x_party_type := p_party_type;
3031 end if;
3032 else
3033 x_party_type := null;
3034 end if;
3035 exception
3036 when others then
3037 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_UTILS.ConvertPartyTypeToCode: ');
3038 if(l_unexpectedEnabled) then
3039 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
3040 end if;
3041 if (l_procedureEnabled) then
3042 ecx_debug.pop(i_method_name);
3043 end if;
3044 raise ecx_utils.PROGRAM_EXIT;
3045 end convertPartyTypeToCode;
3046
3047 /* Return the node path of given attribute, from root till it's parent attribute*/
3048 function getNodePath
3049 (v_map_id IN NUMBER,
3050 v_attribute_id IN NUMBER) return varchar2
3051 IS
3052 i_method_name varchar2(2000) := 'ecx_utils.getNodePath';
3053 l_attribute_name ecx_object_attributes.attribute_name%type;
3054 l_attribute_id ecx_object_attributes.attribute_id%type;
3055 l_node_path varchar2(32767);
3056 l_parent_name ecx_object_attributes.attribute_name%type;
3057 l_parent_id ecx_object_attributes.attribute_id%type;
3058 l_root_node ecx_object_attributes.attribute_name%type;
3059
3060 begin
3061 if (l_procedureEnabled) then
3062 ecx_debug.push(i_method_name);
3063 end if;
3064 SELECT object_level_name INTO l_root_node
3065 FROM ecx_object_levels
3066 WHERE map_id=v_map_id and object_level=0 and object_id=1;
3067
3068 IF (v_attribute_id = 0) THEN
3069 return l_root_node;
3070 END IF;
3071
3072 SELECT attribute_name,attribute_id,parent_attribute_id
3073 INTO l_attribute_name,l_attribute_id,l_parent_id
3074 FROM ecx_object_attributes
3075 WHERE map_id = v_map_id and objectlevel_id in(
3076 select objectlevel_id from ecx_object_levels
3077 WHERE map_id=v_map_id and object_id = 1) AND attribute_id=v_attribute_id;
3078
3079 l_node_path := NULL;
3080
3081 Loop
3082 EXIT WHEN l_parent_id=0;
3083 SELECT attribute_name,attribute_id,parent_attribute_id
3084 INTO l_attribute_name,l_attribute_id,l_parent_id
3085 FROM ecx_object_attributes
3086 WHERE map_id = v_map_id and objectlevel_id in(
3087 select objectlevel_id from ecx_object_levels
3088 WHERE map_id=v_map_id and object_id = 1) AND attribute_id=l_parent_id;
3089
3090 IF (l_node_path IS NULL ) THEN
3091 l_node_path := l_attribute_name;
3092 ELSE
3093 l_node_path := l_attribute_name||'<'||l_node_path;
3094 END IF;
3095
3096
3097 END LOOP;
3098 IF (l_node_path IS NULL ) THEN
3099 l_node_path := l_root_node;
3100 ELSE
3101 l_node_path := l_root_node||'<'||l_node_path;
3102 END IF;
3103 if (l_procedureEnabled) then
3104 ecx_debug.pop(i_method_name);
3105 end if;
3106 return l_node_path;
3107 exception
3108 when others then
3109 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_UTILS.getNodePath ');
3110 if(l_unexpectedEnabled) then
3111 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
3112 end if;
3113 if (l_procedureEnabled) then
3114 ecx_debug.pop(i_method_name);
3115 end if;
3116 raise ecx_utils.PROGRAM_EXIT;
3117 End getNodePath;
3118
3119 /*get XML Parser version*/
3120
3121 Function XMLVersion
3122 return varchar2
3123 is language java name 'oracle.xml.parser.v2.XMLParser.getReleaseVersion() returns java.lang.String';
3124
3125
3126 end ecx_utils;