Question Re: Change History Details Report in Spend Management Q&A

Change History Details Report

Former Member — Wed, 15 Apr 2015 07:27:08 GMT

Hello Experts ,

We have a requirement for creating change history details report for Project . I am aware of that we can see change history details for each documents but the requirement is to create a report which we can run and see change history details for all documents .

The approach we have taken so far is - creating query definition joining FCI_DOC_CHG_HISTORY and FCI_PRO_PROJECTS and in the result field we have defined EVENT_RESOURCE for capturing document change details and field type is - string resource . However , while running the query we are getting result as

The value of '{0}' was changed from '{2}' to '{3}'

User account {0} was created

I believe we need to replace the values with respective parameters . Now while I also add the column - EVENT_PARAMS I am getting parameter values as below

paramcount=5 4=Service Registration 3=true 2=false 1=AGCLMD21-21-00 0=DKT0014

So looks like the value for {0} to be replaced with the value appearing for parameter 0 ( which is DKT0014 )

Can anyone help me how to replace the values ?

Thanks in advance

Sudipta

Re: Change History Details Report

Former Member — Wed, 15 Apr 2015 15:04:36 GMT

Hello,

This type of message format is actually java-specific.

You do have a possibility of implementing the same in Oracle DB, and have it available in the select list but it is not recommended because it requires direct changes at database level.

I have not tested this and I cannot confirm if it will work, because there are some differences between Oracle standard DB and Oracle DB for SAP.

Take a look at Oracle's details for implementing this in the database and decide if it is actually mandatory to have:

Parameterized Custom Message

Regards,

Bogdan Toma

Re: Change History Details Report

Former Member — Wed, 22 Apr 2015 14:12:15 GMT

It's a little tricky and gets ugly if not organized, but this can also be done right in the query by using combinations of standard Oracle functions in the SQL:

12.5 String Functions

12.5.2 Regular Expressions

HTH,

Mike

Re: Change History Details Report

Former Member — Tue, 05 May 2015 05:50:53 GMT

Thanks everyone for suggesting me .

However , while executing the query in SAP sourcing , one of the column is -

The value of '{0}' was changed from '{2}' to '{3}'

and next column is parameter values -

paramcount=5 4=Service Registration 3=true 2=false 1=AGCLMD21-21-00 0=DKT0014 ( in a singe row )

, So somehow in the query result , I have to replace the values so that user can run this report and extract in spreadsheet .

Can you please guide me ?

Re: Change History Details Report

Former Member — Wed, 06 May 2015 11:43:52 GMT

This is the gist of how to do it with a SQL expression:

how
to replace multiple strings together in Oracle - Stack Overflow

In SAP CLM’s “Query Definition” editor, you will add a new field on the “Result Fields” tab and you write your expression in the “Database Column Name” field.

The example in the link is from a function and not a simple query, but you can find what you need by focusing on this piece:

replace( replace(mycol, '{1}', 'myoneval'), '{2}', mytwoval)

This covers replacing the parameter tag in the string but, of course, you have to extract the appropriate value from the EVENT_PARAMS column. I think this is the most powerful way to do it: REGEXP_SUBSTR. You basically will insert this function in place of myoneval, mytwoval etc in the example.

I don't have time at the moment, but if you want to post the SQL from the first tab, I can try to look at it later, if you need help-- or ask a follow-up question here. I’m also interested in this for my own use and don’t mind helping out, if I can.

HTH,

Mike