In our prior post, Oracle ERP Conversions Made Easy with APEX Part 1, we showed you how Oracle APEX can be used to streamline and improve data conversion to Oracle EBS.
This blog entry continues the theme, showing you how the same principles and techniques can be applied in the Cloud world when converting data to ERP Cloud.
Part 3 demonstrates how APEX is the perfect fit to make your conversions simpler, faster, and repeatable.
We’ll share some real-world case studies to show you what can be achieved, including a preview of the pre-built conversions built into our latest APEX cloud software - JMJ ERP Cloud Express.
ERP Cloud Conversion Tools
Some are spreadsheet-driven and geared towards the end-user, others are more technical in nature and require specific software to drive them.
ADF Data Integrator - ADFDi
However, they struggle with larger data volumes, don’t work on MacOS, can be fiddly to use (for example the undo button doesn’t work and you often have to start again if you make a mistake), and Excel template downloads have to be periodically refreshed when Oracle carries out quarterly upgrades.
They also require a local install of the ADFDi framework into Excel - that can be tricky to deliver in some corporate environments and again requires re-installation with each quarterly release. Finally, they’re only available for a limited list of business objects. You won’t be loading customers into ERP Cloud using ADFDi.
File-Based Data Integrator - FBDi
Once data has been entered, an Excel macro is kicked off using a button on the first tab to build a zip file. This contains the data in CSV format. From a technical perspective, you can create the CSV file directly and zip it up yourself, but there are a number of things to look out for - the order of fields in the spreadsheet doesn’t always match the CSV generated by the macro, and the CSV filenames must remain consistent to the Oracle-generated names for some of the loads.
Having got this far, and assuming you have executed due diligence to make sure values are valid (for example by using VLOOKUP), the upload process consists of running Enterprise Scheduler Service (ESS) jobs in Oracle to transfer and load the file. The number of jobs and submission process may vary depending on the business objects, but a typical flow is to:
- Run Load Interface File for Import to stage your file into an Open Interface table, e.g. GL_INTERFACE. Prior to job submission, your source file is staged onto Oracle’s Universal Content Management server (UCM) and the job kicks off subprocesses to transfer this to the Oracle database and run SQL*Loader.
- Optionally, run a second Import process to validate and import the data into the final destination tables, e.g.
- Journal Import (GL Journals)
- Import Costs (PPM Expenditure Items)
- Import Invoices (AP Invoices)
The second process usually provides an output report listing data loaded along with any errors. This should always be reviewed, and issues may require resolution. This can mean going back to the beginning and checking your source spreadsheet, making corrections and repeating the upload process.
End to end, this process can be tricky and time consuming to get right. For the best results, a user has to be rigorous in Excel to ensure the data is correct before loading.
The list of FBDi templates is found here:
FBDi Automation Web Services
- Load files to the Universal Content Management Server (UCM)
- Submit ESS Jobs (e.g. Load Interface File for Import and Import Journals)
- Check ESS Job Statuses
- Download and verify logs and output reports
It can be cumbersome to write a full integration flow, calling each web service in turn and checking statuses, and Oracle helps us here by providing a bulk import web service; a single web service call transfers the file and runs all the required import processes in the correct order. The user can opt to receive an email notification when the job is complete.
The ERPIntegrationService web services are documented here:
Business Object SOAP Web Services
The documentation can be cryptic and working out which fields are required/not required or are updateable can take a bit of time and experimentation. We suggest using the “retrieve” or “get” method to inspect the fields on an existing transaction - this is often a good guide as to what is required when creating a new transaction.
Some of these web services will only accept one transaction at a time; others allow multiple transactions to be sent in a single call, with errors returned for each record.
Business Object REST Web Services
It is wise to check that the REST web service is appropriate for your use case before you start; many still only allow a GET operation, with PUT, POST or PATCH hopefully available in future releases.
We recommend verifying that a REST service includes all the fields you’d like to populate or update; the HCM employee service did not include the Default Expense Account field when it was first introduced, which is not good for Financials implementations. That omission has now been rectified but the web service is still marked as “Controlled Release", meaning that an SR must be raised to enable it, and your use case must be approved by Oracle.
HCM Data Loader (HDL)
HDL expects text files in pipe-separated format (PSV). The sample below for loading jobs shows the structure, with METADATA lines describing the data fields being loaded, followed by one or more data rows.
It is important to pay attention to SourceKeys – they’re crucial for updates to records after they have first been loaded.
Tracking/tracing issues can be problematic without writing custom reports; the Data Exchange allows you to drill through each file to see error rows but isn’t great at summarizing error messages across a large file. The error messages can be cryptic and require some experience to read.
This works well for smaller files. For larger data sets, you need to be aware that a connection or download may time out after a couple of minutes. You have several approaches available to you to deal with this.
- One option is to run the SQL in batches, returning data using multiple sequential web service calls and aggregating it on arrival.
- Alternatively, you can specify that a report’s output be delivered to Oracle’s Universal Content Management server (UCM). Once the report is complete, UCM web services can be invoked to download the zip file.
The UCM web services are documented in this My Oracle Support note:
- How To Get Files from UCM for Fusion Applications using the GenericSoapPort Web Service (Doc ID 2384418.1)
BI Cloud Connector (BICC)
These are extracts that can be scheduled as required to export data for various Business Objects. The resulting files can be accessed using UCM web services or links to Oracle Cloud Storage.
Cloud Conversion Challenges
- Manual Excel manipulation is slow and subject to errors. Users can make mistakes, add trailing spaces, save files with inconsistent date formats and leave mandatory fields blank. Formulas can be out of sync and Excel lookups can fail. Simply opening and closing a CSV file can corrupt the formats of dates and numbers within the file.
- Excel files can be hard to secure and protect. Excel passwords can be cracked online, and files are often stored un-encrypted on local computers. Data security is a wider topic than this blog – however, always remember to treat sensitive data (e.g. PII personal information) with due respect and caution.
- Excel files saved locally can be hard to work on collaboratively. Sharepoint/Box/DropBox and other cloud tools provide an alternative but are often left unused and spreadsheets fly back and forth via email.
- How do I quickly and reliably map and default extra fields into my data? I can use VLOOKUP (or INDEX/MATCH) in Excel – but how do I know the values are right and that all data has looked up correctly?
- How do I know if the data is correct and complete before I load it? How do I check it is still correct when I send it to a different environment that may have different setup values?
- How do I check data has been fully imported once it has been sent to Oracle? How can I provide a reconciliation report back to my users comparing what they sent for load versus what made it into ERP Cloud?
- I have no middleware available - how can I can automate my web service calls?
- Why does it take so long to run the data into Oracle? Why are there so many processes to run? How can I reduce the time taken for each conversion cycle and reduce the potential for user error?
To Be Continued ...
In Part 3, we will show how APEX can vastly simplify the data migration process, and we’ll share some real-world case studies to show you what can be achieved.
This will include a preview of the pre-built conversions and tools built into our latest APEX cloud software - JMJ ERP Cloud Express.