Bookmark and Share Subscribe You may face visibility problem if is blocked on your network

Monday, August 17, 2009

File import in Customer Data Hub (CDH)

You can load customer data using file import or bulk port.

Bulk import is the process of loading customer data in bulk from legacy, third party, or other external sources into the TCA Registry. In Oracle Customers Online, you can load source files of organization, person, or contact data into the TCA import interface tables.

For the transfer of data from the interface tables into the TCA Registry, you have these options.
• If the appropriate import profile options are set, the file load data can automatically transfer from the interface tables into the Registry, as soon as the file load completes successfully.
• Use the Oracle Customer Data Librarian (CDL) batch import feature, if you have CDL.
• Run the Import Batch to TCA Registry program using the Trading Community Manager responsibility.

The bulk import is only for information on the party level. To import both party and account information, need to use Customer Interface Process.

An import batch contains information about transferring a set of records from the interface tables into the TCA Registry, including settings for optional data quality pre-import processes that you can perform on the batch.
Batch de-duplication: Identify and resolve duplicates within the batch that you are importing from the interface tables.
Address validation: Validate addresses within the batch against authorized sources.
Registry de-duplication: Identify and resolve duplicates between the interface tables and the Registry.
Import process is basically consists of below main steps:


In this article we are going to see file import for “Person” type of parties.

Prepare a CSV file with details of the persons you want to import. Here I am going to show you import for two records, with their contact and organization details.

Let’s prepare the CSV file:

First Name,Last name,Address 1,Address 2,City,Country,Email,Gender,Marital Status, Organization Name,Birth Date
Christian,Slator,99 A-Towers,,SouthHall,GB,,MALE,S,AC Networks,1-Jan-1975
DEVendra,Gulve,Rakshak Nagar,Kharadi,Pune,India,,MALE,S,TATA Motors,11-Jan-1999

Logon to Oracle and go to any of the below responsibility:Customer Data Librarian
Customer Online

Provide the details for import. Also specify the format in which data exist in CSV file. If the first row in CSV file has field name / column heading then select ‘File Header Exist’.
Here we are going to import person’s information. Select the information type which you want to import. For each type, some mandatory data must be there in CSV file.
E.g Address: Address1, Country. Contact: Atleast one contact (Phone/email)

Here Columns from CSV file must be mapped with Oracle column names. Usually import is done in specific format (depends on Organizational need), so you can save mapping and use it each time to reduce efforts.
Also one more very good function is given by Oracle “Auto Map”. If Oracle’s Column name and column name in CSV file matches, then those columns gets auto-mapped. This function works only when header information exists in source file.

Oracle will do Auto-mapping only for those columns for which names matches. For rest of column we need to do mapping manually.

Search for appropriate column name and provide mapping as shown below. You can change column name in CSV file to make this process hassle free .

Once the Mapping is done, have a look at Preview and then click on Finish.

Oracle fires one Concurrent request to import data in Interface table. If any error occurs then you can correct it.
Oops!!! We encountered error/s L
Let’s go and correct it.

Here it looks there is some problem in record for First Name=DEVendra

Here looks some problem with value we provide for column Country.

Import program need Country code, but we provided Country name. So let’s correct it to country code (IN) and apply changes.

One more column I am aware of is Gender it does not take M / F, we need to give MALE / FEMALE that too in all caps J .
Also for Marital Status attribute, Married / Single will not work, Oracle need values to be M / S.
Now the obvious questions come to mind that
How we can to know which value is valid for which column?
Is there any document which tells us mandatory columns for import?
I have only one answer for it, this will come to know by experience only. I did not find any document for such kind of question. Or you can search for different look-ups being used for respective attributes.

Now we can finish the error correction process.

If you are going to put data in interface tables directly using SQL, hen it will need an Active Batch. So before using SQL you will need to “Generate Batch” and then “Activate Batch”.
In our case batch is already created and it is in active status. Also errors are corrected, so now the batch is ready to be imported to base tables.
Select Batch and Define Import for the batch.

Here you can specify whether you want to check duplicates in batch or not.

Here you can specify whether you want to validate Addresses in batch or not.

Here are some more options to make import more efficient and accurate.

If you select 'Preview Result Before Running Import' then Oracle will finish only stage-1 and then you need to view details and after you click on 'Complete Import' further processing will be done.
If you select to check of duplicate then you also need to specify the match rule which needs to be used.
As we have not selected for checking duplicates, Oracle create entities even if same entity already exist.

Oracle fires concurrent program which will put records from interface table to base tables.
Keep Refreshing page till the time it completes.

Now the Import status will keep changing based on the stages that are completed.
On completion of stage 1, table named HZ_IMP_PARTIES_SG will get populated with the records.
On completion of stage 2, related tables having table name like HZ_IMP_%_SG gets populated. All the tables having suffix _SG are the staging tables which holds values in import process and same information is pushed to Interface tables (e.g. HZ_IMP_PARTIES_INT).
In stage 3 data gets populated in interface table and then processed to push it to base tables (e.g. HZ_PARTIES).

If any error comes during interface, then details will be available in table HZ_IMP_ERROR.
You can search for records having error with condition INTERFACE_STATUS ='E' in any interface table and then get the associated ERROR_ID. Search for that ERROR_ID in table HZ_IMP_ERROR, here you come to know which column in which table has value causing the error.

Hurry!!! Our program got completed without error.
We added only two records in CSV file but Oracle is showing Total 10 records got imported… how come these many records got imported?
Even if only two records were in CSV file, Oracle entered 5 records for each person,
i.e. Person Name, Address, Contact (Email), Organization, Relationship between Organization-Person.

That makes total imported records =10.

Let’s check whether the correct details we imported …
Query the person:

As we gave Organization name, oracle created organization and build relationship between them.

Below are some of the tables getting used in Import process:

Entity Interface Table TCA Table

For better visibility CLICK HERE


  1. Devendra,
    This is an outstanding article. Very well documented and explained in a simple to understand manner.

  2. Thanks DEV. Really a nice doc.
    I need to understand one thing, from this File Load process we are importing the customers.
    Can we import Customer Accounts too? and can we import Customer along with Salesperson?



Please add your valuable feedback / comments


This blog is purely personal and the thoughts expressed here represent only me. The purpose of this blog is to share information and knowledge about Oracle's product which I have come across with my exposure to the product, practice and observations. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. The blog has been created solely as a educational, for storing portions of the vast Oracle knowledge world. Oracle EBS is an Oracle Corp. product and you should contact Oracle directly for any specific fact or issue.

*NOTE: Few articles on this blog are not completely prepared by me, content is edited and complied after referring various sites to make visitor's job easy