Integrating Legacy Applications with Oracle Applications via Oracle Fusion Middleware

I have been managing an integration requirement to automate the transactions sync between Oracle Applications E Business Suite MFG module and legacy mainframe application.

The solution is built on Oracle Fusion Middleware (OFM) SOA Suite (BPEL and Web Services), to interface between the Oracle Applications E Business Suite (Oracle Apps) and the legacy application.

Manufacturing related transactions - Item Master, Shipment, Purchase Order, Shop Order, Cost, and Internal Sales Order are created out of Oracle Apps into FTP site associated with the Oracle Apps. OFM sweeps them moving to legacy FTP sites. Programs on the mainframe load them into legacy application.

Internal Sales Order, PO Receipt, Safety Stock, Sales Order, Shop Order, and Inventory are synced from legacy applications to Oracle Apps. For this, first the files are moved from legacy FTP to the FTP site associated with Oracle Apps. Next the files are loaded into Oracle Apps using the standard EBiz APIs via Concurrent Requests.

Oracle Fusion Middleware Adapter for Oracle Apps provides the following interface types:
1. XML Gateway
2. e-Commerce Gateway
3. Business Events
4. Concurrent Programs
5. Interface Tables and Views
6. PL/SQL API

More about each of these interface types for another time...

In the current solution approach, combinations of option 4 and 6 concepts are used.
1. A PL/SQL script (Stored Procedure) acts as an abstraction layer between OFM and Oracle Apps.
2. The PL/SQL determines which concurrent request to submit within Oracle Apps based on the transaction specific file name that OFM provides as a input parameter.
3. The PL/SQL returns the status of the concurrent program execution along with the ‘request id’ as a return parameter.

Internally within OFM, the Stored Procedure invocation is encompassed into a Web Service. A WSDL is generated within BPEL Process Manager for the Stored Procedure. So whenever a particular transaction is synced from legacy to Oracle Apps, the Web Service is invoked within OFM passing the file name as a parameter. This Web Service invocation is in synchronous mode. The resulting ‘request id’ is communicated to system users via OFM as an email notification.

With this approach, the concurrent program details for each of the listed transactions and the required user responsibility are abstracted to the OFM. OFM uses just a database user account. Any changes in the concurrent programs, or its parameters, etc., does not warrant a change in the OFM, rather are isolated within the PL/SQL.