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!