Sales Orders Change Log question

[Former Member]

Hi All,

I am currently working on a project that involves querying SAP Database to get custom reports via web browser, I've made a lot of advances and most of the site is ready but I'm missing just one item that I hope you could help me.

Thing is I am working with the Sales Order change log and the problem comes one a user deletes a row. If I go to the change log inside SAP and see the differences a new record will be shown saying that row X was deleted and

if I go to the database (ADOC and ADO1 tables) I don't have a way to see wich row was deleted.

My way of doing this is to check for the number of rows on the current state of the sales order and check the same on the previous instance of the SO and see if a line was deleted. Then I would have to check row by row between instances what rows are there and what row is missing and store that info. I think this kind of approach will definitively kill the server.

I was wondering if some one can give me a hint about how to approach this problem. Does any body know how SAP works on this area?

I'm working with SAP2005A and SQL Server 2005 Standard Edition.

Any help is greatly appreciated.

Thanks!

Alejandro.-

[Former Member]

You can use the transaction notification procedure to catch the changes in so

The log could be stored into a table in a different auxiliary database

[Former Member]

I doubt your way is the right way that works.

Some Business Objects have log tables to log changes, but they're now not fully supported in DI API to get that info.

[Former Member]

Hi, you are correct, this is not the right way of doing this but I need to give a solution to the customer and I can't seem to find anything on this issue, that is why I'm working this accessing directly through SQL Server engine.

Thanks for your time looking at this thread.

Regards.

[Former Member]

Hi,

you should watch the folowing information based on this querys:

select docentry,objtype,loginstanc,* from adoc where objtype=17
select docentry,linenum,objtype,loginstanc,* from ado1 where objtype=17

Primary Keys ok ADO1 (history lines) are: docentry,linenum,objtype,loginstanc

playing with "loginstanc" and "lineNum" you can see watch line have been deleted

I hope helps

Kind regards

[Former Member]

Be carefull!!!

the last information on any log tables is an snapshot of the original table

(for example: information on OCRD is exactly of de last information stored in ACRD... so if you want to see the last modified on OCRD, you should watch the Max(loginstanc -1) on ACRD table)

I hope help you

[Former Member]

Hola Alejandro,

Thanks for your answer, as I see it I'm doing exactly what you proposed but the problem comes with the following scenarios:

a.- If I post a new SO with two lines I will have on the ADO1 table two records with the values 0 and 1 for each line respectively but if I delete the item on LineNum = 1 and then I add a new item, this last item will be also created with LineNum value = 1. This is not a complicated thing to do but the problem comes when the user is used to work with SOs with several items at detail level and is used to add and erase lines once in a while and as I see it it's very difficult for me to track those specific changes. There's also the fact that on the Log table a new instance is created every time the user hits the update button regardless if a modification was actually made or not. I mean I would know that a line was deleted or added but I would have to start comparing each field on each line of a sales order with it's previous instance to check what was actually added/deleted and I'm afraid that this may affect the server performance. I know that SAP does this without affecting to much the system resources and that is what I'm trying to find.

I forgot the other scenario I was considering he he I will post again as soon as I remember it but I hope I'm explaining this ok (I'm not very good at explaining...) If not let me know and I'll try to explain again.

I'm also working on trying to get another approach to see what comes up...

Thanks for checking this thread, your answers are helpful.

Regards.