Inventory Valuation Report automatic and without modifying last evaluated price

[Former Member]

Hallo everybody,

maybe this is a strange request, but vital for my configuration: since we use last evaluated price as base price for a production addon, I need two things:

1. inventory valuation report to be run in automatic at certain hours
2. Possibility to run the report without modifying last evaluated price

We do not use stock continuous.

For the request n.2, all I really need is to have the SQL query/procedure SAP uses to do the report, then I can manage it myself.

Thanks

[agustin_marcoscividanes · Accepted solution]

Hi

You can find the stored procedure in SBO-COMMON.dbo.SCSP table.

You cannot modify, you have to make a CR report or a  query for your requirements.

Kind regards

Agustín Marcos Cividanes

[Former Member · Accepted solution]

Thanks for the reference to SCSP table, but could you be more specific?

There are 1780 rows and none explicitily refers to the inventory valuation report...

[Former Member]

Up! Nobody can help?

[Johan_H]

Hi Damiano,

Have you tried using SQL Profiler ?

Regards,

Johan

[Former Member]

Sorry Johan for not answering in first place: I tried to use SQL Profiler but I'm not getting anything useful out of it.

For example: this is what system calls when I launch the Inventory Valuation Report (the number on the right is one of my item codes).

exec sp_execute 127,N'005342008'

Maybe it's me but from this I do not understand which procedure is called.

P.s. Is there a way to export the results table in an Excel format? I tried XML table but when opened in Excel it generates an error (error opening file).

[Johan_H]

Some of the stored procedures are proprietary, and SAP has encrypted them. My guess is, that the 127 is some sort of way to call a certain encrypted stored procedure.

Most likely this is one sp in a series that all together create the report.

SQL Profiler is indeed not an option, so back to the drawing board.

The whole point of the inventory valuation report is to update the Last Evaluated Price price list. As I understand it, you need this price in your production process ?

A simple option would be to just calculate the price on the fly, as you are suggesting, but perhaps this calculation does not have to be as complicated the one used by the report? A lot depends on what evaluation method you use when you run the report.

Regards,

Johan

P.S. What results table do you mean?

[Former Member]

What I need is to be able to have the value of the stock without launching that report (because it touches the last evaluated prices).

Problem is I use "Moving average" as evaluation method, so I really need to use the same procedure SAP uses.

P.s. I meant the table of traced events.

[Johan_H]

Okay, well moving average is in itself a simple calculation, it is just that you need the entire purchase- and production history of the item.

The report just looks at all purchase and production transactions that get quantities into warehouse stock, and at what price.

The system report takes all possible transactions into account, because it has to, but you may be able to limit the tables, if you do not use all possible methods of increasing an item's stock.

Which transaction types do you (possibly) use to increase stock, and/or change the value of stock ? Just to name a few:

• Goods Receipt  P/O (OPDN, PDN1)
• A/P Invoice (OPCH, PCH1)
• (A/R) Returns (ORDN, RDN1)
  
• A/R Credit Memo (ORIN, RIN1)
• Goods Receipt (OIGN, IGN1)

but there are quite a few more transactions with which you can influence stock and its value.

Does your process apply to all items, or only to say component items ? This in turn may limit the sort of transactions that you need to take into account.

P.S. A simple way to get the trace result into Excel, is to select all rows (select first row, hold shift, select last row) from the trace window, and copying them (CTRL+C). Then open Excel, change all columns, except B to Text, and paste (CTRL+V).

[Johan_H]

Hi Damiano,

You best bet is probably to use SQL Server Profiler from MS SQL Management Studio (Tools menu), to see what queries B1 runs to get the data.

Regards,

Johan