在Sbo中通过单据事务日志进行物料的期初、期末、出入库综合分析

一个非常有用的Sbo业务查询分析，利用Sbo单据事务日志表OINM，对指定期间的物料的期初、期末、入库、出库以及特殊类型的出库入库--在此我们分析了采购入库和采购退货两种特殊的业务类型--进行了统计分析。

 

当然可以进行扩展，进而加上一些分析条件，比如对指定的仓库、指定的物料类型、排除指定的物料等条件进行约束。以下语句在某合作单位的生产环境上测试通过，并用于其生产分析。

 

_declare @dt1 smalldatetime, @dt2 smalldatetime, @ItemCode nvarchar(20)

_select @dt1=t0.docDate FROM OINM t0 WHERE t0.createDate=[%0]
_select @dt2=t1.docDate FROM OINM t1 WHERE t1.createDate=[%1]
_select @ItemCode=t2.ItemCode from OITM t2 where t2.ItemCode = '[%2]'

_select tc.ItmsGrpNam N'物料组名称',tc.ItmsGrpCod N'物料组',ta.itemcode N'物料号',tb.ItemName N'物料名称',sum(ta.begqty) N'期初数量',sum(ta.begtotal) N'期初金额', sum(Buyqty) N'采购入库数量',sum(Buytotal) N'采购入库金额',sum(Retqty) N'采购退货数量',sum(Rettotal) N'采购退货金额',sum(inqty) N'库存收货数量',sum(intotal) N'库存收货金额',sum(outqty) N'库存发货数量',sum(outtotal) N'库存发货金额',sum(endqty) N'期未数量',sum(endtotal) N'期未余额'
from (
 _select ItemCode,sum(begqty) begqty,sum(begtotal) begtotal,sum(inqty)inqty,sum(intotal)intotal,sum(outqty) outqty,sum(outtotal) outtotal,sum(endqty) endqty,sum(endtotal) endtotal, sum(BuyQty) BuyQty, sum(BuyTotal) BuyTotal, sum(RetQty) RetQty, sum(RetTotal) RetTotal
  FROM 
   (
       _select a.ItemCode,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)) begqty,sum(a.TransValue) begtotal,0 inqty,0 intotal,0 outqty,0 outtotal,0 endqty,0 endtotal, 0 BuyQty, 0 BuyTotal, 0 RetQty, 0 RetTotal
        from oinm a 
    where ( a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate < IsNull(@dt1, cast('2001-1-1' as smalldatetime))
       group by a.ItemCode
       union all
       _select a.ItemCode,0,0,sum(IsNull(a.InQty,0)),sum(case when a.TransValue>=0 then a.TransValue else 0 end),sum(IsNull(a.OutQty,0)),sum(case when a.TransValue<0 then a.TransValue else 0 end),0,0,0,0,0,0
        from oinm a 
    where ( a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate between IsNull(@dt1, cast('2001-1-1' as smalldatetime)) and IsNull(@dt2,getdate()) And a.TransType not in ('20','21')
        group by a.ItemCode
       union all
       _select a.ItemCode,0,0,0,0,0,0,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)),sum(a.TransValue),0,0,0,0
        from oinm a 
    where ( a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate<=IsNull(@dt2,getdate())
        group by a.ItemCode
       union all
       _select a.ItemCode,0,0,0,0,0,0,0,0,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)),sum(a.TransValue),0,0
        from oinm a 
    where ( a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate between IsNull(@dt1, cast('2001-1-1' as smalldatetime)) and IsNull(@dt2,getdate()) and a.TransType='20'
        group by a.ItemCode
       union all
       _select a.ItemCode,0,0,0,0,0,0,0,0,0,0,sum(IsNull(IsNull(a.InQty,0),0)-IsNull(IsNull(a.OutQty,0),0)),sum(a.TransValue)
        from oinm a 
    where ( a.ItemCode=@ItemCode or IsNull(@ItemCode,'')='') and a.CreateDate between IsNull(@dt1, cast('2001-1-1' as smalldatetime)) and IsNull(@dt2,getdate()) and a.TransType='21'
        group by a.ItemCode
   ) tmp 
   group by ItemCode) ta 
  inner join oitm tb on ta.itemcode = tb.ItemCode 
  inner join oitb tc on tb.ItmsGrpCod = tc.ItmsGrpCod 
group by tc.ItmsGrpNam, tc.ItmsGrpCod, ta.itemcode, tb.ItemName

本文转自foresun  51CTO博客，原文链接：http://blog.51cto.com/foresun/104533，如需转载请自行联系原作者