Photo by John McArthur on Unsplash
Streamlining ERP Cloud REST Queries
Play Nice And Only Take What You Need
Introduction
ERP Cloud has an expanding suite of REST APIs for Financials, PPM, Supply Chain, and HCM. We look forward to the day when the SOAP APIs can be retired, but it's great that ERP Cloud has extensive REST capabilities.
REST web services are easy to call using APEX's APEX_WEB_SERVICE or middleware tools such as OIC, Dell Boomi, or Mulesoft.
APEX is a good fit as JSON handling works well in the Oracle database, and this will only improve in 23C with the new JSON relational duality features.
l_response_clob := apex_web_service.make_rest_request(
p_url => my_url,
p_http_method => 'GET',
p_username => p_username,
p_password => p_password); -- Or use a web credential
When run without URL parameters, the GET operations retrieve all fields and all rows, paged using REST. This is an inefficient way of using the APIs as most business objects have many empty or unused fields, and you're typically only interested in a specific subset of data. Payloads will be larger and may require more mapping/handling in your middleware.
This article shows you how to call the REST APIs to query just the data that you need, reducing the response payload size and handling, using the HCM Worker REST API as an example.
The Basic Worker API
The ERP Cloud Worker API can be called as follows.
curl --location --request GET \
'https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/resources/latest/workers' \
--header 'REST-Framework-Version: 4' \
Note, you'll need ERP roles giving you access to the REST APIs and data security allowing you access to the worker data.
The response payload contains records for each person, with all fields, and multiple (too many to show here!) self-describing links as recommended by the REST architectural style.
{
"items": [
{
"PersonId": 300000003800427,
"PersonNumber": "X00010",
"CorrespondenceLanguage": null,
"BloodType": null,
"DateOfBirth": null,
"DateOfDeath": null,
"CountryOfBirth": null,
"RegionOfBirth": null,
"TownOfBirth": null,
"ApplicantNumber": null,
"CreatedBy": "DEMO_USER",
"CreationDate": "2022-10-04T03:04:21+00:00",
"LastUpdatedBy": "DEMO_USER",
"LastUpdateDate": "2022-10-04T03:04:25.086+00:00",
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931A8BD6B0000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item",
"properties": {
"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000002770400000002737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000027371007E00020000000178"
}
},
{
"rel": "canonical",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931A8BD6B0000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item"
},
... MANY ADDITIONAL LINKS ....
]
},
{
"PersonId": 300000003897586,
"PersonNumber": "X00011",
"CorrespondenceLanguage": null,
"BloodType": null,
"DateOfBirth": null,
"DateOfDeath": null,
"CountryOfBirth": null,
"RegionOfBirth": null,
"TownOfBirth": null,
"ApplicantNumber": null,
"CreatedBy": "DEMO_USER",
"CreationDate": "2022-10-06T13:36:12+00:00",
"LastUpdatedBy": "DEMO_USER",
"LastUpdateDate": "2022-10-06T13:36:24.175+00:00",
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931AA38F20000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item",
"properties": {
"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000002770400000002737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000027371007E00020000000178"
}
},
{
"rel": "canonical",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931AA38F20000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item"
}
.. MORE LINKS, MORE WORKERS
],
"count": 18,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers",
"name": "workers",
"kind": "collection"
}
]
}
This isn't especially efficient, and we can do a lot to improve the response.
Let's Get Rid of The Additional Links
Adding links=self removes the child links, leaving only the ones that describe each specific worker.
curl --location --request GET \
'https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/resources/latest/workers?links=self' \
--header 'REST-Framework-Version: 4'
The new response payload is much more compact. But we can do more ...
{
"items": [
{
"PersonId": 300000003800427,
"PersonNumber": "X00010",
"CorrespondenceLanguage": null,
"BloodType": null,
"DateOfBirth": null,
"DateOfDeath": null,
"CountryOfBirth": null,
"RegionOfBirth": null,
"TownOfBirth": null,
"ApplicantNumber": null,
"CreatedBy": "DEMO_USER",
"CreationDate": "2022-10-04T03:04:21+00:00",
"LastUpdatedBy": "DEMO_USER",
"LastUpdateDate": "2022-10-04T03:04:25.086+00:00",
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931A8BD6B0000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item",
"properties": {
"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000002770400000002737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000027371007E00020000000178"
}
}
]
},
{
"PersonId": 300000003897586,
"PersonNumber": "X00011",
"CorrespondenceLanguage": null,
"BloodType": null,
"DateOfBirth": null,
"DateOfDeath": null,
"CountryOfBirth": null,
"RegionOfBirth": null,
"TownOfBirth": null,
"ApplicantNumber": null,
"CreatedBy": "DEMO_USER",
"CreationDate": "2022-10-06T13:36:12+00:00",
"LastUpdatedBy": "DEMO_USER",
"LastUpdateDate": "2022-10-06T13:36:24.175+00:00",
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931AA38F20000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item",
"properties": {
"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000002770400000002737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000027371007E00020000000178"
}
}
]
}
... MORE
],
"count": 18,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers",
"name": "workers",
"kind": "collection"
}
]
}
Just Two Fields, Please
The fields parameter allows you to specify that the response should only contain the fields you want, in this example the PersonNumber and PersonId.
curl --location --request GET \
'https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/resources/latest/workers?links=self&fields=PersonNumber,PersonId' \
--header 'REST-Framework-Version: 4'
Our payload is now much cleaner:
{
"items": [
{
"PersonNumber": "X00010",
"PersonId": 300000003800427,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931A8BD6B0000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item",
"properties": {
"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000002770400000002737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000027371007E00020000000178"
}
}
]
},
{
"PersonNumber": "X00011",
"PersonId": 300000003897586,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED00057708000110D931AA38F20000004AACED00057372000D6A6176612E73716C2E4461746514FA46683F3566970200007872000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001842B56800078",
"name": "workers",
"kind": "item",
"properties": {
"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000002770400000002737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000027371007E00020000000178"
}
}
]
}
... MORE
],
"count": 18,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers",
"name": "workers",
"kind": "collection"
}
]
}
I've Changed My Mind, No Self Links Either ...
Adding onlyData=true removes all the self links from all items in the response payload. The collection link remains, and we'll show how to remove that later.
curl --location --request GET \
'https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/resources/latest/workers?links=self&fields=PersonNumber,PersonId;&onlyData=true' \
--header 'REST-Framework-Version: 4
Looking good ...
{
"items": [
{
"PersonNumber": "X00010",
"PersonId": 300000003800427
},
{
"PersonNumber": "X00011",
"PersonId": 300000003897586
},
{
"PersonNumber": "4",
"PersonId": 300000002062335
},
{
"PersonNumber": "X00002",
"PersonId": 300000001844442
},
{
"PersonNumber": "X00006",
"PersonId": 300000001844950
},
{
"PersonNumber": "X00004",
"PersonId": 300000001844733
},
{
"PersonNumber": "X00005",
"PersonId": 300000001844862
},
{
"PersonNumber": "X00008",
"PersonId": 300000003799692
},
{
"PersonNumber": "X00007",
"PersonId": 300000003799918
},
{
"PersonNumber": "Y00001",
"PersonId": 300000004112659
},
{
"PersonNumber": "Y00002",
"PersonId": 300000004112753
},
{
"PersonNumber": "2",
"PersonId": 300000001929994
},
{
"PersonNumber": "X00001",
"PersonId": 300000003603583
},
{
"PersonNumber": "X00003",
"PersonId": 300000001844645
},
{
"PersonNumber": "X00009",
"PersonId": 300000003799787
},
{
"PersonNumber": "X000101",
"PersonId": 300000004112565
},
{
"PersonNumber": "Y00003",
"PersonId": 300000004112847
},
{
"PersonNumber": "1",
"PersonId": 300000001929839
}
],
"count": 18,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers",
"name": "workers",
"kind": "collection"
}
]
}
But I Only Wanted One Worker
Query syntax is simple when you choose Rest Framework Version 4 or higher. You can use simple queries such as:
PersonNumber = 'X00011'
or you can get more complicated with AND, OR etc:
(PrimaryFlag=true AND StartDate <= '2022-10-03' AND (TerminationDate IS NULL OR TerminationDate >= '2022-10-03' ))
curl --location --request GET \
'https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/resources/latest/workers?links=self&q=PersonNumber = '\''X00011'\''&fields=PersonNumber,PersonId;&links=self&onlyData=true' \
--header 'REST-Framework-Version: 4'
Our payload is now very specific to our use case:
{
"items": [
{
"PersonNumber": "X00011",
"PersonId": 300000003897586
}
],
"count": 1,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": [
{
"rel": "self",
"href": "https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com:443/hcmRestApi/resources/11.13.18.05/workers",
"name": "workers",
"kind": "collection"
}
]
}
But Wait, I Need To Know the Name Too
Workers are a hierarchical structure in ERP Cloud, each worker has a name, email, work relationship, assignment etc.
You can tell the REST API to return the child objects using the expand parameter. You can add one or more child objects, and include objects several levels down in the hierarchy as shown in these examples:
Add Names to the response payload:
expand=names
Add Names, Emails, Work Relationships, Assignments and Manager to the response payload:
expand=names,emails,workRelationships.assignments.managers
In our final example, we include the name, email and assignment using this syntax:
expand=names,emails,workRelationships.assignments
As we'd like to restrict the fields for the child objects, we add what we need to the fields parameter:
fields=PersonNumber,PersonId;names:FirstName,LastName;emails:EmailAddress;workRelationships.assignments:AssignmentNumber
And finally, adding links=child hides all self links, leaving child links only, and because onlyData=true hides child links ... every link is now gone.
curl --location --request GET \
'https://fa-xxxx-test-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/resources/latest/workers?links=child&q=PersonNumber = '\''X00011'\''&effectiveDate=2022-10-05&fields=PersonNumber,PersonId;names:FirstName,LastName;emails:EmailAddress;workRelationships.assignments:AssignmentNumber&links=self&onlyData=true&expand=names,emails,workRelationships.assignments' \
--header 'REST-Framework-Version: 4'
Our final payload shows exactly the info we need.
- Employee Number
- Employee ID
- First Name
- Last Name
- Assignment Number
{
"items": [
{
"PersonNumber": "X00011",
"PersonId": 300000003897586,
"emails": {
"items": [
{
"EmailAddress": "test.worker11@test.com"
}
],
"count": 1,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": []
},
"names": {
"items": [
{
"FirstName": "Test",
"LastName": "Worker11"
}
],
"count": 1,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": []
},
"workRelationships": {
"items": [
{
"assignments": {
"items": [
{
"AssignmentNumber": "EX00011"
}
],
"count": 1,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": []
}
}
],
"count": 1,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": []
}
}
],
"count": 1,
"hasMore": false,
"limit": 25,
"offset": 0,
"links": []
}
If we hadn't filtered out all the additional fields and links, the payload would be 4 times larger.
Summary
In this blog, we saw how to query the REST APIs, returning payloads containing the specific fields that you need.
There's more to it than we cover here, such as paging, data sorting and offsets. You can take a look here to see the 22D API functionality:
docs.oracle.com/en/cloud/saas/human-resourc..
Have fun integrating!