生产订单齐套缺料报表

代码示例

SELECT T0.[DocNum], T0.[PostDate], T0.[StartDate], T0.[DueDate], T0.[Type]
	, T1.[LineNum], T1.[ItemCode], T2.[ItemName], T2.[FrgnName], T1.[BaseQty]
	, T1.[PlannedQty], TB.kyl AS 可用量, T1.[wareHouse], T1.[PlannedQty] / T1.[BaseQty] AS '应领数量/基础数量'
	, T1.[IssuedQty] AS '已领数量', T1.[PlannedQty] - T2.[OnHand] AS 缺料数量, T2.[OnHand]
	, T1.[PlannedQty] - T1.[IssuedQty] AS 未清数量
	, T0.[PickRmrk] AS 订单备注
FROM [dbo].[OWOR] T0
	INNER JOIN [dbo].[WOR1] T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN [dbo].[OITM] T2 ON T2.itemcode = t1.itemcode
	LEFT JOIN (
		SELECT T0.[ItemCode], SUM(T0.[OnHand] + T0.[OnOrder] - T0.[IsCommited]) AS kyl
		FROM OITW T0
		GROUP BY T0.[ItemCode]
	) TB
	ON TB.itemcode = t1.itemcode
	INNER JOIN OUSR T3 ON T0.[UserSign] = T3.[USERID]
WHERE (T0.[Type] = '[%1]'
		OR '[%1]' = '')
	AND (T0.[Status] = '[%2]'
		OR '[%2]' = '')
	AND (T0.[PostDate] >= '[%3]'
		OR '[%3]' = '')
	AND (T0.[PostDate] <= '[%13]'
		OR '[%13]' = '')
	AND (T0.[DueDate] >= '[%4]'
		OR '[%4]' = '')
	AND (T0.[DueDate] <= '[%14]'
		OR '[%14]' = '')
	AND (T0.[ItemCode] >= '[%5]'
		OR '[%5]' = '')
	AND (T0.[ItemCode] <= '[%15]'
		OR '[%15]' = '')
	AND (T0.[Warehouse] >= '[%7]'
		OR '[%7]' = '')
	AND (T0.[Warehouse] <= '[%17]'
		OR '[%17]' = '')
	AND (T0.[OriginNum] >= [%8]
		OR [%8] = '')
	AND (T0.[OriginNum] <= [%18]
		OR [%18] = '')
	AND (T0.[CardCode] >= '[%9]'
		OR '[%9]' = '')
	AND (T0.[CardCode] <= '[%19]'
		OR '[%19]' = '')
	AND (T3.[U_NAME] >= '[%10]'
		OR '[%10]' = '')
	AND (T3.[U_NAME] <= '[%20]'
		OR '[%20]' = '')
ORDER BY T0.[DocNum] DESC