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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
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.