 | 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 2 will walk through the tools available in ERP Cloud and highlights some of the challenges in using them.
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.
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
Let’s start by reviewing the tools available to us in ERP Cloud for getting data in and out of the database. There’s a long list (and a lot of acronyms to work with).
Some are spreadsheet-driven and geared towards the end-user, others are more technical in nature and require specific software to drive them.
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
ADF Data Integrator (ADFDi) spreadsheets are similar to the GLDi spreadsheets used with Oracle EBS – preconfigured and heavily coded spreadsheets that you populate with your data. Common uses are for end-users to load GL journals or AP invoices.
ADFDi for importing GL Journals
ADFDi for importing AP Invoices
These have some advantages for loading data: they pre-validate data before loading, and typically have built in dropdowns allowing users to review and select valid values for fields such as GL Accounts or Supplier Names.
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.
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
File Based Data Integrator (FBDi) spreadsheets are downloaded from Oracle’s online cloud documentation pages and consist of simple worksheets with one or more tabs, for example Assets with Asset Distributions, or Customers with Customer Contacts.
Data is entered into the spreadsheet tabs manually but there is no live validation; comments in each column header give guidance on how to fill each field but as there are many fields in each sheet, this can be challenging for an end user to work with. Most fields are optional; others are conditionally required which can cause confusion.
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:
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:
https://docs.oracle.com/en/cloud/saas/financials/20b/oefbf/index.html
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:
https://docs.oracle.com/en/cloud/saas/financials/20b/oefbf/index.html
FBDi Automation Web Services
FBDi lends itself to easy automation via Oracle's SOAP and REST web services. Clearly this is not something you’d expect an end-user to do. If you have Oracle APEX or some other form of middleware, you can use the ERPIntegrationService web service to:
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:
- 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
Oracle provides a series of SOAP web services linked to business objects such as Suppliers and Purchase Orders. These are documented online for each release, so for 20B that is located here.
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.
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.
Sample PO Change Order Payload
Bear in mind that most of the SOAP web services are older and slowly being replaced by REST. Oracle has been known to retire or de-support SOAP web services. The Supplier and Person web services have both been deprecated or replaced by REST or HCM Data Loader (HDL).
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.
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
The move to REST web services is ongoing, and with each recent quarterly release, new REST web services have been made available, or existing web services have been extended. This is good news, and if a REST service is available as well as a SOAP web service, REST is the option to choose.
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.
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.
Sample REST Call to add a team member to a Project
Business Object REST web services are documented here.
HCM Data Loader (HDL)
HCM Cloud provides a tool called HCM Data Loader (HDL), accessed using the Data Exchange usage. This covers a variety of HCM business objects, and is used to load and maintain users, user roles, workers and key setup elements such as Jobs and Locations.
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.
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.
Sample Job Load using HDL
For more complex multiple-level entities such as Workers, the situation is more complex. Multiple metadata rows are combined in the same file, for example, Names, Emails and Assignments can be included along with the Person Number.
Sample Employee load using HDL
Date-tracking in the HCM modules is a key area to watch for and ensure is coded correctly; a change in Start Date is possible but can create problems if noticed several years later and a worker has a complex employment history (e.g. multiple hires, re-hires across multiple legal entities with OTL timesheets).
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.
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.
BI Publisher
BI Publisher reporting allows us to run custom SQL statements against the ERP Cloud Database. We typically need to verify setup values, report on import errors and confirm data imported successfully during our integrations and conversions, and so the easiest way is to call BI Publisher using web services. These are documented here.
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.
The UCM web services are documented in this My Oracle Support note:
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)
BI Cloud Connector allows you to run a Cloud Data Extract to UCM or to Oracle Cloud Storage.
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.
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.
You can be notified once an extract is complete. The zip files on UCM contain the data in CSV format along with a Manifest and JSON file. The Manifest file shows the content files and view version:
Manifest
The JSON file returns the query performance data.
JSON
Documentation for 19A is found here.
Cloud Conversion Challenges
Using the ERP Cloud tools to execute data conversions and integrations without some form of automation can lead to problems.
- 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 2, we have reviewed the tools available to load data into ERP Cloud, and discussed some of the challenges.
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.
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.
Author
Matt Paine , Co-Founder JMJ Cloud
RSS Feed