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

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.

Integrating Oracle eBS and Oracle BI EE – Links

The setup for Integrating Oracle eBS and Oracle BI EE is complete. Now we can create links between the two. These links should make it possible to navigate from Oracle eBS to Oracle BI EE and vice versa. Both in context.

For additional details I would refer to the following document on My Oracle Support about Integrating Oracle Business Intelligence Applications with Oracle E-Business Suite.

First thing I want to look at is how to setup the navigation from Oracle eBS to Oracle BI EE. A standard Oracle eBS installation already contains some initial setup. How this is organized could be retrieved using the following query;

SELECT   fa.application_short_name
, fa.application_name
, fda.application_short_name data_application_short_name
, fda.application_name data_application_name
, fdg.data_group_name
, frt.responsibility_key
, frt.responsibility_name
, frt.description responsibility_description
, fff.function_name
, fff.description function_description
, fff.user_function_name
, fm.menu_name
, fm.user_menu_name
, fm.description menu_description
FROM apps.fnd_menus_vl fm
, apps.fnd_form_functions_vl fff
, apps.fnd_menu_entries_vl fme
, apps.fnd_responsibility_vl frt
, apps.fnd_application_vl fa
, apps.fnd_application_vl fda
, applsys.fnd_data_groups fdg
WHERE fm.menu_id = fme.menu_id
AND frt.menu_id(+) = fm.menu_id
AND frt.responsibility_key LIKE '%OBIEE%'
AND fme.function_id(+) = fff.function_id
AND fa.application_id = frt.application_id
AND fda.application_id = frt.data_group_application_id
AND fdg.data_group_id = frt.data_group_id
ORDER BY frt.responsibility_key

There are a few different ways to navigate to Oracle BI EE. As you could have seen in the pervious query, navigation to Oracle BI EE is controlled via functions in menus. The most easy way to navigate is to go directly to your default Oracle BI Dashboard or to the homepage for Oracle BI Answers.

The following query shows an example of the settings for this default navigation;

SELECT fff.function_name
, fff.user_function_name
, fff.description
, fff.type function_type_code
, fft.meaning function_type
, fff.web_host_name
, fff.web_html_call
FROM apps.fnd_form_functions_vl fff
, fnd_lookups fft
WHERE fft.lookup_code = fff.type
AND fff.function_name like '%OBIEE%F'
AND fft.lookup_type = 'FORM_FUNCTION_TYPE'

The ‘web_html_call’ can be adjusted according to your own specific needs. It’s good to have a good understanding of URL Parameters in Oracle BI EE. Venkat has written about this subject a while back. Check his spreadsheet on Google for more details.

Navigating to the default is controlled by the following entries:

  • OracleOasis.jsp?mode=OBIEE&function=Dashboard
  • OracleOasis.jsp?mode=OBIEE&function=Answers

If you want to navigate to a specific Dashboard(-Page) or a specific Answers Subject Area or report, you should extend the default navigation links to look something like the following:

** Dashboard

  • OracleOasis.jsp?mode=OBIEE&function=Dashboard&parameters=PortalPath~/shared/[Folder_Name]/_portal/[Dashboard_Name] –> This will navigate to a specific Oracle BI Dashboard
  • OracleOasis.jsp?mode=OBIEE&function=Dashboard&parameters=PortalPath~/shared/[Folder_Name]/_portal/[Dashboard_Name]%26Page=[Page Name] –> This will navigate to a specific Oracle BI Dashboard Page

The ‘%26’ is used instead of the ‘&’-sign. Make sure you replace all the spaces with ‘%20’. It’s even better trying to avoid using spaces.

I used a site created by Brian Wilson for the Url-encoding. W3Schools.com could also be used as an HTML URL Encoding Reference.

** Answers

  • OracleOasis.jsp?mode=OBIEE&function=Answers&parameters=SubjectArea~[Subject Area Name] –> This will navigate to the Subject Area of your choice
  • OracleOasis.jsp?mode=OBIEE&function=Go&parameters=Path~/shared/[Folder_Name]/[Report_Name] –> This will directly navigate to the Answer of choice (Make sure you replace all the spaces with ‘%20’)

If you go back to the previously mentioned spreadsheet it’s good to take the following into account;

  • ‘function=’ –> is followed by one of the entries in the ‘Command’-column
  • ‘parameters=’–> contains the full url, including ‘Parameters’ if needed
  • after the ‘~’-sign comes the full url to the location of choice

Next in line could be investigating the possibilities of navigating from a Oracle eBS Form directly to an Oracle BI Dashboard including prompts. This way we could navigate ‘in context’.