oracle使用临时表实现复杂报表

create or replace package body PCK_SUMREPORT is--往来单位余额汇总表/*-- Create table 创建全局临时表create global temporary table TMP_CORPREMSUM( COMPID varchar2(20), COMP varchar2(100), CORPID varchar2(20), CORP varchar2(200), REMTYPE VARCHAR2(50), BEGINBAL NUMBER(16,6), THISIN NUMBER(16,6), THISOUT NUMBER(16,6), ENDBAL NUMBER(16,6) )ON COMMIT DELETE ROWS;*/procedure CORP_REMSUM( n_i_CompID IN varchar2, v_i_StartDate IN varchar2, v_i_EndDate IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype )is v_t_sqlstr long;begindelete from TMP_CORPREMSUM;--begin本期运输费用--本期贷方金额--加工、销售委外运输insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,c.remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (select a.compid,a.comp,a.corpid,a.remtype, 0 as beginbal,sum(a.thisin) as thisin,sum(a.thisout) as thisout,0 as endbalfrom(select a.compid, a.comp, d.TransCorpID as corpid, '运输费用' as remtype, 0 as beginbal, 0 as thisin, round(a.saleprice * c.realweight,2) as thisout, 0 as endbal from etr_transline a join etr_transmain d on nvl(d.isdel, 0) = 0 and d.id = a.mainid and nvl(d.transcorpid, 0) <> 0 join es_pickmain b on nvl(a.isdel, 0) = 0 and nvl(b.isdel, 0) = 0 and a.allbillid = b.id and a.compid = b.compid join est_outmain c on c.ispick=1 and nvl(c.isdel, 0) = 0 and c.pkbillid = b.id and a.compid = c.compid where a.compid=n_i_CompID and trunc(c.realdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(c.realdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') union all--调出委外运输select a.compid, a.comp, d.TransCorpID as corpid, '运输费用' as remtype, 0 as beginbal, 0 as thisin, round(a.saleprice * c.realweight,2) as thisout, 0 as endbal from etr_transline a join etr_transmain d on nvl(d.isdel, 0) = 0 and d.id = a.mainid and nvl(d.transcorpid, 0) <> 0 join est_motpickmain b on nvl(a.isdel, 0) = 0 and nvl(b.isdel, 0) = 0 and a.allbillid = b.id and a.compid = b.compid join est_outmain c on c.ispick=1 and nvl(c.isdel, 0) = 0 and c.pkbillid = b.id and a.compid = c.compid where a.compid=n_i_CompID and trunc(c.realdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(c.realdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd')union all--本期借方金额SELECT a.compid, a.comp, a.corpid, '运输费用' as remtype, 0 as beginbal, a.MoneySum as thisin, 0 as thisout, 0 as endbal FROM efi_inpaymain a WHERE NVl(a.IsDel, 0) = 0 and a.billtype = '113003' and a.compid=n_i_CompID and trunc(a.BizDate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.BizDate) <= to_date(v_i_EndDate, 'yyyy-mm-dd'))a group by a.compid,a.comp,a.corpid,a.remtype --end本期运输费用union all--begin期初运输费用--期初贷方金额--加工、销售委外运输select b.compid,b.comp,b.corpid,b.remtype, sum(b.beginbal) as beginbal,0 as thisin,0 as thisout,0 as endbalfrom(select a.compid, a.comp, d.TransCorpID as corpid, '运输费用' as remtype, round(a.saleprice * c.realweight,2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from etr_transline a join etr_transmain d on nvl(d.isdel, 0) = 0 and d.id = a.mainid and nvl(d.transcorpid, 0) <> 0 join es_pickmain b on nvl(a.isdel, 0) = 0 and nvl(b.isdel, 0) = 0 and a.allbillid = b.id and a.compid = b.compid join est_outmain c on c.ispick=1 and nvl(c.isdel, 0) = 0 and c.pkbillid = b.id and a.compid = c.compid where a.compid=n_i_CompID and trunc(c.realdate) < to_date(v_i_StartDate, 'yyyy-mm-dd')union all--调出委外运输select a.compid, a.comp, d.TransCorpID as corpid, '运输费用' as remtype, round(a.saleprice * c.realweight,2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from etr_transline a join etr_transmain d on nvl(d.isdel, 0) = 0 and d.id = a.mainid and nvl(d.transcorpid, 0) <> 0 join est_motpickmain b on nvl(a.isdel, 0) = 0 and nvl(b.isdel, 0) = 0 and a.allbillid = b.id and a.compid = b.compid join est_outmain c on c.ispick=1 and nvl(c.isdel, 0) = 0 and c.pkbillid = b.id and a.compid = c.compid where a.compid=n_i_CompID and trunc(c.realdate) < to_date(v_i_StartDate, 'yyyy-mm-dd')union all--期初借方金额SELECT a.compid, a.comp, a.corpid, '运输费用' as remtype, -a.MoneySum as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_inpaymain a WHERE NVl(a.IsDel, 0) = 0 and a.billtype = '113003' and a.compid=n_i_CompID and trunc(a.BizDate) < to_date(v_i_StartDate, 'yyyy-mm-dd'))b group by b.compid,b.comp,b.corpid,b.remtype)c group by c.compid,c.comp,c.corpid,c.remtype ;--end期初运输费用--end运输费用--begin销售insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,'销售' as remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (--本期借方--销售合同select a.compid, a.comp, a.corpid, '销售' as remtype, 0 as beginbal, sum(round(a.saleprice * c.realweight,2)) as thisin, 0 as thisout, 0 as endbal from es_contractline a join es_contract d on nvl(d.isdel,0)=0 and nvl(a.isdel, 0) = 0 and a.mainid=d.id and (d.conproperty<>18005) and (d.conproperty<>18006) and a.compid=d.compid join es_pickmain b on b.picktype <> 97005 and b.picktype <> 97008 and nvl(b.isdel, 0) = 0 and b.EsLinkID = a.id and a.compid=b.compid join est_outmain c on nvl(c.isdel, 0) = 0 and c.ispick = 1 and c.pkbillid = b.id and c.compid=b.compid where a.compid=n_i_CompID and trunc(c.realdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(c.realdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid union all--销售退货SELECT a.compid, a.comp, a.corpid, '销售' as remtype, 0 as beginbal, sum(round(b.realweight*c.saleprice,2)) as thisin, 0 as thisout, 0 as endbal FROM es_contract a join es_contractline c on nvl(a.Isdel,0) = 0 and nvl(c.Isdel, 0) = 0 and a.id = c.mainid and ((a.conproperty = 18005 and a.sendbackinflag = 1)) and a.compid=c.compid join est_outmain b on c.id = b.eslinkid and nvl(b.Isdel, 0) = 0 and a.compid=b.compid where a.compid=n_i_CompID and trunc(b.realdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(b.realdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--销售补差SELECT a.compid, a.comp, a.corpid, '销售' as remtype, 0 as beginbal, sum(round(c.realweight*c.saleprice,2)) as thisin, 0 as thisout, 0 as endbal FROM es_contract a join es_contractline c on nvl(a.Isdel,0) = 0 and nvl(c.Isdel, 0) = 0 and a.id = c.mainid and a.conproperty = 18006 and a.compid=c.compid where a.compid=n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--本期贷方--销售收款SELECT B.compid, B.comp, B.Corpid, '销售' as remtype, 0 as beginbal, 0 as thisin, round(sum(B.MONEYSUM),2) as thisout, 0 as endbal FROM efi_outmoneymain B WHERE nvl(B.ISDEL, 0) = 0 and b.billtype = '98001' and b.compid=n_i_CompID and trunc(b.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(b.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by b.compid, b.comp, b.corpid--end本期 union all--期初 --销售合同select a.compid, a.comp, a.corpid, '销售' as remtype, -sum(round(a.saleprice * c.realweight,2)) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from es_contractline a join es_contract d on nvl(d.isdel,0)=0 and nvl(a.isdel, 0) = 0 and a.mainid=d.id and (d.conproperty<>18005) and (d.conproperty<>18006) and a.compid=d.compid join es_pickmain b on b.picktype <> 97005 and b.picktype <> 97008 and nvl(b.isdel, 0) = 0 and b.EsLinkID = a.id and b.compid=a.compid join est_outmain c on nvl(c.isdel, 0) = 0 and c.ispick = 1 and c.pkbillid = b.id and c.compid=b.compid where a.compid=n_i_CompID and trunc(c.realdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid union all--销售退货SELECT a.compid, a.comp, a.corpid, '销售' as remtype, -sum(round(b.realweight*c.saleprice,2)) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM es_contract a join es_contractline c on nvl(a.Isdel,0) = 0 and nvl(c.Isdel, 0) = 0 and a.id = c.mainid and ((a.conproperty = 18005 and a.sendbackinflag = 1)) and a.compid=c.compid join est_outmain b on c.id = b.eslinkid and nvl(b.Isdel, 0) = 0 and a.compid=b.compid where a.compid=n_i_CompID and trunc(b.realdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--销售补差SELECT a.compid, a.comp, a.corpid, '销售' as remtype, -sum(round(c.realweight*c.saleprice,2)) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM es_contract a join es_contractline c on nvl(a.Isdel,0) = 0 and nvl(c.Isdel, 0) = 0 and a.id = c.mainid and a.conproperty = 18006 and a.compid=c.compid where a.compid=n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--期初贷方--销售收款SELECT B.compid, B.comp, B.Corpid, '销售' as remtype, round(sum(B.MONEYSUM),2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_outmoneymain B WHERE nvl(B.ISDEL, 0) = 0 and b.billtype = '98001' and b.compid=n_i_CompID and trunc(b.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by b.compid, b.comp, b.corpid /*--销售收款初始化SELECT B.compid, B.comp, B.corpid as corpid, B.corp as corp, '销售' as remtype, round(sum(b.clemoneysum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_outmoneylineinit B WHERE b.billtype=98001 and b.compid=n_i_CompID group by b.compid, b.comp, b.corpid, b.corp*/ union all--销售单位余额表SELECT B.compid, B.comp, B.corpid as corpid, '销售' as remtype, B.DelegateMoney as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_corpbalinit B WHERE B.type = 2 and b.compid=n_i_CompID --end期初 )c group by c.compid, c.comp, c.corpid;--end销售 --begin采购（不包括采购合同的费用）insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,'采购' as remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (--本期贷方--采购合同SELECT B.compid, B.comp, B.Corpid, '采购' as remtype, 0 as beginbal, 0 as thisin, sum(round(a.weight * a.UnitPrice, 2)) as thisout, 0 as endbal from est_inline a join est_inmain b on a.mainid = b.id and a.compid = b.compid and nvl(a.isdel, 0) = 0 and b.intype in (13001, 13002, 13005) and nvl(b.isdel, 0) = 0 where b.compid = n_i_CompID and trunc(b.indate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(b.indate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by b.compid, b.comp, b.corpidunion all--采购退货37007SELECT B.compid, B.comp, d.Corpid, '采购' as remtype, 0 as beginbal, 0 as thisin, sum(round(a.weight * a.UnitPrice, 2)) as thisout, 0 as endbal from est_inline a join est_inmain b on a.mainid = b.id and a.compid = b.compid and nvl(a.isdel, 0) = 0 and b.intype = 37007 and nvl(b.isdel, 0) = 0 join edt_contractline c on c.id = a.linkid and c.compid = a.compid and nvl(c.isdel, 0) = 0 join edt_contract d on c.mainid = d.id and c.compid = d.compid and nvl(d.isdel, 0) = 0 where b.compid = n_i_CompID and trunc(b.indate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(b.indate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by b.compid, b.comp, d.corpidunion all--成本差异(不包括费用成本差异，采购单位往来包括费用成本差异)select a.compid, a.comp, a.Corpid, '采购' as remtype, 0 as beginbal, 0 as thisin, round(sum(a.MoneySum), 2) as thisout, 0 as endbal from efi_profitline a where nvl(a.isdel, 0) = 0 and a.BillType = 85001 and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--本期借方--采购付款select a.compid, a.comp, a.Corpid, '采购' as remtype, 0 as beginbal, round(sum(a.MoneySum), 2) as thisin, 0 as thisout, 0 as endbal from efi_inpaymain a where nvl(a.isdel, 0) = 0 and a.billtype = '113001' and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--期初贷方--采购合同SELECT B.compid, B.comp, B.Corpid, '采购' as remtype, sum(round(a.weight * a.UnitPrice, 2)) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from est_inline a join est_inmain b on a.mainid = b.id and a.compid = b.compid and nvl(a.isdel, 0) = 0 and b.intype in (13001, 13002, 13005) and nvl(b.isdel, 0) = 0 where b.compid = n_i_CompID and trunc(b.indate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by b.compid, b.comp, b.corpidunion all--采购退货37007SELECT B.compid, B.comp, d.Corpid, '采购' as remtype, sum(round(a.weight * a.UnitPrice, 2)) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from est_inline a join est_inmain b on a.mainid = b.id and a.compid = b.compid and nvl(a.isdel, 0) = 0 and b.intype = 37007 and nvl(b.isdel, 0) = 0 join edt_contractline c on c.id = a.linkid and c.compid = a.compid and nvl(c.isdel, 0) = 0 join edt_contract d on c.mainid = d.id and c.compid = d.compid and nvl(d.isdel, 0) = 0 where b.compid = n_i_CompID and trunc(b.indate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by b.compid, b.comp, d.corpidunion all--成本差异select a.compid, a.comp, a.Corpid, '采购' as remtype, round(sum(a.MoneySum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from efi_profitline a where nvl(a.isdel, 0) = 0 and a.BillType = 85001 and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--期初借方--采购付款select a.compid, a.comp, a.Corpid, '采购' as remtype, -round(sum(a.MoneySum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from efi_inpaymain a where nvl(a.isdel, 0) = 0 and a.billtype = '113001' and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid /* --采购付款初始化 select a.compid, a.comp, a.Corpid, a.Corp, '采购' as remtype, -round(sum(a.PayMoneySum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from efi_inpaylineinit a where nvl(a.isdel, 0) = 0 and a.billtype = '113001' group by a.compid, a.comp, a.corpid, a.corp*/union all--采购单位余额表SELECT B.COMPID, B.COMP, B.CORPID as corpid, '采购' as remtype, B.DebitMoney as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_corpbalinit B WHERE B.type = 1 and b.compid= n_i_CompID ) c group by c.compid, c.comp, c.corpid;--end采购--begin加工insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,'加工' as remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (--本期--加工都放贷方，贸易加工为负，加工模块为正select a.compid, a.comp, a.Corpid, '加工' as remtype, 0 as beginbal, 0 as thisin, sum(thisout) as thisout, 0 as endbal from ( --销售、库存委外加工 select a.compid, a.comp, decode(a.acccashtype, '7101', a.balancecorpid, a.ownerid) as corpid, '加工' as remtype, 0 as beginbal, 0 as thisin, -decode(a.balanceflag, 1, a.realbalancesum, round(a.SalePrice * a.ProcWeight, 2)) as thisout, 0 as endbal from epr_procmain a where a.isdel = 0 and a.acccashtype in (7101, 7102) and a.billtype in (96001, 96003) and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') union all --加工模块的加工 select a.compid, a.comp, decode(a.acccashtype, '7101', a.balancecorpid, a.ownerid) as corpid, '加工' as remtype, 0 as beginbal, 0 as thisin, decode(a.balanceflag, 1, a.realbalancesum, round(a.SalePrice * a.ProcWeight, 2)) as thisout, 0 as endbal from epr_procmain a where a.isdel = 0 and a.billtype in (96002, 96004, 96005, 96006) and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd')) a group by a.compid, a.comp, a.Corpidunion all--加工收、付款都放借方，付款为负，收款为正--加工收款SELECT a.compid, a.comp, a.corpid, '加工' as remtype, 0 as beginbal, round(sum(a.moneysum), 2) as thisin, 0 as thisout, 0 as endbal FROM efi_outmoneymain a WHERE nvl(a.isdel, 0) = 0 and a.billtype = 98002 and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--加工付款SELECT a.compid, a.comp, a.corpid, '加工' as remtype, 0 as beginbal, -round(sum(a.moneysum), 2) as thisin, 0 as thisout, 0 as endbal FROM efi_inpaymain a WHERE nvl(a.isdel, 0) = 0 and a.billtype = 113002 and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid union all --期初--加工都放贷方，贸易加工为负，加工模块为正select a.compid, a.comp, a.Corpid, '加工' as remtype, 0 as beginbal, 0 as thisin, sum(thisout) as thisout, 0 as endbal from ( --销售、库存委外加工 select a.compid, a.comp, decode(a.acccashtype, '7101', a.balancecorpid, a.ownerid) as corpid, '加工' as remtype, 0 as beginbal, 0 as thisin, -decode(a.balanceflag, 1, a.realbalancesum, round(a.SalePrice * a.ProcWeight, 2)) as thisout, 0 as endbal from epr_procmain a where a.isdel = 0 and a.acccashtype in (7101, 7102) and a.billtype in (96001, 96003) and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') union all --加工模块的加工 select a.compid, a.comp, decode(a.acccashtype, '7101', a.balancecorpid, a.ownerid) as corpid, '加工' as remtype, 0 as beginbal, 0 as thisin, decode(a.balanceflag, 1, a.realbalancesum, round(a.SalePrice * a.ProcWeight, 2)) as thisout, 0 as endbal from epr_procmain a where a.isdel = 0 and a.billtype in (96002, 96004, 96005, 96006) and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd')) a group by a.compid, a.comp, a.Corpidunion all--加工收、付款都放借方，付款为负，收款为正--加工收款SELECT a.compid, a.comp, a.corpid, '加工' as remtype, 0 as beginbal, round(sum(a.moneysum), 2) as thisin, 0 as thisout, 0 as endbal FROM efi_outmoneymain a WHERE nvl(a.isdel, 0) = 0 and a.billtype = 98002 and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--加工付款SELECT a.compid, a.comp, a.corpid, '加工' as remtype, 0 as beginbal, -round(sum(a.moneysum), 2) as thisin, 0 as thisout, 0 as endbal FROM efi_inpaymain a WHERE nvl(a.isdel, 0) = 0 and a.billtype = 113002 and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid/*--加工付款初始化select a.compid, a.comp, a.Corpid, a.Corp, '加工' as remtype, -round(sum(a.PayMoneySum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from efi_inpaylineinit a where nvl(a.isdel, 0) = 0 and a.billtype = '113002' and a.compid = 2 group by a.compid, a.comp, a.corpid, a.corpunion all--加工收款初始化SELECT B.compid, B.comp, B.corpid as corpid, B.corp as corp, '加工' as remtype, round(sum(b.clemoneysum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_outmoneylineinit B WHERE b.billtype = 98002 and b.compid = 2 group by b.compid, b.comp, b.corpid, b.corp*/union all--加工单位余额表SELECT B.compid, B.comp, B.corpid as corpid, '加工' as remtype, B.debitmoney as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_corpbalinit B WHERE B.type = 3 and b.compid = n_i_CompID )c group by c.compid, c.comp, c.corpid;--end加工 --begin辅料insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,'辅料' as remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (--本期贷方--辅料采购select a.compid, a.comp, a.corpid, '辅料' as remtype, 0 as beginbal, 0 as thisin, sum(b.money) as thisout, 0 as endbal From epr_assistinmain a join (Select mainid, round(sum(nvl(unitmoney, 0)), 2) as money from epr_assistinline where isdel = 0 group by mainid) b on a.id = b.mainid where (a.intype = '88001') and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--88002退货select a.compid, a.comp, a.corpid, '辅料' as remtype, 0 as beginbal, 0 as thisin, -sum(b.money) as thisout, 0 as endbal From epr_assistinmain a join (Select mainid, round(sum(nvl(unitmoney, 0)), 2) as money from epr_assistinline where isdel = 0 group by mainid) b on a.id = b.mainid where (a.intype = '88002') and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid union all --本期借方--辅料付款select a.compid, a.comp, a.corpid, '辅料' as remtype, 0 as beginbal, round(sum(a.MoneySum), 2) as thisin, 0 as thisout, 0 as endbal From efi_inpaymain a where a.billtype = '113005' and nvl(a.isdel, 0) = 0 and a.compid = n_i_CompID and trunc(a.bizdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.bizdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid union all--期初--辅料采购select a.compid, a.comp, a.corpid, '辅料' as remtype, sum(b.money) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal From epr_assistinmain a join (Select mainid, round(sum(nvl(unitmoney, 0)), 2) as money from epr_assistinline where isdel = 0 group by mainid) b on a.id = b.mainid where (a.intype = '88001') and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--88002退货select a.compid, a.comp, a.corpid, '辅料' as remtype, -sum(b.money) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal From epr_assistinmain a join (Select mainid, round(sum(nvl(unitmoney, 0)), 2) as money from epr_assistinline where isdel = 0 group by mainid) b on a.id = b.mainid where (a.intype = '88002') and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all--辅料付款select a.compid, a.comp, a.corpid, '辅料' as remtype, -round(sum(a.MoneySum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal From efi_inpaymain a where a.billtype = '113005' and nvl(a.isdel, 0) = 0 and a.compid = n_i_CompID and trunc(a.bizdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpidunion all --初始化数据 应付select a.compid, a.comp, a.corpid, '辅料' as remtype, Round(a.debitmoney, 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from efi_corpbalinit a where a.compid = n_i_CompID and a.type = 6 union all--期初暂估select compid, comp, corpid, '辅料' as remtype, -sum(Round(MoneySum, 2)) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal from efi_invoicelineinit where billtype = '86005' and feeitem = '31' and compid = n_i_CompID group by compid, comp, corpid )c group by c.compid, c.comp, c.corpid;--end辅料--begin付款费用insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,'付款费用' as remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (--本期贷方SELECT A.CompID, A.Comp, A.FeeCorpID as corpid, '付款费用' as remtype, 0 as beginbal, 0 as thisin, round(sum(a.balsum), 2) as thisout, 0 as endbal FROM eg_Fee A WHERE nvl(a.IsDel, 0) = 0 and a.checkflag = 1 and a.compid = n_i_CompID and trunc(a.checkdate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.checkdate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by A.CompID, A.Comp, A.FeeCorpID union all --本期借方 SELECT a.compid, a.comp, a.corpid, '付款费用' as remtype, 0 as beginbal, round(sum(a.MoneySum), 2) as thisin, 0 as thisout, 0 as endbal FROM efi_inpaymain a WHERE NVl(a.IsDel, 0) = 0 and a.billtype = '113004' and a.compid = n_i_CompID and trunc(a.BizDate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.BizDate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid --end本期 union all--期初贷方SELECT A.CompID, A.Comp, A.FeeCorpID as corpid, '付款费用' as remtype, round(sum(a.balsum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM eg_Fee A WHERE nvl(a.IsDel, 0) = 0 and a.checkflag = 1 and a.compid = n_i_CompID and trunc(a.checkdate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by A.CompID, A.Comp, A.FeeCorpIDunion all--期初借方SELECT a.compid, a.comp, a.corpid, '付款费用' as remtype, -round(sum(a.MoneySum), 2) as beginbal, 0 as thisin, 0 as thisout, 0 as endbal FROM efi_inpaymain a WHERE NVl(a.IsDel, 0) = 0 and a.billtype = '113004' and a.compid = n_i_CompID and trunc(a.BizDate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid, a.corp )c group by c.compid, c.comp, c.corpid;--end付款费用--begin收款费用insert into TMP_CORPREMSUM(COMPID,COMP,CORPID,REMTYPE,BEGINBAL,THISIN,THISOUT,ENDBAL)select c.compid,c.comp,c.corpid,'收款费用' as remtype, sum(c.beginbal) as beginbal,sum(c.thisin) as thisin,sum(c.thisout) as thisout,sum(c.beginbal + c.thisout - c.thisin) as endbalfrom (--本期借方SELECT A.CompID, A.Comp, A.FeeCorpID as corpid, '收款费用' as remtype, 0 as beginbal, round(sum(a.balsum), 2) as thisin, 0 as thisout, 0 as endbal FROM eg_infee A WHERE nvl(a.IsDel, 0) = 0 and a.checkflag = 1 and a.compid = n_i_CompID and trunc(a.BizDate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.BizDate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by A.CompID, A.Comp, A.FeeCorpIDunion all--本期贷方SELECT a.compid, a.comp, a.corpid, '收款费用' as remtype, 0 as beginbal, 0 as thisin, round(sum(a.MoneySum), 2) as thisout, 0 as endbal FROM efi_outmoneymain a WHERE NVl(a.IsDel, 0) = 0 and a.billtype = '98003' and a.compid = n_i_CompID and trunc(a.BizDate) >= to_date(v_i_StartDate, 'yyyy-mm-dd') and trunc(a.BizDate) <= to_date(v_i_EndDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid union all --期初借方SELECT A.CompID, A.Comp, A.FeeCorpID as corpid, '收款费用' as remtype, 0 as beginbal, round(sum(a.balsum), 2) as thisin, 0 as thisout, 0 as endbal FROM eg_infee A WHERE nvl(a.IsDel, 0) = 0 and a.checkflag = 1 and a.compid = n_i_CompID and trunc(a.BizDate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by A.CompID, A.Comp, A.FeeCorpIDunion all--期初贷方SELECT a.compid, a.comp, a.corpid, '收款费用' as remtype, 0 as beginbal, 0 as thisin, round(sum(a.MoneySum), 2) as thisout, 0 as endbal FROM efi_outmoneymain a WHERE NVl(a.IsDel, 0) = 0 and a.billtype = '98003' and a.compid = n_i_CompID and trunc(a.BizDate) < to_date(v_i_StartDate, 'yyyy-mm-dd') group by a.compid, a.comp, a.corpid, a.corp )c group by c.compid, c.comp, c.corpid;--end收款费用 v_t_sqlstr := 'select l.COMPID, l.COMP, l.CORPID, max(d.corpname) as CORP, l.REMTYPE, sum(l.BEGINBAL) as BEGINBAL, sum(l.THISIN) as THISIN, sum(l.THISOUT) as THISOUT, sum(l.ENDBAL) as ENDBAL from TMP_CORPREMSUM l join eb_corp d on l.corpid = d.id where 1=1 '; v_t_sqlstr := v_t_sqlstr || v_i_swhere; v_t_sqlstr := v_t_sqlstr || 'group by l.COMPID,l.COMP,l.CORPID,l.REMTYPE '; OPEN c_o_rs FOR v_t_sqlstr; --CLOSE c_o_rs; i_o_state := 0; v_o_msg := '查询成功'; EXCEPTION WHEN OTHERS THEN i_o_state := -1; v_o_msg := '往来单位余额查询失败！！！' || SQLERRM; GOTO proc_end; <<proc_end>> NULL;end CORP_REMSUM;end PCK_SUMREPORT;

包中定义如下:create or replace package PCK_SUMREPORT isTYPE myrctype IS REF CURSOR; -- Author : GAOSONG -- Created : 2008-5-23 13:33:29 -- Purpose : 包涵几个复杂汇总报表的存储过程--往来单位余额汇总表procedure CORP_REMSUM( n_i_CompID IN varchar2, v_i_StartDate IN varchar2, v_i_EndDate IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype );--采购往来单位汇总表procedure PUR_CORPSUM( n_i_CorpID IN varchar2, n_i_CompID IN varchar2, n_i_BillMangerSort IN varchar2, v_i_StartDate IN varchar2, v_i_EndDate IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype);--销售往来单位汇总表procedure SAL_CORPSUM( n_i_CorpID IN varchar2, n_i_CompID IN varchar2, n_i_BillMangerSort IN varchar2, v_i_StartDate IN varchar2, v_i_EndDate IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype);--销售单位汇总表按提单procedure SAL_CORPSUMBYPICK( n_i_CorpID IN varchar2, n_i_CompID IN varchar2, n_i_BillMangerSort IN varchar2, v_i_StartDate IN varchar2, v_i_EndDate IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype);--现金银行帐号余额汇总表procedure CASHBANK_REMAIN( n_i_Accountno IN varchar2, v_i_StartBizDate IN varchar2, v_i_EndBizDate IN varchar2, n_i_CompID IN varchar2, n_i_IsCash IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype);--现金银行帐号明细表procedure CASHBANK_DETAIL( n_i_Accountno IN varchar2, v_i_StartBizDate IN varchar2, v_i_EndBizDate IN varchar2, n_i_CompID IN varchar2, n_i_IsCash IN varchar2, v_i_swhere IN varchar2, i_o_state OUT int, v_o_msg OUT varchar2, c_o_rs OUT myrctype);end PCK_SUMREPORT;