Clarity

All,

I am building a Custom Audit Portlet and have hit a roadblock when trying to map dynamic lookup values to cmn_audits - value_before and value_after.

Any guidance in this would be greatly appreciated.

Thanks
________________________


[odf_aud_multi_val_lovs] >>>> dynamic lookups <<<<<<[ cmn_audits ] >>>>>> static lookups <<<<<<<<[odf_aud_lookup_captions]

See if you can do that with ODF_AUD_VALUE_FCT

Martti K.

Martti K,

Thanks for your help, I am new at this and do not understand what ODF_AUD_VALUE_FCT is. Is it a function? Is it a table? What does it do?

Thanks

ODF_AUD_VALUE_FCT is a database function (indicated by FCT).

Put the SQL trace on when you open the audit trail and you can see the database commands the application passes to the database.
UPDATE CMN_SEC_USERS SET SQLTRACE_ACTIVE = 7 WHERE USER_NAME = 'admin'
UPDATE CMN_SEC_USERS SET SQLTRACE_ACTIVE = NULL WHERE USER_NAME = 'admin'

If you are auditing multivalued lookups the trace should show how they retrieved from the database.

Martti K.

The alternative to using the stock function "ODF_AUD_VALUE_FCT" for muti-valued lookups is to read the data directly from the underlying table; ODF_AUD_MULTI_VAL_LOVS - but using that funciton is much easier is you just want to display the formatted data (I need to use the table when I want to do something with the actual values)

--

HOWEVER - I think the original question was rather to with dymamic lookup values rather than multi-valued-lookup values; for that you will just need to incorporate the "logic" from you dynamic lookup NSQL into the NSQL for the query for your custom portlet; i.e. your portlet query has to work out for a given auditted lookup value what this actually means (based on the logic in your lookup NSQL) - this should not be too hard to code?

(this is very much complicated by multi-valued dynamic lookups lookups of course!) :ph34r:

Dave wrote:

The alternative to using the stock function "ODF_AUD_VALUE_FCT" for muti-valued lookups is to read the data directly from the underlying table; ODF_AUD_MULTI_VAL_LOVS - but using that funciton is much easier is you just want to display the formatted data (I need to use the table when I want to do something with the actual values)

--

HOWEVER - I think the original question was rather to with dymamic lookup values rather than multi-valued-lookup values; for that you will just need to incorporate the "logic" from you dynamic lookup NSQL into the NSQL for the query for your custom portlet; i.e. your portlet query has to work out for a given auditted lookup value what this actually means (based on the logic in your lookup NSQL) - this should not be too hard to code?

(this is very much complicated by multi-valued dynamic lookups lookups of course!) :ph34r:

Dave and Martti,

Thanks for your help with this. Looking at the diagram for Audit provided in the technical reference guide. I understand that in order for me to retrieve usable information for the values: value_before and value_after in the CMN_Audits table. I need to unite this table with either odf_aud_multi_val_lovs (for dynamic values) or odf_aud_lookup_captions (for static values).

I was thinking of developing the solution as follows:

Select
audi.value_before
, dyna.DBvalue
,stat.SBvalue
, audi.value_after
, dyna.DAvalue
,stat.SAvalue

From
cmn_audits audi
, odf_aud_multi_val_lovs dyna
, odf_aud_lookup_captions stat

Where
audi.id=dyna.id and audi.id=stat.id

thank you
________________________________________

how do i run a sql trace?

The trace commands are

UPDATE CMN_SEC_USERS SET SQLTRACE_ACTIVE = 7 WHERE USER_NAME = 'admin'

UPDATE CMN_SEC_USERS SET SQLTRACE_ACTIVE = NULL WHERE USER_NAME = 'admin'

See
TEC435531 Using Clarity SQLTrace
TEC439280 How to read SQL Trace Log Timings
TEC513073 Using Clarity SQLTrace - Explanation of Logging Levels

for more details


Martti K.

itslsoto wrote:

I need to unite this table with either odf_aud_multi_val_lovs (for dynamic values) or odf_aud_lookup_captions (for static values).

Your logic is not quite right - if you look at the diagram you have posted ; odf_aud_multi_val_lovs is for MULTI VALUED lookups only (whether these are static / static dependant lists or dynamic)

As I posted above, for any DYNAMIC logic you need to provide the logic (in your code) to decode the stored value into its relevant "display value"; Clarity does not have this information on any table, it is derived dynamically from the dynamic logic in your NSQL that populates the lookup.

itslsoto wrote:

how do i run a sql trace?

Search on the boards - you will find a lot of information about this; or search in the knowledge base and you'll find TEC435531

Dave wrote:

itslsoto wrote:

I need to unite this table with either odf_aud_multi_val_lovs (for dynamic values) or odf_aud_lookup_captions (for static values).

Your logic is not quite right - if you look at the diagram you have posted ; odf_aud_multi_val_lovs is for MULTI VALUED lookups only (whether these are static / static dependant lists or dynamic)

As I posted above, for any DYNAMIC logic you need to provide the logic (in your code) to decode the stored value into its relevant "display value"; Clarity does not have this information on any table, it is derived dynamically from the dynamic logic in your NSQL that populates the lookup.

itslsoto wrote:

how do i run a sql trace?

Search on the boards - you will find a lot of information about this; or search in the knowledge base and you'll find TEC435531

On another note, do you know how I can join CMN_AUDITS to the INV_INVESTMENTS table?

I tried using ID, but running into issues with my output displaying repetitive data.

IE

I query to show the audits of one specific project and it shows me the correct number of items - 3
yet when I add the join with the investments table, the output shows me about 18 line items - for which I only had 3 prior to joining the investments table using id.

itslsoto wrote:

On another note, do you know how I can join CMN_AUDITS to the INV_INVESTMENTS table?

I tried using ID, but running into issues with my output displaying repetitive data.

The join should be on the CMN_AUDITS.OBJECT_ID = INV_INVESTMENTS.ID where CMN_AUDITS.object_code = 'project'