/
'Creditor warehouse not found' error reported when loading inventory in a newly upgraded 6.5.13 database

'Creditor warehouse not found' error reported when loading inventory in a newly upgraded 6.5.13 database

Nov 21, 2022

Problem

Loading some inventory items in a newly upgraded 6.5.13 /7.00.xxx database reports 'Creditor warehouse not found.'

 

Solution

The issue is caused by IN_SupplierWarehouse records which are linked to a warehouse not belonging to the creditor.  The issue probably stemmed from Version 6 which tolerated incorrect linking and the bad records were ignored. Version 7 does not tolerate this inconsistency.

Advance SQL Programming skills required

Completing this troubleshooting article requires advanced SQL Programming ability.

This query identifies the bad records and shows which creditor the warehouse belongs to:

SELECT IN_Main.PartNo, CR_Main.AccountNo [Creditor Account No], CR_Main.Name [Creditor Name], IN_SupplierWarehouse.WarehouseID, (SELECT a.AccountNo FROM CR_Main a JOIN CR_Warehouse b ON a.CreditorID = b.CreditorID WHERE b.WarehouseID = IN_SupplierWarehouse.WarehouseID) [Creditor WarehouseID Belongs To] FROM IN_SupplierWarehouse JOIN IN_Creditor ON IN_Creditor.RecID = IN_SupplierWarehouse.IN_Creditor_RecID JOIN CR_Main ON CR_Main.CreditorID = IN_Creditor.CreditorID JOIN IN_Main ON IN_Main.InventoryID = IN_Creditor.InventoryID WHERE WarehouseID NOT IN (SELECT CR_Warehouse.WarehouseID FROM CR_Warehouse WHERE CR_Warehouse.CreditorID = IN_Creditor.CreditorID) ORDER BY IN_Main.PartNo, CR_Main.AccountNo

The following script will set the WarehouseID to be the default warehouse for the creditor for those IN_SupplierWarehouse records that are affected:

UPDATE IN_SupplierWarehouse SET WarehouseID = (SELECT TOP 1 CR_Warehouse.WarehouseID FROM CR_Warehouse WHERE CR_Warehouse.DefaultItem = 1 AND CR_Warehouse.CreditorID = (SELECT IN_Creditor.CreditorID FROM IN_Creditor WHERE IN_Creditor.RecID = IN_SupplierWarehouse.IN_Creditor_RecID)) WHERE IN_SupplierWarehouse.RecID IN ( SELECT IN_SupplierWarehouse.RecID FROM IN_SupplierWarehouse  JOIN IN_Creditor ON IN_Creditor.RecID = IN_SupplierWarehouse.IN_Creditor_RecID JOIN CR_Main ON CR_Main.CreditorID = IN_Creditor.CreditorID JOIN IN_Main ON IN_Main.InventoryID = IN_Creditor.InventoryID WHERE WarehouseID NOT IN (SELECT CR_Warehouse.WarehouseID FROM CR_Warehouse WHERE CR_Warehouse.CreditorID = IN_Creditor.CreditorID) )

Test this out on a backup copy of the data before running live.

Related articles