Useful Discoverer Queries

Query #1 - To List the Discoverer Reports

SELECT DOC_ID,
        DOC_NAME,
        DOC_DEVELOPER_KEY,
        DOC_CREATED_BY
   FROM EUL5_US.EUL5_DOCUMENTS
      --EUL_APPS.EUL4_DOCUMENTS
 
 
Query #2 - To List the Discoverer Reports and users having access to them

SELECT   ACCESS_PRIVS.AP_CREATED_DATE,
           DECODE (
              EUL_US.EUL5_GET_ISITAPPS_EUL,
              1,
              DECODE (
                 USERS.EU_ROLE_FLAG,
                 1,
                 EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'),
                 DECODE (USERS.EU_USERNAME,
                         NULL, 'Document Not Shared',
                         EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME))
              ),
              NVL (USERS.EU_USERNAME, 'Document Not Shared')
           ),
           DECODE (EUL_US.EUL5_GET_ISITAPPS_EUL,
                   1, EUL_US.EUL5_GET_APPS_USERRESP (DOCUMENTS.DOC_CREATED_BY),
                   DOCUMENTS.DOC_CREATED_BY),
           DOCUMENTS.DOC_DESCRIPTION,
           DOCUMENTS.DOC_NAME
    FROM   EUL_US.EUL5_ACCESS_PRIVS ACCESS_PRIVS,
           EUL_US.EUL5_DOCUMENTS DOCUMENTS,
           EUL_US.EUL5_EUL_USERS USERS
   WHERE ( (DOCUMENTS.DOC_ID = ACCESS_PRIVS.GD_DOC_ID(+))
            AND (USERS.EU_ID(+) = ACCESS_PRIVS.AP_EU_ID))
           AND (DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb')
           AND (DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb')
           AND (DECODE (
                   EUL_US.EUL5_GET_ISITAPPS_EUL,
                   1,
                   DECODE (
                      USERS.EU_ROLE_FLAG,
                      1,
                      EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'),
                      DECODE (
                         USERS.EU_USERNAME,
                         NULL,
                         'Document Not Shared',
                         EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME)
                      )
                   ),
                   NVL (USERS.EU_USERNAME, 'Document Not Shared')
                ) = &Responsibility_name)
ORDER BY   DOCUMENTS.DOC_NAME ASC,
           DOCUMENTS.DOC_CREATED_DATE ASC,
           DECODE (
              EUL_US.EUL5_GET_ISITAPPS_EUL,
              1,
              DECODE (
                 USERS.EU_ROLE_FLAG,
                 1,
                 EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'),
                 DECODE (USERS.EU_USERNAME,
                         NULL, 'Document Not Shared',
                         EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME))
              ),
              NVL (USERS.EU_USERNAME, 'Document Not Shared')
           ) ASC;

Query #3 - To List the Discoverer Reports along with the Folder names, Tables used and User details

SELECT DISTINCT b.doc_developer_key document_developer_key,
                b.doc_name document_name, a.qs_doc_details worksheet_name,
                c.obj_name folder_name,
                c.obj_developer_key folder_developer_key,
                d.ba_name business_unit, fu.user_name created_by
           FROM eul5_us.eul5_qpp_stats a,
                eul5_us.eul5_documents b,
                eul5_us.eul5_objs c,
                eul5_us.eul5_bas d,
                eul5_us.eul5_eul_users e,
                eul5_us.eul5_ba_obj_links f,
                fnd_user fu
          WHERE a.qs_doc_name = b.doc_name
            AND a.qs_doc_owner = e.eu_username
            AND INSTR (a.qs_object_use_key, c.obj_id) <> 0
            AND c.obj_id = f.bol_obj_id
            AND d.ba_id = f.bol_ba_id
            AND TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)
       ORDER BY b.doc_developer_key;

     
Query #4 - To List the Discoverer Workbooks along with the Responsibility details and Users having access

SELECT access_privs.ap_updated_date last_updated,
       documents.doc_name workbook_name,
       documents.doc_developer_key workbook_key,
       documents.doc_description workbook_descr,
       resp.responsibility_name shared_with_respo
  FROM eul_apps.eul5_access_privs access_privs,
       eul_apps.eul5_documents documents,
       eul_apps.eul5_eul_users users,
       apps.fnd_responsibility_tl resp
 WHERE documents.doc_id = access_privs.gd_doc_id
   AND users.eu_id = access_privs.ap_eu_id
   AND documents.doc_content_type = 'application/vnd.oracle-disco.wb'  
   AND users.eu_role_flag = 1
   AND users.eu_username =
                   '#' || resp.responsibility_id || '#' || resp.application_id
   AND resp.LANGUAGE = 'US';