Integrating Oracle eBS Responsibility Profiles and Oracle BI 11g Application Roles

I have been blogging earlier about the integration between Oracle eBS R12 and Oracle BI 11g as well as inheriting the Oracle eBS Security in Oracle BI. Instead of making a Oracle BI Application Role for each Oracle eBS Responsibility you would like to use in Oracle BI, make use of an Oracle eBS Profile Option. You could define an Oracle eBS Profile Option (e.g. XXBI_SECURITY_PROFILE – Oracle BI Security Profile) and apply this to an Oracle eBS Responsibility. This makes it easier to maintain the Security Inheritance. When you add a new Responsibility to Oracle eBS, you do not have add this Responsibility to Oracle BI. Just make sure the Oracle eBS Responsibility gets the Oracle eBS Profile Option applied or define the Oracle BI Security Profile on Site Level as a default.

The Oracle BI Security Configuration for Oracle BI basically looks like this;

Oracle eBS - Oracle BI 11g Security Configuration

 

So instead of matching the Oracle eBS Responsibility to a Oracle BI Application Role, match an Oracle eBS Profile Option (assigned to an Oracle eBS Responsibility or on Site Level) to a Oracle BI Application Role.

You have to follow a few steps in Oracle eBS.

Lookup Type

Navigate to Application Developer – Application, Lookups, Common to add a lookup type with the different values for the Oracle BI Security Profile.

You can check the values via the following query:

select t.lookup_type
 , t.meaning
 , a.application_name
 , t.description
 from applsys.fnd_lookup_types_tl t
 , applsys.fnd_lookup_types b
 , applsys.fnd_application_tl a
 where b.lookup_type = t.lookup_type
 and b.security_group_id = t.security_group_id
 and b.view_application_id = t.view_application_id
 and a.application_id = b.application_id
 and b.lookup_type = '&XXBI_SECURITY_PROFILE'
 and t.language = '&LANGUAGE'
 and a.language = '&LANGUAGE'
;
select flv.lookup_code
, flv.meaning
, flv.description
from applsys.fnd_lookup_values flv
where lookup_type = '&XXBI_SECURITY_PROFILE'
and flv.language = '&LANGUAGE'
and trunc(sysdate) >= trunc(flv.start_date_active)
and trunc(sysdate) < nvl(trunc(flv.end_date_active), trunc(sysdate) +1)
;

Oracle BI Security Profile

Navigate to Application Developer – Profile to add a Oracle BI Security Profile

You can check the values via the following query:

select fpo.profile_option_name
, a.application_name
, fpotl.user_profile_option_name
, fpotl.description
from applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 , applsys.fnd_application_tl a
where fpotl.profile_option_name = fpo.profile_option_name
 and a.application_id = fpo.application_id
 and a.language = fpotl.language
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
;

Add the following code as SQL Validation

******

SQL=”SELECT MEANING \”BI Security Profile\”, LOOKUP_CODE
into :visible_option_value,
:profile_option_value
from applsys.fnd_lookup_values fl
where fl.lookup_type = ‘Enter Lookup Type here’
and fl.language = ‘Enter Language here’
and trunc(sysdate) >= trunc(fl.start_date_active)
and trunc(sysdate) <nvl(trunc(fl.end_date_active), trunc(sysdate) + 1)”
COLUMN=”\”BI Security Profile\”(10)”

******

Assign Oracle BI Security Profile to Oracle eBS Responsibility

Navigate to System Administrator – Profile, System to assign the Oracle BI Security Profile to the Oracle eBS Responsibility

You can check the values via the following query:

Site Level (Default)

select fpov.profile_option_value bi_type_gebruiker_site_level
 from apps.fnd_profile_options_vl fpovl
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.profile_option_id = fpovl.profile_option_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10001 -- Site
 and fpotl.user_profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
;

Responsibility Level (Specific)

select fr.responsibility_id
 , fr.responsibility_name
 , fpov.profile_option_value bi_type_gebruiker_resp_level
 from apps.fnd_responsibility_vl fr
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.level_value = fr.responsibility_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10003 -- Responsibility
 and fpotl.user_profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'

The remainder of the setup in the Oracle Enterprise Manager and the actual match via an Initialization Block is described here. The following query could be used to retrieve the Oracle eBS Profile Option and assign it to the ROLES-session variable

Oracle BI Initialization Block: GetApplicationRoles

select NVL ( rl.bi_security_profile_resp_level
 , sl.bi_security_profile_site_level ) bi_type_gebruiker
 from ( select fpov.profile_option_id
 , fpotl.language
 , fpov.profile_option_value
 bi_type_gebruiker_resp_level
 from applsys.fnd_responsibility fr
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.level_value = fr.responsibility_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10003
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
 and fr.responsibility_id = fnd_global.resp_id
 and fr.application_id = fnd_global.resp_appl_id
 ) rl
 , ( select fpov.profile_option_id
 , fpotl.language
 , fpov.profile_option_value
 bi_type_gebruiker_site_level
 from applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10001
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE' ) sl
 where sl.language = rl.language(+)
 and sl.profile_option_id = rl.profile_option_id(+)

Feel free to comment.

OBIEE 11g – Error selecting ”Coreapplication” in Oracle Enterprise Manager (ADF_FACES-60097, ADF_FACES-60096)

When we logged into Oracle Enterprise Manager (EM) and select ‘Coreapplication’ in the left pane , we encouter the error; “ADF_FACES-60097 : For more information, please see the server’s error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1”

This seems like a known bug so we followed the steps in Doc Id 1363602.1. Unfortunately that didn’t solve the problem.

Oracle Support advised us to increase the logging for the EM as shown in Doc ID 1464333.1. This resulted in Error Logging in one of the AdminServer-diagnostic Log Files. A Fatal Error occurred reading this file: ‘FMWhome/user_projects/domains/lnbisfoundation_domain/sysman/mds/partition1/ai/bi/fragments/mdssys/cust/user/weblogic/deployment.jspx.xml’

Investigating the structure of this file turned out that for some reason this file was empty (zero bytes). Removing the file and restarting the environment did the trick for us. The file got recreated and ‘Coreapplication’  could be selected again.

[Update 09/14/2015 – FMWhome/user_projects/domains//sysman/mds/partition1/ai/bi/mdssys/cust/user//instance.jspx.xml
can also be zero bytes. A delete and a restart can fix the EM.]

– Daan

Solving ORA-01722 – Invalid Number while navigating from Oracle BI 11g to Oracle eBS R12


In a previous blog post, I covered how to navigate from Oracle BI 11g to Oracle eBS R12. While setting this up, I had some challenges to overcome. One of them was the following. When I clicked the column with the Action Link, the following error showed up;

The Oracle eBS Server was available, so that couldn’t be the problem. Checking the logfile(s) showed that there was a ORA-01722 error involved.

The question at this point is; Which query is throwing a ‘Invalid Number’-error. Thanks to Robin I got the idea to Sql Trace the Connection Pool which is being used for the Action Link.

Via the generated Trace Files I was able to identify the problem – query. It turned out to be the query which constructs the Url to navigate to Oracle eBS.

select fnd_run_function.get_run_function_url
 ( cast
 ( fnd_function.get_function_id ( 'AP_APXINWKB_SUMMARY_VIEW' ) as number )
 , cast ( '200.00' as number )
 , cast ( '*****' as number )
 , cast ( '0.00' as number )
 , 'INVOICE_ID=*****'
 , null ) as action_link
 from DUAL

A quick check in SQL*Plus gave the same error. The problem is in the Cast functions and the NLS_NUMERIC_CHARACTERS-settings.

An easy;

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;

….. solved the problem in SQL*Plus.

In Oracle BI, I added the ‘Alter Session’-Statement in the Connection Pool which is being used for the Action Link.

Problem Solved.

– Daan

Navigating from Oracle BI 11g to Oracle eBS R12

One of the interesting features of Oracle BI 11g is the Action Framework. This feature makes it possible to e.g. navigate from Oracle BI 11g to Oracle eBS R12. When you have a look at the different Oracle BI(A) presentations from Oracle you will see a slide regarding Action Links ‘Insight to Action’.

It looks like this is standard Out-of-the-Box functionality. This is partly true. You should have at least the Oracle BI 11g & Oracle eBS R12 integration in place. Next to that there is some additional configuration / implementation required.

Oracle BI configuration

  • Enable Action Link to Oracle eBS (ActionFrameworkConfig.xml)

Navigate to the following location

ORACLE_MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/config/fmwconfig/biinstances/coreapplication

Edit the ActionFrameworkConfig.xml

After you have modified the ActionframeworkConfig.xml file, you have to restart the Managed Server in Weblogic that is hosting your Oracle BI EE environment.

  • Oracle BI Connection Pool

Edit / Add a Connection Pool entry in the Oracle BI Administration Pool. This Connection Pool will be used to connect to Oracle BI Applications

Note  

I – the Application Role must have privileges to execute direct database requests against the Oracle eBS Connection Pool.

II –  for the Application Role (responsibility) to successfully invoke a Navigate to E-Business Suite action, the target Oracle eBS function must be accessible from the user’s current Oracle eBS Context.

Identify Oracle eBS Form Parameter

If you want to navigate to a query the form you are navigating to, you should identify the Oracle eBS Form Parameter. This can be achieved by the following steps.

1. Log into Oracle eBS and select the Form you want to navigate to

2. Identify the name of the Form and the Function which calls this Form

Navigate to the Forms Personalization Screen via Help, Diagnostics, Custom Code, Personalize

  • Forms
  • Function

We will use the name of the Form to identify the possible parameters of this Form. In this case;  APXINWKB. The Function Code will be used later on when creating the actual Action Link.

You can download the related .fmb-file from the Oracle eBS Application Server in the following location; $AU_TOP/forms/NL. The ‘NL’-part refers to the language.

When you open the .fmb-file in Oracle Forms, you should be able to locate the parameters


Action Link

Now everything is in place to create the actual Action Link.

  • Create Action Link

Create the Action Link via New, Action, Navigate to E-Business Suite 

  • Edit Action Link

Now we must edit the specific Action Link details

This consists of;

  • The Function Code (AP_APXINWKB_SUMMARY_VIEW)
  • The Oracle BI Connection Pool (Oracle EBS OLTP Connection Pool – Action Framework)
  • Parameter(s) –> INVOICE_ID

With this Action Link it is possible to call this link directly end fill in the Invoice Id yourself.

Another option is to make the Action Link part of an Oracle BI Answers Request.

  • Edit Oracle BI Answers Request

Select and edit a column in an Oracle BI Answers Request. Navigate to the Interaction Tab. Here we can add / edit an (existing) Action Link.

I have chosen to use a Invoice Number with an Action Link which uses a hidden (Invoice Id) column to navigate to Oracle eBS. This way I do not have to show the Invoice Id in the Oracle BI Answers Request. Still I can use the Invoice Id as a parameter in the Oracle eBS Form.

It’s a little bit of work, but still it’s a nice feature.

– Daan

Setup Oracle BIA 7963 – Data Security

Customer wants to secure their data based on things like Ledger, Company, Operating Unit, etc. All users who login via Oracle eBS should inherit (based on the current responsibility) the same security settings in Oracle BI. The following is a possible solution.

Environment

  • Oracle eBS R12.1.1
  • Oracle BI EE 11.1.1.6.0
  • Oracle BIA 7.9.6.3
Setup

The setup consists of a few different parts

Data Security

The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in Oracle eBS

Oracle eBS

  • Create ‘BI Type User’-profile option
  • Assign ‘BI Type User’-profile option to Responsibility
  • Assign Responsibility to User

Each Responsibility has either a specific ‘BI Type User’-profile option or a ‘BI Type User’-profile option on Site level. A view (xx_obia_user_groups_v) in Oracle eBS ‘holds’ the profile option information.

select fpov.level_value responsibility_id
 , fpov.level_value_application_id application_id
 , 'OBIA '
 || fpov.profile_option_value autorisatierol_code
 , fl.meaning autorisatierol
 from applsys.fnd_profile_option_values fpov
 , apps.fnd_profile_options_vl fpo
 , apps.fnd_lookups fl
 where fpo.profile_option_id = fpov.profile_option_id
 and fpo.application_id = fpov.application_id
 and fpov.profile_option_value = fl.lookup_code
 and fl.lookup_type = 'BI_TYPE_GEBRUIKER'
 and fpo.profile_option_name = 'XXBI_TYPE_GEBRUIKER'
 and fpov.level_id = 10003

Oracle BI

In Oracle BI, there is a Initialization Block which populates the; ROLES Session Variable

select ( select sector
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 || ';'
 || ( select autorisatierol_code &quot;ROLES&quot;
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 || ';'
 || ( select responsibility_key &quot;ROLES&quot;
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 &quot;ROLES&quot;
 from DUAL

Oracle Enterprise Manager (EM)

In the EM ‘all’ the different Application Roles, related to the Data Security are created.

There are a few different Application Roles;

  • Out-of the box
  • Data Security
  • eBS Profile

Out-of-the-Box

  • BIAdministrators
    • Administration privileges.
  • BIAuthors
    • Create, use or consume content.
  • BIConsumers
    • Use / consume content,
    • Every authenticated user.
  • BISystem
    • Component connections between products.

Data Security & eBS Profile

Oracle BI Administrator (Identity Manager)

In the Identity Manager, the Business Model Filters are applied to the Data Security Application Roles

The Business Model Filters are based on the Initialization Blocks. Some out-of-the-box, others custom.

This setup should be sufficient to apply Data Security to all queries, which query the Logical Tables with the Business Model Filters applied to it.

Note: Application roles data filters won’t apply for users with BI Administrator role.

By definition the BIAdministrator application role is granted the “oracle.bi.server.manageRepositories” permission, which is equivalent to the 10g “Administrator” user who also had unrestricted access. Hence, data filters won’t affect users with BIAdministrator Role. (source: Oracle Support)