{$page}

This series of SQL queries will reset all warehouse records back to the default. In essence, it reassigns all past and current warehouses records to the default (ID=10) warehouse.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

This query assumes there is only one inventory record for each warehouse in the database. We have encountered a number of cases where (probably due to some historical issue) certain parts may have multiple warehouse entries. This causes the merge process to fail (since it doesn't know which Inventory record is the one to use) and must be resolved first.

To identify any of the problems, run the following SQL. If it finds any results, they must be cleared before the subsequent queries will run successfully. This query checks to make sure you do not have more than 1 warehouse inventory record for ID = 10, if you did then when we attempt to consolidate we would run into errors. If you do have any records show up you must merge the duplicate warehouse summary records into each other.

code_formatsql

select ID, ClassTypeID, ItemName, PartID, WarehouseID, *

from Inventory

where PartID in

(

  select PartID

  from Inventory

  where WarehouseID=10 and ClassTypeID=12200

  group by PartID

  having Count(*) > 1

)

and WarehouseID=10 and ClassTypeID=12200

order by Inventory.ItemName

code

The following SQL can be run to merge all the warehouse entries into the default warehouse. It also sets this warehouse active and all the others inactive. It does not deactivate warehouses, however. If this is desired it should be done manually afterwards under Setup | Accounting Setup | General.

code_formatsql

–

– this set of queries merges all warehouse items back to the default warehouse (10)

–

– ————————————————————————————–

– set all warehouses but 10 inactive, and make 10 the default

– ————————————————————————————–

update PricingElement

set IsActive = 0, IsDefault = 0

where classtypeid = 12700 and ID 10

;

update PricingElement

set IsActive = 1, IsDefault = 1

where classtypeid = 12700 and ID = 10

– ————————————————————————————–

– change all the InventoryID fields to use the Inventory for the default warehouse

– ————————————————————————————–

– update the InventoryID for the Transpart

update Transpart

set InventoryID =

  (select ID from Inventory where Transpart.PartID=Inventory.PartID and Inventory.WarehouseID=10 and Inventory.ClassTypeID=12200)

where (PartID is not NULL)

– update the InventoryID for the VendorTransDetail's AttachedOrderInvID

update VendorTransDetail

set AttachedOrderInvID =

  (select ID from Inventory where VendorTransDetail.AttachedOrderPartID=Inventory.PartID and Inventory.WarehouseID=10 and Inventory.ClassTypeID=12200)

where (AttachedOrderPartID is not NULL)

– update the InventoryID for the InventoryLog

update InventoryLog

set InventoryID =

  (select ID from Inventory where InventoryLog.PartID=Inventory.PartID and Inventory.WarehouseID=10 and Inventory.ClassTypeID=12200)

where (PartID is not NULL)

– update the InventoryID for the Ledger

update Ledger

set InventoryID =

  (select ID from Inventory where Ledger.PartID=Inventory.PartID and Inventory.WarehouseID=10 and Inventory.ClassTypeID=12200)

where (PartID is not NULL)

– ————————————————————————————–

– change all the warehouses to use the default

– ————————————————————————————–

– change all orders to use the standard warehouse

update TransHeader

set WarehouseID = 10