K3 不用结账就可以查询以后期间的物料收发汇总表

chen_ml

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