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,pqr@eds.com,MALE,S,AC Networks,1-Jan-1975
DEVendra,Gulve,Rakshak Nagar,Kharadi,Pune,India,d@gulve.com,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
Addresses	HZ_IMP_ADDRESSES_INT	HZ_LOCATIONS HZ_PARTY_SITES
Address Uses	HZ_IMP_ADDRESSUSES_INT	HZ_PARTY_SITE_USES
Classifications	HZ_IMP_CLASSIFICS_INT	HZ_CODE_ASSIGNMENTS
Credit Ratings	HZ_IMP_CREDITRTNGS_INT	HZ_CREDIT_RATINGS
Contact Points	HZ_IMP_CONTACTPTS_INT	HZ_CONTACT_POINTS
Financial Numbers	HZ_IMP_FINNUMBERS_INT	HZ_FINANCIAL_NUMBERS
Financial Reports	HZ_IMP_FINREPORTS_INT	HZ_FINANCIAL_REPORTS
Parties	HZ_IMP_PARTIES_INT	HZ_PARTIES HZ_PERSON_PROFILES HZ_ORGANIZATION_PROFILES
Relationships	HZ_IMP_RELSHIPS_INT	HZ_RELATIONSHIPS HZ_ORG_CONTACTS HZ_PARTIES
Contacts	HZ_IMP_CONTACTS_INT	HZ_RELATIONSHIPS HZ_ORG_CONTACTS HZ_PARTIES
Contact Roles	HZ_IMP_CONTACTROLES_INT	HZ_ORG_CONTACT_ROLES

For better visibility CLICK HERE

Relationships in Customer Online

NOTE: If you have problems in accessing Google Docs, then probably you will not get the pictures in this article.

Let’s take quick run of what is this “Relationship” all about…

Relationship Overview

• The TCA relationship model lets you record complex, real-life relationships among entities in the TCA Registry.
• A relationship represents the way two entities interact with each other, based on the role that each entity takes with respect to the other. For example, the employment relationship between a person and an organization is defined by the role of the person as the employee and the organization as the employer.
• In addition, every relationship is reciprocal.

Relationship Phrase and Role Pairs

• Every relationship is based on a relationship phrase and role pair.
• A phrase pair, such as ‘Employee Of’ and ‘Employer Of’, describes the role of either entity in the relationship as the subject. For example, Joe as the subject of the relationship would have Employee Of as the phrase, and Oracle as the subject would have Employer Of
• A relationship role pair, such as Employee and Employer, describes the two entities no matter the direction of the relationship. For example, Joe has the Employee role and Oracle the Employer role, both as either the subject or object.
• When you create a relationship with a relationship phrase or role, the reverse direction of the relationship is automatically created with the other phrase or role in the pair. For example, if you define Joe as the employee of Oracle, Oracle as the employer of Joe is also created.

Relationship Type

• Each relationship phrase and role pair belongs to a relationship type, which categorizes the types of relationships that you can create. For example, the relationship phrase and role pair described above would belong to an Employment relationship type.
• Relationship types determine if the relationships created with the type are hierarchical, and if not, whether they can be circular or not.

Relationship Group

• Relationship groups are used to determine which relationship roles and phrases are displayed in specific application user interfaces. Groups can also be used to categorize roles and phrases for other functional uses.

Hierarchical Relationships

A hierarchical relationship ranks one entity above the other. For Example: parent and child relationship.

Circular Relationships

• If a relationship type allows for circular relationships, you can create a relationship from Party A to Party B to Party C and back to Party A. For example, Party A is a competitor of Party B, which is a competitor of Party C, which is a competitor of Party A.
• Hierarchical relationships cannot be circular. For example, if Alan's manager is Jenny, and Jenny's manager is Chris, then Chris's manager cannot be Alan.

Below diagram describes the process flow of setting up relationship types as well as phrase and role pairs.

Now we are going to see how to

• Create relationship type
• Add relationship phrase and role pairs.
• Assign relationship phrase to relationship group
• Build relation between two parties using the relationship.
• View Relationships

Sample Case: Say ‘Pat Stock’ is girlfriend of ‘Sushant Dhatrak’. Now we will define relationship type ‘Friendship’ and in that will add phrases and roles pairs.

Create relationship type:

Go to any of the below two responsibilities:

• Customers Online Data Librarian Superuser
• Customers Online Superuser

Go to Administration > Relationships

Here you will get all the defined relationship types. If you want to create new one click on CREATE

 

Here we can give name for Relationship Type and specify what kind of relationship it’s going to be. Select appropriate radio button.

All relationship types get added to HZ_RELATIONSHIP_TYPE. e.g. ‘Friendship’ will get added to HZ_RELATIONSHIP_TYPE after successful creation of relation type.

Add relationship phrase and role pairs:

Here select of Subject / Object type will depend on between what entities (Person / Organization) this relationship can exist. Put the appropriate phrase and role.

Ideally speaking Phrases are defined in Look up (PARTY_RELATIONS_TYPE) and we should use only those phrases, but Oracle has given flexibility to enter any text we fill like and proceed… Freely entered text for phrase does not get added to Look up automatically.

To add phrase to Look up go to Receivables > Setups >System > Quick code > Receivables, query for the Type and add values.

Same phrase for both Object and Subject in a pair can be given only if the relationship is of Circular type.

Review the definitions and if it looks OK then click on Finish…

And here is the Relationship, phrase & roles defined just now. We can add many more pairs of phrase & roles to one relationship type.

e.g. In Family relationship type we can have ‘Father-Child’, ‘Mother-Child’, ‘Husband-Wife’, ‘Brother-Sister’ and so on…

So here we have added more phrases to this relationship type.

Before using the Phrase and Roles to build relationship between two entities, we need to assign it to Relationship Group/s.

Assign relationship phrase to relationship group:

Select the Phrase and add it to appropriate Relationship Group.

Relationship Group name comes from Look up RELATIONSHIP_TYPE_GROUP, it has ‘Access Level = System’ so cannot add values in it.

Honestly, I don’t have information how these relationship group works… L

Now we are done with the defining relationship type

Build relation between two parties using the relationship:

Search for the party to want to build relationship for.

As the party is of Person type Employment details can be filled in.

Go to Other sub-tab to build relationship between Pat and Sushant. Select Role and click on Go button.

Here enter the Object name with which relationship needs to define.

If the Object/Party name does not exist Oracle is going to create new party with some default values but if party already exists the Oracle will prompt for existence, also it gives Matching percentage. You can select to create new party but in our case we are going to use party (Sushant Dhatrak) which already exist.

Checking for duplicate party depends on the profile option ‘HZ: Enable Duplicate Prevention at Party Creation’ (here it is enabled for Person and Organization)

Once we proceed with ‘Use existing Person’ Oracle will build and show relationship.

View Relationships:

To view all relationships exist with Party ‘Pat Stock’, go to Overview sub-tab and click on ‘Visualize Relationship’. This will show pictorial view of relationship between entities.

If we go from Pat’s profile it will show ‘Girl-friend of’ as relationship phrase.

If we go from Sushant’s profile it will show ‘Boy-friend of’ as relationship phrase, as it is counter part of ‘Girl-friend of’ phrase as defined in Phrase & Role pair.

Names / Characters taken in this article are imaginary and do have any relation with real world. If any similarity found, treat it as pure coincidence.

.If you are facing problem in visibility of article CLICK HERE

What is Customer Data Hub (CDH) ?

Customer Data Hub is:

• Flexible, extensible world-class customer data model
• High volume import tools with integrated data quality options
• Survivorship rules for conflict resolution
• Configurable data sharing and security-based privileged access
• Embedded Data Quality Management (DQM) Engine
• Duplicate prevention, identification and resolution
• Address validation services (Adaptors available from Trillium and Firstlogic)
• Out-of-the-box integration with D&B (batch and real-time)
• Integrated application for data quality professionals

Oracle Customer Data Hub provides a comprehensive set of services, utilities and applications to create and maintain a trusted master customer identity across the enterprise. Instead of requiring one to ‘rip and replace’ legacy investments, Oracle Customer Data Hub allows companies to maximize their return on investment in legacy systems by consolidating customer data from heterogeneous systems into a central location and establishing enterprise-wide data quality and integrity at the lowest possible cost.

Oracle Customer Data Hub (CDH) centralizes customer data from disparate systems across your enterprise into a master repository. Existing systems are integrated in real-time with the Hub, allowing you to leverage legacy platform investments while capitalizing on the benefits of a single customer identity.

Customer Data Hub (CDH) is a concept; it is not a process or navigation path in Oracle. It is implemented by using the TCA API's and the functionality in Oracle Customers Online and Data Librarian Applications.

CDH in reality is not an application. To implement the CDH, you would use the functionality of our existing applications i.e. Customer Data Librarian (CDL) and Oracle Customer Online (OCO).

Existing solutions such as OCO, ODL, and TCA have been enhanced for the Customer Data Hub to facilitate bi-directional integration with 3rd party systems. So, the Customer Data Hub comprises the newly enhanced versions of Oracle Customers Online, Oracle Data Librarian, and the Trading Community Architecture, which together with Oracle Application Server 10g (or other middleware) creates the truly complete customer hub.

If you have already licensed these Oracle Applications then you can implement the Customer Data Hub. You should talk with your Sales Rep, they would be able to demo how the existing Oracle Applications can be implemented as a Customer Data Hub solution.

Customer Data Hub (CDH) provides a data model and infrastructure (TCA) to create and maintain a physical, central customer database. E-Business Suite applications are native users of CDH, but CDH is application agnostic.

CDH “comes with” any and all Oracle Applications. It allows you to bring in data from outside sources and map them to the Oracle Customer (TCA) data.

Customer Data Librarian (CDL) and Oracle Customer Online (OCO) are the applications used to manage the CDH.
-CDL manages all data in TCA whether it comes from an EBS application or an external system.

-OCO provides a comprehensive UI to view, create, and update data stored in TCA.

KEY BENEFITS

• Enables real-time, high quality, enterprise-wide customer knowledge by consolidating customer data from disparate systems
• Enriches data centrally, and shares the cleansed master record across the enterprise to provide all users access to a single, accurate customer master identity
• Embedded data quality engine improves integrity through duplicate prevention, identification and resolution
• Integrates with standards based third party data cleansing services
• Reduces the total cost of ownership through simplified, low-cost integration based on granular business services

CDL focuses on the three Cs of customer data quality: consolidation, cleanliness, and completeness. A quality customer database benefits the entire enterprise, helping everyone to make real-time decisions with the confidence that the supporting data is accurate, timely, and complete.

Ø Consolidation

• Import customer data from external sources into the TCA Registry.

• Map records between external source systems and the TCA Registry, to tie customer information from multiple sources to the Registry and maintain a single, enterprise-wide view of your customer information.

Ø Cleanliness

• Resolve duplicate parties within the TCA Registry, as well as duplicate information within a party.

• Permanently purge parties from the TCA Registry.

• Maintain party information such as status, internal indicator, and certification level

Ø Completeness

• Enrich customer data with information from third party data sources, such as D&B.

• Both TCA and CDL belong to the Oracle Customer Data Management (CDM) product family. CDM also includes Oracle Customers Online (OCO). Oracle Customer Data Librarian contains all of the Customers Online features.

De-duplicate function is given in CDH:

§ Use de-duplication to:

• Merge duplicate parties in the Oracle Trading Community Architecture (TCA) Registry.

• Cleanse a specific party so that it does not have any duplicate information.

§ De-duplication provides functionality for the entire process of resolving duplicate parties and information in the TCA Registry:

• Identify duplicates: Create merge requests, which contain information about the duplicate parties that you want to merge or the party that you want to cleanse.

• Automatically merge duplicates: Optionally run Automerge to automatically merge parties identified as definite duplicates. Automerge bypasses manually creating, mapping, and submitting merge requests.

• Manage merge requests: Review, assign, and reject specific requests.

• Map merge requests: Determine the results of the merge by specifying the information that remains after the merge. You map the duplicate information to merge either among parties or within the party that you are cleansing.

• Submit merge requests: Run the Party Merge process to perform the actual merge, either for duplicate parties or within a party to cleanse it.