Introduction
This post will show you how to create APEX Apps that maintain Oracle Fusion Cloud ERP data with Zero Code. APEX 23.2 introduced the 'Oracle Cloud Applications (SaaS) REST Service' REST Data Source type. Using this REST Source type, you can create REST Sources that perform CRUD operations on Oracle Fusion Cloud ERP Data without code.
Use Case
This post describes an example of maintaining Fusion ERP Cloud Common Lookups from APEX. The principles are the same for any Fusion REST APIs that support CRUD operations.
Demonstration
Here is a quick video showing the end result: Maintaining Fusion Common Lookup values from APEX with No Code!
Another video showing the same lookup as viewed from Fusion:
REST APIs
Most of the code we write in APEX that interacts with Fusion will do so via REST APIs. Before building the APEX App, we must determine which REST APIs we need and understand how they work.
Documentation
After a quick look through the REST APIs for Common Features documentation, we find the Common Lookups 11.13.18.05 REST APIs.
I next turned to the Oracle Support website and found this note CRUD REST Service for Lookups in Fusion (Doc ID 2751758.1). This had enough rudimentary examples to get me started.
CURL Examples
I will focus on APIs related to creating, updating, and deleting lookup values. FYI, the REST APIs for managing lookup types work the same way.
Create Lookup Value
Note: The lookup type is included in the URL path; in my examples, the lookup type is JD_TEST_LOOKUP_TYPE
which makes the end of the URL path look like this: commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes
curl --location 'https://example.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes' \
--header 'REST-Framework-Version: 4' \
--header 'Content-Type: application/json' \
--header 'Upsert-Mode: true' \
--header 'Authorization: Basic 123=' \
--data '{
"LookupCode": "TEST10",
"Meaning": "TEST10 Meaning",
"Description": "TEST10 Description",
"Tag": "N",
"EnabledFlag": "Y",
"StartDateActive": "2024-07-27"
}'
Upsert-Mode
HTTP header in the CURL above. This is pretty neat. If you set this header, Fusion will create the lookup if the LookupCode does not exist and update it if it does. This saves you writing code to handle create vs update. Even better, if Fusion creates a new lookup, it returns a 201
response, and if it does an update, it returns a 200
http code.If successful, Fusion will return the lookup code object in the response.
{
"LookupCode": "TEST10",
"Meaning": "TEST10 Meaning",
"Description": "TEST10 Description",
"EnabledFlag": "Y",
"StartDateActive": "2024-07-27",
"EndDateActive": null,
"DisplaySequence": null,
"Tag": "N",
"CreatedBy": "TEST1",
"CreationDate": "2024-07-27T17:52:48+00:00",
"LastUpdateDate": "2024-07-27T17:52:48.028+00:00",
"LastUpdateLogin": "1E3E8F073F6D874BE063C05F310ACC04",
"LastUpdatedBy": "TEST1",
"links": [
... removed for brevity
]
}
If you do not use the Upsert-Mode
HTTP header and try to create a lookup code that already exists; you will get a 400
HTTP response with the following response:
{
"title": "Bad Request",
"status": "400",
"o:errorDetails": [
{
"detail": "Too many objects match the primary key oracle.jbo.Key[JD_TEST_LOOKUP_TYPE TEST10 3 0 null ].",
"o:errorCode": "25013"
}
]
}
Update Lookup Value
If you do not want to use the Upsert-Mode
HTTP Header, you can specifically update a lookup code as follows:
curl --location --request PATCH 'https://example.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes/CODE11' \
--header 'REST-Framework-Version: 4' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic 123=' \
--data '{
"LookupCode": "CODE11",
"Meaning": "CODE11 Meaning",
"Description": "CODE11 Description with Update",
"Tag": "N",
"EnabledFlag": "Y",
"StartDateActive": "2024-07-27"
}'
Notice that the lookup code to update has been added to the end of the URL path. e.g. commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes/CODE11
If successful, Fusion will respond with a 200
code along with the updated object:
{
"LookupCode": "CODE11",
"Meaning": "CODE11 Meaning",
"Description": "CODE11 Description with Update",
"EnabledFlag": "Y",
"StartDateActive": "2024-07-27",
"EndDateActive": null,
"DisplaySequence": null,
"Tag": "N",
"CreatedBy": "TEST1",
"CreationDate": "2024-07-27T17:52:48+00:00",
"LastUpdateDate": "2024-07-27T18:02:18+00:00",
"LastUpdateLogin": "1E3E8F0747F17E75E063C05F310ACD4E",
"LastUpdatedBy": "TEST1",
"links": [
... shortened for brevity
]
}
Delete Lookup Value
curl --location --request DELETE 'https://example.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes/CODE11' \
--header 'REST-Framework-Version: 4' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic 123='
You will receive a 204
response if the delete is successful.
Filtering, Selecting Columns, etc. on Fusion REST APIs
Take a look at this great post from Matt Paine for more information on filtering and selecting specific fields with Fusion REST APIs.
Describing Fusion REST APIs
You can use the 'describe' endpoint to get more information about the Fusion REST API you are interested in. The below CURL statement fetches details about the Common Lookups API.
curl --location 'https://example.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/commonLookups/describe' \
--header 'REST-Framework-Version: 4' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic 123='
This provides a large payload with details about every field related to lookup types and codes. The excerpt below describes the Meaning
field in the lookup codes object.
{
"name": "Meaning",
"type": "string",
"updatable": true,
"mandatory": true,
"inputRequired": true,
"queryable": true,
"allowChanges": "always",
"precision": 80
}
APEX Time
As we know, APEX allows connecting to external REST APIs using REST Data Sources. In version 23.2, APEX introduced a REST Data Source Type called 'Oracle Cloud Applications (SaaS) REST Service'. This allowed us to hook up to Fusion ERP Cloud REST APIs declaratively (i.e., no code).
Web Credential
The steps below assume you have a Service Account configured in Fusion and that it has access to maintain Common Lookups. We need an APEX Web Credential to store the Username and Password for the Service Account.
Create a REST Data Source
Navigate to Shared Components > REST Data Sources
Click the Create Button
- Click the Next Button
Note: I have used
:lookup_type
to create a Query String Parameter for the Lookup Type NameClick the Next Button
Enter the name of a Common Lookup Type you know exists
Click the Next Button
- Click the Next Button
Choose the features that your Fusion ERP REST API Supports. The Common Lookups REST API supports all of these features.
Click the Next Button
Select the APEX Web Credential you created for your Fusion Service Account
Click the Discover Button
APEX shows you the columns derived from the REST API. You may notice an additional column
APEX$ResourceKey
. This is a special column which is mapped to the selector@context.key
. In the Data Profile, this column is pre-selected as the Primary Key. When you create a Form or an Interactive Grid on this REST source, you must also specifyAPEX$ResourceKey
as the Primary Key in the Form / IG.Click Create REST Data Source to complete the process.
Create an Interactive Grid on the REST Source
This section assumes you already have a way of selecting a Lookup Type. You can easily create an Interactive Report on the REST API Common Lookup to allow users to query lookup types (as I did in the demo video at the start of this post). In the example below, the lookup type is saved in the page item P10_SELECTED_LOOKUP_TYPE
.
Navigate to your page and create a new region of type 'Interactive Grid'
Select Source
Location: REST Source
REST Source: The REST Data Source you created above
- Hide the column
APEX$RESOURCEKEY
- Make the column
APEX$RESOURCEKEY
the Primary Key
- Make the Grid Editable
- Assign the
lookup_type
parameter to page itemP10_SELECTED_LOOKUP_TYPE
. This page item contains the Lookup Type name for which you want to maintain lookup values.
- Make any other changes you want, hide columns, etc.
Behind The Scenes
Let's look at what APEX does behind the scenes when it interacts with the Fusion Cloud ERP REST APIs.
GET Records
In the APEX Demo video at the start of this post, I first queried my test Lookup Type, called JD_TEST_LOOKUP_TYPE
. If we look at the 'Web Service Activity Log' in APEX Builder, we can see the query string APEX Sent to Fusion:
Let's break down the query string parameters:
onlyData=true
- This tells Fusion to omit links in the response and return only the data.
fields=LookupType,Meaning,Description...
- This tells Fusion only to return enabled fields in the Interactive Grid. This excludes fields in your IG that you have marked as hidden.
q=(upper(LookupType)%20like%20'%25JD_TEST_LOOK%25')
- This tells Fusion to filter on the Lookup Type column and only return records that match the criteria. In this case, only return lookup types with a name like
%JD_TEST_LOOK%
.
- This tells Fusion to filter on the Lookup Type column and only return records that match the criteria. In this case, only return lookup types with a name like
limit=26
- This tells Fusion to return 26 records at a time. This would be higher if the 'Rows Per Page' were set higher in the Interactive Grid.
totalResults=true
- This tells Fusion to return the total count of rows that match the criteria so we can show page X of Y in the pagination area.
Create Update Delete
If the REST API supports it, APEX will also be smart when creating, deleting, and updating records. APEX will utilize the Batch Actions REST API to collect all of the changes (creates and updates in one batch and deletes in another batch) and POST
them to the REST resource root URL in one shot. Here is an example of calling the Batch Actions API to create two Common Lookup codes in one request.
curl --location 'https://example.fa.us2.oraclecloud.com/fscmRestApi/resources' \
--header 'REST-Framework-Version: 4' \
--header 'Content-Type: application/vnd.oracle.adf.batch+json' \
--header 'Upsert-Mode: true' \
--header 'Authorization: Basic 123=' \
--data '{
"parts": [
{
"id": "1",
"path": "/11.13.18.05/commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes",
"operation": "create",
"payload": {
"LookupCode": "CODE100",
"Meaning": "Code 100 Meaning",
"Description": "Code 100 Description",
"EnabledFlag": "Y"
}
},
{
"id": "2",
"path": "/11.13.18.05/commonLookups/JD_TEST_LOOKUP_TYPE/child/lookupCodes",
"operation": "create",
"payload": {
"LookupCode": "CODE101",
"Meaning": "Code 101 Meaning",
"Description": "Code 101 Description",
"EnabledFlag": "Y"
}
}
]
}
This avoids constant back-and-forth 'conversations' between APEX and the REST API, again improving performance for the end user. Read more on batch actions here.
Conclusion
If there is one thing you take away from this post, it should be that APEX makes working with Oracle Fusion Cloud ERP easy. With the native REST Source type 'Oracle Cloud Applications (SaaS) REST Service', you can create fully functional CRUD forms and Interactive Grids in minutes.