Apply Hold in Order Management (OM) Oe_Holds_Pub.Apply_Holds

API to Apply Holds in Oracle Order Management:

CREATE OR REPLACE PROCEDURE APPS.Xx_Om_Apply_SO_HOLD(p_trip_number in varchar2,p_err_msg out varchar2) IS

lv_order_rec   Oe_Holds_Pvt.order_rec_type;

lv_order_tbl   Oe_Holds_Pvt.order_tbl_type;

lv_return_status VARCHAR2(10);

ln_msg_count     NUMBER;

lv_msg_data      VARCHAR2(200);

ln_line_no       number  := 0;

lv_comment       varchar2(100);

 l_msg_index_out number;

ln_hold_id       number ;

lv_hold_name     varchar2(50) := fnd_profile.value('XXXX_VEHICLE_VOLUME_HOLD');

cursor cur_hold

is

 SELECT DISTINCT OOH.header_id

 FROM XX_OM_MTRIP_LINES_V XXL,

      oe_order_headers_v  OOH

 WHERE XXL.trip_number = p_trip_number

 AND XXL.put_on_hold = 'Y'

 AND XXL.SALES_ORDER = OOH.Order_number         

 MINUS

 SELECT Header_id

 FROM OE_HOLDS_HISTORY_V

 WHERE Hold_name  = lv_hold_name

 AND Released_flag = 'N'

 order by 1; 

BEGIN

  FOR I in cur_hold

  loop

    ln_line_no := NVL(ln_line_no,0) + 1;

    lv_order_rec.header_id := I.Header_Id ;

    lv_order_rec.line_id   :=  NULL;

    lv_order_tbl(ln_line_no) := lv_order_rec ;

  end loop;

  begin

    select hold_id

    into ln_hold_id

    from OE_HOLD_DEFINITIONS

    where upper(name) = upper(lv_hold_name);

  exception

    when no_data_found then

      p_err_msg := 'Hold name is not defined';

    when others then

     p_err_msg := substr(sqlerrm,1,80);

  end;

   lv_comment :=  'Hold applied through API - Trip Number :'||p_trip_number;

  Oe_Holds_Pub.Apply_Holds (

       p_api_version => 1.0,

       p_init_msg_list => Fnd_Api.G_FALSE,

       p_commit => Fnd_Api.G_False,

       p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,

       p_order_tbl => lv_order_tbl ,

       p_hold_id => ln_hold_id,--C_HOLD_FOR.HOLD_ID,

       p_hold_until_date => null,

       p_hold_comment => lv_comment,

       x_return_status => lv_return_status,

       x_msg_count => ln_msg_count,

       x_msg_data => lv_msg_data);

   IF NVL(ln_msg_count,0) > 0 THEN

    FOR j IN 1..ln_msg_count

     LOOP

             Fnd_Msg_Pub.get(p_msg_index           => j,

                                                    p_encoded   => 'F',

                                                    p_data                      => lv_msg_data,

                                                    p_msg_index_out => l_msg_index_out);

     END LOOP;            

   END IF; 

   IF NVL(lv_return_status,'X') = 'S' THEN     

   COMMIT;

   END IF;        

   END;