Extending E-Business Suite with Oracle Application Express (APEX)

Traditionally E-Business Suite projects have consisted of a number of technologies – Oracle Forms, OAF, Reports, XMLP, SQL*PLUS and so on. Each of these has its own pro’s and con’s however most are reasonably time-tested and well integrated into the E-Business Suite technology stack.

Over recent years Oracle have put a lot of time and effort into the development of Oracle Application Express (APEX) – Formerly HTMLDB – a rapid application development tool for developing web-based applications on the Oracle database. APEX has truly come on leaps and bounds over recent years, which may surprise some people as it is FREE. Yes, FREE. Ok, that may not be strictly true; you still need an Oracle Database with which APEX now comes shipped, and if that is for commercial purposes then you need to licence that database. However you pay no additional licencing fees to use APEX within your existing infrastructure. If you want to just give it a try then you can get yourself a free Oracle-hosted “workspace”, however this should be used strictly for development and proof of concept only as there is no guarantee of service availability etc. You can also download Oracle XE for free and upgrade to the latest APEX version. I have a small Slackware Linux VirtualBox machine with XE installed and the latest APEX release which I use as a sandbox for proof of concepts etc.
For these reasons combined with the overheads of existing technologies and the very shallow learning curve for basic APEX functionality, more and more customers are looking at how they might utilize APEX for their custom E-Business Suite extensions. Whilst it is possible to simply install APEX and query/update tables (via the supported methods of course!) on the E-Business Suite database within a very quick space of time, what customers are generally looking for is seamless integration. That is, their APEX application is fully integrated within their E-Business Suite environment. Users should not even be aware that they are using a “different” technology. Furthermore, the advantages of this are of course:

• No need to manage users separately – users authenticate using their E-Business Suite credentials.
• No separate login pages or links – users log in using the E-Business Suite login page.
• No need for separate security models – application pages and functionality can be controlled using standard E-Business Suite functionality such as responsibilities, security profiles etc.
• Utilization of E-Business Suite context – profile values, fnd_global constants etc.

There are three basic steps to integrating your APEX installation with your E-Business environment

1. Technical Installation
2. Authentication/Seamless Integration
3. Best Practices

I am not going to cover the installation – it is well documented on the link above (ensure you consult the appropriate documentation for the version you are installing). There are several possible options available depending on your infrastructure and E-Business Suite version (Embedded PLSQL Gateway (EPG), Glassfish server etc), however your DBA should be able to advise. Ultimately the EPG is the “quickest and easiest” however has some caveats that should be understood. More information is available from MOS and this excellent diagram.
Authentication and integration shall be the subject of this blog post and I will be posting another blog in the near future which covers best practices for development. A lot of the existing how-to’s on integration out there at the moment are very light on technical content so hopefully this will help bridge that gap.

For now, it shall be assumed you have an APEX installation with a workspace configured that has access to (either via the parsing schema or grants) to the appropriate E-Business Objects you wish to use. I will cover the benefits of parsing as apps or otherwise in my next blog post – assume APPS for now for simplicity.

Note – Parsing as APPS is not officially supported by Oracle. The whitepaper published by Oracle recommends that a minimal privilege schema be created and all objects granted to that. I have my own views on the justification for this which I have expressed to the author, however you should take your own decision on this. If you choose to parse as a custom schema the APEX side of things is exactly the same, however you will need to create grants to your custom schema and then synonyms as that user.

There are several possible ways of carrying out this integration, however this is the method I would recommend. It is best practice to hold as much code as possible on the database (i.e. in database packages) and for the purposes of this post I will assume that package is called XXAPX_UTIL_PK (see bottom of this post for sourcecode). Feel free to use your own naming conventions where you wish for both the package and any custom functions.

This blog assumes you have a reasonable understanding on both E-Business Suite and APEX authentication/authorization.
If you need any further elaboration on the points then please ask in the comments.

So; you have created the basics of a new application and now want to integrate it.

1. Create a new Authentication Scheme called E-Business Suite Authentication with a scheme type of “Custom”.
2. Set the authentication function to be XXAPX_UTIL_PK.EBS_LOGIN
3. Switch your application to use this scheme

The login function basically just verifies there is a valid session. You can extend it to do more if you wish.

  function ebs_login(p_username varchar2, p_password varchar2) return boolean   is     l_valid_session boolean;     l_session_id    icx_sessions.session_id%type;          cursor c_icx is       select icxs.session_id         from icx_sessions icxs        where icxs.session_id = icx_sec.getsessioncookie          and icxs.last_connect + numtodsinterval(icxs.time_out,'MINUTE') > sysdate;   begin     open  c_icx;     fetch c_icx into l_session_id;     l_valid_session := c_icx%found;     close c_icx;          return l_valid_session;        end ebs_login; 

Now, the standard process is as follows:

In short, the user visits a page which requires authentication, the APEX engine redirects to the login page, the user enters their details, the authentication function is executed and the user is directed back to the page they wanted.
However for E-Business Suite authentication we have already had the user enter a username and password – so we shouldn’t be asking them to do it again. What we have is the following:

So a user visits a page as above and is redirected to the login page. However in this instance the login page automatically processes the login and finally redirects the user to where they wish to go. So how do we do that? Well first we create a new Before Header PLSQL process on the login page (traditionally page 101). As always, we hold as much code in the database as possible, so we will just make a call to a procedure in our package XXAPX_UTIL_PK. The key thing we do here is validate the user session first then call the custom authentication scheme we created above so APEX can set up the internal state.

At this point we need to consider one major feature of APEX which is that it is stateless. It does not retain the same database session throughout the lifecycle of application usage. So when you request a page a connection is made to the database at that point, the page is rendered and the session is disconnected. Subsequent pages similarly follow suit. They key thing here though is that the database session id changes between page requests. This can be seen by creating a simple report of

select userenv('sessionid') s from dual; 

Then refresh the page a few times.
The reason this is important is that E-Business Context is set up against a session. So if the session changes you lose your context! We therefore need to ensure that APEX can initiate the EBS context independently of its parent session. To do that we will create four application items and populate them at login with the following:

• User ID
• Responsibility ID
• Responsibility Application ID
• Security Group ID

So back onto our process, we create something along the lines of:

Now let’s look at what processing we do in that procedure. We need to set up the E-Business Suite context which we can retrieve from the ICX Session cookie we saw earlier.

  procedure auto_login   is     l_user_id             icx_sessions.user_id%type;     l_resp_id             icx_sessions.responsibility_id%type;     l_resp_appl_id        icx_sessions.responsibility_application_id%type;     l_security_group_id   icx_sessions.security_group_id%type;     l_user_name           fnd_user.user_name%type;      -- get the context information from the icx session cookie.     cursor c_session     is        select s.user_id,               s.responsibility_id,               s.responsibility_application_id,               s.security_group_id,               u.user_name          from icx_sessions s, fnd_user u         where u.user_id = s.user_id               and s.session_id = icx_sec.getsessioncookie               and s.last_connect + numtodsinterval (s.time_out, 'minute') > sysdate;    begin     open c_session;     fetch c_session     into l_user_id,          l_resp_id,          l_resp_appl_id,          l_security_group_id,          l_user_name;     close c_session;          apex_util.set_session_state ('GBL_USER_ID', l_user_id);     apex_util.set_session_state ('GBL_RESP_ID', l_resp_id);     apex_util.set_session_state ('GBL_RESP_APPL_ID', l_resp_appl_id);     apex_util.set_session_state ('GBL_SECURITY_GROUP_ID', l_security_group_id);      set_apps_context; -- Explained below      -- call the standard apex login procedure to initialize a valid apex session.     wwv_flow_custom_auth_std.      login (p_uname        => l_user_name,             p_password     => '',             p_session_id   => v ('app_session'),             p_flow_page    => v ('app_alias') || ':1');   end auto_login; 

Naturally you will probably want to extend the above to take the next page as a parameter rather than being hard-coded to 1, possibly remove hard-coding of item names and so on… Anyway, in the above code you will see a procedure call to set_apps_context. This has been separated out intentionally because we use it elsewhere. This has the following content:

procedure set_apps_context is begin   fnd_global.apps_initialize (      user_id             => v('GBL_USER_ID'),      resp_id             => v('GBL_RESP_ID'),      resp_appl_id        => v('GBL_RESP_APPL_ID'),      security_group_id   => v('GBL_SECURITY_GROUP_ID')); end set_apps_context; 

Again, one would ideally remove the hard-coding from above and parameterize the item names. You may also want to extend the authentication function to prevent cookie tampering. Again, we’re covering the bare bones here – that might be a topic of a future post!

Finally we need to consider our earlier point on session state. Each time we do anything within our application we need to ensure the E-Business Suite context has been fully set up. To do this we utilize the APEX VPN functionality which allows a function or procedure to be specified that is executed before rendering.

And that’s it from the APEX side. Now you just need to create the E-Business Suite function and add it to a menu. We utilize the existing Gateway (GWY.jsp) call (assuming EBS R12.x):

Finally ensure the FND: APEX URL profile value is set to the base URL of your APEX instance:

And that’s everything! Now just log into E-Business Suite, select your responsibility and click the menu item. You should be launched directly into your APEX application, fully authenticated, and using E-Business Suite context.

There’s a reasonable amount of information above, so here is a short checklist of key thigns you should have done.

1. Compiled your package code into the E-Business Suite instance as your parsing schema.
2. Created a new authentication scheme and assigned it to your application.
3. Created application items to hold the EBS context.
4. Added a new Before Header PLSQL process to the login page.
5. Added in context set code to the VPD section.

You can download both the APEX application and database package I used from here, however anything you install into your system is done so entirely at your own risk.

I will be writing a blog post in the future covering development topics such as:

• Best practices for coding
• Look and feel best practice
• Security considerations for integration
• Controlling access to different areas/functionality within the application
• Querying data from secured applications (such as Human Resources) – although this might form a blog post in itself!

So keep an eye out, and please feel free to comment or ask any questions.