生产订单进度明细报表

代码示例

SELECT T0.[DocNum], T0.[PostDate], T0.[StartDate], T0.[DueDate], T0.[Type]
	, T0.[ItemCode] AS 产成品, T1.[ItemCode], T2.[ItemName], T2.[FrgnName]
	, T1.[BaseQty], T1.[PlannedQty], T3.[OnOrder] - T3.[IsCommited] + T3.[OnHand] AS 可用数量
	,T1.[wareHouse], ts.sqqt AS '转储申请数量', ts.zc AS '已转储数量',  T1.[PlannedQty] - T1.[IssuedQty] AS 未清数量
	, T0.[CmpltQty]
FROM [dbo].[OWOR] T0  INNER JOIN  [dbo].[WOR1] T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN  [dbo].[OITM] T2 ON T2.itemcode=t1.itemcode INNER JOIN  [dbo].[OITW] T3 ON T2.[ItemCode] = T3.[ItemCode]
LEFT JOIN (
		SELECT t0.docnum
			, LEFT(substring(T0.[Comments], charindex('"', T0.[Comments], 0) + 1, LEN(T0.[Comments])), CHARINDEX('"', substring(T0.[Comments], charindex('"', T0.[Comments], 0) + 1, LEN(T0.[Comments]))) - 1) AS 'sc'
			, T1.[ItemCode], T1.[Quantity] AS 'sqqt', SUM(t2.quantity) AS 'zc'
		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 T0.[DocNum], T0.[Comments], T1.[ItemCode], T1.[Dscription], T1.[Quantity]
	) TS
	ON ts.sc = t0.docnum
		AND ts.itemcode = t1.itemcode 
AND (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]'='') 
ORDER BY T0.[DocNum] DESC