Lately I been tasked by our client to develop OAF page to manage Inventory Reservations from Delivery Details screen. Delivery Detail Id is passed as parameter to all regions displayed one OAF page.
I made a simple Form Personalization to have the custom page opened from Action Menu:
The only thing worth mentioning regarding the personalization is the syntax for Form Function submit:
Following OAF form displays various unique information to support reservations processing that is relevant only for this particular client:
Back to our topic: by manipulating values in Qty column on any line at Lots Available for Reservation table user would be able to Create, Update or Delete reservations of the corresponding Lot for Delivery Detail Line passed to this page as a parameter.
I decided to make this post for two reasons:
First: Most of the similar post tend to mention only Reservation creation from Inventory source and rarely from Order Management.
Second: It took me a while to have a CORRECT minimum number of the parameters in place to them running successfully. (none of the posts I encountered had it right!)
Bellow are the short scripts for every one of the main operations from INV_RESERVATION_PUB API.
* make sure to provide mtl_sales_orders.sales_order_id as demand_source_header_id to "glue" the reservation to corresponding Sales Order:
select mso.sales_order_id
into l_order_header_id
from mtl_sales_orders mso
,oe_order_headers_all ooha
,oe_transaction_types_tl ttl
where ooha.order_number = <corresponding sales order number>
and mso.segment1 = ooha.order_number
and mso.segment2 = ttl.name
and ttl.language = 'US'
and ooha.order_type_id = ttl.transaction_type_id;
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;
/
enjoy,
Felix
