产成品按BOM展开（可筛选）

--使用说明：按产成品展开所有层级BOM，筛选条件不选择默认显示所有BOM
--更新说明
-- 1.修正包含行文本的物料清单取值公式 -- 2.修正包含行文本的物料清单排序公式 -- 3. 增加筛选条件仅显示成品编码

代码示例

DECLARE @ItemCode nvarchar(20)
DECLARE @TMP int
SELECT @TMP=COUNT(*)
FROM(
SELECT DISTINCT T0.[Father] FROM ITT1 T0 
WHERE T0.father NOT IN (
			SELECT code
			FROM ITT1 WHERE code is not null
		)
)T0 WHERE T0.father='[%0]' ;
SET @ItemCode='[%0]';

WITH Bom (Father, code, BomLevel, sort, warehouse, Qty, issue, txt) AS (
		SELECT T0.father, T0.code, 1 AS bomlevel
			, CONVERT(VARCHAR(255), T0.father + CHAR(9) + CAST(T0.ChildNum + 10 AS NVARCHAR))
			, T0.warehouse, CAST(T0.quantity / T1.QAuNTITY AS NUMERIC(19, 6)) AS quantity, T0.issuemthd, T0.LineText
		FROM ITT1 T0
			INNER JOIN OITT T1 ON T1.code = T0.father
		WHERE T0.father NOT IN (
				SELECT code
				FROM ITT1
				WHERE code IS NOT NULL
			)
			AND (T0.father = @itemcode
				OR @itemcode = '')
		UNION ALL
		SELECT T0.father, T0.code, bomlevel + 1
			, CONVERT(VARCHAR(255), RTRIM(sort) + CAST(T0.ChildNum + 10 AS NVARCHAR) + CHAR(9))
			, T0.warehouse, CAST(T0.quantity / T1.Qauntity * T2.Qty AS NUMERIC(19, 6)) AS quantity, T0.issuemthd, T0.LineText
		FROM ITT1 T0
			INNER JOIN OITT T1 ON T0.FATHER = T1.CODE
			INNER JOIN Bom T2 ON T0.father = T2.code
	)
SELECT 产成品, 品名, 深度, 物料号码, 物料描述
	, 用量, 发货仓库, 发货方法, 文本描述
FROM (
	SELECT NULL AS 产成品, NULL AS 品名, sort, bomlevel AS 深度
		, '.' + space(T0.bomlevel * 4) + T0.code AS 物料号码
		, T1.itemname AS 物料描述, CAST(T0.qty AS nvarchar) AS 用量, T0.warehouse AS 发货仓库
		, CASE T0.issue
			WHEN 'M' THEN N'手动'
			WHEN 'B' THEN N'倒冲'
		END AS 发货方法, T0.txt AS 文本描述
	FROM BOM T0
		LEFT JOIN OITM T1 ON T0.code = T1.itemcode
	UNION ALL
	SELECT DISTINCT T0.father, T1.itemname, T0.father, 0, NULL
		, NULL, CAST(T2.Qauntity AS nvarchar), NULL, NULL, NULL
	FROM ITT1 T0
		LEFT JOIN OITM T1 ON T0.father = T1.itemcode
		INNER JOIN OITT T2 ON T0.father = T2.code
	WHERE T0.father NOT IN (
			SELECT code
			FROM ITT1
			WHERE code IS NOT NULL
		)
		AND (T0.father = @itemcode
			OR @itemcode = '')
) t9
ORDER BY sort