JMJ CLOUD
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us

Our Blog

Extending Oracle EBS in the Cloud with PaaS APEX & ORDS

8/16/2018

1 Comment

 
Oracle EBS and Exadata Express

​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

Oracle SecurityPicture
  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

Picture

​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

1 Comment

Extending ERP Cloud with PaaS - APEX Data Synchronization

8/4/2018

0 Comments

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

Picture

Best Practise Data Synchronization

Here’s some thoughts on how to do this successfully in the real world:

​
  • You use the CLOUD_SCHEDULER package to schedule database jobs to pull data down from ERP Cloud.   A restriction in a cloud environment is that you have a limit of 5 simultaneous jobs - so if you want to run 10 different jobs on different schedules a different approach is needed.   Our solution was to use a single job that runs every 15 minutes.   Each time it runs, it then checks to see what synchronizations are required and executes each in turn.
  • Calls to any web service over the internet must be fault-tolerant.  You should expect both intermittent connection errors and planned outages to result in a web service call failing.  A retry strategy helps mitigate shorter outages, for example calling a web service three times before raising an error.  Longer planned downtime such as ERP Cloud patching should be anticipated, and the system needs to pick up again once the downtime is over.
  • Each web service call will result in data being passed over your internet connection from one database to the other.   A key goal is to minimize the size of the data returned.  Specifically:
    • Return only the data you need.  That applies both to rows of data, or individual fields contained on each row.  A "SELECT * FROM" strategy is not a good one to choose here!
    • If the web service you are calling allows it, return data in zipped format.
    • Flat, non hierarchical data such as a list of GL accounts can be retrieved using a simple CSV format without headers.   For structured or hierarchical data (e.g. a project and its tasks), an XML format may be more appropriate but the length of each tag name should be kept short, e.g. 3 characters.
  • There is a risk with larger data sets that a BI Publisher Report will time-out before returning data.  A process that successfully synchronizes 50 projects may start to error when another 20000 projects are created.   We built our extracts to retrieve projects in batches of 500 and this has been found to be very reliable.
  • Each of the synchronization jobs needs to be fully configurable.   You’ll want the ability to schedule jobs at different times and disable/enable them.  The parameters for each job should also be changeable, such as the number of days to refresh or the number of rows returned in each batch.  We built a PaaS Administration application, using the APEX framework that comes the cloud database, giving us the ability to control these behaviours without code changes or table updates.
  • All web service calls should be logged and a robust, debug framework should be put in place to make support simple and issues easy to troubleshoot.   Again, an administration application built using Oracle APEX comes in handy here.

Once you have the data synchronizing you can start to have fun extending Oracle Cloud! 

Look What We Built!

​We recently developed a screen in ERP Cloud to allow users to upload transactions from Concur.    The starting point is the Standard Accounting Extract (SAE) that is downloaded from Concur - this is mapped, validated and the costs sent across to Oracle GL and Oracle PPM.   The data validation process depends on project, worker and application setup values being synchronized from Oracle using the BI publisher process discussed above.


A PaaS form embedded within ERP Cloud makes the upload process simple and quick - and once complete, a Tieback step runs to make sure that the records sent to Oracle actually made it all the way in and didn't get stuck for any reason. 

​As the SAE is a generic format produced by Concur, this solution will scale well for other ERP Cloud Customers.
Picture

If you want some other ideas of what you could do - have a look at our earlier blog here:   ERP Cloud Extensions using APEX.

Author

Matt Paine, Co-Founder JMJ Cloud

0 Comments

    RSS Feed

    Popular Posts

    - APEX Dog Food
    - Cloud ERP & APEX Mashup
    - Modernizing EBS with APEX
    - Easy APEX_WEB_SERVICE
    - Running APEX in RDS
    - ORDS What & Why?

    Categories

    All
    APEX
    AWS
    Fusion Cloud ERP
    INTEGRATION
    MS GRAPH
    OCI
    ORDS
    PaaS
    RAD
    REST
    SOAP

    Archives

    February 2021
    January 2021
    October 2020
    September 2020
    June 2020
    May 2020
    April 2020
    February 2020
    January 2020
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016

Company

About
Contact
Blog
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us