OBIEE - Apex Integration

About

You may have sometime to change some data in your application.
Apex is a great tool that allow you to develop and deploy rapidly a web based application.
Here for our example, we must access to a vendor Apex form to change some values on it.
Our goal is to have the report below in OBIEE with one link by vendor that points to the Apex vendor form and that the Apex form show us the data of the vendor.

Articles Related

• OBIEE - Data Navigation (Drill Down, Through, Link, ...)

Steps

Apex Url

Consider the following example:
http://pocserver01:7777/pls/apex/f?p=103:2:1451367446716618::::P2_MDM_SOURCE_SYSTEM_ID,P2_VENDOR_ID:SAP7:DEC-001
Where:

• pocserver01:7777 is the URL of the server.
• pls is the indicator to use the mod_plsql cartridge.
• apex is the database access descriptor (DAD) name. The DAD describes how Oracle HTTP Server connects to the database server so that it can fulfill an HTTP request. The default value is apex.
• f?p= is a prefix used by Oracle Application Express.
• 103 is the application being called.
• 2 is the page within the application to be displayed.
• 1451367446716618 is the session number.
• P2_MDM_SOURCE_SYSTEM_ID,P2_VENDOR_ID are two fields in our form
• SAP7:DEC-001 are the value of two above fields.

To run this example application, we would use the URL:
http://pocserver01:7777/pls/apex/f?p=103:2:::::P2_MDM_SOURCE_SYSTEM_ID,P2_VENDOR_ID:SAP4,DEC-001
When users log in, they receive unique session numbers.

OBIEE

Here below, you have a picture of the example report. Assume that we want to add a link on the vendor id column. This link will go directly in the Apex Interface in the Vendor form on the good vendor data.
The goal is to add the URL above and that we change for each line the value of the parameters with the value of this column :

1. “Vendor Id”
2. “Source System Id”

First, we must say that the column is in a HTML format.

1. Go to the column properties
2. Check “Override Default Data Format”
3. Select HTML

Second, we must change te value in the formula screen to add the link in HTML language.

'<a href=http://pocserver01:7777/pls/apex/f?p=103:2:::::P2_MDM_SOURCE_SYSTEM_ID,P2_VENDOR_ID:' || 
"Source System"."Source System Id" || ',' || "Vendor"."Vendor Id" || '>' || "Vendor"."Vendor Id" || '</a>'

Save and you are done, you obtain a link to Apex Vendor Form with different values for each line.

If you click on the first line, you will access to the edit form from the vendor DEC-001 for the source system SAP7.