Retrieve which Oracle Discoverer workbooks are shared with a specific responsibility

After my earlier post regarding the SQL query to extract the shared workbooks with a specific user (see here: Retrieve which Oracle Discoverer workbooks are shared with a specific user) a question was raised how to extract the sharing of workbooks with responsibilities.

The EUL database schema provides some logic in how this is recorded. Within table EUL5_EUL_USERS the column EU_USERNAME maintains the link between the user id’s but also with the responsibilities. If column EU_ROLE_FLAG = 0 it means there is sharing with an user. If this column has the value 1 it means there is sharing with a responsibility. The column EU_USERNAME in that case has the following format ‘#RESPONSIBILITY_ID#APPLICATION_ID’ which can be joined with the responsibility table APPLSYS.FND_RESPONSIBILITY(_TL).

So in order to retrieve a list of Discoverer Workbooks which are shared with responsibilities the below SQL can be run. As always make your own adjustments when needed.

SELECT 
    ACCESS_PRIVS.AP_UPDATED_DATE LAST_UPDATED
    , DOCUMENTS.DOC_NAME WORKBOOK_NAME
    , DOCUMENTS.DOC_DEVELOPER_KEY WORKBOOK_KEY
    , DOCUMENTS.DOC_DESCRIPTION WORKBOOK_DESCR
    , RESP.RESPONSIBILITY_NAME SHARED_WITH_RESPO
FROM
    EUL_US.EUL5_ACCESS_PRIVS ACCESS_PRIVS
    ,EUL_US.EUL5_DOCUMENTS DOCUMENTS
    ,EUL_US.EUL5_EUL_USERS USERS
    ,APPLSYS.FND_RESPONSIBILITY_TL RESP
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_ROLE_FLAG = 1
    AND USERS.EU_USERNAME = '#' || RESP.RESPONSIBILITY_ID || '#' || RESP.APPLICATION_ID
    AND RESP.LANGUAGE = 'US';