Clarity

Expand all | Collapse all

Report Audit trail Info

  • 1.  Report Audit trail Info

    Posted 03-25-2014 03:53 PM

    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.

     

    Attachment(s)

    docx
    sql for audit.docx   126K 1 version
    doc
    Audit Info for KM.doc   72K 1 version


  • 2.  RE: Report Audit trail Info

    Posted 03-26-2014 07:14 AM

    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.

    -Gurjeet