金蝶K3库存分类汇总表

⾦蝶K3库存分类汇总表
按照仓库单据的类型分类统计出⽇期段内各种物料各种类型的单据出⼊数量
create Proc stkd_库存分类汇总表
@BegDate DateTime,@EndDate DateTime,
@BegItem Varchar(100),@EndItem Varchar(100),
@stock varchar(20)
As
--Select @BegDate='2004-01-01',@EndDate='2004-10-29',@BegItem='',@EndItem='',@BegCust='',@EndCust='' select@enditem='zzzz'where@enditem=''
Set NoCount On
declare@st int
select@st=fitemid from t_stock where fname=@stock
create table #aa(
fitemid int,
fnumber varchar(80),
fname varchar(80),
fmodel varchar(50),
fbeg decimal(18,2) default(0),
fpur decimal(18,2) default(0),
fbz decimal(18,2) default(0),
fling decimal(18,2) default(0),
fdin decimal(18,2) default(0),
fdout decimal(18,2) default(0),
fpin decimal(18,2) default(0),
fpout decimal(18,2) default(0),
fsale decimal(18,2) default(0),
fqin decimal(18,2) default(0),
fqout decimal(18,2) default(0),
fend decimal(18,2) default(0)
)
declare@beg varchar(10)
select@beg=cast(year(@begdate) as varchar(4))+'-'+cast(month(@begdate) as varchar(2))
+'-01'
insert into #aa(fitemid,fnumber,fname,fmodel)
select fitemid,fnumber,fname,fmodel
from t_icitem
where fnumber>=@begitem and fnumber<=@enditem
update t1
set t1.fbeg=t2.fbegbal
from #aa t1,
(select fitemid,sum(fbegqty) as fbegbal from icinvbal
where fstockid in (select fitemid from t_stock where fname like'%'+@stock+'%')
and fyear=year(@begdate) and fperiod=month(@begdate)
group by fitemid)
t2
where t1.fitemid=t2.fitemid
update t1
set t1.fbeg=t1.fbeg+t2.fbal
from #aa t1,(
select ice.fitemid,
sum(case when ic.ftrantype in (1,2,3,5,10,40,41) and ice.fdcstockid=@st then ice.fqty
when ic.ftrantype in (21,24,29,28,43,41) and ice.fscstockid=@st then-ice.fqty else0end) as fbal
from icstockbill ic,icstockbillentry ice
where ic.finterid=ice.finterid and ic.fdate>=@beg and ic.fdate<@begdate
group by ice.fitemid
) t2
where t1.fitemid=t2.fitemid
update t1
set t1.fpur=t2.fpur,t1.fbz=t2.fbz,t1.fqin=t2.fqin,
t1.fqout=t2.fqout,t1.fsale=t2.fsale,t1.fpin=t2.fpin,t1.fpout=t2.fpout,t1.fling=t2.fling,
t1.fdin=t2.fdin,t1.fdout=t2.fdout
from #aa t1,(
select fitemid,
sum(case when ic.ftrantype=1and ice.fdcstockid=@st then ice.fqty else0end) as fpur,
sum(case when ic.ftrantype=2and ice.fdcstockid=@st then ice.fqty else0end) as fbz, sum(case when ic.ftrantype=10and ice.fdcstockid=@st then ice.fqty else0end) as fqin, sum(case when ic.ftrantype=21and ice.fdcstockid=@st then ice.fqty else0end) as fsale, sum(case when ic.ftrantype=24and ice.fscstockid=@st then ice.fqty else0end) as fling, sum(case when ic.ftrantype=29and ice.fdcstockid=@st then ice.fqty else0end) as fqout, sum(case when ic.ftrantype=40and ice.fdcstockid=@st then ice.fqty else0end) as fpin, sum(case when ic.ftrantype=43and ice.fdcstockid=@st then ice.fqty else0end) as fpout, sum(case when ic.ftrantype=41and ice.fdcstockid=@st then ice.fqty else0end) as fdin, sum(case when ic.ftrantype=41and ice.fscstockid=@st then ice.fqty else0end) as fdout from icstockbill ic,icstockbillentry ice
where ic.finterid=ice.finterid and ic.fdate>=@begdate and ic.fdate<=@enddate
group by ice.fitemid
)
t2
where t1.fitemid=t2.fitemid
update #aa set fend=fbeg+fpur+fbz+fqin+fdin+fpin-fdout-fqout-fling-fpout-fsale
delete from #aa where fbeg=0and fend=0and fqout=0
and fqin=0and fpur=0and fsale=0
select fnumber as物料编码,fname as物料名称,fmodel as规格,fbeg as期初,fpur as外购, fbz as产品⼊库,fdin as调拨⼊库,
fdout as调拨出库,fpin as盘盈⼊库,fpout as盘盈出库,fling as领料,fsale as销售出库, fqin as其他⼊库,fqout as其他出库,fend as期末
from #aa order by fnumber
GO。