--练习：往下展开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, '') = ''
)
