金蝶K3不用結(jié)賬就可以查詢以后期間的物料收發(fā)匯總表

庋藏天下 2013-06-22

展開全文

一般財務的成本計算會比庫存盤點滯后，所以有時為了查庫存收發(fā)需要往往要再不結(jié)賬的情況下查詢報表，此表為不用結(jié)賬就能查看的物料收發(fā)匯總表

CREATE proc stkd_物料收發(fā)匯總表

@begdate datetime,

@enddate datetime,

@begstock varchar(50),

@endstock varchar(50),

@begitem varchar(100),

@enditem varchar(100)

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