Integrating Oracle EBS with Oracle Apex

Introduction / Issue

Organizations often develop custom applications in Oracle APEX to extend the functionality of Oracle E-Business Suite (EBS). However, one common challenge is integrating Oracle APEX with Oracle EBS in a way that provides a seamless user experience. Users expect to navigate between Oracle EBS and Oracle APEX without switching screens, entering different URLs, or facing authentication and accessibility issues. Another key requirement is to allow users to access Oracle APEX using their existing Oracle EBS login session, eliminating the need for a separate Oracle APEX or public user login.

To achieve this, Oracle APEX needs to be integrated with the existing Oracle EBS environment so that both applications are accessible using the same hostname. This creates a unified application environment, improves usability, simplifies system administration, and provides a seamless single sign-on experience between Oracle EBS and Oracle APEX.

Why We Need to Do This / Cause of the Issue

Oracle APEX is commonly deployed using Oracle REST Data Services (ORDS), while Oracle EBS is accessed through Oracle HTTP Server (OHS). If both applications are configured independently, users may need to access different URLs or hostnames to use Oracle APEX applications.

This separation introduces several operational and administrative challenges, including:

• Multiple URLs for users to manage.
• Lack of seamless navigation between Oracle EBS and Oracle APEX.
• Additional configuration effort for system administrators.
• Increased complexity in maintaining separate application endpoints.
• Difficulty providing a unified experience for business users.

From an administration perspective, maintaining separate configurations can increase the effort required for deployment, monitoring, and future enhancements. Integrating Oracle APEX with Oracle EBS under the same hostname simplifies the overall architecture and provides a more consistent user experience.

How Do We Solve It?

The implementation involves creating the required database objects, configuring the necessary Oracle APEX application attributes and application items, compiling the PL/SQL integration package in the Oracle EBS APPS schema, and validating the session information to ensure secure interaction between Oracle APEX and Oracle EBS.

Once the configuration is completed, users can navigate between Oracle EBS and Oracle APEX while maintaining the Oracle EBS application context.

The detailed implementation steps are described below.

Step 1 – Creating an Oracle APEX Application and Application Items

Create an Oracle APEX application. After creating the application, navigate to
Shared Components → Application Items and create the following application items.

Use the following settings for each application item:

• Scope: Application
• Session State Protection: Restricted – May not be set from browser

Step 2 – Create the Log Table, Sequence, and Compile the PL/SQL Package

Create the required log table and sequence in the Oracle EBS APPS schema to capture integration logs, processing status, and error details during Oracle APEX transactions. After creating the database objects, compile the PL/SQL package in the APPS schema to enable communication between Oracle APEX and Oracle EBS.

Note: The log table and sequence are optional for the initial integration setup. They are primarily intended for debugging, auditing, and troubleshooting by recording the parameter values retrieved and processed during the Oracle APEX and Oracle EBS integration flow.

Log Table

CREATE TABLE xx_apex_ebs_debug_log (
    log_id                NUMBER,
    log_date              DATE,
    apex_session_id       NUMBER,
    db_user               VARCHAR2(100),
    app_user              VARCHAR2(255),
    app_id                VARCHAR2(100),
    app_page_id           VARCHAR2(100),
    ebs_user_id           NUMBER,
    responsibility_id     NUMBER,
    resp_application_id   NUMBER,
    security_group_id     NUMBER,
    org_id                NUMBER,
    timeout_minutes       NUMBER,
    ebs_url               VARCHAR2(500),
    application_name      VARCHAR2(240),
    ip_address            VARCHAR2(255),
    host_name             VARCHAR2(255),
    session_cookie        VARCHAR2(500),
    status                VARCHAR2(30),
    error_message         VARCHAR2(4000)
);

Custom Sequence

CREATE SEQUENCE xx_apex_ebs_debug_log_s
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

Package Specification

CREATE OR REPLACE PACKAGE xx_apex_debug_pkg
AS
   FUNCTION check_ebs_credentials
      RETURN BOOLEAN;
END xx_apex_debug_pkg;

Package Body

Compile the following package body in the Oracle EBS APPS schema. The package validates the Oracle EBS session, retrieves the Oracle EBS user and responsibility information, initializes the Oracle APEX session context, and records the integration status in the debug log table.

The package performs the following functions:

• Retrieves the active Oracle EBS session using ICX_SESSIONS.
• Validates whether the Oracle APEX request originates from a valid Oracle EBS session.
• Retrieves the Oracle EBS User, Responsibility, Responsibility Application, Security Group, and Operating Unit.
• Sets the required Oracle APEX Application Items using APEX_UTIL.SET_SESSION_STATE.
• Sets the Oracle APEX authenticated user using APEX_CUSTOM_AUTH.SET_USER.
• Synchronizes the Oracle APEX session timeout with the Oracle EBS session timeout.
• Logs successful, failed, and exception scenarios into the debug log table for auditing and troubleshooting.

CREATE OR REPLACE PACKAGE BODY XX_APEX_DEBUG_PKG
AS
   FUNCTION check_ebs_credentials
      RETURN BOOLEAN
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;

      c_ebs               VARCHAR2 (240) := 'E-Business Suite';

      l_authorized        BOOLEAN;
      l_user_id           NUMBER;
      l_resp_id           NUMBER;
      l_resp_appl_id      NUMBER;
      l_sec_group_id      NUMBER;
      l_org_id            NUMBER;
      l_time_out          NUMBER;
      l_ebs_url           VARCHAR2 (100);
      l_appl_name         VARCHAR2 (240);
      l_session_cookie    VARCHAR2 (500);
      l_error_message     VARCHAR2 (4000);
      l_user_name         VARCHAR2 (500);
      l_app_user          VARCHAR2 (255);
      l_app_id            VARCHAR2 (100);
      l_app_page_id       VARCHAR2 (100);
      l_ip_address        VARCHAR2 (255);
      l_host_name         VARCHAR2 (255);

      CURSOR get_apps_credentials
      IS
         SELECT iss.user_id,
                iss.responsibility_id,
                iss.responsibility_application_id,
                iss.security_group_id,
                iss.org_id,
                iss.time_out,
                isa.VALUE
           FROM apps.icx_sessions iss,
                apps.icx_session_attributes isa
          WHERE iss.session_id = apps.icx_sec.getsessioncookie
            AND isa.session_id = iss.session_id
            AND isa.NAME = '_USERORSSWAPORTALURL';

      CURSOR get_appl_name (b_appl_id NUMBER)
      IS
         SELECT application_name
           FROM apps.fnd_application_tl
          WHERE application_id = b_appl_id
            AND LANGUAGE = USERENV ('LANG');

   BEGIN

      l_session_cookie := apps.icx_sec.getsessioncookie;
      l_app_user       := v('APP_USER');
      l_app_id         := v('APP_ID');
      l_app_page_id    := v('APP_PAGE_ID');
      l_ip_address     := SYS_CONTEXT('USERENV','IP_ADDRESS');
      l_host_name      := SYS_CONTEXT('USERENV','HOST');

      OPEN get_apps_credentials;

      FETCH get_apps_credentials
       INTO l_user_id,
            l_resp_id,
            l_resp_appl_id,
            l_sec_group_id,
            l_org_id,
            l_time_out,
            l_ebs_url;

      IF get_apps_credentials%NOTFOUND THEN

         l_authorized := FALSE;

         INSERT INTO xx_apex_ebs_debug_log (...)
         VALUES (...);

         COMMIT;

      ELSE

         l_authorized := TRUE;

         OPEN get_appl_name(l_resp_appl_id);
         FETCH get_appl_name INTO l_appl_name;

         IF get_appl_name%NOTFOUND THEN
            l_appl_name := c_ebs;
         END IF;

         CLOSE get_appl_name;

         SELECT user_name
           INTO l_user_name
           FROM apps.fnd_user
          WHERE user_id = l_user_id;

         APEX_UTIL.SET_SESSION_STATE('EBS_USER_ID',TO_CHAR(l_user_id));

         APEX_CUSTOM_AUTH.SET_USER
         (
             p_user => l_user_name
         );

         APEX_UTIL.SET_SESSION_STATE('EBS_RESP_ID',TO_CHAR(l_resp_id));
         APEX_UTIL.SET_SESSION_STATE('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
         APEX_UTIL.SET_SESSION_STATE('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
         APEX_UTIL.SET_SESSION_STATE('EBS_ORG_ID',TO_CHAR(l_org_id));
         APEX_UTIL.SET_SESSION_STATE('EBS_URL',l_ebs_url);
         APEX_UTIL.SET_SESSION_STATE('EBS_APPLICATION_NAME',l_appl_name);

         APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS
         (
             l_time_out * 60,
             'APPLICATION'
         );

         INSERT INTO xx_apex_ebs_debug_log (...)
         VALUES (...);

         COMMIT;

      END IF;

      CLOSE get_apps_credentials;

      RETURN l_authorized;

   EXCEPTION

      WHEN OTHERS THEN

         l_error_message := SUBSTR(SQLERRM,1,4000);

         INSERT INTO xx_apex_ebs_debug_log (...)
         VALUES (...);

         COMMIT;

         IF get_apps_credentials%ISOPEN THEN
            CLOSE get_apps_credentials;
         END IF;

         RETURN FALSE;

   END check_ebs_credentials;

END xx_apex_debug_pkg;

Note: The complete package body is provided below and should be compiled without modification. The abbreviated INSERT INTO ... VALUES (...) statements shown above are used only for documentation purposes to improve readability. Replace this section with the full package body when implementing the solution.

Step 3 – Grant the Required Access to the Oracle APEX Schema

Grant the necessary privileges from the Oracle EBS APPS schema to the Oracle APEX parsing schema. These grants allow the Oracle APEX application to execute the custom integration package and access the required Oracle EBS objects during runtime.

The Oracle APEX schema should have, at a minimum, the following permissions:

• EXECUTE privilege on the custom integration package (XX_APEX_DEBUG_PKG).
• SELECT privileges on any required custom tables or views.
• Any additional privileges required by the application.

Compile and verify that all grants are applied successfully before proceeding with the Oracle APEX configuration.

Step 4 – Create a Custom Authorization Scheme for Valid Oracle EBS Login Checks

Navigate to Shared Components → Security → Authorization Schemes and create a new authorization scheme with the following configuration.

BEGIN
   RETURN apps.xx_apex_debug_pkg.check_ebs_credentials;
END;

Save the authorization scheme.

Step 5 – Create a Custom Authentication Scheme

Navigate to Shared Components → Security → Authentication Schemes and create a new authentication scheme from scratch.

Save the authentication scheme.

Step 6 – Update the Oracle APEX Security Attributes

Navigate to Shared Components → Security → Security Attributes and then open the Security tab next to the Definition tab.

Update the following settings:

Save the changes.

Step 7 – Update the Oracle EBS Profile Option

Log in to Oracle E-Business Suite using the SYSADMIN user or any user with the System Administrator responsibility.

Navigate to:

System Administrator → Profile → System

Search for the profile option %APEX% and click Find.

For the profile option FND: APEX URL, enter the following Site value:

https://<hostname>:8080/apex

Save the changes.

Step 8 – Create the Custom Function and Menu for Accessing Oracle APEX Pages

8.1 Create the Function

Log in to Oracle E-Business Suite using the SYSADMIN user or any user with the System Administrator responsibility.

Navigate to:

System Administrator → Application → Function

Description Tab

Properties Tab

Web HTML Tab

Enter the following HTML Call:

GWY.jsp?targetAppType=APEX&p=<APEX_Application_ID>:<APEX_Page>:<Session>:<Request>:<Debug>:<Clear_Cache>:<Parameter_Pairs>

Example:

GWY.jsp?targetAppType=APEX&p=109:9

All remaining parameters are optional.

8.2 Create the Menu

Navigate to:

System Administrator → Application → Menu

Create a new menu using the following values:

Save the menu and attach it to the required Oracle EBS responsibility or an existing menu. Users assigned to the responsibility will then be able to launch the Oracle APEX application directly from Oracle E-Business Suite while maintaining their existing Oracle EBS session.

Benefits of the Solution

Integrating Oracle APEX with Oracle E-Business Suite provides a unified platform that enhances usability, simplifies administration, and enables organizations to extend Oracle EBS functionality without disrupting the existing user experience.

The solution offers the following key benefits:

• Unified access to Oracle E-Business Suite and Oracle APEX using a single hostname.
• Improved user experience through seamless navigation between Oracle EBS and Oracle APEX.
• Single Sign-On (SSO) experience by utilizing the existing Oracle EBS user session.
• Simplified system administration and centralized configuration management.
• Reduced maintenance effort by leveraging the existing Oracle EBS infrastructure.
• Faster deployment of future Oracle APEX applications integrated with Oracle EBS.
• Improved scalability for extending Oracle EBS functionality using Oracle APEX.
• Standardized application architecture across the Oracle EBS environment.
• Enhanced security by validating Oracle EBS sessions before granting access to Oracle APEX applications.

Conclusion

Integrating Oracle APEX with Oracle E-Business Suite using the same hostname creates a unified, secure, and scalable application environment. Rather than maintaining separate entry points for Oracle EBS and Oracle APEX, this approach enables both applications to operate seamlessly within the same infrastructure, allowing users to navigate between them without additional authentication or multiple URLs.

The integration leverages the existing Oracle EBS session to authenticate users, preserve the Oracle EBS application context, and provide a consistent Single Sign-On (SSO) experience. At the same time, it simplifies system administration by reducing configuration overhead and enabling centralized management of application access.

By following the implementation approach described in this guide, organizations can successfully extend Oracle E-Business Suite functionality with Oracle APEX while maintaining a standardized application architecture, improving operational efficiency, and delivering a seamless user experience for business users.