Run Report Server Reports From PL/Sql Script


DECLARE
V_Start_Date_Time    VARCHAR2(100);
V_Gateway            VARCHAR2(1000) :='http://reportstest/reports/rwservlet/';
V_Desname            VARCHAR2(4000);
V_Des_Server   VARCHAR2(4000) :='\\testserver.net\01\Files\AR\XXREPORTS\02\';
--Location of the server to store the Report output we can also pass the email id like xx@maildomain.com
V_Filename VARCHAR2(1000);
V_Userid             VARCHAR2(100):= apps/apps@scott ; --Database login credentials
v_apsuid             VARCHAR2(100) :=;
V_Paramlist          srw_paramlist;
V_Job_Ident          srw.Job_Ident;
V_Sta_Ident          srw.Status_Record;

BEGIN

For I_Index in (SELECT   *
FROM     xxAP_INVOICES
WHERE    1=1
AND      ORG_ID =83
--AND TRUNC(created_date) = TRUNC(SYSDATE) - 1 ; --AND      Request_id =13158029; -- TRUNC(created_date) = TRUNC(SYSDATE) - 0;
AND      Request_id =10701633)
LOOP

V_Filename  :=TO_CHAR(I_Index.invoice_date,'DD.MM.RRRR')||'_'||I_Index.customer_number||'.pdf';
V_Desname   := V_Des_Server||V_Filename;
BEGIN
SELECT   TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
INTO     V_Start_Date_Time
FROM     dual;

V_Paramlist:= srw_paramlist(srw_parameter('',''));
SRW.CLEAR_PARAMETER_LIST(V_Paramlist);
SRW.ADD_PARAMETER(V_Paramlist, 'GATEWAY',V_Gateway);
SRW.ADD_PARAMETER(V_Paramlist, 'REPORT','XXREPORT.rdf');
SRW.ADD_PARAMETER(V_Paramlist, 'DESTYPE','FILE');
SRW.ADD_PARAMETER(V_Paramlist, 'DESFORMAT','PDF');
SRW.ADD_PARAMETER(V_Paramlist, 'DESNAME',V_Desname);
SRW.ADD_PARAMETER(V_Paramlist, 'P_Report_Parameter1',I_Index.value1);
SRW.ADD_PARAMETER(V_Paramlist, 'P_Report_Parameter2',I_Index.value2);
SRW.ADD_PARAMETER(V_Paramlist, 'P_Report_Parameter3',I_Index.value3);
SRW.ADD_PARAMETER(V_Paramlist, 'USERID',V_Userid);

UTL_HTTP.SET_TRANSFER_TIMEOUT(180);

V_Job_Ident := SRW.RUN_REPORT(V_Paramlist);
--V_Sta_Ident := SRW.REPORT_STATUS(V_Job_Ident);
SRW.CLEAR_PARAMETER_LIST(V_Paramlist);
dbms_output.put_line('Created File :'||V_Filename);


EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
NULL;
END;
END LOOP;
END;

----If report out put files were not created check the Scehduled jobs and find the error

Comments

Popular posts from this blog

Queries For Oracle Interface Errors Records.

Customising PO Output For Communication Report in Oracle Purchasing

Oracle APPS Useful Queries