Visual Builder Extension for Fusion SaaS: speed up development process by using generic REST SQL query utility

General

Following the post showing the method of running BIP SQL reports from Visual Builder Extension described here, we have built a generic solution that allows running any Fusion SaaS related SQL queries without the need of creating a dedicated report, custom object and groovy functions.

Use Case

Implement Fusion SaaS attachment category selection in a VBCS screen. There is no published Fusion SaaS REST API that allows fetching attachment categories relevant for a specific attachment entity (Sales Order for example).
The data below can be fetched by the SQL statement below, this post will describe a way to achieve it by building a generic solution for this and other similar cases.

select cat.category_name, cat.user_name
from FND_DOCUMENT_ENTITIES fde, FND_DOC_CATEGORIES_TO_ENTITIES dce, fnd_document_categories_vl cat
where fde.table_name = 'DOO_HEADERS_ALL'
and fde.document_entity_id = dce.document_entity_id
and dce.category_id = cat.category_id

High Level Implementation Details

Create a generic BIP report that can receive any SQL statement and return JSON array response with fixed column names, like COL1, COL2, … , COL5.
Create a generic OIC integrations exposing the report above.
Create a generic custom object to be used to expose REST endpoint for running SQL statement and get standard JSON array response and create Application Composer object function that will receive SQL statement and will return JSON array or results by an OIC integration above.
Utilize VBCS REST and ADP to populate SQL query results to be used in a Select Single component.

Detailed Implementation Details

Create a BIP report that will receive any SQL statement and will return JSON result with standard column names.
For example, the following SQL statement select 1,2,3,4,5 from dual will generate the following result [ { "COL1":"1", "COL2":"2", "COL3":"3", "COL4":"4", "COL5":"5" } ]
In order to achieve it you can create a data model with the following SQL statement:
Select dbms_xmlgen.getxmltype( :p_sql).transform(XMLType('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text" encoding="UTF-8"/><xsl:strip-space elements="*"/><xsl:template match="/"><xsl:text>[</xsl:text><xsl:for-each select="ROWSET/ROW"><xsl:if test="position() > 1"><xsl:text>,</xsl:text></xsl:if><xsl:text>{</xsl:text><xsl:for-each select="*"><xsl:if test="position() > 1"><xsl:text>,</xsl:text></xsl:if><xsl:text>"COL</xsl:text><xsl:value-of select="position()"/><xsl:text>": "</xsl:text><xsl:value-of select="."/><xsl:text>"</xsl:text></xsl:for-each><xsl:text>}</xsl:text></xsl:for-each><xsl:text>]</xsl:text></xsl:template></xsl:stylesheet>')) result from dual
Create a generic OIC integration that will run the BIP report above by using SOAP xmlpserver endpoint, will extract the reportBytes, convert it to XML text, will extract the JSON output and by using Stage File will return its content to the calling party.

To make the payload to include only columns selected in the SQL, use xsl:if to prevent return of non-populated columns:

By using the Application Composer create a custom object without any custom field or pages, for example RestUtil.
Add to the object custom Server Script defined as Externally Accessible.
Function Name: getReportData
Returns: java.util.List<java.util.Map<java.lang.String,java.lang.String>>
Parameter: sqlText (String)
Body:


def res = new java.util.ArrayList<java.util.Map<java.lang.String,java.lang.String>>()
def params = [:]
params.sqlText = sqlText

def report_data = adf.webServices.RunSqlReport.getResults(params)

for (r in report_data)
{
   def rec = r as Map
   res.add(rec)
}

return res

Use the standard Call REST to populate SQL results into an ADP variable to later usage in Select Single. In order to use the REST endpoint above you must ensure that there will be at least one record in the custom object you have created. The process of running the SQL statement would be fetching the Id of custom object and using it as part of method call URL.
The vbEnter of your VBCS page may look like:


class vbEnter extends ActionChain {

    /**
     * @param {Object} context
     */
    async run(context) {
      const { $page, $flow, $application, $extension } = context;

      const callGetRestUtil = await Actions.callRest(context, {
        endpoint: 'site_AccountSiteExtension:crm/getall_RestUtil_c',
        uriParams: {
          limit: '1',
          onlyData: true,
          fields: 'Id',
        },
      });

      if (callGetRestUtil.ok) {

        const getCategoriesReportDataResponse = await Actions.callRest(context, {
          endpoint: 'site_AccountSiteExtension:crm/do_getReportData_RestUtil_c',
          uriParams: {
            'RestUtil__c_Id': callGetRestUtil.body.items[0].Id,
          },
          body: {
            sqlText: 'select cat.category_name, cat.user_name from FND_DOCUMENT_ENTITIES fde, FND_DOC_CATEGORIES_TO_ENTITIES dce, fnd_document_categories_vl cat where fde.table_name = \'DOO_HEADERS_ALL\' and fde.document_entity_id = dce.document_entity_id and dce.category_id = cat.category_id',
          },
          contentType: 'application/vnd.oracle.adf.action+json',
        });

        if (getCategoriesReportDataResponse.ok) {
          $page.variables.categoryListADP.data = getCategoriesReportDataResponse.body.result;

          $page.variables.categoriesLoaded = true;
        } else {
          await Actions.fireNotificationEvent(context, {
            summary: 'Error',
            message: getCategoriesReportDataResponse.statusText,
          });
        }
      } else {
        await Actions.fireNotificationEvent(context, {
          summary: 'Error',
          message: callGetRestUtil.statusText,
        });
      }
    }
  }

  return vbEnter;
});