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

Our Blog

Oracle ERP Robotic Process Automation (RPA) with APEX

9/10/2020

0 Comments

 

Introduction

RPA
​By now, most people have heard about Robotic Process Automation (RPA) tools. While these tools come with the glow of the next new thing in technology, in my experience, ERP integration and automated scenarios are complex, and I am not yet convinced RPAs can handle them as well as the latest hype may lead one to think. I am convinced that APEX can and has handled many real-world automation and integration scenarios in the ERP world. In this post I will describe a real-world example where APEX automates and orchestrates a repetitive e-Business Suite process. ERP Cloud examples will be featured in future posts.

Background

My whole career has been focused on building software to automate manual processes. Most of this automation work has been focused on Oracle’s e-Business Suite (EBS) and ERP Cloud. ERP software has a huge amount of functionality, but it is necessarily geared toward the most common business processes. In view of this, common processes like creating items must be broken down into their component steps, e.g., Create Item > Assign Categories to the Item > Choose Catalog Values for the Item > Create Item Cross References. If you are creating hundreds of items a week, this can be a very time-consuming (and error-prone) process. Of course, item creation is just an example. Depending on your business, there may be many opportunities for automating costly manual processes that occur in your ERP software. Given the hype around RPAs, one obvious solution is to deploy an RPA to solve your automation challenges. I am not so sure….

What is an RPA?

If you have ever used automated testing tools like Selenium or Cypress, then you have a head start in understanding RPA tools. RPAs automate steps that an end user performs via the UI. It repeats steps like button clicks, data entry into fields and page navigation in a web UI.
 
For example, if you need to onboard one new user, you might perform the following steps:
  1. Login to the Application
  2. Navigate to the User Entry page
  3. Enter information about the new user
  4. Click the Create User button
  5. Navigate back to the User Entry page
 
To onboard 100 users you would repeat steps three to five 100 times while reading data from a file. This is where RPA steps in and performs this series of repetitive steps for you. Being able to perform automations at the UI level makes adoption relatively easy. There is no database to connect to, no code to write or APIs to learn. Essentially you don’t really need to involve IT!

RPA Tool Vs Low Code API Automation

In my opinion, RPAs fall short in two important areas when compared to an Low Code API solution like APEX.
​ 

Changing UI
RPAs rely heavily on a consistent UI. They depend on certain HTML tags being present or fields having specific IDs, Labels, etc.
 
The problem is, modern ERP solutions like ERP Cloud release new functionality on a quarterly basis. Releases often comes with changes to the UI which can break your RPA processes. A low code solution will typically call APIs (e.g. REST services) to perform automations. While APIs are not completely immune to change, there is the assumption of a contract between an API developer and its consumers. Developers break this contract at their peril.
 
Performance and Reliability
Because RPAs interact with the UI they rely on the stability and speed of the UI in order to work effectively. What if there is a 5 second delay between clicking the Save button and the next page appearing? While you can accommodate for the delay in an RPA script, it can have a huge impact on how long it takes to load high volumes of data. In addition, if a page fails to load completely, then your RPA script will fail. You are left figuring out where it failed and having to re-start it from the right spot.
 
APIs are typically more stable and always more performant than the UI they serve. When you call an API to create a record, you get a specific response from the API and can handle what happens next directly in your code. In addition, there are often API options to perform bulk loads of data where you can send thousands of records in one post.

License and Resource Costs
You should also consider that while it may not always require the specialized skills of a Developer, RPAs do still require skilled functional resources to keep them running smoothly. When comparing to APEX, you also have to consider the additional license costs that come with an RPA solution.

​Considerations for Automation

There are a few things you should consider before diving into any automated solution. The first is cost. You should weight the cost of developing an automated solution vs the cost of not doing it at all. You should also consider your ability to maintain the solution once it has been implemented. If you build automations based on complex integration software that requires highly specialized skills to maintain, it may end up costing you more (in consulting fees and monthly subscriptions) than the manual processes did.

​Enter APEX

APEX is a Low Code development platform that, assuming you have an Oracle database, requires no additional software licenses. If you are running EBS you can install APEX on your EBS database. If you are running ERP Cloud, you can stand up a PaaS AWS RDS Oracle instance with APEX for as little as $100/month.
 
We have already blogged that APEX is an ideal partner for both EBS and ERP Cloud. On EBS, APEX lives in the same database as EBS and can easily call EBS public APIs to automate EBS activities. In the cloud, APEX comes complete with numerous utilities to make calling web services easier (web source modules, credential stores, APEX_WEB_SERVICE APIs with automated token renewal, etc.). Used in conjunction with Oracle REST Data Services (ORDS), you can also create Webhooks to handle events from your ERP systems.

​An Example Automation for EBS

Now that we have decided on APEX to build your automation vs using an RPA, what might an APEX centric-solution look like? Let’s use the item creation example I mentioned at the start of this post to illustrate what is possible. In this use case, the goal is for a user to be able to create inventory items in EBS with as few manual steps as possible.
 
The manual steps required by a user to create an item include:
  1. Login to EBS
  2. Launch EBS java forms
  3. Choose an Inventory Organization
  4. Navigate to the Item Entry form
  5. Find the item you want to copy from
  6. Create a new Item as a copy of the item
  7. Update the item number and description of the new item
  8. Navigate to the item category form
  9. Assign the correct item category to the item
  10. Navigate to the Item Catalog form
  11. Enter up to 5 catalog name catalog value combinations
  12. Navigate to the Item Cross references form
  13. Create an item cross reference for the new item
  14. Repeat steps 4-13 X times
 
Here is what it looks like when we merge steps 4-13 into one APEX page.

APEX Automation Use Case
When users first navigate to the APEX page, they choose an inventory organization to copy items from and optionally enter an item prefix to filter source items they can choose from.
​
Picture
The user then adds source items to an interactive grid. Source items are items that already exist in EBS and used as the basis for creating new items in this example.
Picture
The target values are handled as follows:
-Item Number - Users can manually enter a target item number or click ‘Generate Item Numbers’ to automatically number the new items using an EBS Document Sequence.
-Item Template – Next the user chooses and item template. Items in EBS have hundreds of attributes. An item template allows you to use the template attributes to set the attributes of newly created items.
-Item Long Description – The user optionally enters a description of the item.
-Catalog Values – Catalogs allow you to assign sets of attributes to an item. The catalog attributes of the source item are brought in automatically and the just must update values for the new item.
-Cross Reference – Cross references allow you to cross reference your items to other entities e.g. Supplier Item Numbers. Finally, the user selects a cross reference type and enters a value.
 
Note: While this looks like a lot, all but two of the above fields are optional. There is even an option for users to upload items Via Excel to perform bulk loads.
 
An APEX collection is used to store the user entered data until the user is ready to initiate the copy process by clicking ‘Copy Items’.
 
The actual process automation i.e. copying the items, category assignments, catalog elements and cross references is performed by a PL/SQL package called from APEX. The PL/SQL package calls the related EBS Public APIs in sequence utilizing the data from the APEX collection for the required parameters.
 
EBS Public APIs used by this process:
  • Item (ego_item_pub.process_items)
  • Category (inv_item_category_pub.create_category_assignment)
  • Catalogs (inv_item_catalog_elem_pub.process_item_descr_elements)
  • Cross References (mtl_cross_references_pub.process_xref)
 
When the copy is complete the user is presented with the results of the copy. An added benefit of using APEX is that users can easily see which steps completed successfully and which ones failed.
Picture

Conclusion

There is a place for RPAs in performing repetitive tasks on moderate amounts of data on simple UIs that do not regularly change. ERPs rarely fit this mold. Data volumes are usually too high to justify a RPA versus an API due to an RPA’s slow performance compared to an API.  Additionally, change is built-in and assumed in the cloud. Inconsistent performance is not an option.
 
APEX answers many questions in the ERP world. In this post we have learned in this post that APEX can also be called on to build complex orchestrated automations both inside and outside of your firewall.

Watch this space for future posts geared towards automating manual processes in ERP Cloud using APEX.
0 Comments

Oracle ERP Conversions Made Easy with APEX (Part 3)

6/10/2020

0 Comments

 
Picture
​In our prior post, Oracle ERP Conversions Made Easy with APEX Part 2, we reviewed the tools available to us to load data into ERP Cloud and discussed some of challenges using them.
 
This final part of our blog series shows you how APEX will make your conversions simpler, faster and repeatable. 
​

APEX to the Rescue


By taking advantage of APEX’s built-in features for modern cloud-based applications, your conversion can be simplified and automated.   With APEX, you can:
  • Build a secure, multi-tenant, cloud application, delegating authentication to other entities such as Office365 or Gmail.
  • Build forms allowing users to upload files securely to an APEX application and review content using an Interactive Report or Interactive Grid.
  • Parse input files using APEX_DATA_PARSER in a variety of formats, for example CSV, Excel, XML and JSON.
  • Call ERP Cloud REST or SOAP web services using the routines defined in the APEX_WEB_SERVICE package.   For users of Autonomous Database, no additional wallet or Access Control List configuration is needed.   If you’re on Exadata Express Cloud Service, you’re also OK, though perhaps it is time to upgrade!
  • Enter Web Service Credentials and store them securely in APEX.   Credentials can then be maintained or set at the session level using the APEX_CREDENTIAL package.
  • Use APEX_JSON to work with JSON files used by many REST web services.
  • Zip FBDi files for import using the built-in package APEX_ZIP.
  • Use the rich functionality of SQL and PL/SQL to clean, map and transform data before it is imported.
  • Use APEX_JWT to work with JSON Web Tokens (JWT) issued by ERP Cloud.

If you don’t have access to APEX, this functionality is available FREE as Oracle provides you with two Always-Free instances of Autonomous Database via this link:   https://www.oracle.com/cloud/free.

If you haven’t done so already, sign up today as it isn’t often you get something for nothing!
Applying the APEX Toolset to ERP Cloud Conversion
An APEX application used to convert data to ERP Cloud can:
  • Stage and parse simplified source data from Excel XLSX spreadsheets.    APEX will accept other formats too (e.g. XML, JSON, CSV, PSV) but XLSX is more user friendly.  APEX will read the underlying numbers and dates in their raw form, regardless of formatting.
  • Reduce end-user data preparation of the number of fields required on an FBDi upload.
  • Automate data cleansing (for example, changing case, removing trailing spaces, applying or reading date formats).
  • Share data, files and results across users in a multitenant application.
  • Maintain mapping tables for data transformation per target environment.
  • Synchronize master data from ERP Cloud to validate data and mappings, for example lists of Legal Entities, Business Units or Chart of Accounts values.
  • Retrieve conversion data from ERP Cloud to validate that all data imported and produce reconciliation reports.
  • Create pre-zipped upload format files with a single button click, e.g. File Based Data Import, HCM Data Loader.
  • Automate data uploads using FBDi, HDL or Business Object Web Services.
 
Sounds good?   Let’s move on to look at some real-world examples.
Real-World Example 1 – Employee Conversion
A client recently needed help converting workers for an ERP Cloud project.     The number of fields to be populated was small, as Oracle HCM was being used only to support Expenses and Self-Service Procurement.  External systems were in place for HR and Payroll.
​
Workers would be provided to us in a simple spreadsheet format, similar to that below.
Picture

The Company field would act as a key used to derive additional fields such as Legal Entity and Business Unit.

The objective was to enrich multiple spreadsheets received in this format from each of the client businesses with the additional fields needed by Oracle, and then to map the data into the Employee/Supervisor HDL file formats.

​First, we built an APEX Classic Report Cards page to allow users to upload an XLSX spreadsheet into a database table.
Picture

This makes use of the APEX_DATA_PARSER package to quickly transform the Excel file into the database.

​A mapping page was built using an APEX Interactive Grid, allowing us to enter and maintain the mapping from Company to other fields required by HCM Data Loader:
Picture

​A key field for ERP Cloud Finance projects is the Default Expense Account, used by Expenses and Procurement.    As the Chart of Accounts structure could vary depending on which Ledger the Business Unit is linked to, the mapping simply populates any segments provided in the source file, then fills in any missing segments based on the Company.   In this case, the Company, Division, and Department are in the source file, and the Account is defaulted in from the Company mapping.

The mapping is applied to the imported file, and the enriched data can be reviewed.   At this stage, data has also been cleansed – for example worker names are trimmed of leading or trailing spaces and changed to uppercase.
Picture

​When the data is ready to be loaded to ERP Cloud, the Build buttons are used to generate zipped HDL files in the correct format.   APEX_ZIP is used to zip up the files.   Only selected records are included; this allows us to re-send a specific worker if an error is encountered during the import process.  
Picture
​The prepared HDL files are then accessed on the Download page.
Picture
​If we open up the zip file, we can see that the hard work of building the HDL file in the correct format has all been done.
Picture
​In this Use Case, the upload was performed manually from ERP Cloud using the Data Exchange screen below.   Note, this step could also have been automated in APEX.
Picture
Having uploaded the workers, we now need to check that they all imported successfully in order to load the supervisors.    That’s easy from the Data Exchange screen if all workers load, in a larger file, getting a quick list of kickouts can be challenging.
​
We’ll use another APEX page for this.    Clicking on the New Worker Extract button initiates a worker download from your ERP Cloud Environment.
Picture
​A background job uses the APEX built in packages APEX_WEB_SERVICE and APEX_CREDENTIAL to connect securely to BI Publisher running on ERP Cloud and run a report on workers.   Data is retrieved in XML format and parsed into our database using APEX_DATA_PARSER.
Picture
​Once loaded, the worker data can be reviewed and verified.
Picture
Back on the original Worker Upload page, the Build Supervisor HDL File button can build a second file adding each worker’s Line Manager.   The form checks first that the Line Manager exists in Oracle, based on the data that was downloaded using BI Publisher in the prior step.
 
APEX helped us reliably validate, map, and load 1000 workers from five different sources in under an hour on cutover weekend – and this process can be repeated on subsequent projects.
Real-World Example 2 – PO Change Orders
Another customer needed assistance doing a mass-update of 7,000 PO Line Schedules from 3-Way Match to 2-Way Match.   

​They would provide a list of the Purchase Orders, Lines and Schedules, and wanted us to create a process to create Change Orders using a Procurement Cloud SOAP web service.
​
We verified the changePurchaseOrder web service was operational and met customer requirements before documenting the inputs it needed for a successful update of single and multi-line orders.

    
​The input file would be a flat XLSX format, which we extended to allow update of up to three fields at the Line or Schedule levels.   As it turned out, the only attribute that the client wanted changing was MatchApprovalLevelCode, but this future-proofed us for any additional requirements or use on other projects.
Picture
​
​As before, we created an upload form in APEX to accept XLSX files using APEX_DATA_PARSER.
Picture

​Imported rows are reviewed, and when the Submit Change Process button is clicked, a background job is run to do the updates.  
Picture

The ERP Cloud Credentials used by the web services are held securely using APEX_CREDENTIAL.  APEX_WEB_SERVICE is used to call SOAP web services for each PO update, checking the PO status before and after the changes using further web service calls.
Picture

​Any issues with updates (for example because a PO already has a change order in process) are reported back to APEX, along with the PO status before and after the change.
Picture

​The standard APEX Interactive Grid download functionality is used to download the files rows and the update messages, providing a status report and Audit Trail.
 
Following a dry-run in a TEST environment first, the mass-update of 7,000 PO schedules worked successfully in PROD in a couple of hours.
Additional Use Cases
Over the last 5 years of working with ERP Cloud, we have encountered many reproducible use-cases that would benefit from the tools available to us in APEX.
​
We are building these into the TurboLoad module of JMJ ERP Cloud Express.
Picture

Further examples include:
​
  • ESS Job Hold and Release.   Oracle asks you to put ESS and BI jobs on hold before quarterly updates; if you have hundreds of jobs running this can be time-consuming without automation.   Upload a simple spreadsheet with a list of the ESS Job Ids to quickly apply the holds and release them once the environment is released back to you by Oracle.
  • Supplier Inactivation.   Do you have hundreds of supplier sites that are no longer used or required?   A mass-update can be used to quickly apply an inactive date to these records to clean up your supplier master.
  • GL Conversion.   The FBDi format is recommended for high-volume uploads but doesn’t pre-validate GL Code Combinations or check that other attributes of your conversion journals are correct before Import Journals is run.   This APEX page allows you to convert your GL data from a simple Excel file, check account segment values and cross-validation rules and journal totals before mapping into FBDi format and automating Import Journals.
  • Password Resets.   We don’t recommend mass password resets using a single password post-clone as this represents a security risk, however it can be necessary to mass-update multiple user passwords from time to time for UAT or Training purposes.   This can be done from a spreadsheet using Oracle’s SCIM (System for Cross-domain Identity Management) web services.
  • Project Status Changes.   If you have many open projects in PPM and want to change their statuses, e.g. from Contracted to Closed, this tool will help you.   Upload a spreadsheet with the Project Numbers and their desired status, and APEX will quickly apply the updates.
Conclusion
​A SaaS based environment with all the features of APEX and the Oracle Database is the ideal tool for Oracle ERP Cloud data conversions and integrations.
 
Streamline your ERP Cloud data loads using APEX built-in web service calls, Excel file parsing and low code UI development allied with the power of SQL & PL/SQL.    
 
Your conversion process can be faster, repeatable, and generate a full audit trail and upload reporting.    Isn’t that we all want when loading data to Production?
0 Comments

Oracle ERP Conversions Made Easy with APEX (Part 2)

5/13/2020

0 Comments

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

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

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. 
Picture
ADFDi for importing GL Journals
Picture
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.

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

  • 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:
  • 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:
  • SOAP 
  • REST 

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

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

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

Author

Matt Paine , Co-Founder JMJ Cloud
0 Comments
<<Previous
Forward>>

    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
    OCI
    ORDS
    PaaS
    RAD
    REST
    SOAP

    Archives

    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