Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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.

http://i60.tinypic.com/23sw7rp.jpg

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

http://i60.tinypic.com/14e0unk.jpg

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).

http://i59.tinypic.com/2qv4aqw.jpg

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

http://i61.tinypic.com/nxrzhg.jpg

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

Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question