Apply Order Holds using OM API

This is an example of placing holds on selected list of orders.

CREATE OR REPLACE PROCEDURE skm_applyHolds
( 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;

  CURSOR c_orders IS
   select hdr.header_id
     from oe_order_headers_all hdr
    where hdr.order_number between p_order_num_low and p_order_num_high
  ;
  --
  --
  CURSOR c_holds IS
   SELECT hold_id
     FROM oe_hold_definitions
  ; 
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';
  init(p_user_name, 'ORDER_MGMT_SUPER_USER', l_msg_data);
  --
  --
  OE_MSG_PUB.initialize;
  --
  --
  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;
  --
  --
  FOR l_hold_rec IN c_holds 
  LOOP
     --
     -- Loop through each order and release hold
     --
     FOR l_orders in c_orders
     LOOP
        l_order_tbl(1).header_id := l_orders.header_id;
        OE_HOLDS_PUB.apply_holds
        ( p_api_version        => 6000 /* this parameter is of no significance */
        , p_init_msg_list      => FND_API.G_FALSE
        , p_commit             => FND_API.G_FALSE
        , p_validation_level   => FND_API.G_VALID_LEVEL_FULL /* this parameter is of no significance */
        , p_order_tbl          => l_order_tbl
        , p_hold_id            => l_hold_rec.hold_id
        , p_hold_until_date    => SYSDATE + 1
        , p_hold_comment       => 'Hold API test on ' || to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS') 
        , x_return_status      => l_return_status
        , x_msg_count          => l_msg_count
        , x_msg_data           => l_msg_data
        ) ;
        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 || ':');
     END LOOP;
  END LOOP;

  --
  -- Look for Any error messages
  --
  OE_MSG_PUB.count_and_get
  (p_encoded => 'T'
  , p_count  => l_msg_count
  , p_data   => l_msg_data
  ) ;
  dbms_output.put_line('Total Messages:' || l_msg_count);
  dbms_output.put_line('Error Message:' || l_msg_data);
  --
  --
  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;
  COMMIT;
END skm_applyHolds ;