I made several blogposts in the past. That’s why it could be useful to collect these posts into one document. This document covers all the steps needed to integrate Oracle eBS (R12) and Oracle BI (11g) with each other. SSO might be a little bit confusing. In this case it’s meant that you can log into Oracle BI via Oracle eBS without having to log in again and while maintaining context.
I am currently reading the following book; ‘Oracle Business Intelligence Applications: Deliver Value Through Rapid Implementations’. As far as I know it’s the first and only book on Oracle BI Applications. In Chapter 5: Financial Analytics there is a paragraph about analyzing on Subledger Accounting (SLA) Transactions. This functionality is not in the Out-of-the-Box version of Oracle BIA 220.127.116.11, but Oracle provides a patch to make SLA Transaction analyzing possible.
If you want more details about this patch, have look on My Oracle Support. Search for; Patch 13697336 – PROVISION FOR LOADING SUB-LEDGER SLA ENTRIES FOR EXCHANGE GAIN/LOSS (Patch)
It could be of added value to my current project, so I will check it out. More on this later.
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
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
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
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
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.
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.
- Oracle eBS R12.1.1
- Oracle BI EE 18.104.22.168.0
- Oracle BIA 22.214.171.124
The setup consists of a few different parts
- Integration Oracle eBS and Oracle BI
- Data Security
The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in 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
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 "ROLES" 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 "ROLES" 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 ) ) "ROLES" 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
- Administration privileges.
- Create, use or consume content.
- Use / consume content,
- Every authenticated user.
- 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)
- Integrating Oracle eBS R12 and Oracle BI 11g (obibb.wordpress.com)
We have successfully integrated Oracle BI 11g with Oracle eBS R12. We log into Oracle BI 11g via Oracle eBS R12. One of the additional requirements is to have a Log out Link in Oracle BI 11g which takes us back to Oracle eBS R12. Therefor we have added the following to our instanceconfig.xml
<!--Adding Logout Link--> <SchemaExtensions> <Schema name="EBS-ICX" logonURL="http://<Oracle eBS R12 Server:port>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE" logoffURL="http://<Oracle eBS R12 Server:port>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE"/> </SchemaExtensions> <!--Adding Logout Link-->
This works like expected, but every time we do a restart of the BI service, the instanceconfig.xml get’s backed up and replaced a ‘new’ instanceconfig.xml, without the Log out Link.
This ‘problem’ is related to the following BUG; 14249563 on My Oracle Support. The good news is that it will be fixed in 126.96.36.199. The bad news is that at present the only workaround is to manually re-set the link.
There is an interesting (for me at least) topic on OTN about integrating Oracle eBS security into Oracle BI. It is a topic about getting Oracle eBS HR-Security to work in Oracle BI. Robin Moffat has a blogpost about; ‘Validating EBS-BI authentication, without BI’. He refers to an My Oracle Support article (758392.1), which provides some (additional) troubleshooting details.
When I want to simulate Oracle eBS to Oracle BI login via SQL, I use some SQL-statements / scripts, to see what happens or at least should happen. If I need to know which HR-Orgs I could expect based on the Oracle eBS HR-Security I use the following scripts;
Query User – Responsibility — Rol
Check which responsibilities a user has within Oracle eBS.
select fu.user_id , frv.responsibility_id , fa.application_id , frv.responsibility_key , frv.responsibility_name from apps.fnd_responsibility_vl frv , applsys.fnd_application fa , applsys.fnd_request_groups frg , apps.fnd_user_resp_groups_all furga , applsys.fnd_user fu where fa.application_id = frv.application_id and TRUNC ( SYSDATE ) between fu.start_date and NVL ( fu.end_date , to_date ( '31-DEC-4712' , 'DD-MON-YYYY' ) ) and fu.user_id = furga.user_id and TRUNC ( SYSDATE ) between furga.start_date and NVL ( furga.end_date , to_date ( '31-DEC-4712' , 'DD-MON-YYYY' ) ) and furga.responsibility_id = frv.responsibility_id and frv.request_group_id = frg.request_group_id(+) and fu.user_name like :p_user_name and frv.responsibility_name like :p_responsibility_name order by frv.responsibility_name
Excecute Apps Initialize
Run the Apps Initialize script to get the session in context of the user / responsibility combination of your choice, based on the previous query.
BEGIN apps.fnd_global.apps_initialize(user_id, resposnsibility_id, resposnsibility_application_id, security_group_id); END;
Validate the Context
Check the output of the previous step
select fnd_global.resp_id , fnd_global.resp_appl_id , fnd_global.security_group_id , fnd_global.resp_name , fnd_global.user_id , fnd_global.employee_id , fnd_global.user_name , ( select distinct responsibility_key from fnd_responsibility where responsibility_id = fnd_global.resp_id ) responsibility_key from DUAL
If all the above steps have completed like expected, you are able to check the query within the Oracle BI Initialization Block (IB) related to HR-Security. In my case it’s IB; ‘HR Organization’. This IB has the following query;
SELECT DISTINCT 'HR_ORG' ,TO_CHAR(SEC_DET.ORGANIZATION_ID) FROM ( SELECT 'HR_ORG', ASG.ORGANIZATION_ID FROM FND_USER_RESP_GROUPS URP ,FND_USER USR ,PER_SECURITY_PROFILES PSEC ,PER_PERSON_LIST PER ,PER_ALL_ASSIGNMENTS_F ASG WHERE URP.START_DATE < TRUNC(SYSDATE) AND (CASE WHEN URP.END_DATE IS NULL THEN TRUNC(SYSDATE) ELSE TO_DATE(URP.END_DATE) END) >= TRUNC(SYSDATE) AND USR.USER_NAME = 'VALUEOF(NQ_SESSION.USER)' AND USR.USER_ID = URP.USER_ID AND TRUNC(SYSDATE) BETWEEN URP.START_DATE AND NVL(URP.END_DATE, HR_GENERAL.END_OF_TIME) AND PSEC.SECURITY_PROFILE_ID = FND_PROFILE.VALUE_SPECIFIC('PER_SECURITY_PROFILE_ID', URP.USER_ID, URP.RESPONSIBILITY_ID, URP.RESPONSIBILITY_APPLICATION_ID) AND PER.SECURITY_PROFILE_ID = PSEC.SECURITY_PROFILE_ID AND PER.PERSON_ID = ASG.PERSON_ID AND ASG.PERSON_ID = USR.EMPLOYEE_ID AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND URP.RESPONSIBILITY_ID = DECODE(FND_GLOBAL.RESP_ID, -1, URP.RESPONSIBILITY_ID, NULL, URP.RESPONSIBILITY_ID, FND_GLOBAL.RESP_ID) UNION SELECT DISTINCT 'HR_ORG', ORGANIZATION_ID FROM PER_ALL_ASSIGNMENTS_F ASG, FND_USER USR WHERE ASG.PERSON_ID = USR.EMPLOYEE_ID AND USR.USER_NAME = 'VALUEOF(NQ_SESSION.USER)' AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND ASG.PRIMARY_FLAG = 'Y' ) SEC_DET
You should replace the ‘VALUEOF(NQ_SESSION.USER)’ part with either the user or fnd_global.user_name.
Now you are able to see whether the Initialization Block is working like expected and retrieves the same values as it does in Oracle eBS. If you can conform these steps and the the Security isn’t working, it’s probably because one of the related IB’s doesn’t retrieve (all) the necessary values.
This whole post assumes that the integration is setup correctly.
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 188.8.131.52, 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.
There are two sides (Oracle eBS & Oracle BI) where you need to make some preparations.
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.
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)
- instanceconfig.xml (ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1)
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.