oracle ebs 删除用户,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;

/