Skip to Content
avatar image
Former Member

Link to Serial Numbers for Sales Order Lines (SAP Business One)

I am trying to write a simple query that will show me the serial numbers allocated to the lines on my sales order. My problem is that I can't find either the correct serial number table or something else that will link the two. I am looking at ORDR, RDR1, OSRD, OSRI, OSRN and OSRQ. I can link the Order to OSRD, but that link is one to one (by line) and doesn't show Serial Numbers. I can't find the link from RDR1 Line to a Serial Number Line or from OSRD to Serial Number Lines.

If I have an order with one line, for a qty of 3 and three serial numbers have been allocated, I want the query to return three lines, with basic line data and the individual/unique serial numbers each.

Thanks for any help.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 01, 2017 at 10:11 PM

    Hi Vaughn,

    Try this query:

    SELECT 
    	T0.DocEntry,
    	T1.ItemCode,
    	T1.Dscription,
    	CASE WHEN T4.Quantity IS NOT NULL THEN T4.Quantity ELSE T1.Quantity END AS 'Qty',
    	ISNULL(T5.DistNumber, T6.DistNumber) AS 'Serial/Batch Number',
    	ISNULL(T5.SysNumber, T6.SysNumber) AS 'Serial/Batch Id'
    FROM
    	ORDR T0 
    	INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
    	INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
    	LEFT JOIN OITL T3 ON T3.DocType = T0.ObjType AND T3.DocEntry = T0.DocEntry AND T3.DocLine= T1.LineNum
    	LEFT JOIN ITL1 T4 ON T4.LogEntry = T3.LogEntry
    	LEFT JOIN OBTN T5 ON T5.ItemCode = T4.ItemCode AND T5.SysNumber = T4.SysNumber AND T2.manbtchnum = 'Y'
    	LEFT JOIN OSRN T6 ON T6.ItemCode = T4.ItemCode AND T6.SysNumber = T4.SysNumber AND T2.mansernum = 'Y'
    ORDER BY
    	T0.DocEntry
    

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded