Visual Builder Extension for Fusion SaaS: creating complex queries with Application Composer Groovy methods

General

Oracle Fusion SaaS comes with built in Visual Builder instance built especially for running extension screens. The benefits of this approach is utilization of current Fusion user’s session, pre-configured backend which exposes Fscm/HCM REST APIs.
Oracle REST APIs exposed in Fusion backend can be used for querying, updating and deleting standard and custom objects. While the query syntax supported by Fusion REST APIs can filter parent and child resource objects, the ability of such queries are limited.
You can find example of such queries here: https://docs.oracle.com/en/cloud/paas/app-builder-cloud/consume-rest/introduction-accessing-business-objects.html#GUID-3D80E4CB-EF28-42C3-A3A6-1AD3F99FEF4E.
When we use Visual Builder Application rather than Visual Builder SaaS Extension – we can utilize Oracle Integration Cloud for creation of sophisticated queries and expose them as REST APIs to VBCS for easy consumption. In Visual Builder Extension mode we are limited to Fusion SaaS backend only.

Use Case

In the example below we will present the option of creating a VBCS Extension screens showing Subscription Non-Amended lines (lines that are not pointed by relations from other lines). Such query can not be achieved by using query syntax and we will show how Application Compose Groovy methods can be utilized for this task.

Implementation Details

In one of previous posts we have shown how to use Application Composer to add calculated fields for custom object and use standard REST API to query the combined data. https://cidsolutions.co.il/2023/09/26/create-efficient-update-screens-for-fusion-saas-custom-objects-by-using-vbcs-and-groovy-formulas/.
The inspiration for the solution below was taken from the following A-TEAM post: https://blogs.oracle.com/ateam/post/enabling-secured-rest-calls-from-vbs-to-custom-secured-rest-services-using-oracle-fusion-application-composer which was further extended for supporting multiple returned records rather than a single record.

1. Create a new Server Script Object Function under Subscription standard object that will return java.util.List<java.util.Map<java.lang.String,java.lang.String>>
   
2. Set the method to be called by External System:
   
3. Loop through subscription products, utilize Groovy Set to calculate the lines to be returned and use ArrayList to generate the return result:
   
4. You can test it with Postman, remember to set proper Content-Type header:
   
5. In VBCS, although standard backend shows the generated method, due to unique Content-Type it is not available to be called directly. In order to overcome it – create direct service connection like here:
   
   
6. And now you can create a standard SDP variable:
   
7. Proceed to other tasks in the screen.