Attempting to report on batch numbers (initially) within Delivery Notes
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.
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.
Nagarajan K replied
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,