SOX Audit Report : Oracle Discoverer Reports Change History With User Details

Oracle Discoverer Reports Change History for SOX Audit

You may want to be able to audit log which user has made changes to a specific workbook.

If you want to query information about user that has made the last update/change to a workbook:

Query:

SELECT DISTINCT disco_docs.doc_name "Discoverer Workbook",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_created_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_created_by
                   WHEN INSTR (disco_docs.doc_created_by, '#') > 0
                   AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_created_by, 2, 9))
                   ELSE NULL
                END "Workbook Owner/Creator",
                disco_docs.doc_created_by,
                disco_docs.doc_created_date "Creation Date/Time",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_updated_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_updated_by
                   WHEN INSTR (disco_docs.doc_updated_by, '#') > 0
                   AND INSTR (disco_docs.doc_updated_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_updated_by, 2, 9))
                   ELSE NULL
                END "Workbook Updated by",
                disco_docs.doc_updated_by,
                disco_docs.doc_updated_date "Update Date/Time"
           FROM EUL_OWNER.eul5_documents disco_docs,
                EUL_OWNER.eul5_eul_users disco_users
            WHERE disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
              AND disco_docs.doc_updated_date >= TO_DATE ('01-APR-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
              AND disco_docs.doc_updated_date <= TO_DATE ('30-APR-2016 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
            --AND disco_docs.doc_updated_date >= (SYSDATE - 10)
            --AND disco_docs.doc_name LIKE '%Oracle%'
            ORDER BY doc_updated_date DESC;

Note : Replace EUL_OWNER with corresponding EUL owner of your environment in above query.

This query will give the output for the oracle discoverer report changes history from 01-APR-2016 to 30-APR-2016.