Importing Contacts Into Siebel CTMS, With Troubleshooting Tips

Recently, we had a client who wanted to import contacts into their Siebel CTMS, which uses a moderately modified version of Innovation Pack 2013 (a.k.a. version 8.1.1.11). The contact data was stored in a spreadsheet and looked something like this:

This seemed to be a perfect match for the import functionality, which is available out of the box from Oracle:In total, they had about 12,000 records they wanted to import, and each contact had a corresponding field in Siebel, with about 20 fields per contact. Some contacts shared addresses (they had the same work location), but each contact was listed only once and only had one address associated.

Since there is a limit to the number of records that can be imported into Siebel CTMS at once, we had the customer split the spreadsheet into eight files and save each file as a Text (tab delimited) file. The first line of each file needed to be the column heading, and needed to match the field names that are in Siebel.

Once they chose to import from the menu, they navigated through two dialog windows; the first identified the import file, while the second displayed the mapping between Siebel and the import file:

While importing each file took several minutes, the import function was still easier than other methods, like EIM (Enterprise Integration Manager), for which the client would need a seasoned Siebel developer to load the data into the Oracle database via SQLLoader.

Accelerate Clinical Trials With AI and Real-Time Data Collaboration

Discover a unified clinical data review and collaboration solution that optimizes the integration and review of complex, ever-changing clinical data.

Get the Guide

That said, we noticed that, for roughly 25% of the imported contacts, a second (and incorrect) address was associated with the contact. Of those 25% incorrect addresses, roughly 50% were labeled as the primary address for that contact. This appeared to be random and, frankly, there was no way to figure out which address was incorrectly added without painstakingly looking through each contact imported into Siebel and comparing it to the spreadsheet.

But, you can use the following SQL to identify which contacts have multiple addresses:

SELECT contact_id,

COUNT(contact_id) as dupe_col

FROM siebel.s_con_addr

GROUP BY contact_id

HAVING COUNT(contact_id) > 1

ORDER BY COUNT(contact_id) DESC ;

So, I suggest you run the SQL above before the import, and then again after the import. If the import worked, the number of records will match.

To avoid this issue all together, I figured out a successful workaround (below). All of these steps are performed in Siebel Tools:

1. Copy the Contact business component (BC) and call it Import Contact
2. Update the Contact business object, adding Import Contact as a business object component
3. Create a new Import Object based on Import Contact
4. On the new Import Contact BC, query for multi value links *Address* and make all of them Inactive, except for Pharma Address (our client’s application uses Pharma Address, yours might use a different one)
5. On the new Import Contact BC, query for multi-value fields where the MVL = *Address* and make all of them Inactive, except for Pharma Address (or the specific address your version of Siebel uses)
6. Create a new list applet based on this new BC, and add it to a view

I am curious if others have noticed this error, as it is not necessarily easy to spot, and what alternative workarounds you’ve implemented. I tested this issue on several different versions of Siebel, from 8.1.1.4 to 16.3, and they all had issues importing addresses related to contacts. I recently opened an SR (service request) with Oracle, and I will update this blog if Oracle determines an alternative approach. Stay tuned!