​With the evolution of ERP Cloud and Oracle Cloud Infrastructure (OCI), we are starting to see ERP Cloud utilize OCI features. This is a positive move and it will allow ERP Cloud customers to gain more value from their ERP Cloud investment. One area where we have seen this partnership blossom is with ERP Cloud BI Cloud Connector (BICC) and OCI Object Storage. In this post I will cover how you can leverage more from this integration using OCI Events and Notifications.

​Oracle BI Cloud Connector (BICC) provides a robust mechanism for performing bulk exports of data from ERP Cloud. Exported CSV files can be directed to Universal Content Management (UCM) (contained within the ERP Cloud environment), or to an OCI Object Storage Bucket. BICC exports are scheduled through the ERP Cloud Enterprise Scheduler Service (ESS). For those of you familiar with e-Business Suite, this is like the Concurrent Manager. So, how do we know when a scheduled job has finished so we can go fetch the file? This is the problem we will address in this post.
 
Although the example below describes a scenario using ERP Cloud, this pattern can be re-used for any scenario where you need to perform an action when a file is created in an OCI bucket, but you don’t know when the file will be created.

Oracle Cloud Infrastructure includes two integration tools that can help us with this problem. These are Events and Notifications.
 
Events Service
Events fire when certain things happen on your OCI infrastructure. In our case we want to launch an event whenever a file is created in a specific OCI Object Storage bucket. An event on its own, however, is not very useful. We need the Event to do something and this is where Notifications come in.
 
Notifications Service
A Notification can perform one of the following operations:

• Email
• Function (Cloud Based Function e.g. Java)
• HTTPS (Custom URL)
• PagerDuty
• Slack

 
The key notification for us is ‘HTTPS (Custom URL)’. This notification type is further described in section 3 below. When this type of Notification fires, it posts a JSON payload to the HTTPS Endpoint that you define. The content of the payload is specific to the OCI service that causes the Event to fire. In our case, it will contain information about the file that was created.

1 - File from ERP Cloud
In our example this file is coming from BI Cloud Connector (BICC) on ERP Cloud. As I mentioned earlier, BICC allows you to perform high volume extracts of data from ERP Cloud to either local Universal Content Manager (UCM) or an OCI Object Store Bucket. We will be using OCI Object store as this gives us more options and easier access to the files once created. BICC allows you to either call a web service to launch an extract job or to use ERP Cloud to schedule the extract. In view of this, files could appear in our OCI Object Store Bucket at any time. We need to know when the file is created so we can go and fetch it.
 
Object Store Bucket
You will need to create an Object Store bucket to receive your file. When creating your Object Store bucket, be sure the ‘Emit Object Events’ option is checked. This is what allows us to subscribe to events on the bucket.

2 - Event
As soon as the file is created in our object store bucket, the event fires. Events consist of an Event Type (this describes the action that should trigger the event) and an Event Action (what you want to happen when the event occurs). In our case the event type is 'Object - Create' and the Event Action is to call a Notification.

3 - Notification
​The Notification is the operation we want to perform when our event fires. We are using the ‘HTTPS (Custom URL)’ operation. Notifications consist of Topics which allow us to categorize our notifications. For each Topic, we can create multiple Subscriptions. This allows us to let multiple systems know when an Event occurs. In this example, we are referencing our ORDS Endpoint.

If your ORDS end point is unavailable when it is called by the Notification, OCI will re-try to post to your service for a period of up to 2 hours.

​The Endpoint service needs to have a POST handler to accept the payload from the Notification. In our case, our Endpoint is an ORDS REST service running on an APEX environment (see step 4 for details).

When you first create a notification, it will go into a status of ‘Pending confirmation’.

At the time you create the Notification, OCI sends a payload to your Endpoint containing a confirmation URL. The confirmation step ensures that you have control over the Endpoint that you define in your Notification.

4 – ORDS Service
The ORDS web service definition is pretty simple. Here is a screen shot (from SQLDeveloper) of the Module, Template and Handler for a service that can receive a payload from an OCI Notification.

The PL/SQL code you add to the handler can do anything you want. In our case it submits a Database Scheduler Job to fetch and process the file asynchronously.
 
5 – Process File
In our DB Scheduler Job, we use the OCI Object Store REST services to fetch the file from the OCI Object Store bucket. This connectivity between APEX and OCI was made much easier in APEX 19.2 when Web Credentials were enhanced to include the ‘Oracle Cloud Infrastructure (OCI)’ Authentication Type. This allows us to configure a native Web Credential for connecting to OCI Web Services. I recommend this Oracle A-Team Blog which describes in detail the OCI and APEX setups that are required to get this integration working.
 
After you have fetched the file (using APEX_WEB_SERVICE), you can then parse it (using APEX_DATA_PARSER) and process it as necessary, making the data available for your APEX Applications.

​I have been using APEX and ORDS on Amazon Web Services (AWS) for a number of years and until recently, I was not sure Oracle was ever going to compete. Oracle Cloud Infrastructure has come a long way in the past 12 months. While it still falls far short of AWS in terms of functionality, the functionality that is there is intuitive and works well. If you add great initiatives like ‘Always Free’ ATP, ORDS and APEX then you have a platform that is worth getting to know..
 
As APEX/ORDS developers and architects I encourage you to explore OCI. I also think we should start  to think differently about how we build our APEX solutions to utilize OCI where it makes sense. 

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

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

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!

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.

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.

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.

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

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.

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.

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.

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!

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.

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.

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.

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:

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

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

​The prepared HDL files are then accessed on the Download page.

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

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

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.

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

​Once loaded, the worker data can be reviewed and verified.

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.

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.

​
​As before, we created an upload form in APEX to accept XLSX files using APEX_DATA_PARSER.

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

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.

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

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.

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.

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