Skip to Content
0

History of Available Stock

Sep 07, 2017 at 10:15 PM

42

avatar image
Former Member

Hello,

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!

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

1 Answer

Best Answer
avatar image
Former Member Sep 15, 2017 at 04:53 PM
0

For anyone else who might need this. This will give you historical stock quantities and cost. Just pass in the item code and the date you want to check. If you omit item code it returns detail for 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 looks like:

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

Thanks,

Ian

Share
10 |10000 characters needed characters left characters exceeded