Loading Data into the Interface Tables

You should have a strong knowledge of the data and data structure to be imported. You can load data through:

Import Batches

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.

Batch IDs

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.

Unique IDs for Interface Table Records

You must provide a unique ID for each record in the interface table. The unique ID is a combination of:

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.

These IDs serve to link:

You should maintain all unique IDs locally in your system, and use them in the future to update specific information in the TCA Registry.

Unique Source IDs for Importing Associated Accounts

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:

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:

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.

Inserts and Updates

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.

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.

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:

Null Values for Update

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.

Party and Party Site Numbers

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 no values for these columns are passed, the import process generates values from an internal sequence.

You should maintain one of these strategies:

Primary Flags

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