Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

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

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

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

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:

Traditional

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:

Authentication

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

Context

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

Process

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.

VPN

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):

EBS Function

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

Profile

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.

Last modified on
in Technical Hits: 41021 38 Comments
0
My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.


Comments

  • Guest
    John W Monday, 18 May 2015

    John,

    To begin with, we just upgraded Apex 5

    I followed the directions in this blog post and am encountering an error - "Error processing authentication.

    ORA-06550: line 4, column 23: PLS-00306: wrong number or types of arguments in call to 'EBS_LOGIN' ORA-06550: line 4, column 1: PL/SQL: Statement ignored"

    When I look at the custom authentication scheme, it is not passing in any parameters to ebs_login and it calls for login name and password to be passed in.

    What Am I missing..

  • John Keymer
    John Keymer Monday, 18 May 2015

    Hi,
    In the authentication scheme, the authentication function should just be defined as the function name without any parameters - the APEX engine automatically passes in two VARCHAR2 parameters - p_username and p_password.
    So the process flow is something like this:

    1. The user lands on your login page and the auto_login procedure executes.
    2. This procedure calls "wwv_flow_custom_auth_std" which takes in the p_uname and p_password parameters and passes them to the authentication function.
    3. The authentication function executes and returns a true/false as to whether the authentication is successful or not.

    The demo I posted above was done in Apex 4, however the process should be the same for APEX 5. Check the following:

    1. That your Login page (Page 101) is defined as Authentication: Page is Public:

    http://s3.postimg.org/9ms4bokz3/New_apx.jpg

    2. That your authentication function definition on the database (ebs_login) takes in two VARCHAR2 parameters.
    3. That your login process calls "wwv_flow_custom_auth_std". Note - password is null as we don't force the user to enter a password a second time.


    If that doesn't get you going then let me know & I'll reproduce it in an Apex 5 environment.

  • Guest
    John W Monday, 18 May 2015

    John

    A simple context error p_user_name versus p_username was all it took to throw it off kilter.

    have you experienced any problems with a redirect error? I.e., "The page isn't redirecting properly..."

  • Guest
    John W Monday, 18 May 2015

    John -

    My context error.. p_username vs p_user_name

    When page 101 redirects to page 1 (home page) should there be any parameters? I keep encountering a page isn't redirecting properly error.

  • John Keymer
    John Keymer Tuesday, 19 May 2015

    Hi,
    Are you passing the "next page" into the following procedure correctly?

    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' ) || ':' || p_next_page );

    and also removed any of the "standard" login processing from page 101?

  • Guest
    John W Tuesday, 19 May 2015

    John,

    Yes, I am passing ':1' as the page number. This is the URL that is "malformed"
    http://appstest4.fubar.com:7001/ords/f?p=105:1:25729442190411:::::

    105 is the application id and I want to go to page 1.

    John

  • John Keymer
    John Keymer Tuesday, 19 May 2015

    Hi,
    It could be that you don't have an APP_ALIAS set - in my example I set up an application alias however this is optional, so it could be that your URL is getting formed of ":1" rather than ":1". Though from your example above it looks like you've passed in your app_id of 105 anyway.
    The other difference is I was using the EPG rather than REST, however I wouldn't have thought that would cause the issue you describe.

    Your URL looks ok - are you able to temporarily set page 1 to be public and then navigate to that URL directly?

    The other option to investigate is using "OWA_UTIL.redirect_url(l_url);" as per the suggestion at the end of this thread:
    https://community.oracle.com/thread/859767?start=15&tstart=0

  • Guest
    John W Wednesday, 20 May 2015

    John,

    I double checked and standard login processing has been deleted from page 101, page 1 is declared as public. I even triedsubstituting a call to API APEX_CUSTOM_AUTH.LOGIN for the API call to wwv_flow_custom_auth_std.login

    all to no avail.

    However, with page #1 being declared as public (not requiring Authentication) I can access page #1 directly and I get a "data not found" error as I suspected I would.

    There is a glimmer of hope....

    John W

  • John Keymer
    John Keymer Friday, 22 May 2015

    Hi,
    The issue with setting page 1 to public is that anyone can navigate directly to that page without authenticating. Did you try using "OWA_UTIL.redirect_url(l_url);"?

  • Guest
    Adam Wednesday, 03 February 2016

    Hi John,
    Can you elaborate on the XXAPX_UTIL_PK package? Is this a default package that is setup in APEX or something that needs to be compiled prior to setting up the authentication?

    Thanks

  • Guest
    Adam Wednesday, 03 February 2016

    Found the package. I didn't realize it was at the bottom of the instructions. FYI, It may help to place the "requirements needed" at the top of the blog instead at the very bottom.

  • John Keymer
    John Keymer Thursday, 04 February 2016

    Hi, thanks for the feedback; I'll update the post to reflect that.

    John

  • Guest
    Guest Wednesday, 03 February 2016

    Can you elaborate on this more please?

    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('session_id') s from dual; --this seems to be an invalid select statement.

  • John Keymer
    John Keymer Thursday, 04 February 2016

    Hi,
    Apologies, that should have been sessionid not session_id (the perils of typing up the post without having a database to test it on at the time). The query will return the session id of the currently logged in user. The purpose of this is to demonstrate that the session ID can change between pages being rendered in APEX.

    Which piece would you like further clarification on? The stateless connection principle that APEX uses or the logic of the login procedure?

    Thanks

  • Guest
    Paul Wednesday, 03 February 2016

    Hello, I tried out your instructions, but I'm receiving this error:

    Application with the alias "F_1061231011021" does not exist.
    Contact your application administrator.

  • John Keymer
    John Keymer Thursday, 04 February 2016

    Hi,
    It sounds like your function call definition in EBS might be wrong because that looks like it might be a session number. Check your HTML call is in the format:

    GWY.jsp?TargetAppType=APEX&p=123:101

    Where 123 is your application ID (or alias) and 101 is your application login page number.

  • Guest
    Paul Thursday, 04 February 2016

    Hi John,
    I'm actually running it from APEX itself. For reference from EBS, my HTML call is this:

    GWY.jsp?targetAppType=APEX&p=102:1:&Session:51884:20023

    I'm not sure if &Session is necessary, but read somewhere that it was possible it would preserve the session. Ideas?

    Thanks.

  • John Keymer
    John Keymer Thursday, 04 February 2016

    Hi,
    I'd personally omit &SESSION. Also the values following that look a little odd - an APEX URL looks like this:

    f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly


    If you are using an E-Business Suite integrated application then you must launch it from E-Business Suite. Where do you get that error when running from APEX (i.e. at what stage)? When launching the app? That could be the redirect if so - because if it's not launched from EBS then it won't have the redirect parameters.

  • Guest
    Paul Thursday, 04 February 2016

    Thanks for the help, John. I was running it from APEX itself as in "Run this application" to see if it would attempt at least the login screen. I'll run it from EBS.

    Those values are hard-coded for application and responsibility ID. I can omit those as well if EBS should pull the values along.

    I'll give it a shot.

  • John Keymer
    John Keymer Friday, 05 February 2016

    Hi,
    Yes, the only thing you pass from EBS in the URL should be the target app and login page. Everything else will be pulled from the EBS session which is retrieved using the cookie and looking up the session in ICX_SESSIONS. That's all done in the XXAPX_UTIL_PK package.

    So when you launch from EBS, it should direct to page 101 of application 102 based on the URL you posted below. Then on page 101 you have the before header PL/SQL process?

    Check that you have the following patches applied: 12726556 & 12316083 as per MOS note 1306563.1:
    Extending Oracle E-Business Suite Release 12.1.3 and Above Using Oracle Application Express (APEX) (Doc ID 1306563.1)
    https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=533586849492299&parent=DOCUMENT&sourceId=1668590.1&id=1306563.1&_afrWindowMode=0&_adf.ctrl-state=c5846a569_133

Leave your comment

Guest
Guest Tuesday, 24 October 2017