eBS integration versus Fusion integration

Integratie EBS versus Fusion

Traditionally a lot of customization was developed around Oracle Applications, although the idea has Always been ofcourse to use flexfields and later form personalization wherever possible.
However, there was Always the need for additional logic using PL/SQL packages and in some cases database triggers (try to avoid it!), additional tables to store data which would be exposed through custom build forms using Oracle Forms and all kinds of integration to external systems.

In this blog I like to elaborate on custom inbound and outbound integration with eBS and also how this would work in Fusion.
Note, my purpose is not to describe ALL integration option that Oracle may offer through XML Gateway,EDI, Standard webservices BPEL, etc, but I aim for the traditional eBS custom developer and functional consultant who like to get an overview idea on the differences between integration of eBS and Fusion.

Inbound integration eBS

For inbound integration we use the schema as shown below.

The external application delivers a file, for example in CSV, which we load with SQL Loader in a custom (maatwerk) table. Then we enrich, validate using a PL/SQL package and fill the Open Interface or call an API directly. And if you are really in to it, you also directly call the import program in your PL/SQL package and use a wait for request to see the result. The import will eventutally pick up the Open Interface records and fill the actual Oracle tables.

Around this you can build a host script that ties the components together and call them sequentially or you use a request set (Load, Validate, Import).

In newer releases webservice options were added and through Integrated SOA Gateway you could easily build your own APIs in PL/SQL and expose this as a webservice. In R11 you could also do this without SOA Gateway, but you simply exposed your package as an URL.

Ofcourse you have much more advanced integration techniques using middleware, messaging services, etc.

So the image of above can be adapted to

Our SQL Loader program, the host script run on the application server of our eBS installation and the custom table, the validation program and the SOAP services are running on the database server.
All inside eBS.
So in a cloud or Fusion solution this will not work anymore ..

Outbound integration eBS

For outbound integration we use the schema as depicted below. Data can be made available through views, which can be queried using database links or we export the data through either custom programs (PL/SQL) or BI Publisher to support multiple export formats like XML, Excel, CSV, etc.

Here also the custom components (views, export programs, BI Publisher reports, your own custom webservices) are in eBS.

So what changes with Fusion?

Now we have Fusion this is no longer possible. We cannot develop in the cloud/fusion application itself (fusion on premise is slightly different of course). We can integrate with it of course. So our schema would look more like this ..

Inbound integration Fusion

From the external application you can either directly call webservices or you can use File Based Data Import and upload a program to UCM (Universal Content Manager) so it can be imported into Fusion.

In OER (Oracle Enterprise Repository www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html) you can download excel templates where you can place data and generate a zip file which can be uploaded directly to UCM or through a webservice.

For a detailed description download the document Using External Data Integration Services for Oracle Cloud in OER (https://fusionappsoer.oracle.com/oer/custom/r10/fbdi/commonfiles/External_Data_Integration_Services.pdf).

Problem with this approach is that all pre-processing has to take place between the extracted data from the client and what Oracle accepts. This is the proces we usually build using SQL Loader, custom tables and custom enrichment and validation.
You can still follow this scenario, but now it has to be done outside the Fusion application. So either in an external application or in an environment in between. This could be a PAAS environment. For
example use the Java Cloud or SOA Cloud to do your enrichment and transformation in BPEL.
So you would need the Database Cloud to define your custom tables where you load the data from the external system (like our staging tables), build validation logic in PL/SQL and add a BPEL process to fetch additional information from Fusion Cloud to do your enrichment. You may use BI Publisher reports to do your own custom queries and fetch internal IDs or do validation in your process.
Then when you have finished validation and enrichment, you create the files to be uploaded and send them to Fusion using your BPEL process and place them in UCM. Now submit the import process.

From a BPEL like environment you can also directly call the FinancialUtilService webservice (see section Flow Automation Using Web Service in above mentioned document, operation UploadFiletoUCM).

Outbound integration Fusion

Then finally outbound integration with Fusion. In a lot of custom solution we fetch data from eBS and use that either in processing or custom screens and processing. That's not possible this way, but we can fetch the information from Fusion using existing webservices or custom written queries in BI Publisher or OTBI.

Running a BI Publisher report can be invoked through the FinancialUtilService webservice (operation submitEssJobRequest), after which the output is available in UCM and can be fetched with operation getEssJobStatus to see the status of the request and downloadESSJobExecutionDetails to fetch the output as a zipfile.

This method of downloading can also be used to fetch data and store it locally for further processing. See for a more detailed description Hakan Biroglu's blog on this subject (http://hakanbiroglu.blogspot.com/2014/11/leverage-power-of-bi-publisher-in-cloud.html#.VjAH1jiFM6Y).