REST API for Fusion Cloud Transportation and Global Trade Management Business Object Resources

Manage Collections

A collection resource represents a list of items, such as locations, orders or shipments. You can sort, filter, and paginate your collection resources to make large result sets easier to use and consume. For instance, you might only want to select a subset of information from a customer collection resource, instead of the entire data set. You can manage data returned from a collection resource using the following actions:

Querying

Currently there are the following options for requesting a number of resources which match certain criteria.

Specifying the q query parameter on the URL for a GET.
Advanced query
Specifying a Saved Query GID on a custom-actions/savedQueries for a GET.
Bulk Query which returns a collection of primary keys rather than complete resources.

Access to the various query actions is given by default via the corresponding resources "{resource} - View" role (or "{resource} Configuration - View" role for Power Data resources). See Access Control Lists for details.

Using the q Query Parameter with GET

The q query string specifies one or more attribute and operation predicates to match certain resources. Multiple predicates can be combined with the normal boolean operators AND, OR and NOT. The string must be URL encoded. The following examples show some of the ways to filter the results when retrieving resources:

To test whether a field's value starts with "PHL"
- locationXid sw "PHL"
For testing if a value exists use "present":
- locationXid pr
- not locationXid pr
To create complex query expressions, use and and or, along with the matching set of parentheses:
- (not descriptionText pr) or ((revenue gt "300000 USD") and (weight ge "80 LB" and weight le "100 LB"))

Note:

Dates and units of measure values should always be specified within quotes. For example, "80 LB". If quotes are not used then there may be ambiguity in the query syntax. For example, when using the in operation it may conflict with the IN unit of measure (inches).

An example URL to query for any locations resource where the ID starts with PHL would be:

curl -u username:password \
-X GET https://servername.oraclecloud.com/logisticsRestApi/resources/v2/locations?q=locationXid%20sw%20%22PHL%22

Child attributes can also be used to query on resources. This URL queries for any locations resource which contains a reference number resource with a locationRefnumValue attribute which ends with "TEST" and does not contain a remarks resource with a remarkText attribute which contains "TEST".

curl -u username:password \
-X GET https://servername.oraclecloud.com/logisticsRestApi/resources/v2/locations?q= refnums.locationRefnumValue%20ew%20%22TEST%22%20and%20not%20remarks.remarkText%20co%20%22TEST%22

Child attributes can be used multiple levels down. This URL queries all shipments resources that contain an orderReleaseGid of "GUEST.MYORDER".

curl -u username:password \
-X GET https://servername.oraclecloud.com/logisticsRestApi/resources/v2/shipments?q= stops.details.sShipUnits.lines.orderReleaseGid%20eq%20%22GUEST.MYORDER%22

The following table lists REST data types and the valid operators that you can use in query parameter strings.

Operator	Description	Behavior
`eq`	equal	The attribute and operator values must be identical for a match.
`ne`	not equal	The attribute and operator values are not identical.
`co`	contains	The entire operator value must be a substring of the attribute value for a match.
`sw`	starts with	The entire operator value must be a substring of the attribute value, starting at the beginning of the attribute value. This criterion is satisfied if the two strings are identical.
`ew`	ends with	The entire operator value must be a substring of the attribute value, starting at the end of the attribute value. This criterion is satisfied if the two strings are identical.
`pr`	present (has value)	If the attribute has a non-empty or non-null value, or if it contains a non-empty node for complex attributes, there is a match.
`gt`	greater than	If the attribute value is greater than the operator value, there is a match. The actual comparison is dependent on the attribute type. For string attribute types, this is a lexicographical comparison, and for DateTime types, it is a chronological comparison. For integer attributes, it is a comparison by numeric value. Boolean and Binary attributes SHALL cause a failed response (HTTP status code 400).
`ge`	greater than or equal two	If the attribute value is greater than or equal to the operator value, there is a match. The actual comparison is dependent on the attribute type. For string attribute types, this is a lexicographical comparison, and for DateTime types, it is a chronological comparison. For integer attributes, it is a comparison by numeric value. Boolean and Binary attributes SHALL cause a failed response (HTTP status code 400).
`lt`	less than	If the attribute value is less than the operator value, there is a match. The actual comparison is dependent on the attribute type. For string attribute types, this is a lexicographical comparison, and for DateTime types, it is a chronological comparison. For integer attributes, it is a comparison by numeric value. Boolean and Binary attributes SHALL cause a failed response (HTTP status code 400).
`le`	less than or equal two	If the attribute value is less than or equal to the operator value, there is a match. The actual comparison is dependent on the attribute type. For string attribute types, this is a lexicographical comparison, and for\| DateTime types, it is a chronological comparison. For integer attributes, it is a comparison by numeric value. Boolean and Binary attributes SHALL cause a failed response (HTTP status code 400).
`in`	match values in a list	The attribute value must be one of the values in the list. The list is specified as comma-separated values between square brackets. For example, ["GID1", "GID2", "GID3", ...]. The number of values in the list is limited by the database and is normally 1,000.

Boolean Operators

Operator	Description	Behavior
`and`	Logical "and"	The filter is only a match if both expressions evaluate to true.
`or`	Logical "or"	The filter is a match if either expression evaluates to true.
`not`	"Not"function	The filter is a match if the expression evaluates to false.

Boolean properties are stored in the database columns as "Y" which corresponds to true or "N" which corresponds to false. As and when new Boolean columns are added to existing tables, it is often convenient to allow NULL values in the column and assume that, depending on the use case, a NULL should be taken as true or false.

However, boolean attributes should only be used in a "q" query string if it is guaranteed that the underlying columns do not have NULL values, otherwise some records may not be selected.

Advanced Query

In some cases it will not be appropriate to specify a query string on the URL for a GET. For example, the criteria values may contain sensitive values or the string itself may be quite long and potentially longer than the URL size limit. URLs also have many reserved characters that are not reserved when present in a JSON string. In these scenarios the query definition should be submitted as a POST to a custom-actions/queries resource.

In addition to the "q" query string approach, an existing Saved Query can also be passed in the message and executed, with optional bind parameters to select a collection of resources.

The following Media Type should be sent to identify the message body content type:

application/vnd.oracle.resource+json; type=query-def

Also, in a future release it will be possible to persist the query definition but for this release it will only be used to be executed and return a collections resource containing any matching resources.

Please specify the following 'Prefer' header parameter to explicitly declare that the query should be executed:

Prefer: transient

If no Prefer header is specified then for the current release this will default to execute the query. However, when persistence of query definitions is implemented the default process will be to persist the definition and so it's recommended to explicitly use the Prefer header when execution is required.

Query String Execution

For example, the following message shows the Advanced Query approach to query for any locations resource where the ID starts with PHL:

POST /logisticsRestApi/resources/v2/custom-actions/queries/locations
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "q": "locationXid sw \"PHL\""
}

Note:

The format of the message is a JSON object and so any quotes surrounding values must be escaped with the '\' character.

Saved Query Execution

A previously created Saved Query can be executed by specifying the GID in the message body. For example:

POST /logisticsRestApi/resources/v2/custom-actions/queries/locations
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "copiedFrom": "GUEST.MY_LOC_SQ"
}

The requirements are that the saved query "GUEST.MY_LOC_SQ" exists, is associated with the LOCATION data query type and contains a SQL syntax query string. The SELECT statement in the saved query must only select the primary key columns for the corresponding resource.

In future, when the capability exists to create and update Advanced Query definitions via the REST API, the "q" query string syntax will also be supported as a Saved Query definition.

Saved Queries with bind parameters are also supported. Bind parameters can be positional or named and are passed in the "parameterValues" property map where the map key is the position or name of the bind parameter. Positional Bind Parameters must be used when the Saved Query SQL syntax uses the "?" bind syntax. For example, we may have a saved query SQL string as follows:

SELECT LOCATION_GID FROM LOCATION WHERE LOCATION_NAME LIKE ? AND DOMAIN_NAME = ?

The corresponding parameter for LOCATION_NAME would be considered position 1 and for DOMAIN_NAME would be position 2. The Advanced Query message would then become:

POST /logisticsRestApi/resources/v2/custom-actions/queries/locations
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "copiedFrom": "GUEST.MY_LOC_SQ",
 "parameterValues" : {
     "1" : "PHL%",
     "2" : "GUEST"
  }
}

Named bind parameters use a unique name to identify the bind parameter. For example, we may have a saved query GUEST.MY_LOC_POS_SQ defined with the SQL string as follows:

SELECT LOCATION_GID FROM LOCATION WHERE LOCATION_NAME LIKE :locName AND DOMAIN_NAME = :domain

The Advanced Query message would then become:

POST /logisticsRestApi/resources/v2/custom-actions/queries/locations
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "copiedFrom": "GUEST.MY_LOC_POS_SQ",
 "parameterValues" : {
     "locName" : "PHL%",
     "domain" : "GUEST"
  }
}

Named bind parameters have the additional capability to use the same name in multiple places in the Saved Query string and only require a single parameter values to be passed. For example, the saved query GUEST.MY_LOC_NAMED_SQ defined with the SQL string could use the following syntax:

SELECT LOCATION_GID FROM LOCATION WHERE POSTAL_SPCL_GID = :spcl AND RAIL_SPCL_GID = :spcl

The Advanced Query message would then become:


POST /logisticsRestApi/resources/v2/custom-actions/queries/locations
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "copiedFrom": "GUEST.MY_LOC_NAMED_SQ",
 "parameterValues" : {
     "spcl" : "GUEST.MY_SPCL_GID"
  }
}

Using the custom-actions/savedQueries Action

Use the savedQueries custom action when you want to execute an existing Saved Query already defined in the target application. The saved query ID is a predefined query associated with a resource and must match the target resource name in the path. It must also only select the primary key columns for the associated resource. For example, use the MySavedQueryGid to get a collection of locations which we've used in the following cURL command example:

curl -u username:password \
-X GET https://servername.us2.oraclecloud.com/logisticsRestApi/resources/v2/custom-actions/savedQueries/locations/MySavedQueryGid

Note:

Currently, only UI Saved Queries are supported. Integration Saved Queries may be supported in a future version.

The savedQueries action now supports positional bind parameters which are declared using the "parameterValue" query parameter. Multiple values are supported and the corresponding position is the position the values appear in the query URL. For example, using the same saved query GID defined above with positional binding, the equivalent savedQueries action URL would be:

curl -u username:password \
-X GET https://servername.us2.oraclecloud.com/logisticsRestApi/resources/v2/custom-actions/savedQueries/locations/GUEST.MY_LOC_POS_SQ?parameterValue=PHL%25&parameterValue=GUEST

Note:

The percent "%" character must be URL-encoded to be passed as query parameter value.

Bulk Query

A common use case is to download a selection of data that was perhaps modified during a certain period, e.g. all shipments completed in the past week. While the REST API collection pagination can be used for that purpose, it is not the primary purpose and there are occasionally data volumes involved where the API response time can degrade. Additionally, if the query/download is run during a period where the underlying data can be added to or removed, this can adversely affect the paging of results.

The Bulk Query capability is an action which will return a collection of root resource primary keys based on an Advanced Query message described above. There is no limit to the number of primary keys returned.

Note:

The intent is to expand this capability in the near future to also support child resources within and even across parent root resources.

The pattern to use the Bulk Query would then be to:

Define Advanced Query message with "q" string or saved query to identify all matching resources.
Execute Bulk Query action with above Advanced Query and retain list of primary keys.
Iterate through list of primary keys in batches of up to 1000.
Execute a normal Advanced Query using the "in" clause with the batch of primary keys to retrieve a collection of the target resource structures.

For example:

POST /logisticsRestApi/resources/v2/custom-actions/bulkQueries/orderReleases
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "q": "sourceLocationGid eq \"GUEST.PLANT\""
}

This will return a number of matching Order Release primary keys:

{
 "pkCount": 156,
 "pks": [
     "GUEST.MY_OR_1",
     "GUEST.MY_OR_2",
     etc
  ]
}

This list of primary keys can then be used to retrieve order release resources in batches:

POST /logisticsRestApi/resources/v2/custom-actions/queries/orderReleases
Prefer: transient
Content-Type: application/vnd.oracle.resource+json;type=query-def
...
{
 "q": "orderReleaseGid in [\"GUEST.OR_1\", [\"GUEST.OR_2\", ..etc.."
}

Paginating

Most resource collections need some kind of pagination. Without it, a simple search could return millions of records, putting excessive workload on the server for potentially no reason. So, instead of receiving all the records of a collection resource, you can limit the number of records that are displayed on a page in the REST client response, and then use the "next" or "previous" links to page through results. To do so, set the following parameters in the request payload:

limit - to specify the paging size. There is a maximum value of 1000 which is a system limit and cannot be configured.

Note:

It is also possible to set a system limit on the amount of data that is retrieved for any collection in order to protect the server from excessive load. The setting of this limit requires Ops Support but when set, this limiting value can cause the actual number of items returned to differ from the "limit" value specified in the request.
offset - to specify the starting point from which the resources are returned
totalResults - set to true to include the total number of search records that match the query

The following fields will be set in the response payload:

hasMore - set to true when there more records to be returned from the collection; set to false when the last set of records are retrieved from the collection
totalResults - set to the total number of search records

If the collection of results amounts to more than one page size, then a "next" link will also be returned with the "offset" query parameter populated. Following this link will then return the next set of results according to the order specified on the original request. Subsequent pages will then also show the "prev" link which can return the results of the page prior to the current page. It is also possible to manually specify the "offset" value (see below for examples). Also, if, as is described above, there is a system limit set for the total returned data size, additional "prevOffset" query parameters may also be present in the "prev" links.

For example, the client runs a GET command on an orderReleases resource. The server matches 100 orderReleases and the current request returns only the first 25. To indicate that there are more records to retrieve, the server sets the read-only hasMore field to true.

Example Response Body

{
  "items": [
   .
   .
   .
  ],
  "count": 25,
  "hasMore": true,
  "limit": 25,
  "offset": 0,
  "links": [
    {
      "rel":"next",
      "href":"http://server/logisticsRestApi/resources/v2/orderReleases?offset=25"

    }
  ]
}

If you set the totalResults parameter to true in the request, the response includes the totalResults field. After retrieving all the records as indicated by the totalResults value, the server sets hasMore to false.

{
  "items": [
   .
   .
   .
  ],
  "totalResults": 100,
  "count": 25,
  "hasMore": true,
  "limit": 25,
  "offset": 0,
  "links": [
    {
	.
    .
	.
    }
  ]
}

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. If you update the collection resource between paging requests, the records displayed in each page may vary.

Example Requests	Records Returned
`GET /orderReleases?offset=10&limit=20`	11 through 30
`GET /locations?q=locationName co "ZHRX"&limit=10`	1 through 10
`GET /locations?q=locationName co "ZHRX"&offset=25`	26 through 50
`GET /drivers?totalResults=true&limit=20`	1 through 20 Response includes the record count of total drivers.

If not specified, the "limit" parameter defaults to the system default which is configurable. The initial value is 25 but can be changed by setting the following property to a different value:

glog.fusion.cil.restapi.config.limit=50

This would return up to 50 matching items if no 'limit' parameter is used. It is also possible to set a maximum value for the limit parameter that any client can use. There is also a system wide maximum value that cannot be exceeded and is not configurable. If a query exceeds this value it is not possible to access the skipped records without narrowing the search criteria.

The maximum value for the limit query parameter is controlled by the following example property:

glog.fusion.cil.restapi.config.max_limit=500

Therefore, if a client specified "limit=600" in their request, the lower value of 500 would be used. The hard system limit is currently set at 1000.

Sorting

Sorting is another feature that makes it easier to work with data-heavy resources. You can sort items returned from a collection resource using the orderBy query parameter. To set the sorting sequence, use asc for ascending order and desc for descending order. The default sequencing order is asc. The items returned in the response payload are sorted in a case-sensitive order.

For example, to sort items according to locationGid in descending order, enter the command:

?orderBy=locationGid:desc

To sort items according to powerUnitNum in ascending order, enter the command:

?orderBy=powerUnitNum:asc

or only

?orderBy=powerUnitNum

because asc is the default sorting order.

If you include multiple fields in the query parameter, the order in which you specify the fields determines the sorting order. For example, to sort items in ascending order of title, and then sort according to dateAdded in descending order, enter the command:

?orderBy=title,dateAdded:desc

You now know how to manage your collection resources!