The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_progress := 'Bulk insert LPNs into temp table for validation';
INSERT INTO WMS_TXN_CONTEXT_TEMP ( line_id, txn_source_name )
VALUES ( p_lpn_id_table(i), 'WMS_LPN_PURGE' );
SELECT lpn_id BULK COLLECT
INTO l_valid_lpns
FROM WMS_LICENSE_PLATE_NUMBERS wlpn, WMS_TXN_CONTEXT_TEMP wtct
WHERE wtct.txn_source_name = 'WMS_LPN_PURGE'
AND wlpn.lpn_id = wtct.line_id
AND wlpn.lpn_context IN (4, 5)
AND NOT EXISTS ( SELECT 1 FROM MTL_TRANSACTIONS_INTERFACE
WHERE lpn_id = wlpn.lpn_id
OR content_lpn_id = wlpn.lpn_id
OR transfer_lpn_id = wlpn.lpn_id )
AND NOT EXISTS ( SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE lpn_id = wlpn.lpn_id
OR content_lpn_id = wlpn.lpn_id
OR transfer_lpn_id = wlpn.lpn_id )
AND NOT EXISTS ( SELECT 1 FROM MTL_CYCLE_COUNT_ENTRIES
WHERE wlpn.lpn_context = 5
AND parent_lpn_id = wlpn.lpn_id
AND entry_status_code = 2 )
AND NOT EXISTS ( SELECT 1 FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE lpn_id = wlpn.lpn_id )
AND NOT EXISTS ( SELECT 1 FROM WMS_LICENSE_PLATE_NUMBERS
WHERE outermost_lpn_id = wlpn.outermost_lpn_id
AND lpn_context <> wlpn.lpn_context)
FOR UPDATE;
DELETE FROM WMS_TXN_CONTEXT_TEMP
WHERE txn_source_name = 'WMS_LPN_PURGE';
l_progress := 'Delete from packaging history of all content packages';
DELETE FROM WMS_PACKAGING_HIST
WHERE rowid in ( SELECT rowid FROM WMS_PACKAGING_HIST
START WITH parent_lpn_id = p_lpn_id_table(i)
CONNECT BY parent_package_id = PRIOR package_id );
l_progress := 'Delete all history records for that LPN';
DELETE FROM WMS_LPN_HISTORIES
WHERE parent_lpn_id = p_lpn_id_table(i)
OR lpn_id = p_lpn_id_table(i);
l_progress := 'Delete all contents for that LPN';
DELETE FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = p_lpn_id_table(i);
l_progress := 'Delete the LPN itself';
DELETE FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_lpn_id_table(i);
l_progress := 'Delete the LPN-EPC cross reference';
DELETE FROM WMS_EPC
WHERE lpn_id = p_lpn_id_table(i)
AND cross_ref_type = 1;
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS wlpn
WHERE organization_id = p_orgid
AND lpn_context IN (4, 5)
AND last_update_date < l_date;
FND_MSG_PUB.DELETE_msg;
delete from wms_device_requests_hist
where creation_date < l_date and organization_id = p_orgid;
l_lpn_tbl.delete;
l_wsh_lpn_rec.lpn_ids.delete;
l_lpn_tbl.delete;
delete from wms_dispatched_tasks_history
where last_update_date < l_date and organization_id = p_orgid ;
delete from wms_exceptions
where creation_date < l_date and organization_id = p_orgid ;
delete WMS_LABEL_REQUESTS_HIST
where creation_date < l_date and organization_id = p_orgid;
delete wms_epc we
where creation_date < sysdate --No organization is here
AND EXISTS ( SELECT 1 FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = we.lpn_id
AND we.cross_ref_type = 1 --LPN-EPC
AND wlpn.lpn_context = 4 )
OR EXISTS ( SELECT 1 FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = we.inventory_item_id
AND msn.serial_number = we.serial_number
AND we.cross_ref_type = 2 --Serial-EPC
AND msn.current_status = 4 ); --Issue out of store
INSERT INTO mtl_purge_header (
purge_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, purge_date
, archive_flag
, purge_name
, organization_id )
VALUES (
mtl_material_transactions_s.NEXTVAL
, SYSDATE
, FND_GLOBAL.user_id
, fnd_global.user_id
, SYSDATE
, FND_GLOBAL.user_id
, l_date
, NULL
, p_purge_name
, p_orgid );
fnd_msg_pub.delete_msg;