产品缺料报表

说明： BOM结构为基础，输入需求的数量，同时合计其他所关联的单据状况

代码示例

DECLARE @itemcode nvarchar(100)
DECLARE @itemname nvarchar(200)
DECLARE @frgnname nvarchar(200)
DECLARE @tmp1 nvarchar(200)

SET @itemcode = '%' + [%0] + '%';
SET @itemname = '%' + [%1] + '%';
SET @frgnname = '%' + [%2] + '%';
SET @tmp1=  [%3]  ;

SELECT @frgnname = frgnname
FROM oitm t4
WHERE t4.frgnname = '[%1]';
WITH Bom (Father, code, BomLevel, sort, warehouse, Qty, issue, mecomment) AS (
		SELECT itt1.father, itt1.code, 1 AS bomlevel
			, convert(varchar(255), itt1.father + char(9) + RIGHT('00000' + CAST(itt1.childnum AS varchar(10)), 5) + char(9) + itt1.code)
			, itt1.warehouse,  cast (itt1.quantity / OITT.QAuNTITY AS NUMERIC (19, 6)) AS quantity, itt1.issuemthd, itt1.comment
		FROM itt1
			INNER JOIN oitt ON oitt.code = itt1.father
			INNER JOIN OITM T99 ON T99.itemcode = itt1.father
		WHERE itt1.father NOT IN (
				SELECT code
				FROM itt1
			)
			AND (T99.itemcode LIKE @itemcode
				OR T99.itemcode IS NULL)
			AND (T99.frgnname LIKE @frgnname
				OR T99.frgnname IS NULL)
			AND (T99.itemname LIKE @itemname
				OR T99.itemname IS NULL)
		UNION ALL
		SELECT e.father, e.code, bomlevel + 1
			, convert(varchar(255), rtrim(sort) + char(9) + RIGHT('00000' + CAST(e.childnum AS varchar(10)), 5) + char(9) + e.code)
			, e.warehouse, cast (e.quantity / OITT.Qauntity * D.Qty AS NUMERIC (19, 6)) AS quantity, e.issuemthd, e.comment
		FROM itt1 e
			INNER JOIN OITT ON E.FATHER = OITT.CODE
			INNER JOIN Bom d ON e.father = d.code
	)
SELECT 产成品, 品名, 深度, 物料号码, 物料描述,需求数量,当前库存数量,短缺数量,已采购申请数量,采购在途数量,生产未领用数量,未转储数量
FROM (
	SELECT NULL AS 产成品, NULL AS 品名, sort
		, '.' + space(bom.bomlevel * 4) + CAST(bomlevel AS nvarchar) AS 深度
		, bom.code AS 物料号码, oitm.itemname AS 物料描述,cast(bom.qty as nvarchar) * cast(@tmp1 as numeric(19,6)) AS 需求数量,ta.onhand as 当前库存数量,cast(bom.qty as nvarchar) * cast(@tmp1 as numeric(19,6))-ta.onhand AS 短缺数量, tb.sqty AS 已采购申请数量,TC.sqty AS 采购在途数量,TD.flqty as 生产未领用数量,te.wzc as 未转储数量
	FROM bom
		LEFT JOIN oitm ON bom.code = oitm.itemcode
-----当前库存数量
LEFT JOIN 
(
SELECT T0.[ItemCode], T0.[OnHand] FROM OITM T0
) TA ON TA.ITEMCODE=bom.code

----未清采购申请数量
LEFT JOIN
(
SELECT T0.[ItemCode], SUM(T0.[OpenQty]) AS sqty FROM PRQ1 T0  INNER JOIN OPRQ T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[CANCELED] ='N'  and T1.[DocStatus] ='O' GROUP BY T0.[ItemCode]
) TB ON TB.ITEMCODE=bom.code

----未清采购订单数量
LEFT JOIN
(
SELECT T0.[ItemCode], SUM(T0.[OpenQty]) AS sqty FROM POR1 T0  INNER JOIN OPOR T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[CANCELED] ='N'  and T1.[DocStatus] ='O' GROUP BY T0.[ItemCode]
) TC ON TC.ITEMCODE=bom.code

-----生产未领用数量
LEFT JOIN
(
SELECT T0.[ItemCode], SUM(T0.[PlannedQty]-T0.[IssuedQty]) AS flqty FROM WOR1 T0 INNER JOIN OWOR T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[Status] ='R' GROUP BY T0.[ItemCode]
) TD ON TD.ITEMCODE=bom.code

----为转储数量
LEFT JOIN 
(
SELECT  T1.[ItemCode], SUM(T1.[Quantity]) - SUM(t2.quantity) AS 'wzc'
		FROM OWTQ T0
			INNER JOIN WTQ1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			LEFT JOIN WTR1 T2
			ON T1.docentry = t2.baseentry
				AND t2.baseline = t1.linenum
		WHERE T0.[Comments] LIKE '%%生产订单%%'
		GROUP BY  T1.[ItemCode]
) TE  ON TE.itemcode= bom.code 




	UNION ALL
	SELECT DISTINCT t.father, oitm.itemname, t.father, '', NULL
		, NULL,NULL,null,null,null,null,null,null
	FROM itt1 t
		LEFT JOIN oitm ON t.father = oitm.itemcode
		INNER JOIN oitt ON t.father = oitt.code
		INNER JOIN OITM T99 ON T99.itemcode = t.father
	WHERE t.father NOT IN (
			SELECT code
			FROM itt1
		)
		AND (T99.itemcode LIKE @itemcode
			OR T99.itemcode IS NULL)
		AND (T99.frgnname LIKE @frgnname
			OR T99.frgnname IS NULL)
		AND (T99.itemname LIKE @itemname
			OR T99.itemname IS NULL)
) t9
ORDER BY sort