Discoverer 相關 SQL
參考資料: 這裡
1. 查詢所有 report 清單
2. 統計使用率
3. 查詢所有 report 與 可使用權限
1. 查詢所有 report 清單
SELECT d.doc_id, d.doc_name, d.doc_description, d.doc_updated_date, DECODE(SUBSTR(d.doc_updated_by, 1, 1), '#', (SELECT fu.user_name FROM fnd_user fu WHERE '#' || fu.user_id = d.doc_created_by), d.doc_updated_by) doc_updated_by, d.doc_created_date, DECODE(SUBSTR(d.doc_created_by, 1, 1), '#', (SELECT fu.user_name FROM fnd_user fu WHERE '#' || fu.user_id = d.doc_created_by), d.doc_created_by) doc_created_by FROM eul_us.eul4_documents d WHERE d.doc_created_date > '15-JUN-2008' ORDER BY d.doc_id |
2. 統計使用率
SELECT DECODE(SUBSTR(a.qs_doc_owner, 1, 1), '#', (SELECT fu.user_name FROM fnd_user fu WHERE '#' || fu.user_id = a.qs_doc_owner), a.qs_doc_owner) owner, a.qs_doc_name workbook, a.qs_doc_details worksheet, COUNT(1) run_count, MAX(a.qs_created_date) last_used, MIN(a.qs_created_date) first_used FROM eul_us.eul4_qpp_stats a, eul_us.eul4_documents b WHERE a.qs_created_date > '01-JAN-2007' --ONLY recent reports AND a.qs_doc_name = b.doc_name GROUP BY a.qs_doc_owner, a.qs_doc_name, a.qs_doc_details ORDER BY run_count DESC |
3. 查詢所有 report 與 可使用權限
SELECT d.doc_id, fu.user_name workbook_owner, d.doc_name workbook_name, --, u.eu_username r.responsibility_name shared_to_resp, su.user_name shared_to_user --, ap.* FROM eul_us.eul4_access_privs ap, eul_us.eul4_documents d, eul_us.eul4_eul_users u, (SELECT ('#' || responsibility_id || '#' || application_id) ora_resp, responsibility_name FROM fnd_responsibility_tl) r, (SELECT ('#' || user_id) ora_user, user_name FROM fnd_user) su, fnd_user fu WHERE 1 = 1 AND ap.gd_doc_id IS NOT NULL AND ap.gd_doc_id = d.doc_id AND ap.ap_eu_id = u.eu_id AND r.ora_resp(+) = u.eu_username AND su.ora_user(+) = u.eu_username AND d.doc_created_by = '#' || fu.user_id ORDER BY 2, 3; |