Solved

Connecting web application to Oracle ERP

Posted on 2010-08-29
5
1,112 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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
V.simple question about Android packages and libraries 1 36
SQL Developer 6 75
Error querying database link from Oracle 10g to Postgresql 7 96
add more rows to hierarchy 3 33
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 recover a database from a user managed backup

685 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