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!

5

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.