How To Delete Reservations Using Standard API INV_RESERVATION_PUB.Delete_Reservation (Doc ID 2219367.1)

Solution

Summary: The reservation API INV_RESERVATION_PUB.Delete_Reservation will delete reservations accepting the reservation id and optionally serial numbers to locate and remove reservations. Care should be taken to ensure related objects like sales orders no longer need the reservation to avoid errors like Negative balances when attempting to ship material. You might also use the reservation API that relieves reservations instead -- INV_RESERVATION_PUB.Relieve_Reservation API.

Details: Here is an example of using the delete reservation API. The example includes values for the inventory application id (401), an example Manufacturing and Distribution Manager responsibility id, and an example MFG user id. Change the values to the appropriate ids on your environment. The SQL also prompts for the organization id and item number. In this example, the code loops through reservations on this item removing them but rownum is limited to 1 so only the first row is removed.

--set serveroutput on
DECLARE
  p_rsv      apps.inv_reservation_global.mtl_reservation_rec_type;
  p_dummy_sn apps.inv_reservation_global.serial_number_tbl_type;

  l_msg_count  NUMBER;
  l_msg_data   VARCHAR2(240);
  l_status     VARCHAR2(1);
  l_index_out  NUMBER;
  v_reserv_id  NUMBER;
  l_err_status VARCHAR2(50);
  l_err_mesg   VARCHAR2(2000);

  -- User Variables: Update for your environment ~~!
  l_resp_appl_id NUMBER := 401; --FND_PROFILE.VALUE ('RESP_APPL_ID');
  l_resp_id      NUMBER := 56229; --FND_PROFILE.VALUE ('RESP_ID');
  l_user_id      NUMBER := 1068; --FND_PROFILE.VALUE ('USER_ID');

  -- Item/Organization Variables
  l_organization_id NUMBER := &yourorgid;
  l_youritem        VARCHAR2(100) := '&YourPartNumber';

  -- Find 1 reservation for your item
  CURSOR c_reserve IS
    SELECT DISTINCT reservation_id
      FROM apps.mtl_reservations_all_v
     WHERE inventory_item_id IN (SELECT inventory_item_id
                                   FROM apps.mtl_system_items_b
                                  WHERE organization_id = l_organization_id
                                        AND segment1 = l_youritem)
           AND organization_id = l_organization_id --p_organization_id;
           AND rownum < 2;

BEGIN
  fnd_global.apps_initialize(user_id      => l_user_id
                            ,resp_id      => l_resp_id
                            ,resp_appl_id => l_resp_appl_id);

  FOR r_reserve IN c_reserve
  LOOP
  
    dbms_output.put_line('Reservation ID : ' || r_reserve.reservation_id);
  
    p_rsv.reservation_id := r_reserve.reservation_id;
  
    apps.inv_reservation_pub.delete_reservation(p_api_version_number => 1.0
                                               ,p_init_msg_lst       => fnd_api.g_false
                                               ,p_rsv_rec            => p_rsv
                                               ,p_serial_number      => p_dummy_sn
                                               ,
                                                -- p_validation_flag => fnd_api.g_true,
                                                x_return_status => l_status
                                               ,x_msg_count     => l_msg_count
                                               ,x_msg_data      => l_msg_data);
  
    dbms_output.put_line('Reservation API : ' || l_status);
  
    IF l_status <> 'S' THEN
      fnd_msg_pub.get(p_msg_index     => l_msg_count
                     ,p_data          => l_msg_data
                     ,p_encoded       => 'F'
                     ,p_msg_index_out => l_index_out);
    
      l_err_status := 'E';
      l_err_mesg   := 'Delete Allocations API failed ' || rtrim(l_msg_data);
      dbms_output.put_line('API failed ' || l_err_mesg);
    
    ELSE
      l_err_status := 'S';
      l_err_mesg   := NULL;
      dbms_output.put_line('API success ' || l_err_mesg);
    END IF;
  
  END LOOP;

END;
/