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 dataDECLARE @SourceID bigint-- Declare and open cursorDECLARE dataCursor CURSOR FOR SELECT ProductGroupID FROM ProductGroup WHERE (ProductGroupID NOT IN (SELECT ProductGroupID FROM MseProductGroup))OPEN dataCursor -- Process dataFETCH Next FROM dataCursor INTO @SourceIDWHILE @@FETCH_STATUS = 0BEGIN	-- 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 @SourceIDEND-- Close and deallocate cursorCLOSE dataCursorDEALLOCATE dataCursorGO--************************ MseStockItem ************************-- Declare variables to hold source dataDECLARE @SourceCode varchar(255)DECLARE @BomItemTypeID bigint-- Declare and open cursorDECLARE dataCursor CURSOR FOR SELECT Code, BomItemTypeID FROM StockItem WHERE (Code NOT IN (SELECT StockCode FROM MseStockItem))OPEN dataCursor -- Process dataFETCH Next FROM dataCursor INTO @SourceCode, @BomItemTypeIDWHILE @@FETCH_STATUS = 0BEGIN	-- 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, @BomItemTypeIDEND-- Close and deallocate cursorCLOSE dataCursorDEALLOCATE dataCursorGO--************************ MseWarehouse ************************-- Declare variables to hold source dataDECLARE @SourceID bigint-- Declare and open cursorDECLARE dataCursor CURSOR FOR SELECT WarehouseID FROM Warehouse WHERE (WarehouseTypeID NOT IN (2,3)) AND (WarehouseID NOT IN (SELECT WarehouseID FROM MseWarehouse))OPEN dataCursor -- Process dataFETCH Next FROM dataCursor INTO @SourceIDWHILE @@FETCH_STATUS = 0BEGIN	-- 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 @SourceIDEND-- Close and deallocate cursorCLOSE dataCursorDEALLOCATE dataCursorGO--************************ MseWarehouseItem ************************DECLARE @Counter bigintSET @Counter = (SELECT TOP 1 NextValue FROM Counter)UPDATE Counter SET NextValue = NextValue + 1INSERT 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 WarehouseItemDECLARE @ItemID bigintOPEN itemCursorFETCH Next FROM itemCursor INTO @ItemIDWHILE @@FETCH_STATUS = 0BEGIN	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 @ItemIDEND-- Close and deallocate cursorCLOSE itemCursorDEALLOCATE itemCursorGO
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;			ENDENDGO-- 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;			ENDENDGO-- 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;			ENDENDGO-- 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;			ENDENDGO-- Remove any duplicate entries fromt the MFGComponentWarehouses table.DELETE FROM MFGComponentWarehousesWHERE 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