cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member185682
Active Contributor
0 Kudos

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

Answers (1)

Answers (1)

0 Kudos

Is it possible to extend this query in a way such that i can see

a) which of my serial numbers have already been delivered to the customer

b) which of my serial numbers have eventually been returnd by the customer

Thanks