Extending Oracle EBS in the Cloud with PaaS APEX & ORDS

​Introduction

A number of our clients have Oracle E-Business Suite (EBS) with some limited functionality available outside of their network. One of these clients challenged us with providing their employees with a more accessible, richer and more intuitive user experience than EBS alone can provide. In this post I will discuss an approach we employed recently to utilize Exadata Express to handle document of record acknowledgements in the Cloud whilst keeping data synchronized with EBS on-premise. This approach employs both APEX and ORDS on the Exadata Express SaaS platform.

Requirement Overview

The customer has EBS on-premise and uses the Human Resources module to manage their employees. They had a requirement for employees to review and acknowledge the receipt of certain key documents (e.g. Employee Handbook). The acknowledgements needed to be stored with the employee’s official HR record so that they can be referenced by the HR department.
 
The customer has a diverse set of employees including salaried as well as hourly workers. All employees have access to Oracle EBS but not all of them have a corporate (or even private) email address and even fewer have access to VPN. The initial notification asking an employee to acknowledge a new document had to be made via an Oracle Work list (through the Oracle Workflow module) notification as well as email (if available). All these factors made it important that we deliver an easy to access, easy to use and mobile friendly experience that is available outside of the firewall.

Approach

The approach we took was to utilize Document of Record standard functionality available in EBS to generate a document of record for each employee and document type. We developed a Concurrent Program to perform the following steps on a nightly basis:

1. Identify new employees that have not acknowledged certain documents of record.
2. Generate a document of record for the employee and document type (using Oracle Document of Record API).
3. Generate a unique token for each employee and document of record.
4. Call an ORDS REST service (hosted on Exadata Express) to POST the document of record to a table in Exadata Express.
5. Launch a workflow to notify the employee that there is a document he or she need to review and acknowledge. The notification contains a link to an APEX application in Exadata Express. The link includes the unique token created in step 3.

 
When the employee clicks the link, they are taken to the APEX App on Exadata Express, which validates the token and shows the employee the document that needs to be reviewed. As well as the document, the user is also presented with an Acknowledge button. Once clicked, the document is marked as acknowledged in a table in Exadata Express.
 
Some documents are displayed in-line in HTML format, while others are shown as an attached document, housed in an Amazon S3 bucket. This is configurable using an APEX administration App.
 
In addition to the End User Application, an Admin APEX App was created to maintain the text of each document type as well as links to the PDF documents on S3. This application allows an administrator to configure what the user sees for a particular document type. This includes the ability to have a variable number of sections for each document type and allow the document to be displayed in three different languages.
 
A second concurrent program was developed which performed the following steps:

1. Calls a 2nd ORDS REST service running on Exadata Express. This service fetches a list of documents that have been acknowledged since the last time the process ran.
2. For each record returned, update the related EBS document of record to indicate the date the document was accepted.

 
Regarding reporting, the HR department can report on which employees had accepted which document, directly in EBS. This is possible because we utilized standard Document of Record functionality in EBS.

​Security Considerations

1. All web service calls are made outbound from the on-premise EBS database to Exadata Express. No inbound traffic is allowed.
2. The table of document of record information stored in the Cloud is a cut down version of the table in EBS excluding any sensitive employee data.
3. The token used to identify the user is invalidated as soon as the employee acknowledges the document. In addition, each token is specific to a combination of employee and document of record.
4. We secured the Exadata Express ORDS REST services using OAUTH2 client credentials flow. This uses a known user name and password (stored in the on-premise database) to obtain a short-lived token. That token is then used to call the web services.

---

Architecture Diagram

​Benefits of the Approach

Some of the benefits of this approach include:

1. The load of thousands of employees viewing and acknowledging various different documents and PDF files is off-loaded from the main transactional ERP database to the Cloud.
2. The APEX application is easily accessible outside the firewall without exposing sensitive HR data sitting in the EBS database.
3. APEX provides for a much more user (and mobile) friendly platform for delivering documents to employees, many of whom use computers very infrequently.
4.  Using APEX, we can capture more details about the user’s interaction with the document. For example, we can determine which users viewed the document but did not acknowledge it.

Conclusion

​Hopefully, the approach described in this document gives you some ideas on how you can utilize Cloud Services such as Exadata Express to extend the reach of your on-premise Oracle EBS environment. This gives you the ability to provide your users modern scalable solutions that may not be possible with just on-premise EBS.

Author

Jon Dixon, Co-Founder of JMJ Cloud