[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 1. Undetailed reservations are deleted.
* 2. Detailed reservations are delinked from the allocations.
* 3. Allocation is deleted for a WMS Organization.
* 4. Move Order Line is updated.
* For more info... see the documentation given in the Spec.
*/
PROCEDURE cancel_move_order_line
( x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_line_id IN NUMBER
, p_delete_reservations IN VARCHAR2
, p_txn_source_line_id IN NUMBER DEFAULT NULL
, p_delete_alloc IN VARCHAR2 DEFAULT NULL -- ER3969328: CI project
, p_delivery_detail_id IN NUMBER DEFAULT NULL -- Planned Crossdocking project
) IS
l_line_status NUMBER;
l_quantity_to_delete NUMBER;
l_deleted_quantity NUMBER;
l_max_delete_quantity NUMBER;
l_delete_reservations VARCHAR2(1);
l_update_rec inv_reservation_global.mtl_reservation_rec_type;
l_sec_quantity_to_delete NUMBER; --INVCONV
l_max_delete_sec_quantity NUMBER; --INVCONV
l_sec_deleted_quantity NUMBER; --INVCONV
SELECT quantity
, NVL(quantity_detailed, 0)
, secondary_quantity
, NVL(secondary_quantity_detailed,0)
, organization_id
, txn_source_line_id
FROM mtl_txn_request_lines
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE mr.demand_source_type_id IN (2, 8)
AND nvl(mr.staged_flag,'N') <> 'Y'
AND demand_source_line_detail IS NULL
AND mr.demand_source_line_id = l_txn_source_line_id
AND mr.primary_reservation_quantity > NVL(mr.detailed_quantity, 0);
SELECT mmtt.transaction_temp_id
, ABS(mmtt.primary_quantity)
, ABS(mmtt.secondary_transaction_quantity) --INVCONV
, mmtt.reservation_id
, mmtt.parent_line_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_line_id
AND NOT EXISTS (SELECT 1 FROM mtl_material_transactions_temp t
WHERE t.parent_line_id = mmtt.transaction_temp_id)
ORDER BY mmtt.transaction_quantity ASC
FOR UPDATE NOWAIT;
SELECT status
FROM wms_dispatched_tasks
WHERE (l_parent_line_id IS NULL AND transaction_temp_id = l_transaction_temp_id)
OR (l_parent_line_id IS NOT NULL AND transaction_temp_id = l_parent_line_id);
SELECT sum(primary_reservation_quantity),
sum(secondary_reservation_quantity)
FROM mtl_reservations
WHERE reservation_id IN (SELECT DISTINCT reservation_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_line_id);
SELECT sum(requested_quantity), sum(requested_quantity2)--INVCONV
FROM wsh_delivery_details
WHERE move_order_line_id = p_line_id
AND released_status ='S';
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_line_id
AND nvl(mmtt.transaction_status,1) = 2
FOR UPDATE NOWAIT;
SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE mr.demand_source_line_detail = p_wdd_id
AND mr.demand_source_type_id IN (2,8)
AND NVL(mr.crossdock_flag,'N') = 'Y'
FOR UPDATE NOWAIT;
|| ', p_delete_reservations: ' || p_delete_reservations
|| ', p_txn_source_line_id: ' || to_char(p_txn_source_line_id)
|| ', p_delete_alloc: ' || p_delete_alloc
|| ', p_delivery_detail_id: ' || to_char(p_delivery_detail_id)
,'CANCEL_MOVE_ORDER_LINE');
l_deleted_quantity := 0;
l_sec_deleted_quantity := 0;
SELECT NVL(mtrh.move_order_type,0)
INTO l_move_order_type
FROM mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mtrl.line_id = p_line_id
AND mtrh.header_id = mtrl.header_id;
IF p_delete_reservations IS NULL OR p_delete_reservations <> 'Y'
THEN
l_delete_reservations := 'N';
l_delete_reservations := 'Y';
DEBUG('Delete Reservations (Y/N) = '
|| l_delete_reservations, 'CANCEL_MOVE_ORDER_LINE');
IF l_delete_reservations = 'Y' AND l_quantity > l_quantity_detailed
THEN
-- {
l_quantity_to_delete := l_quantity - l_quantity_detailed;
l_sec_quantity_to_delete := l_sec_quantity - l_sec_quantity_detailed; --INVCONV
|| l_quantity_to_delete, 'Cancel_Move_Order_Line');
|| l_sec_quantity_to_delete, 'Cancel_Move_Order_Line');
EXIT WHEN l_quantity_to_delete <= 0;
l_update_rec := l_rsv_tbl(1);
l_update_rec.reservation_quantity := NULL;
l_max_delete_quantity
:= l_update_rec.primary_reservation_quantity
- l_update_rec.detailed_quantity;
l_max_delete_sec_quantity
:= l_update_rec.secondary_reservation_quantity
- l_update_rec.secondary_detailed_quantity; --INVCONV
IF l_max_delete_quantity > l_quantity_to_delete
THEN
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity - l_quantity_to_delete;
l_quantity_to_delete := 0;
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity
- l_sec_quantity_to_delete; --INVCONV
l_sec_quantity_to_delete := 0; --INVCONV
l_quantity_to_delete
:= l_quantity_to_delete - l_max_delete_quantity;
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity - l_max_delete_quantity;
l_sec_quantity_to_delete
:= l_quantity_to_delete - l_max_delete_quantity; --INVCONV
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity
- l_max_delete_sec_quantity; --INVCONV
|| l_update_rec.primary_reservation_quantity
,'CANCEL_MOVE_ORDER_LINE');
|| l_update_rec.secondary_reservation_quantity
,'CANCEL_MOVE_ORDER_LINE');
IF (l_update_rec.secondary_uom_code IS NULL)
THEN
l_update_rec.secondary_reservation_quantity := NULL;
l_update_rec.secondary_detailed_quantity := NULL;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv_tbl(1)
, p_to_rsv_rec => l_update_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
DEBUG('update_reservation returned success'
,'CANCEL_MOVE_ORDER_LINE');
DEBUG('Error: Update Reservations return status: '
|| l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
||' requested quantity then need to delete'
,'CANCEL_MOVE_ORDER_LINE');
DEBUG('The extra reserved quantity that needs to be deleted'
|| l_extra_rsv_quantity,'CANCEL_MOVE_ORDER_LINE');
DEBUG('The extra reserved quantity2 that needs to be deleted'
|| l_extra_rsv_quantity2,'CANCEL_MOVE_ORDER_LINE');
l_update_rec := l_rsv_tbl(1);
IF l_update_rec.detailed_quantity > l_primary_quantity
THEN
l_update_rec.detailed_quantity
:= l_update_rec.detailed_quantity - l_primary_quantity;
l_update_rec.secondary_detailed_quantity
:= l_update_rec.secondary_detailed_quantity - l_secondary_quantity;
l_update_rec.detailed_quantity := 0;
l_update_rec.secondary_detailed_quantity := 0;
DEBUG('New Detailed Qty = ' || l_update_rec.detailed_quantity
,'CANCEL_MOVE_ORDER_LINE');
DEBUG('New Detailed Qty1 = ' || l_update_rec.secondary_detailed_quantity
,'CANCEL_MOVE_ORDER_LINE');
IF l_delete_reservations = 'Y' OR l_extra_rsv_quantity > 0
THEN
-- {
l_update_rec.reservation_quantity := NULL;
l_update_rec.secondary_reservation_quantity := NULL;
IF l_update_rec.primary_reservation_quantity > l_primary_quantity
THEN
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity - l_primary_quantity;
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity - l_secondary_quantity;
ELSIF (l_delete_reservations ='N'
AND
(l_update_rec.primary_reservation_quantity >= l_extra_rsv_quantity))
THEN
IF (l_debug=1) THEN
DEBUG('need to reduce reservation quantity only for the extra qty'
|| l_extra_rsv_quantity,'CANCEL_MOVE_ORDER_LINE');
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity - l_extra_rsv_quantity;
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity - l_extra_rsv_quantity2;
|| l_update_rec.primary_reservation_quantity
,'CANCEL_MOVE_ORDER_LINE');
l_extra_rsv_quantity >= l_update_rec.primary_reservation_quantity)
THEN
l_extra_rsv_quantity
:= l_extra_rsv_quantity - l_update_rec.primary_reservation_quantity;
:= l_extra_rsv_quantity2 - l_update_rec.secondary_reservation_quantity;
|| l_update_rec.primary_reservation_quantity
,'CANCEL_MOVE_ORDER_LINE');
|| l_update_rec.secondary_reservation_quantity
,'CANCEL_MOVE_ORDER_LINE');
l_update_rec.primary_reservation_quantity := 0;
l_update_rec.secondary_reservation_quantity := 0;
|| l_update_rec.primary_reservation_quantity
,'Cancel_Move_Order_Line');
|| l_update_rec.secondary_reservation_quantity
,'Cancel_Move_Order_Line');
END IF; -- delete reservations
IF ( l_update_rec.secondary_uom_code IS NULL )
THEN
l_update_rec.secondary_reservation_quantity := NULL;
l_update_rec.secondary_detailed_quantity := NULL;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv_tbl(1)
, p_to_rsv_rec => l_update_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 2 -- Bug 5158514
);
DEBUG('update_reservation returned success'
,'CANCEL_MOVE_ORDER_LINE');
DEBUG('Error: Update Reservations returned '
|| l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
l_deleted_quantity := l_deleted_quantity + l_primary_quantity;
l_sec_deleted_quantity := l_sec_deleted_quantity + l_secondary_quantity;
inv_trx_util_pub.delete_transaction
( x_return_status => l_api_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_transaction_temp_id
);
DEBUG('delete_transaction returned success'
,'CANCEL_MOVE_ORDER_LINE');
DEBUG('Error: delete_transaction return status: '
|| l_api_return_status
,'CANCEL_MOVE_ORDER_LINE');
fnd_message.set_name('INV','INV_DELETE_TXN_FAILED');
UPDATE mtl_material_transactions_temp
SET reservation_id = NULL
WHERE move_order_line_id = p_line_id;
IF l_deleted_quantity >= l_quantity
THEN
l_quantity := 0;
l_quantity := l_quantity - l_deleted_quantity;
l_sec_quantity := l_sec_quantity - l_sec_deleted_quantity;
UPDATE mtl_txn_request_lines
SET quantity = l_quantity
, required_quantity = 0
, line_status = l_line_status
, secondary_quantity = l_sec_quantity
, secondary_required_quantity
= decode(l_sec_quantity, NULL, NULL, 0)
WHERE line_id = p_line_id;
IF p_delete_alloc IS NULL
THEN
IF g_auto_del_alloc is null
THEN
select NVL(auto_del_alloc_flag,'N')
into g_auto_del_alloc
from mtl_parameters
where organization_id = l_org_id;
l_alloc_flag := p_delete_alloc;
select count(*)
into l_count_alloc
from mtl_material_transactions_temp
where move_order_line_id = p_line_id;
inv_mo_line_detail_util.delete_allocations
( x_return_status => l_api_return_status
, x_msg_data => x_msg_count
, x_msg_count => x_msg_data
, p_mo_line_id => p_line_id
, p_transaction_temp_id => c_mmtt_rec.transaction_temp_id
);
DEBUG('delete_allocations returned success'
,'Cancel_Move_Order_Line');
DEBUG('Error: delete_allocations return status: '
|| l_api_return_status
,'Cancel_Move_Order_Line');
update mtl_txn_request_lines
set line_status = 5
where line_id = p_line_id;
SELECT 'x'
INTO l_dummy
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = p_line_id
FOR UPDATE NOWAIT;
l_xdock_rsv_tbl.DELETE;
DEBUG('About to delete reservation ID: '
|| to_char(l_rsv_rec.reservation_id)
,'CANCEL_MOVE_ORDER_LINE');
inv_reservation_pub.delete_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_serial_tbl
);
DEBUG('inv_reservation_pub.delete_reservation returned success'
,'CANCEL_MOVE_ORDER_LINE');
DEBUG('inv_reservation_pub.delete_reservation returned an error status: '
|| l_api_return_status, 'CANCEL_MOVE_ORDER_LINE');
l_xdock_rsv_tbl.DELETE;
l_deleted_quantity NUMBER;
l_update_rec inv_reservation_global.mtl_reservation_rec_type;
l_quantity_to_delete NUMBER;
l_sec_quantity_to_delete NUMBER; --INVCONV
l_sec_deleted_quantity NUMBER; --INVCONV
l_max_delete_sec_quantity NUMBER; --INVCONV
l_sec_qty_to_delete NUMBER; --INVCONV
l_max_delete_quantity NUMBER;
l_prim_quantity_to_delete NUMBER;
'secnodary_required_quantity' are also selected.*/
CURSOR c_line_info IS
SELECT quantity
,NVL(required_quantity, quantity)
, NVL(quantity_detailed, 0)
, secondary_quantity
, NVL(secondary_required_quantity, secondary_quantity) --INVCONV
, secondary_quantity_detailed --INVCONV
, organization_id
, inventory_item_id
, uom_code
, txn_source_line_id
FROM mtl_txn_request_lines
WHERE line_id = p_line_id
FOR UPDATE;
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id;
SELECT source_line_id
FROM wsh_delivery_details
WHERE move_order_line_id IS NOT NULL
AND move_order_line_id = p_line_id
AND released_status = 'S';
SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE mr.demand_source_type_id IN (2,8)
AND NVL(mr.staged_flag,'N') <> 'Y'
AND mr.demand_source_line_id = l_txn_source_line_id
AND mr.demand_source_line_detail IS NULL
AND mr.primary_reservation_quantity > NVL(mr.detailed_quantity, 0);
SELECT mmtt.transaction_temp_id
, ABS(mmtt.transaction_quantity)
, ABS(mmtt.secondary_transaction_quantity)
, ABS(mmtt.primary_quantity)
, mmtt.reservation_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_line_id
AND NOT EXISTS( SELECT 'Y'
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id
= nvl( mmtt.parent_line_id
, mmtt.transaction_temp_id
)
)
ORDER BY mmtt.transaction_quantity ASC;
SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE mr.demand_source_line_detail = p_wdd_id
AND mr.demand_source_type_id IN (2,8)
AND NVL(mr.crossdock_flag,'N') = 'Y'
FOR UPDATE NOWAIT;
SELECT wdd.delivery_detail_id
, wdd.requested_quantity
, wdd.requested_quantity_uom
, wdd.requested_quantity2
, wdd.requested_quantity_uom2
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = p_wdd_id
FOR UPDATE NOWAIT;
l_deleted_quantity := 0;
l_sec_deleted_quantity := 0; --INVCONV
SELECT NVL(mtrh.move_order_type,0)
INTO l_move_order_type
FROM mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mtrl.line_id = p_line_id
AND mtrh.header_id = mtrl.header_id;
, p_delete_reservations => 'Y'
, p_txn_source_line_id => p_txn_source_line_id
);
l_quantity_to_delete := l_quantity - l_quantity_detailed;
l_sec_quantity_to_delete := l_sec_quantity
- l_sec_quantity_detailed; --INVCONV
IF l_reduction_quantity < l_quantity_to_delete
THEN
l_quantity_to_delete := l_reduction_quantity;
l_sec_quantity_to_delete := l_sec_reduction_quantity; --INVCONV
l_reduction_quantity := l_reduction_quantity - l_quantity_to_delete;
- l_sec_quantity_to_delete; --INVCONV
DEBUG('l_quantity_to_delete : ' || l_quantity_to_delete, 'Reduce_Move_Order_Quantity');
l_quantity := l_quantity - l_quantity_to_delete; /*Bug#5095840*/
l_prim_quantity_to_delete := inv_convert.inv_um_convert
( l_inventory_item_id
, NULL
, l_quantity_to_delete
, l_mo_uom_code
, l_primary_uom_code
, NULL
, NULL
);
IF (l_prim_quantity_to_delete = -99999)
THEN
IF (l_debug = 1) THEN
DEBUG('Cannot convert uom to primary uom', 'Reduce_Move_Order_Quantity');
l_prim_quantity_to_delete := l_quantity_to_delete;
l_sec_qty_to_delete := l_sec_quantity_to_delete; --INVCONV
DEBUG('l_prim_quantity_to_delete: ' || l_prim_quantity_to_delete, 'Reduce_Move_Order_Quantity');
EXIT WHEN l_prim_quantity_to_delete <= 0;
l_update_rec := l_rsv_tbl(1);
l_update_rec.reservation_quantity := NULL;
l_update_rec.secondary_reservation_quantity := NULL;
l_max_delete_quantity := l_update_rec.primary_reservation_quantity
- l_update_rec.detailed_quantity;
l_max_delete_sec_quantity := l_update_rec.secondary_reservation_quantity
- l_update_rec.secondary_detailed_quantity; --INVCONV
DEBUG('l_max_delete_quantity::' || l_max_delete_quantity, 'Reduce_Move_Order_Quantity');
IF l_max_delete_quantity > l_prim_quantity_to_delete
THEN
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity
- l_prim_quantity_to_delete;
l_prim_quantity_to_delete := 0;
l_sec_qty_to_delete := 0; --INVCONV
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity
- l_sec_quantity_to_delete; --INVCONV
l_prim_quantity_to_delete := l_prim_quantity_to_delete
- l_max_delete_quantity;
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity
- l_max_delete_quantity;
l_sec_qty_to_delete := l_sec_qty_to_delete
- l_max_delete_sec_quantity; --INVCONV
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity
- l_max_delete_sec_quantity; --INVCONV
IF ( l_update_rec.secondary_uom_code IS NULL ) THEN
l_update_rec.secondary_reservation_quantity := NULL;
l_update_rec.secondary_detailed_quantity := NULL;
DEBUG('l_update_rec.primary_reservation_quantity::' || l_update_rec.primary_reservation_quantity, 'Reduce_Move_Order_Quantity');
DEBUG('l_update_rec.detailed_quantity::' || l_update_rec.detailed_quantity, 'Reduce_Move_Order_Quantity');
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv_tbl(1)
, p_to_rsv_rec => l_update_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
DEBUG('Update reservation returned error'
,'Reduce_Move_Order_Quantity');
DEBUG('Update reservation returned unexpected error'
,'Reduce_Move_Order_Quantity');
l_quantity_to_delete := l_reduction_quantity;
l_sec_quantity_to_delete := l_sec_reduction_quantity; --INVCONV
l_quantity_to_delete := l_task_qty;
l_sec_quantity_to_delete := l_sec_qty; --INVCONV
- l_quantity_to_delete;
- l_sec_quantity_to_delete; --INVCONV
DEBUG('l_quantity_to_delete: ' || l_quantity_to_delete, 'Reduce_Move_Order_Quantity');
, p_quantity_to_delete => l_quantity_to_delete
, p_sec_quantity_to_delete => l_sec_quantity_to_delete
);
l_deleted_quantity := l_deleted_quantity
+ l_quantity_to_delete;
l_sec_deleted_quantity := l_sec_deleted_quantity
+ l_sec_quantity_to_delete; --INCONV
DEBUG('l_deleted_quantity: ' || l_deleted_quantity, 'Reduce_Move_Order_Quantity');
l_quantity := l_quantity - l_deleted_quantity;/*Bug#5095840*/
l_sec_quantity := l_sec_quantity - l_sec_deleted_quantity; --INVCONV/*Bug#5095840*/
DEBUG(' update MTRL with quantity: ' || l_quantity ||
', quantity_detailed: ' || l_quantity_detailed, 'Reduce_Move_Order_Quantity');
/*Bug#5095840. Modified the below UPDATE statement to update
the 'required_quantity' and 'secondary_required_quantity' with
'l_required_quantity' and 'l_sec_required_quantity' respectively
rather than with NULL. Also updation of 'quantity_detailed' is
commented.*/
UPDATE mtl_txn_request_lines
SET quantity = l_quantity
, required_quantity = l_required_quantity
, secondary_quantity = l_sec_quantity --INVCONV
, secondary_required_quantity = l_sec_required_quantity --INVCONV
-- , quantity_detailed = l_quantity_detailed
, secondary_quantity_detailed = l_sec_quantity_detailed
WHERE line_id = p_line_id;
l_xdock_rsv_tbl.DELETE;
l_update_rec := l_rsv_tbl(1);
l_update_rec.reservation_quantity
:= l_update_rec.reservation_quantity - l_reduc_qty_conv;
l_update_rec.primary_reservation_quantity
:= l_update_rec.primary_reservation_quantity - l_prim_qty_conv;
IF l_update_rec.secondary_uom_code IS NOT NULL
THEN
l_update_rec.secondary_reservation_quantity
:= l_update_rec.secondary_reservation_quantity
- l_sec_reduc_qty_conv;
inv_reservation_pub.update_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv_tbl(1)
, p_to_rsv_rec => l_update_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
DEBUG('Error: Update Reservations returned '
|| x_return_status
,'Reduce_Move_Order_Quantity');
fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
ELSE -- RSV qty <= qty to reduce, so delete RSV
-- {
IF l_rsv_rec2.reservation_quantity = l_reduc_qty_conv
THEN
l_reduction_quantity := 0;
DEBUG('About to delete reservation ID: '
|| to_char(l_rsv_rec2.reservation_id)
,'Reduce_Move_Order_Quantity');
inv_reservation_pub.delete_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec2
, p_serial_number => l_serial_tbl
);
DEBUG('inv_reservation_pub.delete_reservation returned an error status: '
|| x_return_status, 'Reduce_Move_Order_Quantity');
DEBUG('inv_reservation_pub.delete_reservation returned success'
,'Reduce_Move_Order_Quantity');
l_xdock_rsv_tbl.DELETE;
, p_quantity_to_delete IN NUMBER
, p_sec_quantity_to_delete IN NUMBER DEFAULT NULL --INVCONV
, p_ato_serial_pick IN VARCHAR2 DEFAULT NULL --7190635 Added to check whether the call is for ATO serial picking
) IS
l_reservation_id NUMBER;
l_update_rec inv_reservation_global.mtl_reservation_rec_type;
l_quantity_to_delete NUMBER;
l_sec_quantity_to_delete NUMBER; --INVCONV
l_sec_deleted_quantity NUMBER; --INVCONV
l_sec_qty_to_delete NUMBER; --INVCONV
l_prim_quantity_to_delete NUMBER;
l_deleted_quantity NUMBER;
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id;
l_deleted_quantity := 0;
l_quantity_to_delete := p_quantity_to_delete;
l_sec_deleted_quantity := 0; --INVCONV
l_sec_quantity_to_delete := p_sec_quantity_to_delete; --INVCONV
DEBUG('quantity_to_delete:' || l_quantity_to_delete, 'reduce_rsv_allocation');
DEBUG('sec_quantity_to_delete:' || l_sec_quantity_to_delete, 'reduce_rsv_allocation');
SELECT ABS(mmtt.transaction_quantity)
, ABS(mmtt.primary_quantity)
, ABS(mmtt.secondary_transaction_quantity) --INVCONV
, mmtt.reservation_id
, mmtt.organization_id
, mmtt.inventory_item_id
, mtrl.uom_code
-- INVCONV correcting the orders
INTO l_task_qty
, l_primary_quantity
, l_sec_qty --INVCONV
, l_reservation_id
, l_organization_id
, l_inventory_item_id
, l_mo_uom_code
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id;
l_prim_quantity_to_delete :=
inv_convert.inv_um_convert(l_inventory_item_id, NULL, l_quantity_to_delete, l_mo_uom_code, l_primary_uom_code, NULL, NULL);
IF (l_prim_quantity_to_delete = -99999) THEN
IF (l_debug = 1) THEN
DEBUG('Cannot convert uom to primary uom', 'reduce_rsv_allocation');
l_prim_quantity_to_delete := l_quantity_to_delete;
l_sec_qty_to_delete := l_sec_quantity_to_delete; --INVCONV
l_update_rec := l_rsv_tbl(1);
IF l_update_rec.detailed_quantity > l_prim_quantity_to_delete THEN
l_update_rec.detailed_quantity := l_update_rec.detailed_quantity - l_prim_quantity_to_delete;
l_update_rec.secondary_detailed_quantity := l_update_rec.secondary_detailed_quantity - l_sec_qty_to_delete; --INCONV
l_update_rec.detailed_quantity := 0;
l_update_rec.secondary_detailed_quantity := 0; --INCONV
l_update_rec.reservation_quantity := NULL;
IF l_update_rec.primary_reservation_quantity > l_prim_quantity_to_delete THEN
l_update_rec.primary_reservation_quantity := l_update_rec.primary_reservation_quantity - l_prim_quantity_to_delete;
l_update_rec.secondary_reservation_quantity := l_update_rec.secondary_reservation_quantity - l_sec_qty_to_delete; --INVCONV
ELSE -- delete entire reservation
l_update_rec.primary_reservation_quantity := 0;
l_update_rec.secondary_reservation_quantity := 0; --INVCONV
DEBUG('update reservation', 'reduce_rsv_allocation');
IF ( l_update_rec.secondary_uom_code IS NULL ) THEN
l_update_rec.secondary_reservation_quantity := NULL;
l_update_rec.secondary_detailed_quantity := NULL;
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv_tbl(1)
, p_to_rsv_rec => l_update_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
DEBUG('Update reservation returned error', 'reduce_rsv_allocation');
DEBUG('Update reservation returned unexpected error', 'reduce_rsv_allocation');
DEBUG('Quantity to delete:'||l_quantity_to_delete, 'reduce_rsv_allocation');
IF l_quantity_to_delete = l_task_qty
OR (l_retain_ato_profile = 'Y' AND l_ato_serial_pick='Y') THEN
l_deleted_quantity := l_deleted_quantity + l_task_qty;
l_sec_deleted_quantity := l_sec_deleted_quantity + l_sec_qty; --INVCONV
inv_trx_util_pub.delete_transaction(
x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_transaction_temp_id
);
l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
l_sec_deleted_quantity := l_sec_deleted_quantity + l_sec_quantity_to_delete; --INVCONV
, p_quantity => l_quantity_to_delete
, p_secondary_quantity => l_sec_quantity_to_delete --INVCONV
);
/* The following procedure is called by shipping to update carton group id whenever
shipping unassign a wdd line from a delivery */
PROCEDURE update_mol_carton_group
( x_return_status OUT NOCOPY VARCHAR2
, x_msg_cnt OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_line_id IN NUMBER
, p_carton_grouping_id IN NUMBER
) IS
l_debug NUMBER;
SELECT mtrh.move_order_type
FROM mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mtrl.line_id = p_mo_line_id
AND mtrh.header_id = mtrl.header_id;
DEBUG('move order line id: ' || p_line_id, 'update_mol_carton_group');
DEBUG('carton group id: ' || p_carton_grouping_id, 'update_mol_carton_group');
DEBUG('before update statement...', 'update_mol_carton_group');
UPDATE mtl_txn_request_lines
SET carton_grouping_id = p_carton_grouping_id
WHERE line_id = p_line_id;
DEBUG('can not find move order line', 'update_mol_carton_group');
DEBUG('after update statement', 'update_mol_carton_group');
DEBUG('Exception: ' || sqlerrm, 'update_mol_carton_group');
fnd_message.set_name('INV', 'FAIL_TO_UPDATE_CARTON_GROUP'); -- need new msg
END update_mol_carton_group;