How to release Order Holds using API

Question: How to release Order Holds using API
   Answer:    Releasing Order Holds – API

If you have many orders on hold and want to use API to release all holds in one shot then user Hold API.

–==================== SQL Script Start =========

CREATE OR REPLACE PROCEDURE XXOST_ReleaseHolds(p_user_name         VARCHAR2,
                                               p_order_num_low     NUMBER,
                                               p_order_num_high    NUMBER
                                               )
IS

   –============================================================
   — Created By Ravindra Tripathi (OptioSys Technologies Inc.)
   — Created on 14-Apr-2011
   — Purpose: Releasing Order holds
   –============================================================
 
   CURSOR cur_orders_hold
   IS 
      SELECT 
               hdra.header_id
               ,hdra.order_number
               ,hsrc.hold_source_id
               ,hsrc.hold_id
        FROM   oe_order_headers_all hdra,
               oe_order_holds_all hlda,
               oe_hold_sources_all hsrc,
               oe_hold_definitions hdef
       WHERE   1=1
               AND hdra.order_number BETWEEN p_order_num_low AND p_order_num_high
               AND hdra.header_id = hlda.header_id
               AND hlda.hold_source_id = hsrc.hold_source_id
               AND hsrc.hold_id = hdef.hold_id
               AND hlda.released_flag=’N’;
   p_hold_source_rec    OE_HOLDS_PVT.hold_source_rec_type;
   p_hold_release_rec   OE_HOLDS_PVT.Hold_Release_Rec_Type;
   p_order_tbl          OE_HOLDS_PVT.order_tbl_type;
   idx                  NUMBER;
   x_return_status     VARCHAR2 (1);
   x_msg_count         NUMBER;
   x_msg_data          VARCHAR2 (2000);
  — Sub Program
   –Apps Initialize
   PROCEDURE apps_initialize (p_user_name          VARCHAR2,
                              p_resp_key           VARCHAR2,
                              px_err_msg    IN OUT VARCHAR2
                             )
   IS
 
      l_user_id   NUMBER;
      l_resp_id   NUMBER;
      l_appl_id   NUMBER;
    
   BEGIN
      px_err_msg := NULL;
      –Get User ID based upon User Name;
      SELECT   user_id
        INTO   l_user_id
        FROM   fnd_user
       WHERE   user_name = p_user_name;

        –Get User ID based upon User Name;
      SELECT   responsibility_id, application_id
        INTO   l_resp_id, l_appl_id
        FROM   fnd_responsibility
       WHERE   responsibility_key = p_resp_key;
      FND_GLOBAL.apps_initialize (l_user_id, l_resp_id, l_appl_id);
    
      DBMS_OUTPUT.put_line (‘User ID:’||l_user_id);
      DBMS_OUTPUT.put_line (‘Responsibility ID:’ || l_resp_id);
      DBMS_OUTPUT.put_line (‘Application ID:’||l_appl_id);
 
 
   exception
   when others then
   px_err_msg:=’ExpErr:apps_initialize:’||SQLERRM; 
   END;
 
    
BEGIN

   — Main Program  
   OE_MSG_PUB.initialize;
   apps_initialize(p_user_name, ‘ORDER_MGMT_SUPER_USER’, x_msg_data);

   IF x_msg_data IS NOT NULL
   THEN
      DBMS_OUTPUT.put_line (‘Init call failed. Error:’||x_msg_data);
      RETURN;
   END IF;

  x_msg_data:=null;
   — Loop through each order and unrealed holds
 
   FOR l_hold_rec IN cur_orders_hold
   LOOP
 
      p_order_tbl (1).header_id := l_hold_rec.header_id;

      oe_holds_pub.Release_Holds (
             p_api_version           => 1.0,
             p_order_tbl             => p_order_tbl,
             p_hold_id               => l_hold_rec.hold_id,
             p_release_reason_code   => ‘OTHER’,
             p_release_comment       => ‘Released Through Release Hold API Call’,
             x_return_status         => x_return_status,
             x_msg_count             => x_msg_count,
             x_msg_data              => x_msg_data
            );

       DBMS_OUTPUT.put_line (‘===== API Status for Order Number :’||l_hold_rec.order_number||’ =====’);
       DBMS_OUTPUT.put_line (‘Return Status = ‘ || x_return_status);
       DBMS_OUTPUT.put_line (‘Message Count = ‘ || x_msg_count);
       DBMS_OUTPUT.put_line (‘Message Data = :’ || x_msg_data || ‘:’);
   END LOOP;

   — Look for Any error messages from API
   FOR j IN 1 .. OE_MSG_PUB.count_msg
   LOOP
    
      OE_MSG_PUB.get (p_msg_index       => j,
                      p_encoded         => ‘F’,
                      p_data            => x_msg_data,
                      p_msg_index_out   => idx);
      DBMS_OUTPUT.put_line (‘Error: ‘ || j || ‘:’ || x_msg_data);
    
   END LOOP;
exception
when others then
x_return_status:=’E’;
x_msg_data :=’ExpErr:XXOST_ReleaseHolds. Error:’||sqlerrm;

DBMS_OUTPUT.put_line (‘ExpErr:XXOST_ReleaseHolds. Error:’||sqlerrm);
 
END XXOST_ReleaseHolds;

–==================== SQL Script End =========