Inventory data updates for committed and on order

[anrodse]

I wrote a query to get last inventory update from SAP using inventory movement table. Something like this:

SELECT TI."ItemCode", IM."CreateDate", IM."CreateTime"
FROM OIVL IM INNER JOIN OITM TI ON IM."ItemCode" = TI."ItemCode"
WHERE IM."TransSeq" IN (SELECT MAX("TransSeq") FROM OIVL GROUP BY "ItemCode")

The problem with this query is that it doesn't show any update when we create a sales order or purchase order.

Is it possible get this information from any specific table? I wonder if I can make this in a simple way.

I know I could check some extra tables (RDR1, POR1, ...) to get this information, but that implies adding some tables to the query and getting lastest date from them all. Also, I'll need to add some new tables for SBO v10.

EDIT: We also have to take into account log tables, i.e., ADOC and ADO1.

EDIT2: I've found OILM table, which includes commited changes but it fails when I delete, update or add document lines.

[jan_cech]

Hi Antonio,

My shot would be to check the values in OITM and OITW for changes. These are acumulating the On Order and Is Commited values on item and warehouse level.

There are also AITM and AITW change logs.

There are many many document types that are affecting the committed and ordered values. You may look up the detection query of SAP Note 999124 .

I hope this helps

Cheers

Jan

[anrodse]

UpdateDate field is not updated at OITM nor OITW whith stock movement. Plus OITW has no UpdateTS column. I cannot use those tables to get stock updates.

My goal is to keep my website synchronized with SBO and I don't want to update stock for all my 10000 references every 10 minutes. Just those which have been updated in the last ~10 minutes.

[jan_cech]

Comparing OITW vs. AITW might be a way. Not sure if you find that a working solution for your needs tough.

Other than that - Please have a look on the query of SAP Note 999124 mentioned earlier. This is how we query and verify committed and ordered figures. There is no "easy way" to query that information e.g. like using a single table, or view, unfortunately.

[PierreBrothier]

Hi Antonio,

you could handle it with transaction notification, to log PO and SO creation in you own custom table.

So you can log item that needs to be updated.

Another way is to use B1iF with document trigger. But as Jan says, that's not so easy

[JesperB1]

Hello,

The query fails with an error or it fails as in giving incorrect results?

Is there an error message? How did you join OILM?

Jesper

[anrodse]

It's giving incorrect results. I'll give you an example:

I create an order with one reference, let's say ITEM1. This action will create a new line at OILM table, so I know that stock for ITEM1 has been modified. That's ok.

Then I update the order, replacing ITEM1 by ITEM2. Linked line at OILM will be updated, reeplacing ITEM1 by ITEM2 as it happened with the order, so I know ITEM2 stock has been modified. My problem: ITEM1 stock has also been updated, I have less stock commited because I modified the order committing the stock, but I have no entries inat OILM table to check it out.

It's the same if I remove any line from the order, stock is not committed any more but I have no entries at OILM related to this item.