[Home] [Help]
PACKAGE: APPS.ECX_UTILS
Source
1 package ecx_utils as
2 -- $Header: ECXUTILS.pls 120.5.12010000.2 2008/08/22 20:03:35 cpeixoto ship $
3
4 program_exit exception;
5 no_seed_data exception;
6 documents_under_process exception;
7 pragma exception_init(documents_under_process,-54);
8 error_type pls_integer ;
9 i_ret_code pls_integer :=0;
10 i_errbuf varchar2(2000);
11 i_errparams varchar2(2000);
12 i_curr_errid number :=0;
13 g_direction varchar2(3);
14 g_source_object_id pls_integer := null;
15 g_target_object_id pls_integer := null;
16 g_ret_code pls_integer :=0;
17
18 g_delete_doctype boolean := false;
19
20 /** Custom message product code placeholder **/
21 g_cust_msg_code varchar2(20) := null;
22 G_XML_FRAG_PROC varchar2(50) := 'ECX_ACTIONS.GET_XML_FRAGMENT';
23 /**
24 Record for Dynamic Stage Data
25 **/
26 TYPE stage_rec is RECORD
27 (
28 stage pls_integer,
29 level pls_integer,
30 object_direction varchar2(1),
31 objectlevel_id pls_integer,
32 seq_number pls_integer,
33 action_type pls_integer,
34 variable_level pls_integer,
35 variable_pos pls_integer,
36 variable_direction varchar2(10),
37 variable_name ecx_tran_stage_data.variable_name%TYPE,
38 variable_value ecx_tran_stage_data.variable_value%TYPE,
39 default_value ecx_tran_stage_data.default_value%TYPE,
40 sequence_name ecx_tran_stage_data.sequence_name%TYPE,
41 custom_procedure_name ecx_tran_stage_data.custom_procedure_name%TYPE,
42 data_type pls_integer,
43 function_name ecx_tran_stage_data.function_name%TYPE,
44 clause ecx_tran_stage_data.where_clause%TYPE,
45 transtage_id pls_integer,
46 cond_logical_operator ecx_tran_stage_data.cond_logical_operator%TYPE,
47 cond_operator1 ecx_tran_stage_data.cond_operator1%TYPE,
48 cond_var1_level pls_integer,
49 cond_var1_pos pls_integer,
50 cond_var1_name ecx_tran_stage_data.cond_var1_name%TYPE,
51 cond_var1_direction ecx_tran_stage_data.cond_var1_direction%TYPE,
52 cond_var1_constant ecx_tran_stage_data.cond_var1_constant%TYPE,
53 cond_val1_level pls_integer,
54 cond_val1_pos pls_integer,
55 cond_val1_name ecx_tran_stage_data.cond_val1_name%TYPE,
56 cond_val1_direction ecx_tran_stage_data.cond_val1_direction%TYPE,
57 cond_val1_constant ecx_tran_stage_data.cond_val1_constant%TYPE,
58 cond_operator2 ecx_tran_stage_data.cond_operator2%TYPE,
59 cond_var2_level pls_integer,
60 cond_var2_pos pls_integer,
61 cond_var2_name ecx_tran_stage_data.cond_var2_name%TYPE,
62 cond_var2_direction ecx_tran_stage_data.cond_var2_direction%TYPE,
63 cond_var2_constant ecx_tran_stage_data.cond_var2_constant%TYPE,
64 cond_val2_level pls_integer,
65 cond_val2_pos pls_integer,
66 cond_val2_name ecx_tran_stage_data.cond_val2_name%TYPE,
67 cond_val2_direction ecx_tran_stage_data.cond_val2_direction%TYPE,
68 cond_val2_constant ecx_tran_stage_data.cond_val2_constant%TYPE,
69 operand1_level pls_integer,
70 operand1_name ecx_tran_stage_data.operand1_name%TYPE,
71 operand1_pos pls_integer,
72 operand1_direction ecx_tran_stage_data.operand1_direction%TYPE,
73 operand1_constant ecx_tran_stage_data.operand1_constant%TYPE,
74 operand1_len ecx_tran_stage_data.operand1_len%TYPE,
75 operand1_start_pos ecx_tran_stage_data.operand1_start_pos%TYPE,
76 operand2_level pls_integer,
77 operand2_name ecx_tran_stage_data.operand2_name%TYPE,
78 operand2_pos pls_integer,
79 operand2_direction ecx_tran_stage_data.operand2_direction%TYPE,
80 operand2_constant ecx_tran_stage_data.operand2_constant%TYPE,
81 operand3_level pls_integer,
82 operand3_name ecx_tran_stage_data.operand3_name%TYPE,
83 operand3_pos pls_integer,
84 operand3_direction ecx_tran_stage_data.operand3_direction%TYPE,
85 operand3_constant ecx_tran_stage_data.operand3_constant%TYPE,
86 operand4_level pls_integer,
87 operand4_name ecx_tran_stage_data.operand4_name%TYPE,
88 operand4_pos pls_integer,
89 operand4_direction ecx_tran_stage_data.operand4_direction%TYPE,
90 operand4_constant ecx_tran_stage_data.operand4_constant%TYPE,
91 operand5_level pls_integer,
92 operand5_name ecx_tran_stage_data.operand5_name%TYPE,
93 operand5_pos pls_integer,
94 operand5_direction ecx_tran_stage_data.operand5_direction%TYPE,
95 operand5_constant ecx_tran_stage_data.operand5_constant%TYPE,
96 operand6_level pls_integer,
97 operand6_name ecx_tran_stage_data.operand6_name%TYPE,
98 operand6_pos pls_integer,
99 operand6_direction ecx_tran_stage_data.operand6_direction%TYPE,
100 operand6_constant ecx_tran_stage_data.operand6_constant%TYPE
101 );
102
103 TYPE stage_data is TABLE of stage_rec INDEX BY BINARY_INTEGER;
104
105 /**
106 Record for PL/SQL Stack
107 **/
108 TYPE stack_rec is RECORD
109 (
110 variable_name varchar2(256),
111 variable_value varchar2(32767),
112 data_type pls_integer
113 );
114
115 TYPE pl_stack is TABLE of stack_rec index by BINARY_INTEGER;
116
117
118 -- Table to hold XMLNode references indexed by ids
119 TYPE node_data is TABLE of xmlDOM.DOMNode INDEX BY BINARY_INTEGER;
120
121
122 /**
123 Record for Interface levels
124 **/
125 TYPE level_rec is RECORD
126 (
127 level pls_integer,
128 cursor_handle pls_integer,
129 sql_stmt varchar2(32000),
130 parent_level pls_integer,
131 base_table_name varchar2(400),
132 rows_processed pls_integer,
133 stage_id pls_integer,
134 document_id pls_integer,
135 document_number Varchar2(500),
136 dtd_node_index pls_integer,
137 parent_stage_id pls_integer,
138 status Varchar2(20),
139 start_element ecx_object_levels.object_level_name%TYPE,
140 file_start_pos pls_integer,
141 file_end_pos pls_integer,
142 first_source_level pls_integer,
143 last_source_level pls_integer,
144 first_target_level pls_integer,
145 last_target_level pls_integer
146 );
147
148 TYPE level_tbl is table of level_rec index by BINARY_INTEGER;
149
150 /**
151 Record for Interface/External Levels
152 **/
153 TYPE source_target_level_rec is RECORD
154 (
155 source_level pls_integer,
156 target_level pls_integer
157 );
158
159 TYPE source_target_level_tbl is TABLE of source_target_level_rec index by BINARY_INTEGER;
160
161
162 /**
163 Used by both External and Internal Objects
164 **/
165 TYPE dtd_node_rec is RECORD
166 (
167 dtd_node_map_id pls_integer,
168 attribute_id pls_integer,
169 attribute_name ecx_object_attributes.attribute_name%TYPE,
170 object_column_flag ecx_object_attributes.object_column_flag%TYPE,
171 base_column_name ecx_object_attributes.attribute_name%TYPE,
172 map_attribute_id pls_integer,
173 parent_attribute_id pls_integer,
174 attribute_type pls_integer,
175 default_value ecx_object_attributes.default_value%TYPE,
176 data_type pls_integer,
177 occurrence pls_integer,
178 cond_value ecx_object_attributes.cond_value%TYPE,
179 cond_node ecx_object_attributes.cond_node%TYPE,
180 cond_node_type ecx_object_attributes.cond_node_type%TYPE,
181 external_level pls_integer,
182 internal_level pls_integer,
183 has_attributes pls_integer,
184 leaf_node pls_integer,
185 required_flag ecx_object_attributes.required_flag%TYPE,
186 xref_category_id pls_integer,
187 value varchar2(32767),
188
189 parent_node_map_id pls_integer,
190 xref_retcode Varchar2(1),
191 clob_value clob,
192 clob_length pls_integer,
193 is_clob varchar2(1)
194 );
195
196 /**
197 Record for dtd nodes
198 **/
199 TYPE dtd_node_tbl is TABLE of dtd_node_rec index by BINARY_INTEGER;
200
201
202 /**
203 -- Global instances of the Stack , PL/SQL data table , Stage Data , Interface
204 -- Levels , External and Interface/External levels.
205 **/
206
207 g_stack pl_stack;
208 g_source_levels level_tbl;
209 g_target_levels level_tbl;
210 g_target_source_levels source_target_level_tbl;
211 g_stage_data stage_data;
212 g_dtd_nodes dtd_node_tbl;
213 g_source dtd_node_tbl;
214 g_empty_source dtd_node_tbl;
215 g_target dtd_node_tbl;
216 g_empty_target dtd_node_tbl;
217 g_node_tbl node_data;
218
219 -- Stack for List of Procedures to be executed.
220 TYPE t_procedures is RECORD
221 (
222 procedure_name Varchar2(80),
223 cursor_handle pls_integer,
224 procedure_call Varchar2(32000)
225 );
226
227 TYPE t_proclist is TABLE of t_procedures index by BINARY_INTEGER;
228
229
230 -- Generic mapping of a Stored Procedure / Function
231 TYPE t_procmapping is RECORD
232 (
233 transtage_id pls_integer,
234 procedure_name ecx_tran_stage_data.custom_procedure_name%TYPE,
235 parameter_name ecx_proc_mappings.parameter_name%TYPE,
236 action_type pls_integer,
237 variable_level pls_integer,
238 variable_name ecx_proc_mappings.variable_name%TYPE,
239 variable_pos pls_integer,
240 variable_direction varchar2(10),
241 variable_constant varchar2(4000),
242 data_type pls_integer
243 );
244
245 /**
246 Stack for Procedure and their mappings to the Data Stack variables.
247 **/
248 TYPE t_procedure_mappings is table of t_procmapping index by BINARY_INTEGER;
249
250 -- Global parameter for a Stored procedure
251 g_procedure_list t_proclist;
252 g_procedure_mappings t_procedure_mappings;
253
254
255 /**
256 Global Document variables
257 **/
258
259 g_routing_id pls_integer := 0;
260 g_total_records pls_integer := 0;
261 g_insert_failed pls_integer := 0;
262 g_previous_level pls_integer := 0;
263 g_current_level pls_integer := 0;
264 g_document_id varchar2(2000):= NULL;
265 g_transaction_type varchar2(2000):= NULL;
266 g_transaction_subtype varchar2(2000):= NULL;
267 g_run_id pls_integer := 0;
268 g_map_id pls_integer := 0;
269 g_rec_tp_id pls_integer := null;
270 g_snd_tp_id pls_integer := null;
271 g_parser xmlparser.parser;
272 g_inb_parser xmlparser.parser;
273 dom_printing Boolean := FALSE;
274 structure_printing Boolean := FALSE;
275 g_xmldoc xmlDOM.DOMNode;
276 g_logfile varchar2(200);
277 g_msgid raw(16);
278 g_standard_id pls_integer;
279 g_org_id pls_integer;
280 g_company_name varchar2(2000) :=NULL;
281 g_logdir varchar2(2000);
282 g_install_mode varchar2(200);
283 g_event wf_event_t;
284 g_item_type varchar2(8);
285 g_item_key varchar2(240);
286 g_activity_id number;
287 g_tp_dtl_id pls_integer;
288
289 /**
290 Global Constants
291 **/
292 G_VARCHAR_LEN pls_integer := 32767;
293
294 /*
295 IMP NOTE: The limit of 16383 needs to be changed to 32767 once
296 we get a fix for bug# 2830478
297 */
298 G_CLOB_VARCHAR_LEN pls_integer := 16383;
299
300 TYPE xml_frag_rec is RECORD
301 (
302 variable_pos pls_integer,
303 value varchar2(32767)
304 );
305
306 TYPE xml_frag_tbl is TABLE of xml_frag_rec index by BINARY_INTEGER;
307
308 g_xml_frag xml_frag_tbl;
309 procedure close_process;
310
311 procedure initialize
312 (
313 i_map_id IN pls_integer,
314 x_same_map OUT NOCOPY Boolean
315 );
316
317 procedure load_dtd_nodes
318 (
319 i_map_id IN pls_integer,
320 i_level_id IN pls_integer,
321 i_object_level IN pls_integer,
322 i_source IN boolean
323 );
324
325 procedure load_procedure_mappings
326 (
327 i_map_id IN pls_integer
328 );
329
330 procedure load_procedure_definitions
331 (
332 i_map_id IN pls_integer
333 );
334
335 procedure build_procedure_call
336 (
337 p_transtage_id IN pls_integer,
338 p_procedure_name IN Varchar2,
339 x_proc_cursor OUT NOCOPY pls_integer
340 );
341
342 procedure get_tran_stage_data
343 (
344 i_map_id IN pls_integer
345 );
346
347 procedure load_mappings
348 (
349 i_map_id in pls_integer
350 );
351
352 procedure load_objects
353 (
354 i_map_id in pls_integer
355 );
356
357 procedure getLogDirectory;
358
359 Function GetFileSeparator
360 return varchar2;
361
362 Function GetLineSeparator
363 return varchar2;
364
365 Procedure set_error(
366 p_error_type in pls_integer default 10,
367 p_error_code in pls_integer default 0,
368 p_error_msg in varchar2 default null,
369 p_token1 in varchar2 default null,
370 p_value1 in varchar2 default null,
371 p_token2 in varchar2 default null,
372 p_value2 in varchar2 default null,
373 p_token3 in varchar2 default null,
374 p_value3 in varchar2 default null,
375 p_token4 in varchar2 default null,
376 p_value4 in varchar2 default null,
377 p_token5 in varchar2 default null,
378 p_value5 in varchar2 default null,
379 p_token6 in varchar2 default null,
380 p_value6 in varchar2 default null,
381 p_token7 in varchar2 default null,
382 p_value7 in varchar2 default null,
383 p_token8 in varchar2 default null,
384 p_value8 in varchar2 default null,
385 p_token9 in varchar2 default null,
386 p_value9 in varchar2 default null,
387 p_token10 in varchar2 default null,
388 p_value10 in varchar2 default null);
389
390
391 procedure convertPartyTypeToCode(
392 p_party_type IN Varchar2,
393 x_party_type OUT NOCOPY Varchar2);
394
395 function getNodePath(
396 v_map_id IN NUMBER,
397 v_attribute_id IN NUMBER) return varchar2;
398
399
400 Function XMLversion
401 return varchar2;
402
403 end ecx_utils;