小崔说SAP技术博客 - BOM层次
type: channel
link: https://sapxiaocui.top/index.php/tag/BOM%E5%B1%82%E6%AC%A1/
items: SAP B1 完整BOM层级结构SQL语句

SAP B1 完整BOM层级结构SQL语句
type: item
date: 2025-12-29T05:53:00+00:00
description: 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 "ItemNam…