[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_XDO_REPORT
Source
1 PACKAGE BODY PAY_NL_XDO_REPORT AS
2 /* $Header: paynlxdo.pkb 120.8 2012/01/19 08:16:37 rpahune ship $ */
3
4 /*-------------------------------------------------------------------------------
5 |Name : WritetoCLOB |
6 |Type : Procedure |
7 |Description : Writes contents of XML file as CLOB |
8 ------------------------------------------------------------------------------*/
9
10 PROCEDURE WritetoCLOB (p_xfdf_blob out nocopy blob) IS
11
12 l_xfdf_string clob;
13 l_str1 varchar2(1000);
14 l_str2 varchar2(20);
15 l_str3 varchar2(20);
16 l_str4 varchar2(20);
17 l_str5 varchar2(20);
18 l_str6 varchar2(30);
19 l_str7 varchar2(1000);
20 l_str8 varchar2(1000);
21 l_str9 varchar2(1000);
22
23 begin
24 hr_utility.set_location('Entered Procedure Write to clob ',100);
25 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
26 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
27 <fields> ' ;
28 l_str2 := '<field name="';
29 l_str3 := '">';
30 l_str4 := '<value>' ;
31 l_str5 := '</value> </field>' ;
32 l_str6 := '</fields> </xfdf>';
33 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
34 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
35 <fields>
36 </fields> </xfdf>';
37 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
38 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
39 if vXMLTable.count > 0 then
40 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
41 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
42 l_str8 := vXMLTable(ctr_table).TagName;
43 l_str9 := vXMLTable(ctr_table).TagValue;
44 if (l_str9 is not null) then
45 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
46 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
47 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
48 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
49 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
50 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
51 elsif (l_str9 is null and l_str8 is not null) then
52 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
53 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
54 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
55 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
56 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
57 else
58 null;
59 end if;
60 END LOOP;
61 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
62 else
63 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
64 end if;
65 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
66 clob_to_blob(l_xfdf_string,p_xfdf_blob);
67 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
68 --return p_xfdf_blob;
69 EXCEPTION
70 WHEN OTHERS then
71 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
72 HR_UTILITY.RAISE_ERROR;
73 END WritetoCLOB;
74
75 /*Function to support building of xml file compatible with RTF processor */
76 PROCEDURE WritetoCLOB_rtf(p_xfdf_blob out nocopy blob) IS
77
78 l_xfdf_string clob;
79 l_str0 varchar2(1000);
80 l_str1 varchar2(1000);
81 l_str2 varchar2(20);
82 l_str3 varchar2(20);
83 l_str4 varchar2(20);
84 l_str5 varchar2(20);
85 l_str6 varchar2(30);
86 l_str7 varchar2(1000);
87 l_str8 varchar2(1000);
88 l_str9 varchar2(1000);
89 l_str10 varchar2(1000);
90 l_concat_str VARCHAR2(32000);
91 begin
92 hr_utility.set_location('Entered Procedure Write to clob ',100);
93 l_str0 := '<?xml version="1.0" encoding="ISO-8859-1"?>';
94 l_str1 := '<fields>' ;
95 l_str2 := '<';
96 l_str3 := '>';
97 l_str4 := '<value>' ;
98 l_str5 := '</value> </' ;
99 l_str6 := '</fields>';
100 l_str7 := '<fields></fields>';
101 l_str10 := '</';
102 l_concat_str := '';
103 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
104 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
105 if vXMLTable.count > 0 then
106 --dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
107 l_concat_str := l_concat_str||l_str1;
108 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
109 l_str8 := vXMLTable(ctr_table).TagName;
110 l_str9 := vXMLTable(ctr_table).TagValue;
111
112 IF length(l_concat_str) > 28000 then
113 dbms_lob.writeAppend( l_xfdf_string, length(l_concat_str), l_concat_str);
114 l_concat_str := '';
115 END IF;
116
117 if (substr(l_str8,1,11) = 'G_CONTAINER') then
118 if (l_str9 is null) then
119 --dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
120 l_concat_str := l_concat_str||l_str2;
121 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
122 l_concat_str := l_concat_str||l_str8;
123 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
124 l_concat_str := l_concat_str||l_str3;
125 else
126 if (l_str9 = 'END') then
127 --dbms_lob.writeAppend( l_xfdf_string, length(l_str10), l_str10 );
128 l_concat_str := l_concat_str||l_str10;
129 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
130 l_concat_str := l_concat_str||l_str8;
131 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
132 l_concat_str := l_concat_str||l_str3;
133 end if;
134 end if;
135 else
136 if (l_str9 is not null) then
137 --dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
138 l_concat_str := l_concat_str||l_str2;
139 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
140 l_concat_str := l_concat_str||l_str8;
141 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
142 l_concat_str := l_concat_str||l_str3;
143 --dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
144 l_concat_str := l_concat_str||l_str4;
145 --dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
146 l_concat_str := l_concat_str||l_str9;
147 --dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
148 l_concat_str := l_concat_str||l_str5;
149 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
150 l_concat_str := l_concat_str||l_str8;
151 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3),l_str3);
152 l_concat_str := l_concat_str||l_str3;
153 elsif (l_str9 is null and l_str8 is not null) then
154 --dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
155 l_concat_str := l_concat_str||l_str2;
156 --dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
157 l_concat_str := l_concat_str||l_str8;
158 --dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
159 l_concat_str := l_concat_str||l_str3;
160 --dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
161 l_concat_str := l_concat_str||l_str4;
162 --dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
163 l_concat_str := l_concat_str||l_str5;
164 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
165 l_concat_str := l_concat_str||l_str8;
166 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3),l_str3);
167 l_concat_str := l_concat_str||l_str3;
168 else
169 null;
170 end if;
171 end if;
172 END LOOP;
173
174 IF length(l_concat_str) > 0 THEN
175 dbms_lob.writeAppend( l_xfdf_string, LENGTH(l_concat_str), l_concat_str);
176 END IF;
177
178 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
179 else
180 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
181 end if;
182 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
183 clob_to_blob(l_xfdf_string,p_xfdf_blob);
184 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
185 --return p_xfdf_blob;
186 EXCEPTION
187 WHEN OTHERS then
188 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
189 HR_UTILITY.RAISE_ERROR;
190 END WritetoCLOB_rtf;
191
192 /*Function which retruns a CLOB to support building of xml file compatible with RTF processor */
193 PROCEDURE WritetoCLOB_rtf_1(p_xfdf_blob out nocopy clob) IS
194
195 l_xfdf_string clob;
196 l_str0 varchar2(1000);
197 l_str1 varchar2(1000);
198 l_str2 varchar2(20);
199 l_str3 varchar2(20);
200 l_str4 varchar2(20);
201 l_str5 varchar2(20);
202 l_str6 varchar2(30);
203 l_str7 varchar2(1000);
204 l_str8 varchar2(1000);
205 l_str9 varchar2(1000);
206 l_str10 varchar2(1000);
207 l_concat_str VARCHAR2(32000);
208 begin
209 hr_utility.set_location('Entered Procedure Write to clob ',100);
210 l_str0 := '<?xml version="1.0" encoding="ISO-8859-1"?>';
211 l_str1 := '<fields>' ;
212 l_str2 := '<';
213 l_str3 := '>';
214 l_str4 := '<value>' ;
215 l_str5 := '</value> </' ;
216 l_str6 := '</fields>';
217 l_str7 := '<fields></fields>';
218 l_str10 := '</';
219 l_concat_str := '';
220 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
221 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
222 if vXMLTable.count > 0 then
223 --dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
224
225 --l_concat_str := l_concat_str||l_str2||l_str1;
226 --Bug 6630722
227 l_concat_str := l_concat_str||l_str1;
228
229 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
230 l_str8 := vXMLTable(ctr_table).TagName;
231 l_str9 := vXMLTable(ctr_table).TagValue;
232
233 IF length(l_concat_str) > 28000 then
234 dbms_lob.writeAppend( l_xfdf_string, length(l_concat_str), l_concat_str);
235 l_concat_str := '';
236 END IF;
237
238 if (substr(l_str8,1,11) = 'G_CONTAINER') then
239 if (l_str9 is null) then
240 --dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
241 l_concat_str := l_concat_str||l_str2;
242 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
243 l_concat_str := l_concat_str||l_str8;
244 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
245 l_concat_str := l_concat_str||l_str3;
246 else
247 if (l_str9 = 'END') then
248 --dbms_lob.writeAppend( l_xfdf_string, length(l_str10), l_str10 );
249 l_concat_str := l_concat_str||l_str10;
250 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
251 l_concat_str := l_concat_str||l_str8;
252 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
253 l_concat_str := l_concat_str||l_str3;
254 end if;
255 end if;
256 else
257 if (l_str9 is not null) then
258 --dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
259 l_concat_str := l_concat_str||l_str2;
260 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
261 l_concat_str := l_concat_str||l_str8;
262 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
263 l_concat_str := l_concat_str||l_str3;
264 --dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
265 l_concat_str := l_concat_str||l_str4;
266 --dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
267 l_concat_str := l_concat_str||l_str9;
268 --dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
269 l_concat_str := l_concat_str||l_str5;
270 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
271 l_concat_str := l_concat_str||l_str8;
272 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3),l_str3);
273 l_concat_str := l_concat_str||l_str3;
274 elsif (l_str9 is null and l_str8 is not null) then
275 --dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
276 l_concat_str := l_concat_str||l_str2;
277 --dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
278 l_concat_str := l_concat_str||l_str8;
279 --dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
280 l_concat_str := l_concat_str||l_str3;
281 --dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
282 l_concat_str := l_concat_str||l_str4;
283 --dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
284 l_concat_str := l_concat_str||l_str5;
285 --dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
286 l_concat_str := l_concat_str||l_str8;
287 --dbms_lob.writeAppend( l_xfdf_string, length(l_str3),l_str3);
288 l_concat_str := l_concat_str||l_str3;
289 else
290 null;
291 end if;
292 end if;
293 END LOOP;
294
295 IF length(l_concat_str) > 0 THEN
296 dbms_lob.writeAppend( l_xfdf_string, LENGTH(l_concat_str), l_concat_str);
297 END IF;
298
299 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
300 else
301 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
302 end if;
303 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
304 p_xfdf_blob := l_xfdf_string;
305 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
306 --return p_xfdf_blob;
307 EXCEPTION
308 WHEN OTHERS then
309 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
310 HR_UTILITY.RAISE_ERROR;
311 END WritetoCLOB_rtf_1;
312
313
314 /*Converts CLOB data to BLOB*/
315
316
317 /*-------------------------------------------------------------------------------
318 |Name : clob_to_blob |
319 |Type : Procedure |
320 |Description : Converts XMLfile currently a CLOB to a BLOB |
321 ------------------------------------------------------------------------------*/
322
323
324 PROCEDURE clob_to_blob(p_clob CLOB
325 ,p_blob IN OUT NOCOPY BLOB) IS
326 l_length_clob NUMBER;
327 l_offset integer;
328 --l_varchar_buffer VARCHAR2(10666); --11835544
329 l_varchar_buffer VARCHAR2(32767); --11835544
330 l_raw_buffer RAW(32000);
331 l_buffer_len NUMBER;
332 l_chunk_len NUMBER;
333 l_blob BLOB;
334 l_db_nls_lang VARCHAR2(200);
335 --
336 l_raw_buffer_len pls_integer;
337 l_blob_offset pls_integer := 1;
338 --
339 begin
340 hr_utility.set_location('Entered Procedure clob to blob',120);
341 l_db_nls_lang := userenv('LANGUAGE');
342 l_length_clob := dbms_lob.getlength(p_clob);
343 l_buffer_len := 10666;
344 l_offset := 1;
345 l_blob_offset := 1;
346 WHILE l_length_clob > 0 LOOP
347
348 IF l_length_clob < l_buffer_len THEN
349 l_chunk_len := l_length_clob;
350 ELSE
351 l_chunk_len := l_buffer_len;
352 END IF;
353 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
354 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',l_db_nls_lang);
355 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',l_db_nls_lang));
356 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
357 --
358 l_blob_offset := l_blob_offset + l_raw_buffer_len;
359 l_offset := l_offset + l_chunk_len;
360 l_length_clob := l_length_clob - l_chunk_len;
361 END LOOP;
362 hr_utility.set_location('Finished Procedure clob to blob ',130);
363 END;
364
365
366 /*Returns template file as a BLOB*/
367
368
369 /*-------------------------------------------------------------------------------
370 |Name : fetch_pdf_blob |
371 |Type : Procedure |
372 |Description : fetches template file as a BLOB |
373 ------------------------------------------------------------------------------*/
374
375 Procedure fetch_pdf_blob(p_year varchar2,p_template_id number,p_pdf_blob OUT NOCOPY BLOB) IS
376
377 BEGIN
378
379 Select file_data Into p_pdf_blob
380 From fnd_lobs
381 Where file_id = (select file_id from per_gb_xdo_templates
382 where file_id=p_template_id and
386 null;
383 fnd_date.canonical_to_date(p_year) between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE);
384 EXCEPTION
385 when no_data_found then
387 END fetch_pdf_blob;
388
389
390
391
392 /*-------------------------------------------------------------------------------
393 |Name : WritetoXML |
394 |Type : Procedure |
395 |Description : Procedure to write the xml to a file. Used for debugging |
396 | purposes |
397 ------------------------------------------------------------------------------*/
398
399
400 PROCEDURE WritetoXML (
401 p_request_id in number,
402 p_output_fname out nocopy varchar2)
403 IS
404 p_l_fp UTL_FILE.FILE_TYPE;
405 l_audit_log_dir varchar2(500) := '/sqlcom/outbound';
406 l_file_name varchar2(50);
407 l_check_flag number;
408 BEGIN
409 /*Msg in the temorary table*/
410 --insert into tstmsg values('Entered the procedure WritetoXML.');
411 -----------------------------------------------------------------------------
412 -- Writing into XML File
413 -----------------------------------------------------------------------------
414 -- Assigning the File name.
415 l_file_name := to_char(p_request_id) || '.xml';
416 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
417 BEGIN
418
419
420 SELECT value
421 INTO l_audit_log_dir
422 FROM v$parameter
423 WHERE LOWER(name) = 'utl_file_dir';
424 -- Check whether more than one util file directory is found
425 IF INSTR(l_audit_log_dir,',') > 0 THEN
426 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
427 END IF;
428 EXCEPTION
429 when no_data_found then
430 null;
431 END;
432 -- Find out whether the OS is MS or Unix based
433 -- If it's greater than 0, it's unix based environment
434 IF INSTR(l_audit_log_dir,'/') > 0 THEN
435 p_output_fname := l_audit_log_dir || '/' || l_file_name;
436 ELSE
437 p_output_fname := l_audit_log_dir || '\' || l_file_name;
438 END IF;
439 -- getting Agency name
440 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A');
441 utl_file.put_line(p_l_fp,'<?xml version="1.0" encoding="UTF-8"?>');
442 utl_file.put_line(p_l_fp,'<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">');
443 -- Writing from and to dates
444 utl_file.put_line(p_l_fp,'<fields>');
445 -- Write the header fields to XML File.
446 --WriteXMLvalues(p_l_fp,'P0_from_date',to_char(p_from_date,'dd') || ' ' || trim(to_char(p_from_date,'Month')) || ' ' || to_char(p_from_date,'yyyy') );
447 --WriteXMLvalues(p_l_fp,'P0_to_date',to_char(p_to_date,'dd') || ' ' ||to_char(p_to_date,'Month') || ' ' || to_char(p_to_date,'yyyy') );
448 -- Loop through PL/SQL Table and write the values into the XML File.
449 -- Need to try FORALL instead of FOR
450 IF vXMLTable.count >0 then
451
452 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
453
454
455 WriteXMLvalues(p_l_fp,vXMLTable(ctr_table).TagName,vXMLTable(ctr_table).TagValue);
456 END LOOP;
457 END IF;
458 -- Write the end tag and close the XML File.
459 utl_file.put_line(p_l_fp,'</fields>');
460 utl_file.put_line(p_l_fp,'</xfdf>');
461 utl_file.fclose(p_l_fp);
462 /*Msg in the temorary table*/
463 --insert into tstmsg values('Leaving the procedure WritetoXML.');
464 END WritetoXML;
465
466
467 /*-------------------------------------------------------------------------------
468 |Name : WriteXMLvalues |
469 |Type : Procedure |
470 |Description : Procedure to write the xml values. Used for debugging |
471 ------------------------------------------------------------------------------*/
472
473
474 PROCEDURE WriteXMLvalues( p_l_fp utl_file.file_type,p_tagname IN VARCHAR2, p_value IN VARCHAR2) IS
475 BEGIN
476 -- Writing XML Tag and values to XML File
477 -- utl_file.put_line(p_l_fp,'<' || p_tagname || '>' || p_value || '</' || p_tagname || '>' );
478 -- New Format XFDF
479 utl_file.put_line(p_l_fp,'<field name="' || p_tagname || '">');
480 utl_file.put_line(p_l_fp,'<value>' || p_value || '</value>' );
481 utl_file.put_line(p_l_fp,'</field>');
482 END WriteXMLvalues;
483
484 /*-------------------------------------------------------------------------------
485 |Name : WritetoXML_rtf |
486 |Type : Procedure |
487 |Description : Procedure to write the xml to a file. Used for debugging |
488 | purposes |
489 ------------------------------------------------------------------------------*/
490
491
492 /*Function to support building of xml file compatible with RTF processor */
493 PROCEDURE WritetoXML_rtf (
494 p_request_id in number,
495 p_output_fname out nocopy varchar2)
496 IS
497 p_l_fp UTL_FILE.FILE_TYPE;
498 l_audit_log_dir varchar2(500) := '/sqlcom/outbound';
499 l_file_name varchar2(50);
500 l_check_flag number;
501 l_concat_str VARCHAR2(32000);
502 BEGIN
503 /*Msg in the temorary table*/
507 -----------------------------------------------------------------------------
504 --insert into tstmsg values('Entered the procedure WritetoXML.');
505 -----------------------------------------------------------------------------
506 -- Writing into XML File
508 -- Assigning the File name.
509 l_file_name := to_char(p_request_id) || '.xml';
510 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
511 BEGIN
512
513
514 SELECT value
515 INTO l_audit_log_dir
516 FROM v$parameter
517 WHERE LOWER(name) = 'utl_file_dir';
518 -- Check whether more than one util file directory is found
519 IF INSTR(l_audit_log_dir,',') > 0 THEN
520 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
521 END IF;
522 EXCEPTION
523 when no_data_found then
524 null;
525 END;
526 -- Find out whether the OS is MS or Unix based
527 -- If it's greater than 0, it's unix based environment
528 IF INSTR(l_audit_log_dir,'/') > 0 THEN
529 p_output_fname := l_audit_log_dir || '/' || l_file_name;
530 ELSE
531 p_output_fname := l_audit_log_dir || '\' || l_file_name;
532 END IF;
533 -- getting Agency name
534 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A',32000);
535 -- Writing from and to dates
536 l_concat_str := '<?xml version="1.0" encoding="ISO-8859-1"?>';
537 l_concat_str := l_concat_str||'<fields>';
538 --utl_file.put_line(p_l_fp,'<fields>');
539 -- Write the header fields to XML File.
540 --WriteXMLvalues(p_l_fp,'P0_from_date',to_char(p_from_date,'dd') || ' ' || trim(to_char(p_from_date,'Month')) || ' ' || to_char(p_from_date,'yyyy') );
541 --WriteXMLvalues(p_l_fp,'P0_to_date',to_char(p_to_date,'dd') || ' ' ||to_char(p_to_date,'Month') || ' ' || to_char(p_to_date,'yyyy') );
542 -- Loop through PL/SQL Table and write the values into the XML File.
543 -- Need to try FORALL instead of FOR
544 IF vXMLTable.count >0 then
545
546 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
547
548 IF length(l_concat_str) > 28000 THEN
549
550 utl_file.put_line(p_l_fp,l_concat_str);
551 l_concat_str := '';
552
553 END IF;
554
555 IF substr(vXMLTable(ctr_table).TagName,1,11)='G_CONTAINER' THEN
556
557 IF vXMLTable(ctr_table).TagValue is null THEN
558 l_concat_str := l_concat_str||'<'||vXMLTable(ctr_table).TagName||'>';
559 ELSIF vXMLTable(ctr_table).TagValue='END' THEN
560 l_concat_str := l_concat_str||'</'||vXMLTable(ctr_table).TagName||'>';
561 END IF;
562
563 ELSE
564
565 l_concat_str := l_concat_str||'<'||vXMLTable(ctr_table).TagName||'>';
566 l_concat_str := l_concat_str||'<value>'||vXMLTable(ctr_table).TagValue||'</value>';
567 l_concat_str := l_concat_str||'</'||vXMLTable(ctr_table).TagName||'>';
568
569 END IF;
570 --WriteXMLvalues_rtf(p_l_fp,vXMLTable(ctr_table).TagName,vXMLTable(ctr_table).TagValue);
571 END LOOP;
572 END IF;
573 IF length(l_concat_str) > 0 THEN
574 utl_file.put_line(p_l_fp,l_concat_str);
575 END IF;
576 -- Write the end tag and close the XML File.
577 utl_file.put_line(p_l_fp,'</fields>');
578 utl_file.fclose(p_l_fp);
579 /*Msg in the temorary table*/
580 --insert into tstmsg values('Leaving the procedure WritetoXML.');
581 END WritetoXML_rtf;
582
583 /*Function to support building of xml file compatible with RTF processor */
584 PROCEDURE WriteXMLvalues_rtf( p_l_fp utl_file.file_type,p_tagname IN VARCHAR2, p_value IN VARCHAR2) IS
585 BEGIN
586 -- Writing XML Tag and values to XML File
587 -- utl_file.put_line(p_l_fp,'<' || p_tagname || '>' || p_value || '</' || p_tagname || '>' );
588 -- New Format XFDF
589 utl_file.put_line(p_l_fp,'<' || p_tagname || '>');
590 utl_file.put_line(p_l_fp,'<value>' || p_value || '</value>' );
591 utl_file.put_line(p_l_fp,'</' || p_tagname || '>');
592 END WriteXMLvalues_rtf;
593
594
595
596 END PAY_NL_XDO_REPORT;