Category Archives: API’s/Interfaces

Create Contact Point API

TCA Create Contact Point API

Name of the API : CREATE CONTACT POINT
Base Tables Affected : HZ_CONTACT_POINTS
PL/SQL Procedure used : CREATE_CONTACT_POINT
Package Used : HZ_CONTACT_POINT_V2PUB

Mandatory Parameters :
Parameter Name  Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_contact_point_rec IN CONTACT_POINT_REC_TYPE
p_edi_rec IN EDI_REC_TYPE:= G_MISS_EDI_REC
p_email_rec IN EMAIL_REC_TYPE:= G_MISS_EMAIL_REC
p_phone_rec IN PHONE_REC_TYPE:= G_MISS_PHONE_REC
p_telex_rec IN TELEX_REC_TYPE:= G_MISS_TELEX_REC
p_web_rec IN WEB_REC_TYPE:= G_MISS_WEB_REC
x_contact_point_id OUT NUMBER
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Create Contact Point API (Phone):
a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN contactphone.sql)
b) Enter the values for the Parameters Primary Flag, Contact Point Purpose, Phone
Area Code, Phone Country Code, Phone Number and Phone Line Type from SQL
Prompt itself as hard coding these parameters means the script has to be changed for
the value of these parameters each time a new Contact Point ( Phone ) is created.
c) The Parameter Primary Flag, Contact Point Purpose and Phone Line Type can be
entered in either lower case or upper case as the system will take care of changing the
case to upper.
d) The values for owner_table_id and owner_table_name parameters can be seen from
HZ_PARTIES table using the query :
SELECT owner_table_id, owner_table_name FROM hz_contact_points WHERE
owner_table_name LIKE ‘%HZ_PARTIES%’;
e) Enter the value for phone_country_code as a valid value from from hz_phone_country_codes
using the query :
SELECT territory_code, phone_country_code FROM hz_phone_country_codes
WHERE UPPER(territory_code) LIKE ‘%US%’;
f) Enter the value of area code using the query :
SELECT UPPER(area_code) FROM hz_timezone_mapping;
g) Set the organization id before running the script as :
EXEC fnd_client_info.set_org_context(‘<orgid>’); for 11i
OR For R12
EXEC mo_global.init (‘AR’);
EXEC mo_global.set_org_context(<org_id>, NULL, ‘AR’);
EXEC fnd_global.set_nls_context(‘AMERICAN’);
EXEC mo_global.set_policy_context(‘S’, <org_id>);

Example For Running a Create Contact Point API (Phone):

 Sample Output of the Create Contact Point (Phone) Script:

 Script To Verify whether the Contact Point API (Phone) is successful:

 

 

Related posts:

Party Site Use API

TCA Create Party Site Use API

Name of the API : CREATE PARTY SITE USE
Base Tables Affected : HZ_PARTY_SITE_USES
PL/SQL Procedure used : CREATE_PARTY_SITE_USE
Package Used : HZ_PARTY_SITE_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_party_site_use_rec IN PARTY_SITE_USE_REC_TYPE
x_party_site_use_id OUT NUMBER
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER

Procedure:

 General Tips for Running the Create Party Site Use API:
a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN createpsiteuse.sql)
b) Enter the values for the Parameters Party Site Use and Party Site Id from SQL
Prompt itself as hard coding these two parameters means the script has
to be changed for the value of these two parameters each time a new
Party Site Use is created.
c) The Parameter Party Site Use can be entered in either lower case or upper
case as the system will take care of changing the case to upper. That is The Site
use can be entered as bill_to or BILL_TO or Bill_To.
d) Enter the value for Party Site Id as a valid value from HZ_PARTY_SITES table.
SELECT party_site_id FROM hz_party_sites;
e)  Set the organization id before running the script as :
EXEC fnd_client_info.set_org_context(‘<orgid>’); for 11i
OR For R12
EXEC mo_global.init (‘AR’);
EXEC mo_global.set_org_context(<org_id>, NULL, ‘AR’);
EXEC fnd_global.set_nls_context(‘AMERICAN’);
EXEC mo_global.set_policy_context(‘S’, <org_id>);

Example For Running a Create Party Site Use API:

 Script To Verify whether the Create Party Site Use API is successful:

 

Related posts:

Create Party Site API TCA

Create Party Site API

Name of the API : CREATE PARTY SITE
Base Tables Affected : HZ_PARTY_SITES
PL/SQL Procedure used : CREATE_PARTY_SITE
Package Used : HZ_PARTY_SITE_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_party_site_rec IN PARTY_SITE_REC_TYPE
x_party_site_id OUT NUMBER
x_party_site_number OUT VARCHAR2
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Create Party Site API:

a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN createpsite.sql)
b) Enter the values for the Parameters Party Id and Location Id from the SQL
prompt itself as hard coding these two parameters means the script has
to be changed for the value of these two parameters each time a new
Party Site is created.
c) Enter the values for Party Id and Location Id as valid values from
HZ_PARTIES and HZ_LOCATIONS respectively.
SELECT party_id FROM hz_parties;
SELECT location_id FROM hz_locations;
EXEC fnd_client_info.set_org_context(‘<orgid>’); for 11i
OR For R12
EXEC mo_global.init (‘AR’);
EXEC mo_global.set_org_context(<org_id>, NULL, ‘AR’);
EXEC fnd_global.set_nls_context(‘AMERICAN’);
EXEC mo_global.set_policy_context(‘S’, <org_id>);

Example For Running a Create Party Site API:

 Sample Output of the Create Party Site Script:

x_return_status = S
x_msg_count = 0
Party Site Id = 328654
Party Site Number = 26474
x_msg_data =

Script To Verify whether the Create Party Site API is successful:

 

 

Related posts:

TCA Create Location API

Create Location API

Name of the API : CREATE LOCATION API
Base Tables Affected : HZ_LOCATIONS
PL/SQL Procedure used : CREATE_LOCATION
Package Used : HZ_LOCATION_V2PUB

Mandatory Parameters :

Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_location_rec IN LOCATION_REC_TYPE
x_location_id OUT NUMBER
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Create Location API:

a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN createloc.sql)
b) Enter the values for the Parameters from inside the script file itself unless it is required to Enter some value from the SQL Prompt
c) Set the organization id before running the script as :
EXEC fnd_client_info.set_org_context(‘<orgid>’); for 11i
OR For R12
EXEC mo_global.init (‘AR’);
EXEC mo_global.set_org_context(<org_id>, NULL, ‘AR’);
EXEC fnd_global.set_nls_context(‘AMERICAN’);
EXEC mo_global.set_policy_context(‘S’, <org_id>);
d) Ensure that the Profile Option Default Country is Set to ‘<Country Name>’

Example For Running a Create Location API:

 

 Script To Verify whether the Create Location API is successful:

SELECT address1,
address2,
county ,
object_version_number
FROM hz_locations
WHERE location_id = 29266;

Related posts:

TCA Create Org Contact API

Create Org Contact API

Name of the API : CREATE ORG CONTACT
Base Tables Affected : HZ_ORG_CONTACTS and HZ_RELATIONSHIPS
PL/SQL Procedure used : CREATE_ORG_CONTACT
Package Used : HZ_PARTY_CONTACT_V2PUB

Mandatory Parameters :

Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_org_contact_rec IN ORG_CONTACT_REC_TYPE
x_org_contact_id OUT NUMBER
x_party_rel_id OUT NUMBER
x_party_id OUT NUMBER
x_party_number OUT VARCHAR2
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Create Organization Contact API:

a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN contactphone.sql)
b) To get the value for Department Parameter run the query:
SELECT DISTINCT department FROM hz_org_contacts;

To get the value for Job Title Parameter run the query :
SELECT DISTINCT job_title FROM hz_org_contacts;

To get the Decision Make Flag Parameter run the query :
SELECT DISTINCT decision_maker_flag FROM hz_org_contacts;

To get the Job Title Code Type Parameter run the query :
SELECT DISTINCT job_title_code FROM hz_org_contacts;

To get the Subject Id Parameter run the query:
SELECT subject_id FROM hz_relationships;

To get the Subject Type Parameter run the query:
SELECT DISTINCT subject_type FROM hz_relationships;

To get the Object Id Parameter run the query:
SELECT DISTINCT object_id FROM hz_relationships;

To get the Object Type Parameter run the query:
SELECT DISTINCT object_type FROM hz_relationships;

To get the Relationship Code Parameter run the query:
SELECT DISTINCT relationship_code FROM hz_relationships;

To get the Relationship Type Parameter run the query :
SELECT DISTINCT relationship_type FROM hz_relationships;
c) Set the organization id before running the script as :
EXEC fnd_client_info.set_org_context(‘<orgid>’); for 11i
OR For R12
EXEC mo_global.init (‘AR’);
EXEC mo_global.set_org_context(<org_id>, NULL, ‘AR’);
EXEC fnd_global.set_nls_context(‘AMERICAN’);
EXEC mo_global.set_policy_context(‘S’, <org_id>);

Example For Running a Create Org Contact API:

 Script To Verify whether the Org Contact Point API is successful:

 

Related posts: