Call a Drilldown Report from a Report with Oracle Developer10g

--=======================================
CREATE TABLE REPORT_STANDARD
(
RPT_SERVER VARCHAR2(50 BYTE),
RPT_BUILDER VARCHAR2(50 BYTE),
RPT_FORMAT VARCHAR2(20 BYTE),
RPT_KEY VARCHAR2(10 BYTE),
MODULE VARCHAR2(10 BYTE),
RPT_PATH VARCHAR2(2000 BYTE),
RPT_URL VARCHAR2(2000 BYTE)
);

ALTER TABLE REPORT_STANDARD ADD (
CONSTRAINT PK_REPORT_STANDARD
PRIMARY KEY(MODULE));

--=======Data=====================================================
SET DEFINE OFF;
Insert into REPORT_STANDARD
(RPT_SERVER, RPT_BUILDER, RPT_FORMAT, RPT_KEY, MODULE,
RPT_PATH, RPT_URL)
Values
('rep_micr', 'rep', 'pdf', 'micr', 'MICR',
'D:\Sampad\forms\Reports\', 'http://10.11.201.121:8889/reports/rwservlet?');
Insert into REPORT_STANDARD
(RPT_SERVER, RPT_BUILDER, RPT_FORMAT, RPT_KEY, MODULE,
RPT_PATH, RPT_URL)
Values
('rep_outward', 'rep', 'pdf', 'out', 'OUTWARD',
'D:\Sampad\forms\Reports\', 'http://10.11.201.121:8889/reports/rwservlet?');
Insert into REPORT_STANDARD
(RPT_SERVER, RPT_BUILDER, RPT_FORMAT, RPT_KEY, MODULE,
RPT_PATH, RPT_URL)
Values
('rep_inward', 'rep', 'pdf', 'inw', 'INWARD',
'D:\Sampad\forms\Reports\', 'http://10.11.201.121:8889/reports/rwservlet?');
COMMIT;


--=========================================================================================
CREATE OR REPLACE Procedure Dpr_Run_Report_Drilldown(p_Rpt_name in varchar2,
p_Dest in varchar2 default 'C',
p_Module in varchar2,
p_Url out varchar2
)
is

v_url varchar2(2000) ;
v_report_name varchar2(100) :=p_Rpt_name ;
v_rpt_name_with_path varchar2(500) ;
v_dest varchar2(20) := p_Dest ;
v_rpt_builder report_standard.rpt_builder%type;
v_rpt_key report_standard.rpt_key%type ;
v_rpt_format report_standard.rpt_format%type ;
v_rpt_server report_standard.rpt_server%type ;
v_rpt_path report_standard.rpt_path%type ;
v_rpt_url report_standard.rpt_url%type ;
begin


--------------------------------------- Retrieving Report info from Report_Standard Table -------------------
begin
select rpt_server ,
rpt_builder,
rpt_format ,
rpt_key ,
rpt_path ,
rpt_url
into v_rpt_server ,
v_rpt_builder,
v_rpt_format ,
v_rpt_key ,
v_rpt_path ,
v_rpt_url
from report_standard
where upper(module)= upper(p_module);

v_rpt_name_with_path:= v_rpt_path||v_report_name;

exception
when others then null;
end;

--------------------------Report Destination------------------------

v_dest:=upper(p_dest);
if v_dest ='C' then
v_dest:='cache';
elsif v_dest='P' then
v_dest:='printer';
elsif v_dest='M' then
v_dest:='mail';
elsif v_dest='F' then
v_dest:='file';
end if;

--------------------------Start Call Report------------------------
v_url:= v_rpt_url||v_rpt_key
||'+desformat=' ||v_rpt_format
||'+destype=' ||v_dest
--||'+mimetype='||'application/vnd.ms-excel'
||'+report=' ||v_rpt_name_with_path;

p_Url:= v_url;


Exception
When others then null;
End Dpr_Run_Report_Drilldown;
/

--==================================================================
--Call from a report with format trigger

function trigger_name return boolean is

v_url varchar2(2000);
Begin

Dpr_Run_Report_Drilldown(Report_name, 'C','OUTWARD',v_url);

SRW.SET_HYPERLINK(v_url||'+Paramerte_name1='||:Paramerte_name1
||'+Paramerte_name1='||:Paramerte_name2
);
return (TRUE);

Exception
When others then return (false);
End;

--=============================================