My Oracle Support Banner

How to Import Contact Points for Organization Contact Through "Import Batch to TCA Registry"? (Doc ID 759924.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Trading Community - Version 12.0 and later
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

NOTE: In the images below and/or the attached document, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision instance). Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

 

While importing data from a flat file (csv) to TCA registry, organization as well as person (for organization contact level) is being imported.    The bulk import is creating records for the contact points for a person and not for the person as 
an organizational contact.  In HZ_IMP_CONTACTS_INT table, relationship type is 'CONTACT' and code is 'CONTACT OF' is provided and subject_id is of person orig_system_ref and object_id
is of organization orig_system_ref provided. Tried with contact orig_system_reference also, still it is not resolving problem.

 

Here is the test case:

SQL > set serveroutput on;

------------------------------------------
Insert this information into interface table.
------------------------------------------

INSERT INTO AR.HZ_IMP_PARTIES_INT
(
BATCH_ID,
PARTY_ORIG_SYSTEM,
PARTY_ORIG_SYSTEM_REFERENCE,
INSERT_UPDATE_FLAG,
PARTY_TYPE,
DUNS_NUMBER_C,
INTERFACE_STATUS,
DQM_ACTION_FLAG,
DUP_WITHIN_INT_FLAG,
ERROR_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REQUEST_ID,
CREATED_BY_MODULE,
ORGANIZATION_NAME,
ORGANIZATION_TYPE
)
VALUES
(
15008, -- value of BATCH_ID obtained in step 1
'DNB',
'JackieDNB01',
'I',
'ORGANIZATION',
NULL,
NULL,
'N',
'N',
NULL,
SYSDATE,
-1,
SYSDATE,
-1,
NULL,
'HZ_IMPORT',
'Jackie Import 01',
'ORGANIZATION'
);




INSERT INTO AR.HZ_IMP_PARTIES_INT
(
BATCH_ID,
PARTY_ORIG_SYSTEM,
PARTY_ORIG_SYSTEM_REFERENCE,
INSERT_UPDATE_FLAG,
PARTY_TYPE,
PERSON_FIRST_NAME,
PERSON_LAST_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY_MODULE
)
VALUES
(
15008, -- value of BATCH_ID obtained in step 1
'DNB',
'JackiePerson1',
'I',
'PERSON',
'Jackie',
'ContactTest',
SYSDATE,
-1,
SYSDATE,
-1,
'HZ_IMPORT'
);

Run Import Batch to TCA Registry concurrent program and make sure the concurrent program completes with no error.

SQL> select party_name, party_id, party_type,ORIG_SYSTEM_REFERENCE from hz_parties where party_id > 314532 and party_id < 314536;

PARTY_NAME PARTY_ID PARTY_TYPE                 ORIG_SYSTEM_REFERE
---------------------------- ---------- -----------------  ------------------
Jackie Import 01 314533 ORGANIZATION             JackieDNB01
Jackie ContactTest 314534 PERSON                    JackiePerson1


SQL> select ORIG_SYSTEM,ORIG_SYSTEM_REFERENCE from hz_orig_sys_references WHERE OWNER_TABLE_NAME='HZ_PARTIES' and OWNER_TABLE_ID in (314533,314534);

ORIG_SYSTEM ORIG_SYSTEM_REFERENCE
------------------------------ ------------------
DNB JackieDNB01
DNB JackiePerson1

SQL> select party_name, party_id, party_type,ORIG_SYSTEM_REFERENCE from hz_parties where party_name like '%Jackie%ContactTe%';

PARTY_NAME PARTY_ID PARTY_TYPE ORIG_SYSTEM_REFERE
---------------------------- ---------- --------------------------- ------------------
Jackie ContactTest-Jackie Import 01 314535        PARTY_RELATIONSHIP 314535
Jackie ContactTest                            314534       PERSON      JackiePerson1


SQL> select ORIG_SYSTEM, ORIG_SYSTEM_REFERENCE, OWNER_TABLE_NAME, OWNER_TABLE_ID from hz_orig_sys_references where ORIG_SYSTEM_REFERENCE like 'Jackie%';

ORIG_SYSTEM     ORIG_SYSTEM_REFERE          OWNER_TABLE_NAME OWNER_TABLE_ID
------------------     ----------------- ----------------- --------------
DNB Jackie                  Conta47476                     HZ_ORG_CONTACTS        126096
DNB Jackie                  DNB01                              HZ_PARTIES                    314533
DNB Jackie                  Person1                           HZ_PARTIES                     314534



Insert into ar.HZ_IMP_CONTACTPTS_INT (
Batch_id,
Cp_orig_system,
Cp_orig_system_reference,
Party_orig_system,
Party_orig_system_reference,
INSERT_UPDATE_FLAG,
CONTACT_POINT_TYPE,
RAW_PHONE_NUMBER,
PHONE_NUMBER,
EMAIL_ADDRESS,
CREATED_BY_MODULE)
Values
(15011,
'DNB',
'J_0001',
'DNB',
'JackieConta47476',
'I',
'GEN',
'12123459999',
'12123459999',
'JackieContact@yahoo.com',
'TCA_IMPORT');

SQL> select phone_number, email_address, owner_table_name, owner_table_id, creation_date from hz_contact_points where email_address like 'JackieContact%';
no row returned

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.