一般财务的成本计算会比库存盘点滞后,所以有时为了查库存收发需要往往要再不结账的情况下查询报表,此表为不用结账就能查看的物料收发汇总表
CREATE proc stkd_物料收发汇总表
@begdate datetime, @enddate datetime, @begstock varchar(50), @endstock varchar(50), @begitem varchar(100), @enditem varchar(100) as set nocount on select @endstock='zzzzzz' where @endstock='' select @enditem='zzzzzz' where @enditem='' declare @year int,@period int,@bd varchar(12) select @year=fvalue from t_systemprofile where fcategory='ic' and fkey='currentyear' select @period=fvalue from t_systemprofile where fcategory='ic' and fkey='currentperiod' if((@year*12+@period)<(year(@begdate)*12+month(@begdate))) begin select @year=year(@begdate),@period=month(@begdate) end select @bd=cast(@year as varchar(4))+'-'+cast(@period as varchar(2))+'-01' create table #aa( fstock int, fstockna varchar(80), fitemid int, fnumber varchar(100), fname varchar(100), fmodel varchar(100), fbatchno varchar(50), funit varchar(50), fbeg decimal(18,4) default(0), fin decimal(18,4) default(0), fout decimal(18,4) default(0), fend decimal(18,4) default(0) ) create table #bb( fstock int, fstockna varchar(80), fitemid int, fnumber varchar(100), fname varchar(100), fmodel varchar(100), fbatchno varchar(50), funit varchar(50), fbeg decimal(18,4) default(0), fin decimal(18,4) default(0), fout decimal(18,4) default(0), fend decimal(18,4) default(0) ) insert into #aa(fstock,fitemid,fbatchno,fbeg) select fstockid,fitemid,fbatchno,sum(fbegqty) as fbeg from icinvbal where fyear=@year and fperiod=@period and fstockid in (select fitemid from t_stock where fnumber>=@begstock and fnumber<=@endstock) and fitemid in (select fitemid from t_icitem where fnumber>=@begitem and fnumber<=@enditem) group by fstockid,fitemid,fbatchno --select * from icinvbal insert into #aa(fstock,fitemid,fbatchno,fbeg) select ice.fdcstockid, ice.fitemid,ice.fbatchno, sum(case when ic.ftrantype in (1,2,3,5,10,40,41) and ice.fdcstockid=ts.fitemid and ts.fnumber>=@begstock and ts.fnumber<=@endstock then ice.fqty else 0 end)+ sum(case when ic.ftrantype in (21,28,29,43) and ice.fdcstockid=ts.fitemid and ts.fnumber>=@begstock and ts.fnumber<=@endstock then -ice.fqty else 0 end) as fout from icstockbill ic,icstockbillentry ice,t_stock ts,t_icitem ti where ic.finterid=ice.finterid and ic.fdate>=@bd and ic.fdate<@begdate and ice.fitemid=ti.fitemid and ti.fnumber>=@begitem and ti.fnumber<=@enditem group by ice.fdcstockid,ice.fitemid,ice.fbatchno insert into #aa(fstock,fitemid,fbatchno,fbeg) select ice.fscstockid, ice.fitemid,ice.fbatchno, sum(case when ic.ftrantype in (41,24) and ice.fscstockid=ts.fitemid and ts.fnumber>=@begstock and ts.fnumber<=@endstock then -ice.fqty else 0 end) as fout from icstockbill ic,icstockbillentry ice,t_stock ts,t_icitem ti where ic.finterid=ice.finterid and ic.fdate>=@bd and ic.fdate<@begdate and ice.fitemid=ti.fitemid and ti.fnumber>=@begitem and ti.fnumber<=@enditem group by ice.fscstockid,ice.fitemid,ice.fbatchno --select * from ictranstype insert into #aa(fstock,fitemid,fbatchno,fin,fout) select ice.fdcstockid, ice.fitemid,ice.fbatchno, sum(case when ic.ftrantype in (1,2,3,5,10,40,41) and ice.fdcstockid=ts.fitemid and ts.fnumber>=@begstock and ts.fnumber<=@endstock then ice.fqty else 0 end) as fin, sum(case when ic.ftrantype in (21,28,29,43) and ice.fdcstockid=ts.fitemid and ts.fnumber>=@begstock and ts.fnumber<=@endstock then ice.fqty else 0 end) as fout from icstockbill ic,icstockbillentry ice,t_stock ts,t_icitem ti where ic.finterid=ice.finterid and ic.fdate>=@begdate and ic.fdate<=@enddate and ice.fitemid=ti.fitemid and ti.fnumber>=@begitem and ti.fnumber<=@enditem group by ice.fdcstockid,ice.fitemid,ice.fbatchno insert into #aa(fstock,fitemid,fbatchno,fin,fout) select ice.fscstockid, ice.fitemid,ice.fbatchno,0, sum(case when ic.ftrantype in (41,24) and ice.fscstockid=ts.fitemid and ts.fnumber>=@begstock and ts.fnumber<=@endstock then ice.fqty else 0 end) as fout from icstockbill ic,icstockbillentry ice,t_stock ts,t_icitem ti where ic.finterid=ice.finterid and ic.fdate>=@begdate and ic.fdate<=@enddate and ice.fitemid=ti.fitemid and ti.fnumber>=@begitem and ti.fnumber<=@enditem group by ice.fscstockid,ice.fitemid,ice.fbatchno delete from #aa where fbeg=0 and fin=0 and fout=0 and fend=0 insert into #bb(fstock,fitemid,fbatchno,fbeg,fin,fout) select fstock,fitemid,fbatchno,sum(fbeg),sum(fin),sum(fout) from #aa group by fstock,fitemid,fbatchno update #bb set fend=fbeg+fin-fout -- update t1 set t1.funit=t3.fname from #bb t1,t_icitem t2,t_measureunit t3 where t1.fitemid=t2.fitemid and t2.funitid=t3.fmeasureunitid update t1 set t1.fstockna=t2.fname from #bb t1,t_stock t2 where t1.fstock=t2.fitemid update t1 set t1.fnumber=t2.fnumber,t1.fname=t2.fname,t1.fmodel=t2.fmodel from #bb t1,t_icitem t2 where t1.fitemid=t2.fitemid insert into #bb(fstockna,fbeg,fin,fout,fend) select fstockna+'合计',sum(fbeg),sum(fin),sum(fout),sum(fend) from #bb group by fstockna insert into #bb(fstockna,fbeg,fin,fout,fend) select '总计',sum(fbeg),sum(fin),sum(fout),sum(fend) from #bb where fstockna like '%合计' select fstockna as 仓库,fnumber as 物料编码,fname as 物料名称,fmodel as 规格型号,fbatchno as 批号,funit as 单位, fbeg as 期初余额,fin as 本期收入,fout as 本期发出,fend as 期末余额 from #bb order by fstockna,fnumber GO |
|