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 in to 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 for 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 to your existing implementation.
Explorer have worked on several EBS-APEX integration projects where APEX has proven to be an excellent fit. We generally find the projects fit in to 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 in to 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
As the whitepaper contains detailed information on the architecture options, my aim was 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 in order to:
- Support the Oracle E-Business Suite patching and upgrading process.
- Provide a level of a 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.
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), granting access where necessary, creating the views and the 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
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 really 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 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, Explorer have created a custom authentication scheme which uses information from the active EBS session to seamlessly log users on to 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 provides the steps involved in creating a secure EBS Navigation Menu entry. These pages are valuable reference content and provide all the detail 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
Licensing the Oracle database for use with e-business suite apps is often done by restricting the licenses to “Limted Use”. This limited use is more than likely going to be refer to “Application Specific Full Use” or ASFU and is intended by Oracle to provide customers with additional discount on the commercial terms agreed but restricting 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 there are no custom objects or packages 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!
Once the supported environment is in place; APEX development is similar to non-EBS projects and provides huge benefits in terms or a clean UI, cost effectiveness and rapid application development.
As development continues; there’s a few ongoing checks to perform
- Does the custom code confirm to 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.
The PDFs of the above are available to download here.
I hope you find them as a useful, handy guide.
Please get in touch if you are thinking of extending EBS with APEX and we can discuss further.
Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of 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 practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.