Skip to Content
0
May 19, 2015 at 08:24 AM

Query to Invoice batch & expiry

705 Views

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