This article assumes that you have already read the article on data migration, if not the click here.

What is common between Data Migration and interfaces?
Both essentially use API or open interface tables. See Data Migration Article

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.

Comments (20)

Subscribe to this comment's feed

...
written by balkrishna , December 11, 2006

can u give the link or place where i got the information regarding warehous builder

thank anil

regards
balkrishna

...
written by Anil Passi , December 11, 2006

Oracle have published plenty of examples on http://www.oracle.com/technolo...index.html

Click on this Oracle link that will take you straight to Examples on Warehouse Builder.

Thanks
Anil Passi

...
written by balkrishna , December 11, 2006

can u give the link or place where i got the information regarding warehous builder

thank anil

regards
balkrishna

...
written by Anil Passi , December 11, 2006

Oracle have published plenty of examples on http://www.oracle.com/technolo...index.html

Click on this Oracle link that will take you straight to Examples on Warehouse Builder.

Thanks
Anil Passi

...
written by VENU , March 27, 2007

hi Anil...
How to develop interfaces with legacy and internal systems?

...
written by Anil Passi , March 28, 2007

Hi Venu

Internal systems? Please elaborate what do you mean by that.

For Legacy systems, ah, my article is quite outdated now that we have ESB and BPEL technologies to use. You can use ESB to poll files/external systems for data and load those in staging tables via ESB itself. alternately you may even decide to do your mapping from legacy to current system within ESB.

I am assuming that your question is in educational context, as it will take time for your management to agree on implementing an enterprise bus.

thanks,
anil

...
written by sai , March 29, 2007

Anil,

Sorry i am still confused about interfaces.
1] I have two schemas in same oracle instance. I have raw data in one schema and i write a pl/sql concurrent program to pump data from raw data schema into HR schema.

OR

2] I have data in HR schema and write the same data into a flat file.

I have checked irep.oracle.com, it has the regular APIs.

I might be completely wrong. Please can you explain using a dummy example.

sai.

...
written by Susan , May 21, 2007

Can you tell me if there is a full list of all the Open Interfaces? For previous versions of the Apps there was an Open Interface Guide. It seems to have disappeared.

Thanks.

...
written by vijay , June 20, 2007

Hi Anil,

Thank you so much for sharing your explicit as well as tacit knowledge with us.

Can you pls take an example of a typical conversion ( Say only Item conversion, not it s categories,category sets, revisions etc.) and explain the whole process right from the extract file , because I could not understand some of the concepts like derivartion, translations during the conversion process.

Thanks in advance for your help.

Regards,

Vijay

...
written by Karthik , June 20, 2007

Hi Anil

Nice blog.

I did not get the rationale behind best practices point 10.

Can you please explain why it is not good to use ROWID reference?

Thanks
Karthik

...
written by Anil Passi , June 20, 2007

If the Database is exported, and then re-imported, then new rowid values will be allocated to each rows.

ROWID is internally managed by Oracle. Some DML operations can cause movement of records in disk, hence givning new ROWIDs

Thanks
Anil

...
written by ramakrishna , June 28, 2007

hi,
i want Order Management material, currently i am trying on oracle applications as a Technical Consultant.

...
written by Arun Reddy , July 04, 2007

Hi,

I want to know the difference between API and Interfaces??

Regards

...
written by Anil Passi , July 13, 2007

Hi Arun

When using API you get a handle to error messages within your main process itself.
However, in case of interfaces, you need to first populate data into interface tables, and then run some batch process.

The data gets pushed into oracle tables directly from the API, and the api will do all possible validation for you.

In case of interface tables, validations take place when you try to run the import process.

Of course it is elegant to use API based approach.

Thanks,
Anil Passi

...
written by Prapoorna , July 30, 2007

Hi,

Thanks for the information you provide. I have a question. We are trying to integrate our legacy systems with Oracle Applications. We are using BPEL to do our integration. While designing the BPEL process and creating the Applications Adapter I see that there are different options to choose: XML Gateway, Open Interfaces, API's etc.

My first question is do all these methods insert data into interface tables or do any of these insert data all the way to base tables. If they all insert into interface tables then how do we decide on when to use what? Say between XML Gateway and Open Interfaces.

Really appreciate if you can let me know this.
Thanks
-Prapoorna

...
written by Anil Passi , July 30, 2007

Hi Prap

BPEL is the preferred tool these days for interfaces [high-frequency low volume].

These adaptors usually do not insert data directly into the base tables.

What is your specific requirement, which interface? I will have a look.

Thanks,
Anil

...
written by PS , September 27, 2007

Hi Anil,
Nice information. I had a basic questions .
- Are API and Open interface same ? if no then what is the difference between them.

Thanks
Prashant

On-Hand Quantity conversion
written by nag daka , December 18, 2007

Hi All,

I have a requirement regarding data conversion.I am pretty much new to data conversion,could any body let me know how to start it.They provided me one script which is fetching data from database.I need to update org i.d.They have some newly defined org I.ds.I came to know the api what i need to consider is 'inventory transaction open interface'.could any body get back me quickly as it is very urgent requirement....

On-Hand Quantity conversion
written by nag daka , December 18, 2007

Hi All,

I have a requirement regarding data conversion.I am pretty much new to data conversion,could any body let me know how to start it.They provided me one script which is fetching data from database.I need to update org i.d.They have some newly defined org I.ds.I came to know the api what i need to consider is 'inventory transaction open interface'.could any body get back me quickly as it is very urgent requirement....

inventory transaction open interface
written by Siva Prabhu , January 22, 2008

Dear nag

For the data conversion, The step need to follow for open interface is follows

The flow is

datafile --> Temp table --> interface table--> base table.

If there is no modification in Data then use standard API.


Use Sql loader in toad to upload the datafile to temp table

in the loader file
i) datafile
ii) control file (save a new notepad as datafile name.ctl)
iii) log file
iv) bad file
V) discard file

except ctrl file others are auto generated one.

Once data file insert in to temp table, Update as per the requirement by using the procedure to interface table.
then run the standard CP.

regards

Siva prabhu