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         --自身品号
)

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

SELECT @ITEM_CODE = '130010009',
       @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
)
SELECT bd.SOURCE_ID_ROid AS ROOT_ITEM_ID,
       '0000' AS TOTAL_SEQ,
       (ROW_NUMBER() OVER (PARTITION BY bd.SOURCE_ID_ROid ORDER BY bd.PARENT_ITEM_ID)) AS SEQ,
       1 AS LEV,
       bd.BOM_ID AS BOM_ID,
       bd.BOM_D_ID AS BOM_D_ID,
       bd.PARENT_ITEM_ID AS PARENT_ITEM_ID,
       bd.SOURCE_ID_ROid AS ITEM_ID
FROM dbo.BOM_D AS bd
    INNER JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = bd.SOURCE_ID_ROid
WHERE i.ITEM_CODE = @ITEM_CODE
      AND bd.SOURCE_ID_RTK = 'ITEM'
      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 bd.SOURCE_ID_ROid,
         bd.PARENT_ITEM_ID


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 = 1


SELECT @CurrentLevel = 2

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
    )
    SELECT tb.ROOT_ITEM_ID,
           tb.TOTAL_SEQ,
           (ROW_NUMBER() OVER (PARTITION BY tb.ROOT_ITEM_ID,
                                            tb.TOTAL_SEQ
                               ORDER BY tb.PARENT_ITEM_ID,
                                        bd.PARENT_ITEM_ID
                              )
           ),
           @CurrentLevel,
           b.BOM_ID,
           bd.BOM_D_ID,
           bd.PARENT_ITEM_ID,
           bd.SOURCE_ID_ROid
    FROM #temp_bom AS tb
        INNER JOIN dbo.BOM_D AS bd
            ON bd.SOURCE_ID_ROid = tb.PARENT_ITEM_ID
               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()
               )
        INNER JOIN dbo.BOM AS b
            ON b.BOM_ID = bd.BOM_ID
               AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
    WHERE tb.LEV = @CurrentLevel - 1

    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 EXISTS
    (
        SELECT 1 FROM #temp_bom AS tb2 WHERE tb2.ITEM_ID = tb.PARENT_ITEM_ID
    )
END



SELECT --tb.BOM_ID,tb.BOM_D_ID,
    --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
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