APEX: Filtering Fusion REST Integrations
Sydney Nurse
Part III: Find by Advanced Search Query
In my discovery and test attempts, I stumbled upon this highly flexible, configurable, and effective way to retrieve Fusion SaaS data and business objects.
I cam across this during my network analysis and tracing of the SaaS module of the day and found it offered a few benefits over the standard APIs. It gives me to ability to:
Configure the attributes or display fields that are returned
Configure which fields to be searched
Configure filters for the API
Configure the sort & ordering for the invoke
As these are string values, I could pass them at runtime to suite my needs. Funny enough I have yet to find the document reference for the actual API I am using but it is listed in the full SaaS module and specific API metadata.
About this Post
I will describe how I am using the advance search query API and provide a similar document reference, as I am unable to supply one for my actual API in use.
As with many of my examples, this post will reference the HCM module of the Fusion API documentation. Oracle APEX and Postman were used in the app development and API testing.
As Close As It Gets
I use recruitingUIJobRequisitions APIs to integrate with my Fusion HCM instance and there are a few Job Requisition API sets in the documentation, just not this one. This is a search API but uses a POST HTTP method to retrieve the data responses.
Check out the documentation reference for the closest or nearest example API.
The metadata description from Postman for …/recruitingUIJobRequisitions/describe is:
{
"Resources" : {
"recruitingUIJobRequisitions" : {
"discrColumnType" : false,
"titlePlural" : "Recruiting Job Requisitions",
"usage" : "BusinessObject",
"collection" : {
"rangeSize" : 25,
"finders" : [ {
"name" : "PrimaryKey",
"annotations" : {
"description" : "Finds all job requisitions for the specified primary key."
},
"attributes" : [ {
"name" : "RequisitionId",
"type" : "integer",
"updatable" : true,
"mandatory" : true,
"queryable" : true,
"allowChanges" : "inCreate",
"precision" : 18,
"hasDefaultValueExpression" : true,
"properties" : {
"fnd:GLOBALLY_UNIQUE" : "true"
},
"annotations" : {
"description" : "Unique identifier of the job requisition."
}
} ]
}, {
"name" : "findByRequisitionNumber",
"title" : "findByRequisitionNumber",
"annotations" : {
"description" : "Finds all job requisitions for the specified requisition number."
},
"attributes" : [ {
"name" : "RequisitionNumber",
"type" : "string",
"updatable" : true,
"required" : "Optional",
"queryable" : false,
"allowChanges" : "always",
"annotations" : {
"description" : "Requisition number of the job requisition."
}
} ]
} ],
"links" : [ {
"rel" : "self",
"href" : "https://my-fusion-hcm-saas.oracle.com.com:443/hcmRestApi/resources/11.13.18.05/recruitingUIJobRequisitions",
"name" : "self",
"kind" : "collection"
} ],
"actions" : [ {
"name" : "get",
"method" : "GET",
"responseType" : [ "application/vnd.oracle.adf.resourcecollection+json", "application/json" ],
"annotations" : {
"title" : "Get all job requisitions"
}
},... {
"name" : "findByAdvancedSearchQuery",
"batchEnabled" : false,
"parameters" : [ {
"name" : "limit",
"type" : "number",
"mandatory" : false
}, {
"name" : "offset",
"type" : "number",
"mandatory" : false
}, {
"name" : "query",
"type" : "string",
"mandatory" : false
}, {
"name" : "autocompleteQuery",
"type" : "string",
"mandatory" : false
}, {
"name" : "searchFields",
"type" : "array",
"typeProperties" : {
"itemType" : "string"
},
"mandatory" : false
}, {
"name" : "displayFields",
"type" : "array",
"typeProperties" : {
"itemType" : "string"
},
"mandatory" : false
}, {
"name" : "filters",
"type" : "array",
"typeProperties" : {
"itemType" : "object",
"typeProperties" : {
"itemType" : "array",
"typeProperties" : {
"itemType" : "string"
}
}
},
"mandatory" : false
}, {
"name" : "sort",
"type" : "array",
"typeProperties" : {
"itemType" : "object",
"typeProperties" : {
"itemType" : "string"
}
},
"mandatory" : false
} ],
"resultType" : "object",
"typeProperties" : {
"itemType" : "array",
"typeProperties" : {
"itemType" : "object",
"typeProperties" : {
"itemType" : "string"
}
}
},
"method" : "POST",
"requestType" : [ "application/vnd.oracle.adf.action+json" ],
"responseType" : [ "application/vnd.oracle.adf.actionresult+json", "application/json" ],
"annotations" : {
"title" : "Return all job requisitions from the advanced Oracle search.",
"description" : "Return all job requisitions from the advanced Oracle search.",
"parameters" : [ {
"name" : "autocompleteQuery",
"description" : "Text string to determine the autocomplete query for the advanced Oracle search."
}, {
"name" : "displayFields",
"description" : "List of fields returned by the advanced Oracle search."
}, {
"name" : "filters",
"description" : "Filter criteria in the advanced Oracle search."
}, {
"name" : "limit",
"description" : "Maximum number of items to return from the advanced Oracle search."
}, {
"name" : "offset",
"description" : "Pagination index where to start returning item for the advanced Oracle search."
}, {
"name" : "query",
"description" : "Keyword text used for searching in advanced Oracle search."
}, {
"name" : "searchFields",
"description" : "List of fields used for searching in the advanced Oracle search."
}, {
"name" : "sort",
"description" : "Sort criteria in the advanced Oracle search."
} ],
"return" : {
"description" : "List of job requisitions matching criteria such as sort, filters, pagination."
}
}
},... ]
},...
}
}
}
One of the API task sets that have this similar method is the HCM Data Loader Data Sets REST Endpoints Perform advanced search
We can see the exact Request Body attributes, to get the same descriptions provided by the metadata describe response.
Transform Describe to apply in API
An observation, perhaps obvious to the Fusion consultant, is that after describing the API and viewing the attributes, the naming format does not match the format required in the API call.
Each attribute that is returned in metadata uses PascalCase, which is the same case that is required for any filtering using a Query string. Based on the example I captured, a format similar to Snake_Case is used but all attributes are in UPPER_CASE.
I made a small change from TITLE to title and had a null response.
An even more curious observation is that camelCase is used for the names of the request body attributes, and YES, these are also caseSenitive.
The one thing that I would have expected to be case sensitive but that was not, was the actual data values.
HR Operations Manager
HR OPERATIONS MANAGER
hr operations MANAGER
All of these returned the row I was searching for
Display and Search Field and Filter attribute names
As noted the attribute names use UPPERCASE versions of the attributes with an underscore to separate each new word.
RequisitionNumber = REQUISITION_NUMBER
RecruitingTypeName = RECRUITING_TYPE_NAME
etc.
The display and search fields were simple to find and changing the format and in general the same is true for the filters but some of the values for filters are something completely different.
What are the accepted values for the filters attribute?
The filters can be based on attributes, similar to displayFields & and searchFields but some are not. Where do you find the reference for the others?
Honestly, I don’t know and it will vary from Fusion API to Business Object, Table & View.
In my case, it is a combination of business rules, roles and most certainly security.
{"REQUISITION_OWNERSHIP":["ORA_RECRUITER",
"ORA_HIRINGMANAGER",
"ORA_COLLABORATOR"
]
}
I have not found a definitive Fusion API to Table/View or Function mapping, it is a guess and since I captured a working set of filter I can not begin to describe the relationship.
I can only offer two options:
Reach out to a Fusion SME for the module you are working with and struggle together
Forget the why, and attempts to fully understand the entire API and use the Network traffic trace of the your Browser’s Developer Tools and get a working API call.
In my case, this is what I did and discovered the existence of this API and after a bit of reading, trial and error, I came upon a working version.
Conclusion
The FindByAdvancedSearchQuery is very useful and provides a level of control on both search and API responses.
Unfortunately it is not available for all of the task and business objects nor are all documented. Though I would suggest keeping to the documented APIs, we all know that if it exists, someone will find it and use it until, well, it changes, is replaced, or removed all together.
Using this action APIs is straight forward and flexible to fit static and dynamic use cases.
I hope you have found this series useful and find a use case in your APEX integrations as well.
Subscribe to my newsletter
Read articles from Sydney Nurse directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
Sydney Nurse
Sydney Nurse
I work with software but it does not define me and my constant is change and I live a life of evolution. Learning, adapting, forgetting, re-learning, repeating I am not a Developer, I simply use software tools to solve interesting challenges and implement different use cases.