on 08-13-2015 9:00 PM
I have looked for this on the forums and have been unable to find anything up to this point. Would someone be able to give me a SQL select query that would give me the batches and/or serial numbers that were on a delivery?
Thanks
Hi Doug,
You don't mention what version of SBO you are working with. The way that batch and serial numbers are stored has changed over time. The following query should work on SBO 8.8x or 9.x:
select T0.DocEntry, T0.DocNum, T0.CardCode, T0.CardName,
T1.ItemCode, T1.Dscription as ItemName, T1.Quantity as LineQty, T3.AllocQty as BatchQty, T4.DistNumber as BatchNum
from ODLN T0
inner join DLN1 T1 on T0.DocEntry = T1.DocEntry
inner join OITM T2 on T1.ItemCode = T2.ItemCode
inner join (select S0.DocEntry, S0.DocLine, S1.SysNumber, -sum(S1.AllocQty) as AllocQty from OITL S0 inner join ITL1 S1
on S0.LogEntry = S1.LogEntry where S0.DocType = 15 group by S0.DocEntry, S0.DocLine, S1.SysNumber) T3
on T1.DocEntry = T3.DocEntry and T1.LineNum = T3.DocLine
inner join OBTN T4 on T3.SysNumber = T4.SysNumber and T1.ItemCode = T4.ItemCode
where T1.Quantity > 0 and T2.ManBtchNum = 'Y'
and T0.DocEntry = 1
For serial numbered items, change the OBTN table to OSRN and T2.ManBtchNum = 'Y' to T2.ManSerNum = 'Y'
Kind Regards,
Owen
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.