WMS LPN DataFix --- LPN hanging at receiving

--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;