Get Started

Visual Builder Extension for Fusion SaaS: utilize BIP SQL queries by using Groovy and OIC

BIP SQL Queries in VB Extensions: Using Groovy and OIC

General

Using of OIC for exposing BIP reports to Visual Builder Application is a quite common approach. However when we develop SaaS extensions we have only access to SaaS backend and there is no secure way (at least up until today) to create a connection to an OIC instance.
According to VBCS blog, the ability to add local server connection in Visual Builder Studio will provide solution for this need.
Meanwhile we can implement the workaround by using Application Composer Groovy scripting, OIC and BIP.

Use Case

A VBCS SaaS Extension screen should display a table with results brought by a complex SQL query.

Implementation Details

This following solution extends similar approach of using Groovy that was described in https://cidsolutions.com/2023/12/01/visual-builder-extension-for-fusion-saas-creating-complex-queries-with-application-composer-groovy-methods/.

  1. Create a REST OIC Integration that is invoked by POST method to allow passing multiple parameters to the query.
    Screenshot 2024 02 09 at 18.33.07
  2. Define JSON payload for multiple optional parameters to be passed.
    Screenshot 2024 02 09 at 18.34.16
  3. Define response payload as array of records.
  4. Use standard ExternalReportWSSService to run the report and convert the XML result to JSON by using staging file.
  5. Define a WebService connection in Application Composer, specify POST method and samples for input and response payloads.
    Screenshot 2024 02 09 at 18.38.20
  6. Similar to the previous post, define an externally callable method, in our case under Subscription object.
    Screenshot 2024 02 09 at 18.40.59
  7. Call the integration, parse the results and return response in a declared format java.util.List<java.util.Map<java.lang.String,java.lang.String>>.
    Screenshot 2024 02 09 at 18.43.13
  8. Define connection to the method above in Visual Builder Studio.
    Screenshot 2024 02 09 at 18.47.03
  9. If using SDP – customize the fetch chain to allow proper Content-Type passing as required by Fusion.
    Screenshot 2024 02 09 at 18.49.24
  10. Implement the rest of required features.
Table of Contents

Leave a Reply

Your email address will not be published. Required fields are marked *