Fusion SaaS: Implement P2T hassle-free generic SQL query REST utility for VB AppUIs by using BIP and App Composer

General

In the previous post we have improved the solution allowing running SQL queries from VB AppUIs by utilizing BIP lexicals.
The solution was based on the following:

1. BIP report receiving an SQL query and returning JSON response, utilizing the lexicals feature.
2. OIC integration that invokes the report above and returns its JSON response, utilizing a binary output feature of OIC to return JSON built by the query.
3. Custom Object row level externally exposed Groovy method, available in VB AppUI projects, calling the integration above and returning results as a JSON array, easily invocable from Javascript action chains.

The last solution element relies on REST Web Service definition that points to an OIC instance. Such a connection should be updated after P2T process to ensure that no TEST instance will access PROD.
The new proposed solution utilizes the fa-internal.oracleoutsourcing.com hostname to call the BIP report above and return the generic response by groovy code only. No OIC involved.
Such a solution is completely hassle-free in P2T process as the logical name above is pointing to the current environment.
The solution below will explain how to setup the connection above, implement the groovy method and finally call it from Javascript chains in VBCS AppUIs.

High Level Implementation Details

1. Define a connection named LocalWssReport pointing to https://fa-internal.oracleoutsourcing.com:10663/xmlpserver/services/ExternalReportWSSService?wsdl
2. Use “Propagate user identity using SAML” to allow seamless login
3. Implement Application Composer groovy function to call the report returning XML with JSON inside
4. Extract the JSON output, unescape the XML special chars and return
5. Use JSON.parse inside action chain to convert the response to easily usable format

Detailed Implementation Details

1. Define a generic report execution SOAP connection to https://fa-internal.oracleoutsourcing.com:10663/xmlpserver/services/ExternalReportWSSService?wsdl in App Composer:
   
2. Implement groovy row level function (object level can not access web services), assuming the report is uploaded to the location /Custom/Integrations/Utils/RunSQLToJson.xdo:

   
   def reportRequest =
   [
     byPassCache :true,
     reportAbsolutePath :'/Custom/Integrations/Utils/RunSQLToJson.xdo',
     sizeOfDataChunkDownload :-1,
     attributeFormat: 'xml',
   parameterNameValues:
   [
   item:
   [[
   name :'p_sql',
    values:
               [
                  item:
                  [[
                        item :sqlText,
                  ]],
               ],
   ]],
   ],
   ];
   def appParams = '';
   def report = adf.webServices.LocalWssReport.runReport(reportRequest, appParams);
   def repResult = decodeBase64(report.reportBytes.toString());
   def json = '';
   
   if (contains(repResult, '</JSON_DOC>')) {
     json = substringAfter(repResult, '<JSON_DOC>');
     json = substringBefore(json,'</JSON_DOC>');
   }
   
   if (json == '') return '[]'
   
   return json.replaceAll("&amp;", "&")
                    .replaceAll("&lt;", "<")
                    .replaceAll("&gt;", ">")
                    .replaceAll("&quot;", "\"")
                    .replaceAll("&apos;", "'")
   

3. Test the method above in Postman:
   
4. Use JSON.parse in action chain to use the output above.