Oracle Discoverer holds a set of database tables for each Discoverer EUL being used. In the following queries, the prefix eul_owner should be replaced by the database owner corresponding to the specific EUL in question.
The following reporting statistics may be of interest…
1. Discoverer Reports run in the last year (i.e. since SYSDATE-365):
SELECT qs.qs_doc_name report_run , qs.qs_doc_details report_detail , qs.qs_created_date date_run , fnd.user_name user_name FROM <eul_owner>.eul5_qpp_stats qs , fnd_user fnd WHERE 1 = 1 AND fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','') -- AND user_name = 'SYSADMIN' AND qs.qs_created_date > SYSDATE - 365 ORDER BY qs.qs_created_date DESC ;
2. Discoverer Report usage by USER:
SELECT COUNT(*) num_reports , fnd.user_name user_name , ppx.full_name employee_name FROM <eul_owner>.eul5_qpp_stats qs , fnd_user fnd , per_people_x ppx WHERE 1 = 1 AND fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','') AND ppx.person_id(+) = fnd.employee_id -- AND user_name = 'SYSADMIN' AND qs.qs_created_date > SYSDATE - 365 GROUP BY fnd.user_name , ppx.full_name ORDER BY COUNT(*) DESC ;
3. Discoverer Report usage by REPORT:
SELECT COUNT(*) num_reports , qs.qs_doc_name report_run , qs.qs_doc_details report_detail FROM <eul_owner>.eul5_qpp_stats qs , fnd_user fnd , per_people_x ppx WHERE 1 = 1 AND fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','') AND ppx.person_id(+) = fnd.employee_id -- AND user_name = 'SYSADMIN' AND qs.qs_created_date > SYSDATE - 365 GROUP BY qs.qs_doc_name , qs.qs_doc_details ORDER BY COUNT(*) DESC ;
4. Discoverer Report ownership and assignment (users/responsibilities):
SELECT DISTINCT a.doc_name disco_report , fu_owner.user_name owner , d.responsibility_key responsbility , fu.user_name , ppx.full_name employee_name FROM <eul_owner>.eul5_documents a , <eul_owner>.eul5_access_privs b , <eul_owner>.eul5_eul_users c , fnd_responsibility d , fnd_user fu , fnd_user fu_owner , per_people_x ppx WHERE a.doc_id = b.gd_doc_id(+) AND b.ap_type(+) = 'GD' AND b.ap_eu_id = c.eu_id(+) AND REPLACE(c.eu_username,'#','') = TO_CHAR(d.responsibility_id(+)) || TO_CHAR(d.application_id(+)) AND TO_CHAR(fu.user_id(+)) = REPLACE(c.eu_username,'#','') AND TO_CHAR(fu_owner.user_id(+)) = REPLACE(a.doc_created_by,'#','') AND NVL(fu.end_date(+),SYSDATE + 1) > SYSDATE AND NVL(fu_owner.end_date(+),SYSDATE + 1) > SYSDATE AND ppx.person_id(+) = fu.employee_id AND ( fu_owner.user_name = <user> OR fu.user_name = <user> ) ORDER BY a.doc_name ASC ;
What is the FND tables?
Super helpful queries, thanks so much for providing them!