cancel
Showing results for 
Search instead for 
Did you mean: 

How to get batch onhand qty from bin location?

Former Member
0 Kudos

Hi all,

I'm working to get batch on hand quantity from bin location by sql query.

The bin location function is the new function in Business one version 9.

Does someone have the sql query to get the batch on hand qty from bin location?

Regards,

Joanne Lee

Accepted Solutions (0)

Answers (3)

Answers (3)

This query was useful to me

SELECT distinct OBIN.BinCode,OBTN.DistNumber,OBBQ.OnHandQty FROM OBBQ
LEFT JOIN obin ON OBIN.WhsCode = OBBQ.WhsCode AND OBIN.AbsEntry = OBBQ.BinAbs
LEFT JOIN OIBT ON OBBQ.ItemCode = OIBT.ItemCode AND OBBQ.WhsCode = OIBT.WhsCode
LEFT JOIN OBTN ON OBBQ.SnBMDAbs = OBTN.AbsEntry
where OBBQ.itemcode = '02101154'
and obtn.DistNumber in ('80T4814B7/1','80T4814B7#') AND OBBQ.OnHandQty > 0

former_member201110
Active Contributor
0 Kudos

Hi Joanne,

The following query will return a recordset of quantities for batch or serial number managed items in a specific bin location:

declare @WhsCode nvarchar(9), @ItemCode nvarchar(21), @BinLocation nvarchar(228)

set @WhsCode = '01'
set @ItemCode = 'B10000'
set @BinLocation = '01-ABC'

select
T0.BinAbs, T0.ItemCode, T2.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T4.AbsEntry,
T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode
from
OIBQ T0
inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
left outer join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode and (T2.AbsEntry is not null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
      where U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))
and T1.BinCode = @BinLocation
union all
select
T0.BinAbs, T0.ItemCode, T3.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T5.AbsEntry,
T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode
from
OIBQ T0
inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
left outer join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode and (T3.AbsEntry is not null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
      where U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))
and T1.BinCode = @BinLocation
union all
select
T0.BinAbs, T0.ItemCode, T0.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T4.AbsEntry,
T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode
from
OIBQ T0 inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
left outer join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode
and (T2.AbsEntry is null and T3.AbsEntry is null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
and T1.BinCode = @BinLocation
where
U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))

Kind Regards,

Owen

Former Member
0 Kudos

Hi Joanna,

Try This:

SELECT

 


T0

.ItemCode,


T0

.DistNumber AS BatchNumber,


T2

.Location,


T1

.Quantity


FROM OBTN AS T0


LEFT OUTER JOIN dbo.OBTQ AS T1 ON T0.ItemCode = T1.ItemCode AND T0.SysNumber = T1.SysNumber


INNER JOIN dbo.OBTW AS T2 ON T0.ItemCode = T2.ItemCode AND T0.SysNumber = T2.SysNumber AND T1.WhsCode = T2.WhsCode