[Home] [Help]
PACKAGE BODY: APPS.ECX_ACTIONS
Source
1 package body ecx_actions as
2 -- $Header: ECXACTNB.pls 120.13 2011/02/15 07:02:18 jmaddila ship $
3
4 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
5 l_statement PLS_INTEGER := ecx_debug.g_statement;
6 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
7 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
8 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
9 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
10
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
283 END find_stack_variable;
284
285
286 procedure build_insert_stmt (
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
349 ecx_debug.log(l_unexpected,'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
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);
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;
389 l_data_conversion_failed EXCEPTION;
390 l_clob_value CLOB;
391 l_value varchar2(32767);
392
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
490 end if;
487 WHEN others then
488 if dbms_lob.istemporary(l_clob_value) = 1 Then
489 dbms_lob.freetemporary(l_clob_value);
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);
498 if dbms_lob.istemporary(l_clob_value) = 1 Then
499 dbms_lob.freetemporary(l_clob_value);
500 end if;
501
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;
638 if(l_unexpectedEnabled) then
642 end if;
639 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR', i_method_name,'PROGRESS_LEVEL',
640 'ECX_ACTIONS.EXECUTE_STRING');
641 ecx_debug.log(l_unexpected, 'ECX', 'ECX_ERROR_MESSAGE', i_method_name, 'ERROR_MESSAGE', SQLERRM);
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;
783 raise ecx_utils.PROGRAM_EXIT;
784
785 END dump_stack;
786
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,
801 i_next_variable_pos IN pls_integer,
798 i_next_variable_level IN pls_integer,
799 i_next_variable_name IN Varchar2,
800 i_next_variable_direction IN varchar2,
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,
926 i_operand3_pos IN pls_integer,
927 i_operand3_constant IN varchar2
928 )
929 IS
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);
956 then
953
954 pre_var_value := i_previous_variable_constant;
955 if pre_var_value is null
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
1069 if pre_var_value is null
1070 then
1071 get_var_attr (i_previous_variable_level, i_previous_variable_name,
1072 i_previous_variable_direction,
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
1116 e.g. x := document.NEXTVAL;
1113
1114 /**
1115 Assign the Nextvalue from a sequence.
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,
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);
1222 end if;
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,
1276 if (l_procedureEnabled) then
1273 'ERROR_MESSAGE',SQLERRM);
1274 end if;
1275 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.APPEND_CLAUSE');
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;
1361 l_data_type pls_integer;
1362
1363 BEGIN
1364 if (l_procedureEnabled) then
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(
1416 end if;
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);
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;
1480 raise ecx_utils.PROGRAM_EXIT;
1481
1482 END bind_variables_for_view;
1483
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
1559 end if;
1556 WHEN ecx_utils.PROGRAM_EXIT then
1557 if (l_procedureEnabled) then
1558 ecx_debug.pop(i_method_name);
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);
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 */
1625 elsif ecx_utils.g_procedure_mappings(i).variable_direction = 'S'
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
1682 l_clob_value := null;
1679 get_varchar(l_clob_value ,
1680 l_var_value,
1681 l_var_value);
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
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,
1726 ':' ||ecx_utils.g_procedure_mappings(i).parameter_name, l_clob_value);
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');
1806 if dbms_lob.istemporary(l_clob_value) = 1 Then
1803 if dbms_lob.istemporary(l_temp_loc) = 1 Then
1804 dbms_lob.freetemporary(l_temp_loc);
1805 end if;
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;
1847 if ( ecx_utils.g_procedure_mappings(i).variable_direction = 'G' )
1848 then
1849 l_stack_var := find_stack_variable(
1850 ecx_utils.g_procedure_mappings(i).variable_name,
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
1933 else
1930 ecx_utils.g_target(l_var_pos).value := null;
1931 ecx_utils.g_target(l_var_pos).clob_value := null;
1932 end if;
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;
1953 if frag_found then
1954 exit;
1955 end if;
1956 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
2059 **/
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.
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,
2077 ecx_utils.g_procedure_list(i_transtage_id).cursor_handle);
2078
2079 assign_out_variables (i_transtage_id, i_procedure_name,
2080 ecx_utils.g_procedure_list(i_transtage_id).cursor_handle);
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;
2204 messages with parameters -2535659 **/
2201 end set_error_exit_program;
2202
2203 /** Overloaded for enabling MLS for user invoked error
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,
2212 p_value2 in varchar2,
2213 p_token3 in varchar2,
2214 p_value3 in varchar2,
2215 p_token4 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
2467 then
2468
2469 if i_default_value = 'CODE_CONVERSION'
2470 then
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
2481 i_variable_pos,i_variable_direction, ret_code);
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,
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
2588 p_sign := '-';
2589 ELSE
2590 p_sign := '+';
2591 END IF;
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
2618 from v$nls_parameters
2615 -- Get Num of Decimal places
2616 /* select substr(value, 1, 1)
2617 into nls_dec_char
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);
2733 ecx_debug.log(l_statement, 'i_server_offset_mins', i_server_offset_mins,i_method_name);
2734 end if;
2735
2736 if (l_procedureEnabled) then
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
2770 variable_value varchar2(2000);
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
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
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;
2870 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.CONVERT_TO_OAG_DATE');
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;
2909 opr1_stack Boolean := FALSE;
2906 stack_pos pls_integer;
2907
2908 /** For Opr1 **/
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 **/
2986 i_curr_code := i_opr2_constant;
2987 if i_curr_code is null
2988 then
2989 if i_opr2_level is not null
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
3058 end if;
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);
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
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);
3133 ecx_debug.log(l_statement,'i_variable_direction',i_variable_direction,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
3189 if i_opr3_level is not null
3186 i_opr3_value := i_opr3_constant;
3187 if i_opr3_value is null
3188 then
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);
3265 end if;
3266 raise ecx_utils.PROGRAM_EXIT;
3267 end convert_to_oag_amt;
3268
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
3333 i_opr1_value := i_opr1_constant;
3330 get_var_attr (i_variable_level, i_variable_name,
3331 i_variable_direction,i_variable_pos,var_value, stack_var, stack_pos);
3332
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
3399 ecx_debug.pop(i_method_name);
3400 end if;
3401 raise ecx_utils.PROGRAM_EXIT;
3402 end convert_to_oag_quantity;
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
3491 end if;
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');
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);
3533 end if;
3534
3535 return (x_date);
3536 exception
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"
3624 var_pos := i_opr1_pos+1;
3621 -- The interface column names should be named YEAR, MONTH, DAY etc.
3622 -- Calculate position
3623
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);
3656
3657
3658 if(l_statementEnabled) then
3659 ecx_debug.log(l_statement,'i_year',i_year,i_method_name);
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;
3763
3760 i_opr3_value Varchar2(2000);
3761
3762 begin
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);
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);
3785 ecx_debug.log(l_statement,'i_opr3_direction',i_opr3_direction,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,
3878 i_opr3_level IN pls_integer,
3875 i_opr2_pos IN pls_integer,
3876 i_opr2_direction IN Varchar2,
3877 i_opr2_constant IN varchar2,
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);
3894 i_crdr varchar2(500);
3895 var_pos number;
3896 stack_var boolean := FALSE;
3897 stack_pos pls_integer := null;
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
4004 then
4001 if ( i_crdr is null )
4002 then
4003 if ( i_sign = '+' )
4005 i_crdr := 'D';
4006 else
4007 i_crdr := 'C';
4008 end if;
4009 end if;
4010
4011 if ( i_opr3_level >=0 )
4012 then
4013 get_var_attr(i_opr3_level, i_opr3_name,i_opr3_direction,i_opr3_pos ,i_opr3_value, opr3_stack, opr3_stack_pos);
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);
4173 end if;
4174 raise ecx_utils.PROGRAM_EXIT;
4175 end derive_address_id;
4176
4177 procedure convert_from_oag_quantity(
4178 i_variable_level IN pls_integer,
4179 i_variable_name IN varchar2,
4183 i_opr1_name IN Varchar2,
4180 i_variable_pos IN pls_integer,
4181 i_variable_direction IN varchar2,
4182 i_opr1_level IN pls_integer,
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
4289 ecx_debug.pop(i_method_name);
4290 end if;
4291 raise ecx_utils.PROGRAM_EXIT;
4292 end convert_from_oag_quantity;
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
4317 y varchar2(2000);
4314 i_method_name varchar2(2000) := 'ecx_actions.execute_math_functions';
4315
4316 x 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;
4458 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXECUTE_MATH_FUNCTIONS');
4459 if (l_procedureEnabled) then
4460 ecx_debug.pop(i_method_name);
4461 end if;
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
4501 end if;
4498 if (l_filename is null) or (l_filename = 'NULL')
4499 then
4500 return;
4502
4503 /*start bug 10254870*/
4504 dbms_lob.trim(ecx_utils.g_out_transformed, 0);
4505 transform_xml_with_xslt (i_filename => l_filename,
4506 i_out_transformed => ecx_utils.g_out_transformed);
4507
4508 exception
4509 when others then
4510 raise ecx_utils.program_exit;
4511 end transform_xml_with_xslt;
4512
4513
4514 procedure transform_xml_with_xslt
4515 (
4516 i_filename in varchar2,
4517 i_version in number,
4518 i_application_code in varchar2
4519 )
4520 is
4521 i_method_name varchar2(2000) := 'ecx_actions.transform_xml_with_xslt';
4522
4523 i_stylesheet xslprocessor.Stylesheet;
4524 i_processor xslprocessor.Processor;
4525 i_xmlDocFrag xmlDOM.DOMDocumentFragment;
4526 i_domDocFrag xmlDOM.DOMDocumentFragment;
4527 i_domNode xmlDOM.DOMNode;
4528 i_xslt_dir varchar2(200);
4529 i_fullpath varchar2(200);
4530 i_string varchar2(2000);
4531 l_xslt_payload clob;
4532 l_parser xmlparser.parser;
4533 l_xsl_doc xmldom.DOMDocument;
4534 i_doc xmlDOM.DOMDocument;
4535 i_doc_frag xmlDOM.DOMDocumentFragment;
4536 i_node_type pls_integer;
4537
4538 begin
4539 if (l_procedureEnabled) then
4540 ecx_debug.push(i_method_name);
4541 end if;
4542
4543 if (xmlDOM.isNull(ecx_utils.g_xmldoc)) then
4544 return;
4545 end if;
4546
4547 if(l_statementEnabled) then
4548 ecx_debug.log(l_statement,'i_filename',i_filename,i_method_name);
4549 end if;
4550
4551 if i_filename is null
4552 then
4553 return;
4554 end if;
4555
4556 -- check if the XSLT file is loaded in the DB
4557 begin
4558 select payload
4559 into l_xslt_payload
4560 from ecx_files
4561 where (i_version is null or version = i_version)
4562 and (i_application_code is null or application_code = i_application_code)
4563 and name = i_filename
4564 and type = 'XSLT';
4565 exception
4566 when no_data_found then
4567 null;
4568 when too_many_rows then
4569 -- currently we do not support version and application_code as
4570 -- input parameters for XSLT actions, so for now, this exception
4571 -- means that the API was invoked within the actions code. So, check
4572 -- the file system
4573 if(l_unexpectedEnabled) then
4574 ecx_debug.log(l_unexpected, SQLERRM,i_method_name);
4575 end if;
4576 l_xslt_payload := null;
4577 when others then
4578 ecx_debug.setErrorInfo(2,30,SQLERRM);
4579 raise ecx_utils.program_exit;
4580 end;
4581
4582 if (l_xslt_payload is null)
4583 then
4584 -- xslt file is not loaded in the DB table
4585 -- do the transformation assuming that it is on the file system
4586 if(l_statementEnabled) then
4587 ecx_debug.log(l_statement,'XSLT file not loaded in the DB. Checking the file system...',
4588 i_method_name);
4589 end if;
4590
4591 if (ecx_actions.g_xslt_dir is null) then
4592 --- Check for the Installation Type ( Standalone or Embedded );
4593 if (ecx_utils.g_install_mode is null) then
4594 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
4595 end if;
4596
4597 if ecx_utils.g_install_mode = 'EMBEDDED'
4598 then
4599 i_string := 'begin
4600 fnd_profile.get('||'''ECX_UTL_XSLT_DIR'''||',ecx_actions.g_xslt_dir);
4601 end;';
4602 execute immediate i_string ;
4603 else
4604 ecx_actions.g_xslt_dir:= wf_core.translate('ECX_UTL_XSLT_DIR');
4605 end if;
4606 end if;
4607
4608 i_fullpath := ecx_actions.g_xslt_dir||ecx_utils.getFileSeparator()||i_filename;
4609 if(l_statementEnabled) then
4610 ecx_debug.log(l_statement, 'XSLT Fullpath', i_fullpath,i_method_name);
4611 end if;
4612 l_parser := xmlparser.newParser;
4613 xmlparser.setPreservewhitespace(l_parser,true); -- bug:4953557
4614 xmlparser.parse(l_parser,i_fullpath);
4615 l_xsl_doc := xmlparser.getDocument(l_parser);
4616 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc,i_fullpath);
4617 else
4618 -- payload found in DB,
4619 if(l_statementEnabled) then
4620 ecx_debug.log(l_statement, 'Found XSLT file in the DB',i_method_name);
4621 end if;
4622 -- convert l_xslt_paylod from clob to DOMDocument
4623 l_parser := xmlparser.newParser;
4624 xmlparser.setPreservewhitespace(l_parser,true); -- bug:4953557
4625 xmlparser.parseCLOB(l_parser, l_xslt_payload);
4626 l_xsl_doc := xmlparser.getDocument(l_parser);
4627
4628 -- get the stylesheet
4629 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc, null);
4630 end if;
4631
4632 i_processor := xslprocessor.newProcessor;
4633
4634 -- get the type of the DOMNode
4635 i_node_type := xmlDOM.getNodeType(ecx_utils.g_xmldoc);
4636 if(l_statementEnabled) then
4637 ecx_debug.log(l_statement, 'i_node_type', i_node_type,i_method_name);
4638 end if;
4639
4640 if (i_node_type = xmlDOM.DOCUMENT_NODE)
4641 then
4642 if(l_statementEnabled) then
4643 ecx_debug.log(l_statement, 'Creating Document Object from DOM Node...',i_method_name);
4644 end if;
4645 i_doc := xmlDOM.makeDocument(ecx_utils.g_xmldoc);
4646 if(l_statementEnabled) then
4647 ecx_debug.log(l_statement, 'Before processing XSL',i_method_name);
4648 end if;
4649 i_xmlDocFrag := xslprocessor.processXSL(i_processor, i_stylesheet, i_doc);
4653 if(l_statementEnabled) then
4650
4651 elsif (i_node_type = xmlDOM.DOCUMENT_FRAGMENT_NODE)
4652 then
4654 ecx_debug.log(l_statement,'Creating Document fragment from DOM Node...',i_method_name);
4655 end if;
4656 i_doc_frag := xmlDOM.makeDocumentFragment(ecx_utils.g_xmldoc);
4657 if(l_statementEnabled) then
4658 ecx_debug.log(l_statement, 'Before processing XSL',i_method_name);
4659 end if;
4660 i_xmlDocFrag := xslprocessor.processXSL(i_processor, i_stylesheet, i_doc_frag);
4661 end if;
4662
4663 if(l_statementEnabled) then
4664 ecx_debug.log(l_statement,'XSL processed.Creating Node...',i_method_name);
4665 end if;
4666 i_domNode := xmlDOM.makeNode(i_xmlDocFrag);
4667 if(l_statementEnabled) then
4668 ecx_debug.log(l_statement, 'Node created.',i_method_name);
4669 end if;
4670 ecx_utils.g_xmldoc := i_domNode;
4671
4672 -- free all the used variables
4673
4674 xslprocessor.freeStylesheet(i_stylesheet);
4675 xslprocessor.freeProcessor(i_processor);
4676
4677 if (l_parser.id <> -1)
4678 then
4679 xmlParser.freeParser(l_parser);
4680 end if;
4681 if (not xmldom.isNull(l_xsl_doc))
4682 then
4683 xmldom.freeDocument(l_xsl_doc);
4684 end if;
4685 if (not xmldom.isNull(i_doc))
4686 then
4687 xmldom.freeDocument(i_doc);
4688 end if;
4689
4690 if (not xmldom.isNull(i_doc_frag))
4691 then
4692 xmldom.freeDocFrag(i_doc_frag);
4693 end if;
4694
4695 if (l_procedureEnabled) then
4696 ecx_debug.pop(i_method_name);
4697 end if;
4698 exception
4699 -- Put All DOM Parser Exceptions Here.
4700 when xmlDOM.INDEX_SIZE_ERR then
4701 ecx_debug.setErrorInfo(1,20,SQLERRM);
4702 if(l_unexpectedEnabled) then
4703 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4704 end if;
4705 -- free all the used variables
4706 if (l_parser.id <> -1)
4707 then
4708 xmlParser.freeParser(l_parser);
4709 end if;
4710 if (not xmldom.isNull(l_xsl_doc))
4711 then
4712 xmldom.freeDocument(l_xsl_doc);
4713 end if;
4714 if (l_procedureEnabled) then
4715 ecx_debug.pop(i_method_name);
4716 end if;
4717 raise ecx_utils.program_exit;
4718 when xmlDOM.DOMSTRING_SIZE_ERR then
4719 ecx_debug.setErrorInfo(1,20,SQLERRM);
4720 if(l_unexpectedEnabled) then
4721 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4722 end if;
4723 -- free all the used variables
4724 if (l_parser.id <> -1)
4725 then
4726 xmlParser.freeParser(l_parser);
4727 end if;
4728 if (not xmldom.isNull(l_xsl_doc))
4729 then
4730 xmldom.freeDocument(l_xsl_doc);
4731 end if;
4732 if (l_procedureEnabled) then
4733 ecx_debug.pop(i_method_name);
4734 end if;
4735 raise ecx_utils.program_exit;
4736 when xmlDOM.HIERARCHY_REQUEST_ERR then
4737 ecx_debug.setErrorInfo(1,20,SQLERRM);
4738 if(l_unexpectedEnabled) then
4739 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4740 end if;
4741 -- free all the used variables
4742 if (l_parser.id <> -1)
4743 then
4744 xmlParser.freeParser(l_parser);
4745 end if;
4746 if (not xmldom.isNull(l_xsl_doc))
4747 then
4748 xmldom.freeDocument(l_xsl_doc);
4749 end if;
4750 if (l_procedureEnabled) then
4751 ecx_debug.pop(i_method_name);
4752 end if;
4753 raise ecx_utils.program_exit;
4754 when xmlDOM.WRONG_DOCUMENT_ERR then
4755 ecx_debug.setErrorInfo(1,20,SQLERRM);
4756 if(l_unexpectedEnabled) then
4757 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4758 end if;
4759 -- free all the used variables
4760 if (l_parser.id <> -1)
4761 then
4762 xmlParser.freeParser(l_parser);
4763 end if;
4764 if (not xmldom.isNull(l_xsl_doc))
4765 then
4766 xmldom.freeDocument(l_xsl_doc);
4767 end if;
4768 if (l_procedureEnabled) then
4769 ecx_debug.pop(i_method_name);
4770 end if;
4771 raise ecx_utils.program_exit;
4772 when xmlDOM.INVALID_CHARACTER_ERR then
4773 ecx_debug.setErrorInfo(1,20,SQLERRM);
4774 if(l_unexpectedEnabled) then
4775 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4776 end if;
4777 -- free all the used variables
4778 if (l_parser.id <> -1)
4779 then
4780 xmlParser.freeParser(l_parser);
4781 end if;
4782 if (not xmldom.isNull(l_xsl_doc))
4783 then
4784 xmldom.freeDocument(l_xsl_doc);
4785 end if;
4786 if (l_procedureEnabled) then
4787 ecx_debug.pop(i_method_name);
4788 end if;
4789 raise ecx_utils.program_exit;
4790 when xmlDOM.NO_DATA_ALLOWED_ERR then
4791 ecx_debug.setErrorInfo(1,20,SQLERRM);
4792 if(l_unexpectedEnabled) then
4793 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4794 end if;
4795 -- free all the used variables
4796 if (l_parser.id <> -1)
4797 then
4798 xmlParser.freeParser(l_parser);
4799 end if;
4800 if (not xmldom.isNull(l_xsl_doc))
4801 then
4802 xmldom.freeDocument(l_xsl_doc);
4803 end if;
4804 if (l_procedureEnabled) then
4805 ecx_debug.pop(i_method_name);
4806 end if;
4807 raise ecx_utils.program_exit;
4808 when xmlDOM.No_MODIFICATION_ALLOWED_ERR then
4809 ecx_debug.setErrorInfo(1,20,SQLERRM);
4810 if(l_unexpectedEnabled) then
4811 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4812 end if;
4813 -- free all the used variables
4814 if (l_parser.id <> -1)
4815 then
4816 xmlParser.freeParser(l_parser);
4820 xmldom.freeDocument(l_xsl_doc);
4817 end if;
4818 if (not xmldom.isNull(l_xsl_doc))
4819 then
4821 end if;
4822 if (l_procedureEnabled) then
4823 ecx_debug.pop(i_method_name);
4824 end if;
4825 raise ecx_utils.program_exit;
4826 when xmlDOM.NOT_FOUND_ERR then
4827 ecx_debug.setErrorInfo(1,20,SQLERRM);
4828 if(l_unexpectedEnabled) then
4829 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4830 end if;
4831 -- free all the used variables
4832 if (l_parser.id <> -1)
4833 then
4834 xmlParser.freeParser(l_parser);
4835 end if;
4836 if (not xmldom.isNull(l_xsl_doc))
4837 then
4838 xmldom.freeDocument(l_xsl_doc);
4839 end if;
4840 if (l_procedureEnabled) then
4841 ecx_debug.pop(i_method_name);
4842 end if;
4843 raise ecx_utils.program_exit;
4844 when xmlDOM.NOT_SUPPORTED_ERR then
4845 ecx_debug.setErrorInfo(1,20,SQLERRM);
4846 if(l_unexpectedEnabled) then
4847 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4848 end if;
4849 -- free all the used variables
4850 if (l_parser.id <> -1)
4851 then
4852 xmlParser.freeParser(l_parser);
4853 end if;
4854 if (not xmldom.isNull(l_xsl_doc))
4855 then
4856 xmldom.freeDocument(l_xsl_doc);
4857 end if;
4858 if (l_procedureEnabled) then
4859 ecx_debug.pop(i_method_name);
4860 end if;
4861 raise ecx_utils.program_exit;
4862 when xmlDOM.INUSE_ATTRIBUTE_ERR then
4863 ecx_debug.setErrorInfo(1,20,SQLERRM);
4864 -- free all the used variables
4865 if (l_parser.id <> -1)
4866 then
4867 xmlParser.freeParser(l_parser);
4868 end if;
4869 if (not xmldom.isNull(l_xsl_doc))
4870 then
4871 xmldom.freeDocument(l_xsl_doc);
4872 end if;
4873 if(l_unexpectedEnabled) then
4874 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
4875 end if;
4876 if (l_procedureEnabled) then
4877 ecx_debug.pop(i_method_name);
4878 end if;
4879 raise ecx_utils.program_exit;
4880
4881 WHEN OTHERS THEN
4882 if(l_unexpectedEnabled) then
4883 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.TRANSFORM_XML_WITH_XSLT');
4884 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
4885 end if;
4886 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.TRANSFORM_XML_WITH_XSLT');
4887 -- free all the used variables
4888 if (l_parser.id <> -1)
4889 then
4890 xmlParser.freeParser(l_parser);
4891 end if;
4892 if (not xmldom.isNull(l_xsl_doc))
4893 then
4894 xmldom.freeDocument(l_xsl_doc);
4895 end if;
4896 if (l_procedureEnabled) then
4897 ecx_debug.pop(i_method_name);
4898 end if;
4899 raise ecx_utils.PROGRAM_EXIT;
4900 end transform_xml_with_xslt;
4901
4902 /**
4903 Executes the Data from the ECX_TRAN_STAGE_DATA for a given Stage and Level.
4904 **/
4905 procedure execute_stage_data (
4906 i_stage IN pls_integer,
4907 i_level IN pls_integer,
4908 i_direction IN varchar2 ) IS
4909
4910 i_method_name varchar2(2000) := 'ecx_actions.execute_stage_data';
4911
4912 /* Cursor to get all the Level 0 Stack Data */
4913 cursor stack_data is
4914 select variable_name,
4915 variable_value,
4916 variable_direction,
4917 data_type datatype
4918 from ecx_tran_stage_data ets
4919 where ets.map_id = ecx_utils.g_map_id
4920 and variable_level = 0
4921 and action_type = 10;
4922
4923 i_counter pls_integer:= 0;
4924 /** variables for evaluating the condition **/
4925
4926 i_var1 varchar2(4000);
4927 i_var2 varchar2(4000);
4928 i_val1 varchar2(4000);
4929 i_val2 varchar2(4000);
4930
4931 i_vartype1 pls_integer;
4932 i_valtype1 pls_integer;
4933 i_vartype2 pls_integer;
4934 i_valtype2 pls_integer;
4935 stack_var boolean;
4936 stack_pos pls_integer;
4937 i_date date;
4938 i_number number;
4939
4940 condition_flag boolean := true;
4941 /*bug 10254870*/
4942 l_counter_xslt pls_integer := 0;
4943 begin
4944 if (l_procedureEnabled) then
4945 ecx_debug.push(i_method_name);
4946 end if;
4947 if(l_statementEnabled) then
4948 ecx_debug.log(l_statement,'i_stage',i_stage,i_method_name);
4949 ecx_debug.log(l_statement,'i_level',i_level,i_method_name);
4950 ecx_debug.log(l_statement,'i_direction',i_direction,i_method_name);
4951 end if;
4952
4953 if i_stage = 10 then
4954
4955 if (i_level = 0) then
4956
4957 ecx_actions.g_server_timezone := null;
4958 ecx_actions.g_xslt_dir := null;
4959
4960 ecx_utils.g_stack.DELETE;
4961
4962 for get_stack_data in stack_data loop
4963 i_counter := i_counter + 1;
4964 ecx_utils.g_stack(i_counter).variable_name := get_stack_data.variable_name;
4965 ecx_utils.g_stack(i_counter).variable_value := get_stack_data.variable_value;
4966 ecx_utils.g_stack(i_counter).data_type := get_stack_data.datatype;
4967 end loop;
4968
4969 if(l_statementEnabled) then
4970 dump_stack;
4971 end if;
4972
4973 end if;
4974 end if;
4975
4976 if(ecx_utils.g_stage_data.count <> 0)
4977 then
4978 FOR i in ecx_utils.g_stage_data.first..ecx_utils.g_stage_data.last
4979 loop
4980 exit when i = ecx_utils.g_stage_data.count;
4981 if (ecx_utils.g_stage_data(i).stage = i_stage
4985
4982 and ecx_utils.g_stage_data(i).level = i_level and
4983 ecx_utils.g_stage_data(i).object_direction = i_direction)
4984 then
4986 /**
4987 Check for Condition First. If Defined , evaluate it first and then execute an action.
4988 If the condition is not defined , execute the action i.e. action without condition.
4989 Initialize the values of the variables.
4990 Word NULL will be used to specify NULL values for the Variables. It will be reserved for XML gateway.
4991 **/
4992 i_var1 :=null;
4993 i_var2 :=null;
4994 i_val1 :=null;
4995 i_val2 :=null;
4996
4997 /** Set Default Datatype to varchar2 **/
4998 i_vartype1 :=1;
4999 i_valtype1 :=1;
5000 i_vartype2 :=1;
5001 i_valtype2 :=1;
5002 condition_flag :=true;
5003
5004 if ecx_utils.g_stage_data(i).cond_operator1 is not null
5005 then
5006 /** Condition based on one variable **/
5007 -- Find variable Values
5008 -- Look for Default first. If null , get it from the variable
5009
5010 i_var1 := ecx_utils.g_stage_data(i).cond_var1_constant;
5011 if i_var1 is null
5012 then
5013 get_var_attr (
5014 ecx_utils.g_stage_data(i).cond_var1_level,
5015 ecx_utils.g_stage_data(i).cond_var1_name,
5016 ecx_utils.g_stage_data(i).cond_var1_direction,
5017 ecx_utils.g_stage_data(i).cond_var1_pos,
5018 i_var1,
5019 stack_var,
5020 stack_pos);
5021
5022 /** Get the Data Type for the Left hand side variables **/
5023 if ecx_utils.g_stage_data(i).cond_var1_direction = 'G'
5024 then
5025 i_vartype1 := ecx_utils.g_stack(stack_pos).data_type;
5026 else
5027 if ecx_utils.g_stage_data(i).cond_var1_direction = 'S'
5028 then
5029 i_vartype1 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_var1_pos).data_type;
5030 else
5031 i_vartype1 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_var1_pos).data_type;
5032 end if;
5033 end if;
5034 if(l_statementEnabled) then
5035 ecx_debug.log(l_statement,'i_vartype1',i_vartype1,i_method_name);
5036 end if;
5037 else
5038 if i_var1 = 'NULL'
5039 then
5040 i_var1 :=null;
5041 else
5042 /** Try to find out the data type **/
5043 /** Attempt for date **/
5044 begin
5045 i_date := to_date(i_var1,'YYYYMMDD HH24MISS');
5046 if(l_statementEnabled) then
5047 ecx_debug.log(l_statement,'i_date',i_date,i_method_name);
5048 end if;
5049 i_vartype1 :=12;
5050 exception
5051 when others then
5052 if(l_unexpectedEnabled) then
5053 ecx_debug.log(l_unexpected,'i_date','Not a date',i_method_name);
5054 end if;
5055
5056 /** Attempt for number **/
5057 begin
5058 i_number := to_number(i_var1);
5059 if(l_unexpectedEnabled) then
5060 ecx_debug.log(l_unexpected,'i_number',i_number,i_method_name);
5061 end if;
5062 i_vartype1 :=2;
5063 exception
5064 when others then
5065 if(l_unexpectedEnabled) then
5066 ecx_debug.log(l_unexpected,'i_number','Not a number',i_method_name);
5067 end if;
5068 end;
5069 end;
5070 end if;
5071 end if;
5072
5073
5074
5075
5076 if ecx_utils.g_stage_data(i).cond_operator1 not in ('6','7')
5077 then
5078 i_val1 := ecx_utils.g_stage_data(i).cond_val1_constant;
5079 if i_val1 is null
5080 then
5081 get_var_attr (
5082 ecx_utils.g_stage_data(i).cond_val1_level,
5083 ecx_utils.g_stage_data(i).cond_val1_name,
5084 ecx_utils.g_stage_data(i).cond_val1_direction,
5085 ecx_utils.g_stage_data(i).cond_val1_pos,
5086 i_val1,
5087 stack_var,
5088 stack_pos);
5089 /** Get the Data Type for the right hand side variables **/
5090 if ecx_utils.g_stage_data(i).cond_val1_direction = 'G'
5091 then
5092 i_valtype1 := ecx_utils.g_stack(stack_pos).data_type;
5093 else
5094 if ecx_utils.g_stage_data(i).cond_val1_direction = 'S'
5095 then
5096 i_valtype1 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_val1_pos).data_type;
5097 else
5098 i_valtype1 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_val1_pos).data_type;
5099 end if;
5100 end if;
5101 if(l_statementEnabled) then
5102 ecx_debug.log(l_statement,'i_valtype1',i_valtype1,i_method_name);
5103 end if;
5104 else
5105 if i_val1 = 'NULL'
5106 then
5107 i_val1 :=null;
5108 else
5109 /** Try to find out the data type **/
5110 /** Attempt for date **/
5111 begin
5112 i_date := to_date(i_val1,'YYYYMMDD HH24MISS');
5113 if(l_statementEnabled) then
5114 ecx_debug.log(l_statement,'i_date',i_date,i_method_name);
5115 end if;
5116 i_valtype1 :=12;
5117 exception
5118 when others then
5119 if(l_unexpectedEnabled) then
5120 ecx_debug.log(l_unexpected,'i_date','Not a date',i_method_name);
5121 end if;
5122 /** Attempt for number **/
5123 begin
5124 i_number := to_number(i_val1);
5125 if(l_unexpectedEnabled) then
5126 ecx_debug.log(l_unexpected,'i_number',i_number,i_method_name);
5127 end if;
5128 i_valtype1 :=2;
5132 ecx_debug.log(l_unexpected,'i_number','Not a number',
5129 exception
5130 when others then
5131 if(l_unexpectedEnabled) then
5133 i_method_name);
5134 end if;
5135
5136 end;
5137 end;
5138 end if;
5139 end if;
5140
5141 end if;
5142 end if;
5143
5144 if ecx_utils.g_stage_data(i).cond_logical_operator is not null
5145 then
5146
5147 /** Condition based on two variables **/
5148 i_var2 := ecx_utils.g_stage_data(i).cond_var2_constant;
5149 if i_var2 is null
5150 then
5151 get_var_attr (
5152 ecx_utils.g_stage_data(i).cond_var2_level,
5153 ecx_utils.g_stage_data(i).cond_var2_name,
5154 ecx_utils.g_stage_data(i).cond_var2_direction,
5155 ecx_utils.g_stage_data(i).cond_var2_pos,
5156 i_var2,
5157 stack_var,
5158 stack_pos);
5159 end if;
5160
5161
5162 if i_var2 = 'NULL'
5163 then
5164 i_var2 :=null;
5165 end if;
5166
5167 /** Get the Data Type for the Left hand side variables **/
5168 if ecx_utils.g_stage_data(i).cond_var2_direction = 'G'
5169 then
5170 i_vartype2 := ecx_utils.g_stack(stack_pos).data_type;
5171 else
5172 if ecx_utils.g_stage_data(i).cond_var2_direction = 'S'
5173 then
5174 i_vartype2 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_var2_pos).data_type;
5175 else
5176 i_vartype2 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_var2_pos).data_type;
5177 end if;
5178 end if;
5179 if(l_statementEnabled) then
5180 ecx_debug.log(l_statement,'i_vartype2',i_vartype2,i_method_name);
5181 end if;
5182
5183
5184 if ecx_utils.g_stage_data(i).cond_operator1 not in ('6','7')
5185 then
5186 i_val2 := ecx_utils.g_stage_data(i).cond_val2_constant;
5187 if i_val2 is null
5188 then
5189 get_var_attr (
5190 ecx_utils.g_stage_data(i).cond_val2_level,
5191 ecx_utils.g_stage_data(i).cond_val2_name,
5192 ecx_utils.g_stage_data(i).cond_val2_direction,
5193 ecx_utils.g_stage_data(i).cond_val2_pos,
5194 i_val2,
5195 stack_var,
5196 stack_pos);
5197 end if;
5198
5199 if i_val2 = 'NULL'
5200 then
5201 i_val2 :=null;
5202 end if;
5203
5204 /** Get the Data Type for the right hand side variables **/
5205 if ecx_utils.g_stage_data(i).cond_val2_direction = 'G'
5206 then
5207 i_valtype2 := ecx_utils.g_stack(stack_pos).data_type;
5208 else
5209 if ecx_utils.g_stage_data(i).cond_val2_direction = 'S'
5210 then
5211 i_valtype2 := ecx_utils.g_source(ecx_utils.g_stage_data(i).cond_val2_pos).data_type;
5212 else
5213 i_valtype2 := ecx_utils.g_target(ecx_utils.g_stage_data(i).cond_val2_pos).data_type;
5214 end if;
5215 end if;
5216 if(l_statementEnabled) then
5217 ecx_debug.log(l_statement,'i_valtype2',i_valtype2,i_method_name);
5218 end if;
5219
5220 end if;
5221 end if;
5222
5223 /** Now check the condition **/
5224 if (ecx_utils.g_stage_data(i).cond_operator1 is not null
5225 or
5226 ecx_utils.g_stage_data(i).cond_logical_operator is not null
5227 )
5228 then
5229 condition_flag := ecx_conditions.check_condition
5230 (
5231 ecx_utils.g_stage_data(i).cond_logical_operator,
5232 ecx_utils.g_stage_data(i).cond_operator1,
5233 i_var1,
5234 i_vartype1,
5235 i_val1,
5236 i_valtype1,
5237 ecx_utils.g_stage_data(i).cond_operator2,
5238 i_var2,
5239 i_vartype2,
5240 i_val2,
5241 i_valtype2
5242 );
5243 else
5244 /** No Condition Found **/
5245 condition_flag :=true;
5246 end if;
5247
5248 if ( condition_flag )
5249 then
5250
5251 /** Not required anymore
5252 if ecx_utils.g_stage_data(i).action_type = ASSIGN_DEFAULT then
5253 assign_default_to_variables
5254 (
5255 ecx_utils.g_stage_data(i).variable_level,
5256 ecx_utils.g_stage_data(i).variable_name,
5260 );
5257 ecx_utils.g_stage_data(i).variable_direction,
5258 ecx_utils.g_stage_data(i).variable_pos,
5259 ecx_utils.g_stage_data(i).default_value
5261 **/
5262
5263 if ecx_utils.g_stage_data(i).action_type = ASSIGN_PRE_DEFINED then
5264 assign_pre_defined_variables
5265 (
5266 ecx_utils.g_stage_data(i).variable_level,
5267 ecx_utils.g_stage_data(i).variable_name,
5271 ecx_utils.g_stage_data(i).operand1_name,
5268 ecx_utils.g_stage_data(i).variable_direction,
5269 ecx_utils.g_stage_data(i).variable_pos,
5270 ecx_utils.g_stage_data(i).operand1_level,
5272 ecx_utils.g_stage_data(i).operand1_direction,
5273 ecx_utils.g_stage_data(i).operand1_pos,
5274 ecx_utils.g_stage_data(i).operand1_constant
5275 );
5276
5277 elsif ecx_utils.g_stage_data(i).action_type = ASSIGN_NEXTVALUE then
5278 assign_nextval_from_sequence
5279 (
5280 ecx_utils.g_stage_data(i).variable_level,
5281 ecx_utils.g_stage_data(i).variable_name,
5282 ecx_utils.g_stage_data(i).variable_direction,
5283 ecx_utils.g_stage_data(i).variable_pos,
5284 ecx_utils.g_stage_data(i).sequence_name
5285 );
5286
5287 elsif ecx_utils.g_stage_data(i).action_type = ASSIGN_FUNCVAL then
5288 assign_function_value
5289 (
5290 ecx_utils.g_stage_data(i).variable_level,
5291 ecx_utils.g_stage_data(i).variable_name,
5292 ecx_utils.g_stage_data(i).variable_direction,
5293 ecx_utils.g_stage_data(i).variable_pos,
5294 ecx_utils.g_stage_data(i).function_name
5295 );
5296
5297 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGDATE then
5298 convert_to_oag_date
5299 (
5300 ecx_utils.g_stage_data(i).variable_level,
5301 ecx_utils.g_stage_data(i).variable_name,
5302 ecx_utils.g_stage_data(i).variable_pos,
5303 ecx_utils.g_stage_data(i).variable_direction,
5304 ecx_utils.g_stage_data(i).operand1_level,
5305 ecx_utils.g_stage_data(i).operand1_name,
5306 ecx_utils.g_stage_data(i).operand1_pos,
5307 ecx_utils.g_stage_data(i).operand1_direction,
5308 ecx_utils.g_stage_data(i).operand1_constant
5309 );
5310
5311 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGOPERAMT then
5312 convert_to_oag_operamt(
5313 ecx_utils.g_stage_data(i).variable_level,
5314 ecx_utils.g_stage_data(i).variable_name,
5315 ecx_utils.g_stage_data(i).variable_pos,
5316 ecx_utils.g_stage_data(i).variable_direction,
5317 ecx_utils.g_stage_data(i).operand1_level,
5318 ecx_utils.g_stage_data(i).operand1_name,
5319 ecx_utils.g_stage_data(i).operand1_pos,
5320 ecx_utils.g_stage_data(i).operand1_direction,
5321 ecx_utils.g_stage_data(i).operand1_constant,
5322 ecx_utils.g_stage_data(i).operand2_level,
5323 ecx_utils.g_stage_data(i).operand2_name,
5324 ecx_utils.g_stage_data(i).operand2_pos,
5325 ecx_utils.g_stage_data(i).operand2_direction,
5326 ecx_utils.g_stage_data(i).operand2_constant,
5327 ecx_utils.g_stage_data(i).operand3_level,
5328 ecx_utils.g_stage_data(i).operand3_name,
5329 ecx_utils.g_stage_data(i).operand3_pos,
5330 ecx_utils.g_stage_data(i).operand3_direction,
5331 ecx_utils.g_stage_data(i).operand3_constant
5332 );
5333 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGAMT then
5334 convert_to_oag_amt(
5335 ecx_utils.g_stage_data(i).variable_level,
5336 ecx_utils.g_stage_data(i).variable_name,
5337 ecx_utils.g_stage_data(i).variable_pos,
5338 ecx_utils.g_stage_data(i).variable_direction,
5339 ecx_utils.g_stage_data(i).operand1_level,
5340 ecx_utils.g_stage_data(i).operand1_name,
5341 ecx_utils.g_stage_data(i).operand1_pos,
5342 ecx_utils.g_stage_data(i).operand1_direction,
5343 ecx_utils.g_stage_data(i).operand1_constant,
5344 ecx_utils.g_stage_data(i).operand2_level,
5345 ecx_utils.g_stage_data(i).operand2_name,
5346 ecx_utils.g_stage_data(i).operand2_pos,
5347 ecx_utils.g_stage_data(i).operand2_direction,
5348 ecx_utils.g_stage_data(i).operand2_constant,
5349 ecx_utils.g_stage_data(i).operand3_level,
5350 ecx_utils.g_stage_data(i).operand3_name,
5351 ecx_utils.g_stage_data(i).operand3_pos,
5352 ecx_utils.g_stage_data(i).operand3_direction,
5353 ecx_utils.g_stage_data(i).operand3_constant
5354 );
5355
5356 elsif ecx_utils.g_stage_data(i).action_type = CONV_TO_OAGQUANT then
5357 convert_to_oag_quantity(
5358 ecx_utils.g_stage_data(i).variable_level,
5359 ecx_utils.g_stage_data(i).variable_name,
5360 ecx_utils.g_stage_data(i).variable_pos,
5361 ecx_utils.g_stage_data(i).variable_direction,
5362 ecx_utils.g_stage_data(i).operand1_level,
5363 ecx_utils.g_stage_data(i).operand1_name,
5364 ecx_utils.g_stage_data(i).operand1_pos,
5365 ecx_utils.g_stage_data(i).operand1_direction,
5366 ecx_utils.g_stage_data(i).operand1_constant,
5367 ecx_utils.g_stage_data(i).operand2_level,
5368 ecx_utils.g_stage_data(i).operand2_name,
5369 ecx_utils.g_stage_data(i).operand2_pos,
5370 ecx_utils.g_stage_data(i).operand2_direction,
5371 ecx_utils.g_stage_data(i).operand2_constant
5372 );
5373
5374 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGDATE then
5375 convert_from_oag_date
5376 (
5377 ecx_utils.g_stage_data(i).variable_level,
5378 ecx_utils.g_stage_data(i).variable_name,
5379 ecx_utils.g_stage_data(i).variable_pos,
5380 ecx_utils.g_stage_data(i).variable_direction,
5381 ecx_utils.g_stage_data(i).operand1_level,
5382 ecx_utils.g_stage_data(i).operand1_name,
5383 ecx_utils.g_stage_data(i).operand1_pos,
5384 ecx_utils.g_stage_data(i).operand1_direction,
5385 ecx_utils.g_stage_data(i).operand1_constant
5386 );
5387
5388 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGOPERAMT then
5389 convert_from_oag_operamt(
5390 ecx_utils.g_stage_data(i).variable_level,
5391 ecx_utils.g_stage_data(i).variable_name,
5392 ecx_utils.g_stage_data(i).variable_pos,
5396 ecx_utils.g_stage_data(i).operand1_pos,
5393 ecx_utils.g_stage_data(i).variable_direction,
5394 ecx_utils.g_stage_data(i).operand1_level,
5395 ecx_utils.g_stage_data(i).operand1_name,
5397 ecx_utils.g_stage_data(i).operand1_direction,
5398 ecx_utils.g_stage_data(i).operand1_constant,
5399 ecx_utils.g_stage_data(i).operand2_level,
5400 ecx_utils.g_stage_data(i).operand2_name,
5401 ecx_utils.g_stage_data(i).operand2_pos,
5402 ecx_utils.g_stage_data(i).operand2_direction,
5403 ecx_utils.g_stage_data(i).operand2_constant,
5404 ecx_utils.g_stage_data(i).operand3_level,
5405 ecx_utils.g_stage_data(i).operand3_name,
5406 ecx_utils.g_stage_data(i).operand3_pos,
5407 ecx_utils.g_stage_data(i).operand3_direction,
5408 ecx_utils.g_stage_data(i).operand3_constant
5409 );
5410 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGAMT then
5411 convert_from_oag_amt(
5412 ecx_utils.g_stage_data(i).variable_level,
5413 ecx_utils.g_stage_data(i).variable_name,
5414 ecx_utils.g_stage_data(i).variable_pos,
5415 ecx_utils.g_stage_data(i).variable_direction,
5416 ecx_utils.g_stage_data(i).operand1_level,
5417 ecx_utils.g_stage_data(i).operand1_name,
5418 ecx_utils.g_stage_data(i).operand1_pos,
5419 ecx_utils.g_stage_data(i).operand1_direction,
5420 ecx_utils.g_stage_data(i).operand1_constant,
5421 ecx_utils.g_stage_data(i).operand2_level,
5422 ecx_utils.g_stage_data(i).operand2_name,
5423 ecx_utils.g_stage_data(i).operand2_pos,
5424 ecx_utils.g_stage_data(i).operand2_direction,
5425 ecx_utils.g_stage_data(i).operand2_constant,
5426 ecx_utils.g_stage_data(i).operand3_level,
5427 ecx_utils.g_stage_data(i).operand3_name,
5428 ecx_utils.g_stage_data(i).operand3_pos,
5429 ecx_utils.g_stage_data(i).operand3_direction,
5430 ecx_utils.g_stage_data(i).operand3_constant
5431 );
5432
5433 elsif ecx_utils.g_stage_data(i).action_type = CONV_FROM_OAGQUANT then
5434 convert_from_oag_quantity(
5435 ecx_utils.g_stage_data(i).variable_level,
5436 ecx_utils.g_stage_data(i).variable_name,
5437 ecx_utils.g_stage_data(i).variable_pos,
5438 ecx_utils.g_stage_data(i).variable_direction,
5439 ecx_utils.g_stage_data(i).operand1_level,
5440 ecx_utils.g_stage_data(i).operand1_name,
5441 ecx_utils.g_stage_data(i).operand1_pos,
5442 ecx_utils.g_stage_data(i).operand1_direction,
5443 ecx_utils.g_stage_data(i).operand1_constant,
5444 ecx_utils.g_stage_data(i).operand2_level,
5445 ecx_utils.g_stage_data(i).operand2_name,
5446 ecx_utils.g_stage_data(i).operand2_pos,
5447 ecx_utils.g_stage_data(i).operand2_direction,
5448 ecx_utils.g_stage_data(i).operand2_constant
5449 );
5450
5451 elsif ecx_utils.g_stage_data(i).action_type = INSERT_INTO_OPEN_INTERFACE then
5452 insert_level_into_table (i_level);
5453
5454 /** Removed the call as it is being called in the initialization of source itself **/
5455 --elsif ecx_utils.g_stage_data(i).action_type = APPEND_WHERECLAUSE then
5456 --append_clause
5457 --(
5458 --ecx_utils.g_stage_data(i).level,
5459 --ecx_utils.g_stage_data(i).clause
5460 --);
5461
5462 elsif ecx_utils.g_stage_data(i).action_type = EXEC_PROCEDURES then
5463 execute_proc
5464 (
5465 ecx_utils.g_stage_data(i).transtage_id,
5466 ecx_utils.g_stage_data(i).custom_procedure_name
5467 );
5468
5469 elsif ecx_utils.g_stage_data(i).action_type = EXITPROGRAM then
5470 exit_program;
5471
5472 elsif ecx_utils.g_stage_data(i).action_type = SEND_ERROR then
5473 send_err
5474 (
5475 ecx_utils.g_stage_data(i).operand1_level,
5476 ecx_utils.g_stage_data(i).operand1_name,
5477 ecx_utils.g_stage_data(i).operand1_direction,
5478 ecx_utils.g_stage_data(i).operand1_pos,
5479 ecx_utils.g_stage_data(i).operand1_constant,
5480 ecx_utils.g_stage_data(i).operand2_level,
5481 ecx_utils.g_stage_data(i).operand2_name,
5482 ecx_utils.g_stage_data(i).operand2_direction,
5483 ecx_utils.g_stage_data(i).operand2_pos,
5484 ecx_utils.g_stage_data(i).operand2_constant
5485 );
5486
5487 elsif ecx_utils.g_stage_data(i).action_type = API_RETURN_CODE then
5488 get_api_retcode
5489 (
5490 ecx_utils.g_stage_data(i).variable_level,
5491 ecx_utils.g_stage_data(i).variable_name,
5492 ecx_utils.g_stage_data(i).variable_direction,
5493 ecx_utils.g_stage_data(i).variable_pos,
5494 ecx_utils.g_stage_data(i).default_value,
5495 ecx_utils.g_stage_data(i).operand1_level,
5496 ecx_utils.g_stage_data(i).operand1_name,
5497 ecx_utils.g_stage_data(i).operand1_direction,
5498 ecx_utils.g_stage_data(i).operand1_pos,
5499 ecx_utils.g_stage_data(i).function_name
5500 );
5501 elsif ecx_utils.g_stage_data(i).action_type = CONCAT_VAR then
5502 concat_variables
5503 (
5504 ecx_utils.g_stage_data(i).variable_level,
5505 ecx_utils.g_stage_data(i).variable_name,
5506 ecx_utils.g_stage_data(i).variable_direction,
5507 ecx_utils.g_stage_data(i).variable_pos,
5508 ecx_utils.g_stage_data(i).operand1_level,
5509 ecx_utils.g_stage_data(i).operand1_name,
5510 ecx_utils.g_stage_data(i).operand1_direction,
5511 ecx_utils.g_stage_data(i).operand1_pos,
5512 ecx_utils.g_stage_data(i).operand1_constant,
5513 ecx_utils.g_stage_data(i).operand2_level,
5514 ecx_utils.g_stage_data(i).operand2_name,
5515 ecx_utils.g_stage_data(i).operand2_direction,
5516 ecx_utils.g_stage_data(i).operand2_pos,
5520 elsif ecx_utils.g_stage_data(i).action_type = SUBSTR_VAR then
5517 ecx_utils.g_stage_data(i).operand2_constant
5518 );
5519
5521 substr_variables
5522 (
5523 ecx_utils.g_stage_data(i).variable_level,
5524 ecx_utils.g_stage_data(i).variable_name,
5525 ecx_utils.g_stage_data(i).variable_direction,
5526 ecx_utils.g_stage_data(i).variable_pos,
5527 ecx_utils.g_stage_data(i).operand1_level,
5528 ecx_utils.g_stage_data(i).operand1_name,
5529 ecx_utils.g_stage_data(i).operand1_direction,
5530 ecx_utils.g_stage_data(i).operand1_pos,
5531 ecx_utils.g_stage_data(i).operand1_constant,
5532 ecx_utils.g_stage_data(i).operand2_level,
5533 ecx_utils.g_stage_data(i).operand2_name,
5534 ecx_utils.g_stage_data(i).operand2_direction,
5535 ecx_utils.g_stage_data(i).operand2_pos,
5536 ecx_utils.g_stage_data(i).operand2_constant,
5537 ecx_utils.g_stage_data(i).operand3_level,
5538 ecx_utils.g_stage_data(i).operand3_name,
5539 ecx_utils.g_stage_data(i).operand3_direction,
5540 ecx_utils.g_stage_data(i).operand3_pos,
5541 ecx_utils.g_stage_data(i).operand3_constant
5542 );
5543
5544 elsif ecx_utils.g_stage_data(i).action_type in ( 4000,4010,4020,4030)
5545 then
5546 execute_math_functions
5547 (
5548 ecx_utils.g_stage_data(i).action_type,
5552 ecx_utils.g_stage_data(i).variable_direction,
5549 ecx_utils.g_stage_data(i).variable_level,
5550 ecx_utils.g_stage_data(i).variable_name,
5551 ecx_utils.g_stage_data(i).variable_pos,
5553 ecx_utils.g_stage_data(i).operand1_level,
5554 ecx_utils.g_stage_data(i).operand1_name,
5555 ecx_utils.g_stage_data(i).operand1_pos,
5556 ecx_utils.g_stage_data(i).operand1_direction,
5557 ecx_utils.g_stage_data(i).operand1_constant,
5558 ecx_utils.g_stage_data(i).operand2_level,
5559 ecx_utils.g_stage_data(i).operand2_name,
5560 ecx_utils.g_stage_data(i).operand2_pos,
5561 ecx_utils.g_stage_data(i).operand2_direction,
5562 ecx_utils.g_stage_data(i).operand2_constant
5563 );
5564 elsif ecx_utils.g_stage_data(i).action_type = XSLT_TRANSFORM then
5565 transform_xml_with_xslt
5566 (
5567 ecx_utils.g_stage_data(i).default_value,
5568 ecx_utils.g_stage_data(i).operand1_level,
5569 ecx_utils.g_stage_data(i).operand1_name,
5570 ecx_utils.g_stage_data(i).operand1_pos,
5571 ecx_utils.g_stage_data(i).operand1_direction,
5572 ecx_utils.g_stage_data(i).operand1_constant
5573 );
5574 /*start bug 10254870*/
5575 l_counter_xslt := l_counter_xslt+1;
5576 /*
5577 * If xslt transformation is defined more that once for a map then writing transformed xml back
5578 * to g_xml_doc so that same can be used during next transformation
5579 */
5580 if(ecx_utils.g_cnt_xslt_defined > 1 and l_counter_xslt <> ecx_utils.g_cnt_xslt_defined) then
5581 ecx_utils.clobToDomNode(ecx_utils.g_out_transformed,ecx_utils.g_xmldoc);
5582 end if;
5583 /*end bug 10254870*/
5584 elsif ecx_utils.g_stage_data(i).action_type = GET_ADDRESS_ID
5585 then
5586 derive_address_id
5587 (
5588 ecx_utils.g_stage_data(i).variable_level,
5589 ecx_utils.g_stage_data(i).variable_name,
5590 ecx_utils.g_stage_data(i).variable_pos,
5591 ecx_utils.g_stage_data(i).variable_direction,
5592 ecx_utils.g_stage_data(i).operand1_level,
5593 ecx_utils.g_stage_data(i).operand1_name,
5594 ecx_utils.g_stage_data(i).operand1_pos,
5595 ecx_utils.g_stage_data(i).operand1_direction,
5596 ecx_utils.g_stage_data(i).operand1_constant,
5597 ecx_utils.g_stage_data(i).operand2_constant,
5598 ecx_utils.g_stage_data(i).operand3_level,
5599 ecx_utils.g_stage_data(i).operand3_name,
5600 ecx_utils.g_stage_data(i).operand3_pos,
5601 ecx_utils.g_stage_data(i).operand3_direction
5602 );
5603 end if;
5604
5605 end if; --- Condition Flag
5606
5607 end if; -- Stage Check
5608 end loop;
5609 end if;
5610 if(l_statementEnabled) then
5611 dump_stack;
5612 end if;
5613
5614 if (l_procedureEnabled) then
5615 ecx_debug.pop(i_method_name);
5616 end if;
5617
5618 EXCEPTION
5619 WHEN ecx_utils.PROGRAM_EXIT then
5620 if (l_procedureEnabled) then
5621 ecx_debug.pop(i_method_name);
5622 end if;
5623 raise;
5624
5625 WHEN OTHERS THEN
5626 if(l_unexpectedEnabled) then
5627 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
5628 'PROGRESS_LEVEL','ECX_ACTIONS.EXECUTE_STAGE_DATA');
5632 if (l_procedureEnabled) then
5629 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
5630 end if;
5631 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.EXECUTE_STAGE_DATA');
5633 ecx_debug.pop(i_method_name);
5634 end if;
5635 raise ecx_utils.PROGRAM_EXIT;
5636
5637
5638 end execute_stage_data;
5639
5640 /*
5641 Gets the timezone offset for the DB server timezone based on the date
5642 */
5643 Function getTimeZoneOffset (year number, month number, day number, hour number,
5644 minute number, second number, timezone varchar2)
5645 return number
5646 is language java
5647 name 'oracle.apps.ecx.util.TimeZoneOffset.getOffset(int, int, int, int, int, int,
5648 java.lang.String) returns float';
5649
5650
5651 Procedure get_clob(clobValue in clob , value in Varchar2 , clobOut out nocopy clob) as
5652 i_method_name varchar2(2000) := 'ecx_actions.get_clob';
5653 begin
5654
5655 if (l_procedureEnabled) then
5656 ecx_debug.push(i_method_name);
5657 end if;
5658 if (clobValue is not null) or
5659 (value is not null ) Then
5660 dbms_lob.createtemporary(clobOut,true,dbms_lob.session);
5661 if (value is not null) then
5662 dbms_lob.write(clobOut ,length(value),1,value );
5663 elsif (clobValue is not null) then
5664 clobOut := clobValue;
5665 end if;
5666 end if;
5667 if (l_procedureEnabled) then
5668 ecx_debug.pop(i_method_name);
5669 end if;
5670 EXCEPTION
5671 WHEN ecx_utils.PROGRAM_EXIT then
5672 if (l_procedureEnabled) then
5673 ecx_debug.pop(i_method_name);
5674 end if;
5675 raise;
5676
5677 WHEN OTHERS THEN
5678 if(l_unexpectedEnabled) then
5679 ecx_debug.log(l_unexpected,'ECX', 'ECX_PROGRAM_ERROR',i_method_name,
5680 'PROGRESS_LEVEL',
5681 'ECX_ACTIONS.GET_CLOB');
5682 end if;
5683 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_CLOB');
5684 if (l_procedureEnabled) then
5685 ecx_debug.pop(i_method_name);
5686 end if;
5687 raise ecx_utils.PROGRAM_EXIT;
5688 end;
5689
5690 procedure get_varchar(clobValue in clob , value in Varchar2 , valueOut out nocopy varchar2) as
5691 i_method_name varchar2(2000) := 'ecx_actions.get_varchar';
5692 begin
5693 if (l_procedureEnabled) then
5694 ecx_debug.push(i_method_name);
5695 end if;
5696 if (value is not null) then
5697 valueOut := substr(value , 1, ecx_utils.G_VARCHAR_LEN);
5698 elsif (clobValue is not null) then
5699 valueOut := dbms_lob.substr(clobValue,ecx_utils.G_VARCHAR_LEN,1);
5700 end if;
5701 if (l_procedureEnabled) then
5702 ecx_debug.pop(i_method_name);
5703 end if;
5704 EXCEPTION
5705 WHEN ecx_utils.PROGRAM_EXIT then
5706 if (l_procedureEnabled) then
5707 ecx_debug.pop(i_method_name);
5708 end if;
5709 raise;
5710 WHEN OTHERS THEN
5711 if(l_unexpectedEnabled) then
5712 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.GET_VARCHAR');
5713 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
5714 end if;
5715 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_VARCHAR');
5716 if (l_procedureEnabled) then
5720
5717 ecx_debug.pop(i_method_name);
5718 end if;
5719 raise ecx_utils.PROGRAM_EXIT;
5721 end;
5722
5723 procedure delete_doctype as
5724 begin
5725 null;
5726 end;
5727
5728 PROCEDURE get_xml_fragment
5729 ( proc_name IN varchar2,
5730 xml_fragment OUT NOCOPY varchar2
5731 ) as
5735 temp_parser xmlparser.parser;
5732 i_method_name varchar2(2000) := 'ecx_actions.get_xml_fragment';
5733 proc_call varchar2(32767);
5734 temp_xml varchar2(32767);
5736 v_name varchar2(32767);
5737 v_value varchar2(32767);
5738 BEGIN
5739 if (l_procedureEnabled) then
5740 ecx_debug.push(i_method_name);
5741 end if;
5742
5743
5744 -- Initialize the event if it is not already initialized.
5745 if (ecx_utils.g_event is null) then
5746 wf_event_t.initialize(ecx_utils.g_event);
5747 end if;
5748
5749 -- Add all global variables as parameters to the event.
5750 for k in 1..ecx_utils.g_stack.count
5751 loop
5752 v_name := ecx_utils.g_stack(k).variable_name;
5753 v_value := ecx_utils.g_stack(k).variable_value;
5754 ecx_utils.g_event.addparametertolist
5755 ( v_name,
5756 v_value);
5757
5758 if(l_statementEnabled) then
5759 ecx_debug.log(l_statement,'global variable name',
5760 ecx_utils.g_stack(k).variable_name,i_method_name);
5761 ecx_debug.log(l_statement,'global variable value',
5762 ecx_utils.g_stack(k).variable_value,i_method_name);
5763 end if;
5764 end loop;
5765 -- Call the procedure. The procedure takes wf_event_t as
5766 -- input and gives out xml_fragment as output.
5767
5768 proc_call := 'BEGIN ' || proc_name || ' (:EVENT,:XML_FRAGMENT);
5769 END;' ;
5770
5771 execute immediate proc_call using in ecx_utils.g_event, out xml_fragment;
5772
5773 if(l_statementEnabled) then
5774 ecx_debug.log(l_statement,'xml fragment',
5775 xml_fragment,i_method_name);
5776 end if;
5777
5778 -- Adding dummy root element before parsing the xml fragment.
5779
5780 temp_xml := '<dummy>'||xml_fragment||'</dummy>';
5781 temp_parser := xmlparser.newparser;
5782 xmlparser.parsebuffer(temp_parser,temp_xml);
5783 if (temp_parser.id not in (-1)) then
5784 xmlparser.freeparser(temp_parser);
5785 end if;
5786 if (l_procedureEnabled) then
5787 ecx_debug.pop(i_method_name);
5788 end if;
5789 EXCEPTION
5790 when others then
5791 if (temp_parser.id not in (-1)) then
5792 xmlparser.freeparser(temp_parser);
5793 end if;
5794 if(l_unexpectedEnabled) then
5795 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.GET_XML_FRAGMENT');
5796 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
5797 end if;
5798 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_XML_FRAGMENT');
5799 if (l_procedureEnabled) then
5800 ecx_debug.pop(i_method_name);
5801 end if;
5802 raise ecx_utils.PROGRAM_EXIT;
5803 end;
5804 /*
5805 bug 10254870
5806 function to transform the given xml based on given xsl (lfilename) and
5807 transformed content will returned in
5808 this is overloaded method so that existing functionality will not get imacted
5809 */
5810 procedure transform_xml_with_xslt
5811 (
5812 i_filename in varchar2,
5813 i_version in number,
5814 i_application_code in varchar2,
5815 i_out_transformed in out nocopy clob
5816 )
5817 is
5818 i_method_name varchar2(2000) := 'ecx_actions.transform_xml_with_xslt';
5819
5820 i_stylesheet xslprocessor.Stylesheet;
5821 i_processor xslprocessor.Processor;
5822 i_xslt_dir varchar2(200);
5823 i_fullpath varchar2(200);
5824 i_string varchar2(2000);
5825 l_xslt_payload clob;
5826 l_parser xmlparser.parser;
5827 l_xsl_doc xmldom.DOMDocument;
5828 i_doc xmlDOM.DOMDocument;
5829 i_doc_frag xmlDOM.DOMDocumentFragment;
5830 i_node_type pls_integer;
5831
5832 begin
5833 if (l_procedureEnabled) then
5834 ecx_debug.push(i_method_name);
5835 end if;
5836
5837 if (xmlDOM.isNull(ecx_utils.g_xmldoc)) then
5838 return;
5839 end if;
5840
5841 if(l_statementEnabled) then
5842 ecx_debug.log(l_statement,'i_filename',i_filename,i_method_name);
5843 end if;
5844
5845 if i_filename is null
5846 then
5847 return;
5848 end if;
5849
5850 -- check if the XSLT file is loaded in the DB
5851 begin
5852 select payload
5853 into l_xslt_payload
5854 from ecx_files
5855 where (i_version is null or version = i_version)
5856 and (i_application_code is null or application_code = i_application_code)
5857 and name = i_filename
5858 and type = 'XSLT';
5859 exception
5860 when no_data_found then
5861 null;
5862 when too_many_rows then
5863 -- currently we do not support version and application_code as
5864 -- input parameters for XSLT actions, so for now, this exception
5865 -- means that the API was invoked within the actions code. So, check
5866 -- the file system
5867 if(l_unexpectedEnabled) then
5868 ecx_debug.log(l_unexpected, SQLERRM,i_method_name);
5869 end if;
5870 l_xslt_payload := null;
5871 when others then
5872 ecx_debug.setErrorInfo(2,30,SQLERRM);
5873 raise ecx_utils.program_exit;
5874 end;
5875
5876 if (l_xslt_payload is null)
5877 then
5878 -- xslt file is not loaded in the DB table
5879 -- do the transformation assuming that it is on the file system
5880 if(l_statementEnabled) then
5881 ecx_debug.log(l_statement,'XSLT file not loaded in the DB. Checking the file system...',
5882 i_method_name);
5886 --- Check for the Installation Type ( Standalone or Embedded );
5883 end if;
5884
5885 if (ecx_actions.g_xslt_dir is null) then
5887 if (ecx_utils.g_install_mode is null) then
5888 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
5889 end if;
5890
5891 if ecx_utils.g_install_mode = 'EMBEDDED'
5892 then
5893 i_string := 'begin
5894 fnd_profile.get('||'''ECX_UTL_XSLT_DIR'''||',ecx_actions.g_xslt_dir);
5895 end;';
5896 execute immediate i_string ;
5897 else
5898 ecx_actions.g_xslt_dir:= wf_core.translate('ECX_UTL_XSLT_DIR');
5899 end if;
5900 end if;
5901
5902 i_fullpath := ecx_actions.g_xslt_dir||ecx_utils.getFileSeparator()||i_filename;
5903 if(l_statementEnabled) then
5904 ecx_debug.log(l_statement, 'XSLT Fullpath', i_fullpath,i_method_name);
5905 end if;
5906 l_parser := xmlparser.newParser;
5907 xmlparser.setPreservewhitespace(l_parser,true); -- bug:4953557
5908 xmlparser.parse(l_parser,i_fullpath);
5909 l_xsl_doc := xmlparser.getDocument(l_parser);
5910 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc,i_fullpath);
5911 else
5912 -- payload found in DB,
5913 if(l_statementEnabled) then
5914 ecx_debug.log(l_statement, 'Found XSLT file in the DB',i_method_name);
5915 end if;
5916 -- convert l_xslt_paylod from clob to DOMDocument
5917 l_parser := xmlparser.newParser;
5918 xmlparser.setPreservewhitespace(l_parser,true); -- bug:4953557
5919 xmlparser.parseCLOB(l_parser, l_xslt_payload);
5920 l_xsl_doc := xmlparser.getDocument(l_parser);
5921
5922 -- get the stylesheet
5923 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc, null);
5924 end if;
5925
5926 i_processor := xslprocessor.newProcessor;
5927
5928 -- get the type of the DOMNode
5929 i_node_type := xmlDOM.getNodeType(ecx_utils.g_xmldoc);
5930 if(l_statementEnabled) then
5931 ecx_debug.log(l_statement, 'i_node_type', i_node_type,i_method_name);
5932 end if;
5933
5934 if (i_node_type = xmlDOM.DOCUMENT_NODE)
5935 then
5936 if(l_statementEnabled) then
5937 ecx_debug.log(l_statement, 'Creating Document Object from DOM Node...',i_method_name);
5938 end if;
5939 i_doc := xmlDOM.makeDocument(ecx_utils.g_xmldoc);
5940 if(l_statementEnabled) then
5941 ecx_debug.log(l_statement, 'Before processing XSL',i_method_name);
5942 end if;
5943 xslprocessor.processXSL(i_processor, i_stylesheet, i_doc,i_out_transformed);
5944
5945 elsif (i_node_type = xmlDOM.DOCUMENT_FRAGMENT_NODE)
5946 then
5947 if(l_statementEnabled) then
5948 ecx_debug.log(l_statement,'Creating Document fragment from DOM Node...',i_method_name);
5949 end if;
5950 i_doc_frag := xmlDOM.makeDocumentFragment(ecx_utils.g_xmldoc);
5951 if(l_statementEnabled) then
5952 ecx_debug.log(l_statement, 'Before processing XSL',i_method_name);
5953 end if;
5954 xslprocessor.processXSL(i_processor, i_stylesheet, i_doc_frag,i_out_transformed);
5955 end if;
5956
5957 if(l_statementEnabled) then
5958 ecx_debug.log(l_statement,'XSL processed. Output written to a CLOB i_xslt_file',i_method_name);
5959 end if;
5960
5961 -- free all the used variables
5962 xslprocessor.freeStylesheet(i_stylesheet);
5963 xslprocessor.freeProcessor(i_processor);
5964
5965 if (l_parser.id <> -1)
5966 then
5967 xmlParser.freeParser(l_parser);
5968 end if;
5969 if (not xmldom.isNull(l_xsl_doc))
5970 then
5971 xmldom.freeDocument(l_xsl_doc);
5972 end if;
5973 if (not xmldom.isNull(i_doc))
5974 then
5975 xmldom.freeDocument(i_doc);
5976 end if;
5977
5978 if (not xmldom.isNull(i_doc_frag))
5979 then
5980 xmldom.freeDocFrag(i_doc_frag);
5981 end if;
5982
5983 if (l_procedureEnabled) then
5984 ecx_debug.pop(i_method_name);
5985 end if;
5986 exception
5987 -- Put All DOM Parser Exceptions Here.
5988 when xmlDOM.INDEX_SIZE_ERR then
5989 ecx_debug.setErrorInfo(1,20,SQLERRM);
5990 if(l_unexpectedEnabled) then
5991 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
5992 end if;
5993 -- free all the used variables
5994 if (l_parser.id <> -1)
5995 then
5996 xmlParser.freeParser(l_parser);
5997 end if;
5998 if (not xmldom.isNull(l_xsl_doc))
5999 then
6000 xmldom.freeDocument(l_xsl_doc);
6001 end if;
6002 if (l_procedureEnabled) then
6003 ecx_debug.pop(i_method_name);
6004 end if;
6005 raise ecx_utils.program_exit;
6006 when xmlDOM.DOMSTRING_SIZE_ERR then
6007 ecx_debug.setErrorInfo(1,20,SQLERRM);
6008 if(l_unexpectedEnabled) then
6009 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6010 end if;
6011 -- free all the used variables
6012 if (l_parser.id <> -1)
6013 then
6014 xmlParser.freeParser(l_parser);
6015 end if;
6016 if (not xmldom.isNull(l_xsl_doc))
6017 then
6018 xmldom.freeDocument(l_xsl_doc);
6019 end if;
6020 if (l_procedureEnabled) then
6021 ecx_debug.pop(i_method_name);
6022 end if;
6023 raise ecx_utils.program_exit;
6024 when xmlDOM.HIERARCHY_REQUEST_ERR then
6025 ecx_debug.setErrorInfo(1,20,SQLERRM);
6026 if(l_unexpectedEnabled) then
6027 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6028 end if;
6029 -- free all the used variables
6030 if (l_parser.id <> -1)
6031 then
6032 xmlParser.freeParser(l_parser);
6033 end if;
6037 end if;
6034 if (not xmldom.isNull(l_xsl_doc))
6035 then
6036 xmldom.freeDocument(l_xsl_doc);
6038 if (l_procedureEnabled) then
6039 ecx_debug.pop(i_method_name);
6040 end if;
6041 raise ecx_utils.program_exit;
6042 when xmlDOM.WRONG_DOCUMENT_ERR then
6043 ecx_debug.setErrorInfo(1,20,SQLERRM);
6044 if(l_unexpectedEnabled) then
6045 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6046 end if;
6047 -- free all the used variables
6048 if (l_parser.id <> -1)
6049 then
6050 xmlParser.freeParser(l_parser);
6051 end if;
6052 if (not xmldom.isNull(l_xsl_doc))
6053 then
6054 xmldom.freeDocument(l_xsl_doc);
6055 end if;
6056 if (l_procedureEnabled) then
6057 ecx_debug.pop(i_method_name);
6058 end if;
6059 raise ecx_utils.program_exit;
6060 when xmlDOM.INVALID_CHARACTER_ERR then
6061 ecx_debug.setErrorInfo(1,20,SQLERRM);
6062 if(l_unexpectedEnabled) then
6063 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6064 end if;
6065 -- free all the used variables
6066 if (l_parser.id <> -1)
6067 then
6068 xmlParser.freeParser(l_parser);
6069 end if;
6070 if (not xmldom.isNull(l_xsl_doc))
6071 then
6072 xmldom.freeDocument(l_xsl_doc);
6073 end if;
6074 if (l_procedureEnabled) then
6075 ecx_debug.pop(i_method_name);
6076 end if;
6077 raise ecx_utils.program_exit;
6078 when xmlDOM.NO_DATA_ALLOWED_ERR then
6079 ecx_debug.setErrorInfo(1,20,SQLERRM);
6083 -- free all the used variables
6080 if(l_unexpectedEnabled) then
6081 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6082 end if;
6084 if (l_parser.id <> -1)
6085 then
6086 xmlParser.freeParser(l_parser);
6087 end if;
6088 if (not xmldom.isNull(l_xsl_doc))
6089 then
6090 xmldom.freeDocument(l_xsl_doc);
6091 end if;
6092 if (l_procedureEnabled) then
6093 ecx_debug.pop(i_method_name);
6094 end if;
6095 raise ecx_utils.program_exit;
6096 when xmlDOM.No_MODIFICATION_ALLOWED_ERR then
6097 ecx_debug.setErrorInfo(1,20,SQLERRM);
6098 if(l_unexpectedEnabled) then
6099 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6100 end if;
6101 -- free all the used variables
6102 if (l_parser.id <> -1)
6103 then
6104 xmlParser.freeParser(l_parser);
6105 end if;
6106 if (not xmldom.isNull(l_xsl_doc))
6107 then
6108 xmldom.freeDocument(l_xsl_doc);
6109 end if;
6110 if (l_procedureEnabled) then
6111 ecx_debug.pop(i_method_name);
6112 end if;
6113 raise ecx_utils.program_exit;
6114 when xmlDOM.NOT_FOUND_ERR then
6115 ecx_debug.setErrorInfo(1,20,SQLERRM);
6116 if(l_unexpectedEnabled) then
6117 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6118 end if;
6119 -- free all the used variables
6120 if (l_parser.id <> -1)
6121 then
6122 xmlParser.freeParser(l_parser);
6123 end if;
6124 if (not xmldom.isNull(l_xsl_doc))
6125 then
6126 xmldom.freeDocument(l_xsl_doc);
6127 end if;
6128 if (l_procedureEnabled) then
6129 ecx_debug.pop(i_method_name);
6130 end if;
6131 raise ecx_utils.program_exit;
6132 when xmlDOM.NOT_SUPPORTED_ERR then
6133 ecx_debug.setErrorInfo(1,20,SQLERRM);
6134 if(l_unexpectedEnabled) then
6135 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6136 end if;
6137 -- free all the used variables
6138 if (l_parser.id <> -1)
6139 then
6140 xmlParser.freeParser(l_parser);
6141 end if;
6142 if (not xmldom.isNull(l_xsl_doc))
6143 then
6144 xmldom.freeDocument(l_xsl_doc);
6145 end if;
6146 if (l_procedureEnabled) then
6147 ecx_debug.pop(i_method_name);
6148 end if;
6149 raise ecx_utils.program_exit;
6150 when xmlDOM.INUSE_ATTRIBUTE_ERR then
6151 ecx_debug.setErrorInfo(1,20,SQLERRM);
6152 -- free all the used variables
6153 if (l_parser.id <> -1)
6154 then
6155 xmlParser.freeParser(l_parser);
6156 end if;
6157 if (not xmldom.isNull(l_xsl_doc))
6158 then
6159 xmldom.freeDocument(l_xsl_doc);
6160 end if;
6161 if(l_unexpectedEnabled) then
6162 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
6163 end if;
6164 if (l_procedureEnabled) then
6165 ecx_debug.pop(i_method_name);
6166 end if;
6167 raise ecx_utils.program_exit;
6168
6169 WHEN OTHERS THEN
6170 if(l_unexpectedEnabled) then
6174 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.TRANSFORM_XML_WITH_XSLT');
6171 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL','ECX_ACTIONS.TRANSFORM_XML_WITH_XSLT');
6172 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
6173 end if;
6175 -- free all the used variables
6176 if (l_parser.id <> -1)
6177 then
6178 xmlParser.freeParser(l_parser);
6179 end if;
6180 if (not xmldom.isNull(l_xsl_doc))
6181 then
6182 xmldom.freeDocument(l_xsl_doc);
6183 end if;
6184 if (l_procedureEnabled) then
6185 ecx_debug.pop(i_method_name);
6186 end if;
6187 raise ecx_utils.PROGRAM_EXIT;
6188 end transform_xml_with_xslt;
6189 /**
6190 function to get no of same action type(e.g.5000-XSLT Transformation) defined for the
6191 given stage(e.g. 30-Post Processing)
6192 **/
6193 function get_Count_Action_Type
6194 (
6195 i_stage IN pls_integer,
6196 i_action_type IN pls_integer
6197 ) return pls_integer is
6198 i_method_name varchar2(2000) := 'ecx_actions.get_Count_Action_Type';
6199 l_cnt_actionType pls_integer:= 0;
6200 BEGIN
6201 if (l_procedureEnabled) then
6202 ecx_debug.push(i_method_name);
6203 end if;
6204 if (ecx_utils.g_stage_data.count <> 0)
6205 then
6206 FOR i in ecx_utils.g_stage_data.first..ecx_utils.g_stage_data.last
6207 loop
6208 if (ecx_utils.g_stage_data(i).stage = i_stage and ecx_utils.g_stage_data(i).action_type = i_action_type)
6209 then
6210 l_cnt_actionType := l_cnt_actionType + 1;
6211 end if;
6212 end loop;
6213 end if;
6214 if (l_procedureEnabled) then
6215 ecx_debug.pop(i_method_name);
6216 end if;
6217 return l_cnt_actionType;
6218 EXCEPTION
6219 WHEN OTHERS THEN
6220 if(l_statementEnabled) then
6221 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,
6222 'PROGRESS_LEVEL','ECX_ACTIONS.GET_COUNT_ACTION_TYPE');
6223 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
6224 end if;
6225 ecx_debug.setErrorInfo(2,30,SQLERRM||' - ECX_ACTIONS.GET_COUNT_ACTION_TYPE');
6226 if (l_procedureEnabled) then
6227 ecx_debug.pop(i_method_name);
6228 end if;
6229 raise ecx_utils.PROGRAM_EXIT;
6230 end get_Count_Action_Type;
6231 end ecx_actions;