Whenever we want to fetch huge amounts of data from BIP report (More than 10MB) in OIC, we usually get payload limitations error.

To avoid this error, we have below two options:

(A) Schedule BIP report and send File to FTP server and then read data from FTP in segment in OIC.

(B) Retrieve BIP report data into chunks using SOAP API WSDL.

In this post, we will see how to call BIP Report output in chunks to read large volume data from SOAP WSDL.

Let us consider the BIP report from fusion instance:

Query for this report:

SELECT 
  INVOICE_ID,
  VENDOR_ID,
  INVOICE_NUM, 
  INVOICE_CURRENCY_CODE,
  PAYMENT_CURRENCY_CODE,
  INVOICE_AMOUNT, 
  VENDOR_SITE_ID,
  INVOICE_TYPE_LOOKUP_CODE, 
  SOURCE,
  APPROVAL_STATUS
  FROM AP_INVOICES_ALL

This report doesn’t have any parameters and will provide a huge amount of data while running from SOAP WSDL.

Let us run the report.

To call BIP report using SOAP API, we have below WSDL:

xmlpserver/services/ExternalReportWSSService? wsdl

Let us configure this WSDL in SOAP UI to get response.

Execute SOAP requests using runReport method.

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: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>

Whenever we pass sizeOfDataChunkDownload =-1, it fetches complete data from BIP report as shown in screen shot.

To fetch the data from BIP report in chunks, we need to pass sizeOfDataChunkDownload =0.

When we pass sizeOfDataChunkDownload =0, it will generate output file on UCM server and will return report ID to fetch data in chunks.

Let us pass sizeOfDataChunkDownload =0 in above request payload.

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:reportAbsolutePath>/Custom/APEX/FUSION_BIP/XX Test AP Invoice Report.xdo</pub:reportAbsolutePath>
              <pub:sizeOfDataChunkDownload>0</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/>
              <ns2:reportContentType>text/plain;charset=UTF-8</ns2:reportContentType>
              <ns2:reportFileID>documents/xmlpwssvpcpkr5Rd8172457218441553167.tmp</ns2:reportFileID>
              <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>

Now to fetch BIP report data into chunks for UCM generated file, use downloadReportDataChunk method and pass report ID got in earlier step.

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:downloadReportDataChunk>
           <pub:fileID>documents/xmlpwssvpcpkr5Rd8172457218441553167.tmp</pub:fileID>
           <pub:beginIdx>0</pub:beginIdx>
           <pub:size>500</pub:size>
        </pub:downloadReportDataChunk>
     </soap:Body>
  </soap:Envelope>

• fileID: Report File ID generated by runReport operation in earlier step.
  
• beginIdx: this indicates from where to start reading data.
  
• Size: It defines the size of data chunk to be downloaded in single call. It takes value in Kilo Bytes.
  

For Response:

• reportDataChunk: Report data bytes for chunks in base64 format.
  
• reportDataFileID: Report File ID which is passed in request payload.
  
• reportDataOffset: It indicates the last index fetched from BIP report data.
  

Let us decode base64 reportDataChunk  

Now again run downloadReportDataChunk method by changing the beginIdx to get next set of chunk data.

Again, decode reportDataChunk to get next set of chunk data.

We can repeat these steps until we get complete BIP report data.

Once all data is fetched, it will show reportDataOffset as -1 in response.