/ Oracle EBS

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;