OIC - How to extract large data from Oracle Fusion using REST APIs | How to overcome REST APIs limitation which can return only 499 records in a single request

Usecase: Here, we will get Rest data in 500 records size chunks using limit and offset.

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

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.

Sample Request URL to test rest: 

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"
        }
    ]
}

Implementaion steps:

1. Create a new Scheduled integration and assign all the required variables offset=0, limit= 500, hasMore="true", onlyData="true", links="self", fields="InvoiceNumber,InvoiceCurrency"
2. Take a stage and write the headers
3. Take a while and loop till hasMore != "false"
4. Configure rest endpoint to get the invoice data with the qurey params
1. URI: fscmRestApi/resources/latest/invoices
2. Query params: fields,onlyData,links,limit, offset
3. Json response 
4. Map the fields
5. Take a stage and write invoices
6. Take a assign and update: 
1. hasMore= from the inboice response hasMore, 
2. offset = $offset + 500
7. Take a ftp adapter and configure to write final response.

Detailed steps with screenshots:

Integration flow:

Assign globals

Write headers

Loop 

Get rest call

Write to stage.

Update variable

Write to ftp directory

Generated file