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; begin delete 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 endbal from ( select a.compid,a.comp,a.corpid,a.remtype, 0 as beginbal,sum(a.thisin) as thisin,sum(a.thisout) as thisout,0 as endbal from( 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 endbal from( 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 endbal from ( --本期借方 --销售合同 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.corpid union 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.corpid union 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.corpid union 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.corpid union 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 endbal from ( --本期贷方 --采购合同 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.corpid union all --采购退货37007 SELECT 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.corpid union 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.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 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.corpid union 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.corpid union all --采购退货37007 SELECT 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.corpid union 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.corpid union 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 endbal from ( --本期 --加工都放贷方,贸易加工为负,加工模块为正 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.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_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.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 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.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_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.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 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.corp union 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 endbal from ( --本期贷方 --辅料采购 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.corpid union 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.corpid union 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.corpid union 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.corpid union 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 endbal from ( --本期贷方 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.FeeCorpID union 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 endbal from ( --本期借方 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.FeeCorpID union 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.FeeCorpID union 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 is TYPE 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;