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!

https://youtu.be/2pDcRkVT6do

Another video showing the same lookup as viewed from Fusion:

https://youtu.be/rzHmBnG1g6Y

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.

Fusion ERP REST API for Common Lookup Codes

😠

Unfortunately, the Oracle Fusion REST API documentation is not very good. The sample payloads are essentially empty stubs, and there are no examples.

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.

ℹ

I am using Basic Authentication to authenticate the REST APIs. This involves creating a Fusion ERP Service account with access to manage Common Lookups.

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"
}'

💡

You may notice the 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

🥳

Now that we understand how the REST APIs work let's get to the fun stuff.

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.

APEX Web Credential for the Fusion Service Account

Create a REST Data Source

Navigate to Shared Components > REST Data Sources
Click the Create Button

Create an Oracle APEX Fusion SaaS REST Source - 1

Click the Next Button

Create an Oracle APEX Fusion SaaS REST Source - 2

Note: I have used :lookup_type to create a Query String Parameter for the Lookup Type Name
Click the Next Button

Create an Oracle APEX Fusion SaaS REST Source - 3

Enter the name of a Common Lookup Type you know exists
Click the Next Button

Create an Oracle APEX Fusion SaaS REST Source - 4

Click the Next Button

Create an Oracle APEX Fusion SaaS REST Source - 5

Choose the features that your Fusion ERP REST API Supports. The Common Lookups REST API supports all of these features.
Click the Next Button

Create an Oracle APEX Fusion SaaS REST Source - 6

Select the APEX Web Credential you created for your Fusion Service Account
Click the Discover Button

Create an Oracle APEX Fusion SaaS REST Source - 7

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 specify APEX$ResourceKey as the Primary Key in the Form / IG.
Click Create REST Data Source to complete the process.

⏰

If you already have the Web Credential and know your instance's URL, the process takes less than 5 minutes!

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

Create APEX IG on Fusion REST Data Source - 1

Hide the column APEX$RESOURCEKEY

Create APEX IG on Fusion REST Data Source - 2

Make the column APEX$RESOURCEKEY the Primary Key

Create APEX IG on Fusion REST Data Source - 3

Make the Grid Editable

Create APEX IG on Fusion REST Data Source - 4

Assign the lookup_type parameter to page item P10_SELECTED_LOOKUP_TYPE. This page item contains the Lookup Type name for which you want to maintain lookup values.

Create APEX IG on Fusion REST Data Source - 5

Make any other changes you want, hide columns, etc.

⏰

Creating the Interactive Grid from the REST Source takes about 5 minutes. This means it takes only 10 minutes (and no code) to create a fully functional CRUD Interactive Grid on the Fusion Common Lookup REST API!

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:

APEX Web Service Activity Log showing REST API Call

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

❗

It is very important for APEX to be able to pass Interactive Grid values like the fields it wants returned, filter criteria, rows per page, sort order, etc. This significantly improves performance by reducing the payload size and pre-filtering the result set before returning it to APEX.

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.

Managing Fusion Cloud ERP Data from APEX with No Code

Comments

More from this blog

Inside an APEX 26.1 AI Interactive Report Request

Using an APEX AI Agent to Turn Purchase Orders into JSON

APEX AI Agent Handlers: Intercepting & Updating Requests and Responses

APEX AI Agent Logging with Request & Response Handlers

AI Agents Need Boundaries, Not Bigger Prompts

Introduction

Use Case

Demonstration

REST APIs

Documentation

CURL Examples

Create Lookup Value

Update Lookup Value

Delete Lookup Value

Filtering, Selecting Columns, etc. on Fusion REST APIs

Describing Fusion REST APIs

APEX Time

Web Credential

Create a REST Data Source

Create an Interactive Grid on the REST Source

Behind The Scenes

GET Records

Create Update Delete

Conclusion

Read More

Command Palette

Comments

More from this blog

Introduction

Use Case

Demonstration

REST APIs

Documentation

CURL Examples

Create Lookup Value

Update Lookup Value

Delete Lookup Value

Filtering, Selecting Columns, etc. on Fusion REST APIs

Describing Fusion REST APIs

APEX Time

Web Credential

Create a REST Data Source

Create an Interactive Grid on the REST Source

Behind The Scenes

GET Records

Create Update Delete

Conclusion

Read More