API’s or Open Interface

"Most of us are / have had worked with API’s & Open Interfaces. Both do the similar job, but have we ever wondered why would we use API’s when it can be done using Open Interface or vice versa? And, which is better" ?

....So, here you go.

..My dear friend, there is hardly any difference the way both is working,do check my previous post. Anyway here is back to basic :

What are Open Interfaces?
The term Open Interfaces actually refers a programming interface, usually a database table, that automates the execution of Oracle APIs.

Open Interfaces provide a single, simple interface for a specified business procedure.

What are the Oracle APIs?

These are called as a collection of “Black Box” interfaces that provide programatic access into the Oracle ERP database.

The term API refers to stored procedure driven interfaces, where you call a stored procedure to perform an action within an Oracle Module, and the data from your external application is passed through the stored procedure’s parameters.

Why use Open Interfaces?

• In EBS one Open Interface may run many API calls.
• Open Interface run asynchronously.
• The good is that if there is failure of record, they remain in the table until either fixed or purged.
• They automate the interface into the APIs.
• This requires less work and less code as few SQL DML would simply .

Why use APIs?

• When there is no corresponding Open Interface.
• Normally all Oracle APIs run synchronously, and provide immediate responses, therefore machism to be provided to handle such situation.
• That requires custom error handling routine.
• This may requires lot more effort as these need fine grain control approach.

Remember, the APIs are also used by the front end screens, and in the same way, will require all the appropriate prerequisites to be implemented.

Important to note, you cannot use APIs as an alternative to implementation.

Conversion is assumed as one time activity but never looks like small activity which can be performed with couple of day

How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

• Frequency
  • conversions are a one time event
  • interfaces are ongoing
• Occurrence in the project timeline
  • conversions executed before production
  • interfaces executed during production
• Manner of execution
  • conversions are batch
  • interfaces may be batch or real time
• Complexity
  • Conversion does have very complex, its totally depends upon the data mapping activity.
  • coordinating with other systems make interfaces more complex
• Maintenance
  • Maintence of interface is bit cost intensive task.

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

• An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.
• A typical inbound interface would follow these steps:
  1. Extract data from legacy system into a flat file.
  2. Use SQL*Loader or equivalent tool to upload information into a temporary table.
  3. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
  4. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

Outbound Interfaces

o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
- Write a PL/SQL program to extract data from Oracle base tables into a flat file.
- Use a custom program to read that data and post it into the legacy system

Do we have some other standard way to do interface?

• Open Interface is a table based interface registered as a concurrent program
  • process records in batches.
  • spawned(Pro-C) or PL/SQL based programs.
• API (Application Program Interface) is a parameter based stored procedure
  • directly impacts base database tables.
  • may be called from Oracle open interfaces,Forms, Reports.
• EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO's, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.(
• Enterprise Application Integration (EAI) solutions are often used when complex integration requirements exist.

What Is An Open Interface Table (OIT)?

• For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
• Open Interface Tables are standard Oracle tables.
  • Oracle uses OITs to provide a simple interface to Oracle base tables.
  • Oracle has list of all the open interface that oracle offered in there product.

Oracle Interface Program

• Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
• These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
• Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
• Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms, etc…).

Examples of standard import programs:

• GL: Journal Import
• AP: Payables Open Interface
• AR: Customer Interface
• INV : Item Import
• AR - Autoinvoice

Ok, thats is all about Conversion and Interfaces briefing. I will write some more for Tools used for Conversion/Interface and will discuss some granular details about a conversion/migration project and share some information about how and where AIM's documents fits into conversion and Migration projects. So watch out this space for some more stuff for conversions.. Till than..your comment and requset you to share some information relaeted to these areas