[Home] [Help]
PACKAGE BODY: APPS.EC_XML_UTILS
Source
1 PACKAGE BODY EC_XML_UTILS AS
2 -- $Header: ECXMLUTB.pls 115.3 99/08/23 15:40:22 porting ship $
3
4 cursor get_transaction_type
5 (
6 p_map_id IN number
7 )
8 is
9 select transaction_type
10 from ece_mappings
11 where map_id = p_map_id;
12
13 cursor get_external_entities
14 (
15 p_transaction_type IN varchar2,
16 p_map_id IN number
17 )
18 is
19 select start_element,
20 external_level,
21 external_level_id,
22 parent_level
23 from ece_external_levels
24 where transaction_type = p_transaction_type
25 and map_id = p_map_id
26 start with parent_level = 0
27 connect by prior external_level_id = parent_level;
28
29 cursor get_external_attributes
30 (
31 p_map_id IN number,
32 p_external_level in number
33 )
34 is
35 select interface_column_name,
36 staging_column,
37 element_tag_name
38 from ece_interface_columns
39 where map_id = p_map_id
40 and external_level = p_external_level
41 and staging_column is not null
42 and element_tag_name is not null
43 order by interface_column_id;
44
45 cursor get_root
46 (
47 p_transaction_type IN varchar2,
48 p_map_id IN number
49 )
50 is
51 select root_element
52 from ece_mappings
53 where transaction_type = p_transaction_type
54 and map_id = p_map_id;
55
56 procedure create_vo_tree
57 (
58 i_map_id IN number,
59 i_run_id IN number
60 )
61 is
62 i_transaction_type ece_mappings.transaction_type%TYPE;
63 i_string varchar2(32000);
64 i_plural_name varchar2(2000);
65 i_singular_name varchar2(2000);
66 i_where_clause varchar2(2000);
67 i_entity_object varchar2(2000);
68 i_select_clause varchar2(32000);
69 i_orderby_clause varchar2(2000);
70 i_from_clause varchar2(2000);
71 i_attributes_to_hide varchar2(2000);
72 i_product_code varchar2(2000);
73 i_apps_error_code varchar2(2000);
74 i_parameter0 varchar2(2000);
75 i_parameter1 varchar2(2000);
76 i_parameter2 varchar2(2000);
77 i_parameter3 varchar2(2000);
78 i_parameter4 varchar2(2000);
79 i_error_string varchar2(32000);
80 i_parent_data_object varchar2(2000);
81 i_bind_parent_attributealiases varchar2(2000);
82 i_bind_child_attributealiases varchar2(2000);
83 i_bind_child_attributecolumns varchar2(2000);
84 i_parent_level pls_integer;
85 i_prv_parent_level pls_integer :=0;
86 i_prv_parent_data_object varchar2(2000);
87 begin
88 ec_debug.push('EC_XMLS.CREATE_VO_TREE');
89 ec_debug.pl(3,'i_map_id',i_map_id);
90 ec_debug.pl(3,'i_run_id',i_run_id);
91
92 for ctran in get_transaction_type
93 (
94 p_map_id => i_map_id
95 )
96 loop
97 i_transaction_type := ctran.transaction_type;
98 ec_debug.pl(3,'Transaction Type ',ctran.transaction_type);
99 end loop;
100
101 for c1 in get_root
102 (
103 p_transaction_type => i_transaction_type,
104 p_map_id => i_map_id
105 )
106 loop
107 ec_debug.pl(3,'Root Element',c1.root_element);
108 end loop;
109
110
111
112 for c2 in get_external_entities
113 (
114 p_transaction_type => i_transaction_type,
115 p_map_id => i_map_id
116 )
117 loop
118 i_string := 'select ';
119 i_singular_name := c2.start_element;
120 i_plural_name := c2.start_element||'S';
121 i_from_clause := 'ECE_STAGE';
122 i_orderby_clause := 'order by stage_id';
123
124
125 if c2.external_level = 1
126 then
127 i_bind_child_attributecolumns := null;
128 i_where_clause := 'run_id = '||i_run_id;
129 else
130 i_bind_child_attributecolumns := 'parent_stage_id';
131 i_bind_parent_attributealiases := 'stage_id';
132 i_where_clause := null;
133 end if;
134
135 if i_prv_parent_level = i_parent_level
136 then
137 i_parent_data_object := i_prv_parent_data_object;
138 else
139 i_parent_data_object := i_plural_name;
140 end if;
141
142 ec_debug.pl(3,'External_level'||c2.external_level,c2.start_element);
143 ec_debug.pl(3,'External_level_id',c2.external_level_id);
144 for c3 in get_external_attributes
145 (
146 p_map_id => i_map_id,
147 p_external_level => c2.external_level
148 )
149 loop
150 i_string := i_string||c3.staging_column;
151 if c3.element_tag_name is not null
152 then
153 i_string := i_string||' AS '||c3.element_tag_name;
154 end if;
155 i_string := i_string ||',';
156 end loop;
157 i_select_clause := substrb(i_string,1,length(i_string)-1);
158
159 if c2.external_level = 1
160 then
161 create_toplevel_vo
162 (
163 i_singular_name,
164 i_plural_name,
165 i_entity_object,
166 i_select_clause,
167 i_from_clause,
168 i_where_clause,
169 i_orderby_clause,
170 i_attributes_to_hide,
171 i_product_code,
172 i_apps_error_code,
173 i_parameter0,
174 i_parameter1,
175 i_parameter2,
176 i_parameter3,
177 i_parameter4,
178 i_error_string
179 );
180
181 else
182 create_child_vo
183 (
184 i_singular_name,
185 i_plural_name,
186 i_entity_object,
187 i_select_clause,
188 i_from_clause,
189 i_where_clause,
190 i_orderby_clause,
191 i_attributes_to_hide,
192 i_parent_data_object,
193 i_bind_parent_attributealiases,
194 i_bind_child_attributealiases,
195 i_bind_child_attributecolumns,
196 i_product_code,
197 i_apps_error_code,
198 i_parameter0,
199 i_parameter1,
200 i_parameter2,
201 i_parameter3,
202 i_parameter4,
203 i_error_string
204 );
205 end if;
206
207 i_prv_parent_level := c2.parent_level;
208 i_prv_parent_data_object := i_plural_name;
209
210 end loop;
211 ec_debug.pop('EC_XMLS.CREATE_VO_TREE');
212 EXCEPTION
213 WHEN EC_UTILS.PROGRAM_EXIT then
214 raise;
215 WHEN OTHERS THEN
216 ec_debug.pl
217 (
218 0,i_product_code,i_apps_error_code,'PARAMETER0',i_parameter0,
219 'PARAMETER1',i_parameter1,
220 'PARAMETER2',i_parameter2,
221 'PARAMETER3',i_parameter3,
222 'PARAMETER4',i_parameter4
223 );
224 ec_debug.pl(0,'EC','EC_JAVA_ERROR',i_error_string);
225 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_XMLS.CREATE_VO_TREE');
226 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
227 ec_utils.i_ret_code :=2;
228 raise EC_UTILS.PROGRAM_EXIT;
229 end create_vo_tree;
230
231 /**
232 Stubs for Java API's
233 **/
234
235 procedure create_toplevel_vo
236 (
237 i_singular_name IN varchar2,
238 i_plural_name IN varchar2,
239 i_entity_object IN varchar2,
240 i_select_clause IN varchar2,
241 i_from_clause IN varchar2,
242 i_where_clause IN varchar2,
243 i_orderby_clause IN varchar2,
244 i_attributes_to_hide IN varchar2,
245 i_product_code OUT varchar2,
246 i_apps_error_code OUT varchar2,
247 i_parameter0 OUT varchar2,
248 i_parameter1 OUT varchar2,
249 i_parameter2 OUT varchar2,
250 i_parameter3 OUT varchar2,
251 i_parameter4 OUT varchar2,
252 i_error_string OUT varchar2
253 )
254 is
255 begin
256 null;
257 end;
258
259 procedure create_child_vo
260 (
261 i_singular_name IN varchar2,
262 i_plural_name IN varchar2,
263 i_entity_object IN varchar2,
264 i_select_clause IN varchar2,
265 i_from_clause IN varchar2,
266 i_where_clause IN varchar2,
267 i_orderby_clause IN varchar2,
268 i_attributes_to_hide IN varchar2,
269 i_parent_data_object IN varchar2,
270 i_bind_parent_attributealiases IN varchar2,
271 i_bind_child_attributealiases IN varchar2,
272 i_bind_child_attributecolumns IN varchar2,
273 i_product_code OUT varchar2,
274 i_apps_error_code OUT varchar2,
275 i_parameter0 OUT varchar2,
276 i_parameter1 OUT varchar2,
277 i_parameter2 OUT varchar2,
278 i_parameter3 OUT varchar2,
279 i_parameter4 OUT varchar2,
280 i_error_string OUT varchar2
281 )
282 is
283 begin
284 null;
285 end;
286
287 procedure produce_xml
288 (
289 i_file_name IN varchar2,
290 i_product_code OUT varchar2,
291 i_apps_error_code OUT varchar2,
292 i_parameter0 OUT varchar2,
293 i_parameter1 OUT varchar2,
294 i_parameter2 OUT varchar2,
295 i_parameter3 OUT varchar2,
296 i_parameter4 OUT varchar2,
297 i_error_string OUT varchar2
298 )
299 is
300 begin
301 null;
302 end;
303
304 procedure consume_xml
305 (
306 i_file_name IN varchar2,
307 i_apps_error_code OUT varchar2,
308 i_parameter0 OUT varchar2,
309 i_parameter1 OUT varchar2,
310 i_parameter2 OUT varchar2,
311 i_parameter3 OUT varchar2,
312 i_parameter4 OUT varchar2,
313 i_error_string OUT varchar2,
314 i_viewolistner_class IN varchar2
315 )
316 as
317 begin
318 null;
319 end;
320
321 PROCEDURE ec_xml_processor_out_generic
322 (
323 c_map_id IN PLS_INTEGER,
324 c_run_id IN PLS_INTEGER,
325 c_output_path IN VARCHAR2,
326 c_file_name IN VARCHAR2
327 )
328 IS
329 i_product_code varchar2(2000);
330 i_apps_error_code varchar2(2000);
331 i_parameter0 varchar2(2000);
332 i_parameter1 varchar2(2000);
333 i_parameter2 varchar2(2000);
334 i_parameter3 varchar2(2000);
335 i_parameter4 varchar2(2000);
336 i_error_string varchar2(32000);
337 BEGIN
338 ec_debug.push('EC_XML_UTILS.EC_XML_PROCESSOR_OUT_GENERIC');
339 ec_debug.pl(3,'c_map_id',c_map_id);
340 ec_debug.pl(3,'c_run_id',c_run_id);
341 ec_debug.pl(3,'c_output_path',c_output_path);
342 ec_debug.pl(3,'c_file_name',c_file_name);
343
344 create_vo_tree
345 (
346 c_map_id,
347 c_run_id
348 );
349
350 produce_xml
351 (
352 c_output_path||'/'||c_file_name,
353 i_product_code,
354 i_apps_error_code,
355 i_parameter0,
356 i_parameter1,
357 i_parameter2,
358 i_parameter3,
359 i_parameter4,
360 i_error_string
361 );
362
363 ec_debug.pop('EC_XML_UTILS.EC_XML_PROCESSOR_OUT_GENERIC');
364 EXCEPTION
365 WHEN EC_UTILS.PROGRAM_EXIT then
366 raise;
367 WHEN OTHERS THEN
368 ec_debug.pl
369 (
370 0,i_product_code,i_apps_error_code,'PARAMETER0',i_parameter0,
371 'PARAMETER1',i_parameter1,
372 'PARAMETER2',i_parameter2,
373 'PARAMETER3',i_parameter3,
374 'PARAMETER4',i_parameter4
375 );
376 ec_debug.pl(0,'EC','EC_JAVA_ERROR',i_error_string);
377 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_XML_UTILS.XML_PROCESSOR_OUT_GENERIC');
378 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
379 ec_utils.i_ret_code :=2;
380 raise EC_UTILS.PROGRAM_EXIT;
381
382 END ec_xml_processor_out_generic;
383
384 PROCEDURE ec_xml_processor_in_generic
385 (
386 c_map_id IN PLS_INTEGER,
387 c_run_id OUT PLS_INTEGER,
388 c_output_path IN VARCHAR2,
389 c_file_name IN VARCHAR2
390 )
391 IS
392 i_product_code varchar2(2000);
393 i_apps_error_code varchar2(2000);
394 i_parameter0 varchar2(2000);
395 i_parameter1 varchar2(2000);
396 i_parameter2 varchar2(2000);
397 i_parameter3 varchar2(2000);
398 i_parameter4 varchar2(2000);
399 i_error_string varchar2(32000);
400 i_viewolistner_class varchar2(2000);
401 BEGIN
402 ec_debug.push('EC_XML_UTILS.EC_XML_PROCESSOR_IN_GENERIC');
403 ec_debug.pl(3,'c_map_id',c_map_id);
404 ec_debug.pl(3,'c_output_path',c_output_path);
405 ec_debug.pl(3,'c_file_name',c_file_name);
406 /**
407 If the program is run from SQLplus , the Concurrent Request id is
408 < 0. In this case , get the run id from ECE_OUTPUT_RUNS_S.NEXTVAL.
409 **/
410 c_run_id := fnd_global.conc_request_id;
411 if c_run_id <= 0
412 then
413 select ece_output_runs_s.NEXTVAL
414 into c_run_id
415 from dual;
416 end if;
417
418 ec_debug.pl(3,'Run Id for the Transaction',c_run_id);
419
420 create_vo_tree
421 (
422 c_map_id,
423 c_run_id
424 );
425
426 consume_xml
427 (
428 c_output_path||'/'||c_file_name,
429 i_apps_error_code,
430 i_parameter0,
431 i_parameter1,
432 i_parameter2,
433 i_parameter3,
434 i_parameter4,
435 i_error_string,
436 i_viewolistner_class
437 );
438
439 ec_debug.pl(3,'c_run_id',c_run_id);
440 ec_debug.pop('EC_XML_UTILS.EC_XML_PROCESSOR_IN_GENERIC');
441 EXCEPTION
442 WHEN EC_UTILS.PROGRAM_EXIT then
443 raise;
444 WHEN OTHERS THEN
445 ec_debug.pl
446 (
447 0,i_product_code,i_apps_error_code,'PARAMETER0',i_parameter0,
448 'PARAMETER1',i_parameter1,
449 'PARAMETER2',i_parameter2,
450 'PARAMETER3',i_parameter3,
451 'PARAMETER4',i_parameter4
452 );
453 ec_debug.pl(0,'EC','EC_JAVA_ERROR',i_error_string);
454 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_XML_UTILS.EC_XML_PROCESSOR_IN_GENERIC');
455 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
456 ec_utils.i_ret_code :=2;
457 raise EC_UTILS.PROGRAM_EXIT;
458 END ec_xml_processor_in_generic;
459
460 END EC_XML_UTILS;
461