Action Link for OBIEE

Prerequisite : The security setup to reach OBIEE from E-Business Suite must be completed before creating Action Link (Action link screens enable to drill back into the transactional application screen from an Oracle BI request or dashboard).

Source : Metalink Note - 552735.1

In order to generate an Action Link you will first have to do the following:

1. Identify the Oracle E-Business Suite Application page/function that you want to link to. Obtain the function_id of that page and identify the querystring parameters required by that page. This will have to be done by going through Oracle E-Business Suite documentation.
2. Identify the Oracle E-Business Suite table that will support the parameters needed for the Oracle E-Business Suite function (page) that you want to build an Action link to and create this physical table in the Oracle E-Business Suite OLTP schema as an opaque view.

Here the OE_ORDER_HEADERS_ALL table was chosen because it is at the grain of the Order Header and supplies the HEADER_ID that we can use to join to the warehouse tables that contain the Order Header information. The function_id of the Oracle E-Business Suite page for Order Details and the parameters supported by that page were also identified from Oracle E-Business Suite documentation.

The Action Link URL is generated by calling the FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL() function in the Oracle E-Business Suite Database Schema. For example:

SELECT
HEADER_ID,
fnd_run_function.get_run_function_url(
CAST(fnd_function.get_function_id('ISC_ORDINF_DETAILS_PMV') AS NUMBER),
CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) AS NUMBER),
CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) AS NUMBER),
CAST( VALUEOF(NQ_SESSION.OLTP_EBS_SEC_GROUP_ID) AS NUMBER),
'HeaderId='||HEADER_ID||'&pFunctionName=ISC_ORDINF_DETAILS_PMV&pMode=NO&pageFunctionName=ISC_ORDINF_DETAILS_PMV',
NULL) as ORDER_HEADER_ACTION_LINK_URL
FROM OE_ORDER_HEADERS_ALL


The parameters to the function are:

p_function_id in number,
p_resp_appl_id in number,
p_resp_id in number,
p_security_group_id in number,
p_parameters in varchar2 default null,
p_override_agent in varchar2 default null

Here p_function_id is the function_id of the page that you want to navigate to, the next three parameters pass the security context to Oracle E-Business Suite. The value of these session variables will be set by the initialization block described in sub-section "Creating Init Block for setting Oracle E-Business Suite Context". The fourth parameter is optional and is used if the page you are navigating to accepts parameters. In many cases if you want to navigate to a particular record on the page you are navigating to (Action Links typically do this), you will need to supply those querystring parameters here. The function call returns a URL to the desired function with encrypted querystring parameters.

The next step is joining this opaque view to the base fact table in the Data Warehouse schema. This join represents a join of tables in different database schemas and will therefore happen in the Oracle BI Server.

IMPORTANT: Ensure sufficient filters are applied when requesting any columns from this opaque view so that a small data set is returned to the Oracle BI Server to join with the results from the warehouse schema. For demo environments, where the EBS table contains only thousands of rows, filters can be ignored. However, customer implementations will typically contain millions of rows so appropriate filters are required.

Map into the logical and presentation layers

Map the URL column from the opaque view into the logical star where you want to create the Action Link. Then expose this logical column in the appropriate presentation catalog. Also ensure that the user who will be logging into Oracle E-Business Suite Applications is assigned access to the appropriate presentation catalogs. For example:

Oracle BI Answers Configuration

When including this Action Link column in a report, edit the column properties to indicate this is of type ‘Hyperlink’. That will automatically make this a clickable link in an Answers report. Further customization can be done to embed an image instead of the text.

Oracle BI Presentation Catalog Configuration

Ensure the user who will be logging into Oracle E-Business Suite Applications is set up in the presentation catalog as a user with the appropriate permissions. You can make the dashboard that you want to embed into Oracle E-Business Suite the default dashboard for that user. This will take the user directly to that dashboard when they click on the hyperlink in Oracle E-Business Suite.

Calling Bursting from XML Template

With the standalone BI Publisher, we have useful Bursting tool in order to split and send the reports to relevant people via email. However, when you are using standalone BI Publisher, you may need to connect E-Business Suite and need to initialize the session for multi-org, VPD, etc. to get the relevant data. In order to do that, you will need to use report triggeres to cal a PLSQL wrapper to initialize the session. With the help of data templates, we can easily call PLSQL functions.

There are 3 steps you need to follow up to be successfull on calling PLSQL function;

1. Add a defaultPackage declaration to the Data Template definition, like -
" Version="1.0">
2. Create a package with above name and add a boolean function to it. For my work I gave the function name as beforeReport.
3. The parameters used in the Data Template should be declared (same name/datatype) in the package specification.

<datatemplate name="DATA" defaultpackage="XX_OBIEE_UTIL" version="1.0">
<dataquery>
<sqlstatement name="ROW">
<!--[CDATA[SELECT * from XX_YOUR_TABLE a ORDER BY a.region, a.CUSTOMER_NUMBER ]]-->
</sqlstatement>
</dataquery>
<datastructure>
<group name="ROW" source="ROW">
<element name="RUNDATE" value="RUNDATE">
<element name="xxEMAIL" value="xxEMAIL">
<element name="CUSTOMER_NUMBER" value="CUSTOMER_NUMBER">
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME">
<element name="PRODUCT" value="PRODUCT">
<element name="REVENUE_TYPE" value="REVENUE_TYPE">
<element name="REGION" value="REGION">
<element name="DM" value="DM">
<element name="CAE" value="CAE">
<element name="SECTOR" value="SECTOR">
<element name="CURRENT_NET_REVENUE" value="CURRENT_NET_REVENUE">
<element name="PREVIOUS_NET_REVENUE" value="PREVIOUS_NET_REVENUE">
<element name="VARIANCE" value="VARIANCE">
<datatrigger name="beforeReport" source="XX_OBIEE_UTIL.beforeReport()">
</datatrigger>
/datastructure></datatemplate>

XSL Formatting Objects

When you design BI Publisher Template for the reports, you will need XSL Formatting Objects to make nice reports, like making the cell red as you can see from the picture. The following link will give you many formatting properties that you can make use of for your design.

XSL Formatting Objects

Math and XSLT

The following link is for understanding the Math and XSLT that you may need to focus on when you build your BI Publisher templates.

Math and XSLT

DBMS_XMLGEN

DBMS XMLGEN is a PL/SQL package that allows programmers to extract XML data from Oracle database tables. It might be useful when you need a XML file to create XML publisher layouts. Here are 2 examples on how to use it;

SELECT DBMS_XMLGEN.getXML('SELECT * FROM emp') FROM dual;

or
DECLARE
ctx DBMS_XMLGEN.ctxHandle;
xml CLOB;
BEGIN
ctx := dbms_xmlgen.newcontext('select * from emp');
dbms_xmlgen.setrowtag(ctx, 'MY-ROW-START-HERE');
xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(substr(xml,1,255));
END;
/

MultiOrg setting for Concurrent Request in R12

While you were upgrading to R12, if you will not implement MOAC, you should make relevant changes for Multi Org concept. In order your custom reports to be run properly, make them "Single" org to continue using without implementing MOAC.

• Login into application with 'System Administration' responsibility
• Click on 'Programs' under 'Concurrent' menu.
• Query for short name of the concurrent program (i.e. XXSIPRNT_CA ).
• Click on Update pencil icon of your program under result region
• Under 'Update Concurrent Program' region, select Request tab
• Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit Mode' field
• Save changes by clicking on 'Apply' button.

Or

update FND_CONCURRENT_PROGRAMS
set multi_org_category='S'
where concurrent_program_id=45718;

Enabling OAF Personalization

To enable Personalization:

• FND: Diagnostics should be set to “Yes”. This will allow the “About this Page” link to be displayed on the OA pages.
• Personalize Self-Service Defn should be set to “Yes”. This will allow the “Personalize Page” link to be displayed on the OA pages.
• FND: Personalization Region Link Enabled should be set to “Yes”. This will allow the Personalize Region links to be displayed at the region level.
• FND: Developer Mode = YES (Optional for debugging)

Use Functional Administrator -> Core Services -> Profiles to change this.

R12 and JRE 1.5.10

In order to run R12, use JRE 1.5.10 which is located under the http:///OA_HTML/oaj2se.exe

After installing this, Internet Explorer and Firefox can run the R12 forms screens properly.

You may need to make adjustments on the Java Runtime settings in order your browser can use the proper version of Java. Check the following settings;

Settings -> Control Panel Java -> Java -> Java Application Runtime Settings

The "Show Log" feature in R12

The "Show Log" feature is available only to the System Administrator responsibility by default. Access is controlled using Function Security. Users can be granted access by including one or more of the following submenus or functions on a user's responsibility:

• Log Search: OAM_BF_SYSLOG_READ_ONLY_MENU (Log Search: Read Only) Menu
• Log Delete: OAM_BF_SYSLOG_DELETE (Log Delete) function
• Log Setup: OAM_BF_SYSLOG_CONFIG (Log Setup) function
• Full access (Search/Configure/Delete): OAM_BF_SYSLOG_ALL_MENU (Log Search: All Functions) Menu.

NOTE: After making these changes please ensure that you bounce Apache.

OAF JDeveloper Version

How to find the correct version of JDeveloper to use with E-Business Suite 11i or Release 12 - Use Metalink Note:416708.1

In order to find the information regarding your installation use the link of your EBusiness Installation;
http://host:port/OA_HTML/OAInfo.jsp

NOTE : Starting from 12.0.4 the page http://host:port/OA_HTML/OAInfo.jsp is not supported anymore, instead we need to login to Applications and select “About this Page” link. It will show us the OAF level and much more information.