How to calculate opening balance value

[former_member275826]

Hello,

I want to calculate opening balance value by item groupwise.

Please help me

[KennedyT21 · Accepted solution]

Hi Pravin...

Try This simple query with Item Group name wise

select  t2.ItmsGrpNam,SUM(t0.inqty-t0.OutQty),Sum(t0.TransValue) from OINM t0

inner join oitm t1 on t1.ItemCode=t0.itemcode

inner join OITB t2 on t1.ItmsGrpCod=t2.ItmsGrpCod

where TransType in (310000001,-2)

group by t2.ItmsGrpNam

Regards

Kennedy

[Former Member]

Hi,

  Try this....

select a.ItmsGrpNam as itemgroup,sum(a.ob)OB,sum(a.inqty) inqty

,sum(a.outqty)outqty,sum((a.ob)+(a.balance)) Balance from

(select 0 as OB,t1.ItmsGrpNam,

(inqty) inqty,(outqty)outqty,(inqty-outqty) as balance

from oinm o1 (nolock)

inner join oitm t0 (nolock) on o1.ItemCode=t0.ItemCode

left outer join OITG t1 (nolock) on t0.ItmsGrpCod =t1.ItmsTypCod 

where

o1.docdate>='[%0]' and o1.docdate<='[%1]'

union all

select (inqty-outqty) as OB,ItmsGrpCod,0 as inqty,0 as outqty,0 as balance

from oinm o1(nolock)

inner join oitm t0 (nolock) on o1.ItemCode=t0.ItemCode

left outer join OITG t1 on t0.ItmsGrpCod =t1.ItmsTypCod

and o1.docdate <'[%0]'

)A

group by a.ItmsGrpNam

Regards,

Priya

[former_member183818]

You are not able to get opening balance through MB5B

[former_member275826]

Thanx Kenedy problem solved.

Hi Ranjit,

What is mean by MB5B?

[former_member183818]

You are not able to get opening balance through MB5B

[KennedyT21]

Hi PRAVIN GHADGE...

Go to Inventory --> Inventory Report --> Inventory Posting List

Here select From and To Date, Warehouse

Click on Expand button, here left side select Initial Quantity check box.

So, display only Item Opening Balance with Price.

Or

Try This

Please try this........

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Whse nvarchar(10)

select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'

select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'

select @Whse = Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]'

Select @Whse as 'Warehouse', a.Itemcode, max(a.Dscription) as ItemName,

(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM,

sum(a.OpeningBalance) as OpeningBalance, sum(a.INq) as 'IN', sum(a.OUT) as OUT,

((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing

from( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))

as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1

Where N1.DocDate < @FromDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,

N1.Dscription Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,

sum(N1.inqty) , 0 as OUT From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate

and N1.Inqty >0 and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription

Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT

From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0

and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1

where a.ItemCode=I1.ItemCode

Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

[former_member275826]

Hi Kenedy thanx for reply

But how should i get opening balance value??

[KennedyT21]

Hi Pravin...

Which query you are trying...

Try This

select  t2.ItmsGrpNam,SUM(t0.inqty-t0.OutQty),Sum(t0.TransValue) from OINM t0

inner join oitm t1 on t1.ItemCode=t0.itemcode

inner join OITB t2 on t1.ItmsGrpCod=t2.ItmsGrpCod

where TransType in (310000001,-2)

group by t2.ItmsGrpNam

Regards

Kennedy