create or replace procedure BXSP_DATA_QB(
startDate in varchar2, --开始日期
endDate in varchar2, --结束日期
feeType in varchar2, --费用类型
procesNode in varchar2, --流程节点
auditor in varchar2 --审批人
)
is
v_startDate date; --开始日期
v_endDate date; --结束日期
v_bostitle varchar2(200); --标题
v_emanem varchar2(50); --报销人
v_acctDate varchar2(50); --报销日期
v_bosnum varchar2(30); --报销流水号
v_nreserved1 varchar2(20); --报销总金额
v_tablename varchar2(200);
v_sql varchar2(200);
cursor c_job is
--查询表名
select distinct ts.exttbl from t_bb_bomdes ts where ts.bomid='ZX_TZQB';
begin
d_startDate := to_date(trim(startDate), 'yyyy-MM-dd');
d_endDate := to_date(trim(endDate), 'yyyy-MM-dd');
open c_job;
fetch c_job into v_tablename;
if c_job % found then
v_sql :='select distinct
tcx.bostitle,
tcx.emname,
tcx.acct_date,
tcx.Bosnum,
tcx.nreserved1
into
v_bostitle,
v_emanem,
v_acctDate,
v_bosnum,
v_nreserved1
from
t_bb_cbobase tce,
t_wf_workitem tm,
t_bo_employee te,
|| v_tablename || tcx,
where
tm.bosid = tcx.bosid
and tcx.expenser = te.empid
and tce.bosdate between v_startDate and v_endDate
and tm.wkiuser = auditor
and tm.wkiname=procesNode
and tm.wkiprinumb !='0'';
execute immediate v_sql;
end if;
close c_job;
end BXSP_DATA_QB;
begin
execute immediate BXSP_DATA_QB('20170101','20170701','ZX_TZQB','付款经办','2179708');
end;
startDate in varchar2, --开始日期
endDate in varchar2, --结束日期
feeType in varchar2, --费用类型
procesNode in varchar2, --流程节点
auditor in varchar2 --审批人
)
is
v_startDate date; --开始日期
v_endDate date; --结束日期
v_bostitle varchar2(200); --标题
v_emanem varchar2(50); --报销人
v_acctDate varchar2(50); --报销日期
v_bosnum varchar2(30); --报销流水号
v_nreserved1 varchar2(20); --报销总金额
v_tablename varchar2(200);
v_sql varchar2(200);
cursor c_job is
--查询表名
select distinct ts.exttbl from t_bb_bomdes ts where ts.bomid='ZX_TZQB';
begin
d_startDate := to_date(trim(startDate), 'yyyy-MM-dd');
d_endDate := to_date(trim(endDate), 'yyyy-MM-dd');
open c_job;
fetch c_job into v_tablename;
if c_job % found then
v_sql :='select distinct
tcx.bostitle,
tcx.emname,
tcx.acct_date,
tcx.Bosnum,
tcx.nreserved1
into
v_bostitle,
v_emanem,
v_acctDate,
v_bosnum,
v_nreserved1
from
t_bb_cbobase tce,
t_wf_workitem tm,
t_bo_employee te,
|| v_tablename || tcx,
where
tm.bosid = tcx.bosid
and tcx.expenser = te.empid
and tce.bosdate between v_startDate and v_endDate
and tm.wkiuser = auditor
and tm.wkiname=procesNode
and tm.wkiprinumb !='0'';
execute immediate v_sql;
end if;
close c_job;
end BXSP_DATA_QB;
begin
execute immediate BXSP_DATA_QB('20170101','20170701','ZX_TZQB','付款经办','2179708');
end;