How to query for bin location/warehouse history for item?

[s_schmidt92]

Warehoues in SAP Business One either use bin locations or not. I need to know the stock history for a specific item in a specific bin. I'm interested when stock for item in that specific bin was at f.e. 1x item A1 in location WH1.L1 at which document caused/resulted in that stock amout.

Same for the case that the item is in a warehouse that is not organized by stock locations.

I use the following tables to calculate the stock amount at a specific inventory log message.

Case bin locations enabled:

1. OIVL - Warehouse Journal (InQty, OutQty)
2. OWHS - Warehouses
3. OBTL - Bin Transaction log
4. OBIN - Bin Locations

Case bin locations disabled:

1. OIVL - Warehouse Journal (InQty, OutQty)
2. OWHS - Warehouses

As far as i can see this catches all inventory transactions besides a stock transfer inside a single warehouse (OIVL stays untouched). That is my problem here. Is there an easy way to sum up stock amount (InQty/OutQty) per item/per bin location/at transaction? Now i would ressort to using OILM and check the OILM-ActionType and tables OIVL and OBTL in addition.

Thanks for any hint.

[SonTran · Accepted solution]

Hi,

For stock movement, you can refer to tables as following:

OITL, ITL1, OILM: transaction log

OBTN: batch number

OBIN: bin location

OIVL, IVL1: inventory value

You can have a look at some queries in my forum to see if they are helpful or not.

• Query for stock by batch and bin location
• Query for stock movement

Hope this helps,

Son Tran

[s_schmidt92 · Accepted solution]

Hi SonTran, i will look into your suggestions and accept the solution afterwards. Thank you!

[s_schmidt92 · Accepted solution]

In addition to that it was very helpful to have a look at the definition of the view OINM. It shows how a join from TransSeq to MessageID is supposed to look.