Data Migration
Data Migration is the process of transferring data
between one system to another system. Data migration is required when
organizations or individuals change computer systems or upgrade to new systems,
or when systems merge (such as when the organizations that use them undergo a
merger/takeover).
To achieve an effective data migration in oracle, data on
the old system is mapped to the oracle database format. Data migration may
involve many phases but it minimally includes data extraction where data is
read from the old system and data loading where data is written to the oracle
system.
After loading into Oracle, results are subjected to data
verification to determine whether data was accurately translated, is complete,
and supports processes in the new system. During verification, there may be a
need for a parallel run of both systems to identify areas of disparity and
forestall erroneous data loss.
Automated and manual data cleaning is commonly performed
in migration to improve data quality, eliminate redundant or obsolete
information, and match the requirements of the new system. Data migration
phases (Design, Extraction, Cleansing, Load,
Verification) for applications of moderate to high complexity are
commonly repeated several times before the new system is deployed.
Data conversion is a bit different from data migration
in the sense that in data conversion we convert data form one format to another
format in the different version of the same system. Example: When we upgrade
from Oracle 11i to Oracle R12 we do a data conversion from 11i to R12
Interface is similar to both the data migration
and conversion in the sense it involves the movement of data from one system to
another system but its requirement in business is completely different.
Interfaces are written to transfer data on a regular basis where as most of the
data migration and conversion job are one time activity.
Following examples distinguishes all the above three
process
- Importing customer / supplier / item
information from legacy – Data Migration
- Moving customer / supplier / item information from
11i to R12 – Data Conversion
- Importing/Exporting PO/SO/WIP information from one
system to oracle on a daily basis – Interface Job
Important APIs/Open Interfaces
Customer APIs
hz_party_v2pub.create_organization
Org Contact Role Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility
hz_party_v2pub.create_organization
Org Contact Role Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility
Item Import
You must import items into the Item Master organization before you import items into additional organizations. You can accomplish this by specifying only your Item Master organization on a first pass run of the Item Interface. Once this has completed, you can run the Item Interface again, this time specifying an additional or all organizations.
Populate these tables first..
MTL_SYSTEM_ITEM_INTERFACE
MTL_ITEMS_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization).
This is the only value currently supported by the Item
Interface.
Set the PROCESS_FLAG to 1 (Pending), so that the Item Interface can
pick up the row and process it.
The program sets the PROCESS_FLAG to 7 (Import
succeeded) or 4 (Import failed) or 3 (validation failed). A row is
inserted into the MTL_INTERFACE_ERRORS table for all failed rows.
When the Item
Interface imports an item, it also assigns the item to the mandatory category
sets based on the item defining attributes. The default category for each
category set is used. The Item Interface also allows you to assign items to
other category sets and categories,
when there is data for item category
assignments in the MTL_ITEM_CATEGORIES_INTERFACE table.
Run Import Items Report :
Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored. Parameter Create or Update Items..
Run Import Items Report :
Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored. Parameter Create or Update Items..
1 Create new items.
2 Update existing items.
2 Update existing items.
Oracle Data & Migration
The sets of data required by Oracle Applications can be
broadly classified in to 4 categories
- Master Data : Item, Customer, Vendor, Bank
Accounts, etc.
- Opening Balances: On-Hand
Quantity of Items, GL Balances, etc.
- Open Transactions : Open
Purchase Orders, Open Sales Order, Open Invoice etc.
- Set-up Data : Item Categories, Stock
Locators
Similar source of data can be classified as
- Existing Applications : DB2,
Tally, SMS, etc.
- Electronic Data : Coming
through EDI in formats of Excel, Word, etc.
- Hard Copy Data : In Registers
- Non - Existent : Equipment Master, Collect and
then migrate
The above diagram depicts the high level Data Migration
approach from Legacy to ORACLE.
At a minimum, the data migration from Legacy to ORACLE
consists of these tasks:
- Extracting the data from Legacy data base.
- Loading the extracted data into staging area.
- Mapping and transformation of source data.
- Migrating source data from staging area into target
data base.
