These are the steps to repair the data for users with incorrect Direct Costs from Bills. The total amount of all Ledger entries for Direct Cost from Bills should always equal zero.

This repair will only make adjustments to Direct Costs entries attributed to orders and bills. If there are any manual entries, the direct costs amount still be incorrect. The user will have to make adjusting entries in this case.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

Make sure the user is running the most current release of Control before beginning this repair.

Download this file:

direct_costs_fix.rar

• Breakdown.sql - A helpful query to view the current state of the repair. It will be explained more below.
• Repair.sql - A query will find broken links between Orders and P.O.s/Bills/R.D.s and repair the link whenever possible or remove the link if not. A summary of the corrections is returned when completed. If the same corrections are made to a database, that indicates a bug.
• Prep For Refresh GL1.sql - A query to flag all bills that will need the “Refresh GL” to run execute on.
• Prep For Refresh GL2.sql - A query to flag all orders that will need the “Refresh GL” to run execute on.
• Manual Direct Cost Entries.sql - A query to view any manual journal entries that contain Direct Costs from Bills. This is helpful when the user has entered adjustments that makes the Direct Costs amount total something other than zero. You can give the user the results of this query so they can make new adjustments.
• DirectCosts OOBs.sql - A query to view the TransParts that currently have a Direct Costs balance that is not zero.

Here are the steps that need to be executed to repair the Direct Costs amounts.

You can use the Breakdown.sql query to monitor the progress.

Rows returns by Breakdown.sql

• GL Amount with Transaction: The current sum of all direct costs in the Ledger associated with an order or bill. When all is well, this number will always be zero.
• GL Amount without Transaction: The current sum of all direct costs in the Ledger not associated with an order or bill. This should never be anything but zero. If it is not zero, then, most likely, the user has manually entered ledger entries for Direct Costs.
• TransHeader Count: The count of orders and bills flagged to have the Refresh GL run.
• Detail OOB Count: The count of Bill Details that have a cost that does not match the Total Part Usage cost of it's TransPart.
• GL OOB Count: The count of TransParts that have a Direct Cost Balance that is not zero.

Please note: If you are monitoring the breakdown as the repairs are running, it is not unusual for the discrepancy to get larger as repairs are made.

1. Execute the Repair.sql query.
2. Run the Control in IDE mode.
3. Execute the Prep For Refresh GL1.sql query to flag the Bills.
4. In Control, go to Bill Explorer in Advanced Mode.
   1. Click Modify Criteria.
   2. Clear the Criteria
   3. Under Bill Criteria, add where the Memo contains !!cyrious!!
5. Refresh the results and show all rows.
6. Under Action, click Refresh GL on these Transactions. After it is complete, leave the Bill Explorer open, because it may be used in the future.
7. Shut down Control
8. Execute the Prep For Refresh GL2.sql query to flag the Orders not corrected by any Bills.
9. In Control, go to Order Explorer in Advanced Mode.
   1. Click Modify Criteria.
   2. Clear the Criteria
   3. Under Order Criteria, add where the Order Notes contains !!cyrious!!
10. Refresh the results and show all rows.
11. Under Action, click Refresh GL on these Transactions. After it is complete, leave the Order Explorer open, because it may be used in the future.
12. Execute Breakdown.sql, verify that “GL Amount with Transaction” is zero, if not, repeat all of these steps starting at step 1. It has been necessary to do these steps twice, but never more than that. If “GL Amount with Transaction” is still not zero after 2 times, notify someone in Development about the situation. Be sure to shut down Control before running the Repair query.

Shutting down Control before running a query ensures that cached data is updated.

When the repair is complete, all of the rows of Breakdown.sql will show zero.

If the “GL Amount without Transaction” value is not zero, execute Manual Direct Cost Entries.sql and give the user the results and explain to them that they will need to make some adjustments to reverse their past adjustments.

• Entered : 07/12/2012
• Version :