Customer TCA Architecure and API's
Architecture
Create Organization
DECLARE p_organization_rec hz_party_v2pub.organization_rec_type; x_return_status VARCHAR2 (2000); x_msg_count NUMBER; x_msg_data VARCHAR2 (2000); x_party_id NUMBER; x_party_number VARCHAR2 (2000); x_profile_id NUMBER; BEGIN p_organization_rec.organization_name := 'erpschools'; p_organization_rec.created_by_module := 'ERPSCHOOLS_DEMO'; hz_party_v2pub.create_organization ('T', p_organization_rec, x_return_status, x_msg_count, x_msg_data, x_party_id, x_party_number, x_profile_id ); DBMS_OUTPUT.put_line ('party id ' || x_party_id); DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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;
=>
Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables. Create a 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 := '2500 W Higgins Rd'; p_location_rec.address2 := 'Suite 920'; p_location_rec.city := 'Thumuluru'; p_location_rec.postal_code := '60118'; p_location_rec.state := 'IL'; p_location_rec.created_by_module := 'ERPSCHOOLS_DEMO'; 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('location id '||x_location_id); dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255)); dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count)); dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255)); 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 Note: The above API shall create an address record in hz_locations table. Create a Party Site: Use the organization_id and location_id created above and create a party site. 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 := 1272023; p_party_site_rec.location_id := 359086; p_party_site_rec.identifying_address_flag := 'Y'; p_party_site_rec.created_by_module := 'ERPSCHOOLS_DEMO'; 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 ('party site id ' || x_party_site_id); DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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; Note: The above API creates a record in hz_party_sites table. Create Party Site Use Use the above party site created DECLARE p_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type; x_party_site_use_id NUMBER; x_return_status VARCHAR2 (2000); x_msg_count NUMBER; x_msg_data VARCHAR2 (2000); BEGIN p_party_site_use_rec.site_use_type := 'SHIP_TO'; p_party_site_use_rec.party_site_id := 349327; p_party_site_use_rec.created_by_module := 'ERPSCHOOLS_DEMO'; hz_party_site_v2pub.create_party_site_use ('T', p_party_site_use_rec, x_party_site_use_id, x_return_status, x_msg_count,
=>
x_msg_data ); DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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; Create a Contact Point DECLARE p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type; p_edi_rec hz_contact_point_v2pub.edi_rec_type; p_email_rec hz_contact_point_v2pub.email_rec_type; p_phone_rec hz_contact_point_v2pub.phone_rec_type; p_telex_rec hz_contact_point_v2pub.telex_rec_type; p_web_rec hz_contact_point_v2pub.web_rec_type; x_return_status VARCHAR2 (2000); x_msg_count NUMBER; x_msg_data VARCHAR2 (2000); x_contact_point_id NUMBER; BEGIN p_contact_point_rec.contact_point_type := 'PHONE'; p_contact_point_rec.owner_table_name := 'HZ_PARTIES'; p_contact_point_rec.owner_table_id := '1272023'; p_contact_point_rec.primary_flag := 'Y'; p_contact_point_rec.contact_point_purpose := 'BUSINESS'; p_phone_rec.phone_area_code := '650'; p_phone_rec.phone_country_code := '1'; p_phone_rec.phone_number := '506-7000'; p_phone_rec.phone_line_type := 'GEN'; p_contact_point_rec.created_by_module := 'ERPSCHOOLS_DEMO'; hz_contact_point_v2pub.create_contact_point ('T', p_contact_point_rec,
=>
p_edi_rec, p_email_rec, p_phone_rec, p_telex_rec, p_web_rec, x_contact_point_id, x_return_status, x_msg_count, x_msg_data ); DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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;
=>
Create a Org Contact: 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 := 'ERPSCHOOLS_DEMO'; p_org_contact_rec.party_rel_rec.subject_id := 16077; 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 := 1272023; 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 (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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;
=>
Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as 'PARTY_RELATIONSHIP'. Create a Customer 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_profilerec_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 := 'John''s A/c'; p_cust_account_rec.created_by_module := 'ERPSCHOOLS_DEMO'; p_person_rec.person_first_name := 'John'; p_person_rec.person_last_name := 'Smith'; 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 (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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; Note:
=>
This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party. Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization. Create a Customer Account Site Use an existing Party Site
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 := 3472; p_cust_acct_site_rec.party_site_id := 1024; p_cust_acct_site_rec.LANGUAGE := 'US'; p_cust_acct_site_rec.created_by_module := 'TCA-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 (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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;
Create Customer Account Site Use Code: 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 := 3580; p_cust_site_use_rec.site_use_code := 'INV'; p_cust_site_use_rec.LOCATION := 'TCA'; p_cust_site_use_rec.created_by_module := 'ERPSCHOOLS_DEMO'; 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 (SUBSTR ('x_return_status = ' || x_return_status, 1, 255 ) ); DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count)); DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255)); 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; More Customer API's:
=>
Org Contact Hz_party_contact_v2pub.Create_Org_Contact_Role Role Relationships HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE Customer HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile Profile Customer Profile HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt Amount Customer HZ_PARTY_INFO_V2PUB.create_credit_rating Credit Rating Sales Person JTF_RS_SALESREPS_PUB.CREATE_SALESREP Sales reps JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES Territories Customer HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE contacts Customer HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility Contact Role