DECLARE @Main_ITEM_CODE VARCHAR(50) = '220080392',
        @pPLANT_CODE VARCHAR(10) = '1';

WITH BOM_CTE
AS (
   -- 锚点：主件的直接子件
   SELECT b.ITEM_ID AS Main_Item_ID,
          i_main.ITEM_CODE AS Main_Item_Code,
          i_main.ITEM_NAME AS Main_Item_Name,
          d.SOURCE_ID_ROid AS Child_Item_ID,
          i_child.ITEM_CODE AS Child_Item_Code,
          i_child.ITEM_NAME AS Child_Item_Name,
          d.QTY_PER AS Qty_Per,
          CAST(d.QTY_PER AS DECIMAL(16, 6)) AS Cumulative_Qty, -- 直接子件的累计组成用量等于其直接用量
          d.EFFECTIVE_DATE AS Effective_Date,
          d.EXPRITY_DATE AS Expiry_Date,
          CAST(i_main.ITEM_CODE + '.' + i_child.ITEM_CODE AS VARCHAR(MAX)) AS Path,
          1 AS Level
   FROM BOM b
       INNER JOIN ITEM i_main
           ON b.ITEM_ID = i_main.ITEM_BUSINESS_ID
       INNER JOIN BOM_D d
           ON b.BOM_ID = d.BOM_ID
       INNER JOIN ITEM i_child
           ON d.SOURCE_ID_ROid = i_child.ITEM_BUSINESS_ID
       INNER JOIN dbo.PLANT AS p
           ON p.PLANT_ID = b.Owner_Org_ROid
   WHERE p.PLANT_CODE = @pPLANT_CODE
         AND i_main.ITEM_CODE = @Main_ITEM_CODE
   UNION ALL

   -- 递归：子件的下阶子件
   SELECT c.Main_Item_ID,
          c.Main_Item_Code,
          c.Main_Item_Name,
          d.SOURCE_ID_ROid AS Child_Item_ID,
          i_child.ITEM_CODE AS Child_Item_Code,
          i_child.ITEM_NAME AS Child_Item_Name,
          d.QTY_PER AS Qty_Per,
          CAST((c.Cumulative_Qty * d.QTY_PER) AS DECIMAL(16, 6)) AS Cumulative_Qty, -- 累计组成用量 = 父累计用量 × 当前直接用量
          d.EFFECTIVE_DATE AS Effective_Date,
          d.EXPRITY_DATE AS Expiry_Date,
          CAST(c.Path + '.' + i_child.ITEM_CODE AS VARCHAR(MAX)) AS Path,
          c.Level + 1 AS Level
   FROM BOM_CTE c
       INNER JOIN BOM b
           ON c.Child_Item_ID = b.ITEM_ID
       INNER JOIN dbo.PLANT AS p
           ON p.PLANT_ID = b.Owner_Org_ROid
       INNER JOIN BOM_D d
           ON b.BOM_ID = d.BOM_ID
       INNER JOIN ITEM i_child
           ON d.SOURCE_ID_ROid = i_child.ITEM_BUSINESS_ID
   WHERE p.PLANT_CODE = @pPLANT_CODE)
SELECT BOM_CTE.Main_Item_ID 主品号ID,
       Main_Item_Code AS 主件品号,
       Main_Item_Name AS 主件品名,
       BOM_CTE.Child_Item_ID 子品号ID,
       Child_Item_Code AS 子品号,
       Child_Item_Name AS 子品名,
       Qty_Per AS 组成用量,
       Cumulative_Qty AS 累计组成用量,
       Effective_Date AS 生效时间,
       Expiry_Date AS 失效时间,
       Path AS 层级关系
FROM BOM_CTE
--WHERE Child_Item_Code = '130060149'
ORDER BY Path
OPTION (MAXRECURSION 20);
-- 注意：如果BOM层级过深或存在循环，可添加 OPTION (MAXRECURSION N) 控制递归深度