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
FROM
Inv_investments inv,
cmn_audits aud,
srm_resources res
WHERE
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.