Skip to Content
author's profile photo Former Member
Former Member

How to get batch onhand qty from bin location?

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Jun 13, 2013 at 08:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 13, 2013 at 02:26 PM

    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
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 20, 2020 at 12:56 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.