Dưới đây là nội dung Package đã sửa dựa trên Package chuẩn được Concurrent Request gọi đến xử lý hiển thị các Tệp output của Oracle EBS
Embedded Files
create or replace package xxx_webfile AUTHID CURRENT_USER as /* $Header: AFCPFILS.pls 120.2 2005/08/19 21:42:24 rckalyan ship $ */ /* Loilm Edit from FND_WEBFILE Date: 28-06-2016 */
function get_url(filename IN varchar2, expire_time IN number) return varchar2;
function create_id(name IN varchar2, node IN varchar2, lifetime IN number default 10, type IN varchar2 default 'EXCEL', x_mode IN varchar2 default 'TEXT') return varchar2; procedure set_debug(dbg IN boolean);
end;/create or replace package body xxx_webfile as /* $Header: AFCPFILB.pls 120.7.12010000.3 2010/03/19 11:20:54 mburra ship $ */
debug varchar2(1) := 'N';
/*-- *-- GENERIC_ERROR (Internal) *-- *-- Set error message and raise exception for unexpected sql errors. *-- */ procedure GENERIC_ERROR(routine in varchar2, errcode in number, errmsg in varchar2) is begin fnd_message.set_name('FND', 'SQL_PLSQL_ERROR'); fnd_message.set_token('ROUTINE', routine); fnd_message.set_token('ERRNO', errcode); fnd_message.set_token('REASON', errmsg); end;
function get_url(filename IN varchar2, expire_time IN number) return varchar2 is base varchar2(257); base_type varchar2(3) := 'CGI'; url varchar2(512); fname varchar2(255); node varchar2(50); mtype varchar2(80) := 'text/plain'; x_mode varchar2(30) := 'TEXT'; temp_id varchar2(32); pos number; svc varchar2(254); begin /* Get URL base. */ --First check IF there is a url base defined FOR CGI - enh# 4477258 IF (fnd_profile.defined('APPS_CGI_AGENT')) THEN fnd_profile.get('APPS_CGI_AGENT', base); END IF; --IF CGI agent is null then check the web agent --Also set the base_type to WEB IF (base IS NULL) THEN fnd_profile.get('APPS_WEB_AGENT', base); base_type := 'WEB'; END IF; if (base is null) then fnd_message.set_name('FND', 'FS-NO URL'); return null; end if; begin select s.file_path || s.file_name into fname from xxx_tt35_sent_files_v s where s.file_name = filename; exception when no_data_found then return null; end; mtype := 'application/vnd.ms-excel'; x_mode := 'BINARY'; begin SELECT upper(SYS_CONTEXT('USERENV', 'SERVER_HOST')) into node FROM dual; exception when no_data_found then return null; end; if (fnd_profile.defined('FS_SVC_PREFIX')) then fnd_profile.get('FS_SVC_PREFIX', svc); if (svc is not null) then svc := substr(svc || node, 1, 254); else svc := 'FNDFS_' || node; end if; else svc := 'FNDFS_' || node; end if; temp_id := xxx_webfile.create_id(fname, svc, expire_time, mtype, x_mode); base := Ltrim(Rtrim(base)); IF (base_type = 'WEB') THEN -- Strip any file path from the base URL by truncating at the -- third '/'. -- This leaves us with something like 'http://ap363sun:8000'. pos := instr(base, '/', 1, 3); if (pos > 0) then base := substr(base, 1, pos - 1); end if; -- 2638328 - security violation - removing login information from URL url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || temp_id; ELSIF (base_type = 'CGI') THEN IF (substr(base, length(base)) <> '/') THEN base := base || '/'; END IF; url := base || 'FNDWRR.exe?' || 'temp_id=' || temp_id; END IF; RETURN url; exception when others then generic_error('xxx_webfile.get_url', SQLCODE, SQLERRM); return null; end get_url;
function create_id(name IN varchar2, node IN varchar2, lifetime IN number default 10, type IN varchar2 default 'EXCEL', x_mode IN varchar2 default 'TEXT') return varchar2 is PRAGMA AUTONOMOUS_TRANSACTION; my_file_id varchar2(32); collision number; nc_encoding varchar2(240) := NULL; fs_prefix varchar2(254) := NULL; nmptr number; svc varchar2(254) := NULL; begin collision := 1; while (collision > 0) loop my_file_id := fnd_concurrent_file.get_file_id; select count(*) into collision from fnd_file_temp T where T.file_id = my_file_id; end loop; --- Begin Shared Appltop failover code begin if (fnd_profile.defined('FS_SVC_PREFIX')) then fnd_profile.get('FS_SVC_PREFIX', fs_prefix); end if; if (fs_prefix is null) then fs_prefix := 'FNDFS_'; end if; -- If prefix already attached - switch node and reattach later if (node LIKE (fs_prefix || '%')) then nmptr := length(fs_prefix) + 1; else -- No prefix, only switch node nmptr := 1; fs_prefix := NULL; end if; select substr(fs_prefix || 'APPLTOP_' || b.name, 1, 254) into svc from fnd_nodes n, fnd_appl_tops a, fnd_appl_tops b where n.node_name = substr(node, nmptr, length(node)) and n.node_id = a.node_id and a.name = b.name and b.node_id <> a.node_id and ROWNUM = 1; exception when NO_DATA_FOUND then svc := node; end; --- End Shared Appltop Failover code insert into fnd_file_temp (file_id, filename, node_name, mime_type, expires, transfer_mode, native_client_encoding, enable_logging) values (my_file_id, name, svc, type, sysdate + (lifetime / 1440), x_mode, nc_encoding, debug); commit; return my_file_id; exception when OTHERS then generic_error('xxx_webfile.create_id', SQLCODE, SQLERRM); rollback; return null; end create_id;
procedure set_debug(dbg IN boolean) IS begin if dbg then debug := 'Y'; else debug := 'N'; end if; end set_debug;
end;/
function get_url(filename IN varchar2, expire_time IN number) return varchar2;
function create_id(name IN varchar2, node IN varchar2, lifetime IN number default 10, type IN varchar2 default 'EXCEL', x_mode IN varchar2 default 'TEXT') return varchar2; procedure set_debug(dbg IN boolean);
end;/create or replace package body xxx_webfile as /* $Header: AFCPFILB.pls 120.7.12010000.3 2010/03/19 11:20:54 mburra ship $ */
debug varchar2(1) := 'N';
/*-- *-- GENERIC_ERROR (Internal) *-- *-- Set error message and raise exception for unexpected sql errors. *-- */ procedure GENERIC_ERROR(routine in varchar2, errcode in number, errmsg in varchar2) is begin fnd_message.set_name('FND', 'SQL_PLSQL_ERROR'); fnd_message.set_token('ROUTINE', routine); fnd_message.set_token('ERRNO', errcode); fnd_message.set_token('REASON', errmsg); end;
function get_url(filename IN varchar2, expire_time IN number) return varchar2 is base varchar2(257); base_type varchar2(3) := 'CGI'; url varchar2(512); fname varchar2(255); node varchar2(50); mtype varchar2(80) := 'text/plain'; x_mode varchar2(30) := 'TEXT'; temp_id varchar2(32); pos number; svc varchar2(254); begin /* Get URL base. */ --First check IF there is a url base defined FOR CGI - enh# 4477258 IF (fnd_profile.defined('APPS_CGI_AGENT')) THEN fnd_profile.get('APPS_CGI_AGENT', base); END IF; --IF CGI agent is null then check the web agent --Also set the base_type to WEB IF (base IS NULL) THEN fnd_profile.get('APPS_WEB_AGENT', base); base_type := 'WEB'; END IF; if (base is null) then fnd_message.set_name('FND', 'FS-NO URL'); return null; end if; begin select s.file_path || s.file_name into fname from xxx_tt35_sent_files_v s where s.file_name = filename; exception when no_data_found then return null; end; mtype := 'application/vnd.ms-excel'; x_mode := 'BINARY'; begin SELECT upper(SYS_CONTEXT('USERENV', 'SERVER_HOST')) into node FROM dual; exception when no_data_found then return null; end; if (fnd_profile.defined('FS_SVC_PREFIX')) then fnd_profile.get('FS_SVC_PREFIX', svc); if (svc is not null) then svc := substr(svc || node, 1, 254); else svc := 'FNDFS_' || node; end if; else svc := 'FNDFS_' || node; end if; temp_id := xxx_webfile.create_id(fname, svc, expire_time, mtype, x_mode); base := Ltrim(Rtrim(base)); IF (base_type = 'WEB') THEN -- Strip any file path from the base URL by truncating at the -- third '/'. -- This leaves us with something like 'http://ap363sun:8000'. pos := instr(base, '/', 1, 3); if (pos > 0) then base := substr(base, 1, pos - 1); end if; -- 2638328 - security violation - removing login information from URL url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || temp_id; ELSIF (base_type = 'CGI') THEN IF (substr(base, length(base)) <> '/') THEN base := base || '/'; END IF; url := base || 'FNDWRR.exe?' || 'temp_id=' || temp_id; END IF; RETURN url; exception when others then generic_error('xxx_webfile.get_url', SQLCODE, SQLERRM); return null; end get_url;
function create_id(name IN varchar2, node IN varchar2, lifetime IN number default 10, type IN varchar2 default 'EXCEL', x_mode IN varchar2 default 'TEXT') return varchar2 is PRAGMA AUTONOMOUS_TRANSACTION; my_file_id varchar2(32); collision number; nc_encoding varchar2(240) := NULL; fs_prefix varchar2(254) := NULL; nmptr number; svc varchar2(254) := NULL; begin collision := 1; while (collision > 0) loop my_file_id := fnd_concurrent_file.get_file_id; select count(*) into collision from fnd_file_temp T where T.file_id = my_file_id; end loop; --- Begin Shared Appltop failover code begin if (fnd_profile.defined('FS_SVC_PREFIX')) then fnd_profile.get('FS_SVC_PREFIX', fs_prefix); end if; if (fs_prefix is null) then fs_prefix := 'FNDFS_'; end if; -- If prefix already attached - switch node and reattach later if (node LIKE (fs_prefix || '%')) then nmptr := length(fs_prefix) + 1; else -- No prefix, only switch node nmptr := 1; fs_prefix := NULL; end if; select substr(fs_prefix || 'APPLTOP_' || b.name, 1, 254) into svc from fnd_nodes n, fnd_appl_tops a, fnd_appl_tops b where n.node_name = substr(node, nmptr, length(node)) and n.node_id = a.node_id and a.name = b.name and b.node_id <> a.node_id and ROWNUM = 1; exception when NO_DATA_FOUND then svc := node; end; --- End Shared Appltop Failover code insert into fnd_file_temp (file_id, filename, node_name, mime_type, expires, transfer_mode, native_client_encoding, enable_logging) values (my_file_id, name, svc, type, sysdate + (lifetime / 1440), x_mode, nc_encoding, debug); commit; return my_file_id; exception when OTHERS then generic_error('xxx_webfile.create_id', SQLCODE, SQLERRM); rollback; return null; end create_id;
procedure set_debug(dbg IN boolean) IS begin if dbg then debug := 'Y'; else debug := 'N'; end if; end set_debug;
end;/
Page updated
Google Sites
Report abuse