# Managing Fusion Cloud ERP Data from APEX with No Code # 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](https://docs.oracle.com/en/cloud/saas/applications-common/24c/farca/api-common-lookups-common-lookups-11.13.18.05.html). ![Fusion ERP REST API for Common Lookup Codes](https://cdn.hashnode.com/res/hashnode/image/upload/v1722103179648/7faad318-446a-43c0-abc3-2c23eeef011b.png align="center")
😠
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)](https://support.oracle.com/epmos/faces/DocContentDisplay?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` ```bash 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. ```json { "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: ```json { "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: ```bash 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: ```json { "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 ```bash 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](https://hashnode.com/@mattpaineintexas) from [Matt Paine](https://hashnode.com/@mattpaineintexas) 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. ```bash 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. ```json { "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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722119855944/77be94c8-cfb7-43c7-a40c-16ea2f3a8ae1.png align="center") ## 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722119929410/d391a48f-d965-47e4-8749-97fb5ac5cba9.png align="center") * Click the **Next** Button ![Create an Oracle APEX Fusion SaaS REST Source - 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1722120025365/fe475713-c7fc-49d1-97fb-cabc83036ca8.png align="center") * 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722120123367/409959e1-5bb6-40fc-8231-ac6931065f8d.png align="center") * Enter the name of a Common Lookup Type you know exists * Click the **Next** Button ![Create an Oracle APEX Fusion SaaS REST Source - 4](https://cdn.hashnode.com/res/hashnode/image/upload/v1722120252193/b11d8541-54b1-4403-88d8-2b9aab711eb9.png align="center") * Click the **Next** Button ![Create an Oracle APEX Fusion SaaS REST Source - 5](https://cdn.hashnode.com/res/hashnode/image/upload/v1722120285017/daaf7d10-6e0c-4862-acff-2c9c06a4821e.png align="center") * 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722120354908/32730131-d442-478e-9b31-ab5f0f01b930.png align="center") * 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722122686727/2d805406-396d-41fa-9580-6e113f491de8.png align="center") * 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](https://docs.oracle.com/en/cloud/saas/applications-common/24c/farca/api-common-lookups-common-lookups-11.13.18.05.html) 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722171839188/c0123044-dae8-4baa-b6b8-a83c8787d126.png align="center") * Hide the column `APEX$RESOURCEKEY` ![Create APEX IG on Fusion REST Data Source - 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1722179642797/e48a9789-3de2-4f99-9117-bbbd17e0e14b.png align="center") * Make the column `APEX$RESOURCEKEY` the Primary Key ![Create APEX IG on Fusion REST Data Source - 3](https://cdn.hashnode.com/res/hashnode/image/upload/v1722179725332/17f6568f-c35c-4ef5-8c56-5b58afe64155.png align="center") * Make the Grid Editable ![Create APEX IG on Fusion REST Data Source - 4](https://cdn.hashnode.com/res/hashnode/image/upload/v1722179752940/617a67b7-74fd-4217-8e73-47711bc28010.png align="center") * 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722190741317/930bd56d-c496-4409-befa-deaf5730cb94.png align="center") * 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1722182468317/6033cc49-4eca-4964-91f6-84accd70e9f4.png align="center") 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](https://docs.oracle.com/en/cloud/saas/sales/faaps/Perform_Batch_Actions.html) 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. ```json 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](https://docs.oracle.com/en/cloud/saas/sales/faaps/Perform_Batch_Actions.html). # 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 * [Streamlining ERP Cloud REST Queries](https://jmjcloudblog.hashnode.dev/streamlining-erp-cloud-rest-queries) * [Flexing Fusion Apps Filters](https://diveintoapex.com/2024/04/19/flexing-fusion-apps-filters/) * [CRUD REST Service for Lookups in Fusion (Doc ID 2751758.1)](https://support.oracle.com/epmos/faces/DocContentDisplay?id=2751758.1)