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

How to query a record from Oracle Table , which you just created ??

How to query a record from Oracle Table , which you just created ??

You created a record(Emp/Cust .. etc) from Standard UI for you application
which would have gone to a Database table. You know the table name. But how to fetch
the record you created??..

Well, Seems like very basic... It is if you have the ID of the record (But usually UIs doesn't show the ID), You may query with the Emp Name (or Cust Name) which you created. But If there are no index on Emp Name or Cust Name column, than your query might take years to fetch you a result.

In most of the cases DB Table have creation_date columns, Which can be used like

SELECT * FROM XXX_EMPLOYEE WHERE CREATION_DATE > SYSDATE - 1/12

(1/12 day = 1 hour , So this query will fetch all records created in last one hour).

But if there are no Index on Creation_date column and XXX_EMPOLOYEE table has huge records, this query will also take hours to execute...

An Alternative approach

Step 1 : Find the DB sequence which is used to populate ID column in this table.

The sequence is generally named as the table itself.

SELECT * FROM all_sequences WHERE sequence_name LIKE 'OE_ORDER_HEADERS%'

SEQUENCE_NAME
--------------
OE_ORDER_HEADERS_S

So for a table name OE_ORDER_HEADERS, the Sequence is found to be OE_ORDER_HEADERS_S
(In fact if you are using Oracle Applications, This is the standard _S is the Sequence Name).

STEP2- Get the sequence NEXTVAL

Now once we have the sequence used for the table,Lets get the value sequence generates.

SELECT OE_ORDER_HEADERS_S.NEXTVAL from DUAL;

NEXTVAL
-------
22450141

STEP3- Since you created a record from UI (Or from Other Apps) in past few hours. Not much sequence should have been used after that. So lets try to query table with ID column having a value 1000 less than sequence. Also now we can join what all information(creation_date, employee_name etc) we have, to filter to exact record.

SELECT * FROM OE_ORDER_HEADERS where header_id > (22450141-1000) and creation_date > sysdate - 1/24

This should return the record(s) which you are looking for and should not take more than a few seconds.

I find this way really handy, esp while working with Oracle Applications

Getting Started with OAF(Oracle Application Framework) Development

Prerequisites
Basic Knowledge of Java
Basic Knowledge of XML
Database understanding
Oracle Applications knowledge

OAF(Oracle Application Framework) is a framework to build/customize Web pages for Oracle Applications. The framework is based on the industry standard MVC architecture and its very flexible.

MVC (Model, View, Controller) is a concept to build Web pages, you can read more about it at
http://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller#Description

In OAF
Model is B4CJ (Business Components for Java)
BC4J should have all the business logic and is responsible to manipulate data
and communicate with database.

Controller is Controller (Also called CO)
Controller is linked to a page. and it handles all the page events (button clicked).
and page navigation.Controller is also responsible for dynamic rendering of the page.

View is XML ( JRAD xml)
In OAF the view(jrad xml) is declarative and the xml is stored in database (mds repository)
Because of its declarative nature any page element can be added/removed from the page xml at runtime.