Building Interface in Oracle Apps.

Building Interface in Oracle Apps.

Well it is a very common requirement to interface another system with Oracle Ebuisness suite. Be it a CRM database sending Orders to EBS(Ebusiness suite) or a payment processing system with nightly feeds.

Some considerations before building an interface to Oracle EBS.

1. Nature and type of data to interface (orders / customers etc).
2. Identify the sources for data  (1 or more source systems can exists).
3. If source is a database or a file or a web service
4. Attributes of data to load and mapping between source and target attributes
5. Interface frequency                (Will it be real time or batch , If batch - how frequent - daily/weekly etc)
6. Maximum data / instance       (100,000 records / instance or  100MB file  etc)
7. Decide to use Oracle Open Interface vs Oracle Public API.
8. How to link a target record with source, For example save source Order number in some flexfield to trace it back
9. If required to maintain history of interfaced records?
10. What to do for validation errors and unexpected errors (network failure/database down) etc.
11. How an error will be fixed and the process will be re-run.
12. How to transfer data between systems?

I will elaborate 12. How to transfer data between systems? , As this is the most imp technical decision for an interface.

There are various ways for data transfer

1. File Based Interface

Most common approach is to have a File based integration. In which the source database will have a outbound program which will extract the required data and create a flat file, the flat file will be FTPed to target system, which will be loaded to interface tables by an Inbound program and then Oracle interface program will get data into base table..

Advantages of this approach

- Source and target are loosely coupled and can independently modify their logic until the file format is kept intact.
- Reuseable solution , can add another source with no changes to target system.

- Easy to interpret and support by developers

- Files are archived and maintained . so its easy to track history

Disadvantages

- Can't have real Time integration.

- Lot of development involved both at source and target system.

- File transfer has risks of data corruption.. esp with special characters present in data.

2. DB Link - tightly coupled

   The target system will directly run queries on source database using db links and would populate oracle interface table (after validations). and then Oracle interface program (or API) will get data into the base tables.

Not the preferred approach for a recurring interface process, Generally used only for one time data loads.

Advantages of this approach

- Quick to develop .

- No change required on source database.

Disadvantages

- Tightly coupled systems, any change in source database tables would require the interface process to change
- Not Scalable

- Cannot have real time integration.
- Works only when source and target both are Oracle db.
- Not easy to maintain DB links, generally not suggested by DBAs.

3. DB Links - loosely coupled.

   The source system runs an Outbound process which extracts the data from base tables and populate an interface table. The target system runs an Inbound process which monitors interface table of source DB using DB link and process the records by Oracle Interface or Oracle API.

Advantages of this approach

- Quick to develop .
- Loosely coupled.

Disadvantages
- Cannot have real time integration.
- Not Scalable
- Works only when source and target both are Oracle db.
- Not easy to maintain DB links, genreally not suggested by DBAs.

4. Middleware solution
     The recommended approach for complex integrations, This involves having a middleware tool (Oracle SOA / IBM SOA/ TIbco / Informatica ETL/ODI etc). The middleware acts as a middleman between source and target, which provides flexibility to replace either source or target with no changes to interface logic which lies in middle tier. 
    Different middle ware solution can achieve the interface requirements in different ways. but underlying approach is to fetch data -> transform it -> load data

Advantages
- Loosely coupled (generally)
- Quick to develop
- Works with any source (DB or file or web service )
- Scalable
- Can have real time integrations

Disadvantages
- Different set of skills required to develop middleware components
- Another server + licence cost involved for middleware


5. Java Concurrent Program     

Very rarely used solution,but is handy. 
Create a java concurrent program. which would connect to source db using credentials(need to save source db credentials in FND VAULT or have JNDI setup at Application Server). It connects to source db and fetch the interface data and populate the oracle interface / call oracle APIs. This approach is fairly uncommon, I will elaborate with an example in my next post. 

 Advantages
- Tightly or Loosely couped ( based on approach)
- Quick to develop
- Works with any source (DB or file or web service )  

Disadvantages
- Cannot have real time integrations
- Not Scalable for other sources. 

Most of the interface would require to bring data from source, transform it, validate it and load.
So if you can build some generic (reusable) components example - for file load into tables, for sending notification mails, to trigger oracle interface program, to do basic validation(lookup+flexfields etc. It will be very quick to design and develop any new interface which comes your way.  

-Idris