on 02-01-2017 8:20 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
9 | |
8 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.