About Interfaces

What is a interface?
In terms of oracle applications interface is a communication channel that allows the data to move in and out of the system.

How many types of interfaces are there?
1.INBOUND INTERFACES
2.OUTBOUND INTEFACES

Inbound InterFace:The one which allows data to get into oracle application from outside is called inbound interface.

OutBound Interface:The one which allows data to get data from oracle applications to other systems is called outbound interface.

What are different types of inbound interfaces available?
1.Open interfaces
2.API's(Application Program Interface)
3.EDI(Electronic Data interchange)--Mainly used for automation transactions with third party systems
4.XML GATEWAY --Mainly used for automation transactions with third party systems
5.WEBADI--Used for uploading data from excel with the scope to have some validations--mainly used for one time loading..Just like sqlloader+validation program..
5.PLSQL Packages for Reading XML Data--Use this in case of importing non stadard transactions

What are different types of outbound interfaces available?
1.Custom programs where we pull data to a csv file using UTL_FILE in the required format
2.EDI
3.XMLGATEWAY
4.PLSQL Packages for generating XML

what is the difference between OPEN INTERFACE & API's?
OPEN INTERFACE:
I dont see much difference between the open other than the way we load data.
In case of open interface the data is loaded in to open interfce table like GL_INTERFACE or Sales Order Interface(OE_ORDER_HEADERS_IFACE_ALL).
Run the interface import program.
This will validate the data and put it into oracle applications.All the invalid records are marked as Error.
One thing is there are GUI Screens available for most of these interface where you check the errror message correct it there only and resubmit the interface.
From the technical perspective there are Error tables available for each interface

API:
API's are the oracle built packages where the validation logic is wrapped inside a package and the data is passed as parameters to the API.
Most of these api's use the PLSQL tables as paremeters to take the advantage of bulk binding concepts for faster loading data.
THey will have the two OUT parameterst to throw back the error code and message in case of data validation failure
Apis' are compartively faster than open interfaces.
If a API's and open interface are available it is better to load through API's.If the records are more.

What is the difference between data migration and conversion?
These are essentially the same.

What is the difference between data migration and interfaces?
Data Migration is a one-time activity however interfaces are ongoing processes that run regularly. Hence error handling must be well thought when designing interfaces.

Best practices for interfaces?
1. Usually an interface must have well defined error reporting mechanism.
2. Data errors must be fixed at the source once the transaction gets rejected.
3. If your support team is often modifying transactions in interface tables using sql, then your interface design is flawed.
4. If you log support issues with any interface on a relatively regular basis, then its time to re-visit your design.
5. Interfaces must be designed in a manner such that, once the original errors are fixed, those transactions must get re-processed during the next run (or on demand)
6. If possible an interface mechanism/infrastructure must be in place, so that all interfaces are written in similar manner.
7. There must not be any hard coding for mapping from source system data to EBS. You can either use oracle's lookup screen to define mappings, or use a custom mapping screen.
8. In some cases you will have a generic screen which facilitates one-to-one, one-to-many, many-to-one, many-to-many mappings.
9. There must exist a simple mechanism to identify the duplicate processing of any transaction must exist. One way this can either be achieved is by making your source system specify a unique identifier for each record.
10. Do not store references to rowid in any part of your interfaces.
11. Think upfront, design and write your interface in such a manner that once it goes to production, you never receive an email to fix any stuck records. If at all you do receive data fixing requests, then ensure that interface program is changed in a manner it gets handled without programmers intervention in future.
12. Have proper debugging. You could add a parameter for debug flag, so that debug messages aren’t generated unnecessarily. Surely, don't forget to use FND Logging, which is delivered out of the box by Oracle.
13. Make the concurrent program end with warning in case of errors. This can be done by passing retcode=1. Use the out-of-the-box concurrent program notification facility can be used to inform end users of the errors encountered during processing.
By doing so, you can make the monitoring automatic, i.e. when submitting/scheduling interface concurrent program, you can attach a workflow role.


What tools can I use for point to point interfaces?
Point to Point interface methodology is not ideal for a large Organization. However to keep the costs low, when number of interfaces are very low, then Point to Point Interface approach could be justified. However please note that Organizations do grow in size , specially after implementing Oracle ERP. Hence Point 2 Point Interfaces can not be a long term strategy.

Please find p2p interface approach listed:-
1. File tables..
   --You can map the structure of an ASCII file to table structure.
2. XML Gateway
   --You can use XML Gateway and XML Message designer if you have too many interfaces having XML source. Although keep in mind that there may not be any future for XML Gateway in Fusion applications.
3. Sql*loader
   --Avoid for interfaces as error reporting is not user friendly. Use this for data migration as SQL*Loader is very efficient.
4. DB link for intra company different Oracle systems
  --DBA's often raise questions regarding security of database links. However security concerns can be minimized by making them non-public and make them connect to not specific schemas with minimum privileges.
5. Java concurrent program with jdbc
If your source system is in a database like SQL*Server, or DB2 or any other database which supports JDBC, this approach could be ideal.
The source system can create a specialized schema and expose the necessary views or tables/synonyms, such that you can pull the data from that system, load it into your system using API. All this can happen seamlessly, all within one program.
6. Java concurrent program with FTP, csv parser
All the above steps can be done within one single program.


Usual bad practices:-
1. Each developer writing their own mapping tables and screens.
2. Hard coding in interface
3. No thoughtful process for Error Reporting and Error Correction(deviation from fix at source philosophy)
4. Calling SQL*Plus sessions from Host programs, without due considerations for APPS Password Security
5. No debugging
6. Each interface program using its own File Dequeue mechanism.

Long term interface strategy using Oracle EBS Delivered tools
Oracle Warehouse Builder [Preferably 10g Version]
This strategy means that you move away from point to point interface design. I think a simple tool like Warehouse builder can be used as a simple single point interface control. Some powerful features of Warehouse builder means that you can do mappings, transformation of data structures without writing your own code. Warehouse Builder appears to be a part of Fusion strategy, hence it is a good investment. Warehouse builder can be used to pull the data from various sources using ODBC, JDBC, DB Links etc. Hence you can leverage a Warehouse Builder interface design run interface end-to-end that includes submission of the Interface API.
Limitation:- This is a batch mechanism, and not messaging based.

XML Gateway
If you want an Oracle delivered tool to implement interfaces using messaging, you can use XML Gateway with Message Maps. But investment in this tool may be lost when you move to BPEL based architecture.

Web Services
Wait for Fusion that integrates BPEL, unless you wish to manage Web Services in a standalone manner.