OINM query as Audit report

[ivan_leonardo]

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

[former_member197621 · Accepted solution]

Hi,

Try this below query and let me know your feedback,

Note: First try without "HAVING" condition. If the value is match then try with HAVING condition.

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(Isnull(T0."InQty",0))- sum(Isnull(T0."OutQty",0)) AS "INSTOCK",
SUM(Isnull(T0.TransValue,0)) 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"

[ivan_leonardo · Accepted solution]

Case close, I change the table to OIVL and problem solved.

Thanks Prasanna for your info.

Rgds,

[kothandaraman_nagarajan · Accepted solution]

Hi,

Could post your modified query here? How did you get previous cumulative value in your query?

Thanks & Regards,

Nagarajan

[ivan_leonardo · Accepted solution]

Here is my Updated Query : give the same result as default inventory audit report

SELECT SUBSTRING(T0."LocCode",3,2) AS "DEPT",T0."ItemCode",T1."ItemName",SUM(T0."InQty") AS "IN QTY",SUM(T0."OutQty") AS "OUT QTY", SUM(T0."InQty"-T0."OutQty") AS "READY QTY", SUM(T0."SumStock" ) AS "READY VALUE" FROM OIVL T0

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

WHERE T0."DocDate" <= [%0]

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

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

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

[kothandaraman_nagarajan · Accepted solution]

Hi,

Thanks for posting modified query.

Regards,

Nagarajan