Query to get the Discoverer Scheduled jobs with User details

Oracle Discoverer Reports Schedule details with User for SOX Audit

You may want to be able to audit log which user has scheduled a specific workbook/ Discoverer report.

If you want to query information about user that has scheduled a report / discoverer workbook:

Query:

SELECT ebr.br_job_id, dj.job "DBA_JOB", ebr.br_created_by
      ,CASE
         WHEN INSTR (ebr.br_created_by, '#') = 0
           THEN ebr.br_created_by
         WHEN INSTR (ebr.br_created_by, '#') > 0 AND INSTR (ebr.br_created_by, '#', 2) = 0
           THEN (SELECT fu.user_name
                   FROM fnd_user fu
                  WHERE fu.user_id = SUBSTR (ebr.br_created_by, 2, 9))
         ELSE NULL
       END "BR Owner/Creator - USER"
      ,dj.what,dj.broken, ebr.br_name, ebr.br_workbook_name, ebr.br_description, ebr.br_created_date, ebr.br_next_run_date, dj.next_date "DBA_JOB_NEXT_DATE", ebr.br_completion_date, ebr.br_updated_by
      ,CASE
         WHEN INSTR (ebr.br_updated_by, '#') = 0
           THEN ebr.br_updated_by
         WHEN INSTR (ebr.br_updated_by, '#') > 0 AND INSTR (ebr.br_updated_by, '#', 2) = 0
           THEN (SELECT fu.user_name
                   FROM fnd_user fu
                  WHERE fu.user_id = SUBSTR (ebr.br_updated_by, 2, 9))
         ELSE NULL
       END "BR Updated by - USER"
      ,ebr.br_updated_date
  FROM EUL_OWNER.eul5_batch_reports ebr, dba_jobs dj
WHERE ebr.br_job_id = dj.job AND dj.broken = 'N';

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

SOX Audit Report : Oracle EBS Financial Statement Generator (FSG) Report Details

Oracle EBS Financial Statement Generator (FSG) Report Details for SOX Audit

You may want to be able to audit FSG Report Details.

If you want to query information related to FSG reports

Query:

SELECT DISTINCT r.NAME "REPORT_NAME"
      , r.report_title
      , r.description
      , (SELECT fu.user_name
              FROM fnd_user fu
             WHERE fu.user_id = (r.created_by)) created_by
      , r.creation_date
      , (SELECT fu.user_name
              FROM fnd_user fu
             WHERE fu.user_id = (r.last_updated_by)) last_updated_by
      , r.last_update_date
FROM rg_reports_v r, fnd_id_flex_structures_v fst, fnd_user u, rg_report_axis_sets_v rw, rg_report_axis_sets_v rw2
 WHERE r.structure_id = fst.id_flex_num
   AND r.row_set_id = rw.axis_set_id
   AND r.column_set_id = rw2.axis_set_id
   AND r.created_by = u.user_id
   --AND r.NAME IN ('FSG Report Name')   -- Enter FSG Report Name Here
   AND r.last_update_date >= TO_DATE ('01-JUN-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
   AND r.last_update_date <= TO_DATE ('30-JUN-2016 23:59:59', 'DD-MON-YYYY HH24:MI:SS') 
ORDER BY 1, 2;

This query will give the output with FSG Report details for the period of 01-JUN-2016 to 30-JUN-2016.

SOX Audit Report : Applied Patches (adpatch) History in Oracle EBS 11i and R12

Applied Patches (adpatch) History Report in Oracle EBS 11i and R12 for SOX Audit

You may want to be able to audit recently applied patches (adpatch) history report in Oracle EBS 11i/R12.

If you want to query information about patches applied history report:

Query for EBS R12:

select distinct aap.patch_name,  apr.patch_top,  aat.name,  aap.patch_type,                        
       aap.creation_date,  fav.APPLICATION_NAME, apd.patch_abstract
from apps.ad_patch_runs apr,
     apps.ad_patch_drivers apd,
     apps.ad_applied_patches aap,
     apps.ad_patch_run_bugs aprb,
     apps.ad_appl_tops aat,
     apps.fnd_application_vl fav
where apr.appl_top_id = aat.appl_top_id
  and apr.patch_driver_id = apd.patch_driver_id
  and apr.patch_run_id = aprb.patch_run_id
  and apd.applied_patch_id = aap.applied_patch_id
  and UPPER(aprb.application_short_name) =fav.application_short_name
  and aprb.applied_flag = 'Y'
and aap.creation_date >= to_date('01-APR-2016','DD-MM-YYYY')
and aap.creation_date <= to_date('30-APR-2016','DD-MM-YYYY')
order by aap.creation_date;

Query for EBS 11i:

select distinct aap.patch_name,  apr.patch_top,  aat.name,  aap.patch_type,                        
       aap.creation_date,  fav.APPLICATION_NAME
from apps.ad_patch_runs apr,
     apps.ad_patch_drivers apd,
     apps.ad_applied_patches aap,
     apps.ad_patch_run_bugs aprb,
     apps.ad_appl_tops aat,
     apps.fnd_application_vl fav
where apr.appl_top_id = aat.appl_top_id
  and apr.patch_driver_id = apd.patch_driver_id
  and apr.patch_run_id = aprb.patch_run_id
  and apd.applied_patch_id = aap.applied_patch_id
  and UPPER(aprb.application_short_name) =fav.application_short_name
  and aprb.applied_flag = 'Y'
and aap.creation_date >= to_date('01-APR-2016','DD-MM-YYYY')
and aap.creation_date <= to_date('30-APR-2016','DD-MM-YYYY')
order by aap.creation_date;

This will give the output with patch details which have been applied during the period of 01-APR-2016 to 30-APR-2016

SOX Audit Report : Oracle Discoverer Reports access/execution History With User Details

Oracle Discoverer Reports Access/Execution History for SOX Audit

You may want to be able to audit user's access information to run Discoverer worksheets to a specific workbook.

If you want to query information about users who have executed a specific report:

Query:

SELECT   qs_doc_name AS "Workbook Name", qs_doc_details AS "Worksheet Name"
        ,CASE
           WHEN INSTR (qs_created_by, '#') = 0
             THEN qs_created_by
           WHEN INSTR (qs_created_by, '#') > 0 AND INSTR (qs_created_by, '#', 2) = 0
             THEN (SELECT fu.user_name
                     FROM fnd_user fu
                    WHERE fu.user_id = SUBSTR (qs_created_by, 2, 9))
           ELSE NULL
         END "Worksheet Run By"
        ,qs_created_date "Run Date"
    FROM EUL_OWNER.eul5_qpp_stats
   WHERE LOWER (qs_doc_name) LIKE LOWER ('%oracle%') --Enter Workbook Name Here
     AND qs_created_date >= TO_DATE ('01-APR-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
     AND qs_created_date <= TO_DATE ('30-APR-2016 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   --AND qs_created_date >= (SYSDATE - 10)
ORDER BY qs_created_date DESC;

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

This query will give the output with users details who have executed a specific report from 01-APR-2016 to 30-APR-2016.

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.