Data entry challenges concerning ERP Cloud customers are experienced in all ERP systems. Organizations can benefit from powerful spreadsheet-based integration options when demands overwhelm the snail’s pace of keyboard and mouse operated forms.
Oracle ERP Cloud is “Open for Integration” with an extensive suite of public-facing web services. Having started with SOAP, Oracle has now committed to REST. With every release, we are seeing new REST web services added to the list.
As an Oracle-focused ISV with a 20-year history of integrating with E-Business Suite (EBS), More4apps has been following Oracle closely, and has been busy building a new suite of Excel based tools to supplement the few ADFdi integrators provided with Oracle ERP Cloud. For this blog we thought we’d give you the benefit of our experience coming to grips with Oracle’s adopted method of integrating with the ERP system.
Firstly, it’s important to recognize it is not completely straight-forward. With new opportunities come new challenges.
Access and security
The great thing about web services is they are so accessible! If you have the URL, you can call the web services. It’s up to Oracle to provide the security model to control functionality and data access. But this is where it can get tricky. REST web services require applicable privileges assigned to the user running the service. Oracle’s documentation often does not fully specify the required privileges. Determining the correct privileges consumes significant time and effort. We expect this to improve over time.
REST services don’t always respect data security. Information sensitive to users might not be restricted. For example, all projects will be returned from a web service GET call despite the user running the web service only having access to a limited set of projects. Also, a second look at a GET web service might reveal you can’t filter the call by the parameters you want. For example, it’s nice to see a web service to return Class Codes for the PPM module, but we found you can’t filter it by the Class Category, which is the single most important feature you’d expect in this web service.
All is not lost though, there is a Plan B. For now we will create our own BI report.
Web services are notorious for “timing out.” This is due to the default 300 second (5 minute) wait period before a web server gives up waiting for a response from the server. You can use nested REST web services to create a single large payload, but if you have to wait more than 5 minutes for the response, you’ll get nothing back! When a timeout occurs, data still might be committed, but no response is provided as to whether or not any errors occur.
Luckily there are ways to work around this issue. Oracle supports single and “batched” REST calls, so you can keep payloads small enough to avoid timeouts.
Enter some more gotchas…
A call to a web service for a single record requires its own round-trip to and from the server. That overhead is acceptable for a small number of records, but if you have a user waiting for 3,000 tasks to be uploaded...
So - let’s try batching. This is a way to accumulate multiple calls to web services in a single payload, nicely removing a lot of communication overhead. When using batching, it’s essential to know which record(s) fail. Some error messages are unhelpful, misleading or non-existent. If Oracle does not tell you which records, this method is a non-starter. Currently, Oracle will also reject an entire batch if a single rejection occurs.
We have to add lots of validation up-front so we can provide a quality user experience. Oracle will need to look at improving consistency in this area.
Web service row limitations
“Get All” REST web services only return 500 records at a time. Multiple round trips must be made to retrieve all data where 500 or more records exist. Of course there is a solution, but you must be mindful of these constraints.
Documentation doesn’t always match the web service definition. In one example, the Line Number for the Patch Invoice Line web service is not the Invoice Line Number, but a unique REST identifier that can only be retrieved by calling the Get All Invoice Lines service. On the plus side, quarterly releases are now accompanied by the 'Cloud Readiness' documentation, highlighting upcoming enhancements and new functionality along with details on fixes.
We’ve had to put the brakes on in certain areas and wait for Oracle development to catch-up. For example, (as of 20D) if you want to load AR Invoices or AR Receipts using the REST web services, you’ll find they’re far from complete. Currently you can use SOAP for receipts, but going forward Oracle will want to deprecate this method.
For Blanket Purchase Agreements and GL Journals, there are no web services at all, and only the CSV file loader is currently available.
The good news is Oracle is constantly improving Cloud ERP, and your votes for features etc. on the Ideas Lab help Oracle prioritize what needs to be built or improved.
This leads on to perhaps the most important thing to consider, Oracle’s mandatory quarterly upgrades. You already have to go through an extensive testing process for the application. You’ll need to include your own custom integrations. Where possible, it might make sense to avoid building when you can buy/subscribe, and let someone else, like More4apps take this job on for you.
Bruce Doig is one of the founders of More4apps. After 20 years of involvement in product development, he is now Head of Innovation. He still likes to keep a hand in the action and finds himself helping out with development of More4apps's new Projects tools for Oracle ERP Cloud as well as helping support the E-Business Suite toolbox.