Oracle Interfaces and Conversion

How we Define Data Conversion

• Process where existing data from the client's old system is extracted, cleansed, formatted, and installed into a new system.
• These can be manual or automated.
• The big difference is that these are One-time only process that requires extensive testing and preparation.
• They must be executed and performed before a system goes into production.

What Is An Interface then

• These are programs for connection between Two Systems In Order To Synchronize the Data.
• They can be Manual, Batch or Real-Time.
• Used Repeatedly and Should Therefore Be Designed and Constructed In the Most Efficient Manner Possible.
• These can Be Triggered by an Event (Such As Running A Concurrent Program) Or It Can Be Scheduled To Run At A Certain Time.
• Can Be Very Costly To Construct And Maintain.

Does the conversion/migration/interface have Life Cycle

Yes, they have, because they have a significant efforts required in development and design and implementation

• Functional Designer works with business owners to determine the data mapping and complete the functional design using the Design Templates.
• If the interface/conversion is automated, the Technical Designer converts functional requirements into technical specs for the construction of the interface programs.
• The developer uses the functional and technical designs to build and test the interface/conversion programs.
• More rounds of testing are done until the interface/conversion is migrated to the production environment for deployment.

Conversion is assumed as one time activity but never looks like small activity which can be performed with couple of days.

How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

• Frequency
• conversions are a one time event
• interfaces are ongoing
• Occurrence in the project timeline
• conversions executed before production
• interfaces executed during production
• Manner of execution
• conversions are batch
• interfaces may be batch or real time
• Complexity
• Conversion does have very complex, its totally depends upon the data mapping activity.
• coordinating with other systems make interfaces more complex
• Maintenance
• Maintence of interface is bit cost intensive task.

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

·         An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.

·         A typical inbound interface would follow these steps:

1.      Extract data from legacy system into a flat file.

2.      Use SQL*Loader or equivalent tool to upload information into a temporary table.

3.      Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.

4.      Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

Outbound Interfaces

o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
- Write a PL/SQL program to extract data from Oracle base tables into a flat file.
- Use a custom program to read that data and post it into the legacy system

Do we have some other standard way to do interface?

• Open Interface is a table based interface registered as a concurrent program
• process records in batches.
• spawned(Pro-C) or PL/SQL based programs.
• API (Application Program Interface) is a parameter based stored procedure
• directly impacts base database tables.
• may be called from Oracle open interfaces,Forms, Reports.
• EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO's, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.(
• Enterprise Application Integration (EAI) solutions are often used when complex integration requirements exist.

What Is An Open Interface Table (OIT)?

• For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
• Open Interface Tables are standard Oracle tables.
• Oracle uses OITs to provide a simple interface to Oracle base tables.
• Oracle has list of all the open interface that oracle offered in there product.

Oracle Interface Program

• Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
• These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
• Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
• Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms, etc…).

Examples of standard import programs:

·         GL: Journal Import

·         AP: Payables Open Interface

·         AR: Customer Interface

·         INV : Item Import

·         AR - Autoinvoice

Conversions –

A conversion does follow there own methodology , being a typically methodology it consist of certain task and subtask identified at sub activity level . Here are some of them as discussed below.

1. Movement of Data or Transport of data

This is where you have to plan the movement of data from an external system/old system to Oracle Applications which normally consider within a conversion project plan. The more important is developing a detailed conversion plan for each entity, listing all design, development, testing, and conversion tasks. You have also include resource,software, and hardware requirements to successfully convert each entity.

2.Design Process

This is where you have to decide what need to convert. This start with identifying all objects first there corresponding volume.

• Examine the business objectives and requirements to determine the data to be converted.
• Specify time constraints for the conversion, especially for transaction data.
• Determine the appropriate conversion method, it is not recommended to go for manual entry.If data volume is low , try to find alternate product.
• Then need is performing data mapping.
• Then its required to install all hardware and software required for the conversion process.
• Determine the testing requirements. Identify testing method if available in Oracle else design a custom query to compare the result.

3.Developing Programs

This process does consist of :

• Writing extract and import programs.
• Scripts to create any interface or translation tables in Oracle RDBMS.
• Writing validation, translation, and migration programs.
• Write verification scripts and reports.

4. Performing Conversion

This is process in which major activity is performed, this consist of:

• Extract and format data.
• Create temporary interface tables.
• Upload data to interface tables.
• Run translation programs & validation programs.
• Migrate data into production tables.
• Run verification scripts.
• Run application reports to verify converted data.

5. Data Verification

In this phase each converted entity, design a conversion process from data extraction through data verification. Main consideration is business objectives and dependencies for each point in the process.