I am trying to report audit trail info. Example: I want to see the project name, task name audited, Attribute audited, operation completed, changed by, old value, new value, date updated. I cannot seem to find the correct joins to create these tables in my universe. We have a portlet page on each project that shows the info per project, I want to replicate that in my report.
SELECT inv.id, inv.name, aud.id, aud.object_code, aud.column_name, aud.table_name, aud.operation_code, aud.value_before, aud.value_after, aud.last_updated_date, aud.last_updated_by, res.unique_name, res.full_name
aud.last_updated_by = res.user_id
and inv.id = aud.id
and inv.id = 5037002
This query only returns one row and does not bring me the task name that was audited. There should be many tasks per project and many audits per task.
All the Audited Information is present in the cmn_audits table.
For Task Information ... Required Task attributes in Task object should be Selected as Audited Attributes. Otherwise there is no information in the Audit table.
As join to Audit table:
PRTASK.PRID = CMN_AUDITS.OBJECT_ID where Object_code = 'task'
I hope this helps.