Mrp计算物料净需求简例

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-24 21:00:07
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul  9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258

--  Subject:Mrp计算物料净需求简例
--------------------------------------------------------------------------

--bom表（父类代码，子类代码，标准用量）
declare @bom table(
parentid varchar(20),
childid varchar(20),
funitqty float
)
insert @bom select '8112212','p8112000',1
union all select 'p8112000','0752913ni',2
union all select '0752913ni','0752913t',1
union all select '4212202','0752913ni',2
union all select '0752913ni','0752913t',1

--订单数量表（物料代码，订单数量）
declare @mxqty table(
fnumber varchar(20),
fmxqty float)
insert @mxqty select '8112212',264
union all select '4212202',36

--可用库存表（物料代码，即时库存，预计入库量，已分配量）
declare @stock table(
fnumber varchar(20),
fstockqty float,
fexpectinqty float,
fassignedqty float
)
insert @stock
select '0752913ni',10,100,50  --60(即时库存+ 预计入库量- 已分配量)
union all
select '0752913t',25,500,100 --425(即时库存+ 预计入库量- 已分配量)

--计算公式：净需求量= 毛需求- (即时库存+ 预计入库量- 已分配量)
--0752913ni:    540  = 600    -  60 
--0752913t :    775  = 1200   -  425
--p8112000 :    264  = 264    -  0
--select * from @bom
--select * from @mxqty
--select * from @stock
;with t as
(
    select a.*,planqty=funitqty*b.fmxqty ,lvl=0
    from  @bom a
        join (select fnumber ,sum(fmxqty ) fmxqty @mxqty

             from 

             group by fnumber )  b
            on a.parentid=b.fnumber
    union all
    select a.*,a.funitqty*b.planqty,lvl+1
    from @bom a
        join t b
            on b.childid=a.parentid
)
,t1 as
(
    select childid,sum(planqty) planqty
    from t a
    group by childid
)
select fnumber=a.childid,
    mustqty=a.planqty-(isnull(b.fstockqty,0)
            +isnull(b.fexpectinqty,0)-isnull(b.fassignedqty,0))
from t1 a
    left join @stock b
        on a.childid=b.fnumber
       
/*--result:

fnumber              mustqty
-------------------- ----------------------
0752913ni            540
0752913t             775
p8112000             264

(3 行受影响)
*/

问题贴：http://topic.csdn.net/u/20100305/14/643ed33f-a5b1-4806-a009-1ae03b1ce0d3.html?seed=690801239&r=64948015#r_64948015