Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

EBS – Integration/Interface options

Posted on September 7th, 2007 by Sanjit Anand ||Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today



Some time back in last post we have already seen the definition of interfaces and there types.Just to summarize here are the high points for interface :

  • Import data from any environment , including own system or old system or from data collection device or external system or any others in the easier way possible,
  • This consist of review the result of import run which as whole include verification ,validation, review, reimport and error identification

Guess the next question in your mind will be what are the options available for interfacing with oracle ERP or any other ERP? The way integration and interfacing works in typical commercial application have almost similar in nature. Lets emphasize some options that technology offer in Oracle application. But the option will be drived on the basis of nature of system and process how its works , moreover Cost is yet another driving factors. From the integration and interfacing these important factors are :

  • Frequency
  • Mode
  • Volume
  • Technology
  • Security ( accessing data)

Is this really driving the integration decisions for choice.? …The answer is YES, on the top of other factors like cost, support etc…etc.But how…lets understand what is really means and how they really affect in :

  • Frequency – The frequency is important in term of running the interface. Many times a interface need to run during month end processing, many times it runs in nightly basis as most of airlines industry updations happen in night only, or may be weekly only.
  • Batch – It should be real time, one time in day or one in batch …somehow it is linked with Frequency.
  • Volume – This is yet another important to understand what volume data need to interface. This is very very important which is affect how technology is helpful in handling such a big volume.
  • Technology : This is based out of all above three factors ,
  • Accessing data :What access is there to the data? Typically points considered here are like;
    • One way sync (Read Only)
    • vs. bi-directional (Read Write)
    • vs. access in place

Under these set of schema , a developer and designer have to take a call which options should he/she can go for interfacing?? Lets take some of the options :

Before exploring the some options, note that integration is broadly categorize into two types:

  • Data Integration
  • Application Integration

We are not going to discuss details her, lets take some of the options as for integration and interfacing in EBS.

Options 1: Open Interface Table (OIT)?

This is the the most standard way for doing interfacing in oracle application till day.This consist of developed interface table and programs. The underline interface table allow you to load data and then call concurrent program if any else use the standard AOL API’s and have a custom concurrent program. The standard/custom concurrent program will take data from the interface table validate it and the data into application base products table.

This kind of Open Interface Table satisfy in most of time, as design and development does not requires much efforts.In oracle each product has number of OIT available to cater most of interfacing/integration need.

Options 2 : Application Programming Interface(API’s)APIS

Many time we need a tighter integration to oracle application that can be provided with interface table. In those cases, Oracle Application API’s are indeed a good Options .APIs allow for tight integration into oracle application product. There are also 3rd party products that need to bolt-on into oracle apps with its integration capability. A typical API’s works as figure in the right.

Options 3 : Enterprise Application Integration Tools (EAI) /Middleware Tools

When there is no standard Oracle process (no interface tables) as well no API’s to import data.In that case, you have to write a program that inserts data directly into the Oracle Base Tables.Though this is not recommended by oracle as it any changes from oracle standard functionality may malfunction during patch or upgrade version. So there is another options where we can use some EAI or middle ware tool . EAI/Middle ware tools typically works as adaptor or connector which then take care of any insert /update in oracle base table without any risk.There are number of adapter available to integrate with oracle application.

These tools normally provide robust visual development GUI as well as have strong functionality to provide integration monitoring and alerting tools. I personally know Taviz( How good this is..i really like this ..sorry guys i am not sells person but Still i will recommode). Normally these tools are higher-quality than a options discussed in OIT or API’s.

There are EAI vendors which has capability with interface and integrate with oracle application. Some of them are oracle cerified partners.

  • Cast Iron
  • WebMethods
  • Tibco
  • Informatica
  • Blue Wolf
  • Taviz(formerly SmartDB)
  • Crossroads
  • SeeBeyond (formerly STC)
  • Vitria

Normally most of EAI products provide a tool called “Studio” which uses a graphical environment for you to design integration process flows called orchestrations (integrations). Integrations are deployed to a provided ‘Application Router’, which is an integration appliance pre-configured before it is installed at the customer’s site.Provides native ‘Connector’ to Oracle E-Business Suite.

Options 4 : EDI

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.This is also a good options. More details can be found in my last post.1.2

A bit on other options

Options 5 : BPEL Process Manager

  • BPEL stands for Business Process Execution Language (This was developed by IBM, BEA, and MSFT)
  • Development tools and server acquired with Oracle’s purchase of Collaxa Inc.
  • Requires extra licenses beyond Oracle application licenses
  • Uses industry standards, web services, and business process design to orchestrate integration development
  • Marketed very heavily by Oracle as their current and future integration standard

It is typically as similar to EAI but i personally found bit simpler. It Provides “Dashboard” for server maintenance and monitoring and moreover this is exposes Oracle APIs as web services. Only drawback is extra license and relatively higher learning curve may lead to higher initial costs

Options 6 : other Oracle Technology

By means of making a either PL/sql or stored procedure or hybrid process.

  • PL / SQL
    • Oracle Corporation’s proprietary server-based procedural extension to the SQL database language
    • Relatively sophisticated procedural programming language
    • Compiled to DB; allows for syntax checking and high-performance DB integration
    • Myriad of purposes in Oracle applications: application logic, integration logic, exception handling
    • Exposes an API layer to Oracle application logic
  • Stored Procedures
    • Can be implemented as server-side Java
    • Capable of calling PL/SQL using JDBC; capable of being called by PL/SQL
    • Higher computational performance than PL/SQL
    • Allows for use of an industry standard technology instead of proprietary PL/SQL
    • Often coupled with PL/SQL as a hybrid approach to Oracle application integration
  • Oracle Advanced Queuing (AQ)
    • Database Integrated Messaging
    • Integrated data, message, and message history
    • Recoverability(any point in time recovery)
    • Auditing,Tracking, Non-repudiation
    • Business Intelligence on message store
    • High Performance
    • Integrated with MQSeries via Oracle Messaging Gateway

Will take some deep drive for options 4 and options 5 in some other post.

Related Posts

Posted in API Integration | 5 Comments »Email This Post Email This Post |

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
5 Responses
  1. Greg Sessions Says:

    Which of these options works well with the Oracle eBusiness Suite? What Oracle products are required to do each of the different integration types?

  2. Sanjit Anand Says:

    Which of these options works well with the Oracle eBusiness Suite?
    >>> All these options are works well with Oracle Ebusiness suite.Design and selection of right option is one of driver of choice, and more over cost.

    Typically in real world, Interfacing via OIT and API is most easiest one, when you are dealing with similar system of database.

    On the other hand , in some cases corporate policy enforce you to choose other options like middleware. I have worked for Healthcare client, they had a corporate policy to use Middle ware for all intergration work other application. This can be best understood rather than directly calling the receipt creation api , it should be triggered by Middle ware servics call to do the action.Typical example is POS based integration with Receipting or creating Item receiving in there central store.

    Based out of my experince, airlines , banking Industry has lot to deal with middleware.

    Options for Middleware is good, but it impart some cost in maintance.The alternate of this use AQ technique of Oracle.

    This can be understood as: Customer has there own web based application of Order capturing, initial order is accepted by web based application, and you have around 1000+ orders/hr coming from Inet application, and you want to intergrate with oracle EBS for rest of functionality like Order fullfillment and logistic and same time you need to triger the information back to your web based application. Here is the case, of real time interfacing and stream of data is continouly coming in that case oracle AQ technique is one of best option you can use. The alternate of this is some Costly product like TIBCO, MQ series which has same purpose.

    Oracle BPEL Process Manager is now part of Oracle new Fusion Technology.

    >>>What Oracle products are required to do each of the different integration types?
    Only in the case of Oracle BPEL Process Manager, you need oracle other product.

    EBS will have OIT and API avaiable,no external product is required.
    Middleware, you need to have third party tool , either you can use oracle Partner products or oracle certified vendor products.

    AQ is Oracle technology, this is again no external product required rather you need to get use of some of API call to make things working. AQ is relativly as easy as Pl/sql to understand.

    Hope this is answer of your question.


  3. Jai Lalwani Says:

    Gr8 post Anand.
    We’re in such scenarios where we needed to create new Oracle Apps customer from out of a Portal.

    I think, YOu’re proposing AQ or EAI approach.


  4. Sen Says:

    We have a certain requirement in EBS and BPEL integration. Your suggestion on solution design is highly appriciated.

    We have the business process which includes many approvals and assignment of work to the people. But the customer wants the business data needs to be stored in EBS as a Service Request (Teleservice). Please help me to identify the solution approach for this scenario with BPEL.

    1. How and where the business will be driven.
    2. Where the approval matrix will be the best.


  5. Pablo Says:


    Which is the difference among “Open Interface Table”, “Open Interface View” and “Base table”? I guess the user can play with OIT and OIV but never see/use the base tables. Is there are any way (programatically) of distinguish OIT from Base Tables?

    Thank you.

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.