Oracle EBS AR 客户API

------------------------------------
1. Set Environment

------------------------------------

-- 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 := 'CUSEXAPIFIG02';
 p_cust_account_rec.created_by_module := 'TCAPI_EXAMPLE';
 
 p_organization_rec.organization_name := 'CUSEXAPIFIG02';
 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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_cust_account_id: 6075
x_account_number: 3040
x_party_id: 17092
x_party_number: 14444
x_profile_id: 5290
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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_location_id: 13326
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 := 17092; --value for party_id from step 2>
 p_party_site_rec.location_id := 13326; --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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_party_site_id: 8885
x_party_site_number: 6893
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 := 6075; --value for cust_account_id you get from step 2>
 p_cust_acct_site_rec.party_site_id := 8885; --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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_cust_acct_site_id: 6155
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 := 6155; --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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_site_use_id: 7149
x_return_status: S
x_msg_count: 0
x_msg_data: 0
***************************

/* 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 := 'ExFennerct4';
 p_create_person_rec.person_last_name := 'ExGiraldoct4';
 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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_party_id: 17093
x_party_number: 14445
x_profile_id: 17187
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 := 17093; --value for party_id from step 7>
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 := 17092; --Value for party_id from step 2>
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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_org_contact_id: 7110
x_party_rel_id: 7788
x_party_id: 17094
x_party_number: 14446
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

------------------------------------
9. Create a contact using party_id you get 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 := 17094; --value for party_id from step 8>
p_cr_cust_acc_role_rec.cust_account_id := 6075; --value for cust_account_id from step 2>
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('***************************');
 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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

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

/* END contact */


/* Create the contact ROLE for the Org Contact - (Contact Roles zone on Customers Form)*/

------------------------------------
10. Create the org contact role using x_cust_account_role_id from step 9

------------------------------------

DECLARE
p_role_responsibility_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.ROLE_RESPONSIBILITY_REC_TYPE;
x_responsibility_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);

BEGIN

p_role_responsibility_rec.cust_account_role_id := 5857; --value for x_cust_account_role_id from step 9>
p_role_responsibility_rec.responsibility_type := 'SOLD_TO';
p_role_responsibility_rec.created_by_module := 'TCAPI_EXAMPLE';

HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility (
'T',
p_role_responsibility_rec,
x_responsibility_id,
x_return_status,
x_msg_count,
x_msg_data
);


 dbms_output.put_line('***************************');
 dbms_output.put_line('Output information ....');
 dbms_output.put_line('***************************');
 dbms_output.put_line('x_responsibility_id: '||x_responsibility_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('***************************');

 IF x_msg_count >1 THEN
  FOR I IN 1..x_msg_count
   LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
  END LOOP;
 END IF;

END;
/

***************************
Output information ....
***************************
x_responsibility_id: 3162
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

/* End contact ROLE */

commit;