Clarity

  • Private Community
 View Only

  • 1.  Project OBS audit

    Posted Oct 07, 2020 02:53 PM
    Hi

    How can we find Project OBS association in audit  from DB query.
    We do not see OBS in cmn_audits even thought it is audited in Project object and we can see the values in Project audit.
    Please help s identify table or query from where we can pull what we see in Project audit tab.

    Thanks,
    Kritee


  • 2.  RE: Project OBS audit

    Broadcom Employee
    Posted Oct 15, 2020 05:02 PM
    Hi Kritee, 

    The OBS audit details can be found in the cmn_audits table. You'll just need to get the attribute id corresponding to the Project OBS on the Project object. These are automatically created ID's so they would look something like this: odf_proj_n998864610
    Original Message


  • 3.  RE: Project OBS audit

    Posted Oct 16, 2020 12:22 PM
    Thanks for your reply, Just wondering which column of  cmn_audits table I search for odf_proj_?

    Original Message


  • 4.  RE: Project OBS audit

    Posted Oct 19, 2020 04:28 PM
      |   view attached
    If I audit Project department OBS, enter a value and then change it this is what I get in the audits table

    id object_code object_id attribute_code table_name column_name value_before value_after raw_value_before raw_value_after operation_code created_date created_by last_updated_date last_updated_by transaction_id
    5000011 project 5002001 odf_obs_fin_dept SRM_PROJECTS NULL /Corporate/IT/Business Operations NULL 5000045 U 00:12.0 5002001 00:12.0 5002001 39886
    5000012 project 5002001 odf_obs_fin_dept SRM_PROJECTS /Corporate/IT/Business Operations /Corporate/IT/Development 5000045 5000043 U 01:47.0 5002001 01:47.0 5002001 48402

    Attachment(s)

    xls
    Audit_proj_obs_qry_rlts.xls   33 KB 1 version
    Original Message


  • 5.  RE: Project OBS audit

    Posted Oct 19, 2020 06:41 PM

    this query snippet may help:

    select
    cmn_audits.id
    , inv.id inv_internal_id
    , inv.code investment_id
    , row_number()
    over (order by cmn_audits.table_name, cmn_audits.object_id, cmn_audits.attribute_code, cmn_audits.created_date)
    as sort_by_this
    , cmn_audits.table_name
    , cmn_audits.object_id
    , cmn_audits.attribute_code
    , cmn_audits.value_before value_before_id
    , cmn_audits.value_after value_after_id
    , cap_bef.name value_before
    , cap_aft.name value_after
    , case when cmn_audits.operation_code = 'D' then 'Deleted'
    when cmn_audits.operation_code = 'U' then 'Updated'
    when cmn_audits.operation_code = 'I' then 'Inserted'
    end AS operation_code
    , cmn_audits.created_date AS change_date
    , srm_resources.full_name AS changed_by
    , lag(cmn_audits.created_date)
    over (partition by cmn_audits.table_name, cmn_audits.object_id, cmn_audits.attribute_code
    order by cmn_audits.created_date)
    as prev_change_date
    , lag(case when cmn_audits.operation_code = 'D' then 'Deleted'
    when cmn_audits.operation_code = 'U' then 'Updated'
    when cmn_audits.operation_code = 'I' then 'Inserted'
    end)
    over (partition by cmn_audits.table_name, cmn_audits.object_id, cmn_audits.attribute_code
    order by cmn_audits.created_date)
    as prev_operation_code
    from cmn_audits
    left join srm_resources on cmn_audits.created_by = srm_resources.user_id
    inner join ODF_AUD_LOOKUP_CAPTIONS cap_bef on cmn_audits.value_before = TO_CHAR(cap_bef.id) and cap_bef.language_code = 'en'
    inner join ODF_AUD_LOOKUP_CAPTIONS cap_aft on cmn_audits.value_after = TO_CHAR(cap_aft.id) and cap_aft.language_code = 'en'


    Original Message