Skip to Content
0

Stock in bin location on a date in the past

Sep 25, 2017 at 12:40 PM

67

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Daniel Radio Pina Apr 18 at 01:45 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded