Explanation
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.
Summary of Steps
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.
Risks
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.)
Step by Step
- 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
Version Information
- Entered : 3/30/2010
- Version : Control 4.4+