x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 16 subscribers
  • Views 1065 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
SAMUEL MAIA

BOM Bug

SOLVED
Posted By

Hi Sage 200 Support

I have been asking for this since we migrated from Sage 50 to Sage 200. 

Is this ever going to be fixed? If I am not mistaken this has been like this for years. The last answer was given to my BP:

"This is in the list of higher priority bugs that I have asked R&D to review for potentially fixing as part of the development work in the future release of Sage 200"

This is something critical to my company!

Looking forward to hearing something from Sage. 

Parents
  • +1
    verified answer

    Hi all

    After a lot of insistence, Sage has finally sorted this out for me. 

    I will share the solution with the comunity.

    1 - MSEStockItem - Remove Duplicates

     

    Delete from MseStockItem Where MseStockItemID in
(
                select MseStockItemID from
                (
                                select B.MseStockItemID, ROW_NUMBER() OVER (PARTITION BY B.StockCode ORDER BY B.DateTimeCreated) AS RowID from MseStockItem B
                ) A
                where A.RowID > 1
)

    2 - MSEWarehouseItem - Remove Duplicates

    Delete from MseWarehouseItem Where MseWarehouseItemID in
(
                select MseWarehouseItemID from
                (
                                select B.MseWarehouseItemID, ROW_NUMBER() OVER (PARTITION BY B.WarehouseItemID ORDER BY B.DateTimeCreated) AS RowID from MseWarehouseItem B
                ) A
                where A.RowID > 1

    3- Set Missing Default Warehouse

    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

Reply
  • +1
    verified answer

    Hi all

    After a lot of insistence, Sage has finally sorted this out for me. 

    I will share the solution with the comunity.

    1 - MSEStockItem - Remove Duplicates

     

    Delete from MseStockItem Where MseStockItemID in
(
                select MseStockItemID from
                (
                                select B.MseStockItemID, ROW_NUMBER() OVER (PARTITION BY B.StockCode ORDER BY B.DateTimeCreated) AS RowID from MseStockItem B
                ) A
                where A.RowID > 1
)

    2 - MSEWarehouseItem - Remove Duplicates

    Delete from MseWarehouseItem Where MseWarehouseItemID in
(
                select MseWarehouseItemID from
                (
                                select B.MseWarehouseItemID, ROW_NUMBER() OVER (PARTITION BY B.WarehouseItemID ORDER BY B.DateTimeCreated) AS RowID from MseWarehouseItem B
                ) A
                where A.RowID > 1

    3- Set Missing Default Warehouse

    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

Children
No Data

*Community Hub is the new name for Sage City