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;

 

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.