Simple Example of MRP computing material demand

--------------------------------------------------------------------------
-- 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: short example of MRP computing Material Net demand
--------------------------------------------------------------------------

-- BOM table (parent code, subclass code, standard usage)
Declare @ BOM table (
Parentid varchar (20 ),
Childid varchar (20 ),
Funitqty float
)
Insert @ BOM select '123', 'p8112000 ', 1
Union all select 'p8112000 ', '0752913ni', 2
Union all select '0752913ni ', '0752913t', 1
Union all select '200', '0752913ni ', 2
Union all select '0752913ni ', '0752913t', 1

-- Order number table (item code, order quantity)
Declare @ mxqty table (
Fnumber varchar (20 ),
Fmxqty float)
Insert @ mxqty select '123', 8112212
Union all select '200', 36

-- Available inventory table (material code, real-time inventory, estimated warehouse volume, allocated volume)
Declare @ stock table (
Fnumber varchar (20 ),
Fstockqty float,
Fexpectinqty float,
Fassignedqty float
)
Insert @ stock
Select '0752913ni ', 10,100, 50 -- 60 (instant inventory + estimated warehouse receiving volume-allocated volume)
Union all
Select '0752913t', 25,500,100 -- 425 (instant inventory + estimated warehouse receiving volume-allocated volume)

-- Calculation formula: Net demand = gross demand-(instant inventory + estimated warehouse receiving volume-allocated volume)
-- 0752913ni: 540 = 600-60
-- 0752913 T: 775 = 1200-425
-- P8112000: 264 = 264-0
-- Select * From @ BOM
-- Select * From @ mxqty
-- Select * From @ stock
; With T
(
Select a. *, planqty = funitqty * B. fmxqty, LVL = 0
From @ BOM
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
Join T B
On B. childid = A. parentid
)
, T1
(
Select childid, sum (planqty) planqty
From t
Group by childid
)
Select fnumber = A. childid,
Mustqty = A. planqty-(isnull (B. fstockqty, 0)
+ Isnull (B. fexpectinqty, 0)-isnull (B. fassignedqty, 0 ))
From T1
Left join @ stock B
On a. childid = B. fnumber

/* -- Result:

Fnumber mustqty
------------------------------------------
0752913ni540
0752913 TB 775
P8112000264

(3 rows affected)
*/

Question: http://topic.csdn.net/u/20100305/14/643ed33f-a5b1-4806-a009-1ae03b1ce0d3.html? Seed = 690801239 & R = 64948015 # r_64948015