Script to Update Customer Locations – HZ_LOCATION_V2PUB.UPDATE_LOCATION

/********************************************************************************
*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;
/