Oracle Fusion invoking BI as webservice:

Integration is an obvious requirement as businesses migrate from legacy on-premise systems to Oracle Fusion Cloud.

During the migration, different businesses could have different requirements. For example, some would prefer to run a parallel system along with Oracle Fusion Cloud while others could opt for a co-existence solution to analyse Fusion data in their legacy systems. Additionally, a few others may require real-time data even before the integration is complete. For example, a legacy system where payroll is implemented, is expected to have real-time salary or absenteeism data.

Oracle’s SaaS applications provide robust integration functionality such as extract, BI, HCM data loader, and web services. For instance, Oracle Fusion’s inbuilt feature, a BI report, built in Fusion can be invoked by a webservice and can help to automate real-time data in a faster and efficient way.

BI as a Webservice

This feature can be used to extract data from Cloud applications for further analysis and for importing into external systems via XML, EXCEL, CSV, or other file types.

Oracle BI Publisher can then be used to export data that needs to be imported into external systems. It allows developers to build custom queries on transactional data, the output of which can be downloaded to Excel. Developers can also configure an OTBI or BI Publisher Report and generate the output based on their organisation’s specific requirements. Report output extraction can also be automated using report service.

Organisations can save a lot of cost and effort by automatically pulling BI reports and loading them to external systems as per business needs.

Oracle Fusion offers many such robust, scalable, and easy to plug-in features, however organisations must choose what suits them the most after careful consideration. At Fusion Practices, our team of domain experts help businesses to map their requirements strategically and build a solution that best serves their needs.

Below is a sample of a BI report created to get team leave details.

Report Path     : Custom/Human Capital Management/Interfaces /Leave Calendar/ Team Leave Calendar.xdo
Output format: XML
Parameter Name: From_Date ,To_Date

Fusion BI service can be leveraged to call BI report and get the output.

BI Webservices details:

1. WSDL : https://abctest.oraclecloud.com/xmlpserver/services/ExternalReportWSSService?WSDL

2. Method : runReport ()

3. Authentication : In this case, we have taken JWT based authentication, however, it can also be basic, which simply needs user and password.

Header: Authorization

Value: Bearer eyJ4NXQiOiJsdsadvSnBCTlFKeUZuemg4UkY2N3V3cFk9IiwidHlwIjoiSldUIiwiYWxnIjoiUlMyNTYifQ.eyJpYXQiOjE1Mzk3ODU4NjEcscsYiI6IjExMTU1NSIsImlzcyI6IlVPVE1fU0VSVkVSX1VBVCIsImV4cCI6MTU0NDk2OTg2MSwicHJuIjoiMTExNTU1In0.lAd_kkIM78Kl7cdJd_18AbvUPbNW7TsD2JXkw2KXRLsX1jtvnyINx0wzEKGTJqP0AUNyBz70eqbQnGHGKZrEtFyysDmlGpEzwAuxyO_Kx2O01Pi6cCjEMyOcnNimKjMnTscsdchFD-ZEMM1lng4Q0Xz9lWPC9RqGDxqM5SIgXTbxnNUh4srft_M7XaI9f9NuFsH6K1x9AHKMLlWlBANauINh5d4eNAivno7gvNnr29iD3Fd4Nqz-zwCMZb73s9GJ66gYBuU5j9rhHwbNfhLLka7R-kYfA_11kyZGfLhwn5AcsdcYGPTnajC7JhKqzVn05ywrLZ0C9oH_iSqw1llWSg

 4. Input 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:parameterNameValues>

               <!–Zero or more repetitions :–>

               <pub:item>

                     <pub:name>From_Date</pub:name>

                     <pub:values>

                     <!–Zero or more repetitions :–>

                     <pub:item>10-01-2018</pub:item>

                  </pub:values>

               </pub:item>

               <pub:item>

                     <pub:name>To_Date</pub:name>

                     <pub:values>

                     <!–Zero or more repetitions :–>

                     <pub:item>10-17-2018</pub:item>

                  </pub:values>

               </pub:item>

             </pub:parameterNameValues>

            <pub:reportAbsolutePath>Custom/Human Capital Management /Interfaces /Leave Calendar/Team Leave Calendar.xdo</pub:reportAbsolutePath>

            <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>

         </pub:reportRequest>

       </pub:runReport>

   </soap:Body>

</soap:Envelope>

5. Output : The output is always in decoded format ‘Base64’

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

cmFjbGUgQkkgUHVibGlzaGVyIC1EYXRhZW5naW5lLCBkYXRhbW9kZWw6X0N1c3RvbV9IdW1hbl9D

YXBpdGFsX01hbmFnZW1lbnRfSUNJQ0lfSW50ZXJmYWNlc19VT1RNX0xlYXZlX0NhbGVuZGFyX0RN

MV94ZG0gLS0+CjxEQVRBX0RTPjxHXzE+CjxESVJFQ1RTX0lEPjI5MzQ3ODwvRElSRUNUU19JRD48

RElSRUNUU19OQU1FPk1yLiBTZWVsYSBKeW90aGkgS2FseWFuPC9ESVJFQ1RTX05BTUU+CjwvR18x

Pgo8R18xPgo8RElSRUNUU19JRD45ODQ1NjM8L0RJUkVDVFNfSUQ+PERJUkVDVFNfTkFNRT5BbWl0

IEt1bWFyPC9ESVJFQ1RTX05BTUU+CjwvR18xPgo8R18xPgo8RElSRUNUU19JRD4yOTM0NzM8L0RJ

UkVDVFNfSUQ+PERJUkVDVFNfTkFNRT5Nci4gUyBOYW5kYSBLaXNob3JlPC9ESVJFQ1RTX05BTUU+

CjwvR18xPgo8R18xPgo8RElSRUNUU19JRD4zNDU2OTg8L0RJUkVDVFNfSUQ+PERJUkVDVFNfTkFN

RT5BcnV5c2ggQmhhdDwvRElSRUNUU19OQU1FPgo8L0dfMT4KPEdfMT4KPERJUkVDVFNfSUQ+MTI2

NTM4PC9ESVJFQ1RTX0lEPjxESVJFQ1RTX05BTUU+UmFnaGF2IEtoYW5uYTwvRElSRUNUU19OQU1F

Pgo8L0dfMT4KPEdfMT4KPERJUkVDVFNfSUQ+Njc0NTg5PC9ESVJFQ1RTX0lEPjxESVJFQ1RTX05B

TUU+U29uYW0gS2Fwb29yPC9ESVJFQ1RTX05BTUU+CjwvR18xPgo8R18xPgo8RElSRUNUU19JRD42

NTQzMjk8L0RJUkVDVFNfSUQ+PERJUkVDVFNfTkFNRT5TYWNoaW4gU2hhcm1hPC9ESVJFQ1RTX05B

TUU+dfeee+CjxH

XzE+CjxESVJFQ1RTX0lEPjI5MzQyMjwvRElSRUNUU19JRD48RElSRUNUU19OQU1FPk1yLiBNIE1h

bGxpa2FyanVuYSBSYW88L0RJUkVDVFNfTkFNRT4KPC9HXzE+CjwvREFUQV9EUz4=</ns2:reportBytes>

            <ns2:reportContentType>text/xml</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>

6. Steps to decrypt the Output (Base64):

• Copy the content between tags <ns2:reportBytes>
• Open the link https://www.base64decode.org/
• Paste the content and click decode

–Decoded Response

<?xml version=”1.0″ encoding=”UTF-8″?>

<!–Generated by Oracle BI Publisher -Dataengine, datamodel:_Custom_Human_Capital_Management_Interfaces_ _Leave_Calendar_DM1_xdm –>

<DATA_DS><G_1>

<DIRECTS_ID>12232</DIRECTS_ID><DIRECTS_NAME>Mr. xyz</DIRECTS_NAME>

</G_1>

<G_1>

<DIRECTS_ID>23242</DIRECTS_ID><DIRECTS_NAME>abc</DIRECTS_NAME>

</G_1>

<G_1>

<DIRECTS_ID>23232</DIRECTS_ID><DIRECTS_NAME>Mr. Test</DIRECTS_NAME>

</G_1>

<G_1><DIRECTS_ID>5453</DIRECTS_ID><DIRECTS_NAME>Mr. er</DIRECTS_NAME>

</G_1>

</DATA_DS>