Category Archives: Oracle Apps

Create Customer Profile API

TCA Create Customer Profile API

Name of the API : CREATE CUSTOMER PROFILE
Base Tables Affected : HZ_CUSTOMER_PROFILES
PL/SQL Procedure used : CREATE_CUSTOMER_PROFILE
Package Used : HZ_CUSTOMER_PROFILE_V2PUB
Mandatory Parameters :
Parameter Name Parameter Type Data Type
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE
p _ customer _prof ile_rec IN CUSTOMER_ PROFI LE_ REC_ TYPE
p_create_profil e_amt IN VARCHAR2:= FND_API. G_TRUE
x_cust_account_profile_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 Profile API:

a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN create_customer_profile.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 Parameter cust_account_id from the SQL Prompt or from within API itself as a fixed value.
d) The Cust_Account_Id must exists in hz_cust_accounts and should not exists in hz_customer_profiles.

Example For Running Create Customer Profile API:

 Sample Output of the Create Customer Profile Script:

x_return_status = S
Cust Account Id = 4429
Cust Account Profile Id = 201877
Status =
Credit Checking =
Interest Charges =
x_msg_count = 0
x_msg_data =

Script To Verify whether the Create Customer Profile API is successful:

 

 

 

Related posts:

Create Customer Account Site Use API

TCA Create Customer Account Site Use API

Name of the API : CREATE CUSTOMER ACCOUNT SITE USE
Base Tables Affected : HZ_CUST_SITE_USES
PL/SQL Procedure used : CREATE_CUST_SITE_USE
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_site_use_rec IN CUST_SITE_USE_REC_TYPE
p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.
CUSTOMER_PROFILE_REC_TYPE
p_create_profile IN VARCHAR2:=FND_API.G_TRUE
p_create_profile_amt IN VARCHAR2:=FND_API.G_TRUE
x_site_use_id OUT NUMBER
x_return_status OUT VARCHAR2
x_msg_count OUT NUMBER
x_msg_data OUT VARCHAR2

Procedure:

Note: p_create_profile indicates whether to create customer profile for the site use being created. If value equals to FND_API.G_TRUE, a profile will be created. Similarly, 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. p_create_profile_amt is processed only when p_create_profile equals to FND_API.G_TRUE.

General Tips for Running the Create Customer Account Site Use API:

a) Save the API in a script file and then run the script from the SQL Prompt (Example : RUN custacctsiteuse.sql)
b) Enter the values for the Cust_Account_Site_Id , Site_Use and Location 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 Site Id run the query:
SELECT cust_acct_site_id FROM hz_cust_acct_sites;
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 a Create Customer Account Site Use API:

 Sample Output of the Create Customer Account Site Use Script:
x_return_status = S
x_msg_count = 0
Site Use Id = 15383
Site Use = INV
x_msg_data =

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

 

Related posts:

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: