In this post, we will walk through the process to call Oracle Fusion BIP report from oracle APEX.

Follow below steps to call Fusion BIP report from Oracle APEX.

Step1 ===> Create Sample Fusion BIP report.

Create sample BIP report in oracle fusion which we will use to call from oracle APEX as shown below:

This report will provide AP Invoice details with Invoice Number as report parameter.

Data Model and Report are shown below:

This report is having below details:

Report Absolute Path	/Custom/APEX/FUSION_BIP/XX Test AP Invoice Report.xdo
Report Parameter Name	P_INVOICE_NUM
Report Output Format	Csv

Step2 ===> Test Fusion BIP report using SOAP WSDL.

Oracle Fusion BIP report can be called externally using ExternalReportWSSService WSDL soap service.

SOAP WSDL:

/xmlpserver/services/ExternalReportWSSService?wsdl

Let us configure this WSDL in SOAP UI and Test with sample Input parameter.

Request Payload:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
     <soap:Header/>
     <soap:Body>
        <pub:runReport>
           <pub:reportRequest>
              <pub:attributeFormat>csv</pub:attributeFormat>
              <pub:attributeLocale>?</pub:attributeLocale>
              <pub:parameterNameValues>
                 <!--Zero or more repetitions:-->
                 <pub:item>
                    <pub:name>P_INVOICE_NUM</pub:name>
                    <pub:values>
                       <!--Zero or more repetitions:-->
                       <pub:item>INV-100067</pub:item>
                    </pub:values>
                 </pub:item>
              </pub:parameterNameValues>
              <pub:reportAbsolutePath>/Custom/APEX/FUSION_BIP/XX Test AP Invoice Report.xdo
              </pub:reportAbsolutePath>
              <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
           </pub:reportRequest>
        </pub:runReport>
     </soap:Body>
  </soap:Envelope>

Response:

 <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
     <env:Header/>
     <env:Body>
        <ns2:runReportResponse xmlns:ns2="http://xmlns.oracle.com/oxp/service/PublicReportService">
           <ns2:runReportReturn>
              <ns2:reportBytes>77u/SU5WT0lDRV9JRCxWRU5ET1JfSUQsSU5WT0lDRV9OVU0sSU5WT0lDRV9DVVJSRU5DWV9DT0RFLFBBWU1FTlRfQ1VSUkVOQ1lfQ09ERSxJTlZPSUNFX0FNT1VOVCxWRU5ET1JfU0lURV9JRCxTT1VSQ0UsSU5WT0lDRV9UWVBFX0xPT0tVUF9DT0RFLEFQUFJPVkFMX1NUQVRVUwozMDAwMDAwMDM1OTMxNzgsMzAwMDAwMDAyOTEwODYxLElOVi0xMDAwNjcsVVNELFVTRCwwLDMwMDAwMDAwMjkxNjQxOSwiTWFudWFsIEludm9pY2UgRW50cnkiLFNUQU5EQVJELCJORUVEUyBSRUFQUFJPVkFMIgo=</ns2:reportBytes>
              <ns2:reportContentType>text/plain;charset=UTF-8</ns2:reportContentType>
              <ns2:reportFileID xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
              <ns2:reportLocale xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
              <ns2:metaDataList xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
           </ns2:runReportReturn>
        </ns2:runReportResponse>
     </env:Body>
  </env:Envelope>

This provides response in base64format which we need to decode to get actual output.

Step3 ===>Create Oracle APEX Application.

Login to Oracle APEX workspace and click on Create

Give meaningful name to APEX app and then click on Create Application.

The application is created successfully.

Change the application theme to Redwood light.

Navigate to shared components —>User Defined Attributes.

Change Theme Style to “Redwood Light” and click on Apply changes.

Run the application.

Provide login credentials and click on Sign in.

We will call fusion BIP report and display data into interactive report using DB table.

Step4 ===>Create DB Table.

Create DB table XXTEST_AP_INVOICE_DETAILS with same columns as we have in BIP report.

CREATE TABLE XXTEST_AP_INVOICE_DETAILS
    (
       INVOICE_ID               NUMBER,
       VENDOR_ID                NUMBER,
       INVOICE_NUM              VARCHAR2(50),
       INVOICE_CURRENCY_CODE    VARCHAR2(3),
       PAYMENT_CURRENCY_CODE    VARCHAR2(3),
       INVOICE_AMOUNT           NUMBER,
       VENDOR_SITE_ID           NUMBER,
       SOURCE                   VARCHAR2(30),
       INVOICE_TYPE_LOOKUP_CODE VARCHAR2(30),
       APPROVAL_STATUS          VARCHAR2(30)
    )

Navigate to Object Browsers —->SQL Commands.

Execute table creation script.

Step4 ===>Create Data Load Definition based on Table.

Navigate to Shared Components—>Othe components—>Data Load Definitions.

Click on Create.

Click on Next.

Provide valid DD name and select Table which we have created in earlier step.

Download sample Bip report output file and upload as sample file. This will create mapping between BIP report and table columns.

Click on Next.

Click on Create Data Load.

This will create Data Load Definitions.

Please note Static ID.

Step5 ===>Create Credentials to store SOAP service credentials.

We will setup credentials to store Fusion instance credentials which are required to call SOAP service.

Navigate to Shared Components—>Credentials

Click on Create.

Provide valid name, username, password for fusion instance as shown below:

Click on Create.

This will create credentials separately. We can call this credential using static id associated with this credential.

Step6 ===>Create Page Item, Button and Interactive Report on Home Page.

Navigate to home page and create Page Item, Button and Interactive Report region based on XXTEST_AP_INVOICE_DETAILS table as shown below:

Step7 ===>Create Page Process to Call BIP report and Populate table.

Navigate to Processing tab to create new page process to call BIP report using PLSQL. SOAP API can be called from Oracle APEX using APEX_WEB_SERVICE.MAKE_REQUEST package.

The APEX_WEB_SERVICE API enables you to integrate other systems with APEX by enabling you to interact with web services using PLSQL.

This API contains procedures and functions to call both SOAP and Restful style web services. Functions parse the responses from web services and encode/decode into SOAP-friendly base64 encoding.

For our use case, we are going to use MAKE_REQUEST function which invokes SOAP style web service with supplied SOAP envelop retuning result into an CLOB.

We will pass below parameters to this function:

Parameter	Description
p_url	The URL endpoint of the Web service.
p_http_method	The HTTP method to use (PUT, POST, GET, HEAD, or DELETE).
p_body	The HTTP payload to be sent as CLOB.
p_credential_static_id	The name of the Web Credentials to be used. Web Credentials are configured in Workspace Utilities.
p_scheme	The authentication scheme, Basic (default) or AWS or Digest or OAUTH_CLIENT_CRED if supported by your database release.

Navigate to Processes —>Create New Process.

Add below PLSQL code:

PLSQL Code:

DECLARE
      l_envelope      CLOB;
      l_report_bytes  CLOB;
      l_instance_url  VARCHAR2(100);
      l_username      VARCHAR2(50);
      l_password      VARCHAR2(50);
      l_report_bytes2 CLOB;
      l_result_blob   BLOB;
      l_result_3      apex_data_loading.t_data_load_result;
  BEGIN
      ----Payload
      l_envelope := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
  	<soap:Header/>
  	<soap:Body>
  		<pub:runReport>
  			<pub:reportRequest>
  				<pub:attributeFormat>csv</pub:attributeFormat>
  				<pub:parameterNameValues>
  					<!--Zero or more repetitions:-->
  					<pub:item>
  						<pub:name>P_INVOICE_NUM</pub:name>
  						<pub:values>
  							<!--Zero or more repetitions:-->
  							<pub:item>'||:p1_invoice_num||'</pub:item>
  						</pub:values>
  					</pub:item>
  				</pub:parameterNameValues>
  				<pub:reportAbsolutePath>/Custom/APEX/FUSION_BIP/XX Test AP Invoice Report.xdo</pub:reportAbsolutePath>
  				<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
  			</pub:reportRequest>
  		</pub:runReport>
  	</soap:Body>
  </soap:Envelope>';
      l_instance_url := 'https://fa-exjg-dev7-saasfaprod1.fa.ocs.oraclecloud.com/';
      l_username := 'intuser';
      l_password := 'Welcome123';
      apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name := 'Content-Type';
      apex_web_service.g_request_headers(1).value := 'application/soap+xml;charset=UTF-8';
  -- Get the XML response from the web service.            
      l_report_bytes := apex_web_service.make_rest_request(
          p_url         => l_instance_url || 'xmlpserver/services/ExternalReportWSSService',
          p_http_method => 'POST',
          p_body        => l_envelope,
          p_username    => l_username,
          p_password    => l_password,
          p_scheme      => 'Basic'
      );
      SELECT
          reportbytesdata.reportbytes
      INTO l_report_bytes2
      FROM
          XMLTABLE ( XMLNAMESPACES ( 'http://www.w3.org/2003/05/soap-envelope' AS "env", 'http://xmlns.oracle.com/oxp/service/PublicReportService'
          AS "ns2" ),
          '/env:Envelope/env:Body/ns2:runReportResponse/ns2:runReportReturn'
                  PASSING xmltype(l_report_bytes)
              COLUMNS
                  reportbytes CLOB PATH 'ns2:reportBytes'
          ) AS reportbytesdata;
      IF l_report_bytes2 <> '77u/REFUQV9EUwo=' THEN
          l_result_blob := apex_web_service.clobbase642blob(l_report_bytes2);
          l_result_3 := apex_data_loading.load_data(
              p_data_to_load => l_result_blob,
              p_static_id    => 'xxtest_ap_invoice_details_dd'
          );
      END IF;
  END;

Step8 ===>Run the Application.

Provide Invoice Number and click on Submit.

This will display the BIP report data into interactive report region.