Sales report with Batch number & expiry dates

[former_member186803]

Hello friends,

would any one please help me to frame this query to achieve the above ..

my query is as bellow, but I want to add the batch number & expiry dates along with the other information.....

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] >=[%0] AND  T1.[ItemCode] <=[%1] AND  T0.[DocDate] >=[%2] AND  T0.[DocDate] <=[%3]

So which table to join to get the batch & expiry...

Rgds

Suman

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T4.[DistNumber] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left join  OITL T2 on t1.docentry = T2.[ApplyEntry] and T1.[LineNum] = T2.[ApplyLine] and T2.[ApplyType] = 13 left JOIN ITL1 T3 ON T2.LogEntry = T3.LogEntry left join  OBTN T4 on T4.[ItemCode]  = T3.[ItemCode] and T3.[MdAbsEntry]  =  t4.[absentry] WHERE T1.[ItemCode] >=[%0] AND  T1.[ItemCode] <=[%1] AND  T0.[DocDate] >=[%2] AND  T0.[DocDate] <=[%3]

Thanks & Regards,

Nagarajan

[Former Member]

HI

Batch table is OIBT, IBT1,OBTN

SELECT distinct T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T4.[ExpDate] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry inner join

IBT1 T2 on T1.ItemCode = t2.ItemCode and T0.objtype=T2.basetype and T2.baseentry=T1.Docentry inner join

oitm T3 on T1.Itemcode=T3.Itemcode inner join OIBT T4 on T1.Itemcode=T4.Itemcode  WHERE T1.[ItemCode] >=[%0] AND  T1.[ItemCode] <=[%1] AND  T0.[DocDate] >=[%2] AND  T0.[DocDate] <=[%3]

group by T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity],T4.[ExpDate]

Thanks

Mohammad Imran