Introduction

I have worked on several projects where we used APEX to integrate data to and from Oracle Fusion ERP. APEX running on the OCI APEX or Autonomous Database Service is a great option for extending and integrating with Oracle Fusion ERP Cloud.

In this post, I will describe how to call Oracle Business Intelligence Publisher (BIP) reports developed in Fusion using APEX and how to consume that data in the Oracle database.

Why BI Publisher Reports?

As you may know, Oracle has many REST web services that you can use to get data from Oracle Fusion ERP. When writing this post, the financials module alone had 178 (and counting) REST APIs. Even with all these APIs, there are still reasons why we may need to use BI Publisher to get data:

• A REST (or SOAP) API does not exist for the data you want to extract.

• You want to fetch data from multiple sources simultaneously to avoid multiple REST service calls.

• You need real-time access to the data.

Use Case

We have an APEX Application that was developed to simplify the entry of manual GL Journals. Completed Journals are posted to Fusion using the ERP Integrations REST API. This API loads a File Based Data Import (FBDI) CSV file into the GL_INTERFACE table and runs the Journal Import ESS job. This is all great, but if Journal Import fails, you need a way to fetch the failed journal lines from Fusion ERP to show to your APEX end user. There is no REST API to extract failed Journal Import records, so we need to use BI Publisher to get the data.

Building the BIP Report

In this section, I will review the BI Publisher report. I won't go through it step by step, but I will highlight the key points in creating a BIP report for consumption with APEX.

Data Model

SQL Query

All BI Publisher reports start with a data model, and the data model starts with a SQL Query. In this case, the SQL Query is simple. We want to fetch the GL interface lines for a specific group_id, where the status column is not equal to 'P' or Passed Validation.

SELECT group_id
,      je_line_num
,      status
,      status_description
,      TO_CHAR(accounting_date,'YYYY-MM-DD') accounting_date
,      segment1
,      segment2
,      segment3
,      segment4
,      currency_code
,      entered_dr
,      entered_cr
,      user_je_source_name
,      user_je_category_name
,      reference1         batch_name
,      reference4         journal_name
FROM   gl_interface
WHERE  group_id = :p_group_id
AND    NVL(status,'P') <> 'P'

The data model SQL query looks like this:

We have one parameter which we have called p_group_id.

Data Model Properties

We need to change two properties of the Data Model:

• Enable CSV Output - Checking this property (along with some settings we will make in the template) improves performance by causing BI Publisher not to first generate XML and then transform that to CSV.

• Include Parameter Tags—If you do not uncheck this property, BI Publisher will include your parameter values as columns in the CSV output, unnecessarily increasing the size of the output CSV file.

After generating sample output for the data model, you can move on to creating a report for the data model.

Report and Layout

After generating a report and layout for your data model, we must select the output formats the report will support. You can do this by clicking the 'View a list' link from the report page:

Next, uncheck all output formats except for 'Data (CSV)'. This, along with the Data Model Property 'Enable CSV Output', ensures your BIP reports will run as fast as possible.

You should now be able to run the report and view the result:

Why CSV?

You may wonder why I chose CSV as the output format, not XML (JSON is not an option). The reasons for this are:

1. CSV files are typically 5 times smaller than the equivalent XML files.

2. BI Publisher has to do less work to generate a CSV file.

3. Although it will be faster to parse XML in the database (because of the native XML parser), the larger file size of XML files makes up for this in most cases.

Calling from CURL

Before we can call BI Publisher from APEX, we need to understand how the BI Publisher End-Point works. We can do this by calling it using a curl command.

curl --location 'https://xyz.fa.us1.oraclecloud.com/xmlpserver/services/ExternalReportWSSService' \
--header 'Accept-Encoding: x-gzip' \
--header 'SOAPAction: submitRequest' \
--header 'Content-Type: application/soap+xml;charset=UTF-8' \
--header 'Authorization: Basic oZGd0NiM=' \
--data '<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:parameterNameValues>
              <pub:item><pub:name>p_group_id</pub:name><pub:values><pub:item>1234</pub:item></pub:values></pub:item>
             </pub:parameterNameValues>
             <pub:attributeFormat>csv</pub:attributeFormat>
             <pub:reportAbsolutePath>/Custom/Financial/Interfaces/AJE/GL_INTERFACE_ERRORS/GL_INTERFACE_ERRORS_RPT.xdo</pub:reportAbsolutePath>
             <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
          </pub:reportRequest>
       </pub:runReport>
    </soap:Body>
</soap:Envelope>

• The location / URL is the BI Publisher End-Point for your Fusion environment.

• The Authorization Header contains the Base64-encoded username and password of the Fusion user you are using to call the report. This user must have access to run the report in Fusion.

• reportAbsolutePath in the payload is the complete path to your BI Publisher Report.

If all goes well, you will get a response like this:

<?xml version="1.0" encoding="UTF-8"?>
<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>[Base64 Encoded Report]</ns2:reportBytes>
        <ns2:reportContentType>text/plain;charset=UTF-8</ns2:reportContentType>
        <ns2:reportFileID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
        <ns2:reportLocale xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
        <ns2:metaDataList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
      </ns2:runReportReturn>
    </ns2:runReportResponse>
  </env:Body>
</env:Envelope>

The reportBytes field contains the Base64 encoded result of running the report.

Running the Report from APEX

We can run the BI Publisher report from APEX using APEX_WEB_SERVICE.

DECLARE
  l_soap_payload  VARCHAR2(32000) := q'[
<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:parameterNameValues>
              <pub:item><pub:name>p_group_id</pub:name><pub:values><pub:item>85000023</pub:item></pub:values></pub:item>
             </pub:parameterNameValues>
             <pub:attributeFormat>csv</pub:attributeFormat>
             <pub:reportAbsolutePath>/Custom/VHG Custom/Financial/Interfaces/EXT-FIN-265_APEX_Journal Entry/GL_INTERFACE_ERRORS/GL_INTERFACE_ERRORS_RPT.xdo</pub:reportAbsolutePath>
             <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
          </pub:reportRequest>
       </pub:runReport>
    </soap:Body>
</soap:Envelope>]';
  l_xml_response       CLOB;
  l_base64_csv         CLOB;
  l_csv_blob           BLOB;
BEGIN
  -- Set HTTP Headers.
  apex_web_service.set_request_headers
   (p_name_01        => 'Accept-Encoding',
    p_value_01       => 'x-gzip',
    p_name_02        => 'SOAPAction',
    p_value_02       => 'submitRequest',
    p_name_03        => 'Content-Type',
    p_value_03       => 'application/soap+xml;charset=UTF-8',
    p_reset          => true);

  -- Call BI Publisher.
  l_xml_response := apex_web_service.make_rest_request 
   (p_url                  => 'https://xyz.fa.us1.oraclecloud.com/xmlpserver/services/ExternalReportWSSService', 
    p_http_method          => 'POST', 
    p_body                 => l_soap_payload,
    p_credential_static_id => 'FUSION_SERVICE_ACCOUNT',
    p_scheme               => 'Basic');

  IF apex_web_service.g_status_code = 200 THEN
    -- Parse the XML from BIP and Extract the Base64 Encoded Report Content.
    SELECT x.reportBytes INTO l_base64_csv
    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_xml_response)
           COLUMNS
             reportBytes CLOB PATH 'ns2:reportBytes') AS x;
    -- Convert the Base64 Encoded CLOB to a BLOB
    l_csv_blob := apex_web_service.clobbase642blob(l_base64_csv);
  END IF;
END;

We should end up with a BLOB l_csv_blob containing just the CSV output from the report.

APEX Web Credential

We can use an APEX Web Credential to store the Username and Password of the Fusion Cloud ERP Service account we use to call the BIP Report. In the same code above, the Web Credential Static ID is FUSION_SERVICE_ACCOUNT.

Parsing the Response

Now that we have the CSV in a BLOB, we can easily parse it and consume it using APEX_DATA_PARSER.

SELECT TO_NUMBER(col001) AS group_id
,      TO_NUMBER(col002) AS je_line_num
,      col003 AS status
,      col004 AS status_description
,      TO_DATE(col005,'YYYY-MM-DD') AS accounting_date
,      col006 AS segment1
,      col007 AS segment2
,      col008 AS segment3 
,      col009 AS segment4
,      col010 AS segment5
,      col011 AS segment6
,      col012 AS segment7
,      col013 AS segment8
,      col014 AS currency_code
,      TO_NUMBER(col015) AS entered_dr
,      TO_NUMBER(col016) AS entered_cr
,      col017 AS user_je_source_name
,      col018 AS user_je_category_name
,      col019 AS batch_name
,      col020 AS journal_name
,      TO_NUMBER(col021) AS request_id
,      TO_NUMBER(col022) AS load_request_id
FROM   TABLE(apex_data_parser.parse 
              (p_content            => l_csv_blob,
              p_file_name           => 'BIP.csv',
              p_file_type           => apex_data_parser.c_file_type_csv,
              p_fix_excel_precision => 'Y',
              p_detect_data_types   => 'N',
              p_skip_rows           => 1));

Conclusion

I wish there were a better way of consuming the results of SQL queries from Fusion. In an ideal world, the Fusion team would include Oracle REST Data Services (ORDS) in the stack and allow us to create REST APIs with our own SQL, providing convenience and additional capabilities like pagination.