[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE proc_insert_update_task
(p_action IN VARCHAR2
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_temp_id IN NUMBER
,p_new_temp_id IN NUMBER
,p_merge_temp_id IN NUMBER
,p_task_status IN NUMBER
,p_user_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2);
PROCEDURE proc_insert_update_mmtt
(p_action IN VARCHAR2
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_transaction_temp_id_to_merge IN NUMBER
,p_lpn_id IN NUMBER
,p_content_lpn_id IN NUMBER
,p_transfer_lpn_id IN NUMBER
,p_confirmed_sub IN VARCHAR2
,p_confirmed_locator_id IN NUMBER
,p_confirmed_uom IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_confirmed_trx_qty IN NUMBER
,p_confirmed_lots IN VARCHAR2
,p_confirmed_lot_trx_qty IN VARCHAR2
,p_confirmed_sec_uom IN VARCHAR2
,p_confirmed_sec_qty IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_container_item_id IN NUMBER
,p_wms_task_status IN NUMBER
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_serial_allocated_flag IN VARCHAR2
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,p_exception IN VARCHAR2 -- OVER/SHORT
,p_parent_lpn_id IN NUMBER
,x_new_transaction_temp_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2);
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_new_transaction_temp_id IN NUMBER
,p_transaction_temp_id_to_merge IN NUMBER
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_confirmed_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_confirmed_lots IN VARCHAR2
,p_confirmed_lot_trx_qty IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_serial_allocated_flag IN VARCHAR2
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_confirmed_sec_uom IN VARCHAR2
,p_confirmed_sec_qty IN VARCHAR2
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,p_exception IN VARCHAR2 -- OVER/SHORT
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2);
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_new_transaction_temp_id IN NUMBER
,p_transaction_temp_id_to_merge IN NUMBER
,p_serial_transaction_temp_id IN NUMBER
,p_mtlt_serial_temp_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_confirmed_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_serial_lot_number IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_serial_allocated_flag IN VARCHAR2
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2);
PROCEDURE proc_insert_mtlt
(p_lot_record IN mtl_transaction_lots_temp%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2);
PROCEDURE proc_insert_msnt
(p_transaction_temp_id IN NUMBER
,p_organization_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_serial_number IN VARCHAR2
,p_lpn_id IN NUMBER
,p_serial_lot_number IN VARCHAR2
,p_user_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2);
PROCEDURE update_loaded_part
(p_user_id IN NUMBER,
p_transaction_temp_id1 IN NUMBER,
p_transaction_temp_id2 IN NUMBER,
p_transfer_lpn_id IN NUMBER,
p_transaction_uom IN VARCHAR2,
p_transaction_quantity IN NUMBER,
p_lot_numbers IN VARCHAR2,
p_lot_transaction_quantity IN VARCHAR2,
p_secondary_uom IN VARCHAR2,
p_secondary_quantity IN VARCHAR2,
p_serial_numbers IN VARCHAR2,
p_serial_allocated_flag IN VARCHAR2, -- Y/N
p_lot_controlled IN VARCHAR2, -- Y/N
p_serial_controlled IN VARCHAR2, -- Y/N
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_serial_transaction_temp_id NUMBER;
update_mtlt BOOLEAN,
delete_mtlt BOOLEAN);
delete_msnt BOOLEAN);
SELECT inventory_item_id, transaction_temp_id, primary_quantity
, item_primary_uom_code, transaction_quantity, transaction_uom
, secondary_transaction_quantity, secondary_uom_code
FROM mtl_material_transactions_temp
WHERE transaction_temp_id IN (v_transaction_temp_id1, v_transaction_temp_id2)
FOR UPDATE;
SELECT lot_number, primary_quantity, transaction_quantity, secondary_quantity
, serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = v_transaction_temp_id
ORDER BY lot_number
FOR UPDATE;
SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = v_transaction_temp_id
ORDER BY fm_serial_number
FOR UPDATE;
SELECT
transaction_temp_id,
lot_number,
serial_number,
SUM(transaction_quantity) transaction_quantity,
SUM(primary_quantity) primary_quantity,
SUM(secondary_quantity) secondary_quantity
FROM mtl_allocations_gtmp
WHERE (lot_number IS NOT NULL OR serial_number IS NOT NULL)
GROUP BY transaction_temp_id, lot_number, serial_number, secondary_quantity
ORDER BY transaction_temp_id, lot_number, serial_number;
mydebug('Inside UPDATE_LOADED_PART', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Conversion Factor: ' || l_conversion_factor, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Conversion Factor1: ' || l_conversion_factor1, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Conversion Factor2: ' || l_conversion_factor2, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
fnd_message.set_token('ROUTINE', '- proc_insert_update_task' );
mydebug('Return Status from Lot Serial Parse: ' || x_return_status, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Loaded...', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || ' Lot Number: ' || mtlt_record.lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Confirmed...', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Marking Lot Number: ' || l_lot_table1(i).lot_number || ' to be deleted', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
l_lot_table1(i).delete_mtlt := TRUE;
mydebug(i || ' Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Marking Lot Number: ' || l_lot_table1(i).lot_number || ' to be updated', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
l_lot_table1(i).update_mtlt := TRUE;
l_lot_table1(i).delete_mtlt := TRUE;
SELECT serial_transaction_temp_id
INTO l_serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id2
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Lot Number: ' || l_lot_table1(i).lot_number || ' exists in remaining', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Lot Number: ' || l_lot_table1(i).lot_number || ' does not exist in remaining', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Delta: ' || l_lot_table1(i).delta_primary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Sec Delta: ' || l_lot_table1(i).delta_secondary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
IF l_lot_table1(i).delete_mtlt THEN
IF l_row_exists = 1 THEN
IF (l_debug = 1) THEN
mydebug(i || 'Deleting Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
transaction_quantity = transaction_quantity +
Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
l_g_decimal_precision),
secondary_quantity = secondary_quantity + l_lot_table1(i).delta_secondary_quantity,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id2
AND lot_number = l_lot_table1(i).lot_number;
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Transferring Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_transaction_temp_id2,
transaction_quantity = Round(primary_quantity * l_conversion_factor2, l_g_decimal_precision),
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
ELSIF l_lot_table1(i).update_mtlt THEN
UPDATE mtl_transaction_lots_temp
SET primary_quantity = l_lot_table1(i).primary_quantity,
transaction_quantity = Round(l_lot_table1(i).primary_quantity * l_conversion_factor, l_g_decimal_precision),
secondary_quantity = l_lot_table1(i).secondary_quantity,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Updating Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
transaction_quantity = transaction_quantity + Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
l_g_decimal_precision),
secondary_quantity = secondary_quantity + l_lot_table1(i).delta_secondary_quantity,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id2
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Inserting Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
SELECT *
INTO l_mtlt_rec
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
proc_insert_mtlt
(p_lot_record => l_mtlt_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END IF; -- delete/update mtlt
mydebug('Loaded...', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(j || ' Serial Number: ' || msnt_record.fm_serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Confirmed...', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
l_serial_table1(j).delete_msnt := TRUE;
mydebug(j || ' Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
l_serial_table1(j).delete_msnt := TRUE;
IF l_serial_table1(j).delete_msnt THEN
IF p_serial_allocated_flag = 'Y' THEN
IF (l_debug = 1) THEN
mydebug(j || 'Transferring Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = p_transaction_temp_id2,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id1
AND fm_serial_number = l_serial_table1(j).serial_number;
mydebug(j || 'Deleting Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id1
AND fm_serial_number = l_serial_table1(j).serial_number;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE serial_number = l_serial_table1(j).serial_number
AND inventory_item_id = l_inventory_item_id;
mydebug('Loaded...', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || ' Lot Number: ' || mtlt_record.lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(j || ' Serial Number: ' || msnt_record.fm_serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Confirmed...', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || ' Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(j || 'Marking Serial Number: ' || l_serial_table1(j).serial_number || ' to be deleted', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
l_serial_table1(j).delete_msnt := TRUE;
l_lot_table1(i).update_mtlt := TRUE;
l_lot_table1(i).delete_mtlt := TRUE;
mydebug(i || 'Marking Lot Number: ' || l_lot_table1(i).lot_number || ' to be deleted', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Marking Lot Number: ' || l_lot_table1(i).lot_number || ' to be updated', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Incrementing i', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(j || ' Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(j || 'Marking Serial Number: ' || l_serial_table1(j).serial_number || ' to be deleted', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
l_serial_table1(j).delete_msnt := TRUE;
l_lot_table1(i).update_mtlt := TRUE;
l_lot_table1(i).delete_mtlt := TRUE;
mydebug(i || 'Marking Lot Number: ' || l_lot_table1(i).lot_number || ' to be deleted', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Marking Lot Number: ' || l_lot_table1(i).lot_number || ' to be updated', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Incrementing i', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
SELECT serial_transaction_temp_id
INTO l_serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id2
AND lot_number = l_lot_table1(i).lot_number;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_transaction_temp_id
FROM dual;
mydebug(i || 'Lot Number: ' || l_lot_table1(i).lot_number || ' exists in remaining', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug(i || 'Lot Number: ' || l_lot_table1(i).lot_number || ' does not exist in remaining', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
IF l_serial_table1(j).delete_msnt THEN
IF p_serial_allocated_flag = 'Y' THEN
IF (l_debug = 1) THEN
mydebug(j || 'Transferring Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_serial_transaction_temp_id,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id
AND fm_serial_number = l_serial_table1(j).serial_number;
mydebug(j || 'Deleting Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id
AND fm_serial_number = l_serial_table1(j).serial_number;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE serial_number = l_serial_table1(j).serial_number
AND inventory_item_id = l_inventory_item_id;
mydebug(j || ' Serial Number: ' || l_serial_table1(j).serial_number || ' left untouched', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
IF l_lot_table1(i).delete_mtlt THEN
IF l_row_exists = 1 THEN
IF (l_debug = 1) THEN
mydebug(i || 'Deleting Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
transaction_quantity = transaction_quantity +
Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
l_g_decimal_precision),
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id2
AND lot_number = l_lot_table1(i).lot_number;
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Transferring Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_transaction_temp_id2,
serial_transaction_temp_id = l_serial_transaction_temp_id,
transaction_quantity = Round(primary_quantity * l_conversion_factor2, l_g_decimal_precision),
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
ELSIF l_lot_table1(i).update_mtlt THEN
UPDATE mtl_transaction_lots_temp
SET primary_quantity = l_lot_table1(i).primary_quantity,
transaction_quantity = Round(l_lot_table1(i).primary_quantity * l_conversion_factor, l_g_decimal_precision),
secondary_quantity = l_lot_table1(i).secondary_quantity,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Updating Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
transaction_quantity = transaction_quantity + Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
l_g_decimal_precision),
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id2
AND lot_number = l_lot_table1(i).lot_number;
mydebug(i || 'Inserting Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
SELECT *
INTO l_mtlt_rec
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id1
AND lot_number = l_lot_table1(i).lot_number;
proc_insert_mtlt
(p_lot_record => l_mtlt_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
mydebug('Return status from insert MTLT : ' || x_return_status, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
END IF; -- delete/update mtlt
mydebug('Updating MMTT with delta qty: ' || l_delta_primary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Updating MMTT with delta sec qty: ' || l_delta_secondary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
UPDATE mtl_material_transactions_temp
SET transaction_quantity = Round(l_primary_quantity*l_conversion_factor, l_g_decimal_precision),
transaction_uom = p_transaction_uom,
primary_quantity = l_primary_quantity,
secondary_transaction_quantity = l_secondary_quantity,
secondary_uom_code = p_secondary_uom,
lpn_id = content_lpn_id,
content_lpn_id = NULL,
transfer_lpn_id = p_transfer_lpn_id,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id1
returning lpn_id INTO l_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 1
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_lpn_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity + Round(l_delta_primary_quantity*l_conversion_factor2, l_g_decimal_precision),
primary_quantity = primary_quantity + l_delta_primary_quantity,
secondary_transaction_quantity = secondary_transaction_quantity+l_delta_secondary_quantity,
last_update_date = Sysdate,
last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id2;
mydebug('Error', 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
mydebug('Error: ' || Sqlerrm, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
END update_loaded_part;
p_action IN VARCHAR2 -- LOAD_MULTIPLE/LOAD_SINGLE/SPLIT/UPDATE_LOADED
,p_exception IN VARCHAR2 -- SHORT/OVER
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_parent_line_id IN NUMBER
,p_remaining_temp_id IN NUMBER
,p_lpn_id IN NUMBER
,p_content_lpn_id IN NUMBER
,p_transfer_lpn_id IN NUMBER
,p_confirmed_sub IN VARCHAR2
,p_confirmed_locator_id IN NUMBER
,p_confirmed_uom IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_confirmed_trx_qty IN NUMBER
,p_confirmed_lots IN VARCHAR2
,p_confirmed_lot_trx_qty IN VARCHAR2
,p_confirmed_sec_uom IN VARCHAR2
,p_confirmed_sec_qty IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_container_item_id IN NUMBER
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_serial_allocated_flag IN VARCHAR2
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,p_parent_lpn_id IN NUMBER
,x_new_transaction_temp_id OUT NOCOPY NUMBER
,x_cms_check OUT NOCOPY VARCHAR2 -- FAIL/PASS
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
l_insert VARCHAR2(2) := NULL;
l_update VARCHAR2(2) := NULL;
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id <> p_transaction_temp_id
AND inventory_item_id = p_inventory_item_id
AND nvl(revision,'@@') = nvl(p_revision,'@@')
AND subinventory_code = p_confirmed_sub
AND locator_id = p_confirmed_locator_id
AND transaction_uom = p_confirmed_uom
AND transfer_lpn_id = p_transfer_lpn_id
AND nvl(content_lpn_id,0) = nvl(p_content_lpn_id,0)
AND nvl(lpn_id,0) = nvl(p_lpn_id,0);
IF p_action = 'UPDATE_LOADED' THEN
update_loaded_part
(p_user_id => p_user_id,
p_transaction_temp_id1 => p_transaction_temp_id,
p_transaction_temp_id2 => p_remaining_temp_id,
p_transfer_lpn_id => l_transfer_lpn_id,
p_transaction_uom => p_confirmed_uom,
p_transaction_quantity => p_confirmed_trx_qty,
p_lot_numbers => p_confirmed_lots,
p_lot_transaction_quantity => p_confirmed_lot_trx_qty,
p_secondary_uom => p_confirmed_sec_uom,
p_secondary_quantity => p_confirmed_sec_qty,
p_serial_numbers => p_confirmed_serials,
p_serial_allocated_flag => p_serial_allocated_flag,
p_lot_controlled => p_lot_controlled,
p_serial_controlled => p_serial_controlled,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* Update the LPN context to "Packing context" (8).
-- if p_lpn_match = 1,3 (exact match or fully consumable LPN) then
-- p_lpn_match_lpn_id will be set to Packing context(8) otherwise , we will not
-- change the context of from lpn .
-- Always set the status of p_transfer_lpn_id = Packing context(8) , whether
-- pre-generated or already in Packing context" */
--
l_progress := '200';
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_lpn_match_lpn_id
AND lpn_context = l_lpn_context_inv --, l_transfer_lpn_id)
AND organization_id = p_organization_id;
mydebug ('Cannot find LPNs to update the context' );
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_transfer_lpn_id
AND lpn_context in (l_lpn_context_packing , l_lpn_context_pregenerated)
AND organization_id = p_organization_id;
mydebug ('Cannot find LPNs to update the context' );
l_insert := 'Y'; --insert new MMTT
l_update := 'Y1'; -- update original MMTT to reduce qty
l_insert := 'N'; -- do not insert new MMTT
l_update := 'Y1'; -- update original MMTT = conmfirmed qty
l_insert := 'N'; -- Do not insert new MMTT
l_update := 'Y2'; -- 2 updates. 1- original MMTT to reduce qty
mydebug('l_insert:' || l_insert || ':l_update:'|| l_update);
proc_insert_update_mmtt
(p_action => l_action
,p_insert => l_insert
,p_update => l_update
,p_organization_id => p_organization_id
,p_user_id => p_user_id
,p_transaction_header_id => p_transaction_header_id
,p_transaction_temp_id => p_transaction_temp_id
,p_transaction_temp_id_to_merge => l_transaction_temp_id_to_merge
,p_lpn_id => l_lpn_id
,p_content_lpn_id => l_content_lpn_id
,p_transfer_lpn_id => l_transfer_lpn_id
,p_confirmed_sub => p_confirmed_sub
,p_confirmed_locator_id => p_confirmed_locator_id
,p_confirmed_uom => p_confirmed_uom
,p_suggested_uom => p_suggested_uom
,p_primary_uom => p_primary_uom
,p_inventory_item_id => p_inventory_item_id
,p_revision => p_revision
,p_confirmed_trx_qty => p_confirmed_trx_qty
,p_confirmed_lots => p_confirmed_lots
,p_confirmed_lot_trx_qty => p_confirmed_lot_trx_qty
,p_confirmed_sec_uom => p_confirmed_sec_uom
,p_confirmed_sec_qty => p_confirmed_sec_qty
,p_confirmed_serials => p_confirmed_serials
,p_container_item_id => l_container_item_id
,p_lpn_match => p_lpn_match
,p_lpn_match_lpn_id => l_lpn_match_lpn_id
,p_serial_allocated_flag => p_serial_allocated_flag
,p_lot_controlled => p_lot_controlled
,p_serial_controlled => p_serial_controlled
,p_wms_task_status => l_g_task_loaded
,p_exception => p_exception
,p_parent_lpn_id => l_parent_lpn_id
,x_new_transaction_temp_id => l_new_transaction_temp_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
Action l_insert L_update update orginalTSK UpdMergeTSK InsertNewTSK
SPLIT Y Y1 N N Y
SPLIT N Y2 Y Y N
LOAD_M N Y1 Y N N
LOAD_M N Y2 Y-Delete Y N
LOAD_S N Y1 Y N N
*/
l_progress := '500';
proc_insert_update_task -- new task using p_transaction_temp_id);
,p_insert => l_insert
,p_update => l_update
,p_temp_id => p_transaction_temp_id
,p_new_temp_id => l_new_transaction_temp_id -- will be notNULL only if p_insert=Y
,p_merge_temp_id => l_transaction_temp_id_to_merge
,p_task_status => l_g_task_loaded
,p_user_id => p_user_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PROCEDURE proc_insert_update_task
(p_action IN VARCHAR2
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_temp_id IN NUMBER
,p_new_temp_id IN NUMBER
,p_merge_temp_id IN NUMBER
,p_task_status IN NUMBER
,p_user_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(30) := 'proc_insert_update_task';
mydebug(l_proc_name || ': Before Insert into WMSDT');
mydebug ('p_insert = ' || p_insert);
mydebug ('p_update = ' || p_update);
IF p_insert = 'Y' THEN
INSERT INTO wms_dispatched_tasks
(task_id
,transaction_temp_id
,organization_id
,user_task_type
,person_id
,effective_start_date
,effective_end_date
,equipment_id
,equipment_instance
,person_resource_id
,machine_resource_id
,status
,dispatched_time
,loaded_time
,drop_off_time
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,task_type
,priority
,task_group_id
,device_id
,device_inVoked
,device_request_id
,suggested_dest_subinventory
,suggested_dest_locator_id
,operation_plan_id
,move_order_line_id
,transfer_lpn_id )
(SELECT wms_dispatched_tasks_s.NEXTVAL
,p_new_temp_id -- parameter
,organization_id
,user_task_type
,person_id
,effective_start_date
,effective_end_date
,equipment_id
,equipment_instance
,person_resource_id
,machine_resource_id
,p_task_status -- parameter
,dispatched_time
,SYSDATE
,drop_off_time
,SYSDATE
,last_updated_by
,SYSDATE
,p_user_id -- parameter
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,task_type
,priority
,task_group_id
,device_id
,device_invoked
,device_request_id
,suggested_dest_subinventory
,suggested_dest_locator_id
,operation_plan_id
,move_order_line_id
,transfer_lpn_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id);
myDebug('Error inserting a new task using WDT record for : '|| p_temp_id);
fnd_message.set_token('ROUTINE', '- proc_insert_update_task' );
IF p_update = 'Y1' THEN
l_transaction_temp_id := p_temp_id; -- update only the original task
l_transaction_temp_id := p_merge_temp_id; -- update the merged task
UPDATE wms_dispatched_tasks
SET status = p_task_status
,loaded_time = SYSDATE
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = l_transaction_temp_id;
fnd_message.set_token('ROUTINE', '- proc_insert_update_task' );
IF p_update = 'Y2' AND p_action = l_g_action_load_multiple THEN
fnd_message.set_token('ROUTINE', '- proc_insert_update_task' );
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
fnd_message.set_token('ROUTINE', '- proc_insert_update_task' );
END proc_insert_update_task;
PROCEDURE proc_insert_update_mmtt
(p_action IN VARCHAR2
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_transaction_temp_id_to_merge IN NUMBER
,p_lpn_id IN NUMBER
,p_content_lpn_id IN NUMBER
,p_transfer_lpn_id IN NUMBER
,p_confirmed_sub IN VARCHAR2
,p_confirmed_locator_id IN NUMBER
,p_confirmed_uom IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_confirmed_trx_qty IN NUMBER
,p_confirmed_lots IN VARCHAR2
,p_confirmed_lot_trx_qty IN VARCHAR2
,p_confirmed_sec_uom IN VARCHAR2
,p_confirmed_sec_qty IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_container_item_id IN NUMBER
,p_wms_task_status IN NUMBER
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_serial_allocated_flag IN VARCHAR2
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,p_exception IN VARCHAR2 -- SHORT/OVER
,p_parent_lpn_id IN NUMBER
,x_new_transaction_temp_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(30) := 'PROC_INSERT_UPDATE_MMTT';
mydebug ('p_insert = ' || p_insert );
mydebug ('p_update = ' || p_update );
IF p_insert = 'Y' THEN
l_progress := '110';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_transaction_temp_id
FROM DUAL;
INSERT INTO mtl_material_transactions_temp
( TRANSACTION_HEADER_ID
,TRANSACTION_TEMP_ID
,SOURCE_CODE
,SOURCE_LINE_ID
,TRANSACTION_MODE
,LOCK_FLAG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,INVENTORY_ITEM_ID
,REVISION
,ORGANIZATION_ID
,SUBINVENTORY_CODE
,LOCATOR_ID
,TRANSACTION_QUANTITY
,PRIMARY_QUANTITY
,TRANSACTION_UOM
,TRANSACTION_COST
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_NAME
,TRANSACTION_DATE
,ACCT_PERIOD_ID
,DISTRIBUTION_ACCOUNT_ID
,TRANSACTION_REFERENCE
,REQUISITION_LINE_ID
,REQUISITION_DISTRIBUTION_ID
,REASON_ID
,LOT_NUMBER
,LOT_EXPIRATION_DATE
,SERIAL_NUMBER
,RECEIVING_DOCUMENT
,DEMAND_ID
,RCV_TRANSACTION_ID
,MOVE_TRANSACTION_ID
,COMPLETION_TRANSACTION_ID
,WIP_ENTITY_TYPE
,SCHEDULE_ID
,REPETITIVE_LINE_ID
,EMPLOYEE_CODE
,PRIMARY_SWITCH
,SCHEDULE_UPDATE_CODE
,SETUP_TEARDOWN_CODE
,ITEM_ORDERING
,NEGATIVE_REQ_FLAG
,OPERATION_SEQ_NUM
,PICKING_LINE_ID
,TRX_SOURCE_LINE_ID
,TRX_SOURCE_DELIVERY_ID
,PHYSICAL_ADJUSTMENT_ID
,CYCLE_COUNT_ID
,RMA_LINE_ID
,CUSTOMER_SHIP_ID
,CURRENCY_CODE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_DATE
,USSGL_TRANSACTION_CODE
,VENDOR_LOT_NUMBER
,ENCUMBRANCE_ACCOUNT
,ENCUMBRANCE_AMOUNT
,SHIP_TO_LOCATION
,SHIPMENT_NUMBER
,TRANSFER_COST
,TRANSPORTATION_COST
,TRANSPORTATION_ACCOUNT
,FREIGHT_CODE
,CONTAINERS
,WAYBILL_AIRBILL
,EXPECTED_ARRIVAL_DATE
,TRANSFER_SUBINVENTORY
,TRANSFER_ORGANIZATION
,TRANSFER_TO_LOCATION
,NEW_AVERAGE_COST
,VALUE_CHANGE
,PERCENTAGE_CHANGE
,MATERIAL_ALLOCATION_TEMP_ID
,DEMAND_SOURCE_HEADER_ID
,DEMAND_SOURCE_LINE
,DEMAND_SOURCE_DELIVERY
,ITEM_SEGMENTS
,ITEM_DESCRIPTION
,ITEM_TRX_ENABLED_FLAG
,ITEM_LOCATION_CONTROL_CODE
,ITEM_RESTRICT_SUBINV_CODE
,ITEM_RESTRICT_LOCATORS_CODE
,ITEM_REVISION_QTY_CONTROL_CODE
,ITEM_PRIMARY_UOM_CODE
,ITEM_UOM_CLASS
,ITEM_SHELF_LIFE_CODE
,ITEM_SHELF_LIFE_DAYS
,ITEM_LOT_CONTROL_CODE
,ITEM_SERIAL_CONTROL_CODE
,ITEM_INVENTORY_ASSET_FLAG
,ALLOWED_UNITS_LOOKUP_CODE
,DEPARTMENT_ID
,DEPARTMENT_CODE
,WIP_SUPPLY_TYPE
,SUPPLY_SUBINVENTORY
,SUPPLY_LOCATOR_ID
,VALID_SUBINVENTORY_FLAG
,VALID_LOCATOR_FLAG
,LOCATOR_SEGMENTS
,CURRENT_LOCATOR_CONTROL_CODE
,NUMBER_OF_LOTS_ENTERED
,WIP_COMMIT_FLAG
,NEXT_LOT_NUMBER
,LOT_ALPHA_PREFIX
,NEXT_SERIAL_NUMBER
,SERIAL_ALPHA_PREFIX
,SHIPPABLE_FLAG
,POSTING_FLAG
,REQUIRED_FLAG
,PROCESS_FLAG
,ERROR_CODE
,ERROR_EXPLANATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,MOVEMENT_ID
,RESERVATION_QUANTITY
,SHIPPED_QUANTITY
,TRANSACTION_LINE_NUMBER
,TASK_ID
,TO_TASK_ID
,SOURCE_TASK_ID
,PROJECT_ID
,SOURCE_PROJECT_ID
,PA_EXPENDITURE_ORG_ID
,TO_PROJECT_ID
,EXPENDITURE_TYPE
,FINAL_COMPLETION_FLAG
,TRANSFER_PERCENTAGE
,TRANSACTION_SEQUENCE_ID
,MATERIAL_ACCOUNT
,MATERIAL_OVERHEAD_ACCOUNT
,RESOURCE_ACCOUNT
,OUTSIDE_PROCESSING_ACCOUNT
,OVERHEAD_ACCOUNT
,FLOW_SCHEDULE
,COST_GROUP_ID
,TRANSFER_COST_GROUP_ID
,DEMAND_CLASS
,QA_COLLECTION_ID
,KANBAN_CARD_ID
,OVERCOMPLETION_TRANSACTION_QTY
,OVERCOMPLETION_PRIMARY_QTY
,OVERCOMPLETION_TRANSACTION_ID
,END_ITEM_UNIT_NUMBER
,SCHEDULED_PAYBACK_DATE
,LINE_TYPE_CODE
,PARENT_TRANSACTION_TEMP_ID
,PUT_AWAY_STRATEGY_ID
,PUT_AWAY_RULE_ID
,PICK_STRATEGY_ID
,PICK_RULE_ID
,MOVE_ORDER_LINE_ID
,TASK_GROUP_ID
,PICK_SLIP_NUMBER
,RESERVATION_ID
,COMMON_BOM_SEQ_ID
,COMMON_ROUTING_SEQ_ID
,ORG_COST_GROUP_ID
,COST_TYPE_ID
,TRANSACTION_STATUS
,STANDARD_OPERATION_ID
,TASK_PRIORITY
,WMS_TASK_TYPE
,PARENT_LINE_ID
,LPN_ID
,TRANSFER_LPN_ID
,WMS_TASK_STATUS
,CONTENT_LPN_ID
,CONTAINER_ITEM_ID
,CARTONIZATION_ID
,PICK_SLIP_DATE
,REBUILD_ITEM_ID
,REBUILD_SERIAL_NUMBER
,REBUILD_ACTIVITY_ID
,REBUILD_JOB_NAME
,ORGANIZATION_TYPE
,TRANSFER_ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,XFR_OWNING_ORGANIZATION_ID
,TRANSFER_OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,XFR_PLANNING_ORGANIZATION_ID
,TRANSFER_PLANNING_TP_TYPE
,SECONDARY_UOM_CODE
,SECONDARY_TRANSACTION_QUANTITY
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,ALLOCATED_LPN_ID
,SCHEDULE_NUMBER
,SCHEDULED_FLAG
,CLASS_CODE
,SCHEDULE_GROUP
,BUILD_SEQUENCE
,BOM_REVISION
,ROUTING_REVISION
,BOM_REVISION_DATE
,ROUTING_REVISION_DATE
,ALTERNATE_BOM_DESIGNATOR
,ALTERNATE_ROUTING_DESIGNATOR
,OPERATION_PLAN_ID
,INTRANSIT_ACCOUNT
,FOB_POINT
,MOVE_ORDER_HEADER_ID
,SERIAL_ALLOCATED_FLAG
)
(SELECT
TRANSACTION_HEADER_ID
,l_new_transaction_temp_id
,SOURCE_CODE
,SOURCE_LINE_ID
,TRANSACTION_MODE
,LOCK_FLAG
,SYSDATE -- it should not copy from original MMTT
,p_user_id -- it should not copy from original MMTT
,SYSDATE
,p_user_id
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,INVENTORY_ITEM_ID
,REVISION
,ORGANIZATION_ID
,p_confirmed_sub
,p_confirmed_locator_id
,p_confirmed_trx_qty
,l_confirmed_prim_qty
,nvl(p_confirmed_uom, item_primary_uom_code)
,TRANSACTION_COST
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_NAME
,TRANSACTION_DATE
,ACCT_PERIOD_ID
,DISTRIBUTION_ACCOUNT_ID
,TRANSACTION_REFERENCE
,REQUISITION_LINE_ID
,REQUISITION_DISTRIBUTION_ID
,REASON_ID
,LOT_NUMBER
,LOT_EXPIRATION_DATE
,SERIAL_NUMBER
,RECEIVING_DOCUMENT
,DEMAND_ID
,RCV_TRANSACTION_ID
,MOVE_TRANSACTION_ID
,COMPLETION_TRANSACTION_ID
,WIP_ENTITY_TYPE
,SCHEDULE_ID
,REPETITIVE_LINE_ID
,EMPLOYEE_CODE
,PRIMARY_SWITCH
,SCHEDULE_UPDATE_CODE
,SETUP_TEARDOWN_CODE
,ITEM_ORDERING
,NEGATIVE_REQ_FLAG
,OPERATION_SEQ_NUM
,PICKING_LINE_ID
,TRX_SOURCE_LINE_ID
,TRX_SOURCE_DELIVERY_ID
,PHYSICAL_ADJUSTMENT_ID
,CYCLE_COUNT_ID
,RMA_LINE_ID
,CUSTOMER_SHIP_ID
,CURRENCY_CODE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_DATE
,USSGL_TRANSACTION_CODE
,VENDOR_LOT_NUMBER
,ENCUMBRANCE_ACCOUNT
,ENCUMBRANCE_AMOUNT
,SHIP_TO_LOCATION
,SHIPMENT_NUMBER
,TRANSFER_COST
,TRANSPORTATION_COST
,TRANSPORTATION_ACCOUNT
,FREIGHT_CODE
,CONTAINERS
,WAYBILL_AIRBILL
,EXPECTED_ARRIVAL_DATE
,TRANSFER_SUBINVENTORY
,TRANSFER_ORGANIZATION
,TRANSFER_TO_LOCATION
,NEW_AVERAGE_COST
,VALUE_CHANGE
,PERCENTAGE_CHANGE
,MATERIAL_ALLOCATION_TEMP_ID
,DEMAND_SOURCE_HEADER_ID
,DEMAND_SOURCE_LINE
,DEMAND_SOURCE_DELIVERY
,ITEM_SEGMENTS
,ITEM_DESCRIPTION
,ITEM_TRX_ENABLED_FLAG
,ITEM_LOCATION_CONTROL_CODE
,ITEM_RESTRICT_SUBINV_CODE
,ITEM_RESTRICT_LOCATORS_CODE
,ITEM_REVISION_QTY_CONTROL_CODE
,ITEM_PRIMARY_UOM_CODE
,ITEM_UOM_CLASS
,ITEM_SHELF_LIFE_CODE
,ITEM_SHELF_LIFE_DAYS
,ITEM_LOT_CONTROL_CODE
,ITEM_SERIAL_CONTROL_CODE
,ITEM_INVENTORY_ASSET_FLAG
,ALLOWED_UNITS_LOOKUP_CODE
,DEPARTMENT_ID
,DEPARTMENT_CODE
,WIP_SUPPLY_TYPE
,SUPPLY_SUBINVENTORY
,SUPPLY_LOCATOR_ID
,VALID_SUBINVENTORY_FLAG
,VALID_LOCATOR_FLAG
,LOCATOR_SEGMENTS
,CURRENT_LOCATOR_CONTROL_CODE
,NUMBER_OF_LOTS_ENTERED
,WIP_COMMIT_FLAG
,NEXT_LOT_NUMBER
,LOT_ALPHA_PREFIX
,NEXT_SERIAL_NUMBER
,SERIAL_ALPHA_PREFIX
,SHIPPABLE_FLAG
,POSTING_FLAG
,REQUIRED_FLAG
,PROCESS_FLAG
,ERROR_CODE
,ERROR_EXPLANATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,MOVEMENT_ID
,RESERVATION_QUANTITY
,SHIPPED_QUANTITY
,TRANSACTION_LINE_NUMBER
,TASK_ID
,TO_TASK_ID
,SOURCE_TASK_ID
,PROJECT_ID
,SOURCE_PROJECT_ID
,PA_EXPENDITURE_ORG_ID
,TO_PROJECT_ID
,EXPENDITURE_TYPE
,FINAL_COMPLETION_FLAG
,TRANSFER_PERCENTAGE
,TRANSACTION_SEQUENCE_ID
,MATERIAL_ACCOUNT
,MATERIAL_OVERHEAD_ACCOUNT
,RESOURCE_ACCOUNT
,OUTSIDE_PROCESSING_ACCOUNT
,OVERHEAD_ACCOUNT
,FLOW_SCHEDULE
,COST_GROUP_ID
,TRANSFER_COST_GROUP_ID
,DEMAND_CLASS
,QA_COLLECTION_ID
,KANBAN_CARD_ID
,OVERCOMPLETION_TRANSACTION_QTY
,OVERCOMPLETION_PRIMARY_QTY
,OVERCOMPLETION_TRANSACTION_ID
,END_ITEM_UNIT_NUMBER
,SCHEDULED_PAYBACK_DATE
,LINE_TYPE_CODE
,PARENT_TRANSACTION_TEMP_ID
,PUT_AWAY_STRATEGY_ID
,PUT_AWAY_RULE_ID
,PICK_STRATEGY_ID
,PICK_RULE_ID
,MOVE_ORDER_LINE_ID
,TASK_GROUP_ID
,PICK_SLIP_NUMBER
,reservation_id
,COMMON_BOM_SEQ_ID
,COMMON_ROUTING_SEQ_ID
,ORG_COST_GROUP_ID
,COST_TYPE_ID
,TRANSACTION_STATUS
,STANDARD_OPERATION_ID
,TASK_PRIORITY
,WMS_TASK_TYPE
,decode(PARENT_LINE_ID, NULL,NULL,l_new_transaction_temp_id) -- Take care of BULK parent
,nvl(p_lpn_id,p_parent_lpn_id) -- process the nesting
-- fully consumble LPN Pick
,p_transfer_lpn_id
-- Bug4185621: instead of inheriting previous line's status, use loaded as status for new line
, p_wms_task_status -- wms_task_status
,p_content_lpn_id
,nvl(p_container_item_id,container_item_id)
,CARTONIZATION_ID --??
,PICK_SLIP_DATE
,REBUILD_ITEM_ID
,REBUILD_SERIAL_NUMBER
,REBUILD_ACTIVITY_ID
,REBUILD_JOB_NAME
,ORGANIZATION_TYPE
,TRANSFER_ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,XFR_OWNING_ORGANIZATION_ID
,TRANSFER_OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,XFR_PLANNING_ORGANIZATION_ID
,TRANSFER_PLANNING_TP_TYPE
,p_confirmed_sec_uom
,decode( p_confirmed_sec_uom, null, null, l_confirmed_sec_qty )-- Bug 4576653
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,ALLOCATED_LPN_ID
,SCHEDULE_NUMBER
,SCHEDULED_FLAG
,CLASS_CODE
,SCHEDULE_GROUP
,BUILD_SEQUENCE
,BOM_REVISION
,ROUTING_REVISION
,BOM_REVISION_DATE
,ROUTING_REVISION_DATE
,ALTERNATE_BOM_DESIGNATOR
,ALTERNATE_ROUTING_DESIGNATOR
,OPERATION_PLAN_ID
,INTRANSIT_ACCOUNT
,FOB_POINT
,MOVE_ORDER_HEADER_ID
,SERIAL_ALLOCATED_FLAG
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id);
fnd_message.set_name('WMS', 'WMS_INSERT_ALLOCATION'); -- NEWMSG
END IF ; -- insert MMTT only if p_insert = 'Y'
,p_insert => p_insert
,p_update => p_update
,p_organization_id => p_organization_id
,p_user_id => p_user_id
,p_transaction_header_id => p_transaction_header_id
,p_transaction_temp_id => p_transaction_temp_id
,p_new_transaction_temp_id => l_new_transaction_temp_id
,p_transaction_temp_id_to_merge => p_transaction_temp_id_to_merge
,p_inventory_item_id => p_inventory_item_id
,p_revision => p_revision
,p_suggested_uom => p_suggested_uom
,p_confirmed_uom => p_confirmed_uom
,p_primary_uom => p_primary_uom
,p_confirmed_lots => p_confirmed_lots
,p_confirmed_lot_trx_qty => p_confirmed_lot_trx_qty
,p_confirmed_serials => p_confirmed_serials
,p_serial_allocated_flag => p_serial_allocated_flag
,p_lpn_match => p_lpn_match
,p_lpn_match_lpn_id => p_lpn_match_lpn_id
,p_confirmed_sec_uom => p_confirmed_sec_uom
,p_confirmed_sec_qty => p_confirmed_sec_qty
,p_lot_controlled => p_lot_controlled
,p_serial_controlled => p_serial_controlled
,p_exception => p_exception
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,p_insert => p_insert
,p_update => p_update
,p_organization_id => p_organization_id
,p_user_id => p_user_id
,p_transaction_header_id => p_transaction_header_id
,p_transaction_temp_id => p_transaction_temp_id
,p_new_transaction_temp_id => l_new_transaction_temp_id
,p_transaction_temp_id_to_merge => p_transaction_temp_id_to_merge
,p_serial_transaction_temp_id => NULL
,p_mtlt_serial_temp_id => NULL
,p_inventory_item_id => p_inventory_item_id
,p_revision => p_revision
,p_suggested_uom => p_suggested_uom
,p_confirmed_uom => p_confirmed_uom
,p_primary_uom => p_primary_uom
,p_serial_lot_number => NULL
,p_confirmed_serials => p_confirmed_serials
,p_serial_allocated_flag => p_serial_allocated_flag
,p_lpn_match => p_lpn_match
,p_lpn_match_lpn_id => p_lpn_match_lpn_id
,p_lot_controlled => p_lot_controlled
,p_serial_controlled => p_serial_controlled
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IF p_update = 'Y1' -- update p_transaction_temp_id to reduce primary/trxqty
THEN
IF p_action = l_g_action_split THEN
l_progress := '190';
select sum(primary_quantity),sum(transaction_quantity),sum(SECONDARY_QUANTITY)
into l_rem_lot_pri_qty,l_rem_lot_trx_qty,l_rem_lot_sec_qty
from mtl_transaction_lots_temp
where transaction_temp_id = p_transaction_temp_id
group by transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_rem_lot_trx_qty
, primary_quantity = l_rem_lot_pri_qty
, secondary_transaction_quantity = l_rem_lot_sec_qty
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_confirmed_sugg_qty
, primary_quantity = primary_quantity - l_confirmed_prim_qty
, secondary_transaction_quantity = secondary_transaction_quantity - l_confirmed_sec_qty
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = p_confirmed_trx_qty
, primary_quantity = l_confirmed_prim_qty
, secondary_transaction_quantity = decode( p_confirmed_sec_uom,
null,
null,
l_confirmed_sec_qty
) -- Bug 4576653
, secondary_uom_code = p_confirmed_sec_uom
, lpn_id = nvl(p_lpn_id,p_parent_lpn_id)
-- process the nesting
-- fully consumble LPN Pick
, content_lpn_id = p_content_lpn_id
, transfer_lpn_id = p_transfer_lpn_id
, subinventory_code = p_confirmed_sub
, locator_id = p_confirmed_locator_id
, transaction_uom = p_confirmed_uom
, container_item_id = p_container_item_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
, wms_task_status = p_wms_task_status -- Bug4185621: update mmtt task status to loaded
WHERE transaction_temp_id = p_transaction_temp_id;
IF p_update = 'Y2' -- -- and update p_transaction_temp_id_to_merge to add qty)
THEN
l_progress := '190';
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity + p_confirmed_trx_qty
, primary_quantity = primary_quantity + nvl(l_confirmed_prim_qty ,0)
, secondary_transaction_quantity = secondary_transaction_quantity + nvl(l_confirmed_sec_qty, 0)
, secondary_uom_code = p_confirmed_sec_uom
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id_to_merge;
l_progress := '190'; -- Delete the original MMTT, if merging into another MMTT
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_confirmed_sugg_qty
, primary_quantity = primary_quantity - l_confirmed_prim_qty
, secondary_transaction_quantity = secondary_transaction_quantity - l_confirmed_sec_qty
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id;
END proc_insert_update_mmtt;
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_new_transaction_temp_id IN NUMBER
,p_transaction_temp_id_to_merge IN NUMBER
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_confirmed_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_confirmed_lots IN VARCHAR2
,p_confirmed_lot_trx_qty IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_serial_allocated_flag IN VARCHAR2
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_confirmed_sec_uom IN VARCHAR2
,p_confirmed_sec_qty IN VARCHAR2
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,p_exception IN VARCHAR2 -- SHORT/OVER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(30) := 'PROC_PROCESS_CONFIRMED_LOTS';
SELECT mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE transaction_temp_id = p_lot_transaction_temp_id
AND lot_number = p_lot_number;
SELECT DISTINCT -- so that we get only lot records in case of lot+Serial item control
lot_number
,transaction_temp_id
,serial_number
,transaction_quantity
,primary_quantity
,suggested_quantity
,secondary_quantity
FROM mtl_allocations_gtmp
ORDER BY
transaction_temp_id
,lot_number;
Action l_insert L_update update orginalMMTT UpdMergeMMTT InsertNewMMTT
-----------------------------------------------------------------------------------------------
SPLIT Y Y1 N N Y
SPLIT N Y2 Y Y N
LOAD_M N Y1 Y N N
LOAD_M N Y2 Y-Delete Y N
LOAD_S N Y1 Y N N
****MTLT ****
Action l_insert L_update update orginalMTLT UpdMergeMTLT
-----------------------------------------------------------------------------------------------
SPLIT Y Y1 Y-upd original OR N
-ins new/upd orig
SPLIT N Y2 Y Y if MTLT exist
OR ins new/upd orig
LOAD_M N Y1 N N
LOAD_M N Y2 Y Y if MTLT exist
OR ins new/upd orig
LOAD_S N Y1 N-not necessary N
*/
x_return_status := l_g_ret_sts_success;
mydebug ('p_insert = ' || p_insert );
mydebug ('p_update = ' || p_update );
IF p_insert = 'Y' or p_update = 'Y2' THEN
-- we need this only if we ever need to create a new MTLT
FOR rec_mtlt_to_copy_from IN cur_mtlt_to_copy_from
(p_lot_number => rec_confirmed_lots_serials.lot_number,
p_lot_transaction_temp_id => p_transaction_temp_id)
LOOP
l_progress := '150';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_transaction_temp_id
FROM DUAL;
mydebug ('p_insert: ' || p_insert);
IF p_insert = 'Y' THEN
l_progress := '190';
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_new_transaction_temp_id
, transaction_quantity = rec_confirmed_lots_serials.transaction_Quantity
, primary_quantity = rec_confirmed_lots_serials.primary_quantity
, secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
, secondary_unit_of_measure = p_confirmed_sec_uom
, serial_transaction_temp_id = l_serial_transaction_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = l_rec_mtlt_to_copy_from.transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
proc_insert_mtlt
(p_lot_record => l_rec_mtlt_to_copy_from
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity - rec_confirmed_lots_serials.suggested_quantity
,primary_quantity = primary_quantity - rec_confirmed_lots_serials.primary_quantity
,secondary_quantity = NVL(secondary_quantity, rec_confirmed_lots_serials.secondary_quantity) - rec_confirmed_lots_serials.secondary_quantity
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
mydebug ('p_update: ' || p_update);
IF p_update = 'Y2'
THEN
l_progress := '240';
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity +
rec_confirmed_lots_serials.transaction_Quantity
,primary_quantity = primary_quantity + rec_confirmed_lots_serials.primary_quantity
,secondary_quantity = secondary_quantity+ rec_confirmed_lots_serials.secondary_quantity
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id_to_merge
AND lot_number = rec_confirmed_lots_serials.lot_number
-- For lot + serial controlled items
RETURNING serial_transaction_temp_id INTO l_serial_transaction_temp_id;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_transaction_temp_id_to_merge --p_new_transaction_temp_id
, secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
, secondary_unit_of_measure = p_confirmed_sec_uom
-- For lot + serial controlled items
, serial_transaction_temp_id = l_serial_transaction_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = l_rec_mtlt_to_copy_from.transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
proc_insert_mtlt
(p_lot_record => l_rec_mtlt_to_copy_from
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity -
rec_confirmed_lots_serials.suggested_Quantity
,primary_quantity = primary_quantity -
rec_confirmed_lots_serials.primary_quantity
,secondary_quantity = NVL(secondary_quantity, rec_confirmed_lots_serials.secondary_quantity) - rec_confirmed_lots_serials.secondary_quantity
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id -- l_rec_mtlt_to_copy_from.transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id -- l_rec_mtlt_to_copy_from.transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity - rec_confirmed_lots_serials.suggested_Quantity
,primary_quantity = primary_quantity - rec_confirmed_lots_serials.primary_quantity
,secondary_quantity = NVL(secondary_quantity, rec_confirmed_lots_serials.secondary_quantity) -
rec_confirmed_lots_serials.secondary_quantity
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id -- l_rec_mtlt_to_copy_from.transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
/* Usually for p_update = 'Y1' one does not need to update MTLT since nothing would have changed
But, for catch weight enabled items, it is necessary */
/* If serials are not allocateed and lpn_match = 1/3 , MSNT records need to be populated
so, update serial_transaction_temp_id mtlt*. MSNT gets created in process_confirmed_serials */
IF (p_update = 'Y1' AND
p_insert = 'N' AND
p_serial_controlled = 'Y' AND
p_serial_allocated_flag = 'N' AND
p_confirmed_serials IS NULL AND
p_lpn_match IN (1,3) )
THEN
l_progress := '350';
UPDATE mtl_transaction_lots_temp
SET
transaction_quantity = rec_confirmed_lots_serials.transaction_quantity --jxlu
,primary_quantity = rec_confirmed_lots_serials.primary_quantity --jxlu
,secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
,secondary_unit_of_measure = p_confirmed_sec_uom
,serial_transaction_temp_id = l_serial_transaction_temp_id
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
/* Usually for p_update = 'Y1' one does not need to update MTLT since nothing would have changed
But, for catch weight enabled items, Overpicking or changed TXN-UOM it is necessary
*/
/* following condition is independent of the above condition in that :
* for serial controlled items, UOM cannot be different from primary
* UOM (atleast as of this patchset (11.5.10)) .
* for lpn_match 1,3 , it cannot be a case of overpick. */
IF (p_update = 'Y1' AND
p_insert = 'N' ) AND
(p_confirmed_sec_qty is NOT NULL OR
p_confirmed_uom <> p_primary_uom OR
p_exception = 'OVER')
THEN
l_progress := '360';
UPDATE mtl_transaction_lots_temp
SET
transaction_quantity = rec_confirmed_lots_serials.transaction_quantity
,primary_quantity = rec_confirmed_lots_serials.primary_quantity
,secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
,secondary_unit_of_measure = p_confirmed_sec_uom
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = rec_confirmed_lots_serials.lot_number;
UPDATE mtl_allocations_gtmp
SET child_transaction_temp_id = l_serial_transaction_temp_id
WHERE lot_number = rec_confirmed_lots_serials.lot_number
AND transaction_temp_id = rec_confirmed_lots_serials.transaction_temp_id ;
,p_insert => p_insert
,p_update => p_update
,p_organization_id => p_organization_id
,p_user_id => p_user_id
,p_transaction_header_id => p_transaction_header_id
,p_transaction_temp_id => p_transaction_temp_id
,p_new_transaction_temp_id => p_new_transaction_temp_id --??l_serial_transaction_temp_id
,p_transaction_temp_id_to_merge => p_transaction_temp_id_to_merge
,p_serial_transaction_temp_id => l_serial_transaction_temp_id
,p_mtlt_serial_temp_id => l_mtlt_serial_temp_id
,p_inventory_item_id => p_inventory_item_id
,p_revision => p_revision
,p_suggested_uom => p_suggested_uom
,p_confirmed_uom => p_confirmed_uom
,p_primary_uom => p_primary_uom
,p_serial_lot_number => rec_confirmed_lots_serials.lot_number
,p_confirmed_serials => p_confirmed_serials
,p_serial_allocated_flag => p_serial_allocated_flag
,p_lpn_match => p_lpn_match
,p_lpn_match_lpn_id => p_lpn_match_lpn_id
,p_lot_controlled => p_lot_controlled
,p_serial_controlled => p_serial_controlled
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,p_insert IN VARCHAR2
,p_update IN VARCHAR2
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_new_transaction_temp_id IN NUMBER
,p_transaction_temp_id_to_merge IN NUMBER
,p_serial_transaction_temp_id IN NUMBER
,p_mtlt_serial_temp_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_suggested_uom IN VARCHAR2
,p_confirmed_uom IN VARCHAR2
,p_primary_uom IN VARCHAR2
,p_serial_lot_number IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_serial_allocated_flag IN VARCHAR2
,p_lpn_match IN NUMBER
,p_lpn_match_lpn_id IN NUMBER
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(30) := 'PROC_PROCESS_CONFIRMED_SERIALS';
SELECT transaction_temp_id
,lot_number
,serial_number
,transaction_quantity
,primary_quantity
FROM mtl_allocations_gtmp
WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
ORDER BY
transaction_temp_id
--,nvl(lot_number,'@@')
,serial_number;
Action l_insert L_update update orginalMMTT UpdMergeMMTT InsertNewMMTT
-----------------------------------------------------------------------------------------------
SPLIT Y Y1 N N Y
SPLIT N Y2 Y Y N
LOAD_M N Y1 Y N N
LOAD_M N Y2 Y-Delete Y N
LOAD_S N Y1 Y N N
****MSNT ****
Action l_insert L_update update orginalMSNT
-----------------------------------------------------------------------------------------------
SPLIT Y Y1 Y-set temp_id = new temp_id
SPLIT N Y2 Y-set temp_id= merge temp_id
LOAD_M N Y1 N
LOAD_M N Y2 Y-set temp_id= merge temp_id
LOAD_S N Y1 N-not necessary
*/
mydebug ('In : ' || l_proc_name);
mydebug ('p_insert = ' || p_insert );
mydebug ('p_update = ' || p_update );
IF (p_insert = 'Y' ) THEN
l_n_msnt_transaction_temp_id := p_new_transaction_temp_id;
IF p_update = 'Y2' THEN
l_n_msnt_transaction_temp_id := p_transaction_temp_id_to_merge;
IF (p_update = 'Y1' ) THEN
l_n_msnt_transaction_temp_id := p_transaction_temp_id; -- original MMTT
(p_insert = 'Y' OR p_update = 'Y2')
THEN
-- AND ( p_serial_allocated_flag = 'Y'))
/* If serials are allocted then MSNT records will be associated with p_transaction_temp_id.
If serials are not allocated but confirmed_serials is not null that means Java-UI created MSNT
records and associated them with p_transaction_temp_id. there fore, for a case of SPLIT
(leading to Merge or split) these MSNT records have to be moved to the confirmed_mmtt created.
identified by l_n_msnt_transaction_temp_id */
l_progress := '110';
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_n_msnt_transaction_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
AND fm_serial_number IN
(SELECT serial_number
FROM mtl_allocations_gtmp
WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
mydebug('msnt not updateed..');
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = l_n_msnt_transaction_temp_id
,last_update_date= SYSDATE
,last_updated_by = p_user_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND group_mark_id IS NULL
AND serial_number IN
(SELECT serial_number
FROM mtl_allocations_gtmp
WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
proc_insert_msnt (p_transaction_temp_id => l_n_msnt_transaction_temp_id
,p_organization_id => p_organization_id
,p_inventory_item_id => p_inventory_item_id
,p_revision => p_revision
,p_confirmed_serials => p_confirmed_serials
,p_serial_number => NULL
,p_lpn_id => p_lpn_match_lpn_id -- NULL if lpn_match!=(1,3)
,p_serial_lot_number => p_serial_lot_number
,p_user_id => p_user_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
(p_insert = 'Y' OR p_update = 'Y2')
THEN
l_progress := '200';
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_n_msnt_transaction_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
AND fm_serial_number IN
(SELECT serial_number
FROM mtl_serial_numbers msn
,mtl_serial_numbers_temp msnt
WHERE msn.serial_number = msnt.fm_serial_number
AND msnt.transaction_temp_id = l_o_msnt_transaction_temp_id
AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
AND msn.lpn_id = p_lpn_match_lpn_id);
mydebug('msnt not updateed..');
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = l_n_msnt_transaction_temp_id
,last_update_date= SYSDATE
,last_updated_by = p_user_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number IN
(SELECT serial_number
FROM mtl_serial_numbers msn
,mtl_serial_numbers_temp msnt
WHERE msn.serial_number = msnt.fm_serial_number
AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
AND msn.lpn_id = p_lpn_match_lpn_id);
PROCEDURE proc_insert_mtlt
( p_lot_record IN mtl_transaction_lots_temp%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(30) := 'PROC_INSERT_MTLT';
INSERT INTO mtl_transaction_lots_temp
(transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,transaction_quantity
,primary_quantity
,lot_number
,lot_expiration_date
,error_code
,serial_transaction_temp_id
,group_header_id
,put_away_rule_id
,pick_rule_id
,description
,vendor_id
,supplier_lot_number
,territory_code
,origination_date
,date_code
,grade_code
,change_date
,maturity_date
,status_id
,retest_date
,age
,item_size
,color
,volume
,volume_uom
,place_of_origin
,best_by_date
,length
,length_uom
,recycled_content
,thickness
,thickness_uom
,width
,width_uom
,curl_wrinkle_fold
,lot_attribute_category
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,vendor_name
,sublot_num
,secondary_quantity
,secondary_unit_of_measure
,qc_grade
,reason_code
,product_code
,product_transaction_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15)
VALUES (
p_lot_record.transaction_temp_id
,p_lot_record.last_update_date
,p_lot_record.last_updated_by
,SYSDATE
,p_lot_record.created_by
,p_lot_record.last_update_login
,p_lot_record.request_id
,p_lot_record.program_application_id
,p_lot_record.program_id
,SYSDATE
,p_lot_record.transaction_quantity
,p_lot_record.primary_quantity
,p_lot_record.lot_number
,p_lot_record.lot_expiration_date
,p_lot_record.error_code
,p_lot_record.serial_transaction_temp_id
,p_lot_record.group_header_id
,p_lot_record.put_away_rule_id
,p_lot_record.pick_rule_id
,p_lot_record.description
,p_lot_record.vendor_id
,p_lot_record.supplier_lot_number
,p_lot_record.territory_code
,p_lot_record.origination_date
,p_lot_record.date_code
,p_lot_record.grade_code
,p_lot_record.change_date
,p_lot_record.maturity_date
,p_lot_record.status_id
,p_lot_record.retest_date
,p_lot_record.age
,p_lot_record.item_size
,p_lot_record.color
,p_lot_record.volume
,p_lot_record.volume_uom
,p_lot_record.place_of_origin
,p_lot_record.best_by_date
,p_lot_record.length
,p_lot_record.length_uom
,p_lot_record.recycled_content
,p_lot_record.thickness
,p_lot_record.thickness_uom
,p_lot_record.width
,p_lot_record.width_uom
,p_lot_record.curl_wrinkle_fold
,p_lot_record.lot_attribute_category
,p_lot_record.c_attribute1
,p_lot_record.c_attribute2
,p_lot_record.c_attribute3
,p_lot_record.c_attribute4
,p_lot_record.c_attribute5
,p_lot_record.c_attribute6
,p_lot_record.c_attribute7
,p_lot_record.c_attribute8
,p_lot_record.c_attribute9
,p_lot_record.c_attribute10
,p_lot_record.c_attribute11
,p_lot_record.c_attribute12
,p_lot_record.c_attribute13
,p_lot_record.c_attribute14
,p_lot_record.c_attribute15
,p_lot_record.c_attribute16
,p_lot_record.c_attribute17
,p_lot_record.c_attribute18
,p_lot_record.c_attribute19
,p_lot_record.c_attribute20
,p_lot_record.d_attribute1
,p_lot_record.d_attribute2
,p_lot_record.d_attribute3
,p_lot_record.d_attribute4
,p_lot_record.d_attribute5
,p_lot_record.d_attribute6
,p_lot_record.d_attribute7
,p_lot_record.d_attribute8
,p_lot_record.d_attribute9
,p_lot_record.d_attribute10
,p_lot_record.n_attribute1
,p_lot_record.n_attribute2
,p_lot_record.n_attribute3
,p_lot_record.n_attribute4
,p_lot_record.n_attribute5
,p_lot_record.n_attribute6
,p_lot_record.n_attribute7
,p_lot_record.n_attribute8
,p_lot_record.n_attribute9
,p_lot_record.n_attribute10
,p_lot_record.vendor_name
,p_lot_record.sublot_num
,p_lot_record.secondary_quantity
,p_lot_record.secondary_unit_of_measure
,p_lot_record.qc_grade
,p_lot_record.reason_code
,p_lot_record.product_code
,p_lot_record.product_transaction_id
,p_lot_record.attribute_category
,p_lot_record.attribute1
,p_lot_record.attribute2
,p_lot_record.attribute3
,p_lot_record.attribute4
,p_lot_record.attribute5
,p_lot_record.attribute6
,p_lot_record.attribute7
,p_lot_record.attribute8
,p_lot_record.attribute9
,p_lot_record.attribute10
,p_lot_record.attribute11
,p_lot_record.attribute12
,p_lot_record.attribute13
,p_lot_record.attribute14
,p_lot_record.attribute15);
END proc_insert_mtlt;
PROCEDURE proc_insert_msnt
(p_transaction_temp_id IN NUMBER
,p_organization_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_revision IN VARCHAR2
,p_confirmed_serials IN VARCHAR2
,p_serial_number IN VARCHAR2
,p_lpn_id IN NUMBER
,p_serial_lot_number IN VARCHAR2
,p_user_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
--p_transaction_temp_id := transaction_temp_id of the new MSNT
l_proc_name VARCHAR2(30) := 'PROC_INSERT_MSNT';
INSERT INTO mtl_serial_numbers_temp
(transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,vendor_serial_number
,vendor_lot_number
,fm_serial_number
,to_serial_number
,serial_prefix
,error_code
,parent_serial_number
,group_header_id
,end_item_unit_number
,serial_attribute_category
,territory_code
,origination_date
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,status_id
,time_since_new
,cycles_since_new
,time_since_overhaul
,cycles_since_overhaul
,time_since_repair
,cycles_since_repair
,time_since_visit
,cycles_since_visit
,time_since_mark
,cycles_since_mark
,number_of_repairs
,product_code
,product_transaction_id )
(SELECT
p_transaction_temp_id
,SYSDATE
,-1
,SYSDATE
,p_user_id
,msn.last_update_login
,msn.request_id
,msn.program_application_id
,msn.program_id
,msn.program_update_date
,msn.vendor_serial_number
,msn.vendor_lot_number
,msn.serial_number
,msn.serial_number
,NVL(l_serial_prefix, 1)
,NULL -- error code
,msn.parent_serial_number
,NULL --group_header_id
,msn.end_item_unit_number
,msn.serial_attribute_category
,msn.territory_code
,msn.origination_date
,msn.c_attribute1
,msn.c_attribute2
,msn.c_attribute3
,msn.c_attribute4
,msn.c_attribute5
,msn.c_attribute6
,msn.c_attribute7
,msn.c_attribute8
,msn.c_attribute9
,msn.c_attribute10
,msn.c_attribute11
,msn.c_attribute12
,msn.c_attribute13
,msn.c_attribute14
,msn.c_attribute15
,msn.c_attribute16
,msn.c_attribute17
,msn.c_attribute18
,msn.c_attribute19
,msn.c_attribute20
,msn.d_attribute1
,msn.d_attribute2
,msn.d_attribute3
,msn.d_attribute4
,msn.d_attribute5
,msn.d_attribute6
,msn.d_attribute7
,msn.d_attribute8
,msn.d_attribute9
,msn.d_attribute10
,msn.n_attribute1
,msn.n_attribute2
,msn.n_attribute3
,msn.n_attribute4
,msn.n_attribute5
,msn.n_attribute6
,msn.n_attribute7
,msn.n_attribute8
,msn.n_attribute9
,msn.n_attribute10
,msn.status_id
,msn.time_since_new
,msn.cycles_since_new
,msn.time_since_overhaul
,msn.cycles_since_overhaul
,msn.time_since_repair
,msn.cycles_since_repair
,msn.time_since_visit
,msn.cycles_since_visit
,msn.time_since_mark
,msn.cycles_since_mark
,msn.number_of_repairs
,NULL --product_code
,NULL --product_transaction_id
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id
AND lpn_id = p_lpn_id
AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
INSERT INTO mtl_serial_numbers_temp
(transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,vendor_serial_number
,vendor_lot_number
,fm_serial_number
,to_serial_number
,serial_prefix
,error_code
,parent_serial_number
,group_header_id
,end_item_unit_number
,serial_attribute_category
,territory_code
,origination_date
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,status_id
,time_since_new
,cycles_since_new
,time_since_overhaul
,cycles_since_overhaul
,time_since_repair
,cycles_since_repair
,time_since_visit
,cycles_since_visit
,time_since_mark
,cycles_since_mark
,number_of_repairs
,product_code
,product_transaction_id )
(SELECT
p_transaction_temp_id
,SYSDATE
,-1
,SYSDATE
,p_user_id
,msn.last_update_login
,msn.request_id
,msn.program_application_id
,msn.program_id
,msn.program_update_date
,msn.vendor_serial_number
,msn.vendor_lot_number
,msn.serial_number
,msn.serial_number
,NVL(l_serial_prefix, 1)
,NULL -- error code
,msn.parent_serial_number
,NULL --group_header_id
,msn.end_item_unit_number
,msn.serial_attribute_category
,msn.territory_code
,msn.origination_date
,msn.c_attribute1
,msn.c_attribute2
,msn.c_attribute3
,msn.c_attribute4
,msn.c_attribute5
,msn.c_attribute6
,msn.c_attribute7
,msn.c_attribute8
,msn.c_attribute9
,msn.c_attribute10
,msn.c_attribute11
,msn.c_attribute12
,msn.c_attribute13
,msn.c_attribute14
,msn.c_attribute15
,msn.c_attribute16
,msn.c_attribute17
,msn.c_attribute18
,msn.c_attribute19
,msn.c_attribute20
,msn.d_attribute1
,msn.d_attribute2
,msn.d_attribute3
,msn.d_attribute4
,msn.d_attribute5
,msn.d_attribute6
,msn.d_attribute7
,msn.d_attribute8
,msn.d_attribute9
,msn.d_attribute10
,msn.n_attribute1
,msn.n_attribute2
,msn.n_attribute3
,msn.n_attribute4
,msn.n_attribute5
,msn.n_attribute6
,msn.n_attribute7
,msn.n_attribute8
,msn.n_attribute9
,msn.n_attribute10
,msn.status_id
,msn.time_since_new
,msn.cycles_since_new
,msn.time_since_overhaul
,msn.cycles_since_overhaul
,msn.time_since_repair
,msn.cycles_since_repair
,msn.time_since_visit
,msn.cycles_since_visit
,msn.time_since_mark
,msn.cycles_since_mark
,msn.number_of_repairs
,NULL --product_code
,NULL --product_transaction_id
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id
AND nvl(lpn_id,0) = nvl(p_lpn_id,0)
AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
--AND group_mark_id IS NULL
AND msn.serial_number IN
(SELECT serial_number
FROM mtl_allocations_gtmp
WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'))
);
END proc_insert_msnt ;
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = p_group_mark_id
, last_updated_by = p_user_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lpn_id = p_lpn_id ;
UPDATE MTL_SERIAL_NUMBERS msn
SET group_mark_id = p_group_mark_id
, last_updated_by = p_user_id
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id
AND nvl(lpn_id,0) = nvl(p_lpn_id,0)
AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
--AND group_mark_id IS NULL
AND msn.serial_number IN
(SELECT serial_number
FROM mtl_allocations_gtmp
WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = p_group_mark_id
, last_updated_by = p_user_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND nvl(lpn_id,0) = nvl(p_lpn_id,0)
AND DECODE(p_serial_number,NULL,'@@',serial_number) = nvl(p_serial_number,'@@') ;
* newly inserted/updated details, the rollback has to be done manually.
*/
PROCEDURE process_F2(
p_action IN VARCHAR2 -- NULL, CMS
,p_organization_id IN NUMBER
,p_user_id IN NUMBER
,p_employee_id IN NUMBER
,p_transaction_header_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_original_sub IN VARCHAR2
,p_original_locator_id IN NUMBER
,p_lot_controlled IN VARCHAR2 -- Y/N
,p_serial_controlled IN VARCHAR2 -- Y/N
,p_serial_allocated_flag IN VARCHAR2 -- Y/N
,p_suggested_uom IN VARCHAR2 -- original allocation UOM
,p_start_over IN VARCHAR2 -- Y/N start_over
,p_retain_task IN VARCHAR2 -- Y/N for bug 4310093
,x_start_over_taskno OUT NOCOPY NUMBER -- start_over task
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
l_op_msnt_to_delete NUMBER := 0;
SELECT sum(primary_quantity) mmtt_primary_quantity
-- bug #4141928 INV CONV
, sum(secondary_transaction_quantity) mmtt_secondary_quantity
, COUNT(*) mmtt_group_count
, MIN(transaction_temp_id) group_temp_id
, MIN(parent_line_id) parent_line_id -- Bug#4185621
, inventory_item_id
, revision
, subinventory_code
, locator_id
, item_primary_uom_code
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
GROUP BY
inventory_item_id
,revision
,subinventory_code
,locator_id
,item_primary_uom_code;
SELECT sum(mtlt.primary_quantity) group_lot_primary_quantity
-- bug #4141928 INV CONV
, sum(mtlt.secondary_quantity) group_lot_secondary_quantity
,COUNT(*) group_lot_count
,MIN(mtlt.transaction_temp_id) group_lot_temp_id
,mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_locator_id
AND mmtt.item_primary_uom_code = p_uom_code
AND mmtt.inventory_item_id = p_inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(p_revision,'@@')
GROUP BY
mtlt.lot_number;
CURSOR cur_msnt_to_delete ( p_rec_mmtt1_subinventory_code VARCHAR2
,p_rec_mmtt1_locator_id NUMBER
,p_rec_mmtt1_item_primary_uom VARCHAR2
,p_rec_mmtt1_inventory_item_id NUMBER
,p_rec_mmtt1_revision VARCHAR2)
IS
SELECT msnt.transaction_temp_id
,msnt.fm_serial_number
,mmtt.organization_id
,mmtt.inventory_item_id
,msnt.creation_date
FROM mtl_serial_numbers_temp msnt
,mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND mmtt.subinventory_code = p_rec_mmtt1_subinventory_code
AND mmtt.locator_id = p_rec_mmtt1_locator_id
AND mmtt.item_primary_uom_code = p_rec_mmtt1_item_primary_uom
AND mmtt.inventory_item_id = p_rec_mmtt1_inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(p_rec_mmtt1_revision,'@@')
ORDER BY msnt.creation_date DESC;
CURSOR cur_msnt_to_delete_LS (p_serial_transaction_temp_id NUMBER)
IS
SELECT msnt.transaction_temp_id
,msnt.fm_serial_number
,msnt.creation_date
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_serial_transaction_temp_id
ORDER BY msnt.creation_date DESC;
* as done before.All task would return to pending wdd would be deleted.
*}}
*/
IF p_start_over ='N' and p_retain_task='N' THEN --bug 4310093
mydebug('viks start_over button not pressed:');
DELETE mtl_allocations_gtmp ;
INSERT
INTO mtl_allocations_gtmp
(transaction_temp_id)
VALUES ( rec_mmtt1.group_temp_id);
mydebug('Inserted temp_id into mtl_allocations_gtmp: ' || rec_mmtt1.group_temp_id);
l_parent_posting_flag := 'N'; -- bulk parent, need to update posting flag back to 'N'
/* Update the MMTT record with transacttion-temp_id = group_temp_id.
Updating transaction_quantity same as primary_quantity since
the transaction_qty should be IN same uom AS primary qty FOR this mmtt */
UPDATE mtl_material_transactions_temp
SET primary_quantity = rec_mmtt1.mmtt_primary_quantity
, transaction_quantity = l_suggested_mmtt_qty
, secondary_transaction_quantity = DECODE(secondary_transaction_quantity, NULL, NULL, l_suggested_mmtt_sec_qty)
, transaction_uom = p_suggested_uom
, transfer_lpn_id = NULL
, lpn_id = NULL
, content_lpn_id = NULL
, last_update_date = SYSDATE
, last_updated_by = p_user_id
, wms_task_status = l_g_task_pending -- Bug4185621: update mmtt task status back to pending
, posting_flag = l_parent_posting_flag -- Bug4185621: updating posting flag
WHERE transaction_temp_id = rec_mmtt1.group_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET posting_flag = 'Y'
WHERE parent_line_id = rec_mmtt1.group_temp_id
AND parent_line_id <> transaction_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_transaction_temp_id
FROM DUAL;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_serial_transaction_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND mtlt.lot_number = rec_mtlt1.lot_number
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = l_serial_transaction_temp_id
, last_updated_by = p_user_id
, last_update_date = SYSDATE
WHERE current_organization_id = p_organization_id
AND inventory_item_id = rec_mmtt1.inventory_item_id
AND serial_number IN
(SELECT fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_serial_transaction_temp_id);
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = NULL
, last_updated_by = p_user_id
, last_update_date = SYSDATE
WHERE (current_organization_id
,inventory_item_id
,serial_number)
IN (SELECT mmtt.organization_id
,mmtt.inventory_item_id
,msnt.fm_serial_number
FROM mtl_transaction_lots_temp mtlt
,mtl_serial_numbers_temp msnt
,mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND mtlt.lot_number = rec_mtlt1.lot_number
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id
IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND mtlt.lot_number = rec_mtlt1.lot_number
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
l_op_msnt_to_delete := 0;
IF p_serial_allocated_flag = 'Y' THEN -- to delete all overpicked serials
l_progress := 4000;
SELECT count(*)
INTO l_op_msnt_to_delete
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_serial_transaction_temp_id;
mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete
|| ':' || rec_mtlt1.group_lot_primary_quantity);
IF rec_mtlt1.group_lot_primary_quantity < l_op_msnt_to_delete
THEN
FOR rec_msnt_to_delete_LS IN cur_msnt_to_delete_LS
( l_serial_transaction_temp_id)
LOOP
mydebug('rec_msnt_to_delete_ls.fm_serial_number : ' || rec_msnt_to_delete_ls.fm_serial_number);
mydebug('rec_msnt_to_delete_ls.transaction_temp_id : ' || rec_msnt_to_delete_ls.transaction_temp_id);
* assumption that the overpicked serials are newly inserted MSNT records and
* they will have creation date higher than the originally allocated serials. */
IF l_op_msnt_to_delete <= rec_mtlt1.group_lot_primary_quantity
THEN
mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete);
l_op_msnt_to_delete := l_op_msnt_to_delete - 1;
mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete);
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = NULL
,last_updated_by = p_user_id
,last_update_date = SYSDATE
WHERE current_organization_id = p_organization_id
AND inventory_item_id = rec_mmtt1.inventory_item_id
AND serial_number = rec_msnt_to_delete_ls.fm_serial_number;
mydebug('No MSN found to be updated..not good' );
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = rec_msnt_to_delete_ls.transaction_temp_id
AND fm_serial_number = rec_msnt_to_delete_ls.fm_serial_number;
END IF; -- serial_allocated_flag = Y and delete overpicked serials
/* even if the # of records in MTLT for this lot is 1, it has to be updated
with serial-transaction-temp_id and user_id, sysdate.
So, there is no harm is updating qty too */
l_progress := 1500;
UPDATE MTL_transaction_lots_temp mtlt
SET transaction_temp_id = rec_mmtt1.group_temp_id
, primary_quantity = rec_mtlt1.group_lot_primary_quantity
, transaction_quantity = l_suggested_mtlt_qty
, secondary_quantity = decode (secondary_quantity, null, null, l_suggested_mtlt_sec_qty)
, mtlt.serial_transaction_temp_id = l_serial_transaction_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE lot_number = rec_mtlt1.lot_number
AND transaction_temp_id = rec_mtlt1.group_lot_temp_id;
DELETE mtl_transaction_lots_temp
WHERE lot_number = rec_mtlt1.lot_number
AND transaction_temp_id
IN
(SELECT mtlt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
,mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id <> rec_mtlt1.group_lot_temp_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = rec_mtlt1.lot_number
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND mtlt.lot_number = rec_mtlt1.lot_number
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = rec_mmtt1.group_temp_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id
IN
(SELECT msnt.transaction_temp_id
FROM mtl_serial_numbers_temp msnt,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id <> rec_mmtt1.group_temp_id
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = rec_mmtt1.group_temp_id
, last_updated_by = p_user_id
, last_update_date = SYSDATE
WHERE current_organization_id = p_organization_id
AND inventory_item_id = rec_mmtt1.inventory_item_id
AND serial_number
IN
(SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = rec_mmtt1.group_temp_id);
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = NULL
, last_updated_by = p_user_id
, last_update_date = SYSDATE
WHERE (current_organization_id
,inventory_item_id
,serial_number)
IN (SELECT mmtt.organization_id
,mmtt.inventory_item_id
,msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
,mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT msnt.transaction_temp_id
FROM mtl_serial_numbers_temp msnt,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@') );
l_op_msnt_to_delete := 0;
IF p_serial_allocated_flag = 'Y' THEN -- to delete all overpicked serials
l_progress := 3000;
SELECT count(*)
INTO l_op_msnt_to_delete
FROM mtl_serial_numbers_temp
WHERE (transaction_temp_id ,
fm_serial_number)
IN
(SELECT msnt.transaction_temp_id
,msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
,mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
AND mmtt.locator_id = rec_mmtt1.locator_id
AND mmtt.item_primary_uom_code = rec_mmtt1.item_primary_uom_code
AND mmtt.inventory_item_id = rec_mmtt1.inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@'));
mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete);
IF rec_mmtt1.mmtt_primary_quantity < l_op_msnt_to_delete
THEN
FOR rec_msnt_to_delete IN cur_msnt_to_delete
( rec_mmtt1.subinventory_code
,rec_mmtt1.locator_id
,rec_mmtt1.item_primary_uom_code
,rec_mmtt1.inventory_item_id
,rec_mmtt1.revision )
LOOP
mydebug('rec_msnt_to_delete.fm_serial_number : ' || rec_msnt_to_delete.fm_serial_number);
mydebug('rec_msnt_to_delete.transaction_temp_id : ' || rec_msnt_to_delete.transaction_temp_id);
mydebug('rec_msnt_to_delete.organization_id : ' || rec_msnt_to_delete.organization_id);
mydebug('rec_msnt_to_delete.inventory_item_id : ' || rec_msnt_to_delete.inventory_item_id);
mydebug('rec_msnt_to_delete.creation_date : ' ||
to_char(rec_msnt_to_delete.creation_date,'dd:mon-yyyy:hh24:mi:ss'));
* assumption that the overpicked serials are newly inserted MSNT records and
* they will have creation date higher than the originally allocated serials. */
IF l_op_msnt_to_delete <= rec_mmtt1.mmtt_primary_quantity
THEN
l_progress := 3200;
l_op_msnt_to_delete := l_op_msnt_to_delete - 1;
mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete);
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = NULL
,last_updated_by = p_user_id
,last_update_date = SYSDATE
WHERE current_organization_id = p_organization_id
AND inventory_item_id = rec_mmtt1.inventory_item_id
AND serial_number = rec_msnt_to_delete.fm_serial_number;
mydebug('No MSN found to be updated..not good' );
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = rec_msnt_to_delete.transaction_temp_id
AND fm_serial_number = rec_msnt_to_delete.fm_serial_number;
wms_picking_pkg.g_start_over_tempid.DELETE;
UPDATE wms_dispatched_tasks
SET status = l_g_task_dispatched
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_allocations_gtmp);
UPDATE wms_dispatched_tasks
SET device_invoked = null
-- Following two statement are commnet for bug 4560814
--task_method = NULL -- for cluster picking
-- ,task_group_id = NULL
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE person_id = p_employee_id
AND status = l_g_task_dispatched;
DELETE wms_dispatched_tasks
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id NOT IN
(SELECT transaction_temp_id
FROM mtl_allocations_gtmp));
mydebug('no extra WDTs to delete :' );
/* {{ If start over is pressed wdd would be deleted .Lines need to stay in
* status dispatched.
* }}
*/
IF p_start_over ='N' and p_retain_task='N' THEN --bug 4310093
DELETE wms_dispatched_tasks
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_allocations_gtmp)
AND status <> l_g_task_queued;
mydebug('no non queued WDTs to delete :' );
DELETE mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id NOT IN
(SELECT transaction_temp_id
FROM mtl_allocations_gtmp);
mydebug('no extra MMTTs to delete :' );
SELECT transaction_temp_id
, device_request_id
FROM wms_dispatched_tasks
WHERE person_id = p_employee_id
AND ( status <= l_g_task_dispatched OR
status = l_g_task_active) -- (<=3, OR 9 ) ;
SELECT transaction_temp_id
, device_request_id
FROM wms_dispatched_tasks
WHERE person_id = p_employee_id
AND transaction_temp_id = p_transaction_temp_id
AND device_request_id IS NOT NULL;
l_deleted_mmtt_qty NUMBER := 0;
l_deleted_mmtt_sec_qty NUMBER := 0;
SELECT mtrl.line_id
, mtrl.uom_code
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE (mmtt.transaction_temp_id = p_transaction_temp_id
-- shld add : and mmtt.mmtt.transaction_temp_id <> mmtt.parent_line_id
OR mmtt.parent_line_id = p_transaction_temp_id)
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
SELECT mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.primary_quantity
, mmtt.item_primary_uom_code
, NVL(mmtt.secondary_transaction_quantity, 0) secondary_transaction_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_mo_line_id
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id)
AND NOT EXISTS(SELECT 1
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
l_deleted_mmtt_qty := 0;
l_deleted_mmtt_sec_qty := 0;
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => rec_mmtt_to_del.transaction_temp_id);
mydebug('Not able to delete the Txn = ' || rec_mmtt_to_del.transaction_temp_id);
l_deleted_mmtt_qty := l_deleted_mmtt_qty +
INV_Convert.inv_um_convert
( item_id => rec_mmtt_to_del.inventory_item_id
,precision => 5
,from_quantity => rec_mmtt_to_del.primary_quantity
,from_unit => rec_mmtt_to_del.item_primary_uom_code
,to_unit => rec_cancelled_mols.uom_code
,from_name => NULL
,to_name => NULL);
l_deleted_mmtt_qty := l_deleted_mmtt_qty + rec_mmtt_to_del.primary_quantity;
l_deleted_mmtt_sec_qty := l_deleted_mmtt_sec_qty + rec_mmtt_to_del.secondary_transaction_quantity;
SELECT count(*)
INTO l_mmtt_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = rec_cancelled_mols.line_id
AND NOT EXISTS ( SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id);
UPDATE mtl_txn_request_lines
SET quantity_detailed =(quantity_detailed - l_deleted_mmtt_qty)
, SECONDARY_QUANTITY_DETAILED = (SECONDARY_QUANTITY_DETAILED - l_deleted_mmtt_sec_qty)
, line_status = DECODE(l_mmtt_count, 0, INV_GLOBALS.G_TO_STATUS_CLOSED, line_status)
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE line_id = rec_cancelled_mols.line_id;
SELECT DISTINCT lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND lpn_id IS NOT NULL;
SELECT DISTINCT content_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND content_lpn_id IS NOT NULL;
SELECT DISTINCT transfer_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND nvl(content_lpn_id , nvl(lpn_id,-999)) <> transfer_lpn_id;
SELECT 1
INTO l_other_tasks
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_header_id <> p_transaction_header_id
AND transfer_lpn_id = rec_transfer_lpns.transfer_lpn_id);
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_PREGENERATED
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = rec_transfer_lpns.transfer_lpn_id
AND lpn_context <> l_lpn_context_picked;
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_INV
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = rec_from_lpns.lpn_id
AND lpn_context = l_lpn_context_packing;
SELECT 1
INTO l_other_tasks
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_header_id <> p_transaction_header_id
AND content_lpn_id = rec_content_lpns.content_lpn_id);
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_inv
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = rec_content_lpns.content_lpn_id
AND lpn_context <> l_lpn_context_picked;
SELECT transaction_temp_id
,task_id
FROM wms_dispatched_tasks
WHERE person_id = p_employee_id
AND ( status = l_g_task_dispatched OR
status = l_g_task_active); -- IN (3,9 ) ;
DELETE wms_dispatched_tasks
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id);
mydebug('no WDTs to delete for this header:' );
mydebug('WDTs deleted for this header:' );
wms_picking_pkg.g_previous_task_status.delete(p_transaction_temp_id);
mydebug('l_progress = ' || l_progress || ' Update status for all temp_ids in thie header_id');
/* this update is seperate because for a pick nmore case, there can be multiple temp_ids (MMTTs)
for the given header id ...current task) */
UPDATE wms_dispatched_tasks
SET status = l_prev_task_status
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE person_id = p_employee_id
AND ( status = l_g_task_dispatched OR
status = l_g_task_active) -- IN (3,9 ) ;
AND transaction_temp_id in (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id);
mydebug('no WDT to update for this employee id for this header id with stat in ( 3,9) ' );
mydebug('l_progress = ' || l_progress || ' Update status of all other tasks in this group' );
wms_picking_pkg.g_previous_task_status.delete(rec_reset_task_status.transaction_temp_id);
UPDATE wms_dispatched_tasks
SET status = l_prev_task_status
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE task_id = rec_reset_task_status.task_id;
mydebug('task_id : ' || rec_reset_task_status.task_id || ' : not found to be updated');
DELETE wms_dispatched_tasks
WHERE person_id = p_employee_id
AND (status = l_g_task_pending OR
status = l_g_task_dispatched OR
status = l_g_task_active) ; -- IN (3,9 ) ;
mydebug('no WDT with status 3,9,1 remaining to delete for this employee id ' );
mydebug('Deleted all WDT with staus 3,9,1 for p_employee_id = ' || p_employee_id);
UPDATE wms_dispatched_tasks
SET task_method = NULL -- for cluster picking
WHERE person_id = p_employee_id
AND status = l_g_task_queued;
mydebug('no WDT to update for this employee id to be updated for cluster picking case' );
SELECT transaction_temp_id from mtl_allocations_gtmp
WHERE transaction_temp_id <> v_transaction_temp_id
ORDER BY transaction_temp_id;
SELECT count(*) into new_mmtt_count from mtl_allocations_gtmp;
wms_picking_pkg.g_start_over_tempid.DELETE(i);
UPDATE wms_dispatched_tasks
SET status = l_g_task_dispatched
,last_update_date = SYSDATE
,last_updated_by = p_user_id
WHERE transaction_temp_id = wms_picking_pkg.g_start_over_tempid(L);
mydebug('transaction_temp_id : ' ||wms_picking_pkg.g_start_over_tempid(L) ||' : not found to be updated');
mydebug('transaction_temp_id : ' ||wms_picking_pkg.g_start_over_tempid(L) ||' : not found to be updated');
DELETE mtl_allocations_gtmp ;
INSERT
INTO mtl_allocations_gtmp
(transaction_temp_id
, lot_number
, serial_number
, transaction_quantity
, primary_quantity
, suggested_quantity
, secondary_quantity)
VALUES (l_group_number
, l_lot_number
, l_serial_number
, l_lot_trx_qty
, l_lot_prim_qty
, l_lot_sugg_qty
, l_sec_qty );
INSERT
INTO mtl_allocations_gtmp
(transaction_temp_id
, lot_number
, serial_number
, transaction_quantity
, primary_quantity
, suggested_quantity
, secondary_quantity)
VALUES (l_group_number
, l_lot_number
, NULL
, l_lot_trx_qty
, l_lot_prim_qty
, l_lot_sugg_qty
, l_sec_qty );
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id;
SELECT lot_number, serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id;
CURSOR insert_serial_allocated_csr (p_serial_lot_number VARCHAR2) IS
SELECT serial_number
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_item_id
AND lpn_id = p_lpn_match_lpn_id
AND NVL(msn.lot_number,'@@') = NVL(p_serial_lot_number, '@@')
AND msn.serial_number NOT IN
( select msnt.fm_serial_number
from mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
where mmtt.inventory_item_id = p_item_id
AND mmtt.organization_id = p_organization_id
and mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
and NVL(mtlt.lot_number, '@@') = NVL(p_serial_lot_number, '@@')
and mmtt.transaction_temp_id = p_temp_id);
wms_picking_pkg.g_previous_task_status.delete(p_temp_id);
SELECT move_order_line_id
INTO l_mo_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
FOR serial_rec in insert_serial_allocated_csr(lot_rec.lot_number) LOOP
-- insert serial into msnt and mark msn
insert_serial(
p_serial_transaction_temp_id => lot_rec.serial_transaction_temp_id,
p_organization_id => p_organization_id,
p_item_id => p_item_id,
p_revision => p_revision,
p_lot => lot_rec.lot_number,
p_transaction_temp_id => p_temp_id,
p_created_by => p_user_id,
p_from_serial => serial_rec.serial_number,
p_to_serial => serial_rec.serial_number,
p_status_id => NULL,
x_return_status => l_return_status,
x_msg_data => l_msg_data);
fnd_message.set_token('ROUTINE', '-INSERT_SERIAL API - ' || p_action);
FOR serial_rec IN insert_serial_allocated_csr(NULL) LOOP
-- insert serial into msnt and mark msn
insert_serial(
p_serial_transaction_temp_id => l_transaction_temp_id,
p_organization_id => p_organization_id,
p_item_id => p_item_id,
p_revision => p_revision,
p_lot => NULL,
p_transaction_temp_id => p_temp_id,
p_created_by => p_user_id,
p_from_serial => serial_rec.serial_number,
p_to_serial => serial_rec.serial_number,
p_status_id => NULL,
x_return_status => l_return_status,
x_msg_data => l_msg_data);
fnd_message.set_token('ROUTINE', '- INSERT_SERIAL API - ');
mydebug('Should update quantity_detailed for MO Line ID: ' || l_mo_line_id);
SELECT uom_code
INTO l_mol_uom
FROM mtl_txn_request_lines
WHERE line_id = l_mo_line_id
FOR UPDATE;
SELECT ABS(SUM(primary_quantity))
INTO l_sum_mmtt_qty
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_line_id;
mydebug('update quantity_detailed in MOL with: ' || l_mmtt_qty_in_mol_uom);
UPDATE mtl_txn_request_lines
SET quantity_detailed = NVL(quantity_delivered, 0) + l_mmtt_qty_in_mol_uom
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE line_id = l_mo_line_id;
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_id
AND NVL(lot_number, -999) = NVL(p_lot, -999);
SELECT mtlt.primary_quantity
, NVL(mtlt.secondary_quantity, 0) -- Bug #4141928
, mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
ORDER BY LOT_NUMBER;
SELECT lot_number, sum(transaction_quantity) transaction_quantity
from wms_ALLOCATIONS_GTMP
GROUP BY LOT_NUMBER
ORDER BY LOT_NUMBER;
t_lpn_lot_qty_table.DELETE;
SELECT primary_uom_code
, secondary_uom_code -- Bug #4141928
, lot_control_code
, serial_number_control_code
INTO l_primary_uom
, l_secondary_uom -- Bug #4141928
, l_lot_code
, l_serial_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
select value
into l_value
from v$nls_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';
SELECT 1
, lpn_context
, parent_lpn_id
, subinventory_code
, locator_id
INTO l_lpn_exists
, l_lpn_context
, x_parent_lpn_id
, l_lpn_sub
, l_lpn_loc
FROM wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_org_id
AND wlpn.lpn_id = p_fromlpn_id;
SELECT 1
INTO l_loaded
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE (transfer_lpn_id = p_fromlpn_id
OR content_lpn_id = p_fromlpn_id));
SELECT w.subinventory_code
, inv_project.get_locsegs(w.locator_id, w.organization_id)
, w.license_plate_number
, w.locator_id
, w.lpn_context
INTO l_sub
, l_loc
, l_from_lpn
, l_loc_id
, l_lpn_context
FROM wms_license_plate_numbers w
WHERE w.lpn_id = p_fromlpn_id
AND w.locator_id IS NOT NULL;
SELECT COUNT(*)
INTO l_sub_active
FROM mtl_secondary_inventories
WHERE NVL(disable_date, SYSDATE + 1) > SYSDATE
AND organization_id = p_org_id
AND secondary_inventory_name = l_sub;
SELECT COUNT(*)
INTO l_loc_active
FROM mtl_item_locations_kfv
WHERE NVL(disable_date, SYSDATE + 1) > SYSDATE
AND organization_id = p_org_id
AND subinventory_code = l_sub
AND inventory_location_id = l_loc_id;
SELECT locator_id,organization_id,
transaction_header_id,
transaction_uom,
SECONDARY_UOM_CODE -- Bug #4141928
INTO l_locator_id, l_organization_id,
l_transaction_header_id,
l_transaction_uom,
l_sec_transaction_uom -- Bug #4141928
from mtl_material_transactions_temp
where transaction_temp_id = p_temp_id;
select nvl(project_id ,-999) , nvl(task_id ,-999)
into l_mmtt_proj_id , l_mmtt_task_id
from mtl_item_locations
where inventory_location_id = l_locator_id
and organization_id = l_organization_id ;
select nvl(project_id, -999) , nvl(task_id ,-999)
into l_mil_proj_id , l_mil_task_id
from mtl_item_locations
where inventory_location_id = l_loc_id
and organization_id = p_org_id ;
SELECT 1
INTO l_so_cnt
FROM wms_license_plate_numbers
WHERE lpn_context = 11
AND lpn_id = p_fromlpn_id
AND organization_id = p_org_id;
SELECT mmtt.transfer_subinventory
, mmtt.subinventory_code
, mmtt.locator_id
INTO l_xfr_sub_code
, l_mmtt_sub
, l_mmtt_loc
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT 1
INTO l_item_cnt
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_fromlpn_id
AND wlc.organization_id = p_org_id
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.revision, '-999') = NVL(p_rev, '-999'));
SELECT 1
INTO l_item_cnt
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_lpn_contents wlc, mtl_transaction_lots_temp mtlt
WHERE wlc.parent_lpn_id = p_fromlpn_id
AND wlc.organization_id = p_org_id
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.revision, '-999') = NVL(p_rev, '-999')
AND(mtlt.transaction_temp_id = p_temp_id
AND mtlt.lot_number = wlc.lot_number));
SELECT allocated_lpn_id
INTO l_allocated_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(DISTINCT inventory_item_id)
, COUNT(DISTINCT lot_number)
, COUNT(DISTINCT revision)
, COUNT(DISTINCT cost_group_id)
INTO l_item_cnt2
, l_lot_cnt
, l_rev_cnt
, l_cg_cnt
FROM wms_lpn_contents
WHERE parent_lpn_id = p_fromlpn_id
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_lpn_include_lpn
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_fromlpn_id
AND organization_id = p_org_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_secondary_quantity => -l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('after update qty tree for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('after update qty tree for lpn l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number);
mydebug('calling update qty tree with lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_secondary_quantity => -l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('after update qty tree without lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('after update qty tree without lpn l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number);
mydebug('calling update qty tree back without lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_secondary_quantity => l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('after update qty tree back for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('after update qty tree back for lpn l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number);
mydebug('calling update qty tree back with lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_secondary_quantity => l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('after update qty tree back without lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('after update qty tree back without lpn l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number);
mydebug('calling update qty tree back without lpn 1st time failed ');
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_pr_qty
, p_secondary_quantity => -l_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree with lpn 2nd time: l_att:' || l_att);
mydebug('update qty tree with lpn 2nd time: l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree with lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_pr_qty
, p_secondary_quantity => -l_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree without lpn 2nd time:l_att:' || l_att);
mydebug('update qty tree with lpn 2nd time: l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree back without lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_pr_qty
, p_secondary_quantity => l_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree back with lpn 2nd time: l_att:' || l_att);
mydebug('update qty tree back with lpn 2nd time: l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree with lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_pr_qty
, p_secondary_quantity => l_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree back without lpn 2nd time:l_att:' || l_att);
mydebug('update qty tree back without lpn 2nd time:l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree back without lpn 2nd time failed ');
SELECT primary_quantity, NVL(secondary_transaction_quantity, 0) -- Bug #4141928
INTO l_mmtt_qty, l_mmtt_sec_qty -- Bug #4141928
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_secondary_quantity => -l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree 3rd time for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('update qty tree 3rd time for lpn l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number); -- Bug #4141928
mydebug('calling update qty tree with lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_secondary_quantity => -l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('after update without lpn 3rd time l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('after update without lpn 3rd time l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number); -- Bug #4141928
mydebug('calling update qty tree back 3rd time without lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_secondary_quantity => l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree back 3rd time for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('update qty tree back 3rd time for lpn l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number); -- Bug #4141928
mydebug('calling update qty tree with lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_secondary_quantity => l_mtlt_secondary_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('after update qty tree back without lpn 3rd time l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('after update qty tree back without lpn 3rd time l_satt:' || l_satt || ' for lot:' || l_mtlt_lot_number); -- Bug #4141928
mydebug('calling update qty tree back without lpn 3rd time failed ');
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mmtt_qty
, p_secondary_quantity => -l_mmtt_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree with lpn 4th time: l_att:' || l_att);
mydebug('update qty tree with lpn 4th time: l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree with lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mmtt_qty
, p_secondary_quantity => -l_mmtt_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree without lpn 4th time:l_att:' || l_att);
mydebug('update qty tree without lpn 4th time:l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree without lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mmtt_qty
, p_secondary_quantity => l_mmtt_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
, p_lpn_id => p_fromlpn_id
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree back with lpn 4th time: l_att:' || l_att);
mydebug('update qty tree back with lpn 4th time: l_satt:' || l_satt); -- Bug #4141928
mydebug('calling update qty tree back with lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mmtt_qty
, p_secondary_quantity => l_mmtt_sec_qty -- Bug #4141928
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- Bug #4141928
, x_srqoh => l_srqoh -- Bug #4141928
, x_sqr => l_sqr -- Bug #4141928
, x_sqs => l_sqs -- Bug #4141928
, x_satt => l_satt -- Bug #4141928
, x_satr => l_satr -- Bug #4141928
-- , p_lpn_id => p_fromlpn_id withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('update qty tree back without lpn 4th time:l_att:' || l_att);
mydebug('update qty tree back without lpn 4th time:l_satt:' || l_satt);
mydebug('calling update qty tree back without lpn 4th time failed ');
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id;
delete from wms_allocations_gtmp;
INSERT INTO WMS_ALLOCATIONS_GTMP
(lot_number,
serial_number,
transaction_quantity,
primary_quantity)
SELECT mtlt.lot_number,fm_serial_number,1,1
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers msn
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number = msn.serial_number
AND msn.lpn_id = p_fromlpn_id
AND msn.inventory_item_id = p_item_id;
INSERT INTO WMS_ALLOCATIONS_GTMP
(serial_number,
transaction_quantity,
primary_quantity)
SELECT fm_serial_number,1,1
FROM mtl_serial_numbers_temp msnt,
mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number = msn.serial_number
AND msn.lpn_id = p_fromlpn_id
AND msn.inventory_item_id = p_item_id;
INSERT INTO wms_allocations_gtmp
(lot_number,
serial_number,
transaction_quantity,
primary_quantity)
SELECT mtlt.lot_number, serial_number, 1, 1
FROM mtl_transaction_lots_temp mtlt,
mtl_serial_numbers msn
WHERE mtlt.transaction_temp_id = p_temp_id
AND msn.lpn_id = p_fromlpn_id
AND mtlt.lot_number = msn.lot_number
AND msn.inventory_item_id = p_item_id
AND Nvl(msn.group_mark_id, -1) = -1;
INSERT INTO wms_allocations_gtmp
(serial_number,
transaction_quantity,
primary_quantity)
SELECT serial_number,1,1
FROM mtl_serial_numbers msn
WHERE msn.lpn_id = p_fromlpn_id
AND msn.inventory_item_id = p_item_id
AND Nvl(msn.group_mark_id, -1) = -1;
INSERT INTO wms_allocations_gtmp(lot_number, primary_quantity,
transaction_quantity, secondary_quantity) -- Bug #4141928
values(t_lpn_lot_qty_table(l_table_count).lot_number,
t_lpn_lot_qty_table(l_table_count).pri_qty,
t_lpn_lot_qty_table(l_table_count).trx_qty,
t_lpn_lot_qty_table(l_table_count).sec_qty
); -- Bug #4141928
SELECT NVL(SUM(primary_transaction_quantity),0), NVL(SUM(secondary_transaction_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_onhand_quantities_detail
WHERE lpn_id = p_fromlpn_id
AND organization_id = p_org_id;
* We will select this LPN for Update , so that otehr processes cannot get it.
* yes, there is a possibility that
*/
IF (x_match = 3) or (x_match = 1) THEN -- added x_match=1
IF (l_debug = 1) THEN
mydebug('Lock lpn_ID : ' || p_fromlpn_id);
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_fromlpn_id
FOR UPDATE NOWAIT;
SELECT mtlt.primary_quantity
, mtlt.transaction_quantity
, NVL(mtlt.secondary_quantity, 0) -- Bug #4141928
, mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
ORDER BY LOT_NUMBER;
SELECT lot_number, sum(transaction_quantity) transaction_quantity
from wms_ALLOCATIONS_GTMP
GROUP BY LOT_NUMBER
ORDER BY LOT_NUMBER;
SELECT transfer_subinventory
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id; -- Bug #7257709
DELETE wms_allocations_gtmp;
t_lpn_lot_qty_table.DELETE;
INSERT INTO wms_allocations_gtmp
(lot_number,
serial_number,
transaction_quantity,
primary_quantity)
SELECT mtlt.lot_number,fm_serial_number,1,1
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers msn
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number = msn.serial_number
AND msn.lpn_id is null -- make sure it is loose pick
AND msn.inventory_item_id = p_item_id;
INSERT INTO wms_ALLOCATIONS_GTMP
(serial_number,
transaction_quantity,
primary_quantity)
SELECT fm_serial_number,1,1
FROM mtl_serial_numbers_temp msnt,
mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number = msn.serial_number
AND msn.lpn_id is null
AND msn.inventory_item_id = p_item_id;
UPDATE mtl_material_transactions_temp mmtt
SET posting_flag = 'N'
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET posting_flag = 'Y'
WHERE transaction_temp_id = p_temp_id;
mydebug('inserting into global temp table for serial is non allocated....');
INSERT INTO wms_ALLOCATIONS_GTMP
(lot_number,
primary_quantity,
transaction_quantity,
secondary_quantity) -- Bug #4141928
VALUES( l_lot_number,
l_lot_primary_qty,
l_trx_lot_qty,
l_trx_lot_sec_qty);
INSERT INTO wms_ALLOCATIONS_GTMP
(lot_number,
primary_quantity,
transaction_quantity,
secondary_quantity) -- Bug #4141928
VALUES( l_lot_number,
LEAST(l_lot_primary_qty,l_att),
LEAST(l_trx_lot_qty,l_att_trx_qty),
LEAST(l_trx_lot_sec_qty,l_att_trx_sec_qty));
SELECT 'FAIL'
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND mmtt.move_order_line_id = mol.line_id
AND mol.line_status = inv_globals.g_to_status_cancel_by_source
AND ROWNUM = 1;
SELECT lpn_context
, organization_id
, locator_id
, lpn_id
, outermost_lpn_id
INTO lpn_cont
, l_org_id
, l_locator_id
, x_lpn_id
, x_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_lpn;
l_CursorStmt := 'SELECT count (*) FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta '||
'WHERE mmtt.transaction_temp_id = :x_temp_id ' ||
' AND mmtt.standard_operation_id = wutta.user_task_type_id '||
' AND mmtt.organization_id = wutta.organization_id '||
' AND wutta.honor_case_pick_flag = ''Y'' '; --Added for Bug#7584906
wsh_update_tbl WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = l_lpn_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = l_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, mtl_material_transactions_temp mmtt
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = p_organization_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = l_lpn_id
AND wdd.organization_id = p_organization_id
AND wdd.released_status = 'X'; -- For LPN reusability ER : 6845650
SELECT NVL(mil.project_id, -1)
, NVL(mil.task_id, -1)
FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
WHERE mil.inventory_location_id = mmtt.transfer_to_location
AND mil.organization_id = mmtt.organization_id
AND mmtt.transfer_lpn_id = p_pick_to_lpn_id
AND mmtt.organization_id = p_organization_id;
SELECT NVL(mil.project_id, -1)
, NVL(mil.task_id, -1)
FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
WHERE mil.inventory_location_id = mmtt.transfer_to_location
AND mil.organization_id = mmtt.organization_id
AND mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT mol.carton_grouping_id
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = mol.organization_id
AND mmtt.move_order_line_id = mol.line_id;
SELECT DISTINCT mol.carton_grouping_id
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.organization_id = mol.organization_id
AND mmtt.move_order_line_id = mol.line_id;
SELECT distinct wda.delivery_id
FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
WHERE wdd.lpn_id IN (select lpn_id from wms_license_plate_numbers
where organization_id = p_organization_id
and (lpn_id = p_lpn_id
or parent_lpn_id = p_lpn_id
or outermost_lpn_id = p_lpn_id))
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.released_status = 'X'; -- For LPN reusability ER : 6845650
SELECT wlpn.LICENSE_PLATE_NUMBER
INTO l_lpn_name
FROM Wms_License_Plate_Numbers wlpn
WHERE organization_id = p_organization_id
and (lpn_id = pick_to_lpn_rec.lpn_id
or parent_lpn_id = pick_to_lpn_rec.lpn_id
or outermost_lpn_id = pick_to_lpn_rec.lpn_id);
SELECT wdd.released_status,wdd.delivery_detail_id
INTO l_status_code,l_delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE wdd.container_name = l_lpn_name
AND wdd.released_status = 'X'; -- For LPN reusability ER : 6845650
2. Replace API call to wsh_container_grp.update_container
with new API call WSH_WMS_LPN_GRP.Create_Update_Containers
*/
IF l_status_code = 'C' THEN
l_wsh_dd_upd_rec.delivery_detail_id := l_delivery_detail_id;
wsh_update_tbl(1) := l_wsh_dd_upd_rec;
l_IN_rec.action_code := 'UPDATE_NULL';
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_update_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
SELECT nvl(inventory_item_id, -999)
INTO l_container_item_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_pick_to_lpn
AND organization_id = p_organization_id
AND lpn_context IN (wms_container_pub.lpn_context_packing, wms_container_pub.LPN_CONTEXT_PREGENERATED);
SELECT CONCATENATED_SEGMENTS
INTO l_concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = l_container_item_id
AND organization_id = p_organization_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
WHERE lpn_id = pick_to_lpn_rec.lpn_id;
SELECT mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.inventory_item_id
, mmtt.operation_plan_id
, nvl(mmtt.parent_line_id,-1)
, mmtt.transaction_header_id
INTO l_xfr_sub
, l_xfr_to_location
, l_item_id
, l_operation_plan_id
, l_parent_line_id
, l_transaction_header_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
select 'N'
into l_multiple_pick
from dual
where exists (select 1
from mtl_material_transactions_temp
where transfer_lpn_id = pick_to_lpn_rec.lpn_id
and transaction_header_id <>l_transaction_header_id);
select 'Y'
into l_bulk_task_exist
from dual
where exists (select 1
from mtl_material_transactions_temp
where transfer_lpn_id = pick_to_lpn_rec.lpn_id
and transaction_temp_id = parent_line_id -- bulk task
);
SELECT mtrh.move_order_type
, mmtt.transaction_type_id
, mmtt.wip_entity_type
INTO l_mmtt_mo_type
, l_mmtt_txn_type_id
, l_mmtt_wip_entity_type
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT mtrh.move_order_type
, mmtt.wip_entity_type
INTO l_mo_type_in_lpn
, l_wip_entity_type_in_lpn
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND ROWNUM < 2;
SELECT mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.inventory_item_id
, mmtt.operation_plan_id
INTO l_xfr_sub
, l_xfr_to_location
, l_item_id
, l_operation_plan_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id; */
SELECT lpn_controlled_flag
INTO l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = l_xfr_sub;
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND ( NVL(mmtt.transfer_subinventory, 0) <> l_xfr_sub
OR
NVL(mmtt.transfer_to_location, 0) <> l_xfr_to_location
);
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND msi.organization_id = p_organization_id
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.lpn_controlled_flag = wms_globals.g_lpn_controlled_sub
);
SELECT COUNT(1)
INTO l_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND mmtt.operation_plan_id <> l_operation_plan_id;
SELECT wdd.delivery_detail_id INTO l_line_rows(1)
FROM wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id;
SELECT wdd.delivery_detail_id INTO l_line_rows(2)
FROM wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND rownum<2;
SELECT mmtt.transaction_type_id
, mmtt.organization_id
, mmtt.inventory_item_id
INTO l_transaction_type_id
, l_org_id
, l_item_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
PROCEDURE insert_serial(
p_serial_transaction_temp_id IN OUT NOCOPY NUMBER,
p_organization_id IN NUMBER,
p_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_lot IN VARCHAR2,
p_transaction_temp_id IN NUMBER,
p_created_by IN NUMBER,
p_from_serial IN VARCHAR2,
p_to_serial IN VARCHAR2,
p_status_id IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
mydebug('Enter insert_serial: 10:'|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SELECT 1
INTO l_count
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE (p_from_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
OR p_to_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
AND mmtt.inventory_item_id = p_item_id
AND mmtt.organization_id = p_organization_id
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id);
SELECT serial_transaction_temp_id
INTO p_serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number= p_lot;
SELECT mtl_material_transactions_s.NEXTVAL
INTO p_serial_transaction_temp_id
FROM DUAL;
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = p_serial_transaction_temp_id
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number= p_lot;
l_return := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => p_serial_transaction_temp_id,
p_user_id => p_created_by,
p_fm_ser_num => p_from_serial,
p_to_ser_num => p_to_serial,
p_status_id => p_status_id,
x_proc_msg => x_msg_data
);
UPDATE mtl_serial_numbers
SET group_mark_id = p_serial_transaction_temp_id
WHERE inventory_item_id = p_item_id
AND serial_number BETWEEN p_from_serial AND p_to_serial
AND LENGTH(serial_number) = LENGTH(p_from_serial);
mydebug('Insert serial vals'|| p_item_id || ':' || p_from_serial || ':' || p_to_serial, 4);
mydebug('Insert serial, inserted with '|| p_serial_transaction_temp_id || ':' || l_success, 4);
mydebug('Exitting insert_serial : 90 '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
mydebug('Exitting insert_serial - execution error:'|| l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('wms_task_load.insert_serial', l_progress, SQLCODE);
mydebug('Exitting insert_serial - other exception:'|| l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'insert_serial');
END insert_serial;
select mmtt.subinventory_code,
mmtt.locator_id,
mmtt.revision,
mmtt.lpn_id,
null,
null,
mmtt.transaction_action_id, -- Bug 4632519
mmtt.transaction_type_id -- Bug 4632519
INTO l_sub,
l_loc,
l_rev,
l_lpn,
l_ser,l_lot,
l_transaction_action_id, -- Bug 4632519
l_transaction_type_id -- Bug 4632519
from mtl_material_Transactions_temp mmtt
where mmtt.inventory_item_id = p_inventory_item_id
and mmtt.organization_id = p_organization_id
and mmtt.transfer_lpn_id = p_transfer_lpn_id
and mmtt.content_lpn_id is null
and decode(p_revision_control,2,mmtt.revision,1,'~~') = nvl(p_revision,'~~')
and rownum<2;
select mmtt.subinventory_code,
mmtt.locator_id,
mmtt.revision,
mmtt.lpn_id,
null,
mtlt.lot_number,
mmtt.transaction_action_id,
mmtt.transaction_type_id
INTO l_sub,
l_loc,
l_rev,
l_lpn,
l_ser,
l_lot,
l_transaction_action_id, -- Bug 4632519
l_transaction_type_id -- Bug 4632519
from mtl_material_Transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
where mmtt.inventory_item_id = p_inventory_item_id
and mmtt.organization_id = p_organization_id
and mmtt.transfer_lpn_id = p_transfer_lpn_id
and mmtt.content_lpn_id is null
and decode(p_revision_control,2,mmtt.revision,1,'~~') = nvl(p_revision,'~~')
and mmtt.transaction_temp_id = mtlt.transaction_temp_id
and mtlt.lot_number = p_lot_number
and rownum<2;
inv_cost_group_update.proc_determine_costgroup(
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => null,
p_containerized_flag => null,
p_lpn_id => p_lpn_id,
p_transaction_action_id => p_trx_action_id,
p_is_backflush_txn => l_is_bf,
x_cost_group_id => l_cur_cost_group_id,
x_return_status => x_return_status);
inv_cost_group_update.proc_determine_costgroup(
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_subinventory_code => l_sub,
p_locator_id => l_loc,
p_revision => l_rev,
p_lot_number => l_lot,
p_serial_number => l_ser,
p_containerized_flag => null,
p_lpn_id => l_lpn,
p_transaction_action_id => l_transaction_action_id,
p_is_backflush_txn => l_is_bf,
x_cost_group_id => l_exist_cost_group_id,
x_return_status => x_return_status);