详解ebs接口之客户配置文件导入（二）

 ------------------------------------
-- 1a. Setup the Org_id
------------------------------------

exec dbms_application_info.set_client_info('204');

 
------------------------------------
-- 1b. Show the output variables
------------------------------------

set serveroutput on

 
------------------------------------
-- 2a. 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);
 p_create_profile_amt  VARCHAR2(2000);

BEGIN
-- record for the account
 p_cust_account_rec.account_name := 'FennerProfAPIamtc002';
 p_cust_account_rec.created_by_module := 'TCAPI_EXAMPLE';
 -- p_cust_account_rec.orig_system_reference := '001_001';  -- is not mandatory

-- record for the organization
 p_organization_rec.organization_name := 'FennerProfAPIamtc002';
 p_organization_rec.created_by_module := 'TCAPI_EXAMPLE';

-- record for the profile (this will use he DEFAULT profile but change these fields)
 p_customer_profile_rec.credit_checking := 'Y';
 p_customer_profile_rec.interest_charges := 'N';
 -- as interest charges is N, you need to set this two values in null
   p_customer_profile_rec.charge_on_finance_charge_flag := FND_API.G_MISS_CHAR;
   p_customer_profile_rec.interest_period_days := FND_API.G_MISS_NUM;
 p_customer_profile_rec.created_by_module := 'TCAPI_EXAMPLE';

-- Record for the profile amounts
-- You are not able to assign values for the amounts in this API
-- As you want to create specific values for the amounts set the field in 'F'
-- You will insertthe information later
 p_create_profile_amt := 'F';


 hz_cust_account_v2pub.create_cust_account(
 'T',
 p_cust_account_rec,
 p_organization_rec,
 p_customer_profile_rec,
 p_create_profile_amt,
 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: 7744
x_account_number: 3991
x_party_id: 19566
x_party_number: 16479
x_profile_id: 8679
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************


------------------------------------
-- 2b. Create the profile amounts record
------------------------------------

DECLARE
 p_cpamt_rec HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
 v_cust_account_profile_id NUMBER;

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

BEGIN

select cust_account_profile_id into v_cust_account_profile_id
from hz_customer_profiles where cust_account_id = 7744; --<<value for cust_account_id from step 2a

p_cpamt_rec.cust_account_profile_id := v_cust_account_profile_id;
p_cpamt_rec.currency_code := 'USD'; --<< Currency Code
p_cpamt_rec.created_by_module := 'TCAPI_EXAMPLE';
p_cpamt_rec.overall_credit_limit := 1000000;
p_cpamt_rec.cust_account_id := 7744;  --<<value for cust_account_id from step 2a

HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt (
 'T',
 'T',
 p_cpamt_rec,
 x_cust_acct_profile_amt_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_profile_amt_id: '||x_cust_acct_profile_amt_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_profile_amt_id: 14883
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 := 'FennerProfAPIamtc002';
 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: 15538
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

------------------------------------
-- 4. Create a party site using party_id from step 2a 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 := 19566; --<<value for party_id from step 2a>
 p_party_site_rec.location_id := 15538; --<<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: 10710
x_party_site_number: 8437
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

------------------------------------
-- 5. Create an account site using cust_account_id from step 2a 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 := 7744; --<<value for cust_account_id you get from step 2a>
 p_cust_acct_site_rec.party_site_id := 10710; --<<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: 7261
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 := 7261; --<<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: 8943
x_return_status: S
x_msg_count: 0
x_msg_data: 0
***************************

/* END address  */

commit;