Solved

Connecting web application to Oracle ERP

Posted on 2010-08-29
5
1,103 Views
Last Modified: 2013-12-19
I have an Oracle database driven web application running on JBOSS AP. It has its own database. This applicaiton requires to fetch/update few tables in Oracle ERP database. Some of the data that need to be updated has to be in real time and some data can be updated in batch mode on weekly basis. One approach to fetch/update the data in Oracle ERP is via DB links, but this approach is not feasible for our application for number of reasons for e.g. what will happen when the Oracle ERP database is down. I am trying to find what's the best way for my web applcation to be able to fetch data from Oracle ERP and also update the data in Oracle ERP? I would like to initiate the discussion at this forum and I'm sure it is going to be a long and interesting discussion.
0
Comment
Question by:javaCaravan0
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:j_coreil
ID: 33560388
Oracle recommends that you use the standard interface tables and APIs to interact with the Oracle EBS instances. Do you have a metalink account? If so you can go to the ETRM and find the tables that you are looking for or post on here which pieces of information you need and I'll provide some content.

If you google Oracle EBS Payables API or Oracle EBS Payables interface tables, you'll find examples of PL/SQL and regular SQL to pull information. It is STRONGLY recommended that any information that flows INTO Oracle EBS instances go through the interface tables. The data that flows out of Oracle EBS can come directly from the base tables but this is not usually recommended.
0
 

Author Comment

by:javaCaravan0
ID: 33576694
Thanks for the reply.

I don't have metalink account.
I'll need to use Inventory related tables in Oracle ERP for example: Material Usage Type, Check for on-hand quantity etc.
0
 
LVL 2

Expert Comment

by:j_coreil
ID: 33576844
Here are a few public links to Oracle's documentation on the supply chain APIs.

http://download.oracle.com/docs/cd/A85683_01/acrobat/115mfgoim.pdf - Manufacturing Related
http://download-west.oracle.com/docs/cd/A89549_01/acrobat/115omapi.pdf - Order Management Related

If you google "Oracle EBS APIs" or "Oracle Inventory Open Interfaces" or something similar you can find more. If you have a specific question when starting your development project let me know, otherwise there is a lot you need to start reading and getting familiar with such as which API to use. If inventory costs, on hand qty, available to transaction, and item information is what you need currently, I would focus on the APIs that start with "mtl". Get familiar with the mtl_systems_items_b table as well, that is a base table that stores a lot of item attribute information.
0
 

Author Comment

by:javaCaravan0
ID: 33591399
Thank for the valuable input.
I still need to read the pdf file but another related question that I have is:

What would happen when the Oracle ERP DB is not accessible to my web application for any reason, eg. DB is down or network is down etc.... how can such scenarios be taken care of.
0
 
LVL 2

Accepted Solution

by:
j_coreil earned 500 total points
ID: 33591697
If your intent is to build a web application that has necessary metadata inside the Oracle database, you will always have that issue. What most people do is cache the metadata locally within the web application. Oracle's application server as well as JBOSS have some pretty good web caches that can take care of this for you.

If you are asking about just business data that will need to be accessible from the Oracle database, you can handle this again by storing some of the data in a local data repository and only access it if the connectivity to the database is down. That would be a poor man's data failover.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now