Monday 20 June 2011

CUSTOMER API

  CUSTOMER creation steps:=
====================================
1) create customer  -------------> HZ_PARTY_V2PUB.create_person
2) create location  --------------> hz_location_v2pub.create_location
3) create party_site  --------------->  hz_party_site_v2pub.create_party_site
4) create cusomer account   ----------------> hz_cust_account_v2pub.create_cust_account
5) customer_account_site  ---------------> hz_cust_account_site_v2pub.create_cust_acct_site
6) customer site account use ---------->  hz_cust_account_site_v2pub.create_cust_site_use
7) create party contact ---------->hz_party_contact_v2pub.create_org_contact
8) create HZ_CUST_ACCOUNT_ROLE ------------>HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role

===========================================================================================================

  VALIDATION:=
-----------------
1.organization  -------->AR_LOOKUPS
2.customer_name notnull
3.customer_number notnull
4.customer_catagory is notnull
5.cuntory    --------------> fnd_territories_tl
6. city, state,location  --------->AR_location_value
================================================================================================================

  step 1
------------
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 := 'ELANGOVAN';
 p_create_person_rec.person_last_name := 'RAGAVAN';
 p_create_person_rec.created_by_module := 'HZ_CPUI';
 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;

SELECT * FROM HZ_PARTIES WHERE TRUNC(creation_date)=TRUNC(SYSDATE)

SELECT * FROM HZ_PARTIES WHERE party_id=508742



***************************
Output information ....
x_party_id: 508742
x_party_number: 60517
x_profile_id: 500832
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

=================================================================================================

  STEP2
-----------
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 := 'IN';
 p_location_rec.address1 := 'DINDIGUL';
 p_location_rec.city := 'San Mateo';
 p_location_rec.postal_code := '9790092327';
 p_location_rec.state := 'CA';
 p_location_rec.created_by_module := 'HZ_CPUI';
 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;

SELECT * FROM HZ_LOCATIONS where trunc(creation_date)=trunc(sysdate)

SELECT * FROM HZ_LOCATIONS where location_id=29228


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

====================================================================================================

  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 :=508742; --<<value for party_id from step 2>
 p_party_site_rec.location_id := 29228; --<<value for location_id from step 3>
 p_party_site_rec.identifying_address_flag := 'Y';
 p_party_site_rec.created_by_module := 'HZ_CPUI';
 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;

select * from hz_party_sites where party_site_id=306650


***************************
Output information ....
x_party_site_id: 306650
x_party_site_number: 26498
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

============================================================================================

  step 4
------------
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 := 'Elango_account';
 p_cust_account_rec.created_by_module := 'HZ_CPUI';
 -- p_cust_account_rec.orig_system_reference := '001_001';  -- is not mandatory

 p_organization_rec.organization_name := 'FennerAPIc001';
 p_organization_rec.created_by_module := 'HZ_CPUI';

 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;

select * from hz_cust_accounts where trunc(creation_date)=trunc(sysdate)

select * from hz_cust_accounts where cust_account_id=164753


***************************
Output information ....
x_cust_account_id: 164753
x_account_number: 6953
x_party_id: 508743
x_party_number: 60518
x_profile_id: 271455
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************

========================================================================================================
  step 5
-------------
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
 mo_global.init ('AR');
 fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 50559,
                               resp_appl_id      => 222
                              );
 mo_global.set_policy_context ('S', 204);
 p_cust_acct_site_rec.cust_account_id := 164753; --<<value for cust_account_id you get from step 4>
 p_cust_acct_site_rec.party_site_id := 306650; --<<value for party_site_id from step 3>
-- p_cust_acct_site_rec.language := 'US';
 p_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
 --mo_global.set_policy_context ('S', 204);
 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;


select * from hz_cust_acct_sites_all where cust_acct_site_id=12388

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

==========================================================================================

  step 6
---------------

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 := 12388; --<<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 := 'HZ_CPUI';
 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;

select * from hz_cust_site_uses_all where site_use_id=14709


***************************
Output information ....
x_site_use_id: 14709
x_return_status: S
x_msg_count: 0
x_msg_data: 0
***************************
============================================================================================================

    step7
--------------

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 := 'HZ_CPUI';
p_org_contact_rec.party_rel_rec.subject_id :=508742; --<<value for party_id from step 2>
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 :=508743 ; --<<value for party_id from step 4>
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;

select * from hz_org_contacts where trunc(creation_date)=trunc(sysdate)

select * from hz_parties where party_id=508744

select * from hz_org_contacts where org_contact_id=194427

***************************
Output information ....
x_org_contact_id: 194427
x_party_rel_id: 412375
x_party_id: 508744
x_party_number: 60519
x_return_status: S
x_msg_count: 0
x_msg_data:
***************************
=======================================================================================================
    step8
----------------   

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 := 508744; --<<value for party_id from step 7>
p_cr_cust_acc_role_rec.cust_account_id := 164753; --<<value for cust_account_id from step 4>
p_cr_cust_acc_role_rec.cust_acct_site_id := 12388; --<<value for cust_acct_site_id from step 5>
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 := 'HZ_CPUI';

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;

select * from HZ_CUST_accounts where trunc(creation_date)=trunc(sysdate)


*************************** 

2 comments: