Skip to Content

Inventory Report with Batch Numbers And Prices Managed By Warehouses

Good day Experts,

I am trying to write a query that provides me with the Item Code, Batch Number, Remaining Qty of Batch, Warehouse that the Batch Quantity is sitting in, and the Price of the Item in the warehouse since Item Cost is managed by warehouse.

However the total sum of the stock, must match to the inventory audit report, which means it must also have a date parameter.

This is the code that I have thus far:

 SELECT
	 DISTINCT 
	 T1."DistNumber", T3."ItemCode",
	 T2."ItemName",
	 T1."U_DSC",
         T0."WhsCode",
	 T0."Quantity" as "Quantity Remaining",
	 T4."AvgPrice", 
         (T0."Quantity" * T4."AvgPrice") AS "Total Value",
	 SUM(T5."Quantity") AS "Qty per Coil Number"
FROM OBTQ T0 
INNER JOIN OBTN T1 ON T0."MdAbsEntry" = T1."AbsEntry" 
AND T0."SysNumber" = T1."SysNumber" 
INNER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode" 
INNER JOIN OIVL T3 ON T2."ItemCode" = T3."ItemCode" 
LEFT JOIN OITW T4 ON T0."ItemCode" = T4."ItemCode" 
AND T0."WhsCode" = T4."WhsCode" 
INNER JOIN IBT1 T5 ON T2."ItemCode" = T5."ItemCode"
WHERE T0."Quantity" <> 0 AND T3."DocDate" <= [%0]
GROUP BY  T3."ItemCode",
	 T2."ItemName",
	 T1."U_DSC",
	 T0."Quantity",
	 T0."WhsCode",
	 T1."DistNumber",
	 T1."CreateDate",
	 T1."InDate",
	 T4."AvgPrice",

The total from this query gives me 551 million where the Inventory Audit Report gives me a figure of 545 million

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 28, 2017 at 07:34 AM

    try this

    BEGIN
    DECLARE dFromDate DATE;
    DECLARE dToDate DATE;



    SELECT MIN(T0."DocDate") INTO dFromDate FROM OINM T0 Where T0."DocDate" >= '[%0]';

    SELECT MAX(T1."DocDate") INTO dToDate FROM OINM T1 Where T1."DocDate" <= '[%1]';




    Select a."ItemCode", Max(a."Dscription") as "ItemName"
    ,sum(a."OpeningBalance") as "OpeningBalance",sum(a."INq") as "IN",sum(a."OUTq") as "OUT"
    ,((sum(a."OpeningBalance") + sum(a."INq")) - Sum(a."OUTq")) as "Closing"
    ,(Select i."InvntryUom" From OITM i Where i."ItemCode"=a."ItemCode") as "UOM",max(I1."LastPurPrc") as "Rate"
    ,max(I1."ItmsGrpCod") as "GrpCod", max(I1."FrgnName") as "SubGrpName", Max(G1."ItmsGrpNam")

    From
    (Select N1."ItemCode", N1."Dscription",(sum(N1."InQty")-sum(N1."OutQty")) as
    "OpeningBalance"
    ,0 as "INq", 0 as "OUTq"
    From OINM N1
    Where N1."DocDate" < :dFromDate and N1."TransType" <> 67
    Group By N1."ItemCode",N1."Dscription"
    Union All
    Select N1."ItemCode", N1."Dscription", 0 as "OpeningBalance",
    sum(N1."InQty"), 0 as "OUTq"
    From OINM N1
    Where N1."DocDate" >= :dFromDate and N1."DocDate" <= :dToDate and N1."InQty" >0 and N1."TransType" <> 67

    Group By N1."ItemCode",N1."Dscription"
    Union All
    Select N1."ItemCode", N1."Dscription", 0 as "OpeningBalance", 0 ,
    sum(N1."OutQty") as "OUTq"
    From OINM N1
    Where N1."DocDate" >= :dFromDate and N1."DocDate" <=:dToDate and N1."OutQty" > 0 and N1."TransType" <> 67
    Group By N1."ItemCode",N1."Dscription"
    )a
    , OITM I1
    Left Join OITB G1 On I1."ItmsGrpCod" = G1."ItmsGrpCod"
    Where a."ItemCode"=I1."ItemCode"
    Group By a."ItemCode" Having sum(a."OpeningBalance") + sum(a."INq") + sum(a."OUTq") > 0
    Order By a."ItemCode";
    END;

    Add comment
    10|10000 characters needed characters exceeded