History of Available Stock in SAP B1

How can I find what was the available stock on a specific date in the past?

I know how to get OnHand for any date in the past:

 SELECT LocType, LocCode, ItemCode, SUM(InQty-OutQty) [OnHand] From OIVL Where DocDate <= '7/7/2017' AND ITEMCODE = 'xyz' Group by LocType, LocCode, ItemCode 

But this does not account for what was committed. It shows what was on hand, but not what was actually available (OnHand - Committed = Available). So, how could I get to this "Available" number for dates in the past?

Thanks!

1 Answer

In case this helps anyone else.... here is an example of how to get your item quantity and cost history in SAP BusinessOne. Pass the date and item you want to see history for into the function. If you omit item code it returns all items.

CREATE FUNCTION [dbo].[ufnStockAndCostHistory] ( @EndDate DATETIME, @ItemCode NVARCHAR(255) = 'All' ) RETURNS TABLE AS RETURN ( WITH Cost AS ( SELECT OINM.TransNum, OINM.ItemCode, OINM.Warehouse, OINM.Balance, OINM.CreateDate FROM OINM (NOLOCK) JOIN OITM (NOLOCK) ON OITM.ItemCode = OINM.ItemCode JOIN OITB (NOLOCK) ON OITM.ItmsGrpCod=OITB.ItmsGrpCod WHERE OINM.CreateDate <= @EndDate ), FinalCostByWarehouse AS ( SELECT * FROM Cost WHERE TransNum = ( SELECT MAX(TransNum) FROM Cost sub WHERE Cost.ItemCode = sub.ItemCode AND Cost.Warehouse = sub.Warehouse) ), Quantity AS ( SELECT OINM.ItemCode, OINM.Warehouse, SUM(OINM.InQty) - SUM(OINM.OutQty) [Qty] FROM OINM (NOLOCK) JOIN OITM (NOLOCK) ON OITM.ItemCode = OINM.ItemCode JOIN OITB (NOLOCK) ON OITM.ItmsGrpCod=OITB.ItmsGrpCod WHERE OINM.CreateDate <= @EndDate group by OINM.ItemCode, OINM.Warehouse ) SELECT OITW.ItemCode, OITW.WhsCode, c.Balance [Cost], q.Qty [Quantity] FROM OITW LEFT JOIN FinalCostByWarehouse c ON OITW.ItemCode = c.ItemCode AND OITW.WhsCode = c.Warehouse LEFT JOIN Quantity q ON OITW.ItemCode = q.ItemCode AND OITW.WhsCode = q.Warehouse WHERE (OITW.ItemCode = @ItemCode OR @ItemCode = 'All') AND (ISNULL(c.Balance,0) > 0 OR ISNULL(q.Qty,0) > 0) )

Usage would look like:

SELECT * FROM ufnStockAndCostHistory('9/15/2017','B107A-12HB')