Applies to:

Oracle Warehouse Management - Version: 11.5.10.CU2 and later   [Release: 11.5.10 and later ]
Information in this document applies to any platform.

Symptoms

On : 11.5.10.CU2 version, LPN Management

Drop LPN errors in production system.

Problems when trying to drop lpn "In Receiving" status in material workbench.
1)When  putaway move any lpn with mobile, geting "The task errored out while processing. Do you want to continue with the next task?" error.
2) When putaway move any lpn with mobile , we get "ERROR READS LOCKED BY ANOTHER USER AND DISPLAYS THE NAME OF THE FIRST USER"

ERROR
-----------------------
a) The task errored out while processing. Do you want to continue with the next task.
and
b) ERROR READS LOCKED BY ANOTHER USER AND DISPLAYS THE NAME OF THE FIRST USER

Inventory Debug Log:

INV_LOC_WMS_UTILS : Inside Procedure Revert Suggested Locator Capacity
WMS_Task_Dispatch_put_away: Failure returned from revert_loc_suggested_capacity API
WMS_Task_Dispatch_put_away: Locator id value is
WMS_Task_Dispatch_put_away: Getting locator value using parent mmtt as locator value is null
WMS_Task_Dispatch_put_away: The locator id could not be fetched
WMS_Task_Dispatch_put_away: Current MMTT suggestion values:
WMS_Task_Dispatch_put_away: Inventory Item ID: => 1073753
WMS_Task_Dispatch_put_away: Locator ID: ========>
WMS_Task_Dispatch_put_away: Transaction qty: ===> 2
WMS_Task_Dispatch_put_away: Transaction UOM: ===> AD
WMS_Task_Dispatch_put_away: Call INV_LOC_WMS_UTILS.revert_loc_suggested_capacity API
INV_LOC_WMS_UTILS : Inside Procedure Revert Suggested Locator Capacity
WMS_Task_Dispatch_put_away: Failure returned from revert_loc_suggested_capacity API
WMS_Task_Dispatch_put_away: complete_putaway: Excection error - User-Defined Exception
WMS_PUTAWAY_UTILS.complete_putaway_wrapper: complete_putaway_wrapper: Error at progress: 230 complete_putaway API returns g_exc_error
WMS_PUTAWAY_UTILS.complete_putaway_wrapper: complete_putaway_wrapper: G_EXC_ERROR ocurred after l_progress = 230

STEPS
-----------------------
Whse mgmt responbility (N) Tasks > Directed tasks > Directed move > Move Any LPN > Drop lpn -> Get errors.

Cause

The issue has been identified in the datafix bug <<13605271>>
The cause of the issue is invalid / incorrect data in wms_op_operation_instances, wms_dispatched_tasks, mtl_transaction_lots_temp, wms_op_plan_instances, mtl_material_transactions_temp, mtl_transaction_lots_interface, rcv_lots_interface, rcv_transactions_interface, mtl_txn_request_lines, and wms_lpn_contents.

Solution

Please perform. the following steps in clone instance

a) If the issue is similar to this note please log a service request to get datafix

b) Upload WMS & INV debug logs and WMS LPN Details.

Cleanup_Script.sql

set serveroutput on
DECLARE
  l_lpn_id      NUMBER :=&lpn_id; 
BEGIN
  -- Data Clean Up
  -- Run the following script. for one LPN ID at a time
  dbms_output.put_line('Cleaning wms_op_plan_instances..'); 
  delete from wms_op_plan_instances
  where source_task_id in (
  select parent_line_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id union
  select transaction_temp_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('wms_op_plan_instances: No of rows deleted ..' || SQL%ROWCOUNT);
 
 
  dbms_output.put_line('Cleaning wms_op_operation_instances..');
  delete from wms_op_operation_instances
  where source_task_id in (
  select transaction_temp_id from mtl_material_transactions_temp
  where  lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id union
  select parent_line_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('wms_op_operation_instances: No of rows deleted ..' || SQL%ROWCOUNT);
 
 
  dbms_output.put_line('Cleaning wms_dispatched_tasks..');
  delete from wms_dispatched_tasks where
  transaction_temp_id in (select transaction_temp_id from mtl_material_transactions_temp
  where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id union
  select parent_line_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('wms_dispatched_tasks: No of rows deleted ..' || SQL%ROWCOUNT);
 
 
  dbms_output.put_line('Cleaning mtl_material_transactions_temp O..');
  delete from mtl_transaction_lots_temp
  where transaction_temp_id in (select transaction_temp_id from
  mtl_material_transactions_temp where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('mtl_material_transactions_temp O: No of rows deleted ..' || SQL%ROWCOUNT);
 
 
  dbms_output.put_line('Cleaning mtl_material_transactions_temp A..');
  delete from  mtl_material_transactions_temp
  where transaction_temp_id in
  (select parent_line_id from mtl_material_transactions_temp
  where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('mtl_material_transactions_temp A: No of rows deleted ..' || SQL%ROWCOUNT);
 
  dbms_output.put_line('Cleaning mtl_material_transactions_temp B..');
  delete from mtl_material_transactions_temp
  where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id;
  dbms_output.put_line('mtl_material_transactions_temp B: No of rows deleted ..' || SQL%ROWCOUNT);
 
  dbms_output.put_line('Cleaning mtl_transaction_lots_interface..');
  delete from mtl_transaction_lots_interface
  where lot_number in (select lot_number
  from wms_lpn_contents where parent_lpn_id = l_lpn_id);
  dbms_output.put_line('mtl_transaction_lots_interface: No of rows deleted ..' || SQL%ROWCOUNT);
 
  dbms_output.put_line('Cleaning rcv_transactions_interface..');
  delete from rcv_lots_interface
  where interface_transaction_id in (select interface_transaction_id
  from rcv_transactions_interface
  where lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id);
  dbms_output.put_line('rcv_transactions_interface: No of rows deleted ..' || SQL%ROWCOUNT);
 
  dbms_output.put_line('Cleaning rcv_transactions_interface..');
  delete from rcv_transactions_interface
  where lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id;
  dbms_output.put_line('rcv_transactions_interface: No of rows deleted ..' || SQL%ROWCOUNT);
 
  dbms_output.put_line('Cleaning mtl_txn_request_lines..');
  UPDATE mtl_txn_request_lines
  SET quantity_detailed = null, wms_process_flag = 1
  WHERE lpn_id = l_lpn_id
  and line_status=7;
  dbms_output.put_line('mtl_txn_request_lines: No of rows updated ..' || SQL%ROWCOUNT);
 
  update wms_lpn_contents
  set source_header_id = NULL,
  source_name = null
  where parent_lpn_id = l_lpn_id;
  dbms_output.put_line('wms_lpn_contents: No of rows updated ..' || SQL%ROWCOUNT);

  commit;

END;