Project / Context
In an ever changing business environment, the need for adopting and building more robust processes in an ERP implementation projects is not only essential but also very crucial for the success of the implementations. The requirement was to seamlessly integrate the Oracle R12 Applications with various other systems. The Interface needs to handle both history data migration as well as replication of ongoing data. Since the volume of history data was expected as very high, the performance of the Interface program needed to be at the optimum level. Since the data needs to flow into Oracle R12 system through various Interfaces from various systems, the business cannot afford to take any failures at Oracle end.
PurposeFor the requirement of building various interfaces in order to integrate seamlessly between Oracle R12 ERP system and various sub systems as well as external systems, we have developed a robust interface design solution which can be adopted by others as well.
By following these guidelines, it would make the application maintenance smoother, flexible, productive, and also ensures seamless integration in an Oracle ERP Applications environment. Suggested solution includes various other functionalities such as usage of lookups for data mapping, error-handling, audit trails, parameter driven debug mode, etc.
Interface Approach
There are two interface development approaches: Open Interface Tables and Application Programming interfaces (APIs).
Open Interface Table Approach
This is the traditional approach where Oracle provides an Open Interface table(s), module wise and entity wise to bring the data from external systems into Oracle Applications. In this approach, the Open Interface table(s) needs to populated, then the respective standard concurrent program need to run to import the data into Oracle base tables. A detailed flow chart of this approach can be found in the subsequent slides.
Application Programming Interface [API]
In this approach, Oracle provides module wise and entity wise published PL/SQL packages called APIs to import the data into Oracle base tables. This provides a flexibility to back out the transaction at any time programmatically prior to issuing commit command. A detailed flow chart of this approach can be found in the subsequent slides.
1 Fail All Fail Concept
This approach, works only for API based Interfaces as it provides the program level control on saving or backing out the data. The only exception to this is GL Interface, as it works with Group Id concept, means the Batch of Journals are submitted as one Batch with a
Group Id, so by naturally as per the Journal Import concurrent program, the way it’s designed, it either imports all the Journals or none of a given Batch. This is explained again in details in a separate section below.
