Category Archives: Scripts

Create Customer Account Site API

TCA Create Customer Account Site API

Name of the API : CREATE CUSTOMER ACCOUNT SITE
Base Tables Affected : HZ_CUST_ACCT_SITES
PL/SQL Procedure used : CREATE_CUST_ACCT_SITE
Package Used : HZ_CUST_ACCOUNT_SITE_V2PUB
Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_cust_acct_site_rec IN CUST_ACCT_SITE_REC_TYPE
x_cust_acct_site_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 Customer Account Site API:
a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN createcustacctsite.sql)
b) Enter the values for the Cust_Account_Id and Party_Site_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 Customer Account Site is created.
c) To Select the Customer Account Id run the query:
SELECT cust_account_id FROM hz_cust_accounts;
d) To Select the Party Site Id run the Query :
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 Customer Account Site API:

 Sample Output of the Create Customer Account Site Script:

x_return_status = S
x_msg_count = 0
Customer Account Site Id is = 12743
x_msg_data =

Script To Verify whether the Create Customer Account Site API is successful:

 

Related posts:

Update Customer Account API

TCA Update Customer Account API

Name of the API : UPDATE CUSTOMER ACCOUNT
Base Tables Affected : HZ_CUST_ACCOUNTS
PL/SQL Procedure used : UPDATE_CUST_ACCOUNT
Package Used : HZ_CUST_ACCOUNT_V2PUB
Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_cust_account_rec IN CUST_ACCOUNT_REC_TYPE
p_object_version_number IN OUT NUMBER
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Update Customer Account API:

a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN updatecustacct.sql)
b) Enter the values for the Cust_Account_Id and Customer_Type 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 Customer Account is Updated.
c) To Select the Customer Account Id run the query:
SELECT cust_account_id FROM hz_cust_accounts;
d) 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 Update Customer Account Record API:

 Sample Output of the Update Customer Account Record Script:
x_return_status = S
x_msg_count = 0
Object Version Number =2
x_msg_data =

Script To Verify whether the Update Customer Account API is successful:

 

Related posts:

Create Customer Account API

TCA Create Customer Account API

Name of the API : CREATE CUSTOMER ACCOUNT API
Base Tables Affected : HZ_CUST_ACCOUNTS, HZ_CUSTOMER_PROFILES
PL/SQL Procedure used : CREATE_CUST_ACCOUNT
Package Used : HZ_CUST_ACCOUNT_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list         IN  VARCHAR2:= FND_API.G_FALSE
p_cust_account_rec      IN  CUST_ACCOUNT_REC_TYPE
p_person_rec            IN  HZ_PARTY_V2PUB.PERSON_REC_TYPE
p_customer_profile_rec  IN  HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
p_create_profile_amt    IN  VARCHAR2:= FND_API. G_TRUE
x_cust_account_id       OUT NUMBER
x_account_number        OUT VARCHAR2
x_party_id              OUT NUMBER
x_party_number          OUT VARCHAR2
x_profile_id            OUT NUMBER
x_return_status         OUT VARCHAR2
x_msg_count             OUT NUMBER
x_msg_data              OUT VARCHAR2

Procedure:

Note: p_create_profile_amt indicates whether to create profile amounts for the customer profile being created. If value equals to FND_API.G_TRUE, profile amounts will be created by copying over the profile amounts for the profile class on which this customer profile is based.

General Tips for Running the Create Customer Account API (Person):

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 Account Name, First Name and Last Name 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>);

Example For Running a Create Customer Account API (Person) For an Existing Party:

 Sample Output of the Create Customer Account Script (Person):

x_return_status = S
x_msg_count = 0
Party Id = 550707
Party Number = 60490
Profile Id =
x_msg_data =

Script To Verify whether Create Customer Account API (Person) is successful:

 Example For Running a Create Customer Account API (Person) For a New Party:

 Result:

x_return_status = S
x_msg_count = 0
Party Id = 554754
Party Number = 60594
Profile Id = 559833
x_msg_data =

Script To Verify whether Create Customer Account API (Person) is successful:

 

 

 

Related posts:

Create Class Category API

Create Class Category API TCA

Name of the API : CREATE CLASS CATEGORY
Base Tables Affected : HZ_CLASS_CATEGORIES
PL/SQL Procedure used : CREATE_CLASS_CATEGORY
Package Used : HZ_CLASSIFICATION_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_class_category_rec IN CLASS_CATEGORY_REC_TYPE
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

 General Tips for Running the Create Class Category API:
a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN create_class_category.sql)
b)  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>);
c) Enter the values for the Parameters :
Class_Category
Allow_Multi_Parent_Flag
Allow_Multi_Assign_Flag
Allow_Leaf_Node_Flag
from the SQL Prompt.
The Value of the Parameter Class_Category must belong to a Lookup
Code YES/NO.
The Value for Parameter Allow_Multi_Parent_Flag must belong to a
Lookup Code YES/NO.
The Value for Parameter Allow_Leaf_Node_Flag must belong to a
Lookup Code YES/NO.

Example For Running Create Class Category API:

 Sample Output of the Create Class Category Script:
x_return_status = S
x_msg_count = 0
Class Category = ACCOUNT_CLASSES
Allow Multi Parent Flag = Y
x_msg_data =

Script To Verify whether the Create Class Category API is successful:

 

Related posts:

Create Relationship API

TCA Create Relationship API

Name of the API : CREATE RELATIONSHIP
Base Tables Affected : HZ_RELATIONSHIPS
PL/SQL Procedure used : CREATE_RELATIONSHIP
Package Used : HZ_RELATIONSHIP_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p_relationship_rec IN RELATIONSHIP_REC_TYPE
x_relationship_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 Relationship API:
a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN createrelation.sql)
b) 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>);
c) Enter the Values of the Parameters :
Relationship_Type,
Relationship_Code,
Subject_Id,
Subject_Table_Name,
Subject_Type,
Object_Id,
Object_Table_Name,
Object_Type,
Start_Date,
Created_By_Moudle
either from inside the API or at the SQL Prompt.
i. The value for Parameter Relationship_Type should be one of the Values defined
for the Look Up Code PARTY_RELATIONS_TYPE .
ii. The Value for Parameter Relationship_Code should be one of the Values
for the Look Up Code PARTY_RELATIONS_TYPE .
iii. The Value for Parameter Subject_Id should be one of the Values
from the Column Object_Id in FND_OBJECT_INSTANCE_SETS .
iv. The Value for Parameter Subject_Table_Name should be one of the values
from the Column Obj_Name from table FND_OBJECTS.
v. The Value for Parameter Subject_Type should be one of the Values
from the Column INSTANCE_SET_NAME from the table
FND_OBJECT_INSTANCE_SETS .
vi. The Value for Parameter Object_Id should be one of the Values
from the Column Object_Id from table FND_OBJECTS.
vii. The Value for Parameter Object_Table_Name should be one of the Values
from the Column Obj_Name from table FND_OBJECTS.
viii. The Value for Parameter Object_Type should be one of the Values
from the Column INSTANCE_SET_NAME from the table
FND_OBJECT_INSTANCE_SETS .
ix. When user call the create relationship procedure, two new records will be
created in HZ_RELATIONSHIPS table.
x. Users are required to specify a subject id, an object id, a subject type, an object
type, a subject table name (where the subject belongs to, for example, HZ_PARTIES
for subject type of ‘PERSON’, ‘ORGANIZATION’), an object table name,
a relationship type, a relationship code, and start date to create a relationship.
xi. The relationship code must be either a forward or backward relationship code
defined in active records in the HZ_RELATIONSHIP_TYPES table for the
relationship type.
xii. The end date will be default to ’31-DEC-4712′ if not specified. The end date
must be greater than the start date.
xiii. For a given subject_id, object_id, relationship_code, there can be no overlap of
the start date and the end date.
xiv. The subject type and the object type will be used to do the foreign key check for
the subject id and the object id column. The subject id should be a valid object
instance defined in the object instance set which is specified as the subject type.
The object id should be a valid object instance defined in the object instance set
which is specified as the object type.
xv. The subject id and the object id cannot be the same value unless the self related
flag of the relationship type is set to yes.
xvi. It will check the “denormalized to party” flag in the HZ_RELATIONSHIP_
TYPES table to see if a denormalized party is necessary.
If the flag is set to yes, a party will be created for the relationship. Only the relationship
with both subject_table_name and object_table_name as ‘HZ_PARTIES’ can be
denormalized to HZ_PARTIES table.
xvii. A record will be created in the HZ_RELATIONSHIPS table with the relationship
code Entered as input parameter.
xviii. It will find the 2 nd relationship code from the HZ_RELATIONSHIP_TYPES
table, and use it to create a second relationship. The 2 nd relationship will have
the same relationship_id and party_id as the first relationship. The requested
relationship will be created with directional_code value ‘F’ (meaning forward)
and the reciprocal relationship will be created with directional_code value ‘B’
(meaning backward).

Example For Running Create Relationship API:

 Sample Output of the Create Relationship Script:
x_return_status = S
x_msg_count = 0
Relationship Id =430373
Party Id = 553708
Party Number = 60551
x_msg_data =

Script To Verify whether Create Relationship API is successful:

 

Related posts: