Retrieve which Oracle Discoverer workbooks are shared with a specific user

Discoverer is a great reporting tool from Oracle which you use to create reports from your Oracle eBS database. You create an End User Layer (EUL) which will describe views and tables that are available in your database schemas – of course also any customized ones. Also you’re able to reference any custom pl/sql functions that you’ve added to your instance – and use those in your EUL definition.

End-users will drag and drop columns from your EUL in a report (i.e. table styled, pivoted etc). Custom functions can be used here also. At the background Discoverer will generate the corresponding SQL statement which will include all the types of relationships (inner, outer, left outer etc) which you’ve defined in the EUL. The SQL is fired against the database which will return the dataset. The Discoverer interpreter will generate the layout that’s defined in the workbook.

Workbooks can be shared among users. So, user A will create a nice and terrific report but user B wants to use this report also. In that case user A will share his report with user B which becomes available in his repository.

Sometimes you want to know which workbooks are shared with an user. Use below statement to get that information from the database. (The complete EUL definition is stored in specifc tables within the EUL database schema).

SELECT 
    ACCESS_PRIVS.AP_UPDATED_DATE LAST_UPDATED
    , DOCUMENTS.DOC_NAME WORKBOOK_NAME
    , DOCUMENTS.DOC_DEVELOPER_KEY WORKBOOK_KEY
    , DOCUMENTS.DOC_DESCRIPTION WORKBOOK_DESCR
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 USERS.EU_USERNAME LIKE '#' || (SELECT USER_ID FROM FND_USER WHERE USER_NAME = <ENTER USERNAME HERE>) || '%'