Dear All,
I want to frame a query to get the detail of AR Invoice along with Batch& Expiry, But this gives me all the available batches of the item, pls let me know how I can filter this query to get only the batches which is there in the invoice.
My query is as bellow
SELECT T2.[CardCode], T2.[CardName], T2.[Address], T0.[DocNum],T4.[BaseNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription],
T5.[DistNumber] BATCHNO,T5.[ExpDate],
T1.[Quantity],
T1.[PriceBefDi], T1.[DiscPrcnt], T1.[Price], T1.[LineTotal],T3.[WhsName]
FROM [dbo].[OINV] T0
INNER JOIN [dbo].[INV1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OCRD] T2 ON T0.[CardCode] = T2.[CardCode]
INNER JOIN [dbo].[OWHS] T3 ON T1.[WhsCode]= T3.[WhsCode]
INNER JOIN [dbo].[IBT1] T4 ON T0.[DocNum] =T4.[BaseNum]
INNER JOIN [dbo].[OBTN] T5 ON T4.[BatchNum]=T5.[DistNumber]
WHERE T0.[DocDate] >=[%0] and
T0.[DocDate] <=[%1] and
T1.[ItemCode] >=[%2] and
T1.[ItemCode] <=[%3]
rgds
Suman