cancel
Showing results for 
Search instead for 
Did you mean: 

Delivery Batches And Serial Numbers

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member201110
Active Contributor
0 Kudos

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

Answers (1)

Answers (1)

edy_simon
Active Contributor
0 Kudos

Hi Doug,

Aside from  ,

for Drafts, it is stored in DRF16 table.

Regards

Edy