Skip to Content
May 25 at 09:13 AM

System Inventory Days of Supply by Warehouse


Hi All

This is standard System Query available in Hana Database

Please help to explain the formula for Days of Inventory ("Open Stock" + "Close Stock")/2*365/"Issued Qty")?

What and where is the Open Stock and Closing Stock Days?

select "ItemCode" as "Item Code", "WhsCode" as "Warehouse Code", "WhsName" as "Warehouse Name", "Issued Qty" as "Issued Quantity", "Open Stock" as "Open Stock", "Close Stock" as "Closed Stock", ("Open Stock" + "Close Stock")/2*365/"Issued Qty" as "Days of Inventory" from (select T0."ItemCode", T0."LocType", T1."WhsCode", T1."WhsName", T0."Issued Qty", IFNULL(T2."OpenStock", 0) as "Open Stock", IFNULL(T3."CloseStock", 0) as "Close Stock" from (Select "LocType", "LocCode", "ItemCode", Sum("OutQty") as "Issued Qty" From "OIVL" Where "DocDate" BETWEEN ADD_DAYS(CURRENT_DATE, -364) and CURRENT_DATE and "OutQty" > 0 Group by "LocType", "LocCode", "ItemCode" ) T0 inner join OWHS T1 on T0."LocCode" = T1."WhsCode" left outer join (Select "LocType", "LocCode", "ItemCode", Sum("InQty" - "OutQty") as "OpenStock" From "OIVL" Where "DocDate" <= ADD_DAYS(CURRENT_DATE, -364) Group by "LocType", "LocCode", "ItemCode") T2 on T0."ItemCode" = T2."ItemCode" and T0."LocType" = T2."LocType" and T0."LocCode" = T2."LocCode" left outer join (Select "LocType", "LocCode", "ItemCode", Sum("InQty" - "OutQty") as "CloseStock" From "OIVL" Where "DocDate" <= CURRENT_DATE Group by "LocType", "LocCode", "ItemCode") T3 on T0."ItemCode" = T3."ItemCode" and T0."LocType" = T3."LocType" and T0."LocCode" = T3."LocCode")