获取单个BOM所有层级

-- 单个母料所有层级获取
-- EXEC Pro_GetBOMItems @ItemMaster=1002103110847604
CREATE OR ALTER PROCEDURE [dbo].[Pro_GetBOMItems]
    @ItemMaster AS BIGINT -- 母料ID
WITH ENCRYPTION
AS
BEGIN
    -- 递归当前成品
    ;WITH BOM_Lines (LevelA, BOM, FItem, FItemCode, FItemName, BOMLine, SonItem, SonCode, SonName, BOMQty, PQty, Scrap, BOMLevel)
    AS(
        -- 起始
        SELECT
            1 AS LevelA,
            BOM.ID AS BOM,
            FItem.ID AS FItem,
            FItem.Code AS FItemCode,
            FItem.Name AS FItemName,
            BOMLine.ID AS BOMLine,
            SItem.ID AS SonItem,
            SItem.Code AS SonCode,
            SItem.Name AS SonName,
            BOMLine.UsageQty AS BOMQty,
            BOMLine.ParentQty AS PQty,
            1 + BOMLine.Scrap AS Scrap,
            CAST(ROW_NUMBER() OVER(ORDER BY BOM.ID) AS VARCHAR(200)) AS BOMLevel
        FROM
            CBO_BOMMaster AS BOM
            INNER JOIN CBO_ItemMaster AS FItem ON BOM.ItemMaster = FItem.ID
            INNER JOIN CBO_BOMComponent AS BOMLine ON BOM.id = BOMLine.BOMMaster
            INNER JOIN CBO_ItemMaster AS SItem ON BOMLine.ItemMaster = SItem.ID
        WHERE
            BOMLine.IsCharge = 1
            AND BOM.Status= 2
            AND BOM.EffectiveDate <= GETDATE()
            AND GETDATE() <= BOM.DisableDate
            AND BOMLine.IsEffective = 1
            AND BOM.ItemMaster = @ItemMaster
            
        UNION ALL
        -- 递归
        SELECT
            b.LevelA + 1 AS LevelA,
            BOM.ID AS BOM,
            FItem.ID AS FItem,
            FItem.Code AS FItemCode,
            FItem.Name AS FItemName,
            BOMLine.ID AS BOMLine,
            SItem.ID AS SonItem,
            SItem.Code AS SonCode,
            SItem.Name AS SonName,
            BOMLine.UsageQty AS BOMQty,
            BOMLine.ParentQty AS PQty,
            1 + BOMLine.Scrap AS Scrap,
            CAST(b.BOMLevel +'_'+ CAST(ROW_NUMBER() OVER(ORDER BY BOM.ID) AS VARCHAR(50)) as VARCHAR(200)) AS BOMLevel
        FROM
            CBO_BOMMaster AS BOM
            INNER JOIN CBO_ItemMaster AS FItem ON BOM.ItemMaster = FItem.ID
            INNER JOIN CBO_BOMComponent AS BOMLine ON BOM.id = BOMLine.BOMMaster
            INNER JOIN CBO_ItemMaster AS SItem ON BOMLine.ItemMaster = SItem.ID
            INNER JOIN BOM_Lines AS b ON BOM.ItemMaster = b.SonItem
        WHERE
            BOMLine.IsCharge = 1
            AND BOM.Status= 2
            AND BOM.EffectiveDate <= GETDATE()
            AND GETDATE() <= BOM.DisableDate
            AND BOMLine.IsEffective = 1
    )
    SELECT * FROM BOM_Lines

END