We can use standard API HZ_LOCATION_V2PUB.UPDATE_LOCATION to update the existing customer address stored in the HZ_LOCATIONS table.
/********************************************************************************
*PURPOSE: To Customer Locations information from back-end *
*AUTHOR: Shailender Thallam *
*********************************************************************************/
SET SERVEROUTPUT ON;
DECLARE
--
--Cursor to fetch location details
--
cursor cur_loc
is
select location_id,
object_version_number,
country,
state,
province
from hz_locations
where 1 = 1
and trim(province) = trim(state)
and country = 'CA'
; -- Modify this query as per your requirements
--
p_location_rec hz_location_v2pub.location_rec_type;
p_object_version_number NUMBER;
--
l_user_id NUMBER;
l_responsibility_id NUMBER;
l_application_id NUMBER;
--
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
--
-- Setting the Context
--
mo_global.init ('AR');
--
--Finding user_id, responsibility_id and application_id
--
SELECT fu.user_id, frt.responsibility_id, frt.application_id
INTO l_user_id, l_responsibility_id, l_application_id
FROM fnd_user fu,
fnd_user_resp_groups_direct furgd,
fnd_responsibility_tl frt
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frt.responsibility_id
AND UPPER (fu.user_name) = UPPER ('CONVUSER')
AND UPPER (frt.responsibility_name) = UPPER ('Receivables Manager');
--
--Apps Initialize
--
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_responsibility_id,
resp_appl_id => l_application_id
);
--
--Setting Org Context
--
mo_global.set_policy_context ('S', 81);
--
FOR rec in cur_loc
LOOP
BEGIN
--
-- Initializing the API parameters
--
p_location_rec.location_id := rec.location_id;
p_location_rec.state := fnd_api.g_miss_char; --making state as NULL
p_object_version_number := rec.object_version_number;
--
--You can add any column from hz_location_v2pub.location_rec_type to update them
--
DBMS_OUTPUT.put_line ('Calling the API for location_id: '||rec.location_id);
--
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
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Updation of Location failed: ' || x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');
DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
END LOOP;
END IF;
EXCEPTION WHEN OTHERS THEN
--
DBMS_OUTPUT.put_line ('Un handled exception for location_id: '||rec.location_id||' - Error: '||substr(SQLERRM,1,250));
--
END;
END LOOP;
DBMS_OUTPUT.put_line ('Completion of API');
END;
/
After running this API you may see below error messages based on the setup on your instance:
“Update of this column is not allowed because Printed, Posted, or Applied Transaction(s) exists for one of the sites for this address.”
To overcome this error we need to change System Options setup ‘Allow Change to Printed Invoices‘ to ‘Yes’
Process to change the setup:
Responsibility: Receivables Manager
Navigation: Setup > System > System Options
Navigate to the Trans and Customers tab
Check the Allow Change to Printed Invoices checkbox
Below is the screen shot after changing the setup:
