Integration of Oracle Apex with APPS

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:

OWA_COOKIE.send
(name=>’APEX_APPS_’||application,
value=>FND_GLOBAL.user_name||’:’||
apex_generate_hash(FND_GLOBAL.user_name)||’:’||
FND_GLOBAL.user_id||’:’||
FND_GLOBAL.resp_id||’:’||
FND_GLOBAL.resp_appl_id
);

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.