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.
😠
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.
Create a REST Data Source
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.
- Hide the column
APEX$RESOURCEKEY
- Make the column
APEX$RESOURCEKEY the Primary Key
- 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.
- 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:
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: --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.
Read More
评论
发表评论