BOM算法

在Oracle、SAP以及其它ERP等系统中，BOM为一个树状结构，通常用一个Table保存BOM的数据。
我们经常在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结构里的有些信息是没办法保留的，可能这些信息你也不需要，例如替代关系、工序等。