Tips and Recommendations to assist you in deploying Fusion Applications

Thursday Aug 08, 2013

We often find a need to get a list of enterprise roles assigned to a Fusion Applications user, a need for a simple report. This can also be useful when there is no access to OIM screens, but only a simple read-only access is provided to the Fusion database. Below are certain simple SQL scripts that would assist in getting such a report. These scripts can be run by creating data model queries in BI Publisher if you are accessing a SaaS implementation or directly run in any SQL client if you are in an on-premise setup.

1. The SQL below can be used to get a list of roles assigned to an FA user:

Below is a sample output from the SQL and the screenshot from OIM for the same user (FA user 'FUSION' is used for this example here).

OIM Screenshot for 'FUSION' user is below:

2. Further drill-down of the individual roles can be obtained using the query below which provides the detailed listing of roles inherited by a specific user session. The result from this query would match the results you see when drilling down 'Application Implementation Consultant', 'Employee' and 'IT Security Manager' above.

The above queries, using FND_SESSIONS, will only be valid if the FA user has logged into Fusion Applications at any time (or if there is an active session of this user) and the user's login information exists in this table (not purged by any purge routines).

About

This blog shares with the broader Fusion Applications community instructional material in the areas of Enterprise Structures, Extensibility, Integration and Security with the a focus on implementation. This blog is updated by the Fusion Applications Functional Architecture organization.