Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1126
  • Last Modified:

Connecting web application to Oracle ERP

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
javaCaravan0
Asked:
javaCaravan0
  • 3
  • 2
1 Solution
 
j_coreilCommented:
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
 
javaCaravan0Author Commented:
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
 
j_coreilCommented:
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
 
javaCaravan0Author Commented:
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
 
j_coreilCommented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now