17 Using Discoverer with Oracle Applications

This chapter explains how Discoverer supports access to Oracle Applications databases using Oracle Applications security and contains the following topics:

• "What are Oracle Applications?"

• "What are Oracle Applications responsibilities?"

• "What features does Discoverer support for Oracle Applications users?"

• "What are the prerequisites for using Discoverer with Oracle Applications?"

• "What is different about running Discoverer in Oracle Applications mode?"

• "How to configure the Connect dialog for Oracle Applications users"

• "How to create an Oracle Applications EUL"

• "Which user should maintain an Oracle Applications EUL?"

• "How to enable an Oracle Applications user to maintain an Oracle Applications EUL"

• "How to specify the Oracle Applications users/responsibilities who can access a business area"

• "How to specify the business areas that an Oracle Applications user/responsibility can access"

• "How to specify the tasks an Oracle Applications user or responsibility can perform"

• "How to specify the Oracle Applications users/responsibilities who can perform a specific task"

• "How to display your Oracle Applications database user name and responsibility in a workbook"

17.1 What are Oracle Applications?

Oracle Applications are Oracle's integrated enterprise resource planning (ERP) and customer relationship management (CRM) solutions. Oracle Applications enable companies to run and manage their worldwide operations from a central site.

17.2 What are Oracle Applications responsibilities?

Oracle Applications responsibilities are akin to database roles. They are an abstraction to which privileges can be assigned and which can apply to many users. Oracle Applications responsibilities are used to control Oracle Applications users functional and data access.

An Oracle Applications user connects to an Oracle Applications database and chooses a single Oracle Applications responsibility. Each Oracle Applications responsibility can have a set of privileges associated with it. So an Oracle Applications user, by default, assumes the task privileges granted to the chosen responsibility (for more information see "How to specify the Oracle Applications users/responsibilities who can perform a specific task"). To change the responsibility but keep the same user you must reconnect.

Notes

• If you encounter anomalies in Discoverer items available to users (for example, there is a difference between folders or items visible in Discoverer Administrator and Discoverer Plus for the same Appsuserid or Responsibility), this might be caused by how Oracle Applications security is set up. For more information about Oracle Applications security, contact the Oracle Applications administrator.

17.3 What features does Discoverer support for Oracle Applications users?

Discoverer supports the following features for Oracle Applications users:

• Ad-hoc query access to Oracle Applications databases using Oracle Applications user names, passwords and responsibilities

• Oracle Applications mode EULs

  An Oracle Applications mode EUL is a Discoverer End User Layer based on an Oracle Applications schema (containing the Oracle Applications FND (Foundation) tables and views). Oracle Applications EULs employ Oracle Applications user names and responsibilities whereas standard EULs use database users and roles.

  The only database user (that is, non-Oracle Applications user) that can connect to an Oracle Applications mode EUL is the EUL owner. The EUL owner is the database user that is used to create the EUL. However, the EUL owner can grant administration privileges to Oracle Applications users. The authorized Oracle Applications users can then connect to the Oracle Applications mode EUL using Discoverer Administrator.

• Oracle Applications row level security

  Many Oracle Applications tables and views are user-sensitive, and will return different results depending on which user/responsibility is used to access these tables/views. Discoverer correctly runs queries that respect these user-sensitive tables and views.

• Oracle Applications multiple reporting currencies

  When connecting to Discoverer as an Oracle Applications user/responsibility, Discoverer will automatically connect to the correct schema (either APPS or APPS_MRC) to support Oracle Applications instances that have implemented the multiple reporting currencies feature.

• Oracle Applications multiple organizations

  Using Discoverer with Oracle Applications multiple organizations support enables you to work with data from more than one organization. Discoverer end users can query and analyze data from a set of organizations to which they have been granted access. The folders in the EUL you are connecting to must be based on Oracle Business Views (available in Oracle Applications 11i).

These features are only available when Discoverer is running in Oracle Applications mode. In other words, when Discoverer is running with an Oracle Applications mode EUL against an Oracle Applications database.

17.4 What are the prerequisites for using Discoverer with Oracle Applications?

To start Discoverer as an Oracle Applications user the following conditions must be met:

• Oracle Applications must be installed before Discoverer can be used in applications mode

• Discoverer must be running against one of the following Oracle Applications versions:

  • Release 10.7

  • Release 11

  • Release 11i

17.5 What is different about running Discoverer in Oracle Applications mode?

The following differences apply in Discoverer when running in Oracle Applications mode:

• "Differences with the Discoverer Connect dialog in Oracle Applications mode"

• "Differences with Discoverer security in Oracle Applications mode"

• "Differences with Discoverer summary folders in Oracle Applications mode"

• "Differences with Discoverer and secure views/language settings in Oracle Applications mode"

17.5.1 Differences with the Discoverer Connect dialog in Oracle Applications mode

When you run Discoverer in Oracle Applications mode the "Connect to Oracle Business Intelligence Discoverer Administrator dialog (for Oracle Applications users)" will either display or not display the Oracle Applications user check box.

Figure 17-1 Connect to Oracle Business Intelligence Discoverer Administrator dialog (for Oracle Applications users)

For more information, see "How to configure the Connect dialog for Oracle Applications users".

The following conditions apply when the Connect dialog is configured for Oracle Applications users:

• if the Oracle Applications user check box is not displayed you connect to Discoverer as an Oracle Applications database user

• if the Oracle Applications user check box is displayed, you select the check box to connect to Discoverer as an Oracle Applications user. Alternatively, clear the Oracle Applications user check box to connect as an Oracle database user.

After you enter details in the Connect dialog and click OK, Discoverer displays a Responsibilities dialog and you can choose the responsibility with which to connect (if the Oracle Applications user you are connecting with has more than one responsibility).

Figure 17-2 Choose a Responsibility dialog

You can bypass the Choose a Responsibility dialog by entering both the Oracle Applications user and the responsibility into the Username field in the form 'user:responsibility'.

17.5.2 Differences with Discoverer security in Oracle Applications mode

When you run Discoverer Administrator as an Oracle Applications user, then the Discoverer Privileges and Security dialogs display Oracle Applications user names and responsibilities. You can assign privileges and security to Oracle Applications user names and responsibilities. When you run Discoverer Administrator as a database user then the Discoverer Privileges and Security dialogs display database users and database roles.

For more information about using privileges to control access to information, see Chapter 7, "Controlling Access to Information".

17.5.3 Differences with Discoverer summary folders in Oracle Applications mode

As the Discoverer manager of an Oracle Applications mode EUL you must be aware of the following:

• Some Oracle Applications database views contain row-level security and return differing result sets depending upon the currently active responsibility. So materialized views (summary tables in Oracle Standard Edition databases) contain different data depending on the responsibility of the user refreshing the external summary folder.

• A summary folder based on an Oracle Applications secure view or Oracle Applications business view must be registered in Discoverer as an external summary folder (for more information, see "How to create summary folders based on external summary tables").

• Where an external summary folder is mapped against a table or view with Oracle Applications row level security, it is your responsibility as Discoverer manager to make sure the external table or view provides secure access. If you are not sure, ask your database administrator.

• If a managed summary folder is found that satisfies a query (based on data with Oracle Applications row level security), Discoverer informs the end user that no rows satisfy the query criteria. Discoverer does this to ensure that end users do not see data they should not have access to.

• If a managed summary folder is found that satisfies a query (based on data without Oracle Applications row level security), Discoverer returns the rows unaffected. Discoverer does this because the data can be safely accessed by all end users.

• Some Oracle Applications views with row-level security support "public" rows (particularly Human Resources).

17.5.4 Differences with Discoverer and secure views/language settings in Oracle Applications mode

When a Discoverer end user uses a workbook that accesses Oracle Applications secure views, the user might see different results on different machines (even when using the same connection information) if the machines have different local language (NLS) settings.

When using Oracle Applications secure views, the local language setting of the machine affects the data retrieved by Discoverer. Discoverer will display data consistently across machines with the same language setting.

To change a machines local language setting (on Windows), choose Start | Settings | Control Panel | Regional Settings and change the language value.

For more information on secure views, see "How to use query prediction with secure views".

You can also define a language setting (NLS) for a user, responsibility, application or site using the Profiles setting in Oracle Applications. For more information see the Oracle Applications documentation.

17.6 How to configure the Connect dialog for Oracle Applications users

Before you connect to Discoverer as an Oracle Applications user, you must configure the Connect dialog to default to Oracle Applications users.

To configure the Connect dialog for Discoverer Administrator and Discoverer Desktop:

1. Select Tools | Options to display the "Options dialog: Connection tab":

   Figure 17-3 The Options dialog: Connection tab

   
   

2. Specify the type of EUL to connect to by selecting one of the following option:

   Option	Use this option to:
   Connect to standard EULs	The Oracle Applications User check box is not displayed in the Connect dialog and Discoverer expects standard database users.
   Connect to applications EULs	The Oracle Applications User check box is not displayed in the Connect dialog but Discoverer expects users to connect using an Applications user id/password and Oracle Applications database TNS alias.
   Connect to both standard and applications EULs	The Oracle Applications User check box is displayed in the Connect dialog and (depending on whether the check box is cleared or selected) you can connect to either standard or Oracle Applications database EULs.

   
   

Notes

• To configure the Connect dialog for Discoverer Desktop, repeat the steps described above for Discoverer Administrator, but within Discoverer Desktop.

• To configure the Connect dialog for Discoverer Plus and Viewer to validate Oracle Applications user names and passwords, see the Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Discoverer.

About entering details into the fields GWYUID/Password and FNDNAM

When you use the Options dialog: Connections tab and you select either the Connect to applications EULs option or the Connect to both standard and applications EULs option you can enter values into these fields, but Discoverer uses default values if you do not. The fields and default values are as follows:

• Gateway User ID (GWYUID)/Password

  You can enter your Gateway User ID and Password here (the default value used is 'applsyspub/pub' if you enter nothing here).

• Foundation Name (FNDNAM)

  You can enter the Foundation Name (the default value used is 'apps' if you enter nothing here).

If you do not know the values to enter into the above fields contact your Oracle Applications database administrator.

17.7 How to create an Oracle Applications EUL

You create an Oracle Applications EUL in two ways:

• using the Create EUL dialog

• using the command-line interface

To create an Oracle Applications EUL using the Create EUL dialog:

1. Start Discoverer Administrator and display the "Connect dialog".

2. Enter the user name, password and TNS alias for a DBA user.

   For example, dba/dbapassword@oracleappsdb.

   Note: You must not specify the user name of an Oracle Applications user. The EUL owner is always a database user.

   Figure 17-4 Connect to Oracle Business Intelligence Discoverer Administrator dialog

   
   

   Note: The Oracle Applications user Connect dialog might display the Oracle Applications User check box. For more information see "How to configure the Connect dialog for Oracle Applications users".

3. Clear the Oracle Applications User check box (if the check box is displayed).

4. Click Connect.

   If no EULs exist Discoverer displays a dialog for you to choose whether to create an EUL now.

5. Click Yes.

6. Discoverer displays the "EUL Manager dialog".

   Figure 17-5 EUL Manager dialog

   
   

7. Click Create an EUL to display the "Create EUL Wizard dialog".

   This is where you create a new database user and Oracle Applications EUL.

   Figure 17-6 Create EUL Wizard dialog

   
   

8. Specify whether an existing or new user is to own the Oracle Applications EUL by selecting one of the following options:

   • Select an existing user

     then select a user from the drop down list in the User field

   • Create a new user

     then enter a user name, password/confirmation for the new user

   Note: The EUL owner is always a database user. Please specify a database user.

9. (optional) Clear the Grant EUL access to PUBLIC check box.

   Tip: We recommend that you clear the Grant EUL access to PUBLIC check box to restrict public access to your EUL tables. If you do not select the Grant EUL access to PUBLIC check box, but still want other database users to have access to your EUL tables, you will need to grant access to your EUL tables manually.

   If you want to grant all database users access to your EUL tables you must select the Grant EUL access to PUBLIC check box.

10. Select the New EUL is for Oracle Applications users ONLY check box to create an Oracle Applications mode EUL.

    Note: The choice that you make here is final. You cannot change an Oracle Applications EUL into a standard EUL (and vice versa).

11. Click Next to display the "Create EUL Wizard: Step 2 dialog" where you select the Oracle Applications schema and enter the schema password.

    Figure 17-7 Create EUL Wizard: Step 2 dialog

    
    

12. Use the Schema drop down list to select the Oracle Applications schema containing the Oracle Applications FND tables.

13. Enter the password for the Oracle Applications schema and click Next.

14. (if creating a new user) If you are creating a new user Discoverer displays the "Create EUL Wizard: Step 3 dialog" where you select the default and temporary tablespaces for the new database user/schema.

    Figure 17-8 Create EUL Wizard Step 3 dialog

    
    

15. (if creating a new user) Highlight the required Default and Temporary tablespaces you want to use for the Oracle Applications EUL owner.

    Tip: Ask your Oracle Applications database administrator if you are not sure.

16. Click Finish to create the tables and views for the new Oracle Applications mode EUL and populate them with default data.

    Discoverer displays a dialog to confirm the creation of the new EUL:

17. Click OK to close the confirmation dialog.

    Discoverer displays a dialog that gives you the option to install tutorial data into the new EUL.

18. Click Yes or No to specify whether to install the tutorial data.

    Discoverer displays a dialog that gives you the option to reconnect to the database as the owner of the new Oracle Applications EUL you have just created, or to remain connected as the DBA.

Notes

• Having created an Oracle Applications EUL you can now:

  • Grant task privileges to an Oracle Applications user so that they have the ability to perform tasks in Discoverer Administrator or Discoverer Plus using this Oracle Applications EUL (for more information, see "How to specify the tasks an Oracle Applications user or responsibility can perform").

  • Create a new business area using the Oracle Applications tables (for further information, see Chapter 5, "Creating and Maintaining Business Areas").

  • Grant access to the tutorial business area to Oracle Applications users and responsibilities (for more information, see "How to specify a user or role (responsibility) that can access a business area"). Granting access to the tutorial business area enables Oracle Applications users and responsibilities to access the sample data.

  • If you did not install the sample data into the Apps Schema, you must grant the select privilege on the sample data tables to the APPS schema owner (for more information about granting privileges, see the Oracle Database SQL Reference Guide. The select privilege is required because the APPS schema owner is the database user that is used as the proxy by applications users. Therefore, APPS schema owner must have select access to the sample data tables.

• You must not use a database user (for example, the EUL owner) to maintain an Oracle Applications EUL (for more information, see "Which user should maintain an Oracle Applications EUL?".

• When you create a new Oracle Applications business area, the schema objects you load into the business area must be based on secure views. This retains the row-level security associated with a specific Oracle Applications responsibility. Ask your Oracle Applications database administrator for further information.

• You can also create an Applications mode EUL from the command line (for more information see "/create_eul /apps_mode").

17.8 Which user should maintain an Oracle Applications EUL?

When you maintain an Oracle Applications EUL in Discoverer Administrator, it is advisable that you do not use a database user (for example, the EUL owner). You should use an Oracle Applications user (for example, SYSADMIN) or Responsibility (for example, System Administrator). When you use an Oracle Applications user (instead of a database user) as the EUL administrator, Discoverer provides correct access to the necessary Oracle Applications objects (for example, when refreshing, importing, creating, business areas and folders).

For information about how to grant the necessary privileges required to enable an Oracle Applications user to maintain an Oracle Applications EUL, see "How to enable an Oracle Applications user to maintain an Oracle Applications EUL".

17.9 How to enable an Oracle Applications user to maintain an Oracle Applications EUL

The Oracle Applications user that maintains an Oracle Applications EUL must have the Administration privilege to provide correct access to the necessary Oracle Applications objects (for example, when refreshing, importing and creating business areas and folders).

To enable an Oracle Applications user to maintain an Oracle Applications EUL:

1. Connect to Discoverer Administrator in Applications mode as the EUL owner (for more information, see "What is different about running Discoverer in Oracle Applications mode?").

2. Choose Tools | Security to display the "Security dialog: Business Area - > User tab".

   Figure 17-9 Security dialog: Users-> Business Area tab

   
   

3. Click Select to display the "Select User/Role dialog" where you can search for and select the Oracle Applications user to which you want to grant access.

   Figure 17-10 Select User/Role dialog

   
   

4. Enter the search criteria in the Search For field and click Go.

   Discoverer Administrator displays the search results in the Results list.

5. Select the Oracle Applications user from the Results list.

6. Click OK to display the "Security dialog: Users - > Business Area tab" with the business areas for the selected Oracle Applications user.

   Figure 17-11 Security dialog: Users-> Business Area tab

   
   

7. Move the business areas that you want the selected Oracle Applications user to have access to from the Available business areas list to the Selected business areas list.

   You can select more than one business area by pressing the Ctrl key and clicking another business area.

8. For each new business area you add to the Selected business areas list, follow the instructions below to specify whether the selected Oracle Applications user has administration access to the business area:

   1. Click the business area in the Selected business areas list.

   2. Select the Allow Administration check box as required.

   The setting of a user's Allow Administration privilege controls which administration tasks the Oracle Applications user can perform. For more information, see "How to specify the tasks a user or role (responsibility) can perform".

9. Click OK to save the changes you have made and close the Security dialog.

10. Choose Tools | Privileges and display the "Privileges dialog: Privileges tab".

    Figure 17-12 Privileges dialog: Privileges tab

    
    

11. Click Select to display the "Select User/Role dialog" where you can search for and select the Oracle Applications user to which you want to grant Administration privileges.

    Figure 17-13 Select User/Role dialog

    
    

12. Enter the search criteria in the Search For field and click Go.

    Discoverer Administrator displays the search results in the Results list.

13. Select the required Oracle Applications user from the Results list.

14. Click OK to display the "Privileges dialog: Privileges tab" for the selected Oracle Applications user.

    Figure 17-14 Privileges dialog: Privileges tab

    
    

15. Grant task privileges for the Oracle Applications user by selecting the Administration Privilege check box and the check boxes in the Administration Privilege list.

    Note: To grant these privileges, you must first grant the parent privilege using the Administration Privilege check box. For more information, see "How to specify the tasks a user or role (responsibility) can perform".

    Tip: Click a privilege in the Administration Privilege list or the Desktop and Plus Privilege lists to see a brief description of that privilege in the area below the lists.

16. Click OK to save the changes you have made and close the Privileges dialog.

    You can now reconnect to Discoverer Administrator as the Oracle Applications user and perform maintenance tasks on the Oracle Applications EUL (for example, refreshing, importing and creating, business areas and folders).

17.10 How to specify the Oracle Applications users/responsibilities who can access a business area

This task describes how to grant (or deny) access permission for business areas to specific users or responsibilities.

For information about Oracle Applications responsibilities, see "What are Oracle Applications responsibilities?".

Note: When completing the following task there is a notable difference between what Oracle database users and Oracle Applications users will see in the dialog:

• Oracle database users will see the words Users and Roles

• Oracle Applications database users will see the words Users and Responsibilities

For more information about this task see "How to specify a user or role (responsibility) that can access a business area".

17.11 How to specify the business areas that an Oracle Applications user/responsibility can access

This task describes how to specify which business areas a specific Oracle Applications user or responsibility can access.

For information about Oracle Applications responsibilities, see "What are Oracle Applications responsibilities?"

Note: When completing the following task there is a notable difference between what Oracle database users and Oracle Applications users will see in the dialog:

• Oracle database users will see the words Users and Roles

• Oracle Applications database users will see the words Users and Responsibilities

For information about this task see "How to specify the business areas a user or role (responsibility) can access".

17.12 How to specify the tasks an Oracle Applications user or responsibility can perform

This section describes how to specify the tasks a specific user or responsibility can perform.

For information about Oracle Applications responsibilities, see "What are Oracle Applications responsibilities?".

Note: When completing the following task there is a notable difference between what Oracle database users and Oracle Applications users will see in the dialog:

• Oracle database users will see the words Users and Roles

• Oracle Applications database users will see the words Users and Responsibilities

For information about this task see "How to specify the tasks a user or role (responsibility) can perform".

17.13 How to specify the Oracle Applications users/responsibilities who can perform a specific task

This section describes how to specify the users or responsibilities that can perform a specific task.

For information about Oracle Applications responsibilities, see "What are Oracle Applications responsibilities?".

Note: When completing the following task there is a notable difference between what Oracle database users and Oracle Applications users will see in the dialog:

• Oracle database users will see the words Users and Roles

• Oracle Applications database users will see the words Users and Responsibilities

For information about this task see "How to specify a user or role (responsibility) to perform a specific task".

17.14 How to display your Oracle Applications database user name and responsibility in a workbook

You can use a custom folder to display the name of your Oracle Applications database user and responsibility in a Discoverer workbook. You might want to do this because Discoverer workbooks can display different results depending on the Oracle Applications database user and responsibility that runs the workbook. This task enables you to identify which Oracle Applications database user and responsibility has run a particular Discoverer workbook.

To display your Oracle Applications database user name and responsibility in a Discoverer workbook using a custom folder:

1. Start Discoverer Administrator and display the "Connect dialog".

2. Enter your Oracle Applications database user name, password and TNS alias.

   For example, apps1/apps1password@oracleappsdb:

3. If the Oracle Applications database user has more than one responsibility, choose a responsibility from the Responsibility list displayed in the "Responsibilities dialog".

4. Choose Tools |Register PL/SQL Functions to display the "PL/SQL Functions dialog: Arguments tab".

   Note: The PL/SQL functions FND_GLOBAL.USER_NAME and FND_GLOBAL.RESP_NAME must be available in the "PL/SQL Functions dialog: Arguments tab" before you can subsequently use them in a custom folder.

5. If either of the functions FND_GLOBAL.USER_NAME and FND_GLOBAL.RESP_NAME do not appear in the functions list you can import them.

   To import the PL/SQL functions:

   1. Click Import to display the "Import PL/SQL Functions dialog".

   2. Choose both PL/SQL functions from the list and click OK to close the import PL/SQL Functions dialog.

      Note: Each PL/SQL function is prefixed with the default Oracle Applications user, Apps (for example, Apps.FND_GLOBAL.USER_NAME).

   3. Click OK to close the PL/SQL Functions dialog: Functions tab and apply the changes.

6. (optional) If you do not want to import the PL/SQL functions FND_GLOBAL.USER_NAME and FND_GLOBAL.RESP_NAME, you can create them.

   Note: You might choose to create these two PL/SQL functions (instead of importing them), if the database takes a long time to display information.

   To create the PL/SQL functions:

   1. Click New to create a new PL/SQL function, and populate the fields with default data.

   2. Enter FND_GLOBAL.USER_NAME into the Function Name field.

   3. Enter FND_GLOBAL.USER_NAME into the Display Name field.

   4. Enter APPS into the Owner field.

   5. Choose Varchar in the Return Type field.

   6. Enter some meaningful text into the Identifier field.

   7. Click Apply to save the changes.

   8. Repeat the above steps to create another PL/SQL function for FND_GLOBAL.RESP_NAME (that is, replace FND_GLOBAL.USER_NAME with FND_GLOBAL.RESP_NAME in step b).

   9. Click OK to close the dialog and save the changes.

7. Create a new business area or open an existing business area.

   For more information, see Chapter 5, "Creating and Maintaining Business Areas".

   Note: In the following steps you will create a custom folder that contains the items User Name and Resp Name, and then you will include these items in a workbook. To make the custom folder readily available to other Oracle Applications business areas, you can create a new business area to contain just the custom folder.

8. Choose Insert | Folder | Custom to display the "Custom Folder dialog".

9. Enter a meaningful name for the custom folder (for example, UserResp_Name).

10. Enter the following SQL statement into the Enter the SQL to define the custom folder field:

    Select fnd_global.user_name, fnd_global.resp_name from dual; 
    

    The SQL statement above creates a custom folder containing the two items, User Name and Resp Name. Discoverer will use the PL/SQL functions (that you previously imported or created) to display the Oracle Applications database user name and responsibility represented by User Name and Resp Name.

    For more information, see "How to create custom folders".

11. Connect to Discoverer Plus or Discoverer Desktop using your Oracle Applications database user and responsibility name.

    For more information about using:

    • Discoverer Plus, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.

    • Discoverer Desktop, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Desktop.

12. Open an existing workbook or create a new workbook.

13. Create a new worksheet that contains just the custom folder items User Name and Resp Name.

    Note: Because this custom folder is not joined to other folders, the items User Name and Resp Name must be the only items on the worksheet.

14. Run the workbook in Discoverer Plus, Discoverer Desktop or Discoverer Viewer.

    The Discoverer workbook will contain a worksheet that displays your Oracle Applications database user name and responsibility name.