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