This page describes the process to recreate Part Usage Cards. This may be helpful for a specific order or for an entire system in order to correct errors in the historic inventory valuations. It should ONLY be used by a highly skilled technician as real data is permanently created and there are financial ramifications of the changes.

This process will delete all the affected Part Usage Cards, their related Journal entries, and the related GL entries for inventory and expenses from parts. It will then recreate these entries and they will be backdated to the appropriate date.

For orders that are in a Sale or Closed status, they must first be changed as Built (unless they Built Date equals the Sale Date) so that the transfer is recorded into the Cost of WIP/Built GL Account. Those entries are then backdated to the sale date.

Those orders that were Sale or Closed are changed to the final status. An Edit/Save is run on all of them to record the Expenses. Those entries and then backdated to the Sale Date.

The following caveats should be understood before running this sequence:

• Timing. All expenses and inventory transfers are recorded on the Sale Date and Built Date, respectively. If the order was edited and changed while in Built or Sale, the expenses and inventory movements associated changes are still put on the original sale or built date.
• Historic Costs. Since all Part Usages are recalculated, they will be recalculated with the latest cost and not the cost that they may have been at the time the order was originally built or marked a sale.
• Reserved Usage Cards. The reserved usage cards (from WIP Orders) are not recreated from this process. The reserved numbers will still be correct, though the wrong cost may still show on the activity. However, the reserved numbers do not appear in the GL and are for tracking only.
• Historic Reserved Usage Cards. Orders that are in Built, Sale, or Closed will no longer have the reserved usage cards showing that inventory was reserved while the order was in WIP. However, the reserved numbers do not appear in the GL and were for tracking only. Once the order is Built, Sale, or Closed these numbers were reversed anyway.
• Voided Orders. Voided orders are left alone in this process. Any actual parts recorded are left intact. (No changes are made to these orders.)

• MAKE SURE NO ONE ELSE IS RUNNING CONTROL WHEN THESE QUERIES ARE RUN!!. It is recommended you add a port offset to prevent other users from running.
• Create a table of Orders to modify. The actual orders will vary by customer, but this allows the rest of the SQLs to not change.

SELECT ID AS TransHeaderID,
    StatusID,
    StatusText,
    BuiltDate,
    SaleDate,
    ClosedDate
INTO RecreatedPartOrders
FROM TransHeader
WHERE ( TransactionType IN (1,6) )  -- include Orders and Service Tickets
      AND ( StatusID NOT IN (1,9) ) -- skip WIP and voided orders
      ... 
;
-- For speed, create an index on this table
CREATE INDEX TempIndex ON RecreatedPartOrders (TransHeaderID)
;
-- Store the Last Journal ID so we can use it to determine what changes were made
SELECT MAX(ID) AS JournalID, 'MaxPreBuilt' AS Description
INTO RecreatedPartJournalIDs
FROM Journal
;

• Delete All Usage Cards for these orders

DELETE
FROM PartUsageCard
WHERE TransHeaderID IN (SELECT TransHeaderID FROM RecreatedPartOrders)
;

• Delete the Inventory Log and Part Usage Activities (Split Table)

DELETE FROM InventoryLog
WHERE ID IN (SELECT ID FROM Journal WHERE ClassTypeID = 20520 
             AND TransactionID IN (SELECT TransHeaderID FROM RecreatedPartOrders))
;
DELETE FROM Journal
WHERE ClasstypeID = 20520
  AND TransactionID IN (SELECT TransHeaderID FROM RecreatedPartOrders)
;

• Delete GL entries associate with Parts. This will delete the GL entries for those orders associated with:
  • Inventory
  • Cost of WIP/Built
  • Expenses

DELETE FROM Ledger
WHERE TransactionID IN (SELECT TransHeaderID FROM RecreatedPartOrders)
  AND PartID IS NOT NULL
;

• Recalculate the TransPart amounts based on the current Cost of the Part

UPDATE TransPart
SET ___ = Quantity * (GET part cost)
WHERE TransHeaderID IN (SELECT TransHeaderID FROM RecreatedPartOrders)
;

• Rebuild the Inventory Quantities from the remaining

• Set All of the Orders to Built

UPDATE TransHeader
SET StatusID = 2, StatusText = 'Built', SaleDate = NULL, ClosedDate = NULL,
    BuiltDate = '1/1/2050'  -- set the built date far in the future so we can identify these orders
WHERE ( ID IN (SELECT TransHeaderID FROM RecreatedPartOrders) )

• Recreate the Part Usage Cards for these orders
  • Run Control in IDE mode on the server computer (for speed reasons).
  • Open Order Explorer
  • Go to the advanced screen and select all orders with Built Date equal to '1/1/2050'
  • Refresh. Select to show 9999 records.
  • Verify that all of the orders are showing and only the orders.
  • Click Select All so that all records are selected.
  • Click on Action | to begin the process.
  • Wait and monitor. This may take a while. (Estimated ~10 orders per minute.)
• Reset the Built Date on these orders

UPDATE TransHeader
SET BuiltDate = (SELECT R.BuiltDate FROM RecreatedPartOrders R WHERE R.TransHeaderID = ID),
    StatusText = 'Built'
WHERE ID IN (SELECT TransHeaderID FROM RecreatedPartOrders)

• Backdate all of these transactions to the built date

UPDATE Journal
SET CompletedDateTime = (SELECT BuiltDate FROM TransHeader TH WHERE TH.ID = Journal.TransHeaderID)
WHERE ID > (SELECT JournalID FROM RecreatedPartJournalIDs WHERE Description = 'MaxPreBuilt')
;
-- now update all the rest of the Journal Dates
UPDATE Journal
SET  = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime
WHERE ID > (SELECT JournalID FROM RecreatedPartJournalIDs WHERE Description = 'MaxPreBuilt')
;
-- now update the GL
UPDATE GL
SET EntryDateTime = (SELECT BuiltDate FROM TransHeader TH WHERE TH.ID = GL.TransactionID)
WHERE TransactionID IS NOT NULL
  AND JournalID > (SELECT JournalID FROM RecreatedPartJournalIDs WHERE Description = 'MaxPreBuilt')
;

• Reset the orders to Sale that should be Sale

UPDATE TransHeader
SET StatusID = 3, StatusText = 'Sale', ClosedDate = NULL
    SaleDate = '1/1/2050'  -- set the sale date far in the future so we can identify these orders
WHERE ID IN (SELECT TransHeaderID FROM RecreatedPartOrders WHERE StatusID IN (3,4))
;
-- Store the Last Journal ID so we can use it to determine what changes were made
SELECT MAX(ID) AS JournalID, 'MaxPreSale' AS Description
INTO RecreatedPartJournalIDs
FROM Journal
;

• Recalculate the GL
  • Run Control in IDE mode on the server computer (for speed reasons).
  • Open Order Explorer
  • Go to the advanced screen and select all orders with Sale Date equal to '1/1/2050'
  • Refresh. Select to show 9999 records.
  • Verify that all of the orders are showing and only the orders.
  • Click Select All so that all records are selected.
  • Click on Action | to begin the process.
  • Wait and monitor. This may take a while. (Estimated ~10 orders per minute.)
• Reset the Sale Date on these orders

UPDATE TransHeader
SET SaleDate = (SELECT R.SaleDate FROM RecreatedPartOrders R WHERE R.TransHeaderID = ID),
    ClosedDate = (SELECT R.ClosedDate FROM RecreatedPartOrders R WHERE R.TransHeaderID = ID),
    StatusText = (SELECT R.StatusText FROM RecreatedPartOrders R WHERE R.TransHeaderID = ID)
WHERE ID IN (SELECT TransHeaderID FROM RecreatedPartOrders AND StatusID IN (3,4))
;

• Backdate all of these transactions to the sale date

UPDATE Journal
SET CompletedDateTime = (SELECT SaleDate FROM TransHeader TH WHERE TH.ID = Journal.TransHeaderID)
WHERE ID > (SELECT JournalID FROM RecreatedPartJournalIDs WHERE Description = 'MaxPreSale')
;
-- now update all the rest of the Journal Dates
UPDATE Journal
SET  = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime,
     = CompletedDateTime
WHERE ID > (SELECT JournalID FROM RecreatedPartJournalIDs WHERE Description = 'MaxPreSale')
;
-- now update the GL
UPDATE GL
SET EntryDateTime = (SELECT SaleDate FROM TransHeader TH WHERE TH.ID = GL.TransactionID)
WHERE TransactionID IS NOT NULL
  AND JournalID > (SELECT JournalID FROM RecreatedPartJournalIDs WHERE Description = 'MaxPreSale')
;

• Rebuild the Final Inventory Quantities

• Entered : 3/30/2010
• Version : Control 4.4+