Oracle EBS INV 删除保留

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