生产收货报表(模糊查询)
大约 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 库位转入数量, TA.SJSL 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
LEFT JOIN (
SELECT SUM(T1.[U_Qty]) AS SJSL, T1.[U_BaseNum], T1.[U_BaseLine]
FROM [dbo].[@OQAS] T0
INNER JOIN [dbo].[@QAS1] T1 ON T1.docentry = t0.docentry
WHERE T0.[U_DocType] = 'B'
AND T1.[U_Status] = 'OK'
GROUP BY T1.[U_BaseNum], T1.[U_BaseLine]
) TA
ON TA.[U_BaseNum] = T1.[BaseEntry]
AND TA.[U_BaseLine] = T1.[BaseLine]
WHERE (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