[Home] [Help]
PACKAGE BODY: APPS.EC_EXECUTION_UTILS
Source
1 PACKAGE BODY ec_execution_utils as
2 -- $Header: ECXUTILB.pls 115.24 2003/04/04 07:26:50 hgandiko ship $
3
4 -- Internal DBMS_DESCRIBE.DESCRIBE_PROCEDURE variables
5 v_overload dbms_describe.number_table;
6 v_position dbms_describe.number_table;
7 v_level dbms_describe.number_table;
8 v_argumentname dbms_describe.varchar2_table;
9 v_datatype dbms_describe.number_table;
10 v_defaultvalue dbms_describe.number_table;
11 v_inout dbms_describe.number_table;
12 v_length dbms_describe.number_table;
13 v_precision dbms_describe.number_table;
14 v_scale dbms_describe.number_table;
15 v_radix dbms_describe.number_table;
16 v_spare dbms_describe.number_table;
17
18 procedure printparams
19 (
20 i_procedure_name IN varchar2
21 )
22 is
23 BEGIN
24 if EC_DEBUG.G_debug_level >= 2 then
25 ec_debug.push('EC_EXECUTION_UTILS.PRINTPARAMS');
26 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
27 end if;
28 for i in 1..ec_utils.g_parameter_stack.COUNT
29 loop
30 if ec_utils.g_parameter_stack(i).procedure_name = i_procedure_name
31 then
32 if EC_DEBUG.G_debug_level >= 3 then
33 ec_debug.pl(3,ec_utils.g_parameter_stack(i).parameter_name,ec_utils.g_parameter_stack(i).value);
34 end if;
35 end if;
36 end loop;
37 if EC_DEBUG.G_debug_level >= 2 then
38 ec_debug.pop('EC_EXECUTION_UTILS.PRINTPARAMS');
39 end if;
40 exception
41 WHEN EC_UTILS.PROGRAM_EXIT then
42 raise;
43 WHEN OTHERS THEN
44 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.PRINTPARAMS');
45 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
46 ec_utils.i_ret_code :=2;
47 raise EC_UTILS.PROGRAM_EXIT;
48 end printparams;
49
50 procedure describeproc
51 (
52 i_procedure_name IN varchar2
53 )
54 is
55 i pls_integer :=0;
56 j pls_integer :=0;
57 v_argcounter pls_integer :=1;
58 i_procedure_loaded BOOLEAN := FALSE;
59 v_proc_string varchar2(32767); -- Bug 2637838
60 v_firstparam BOOLEAN := TRUE;
61 error_position pls_integer;
62 is_function BOOLEAN := FALSE;
63 i_return_value varchar2(32000);
64 BEGIN
65 if EC_DEBUG.G_debug_level >= 2 then
66 ec_debug.push('EC_EXECUTION_UTILS.DESCRIBEPROC');
67 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
68 end if;
69
70
71 -- This code is added for NULL procedure name
72 if nvl(length(replace(i_procedure_name, ' ')),0) < 1 then
73 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.DESCRIBEPROC');
74 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',' ===> NULL supplied as procedure name');
75 ec_utils.i_ret_code :=2;
76 raise EC_UTILS.PROGRAM_EXIT;
77 end if;
78
79 --- Describe and Load the procedure on Procedure Stack only if not loaded
80 for i in 1..ec_utils.g_procedure_stack.COUNT
81 loop
82 if ec_utils.g_procedure_stack(i).procedure_name = i_procedure_name
83 then
84 i_procedure_loaded := TRUE;
85 exit;
86 end if;
87 end loop;
88
89 if NOT ( i_procedure_loaded )
90 then
91 -- Standard Call provided by Oracle RDBMS
92
93 BEGIN
94 dbms_describe.describe_procedure
95 (
96 i_procedure_name,
97 null,
98 null,
99 v_overload,
100 v_position,
101 v_level,
102 v_argumentname,
103 v_datatype,
104 v_defaultvalue,
105 v_inout,
106 v_length,
107 v_precision,
108 v_scale,
109 v_radix,
110 v_spare
111 );
112 EXCEPTION
113 WHEN OTHERS THEN
114 ec_debug.pl(0,'EC','ECE_PROCEDURE_EXECUTION','PROCEDURE_NAME',i_procedure_name);
115 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.DESCRIBEPROC');
116 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
117 ec_utils.i_ret_code :=2;
118 raise EC_UTILS.PROGRAM_EXIT;
119 END;
120
121
122 v_proc_string := 'BEGIN '|| i_procedure_name || ' ( ';
123
124 loop
125 begin
126 if EC_DEBUG.G_debug_level = 3 then
127 ec_debug.pl(3,'Overloaded',v_overload(v_argcounter));
128 ec_debug.pl(3,'Position',v_position(v_argcounter));
129 ec_debug.pl(3,'Argument Name',v_argumentname(v_argcounter));
130 ec_debug.pl(3,'Level',v_level(v_argcounter));
131 ec_debug.pl(3,'Data Type',v_datatype(v_argcounter));
132 ec_debug.pl(3,'In/Out',v_inout(v_argcounter));
133 ec_debug.pl(3,'Length',v_length(v_argcounter));
134 ec_debug.pl(3,'Precision',v_precision(v_argcounter));
135 ec_debug.pl(3,'scale',v_scale(v_argcounter));
136 end if;
137
138
139 -- Procedure with no Parameters.
140 if v_datatype(v_argcounter) = 0
141 then
142 null;
143 exit;
144 end if;
145
146 /**
147 Procedures with Number,varchar2 and date are supported only.
148 **/
149 -- Procedure with no Parameters.
150 if v_datatype(v_argcounter) not in ( 1,2,12,96)
151 then
152 ec_debug.pl(0,'EC','ECE_PROCEDURE_EXECUTION','PROCEDURE_NAME',i_procedure_name);
153 ec_debug.pl(0,'EC','ECE_UNSUPPORTED_DATATYPE','Unsupported Data Type');
154 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.DESCRIBEPROC');
155 ec_utils.i_ret_code :=2;
156 raise EC_UTILS.PROGRAM_EXIT;
157 end if;
158
159 /**
160 Check For Function
161 **/
162 if v_position(v_argcounter) = 0
163 then
164 is_function := TRUE;
165 v_proc_string := 'BEGIN :i_return_value := '||i_procedure_name||' (';
166 i := ec_utils.g_parameter_stack.COUNT+1;
167 ec_utils.g_parameter_stack(i).procedure_name := upper(i_procedure_name);
168 ec_utils.g_parameter_stack(i).parameter_name := 'i_return_value';
169 ec_utils.g_parameter_stack(i).data_type := v_datatype(v_argcounter);
170 ec_utils.g_parameter_stack(i).in_out := v_inout(v_argcounter);
171
172 goto end_fun;
173 end if;
174
175 -- Load the definition on the Parameter Stack.
176 i := ec_utils.g_parameter_stack.COUNT+1;
177 ec_utils.g_parameter_stack(i).procedure_name := upper(i_procedure_name);
178 ec_utils.g_parameter_stack(i).parameter_name := v_argumentname(v_argcounter);
179 ec_utils.g_parameter_stack(i).data_type := v_datatype(v_argcounter);
180 ec_utils.g_parameter_stack(i).in_out := v_inout(v_argcounter);
181
182
183 if is_function
184 then
185 if v_argcounter =2
186 then
187 v_proc_string := v_proc_string || ':'||v_argumentname(v_argcounter);
188 elsif v_argcounter >= 2
189 then
190 v_proc_string := v_proc_string || ',:'||v_argumentname(v_argcounter);
191 end if;
192 else
193 if v_firstparam
194 then
195 v_proc_string := v_proc_string || ':'||v_argumentname(v_argcounter);
196 v_firstparam := FALSE;
197 else
198 v_proc_string := v_proc_string || ',:'||v_argumentname(v_argcounter);
199 end if;
200 end if;
201
202
203 <<end_fun>>
204 if EC_DEBUG.G_debug_level >= 3 then
205 ec_debug.pl(3,'Procedure name',ec_utils.g_parameter_stack(i).procedure_name);
206 ec_debug.pl(3,'Parameter name',ec_utils.g_parameter_stack(i).parameter_name);
207 ec_debug.pl(3,'Data Type',ec_utils.g_parameter_stack(i).data_type);
208 ec_debug.pl(3,'In Out',ec_utils.g_parameter_stack(i).in_out);
209 end if;
210
211 v_argcounter := v_argcounter + 1;
212 exception
213 when no_data_found then
214 exit;
215 end;
216 end loop;
217
218
219 v_proc_string := v_proc_string || '); END;';
220
221 if is_function
222 then
223 if v_argcounter = 2
224 then
225 v_proc_string := 'BEGIN :i_return_value := '||i_procedure_name||';END;';
226 end if;
227 else
228 if v_argcounter = 1
229 then
230 v_proc_string := 'BEGIN '||i_procedure_name||'; END;';
231 end if;
232 end if;
233 if EC_DEBUG.G_debug_level >= 3 then
234 ec_debug.pl(3,'Execution String :',v_proc_string);
235 end if;
236 -- Open the Cursor and parse the Statement.
237
238 -- Load the Procedure Stack
239 j := ec_utils.g_procedure_stack.COUNT + 1;
240 ec_utils.g_procedure_stack(j).procedure_name := i_procedure_name;
241 ec_utils.g_procedure_stack(j).cursor_handle := dbms_sql.open_cursor;
242 ec_utils.g_procedure_stack(j).execution_clause := v_proc_string;
243
244 if EC_DEBUG.G_debug_level >= 3 then
245 ec_debug.pl(3,'Procedure name',ec_utils.g_procedure_stack(j).procedure_name);
246 ec_debug.pl(3,'Cursor Handle ',ec_utils.g_procedure_stack(j).cursor_handle);
247 ec_debug.pl(3,'Procedure name',ec_utils.g_procedure_stack(j).execution_clause);
248 end if;
249
250 --Parse the Procedure String
251 BEGIN
252 dbms_sql.parse(ec_utils.g_procedure_stack(j).cursor_handle,v_proc_string,dbms_sql.native);
253 EXCEPTION
254 WHEN OTHERS THEN
255 ec_debug.pl(0,'EC','ECE_PROCEDURE_EXECUTION','PROCEDURE_NAME',i_procedure_name);
256 error_position := dbms_sql.last_error_position;
257 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.RUNPROC');
258 ece_error_handling_pvt.print_parse_error (error_position,v_proc_string);
259 ec_utils.i_ret_code :=2;
260 raise EC_UTILS.PROGRAM_EXIT;
261 END;
262
263 end if;
264
265 -- Bug 2340691
266 if EC_DEBUG.G_debug_level = 3 then
267 printparams
268 (
269 i_procedure_name
270 );
271 end if;
272
273 if EC_DEBUG.G_debug_level >= 2 then
274 ec_debug.pop('EC_EXECUTION_UTILS.DESCRIBEPROC');
275 end if;
276 exception
277 WHEN EC_UTILS.PROGRAM_EXIT then
278 raise;
279 WHEN OTHERS THEN
280 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.DESCRIBEPROC');
281 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
282 ec_utils.i_ret_code :=2;
283 raise EC_UTILS.PROGRAM_EXIT;
284 end describeproc;
285
286 procedure runproc
287 (
288 i_procedure_name IN varchar2
289 )
290 is
291
292 -- DBMS_SQL variables
293 v_cursor pls_integer;
294 v_numrows pls_integer;
295
296 v_proccall varchar2(500);
297 v_firstparam BOOLEAN := TRUE;
298 error_position pls_integer;
299 i_date date;
300 j_date date;
301 i_number number;
302 j_number number;
303 i_char char(500);
304
305 BEGIN
306 if EC_DEBUG.G_debug_level >= 2 then
307 ec_debug.push('EC_EXECUTION_UTILS.RUNPROC');
308 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
309 end if;
310
311 -- Bug 2340691
312 if EC_DEBUG.G_debug_level = 3 then
313 printparams
314 (
315 i_procedure_name
316 );
317 end if;
318
319 for i in 1..ec_utils.g_procedure_stack.COUNT
320 loop
321 if ec_utils.g_procedure_stack(i).procedure_name = i_procedure_name
322 then
323 -- Bind the procedure parameters.
324 for j in 1..ec_utils.g_parameter_stack.COUNT
325 loop
326 if ec_utils.g_parameter_stack(j).procedure_name = i_procedure_name
327 then
328
329 -- First set the Parameter Name
330 if EC_DEBUG.G_debug_level >= 3 then
331 ec_debug.pl(3,'Name',ec_utils.g_parameter_stack(j).parameter_name);
332 ec_debug.pl(3,'Datatype',ec_utils.g_parameter_stack(j).data_type);
333 end if;
334
335 -- Bind based on the parameter type
336 -- 2 Number
337 IF ec_utils.g_parameter_stack(j).data_type = 2
338 then
339 i_number := to_number(ec_utils.g_parameter_stack(j).value);
340 dbms_sql.bind_variable
341 (
342 ec_utils.g_procedure_stack(i).cursor_handle,
343 ':'||ec_utils.g_parameter_stack(j).parameter_name,
344 i_number
345 );
346 if EC_DEBUG.G_debug_level >= 3 then
347 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
348 end if;
349 -- 1 VARCHAR2
350 elsif ec_utils.g_parameter_stack(j).data_type = 1
351 then
352 dbms_sql.bind_variable
353 (
354 ec_utils.g_procedure_stack(i).cursor_handle,
355 ':'||ec_utils.g_parameter_stack(j).parameter_name,
356 ec_utils.g_parameter_stack(j).value,
357 32000
358 );
359 if EC_DEBUG.G_debug_level >= 3 then
360 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
361 end if;
362 -- 12 DATE
363 elsif ec_utils.g_parameter_stack(j).data_type = 12
364 then
365 i_date := to_date(ec_utils.g_parameter_stack(j).value,'YYYYMMDD HH24MISS');
366 dbms_sql.bind_variable
367 (
368 ec_utils.g_procedure_stack(i).cursor_handle,
369 ':'||ec_utils.g_parameter_stack(j).parameter_name,
370 i_date
371 );
372 if EC_DEBUG.G_debug_level >= 3 then
373 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,i_date);
374 end if;
375 -- 96 CHAR
376 elsif ec_utils.g_parameter_stack(j).data_type = 96
377 then
378 dbms_sql.bind_variable
379 (
380 ec_utils.g_procedure_stack(i).cursor_handle,
381 ':'||ec_utils.g_parameter_stack(j).parameter_name,
382 ec_utils.g_parameter_stack(j).value,
383 600
384 );
385 if EC_DEBUG.G_debug_level >= 3 then
386 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
387 end if;
388 else
389 ec_debug.pl(0,'EC','ECE_PROCEDURE_EXECUTION','PROCEDURE_NAME',i_procedure_name);
390 ec_debug.pl(0,'EC','ECE_UNSUPPORTED_DATATYPE','Unsupported Data Type');
391 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.RUNPROC');
392 ec_utils.i_ret_code :=2;
393 raise EC_UTILS.PROGRAM_EXIT;
394 end if;
395
396 end if; --- procedure name in the parameter Stack
397 end loop; -- End of paremeter Loop i.e. j
398
399 -- Execute the Procedure.
400 if EC_DEBUG.G_debug_level >= 3 then
401 ec_debug.pl(3,'Before Execution','Yes');
402
403 ec_debug.pl(3,'Procedure name',ec_utils.g_procedure_stack(i).procedure_name);
404 ec_debug.pl(3,'Cursor Handle ',ec_utils.g_procedure_stack(i).cursor_handle);
405 ec_debug.pl(3,'Procedure name',ec_utils.g_procedure_stack(i).execution_clause);
406 end if;
407 v_numrows := DBMS_SQL.execute(ec_utils.g_procedure_stack(i).cursor_handle);
408 if EC_DEBUG.G_debug_level >= 3 then
409 ec_debug.pl(3,'Execution Successful','Yes');
410 end if;
411 -- Call Variable value for any OUT or IN/OUT parameters
412 for j in 1..ec_utils.g_parameter_stack.COUNT
413 loop
414 if ec_utils.g_parameter_stack(j).procedure_name = i_procedure_name
415 then
416 if ec_utils.g_parameter_stack(j).in_out = 1 or ec_utils.g_parameter_stack(j).in_out = 2
417 then
418 if ec_utils.g_parameter_stack(j).data_type= 2
419 then
420 dbms_sql.variable_value
421 (
422 ec_utils.g_procedure_stack(i).cursor_handle,
423 ':'||ec_utils.g_parameter_stack(j).parameter_name,
424 j_number
425 );
426 ec_utils.g_parameter_stack(j).value := to_number(j_number);
427 if EC_DEBUG.G_debug_level >= 3 then
428 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
429 end if;
430 elsif ec_utils.g_parameter_stack(j).data_type= 1
431 then
432 dbms_sql.variable_value
433 (
434 ec_utils.g_procedure_stack(i).cursor_handle,
435 ':'||ec_utils.g_parameter_stack(j).parameter_name,
436 ec_utils.g_parameter_stack(j).value
437 );
438 if EC_DEBUG.G_debug_level >= 3 then
439 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
440 end if;
441 elsif ec_utils.g_parameter_stack(j).data_type= 12
442 then
443 dbms_sql.variable_value
444 (
445 ec_utils.g_procedure_stack(i).cursor_handle,
446 ':'||ec_utils.g_parameter_stack(j).parameter_name,
447 j_date
448 );
449
450 ec_utils.g_parameter_stack(j).value := to_char(j_date,'YYYYMMDD HH24MISS');
451 if EC_DEBUG.G_debug_level >= 3 then
452 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
453 end if;
454 elsif ec_utils.g_parameter_stack(j).data_type= 96
455 then
456 dbms_sql.variable_value
457 (
458 ec_utils.g_procedure_stack(i).cursor_handle,
459 ':'||ec_utils.g_parameter_stack(j).parameter_name,
460 ec_utils.g_parameter_stack(j).value
461 );
462 if EC_DEBUG.G_debug_level >= 3 then
463 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,ec_utils.g_parameter_stack(j).value);
464 end if;
465 else
466 ec_debug.pl(0,'EC','ECE_UNSUPPORTED_DATATYPE','Unsupported Data Type');
467 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.RUNPROC');
468 ec_utils.i_ret_code :=2;
469 raise EC_UTILS.PROGRAM_EXIT;
470 end if;
471
472 end if; -- For In/OUT
473
474 end if; -- i_procedure_name
475 end loop; -- for parameter stack
476
477 exit; --- procedure Found. Exit the Loop.
478 end if; -- Main Procedure_name
479 end loop; -- for procedure_name in the Program Stack for i
480
481 -- Bug 2340691
482 if EC_DEBUG.G_debug_level = 3 then
483 printparams
484 (
485 i_procedure_name
486 );
487 end if;
488
489 if EC_DEBUG.G_debug_level >= 2 then
490 ec_debug.pop('EC_EXECUTION_UTILS.RUNPROC');
491 end if;
492 exception
493 WHEN EC_UTILS.PROGRAM_EXIT then
494 raise;
495 WHEN OTHERS THEN
496 ec_debug.pl(0,'EC','ECE_PROCEDURE_ERROR','PROCEDURE_NAME',i_procedure_name);
497 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.RUNPROC');
498 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
499 ec_utils.i_ret_code :=2;
500 raise EC_UTILS.PROGRAM_EXIT;
501 end runproc;
502
503 procedure load_procedure_definitions
504 is
505 BEGIN
506 if EC_DEBUG.G_debug_level >= 2 then
507 ec_debug.push('EC_EXECUTION_UTILS.LOAD_PROCEDURE_DEFINITIONS');
508 end if;
509 for i in 1..ec_utils.g_stage_data.COUNT
510 loop
511 if (
512 ec_utils.g_stage_data(i).action_type = 1050 OR
513 ec_utils.g_stage_data(i).action_type = 80 OR
514 ec_utils.g_stage_data(i).action_type = 1080 OR
515 ec_utils.g_stage_data(i).action_type = 1090 OR
516 ec_utils.g_stage_data(i).action_type = 1100 OR
517 ec_utils.g_stage_data(i).action_type = 1110
518 )
519 then
520 describeproc
521 (
522 ec_utils.g_stage_data(i).custom_procedure_name
523 );
524 end if;
525 end loop;
526
527 if EC_DEBUG.G_debug_level >= 2 then
528 ec_debug.pop('EC_EXECUTION_UTILS.LOAD_PROCEDURE_DEFINITIONS');
529 end if;
530 exception
531 WHEN EC_UTILS.PROGRAM_EXIT then
532 raise;
533 WHEN OTHERS THEN
534 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.LOAD_PROCEDURE_DEFINITIONS');
535 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
536 ec_utils.i_ret_code :=2;
537 raise EC_UTILS.PROGRAM_EXIT;
538 end load_procedure_definitions;
539
540 procedure load_mandatory_columns
541 (
542 i_level IN pls_integer
543 )
544 is
545 BEGIN
546 if EC_DEBUG.G_debug_level >= 2 then
547 ec_debug.push('EC_EXECUTION_UTILS.LOAD_MANDATORY_COLUMNS');
548 ec_debug.pl(3,'i_level',i_level);
549 end if;
550
551 for i in 1..ec_utils.g_stage_data.COUNT
552 loop
553 if (
554 ec_utils.g_stage_data(i).variable_level = i_level AND
555 ec_utils.g_stage_data(i).action_type = 110
556 )
557 then
558 ec_utils.create_mandatory_columns
559 (
560 ec_utils.g_stage_data(i).variable_level,
561 ec_utils.g_stage_data(i).previous_variable_level,
562 ec_utils.g_stage_data(i).variable_name,
563 ec_utils.g_stage_data(i).default_value,
564 ec_utils.g_stage_data(i).data_type,
565 ec_utils.g_stage_data(i).function_name
566 );
567 end if;
568 end loop;
569
570 if EC_DEBUG.G_debug_level >= 2 then
571 ec_debug.pop('EC_EXECUTION_UTILS.LOAD_MANDATORY_COLUMNS');
572 end if;
573 exception
574 WHEN EC_UTILS.PROGRAM_EXIT then
575 raise;
576 WHEN OTHERS THEN
577 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.LOAD_MANDATORY_COLUMNS');
578 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
579 ec_utils.i_ret_code :=2;
580 raise EC_UTILS.PROGRAM_EXIT;
581 end load_mandatory_columns;
582
583 procedure load_procedure_mappings
584 (
585 i_transaction_type IN varchar2
586 )
587 is
588 cursor proc_mapping
589 (
590 p_transaction_type IN varchar2
591 )
592 is
593 select epm.transtage_id transtage_id,
594 upper(etsd.custom_procedure_name) procedure_name,
595 upper(epm.parameter_name) parameter_name,
596 epm.action_type action_type,
597 epm.variable_level variable_level,
598 upper(epm.variable_name) variable_name
599 from ece_procedure_mappings epm,
600 ece_tran_stage_data etsd
601 where etsd.transtage_id = epm.transtage_id
602 and etsd.transaction_type = p_transaction_type
603 and etsd.map_id = ec_utils.g_map_id;
604 i pls_integer := ec_utils.g_procedure_mappings.COUNT;
605 BEGIN
606 if EC_DEBUG.G_debug_level >= 2 then
607 ec_debug.push('EC_EXECUTION_UTILS.LOAD_PROCEDURE_MAPPINGS');
608 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
609 end if;
610
611 for proc_map in proc_mapping
612 (
613 p_transaction_type => i_transaction_type
614 )
615 loop
616 i := i + 1;
617 ec_utils.g_procedure_mappings(i).transtage_id := proc_map.transtage_id;
618 ec_utils.g_procedure_mappings(i).procedure_name := proc_map.procedure_name;
619 ec_utils.g_procedure_mappings(i).parameter_name := proc_map.parameter_name;
620 ec_utils.g_procedure_mappings(i).action_type := proc_map.action_type;
621 ec_utils.g_procedure_mappings(i).variable_level := proc_map.variable_level;
622 ec_utils.g_procedure_mappings(i).variable_name := proc_map.variable_name;
623
624 if EC_DEBUG.G_debug_level >= 3 then
625 ec_debug.pl(3, ec_utils.g_procedure_mappings(i).tranStage_id ||' '||
626 ec_utils.g_procedure_mappings(i).procedure_name||' '||
627 ec_utils.g_procedure_mappings(i).parameter_name||' '||
628 ec_utils.g_procedure_mappings(i).action_type||' '||
629 ec_utils.g_procedure_mappings(i).variable_level||' '||
630 ec_utils.g_procedure_mappings(i).variable_name
631 );
632 end if;
633 end loop;
634
635 if EC_DEBUG.G_debug_level >= 2 then
636 ec_debug.pop('EC_EXECUTION_UTILS.LOAD_PROCEDURE_MAPPINGS');
637 end if;
638 exception
639 WHEN EC_UTILS.PROGRAM_EXIT then
640 raise;
641 WHEN OTHERS THEN
642 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.LOAD_PROCEDURE_MAPPINGS');
643 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
644 ec_utils.i_ret_code :=2;
645 raise EC_UTILS.PROGRAM_EXIT;
646 end load_procedure_mappings;
647
648 procedure assign_values
649 (
650 i_transtage_id IN pls_integer,
651 i_procedure_name IN varchar2,
652 i_action_type IN pls_integer
653 )
654 is
655 m_var_found BOOLEAN := FALSE;
656 i_stack_pos pls_integer;
657 i_plsql_pos pls_integer;
658 BEGIN
659 if EC_DEBUG.G_debug_level >= 2 then
660 ec_debug.push('EC_EXECUTION_UTILS.ASSIGN_VALUES');
661 ec_debug.pl(3,'i_transtage_id',i_transtage_id);
662 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
663 ec_debug.pl(3,'i_action_type',i_action_type);
664 end if;
665 -- Print Procedure Mappings as well as Parameters
666 -- Bug 2340691
667 if EC_DEBUG.G_debug_level >= 3 then
668 for k in 1..ec_utils.g_procedure_mappings.COUNT
669 loop
670 if (
671 ec_utils.g_procedure_mappings(k).procedure_name = i_procedure_name AND
672 ec_utils.g_procedure_mappings(k).transtage_id = i_transtage_id
673 )
674 then
675 ec_debug.pl(3,
676 ec_utils.g_procedure_mappings(k).parameter_name||' '||
677 ec_utils.g_procedure_mappings(k).variable_level||' '||
678 ec_utils.g_procedure_mappings(k).variable_name
679 );
680 end if;
681 end loop;
682 end if;
683
684 --- Initialize all the values of parameter before assignment.
685 if i_action_type = 1060
686 then
687 for k in 1..ec_utils.g_parameter_stack.COUNT
688 loop
689 if (
690 ec_utils.g_parameter_stack(k).procedure_name = i_procedure_name
691 )
692 then
693 ec_utils.g_parameter_stack(k).value := NULL;
694 end if;
695 end loop;
696 end if;
697
698 -- Bug 2340691
699 if EC_DEBUG.G_debug_level >= 3 then
700 printparams
701 (
702 i_procedure_name
703 );
704 end if;
705
706 for i in 1..ec_utils.g_procedure_mappings.COUNT
707 loop
708 IF ( ec_utils.g_procedure_mappings(i).transtage_id = i_transtage_id
709 AND ec_utils.g_procedure_mappings(i).procedure_name = i_procedure_name
710 AND ec_utils.g_procedure_mappings(i).action_type = i_action_type
711 )
712 then
713 if EC_DEBUG.G_debug_level >= 3 then
714 ec_debug.pl(3,'Processing ',ec_utils.g_procedure_mappings(i).parameter_name);
715 end if;
716 m_var_found := ec_utils.find_variable
717 (
718 ec_utils.g_procedure_mappings(i).variable_level,
719 ec_utils.g_procedure_mappings(i).variable_name,
720 i_stack_pos,
721 i_plsql_pos
722 );
723 if NOT ( m_var_found)
724 then
725 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',
726 ec_utils.g_procedure_mappings(i).variable_name);
727 ec_utils.i_ret_code := 2;
728 raise EC_UTILS.PROGRAM_EXIT;
729 end if;
730
731 for j in 1..ec_utils.g_parameter_stack.COUNT
732 loop
733 if (
734 ec_utils.g_procedure_mappings(i).parameter_name is not null AND
735 ec_utils.g_parameter_stack(j).procedure_name = ec_utils.g_procedure_mappings(i).procedure_name AND
736 ec_utils.g_parameter_stack(j).parameter_name = ec_utils.g_procedure_mappings(i).parameter_name
737 )
738 then
739 if EC_DEBUG.G_debug_level >= 3 then
740 ec_debug.pl(3,'Match Found for ',ec_utils.g_procedure_mappings(i).parameter_name);
741 end if;
742 if ec_utils.g_procedure_mappings(i).action_type = 1060
743 then
744 if ec_utils.g_procedure_mappings(i).variable_level = 0
745 then
746 ec_utils.g_parameter_stack(j).value :=
747 ec_utils.g_stack(i_stack_pos).variable_value;
748 if EC_DEBUG.G_debug_level >= 3 then
749 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,
750 ec_utils.g_parameter_stack(j).value);
751 end if;
752 else
753 ec_utils.g_parameter_stack(j).value := ec_utils.g_file_tbl(i_plsql_pos).value;
754 if EC_DEBUG.G_debug_level >= 3 then
755 ec_debug.pl(3,ec_utils.g_parameter_stack(j).parameter_name,
756 ec_utils.g_parameter_stack(j).value);
757 end if;
758 end if;
759 exit;
760
761 elsif ec_utils.g_procedure_mappings(i).action_type = 1070
762 then
763
764 if ec_utils.g_parameter_stack(j).parameter_name is not null AND
765 ec_utils.g_parameter_stack(j).parameter_name = ec_utils.g_procedure_mappings(i).parameter_name
766 then
767 if ec_utils.g_procedure_mappings(i).variable_level = 0
768 then
769 ec_utils.g_stack(i_stack_pos).variable_value := ec_utils.g_parameter_stack(j).value;
770 if EC_DEBUG.G_debug_level >= 3 then
771 ec_debug.pl(3,ec_utils.g_stack(i_stack_pos).variable_name,
772 ec_utils.g_stack(i_stack_pos).variable_value);
773 end if;
774 else
775 ec_utils.g_file_tbl(i_plsql_pos).value := ec_utils.g_parameter_stack(j).value;
776 if EC_DEBUG.G_debug_level >= 3 then
777 ec_debug.pl(3,
778 ec_utils.g_file_tbl(i_plsql_pos).interface_column_name,
779 ec_utils.g_file_tbl(i_plsql_pos).value);
780 end if;
781 end if;
782 exit;
783 end if;
784
785 else
786 ec_debug.pl(0,'EC','ECE_PROCEDURE_EXECUTION','PROCEDURE_NAME',i_procedure_name);
787 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.ASSIGN_VALUES');
788 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
789 ec_utils.i_ret_code :=2;
790 raise EC_UTILS.PROGRAM_EXIT;
791 end if; -- For Action Types
792
793 else
794 -- Assign Back the Function value
795 if (
796 ec_utils.g_procedure_mappings(i).parameter_name is null AND
797 ec_utils.g_parameter_stack(j).procedure_name = ec_utils.g_procedure_mappings(i).procedure_name AND
798 ec_utils.g_parameter_stack(j).parameter_name is null
799 )
800 then
801 if EC_DEBUG.G_debug_level >= 3 then
802 ec_debug.pl(3,'Match Found for ',ec_utils.g_procedure_mappings(i).parameter_name);
803 end if;
804 if ec_utils.g_procedure_mappings(i).action_type = 1070
805 then
806 if ec_utils.g_procedure_mappings(i).variable_level = 0
807 then
808 ec_utils.g_stack(i_stack_pos).variable_value := ec_utils.g_parameter_stack(j).value;
809 else
810 ec_utils.g_file_tbl(i_plsql_pos).value := ec_utils.g_parameter_stack(j).value;
811 end if;
812 end if;
813 exit;
814 end if;
815 end if;
816 end loop; --- j
817
818 end if;
819 end loop; --- i
820
821 if EC_DEBUG.G_debug_level >= 2 then
822 ec_debug.pop('EC_EXECUTION_UTILS.ASSIGN_VALUES');
823 end if;
824 exception
825 WHEN EC_UTILS.PROGRAM_EXIT then
826 raise;
827 WHEN OTHERS THEN
828 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.ASSIGN_VALUES');
829 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
830 ec_utils.i_ret_code :=2;
831 raise EC_UTILS.PROGRAM_EXIT;
832 end assign_values;
833
834 procedure load_mappings
835 (
836 i_transaction_type in varchar2,
837 i_map_id in pls_integer
838 )
839 is
840 cursor external_rec
841 (
842 p_transaction_type IN varchar2,
843 p_map_id in number
844 )
845 is
846 select external_level_id,
847 external_level,
848 start_element
849 from ece_external_levels
850 where transaction_type = p_transaction_type
851 and map_id = p_map_id
852 order by external_level;
853
854 cursor interface_rec
855 (
856 p_transaction_type in varchar2,
857 p_map_id In number
858 )
859 is
860 select to_number(output_level) output_level,
861 interface_table_name,
862 parent_level,
863 interface_table_id,
864 key_column_name
865 from ece_interface_tables
866 where transaction_type = p_transaction_type
867 and map_id = p_map_id
868 order by to_number(output_level);
869
870 cursor interface_external_rec
871 (
872 p_external_id in number
873 )
874 is
875 select to_number(eit.output_level) output_level
876 from ece_level_matrices elm,
877 ece_interface_tables eit
878 where elm.external_level_id = p_external_id
879 and eit.interface_table_id = elm.interface_table_id;
880
881 cursor mapping_rec
882 (
883 p_interface_table_id number,
884 p_map_id number
885 ) is
886 select interface_column_name,
887 interface_column_id,
888 base_column_name,
889 conversion_sequence,
890 conversion_group_id,
891 xref_category_id,
892 xref_category_allowed,
893 xref_key1_source_column,
894 xref_key2_source_column,
895 xref_key3_source_column,
896 xref_key4_source_column,
897 xref_key5_source_column,
898 staging_column,
899 data_type,
900 width,
901 record_number,
902 position,
903 record_layout_code,
904 record_layout_qualifier,
905 element_tag_name,
906 external_level
907 from ece_interface_columns
908 where interface_table_id = p_interface_table_id
909 and map_id = p_map_id
910 ORDER BY interface_column_id;
911
912 -- Bug 2708573
913 CURSOR c_col_rule_info
914 (
915 p_interface_column_id NUMBER
916 ) IS
917 select column_rule_id,
918 rule_type,
919 action_code
920 from ece_column_rules
921 where interface_column_id = p_interface_column_id
922 order by sequence;
923
924 m_count pls_integer := ec_utils.g_file_tbl.COUNT;
925 i_count pls_integer :=0;
926 j_count pls_integer :=0;
927 i_file_count pls_integer :=0;
928 i pls_integer :=0;
929 j pls_integer :=0;
930 k pls_integer :=0;
931 i_first_found BOOLEAN := FALSE;
932 i_last_found BOOLEAN := FALSE;
933 hash_value pls_integer; -- Bug 2617428
934 hash_val pls_integer;
935 hash_string varchar2(3200);
936 p_count pls_integer := 0;
937 begin
938
939 if EC_DEBUG.G_debug_level >= 2 then
940 ec_debug.push('EC_EXECUTION_UTILS.LOAD_MAPPINGS');
941 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
942 ec_debug.pl(3,'i_map_id',i_map_id);
943
944 ec_debug.pl(3,'EC','ECE_LOADING_LEVELS','LEVEL','Loading External levels');
945 end if;
946 i_count := ec_utils.g_ext_levels.COUNT;
947 i := i_count;
948
949 /**
950 Set the Stack Pointer for Level 0 to 1..0
951 **/
952 ec_utils.g_stack_pointer(0).start_pos :=1;
953 ec_utils.g_stack_pointer(0).end_pos :=0;
954
955 for c1 in external_rec
956 (
957 p_transaction_type => i_transaction_type,
958 p_map_id => i_map_id
959 )
960 loop
961 i_count := i_count + 1;
962 ec_utils.g_ext_levels(i_count).external_level := c1.external_level;
963 ec_utils.g_ext_levels(i_count).record_number := c1.start_element;
964 ec_utils.g_ext_levels(i_count).sql_stmt := NULL;
965 ec_utils.g_ext_levels(i_count).cursor_handle := 0;
966 ec_utils.g_ext_levels(i_count).file_start_pos := 0;
967 ec_utils.g_ext_levels(i_count).file_end_pos := 0;
968 if EC_DEBUG.G_debug_level >= 3 then
969 ec_debug.pl(3,'External Level ('||i_count||')',ec_utils.g_ext_levels(i_count).external_level);
970
971 ec_debug.pl(3,'EC','ECE_LOADING_LEVELS','LEVEL','Loading Levels Matrices');
972 end if;
973
974 j_count := ec_utils.g_int_ext_levels.COUNT;
975 j := j_count;
976 for c3 in interface_external_rec
977 (
978 p_external_id => c1.external_level_id
979 )
980 loop
981 j_count := j_count + 1;
982 ec_utils.g_int_ext_levels(j_count).interface_level := c3.output_level;
983 ec_utils.g_int_ext_levels(j_count).external_level := c1.external_level;
984 if EC_DEBUG.G_debug_level >= 3 then
985 ec_debug.pl(3,'Internal Level '||c3.Output_level||' External Level '||c1.External_level);
986 end if;
987 end loop;
988
989 /**
990 Check for Seed Data. If not Found then , then do not process.
991 **/
992 if j_count = j
993 then
994 ec_debug.pl(0,'EC','ECE_SEED_DATA_MISSING','TRANSACTION_TYPE',i_transaction_type,'MAPID',i_map_id);
995 /**
996 Set the Retcode for the Concurrent Manager
997 **/
998 ec_utils.i_ret_code := 2;
999 raise ec_utils.program_exit;
1000 end if;
1001
1002 end loop;
1003
1004 /**
1005 Check for Seed Data. If not Found then , then do not process.
1006 **/
1007 if i_count = i
1008 then
1009 ec_debug.pl(0,'EC','ECE_SEED_DATA_MISSING','TRANSACTION_TYPE',i_transaction_type,'MAPID',i_map_id);
1010 /**
1011 Set the Retcode for the Concurrent Manager
1012 **/
1013 ec_utils.i_ret_code := 2;
1014 raise ec_utils.program_exit;
1015 end if;
1016
1017 if EC_DEBUG.G_debug_level >= 3 then
1018 ec_debug.pl(3,'EC','ECE_LOADING_LEVELS','LEVEL','Loading Internal Levels');
1019 end if;
1020 i_count :=ec_utils.g_int_levels.COUNT;
1021 k := i_count;
1022 for c2 in interface_rec
1023 (
1024 p_transaction_type => i_transaction_type,
1025 p_map_id => i_map_id
1026 )
1027 loop
1028 i_count := ec_utils.g_int_levels.COUNT + 1;
1029 ec_utils.g_int_levels(i_count).interface_level := c2.output_level;
1030 ec_utils.g_int_levels(i_count).base_table_name := c2.interface_table_name;
1031 ec_utils.g_int_levels(i_count).parent_level := c2.parent_level;
1032 ec_utils.g_int_levels(i_count).key_column_name := c2.key_column_name;
1033 ec_utils.g_int_levels(i_count).cursor_handle := 0;
1034 ec_utils.g_int_levels(i_count).rows_processed := 0;
1035 ec_utils.g_int_levels(i_count).file_start_pos := 1;
1036 ec_utils.g_int_levels(i_count).file_end_pos := 0;
1037 if EC_DEBUG.G_debug_level >= 3 then
1038 ec_debug.pl(3,'Internal Level ('||i_count||') Object Name: '
1039 ||ec_utils.g_int_levels(i_count).base_table_name||
1040 ' Parent Level '||ec_utils.g_int_levels(i_count).parent_level);
1041 end if;
1042 i_file_count := m_count;
1043 i_first_found := FALSE;
1044
1045 for c4 in mapping_rec
1046 (
1047 c2.interface_table_id,
1048 i_map_id
1049 )
1050 loop
1051 m_count := ec_utils.g_file_tbl.COUNT + 1;
1052 /**
1053 Set the File Pointer now
1054 **/
1055 if NOT (i_first_found)
1056 then
1057 i_first_found := TRUE;
1058 ec_utils.g_int_levels(i_count).file_start_pos := m_count;
1059 end if;
1060
1061 ec_utils.g_file_tbl(m_count).interface_level := c2.output_level;
1062 ec_utils.g_file_tbl(m_count).interface_column_id := c4.interface_column_id;
1063 ec_utils.g_file_tbl(m_count).interface_column_name := c4.interface_column_name;
1064 ec_utils.g_file_tbl(m_count).base_column_name := c4.base_column_name;
1065 ec_utils.g_file_tbl(m_count).conversion_sequence := c4.conversion_sequence;
1066 ec_utils.g_file_tbl(m_count).conversion_group_id := c4.conversion_group_id;
1067 ec_utils.g_file_tbl(m_count).xref_category_id := c4.xref_category_id;
1068 ec_utils.g_file_tbl(m_count).xref_category_allowed := c4.xref_category_allowed;
1069 ec_utils.g_file_tbl(m_count).xref_key1_source_column := c4.xref_key1_source_column;
1070 ec_utils.g_file_tbl(m_count).xref_key2_source_column := c4.xref_key2_source_column;
1071 ec_utils.g_file_tbl(m_count).xref_key3_source_column := c4.xref_key3_source_column;
1072 ec_utils.g_file_tbl(m_count).xref_key4_source_column := c4.xref_key4_source_column;
1073 ec_utils.g_file_tbl(m_count).xref_key5_source_column := c4.xref_key5_source_column;
1074 ec_utils.g_file_tbl(m_count).staging_column := c4.staging_column;
1075 ec_utils.g_file_tbl(m_count).data_type := c4.data_type;
1076 ec_utils.g_file_tbl(m_count).width := c4.width;
1077 ec_utils.g_file_tbl(m_count).record_layout_code := c4.record_layout_code;
1078 ec_utils.g_file_tbl(m_count).record_layout_qualifier := c4.record_layout_qualifier;
1079 ec_utils.g_file_tbl(m_count).record_number := c4.record_number;
1080 ec_utils.g_file_tbl(m_count).position := c4.position;
1081 ec_utils.g_file_tbl(m_count).element_tag_name := c4.element_tag_name;
1082 ec_utils.g_file_tbl(m_count).external_level := c4.external_level;
1083
1084 if EC_DEBUG.G_debug_level = 3 then
1085 ec_debug.pl(3,
1086 m_count||' '||
1087 ec_utils.g_file_tbl(m_count).interface_level||' '||
1088 ec_utils.g_file_tbl(m_count).external_level||' '||
1089 ec_utils.g_file_tbl(m_count).interface_column_id||' '||
1090 ec_utils.g_file_tbl(m_count).interface_column_name||' '||
1091 ec_utils.g_file_tbl(m_count).base_column_name||' '||
1092 ec_utils.g_file_tbl(m_count).staging_column||' '||
1093 ec_utils.g_file_tbl(m_count).data_type||' '||
1094 ec_utils.g_file_tbl(m_count).width||' '||
1095 ec_utils.g_file_tbl(m_count).record_layout_code||' '||
1096 ec_utils.g_file_tbl(m_count).record_layout_qualifier||' '||
1097 ec_utils.g_file_tbl(m_count).record_number||' '||
1098 ec_utils.g_file_tbl(m_count).position||' '||
1099 ec_utils.g_file_tbl(m_count).element_tag_name||' '||
1100 ec_utils.g_file_tbl(m_count).conversion_sequence||' '||
1101 ec_utils.g_file_tbl(m_count).conversion_group_id||' '||
1102 ec_utils.g_file_tbl(m_count).xref_category_id||' '||
1103 ec_utils.g_file_tbl(m_count).xref_category_allowed||' '||
1104 ec_utils.g_file_tbl(m_count).xref_key1_source_column||' '||
1105 ec_utils.g_file_tbl(m_count).xref_key2_source_column||' '||
1106 ec_utils.g_file_tbl(m_count).xref_key3_source_column||' '||
1107 ec_utils.g_file_tbl(m_count).xref_key4_source_column||' '||
1108 ec_utils.g_file_tbl(m_count).xref_key5_source_column
1109 );
1110 end if;
1111
1112 /* Bug 1853627
1113 Added the following code which will be used by Validate
1114 Column Rules later in the inbound processing.
1115 */
1116 -- Bug 2112028 - Added the rownum condition in the following query
1117
1118 /* Bug 2708573
1119 begin
1120 select 'Y'
1121 into ec_utils.g_file_tbl(m_count).column_rule_flag
1122 from ece_column_rules
1123 where interface_column_id = c4.interface_column_id
1124 and rownum = 1;
1125 exception
1126 when no_data_found then
1127 ec_utils.g_file_tbl(m_count).column_rule_flag := 'N';
1128 end;
1129 */
1130
1131 -- Bug 2708573
1132 for c5 in c_col_rule_info
1133 (
1134 c4.interface_column_id
1135 )
1136 loop
1137 ec_utils.g_column_rule_tbl(m_count).column_rule_id := c5.column_rule_id;
1138 ec_utils.g_column_rule_tbl(m_count).rule_type := c5.rule_type;
1139 ec_utils.g_column_rule_tbl(m_count).action_code := c5.action_code;
1140 ec_utils.g_column_rule_tbl(m_count).level := c4.external_level;
1141 end loop;
1142
1143 -- Bug 2617428.
1144 -- Build hash table to store positions of columns requiring code conversion.
1145 -- Bug 2791195: Modified the hash string and used dbms_utility to create hash table..
1146 if c4.conversion_group_id IS NOT NULL then
1147 hash_string:=to_char(c4.conversion_group_id)||'-'||
1148 to_char(c4.external_level)||'-'||
1149 to_char(c4.conversion_sequence);
1150 hash_value := dbms_utility.get_hash_value(hash_string,1,8192);
1151 if ec_utils.g_code_conv_pos_tbl_1.exists(hash_value) then
1152 if ec_utils.g_code_conv_pos_tbl_1(hash_value).occr=1 then
1153 p_count:=ec_utils.g_code_conv_pos_tbl_1(hash_value).value;
1154 ec_utils.g_code_conv_pos_tbl_2(p_count):=hash_value;
1155 ec_utils.g_code_conv_pos_tbl_1(hash_value).value:=0;
1156 ec_utils.g_code_conv_pos_tbl_1(hash_value).start_pos:=p_count;
1157 end if;
1158 ec_utils.g_code_conv_pos_tbl_1(hash_value).occr:=
1159 ec_utils.g_code_conv_pos_tbl_1(hash_value).occr +1;
1160 ec_utils.g_code_conv_pos_tbl_2(m_count):=hash_value;
1161 else
1162 ec_utils.g_code_conv_pos_tbl_1(hash_value).value:=m_count;
1163 ec_utils.g_code_conv_pos_tbl_1(hash_value).occr:=1;
1164 ec_utils.g_code_conv_pos_tbl_1(hash_value).value:=m_count;
1165 end if;
1166 end if;
1167
1168 -- Build hash table to store positions of columns in g_file_tbl.
1169 -- bug 2721631
1170 hash_string:=to_char(c4.external_level)||'-'||upper(c4.interface_column_name);
1171 hash_val := dbms_utility.get_hash_value(hash_string,1,8192);
1172
1173 -- Bug 2834366
1174 if ec_utils.g_col_pos_tbl_1.exists(hash_val) then
1175 if ec_utils.g_col_pos_tbl_1(hash_val).occr=1 then
1176 p_count:=ec_utils.g_col_pos_tbl_1(hash_val).value;
1177 ec_utils.g_col_pos_tbl_2(p_count):=hash_val;
1178 ec_utils.g_col_pos_tbl_1(hash_val).value:=0;
1179 ec_utils.g_col_pos_tbl_1(hash_val).start_pos:=p_count;
1180 end if;
1181 ec_utils.g_col_pos_tbl_1(hash_val).occr:=
1182 ec_utils.g_col_pos_tbl_1(hash_val).occr +1;
1183 ec_utils.g_col_pos_tbl_2(m_count):=hash_val;
1184 else
1185 ec_utils.g_col_pos_tbl_1(hash_val).occr:=1;
1186 ec_utils.g_col_pos_tbl_1(hash_val).value:=m_count;
1187 end if;
1188
1189 end loop;
1190
1191 /**
1192 Stub for calling ACTION TYPE = 110
1193 Applicable only for Inbound
1194 **/
1195
1196 if ec_utils.g_direction = 'I'
1197 then
1198 load_mandatory_columns(i_count);
1199 end if;
1200
1201
1202 if i_file_count = m_count
1203 then
1204 ec_debug.pl(0,'EC','ECE_SEED_NOT_LEVEL','TRANSACTION_TYPE',i_transaction_type,'LEVEL',c2.output_level);
1205 /**
1206 Set the Retcode for the Concurrent Manager to Error.
1207 **/
1208 ec_utils.i_ret_code := 2;
1209 raise EC_UTILS.PROGRAM_EXIT;
1210 end if;
1211
1212 /**
1213 Update the File/Stack pointer for Internal Levels over here.
1214 **/
1215 ec_utils.g_int_levels(i_count).file_end_pos := ec_utils.g_file_tbl.COUNT;
1216 if ec_utils.g_direction = 'O'
1217 then
1218 ec_utils.g_stack_pointer(i_count).start_pos :=1;
1219 ec_utils.g_stack_pointer(i_count).end_pos :=0;
1220 end if;
1221 if EC_DEBUG.G_debug_level >= 3 then
1222 ec_debug.pl(3,'EC','ECE_INT_FILE_START','LEVEL',i_count,'POSITION',
1223 ec_utils.g_int_levels(i_count).file_start_pos);
1224 ec_debug.pl(3,'EC','ECE_INT_FILE_END','LEVEL',i_count,'POSITION',
1225 ec_utils.g_int_levels(i_count).file_end_pos);
1226 end if;
1227
1228 end loop;
1229
1230 /**
1231 Check for Seed Data. If not Found then , then do not process.
1232 **/
1233 if i_count = k
1234 then
1235 ec_debug.pl(0,'EC','ECE_SEED_DATA_MISSING','TRANSACTION_TYPE',i_transaction_type,'MAPID',i_map_id);
1236 /**
1237 Set the Retcode for the Concurrent Manager
1238 **/
1239 ec_utils.i_ret_code := 2;
1240 raise ec_utils.program_exit;
1241 end if;
1242
1243 /**
1244 Update the File_pointer for External Levels over here.
1245 **/
1246 if ec_utils.g_direction = 'I'
1247 then
1248 for i in 1..ec_utils.g_ext_levels.COUNT
1249 loop
1250 i_first_found := FALSE;
1251 i_last_found := FALSE;
1252 for j in 1..ec_utils.g_file_tbl.COUNT
1253 loop
1254 if ec_utils.g_file_tbl(j).external_level is null
1255 then
1256 ec_utils.i_ret_code := 2;
1257 ec_debug.pl(0,'EC','ECE_EXTERNAL_LEVELS_NULL','External Level is NULL');
1258 raise EC_UTILS.PROGRAM_EXIT;
1259 end if;
1260 if ec_utils.g_file_tbl(j).external_level = i
1261 then
1262 if NOT (i_first_found)
1263 then
1264 i_first_found := TRUE;
1265 ec_utils.g_ext_levels(i).file_start_pos := j;
1266 else
1267 if j < ec_utils.g_file_tbl.COUNT
1268 then
1269 if ec_utils.g_file_tbl(j+1).external_level <> i
1270 then
1271 i_last_found := TRUE;
1272 ec_utils.g_ext_levels(i).file_end_pos := j;
1273 exit;
1274 end if;
1275 else
1276 i_last_found := TRUE;
1277 ec_utils.g_ext_levels(i).file_end_pos := j;
1278 exit;
1279 end if;
1280 end if;
1281 end if;
1282 end loop;
1283
1284 if (i_first_found) and NOT (i_last_found)
1285 then
1286 ec_utils.g_ext_levels(i).file_end_pos := ec_utils.g_ext_levels(i).file_start_pos;
1287 end if;
1288 if EC_DEBUG.G_debug_level >= 3 then
1289 ec_debug.pl(3,'EC','ECE_EXT_FILE_START','LEVEL',i,'POSITION',ec_utils.g_ext_levels(i).file_start_pos);
1290 ec_debug.pl(3,'EC','ECE_EXT_FILE_END','LEVEL',i,'POSITION',ec_utils.g_ext_levels(i).file_end_pos);
1291 end if;
1292 ec_utils.g_stack_pointer(i).start_pos :=1;
1293 ec_utils.g_stack_pointer(i).end_pos :=0;
1294 end loop;
1295 end if;
1296
1297
1298 /**
1299 Load the Custom procedures and their Mappings
1300 **/
1301 load_procedure_definitions;
1302
1303 load_procedure_mappings
1304 (
1305 ec_utils.g_transaction_type
1306 );
1307
1308 if EC_DEBUG.G_debug_level >= 2 then
1309 ec_debug.pop('EC_EXECUTION_UTILS.LOAD_MAPPINGS');
1310 end if;
1311 EXCEPTION
1312 WHEN EC_UTILS.PROGRAM_EXIT then
1313 raise;
1314 WHEN OTHERS THEN
1315 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_EXECUTION_UTILS.LOAD_MAPPINGS');
1316 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1317 ec_utils.i_ret_code :=2;
1318 raise EC_UTILS.PROGRAM_EXIT;
1319 end load_mappings;
1320
1321 end ec_execution_utils;