API to Apply the Hold on Order

This script can be used on Version 12.0 and later

DECLARE
   l_file                VARCHAR2 (100);
   l_data                VARCHAR2 (2000);
   l_hold_source_rec     oe_holds_pvt.hold_source_rec_type;
   l_org                 NUMBER := 204;
   l_file_val            VARCHAR2 (100);
   l_error_message       VARCHAR2 (2000);
   l_msg_index_out       NUMBER (10);
   x_return_status       VARCHAR2 (2000);
   x_msg_count           NUMBER;
   x_msg_data            VARCHAR2 (2000);
   x_debug_file          VARCHAR2 (100);
BEGIN
-- Source the Environment variables
   fnd_global.apps_initialize (
                          user_id  => '<user_id>'
                        , resp_id   => '<resp_id>'
                        , resp_appl_id => '<resp_appl_id>'
                        , security_group_id => '<security_group_id>'
                              );
   DBMS_OUTPUT.ENABLE (1000000);
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel (5);
   oe_debug_pub.debug_on;
   l_file_val   := oe_debug_pub.set_debug_mode ('FILE');
   DBMS_OUTPUT.put_line ('.');
   DBMS_OUTPUT.put_line (' Log File Name and Location :- ' ||                 oe_debug_pub.g_dir || '/' || oe_debug_pub.g_file);
   DBMS_OUTPUT.put_line ('.');
   mo_global.set_policy_context ('S', l_org);
   l_hold_source_rec   := oe_holds_pvt.g_miss_hold_source_rec;
   l_hold_source_rec.hold_id := '<hold_id>';
   l_hold_source_rec.hold_entity_code := 'O';   -- order level hold
   l_hold_source_rec.hold_entity_id:= '<header_id of the order>';
   l_hold_source_rec.header_id := '<header_id of the order>';
   x_return_status   := NULL;
   x_msg_data        := NULL;
   x_msg_count        := NULL;
   oe_debug_pub.ADD ('Just Before calling OE_Holds_PUB.Apply_Holds:');
   oe_holds_pub.apply_holds (p_api_version  => 1.0
                           , p_init_msg_list=> fnd_api.g_true
                           , p_commit=> fnd_api.g_true
                           , p_hold_source_rec => l_hold_source_rec
                           , x_return_status => x_return_status
                           , x_msg_count => x_msg_count
                           , x_msg_data  => x_msg_data
                            );
   oe_debug_pub.ADD ('Just After calling OE_Holds_PUB.Apply_Holds:');

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index  => i
                             , p_encoded   => fnd_api.g_false
                             , p_data      => x_msg_data
                             , p_msg_index_out => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message  := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=                                                   l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('Hold Applied Successfully ');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error is ' || SQLCODE || '---' || SQLERRM);
END;