Attempting to report on batch numbers (initially) within Delivery Notes

[Former Member]

Hi Everyone,

I am interesting in using SQL to determine which batch numbers (for an Item Code) are being allocated to Deliveries, Inventory Stock Transfers, etc.

On my system under 'Inventory Master Data' I can see that some items are managed by Batches (as shown in yellow highlighting below). Furthermore if I right click on an item that is managed by Batches I can select 'Batch Numbers Transaction Report' to show how batch numbers have been allocated, and subsequently used.

Clicking on 'Batch Number Transactions Report' shows a screen like the one below -

If I select one of the batches, e.g.: batch 1147, I can see a list of transactions related to that batch, and which direction stock moved in (for each transaction).

Looking at Delivery Note DN 414015 as an example produces the screen below -

I want to write a query (which initially just) looks at Delivery Notes and reports on batch number allocations related to each Delivery Note.

However I am uncertain of where to start; it is my belief that batch numbers reside within the table OBTN (Batch Numbers Master Data), within the field DistNumber. I have attempted to write a query before making this post, but results have been undesirable.

SELECT
T2.DocNum AS 'Delivery No.'
, T1.ItemCode AS 'Item Code'
, T4.DistNumber AS 'Batch No.'


FROM OITL T0
LEFT JOIN DLN1 T1 ON T1.DocEntry = T0.ApplyEntry AND T1.LineNum = T0.ApplyLine AND T0.ApplyType = 15
INNER JOIN ODLN T2 ON T2.DocEntry = T1.DocEntry
LEFT JOIN OBTQ T3 ON T3.AbsEntry = T0.DocEntry
LEFT JOIN OBTN T4 ON T4.AbsEntry = T3.AbsEntry

Any help here will be greatly appreciated.

Kind Regards,

David

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T4.[DistNumber] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry left join  OITL T2 on t1.docentry = T2.[ApplyEntry] and  T1.[LineNum]  = T2.[ApplyLine] and T2.[ApplyType] = 15 INNER JOIN ITL1 T3 ON T2.LogEntry = T3.LogEntry left join  OBTN T4 on T4.[ItemCode]  =  T3.[ItemCode]  and T3.[MdAbsEntry]  =  t4.[absentry] WHERE T0.Docnum = [%0] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T4.[DistNumber]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thank you Nagarajan the report that you kindly provided has me on the right track...