Inventory Audit Report question - Underlying Table

[Former Member]

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

[KennedyT21]

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...

Hope Helpful

Regards

Kennedy

[KennedyT21]

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