Skip to Content

Stock in bin location on a date in the past

Sep 25, 2017 at 12:40 PM


avatar image


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'


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.


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

0 Answers