1. Home
  2. Point-Of-Sale Software
  3. HQ and SO Item Reconcilliation

HQ and SO Item Reconcilliation

A new customer that has been using RMS for about a year asked me to look into a problem they were having. Item inventory was never correct, duplicate items would appear and dissapear with no apparent reason, and purchase order would not be generated for items that were below the restock level.

I did some research and found that a large portion of their Items were created in StoreOps after having started using HQ. When they wouldn't shouw up in HQ, they would add some items there as well. What you end up with is items with the same LookupCode, but with different ID numbers and many at the StoreOps level with no HQID.

Microsoft suggest moving the items by hand, but there are about 9000 items for each of the five stores that have no HQIDs. So I have written a procedure that I think will resolve the problem. The only caveat is that their historical data and outstanding POs will no longer be accurate as they will be referencing ItemsIDs that probably no longer exist.

I'd like this community to review my procedure and give me some feedback before I implement it.

Procedure:

  1. Backup HQ and SO databases
  2. Export the entire Items table from SO, but only the fields ItemLookupCode and Quantity. This data will later be used to correct the on hand quantities.
  3. Run a query on SO to present all Items with a "0" (zero) in the "HQID" field. These are the items that were created in Store Operations and not in Headquarters.
  4. Export the above query as a text file including only the necessary fields.
  5. Use the QSImport Utility to import the resultant text file into HQ. Many of these items will not import as they will be duplicates of existing ItemLookupCodes. This is irrelevant for our purposes.
  6. Erase the entire contents of the Items table in SO.
  7. Perform a Worksheet 260 to download the complete item list from HQ into SO.
  8. Create a new Physical Inventory in StoreOpeartions Manager importing the text from step #2 to reset all of the store quantities to what they were before.
  9. From Headquarters Manager issue a Worksheet 501.
  10. Process the Worksheet 501 on SO using the Headquarters Client .
  11. Run a Task 190 from Headquarters Manager to reconcile the inventory counts and commit the values from StoreOperations.
  12. Repeat this procedure for each additional store.
Reply to
manicsquirrel
Loading thread data ...

I don't think your process will work. Here's how I would do it. I've also seen someone mention using WS101 to sync ILCs, though the documentation says you should only use that WS one time per store. You might wait a while to see if anyone else offers a better plan.

Here goes...

Use a series of INSERT INTO statements to add the 'missing' items to the HQ Item table. To accomplish this:

*Be careful upening .csv files with Excel. Leading zeroes can be lost. If you have leading zeroes you want to keep, open the .csv file with MS Word, copy and paste into Excel, use Date|Text to Columns... to separate the fields, being careful to select the Text format for the ILC column.

Export the entire HQ Items table. Export the entire SO Items table. Get the data to Excel. WORK ON A COPY OF YOUR DATA. You can delete or collapse unwanted columns.

In Excel, use VLOOKUP to find the new HQID for the items where HQID = 0. Use a series of UPDATE queries to associate the existing items with the proper HQIDs.

Create your query statements using text funcions in Excel. There are lots of examples on this newsgroup. If you need more help, let me know.

Use this query to update the ItemDynamic table: INSERT INTO ItemDynamic (itemid, StoreID, quantitycommitted, quantity, ReorderPoint, RestockLevel, SnapShotQuantity, SnapShotQuantityCommitted) SELECT Item.ID as ItemID, store.id as StoreID, 0 as quantitycommitted, 0 as quantity, 0 as ReorderPoint, 0 as RestockLevel, 0 as SnapShotQuantity, 0 as SnapShotQuantityCommitted FROM Item INNER JOIN Store on Item.ID=Item.ID LEFT JOIN ItemDynamic on Item.ID=ItemID and Store.ID=StoreID WHERE StoreID is NULL

(I got that from this group, too).

Do your WS501, Inventory Task 190 routine.

If you do it this way, you won't lose your store history.

"manicsquirrel" wrote:

GOOD IDEA

Export the ENTIRE table. You can manipulate it in Excel easily enough.

OK.

I'm not sure that's a good idea. QSCimport is not intended for use with HQ. A series of INSERT INTO query statements might be better.

I'd advise against that.

You just lost all of the history at the stores!

Reply to
Terrible Tom

Tom,

Thanks for your advice. I finally got a procedure worked out to sync the items tables between HQ and SO. However, because of all the existing duplicates, inventory count has to be a casualty. If two entries are the same but the QTY is different I cannot know which count is correct. They can perform an inventory count again.

The only thing that leaves me worried is the ItemID reference in the Transaction Entry table. If I could create a cross reference of ItemlookupCode, oldID, newID, I could write a query to update all those entries to reference the new ItemIDs.

Hmmmmm...

Anyway, I'll post the full procedure here for some> I don't think your process will work. Here's how I would do it. I've also

Reply to
manicsquirrel

Here's a summary of my method to reconcile Items created in StoreOperations instead of Headquarters. Everything seems to work great. The one casualty is the inventory count. Because there are so many duplicates and new items coming from HQ, I cannot know which is the correct count. I hope this helps someone else in the future. I'm soon to be performing a similar process on Suppliers and Customers. The SQL script details are listed below:

  1. Run Copy Table and Remove Duplicate Items.sql
  2. Perform WS101 Sync
  3. Perform WS250
  4. Perform WS401

--------------------------

  1. Run Correct Orphaned ItemID records.sql
  2. Perform WS101 Sync
  3. Perform WS250
  4. Perform WS401

--------------------------

  1. Perform WS501
  2. Perform Task 190
  3. Commit Store counts to HQ

Repeat Process for each store.

/ *******************************************************************************/ /* Copy Table and Remove Duplicate Items.sql / *******************************************************************************/

--copy Items table for later use SELECT * INTO ItemCopy FROM Item

-- add a new column ALTER TABLE Item ADD NewPK int NULL go

-- populate the new Primary Key DECLARE @intCounter int SET @intCounter = 0 UPDATE Item SET @intCounter = NewPK = @intCounter + 1

-- ID the records to delete and get one primary key value also

-- We'll delete all but this primary key SELECT ItemLookupCode, RecCount=count(*), PktoKeep = max(NewPK) INTO #dupes FROM Item GROUP BY ItemLookupCode HAVING count(*) > 1 ORDER BY count(*) DESC, ItemLookupCode

-- delete dupes except one Primary key for each dup record DELETE Item FROM Item a join #dupes d ON d.ItemLookupCode = a.ItemLookupCode WHERE a.NewPK not in (SELECT PKtoKeep FROM #dupes)

-- remove the NewPK column ALTER TABLE Item DROP COLUMN NewPK go

DROP TABLE #dupes

/ *******************************************************************************/ /* Correct Orphaned ItemID records.sql / *******************************************************************************/

--copy the orphaned Alias ids to a temp table SELECT DISTINCT Alias.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM Alias LEFT JOIN Item ON Alias.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the Alias table with corrected itemIDs UPDATE Alias SET ItemID=b.newID FROM Alias a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

--copy the orphaned InventoryTransferLogids to a temp table SELECT DISTINCT InventoryTransferLog.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM InventoryTransferLog LEFT JOIN Item ON InventoryTransferLog.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the InventoryTransferLog table with corrected itemIDs UPDATE InventoryTransferLog SET ItemID=b.newID FROM InventoryTransferLog a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

--copy the orphaned OrderEntryids to a temp table SELECT DISTINCT OrderEntry.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM OrderEntry LEFT JOIN Item ON OrderEntry.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the OrderEntry table with corrected itemIDs UPDATE OrderEntry SET ItemID=b.newID FROM OrderEntry a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

--copy the orphaned SupplierList ids to a temp table SELECT DISTINCT SupplierList.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM SupplierList LEFT JOIN Item ON SupplierList.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the SupplierList table with corrected itemIDs UPDATE SupplierList SET ItemID=b.newID FROM SupplierList a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

--copy the orphaned TransactionEntry ids to a temp table SELECT DISTINCT TransactionEntry.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM TransactionEntry LEFT JOIN Item ON TransactionEntry.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the TransactionEntry table with corrected itemIDs UPDATE TransactionEntry SET ItemID=b.newID FROM TransactionEntry a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

--copy the orphaned TransactionHoldEntry ids to a temp table SELECT DISTINCT TransactionHoldEntry.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM TransactionHoldEntry LEFT JOIN Item ON TransactionHoldEntry.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the TransactionHoldEntry table with corrected itemIDs UPDATE TransactionHoldEntry SET ItemID=b.newID FROM TransactionHoldEntry a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

--copy the orphaned PurchaseOrderEntry ids to a temp table SELECT DISTINCT PurchaseOrderEntry.ItemID as oldID, Item.ItemLookupCode INTO #Orphaned FROM PurchaseOrderEntry LEFT JOIN Item ON PurchaseOrderEntry.ItemID = Item.ID WHERE ItemLookupCode is null GO

ALTER TABLE #Orphaned ADD newID int NULL GO

--using the previously saved ItemCopy find the ILC UPDATE #Orphaned SET ItemLookupCode=b.ItemLookupCode FROM #Orphaned a, ItemCopy b WHERE a.oldID = b.ID

--now match the ILC to the new ID UPDATE #Orphaned SET newID=b.ID FROM #Orphaned a, Item b WHERE a.ItemLookupCode = b.ItemLookupCode

--now update the PurchaseOrderEntry table with corrected itemIDs UPDATE PurchaseOrderEntry SET ItemID=b.newID FROM PurchaseOrderEntry a, #Orphaned b WHERE (a.ItemID = b.oldID) AND (b.newID IS NOT NULL )

DROP TABLE #Orphaned

Reply to
manicsquirrel

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.