Most customers use Oracle APEX to build extensions for Fusion Applications because it provides a fast, low-code, and secure way to add new functionality without modifying the core Fusion system. Using APEX, they can easily create custom dashboards, reports, and workflows that meet their unique business needs. To achieve this, they often create REST Data Sources in APEX that connect to Fusion’s REST APIs, allowing them to seamlessly fetch and update data from Fusion while keeping everything integrated and up to date.
Working with REST APIs in Oracle Fusion Applications can feel like going on a little adventure. At first, it looks easy, you grab the endpoint, plug it in, run a GET or POST request, and you’re done. But as your project grows and new requirements come up, you often end up back in the documentation, hunting for the right API like trying to find a missing puzzle piece.
The tricky part is in the details. Things like authentication tokens, user roles, and payload formats can easily cause problems if they aren’t set up just right. Sometimes it takes testing, re-testing, and a bit of patience to get everything working smoothly.
Thankfully, Oracle APEX now offers APIs for all Fusion pillars in the form of SQL files (created from the JSON supplied by Fusion) that can be imported into the REST Catalog in Oracle APEX, simplifying the work for APEX developers. This functionality enables developers to configure REST Data Sources with the appropriate parameters and authentication significantly more quickly.
All you need to do is start by identifying the Fusion Application release version you need to work with and then choose the Catalogs of a pillar you want to use in your APEX Workspace, and you’ll be ready to create REST Data Sources.
Pre-Requisites
The pre-requisites for importing FA REST Catalogs into Oracle APEX are as follows:
- An Oracle APEX Workspace that is linked to a Fusion environment. This can be done in a simple 3-step process introduced in Oracle APEX 24.2. This is necessary as we will utilise the web credentials created during the application creation process. This process is can be referred from here.
- A Catalog Group must be present in the Oracle APEX workspace, which will be used to map the FA Catalog during the upload process.
- Download the latest FA Catalog from the GitHub location here, where the catalogs are present based on the Fusion version (APEX’s Fusion REST Source Catalogs)
Catalog Group
A Catalog Group is used to organize catalogs. For this blog, we are using them to group pillar catalogs your import by Fusion Release version i.e., 25B, 25C, etc.,
To create a Catalog Group in Oracle APEX Workspace, please follow the steps outlined below:
- Navigate to Workspace Utilities > REST Source Catalogs
- Select Manage Catalog Groups from the sidebar on the right.
- Next, create catalog group by clicking on Create Group.
- Please enter a Name and Description for your Catalog Group.
Uploading FA REST Catalogs
Oracle APEX offers developers two approaches to import the REST Catalog into the APEX Workspace:
Connect to your Oracle APEX Parsing schema using sqlcl
SQLcl –> This is the recommended approach for developers and administrators, as typically the FA REST Catalog is substantial in size since it encompasses all the REST APIs of a specific Fusion pillar. To import the FA REST Catalog into your APEX Workspace using sqlcl, follow the steps below:
Run the following PL/SQL block to configure the Workspace and assign the Catalog Group that the FA REST Catalog will be linked to
SQL> begin
--This should be the workspace where you want to import the catalog
apex_util.set_workspace('WKSP_FA');
--Oracle APEX catalog group to which the imported REST Catalog is to be mapped
apex_application_install.set_rest_source_catalog_group('25C');
end;
/
You can now choose the Fusion pillar you wish to import into Oracle APEX by executing the command below.
SQL>@/{UnzipDirectoryPath}/{ChooseThePillarToUpload}
Example: SQL>@/Users/{user}/Desktop/APEXInstalls/FACatalog/25C/25C_grc.sql
APEX UI
- To import the FA REST Catalog into your Oracle APEX Workspace, go to Workspace Utilities > REST Source Catalogs.
- In this section, you can import the designated FA pillar file that includes all the REST APIs for a particular pillar offered by FA.
- To import a catalog, click on Import Catalog located in the right-side pane of your REST Source Catalogs page.
- Next, select the .sql file corresponding to the FA Pillar you prefer, click Open, and then click Next twice.
- On the Import REST Catalog screen, choose the catalog group to which you wish to associate the FA REST Source Catalog that is being imported, and then click on Import REST Catalog.
Considering the quantity of APIs available in the FA REST Catalog, the import process will require a few minutes. After completion, you will find them listed under Workspace Utilities > REST Source Catalogs together with all the APIs.
Creating REST Data Source from a REST Source Catalog
- Go to the application in which you wish to create a REST Data Source from a REST Source Catalog, then proceed to the REST Data Source creation screen (Application XXX > Shared Components > Data Sources (REST Data Sources)) and click on Create.
- Next, select the option From a REST Source Catalog
This will display a list of all FA Source Catalogs by pillar that have been imported into your Workspace, including their respective versions.
- You can click on Browse to pick from the list of APIs available for that FA pillar. In this section, you can either scroll through the available APIs or simply enter your API in the search bar. You can also select or add multiple APIs of your choice as shown below to create multiple REST Data Sources
- After making your selection, click Next to proceed to Authentication. Here, you will need to select the Web Credentials that you have previously configured (as part of Pre-Requisites) to connect to the corresponding Fusion instance, and then click on Create REST Data Sources.
- As shown in the snippet above, the Fusion Host or Base URL it points to is facatalog_restapi_baseurl.example.com, which serves as the default configuration. We can now take advantage of the Flexible Remote Server feature (introduced in Oracle APEX 24.2 release) by modifying the Base URL. With this approach, we can remap the generic remote server name from the REST catalog to the customer’s FA domain Base URL in a single, central place.
- To modify the Remote Server, you can go to Workspace Utilities > Remote Server or select the REST Data Source that has been created and click on the edit icon next to Remote Server.
- You can then use the below code in the PL/SQL Code section located under Advanced. Then, enter the specified procedure name in the Configuration Procedure section and click on Apply Changes.
procedure my_server_config(
p_info in apex_plugin.t_remote_server_info,
p_config out apex_plugin.t_remote_server_config )
is
begin
p_config.base_url := 'https://{YourFusionHost}';
end;
- Repeat the above procedure for adding PLSQL code block to change the remote server for the authentication server as well, which you can do from here
You can now verify the functionality of the REST Data Source to ensure it operates as intended.
Additional Info
- Linking between Catalog & REST Data Source
The FA Source Catalog and REST Data Source are not directly linked; after creating REST Data Sources from a specific Catalog, they will not be hard linked. This ensures that you won’t encounter any problems later if someone decides to remove the FA Catalog from your workspace. In fact, it provides you with the option to recreate a FA Source Catalog for that specific API from the REST Data Source.
- Updating Data Profile
Your REST Data Source will reference the configured Endpoint rather than the REST Source Catalog. Therefore, if there are any modifications to the REST API attributes from the Fusion endpoint, you can incorporate those changes by refreshing the Data Profile here.
Furthermore, if your REST Data Source contains 100 attributes and you wish to exclude some of them, you can set them to be invisible at the Data Profile level. This way, they will not appear on your application pages, eliminating the need for you to manually delete, comment out, or hide them.
