Hi. I americium trying to constitute a query for inventory counting wherever one privation it show the counted qty per batch for batch item:
but my codification here, one managed to get the counted qty from INC3 array but the information duplicated. Here is my codification and the outcome:
SELECT distinct
T0.ItemCode,
T5.ItemName,
T0.WhsCode,
T4.DistNumber,
--SUM(T3.OnHandQty) arsenic 'Batch Quantity',
SUM(T2.OnHandQty) arsenic 'Total Stock Bin',
--SUM(T0.OnHand) AS 'Total Stock Warehouse',
T1.BinCode, t1.AbsEntry arsenic 'BinEntry', t6.Quantity arsenic 'Batch Quantity'
FROM
OITW T0
INNER JOIN OBIN T1 ON T0.WhsCode = T1.WhsCode
INNER JOIN OIBQ T2 ON T2.WhsCode = T0.WhsCode AND T1.AbsEntry = T2.BinAbs AND T0.ItemCode = T2.ItemCode
INNER JOIN OBBQ T3 ON T3.ItemCode = T0.ItemCode AND T3.BinAbs = T1.AbsEntry AND T3.WhsCode = T2.WhsCode
INNER JOIN OBTN T4 ON T4.AbsEntry = T3.SnBMDAbs
INNER JOIN OITM T5 ON T5.ItemCode = T0.ItemCode
inner articulation INC3 t6 connected t5.ItemCode=t6.ItemCode
WHERE
T2.OnHandQty <> 0 and t0.WhsCode='P2' and t5.ManBtchNum='Y' and t0.ItemCode='MP0742SMN013 ' --and t4.DistNumber='309677-1' --and t1.BinCode='P2-GF1'
GROUP BY
T0.ItemCode, T5.ItemName, T0.WhsCode,
T0.WhsCode, T1.BinCode, T4.DistNumber, t1.AbsEntry,t6.Quantity