金蝶k3wise数据开发，VMI仓库当前领用情况

查询当前企业VMI仓库期初数据，入库多少、领用多少和结存多少

 

declare @FTime date set @FTime='2019-10-01'
declare @TTime date set @TTime='2019-10-10'
declare @FSuppNumber varchar(30)

set @FSuppNumber='C塑胶.04.1134'


select FDate,b.FItemID,FStockID,b.FQty into #inQty 
from ICVMIInStock a inner join ICVMIInStockEntry b on a.FID=b.FID
inner join t_Supplier c on a.FSupplyID=c.FItemID 
where c.FNumber=@FSuppNumber



select FItemID,FStockID,sum(FQty) as FQty into #endQty 
from ICInventory  where FStockID in (select distinct FStockID from #inQty ) group by FItemID,FStockID

select z.* into #OutQty from (
select a.FDate,b.FItemID,case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end as FStockID,b.FQty 
from ICStockBill a inner join ICStockBillEntry b on a.FInterID=b.FInterID where a.FCancellation=0 
and  a.FTranType in (21,24,28,29,43) and b.FQty<>0 and  case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end = (select FVMIStockID from t_Supplier where FNumber=@FSuppNumber)
union all
select a.FDate,b.FItemID,case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end as FStockID,FQty 
from ICStockBill a inner join ICStockBillEntry b on a.FInterID=b.FInterID where a.FCancellation=0 
and  a.FTranType in (41) and b.FQty<>0 and  case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end = (select FVMIStockID from t_Supplier where FNumber=@FSuppNumber)
) z where FStockID in (select distinct FStockID from #inQty )




select a.FItemID,b.FNumber,b.FName,b.FModel,c.FName as FUnitName,a.FBeginQty,a.FInQty,a.FOutQty,(a.FBeginQty+a.FInQty-a.FOutQty) as FEndQty into #ztab from (
select isnull(x.FItemID,y.FItemID) as FItemID,isnull(x.FBeginQty,0) as FBeginQty,isnull(y.FInQty,0) as FInQty,isnull(y.FOutQty,0) as FOutQty from (
select isnull(a.FItemID,b.FItemID) as FItemID,isnull(a.FBeginQty,0)-isnull(b.FOutQty,0) as FBeginQty from (
select FItemID, sum(FQty) as FBeginQty from #inQty where FDate<@FTime group by FItemID ) a full join 
(select FItemID, sum(FQty) as FOutQty from #OutQty where FDate<@FTime group by FItemID) b on a.FItemID=b.FItemID
) x full join (
select isnull(a.FItemID,b.FItemID) as FItemID,isnull(a.FBeginQty,0) as FInQty,isnull(b.FOutQty,0) as FOutQty from (
select FItemID, sum(FQty) as FBeginQty from #inQty where FDate>=@FTime and FDate<=@TTime group by FItemID ) a full join 
(select FItemID, sum(FQty) as FOutQty from #OutQty where FDate>=@FTime and FDate<=@TTime group by FItemID) b on a.FItemID=b.FItemID) y on x.FItemID=y.FItemID
) a inner join t_ICItem b on a.FItemID=b.FItemID
inner join t_MeasureUnit c on b.FUnitID=c.FItemID


select cast(FItemID AS varchar(20)) as FItemID,FNumber,FName,FModel,FUnitName,FBeginQty as FBegQty,FInQty,FOutQty,FEndQty from #ztab
union all 
select '','合计','','','',sum(FBeginQty),sum(FInQty),sum(FOutQty),sum(FEndQty) from #ztab 

drop table #endQty
drop table #OutQty
drop table #inQty
drop table #ztab