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 := 20634; --FND_PROFILE.VALUE ('RESP_ID');
l_user_id NUMBER := -1; --26406 --FND_PROFILE.VALUE ('USER_ID');
-- Item/Organization Variables
-- l_organization_id NUMBER := 2177;
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 (
SELECT
xx.reservation_id,
xx.segment1 item_code,
xx.inventory_item_id,
xx.organization_id,
xx.locator_id
FROM
(
SELECT
msib.segment1,
mp.organization_code,
msib.organization_id,
msib.inventory_item_id,
msib.purchasing_item_flag,
msib.shippable_item_flag,
msib.inventory_item_flag,
lot_control_code,
decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',
5,
'At Receipt',
6,
'At Issue') "Serial Control",
mc.segment1 "PPL",
mc.segment2 "SPL",
mc.segment3 "ITEM CATEGORY",
mr.reservation_quantity reserved_qty,
mr.reservation_id,
mr.locator_id
FROM
mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories mc,
mtl_parameters mp,
mtl_reservations mr
WHERE
msib.inventory_item_id = mic.inventory_item_id
AND msib.organization_id = mic.organization_id
AND msib.organization_id = mp.organization_id
AND mic.category_id = mc.category_id
AND mic.category_set_id = 1
AND mc.structure_id = 101
AND msib.enabled_flag = 'Y'
AND msib.lot_control_code = 1
-- AND msib.segment1='NTTC90ABE6'
AND nvl(msib.end_date_active, sysdate) >= sysdate
AND upper(mc.segment1) IN ( 'JUNIPER', 'CIENA', 'UTAHSCI',
'WESTELL',
'ADTRAN',
'NOKIA',
'ALCATEL',
'ALCATEL-LUCENT',
'TELLABS',
'FUJI',
'DELTA ELECTRONICS',
'CORNING' )
AND mc.disable_date IS NULL
AND msib.organization_id IN ( 85, 97 )
AND mr.inventory_item_id = msib.inventory_item_id
AND mr.organization_id = msib.organization_id
ORDER BY
mc.segment1
) xx
WHERE
reserved_qty <> 0
) 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;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('p_msg_cnt: ');
END;
No comments:
Post a Comment