Tags

, , , ,

Here is the script to find out oracle discoverer reports statistics:

DECLARE
CURSOR c_main
IS
SELECT doc_name
FROM EUL10g.EUL5_DOCUMENTS
WHERE doc_batch = 0;
CURSOR c_detail (l_doc_name VARCHAR2)
IS
SELECT QS_DOC_NAME, QS_DOC_OWNER, MAX (last_run_date) last_used
FROM (SELECT doc_name QS_DOC_NAME,
CASE
WHEN INSTR (docs.doc_created_by, ‘#’) = 0
THEN
docs.doc_created_by
WHEN INSTR (docs.doc_created_by, ‘#’) > 0
THEN
(SELECT fu.user_name
FROM apps.fnd_user fu
WHERE     1 = 1
AND fu.user_id =
SUBSTR (docs.doc_created_by, 2, 5))
END
QS_DOC_OWNER,
NULL last_run_date
FROM EUL10g.EUL5_DOCUMENTS docs
WHERE doc_name = l_doc_name —
UNION
SELECT QS_DOC_NAME,
QS_DOC_OWNER,
MAX (QS_CREATED_DATE) last_run_date
FROM EUL10g.EUL5_QPP_STATS
WHERE qs_doc_name = l_doc_name –
GROUP BY QS_DOC_NAME, QS_DOC_OWNER) a
GROUP BY QS_DOC_NAME, QS_DOC_OWNER;
BEGIN
DBMS_OUTPUT.put_line (‘QS_DOC_NAME’ || ‘QS_DOC_OWNER’ || ‘LAST_USED’);
FOR i IN c_main
LOOP
FOR j IN c_detail (i.doc_name)
LOOP
DBMS_OUTPUT.put_line (
j.QS_DOC_NAME || ‘,’ || j.QS_DOC_OWNER || ‘,’ || j.LAST_USED);
END LOOP;
END LOOP;
END;

Advertisements