Customer Conversion In Oracle 11i Steps

/*--Step 1*/

creating the customer

SET SERVEROUTPUT ON

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(user_id,resp_appl_id,resp_id,user_id);

--fnd_global.apps_initialize(1008095,222,20678,1008095); ---bassprod

fnd_global.apps_initialize (1008095,

222,

20678,

1008095); --cship

p_cust_account_rec.account_name := 'sonali_Test_Customer1'; ---ACCOUNT NAME FROM LEGACY

p_cust_account_rec.price_list_id := 1000;

--p_cust_account_rec.order_type_id :=1111; --Added

p_cust_account_rec.status := 'A';

--p_cust_account_rec.ship_via := 'DHL';

--p_cust_account_rec.orig_system_reference := ;

p_cust_account_rec.ship_sets_include_lines_flag := 'Y';

p_cust_account_rec.created_by_module := 'TCAPI_EXAMPLE';

p_customer_profile_rec.profile_class_id := 1000;

---p_customer_profile_rec.standard_terms := ; --payment_term

p_customer_profile_rec.created_by_module := 'TCAPI_EXAMPLE';

p_organization_rec.organization_name := 'ORGANIZATION_KAZ'; ---customer_NAME FROM LEGACY

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;

/-->#################################################################

/*--step 2*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1008099,222,20678,1008099);

fnd_global.apps_initialize (1008099,

222,

20678,

1008099); --cship

p_location_rec.country := 'US'; --- 'US';

p_location_rec.address1 := 'Apps'; --- 'Street1';

p_location_rec.city := 'newyork'; ---- 'EDGEWATER';

p_location_rec.postal_code := '4830'; ---'07021';

p_location_rec.county := 'manhattan';

p_location_rec.state := 'ny'; ---- 'NJ';

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;

/

-->#################################################################

/*--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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1008095,222,20678,1008095);

fnd_global.apps_initialize (1008095,

222,

20678,

1008095); --cship

p_party_site_rec.party_id := 33544; --<

p_party_site_rec.location_id := 24102; --<

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;

/

-->#################################################################

/*--Step 4 ---U CAN SEE THE ADDRESS IN THE FRONT END*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1008095,222,20678,1008095);

fnd_global.apps_initialize (1008095,

222,

20678,

1008095); --cship

p_cust_acct_site_rec.cust_account_id := 33574; --6312; --<

p_cust_acct_site_rec.party_site_id := 51556; --8622; --<

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;

/

-->#################################################################

/*--Step5*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1005208,222,20678,1005208);

fnd_global.apps_initialize (1005327,

222,

20678,

1005327); --cship

p_cust_site_use_rec.cust_acct_site_id := 5259; --6378 ; --<

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 ('***************************');

p_cust_site_use_rec.cust_acct_site_id := 5259; --6378; --<

p_cust_site_use_rec.site_use_code := 'SHIP_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;

/

-->#################################################################

/*--Step 6*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1005208,222,20678,1005208);

--fnd_global.apps_initialize(1005313,222,20678,1005313);

fnd_global.apps_initialize (1005327,

222,

20678,

1005327); --cship

p_create_person_rec.person_pre_name_adjunct := 'MR.';

p_create_person_rec.person_first_name := 'Person'; ---'TESTING_PER'

p_create_person_rec.person_last_name := 'Test_Contact'; ----'TESTING_LAST'

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;

/

-->#################################################################

/*--Step 7 ---THIS WILL ASSIGN THE PARTY RELATIONSHIPS IN CUSTOMER FORM AND ALLOCATE THE

--- PERSON CREATED IN STEP 6 TO PARTY IN STEP1*/

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

fnd_client_info.set_org_context (204);

--Fnd_global.apps_initialize(1005208,222,20678,1005208);

-----fnd_global.apps_initialize(1005313,222,20678,1005313);

fnd_global.apps_initialize (1005327,

222,

20678,

1005327); --cship

--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 := 15512; --16861; --<

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 := 15511; --16860; --<

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;

/

-->#################################################################

/*--Step 8 THIS WILL CREATE THE CONTACT PERSON DETAILS FOR THE PARTY NUMBER

nOW WE CAN SEE THE CONTACT PERSON IN THE APPLICATION*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1005313,222,20678,1005313);

fnd_global.apps_initialize (1005327,

222,

20678,

1005327); --cship

-- 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 := 15513; --16862; --<

p_cr_cust_acc_role_rec.cust_account_id := 4971; --6312; --<

/*p_cr_cust_acc_role_rec.party_id := 18599; --<

p_cr_cust_acc_role_rec.cust_account_id := 7440; --<

p_cr_cust_acc_role_rec.cust_acct_site_id := 7716; --<*/

---p_cr_cust_acc_role_rec.primary_flag := 'Y'; ---if 2 contact persons r there only one can be '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;

/

-->#################################################################

/*--Step 9 this will create phone number at the contact points for party_id in step7*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1005313,222,20678,1005313);

fnd_global.apps_initialize (1005327,

222,

20678,

1005327); --cship

p_contact_point_rec.contact_point_type := 'PHONE';

p_contact_point_rec.owner_table_name := 'HZ_PARTIES';

p_contact_point_rec.owner_table_id := 15513; --16679 ; --

p_contact_point_rec.created_by_module := 'TCAPI_EXAMPLE';

p_phone_rec.Phone_number := '55991106';

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;

/

-->#################################################################

/*--Step 9 this will create phone number at the contact points for party_id in step1*/

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

fnd_client_info.set_org_context (204);

--fnd_global.apps_initialize(1005208,222,20678,1005208);

fnd_global.apps_initialize (1005327,

222,

20678,

1005327); --cship

p_contact_point_rec.contact_point_type := 'PHONE';

p_contact_point_rec.owner_table_name := 'HZ_PARTIES';

p_contact_point_rec.owner_table_id := 15511; --16860; --

p_contact_point_rec.created_by_module := 'TCAPI_EXAMPLE';

p_phone_rec.Phone_number := '9885404729';

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;/