X

Best Practices from Oracle Development's A‑Team

  • October 5, 2020

The Power of Running BIP Report using SOAP from Custom Component in Oracle Digital Assistant

Executive Summary

Oracle Digital Assistant is a powerful platform that offers unlimited extensibility features. A common extension request received is to provide the capability to run any custom BIP (Business Intelligence Publisher) report from the Digital Assistant and display the results returned from the BIP report in the chat session. To address the requirement, this blog attempts to describe a generic framework that can be setup with a custom skill to call any BIP report and retrieve the results for display within the chat window.

Solution Architecture

The solution is implemented by creating a custom skill with the primary intent to call a BIP report. The corresponding custom component within the skill sends a SOAP request to run the BIP report using the WSDL exposed by the BIP report service. We have used the BI Publisher included within the Fusion Applications but this framework can be applied to any BI Publisher service. The overall architecture of the solution is described in Fig. 1

Fig. 1 Custom Skill calling a BIP report in Fusion Apps using SOAP

 

Setup 

The complete setup consists of tasks carried out within BI Publisher and Digital Assistant. The following sections outline the details of configuration in both of the component areas.

BI Publisher

BI Publisher can publish reports in many different formats. For our purposes, the XML format needs to be selected. Please refer to the blog[1] by our colleague, Richard Willams for details on how a BIP report can be set up to retrieve the results in XML format. The blog also mentions that the results returned in the SOAP call is encoded in Base64 format. Thus, in order to extract the XML payload, the result payload from the SOAP request, has to be decoded from Base64 encoded format.

The sample SOAP request corresponding to the BIP report used for this blog is shown in Fig. 2. It takes an input argument as the INPUT_REQUESTER_ID and provides a list of open POs that are owned by the person ID. 

Fig 2 Sample request to run BIP report from SoapUI

The Base64 encoded payload from the SOAP response is then decoded offline to show in Fig. 3 below the XML payload returned by the BIP report.

<DATA_DS>
   <INPUT_REQUESTER_ID>300000047639648</INPUT_REQUESTER_ID>
   <G_1>
      <PO_NUM>163158</PO_NUM>
      <LINE_NUM>2</LINE_NUM>
      <ITEM_DESCRIPTION>Round Stic Ball Point Pen, Medium, Red</ITEM_DESCRIPTION>
      <QUANTITY>1</QUANTITY>
   </G_1>
   <G_1>
      <PO_NUM>162304</PO_NUM>
      <LINE_NUM>1</LINE_NUM>
      <ITEM_DESCRIPTION>Stop-Step 4-Step Ladder with Right Handrail, 27 inch Top Step, 23-3/4 inch x 33-1/2 inch x 40-3/4 inch</ITEM_DESCRIPTION>
      <QUANTITY>1</QUANTITY>
   </G_1>
   <G_1>
      <PO_NUM>163158</PO_NUM>
      <LINE_NUM>1</LINE_NUM>
      <ITEM_DESCRIPTION>Computation Notebook, 75 Numbered Sets, 4 inch x 4 inch Quad Ruled, 11-3/4 inch x 9-1/4 inch</ITEM_DESCRIPTION>
      <QUANTITY>1</QUANTITY>
   </G_1>
   <G_1>
      <PO_NUM>163157</PO_NUM>
      <LINE_NUM>1</LINE_NUM>
      <ITEM_DESCRIPTION>D530 Monochrome Laser Printer with Scanner and Copier</ITEM_DESCRIPTION>
      <QUANTITY>1</QUANTITY>
   </G_1>
   <G_1>
      <PO_NUM>163157</PO_NUM>
      <LINE_NUM>2</LINE_NUM>
      <ITEM_DESCRIPTION>Hi Capacity Black Laser Printer Toner Cartridge</ITEM_DESCRIPTION>
      <QUANTITY>1</QUANTITY>
   </G_1>
</DATA_DS>

Fig. 3 XML response after decode of payload received in SoapUI from BIP report

Digital Assistant

Within Digital Assistant, the setup is again done in 2 areas, namely the conversation flow and the custom component. The following sub-sections describe the steps necessary to complete the implementation.

Conversational Flow

The conversational flow is modified to complete the following steps.

  1. Get OAuth Token for Fusion Apps access, if not available. This step is fairly standard in most of the skills interacting with Fusion Apps. Hence, it is not covered in details here. 
  2. Call the custom component passing the parameters listed below
    • Fusion Apps Instance Name
    • OAuth Token
    • BIP Report Path
    • Report Input parameter 

Excerpt of conversational flow to call the custom component is shown below.

  getEncodedMht:
    component: "getReportXML"
    properties:
      instance: "myFusionInstanceName"
      reportPath: "/Custom/myDirectory/SL_BIPReport.xdo"
      reportInputParam: "myInputParamValue"
      accessToken: "${user.oAuthToken}" 
    transitions:
      actions:
        success: "listPOs"
        fail: "exitFlow"
      next: "listPOs"         

Custom Component

The key areas of building the custom component are discussed here with relevant code snippets.

  • The custom component has a few dependencies on other modules, which are listed below.
     "use strict";
     var soap = require('strong-soap').soap;
     var parseString = require('xml2js').parseString;
     var xml2js = require('xml2js');
     var stripPrefix = require('../node_modules/xml2js/lib/processors.js').stripPrefix;
     var log4js = require('log4js');
     var logger = log4js.getLogger();
  • Parameters passed to the custom component from the conversation are the same as listed in the conversational flow
     const instance = conversation.properties().instance;
     const reportPath = conversation.properties().reportPath;
     const reportInputParam = conversation.properties().reportInputParam;
     const accessToken = conversation.properties().accessToken;
  • Initialize the parameters required to receive the SOAP response 
     // enable Support of SOAP 1.2
     var requestOptions={
         "forceSoap12Headers": true
         };         
     // set response options for SOAP response payload
     var responseOptions={
         explicitArray: false,
         ignoreAttrs: true,
         tagNameProcessors: [stripPrefix]
         };        
     var outputType = 'XML';      
     var requestMethod= "runReport";
  • Build the SOAP endpoint WSDL based on the dynamic instance name passed and set up the request parameters with the arguments including the input parameters for the BIP report
     var soap = require('soap');
     var url = 'https://' + instance + '.myFADomain.oraclecloud.com/xmlpserver/services/ExternalReportWSSService?WSDL';
     var args = {
         "reportRequest": {
             "parameterNameValues": {
                 "item": {
                     "name": "INPUT_REQUESTER_ID",
                     "values": {
                        "item": reportInputParam
                     }
                 }
             },
             "reportAbsolutePath": reportPath,
             "sizeOfDataChunkDownload": -1
         }
     };
  • Create the SOAP client with the security option to include the access token received and invoke the method described in the BIP Report Service WSDL
     soap.createClient(url, requestOptions, function(err, client) {
        if (err) {
            // WSDL couldn't be reached due to connectivity errors
            conversation.transition("fail");
            done();
            }
        var method = client[requestMethod];
        // Token sent for authentication
        client.setSecurity(new soap.BearerSecurity(accessToken));
        // Invoke the runReport method defined in BIP Report Service WSDL
        method(args, function(err, result, envelope, soapHeader) {
  • Decode the base64 encoded payload to extract the XML payload
     var encodedReport = result['runReportReturn']['reportBytes'];
     var decodedBinary = new Buffer.from(encodedReport, 'base64');
     var decodedReportString = decodedBinary.toString('utf-8');
  • Convert the XML payload to a JSON object and extract the desired result tree from the JSON object
     var JSONResult = '';
     var parser = new xml2js.Parser();
     parseString(decodedReportString, { explicitArray : false, ignoreAttrs : true,tagNameProcessors: [stripPrefix]}, function (err, result) {
         if(err) throw err;
         // traverse to the right level and node in the JSON object to extract the desired result data
         JSONResult = result.DATA_DS.G_1;
         });
  • Return the JSON array back to the conversational flow
     conversation.variable('encodedReport', JSONResult);
     conversation.transition("success");
     done();

Upon return from the custom component, the conversational flow can then use standard techniques available to process the JSON array containing the results returned from the BIP report. For more details on ways to display the results returned from a custom component, please refer to the product documentation set for Oracle Digital Assistant[2].

Testing

After packaging the custom skill for testing, we run it in a chatbot session and observe that the bot response is the same as that returned via SoapUI.  A sample chat session output is shown in Fig. 4 below, which displays the same entries as that seen in Fig. 3.

Fig. 4. BIP Report results returned in a chatbot session within Fusion Apps

This concludes the testing of our custom skill in Digital Assistant built to invoke any custom BIP report.

Summary

The approach outlined here is primarily documented to provide the initial guidance to build a basic framework that can call a BIP report from a custom skill in Digital Assistant. It has not been hardened for production usage by any means and is beyond the scope of this blog. It is left to the readers discretion to take this approach as the starting point and then build on it for further enhancements and optimizations to address the unique requirements different situations may demand. 

Acknowledgements

At the end, I would like to acknowledge Mireille Duroussaud of Oracle Demo Team, who provided valuable guidance for the implementation of the SOAP client within the custom component of the Digital Assistant.

I would also like to thank my teammates, Siming Mu and Greg Mally, who helped me at different times with build or compilation of this content. 

References

  1. Using Oracle BI Publisher to Extract Data From Oracle Sales and ERP Clouds - A-Team Blog, Richard Williams 
  2. Using Oracle Digital Assistant - Product Documentation for Oracle Digital Assistant

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha