APEX: Filtering Fusion REST Integrations

On my Fusion extension journey I ran into an error or lack there of, that lead to a conversation and the discovery of the Fusion REST Filtering.

About this Post

REST and remote tend to be in tandem and retrieving large datasets from Fusion SaaS can result in timeouts at best and on an enterprise scale can overload some systems.

In such environments, the ability to query and filter data at source has many advantages; leaner datasets, lighter loads on connections & networks, and better resource consumption at both source and target are essential.

This article on filtering is the first in a series on achieving this.

Filtering Results

In my first article on extending Fusion, I harped on about the describe function(method) in the documentation and how useful it was. After reviewing the responses from the tasks I would invoke, I created a few REST Data Sources in my APEX app and had a good feeling as each test operation yielded the expected results and body documents.

• The module: HCM

• The purpose: Job Requisitions and its candidate details.

Each REST API was configured and tested and I would use APEX’s filtering support for Fusion APIs.

Two regions were added to the page with REST sources; Job Requisitions & Candidates.

From the screen, with a little more inspection, you will see that the Job Requisitions accepted parameters and the Candidates has the external filter option enabled. I will cover this in more details in Part II.

The third option is to use a Query By Example (QBE) which I’ve not had a use case for but will attempt in a future post.

APEX Data Filtering Runtime feature for Oracle Cloud SaaS applications

In the Working with REST Data Sources for Oracle Cloud SaaS Applications, REST Data Source Definitions for Oracle Cloud SaaS Apps section, we learn that the GET rows supports invokes with filtering, ordering, and pagination.

Data Filtering is further explained in the following section that describes the REST Data Source Runtime Features for Oracle Cloud SaaS Apps section.

As you can see in my configuration I use the Session State Substitution syntax format based on the documentation.

Supported Query Operators

My filter expression

RequisitionNumber=&P11_REQUISITION. AND JobTitle like &P11_TITLE_FILTER.

The API provider will document the complete list of supported query / filter operators.

• Common Query

• HCM Query

• Sales Query under the q section of support HTTP Methods

• SCM Query

The query capabilities are typically under the Learn More section in each Fusion REST API documentation set.

Conclusion

The APEX can extend Fusion SaaS (Oracle Cloud Applications) and provide support for connectivity, REST descriptions with the Oracle Cloud Apps REST Data Source Explorer utility app, highlighted in Fusion /describe post, and external filtering.

The external filtering, delegates all of the filtering operations to the SaaS host, freeing the APEX server to hopefully simple local post processing and presentation tasks.

This article is the first in a series that will explore the various filtering options and I hope you find this useful.

Let me know in the comments section.