Skip to content
logo Knowledgebase

Sage 200 Manufacturing - Steps to fix incorrectly closed Works Orders with missing nominal postings

Created on  | Last modified on 

Summary

This article takes you through the steps to fix incorrectly closed Works Orders with missing nominal postings.

Description

NOTE: This article is designed to guide Business Partners and not intended for general users of Sage 200.
The guidelines below reference the tables, fields and criteria to use, but assumes you know how the various tables link together or are comfortable finding this information using Microsoft SQL Server Management Studio.

If you are unsure of any of the instructions in this article please contact Sage 200 Manufacturing Support.

Resolution

Q. I have found a 'Works Order' which is closed but the expected nominal postings against 'Stock Issues', 'WIP' and 'Finished Stock Nominals' have not been posted. How can I reset the 'Works Order' back to a 'Complete' status? 

A. This can happen if your customer has changed the status of the 'Works Order' using an 'SQL' script rather than the Close form in Works Order > Processing.

  1. Ensure no one else is using the database.
  2. Take a backup of the data.
  3. Delete any lines in SOPDespatchReceiptLineShortfall, StockHistoryShortfall and MFGIssueShortfall related to 'MovementBalances' where BinItem.UnconfirmedQtyInStock is 0 and MovementBalance.MovementBalanceTypeID of either 1 or 2.
  4. Delete the lines from 'MovementBalance' where BinItem.UnconfirmedQtyInStock is 0 and MovementBalance.MovementBalanceTypeID of either 1 or 2.
  5. Pull together a list of invalid unconfirmed 'MovementBalances' where the 'Works Order' has been closed. Good criteria to use in the 'where' clause are WorksOrders.Status of closed and MovementBalanceTypeID of 1.
  6. Pull together a list of completed POP's with incomplete lines and use this list to reset the POPOrderReturn.DocumentStatusID to 0. Good criteria to use in the 'where' clause would be POPOrderReturn.DocumentStatusID is 2, POPOrderReturnLine.LineQuantity is greater than POPOrderReturnLine.InvoiceCreditQuantity and POPOrderReturnLine.LineTypeID is 0.
  7. Use the list from step 4 to produce a distinct list of POP's which need to be written off.
  8. Use the list from step 4 to update POPOrderReturn.DocumentStatusID to 0.

    These are the rules for updating the MovementBalance.CostPrice.

    • If the ProductGroup.CostingMethodID is 0 and BinItem.LastCostPrice is 0 then MovementBalance.CostPrice should be the same as StockItem.AverageBuyingPrice
    • If the ProductGroup.CostingMethodID is 0 and BinItem.LastCostPrice is not 0 then MovementBalance.CostPrice should be the same as BinItem.LastCostPrice
    • If the ProductGroup.CostingMethodID is 1 then MovementBalance.CostPrice should be the same as StockItem.AverageBuyingPrice
    • If the ProductGroup.CostingMethodID is 2 then MovementBalance.CostPrice should be the same as StockItem.StandardCost
    • If the ProductGroup.CostingMethodID is 3 then MovementBalance.CostPrice should be the same as StockItem.AverageBuyingPrice
  9. Where the WorksOrders.Status is closed and the MovementBalanceTypeID is 1 update MovementBalance.MovementBalanceTypeID to 0 and the MovementBalance.CostPrice using the rules above.
  10. Pull together a list of invalid unconfirmed MovementBalances where the 'POP' has been completed. Good criteria to use in the 'where' clause would be MovementBalance.MovementBalanceTypeID is 1, POPOrderReturn. DocumentStatusID is 2 and POPOrderReturn.DocumentTypeID is 0.
  11. Use the list from step 8 to update the MovementBalance.MovementBalanceTypeID to 0 and the MovementBalance.CostPrice using the rules above.
  12. Check in Sage 200 to ensure those 'Works Orders' which were identified as incorrectly closed are now showing as 'Complete'.
  13. Close the Works Orders in the normal manner.