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.

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.

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.

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.

​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.

We've posted before on how ERP Cloud can be extended using one of Oracle’s Cloud database offerings, e.g. Exadata Express.  We can embed custom APEX pages into ERP Cloud to add client specific functionality, for instance adding a page to streamline the mapping and upload of Concur costs into Oracle GL and PPM.  


​If you want to read more on this subject, please have a look at this prior blog entry:  Oracle APEX as a Cloud ERP PaaS Solution.

A challenge to overcome is that your cloud database can’t directly access the tables in the ERP Cloud database.   This means that you're unable to run SQL on tables such as PER_USERS or GL_LEDGERS.  

Choosing Your Synchronization Strategy

So if you want to be working with employees, roles, projects or any other ERP Cloud information, then you will need a strategy for pulling this data down from Cloud.  Here’s a few things to think about:
​

• Does the information need to be stored in your database?   Information such as GL period statuses could be retrieved on the fly by a web service call and not stored in a custom table at all.
• When and how should data be synchronized?   Does your data synchronization need to be close to realtime, or is a daily or weekly feed sufficient?
• Should APEX receive new transactions, be refreshed in its entirety, or refreshed for a given timeframe, e.g. the last month or week?   We have found refresh strategies can be more fault tolerant than incremental flows and self-healing if there is an outage.   The downside of this approach is that you tend to end up sending a larger volume of data each time the integration runs.
• Will you push or pull the data from ERP Cloud?.   If the data set is vast or the extract process is slow, you’d be better off scheduling a BI Report to the Universal Content Management (UCM) server that comes with Oracle.   APEX can then use the UCM web services pull down the data directly, or if middleware is available you can write an orchestration process to retrieve the data, zip it up and post it into an APEX REST web service.
• If pulling data, how will you achieve this?  You could upload slowly changing or monthly data manually, but automated strategies are usually required, such as using APEX_WEB_SERVICE or APEX_EXEC to call custom BI reports or product specific web services.   Our blog on How Version 18.1 will help APEX win the Cloud has good detail on how to use these.
• What user will you use to connect to Oracle to call the web service to retrieve the data?  What roles and data access do they need?  
• What is your data retention strategy?   Space in a cloud database has to be managed carefully to make sure that you are well within your allocated quota.    Older data can be purged or archived across to a compressed table.

At JMJ we've used custom BI reports to do the synchronization - this gives us the most flexibility to get the data we need.   We have used this strategy in a Production environment for over a year and have found it to be reliable and scalable.