Skip to content
logo Knowledgebase

Sage 200 Professional - How to sync Manufacturing stock tables

Created on  | Last modified on 

Summary

Potentially there may be situations where a warehouse is showing as duplicated in SQL in the MFGWarehouse table, when compared with the MSEWarehouse which has one unique warehouse. This could be a result of products being imported without the Manufacturing Stock Item Extensions.

Description

This article has been prepared and issued to you as a goodwill gesture only and Sage accepts no liability or responsibility for its use.

Resolution

To correct this there are 3 scripts which can be run against the SQL database. These scripts in effect 'sync' the Stock, MSE and MFG tables.

Any errors encountered when running the scripts are usually associated with (but not solely) duplicate entries in one of the tables. These duplicates should be investigated and resolved before running the scripts again.

Identify Missing MSE Records
select * from Stockitem where (code not in (select stockcode from MSEStockItem))
InsertMissingMseData
 
-- This script ensures that we have data in the following tables if
-- data already exists in the core table they are related to.
---- MseProductGroup
-- MseStockItem
-- MseWarehouse
-- MseWarehouseItem
--************************ MseProductGroup ************************
-- Declare variables to hold source data
DECLARE @SourceID bigint
-- Declare and open cursor
DECLARE dataCursor CURSOR FOR SELECT ProductGroupID FROM ProductGroup WHERE (ProductGroupID NOT IN (SELECT ProductGroupID FROM MseProductGroup))
OPEN dataCursor 
-- Process data
FETCH Next FROM dataCursor INTO @SourceID
WHILE @@FETCH_STATUS = 0
BEGIN	
-- Get next counter and then increment it
	DECLARE @Counter bigint	SET @Counter = (SELECT TOP 1 NextValue FROM Counter)	UPDATE Counter SET NextValue = NextValue + 1	
-- Insert row into our parallel table
	INSERT INTO MseProductGroup (MseProductGroupID, ProductGroupID, MseContactID, UseDemandWarehouse, UseWOComponentWarehouse)
	VALUES (@Counter, @SourceID, null, 0, 0)		
-- Get next row	
FETCH Next FROM dataCursor INTO @SourceID
END
-- Close and deallocate cursor
CLOSE dataCursor
DEALLOCATE dataCursor
GO
--************************ MseStockItem ************************
-- Declare variables to hold source data
DECLARE @SourceCode varchar(255)
DECLARE @BomItemTypeID bigint
-- Declare and open cursor
DECLARE dataCursor CURSOR FOR SELECT Code, BomItemTypeID FROM StockItem WHERE (Code NOT IN (SELECT StockCode FROM MseStockItem))
OPEN dataCursor 
-- Process data
FETCH Next FROM dataCursor INTO @SourceCode, @BomItemTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Get next counter and then increment it
	DECLARE @Counter bigint	SET @Counter = (SELECT TOP 1 NextValue FROM Counter)	UPDATE Counter SET NextValue = NextValue + 1
	-- Insert row into our parallel table
	INSERT INTO MseStockItem (MseStockItemID, StockCode, AggregateDays, WorksOrderBatchMinQty, WorksOrderBatchMaxQty,
		CanCancelWorksOrders, CanCancelPurchaseOrders, MRPReplenishmentRulesTypeID, MRPReplenishmentMultipleValue,
		CanAmendPOReceiptAllocation, Linked, UseDemandWarehouse, UseWOCompletionWarehouse, ApplyReorderLevelAfterMaximum, ReplenishmentHorizonDays,
		BuiltBoughtDefaultMake, AdditionalDescription2, LeadTime, StdCost, Conversion, BoughtInUnit, BoughtInDesc, ProcessUnit,
		ProcessDesc, AutoUpdateSuppliers, Quarantine, MaximumStockLevel, ShelfLifeNo, ShelfLifeInterval, MaximumBatchSize,
		BuyerCode, MakeItem, OrderingMethod, OrderMultiple, ScrapPercent, StockConversionRound, BulkIssue, BomItemType, AdditionalDescription1,
		CostFreeze, MsmCostHeadingID)
	VALUES (@Counter, @SourceCode, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, @BomItemTypeID, '', 0, 1)
	-- Get next row
	FETCH Next FROM dataCursor INTO @SourceCode, @BomItemTypeID
END
-- Close and deallocate cursor
CLOSE dataCursor
DEALLOCATE dataCursor
GO
--************************ MseWarehouse ************************
-- Declare variables to hold source data
DECLARE @SourceID bigint
-- Declare and open cursor
DECLARE dataCursor CURSOR FOR SELECT WarehouseID FROM Warehouse WHERE (WarehouseTypeID NOT IN (2,3)) AND (WarehouseID NOT IN (SELECT WarehouseID FROM MseWarehouse))
OPEN dataCursor 
-- Process data
FETCH Next FROM dataCursor INTO @SourceID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Get next counter and then increment it
	DECLARE @Counter bigint	SET @Counter = (SELECT TOP 1 NextValue FROM Counter)	UPDATE Counter SET NextValue = NextValue + 1
	-- Insert row into our parallel table
	INSERT INTO MseWarehouse (MseWarehouseID, WarehouseID, IsComponentSource, MseContactID)
	VALUES (@Counter, @SourceID, 1, null)
	-- Get next row
	FETCH Next FROM dataCursor INTO @SourceID
END
-- Close and deallocate cursor
CLOSE dataCursor
DEALLOCATE dataCursor
GO
--************************ MseWarehouseItem ************************
DECLARE @Counter bigint SET @Counter = (SELECT TOP 1 NextValue FROM Counter)
UPDATE Counter SET NextValue = NextValue + 1
INSERT INTO MseWarehouseItem(MseWarehouseItemID, WarehouseItemID, IsDefaultWorksOrderWarehouse)
	SELECT @Counter + RANK() OVER (ORDER BY WarehouseItemID), WarehouseItemID, 0 	FROM WarehouseItem
 	WHERE (WarehouseItemID NOT IN (SELECT WarehouseItemID FROM MseWarehouseItem))
IF (SELECT MAX(MseWarehouseItemID) FROM MseWarehouseItem) > @Counter
	UPDATE Counter SET NextValue = (SELECT MAX(MseWarehouseItemID) + 1 FROM MseWarehouseItem)
DECLARE itemCursor CURSOR FOR SELECT DISTINCT ItemID FROM WarehouseItem
DECLARE @ItemID bigint
OPEN itemCursor
FETCH Next FROM itemCursor INTO @ItemID
WHILE @@FETCH_STATUS = 0
BEGIN
	IF NOT EXISTS(SELECT TOP 1 * FROM MseWarehouseItem, WarehouseItem WHERE MseWarehouseItem.WarehouseItemID = WarehouseItem.WarehouseItemID AND WarehouseItem.ItemID = @ItemID AND MseWarehouseItem.IsDefaultWorksOrderWarehouse = 1)
	BEGIN
		UPDATE MseWarehouseItem SET IsDefaultWorksOrderWarehouse = 1
		WHERE WarehouseItemID = (SELECT TOP 1 WarehouseItemID FROM WarehouseItem WHERE ItemID = @ItemID)
	END
	FETCH Next FROM itemCursor INTO @ItemID
END
-- Close and deallocate cursor
CLOSE itemCursor
DEALLOCATE itemCursor
GO
UpdateMFGTables
 
-- Synchronises the contents of the MseStockItem & MFGStockItem tables.
-- First make sure we have entries to copy in the MseStockItem table.
IF EXISTS(SELECT * FROM MseStockItem)
BEGIN	
-- Now make sure that we have an MFGStockItem table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGStockItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGStockItem entries.
		DELETE FROM MFGStockItem
		-- Copy all the rows from MseStockItem into MFGStockItem
		INSERT INTO MFGStockItem(MFGStockItemID, StockItemID, AggregateDays, WorksOrderBatchMinQty, WorksOrderBatchMaxQty, MFGContactID, CanCancelWorksOrders, CanCancelPurchaseOrders, MRPReplenishmentRulesTypeID, MRPReplenishmentMultipleValue, CanAmendPOReceiptAllocation, Linked, UseDemandWarehouse, UseWOCompletionWarehouse, ApplyReorderLevelAfterMaximum, ReplenishmentHorizonDays, BuiltBoughtDefaultMake)
		SELECT MseStockItemID, s.ItemID, AggregateDays, WorksOrderBatchMinQty, WorksOrderBatchMaxQty, MseContactID,  CanCancelWorksOrders, CanCancelPurchaseOrders, MRPReplenishmentRulesTypeID, MRPReplenishmentMultipleValue, CanAmendPOReceiptAllocation, Linked, UseDemandWarehouse, UseWOCompletionWarehouse, ApplyReorderLevelAfterMaximum, ReplenishmentHorizonDays, BuiltBoughtDefaultMake FROM MseStockItem m, StockItem s	    WHERE m.StockCode = s.Code;
			END
END
GO
-- Synchronises the contents of the MseWarehouseItem & MFGWarehouseItem tables.
-- First make sure we have entries to copy in the MseWarehouseItem table.
IF EXISTS(SELECT * FROM MseWarehouseItem)
BEGIN
	-- Now make sure that we have an MFGWarehouseItem table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGWarehouseItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGWarehouseItem entries.
		DELETE FROM MFGWarehouseItem
		-- Copy all the rows from MseWarehouseItem into MFGWarehouseItem
		INSERT INTO MFGWarehouseItem(MFGWarehouseItemID, WarehouseItemID, IsDefaultWorksOrderWarehouse )
		SELECT MseWarehouseItemID, WarehouseItemID, IsDefaultWorksOrderWarehouse FROM MseWarehouseItem;
			END
END
GO
-- Synchronises the contents of the MseProductGroup & MFGProductGroup tables.
-- First make sure we have entries to copy in the MseProductGroup table.
IF EXISTS(SELECT * FROM MseProductGroup)
BEGIN
	-- Now make sure that we have an MFGProductGroup table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGProductGroup]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGProductGroup entries.
		DELETE FROM MFGProductGroup
		-- Copy all the rows from MseProductGroup into MFGProductGroup
		INSERT INTO MFGProductGroup(MFGProductGroupID, ProductGroupID, MFGContactID, UseDemandWarehouse, UseWOComponentWarehouse)
		SELECT MseProductGroupID, ProductGroupID, MseContactID, UseDemandWarehouse, UseWOComponentWarehouse FROM MseProductGroup;
			END
END
GO
-- Synchronises the contents of the MseWarehouse & MFGWarehouse tables.
-- First make sure we have entries to copy in the MseWarehouse table.
IF EXISTS(SELECT * FROM MseWarehouse)
BEGIN
	-- Now make sure that we have an MFGWarehouse table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGWarehouse]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGWarehouse entries.
		DELETE FROM MFGWarehouse
		-- Copy all the rows from MseWarehouse into MFGWarehouse
		INSERT INTO MFGWarehouse(MFGWarehouseID, WarehouseID, IsComponentSource, MFGContactID)
		SELECT MseWarehouseID, WarehouseID, IsComponentSource, MseContactID FROM MseWarehouse;
			END
END
GO
-- Remove any duplicate entries fromt the MFGComponentWarehouses table.
DELETE FROM MFGComponentWarehouses
WHERE MfgComponentWarehousesID IN(
	SELECT MfgComponentWarehousesID FROM
	(
		SELECT     (ROW_NUMBER() OVER(PARTITION BY ParentWarehouseID, ChildWarehouseID ORDER BY MfgComponentWarehousesID)) AS ROWID,
		 MfgComponentWarehousesID, ParentWarehouseID, ChildWarehouseID
		FROM         MFGComponentWarehouses
	) A WHERE ROWID != 1)
	GO