How to extract data from Oracle Fusion using REST APIs in OIC

Extracting data from Oracle Fusion is one of the common asks in the Integration world and people are confused about how to extract the data and send it to other applications.

So, in this article, I’m going to explain & demonstrate a way to extract data from Oracle Fusion using REST APIs via Oracle Integration.

In this complete article, I’ll execute the following one by one:

Extracting data from Oracle Fusion using REST APIs
Usage of REST API parameters and restrict payload to increase performance
Stage File and Notify actions in OIC

Prerequisites

Basic knowledge of REST APIs
Oracle SaaS Instance
Oracle Integration Cloud (OIC) Instance
REST Adapter Connection to Oracle Fusion in OIC

Usecase

Extract data from Oracle Fusion using REST APIs in OIC. There are many ways to extract data from Oracle Fusion like BI Reports, OTBI, BICC, HCM Extracts, web services, etc. In this article, I will be explaining extracting data by using REST APIs.

Limitation

Oracle REST APIs can return only 499 (<500) records in a single request. But in Fusion, there may be more than 499+ records so we need to fetch all records

REST API Parameters

offset: An integer value that specifies the index of the first item to be returned. The default value is 0.

limit: An integer value that specifies the paging size that the server uses for a client request. The default value is 25. The server might override this value to improve application performance. The maximum value for this parameter is 499. If you specify a value more than 499, only the first 499 records are returned and the hasMore parameter is set to true if more records exist.

hasMore: A Boolean value that indicates whether there are more items to be retrieved. Valid values are:

true which indicates there are more values to retrieve
false which indicates there are no more values to retrieve

fields: This parameter is used to extract only specific attributes from the response and restrict all other parameters.|

onlyData: This parameter is used to exclude all the links in the response and thereby reduce the size of the response.

links: This parameter is used to extract specific links from the response.

There are many other parameters and properties that you can set while calling the REST APIs. For more information click here. The best practice is to use the available parameters / and reduce the response payload as per the requirement so that performance can be improved.

Solution

We need to make use of Oracle REST APIs parameters like offset and limit and call REST API in the loop until hasMore attribute in the response is false.

Example :

Suppose you have 1100 invoices in the system, then the iteration should be like this.

offset=0,limit=499 hasMore =true

offset=499,limit=499 hasMore =true

offset=998,limit=499 hasMore =false (Stop the loop).

Now we are familiar with how to call the REST API with the necessary parameters, let’s start development by taking the invoice REST API as an example. For more information related to Oracle, invoice REST API click here.

Requirement

Extraction of all Invoice numbers and Currency from Oracle Fusion.

Analysis

As we need to extract full data we need to use the approach explained earlier in the solution section by using offset and limit and loop until hasMore attribute value in the response is false.
As the requirement is to extract only 2 fields we will make use of the fields parameter as explained earlier in the REST API Parameters section. We need to pass the “InvoiceNumber,InvoiceCurrency” value in the fields parameter in REST API so that in response only these two fields will be extracted.
There is no need to use any data from links of child response so we need to restrict extraction of it in response. For this, we need to use onlyData. We need to pass the “true” value in onlyData parameter.
There is no need to use any data from links of items response so we need to restrict extraction of it in response. For this, we need to use links. We need to pass the “self” value in the links parameter so that only links with the relationship of self are extracted.

Sample REST Request:

Request URL: https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/latest/invoices?fields=InvoiceNumber,InvoiceCurrency&onlyData=true&links=self&limit=1&offset=0

Operation: GET

Response

{
    "items": [
        {
            "InvoiceNumber": "test23",
            "InvoiceCurrency": "USD"
        }
    ],
    "count": 1,
    "hasMore": true,
    "limit": 1,
    "offset": 0,
    "links": [
        {
            "rel": "self",
            "href": "https://servername.fa.us2.oraclecloud.com:443/fscmRestApi/resources/11.13.18.05/invoices",
            "name": "invoices",
            "kind": "collection"
        }
    ]
}

In the above response, we can see only 1 record because the limit =1 and we can see only 2 attributes (InvoiceNumber, InvoiceCurrency) because we passed fields =InvoiceNumber,InvoiceCurrency, and we can see limited payload because we used onlyData=true & links=self

Now, let’s start building the OIC integration with the above REST API by using the required parameters and create the data file and send as an email attachment.

OIC Integration

Create a new Scheduled Orchestration style integration. I have named as InvoiceExtract.
Declare all necessary variables using Assign activity as shown below. I have taken the limit as 200 so each REST request will not contain more than 200 records response.

To store the response of REST API, we need to have to stage write action as shown below and hardcode the column headers. Here we have created a file with 2 fields as we need to extract only 2 fields (Invoice Number and Invoice Currency

We need to call the REST API in the loop so we need to declare a while with exit condition (hasMore!=’false’) as shown in the following image:

Inside the loop we need to call the Invoice REST API, So define REST connection as shown below by adding REST parameters and defining sample response payload (use sample response payload from above code section)

Map the REST request mapper as shown in the following screenshot.

Then use a stage write action to store the response as shown below by appending it to the file created in step 3

Before the end of the loop increase the limit by 200 and assign hasMore variable value from the REST response.

After the loop drop a Notify activity and assign the stage file reference in attachments.

Finally, the integration will look as shown below

Testing

Activate the integrations once all the configuration is completed.
Click on the Run icon and then click on Submit Now.
The integration will start running and check the integration execution details in the activity stream.
Once the integration completes an email will be sent with an attachment that contains the output file as shown below.

So we have successfully created and tested an integration that extracts data from Oracle Fusion using REST APIs.

You can look at our YouTube channel for Oracle Integration-related videos. Also, don’t forget to subscribe to our channel.

Enter your keyword

post