Monday, March 2nd, 2009

Integration Oracle Apex with E-Business Suite

Oracle Application Express (Oracle Apex) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications. We wanted to extend the E-Business Suite functionality with Apex features. Apex functions need to be available from the EBS user menu. Users have to be able to move seamlessly from EBS to Apex, without logging in twice. The context of the EBS user needs to specify which data the user is allowed to see in Apex. Our goal was to develop a calendar in Apex which shows a team’s absences for the logged in supervisor. In EBS the “supervisor -> employee” relation has a hierarchical structure, which has to be available in the calendar.

The first challenge we have to deal with is authentication. Apex users have to log in to access an application. We used the EBS security to allow users to logon to Apex using their EBS username and password. In Apex we created a custom authentication scheme which calls an external function to validate the username and password. The authentication scheme calls our plsql function “apex_authorise” which uses the EBS “fnd_web_sec.validate_login” to validate the username and password. At this point we are able to log in to an Apex application using any valid EBS username and password combination.

Now we want to link Apex forms from the EBS menu. The first problem is that Apex requires a valid password for the user and the EBS database does not contain passwords, it holds a hash of the username/password combination. You can handle this by creating an alternative password using the DBMS_OBFUSCATION_TOOLKIT md5 function. The password will be generated by hashing the username, a time component and a key held within the database. This mechanism will be handled in two functions: “apex_generate_hash” and “apex_validate_hash”. To check if the password is valid, we extend the already created function “apex_authorise”.

Now we want to login in Apex automatically. We use a cookie to achieve this. The form function we use in EBS, sets a cookie containing the username and password and redirects to the Apex URL to launch the Apex application. In EBS we have to create a web enabled mod_plsql procedure to achieve this. This form function can then be added to the EBS menu. In Apex we use a “before header” process to read the cookie and log the user in.

The cookie we used:


We store the FND_GLOBAL values in global variables in Apex. We use these variables in the page query of an Apex application to only show the results for this specific user. In our case we used the standard calendar feature of Apex. In this calendar you can specify a sql query which selects the data to publish.

Ideally we want to save the context of the environment an APPS user is working in and seamlessly use this context in our Apex application. This way we don’t have to care about which data is visible for the user, the APPS security mechanism will take care of that. This is something which has to be investigated. In our project we send the user information in the cookie, and use this information for our data selection.

 Viewed 16199 times by 5079 visitors

Category: General / Technical
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses (last comment shown first)

April 28, 2010
valerie jones
valerie jones

I’m looking for a method to link out to an external website from the Oracle Applications, passing the username. Any suggestions?

[Reply to this comment]

July 22, 2009
Sergio Coutinho
Sergio Coutinho

Í have an EBS 11.5.9 release (RDBMS, and i would like to integrate with APEX 3.2.
I would like to install APEX on EBS database ( and use EBS apache service.
I tried to execute scripts to install APEX on EBS RDBMS, burt it failed in script checklist step. I think that it´s some conflict with APEX flow and simillar routine on EBS.
Is there any article about how to integrate APEX with EBS in these releases?



[Reply to this comment]

July 6, 2009

I am trying integrating Oracle Apex with EBIZ R12. How about the article mentioned in

i had a clear installation when following the link. The link is very much useful


[Reply to this comment]

June 17, 2009
Zahid Khan
Zahid Khan

An excellent article!
I have seen many questions on this subject at apex users forum. More and more people are working on integration of EBS with Apex now a days. Your article is a great help in this regard.


[Reply to this comment]

March 2, 2009
Paolo Marzucco

Hi Bob,
been there, done that. Anyway, since we had timeout problems using this approach, we developed a simpler solution to handle HR security using the VPD (Virtual Private Database) functionality in Apex. You may want to check it on this site:

Anyway all of our approaches are working until you stay on 11i, because on R12 the web enabled mod_plsql procedures are not supported anymore.

We are all waiting for the official Oracle white paper to do the Apex Apps integration…

Good luck!


[Reply to this comment]