[Home] [Help]
PACKAGE: APPS.EC_UTILS
Source
1 package ec_utils as
2 -- $Header: ECUTILS.pls 120.2 2005/09/30 06:18:58 arsriniv ship $
3
4 program_exit exception;
5 documents_under_process exception;
6 pragma exception_init(documents_under_process,-54);
7 i_ret_code pls_integer :=0;
8 i_errbuf varchar2(2000);
9
10 /**
11 Column Rule table bug# 2500898
12 **/
13 Type column_rule_rec is Record
14 (
15 column_rule_id ece_column_rules.column_rule_id%type,
16 rule_type ece_column_rules.rule_type%type,
17 action_code ece_column_rules.action_code%type,
18 level pls_integer
19 );
20
21 TYPE column_rule_tbl is table of column_rule_rec index by BINARY_INTEGER;
22
23 /**
24 Mapping Record Information.
25 **/
26 TYPE mapping_record_type is RECORD
27 (
28 interface_level pls_integer,
29 external_level pls_integer,
30 interface_column_id pls_integer,
31 base_column_name ece_interface_columns.base_column_name%TYPE,
32 interface_column_name ece_interface_columns.interface_column_name%TYPE,
33 staging_column ece_interface_columns.staging_column%TYPE,
34 record_layout_code ECE_INTERFACE_COLUMNS.record_layout_code%TYPE,
35 record_layout_qualifier ECE_INTERFACE_COLUMNS.record_layout_qualifier%TYPE,
36 record_number pls_integer,
37 element_tag_name ECE_INTERFACE_COLUMNS.element_tag_name%TYPE,
38 position pls_integer,
39 width pls_integer,
40 data_type ece_interface_columns.data_type%TYPE,
41 value varchar2(32000),
42 conversion_sequence pls_integer,
43 xref_category_id pls_integer,
44 xref_category_allowed ece_interface_columns.xref_category_allowed%TYPE,
45 conversion_group_id pls_integer,
46 xref_key1_source_column ece_interface_columns.xref_key1_source_column%TYPE,
47 xref_key2_source_column ece_interface_columns.xref_key2_source_column%TYPE,
48 xref_key3_source_column ece_interface_columns.xref_key3_source_column%TYPE,
49 xref_key4_source_column ece_interface_columns.xref_key4_source_column%TYPE,
50 xref_key5_source_column ece_interface_columns.xref_key5_source_column%TYPE,
51 ext_val1 VARCHAR2(500),
52 ext_val2 VARCHAR2(500),
53 ext_val3 VARCHAR2(500),
54 ext_val4 VARCHAR2(500),
55 ext_val5 VARCHAR2(500),
56 column_rule_flag VARCHAR2(1),
57 staging_column_no pls_integer --bug 2500898
58 );
59
60 TYPE mapping_tbl is table of mapping_record_type index by BINARY_INTEGER;
61
62 /**
63 Record for Dynamic Stage Data
64 **/
65 TYPE stage_rec is RECORD
66 (
67 stage pls_integer,
68 level pls_integer,
69 seq_number pls_integer,
70 action_type pls_integer,
71 variable_level pls_integer,
72 variable_name ece_tran_stage_data.variable_name%TYPE,
73 variable_value ece_tran_stage_data.variable_value%TYPE,
74 default_value ece_tran_stage_data.default_value%TYPE,
75 previous_variable_level pls_integer,
76 previous_variable_name ece_tran_stage_data.previous_variable_name%TYPE,
77 next_variable_name ece_tran_stage_data.next_variable_name%TYPE,
78 sequence_name ece_tran_stage_data.sequence_name%TYPE,
79 custom_procedure_name ece_tran_stage_data.custom_procedure_name%TYPE,
80 data_type ece_tran_stage_data.data_type%TYPE,
81 function_name ece_tran_stage_data.function_name%TYPE,
82 clause ece_tran_stage_data.where_clause%TYPE,
83 transtage_id pls_integer
84 );
85
86 TYPE stage_data is TABLE of stage_rec INDEX BY BINARY_INTEGER;
87
88
89 /**
90 Record for PL/SQL Stack
91 **/
92 TYPE stack_rec is RECORD
93 (
94 level pls_integer,
95 variable_name varchar2(80),
96 variable_value varchar2(32000),
97 variable_position pls_integer,
98 data_type varchar2(30)
99 );
100
101 TYPE pl_stack is TABLE of stack_rec index by BINARY_INTEGER;
102
103
104 /**
105 Record for Interface levels
106 **/
107 TYPE interface_level_rec is RECORD
108 (
109 interface_level pls_integer,
110 base_table_name varchar2(400),
111 cursor_handle pls_integer,
112 sql_stmt varchar2(32000),
113 parent_level pls_integer,
114 key_column_name ece_interface_tables.key_column_name%TYPE,
115 rows_processed pls_integer,
116 file_start_pos pls_integer,
117 file_end_pos pls_integer
118 );
119
120 TYPE interface_level_tbl is table of interface_level_rec index by BINARY_INTEGER;
121
122
123 /**
124 Record for External Levels
125 **/
126 TYPE external_level_rec is RECORD
127 (
128 external_level pls_integer,
129 cursor_handle pls_integer,
130 sql_stmt varchar2(32000),
131 stage_id pls_integer,
132 document_id pls_integer,
133 document_number ece_stage.document_number%TYPE,
134 line_number pls_integer,
135 parent_stage_id pls_integer,
136 status ece_stage.status%TYPE,
137 record_number ece_external_levels.start_element%TYPE,
138 file_start_pos pls_integer,
139 file_end_pos pls_integer
140 );
141
142 TYPE external_level_tbl is TABLE of external_level_rec index by BINARY_INTEGER;
143
144 /**
145 Record for Interface/External Levels
146 **/
147 TYPE interface_external_level_rec is RECORD
148 (
149 interface_level pls_integer,
150 external_level pls_integer
151 );
152
153 TYPE interface_external_tbl is TABLE of interface_external_level_rec index by BINARY_INTEGER;
154
155 /**
156 Record for maintaing the location of variables on the Stack for each level
157 **/
158 TYPE stack_location is RECORD
159 (
160 start_pos pls_integer,
161 end_pos pls_integer
162 );
163
164 TYPE stack_pointer is TABLE of stack_location index by BINARY_INTEGER;
165
166 --Bug 2617428 Declared Global variables to store computed hash values
167 -- on the positions for g_file_tbl.
168 -- Bug 2834366
169 TYPE hash_rec IS RECORD
170 (
171 value pls_integer,
172 occr pls_integer,
173 start_pos pls_integer
174 );
175 TYPE hash_tbl IS TABLE OF hash_rec INDEX BY BINARY_INTEGER;
176
177 TYPE pos_tbl IS TABLE OF number INDEX BY BINARY_INTEGER;
178
179 g_code_conv_pos_tbl_1 hash_tbl;
180 g_code_conv_pos_tbl_2 pos_tbl;
181 g_col_pos_tbl_1 hash_tbl;
182 g_col_pos_tbl_2 pos_tbl;
183 g_stack_pos_tbl pos_tbl; -- Bug 2708573
184
185 /**
186 -- Global instances of the Stack , PL/SQL data table , Stage Data , Interface
187 -- Levels , External and Interface/External levels.
188 **/
189
190 g_stack pl_stack;
191 g_file_tbl mapping_tbl;
192 g_int_levels interface_level_tbl;
193 g_ext_levels external_level_tbl;
194 g_int_ext_levels interface_external_tbl;
195 g_stage_data stage_data;
196 g_stack_pointer stack_pointer;
197 g_file_pointer stack_pointer;
198 g_column_rule_tbl column_rule_tbl; --bug 2500898
199
200 -- Global parameter for a Stored procedure
201 g_parameter_stack ec_execution_utils.t_procparameters;
202 g_procedure_stack ec_execution_utils.t_proclist;
203 g_procedure_mappings ec_execution_utils.t_procedure_mappings;
204
205
206 /**
207 Global Document variables
208 **/
209 g_documents_skipped pls_integer :=0;
210 g_insert_failed pls_integer :=0;
211 g_current_level pls_integer :=0;
212 g_transaction_type varchar2(20) :=NULL;
213 g_document_id varchar2(20) :=NULL;
214 g_run_id pls_integer :=0;
215 g_direction varchar2(1);
216 g_map_id pls_integer :=0;
217
218 /** Bug 2422787
219 Global stage variables
220 **/
221 i_tmp_stage_data stage_data;---- Used for Stage 10 data only.
222 i_tmp2_stage_data stage_data;---- used for Stages other than 10.
223 i_stage_data stage_data;---- Temporary place holder for all Stages .
224
225 procedure find_pos
226 (
227 i_level IN number,
228 i_search_text IN varchar2,
229 o_pos OUT NOCOPY NUMBER,
230 i_required IN BOOLEAN DEFAULT TRUE
231 ) ;
232
233 procedure find_pos
234 (
235 i_from_level IN number,
236 i_to_level IN number,
237 i_search_text IN varchar2,
238 o_pos OUT NOCOPY NUMBER,
239 i_required IN BOOLEAN DEFAULT TRUE
240 );
241
242 function find_variable
243 (
244 i_variable_level IN number,
245 i_variable_name IN VARCHAR2,
246 i_stack_pos OUT NOCOPY NUMBER,
247 i_plsql_pos OUT NOCOPY NUMBER
248 ) return boolean;
249
250 procedure get_nextval_seq
251 (
252 i_seq_name in varchar2,
253 o_value OUT NOCOPY varchar2
254 );
255
256 procedure get_function_value
257 (
258 i_function_name IN varchar2,
259 o_value OUT NOCOPY varchar2
260 );
261
262 procedure execute_string
263 (
264 cString in varchar2,
265 o_value OUT NOCOPY varchar2
266 );
267
268 procedure dump_stack;
269
270 procedure get_tran_stage_data
271 (
272 i_transaction_type IN varchar2,
273 i_map_id IN number
274 );
275
276 procedure sort_stage_data;
277
278 procedure execute_stage_data
279 (
280 i_stage IN number,
281 i_level IN number
282 );
283
284 procedure create_new_variable
285 (
286 i_variable_level IN NUMBER,
287 i_variable_name IN VARCHAR2,
288 i_variable_value IN varchar2,
289 i_data_type IN varchar2 default NULL
290 );
291
292 procedure assign_default_to_variables
293 (
294 i_variable_level IN number,
295 i_variable_name IN varchar2,
296 i_default_value IN varchar2
297 );
298
299 procedure IF_XNULL_SETYDEFAULT
300 (
301 i_variable_level IN number,
302 i_variable_name IN varchar2,
303 i_previous_variable_level IN number,
304 i_previous_variable_name IN varchar2,
305 i_default_value IN varchar2
306 );
307
308 procedure assign_pre_defined_variables
309 (
310 i_variable_level IN number,
311 i_variable_name IN varchar2,
312 i_previous_variable_level IN number,
313 i_previous_variable_name IN varchar2
314 );
315
316 procedure assign_nextval_from_sequence
317 (
318 i_variable_level IN number,
319 i_variable_name IN varchar2,
320 i_sequence_name IN varchar2
321 );
322
323 procedure assign_function_value
324 (
325 i_variable_level IN number,
326 i_variable_name IN varchar2,
327 i_function_name IN varchar2
328 );
329
330 procedure increment_by_one
331 (
332 i_variable_level IN number,
333 i_variable_name IN varchar2
334 );
335
336 procedure if_null_pre_defined_variable
337 (
338 i_variable_level IN number,
339 i_variable_name IN varchar2,
340 i_previous_variable_level IN number,
341 i_previous_variable_name IN varchar2
342 );
343
344 /* Bug 1999536
345 Procedure to implement the new action code 150.
346 */
347 procedure if_not_null_defined_variable
348 (
349 i_variable_level IN number,
350 i_variable_name IN varchar2,
351 i_previous_variable_level IN number,
352 i_previous_variable_name IN varchar2
353 );
354
355 procedure if_default_pre_defined_var
356 (
357 i_variable_level IN number,
358 i_variable_name IN varchar2,
359 i_previous_variable_level IN number,
360 i_previous_variable_name IN varchar2,
361 i_default_value IN varchar2
362 );
363
364 procedure if_diff_pre_next_then_default
365 (
366 i_variable_level IN number,
367 i_variable_name IN varchar2,
368 i_previous_variable_level IN number,
369 i_previous_variable_name IN varchar2,
370 i_next_variable_name IN varchar2,
371 i_default_value IN varchar2
372 );
373
374 procedure if_null_equal_default_value
375 (
376 i_variable_level IN number,
377 i_variable_name IN varchar2,
378 i_default_value IN varchar2
379 );
380
381 procedure if_null_skip_document
382 (
383 i_variable_level IN number,
384 i_variable_name IN varchar2
385 );
386
387 procedure create_mandatory_columns
388 (
389 i_variable_level IN number,
390 i_previous_variable_level IN number,
391 i_variable_name IN varchar2,
392 i_default_value IN varchar2,
393 i_data_type IN varchar2,
394 i_function_name IN varchar2
395 );
396
397 procedure append_clause
398 (
399 i_level IN number,
400 i_where_clause IN varchar2
401 );
402
403 procedure if_Notnull_append_clause
404 (
405 i_level IN number,
406 i_variable_level IN number,
407 i_variable_name IN varchar2,
408 i_where_clause IN varchar2
409 );
410
411 procedure bind_variables_for_view
412 (
413 i_variable_name IN varchar2,
414 i_previous_variable_level IN integer,
415 i_previous_variable_name IN varchar2
416 );
417
418 procedure execute_proc
419 (
420 i_transtage_id IN number,
421 i_procedure_name IN varchar2
422 );
423 procedure ifxnull_execute_proc
424 (
425 i_variable_level IN number,
426 i_variable_name IN varchar2,
427 i_transtage_id IN number,
428 i_procedure_name IN varchar2
429 );
430 procedure ifxnotnull_execute_proc
431 (
432 i_variable_level IN number,
433 i_variable_name IN varchar2,
434 i_transtage_id IN number,
435 i_procedure_name IN varchar2
436 );
437 procedure ifxconst_execute_proc
438 (
439 i_variable_level IN number,
440 i_variable_name IN varchar2,
441 i_default_value IN varchar2,
442 i_transtage_id IN number,
443 i_procedure_name IN varchar2
444 );
445
446 procedure ifxpre_execute_proc
447 (
448 i_variable_level IN number,
449 i_variable_name IN varchar2,
450 i_previous_variable_level IN number,
451 i_previous_variable_name IN varchar2,
452 i_transtage_id IN number,
453 i_procedure_name IN varchar2
454 );
455
456 procedure ext_find_position
457 (
458 i_level IN number,
459 i_search_text IN varchar2,
460 o_pos OUT NOCOPY NUMBER,
461 i_required IN BOOLEAN DEFAULT TRUE
462 ) ;
463
464 procedure ext_get_key_value
465 (
466 i_position IN number,
467 o_value OUT NOCOPY varchar2
468 );
469
470 procedure ext_insert_value
471 (
472 i_position IN number,
473 i_value IN varchar2
474 );
475
476
477
478 end ec_utils;