[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mtl_txn_request_headers_s.NEXTVAL
INTO l_header_id
FROM DUAL;
SELECT 'X'
INTO l_dummy
FROM mtl_txn_request_headers
WHERE request_number = TO_CHAR(l_header_id)
AND organization_id = NVL(p_organization_id, organization_id);
SELECT 'X'
INTO l_dummy
FROM mtl_txn_request_headers
WHERE request_number = p_request_number
AND organization_id = p_organization_id;
SELECT 'X'
INTO l_dummy
FROM mtl_txn_request_lines
WHERE header_id = p_header_id
AND organization_id = p_organization_id
AND line_number = p_line_number;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
PROCEDURE delete_troldt(x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, p_troldt_tbl IN inv_mo_line_detail_util.g_mmtt_tbl_type, p_move_order_type IN NUMBER) IS
l_troldt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_temp_rec
, p_to_rsv_rec => l_to_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
inv_mo_line_detail_util.delete_row(x_return_status => l_return_status, p_line_id => l_troldt_tbl(l_counter).move_order_line_id, p_line_detail_id => l_troldt_tbl(l_counter).transaction_temp_id);
END delete_troldt;
PROCEDURE update_lots_temp(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_operation VARCHAR2
, p_item_id NUMBER
, p_org_id NUMBER
, p_trx_temp_id NUMBER
, p_cancel_qty NUMBER
, p_trx_uom VARCHAR2
, p_primary_uom VARCHAR2
, p_last_updated_by NUMBER
, p_last_update_date DATE
, p_creation_date DATE
, p_created_by NUMBER
) IS
l_mtlt_cancel_qty NUMBER;
SELECT transaction_quantity,
secondary_quantity --INVCONV
, lot_number -- nsinghi bug#5724815.
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_temp_id
ORDER BY creation_date
FOR UPDATE OF transaction_temp_id;
SELECT tracking_quantity_ind, secondary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
IF p_operation = 'DELETE' THEN -- when cancel qty >sum(mmtt trx qty)
IF (l_debug = 1) THEN
DEBUG('Deleting the row in mtlt ', 'UPDATE LOTS TEMP');
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_temp_id;
ELSIF p_operation = 'UPDATE' THEN -- when cancel qty < sum(mmtt trx qty)
SELECT COUNT(*)
INTO l_lot_count
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_temp_id;
DEBUG('Delete current row ', 'UPDATE LOTS TEMP');
DELETE FROM mtl_transaction_lots_temp
WHERE CURRENT OF c_mtlt;
DEBUG('Update current row ', 'UPDATE LOTS TEMP');
DEBUG('Cannot convert uom to secondary uom', 'UPDATE LOTS TEMP');
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_mtlt_trx_qty
, secondary_quantity = l_mtlt_sec_trx_qty -- INVCONV
, primary_quantity = l_mtlt_primary_qty
, last_update_date = p_last_update_date
, last_updated_by = p_last_updated_by
, creation_date = p_creation_date
, created_by = p_created_by
WHERE CURRENT OF c_mtlt;
fnd_msg_pub.add_exc_msg(g_pkg_name, 'update lots temp');
END update_lots_temp;
PROCEDURE update_serial_temp(x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, p_operation VARCHAR2, p_trx_temp_id NUMBER, p_cancel_qty NUMBER) IS
l_serial_temp_id NUMBER;
SELECT fm_serial_number
, to_serial_number
, group_header_id
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_trx_temp_id
ORDER BY fm_serial_number DESC
FOR UPDATE OF transaction_temp_id;
IF p_operation = 'DELETE' THEN -- when cancel qty >sum(mmtt trx qty)
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_trx_temp_id;
ELSIF p_operation = 'UPDATE' THEN -- when cancel qty < sum(mmtt trx qty)
SELECT transaction_header_id
INTO l_transaction_header_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_trx_temp_id;
SELECT COUNT(*)
INTO l_serial_count
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_trx_temp_id;
DELETE FROM mtl_serial_numbers_temp
WHERE CURRENT OF serial_temp_csr;
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE group_mark_id IN (p_trx_temp_id, l_transaction_header_id)
AND serial_number >= NVL(l_fm_serial_number, serial_number)
AND serial_number <= NVL(l_to_serial_number, NVL(l_fm_serial_number, serial_number))
AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
,p_update_reservation => fnd_api.g_true);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'update serial temp');
END update_serial_temp;
PROCEDURE update_troldt(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_trolin_rec IN inv_move_order_pub.trolin_rec_type
, p_old_trolin_rec IN inv_move_order_pub.trolin_rec_type
, p_troldt_tbl IN inv_mo_line_detail_util.g_mmtt_tbl_type
, p_move_order_type IN NUMBER
, x_trolin_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
, p_delete_mmtt IN VARCHAR2 DEFAULT 'YES' --Added bug3524130
) IS
l_troldt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
DEBUG('Cannot convert uom to secondary uom', 'UPDATE TROLNDT ');
DEBUG(l_quantity_cancel, 'UPDATE TROLNDT ');
DEBUG('inside the loop', 'UPDATE TROLNDT');
SELECT lot_control_code
, serial_number_control_code
INTO l_lot_control_code
, l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_troldt_tbl(l_counter).inventory_item_id
AND organization_id = l_troldt_tbl(l_counter).organization_id;
DEBUG('Lot control code '|| TO_CHAR(l_lot_control_code), 'UPDATE TROLNDT');
DEBUG('inside the decrease quantity condition', 'UPDATE TROLNDT');
if detail exist, we need to delete the line detail for the delta quantity
and update the detailed_qty on reservation if it exist for the particular detail record */
IF (l_troldt_tbl(l_counter).transaction_quantity <= l_quantity_cancel) THEN
IF (l_debug = 1) THEN
DEBUG('delete detail row', 'UPDATE TROLNDT');
update_lots_temp(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_operation => 'DELETE'
, p_item_id => l_troldt_tbl(l_counter).inventory_item_id
, p_org_id => l_troldt_tbl(l_counter).organization_id
, p_trx_temp_id => l_troldt_tbl(l_counter).transaction_temp_id
, p_cancel_qty => l_quantity_cancel
, p_trx_uom => l_troldt_tbl(l_counter).transaction_uom
, p_primary_uom => l_troldt_tbl(l_counter).item_primary_uom_code
, p_last_updated_by => l_troldt_tbl(l_counter).last_updated_by
, p_last_update_date => l_troldt_tbl(l_counter).last_update_date
, p_creation_date => l_troldt_tbl(l_counter).creation_date
, p_created_by => l_troldt_tbl(l_counter).created_by
);
update_serial_temp(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_operation => 'DELETE', p_trx_temp_id => l_troldt_tbl(l_counter).transaction_temp_id, p_cancel_qty => l_quantity_cancel);
IF p_delete_mmtt = 'YES' THEN --Added bug3524130
inv_mo_line_detail_util.delete_row(x_return_status => l_return_status, p_line_id => l_trolin_rec.line_id, p_line_detail_id => l_troldt_tbl(l_counter).transaction_temp_id);
DEBUG('only need to update the line detail', 'UPDATE TROLNDT ');
update_lots_temp(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_operation => 'UPDATE'
, p_item_id => l_troldt_tbl(l_counter).inventory_item_id
, p_org_id => l_troldt_tbl(l_counter).organization_id
, p_trx_temp_id => l_troldt_tbl(l_counter).transaction_temp_id
, p_cancel_qty => l_quantity_cancel
, p_trx_uom => l_troldt_tbl(l_counter).transaction_uom
, p_primary_uom => l_troldt_tbl(l_counter).item_primary_uom_code
, p_last_updated_by => l_troldt_tbl(l_counter).last_updated_by
, p_last_update_date => l_troldt_tbl(l_counter).last_update_date
, p_creation_date => l_troldt_tbl(l_counter).creation_date
, p_created_by => l_troldt_tbl(l_counter).created_by
);
update_serial_temp(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_operation => 'DELETE', p_trx_temp_id => l_troldt_tbl(l_counter).transaction_temp_id, p_cancel_qty => l_quantity_cancel);
DEBUG('Cannot convert uom to secondary uom', 'UPDATE TROLNDT ');
inv_mo_line_detail_util.update_row(x_return_status => l_return_status, p_mo_line_detail_rec => l_troldt_tbl(l_counter));
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_temp_rec
, p_to_rsv_rec => l_to_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
DEBUG('change status', 'UPDATE TROLNDT');
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv_temp_rec
, p_to_rsv_rec => l_to_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
inv_mo_line_detail_util.delete_row(x_return_status => l_return_status, p_line_id => l_trolin_rec.line_id, p_line_detail_id => l_troldt_tbl(l_counter).transaction_temp_id);
END update_troldt;
ELSIF l_trohdr_rec.operation = inv_globals.g_opr_update
OR l_trohdr_rec.operation = inv_globals.g_opr_delete THEN
l_trohdr_rec.db_flag := fnd_api.g_true;
IF (l_trohdr_rec.operation = inv_globals.g_opr_update
OR l_trohdr_rec.operation = inv_globals.g_opr_create
OR l_trohdr_rec.operation = inv_globals.g_opr_delete)
THEN
-- Attribute level validation.
IF l_control_rec.default_attributes
OR l_control_rec.change_attributes THEN
-- Default missing attributes
--Bug# 4554438.Start
-- l_tmp_trohdr_rec is passed as there is NOCOPY for x_trohdr_rec
-- inv_default_trohdr.ATTRIBUTES(p_trohdr_rec => l_trohdr_rec, x_trohdr_rec => l_trohdr_rec);
IF l_trohdr_rec.operation = inv_globals.g_opr_delete THEN
inv_validate_trohdr.entity_delete(x_return_status => l_return_status, p_trohdr_rec => l_trohdr_rec);
IF l_trohdr_rec.operation = inv_globals.g_opr_delete THEN
inv_trohdr_util.delete_row(p_header_id => l_trohdr_rec.header_id);
l_trohdr_rec.last_update_date := SYSDATE;
l_trohdr_rec.last_updated_by := fnd_global.user_id;
l_trohdr_rec.last_update_login := fnd_global.login_id;
IF l_trohdr_rec.operation = inv_globals.g_opr_update THEN
inv_trohdr_util.update_row(l_trohdr_rec);
inv_trohdr_util.insert_row(l_trohdr_rec);
, p_delete_mmtt IN VARCHAR2 DEFAULT 'YES' --Added bug3524130
) IS
l_return_status VARCHAR2(1);
ELSIF l_trolin_rec.operation = inv_globals.g_opr_update
OR l_trolin_rec.operation = inv_globals.g_opr_delete THEN
l_trolin_rec.db_flag := fnd_api.g_true;
IF l_trolin_rec.operation = inv_globals.g_opr_delete THEN
inv_validate_trolin.entity_delete(x_return_status => l_return_status, p_trolin_rec => l_trolin_rec);
IF l_trolin_rec.operation = inv_globals.g_opr_delete THEN
--debug('Calling inv_mold_query_rows');
DEBUG('Calling Delete_Troldt', 'Inv_Transfer_Order_PVT.Trolins');
delete_troldt(x_return_status => l_return_status, x_msg_data => l_msg_data, x_msg_count => l_msg_count, p_troldt_tbl => l_troldt_tbl, p_move_order_type => l_move_order_type);
inv_trolin_util.delete_row(p_line_id => l_trolin_rec.line_id);
ELSIF l_trolin_rec.operation = inv_globals.g_opr_update THEN
-- Get Who Information
-- Bug 3030538. Creation_date becomes null when both an update and
-- create are done in move orders form
l_trolin_rec.creation_date := l_old_trolin_rec.creation_date;
l_trolin_rec.last_update_date := SYSDATE;
l_trolin_rec.last_updated_by := fnd_global.user_id;
l_trolin_rec.last_update_login := fnd_global.login_id;
DEBUG('calling update troldt', 'Inv_Transfer_Order_PVT.Trolins');
update_troldt(
x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_trolin_rec => l_trolin_rec
, p_old_trolin_rec => l_old_trolin_rec
, p_troldt_tbl => l_troldt_tbl
, p_move_order_type => p_move_order_type
, x_trolin_rec => l_trolin_rec
, p_delete_mmtt => p_delete_mmtt --Added bug 3524130
);
DEBUG('calling update row', 'Inv_Transfer_Order_PVT.Trolins');
inv_trolin_util.update_row(l_trolin_rec);
DEBUG('after update row', 'Inv_Transfer_Order_PVT.Trolins');
l_trolin_rec.last_update_date := SYSDATE;
l_trolin_rec.last_updated_by := fnd_global.user_id;
l_trolin_rec.last_update_login := fnd_global.login_id;
inv_trolin_util.insert_row(l_trolin_rec);
, p_delete_mmtt IN VARCHAR2 DEFAULT 'YES' --Added bug3524130
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
, p_delete_mmtt => p_delete_mmtt --Added bug 3524130
);
PROCEDURE update_txn_source_line(p_line_id IN NUMBER,
p_new_source_line_id IN NUMBER)
IS
l_debug number;
SELECT reservation_id, transaction_quantity, primary_quantity
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_line_id;
UPDATE mtl_txn_request_lines
SET txn_source_line_id = p_new_source_line_id
WHERE line_id = p_line_id;
UPDATE mtl_material_transactions_temp
SET trx_source_line_id = p_new_source_line_id
WHERE move_order_line_id = p_line_id;
debug('About to call inv_reservation_pub.query_reservations', 'update_txn_source_line');
debug('Error from query_reservations', 'update_txn_source_line');
debug('Error from query_reservations', 'update_txn_source_line');
debug('Original Reservation_ID: ' || rec_mmtt.reservation_id, 'update_txn_source_line');
END update_txn_source_line;
g_mo_picked_quantity_tbl.DELETE;
g_rsv_picked_quantity_tbl.DELETE;
g_mmtt_cache_tbl.DELETE; --Bug: 4994950 (Actual bug #4762505)
SELECT reservation_id
FROM mtl_reservations
WHERE NVL(staged_flag, 'N') = 'N'
AND supply_source_type_id = 13
AND demand_source_type_id IN (2, 8)
AND demand_source_line_id = p_source_line_id
AND demand_source_line_detail IS NULL;
SELECT 1
INTO l_ato_item
FROM dual
WHERE EXISTS (SELECT msi.inventory_item_id
FROM mtl_system_items msi, mtl_Reservations mtr
WHERE msi.inventory_item_id = mtr.inventory_item_id
AND msi.organization_id = mtr.organization_id
AND bom_item_type = 4
AND replenish_to_order_flag = 'Y'
AND mtr.demand_source_line_id = p_source_line_id
AND mtr.demand_source_line_detail IS NULL);
SELECT 1
INTO l_unstaged_so_exists
FROM dual
WHERE EXISTS ( SELECT delivery_Detail_id
FROM wsh_delivery_Details
WHERE source_line_id = p_source_line_id
AND released_status in ( 'B','R','S'));
SELECT 1
INTO l_unstaged_so_exists
FROM DUAL
WHERE EXISTS( SELECT /*+ index(wsh_delivery_details WSH_DELIVERY_DETAILS_N3) */ delivery_detail_id
FROM wsh_delivery_details
WHERE source_line_id = p_source_line_id
AND (released_status IN ('B','R','S')));
inv_reservation_pub.delete_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_mtl_reservation_rec
, p_serial_number => l_original_serial_number
);
DEBUG('after delete reservation return status is '|| l_return_status, 'Clean_Reservations');
l_update_shipping BOOLEAN;
SELECT primary_uom_code, secondary_uom_code, tracking_quantity_ind,secondary_default_ind
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT lot_number
, primary_quantity
, transaction_quantity
, secondary_quantity
, secondary_unit_of_measure
, serial_transaction_temp_id
, grade_code
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_trx_temp_id
ORDER BY lot_number;
SELECT msn.serial_number
FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = DECODE(l_lot_control_code, 1, l_trx_temp_id, l_ser_trx_temp_id)
AND msn.current_organization_id = l_org_id
AND msn.inventory_item_id = l_item_id
AND msn.serial_number BETWEEN msnt.fm_serial_number AND NVL(msnt.to_serial_number, msnt.fm_serial_number)
AND length(msn.serial_number) = length(msnt.fm_serial_number);
SELECT reservation_id
, primary_reservation_quantity
, detailed_quantity
FROM mtl_reservations
WHERE demand_source_line_id = l_trolin_rec.txn_source_line_id
AND NVL(detailed_quantity, 0) > 0
AND NVL(staged_flag, 'N') = 'N'
AND demand_source_line_detail IS NULL;
SELECT reservation_id
FROM mtl_reservations
WHERE NVL(staged_flag, 'N') = 'N'
AND demand_source_line_id = l_trolin_rec.txn_source_line_id
AND demand_source_line_detail IS NULL;
SELECT msn.serial_number
FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_trx_temp_id
AND msn.serial_number BETWEEN msnt.fm_serial_number AND NVL(msnt.to_serial_number, msnt.fm_serial_number)
AND length(msn.serial_number) = length(msnt.fm_serial_number)
AND msn.reservation_id = l_reservation_id;
Select entity_type
From wip_entities
Where wip_entity_id = l_trolin_rec.txn_source_id;
SELECT ABS(transaction_quantity) ,
mmtt.transaction_uom,
ABS(primary_quantity),
NVL(ABS(secondary_transaction_quantity), 0),
secondary_uom_code
FROM mtl_material_transactions_temp mmtt
WHERE move_order_line_id = p_mo_line_id;
SELECT moh.move_order_type
INTO l_move_order_type
FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
WHERE mol.line_id = l_trolin_rec.line_id
AND mol.header_id = moh.header_id;
SELECT NVL(SUM(ABS(transaction_quantity)), 0)
, NVL(SUM(ABS(primary_quantity)), 0)
, NVL(SUM(ABS(secondary_transaction_quantity)), 0)
INTO l_pending_quantity
, l_primary_pending_quantity
, l_sec_pending_quantity
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_trolin_rec.line_id; */
SELECT NVL(SUM(ABS(primary_quantity)), 0)
, NVL(SUM(ABS(secondary_transaction_quantity)), 0)
INTO l_rsv_primary_quantity
, l_sec_rsv_quantity
FROM mtl_material_transactions_temp
WHERE reservation_id = l_mmtt_rec.reservation_id;
DEBUG('Unable to lock the work order line for update', 'Finalize_Pick_Confirm');
SELECT COUNT(*)
INTO l_other_mmtt_rec
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_trolin_rec.line_id
AND transaction_temp_id <> l_mmtt_rec.transaction_temp_id;
g_mmtt_cache_tbl.DELETE(MOD(l_trolin_rec.line_id,2147483648));
g_mmtt_cache_tbl.DELETE(MOD(l_trolin_rec.line_id,2147483648)); --Bug:4994950
DEBUG('calling update_row', 'Finalize_Pick_Confirm');
inv_trolin_util.update_row(l_trolin_rec);
DEBUG('Replenishment Move Order will not update g_mmtt_cache_tbl', 'Finalize_Pick_Confirm');
SELECT lot_control_code
, serial_number_control_code
, reservable_type
INTO l_lot_control_code
, l_serial_control_code
, l_reservable_type_item
FROM mtl_system_items
WHERE inventory_item_id = l_mmtt_rec.inventory_item_id
AND organization_id = l_mmtt_rec.organization_id;
DEBUG('After select lot_control_code = '|| l_lot_control_code, 'Finalize_Pick_confirm');
DEBUG('After select l_serial_control_code = '|| l_serial_control_code, 'Finalize_Pick_Confirm');
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_mmtt_rec.inventory_item_id;
,p_update_reservation => fnd_api.g_true);
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_mmtt_rec.inventory_item_id;
,p_update_reservation => fnd_api.g_true);
SELECT reservable_type
INTO l_reservable_type
FROM mtl_secondary_inventories
WHERE organization_id = l_mmtt_rec.organization_id
AND secondary_inventory_name = to_char(l_mmtt_rec.transfer_subinventory);
DEBUG('Before calling update_reservation l_mtl_reservation_rec.reservation_quantity '|| l_mtl_reservation_rec.reservation_quantity, 'Finalize_Pick_Confirm');
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true -- Explicitly set the validation flag to true with respect to the Bug 4004597
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
SELECT COUNT(*) INTO l_lot_count
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
UPDATE mtl_serial_numbers
SET reservation_id = null
WHERE reservation_id = l_mtl_reservation_tbl(1).reservation_id
AND serial_number = l_serial_number;
DEBUG('serial count with updated null reservation_id = ' || l_serial_count, 'Finalize_Pick_Confirm');
UPDATE mtl_reservations
SET serial_reservation_quantity = serial_reservation_quantity - l_serial_count
WHERE reservation_id = l_mtl_reservation_tbl(1).reservation_id;
UPDATE mtl_serial_numbers
SET reservation_id = null
WHERE reservation_id = l_mtl_reservation_tbl(1).reservation_id
AND serial_number = l_serial_number;
DEBUG('serial count with updated null reservation_id = ' || l_serial_count, 'Finalize_Pick_Confirm');
UPDATE mtl_reservations
SET serial_reservation_quantity = serial_reservation_quantity - l_serial_count
WHERE reservation_id = l_mtl_reservation_tbl(1).reservation_id;
DEBUG('not reservable staging subinventory, '|| 'delete org wide reservation', 'Finalize_Pick_Confirm');
ELSE -- if qty > rsv qty, delete reservation
l_mtl_reservation_rec.primary_reservation_quantity := 0;
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
SELECT reservable_type
INTO l_reservable_type_lot
FROM mtl_lot_numbers
WHERE inventory_item_id = l_mtl_reservation_rec.inventory_item_id
AND organization_id = l_mtl_reservation_rec.organization_id
AND lot_number = l_mtl_reservation_rec.lot_number;
SELECT lot_divisible_flag
INTO l_lot_divisible_flag
FROM mtl_system_items
WHERE inventory_item_id = l_mmtt_rec.inventory_item_id
AND organization_id = l_mmtt_rec.organization_id;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET POSTING_FLAG = 'N'
WHERE inventory_item_id = l_mmtt_rec.inventory_item_id
AND organization_id = l_mmtt_rec.organization_id
AND transaction_source_id= l_mmtt_rec.transaction_source_id
AND POSTING_FLAG = 'Y'
AND TRANSACTION_ACTION_ID = 28 --Staging Transfer
AND TRANSACTION_SOURCE_TYPE_ID in (2,8); --SO or ISO
DEBUG('update POSTING_FLAG to N for indivisible item ','Finalize_Pick_Confirm');
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET POSTING_FLAG = 'Y'
WHERE inventory_item_id = l_mmtt_rec.inventory_item_id
AND organization_id = l_mmtt_rec.organization_id
AND transaction_source_id= l_mmtt_rec.transaction_source_id
AND POSTING_FLAG = 'N'
AND TRANSACTION_ACTION_ID = 28 --Staging Transfer
AND TRANSACTION_SOURCE_TYPE_ID in (2,8); --SO or ISO
DEBUG('update POSTING_FLAG back to Y ','Finalize_Pick_Confirm');
inv_staged_reservation_util.update_staged_flag(x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_reservation_id => l_reservation_id, p_staged_flag => 'Y');
DEBUG('Error in update_staged_flag', 'Finalize_Pick_Confirm');
DEBUG('Unexpected error in update_staged_flag', 'Finalize_Pick_Confirm');
END IF; -- Create or Update
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
inv_staged_reservation_util.update_staged_flag
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_reservation_id
, p_staged_flag => 'Y'
);
DEBUG('Error in update_staged_flag', 'Finalize_Pick_Confirm');
DEBUG('Unexpected error in update_staged_flag', 'Finalize_Pick_Confirm');
END IF; -- Create or Update
DEBUG('before select delivery_detail_id', 'Finalize_Pick_Confirm');
SELECT /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/
delivery_detail_id, source_header_id, source_line_id
INTO l_delivery_detail_id, l_source_header_id, l_source_line_id
FROM wsh_delivery_details WDD
WHERE WDD.move_order_line_id = l_mmtt_rec.move_order_line_id
AND WDD.move_order_line_id IS NOT NULL
AND WDD.released_status = 'S'
FOR UPDATE NOWAIT;
DEBUG('after select delivery_detail_id', 'Finalize_Pick_Confirm');
SELECT license_plate_number
INTO l_container_name
FROM wms_license_plate_numbers
WHERE organization_id= l_mmtt_rec.organization_id
AND lpn_id=l_shipping_attr(1).transfer_lpn_id;
SELECT wdd.released_status,wdd.delivery_detail_id
INTO l_status_code,l_container_delivery_det_id
FROM wsh_delivery_details wdd
WHERE wdd.container_name =l_container_name
AND wdd.released_status = 'X'; -- ER : 6845650
WSH_CONTAINER_GRP.Update_Container(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_container_rec => l_container_rec
);
debug('WSH_Container_Grp.Update_Containers returns error','Finalize Pick Confirm');
debug('WSH_Container_Grp.Update_Containers returns success','Finalize Pick Confirm');
/*UPDATE WSH_DELIVERY_DETAILS
SET CONTAINER_NAME=l_new_container_name,
lpn_id=NULL,
last_update_date=sysdate,
last_updated_by=fnd_global.user_id,
last_update_login=fnd_global.login_id
WHERE container_name = l_container_name ;
DEBUG('about to call update shipping attributes', 'Finalize_Pick_Confirm');
SELECT 1 INTO l_dummy_num
FROM fnd_tables
WHERE table_name = 'WMS_OP_PLANS_B'
AND rownum < 2;
l_update_shipping := TRUE;
SELECT WMS_CATCH_WEIGHT_PVT.G_PRICE_PRIMARY INTO l_catch_weight_enabled FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = l_mmtt_rec.organization_id
AND mmtt.inventory_item_id = l_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision, '@') = NVL(l_mmtt_rec.revision, '@')
AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(mmtt.content_lpn_id, mmtt.transfer_lpn_id) = l_lpn_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = l_lot_number
AND (mtlt.secondary_quantity IS NULL OR mtlt.secondary_unit_of_measure IS NULL) );
DEBUG('Calling Update Shipping Attributes for a serial range of lot items', 'Finalize Pick Confirm');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
l_update_shipping := FALSE;
IF l_update_shipping = TRUE THEN
l_shipping_attr(1).serial_number := l_serial_number;
DEBUG('Calling Update Shipping Attributes', 'Finalize Pick Confirm');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_mmtt_rec.inventory_item_id;
,p_update_reservation => fnd_api.g_true);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_trx_temp_id;
SELECT WMS_CATCH_WEIGHT_PVT.G_PRICE_PRIMARY INTO l_catch_weight_enabled FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = l_mmtt_rec.organization_id
AND mmtt.inventory_item_id = l_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision, '@') = NVL(l_mmtt_rec.revision, '@')
AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(mmtt.content_lpn_id, mmtt.transfer_lpn_id) = l_lpn_id
AND (mmtt.secondary_transaction_quantity IS NULL OR mmtt.secondary_uom_code IS NULL) );
DEBUG('Calling Update Shipping Attributes for a serial range', 'Finalize Pick Confirm');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return unexpected error from update shipping attributes', 'Finalize_Pick_Confirm');
l_update_shipping := TRUE;
l_update_shipping := FALSE;
IF l_update_shipping = TRUE THEN
l_shipping_attr(1).serial_number := l_serial_number;
DEBUG('Calling Update Shipping Attributes', 'Finalize Pick Confirm');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_mmtt_rec.inventory_item_id;
,p_update_reservation => fnd_api.g_true);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT WMS_CATCH_WEIGHT_PVT.G_PRICE_PRIMARY INTO l_catch_weight_enabled FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = l_mmtt_rec.organization_id
AND mmtt.inventory_item_id = l_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision, '@') = NVL(l_mmtt_rec.revision, '@')
AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(mmtt.content_lpn_id, mmtt.transfer_lpn_id) = l_lpn_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = l_lot_number
AND (mtlt.secondary_quantity IS NULL OR mtlt.secondary_unit_of_measure IS NULL) );
DEBUG('Calling Update Shipping Attributes', 'Finalize Pick Confirm');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
SELECT WMS_CATCH_WEIGHT_PVT.G_PRICE_PRIMARY INTO l_catch_weight_enabled FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = l_mmtt_rec.organization_id
AND mmtt.inventory_item_id = l_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision, '@') = NVL(l_mmtt_rec.revision, '@')
AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(mmtt.content_lpn_id, mmtt.transfer_lpn_id) = l_lpn_id
AND (mmtt.secondary_transaction_quantity IS NULL OR mmtt.secondary_uom_code IS NULL) );
DEBUG('Calling Update Shipping Attributes', 'Finalize Pick Confirm');
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr, x_return_status => l_return_status);
DEBUG('after update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('after update shipping attributes Raising exec_wsh_int_upd_SA', 'Finalize_Pick_Confirm');
DEBUG('return error from update shipping attributes', 'Finalize_Pick_Confirm');
DEBUG('return unexpected error from update shipping attributes', 'Finalize_Pick_Confirm');
SELECT NVL(SUM(ABS(primary_quantity)), 0)
, NVL(SUM(ABS(secondary_transaction_quantity)), 0)
INTO l_mmtt_rsv_quantity
, l_sec_mmtt_rsv_quantity
FROM mtl_material_transactions_temp
WHERE reservation_id = l_reservation_id;
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_mtl_reservation_rec2
, p_to_rsv_rec => l_mtl_reservation_rec3
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
SELECT lot_control_code
, serial_number_control_code
, reservable_type
INTO l_lot_control_code
, l_serial_control_code
, l_reservable_type_item
FROM mtl_system_items
WHERE inventory_item_id = l_mmtt_rec.inventory_item_id
AND organization_id = l_mmtt_rec.organization_id;
DEBUG('After select lot_control_code = '|| l_lot_control_code, 'Finalize_Pick_confirm');
DEBUG('After select l_serial_control_code = '|| l_serial_control_code, 'Finalize_Pick_Confirm');
SELECT reservable_type
INTO l_reservable_type
FROM mtl_secondary_inventories
WHERE organization_id = l_mmtt_rec.organization_id
AND secondary_inventory_name = l_mmtt_rec.transfer_subinventory;
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true -- Explicitly set the validation flag to true with respect to the Bug 4004597
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
SELECT COUNT(*) INTO l_lot_count
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
DEBUG('not reservable staging subinventory, '|| 'delete org wide reservation', 'Finalize_Pick_Confirm');
ELSE -- if qty > rsv qty, delete reservation
l_mtl_reservation_rec.primary_reservation_quantity := 0;
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
DEBUG('Update reservation id '|| l_mtl_reservation_rec.reservation_id, 'Finalize_Pick_Confirm');
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
SELECT reservable_type
INTO l_reservable_type_lot
FROM mtl_lot_numbers
WHERE inventory_item_id = l_mtl_reservation_rec.inventory_item_id
AND organization_id = l_mtl_reservation_rec.organization_id
AND lot_number = l_mtl_reservation_rec.lot_number;
inv_staged_reservation_util.update_staged_flag(x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_reservation_id => l_reservation_id, p_staged_flag => 'Y');
DEBUG('Error in update_staged_flag', 'Finalize_Pick_Confirm');
DEBUG('Unexpected error in update_staged_flag', 'Finalize_Pick_Confirm');
END IF; -- Create or Update
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_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
inv_staged_reservation_util.update_staged_flag
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_reservation_id
, p_staged_flag => 'Y'
);
DEBUG('Error in update_staged_flag', 'Finalize_Pick_Confirm');
DEBUG('Unexpected error in update_staged_flag', 'Finalize_Pick_Confirm');
END IF; -- Create or Update
SELECT NVL(SUM(ABS(primary_quantity)), 0)
, NVL(SUM(ABS(secondary_transaction_quantity)), 0)
INTO l_mmtt_rsv_quantity
, l_sec_mmtt_rsv_quantity
FROM mtl_material_transactions_temp
WHERE reservation_id = l_reservation_id;
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_mtl_reservation_rec2
, p_to_rsv_rec => l_mtl_reservation_rec3
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
, p_over_reservation_flag => 3 -- Bug 4997704, Passing p_over_reservation_flag to reservation API to handle overpicking scenarios
);
DEBUG('after update reservation return status is '|| l_return_status, 'Finalize_Pick_Confirm');
DEBUG('wsh_interface.update_shipping_attributes Raised an exception ', 'Finalize_Pick_Confirm');
SELECT DISTINCT move_order_line_id
FROM mtl_material_transactions_temp a
WHERE transaction_header_id = p_transaction_header_id
AND transaction_source_type_id = 4
AND transaction_action_id = 2
AND move_order_line_id IS NOT NULL
AND nvl(transaction_status, 1) <> 2
AND process_flag = 'Y'
AND NOT EXISTS ( SELECT 1
FROM mtl_material_transactions_temp b
WHERE b.move_order_line_id = a.move_order_line_id
AND (nvl(transaction_status,1) = 2 OR error_code IS NOT NULL));
SELECT DISTINCT move_order_line_id
FROM mtl_material_transactions_temp a
WHERE transaction_header_id = p_transaction_header_id
AND move_order_line_id IS NOT NULL
AND nvl(transaction_status,1) <> 2
AND process_flag = 'Y'
AND NOT EXISTS ( SELECT 1
FROM mtl_material_transactions_temp b
WHERE b.move_order_line_id = a.move_order_line_id
AND (nvl(transaction_status,1) = 2 OR error_code IS NOT NULL));
SELECT mtrh.move_order_type
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
WHERE mtrl.line_id = p_mo_line_id
AND mtrh.header_id = mtrl.header_id;
inv_trolin_util.update_row(l_trolin_rec);
SELECT a.rowid
,a.FM_SERIAL_NUMBER
,a.TO_SERIAL_NUMBER
,b.organization_id
,b.inventory_item_id
FROM mtl_Serial_numbers_temp a, mtl_material_transactions_temp b
where a.transaction_temp_id= p_transaction_temp_id
and a.transaction_temp_id = b.transaction_temp_id;
delete_remaining_serial BOOLEAN := FALSE;
l_new_num_update NUMBER;
l_new_NUM_str_update VARCHAR2(100);
if (qty_count>p_qty AND NOT delete_remaining_Serial) then
--get rid of xtra serial numbers from msnt
--(qty_count-p_qty) serials
--if the diff_qty>(qty_count-p_qty)
--if range then reduce this range
--following logic will derive the to_Serial_number
--If range
IF (l_debug = 1) THEN
DEBUG('p_qty= '||p_qty||' diff_qty= '||diff_qty||' qty_count= '||qty_count,'INV_TRANSFER_ORDER_PVT.adjust_serial_numbers_in_MMTT');
delete from mtl_Serial_numbers_temp
where transaction_temp_id = p_transaction_temp_id
and fm_Serial_number = l_Serial_cursor.fm_serial_number
and to_Serial_number = l_Serial_cursor.to_serial_number;
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
update mtl_Serial_numbers
set group_mark_id = null
where current_organization_id = l_serial_cursor.organization_id
and inventory_item_id = l_Serial_cursor.inventory_item_id
and serial_number between l_Serial_cursor.fm_Serial_number and l_serial_cursor.to_Serial_number;
,p_update_reservation => fnd_api.g_true);
delete_remaining_serial := TRUE;
l_new_num_update:=l_from_number+(diff_qty-((qty_count-p_qty)+1))+1;
DEBUG('l_new_num= '||l_new_num||' l_new_num_update='||l_new_num_update,'INV_TRANSFER_ORDER_PVT.adjust_serial_numbers_in_MMTT');
l_new_num_str_update:=lpad(l_new_num_update,length(l_from_number),'0');
DEBUG('l_new_num_Str= '||l_new_num_str||' l_new_num_Str_update='||l_new_num_str_update,'INV_TRANSFER_ORDER_PVT.adjust_serial_numbers_in_MMTT');
update mtl_serial_numbers_temp
set fm_Serial_number = l_Serial_cursor.fm_serial_number
,to_Serial_number = x_prefix||l_new_num_str
where fm_Serial_number = l_Serial_cursor.fm_serial_number
and to_Serial_number = l_Serial_cursor.to_serial_number
and transaction_temp_id = p_transaction_temp_id;
DEBUG('after update l_new_num_Str= '||l_new_num_str,'INV_TRANSFER_ORDER_PVT.adjust_serial_numbers_in_MMTT');
--set a flag to delete the subsequent records from MSNT
--unmark these serial numbers in MSN
/*** {{ R12 Enhanced reservations code changes,
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
update mtl_serial_numbers
set group_mark_id = null
where current_organization_id = l_serial_cursor.organization_id
and inventory_item_id = l_serial_cursor.inventory_item_id
and serial_number between x_prefix||l_new_num_str_update and l_Serial_cursor.to_serial_number;
(from_serial_number => x_prefix||l_new_num_str_update
,to_serial_number => l_serial_cursor.to_serial_number
,serial_code => null
,hdr_id => null
,p_inventory_item_id => l_serial_cursor.inventory_item_id
,p_update_reservation => fnd_api.g_true);
delete_remaining_serial := TRUE;
DEBUG('after update fm_Serial_num= '||l_Serial_cursor.fm_serial_number||' to_Ser_num= '||l_serial_cursor.to_serial_number,'INV_TRANSFER_ORDER_PVT.adjust_serial_numbers_in_MMTT');
delete from mtl_serial_numbers_temp
where fm_Serial_number = l_Serial_cursor.fm_serial_number
and to_serial_number = l_serial_cursor.to_serial_number
and transaction_temp_id = p_transaction_Temp_id;
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
update mtl_serial_numbers
set group_mark_id = null
where current_organization_id = l_serial_cursor.organization_id
and inventory_item_id = l_serial_cursor.inventory_item_id
and serial_number = l_Serial_cursor.fm_serial_number;
,p_update_reservation => fnd_api.g_true);
delete_remaining_serial := TRUE;
if (delete_remaining_serial and l_counter > 1 and NOT been_here) then
IF (l_debug = 1) THEN
DEBUG('After update fm_Serial_num= '||l_Serial_cursor.fm_serial_number||' to_Ser_num= '||l_serial_cursor.to_serial_number,'INV_TRANSFER_ORDER_PVT.adjust_serial_numbers_in_MMTT');
delete from mtl_serial_numbers_temp
where fm_Serial_number = l_Serial_cursor.fm_serial_number
and to_serial_number = l_serial_cursor.to_serial_number
and transaction_temp_id = p_transaction_Temp_id;
*** call serial_check.inv_unmark_rsv_serial instead of update msn directly
update mtl_serial_numbers
set group_mark_id = null
where current_organization_id = l_serial_cursor.organization_id
and inventory_item_id = l_serial_cursor.inventory_item_id
and serial_number = l_Serial_cursor.to_serial_number;
,p_update_reservation => fnd_api.g_true);