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

Inventory Audit Report question - Underlying Table

I am trying to analyse an issue with the Inventory Audit report where the Cumulative Value for some items does not make sense.

I would like to analyse the data using the query manager but I can not determine which tables are used by SAP to generate this report,

I remember from the past there is a table that contains a line for each transaction for the item and one of the fiels is the runniing balance foe the item

Can any of you experts halp me with the table or tables names?

Thanks in advance!


Dana

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Nov 20, 2012 at 01:35 AM

    Hi Dana Colwell...

    If you want to make Inventory Audit Query then please refer OINM Table where you get all the inventory journal details.

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 20, 2012 at 01:40 AM

    Hi Dana Colwell...

    Try This query and modify according your need..

    Declare @FromDate Datetime

    Declare @ToDate Datetime

    Declare @Group nvarchar(10)

    Declare @Whse 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]')

    Set @Whse = (Select Max(s3.Warehouse) from dbo.OINM S3 Where S3.Warehouse = '[%3]' )

    Select

    @Whse as 'Warehouse',

    a.Itemcode,

    max(a.Dscription) as 'Description',MAX(a.Price) as 'Price',

    sum(a.[Opening Balance]) as [Opening Balance],

    sum(a.[IN]) as [Receipt],

    sum(a.OUT) as [Issue],

    ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing,

    ( MAX(a.Price) * ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) ) as ClosingValue

    from(

    Select

    N1.Warehouse,

    N1.Itemcode,

    N1.Dscription,N1.Price,

    (sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],

    0 as [IN],

    0 as OUT

    From dbo.OINM N1

    Where

    N1.DocDate < @FromDate and N1.Warehouse = @Whse

    Group By

    N1.Warehouse,N1.ItemCode,N1.Dscription,N1.Price

    Union All

    select

    N1.Warehouse,

    N1.Itemcode,

    N1.Dscription,N1.price,

    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

    and N1.Warehouse = @Whse

    Group By

    N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price

    Union All

    select

    N1.Warehouse,

    N1.Itemcode,

    N1.Dscription,N1.price,

    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

    and N1.Warehouse = @Whse

    Group By

    N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price) a, dbo.OITM I1

    where

    a.ItemCode=I1.ItemCode and

    I1.ItmsGrpCod = @Group

    Group By

    a.Itemcode

    Order By a.Itemcode

    Check with also these links for better understanding

    http://scn.sap.com/thread/2064822

    http://scn.sap.com/thread/1889531

    http://scn.sap.com/thread/1409449

    http://scn.sap.com/thread/1974390

    http://scn.sap.com/thread/3146906

    http://wiki.sdn.sap.com/wiki/display/B1/SAP+B1+SQL+H-IN+Item+Inventory+Opening+and+Closing+Stock+per+warehouse

    Hope Helpful 😊

    Regards

    Kennedy

    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.