[Home] [Help]
PACKAGE BODY: APPS.ECX_ACTIONS
Source
1 package body ecx_actions as
2 -- $Header: ECXACTNB.pls 120.12 2006/07/07 10:00:09 gsingh ship $
3
4 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
5 l_statement PLS_INTEGER := ecx_debug.g_statement;
6 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
7 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
8 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
9 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
10
11 ASSIGN_DEFAULT CONSTANT pls_integer :=20;
12 ASSIGN_PRE_DEFINED CONSTANT pls_integer :=30;
13 ASSIGN_NEXTVALUE CONSTANT pls_integer :=40;
14 ASSIGN_FUNCVAL CONSTANT pls_integer :=100;
15 APPEND_WHERECLAUSE CONSTANT pls_integer := 1000;
16 EXEC_PROCEDURES CONSTANT pls_integer := 1050;
17 SEND_ERROR CONSTANT pls_integer := 1120;
18 API_RETURN_CODE CONSTANT pls_integer := 1130;
19 DOC_ID CONSTANT pls_integer := 1140;
20 CONV_TO_OAGDATE CONSTANT pls_integer := 2000;
21 CONV_TO_OAGOPERAMT CONSTANT pls_integer := 2010;
22 CONV_TO_OAGQUANT CONSTANT pls_integer := 2020;
23 CONV_FROM_OAGDATE CONSTANT pls_integer := 2030;
24 CONV_FROM_OAGOPERAMT CONSTANT pls_integer := 2040;
25 CONV_FROM_OAGQUANT CONSTANT pls_integer := 2050;
26 CONV_TO_OAGAMT CONSTANT pls_integer := 2100;
27 CONV_FROM_OAGAMT CONSTANT pls_integer := 2110;
28 INSERT_INTO_OPEN_INTERFACE CONSTANT pls_integer := 3000;
29 EXITPROGRAM CONSTANT pls_integer := 3020;
30 SUBSTR_VAR CONSTANT pls_integer := 3030;
31 CONCAT_VAR CONSTANT pls_integer := 3040;
32 ADD CONSTANT pls_integer := 4000;
33 SUB CONSTANT pls_integer := 4010;
34 MUL CONSTANT pls_integer := 4020;
35 DIV CONSTANT pls_integer := 4030;
36 XSLT_TRANSFORM CONSTANT pls_integer := 5000;
37 GET_ADDRESS_ID CONSTANT pls_integer := 6000;
38
39 procedure get_var_attr (
40 i_variable_level IN pls_integer,
41 i_variable_name IN Varchar2,
42 i_variable_direction IN VARCHAR2,
43 i_variable_pos IN pls_integer,
44 x_value OUT NOCOPY Varchar2,
45 x_stack_var OUT NOCOPY Boolean,
46 x_stack_pos OUT NOCOPY pls_integer) IS
47
48 i_method_name varchar2(2000) := 'ecx_actions.get_var_attr';
49 l_var_present Boolean := FALSE;
50 l_dummy pls_integer;
51
52 BEGIN
53 if (l_procedureEnabled) then
54 ecx_debug.push(i_method_name);
55 end if;
56 if(l_statementEnabled) then
57 ecx_debug.log(l_statement,'i_variable_level', i_variable_level,i_method_name);
58 ecx_debug.log(l_statement,'i_variable_name', i_variable_name,i_method_name);
59 ecx_debug.log(l_statement,'i_variable_pos', i_variable_pos,i_method_name);
60 ecx_debug.log(l_statement,'i_variable_direction', i_variable_direction,i_method_name);
61 end if;
62
63 --if (i_variable_level = 0 ) then
64 if ( i_variable_direction = 'G' )
65 then
66 l_var_present := find_stack_variable (i_variable_name,
67 x_stack_pos);
68 if (l_var_present) then
69 x_stack_var := TRUE;
70 x_value := ecx_utils.g_stack(x_stack_pos).variable_value;
71 else
72 if(l_statementEnabled) then
73 ecx_debug.log(l_statement,'ECX', 'ECX_VARIABLE_NOT_ON_STACK', i_method_name,
74 'VARIABLE_NAME',i_variable_name);
75 end if;
76 ecx_debug.setErrorInfo(2,30,'ECX_STACKVAR_NOT_FOUND');
77 raise ecx_utils.PROGRAM_EXIT;
78 end if;
79 else
80 /* Get the Value based on the direction */
81 if ( i_variable_direction = 'S' )
82 then
83 x_value := ecx_utils.g_source(i_variable_pos).value;
84 else
85 x_value := ecx_utils.g_target(i_variable_pos).value;
86 end if;
87 x_stack_var := FALSE;
88 end if;
89
90 if(l_statementEnabled) then
91 ecx_debug.log(l_statement,'x_value', x_value,i_method_name);
92 ecx_debug.log(l_statement,'x_stack_var', x_stack_var,i_method_name);
93 ecx_debug.log(l_statement,'x_stack_pos', x_stack_pos,i_method_name);
94 end if;
95
96 if (l_procedureEnabled) then
97 ecx_debug.pop(i_method_name);
98 end if;
99
100 EXCEPTION
101 WHEN ecx_utils.PROGRAM_EXIT then
102 if (l_procedureEnabled) then
103 ecx_debug.pop(i_method_name);
104 end if;
105 raise;
106
107 WHEN OTHERS THEN
108 if(l_unexpectedEnabled) then
109 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
110 'ECX_ACTIONS.GET_VAR_ATTR');
111 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE', SQLERRM);
112 end if;
113 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_VAR_ATTR');
114 if (l_procedureEnabled) then
115 ecx_debug.pop(i_method_name);
116 end if;
117 raise ecx_utils.PROGRAM_EXIT;
118
119 END get_var_attr;
120
121 procedure assign_value (
122 i_stack_var IN Boolean,
123 i_stack_pos IN pls_integer,
124 i_plsql_pos IN pls_integer,
125 i_direction IN varchar2,
126 i_value IN Varchar2) IS
127 i_method_name varchar2(2000) := 'ecx_actions.assign_value';
128 BEGIN
129 if (l_procedureEnabled) then
130 ecx_debug.push(i_method_name);
131 end if;
132
133 if(l_statementEnabled) then
134 ecx_debug.log(l_statement,'i_stack_var', i_stack_var,i_method_name);
135 ecx_debug.log(l_statement,'i_stack_pos', i_stack_pos,i_method_name);
136 ecx_debug.log(l_statement,'i_plsql_pos', i_plsql_pos,i_method_name);
137 ecx_debug.log(l_statement,'i_value', i_value,i_method_name);
138 ecx_debug.log(l_statement,'i_direction', i_direction,i_method_name);
139 end if;
140
141 if (i_stack_var) then
142 ecx_utils.g_stack(i_stack_pos).variable_value := i_value;
143 else
144 /* Based on the direction look into either Source or Target Struct */
145 if ( i_direction = 'S' )
146 then
147 ecx_utils.g_source(i_plsql_pos).value := i_value;
148 else
149 ecx_utils.g_target(i_plsql_pos).value := i_value;
150 end if;
151 end if;
152
153 if (l_procedureEnabled) then
154 ecx_debug.pop(i_method_name);
155 end if;
156
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 if(l_unexpectedEnabled) then
161 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
162 'ECX_ACTIONS.ASSIGN_VALUE');
163 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
164 end if;
165 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.ASSIGN_VALUE');
166 if (l_procedureEnabled) then
167 ecx_debug.pop(i_method_name);
168 end if;
169
170 raise ecx_utils.PROGRAM_EXIT;
171
172 END assign_value;
173
174
175 ---This is overloaded procedure to handle clob value
176
177 procedure assign_value (
178 i_stack_var IN Boolean,
179 i_stack_pos IN pls_integer,
180 i_plsql_pos IN pls_integer,
181 i_direction IN varchar2,
182 i_c_value IN Clob) IS
183
184 i_method_name varchar2(2000) := 'ecx_actions.assign_value';
185
186 BEGIN
187 if (l_procedureEnabled) then
188 ecx_debug.push(i_method_name);
189 end if;
190 if(l_statementEnabled) then
191 ecx_debug.log(l_statement,'i_stack_var', i_stack_var,i_method_name);
192 ecx_debug.log(l_statement,'i_stack_pos', i_stack_pos,i_method_name);
193 ecx_debug.log(l_statement,'i_plsql_pos', i_plsql_pos,i_method_name);
194 ecx_debug.log(l_statement,'i_c_value', i_c_value,i_method_name);
195 ecx_debug.log(l_statement,'i_direction', i_direction,i_method_name);
196 end if;
197
198 if (i_stack_var) then
199 /** Change required for Clob Support -- 2263729 ***/
200 /*** Change Storing CLOB as a varchar2***/
201 ecx_utils.g_stack(i_stack_pos).variable_value := dbms_lob.substr(i_c_value,
202 ecx_utils.G_VARCHAR_LEN ,1);
203 else
204 /* Based on the direction look into either Source or Target Struct */
205 if ( i_direction = 'S' )
206 then
207 ecx_utils.g_source(i_plsql_pos).clob_value := i_c_value;
208 ecx_utils.g_source(i_plsql_pos).is_clob := 'N';
209 else
210 ecx_utils.g_target(i_plsql_pos).clob_value := i_c_value;
211 ecx_utils.g_target(i_plsql_pos).is_clob := 'N';
212 end if;
213 end if;
214
215 if (l_procedureEnabled) then
216 ecx_debug.pop(i_method_name);
217 end if;
218
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 if(l_unexpectedEnabled) then
223 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name, 'PROGRESS_LEVEL',
224 'ECX_ACTIONS.ASSIGN_VALUE');
225 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
226 end if;
227 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.ASSIGN_VALUE');
228 if (l_procedureEnabled) then
229 ecx_debug.pop(i_method_name);
230 end if;
231
232 raise ecx_utils.PROGRAM_EXIT;
233
234 END assign_value;
235
236 function find_stack_variable (
237 i_variable_name IN Varchar2,
238 i_stack_pos OUT NOCOPY pls_integer) return Boolean IS
239
240 i_method_name varchar2(2000) := 'ecx_actions.find_stack_variable';
241 bFound Boolean := FALSE;
242
243 BEGIN
244 if (l_procedureEnabled) then
245 ecx_debug.push(i_method_name);
246 end if;
247 if(l_statementEnabled) then
248 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
249 end if;
250
251 for k in 1..ecx_utils.g_stack.count loop
252 if UPPER(ecx_utils.g_stack(k).VARIABLE_NAME) = UPPER(i_variable_name)
253 then
254 i_stack_pos := k;
255 bFound := TRUE;
256 exit;
257 end if;
258 end loop;
259
260 if(l_statementEnabled) then
261 ecx_debug.log(l_statement,'i_stack_pos',i_stack_pos,i_method_name);
262 ecx_debug.log(l_statement,'bFound',bFound,i_method_name);
263 end if;
264 if (l_procedureEnabled) then
265 ecx_debug.pop(i_method_name);
266 end if;
267
268 return bFound;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 if(l_unexpectedEnabled) then
273 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
274 'ECX_ACTIONS.FIND_STACK_VARIABLE');
275 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
276 end if;
277 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.FIND_STACK_VARIABLE');
278 if (l_procedureEnabled) then
279 ecx_debug.pop(i_method_name);
280 end if;
281 raise ecx_utils.PROGRAM_EXIT;
282
286 procedure build_insert_stmt (
283 END find_stack_variable;
284
285
287 p_insert_cursor IN OUT NOCOPY pls_integer,
288 p_level IN pls_integer) IS
289
290 i_method_name varchar2(2000) := 'ecx_actions.build_insert_stmt';
291
292 cInsert_stmt varchar2(32000) := 'INSERT INTO ';
293 cValue_stmt varchar2(32000) := 'VALUES ( ';
294 i pls_integer;
295 l_error_position pls_integer;
296 l_parse_error EXCEPTION;
297
298 BEGIN
299 if (l_procedureEnabled) then
300 ecx_debug.push(i_method_name);
301 end if;
302
303 cInsert_stmt := cInsert_stmt || ' ' ||
304 ecx_utils.g_target_levels(p_level).base_table_name || ' (';
305
306
307 i := ecx_utils.g_target_levels(p_level).file_start_pos;
308 loop
309 if (ecx_utils.g_target(i).internal_level = p_level) and
310 (ecx_utils.g_target(i).base_column_name is not null) then
311 cInsert_stmt := cInsert_stmt || ' ' ||
312 ecx_utils.g_target(i).base_column_name || ',';
313
314 cValue_stmt := cValue_stmt || ':f' || i || ',';
315 end if;
316 exit when i = ecx_utils.g_target_levels(p_level).file_end_pos;
317 i := ecx_utils.g_target.next(i);
318
319 end loop;
320
321 cInsert_stmt := RTRIM(cInsert_stmt, ',') || ')';
322 cValue_stmt := RTRIM(cValue_stmt, ',') || ')';
323 cInsert_stmt := cInsert_stmt || cValue_stmt;
324 if(l_statementEnabled) then
325 ecx_debug.log(l_statement,'Insert_statement ', cInsert_stmt,i_method_name);
326 end if;
327 p_insert_cursor := dbms_sql.open_cursor;
328 if(l_statementEnabled) then
329 ecx_debug.log(l_statement,'p_insert_cursor', p_insert_cursor,i_method_name);
330 end if;
331
332 begin
333 dbms_sql.parse (p_insert_cursor, cInsert_stmt, dbms_sql.native);
334 exception
335 when others then
336 l_error_position := dbms_sql.last_error_position;
337 raise l_parse_error;
338 end;
339
340 if (l_procedureEnabled) then
341 ecx_debug.pop(i_method_name);
342 end if;
343
344 EXCEPTION
345 WHEN l_parse_error then
346 ecx_error_handling_pvt.print_parse_error (l_error_position, cInsert_stmt);
347 if(l_unexpectedEnabled) then
348 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', 'PROGRESS_LEVEL',i_method_name);
349 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
350 end if;
351 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.BUILD_INSERT_STMT');
352 if (l_procedureEnabled) then
353 ecx_debug.pop(i_method_name);
354 end if;
355 raise ecx_utils.program_exit;
356
357 WHEN ecx_utils.program_exit then
358 if (l_procedureEnabled) then
359 ecx_debug.pop(i_method_name);
360 end if;
361 raise;
362
363 WHEN others then
364 if(l_unexpectedEnabled) then
365 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', 'PROGRESS_LEVEL', 'ECX_ACTIONS.BUILD_INSERT_STMT');
366 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
367 end if;
368 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.BUILD_INSERT_STMT');
369
370 if (l_procedureEnabled) then
371 ecx_debug.pop(i_method_name);
372 end if;
373 raise ecx_utils.program_exit;
374
375 END build_insert_stmt;
376
377
378 procedure insert_level_into_table (
379 p_level IN pls_integer) IS
380
381 i_method_name varchar2(2000) := 'ecx_actions.insert_level_into_table';
382
383 l_dummy pls_integer;
384 k pls_integer;
385 l_date DATE;
386 l_number number;
387 l_insert_cursor pls_integer;
388 l_insert_failed EXCEPTION;
392
389 l_data_conversion_failed EXCEPTION;
390 l_clob_value CLOB;
391 l_value varchar2(32767);
393 BEGIN
394
395 if (l_procedureEnabled) then
396 ecx_debug.push(i_method_name);
397 end if;
398 if(l_statementEnabled) then
399 ecx_debug.log(l_statement,'p_level', p_level,i_method_name);
400 end if;
401
402 if (ecx_utils.g_target_levels(p_level).cursor_handle = 0 or
403 ecx_utils.g_target_levels(p_level).cursor_handle is null) then
404 build_insert_stmt (ecx_utils.g_target_levels(p_level).cursor_handle, p_level);
405 end if;
406
407 l_insert_cursor := ecx_utils.g_target_levels(p_level).cursor_handle;
408
409 -- bind insert_statement
410
411 k := ecx_utils.g_target_levels(p_level).file_start_pos;
412 loop
413 if (ecx_utils.g_target(k).internal_level = p_level) and
414 (ecx_utils.g_target(k).base_column_name is not null) then
415
416 BEGIN
417
418 if ecx_utils.g_target(k).data_type = 12 then
419 if ecx_utils.g_target(k).value is null then
420 l_date := null;
421 else
422 l_date := to_date(ecx_utils.g_target(k).value,
423 'YYYYMMDD HH24MISS');
424 end if;
425 if(l_statementEnabled) then
426 ecx_debug.log(l_statement,ecx_utils.g_target(k).base_column_name, l_date,i_method_name);
427 end if;
428 dbms_sql.bind_variable (l_insert_cursor, 'f' || k, l_date);
429
430 elsif ecx_utils.g_target(k).data_type = 2 then
431 if ecx_utils.g_target(k).value is null then
432 l_number := null;
433 else
434 l_number := to_number(ecx_utils.g_target(k).value);
435 end if;
436 if(l_statementEnabled) then
437 ecx_debug.log(l_statement,ecx_utils.g_target(k).base_column_name, l_number,i_method_name);
438 end if;
439 dbms_sql.bind_variable (l_insert_cursor, 'f' || k, l_number);
440
441 -- Target is a CLOB datatype
442 elsif ecx_utils.g_target(k).data_type = 112 then
443
444 get_clob (ecx_utils.g_target(k).clob_value ,
445 ecx_utils.g_target(k).value,
446 l_clob_value);
447
448 dbms_sql.bind_variable (l_insert_cursor, 'f' || k,l_clob_value );
449
450 if ecx_utils.g_target(k).clob_value is not null Then
451 if(l_statementEnabled) then
452 ecx_debug.log(l_statement,ecx_utils.g_target(k).base_column_name,
453 ecx_utils.g_target(k).clob_value,
454 i_method_name);
455 end if;
456 else
457 if(l_statementEnabled) then
458 ecx_debug.log(l_statement,ecx_utils.g_target(k).base_column_name, ecx_utils.g_target(k).value,
459 i_method_name);
460 end if;
461 end if;
462
463 -- Target data type is VARCHAR2
464 else
465 get_varchar(ecx_utils.g_target(k).clob_value ,
466 ecx_utils.g_target(k).value,
467 l_value);
468
469 dbms_sql.bind_variable (l_insert_cursor, 'f' || k,l_value);
470
471
472 if ecx_utils.g_target(k).clob_value is not null Then
473 if(l_statementEnabled) then
474 ecx_debug.log(l_statement,ecx_utils.g_target(k).base_column_name,
475 ecx_utils.g_target(k).clob_value,i_method_name);
476 end if;
477 else
478 if(l_statementEnabled) then
479 ecx_debug.log(l_statement,ecx_utils.g_target(k).base_column_name, ecx_utils.g_target(k).value,
480 i_method_name);
481 end if;
482 end if;
483
484 end if;
485
486 EXCEPTION
487 WHEN others then
488 if dbms_lob.istemporary(l_clob_value) = 1 Then
489 dbms_lob.freetemporary(l_clob_value);
490 end if;
491
492 raise l_data_conversion_failed;
493 END;
494
495 end if;
496 exit when k = ecx_utils.g_target_levels(p_level).file_end_pos;
497 k := ecx_utils.g_target.next(k);
501
498 if dbms_lob.istemporary(l_clob_value) = 1 Then
499 dbms_lob.freetemporary(l_clob_value);
500 end if;
502 end loop;
503
504 l_dummy := dbms_sql.execute(l_insert_cursor);
505 if (l_dummy <> 1) then
506 raise l_insert_failed;
507 end if;
508
509 if dbms_lob.istemporary(l_clob_value) = 1 Then
510 dbms_lob.freetemporary(l_clob_value);
511 end if;
512
513 if (l_procedureEnabled) then
514 ecx_debug.pop(i_method_name);
515 end if;
516
517 EXCEPTION
518 WHEN l_data_conversion_failed then
519 if(l_unexpectedEnabled) then
520 ecx_debug.log(l_unexpected , 'ECX', 'ECX_DATATYPE_CONVERSION_FAILED',i_method_name, 'DATATYPE',
521 ecx_utils.g_target(k).data_type);
522 ecx_debug.log(l_unexpected, ecx_utils.g_target(k).base_column_name,
523 'yy'||ecx_utils.g_target(k).value||'xx',i_method_name);
524 end if;
525 ecx_debug.setErrorInfo(2,30,'ECX_DATATYPE_CONV_FAILED');
526 if (l_procedureEnabled) then
527 ecx_debug.pop(i_method_name);
528 end if;
529 raise ecx_utils.program_exit;
530
531 WHEN l_insert_failed then
532 if(l_unexpectedEnabled) then
533 ecx_debug.log(l_unexpected ,'ECX', 'ECX_STAGE_INSERT_FAILED',i_method_name, 'LEVEL', p_level);
534 ecx_debug.log(l_unexpected, 'ECX', 'ECX_PROGRAM_ERROR',i_method_name, 'PROGRESS_LEVEL', 'ECX_ACTIONS.INSERT_LEVEL_INTO_TABLE');
535 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
536 end if;
537 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.INSERT_LEVEL_INTO_TABLE');
538 if (l_procedureEnabled) then
539 ecx_debug.pop(i_method_name);
540 end if;
541 raise ecx_utils.program_exit;
542
543 WHEN ecx_utils.program_exit then
544 if (l_procedureEnabled) then
545 ecx_debug.pop(i_method_name);
546 end if;
547 raise;
548
549 WHEN others then
550 if(l_unexpectedEnabled) then
551 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name, 'PROGRESS_LEVEL', 'ECX_ACTIONS.INSERT_LEVEL_INTO_TABLE');
552 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
553 end if;
554 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.INSERT_LEVEL_INTO_TABLE');
555
556 if (l_procedureEnabled) then
557 ecx_debug.pop(i_method_name);
558 end if;
559 raise ecx_utils.program_exit;
560
561 END insert_level_into_table;
562
563
564 /**
565 Executes a select string and returns the First Column from the select
566 clause as OUT parameter.
567 **/
568 procedure execute_string (
569 cString IN Varchar2,
570 o_value OUT NOCOPY Varchar2) IS
571
572 i_method_name varchar2(2000) := 'ecx_actions.execute_string';
573
574 cursor_handle pls_integer;
575 ret_query pls_integer;
576 m_value Varchar2(20000);
577 m_success Boolean;
578 error_position pls_integer;
579 parse_error EXCEPTION;
580
581 BEGIN
582 if (l_procedureEnabled) then
583 ecx_debug.push(i_method_name);
584 end if;
585
586 if(l_statementEnabled) then
587 ecx_debug.log(l_statement,'cString',cString,i_method_name);
588 end if;
589
590 cursor_handle := dbms_sql.open_cursor;
591
592 BEGIN
593 dbms_sql.parse(cursor_handle,cString,dbms_sql.native);
594
595 EXCEPTION
596 WHEN OTHERS THEN
597 raise parse_error;
598 END;
599
600 dbms_sql.define_column(cursor_handle,1,m_value,20000);
601 ret_query := dbms_sql.execute_and_fetch(cursor_handle,m_success);
602 dbms_sql.column_value(cursor_handle,1,m_value);
603 dbms_sql.close_cursor(cursor_handle);
604 o_value := m_value;
605
606 if(l_statementEnabled) then
607 ecx_debug.log(l_statement,'o_value',o_value,i_method_name);
608 end if;
609 if (l_procedureEnabled) then
610 ecx_debug.pop(i_method_name);
611 end if;
612
613
614 EXCEPTION
615 WHEN PARSE_ERROR then
616 error_position := dbms_sql.last_error_position;
617 ecx_error_handling_pvt.print_parse_error (error_position,cString);
618 if(l_unexpectedEnabled) then
619 ecx_debug.log(l_unexpected, 'ECX', 'ECX_PROGRAM_ERROR', i_method_name, 'PROGRESS_LEVEL', 'ECX_ACTIONS.EXECUTE_STRING');
620 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
621 end if;
622 if dbms_sql.is_open(cursor_handle) then
623 dbms_sql.close_cursor(cursor_handle);
624 end if;
625 ecx_debug.seTErrorInfo(2,30,SQLERRM||' - ECX_UTILS.EXECUTE_STRING');
626 raise ecx_utils.PROGRAM_EXIT;
627
628 WHEN ecx_utils.PROGRAM_EXIT then
629 if (l_procedureEnabled) then
630 ecx_debug.pop(i_method_name);
631 end if;
632 raise;
633
634 WHEN OTHERS then
635 if DBMS_SQL.IS_OPEN(cursor_handle) then
636 dbms_sql.close_cursor(cursor_handle);
637 end if;
641 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
638 if(l_unexpectedEnabled) then
639 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
640 'ECX_ACTIONS.EXECUTE_STRING');
642 end if;
643 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXECUTE_STRING');
644 if (l_procedureEnabled) then
645 ecx_debug.pop(i_method_name);
646 end if;
647 raise ecx_utils.PROGRAM_EXIT;
648
649 END execute_string;
650
651
652 procedure get_nextval_seq (
653 i_seq_name IN Varchar2,
654 o_value OUT NOCOPY Varchar2) IS
655
656 i_method_name varchar2(2000) := 'ecx_actions.get_nextval_seq';
657 cString Varchar2(2000);
658
659 BEGIN
660 if (l_procedureEnabled) then
661 ecx_debug.push(i_method_name);
662 end if;
663
664 cString := 'select '||i_seq_name||'.NEXTVAL from dual';
665 execute_string ( cString, o_value);
666
667 if (l_procedureEnabled) then
668 ecx_debug.pop(i_method_name);
669 end if;
670
671
672 EXCEPTION
673 WHEN ecx_utils.PROGRAM_EXIT then
674 if (l_procedureEnabled) then
675 ecx_debug.pop(i_method_name);
676 end if;
677 raise;
678
679 WHEN OTHERS then
680 if(l_unexpectedEnabled) then
681 ecx_debug.log(l_unexpected, 'ECX', 'ECX_PROGRAM_ERROR',i_method_name, 'PROGRESS_LEVEL',
682 'ECX_ACTIONS.GET_NEXTVAL_SEQ');
683 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
684 end if;
685 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_NEXTVAL_SEQ');
686 if (l_procedureEnabled) then
687 ecx_debug.pop(i_method_name);
688 end if;
689 raise ecx_utils.PROGRAM_EXIT;
690
691 END get_nextval_seq;
692
693
694 /**
695 Returns the Function Value by building a select Clause for the
696 Function name.
697 **/
698 procedure get_function_value (
699 i_function_name IN Varchar2,
700 o_value OUT NOCOPY Varchar2) IS
701
702 i_method_name varchar2(2000) := 'ecx_actions.get_function_value';
703 cString Varchar2(2000);
704
705 BEGIN
706 if (l_procedureEnabled) then
707 ecx_debug.push(i_method_name);
708 end if;
709
710 if i_function_name = 'SYSDATE' then
711 select to_char(SYSDATE,'YYYYMMDD HH24MISS') into o_value from dual;
712
713 else
714 cString := i_function_name;
715 cString := 'select '||cString||' from dual';
716 execute_string (cString, o_value);
717 end if;
718
719 if (l_procedureEnabled) then
720 ecx_debug.pop(i_method_name);
721 end if;
722
723 EXCEPTION
724 WHEN ecx_utils.PROGRAM_EXIT then
725 if (l_procedureEnabled) then
726 ecx_debug.pop(i_method_name);
727 end if;
728 raise;
729
730 WHEN OTHERS then
731 if(l_unexpectedEnabled) then
732 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
733 'ECX_ACTIONS.GET_FUNCTION_VALUE');
734 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE',i_method_name, 'ERROR_MESSAGE', SQLERRM);
735 end if;
736 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_FUNCTION_VALUE');
737 if (l_procedureEnabled) then
738 ecx_debug.pop(i_method_name);
739 end if;
740 raise ecx_utils.PROGRAM_EXIT;
741
742 END get_function_value;
743
744
745 procedure dump_stack IS
746
747 i_method_name varchar2(2000) := 'ecx_actions.dump_stack';
748 m_count pls_integer := ecx_utils.g_stack.COUNT;
749
750 BEGIN
751 if (l_procedureEnabled) then
752 ecx_debug.push(i_method_name);
753 end if;
754
755 if(l_statementEnabled) then
756 ecx_debug.log(l_statement,'ECX','ECX_STACK_DUMP',i_method_name,null);
757 end if;
758
759
760 for i in 1..m_count loop
761 --for i in m_count.first..m_count.last loop
762 if(l_statementEnabled) then
763 ecx_debug.log(l_statement,ecx_utils.g_stack(i).variable_name||' ' ||
764 ecx_utils.g_stack(i).variable_value||' '||
765 ecx_utils.g_stack(i).data_type,i_method_name);
766 end if;
767 end loop;
768
769 if (l_procedureEnabled) then
770 ecx_debug.pop(i_method_name);
771 end if;
772
773 EXCEPTION
774 WHEN OTHERS then
775 if(l_unexpectedEnabled) then
776 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL', 'ECX_ACTIONS.DUMP_STACK');
777 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', i_method_name,'ERROR_MESSAGE',SQLERRM);
778 end if;
779 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.DUMP_STACK');
780 if (l_procedureEnabled) then
781 ecx_debug.pop(i_method_name);
782 end if;
786
783 raise ecx_utils.PROGRAM_EXIT;
784
785 END dump_stack;
787 PROCEDURE concat_variables
788 (
789 i_variable_level IN pls_integer,
790 i_variable_name IN Varchar2,
791 i_variable_direction IN Varchar2,
792 i_variable_pos IN pls_integer,
793 i_previous_variable_level IN pls_integer,
794 i_previous_variable_name IN Varchar2,
795 i_previous_variable_direction IN Varchar2,
796 i_previous_variable_pos IN pls_integer,
797 i_previous_default_value IN varchar2,
798 i_next_variable_level IN pls_integer,
799 i_next_variable_name IN Varchar2,
800 i_next_variable_direction IN varchar2,
801 i_next_variable_pos IN pls_integer,
802 i_next_default_value IN varchar2
803 )
804 IS
805
806 i_method_name varchar2(2000) := 'ecx_actions.concat_variables';
807
808 var_value Varchar2(4000);
809 var_on_stack Boolean := FALSE;
810 var_stack_pos pls_integer;
811 pre_var_value Varchar2(4000);
812 pre_var_on_stack Boolean := FALSE;
813 pre_var_stack_pos pls_integer;
814 nxt_var_value Varchar2(4000);
815 nxt_var_on_stack Boolean := FALSE;
816 nxt_var_stack_pos pls_integer;
817 concat_val varchar2(4000);
818 BEGIN
819 if (l_procedureEnabled) then
820 ecx_debug.push(i_method_name);
821 end if;
822
823 get_var_attr(
824 i_variable_level,
825 i_variable_name,
826 i_variable_direction,
827 i_variable_pos,
828 var_value,
829 var_on_stack,
830 var_stack_pos
831 );
832
833 pre_var_value := i_previous_default_value;
834 if pre_var_value is null
835 then
836 get_var_attr(
837 i_previous_variable_level, i_previous_variable_name,
838 i_previous_variable_direction,
839 i_previous_variable_pos, pre_var_value, pre_var_on_stack,
840 pre_var_stack_pos
841 );
842 end if;
843 if pre_var_value = 'NULL'
844 then
845 pre_var_value :=null;
846 end if;
847
848 nxt_var_value := i_next_default_value;
849
850 if nxt_var_value is null
851 then
852 get_var_attr(
853 i_next_variable_level, i_next_variable_name,
854 i_next_variable_direction,
855 i_next_variable_pos, nxt_var_value, nxt_var_on_stack,
856 nxt_var_stack_pos
857 );
858 end if;
859 if nxt_var_value = 'NULL'
860 then
861 nxt_var_value :=null;
862 end if;
863
864 concat_val := pre_var_value || nxt_var_value;
865
866 assign_value(
867 var_on_stack,
868 var_stack_pos,
869 i_variable_pos,
870 i_variable_direction,
871 concat_val
872 );
873
874 if (l_procedureEnabled) then
875 ecx_debug.pop(i_method_name);
876 end if;
877
878 EXCEPTION
879 WHEN VALUE_ERROR then
880 ecx_debug.setErrorInfo(1, 30, 'ECX_INVALID_VARCHAR2_LEN');
881 if(l_unexpectedEnabled) then
882 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
883 end if;
884 if (l_procedureEnabled) then
885 ecx_debug.pop(i_method_name);
886 end if;
887 raise ecx_utils.PROGRAM_EXIT;
888 WHEN ecx_utils.PROGRAM_EXIT then
889 if (l_procedureEnabled) then
890 ecx_debug.pop(i_method_name);
891 end if;
892 raise ecx_utils.PROGRAM_EXIT;
893 WHEN OTHERS THEN
894 if(l_unexpectedEnabled) then
895 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
896 'PROGRESS_LEVEL', 'ECX_ACTIONS.CONCAT_VAR');
897 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
898 end if;
899 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONCAT_VAR');
900 if (l_procedureEnabled) then
901 ecx_debug.pop(i_method_name);
902 end if;
903 raise ecx_utils.PROGRAM_EXIT;
904
905 END concat_variables;
906
907 PROCEDURE substr_variables
908 (
909 i_variable_level IN pls_integer,
910 i_variable_name IN Varchar2,
911 i_variable_direction IN Varchar2,
912 i_variable_pos IN pls_integer,
913 i_previous_variable_level IN pls_integer,
914 i_previous_variable_name IN Varchar2,
915 i_previous_variable_direction IN Varchar2,
916 i_previous_variable_pos IN pls_integer,
917 i_previous_variable_constant IN varchar2,
918 i_operand2_level IN pls_integer,
919 i_operand2_name IN varchar2,
920 i_operand2_direction IN varchar2,
921 i_operand2_pos IN pls_integer,
922 i_operand2_constant IN varchar2,
923 i_operand3_level IN pls_integer,
924 i_operand3_name IN varchar2,
925 i_operand3_direction IN varchar2,
929 IS
926 i_operand3_pos IN pls_integer,
927 i_operand3_constant IN varchar2
928 )
930
931 i_method_name varchar2(2000) := 'ecx_actions.substr_variables';
932 var_value Varchar2(4000);
933 var_on_stack Boolean := FALSE;
934 var_stack_pos pls_integer;
935 pre_var_value Varchar2(4000);
936 pre_var_on_stack Boolean := FALSE;
937 pre_var_stack_pos pls_integer;
938 substr_val varchar2(4000);
939 i_length varchar2(200);
940 i_operand2_stack boolean := false;
941 i_operand2_stack_pos pls_integer;
942 i_operand3_stack boolean := false;
943 i_operand3_stack_pos pls_integer;
944 i_start_pos varchar2(200);
945 BEGIN
946 if (l_procedureEnabled) then
947 ecx_debug.push(i_method_name);
948 end if;
949
950 get_var_attr (i_variable_level, i_variable_name,i_variable_direction,
951 i_variable_pos,
952 var_value, var_on_stack, var_stack_pos);
953
954 pre_var_value := i_previous_variable_constant;
955 if pre_var_value is null
956 then
957 get_var_attr (i_previous_variable_level, i_previous_variable_name,
958 i_previous_variable_direction,
959 i_previous_variable_pos, pre_var_value, pre_var_on_stack,
960 pre_var_stack_pos);
961 end if;
962
963 if pre_var_value = 'NULL'
964 then
965 pre_var_value :=null;
966 end if;
967
968 if(l_statementEnabled) then
969 ecx_debug.log(l_statement,'i_operand2_constant',i_operand2_constant,i_method_name);
970 end if;
971 i_start_pos := i_operand2_constant;
972 if i_start_pos is null
973 then
974 get_var_attr (i_operand2_level, i_operand2_name,
975 i_operand2_direction,
976 i_operand2_pos, i_start_pos, i_operand2_stack,
977 i_operand2_stack_pos);
978 end if;
979
980 if i_start_pos = 'NULL'
981 then
982 i_start_pos :=null;
983 end if;
984
985 if(l_statementEnabled) then
986 ecx_debug.log(l_statement,'i_operand3_constant',i_operand3_constant,i_method_name);
987 end if;
988 i_length := i_operand3_constant;
989 if i_length is null
990 then
991 get_var_attr (i_operand3_level, i_operand3_name,
992 i_operand3_direction,
993 i_operand3_pos, i_length, i_operand3_stack,
994 i_operand3_stack_pos);
995 end if;
996
997 if i_length = 'NULL'
998 then
999 i_length :=null;
1000 end if;
1001
1002 substr_val := substr(pre_var_value,i_start_pos,
1003 i_length);
1004 assign_value (var_on_stack, var_stack_pos,
1005 i_variable_pos,i_variable_direction, substr_val);
1006 if (l_procedureEnabled) then
1007 ecx_debug.pop(i_method_name);
1008 end if;
1009
1010
1011 EXCEPTION
1012 WHEN ecx_utils.PROGRAM_EXIT then
1013 if (l_procedureEnabled) then
1014 ecx_debug.pop(i_method_name);
1015 end if;
1016 raise;
1017
1018 WHEN OTHERS THEN
1019 if(l_unexpectedEnabled) then
1020 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
1021 'PROGRESS_LEVEL', 'ECX_ACTIONS.SUBSTR_VAR');
1022 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
1023 'ERROR_MESSAGE',SQLERRM);
1024 end if;
1025 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.SUBSTR_VAR');
1026 if (l_procedureEnabled) then
1027 ecx_debug.pop(i_method_name);
1028 end if;
1029 raise ecx_utils.PROGRAM_EXIT;
1030
1031 END substr_variables;
1032
1033
1034 /**
1035 Assigns the Value from a Previously Defined Variable
1036 i.e. i := j;
1037 **/
1038 procedure assign_pre_defined_variables (
1039 i_variable_level IN pls_integer,
1040 i_variable_name IN Varchar2,
1041 i_variable_direction IN Varchar2,
1042 i_variable_pos IN pls_integer,
1043 i_previous_variable_level IN pls_integer,
1044 i_previous_variable_name IN Varchar2,
1045 i_previous_variable_direction IN Varchar2,
1046 i_previous_variable_pos IN pls_integer,
1047 i_previous_variable_constant IN varchar2) IS
1048
1049 i_method_name varchar2(2000) := 'ecx_actions.assign_pre_defined_variables';
1050
1051 var_value Varchar2(4000);
1052 var_on_stack Boolean := FALSE;
1053 var_stack_pos pls_integer;
1054 pre_var_value Varchar2(4000);
1055 pre_var_on_stack Boolean := FALSE;
1056 pre_var_stack_pos pls_integer;
1057
1058 BEGIN
1059 if (l_procedureEnabled) then
1060 ecx_debug.push(i_method_name);
1061 end if;
1062
1063 get_var_attr (i_variable_level, i_variable_name,
1064 i_variable_direction,i_variable_pos,
1065 var_value, var_on_stack, var_stack_pos);
1066
1067 pre_var_value := i_previous_variable_constant;
1068
1072 i_previous_variable_direction,
1069 if pre_var_value is null
1070 then
1071 get_var_attr (i_previous_variable_level, i_previous_variable_name,
1073 i_previous_variable_pos, pre_var_value, pre_var_on_stack,
1074 pre_var_stack_pos);
1075 end if;
1076
1077 if pre_var_value = 'NULL'
1078 then
1079 pre_var_value :=null;
1080 end if;
1081
1082 assign_value (var_on_stack, var_stack_pos,
1083 i_variable_pos, i_variable_direction,pre_var_value);
1084
1085 if (l_procedureEnabled) then
1086 ecx_debug.pop(i_method_name);
1087 end if;
1088
1089
1090 EXCEPTION
1091 WHEN ecx_utils.PROGRAM_EXIT then
1092 if (l_procedureEnabled) then
1093 ecx_debug.pop(i_method_name);
1094 end if;
1095 raise;
1096
1097 WHEN OTHERS THEN
1098 if(l_unexpectedEnabled) then
1099 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,
1100 'PROGRESS_LEVEL',
1101 'ECX_ACTIONS.ASSIGN_PRE_DEFINED_VARIABLES');
1102 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE',i_method_name,
1103 'ERROR_MESSAGE', SQLERRM);
1104 end if;
1105 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.ASSIGN_PRE_DEFINED_VARIABLES');
1106 if (l_procedureEnabled) then
1107 ecx_debug.pop(i_method_name);
1108 end if;
1109 raise ecx_utils.PROGRAM_EXIT;
1110
1111 END assign_pre_defined_variables;
1112
1113
1114 /**
1115 Assign the Nextvalue from a sequence.
1116 e.g. x := document.NEXTVAL;
1117 **/
1118 procedure assign_nextval_from_sequence (
1119 i_variable_level IN pls_integer,
1120 i_variable_name IN Varchar2,
1121 i_variable_direction IN Varchar2,
1122 i_variable_pos IN pls_integer,
1123 i_sequence_name IN Varchar2) IS
1124
1125 i_method_name varchar2(2000) := 'ecx_actions.assign_nextval_from_sequence';
1126
1127 var_value Varchar2(4000);
1128 var_on_stack Boolean := FALSE;
1129 var_stack_pos pls_integer;
1130 seq_value pls_integer;
1131
1132 BEGIN
1133
1134 if (l_procedureEnabled) then
1135 ecx_debug.push(i_method_name);
1136 end if;
1137
1138
1139 get_var_attr (i_variable_level, i_variable_name,
1140 i_variable_direction,i_variable_pos,
1141 var_value, var_on_stack, var_stack_pos);
1142
1143 get_nextval_seq (i_sequence_name, seq_value);
1144
1145 assign_value (var_on_stack, var_stack_pos,
1146 i_variable_pos,i_variable_direction, seq_value);
1147
1148 if (l_procedureEnabled) then
1149 ecx_debug.pop(i_method_name);
1150 end if;
1151
1152 EXCEPTION
1153 WHEN ecx_utils.PROGRAM_EXIT then
1154 if (l_procedureEnabled) then
1155 ecx_debug.pop(i_method_name);
1156 end if;
1157 raise;
1158
1159 WHEN OTHERS THEN
1160 if(l_unexpectedEnabled) then
1161 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
1162 'ECX_ACTIONS.ASSIGN_NEXTVAL_FROM_SEQUENCE');
1163 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1164 end if;
1165 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.ASSIGN_NEXTVAL_FROM_SEQUENCE');
1166 if (l_procedureEnabled) then
1167 ecx_debug.pop(i_method_name);
1168 end if;
1169 raise ecx_utils.PROGRAM_EXIT;
1170
1171 END assign_nextval_from_sequence;
1172
1173
1174 /**
1175 Assigns the value returned by a function.
1176 **/
1177 procedure assign_function_value (
1178 i_variable_level IN pls_integer,
1179 i_variable_name IN Varchar2,
1180 i_variable_direction IN Varchar2,
1181 i_variable_pos IN pls_integer,
1182 i_function_name IN Varchar2) IS
1183
1184 i_method_name varchar2(2000) := 'ecx_actions.assign_function_value';
1185
1186 var_value Varchar2(4000);
1187 var_on_stack Boolean := FALSE;
1188 var_stack_pos pls_integer;
1189 function_value Varchar2(4000);
1190
1191 BEGIN
1192 if (l_procedureEnabled) then
1193 ecx_debug.push(i_method_name);
1194 end if;
1195
1196 get_var_attr (i_variable_level, i_variable_name,
1197 i_variable_direction,i_variable_pos,
1198 var_value, var_on_stack, var_stack_pos);
1199
1200 get_function_value (i_function_name, function_value);
1201
1202 assign_value (var_on_stack, var_stack_pos,
1203 i_variable_pos, i_variable_direction,function_value);
1204
1205 if (l_procedureEnabled) then
1206 ecx_debug.pop(i_method_name);
1207 end if;
1208
1209 EXCEPTION
1210 WHEN ecx_utils.PROGRAM_EXIT then
1211 if (l_procedureEnabled) then
1212 ecx_debug.pop(i_method_name);
1213 end if;
1214 raise;
1215
1216 WHEN OTHERS THEN
1217 if(l_unexpectedEnabled) then
1218 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name,
1222 end if;
1219 'PROGRESS_LEVEL', 'ECX_ACTIONS.ASSIGN_FUNCTION_VALUE');
1220 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
1221 'ERROR_MESSAGE',SQLERRM);
1223 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.ASSIGN_FUNCTION_VALUE');
1224 if (l_procedureEnabled) then
1225 ecx_debug.pop(i_method_name);
1226 end if;
1227 raise ecx_utils.PROGRAM_EXIT;
1228
1229 END assign_function_value;
1230
1231
1232 procedure append_clause (
1233 i_level IN pls_integer,
1234 i_where_clause IN Varchar2) IS
1235
1236 i_method_name varchar2(2000) := 'ecx_actions.append_clause';
1237
1238
1239 BEGIN
1240 if (l_procedureEnabled) then
1241 ecx_debug.push(i_method_name);
1242 end if;
1243 if(l_statementEnabled) then
1244 ecx_debug.log(l_statement,'i_level',i_level,i_method_name);
1245 ecx_debug.log(l_statement,'i_where_clause',i_where_clause,i_method_name);
1246 end if;
1247
1248 ecx_utils.g_source_levels(i_level).sql_stmt :=
1249 ecx_utils.g_source_levels(i_level).sql_stmt ||' '|| i_where_clause;
1250
1251 if(l_statementEnabled) then
1252 ecx_debug.log(l_statement,'i_where_clause', ecx_utils.g_source_levels(i_level).sql_stmt,
1253 i_method_name);
1254 end if;
1255
1256 if (l_procedureEnabled) then
1257 ecx_debug.pop(i_method_name);
1258 end if;
1259
1260 EXCEPTION
1261 WHEN ecx_utils.PROGRAM_EXIT then
1262 if (l_procedureEnabled) then
1263 ecx_debug.pop(i_method_name);
1264 end if;
1265 raise;
1266
1267 WHEN OTHERS THEN
1268 if(l_unexpectedEnabled) then
1269 ecx_debug.log(l_unexpected, 'ECX', 'ECX_PROGRAM_ERROR', i_method_name,
1270 'PROGRESS_LEVEL',
1271 'ECX_ACTIONS.APPEND_CLAUSE');
1272 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', i_method_name,
1273 'ERROR_MESSAGE',SQLERRM);
1274 end if;
1275 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.APPEND_CLAUSE');
1276 if (l_procedureEnabled) then
1277 ecx_debug.pop(i_method_name);
1278 end if;
1279 raise ecx_utils.PROGRAM_EXIT;
1280
1281 END append_clause;
1282
1283 procedure append_clause_for_view (
1284 i_stage IN pls_integer,
1285 i_level IN pls_integer
1286 ) IS
1287
1288 i_method_name varchar2(2000) := 'ecx_actions.get_var_attr';
1289 pre_var_value Varchar2(2000);
1290 pre_var_on_stack Boolean;
1291 pre_var_stack_pos pls_integer;
1292 i_date date;
1293 i_number Number;
1294 i_variable_name varchar2(100);
1295 l_data_type pls_integer;
1296 i_where_clause varchar2(2000);
1297
1298 BEGIN
1299 if (l_procedureEnabled) then
1300 ecx_debug.push(i_method_name);
1301 end if;
1302
1303 if (ecx_utils.g_stage_data.count <> 0)
1304 then
1305 FOR i in ecx_utils.g_stage_data.first..ecx_utils.g_stage_data.last
1306 loop
1307 if (ecx_utils.g_stage_data(i).stage = i_stage and ecx_utils.g_stage_data(i).level = i_level)
1308 then
1309 if ecx_utils.g_stage_data(i).action_type = APPEND_WHERECLAUSE
1310 then
1311 i_where_clause := ecx_utils.g_stage_data(i).clause;
1312 if(l_statementEnabled) then
1313 ecx_debug.log(l_statement,'Where Clause ', i_where_clause,i_method_name);
1314 end if;
1315 if i_where_clause is not null
1316 then
1317 append_clause(i_level,i_where_clause);
1318 end if; -- if_condition
1319 end if; -- Append Clause
1320 end if; -- Level /Stage Check
1321 end loop;
1322 end if;
1323 if (l_procedureEnabled) then
1324 ecx_debug.pop(i_method_name);
1325 end if;
1326
1327
1328 EXCEPTION
1329 WHEN ecx_utils.PROGRAM_EXIT then
1330 if (l_procedureEnabled) then
1331 ecx_debug.pop(i_method_name);
1332 end if;
1333 raise;
1334
1335 WHEN OTHERS THEN
1336 if(l_statementEnabled) then
1337 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
1338 'PROGRESS_LEVEL','ECX_ACTIONS.APPEND_CLAUSE_FOR_VIEW');
1339 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1340 end if;
1341 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.APPEND_CLAUSE_FOR_VIEW');
1342 if (l_procedureEnabled) then
1343 ecx_debug.pop(i_method_name);
1344 end if;
1345 raise ecx_utils.PROGRAM_EXIT;
1346
1347 END append_clause_for_view;
1348
1349 procedure bind_variables_for_view (
1350 i_stage IN pls_integer,
1351 i_level IN pls_integer
1352 ) IS
1353
1354 i_method_name varchar2(2000) := 'ecx_actions.bind_variables_for_view';
1355 pre_var_value Varchar2(4000);
1356 pre_var_on_stack Boolean;
1357 pre_var_stack_pos pls_integer;
1358 i_date date;
1359 i_number Number;
1360 i_variable_name ecx_tran_stage_data.variable_value%type;
1364 if (l_procedureEnabled) then
1361 l_data_type pls_integer;
1362
1363 BEGIN
1365 ecx_debug.push(i_method_name);
1366 end if;
1367
1368 if (ecx_utils.g_stage_data.count <> 0)
1369 then
1370 FOR i in ecx_utils.g_stage_data.first..ecx_utils.g_stage_data.last
1371 loop
1372 if (ecx_utils.g_stage_data(i).stage = i_stage and ecx_utils.g_stage_data(i).level = i_level)
1373 then
1374 if ecx_utils.g_stage_data(i).action_type = APPEND_WHERECLAUSE
1375 then
1376 i_variable_name := ecx_utils.g_stage_data(i).variable_value;
1377 if(l_statementEnabled) then
1378 ecx_debug.log(l_statement,'Bind Variable Name ', i_variable_name,i_method_name);
1379 end if;
1380
1381 if i_variable_name is not null
1382 then
1383 /**
1384 get from Default. If null , get it from the variable
1385 **/
1386 pre_var_value := ecx_utils.g_stage_data(i).operand1_constant;
1387 if pre_var_value is null
1388 then
1389 get_var_attr (ecx_utils.g_stage_data(i).operand1_level,
1390 ecx_utils.g_stage_data(i).operand1_name,
1391 ecx_utils.g_stage_data(i).operand1_direction,
1392 ecx_utils.g_stage_data(i).operand1_pos,
1393 pre_var_value, pre_var_on_stack,
1394 pre_var_stack_pos);
1395 end if;
1396 if pre_var_value = 'NULL'
1397 then
1398 pre_var_value :=null;
1399 end if;
1400 if(l_statementEnabled) then
1401 ecx_debug.log(l_statement,'Prev Variable Val ', pre_var_value,i_method_name);
1402 ecx_debug.log(l_statement,'Prev Variable Pos ', pre_var_stack_pos,i_method_name);
1403 end if;
1404
1405 --if not ( ecx_conditions.check_type_condition('6',pre_var_value,1,null,1)) then
1406 if pre_var_on_stack then
1407 l_data_type := ecx_utils.g_stack(pre_var_stack_pos).data_type;
1408 if(l_statementEnabled) then
1409 ecx_debug.log(l_statement,'Data Type' , to_char(nvl(l_data_type,-1)),i_method_name);
1410 end if;
1411 else
1412 l_data_type := ecx_utils.g_source(
1413 ecx_utils.g_stage_data(i).operand1_pos).data_type;
1414 if(l_statementEnabled) then
1415 ecx_debug.log(l_statement, 'Data Type 2' , to_char(nvl(l_data_type,-1)),i_method_name);
1416 end if;
1417 end if;
1418
1419
1420 if l_data_type = 2 then
1421 i_number := to_number(pre_var_value);
1422 dbms_sql.bind_variable (
1423 ecx_utils.g_source_levels(i_level).Cursor_Handle,
1424 i_variable_name, i_number);
1425 if(l_statementEnabled) then
1426 ecx_debug.log(l_statement, 'Binding Value ',i_number,i_method_name);
1427 end if;
1428
1429 elsif l_data_type = 12 then
1430 i_date := to_date(pre_var_value,'YYYYMMDD HH24MISS');
1431 dbms_sql.bind_variable (
1432 ecx_utils.g_source_levels(i_level).Cursor_Handle,
1433 i_variable_name, i_date);
1434 if(l_statementEnabled) then
1435 ecx_debug.log(l_statement,'Binding Value ',i_date,i_method_name);
1436 end if;
1437 else
1438 dbms_sql.bind_variable (
1439 ecx_utils.g_source_levels(i_level).Cursor_Handle,
1440 i_variable_name, pre_var_value);
1441 if(l_statementEnabled) then
1442 ecx_debug.log(l_statement,'Binding Value ',pre_var_value,i_method_name);
1443 end if;
1444 end if;
1445 --end if; -- if condition for variable_name
1446 end if; -- if_condition
1447 end if; -- Append Clause
1448 end if; -- Level /Stage Check
1449 end loop;
1450 end if;
1451 if (l_procedureEnabled) then
1452 ecx_debug.pop(i_method_name);
1453 end if;
1454
1455 EXCEPTION
1456 WHEN invalid_number then
1457 if (l_procedureEnabled) then
1458 ecx_debug.pop(i_method_name);
1459 end if;
1460 ecx_debug.setErrorInfo(1,30,'ECX_INVALID_NUMBER','pre_var_value',
1461 pre_var_value,'variable_name',i_variable_name);
1462 raise ecx_utils.program_exit;
1463 WHEN ecx_utils.PROGRAM_EXIT then
1464 if (l_procedureEnabled) then
1465 ecx_debug.pop(i_method_name);
1466 end if;
1467 raise;
1468
1469 WHEN OTHERS THEN
1470 if(l_unexpectedEnabled) then
1471 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
1472 'PROGRESS_LEVEL','ECX_ACTIONS.BIND_VARIABLES_FOR_VIEW');
1473 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
1474 'ERROR_MESSAGE',SQLERRM);
1475 end if;
1476 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.BIND_VARIABLES_FOR_VIEW');
1477 if (l_procedureEnabled) then
1478 ecx_debug.pop(i_method_name);
1479 end if;
1483
1480 raise ecx_utils.PROGRAM_EXIT;
1481
1482 END bind_variables_for_view;
1484
1485 procedure build_procedure_call (
1486 p_transtage_id IN pls_integer,
1487 p_procedure_name IN Varchar2,
1488 x_proc_cursor OUT NOCOPY pls_integer) IS
1489
1490 i_method_name varchar2(2000) := 'ecx_actions.build_procedure_call';
1491
1492 error_position pls_integer;
1493 l_proc_call Varchar2(32000);
1494 l_first_param Boolean := True;
1495 parse_error EXCEPTION;
1496
1497 BEGIN
1498 if (l_procedureEnabled) then
1499 ecx_debug.push(i_method_name);
1500 end if;
1501
1502 l_proc_call := 'BEGIN ' || p_procedure_name || '(';
1503 if (ecx_utils.g_procedure_mappings.count <> 0)
1504 then
1505 for i in ecx_utils.g_procedure_mappings.first..ecx_utils.g_procedure_mappings.last
1506 loop
1507 if (ecx_utils.g_procedure_mappings(i).transtage_id = p_transtage_id) and
1508 (ecx_utils.g_procedure_mappings(i).procedure_name = p_procedure_name) then
1509
1510 if not (l_first_param) then
1511 l_proc_call := l_proc_call || ', ';
1512 else
1513 l_first_param := false;
1514 end if;
1515
1516 l_proc_call := l_proc_call ||
1517 ecx_utils.g_procedure_mappings(i).parameter_name ||
1518 ' => :' || ecx_utils.g_procedure_mappings(i).parameter_name;
1519 end if;
1520 end loop;
1521 end if;
1522
1523 l_proc_call := l_proc_call || '); END;';
1524
1525 if(l_statementEnabled) then
1526 ecx_debug.log(l_statement,'proc_call', l_proc_call, i_method_name);
1527 end if;
1528 x_proc_cursor := dbms_sql.open_cursor;
1529
1530 BEGIN
1531 dbms_sql.parse (x_proc_cursor, l_proc_call, dbms_sql.native);
1532
1533 EXCEPTION
1534 when others then
1535 raise parse_error;
1536 END;
1537
1538 if (l_procedureEnabled) then
1539 ecx_debug.pop(i_method_name);
1540 end if;
1541
1542 EXCEPTION
1543 WHEN PARSE_ERROR then
1544 error_position := dbms_sql.last_error_position;
1545 if(l_unexpectedEnabled) then
1546 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
1547 'PROGRESS_LEVEL','ECX_ACTIONS.BUILD_PROCEDURE_CALL');
1548 end if;
1549 ecx_error_handling_pvt.print_parse_error (error_position, l_proc_call);
1550 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.BUILD_PROCEDURE_CALL');
1551 if (l_procedureEnabled) then
1552 ecx_debug.pop(i_method_name);
1553 end if;
1554 raise ecx_utils.program_exit;
1555
1556 WHEN ecx_utils.PROGRAM_EXIT then
1557 if (l_procedureEnabled) then
1558 ecx_debug.pop(i_method_name);
1559 end if;
1560 raise;
1561
1562 WHEN OTHERS THEN
1563 if(l_unexpectedEnabled) then
1564 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
1565 'PROGRESS_LEVEL','ECX_ACTIONS.BUILD_PROCEDURE_CALL');
1566 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1567 end if;
1568 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.BUILD_PROCEDURE_CALL');
1569 if (l_procedureEnabled) then
1570 ecx_debug.pop(i_method_name);
1571 end if;
1572 raise ecx_utils.PROGRAM_EXIT;
1573
1574 END build_procedure_call;
1575
1576 procedure bind_proc_variables (
1577 p_transtage_id IN pls_integer,
1578 p_procedure_name IN Varchar2,
1579 p_proc_cursor IN pls_integer) IS
1580
1581 i_method_name varchar2(2000) := 'ecx_actions.bind_proc_variables';
1582 l_stack_found Boolean;
1583 l_var_value Varchar2(32767);
1584 l_stack_pos pls_integer;
1585 l_numrows pls_integer;
1586 l_clob_value Clob;
1587 l_temp_loc Clob;
1588 i_clob_len pls_integer;
1589 l_len number;
1590
1591
1592 BEGIN
1593 if (l_procedureEnabled) then
1594 ecx_debug.push(i_method_name);
1595 end if;
1596 if (ecx_utils.g_procedure_mappings.count <> 0)
1597 then
1598 for i in ecx_utils.g_procedure_mappings.first..ecx_utils.g_procedure_mappings.last
1599 loop
1600 if (ecx_utils.g_procedure_mappings(i).transtage_id = p_transtage_id) and
1601 (ecx_utils.g_procedure_mappings(i).procedure_name = p_procedure_name) then
1602 /** Look for Constant Value first. If null get it from Variable **/
1603 l_var_value := null;
1604 l_clob_value := null;
1605 l_temp_loc := null;
1606 l_var_value := ecx_utils.g_procedure_mappings(i).variable_constant;
1607
1608 if l_var_value = 'NULL'
1609 then
1610 l_var_value :=null;
1611 end if;
1612
1613 if l_var_value is null
1614 --do not assign value to OUT parameters
1615 and ecx_utils.g_procedure_mappings(i).action_type <> 1070
1616 then
1617 if ( ecx_utils.g_procedure_mappings(i).variable_direction = 'G' )
1618 then
1619 l_stack_found := find_stack_variable(
1620 ecx_utils.g_procedure_mappings(i).variable_name,
1621 l_stack_pos);
1625 elsif ecx_utils.g_procedure_mappings(i).variable_direction = 'S'
1622 l_var_value := ecx_utils.g_stack(l_stack_pos).variable_value;
1623
1624 /* Based on the direction look in either source or target */
1626 then
1627 if(l_statementEnabled) then
1628 ecx_debug.log(l_statement,'Bind variable is in source',i_method_name);
1629 end if;
1630 if (l_clob_value is null) then
1631 dbms_lob.createtemporary(l_clob_value,true,dbms_lob.session);
1632 end if;
1633 l_clob_value := ecx_utils.g_source(ecx_utils.g_procedure_mappings(i).variable_pos).clob_value;
1634 l_var_value := ecx_utils.g_source(ecx_utils.g_procedure_mappings(i).variable_pos).value;
1635
1636 if ecx_utils.g_procedure_mappings(i).data_type <> 112 then
1637 l_len := dbms_lob.getlength(l_clob_value) ;
1638 if (l_clob_value is not null and
1639 ( l_len > ecx_utils.G_VARCHAR_LEN)) then
1640 get_varchar(l_clob_value ,
1641 l_var_value,
1642 l_var_value);
1643 l_clob_value := null;
1644 end if;
1645 l_len := length(l_var_value) ;
1646 if (l_var_value is not null and
1647 ( l_len > ecx_utils.G_VARCHAR_LEN)) then
1648 get_varchar(l_clob_value ,
1649 l_var_value,
1650 l_var_value);
1651 l_clob_value := null;
1652 end if;
1653
1654
1655 End If;
1656 else
1657 if(l_statementEnabled) then
1658 ecx_debug.log(l_statement,'Bind variable is in target',i_method_name);
1659 end if;
1660 if (l_clob_value is null) then
1661 dbms_lob.createtemporary(l_clob_value,true,dbms_lob.session);
1662 end if;
1663
1664 l_clob_value := ecx_utils.g_target(ecx_utils.g_procedure_mappings(i).variable_pos).clob_value;
1665 l_var_value := ecx_utils.g_target(ecx_utils.g_procedure_mappings(i).variable_pos).value;
1666
1667 if ecx_utils.g_procedure_mappings(i).data_type <> 112 then
1668 l_len := dbms_lob.getlength(l_clob_value) ;
1669 if (l_clob_value is not null and
1670 (l_len > ecx_utils.G_VARCHAR_LEN)) then
1671 get_varchar(l_clob_value ,
1672 l_var_value,
1673 l_var_value);
1674 l_clob_value := null;
1675 end if;
1676 l_len := length(l_var_value) ;
1677 if (l_var_value is not null and
1678 ( l_len > ecx_utils.G_VARCHAR_LEN)) then
1679 get_varchar(l_clob_value ,
1680 l_var_value,
1681 l_var_value);
1682 l_clob_value := null;
1683 end if;
1684
1685
1686 End If;
1687 end if;
1688 end if;
1689 if(l_statementEnabled) then
1690 ecx_debug.log(l_statement,'l_var_value', l_var_value,i_method_name);
1691 if (l_clob_value is not null) then
1692 ecx_debug.log(l_statement,'l_clob_value', l_clob_value,i_method_name);
1693 end if;
1694 ecx_debug.log(l_statement,'variable_name', ecx_utils.g_procedure_mappings(i).variable_name,
1695 i_method_name);
1696 end if;
1697
1698 if (ecx_utils.g_procedure_mappings(i).data_type = 1) then
1699 dbms_sql.bind_variable(p_proc_cursor,
1700 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
1701 l_var_value,ecx_utils.G_VARCHAR_LEN);
1702
1703 elsif (ecx_utils.g_procedure_mappings(i).data_type = 2) then
1704 dbms_sql.bind_variable(p_proc_cursor,
1705 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
1706 to_number(l_var_value));
1707 if(l_statementEnabled) then
1708 ecx_debug.log(l_statement,'after binding',i_method_name);
1709 end if;
1710 elsif (ecx_utils.g_procedure_mappings(i).data_type = 12) then
1711 dbms_sql.bind_variable(p_proc_cursor,
1712 ':' || ecx_utils.g_procedure_mappings(i).parameter_name,
1713 to_date(l_var_value, 'YYYYMMDD HH24MISS'));
1714
1715 elsif (ecx_utils.g_procedure_mappings(i).data_type = 96) then
1716 dbms_sql.bind_variable(p_proc_cursor,
1717 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name, l_var_value, 600);
1718
1719 /** Change required for Clob Support -- 2263729 ***/
1720 elsif (ecx_utils.g_procedure_mappings(i).data_type = 112) then
1721 if l_clob_value is not null Then
1722 if(l_statementEnabled) then
1726 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name, l_clob_value);
1723 ecx_debug.log(l_statement,'Binding clob is not null',i_method_name);
1724 end if;
1725 dbms_sql.bind_variable(p_proc_cursor,
1727
1728 elsif l_var_value is not null Then
1729 if(l_statementEnabled) then
1730 ecx_debug.log(l_statement,'Binding varchar is not null',i_method_name);
1731 end if;
1732 /** bind the varchar2 variable to clob. ***/
1733 get_clob(l_clob_value,l_var_value,l_temp_loc);
1734 dbms_sql.bind_variable(p_proc_cursor,
1735 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,l_temp_loc);
1736
1737 else
1738 if(l_statementEnabled) then
1739 ecx_debug.log(l_statement,'Binding clob is null',i_method_name);
1740 end if;
1741
1742 /** just bind the null clob so that bind will not fail***/
1743 dbms_sql.bind_variable(p_proc_cursor,
1744 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,l_clob_value);
1745 end if;
1746 else
1747 if(l_statementEnabled) then
1748 ecx_debug.log(l_statement,'ECX', 'ECX_PROCEDURE_EXECUTION',i_method_name,
1749 'PROCEDURE_NAME',
1750 p_procedure_name);
1751 ecx_debug.log(l_statement,'ECX', 'ECX_UNSUPPORTED_DATATYPE',i_method_name,
1752 'Unsupported Data Type');
1753 end if;
1754 ecx_debug.setErrorInfo(2,30,'ECX_UNSUPPORTED_DATATYPE');
1755 raise ecx_utils.program_exit;
1756 end if;
1757 end if;
1758 if dbms_lob.istemporary(l_temp_loc) = 1 Then
1759 dbms_lob.freetemporary(l_temp_loc);
1760 end if;
1761 if dbms_lob.istemporary(l_clob_value) = 1 Then
1762 dbms_lob.freetemporary(l_clob_value);
1763 end if;
1764 end loop;
1765 end if;
1766 if(l_statementEnabled) then
1767 ecx_debug.log(l_statement,'before execute procedure',i_method_name);
1768 end if;
1769 l_numrows := dbms_sql.execute (p_proc_cursor);
1770 if(l_statementEnabled) then
1771 ecx_debug.log(l_statement,'after execute procedure',i_method_name);
1772 end if;
1773 if dbms_lob.istemporary(l_temp_loc) = 1 Then
1774 dbms_lob.freetemporary(l_temp_loc);
1775 end if;
1776 if dbms_lob.istemporary(l_clob_value) = 1 Then
1777 dbms_lob.freetemporary(l_clob_value);
1778 end if;
1779 if (l_procedureEnabled) then
1780 ecx_debug.pop(i_method_name);
1781 end if;
1782
1783 EXCEPTION
1784 WHEN ecx_utils.PROGRAM_EXIT then
1785 if dbms_lob.istemporary(l_temp_loc) = 1 Then
1786 dbms_lob.freetemporary(l_temp_loc);
1787 end if;
1788 if dbms_lob.istemporary(l_clob_value) = 1 Then
1789 dbms_lob.freetemporary(l_clob_value);
1790 end if;
1791 if (l_procedureEnabled) then
1792 ecx_debug.pop(i_method_name);
1793 end if;
1794 raise;
1795
1796 WHEN OTHERS THEN
1797 if(l_unexpectedEnabled) then
1798 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
1799 'PROGRESS_LEVEL','ECX_ACTIONS.BIND_PROC_VARIABLES');
1800 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1801 end if;
1802 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.BIND_PROC_VARIABLES');
1803 if dbms_lob.istemporary(l_temp_loc) = 1 Then
1804 dbms_lob.freetemporary(l_temp_loc);
1805 end if;
1806 if dbms_lob.istemporary(l_clob_value) = 1 Then
1807 dbms_lob.freetemporary(l_clob_value);
1808 end if;
1809 if (l_procedureEnabled) then
1810 ecx_debug.pop(i_method_name);
1811 end if;
1812 raise ecx_utils.PROGRAM_EXIT;
1813
1814 END bind_proc_variables;
1815
1816
1817 procedure assign_out_variables (
1818 p_transtage_id IN pls_integer,
1819 p_procedure_name IN Varchar2,
1820 p_proc_cursor IN pls_integer) IS
1821
1822 i_method_name varchar2(2000) := 'ecx_actions.assign_out_variables';
1823
1824 l_stack_var Boolean;
1825 l_var_pos pls_integer;
1826 l_stack_pos pls_integer;
1827 l_varchar_value Varchar2(32767);
1828 l_clob_value clob;
1829 l_num number;
1830 l_date date;
1831 xml_frag_count pls_integer;
1832 frag_found Boolean;
1833 BEGIN
1834 if (l_procedureEnabled) then
1835 ecx_debug.push(i_method_name);
1836 end if;
1837
1838 if (ecx_utils.g_procedure_mappings.count <> 0)
1839 then
1840 for i in ecx_utils.g_procedure_mappings.first..ecx_utils.g_procedure_mappings.last
1841 loop
1842 if (ecx_utils.g_procedure_mappings(i).transtage_id = p_transtage_id) and
1843 (ecx_utils.g_procedure_mappings(i).procedure_name = p_procedure_name) and
1844 (ecx_utils.g_procedure_mappings(i).action_type = 1070 or
1845 ecx_utils.g_procedure_mappings(i).action_type = 1080) then
1846 xml_frag_count := ecx_utils.g_xml_frag.count;
1850 ecx_utils.g_procedure_mappings(i).variable_name,
1847 if ( ecx_utils.g_procedure_mappings(i).variable_direction = 'G' )
1848 then
1849 l_stack_var := find_stack_variable(
1851 l_stack_pos);
1852 else
1853 l_stack_var := false;
1854 l_var_pos := ecx_utils.g_procedure_mappings(i).variable_pos;
1855 end if;
1856 if(l_statementEnabled) then
1857 ecx_debug.log(l_statement,'fragment count',xml_frag_count,i_method_name);
1858 end if;
1859
1860
1861 if (ecx_utils.g_procedure_mappings(i).data_type = 1) then
1862 dbms_sql.variable_value (p_proc_cursor,
1863 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
1864 l_varchar_value);
1865 frag_found := FALSE;
1866 -- XML Fragment change
1867 if (instr(p_procedure_name,ECX_UTILS.G_XML_FRAG_PROC) > 0 and l_var_pos is
1868 not null and ecx_utils.g_procedure_mappings(i).variable_direction = 'T') then
1869 if ecx_utils.g_xml_frag.count > 0 then
1870 for frag_count in ecx_utils.g_xml_frag.FIRST .. ecx_utils.g_xml_frag.LAST
1871 loop
1872 if ecx_utils.g_xml_frag(frag_count).variable_pos = l_var_pos
1873 then
1874 ecx_utils.g_xml_frag(frag_count).value := l_varchar_value;
1875 frag_found := TRUE;
1876 if frag_found then
1877 exit;
1878 end if;
1879 end if;
1880 end loop;
1881 end if;
1882
1883 if (not frag_found) then
1884 ecx_utils.g_xml_frag(xml_frag_count+1).variable_pos :=
1885 l_var_pos;
1886 ecx_utils.g_xml_frag(xml_frag_count+1).value := l_varchar_value;
1887 if(l_statementEnabled) then
1888 ecx_debug.log(l_statement,'value',ecx_utils.g_xml_frag(xml_frag_count+1).value,i_method_name);
1889 end if;
1890 end if;
1891
1892 if (ecx_utils.g_target(l_var_pos).attribute_type =1) then
1893 ecx_utils.g_target(l_var_pos).value := null;
1894 ecx_utils.g_target(l_var_pos).clob_value := null;
1895 end if;
1896
1897 else
1898 assign_value (l_stack_var, l_stack_pos, l_var_pos,
1899 ecx_utils.g_procedure_mappings(i).variable_direction,l_varchar_value);
1900 end if;
1901
1902 elsif (ecx_utils.g_procedure_mappings(i).data_type = 2) then
1903 dbms_sql.variable_value (p_proc_cursor,
1904 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
1905 l_num);
1906 frag_found := FALSE;
1907 if (instr(p_procedure_name,ECX_UTILS.G_XML_FRAG_PROC) > 0 and l_var_pos is
1908 not null and ecx_utils.g_procedure_mappings(i).variable_direction = 'T' ) then
1909 -- XML Fragment change
1910
1911 if ecx_utils.g_xml_frag.count > 0 then
1912 for frag_count in ecx_utils.g_xml_frag.FIRST .. ecx_utils.g_xml_frag.LAST
1913 loop
1914 if ecx_utils.g_xml_frag(frag_count).variable_pos = l_var_pos then
1915 ecx_utils.g_xml_frag(frag_count).value := to_char(l_num);
1916 frag_found := TRUE;
1917 if frag_found then
1918 exit;
1919 end if;
1920 end if;
1921 end loop;
1922 end if;
1923
1924 if (not frag_found) then
1925 ecx_utils.g_xml_frag(xml_frag_count+1).variable_pos := l_var_pos;
1926 ecx_utils.g_xml_frag(xml_frag_count+1).value := to_char(l_num);
1927 end if;
1928
1929 if (ecx_utils.g_target(l_var_pos).attribute_type =1) then
1930 ecx_utils.g_target(l_var_pos).value := null;
1931 ecx_utils.g_target(l_var_pos).clob_value := null;
1932 end if;
1933 else
1934 assign_value (l_stack_var, l_stack_pos, l_var_pos,
1935 ecx_utils.g_procedure_mappings(i).variable_direction,
1936 to_char(l_num));
1937 end if;
1938
1939 elsif (ecx_utils.g_procedure_mappings(i).data_type = 12) then
1940 dbms_sql.variable_value (p_proc_cursor,
1941 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
1942 l_date);
1943 frag_found := FALSE;
1944 if (instr(p_procedure_name,ECX_UTILS.G_XML_FRAG_PROC) > 0 and l_var_pos is
1945 not null and ecx_utils.g_procedure_mappings(i).variable_direction = 'T' ) then
1946
1947 if ecx_utils.g_xml_frag.count > 0 then
1948 for frag_count in ecx_utils.g_xml_frag.FIRST .. ecx_utils.g_xml_frag.LAST
1949 loop
1950 if ecx_utils.g_xml_frag(frag_count).variable_pos = l_var_pos then
1951 ecx_utils.g_xml_frag(frag_count).value := to_char(l_date,'YYYYMMDD HH24MISS');
1952 frag_found := TRUE;
1956 end if;
1953 if frag_found then
1954 exit;
1955 end if;
1957 end loop;
1958 end if;
1959
1960 if (not frag_found) then
1961 ecx_utils.g_xml_frag(xml_frag_count+1).variable_pos := l_var_pos;
1962 ecx_utils.g_xml_frag(xml_frag_count+1).value := to_char(l_date,'YYYYMMDD HH24MISS');
1963 end if;
1964
1965 if (ecx_utils.g_target(l_var_pos).attribute_type =1) then
1966 ecx_utils.g_target(l_var_pos).value := null;
1967 ecx_utils.g_target(l_var_pos).clob_value := null;
1968 end if;
1969 else
1970 assign_value (l_stack_var, l_stack_pos, l_var_pos,
1971 ecx_utils.g_procedure_mappings(i).variable_direction,
1972 to_char(l_date,'YYYYMMDD HH24MISS'));
1973 end if;
1974
1975
1976 elsif (ecx_utils.g_procedure_mappings(i).data_type = 96) then
1977 dbms_sql.variable_value (p_proc_cursor,
1978 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
1979 l_varchar_value);
1980 frag_found := FALSE;
1981 if (instr(p_procedure_name,ECX_UTILS.G_XML_FRAG_PROC) > 0 and l_var_pos is
1982 not null and ecx_utils.g_procedure_mappings(i).variable_direction = 'T' ) then
1983 if ecx_utils.g_xml_frag.count > 0 then
1984 for frag_count in ecx_utils.g_xml_frag.FIRST .. ecx_utils.g_xml_frag.LAST
1985 loop
1986 if ecx_utils.g_xml_frag(frag_count).variable_pos = l_var_pos then
1987 ecx_utils.g_xml_frag(frag_count).value := l_varchar_value;
1988 frag_found := TRUE;
1989 if frag_found then
1990 exit;
1991 end if;
1992 end if;
1993 end loop;
1994 end if;
1995
1996 if (not frag_found) then
1997 ecx_utils.g_xml_frag(xml_frag_count+1).variable_pos :=
1998 l_var_pos;
1999 ecx_utils.g_xml_frag(xml_frag_count+1).value :=
2000 l_varchar_value;
2001 end if;
2002
2003 if (ecx_utils.g_target(l_var_pos).attribute_type =1) then
2004 ecx_utils.g_target(l_var_pos).value := null;
2005 ecx_utils.g_target(l_var_pos).clob_value := null;
2006 end if;
2007 else
2008 assign_value (l_stack_var, l_stack_pos, l_var_pos,
2009 ecx_utils.g_procedure_mappings(i).variable_direction,
2010 l_varchar_value );
2011 end if;
2012 /** Change required for Clob Support -- 2263729 ***/
2013 elsif (ecx_utils.g_procedure_mappings(i).data_type = 112) then
2014 dbms_sql.variable_value (p_proc_cursor,
2015 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name,
2016 l_clob_value);
2017
2018 assign_value (l_stack_var, l_stack_pos, l_var_pos,
2019 ecx_utils.g_procedure_mappings(i).variable_direction,
2020 l_clob_value );
2021 end if;
2022 end if;
2023 end loop;
2024 end if;
2025 if (l_procedureEnabled) then
2026 ecx_debug.pop(i_method_name);
2027 end if;
2028
2029 EXCEPTION
2030 WHEN ecx_utils.PROGRAM_EXIT then
2031 if (l_procedureEnabled) then
2032 ecx_debug.pop(i_method_name);
2033 end if;
2034 raise;
2035
2036 WHEN OTHERS THEN
2037 if(l_unexpectedEnabled) then
2038 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
2039 'PROGRESS_LEVEL','ECX_ACTIONS.ASSIGN_OUT_VARIABLES');
2040 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2041 end if;
2042 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.ASSIGN_OUT_VARIABLES');
2043 if (l_procedureEnabled) then
2044 ecx_debug.pop(i_method_name);
2045 end if;
2046 raise ecx_utils.PROGRAM_EXIT;
2047
2048 END assign_out_variables;
2049
2050
2051 /**
2052 Executes a given stored procedure or a function with parameters of following datatype :
2053 1. Date
2054 2. Number
2055 3. Varchar2
2056 4. Char.
2057 Other types are not supported at this point of time because PL/SQL language does not support
2058 dynamic binding of other types of variables. Probably we should use Java and do it in next release.
2059 **/
2060
2061 procedure execute_proc (
2062 i_transtage_id IN pls_integer,
2063 i_procedure_name IN Varchar2) IS
2064
2065 i_method_name varchar2(2000) := 'ecx_actions.execute_proc';
2066
2067 BEGIN
2068 if (l_procedureEnabled) then
2069 ecx_debug.push(i_method_name);
2070 end if;
2071 if(l_statementEnabled) then
2072 ecx_debug.log(l_statement,'i_transtage_id',i_transtage_id,i_method_name);
2073 ecx_debug.log(l_statement,'i_procedure_name',i_procedure_name,i_method_name);
2074 end if;
2075
2076 bind_proc_variables (i_transtage_id, i_procedure_name,
2080 ecx_utils.g_procedure_list(i_transtage_id).cursor_handle);
2077 ecx_utils.g_procedure_list(i_transtage_id).cursor_handle);
2078
2079 assign_out_variables (i_transtage_id, i_procedure_name,
2081
2082 if (l_procedureEnabled) then
2083 ecx_debug.pop(i_method_name);
2084 end if;
2085
2086 EXCEPTION
2087 WHEN ecx_utils.PROGRAM_EXIT then
2088 if (l_procedureEnabled) then
2089 ecx_debug.pop(i_method_name);
2090 end if;
2091 raise;
2092
2093 WHEN OTHERS THEN
2094 if(l_unexpectedEnabled) then
2095 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.EXECUTE_PROC');
2096 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2097 end if;
2098 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXECUTE_PROC');
2099 if (l_procedureEnabled) then
2100 ecx_debug.pop(i_method_name);
2101 end if;
2102 raise ecx_utils.PROGRAM_EXIT;
2103
2104 END execute_proc;
2105
2106 procedure exit_program
2107 IS
2108 i_method_name varchar2(2000) := 'ecx_actions.exit_program';
2109 begin
2110 ecx_debug.setErrorInfo(1,20,'ECX_USER_INVOKED_EXIT');
2111 raise ecx_utils.program_exit;
2112 exception
2113 WHEN ecx_utils.PROGRAM_EXIT then
2114 raise ecx_utils.program_exit;
2115 WHEN OTHERS THEN
2116 if(l_unexpectedEnabled) then
2117 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.EXIT_PROGRAM');
2118 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2119 end if;
2120 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXIT_PROGRAM');
2121 raise ecx_utils.PROGRAM_EXIT;
2122 end exit_program;
2123
2124 /** This procedure is overloaded below **/
2125 procedure set_error_exit_program(
2126 i_err_type in pls_integer,
2127 i_err_code in pls_integer,
2128 i_err_msg in varchar2
2129 )
2130 IS
2131
2132 i_method_name varchar2(2000) := 'ecx_actions.set_error_exit_program';
2133 i_len pls_integer := 0;
2134 i_prod_code_cnt pls_integer :=0;
2135
2136 /* Start of Bug 2186358 */
2137 i_msg varchar2(2000):= null;
2138 /* End of Bug 2186358 */
2139 begin
2140 if (l_procedureEnabled) then
2141 ecx_debug.push(i_method_name);
2142 end if;
2143 if(l_statementEnabled) then
2144 ecx_debug.log(l_statement,'Error Type : ' , i_err_type,i_method_name);
2145 ecx_debug.log(l_statement,'Error Code: ', i_err_code,i_method_name);
2146 ecx_debug.log(l_statement,'Error Msg: ', i_err_msg,i_method_name);
2147 end if;
2148
2149 i_msg := i_err_msg;
2150
2151 if (i_msg is null) then
2152 if(l_statementEnabled) then
2153 ecx_debug.log(l_statement,'Resetting error msg',i_method_name);
2154 end if;
2155 i_msg := 'ECX_USER_INVOKED_EXIT';
2156 end if;
2157
2158 if ((i_err_msg not like 'ECX%') and (i_err_msg not like 'WF%'))
2159 then
2160 i_len := instr(i_msg, '_') - 1;
2161 /* if i_len <= 20 then
2162 ecx_utils.g_cust_msg_code := substr(i_msg, 1, i_len);
2163 end if;*/
2164 select count(*) into i_prod_code_cnt from fnd_application where APPLICATION_SHORT_NAME = substr(i_msg, 1, i_len);
2165 if i_prod_code_cnt = 1 then
2166 ecx_utils.g_cust_msg_code := substr(i_msg, 1, i_len);
2167 end if;
2168 end if;
2169 if(l_statementEnabled) then
2170 ecx_debug.log(l_statement,'Product Code' , ecx_utils.g_cust_msg_code,i_method_name);
2171 end if;
2172
2173 ecx_utils.set_error(p_error_type => i_err_type,
2174 p_error_code => i_err_code,
2175 p_error_msg => i_msg);
2176
2177 if(l_statementEnabled) then
2178 ecx_debug.log(l_statement,'Raising program exit',i_method_name);
2179 end if;
2180 raise ecx_utils.program_exit;
2181 if (l_procedureEnabled) then
2182 ecx_debug.pop(i_method_name);
2183 end if;
2184 exception
2185 WHEN ecx_utils.PROGRAM_EXIT then
2186 if (l_procedureEnabled) then
2187 ecx_debug.pop(i_method_name);
2188 end if;
2189 raise ecx_utils.program_exit;
2190 WHEN OTHERS THEN
2191 if (l_procedureEnabled) then
2192 ecx_debug.pop(i_method_name);
2193 end if;
2194 /*if(l_statementEnabled) then
2195 ecx_debug.log(l_statement,'ECX','ECX_PROGRAM_ERROR',i_method_name,
2196 'PROGRESS_LEVEL','ECX_ACTIONS.SET_ERR_EXIT_PROGRAM');
2197 ecx_debug.log(l_statement,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2198 end if;*/
2199 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.SET_ERR_EXIT_PROGRAM');
2200 raise ecx_utils.PROGRAM_EXIT;
2201 end set_error_exit_program;
2202
2203 /** Overloaded for enabling MLS for user invoked error
2204 messages with parameters -2535659 **/
2205 procedure set_error_exit_program(
2206 i_err_type in pls_integer,
2207 i_err_code in pls_integer,
2208 i_err_msg in varchar2,
2209 p_token1 in varchar2,
2210 p_value1 in varchar2,
2211 p_token2 in varchar2,
2215 p_token4 in varchar2,
2212 p_value2 in varchar2,
2213 p_token3 in varchar2,
2214 p_value3 in varchar2,
2216 p_value4 in varchar2,
2217 p_token5 in varchar2,
2218 p_value5 in varchar2,
2219 p_token6 in varchar2,
2220 p_value6 in varchar2,
2221 p_token7 in varchar2,
2222 p_value7 in varchar2,
2223 p_token8 in varchar2,
2224 p_value8 in varchar2,
2225 p_token9 in varchar2,
2226 p_value9 in varchar2,
2227 p_token10 in varchar2,
2228 p_value10 in varchar2
2229 )
2230 IS
2231 i_method_name varchar2(2000) := 'ecx_actions.set_error_exit_program';
2232 i_len pls_integer := 0;
2233 i_prod_code_cnt pls_integer :=0;
2234
2235 /* Start of Bug 2186358 */
2236 i_msg varchar2(2000):= null;
2237 /* End of Bug 2186358 */
2238 begin
2239 if (l_procedureEnabled) then
2240 ecx_debug.push(i_method_name);
2241 end if;
2242 if(l_statementEnabled) then
2243 ecx_debug.log(l_statement,'Error Type : ' , i_err_type,i_method_Name);
2244 ecx_debug.log(l_statement,'Error Code: ', i_err_code,i_method_Name);
2245 ecx_debug.log(l_statement,'Error Msg: ', i_err_msg,i_method_Name);
2246 ecx_debug.log(l_statement,'Param1' , p_token1,i_method_Name);
2247 ecx_debug.log(l_statement,'Value1' , p_value1,i_method_Name);
2248 ecx_debug.log(l_statement,'Param2' , p_token2,i_method_Name);
2249 ecx_debug.log(l_statement,'Value2' , p_value2,i_method_Name);
2250 ecx_debug.log(l_statement,'Param3' , p_token3,i_method_Name);
2251 ecx_debug.log(l_statement,'Value3' , p_value3,i_method_Name);
2252 ecx_debug.log(l_statement,'Param4' , p_token4,i_method_Name);
2253 ecx_debug.log(l_statement,'Value4' , p_value4,i_method_Name);
2254 ecx_debug.log(l_statement,'Param5' , p_token5,i_method_Name);
2255 ecx_debug.log(l_statement,'Value5' , p_value5,i_method_Name);
2256 ecx_debug.log(l_statement,'Param6' , p_token6,i_method_Name);
2257 ecx_debug.log(l_statement,'Value6' , p_value6,i_method_Name);
2258 ecx_debug.log(l_statement,'Param7' , p_token7,i_method_Name);
2259 ecx_debug.log(l_statement,'Value7' , p_value7,i_method_Name);
2260 ecx_debug.log(l_statement,'Param8' , p_token8,i_method_Name);
2261 ecx_debug.log(l_statement,'Value8' , p_value8,i_method_Name);
2262 ecx_debug.log(l_statement,'Param9' , p_token9,i_method_Name);
2263 ecx_debug.log(l_statement,'Value9' , p_value9,i_method_Name);
2264 ecx_debug.log(l_statement,'Param10' , p_token10,i_method_Name);
2265 ecx_debug.log(l_statement,'Value10' , p_value10,i_method_Name);
2266 end if;
2267 i_msg := i_err_msg;
2268
2269 if (i_msg is null) then
2270 if(l_statementEnabled) then
2271 ecx_debug.log(l_statement,'Resetting error msg',i_method_name);
2272 end if;
2273 i_msg := 'ECX_USER_INVOKED_EXIT';
2274 end if;
2275
2276 if ((i_err_msg not like 'ECX%') and (i_err_msg not like 'WF%'))
2277 then
2278 i_len := instr(i_msg, '_') - 1;
2279 /* if i_len <= 20 then
2280 ecx_utils.g_cust_msg_code := substr(i_msg, 1, i_len);
2281 end if;*/
2282 select count(*) into i_prod_code_cnt from fnd_application where APPLICATION_SHORT_NAME = substr(i_msg, 1, i_len);
2283 if i_prod_code_cnt = 1 then
2284 ecx_utils.g_cust_msg_code := substr(i_msg, 1, i_len);
2285 end if;
2286 end if;
2287 if(l_statementEnabled) then
2288 ecx_debug.log(l_statement,'Product Code' , ecx_utils.g_cust_msg_code,i_method_name);
2289 end if;
2290
2291 ecx_utils.set_error(p_error_type => i_err_type,
2292 p_error_code => i_err_code,
2293 p_error_msg => i_msg,
2294 p_token1 => p_token1,
2295 p_value1 => p_value1,
2296 p_token2 => p_token2,
2297 p_value2 => p_value2,
2298 p_token3 => p_token3,
2299 p_value3 => p_value3,
2300 p_token4 => p_token4,
2301 p_value4 => p_value4,
2302 p_token5 => p_token5,
2303 p_value5 => p_value5,
2304 p_token6 => p_token6,
2305 p_value6 => p_value6,
2306 p_token7 => p_token7,
2307 p_value7 => p_value7,
2308 p_token8 => p_token8,
2309 p_value8 => p_value8,
2310 p_token9 => p_token9,
2311 p_value9 => p_value9,
2312 p_token10 => p_token10,
2313 p_value10 => p_value10);
2314
2315 if(l_statementEnabled) then
2316 ecx_debug.log(l_statement,'Raising program exit',i_method_name);
2317 end if;
2318 raise ecx_utils.program_exit;
2319 if (l_procedureEnabled) then
2320 ecx_debug.pop(i_method_name);
2321 end if;
2322
2323 exception
2324 WHEN ecx_utils.PROGRAM_EXIT then
2325 if (l_procedureEnabled) then
2326 ecx_debug.pop(i_method_name);
2327 end if;
2331 ecx_debug.pop(i_method_name);
2328 raise ecx_utils.program_exit;
2329 WHEN OTHERS THEN
2330 if (l_procedureEnabled) then
2332 end if;
2333 if(l_unexpectedEnabled) then
2334 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
2335 'PROGRESS_LEVEL','ECX_ACTIONS.SET_ERR_EXIT_PROGRAM');
2336 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2337 end if;
2338 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.SET_ERR_EXIT_PROGRAM');
2339 raise ecx_utils.PROGRAM_EXIT;
2340 end set_error_exit_program;
2341
2342
2343
2344 procedure send_err (
2345 i_variable_level IN pls_integer,
2346 i_variable_name IN Varchar2,
2347 i_variable_direction IN Varchar2,
2348 i_variable_pos IN pls_integer,
2349 i_variable_constant IN varchar2,
2350 i_previous_variable_level IN pls_integer,
2351 i_previous_variable_name IN Varchar2,
2352 i_previous_variable_direction IN Varchar2,
2353 i_previous_variable_pos IN pls_integer,
2354 i_previous_variable_constant IN varchar2) IS
2355
2356 var_value Varchar2(4000);
2357 var_on_stack Boolean := FALSE;
2358 var_stack_pos pls_integer;
2359 pre_var_value Varchar2(4000);
2360 pre_var_on_stack Boolean := FALSE;
2361 pre_var_stack_pos pls_integer;
2362 o_ret_code pls_integer;
2363 o_ret_msg Varchar2(2000);
2364
2365 i_method_name varchar2(2000) := 'ecx_actions.send_err';
2366 BEGIN
2367 if (l_procedureEnabled) then
2368 ecx_debug.push(i_method_name);
2369 end if;
2370
2371
2372 /** First get the COnstant and then look for the variable value **/
2373 var_value := i_variable_constant;
2374 if var_value is null
2375 then
2376 get_var_attr (i_variable_level, i_variable_name,
2377 i_variable_direction,i_variable_pos,
2378 var_value, var_on_stack, var_stack_pos);
2379 end if;
2380
2381 if var_value = 'NULL'
2382 then
2383 var_value :=null;
2384 end if;
2385
2386 pre_var_value := i_previous_variable_constant;
2387 if pre_var_value is null
2388 then
2389 get_var_attr (i_previous_variable_level, i_previous_variable_name,
2390 i_previous_variable_direction,
2391 i_previous_variable_pos, pre_var_value, pre_var_on_stack,
2392 pre_var_stack_pos);
2393
2394 end if;
2395
2396 if pre_var_value = 'NULL'
2397 then
2398 pre_var_value :=null;
2399 end if;
2400
2401 ecx_errorlog.send_error (to_number(var_value),
2402 pre_var_value,
2403 ecx_utils.g_snd_tp_id,
2404 ecx_utils.g_document_id,
2405 ecx_utils.g_transaction_type,
2406 o_ret_code,
2407 o_ret_msg);
2408
2409 if (l_procedureEnabled) then
2410 ecx_debug.pop(i_method_name);
2411 end if;
2412
2413 EXCEPTION
2414 WHEN ecx_utils.PROGRAM_EXIT then
2415 if (l_procedureEnabled) then
2416 ecx_debug.pop(i_method_name);
2417 end if;
2418 raise;
2419
2420 WHEN OTHERS THEN
2421 if(l_unexpectedEnabled) then
2422 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
2423 'PROGRESS_LEVEL','ECX_ACTIONS.SEND_ERR');
2424 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2425 end if;
2426 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.SEND_ERR');
2427 if (l_procedureEnabled) then
2428 ecx_debug.pop(i_method_name);
2429 end if;
2430 raise ecx_utils.PROGRAM_EXIT;
2431 END send_err;
2432
2433
2434 procedure get_api_retcode (
2435 i_variable_level IN pls_integer,
2436 i_variable_name IN Varchar2,
2437 i_variable_direction IN Varchar2,
2438 i_variable_pos IN pls_integer,
2439 i_default_value IN varchar2,
2440 i_previous_variable_level IN pls_integer,
2441 i_previous_variable_name IN varchar2,
2442 i_previous_variable_direction IN varchar2,
2443 i_previous_variable_pos IN pls_integer,
2444 i_function_name IN varchar2) IS
2445
2446 i_method_name varchar2(2000) := 'ecx_actions.get_api_retcode';
2447
2448 var_value Varchar2(2000);
2449 var_on_stack Boolean := FALSE;
2450 var_stack_pos pls_integer;
2451 pre_var_value Varchar2(2000);
2452 pre_var_on_stack Boolean := FALSE;
2453 pre_var_stack_pos pls_integer;
2454 ret_code Varchar2(1);
2455
2456 BEGIN
2457 if (l_procedureEnabled) then
2458 ecx_debug.push(i_method_name);
2459 end if;
2460
2461 get_var_attr (i_variable_level, i_variable_name,
2462 i_variable_direction,
2463 i_variable_pos, var_value, var_on_stack,
2464 var_stack_pos);
2465
2466 if i_default_value is not null
2470 then
2467 then
2468
2469 if i_default_value = 'CODE_CONVERSION'
2471 if i_previous_variable_direction = 'S'
2472 then
2473 ret_code := ecx_utils.g_source(i_previous_variable_pos).xref_retcode;
2474 else
2475 ret_code := ecx_utils.g_target(i_previous_variable_pos).xref_retcode;
2476 end if;
2477 if(l_statementEnabled) then
2478 ecx_debug.log(l_statement,'return code', ret_code, i_method_name);
2479 end if;
2480 assign_value (var_on_stack, var_stack_pos,
2481 i_variable_pos,i_variable_direction, ret_code);
2482
2483 elsif i_default_value = 'DOCUMENT_ID'
2484 then
2485 assign_value (var_on_stack, var_stack_pos,
2486 i_variable_pos,i_variable_direction, ecx_utils.g_document_id);
2487
2488 elsif i_default_value = 'RET_CODE'
2489 then
2490 assign_value (var_on_stack, var_stack_pos,
2491 i_variable_pos,i_variable_direction, ecx_utils.i_ret_code);
2492
2493 elsif i_default_value = 'RET_MESG'
2494 then
2495 assign_value (var_on_stack, var_stack_pos,
2496 i_variable_pos,i_variable_direction, ecx_utils.i_errbuf);
2497 elsif i_default_value = 'SENDER_TP_ID'
2498 then
2499 assign_value (var_on_stack, var_stack_pos,
2500 i_variable_pos,i_variable_direction, ecx_utils.g_snd_tp_id);
2501 elsif i_default_value = 'RECEIVER_TP_ID'
2502 then
2503 assign_value (var_on_stack, var_stack_pos,
2504 i_variable_pos,i_variable_direction, ecx_utils.g_rec_tp_id);
2505
2506 elsif i_default_value = 'ORG_ID'
2507 then
2508 assign_value (var_on_stack, var_stack_pos,
2509 i_variable_pos,i_variable_direction, ecx_utils.g_org_id);
2510 end if;
2511 end if;
2512
2513 if (l_procedureEnabled) then
2514 ecx_debug.pop(i_method_name);
2515 end if;
2516
2517 EXCEPTION
2518 WHEN ecx_utils.PROGRAM_EXIT then
2519 if (l_procedureEnabled) then
2520 ecx_debug.pop(i_method_name);
2521 end if;
2522 raise;
2523
2524 WHEN OTHERS THEN
2525 if(l_unexpectedEnabled) then
2526 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
2527 'PROGRESS_LEVEL','ECX_ACTIONS.GET_API_RETCODE');
2528 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2529 end if;
2530 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_API_RETCODE');
2531 if (l_procedureEnabled) then
2532 ecx_debug.pop(i_method_name);
2533 end if;
2534 raise ecx_utils.PROGRAM_EXIT;
2535 END get_api_retcode;
2536
2537
2538 procedure split_number (
2539 p_number IN NUMBER,
2540 p_value OUT NOCOPY VARCHAR2,
2541 p_sign OUT NOCOPY VARCHAR2,
2542 p_numofdec OUT NOCOPY VARCHAR2) IS
2543
2544 i_method_name varchar2(2000) := 'ecx_actions.split_number';
2545
2546 num1 NUMBER := 0;
2547 num2 NUMBER := 0;
2548 numchar VARCHAR2(40);
2549 nls_dec_char VARCHAR2(1);
2550 charvalue VARCHAR2(100);
2551 /*http://st-doc.us.oracle.com/9.0/9202/appdev.920/a96624/03_types.htm#10680
2552 The maximum precision (total number of digits) of a NUMBER value is 38 decimal digits. */
2553 /*max_dec_length is max (theoritical) num of digits in decimal part.
2554 Actual num of digits in decimal part will be always less than or equal to 38.
2555 If num > 0 then total number of digits will be 39 or less
2556 If num < 0 then total number of digits will be 38 or less
2557
2558 few examples of expected behaviour of this procedure:
2559 p_number = 1.0123456789 ==> p_value = 10123456789, p_sign = +, p_numofdec = 10
2560 p_number = -00.012345678901234567890123456789012345678901234567899123456789 ==> p_value = 1234567890123456789012345678901234568, p_sign = -, p_numofdec = 38
2561 p_number = +12345.000001234567890123456789012345678901234567890123456789 ==> p_value = 123450000012345678901234567890123456789, p_sign = +, p_numofdec = 34
2562 p_number = +12345000001234567890123456789012345678901234567890123456789.12 ==> p_value = 12345000001234567890123456789012345678900000000000000000000, p_sign = +, p_numofdec = null
2563 p_number = +1.000001234567890123456789012345678901234567890123456789 ==> p_value = 100000123456789012345678901234567890123, p_sign = +, p_numofdec = 38
2564 p_number = -12345678900.00000001234567890123456789012345678901234567890123456789 ==> p_value = 123456789000000000123456789012345678901, p_sign = -, p_numofdec = 28
2565 p_number = -.014400 ==> p_value = 144, p_sign = -, p_numofdec = 4
2566 p_number = -.54 ==> p_value = 54, p_sign = -, p_numofdec = 2
2567 p_number = +00.054040 ==> p_value = 5404, p_sign = +, p_numofdec = 5
2568 */
2569 max_dec_length PLS_INTEGER := 38;
2570
2571 begin
2572 if (l_procedureEnabled) then
2573 ecx_debug.push(i_method_name);
2574 end if;
2575 /* Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
2576 precision => the total number of digits */
2577
2578 /* Bug #2319022 */
2579 if (p_number = 0 ) then
2580 p_value := 0;
2581 p_sign := '+';
2582 p_numofdec := 0;
2583 return;
2584 end if;
2585
2586 /* Determine Sign */
2587 IF nvl(p_number, 0) < 0 THEN
2591 END IF;
2588 p_sign := '-';
2589 ELSE
2590 p_sign := '+';
2592 if(l_statementEnabled) then
2593 ecx_debug.log(l_statement,'sign', p_sign,i_method_name);
2594 end if;
2595
2596 /* Get Value */
2597 num1 := trunc(p_number,0);
2598 num2 := mod(p_number, num1);
2599
2600 /* if num2 is not null then */
2601 if ( num2 <> 0 ) then
2602 num2 := round(num2, max_dec_length);
2603 numchar := substrb(to_char(abs(num2)),2);
2604 end if;
2605
2606 if (num1 = 0) then
2607 p_value := abs(to_number(numchar));
2608 else
2609 p_value := ltrim(rtrim(to_char(abs(num1)) || numchar));
2610 end if;
2611 if(l_statementEnabled) then
2612 ecx_debug.log(l_statement,'value',p_value,i_method_name);
2613 end if;
2614
2615 -- Get Num of Decimal places
2616 /* select substr(value, 1, 1)
2617 into nls_dec_char
2618 from v$nls_parameters
2619 where parameter = 'NLS_NUMERIC_CHARACTERS';
2620
2621 if(num1 = 0) then
2622 charvalue := ltrim(rtrim(to_char(abs(p_number)), max_dec_length));
2623 else
2624 charvalue := ltrim(rtrim(to_char(abs(p_number))));
2625 end if;
2626
2627 if (instrb(charvalue, nls_dec_char) > 0) then
2628 p_numofdec := lengthb(charvalue) - instrb(charvalue, nls_dec_char);
2629 if (p_numofdec > max_dec_length) then
2630 p_numofdec := max_dec_length;
2631 end if;
2632 else
2633 p_numofdec := 0;
2634 end if;
2635 */
2636 p_numofdec := lengthb(numchar);
2637
2638 if(l_statementEnabled) then
2639 ecx_debug.log(l_statement, 'numofdec', p_numofdec, i_method_name);
2640 ecx_debug.pop(i_method_name);
2641 end if;
2642
2643 EXCEPTION
2644 WHEN ecx_utils.PROGRAM_EXIT then
2645 if (l_procedureEnabled) then
2646 ecx_debug.pop(i_method_name);
2647 end if;
2648 raise;
2649
2650 WHEN OTHERS THEN
2651 if(l_unexpectedEnabled) then
2652 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.SPLIT_NUMBER');
2653 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2654 end if;
2655 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.SPLIT_NUMBER');
2656 if (l_procedureEnabled) then
2657 ecx_debug.pop(i_method_name);
2658 end if;
2659 raise ecx_utils.PROGRAM_EXIT;
2660
2661 end split_number;
2662
2663 /**
2664 Returns the server timezone's offset from GMT in OAG format
2665 **/
2666 function gmt_offset(
2667 i_year varchar2,
2668 i_month varchar2,
2669 i_day varchar2,
2670 i_hour varchar2,
2671 i_minute varchar2,
2672 i_second varchar2
2673
2674 ) return varchar2
2675 is
2676
2677 i_method_name varchar2(2000) := 'ecx_actions.gmt_offset';
2678 i_server_offset number;
2679 i_server_offset_hours number;
2680 i_server_offset_mins number;
2681 i_timezone varchar2(500);
2682 i_string varchar2(2000);
2683 i_timezone_sign varchar2(80);
2684 begin
2685 if (l_procedureEnabled) then
2686 ecx_debug.push(i_method_name);
2687 end if;
2688
2689 if (ecx_actions.g_server_timezone is null) then
2690 --- Check for the Installation Type ( Standalone or Embedded );
2691 if (ecx_utils.g_install_mode is null) then
2692 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
2693 end if;
2694
2695 if ecx_utils.g_install_mode = 'EMBEDDED'
2696 then
2697 i_string := 'begin
2698 fnd_profile.get('||'''ECX_SERVER_TIMEZONE'''||',ecx_actions.g_server_timezone);
2699 end;';
2700 execute immediate i_string ;
2701 else
2702 ecx_actions.g_server_timezone:= wf_core.translate('ECX_SERVER_TIMEZONE');
2703 end if;
2704 end if;
2705
2706
2707 -- if profile option is not set assume gmt
2708 if (ecx_actions.g_server_timezone is null) then
2709 ecx_actions.g_server_timezone := 'GMT';
2710 end if;
2711
2712 -- get the DB server offset from the Java API
2713 i_server_offset := getTimeZoneOffset(to_number(i_year), to_number(i_month),
2714 to_number(i_day), to_number(i_hour),
2715 to_number(i_minute), to_number(i_second),
2716 ecx_actions.g_server_timezone);
2717 if i_server_offset >= 0 then
2718 i_timezone_sign := '+';
2719 end if;
2720
2721 -- calculate the timezone in the OAG format
2722 i_server_offset_hours := floor(i_server_offset);
2723 i_server_offset_mins := (i_server_offset * 60) mod 60;
2724 i_timezone := rtrim(ltrim(to_char(i_server_offset_hours, '09'))) ||
2725 rtrim(ltrim(to_char(i_server_offset_mins, '09')));
2726
2727 if i_timezone_sign is NOT NULL then
2728 i_timezone := i_timezone_sign || i_timezone;
2729 end if;
2730 if(l_statementEnabled) then
2731 ecx_debug.log(l_statement,'i_server_offset', i_server_offset,i_method_name);
2732 ecx_debug.log(l_statement, 'i_server_offset_hours', i_server_offset_hours,i_method_name);
2736 if (l_procedureEnabled) then
2733 ecx_debug.log(l_statement, 'i_server_offset_mins', i_server_offset_mins,i_method_name);
2734 end if;
2735
2737 ecx_debug.pop(i_method_name);
2738 end if;
2739
2740
2741 return(i_timezone);
2742 exception
2743 WHEN OTHERS THEN
2744 if(l_unexpectedEnabled) then
2745 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.GMT_OFFSET');
2746 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2747 end if;
2748 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GMT_OFFSET');
2749 if (l_procedureEnabled) then
2750 ecx_debug.pop(i_method_name);
2751 end if;
2752 raise ecx_utils.PROGRAM_EXIT;
2753 end gmt_offset;
2754
2755 procedure convert_to_oag_date(
2756 i_variable_level IN pls_integer,
2757 i_variable_name IN varchar2,
2758 i_variable_pos IN pls_integer,
2759 i_variable_direction IN varchar2,
2760 i_opr1_level IN pls_integer,
2761 i_opr1_name IN varchar2,
2762 i_opr1_pos IN pls_integer,
2763 i_opr1_direction IN varchar2,
2764 i_opr1_constant in varchar2
2765 ) IS
2766
2767 i_method_name varchar2(2000) := 'ecx_actions.convert_to_oag_date';
2768 TYPE oag_dt_tbl is table of varchar2(10) index by BINARY_INTEGER;
2769
2770 variable_value varchar2(2000);
2771 var date;
2772 stack_var boolean := FALSE;
2773 stack_pos pls_integer;
2774 var_pos pls_integer;
2775 oag_date_tbl oag_dt_tbl;
2776 opr1_stack boolean := FALSE;
2777 opr1_stack_pos pls_integer;
2778 opr1_value varchar2(2000);
2779
2780 begin
2781 if (l_procedureEnabled) then
2782 ecx_debug.push(i_method_name);
2783 end if;
2784 if(l_statementEnabled) then
2785 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
2786 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
2787 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
2788 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
2789 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
2790 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
2791 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
2792 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
2793 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
2794 end if;
2795
2796 opr1_value := i_opr1_constant;
2797 if opr1_value is null
2798 then
2799 get_var_attr(i_opr1_level, i_opr1_name, i_opr1_direction,
2800 i_opr1_pos, opr1_value, opr1_stack, opr1_stack_pos);
2801 end if;
2802 if opr1_value = 'NULL'
2803 then
2804 opr1_value :=null;
2805 end if;
2806
2807 /** Resultant variable **/
2808 get_var_attr(i_variable_level, i_variable_name, i_variable_direction,
2809 i_variable_pos, variable_value, stack_var, stack_pos);
2810
2811 var := to_date(opr1_value, 'YYYYMMDD HH24MISS');
2812
2813 oag_date_tbl(1) := to_char(var,'YYYY');
2814 oag_date_tbl(2) := to_char(var,'MM');
2815 oag_date_tbl(3) := to_char(var,'DD');
2816 oag_date_tbl(4) := to_char(var,'HH24');
2817 oag_date_tbl(5) := to_char(var,'MI');
2818 oag_date_tbl(6) := to_char(var,'SS');
2819 if(opr1_value is not null) then
2820 oag_date_tbl(7) := '0000';
2821 oag_date_tbl(8) := gmt_offset(oag_date_tbl(1), oag_date_tbl(2), oag_date_tbl(3),
2822 oag_date_tbl(4), oag_date_tbl(5), oag_date_tbl(6));
2823 end if;
2824
2825 var_pos := i_variable_pos+1;
2826
2827 if(l_statementEnabled) then
2828 ecx_debug.log(l_statement,'name',ecx_utils.g_target(var_pos).attribute_name,i_method_name);
2829 end if;
2830 if ecx_utils.g_target(var_pos).attribute_name = 'qualifier' then
2831 var_pos := var_pos + 1;
2832 end if;
2833
2834 if ecx_utils.g_target(var_pos).attribute_name = 'type' then
2835 var_pos := var_pos + 1;
2836 end if;
2837 if ecx_utils.g_target(var_pos).attribute_name = 'index' then
2838 var_pos := var_pos +1;
2839 end if;
2840
2841 for i in 1..oag_date_tbl.COUNT loop
2842 assign_value(stack_var, stack_pos, var_pos,i_variable_direction, oag_date_tbl(i));
2843 if(l_statementEnabled) then
2844 ecx_debug.log(l_statement,'name',ecx_utils.g_target(var_pos).attribute_name,i_method_name);
2845 ecx_debug.log(l_statement,'value',ecx_utils.g_target(var_pos).value,i_method_name);
2846 end if;
2847 var_pos := var_pos + 1;
2848 end loop;
2849
2850 /** make the value of the DATETIME to null **/
2851 ecx_utils.g_target(i_variable_pos).value:=null;
2852
2853 if (l_procedureEnabled) then
2854 ecx_debug.pop(i_method_name);
2855 end if;
2856
2857
2858 EXCEPTION
2859 WHEN ecx_utils.PROGRAM_EXIT then
2860 if (l_procedureEnabled) then
2861 ecx_debug.pop(i_method_name);
2862 end if;
2863 raise;
2864
2865 WHEN OTHERS THEN
2866 if(l_unexpectedEnabled) then
2870 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_TO_OAG_DATE');
2867 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_TO_OAG_DATE');
2868 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
2869 end if;
2871 if (l_procedureEnabled) then
2872 ecx_debug.pop(i_method_name);
2873 end if;
2874 raise ecx_utils.PROGRAM_EXIT;
2875 end convert_to_oag_date;
2876
2877
2878 procedure convert_to_oag_operamt(
2879 i_variable_level IN pls_integer,
2880 i_variable_name IN varchar2,
2881 i_variable_pos IN pls_integer,
2882 i_variable_direction IN varchar2,
2883 i_opr1_level IN pls_integer,
2884 i_opr1_name IN Varchar2,
2885 i_opr1_pos IN pls_integer,
2886 i_opr1_direction IN Varchar2,
2887 i_opr1_constant IN varchar2,
2888 i_opr2_level IN pls_integer,
2889 i_opr2_name IN Varchar2,
2890 i_opr2_pos IN pls_integer,
2891 i_opr2_direction IN Varchar2,
2892 i_opr2_constant IN varchar2,
2893 i_opr3_level IN pls_integer,
2894 i_opr3_name IN Varchar2,
2895 i_opr3_pos IN pls_integer,
2896 i_opr3_direction IN Varchar2,
2897 i_opr3_constant IN varchar2
2898 ) IS
2899
2900 i_method_name varchar2(2000) := 'ecx_actions.convert_to_oag_operamt';
2901 TYPE oag_oamt_tbl is table of VARCHAR2(2000) index by BINARY_INTEGER;
2902 /** For Resultant **/
2903
2904 var_value varchar2(2000);
2905 stack_var boolean := FALSE;
2906 stack_pos pls_integer;
2907
2908 /** For Opr1 **/
2909 opr1_stack Boolean := FALSE;
2910 opr1_stack_pos pls_integer;
2911 i_amount NUMBER;
2912 i_opr1_value varchar2(2000);
2913
2914 /** For Opr2 **/
2915 opr2_stack Boolean := FALSE;
2916 opr2_stack_pos pls_integer;
2917 i_curr_code Varchar2(2000);
2918
2919 /** For Opr3 **/
2920 opr3_stack Boolean := FALSE;
2921 opr3_stack_pos pls_integer;
2922 i_uom_code Varchar2(2000) := 'EACH';
2923 var_pos pls_integer;
2924 i_value varchar2(2000);
2925 i_sign varchar2(1);
2926 i_numofdec varchar2(100);
2927 numofdec number;
2928 p_numofdec number := 0;
2929 v_numofdec number :=0;
2930 i_uom_quant number :=1;
2931 i_uom_value varchar2(2000) := '1';
2932 i_uomnumofdec varchar2(100) := '0';
2933 i_uomsign varchar2(1) :='+';
2934 oag_operamt_tbl oag_oamt_tbl;
2935
2936 begin
2937 if (l_procedureEnabled) then
2938 ecx_debug.push(i_method_name);
2939 end if;
2940
2941 if(l_statementEnabled) then
2942 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
2943 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
2944 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
2945 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
2946 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
2947 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
2948 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
2949 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
2950 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
2951 ecx_debug.log(l_statement,'i_opr2_level',i_opr2_level,i_method_name);
2952 ecx_debug.log(l_statement,'i_opr2_name',i_opr2_name,i_method_name);
2953 ecx_debug.log(l_statement,'i_opr2_pos',i_opr2_pos,i_method_name);
2954 ecx_debug.log(l_statement,'i_opr2_direction',i_opr2_direction,i_method_name);
2955 ecx_debug.log(l_statement,'i_opr2_constant',i_opr2_constant,i_method_name);
2956 ecx_debug.log(l_statement,'i_opr3_level',i_opr3_level,i_method_name);
2957 ecx_debug.log(l_statement,'i_opr3_name',i_opr3_name,i_method_name);
2958 ecx_debug.log(l_statement,'i_opr3_pos',i_opr3_pos,i_method_name);
2959 ecx_debug.log(l_statement,'i_opr3_direction',i_opr3_direction,i_method_name);
2960 ecx_debug.log(l_statement,'i_opr3_constant',i_opr3_constant,i_method_name);
2961 end if;
2962
2963 get_var_attr(i_variable_level, i_variable_name,
2964 i_variable_direction,i_variable_pos,
2965 var_value, stack_var, stack_pos);
2966
2967
2968 i_opr1_value := i_opr1_constant;
2969 if i_opr1_value is null
2970 then
2971 get_var_attr(i_opr1_level, i_opr1_name,
2972 i_opr1_direction,i_opr1_pos,
2973 i_opr1_value, opr1_stack, opr1_stack_pos);
2974 end if;
2975
2976 if i_opr1_value = 'NULL'
2977 then
2978 i_opr1_value :=null;
2979 end if;
2980
2981 i_amount := to_number(i_opr1_value);
2982
2983 split_number(i_amount, i_value, i_sign, i_numofdec);
2984
2985 /** Currency Code **/
2989 if i_opr2_level is not null
2986 i_curr_code := i_opr2_constant;
2987 if i_curr_code is null
2988 then
2990 then
2991 get_var_attr (i_opr2_level, i_opr2_name,
2992 i_opr2_direction,i_opr2_pos,
2993 i_curr_code, opr2_stack,
2994 opr2_stack_pos);
2995 end if;
2996 end if;
2997
2998 if i_curr_code = 'NULL'
2999 then
3000 i_curr_code :=null;
3001 end if;
3002
3003 /** UOM Code **/
3004 i_uom_code := i_opr3_constant;
3005 if i_uom_code is null
3006 then
3007 if i_opr3_level is not null
3008 then
3009 get_var_attr (i_opr3_level, i_opr3_name,
3010 i_opr3_direction,
3011 i_opr3_pos, i_uom_code, opr3_stack,
3012 opr3_stack_pos);
3013 end if;
3014 end if;
3015
3016 if i_uom_code = 'NULL'
3017 then
3018 i_uom_code :=null;
3019 end if;
3020
3021 if (i_uom_quant <> 1) then
3022 split_number(i_uom_quant, i_uom_value,i_uomsign, i_uomnumofdec);
3023 end if;
3024
3025 oag_operamt_tbl(1) := i_value;
3026 if (i_value is not null) then
3027 oag_operamt_tbl(2) := i_numofdec;
3028 oag_operamt_tbl(3) := i_sign;
3029 oag_operamt_tbl(4) := i_curr_code; --currency code
3030 oag_operamt_tbl(5) := i_uom_value;
3031 oag_operamt_tbl(6) := i_uomnumofdec;
3032 oag_operamt_tbl(7) := i_uom_code;
3033 end if;
3034
3035 -- UOM code
3036 var_pos := i_variable_pos +3;
3037
3038 for i in 1..oag_operamt_tbl.COUNT loop
3039 assign_value(stack_var, stack_pos, var_pos, i_variable_direction,oag_operamt_tbl(i));
3040 var_pos := var_pos +1;
3041 end loop;
3042
3043 if (l_procedureEnabled) then
3044 ecx_debug.pop(i_method_name);
3045 end if;
3046
3047 EXCEPTION
3048 WHEN ecx_utils.PROGRAM_EXIT then
3049 if (l_procedureEnabled) then
3050 ecx_debug.pop(i_method_name);
3051 end if;
3052 raise;
3053
3054 WHEN OTHERS THEN
3055 if(l_unexpectedEnabled) then
3056 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_TO_OAG_OPERAMT');
3057 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3058 end if;
3059 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_TO_OAG_OPERAMT');
3060 if (l_procedureEnabled) then
3061 ecx_debug.pop(i_method_name);
3062 end if;
3063 raise ecx_utils.PROGRAM_EXIT;
3064 end convert_to_oag_operamt;
3065
3066 procedure convert_to_oag_amt(
3067 i_variable_level IN pls_integer,
3068 i_variable_name IN varchar2,
3069 i_variable_pos IN pls_integer,
3070 i_variable_direction IN varchar2,
3071 i_opr1_level IN pls_integer,
3072 i_opr1_name IN Varchar2,
3073 i_opr1_pos IN pls_integer,
3074 i_opr1_direction IN Varchar2,
3075 i_opr1_constant IN varchar2,
3076 i_opr2_level IN pls_integer,
3077 i_opr2_name IN Varchar2,
3078 i_opr2_pos IN pls_integer,
3079 i_opr2_direction IN Varchar2,
3080 i_opr2_constant IN varchar2,
3081 i_opr3_level IN pls_integer,
3082 i_opr3_name IN Varchar2,
3083 i_opr3_pos IN pls_integer,
3084 i_opr3_direction IN Varchar2,
3085 i_opr3_constant IN varchar2
3086 )
3087 is
3088
3089 i_method_name varchar2(2000) := 'ecx_actions.convert_to_oag_amt';
3090 TYPE oag_oamt_tbl is table of VARCHAR2(2000) index by BINARY_INTEGER;
3091
3092 var_value varchar2(2000);
3093 stack_var boolean := FALSE;
3094 stack_pos pls_integer;
3095
3096 /** For Opr1 **/
3097 opr1_stack Boolean := FALSE;
3098 opr1_stack_pos pls_integer;
3099 i_opr1_value varchar2(2000);
3100 i_amount NUMBER;
3101
3102 /** For Opr2 **/
3103 opr2_stack Boolean := FALSE;
3104 opr2_stack_pos pls_integer;
3105 i_opr2_value Varchar2(2000);
3106
3107 /** For Opr3 **/
3108 opr3_stack Boolean := FALSE;
3109 opr3_stack_pos pls_integer;
3110 i_opr3_value Varchar2(2000);
3111 i_crdr varchar2(1);
3112 var_pos pls_integer := 0;
3113 i_value varchar2(2000);
3114 i_sign varchar2(1);
3115 i_numofdec varchar2(100);
3116 numofdec number;
3117 p_numofdec number := 0;
3118 v_numofdec number :=0;
3119 i_uom_quant number :=1;
3120 i_uom_value varchar2(2000) := '1';
3121 i_uomnumofdec varchar2(100) := '0';
3122 i_uomsign varchar2(1) :='+';
3123 oag_operamt_tbl oag_oamt_tbl;
3124
3125 begin
3126 if (l_procedureEnabled) then
3127 ecx_debug.push(i_method_name);
3128 end if;
3129 if(l_statementEnabled) then
3133 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
3130 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
3131 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
3132 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
3134 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
3135 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
3136 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
3137 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
3138 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
3139 ecx_debug.log(l_statement,'i_opr2_level',i_opr2_level,i_method_name);
3140 ecx_debug.log(l_statement,'i_opr2_name',i_opr2_name,i_method_name);
3141 ecx_debug.log(l_statement,'i_opr2_pos',i_opr2_pos,i_method_name);
3142 ecx_debug.log(l_statement,'i_opr2_direction',i_opr2_direction,i_method_name);
3143 ecx_debug.log(l_statement,'i_opr2_constant',i_opr2_constant,i_method_name);
3144 ecx_debug.log(l_statement,'i_opr3_level',i_opr3_level,i_method_name);
3145 ecx_debug.log(l_statement,'i_opr3_name',i_opr3_name,i_method_name);
3146 ecx_debug.log(l_statement,'i_opr3_pos',i_opr3_pos,i_method_name);
3147 ecx_debug.log(l_statement,'i_opr3_direction',i_opr3_direction,i_method_name);
3148 ecx_debug.log(l_statement,'i_opr3_constant',i_opr3_constant,i_method_name);
3149 end if;
3150
3151 get_var_attr(i_variable_level, i_variable_name,
3152 i_variable_direction,i_variable_pos,
3153 var_value, stack_var, stack_pos);
3154
3155 i_opr1_value := i_opr1_constant;
3156 if i_opr1_value is null
3157 then
3158 get_var_attr(i_opr1_level, i_opr1_name,
3159 i_opr1_direction,i_opr1_pos,
3160 i_opr1_value, opr1_stack,opr1_stack_pos);
3161 end if;
3162
3163 if i_opr1_value = 'NULL'
3164 then
3165 i_opr1_value :=null;
3166 end if;
3167 i_amount := to_number(i_opr1_value);
3168
3169 split_number(i_amount, i_value, i_sign, i_numofdec);
3170
3171 i_opr2_value := i_opr2_constant;
3172 if i_opr2_value is null
3173 then
3174 if i_opr2_level is not null
3175 then
3176 get_var_attr(i_opr2_level, i_opr2_name,
3177 i_opr2_direction,i_opr2_pos,
3178 i_opr2_value, opr2_stack, opr2_stack_pos);
3179 end if;
3180 end if;
3181 if i_opr2_value = 'NULL'
3182 then
3183 i_opr2_value :=null;
3184 end if;
3185
3186 i_opr3_value := i_opr3_constant;
3187 if i_opr3_value is null
3188 then
3189 if i_opr3_level is not null
3190 then
3191 get_var_attr(i_opr3_level, i_opr3_name,
3192 i_opr3_direction,i_opr3_pos,
3193 i_opr3_value, opr3_stack, opr3_stack_pos);
3194 end if;
3195 end if;
3196 if i_opr3_value = 'NULL'
3197 then
3198 i_opr3_value :=null;
3199 end if;
3200
3201 if (i_uom_quant <> 1) then
3202 split_number(i_uom_quant, i_uom_value,i_uomsign, i_uomnumofdec);
3203 end if;
3204
3205 i_crdr := substrb(i_opr3_value,1,1);
3206
3207 if (( i_crdr is null ) OR (i_crdr = ' '))
3208 then
3209 if ( i_sign = '+' )
3210 then
3211 i_crdr := 'D';
3212 else
3213 i_crdr := 'C';
3214 end if;
3215 end if;
3216
3217 oag_operamt_tbl(1) := i_value;
3218 if (i_value is not null) then
3219 oag_operamt_tbl(2) := i_numofdec;
3220 oag_operamt_tbl(3) := i_sign;
3221 oag_operamt_tbl(4) := i_opr2_value; --currency code
3222 oag_operamt_tbl(5) := i_crdr;
3223 end if;
3224
3225 var_pos := i_variable_pos+1;
3226
3227 if(l_statementEnabled) then
3228 ecx_debug.log(l_statement,'name',ecx_utils.g_target(var_pos).attribute_name,
3229 i_method_name);
3230 end if;
3231 if ecx_utils.g_target(var_pos).attribute_name = 'qualifier' then
3232 var_pos := var_pos + 1;
3233 end if;
3234
3235 if ecx_utils.g_target(var_pos).attribute_name = 'type' then
3236 var_pos := var_pos + 1;
3237 end if;
3238 if ecx_utils.g_target(var_pos).attribute_name = 'index' then
3239 var_pos := var_pos +1;
3240 end if;
3241
3242 for i in 1..oag_operamt_tbl.COUNT loop
3243 assign_value(stack_var, stack_pos, var_pos,i_variable_direction, oag_operamt_tbl(i));
3244 var_pos := var_pos +1;
3245 end loop;
3246
3247 if (l_procedureEnabled) then
3248 ecx_debug.pop(i_method_name);
3249 end if;
3250 EXCEPTION
3251 WHEN ecx_utils.PROGRAM_EXIT then
3252 if (l_procedureEnabled) then
3253 ecx_debug.pop(i_method_name);
3254 end if;
3255 raise;
3256
3257 WHEN OTHERS THEN
3258 if(l_unexpectedEnabled) then
3259 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_TO_OAG_AMT');
3260 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3261 end if;
3262 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_TO_OAG_AMT');
3263 if (l_procedureEnabled) then
3264 ecx_debug.pop(i_method_name);
3268
3265 end if;
3266 raise ecx_utils.PROGRAM_EXIT;
3267 end convert_to_oag_amt;
3269
3270 procedure convert_to_oag_quantity(
3271 i_variable_level IN pls_integer,
3272 i_variable_name IN varchar2,
3273 i_variable_pos IN pls_integer,
3274 i_variable_direction IN varchar2,
3275 i_opr1_level IN pls_integer,
3276 i_opr1_name IN Varchar2,
3277 i_opr1_pos IN pls_integer,
3278 i_opr1_direction IN Varchar2,
3279 i_opr1_constant IN varchar2,
3280 i_opr2_level IN pls_integer,
3281 i_opr2_name IN Varchar2,
3282 i_opr2_pos IN pls_integer,
3283 i_opr2_direction IN Varchar2,
3284 i_opr2_constant IN varchar2
3285 ) is
3286 i_method_name varchar2(2000) := 'ecx_actions.convert_to_oag_quantity';
3287 var_value varchar2(2000);
3288 stack_var boolean := FALSE;
3289 stack_pos pls_integer;
3290
3291 opr1_stack Boolean := FALSE;
3292 opr1_stack_pos pls_integer;
3293 i_opr1_value varchar2(2000);
3294 i_amount NUMBER;
3295
3296 /** For Opr2 **/
3297 opr2_stack Boolean := FALSE;
3298 opr2_stack_pos pls_integer;
3299 i_opr2_value Varchar2(2000);
3300 var_pos pls_integer;
3301 i_uom_quant number;
3302 i_uom_value varchar2(2000);
3303 i_uomnumofdec varchar2(100);
3304 i_uomsign varchar2(1);
3305
3306 TYPE oag_qt_tbl is TABLE OF varchar2(2000) index by BINARY_INTEGER;
3307 oag_quant_tbl oag_qt_tbl;
3308
3309 begin
3310 if (l_procedureEnabled) then
3311 ecx_debug.push(i_method_name);
3312 end if;
3313 if(l_statementEnabled) then
3314 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
3315 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
3316 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
3317 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
3318 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
3319 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
3320 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
3321 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
3322 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
3323 ecx_debug.log(l_statement,'i_opr2_level',i_opr2_level,i_method_name);
3324 ecx_debug.log(l_statement,'i_opr2_name',i_opr2_name,i_method_name);
3325 ecx_debug.log(l_statement,'i_opr2_pos',i_opr2_pos,i_method_name);
3326 ecx_debug.log(l_statement,'i_opr2_direction',i_opr2_direction,i_method_name);
3327 ecx_debug.log(l_statement,'i_opr2_constant',i_opr2_constant,i_method_name);
3328 end if;
3329
3330 get_var_attr (i_variable_level, i_variable_name,
3331 i_variable_direction,i_variable_pos,var_value, stack_var, stack_pos);
3332
3333 i_opr1_value := i_opr1_constant;
3334 if i_opr1_value is null
3335 then
3336 get_var_attr(i_opr1_level, i_opr1_name,
3337 i_opr1_direction,i_opr1_pos,
3338 i_opr1_value, opr1_stack, opr1_stack_pos);
3339 end if;
3340 if i_opr1_value = 'NULL'
3341 then
3342 i_opr1_value :=null;
3343 end if;
3344
3345 i_uom_quant := to_number(i_opr1_value);
3346
3347 if i_uom_quant is NOT null then
3348 split_number(i_uom_quant, i_uom_value,i_uomsign, i_uomnumofdec);
3349 end if;
3350
3351 i_opr2_value := i_opr2_constant;
3352 if i_opr2_value is null
3353 then
3354 if i_opr2_level is not null
3355 then
3356 get_var_attr(i_opr2_level, i_opr2_name,
3357 i_opr2_direction,i_opr2_pos,
3358 i_opr2_value, opr2_stack, opr2_stack_pos);
3359 end if;
3360 end if;
3361 if i_opr2_value = 'NULL'
3362 then
3363 i_opr2_value :=null;
3364 end if;
3365
3366 var_pos := i_variable_pos +2;
3367 oag_quant_tbl(1) := i_uom_value;
3368
3369 if (i_uom_value is not null) then
3370 oag_quant_tbl(2) := i_uomnumofdec;
3371 oag_quant_tbl(3) := i_uomsign;
3372 oag_quant_tbl(4) := i_opr2_value;
3373 end if;
3374
3375 for i in 1..oag_quant_tbl.COUNT loop
3376 assign_value(stack_var, stack_pos, var_pos,i_variable_direction,
3377 oag_quant_tbl(i));
3378 var_pos := var_pos +1;
3379 end loop;
3380
3381 if (l_procedureEnabled) then
3382 ecx_debug.pop(i_method_name);
3383 end if;
3384
3385 EXCEPTION
3386 WHEN ecx_utils.PROGRAM_EXIT then
3387 if (l_procedureEnabled) then
3388 ecx_debug.pop(i_method_name);
3389 end if;
3390 raise;
3391
3392 WHEN OTHERS THEN
3393 if(l_unexpectedEnabled) then
3394 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_TO_OAG_QUANTITY');
3395 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3396 end if;
3397 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_TO_OAG_QUANTITY');
3398 if (l_procedureEnabled) then
3402 end convert_to_oag_quantity;
3399 ecx_debug.pop(i_method_name);
3400 end if;
3401 raise ecx_utils.PROGRAM_EXIT;
3403
3404
3405 procedure combine_number (
3406 p_number out NOCOPY number,
3407 p_value in varchar2,
3408 p_sign in varchar2,
3409 p_numofdec in varchar2) IS
3410
3411 i_method_name varchar2(2000) := 'ecx_actions.combine_number';
3412
3413 begin
3414
3415 if (to_number(ltrim(rtrim(p_numofdec))) > 0) then
3416 p_number := to_number(ltrim(rtrim(p_value)))/power(10,to_number(ltrim(rtrim(p_numofdec))));
3417 else
3418 p_number := to_number(ltrim(rtrim(p_value)));
3419 end if;
3420
3421 if (ltrim(rtrim(p_sign)) = '-') then
3422 p_number := -1 * p_number;
3423 end if;
3424
3425 EXCEPTION
3426 WHEN OTHERS THEN
3427 if(l_unexpectedEnabled) then
3428 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.COMBINE_NUMBER');
3429 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3430 end if;
3431 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.COMBINE_NUMBER');
3432 if (l_procedureEnabled) then
3433 ecx_debug.pop(i_method_name);
3434 end if;
3435 raise ecx_utils.PROGRAM_EXIT;
3436 end combine_number;
3437
3438
3439 /**
3440 Returns the date in the server timezone
3441 **/
3442 function get_converted_date(
3443 i_year IN varchar2,
3444 i_month IN varchar2,
3445 i_day IN varchar2,
3446 i_hour IN varchar2,
3447 i_minute IN varchar2,
3448 i_second IN varchar2,
3449 i_timezone IN varchar2
3450 ) return date
3451 is
3452
3453 i_method_name varchar2(2000) := 'ecx_actions.get_converted_date';
3454 v_datetime varchar2(500);
3455 x_date Date := null;
3456 i_server_offset number;
3457 i_offset_diff number;
3458 i_timezone_hours number;
3459 incomplete_date exception;
3460
3461 i_string varchar2(2000);
3462
3463 begin
3464 if (l_procedureEnabled) then
3465 ecx_debug.push(i_method_name);
3466 end if;
3467
3468 -- Combining the values obtained to a datetime format specified in the next line.
3469 if ( (i_year is not null) and (i_month is not null) and (i_day is not null) and
3470 (i_hour is not null) and (i_minute is not null) and (i_second is not null) ) then
3471 v_datetime := i_year || i_month || i_day || ' '||i_hour || i_minute || i_second;
3472
3473 elsif ( (i_year is null) or (i_month is null) or (i_day is null) or (i_hour is null) or
3474 (i_minute is null) or (i_second is null) ) then
3475 v_datetime := i_year || i_month || i_day || ' '||i_hour || i_minute || i_second;
3476
3477 if nvl(v_datetime,' ') = ' ' then
3478 v_datetime := null;
3479 else
3480 raise incomplete_date;
3481 end if;
3482 end if;
3483
3484 if (v_datetime is not null) then
3485 x_date := to_date(v_datetime,'YYYYMMDD HH24MISS');
3486
3487 if (ecx_actions.g_server_timezone is null) then
3488 --- Check for the Installation Type ( Standalone or Embedded );
3489 if (ecx_utils.g_install_mode is null) then
3490 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
3491 end if;
3492
3493 if ecx_utils.g_install_mode = 'EMBEDDED'
3494 then
3495 i_string := 'begin
3496 fnd_profile.get('||'''ECX_SERVER_TIMEZONE'''||',ecx_actions.g_server_timezone);
3497 end;';
3498 execute immediate i_string ;
3499 else
3500 ecx_actions.g_server_timezone:= wf_core.translate('ECX_SERVER_TIMEZONE');
3501 end if;
3502 end if;
3503
3504 -- if profile option is not set assume gmt
3505 if (ecx_actions.g_server_timezone is null) then
3506 ecx_actions.g_server_timezone := 'GMT';
3507 end if;
3508
3509 -- get the DB server offset from the Java API
3510 i_server_offset := getTimeZoneOffset(to_number(i_year), to_number(i_month), to_number(i_day),
3511 to_number(i_hour), to_number(i_minute), to_number(i_second),
3512 ecx_actions.g_server_timezone);
3513
3514 -- get the time in hours from the input xml
3515 i_timezone_hours := to_number(substr(i_timezone, 1, length(i_timezone) - 2)) +
3516 (to_number(substr(i_timezone, length(i_timezone) - 1)) / 60);
3517
3518 -- get the offset difference
3519 i_offset_diff := i_server_offset - i_timezone_hours;
3520
3521 if(l_statementEnabled) then
3522 ecx_debug.log(l_statement, 'g_server_timezone', ecx_actions.g_server_timezone,i_method_name);
3523 ecx_debug.log(l_statement, 'i_server_offset', i_server_offset,i_method_name);
3524 ecx_debug.log(l_statement, 'i_timezone_hours', i_timezone_hours,i_method_name);
3525 ecx_debug.log(l_statement, 'i_offset_diff', i_offset_diff,i_method_name);
3526 end if;
3527
3528 x_date := x_date + (i_offset_diff / 24);
3529 end if;
3530
3531 if (l_procedureEnabled) then
3532 ecx_debug.pop(i_method_name);
3536 exception
3533 end if;
3534
3535 return (x_date);
3537 WHEN incomplete_date THEN
3538 ecx_debug.setErrorInfo(1,20,'ECX_INCOMPLETE_OAG_DATE',
3539 'p_datetime',v_datetime);
3540 if(l_unexpectedEnabled) then
3541 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
3542 'PROGRESS_LEVEL', 'ECX_ACTIONS.GET_CONVERTED_DATE');
3543 ecx_debug.log(l_unexpected,'ECX','ECX_INCOMPLETE_OAG_DATE',i_method_name,'p_datetime',
3544 v_datetime);
3545 end if;
3546 if (l_procedureEnabled) then
3547 ecx_debug.pop(i_method_name);
3548 end if;
3549 raise ecx_utils.program_exit;
3550
3551 WHEN ecx_utils.PROGRAM_EXIT THEN
3552 if (l_procedureEnabled) then
3553 ecx_debug.pop(i_method_name);
3554 end if;
3555 raise;
3556
3557 WHEN OTHERS THEN
3558 if(l_unexpectedEnabled) then
3559 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.GET_CONVERTED_DATE');
3560 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3561 end if;
3562 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_CONVERTED_DATE');
3563 if (l_procedureEnabled) then
3564 ecx_debug.pop(i_method_name);
3565 end if;
3566 raise ecx_utils.PROGRAM_EXIT;
3567 end get_converted_date;
3568
3569
3570 procedure convert_from_oag_date(
3571 i_variable_level IN pls_integer,
3572 i_variable_name IN varchar2,
3573 i_variable_pos IN pls_integer,
3574 i_variable_direction IN varchar2,
3575 i_opr1_level IN pls_integer,
3576 i_opr1_name IN Varchar2,
3577 i_opr1_pos IN pls_integer,
3578 i_opr1_direction IN Varchar2,
3579 i_opr1_constant IN varchar2
3580 ) is
3581
3582 i_method_name varchar2(2000) := 'ecx_actions.convert_from_oag_date';
3583
3584 i_year varchar2(4);
3585 i_month varchar2(2);
3586 i_day varchar2(2);
3587 i_hour varchar2(2);
3588 i_minute varchar2(2);
3589 i_second varchar2(2);
3590 i_subsecond varchar2(4);
3591 i_timezone varchar2(5);
3592 var_pos pls_integer;
3593 stack_pos pls_integer := null;
3594 stack_var Boolean := FALSE;
3595
3596 i_variable_value varchar2(2000);
3597
3598 /** For Opr1 **/
3599 opr1_stack Boolean := FALSE;
3600 opr1_stack_pos pls_integer;
3601 i_opr1_value varchar2(2000);
3602 v_datetime varchar2(25);
3603 x_date date;
3604
3605 begin
3606 if (l_procedureEnabled) then
3607 ecx_debug.push(i_method_name);
3608 end if;
3609 if(l_statementEnabled) then
3610 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
3611 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
3612 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
3613 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
3614 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
3615 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
3616 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
3617 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
3618 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
3619 end if;
3620 -- The variable_name, level and variable_pos passed in should be that of "DATETIME"
3621 -- The interface column names should be named YEAR, MONTH, DAY etc.
3622 -- Calculate position
3623
3624 var_pos := i_opr1_pos+1;
3625
3626 if i_opr1_direction = 'S'
3627 then
3628 while ecx_utils.g_source(var_pos).attribute_name <> 'YEAR'
3629 loop
3630 if(l_statementEnabled) then
3631 ecx_debug.log(l_statement,'name',ecx_utils.g_source(var_pos).attribute_name,i_method_name);
3632 end if;
3633 var_pos := var_pos + 1;
3634 end loop;
3635 else
3636 while ecx_utils.g_target(var_pos).attribute_name <> 'YEAR'
3637 loop
3638 if(l_statementEnabled) then
3639 ecx_debug.log(l_statement,'name',ecx_utils.g_target(var_pos).attribute_name,i_method_name);
3640 end if;
3641 var_pos := var_pos + 1;
3642 end loop;
3643 end if;
3644
3645
3646 -- Using positional dependence from this point to get the values.
3647
3648 get_var_attr(i_opr1_level, 'YEAR',i_opr1_direction,var_pos,i_year, opr1_stack, opr1_stack_pos);
3649 get_var_attr(i_opr1_level, 'MONTH',i_opr1_direction,var_pos+1,i_month, opr1_stack, opr1_stack_pos);
3650 get_var_attr(i_opr1_level, 'DAY',i_opr1_direction,var_pos+2,i_day, opr1_stack, opr1_stack_pos);
3651 get_var_attr(i_opr1_level, 'HOUR',i_opr1_direction,var_pos+3,i_hour, opr1_stack, opr1_stack_pos);
3652 get_var_attr(i_opr1_level, 'MINUTE',i_opr1_direction,var_pos+4,i_minute, opr1_stack, opr1_stack_pos);
3653 get_var_attr(i_opr1_level, 'SECOND',i_opr1_direction,var_pos+5,i_second, opr1_stack, opr1_stack_pos);
3654 get_var_attr(i_opr1_level, 'SUBSECOND',i_opr1_direction,var_pos+6,i_subsecond, opr1_stack, opr1_stack_pos);
3655 get_var_attr(i_opr1_level, 'TIMEZONE',i_opr1_direction,var_pos+7,i_timezone, opr1_stack, opr1_stack_pos);
3659 ecx_debug.log(l_statement,'i_year',i_year,i_method_name);
3656
3657
3658 if(l_statementEnabled) then
3660 ecx_debug.log(l_statement,'i_month',i_month,i_method_name);
3661 ecx_debug.log(l_statement,'i_day',i_day,i_method_name);
3662 ecx_debug.log(l_statement,'i_hour',i_hour,i_method_name);
3663 ecx_debug.log(l_statement,'i_minute',i_minute,i_method_name);
3664 ecx_debug.log(l_statement,'i_second',i_second,i_method_name);
3665 ecx_debug.log(l_statement,'i_timezone',i_timezone,i_method_name);
3666 end if;
3667 -- convert the date to the database timezone
3668 x_date := get_converted_date(i_year, i_month, i_day, i_hour,
3669 i_minute, i_second, i_timezone);
3670
3671 if (x_date is not null) then
3672 v_datetime := to_char(x_date, 'YYYYMMDD HH24MISS');
3673 end if;
3674
3675 if(l_statementEnabled) then
3676 ecx_debug.log(l_statement,'v_datetime', v_datetime,i_method_name);
3677 end if;
3678
3679 -- Assigning the date value to the DATETIME field.
3680 get_var_attr(i_variable_level, i_variable_name,i_variable_direction,
3681 i_variable_pos,i_variable_value, stack_var, stack_pos);
3682
3683 assign_value(stack_var, stack_pos, i_variable_pos,i_variable_direction, ltrim(rtrim(v_datetime)));
3684
3685 if (l_procedureEnabled) then
3686 ecx_debug.pop(i_method_name);
3687 end if;
3688
3689 EXCEPTION
3690 WHEN ecx_utils.PROGRAM_EXIT THEN
3691 if (l_procedureEnabled) then
3692 ecx_debug.pop(i_method_name);
3693 end if;
3694 raise;
3695
3696 WHEN OTHERS THEN
3697 if(l_unexpectedEnabled) then
3698 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_FROM_OAG_DATE');
3699 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3700 end if;
3701 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_FROM_OAG_DATE');
3702 if (l_procedureEnabled) then
3703 ecx_debug.pop(i_method_name);
3704 end if;
3705 raise ecx_utils.PROGRAM_EXIT;
3706 end convert_from_oag_date;
3707
3708
3709 procedure convert_from_oag_operamt(
3710 i_variable_level IN pls_integer,
3711 i_variable_name IN varchar2,
3712 i_variable_pos IN pls_integer,
3713 i_variable_direction IN varchar2,
3714 i_opr1_level IN pls_integer,
3715 i_opr1_name IN Varchar2,
3716 i_opr1_pos IN pls_integer,
3717 i_opr1_direction IN Varchar2,
3718 i_opr1_constant IN varchar2,
3719 i_opr2_level IN pls_integer,
3720 i_opr2_name IN Varchar2,
3721 i_opr2_pos IN pls_integer,
3722 i_opr2_direction IN Varchar2,
3723 i_opr2_constant IN varchar2,
3724 i_opr3_level IN pls_integer,
3725 i_opr3_name IN Varchar2,
3726 i_opr3_pos IN pls_integer,
3727 i_opr3_direction IN Varchar2,
3728 i_opr3_constant IN varchar2
3729 ) IS
3730
3731 i_method_name varchar2(2000) := 'ecx_actions.convert_from_oag_operamt';
3732
3733 i_number number;
3734 i_value varchar2(250);
3735 i_numofdec varchar2(100);
3736 i_sign varchar2(1);
3737 i_currency varchar2(5);
3738 i_uomvalue varchar2(30);
3739 i_uomnumdec varchar2(100);
3740 i_uom varchar2(30);
3741
3742 stack_var boolean := FALSE;
3743 stack_pos pls_integer := null;
3744
3745 i_variable_value varchar2(2000);
3746
3747 /** For Opr1 **/
3748 opr1_stack Boolean := FALSE;
3749 opr1_stack_pos pls_integer;
3750 i_opr1_value varchar2(2000);
3751
3752 /** For Opr2 **/
3753 opr2_stack Boolean := FALSE;
3754 opr2_stack_pos pls_integer;
3755 i_opr2_value Varchar2(2000);
3756
3757 /** For Opr3 **/
3758 opr3_stack Boolean := FALSE;
3759 opr3_stack_pos pls_integer;
3760 i_opr3_value Varchar2(2000);
3761
3762 begin
3763
3764 if (l_procedureEnabled) then
3765 ecx_debug.push(i_method_name);
3766 end if;
3767 if(l_statementEnabled) then
3768 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
3769 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
3770 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
3771 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
3772 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
3773 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
3774 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
3775 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
3776 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
3777 ecx_debug.log(l_statement,'i_opr2_level',i_opr2_level,i_method_name);
3778 ecx_debug.log(l_statement,'i_opr2_name',i_opr2_name,i_method_name);
3779 ecx_debug.log(l_statement,'i_opr2_pos',i_opr2_pos,i_method_name);
3780 ecx_debug.log(l_statement,'i_opr2_direction',i_opr2_direction,i_method_name);
3781 ecx_debug.log(l_statement,'i_opr2_constant',i_opr2_constant,i_method_name);
3785 ecx_debug.log(l_statement,'i_opr3_direction',i_opr3_direction,i_method_name);
3782 ecx_debug.log(l_statement,'i_opr3_level',i_opr3_level,i_method_name);
3783 ecx_debug.log(l_statement,'i_opr3_name',i_opr3_name,i_method_name);
3784 ecx_debug.log(l_statement,'i_opr3_pos',i_opr3_pos,i_method_name);
3786 ecx_debug.log(l_statement,'i_opr3_constant',i_opr3_constant,i_method_name);
3787 end if;
3788
3789 -- Variable_name, level, pos is OPERAMT
3790 -- Previous variable, name, pos is CURRENCY
3791 -- Next Variable, name pos is UOM
3792
3793 -- Adding 3 to i_variable_pos to skip the attributes of OPERAMT
3794
3795 get_var_attr(i_opr1_level, 'VALUE',i_opr1_direction,i_opr1_pos +3,i_value, opr1_stack, opr1_stack_pos);
3796 get_var_attr(i_opr1_level, 'NUMOFDEC',i_opr1_direction,i_opr1_pos +4,i_numofdec, opr1_stack, opr1_stack_pos);
3797 get_var_attr(i_opr1_level, 'SIGN',i_opr1_direction,i_opr1_pos +5,i_sign, opr1_stack, opr1_stack_pos);
3798 get_var_attr(i_opr1_level, 'CURRENCY',i_opr1_direction,i_opr1_pos +6,i_currency, opr1_stack, opr1_stack_pos);
3799 get_var_attr(i_opr1_level, 'UOMVALUE',i_opr1_direction,i_opr1_pos +7,i_uomvalue, opr1_stack, opr1_stack_pos);
3800 get_var_attr(i_opr1_level, 'UOMNUMDEC',i_opr1_direction,i_opr1_pos +8,i_uomnumdec, opr1_stack, opr1_stack_pos);
3801 get_var_attr(i_opr1_level, 'UOM',i_opr1_direction,i_opr1_pos +9,i_uom, opr1_stack, opr1_stack_pos);
3802
3803 if(l_statementEnabled) then
3804 ecx_debug.log(l_statement,'i_value',i_value,i_method_name);
3805 ecx_debug.log(l_statement,'i_numofdec',i_numofdec,i_method_name);
3806 ecx_debug.log(l_statement,'i_sign',i_sign,i_method_name);
3807 ecx_debug.log(l_statement,'i_currency',i_currency,i_method_name);
3808 ecx_debug.log(l_statement,'i_uomvalue',i_uomvalue,i_method_name);
3809 ecx_debug.log(l_statement,'i_uomnumdec',i_uomnumdec,i_method_name);
3810 ecx_debug.log(l_statement,'i_uom',i_uom,i_method_name);
3811 end if;
3812
3813 if i_value is not null then
3814 combine_number(i_number, i_value, i_sign, i_numofdec);
3815 end if;
3816 if(l_statementEnabled) then
3817 ecx_debug.log(l_statement,'i_number',i_number);
3818 end if;
3819 -- Assign the amount obtained to the OPERAMT variable
3820 get_var_attr(i_variable_level, i_variable_name,i_variable_direction,i_variable_pos,
3821 i_variable_value, stack_var, stack_pos);
3822 assign_value(stack_var, stack_pos,i_variable_pos,i_variable_direction, i_number);
3823
3824 /** This field is optional.Target Currency **/
3825 if (i_opr2_level >= 0 )
3826 then
3827 get_var_attr(i_opr2_level, i_opr2_name,i_opr2_direction,i_opr2_pos,i_opr2_value, opr2_stack, opr2_stack_pos);
3828 -- Assign the currency to the CURRENCY
3829 assign_value(opr2_stack, opr2_stack_pos, i_opr2_pos,i_opr2_direction, i_currency);
3830 end if;
3831
3832 /** This field is optional.UOM_CODE **/
3833 if ( i_opr3_level >=0 )
3834 then
3835 get_var_attr(i_opr3_level, i_opr3_name,i_opr3_direction,i_opr3_pos,i_opr3_value, opr3_stack, opr3_stack_pos);
3836 -- Assign the uom code to the UOM_CODE
3837 assign_value(opr3_stack, opr3_stack_pos,i_opr3_pos,i_opr3_direction, i_uom);
3838 end if;
3839
3840 if (l_procedureEnabled) then
3841 ecx_debug.pop(i_method_name);
3842 end if;
3843 EXCEPTION
3844 WHEN ecx_utils.PROGRAM_EXIT then
3845 if (l_procedureEnabled) then
3846 ecx_debug.pop(i_method_name);
3847 end if;
3848 raise;
3849
3850 WHEN OTHERS THEN
3851 if(l_unexpectedEnabled) then
3852 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
3853 'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_FROM_OAG_OPERAMT');
3854 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
3855 end if;
3856 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_FROM_OAG_OPERAMT');
3857 if (l_procedureEnabled) then
3858 ecx_debug.pop(i_method_name);
3859 end if;
3860 raise ecx_utils.PROGRAM_EXIT;
3861 end convert_from_oag_operamt;
3862
3863 procedure convert_from_oag_amt(
3864 i_variable_level IN pls_integer,
3865 i_variable_name IN varchar2,
3866 i_variable_pos IN pls_integer,
3867 i_variable_direction IN varchar2,
3868 i_opr1_level IN pls_integer,
3869 i_opr1_name IN Varchar2,
3870 i_opr1_pos IN pls_integer,
3871 i_opr1_direction IN Varchar2,
3872 i_opr1_constant IN varchar2,
3873 i_opr2_level IN pls_integer,
3874 i_opr2_name IN Varchar2,
3875 i_opr2_pos IN pls_integer,
3876 i_opr2_direction IN Varchar2,
3877 i_opr2_constant IN varchar2,
3878 i_opr3_level IN pls_integer,
3879 i_opr3_name IN Varchar2,
3880 i_opr3_pos IN pls_integer,
3881 i_opr3_direction IN Varchar2,
3882 i_opr3_constant IN varchar2
3883 ) IS
3884
3885 i_method_name varchar2(2000) := 'ecx_actions.convert_from_oag_amt';
3886 i_number number;
3887 i_value varchar2(250);
3888 i_numofdec varchar2(100);
3889 i_sign varchar2(1);
3890 i_currency varchar2(5);
3891 i_uomvalue varchar2(30);
3892 i_uomnumdec varchar2(100);
3893 --i_uom varchar2(30);
3897 stack_pos pls_integer := null;
3894 i_crdr varchar2(500);
3895 var_pos number;
3896 stack_var boolean := FALSE;
3898 i_variable_value varchar2(2000);
3899
3900 /** For Opr1 **/
3901 opr1_stack Boolean := FALSE;
3902 opr1_stack_pos pls_integer;
3903 i_opr1_value varchar2(2000);
3904
3905 /** For Opr2 **/
3906 opr2_stack Boolean := FALSE;
3907 opr2_stack_pos pls_integer;
3908 i_opr2_value Varchar2(2000);
3909
3910 /** For Opr3 **/
3911 opr3_stack Boolean := FALSE;
3912 opr3_stack_pos pls_integer;
3913 i_opr3_value Varchar2(2000);
3914
3915 begin
3916 if (l_procedureEnabled) then
3917 ecx_debug.push(i_method_name);
3918 end if;
3919 if(l_statementEnabled) then
3920 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
3921 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
3922 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
3923 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
3924 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
3925 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
3926 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
3927 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
3928 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
3929 ecx_debug.log(l_statement,'i_opr2_level',i_opr2_level,i_method_name);
3930 ecx_debug.log(l_statement,'i_opr2_name',i_opr2_name,i_method_name);
3931 ecx_debug.log(l_statement,'i_opr2_pos',i_opr2_pos,i_method_name);
3932 ecx_debug.log(l_statement,'i_opr2_direction',i_opr2_direction,i_method_name);
3933 ecx_debug.log(l_statement,'i_opr2_constant',i_opr2_constant,i_method_name);
3934 ecx_debug.log(l_statement,'i_opr3_level',i_opr3_level,i_method_name);
3935 ecx_debug.log(l_statement,'i_opr3_name',i_opr3_name,i_method_name);
3936 ecx_debug.log(l_statement,'i_opr3_pos',i_opr3_pos,i_method_name);
3937 ecx_debug.log(l_statement,'i_opr3_direction',i_opr3_direction,i_method_name);
3938 ecx_debug.log(l_statement,'i_opr3_constant',i_opr3_constant,i_method_name);
3939 end if;
3940 -- Variable_name, level, pos is AMOUNT
3941 -- Previous variable, name, pos is CURRENCY
3942 -- Next Variable, name pos is CR/DR
3943
3944 -- Adding 4 to i_variable_pos to skip the attributes of OPERAMT
3945
3946 -- Calculate position
3947 var_pos := i_opr1_pos+1;
3948
3949 if i_opr1_direction = 'S'
3950 then
3951 while ecx_utils.g_source(var_pos).attribute_name <> 'VALUE'
3952 loop
3953 if(l_statementEnabled) then
3954 ecx_debug.log(l_statement,'name',ecx_utils.g_source(var_pos).attribute_name,i_method_name);
3955 end if;
3956 var_pos := var_pos + 1;
3957 end loop;
3958 else
3959 while ecx_utils.g_target(var_pos).attribute_name <> 'VALUE'
3960 loop
3961 if(l_statementEnabled) then
3962 ecx_debug.log(l_statement,'name',ecx_utils.g_target(var_pos).attribute_name,i_method_name);
3963 end if;
3964 var_pos := var_pos + 1;
3965 end loop;
3966 end if;
3967
3968 get_var_attr(i_opr1_level, 'VALUE',i_opr1_direction,var_pos ,i_value, opr1_stack, opr1_stack_pos);
3969 get_var_attr(i_opr1_level, 'NUMOFDEC',i_opr1_direction,var_pos+1,i_numofdec, opr1_stack, opr1_stack_pos);
3970 get_var_attr(i_opr1_level, 'SIGN',i_opr1_direction,var_pos+2,i_sign, opr1_stack, opr1_stack_pos);
3971 get_var_attr(i_opr1_level, 'CURRENCY',i_opr1_direction,var_pos +3,i_currency, opr1_stack, opr1_stack_pos);
3972 get_var_attr(i_opr1_level, 'CRDR',i_opr1_direction,var_pos +4,i_crdr, opr1_stack, opr1_stack_pos);
3973
3974 if(l_statementEnabled) then
3975 ecx_debug.log(l_statement,'i_value',i_value,i_method_name);
3976 ecx_debug.log(l_statement,'i_numofdec',i_numofdec,i_method_name);
3977 ecx_debug.log(l_statement,'i_sign',i_sign,i_method_name);
3978 ecx_debug.log(l_statement,'i_currency',i_currency,i_method_name);
3979 ecx_debug.log(l_statement,'i_crdr',i_crdr,i_method_name);
3980 end if;
3981
3982 if i_value is not null then
3983 combine_number(i_number, i_value, i_sign, i_numofdec);
3984 end if;
3985 if(l_statementEnabled) then
3986 ecx_debug.log(l_statement,'i_number',i_number,i_method_name);
3987 end if;
3988
3989 -- Assign the amount obtained to the OPERAMT variable
3990 get_var_attr(i_variable_level, i_variable_name,i_variable_direction,i_variable_pos ,i_variable_value, stack_var, stack_pos);
3991 assign_value(stack_var, stack_pos,i_variable_pos,i_variable_direction, i_number);
3992
3993 if ( i_opr2_level >=0 )
3994 then
3995 -- Assign the currency to the CURRENCY
3996 get_var_attr(i_opr2_level, i_opr2_name,i_opr2_direction,i_opr2_pos,i_opr2_value, opr2_stack, opr2_stack_pos);
3997 assign_value(opr2_stack, opr2_stack_pos, i_opr2_pos,i_opr2_direction, i_currency);
3998 end if;
3999
4000 -- Assign the cr/dr sign to the CRDR
4001 if ( i_crdr is null )
4002 then
4003 if ( i_sign = '+' )
4004 then
4005 i_crdr := 'D';
4006 else
4007 i_crdr := 'C';
4008 end if;
4009 end if;
4013 get_var_attr(i_opr3_level, i_opr3_name,i_opr3_direction,i_opr3_pos ,i_opr3_value, opr3_stack, opr3_stack_pos);
4010
4011 if ( i_opr3_level >=0 )
4012 then
4014 assign_value(opr3_stack, opr3_stack_pos,i_opr3_pos,i_opr3_direction, i_crdr);
4015 end if;
4016
4017 if (l_procedureEnabled) then
4018 ecx_debug.pop(i_method_name);
4019 end if;
4020 EXCEPTION
4021 WHEN ecx_utils.PROGRAM_EXIT then
4022 if (l_procedureEnabled) then
4023 ecx_debug.pop(i_method_name);
4024 end if;
4025 raise;
4026
4027 WHEN OTHERS THEN
4028 if(l_unexpectedEnabled) then
4029 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_FROM_OAG_AMT');
4030 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
4031 end if;
4032 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_FROM_OAG_AMT');
4033 if (l_procedureEnabled) then
4034 ecx_debug.pop(i_method_name);
4035 end if;
4036 raise ecx_utils.PROGRAM_EXIT;
4037 end convert_from_oag_amt;
4038
4039 procedure derive_address_id
4040 (
4041 i_variable_level IN pls_integer,
4042 i_variable_name IN varchar2,
4043 i_variable_pos IN pls_integer,
4044 i_variable_direction IN varchar2,
4045 i_opr1_level IN pls_integer,
4046 i_opr1_name IN Varchar2,
4047 i_opr1_pos IN pls_integer,
4048 i_opr1_direction IN Varchar2,
4049 i_opr1_constant IN varchar2,
4050 i_opr2_constant IN varchar2,
4051 i_opr3_level IN pls_integer,
4052 i_opr3_name IN Varchar2,
4053 i_opr3_pos IN pls_integer,
4054 i_opr3_direction IN Varchar2
4055 )
4056 is
4057
4058 i_method_name varchar2(2000) := 'ecx_actions.derive_address_id';
4059 i_variable_value varchar2(2000);
4060 i_value varchar2(2000);
4061 i_value3 varchar2(2000);
4062 i_info_type varchar2(2000);
4063 opr3_stack boolean;
4064 opr3_stack_pos pls_integer;
4065 var_stack boolean;
4066 var_stack_pos pls_integer;
4067 opr1_stack boolean;
4068 opr1_stack_pos pls_integer;
4069 retcode pls_integer;
4070 retmsg varchar2(400);
4071 p_entity_address_id pls_integer;
4072 p_org_id pls_integer;
4073 begin
4074 if (l_procedureEnabled) then
4075 ecx_debug.push(i_method_name);
4076 end if;
4077 i_value := i_opr1_constant;
4078 if i_value is null
4079 then
4080 get_var_attr(
4081 i_opr1_level,
4082 i_opr1_name,
4083 i_opr1_direction,
4084 i_opr1_pos ,
4085 i_value,
4086 opr1_stack,
4087 opr1_stack_pos
4088 );
4089 end if;
4090
4091 if i_value = 'NULL'
4092 then
4093 i_value := null;
4094 end if;
4095
4096 /** check for Info Type **/
4097 if i_opr2_constant = 0
4098 then
4099 i_info_type := 'CUSTOMER';
4100 elsif i_opr2_constant = 1
4101 then
4102 i_info_type := 'SUPPLIER';
4103 elsif i_opr2_constant = 2
4104 then
4105 i_info_type := 'LOCATION';
4106 elsif i_opr2_constant = 3
4107 then
4108 i_info_type := 'BANK';
4109 end if;
4110
4111 ecx_trading_partner_pvt.get_address_id
4112 (
4113 i_value,
4114 i_info_type,
4115 p_entity_address_id,
4116 p_org_id,
4117 retcode,
4118 retmsg
4119 );
4120
4121 /** Assign the value back to the Address Id **/
4122 if i_variable_level is not null
4123 then
4124 get_var_attr(
4125 i_variable_level,
4126 i_variable_name,
4127 i_variable_direction,
4128 i_variable_pos ,
4129 i_variable_value,
4130 var_stack,
4131 var_stack_pos
4132 );
4133 assign_value(var_stack, var_stack_pos,i_variable_pos,i_variable_direction, p_entity_address_id);
4134 end if;
4135
4136 /** Assign the value back to the p_org_id **/
4137 if i_opr3_level is not null
4138 then
4139 get_var_attr(
4140 i_opr3_level,
4141 i_opr3_name,
4142 i_opr3_direction,
4143 i_opr3_pos ,
4144 i_value3,
4145 opr3_stack,
4146 opr3_stack_pos
4147 );
4148 assign_value(opr3_stack, opr3_stack_pos,i_opr3_pos,i_opr3_direction, p_org_id);
4149 end if;
4150
4151 if retcode = 2
4152 then
4153 raise ecx_utils.program_exit;
4154 end if;
4155 if (l_procedureEnabled) then
4156 ecx_debug.pop(i_method_name);
4157 end if;
4158 EXCEPTION
4159 WHEN ecx_utils.PROGRAM_EXIT then
4160 if (l_procedureEnabled) then
4161 ecx_debug.pop(i_method_name);
4162 end if;
4163 raise;
4164
4165 WHEN OTHERS THEN
4166 if(l_unexpectedEnabled) then
4167 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.DERIVE_ADDRESS_ID');
4168 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
4169 end if;
4170 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.DERIVE_ADDRESS_ID');
4171 if (l_procedureEnabled) then
4172 ecx_debug.pop(i_method_name);
4176
4173 end if;
4174 raise ecx_utils.PROGRAM_EXIT;
4175 end derive_address_id;
4177 procedure convert_from_oag_quantity(
4178 i_variable_level IN pls_integer,
4179 i_variable_name IN varchar2,
4180 i_variable_pos IN pls_integer,
4181 i_variable_direction IN varchar2,
4182 i_opr1_level IN pls_integer,
4183 i_opr1_name IN Varchar2,
4184 i_opr1_pos IN pls_integer,
4185 i_opr1_direction IN Varchar2,
4186 i_opr1_constant IN varchar2,
4187 i_opr2_level IN pls_integer,
4188 i_opr2_name IN Varchar2,
4189 i_opr2_pos IN pls_integer,
4190 i_opr2_direction IN Varchar2,
4191 i_opr2_constant IN varchar2
4192 )IS
4193
4194
4195 i_method_name varchar2(2000) := 'ecx_actions.convert_from_oag_quantity';
4196
4197 var_value varchar2(2000);
4198 stack_var boolean := FALSE;
4199 stack_pos pls_integer := null;
4200 i_variable_value varchar2(2000);
4201
4202 /** For Opr1 **/
4203 opr1_stack Boolean := FALSE;
4204 opr1_stack_pos pls_integer;
4205 i_opr1_value varchar2(2000);
4206
4207 /** For Opr2 **/
4208 opr2_stack Boolean := FALSE;
4209 opr2_stack_pos pls_integer;
4210 i_opr2_value Varchar2(2000);
4211 i_number number;
4212 i_value varchar2(2000);
4213 i_numofdec varchar2(100);
4214 i_sign varchar2(1);
4215 i_uom varchar2(250);
4216 var_pos pls_integer;
4217
4218 begin
4219 if (l_procedureEnabled) then
4220 ecx_debug.push(i_method_name);
4221 end if;
4222 if(l_statementEnabled) then
4223 ecx_debug.log(l_statement,'i_variable_level',i_variable_level,i_method_name);
4224 ecx_debug.log(l_statement,'i_variable_name',i_variable_name,i_method_name);
4225 ecx_debug.log(l_statement,'i_variable_pos',i_variable_pos,i_method_name);
4226 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,i_method_name);
4227 ecx_debug.log(l_statement,'i_opr1_level',i_opr1_level,i_method_name);
4228 ecx_debug.log(l_statement,'i_opr1_name',i_opr1_name,i_method_name);
4229 ecx_debug.log(l_statement,'i_opr1_pos',i_opr1_pos,i_method_name);
4230 ecx_debug.log(l_statement,'i_opr1_direction',i_opr1_direction,i_method_name);
4231 ecx_debug.log(l_statement,'i_opr1_constant',i_opr1_constant,i_method_name);
4232 ecx_debug.log(l_statement,'i_opr2_level',i_opr2_level,i_method_name);
4233 ecx_debug.log(l_statement,'i_opr2_name',i_opr2_name,i_method_name);
4234 ecx_debug.log(l_statement,'i_opr2_pos',i_opr2_pos,i_method_name);
4235 ecx_debug.log(l_statement,'i_opr2_direction',i_opr2_direction,i_method_name);
4236 ecx_debug.log(l_statement,'i_opr2_constant',i_opr2_constant,i_method_name);
4237 end if;
4238 -- Variable_name, pos, level here correspond to QUANTITY
4239 -- Previuos variable_name, pos, level here is UOM CODE.
4240 -- Adding 2 to i_variable_pos to skip the attribute of QUANTITY
4241
4242 get_var_attr(i_opr1_level,'VALUE',i_opr1_direction,i_opr1_pos+2,i_value, opr1_stack, opr1_stack_pos);
4243 get_var_attr(i_opr1_level,'NUMOFDEC',i_opr1_direction,i_opr1_pos+3, i_numofdec, opr1_stack, opr1_stack_pos);
4244 get_var_attr(i_opr1_level,'SIGN',i_opr1_direction,i_opr1_pos+4,i_sign, opr1_stack, opr1_stack_pos);
4245 get_var_attr(i_opr1_level,'UOM',i_opr1_direction,i_opr1_pos+5,i_uom, opr1_stack, opr1_stack_pos);
4246
4247 if(l_statementEnabled) then
4248 ecx_debug.log(l_statement,'i_value',i_value,i_method_name);
4249 ecx_debug.log(l_statement,'i_sign',i_sign,i_method_name);
4250 ecx_debug.log(l_statement,'i_uom',i_uom,i_method_name);
4251 ecx_debug.log(l_statement,'i_numofdec',i_numofdec,i_method_name);
4252 end if;
4253
4254 if i_value is not null then
4255 combine_number(i_number, i_value, i_sign, i_numofdec);
4256 end if;
4257 if(l_statementEnabled) then
4258 ecx_debug.log(l_statement,'i_number',i_number,i_method_name);
4259 end if;
4260 -- Assign the quantity obtained to the field QUANTITY
4261 get_var_attr(i_variable_level, i_variable_name,i_variable_direction,i_variable_pos ,i_variable_value, stack_var, stack_pos);
4262 assign_value(stack_var, stack_pos,i_variable_pos,i_variable_direction, i_number);
4263
4264 if ( i_opr2_level >= 0 )
4265 then
4266 -- Assign the uom code to the fied UOM_CODE
4267 get_var_attr(i_opr2_level, i_opr2_name,i_opr2_direction,i_opr2_pos ,i_opr2_value, opr2_stack, opr2_stack_pos);
4268 assign_value(opr2_stack, opr2_stack_pos,i_opr2_pos,i_opr2_direction, i_uom);
4269 end if;
4270
4271 if (l_procedureEnabled) then
4272 ecx_debug.pop(i_method_name);
4273 end if;
4274 EXCEPTION
4275 WHEN ecx_utils.PROGRAM_EXIT then
4276 if (l_procedureEnabled) then
4277 ecx_debug.pop(i_method_name);
4278 end if;
4279 raise;
4280
4281 WHEN OTHERS THEN
4282 if(l_unexpectedEnabled) then
4283 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
4284 'PROGRESS_LEVEL','ECX_ACTIONS.CONVERT_FROM_OAG_QUANTITY');
4285 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
4286 end if;
4287 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_FROM_AG_QUANTITY');
4288 if (l_procedureEnabled) then
4292 end convert_from_oag_quantity;
4289 ecx_debug.pop(i_method_name);
4290 end if;
4291 raise ecx_utils.PROGRAM_EXIT;
4293
4294 procedure execute_math_functions
4295 (
4296 action in pls_integer,
4297 x_level in pls_integer,
4298 x_name in varchar2,
4299 x_pos in pls_integer,
4300 x_dir in varchar2,
4301 y_level in pls_integer,
4302 y_name in varchar2,
4303 y_pos in pls_integer,
4304 y_dir in varchar2,
4305 y_def in varchar2,
4306 z_level in pls_integer,
4307 z_name in varchar2,
4308 z_pos in pls_integer,
4309 z_dir in varchar2,
4310 z_def in varchar2
4311 )
4312 is
4313
4314 i_method_name varchar2(2000) := 'ecx_actions.execute_math_functions';
4315
4316 x varchar2(2000);
4317 y varchar2(2000);
4318 z varchar2(2000);
4319 x1 varchar2(2000);
4320
4321 stack_var boolean := false;
4322 stack_pos pls_integer;
4323 i_math_fun_type varchar2(20);
4324 y_number number;
4325 z_number number;
4326 begin
4327 if (l_procedureEnabled) then
4328 ecx_debug.push(i_method_name);
4329 end if;
4330 if(l_statementEnabled) then
4331 ecx_debug.log(l_statement,'action',action,i_method_name);
4332 ecx_debug.log(l_statement,'x_level',x_level,i_method_name);
4333 ecx_debug.log(l_statement,'x_name',x_name,i_method_name);
4334 ecx_debug.log(l_statement,'x_pos',x_pos,i_method_name);
4335 ecx_debug.log(l_statement,'x_dir',x_dir,i_method_name);
4336 ecx_debug.log(l_statement,'y_level',y_level,i_method_name);
4337 ecx_debug.log(l_statement,'y_name',y_name,i_method_name);
4338 ecx_debug.log(l_statement,'y_pos',y_pos,i_method_name);
4339 ecx_debug.log(l_statement,'y_dir',y_dir,i_method_name);
4340 ecx_debug.log(l_statement,'y_def',y_def,i_method_name);
4341 ecx_debug.log(l_statement,'z_level',z_level,i_method_name);
4342 ecx_debug.log(l_statement,'z_name',z_name,i_method_name);
4343 ecx_debug.log(l_statement,'z_pos',z_pos,i_method_name);
4344 ecx_debug.log(l_statement,'z_dir',z_dir,i_method_name);
4345 ecx_debug.log(l_statement,'z_def',z_def,i_method_name);
4346 end if;
4347 if action = 4000
4348 then
4349 i_math_fun_type := '+';
4350 elsif action = 4010
4351 then
4352 i_math_fun_type := '-';
4353 elsif action = 4020
4354 then
4355 i_math_fun_type := '*';
4356 elsif action = 4030
4357 then
4358 i_math_fun_type := '/';
4359 end if;
4360
4361 /** Find the values of y and z
4362 Look for Default First and than the variable Value
4363 **/
4364
4365 z := z_def;
4366 if z is null
4367 then
4368 get_var_attr (
4369 z_level,
4370 z_name,
4371 z_dir,
4372 z_pos,
4373 z,
4374 stack_var,
4375 stack_pos);
4376
4377 end if;
4378 if z = 'NULL'
4379 then
4380 z :=null;
4381 end if;
4382
4383 y := y_def;
4384 if y is null
4385 then
4386 get_var_attr (
4387 y_level,
4388 y_name,
4389 y_dir,
4390 y_pos,
4391 y,
4392 stack_var,
4393 stack_pos);
4394 end if;
4395 if y = 'NULL'
4396 then
4397 y :=null;
4398 end if;
4399
4400 /** Check for numbers here **/
4401 begin
4402 y_number := to_number(y);
4403 exception
4404 when others then
4405 if(l_unexpectedEnabled) then
4406 ecx_debug.log(l_unexpected,'Cannot convert to number',y,i_method_name);
4407 end if;
4408 ecx_debug.setErrorInfo(2,30,'ECX_CANNOT_CONVERT_TO_NUM',
4409 'p_value',y);
4410 raise ecx_utils.program_exit;
4411 end;
4412
4413 begin
4414 z_number := to_number(z);
4415 exception
4416 when others then
4417 if(l_unexpectedEnabled) then
4418 ecx_debug.log(l_unexpected,'Cannot convert to number the value',z,i_method_name);
4419 end if;
4420 ecx_debug.setErrorInfo(2,30,'ECX_CANNOT_CONVERT_TO_NUM',
4421 'p_value',z);
4422 raise ecx_utils.program_exit;
4423 end;
4424 x := ecx_conditions.math_functions ( i_math_fun_type, y_number, z_number);
4425
4426 get_var_attr (
4427 x_level,
4428 x_name,
4429 x_dir,
4430 x_pos,
4431 x1,
4432 stack_var,
4433 stack_pos);
4434
4435 -- Assign the value obtained for x
4436 assign_value (
4437 stack_var,
4438 stack_pos,
4439 x_pos,
4440 x_dir,
4441 x);
4442 if (l_procedureEnabled) then
4443 ecx_debug.pop(i_method_name);
4444 end if;
4445 EXCEPTION
4446 WHEN ecx_utils.PROGRAM_EXIT then
4447 if (l_procedureEnabled) then
4448 ecx_debug.pop(i_method_name);
4449 end if;
4450 raise;
4451 WHEN OTHERS THEN
4452 if(l_unexpectedEnabled) then
4453 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
4454 'PROGRESS_LEVEL','ECX_ACTIONS.EXECUTE_MATH_FUNCTIONS');
4455 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,
4456 'ERROR_MESSAGE',SQLERRM);
4457 end if;
4461 end if;
4458 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXECUTE_MATH_FUNCTIONS');
4459 if (l_procedureEnabled) then
4460 ecx_debug.pop(i_method_name);
4462 raise ecx_utils.PROGRAM_EXIT;
4463 end execute_math_functions;
4464
4465
4466 procedure transform_xml_with_xslt
4467 (
4468 p_default_value IN varchar2,
4469 p_opr1_level IN pls_integer,
4470 p_opr1_name IN Varchar2,
4471 p_opr1_pos IN pls_integer,
4472 p_opr1_direction IN Varchar2,
4473 p_opr1_constant IN varchar2
4474 ) is
4475
4476 l_filename Varchar2(500);
4477 l_opr1_stack boolean;
4478 l_opr1_stack_pos pls_integer;
4479
4480 begin
4481 if p_default_value is not null then
4482 l_filename := p_default_value;
4483 else
4484 l_filename := p_opr1_constant;
4485 if l_filename is null then
4486 get_var_attr(
4487 p_opr1_level,
4488 p_opr1_name,
4489 p_opr1_direction,
4490 p_opr1_pos ,
4491 l_filename,
4492 l_opr1_stack,
4493 l_opr1_stack_pos
4494 );
4495 end if;
4496 end if;
4497
4498 if (l_filename is null) or (l_filename = 'NULL')
4499 then
4500 return;
4501 end if;
4502
4503 transform_xml_with_xslt (l_filename);
4504
4505 exception
4506 when others then
4507 raise ecx_utils.program_exit;
4508 end transform_xml_with_xslt;
4509
4510
4511 procedure transform_xml_with_xslt
4512 (
4513 i_filename in varchar2,
4514 i_version in number,
4515 i_application_code in varchar2
4516 )
4517 is
4518 i_method_name varchar2(2000) := 'ecx_actions.transform_xml_with_xslt';
4519
4520 i_stylesheet xslprocessor.Stylesheet;
4521 i_processor xslprocessor.Processor;
4522 i_xmlDocFrag xmlDOM.DOMDocumentFragment;
4523 i_domDocFrag xmlDOM.DOMDocumentFragment;
4524 i_domNode xmlDOM.DOMNode;
4525 i_xslt_dir varchar2(200);
4526 i_fullpath varchar2(200);
4527 i_string varchar2(2000);
4528 l_xslt_payload clob;
4529 l_parser xmlparser.parser;
4530 l_xsl_doc xmldom.DOMDocument;
4531 i_doc xmlDOM.DOMDocument;
4532 i_doc_frag xmlDOM.DOMDocumentFragment;
4533 i_node_type pls_integer;
4534
4535 begin
4536 if (l_procedureEnabled) then
4537 ecx_debug.push(i_method_name);
4538 end if;
4539
4540 if (xmlDOM.isNull(ecx_utils.g_xmldoc)) then
4541 return;
4542 end if;
4543
4544 if(l_statementEnabled) then
4545 ecx_debug.log(l_statement,'i_filename',i_filename,i_method_name);
4546 end if;
4547
4548 if i_filename is null
4549 then
4550 return;
4551 end if;
4552
4553 -- check if the XSLT file is loaded in the DB
4554 begin
4555 select payload
4556 into l_xslt_payload
4557 from ecx_files
4558 where (i_version is null or version = i_version)
4559 and (i_application_code is null or application_code = i_application_code)
4560 and name = i_filename
4561 and type = 'XSLT';
4562 exception
4563 when no_data_found then
4564 null;
4565 when too_many_rows then
4566 -- currently we do not support version and application_code as
4567 -- input parameters for XSLT actions, so for now, this exception
4568 -- means that the API was invoked within the actions code. So, check
4569 -- the file system
4570 if(l_unexpectedEnabled) then
4571 ecx_debug.log(l_unexpected, SQLERRM,i_method_name);
4572 end if;
4573 l_xslt_payload := null;
4574 when others then
4575 ecx_debug.setErrorInfo(2,30,SQLERRM);
4576 raise ecx_utils.program_exit;
4577 end;
4578
4579 if (l_xslt_payload is null)
4580 then
4581 -- xslt file is not loaded in the DB table
4582 -- do the transformation assuming that it is on the file system
4583 if(l_statementEnabled) then
4584 ecx_debug.log(l_statement,'XSLT file not loaded in the DB. Checking the file system...',
4585 i_method_name);
4586 end if;
4587
4588 if (ecx_actions.g_xslt_dir is null) then
4589 --- Check for the Installation Type ( Standalone or Embedded );
4590 if (ecx_utils.g_install_mode is null) then
4591 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
4592 end if;
4593
4594 if ecx_utils.g_install_mode = 'EMBEDDED'
4595 then
4596 i_string := 'begin
4597 fnd_profile.get('||'''ECX_UTL_XSLT_DIR'''||',ecx_actions.g_xslt_dir);
4598 end;';
4599 execute immediate i_string ;
4600 else
4601 ecx_actions.g_xslt_dir:= wf_core.translate('ECX_UTL_XSLT_DIR');
4602 end if;
4603 end if;
4604
4605 i_fullpath := ecx_actions.g_xslt_dir||ecx_utils.getFileSeparator()||i_filename;
4606 if(l_statementEnabled) then
4607 ecx_debug.log(l_statement, 'XSLT Fullpath', i_fullpath,i_method_name);
4608 end if;
4609 l_parser := xmlparser.newParser;
4610 xmlparser.setPreservewhitespace(l_parser,true); -- bug:4953557
4611 xmlparser.parse(l_parser,i_fullpath);
4612 l_xsl_doc := xmlparser.getDocument(l_parser);
4616 if(l_statementEnabled) then
4613 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc,i_fullpath);
4614 else
4615 -- payload found in DB,
4617 ecx_debug.log(l_statement, 'Found XSLT file in the DB',i_method_name);
4618 end if;
4619 -- convert l_xslt_paylod from clob to DOMDocument
4620 l_parser := xmlparser.newParser;
4621 xmlparser.setPreservewhitespace(l_parser,true); -- bug:4953557
4622 xmlparser.parseCLOB(l_parser, l_xslt_payload);
4623 l_xsl_doc := xmlparser.getDocument(l_parser);
4624
4625 -- get the stylesheet
4626 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc, null);
4627 end if;
4628
4629 i_processor := xslprocessor.newProcessor;
4630
4631 -- get the type of the DOMNode
4632 i_node_type := xmlDOM.getNodeType(ecx_utils.g_xmldoc);
4633 if(l_statementEnabled) then
4634 ecx_debug.log(l_statement, 'i_node_type', i_node_type,i_method_name);
4635 end if;
4636
4637 if (i_node_type = xmlDOM.DOCUMENT_NODE)
4638 then
4639 if(l_statementEnabled) then
4640 ecx_debug.log(l_statement, 'Creating Document Object from DOM Node...',i_method_name);
4641 end if;
4642 i_doc := xmlDOM.makeDocument(ecx_utils.g_xmldoc);
4643 if(l_statementEnabled) then
4644 ecx_debug.log(l_statement, 'Before processing XSL',i_method_name);
4645 end if;
4646 i_xmlDocFrag := xslprocessor.processXSL(i_processor, i_stylesheet, i_doc);
4647
4648 elsif (i_node_type = xmlDOM.DOCUMENT_FRAGMENT_NODE)
4649 then
4650 if(l_statementEnabled) then
4651 ecx_debug.log(l_statement,'Creating Document fragment from DOM Node...',i_method_name);
4652 end if;
4653 i_doc_frag := xmlDOM.makeDocumentFragment(ecx_utils.g_xmldoc);
4654 if(l_statementEnabled) then
4655 ecx_debug.log(l_statement, 'Before processing XSL',i_method_name);
4656 end if;
4657 i_xmlDocFrag := xslprocessor.processXSL(i_processor, i_stylesheet, i_doc_frag);
4658 end if;
4659
4660 if(l_statementEnabled) then
4661 ecx_debug.log(l_statement,'XSL processed.Creating Node...',i_method_name);
4662 end if;
4663 i_domNode := xmlDOM.makeNode(i_xmlDocFrag);
4664 if(l_statementEnabled) then
4665 ecx_debug.log(l_statement, 'Node created.',i_method_name);
4666 end if;
4667 ecx_utils.g_xmldoc := i_domNode;
4668
4669 -- free all the used variables
4670
4671 xslprocessor.freeStylesheet(i_stylesheet);
4672 xslprocessor.freeProcessor(i_processor);
4673
4674 if (l_parser.id <> -1)
4675 then
4676 xmlParser.freeParser(l_parser);
4677 end if;
4678 if (not xmldom.isNull(l_xsl_doc))
4679 then
4680 xmldom.freeDocument(l_xsl_doc);
4681 end if;
4682 if (not xmldom.isNull(i_doc))
4683 then
4684 xmldom.freeDocument(i_doc);
4685 end if;
4686
4687 if (not xmldom.isNull(i_doc_frag))
4688 then
4689 xmldom.freeDocFrag(i_doc_frag);
4690 end if;
4691
4692 if (l_procedureEnabled) then
4693 ecx_debug.pop(i_method_name);
4694 end if;
4695 exception
4696 -- Put All DOM Parser Exceptions Here.
4697 when xmlDOM.INDEX_SIZE_ERR then
4698 ecx_debug.setErrorInfo(1,20,SQLERRM);
4699 if(l_unexpectedEnabled) then
4700 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4701 end if;
4702 -- free all the used variables
4703 if (l_parser.id <> -1)
4704 then
4705 xmlParser.freeParser(l_parser);
4706 end if;
4707 if (not xmldom.isNull(l_xsl_doc))
4708 then
4709 xmldom.freeDocument(l_xsl_doc);
4710 end if;
4711 if (l_procedureEnabled) then
4712 ecx_debug.pop(i_method_name);
4713 end if;
4714 raise ecx_utils.program_exit;
4715 when xmlDOM.DOMSTRING_SIZE_ERR then
4716 ecx_debug.setErrorInfo(1,20,SQLERRM);
4717 if(l_unexpectedEnabled) then
4718 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4719 end if;
4720 -- free all the used variables
4721 if (l_parser.id <> -1)
4722 then
4723 xmlParser.freeParser(l_parser);
4724 end if;
4725 if (not xmldom.isNull(l_xsl_doc))
4726 then
4727 xmldom.freeDocument(l_xsl_doc);
4728 end if;
4729 if (l_procedureEnabled) then
4730 ecx_debug.pop(i_method_name);
4731 end if;
4732 raise ecx_utils.program_exit;
4733 when xmlDOM.HIERARCHY_REQUEST_ERR then
4734 ecx_debug.setErrorInfo(1,20,SQLERRM);
4735 if(l_unexpectedEnabled) then
4736 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4737 end if;
4738 -- free all the used variables
4739 if (l_parser.id <> -1)
4740 then
4741 xmlParser.freeParser(l_parser);
4742 end if;
4743 if (not xmldom.isNull(l_xsl_doc))
4744 then
4745 xmldom.freeDocument(l_xsl_doc);
4746 end if;
4747 if (l_procedureEnabled) then
4748 ecx_debug.pop(i_method_name);
4749 end if;
4750 raise ecx_utils.program_exit;
4751 when xmlDOM.WRONG_DOCUMENT_ERR then
4752 ecx_debug.setErrorInfo(1,20,SQLERRM);
4753 if(l_unexpectedEnabled) then
4754 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4755 end if;
4756 -- free all the used variables
4757 if (l_parser.id <> -1)
4761 if (not xmldom.isNull(l_xsl_doc))
4758 then
4759 xmlParser.freeParser(l_parser);
4760 end if;
4762 then
4763 xmldom.freeDocument(l_xsl_doc);
4764 end if;
4765 if (l_procedureEnabled) then
4766 ecx_debug.pop(i_method_name);
4767 end if;
4768 raise ecx_utils.program_exit;
4769 when xmlDOM.INVALID_CHARACTER_ERR then
4770 ecx_debug.setErrorInfo(1,20,SQLERRM);
4771 if(l_unexpectedEnabled) then
4772 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4773 end if;
4774 -- free all the used variables
4775 if (l_parser.id <> -1)
4776 then
4777 xmlParser.freeParser(l_parser);
4778 end if;
4779 if (not xmldom.isNull(l_xsl_doc))
4780 then
4781 xmldom.freeDocument(l_xsl_doc);
4782 end if;
4783 if (l_procedureEnabled) then
4784 ecx_debug.pop(i_method_name);
4785 end if;
4786 raise ecx_utils.program_exit;
4787 when xmlDOM.NO_DATA_ALLOWED_ERR then
4788 ecx_debug.setErrorInfo(1,20,SQLERRM);
4789 if(l_unexpectedEnabled) then
4790 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4791 end if;
4792 -- free all the used variables
4793 if (l_parser.id <> -1)
4794 then
4795 xmlParser.freeParser(l_parser);
4796 end if;
4797 if (not xmldom.isNull(l_xsl_doc))
4798 then
4799 xmldom.freeDocument(l_xsl_doc);
4800 end if;
4801 if (l_procedureEnabled) then
4802 ecx_debug.pop(i_method_name);
4803 end if;
4804 raise ecx_utils.program_exit;
4805 when xmlDOM.No_MODIFICATION_ALLOWED_ERR then
4806 ecx_debug.setErrorInfo(1,20,SQLERRM);
4807 if(l_unexpectedEnabled) then
4808 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4809 end if;
4810 -- free all the used variables
4811 if (l_parser.id <> -1)
4812 then
4813 xmlParser.freeParser(l_parser);
4814 end if;
4815 if (not xmldom.isNull(l_xsl_doc))
4816 then
4817 xmldom.freeDocument(l_xsl_doc);
4818 end if;
4819 if (l_procedureEnabled) then
4820 ecx_debug.pop(i_method_name);
4821 end if;
4822 raise ecx_utils.program_exit;
4823 when xmlDOM.NOT_FOUND_ERR then
4824 ecx_debug.setErrorInfo(1,20,SQLERRM);
4825 if(l_unexpectedEnabled) then
4826 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4827 end if;
4828 -- free all the used variables
4829 if (l_parser.id <> -1)
4830 then
4831 xmlParser.freeParser(l_parser);
4832 end if;
4833 if (not xmldom.isNull(l_xsl_doc))
4834 then
4835 xmldom.freeDocument(l_xsl_doc);
4836 end if;
4837 if (l_procedureEnabled) then
4838 ecx_debug.pop(i_method_name);
4839 end if;
4840 raise ecx_utils.program_exit;
4841 when xmlDOM.NOT_SUPPORTED_ERR then
4842 ecx_debug.setErrorInfo(1,20,SQLERRM);
4843 if(l_unexpectedEnabled) then
4844 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4845 end if;
4846 -- free all the used variables
4847 if (l_parser.id <> -1)
4848 then
4849 xmlParser.freeParser(l_parser);
4850 end if;
4851 if (not xmldom.isNull(l_xsl_doc))
4852 then
4853 xmldom.freeDocument(l_xsl_doc);
4854 end if;
4855 if (l_procedureEnabled) then
4856 ecx_debug.pop(i_method_name);
4857 end if;
4858 raise ecx_utils.program_exit;
4859 when xmlDOM.INUSE_ATTRIBUTE_ERR then
4860 ecx_debug.setErrorInfo(1,20,SQLERRM);
4861 -- free all the used variables
4862 if (l_parser.id <> -1)
4863 then
4864 xmlParser.freeParser(l_parser);
4865 end if;
4866 if (not xmldom.isNull(l_xsl_doc))
4867 then
4868 xmldom.freeDocument(l_xsl_doc);
4869 end if;
4870 if(l_unexpectedEnabled) then
4871 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4872 end if;
4873 if (l_procedureEnabled) then
4874 ecx_debug.pop(i_method_name);
4875 end if;
4876 raise ecx_utils.program_exit;
4877
4878 WHEN OTHERS THEN
4879 if(l_unexpectedEnabled) then
4880 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.TRANSFORM_XML_WITH_XSLT');
4881 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
4882 end if;
4883 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.TRANSFORM_XML_WITH_XSLT');
4884 -- free all the used variables
4885 if (l_parser.id <> -1)
4886 then
4887 xmlParser.freeParser(l_parser);
4888 end if;
4889 if (not xmldom.isNull(l_xsl_doc))
4890 then
4891 xmldom.freeDocument(l_xsl_doc);
4892 end if;
4893 if (l_procedureEnabled) then
4894 ecx_debug.pop(i_method_name);
4895 end if;
4896 raise ecx_utils.PROGRAM_EXIT;
4897 end transform_xml_with_xslt;
4898
4899 /**
4900 Executes the Data from the ECX_TRAN_STAGE_DATA for a given Stage and Level.
4901 **/
4902 procedure execute_stage_data (
4903 i_stage IN pls_integer,
4904 i_level IN pls_integer,
4905 i_direction IN varchar2 ) IS
4906
4907 i_method_name varchar2(2000) := 'ecx_actions.execute_stage_data';
4908
4909 /* Cursor to get all the Level 0 Stack Data */
4913 variable_direction,
4910 cursor stack_data is
4911 select variable_name,
4912 variable_value,
4914 data_type datatype
4915 from ecx_tran_stage_data ets
4916 where ets.map_id = ecx_utils.g_map_id
4917 and variable_level = 0
4918 and action_type = 10;
4919
4920 i_counter pls_integer:= 0;
4921 /** variables for evaluating the condition **/
4922
4923 i_var1 varchar2(4000);
4924 i_var2 varchar2(4000);
4925 i_val1 varchar2(4000);
4926 i_val2 varchar2(4000);
4927
4928 i_vartype1 pls_integer;
4929 i_valtype1 pls_integer;
4930 i_vartype2 pls_integer;
4931 i_valtype2 pls_integer;
4932 stack_var boolean;
4933 stack_pos pls_integer;
4934 i_date date;
4935 i_number number;
4936
4937 condition_flag boolean := true;
4938
4939 begin
4940 if (l_procedureEnabled) then
4941 ecx_debug.push(i_method_name);
4942 end if;
4943 if(l_statementEnabled) then
4944 ecx_debug.log(l_statement,'i_stage',i_stage,i_method_name);
4945 ecx_debug.log(l_statement,'i_level',i_level,i_method_name);
4946 ecx_debug.log(l_statement,'i_direction',i_direction,i_method_name);
4947 end if;
4948
4949 if i_stage = 10 then
4950
4951 if (i_level = 0) then
4952
4953 ecx_actions.g_server_timezone := null;
4954 ecx_actions.g_xslt_dir := null;
4955
4956 ecx_utils.g_stack.DELETE;
4957
4958 for get_stack_data in stack_data loop
4959 i_counter := i_counter + 1;
4960 ecx_utils.g_stack(i_counter).variable_name := get_stack_data.variable_name;
4961 ecx_utils.g_stack(i_counter).variable_value := get_stack_data.variable_value;
4962 ecx_utils.g_stack(i_counter).data_type := get_stack_data.datatype;
4963 end loop;
4964
4965 if(l_statementEnabled) then
4966 dump_stack;
4967 end if;
4968
4969 end if;
4970 end if;
4971
4972 if(ecx_utils.g_stage_data.count <> 0)
4973 then
4974 FOR i in ecx_utils.g_stage_data.first..ecx_utils.g_stage_data.last
4975 loop
4976 exit when i = ecx_utils.g_stage_data.count;
4977 if (ecx_utils.g_stage_data(i).stage = i_stage
4978 and ecx_utils.g_stage_data(i).level = i_level and
4979 ecx_utils.g_stage_data(i).object_direction = i_direction)
4980 then
4981
4982 /**
4983 Check for Condition First. If Defined , evaluate it first and then execute an action.
4984 If the condition is not defined , execute the action i.e. action without condition.
4985 Initialize the values of the variables.
4986 Word NULL will be used to specify NULL values for the Variables. It will be reserved for XML gateway.
4987 **/
4988 i_var1 :=null;
4989 i_var2 :=null;
4990 i_val1 :=null;
4991 i_val2 :=null;
4992
4993 /** Set Default Datatype to varchar2 **/
4994 i_vartype1 :=1;
4995 i_valtype1 :=1;
4996 i_vartype2 :=1;
4997 i_valtype2 :=1;
4998 condition_flag :=true;
4999
5000 if ecx_utils.g_stage_data(i).cond_operator1 is not null
5001 then
5002 /** Condition based on one variable **/
5003 -- Find variable Values
5004 -- Look for Default first. If null , get it from the variable
5005
5006 i_var1 := ecx_utils.g_stage_data(i).cond_var1_constant;
5007 if i_var1 is null
5008 then
5009 get_var_attr (
5010 ecx_utils.g_stage_data(i).cond_var1_level,
5011 ecx_utils.g_stage_data(i).cond_var1_name,
5012 ecx_utils.g_stage_data(i).cond_var1_direction,
5013 ecx_utils.g_stage_data(i).cond_var1_pos,
5014 i_var1,
5015 stack_var,
5016 stack_pos);
5017
5018 /** Get the Data Type for the Left hand side variables **/
5019 if ecx_utils.g_stage_data(i).cond_var1_direction = 'G'
5020 then
5021 i_vartype1 := ecx_utils.g_stack(stack_pos).data_type;
5022 else
5023 if ecx_utils.g_stage_data(i).cond_var1_direction = 'S'
5024 then
5025 i_vartype1 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_var1_pos).data_type;
5026 else
5027 i_vartype1 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_var1_pos).data_type;
5028 end if;
5029 end if;
5030 if(l_statementEnabled) then
5031 ecx_debug.log(l_statement,'i_vartype1',i_vartype1,i_method_name);
5032 end if;
5033 else
5034 if i_var1 = 'NULL'
5035 then
5036 i_var1 :=null;
5037 else
5038 /** Try to find out the data type **/
5039 /** Attempt for date **/
5040 begin
5041 i_date := to_date(i_var1,'YYYYMMDD HH24MISS');
5042 if(l_statementEnabled) then
5043 ecx_debug.log(l_statement,'i_date',i_date,i_method_name);
5044 end if;
5045 i_vartype1 :=12;
5046 exception
5047 when others then
5048 if(l_unexpectedEnabled) then
5049 ecx_debug.log(l_unexpected,'i_date','Not a date',i_method_name);
5050 end if;
5051
5052 /** Attempt for number **/
5053 begin
5054 i_number := to_number(i_var1);
5055 if(l_unexpectedEnabled) then
5056 ecx_debug.log(l_unexpected,'i_number',i_number,i_method_name);
5057 end if;
5058 i_vartype1 :=2;
5059 exception
5060 when others then
5061 if(l_unexpectedEnabled) then
5062 ecx_debug.log(l_unexpected,'i_number','Not a number',i_method_name);
5063 end if;
5064 end;
5065 end;
5066 end if;
5067 end if;
5068
5069
5070
5071
5072 if ecx_utils.g_stage_data(i).cond_operator1 not in ('6','7')
5073 then
5074 i_val1 := ecx_utils.g_stage_data(i).cond_val1_constant;
5075 if i_val1 is null
5076 then
5077 get_var_attr (
5078 ecx_utils.g_stage_data(i).cond_val1_level,
5079 ecx_utils.g_stage_data(i).cond_val1_name,
5080 ecx_utils.g_stage_data(i).cond_val1_direction,
5081 ecx_utils.g_stage_data(i).cond_val1_pos,
5082 i_val1,
5083 stack_var,
5084 stack_pos);
5085 /** Get the Data Type for the right hand side variables **/
5086 if ecx_utils.g_stage_data(i).cond_val1_direction = 'G'
5087 then
5088 i_valtype1 := ecx_utils.g_stack(stack_pos).data_type;
5089 else
5090 if ecx_utils.g_stage_data(i).cond_val1_direction = 'S'
5091 then
5092 i_valtype1 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_val1_pos).data_type;
5093 else
5094 i_valtype1 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_val1_pos).data_type;
5095 end if;
5096 end if;
5097 if(l_statementEnabled) then
5098 ecx_debug.log(l_statement,'i_valtype1',i_valtype1,i_method_name);
5099 end if;
5100 else
5101 if i_val1 = 'NULL'
5102 then
5103 i_val1 :=null;
5104 else
5105 /** Try to find out the data type **/
5106 /** Attempt for date **/
5107 begin
5108 i_date := to_date(i_val1,'YYYYMMDD HH24MISS');
5109 if(l_statementEnabled) then
5110 ecx_debug.log(l_statement,'i_date',i_date,i_method_name);
5111 end if;
5112 i_valtype1 :=12;
5113 exception
5114 when others then
5115 if(l_unexpectedEnabled) then
5116 ecx_debug.log(l_unexpected,'i_date','Not a date',i_method_name);
5117 end if;
5118 /** Attempt for number **/
5119 begin
5120 i_number := to_number(i_val1);
5121 if(l_unexpectedEnabled) then
5122 ecx_debug.log(l_unexpected,'i_number',i_number,i_method_name);
5123 end if;
5124 i_valtype1 :=2;
5125 exception
5126 when others then
5127 if(l_unexpectedEnabled) then
5128 ecx_debug.log(l_unexpected,'i_number','Not a number',
5129 i_method_name);
5130 end if;
5131
5132 end;
5133 end;
5134 end if;
5135 end if;
5136
5137 end if;
5138 end if;
5139
5140 if ecx_utils.g_stage_data(i).cond_logical_operator is not null
5141 then
5142
5143 /** Condition based on two variables **/
5144 i_var2 := ecx_utils.g_stage_data(i).cond_var2_constant;
5145 if i_var2 is null
5146 then
5147 get_var_attr (
5148 ecx_utils.g_stage_data(i).cond_var2_level,
5149 ecx_utils.g_stage_data(i).cond_var2_name,
5150 ecx_utils.g_stage_data(i).cond_var2_direction,
5151 ecx_utils.g_stage_data(i).cond_var2_pos,
5152 i_var2,
5153 stack_var,
5154 stack_pos);
5155 end if;
5156
5157
5158 if i_var2 = 'NULL'
5159 then
5160 i_var2 :=null;
5161 end if;
5162
5163 /** Get the Data Type for the Left hand side variables **/
5164 if ecx_utils.g_stage_data(i).cond_var2_direction = 'G'
5165 then
5166 i_vartype2 := ecx_utils.g_stack(stack_pos).data_type;
5167 else
5168 if ecx_utils.g_stage_data(i).cond_var2_direction = 'S'
5169 then
5170 i_vartype2 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_var2_pos).data_type;
5171 else
5172 i_vartype2 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_var2_pos).data_type;
5173 end if;
5174 end if;
5175 if(l_statementEnabled) then
5176 ecx_debug.log(l_statement,'i_vartype2',i_vartype2,i_method_name);
5177 end if;
5178
5179
5180 if ecx_utils.g_stage_data(i).cond_operator1 not in ('6','7')
5181 then
5182 i_val2 := ecx_utils.g_stage_data(i).cond_val2_constant;
5183 if i_val2 is null
5184 then
5185 get_var_attr (
5186 ecx_utils.g_stage_data(i).cond_val2_level,
5187 ecx_utils.g_stage_data(i).cond_val2_name,
5188 ecx_utils.g_stage_data(i).cond_val2_direction,
5189 ecx_utils.g_stage_data(i).cond_val2_pos,
5190 i_val2,
5191 stack_var,
5192 stack_pos);
5193 end if;
5194
5195 if i_val2 = 'NULL'
5196 then
5197 i_val2 :=null;
5198 end if;
5199
5200 /** Get the Data Type for the right hand side variables **/
5201 if ecx_utils.g_stage_data(i).cond_val2_direction = 'G'
5202 then
5203 i_valtype2 := ecx_utils.g_stack(stack_pos).data_type;
5204 else
5205 if ecx_utils.g_stage_data(i).cond_val2_direction = 'S'
5206 then
5207 i_valtype2 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_val2_pos).data_type;
5208 else
5212 if(l_statementEnabled) then
5209 i_valtype2 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_val2_pos).data_type;
5210 end if;
5211 end if;
5213 ecx_debug.log(l_statement,'i_valtype2',i_valtype2,i_method_name);
5214 end if;
5215
5216 end if;
5217 end if;
5218
5219 /** Now check the condition **/
5220 if (ecx_utils.g_stage_data(i).cond_operator1 is not null
5221 or
5222 ecx_utils.g_stage_data(i).cond_logical_operator is not null
5223 )
5224 then
5225 condition_flag := ecx_conditions.check_condition
5226 (
5227 ecx_utils.g_stage_data(i).cond_logical_operator,
5228 ecx_utils.g_stage_data(i).cond_operator1,
5229 i_var1,
5230 i_vartype1,
5231 i_val1,
5232 i_valtype1,
5233 ecx_utils.g_stage_data(i).cond_operator2,
5234 i_var2,
5235 i_vartype2,
5236 i_val2,
5237 i_valtype2
5238 );
5239 else
5240 /** No Condition Found **/
5241 condition_flag :=true;
5242 end if;
5243
5244 if ( condition_flag )
5245 then
5246
5247 /** Not required anymore
5248 if ecx_utils.g_stage_data(i).action_type = ASSIGN_DEFAULT then
5249 assign_default_to_variables
5250 (
5251 ecx_utils.g_stage_data(i).variable_level,
5252 ecx_utils.g_stage_data(i).variable_name,
5253 ecx_utils.g_stage_data(i).variable_direction,
5254 ecx_utils.g_stage_data(i).variable_pos,
5255 ecx_utils.g_stage_data(i).default_value
5256 );
5257 **/
5258
5259 if ecx_utils.g_stage_data(i).action_type = ASSIGN_PRE_DEFINED then
5260 assign_pre_defined_variables
5261 (
5262 ecx_utils.g_stage_data(i).variable_level,
5263 ecx_utils.g_stage_data(i).variable_name,
5264 ecx_utils.g_stage_data(i).variable_direction,
5265 ecx_utils.g_stage_data(i).variable_pos,
5266 ecx_utils.g_stage_data(i).operand1_level,
5267 ecx_utils.g_stage_data(i).operand1_name,
5268 ecx_utils.g_stage_data(i).operand1_direction,
5269 ecx_utils.g_stage_data(i).operand1_pos,
5270 ecx_utils.g_stage_data(i).operand1_constant
5271 );
5272
5273 elsif ecx_utils.g_stage_data(i).action_type = ASSIGN_NEXTVALUE then
5274 assign_nextval_from_sequence
5275 (
5276 ecx_utils.g_stage_data(i).variable_level,
5277 ecx_utils.g_stage_data(i).variable_name,
5278 ecx_utils.g_stage_data(i).variable_direction,
5279 ecx_utils.g_stage_data(i).variable_pos,
5280 ecx_utils.g_stage_data(i).sequence_name
5281 );
5282
5283 elsif ecx_utils.g_stage_data(i).action_type = ASSIGN_FUNCVAL then
5284 assign_function_value
5285 (
5286 ecx_utils.g_stage_data(i).variable_level,
5287 ecx_utils.g_stage_data(i).variable_name,
5288 ecx_utils.g_stage_data(i).variable_direction,
5289 ecx_utils.g_stage_data(i).variable_pos,
5290 ecx_utils.g_stage_data(i).function_name
5291 );
5292
5293 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGDATE then
5294 convert_to_oag_date
5295 (
5296 ecx_utils.g_stage_data(i).variable_level,
5297 ecx_utils.g_stage_data(i).variable_name,
5298 ecx_utils.g_stage_data(i).variable_pos,
5299 ecx_utils.g_stage_data(i).variable_direction,
5300 ecx_utils.g_stage_data(i).operand1_level,
5301 ecx_utils.g_stage_data(i).operand1_name,
5302 ecx_utils.g_stage_data(i).operand1_pos,
5303 ecx_utils.g_stage_data(i).operand1_direction,
5304 ecx_utils.g_stage_data(i).operand1_constant
5305 );
5306
5307 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGOPERAMT then
5308 convert_to_oag_operamt(
5309 ecx_utils.g_stage_data(i).variable_level,
5310 ecx_utils.g_stage_data(i).variable_name,
5311 ecx_utils.g_stage_data(i).variable_pos,
5312 ecx_utils.g_stage_data(i).variable_direction,
5313 ecx_utils.g_stage_data(i).operand1_level,
5314 ecx_utils.g_stage_data(i).operand1_name,
5315 ecx_utils.g_stage_data(i).operand1_pos,
5316 ecx_utils.g_stage_data(i).operand1_direction,
5317 ecx_utils.g_stage_data(i).operand1_constant,
5318 ecx_utils.g_stage_data(i).operand2_level,
5319 ecx_utils.g_stage_data(i).operand2_name,
5320 ecx_utils.g_stage_data(i).operand2_pos,
5321 ecx_utils.g_stage_data(i).operand2_direction,
5322 ecx_utils.g_stage_data(i).operand2_constant,
5323 ecx_utils.g_stage_data(i).operand3_level,
5324 ecx_utils.g_stage_data(i).operand3_name,
5325 ecx_utils.g_stage_data(i).operand3_pos,
5326 ecx_utils.g_stage_data(i).operand3_direction,
5327 ecx_utils.g_stage_data(i).operand3_constant
5328 );
5329 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGAMT then
5330 convert_to_oag_amt(
5331 ecx_utils.g_stage_data(i).variable_level,
5332 ecx_utils.g_stage_data(i).variable_name,
5333 ecx_utils.g_stage_data(i).variable_pos,
5334 ecx_utils.g_stage_data(i).variable_direction,
5335 ecx_utils.g_stage_data(i).operand1_level,
5336 ecx_utils.g_stage_data(i).operand1_name,
5337 ecx_utils.g_stage_data(i).operand1_pos,
5338 ecx_utils.g_stage_data(i).operand1_direction,
5339 ecx_utils.g_stage_data(i).operand1_constant,
5340 ecx_utils.g_stage_data(i).operand2_level,
5341 ecx_utils.g_stage_data(i).operand2_name,
5342 ecx_utils.g_stage_data(i).operand2_pos,
5343 ecx_utils.g_stage_data(i).operand2_direction,
5344 ecx_utils.g_stage_data(i).operand2_constant,
5345 ecx_utils.g_stage_data(i).operand3_level,
5346 ecx_utils.g_stage_data(i).operand3_name,
5347 ecx_utils.g_stage_data(i).operand3_pos,
5348 ecx_utils.g_stage_data(i).operand3_direction,
5349 ecx_utils.g_stage_data(i).operand3_constant
5350 );
5354 ecx_utils.g_stage_data(i).variable_level,
5351
5352 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGQUANT then
5353 convert_to_oag_quantity(
5355 ecx_utils.g_stage_data(i).variable_name,
5356 ecx_utils.g_stage_data(i).variable_pos,
5357 ecx_utils.g_stage_data(i).variable_direction,
5358 ecx_utils.g_stage_data(i).operand1_level,
5359 ecx_utils.g_stage_data(i).operand1_name,
5360 ecx_utils.g_stage_data(i).operand1_pos,
5361 ecx_utils.g_stage_data(i).operand1_direction,
5362 ecx_utils.g_stage_data(i).operand1_constant,
5363 ecx_utils.g_stage_data(i).operand2_level,
5364 ecx_utils.g_stage_data(i).operand2_name,
5365 ecx_utils.g_stage_data(i).operand2_pos,
5366 ecx_utils.g_stage_data(i).operand2_direction,
5367 ecx_utils.g_stage_data(i).operand2_constant
5368 );
5369
5370 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGDATE then
5371 convert_from_oag_date
5372 (
5373 ecx_utils.g_stage_data(i).variable_level,
5374 ecx_utils.g_stage_data(i).variable_name,
5375 ecx_utils.g_stage_data(i).variable_pos,
5376 ecx_utils.g_stage_data(i).variable_direction,
5377 ecx_utils.g_stage_data(i).operand1_level,
5378 ecx_utils.g_stage_data(i).operand1_name,
5379 ecx_utils.g_stage_data(i).operand1_pos,
5380 ecx_utils.g_stage_data(i).operand1_direction,
5381 ecx_utils.g_stage_data(i).operand1_constant
5382 );
5383
5384 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGOPERAMT then
5385 convert_from_oag_operamt(
5386 ecx_utils.g_stage_data(i).variable_level,
5387 ecx_utils.g_stage_data(i).variable_name,
5388 ecx_utils.g_stage_data(i).variable_pos,
5389 ecx_utils.g_stage_data(i).variable_direction,
5390 ecx_utils.g_stage_data(i).operand1_level,
5391 ecx_utils.g_stage_data(i).operand1_name,
5392 ecx_utils.g_stage_data(i).operand1_pos,
5393 ecx_utils.g_stage_data(i).operand1_direction,
5394 ecx_utils.g_stage_data(i).operand1_constant,
5395 ecx_utils.g_stage_data(i).operand2_level,
5396 ecx_utils.g_stage_data(i).operand2_name,
5397 ecx_utils.g_stage_data(i).operand2_pos,
5398 ecx_utils.g_stage_data(i).operand2_direction,
5399 ecx_utils.g_stage_data(i).operand2_constant,
5400 ecx_utils.g_stage_data(i).operand3_level,
5401 ecx_utils.g_stage_data(i).operand3_name,
5402 ecx_utils.g_stage_data(i).operand3_pos,
5403 ecx_utils.g_stage_data(i).operand3_direction,
5404 ecx_utils.g_stage_data(i).operand3_constant
5405 );
5406 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGAMT then
5407 convert_from_oag_amt(
5408 ecx_utils.g_stage_data(i).variable_level,
5409 ecx_utils.g_stage_data(i).variable_name,
5410 ecx_utils.g_stage_data(i).variable_pos,
5411 ecx_utils.g_stage_data(i).variable_direction,
5412 ecx_utils.g_stage_data(i).operand1_level,
5413 ecx_utils.g_stage_data(i).operand1_name,
5414 ecx_utils.g_stage_data(i).operand1_pos,
5415 ecx_utils.g_stage_data(i).operand1_direction,
5416 ecx_utils.g_stage_data(i).operand1_constant,
5417 ecx_utils.g_stage_data(i).operand2_level,
5418 ecx_utils.g_stage_data(i).operand2_name,
5419 ecx_utils.g_stage_data(i).operand2_pos,
5420 ecx_utils.g_stage_data(i).operand2_direction,
5421 ecx_utils.g_stage_data(i).operand2_constant,
5422 ecx_utils.g_stage_data(i).operand3_level,
5423 ecx_utils.g_stage_data(i).operand3_name,
5424 ecx_utils.g_stage_data(i).operand3_pos,
5425 ecx_utils.g_stage_data(i).operand3_direction,
5426 ecx_utils.g_stage_data(i).operand3_constant
5427 );
5428
5429 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGQUANT then
5430 convert_from_oag_quantity(
5431 ecx_utils.g_stage_data(i).variable_level,
5432 ecx_utils.g_stage_data(i).variable_name,
5433 ecx_utils.g_stage_data(i).variable_pos,
5434 ecx_utils.g_stage_data(i).variable_direction,
5435 ecx_utils.g_stage_data(i).operand1_level,
5436 ecx_utils.g_stage_data(i).operand1_name,
5437 ecx_utils.g_stage_data(i).operand1_pos,
5438 ecx_utils.g_stage_data(i).operand1_direction,
5439 ecx_utils.g_stage_data(i).operand1_constant,
5440 ecx_utils.g_stage_data(i).operand2_level,
5441 ecx_utils.g_stage_data(i).operand2_name,
5442 ecx_utils.g_stage_data(i).operand2_pos,
5443 ecx_utils.g_stage_data(i).operand2_direction,
5444 ecx_utils.g_stage_data(i).operand2_constant
5445 );
5446
5447 elsif ecx_utils.g_stage_data(i).action_type = INSERT_INTO_OPEN_INTERFACE then
5448 insert_level_into_table (i_level);
5449
5450 /** Removed the call as it is being called in the initialization of source itself **/
5451 --elsif ecx_utils.g_stage_data(i).action_type = APPEND_WHERECLAUSE then
5452 --append_clause
5453 --(
5454 --ecx_utils.g_stage_data(i).level,
5455 --ecx_utils.g_stage_data(i).clause
5456 --);
5457
5458 elsif ecx_utils.g_stage_data(i).action_type = EXEC_PROCEDURES then
5459 execute_proc
5460 (
5461 ecx_utils.g_stage_data(i).transtage_id,
5462 ecx_utils.g_stage_data(i).custom_procedure_name
5463 );
5464
5465 elsif ecx_utils.g_stage_data(i).action_type = EXITPROGRAM then
5466 exit_program;
5467
5468 elsif ecx_utils.g_stage_data(i).action_type = SEND_ERROR then
5469 send_err
5470 (
5471 ecx_utils.g_stage_data(i).operand1_level,
5472 ecx_utils.g_stage_data(i).operand1_name,
5473 ecx_utils.g_stage_data(i).operand1_direction,
5474 ecx_utils.g_stage_data(i).operand1_pos,
5478 ecx_utils.g_stage_data(i).operand2_direction,
5475 ecx_utils.g_stage_data(i).operand1_constant,
5476 ecx_utils.g_stage_data(i).operand2_level,
5477 ecx_utils.g_stage_data(i).operand2_name,
5479 ecx_utils.g_stage_data(i).operand2_pos,
5480 ecx_utils.g_stage_data(i).operand2_constant
5481 );
5482
5483 elsif ecx_utils.g_stage_data(i).action_type = API_RETURN_CODE then
5484 get_api_retcode
5485 (
5486 ecx_utils.g_stage_data(i).variable_level,
5487 ecx_utils.g_stage_data(i).variable_name,
5488 ecx_utils.g_stage_data(i).variable_direction,
5489 ecx_utils.g_stage_data(i).variable_pos,
5490 ecx_utils.g_stage_data(i).default_value,
5491 ecx_utils.g_stage_data(i).operand1_level,
5492 ecx_utils.g_stage_data(i).operand1_name,
5493 ecx_utils.g_stage_data(i).operand1_direction,
5494 ecx_utils.g_stage_data(i).operand1_pos,
5495 ecx_utils.g_stage_data(i).function_name
5496 );
5497 elsif ecx_utils.g_stage_data(i).action_type = CONCAT_VAR then
5498 concat_variables
5499 (
5500 ecx_utils.g_stage_data(i).variable_level,
5501 ecx_utils.g_stage_data(i).variable_name,
5502 ecx_utils.g_stage_data(i).variable_direction,
5503 ecx_utils.g_stage_data(i).variable_pos,
5507 ecx_utils.g_stage_data(i).operand1_pos,
5504 ecx_utils.g_stage_data(i).operand1_level,
5505 ecx_utils.g_stage_data(i).operand1_name,
5506 ecx_utils.g_stage_data(i).operand1_direction,
5508 ecx_utils.g_stage_data(i).operand1_constant,
5509 ecx_utils.g_stage_data(i).operand2_level,
5510 ecx_utils.g_stage_data(i).operand2_name,
5511 ecx_utils.g_stage_data(i).operand2_direction,
5512 ecx_utils.g_stage_data(i).operand2_pos,
5513 ecx_utils.g_stage_data(i).operand2_constant
5514 );
5515
5516 elsif ecx_utils.g_stage_data(i).action_type = SUBSTR_VAR then
5517 substr_variables
5518 (
5519 ecx_utils.g_stage_data(i).variable_level,
5520 ecx_utils.g_stage_data(i).variable_name,
5521 ecx_utils.g_stage_data(i).variable_direction,
5522 ecx_utils.g_stage_data(i).variable_pos,
5523 ecx_utils.g_stage_data(i).operand1_level,
5524 ecx_utils.g_stage_data(i).operand1_name,
5525 ecx_utils.g_stage_data(i).operand1_direction,
5526 ecx_utils.g_stage_data(i).operand1_pos,
5527 ecx_utils.g_stage_data(i).operand1_constant,
5528 ecx_utils.g_stage_data(i).operand2_level,
5529 ecx_utils.g_stage_data(i).operand2_name,
5530 ecx_utils.g_stage_data(i).operand2_direction,
5531 ecx_utils.g_stage_data(i).operand2_pos,
5532 ecx_utils.g_stage_data(i).operand2_constant,
5533 ecx_utils.g_stage_data(i).operand3_level,
5534 ecx_utils.g_stage_data(i).operand3_name,
5535 ecx_utils.g_stage_data(i).operand3_direction,
5536 ecx_utils.g_stage_data(i).operand3_pos,
5537 ecx_utils.g_stage_data(i).operand3_constant
5538 );
5539
5540 elsif ecx_utils.g_stage_data(i).action_type in ( 4000,4010,4020,4030)
5541 then
5542 execute_math_functions
5543 (
5544 ecx_utils.g_stage_data(i).action_type,
5545 ecx_utils.g_stage_data(i).variable_level,
5546 ecx_utils.g_stage_data(i).variable_name,
5547 ecx_utils.g_stage_data(i).variable_pos,
5548 ecx_utils.g_stage_data(i).variable_direction,
5549 ecx_utils.g_stage_data(i).operand1_level,
5550 ecx_utils.g_stage_data(i).operand1_name,
5551 ecx_utils.g_stage_data(i).operand1_pos,
5552 ecx_utils.g_stage_data(i).operand1_direction,
5553 ecx_utils.g_stage_data(i).operand1_constant,
5554 ecx_utils.g_stage_data(i).operand2_level,
5555 ecx_utils.g_stage_data(i).operand2_name,
5556 ecx_utils.g_stage_data(i).operand2_pos,
5557 ecx_utils.g_stage_data(i).operand2_direction,
5558 ecx_utils.g_stage_data(i).operand2_constant
5559 );
5560 elsif ecx_utils.g_stage_data(i).action_type = XSLT_TRANSFORM then
5561 transform_xml_with_xslt
5562 (
5563 ecx_utils.g_stage_data(i).default_value,
5564 ecx_utils.g_stage_data(i).operand1_level,
5565 ecx_utils.g_stage_data(i).operand1_name,
5566 ecx_utils.g_stage_data(i).operand1_pos,
5567 ecx_utils.g_stage_data(i).operand1_direction,
5568 ecx_utils.g_stage_data(i).operand1_constant
5569 );
5570 elsif ecx_utils.g_stage_data(i).action_type = GET_ADDRESS_ID
5571 then
5572 derive_address_id
5573 (
5574 ecx_utils.g_stage_data(i).variable_level,
5575 ecx_utils.g_stage_data(i).variable_name,
5576 ecx_utils.g_stage_data(i).variable_pos,
5577 ecx_utils.g_stage_data(i).variable_direction,
5578 ecx_utils.g_stage_data(i).operand1_level,
5579 ecx_utils.g_stage_data(i).operand1_name,
5580 ecx_utils.g_stage_data(i).operand1_pos,
5581 ecx_utils.g_stage_data(i).operand1_direction,
5582 ecx_utils.g_stage_data(i).operand1_constant,
5583 ecx_utils.g_stage_data(i).operand2_constant,
5584 ecx_utils.g_stage_data(i).operand3_level,
5585 ecx_utils.g_stage_data(i).operand3_name,
5586 ecx_utils.g_stage_data(i).operand3_pos,
5587 ecx_utils.g_stage_data(i).operand3_direction
5588 );
5589 end if;
5590
5591 end if; --- Condition Flag
5592
5593 end if; -- Stage Check
5594 end loop;
5595 end if;
5596 if(l_statementEnabled) then
5597 dump_stack;
5598 end if;
5599
5600 if (l_procedureEnabled) then
5601 ecx_debug.pop(i_method_name);
5602 end if;
5603
5604 EXCEPTION
5605 WHEN ecx_utils.PROGRAM_EXIT then
5606 if (l_procedureEnabled) then
5607 ecx_debug.pop(i_method_name);
5608 end if;
5609 raise;
5610
5611 WHEN OTHERS THEN
5612 if(l_unexpectedEnabled) then
5613 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
5614 'PROGRESS_LEVEL','ECX_ACTIONS.EXECUTE_STAGE_DATA');
5615 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
5616 end if;
5617 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXECUTE_STAGE_DATA');
5618 if (l_procedureEnabled) then
5619 ecx_debug.pop(i_method_name);
5620 end if;
5621 raise ecx_utils.PROGRAM_EXIT;
5622
5623
5624 end execute_stage_data;
5625
5626 /*
5627 Gets the timezone offset for the DB server timezone based on the date
5628 */
5629 Function getTimeZoneOffset (year number, month number, day number, hour number,
5630 minute number, second number, timezone varchar2)
5631 return number
5632 is language java
5633 name 'oracle.apps.ecx.util.TimeZoneOffset.getOffset(int, int, int, int, int, int,
5634 java.lang.String) returns float';
5635
5636
5637 Procedure get_clob(clobValue in clob , value in Varchar2 , clobOut out nocopy clob) as
5638 i_method_name varchar2(2000) := 'ecx_actions.get_clob';
5639 begin
5640
5644 if (clobValue is not null) or
5641 if (l_procedureEnabled) then
5642 ecx_debug.push(i_method_name);
5643 end if;
5645 (value is not null ) Then
5646 dbms_lob.createtemporary(clobOut,true,dbms_lob.session);
5647 if (value is not null) then
5648 dbms_lob.write(clobOut ,length(value),1,value );
5649 elsif (clobValue is not null) then
5650 clobOut := clobValue;
5651 end if;
5652 end if;
5653 if (l_procedureEnabled) then
5654 ecx_debug.pop(i_method_name);
5655 end if;
5656 EXCEPTION
5657 WHEN ecx_utils.PROGRAM_EXIT then
5658 if (l_procedureEnabled) then
5659 ecx_debug.pop(i_method_name);
5660 end if;
5661 raise;
5662
5663 WHEN OTHERS THEN
5664 if(l_unexpectedEnabled) then
5665 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name,
5666 'PROGRESS_LEVEL',
5667 'ECX_ACTIONS.GET_CLOB');
5668 end if;
5669 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_CLOB');
5670 if (l_procedureEnabled) then
5671 ecx_debug.pop(i_method_name);
5672 end if;
5673 raise ecx_utils.PROGRAM_EXIT;
5674 end;
5675
5676 procedure get_varchar(clobValue in clob , value in Varchar2 , valueOut out nocopy varchar2) as
5677 i_method_name varchar2(2000) := 'ecx_actions.get_varchar';
5678 begin
5679 if (l_procedureEnabled) then
5680 ecx_debug.push(i_method_name);
5681 end if;
5682 if (value is not null) then
5683 valueOut := substr(value , 1, ecx_utils.G_VARCHAR_LEN);
5684 elsif (clobValue is not null) then
5685 valueOut := dbms_lob.substr(clobValue,ecx_utils.G_VARCHAR_LEN,1);
5686 end if;
5687 if (l_procedureEnabled) then
5688 ecx_debug.pop(i_method_name);
5689 end if;
5690 EXCEPTION
5691 WHEN ecx_utils.PROGRAM_EXIT then
5692 if (l_procedureEnabled) then
5693 ecx_debug.pop(i_method_name);
5694 end if;
5695 raise;
5696 WHEN OTHERS THEN
5697 if(l_unexpectedEnabled) then
5698 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.GET_VARCHAR');
5699 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
5700 end if;
5701 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_VARCHAR');
5702 if (l_procedureEnabled) then
5703 ecx_debug.pop(i_method_name);
5704 end if;
5705 raise ecx_utils.PROGRAM_EXIT;
5706
5707 end;
5708
5709 procedure delete_doctype as
5710 begin
5711 null;
5712 end;
5713
5714 PROCEDURE get_xml_fragment
5715 ( proc_name IN varchar2,
5716 xml_fragment OUT NOCOPY varchar2
5717 ) as
5718 i_method_name varchar2(2000) := 'ecx_actions.get_xml_fragment';
5719 proc_call varchar2(32767);
5720 temp_xml varchar2(32767);
5721 temp_parser xmlparser.parser;
5722 v_name varchar2(32767);
5723 v_value varchar2(32767);
5724 BEGIN
5725 if (l_procedureEnabled) then
5726 ecx_debug.push(i_method_name);
5727 end if;
5728
5729
5730 -- Initialize the event if it is not already initialized.
5731 if (ecx_utils.g_event is null) then
5732 wf_event_t.initialize(ecx_utils.g_event);
5733 end if;
5734
5735 -- Add all global variables as parameters to the event.
5736 for k in 1..ecx_utils.g_stack.count
5737 loop
5738 v_name := ecx_utils.g_stack(k).variable_name;
5739 v_value := ecx_utils.g_stack(k).variable_value;
5740 ecx_utils.g_event.addparametertolist
5741 ( v_name,
5742 v_value);
5743
5744 if(l_statementEnabled) then
5745 ecx_debug.log(l_statement,'global variable name',
5746 ecx_utils.g_stack(k).variable_name,i_method_name);
5747 ecx_debug.log(l_statement,'global variable value',
5748 ecx_utils.g_stack(k).variable_value,i_method_name);
5749 end if;
5750 end loop;
5751 -- Call the procedure. The procedure takes wf_event_t as
5752 -- input and gives out xml_fragment as output.
5753
5754 proc_call := 'BEGIN ' || proc_name || ' (:EVENT,:XML_FRAGMENT);
5755 END;' ;
5756
5757 execute immediate proc_call using in ecx_utils.g_event, out xml_fragment;
5758
5759 if(l_statementEnabled) then
5760 ecx_debug.log(l_statement,'xml fragment',
5761 xml_fragment,i_method_name);
5762 end if;
5763
5764 -- Adding dummy root element before parsing the xml fragment.
5765
5766 temp_xml := '<dummy>'||xml_fragment||'</dummy>';
5767 temp_parser := xmlparser.newparser;
5768 xmlparser.parsebuffer(temp_parser,temp_xml);
5769 if (temp_parser.id not in (-1)) then
5770 xmlparser.freeparser(temp_parser);
5771 end if;
5772 if (l_procedureEnabled) then
5773 ecx_debug.pop(i_method_name);
5774 end if;
5775 EXCEPTION
5776 when others then
5777 if (temp_parser.id not in (-1)) then
5778 xmlparser.freeparser(temp_parser);
5779 end if;
5780 if(l_unexpectedEnabled) then
5781 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.GET_XML_FRAGMENT');
5782 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
5783 end if;
5784 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_XML_FRAGMENT');
5785 if (l_procedureEnabled) then
5786 ecx_debug.pop(i_method_name);
5787 end if;
5788 raise ecx_utils.PROGRAM_EXIT;
5789 end;
5790
5791 end ecx_actions;