Thursday Aug 08, 2013

Reporting on User Roles in Fusion Applications

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:

SELECT a.USERNAME,
  c.ROLE_COMMON_NAME,
  c.ROLE_DISTINGUISHED_NAME
FROM PER_USERS a,
  PER_USER_ROLES b,
PER_ROLES_DN_VL c
WHERE a.USER_ID = b.USER_ID
AND b.ROLE_ID = c.ROLE_ID
AND a.USERNAME = '&username'

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.

SELECT ROLE_NAME,
ROLE_GUID,
  SESSION_ID
FROM FND_SESSION_ROLES
WHERE  SESSION_ID IN
  (SELECT SESSION_ID
  FROM
    (SELECT SESSION_ID
    FROM FND_SESSIONS
    WHERE fnd_sessions.user_name = ‘&username’
    ORDER BY FIRST_CONNECT DESC
)
WHERE rownum<=1
)
ORDER BY role_name


The same result can also be obtained using the below query:

SELECT srs.ROLE_NAME
FROM FND_SESSIONS s,
FUSION.FND_SESSION_ROLE_SETS srs
WHERE s.SESSION_ROLE_SET_KEY = srs.SESSION_ROLE_SET_KEY
AND s.SESSION_ID IN
  (SELECT SESSION_ID
  FROM
    (SELECT b.SESSION_ID
    FROM FND_SESSIONS b
    WHERE b.USER_NAME = ‘&username’
    ORDER BY FIRST_CONNECT DESC
    )
  WHERE ROWNUM <= 1
)
ORDER BY srs.ROLE_NAME

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).

For a list of duties and privileges assigned to various job (or external) roles, please refer to My Oracle Support Reference Note: 1460486.1 Mapping of Roles, Duties and Privileges in Fusion Applications.

Keep visiting our blog for other useful tips and tricks in Fusion Applications.

Tuesday Aug 07, 2012

How to Create a View Only Role in Fusion Applications

Fusion Applications are packaged with a seeded Role Based Access Control reference implementation consisting of over 180 Roles that represent a wide variety of enterprise business job functions. In certain cases, customers have within their organizations auditor roles that assume oversight responsibilities over transactional systems and require View Only access to various system transactions. This POST aims to show an example of how such a Role can be defined.

We will use the Procurement Applications as an example of how View Only Roles are defined in Fusion Applications.  It should be noted that the ability to do the same type of setup in other product families depends on the availability within those products of duties similar to the ones we will use in this example to model of our View Only Role.

Procurement Agents in Fusion Applications are primarily responsible for the generation and management of purchasing documents such as purchase orders and purchasing agreements. Depending on their roles they could also be responsible for the management of the RFx process and the awarding of supply contracts.

 Fusion Procurement provides the following Agent RBAC seeded roles

Seeded Role

Description

Buyer

Procurement professional responsible for transactional aspects of the procurement processes.

Category Manager

Procurement professional responsible for identifying savings opportunities, determining negotiation strategies, creating request for quote, request for information, request for proposal, or auction events on behalf of their organization and awarding future business typically in the form of contracts or purchase orders to suppliers.

Procurement Manager

Procurement professional responsible managing a group of buyers in an organization.

Procurement Application Administrator

Responsible for technical aspects of keeping procurement applications systems available as well as configuring the applications to meet the needs of the business.

Procurement Catalog Administrator

Manages agreements and catalog content including catalogs, category hierarchy, content zones, information templates, map sets, public shopping lists, and smart forms.

Procurement Contract Administrator

Procurement professional responsible for creating, managing, and administering procurement contracts.

In addition to the Agent Roles listed above, Fusion Procurement provides:

  • Requester Roles provisioned to Employees and Contingent Workers to create requisitions for themselves or for others.
  • External Supplier Roles provisioned to Supplier Users.

The main Purchasing Duties and their corresponding Privileges are listed below.  The highlighted entries represent the seeded View Only Duty and Privileges.  In order to create a View Only Role we will need to have our custom Role inherit this Duty to the exclusion of other Duties which provide broader access to Purchasing Functionality.

DUTIES

PRIVILEGES

Purchase Order Administration Duty

Communicate Purchase Order and Purchase Agreement


Generate Purchase Order


Import Purchase Order


Purge Purchasing Document Open Interface


Reassign Purchasing Document


Retroactively Price Purchase Order

Purchase Order Changes Duty

Change Purchase Order


Communicate Purchase Order and Purchase Agreement

Purchase Order Control Duty

Acknowledge Purchase Order


Cancel Purchase Order


Change Purchase Order Line Negotiated Flag


Change Supplier Site


Close Purchase Order


Finally Close Purchase Order


Freeze Purchase Order


Hold Purchase Order

Purchase Order Creation Duty

Cancel Purchase Order


Create Purchase Order


Create Purchase Order from Requisitions


Create Purchase Order Line from Catalog

Purchase Order Creation from Requisition Lines Only Duty

Cancel Purchase Order


Create Purchase Order from Requisitions

Purchase Order Overview Duty

Search Purchase Order


View Purchase Order


View Purchasing Workarea

Purchase Order Viewing Duty

View Purchase Order


Case Study

Introduction

This example illustrates the process of creating a View Only Role for a procurement auditor.

Before we outline the setup steps, let us examine the Menu entries available in the Fusion Navigator to a user with the Buyer Role.

dif1.jpg

Figure 1. Menu Items of a User Provisioned with the Buyer Role


The figure above traces the Menu Items available to the Buyer Role to the Privileges contained in their assigned Duties.  The Buyer however has several additional Duties that provide access to multiple tasks as seen in the Figure 2 illustrating the Purchasing Workarea‘s Tasklist in the left pane of the page.
Of note also is the list of Actions that the Buyer can take on a Purchasing Document, notably the creation of a Document as seen in Figure 2 and the Editing Actions seen in Figure 3

dif2.jpg

Figure 2. Tasklist and Actions in the Purchasing Workarea for a User Provisioned with the Buyer Role

dif2.jpg

Figure 3. Available Actions on a Purchasing Document for a  User Provisioned with the Buyer Role

The View Only Role

We will now proceed to create a custom View Only Role that inherits the Purchase Order Overview Duty and provision that Role which we will name ECW Purchasing Only Role to a user who serves as the auditor in the enterprise.
Figure 4 shows the Custom Role in the Authorization Policy Manager Dashboard.

dif2.jpg

Figure 4. Custom Role that inherits the Purchase Order Overview Duty

Once the Role is created and the hierarchy mapped, our next step is to assign that Role to a user through the HCM Manage Users task.

Figure 5 below shows the provisioned role in the Oracle Identity Manager dashboard. 

dif2.jpg

Figure 5. Assigned View Only Role visible in OIM

To allow access to purchasing documents, we need to define the user as a purchasing agent and determine that user’s access to procurement business units and within these business units to determine the level of access the user will have to purchasing documents

dif2.jpg

Figure 6. Agent Setup

The auditor user is now ready to use the system to view purchase orders. As we can see in the following three figures, the user has the Purchasing Menu item in their Fusion Navigator but are not able to either create or edit any of the purchasing document they can view.

dif2.jpg

Figure 7. Navigator Menu Items for the Auditor user

dif2.jpg

Figure 8. No Create Document capability for the Auditor user

dif2.jpg

Figure 9. No Edit  Document capability for the Auditor user

Additional Considerations

The Manage Orders task in the Purchasing workarea points to the following taskflow:


/WEB-INF/oracle/apps/prc/po/manageDocument/publicUi/searchDocument/flow/PurchaseOrderSearchMainFlow.xml#PurchaseOrderSearchMainFlow


This taskflow is one of the resources available in the Search Purchase Order Privilege itself included in the Purchase Order Overview Duty  we have assigned to our custom role and which is also in the hierarchy of the Buyer Role.  This explains the availability of the Manage Orders Entry for both users referenced in this document.

dif2.jpg

Figure 10. Search Purchase Orders Privilege

On the other hand, creating purchase orders is available to the Buyer role but not to our custom role.  Of the two roles outlined in this case study section of this document, only the Buyer role has in its hierarchy the Purchase Order Creation Duty. This explains why the user with the Buyer role can create orders but the user with our custom role cannot.

dif2.jpg

Figure 11.  Create Purchase Order Privilege

Conclusion

In this document we have shown how to create a view only role for an auditor of purchasing documents. We were able to do so without the creation of new privileges or the manipulation of resources but simply by creating a custom role and assigning to it an existing view only duty. In the reference implementation, the view only duty we used is available to many roles within and outside of Procurement; however these roles have other duties that might not be relevant to a procurement auditor.

Your feedback is welcome

We are very interested in hearing about your experiences with this new tool.  Please post your comments below


Resources
  • “Roles, Duties & Privileges” My Oracle Support  (Note 1460486.1)

  • “Menu to privilege mapping” My Oracle Support (Note 1459828.1)

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 Implementation Solutions Task force, part of the Fusion Applications Fusion Architecture organization.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today