DEV Community

Aimpex Resources Group LLC
Aimpex Resources Group LLC

Posted on

Oracle Fusion REST API Returns Only 25 Invoices? Page Through With limit, offset and hasMore

A REST call to Oracle Fusion Cloud ERP that returns exactly 25 rows is usually telling you something. For ERP Architecture teams at Aimpex Resources Group LLC, that number is a reason to check paging before anything else.

What does the problem look like?

A team built an AI agent on the Payables invoices REST resource. When it asked for all invoices on one day, it got exactly 25 records and treated them as the full list, though there were many more.

The team also saw different results from these two calls:

/fscmRestApi/resources/11.13.18.05/invoices?orderBy=InvoiceDate:desc
/fscmRestApi/resources/11.13.18.05/invoices?orderBy=InvoiceDate:asc
Enter fullscreen mode Exit fullscreen mode

Both problems come from the same place: how Fusion REST returns a collection one page at a time.

Why does the API stop at 25?

Oracle's REST docs explain it in the section on navigating a collection: "Each call to a collection resource returns a fragment of the collection (a single page). The default size of the page is 25 items." (Oracle Fusion Cloud REST: Navigate a collection)

The response tells you when there is more. The Get all invoices page describes hasMore this way: "If the value is true, then there are more resources to retrieve from the server." A response that looks like this is a first page, not the full answer:

{ "items": [ ... ], "count": 25, "hasMore": true, "limit": 25, "offset": 0 }
Enter fullscreen mode Exit fullscreen mode

So the tool read page one and stopped. The data was fine. The loop was missing.

How do you read every page?

Oracle's rule is short: "To retrieve the next page of the collection, increase the offset by the limit value." And: "to retrieve all the items, you must repeat the process until hasMore is false."

The invoices page adds one detail that is easy to miss. In the response, limit is "The actual paging size used by the server." That may not be the number you asked for. Moving offset by the server's limit, not your own, keeps the loop from skipping rows.

Why did asc and desc give different invoices?

Sort order matters while paging. Oracle's Fusion collection docs say: "When you specify the limit and offset parameters, the paginated result isn't ordered. To ensure consistent results, use the orderBy parameter to order the results." (Manage collections)

In the example above, orderBy=InvoiceDate:desc and InvoiceDate:asc return the first 25 rows of two different sort orders, so they show different invoices. On top of that, many invoices share the same InvoiceDate, so the order inside one date is not fixed. For paging, sort on a unique key such as InvoiceId. The same docs also warn: "If you update the collection resource between paging requests, the records displayed in each page may vary."

What does a full read look like in Python?

The invoices page lists the query parameters this script uses: q for the filter, orderBy, fields, onlyData, limit and offset. Its example request uses basic authentication with curl -i -u "<username>:<password>", so the script does the same with requests.

import os

import requests

BASE_URL = os.environ["FUSION_BASE_URL"]  # for example https://servername.fa.us2.oraclecloud.com
RESOURCE = "/fscmRestApi/resources/11.13.18.05/invoices"

session = requests.Session()
session.auth = (os.environ["FUSION_USER"], os.environ["FUSION_PASSWORD"])


def get_invoices_for_day(day, page_size=200):
    rows, offset = [], 0
    while True:
        resp = session.get(
            BASE_URL + RESOURCE,
            params={
                "q": f"InvoiceDate='{day}'",
                "orderBy": "InvoiceId:asc",
                "fields": "InvoiceId,InvoiceNumber,InvoiceDate,Supplier,InvoiceAmount",
                "onlyData": "true",
                "limit": page_size,
                "offset": offset,
            },
            timeout=120,
        )
        resp.raise_for_status()
        body = resp.json()
        rows.extend(body["items"])
        if not body["hasMore"] or body["count"] == 0:
            return rows
        offset += body["limit"]  # the page size the server actually used


if __name__ == "__main__":
    invoices = get_invoices_for_day("2026-11-03")
    print(len(invoices), "invoices")
Enter fullscreen mode Exit fullscreen mode

What each part does:

  • q filters on InvoiceDate, which is listed as a queryable attribute on the invoices page. Check the date format against your own release before relying on it.
  • orderBy=InvoiceId:asc gives every page a fixed order, so no row moves between pages.
  • fields and onlyData cut the payload down to the columns you need. Per the docs, onlyData filters the payload "to contain only data (no links section, for example)."
  • offset += body["limit"] follows Oracle's paging rule and uses the server's page size.
  • The count == 0 check is a guard so the loop can never spin on an empty page.

If you need a total before you start, add totalResults=true. The invoices page says the response then includes the "estimated row count", so treat it as a hint and still loop on hasMore.

How do you test it before production?

Run the loop against a day where you already know the invoice count from the Payables screens. Then check three things:

  • The number of rows matches that known count.
  • No InvoiceId appears twice.
  • The last response has hasMore set to false.

If the count is short, look at the q filter first. If there are duplicates, look at the sort key.

Sources


Founded in 2025 and based in Lincoln, NE, Aimpex Resources Group LLC delivers ERP Architecture, Supply Chain Analytics and IT Staffing Solutions. Its architects work across Oracle Cloud ERP, SAP S/4HANA and Microsoft Dynamics, including the integrations that pull Payables data into reports and tools. Teams building on Oracle Fusion REST can learn more at aimpexresources.com.

Top comments (0)