Releasing Order Holds

This is an example of removing order holds using OM API

CREATE OR REPLACE PROCEDURE skm_releaseHolds
( p_user_name          VARCHAR2
, p_order_num_low      NUMBER
, p_order_num_high     NUMBER
) IS
  l_order_tbl            OE_HOLDS_PVT.order_tbl_type;
  l_return_status        VARCHAR2(5);
  l_msg_count            NUMBER;
  l_msg_data             VARCHAR2(2000);
  i                      NUMBER;
  l_hold_source_rec      OE_HOLDS_PVT.hold_source_rec_type;
  l_hold_release_rec     OE_HOLDS_PVT.Hold_Release_Rec_Type;

  CURSOR c_orders_held IS
   select /*+ index(hld OE_ORDER_HOLDS_ALL_N1) index(hsrc OE_HOLD_SOURCES_U1)*/
          hdr.header_id
        , hsrc.hold_source_id
        , hsrc.hold_id
     from oe_order_headers_all hdr
        , oe_order_holds_all   hld
        , oe_hold_sources_all  hsrc
        , oe_hold_definitions  hdef
    where hdr.order_number between p_order_num_low and p_order_num_high
      and hdr.header_id      = hld.header_id
      and hld.hold_source_id = hsrc.hold_source_id
      and hsrc.hold_id       = hdef.hold_id
    ;
PROCEDURE get_user_id ( p_user_name VARCHAR2
                      , p_user_id   IN OUT NUMBER
                      , p_err_msg   IN OUT VARCHAR2
                      ) IS
BEGIN
  p_err_msg := null;
  SELECT user_id
    INTO p_user_id
    FROM fnd_user
   WHERE user_name = p_user_name;
  --
  --
  EXCEPTION
    WHEN OTHERS THEN
      p_user_id := -1;
      p_err_msg := 'procedure Get_user_Id error:'||sqlerrm;
END;
PROCEDURE get_resp_id ( p_resp_key            VARCHAR2
                      , p_resp_id      IN OUT NUMBER
                      , p_resp_appl_id IN OUT NUMBER
                      , p_err_msg      IN OUT VARCHAR2
                      ) IS
BEGIN
  SELECT responsibility_id
       , application_id
    INTO p_resp_id
       , p_resp_appl_id
    FROM fnd_responsibility
   WHERE responsibility_key = p_resp_key;
  --
  --
  EXCEPTION
    WHEN OTHERS THEN
      p_resp_id      := -1;
      p_resp_appl_id := -1;
      p_err_msg      := 'procedure Get_resp_Id error:'||sqlerrm;
END;
PROCEDURE init
( 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(p_user_name, l_user_id, px_err_msg);
  IF px_err_msg IS NOT NULL
  THEN
    RETURN;
  END IF;
  --
  get_resp_id(p_resp_key, l_resp_id, l_appl_id, px_err_msg);
  IF px_err_msg IS NOT NULL
  THEN
    RETURN;
  END IF;
  --
  --
  FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id);
  dbms_output.put_line(l_user_id || ','|| l_resp_id || ',' || l_appl_id);
END;
begin
  l_return_status  := 'S';
  OE_MSG_PUB.initialize;
  init(p_user_name, 'ORDER_MGMT_SUPER_USER', l_msg_data);
  --
  --
  IF l_msg_data IS NOT NULL
  THEN
    dbms_output.put_line('init call failed with the following message');
    dbms_output.put_line(l_msg_data);
    return;
  END IF;
  --
  -- Loop through each order and release hold
  --
  FOR l_orders_held in c_orders_held
  LOOP
     l_order_tbl(1).header_id := l_orders_held.header_id;
     oe_holds_pub.Release_Holds
     ( p_api_version          => 1.0
     , p_order_tbl            =>  l_order_tbl
     , p_hold_id              => l_orders_held.hold_id
     , p_release_reason_code  => 'EXPIRE'
     , p_release_comment      => 'Updated Through release hold API Call'
     , x_return_status        =>  l_return_status
     , x_msg_count            =>  l_msg_count
     , x_msg_data             =>  l_msg_data
     );
  END LOOP;
  dbms_output.put_line('Return Status = ' || l_return_status);
  dbms_output.put_line('Message Count = ' || l_msg_count);
  dbms_output.put_line('Message Data = :' || l_msg_data || ':');
  --
  -- Look for Any error messages
  --
  FOR j in 1..OE_MSG_PUB.count_msg
  LOOP
     OE_MSG_PUB.get
     ( p_msg_index     => j
     , p_encoded       => 'F'
     , p_data          => l_msg_data
     , p_msg_index_out => i
     );
     dbms_output.put_line('Error: ' || j || ':' || l_msg_data);
  END LOOP;
end skm_releaseHolds ;