Skip to Content
avatar image
Former Member

History of Available Stock

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

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

    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

    Add comment
    10|10000 characters needed characters exceeded