Skip to Content
0

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

Feb 01, 2017 at 08:20 PM

94

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
DIEGO LOTHER Feb 01, 2017 at 10:11 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded