Monday, January 16, 2017

Trading Community Architecture (TCA)

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables.
Architecture
Create Organization
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
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.
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
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
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 an Org Contact:
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:
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
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:
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 RoleHz_party_contact_v2pub.Create_Org_Contact_Role
RelationshipsHZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer ProfileHZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile AmountHZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit RatingHZ_PARTY_INFO_V2PUB.create_credit_rating
Sales PersonJTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps TerritoriesJTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contactsHZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact RoleHZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...