SAP完整BOM层级结构

SQL版本

--SQL版本

DROP FUNCTION [IPS_BomList]

GO

CREATE FUNCTION [IPS_BomList]()
RETURNS TABLE
AS
RETURN
(
  WITH BOMChild AS 
  (
    --获取产品数据
    SELECT 
      T0.Code AS ProductCode,
      T0.Name AS ProductName,
      CAST(NULL AS NVARCHAR(50)) AS FatherCode,
      CAST(NULL AS NVARCHAR(200)) AS FatherName,
      T0.Code AS ItemCode,
      T0.Name AS ItemName,
      T0.Qauntity AS Quantity,
      CAST(T0.Code AS NVARCHAR(500)) AS BomCode,
      1 AS BomLevel,
      CAST('000' AS NVARCHAR(200)) AS LevelTag
    FROM OITT T0 
    WHERE 1=1

    UNION ALL 

    --递归获取子件
    SELECT
      T2.ProductCode,
      T2.ProductName, 
      T0.Code AS FatherCode,
      T0.Name AS FatherName,
      T1.Code AS ItemCode,
      T1.ItemName AS ItemName,
      T1.Quantity AS Quantity,
      CAST(REPLICATE('　',T2.BomLevel)+T1.Code AS NVARCHAR(500)) AS BomCode,
      T2.BomLevel+1 AS BomLevel,
      CAST(T2.LevelTag+'.'+RIGHT('000'+CAST(T1.ChildNum AS NVARCHAR(10)),3) AS NVARCHAR(200)) AS LevelTag
    FROM OITT T0
    INNER JOIN ITT1 T1 ON T1.Father=T0.Code
    INNER JOIN BOMChild T2 ON T2.ItemCode=T0.Code OR (T2.BomLevel IS NULL AND T0.Code = T2.ProductCode)
  )

  SELECT
    ProductCode,
    ProductName,
    FatherCode,
    FatherName,
    ItemCode,
    ItemName,
    Quantity,
    BomCode,
    BomLevel,
    LevelTag
  FROM BOMChild  
)

HANA版本

--HANA版本

DROP PROCEDURE "IPS_BomList";
CREATE PROCEDURE "IPS_BomList"()
AS
BEGIN
  DECLARE CNT INTEGER;

  --获取所有产成品
  TMP_Product=
    SELECT
      T0."Code" AS "ProductCode",
      T0."Name" AS "ProductName",
      T0."Code" AS "ItemCode",
      T0."Name" AS "ItemName",
      T0."Qauntity" AS "Quantity",
      CAST('000' AS NVARCHAR(200)) AS "BOM_Level"
    FROM "OITT" T0
    ORDER BY T0."Code";

  --获取所有BOM明细清单
  TMP_BOMDetail=
    SELECT
      T0."Code" AS "ItemCode",
      T0."Name" AS "ItemName",
      T0."Qauntity" AS "Quantity",
      T1."Code" AS "ChildCode",
      T1."ItemName" AS "ChildName",
      T1."Quantity" AS "ChildQuantity",
      T1."ChildNum",
      CAST('000.'||RIGHT('000'||(T1."ChildNum"),3) AS NVARCHAR(200)) AS "BOM_Level"
    FROM OITT T0
    INNER JOIN ITT1 T1 ON T1."Father"=T0."Code"; 

  --将产成品构建到总表中
  TMP_ProcBom=
    SELECT * FROM :TMP_Product;
  
  --获取下级清单
  TMP_Child=
    SELECT
      T0."ProductCode",
      T0."ProductName",
      T1."ChildCode" AS "ItemCode",
      T1."ChildName" AS "ItemName",
      T1."ChildQuantity" AS "Quantity",
      T0."BOM_Level"||'.'||RIGHT('000'||T1."ChildNum",3) AS "BOM_Level"
    FROM :TMP_Product T0
    INNER JOIN :TMP_BOMDetail T1 ON T1."ItemCode"=T0."ItemCode";

  --判断是否存在下级，存在则进行合并
  SELECT COUNT(1) INTO CNT FROM :TMP_Child;
  WHILE :CNT>0 DO
    --合并到总表
    TMP_ProcBom=
      SELECT * FROM :TMP_ProcBom
      UNION ALL
      SELECT * FROM :TMP_Child;
    
    --根据下级清单继续获取下下级清单
    TMP_Child=
      SELECT  
        T0."ProductCode",
        T0."ProductName",
        T1."ChildCode" AS "ItemCode",
        T1."ChildName" AS "ItemName",
        T1."ChildQuantity" AS "Quantity",
        T0."BOM_Level"||'.'||RIGHT('000'||T1."ChildNum",3) AS "BOM_Level"
      FROM :TMP_Child T0
      INNER JOIN :TMP_BOMDetail T1 ON T1."ItemCode"=T0."ItemCode";
    
    --获取下级记录数(当记录数>0时，循环合并和取下级操作)
    SELECT COUNT(1) INTO CNT FROM :TMP_Child;
  
  END WHILE;
  
  --返回结果
  SELECT * FROM :TMP_ProcBom T0
  ORDER BY T0."ProductCode",T0."BOM_Level";
 
END;