8 Replies Latest reply: Oct 17, 2012 2:31 PM by Priya Lakshmanan

# How to calculate opening balance value

Currently Being Moderated

Hello,

I want to calculate opening balance value by item groupwise.

• ###### Re: How to calculate opening balance value
Currently Being Moderated

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

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

• ###### Re: How to calculate opening balance value
Currently Being Moderated

Hi Kenedy thanx for reply

But how should i get opening balance value??

• ###### Re: How to calculate opening balance value
Currently Being Moderated

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

• ###### Re: How to calculate opening balance value
Currently Being Moderated

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

• ###### How to calculate opening balance value
Currently Being Moderated

You are not able to get opening balance through MB5B

• ###### How to calculate opening balance value
Currently Being Moderated

You are not able to get opening balance through MB5B

• ###### Re: How to calculate opening balance value
Currently Being Moderated

Thanx Kenedy problem solved.

Hi Ranjit,

What is mean by MB5B?

• ###### Re: How to calculate opening balance value
Currently Being Moderated

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