Hi guys, here I am again after a long time break :)
So, now a days people are asking about development stuff more than administration, so I decided to continue my post again.
Well above is the Form I created to email payslips to employees at the end of each month after payroll completed. In this both things covers , but this post I will be explaining for creating 11g reports in different formats like HTML, PDF, RTF, and EXCEL.
So as you knew that in 11g you need to first generate the report on application server, download it at client's PC and then you will able use that report.
Pre-requisites are :
1. You have WEBUTIL configured in your environment and should be working condition .
2. Global environment variables are already in default.env file like mentioned below, and you have called those in WHEN_FORM_INSTANCE trigger :
--- Defined in default.env file --
REP_SERVER=rep_wls_reports_erptstapp01_frminst_1
REP_PATH=/app/oracle/softsol/reports/bin/
REP_FDST=C:\TEMP\
REP_FSRC=/u00/temp/
REP_SRV=rep_wls_reports_erptstapp01_frminst_1
REP_URL=http://erptstapp01.oracle.us.com:7350
--- End in default.env file --
-- start WHEN_NEW_FORM_INSTANCE------
TOOL_ENV.GETVAR('REP_SRV',OUT_VAR);
:GLOBAL.REP_SERVER := OUT_VAR;
TOOL_ENV.GETVAR('REP_URL',OUT_VAR);
:GLOBAL.REP_URL := OUT_VAR;
TOOL_ENV.GETVAR('REP_PATH',OUT_VAR);
:GLOBAL.REP_PATH := OUT_VAR;
TOOL_ENV.GETVAR('REP_FSRC',OUT_VAR); -- to store report at Linux app server folder
:GLOBAL.REP_FSRC := OUT_VAR;
TOOL_ENV.GETVAR('REP_FDST',OUT_VAR); -- to store report at client's PC folder
:GLOBAL.REP_FDST := OUT_VAR;
Tool_Env.Getvar('REP_SERVER', :global.rptserver);
Tool_Env.Getvar('REP_PATH', :global.reports_path);
-- end WHEN_NEW_FORM_INSTANCE------
-- Run Report Button --- WHEN_BUTTON_PRESSED -----
declare
pl_id ParamList;
v_FILENAME varchar2(200);
sysd varchar2(20);
v_SRCDESNAME varchar2(100);
v_DST_DESNAME varchar2(100);
cursor c1 is
select s.emp_id emp , s.name ename, EM.EMP_TAB_EMAIL email , e.emp_type emptype, replace(to_char(lea_date,'MONTH-YYYY'),' ',null) ldate
from employee e , salary s , employee_emails em
where e.emp_id = s.emp_id
and S.emp_id = :empid
AND TRUNC(LEA_DATE) = :SAL_DATE1
and S.EMP_ID = em.emp_id
and em.active = 'Y';
BEGIN
select to_char(sysdate,'dd-mon-yyyy_hh24-mi-ss') into sysd from dual;
for rec in c1
loop
pl_id := Get_Parameter_List('tmpdata');
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
pl_id := Create_Parameter_List('tmpdata');
Add_Parameter(pl_id, 'EMPID', TEXT_PARAMETER, rec.emp);
Add_Parameter(pl_id, 'SAL_DATE', TEXT_PARAMETER, TO_CHAR(:SAL_DATE1,'DD-MON-YYYY'));
Add_Parameter(pl_id, 'TYPE', TEXT_PARAMETER, rec.emptype);
v_FILENAME:=rec.emp||'_'||replace(rec.ename,' ','_')||'_'||rec.ldate;
v_SRCDESNAME:= :global.REP_FSRC||v_FILENAME||'_'||sysd||'_'||'Payslip';
v_DST_DESNAME:= :global.REP_FDST||v_FILENAME||'_'||sysd||'_'||'Payslip';
run_report ('payslip_email', :L_PRINT, pl_id, v_SRCDESNAME,v_DST_DESNAME); --Calling RUN_REPORT Form function can performs run report and generate output , L_PRINT is a list item on Form contain HTML, RTF, PDF and SPREADSHEET output formats
end loop;
END;
----- End WHEN-BUTTON-PRESSED ------
--- Start RUN_REPORT Form Function -----
PROCEDURE run_report (file_name varchar2, P_print_mode varchar2 default 'PDF',pl_id ParamList,v_SRCDESNAME varchar2,v_DST_DESNAME varchar2 ) IS
lv_report VARCHAR2(100);
rep_status VARCHAR2(100);
lv_repid REPORT_OBJECT;
repserver varchar2(200):=:global.rptserver;
vjob_id varchar2(100);
V_REPORTSTATUS varchar2(100);
BEGIN
lv_repid:=FIND_REPORT_OBJECT('rp2rro'); ---REPORT NAME
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_FILENAME, :global.reports_path||lower(file_name)||'.rep');
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_COMM_MODE , SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_SERVER , repserver);
IF P_print_mode <> 'PRINT' THEN
ADD_PARAMETER(pl_id, 'DESTYPE', TEXT_PARAMETER, FILE);
ADD_PARAMETER(pl_id, 'DESFORMAT', TEXT_PARAMETER, P_print_mode);
IF p_print_mode='ENHANCEDSPREADSHEET' THEN
ADD_PARAMETER(pl_id, 'DESNAME', TEXT_PARAMETER, v_SRCDESNAME||'.xls');
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_DESNAME, v_SRCDESNAME || '.xls');
ELSE
ADD_PARAMETER(pl_id, 'DESNAME', TEXT_PARAMETER, v_SRCDESNAME||'.'||SUBSTR(P_print_mode, 1, 3));
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_DESNAME, v_SRCDESNAME || '.' ||SUBSTR(P_print_mode, 1, 3));
END IF;
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_DESTYPE, FILE);
SET_REPORT_OBJECT_PROPERTY(lv_repid, REPORT_DESFORMAT, P_print_mode);
ELSE
ADD_PARAMETER(pl_id, 'DESFORMAT', TEXT_PARAMETER, P_print_mode);
END IF;
lv_report := RUN_REPORT_OBJECT (lv_repid,pl_id);
vjob_id :=substr(lv_report,length(repserver)+2, length(lv_report));
V_REPORTSTATUS := REPORT_OBJECT_STATUS(lv_report);
WHILE V_REPORTSTATUS in ('RUNNING','OPENING_REPORT','ENQUEUED')
LOOP
V_REPORTSTATUS := report_object_status(lv_report);
END LOOP;
IF V_REPORTSTATUS = 'FINISHED' THEN
IF P_print_mode <> 'PRINT' THEN
IF p_print_mode='ENHANCEDSPREADSHEET' THEN
DOWNLOAD_AS(v_SRCDESNAME||'.xls', v_DST_DESNAME||'.xls');
MESSAGES ('FILE ' ||v_DST_DESNAME || '.xls has been created. You can view it ');
ELSE
DOWNLOAD_AS(v_SRCDESNAME||'.' ||SUBSTR(P_print_mode, 1, 3), v_DST_DESNAME||'.' ||SUBSTR(P_print_mode, 1, 3));
MESSAGES ('FILE ' ||v_DST_DESNAME || '.' ||SUBSTR(P_print_mode, 1, 3)||' has been created. You can view it ');
:PARAMETER.P_REPORT_DEST_FILE_NAME:=v_DST_DESNAME || '.' ||SUBSTR(P_print_mode, 1, 3);
END IF;
ELSE
Web.show_document('/reports/rwservlet/getjobid='||vjob_id||'?server='||repserver,'_BLANK');
SYNCHRONIZE;
END IF;
END IF;
END;
-- End RUN_REPORT
-- Start DOWNLOAD_AS --- this function able download your report from Application server to client's PC under C:\TEMP
PROCEDURE DOWNLOAD_AS(p_src_file_name IN VARCHAR2,
p_dst_file_name IN VARCHAR2) IS
l_success boolean;
l_bare_filename varchar2(100);
BEGIN
l_success := webutil_file_transfer.AS_to_Client_with_progress
(clientFile => p_dst_file_name
,serverFile => p_src_file_name
,progressTitle => 'File generation in progress'
,progressSubTitle => 'Please wait'
);
if l_success
then
message('File downloaded successfully from the Application Server');
else
message('File download from Application Server failed');
end if;
exception
when others
then
message('File download failed: '||sqlerrm);
END;
-- End DOWNLOAD_AS
Hope this helps , and in case of any issues , do email me : kamarsyed@gmail.com
Cheers !!!
No comments:
Post a Comment