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;
/