1. CREATE_RESERVATION
DECLARE
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
BEGIN
fnd_global.apps_initialize(2116,50641,660);
p_rsv.requirement_date := Sysdate;
p_rsv.organization_id := 101; --mtl_parameters.organization id
p_rsv.inventory_item_id := 258;--mtl_system_items.Inventory_item_id;
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe; -- which is 2
p_rsv.demand_source_name := NULL;
p_rsv.demand_source_header_id := l_order_header_id;--1334166 ; --mtl_sales_orders.sales_order_id
p_rsv.demand_source_line_id := 48067;--4912468 ; -- oe_order_lines.line_id
p_rsv.primary_uom_code := 'SLB';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'SLB';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := 1;
p_rsv.primary_reservation_quantity := 1;
p_rsv.lot_number := '1187341A';--p_lot_number;
p_rsv.locator_id := null;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_inv;
p_rsv.ship_ready_flag := NULL;
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_id := NULL;
p_rsv.subinventory_code := '102';
p_rsv.subinventory_id := NULL;
p_rsv.attribute15 := NULL ;
p_rsv.attribute14 := NULL ;
p_rsv.attribute13 := NULL ;
p_rsv.attribute12 := NULL ;
p_rsv.attribute11 := NULL ;
p_rsv.attribute10 := NULL ;
p_rsv.attribute9 := NULL ;
p_rsv.attribute8 := NULL ;
p_rsv.attribute7 := NULL ;
p_rsv.attribute6 := NULL ;
p_rsv.attribute5 := NULL ;
p_rsv.attribute4 := NULL ;
p_rsv.attribute3 := NULL ;
p_rsv.attribute2 := NULL ;
p_rsv.attribute1 := NULL ;
p_rsv.attribute_category := NULL ;
p_rsv.lpn_id := NULL ;
p_rsv.pick_slip_number := NULL ;
p_rsv.lot_number_id := NULL ;
p_rsv.revision := NULL ;
p_rsv.external_source_line_id := NULL ;
p_rsv.external_source_code := NULL ;
p_rsv.autodetail_group_id := NULL ;
p_rsv.reservation_uom_id := NULL ;
p_rsv.primary_uom_id := NULL ;
p_rsv.demand_source_delivery := NULL ;
p_rsv.supply_source_line_detail := NULL;
p_rsv.supply_source_name := NULL;
p_rsv.supply_source_header_id := NULL;
p_rsv.supply_source_line_id := NULL;
inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);
dbms_output.put_line('Return status = '||x_status);
dbms_output.put_line('msg count = '||to_char(x_msg_count));
dbms_output.put_line('msg data = '||x_msg_data);
dbms_output.put_line('Quantity reserved = '||to_char(x_qty));
dbms_output.put_line('Reservation id = '||to_char(x_rsv_id));
IF x_msg_count >=1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
END LOOP;
END IF;
COMMIT;
END;
/
2. UPDATE_RESERVATION
DECLARE
l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_rsv_id NUMBER;
l_dummy_sn inv_reservation_global.serial_number_tbl_type;
l_status VARCHAR2(1);
l_quantity_reserved NUMBER;
BEGIN
fnd_global.apps_initialize(2116,50641,660);
-- could be "located" by order_header_id, order_line_id, item and warehouse - in that case several reservations could be updated
l_rsv_old.reservation_id := 69956;
-- specify the new values
l_rsv_new.reservation_id := 69956;
l_rsv_new.primary_reservation_quantity := 10;
l_rsv_new.requirement_date := Sysdate+2;
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_old
, p_to_rsv_rec => l_rsv_new
, p_original_serial_number => l_dummy_sn -- no serial contorl
, p_to_serial_number => l_dummy_sn -- no serial control
, p_validation_flag => fnd_api.g_true
, p_check_availability => fnd_api.g_false
, p_over_reservation_flag => 0
);
IF l_status = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('reservation updated');
ELSE
IF l_msg_count >=1 THEN
FOR I IN 1..l_msg_count LOOP
dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
--fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
END LOOP;
END IF;
END IF;
COMMIT;
END;
/
3. DELETE_RESERVATION (decided not to use relieve_reservation since secondary quantity is not used )
DECLARE
l_rsv inv_reservation_global.mtl_reservation_rec_type;
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_rsv_id NUMBER;
l_dummy_sn inv_reservation_global.serial_number_tbl_type;
l_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(2116,50641,660);
l_rsv.reservation_id := 95377;
/*l_rsv.organization_id := 101;
l_rsv.inventory_item_id := 258;
l_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe; -- order entry
l_rsv.demand_source_header_id := 76069; --oe_order_headers.header_id
l_rsv.demand_source_line_id := 48067; --oe_order_lines.line_id
l_rsv.supply_source_type_id := inv_reservation_global.g_source_type_inv;
l_rsv.subinventory_code := '102';*/
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv
, p_serial_number => l_dummy_sn
);
IF l_status = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('reservation deleted');
ELSE
IF l_msg_count >=1 THEN
FOR I IN 1..l_msg_count LOOP
dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
--fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
END LOOP;
END IF;
END IF;
COMMIT;
END;
/