Need warehouse stock report

[Former Member]

Hi,

I need all warehouse stack report with g/L account(Format code) for particular day(31/03/2015).

Please provide me query for that.

Regards,

J.vigneshwaran

[Former Member · Accepted solution]

Thank you sir,

Date is not appear in table please i need date in the report. It can be made it.

Thanks

[former_member212181 · Accepted solution]

Hi Vigneshwaran,

Please try to communicate all of your requirements in one stretch

Please try below query

Declare @DocDate DateTime

Select @DocDate = Max(AA.DocDate) from OINM AA Where AA.DocDate<=[%0]

Set @DocDate = '[%0]'

SELECT T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName

,T2.FormatCode,T2.AcctName--, T0.InvntAct

,@DocDate[Stock As on Date]

,Sum(T0.[InQty]-T0.[OutQty])[Qty]

,Sum(T0.[TransValue]) [Value]

FROM OINM T0 

  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

  Left Outer Join OACT T2 on T0.InvntAct = T2.AcctCode

  Inner join OWHS T3 on T0.Warehouse = T3.WhsCode

WHERE T0.[DocDate] <=@DocDate

GROUP BY T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName--, T0.InvntAct

,T2.FormatCode ,T2.AcctName

Having ABS(Sum(T0.[InQty]-T0.[OutQty]))+ABS(Sum(T0.[TransValue]))>0

Thanks

Unnikrishnan

[Former Member]

Thank you Unnikrishnan sir,

I got it.

sorry,Next time i will communicate with full requirements if needed.

Please make me connect with you scn community.

Thank you

Regards,

J.vigneshwaran

[Former Member]

Hi Unnikrishnan,

Warehouse name also need in this table.

Regards,

J.vigneshwaran

[former_member212181]

Hi Vigneshwaran,

Please try below query, I made necessary changes to bring warehouse name

SELECT T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName

,T2.FormatCode,T2.AcctName--, T0.InvntAct

,Sum(T0.[InQty]-T0.[OutQty])[Qty]

,Sum(T0.[TransValue]) [Value]

FROM OINM T0 

  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

  Left Outer Join OACT T2 on T0.InvntAct = T2.AcctCode

  Inner join OWHS T3 on T0.Warehouse = T3.WhsCode

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

GROUP BY T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName--, T0.InvntAct

,T2.FormatCode ,T2.AcctName

Having ABS(Sum(T0.[InQty]-T0.[OutQty]))+ABS(Sum(T0.[TransValue]))>0

Thanks

Unnikrishnan

[former_member212181]

Hi Vigneshwaran,

Please try below query. I made necessary changes to bring warehouse name

SELECT T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName

,T2.FormatCode,T2.AcctName--, T0.InvntAct

,Sum(T0.[InQty]-T0.[OutQty])[Qty]

,Sum(T0.[TransValue]) [Value]

FROM OINM T0 

  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

  Left Outer Join OACT T2 on T0.InvntAct = T2.AcctCode

  Inner join OWHS T3 on T0.Warehouse = T3.WhsCode

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

GROUP BY T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName--, T0.InvntAct

,T2.FormatCode ,T2.AcctName

Having ABS(Sum(T0.[InQty]-T0.[OutQty]))+ABS(Sum(T0.[TransValue]))>0

Thanks

Unnikrishnan

[Former Member]

Thanks Unnikrishnan Balan for reply sir,

Its coming but i need with date and need inventory account in Format code sir, and also stock quantity showing in negative please can correct it sir. It is possible to show posting date from to to date when giving date.

Regards,

J.vigneshwaran

[former_member212181]

Hi Vignesh,

Please try below query

SELECT T0.[ItemCode], T1.[ItemName], T0.[Warehouse]

,T2.FormatCode,T2.AcctName--, T0.InvntAct

,Sum(T0.[InQty]-T0.[OutQty])[Qty]

,Sum(T0.[TransValue]) [Value]

FROM OINM T0 

  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

  Left Outer Join OACT T2 on T0.InvntAct = T2.AcctCode

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

GROUP BY T0.[ItemCode], T1.[ItemName], T0.[Warehouse]--, T0.InvntAct

,T2.FormatCode ,T2.AcctName

Having ABS(Sum(T0.[InQty]-T0.[OutQty]))+ABS(Sum(T0.[TransValue]))>0

Thanks

Unnikrishnan