Skip to Content
author's profile photo Former Member
Former Member

Inventory History Report

Hi SAP consultants,

I have a client that would like a report on inventory that shows the following information about their inventory. These are the required columns.

1. Opening balance as at a given date.

2. Total amount bought in the period.

3. Total amount sold in the period.

4. Closing balance at the end of the given period.

The report should display this information per item.

Kindly assist in generating this report.

Kind regards,

Andrew.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 20, 2009 at 10:25 AM

    Hi!

    Try this

    Declare @FromDate Datetime
    Declare @ToDate Datetime
    Declare @Group nvarchar(10)
     
    Set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')
    Set @ToDate = (Select max(S1.Docdate) from OINM s1 where S1.Docdate <='[%1]')
     Set @Group = (Select Max(s2.ItmsGrpCod) from OITB S2 Where S2.ItmsGrpNam = '[%2]')
    
    Select 
    a.Itemcode,
    a.Dscription,
    sum(a.[Opening Balance]) as [Opening Balance],
    sum(a.[IN]) as [IN],
    Sum(a.OUT) as OUT,
    ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(
     
    Select
    N1.Itemcode,
    N1.Dscription,
    (sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
    0 as [IN],
    0 as OUT
     
    From OINM N1
    Where
    N1.DocDate < @FromDate
    Group By
    N1.ItemCode,N1.Dscription
     
    Union All
    select
    N1.Itemcode,
    N1.Dscription,
    0 as [Opening Balance],
    sum(N1.inqty) as [IN],
    0 as OUT
     
    From OINM N1
    Where
    N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
    N1.Inqty >0
    Group By
    N1.ItemCode,N1.Dscription
     
    Union All
    select
    N1.Itemcode,
    N1.Dscription,
    0 as [Opening Balance],
    0 as [IN],
    sum(N1.outqty) as OUT
     
    From OINM N1
    Where
    N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
    N1.OutQty > 0
    Group By
    N1.ItemCode,N1.Dscription)a, OITM I1
    where
    a.ItemCode=I1.ItemCode and
    I1.ItmsGrpCod = @Group
     
    Group By
    a.Itemcode,a.Dscription

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 20, 2009 at 06:09 PM

    Try this one, it may be faster especially when more than one user need it:

    Declare @FromDate Datetime
    Declare @ToDate Datetime
    Declare @Group nvarchar(10)
     
    Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')
    Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')
    Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')
     
    Select 
    a.Itemcode,
    max(a.Dscription),
    sum(a.[Opening Balance]) as [Opening Balance],
    sum(a.[IN]) as [IN],
    sum(a.OUT) as OUT,
    ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing
    from(
    Select
    N1.Itemcode,
    N1.Dscription,
    (sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
    0 as [IN],
    0 as OUT
    
    From dbo.OINM N1
    Where
    N1.DocDate < @FromDate
    Group By
    N1.ItemCode,N1.Dscription
     
    Union All
    select
    N1.Itemcode,
    N1.Dscription,
    0 as [Opening Balance],
    sum(N1.inqty) as [IN],
    0 as OUT
     
    From dbo.OINM N1
    Where
    N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
    N1.Inqty >0
    Group By
    N1.ItemCode,N1.Dscription
     
    Union All
    select
    N1.Itemcode,
    N1.Dscription,
    0 as [Opening Balance],
    0 as [IN],
    sum(N1.outqty) as OUT
     
    From dbo.OINM N1
    Where
    N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
    N1.OutQty > 0
    Group By
    N1.ItemCode,N1.Dscription) a, dbo.OITM I1
    where
    a.ItemCode=I1.ItemCode and
    I1.ItmsGrpCod = @Group 
    Group By
    a.Itemcode
    Having sum(a.[Opening Balance]) + sum(a.[IN]) + sum(a.OUT) > 0
    Order By a.Itemcode
    

    I have made sure only one line for one item in case you changed your item descriptions during the time frame. Also the all 0 items have been taken out.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 20, 2009 at 09:14 AM

    Hi!

    Try this

    Declare @FromDate Datetime
    Declare @ToDate Datetime
    
    Set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')
    Set @ToDate = (Select max(S1.Docdate) from OINM s1 where S1.Docdate <='[%1]')
    
    Select 
    a.Itemcode,
    sum(a.[Opening Balance]) as [Opening Balance],
    sum(a.[IN]) as [IN],
    Sum(a.OUT) as OUT,
    ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(
    
    Select
    N1.Itemcode,
    sum(N1.Transvalue) as [Opening Balance],
    0 as [IN],
    0 as OUT
    
    From OINM N1
    Where
    N1.DocDate < @FromDate
    Group By
    N1.ItemCode
    
    Union All
    select
    N1.Itemcode,
    0 as [Opening Balance],
    sum(N1.TransValue) as [IN],
    0 as OUT
    
    From OINM N1
    Where
    N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
    N1.Inqty >0
    Group By
    N1.ItemCode
    
    Union All
    select
    N1.Itemcode,
    0 as [Opening Balance],
    0 as [IN],
    sum(N1.Transvalue) as OUT
    
    From OINM N1
    Where
    N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
    N1.OutQty > 0
    Group By
    N1.ItemCode)a
    
    Group By
    a.Itemcode

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.