Chitika Add

Friday, July 18

Oracle Discoverer Reports and EUL Table

Everything is stored in EUL tables, i.e. tables under the EUL owner DB scheme (about 50 tables).

Reports are stored in the EUL5_DOCUMENTS table, I suppose (this is not documented so you can only guess)

But it is strongly recommended not to insert or update data in these tables directly using SQL commands!   You should run only SELECT on EUL tables.
 
/* List of Workbooks */

SELECT doc_name "Workbook",
doc_developer_key ,
doc_description "Description"
FROM eul_owner.eul5_documents -- in my case EUL_US.eul5_documents
WHERE eul5_documents.doc_name LIKE '%&enter_workbook_name%'
ORDER BY doc_name;

/*With respect to workbook usage, try this:*

SELECT a.QS_DOC_OWNER USERNAME,
  a.QS_DOC_NAME WORKBOOK ,
  a.QS_DOC_DETAILS WORKSHEET ,
  MAX(a.QS_CREATED_DATE) LAST_USED
FROM eul_owner.EUL5_QPP_STATS a,
  eul_owner.EUL5_DOCUMENTS b
WHERE a.QS_DOC_NAME = b.doc_name
GROUP BY a.QS_DOC_OWNER,
  a.QS_DOC_NAME ,
  a.QS_DOC_DETAILS ;






The Oracle Discoverer 10g Handbook in appendix A discusses many of the EUL tables.

To get the frequency of each report

SELECT *
FROM
(SELECT --a.QS_DOC_OWNER USERNAME,
a.QS_DOC_NAME WORKBOOK ,
--a.QS_DOC_DETAILS WORKSHEET   ,
COUNT(*) Frequency
FROM EUL_OWNER.EUL5_QPP_STATS a,
EUL_US.EUL5_DOCUMENTS b
WHERE a.QS_DOC_NAME = b.doc_name
GROUP BY --a.QS_DOC_OWNER,
a.QS_DOC_NAME
--a.QS_DOC_DETAILS
) a
ORDER BY a.frequency DESC ;

This tells me what workbooks are using specific folders and who owns the workbooks.

/* Formatted on 2008/08/07 17:49 (Formatter Plus v4.8.8) */
SELECT DISTINCT
DECODE (eul5_elem_xrefs.ex_to_type,'ITE', 'Item','JOI', 'Join','FIL', 'Condition',eul5_elem_xrefs.ex_to_type) dependency,
CASE WHEN eul5_documents.doc_created_by = 'OPM_EUL'
THEN 'OPM_EUL'
ELSE apps.gmf_fnd_get_users.fnd_get_users(TO_NUMBER (SUBSTR (eul5_documents.doc_created_by,2,14)))
END user_name,
eul5_documents.doc_created_by workbook_created_by,
eul5_documents.doc_description workbook_description,
eul5_documents.doc_name workbook_name,
eul5_elem_xrefs.ex_to_par_name folder_name,
eul5_documents.doc_content_type
FROM opm_eul.eul5_documents eul5_documents,
opm_eul.eul5_elem_xrefs eul5_elem_xrefs
WHERE (eul5_elem_xrefs.ex_from_id) = TO_CHAR(eul5_documents.doc_id)
AND eul5_documents.doc_content_type = 'application/vnd.oracle-disco.wb';




IF your reports are based ON custom folders (ie. SQL statements enbedded IN the EUL) THEN  there is  no RELIABLE WAY of DETERMINING the VIEWS/tables THAT ARE accessed. ONE of the REASONS I AVOID CUSTOM FOLDERS. YOU CAN TRY THIS sql for simple FOLDERS.
  ;
  SELECT d.doc_name,
    d.doc_developer_key,
    o.obj_name folder_name,
    o.obj_type folder_type,
    o.sobj_ext_table database_object,
    e.exp_name item_name
  FROM EUL_US.eul5_documents d ,
    EUL_US.eul5_elem_xrefs x ,
    EUL_US.eul5_expressions e ,
    EUL_US.eul5_objs o
  WHERE x.ex_from_type = 'DOC'
  AND x.ex_from_id     = d.doc_id
  AND x.ex_to_id       = e.exp_id
  AND e.IT_OBJ_ID      = o.obj_id;









 

No comments: