Query Qty not matching with Batch Inventory Transactions Report

[ramco1917]

Hi

In below query Qty is not matching with Batch Inventory Transactions Reports. Query does not show some Warehouses Data. Is there any other table to be linked or may be Opening Batch Quantities not coming.

SELECT T0."ItemCode", max(T4."ItemName") ,

T3."DistNumber",T3."InDate",T3."MnfDate", T3."ExpDate",

T2."WhsCode",

(Select Sum(A0."OnHandQty") from OBBQ A0 where A0."SnBMDAbs"  = Max(T3."AbsEntry") and A0."ItemCode" = T0."ItemCode" and A0."WhsCode" = T2."WhsCode") as Qty

FROM OIBQ T0  LEFT JOIN OBIN T1 ON T0."BinAbs" = T1."AbsEntry" and T0."WhsCode" = T1."WhsCode" LEFT JOIN OBBQ T2 ON T1."AbsEntry" = T2."BinAbs" and T0."ItemCode" = T2."ItemCode" LEFT JOIN OBTN T3 ON T2."SnBMDAbs" = T3."AbsEntry" and T0."ItemCode" = T3."ItemCode" left join OITm T4 on T4."ItemCode" = t0."ItemCode"

group by T0."ItemCode", T0."OnHandQty", T3."DistNumber" ,T2."WhsCode",T3."ExpDate", T3."MnfDate", T3."InDate"

Having sum(T2."OnHandQty") <>0

[Avis682Holmes]

Your query may miss some warehouse data or opening batch quantities. Ensure all warehouses are correctly referenced and check if any additional tables store opening batch invisalign treatment quantities. Here's a revised query: sql SELECT T0."ItemCode", MAX(T4."ItemName"), T3."DistNumber", T3."InDate", T3."MnfDate", T3."ExpDate", T2."WhsCode", (SELECT SUM(A0."OnHandQty") FROM OBBQ A0 WHERE A0."SnBMDAbs" = MAX(T3."AbsEntry") AND A0."ItemCode" = T0."ItemCode" AND A0."WhsCode" = T2."WhsCode") AS Qty FROM OIBQ T0 LEFT JOIN OBIN T1 ON T0."BinAbs" = T1."AbsEntry" AND T0."WhsCode" = T1."WhsCode" LEFT JOIN OBBQ T2 ON T1."AbsEntry" = T2."BinAbs" AND T0."ItemCode" = T2."ItemCode" LEFT JOIN OBTN T3 ON T2."SnBMDAbs" = T3."AbsEntry" AND T0."ItemCode" = T3."ItemCode" LEFT JOIN OITM T4 ON T4."ItemCode" = T0."ItemCode" GROUP BY T0."ItemCode", T0."OnHandQty", T3."DistNumber", T2."WhsCode", T3."ExpDate", T3."MnfDate", T3."InDate" HAVING SUM(T2."OnHandQty") <> 0 This might help include all relevant data.

[ramco1917]

Hi Avis, Which other table should be checked. Is there not a single table from where all Batch Wise Transactions should be got.