Script to Update Customer Locations – HZ_LOCATION_V2PUB.UPDATE_LOCATION

By | October 22, 2015

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:

WOW! Did you like this post? We'll send more interesting posts like Script to Update Customer Locations – HZ_LOCATION_V2PUB.UPDATE_LOCATION to you!
Enter your Email Address: