#732 Fusion ERP Batch Extracts with OIC

Simple example and background explanation here.
Caveat to begin with - I am not a Fusion ERP expert, but I do like understanding how things work.

So what is a batch extract?
Essentially a scheduled process (ESS- Enterprise Scheduler) that runs a report in ERP e.g.
Extract payment data since last execution to update downstream or upstream applications to reflect payments.

So two components here - the scheduled process and the report to be run, according to that schedule.

Where can I monitor the execution of these scheduled processes in Fusion ERP?

Here is a job I ran earlier, triggered by an OIC integration
(we will look at the integration in detail later) -

Payables Transactions Extract or sliocht idirbhearta iníoctha, as we are wont to say in Ireland.

However, there are many more of them -

A scheduled process might have parameters that you can set to control which records are included or how they're affected. For example, a process updates only the records that are effective within the date range that you define.

So where are these ESS processes or jobs defined?

Search for Manage Enterprise Scheduler Job Definitions -

As you can see from the above, I then searched for Payables Transactions Extract -

The job is of type - BI Publisher. That means there is a BI Report defined already.

Here are the BI Report details -

So net, net is you begin with a BI Report, either one of those delivered with ERP or a custom report of your own. You then call this from an ESS job.

I have a simple example which covers the whole lifecycle -

The interaction is as follows -

1. OIC Trigger integration: Trigger the job to execute the report.

The job payload will also specify a callback url - which enables a 2nd integration to be called once the job has run successfully and the result is in UCM.

2. Fusion ERP: The report executes

3. Fusion ERP: Result is written to UCM

4. Fusion ERP: Callback Integration is triggered

5. OIC: Result is picked up from UCM and sent to destination.

OIC Integration triggering BI Report run

initiateExtract: Here we will leverage the Fusion ERP exportBulkData operation. This submits an ESS job to start the BIP report processor and eventually uploads the report output to UCM. The supported output formats are XML and CSV. Callback and notification are also supported.

The Request Payload - 

The parameter list refers to the PaymentsTransactionsExtract: Parameters screenshot above.

Here is a sample input for the parameter list -

92,/oracle/apps/ess/financials/commonModules/shared/common/outbound;PayablesTransactionsExtract,BIPREPORT,FULL_EXTRACT,#NULL,300000046987012,#NULL,#NULL,#NULL,#NULL,#NULL,12-18,N,N,300000046975971,#NULL,#NULL,#NULL,FULL_EXTRACT,#NULL,#NULL,#NULL,PayablesTransactionsExtract,#NULL

jobOptions will be set to ExtractFileType=ALL

notificationCode is set to 30

callbackURL is set to 
nn/ic/ws/integration/v1/flows/soap/DEMO_ERP_BULK_EXTRAC_CBK/1.0/'

this points to my callback integration, more about that later.


The Response will be the requestId of the submitted ESS job.

I execute the integration and view progress in ERP - Scheduled Processes -

I check the response received from the initiateExtract call -

Note the requestId returned - 1615660.

OIC Callback Integration - picking up result from UCM

This integration picks up the extract result from UCM.

Remember, we have passed the url to this integration as a parameter in the invoke of exportBulkData 

Let's look at the individual steps -

bulkExtractCallback:

The trigger is based on a SOAP connection, which itself is based on the following wsdl -

As you can see, the request contains the following -

 "requestId", "state", "resultMessage"

Note: requestId = 1615660.

downloadFile: leverages a SOAP connection, based on the Fusion ERP wsdl

The operation selected is getDocumentsForFilePrefix.

Details can be found here -

prefix is set to “ESS_” + //requestId

makes sense!

account is set to fin$/payables$/export$

comments is set to "processedby=" , //requestId  

The Response is as follows - (this is from a different run )

The file is written to my ftp server -