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

How to calculate opening balance value

PRAVIN GHADGE
Currently Being Moderated

Hello,

 

I want to calculate opening balance value by item groupwise.

 

 

Please help me

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

    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

  • Re: How to calculate opening balance value
    Kennedy T
    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
    Ranjit John
    Currently Being Moderated

    You are not able to get opening balance through MB5B

  • How to calculate opening balance value
    Ranjit John
    Currently Being Moderated

    You are not able to get opening balance through MB5B

  • Re: How to calculate opening balance value
    Priya Lakshmanan
    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

Actions