This chapter describes using Bulk Import to load data from legacy or other external systems into the TCA Registry.
This chapter covers the following topics:
Use Oracle Trading Community Architecture (TCA) Bulk Import to load data from legacy or other external systems in bulk into the TCA Registry. TCA provides validations and optional de-duplication to ensure the quality of the imported information.
The import is only for information on the party level. To import both party and account information, use Customer Interface. See: Customer Interface.
Note: Customer Interface runs independently and does not regard party information already loaded in the TCA Registry. In particular, if the process does not find an existing account site or account contact point, it creates a new account site or contact point, as well as the associated party site or contact point, even if the party level versions already exist. As a result, if duplicates are created, use Party Merge or Oracle Customer Data Librarian to resolve them. See: Party Merge Overview or De-Duplication Overview, Oracle Customer Data Librarian User Guide.
Your administrator can set up for Bulk Import. See: Setting Up Bulk Import, Oracle Trading Community Architecture Administration Guide.
Load data into the interface tables. See: Loading Data into the Interface Tables.
Run the Import Batch to TCA Registry program to import a batch into the TCA Registry. See: Import Batch to TCA Registry.
Optionally run the Import Batch De-Duplication report to view results of batch de-duplication. See: Import Batch De-Duplication Report.
Run the TCA Import Error report to view errors, and reload the corrected data. See: Resolving Errors.
Purge data from the interface tables. See: TCA Import Batch Purge.
Related Topics
Using Oracle Trading Community Architecture
You should have a strong knowledge of the data and data structure to be imported. You can load data through:
SQL/ETL Load: Create scripts or use tools to extract the source information, transform the values to meet the data requirements of the interface tables, and populate the interface tables with large volumes of information. See: Bulk Import Interface Tables, Oracle Trading Community Architecture Reference Guide.
D&B Load: Use the standard D&B bulk file that you receive from D&B and run the D&B Import Adapter request set to automatically map and load the D&B information into the interface tables. See: D&B Import Adapter and Batch Loading.
File Load: Use Oracle Customers Online (OCO) or Oracle Customer Data Librarian (CDL) to load data from a comma-separated value (CSV) file, or file delimited by another allowed character, into the interface tables. See: File Loads Overview, Oracle Customers Online User Guide.
A set of data to be loaded into the TCA Registry at one time is called a batch. The data in one batch must be from the same data source. The interface tables can store as many batches from different sources as needed, and any number of batches can be actively populating the interface tables at the same time. However, if a D&B import batch is being loaded into the interface tables, using the D&B Import Adapter request set, the tables are locked and all other batches must wait until the D&B batch is completed. The request set uses direct inserts into the interface tables, which requires table-level locks.
Note: When the request set is running, you also cannot run the Import Batch to TCA Registry program to transfer any batch from the interface tables into the TCA Registry.
Each import batch must have a batch ID. Use the create batch procedure from the Create Import Batch API to guarantee that a unique batch ID is assigned.
Call the following script to create a batch ID:
Set serveroutput on;
DECLARE
x_batch_id NUMBER;
x_return_status VARCHAR2(4000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
BEGIN
hz_imp_batch_summary_v2pub.create_import_batch(NULL,'&batchname','&batchdescription',
'&ORIGSYS','&loadtype',12,x_batch_id,x_return_status,x_msg_count,x_msg_data);
dbms_output.put_line(SubStr('x_batch_id = '||TO_CHAR(x_batch_id), 1, 255));
dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line(SubStr('x_msg_count = '||TO_CHAR(x_msg_count), 1, 255));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
RAISE;
END;
See: Create Import Batch API, Oracle Trading Community Architecture Technical Implementation Guide.
You must provide a unique ID for each record in the interface table. The unique ID is a combination of:
The source system code, defined through Source System Management (SSM) administration, which identifies the source that the imported data comes from
The source ID, which identifies the record in the source system
In the HZ_PARTIES, HZ_PARTY_SITES, HZ_CONTACT_POINTS, and HZ_ORG_CONTACTS tables, these unique IDs are treated as source IDs for Source System Management. In other tables that do not support SSM, the ID is treated as a logical key. See: Source Systems Overview, Oracle Trading Community Architecture Administration Guide.
These IDs serve to link:
Details of each party together, as foreign key references among interface tables.
The data in the source system, now in the interface tables, to the target TCA tables, for making updates to existing parties in the Registry from the same source data. See: Matching Records with Source System Management.
You should maintain all unique IDs locally in your system, and use them in the future to update specific information in the TCA Registry.
If you plan to use Customer Interface to import accounts that are associated with the imported parties, you must ensure that the source ID alone is unique across all source systems. Customer Interface uses only source IDs, not source system codes, so the imported account must have a unique source ID to link to the corresponding imported party.
For example, if you are importing a party and do not plan to import associated accounts, you pass for the imported party:
Source system code: MKT
Source ID: 141442
You only need to ensure that this combination is unique across all source systems. If you are importing a party and plan to import associated accounts, then you pass for the imported party:
Source system code: MKT
Source ID: MKT141442
You can concatenate the source system code and source ID values to use as the source ID for the party, or implement your own system to ensure that the source IDs alone are unique across all source systems. When you load the accounts with Customer Interface, you would pass the MKT141442 source ID to associate the accounts with the imported party. See: Customer Interface.
Through import, you either insert new records into the Registry or update existing TCA records. An update means that the imported information, if not null, completely overwrites all values in the corresponding TCA record.
All records inserted into the TCA tables are assumed to be active.
You can update TCA Registry records of any status through import, but you cannot change the status value itself during import.
The matching phase of the import process determines whether the interface table record is an insert or update. See: Matching Records with Source System Management.
You can use the optional INSERT_UPDATE_FLAG column in the interface tables for insert or update protection: I for insert and U for update. The system matching must align with the action in this column. If not, the record is marked with error and not loaded into TCA. If the column is left blank, the system matching alone decides between insert or update for each record.
If the HZ: Use Data Sharing and Security During Import profile option is set to Yes, then the import process invokes Data Sharing and Security (DSS) each time that a TCA record is updated. DSS provides additional security against update and is enabled for these tables involved in import:
See: Data Sharing and Security Overview, Oracle Trading Community Architecture Administration Guide.
If the HZ: Validate Flexfields During Import profile option is set to Yes, then flexfield validation also applies to inserts and updates for these tables:
When a record in the interface table is designated to update an existing record upon import, any null values in that record would not update the TCA value. The existing value in TCA remains the same after import. You are not required to pass values in all interface table columns when values are optional and not available from a given source system.
If you want to intentionally overwrite a TCA value with NULL, you must pass a specific value, as defined in these profile options:
For example, the HZ: Date Value to Indicate NULL During Import profile option is set to 01-01-4000, and a record in the TCA Registry currently has an end date of 02-02-2005. If you want to update the date with a null value, then you must pass 01-01-4000 for that column.
The PARTY_NUMBER and PARTY_SITE_NUMBER columns in the TCA tables must contain unique values.
If values for these columns are passed in the interface tables, the import process checks for the uniqueness during the import.
If unique, the values are accepted.
If not unique, the record fails with a unique index error.
If no values for these columns are passed, the import process generates values from an internal sequence.
If the sequence numbers are unique, the values are accepted.
If not unique, the record fails with a unique index error.
You should maintain one of these strategies:
Always pass values for these columns so that the import process does not have to generate sequence numbers.
Never pass values for these columns so that the generated sequence numbers would not conflict with existing values in the Registry.
Primary flags indicate which record is the primary one when multiple records exist. For example, if a party has multiple bill-to addresses, the main bill-to address is marked as primary.
Primary flags are exposed in these interface tables, which correspond to TCA tables that contain primary flags:
If a new party is inserted through import, you can define primary flags for any of the above child entities.
If an existing party in the Registry do not have any contact points and is being updated with one or more contact points, you can mark one of the imported contact points as primary. If you do not specify any of the new records as primary, the import process marks one at random. If you mark more than one record as primary, the import process will pick one of the marked records at random.
If the party already has an existing address, contact point, address use, or classification, then you cannot change the primary status through import. Even if you pass a value in the PRIMARY_FLAG column of the interface table, the value is ignored. You can only change primary statuses through a user interface or APIs.
Related Topics
This table shows the interface tables and the TCA tables that the data would be imported into. The interface tables do not include any columns that have been made obsolete in TCA.
See: Bulk Import Interface Tables, Oracle Trading Community Architecture Reference Guide.
The parent and child relationships among the import interface tables are:
HZ_IMP_FINREPORTS_INT is the parent of HZ_IMP_FINNUMBERS_INT.
HZ_IMP_ADDRESSES_INT is the parent of HZ_IMP_ADDRESSUSES_INT.
HZ_IMP_CONTACTS_INT is the parent of HZ_IMP_CONTACTROLES_INT, and potentially HZ_IMP_ADDRESSES_INT and HZ_IMP_CONTACTPTS_INT because addresses and contact points can be defined at the contact level.
If a child entity record has an error, the parent entity record can still be imported if it has no errors. If a parent entity record has errors, however, all related child entity records will also fail.
Related Topics
Loading Data into the Interface Tables
Use the Import Batch to TCA Registry program to import a batch from the interface tables into the TCA Registry. The program provides optional preimport processes you can perform on the data before the actual import into TCA:
Batch de-duplication: Identify and resolve duplicates within the batch that you are importing from the interface tables.
Note: The Import Batch to TCA Registry program identifies only duplicates with respect to the batch that you are importing. You cannot check for duplicates between batches in the interface tables.
You can run the Import Batch De-Duplication report to view results. See Import Batch De-Duplication Report.
Address validation: See: Import Address Validation.
Registry de-duplication: Identify duplicates between the interface tables and the Registry.
Tip: You should purge data from the interface tables after the corresponding batch has been loaded to TCA, to reduce the overall size of the interface tables and improve performance for subsequent batch loads. See: TCA Import Batch Purge.
The data you want to import must be loaded into the interface tables. See: Loading Data into the Interface Tables.
Optionally check that the D&B Import Adapter request set is not currently running. If it is and you submit the Import Batch to TCA Registry program, the program will be pending until the request set is complete. See: Import Batches.
If you are running the Import Batch to TCA Registry program for an import batch that was not loaded into the interface tables using File Load in Oracle Customers Online or Oracle Customer Data Librarian, optionally check that another non-File Load batch is not currently being loaded into the TCA Registry. If it is, and you submit the program for a non-File Load batch, the program will be pending until the process for the other batch is complete. See: File Loads Overview, Oracle Customers Online User Guide.
Use the following parameters to submit the Import Batch to TCA Registry program.
Enter the batch to import.
Specify whether to run both preimport and import processes, or only the preimport processes.
If you plan to run batch de-duplication, you can run only preimport processes and then run the Import Batch De-Duplication report to preview batch de-duplication results.
Specify whether you want to run de-duplication on the batch or not.
If you select to run batch de-duplication, you must enter the match rule to use. Only match rules of type Bulk Duplicate Identification are used for batch de-duplication.
Important: Do not select a match rule with user-defined custom attributes.
Specify how to resolve duplicates found with batch de-duplication. The specified action would be taken on the batch before the data is imported into the TCA Registry.
Specify whether you want to run address validation on the batch or not.
Specify whether or not you want to run de-duplication between the batch and the TCA Registry.
If you select to run Registry de-duplication, you must enter the match rule to use. Only match rules of type Bulk Duplicate Identification are used for Registry de-duplication. You can select the same match rule as for batch de-duplication.
Important: Do not select a match rule with user-defined custom attributes.
If the specified match rule is designated for Automerge, then potential duplicates that reach or exceed the match rule's:
Match threshold, but not the automatic merge threshold, are included in System Duplicate Identification (SDI) batches for merge consideration in Oracle Customer Data Librarian.
Automatic merge threshold are automatically merged after the import.
If the match rule is not enabled for Automerge, then all potential duplicates that reach or exceed the match threshold are included in SDI batches.
All records that do not reach the match threshold are considered not duplicates and inserted as new parties. All potential duplicates are also inserted into the TCA Registry as new parties, but then accordingly included in SDI batches or automatically merged.
Specify whether or not to generate fuzzy keys as part of the postimport processes.
Important: Skipping fuzzy key generation helps optimize performance, but select No only if you do not use Oracle E-Business Suite applications that use fuzzy keys.
See: Postimport Processes.
Related Topics
If you run batch de-duplication as part of the Import Batch to TCA Registry program, you can run the Import Batch De-Duplication report:
After the preimport processes and before the actual import, to preview the de-duplication results.
After the import, to view the actual de-duplication results.
The report shows the duplicates that were identified and resolved within the batch that you are importing from the interface tables. This de-duplication does not resolve duplicates between the batch and the TCA Registry itself.
Enter the batch that was imported with batch de-duplication.
Related Topics
When the Import Batch to TCA Registry program is submitted, this process follows:
The batch de-duplication and address validation runs, if selected.
Duplicates found through batch de-duplication are resolved as you specified in the Action to Take on Duplicates parameter, for example, to keep the records that are last updated.
If you chose to run preimport processes only so that you can preview preimport results, then address validation includes all records. If not, then address validation includes only records that batch de-duplication determines as not duplicates.
The data in the interface tables are matched against source system mappings to determine if imported records are inserted as new parties or to update existing parties. See: Matching Records with Source System Management.
For performance reasons, the records are populated into staging tables for processing before loading into the TCA tables.
If you have selected the optional Registry de-duplication, then duplicates are identified between the batch that you are importing and the TCA Registry. All parties are to be inserted as new parties, but then the duplicates are resolved as described in step 7.
After any selected preimport processes finish, depending on your choice, either:
The actual import into TCA starts immediately.
You start the actual import by rerunning the program, after you optionally:
Mandatory validations are performed on all data, and records that pass all validations are loaded into the TCA Registry. Records that fail are marked with error and not imported. See: Import Validations.
If Registry de-duplication was run, then:
System Duplicate Identification batches are created with potential duplicates for merge consideration in Oracle Customer Data Librarian. See: System Duplicate Identification, Oracle Customer Data Librarian User Guide.
Duplicates are automatically merged through Automerge, if applicable. See: Automerge, Oracle Trading Community Architecture Administration Guide.
Records that are not duplicates are inserted as new parties.
Postimport processes automatically run. See: Postimport Processes.
If you ran batch de-duplication, optionally run the Import Batch De-Duplication report for this batch. The report output provides the de-duplication results.
Related Topics
If you choose to validate the addresses in the interface tables before importing them into the TCA Registry, the addresses are validated using address validation adapters. An adapter connects TCA to an external source of information, which provides the validation service.
Each address is validated through the default adapter set up for each country. For example, if the Vision adapter is the default for Thailand, then all Thai addresses in the interface tables are validated against Vision's standard addresses.
If an address from the interface tables differs from the validated address, it is updated with the validated address only if it is valid above the threshold defined for the adapter. Such an update is called an address correction. For example, if the adapter configuration has the threshold at the municipality level, an address in the interface tables is corrected if at least its city or town is valid.
Note: Aside from the validation threshold, address correction only occurs if the update does not violate other validations, such as tax validation rules.
After the addresses are validated and corrected in the interface tables, they are ready for import into the TCA Registry.
Related Topics
When the import into the TCA tables starts, the matching phase of the import process automatically begins to identify if each record in the interface table has an associated record in the TCA Registry. The matching determines whether an imported record is inserted as a new party or updates an existing party in the TCA Registry.
The unique ID of each record in the interface table is compared against the Source System Management (SSM) mappings. See: Unique IDs for Interface Table Records.
A SSM mapping links a TCA record to the original record from an external source. SSM mappings are enabled for these tables:
See: Source Systems Overview, Oracle Trading Community Architecture Administration Guide.
The unique ID indicates the source ID and source system of the interface table record. If that unique ID matches a SSM mapping, the interface table record will be used to update the corresponding existing record in TCA. If no match is found, the interface table record will be inserted into the TCA Registry as a new party.
For example, you import a record from the Vision source system, with source ID 100, for the first time. This record is inserted as a new party in TCA, and a SSM mapping is created between this TCA party and the source ID 100 from Vision. If you try to reimport the record from Vision with source ID 100, that record would be matched to the existing SSM mapping, and the reimported data would update the existing TCA party.
Note: Each combination of source ID and source system can update only one party in the TCA Registry.
You can choose to load only entities without the associated party, as long as the provided source ID and source system matches a parent record in the TCA Registry to be updated. For example, you can import addresses without parties, if the addresses are matched to existing parties. Otherwise, you must import associated parties along with addresses.
You cannot change the source ID of a record in the TCA Registry during the import process unless you are importing data from D&B.
If an address's source ID in the interface tables matches an existing SSM address mapping, then the interface table address is compared to the existing address.
If the addresses are exactly the same, the existing address will be updated with the imported address.
If not the same, the value passed in the CORRECT_MOVE_INDICATOR column of the HZ_IMP_ADDRESSES_INT table determines if the address should be a correction or move. The default is move.
Correction: The HZ: Allow Updates of Address Records During Import profile option must be set to Yes. The imported address will update the existing address. If the country, state, province, county, city, or postal code changes due to the update, the tax assignment is deleted.
If the profile option is set to No, you will get an error. You should either change the profile option or the CORRECT_MOVE_INDICATOR column value.
Move: A new location is created with the imported address, and the existing address is inactivated. Any SSM mappings to the existing address will be changed to the new address, but none of the transactions are automatically transferred.
Related Topics
Before import from the interface tables into the TCA Registry, a record must pass import validations. All elements of an interface table record must succeed, or the entire record is considered with error.
Records that fail validations are marked, and all errors for one record are captured after the entire record is validated. After validation, only successful records are imported into the Registry.
The types of validations include:
Lookups. For example, the HZ_ORG_CONTACTS.DEPARTMENT_CODE value should be from the DEPARTMENT_TYPE lookup. If the HZ: Allow Import of Records with Disabled Lookups profile option is set to Yes, then values that reference disabled lookups are still valid.
Foreign keys. For example, HZ_LOCATIONS.COUNTRY should have a foreign key to FND_TERRITORIES.TERRITORY_CODE.
Requirements, if a value is mandatory or conditionally required. For example, HZ_CONTACT_POINTS.CONTACT_POINT_TYPE is mandatory.
Cross-column dependencies. For example, EDI as the value for HZ_CONTACT_POINTS.COMMUNICATION_TYPE is allowed only if the value for HZ_CONTACT_POINTS.OWNER_TABLE_ID is a party ID of an organization.
TCA columns that are not updateable. Values that are entered for such columns are ignored without error.
Flags that should have Y, N, or NULL as values.
Flexfields. If the HZ: Validate Flexfields During Import profile option is set to Yes, then flexfields are validated based on the flexfield setups.
These tables have flexfields:
Address sharing. Multiple parties cannot share a single address. If the same address applies to two parties, you must enter the address for each party.
Business rules.
The parent and child relationships of entities also affect whether a record succeeds or fails validation. See: Interface Table Parent and Child Relationships.
Related Topics
Bulk Import Interface Tables, Oracle Trading Community Architecture Reference Guide
After data is loaded into the TCA Registry, the TCA Import Postprocessing program automatically runs and kicks off various postimport processes. Even before some of these processes finish, imported data can already be viewed and used in the Oracle E-Business Suite.
Person name information is reformatted to be properly displayed based on context, location, and language.
Fuzzy keys are generated for customer and address information, if selected in the Generate Fuzzy Key During Post Processing parameter. See: Program Parameters.
Phone numbers imported in a raw format are split into the appropriate segments, for example area code, prefix, and so on.
Phone numbers that include letters are converted into numbers.
Certain relationship information is denormalized into the HZ_PARTIES table to improve performance for certain Oracle E-Business Suite applications.
The D&B hierarchy is created from D&B data that has been entered in a flat format.
A time zone description is applied to each address and phone number that has been imported or updated during import.
Tax assignment is calculated and regenerated, if address elements used to determine tax assignments are updated as a result of the import.
If the TCA Import Postprocessing program results in error, you can resubmit it from Standard Request Submission.
Use these parameters to resubmit the TCA Import Postprocessing program for a previous run that resulted in error.
Enter the request ID of the Import Batch to TCA Registry submission that the failed postprocessing was originally run for.
Enter the number of workers to run the program. The recommended number is four, and the maximum number is ten.
Related Topics
Records with errors are marked with E in the INTERFACE_STATUS column of the interface table and associated with detail records in the import errors table, HZ_IMP_ERRORS. Use the TCA Import Error report to view the error details for a specific import batch.
Run the TCA Import Error report, specifying the batch that was imported.
Go to the report output to see import errors for the batch.
Correct the data directly in the interface tables.
Change the INTERFACE_STATUS column value to C for each corrected record.
Run the Import Batch to TCA Registry program again for the same batch. Only records with an INTERFACE_STATUS column value of C is processed.
If the TCA Import Postprocessing program results in error, review the log of the program from Standard Request Submission. After you fix the errors, you can resubmit the TCA Import Postprocessing program. See: Postimport Processes.
Related Topics
Use the TCA Import Batch Purge program to purge batches from the import interface tables, as well as internal tables such as staging and error tables. You permanently remove all records in the batch from all these tables.
You should purge batches after a satisfactory number of records are successfully imported into the TCA Registry from that batch.
Purging the interface tables improves import performance. To archive imported data, you should copy the data to a set of custom tables.
Use the following parameter to submit the TCA Import Batch Purge program.
Enter the batch ID of the batch that you want to purge.
Related Topics