[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_XDO_REPORT
Source
1 PACKAGE BODY PAY_NL_XDO_REPORT AS
2 /* $Header: paynlxdo.pkb 120.3.12000000.4 2007/11/15 16:17:09 rsahai noship $ */
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);
313
310 HR_UTILITY.RAISE_ERROR;
311 END WritetoCLOB_rtf_1;
312
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);
329 l_raw_buffer RAW(32000);
330 l_buffer_len NUMBER;
331 l_chunk_len NUMBER;
332 l_blob BLOB;
333 l_db_nls_lang VARCHAR2(200);
334 --
335 l_raw_buffer_len pls_integer;
336 l_blob_offset pls_integer := 1;
337 --
338 begin
339 hr_utility.set_location('Entered Procedure clob to blob',120);
340 l_db_nls_lang := userenv('LANGUAGE');
341 l_length_clob := dbms_lob.getlength(p_clob);
342 l_buffer_len := 10666;
343 l_offset := 1;
344 l_blob_offset := 1;
345 WHILE l_length_clob > 0 LOOP
346
347 IF l_length_clob < l_buffer_len THEN
348 l_chunk_len := l_length_clob;
349 ELSE
350 l_chunk_len := l_buffer_len;
351 END IF;
352 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
353 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',l_db_nls_lang);
354 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));
355 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
356 --
357 l_blob_offset := l_blob_offset + l_raw_buffer_len;
358 l_offset := l_offset + l_chunk_len;
359 l_length_clob := l_length_clob - l_chunk_len;
360 END LOOP;
361 hr_utility.set_location('Finished Procedure clob to blob ',130);
362 END;
363
364
365 /*Returns template file as a BLOB*/
366
367
368 /*-------------------------------------------------------------------------------
369 |Name : fetch_pdf_blob |
370 |Type : Procedure |
371 |Description : fetches template file as a BLOB |
372 ------------------------------------------------------------------------------*/
373
374 Procedure fetch_pdf_blob(p_year varchar2,p_template_id number,p_pdf_blob OUT NOCOPY BLOB) IS
375
376 BEGIN
377
378 Select file_data Into p_pdf_blob
379 From fnd_lobs
380 Where file_id = (select file_id from per_gb_xdo_templates
381 where file_id=p_template_id and
382 fnd_date.canonical_to_date(p_year) between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE);
383 EXCEPTION
384 when no_data_found then
385 null;
386 END fetch_pdf_blob;
387
388
389
390
391 /*-------------------------------------------------------------------------------
392 |Name : WritetoXML |
393 |Type : Procedure |
394 |Description : Procedure to write the xml to a file. Used for debugging |
395 | purposes |
396 ------------------------------------------------------------------------------*/
397
398
399 PROCEDURE WritetoXML (
400 p_request_id in number,
401 p_output_fname out nocopy varchar2)
402 IS
403 p_l_fp UTL_FILE.FILE_TYPE;
404 l_audit_log_dir varchar2(500) := '/sqlcom/outbound';
405 l_file_name varchar2(50);
406 l_check_flag number;
407 BEGIN
408 /*Msg in the temorary table*/
409 --insert into tstmsg values('Entered the procedure WritetoXML.');
410 -----------------------------------------------------------------------------
411 -- Writing into XML File
412 -----------------------------------------------------------------------------
413 -- Assigning the File name.
414 l_file_name := to_char(p_request_id) || '.xml';
415 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
416 BEGIN
417
418
419 SELECT value
420 INTO l_audit_log_dir
421 FROM v$parameter
422 WHERE LOWER(name) = 'utl_file_dir';
423 -- Check whether more than one util file directory is found
424 IF INSTR(l_audit_log_dir,',') > 0 THEN
425 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
426 END IF;
427 EXCEPTION
428 when no_data_found then
429 null;
430 END;
431 -- Find out whether the OS is MS or Unix based
432 -- If it's greater than 0, it's unix based environment
433 IF INSTR(l_audit_log_dir,'/') > 0 THEN
434 p_output_fname := l_audit_log_dir || '/' || l_file_name;
435 ELSE
436 p_output_fname := l_audit_log_dir || '\' || l_file_name;
437 END IF;
438 -- getting Agency name
439 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A');
440 utl_file.put_line(p_l_fp,'<?xml version="1.0" encoding="UTF-8"?>');
444 -- Write the header fields to XML File.
441 utl_file.put_line(p_l_fp,'<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">');
442 -- Writing from and to dates
443 utl_file.put_line(p_l_fp,'<fields>');
445 --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') );
446 --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') );
447 -- Loop through PL/SQL Table and write the values into the XML File.
448 -- Need to try FORALL instead of FOR
449 IF vXMLTable.count >0 then
450
451 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
452
453
454 WriteXMLvalues(p_l_fp,vXMLTable(ctr_table).TagName,vXMLTable(ctr_table).TagValue);
455 END LOOP;
456 END IF;
457 -- Write the end tag and close the XML File.
458 utl_file.put_line(p_l_fp,'</fields>');
459 utl_file.put_line(p_l_fp,'</xfdf>');
460 utl_file.fclose(p_l_fp);
461 /*Msg in the temorary table*/
462 --insert into tstmsg values('Leaving the procedure WritetoXML.');
463 END WritetoXML;
464
465
466 /*-------------------------------------------------------------------------------
467 |Name : WriteXMLvalues |
468 |Type : Procedure |
469 |Description : Procedure to write the xml values. Used for debugging |
470 ------------------------------------------------------------------------------*/
471
472
473 PROCEDURE WriteXMLvalues( p_l_fp utl_file.file_type,p_tagname IN VARCHAR2, p_value IN VARCHAR2) IS
474 BEGIN
475 -- Writing XML Tag and values to XML File
476 -- utl_file.put_line(p_l_fp,'<' || p_tagname || '>' || p_value || '</' || p_tagname || '>' );
477 -- New Format XFDF
478 utl_file.put_line(p_l_fp,'<field name="' || p_tagname || '">');
479 utl_file.put_line(p_l_fp,'<value>' || p_value || '</value>' );
480 utl_file.put_line(p_l_fp,'</field>');
481 END WriteXMLvalues;
482
483 /*-------------------------------------------------------------------------------
484 |Name : WritetoXML_rtf |
485 |Type : Procedure |
486 |Description : Procedure to write the xml to a file. Used for debugging |
487 | purposes |
488 ------------------------------------------------------------------------------*/
489
490
491 /*Function to support building of xml file compatible with RTF processor */
492 PROCEDURE WritetoXML_rtf (
493 p_request_id in number,
494 p_output_fname out nocopy varchar2)
495 IS
496 p_l_fp UTL_FILE.FILE_TYPE;
497 l_audit_log_dir varchar2(500) := '/sqlcom/outbound';
498 l_file_name varchar2(50);
499 l_check_flag number;
500 l_concat_str VARCHAR2(32000);
501 BEGIN
502 /*Msg in the temorary table*/
503 --insert into tstmsg values('Entered the procedure WritetoXML.');
504 -----------------------------------------------------------------------------
505 -- Writing into XML File
506 -----------------------------------------------------------------------------
507 -- Assigning the File name.
508 l_file_name := to_char(p_request_id) || '.xml';
509 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
510 BEGIN
511
512
513 SELECT value
514 INTO l_audit_log_dir
515 FROM v$parameter
516 WHERE LOWER(name) = 'utl_file_dir';
517 -- Check whether more than one util file directory is found
518 IF INSTR(l_audit_log_dir,',') > 0 THEN
519 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
520 END IF;
521 EXCEPTION
522 when no_data_found then
523 null;
524 END;
525 -- Find out whether the OS is MS or Unix based
526 -- If it's greater than 0, it's unix based environment
527 IF INSTR(l_audit_log_dir,'/') > 0 THEN
528 p_output_fname := l_audit_log_dir || '/' || l_file_name;
529 ELSE
530 p_output_fname := l_audit_log_dir || '\' || l_file_name;
531 END IF;
532 -- getting Agency name
533 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A',32000);
534 -- Writing from and to dates
535 l_concat_str := '<?xml version="1.0" encoding="ISO-8859-1"?>';
536 l_concat_str := l_concat_str||'<fields>';
537 --utl_file.put_line(p_l_fp,'<fields>');
538 -- Write the header fields to XML File.
539 --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') );
540 --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') );
541 -- Loop through PL/SQL Table and write the values into the XML File.
542 -- Need to try FORALL instead of FOR
543 IF vXMLTable.count >0 then
544
545 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
546
547 IF length(l_concat_str) > 28000 THEN
548
549 utl_file.put_line(p_l_fp,l_concat_str);
550 l_concat_str := '';
551
552 END IF;
553
554 IF substr(vXMLTable(ctr_table).TagName,1,11)='G_CONTAINER' THEN
555
556 IF vXMLTable(ctr_table).TagValue is null THEN
557 l_concat_str := l_concat_str||'<'||vXMLTable(ctr_table).TagName||'>';
558 ELSIF vXMLTable(ctr_table).TagValue='END' THEN
559 l_concat_str := l_concat_str||'</'||vXMLTable(ctr_table).TagName||'>';
560 END IF;
561
562 ELSE
563
564 l_concat_str := l_concat_str||'<'||vXMLTable(ctr_table).TagName||'>';
565 l_concat_str := l_concat_str||'<value>'||vXMLTable(ctr_table).TagValue||'</value>';
566 l_concat_str := l_concat_str||'</'||vXMLTable(ctr_table).TagName||'>';
567
568 END IF;
569 --WriteXMLvalues_rtf(p_l_fp,vXMLTable(ctr_table).TagName,vXMLTable(ctr_table).TagValue);
570 END LOOP;
571 END IF;
572 IF length(l_concat_str) > 0 THEN
573 utl_file.put_line(p_l_fp,l_concat_str);
574 END IF;
575 -- Write the end tag and close the XML File.
576 utl_file.put_line(p_l_fp,'</fields>');
577 utl_file.fclose(p_l_fp);
578 /*Msg in the temorary table*/
579 --insert into tstmsg values('Leaving the procedure WritetoXML.');
580 END WritetoXML_rtf;
581
582 /*Function to support building of xml file compatible with RTF processor */
583 PROCEDURE WriteXMLvalues_rtf( p_l_fp utl_file.file_type,p_tagname IN VARCHAR2, p_value IN VARCHAR2) IS
584 BEGIN
585 -- Writing XML Tag and values to XML File
586 -- utl_file.put_line(p_l_fp,'<' || p_tagname || '>' || p_value || '</' || p_tagname || '>' );
587 -- New Format XFDF
588 utl_file.put_line(p_l_fp,'<' || p_tagname || '>');
589 utl_file.put_line(p_l_fp,'<value>' || p_value || '</value>' );
590 utl_file.put_line(p_l_fp,'</' || p_tagname || '>');
591 END WriteXMLvalues_rtf;
592
593
594
595 END PAY_NL_XDO_REPORT;