Discoverer Reports – Usage – Submitted

The schema name for your disco reports might not be the same as those used in these SQL statements. I’ve used disco_us but yours might be different, e.g. noetix_eul.

Sections:

  1. Report Runs – Details
  2. Counting
  3. Usage Per Report Including Last Used

Each time a Disco Report is manually submitted, the data is is stored in the eul5_qpp_stats table, compared to the eul5_br_runs table, which is used to store info about instances when scheduled reports run.

Manually Submitted Reports – Report Runs – Details

-- ##############################################################################
--       REPORT RUNS - DETAILS
-- ############################################################################*/

-- details v1

  SELECT q.qs_doc_name workbook
       , q.qs_act_elap_time run_secs
       , FLOOR (q.qs_act_elap_time / 60) || ':' || MOD (FLOOR (q.qs_act_elap_time), 60) mins
       , q.qs_act_elap_time
       , q.qs_est_elap_time
       , q.qs_doc_details sheet
       , q.qs_created_date run_date
       , DECODE (q.qs_state, 0, 'ERROR :CANCELLED', 1, 'RUNNING', 2, 'COMPLETED') status
       , u1.user_name owner
       , u1.description owner_name
       , u2.description run_by
    FROM disco_us.eul5_documents d
    JOIN disco_us.eul5_qpp_stats q ON d.doc_name =     q.qs_doc_name 
    JOIN applsys.fnd_user u1       ON q.qs_doc_owner = u1.user_name
    JOIN applsys.fnd_user u2       ON q.qs_created_by = '#' || u2.user_id 
--   WHERE q.qs_doc_name IN ('441 SA Valid Payroll Codes Check')
     AND q.qs_created_date >= '24-JAN-2016'
     AND u2.user_name = 'MTFSSPO4'
--     AND q.qs_doc_details = 'Not interfaced'
--     AND u2.description = 'Khalil Mohammed'
ORDER BY q.qs_created_date DESC;

-- details v2

SELECT fu.user_name
     , fu.description
     , q.qs_created_date run_date
     , q.qs_doc_name report
     , q.qs_doc_details sheet
     , q.qs_act_elap_time elapsed_time_seconds
  FROM disco_us.eul5_qpp_stats q
  JOIN applsys.fnd_user fu ON q.qs_created_by = '#' || fu.user_id
 WHERE 1 = 1
   AND q.qs_created_date > '20-MAY-2016'
--   AND fu.user_name = 'SYSADMIN'
--   AND q.qs_doc_name IN ('My Test Report')
ORDER BY q.qs_created_date DESC;

Counting

-- ##############################################################################
--       COUNTING
-- ############################################################################*/

  SELECT COUNT(*) tally
       , SUM(q.qs_act_elap_time) ttl_secs
       , TO_CHAR(q.qs_created_date, 'RRRR-MM') mnth
       , TO_CHAR(q.qs_created_date, 'MONTH') mnth_name
    FROM disco_us.eul5_documents d
    JOIN disco_us.eul5_qpp_stats q ON d.doc_name =     q.qs_doc_name 
    JOIN applsys.fnd_user u1       ON q.qs_doc_owner = u1.user_name
    JOIN applsys.fnd_user u2       ON q.qs_created_by = '#' || u2.user_id 
GROUP BY TO_CHAR(q.qs_created_date, 'RRRR-MM')
       , TO_CHAR(q.qs_created_date, 'MONTH')
ORDER BY TO_CHAR(q.qs_created_date, 'RRRR-MM') DESC;

-- total run time per report

  SELECT COUNT(*) tally
       , SUM(q.qs_act_elap_time) ttl_secs
       , d.doc_name
    FROM disco_us.eul5_documents d
    JOIN disco_us.eul5_qpp_stats q ON d.doc_name =     q.qs_doc_name 
    JOIN applsys.fnd_user u2       ON u2.user_id = REPLACE(q.qs_created_by, '#', '')
    WHERE q.qs_created_date > '20-MAY-2016' 
 GROUP BY d.doc_name;

-- total run count

SELECT trunc(q.qs_created_date) run_date
     , COUNT(*) ct
  FROM disco_us.eul5_qpp_stats q
  JOIN applsys.fnd_user fu ON q.qs_created_by = '#' || fu.user_id
 WHERE 1 = 1
   AND q.qs_created_date > '20-MAY-2016'
GROUP BY trunc(q.qs_created_date) 
ORDER BY 1 DESC;

Usage Per Report Including Last Used

-- ##############################################################################
--       USAGE PER REPORT INCLUDING LAST USED
-- ############################################################################*/

SELECT q.qs_doc_name
     , COUNT(*) usage_count
     , MAX(q.qs_created_date) last_used
  FROM disco_us.eul5_qpp_stats q
 WHERE q.qs_doc_name = 'My Test Report'
GROUP BY q.qs_doc_name
ORDER BY q.qs_doc_name;

Discoverer Reports – Usage – Scheduled

The schema name for your disco reports might not be the same as those used in these SQL statements. I’ve used disco_us but yours might be different, e.g. noetix_eul.

Sections:

  1. Scheduled Reports – Report Runs – Details
  2. Usage Data – Scheduled Reports
  3. Scheduled Reports – Total Run Times Per Month
  4. Scheduled Reports – Currently Running Scheduled Jobs
  5. Scheduled Reports – Cancelling Options

Each time a Scheduled Report runs, the details are stored in the eul5_br_runs table, compared to the eul5_qpp_stats table for a job which is manually submitted.

Scheduled Reports – Report Runs – Details

-- ##############################################################################
--       SCHEDULED REPORTS - REPORT RUNS - DETAILS
-- ############################################################################*/

-- https://community.oracle.com/thread/709670?start=0&tstart=0

-- Version 1

    SELECT a.eu_username "Disco User Name"
         , u.user_name "User Name"
         , u.description "User Description"
         , b.br_workbook_name "Workbook Name"
         , b.br_id "Workbook ID"
         , r.brr_run_number "Run Number"
         , r.brr_run_date "Run Date"
         , r.brr_act_elap_time "Run Time Secs"
         , DECODE(r.brr_state, 1, 'Scheduled'
                             , 2, 'Running Query'
                             , 3, 'Error Submitting'
                             , 4, 'Error While Running Query'
                             , 5, 'Report Deleted'
                             , 6, 'EUL has Changed'
                             , 7, 'Report Expired'
                             , 8, 'Reached Max Rows Limit'
                             , 9, 'Report Ready'
                             , 'Other') state
      FROM disco_us.eul5_batch_reports b
      JOIN disco_us.eul5_br_runs r      ON r.brr_br_id = b.br_id
      JOIN disco_us.eul5_eul_users a    ON a.eu_id =     b.br_eu_id
 LEFT JOIN applsys.fnd_user u           ON a.eu_username = '#' || u.user_id
     WHERE 1 = 1
--       AND r.brr_run_date > '01-JAN-2016'
--       AND u.user_name = 'SYSADMIN'
       AND r.brr_state = 1
       AND 1 = 1;

-- Version 2

    SELECT d.doc_id
         , d.doc_name
         , d.doc_created_date cr_dt
         , fu1.description cr_by
         , fu1.user_name
         , fu1.end_date
         , ebr.br_created_date sched_cr_dt1
         , TO_CHAR(ebr.br_created_date, 'YYMMDDHHMISS') sched_cr_dt2
         , ebr.br_id
         , ebr.br_job_id
         , ebr2.brr_id
         , TO_CHAR(TRUNC(ebr2.brr_act_elap_time/3600),'FM9900') || ':' || TO_CHAR(TRUNC(MOD(ebr2.brr_act_elap_time,3600)/60),'FM00') || ':' || TO_CHAR(MOD(ebr2.brr_act_elap_time,60),'FM00') run_time -- http://stackoverflow.com/questions/11003918/oracle-convert-seconds-to-hoursminutesseconds
--         , ebr2.brr_act_elap_time run_secs
--         , round((ebr2.brr_act_elap_time/60),2) run_mins
--         , round((ebr2.brr_act_elap_time/60)/60,2) run_hrs
         , DECODE(ebr2.brr_state, 1, 'Scheduled'
                                , 2, 'Running Query'
                                , 3, 'Error Submitting'
                                , 4, 'Error While Running Query'
                                , 5, 'Report Deleted'
                                , 6, 'EUL has Changed'
                                , 7, 'Report Expired'
                                , 8, 'Reached Max Rows Limit'
                                , 9, 'Report Ready'
                                , 'Other') state
         , ebr2.brr_run_date job_start
         , CASE WHEN ebr2.brr_act_elap_time IS NOT NULL THEN ebr2.brr_run_date + ((ebr2.brr_act_elap_time)  / (24 * 60 * 60)) END job_end 
--         , j.job
      FROM disco_us.eul5_documents d
      JOIN applsys.fnd_user fu1 ON '#' || fu1.user_id = doc_created_by
      JOIN disco_us.eul5_batch_reports ebr ON d.doc_name = ebr.br_name
 LEFT JOIN disco_us.eul5_br_runs ebr2 ON ebr2.brr_br_id = ebr.br_id
-- LEFT JOIN sys.dba_jobs j ON j.job = ebr.br_job_id
     WHERE 1 = 1
       AND d.doc_batch = 1
--       AND ebr2.brr_run_date > '30-APR-2016'
--       AND fu1.user_name = 'MTFSSZJR'
--       AND TO_CHAR(ebr.br_created_date, 'YYMMDDHHMI') = '1602030943'
--       AND TO_CHAR(ebr.br_created_date, 'YYMMDDHHMI') IN ('1504130934','1504130941','1506291148','1507311620','1508130858','1602030943')
--       AND d.doc_name LIKE '%Monthly Age Debt Report%'
       AND ebr2.brr_state = 2
       AND 1 = 1
  ORDER BY ebr2.brr_run_date DESC;

-- Summary usage per user

    SELECT fu1.user_name
         , fu1.description
         , COUNT(*) ct
      FROM disco_us.eul5_documents d
      JOIN applsys.fnd_user fu1 ON '#' || fu1.user_id = doc_created_by
      JOIN disco_us.eul5_batch_reports ebr ON d.doc_name = ebr.br_name
 LEFT JOIN disco_us.eul5_br_runs ebr2 ON ebr2.brr_br_id = ebr.br_id
-- LEFT JOIN sys.dba_jobs j ON j.job = ebr.br_job_id
     WHERE 1 = 1
       AND d.doc_batch = 1
--       AND ebr2.brr_run_date > '30-APR-2016'
       AND 1 = 1
  GROUP BY fu1.user_name
         , fu1.description;

Usage Data – Scheduled Reports

-- ##############################################################################
--       USAGE DATA - SCHEDULED REPORTS
-- ############################################################################*/

-- stats scheduled

    SELECT COUNT(*) report_run_count
         , SUM(r.brr_act_elap_time) total_seconds
         , TO_CHAR(r.brr_run_date, 'RRRR-MM') month_year
         , TO_CHAR(r.brr_run_date, 'MONTH') month_name
      FROM disco_us.eul5_batch_reports b
         , disco_us.eul5_br_runs r
         , disco_us.eul5_eul_users a
         , applsys.fnd_user u
     WHERE a.eu_id = b.br_eu_id
       AND r.brr_br_id = b.br_id
       AND a.eu_username = '#' || u.user_id
  GROUP BY TO_CHAR(r.brr_run_date, 'RRRR-MM')
         , TO_CHAR(r.brr_run_date, 'MONTH')
  ORDER BY TO_CHAR(r.brr_run_date, 'RRRR-MM') DESC;

Scheduled Reports – Total Run Times Per Month

-- ##############################################################################
--       SCHEDULED REPORTS - total run times per month
-- ############################################################################*/

SELECT   COUNT(*) tally
       , SUM(q.brr_act_elap_time) ttl_secs
       , TO_CHAR(q.brr_run_date, 'RRRR-MM') mnth
       , TO_CHAR(q.brr_run_date, 'MONTH') mnth_name
    FROM disco_us.EUL5_BR_RUNS q
    JOIN APPLSYS.FND_USER U ON REPLACE(q.brr_updated_by, '#', '') = u.user_id
GROUP BY TO_CHAR(q.brr_run_date, 'RRRR-MM')
       , TO_CHAR(q.brr_run_date, 'MONTH')
ORDER BY TO_CHAR(q.brr_run_date, 'RRRR-MM') DESC;

SCHEDULED REPORTS – currently running scheduled jobs

-- ##############################################################################
--       SCHEDULED REPORTS - currently running scheduled jobs
-- ############################################################################*/

-- http://blog.contractoracle.com/2008/08/check-for-scheduled-and-running.html

    SELECT /*+ ordered */ j.sid 
         , r.br_job_id
         , f.user_name
         , f.description
         , r.br_workbook_name
         , ses.username
         , ses.machine
         , ses.module
      FROM disco_us.eul5_batch_reports r
 LEFT JOIN applsys.fnd_user f       ON f.user_id = SUBSTR(r.br_created_by,2,4)
      JOIN sys.dba_jobs_running j   ON j.job =     r.br_job_id
      JOIN v$session ses            ON j.sid =     ses.sid
     WHERE 1 = 1; 

-- all scheduled reports

    SELECT f.user_name
         , f.description
         , r.br_workbook_name
         , r.br_job_id
         , r.br_next_run_date
      FROM disco_us.eul5_batch_reports r
         , applsys.fnd_user f
         , sys.dba_jobs j
     WHERE f.user_id = SUBSTR(r.br_created_by,2,4)
       AND j.job = r.br_job_id
  ORDER BY r.br_next_run_date ASC;

Scheduled Reports – Cancelling Options

-- ##############################################################################
--       SCHEDULED REPORTS - CANCELLING OPTIONS
-- ############################################################################*/

-- How to Cancel or Delete a Scheduled Discoverer Report When Running (Doc ID 429415.1)
-- https://support.oracle.com/rs?type=doc&id=429415.1

    SELECT 'UPDATE eul5_br_runs SET brr_state = 4 WHERE brr_id = ' || ebr2.brr_id || ';' update_sql
      FROM disco_us.eul5_documents d
      JOIN disco_us.eul5_batch_reports ebr ON d.doc_name = ebr.br_name
      JOIN disco_us.eul5_br_runs ebr2 ON ebr2.brr_br_id = ebr.br_id
     WHERE 1 = 1
       AND d.doc_batch = 1
       AND ebr2.brr_state = 2
       AND 1 = 1;

UPDATE eul5_br_runs SET brr_state = 4 WHERE brr_id = 3773601;
UPDATE eul5_br_runs SET brr_state = 4 WHERE brr_id = 3773613;
COMMIT();

Discoverer Reports – Sharing Details

The schema name for your disco reports might not be the same as those used in these SQL statements. I’ve used disco_us but yours might be different, e.g. noetix_eul.

Sections:

  1. Shared With A User
  2. Shared With A Responsibility
  3. Access via Responsibility Assigned To User
  4. User and Responsibility Combined

Disco Reports can be shared with a user or with a responsibility. Here are some SQLs to find out sharing info.

Shared With A User

   -- ##############################################################################
--       SHARED WITH A USER
-- ############################################################################*/

-- Shared with a User

    SELECT fu.user_name
         , docs.doc_name
         , NULL responsibility
         , 'User' access_level
      FROM disco_us.eul5_documents docs
      JOIN disco_us.eul5_access_privs priv   ON docs.doc_id =     priv.gd_doc_id
      JOIN disco_us.eul5_eul_users usr       ON priv.ap_eu_id =   usr.eu_id
      JOIN applsys.fnd_user fu               ON usr.eu_username = '#' || fu.user_id
     WHERE 1 = 1
       AND usr.eu_role_flag = 0
       AND fu.user_name = 'SYSADMIN'
       AND 1 = 1;

Shared With A Responsibility

-- ##############################################################################
--       SHARED WITH A RESPONSIBILITY
-- ############################################################################*/

-- Shared with a responsibility

    SELECT docs.doc_name
         , fnd.responsibility_name
         , DECODE (usr.eu_role_flag,  0, 'user',  1, 'role') user_role
      FROM disco_us.eul5_documents docs
      JOIN disco_us.eul5_access_privs priv   ON docs.doc_id =     priv.gd_doc_id
      JOIN disco_us.eul5_eul_users usr       ON priv.ap_eu_id =   usr.eu_id
      JOIN applsys.fnd_responsibility_tl fnd ON usr.eu_username = '#' || fnd.responsibility_id || '#' || fnd.application_id
     WHERE 1 = 1
       AND fnd.responsibility_name = 'System Administrator'
       AND 1 = 1;

Access Via Responsibility Assigned To User

-- ##############################################################################
--       ACCESS VIA RESPONSIBILITY ASSIGNED TO USER
-- ############################################################################*/

-- by responsibility assigned to user

    SELECT docs.doc_name
         , fu.user_name
         , DECODE (usr.eu_role_flag,  0, 'user',  1, 'role') user_role
      FROM disco_us.eul5_documents docs
      JOIN disco_us.eul5_access_privs priv   ON docs.doc_id =     priv.gd_doc_id
      JOIN disco_us.eul5_eul_users usr       ON priv.ap_eu_id =   usr.eu_id
 LEFT JOIN applsys.fnd_responsibility_tl fnd ON usr.eu_username = '#' || fnd.responsibility_id || '#' || fnd.application_id
 LEFT JOIN applsys.fnd_user fu               ON usr.eu_username = '#' || fu.user_id
     WHERE 1 = 1
       AND fu.user_name = 'SYSADMIN'
       AND 1 = 1;

User And Responsibility Combined

-- ##############################################################################
--       USER AND RESPONSIBILITY COMBINED
-- ############################################################################*/

-- What a user has access to, whether via direct share against user, or against the resp. the user has access to

    SELECT fu.user_name
         , NULL responsibility
         , docs.doc_name
         , 'User' access_level
      FROM disco_us.eul5_documents docs
      JOIN disco_us.eul5_access_privs priv   ON docs.doc_id =     priv.gd_doc_id
      JOIN disco_us.eul5_eul_users usr       ON priv.ap_eu_id =   usr.eu_id
      JOIN applsys.fnd_user fu               ON usr.eu_username = '#' || fu.user_id
     WHERE 1 = 1
       AND usr.eu_role_flag = 0
       AND fu.user_name = :un
    UNION
    SELECT NULL user_name
         , fnd.responsibility_name responsibility
         , docs.doc_name
         , 'Resp' access_level
      FROM disco_us.eul5_documents docs
      JOIN disco_us.eul5_access_privs priv   ON docs.doc_id =    priv.gd_doc_id
      JOIN disco_us.eul5_eul_users usr       ON  priv.ap_eu_id = usr.eu_id
      JOIN applsys.fnd_responsibility_tl fnd ON usr.eu_username = '#' || fnd.responsibility_id || '#' || fnd.application_id
     WHERE fnd.responsibility_name IN (SELECT frt.responsibility_name
                                         FROM applsys.fnd_user fu
                                           , apps.fnd_user_resp_groups_direct furg
                                           , applsys.fnd_responsibility_tl frt
                                       WHERE frt.responsibility_id = furg.responsibility_id
                                         AND furg.user_id = fu.user_id
                                         AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE
                                         AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
                                         AND fu.user_name = :un);
ORDER BY 1, 2;

Discoverer Reports – Scheduled – Basic Info

The schema name for your disco reports might not be the same as those used in these SQL statements. I’ve used disco_us but yours might be different, e.g. noetix_eul.

Sections:

  1. Scheduled Reports – Basic Details
  2. Scheduled Reports – Not Run Yet
  3. Scheduled Reports – Count Summary Data

This page lists some SQLs which are useful for checking the details of scheduled reports – covering details about the schedules, rather than the specific instances of the scheduled jobs running. For information about that, have a look at Disco Reports – Usage – Scheduled.

Scheduled Reports – Basic Details

-- ##############################################################################
--     SCHEDULED REPORTS - BASIC DETAILS
-- ############################################################################*/

-- If a user has access to schedule the running of Disco Reports, the details are stored in various tables.

-- The eul5_batch_reports contains the details of the scheduled reports, including when the report will next run
---- and when the schedule was first created (BR_CREATED_DATE)

SELECT ebr.br_name
     , TO_CHAR(ebr.br_created_date, 'YYMMDDHHMISS') cr_dt
     , ebr.br_workbook_name
     , ebr.br_next_run_date
     , fu.user_name
     , fu.description
     , ebr.*
  FROM disco_us.eul5_batch_reports ebr
  JOIN applsys.fnd_user fu ON ebr.br_created_by = '#' || fu.user_id 
 WHERE 1 = 1
--   AND fu.user_name = 'MTFSSPO4'
--   AND TO_CHAR(ebr.br_created_date, 'YYMMDD') = '150731'
 ORDER BY ebr.br_next_run_date DESC;

-- However, the data is also stored in the "eul5_documents", and the doc_batch value = 1 compared to 0 for non scheduled reports:

        SELECT d.doc_id
             , d.doc_name
             , d.doc_created_date
             , d.doc_created_by
             , fu_cr.description created_by_person
             , d.doc_updated_date
             , d.doc_updated_by
             , fu_up.description updatd_by_person
             , DECODE(d.doc_batch, 0, 'Standard', 1, 'Scheduled') doc_type
             , '###########'
             , d.doc_developer_key
             , d.doc_description
             , d.doc_eu_id
             , d.doc_length
             , d.doc_batch
             , d.doc_folder_id
             , d.doc_content_type
             , d.doc_document
             , d.doc_user_prop2
             , d.doc_user_prop1
             , d.doc_element_state
             , d.notm
          FROM disco_us.eul5_documents d
     LEFT JOIN applsys.fnd_user fu_cr ON '#' || fu_cr.user_id = d.doc_created_by
     LEFT JOIN applsys.fnd_user fu_up ON '#' || fu_up.user_id = d.doc_updated_by
         WHERE 1 = 1
           AND d.doc_batch = 1
--           AND fu_cr.user_name = 'TESTUSER'
           AND 1 = 1;

Scheduled Reports – Not Run Yet

-- ##############################################################################
--      SCHEDULED REPORTS - NOT RUN YET
-- ############################################################################*/

    SELECT a.eu_username "Disco User Name"
         , u.user_name "User Name"
         , u.description "User Description"
         , b.br_id
         , b.br_name "Workbook Name"
         , b.br_created_date "Created Date"
         , b.br_next_run_date "Next Scheduled Run Time"
         , 'Every ' || b.br_num_freq_units || ' ' ||
                DECODE(b.br_rfu_id,
                2000,'Minutes',
                2001,'Hours',
                2002,'Days',
                2003,'Weeks',
                2004,'Months','Years') "Often"
      FROM disco_us.eul5_batch_reports b
      JOIN disco_us.eul5_eul_users A ON b.br_eu_id=a.eu_id
 LEFT JOIN applsys.fnd_user u ON a.eu_username = '#' || u.user_id
     WHERE 1 = 1
--       AND u.user_name = 'TESTUSER'
       AND 1 = 1; 

    SELECT u.user_name "User Name"
         , u.description
         , u.user_id
         , u.end_date
         , count(*) ct
      FROM disco_us.eul5_batch_reports b
      JOIN disco_us.eul5_eul_users A ON b.br_eu_id=a.eu_id
 LEFT JOIN applsys.fnd_user u ON a.eu_username = '#' || u.user_id
  GROUP BY u.user_name, u.description, u.user_id, u.end_date;

Scheduled Reports – Count Summary Data

-- ##############################################################################
--      SCHEDULED REPORTS - COUNT SUMMARY DATA
-- ############################################################################*/

    SELECT u.user_name "User Name"
         , u.description "User Description"
         , u.end_date
         , count(*) ct
      FROM disco_us.eul5_batch_reports b
      JOIN disco_us.eul5_br_runs r      ON r.brr_br_id = b.br_id
      JOIN disco_us.eul5_eul_users a    ON a.eu_id =     b.br_eu_id
 LEFT JOIN applsys.fnd_user u           ON a.eu_username = '#' || u.user_id
  GROUP BY u.user_name, u.description, u.end_date;

    SELECT u.user_name "User Name"
         , u.description "User Description"
         , u.end_date
         , u.last_update_date
         , count(distinct b.br_name) rpt_types
         , count(*) ct
      FROM disco_us.eul5_batch_reports b
      JOIN disco_us.eul5_br_runs r      ON r.brr_br_id = b.br_id
      JOIN disco_us.eul5_eul_users a    ON a.eu_id =     b.br_eu_id
 LEFT JOIN applsys.fnd_user u           ON a.eu_username = '#' || u.user_id
  GROUP BY u.user_name, u.description, u.end_date, u.last_update_date
ORDER BY 6 DESC;

Discoverer Reports – Main Components

The schema name for your disco reports might not be the same as those used in these SQL statements. I’ve used disco_us but yours might be different, e.g. noetix_eul.

Sections:

  1. Folders By Business Area
  2. Folders, Columns, Business Areas Etc
  3. List Folders, Items and Joins Included in a Document
  4. List of Values
  5. Miscellaneous

Folders By Business Area

-- ##############################################################################
--       FOLDERS BY BUSINESS AREA
-- ############################################################################*/

        SELECT d.doc_id
             , d.doc_name
             , d.doc_created_date
             , d.doc_created_by
             , fu_cr.description created_by_person
             , d.doc_updated_date
             , d.doc_updated_by
             , fu_up.description updatd_by_person
             , DECODE(d.doc_batch, 0, 'Standard', 1, 'Scheduled') doc_type
             , '###########'
             , d.doc_developer_key
             , d.doc_description
             , d.doc_eu_id
             , d.doc_length
             , d.doc_batch
             , d.doc_folder_id
             , d.doc_content_type
             , d.doc_document
             , d.doc_user_prop2
             , d.doc_user_prop1
             , d.doc_element_state
             , d.notm
          FROM disco_us.eul5_documents d
     LEFT JOIN applsys.fnd_user fu_cr ON '#' || fu_cr.user_id = d.doc_created_by
     LEFT JOIN applsys.fnd_user fu_up ON '#' || fu_up.user_id = d.doc_updated_by
         WHERE 1 = 1
           AND d.doc_batch = 0
--           AND d.doc_name LIKE '701%'
--           AND d.doc_id = 3284566
           AND doc_updated_date > '22-MAR-2016'
           AND 1 = 1;

Folders, Columns, Business Areas Etc

-- ##############################################################################
--        FOLDERS, COLUMNS, BUSINESS AREAS etc
-- ############################################################################*/

-- folder, column, item class

    SELECT folders.obj_name folder
         , folders.obj_updated_date
         , cols.exp_name column_name
         , ed.dom_name item_class
         , cols.it_format_mask format
         , cols.exp_updated_date col_updated_date
      FROM disco_us.eul5_expressions cols
      JOIN disco_us.eul5_objs folders on cols.it_obj_id = folders.obj_id
 LEFT JOIN disco_us.eul5_domains ed   on cols.it_dom_id = ed.dom_id
     WHERE folders.obj_name = 'GL Journal Details'
  ORDER BY cols.exp_updated_date DESC;

-- document, folder, business area
-- duplicates returned when a folder is shared with more than business area

    SELECT DISTINCT
           doc_name
         , obj.obj_name folder_name
         , obj.obj_developer_key
         , obj.obj_updated_date
         , bas.ba_name
         , doc.doc_updated_date
      FROM disco_us.eul5_documents doc
 LEFT JOIN disco_us.eul5_elem_xrefs xref  ON doc.doc_id =     xref.ex_from_id
 LEFT JOIN disco_us.eul5_expressions xpr  ON xref.ex_to_id =  xpr.exp_id
 LEFT JOIN disco_us.eul5_objs obj         ON obj.obj_id =     xpr.it_obj_id
 LEFT JOIN disco_us.eul5_ba_obj_links bol ON bol.bol_obj_id = obj.obj_id
 LEFT JOIN disco_us.eul5_bas bas          ON bas.ba_id =      bol.bol_ba_id
     WHERE 1 = 1
--       AND doc.doc_name LIKE '242%'
       AND doc.doc_name = 'AR Transaction Details'
    ORDER BY doc.doc_updated_date DESC;

-- https://community.oracle.com/thread/2188681

  SELECT DECODE (eb.ba_created_by, 'ORACLE_APPS', 'Seeded', 'Custom') Origin,
         eb.ba_name business_area,
         eo.OBJ_TYPE,
         DECODE (eo.obj_type,
                 'SOBJ', 'From Database',
                 'CUO', 'Custom Query',
                 'COBJ', 'Complex Folder',
                 eo.obj_type)
            obj_type_desc,
         eo.OBJ_NAME folder,
         eo.OBJ_DESCRIPTION folder_description,
         DECODE (eo.sobj_ext_table,
                 NULL, NULL,
                 eo.obj_ext_owner || '.' || eo.sobj_ext_table)
            base_object,
         DECODE (
            es.seg_obj_id,
            NULL, UPPER (
                        TRIM (eo.obj_object_sql1)
                     || TRIM (eo.obj_object_sql2)
                     || TRIM (eo.obj_object_sql3)),
            UPPER (
                  TRIM (es.seg_chunk1)
               || TRIM (es.seg_chunk2)
               || TRIM (es.seg_chunk3)
               || TRIM (es.seg_chunk4)))
            SQL
    FROM disco_us.EUL5_BAS eb,
         disco_us.EUL5_objs eo,
         disco_us.EUL5_BA_OBJ_LINKS ebol,
         disco_us.eul5_segments es
   WHERE     eb.BA_ID = ebol.BOL_BA_ID
         AND ebol.BOL_OBJ_ID = eo.OBJ_ID
         AND eo.obj_id = es.seg_obj_id(+)
         AND DECODE (eb.ba_created_by, 'ORACLE_APPS', 'Seeded', 'Custom') = 'Custom'
         AND eo.OBJ_NAME LIKE '%BUCKETS%'
ORDER BY /*decode(eb.ba_created_by,'ORACLE_APPS','Seeded','Custom'), */
         eb.ba_name, ebol.BOL_SEQUENCE, es.seg_sequence;

List Folders, Items and Joins Included in a Document

I can’t remember where I found this piece of SQL but it is useful in that it displays all of the details for a document, providing a list of:

  1. Folders
  2. Items (fields)
  3. Worksheet Joins
-- ##############################################################################
--        DISCO REPORTS AND FOLDERS
-- ############################################################################*/

  SELECT DISTINCT qs_doc_name report_name
                , qs_doc_details work_sheet
                , (disco_us.eul5_get_object_name (
                      SUBSTR (qs_object_use_key
                            , 1
                            , DECODE (INSTR (qs_object_use_key
                                           , 'S'
                                           , 1
                                           , 1)
                                    , 0, LENGTH (qs_object_use_key)
                                    , INSTR (qs_object_use_key
                                           , 'S'
                                           , 1
                                           , 1)
                                      - 2))
                    , 'F'))
                     folders_used
                , (disco_us.eul5_get_object_name (
                      disco_us.eul5_get_item_name (qs_id)
                    , 'I'))
                     items_used
                , (disco_us.eul5_get_object_name (
                      disco_us.eul5_get_forj_itemid (qs_id)
                    , 'I'))
                     items_used_in_condition
                , (disco_us.eul5_get_object_name ( (disco_us.eul5_get_forj_itemid (
                                                       qs_id
                                                     , 'JOIN'))
                                                , 'J'))
                     worksheet_joins
    FROM (  SELECT qs_doc_name
                 , qs_doc_details
                 , MAX (qs_id) qs_id
                 , qs_object_use_key
              FROM disco_us.eul5_qpp_stats
             WHERE (qs_doc_name) = 'AR Transaction Details'
          GROUP BY qs_doc_name
                 , qs_doc_details
                 , qs_object_use_key)
ORDER BY qs_doc_name ASC;

List of Values

Provides details about List of Values, and which folder and Item Name they are linked to.

-- ##############################################################################
--      LIST OF VALUES
-- ############################################################################*/

  SELECT DOM.DOM_NAME "LOV Name",
         OBJ.OBJ_NAME "Folder Name",
         EXP.EXP_NAME "Item Name"
    FROM disco_us.EUL5_BA_OBJ_LINKS BOL 
    JOIN disco_us.EUL5_BAS BA          ON BOL.BOL_BA_ID = BA.BA_ID
    JOIN disco_us.EUL5_OBJS OBJ        ON BOL.BOL_OBJ_ID = OBJ.OBJ_ID
    JOIN disco_us.EUL5_EXPRESSIONS EXP ON EXP.IT_OBJ_ID = OBJ.OBJ_ID
    JOIN disco_us.EUL5_DOMAINS DOM     ON EXP.IT_DOM_ID = DOM.DOM_ID
   WHERE BA_NAME = 'Enterprise Asset Management'
--     AND DOM_NAME LIKE '%Type%'
ORDER BY 2,3;

Miscellaneous

-- ##############################################################################
--      MISCELLANEOUS
-- ############################################################################*/

-- Contains joins between Business Areas and Folders (objects), sort order
select * from disco_us.EUL5_BA_OBJ_LINKS ebol
where ebol.bol_ba_id = 3145488;

-- Master Table for Business Areas, Names, Descriptions, Keys, etc
select * from disco_us.EUL5_BAS;

select * from disco_us.EUL5_OBJ_DEPS;

-- Contains joins between Folders (objects) within the Business Areas ?
select * from disco_us.EUL5_OBJ_JOIN_USGS;

-- Master Table for Folders contained in the Business Areas
select * from disco_us.EUL5_OBJS where obj_id = 328871;

-- Field (item) information related to Folders (objects) ?
select * from disco_us.EUL5_EXPRESSIONS;

select * from disco_us.EUL5_EXP_DEPS;

-- Contains information on Folder (objects) Joins ?
select * from disco_us.EUL5_KEY_CONS;

-- Contains EUL Version information
select * from disco_us.EUL5_VERSIONS;

Discoverer Reports – Report Details

The schema name for your disco reports might not be the same as those used in these SQL statements. I’ve used disco_us but yours might be different, e.g. noetix_eul.

-- ##############################################################################
--       DISCO REPORTS - DOCUMENT DETAILS
-- ############################################################################*/

        SELECT d.doc_id
             , d.doc_name
             , d.doc_created_date
             , d.doc_created_by
             , fu_cr.description created_by_person
             , d.doc_updated_date
             , d.doc_updated_by
             , fu_up.description updatd_by_person
             , DECODE(d.doc_batch, 0, 'Standard', 1, 'Scheduled') doc_type
             , '###########'
             , d.doc_developer_key
             , d.doc_description
             , d.doc_eu_id
             , d.doc_length
             , d.doc_batch
             , d.doc_folder_id
             , d.doc_content_type
             , d.doc_document
             , d.doc_user_prop2
             , d.doc_user_prop1
             , d.doc_element_state
             , d.notm
          FROM disco_us.eul5_documents d
     LEFT JOIN applsys.fnd_user fu_cr ON '#' || fu_cr.user_id = d.doc_created_by
     LEFT JOIN applsys.fnd_user fu_up ON '#' || fu_up.user_id = d.doc_updated_by
         WHERE 1 = 1
           AND d.doc_batch = 0
--           AND d.doc_name LIKE '701%'
--           AND d.doc_id = 3284566
           AND doc_updated_date > '22-MAR-2016'
           AND 1 = 1;