declare
v_show_document VARCHAR2 (2000) := 'http://localhost:9002/reports/rwservlet?';
v_connect VARCHAR2 (200) := 'userid=jayson/jsn@orcl';
v_report_server VARCHAR2 (30) := 'rep_wls_reports_abdulquium';
v_report_name VARCHAR2(100) := 'E:\JAYSON\JNPBD\Reports\jnpbd_standard_batrch.rdf';
v_format VARCHAR2(12) := 'PDF'; -- PDF or SPREADSHEET
begin
v_show_document := v_show_document
|| v_connect
-- Report server
|| '&server='
|| v_report_server
-- Report name
|| '&report='||v_report_name
-- Reports parameters
|| '&destype=CACHE'
|| '&desformat='||v_format
|| '¶mform=no'
|| '&P_SBD_NO='||:JNPBD_STN_MAST.SBD_NO --Parameter Passed to report
/*
|| '&date_from='||:ACCOUNTS_REPORTS_DATE.date_from --Parameter Passed to report
|| '&date_to='||:ACCOUNTS_REPORTS_DATE.date_to --Parameter Passed to report
|| '&acc_id_from='||:ACCOUNTS_REPORTS.cb_acc_id1 --Parameter Passed to report
|| '&acc_id_to='||:ACCOUNTS_REPORTS.cb_acc_id2 --Parameter Passed to report
|| '&Catagory_from='||:ACCOUNTS_REPORTS.cb_acc_id1 --Parameter Passed to report
|| '&Catagory_to='||:ACCOUNTS_REPORTS.cb_acc_id2 --Parameter Passed to report
|| '&Bl_noo='||:ACCOUNTS_REPORTS.BL_NO --Parameter Passed to report
|| '&bl_lot_noo='||:ACCOUNTS_REPORTS.LOT_NO --Parameter Passed to report
|| '&Lot_no_sm='||:ACCOUNTS_REPORTS.Lot_no_sm --Parameter Passed to report
|| '&SUPPLIERR='||:ACCOUNTS_REPORTS.SUPPLIERR --Parameter Passed to report
|| '&cleared_byy='||:ACCOUNTS_REPORTS.CLEARED_BY --Parameter Passed to report
|| '&chassis_noo='||:ACCOUNTS_REPORTS.chassis_no --Parameter Passed to report
|| '&codee='||:ACCOUNTS_REPORTS.chassis_code --Parameter Passed to report
|| '&modell='||:ACCOUNTS_REPORTS.modell --Parameter Passed to report
|| '&yardd='||:ACCOUNTS_REPORTS.yard_name --Parameter Passed to report
|| '&colorr='||:ACCOUNTS_REPORTS.colorr --Parameter Passed to report
|| '&Companyy='||:ACCOUNTS_MISC.company --Parameter Passed to report
|| '&Remarks='||:ACCOUNTS_REPORTS.LOT_NO_SM --Parameter Passed to report
*/
;
web.show_document(v_show_document);
end;
2#
PROCEDURE PRINT_PROC IS
repid REPORT_OBJECT;
v_rep VARCHAR2(1000);
rep_status varchar2(2000);
vjob_id VARCHAR2(1000) :='';
v_url varchar2(1000) := '';
pl_id ParamList;
pl_name VARCHAR2(1000) := 'RP';
v_REPORT_DESNAME varchar2(2000) := '';
v_rptserver varchar2(1000):=:global.RWS;
v_host varchar2(1000):=:global.IP;
v_path varchar2(1000):=:GLOBAL.MENU_PATH;
BEGIN
pl_id := Get_Parameter_List(pl_name);
IF Id_Null(pl_id) THEN
null;
ELSE
destroy_Parameter_List(pl_name);
END IF;
pl_id := Create_Parameter_List(pl_name);
repid := find_report_object('REPORT46');
Add_Parameter(pl_id, 'P_SBD_NO', TEXT_PARAMETER,:JNPBD_STN_MAST.SBD_NO);
--Add_Parameter(pl_id, 'P_PRODUCT_CD', TEXT_PARAMETER, :JNPBD_STN_MAST.PRODUCT_CD);
--if :REPOT_LIST=1 then
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_FILENAME, 'E:\JAYSON\JNPBD\Reports\jnpbd_standard_batrch.RDF');
/*
elsif :REPOT_LIST=2 then
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_FILENAME, v_path||'\BD_B\REPORTS\BD_B_MFG_PROCESS_PRODUCT_WSIE.jsp');
elsif :REPOT_LIST=3 then
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_FILENAME, v_path||'\BD_B\REPORTS\BD_B_MFG_PRECUTION.jsp');
*/
--end if;
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_SERVER, v_rptserver);
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESTYPE, CACHE);
-- SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESFORMAT,:REPOT_FMT);
SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESFORMAT, 'PDF');
v_rep := RUN_REPORT_OBJECT(repid,pl_name);
rep_status := REPORT_OBJECT_STATUS(v_rep);
vjob_id := substr(v_rep, 6, length(v_rep));
vjob_id := substr(v_rep,instr(v_rep,'_',-1)+1);
if rep_status = 'FINISHED' then
message('Report Completed'||v_rep, no_acknowledge);
WEB.SHOW_DOCUMENT('http://'||v_host||'/reports/rwservlet/getjobid'||ltrim(vjob_id,'_')||'?'||'server='||v_rptserver,'_blank');
else
message('Error when running report.'||v_rep, no_acknowledge);
end if;
EXCEPTION
WHEN OTHERS THEN
MESSAGE('Error '||v_rep||error_text,no_acknowledge);
END;
=================
/* Formatted on 1/18/2013 7:01:45 PM (QP5 v5.114.809.3010) */
PROCEDURE view_report
IS
v_canteenlocation varchar2 (500) := NULL;
v_item_name varchar2 (500);
PMWHERE varchar2(1000);
V_URL varchar2(1000);
v_report_server_name varchar2(1000);
v_ip_local varchar2(1000);
v_ip_real varchar2(1000);
v_item_name_t varchar2 (500);
v_path varchar2 (500);
v_sub_path varchar2 (50);
v_format varchar2 (20):='PDF'; --enhancedspreadsheet
pm_where varchar2 (3000);
v_char_val varchar2 (100);
v_date_val date;
v_char_val_t varchar2 (100);
v_date_val_char varchar2 (100);
v_report_id number;
v_rdf_name varchar2 (100);
v_report_name varchar2 (100);
CURSOR c1 (p_report_id IN number)
IS
SELECT b.report_id ,
b.parameter_id parameter_id_pk ,
a.parameter_title parameter_name,
b.parameter_title,
DEFAULT_VALUE,
mandatory,
lov_button,
lov_id,
a.parameter_datatype_id,
tool_tips,
b.parameter_datatype_id||'0'||sl parameter_id
FROM report_parameter_list a, report_parameter b
WHERE a.parameter_id = b.parameter_id
and a.parameter_datatype_id = b.parameter_datatype_id
AND b.report_id = p_report_id
order by to_number(b.parameter_id||'0'||sl) asc;
pl_id ParamList;
vloc varchar2(100):=:global.r_path;
BEGIN
--- collect report name
v_report_id := :obj_id_R ;
select max(obj_name ) into v_report_name
from sm_object_list
where obj_id=v_report_id;
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, 'PARAMFORM', TEXT_PARAMETER, 'NO');
FOR i IN c1 (v_report_id)
LOOP
v_item_name := 'PARAMETER_LIST.P_' || i.parameter_id;
v_item_name_t := 'PARAMETER_LIST.TP_' || i.parameter_id;
IF i.parameter_id BETWEEN 300 AND 399
THEN
--- retrive item value
v_date_val :=
GET_ITEM_PROPERTY (v_item_name,
database_value);
v_date_val_char:=to_CHAR(v_date_val,'DD-MON-RR');
-----
IF i.mandatory = 1 AND TRUNC(v_date_val) IS NULL
THEN
msg( 'You must provide value in the field '''
|| i.parameter_title
|| ''' for the selected report');
GO_BLOCK ('PARAMETER_LIST');
GO_ITEM (v_item_name);
RAISE form_trigger_failure;
END IF;
scs.rp_parameter_update.ins_report_param_default_value (
:global.userid ,
i.report_id ,
i.parameter_id_pk,
v_date_val,
null,
v_char_val_t);
Add_Parameter(pl_id,i.parameter_name ,TEXT_PARAMETER,v_date_val);
if i.parameter_name is not null and v_date_val is not null then
PMWHERE := RTRIM (PMWHERE||LTRIM (RTRIM ('''&''', ''''), '''')||i.parameter_name|| '='|| v_date_val|| '||','||');
end if;
ELSE
--- retrive item value
v_char_val :=
GET_ITEM_PROPERTY (v_item_name,
database_value);
v_char_val_t :=
GET_ITEM_PROPERTY (v_item_name_t,
database_value);
---- mandatory paprameter check
IF i.mandatory = 1 AND v_char_val IS NULL
THEN
msg( 'You must provide value in the field '''
|| i.parameter_title
|| ''' for the selected report');
GO_BLOCK ('PARAMETER_LIST');
GO_ITEM (v_item_name);
RAISE form_trigger_failure;
END IF;
scs.rp_parameter_update.ins_report_param_default_value (
:global.userid ,
i.report_id ,
i.parameter_id_pk,
null,
v_char_val,v_char_val_t);
Add_Parameter(pl_id,i.parameter_name ,TEXT_PARAMETER,v_char_val);
if i.parameter_name is not null and v_char_val is not null then
PMWHERE := RTRIM (PMWHERE||LTRIM (RTRIM ('''&''', ''''), '''')||i.parameter_name|| '='|| v_char_val|| '||','||');
end if;
END IF;
END LOOP;
Add_Parameter(pl_id,'p_userid' ,TEXT_PARAMETER,:global.userid);
if :global.dpcode <> 'FAC' THEN
v_canteenlocation :='HO';
ELSE
v_canteenlocation :='FACTORY' ;
END IF;
-- PMWHERE :=RTRIM ( PMWHERE|| LTRIM (RTRIM ('''&''', ''''), '''')|| I.RDF_PARAMETER|| '='|| V_FORM_FIELD || '||','||');
-- PMWHERE := PMWHERE||'&p_user_id='||:GLOBAL.g_user_id||'&p_obj_id='||V_REPORT_ID;
select max(name ) ,max(ip_local ),max(ip_real ) into v_report_server_name,v_ip_local,v_ip_real
from report_server_name
where is_active=1;
if v_report_server_name is null then
v_report_server_name :='rep_wls_reports_webserver12c';
end if;
if v_ip_real is null then
v_ip_real :='180.148.211.170';
end if;
V_URL := 'http://'||v_ip_real||':9002/reports/rwservlet?userid='||:dbuser||'/'||:dbpass||'@'||:dbname||'&server='||v_report_server_name
|| '&desformat='
|| V_FORMAT
|| '&destype=cache&report='
|| 'Z:\Stepserp\'||:path
|| '\'
|| v_report_name
|| '.rdf'
|| LTRIM (PMWHERE, '''');
:o_msg:='';
--:o_msg:=v_url;
--message('url '||v_url);
web.show_document(v_url);
--- Report Calling
--Add_Parameter(pl_id,'P_canteenlocation' ,TEXT_PARAMETER,v_canteenlocation);
--- report calling
/*
Run_Product(REPORTS, :global.r_dir||v_report_name, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
*/
exception
when others then
:o_msg:=sqlerrm;
END;
No comments:
Post a Comment