​This is the first in a two-part post on how to use APEX to facilitate data conversions (and data uploads) into Oracle e-Business Suite (EBS) and Oracle ERP Cloud.

ERP systems are data hungry beasts that require constant feeding. The task of converting and loading data using existing tools are both time consuming and error prone.

In these posts we will cover the benefits of using APEX for data upload and describe several examples where APEX has been used to make the task of bulk loading data into EBS and ERP Cloud more user friendly and more accurate than traditional methods.

In this first post, we will focus on Oracle e-Business Suite (EBS).

In the olden days, the business and IT had to perform the following complicated dance in order to load data into EBS:

1. IT receives a copy of the data file from the end user.
2. IT copies the file to the application file server.
3. IT runs a SQL*Loader script to load the table to a custom staging table.
4. IT runs a concurrent program to validate the staged data and sends a report to the end user to review.
5. The end user corrects errors in the data and sends a new file to IT to repeat steps 1-4.
6. Once all data has been validated, IT will run another concurrent program to move the data to the Open Interface table and launch the Open Interface concurrent program.
7. IT sends the report output from the Open Interface to the end user.
8. End user reviews results and potentially sends a revised file with fixes to any failed records.

Any solution we provide using APEX must meet the following criteria:

• Must be user friendly so that occasional business users can easily preform uploads.
• Must clearly show end users what data is valid and what is invalid.
• Must utilize standard EBS mechanisms to perform the final data load (e.g. EBS Open Interface Tables, EBS Public APIs etc.).
• Must prevent duplicate and or bad data from being loaded into the system.

As you can see from the design pattern above, using APEX, we have reduced the number of steps from 8 to 4 and put the entire process in the hands of the end user.

Let’s drill into these steps in a little more detail.
 
1. Data Parsing and Initial Validation
The File item type in APEX allows users to drag and drop (or select a file) from their file system onto an APEX page. You can then process that file as a BLOB within PL/SQL. Once you have the BLOB you can hand over the heavy lifting of parsing the file to APEX_DATA_PARSER. APEX_DATA_PARSER (available since APEX 19.1) does the heavy lifting of parsing the BLOB and giving you the results so you can consume them in a SELECT statement. We blogged in more details about APEX_DATA_PARSER here.

​Example SQL Parsing a BLOB:

Now that the data is parsed you can loop through it performing your own pre-validations. It is a good idea to perform pre-validations on user-provided data so that you can intercept 95% of errors before records make it to the EBS Open Interface tables. It is much harder to clear records and re-try failed records once they are in the Open Interface tables. As the records are validated, we add them to an APEX Collection. Collections are possibly the most useful tool in your APEX toolbox.  They provide a temporary storage area for data that is contained within your specific APEX session.
 
2. Review Validation Results
Now that we have the valid and invalid records in a collection, we can present these to the user in an Interactive Report (or Grid). The user can review invalid records, correct data in their source file, re-upload and re-iterate until all records pass validation. The ability for the end user to iterate is a key differentiator when using this method over traditional approaches.
 
3. Initiate EBS Open Interface
Once the user is happy with the results of validation, they click a button to insert the valid records into the appropriate Open Interface tables and then the Open Interface Concurrent program is launched using a PL/SQL API.
 
4. Review Final Results
Once the concurrent program is complete, the user can review any failed records in the interface table and see errors from the open interface table’s associated error table. For example, the item interface table mtl_system_items_interface populates errors in the table mtl_interface_errors. This is achieved without the user having to leave APEX by providing a 2nd APEX page to review the content of these tables.

Here are some considerations when using APEX to load data:

• APEX Collections
  • APEX Collections offer a great deal of flexibility. Because collections are widely used by APEX developers you need to ensure you don’t adversely impact performance of other code that uses collections. You can do this by making sure you delete the collection once you are done with it.  This is especially important when loading large volumes of data.
• Unique Batch Identifier
  • Once you move validated records into the EBS Open Interface table, you need a way to uniquely identify your specific batch of records. Most EBS interfaces have a batch identifier column that you can use for this. For example, the item interface table mtl_system_items_interface has a column batch_id that you can use for this purpose.
• Viewing Results of the EBS Open Interface in APEX
  • Once the Open Interface has run you should provide another APEX page to allow users to see records in the interface table and its respective error table, e.g., the item interface table mtl_system_items_interface and its error table mtl_interface_errors. The unique batch identifier described previously helps you query records for a specific interface run.
• Using EBS Public APIs
  • Many EBS entities provide a public PL/SQL API as well as an Open Interface. Using a public API gives you immediate feedback (success or error) on whether a record was successfully created in EBS. This does have obvious benefits, but you should use caution when loading large numbers of records. When calling the public API for each record, your user will have to wait until all records are created before they get control back. There is also a risk that the web server will timeout. However, if you submit a concurrent program to load via the Open Interface, you user can go about their work while the load occurs in the background. I typically try to use public APIs for loads of up to 10 thousand records and the Open Interface for larger loads.
• Reduced Custom Objects
  • Using APEX, APEX_DATA_PARSER and APEX Collections reduces the number of custom objects you need to deploy to EBS to support your solution.
  • APEX_DATA_PARSER removes the need for SQL*Loader scripts to load data files into a staging table.
  • APEX Collections allow us to store data temporarily while it is being validated, thus, removing the need to create or maintain a set of staging tables.
• More Parsing Options
  • APEX_DATA_PARSER allows you to natively parse xlsx, csv, json and xml files whereas SQL*Loader only handles delimited and fixed format files.
• High Volume Uploads
  • If you are uploading hundreds of thousands (or even millions of records), then SQL*Loader is probably still the way to go. SQL*Loader offers features such as direct path upload that make writing data to staging tables orders of magnitude faster than standard INSERT statements.

​Here are just a few examples of EBS conversions and data loads we have developed for our customers using the above approach. Hopefully they will give you some ideas.
 
Inventory Item Uploads
Load new inventory items into EBS from an Excel Spreadsheet.
 
Inventory Item Category Assignments
Load item category assignments from an Excel Spreadsheet.
 
Inventory Item OnHand and Cost Upload
When you need to upload items you typically also need to load starting inventory on-hand balances as well as average or standard costs.
 
Mass Blanket Agreement Price Updates
If your organization has hundreds or even thousands of blanket purchase order agreements, applying price updates across these blankets can be a challenge. In this interface we allowed end users to build an Excel file of items with their new prices and apply them to blanket purchase orders using the Purchasing Open Interface.
 
Import WIP Labor Hours
If you use EBS for managing Work in Process (WIP) jobs, you need to capture labor costs to accurately cost these jobs. In this interface users uploaded csv extracts from their time and attendance system into an APEX page which then uploaded the data into EBS using the WIP Cost Transactions Interface.
 
Import External Payroll Data
In this instance the customer used Oracle Payroll but needed to interface additional payroll data from several 3rd party payroll systems. End users export payroll reports from the external payroll systems and then drag and drop these files into an APEX page so they could then be uploaded into Oracle Payroll. This gave the customer the ability to view payroll data across the entire organization.

​You may still be using EBS, but by utilizing APEX, you can introduce significant process efficiencies and UI modernization initiatives to delight your users.
 
Utilizing APEX along with EBS allows you to introduce process efficiencies and modernize the UI experience to the delight of your users.

​I worked for Oracle Consulting for nearly 18 years. During that time Oracle steadfastly stuck to their mantra of using their own products wherever possible (eating their own dog food). Trust me Oracle’s home-grown email client in 1998 was not pretty. This approach did give Oracle the chance to showcase and improve their products (especially the customer facing ones).
 
This approach stuck with me when my partners and I started JMJ Cloud. Since the weekend we started our business (when we built our time sheet application in APEX) through to our most recent push to replace Jira Service Desk with an APEX App, we have been using APEX to solve our own business problems as well as our customer's. In this post I will review the building blocks of the integrated APEX portal we built to help us run our business and interact with our customers.

There was never any doubt that we would choose APEX to build our platform. In addition, we did not want to be managing servers ourselves so the first thing we did was find a Cloud Provider to host us. We started with the old Schema as a Service then moved to Exadata Express and most recently Amazon RDS. Building and running our platform on these solutions has allowed us to provide real world expertise to our customers running APEX in the cloud. At less than $100 / month for our current AWS setup, our costs are very reasonable too. See our post on why we moved to AWS. We are still looking forward to moving back to an Oracle platform (when the price is right).

We did not want to be in the business of managing passwords, so we utilized a custom authentication scheme using APEX Social Sign on. This allows our customers and consultants to login to the platform using their own Microsoft (Office or Personal) credentials. Not only is this safer for us (we don’t have to store or manage passwords) it is also much more convenient for our users.

Tip: If all you do is create an authentication scheme for Microsoft Social Sign On, then you have just given everyone with a Microsoft account access to your applications. You must follow it up with a default Authorization scheme that verifies the authenticated user has access to your portal.

Authorization Schemes in APEX offer a flexible way to validate user access. An authorization scheme allows you to test a condition and return a TRUE or FALSE. The condition could be a simple comparison of application items all the way to hundreds of lines of PL/SQL which results in a TRUE or FALSE. Once you have an authorization scheme, you can then associate it to just about any APEX object (Menu Option, List Item, Page, Region, Page Item, Button, Report Column etc. etc.). You can even test authorization schemes in your PL/SQL code using APEX_AUTHORIZATION.IS_AUTHORIZED.

Of course, your authorization scheme must be based on some data. We decided on a simple role-based model with the following entities:

• Users
• Applications
• Application Roles (User, Admin, Project Manager etc.)
• User > Application > Roles

 
For most applications, a role-based model will work just fine. The main drawback of this approach, however, is that it does not allow you to be very granular. Sometimes you need to explode this model and include privileges that give users access to specific actions.
 
Tip: Whenever possible set the ‘Evaluation Point’ of your Authorization Schemes to ‘Once per session’. This ensures that APEX only checks the Authorization Scheme at the time of login as opposed to every time a page (or component) is rendered. This is especially important if your authorization scheme contains a lot of code (or even calls to web services). This may seem like a small thing but poor performance in APEX comes about incrementally. Two tenths of a second for two authorization scheme calls, a tenth of a second for a sentry function call and two tenths for a before page process and your user has just waited half a second and the page hasn’t even rendered yet.

We have several applications available to our consultants and our customers. We needed a secure and easy way for users to switch between these applications. After reading Scott Wesley’s blog, we decided on Application Session Sharing. This allows us to easily share sessions across all applications in a workspace. Session Sharing is configured in the Session Sharing section of your Authentication Scheme.

The APEX team continues to make it easier to interact with other systems via REST services. We utilize several third-party services to provide additional functionality to our users.

• SMS Messages Via Twillio (time entry reminders, important notifications)
• File Object store via Amazon S3
• SMTP email via AWS Simple Email Service (SES)

 
Tip: When integrating with external web services you need to become familiar with installing SSL certificates in your database wallet. I suggest creating a single wallet (with auto login) that can be referenced in your APEX environment settings (via the INTERNAL Workspace) so that native APEX components (e.g. Social Sign on, APEX_WEB_SERVICE etc.) can utilize this wallet when making calls to external services. If you are using a completely managed service like Oracle Autonomous ATP then this has been done for you.

Email notifications are a critical component of many applications. We use Amazon Simple Email Service (SES) to host an SMTP server. This allows us to configure our APEX environment (via the INTERNAL Workspace) to send all emails via the APEX_MAIL PL/SQL API. Used in conjunction with APEX Email Templates you have all you need to send professional looking emails that won’t end up in the recipients spam folder.

​In this section I am going to touch on a few of the applications in our portal. The goal here is to give a flavor of what is possible with APEX.
 
Time Entry Application
Time entry is the lifeblood of a consulting company. Even a small consulting company will get mired in spreadsheets without a system for their consultants to enter their time. As I mentioned, we built our current time-entry APEX application the weekend before we officially started our business. Fifteen thousand time entries later and the application is still going strong. Not only does it make time entry more accurate it helps us to generate our monthly invoices in minutes instead of hours.
 
e-Business Suite (EBS) Code Deployment Application
I have been building EBS extensions for nearly 20 years. A constant challenge has been to consistently and accurately deploy code between EBS instances. In days gone by, we achieved this by hand crafting shell scripts to deploy the code and handing these off with the source code to a DBA as attachments to an email. This approach is fraught with errors and inconsistencies.
 
In view of this challenge, we built an APEX application where developers can upload their source scripts for a deployment and the application automatically generates a shell script to install them. The application is aware of all the types of objects you can deploy in an EBS environment and builds the shell script appropriately. These object types include standard database objects (APEX applications, tables, views, PL/SQL packages etc.) as well as EBS specific objects (Menus, Responsibilities, Lookups, Forms, Reports BI Publisher Templates etc.). Once the developer has submitted their deployment, it goes through an approval and eventually a notification is sent to the DBA. The DBA can then download a single zip file and run the deployment on the EBS server. Once a deployment is complete in a TEST environment, it can then be copied to create a PROD deployment (with no manual intervention). This approach improves consistency, accuracy and audit traceability which are all key in an any ERP environment.
 
Service Request Tracking Application
When we started out, we used Jira Service Desk (for managing tickets with customers), Jira Software for managing projects and Jira Confluence for documentation. We loved these products but felt that Jira Service Desk was a little much (functionality wise and cost wise) for our needs. Three weeks ago, we introduced our replacement for Jira Service desk developed in APEX. The key to ticket management for us is to be able to have our customers create and update tickets as well as our consultants.
 
As we already had the portal framework in place (with Microsoft login, authorization management, email and object store capabilities) we were able to build the SR application in matter of weeks.
 
Tip: Our SR system allows users to add attachments. I believe file attachments do not belong in the database. They take up a lot of space on relatively expensive storage. We keep attachments out of the database using AWS S3. You can store a hundred GB worth of files in services like S3 or Oracle’s object store for less than $5 per month.

​APEX has provided us the ability to build a suite of applications that automate many of the manual tasks involved in running our business and in performing our day to day development activities. Just as importantly, it provides us another way to engage with our customers and offer them unique products which add real value.  

​I do not typically advocate building a solution where there is already an off the shelf solution. There are times, however, when being able to quickly build a suite of unique products with a low code development platform such as APEX can add real value to your business. APEX provides us the flexibility to build these solutions using a combination of low code and high code wrapped in a framework which is easy to maintain and upgrade.

If you are running APEX applications within an iframe then changes are coming. Read on to find out how these changes may impact you and what you can do to mitigate their impact on your applications.

As we all know APEX relies on a session cookie to keep track of your current APEX session. Without the ability to maintain a session cookie, you are forced to make your application public (99% of the time, this is not a good idea).
 
In May of 2019, Google warned changes were coming to the way Chrome handles cookies. In February of 2020, Google announced these changes are being rolled out into version 80 of Chrome. (Read here for the details: https://blog.chromium.org/2020/02/samesite-cookie-changes-in-february.html).

We reached out to the APEX development team (via Joel Kallman) and asked what we could do.  That same day (what else would you expect), Christian responded with a workaround. The workaround involves a small change to any Authentication Schemes your application uses. These instructions should work for APEX 18.1 or above.
 
Navigation: Application > Shared Components > Authentication Schemes > Create / Edit

• Change Type to ‘Custom’
• If you have ORDS setup to have /apex in the URL
  • Set Cookie Path to ‘/apex; SameSite=none’
• If you have the default path for ORDS /ords
  • /ords; SameSite=none
    
• Finally, set ‘Secure’ on

​

​When you look at an APEX application session cookie using Chrome Tools, you should see something like the following:

​

Please note, per Christian, some older versions of browsers do not support samesite=none.
https://www.chromium.org/updates/same-site/incompatible-client

According to the development team there are plans to introduce the samesite=none attribute into the session cookie in APEX out of the box. Of course, they would not commit to which APEX version this change will be released in, but we are hopeful it will be soon.