How to call Oracle fusion ESS job from OIC

In this blog I will list the Steps to Call ESS Job/BIP Report from  OIC. Many a time we have requirements where we need to extract data from Oracle Fusion and send it to 3d party or take some action.

There are many way to extract data from EBS , eg.

1. GET APIs
2. BIP Report 

BIP Report give me lot of flexibility over APIs , as I can write a required sql to extract data from fusion table and then create an ESS job to run my BIP report

ESS Job - An ESS Job in Oracle Fusion is a type of job that is used to run reports and other processes on demand or on a schedule. ESS stands for Enterprise Scheduler Service, and it is a service that is used to manage and run jobs in Oracle Fusion.

BIP - Oracle Business Intelligence Publisher (BIP) is a reporting tool that is part of Oracle Fusion. It allows users to create and publish reports from a variety of data sources, including Oracle Fusion applications, external databases, and flat files. BIP reports can be generated in a variety of formats, including PDF, Excel, CSV, and XML.

Below are the steps that I usually write to trigger an ESS job ( to run BIP report) from OIC

Note : Though we have SOAP based API to call the BIP report from OIC, I most of time preferred to call ESS job from OIC , rather than calling API to run BIP report. One advantage of doing that ESS job is , we can design a asynchronous OIC flow .

Calling ESS job from OIC involved 2 OICS flows

1. Flow-1 : This flow do the Initial validation work ( for example Grab the last run date of report ) , and the triggered the ERP cloud adapter and submit the ESS job.
2. Flow-2  :This is event based flow and keep listening to ERP cloud adapter about the Report Completion , as soon as ERP cloud raise the Job completion event , this flow will catch it and do initial validation ( to make it pick the correct event) and initiate the 2nd flow.

End to End Steps : 

1. Create a BIP Report Data Model (DM)
2. Create a BIP Report (Report)
3. Create ESS report ( for your DM and Report)
4. Create OIC flows( 2)

Flow -1 : This is very simple flow  and trigger ESS job, usually involved 

• Call to ERP Adapter 
• Configuring ERPIntegration Service
• Operation : ExpportBulkData
• Passing ESS Report Name to ERP adapter (Job Name)
• Make sure you set Job Options  "EnableEvent=Y" , other wise Oracle SaaS cloud will not trigger call back event ( Once ESS job completed)

Flow -2 :This flow will trigger when ESS job completed

• Create Event based flow, this will keep listening to ERP Integration Outbound events 
• Make sure you add filter for you job , so that this OIC flow only trigger when your ESS job completed

• Once this OIC flow triggered , it will invoke ERP adapter to get the  output of ESS job from the UCM Server
• Unzip the file ( extracted from UCM server)
• Read the file 
• Send that to 3rd party ( in my case it was loading data into a database table) . we can use SFTP/Database /File adapter for this.