[Home] [Help]
PACKAGE BODY: APPS.EC_UTILS
Source
1 package body ec_utils as
2 -- $Header: ECUTILB.pls 120.2.12000000.2 2007/02/08 14:05:55 cpeixoto ship $
3
4 NEW_VARIABLE CONSTANT pls_integer :=10;
5 ASSIGN_DEFAULT CONSTANT pls_integer :=20;
6 ASSIGN_PRE_DEFINED CONSTANT pls_integer :=30;
7 ASSIGN_NEXTVALUE CONSTANT pls_integer :=40;
8 IF_NULL_PRE_DEFINED CONSTANT pls_integer :=50;
9 INCREMENT_ONE CONSTANT pls_integer :=60;
10 IF_DEFAULT_PRE_DEFINED CONSTANT pls_integer :=70;
11 CUSTOM_PROCEDURE CONSTANT pls_integer :=80;
12 IF_NULL_DEFAULT CONSTANT pls_integer :=90;
13 ASSIGN_FUNCVAL CONSTANT pls_integer :=100;
14 MANDATORY_COLUMNS CONSTANT pls_integer :=110;
15 IF_DIFF_PRE_NEXT_DEFAULT CONSTANT pls_integer :=120;
16 IF_NULL_SKIP_DOC CONSTANT pls_integer :=130;
17 IF_XNULL_SET_YDEFAULT CONSTANT pls_integer :=140;
18 IF_NOT_NULL_DEFINED CONSTANT pls_integer :=150; /*Bug 1999536*/
19 APPEND_WHERECLAUSE CONSTANT pls_integer := 1000;
20 IF_NOTNULL_APPCLAUSE CONSTANT pls_integer := 1010;
21 BIND_VARIABLES CONSTANT pls_integer := 1030;
22 EXEC_PROCEDURES CONSTANT pls_integer := 1050;
23 IFXNULLEXEC_PROCEDURES CONSTANT pls_integer := 1080;
24 IFXNOTNULLEXEC_PROCEDURES CONSTANT pls_integer := 1090;
25 IFXPREEXEC_PROCEDURES CONSTANT pls_integer := 1100;
26 IFXCONSTEXEC_PROCEDURES CONSTANT pls_integer := 1110;
27
28 /* Bug 2422787
29 i_tmp_stage_data stage_data;---- Used for Stage 10 data only.
30 i_tmp2_stage_data stage_data;---- used for Stages other than 10.
31 i_stage_data stage_data;---- Temporary place holder for all Stages .
32 */
33
34 function find_variable
35 (
36 i_variable_level IN NUMBER,
37 i_variable_name IN varchar2,
38 i_stack_pos OUT NOCOPY NUMBER,
39 i_plsql_pos OUT NOCOPY NUMBER
40 ) return boolean
41 IS
42 cIn_String varchar2(1000) := UPPER(i_variable_name);
43 bFound BOOLEAN := FALSE;
44 hash_value pls_integer; -- Bug 2708573
45 hash_string varchar2(3200);
46 BEGIN
47 if EC_DEBUG.G_debug_level >= 2 then
48 ec_debug.PUSH('EC_UTILS.FIND_VARIABLE');
49 ec_debug.pl(3,'i_variable_level',i_variable_level);
50 ec_debug.pl(3,'i_variable_name',i_variable_name);
51 end if;
52 /**
53 For a given Level , find out the Start and End Position of the Stack. use this range
54 to loop through the Stack.
55 **/
56 /* Bug 2708573
57 for k in g_stack_pointer(i_variable_level).start_pos..g_stack_pointer(i_variable_level).end_pos
58 loop
59 if upper(g_stack(k).VARIABLE_NAME) = cIn_String
60 then
61 i_stack_pos := k;
62 i_plsql_pos := to_number(nvl(g_stack(k).variable_position,0));
63 bFound := TRUE;
64 exit;
65 end if;
66 end loop;
67 */
68 -- Bug 2708573
69 hash_string:=to_char(i_variable_level) || '-' ||i_variable_name;
70 hash_value:= dbms_utility.get_hash_value(hash_string,1,100000);
71 if ec_utils.g_stack_pos_tbl.exists(hash_value) then
72 i_stack_pos := ec_utils.g_stack_pos_tbl(hash_value);
73 i_plsql_pos := to_number(nvl(g_stack(i_stack_pos).variable_position,0));
74 bFound := TRUE;
75 end if;
76
77 if EC_DEBUG.G_debug_level >= 2 then
78 ec_debug.pl(3,'i_stack_pos',i_stack_pos);
79 ec_debug.pl(3,'i_plsql_pos',i_plsql_pos);
80 ec_debug.pl(3,'bFound',bFound);
81 ec_debug.pop('EC_UTILS.FIND_VARIABLE');
82 end if;
83 return bFound;
84 EXCEPTION
85 WHEN EC_UTILS.PROGRAM_EXIT then
86 raise;
87 WHEN OTHERS THEN
88 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.FIND_VARIABLE');
89 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
90 i_ret_code :=2;
91 raise EC_UTILS.PROGRAM_EXIT;
92 END find_variable;
93
94 /**
95 Executes a select string and returns the First Column from the select
96 clause as OUT parameter.
97 **/
98 procedure execute_string
99 (
100 cString in varchar2,
101 o_value OUT NOCOPY varchar2
102 )
103 is
104 cursor_handle pls_integer;
105 ret_query pls_integer;
106 m_value varchar2(20000);
107 m_success boolean;
108 error_position pls_integer;
109 begin
110 if EC_DEBUG.G_debug_level >= 2 then
111 ec_debug.push('EC_UTILS.EXECUTE_STRING');
112 ec_debug.pl(3,'cString',cString);
113 end if;
114
115 /* cursor_handle := dbms_sql.open_cursor;
116
117 BEGIN
118 dbms_sql.parse(cursor_handle,cString,dbms_sql.native);
119 EXCEPTION
120 WHEN OTHERS THEN
121 error_position := dbms_sql.last_error_position;
122 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXECUTE_STRING');
123 ece_error_handling_pvt.print_parse_error (error_position,cString);
124
125 if dbms_sql.is_open(cursor_handle)
126 then
127 dbms_sql.close_cursor(cursor_handle);
128 end if;
129 i_ret_code :=2;
130 raise EC_UTILS.PROGRAM_EXIT;
131 END;
132
133 dbms_sql.define_column(cursor_handle,1,m_value,20000);
134 ret_query := dbms_sql.execute_and_fetch(cursor_handle,m_success);
135 dbms_sql.column_value(cursor_handle,1,m_value);
136 dbms_sql.close_cursor(cursor_handle);
137 o_value := m_value;
138 */
139
140 /*Bug 1853627- Replaced the above dbms_sql with execute immediate statement */
141
142 EXECUTE IMMEDIATE cString
143 INTO o_value;
144 if EC_DEBUG.G_debug_level >= 2 then
145 ec_debug.pl(3,'o_value',o_value);
146 ec_debug.POP('EC_UTILS.EXECUTE_STRING');
147 end if;
148 exception
149 WHEN EC_UTILS.PROGRAM_EXIT then
150 raise;
151 WHEN OTHERS then
152 /* if DBMS_SQL.IS_OPEN(cursor_handle) then
153 dbms_sql.close_cursor(cursor_handle);
154 end if;
155 */
156 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXECUTE_STRING');
157 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
158 i_ret_code :=2;
159 raise EC_UTILS.PROGRAM_EXIT;
160 end execute_string;
161
162 procedure get_nextval_seq
163 (
164 i_seq_name IN VARCHAR2,
165 o_value OUT NOCOPY varchar2
166 )
167 is
168 cString varchar2(2000);
169 begin
170 if EC_DEBUG.G_debug_level >= 2 then
171 ec_debug.push('EC_UTILS.GET_NEXTVAL_SEQ');
172 ec_debug.pl(3,'i_seq_name',i_seq_name);
173 end if;
174 cString := 'select '||i_seq_name||'.NEXTVAL from dual';
175 execute_string
176 (
177 cString,
178 o_value
179 );
180 if EC_DEBUG.G_debug_level >= 2 then
181 ec_debug.pl(3,'o_value',o_value);
182 ec_debug.pop('EC_UTILS.GET_NEXTVAL_SEQ');
183 end if;
184 EXCEPTION
185 WHEN EC_UTILS.PROGRAM_EXIT then
186 raise;
187 WHEN OTHERS then
188 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.GET_NEXTVAL_SEQ');
189 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
190 i_ret_code := 2;
191 raise EC_UTILS.PROGRAM_EXIT;
192 end get_nextval_seq;
193
194 /**
195 Returns the Function Value by building a select Clause for the
196 Function name.
197 **/
198 procedure get_function_value
199 (
200 i_function_name IN VARCHAR2,
201 o_value OUT NOCOPY varchar2
202 )
203 is
204 cString varchar2(2000);
205 begin
206 if EC_DEBUG.G_debug_level >= 2 then
207 ec_debug.push('EC_UTILS.GET_FUNCTION_VALUE');
208 ec_debug.pl(3,'i_function_name',i_function_name);
209 end if;
210 if i_function_name = 'SYSDATE'
211 then
212 cString := 'to_char(SYSDATE,''YYYYMMDD HH24MISS'')';
213 else
214 cString := i_function_name;
215 end if;
216
217
218 cString := 'select '||cString||' from dual';
219 execute_string
220 (
221 cString,
222 o_value
223 );
224 if EC_DEBUG.G_debug_level >= 2 then
225 ec_debug.pl(3,'o_value',o_value);
226 ec_debug.pop('EC_UTILS.GET_FUNCTION_VALUE');
227 end if;
228 EXCEPTION
229 WHEN EC_UTILS.PROGRAM_EXIT then
230 raise;
231 WHEN OTHERS then
232 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.GET_FUNCTION_VALUE');
233 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
234 i_ret_code := 2;
235 raise EC_UTILS.PROGRAM_EXIT;
236 end get_function_value;
237
238 procedure dump_stack
239 is
240 m_count pls_integer := g_stack.COUNT;
241 begin
242 if EC_DEBUG.G_debug_level >= 2 then
243 ec_debug.PUSH('EC_UTILS.DUMP_STACK');
244 ec_debug.pl(3,'EC','ECE_STACK_DUMP',null);
245 end if;
246
247 for i in 1..m_count
248 loop
249 if EC_DEBUG.G_debug_level = 3 then
250 ec_debug.pl
251 (3,
252 g_stack(i).level||' '||
253 g_stack(i).variable_name||' ' ||
254 g_stack(i).variable_position||' '||
255 g_stack(i).variable_value||' '||
256 g_stack(i).data_type
257 );
258 end if;
259 end loop;
260 if EC_DEBUG.G_debug_level >= 2 then
261 ec_debug.POP('EC_UTILS.DUMP_STACK');
262 end if;
263 EXCEPTION
264 WHEN OTHERS then
265 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.DUMP_STACK');
266 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
267 i_ret_code := 2;
268 raise EC_UTILS.PROGRAM_EXIT;
269 end dump_stack;
270
271 procedure get_position_for_stack
272 (
273 i_level IN number
274 )
275 is
276 i_first_found BOOLEAN := FALSE;
277 i_last_found BOOLEAN := FALSE;
278 begin
279 if EC_DEBUG.G_debug_level >= 2 then
280 ec_debug.push('EC_UTILS.GET_POSITION_FOR_STACK');
281 ec_debug.pl(3,'i_level',i_level);
282 end if;
283
284 if ec_utils.g_stack.COUNT = 0
285 then
286 if EC_DEBUG.G_debug_level >= 2 then
287 ec_debug.pop('EC_UTILS.GET_POSITION_FOR_STACK');
288 end if;
289 return;
290 end if;
291 for i in 1..ec_utils.g_stack.COUNT
292 loop
293 if g_stack(i).level is null
294 then
295 ec_debug.pl(0,'EC','ECE_LEVEL_NULL','VARIABLE_NAME',ec_utils.g_stack(i).variable_name);
296 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.FIND_VARIABLE');
297 i_ret_code :=2;
298 raise EC_UTILS.PROGRAM_EXIT;
299 end if;
300
301 if g_stack(i).level = i_level
302 then
303 if NOT ( i_first_found )
304 then
305 i_first_found := TRUE;
306 g_stack_pointer(i_level).start_pos := i;
307 else
308 if i < g_stack.COUNT
309 then
310 if g_stack(i).level <> g_stack(i+1).level
311 then
312 i_last_found := TRUE;
313 g_stack_pointer(i_level).end_pos := i;
314 exit;
315 end if;
316 elsif i = g_stack.COUNT
317 then
318 i_last_found := TRUE;
319 g_stack_pointer(i_level).end_pos := i;
320 exit;
321 else
322 null;
323 end if;
324 end if;
325 end if;
326 end loop;
327
328 if ( i_first_found) and NOT (i_last_found)
329 then
330 g_stack_pointer(i_level).end_pos := g_stack_pointer(i_level).start_pos;
331 end if;
332 if EC_DEBUG.G_debug_level >= 2 then
333 ec_debug.pl(3,'Stack Pointer('||i_level||') Start Position',g_stack_pointer(i_level).start_pos);
334 ec_debug.pl(3,'Stack Pointer('||i_level||') End Position',g_stack_pointer(i_level).end_pos);
335 ec_debug.pop('EC_UTILS.GET_POSITION_FOR_STACK');
336 end if;
337 EXCEPTION
338 WHEN EC_UTILS.PROGRAM_EXIT then
339 raise;
340 WHEN OTHERS then
341 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.GET_POSITION_FOR_STACK');
342 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
343 i_ret_code := 2;
344 raise EC_UTILS.PROGRAM_EXIT;
345 end get_position_for_stack;
346 /**
347 Executes the Data from the ECE_TRAN_STAGE_DATA for a given Stage and Level.
348 **/
349 procedure execute_stage_data
350 (
351 i_stage IN number,
352 i_level IN number
353 )
354 is
355 begin
356 if EC_DEBUG.G_debug_level >= 2 then
357 ec_debug.push('EC_UTILS.EXECUTE_STAGE_DATA');
358 ec_debug.pl(3,'i_stage',i_stage);
359 ec_debug.pl(3,'i_level',i_level);
360 end if;
361 /* Bug 2708573
362 if i_stage = 10
363 then
364 i_stage_data := i_tmp_stage_data;
365 else
366 i_stage_data := i_tmp2_stage_data;
367 end if;
368 */
369
370 FOR i in 1..i_stage_data.COUNT
371 loop
372 if (
373 i_stage_data(i).stage = i_stage
374 and i_stage_data(i).level = i_level
375 and i_stage_data(i).action_type <> 110
376 )
377 then
378 if EC_DEBUG.G_debug_level = 3 then
379 ec_debug.pl(3,'i_transtage_id',i_stage_data(i).transtage_id);
380 ec_debug.pl(3,'i_seq_number',i_stage_data(i).seq_number);
381 ec_debug.pl(3,'i_action_type',i_stage_data(i).action_type);
382 ec_debug.pl(3,'i_variable_level',i_stage_data(i).variable_level);
383 ec_debug.pl(3,'i_variable_name',i_stage_data(i).variable_name);
384 ec_debug.pl(3,'i_variable_value',i_stage_data(i).variable_value);
385 ec_debug.pl(3,'i_default_value',i_stage_data(i).default_value);
386 ec_debug.pl(3,'i_previous_variable_level',i_stage_data(i).previous_variable_level);
387 ec_debug.pl(3,'i_previous_variable_name',i_stage_data(i).previous_variable_name);
388 ec_debug.pl(3,'i_next_variable_name',i_stage_data(i).next_variable_name);
389 ec_debug.pl(3,'i_sequence_name',i_stage_data(i).sequence_name);
390 ec_debug.pl(3,'i_custom_procedure_name',i_stage_data(i).custom_procedure_name);
391 ec_debug.pl(3,'i_data_type',i_stage_data(i).data_type);
392 ec_debug.pl(3,'i_function_name',i_stage_data(i).function_name);
393 ec_debug.pl(3,'i_where_clause',i_stage_data(i).clause);
394 end if;
395 if i_stage_data(i).action_type = NEW_VARIABLE
396 then
397 create_new_variable
398 (
399 i_stage_data(i).variable_level,
400 i_stage_data(i).variable_name,
401 i_stage_data(i).variable_value,
402 i_stage_data(i).data_type
403 );
404
405 elsif i_stage_data(i).action_type = ASSIGN_DEFAULT
406 then
407 assign_default_to_variables
408 (
409 i_stage_data(i).variable_level,
410 i_stage_data(i).variable_name,
411 i_stage_data(i).default_value
412 );
413 elsif i_stage_data(i).action_type = ASSIGN_PRE_DEFINED
414 then
415 assign_pre_defined_variables
416 (
420 i_stage_data(i).previous_variable_name
417 i_stage_data(i).variable_level,
418 i_stage_data(i).variable_name,
419 i_stage_data(i).previous_variable_level,
421 );
422 elsif i_stage_data(i).action_type = ASSIGN_NEXTVALUE
423 then
424 assign_nextval_from_sequence
425 (
426 i_stage_data(i).variable_level,
427 i_stage_data(i).variable_name,
428 i_stage_data(i).sequence_name
429 );
430 elsif i_stage_data(i).action_type = ASSIGN_FUNCVAL
431 then
432 assign_function_value
433 (
434 i_stage_data(i).variable_level,
435 i_stage_data(i).variable_name,
436 i_stage_data(i).function_name
437 );
438 elsif i_stage_data(i).action_type = INCREMENT_ONE
439 then
440 increment_by_one
441 (
442 i_stage_data(i).variable_level,
443 i_stage_data(i).variable_name
444 );
445 elsif i_stage_data(i).action_type = IF_NULL_PRE_DEFINED
446 then
447 if_null_pre_defined_variable
448 (
449 i_stage_data(i).variable_level,
450 i_stage_data(i).variable_name,
451 i_stage_data(i).previous_variable_level,
452 i_stage_data(i).previous_variable_name
453 );
454 elsif i_stage_data(i).action_type = IF_DEFAULT_PRE_DEFINED
455 then
456 if_default_pre_defined_var
457 (
458 i_stage_data(i).variable_level,
459 i_stage_data(i).variable_name,
460 i_stage_data(i).previous_variable_level,
461 i_stage_data(i).previous_variable_name,
462 i_stage_data(i).default_value
463 );
464 elsif i_stage_data(i).action_type = CUSTOM_PROCEDURE
465 then
466 execute_proc
467 (
468 i_stage_data(i).transtage_id,
469 i_stage_data(i).custom_procedure_name
470 );
471
472 elsif i_stage_data(i).action_type = IF_NULL_DEFAULT
473 then
474 if_null_equal_default_value
475 (
476 i_stage_data(i).variable_level,
477 i_stage_data(i).variable_name,
478 i_stage_data(i).default_value
479 );
480 elsif i_stage_data(i).action_type = MANDATORY_COLUMNS
481 then
482 create_mandatory_columns
483 (
484 i_stage_data(i).variable_level,
485 i_stage_data(i).previous_variable_level,
486 i_stage_data(i).variable_name,
487 i_stage_data(i).default_value,
488 i_stage_data(i).data_type,
489 i_stage_data(i).function_name
490 );
491 elsif i_stage_data(i).action_type = IF_DIFF_PRE_NEXT_DEFAULT
492 then
493 if_diff_pre_next_then_default
494 (
495 i_stage_data(i).variable_level,
496 i_stage_data(i).variable_name,
497 i_stage_data(i).previous_variable_level,
498 i_stage_data(i).previous_variable_name,
499 i_stage_data(i).next_variable_name,
500 i_stage_data(i).default_value
501 );
502 elsif i_stage_data(i).action_type = IF_NULL_SKIP_DOC
503 then
504 if_null_skip_document
505 (
506 i_stage_data(i).variable_level,
507 i_stage_data(i).variable_name
508 );
509 elsif i_stage_data(i).action_type = IF_XNULL_SET_YDEFAULT
510 then
511 if_xnull_setydefault
512 (
513 i_stage_data(i).variable_level,
514 i_stage_data(i).variable_name,
515 i_stage_data(i).previous_variable_level,
516 i_stage_data(i).previous_variable_name,
517 i_stage_data(i).default_value
518 );
519
520 elsif i_stage_data(i).action_type = IF_NOT_NULL_DEFINED /*Bug 1999536*/
521 then
522 if_not_null_defined_variable
523 (
524 i_stage_data(i).variable_level,
525 i_stage_data(i).variable_name,
526 i_stage_data(i).previous_variable_level,
527 i_stage_data(i).previous_variable_name
528 );
529
530 elsif i_stage_data(i).action_type = APPEND_WHERECLAUSE
531 then
532 append_clause
533 (
534 i_stage_data(i).level,
535 i_stage_data(i).clause
536 );
537 elsif i_stage_data(i).action_type = IF_NOTNULL_APPCLAUSE
538 then
539 if_notnull_append_clause
540 (
541 i_stage_data(i).level,
542 i_stage_data(i).variable_level,
543 i_stage_data(i).variable_name,
544 i_stage_data(i).clause
545 );
546 elsif i_stage_data(i).action_type = BIND_VARIABLES
547 then
548 bind_variables_for_view
549 (
550 i_stage_data(i).variable_name,
551 i_stage_data(i).previous_variable_level,
552 i_stage_data(i).previous_variable_name
553 );
554 elsif i_stage_data(i).action_type = EXEC_PROCEDURES
555 then
559 i_stage_data(i).custom_procedure_name
556 execute_proc
557 (
558 i_stage_data(i).transtage_id,
560 );
561
562 elsif i_stage_data(i).action_type = IFXNULLEXEC_PROCEDURES
563 then
564 ifxnull_execute_proc
565 (
566 i_stage_data(i).variable_level,
567 i_stage_data(i).variable_name,
568 i_stage_data(i).transtage_id,
569 i_stage_data(i).custom_procedure_name
570 );
571
572 elsif i_stage_data(i).action_type = IFXNOTNULLEXEC_PROCEDURES
573 then
574 ifxnotnull_execute_proc
575 (
576 i_stage_data(i).variable_level,
577 i_stage_data(i).variable_name,
578 i_stage_data(i).transtage_id,
579 i_stage_data(i).custom_procedure_name
580 );
581 elsif i_stage_data(i).action_type = IFXPREEXEC_PROCEDURES
582 then
583 ifxpre_execute_proc
584 (
585 i_stage_data(i).variable_level,
586 i_stage_data(i).variable_name,
587 i_stage_data(i).previous_variable_level,
588 i_stage_data(i).previous_variable_name,
589 i_stage_data(i).transtage_id,
590 i_stage_data(i).custom_procedure_name
591 );
592 elsif i_stage_data(i).action_type = IFXCONSTEXEC_PROCEDURES
593 then
594 ifxconst_execute_proc
595 (
596 i_stage_data(i).variable_level,
597 i_stage_data(i).variable_name,
598 i_stage_data(i).default_value,
599 i_stage_data(i).transtage_id,
600 i_stage_data(i).custom_procedure_name
601 );
602 end if;
603
604 end if;
605 end loop;
606
607 /* Bug 1853627 - Added the following condition which suppress the call to the procedure
608 ** dump_stack when the debug_mode is less than 2
609 */
610
611 if (ec_debug.G_debug_level >=2) then
612 dump_stack;
613 end if;
614 /**
615 If Stage = 10 , get the stack pointer location.
616 **/
617 if i_stage = 10 and
618 i_level = 0
619 then
620 if g_direction = 'I'
621 then
622 for i in 0..g_ext_levels.COUNT
623 loop
624 get_position_for_stack(i);
625 end loop;
626 else
627 for i in 0..g_int_levels.COUNT
628 loop
629 get_position_for_stack(i);
630 end loop;
631 end if;
632 end if;
633 if EC_DEBUG.G_debug_level >= 2 then
634 ec_debug.pop('EC_UTILS.EXECUTE_STAGE_DATA');
635 end if;
636 EXCEPTION
637 WHEN EC_UTILS.PROGRAM_EXIT then
638 raise;
639 WHEN OTHERS THEN
640 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXECUTE_STAGE_DATA');
641 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
642 i_ret_code := 2;
643 raise EC_UTILS.PROGRAM_EXIT;
644 end execute_stage_data;
645
646
647 /**
648 Retrieves the Data from the ECE_TRAN_STAGE_DATA for a given Stage and Level.
649 **/
650 procedure get_tran_stage_data
651 (
652 i_transaction_type IN varchar2,
653 i_map_id IN number
654 )
655 is
656 cursor stage_data
657 (
658 p_transaction_type IN varchar2,
659 p_map_id IN number
660 ) is
661 select transtage_id,
662 transaction_level,
663 stage,
664 seq_number,
665 action_type,
666 variable_level,
667 upper(variable_name) variable_name,
668 variable_value,
669 default_value,
670 previous_variable_level,
671 upper(previous_variable_name) previous_variable_name,
672 upper(next_variable_name) next_variable_name,
673 upper(sequence_name) sequence_name,
674 upper(custom_procedure_name) custom_procedure_name,
675 data_type,
676 upper(function_name) function_name,
677 where_clause
678 from ece_tran_stage_data
679 where transaction_type = p_transaction_type
680 and map_id = p_map_id
681 order by stage,transaction_level,seq_number;
682
683 i_counter pls_integer :=0;
684 begin
685 if EC_DEBUG.G_debug_level >= 2 then
686 ec_debug.push('EC_UTILS.GET_TRAN_STAGE_DATA');
687 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
688 ec_debug.pl(3,'EC','ECE_TRAN_STAGE_DATA','TRANSACTION_TYPE',i_transaction_type);
689 end if;
690
691 /* Bug 2019253 Clearing the Staging tables */
692
693 i_tmp_stage_data.DELETE;
694 i_tmp2_stage_data.DELETE;
695 i_stage_data.DELETE;
696
697 for get_stage_data in stage_data
698 (
699 p_transaction_type => i_transaction_type,
700 p_map_id => i_map_id
701 )
702 loop
706 g_stage_data(i_counter).stage := get_stage_data.stage;
703 i_counter := g_stage_data.COUNT + 1;
704 g_stage_data(i_counter).transtage_id := get_stage_data.transtage_id;
705 g_stage_data(i_counter).level := get_stage_data.transaction_level;
707 g_stage_data(i_counter).seq_number := get_stage_data.seq_number;
708 g_stage_data(i_counter).action_type := get_stage_data.action_type;
709 g_stage_data(i_counter).variable_level := get_stage_data.variable_level;
710 g_stage_data(i_counter).variable_name := get_stage_data.variable_name;
711 g_stage_data(i_counter).variable_value := get_stage_data.variable_value;
712 g_stage_data(i_counter).default_value := get_stage_data.default_value;
713 g_stage_data(i_counter).previous_variable_level := get_stage_data.previous_variable_level;
714 g_stage_data(i_counter).previous_variable_name := get_stage_data.previous_variable_name;
715 g_stage_data(i_counter).next_variable_name := get_stage_data.next_variable_name;
716 g_stage_data(i_counter).sequence_name := get_stage_data.sequence_name;
717 g_stage_data(i_counter).custom_procedure_name := get_stage_data.custom_procedure_name;
718 g_stage_data(i_counter).data_type := get_stage_data.data_type;
719 g_stage_data(i_counter).function_name := get_stage_data.function_name;
720 g_stage_data(i_counter).clause := get_stage_data.where_clause;
721 if EC_DEBUG.G_debug_level >= 3 then
722 ec_debug.pl
723 (3,
724 g_stage_data(i_counter).transtage_id||' '||
725 g_stage_data(i_counter).level||' '||
726 g_stage_data(i_counter).stage||' '||
727 g_stage_data(i_counter).seq_number||' '||
728 g_stage_data(i_counter).action_type||' '||
729 g_stage_data(i_counter).variable_level||' '||
730 g_stage_data(i_counter).variable_name||' '||
731 g_stage_data(i_counter).variable_value||' '||
732 g_stage_data(i_counter).default_value||' '||
733 g_stage_data(i_counter).previous_variable_level||' '||
734 g_stage_data(i_counter).previous_variable_name||' '||
735 g_stage_data(i_counter).next_variable_name||' '||
736 g_stage_data(i_counter).custom_procedure_name||' '||
737 g_stage_data(i_counter).data_type||' '||
738 g_stage_data(i_counter).function_name||' '||
739 g_stage_data(i_counter).clause
740 );
741 end if;
742
743 end loop;
744 if EC_DEBUG.G_debug_level >= 2 then
745 ec_debug.pop('EC_UTILS.GET_TRAN_STAGE_DATA');
746 end if;
747 EXCEPTION
748 WHEN OTHERS THEN
749 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.GET_TRAN_STAGE_DATA');
750 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
751 i_ret_code := 2;
752 raise EC_UTILS.PROGRAM_EXIT;
753 end get_tran_stage_data;
754
755 procedure sort_stage_data
756 is
757 j pls_integer :=0;
758 k pls_integer :=0;
759 l pls_integer :=0;
760 i_tmp1_stage_data ec_utils.stage_data;
761 begin
762 if EC_DEBUG.G_debug_level >= 2 then
763 ec_debug.PUSH('EC_UTILS.SORT_STAGE_DATA');
764 end if;
765 /**
766 Store the Stage 10 data in i_tmp1_stage_data tbl.
767 **/
768 for i in 1..g_stage_data.COUNT
769 loop
770 if g_stage_data(i).stage = 10
771 then
772 j := i_tmp1_stage_data.COUNT +1;
773 i_tmp1_stage_data(j).transtage_id := g_stage_data(i).transtage_id;
774 i_tmp1_stage_data(j).level := g_stage_data(i).level;
775 i_tmp1_stage_data(j).stage := g_stage_data(i).stage;
776 i_tmp1_stage_data(j).seq_number := g_stage_data(i).seq_number;
777 i_tmp1_stage_data(j).action_type := g_stage_data(i).action_type;
778 i_tmp1_stage_data(j).variable_level := g_stage_data(i).variable_level;
779 i_tmp1_stage_data(j).variable_name := g_stage_data(i).variable_name;
780 i_tmp1_stage_data(j).variable_value := g_stage_data(i).variable_value;
781 i_tmp1_stage_data(j).default_value := g_stage_data(i).default_value;
782 i_tmp1_stage_data(j).previous_variable_level := g_stage_data(i).previous_variable_level;
783 i_tmp1_stage_data(j).previous_variable_name := g_stage_data(i).previous_variable_name;
784 i_tmp1_stage_data(j).next_variable_name := g_stage_data(i).next_variable_name;
785 i_tmp1_stage_data(j).sequence_name := g_stage_data(i).sequence_name;
786 i_tmp1_stage_data(j).custom_procedure_name := g_stage_data(i).custom_procedure_name;
787 i_tmp1_stage_data(j).data_type := g_stage_data(i).data_type;
788 i_tmp1_stage_data(j).function_name := g_stage_data(i).function_name;
789 i_tmp1_stage_data(j).clause := g_stage_data(i).clause;
790 else
791 k := i_tmp2_stage_data.COUNT +1;
792 i_tmp2_stage_data(k).transtage_id := g_stage_data(i).transtage_id;
793 i_tmp2_stage_data(k).level := g_stage_data(i).level;
794 i_tmp2_stage_data(k).stage := g_stage_data(i).stage;
795 i_tmp2_stage_data(k).seq_number := g_stage_data(i).seq_number;
796 i_tmp2_stage_data(k).action_type := g_stage_data(i).action_type;
797 i_tmp2_stage_data(k).variable_level := g_stage_data(i).variable_level;
798 i_tmp2_stage_data(k).variable_name := g_stage_data(i).variable_name;
799 i_tmp2_stage_data(k).variable_value := g_stage_data(i).variable_value;
800 i_tmp2_stage_data(k).default_value := g_stage_data(i).default_value;
801 i_tmp2_stage_data(k).previous_variable_level := g_stage_data(i).previous_variable_level;
802 i_tmp2_stage_data(k).previous_variable_name := g_stage_data(i).previous_variable_name;
803 i_tmp2_stage_data(k).next_variable_name := g_stage_data(i).next_variable_name;
804 i_tmp2_stage_data(k).sequence_name := g_stage_data(i).sequence_name;
805 i_tmp2_stage_data(k).custom_procedure_name := g_stage_data(i).custom_procedure_name;
809 end if;
806 i_tmp2_stage_data(k).data_type := g_stage_data(i).data_type;
807 i_tmp2_stage_data(k).function_name := g_stage_data(i).function_name;
808 i_tmp2_stage_data(k).clause := g_stage_data(i).clause;
810 end loop;
811 if EC_DEBUG.G_debug_level >= 3 then
812 ec_debug.pl(3,'Separation of Data into Stage 10 and rest done. Sorting to proceed');
813 end if;
814
815 /**
816 Depending on Inbound or Outbound transaction sort the
817 Stage 10 data based on variable_level.If direction not set then
818 default to Internal representation.
819 **/
820 if g_direction = 'I'
821 then
822 /**
823 Sorts only entries with Action Type = 10 i.e. Create_new_variable
824 **/
825 for j in 0..g_ext_levels.COUNT
826 loop
827 for k in 1..i_tmp1_stage_data.COUNT
828 loop
829 if (
830 i_tmp1_stage_data(k).action_type = 10 and
831 i_tmp1_stage_data(k).variable_level = j
832 )
833 then
834 l := i_tmp_stage_data.COUNT +1;
835 i_tmp_stage_data(l).transtage_id := i_tmp1_stage_data(k).transtage_id;
836 i_tmp_stage_data(l).level := i_tmp1_stage_data(k).level;
837 i_tmp_stage_data(l).stage := i_tmp1_stage_data(k).stage;
838 i_tmp_stage_data(l).seq_number := i_tmp1_stage_data(k).seq_number;
839 i_tmp_stage_data(l).action_type := i_tmp1_stage_data(k).action_type;
840 i_tmp_stage_data(l).variable_level := i_tmp1_stage_data(k).variable_level;
841 i_tmp_stage_data(l).variable_name := i_tmp1_stage_data(k).variable_name;
842 i_tmp_stage_data(l).variable_value := i_tmp1_stage_data(k).variable_value;
843 i_tmp_stage_data(l).default_value := i_tmp1_stage_data(k).default_value;
844 i_tmp_stage_data(l).previous_variable_level := i_tmp1_stage_data(k).previous_variable_level;
845 i_tmp_stage_data(l).previous_variable_name := i_tmp1_stage_data(k).previous_variable_name;
846 i_tmp_stage_data(l).next_variable_name := i_tmp1_stage_data(k).next_variable_name;
847 i_tmp_stage_data(l).sequence_name := i_tmp1_stage_data(k).sequence_name;
848 i_tmp_stage_data(l).custom_procedure_name := i_tmp1_stage_data(k).custom_procedure_name;
849 i_tmp_stage_data(l).data_type := i_tmp1_stage_data(k).data_type;
850 i_tmp_stage_data(l).function_name := i_tmp1_stage_data(k).function_name;
851 i_tmp_stage_data(l).clause := i_tmp1_stage_data(k).clause;
852 end if;
853 end loop;
854 end loop;
855 /**
856 Put all other action Types .
857 **/
858 for k in 1..i_tmp1_stage_data.COUNT
859 loop
860 if i_tmp1_stage_data(k).action_type <> 10
861 then
862 l := i_tmp_stage_data.COUNT +1;
863 i_tmp_stage_data(l).transtage_id := i_tmp1_stage_data(k).transtage_id;
864 i_tmp_stage_data(l).level := i_tmp1_stage_data(k).level;
865 i_tmp_stage_data(l).stage := i_tmp1_stage_data(k).stage;
866 i_tmp_stage_data(l).seq_number := i_tmp1_stage_data(k).seq_number;
867 i_tmp_stage_data(l).action_type := i_tmp1_stage_data(k).action_type;
868 i_tmp_stage_data(l).variable_level := i_tmp1_stage_data(k).variable_level;
869 i_tmp_stage_data(l).variable_name := i_tmp1_stage_data(k).variable_name;
870 i_tmp_stage_data(l).variable_value := i_tmp1_stage_data(k).variable_value;
871 i_tmp_stage_data(l).default_value := i_tmp1_stage_data(k).default_value;
872 i_tmp_stage_data(l).previous_variable_level := i_tmp1_stage_data(k).previous_variable_level;
873 i_tmp_stage_data(l).previous_variable_name := i_tmp1_stage_data(k).previous_variable_name;
874 i_tmp_stage_data(l).next_variable_name := i_tmp1_stage_data(k).next_variable_name;
875 i_tmp_stage_data(l).sequence_name := i_tmp1_stage_data(k).sequence_name;
876 i_tmp_stage_data(l).custom_procedure_name := i_tmp1_stage_data(k).custom_procedure_name;
877 i_tmp_stage_data(l).data_type := i_tmp1_stage_data(k).data_type;
878 i_tmp_stage_data(l).function_name := i_tmp1_stage_data(k).function_name;
879 i_tmp_stage_data(l).clause := i_tmp1_stage_data(k).clause;
880 end if;
881 end loop;
882 else
883 for j in 0..g_int_levels.COUNT
884 loop
885 for k in 1..i_tmp1_stage_data.COUNT
886 loop
887 if (
888 i_tmp1_stage_data(k).action_type = 10 and
889 i_tmp1_stage_data(k).variable_level = j
890 )
891 then
892 l := i_tmp_stage_data.COUNT + 1;
893 i_tmp_stage_data(l).transtage_id := i_tmp1_stage_data(k).transtage_id;
894 i_tmp_stage_data(l).level := i_tmp1_stage_data(k).level;
895 i_tmp_stage_data(l).stage := i_tmp1_stage_data(k).stage;
896 i_tmp_stage_data(l).seq_number := i_tmp1_stage_data(k).seq_number;
897 i_tmp_stage_data(l).action_type := i_tmp1_stage_data(k).action_type;
898 i_tmp_stage_data(l).variable_level := i_tmp1_stage_data(k).variable_level;
899 i_tmp_stage_data(l).variable_name := i_tmp1_stage_data(k).variable_name;
900 i_tmp_stage_data(l).variable_value := i_tmp1_stage_data(k).variable_value;
901 i_tmp_stage_data(l).default_value := i_tmp1_stage_data(k).default_value;
902 i_tmp_stage_data(l).previous_variable_level := i_tmp1_stage_data(k).previous_variable_level;
903 i_tmp_stage_data(l).previous_variable_name := i_tmp1_stage_data(k).previous_variable_name;
904 i_tmp_stage_data(l).next_variable_name := i_tmp1_stage_data(k).next_variable_name;
905 i_tmp_stage_data(l).sequence_name := i_tmp1_stage_data(k).sequence_name;
906 i_tmp_stage_data(l).custom_procedure_name := i_tmp1_stage_data(k).custom_procedure_name;
907 i_tmp_stage_data(l).data_type := i_tmp1_stage_data(k).data_type;
911 end loop;
908 i_tmp_stage_data(l).function_name := i_tmp1_stage_data(k).function_name;
909 i_tmp_stage_data(l).clause := i_tmp1_stage_data(k).clause;
910 end if;
912 end loop;
913 /**
914 Put the rest of the Data now
915 **/
916 for k in 1..i_tmp1_stage_data.COUNT
917 loop
918 if i_tmp1_stage_data(k).action_type <> 10
919 then
920 l := i_tmp_stage_data.COUNT + 1;
921 i_tmp_stage_data(l).transtage_id := i_tmp1_stage_data(k).transtage_id;
922 i_tmp_stage_data(l).level := i_tmp1_stage_data(k).level;
923 i_tmp_stage_data(l).stage := i_tmp1_stage_data(k).stage;
924 i_tmp_stage_data(l).seq_number := i_tmp1_stage_data(k).seq_number;
925 i_tmp_stage_data(l).action_type := i_tmp1_stage_data(k).action_type;
926 i_tmp_stage_data(l).variable_level := i_tmp1_stage_data(k).variable_level;
927 i_tmp_stage_data(l).variable_name := i_tmp1_stage_data(k).variable_name;
928 i_tmp_stage_data(l).variable_value := i_tmp1_stage_data(k).variable_value;
929 i_tmp_stage_data(l).default_value := i_tmp1_stage_data(k).default_value;
930 i_tmp_stage_data(l).previous_variable_level := i_tmp1_stage_data(k).previous_variable_level;
931 i_tmp_stage_data(l).previous_variable_name := i_tmp1_stage_data(k).previous_variable_name;
932 i_tmp_stage_data(l).next_variable_name := i_tmp1_stage_data(k).next_variable_name;
933 i_tmp_stage_data(l).sequence_name := i_tmp1_stage_data(k).sequence_name;
934 i_tmp_stage_data(l).custom_procedure_name := i_tmp1_stage_data(k).custom_procedure_name;
935 i_tmp_stage_data(l).data_type := i_tmp1_stage_data(k).data_type;
936 i_tmp_stage_data(l).function_name := i_tmp1_stage_data(k).function_name;
937 i_tmp_stage_data(l).clause := i_tmp1_stage_data(k).clause;
938 end if;
939 end loop;
940 end if;
941 if EC_DEBUG.G_debug_level >= 2 then
942 ec_debug.pl(3,'Stage 10 data after sorting is as follows :');
943 for i in 1..i_tmp_stage_data.COUNT
944 loop
945 ec_debug.pl
946 (3,
947 i_tmp_stage_data(i).transtage_id||' '||
948 i_tmp_stage_data(i).level||' '||
949 i_tmp_stage_data(i).stage||' '||
950 i_tmp_stage_data(i).seq_number||' '||
951 i_tmp_stage_data(i).action_type||' '||
952 i_tmp_stage_data(i).variable_level||' '||
953 i_tmp_stage_data(i).variable_name||' '||
954 i_tmp_stage_data(i).variable_value||' '||
955 i_tmp_stage_data(i).default_value||' '||
956 i_tmp_stage_data(i).previous_variable_level||' '||
957 i_tmp_stage_data(i).previous_variable_name||' '||
958 i_tmp_stage_data(i).next_variable_name||' '||
959 i_tmp_stage_data(i).custom_procedure_name||' '||
960 i_tmp_stage_data(i).data_type||' '||
961 i_tmp_stage_data(i).function_name||' '||
962 i_tmp_stage_data(i).clause
963 );
964 end loop;
965 ec_debug.pl(3,'Data for Stages other then 10 is as follows :');
966 for i in 1..i_tmp2_stage_data.COUNT
967 loop
968 ec_debug.pl
969 (3,
970 i_tmp2_stage_data(i).transtage_id||' '||
971 i_tmp2_stage_data(i).level||' '||
972 i_tmp2_stage_data(i).stage||' '||
973 i_tmp2_stage_data(i).seq_number||' '||
974 i_tmp2_stage_data(i).action_type||' '||
975 i_tmp2_stage_data(i).variable_level||' '||
976 i_tmp2_stage_data(i).variable_name||' '||
977 i_tmp2_stage_data(i).variable_value||' '||
978 i_tmp2_stage_data(i).default_value||' '||
979 i_tmp2_stage_data(i).previous_variable_level||' '||
980 i_tmp2_stage_data(i).previous_variable_name||' '||
981 i_tmp2_stage_data(i).next_variable_name||' '||
982 i_tmp2_stage_data(i).custom_procedure_name||' '||
983 i_tmp2_stage_data(i).data_type||' '||
984 i_tmp2_stage_data(i).function_name||' '||
985 i_tmp2_stage_data(i).clause
986 );
987 end loop;
988 ec_debug.POP('EC_UTILS.SORT_STAGE_DATA');
989 end if;
990 EXCEPTION
991 WHEN OTHERS THEN
992 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.SORT_STAGE_DATA');
993 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
994 i_ret_code := 2;
995 raise EC_UTILS.PROGRAM_EXIT;
996 end sort_stage_data;
997
998 procedure find_pos
999 (
1000 i_level IN number,
1001 i_search_text IN varchar2,
1002 o_pos OUT NOCOPY NUMBER,
1003 i_required IN BOOLEAN DEFAULT TRUE
1004 )
1005 IS
1006 cIn_String varchar2(1000) := UPPER(i_search_text);
1007 bFound BOOLEAN := FALSE;
1008 POS_NOT_FOUND EXCEPTION;
1009 hash_value pls_integer; -- Bug 2617428
1010 hash_string varchar2(3200);
1011 tbl_pos pls_integer;
1012
1013 BEGIN
1014 if EC_DEBUG.G_debug_level >= 2 then
1015 ec_debug.PUSH('EC_UTILS.FIND_POS');
1016 ec_debug.pl(3,'i_level',i_level);
1017 ec_debug.pl(3,'i_search_text',i_search_text);
1018 end if;
1019
1020 if g_direction = 'I'
1021 then
1022 /* Bug 2617428
1023 for i in g_ext_levels(i_level).file_start_pos..g_ext_levels(i_level).file_end_pos
1024 loop
1025 if upper(g_file_tbl(i).interface_column_name) = cIn_String
1026 then
1027 o_pos := i;
1028 bFound := TRUE;
1029 exit;
1030 end if;
1031 end loop;
1032 */
1033 -- Bug 2617428
1034 hash_string:=to_char(i_level) || '-' ||cIn_String;
1038 o_pos := ec_utils.g_col_pos_tbl_1(hash_value).value;
1035 hash_value:= dbms_utility.get_hash_value(hash_string,1,8192);
1036 if ec_utils.g_col_pos_tbl_1.exists(hash_value) then
1037 if ec_utils.g_col_pos_tbl_1(hash_value).occr = 1 then
1039 bFound := TRUE;
1040 else
1041 -- Bug 2834366
1042 -- Added the following to prevent hash collision.
1043 tbl_pos := ec_utils.g_col_pos_tbl_1(hash_value).start_pos;
1044 while tbl_pos<=ec_utils.g_col_pos_tbl_2.LAST
1045 loop
1046 if ec_utils.g_col_pos_tbl_2(tbl_pos) = hash_value then
1047 if upper(g_file_tbl(tbl_pos).interface_column_name) = cIn_String and
1048 g_file_tbl(tbl_pos).external_level=i_level then
1049 o_pos := tbl_pos;
1050 bFound := TRUE;
1051 exit;
1052 end if;
1053 end if;
1054 tbl_pos:=ec_utils.g_col_pos_tbl_2.NEXT(tbl_pos);
1055 end loop;
1056 end if;
1057 end if;
1058 else
1059 for i in g_int_levels(i_level).file_start_pos..g_int_levels(i_level).file_end_pos
1060 loop
1061 if upper(g_file_tbl(i).interface_column_name) = cIn_String
1062 then
1063 o_pos := i;
1064 bFound := TRUE;
1065 exit;
1066 end if;
1067 end loop;
1068 end if;
1069
1070 if not bFound
1071 then
1072 if (i_required)
1073 then
1074 raise POS_NOT_FOUND;
1075 else
1076 o_pos := NULL;
1077 end if;
1078 end if;
1079 if EC_DEBUG.G_debug_level >= 2 then
1080 ec_debug.pl(3,'o_pos',o_pos);
1081 ec_debug.POP('EC_UTILS.FIND_POS');
1082 end if;
1083 EXCEPTION
1084 WHEN POS_NOT_FOUND THEN
1085 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME',cIn_String);
1086 ec_debug.POP('EC_UTILS.FIND_POS');
1087 i_ret_code := 2;
1088 raise EC_UTILS.PROGRAM_EXIT;
1089 WHEN OTHERS THEN
1090 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.FIND_POS');
1091 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1092 i_ret_code := 2;
1093 raise EC_UTILS.PROGRAM_EXIT;
1094 END find_pos;
1095
1096 /**
1097 Overloaded Function to search for a column on the Pl/SQL table , from a Given level to
1098 an Lower Level.
1099 **/
1100 procedure find_pos
1101 (
1102 i_from_level IN number,
1103 i_to_level IN number,
1104 i_search_text IN varchar2,
1105 o_pos OUT NOCOPY NUMBER,
1106 i_required IN BOOLEAN DEFAULT TRUE
1107 )
1108 IS
1109 cIn_String varchar2(1000) := UPPER(i_search_text);
1110 nColumn_count pls_integer := g_file_tbl.COUNT;
1111 bFound BOOLEAN := FALSE;
1112 POS_NOT_FOUND EXCEPTION;
1113 BEGIN
1114 if EC_DEBUG.G_debug_level >= 2 then
1115 ec_debug.PUSH('EC_UTILS.FIND_POS');
1116 ec_debug.pl(3,'i_from_level',i_from_level);
1117 ec_debug.pl(3,'i_to_level',i_to_level);
1118 ec_debug.pl(3,'i_search_text',i_search_text);
1119 ec_debug.pl(3,'i_required',i_required);
1120 end if;
1121 for j in i_from_level..i_to_level
1122 loop
1123 for k in 1..nColumn_count
1124 loop
1125 if g_file_tbl(k).interface_level = j
1126 then
1127 if upper(g_file_tbl(k).interface_column_name) = cIn_String
1128 then
1129 o_pos := k;
1130 bFound := TRUE;
1131 exit;
1132 end if;
1133 end if;
1134 end loop;
1135
1136 if ( bfound)
1137 then
1138 exit;
1139 end if;
1140 end loop;
1141
1142 if not bFound
1143 then
1144 if (i_required)
1145 then
1146 raise POS_NOT_FOUND;
1147 else
1148 o_pos := NULL;
1149 end if;
1150 end if;
1151 if EC_DEBUG.G_debug_level >= 3 then
1152 ec_debug.pl(3,'o_pos',o_pos);
1153 ec_debug.POP('EC_UTILS.FIND_POS');
1154 end if;
1155 EXCEPTION
1156 WHEN POS_NOT_FOUND THEN
1157 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME',cIn_String);
1158 ec_debug.POP('EC_UTILS.FIND_POS');
1159 i_ret_code := 2;
1160 raise EC_UTILS.PROGRAM_EXIT;
1161 WHEN OTHERS THEN
1162 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.FIND_POS');
1163 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1164 i_ret_code := 2;
1165 raise EC_UTILS.PROGRAM_EXIT;
1166 END find_pos;
1167
1168 /**
1169 Creates a variable passed as i_variable_name on the Stack for a given Level.
1170 If the variable level <> 0 , then it means that the variable is to be searched
1171 on the PL/SQL mapping table. The position and the Value from the PL/SQL mapping
1172 table is maintained on the Stack table to avoid un-necessary search.
1173 i.e. i := 5;
1174 **/
1175 procedure create_new_variable
1176 (
1177 i_variable_level IN NUMBER,
1178 i_variable_name IN VARCHAR2,
1179 i_variable_value IN varchar2,
1180 i_data_type IN varchar2 default NULL
1181 )
1182 is
1183 m_count pls_integer := g_stack.COUNT;
1187 i_pre_interface_pos pls_integer;
1184 current_on_stack BOOLEAN := FALSE;
1185 i_stack_pos pls_integer;
1186 i_interface_pos pls_integer;
1188 hash_value pls_integer; -- Bug 2708573
1189 hash_string varchar2(3200);
1190 begin
1191 if EC_DEBUG.G_debug_level >= 2 then
1192 ec_debug.push('EC_UTILS.CREATE_NEW_VARIABLE');
1193 ec_debug.pl(3,'i_variable_level',i_variable_level);
1194 ec_debug.pl(3,'i_variable_name',i_variable_name);
1195 ec_debug.pl(3,'i_variable_value',i_variable_value);
1196 end if;
1197 current_on_stack := find_variable
1198 (
1199 i_variable_level,
1200 i_variable_name,
1201 i_stack_pos,
1202 i_interface_pos
1203 );
1204
1205 if ( current_on_stack )
1206 then
1207 /* Bug 2708573
1208 ec_debug.pl(0,'EC','ECE_DUPLICATE_VARIABLE_STACK','VARIABLE_NAME',i_variable_name);
1209 i_ret_code := 2;
1210 raise EC_UTILS.PROGRAM_EXIT;
1211 */
1212 -- Bug 2708573
1213 if i_variable_level = 0 and g_stack(i_stack_pos).variable_value is null
1214 then
1215 g_stack(i_stack_pos).variable_value := i_variable_value;
1216 end if;
1217 if EC_DEBUG.G_debug_level >= 3 then
1218 ec_debug.pl(3,'Variable already on Stack');
1219 ec_debug.pl(3,i_variable_name,i_variable_value);
1220 end if;
1221
1222 else
1223 if i_variable_level = 0
1224 then
1225 m_count := m_count + 1;
1226 g_stack(m_count).level := i_variable_level;
1227 g_stack(m_count).variable_name := i_variable_name;
1228 g_stack(m_count).variable_value := i_variable_value;
1229 g_stack(m_count).data_type := i_data_type;
1230 if EC_DEBUG.G_debug_level >= 3 then
1231 ec_debug.pl(3,i_variable_name,i_variable_value);
1232 end if;
1233 else
1234 FIND_POS
1235 (
1236 i_variable_level,
1237 i_variable_name,
1238 i_pre_interface_pos
1239 );
1240
1241 m_count := m_count + 1;
1242 g_stack(m_count).level := i_variable_level;
1243 g_stack(m_count).variable_name := i_variable_name;
1244 g_stack(m_count).variable_position := i_pre_interface_pos;
1245 g_stack(m_count).data_type := i_data_type;
1246 g_file_tbl(i_pre_interface_pos).value := i_variable_value;
1247 if EC_DEBUG.G_debug_level >= 3 then
1248 ec_debug.pl(3,i_variable_name,g_file_tbl(i_pre_interface_pos).value);
1249 end if;
1250 end if;
1251 -- Bug 2708573
1252 -- Populate Hash table for searching the g_stack
1253 hash_string := to_char(i_variable_level)||'-'||i_variable_name ;
1254 hash_value := dbms_utility.get_hash_value(hash_string,1,100000);
1255 ec_utils.g_stack_pos_tbl(hash_value):=m_count;
1256
1257 end if;
1258 if EC_DEBUG.G_debug_level >= 2 then
1259 ec_debug.pop('EC_UTILS.CREATE_NEW_VARIABLE');
1260 end if;
1261 EXCEPTION
1262 WHEN EC_UTILS.PROGRAM_EXIT then
1263 raise;
1264 WHEN OTHERS THEN
1265 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.CREATE_NEW_VARIABLE');
1266 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1267 i_ret_code := 2;
1268 raise EC_UTILS.PROGRAM_EXIT;
1269 end create_new_variable;
1270
1271 /**
1272 if x is null then set y := default value
1273 i.e. i := j;
1274 **/
1275 procedure IF_XNULL_SETYDEFAULT
1276 (
1277 i_variable_level IN number,
1278 i_variable_name IN varchar2,
1279 i_previous_variable_level IN number,
1280 i_previous_variable_name IN varchar2,
1281 i_default_value IN varchar2
1282 )
1283 is
1284
1285 var_present BOOLEAN := FALSE;
1286 pre_var_present BOOLEAN := FALSE;
1287 o_stack_pos pls_integer;
1288 o_stack_pre_pos pls_integer;
1289 o_plsql_pos pls_integer;
1290 o_plsql_pre_pos pls_integer;
1291 BEGIN
1292 if EC_DEBUG.G_debug_level >= 2 then
1293 ec_debug.push('EC_UTILS.IF_XNULL_SETYDEFAULT');
1294 ec_debug.pl(3,'i_variable_level',i_variable_level);
1295 ec_debug.pl(3,'i_variable_name',i_variable_name);
1296 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
1297 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
1298 ec_debug.pl(3,'i_default_value',i_default_value);
1299 end if;
1300 var_present :=find_variable
1301 (
1302 i_variable_level,
1303 i_variable_name,
1304 o_stack_pos,
1305 o_plsql_pos
1306 );
1307 if NOT ( var_present)
1308 then
1309 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1310 i_ret_code := 2;
1311 raise EC_UTILS.PROGRAM_EXIT;
1312 end if;
1313
1314 pre_var_present := find_variable
1315 (
1316 i_previous_variable_level,
1317 i_previous_variable_name,
1318 o_stack_pre_pos,
1319 o_plsql_pre_pos
1320 );
1321
1322 if NOT ( pre_var_present)
1323 then
1324 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
1325 'VARIABLE_NAME',i_previous_variable_name);
1326 i_ret_code := 2;
1327 raise EC_UTILS.PROGRAM_EXIT;
1328 end if;
1329
1330 if i_variable_level = 0
1331 then
1332 if g_stack(o_stack_pos).variable_value is NULL
1336 g_stack(o_stack_pre_pos).variable_value
1333 then
1334 if i_previous_variable_level = 0
1335 then
1337 := i_default_value;
1338 if EC_DEBUG.G_debug_level >= 3 then
1339 ec_debug.pl(3,i_previous_variable_name,g_stack(o_stack_pre_pos).variable_value);
1340 end if;
1341 else
1342 g_file_tbl(o_plsql_pre_pos).value := i_default_value;
1343 if EC_DEBUG.G_debug_level >= 3 then
1344 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pre_pos).value);
1345 end if;
1346 end if;
1347 end if;
1348 else
1349 if g_file_tbl(o_plsql_pos).value is NULL
1350 then
1351 if i_previous_variable_level = 0
1352 then
1353 g_stack(o_stack_pre_pos).variable_value
1354 := i_default_value;
1355 if EC_DEBUG.G_debug_level >= 3 then
1356 ec_debug.pl(3,i_previous_variable_name,g_stack(o_stack_pre_pos).variable_value);
1357 end if;
1358 else
1359 g_file_tbl(o_plsql_pre_pos).value := i_default_value;
1360 if EC_DEBUG.G_debug_level >= 3 then
1361 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pre_pos).value);
1362 end if;
1363 end if;
1364 end if;
1365 end if;
1366 if EC_DEBUG.G_debug_level >= 2 then
1367 ec_debug.pop('EC_UTILS.IF_XNULL_SETYDEFAULT');
1368 end if;
1369 EXCEPTION
1370 WHEN EC_UTILS.PROGRAM_EXIT then
1371 raise;
1372 WHEN OTHERS THEN
1373 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_XNULL_SETYDEFAULT');
1374 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1375 i_ret_code := 2;
1376 raise EC_UTILS.PROGRAM_EXIT;
1377 END IF_XNULL_SETYDEFAULT;
1378
1379 /**
1380 Assigns the Value from a Previously Defined Variable
1381 i.e. i := j;
1382 **/
1383 procedure assign_pre_defined_variables
1384 (
1385 i_variable_level IN number,
1386 i_variable_name IN varchar2,
1387 i_previous_variable_level IN number,
1388 i_previous_variable_name IN varchar2
1389 )
1390 is
1391
1392 var_present BOOLEAN := FALSE;
1393 pre_var_present BOOLEAN := FALSE;
1394 o_stack_pos pls_integer;
1395 o_stack_pre_pos pls_integer;
1396 o_plsql_pos pls_integer;
1397 o_plsql_pre_pos pls_integer;
1398 BEGIN
1399 if EC_DEBUG.G_debug_level >= 2 then
1400 ec_debug.push('EC_UTILS.ASSIGN_PRE_DEFINED_VARIABLES');
1401 ec_debug.pl(3,'i_variable_level',i_variable_level);
1402 ec_debug.pl(3,'i_variable_name',i_variable_name);
1403 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
1404 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
1405 end if;
1406 var_present :=find_variable
1407 (
1408 i_variable_level,
1409 i_variable_name,
1410 o_stack_pos,
1411 o_plsql_pos
1412 );
1413 if NOT ( var_present)
1414 then
1415 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1416 i_ret_code := 2;
1417 raise EC_UTILS.PROGRAM_EXIT;
1418 end if;
1419
1420 pre_var_present := find_variable
1421 (
1422 i_previous_variable_level,
1423 i_previous_variable_name,
1424 o_stack_pre_pos,
1425 o_plsql_pre_pos
1426 );
1427
1428 if NOT ( pre_var_present)
1429 then
1430 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
1431 'VARIABLE_NAME',i_previous_variable_name);
1432 i_ret_code := 2;
1433 raise EC_UTILS.PROGRAM_EXIT;
1434 end if;
1435
1436 if i_variable_level = 0
1437 then
1438 if i_previous_variable_level = 0
1439 then
1440 g_stack(o_stack_pos).variable_value
1441 := g_stack(o_stack_pre_pos).variable_value;
1442 if EC_DEBUG.G_debug_level >= 3 then
1443 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1444 end if;
1445 else
1446 g_stack(o_stack_pos).variable_value
1447 := g_file_tbl(o_plsql_pre_pos).value;
1448 if EC_DEBUG.G_debug_level >= 3 then
1449 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1450 end if;
1451 end if;
1452 else
1453 if i_previous_variable_level = 0
1454 then
1455 g_file_tbl(o_plsql_pos).value
1456 := g_stack(o_stack_pre_pos).variable_value;
1457 if EC_DEBUG.G_debug_level >= 3 then
1458 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1459 end if;
1460 else
1461 g_file_tbl(o_plsql_pos).value
1462 := g_file_tbl(o_plsql_pre_pos).value;
1463 if EC_DEBUG.G_debug_level >= 3 then
1464 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1465 end if;
1466 end if;
1467 end if;
1468 if EC_DEBUG.G_debug_level >= 2 then
1469 ec_debug.pop('EC_UTILS.ASSIGN_PRE_DEFINED_VARIABLES');
1470 end if;
1471 EXCEPTION
1472 WHEN EC_UTILS.PROGRAM_EXIT then
1473 raise;
1474 WHEN OTHERS THEN
1475 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.ASSIGN_PRE_DEFINED_VARIABLES');
1476 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1477 i_ret_code := 2;
1478 raise EC_UTILS.PROGRAM_EXIT;
1482 /**
1479 END assign_pre_defined_variables;
1480
1481
1483 Assign Default Value to a Variable.
1484 i.e. i := 5;
1485 **/
1486 procedure assign_default_to_variables
1487 (
1488 i_variable_level IN number,
1489 i_variable_name IN varchar2,
1490 i_default_value IN varchar2
1491 )
1492 is
1493 var_present BOOLEAN := FALSE;
1494 o_stack_pos pls_integer;
1495 o_plsql_pos pls_integer;
1496 BEGIN
1497 if EC_DEBUG.G_debug_level >= 2 then
1498 ec_debug.push('EC_UTILS.ASSIGN_DEFAULT_TO_VARIABLES');
1499 ec_debug.pl(3,'i_variable_level',i_variable_level);
1500 ec_debug.pl(3,'i_variable_name',i_variable_name);
1501 ec_debug.pl(3,'i_default_value',i_default_value);
1502 end if;
1503
1504 var_present := find_variable
1505 (
1506 i_variable_level,
1507 i_variable_name,
1508 o_stack_pos,
1509 o_plsql_pos
1510 );
1511
1512 if ( var_present )
1513 then
1514 if i_variable_level = 0
1515 then
1516 g_stack(o_stack_pos).variable_value := i_default_value;
1517 if EC_DEBUG.G_debug_level >= 3 then
1518 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1519 end if;
1520 else
1521 g_file_tbl(o_plsql_pos).value := i_default_value;
1522 if EC_DEBUG.G_debug_level >= 3 then
1523 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1524 end if;
1525 end if;
1526 else
1527 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1528 i_ret_code := 2;
1529 raise EC_UTILS.PROGRAM_EXIT;
1530 end if;
1531 if EC_DEBUG.G_debug_level >= 2 then
1532 ec_debug.pop('EC_UTILS.ASSIGN_DEFAULT_TO_VARIABLES');
1533 end if;
1534 EXCEPTION
1535 WHEN EC_UTILS.PROGRAM_EXIT then
1536 raise;
1537 WHEN OTHERS THEN
1538 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.ASSIGN_DEFAULT_TO_VARIABLES');
1539 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1540 i_ret_code := 2;
1541 raise EC_UTILS.PROGRAM_EXIT;
1542 END assign_default_to_variables;
1543
1544 /**
1545 If the Variable is null then assign the Default Value.
1546 i.e. if i is NULL
1547 then
1548 i := 5;
1549 end if;
1550 **/
1551 procedure if_null_equal_default_value
1552 (
1553 i_variable_level IN number,
1554 i_variable_name IN varchar2,
1555 i_default_value IN varchar2
1556 )
1557 is
1558 var_present BOOLEAN := FALSE;
1559 o_stack_pos pls_integer;
1560 o_plsql_pos pls_integer;
1561 BEGIN
1562 if EC_DEBUG.G_debug_level >= 2 then
1563 ec_debug.push('EC_UTILS.IF_NULL_EQUAL_DEFAULT_VALUE');
1564 ec_debug.pl(3,'i_variable_level',i_variable_level);
1565 ec_debug.pl(3,'i_variable_name',i_variable_name);
1566 ec_debug.pl(3,'i_default_value',i_default_value);
1567 end if;
1568
1569 var_present := find_variable
1570 (
1571 i_variable_level,
1572 i_variable_name,
1573 o_stack_pos,
1574 o_plsql_pos
1575 );
1576
1577 if ( var_present )
1578 then
1579 if i_variable_level = 0
1580 then
1581 if g_stack(o_stack_pos).variable_value is NULL
1582 then
1583 g_stack(o_stack_pos).variable_value := i_default_value;
1584 if EC_DEBUG.G_debug_level >= 3 then
1585 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1586 end if;
1587 end if;
1588 else
1589 if g_file_tbl(o_plsql_pos).value is NULL
1590 then
1591 g_file_tbl(o_plsql_pos).value := i_default_value;
1592 if EC_DEBUG.G_debug_level >= 3 then
1593 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1594 end if;
1595 end if;
1596 end if;
1597 else
1598 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1599 i_ret_code := 2;
1600 raise EC_UTILS.PROGRAM_EXIT;
1601 end if;
1602 if EC_DEBUG.G_debug_level >= 2 then
1603 ec_debug.pop('EC_UTILS.IF_NULL_EQUAL_DEFAULT_VALUE');
1604 end if;
1605 EXCEPTION
1606 WHEN EC_UTILS.PROGRAM_EXIT then
1607 raise;
1608 WHEN OTHERS THEN
1609 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_NULL_EQUAL_DEFAULT_VALUE');
1610 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1611 i_ret_code := 2;
1612 raise EC_UTILS.PROGRAM_EXIT;
1613 END if_null_equal_default_value;
1614
1615 /**
1616 If the Value of the Variable is Null then assign
1617 the Value of a previously defined variable.
1618 i.e. If i is NULL
1619 then
1620 i := j;
1621 end if;
1622 **/
1623 procedure if_null_pre_defined_variable
1624 (
1625 i_variable_level IN number,
1626 i_variable_name IN varchar2,
1627 i_previous_variable_level IN number,
1628 i_previous_variable_name IN varchar2
1629 )
1630 is
1631
1632 var_present BOOLEAN := FALSE;
1633 pre_var_present BOOLEAN := FALSE;
1634 o_stack_pos pls_integer;
1635 o_stack_pre_pos pls_integer;
1636 o_plsql_pos pls_integer;
1640 ec_debug.push('EC_UTILS.IF_NULL_PRE_DEFINED_VARIABLE');
1637 o_plsql_pre_pos pls_integer;
1638 BEGIN
1639 if EC_DEBUG.G_debug_level >= 2 then
1641 ec_debug.pl(3,'i_variable_level',i_variable_level);
1642 ec_debug.pl(3,'i_variable_name',i_variable_name);
1643 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
1644 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
1645 end if;
1646
1647 var_present :=find_variable
1648 (
1649 i_variable_level,
1650 i_variable_name,
1651 o_stack_pos,
1652 o_plsql_pos
1653 );
1654 if NOT ( var_present)
1655 then
1656 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1657 i_ret_code := 2;
1658 raise EC_UTILS.PROGRAM_EXIT;
1659 end if;
1660
1661 pre_var_present := find_variable
1662 (
1663 i_previous_variable_level,
1664 i_previous_variable_name,
1665 o_stack_pre_pos,
1666 o_plsql_pre_pos
1667 );
1668
1669 if NOT ( pre_var_present)
1670 then
1671 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
1672 'VARIABLE_NAME',i_previous_variable_name);
1673 i_ret_code := 2;
1674 raise EC_UTILS.PROGRAM_EXIT;
1675 end if;
1676
1677 if i_variable_level = 0
1678 then
1679 if g_stack(o_stack_pos).variable_value is NULL
1680 then
1681 if i_previous_variable_level = 0
1682 then
1683 g_stack(o_stack_pos).variable_value
1684 := g_stack(o_stack_pre_pos).variable_value;
1685 if EC_DEBUG.G_debug_level >= 3 then
1686 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1687 end if;
1688 else
1689 g_stack(o_stack_pos).variable_value
1690 := g_file_tbl(o_plsql_pre_pos).value;
1691 if EC_DEBUG.G_debug_level >= 3 then
1692 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1693 end if;
1694 end if;
1695 end if;
1696 else
1697 if g_file_tbl(o_plsql_pos).value is NULL
1698 then
1699 if i_previous_variable_level = 0
1700 then
1701 g_file_tbl(o_plsql_pos).value
1702 := g_stack(o_stack_pre_pos).variable_value;
1703 if EC_DEBUG.G_debug_level >= 3 then
1704 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1705 end if;
1706 else
1707 g_file_tbl(o_plsql_pos).value
1708 := g_file_tbl(o_plsql_pre_pos).value;
1709 if EC_DEBUG.G_debug_level >= 3 then
1710 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1711 end if;
1712 end if;
1713 end if;
1714 end if;
1715 if EC_DEBUG.G_debug_level >= 2 then
1716 ec_debug.pop('EC_UTILS.IF_NULL_PRE_DEFINED_VARIABLE');
1717 end if;
1718 EXCEPTION
1719 WHEN EC_UTILS.PROGRAM_EXIT then
1720 raise;
1721 WHEN OTHERS THEN
1722 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_NULL_PRE_DEFINED_VARIABLE');
1723 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1724 i_ret_code := 2;
1725 raise EC_UTILS.PROGRAM_EXIT;
1726 END if_null_pre_defined_variable;
1727
1728 /**
1729 If the Value of the Variable is not Null then assign
1730 the Value of the Variable to previously defined variable.
1731 i.e. If b is not NULL b -> Variable at current Level
1732 then a -> Variable at Previous Level
1733 a := b;
1734 end if;
1735 **/
1736
1737 /* Bug 1999536*/
1738 procedure if_not_null_defined_variable
1739 (
1740 i_variable_level IN number,
1741 i_variable_name IN varchar2,
1742 i_previous_variable_level IN number,
1743 i_previous_variable_name IN varchar2
1744 )
1745 is
1746
1747 var_present BOOLEAN := FALSE;
1748 pre_var_present BOOLEAN := FALSE;
1749 o_stack_pos pls_integer;
1750 o_stack_pre_pos pls_integer;
1751 o_plsql_pos pls_integer;
1752 o_plsql_pre_pos pls_integer;
1753 BEGIN
1754 if EC_DEBUG.G_debug_level >= 2 then
1755 ec_debug.push('EC_UTILS.IF_NOT_NULL_DEFINED_VARIABLE');
1756 ec_debug.pl(3,'i_variable_level',i_variable_level);
1757 ec_debug.pl(3,'i_variable_name',i_variable_name);
1758 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
1759 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
1760 end if;
1761
1762 var_present :=find_variable
1763 (
1764 i_variable_level,
1765 i_variable_name,
1766 o_stack_pos,
1767 o_plsql_pos
1768 );
1769 if NOT ( var_present)
1770 then
1771 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1772 i_ret_code := 2;
1773 raise EC_UTILS.PROGRAM_EXIT;
1774 end if;
1775
1776 pre_var_present := find_variable
1777 (
1781 o_plsql_pre_pos
1778 i_previous_variable_level,
1779 i_previous_variable_name,
1780 o_stack_pre_pos,
1782 );
1783
1784 if NOT ( pre_var_present)
1785 then
1786 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_previous_variable_name);
1787 i_ret_code := 2;
1788 raise EC_UTILS.PROGRAM_EXIT;
1789 end if;
1790
1791 if i_variable_level = 0 /* My current Variable Level is 0 */
1792 then
1793 if g_stack(o_stack_pos).variable_value is NOT NULL
1794 then
1795 if i_previous_variable_level = 0
1796 then
1797 g_stack(o_stack_pre_pos).variable_value
1798 := g_stack(o_stack_pos).variable_value;
1799 if EC_DEBUG.G_debug_level >= 3 then
1800 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1801 end if;
1802 else
1803 g_file_tbl(o_plsql_pre_pos).value
1804 := g_stack(o_stack_pos).variable_value;
1805 if EC_DEBUG.G_debug_level >= 3 then
1806 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1807 end if;
1808 end if;
1809 end if;
1810 else /* My current Variable Level is not 0 */
1811 if g_file_tbl(o_plsql_pos).value is NOT NULL
1812 then
1813 if i_previous_variable_level = 0
1814 then
1815 g_stack(o_stack_pre_pos).variable_value
1816 := g_file_tbl(o_plsql_pos).value;
1817 if EC_DEBUG.G_debug_level >= 3 then
1818 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1819 end if;
1820 else
1821 g_file_tbl(o_plsql_pre_pos).value
1822 := g_file_tbl(o_plsql_pos).value;
1823 if EC_DEBUG.G_debug_level >= 3 then
1824 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1825 end if;
1826 end if;
1827 end if;
1828 end if;
1829 if EC_DEBUG.G_debug_level >= 2 then
1830 ec_debug.pop('EC_UTILS.IF_NOT_NULL_DEFINED_VARIABLE');
1831 end if;
1832 EXCEPTION
1833 WHEN EC_UTILS.PROGRAM_EXIT then
1834 raise;
1835 WHEN OTHERS THEN
1836 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_NOT_NULL_DEFINED_VARIABLE');
1837 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1838 i_ret_code := 2;
1839 raise EC_UTILS.PROGRAM_EXIT;
1840 END if_not_null_defined_variable; /* Bug 1999536*/
1841
1842 /**
1843 If the Variable is different from Previous variable Value and the
1844 Next variable Value , then Assign Default.
1845 e.g. If i <> x AND i <> y
1846 then
1847 i = 10;
1848 end if;
1849 **/
1850 procedure if_diff_pre_next_then_default
1851 (
1852 i_variable_level IN number,
1853 i_variable_name IN varchar2,
1854 i_previous_variable_level IN number,
1855 i_previous_variable_name IN varchar2,
1856 i_next_variable_name IN varchar2,
1857 i_default_value IN varchar2
1858 )
1859 is
1860
1861 var_present BOOLEAN := FALSE;
1862 pre_var_present BOOLEAN := FALSE;
1863 next_var_present BOOLEAN := FALSE;
1864 o_stack_pos pls_integer;
1865 o_stack_pre_pos pls_integer;
1866 o_stack_next_pos pls_integer;
1867 o_plsql_pos pls_integer;
1868 o_plsql_pre_pos pls_integer;
1869 o_plsql_next_pos pls_integer;
1870 BEGIN
1871 if EC_DEBUG.G_debug_level >= 2 then
1872 ec_debug.push('EC_UTILS.IF_DIFF_PRE_NEXT_THEN_DEFAULT');
1873 ec_debug.pl(3,'i_variable_level',i_variable_level);
1874 ec_debug.pl(3,'i_variable_name',i_variable_name);
1875 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
1876 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
1877 ec_debug.pl(3,'i_next_variable_name',i_next_variable_name);
1878 ec_debug.pl(3,'i_default_value',i_default_value);
1879 end if;
1880 var_present :=find_variable
1881 (
1882 i_variable_level,
1883 i_variable_name,
1884 o_stack_pos,
1885 o_plsql_pos
1886 );
1887 if NOT ( var_present)
1888 then
1889 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
1890 i_ret_code := 2;
1891 raise EC_UTILS.PROGRAM_EXIT;
1892 end if;
1893
1894 pre_var_present := find_variable
1895 (
1896 i_previous_variable_level,
1897 i_previous_variable_name,
1898 o_stack_pre_pos,
1899 o_plsql_pre_pos
1900 );
1901
1902 if NOT ( pre_var_present)
1903 then
1904 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
1905 'VARIABLE_NAME',i_previous_variable_name);
1906 i_ret_code := 2;
1907 raise EC_UTILS.PROGRAM_EXIT;
1908 end if;
1909
1910 next_var_present := find_variable
1911 (
1912 i_previous_variable_level,
1913 i_next_variable_name,
1917
1914 o_stack_next_pos,
1915 o_plsql_next_pos
1916 );
1918 if NOT ( next_var_present)
1919 then
1920 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
1921 'VARIABLE_NAME',i_next_variable_name);
1922 i_ret_code := 2;
1923 raise EC_UTILS.PROGRAM_EXIT;
1924 end if;
1925
1926 if i_variable_level = 0
1927 then
1928 if i_previous_variable_level = 0
1929 then
1930 if g_stack(o_stack_pos).variable_value <>
1931 g_stack(o_stack_pre_pos).variable_value and
1932 g_stack(o_stack_pos).variable_value <>
1933 g_stack(o_stack_next_pos).variable_value
1934 then
1935 g_stack(o_stack_pos).variable_value := i_default_value;
1936 if EC_DEBUG.G_debug_level >= 3 then
1937 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1938 end if;
1939 end if;
1940 else
1941 if g_stack(o_stack_pos).variable_value <>
1942 g_file_tbl(o_plsql_pre_pos).value and
1943 g_stack(o_stack_pos).variable_value <>
1944 g_file_tbl(o_plsql_next_pos).value
1945 then
1946 g_stack(o_stack_pos).variable_value := i_default_value;
1947 if EC_DEBUG.G_debug_level >= 3 then
1948 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
1949 end if;
1950 end if;
1951 end if;
1952
1953 else
1954 if i_previous_variable_level = 0
1955 then
1956 if g_file_tbl(o_plsql_pos).value <>
1957 g_stack(o_stack_pre_pos).variable_value and
1958 g_file_tbl(o_plsql_pos).value <>
1959 g_stack(o_stack_next_pos).variable_value
1960 then
1961 g_file_tbl(o_plsql_pos).value := i_default_value;
1962 if EC_DEBUG.G_debug_level >= 3 then
1963 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1964 end if;
1965 end if;
1966 else
1967 if g_file_tbl(o_plsql_pos).value <>
1968 g_file_tbl(o_plsql_pre_pos).value and
1969 g_file_tbl(o_plsql_pos).value <>
1970 g_file_tbl(o_plsql_next_pos).value
1971 then
1972 g_file_tbl(o_plsql_pos).value := i_default_value;
1973 if EC_DEBUG.G_debug_level >= 3 then
1974 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
1975 end if;
1976 end if;
1977 end if;
1978 end if;
1979 if EC_DEBUG.G_debug_level >= 2 then
1980 ec_debug.pop('EC_UTILS.IF_DIFF_PRE_NEXT_THEN_DEFAULT');
1981 end if;
1982 EXCEPTION
1983 WHEN EC_UTILS.PROGRAM_EXIT then
1984 raise;
1985 WHEN OTHERS THEN
1986 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_DIFF_PRE_NEXT_THEN_DEFAULT');
1987 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1988 i_ret_code := 2;
1989 raise EC_UTILS.PROGRAM_EXIT;
1990 end if_diff_pre_next_then_default;
1991
1992 /**
1993 If the Variable is equal to Default value then assign the value of a
1994 previously defined variable.
1995 e.g. if x = 1000
1996 then
1997 x = y;
1998 end if;
1999 **/
2000 procedure if_default_pre_defined_var
2001 (
2002 i_variable_level IN number,
2003 i_variable_name IN varchar2,
2004 i_previous_variable_level IN number,
2005 i_previous_variable_name IN varchar2,
2006 i_default_value IN varchar2
2007 )
2008 is
2009 var_present BOOLEAN := FALSE;
2010 pre_var_present BOOLEAN := FALSE;
2011 o_stack_pos pls_integer;
2012 o_stack_pre_pos pls_integer;
2013 o_plsql_pos pls_integer;
2014 o_plsql_pre_pos pls_integer;
2015 BEGIN
2016 if EC_DEBUG.G_debug_level >= 2 then
2017 ec_debug.push('EC_UTILS.IF_DEFAULT_PRE_DEFINED_VAR');
2018 ec_debug.pl(3,'i_variable_level',i_variable_level);
2019 ec_debug.pl(3,'i_variable_name',i_variable_name);
2020 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
2021 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
2022 ec_debug.pl(3,'i_default_value',i_default_value);
2023 end if;
2024
2025 var_present :=find_variable
2026 (
2027 i_variable_level,
2028 i_variable_name,
2029 o_stack_pos,
2030 o_plsql_pos
2031 );
2032 if NOT ( var_present)
2033 then
2034 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2035 i_ret_code := 2;
2036 raise EC_UTILS.PROGRAM_EXIT;
2037 end if;
2038
2039 pre_var_present := find_variable
2040 (
2041 i_previous_variable_level,
2042 i_previous_variable_name,
2043 o_stack_pre_pos,
2044 o_plsql_pre_pos
2045 );
2046
2047 if NOT ( pre_var_present)
2048 then
2049 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
2050 'VARIABLE_NAME',i_previous_variable_name);
2051 i_ret_code := 2;
2052 raise EC_UTILS.PROGRAM_EXIT;
2053 end if;
2054
2055 if i_variable_level = 0
2056 then
2057 if g_stack(o_stack_pos).variable_value = i_default_value
2058 then
2059 if i_previous_variable_level = 0
2060 then
2061 g_stack(o_stack_pos).variable_value
2062 := g_stack(o_stack_pre_pos).variable_value;
2063 if EC_DEBUG.G_debug_level >= 3 then
2064 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
2065 end if;
2066 else
2067 g_stack(o_stack_pos).variable_value
2071 end if;
2068 := g_file_tbl(o_plsql_pre_pos).value;
2069 if EC_DEBUG.G_debug_level >= 3 then
2070 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
2072 end if;
2073 end if;
2074 else
2075 if g_file_tbl(o_plsql_pos).value = i_default_value
2076 then
2077 if i_previous_variable_level = 0
2078 then
2079 g_file_tbl(o_plsql_pos).value
2080 := g_stack(o_stack_pre_pos).variable_value;
2081 if EC_DEBUG.G_debug_level >= 3 then
2082 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
2083 end if;
2084 else
2085 g_file_tbl(o_plsql_pos).value
2086 := g_file_tbl(o_plsql_pre_pos).value;
2087 if EC_DEBUG.G_debug_level >= 3 then
2088 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
2089 end if;
2090 end if;
2091 end if;
2092 end if;
2093 if EC_DEBUG.G_debug_level >= 2 then
2094 ec_debug.pop('EC_UTILS.IF_DEFAULT_PRE_DEFINED_VAR');
2095 end if;
2096 EXCEPTION
2097 WHEN EC_UTILS.PROGRAM_EXIT then
2098 raise;
2099 WHEN OTHERS THEN
2100 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_DEFAULT_PRE_DEFINED_VAR');
2101 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2102 i_ret_code := 2;
2103 raise EC_UTILS.PROGRAM_EXIT;
2104 END if_default_pre_defined_var;
2105
2106 /**
2107 Assign the Nextvalue from a sequence.
2108 e.g. x := document.NEXTVAL;
2109 **/
2110 procedure assign_nextval_from_sequence
2111 (
2112 i_variable_level IN number,
2113 i_variable_name IN varchar2,
2114 i_sequence_name IN varchar2
2115 )
2116 is
2117 var_present BOOLEAN := FALSE;
2118 o_stack_pos pls_integer;
2119 o_plsql_pos pls_integer;
2120 BEGIN
2121 if EC_DEBUG.G_debug_level >= 2 then
2122 ec_debug.push('EC_UTILS.ASSIGN_NEXTVAL_FROM_SEQUENCE');
2123 ec_debug.pl(3,'i_variable_level',i_variable_level);
2124 ec_debug.pl(3,'i_variable_name',i_variable_name);
2125 ec_debug.pl(3,'i_sequence_name',i_sequence_name);
2126 end if;
2127 var_present := find_variable
2128 (
2129 i_variable_level,
2130 i_variable_name,
2131 o_stack_pos,
2132 o_plsql_pos
2133 );
2134
2135 if ( var_present )
2136 then
2137 if i_variable_level = 0
2138 then
2139 get_nextval_seq
2140 (
2141 i_sequence_name,
2142 g_stack(o_stack_pos).variable_value
2143 );
2144 if EC_DEBUG.G_debug_level >= 3 then
2145 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
2146 end if;
2147 else
2148 get_nextval_seq
2149 (
2150 i_sequence_name,
2151 g_file_tbl(o_plsql_pos).value
2152 );
2153 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
2154 end if;
2155 else
2156 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2157 i_ret_code := 2;
2158 raise EC_UTILS.PROGRAM_EXIT;
2159 end if;
2160 if EC_DEBUG.G_debug_level >= 2 then
2161 ec_debug.pop('EC_UTILS.ASSIGN_NEXTVAL_FROM_SEQUENCE');
2162 end if;
2163 EXCEPTION
2164 WHEN EC_UTILS.PROGRAM_EXIT then
2165 raise;
2166 WHEN OTHERS THEN
2167 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.ASSIGN_NEXTVAL_FROM_SEQUENCE');
2168 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2169 i_ret_code := 2;
2170 raise EC_UTILS.PROGRAM_EXIT;
2171 END assign_nextval_from_sequence;
2172
2173 /**
2174 Assigns the value returned by a function.
2175 e.g. x := fnd_global.sysdate;
2176 **/
2177 procedure assign_function_value
2178 (
2179 i_variable_level IN number,
2180 i_variable_name IN varchar2,
2181 i_function_name IN varchar2
2182 )
2183 is
2184 var_present BOOLEAN := FALSE;
2185 o_stack_pos pls_integer;
2186 o_plsql_pos pls_integer;
2187 BEGIN
2188 if EC_DEBUG.G_debug_level >= 2 then
2189 ec_debug.push('EC_UTILS.ASSIGN_FUNCTION_VALUE');
2190 ec_debug.pl(3,'i_variable_level',i_variable_level);
2191 ec_debug.pl(3,'i_variable_name',i_variable_name);
2192 ec_debug.pl(3,'i_function_name',i_function_name);
2193 end if;
2194
2195 var_present := find_variable
2196 (
2197 i_variable_level,
2198 i_variable_name,
2199 o_stack_pos,
2200 o_plsql_pos
2201 );
2202
2203 if ( var_present )
2204 then
2205 if i_variable_level = 0
2206 then
2207 get_function_value
2208 (
2209 i_function_name,
2210 g_stack(o_stack_pos).variable_value
2211 );
2212 if EC_DEBUG.G_debug_level >= 3 then
2213 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
2214 end if;
2215 else
2216 get_function_value
2217 (
2218 i_function_name,
2219 g_file_tbl(o_plsql_pos).value
2220 );
2221 if EC_DEBUG.G_debug_level >= 3 then
2222 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
2223 end if;
2224 end if;
2225 else
2226 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2227 i_ret_code := 2;
2228 raise EC_UTILS.PROGRAM_EXIT;
2232 end if;
2229 end if;
2230 if EC_DEBUG.G_debug_level >= 2 then
2231 ec_debug.pop('EC_UTILS.ASSIGN_FUNCTION_VALUE');
2233 EXCEPTION
2234 WHEN EC_UTILS.PROGRAM_EXIT then
2235 raise;
2236 WHEN OTHERS THEN
2237 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.ASSIGN_FUNCTION_VALUE');
2238 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2239 i_ret_code := 2;
2240 raise EC_UTILS.PROGRAM_EXIT;
2241 END assign_function_value;
2242
2243 /**
2244 If the value of the variable is null then skip the Document.
2245 e.g. If Purchase_Order_Num is null
2246 then
2247 skip Document;
2248 end if;
2249 **/
2250 procedure if_null_skip_document
2251 (
2252 i_variable_level IN number,
2253 i_variable_name IN varchar2
2254 )
2255 is
2256 var_present BOOLEAN := FALSE;
2257 o_stack_pos pls_integer;
2258 o_plsql_pos pls_integer;
2259 BEGIN
2260 if EC_DEBUG.G_debug_level >= 2 then
2261 ec_debug.push('EC_UTILS.IF_NULL_SKIP_DOCUMENT');
2262 ec_debug.pl(3,'i_variable_level',i_variable_level);
2263 ec_debug.pl(3,'i_variable_name',i_variable_name);
2264 end if;
2265
2266 var_present := find_variable
2267 (
2268 i_variable_level,
2269 i_variable_name,
2270 o_stack_pos,
2271 o_plsql_pos
2272 );
2273
2274 if NOT ( var_present )
2275 then
2276 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2277 i_ret_code := 2;
2278 raise EC_UTILS.PROGRAM_EXIT;
2279 end if;
2280
2281 if i_variable_level = 0
2282 then
2283 if g_stack(o_stack_pos).variable_value is null
2284 then
2285 ec_debug.pl(0,'EC','ECE_MANDATORY_NULL','VARIABLE_NAME',i_variable_name);
2286 g_ext_levels(g_current_level).Status := 'SKIP_DOCUMENT';
2287 i_ret_code :=1;
2288 end if;
2289 else
2290 if g_file_tbl(o_plsql_pos).value is null
2291 then
2292 ec_debug.pl(0,'EC','ECE_MANDATORY_NULL','VARIABLE_NAME',i_variable_name);
2293 g_ext_levels(g_current_level).Status := 'SKIP_DOCUMENT';
2294 i_ret_code :=1;
2295 end if;
2296 end if;
2297 if EC_DEBUG.G_debug_level >= 2 then
2298 ec_debug.pop('EC_UTILS.IF_NULL_SKIP_DOCUMENT');
2299 end if;
2300 EXCEPTION
2301 WHEN EC_UTILS.PROGRAM_EXIT then
2302 raise;
2303 WHEN OTHERS THEN
2304 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_NULL_SKIP_DOCUMENT');
2305 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2306 i_ret_code := 2;
2307 raise EC_UTILS.PROGRAM_EXIT;
2308 END if_null_skip_document;
2309
2310 /**
2311 Increment the value of the variable by 1.
2312 e.g. i := i + 1;
2313 **/
2314 procedure increment_by_one
2315 (
2316 i_variable_level IN number,
2317 i_variable_name IN varchar2
2318 )
2319 is
2320 var_present BOOLEAN := FALSE;
2321 o_stack_pos pls_integer;
2322 o_plsql_pos pls_integer;
2323 BEGIN
2324 if EC_DEBUG.G_debug_level >= 2 then
2325 ec_debug.push('EC_UTILS.INCREMENT_BY_ONE');
2326 ec_debug.pl(3,'i_variable_level',i_variable_level);
2327 ec_debug.pl(3,'i_variable_name',i_variable_name);
2328 end if;
2329 var_present := find_variable
2330 (
2331 i_variable_level,
2332 i_variable_name,
2333 o_stack_pos,
2334 o_plsql_pos
2335 );
2336
2337 if ( var_present )
2338 then
2339 if i_variable_level = 0
2340 then
2341 g_stack(o_stack_pos).variable_value
2342 := g_stack(o_stack_pos).variable_value + 1;
2343 if EC_DEBUG.G_debug_level >= 3 then
2344 ec_debug.pl(3,i_variable_name,g_stack(o_stack_pos).variable_value);
2345 end if;
2346 else
2347 g_file_tbl(o_plsql_pos).value
2348 := g_file_tbl(o_plsql_pos).value + 1;
2349 if EC_DEBUG.G_debug_level >= 3 then
2350 ec_debug.pl(3,i_variable_name,g_file_tbl(o_plsql_pos).value);
2351 end if;
2352 end if;
2353 else
2354 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2355 i_ret_code := 2;
2356 raise EC_UTILS.PROGRAM_EXIT;
2357 end if;
2358 if EC_DEBUG.G_debug_level >= 2 then
2359 ec_debug.pop('EC_UTILS.INCREMENT_BY_ONE');
2360 end if;
2361 EXCEPTION
2362 WHEN EC_UTILS.PROGRAM_EXIT then
2363 raise;
2364 WHEN OTHERS THEN
2365 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.INCREMENT_BY_ONE');
2366 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2367 i_ret_code := 2;
2368 raise EC_UTILS.PROGRAM_EXIT;
2369 END increment_by_one;
2370
2371 /**
2372 Creates the Columns in the PL/SQL table required by the Open Interface Tables but are not
2373 present on the Spreasheet. If the Function is defined , then it assigns
2374 the value of the Function or assigns the Default Value.
2375 e.g. CREATION_DATE := FND_GLOBAL.SYSDATE;
2376 **/
2377
2378 procedure create_mandatory_columns
2379 (
2380 i_variable_level IN number,
2381 i_previous_variable_level IN number,
2382 i_variable_name IN varchar2,
2383 i_default_value IN varchar2,
2384 i_data_type IN varchar2,
2385 i_function_name IN varchar2
2386 )
2387 is
2388 m_count pls_integer :=g_file_tbl.COUNT;
2392 BEGIN
2389 hash_val pls_integer; -- 2996147
2390 hash_string varchar2(3200);
2391 p_count pls_integer :=0; -- 2996147
2393 if EC_DEBUG.G_debug_level >= 2 then
2394 ec_debug.push('EC_UTILS.CREATE_MANDATORY_COLUMNS');
2395 ec_debug.pl(3,'i_variable_level',i_variable_level);
2396 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
2397 ec_debug.pl(3,'i_variable_name',i_variable_name);
2398 ec_debug.pl(3,'i_default_value',i_default_value);
2399 ec_debug.pl(3,'i_data_type',i_data_type);
2400 ec_debug.pl(3,'i_function_name',i_function_name);
2401 end if;
2402 m_count := m_count + 1;
2403 g_file_tbl(m_count).interface_level := i_variable_level;
2404 g_file_tbl(m_count).external_level := i_previous_variable_level;
2405 g_file_tbl(m_count).base_column_name := upper(i_variable_name);
2406 g_file_tbl(m_count).interface_column_name := upper(i_variable_name);
2407 g_file_tbl(m_count).data_type := upper(i_data_type);
2408 g_file_tbl(m_count).value := i_default_value;
2409
2410 if i_previous_variable_level is null
2411 then
2412 g_file_tbl(m_count).external_level := i_variable_level;
2413 end if;
2414
2415 if i_function_name is not null
2416 then
2417 get_function_value
2418 (
2419 i_function_name,
2420 g_file_tbl(m_count).value
2421 );
2422 end if;
2423 -- 2996147
2424 hash_string:=to_char( g_file_tbl(m_count).external_level)||'-'||upper(g_file_tbl(m_count).interface_column_name);
2425 hash_val := dbms_utility.get_hash_value(hash_string,1,8192);
2426 if ec_utils.g_col_pos_tbl_1.exists(hash_val) then
2427 if ec_utils.g_col_pos_tbl_1(hash_val).occr=1 then
2428 p_count:=ec_utils.g_col_pos_tbl_1(hash_val).value;
2429 ec_utils.g_col_pos_tbl_2(p_count):=hash_val;
2430 ec_utils.g_col_pos_tbl_1(hash_val).value:=0;
2431 ec_utils.g_col_pos_tbl_1(hash_val).start_pos:=p_count;
2432 end if;
2433 ec_utils.g_col_pos_tbl_1(hash_val).occr:=
2434 ec_utils.g_col_pos_tbl_1(hash_val).occr +1;
2435 ec_utils.g_col_pos_tbl_2(m_count):=hash_val;
2436 else
2437 ec_utils.g_col_pos_tbl_1(hash_val).occr:=1;
2438 ec_utils.g_col_pos_tbl_1(hash_val).value:=m_count;
2439 end if;
2440 -- 2996147
2441 if EC_DEBUG.G_debug_level >= 3 then
2442 ec_debug.pl(3,i_variable_name,g_file_tbl(m_count).value);
2443 end if;
2444 if EC_DEBUG.G_debug_level >= 2 then
2445 ec_debug.pop('EC_UTILS.CREATE_MANDATORY_COLUMNS');
2446 end if;
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.CREATE_MANDATORY_COLUMNS');
2450 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2451 i_ret_code := 2;
2452 raise EC_UTILS.PROGRAM_EXIT;
2453 end create_mandatory_columns;
2454
2455 procedure append_clause
2456 (
2457 i_level In number,
2458 i_where_clause IN varchar2
2459 )
2460 is
2461 var_present BOOLEAN := FALSE;
2462 o_stack_pos pls_integer;
2463 o_plsql_pos pls_integer;
2464 BEGIN
2465 if EC_DEBUG.G_debug_level >= 2 then
2466 ec_debug.push('EC_UTILS.APPEND_CLAUSE');
2467 ec_debug.pl(3,'i_level',i_level);
2468 ec_debug.pl(3,'i_where_clause',i_where_clause);
2469 end if;
2470 g_int_levels(i_level).sql_stmt :=
2471 g_int_levels(i_level).sql_stmt ||' '|| i_where_clause;
2472 if EC_DEBUG.G_debug_level >= 3 then
2473 ec_debug.pl(3,'i_where_clause',
2474 g_int_levels(i_level).sql_stmt);
2475 end if;
2476 if EC_DEBUG.G_debug_level >= 2 then
2477 ec_debug.pop('EC_UTILS.APPEND_CLAUSE');
2478 end if;
2479 EXCEPTION
2480 WHEN OTHERS THEN
2481 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.APPEND_CLAUSE');
2482 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2483 i_ret_code := 2;
2484 raise EC_UTILS.PROGRAM_EXIT;
2485 END append_clause;
2486
2487 procedure if_Notnull_append_clause
2488 (
2489 i_level IN number,
2490 i_variable_level IN number,
2491 i_variable_name IN varchar2,
2492 i_where_clause IN varchar2
2493 )
2494 is
2495 var_present BOOLEAN := FALSE;
2496 o_stack_pos pls_integer;
2497 o_plsql_pos pls_integer;
2498 BEGIN
2499 if EC_DEBUG.G_debug_level >= 2 then
2500 ec_debug.push('EC_UTILS.IF_NOTNULL_APPEND_CLAUSE');
2501 ec_debug.pl(3,'i_level',i_level);
2502 ec_debug.pl(3,'i_variable_level',i_variable_level);
2503 ec_debug.pl(3,'i_variable_name',i_variable_name);
2504 ec_debug.pl(3,'i_where_clause',i_where_clause);
2505 end if;
2506
2507 var_present := find_variable
2508 (
2509 i_variable_level,
2510 i_variable_name,
2511 o_stack_pos,
2512 o_plsql_pos
2513 );
2514
2515
2516 if ( var_present )
2517 then
2518 if g_stack(o_stack_pos).variable_value is not null
2519 then
2520 g_int_levels(i_level).sql_stmt
2521 := g_int_levels(i_level).sql_stmt
2522 ||' '||i_where_clause;
2523 end if;
2524
2525 else
2526 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2527 i_ret_code := 2;
2531 ec_debug.pl(3,'i_where_clause',g_int_levels(i_level).sql_stmt);
2528 raise EC_UTILS.PROGRAM_EXIT;
2529 end if;
2530
2532 if EC_DEBUG.G_debug_level >= 2 then
2533 ec_debug.pop('EC_UTILS.IF_NOTNULL_APPEND_CLAUSE');
2534 end if;
2535 EXCEPTION
2536 WHEN EC_UTILS.PROGRAM_EXIT then
2537 raise;
2538 WHEN OTHERS THEN
2539 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IF_NOTNULL_APPEND_CLAUSE');
2540 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2541 i_ret_code := 2;
2542 raise EC_UTILS.PROGRAM_EXIT;
2543 END if_notnull_append_clause;
2544
2545 procedure bind_variables_for_view
2546 (
2547 i_variable_name IN varchar2,
2548 i_previous_variable_level IN integer,
2549 i_previous_variable_name IN varchar2
2550 )
2551 is
2552 var_present BOOLEAN := FALSE;
2553 o_stack_pos pls_integer;
2554 o_plsql_pos pls_integer;
2555 i_date date;
2556 i_number number;
2557 BEGIN
2558 if EC_DEBUG.G_debug_level >= 2 then
2559 ec_debug.push('EC_UTILS.BIND_VARIABLES_FOR_VIEW');
2560 ec_debug.pl(3,'i_variable_name',i_variable_name);
2561 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
2562 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
2563 end if;
2564 var_present := find_variable
2565 (
2566 i_previous_variable_level,
2567 i_previous_variable_name,
2568 o_stack_pos,
2569 o_plsql_pos
2570 );
2571
2572 if ( var_present )
2573 then
2574 if EC_DEBUG.G_debug_level >= 3 then
2575 ec_debug.pl(3,'Select Stmt',g_int_levels(g_current_level).sql_stmt);
2576 end if;
2577 if i_previous_variable_level = 0
2578 then
2579 if EC_DEBUG.G_debug_level >= 3 then
2580 ec_debug.pl(3,'Current Level and Cursor '||g_current_level||' '||
2581 g_int_levels(g_current_level).Cursor_Handle,g_stack(o_stack_pos).variable_value);
2582 end if;
2583
2584 if g_stack(o_stack_pos).variable_value is not null
2585 then
2586
2587 if g_stack(o_stack_pos).data_type = 'NUMBER'
2588 then
2589 i_number := to_number(g_stack(o_stack_pos).variable_value);
2590 dbms_sql.bind_variable
2591 (
2592 g_int_levels(g_current_level).Cursor_Handle,
2593 i_variable_name,
2594 i_number
2595 );
2596 if EC_DEBUG.G_debug_level >= 3 then
2597 ec_debug.pl(3,'Binding Value ',i_number);
2598 end if;
2599 elsif g_stack(o_stack_pos).data_type = 'DATE'
2600 then
2601 /* Bug 2463916
2602 Bug 5763541
2603 i_date := to_date(g_stack(o_stack_pos).variable_value,'DD-MM-RR'); */
2604
2605 i_date := FND_CONC_DATE.string_to_date(g_stack(o_stack_pos).variable_value);
2606 dbms_sql.bind_variable
2607 (
2608 g_int_levels(g_current_level).Cursor_Handle,
2609 i_variable_name,
2610 i_date
2611 );
2612 if EC_DEBUG.G_debug_level >= 3 then
2613 ec_debug.pl(3,'Binding Value ',i_date);
2614 end if;
2615 else
2616 dbms_sql.bind_variable
2617 (
2618 g_int_levels(g_current_level).Cursor_Handle,
2619 i_variable_name,
2620 g_stack(o_stack_pos).variable_value
2621 );
2622 if EC_DEBUG.G_debug_level >= 3 then
2623 ec_debug.pl(3,'Binding Value ',g_stack(o_stack_pos).variable_value);
2624 end if;
2625 end if;
2626 end if;
2627 else
2628 dbms_sql.bind_variable
2629 (
2630 g_int_levels(g_current_level).Cursor_Handle,
2631 i_variable_name,
2632 g_file_tbl(o_plsql_pos).value
2633 );
2634 if EC_DEBUG.G_debug_level >= 3 then
2635 ec_debug.pl(3,'Binding Value ',g_file_tbl(o_plsql_pos).value);
2636 end if;
2637 end if;
2638
2639 else
2640 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2641 i_ret_code := 2;
2642 raise EC_UTILS.PROGRAM_EXIT;
2643 end if;
2644 if EC_DEBUG.G_debug_level >= 2 then
2645 ec_debug.pop('EC_UTILS.BIND_VARIABLES_FOR_VIEW');
2646 end if;
2647 EXCEPTION
2648 WHEN EC_UTILS.PROGRAM_EXIT then
2649 raise;
2650 WHEN OTHERS THEN
2651 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.BIND_VARIABLES_FOR_VIEW');
2652 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2653 i_ret_code := 2;
2654 raise EC_UTILS.PROGRAM_EXIT;
2655 END bind_variables_for_view;
2656
2657 /**
2658 Executes a given stored procedure or a function with parameters of following datatype :
2659 1. Date
2660 2. Number
2661 3. varchar2
2662 4. Char.
2663 Other types are not supported at this point of time because PL/SQL language does not support
2664 dynamic binding of other types of variables. Probably we should use Java and do it in next release.
2665 **/
2666 procedure execute_proc
2667 (
2668 i_transtage_id IN number,
2669 i_procedure_name IN varchar2
2670 )
2671 is
2675 BEGIN
2672 var_present BOOLEAN := FALSE;
2673 o_stack_pos pls_integer;
2674 o_plsql_pos pls_integer;
2676 if EC_DEBUG.G_debug_level >= 2 then
2677 ec_debug.push('EC_UTILS.EXECUTE_PROC');
2678 ec_debug.pl(3,'i_transtage_id',i_transtage_id);
2679 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
2680 end if;
2681
2682 ec_execution_utils.assign_values
2683 (
2684 i_transtage_id,
2685 i_procedure_name,
2686 1060
2687 );
2688
2689 ec_execution_utils.runproc
2690 (
2691 i_procedure_name
2692 );
2693
2694 ec_execution_utils.assign_values
2695 (
2696 i_transtage_id,
2697 i_procedure_name,
2698 1070
2699 );
2700 if EC_DEBUG.G_debug_level >= 2 then
2701 ec_debug.pop('EC_UTILS.EXECUTE_PROC');
2702 end if;
2703 EXCEPTION
2704 WHEN OTHERS THEN
2705 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXECUTE_PROC');
2706 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2707 i_ret_code := 2;
2708 raise EC_UTILS.PROGRAM_EXIT;
2709 END execute_proc;
2710
2711 /**
2712 Execute the Stored procedure or function only when x is null.
2713 i.e.
2714 If x is null
2715 then
2716 execute_procedure;
2717 end if;
2718 **/
2719 procedure ifxnull_execute_proc
2720 (
2721 i_variable_level IN number,
2722 i_variable_name IN varchar2,
2723 i_transtage_id IN number,
2724 i_procedure_name IN varchar2
2725 )
2726 is
2727 var_present BOOLEAN := FALSE;
2728 o_stack_pos pls_integer;
2729 o_plsql_pos pls_integer;
2730 BEGIN
2731 if EC_DEBUG.G_debug_level >= 2 then
2732 ec_debug.push('EC_UTILS.IFXNULL_EXECUTE_PROC');
2733 ec_debug.pl(3,'i_variable_level',i_variable_level);
2734 ec_debug.pl(3,'i_variable_name',i_variable_name);
2735 ec_debug.pl(3,'i_transtage_id',i_transtage_id);
2736 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
2737 end if;
2738
2739 var_present :=find_variable
2740 (
2741 i_variable_level,
2742 i_variable_name,
2743 o_stack_pos,
2744 o_plsql_pos
2745 );
2746 if NOT ( var_present)
2747 then
2748 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2749 i_ret_code := 2;
2750 raise EC_UTILS.PROGRAM_EXIT;
2751 end if;
2752
2753 if i_variable_level = 0
2754 then
2755 if g_stack(o_stack_pos).variable_value is null
2756 then
2757 execute_proc
2758 (
2759 i_transtage_id,
2760 i_procedure_name
2761 );
2762 end if;
2763 else
2764 if g_file_tbl(o_plsql_pos).value is null
2765 then
2766 execute_proc
2767 (
2768 i_transtage_id,
2769 i_procedure_name
2770 );
2771 end if;
2772 end if;
2773 if EC_DEBUG.G_debug_level >= 2 then
2774 ec_debug.pop('EC_UTILS.IFXNULL_EXECUTE_PROC');
2775 end if;
2776 EXCEPTION
2777 WHEN EC_UTILS.PROGRAM_EXIT then
2778 raise;
2779 WHEN OTHERS THEN
2780 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IFXNULL_EXECUTE_PROC');
2781 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2782 i_ret_code := 2;
2783 raise EC_UTILS.PROGRAM_EXIT;
2784 END ifxnull_execute_proc;
2785
2786 /**
2787 Execute the Stored procedure or function only when x is null.
2788 i.e.
2789 If x is not null
2790 then
2791 execute_procedure;
2792 end if;
2793 **/
2794 procedure ifxnotnull_execute_proc
2795 (
2796 i_variable_level IN number,
2797 i_variable_name IN varchar2,
2798 i_transtage_id IN number,
2799 i_procedure_name IN varchar2
2800 )
2801 is
2802 var_present BOOLEAN := FALSE;
2803 o_stack_pos pls_integer;
2804 o_plsql_pos pls_integer;
2805 BEGIN
2806 if EC_DEBUG.G_debug_level >= 2 then
2807 ec_debug.push('EC_UTILS.IFXNOTNULL_EXECUTE_PROC');
2808 ec_debug.pl(3,'i_variable_level',i_variable_level);
2809 ec_debug.pl(3,'i_variable_name',i_variable_name);
2810 ec_debug.pl(3,'i_transtage_id',i_transtage_id);
2811 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
2812 end if;
2813
2814 var_present :=find_variable
2815 (
2816 i_variable_level,
2817 i_variable_name,
2818 o_stack_pos,
2819 o_plsql_pos
2820 );
2821 if NOT ( var_present)
2822 then
2823 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2824 i_ret_code := 2;
2825 raise EC_UTILS.PROGRAM_EXIT;
2826 end if;
2827
2828 if i_variable_level = 0
2829 then
2830 if g_stack(o_stack_pos).variable_value is not null
2831 then
2832 execute_proc
2833 (
2834 i_transtage_id,
2835 i_procedure_name
2836 );
2837 end if;
2838 else
2839 if g_file_tbl(o_plsql_pos).value is not null
2840 then
2841 execute_proc
2842 (
2843 i_transtage_id,
2844 i_procedure_name
2845 );
2846 end if;
2847 end if;
2848 if EC_DEBUG.G_debug_level >= 2 then
2849 ec_debug.pop('EC_UTILS.IFXNOTNULL_EXECUTE_PROC');
2850 end if;
2851 EXCEPTION
2855 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IFXNOTNULL_EXECUTE_PROC');
2852 WHEN EC_UTILS.PROGRAM_EXIT then
2853 raise;
2854 WHEN OTHERS THEN
2856 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2857 i_ret_code := 2;
2858 raise EC_UTILS.PROGRAM_EXIT;
2859 END ifxnotnull_execute_proc;
2860
2861 /**
2862 Execute the Stored procedure or function only when x = default value.
2863 i.e.
2864 If x = 5
2865 then
2866 execute_procedure;
2867 end if;
2868 **/
2869 procedure ifxconst_execute_proc
2870 (
2871 i_variable_level IN number,
2872 i_variable_name IN varchar2,
2873 i_default_value IN varchar2,
2874 i_transtage_id IN number,
2875 i_procedure_name IN varchar2
2876 )
2877 is
2878 var_present BOOLEAN := FALSE;
2879 o_stack_pos pls_integer;
2880 o_plsql_pos pls_integer;
2881 BEGIN
2882 if EC_DEBUG.G_debug_level >= 2 then
2883 ec_debug.push('EC_UTILS.IFXCONST_EXECUTE_PROC');
2884 ec_debug.pl(3,'i_variable_level',i_variable_level);
2885 ec_debug.pl(3,'i_variable_name',i_variable_name);
2886 ec_debug.pl(3,'i_default_value',i_default_value);
2887 ec_debug.pl(3,'i_transtage_id',i_transtage_id);
2888 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
2889 end if;
2890 var_present :=find_variable
2891 (
2892 i_variable_level,
2893 i_variable_name,
2894 o_stack_pos,
2895 o_plsql_pos
2896 );
2897 if NOT ( var_present)
2898 then
2899 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2900 i_ret_code := 2;
2901 raise EC_UTILS.PROGRAM_EXIT;
2902 end if;
2903
2904 if i_variable_level = 0
2905 then
2906 if g_stack(o_stack_pos).variable_value = i_default_value
2907 then
2908 execute_proc
2909 (
2910 i_transtage_id,
2911 i_procedure_name
2912 );
2913 end if;
2914 else
2915 if g_file_tbl(o_plsql_pos).value = i_default_value
2916 then
2917 execute_proc
2918 (
2919 i_transtage_id,
2920 i_procedure_name
2921 );
2922 end if;
2923 end if;
2924 if EC_DEBUG.G_debug_level >= 2 then
2925 ec_debug.pop('EC_UTILS.IFXCONST_EXECUTE_PROC');
2926 end if;
2927 EXCEPTION
2928 WHEN EC_UTILS.PROGRAM_EXIT then
2929 raise;
2930 WHEN OTHERS THEN
2931 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IFXCONST_EXECUTE_PROC');
2932 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2933 i_ret_code := 2;
2934 raise EC_UTILS.PROGRAM_EXIT;
2935 END ifxconst_execute_proc;
2936
2937 /**
2938 if x is equal to y , then execute procedure or function.
2939 i.e. if i = j
2940 then
2941 execute_procedure
2942 end if;
2943 **/
2944 procedure ifxpre_execute_proc
2945 (
2946 i_variable_level IN number,
2947 i_variable_name IN varchar2,
2948 i_previous_variable_level IN number,
2949 i_previous_variable_name IN varchar2,
2950 i_transtage_id IN number,
2951 i_procedure_name IN varchar2
2952 )
2953 is
2954 var_present BOOLEAN := FALSE;
2955 pre_var_present BOOLEAN := FALSE;
2956 o_stack_pos pls_integer;
2957 o_stack_pre_pos pls_integer;
2958 o_plsql_pos pls_integer;
2959 o_plsql_pre_pos pls_integer;
2960 BEGIN
2961 if EC_DEBUG.G_debug_level >= 2 then
2962 ec_debug.push('EC_UTILS.IFXPRE_EXECUTE_PROC');
2963 ec_debug.pl(3,'i_variable_level',i_variable_level);
2964 ec_debug.pl(3,'i_variable_name',i_variable_name);
2965 ec_debug.pl(3,'i_previous_variable_level',i_previous_variable_level);
2966 ec_debug.pl(3,'i_previous_variable_name',i_previous_variable_name);
2967 ec_debug.pl(3,'i_transtage_id',i_transtage_id);
2968 ec_debug.pl(3,'i_procedure_name',i_procedure_name);
2969 end if;
2970 var_present :=find_variable
2971 (
2972 i_variable_level,
2973 i_variable_name,
2974 o_stack_pos,
2975 o_plsql_pos
2976 );
2977 if NOT ( var_present)
2978 then
2979 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME',i_variable_name);
2980 i_ret_code := 2;
2981 raise EC_UTILS.PROGRAM_EXIT;
2982 end if;
2983
2984 pre_var_present := find_variable
2985 (
2986 i_previous_variable_level,
2987 i_previous_variable_name,
2988 o_stack_pre_pos,
2989 o_plsql_pre_pos
2990 );
2991
2992 if NOT ( pre_var_present)
2993 then
2994 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK',
2995 'VARIABLE_NAME',i_previous_variable_name);
2996 i_ret_code := 2;
2997 raise EC_UTILS.PROGRAM_EXIT;
2998 end if;
2999
3000 if i_variable_level = 0
3001 then
3002 if i_previous_variable_level = 0
3003 then
3004 if g_stack(o_stack_pos).variable_value = g_stack(o_stack_pre_pos).variable_value
3005 then
3006 execute_proc
3007 (
3008 i_transtage_id,
3009 i_procedure_name
3010 );
3011 end if;
3012 else
3013 if g_stack(o_stack_pos).variable_value = g_file_tbl(o_plsql_pre_pos).value
3014 then
3015 execute_proc
3016 (
3017 i_transtage_id,
3018 i_procedure_name
3022 else
3019 );
3020 end if;
3021 end if;
3023 if i_previous_variable_level = 0
3024 then
3025 if g_file_tbl(o_plsql_pos).value = g_stack(o_stack_pre_pos).variable_value
3026 then
3027 execute_proc
3028 (
3029 i_transtage_id,
3030 i_procedure_name
3031 );
3032 end if;
3033 else
3034 if g_file_tbl(o_plsql_pos).value = g_file_tbl(o_plsql_pre_pos).value
3035 then
3036 execute_proc
3037 (
3038 i_transtage_id,
3039 i_procedure_name
3040 );
3041 end if;
3042 end if;
3043 end if;
3044 if EC_DEBUG.G_debug_level >= 2 then
3045 ec_debug.pop('EC_UTILS.IFXPRE_EXECUTE_PROC');
3046 end if;
3047 EXCEPTION
3048 WHEN EC_UTILS.PROGRAM_EXIT then
3049 raise;
3050 WHEN OTHERS THEN
3051 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.IFXPRE_EXECUTE_PROC');
3052 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3053 i_ret_code := 2;
3054 raise EC_UTILS.PROGRAM_EXIT;
3055 END ifxpre_execute_proc;
3056
3057 procedure ext_find_position
3058 (
3059 i_level IN number,
3060 i_search_text IN varchar2,
3061 o_pos OUT NOCOPY NUMBER,
3062 i_required IN BOOLEAN DEFAULT TRUE
3063 )
3064 IS
3065 cIn_String varchar2(1000) := UPPER(i_search_text);
3066 bFound BOOLEAN := FALSE;
3067 POS_NOT_FOUND EXCEPTION;
3068 BEGIN
3069 if EC_DEBUG.G_debug_level >= 2 then
3070 ec_debug.PUSH('EC_UTILS.EXT_FIND_POSITION');
3071 ec_debug.pl(3,'i_level',i_level);
3072 ec_debug.pl(3,'i_search_text',i_search_text);
3073 end if;
3074
3075 if g_direction = 'I'
3076 then
3077 for i in g_ext_levels(i_level).file_start_pos..g_ext_levels(i_level).file_end_pos
3078 loop
3079 if upper(g_file_tbl(i).interface_column_name) = cIn_String
3080 then
3081 o_pos := i;
3082 bFound := TRUE;
3083 exit;
3084 end if;
3085 end loop;
3086 else
3087 for i in g_int_levels(i_level).file_start_pos..g_int_levels(i_level).file_end_pos
3088 loop
3089 if upper(g_file_tbl(i).interface_column_name) = cIn_String
3090 then
3091 o_pos := i;
3092 bFound := TRUE;
3093 exit;
3094 end if;
3095 end loop;
3096 end if;
3097
3098 if not bFound
3099 then
3100 if (i_required)
3101 then
3102 raise POS_NOT_FOUND;
3103 else
3104 o_pos := NULL;
3105 end if;
3106 end if;
3107 if EC_DEBUG.G_debug_level >= 2 then
3108 ec_debug.pl(3,'o_pos',o_pos);
3109 ec_debug.POP('EC_UTILS.EXT_FIND_POSITION');
3110 end if;
3111 EXCEPTION
3112 WHEN POS_NOT_FOUND THEN
3113 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME',cIn_String);
3114 ec_debug.POP('EC_UTILS.EXT_FIND_POSITION');
3115 i_ret_code := 2;
3116 raise EC_UTILS.PROGRAM_EXIT;
3117 WHEN OTHERS THEN
3118 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXT_FIND_POSITION');
3119 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3120 i_ret_code := 2;
3121 raise EC_UTILS.PROGRAM_EXIT;
3122 END ext_find_position;
3123
3124 procedure EXT_GET_KEY_VALUE
3125 (
3126 i_position IN number,
3127 o_value OUT NOCOPY varchar2
3128 )
3129 is
3130 o_stack_pos pls_integer;
3131 o_plsql_pos pls_integer;
3132 BEGIN
3133 if EC_DEBUG.G_debug_level >= 2 then
3134 ec_debug.push('EC_UTILS.EXT_GET_KEY_VALUE');
3135 ec_debug.pl(3,'i_position',i_position);
3136 end if;
3137 if i_position is not null then
3138 o_value := g_file_tbl(i_position).value;
3139 if EC_DEBUG.G_debug_level >= 3 then
3140 ec_debug.pl(3,o_value,g_file_tbl(i_position).value);
3141 end if;
3142 end if;
3143 if EC_DEBUG.G_debug_level >= 2 then
3144 ec_debug.pop('EC_UTILS.EXT_GET_KEY_VALUE');
3145 end if;
3146 EXCEPTION
3147 WHEN OTHERS THEN
3148 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXT_GET_KEY_VALUE');
3149 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3150 i_ret_code := 2;
3151 raise EC_UTILS.PROGRAM_EXIT;
3152 END EXT_GET_KEY_VALUE;
3153
3154 procedure EXT_INSERT_VALUE
3155 (
3156 i_position IN number,
3157 i_value IN varchar2
3158 )
3159 is
3160 o_stack_pos pls_integer;
3161 o_plsql_pos pls_integer;
3162 BEGIN
3163 if EC_DEBUG.G_debug_level >= 2 then
3164 ec_debug.push('EC_UTILS.EXT_INSERT_VALUE');
3165 ec_debug.pl(3,'i_position',i_position);
3166 ec_debug.pl(3,'i_value',i_value);
3167 end if;
3168 if i_position is not null then
3169 g_file_tbl(i_position).value := i_value;
3170 ec_debug.pl(3,'i_value_put',g_file_tbl(i_position).value);
3171 end if;
3172
3173 if EC_DEBUG.G_debug_level >= 2 then
3174 ec_debug.pop('EC_UTILS.EXT_INSERT_VALUE');
3175 end if;
3176 EXCEPTION
3177 WHEN OTHERS THEN
3178 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_UTILS.EXT_INSERT_VALUE');
3179 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3180 i_ret_code := 2;
3181 raise EC_UTILS.PROGRAM_EXIT;
3182 END EXT_INSERT_VALUE;
3183
3184 end ec_utils;