Basic Tables and Programs involved in Conversions

Order Import Interface (Sales Order Conversion)

Interface Tables

OE_HEADERS_IFACE_ALL

OE_LINES_IFACE_ALL

OE_ACTIONS_IFACE_ALL

OE_ORDERS_CUST_IFACE_ALL

OE_PRICE_ADJS_IFACE_ALL

OE_PRICE_ATTS_IFACE_ALL

Base Tables

OE_ORDER_HEADERS_ALL

OE_ORDER_LINES_ALL

QP_PRICING_ATTRIBUTES

Concurrent Program:

Order Import

API

OE_ORDER_PUB.GET_ORDER and OE_ORDER_PUB.PROCESS_ORDER

Item Import

Interface Tables

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

MTL_INTERFACE_ERRORS

Concurrent program:

Item Import

Base Tables:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS

MTL_ITEM_TEMPLATES

Inventory Onhand Quantity

Interface Tables

MTL_TRANSACTION_INTERFACE

MTL_TRANSACTION_LOTS_INTERFACE

MTL_SERIAL_NUMBERS_INTERFACE

Concurrent Program:

Can be submitted in Transaction Manager through Interface Manager

API

INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS

Base Tables:

MTL_ONHAND_QUANTITIES

MTL_LOT_NUMBERS

MTL_SERIAL_NUMBERS

Customer Conversions

Interface Tables

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILE_INT_ALL

RA_CONTACT_PHONES_INT_ALL

RA_CUSTOMER_BANK_INT_ALL

RA_CUST_PAY_METHOD_INT_ALL

Base Tables

RA_CUSTOMERS

RA_ADDRESSES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_SITE_USES_ALL

Concurrent Program

Customer Interface

API

Trading Community Architecture uses the APIs to create the customers too

APIs are

Set the organization id

Exec dbms_application_info.set_client_info(<org_id>);

Create a party and an account

HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT

HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE

HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Create a physical location

HZ_LOCATION_V2PUB.CREATE_LOCATION()

HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

Create a party site

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()

HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

Create an account site

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

Create an account site use

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base Tables

HZ_PARTIES

HZ_PARTY_SITES

HZ_LOCATIONS

HZ_CUST_ACCOUNTS

HZ_CUST_SITE_USES_ALL

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITE_USES

Auto Invoice Interface

Interface Tables

RA_INTERFACE_LINES_ALL

RA_INTERFACE_SALESCREDITS

RA_INTERFACE_DISTRIBUTIONS

RA_INTERFACE_ERRORS

Base Tables

RA_BATCHES

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

AR_PAYMENT_SCHEDULES_ALL

RA_CUSTOMER_TRX_LINE_SALESREPS

RA_CUST_TRX_GL_DIST_ALL

RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program

Auto invoice master program

Requisition Import

Interface Tables

PO_REQUISITIONS_INTERFACE_ALL

PO_REQ_DIST_INTERFACE_ALL

Base Tables:

PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Concurrent Program:

Requisition Import

Purchase Order Conversions

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERACE

PO_INTERCAE_ERRORS

Concurrent Interface Program:

Import Standard Purchase Orders

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Basic Tables and Programs involved in Conversions

Order Import Interface (Sales Order Conversion)

Interface Tables

OE_HEADERS_IFACE_ALL

OE_LINES_IFACE_ALL

OE_ACTIONS_IFACE_ALL

OE_ORDERS_CUST_IFACE_ALL

OE_PRICE_ADJS_IFACE_ALL

OE_PRICE_ATTS_IFACE_ALL

Base Tables

OE_ORDER_HEADERS_ALL

OE_ORDER_LINES_ALL

QP_PRICING_ATTRIBUTES

Concurrent Program:

Order Import

API

OE_ORDER_PUB.GET_ORDER and OE_ORDER_PUB.PROCESS_ORDER

Item Import

Interface Tables

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

MTL_INTERFACE_ERRORS

Concurrent program:

Item Import

Base Tables:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS

MTL_ITEM_TEMPLATES

Inventory Onhand Quantity

Interface Tables

MTL_TRANSACTION_INTERFACE

MTL_TRANSACTION_LOTS_INTERFACE

MTL_SERIAL_NUMBERS_INTERFACE

Concurrent Program:

Can be submitted in Transaction Manager through Interface Manager

API

INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS

Base Tables:

MTL_ONHAND_QUANTITIES

MTL_LOT_NUMBERS

MTL_SERIAL_NUMBERS

Customer Conversions

Interface Tables

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILE_INT_ALL

RA_CONTACT_PHONES_INT_ALL

RA_CUSTOMER_BANK_INT_ALL

RA_CUST_PAY_METHOD_INT_ALL

Base Tables

RA_CUSTOMERS

RA_ADDRESSES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_SITE_USES_ALL

Concurrent Program

Customer Interface

API

Trading Community Architecture uses the APIs to create the customers too

APIs are

Set the organization id

Exec dbms_application_info.set_client_info();

Create a party and an account

HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT

HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE

HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Create a physical location

HZ_LOCATION_V2PUB.CREATE_LOCATION()

HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

Create a party site

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()

HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

Create an account site

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

Create an account site use

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base Tables

HZ_PARTIES

HZ_PARTY_SITES

HZ_LOCATIONS

HZ_CUST_ACCOUNTS

HZ_CUST_SITE_USES_ALL

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITE_USES

Auto Invoice Interface

Interface Tables

RA_INTERFACE_LINES_ALL

RA_INTERFACE_SALESCREDITS

RA_INTERFACE_DISTRIBUTIONS

RA_INTERFACE_ERRORS

Base Tables

RA_BATCHES

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

AR_PAYMENT_SCHEDULES_ALL

RA_CUSTOMER_TRX_LINE_SALESREPS

RA_CUST_TRX_GL_DIST_ALL

RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program

Auto invoice master program

Requisition Import

Interface Tables

PO_REQUISITIONS_INTERFACE_ALL

PO_REQ_DIST_INTERFACE_ALL

Base Tables:

PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Concurrent Program:

Requisition Import

Purchase Order Conversions

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERACE

PO_INTERCAE_ERRORS

Concurrent Interface Program:

Import Standard Purchase Orders

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Conversion in Oracle

Oracle Conversion

Conversion is to convert something for some destined requirement.

Exactly this is what literally means in Oracle too.

This is converting the business data into specified oracle formats which you need to create in oracle system irrespective of the current source of the data, which can be held by the oracle base tables properly.

For example, you were using a legacy system suppose a plain MS Excel or MS Access or any other system where you maintain your business data. But now you are planning to implement oracle EBS for your business and processes and you need your existing data (legacy data) to be moved into oracle. In this scenario, you cannot simply copy and paste your existing data into oracle system table (base tables). So for this you need to convert the data into oracle format and insert/create those data in oracle system through different process which oracle allows.

This process of converting the legacy data into oracle format and inserting those into oracle system is known as conversion.

Fortunate enough in oracle, as it provides flexibility in the form of interface programs to import the legacy data like customers, Invoices, Sales Orders, Price Lists, Items , etc.

Conversion can be done using two processes

·         Using Interface Programs

You use interface for mass loads, migrations, conversions; you can populate interface tables with many records and then start interface any time, so it’s asynchronous; if any record fails, it will stay in the interface tables till either fixed or purged.

·         Using Oracle API

API is used for synchronous tasks, like integrations or web site calls; you normally would only be processing one record at the time and get results right away; also, you would have to handle situations where Error status is returned.

Steps of Conversion

1.       Data Mapping

This is the initial steps where you have to identify the data set and data elements which need to be moved to the Oracle tables as part of conversions. Data mapping tables are prepared as part of this activity. This shows what are the data elements that needed by the target system to meet the business requirements.

2.       Preparing flat file

After mapping of the data elements, it’s time to prepare a flat file taking the data from the old system in ASCII format. The flat file can be of comma separated or pipe delimited or a tab separated file.

3.       Upload the data file into the server

After preparation of the flat file, need to upload the file into the target system i.e. the oracle system. SQL Loader or UTL_FILE utilities can be used to upload the data directly into the custom user defined staging tables. Then the custom programs which are written will run and validate the records from the staging table and will populate in the oracle provided standard interface tables.

4.       Interface Program

Once the interface tables are populated, the respective interface programs will be submitted. This program will validate the data and will derive the values and other information and finally will post those to the standard base tables. For different conversion types, different interface programs are there.

Example to understand the conversion process

Lets do the conversion of Item.

Scenario: In your old system, you have number of Items defined. Now you want those to be created in Oracle system.

Pre-requisite:

·         Organization need to be created in oracle in which you will create these items

·         Code combinations needs to be setup

·         Templates need to be defined

·         Item Status Codes need to be defined

·         Item Types need to be defined

Steps:

·         First prepare the list of items you need to move from the old system to the Oracle system.

·         Make a list of all items and its attributes which are required for the oracle system.

·         Then prepare a flat file.

·         Upload the records from the file into the custom staging tables.

·         Then validate the data and post the qualified data into the interface tables through the custom programs written.

·         Then submit the Item import interface program, this will take the data from interface table and after validation and extraction of all other values, it will populate in the base table.

Validations:

Note: There are few mandatory columns need to be filled in the interface table for every conversion type. That needs to be taken care of for error free conversion.

·         Validate Organization

·         Check for valid item type

·         Check for the valid item_id and segments

·         Validate the segment1/item,_id for master org for unique creation

·         Check for valid item status

·         Valid template/attributes

Interface Tables:

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)

MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)

MTL_INTERFACE_ERRORS (View errors after import)

Base Table:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS

MTL_ITEM_TEMPLATES

Conversion in Oracle

Oracle Conversion

Conversion is to convert something for some destined requirement.

Exactly this is what literally means in Oracle too.

This is converting the business data into specified oracle formats which you need to create in oracle system irrespective of the current source of the data, which can be held by the oracle base tables properly.

For example, you were using a legacy system suppose a plain MS Excel or MS Access or any other system where you maintain your business data. But now you are planning to implement oracle EBS for your business and processes and you need your existing data (legacy data) to be moved into oracle. In this scenario, you cannot simply copy and paste your existing data into oracle system table (base tables). So for this you need to convert the data into oracle format and insert/create those data in oracle system through different process which oracle allows.

This process of converting the legacy data into oracle format and inserting those into oracle system is known as conversion.

Fortunate enough in oracle, as it provides flexibility in the form of interface programs to import the legacy data like customers, Invoices, Sales Orders, Price Lists, Items , etc.

Conversion can be done using two processes

·         Using Interface Programs

You use interface for mass loads, migrations, conversions; you can populate interface tables with many records and then start interface any time, so it’s asynchronous; if any record fails, it will stay in the interface tables till either fixed or purged.

·         Using Oracle API

API is used for synchronous tasks, like integrations or web site calls; you normally would only be processing one record at the time and get results right away; also, you would have to handle situations where Error status is returned.

Steps of Conversion

1.       Data Mapping

This is the initial steps where you have to identify the data set and data elements which need to be moved to the Oracle tables as part of conversions. Data mapping tables are prepared as part of this activity. This shows what are the data elements that needed by the target system to meet the business requirements.

2.       Preparing flat file

After mapping of the data elements, it’s time to prepare a flat file taking the data from the old system in ASCII format. The flat file can be of comma separated or pipe delimited or a tab separated file.

3.       Upload the data file into the server

After preparation of the flat file, need to upload the file into the target system i.e. the oracle system. SQL Loader or UTL_FILE utilities can be used to upload the data directly into the custom user defined staging tables. Then the custom programs which are written will run and validate the records from the staging table and will populate in the oracle provided standard interface tables.

4.       Interface Program

Once the interface tables are populated, the respective interface programs will be submitted. This program will validate the data and will derive the values and other information and finally will post those to the standard base tables. For different conversion types, different interface programs are there.

Example to understand the conversion process

Lets do the conversion of Item.

Scenario: In your old system, you have number of Items defined. Now you want those to be created in Oracle system.

Pre-requisite:

·         Organization need to be created in oracle in which you will create these items

·         Code combinations needs to be setup

·         Templates need to be defined

·         Item Status Codes need to be defined

·         Item Types need to be defined

Steps:

·         First prepare the list of items you need to move from the old system to the Oracle system.

·         Make a list of all items and its attributes which are required for the oracle system.

·         Then prepare a flat file.

·         Upload the records from the file into the custom staging tables.

·         Then validate the data and post the qualified data into the interface tables through the custom programs written.

·         Then submit the Item import interface program, this will take the data from interface table and after validation and extraction of all other values, it will populate in the base table.

Validations:

Note: There are few mandatory columns need to be filled in the interface table for every conversion type. That needs to be taken care of for error free conversion.

·         Validate Organization

·         Check for valid item type

·         Check for the valid item_id and segments

·         Validate the segment1/item,_id for master org for unique creation

·         Check for valid item status

·         Valid template/attributes

Interface Tables:

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)

MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)

MTL_INTERFACE_ERRORS (View errors after import)

Base Table:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS

MTL_ITEM_TEMPLATES