Inventory Audit Report query

[Former Member]

Dear All,

  I have to add additional fields on Inventory audit report.

From where can I find the query to modify the report?

Thanks

[kothandaraman_nagarajan · Accepted solution]

Hi Irene Vas,

Inventory Audit Report is the standard report in SAP business one. You can't add additional field by query.

Please advice your exact requirement.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thanks for the quick response.

I'm new to SAP business one and trying to understand the relationship between tables.

My requirement is I want the report exactly same as system report in addition I need to display user defined fields. How can I achive this?

Thanks,

[kothandaraman_nagarajan · Accepted solution]

Hi Irene Vas,

Not possible to add UDF in standard report. But you can create query to get UDF.

Try this query:

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,

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 ,

(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

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

Note: OINM -Ware house Journal table

          OITM- Items table

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thanks Nagarajan for your input.

I used the query above to fetch records.

But for the document column as per audit report I added Base_Ref from OINM.

But it doesn't give me the link to open the document.

Please advise.

Thanks,

Irene

[kothandaraman_nagarajan · Accepted solution]

HI Irene Vas,

Please add for browse at end of query and try.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

I tried but it did not work.

Even when I query only OINM table, for the base reference column there is no link.

Is there any way to achieve this?

For time being, I'm displaying all information from OINM for a particular posting period.User wants the link to open SO , SI etc

Thanks,

Irene

[kothandaraman_nagarajan · Accepted solution]

Hi Irene Vas,

Link arrow will not work for union all function.

You can wait other great experts answers.

Thanks & Regards,

Nagarajan

[KennedyT21 · Accepted solution]

Hi Irene Vas...

The orange arrow will be displayed on all document transaction and mastercodes having a unique Id generate by SAP. specially numbers link BP codes, Item codes.

You could try using "FOR BROWSE" at the end of your query.

To your Query please find the solution in the below link SAP Note.

[https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1410595]

Cause

Consulting

Solution

The Orange Arrow may not show in the following cases:

1. The field does not represent keys of a table.
2. Queries that contain groupings.
3. Queries that are based on Archive tables ("A" tables).
4. Queries that contain UNION clause.
5. Queries that contains temporary tables.
6. Queries for Formatted Search.
7. Queries with parameters declared.
8. Queries with certain restricted names in comment section

You could try using "FOR BROWSE" at the end of your query.

FOR BROWSE means that the result displays the orange arrow to navigate into the master record or document behind it.

Hope Helpful

Regards

Kennedy

[Former Member · Accepted solution]

Thank you for the information.

Where can I find OINM view definition? I want to check underlying tables of this view.

When I query  OIGE,OIGN, OPDN,OWTR  tables with union all clause I get partial output.

But my concern is when I query OIGE table  I get one record for the Document Number(SO) but in Inventory Audit Report/OINM  there are two records for the same SO with quantity split.

Any advise?

Regards,

Irene

[KennedyT21 · Accepted solution]

Hi  Irene...

You can able to view the underlying tables of OINM by in sql right click the view and script to new query window will show the query consist of the OINM

Hope Helpful

Regards

Kennedy

[Former Member · Accepted solution]

Thank you for the information.

It was helpful.

Best Regards,

Irene

[Former Member]

Hi Nagarajan K

I am trying to make modification an excel report as from Inventory Audit Report Criteria  the table OINM & OITM I had used to find the results. I have attached a picture which I need as a output into SAP system.On the below is my desire output needed

Value of Quantiy and Cumulative value I  took T0.[InQty]) ,T0.[OutQty] and T0.[CalcPrice]

from the Query but not getting proper one. Any one have any suggestions for this.

Right now getting this