Skip to Content
0

Inventory Report with Batch Numbers And Prices Managed By Warehouses

Oct 12, 2017 at 11:03 AM

118

avatar image

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

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

1 Answer

Mohd Danish Nov 28, 2017 at 07:34 AM
0

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;

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Mohd,

Thank you for the query that you have provided, however, it does not provide me with Batch Numbers.

Kind regards,

Vinolan

0