Batch Numbers in Credit Note Layout

[Former Member]

Hi Guys,

I have the following query that was based on a query I was using for Invoices to bring n the Batch Numbers and Expiry Dates, I need this to function for Credit Notes, sadly everything appears to be working except it is not pulling in the Batch Number and Expiry date from the Invoice, can anyone point me in the right direction ?

Select T2.CardCode, T2.CardName, T3.PymntGroup, T0.DocDate, T0.DocDueDate,T0.DocCur, T0.PayToCode, T0.Address, T0.ShipToCode, T0.Address2, T0.DocNum, T0.DocEntry AS 'Doc Entry', T0.NumatCard, T0.Comments,T0.VatSum,T0.VatSumFC, T0.DocTotalFC, T0.Comments,T1.ItemCode, T1.Dscription, T1.FreeTxt, T1.Quantity, T1.Price, T1.PriceBefDi AS 'Price Before Discount', T1.LineTotal, T1.VatPrcnt, T1.VatGroup, T1.TotalFrgn, T0.DocTotal, T8.ItemCode, T8.DistNumber, T8.ExpDate, T1.BaseatCard, I1.BatchNum, T1.Height1, T1.Hght1Unit, T1.Height2, T1.Hght2Unit, T1.Width1, T1.Wdth1Unit, T1.Width2, T1.Wdth2Unit, T1.Length1, T1.Len1Unit, T1.Len1Unit, T1.Length2, T1.Len2Unit, T1.Volume, T1.VolUnit, T1.Weight1, T1.Wght1Unit, T1.Weight2, T1.Wght2Unit, T1.Factor1, T1.Factor2, T7.Linetotal AS 'INV3 Linetotal', T7.TotalFrgn, I1.Quantity AS 'Batch Qty', T1.BaseRef

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode=T0.CardCode INNER JOIN OCTG T3 ON T3.GroupNum=T2.GroupNum LEFT Join RIN1 T6 On T6.DocEntry=T1.BaseEntry and T6.LineNum=T1.BaseLine LEFT OUTER JOIN RIN3 T7 ON T0.DocEntry = T7.DocEntry

left join IBT1 I1 on T1.ItemCode=I1.ItemCode and T6.DocEntry=I1.BaseEntry and T6.ObjType=I1.BaseType

left join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber

Any help appreciated

Kind regards

Sean Martin

[Former Member]

Hi Sean.......

Try this.....

Select T2.CardCode, T2.CardName, T3.PymntGroup, T0.DocDate, T0.DocDueDate,T0.DocCur, T0.PayToCode, T0.Address, T0.ShipToCode, T0.Address2, T0.DocNum, T0.DocEntry AS 'Doc Entry', T0.NumatCard, T0.Comments,T0.VatSum,T0.VatSumFC, T0.DocTotalFC, T0.Comments,T1.ItemCode, T1.Dscription, T1.FreeTxt, T1.Quantity, T1.Price, T1.PriceBefDi AS 'Price Before Discount', T1.LineTotal, T1.VatPrcnt, T1.VatGroup, T1.TotalFrgn, T0.DocTotal, T8.ItemCode, T8.DistNumber, T8.ExpDate, T1.BaseatCard, I1.BatchNum, T1.Height1, T1.Hght1Unit, T1.Height2, T1.Hght2Unit, T1.Width1, T1.Wdth1Unit, T1.Width2, T1.Wdth2Unit, T1.Length1, T1.Len1Unit, T1.Len1Unit, T1.Length2, T1.Len2Unit, T1.Volume, T1.VolUnit, T1.Weight1, T1.Wght1Unit, T1.Weight2, T1.Wght2Unit, T1.Factor1, T1.Factor2, T7.Linetotal AS 'INV3 Linetotal', T7.TotalFrgn, I1.Quantity AS 'Batch Qty', T1.BaseRef
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode=T0.CardCode INNER JOIN OCTG T3 ON T3.GroupNum=T2.GroupNum LEFT Join INV1 T6 On T6.DocEntry=T1.BaseEntry and T6.LineNum=T1.BaseLine LEFT OUTER JOIN RIN3 T7 ON T0.DocEntry = T7.DocEntry
left join IBT1 I1 on T1.ItemCode=I1.ItemCode and T6.DocEntry=I1.BaseEntry and T6.ObjType=I1.BaseType
left join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber

Regards,

Rahul

[Former Member]

Hi Rahul,

Thanks for the post but the result is still the same, I am not getting the batch numbers through from the Invoice, any further thoughts ?

Kind regards

Sean

[Former Member]

Change I1.BatchNum=T8.DistNumber to I1.BatchNum=T8.BatchNumber

[Former Member]

Hi Sean.......

If you have done Delivery also then it may not be the query.

You have to make slight change in the query......

Select T0.CardCode, T0.CardName, T2.PymntGroup, T0.DocDate, T0.DocDueDate,T0.DocCur, T0.PayToCode, T0.Address, T0.ShipToCode, T0.Address2, T0.DocNum, T0.DocEntry AS 'Doc Entry', T0.NumatCard, T0.Comments,T0.VatSum,T0.VatSumFC, T0.DocTotalFC, T0.Comments,T1.ItemCode, T1.Dscription, T1.FreeTxt, T1.Quantity, T1.Price, T1.PriceBefDi AS 'Price Before Discount', T1.LineTotal, T1.VatPrcnt, T1.VatGroup, T1.TotalFrgn, T0.DocTotal, T8.ItemCode, T8.DistNumber, T8.ExpDate, I1.BatchNum, T1.Height1, T1.Hght1Unit, T1.Height2, T1.Hght2Unit, T1.Width1, T1.Wdth1Unit, T1.Width2, T1.Wdth2Unit, T1.Length1, T1.Len1Unit, T1.Len1Unit, T1.Length2, T1.Len2Unit, T1.Volume, T1.VolUnit, T1.Weight1, T1.Wght1Unit, T1.Weight2, T1.Wght2Unit, T1.Factor1, T1.Factor2, T7.Linetotal AS 'RIN3 Linetotal', T7.TotalFrgn, I1.Quantity AS 'Batch Qty', T1.BaseRef

FROM ORIN T0 Inner Join RIN1 T1 On T0.DocEntry=T1.DocEntry INNER JOIN INV1 T20 ON T1.BaseEntry = T20.DocEntry Inner Join OINV T21 On T20.DocEntry=T21.DocEntry INNER JOIN OCTG T2 ON 
T0.GroupNum = T2.GroupNum LEFT Join RIN12 T3 On T3.DocEntry=T0.DocEntry LEFT JOIN OITM T4 On T4.ItemCode=T1.ItemCode LEFT JOIN OCHP T5 On T4.ChapterID=T5.AbsEntry
Inner Join DLN1 T6 On T6.DocEntry=T20.BaseEntry and T6.LineNum=T20.BaseLine 
left outer join IBT1 I1 on T1.ItemCode=I1.ItemCode   and (T6.DocEntry=I1.BaseEntry and T6.ObjType=I1.BaseType)
left outer join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber

Hope this will help you......

Regards,

Rahul

[Former Member]

Hi,

LEFT Join RIN1 T6 is not correct. Try INV1.

Thanks,

Gordon

[Former Member]

Hi Gordon,

Changed as you suggested but still do not get the Batch Numbers in the Credit Note, have the Same Problem with a Delivery Note Version I am working on as well. Any further thoughts ?

Kind regards

Sean