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