Oracle API to Release Hold of Sale Order

Oracle API to Release Hold of Sales Order

 

 

Below is the script to release SO hold.

 

DECLARE
   l_order_tbl                                       oe_holds_pvt.order_tbl_type;
   x_return_status                                   VARCHAR2 (30);
   x_msg_data                                        VARCHAR2 (256);
   x_msg_count                                       NUMBER;
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
BEGIN
   fnd_global.apps_initialize (724780, 56852, 660 );   -- pass correct user_id, responsibility_id, and application_id
      MO_GLOBAL.SET_POLICY_CONTEXT ( 'S', 185 ) ; --Pass Proper org id
   --oe_debug_pub.initialize;
   --oe_debug_pub.setdebuglevel (5);
   oe_msg_pub.initialize;
      FOR c1 IN (select oola.header_id, oola.line_id, oha.order_hold_id
             from oe_order_holds_all  oha,
                  oe_order_lines_all oola
                 where oha.header_id = 25521028 --Pass Sales Order header id
                and oha.line_id = oola.line_id
                and oha.hold_release_id is null
                                                --and oola.line_id = 112224229
                                                )
LOOP
     
   l_order_tbl (1).header_id                                := c1.header_id;
      l_order_tbl (1).line_id                                  := c1.line_id;
   --oe_debug_pub.ADD ('Just before calling OE_Holds_PUB.Apply_Holds:');
   oe_holds_pub.release_holds (p_api_version                 => 1.0
                             , p_order_tbl                   => l_order_tbl
                             , p_hold_id                     => null
                             , p_release_reason_code         => 'NEXTLINX'
                             , p_release_comment             => 'Released by GTT Export Import Interface'
                             , 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 ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Hold Released Successfully ');
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;

   oe_debug_pub.debug_off;
     
END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error is ' || SQLCODE || '---' || SQLERRM);
END;