Introduction
This Post illustrates the steps required to update customer Profile in Oracle EBS R12.
Script to update customer Profile in Oracle Applications
DECLARE
p_customer_profile_rec_type hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_account_profile_id NUMBER;
p_object_version_number NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
g_org_id NUMBER := fnd_profile.VALUE (‘ORG_ID’);
g_user_name VARCHAR2 (200)
:= fnd_profile.VALUE (‘USERNAME’);
g_resp_name VARCHAR2 (200)
:= fnd_profile.VALUE (‘RESP_NAME’);
CURSOR c_data_rtvl ——–+=data retrival cursor
IS
SELECT hcp.object_version_number obj_ver_no,
hcp.cust_account_profile_id account_profile
FROM hz_customer_profiles hcp, hz_parties hp, hz_cust_accounts hca
WHERE hcp.attribute5 IS NULL
AND hca.party_id = hp.party_id
AND hca.cust_account_id = hcp.cust_account_id
AND hcp.attribute_category LIKE ‘ALL_CAT’
AND hca.account_number LIKE ‘C0001%’;
CURSOR c_cntx_vlu ——–+=context value cursor
IS
SELECT afu.user_id user_id, aft.application_id application_id,
aft.responsibility_id resp_id
FROM applsys.fnd_user afu, applsys.fnd_responsibility_tl aft
WHERE afu.user_name LIKE g_user_name
AND aft.responsibility_name LIKE g_resp_name;
cdr_rec c_data_rtvl%ROWTYPE;
ccv_rec c_cntx_vlu%ROWTYPE;
BEGIN
— Setting the Context —
OPEN c_cntx_vlu;
FETCH c_cntx_vlu
INTO ccv_rec;
mo_global.init (‘AR’);
fnd_global.apps_initialize (user_id => ccv_rec.user_id,
resp_id => ccv_rec.application_id,
resp_appl_id => ccv_rec.resp_id
);
mo_global.set_policy_context (‘S’, g_org_id);
fnd_global.set_nls_context (‘AMERICAN’);
— Initializing the Mandatory API parameters
FOR cdr_rec IN c_data_rtvl
LOOP
p_customer_profile_rec_type.cust_account_profile_id :=
cdr_rec.account_profile;
–first value
p_customer_profile_rec_type.credit_rating := NULL;
p_customer_profile_rec_type.attribute5 := ‘S’;
p_object_version_number := cdr_rec.obj_ver_no;
–always change this also
DBMS_OUTPUT.put_line
(‘Calling the API hz_customer_profile_v2pub.update_customer_profile’);
hz_customer_profile_v2pub.update_customer_profile
(p_init_msg_list => fnd_api.g_true,
p_customer_profile_rec => p_customer_profile_rec_type,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
–COMMIT;
DBMS_OUTPUT.put_line (‘Updation of Customer Profile is Successful ‘);
DBMS_OUTPUT.put_line (‘Output information ….’);
DBMS_OUTPUT.put_line ( ‘Object Version Number = ‘
|| TO_CHAR (p_object_version_number)
);
DBMS_OUTPUT.put_line ( ‘CDE STMT DIST = ‘
|| p_customer_profile_rec_type.attribute5
);
ELSE
DBMS_OUTPUT.put_line ( ‘Updation of Customer Profile got failed:’
|| x_msg_data
);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data :=
fnd_msg_pub.get (p_msg_index => i,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (i || ‘) ‘ || x_msg_data);
END LOOP;
END IF;
EXIT WHEN c_data_rtvl%NOTFOUND; ———–look up
END LOOP;
DBMS_OUTPUT.put_line (‘Completion of API’);
END;
What is Expect in This Script?
This script helps us to comprehend how to update customer Profile into oracle through API. It also demonstrates the required validation taking place, and additional validation can be incorporated anytime in your package based on business requirements. Post validation valid records are being passed into APIs. Couple of APIs which is being used in the scripts are hz_customer_profile_v2pub.update_customer_profile etc.
Summary
This Post described the script update customer Profile in Oracle EBS 12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
