[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0); --13860546 selecting from profile earlier hard coded to 1
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_substitute_lots IN VARCHAR2 --/* Bug 9448490 Lot Substitution Project */
,p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' ); -- Added for 14699845 (Flexible Lot Allocation)
,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_substitute_lots IN VARCHAR2 --/* Bug 9448490 Lot Substitution Project */
,p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y'
,p_fulfillment_base IN VARCHAR2
,p_orig_mmtt_txn_uom IN VARCHAR2); -- Added for 14699845 (Flexible Lot Allocation)
,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
,p_substitute_lots IN VARCHAR2 --/* Bug 12670785 Lot Substitution enhancement for Serial Items ER */
,p_full_lot_allocation IN VARCHAR2); -- Added for 14699845 (Flexible Lot Allocation)
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 insert_mtlt (
p_new_temp_id IN NUMBER
, p_serial_temp_id IN NUMBER := NULL
, p_pri_att_qty IN NUMBER
, p_sec_att_qty IN NUMBER := NULL
, p_trx_att_qty IN NUMBER
, p_lot_number IN VARCHAR2
, p_item_id IN NUMBER
, p_organization_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2) ;
PROCEDURE insert_serials_temp (
p_transaction_temp_id IN NUMBER,
p_lpn_id IN NUMBER,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_rev IN VARCHAR2,
p_confirmed_trx_qty IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg OUT NOCOPY VARCHAR2
);
PROCEDURE update_loaded_part
(p_user_id IN NUMBER,
p_organization_id IN NUMBER, --BUG12622871LSC
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' );
p_confirmed_sugg_qty => l_secondary_quantity1,--need to revisit on testing case for update_loaded_part for fulfill base 'S'
p_confirmed_sec_qty => p_secondary_quantity,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
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 /* Bug 9448490 Lot Substitution Project */
--, last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
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
,p_substitute_lots IN VARCHAR2--/* Bug 9448490 Lot Substitution Project */
,p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' --Added for 14699845 (Flexible Lot Allocation)
,p_fulfillment_base IN VARCHAR2 DEFAULT 'P') --16070349
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);
--Insert the MSNTs HWSNIssue 13860546
IF ( NVL (p_lot_controlled,'N') = 'Y'
AND NVL (p_serial_controlled,'N') = 'Y'
AND NVL (p_serial_allocated_flag, 'N') = 'N'
AND p_lpn_match IN (1, 3)
AND lpn_has_unalloc_lots (NVL (l_content_lpn_id,l_lpn_id),
p_organization_id,
p_inventory_item_id,
p_revision,
p_transaction_temp_id
)
)
THEN
--Have to review this condition
mydebug ('Inserting msnts');
insert_serials_temp (p_transaction_temp_id => p_transaction_temp_id,
p_lpn_id => NVL (l_content_lpn_id , l_lpn_id),
p_org_id => p_organization_id,
p_item_id => p_inventory_item_id,
p_rev => p_revision,
p_confirmed_trx_qty => p_confirmed_trx_qty,
x_return_status => x_return_status,
x_msg => x_msg_data
);
mydebug ('Error while inserting MSNTs');
IF p_action = 'UPDATE_LOADED' THEN
update_loaded_part
(p_user_id => p_user_id,
p_organization_id => p_organization_id, --BUG12622871LSC
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';
-- Bug5659809: update last_update_date and last_update_by as well
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_packing
,catch_weight_flag = NULL --16466288
-- , last_update_date = SYSDATE /* Bug 9448490 Lot Substitution Project */
-- , last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
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' );
ELSIF (p_lpn_match = 4 AND l_transfer_lpn_id = l_lpn_match_lpn_id) THEN --added following so that lpn context is updated properly after load bug 12595055 OR (l_lpn_id = l_lpn_match_lpn_id))
mydebug ('Coming to the elseif portion with lpn match values as ' || p_lpn_match );
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_packing
,catch_weight_flag = NULL --16466288
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' );
-- Bug5659809: update last_update_date and last_update_by as well
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_packing
,catch_weight_flag = NULL --16466288
--, last_update_date = SYSDATE /* Bug 9448490 Lot Substitution Project */
--, last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
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
,p_substitute_lots => p_substitute_lots --/* Bug 9448490 Lot Substitution Project */
,p_full_lot_allocation => p_full_lot_allocation); -- Added for 14699845 (Flexible Lot Allocation)
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 the original one with p_transaction_temp_id
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
,p_substitute_lots IN VARCHAR2 --/* Bug 9448490 Lot Substitution Project */
,p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y')
IS
l_proc_name VARCHAR2(30) := 'PROC_INSERT_UPDATE_MMTT';
SELECT NVL(fulfillment_base, 'P'),
transaction_uom,
primary_quantity,
transaction_quantity,
secondary_transaction_quantity
INTO l_fulfillment_base,
l_orig_mmtt_txn_uom,
l_orig_mmtt_pri_qty,
l_orig_mmtt_txn_qty,
l_orig_mmtt_sec_qty
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
mydebug ('p_insert = ' || p_insert );
mydebug ('p_update = ' || p_update );
mydebug ('check for p_substitute_lots as the MTLT in spl case not deleted = ' || p_substitute_lots);
mydebug ('l_confirmed_sec_qty post processing before MMTT insert is ' || l_confirmed_sec_qty);
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
,FULFILLMENT_BASE
)
(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, DECODE(fulfillment_base, 'S', secondary_uom_code, item_primary_uom_code))--MUOM the transaction uom should be confirmed uom
--or secondary uom for fulfillment_base 'S' else primary 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
,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)--Bug4576653/MUOM for fulfill base 'S' assumption is confirmed_sec_uom won't be NULL
,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
,FULFILLMENT_BASE
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id);
fnd_message.set_name('WMS', 'WMS_INSERT_ALLOCATION'); -- NEWMSG
-- "Error Inserting Allocation ."
fnd_msg_pub.ADD;
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_substitute_lots => p_substitute_lots -- Bug 9448490 Lot Substitution Project
,p_full_lot_allocation => p_full_lot_allocation -- Added for 14699845 (Flexible Lot Allocation)
,p_fulfillment_base => NVL(l_fulfillment_base, 'P') -- 16070349
,p_orig_mmtt_txn_uom => l_orig_mmtt_txn_uom); -- 16070349
-- may not be necessary to call if p_insert = N , LOAD_SINGLE
proc_process_confirmed_serials
(p_action => p_action
,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
,p_substitute_lots => p_substitute_lots --BUG12670785
,p_full_lot_allocation => p_full_lot_allocation); -- Added for 14699845 (Flexible Lot Allocation)
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';
mydebug('l_progress for p_update Y1 in split case ' || l_progress);
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 = l_calc_mmtt_txn_qty
, primary_quantity = l_calc_mmtt_pri_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 -- muom:sk added decode this is for full pick again.. SSK
, secondary_transaction_quantity = DECODE(p_confirmed_sec_uom, NULL, NULL, l_confirmed_sec_qty)
, 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 = l_calc_mmtt_txn_qty
, primary_quantity = l_calc_mmtt_pri_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
,p_substitute_lots IN VARCHAR2 --/* Bug 9448490 Lot Substitution Project */
,p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' -- Added for 14699845 (Flexible Lot Allocation)
,p_fulfillment_base IN VARCHAR2
,p_orig_mmtt_txn_uom IN 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;
SELECT DISTINCT lot_number, transaction_quantity,
primary_quantity, secondary_quantity --BUG12670785
FROM mtl_allocations_gtmp
WHERE lot_number NOT IN (
SELECT DISTINCT lot_number
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_lot_transaction_temp_id)
ORDER BY lot_number;
CURSOR cur_mtlts_deleted_ls IS
SELECT mtlt.transaction_temp_id, mtlt.lot_number, mtlt.primary_quantity FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.lot_number NOT IN (SELECT mag.lot_number FROM mtl_allocations_gtmp mag)
AND mtlt.transaction_temp_id = p_transaction_temp_id;
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 );
mydebug('Inserting into MTLT - p_transaction_temp_id :' || p_transaction_temp_id);
mydebug('Inserting into MTLT - l_lot_prim_qty_ls :' || l_lot_prim_qty_ls);
mydebug('Inserting into MTLT - l_lot_sec_qty_ls :' || l_lot_sec_qty_ls);
mydebug('Inserting into MTLT - l_lot_trx_qty_ls :' || l_lot_trx_qty_ls);
mydebug('Inserting into MTLT - l_lot_number_ls :' || l_lot_number_ls);
mydebug('Inserting into MTLT - p_inventory_item_id :' || p_inventory_item_id);
mydebug('Inserting into MTLT - p_organization_id :' || p_organization_id);
insert_mtlt (
p_new_temp_id => p_transaction_temp_id
, p_serial_temp_id => NULL
, p_pri_att_qty => l_lot_prim_qty_ls
, p_sec_att_qty => l_lot_sec_qty_ls
, p_trx_att_qty => l_lot_trx_qty_ls
, p_lot_number => l_lot_number_ls
, p_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, x_return_status => l_return_status_ls) ;
mydebug('Inserting into MTLT - l_return_status_ls-' || l_return_status_ls);
mydebug('The value of p_insert :' || p_insert);
IF ((p_action in ('LOAD_SINGLE', 'LOAD_MULTIPLE')) AND p_insert = 'N' ) THEN --12871057
--The following loop is only to print debug messages..
OPEN cur_mtlts_deleted_ls;
FETCH cur_mtlts_deleted_ls INTO l_ls_temp_id, l_ls_lot_number, l_ls_lot_prim_qty;
EXIT WHEN cur_mtlts_deleted_ls%NOTFOUND;
CLOSE cur_mtlts_deleted_ls;
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.lot_number NOT IN (SELECT NVL(lot_number, '@####') FROM mtl_allocations_gtmp) --Added NVL as in some cases p_lot_numbers had ':' extra causing insert with null values
AND mtlt.transaction_temp_id = p_transaction_temp_id;
-- Only p_insert = y means a new MMTT is created and therefore new MTLT will have to
-- be created.
IF l_prev_lot_number <> rec_confirmed_lots_serials.lot_number
THEN
l_prev_lot_number := rec_confirmed_lots_serials.lot_number ;
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';
-- lot qty in the selected, in the above cursor, MTLT equals qty that is needed for the new MTLT
-- update the MTLT with new temp id instead of inserting a new and then deleting the old one
l_progress := '200';
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;
-- insert a new MTLT
l_progress := '210';
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);
-- If new MTLT is inserted for p_new_transaction_temp_id this means
-- the original MTLT still has some qty remaining...so update it.
-- the original MTLT need not be adjusted for secondary quantity.
-- It is not expected to be populated
IF(p_fulfillment_base = 'S') THEN
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_calc_lot_txn_qty
,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
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
AND lot_number = rec_confirmed_lots_serials.lot_number;
mydebug ('p_update: ' || p_update);
-- p_update = 'Y2' means, p_insert = N which means that current MMTT (in this call)
-- was merged with another existing MMTT. In this case, it is possible that existing MMTT already has
-- MTLT record for this lot number. If this is the case then update the MTLT otherwise ,
-- insert a new MTLT using l_rec_mtlt_to_copy_from from the above cursor
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;
--lot qty in the selected MTLT = qty that is needed for the new MTLT
--update the MTLT with new temp id instead of inserting a new and then deleting the old one
l_progress := '250';
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);
-- If new MTLT is inserted for p_transaction_temp_id_to_merge this means
-- the original MTLT still has some qty remaining...so update it.
IF(p_fulfillment_base = 'S') THEN
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_calc_lot_txn_qty
,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 -- 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;
-- delete it.
IF ((l_rec_mtlt_to_copy_from.primary_quantity = rec_confirmed_lots_serials.primary_quantity AND NVL(p_fulfillment_base, 'P') = 'P') OR
(l_rec_mtlt_to_copy_from.secondary_quantity = rec_confirmed_lots_serials.secondary_quantity AND p_fulfillment_base = 'S'))THEN
l_progress := '290';
-- p_transaction_temp_id_to_merge we do not need the original MTLT ..delete it
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;
-- If all the qty from the original MTLT is not consumed then update the original MTLT
-- attached to p_transaction_temp_id .. as selected in the cursor above
l_progress := '300';
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_calc_lot_txn_qty
,primary_quantity = primary_quantity - rec_confirmed_lots_serials.primary_quantity
,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;
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_lpn_match IN (1,3)
AND(p_substitute_lots IS NULL OR (p_substitute_lots IS NOT NULL
AND check_if_lot_is_substituted(p_substitute_lots, rec_confirmed_lots_serials.lot_number, p_full_lot_allocation)))) OR --HWSNIssue 13860546 -- Modified for 14699845 (Flexible Lot Allocation)
((p_lpn_match = 4 or (nvl(p_lpn_match,0) <=0)) AND check_if_lot_is_substituted(p_substitute_lots, rec_confirmed_lots_serials.lot_number,p_full_lot_allocation)))) --BUG 12670785 only in case of lot substitution for serila items
-- Modified for 14699845 (Flexible Lot Allocation)
THEN
l_progress := '350';
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
,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_confirmed_uom <> p_suggested_uom OR --BUG13491033
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 all serial records with the serial_transaction_temp_id to be populated
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_substitute_lots => p_substitute_lots--BUG12670785
,p_full_lot_allocation => p_full_lot_allocation); -- Added for 14699845 (Flexible Lot Allocation)
,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
,p_substitute_lots IN VARCHAR2 --for BUG12670785
,p_full_lot_allocation IN VARCHAR2) -- Added for 14699845 (Flexible Lot Allocation)
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 a new mmtt ininserted then the MSNT
-- should be attached to p_new_transaction_temp_id
ELSE
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
-- and this will be used only if we are inserting MSNTs for
-- no allocated serIals
END IF;
((p_insert = 'Y' OR p_update = 'Y2') AND (NOT check_if_lot_is_substituted(p_substitute_lots, p_serial_lot_number , p_full_lot_allocation))) --BUG12670785 -- Modified for 14699845 (Flexible Lot Allocation)
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 the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
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('11 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,'@@'));
-- Pick Load page will insert these MSNTs
THEN
l_progress := '180';
--proc_insert_msnt inserts into MSNT using the data from MSN and also marks MSN
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 the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
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);
(p_insert = 'N' OR p_update = 'Y1') AND
(check_if_lot_is_substituted (p_substitute_lots, p_serial_lot_number, p_full_lot_allocation)) --BUG12670785 -- Modified for 14699845 (Flexible Lot Allocation)
THEN
l_progress := '200111';
-- update the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
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 = p_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 = p_transaction_temp_id
AND msn.current_organization_id = p_organization_id --HWSNIssue 13860546(Performance Issue)
AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
AND Nvl(msn.lpn_id,'-999') = Nvl(p_lpn_match_lpn_id ,'-999'));--12871057
mydebug('msnt not updateed coming to AMO code....');
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 msn.current_organization_id = p_organization_id --HWSNIssue 13860546(Performance Issue)
AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
AND Nvl(msn.lpn_id,'-999') = Nvl(p_lpn_match_lpn_id ,'-999'));
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';
-- copied the logic from INV_TRX_UTIL_PUB.INSERT_SER_TRX
l_real_serial_prefix := RTRIM(p_serial_number, '0123456789');
SELECT '1' INTO l_check FROM DUAL WHERE (SELECT Count(1) FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
AND lpn_id = p_lpn_id AND current_status = 3) = (SELECT Sum(primary_transaction_quantity) FROM mtl_onhand_quantities_detail
WHERE inventory_item_id = p_inventory_item_id
AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
AND lpn_id = p_lpn_id AND organization_id = p_organization_id );
-- P_serial_number is null means we are inserting a group of serials either
-- 1. using LPN_ID passed in or serial_lot_number passed in (p-confirmed_serials is NULL)
-- OR 2. using the data from the gtmp table (when p_confirmed_serials is nOT NULL)
IF p_serial_number IS NULL
THEN
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;
-- update one of the MMTTs(min temp_id) from each group and delete rest from that group */
CURSOR cur_mmtt1
IS
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
, SECONDARY_UOM_CODE --BUG12622871LSC
, fulfillment_base
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
,SECONDARY_UOM_CODE --BUG12622871LSC
,fulfillment_base;
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:');
-- update one of the MMTTs(min temp_id) from each group and delete rest from that group
l_progress := 500;
DELETE mtl_allocations_gtmp ;
-- MMTTs should be deleted that belong to the p_transaction-header_id but are not
-- in this list
l_progress := 600;
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);
-- Bug#4185621: decide whether to update posting flag
IF (rec_mmtt1.parent_line_id = rec_mmtt1.group_temp_id) THEN
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;
-- Bug# 4185621: update child line posting flag back to 'Y' for bulk picking
IF (l_parent_posting_flag = 'N') THEN
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 'Y'
INTO l_lot_alloc_exist
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND ROWNUM = 1;
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,'@@')
UNION --For lot substitution of lot and serial items when serial are confirmed we insert msnt with mmtt.transaction_temp_id
--at that time their is no MTLT for substituted lot..
(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,'@@') )
);
-- Now delete MSNTs
l_progress := 1400;
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,'@@')
UNION --For lot substitution of lot and serial items when serial are confirmed we insert msnt with mmtt.transaction_temp_id
--at that time their is no MTLT for substituted lot..
(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 := 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_mmtt1.group_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,'@@') );
-- Now update MSNT
l_progress := 1900;
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);
-- delete all msnts and unmark all these serials in MSN.
l_progress := 2100;
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;
-- are confirmed to stay back in MMTT and WDT. Using this we will delete WDT and MMTT
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 :' );
-- are confirmed to stay back in MMTT and WDT. Using this we will delete WDT and MMTT
/* {{ 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 all tasks that belong to this employee
CURSOR cur_wdt_for_emp IS
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 task that belong to this employee and p_transaction_temp_id
CURSOR cur_wdt_for_temp_id IS
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 all MOLs that are cancelled , so that cancelled tasks can be reduced
CURSOR cur_cancelled_MOLs IS
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 mmtt
WHERE transaction_header_id = p_transaction_header_id
AND nvl(content_lpn_id , nvl(lpn_id,-999)) <> transfer_lpn_id
aND not exists ( select 1 from mtl_material_transactions_temp mmtt1
where mmtt1.transaction_header_id = p_transaction_header_id
and mmtt.transfer_lpn_id=mmtt1.transfer_lpn_id
and (mmtt1.lpn_id=mmtt1.transfer_lpn_id or
mmtt1.content_lpn_id=mmtt1.transfer_lpn_id )); --BUG 12803567
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);
-- Bug5659809: update last_update_date and last_update_by as well
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_PREGENERATED
-- , last_update_date = SYSDATE /* Bug 9448490 Lot Substitution Project */
-- , last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
WHERE lpn_id = rec_transfer_lpns.transfer_lpn_id
AND lpn_context <> l_lpn_context_picked;
-- Bug5659809: update last_update_date and last_update_by as well
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_INV
--, last_update_date = SYSDATE /* Bug 9448490 Lot Substitution Project */
--, last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
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);
-- Bug5659809: update last_update_date and last_update_by as well
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context_inv
--, last_update_date = SYSDATE /* Bug 9448490 Lot Substitution Project */
--, last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
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:' );
-- The idea is to delete all tasks belonging to the same header_id (as in case of splits)
-- and also to delete all dscpatched and active tasks
-- Decide to delete all tasks with status = dispatched (3 or Active 9).
-- There is no need to check for a specific header_id . For F2, we anyway always
-- delete all dispatched and active tasks
BEGIN
l_prev_task_status := wms_picking_pkg.g_previous_task_status(p_transaction_temp_id);
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 ' );
--It is OK not to find even one task to delete
ELSE
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' );
--It is OK not to find even one task to update
END IF;
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);
SELECT NVL(SUM(mtlt.primary_quantity) , SUM(mmtt.primary_quantity))
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.reservation_id = p_reservation_id
AND mmtt.transaction_temp_id = mtlt.transaction_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;
SELECT Sum(quantity) INTO l_lpn_quantity
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_item_id ;
SELECT parent_lpn_id INTO l_parent_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id=l_content_lpn_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 - ');
--At the end of the load process, we need to update the quantity_detailed
--of the move order line. Do this for non-bulk tasks
IF (l_mo_line_id IS NOT NULL AND l_parent_line_id IS NULL) THEN
IF (l_debug = 1) THEN
mydebug('Should update quantity_detailed for MO Line ID: ' || l_mo_line_id);
SELECT uom_code, SECONDARY_UOM_CODE -- muom:sk
INTO l_mol_uom, l_sec_mol_uom -- muom:sk
FROM mtl_txn_request_lines
WHERE line_id = l_mo_line_id
FOR UPDATE;
SELECT ABS(SUM(primary_quantity)), ABS(SUM(secondary_transaction_quantity))
INTO l_sum_mmtt_qty, l_sum_mmtt_sec_txn_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);
mydebug('update secondary_quantity_detailed in MOL with: ' || l_sum_mmtt_sec_txn_qty);
--Now update quantity_detailed as quanity_delivered + sum(mmtt qty)
--Bug#10120826 Now update secondary_quantity_detailed as secondary_quanity_delivered + l_sum_mmtt_sec_txn_qty
-- muom:sk ssk: this takes care of full pick only..
UPDATE mtl_txn_request_lines
SET quantity_detailed = DECODE(l_fulfillment_base, 'S', l_sum_mmtt_qty, (NVL(quantity_delivered, 0) +l_mmtt_qty_in_mol_uom)),
secondary_quantity_detailed = DECODE(l_fulfillment_base, 'S', l_mmtt_sec_qty_in_mol_uom, (NVL(secondary_quantity_delivered,0) + l_sum_mmtt_sec_txn_qty)),
last_update_date = SYSDATE,
last_updated_by = p_user_id
WHERE line_id = l_mo_line_id;
SELECT reservation_id
INTO l_reservation_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id
FOR UPDATE;
UPDATE mtl_reservations
SET detailed_quantity = LEAST(l_new_mmtt_qty, primary_reservation_quantity)
WHERE reservation_id = l_reservation_id;
mydebug('Could not find MMTT to update the Detailed quantity for FB=S ');
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
--added material status check for lot under bug8398578
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_org_id
, p_item_id
, NULL
, null
, mtlt.lot_number
, NULL
, 'O') = 'Y'
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 NVL(SUM(primary_transaction_quantity),0)
, lot_number , Nvl(Sum(secondary_transaction_quantity),0) --16267113
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_org_id
AND Nvl(containerized_flag, 2) = 1
AND lpn_id = p_fromlpn_id
AND subinventory_code = p_confirmed_sub
AND locator_id = p_confirmed_loc_id
AND inventory_item_id = p_item_id
AND (revision = p_rev OR (revision IS NULL AND p_rev IS NULL))
AND lot_number NOT IN (
SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
)
AND lot_number IS NOT NULL
GROUP BY lot_number
ORDER BY lot_number;
t_lpn_lot_qty_table.DELETE;
SELECT primary_uom_code
, secondary_uom_code
, lot_control_code
, serial_number_control_code
INTO l_primary_uom
, l_secondary_uom
, 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)
AND organization_id = p_org_id); -- this AND organization_id condition is added for Bug# 12541060
-- WMS PJM Integration, Selecting the resolved concatenated segments instead of concatenated segments
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
transaction_source_type_id --11068325
INTO l_locator_id, l_organization_id,
l_transaction_header_id,
l_transaction_uom,
l_sec_transaction_uom, -- Bug #4141928
l_transaction_source_type_id --11068325
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
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 (p_changelotNoException <> 'N'
OR (p_full_lot_allocation IN ('N', 'P'))
OR EXISTS (SELECT 1
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND mtlt.lot_number = wlc.lot_number)
)); -- Modified for 14699845 (Flexible Lot Allocation)
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'));
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
--in order to get correct att.
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 -- Bug #14753999
);
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 ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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 ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 -- Bug #14753999
);
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 ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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'));
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 -- Bug #14753999
);
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 ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
mydebug('update qty tree without lpn 2nd time: l_att :' || l_att);
mydebug('update qty tree without lpn 2nd time: l_satt:' || l_satt);
mydebug('calling update qty tree back without lpn 2nd time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 -- Bug #14753999
);
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);
mydebug('calling update qty tree with lpn 2nd time failed ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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);
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'));
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 -- Bug #14753999
);
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 ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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);
mydebug('calling update qty tree back 3rd time without lpn 3rd time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
--in order to get correct att.
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 -- Bug #14753999
);
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 ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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'));
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 -- Bug #14753999
);
mydebug('update qty tree with lpn 4th time: l_att :' || l_att);
mydebug('update qty tree with lpn 4th time: l_satt:' || l_satt);
mydebug('calling update qty tree with lpn 4th time failed ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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 ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 -- Bug #14753999
);
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 ');
--Bug#5649056: only update if subinventory and locator match
ELSIF ( l_lpn_sub = l_mmtt_sub AND l_lpn_loc = l_mmtt_loc ) THEN
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 -- Bug #14753999
);
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;
/* In Case of Flexible Lot Allocation, we would need to update the Qty Tree
with Negative values before query tree since we are not passing the demand information 16267113 */
--*******************************************************--
-- Start for 16267113 Flexible Lot Allocation
IF (p_full_lot_allocation IN ('N', 'P')) THEN
IF NVL(l_allocated_lpn_id, 0) = p_fromlpn_id THEN
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_task_pri_qty
, p_secondary_quantity => -l_task_sec_qty
, 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
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_fromlpn_id
);
mydebug('update qty tree for FlexiLotAlloc lpn l_att:' || l_att || ' at Locator Level');
mydebug('update qty tree for FlexiLotAlloc lpn l_satt:' || l_satt || ' at Locator Level');
mydebug('calling update qty tree with lpn for FlexiLotAlloc 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 => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_task_pri_qty
, p_secondary_quantity => -l_task_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
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
);
mydebug('after update without lpn for FlexiLotAlloc l_att:' || l_att || ' at Locator Level');
mydebug('after update without lpn for FlexiLotAlloc l_satt:' || l_satt || ' at Locator Level');
mydebug('calling update qty tree for FlexiLotAlloc without lpn 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 => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_task_pri_qty
, p_secondary_quantity => l_task_sec_qty
, 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
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_fromlpn_id
);
mydebug('update qty tree back with +ve qty for FlexiLotAlloc lpn l_att:' || l_att || ' at Locator Level');
mydebug('update qty tree back with +ve qty for FlexiLotAlloc lpn l_satt:' || l_satt || ' at Locator Level'); -- Bug #4141928
mydebug('calling update qty tree with lpn for FlexiLotAlloc 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_task_pri_qty
, p_secondary_quantity => l_task_sec_qty
, 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
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
);
mydebug('after update qty tree back for +ve qty without lpn for FlexiLotAlloc l_att:' || l_att || ' at Locator Level');
mydebug('after update qty tree back for +ve qty without lpn for FlexiLotAlloc l_satt:' || l_satt || ' at Locator Level'); -- Bug #4141928
mydebug('calling update qty tree back for +ve qty without lpn for FlexiLotAlloc failed ');
SELECT NVL(SUM(primary_quantity),0), NVL(SUM(secondary_quantity),0)
INTO l_lpn_qoh, l_lpn_sqoh
FROM wms_lpn_contents
WHERE parent_lpn_id = p_fromlpn_id
AND inventory_item_id = p_item_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
--added material status check for lot under bug8398578
AND inv_material_status_grp.is_status_applicable(
NULL
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_org_id
, p_item_id
, NULL
, null
, mtlt.lot_number
, NULL
, 'O') = 'Y'
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
SELECT NVL(SUM(primary_transaction_quantity),0)
, NVL(SUM(transaction_quantity), 0)
, lot_number
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_org_id
AND Nvl(containerized_flag, 2) = 2 -- different from lpn_match
AND subinventory_code = p_confirmed_sub
AND locator_id = p_confirmed_locator
AND inventory_item_id = p_item_id
AND (revision = p_rev OR (revision IS NULL AND p_rev IS NULL))
AND lot_number NOT IN (
SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
)
AND lot_number IS NOT NULL
GROUP BY lot_number
UNION
SELECT mtlt.primary_quantity
, mtlt.transaction_quantity
, mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
ORDER BY lot_number;
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....');
-- If negative Balance allowed then update vikas 09/07/04 V1
IF (p_is_negbal_allowed ='true') THEN
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 decode(mmtt.transaction_type_id, 35,'N',51,'N','Y')
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;
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 'Y'
INTO l_fb_comingle
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = p_organization_id
AND EXISTS(SELECT 1
FROM mtl_material_transactions_temp mmtt1
WHERE mmtt1.transaction_temp_id <> p_temp_id
AND mmtt1.organization_id = p_organization_id
AND mmtt1.inventory_item_id = p_inventory_item_id
AND (mmtt1.transfer_lpn_id = pick_to_lpn_rec.lpn_id
OR mmtt1.content_lpn_id = pick_to_lpn_rec.lpn_id)
AND NVL(mmtt.fulfillment_base, 'P') <> NVL(mmtt1.fulfillment_base,'P'));
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);
--SAVEPOINT rcv_insert_serial_sp;
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);
-- if the trx manager returned a 1 then it could not insert the row
IF l_return = 1 THEN
RAISE fnd_api.g_exc_error;
mydebug('Exitting insert_serial : 90 '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
--ROLLBACK TO rcv_insert_serial_sp;
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);
PROCEDURE insert_mtlt (
p_new_temp_id IN NUMBER
, p_serial_temp_id IN NUMBER := NULL
, p_pri_att_qty IN NUMBER
, p_sec_att_qty IN NUMBER := NULL
, p_trx_att_qty IN NUMBER
, p_lot_number IN VARCHAR2
, p_item_id IN NUMBER
, p_organization_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := fnd_api.g_ret_sts_success;
mydebug(' Inside insert mtlt' );
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_quantity
, primary_quantity
, secondary_quantity
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, 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_id
, territory_code
)
(SELECT p_new_temp_id
, sysdate
, -9999
, sysdate
, -9999
, p_trx_att_qty
, p_pri_att_qty
, p_sec_att_qty
, p_lot_number
, mln.expiration_date
, p_serial_temp_id
, mln.description
, mln.vendor_name
, mln.supplier_lot_number
, mln.origination_date
, mln.date_code
, mln.grade_code
, mln.change_date
, mln.maturity_date
, mln.retest_date
, mln.age
, mln.item_size
, mln.color
, mln.volume
, mln.volume_uom
, mln.place_of_origin
, mln.best_by_date
, mln.LENGTH
, mln.length_uom
, mln.recycled_content
, mln.thickness
, mln.thickness_uom
, mln.width
, mln.width_uom
, mln.curl_wrinkle_fold
, mln.lot_attribute_category
, mln.c_attribute1
, mln.c_attribute2
, mln.c_attribute3
, mln.c_attribute4
, mln.c_attribute5
, mln.c_attribute6
, mln.c_attribute7
, mln.c_attribute8
, mln.c_attribute9
, mln.c_attribute10
, mln.c_attribute11
, mln.c_attribute12
, mln.c_attribute13
, mln.c_attribute14
, mln.c_attribute15
, mln.c_attribute16
, mln.c_attribute17
, mln.c_attribute18
, mln.c_attribute19
, mln.c_attribute20
, mln.d_attribute1
, mln.d_attribute2
, mln.d_attribute3
, mln.d_attribute4
, mln.d_attribute5
, mln.d_attribute6
, mln.d_attribute7
, mln.d_attribute8
, mln.d_attribute9
, mln.d_attribute10
, mln.n_attribute1
, mln.n_attribute2
, mln.n_attribute3
, mln.n_attribute4
, mln.n_attribute5
, mln.n_attribute6
, mln.n_attribute7
, mln.n_attribute8
, mln.n_attribute9
, mln.n_attribute10
, mln.vendor_id
, mln.territory_code
FROM mtl_lot_numbers mln
WHERE mln.lot_number = p_lot_number
AND mln.inventory_item_id = p_item_id
AND mln.organization_id = p_organization_id);
mydebug(' Insert mtlt returns exception' );
SELECT NVL(SUM(primary_transaction_quantity),0)
, lot_number
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_org_id
AND Nvl(containerized_flag, 2) = 1 -- different from loose_match
AND lpn_id = p_fromlpn_id
AND subinventory_code = p_confirmed_sub
AND locator_id = p_confirmed_loc_id
AND inventory_item_id = p_item_id
AND (revision = p_rev OR (revision IS NULL AND p_rev IS NULL))
AND lot_number NOT IN (
SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
)
AND lot_number IS NOT NULL
GROUP BY lot_number
ORDER BY lot_number;
SELECT NVL(SUM(primary_transaction_quantity),0)
, lot_number
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_org_id
AND Nvl(containerized_flag, 2) <> 1
AND subinventory_code = p_confirmed_sub
AND locator_id = p_confirmed_loc_id
AND inventory_item_id = p_item_id
AND (revision = p_rev OR (revision IS NULL AND p_rev IS NULL))
AND lot_number NOT IN (
SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
)
AND lot_number IS NOT NULL
GROUP BY lot_number
ORDER BY lot_number;
t_lpn_lot_qty_table.DELETE;
SELECT primary_uom_code
, lot_control_code
, serial_number_control_code
INTO l_primary_uom
, 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';
INSERT INTO wms_allocations_gtmp(lot_number, primary_quantity,
transaction_quantity)
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);
SELECT mmtt.ITEM_PRIMARY_UOM_CODE, mmtt.TRANSACTION_UOM, mmtt.SECONDARY_UOM_CODE, mmtt.INVENTORY_ITEM_ID,
DECODE(mmtt.fulfillment_base, NULL, 'P', mmtt.fulfillment_base), mmtt.organization_id
INTO l_puom, l_tuom, l_suom, l_item_id, l_fulfillment_base, l_organization_id
FROM mtl_material_transactions_temp mmtt
WHERE transaction_temp_id = p_temp_id;
mydebug ('Inside proc_decrement_allocated_mtlts before update to MTLT l_prim_qty :' || l_prim_qty);
mydebug ('Inside proc_decrement_allocated_mtlts before update to MTLT l_sec_qty :' || l_sec_qty);
mydebug ('Inside proc_decrement_allocated_mtlts before update to MTLT l_txn_qty :' || l_txn_qty);
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_txn_qty,
primary_quantity = l_prim_qty,
secondary_quantity = l_sec_qty
WHERE transaction_temp_id = p_temp_id
AND lot_number = l_lot_number;
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_quantity = 0
AND transaction_temp_id = p_temp_id
AND lot_number = l_lot_number;
PROCEDURE insert_serials_temp (
p_transaction_temp_id IN NUMBER,
p_lpn_id IN NUMBER,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_rev IN VARCHAR2,
p_confirmed_trx_qty IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg OUT NOCOPY VARCHAR2
)
IS
CURSOR c_lots_in_lpn
IS
SELECT mtlt.lot_number, 'Y' allocated_lot
FROM wms_lpn_contents wlc, mtl_transaction_lots_temp mtlt
WHERE wlc.parent_lpn_id = p_lpn_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_transaction_temp_id
AND mtlt.lot_number = wlc.lot_number
UNION ALL
SELECT lot_number, 'N' allocated_lot
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_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 NOT EXISTS (
SELECT 1
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND mtlt.lot_number = wlc.lot_number);
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = v_lpn_id
AND lot_number = v_lot_number
AND inventory_item_id = p_item_id
AND NVL (revision, '-999') = NVL (p_rev, '-999')
AND current_organization_id = p_org_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
); --Added this Condition as while inserting serials, the same is not checked
mydebug ('In insert_serials_temp');
mydebug ('insert_serials_temp: p_transaction_temp_id : ' || p_transaction_temp_id);
mydebug ('insert_serials_temp: p_lpn_id : ' || p_lpn_id);
mydebug ('insert_serials_temp: p_org_id : ' || p_org_id);
mydebug ('insert_serials_temp: p_item_id : ' || p_item_id);
mydebug ('insert_serials_temp: p_rev : ' || p_rev);
/* Loop through all the Lots in the LPN and insert serials associated with the Lots.
For lots which are allocated, we pass the serial_transaction_temp_id as NULL and the api itself
generates one. In case we have an unallocated Lot in the LPN, the serial_transaction_temp_id
is passed as the transaction_temp_id of the task itself.
*/
FOR rec_lots_in_lpn IN c_lots_in_lpn
LOOP
FOR rec_serials_in_lpn IN c_serials_in_lpn (p_lpn_id, rec_lots_in_lpn.lot_number)
LOOP
IF (rec_lots_in_lpn.allocated_lot = 'Y')
THEN
mydebug ('insert_serials_temp: Its an allocated Lot');
mydebug ('insert_serials_temp: Its an unallocated Lot');
mydebug ('insert_serials_temp: Inside Loop');
mydebug ('insert_serials_temp: Lot Number : ' || rec_lots_in_lpn.lot_number);
mydebug ('insert_serials_temp: Serial Number : ' || rec_serials_in_lpn.serial_number);
mydebug ('insert_serials_temp: l_serial_temp_id : ' || l_serial_temp_id);
mydebug ('insert_serials_temp: p_rev : ' || p_rev);
insert_serial (p_serial_transaction_temp_id => l_serial_temp_id,
p_organization_id => p_org_id,
p_item_id => p_item_id,
p_revision => p_rev,
p_lot => rec_lots_in_lpn.lot_number,
p_transaction_temp_id => p_transaction_temp_id,
p_created_by => fnd_global.user_id,
p_from_serial => rec_serials_in_lpn.serial_number,
p_to_serial => rec_serials_in_lpn.serial_number,
p_status_id => NULL,
x_return_status => l_ser_return_status,
x_msg_data => l_ser_msg
);
x_msg := 'Error while calling insert_serial';
mydebug ('insert_serials_temp: Error while calling insert_serial');
mydebug ('insert_serials_temp: l_ser_return_status ' || l_ser_return_status);
mydebug ('insert_serials_temp: l_ser_msg ' || l_ser_msg);
mydebug ('insert_serials_temp: p_confirmed_trx_qty ' || p_confirmed_trx_qty);
mydebug ('insert_serials_temp: l_serial_cnt ' || l_serial_cnt);
END insert_serials_temp;
SELECT 1
INTO l_unalloc_lots
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_id -- add revision I think No need to add rev since x_match should take care.
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.revision,'-999') = NVL(p_rev,'-999')
AND wlc.organization_id = p_organization_id
AND NOT EXISTS (
SELECT 1
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND mtlt.lot_number = wlc.lot_number);
UPDATE MTL_SERIAL_NUMBERS
SET group_mark_id = NULL
, last_updated_by = fnd_global.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
UNION --For lot substitution of lot and serial items when serial are confirmed we insert msnt with mmtt.transaction_temp_id
--at that time their is no MTLT for substituted lot..
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
);
mydebug (p_api_name||' Updated '||SQL%ROWCOUNT||' rows in MSN');
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN ((SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id))
UNION
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id));
mydebug (p_api_name||' Deleted '||SQL%ROWCOUNT||' rows from MSNT');
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id);
SELECT NVL(lot_divisible_flag, 'Y')
FROM mtl_system_items_b
WHERE organization_id = org_id
AND inventory_item_id = item_id;
PROCEDURE Update_Tree_Flex_Lot (p_fromlpn_id IN NUMBER,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_rev IN VARCHAR2,
p_trx_qty IN NUMBER,
p_trx_uom IN VARCHAR2,
p_sec_qty IN NUMBER,
p_sec_uom IN VARCHAR2,
p_lot IN VARCHAR2,
p_temp_id IN NUMBER,
p_transaction_type_id IN NUMBER,
p_user_id IN NUMBER,
p_transaction_action_id IN NUMBER ,
p_confirmed_sub IN VARCHAR2,
p_confirmed_loc_id IN NUMBER,
p_full_lot_allocation IN VARCHAR2,
p_fulfillment_base IN VARCHAR2,
x_lpn_lot_vector OUT NOCOPY VARCHAR2,
x_lpn_lot_vector2 OUT NOCOPY VARCHAR2,
x_lpn_lot_vector3 OUT NOCOPY VARCHAR2,
x_lpn_lot_vector4 OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(30) := 'Update_Tree_Flex_Lot' ;
t_lpn_lot_qty_table.DELETE;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT mmtt.subinventory_code
,mmtt.locator_id
,allocated_lpn_id
INTO l_mmtt_sub
, l_mmtt_loc
, l_allocated_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT subinventory_code
, locator_id
INTO 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 value
into l_value
from v$nls_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';
mydebug('In Update_Tree_Flex_Lot Fulfillment basis is S and transaction UOM is different than secondary UOM');
mydebug('In Update_Tree_Flex_Lot Fulfillment basis is S and transaction UOM is same than secondary UOM');
mydebug('In Update_Tree_Flex_Lot Fulfillment basis is P and transaction UOM is different than Primary UOM');
mydebug('In Update_Tree_Flex_Lot Fulfillment basis is P and transaction UOM is same as Primary UOM');
inv_log_util.trace( 'Value of p_transaction_type_id : ' ||p_transaction_type_id, 'Inside Update_Tree_Flex_Lot ', 9);
inv_log_util.trace( 'Value of l_transaction_action_id : ' ||l_transaction_action_id, 'Inside Update_Tree_Flex_Lot ', 9);
inv_log_util.trace( 'Value of l_txn_source_type_id : ' ||l_txn_source_type_id, 'Inside Update_Tree_Flex_Lot ', 9);
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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 => NULL
, p_subinventory_code => l_lpn_sub
, p_locator_id => l_lpn_loc
, p_primary_quantity => -l_pr_qty
, p_secondary_quantity => -l_sec_qty
, 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
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_fromlpn_id
);
mydebug('update qty tree for FlexiLotAlloc lpn l_att:' || l_att || ' at Locator Level');
mydebug('update qty tree for FlexiLotAlloc lpn l_satt:' || l_satt || ' at Locator Level');
mydebug('calling update qty tree with lpn for FlexiLotAlloc 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 => NULL
, p_subinventory_code => l_mmtt_sub
, p_locator_id => l_mmtt_loc
, p_primary_quantity => -l_pr_qty
, p_secondary_quantity => -l_sec_qty
, 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
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
);
mydebug('after update without lpn for FlexiLotAlloc l_att:' || l_att || ' at Locator Level');
mydebug('after update without lpn for FlexiLotAlloc l_satt:' || l_satt || ' at Locator Level');
mydebug('calling update qty tree for FlexiLotAlloc without lpn failed ');
END Update_Tree_Flex_Lot;