Database Query for Discoverer


Database Query for Discoverer

All discoverer folders and workbooks that you create get stored in database. You can use the below queries to find the details.

Find your Discoverer Workbook and Folder Details from Database:

select  fu.user_name owner,
        doc.doc_id "Document Id",
        doc.doc_name "Document Name",
        doc.doc_developer_key,
        doc.doc_folder_id,
        doc.doc_created_by,
        doc.doc_created_date,
        doc.doc_updated_by,
        doc.doc_updated_date,
        min(qs.qs_created_date) first_acc,
        max(qs.qs_created_date) Last_Acc
from    disco_apps.eul5_documents doc,
        apps.fnd_user fu,
        disco_apps.eul5_qpp_stats qs
where   '#'||fu.user_id = doc.doc_created_by
        and qs.qs_doc_name=doc.doc_name
        and qs.qs_doc_owner=fu.user_name
        and doc.doc_created_date<qs.qs_created_date
group by fu.user_name,
        doc.doc_id,
        doc.doc_name,
        doc.doc_developer_key,
        doc.doc_folder_id,
        doc.doc_created_by,
        doc.doc_created_date,
        doc.doc_updated_by,
        doc.doc_updated_date
order by doc.doc_updated_date desc;

Find your Discoverer Workbook performance for different Business Areas:

select  ba.ba "Business Area",
        usr.user_name,
        doc.doc_name "Work Book",
        min(qs_act_elap_time) "Fastest",
        max(qs_act_elap_time) "Slowest",
        round(avg(qs_act_elap_time),2) "AVG (s)",
        round(avg(qs_act_elap_time)/60,2) "AVG (m)",
        count(*) "Often",
        doc.doc_created_date,
        min(acc.qs_created_date) "First",
        max(acc.qs_created_date) "Last Access"
from    disco_apps.eul5_documents doc,
        apps.fnd_user usr,
        disco_apps.eul5_qpp_stats acc,
        (select distinct gd_doc_id from disco_apps.eul5_access_privs ) privs,
        (
          select distinct doc.doc_id,ba.ba_name ba
          from disco_apps.eul5_documents doc
          ,disco_apps.eul5_elem_xrefs eex
          ,disco_apps.eul5_ba_obj_links bol
          ,disco_apps.eul5_objs obj
          ,disco_apps.eul5_bas ba
          WHERE doc.doc_id = eex.ex_from_id
          AND eex.ex_to_par_name = obj.obj_name
          AND obj.obj_id = bol.bol_obj_id
          AND bol.bol_ba_id = ba.ba_id
        )ba
where   '#'||usr.user_id=doc.doc_created_by
        And doc.doc_name=acc.qs_doc_name
        And privs.gd_doc_id = doc.doc_id
        And usr.user_name = upper(acc.QS_DOC_OWNER)
        And doc.doc_created_date<acc.qs_created_date
        And doc.doc_id=BA.doc_id
group by ba.ba,
        usr.user_name,
        doc.doc_name,
        doc.doc_created_date;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: