APPS.WMS_PURGE_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 57

  l_progress := 'Bulk insert LPNs into temp table for validation';

Line: 59

  INSERT INTO WMS_TXN_CONTEXT_TEMP ( line_id, txn_source_name )
  VALUES ( p_lpn_id_table(i), 'WMS_LPN_PURGE' );

Line: 63

  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;

Line: 89

  DELETE FROM WMS_TXN_CONTEXT_TEMP
  WHERE  txn_source_name = 'WMS_LPN_PURGE';

Line: 162

  l_progress := 'Delete from packaging history of all content packages';

Line: 164

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

Line: 175

  l_progress := 'Delete all history records for that LPN';

Line: 177

  DELETE FROM WMS_LPN_HISTORIES
  WHERE  parent_lpn_id = p_lpn_id_table(i)
  OR     lpn_id = p_lpn_id_table(i);

Line: 187

  l_progress := 'Delete all contents for that LPN';

Line: 189

  DELETE FROM WMS_LPN_CONTENTS
  WHERE  parent_lpn_id = p_lpn_id_table(i);

Line: 198

  l_progress := 'Delete the LPN itself';

Line: 200

  DELETE FROM WMS_LICENSE_PLATE_NUMBERS
  WHERE  lpn_id = p_lpn_id_table(i);

Line: 209

  l_progress := 'Delete the LPN-EPC cross reference';

Line: 211

  DELETE FROM WMS_EPC
    WHERE  lpn_id = p_lpn_id_table(i)
    AND cross_ref_type = 1;

Line: 262

  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;

Line: 279

    FND_MSG_PUB.DELETE_msg;

Line: 282

      delete from wms_device_requests_hist
      where creation_date < l_date and organization_id = p_orgid;

Line: 302

        l_lpn_tbl.delete;

Line: 341

          l_wsh_lpn_rec.lpn_ids.delete;

Line: 366

          l_lpn_tbl.delete;

Line: 430

      delete from wms_dispatched_tasks_history
      where last_update_date < l_date and organization_id = p_orgid ;

Line: 437

      delete from wms_exceptions
      where creation_date < l_date and organization_id = p_orgid ;

Line: 446

      delete WMS_LABEL_REQUESTS_HIST
      where  creation_date < l_date and organization_id = p_orgid;

Line: 457

       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

Line: 477

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

Line: 514

    fnd_msg_pub.delete_msg;