Details of Discoverer Report by sql query

Details of Discoverer Report by sql query
by
malli_18arjun
Rate this article

Excellent

Good

Average

Bad

Terrible
Published on 04-30-2009 10:03 AM Number of Views: 791

3 Comments
By using the following query ,we can get the following details of Discoverer Reports
1. creation date
2. shared responsibity or owner
3. user name
4. shared name /responsibility
Code sql:
SELECT DISTINCT disco_docs.doc_name "Discoverer Workbook", TRUNC (disco_docs.doc_created_date) "Workbook Create Date", CASE WHEN INSTR (disco_docs.doc_created_by, '#' ) = 0 THEN disco_docs.doc_created_by WHEN INSTR (disco_docs.doc_created_by, '#') > 0 AND INSTR (disco_docs.doc_created_by, '#', 2) = 0 THEN (SELECT fu.user_name FROM fnd_user fu WHERE fu.user_id = SUBSTR (disco_docs.doc_created_by, 2, 5)) ELSE NULL END "Workbook Owner/Creator", disco_users.eu_username, CASE WHEN INSTR (disco_users.eu_username, '#' ) = 0 THEN disco_users.eu_username WHEN INSTR (disco_users.eu_username, '#') > 0 AND INSTR (disco_users.eu_username, '#', 2) = 0 THEN (SELECT fu.user_name FROM fnd_user fu WHERE fu.user_id = SUBSTR (disco_users.eu_username, 2, 5)) ELSE (SELECT resp.responsibility_name FROM fnd_responsibility_tl resp WHERE resp.responsibility_id = SUBSTR (disco_users.eu_username, 2, 5)) END AS "Shared Name / Responsibility" FROM eul4_us.eul5_documents disco_docs, eul4_us.eul5_access_privs disco_shares, eul4_us.eul5_eul_users disco_users WHERE disco_docs.doc_id = disco_shares.gd_doc_id AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC') AND disco_users.eu_id(+) = disco_shares.ap_eu_id AND disco_docs.doc_name LIKE '%Account%';
Get your free Complementary Copy of Oracle Magazine

You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.
3 Comments
Sadik - 04-30-2009

very nice...

Reply

Ori Tal - 07-23-2009

Hi, The subquery for retrieving the responsibility_name returns more than 1 value.
Any suggestions ?
Anyway, Here is a Query that I use :

Code sql:

SELECT ACCESS_PRIVS.AP_CREATED_DATE, DECODE ( EUL_US.EUL5_GET_ISITAPPS_EUL, 1, DECODE ( USERS.EU_ROLE_FLAG, 1, EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'), DECODE (USERS.EU_USERNAME, NULL, 'Document Not Shared', EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME)) ), NVL (USERS.EU_USERNAME, 'Document Not Shared') ), DECODE (EUL_US.EUL5_GET_ISITAPPS_EUL, 1, EUL_US.EUL5_GET_APPS_USERRESP (DOCUMENTS.DOC_CREATED_BY), DOCUMENTS.DOC_CREATED_BY), DOCUMENTS.DOC_DESCRIPTION, DOCUMENTS.DOC_NAME FROM EUL_US.EUL5_ACCESS_PRIVS ACCESS_PRIVS, EUL_US.EUL5_DOCUMENTS DOCUMENTS, EUL_US.EUL5_EUL_USERS USERS WHERE ( (DOCUMENTS.DOC_ID = ACCESS_PRIVS.GD_DOC_ID(+)) AND (USERS.EU_ID(+) = ACCESS_PRIVS.AP_EU_ID)) AND (DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb') AND (DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb') AND (DECODE ( EUL_US.EUL5_GET_ISITAPPS_EUL, 1, DECODE ( USERS.EU_ROLE_FLAG, 1, EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'), DECODE ( USERS.EU_USERNAME, NULL, 'Document Not Shared', EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME) ) ), NVL (USERS.EU_USERNAME, 'Document Not Shared') ) = &Responsibility_name) ORDER BY DOCUMENTS.DOC_NAME ASC, DOCUMENTS.DOC_CREATED_DATE ASC, DECODE ( EUL_US.EUL5_GET_ISITAPPS_EUL, 1, DECODE ( USERS.EU_ROLE_FLAG, 1, EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'), DECODE (USERS.EU_USERNAME, NULL, 'Document Not Shared', EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME)) ), NVL (USERS.EU_USERNAME, 'Document Not Shared') ) ASC;

Reply

paul.s.richards - 01-22-2010

Awesome! Works for Discoverer 4 if you change eul5 to eul4. Many thanks.

Reply