History of Available Stock in SAP B1

Question

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!

Answer 1

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')