How to use Oracle Fusion Rest API's

Oracle Fusion Applications provide a comprehensive set of REST APIs that act as gateway for essential operations: Getting data out (GET), Creating new records (POST), Updating existing data (PATCH/PUT), and Deleting (DELETE).

While Oracle's documentation serves as a necessary foundation, the sheer number of possible use cases and data combinations means it can't cover every specific scenario. It is up to us, the developers and integrators, to master the nuances of these APIs to extract maximum value.It is on us to explore them and use for our purpose. 

In this Post we will focus on how we can effectively use GET operation for Oracle Fusion rest API's.

To execute precise and efficient data requests, we need to understand the powerful query parameters available for any Fusion resource. We will be exploring the following key parameters in this post, using the purchaseOrders REST API for our examples:

1. q - Query Filter (The WHERE Clause)

2. finder - Predefined Optimized Searches

3. expand - Nested/Related Data Retrieval

4. onlyData - Suppressing Metadata( Child Links)

5. hasMore - Pagination Indicator

6. offset - Controlling Paging

7. limit - Controlling Paging

8. totalResults - Getting the Total Count

9. fields - Data Projection (Selecting specific columns)

1. q - query parameter

The q parameter is the most fundamental and frequently used method for filtering our data. It acts exactly like the WHERE clause in a traditional SQL query, allowing us to define one or more expressions to restrict the resource collection returned.

The resource collection will be queried using the provided expressions. The value of this query parameter is one or more expressions

Format: ?q=expression1;expression2

Let us take example of purchase order.

We want to query by purchase order number.

Example : https://abcd-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=OrderNumber=US164932

Now let us see how we can add more query parameters.
https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=SupplierId=300000047507499;StatusCode=CLOSED

2. Finder

Used as a predefined finder to search the collection.

Format: ?finder=<finderName>;<variableName>=<variableValue>,<variableName2>=<variableValue2>

The following are the available finder names and corresponding finder variables:

• PrimaryKey: Finds a purchase order according to the purchase order identifier.
  Finder Variables:
  • POHeaderId; integer; Value that uniquely identifies a purchase order.
• findByIntent: Finds purchase orders according to the intent.
  Finder Variables:
• Intent; string; Value that uniquely identifies the intent. Valid values are APUser, POUser, LOBUser, SSPUser, and NonAgentUser.
• POHeaderId; integer; Value that uniquely identifies the purchase order.
• SysEffectiveDate; object.

Let us take example for primary key finer with POHeaderID

3. Expand

By default, an Oracle Fusion REST endpoint, such as /purchaseOrders, is designed to return only the primary or Header details of the resource. This minimizes the initial payload size.

However, in real-world integration, we frequently need the related Child resources (like lines, distributions, or attachments) in the same API call. This is where the powerful expand parameter comes in.

The expand parameter tells the server to fetch and embed related resources directly within the main resource's JSON response, saving us from making multiple round-trip API calls.

Note: In our example we are  using query parameter for one Po to restrict the result : POHeaderId= 4, it is not mandatory

We can see in te below screenshot that lines data of the queried po also came in response:

expand=lines

We can use expand=all as well which will give us expanded response with lines, dff etc.

We've learned that expand=lines brings the immediate child elements (the PO lines) into our response. However, often we need data that is nested even deeper, such as the Schedules associated with each PO Line. 

To selectively retrieve data that is nested multiple levels deep—for example, getting the Schedules (a child of a Line, which is a child of the Header)—we need to use dot notation to define the path in the expand parameter.

Hierarchy Example: 

PurchaseOrder Header -> lines -> schedules

Goal: Get the Purchase Order Header, its Lines, and the Schedules for each line in a single, efficient call.

Syntax for Deep Expansion: We specify the parent resource (lines) and then the child resource (schedules) separated by a dot.

expand=lines,lines.schedules

https://abcd-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=POHeaderId= 4&expand=lines,lines.schedules

If we want distributions as well which is child of schedules then we can simply write like below and we will get expanded response of lines, schedules and distributions:

https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=POHeaderId= 4&expand=lines.schedules.distributions

4. onlyData

The response payload gets filtered in order to contain only data (eg. no links section for child elements )

After we use onlyData=true we get respone like below.

If we do not use onlyData=true we get the default response like below.

5. Hasmore

By default Oracle Fusion Rest API's returns 25 records in the result and if there are more records in the result then it mentions hasMore as true like below:

We can use this along with limit and offset to iterate to get remaining data untill it becomes false.

6. offset

We use this to iterate over to get the remaining records, the offset value has to be changed in every iteration and it becomes the next value to start from and get the reults with the specified limit.
By default the offset will be 0 to fetch records from starting.

We can use offset like below:

7. limit

This parameter restricts the number of records returned inside the resource collection. If the limit exceeds the record count then it will only return the available records.
It is useful in iterating to get the remaining data in subsequent calls.
We can specify limit upto 500 to bring records in one call.

If we use limit value mre than 500, for eg. 1000 then Oracle Fusion makes it to 500 by default as we can see in below example.

8. Total results

By default the value is set to false.
The totalResults provide us the total count of records we have for the resource we are making the call for.
If we set the value to true in the request then we get the totalResults in the response:

totalResults=true

As mentioned in the below screenshot.

9. Fields

This parameter filters the resource fields. Only the specified fields are returned.

It cannot be combined with expand query parameter. If both are provided, only fields will be considered.

Format: ?fields=Attribute1,Attribute2

Format for fields in child resource: ?fields=Accessor1:Attribute1,Attribute2

Now lets see how we can use child elements along with the parent element , like few elements from line and few from headers.

We can request like below:

https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?fields=OrderNumber,Supplier;lines:Description,LineType,LineNumber

As we can see in the blow screenshot,
OrderNumber and Supplier fields are from headers and Description,LineType and LineNumber are from line level.

Now that we have seen all the different types of parameters we can easily combine these and use in our rest calls as required.

Here is one example to fetch all POs for supplier EDF, selected fields from headers and lines, get only data to remove child links, set limit of 200 records per page and display totalResults

example request:

https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=Supplier=EDF &fields=OrderNumber,Supplier;lines:Description,LineType,LineNumber&onlyData=true&limit=200&totalResults=true

The resonse payload will look like below: