SQL——练习：往下展开BOM

--练习：往下展开BOM
IF EXISTS
(
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb.dbo.#temp_bom')
) --是否存在该临时表
    DROP TABLE #temp_bom --存在则删除

CREATE TABLE #temp_bom --创建临时表
(
    ROOT_ITEM_ID UNIQUEIDENTIFIER,   --根品号
    TOTAL_SEQ NVARCHAR(200),         --层级关系
    SEQ INT,                         --序号
    LEV INT,                         --层次
    BOM_ID UNIQUEIDENTIFIER,
    BOM_D_ID UNIQUEIDENTIFIER,
    PARENT_ITEM_ID UNIQUEIDENTIFIER, --父级品号
    ITEM_ID UNIQUEIDENTIFIER,        --自身品号
    EFFECTIVE_DATE DATE,             --生效日期
    EXPRITY_DATE DATE,               --失效日期
    QTY_PER DECIMAL(16, 6)           --组成用量
)

DECLARE @ITEM_CODE VARCHAR(20),
        @CurrentLevel AS INT,
        @Level INT,
        @IsBottom INT -- 0-多阶，1-尾阶

SELECT @ITEM_CODE = '210010001', --130010005
       @Level = 20,
       @IsBottom = 0

INSERT INTO #temp_bom
(
    ROOT_ITEM_ID,
    TOTAL_SEQ,
    SEQ,
    LEV,
    BOM_ID,
    BOM_D_ID,
    PARENT_ITEM_ID,
    ITEM_ID,
    EFFECTIVE_DATE,
    EXPRITY_DATE,
    QTY_PER
)
SELECT b.ITEM_ID AS ROOT_ITEM_ID,
       '0001' AS TOTAL_SEQ,
       1 AS SEQ,
       0 AS LEV,
       b.BOM_ID AS BOM_ID,
       '00000000-0000-0000-0000-000000000000' AS BOM_D_ID,
       '00000000-0000-0000-0000-000000000000' AS PARENT_ITEM_ID,
       b.ITEM_ID AS ITEM_ID,
       CAST('1900-01-01' AS DATE) AS EFFECTIVE_DATE,
       CAST('1900-01-01' AS DATE) AS EXPRITY_DATE,
       1 AS QTY_PER
FROM dbo.BOM AS b
    INNER JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = b.ITEM_ID
WHERE i.ITEM_CODE = @ITEM_CODE
      AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'



SELECT @CurrentLevel = 1

WHILE @CurrentLevel <= @Level
BEGIN

    INSERT INTO #temp_bom
    (
        ROOT_ITEM_ID,
        TOTAL_SEQ,
        SEQ,
        LEV,
        BOM_ID,
        BOM_D_ID,
        PARENT_ITEM_ID,
        ITEM_ID,
        EFFECTIVE_DATE,
        EXPRITY_DATE,
        QTY_PER
    )
    SELECT tb.ROOT_ITEM_ID,                                                                          -- ROOT_ITEM_ID - uniqueidentifier
           tb.TOTAL_SEQ,                                                                             -- TOTAL_SEQ - nvarchar(200)
           (ROW_NUMBER() OVER (PARTITION BY tb.ROOT_ITEM_ID, b.ITEM_ID ORDER BY bd.SequenceNumber)), -- SEQ - int
           @CurrentLevel,                                                                            -- LEV - int
           b.BOM_ID,                                                                                 -- BOM_ID - uniqueidentifier
           bd.BOM_D_ID,                                                                              -- BOM_D_ID - uniqueidentifier
           b.ITEM_ID,                                                                                -- PARENT_ITEM_ID - uniqueidentifier
           bd.SOURCE_ID_ROid,                                                                        -- ITEM_ID - uniqueidentifier
           bd.EFFECTIVE_DATE,                                                                        -- EFFECTIVE_DATE - date
           bd.EXPRITY_DATE,                                                                          -- EXPRITY_DATE - date
           bd.QTY_PER                                                                                -- QTY_PER - decimal(16, 6)
    FROM #temp_bom AS tb
        INNER JOIN dbo.BOM AS b
            ON b.ITEM_ID = tb.ITEM_ID
               AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
        INNER JOIN dbo.BOM_D AS bd
            ON bd.BOM_ID = b.BOM_ID
    WHERE tb.LEV = @CurrentLevel - 1
          AND
          (
              bd.EXPRITY_DATE = '9998-12-31 00:00:00.0000000'
              OR bd.EXPRITY_DATE >= GETDATE()
          )
          AND
          (
              bd.EFFECTIVE_DATE = '1900-01-01 00:00:00.0000000'
              OR bd.EFFECTIVE_DATE <= GETDATE()
          )
    ORDER BY tb.ROOT_ITEM_ID,
             b.ITEM_ID,
             bd.SequenceNumber

    IF @@ROWCOUNT = 0
    BEGIN
        BREAK
    END

    UPDATE tb
    SET tb.TOTAL_SEQ = tb.TOTAL_SEQ + '.' + RIGHT('1000' + CAST(tb.SEQ AS VARCHAR(4)), 4)
    FROM #temp_bom AS tb
    WHERE tb.LEV = @CurrentLevel

    SELECT @CurrentLevel += 1
END

IF @IsBottom = 1
BEGIN
    DELETE tb
    FROM #temp_bom AS tb
    WHERE tb.LEV > 0
          AND EXISTS
    (
        SELECT 1 FROM #temp_bom AS tb2 WHERE tb2.PARENT_ITEM_ID = tb.ITEM_ID
    )
END



SELECT tb.ROOT_ITEM_ID,
       i.ITEM_CODE 根品号,
       --i.ITEM_NAME,
       tb.TOTAL_SEQ,
       tb.SEQ,
       tb.LEV,
       tb.PARENT_ITEM_ID,
       i2.ITEM_CODE 父品号,
       --i2.ITEM_NAME,
       tb.ITEM_ID,
       i3.ITEM_CODE 元件品号,
       --i3.ITEM_NAME,
       tb.EFFECTIVE_DATE,
       tb.EXPRITY_DATE,
       tb.QTY_PER
FROM #temp_bom AS tb
    LEFT JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = tb.ROOT_ITEM_ID
    LEFT JOIN dbo.ITEM AS i2
        ON i2.ITEM_BUSINESS_ID = tb.PARENT_ITEM_ID
    LEFT JOIN dbo.ITEM AS i3
        ON i3.ITEM_BUSINESS_ID = tb.ITEM_ID
ORDER BY tb.TOTAL_SEQ

 

 

 

 

DECLARE @pItemCode_1 VARCHAR(100),
        @pItemCode_level VARCHAR(100),
        @pItemCode_2 VARCHAR(100),
        @pItemCode_3 VARCHAR(100)

SELECT @pItemCode_1 = '220050003'
--@pItemCode_level='160160016'

IF EXISTS
(
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb.dbo.#temp_bom')
) --是否存在该临时表
    DROP TABLE #temp_bom --存在则删除

CREATE TABLE #temp_bom --创建临时表
(
    ROOT_ITEM_ID UNIQUEIDENTIFIER,   --根品号
    TOTAL_SEQ NVARCHAR(MAX),         --层级关系
    SEQ INT,                         --序号
    LEV INT,                         --层次
    BOM_ID UNIQUEIDENTIFIER,
    BOM_D_ID UNIQUEIDENTIFIER,
    PARENT_ITEM_ID UNIQUEIDENTIFIER, --父级品号
    ITEM_ID UNIQUEIDENTIFIER,        --自身品号
    EFFECTIVE_DATE DATE,             --生效日期
    EXPRITY_DATE DATE,               --失效日期
    QTY_PER DECIMAL(16, 6),          --组成用量
    SUM_QTY_PER DECIMAL(16, 6),      --累计组成用量
    ITEM_LEVEL NVARCHAR(MAX),        --品号层级
    COMPONENT_LOCATION NVARCHAR(MAX) --插件位置
)

DECLARE @ITEM_CODE VARCHAR(100),
        @CurrentLevel AS INT,
        @Level INT,
        @IsBottom INT -- 0-多阶，1-尾阶

SELECT @ITEM_CODE = @pItemCode_1,
       @Level = 20,
       @IsBottom = 0

INSERT INTO #temp_bom
(
    ROOT_ITEM_ID,
    TOTAL_SEQ,
    SEQ,
    LEV,
    BOM_ID,
    BOM_D_ID,
    PARENT_ITEM_ID,
    ITEM_ID,
    EFFECTIVE_DATE,
    EXPRITY_DATE,
    QTY_PER,
    SUM_QTY_PER,
    ITEM_LEVEL,
    COMPONENT_LOCATION
)
SELECT b.ITEM_ID AS ROOT_ITEM_ID,
       '0000' AS TOTAL_SEQ,
       1 AS SEQ,
       0 AS LEV,
       b.BOM_ID AS BOM_ID,
       '00000000-0000-0000-0000-000000000000' AS BOM_D_ID,
       '00000000-0000-0000-0000-000000000000' AS PARENT_ITEM_ID,
       b.ITEM_ID AS ITEM_ID,
       CAST('1900-01-01' AS DATE) AS EFFECTIVE_DATE,
       CAST('9998-12-31' AS DATE) AS EXPRITY_DATE,
       1 AS QTY_PER,
       1 AS SUM_QTY_PER,
       i.ITEM_CODE AS ITEM_LEVEL,
       ''
FROM dbo.BOM AS b
    INNER JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = b.ITEM_ID
WHERE i.ITEM_CODE = @ITEM_CODE
      AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'



SELECT @CurrentLevel = 1

WHILE @CurrentLevel <= @Level
BEGIN

    INSERT INTO #temp_bom
    (
        ROOT_ITEM_ID,
        TOTAL_SEQ,
        SEQ,
        LEV,
        BOM_ID,
        BOM_D_ID,
        PARENT_ITEM_ID,
        ITEM_ID,
        EFFECTIVE_DATE,
        EXPRITY_DATE,
        QTY_PER,
        SUM_QTY_PER,
        ITEM_LEVEL,
        COMPONENT_LOCATION
    )
    SELECT tb.ROOT_ITEM_ID,
           tb.TOTAL_SEQ,
           (ROW_NUMBER() OVER (PARTITION BY tb.ROOT_ITEM_ID, b.ITEM_ID ORDER BY bd.SequenceNumber)),
           @CurrentLevel,
           b.BOM_ID,
           bd.BOM_D_ID,
           b.ITEM_ID,
           bd.SOURCE_ID_ROid,
           bd.EFFECTIVE_DATE,
           bd.EXPRITY_DATE,
           bd.QTY_PER,
           (bd.QTY_PER * tb.SUM_QTY_PER),
           (tb.ITEM_LEVEL + '.' + i2.ITEM_CODE) ITEM_LEVEL,
           bd.COMPONENT_LOCATION
    FROM #temp_bom AS tb
        INNER JOIN dbo.BOM AS b
            ON b.ITEM_ID = tb.ITEM_ID
               AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
        INNER JOIN dbo.ITEM AS i
            ON i.ITEM_BUSINESS_ID = b.ITEM_ID
        INNER JOIN dbo.BOM_D AS bd
            ON bd.BOM_ID = b.BOM_ID
        INNER JOIN dbo.ITEM AS i2
            ON i2.ITEM_BUSINESS_ID = bd.SOURCE_ID_ROid
    WHERE tb.LEV = @CurrentLevel - 1
          AND
          (
              bd.EXPRITY_DATE = '9998-12-31 00:00:00.0000000'
              OR bd.EXPRITY_DATE > GETDATE()
          )
          AND
          (
              bd.EFFECTIVE_DATE = '1900-01-01 00:00:00.0000000'
              OR bd.EFFECTIVE_DATE <= GETDATE()
          )
    ORDER BY tb.ROOT_ITEM_ID,
             b.ITEM_ID,
             bd.SequenceNumber

    IF @@ROWCOUNT = 0
    BEGIN
        BREAK
    END

    UPDATE tb
    SET tb.TOTAL_SEQ = tb.TOTAL_SEQ + '.' + RIGHT('1000' + CAST(tb.SEQ AS VARCHAR(4)), 4)
    FROM #temp_bom AS tb
    WHERE tb.LEV = @CurrentLevel

    SELECT @CurrentLevel += 1
END

IF @IsBottom = 1
BEGIN
    DELETE tb
    FROM #temp_bom AS tb
    WHERE tb.LEV > 0
          AND EXISTS
    (
        SELECT 1 FROM #temp_bom AS tb2 WHERE tb2.PARENT_ITEM_ID = tb.ITEM_ID
    )
END



SELECT tb.ROOT_ITEM_ID 主件ID,
       i.ITEM_CODE 主件品号,
       i.ITEM_NAME 主件品名,
       i.ITEM_SPECIFICATION 主件规格,
       tb.PARENT_ITEM_ID 产品ID,
       i2.ITEM_CODE 产品品号,
       i2.ITEM_NAME 产品品名,
       i2.ITEM_SPECIFICATION 产品规格,
       tb.ITEM_ID 元件ID,
       i3.ITEM_CODE 元件品号,
       i3.ITEM_NAME 元件品名,
       i3.ITEM_SPECIFICATION 元件规格,
       ip.ITEM_PROPERTY 品号类型,
       tb.QTY_PER 组成用量,
       tb.SUM_QTY_PER 累计组成用量,
       u.UNIT_NAME 单位,
       tb.COMPONENT_LOCATION 插件位置,
       (CASE
            WHEN tb.EFFECTIVE_DATE = '1900-01-01' THEN
                ''
            ELSE
                CONVERT(VARCHAR(10), tb.EFFECTIVE_DATE, 120)
        END
       ) 生效日期,
       (CASE
            WHEN tb.EXPRITY_DATE = '9998-12-31' THEN
                ''
            ELSE
                CONVERT(VARCHAR(10), tb.EXPRITY_DATE, 120)
        END
       ) 失效日期,
       tb.ITEM_LEVEL 品号层阶关系,
       tb.TOTAL_SEQ 层阶关系,
       tb.LEV 层阶,
       tb.SEQ 层阶序号
FROM #temp_bom AS tb
    LEFT JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = tb.ROOT_ITEM_ID
    LEFT JOIN dbo.ITEM AS i2
        ON i2.ITEM_BUSINESS_ID = tb.PARENT_ITEM_ID
    LEFT JOIN dbo.ITEM AS i3
        ON i3.ITEM_BUSINESS_ID = tb.ITEM_ID
    LEFT JOIN dbo.ITEM_PLANT AS ip
        ON ip.ITEM_ID = i3.ITEM_BUSINESS_ID
           AND ip.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
    LEFT JOIN dbo.UNIT AS u
        ON u.UNIT_ID = i3.STOCK_UNIT_ID
WHERE (
          i3.ITEM_CODE = @pItemCode_3
          OR ISNULL(@pItemCode_3, '') = ''
      )
      AND
      (
          i2.ITEM_CODE = @pItemCode_2
          OR ISNULL(@pItemCode_2, '') = ''
      )
      AND
      (
          tb.ITEM_LEVEL LIKE ('%' + @pItemCode_level + '%')
          OR ISNULL(@pItemCode_level, '') = ''
      )