Checked for relevance on 10-Nov-2008 

How To Create A Contact At Customer Level With Phone Via TCA API 
----------------------------------------------------------------

Example of How To Add A Contact At Customer Level With Phone Via TCA API

------------------------------------
-- 1a. Setup the Org_id
------------------------------------
exec dbms_application_info.set_client_info('204');

------------------------------------
-- 1b. Show the output variables
------------------------------------
set serveroutput on

------------------------------------
-- 2. Create a party and an account
------------------------------------
DECLARE
 p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
 p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
 p_customer_profile_rec
 HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
 x_cust_account_id NUMBER;
 x_account_number VARCHAR2(2000);
 x_party_id NUMBER;
 x_party_number VARCHAR2(2000);
 x_profile_id NUMBER;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);

BEGIN
 p_cust_account_rec.account_name := 'Fenner_01';
 p_cust_account_rec.created_by_module := 'TCAPI_EXAMPLE';

 p_organization_rec.organization_name := 'Fenner_01';
 p_organization_rec.created_by_module := 'TCAPI_EXAMPLE';

 hz_cust_account_v2pub.create_cust_account(
 'T',
 p_cust_account_rec,
 p_organization_rec,
 p_customer_profile_rec,
 'F',
 x_cust_account_id,
 x_account_number,
 x_party_id,
 x_party_number,
 x_profile_id,
 x_return_status,
 x_msg_count,
 x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_cust_account_id: '||x_cust_account_id);
 dbms_output.put_line('x_account_number: '||x_account_number);
 dbms_output.put_line('x_party_id: '||x_party_id);
 dbms_output.put_line('x_party_number: '||x_party_number);
 dbms_output.put_line('x_profile_id: '||x_profile_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_cust_account_id: 10033
x_account_number: 2441
x_party_id: 11337
x_party_number: 20413
x_profile_id: 3231
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************


/* BEGIN address  */
------------------------------------
-- 3. Create a physical location
------------------------------------
DECLARE
 p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
 x_location_id NUMBER;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);
BEGIN
 p_location_rec.country := 'US';
 p_location_rec.address1 := 'Address4';
 p_location_rec.city := 'San Mateo';
 p_location_rec.postal_code := '94401';
 p_location_rec.state := 'CA';
 p_location_rec.created_by_module := 'TCAPI_EXAMPLE';
 hz_location_v2pub.create_location(
 'T',
 p_location_rec,
 x_location_id,
 x_return_status,
 x_msg_count,
 x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_location_id: '||x_location_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_location_id: 21581
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************


------------------------------------
-- 4. Create a party site using party_id from step 2 and location_id from step 3
------------------------------------
DECLARE
 p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
 x_party_site_id NUMBER;
 x_party_site_number VARCHAR2(2000);
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);
BEGIN
 p_party_site_rec.party_id := 11337; --<
 p_party_site_rec.location_id := 21581; --<
 p_party_site_rec.identifying_address_flag := 'Y';
 p_party_site_rec.created_by_module := 'TCAPI_EXAMPLE';
 hz_party_site_v2pub.create_party_site(
 'T',
 p_party_site_rec,
 x_party_site_id,
 x_party_site_number,
 x_return_status,
 x_msg_count,
 x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_party_site_id: '||x_party_site_id);
 dbms_output.put_line('x_party_site_number: '||x_party_site_number);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_party_site_id: 5967
x_party_site_number: 4476
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

------------------------------------
-- 5. Create an account site using cust_account_id from step 2 and party_site_id from step 4.
------------------------------------
DECLARE
 p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);
 x_cust_acct_site_id NUMBER;
BEGIN
 p_cust_acct_site_rec.cust_account_id := 10033; --<
 p_cust_acct_site_rec.party_site_id := 5967; --<
 p_cust_acct_site_rec.language := 'US';
 p_cust_acct_site_rec.created_by_module := 'TCAPI_EXAMPLE';
 hz_cust_account_site_v2pub.create_cust_acct_site(
 'T',
 p_cust_acct_site_rec,
 x_cust_acct_site_id,
 x_return_status,
 x_msg_count,
 x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_cust_acct_site_id: '||x_cust_acct_site_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_cust_acct_site_id: 4672
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

------------------------------------
-- 6. Create an account site use using cust_acct_site_id from step 5 and site_use_code='BILL_TO'
------------------------------------
DECLARE
 p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
 p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
 x_site_use_id NUMBER;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);
BEGIN
 p_cust_site_use_rec.cust_acct_site_id := 4672; --<
 p_cust_site_use_rec.site_use_code := 'BILL_TO';
 p_cust_site_use_rec.created_by_module := 'TCAPI_EXAMPLE';
 hz_cust_account_site_v2pub.create_cust_site_use(
 'T',
 p_cust_site_use_rec,
 p_customer_profile_rec,
 '',
 '',
 x_site_use_id,
 x_return_status,
 x_msg_count,
 x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_site_use_id: '||x_site_use_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_count);
 dbms_output.put_line('***************************');

END;
/

/* END address  */

commit;

/* BEGIN contact to an organization */
------------------------------------
-- 7. Create a definition contact
------------------------------------
DECLARE
 p_create_person_rec HZ_PARTY_V2PUB.person_rec_type;
 x_party_id NUMBER;
 x_party_number VARCHAR2(2000);
 x_profile_id NUMBER;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);

BEGIN
 p_create_person_rec.person_pre_name_adjunct := 'MR.';
 p_create_person_rec.person_first_name := 'FENNERCTFN_01';
 p_create_person_rec.person_last_name := 'FENNERCTLN_01';
 p_create_person_rec.created_by_module := 'TCAPI_EXAMPLE';

 HZ_PARTY_V2PUB.create_person(
 'T',
 p_create_person_rec,
 x_party_id,
 x_party_number,
 x_profile_id,
 x_return_status,
 x_msg_count,
 x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_party_id: '||x_party_id);
 dbms_output.put_line('x_party_number: '||x_party_number);
 dbms_output.put_line('x_profile_id: '||x_profile_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_party_id: 11338
x_party_number: 20414
x_profile_id: 8323
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

------------------------------------
-- 8. Create a relation cont-org using party_id from step 7 and party_id from step 2
------------------------------------
DECLARE
p_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
--p_org_contact_rec.department_code := 'ACCOUNTING';
--p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
--p_org_contact_rec.decision_maker_flag := 'Y';
--p_org_contact_rec.job_title_code := 'APC';
p_org_contact_rec.created_by_module := 'TCAPI_EXAMPLE';
p_org_contact_rec.party_rel_rec.subject_id := 11338; --<
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id := 11337; --<
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
hz_party_contact_v2pub.create_org_contact(
'T',
p_org_contact_rec,
x_org_contact_id,
x_party_rel_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_org_contact_id: '||x_org_contact_id);
 dbms_output.put_line('x_party_rel_id: '||x_party_rel_id);
 dbms_output.put_line('x_party_id: '||x_party_id);
 dbms_output.put_line('x_party_number: '||x_party_number);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_org_contact_id: 4566
x_party_rel_id: 5789
x_party_id: 11339
x_party_number: 20415
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************


------------------------------------
-- 9. Create a contact using party_id you get in step 8 and cust_account_id from step 2
------------------------------------
DECLARE
p_cr_cust_acc_role_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
x_cust_account_role_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);

BEGIN

-- NOTE:
-- must be unique CUST_ACCOUNT_ID, PARTY_ID,ROLE_TYPE 
-- must be unique CUST_ACCT_SITE_ID, PARTY_ID,ROLE_TYPE

p_cr_cust_acc_role_rec.party_id := 11339; --<
p_cr_cust_acc_role_rec.cust_account_id := 10033; --<
p_cr_cust_acc_role_rec.primary_flag := 'Y';
p_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_cr_cust_acc_role_rec.created_by_module := 'TCAPI_EXAMPLE';

HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role(
'T',
p_cr_cust_acc_role_rec,
x_cust_account_role_id,
x_return_status,
x_msg_count,
x_msg_data);

 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_cust_account_role_id: '||x_cust_account_role_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_cust_account_role_id: 4556
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************


/* END contact */

commit;




/* Begin phone */
------------------------------------------------------
-- 10. Create phon using party_id you get in atep 8
------------------------------------------------------
DECLARE
p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
p_edi_rec_type HZ_CONTACT_POINT_V2PUB.edi_rec_type;
p_email_rec_type HZ_CONTACT_POINT_V2PUB.email_rec_type;
p_telex_rec_type HZ_CONTACT_POINT_V2PUB.telex_rec_type;
p_web_rec_type HZ_CONTACT_POINT_V2PUB.web_rec_type;

x_contact_point_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);

BEGIN

p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := 11339; --
p_contact_point_rec.created_by_module := 'TCAPI_EXAMPLE';
p_phone_rec.Phone_number := '407';
p_phone_rec.phone_line_type := 'GEN';

HZ_CONTACT_POINT_V2PUB.create_contact_point (
'T',
p_contact_point_rec,
p_edi_rec_type,
p_email_rec_type,
p_phone_rec,
p_telex_rec_type,
p_web_rec_type,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data
);


 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('x_contact_point_id: '||x_contact_point_id);
 dbms_output.put_line('x_return_status: '||x_return_status);
 dbms_output.put_line('x_msg_count: '||x_msg_count);
 dbms_output.put_line('x_msg_data: '||x_msg_data);
 dbms_output.put_line('***************************');

END;
/

***************************
Output information ....
x_contact_point_id: 8141
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

commit;