Oracle Supported EBS extensions with APEX – Simply explained

I recently reviewed the White Paper “Extending Oracle E-Business Suite Release 12 using Oracle APEX – Revision 3.02”.

This document came out in April 2019 and replaced a much older document on the same subject and introduces newer integration examples using APEX 18.2

In this blog, I will attempt to deconstruct the white paper into easily digestible parts with some chapters represented in diagrammatic form.

The 64-page white paper is packed full of excellent technical content. However, I felt producing a series of diagrams was a better way to visually communicate its details to a wider audience. This approach is much more approachable than flipping back and forth through the document.

Reading the first half of the document especially, there was a very thin line to tread in terms of toeing the line in regards to EBS & APEX co-existing on a supported environment… with the word ‘supported’ being mentioned 15 times. Therefore, it was of special importance to provide at least a visual index of steps to take.

Let’s start with some basics.

Why extend EBS with APEX

EBS (Oracle e-Business Suite) is an extensive software product, by Oracle, which delivers a wide range of functionality to handle core areas of business processing needs.

However, there are situations where a business may want to extend functionality beyond the range covered by EBS. These extensions may arise from a need to adapt to an industry change, a tactical opportunity or even a streamlining change request within the business.

These may range from a basic change to something quite extensive. In all these scenarios, Oracle Application Express (APEX) can provide the solution.

There are already many reasons why you would use APEX (even without EBS). However, APEX offers a quick and highly productive way to extend your Oracle E-Business Suite environment with almost no impact on your existing implementation.

DSP has worked on several EBS-APEX integration projects where APEX has proven to be an excellent fit. We generally find the projects fit into the following categories.

• Redevelopment of an existing EBS extension in APEX providing UI modernisations
• New functionality delivered using APEX utilising the Oracle EBS DB & EBS API
• Streaming a lengthy EBS process consisting of traversing several screens into a single APEX page (e.g. a Wizard)

What the white paper contains

The whitepaper covers

• Overview (similar to the blog so far)
• Supported EBS-APEX environments
• Upgrading ORDS & APEX within EBS
• Configuration within EBS to open an APEX application
• Obtaining EBS information, e.g. responsibilities
• Constructing an APEX app
• Calling EBS APIs

Supported Architecture

As the whitepaper contains detailed information on the architecture options, I aimed to provide a diagrammatic, high-level, flowchart of those options.

These flowcharts are intended to provide a high-level quick-reference overview and are intended to be read in conjunction with the white paper which, on occasion, references APEX/EBS official documentation.

The white paper stresses on serval occasions the importance of adherence to the deployment options to:

• Support the Oracle E-Business Suite patching and upgrading process.
• Provide a level of security whereby APEX…
• Can view EBS data-based views on EBS synonyms which use MOAC VPD
• Can only use EBS functionality including EBS APIs which are specifically granted.
• To be supported by Oracle

Oracle always advises using the latest versions of APEX & ORDS although minimum versions are provided if there is a particular reason to use an older version.

ORDS is a highly recommended component of APEX. Its deployment, either standalone or within a web container, on the same HTTP applications server as EBS is dependent on the version of EBS.

EBS Upgrade

If you have EBS 12.1 and have never used APEX, then chances are that you have a dormant version 4.0 of APEX already installed in your DB. Whether you are actively using APEX or not dictates the configuration you are advised to follow.

Supported Schema Usage

Here you have two options; deploy APEX in the same DB as EBS (recommended) or in to a separate environment.

In Explorer’s experience, the Oracle recommended configuration is the easiest and pain-free option to develop EBS extensions with APEX.

It’s imperative to create the correct schemas (see diagrams below), grant access where necessary, create the views instead of triggers, etc., etc.

The flowcharts below serve as a reference to describe what goes where when designing the schema

Recommended Database Architecture

Alternative Database Architecture

Single Sign-on

For me, this is important – it’s all very well establishing the Environment required, however, if the first thing the users see when clicking through the EBS Navigation Menu is an APEX login page… well, this is frustrating. The user should ideally be automatically navigated to the correct APEX page they are trying to access whilst being fully authenticated and authorized – this is called ‘seamless integration’.

The white paper describes that seamless integration can be achieved by the use of Oracle Access Manager (OAM) or Oracle Single Sign On (OSSO). It goes on to say “If using custom authentication, users will not automatically log into Oracle APEX”.

Despite this, DSP has created a custom authentication scheme which uses information from the active EBS session to seamlessly log users onto APEX, bypassing the APEX log-in page. Due to functionality in recent versions of APEX to be able to switch authentication schemes in-session, this means that Explorer EBS-APEX applications can dual-support i. EBS seamless integration or ii. standalone authentication e.g. an alternate custom authentication (should an EBS session not be detectable, i.e. the user is not logged in to EBS).

EBS Menu Configuration

Pages 47-54 provide the steps involved in creating a secure EBS Navigation Menu entry. These pages are valuable reference content and provide all the details necessary to configure EBS to call an APEX URL.

• Page 47: Defining Oracle E-Business Suite Profile and Form Functions
• Page 50: Defining Oracle E-Business Suite Menus and Responsibilities
• Page 54: Complete the configuration

EBS Licensing

Licensing the Oracle database for use with e-business suite apps is often done by restricting the licenses to “Limited Use”. This limited use is more than likely going to be referred to as “Application Specific Full Use” or ASFU and is intended by Oracle to provide customers with additional discounts on the commercial terms agreed upon but restrict the software usage rights at the same time.  This means that any customer who has purchased Oracle e-business suite applications over the years has done so on the understanding that they cannot make custom changes to the application or underlying database schema. Other customers have purchased “Full Use” licenses, which means there are no restrictions on using the Oracle database for any other purpose underpinning EBS. Any organisation that is looking to use APEX to create custom apps or objects within the same database schema or in a new schema running on the same database must have “Full Use” license terms. If you don’t, you run the risk of being non-compliant and faced with a painful audit experience with LMS! It is possible to convert ASFU licenses to Full Use but this will come at a cost too. If you create custom apps on a completely separate database, which is appropriately licensed for the use case, and as long as no custom objects or packages are running on the EBS database then there will not be any license compliance issues. However, you are then restricted with functionality and single sign-on capabilities so customers have to balance the benefits and costs appropriately. For further guidance and advice, we can help. Simply get in touch!

Summary

Once the supported environment is in place; APEX development is similar to non-EBS projects and provides huge benefits in terms of a clean UI, cost-effectiveness and rapid application development.

As development continues; there are a few ongoing checks to perform

• Does the custom code confirm doc 1577707.1 chapter 4?
• You are on R12.2 and your custom tables are EBR enabled
• Grants from APPS should be for SELECT and never for direct manipulation.

Please get in touch if you are thinking of extending EBS with APEX and we can discuss further.

Author: Matt Mulvaney

Job Title: Senior Oracle APEX Development Consultant

Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience with Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practices, techniques & approaches. Matt has a passion for excellence and enjoys producing high-quality software solutions which provide a real business benefit.