Oracle APEX is a low-code development tool which is marvellous for developing responsive cloud applications. Oracle Cloud SaaS Applications (aka Fusion Applications) are a comprehensive suite of SaaS applications which are used by many leading organisations to manage their HCM, Finance, Procurement, etc functions. With the release of Oracle APEX 23.2 we now have the ability to easily work with Oracle Cloud SaaS REST endpoints to not only query but also update and delete data in the Oracle SaaS applications.
The capabilities of the integration are quite wide ranging as the documentation shows and include the ability to create filter parameters and reference sandboxes. As an introduction to the topic, I will look at an aspect of Oracle Spend Classification within the Procurement SaaS application, and show how we can surface this data in APEX.
Using 23D of Procurement, I have the Procurement Data Analyst job role so I can access Spend Categorisation and look at the Taxonomies that have been created. I have 5 of them, of which 3 have some categories set up.
If I wanted to put an APEX wrapper around this data and blend it into other applications or allow users to interact with it in different ways then I can, so let’s do that as a simple example.
Finding the relevant REST API to get the Taxonomy details would be a good start. As I’m using 23D then let’s look at the Procurement REST API manual for the GET API.
I can now setup the REST API in my APEX application (version 23.2+). I’ll create a new empty application and look at the Data Sources part of the Shared Components where we now see the option to define REST Data Sources.
This gives us a wizard to use to define the REST data source. Firstly for this type, select the new “Oracle Cloud Applications (SaaS) REST Service”.
For Name we can choose anything we want. For the URL Endpoint, we want to use the URL for the Fusion instance with the /fscmRestApi/resources/11.13.18.05/spendClassificationTaxonomies at the end of it. Then moving onto the next screen we see the BASE URL and Service URL are created for us.
Next we create the “remote server”. The “Base URL” is the server specific part and when you have a number of Fusion pods (e.g. production, development) this will differ between them, wheras the service URL path likely will not.
Next we get a few options that are mostly performance related. Each has a link to the relevent documention, but the Batch DML is the batching of insert/update/delete commands rather than doing them individually. Total Results returns the total number of results from the SaaS application, even if we are just returning the first set from the initial fetch – which can impose a computation overhead. Pagination determines if we are to return results a page at a time or not ( and this is also dependent on the capabilities of the REST call). Here I have left them as their defaults.
Next we need to create the Authentication so that APEX can connect to the SaaS instance.
I select “Basic Authentication” and enter the username/password of the account I wish to use as the connection. Then I press “Discover” to perform the dicovery of the REST API and, assuming I have successfully set it up, I see the data returned from the GET.
I can check the results with the data in the SaaS application – they match – so I am happy that is working. That is the end of the setup for me so I now have a SaaS REST API defined and available to use.
Before we do use it though, we can consider using REST Synchronization. This is basically a facility that can be invoked manually or progamatically on a schedule to fetch data from the REST API and synch the data with that from a local table rather than executing the REST API to get the data on demand. This also means we can perform other activity such as manipulating/augmenting the date returned or index the local table as we want as it is under our control.
We can create this from the REST Data Sources option and selecting the data source for which we want to create the synchronization. Look to the right to see the “Manage Synchronization” action.
We will not create one though, and just retrieve the data from the REST API on demand as we need it as there are only a few rows.
Create a new page in our APEX app to visualise the data. Let’s choose a Cards region.
When defining the Data Source we can point that to the new REST Data source we have created.
Once the Cards region has been created we can then use APEX to perform some formatting and lay the records out. Here I have done some simple manipulation and added a button to each card as an action where I will allow the users to navigate to an in-line region which will show the categories assigned to the taxonomy.
Now we have taken our first steps to access Fusion SaaS data in APEX, we can look at extentions to apply filtering and also bi-directional support with updates and inserts of Fusion data.