Skip to Content
0
May 26, 2014 at 04:59 AM

OINM query as Audit report

121 Views

Hi,

I create a query from OINM table but the total value ("INSTOCK VALUE") is different as in default Inventory audit report ("Cummulative Value" field). it seems default audit report don't include GR PO cancelation. here is my query :

SELECT SUBSTRING(T0."Warehouse",3,2) AS "DEPT",T0."ItemCode", T1."ItemName", SUM(T0."InQty") AS "QTY IN", SUM(T0."OutQty") AS "QTY OUT",

SUM(T0."InQty"-T0."OutQty") AS "INSTOCK",

SUM((T0."InQty"*T0."CalcPrice")-(T0."OutQty"*T0."CalcPrice")) AS "INSTOCK VALUE"

FROM OINM T0

INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"

WHERE T0."DocDate" <= [%0] AND T1."validFor"='Y' AND T0."ItemCode"='1200285'

GROUP BY SUBSTRING(T0."Warehouse",3,2),T0."ItemCode", T1."ItemName"

HAVING SUM(T0."InQty"-T0."OutQty")>0

ORDER BY SUBSTRING(T0."Warehouse",3,2), T0."ItemCode"

any idea ?

Thanks