Generic code for Oracle forms calling reports

PROCEDURE CALL_REPORT(rptname varchar2,paramval varchar2 default null,paraform varchar2 default 'N') IS
plid paramlist;
a_paralist varchar2(4000);
report_id Report_Object;
report_job_id VARCHAR2(100);
report_status VARCHAR2(100);
vc_use_report_server char(1) := 'N';
vs_report_server varchar2(256) := 'GEOREPSRV';
vs_report_server_url varchar2(256) := 'dev60cgi/rwcgi60';
vs_report_format VARCHAR2(100) := 'pdf';
BEGIN
dbms_application_info.set_module('GEORPT',rptname);
if paramval is null then
a_paralist := 'PARAMFORM=NO';
else
a_paralist := 'PARAMFORM=NO' paramval;
end if;
-- vincent
-- replace spaces with %20 for web.show_document
a_paralist := replace(a_paralist,' ','%20');
--

plid := buildparamlist(a_paralist);

if instr(UPPER(a_paralist),'COPIES=') > 0 then
delete_parameter(plid,'COPIES');
end if;
if instr(UPPER(a_paralist),'DESFORMAT=') > 0 then
vs_report_format := substr(a_paralist,(10 + instr(UPPER(a_paralist),'DESFORMAT=')),(instr(UPPER(a_paralist),'',instr(UPPER(a_paralist),'DESFORMAT=')) - instr(UPPER(a_paralist),'DESFORMAT=') - 10));
delete_parameter(plid,'DESFORMAT');
end if;
if instr(UPPER(a_paralist),'BACKGROUND=') > 0 then
delete_parameter(plid,'BACKGROUND');
end if;
if instr(UPPER(a_paralist),'CURRENCY=') > 0 then
delete_parameter(plid,'CURRENCY');
end if;
if instr(UPPER(a_paralist),'DECIMAL=') > 0 then
delete_parameter(plid,'DECIMAL');
end if;
if instr(UPPER(a_paralist),'DESNAME=') > 0 then
delete_parameter(plid,'DESNAME');
end if;
if instr(UPPER(a_paralist),'DESTYPE=') > 0 then
delete_parameter(plid,'DESTYPE');
end if;
if instr(UPPER(a_paralist),'MODE=') > 0 then
delete_parameter(plid,'MODE');
end if;
if instr(UPPER(a_paralist),'ORIENTATION=') > 0 then
delete_parameter(plid,'ORIENTATION');
end if;
if instr(UPPER(a_paralist),'PRINTJOB=') > 0 then
delete_parameter(plid,'PRINTJOB');
end if;
if instr(UPPER(a_paralist),'THOUSANDS=') > 0 then
delete_parameter(plid,'THOUSANDS');
end if;

begin
select REF_STRING into vc_use_report_server from SYS_REF_TABLE where ref_code = 'USEREPSRV';
if vc_use_report_server not in ('Y','N') then
vc_use_report_server := 'N';
end if;
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('USEREPSRV', 'Use Report Server','N');
commit;
vc_use_report_server := 'N';
when others then
vc_use_report_server := 'N';
end;



begin
select REF_STRING into vs_report_server from SYS_REF_TABLE where ref_code = 'GEOREPSRV';
vs_report_server := nvl(vs_report_server,'GEOREPSRV');
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('GEOREPSRV', 'GeO Report Server','GEOREPSRV');
commit;
vs_report_server := 'GEOREPSRV';
when others then
vs_report_server := 'GEOREPSRV';
end;
begin
select REF_STRING into vs_report_server_url from SYS_REF_TABLE where ref_code = 'GEOREPSRVURL';
vs_report_server_url := nvl(vs_report_server_url,'GEOREPSRVURL');
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('GEOREPSRVURL', 'GeO Report Server URL','dev60cgi/rwcgi60');
commit;
vs_report_server_url := 'dev60cgi/rwcgi60';
when others then
vs_report_server_url := 'dev60cgi/rwcgi60';
end;
report_id:= find_report_object('REPORT1');
if GET_APPLICATION_PROPERTY(USER_INTERFACE) <> 'WEB' then
vc_use_report_server := 'N';
END IF;
if vc_use_report_server = 'N' then
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,SCREEN);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,rptname);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,vs_report_format);
report_job_id:=run_report_object(report_id,plid);
else
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,CACHE);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,vs_report_format);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,rptname);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,vs_report_server);
report_job_id :=run_report_object(report_id,plid);
report_status := report_object_status(report_job_id);

if report_status = 'FINISHED' THEN
IF UPPER(vs_report_format) = 'RTF' THEN
Web.Show_Document(vs_report_server_url '/' url_encode2('getjobid' substr(report_job_id,length(vs_report_server) + 2)) '?' url_encode2('server') '=' url_encode2(vs_report_server) '&mimetype=application/msword', '_blank');
ELSE
Web.Show_Document(vs_report_server_url '/' url_encode2('getjobid' substr(report_job_id,length(vs_report_server) + 2)) '?' url_encode2('server') '=' url_encode2(vs_report_server), '_blank');
END IF;
else
msg('Report failed with message ' report_status chr(13) substr(a_paralist,instr(UPPER(a_paralist),'P_USERNAME'),30) );
end if;
end if;
dbms_application_info.set_module('','');
END;