How To Fix Missing Responsibilities For A User (Doc ID 429852.1)
Reply
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 ;
The following query will list database locks, but needs to be run as a user with the appropriate access to the v$… and dba_locks tables:
SELECT w.session_id waiting_session , h.session_id holding_session , w.lock_type --, h.mode_held --, w.mode_requested , ( SELECT module || ' :: ' || action FROM v$session WHERE sid=h.session_id ) holding_module_action , ( SELECT module || ' :: ' || action FROM v$session WHERE sid = w.session_id ) waiting_module_action FROM ( SELECT /*+ NO_MERGE */ * FROM dba_locks ) w , ( SELECT /*+ NO_MERGE */ * FROM dba_locks ) h WHERE (( (h.mode_held != 'None') AND (h.mode_held != 'Null') AND ( (h.mode_requested = 'None') OR (h.mode_requested = 'Null') ) ) AND (( (w.mode_held = 'None') OR (w.mode_held = 'Null') ) AND ( (w.mode_requested != 'None') AND (w.mode_requested != 'Null') ) ) ) AND w.lock_type = h.lock_type AND w.lock_id1 = h.lock_id1 AND w.lock_id2 = h.lock_id2 AND w.session_id != h.session_id ;