Hey Everyone,
I am trying to write a query which return batch number, batch expiry and the quantity that was removed from the batch in sales invoice. Here's my code:
select t0.CardCode, t0.CardName,t0.LicTradNum, T0.DiscSum,t0.Address, t0.DocEntry, t0.DocDate,T5.Rate,
t1.Dscription,t1.price,t3.Quantity, T1.VatSum,
T0.DiscPrcnt,t4.DistNumber,t4.ExpDate,t1.PriceAfVAT,t1.LineTotal,t1.GTotal, t0.DocTotal
from OINV t0
INNER JOIN INV1 t1 on t0.DocEntry = t1.DocEntry
LEFT OUTER JOIN IBT1 T3 ON T1.BaseEntry= T3.BaseEntry AND T1.ItemCode = T3.ItemCode AND T3.BaseType = 15
LEFT OUTER JOIN obtn T4 on T4.DistNumber = T3.BatchNum
left outer join OVTG T5 on t1.VatGroup=t5.Code
The problem that I am facing is that it return null in those fields if the invoice is copied from sale order or generated directly but works fine if invoice is copied from delivery. I have tried replacing T3.BaseType to '17' and '13' still doesn't work.
Kindly help me this what am I doing wrong. Thanks!