Category Archives: Scripts

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:

TCA Create Group API

Create Group API

Name of the API : CREATE GROUP API

Base Tables Affected : HZ_PARTIES

PL/SQL Procedure used : CREATE_GROUP

Package Used : HZ_PARTY_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_group_rec IN GROUP_REC_TYPE
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 Group API
a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN create_party_group.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 mo_global.set_policy_context(‘S’, <org_id>);
d) Ensure that the Profile Option HZ : Generate Party Number is Set to ‘Yes’ at Site Level.

Code for Create Group API:

 Output of the Create Group Script:

x_return_status = S
x_msg_count = 0
Party Id = 546717
Party Number = 60417
Group Name = AOA Group
x_msg_data =

Script To Verify whether the Create Group API is successful:

 

Related posts:

TCA Create Person API

Create Person API

Name of the API : CREATE PERSON API
Base Tables Affected :

  • HZ_PARTIES
  • HZ_PERSON_PROFILES

PL/SQL Procedure used : CREATE_PERSON
Package Used : 

  • HZ_PARTY_V2PUB

Mandatory Parameters:

p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_person_rec IN PERSON_REC_TYPE
x_party_id OUT NUMBER,
x_party_number OUT VARCHAR2
x_prof i l e_i d OUT NUMBER
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Create Person API:

a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN createperson.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 mo_global.set_policy_context(‘S’, <org_id>);
d) Ensure that the Profile Option HZ : Generate Party Number is Set to ‘Yes’ at Site Level.

Code for Create Person API:

 Output of the Create Person Script:

x_return_status = S
x_msg_count = 0
Party Id = 546715
Party Number = 60415
Profile Id = 554833
x_msg_data =

Script To Verify whether the Create Person API is successful:

 

Related posts: