OIC - How to create BI report in Oracle ERP

Following 2 steps to perform:

• Create custom data models
• Create the Report based on the data model.

Create custom data models:

Log in to Oracle Fusion Application.

At the very first time, creating a “Data model” and “Reports” follow the below steps.

Step1: Go to “Reports and Analysis”. -> Navigation: Navigator -> Reports and Analysis. And then Click the Browser Catalog Icon to Open BI Catalog to Get the BI Publisher.

To Create

1. To Create a new BI Publisher Data Model, Let’s Select New from the toolbar. >> And then the Data Model. 
2. From new Data Set dropdown List, Select SQL Query Name the New Data set as Invoice_id, and Select the Appropriate Data source. Let’s build the query based on the Subject area tables and Not on the Physical Tables. Paste the Applications SQL Query to Defines application names, each mapped to a unique auto-generated ID, for users and roles. Then Click OK After Selecting OK, Columns from the Query are Displayed.
3. Navigate to Data Tab. Click to View, A sample set of Data is Shown. We can Able to save this dataset as sample data.
4. We can able to Save the Data Model in a custom folder so that our custom data models and reports will be preserved during upgrades. Click to Save Icon. Saved in POC folder as "Generate_invoice_id"

Create BI Publish Reports:

Now Let’s Create the BI Publish Report, Based on the newly created Data Model. The Data Model is Already Selected. Click to Next.

1. Choose the table layout and include all data columns for the report.
2. Drag Fields from the Data Source to Create the Table.
3. We can able to Customize the Report. Now Select the View Report and Click to Finish. After Finishing, Select the Path to Save the Report. After Saving, the Created Report will show.

We can also able to Export the Report to Corresponding formats.

Detailed screenshots:

OIC - Invoke ERP BI report | Create ExternalReportWSSService wsdl connection in oracle integration

Usecase: Here, we will see the steps to invoke a BI Report in an Integration

• Create ERP SOAP Connection
• Create an integration and configure BI report Invoke
• Mapping the required fields
• Decode the BI base64 response

Create Connection : 

Here we will configure  " ExternalReportWSSService WSDL URL " which can be used to run a report. The same connection we will use in below steps while developing integration.

1. Navigate to connection window and select SOAP adapter, give any meaningful name for connection.
2. Enter the ExternalReportWSSService WSDL URL  and Select the TLS Version. TLS Version depends on your ERP Cloud Application Instance. Mostly it is TLSv1.1WSDL URL Format : https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL
3. Choose Security policy as Username Password token and Enter the *Username and *Password of the Oracle Cloud Application Instance. Click SAVE and TEST the Connection.

Steps with screenshots:

Create Integration :

Create one Scheduled Integration by giving some meaningful name >>  Search for the connection which we have created above and select it. Enter some meaningfull name(ex: invoke BI Report) and then click NEXT. >>  Select the operation = runReport and then click NEXT >> In headers configuration window, select Accept attachments in response. Click Next

SOAP Endpoint configuration completed. Click Done.

Mapping the required fields:

Now let's do the MAPPINGS. We will pass all the required fields to invoke BI report ( like absolute Report Path, size of data chunk download, Report Parameters Name and its Value ,attribute template etc.)

For my case I have used the following:

reportAbsolutePath* element =Custom\Integrations\Inbound\POC\eTEXT_INV_ID_SEQ.xdo

sizeOfDataChunkDownload*="-1"

You can also use the following paramters as per your requirement:

Expand the "parameterNameValues" element

Expand "Item" and then enter your BI Report Parameter name in name* element 

Now expand values* and map or pass Parameter Values in Item element .

Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values.

Decode Base64 BI report response:

Use decodeBase64(reportBytes) and store in a variable and then it can contain carriage return so we need to use a custom javascript and remove the carriage returns to get the actual data.

Use my post to know how to remove carriage returns using JS - oic-javascript-to-remove-carriage-return

Inside this step we will enable the tracking and do one round of testing - Enable the Tracking - BI Report call configuration completed. Click save and Close the integration edit window - Activate the integration - Submit the integration by clicking Submit now and then click on Instance Id to track the flow - Once processing succeeded , open the flow by clicking start time - Click on active stream and check the payload received as output from report

OIC - ERP - How to get Job Package and Name

Perform the following steps: 

• From Fusion Apps main page, Click the Navigator, and others then click Setup and Maintenance.

• On the Setup page, select your setup offering such as Financials

• Click on right icon as shown above to search for "Manage Enterprise Scheduler Job Definition and Job Sets for Financial, Supply Chain Management and Related Applications":

• Select and Click "Manage Enterprise Scheduler Job Definition and Job Sets for Financial, Supply Chain Management and Related Applications"

•  and search for "%Invoice%" in display name.

• Select "Import Payables Invoices" row and click edit (please do not change anything as our goal is to get job package and name):

• Now you can see the job package is the PATH and job name is the NAME.

•  These will be the first two columns in your property file for "Import Payables Invoices" with third column the sample prefix name of the zip file as suggested earlier: 

oracle/apps/ess/financials/payables/invoices/transactions/,APXIIMPT,APInvoices, <list of parameter separated by comma>

From User Properties tab, we can see the number of arguments to send. For invoice , it is 14.

OIC - how to show the import response id to the activity stream for better support

 To show the import response id to the activity stream for better support, 

we just need to perform following steps:

• Add teh logger activity and provide a proper name like "CaptureImportId" 
• Then mention Log as "Always" 
• Mapthe response import result.

OIC - How to handle more than 10 mb file or large file poll

How to handle more than 10  mb file or large file poll???

If we use ftp or file adapter to poll files from a directory, it can only allow to poll or read file content max 10MB file.

So in order to read large files(>10 MB),  we’ll have to download the file to a Virtual file system or a stage location as it supports 1 GB data poll but  if you want to read the content, again it supports 10MB , so using file read in segment , best option to read contents in segments or smaller chunks and then marge it to create full target specific file.

Basically, we need to follow the following sequences:

1. First list the files using ftp.
2. Then for each file
3. Download each file to stage location
4. Using stage activity, read file in segments. In esch segment it support 200 records to process.
5. Create the required file format and marge each segment to create full file.