Integrating Oracle eBS R12 and Oracle BI 11g

I have made a blogpost in the past about integrating Oracle eBS R12 and Oracle BI 10g. In the course of an upgrade of Oracle BI Applications (OBIA) to OBIA 7.9.6.3, I came to the subject of integrating  Oracle eBS R12 and Oracle BI 11g. Of course you should start with the documentation. Next to that, Oracle provides a note on Oracle Support (ID 1343143.1). A lot of integration steps are equal to the 10g version.

In short:

There are two sides (Oracle eBS & Oracle BI) where you need to make some preparations.

Oracle eBS:

You need to define the link from to Oracle eBS to Oracle BI. This functionality hasn’t changed and I have described that process here. Combined with the ‘FND: Oracle Business Intelligence Suite EE base URL’-profile option in Oracle eBS, you now are ready to navigate from Oracle eBS to Oracle BI.

Oracle BI:

Now the Oracle eBS side is ready, you’ll have to prepare Oracle BI for accepting login requests from Oracle eBS. This parts differs a little from 10g. The changes in the Repository are still the same and consist of validation of the ICX-cookie in the Oracle eBS Connection Pool and the Authentication via Session variables. You can choose to either authenticate via the GROUP- system variable or directly via the new 11g ROLES-system variable.

After that you need to change the Oracle BI configuration;

  • authenticationschemas.xml (ORACLE_HOME/bifoundation/web/display)

authenticationschemas.xml (SchemaKeyVariable)

authenticationschemas.xml

  • instanceconfig.xml (ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1)

instanceconfig.xml

Note: Don’t get mislead by the following sentence; ‘<!–This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control–>’. You must adjust these settings directly in the instanceconfig.xml itself.

This should (all) be sufficient to log into Oracle BI via a selected responsibility in Oracle.

In a following post I will cover the subject of applying Data Security in Oracle BI, based on the Oracle eBS Responsibility.

Automatic login to Oracle BI via Oracle eBS

I have been blogging about the integration between Oracle eBS and Oracle BI. It’s possible to login to Oracle BI via Oracle eBS. In these situations it’s necessarily to have an account and a responsibility in Oracle eBS.

Some people make use of Oracle BI and not Oracle eBS. Normally the wouldn’t need an Oracle eBS account.

In cases of an integration between the two systems you have to login to Oracle eBS, select a responsibility and select a link to Oracle BI.

For some people these are to many steps to get to Oracle BI.

It’s possible to set a default start page when you login to Oracle eBS. You could eg. choose ‘Oracle BI Answers’ as your default start page.

This could be set per user via the Preferences of the user who has logged in.

Here you could set the start page

Be careful setting this option, because once you set it, you wont be able to return to Oracle eBS. In such a case when a user wants to get rid of this startpage use the Oracle eBS Profile Option; ‘Applications Start page’. This option could be set for a specific user or a responsibility via the ‘System Administrator’-responsibility.

Via ‘Profile’, ‘System’, you can find the specific ‘System Profile Value’

For the user; ‘DBAKBOORD’ the value of this profile is;

This value can be retrieved from the database via the following query;


select fpov.level_value user_id

, fu.user_name

, fpov.profile_option_value

from applsys.fnd_profile_option_values fpov

, apps.fnd_profile_options_vl fpo

, applsys.fnd_user fu

where fpo.profile_option_id = fpov.profile_option_id

and fpo.application_id = fpov.application_id

and fu.user_id = fpov.level_value

and fpo.profile_option_name = 'APPLICATIONS_START_PAGE'

and fpov.level_id = 10004

The output of this query is the following;

Using the Oracle eBS Profile Option; ‘Applications Start page’ could also be an easy solution to set a startpage for a whole group of user. After setting this option they all will login to Oracle BI Answers directly without selecting the menuoption in Oracle eBS first.

SLS: Subledger Security

I am in the proces of implementing security for Oracle BI Apps 7.9.6 in a Oracle eBS R12 environment. One of the requirements is Subledger Security (User Guide, My Oracle Support).

As per the documentation; Subledger Security is an extension to Oracle Financials that enables the user to selectively partition data within a single install of Oracle Financials. Subledger Security provides a system where all business units can access their own financial information only.

In my clients case it makes it possible to secure parties, customers and suppliers. Certain customers are only visible for selected responsibilities within the same operating unit.

For implementing the security I am interested in the database-implementation of SLS. Check the following diagram:

For all the Subledger Security tables you have to refer to the IGI-scheme in Oracle eBS:

Whether Subledger Security is applied depends on two profiles;

  • ‘Subledger Security : Security Group’
  • ‘Subledger Security : SLS Responsibility’

A query to retrieve the values of these profiles could be;


SELECT r.responsibility_id ,
  r.responsibility_key ,
  r.responsibility_name ,
  nvl(sr.profile_option_value, 'N') sls_responsibility ,
  nvl(sg.profile_option_value, 'No SLS Responsibility') sls_security_group ,
  nvl(i.sls_group, 'No SLS Responsibility') sls_group
FROM
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : Security Group'
  AND t.language                 = 'US'
  ) sg ,
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : SLS Responsibility'
  AND t.language                 = 'US'
  ) sr ,
  igi_sls_groups i,
  fnd_responsibility_vl r
WHERE sr.responsibility_id = sg.responsibility_id (+)
AND sr.responsibility_id (+)  = r.responsibility_id
AND i.sls_group (+)        = sg.profile_option_value ;

Other queries to find out which data a responsibility is allowed to view:

The query below gives insight in the available SLS Security Groups, eg”.: ‘AR Security for Dept A’

SELECT sls_groups --'AR Security for Dept A' 
FROM   igi_sls_groups
WHERE  sls_group_type = 'S';

The query below gives insight in which tables are ‘SLS Security’-enabled within a certain SLS Security Group.

SELECT sls_group --'AR Security for Dept A'
,      table_name -- HZ_PARTIES 
FROM   igi_sls_enabled_alloc_v;

The query below gives insight in which ‘IG_SLS_#’-table the ‘SLS-Security’-details are stored. Depending on the configuration, multiple ‘IG_SLS_#’-tables  (IG_SLS_1, IG_SLS_2, IG_SLS_3, IG_SLS_4, IG_SLS_5, etc.) can exist.

SELECT owner -- AR
,      table_name -- HZ_PARTIES
,      sls_table_name -- IG_SLS_1 
FROM igi_sls_secure_tables;

The query below gives insight in the actual id’s which are secured by the ‘SLS-Security’-settings. The rowid of the source table is stored in the ‘IG_SLS_#’-table.

SELECT sls1.sls_rowid    
,      sls1.sls_sec_grp    
,      p1.party_id    
,      p1.party_number    
,      p1.party_name 
FROM   igi_sls_1 sls1 -- HZ_PARTIES    
,      hz_parties p1
WHERE  p1.ROWID = sls1.sls_rowid;

Using these queries, you should be able to select which id’s are available to a selected responsibility. Within the Oracle eBS R12 application, this is managed by policies (VPD – Virtual Private Database, RLS – Row Level Security)

Check the following query, to see how it’s setup;

select object_owner --'AR'
,      object_name  -- 'HZ_PARTIES'
,      policy_name  -- 'IGI_SLS_3_POL'
,      pf_owner     -- 'APPS'
,      function     --'IGI_SLS_1_FUN'
from   dba_policies
where  policy_name like 'IGI%'

With this information I am able to built ‘SLS Security’ into Oracle BI Applications.

Oracle eBS Release 12.1.1 and Internet Explorer 8

Oracle eBS Release 12.1.1 and Internet Explorer 8; it’s not always a happy marriage. Although Internet Explorer 8 is certified with E-Business Suite Release 12, you could experiencing problems. If this is the case, the following could be a solution.

– Add URL to Local Intranet Trusted Sites

Internet Explorer Menu => Tools => Internet Options => (Tab)Security => Local Intranet => (Button) Sites => (Button) Advanced

** Add the URL

– Internet Explorer Menu => Tools => Internet Options => (Tab)Security => (Button) Custom Level => (Option) Scripting =>

   ** Enable XXS Filter => Disable

– Disable Google Toolbar

Good Luck.

Navigating Back to Oracle eBS from Oracle BI EE

In one of the my earlier posts I was able to navigate from Oracle eBS directly into Oracle BI EE. By default I was not able to navigate back to the Oracle eBS homepage. After a little research on Google and My Oracle Support I found a link with a; ‘How To : Navigating Back to EBS from OBIEE’ (Id: 872092.1).

Check the following in your Oracle BI Dashboard Menu.

Add the LogoffUrl-tag to your instanceconfig.xml and restart the Presentation Server

<LogoffUrl> <a href="http://localhost:8000/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE">http://serverUrl:8000/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE</a>         
</LogoffUrl>

 Your instanceconfig.xml-file will look something like this:

<ExternalLogon enabled="true">
<LogoffUrl> <a href="http://localhost:8000/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE">http://serverUrl:8000/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE</a>         
</LogoffUrl>
  <ParamList>
  <Param name="NQ_SESSION.ICX_SESSION_COOKIE"
  source="cookie"
  nameInSource="VIS"/>
  <Param name="NQ_SESSION.ACF"
  source="url"
  nameInSource="acf"/>
  </ParamList>
</ExternalLogon>
</Auth>  

Replace the LogoffUrl according to your specifications
Check the your Oracle BI Dashboard Menu again and verify that a ‘Log Out’-link is in place. Via this link you can navigate back to the Oracle  eBS Homepage.