跳至主要內容

生产收货报表(仓库版)--模糊查询

五六零网校大约 1 分钟

代码示例

SELECT T0.[JrnlMemo] AS 收货类型, T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription]
	, T2.[FrgnName], T1.[UseBaseUn], T1.[Quantity], T1.[Price], T1.[LineTotal]
	, TS.[BatchNum] AS 批次号, TS.[Quantity] AS 批次数量, TS.[BinCode] AS 库位号, TS.[Quantity] AS 库位转入数量, T2.[OnHand]
	, T5.[AvgPrice]
FROM [dbo].[OIGN] T0
	INNER JOIN [dbo].[IGN1] T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN [dbo].[OITM] T2 ON T1.[ItemCode] = T2.[ItemCode]
	INNER JOIN [dbo].[OITB] T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]
	INNER JOIN [dbo].[OSLP] T4 ON T0.[SlpCode] = T4.[SlpCode]
	INNER JOIN [dbo].[OITW] T5
	ON T5.itemcode = t2.itemcode
		AND t5.whscode = t1.whscode
	LEFT JOIN (
		SELECT T2.[BinCode], T1.[Quantity], T1.[SnBMDAbs], T4.[BatchNum], T0.[LocCode]
			, T0.[DocEntry], T0.[ItemCode], T0.[TransType]
		FROM [dbo].[OILM] T0
			INNER JOIN [dbo].[OBTL] T1 ON T0.[MessageID] = T1.[MessageID]
			INNER JOIN [dbo].[OBIN] T2 ON T1.[BinAbs] = T2.[AbsEntry]
			INNER JOIN [dbo].[OBTN] T3 ON T3.ABSENTRY = T1.[SnBMDAbs]
			INNER JOIN [dbo].[IBT1] T4
			ON T4.[BatchNum] = T3.[DistNumber]
				AND T4.itemcode = t0.itemcode
				AND T4.[WhsCode] = T0.[LocCode]
				AND T4.[BaseEntry] = T0.[DocEntry]
	) TS
	ON Ts.[DocEntry] = t0.docentry
		AND Ts.[ItemCode] = t1.itemcode
		AND Ts.[LocCode] = t1.whscode
		AND TS.[TransType] = t0.objtype
WHERE
---物料编码 大于等于 A    AND  物料编码小于等于 B
 (T1.[ItemCode] Like '%%[%0]%%'or '[%0]'='') 
----物料名称
AND ( T2.[FrgnName]Like '%%[%2]%%' or '[%2]'='') 
----单据日期
AND  ( T0.[taxDate] Like '%%[%4]%%' or '[%4]'='')  
----单据状态
AND   ( T0.[DocStatus] = '[%6]' or '[%6]'='') 
----物料组
AND (T3.[ItmsGrpNam]  Like '%%[%7]%%'or '[%7]'='')
----单据编号
AND (T0.[DOCNUM]  Like '%%[%17]%%' or [%17]='') 
AND
T1.[BaseType]='202' 
ORDER BY T0.[DocNum] DESC