整理最近做的一个存储过程：“提取Bom物料及对应需求数量”

最近帮同事做了一个存储过程，要求是根据Bom单号和数量，分解需要物料及对应数量。现在整理一下核心功能做个记录。

通过这件事我也加深了对信息化的基本认识，“快速准确地提供企业数据”，也因此感到自己工作的真实意义。希望这种机会越来越多，呵呵。

 

背景介绍：

　    物料清单（BOM, Bill of Material），是指产品所需零部件明细表及其结构。具体而言，物料清单是构成父项装配件的所有子装配件、零件和原材料的清单，也是制造一个装配件所需要每种零部件的数量的清单。 

 

技术点： 

　   迭代；逐级分解Bom直至明细

 

代码如下： 

 -- Bom主表

CREATE TABLE tBom
(
    OID        BIGINT IDENTITY,
    vName    NVARCHAR(50)    --产品（部件）名称
)
GO

-- Bom子表
CREATE TABLE tBomBody
(
    OID        BIGINT IDENTITY,
    lParent BIGINT,
    lMaterial    BIGINT,
    dcQty    DECIMAL(18, 5),
    lNextBomId    BIGINT        --下一级BomId
)
GO

CREATE PROC up_GetMaterial_FromBom
    @lBomId BIGINT,                    /*Bom单内码Id*/
    @dcQty    DECIMAL(18, 5)            /*数量*/
AS
/*
创建时间：2010-02-04 15:11
创建人员：王召冠
功能描述：根据参数逐级取得BOM单的所有叶子物料及对应的需求数量

*/

--暂存当前循环的Bom主表信息及对应的子表信息
DECLARE @tBom TABLE(lBomId BIGINT, dcQty DECIMAL(38, 10))
DECLARE @tBomBody TABLE(lMaterialId BIGINT, dcQty DECIMAL(18, 5), lNextBomId BIGINT)
DECLARE @tbMaterial TABLE(lMaterialId BIGINT, dcQty DECIMAL(18, 5))

INSERT INTO @tBom ( lBomId, dcQty )
VALUES( @lBomId, @dcQty )

--依次循环取出每个Bom需要的所有叶子级物料及对应数量（无论它是原料还是部件）
WHILE EXISTS(SELECT 1 FROM @tBom)
BEGIN

    --清除可能残留的上一级Bom子表的信息，以便于暂存当前级别Bom的子表信息
    DELETE FROM @tBomBody    
    --生成当前Bom的子表信息，各物料累计数量=主表数量*子表各物料的需求数量；
    --（注：主表数量其实是从上级Bom累计的数量）
    INSERT INTO @tBomBody (lMaterialId, dcQty, lNextBomId) 
    SELECT    b.lMaterialID, 
            a.dcQty * b.dcQty,
            b.lNextBomId
    FROM    @tBom AS a 
        JOIN PR_BOMBody AS b ON a.lBomId = b.lParent

    --将叶子级物料暂存至最终物料表（注：叶子级物料即没有下一级Bom）
    INSERT INTO @tbMaterial (lMaterialId, dcQty) 
    SELECT    lMaterialId, dcQty
    FROM    @tBomBody
    WHERE    lNextBomId IS NULL
    
    --当前级别bom处理完毕，删除暂存的Bom主表信息
    DELETE FROM @tBom
    --取出下一级Bom主表信息
    INSERT INTO @tBom (lBomId, dcQty) 
    SELECT    lNextBomId, dcQty
    FROM    @tBomBody
    WHERE    lNextBomId IS NOT NULL
END

--生成bom单对应的所有物料
SELECT    lMaterialId, SUM(dcQty) AS dcQty
FROM    @tbMaterial
GROUP BY lMaterialId