Skip to Content

FIFO Stock value for report/query


I need to get the stock value for our system to show in queries and reports. I'm currently using the below code but this can take between 15 - 90 seconds to run (we do seem to have an I/O bottleneck on our system which our support company says we need to upgrade our hardware to fix, but we're not in a position to do that at present). Is there a more efficient way I can get this value quickly ?

Select SUM(TransValue) FROM 

(Select SUM(R0.TransValue) TransValue FROM OINM R0 with (no lock) 

INNER JOIN OITW R1 with (no lock) ON R0.ItemCode=R1.ItemCode AND R0.Warehouse=R1.WhsCode 

WHERE R0.DocDate<=CONVERT(date,GetDate(),113) GROUP BY R0.ItemCode 

HAVING SUM(R0.InQty-R0.OutQty)<> 0)T0
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 01, 2018 at 09:55 AM

    How about something like this? We are using perpetual inventory and item cost by warehouse, this query shows warehouse status and value on a given date. Posting date is mandatory, item code, item group and warehouse are optional filters.

    Note: this is HANA, adjust it for SQL

    SELECT T0."ItemCode" AS "Item Code", T1."ItemName" AS "Item Name", T2."ItmsGrpNam" AS "Item Group", T0."LocCode" AS "Whse", SUM(T0."InQty"-T0."OutQty") AS "In Stock Qty", SUM(T0."SumStock") AS "In Stock Value"
    FROM OIVL T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"
    WHERE T0."DocDate" <=[%1] AND (T0."ItemCode"=[%2] OR [%2] = '') AND (T2."ItmsGrpNam"=[%3] OR [%3] = '') AND (T0."LocCode"=[%5] OR [%5] = '')
    GROUP BY T0."ItemCode", T1."ItemName", T2."ItmsGrpNam", T0."LocCode"
    ORDER BY T0."ItemCode" asc, T0."LocCode" asc

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01, 2018 at 10:08 AM


    I would not use the OINM because is a view to mantain the compatibility with previous versions of SBO.

    I would advice you to use the new tables of log inventory: OILM, OITL, OIVL.

    Kind regards


    Add comment
    10|10000 characters needed characters exceeded