Introduction
This Post illustrates the steps required to update the customer locations in Oracle EBS R12.
Below script for update customer locations
DECLARE
p_location_rec hz_location_v2pub.location_rec_type;
p_object_version_number NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_loc_update_cnt NUMBER := 0;
CURSOR location_update
IS
SELECT a.location_id location_id, a.address1 address1,
a.address2 address2, a.address3 address3,
a.postal_code postal_code, a.country country, a.city city,
b.object_version_number object_version_number
FROM apps.iron_es_location_update_stg a, apps.hz_locations b
WHERE a.location_id = b.location_id;
BEGIN
fnd_client_info.set_org_context (‘3106’);
FOR j IN location_update
LOOP
p_location_rec.location_id := j.location_id;
p_location_rec.address1 := j.address1;
p_location_rec.address2 := ‘ ‘;
p_location_rec.address3 := ‘ ‘;
p_location_rec.postal_code := TRIM (j.postal_code);
p_location_rec.country := j.country;
p_location_rec.city := j.city;
p_object_version_number := j.object_version_number;
hz_location_v2pub.update_location
(p_init_msg_list => fnd_api.g_true,
p_location_rec => p_location_rec,
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
);
–END;
COMMIT;
l_loc_update_cnt := l_loc_update_cnt + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘Location update Count ‘ || l_loc_update_cnt);
— DBMS_OUTPUT.put_line (‘Location update Count ‘ || j.address3);
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_data);
END;
What we expect in the script.
This script helps us to comprehend how to update the customer locations 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 correct records are being passed into APIs. Couple of APIs which is being used in the scripts are hz_location_v2pub.update_location etc.
Summary
This Post described the script to update the customer locations in Oracle EBS R12.
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.
