What are Interfaces?
The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
How can be used?
Oracle provides flexible and flexible tools in the form of Interface programs.
When can be used?
To import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.
What are the types of interfaces?
There are two major types of Interfaces:
Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
Outbound Interface : These interfaces are used to transfer data from Oracle Applications to external systems.
Two other distinctions of Interfaces:
Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.
What is the Diference between Interface and Application Program Interface (API)?
Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.
What are the steps involved in Open interface logic?
First the data from the source application is loaded into a database table (called Interface table).
Then the provided validation program logic validates the records whether they are correct or not .
If the validation fails, the errors are transferred into another table (called Error Table).
If the validation succeeds, the correct records are transferred through a process into the destination application table.
Please describe difference between interface & sql loader?
SQL Loader is to just lift the data from xls or any format and shift the data to some interface table or staging table. Its a standard set of commands, which we use to lift and Shift the data from one place to another place as this is much faster.
interface on other side is a standard API provided by Oracle, which is combination of Interface table(s) and API to load the data into base tables.
SQL Loader is NOT a ideal solution to load data into base table.
What is Source Application?
You obtain data from a source application to pass on to a destination application for further processing and/or storage.
What are Source Data Issues we need to consider?
Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.
What is Destination Application?
You send data to a destination application so that the application can perform further processing and/or storage.
What is Interface Table?
For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.
What is Identifier columns?
Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.
What is Control Columns?
Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
WHO columns are also control columns.
What is Data Columns?
Stores the data that is being converted.Required columns store the minimum information needed by the destination application to successfully process the interface row.
What are Derived Columns?
Derived columns are created by the destination application from information in the required columns.
What are Optional Columns?
Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.
What is Error Table?
For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.
What are the Steps to develop interface Program?
1] Identification(check if any existing open interface to carry out this functionality)
2] Creation of Pre-Interface table ( staging Tables:A table in the format of the data file)
3] Load data into Pre-Interface table(SQL*LOADER can be used to load the flat file into the pre-interface table/staging tables).
4] Validate data in the Pre-Interface table(Null check,Foriegn Key check,Duplicate Check,Business Rule validation)
5] Mapping the values:
Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.
6] Load data into Interface table:
Once the data is as clean as you can get it, the data can be inserted into the Interface table.At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.
7] Run the interface program
8] Check for Errors
9] Report on the Interface.
What is Migration?
Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.
What is the process of Migrating of data?
Identify the data to be imported to new system (Business requirement).
Extract the data into flat file/Staging table and Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface).
What is Conversion?
Conversion of data means translating the data to suite target system (data should be formatted according to target system ) and then move the translated data using Interface Programs/APIs.
• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data.
What is the difference between conversion/Migration?
Below are the parameter to be consider.
Frequency
• Conversions/Migration are a one time event
• interfaces are ongoing
Occurrence in the project timeline
• conversions/Migration executed before production
• interfaces executed during production
Manner of execution
• Conversions/Migration are batch
• Interfaces may be batch or real time
Complexity
• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity.
• Coordinating with other systems make interfaces more complex
Maintenance
• Maintenance of interface is bit cost intensive task.
What is the need of Migration/Conversion?
Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk of data (sometimes millions or even more than that) that needs to be moved from one system to another and before moving the data it should be validated and only valid records should be entered into Oracle Apps.
The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
How can be used?
Oracle provides flexible and flexible tools in the form of Interface programs.
When can be used?
To import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.
What are the types of interfaces?
There are two major types of Interfaces:
Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
Outbound Interface : These interfaces are used to transfer data from Oracle Applications to external systems.
Two other distinctions of Interfaces:
Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.
What is the Diference between Interface and Application Program Interface (API)?
Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.
What are the steps involved in Open interface logic?
First the data from the source application is loaded into a database table (called Interface table).
Then the provided validation program logic validates the records whether they are correct or not .
If the validation fails, the errors are transferred into another table (called Error Table).
If the validation succeeds, the correct records are transferred through a process into the destination application table.
Please describe difference between interface & sql loader?
SQL Loader is to just lift the data from xls or any format and shift the data to some interface table or staging table. Its a standard set of commands, which we use to lift and Shift the data from one place to another place as this is much faster.
interface on other side is a standard API provided by Oracle, which is combination of Interface table(s) and API to load the data into base tables.
SQL Loader is NOT a ideal solution to load data into base table.
What is Source Application?
You obtain data from a source application to pass on to a destination application for further processing and/or storage.
What are Source Data Issues we need to consider?
Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.
What is Destination Application?
You send data to a destination application so that the application can perform further processing and/or storage.
What is Interface Table?
For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.
What is Identifier columns?
Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.
What is Control Columns?
Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
WHO columns are also control columns.
What is Data Columns?
Stores the data that is being converted.Required columns store the minimum information needed by the destination application to successfully process the interface row.
What are Derived Columns?
Derived columns are created by the destination application from information in the required columns.
What are Optional Columns?
Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.
What is Error Table?
For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.
What are the Steps to develop interface Program?
1] Identification(check if any existing open interface to carry out this functionality)
2] Creation of Pre-Interface table ( staging Tables:A table in the format of the data file)
3] Load data into Pre-Interface table(SQL*LOADER can be used to load the flat file into the pre-interface table/staging tables).
4] Validate data in the Pre-Interface table(Null check,Foriegn Key check,Duplicate Check,Business Rule validation)
5] Mapping the values:
Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.
6] Load data into Interface table:
Once the data is as clean as you can get it, the data can be inserted into the Interface table.At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.
7] Run the interface program
8] Check for Errors
9] Report on the Interface.
What is Migration?
Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.
What is the process of Migrating of data?
Identify the data to be imported to new system (Business requirement).
Extract the data into flat file/Staging table and Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface).
What is Conversion?
Conversion of data means translating the data to suite target system (data should be formatted according to target system ) and then move the translated data using Interface Programs/APIs.
• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data.
What is the difference between conversion/Migration?
Below are the parameter to be consider.
Frequency
• Conversions/Migration are a one time event
• interfaces are ongoing
Occurrence in the project timeline
• conversions/Migration executed before production
• interfaces executed during production
Manner of execution
• Conversions/Migration are batch
• Interfaces may be batch or real time
Complexity
• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity.
• Coordinating with other systems make interfaces more complex
Maintenance
• Maintenance of interface is bit cost intensive task.
What is the need of Migration/Conversion?
Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk of data (sometimes millions or even more than that) that needs to be moved from one system to another and before moving the data it should be validated and only valid records should be entered into Oracle Apps.
No comments:
Post a Comment