BOM展开SQL Server数据库算法[转]

BOM为一个树状结构，通常，ERP中用一个Table保存BOM的数据（Oracle、SAP以及其它ERP系统等）。
很多时候，在ERP或其它一些系统中，经常需要这样的数据结构：BOM的最顶层成品半成品和最底层所有原材料，不需要中间节点。这样，就需要求一颗树的所有或者是部分根节点。当然，在面向对象的设计中，一层一层的遍历，找出所有根节点并不难。但是，当一次需要对几万甚至几十万的成品、半成品来操作，并且BOM结构又比较复杂（层次普遍比较多）的情况下，面向对象的设计效率将是一个很大的问题。最快的办法是直接在数据库中使用存储过程完成。
算法描述
下面在SQL Server中实现这个算法。
假设多层的BOM存储在表TBLBOM中，直接对TBLBOM进行处理，处理完毕之后结果就是TBLBOM中的记录，表结构如下：
-----------------------------------------------------

 
    TBLBOM 
    ( 
       --制造工厂 
       PPLANT nvarchar(8), 
       --父料 
       PITEM nvarchar(20), 
       --发料工厂 
       CPLANT nvarchar(8), 
       --子料 
       CITEM nvarchar(20), 
       --有效期开始 
       EFFECTIVEDATE datetime, 
       --有效期结束 
       DISCONTINUEDATE datetime, 
       --用量 
       USAGE decimal(18,6) 
    )

-----------------------------------
处理过程中，创建临时表#i_temp_bom存放中间处理数据，在一系列循环中完成处理。
每次，我们将那些下层节点不是根节点的记录取出来，放入#i_temp_bom中。在这一次的循环中，我们把这些节点往下展开一层。
假如BOM的结构如下图所示：

在TBLBOM中的数据如下：

实际上，算法将得到四个BOM结构，在这个算法描述部分，我们只关注00001的展开情况。
第一次循环，将记录00001-00010、00001-00012、00012-00122这三条记录取到临时表#i_temp_bom中，因为这三条记录的下层节点不是根节点。
在第一次循环内，将这三条记录的下层节点进行展开。展开后的00001结构如下：

在TBLBOM中的数据如下：

现在，整个TBLBOM表中就只有00001-00122这一条数据还未展开到最底层。接下来，第二次循环中，即将这条记录展开到最底层。
最终得到00001的结构如下：

TBLBOM中的数据如下：

SQL实现

    create table #i_temp_bom
    (
       PPLANT nvarchar(8),
       PITEM nvarchar(20),
       CPLANT nvarchar(8),
       CITEM nvarchar(20),
       EFFECTIVEDATE datetime,
       DISCONTINUEDATE datetime,
       USAGE decimal(18,6)
    )
    insert into #i_temp_bom(PPLANT,PITEM,CPLANT,CITEM,USAGE,EFFECTIVEDATE,DISCONTINUEDATE)
       select distinct A.PPLANT,A.PITEM,A.CPLANT,A.CITEM,A.USAGE,A.EFFECTIVEDATE,A.DISCONTINUEDATE
       from TBLBOM A
       inner join (select distinct PPLANT,PITEM from TBLBOM)T on T.PPLANT=A.CPLANT and T.PITEM=A.CITEM
    while exists(select top 1 * from #i_temp_bom)
    begin
       insert into TBLBOM (PPLANT,PITEM,CPLANT,CITEM,USAGE,EFFECTIVEDATE,DISCONTINUEDATE)
          select B.PPLANT,B.PITEM,A.CPLANT,A.CITEM,A.USAGE*B.USAGE,
             case when A.EFFECTIVEDATE>=B.EFFECTIVEDATE then A.EFFECTIVEDATE else B.EFFECTIVEDATE end,
             case when A.DISCONTINUEDATE<=B.DISCONTINUEDATE then A.DISCONTINUEDATE else B.DISCONTINUEDATE end
          from TBLBOM A
          inner join #i_temp_bom B on B.CPLANT=A.PPLANT and B.CITEM=A.PITEM
       delete TBLBOM
          from #i_temp_bom A
          where TBLBOM.PPLANT=A.PPLANT and TBLBOM.PITEM=A.PITEM and TBLBOM.CPLANT=A.CPLANT and TBLBOM.CITEM=A.CITEM
       truncate table #i_temp_bom
       insert into #i_temp_bom(PPLANT,PITEM,CPLANT,CITEM,USAGE,EFFECTIVEDATE,DISCONTINUEDATE)
          select distinct A.PPLANT,A.PITEM,A.CPLANT,A.CITEM,A.USAGE,A.EFFECTIVEDATE,A.DISCONTINUEDATE
          from TBLBOM A
          inner join (select distinct PPLANT,PITEM from TBLBOM)T on T.PPlant=A.CPlant and T.PItem=A.CItem
    end
    drop table #i_temp_bom

附加说明
不管BOM的层级是多少，最多5次循环会将整个BOM结构完全展开。
数据量大时，利用索引，包括临时表也可以创建索引，能够进一步加快执行。
不同的ERP系统，BOM表的数据结构会不一样，关键字等都可能不相同。上面的算法只是以一种最简单的BOM结构为例进行说明。在针对具体的ERP BOM数据结构时，需要考虑更多的情况，否则算法中将存在Bug。
实际上ERP系统中，都会有类似半成品（或者半成品BOM）、虚拟件（或者虚拟BOM）等，有些类型需要继续展开下层，而有些是不需要，可以根据具体的情况和需求加上限制条件。如果可以提供一个成品、半成品列表，只需要这个列表中材料的BOM结构，也可以运用到算法中，以缩小每次的数据范围。在这样的算法中，BOM结构里的有些信息是没办法保留的，可能这些信息你也不需要，例如替代关系、工序等。

posted on 2010-03-15 20:06 EricLi 阅读(2974) 评论(0) 编辑收藏举报