Skip to Content

Stock in bin location on a date in the past

Hello,

I'm trying to achieve the following for SAP B1: get a stock report in different bin locations for a date in the past. I already found 2 queries for either of these problems:
1. SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand'

FROM OINM T0

JOIN OWHS T1 ON T1.WhsCode=T0.Warehouse

WHERE T0.DocDate <= [%0]

GROUP BY T1.WhsName,T0.ItemCode

Having SUM(T0.InQty-T0.OutQty) > 0

Which gives me the stock on a date in the past.

2. select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as "On Hand", t2.BinCode, t2.SL1Code, t2.SL2Code, t0.WhsCode
from OITW T0

inner join OITM t1 on t0.ItemCode = t1.ItemCode
inner join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode
inner join OBIN T2 on t2.AbsEntry = t3.BinAbs

order by t2.SL1Code, t2.SL2Code, t0.ItemCode
Which gives me the stock on a particular bin location

Somehow these 2 queries should be combined (i think) to make my report. However, lacking some knowledge of SQL I'm having trouble doing this.

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 18 at 01:45 PM

    Hello.

    Try this Query, Fisrt part por batch Items, second par for not batch Items.

    SELECT T2.ItemCode

    , T6.BinCode AS "Ubicación"

    , T5.DistNumber AS "Lote"

    ,SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))AS "Cantidad 2"

    FROM OILM T2

    INNER JOIN ILM1 T3 ON (T2.[MessageID] = T3.[MessageID])-- Tabla Numero de Serie y Lotes de Log Inventario

    INNER JOIN OBTL T4 -- Tabla Log de Ubicaciones

    ON ( T2.MessageID = T4.MessageID

    AND T3.[MdAbsEntry] = T4.[SnBMDAbs]

    )

    INNER JOIN OBTN T5 ON (T3.[MdAbsEntry] = T5.[AbsEntry])-- Tabla Lotes

    INNER JOIN OBIN T6 ON (T4.BinAbs = T6.AbsEntry)-- Tabla Ubicaciones

    INNER JOIN OITM T7 ON (T2.ItemCode = T7.ItemCode)-- Tabla Articulos

    WHERE (1=1)

    AND(T2.DocDate <='[%1]')

    AND(T7.ManBtchNum ='Y')

    GROUP BY

    T2.[ItemCode]

    , T5.[DistNumber]

    , T6.[BinCode]

    HAVING (SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))<> 0)

    UNION ALL

    SELECT T2.ItemCode

    , T6.BinCode AS "Ubicación"

    ,NULL AS "Lote"

    ,SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))AS "Cantidad 2"

    FROM OILM T2

    INNER JOIN OBTL T4 -- Tabla Log de Ubicaciones

    ON ( T2.MessageID = T4.MessageID

    )

    INNER JOIN OBIN T6 ON (T4.BinAbs = T6.AbsEntry)-- Tabla Ubicaciones

    INNER JOIN OITM T7 ON (T2.ItemCode = T7.ItemCode)-- Tabla Articulos

    WHERE (1 = 1)

    AND(T2.DocDate <='[%1]')

    AND(T7.ManBtchNum ='N')

    GROUP BY

    T2.[ItemCode]

    , T6.[BinCode]

    HAVING (SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))<> 0)

    ORDERBY 1,2,3

    Add comment
    10|10000 characters needed characters exceeded