There are many businesses scenario where we need to Invoke Oracle BIP Report in Oracle Integration (OIC) flow.
Few are:
- Retrieving configuration data such as Business Unit (BU), Location, Department, or transactional data like employee attributes, etc.
- Storing similar data in databases such as Autonomous Transaction Processing (ATP), Autonomous Data Warehouse (ADW), Oracle Database, MySQL, etc. by establishing connections through Oracle Integration Cloud (OIC).
- Sending email attachments or notifications through OIC.
- Uploading files to a third-party sFTP server.
Another common use case involves developing Visual Builder Cloud Service (VBCS) pages that integrate with OIC, which, in turn, invokes Business Intelligence Publisher (BIP) reports. When working on custom page development through VBCS, accessing data directly from Oracle Fusion (SaaS) applications may not be feasible. In such cases, the recommended approach is to create an Oracle Integration (OIC) to call the BIP report and retrieve Fusion data. OIC can then return this data to the VBCS page as a response payload. Alternatively, an Oracle Integration can be invoked to fetch Oracle Fusion data by calling or invoking the BI report. This data can then be inserted into a database table. By creating Oracle REST Data Services (ORDS) services for the database table data, the data can be directly accessed in VBCS.
Mandatory:
1.BI SOAP API URL: https://<server>/xmlpserver/services/v2/ReportService?wsdl
Replace <server> with your Fusion URL.
2.Fusion Credentials: To call the BI SOAP API.
3.Required Roles in Fusion: Integration Specialist
Once you have all prerequisites, you can start your integration with Oracle Integration Cloud.
- Define Connection for BIP Report in OIC
To define a connection, Go to ICS Home à Integrations à Connections
- Click Create button from Connections page.
Search SOAP and select SOAP adapter.
- Enter below information and click on Create button.
- Name: Meaningful Connection name of your choice.
- Identifier: It will automatically get picked from the Name. However, you can update this
- Role: Select Trigger and Invoke
- Populate Fusion Credentials created in Prerequisite step 2
- Click Test button from upper right corner then click Validate and Test.
- If all details are valid, then your connection will reach 100%.
- Now you can use this SOAP Connection as a Trigger / Invoke point in the ICS Integration.
- Create Integration:
To call BIP report, first you need to create an OIC Integration. Here we will create Scheduled Integration using the above configured SOAP connection.
Create Scheduled Integration.
- Enter below information and click on Create button.
- Name: Meaningful name of your integration.
- Identifier: It will automatically be populated based on Name chosen.
- Version: It will define the version of your integration.
- Description: Optional field.
- Package: To group same kind of Integration into single logical package.
Drop BI SOAP Connection just besides the Schedule Activity.
From the wizard enter the endpoint name, select Next. From the Next screen select Operation name “runReport”, select Next. From the Next screen Configure Headers (Non Mandatory) information and finish the wizard.
Open the Mapper and map the request parameters.
Here are some of the Parameter which you need to use while calling BIP report.
- attributeFormat: It defines the output format of the requested report. Valid values are pdf, rtf, html, excel, excel2000, mhtml, csv, data, flash, and powerpoint.
- attributeLocale: It defines the Locale for the report.
- reportAbsolutePath: It defines the absolute path to the report in the BI Publisher repository. For example: /HR Manager/HR Reports/Employee Listing.xdo.
- sizeOfDataChunkDownload: Set this parameter to -1 to return all data back to the client.
- parameterNameValues: You can pass the report parameters and its value dynamically using this field.
- BIP report will return report Bytes in encoded format, to read the data first we need to decode and then we will assign it to some variable for future use.
- Drop Assign Action into Integration template and click on Create button after populating below information.
- Name: Meaningful name of the Assign activity.
- Description: Optional Description.
Add variable by clicking on right bottom Plus(+) icon.
Provide Meaningful name to added variable and click on update value icon.
From components panel at bottom left corner search for decodeBase64 and drag and drop to the Expression builder, then take the reportbytes from BIP Report SOAP connection response and pass it to decodeBase64 function.
- Click on Validate (Upper Right corner) and Save and Close your Integration.
Integration is completed now, and you can Activate the integration.
To run the Integration, click on Adhoc icon or Schedule your integration from Scheduler menu.