asked on

Creating a person entry in HZ_Parties table

Hello,

I am developing a form for one of our clients. As a part of this developement I need to insert a record into HZ_PARTIES table using the API
HZ_PARTY_V2PUB.CREATE_PERSON.

I am totally new to these APIs and hence finding it a bit tough to go forward even though I have some pdf documentation from Oracle. Kindly provide me the details on how to use the "create_person" to create an entry in HZ_PARTIES with party_type as PERSON.

Regards

Dr_Billy

First of all what is the type of the person you need to create , is it a customer or an employee or a group ?

I don't if you have seen the attached file or not but it may be useful to have a look at the documentation of this API in that attached document.

https://metalink.oracle.com/cgi-bin/cr/getfile.cgi?p_attid=241320.1:1

Here is a similar example but for a customer hope it's helpful


Subject: 	How to create a PERSON customer via TCA API ?
  	Doc ID: 	NOTE:204170.1 	Type: 	PROBLEM
  	Last Revision Date: 	09-NOV-2008 	Status: 	PUBLISHED
 
 
Checked for relevance on 10-Nov-2008 
 
Goal: How to create a PERSON customer via TCA API ?
Fact: Oracle Trading Community 11.5.7
Fix: Example to create a PERSON customer via API in TCA.
 
------------------------------------
-- 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_person_rec HZ_PARTY_V2PUB.PERSON_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 := 'TPERSON01';
 p_cust_account_rec.created_by_module := 'TCAPI_EXAMPLE';
 
 p_person_rec.person_first_name := 'FennerT1';
 p_person_rec.person_last_name := 'GiraldoT1';
 p_person_rec.created_by_module := 'TCAPI_EXAMPLE';
 
 
 hz_cust_account_v2pub.create_cust_account(
 'T',
 p_cust_account_rec,
 p_person_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: 7645
x_account_number: 3951
x_party_id: 7989
x_party_number: 6333
x_profile_id: 4742
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 := 'Address1';
 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: 5126
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 := 7989; --<<value for party_id from step 2>
 p_party_site_rec.location_id := 5126; --<<value for location_id from step 3>
 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: 6670
x_party_site_number: 5303
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 := 7645; --<<value for cust_account_id you get from step 2>
 p_cust_acct_site_rec.party_site_id := 6670; --<<value for party_site_id from step 4>
 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: 6583
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 := 6583; --<<value for cust_acct_site_id from step 5>
 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;
 
 
***************************
Output information ....
x_site_use_id: 6637
x_return_status: S
x_msg_count: 0
x_msg_data: 0
***************************
 
/* END address  */
 
commit;
 
 
.

Open in new window

sreejithm

ASKER

Dr Billy,

We have the customers (organizations) already created. Now I need to create the employees. I understand that I need to follow this sequence

1. create person using the HZ_PARTY_V2PUB.CREATE_PERSON API
2. create relations using Hz_Party_Contact_V2pub.create_org_contact

and for any changed (eg terminated employee) use Hz_Party_Contact_V2pub.update_org_contact

Kindly give me some guidelines on the usage of these APIs. I am unable to access the link that you have provided from metalink.

Regards,

ASKER CERTIFIED SOLUTION

Dr_Billy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dr_Billy

Hope that helps

sreejithm

ASKER

Thanks Dr billy. It really helped.