1 PACKAGE BODY fnd_gfm AS
2 /* $Header: AFGFMB.pls 120.23.12020000.3 2012/08/20 20:15:34 ctilley ship $ */
3
4 -----------------------------------------------------------------------------
5 --global variable to indicate wether we are are in R12 env or 11i
6 --in a broad way we use the 'TYPE' of function 'FND_FNDFLUPL to
7 --obtain the same 11i = TYPE = WWW / R12 = TYPE = JSP respectively
8 --setter and getter methods are used to access the same.
9
10 g_release_version number := 0;
11
12 /*--------------------------------------------------------------------------*/
13 /*
14 * file_handle - internal file descriptor
15 * Files are referenced by a numeric file identifier, which is an index
16 * into the open_file_handles table. Due to an odd PL/SQL API for tables,
17 * we do not actually delete old elements in the table, but simply nullify
18 * them. Thus, on new file openings, we first traverse the table looking
19 * for empty slots.
20 */
21 TYPE file_handle IS RECORD ( fid NUMBER, offset INTEGER );
22 TYPE file_handles IS TABLE OF file_handle;
23 open_file_handles file_handles := file_handles();
24 /*--------------------------------------------------------------------------*/
25 /*
26 * err_msg - (PRIVATE) shortcut for building standard gfm error message
27 */
28 PROCEDURE err_msg(name varchar2) is
29 begin
30 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
31 fnd_message.set_token('ROUTINE', 'FND_GFM.'||name);
32 fnd_message.set_token('ERRNO', SQLCODE);
33 fnd_message.set_token('REASON', SQLERRM);
34 end err_msg;
35 /*--------------------------------------------------------------------------*/
36 /*
37 * authenticate - validate the specified access key
38 */
39 FUNCTION authenticate(access_id number,
40 file_id number default -1) RETURN boolean
41 is
42 rowcount number;
43 begin
44 select count(*) into rowcount
45 from fnd_lob_access
46 where nvl(file_id,-1) = nvl(authenticate.file_id, -1)
47 and access_id = authenticate.access_id
48 and timestamp > sysdate;
49
50 if (rowcount=1) then
51 return TRUE;
52 end if;
53 return FALSE;
54 exception
55 when others then
56 fnd_gfm.err_msg('authenticate');
57 raise;
58 end;
59 /*--------------------------------------------------------------------------*/
60 /*
61 * authorize - generate an authorized access key, good for one day.
62 */
63 FUNCTION authorize(file_id number) return number is
64 pragma autonomous_transaction;
65 result number;
66 begin
67 loop
68 begin
69 insert into fnd_lob_access (access_id, file_id, timestamp)
70 values (fnd_crypto.RANDOMNUMBER,
71 file_id, sysdate+1)
72 returning access_id into result;
73 commit;
74 return result;
75 exception
76 when DUP_VAL_ON_INDEX then
77 null;
78 end;
79 end loop;
80 exception
81 when others then
82 fnd_gfm.err_msg('authorize');
83 raise;
84 end;
85 /*--------------------------------------------------------------------------*/
86 /*
87 * construct_download_URL - construct a download URL
88 * Very little work is done here but the knowledge of the exact
89 * syntax of the URL is self-contained. If the purge option is
90 * indicated, we set the expiration date to the near future, so
91 * that the file is purged whether or not the URL is issued.
92 */
93 FUNCTION construct_download_url2(gfm_agent varchar2,
94 file_id number,
95 purge_on_view boolean default FALSE,
96 modplsql boolean,
97 authenticate boolean)
98 return varchar2 is
99 pragma autonomous_transaction;
100 access_id number;
101 url varchar2(2000);
102 file_ext varchar2(10);
103 file_name varchar2(300);
104 export_mime varchar2(30);
105 ext_length number;
106 begin
107 access_id := fnd_gfm.authorize(file_id);
108
109 /* Changed url structure to be gfmagent/fndgfm/fnd_gfm.get/arg1/arg2/fnd_gfm.ext
110 * to eliminate the dependancy on the desktop having the content/mime type set
111 * correctly. Now the file downloaded will have the correct extension so as long
112 * as the file ext is associated to an application it will open successfully/correctly.
113 * Temporarily hardcoding the most common export file types until a lookup can be created.
114 */
115
116 -- purge_on_view is TRUE then exporting
117
118 if purge_on_view then
119
120 export_mime := fnd_profile.value('FND_EXPORT_MIME_TYPE');
121
122 if export_mime = 'text/tab-separated-values' then
123 file_ext := '.tsv';
124 elsif
125 export_mime = 'text/comma-separated-values' then
126 file_ext := '.csv';
127 elsif
128 export_mime = 'text/plain' then
129 file_ext := '.txt';
130 elsif
131 export_mime like '%excel' then
132 file_ext := '.xls';
133 elsif
134 export_mime = 'text/html' then
135 file_ext := '.htm';
136 elsif
137 export_mime like 'application/%msword' then
138 file_ext := '.doc';
139 else
140 file_ext := '';
141 end if;
142
143 file_name := 'fnd_gfm'||file_ext;
144 update fnd_lobs set fnd_lobs.expiration_date = sysdate + 0.5
145 where fnd_lobs.file_id = construct_download_url2.file_id;
146
147 else
148 select instr(file_name,'.',-1) into ext_length
149 from fnd_lobs
150 where file_id = construct_download_url2.file_id;
151
152 if ext_length > 0 then
153 select substr(file_name,instr(file_name,'/',-1)+1) into file_name
154 from fnd_lobs
155 where file_id = construct_download_url2.file_id;
156 else
157 file_name := 'fnd_gfm';
158 end if;
159 end if;
160
161 --check the release
162 if (fnd_gfm.getRelease = 11) then
163 url := fnd_web_config.trail_slash(gfm_agent)||'fndgfm/fnd_gfm.get/'||access_id||'/'||file_id||'/'||file_name;
164 else
165 -- we take all this trouble only if we are on R12
166 --construct the download URL
167 if (modplsql) then
168 url := fnd_web_config.trail_slash(gfm_agent)||'fndgfm/fnd_gfm.get/'||access_id||'/'||file_id||'/'||file_name;
169 else
170 if (authenticate) then
171 url := fnd_web_config.trail_slash(fnd_profile.value('APPS_FRAMEWORK_AGENT'))||'OA_HTML/fndgfm.jsp?mode=download_blob'||'&'||'fid='||file_id||'&'||'accessid='||access_id;
172 else
173 url := fnd_web_config.trail_slash(fnd_profile.value('APPS_FRAMEWORK_AGENT'))||'OA_HTML/fndgfm.jsp?mode=download_blob'||'&'||'fid='||file_id;
174 end if;
175
176 end if;
177 end if;
178 commit;
179 return url;
180 exception
181 when others then
182 fnd_gfm.err_msg('construct_download_url2');
183 raise;
184 end;
185
186 /*--------------------------------------------------------------------------*/
187 /*
188 * construct_upload_URL - construct an upload URL
189 * Very little work is done here but the knowledge of the exact
190 * syntax of the URL is self-contained.
191 */
192 FUNCTION construct_upload_URL(gfm_agent VARCHAR2,
193 proc VARCHAR2,
194 access_id NUMBER)
195 return varchar2 is
196 --sqlbuf varchar2(1000);
197 params_passed varchar2(2000);
198 func_id number;
199 user_id number;
200 resp_id number;
201 resp_appl_id number;
202 sec_grp_id number :=0;
203 begin
204 if (fnd_gfm.getRelease = 12) then
205
206 user_id := fnd_profile.value('USER_ID');
207 resp_id := fnd_profile.value('RESP_ID');
208 resp_appl_id := fnd_profile.value('RESP_APPL_ID');
209 sec_grp_id := fnd_profile.value('SECURITY_GROUP_ID');
210
211 params_passed := 'access_id'||access_id;
212
213 begin
214 select function_id
215 into func_id
216 from fnd_form_functions
217 where function_name = 'FND_FNDFLUPL';
218 exception
219 when no_data_found then
220 return fnd_web_config.trail_slash(gfm_agent)||proc;
221 when too_many_rows then
222 select function_id
223 into func_id
224 from fnd_form_functions
225 where function_name = 'FND_FNDFLUPL'
226 and upper(type) = 'JSP';
227 when others then
228 fnd_gfm.err_msg('construct_upload_url_r12');
229 raise;
230 end;
231
232 return FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL(
233 P_FUNCTION_ID => func_id,
234 P_RESP_APPL_ID => resp_appl_id,
235 P_RESP_ID => resp_id,
236 P_SECURITY_GROUP_ID => sec_grp_id,
237 P_PARAMETERS => params_passed);
238
239 /*
240 As of now there seems no way to open from pl/sql block this
241 OA Framework page. If we do we could incorporate the below code
242 for a generic callback
243 */
244
245 --block the call till we have a submit
246 --fnd_gfm.wait_for_upload('FND_GFM_ALERT' || to_char(access_id));
247 --if we can create a unfirom structure for the callback
248 --we could invoke a callback here things we can provide is
249 --file_id , access_id etc as of now aniticipating a maximum
250 --these params
251 --if (proc is NOT null) then
252 -- sqlbuf := 'begin ' || proc || ' (:v1, :v2, :v3, :v4, :v5); end;';
253 -- execute immediate sqlbuf using
254 -- in access_id,
255 -- in fid,
256 -- in out result;
257 --if (result = true)
258 -- return 'SUCCESS';
259 --else
260 --fnd_gfm.err_msg('construct_upload_url.failed_callback');
261 --raise;
262 --end if;
263 --end if;
264
265 else
266 return fnd_web_config.trail_slash(gfm_agent)||proc;
267 end if;
268
269 exception
270 when others then
271 fnd_gfm.err_msg('construct_upload_url');
272 raise;
273 end;
274 /*--------------------------------------------------------------------------*/
275 /*
276 * construct_get_url - construct a get request URL
277 * Very little work is done here but the knowledge of the exact
278 * syntax of the URL is self-contained.
279 */
280 FUNCTION construct_get_url(gfm_agent varchar2,
281 proc varchar2,
282 path varchar2) return varchar2 is
283 pragma autonomous_transaction;
284 begin
285 return fnd_web_config.trail_slash(gfm_agent)||
286 fnd_gfm.construct_relative_get(proc,path);
287 exception
288 when others then
292 /*--------------------------------------------------------------------------*/
289 fnd_gfm.err_msg('construct_get_url');
290 raise;
291 end;
293 /*
294 * construct_relative_get - construct a relative get request URL
295 * fragment without the plsql_agent/dad info.
296 *
297 * This function constructs a URL that may be presented to the browser
298 * as a relative url to a document that has already been "gotten".
299 *
300 * proc Procedure to run
301 * path Argument path
302 */
303 FUNCTION construct_relative_get(proc varchar2,
304 path varchar2) return varchar2 is
305 pragma autonomous_transaction;
306 begin
307 return 'fndgfm/'||proc||'/'||path;
308 exception
309 when others then
310 fnd_gfm.err_msg('construct_relative_get');
311 raise;
312 end;
313 /*--------------------------------------------------------------------------*/
314 /*
315 * confirm_upload - confirm the completion of an upload
316 * This procedure must be called from within the user's PL/SQL upload
317 * procedure to authenticate the upload and move the blob into the
318 * fnd_lobs table. The new generated file_id is returned to the caller.
319 *
320 * access_id The access identifier that authorized the upload
321 * file_name The file_name as stored in the fnd_lobs_document table
322 * by WebDB...the value of the file type input field.
323 * program_name The application short name to record
324 * program_tag The application tag to record
325 * expiration_date The expiration date to record; typically, this is null
326 * which means that the LOB never expires, but must be
327 * explicitly deleted
328 * language The document language; defaults to userenv('LANG')
329 * wakeup If TRUE, indicates that wait_for_upload is expected to
330 * be called on the original authorization key; the GFM
331 * can associate the given file identifier to the original
332 * authorization key and wake up the identified waiter.
333 *
334 * BUG#2461866 Added fn to parse out file_name.
335 */
336 FUNCTION confirm_upload(
337 access_id number,
338 file_name varchar2,
339 program_name varchar2 default null,
340 program_tag varchar2 default null,
341 expiration_date date default null,
342 language varchar2 default userenv('LANG'),
343 wakeup boolean default FALSE)
344 return number is
345 fid number := -1;
346 fn varchar2(256);
347 mt varchar2(240);
348 bloblength number; -- bug 3045375, added variable to set length of blob.
349 ufslim number;
350 begin
351 if (getRelease = 12) then
352 begin
353 --we already through the OA page has inserted the
354 --data into fnd_lobs table
355 --return the file_id corresponding to this access_id
356 select file_id
357 into fid
358 from fnd_lob_access
359 where access_id = confirm_upload.access_id;
360
361 --raise the alert back so that the wait is ended
362 if wakeup then
363 dbms_alert.signal('FND_GFM_ALERT'||to_char(access_id), to_char(fid));
364 end if;
365
366 return fid;
367 exception
368 when others then
369 return -1;
370 end;
371 else
372 if (fnd_gfm.authenticate(confirm_upload.access_id)) then
373
374 if (verify_file_type(file_name => confirm_upload.file_name) = 'Y') then
375 select fnd_lobs_s.nextval into fid from dual;
376
377 fn := SUBSTR(confirm_upload.file_name, INSTR(confirm_upload.file_name,'/')+1);
378
379 -- bug 3045375, added select to get length of BLOB.
380 select dbms_lob.getlength(blob_content), mime_type
381 into bloblength, mt
382 from fnd_lobs_document
383 where name = confirm_upload.file_name
384 and rownum=1;
385
386 -- bug 3045375, added if to check length of blob.
387 -- bug 4279252. added UPLOAD_FILE_SIZE_LIMIT check.
388
389 if fnd_profile.value('UPLOAD_FILE_SIZE_LIMIT') is null then
390 ufslim := bloblength;
391 else
392 /* The profile is not limited to being a numeric value. Stripping off any
393 reference to kilobytes. */
394
395 if (instr(upper(fnd_profile.value('UPLOAD_FILE_SIZE_LIMIT')),'K')>0) then
396 ufslim := substr(fnd_profile.value('UPLOAD_FILE_SIZE_LIMIT'),1,
397 instr(upper(fnd_profile.value('UPLOAD_FILE_SIZE_LIMIT')),'K')-1);
398 else
399 ufslim := fnd_profile.value('UPLOAD_FILE_SIZE_LIMIT');
400 end if;
401
402 /* Bug 6490050 - profile is defined to be in KB so we need to convert
403 here. Consistent with the fwk code. */
404
405 ufslim := ufslim * 1000;
406 end if;
407
408 if bloblength BETWEEN 1 and ufslim then
409 insert into fnd_lobs (file_id,
410 file_name,
411 file_content_type,
412 file_data,
413 upload_date,
414 expiration_date,
415 program_name,
416 program_tag,
417 language,
418 file_format)
419 (select confirm_upload.fid,
420 fn,
421 ld.mime_type,
422 ld.blob_content,
423 sysdate,
424 confirm_upload.expiration_date,
425 confirm_upload.program_name,
426 confirm_upload.program_tag,
427 confirm_upload.language,
428 fnd_gfm.set_file_format(mt)
429 from fnd_lobs_document ld
430 where ld.name = confirm_upload.file_name
431 and rownum=1);
432
433 if (sql%rowcount <> 1) then
434 raise no_data_found;
435 end if;
436
437 update fnd_lob_access set file_id = fid
438 where access_id = confirm_upload.access_id;
439
440 if wakeup then
441 dbms_alert.signal('FND_GFM_ALERT'||to_char(access_id), to_char(fid));
442 end if;
443 -- bug 3045375, added else to return fid = -2.
444 else
445 -- This indicates that an invalid file size has been uploaded.
446 fid := -2;
447 end if;
448 else
449 -- bug 9276419 - indicate a restricted file by returning -3
450 fid := -3;
451 end if;
452 delete from fnd_lobs_document;
453 delete from fnd_lobs_documentpart;
454 end if;
455 end if;
456 return fid;
457 exception
458 when others then
459 delete from fnd_lobs_document;
460 delete from fnd_lobs_documentpart;
461
462 fnd_gfm.err_msg('confirm_upload');
463 raise;
464 end;
465 /*--------------------------------------------------------------------------*/
466 /*
467 * get_file_id
468 * This function retrieves the file_id for the corresponding access_id
469 * from the fnd_lob_access table.
470 *
471 * access_id the access id
472 */
473 FUNCTION get_file_id(access_id number) return number is
474 fid number := -1;
475 begin
476 select file_id into fid
477 from fnd_lob_access
478 where access_id = get_file_id.access_id;
479
480 if (sql%rowcount <> 1) then
481 raise no_data_found;
482 end if;
483
484 return fid;
485 exception
486 when others then
487 fnd_gfm.err_msg('get_file_id');
488 raise;
489 end;
490 /*--------------------------------------------------------------------------*/
491 /*
492 * dispatch
493 * This procedure takes a single argument in the form of
494 * <proc>/<arg>
495 *
496 * and executes the proc, passing along the single arg.
497 *
498 * p_path <proc>/<arg>
499 */
500 PROCEDURE dispatch(p_path in varchar2) is
501 proc varchar2(256);
502 arg varchar2(1000);
503 slash number;
504 cmd varchar2(1300);
505
506 begin
507 cmd := ltrim(p_path, '/');
508 slash := nvl(instr(cmd, '/'), 0);
509 proc := upper(substr(cmd, 1, slash-1));
510 arg := substr(cmd, slash);
511
512 if (fnd_web_config.check_enabled(proc) = 'Y') then
513 cmd := 'begin '||proc||'(:1); end;';
514 execute immediate cmd using in arg;
515 else
516 htp.htmlOpen;
517 htp.headOpen;
518 htp.title('Access Denied');
519 htp.headClose;
520 htp.bodyOpen;
521 htp.p('<b>Access Denied</b>');
522 htp.hr;
523
524 -- If the procedure contains an HTML tag, don't display it.
525 --
526 if (instr(proc,'<') > 0) then
527 htp.p('Not authorized to access procedure.');
528 else
529 htp.p('Not authorized to access '||proc||'.');
530 end if;
531
532 htp.bodyClose;
533 htp.htmlClose;
534 end if;
535
539 raise;
536 exception
537 when others then
538 fnd_gfm.err_msg('dispatch');
540 end;
541 /*--------------------------------------------------------------------------*/
542 /*
543 * wait_for_upload - wait for upload confirmation
544 */
545 PROCEDURE wait_for_upload(access_id number,
546 timeout number,
547 file_id out NOCOPY number) is
548 pragma autonomous_transaction;
549 name varchar2(64);
550 message varchar2(32);
551 status integer;
552 begin
553 name := 'FND_GFM_ALERT' || to_char(access_id);
554 dbms_alert.register(name);
555 dbms_alert.waitone(name, message, status, timeout);
556 dbms_alert.remove(name);
557
558 if status = 1 then
559 file_id := null;
560 else
561 file_id := to_number(message);
562 end if;
563 commit;
564 end;
565 /*--------------------------------------------------------------------------*/
566 /*
567 * purge_expired - purge all expired rows in GFM tables
568 */
569 PROCEDURE purge_expired(program_name varchar2 default null) is
570 pragma autonomous_transaction;
571
572 CURSOR c_orph_attch_lob IS
573 SELECT file_id
574 FROM FND_LOBS FL
575 WHERE NOT EXISTS (SELECT '1'
576 FROM FND_DOCUMENTS_VL FDVL
577 WHERE FDVL.MEDIA_ID = FL.FILE_ID
578 AND FDVL.DATATYPE_ID = 6)
579 AND PROGRAM_NAME = 'FNDATTCH'
580 AND EXPIRATION_DATE IS NULL;
581
582 l_file_id number;
583 l_doc_cnt number;
584 l_module_source varchar2(256) := 'FND_GFM.PURGE_EXPIRED';
585
586 begin
587 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
588 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'Begin');
589 end if;
590
591 -- For performance a check for orphaned attachment lobs will be done when
592 -- explicitly purging FNDATTCH program data
593
594 if (program_name = 'FNDATTCH') then
595 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
596 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'PROGRAM_NAME=FNDATTCH - set expiration date for orphaned Attachment lobs');
597 end if;
598
599 -- Set expiration_date for orphaned lobs
600 -- Limiting to One time documents (for now)
601 open c_orph_attch_lob;
602
603 LOOP
604 fetch c_orph_attch_lob into l_file_id;
605 EXIT WHEN c_orph_attch_lob%NOTFOUND;
606
607 select count(*) into l_doc_cnt
608 from fnd_documents_vl
609 where datatype_id = 6
610 and media_id = l_file_id;
611
612 if (l_doc_cnt = 0) then -- set expiration date
613 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
614 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Found orphaned fnd_lobs record: '||l_file_id);
615 end if;
616
617 update fnd_lobs fl
618 set expiration_date = sysdate-1
619 where program_name = 'FNDATTCH'
620 and file_id = l_file_id;
621 end if;
622
623 END LOOP;
624 close c_orph_attch_lob;
625 commit;
626 end if;
627
628 if purge_expired.program_name is null then
629 delete from fnd_lobs where sysdate > expiration_date;
630 delete from fnd_lob_access where sysdate > timestamp;
631 commit;
632 else
633 delete from fnd_lobs
634 where fnd_lobs.program_name = purge_expired.program_name
635 and sysdate > expiration_date;
636 commit;
637 end if;
638
639 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
640 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'End');
641 end if;
642
643 exception
644 when others then
645 fnd_gfm.err_msg('purge_expired');
646 raise;
647 end;
648 /*--------------------------------------------------------------------------*/
649 /*
650 * purge_set - purge selected rows from the LOB table
651 */
652 PROCEDURE purge_set(program_name varchar2,
653 program_tag varchar2 default null) is
654 pragma autonomous_transaction;
655 begin
656 if program_tag is null then
657 delete from fnd_lobs where fnd_lobs.program_name = purge_set.program_name;
658 else
659 delete from fnd_lobs
660 where fnd_lobs.program_name = purge_set.program_name
661 and fnd_lobs.program_tag = purge_set.program_tag;
662 end if;
663 commit;
664 exception
665 when others then
666 fnd_gfm.err_msg('purge_set');
667 raise;
668 end;
669 /*--------------------------------------------------------------------------*/
670 /*
671 * purge - CM cover routine for purge_set and purge_expired
672 */
673 PROCEDURE purge(errbuf out NOCOPY varchar2,
674 retcode out NOCOPY varchar2,
675 expired in varchar2 default 'Y',
676 program_name in varchar2 default null,
677 program_tag in varchar2 default null)
678 is
679 begin
680 if (purge.expired <> 'N') then
681 fnd_gfm.purge_expired(purge.program_name);
682
683 elsif (purge.program_name is not null) then
684 fnd_gfm.purge_set(purge.program_name, purge.program_tag);
685
686 end if;
687
688 retcode := '0'; -- (successful completion)
689 errbuf := '';
690
691 exception
692 when others then
693 retcode := '2'; -- (error)
694 fnd_message.retrieve(errbuf);
695 end;
696 /*--------------------------------------------------------------------------*/
697 /*
698 * file_create - create a new empty file
699 */
700 FUNCTION file_create(file_name varchar2 default null,
701 content_type varchar2 default 'text/plain',
702 program_name varchar2 default null,
703 program_tag varchar2 default null) return number is
704 pragma autonomous_transaction;
705 fd integer;
706 fh file_handle;
707 l_lang varchar2(4):= userenv('LANG');
708 l_file_format varchar2(10) := 'binary';
709 iana_cs varchar2(150);
710 ocs varchar2(30);
711 ct varchar2(100);
712 begin
713 -- get a file handle slot
714 fd := null;
715 for i in 1..open_file_handles.count loop
716 if open_file_handles(i).fid is null then
717 fd := i;
718 exit;
719 end if;
720 end loop;
721
722 -- Set file format
723 if(upper(substr(content_type,1,4)) = 'TEXT' ) then
724 l_file_format := 'text';
725 end if;
726
727 -- Determine the IANA Charset and add to content-type, if necessary
728 if (instr(content_type,'charset=') > 0) then
729 iana_cs := substr(content_type, instr(content_type,'=',-1)+1);
730 ct := content_type;
731 else
732 iana_cs := fnd_gfm.get_iso_charset;
733 ct := content_type||'; charset='||iana_cs;
734 end if;
735
736 if fd is null then
737 open_file_handles.extend;
738 fd := open_file_handles.count;
739 end if;
740
741 insert into fnd_lobs (file_id, file_name, file_content_type,
742 file_data, upload_date, expiration_date, program_name, program_tag,
743 language,oracle_charset,file_format)
744 values (fnd_lobs_s.nextval, file_name, ct,
745 EMPTY_BLOB(), sysdate, sysdate + 1, program_name, program_tag,
746 l_lang, fnd_gfm.iana_to_oracle(iana_cs), l_file_format)
747 returning file_id into fh.fid;
748
749 fh.offset := 1;
750 open_file_handles(fd) := fh;
751
752 commit;
753 return fd;
754 exception
755 when others then
756 fnd_gfm.err_msg('file_create');
757 raise;
758 end;
759 /*--------------------------------------------------------------------------*/
760 /*
761 * file_close - close a file
762 */
763 FUNCTION file_close(fd number) return number is
764 pragma autonomous_transaction;
765 fh file_handle;
766 begin
767 fh := open_file_handles(fd);
768 open_file_handles(fd) := null;
769
770 update fnd_lobs set expiration_date = null where file_id = fh.fid;
771 commit;
772
773 return fh.fid;
774 exception
775 when others then
776 fnd_gfm.err_msg('file_close');
777 raise;
778 end;
779 /*--------------------------------------------------------------------------*/
780 /*
781 * file_write - write raw data to a file
782 */
783 PROCEDURE file_write(fd number, amount binary_integer, buffer raw) is
784 pragma autonomous_transaction;
785 fh file_handle;
786 flob blob;
787 ocs varchar2(30);
788 l_len number := utl_raw.length(buffer);
789 l_pos number := 1;
790 l_str raw(6000) := null;
791 begin
792 fh := open_file_handles(fd);
793
794 -- Re-open blob and get data. We must re-open every time instead of
795 -- caching so that procedures can be made autonomous.
796 select file_data, oracle_charset into flob, ocs
797 from fnd_lobs
798 where file_id = fh.fid
799 for update of file_data;
800
801 -- BUG#1449494, created while loop to bypass convert() limitation.
802 while (l_len > 1000) loop
803 l_str := utl_raw.substr(buffer,l_pos, 1000);
804 dbms_lob.write(flob,1000,fh.offset, convert(l_str,ocs));
805 l_pos := l_pos + 1000;
806 l_len := l_len - 1000;
807 fh.offset := fh.offset + 1000;
808 end loop;
809 l_str := utl_raw.substr(buffer, l_pos);
810 dbms_lob.write(flob, l_len, fh.offset, convert(l_str,ocs));
811 fh.offset := fh.offset + l_len;
812 open_file_handles(fd) := fh;
813 commit;
814 exception
815 when others then
816 fnd_gfm.err_msg('file_write');
817 raise;
818 end;
819 /*--------------------------------------------------------------------------*/
820 /*
821 * file_write - write a line of text to a file
822 */
823 PROCEDURE file_write(fd number, buffer varchar2) IS
824 -- NOTE: This procedure is implicitly Autonomous Transaction,
825 -- because file_write(..<raw>..) is autonomous.
826 begin
827 if buffer is not null then
828 file_write(fd, lengthb(buffer), utl_raw.cast_to_raw(buffer));
829 end if;
830 end;
831 /*--------------------------------------------------------------------------*/
832 /*
833 * file_write_line - write a line of text to a file with a line terminator
834 */
835 PROCEDURE file_write_line(fd number, buffer varchar2) is
836 -- NOTE: This procedure is implicitly Autonomous Transaction,
837 -- because file_write(..<raw>..) is autonomous.
838 -- BUG#1811196
839 -- Combined 2 file_write() calls to 1 for performance gain.
840 begin
841 file_write(fd, (lengthb(buffer)+2),
842 (utl_raw.concat(utl_raw.cast_to_raw(buffer), hextoraw('0D0A'))));
843 end;
844 /*--------------------------------------------------------------------------*/
845 /*
846 * test - testing procedure
847 * This procedure exists to give the GFM cartridge a test target
848 * in the database.
849 */
850 PROCEDURE test is
851 begin
852 htp.print('Success');
853 end;
854 /*--------------------------------------------------------------------------*/
855 /*
856 * get_iso_charset
857 * This procedure retrieves the iso equivalent of the db's character set.
858 */
859 FUNCTION get_iso_charset return varchar2 is
860 charset varchar2(150);
861 charmap constant varchar2(30) := 'FND_ISO_CHARACTER_SET_MAP';
862 begin
863 select tag into charset
864 from fnd_lookup_values_vl
865 where lookup_type = charmap
866 and lookup_code = substr(userenv('LANGUAGE'),
867 instr(userenv('LANGUAGE'),'.')+1);
868 return charset;
869 exception
870 when others then
871 fnd_gfm.err_msg('get_iso_charset');
872 raise;
873 end;
874 /*--------------------------------------------------------------------------*/
875 /*
876 * iana_to_oracle
877 * This procedure retrieves the oracle equivalent of the specified iana
878 * charset.
879 */
880 FUNCTION iana_to_oracle(ics varchar2) return varchar2 is
881 cs varchar2(50);
882 charmap constant varchar2(30) := 'FND_IANA_TO_ORACLE_CHARSET_MAP';
883 begin
884 select tag into cs
885 from fnd_lookup_values_vl
886 where lookup_type = charmap
887 and upper(lookup_code) = upper(ics);
888
889 if (sql%rowcount <> 1) then
890 raise no_data_found;
891 end if;
892
893 return cs;
894 exception
895 when others then
896 fnd_gfm.err_msg('iana_to_oracle');
897 raise;
898 end;
899 /*--------------------------------------------------------------------------*/
900 /*
901 * oracle_to_iana
902 * This procedure retrieves the iana equivalent of the specified oracle
903 * charset
904 */
905 FUNCTION oracle_to_iana (cs varchar2) return varchar2 is
906 ics varchar2(150);
907 charmap constant varchar2(30) := 'FND_ISO_CHARACTER_SET_MAP';
908 begin
909 select tag into ics
910 from fnd_lookup_values_vl
911 where lookup_type = charmap
912 and upper(lookup_code) = upper(cs)
913 and rownum = 1;
914
915 if (sql%rowcount <> 1) then
916 raise no_data_found;
917 end if;
918
919 return ics;
920 exception
921 when others then
922 fnd_gfm.err_msg('oracle_to_iana');
923 raise;
924 end;
925 /*--------------------------------------------------------------------------*/
926 /*
927 * download -
928 * This procedure processes a download request
929 * If purge is specified, then the row is deleted immediately
930 */
931 PROCEDURE download(file_id number,
932 access number,
933 purge varchar2 default NULL) is
934 doc blob;
935 ct varchar2(100);
936 begin
937 if (fnd_gfm.authenticate(access, file_id) = FALSE) then
938 htp.p(Fnd_Message.Get_String('PAY','HR_51401_WEB_NOT_AUTHORIZED'));
939 else
940 fnd_gfm.download_blob(file_id);
941
942 if (purge = 'yes') then
943 /* Instead of deleting the data immediatlely we are setting the
944 * expiration data.
945 * delete from fnd_lobs where file_id = download.file_id; */
946 update fnd_lobs
947 set fnd_lobs.expiration_date = sysdate + 0.5
948 where fnd_lobs.file_id = download.file_id;
949 end if;
950 end if;
951 exception
952 when others then
953 fnd_gfm.err_msg('download');
954 raise;
955 end download;
956 /*--------------------------------------------------------------------------*/
957 /*
958 ** download_blob -
959 ** encapsulate the actual downloading of the blob using webdb's procedure
960 */
961 PROCEDURE download_blob(fid number) is
962 ct varchar2(255);
963 ctype varchar2(255);
964 doc blob;
965 cs varchar2(150);
966 semi number;
967 eq number;
968 len number;
969 bb boolean;
970 buffer raw(32000);
971 amount number :=16000;
972 b_amount number;
973 lenvarb number;
974 offset number :=1;
975 offset2 number :=1;
976 lob_loc blob;
977 nce varchar2(120);
978 ocs varchar2(120);
979 var varchar2(32000);
980 pn varchar2(32);
981 fn varchar2(256);
982 c_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
983
984
985 begin
986 select file_content_type, oracle_charset, program_name, file_data, file_name
987 into ct, ocs, pn, doc, fn
988 from fnd_lobs
989 where file_id = download_blob.fid;
990
991 semi := instr(ct, ';');
992 -- Bug 3966022 - looking now specifically for charset value
993 -- eq := instr(ct, '=');
994 eq := instr(upper(ct), 'CHARSET=');
995
996 if (semi = 0) then
997 ctype := ct;
998 else
999 ctype := substr(ct, 1, semi-1);
1000 end if;
1001
1002 if (eq = 0) then -- no charset so pass null
1003 cs := null;
1004 else
1005 eq := eq + 8; -- Add 8 to account for the length of charset=
1006 semi := instr(ct,';', eq);
1007 if (semi = 0) then
1008 cs := substr(ct, eq);
1009 else
1010 cs := substr(ct, eq, semi-eq);
1011 end if;
1012 end if;
1013
1014 fnd_profile.get_specific('FND_NATIVE_CLIENT_ENCODING',fnd_global.user_id,NULL,NULL,nce,bb);
1015
1016 if (pn = 'export' and nce is not null) then
1017 cs := fnd_gfm.oracle_to_iana(nce);
1018 nce := 'american_america.'||nce;
1019 ocs := 'american_america.'||ocs;
1020 dbms_lob.createtemporary(lob_loc,TRUE);
1021 dbms_lob.open(doc, DBMS_LOB.LOB_READONLY);
1022 len := dbms_lob.getlength(doc);
1023 loop
1024 if len > amount
1025 then
1026 dbms_lob.read(doc, amount, offset, buffer);
1027 var :=utl_raw.cast_to_varchar2(buffer);
1028 lenvarb :=lengthb(var);
1029 if length(var) > 10
1030 then
1031 var := substr(var, 1, length(var) - 10);
1032 end if;
1033 lenvarb := lenvarb - lengthb(var);
1034 buffer := utl_raw.cast_to_raw(var);
1035 buffer := utl_raw.convert(buffer, nce, ocs);
1036 b_amount := utl_raw.length(buffer);
1037 dbms_lob.write(lob_loc, b_amount, offset2, buffer);
1038 len := len - amount + lenvarb;
1039 offset := offset + amount - lenvarb;
1040 offset2 := offset2 + b_amount;
1041 else
1042 dbms_lob.read(doc, len, offset, buffer);
1043 buffer := utl_raw.convert(buffer, nce, ocs);
1044 amount := utl_raw.length(buffer);
1045 dbms_lob.write(lob_loc, amount, offset2, buffer);
1046 exit;
1047 end if;
1048 end loop;
1049 end if;
1050
1051 owa_util.mime_header(ctype, FALSE, cs);
1052
1053 -- Mime sniffing bug 11706983: force to download as an attachment in IE
1054
1055 if ((instr(c_browser,'MSIE') > 0) and pn <> 'export' and ct='text/plain') then
1056 htp.p('Content-Disposition: attachment; filename='||'"'||fn||'"');
1057 end if;
1058
1059 if (pn = 'export' and nce is not null) then
1060 htp.p( 'Content-length: ' || dbms_lob.getlength(lob_loc));
1061 else
1062 htp.p( 'Content-length: ' || dbms_lob.getlength(doc));
1063 end if;
1064 /* Commenting out the following do resolve the issue with Export
1065 failing on Internet Explorer.
1066 htp.p( 'Cache-Control: no-cache' ); */
1067 owa_util.http_header_close;
1068 if (pn = 'export' and nce is not null) then
1069 wpg_docload.download_file(lob_loc);
1070 dbms_lob.freetemporary(lob_loc);
1071 dbms_lob.close(doc);
1072 else
1073 wpg_docload.download_file(doc);
1074 end if;
1075
1076 exception
1077 when no_data_found then
1078 htp.htmlOpen;
1079 htp.headOpen; htp.title('404 Not Found'); htp.headClose;
1080 htp.bodyOpen; htp.hr; htp.header(nsize=>1, cheader=>'HTTP Error 404');
1081 htp.hr;
1082 htp.p(Fnd_Message.Get_String('GMD','LM_BAD_FILENAME'));
1083 htp.bodyClose; htp.htmlClose;
1084 when others then
1085 fnd_gfm.err_msg('download_blob');
1086 raise;
1087 end download_blob;
1088
1089 /*
1090 * one_time_use_store
1091 * Store a value in the FND_LOB_ACCESS table and return a one-time-use
1092 * ticket that can be used by one_time_use_retrieve() to fetch the value.
1093 */
1094
1095 FUNCTION one_time_use_store(value number) RETURN number IS
1096 pragma autonomous_transaction;
1097 ticket number;
1098 begin
1099 for i in 1..10 loop
1100 begin
1101 ticket := fnd_crypto.RANDOMNUMBER;
1102 INSERT INTO fnd_lob_access (access_id, file_id, timestamp)
1103 VALUES (ticket, value, sysdate+1);
1104 commit;
1105 return ticket;
1106 exception
1107 when dup_val_on_index then
1108 null;
1109 end;
1110 end loop;
1111 -- More then 10 duplicates return -1 (error)
1112 return -1;
1113
1114 exception
1115 when others then
1116 rollback;
1117 return -1;
1118 end;
1119
1120 /*
1121 * one_time_use_store
1122 * A more secure API with a large entropy.
1123 * It returns a string that expires after lifespan
1124 * seconds. Opcode is a verification code used
1125 * when retrieving ticket value.
1126 */
1127 function one_time_use_store( value in varchar2 ,
1128 lifespan in number default null,
1129 opcode in varchar2 default null)
1130 return varchar2 is
1131 l_lifespan number;
1132 begin
1133 if ( lifespan is not null) then
1134 return FND_HTTP_TICKET.CREATE_TICKET_STRING(opcode, value, lifespan);
1135 else
1136 return FND_HTTP_TICKET.CREATE_TICKET_STRING(opcode, value);
1137 end if;
1138 end;
1139
1140 /*
1141 * one_time_use_retrieve
1142 * Retrieve a value from the FND_LOB_ACCESS table, given a one-time-use
1143 * ticket that was generated by one_time_use_store().
1144 */
1145 FUNCTION one_time_use_retrieve(ticket number) return number is
1146 pragma autonomous_transaction;
1147 value number;
1148 begin
1149 select file_id into value from fnd_lob_access
1150 where access_id = ticket for update;
1151 delete from fnd_lob_access where access_id = ticket;
1152 commit;
1153 return value;
1154 exception
1155 when others then
1156 rollback;
1157 return -1;
1158 end;
1159
1160 /*
1161 * one_time_use_retrieve
1162 * Retrieves value given a one-time-use
1163 * ticket that was generated by one_time_use_store().
1164 * if opcode passed, the API verifies the opcode value
1165 * against the opcode value passed in one_time_use_store.
1166 */
1167 function one_time_use_retrieve( ticket in varchar2 ,
1168 opcode in varchar2 default null)
1169 return varchar2 is
1170 l_args varchar2(4000);
1171 l_operation varchar2(4000);
1172 begin
1173 -- bug 6772298
1174 if(FND_HTTP_TICKET.CHECK_ONETIME_TICKET_STRING(ticket,
1175 l_operation,
1176 l_args) )
1177 then
1178 if(l_operation is null AND opcode is null)
1179 then
1180 return l_args;
1181 end if;
1182
1183 if(l_operation = opcode)
1184 then
1185 return l_args;
1186 end if;
1187 end if;
1188
1189 return null;
1190 end;
1191 /*--------------------------------------------------------------------------*/
1192 /*
1193 * Get -
1194 * This procedure processes a download request
1195 * If purge is specified, then the row is deleted immediately
1196 */
1197 PROCEDURE get(p_path varchar2) is
1198 doc blob;
1199 l_file_id number;
1200 access number;
1201 bool boolean;
1202 prog_name varchar2(30);
1203 exp_date date;
1204 p_purge_on_view varchar2(1) := 'N';
1205 l_module_source varchar2(256);
1206 begin
1207 l_module_source := 'FND_GFM.GET';
1208
1209 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1210 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Begin');
1211 end if;
1212
1213 bool := icx_sec.validatesession();
1214 access := substr(p_path,instr(p_path,'/',1)+1,instr(p_path,'/',2)-2);
1215 l_file_id := substr(p_path,instr(p_path,'/',2)+1,(instr(p_path,'/',-1)-instr(p_path,'/',2)-1));
1216
1217 if (fnd_gfm.authenticate(access, l_file_id) = FALSE) then
1218 htp.p(Fnd_Message.Get_String('PAY','HR_51401_WEB_NOT_AUTHORIZED'));
1219 else
1220
1221 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1222 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Downloading file_id '||to_char(l_file_id));
1223 end if;
1224
1225 fnd_gfm.download_blob(l_file_id);
1226
1227 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1228 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Done downloading file. Determine if this was an export that needs to be purged');
1229 end if;
1230
1231 select lower(program_name),expiration_date
1232 into prog_name, exp_date
1233 from fnd_lobs
1234 where file_id = l_file_id;
1235
1236 if (prog_name = 'export' and exp_date is not null) then
1237 fnd_profile.get('FND_EXPORT_PURGE_ON_VIEW',p_purge_on_view);
1238
1239 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1240 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Export - purge on view: '||p_purge_on_view);
1241 end if;
1242
1243 if (p_purge_on_view = 'Y') then
1244 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1245 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Deleting lob');
1246 end if;
1247
1248 fnd_gfm.delete_lob(l_file_id);
1249 end if;
1250 end if;
1251 end if;
1252
1253 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1254 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'End');
1255 end if;
1256
1257 exception
1258 when others then
1259 fnd_gfm.err_msg('get');
1260 raise;
1261 end get;
1262 /*--------------------------------------------------------------------------*/
1263 /*
1264 * copy_lob - This function copies one lob to another
1265 * Returns the file_id of the new lob.
1266 */
1267 FUNCTION copy_lob(fid_from number) return number is
1268 fid_to number;
1269 fnd_usr_rec fnd_user%ROWTYPE;
1270 fnd_lobs_rec fnd_lobs%ROWTYPE;
1271 begin
1272
1273 SELECT fnd_lobs_s.nextval
1274 INTO fid_to
1275 FROM dual;
1276
1277 SELECT file_id,
1278 file_name,
1279 file_content_type,
1280 upload_date,
1281 expiration_date,
1282 program_name,
1283 program_tag,
1284 file_data,
1285 language,
1286 oracle_charset,
1287 file_format
1288 INTO fnd_lobs_rec.file_id,
1289 fnd_lobs_rec.file_name,
1290 fnd_lobs_rec.file_content_type,
1291 fnd_lobs_rec.upload_date,
1292 fnd_lobs_rec.expiration_date,
1293 fnd_lobs_rec.program_name,
1294 fnd_lobs_rec.program_tag,
1295 fnd_lobs_rec.file_data,
1296 fnd_lobs_rec.language,
1297 fnd_lobs_rec.oracle_charset,
1298 fnd_lobs_rec.file_format
1299 FROM fnd_lobs
1300 WHERE file_id = fid_from;
1301
1302 INSERT INTO fnd_lobs (file_id,
1303 file_name,
1304 file_content_type,
1305 upload_date,
1306 expiration_date,
1307 program_name,
1308 program_tag,
1309 file_data,
1310 language,
1311 oracle_charset,
1312 file_format)
1313 VALUES (fid_to,
1314 fnd_lobs_rec.file_name,
1315 fnd_lobs_rec.file_content_type,
1316 fnd_lobs_rec.upload_date,
1317 fnd_lobs_rec.expiration_date,
1318 fnd_lobs_rec.program_name,
1319 fnd_lobs_rec.program_tag,
1320 fnd_lobs_rec.file_data,
1321 fnd_lobs_rec.language,
1322 fnd_lobs_rec.oracle_charset,
1323 fnd_lobs_rec.file_format);
1324
1325 return fid_to;
1326
1327 exception
1328 when others then
1329 fnd_gfm.err_msg('copy_lob');
1330 raise;
1331 end copy_lob;
1332 /*--------------------------------------------------------------------------*/
1333 /*
1334 * set_file_format - This function sets the file_format for fnd_lobs CTX
1335 * Returns file_format derived from file_content_type in fnd_lobs.
1336 */
1337 FUNCTION set_file_format(l_file_content_type VARCHAR2) RETURN VARCHAR2 IS
1338 l_semicol_exists number;
1339 l_mime_type varchar2(256);
1340 l_file_format varchar2(10);
1341
1342 BEGIN
1343 -- Check l_file_content_type for a ;
1344 l_semicol_exists := instrb(l_file_content_type, ';', 1, 1);
1345
1346 IF substr(l_file_content_type, 1, 5) = 'text/' THEN
1347 return('TEXT');
1348 ELSIF l_semicol_exists > 0 THEN
1349 l_mime_type := substr(l_file_content_type, 1, l_semicol_exists-1);
1350 ELSIF l_semicol_exists = 0 THEN
1351 l_mime_type := l_file_content_type;
1352 ELSE
1353 return('IGNORE');
1354 END IF;
1355
1356 -- Bug 9276419 - Added distinct to this query since duplicate mime_types may
1357 -- exist as the unique index has been altered to now include file_ext
1358 -- i.e (mime_type,file_ext)
1359
1360 SELECT distinct ctx_format_code
1361 INTO l_file_format
1362 FROM fnd_mime_types
1363 WHERE mime_type = l_mime_type;
1364
1365 return(l_file_format);
1366
1367 exception
1368 when others then
1369 return('IGNORE');
1370
1371 END set_file_format;
1372 /*--------------------------------------------------------------------------*/
1373 /*
1374 * clob_to_blob This function takes my_clob which can be a varchar2, long,
1375 * or clob and creates a file in fnd_lobs. It will create a file id if none
1376 * is passed in. file_name will need an extension.
1377 */
1378
1379 PROCEDURE CLOB_TO_BLOB (
1380 my_clob clob,
1381 file_name varchar2,
1382 fid in out nocopy number,
1383 file_content_type varchar2 default null,
1384 language varchar2 default null,
1385 x_return_status OUT NOCOPY Varchar2,
1386 x_msg_count OUT NOCOPY Number,
1387 x_msg_data OUT NOCOPY Varchar2
1388 ) IS
1389 v_InputOffset BINARY_INTEGER;
1390 v_LOBLength BINARY_INTEGER;
1391 v_CurrentChunkSize BINARY_INTEGER;
1392 v_ChunkSize NUMBER := 10000;
1393 varbuf VARCHAR2(32767);
1394 l_blob_loc blob;
1395 v_content_type varchar2(100);
1396 v_language varchar2(20);
1397 retval INTEGER;
1398 G_EXC_ERROR EXCEPTION;
1399
1400 BEGIN
1401
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403 if file_name is null then
1404 fnd_message.set_name('FND','GR_FILE_DATA_NULL');
1405 raise G_EXC_ERROR;
1406 end if;
1407
1408 if fid is null then
1409 select fnd_lobs_s.nextval into fid from dual;
1410 end if;
1411
1412 if file_content_type is null then
1413 v_content_type := 'text/plain';
1414 else
1415 v_content_type := file_content_type;
1416 end if;
1417
1418 if language is null then
1419 v_language := userenv('LANG');
1420 else
1421 v_language := language;
1422 end if;
1423
1424 INSERT INTO fnd_lobs (
1425 file_id,
1426 file_name,
1427 file_content_type,
1428 upload_date,
1429 expiration_date,
1430 program_name,
1431 program_tag,
1432 file_data,
1433 language,
1434 oracle_charset,
1435 file_format )
1436 VALUES (
1437 fid,
1438 file_name,
1439 v_content_type,
1440 sysdate,
1441 null,
1442 'FNDAPI',
1443 null,
1444 empty_blob(),
1445 v_language,
1446 fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset),
1447 fnd_gfm.set_file_format(v_content_type));
1448
1449 select file_data into l_blob_loc from fnd_lobs
1450 where file_id = fid;
1451
1452
1453 DBMS_LOB.OPEN (l_blob_loc, DBMS_LOB.LOB_READWRITE);
1454
1455 -- First determine the input LOB length.
1456 v_LOBLength := DBMS_LOB.GETLENGTH(my_clob);
1457
1458 -- Set up the initial offsets. The input offset starts at the
1459 -- beginning, the output offset at the end.
1460 v_InputOffset := 1;
1461
1462 -- Loop through the input LOB, and write each chunk to the output
1463 -- LOB.
1464 LOOP
1465 -- Exit the loop when we've done all the chunks, indicated by
1466 -- v_InputOffset passing v_LOBLength.
1467 EXIT WHEN v_InputOffset > v_LOBLength;
1468 -- If at least v_ChunkSize remains in the input LOB, copy that
1469 -- much. Otherwise, copy only however much remains.
1470 IF (v_LOBLength - v_InputOffset + 1) > v_ChunkSize THEN
1471 v_CurrentChunkSize := v_ChunkSize;
1472 ELSE
1473 v_CurrentChunkSize := v_LOBLength - v_InputOffset + 1;
1474 END IF;
1475
1476 dbms_lob.read(my_clob, v_CurrentChunkSize, v_InputOffset, varbuf);
1477
1478 -- Write the current chunk.
1479 DBMS_LOB.writeappend(l_blob_loc, lengthb(varbuf), UTL_RAW.cast_to_raw(varbuf));
1480
1481 -- Increment the input offset by the current chunk size.
1482 v_InputOffset := v_InputOffset + v_CurrentChunkSize;
1483 END LOOP;
1484
1485 DBMS_LOB.CLOSE(l_blob_loc);
1486 --return x_return_status;
1487
1488 exception
1489 when G_EXC_ERROR then
1490 x_return_status := FND_API.G_RET_STS_ERROR;
1491 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1492 p_data => x_msg_data);
1493 when others then
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1495 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1496 p_data => x_msg_data);
1497
1498 END clob_to_blob;
1499 /*--------------------------------------------------------------------------*/
1500
1501 /*
1502 * delete_lob - this api deletes a lob in fnd_lobs using file_id.
1503 *
1504 */
1505
1506 PROCEDURE DELETE_LOB (fid number) IS
1507 BEGIN
1508 delete from fnd_lobs where file_id = fid;
1509 commit;
1510 exception
1511 when others then
1512 fnd_gfm.err_msg('delete_lob');
1513 raise;
1514 END delete_lob;
1515 /*--------------------------------------------------------------------------*/
1516
1517 FUNCTION construct_download_url(gfm_agent varchar2,
1518 file_id number,
1519 purge_on_view boolean default FALSE)
1520 return varchar2 is
1521 begin
1522 return construct_download_url2(gfm_agent,file_id,purge_on_view,false,true);
1523 exception
1524 when others then
1525 fnd_gfm.err_msg('construct_download_url');
1526 raise;
1527 end;
1528
1529 ------------------------------------------------------------------------------
1530
1531 /*
1532 * setRelease - returnes the current major release
1533 *
1534 */
1535 FUNCTION setRelease
1536 return number is
1537 begin
1538
1539 /* Bug 9860032 - release should not be based on this function definition
1540 * Backporting the R12 JAVA File Upload requires this to be changed.
1541 * Now using FND_RELEASE. Removed exception as it is no longer needed.
1542 *
1543 * Please see bug and previous version for details
1544 *
1545 */
1546
1547 return fnd_release.major_version;
1548
1549
1550 end;
1551
1552
1553 ------------------------------------------------------------------------------
1554
1555 /*
1556 * getRelease - returns the current major release
1557 *
1558 */
1559 FUNCTION getRelease
1560 return number is
1561 begin
1562 if (g_release_version = 0) then
1563 --call the SetRelease for the first time
1564 g_release_version := setRelease;
1565 end if;
1566
1567 return g_release_version;
1568 exception
1569 when others then
1570 fnd_gfm.err_msg('getRelease');
1571 raise;
1572 end;
1573
1574
1575 ------------------------------------------------------------------------------
1576
1577 /* verify_file_type - returns whether a file type is allowed to be uploaded into
1578 * fnd_lobs
1579 * return Y=do not retrict this filetype
1580 * return N=restrict this filetype
1581 */
1582 FUNCTION verify_file_type (file_name varchar2, file_ext varchar2)
1583 return varchar2 is
1584 l_module_source varchar2(256);
1585 l_allow varchar2(1);
1586 l_cnt number;
1587 l_file_ext varchar2(10);
1588 l_dflt_allow varchar2(1);
1589 l_ext_pos number;
1590
1591 begin
1592
1593 l_module_source := 'FND_GFM.VERIFY_FILE_TYPE';
1594 l_file_ext := file_ext;
1595
1596 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1597 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Begin');
1598 end if;
1599
1600 l_dflt_allow := fnd_profile.value('FND_SECURITY_FILETYPE_RESTRICT_DFLT');
1601
1602 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1603 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Profile value is: '||nvl(l_dflt_allow,'Profile not set'));
1604 end if;
1605
1606
1607 if (l_dflt_allow is null) then
1608
1609 -- Profile not defined default to allow
1610 l_dflt_allow := 'Y';
1611
1612 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1613 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Profile value is null...default to Y');
1614 end if;
1615
1616 end if;
1617
1618 if (file_ext is null and file_name is null) then
1619 return l_dflt_allow;
1620 elsif (file_ext is null and file_name is not null) then
1621 l_ext_pos := instr(file_name,'.',-1);
1622
1623 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1624 fnd_Log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'extension position is: '||l_ext_pos);
1625 end if;
1626
1627 if (l_ext_pos > 0) then
1628 l_file_ext := substr(file_name,instr(file_name,'.',-1)+1);
1629
1630 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1631 fnd_Log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Get extension from filename');
1632 end if;
1633 else
1634 l_file_ext := 'null';
1635
1636 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1637 fnd_Log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'No file extension - default extension to null');
1638 end if;
1639
1640 end if;
1641 end if;
1642
1643 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1644 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'file extension is: '||l_file_ext);
1645 end if;
1646
1647 select count(distinct(upper(allow_file_upload))) into l_cnt
1648 from fnd_mime_types where lower(file_ext) = lower(l_file_ext);
1649
1650 if (l_cnt > 1) then
1651
1652 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1653 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Same filetype exists with conflicting values - most restricted takes precedence');
1654 end if;
1655
1656 select distinct upper(allow_file_upload) into l_allow
1657 from fnd_mime_types where lower(file_ext) = lower(l_file_ext)
1658 and upper(allow_file_upload) = 'N';
1659
1660 else
1661
1662 select distinct upper(allow_file_upload) into l_allow
1663 from fnd_mime_types where lower(file_ext) = lower(l_file_ext);
1664
1665 end if;
1666
1667 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1668 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'File type: '||l_file_ext||' allowed: '||l_allow);
1669 end if;
1670
1671 if (l_allow in ('Y','N')) then
1672 return l_allow;
1673 else
1674
1675 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1676 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'allow_file_upload is null or an invalid value...default to profile value');
1677 end if;
1678
1679 return l_dflt_allow;
1680
1681 end if;
1682
1683 exception
1684 when no_data_found then
1685 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1686 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Default to profile value - No data found for file type: '||l_file_ext);
1687 end if;
1688
1689 return l_dflt_allow;
1690 when others then
1691 if (fnd_log.level_statement >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1692 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'When others exception occurred');
1693 end if;
1694
1695 fnd_gfm.err_msg('verify_file_type');
1696 raise;
1697 end verify_file_type;
1698
1699
1700 end FND_GFM;
[Home] [Help]