Multiple APEX listeners, one Application Server using Oracle REST Data Services (ORDS)

Oracle REST Data Services (ORDS) gives us the capability us to deploy multiple versions of the APEX listeners to a single Glassfish instance.  This is great for centralizing resources on a single server in our non-production environments.  This short post will show you how quick and painless it is to setup.

Setting up 2 instances (apptest & appdev)

Using ords.2.0.8.163.10.40.zip

Set up 2 directories and unzip into both directories

for example:

• <some path>/apptest
• <some path>/appdev

Rename the ords.war files in both

for example:

• <some path>/apptest/ords_apptest.war
• <some path>/appdev/ords_appdev.war

Run the war config process on each war using your specific setup info for each app

• cd <some path>/apptest
• java -jar ords_apptest.war

note: we use app and date specific configurations file locations 

<some path>/apptest/ords_apptest_<today's date>_cfg

• cd <some path>/appdev
• java -jar ords_appdev.war

note: we use app and date specific configurations file locations 

<some path>/appdev/ords_appdev_<today's date>_cfg

Configure the "Listener Administrator" user with your specific setup info for each app

• cd <some path>/apptest
• java -jar ords_apptest.war user username "Listener Administrator"
• enter password
• confirm password

• cd <some path>/appdev
• java -jar ords_appdev.war user username "Listener Administrator"
• enter password
• confirm password

Configure images war files with 

• cd <some path>/apptest
• java -jar ords_apptest.war static --context-path /i_apptest <glassfish install path>/glassfish/domains/domain1/docroot/i_apptest

• cd <some path>/appdev
• java -jar ords_appdev.war static --context-path /i_appdev <glassfish install path>/glassfish/domains/domain1/docroot/i_appdev

Copy images from each APEX instance to glassfish as needed

• <glassfish install path>/glassfish/domains/domain1/docroot/i_apptest
• <glassfish install path>/glassfish/domains/domain1/docroot/i_appdev

Deploy war files to glassfish

• ords_apptest.war
• i_apptest.war
• ords_appdev.war
• i_appdev.war

Note: if you have already chosen /i/ as the images context during setup (instead of specifying /i_apptest/ or /i_appdev/), run the reset_image_prefix.sql in <apex installation folder>/utilities and specify the correct context for your image files. This might take some time to finish so be patient.

Conclusion

There you have it, a down and dirty, quick and easy setup of multiple listeners for your different APEX installations on a single Glassfish install.

Custom EBS 12.2.3 to APEX 4.2.5 Integration Part 4: APEX Application Setup

Before setting up your APEX application, you will need to know 3 things about your EBS to APEX env

1. The name of the cookie for APEX in the custom LaunchApex.jsp (apexCookieName) from Part 2
2. The Responsibility IDs of the responsibilities that are allowed to access this application
3. The URL of the EBS application Navigator Page (after login). 

For our example:

1. VISAPEX
2. 20420 (System Administrator)
3. http://YOURSERVER.YOURDOMAIN.com/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

Quick query to get a list of responsibility ids and names:

select 
  responsibility_id
  ,responsibility_name 
from
  fnd_responsibility_tl
order by 
  responsibility_id
;

Create Application

Create your application with whatever basic steps you take.

Create Application Items for Global Usage

Shared Components -> Application Items

• G_FND_GLOBAL_USER_ID
• G_FND_GLOBAL_RESP_APPL_ID
• G_FND_GLOBAL_RESP_ID
• G_FND_GLOBAL_SEC_GROUP_ID

Create Custom Authentication Scheme

For the Authentication Scheme, we don't need any specific code.  Just setting the authentication scheme, we force all users to Page 101 (Login) where we will handle all the true authentication steps. For the Post-Logout URL we are going to add CLOSE to the request to trigger a redirect back to EBS.

Shared Components -> Authentication Schemes

• Name: EBS2APEX
• Scheme Type: Custom
• Post-Logout URL: f?p=&APP_ID.:101::CLOSE

Create Custom Authorization Scheme
"BASIC USER"

For the Authorization Scheme we are going to do something very simple.  No need to over-complicate this piece. We are going to look in the Substitution String we will create in the next section and see if the responsibility ids listed in the ALLOWED_RESPONSIBILITIES contain the responsibility id associated with the EBS session identified by the session cookie.

Shared Components -> Authorization Schemes

• Name: Basic User
• Scheme Type:: PL/SQL Function Returning Boolean
• PL/SQL Function Body:
  RETURN instr(:ALLOWED_RESPONSIBILITIES,:G_FND_GLOBAL_RESP_ID) > 0;
• Identify error message… : You are not authorized to view this page.

Create Substitution Strings 

These three substitution stings will contain the information required at the top of this post.

Application # ->Edit Application Properties

• COOKIE_NAME - VISAPEX
• ALLOWED_RESPONSIBILITIES - 20420
• EBS_LINK - http://YOURSERVER.YOURDOMAIN.com/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

note: for multiple allowed responsibility ids use a comma sorted list. For example 20420, 20421, 33333, etc. In future releases of our apps we will use an intermediate table to add/remove responsibilities to each separate application.

Enable Authorization Scheme 

“BASIC USER” 

Application # -> Edit Application Properties -> Security Tab

Set Authorization Schema to Basic User

Edit Page 101 (Login Page)

Create Before Header Process

CreateSessionFromCookie 

In this section we create the main authentication and login process. We will grab the value of the VISAPEX cookie and set the session state from the associated icx_session values. 

Security wise, exceptions happen with any of the following:

• No Cookie
• Cookie has an invalid session id
• No Responsibility Id associated with the session id

Now we know this isn't best practice, but we used the "WHEN OTHERS" handling.  From a security standpoint we do not want the user to see any part of the error they are causing at login.  Yes, this can be a pain to troubleshoot, but the function below has very few parts and a specific set of conditions that have to be met for success.  Basically: fewer moving parts, fewer thing to troubleshoot. Keeping it simple! 

Create a Before Header Process

• Name - CreateSessionFromCookie
• Source - 

DECLARE
    l_cookie      OWA_COOKIE.COOKIE;    l_sessionID   varchar2(100);    l_session_id  NUMBER;    l_userID      NUMBER;    l_username    VARCHAR2(512);
    l_respID      NUMBER;    l_respapplID  NUMBER;    l_secgroupID  NUMBER;

    l_cookieName varchar2(512) := :COOKIE_NAME;

BEGIN

    l_cookie := owa_cookie.get(l_cookieName);    l_sessionID := l_cookie.vals(1);     

    --is EBS session valid, will throw exception if not valid    app_session.validate_icx_session(l_sessionID);

    --has a valid EBS session, but has user chose a responsibility in EBS     select responsibility_id into l_respID from icx_sessions where XSID =  l_sessionID;   

    IF l_respID <> -1

    THEN

       --user has a good session and has a responsibility id assigned in session       --set global variables and login to APEX

       select user_id into l_userID from icx_sessions where XSID =  l_sessionID;       select user_name into l_username from fnd_user where user_id = l_userID;       select responsibility_application_id into l_respapplID from icx_sessions where XSID =  l_sessionID;       select security_group_id into l_secgroupID from icx_sessions where XSID =  l_sessionID;

       apex_util.set_session_state('G_FND_GLOBAL_USER_ID',l_userID);       apex_util.set_session_state('G_FND_GLOBAL_RESP_ID',l_respID);        apex_util.set_session_state('G_FND_GLOBAL_RESP_APPL_ID',l_respapplID);           apex_util.set_session_state('G_FND_GLOBAL_SEC_GROUP_ID',l_secgroupID);              apex_custom_auth.post_login(

              p_uname         => l_username,              p_session_id    => nv('APP_SESSION'),               p_app_page      => apex_application.g_flow_id || ':1',               p_preserve_case => FALSE

       );

    END IF;

EXCEPTION

   -- no cookie, session id, or chosen responsibility    WHEN OTHERS THEN NULL;

END;

• Condition Type - Request != Expression 1
• Expression 1 - CLOSE

Create Before Header Process

RedirectBackToEBS

In this process we will create the action when someone clicks the logout link. In our case we want the user redirected back to EBS using link in the Substitution Strings.

Create a Before Header Process

• Name - RedirectBackToEBS
• Source - owa_util.redirect_url(:EBS_LINK, true);
• Condition Type - Request = Expression 1
• Expression 1 - CLOSE

Create a new Login Button

This section is partly optional. We set the default P101_USERNAME, P101_PASSWORD fields and the P101_LOGIN button Condition Type to "Never".  We change the username and password fields to never because we do not allow direct logins on the APEX login page. We don't really have a good reason to "Never" the login button. We could just change the action of login button to the actions we use when we create the new button, but we like a separate button for this. It's just how we roll!

Change Condition Type to Never:

• P101_USERNAME
• P101_PASSWORD
• P101_LOGIN

Create Page Item Button in Login Region:

• Name - P101_LOGIN_TO_ORACLE
• Text Label/Alt - Login to Oracle
• Action - Redirect to URL
• URL Target - &EBS_LINK.

Conclusion

Now you should be able to login to EBS with System Administrator responsibility and click on your Auth_Test link to enter APEX. We have tested several different methods to make sure a user can not enter without logging into EBS and have found only a few that work. But they all involve APEX retaining its current session cookie.  So this is exactly as expected. 

We hope this helps you in your environment or at least gets you pointed in the right direction.  Feel free to comment and ask questions. We look forward to your feedback!

Custom EBS 12.2.3 to APEX 4.2.5 Integration Part 3: EBS Function and Menu Setup

The following steps were done while logged into EBS with the System Administrator responsibility.

note: The System Administrator menus are used as references.  The same techniques can be used to create menu entries under other responsibilities as well.

Set APEX Function path

System Administrator -> Profile -> System
In the “Find System Profile Values” -> Profile field enter FND: APEX URL and press the Find button

Change the Site value to your appropriate path and save.
For example: http://yourserver.yourdomain.com:8080/ords

Create Form Function

Application -> Function

Description Tab:

• Function Name
• User Function Name
• Description

Properties Tab - Type: SSWA jsp function

Web HTML Tab - HTML Call: LaunchApex.jsp?targetAppType=APEX&p=900:1
(using application id 900 and page 1, make sure the application ID matches what you are using for your APEX app. )
Notice we are not sending anything else in the call.  Therefore, no other information will be sent in the calling URL!

Adding Menu Prompt

Application -> Menu

Create a new entry(see bottom entry in image below):

• Prompt - whatever you wish the user to see
• Submenu - depends on your setup
• Function - the function we created above
• Description - optional

Notes:

• You can set up multiple Form Functions. We use one for each application. For example, HR and Sales each have different Form Functions and menu entries.  
• On the APEX side we have completely separate applications for each group.  Therefore we can completely separate responsibilities and groups that are affected with changes in any applications.

Custom EBS 12.2.3 to APEX 4.2.5 Integration Part 2: LaunchApex.jsp

We didn't alter any of the original GWY.jsp code. Instead we added a piece to capture the value of the current EBS session cookie and rewrite it to another cookie that APEX will expect. By using the GWY.jsp, in the case where someone has bookmarked the urls within EBS to launch APEX, we can lean on the session checking already included. Anyone without a valid session will be sent to the Oracle login page.

Where does LaunchApex.jsp go on the server?

$OA_HTML should point to the running file system.
For example: 

<some path>/fs1/FMW_Home/Oracle_EBS-app1/applications/oacore/html

For production you would only place in the non-running or Patch file system, then compile and wait for a patch cycle to commit to the running file system.  But in our test environment we place in both file systems

For example:

<some path>/fs1/FMW_Home/Oracle_EBS-app1/applications/oacore/html

<some path>/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html

How is LaunchApex.jsp used?

LaunchApex.jsp will be set in the function call within EBS and we will see this in another post.

But first we have to compile the jsp and restart Weblogic so the new jsps are ready for use.

For Example:
cd <some path>/fs1/FMW_Home/Oracle_EBS-app1/applications/oacore/html

$FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'ZEUS_LaunchApex.jsp' –flush

cd <some path>/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html

$FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'ZEUS_LaunchApex.jsp' --flush

Restarting Weblogic Server

admanagedsrvctl.sh stop oacore_server1

admanagedsrvctl.sh start oacore_server1

The Cookie Variables explained:

        String ebsCookieName = "VIS"; - this is the name of the EBS cookie, there are several articles on the internet that show you how to locate this value either in your browser or in your database.

        String apexCookieName = "VISAPEX";   - this is the name of the cookie for which APEX will be looking in the browser. In the APEX application this is a substitution string setting (don't forget to set it)

        String apexDomain = ".YOURDOMAIN.com"; - the SHARED domain between apex and ebs. Not setting this could cause an issue if you have any custom code that uses a default domain that isn't correct.  Maybe a bad clone from another instance kept an older value or something to that effect.The leading "." isn't strictly required but some browsers may forget to add it, so explicitly setting is a good idea.

        String apexPath = "/"; - another parameter that isn't required but could be set to an undesirable value by your webserver due to any number of ways defaults/non set values are handled, so explicitly setting is a good idea.

        boolean isCookieSecure = false; - If both servers are using SSL, change this to true. Otherwise leave alone.

Full listing for our LaunchApex.jsp 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<%--

/*===========================================================================+

 |      Copyright (c) 2009 Oracle Corporation, Redwood Shores, CA, USA       |

 |                         All rights reserved.                              |

 +===========================================================================+

 |  FILENAME                                                                 |

 |    GWY.jsp                                                                |

 |                                                                           |

 |  DESCRIPTION                                                              |

 |    GWY.jsp handles external application URL embedding within              |

 |    E-Business Suite. GWY expects to be invoked only from RF as            |

 |    standard function invocation.                                          |

 |                                                                           |

 |  DEPENDENCIES                                                             |

 |                                                                           |

 |  HISTORY                                                                  |

 |    01-AUG-2009   raghosh     created                                      |

 +===========================================================================*/

--%>

<%@ page contentType="text/html;charset=windows-1252"%>

<%@ page import="java.util.Map"%>

<%@ page import="java.util.HashMap"%>

<%@ page import="java.util.Enumeration"%>

<%@ page import="java.util.Iterator"%>

<%@ page import="oracle.apps.fnd.common.VersionInfo"%>

<%@ page import="oracle.apps.fnd.services.gwy.ExternalAppManager"%>

<html>

  <head>

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

    <title>Oracle Applications External Gateway - Custom Launch Apex 20140801 - 5</title>

  </head>

  <body>

  

    <%! public static final String RCS_ID =  "$Header: GWY.jsp 120.3.12020000.1 2012/06/30 05:28:11 appldev ship $"; %>

    <%! public static final boolean RCS_ID_RECORDED =  VersionInfo.recordClassVersion(RCS_ID,"oa_html"); %>

    

    <%

        

        Enumeration<String> paramNames = request.getParameterNames();

        Map<String, String> params = new HashMap<String, String>();

        while(paramNames.hasMoreElements()) {

            String param = paramNames.nextElement();

            String paramVal = request.getParameter(param);

            if (!(paramVal == null || "".equals(paramVal)))

                paramVal = paramVal.trim();

            params.put(param, paramVal);

        }

        //boolean debugMode = "Y".equalsIgnoreCase(params.get("debug")) ? true : false;

        //if (debugMode) {

        //      Iterator iter = params.entrySet().iterator();

        //      while (iter.hasNext()) {

        //              Map.Entry aPair = (Map.Entry) iter.next();

        //              out.println(String.valueOf(aPair.getKey()) + "=" + String.valueOf(aPair.getValue()) + "<br>");

        //      }

        //}

        String targetType = params.get(ExternalAppManager.EXTERNAL_APP_TYPE_PARAM);

        if (targetType == null || "".equals(targetType))

            targetType = (String) request.getAttribute(ExternalAppManager.EXTERNAL_APP_TYPE_PARAM);

            

        String handlerClass = params.get(ExternalAppManager.EXTERNAL_APP_HANDLER_PARAM);

        if (handlerClass == null || "".equals(handlerClass))

            handlerClass = (String) request.getAttribute(ExternalAppManager.EXTERNAL_APP_HANDLER_PARAM);

        //String authFunction = params.get(ExternalAppManager.EXTERNAL_APP_AUTH_FUNCTION);

        ExternalAppManager manager = new ExternalAppManager(request, response, targetType, handlerClass);

        manager.logParams(params);

        

        /** COOKIE REWRITE BEGIN **/

        // COOKIE VARIABLES 

        String ebsCookieName = "VIS";

        String apexCookieName = "VISAPEX";   

        String apexDomain = ".YOURDOMAIN.com";

        String apexPath = "/";

        boolean isCookieSecure = false;

        // RETRIEVE EBS COOKIE

        Cookie[] cookies = request.getCookies();

        Cookie ebsCookie = null;

        if (cookies != null) {

            for (int i = 0; i < cookies.length; i++) {

                if (cookies [i].getName().equals (ebsCookieName)) {

                    ebsCookie = cookies[i];

                    break;

                }

            }

        }

        

        // CREATE APEX COOKIE USING EBS COOKIE VALUE        

        Cookie apexCookie = new Cookie(apexCookieName, ebsCookie.getValue());

        apexCookie.setDomain(apexDomain);

        apexCookie.setPath(apexPath);

        apexCookie.setSecure(isCookieSecure);

        

        // ADD COOKIE INTO RESPONSE

        response.addCookie(apexCookie);    

        

        /** COOKIE REWRITE END **/

        

        manager.doForward(params, false);

        manager.releaseResources();

    %>

  </body>

</html>

Custom EBS 12.2.3 to APEX 4.2.5 Integration Part 1: The obligatory listing of caveats, gotchas, and mumbo jumbo

This part is to introduce you to what we used and why we did it this way. 

Software Versions:

Oracle E-Business Suite 12.2.3

Oracle Application Express: 4.2.5.00.08

Glassfish Server: 4.0

Oracle REST Data Services: 2.0.8.163.10.40

General Approach:

Requirements:

• Users cannot log directly into APEX for EBS integrating applications
• Users MUST log into Oracle EBS first
• APEX applications must be assigned to Responsibilities for inclusion in Menus for those responsibilities not to individual users or a custom APEX Responsibility.  For example, an HR APEX application should be accessible from HR specific responsibilities only.  
• APEX access must work in a mix of environments SSL and non-SSL.  For instance, EBS uses SSL but APEX (being a non-externally facing application) is non-SSL
• EBS to APEX must not pass anything user identifiable in the URL, including responsibility IDs
• Each Responsibility group must not be able to access the other responsibilities applications. In other words, HR should not be able to access Sales applications in APEX nor see any menu items for Sales APEX applications 

Caveats:

• Our code is minimal in both commenting and error handling. This is done on purpose to make it easier to understand and for quick adaptability into your environment (should you choose to use it).
• Yes we use a customized jsp based on the GWY.jsp.  In order to accomplish the requirement for working in mixed SSL and non-SSL environments, we had to make a non-secure copy of the EBS session cookie that could be read in non-SSL APEX pages. We know, we know... we really should be full SSL everywhere, even if it's non-externally facing.
• We also realize that this solution is not supported by Oracle and any function call we make today may not work in the future.  But how is this different from any other customization we have to make in house?  Every organization is different and has different needs.  We make do with what we have.
• We host several APEX instances on a single Glassfish server so urls contexts may seem a little strange.  I will post how we accomplished this at a later date.
• The LAST and MOST IMPORTANT thing we have to mention.  Using cookies means both servers MUST BE ON THE SAME DOMAIN.  This is a strict cookie and session management requirement set in stone many, many years ago. So if your EBS server is at abc.def.com and APEX is at ghi.jkl.com, this method will not work.  Both servers must be on the .def.com or both on the .jkl.com domain. However, that doesn't mean all is lost.  My next project is to... well that's for another post ;)

E-Business Suite and APEX Extending or Integrating?

Since returning from KScope14, I have been spending a great deal of time and effort examining any method of truly integrating E-Business Suite 12.2.3 (EBS) and APEX 4.2.5 (APEX). I have read the Oracle white paper, Extending Oracle E-Business Suite Release 12 using Oracle Application Express, and I realize this is the only supported method.  But, it feels incomplete to me...

Currently our users are logged in to APEX automatically when they choose an APEX app in EBS (version 11). From a usability standpoint I do not want my users to have to re-login to APEX after logging in to EBS (version 12).

In the following series of posts, I will post the steps we took and the issues we ran into while developing our solution.

Slide deck is pretty much done and uploaded to ODTUG.  I still have a few ideas in my head, just not sure how to lay them out in a slide.  If you aren't coming to Seattle, no worries! After KSCOPE, I will start uploading the deck in a series of posts...

Interestingly, while working on the EBS 12 test system I ran across the same issue everyone has when migrating my APEX integration pieces from 11.  The removal of mod pl/sql by Oracle ruins most of what everyone has done in 11.

So my next undertaking will be to figure out a way to migrate my 11 integration to 12 without changing too much in the way of code AND including an extra layer of security.  Preliminary tests show that while the current methodologies suggested to integrate 12 work and is "secure", I believe there might be a missing piece in making the integration more robust in terms of security.

Stay tuned...