For the purpose of the examples, where on certain stock codes there are discrepancies between the actual Stock level and number of traceable items (Batch numbered items) it will be assumed that the (Computer) Stock Level / Free Stock Available is correct. There will be 2 basic types of examples:

1. Where the Stock Level is less than the total number of batches.
2. Where the Stock Level is greater than the total number of batches.

Where the Stock Level is less than the number of batches

Follow these steps:

1. In Sage 200 follow these steps: search for the Stock Control > Enquiries > View Stock Item Balances form (this form shows the quantity of stock from a non traceable table in the data) > select relevant stock code > Batch No Details > Make a note of the batch numbers which need to be adjusted and by how much. A stock take may be required to get correct quantities.
2. In SQL Server Management Studio access the Stock item table for each Stock Code from the Code column and make a note of its ItemID.
3. Access the Traceable Item table and use the following criteria to search for batches to adjust:
   • Identification No = Batch Number Details noted earlier
   • StockItemID = ItemID noted earlier
4. Increase the 'Goods Out Quantity' by the required amount.

   If there are more than one occurrence of the same Batch Number, you may need to scroll to find the relevant line to adjust.

5. Make a note of the TraceableItemID for the line you adjust.
6. Access the TraceableBinItem table and use the TraceableItemID to search for batches to adjust.
7. Increase the Goods Out Quantity by the required amount.

Where the Stock Level is greater than the number of batches

There are 2 possible reasons for this scenario:

1. The original goods in quantity was incorrect.
2. The system crashed during the Confirm Despatch process.

Only a discussion with the user of the system will establish which of these are most likely, based on the information found do the following:

If an original Goods In Quantity was incorrect

1. Using the tools in Stock Control > Enquiries, it is possible to look at the balance of stock (View Stock Item Balances) and the activity (View Stock Item History) to compare the good in quantity to the balance showing on the stock record. There is also a form (View Batch/Serial No Details) which will help you find which Batch or Serial numbers will need to be adjusted and by how much by drilling down to the details of the original Purchase Order if you are using Batch or Serial number traceability. A stock take may be required to get a handle on which quantities are correct.
2. In SQL Server Management Studio access the StockItem table for each Stock Code from the Code column and make a note of its ItemID.
3. Access the Traceable Item table and use the following criteria to search for batches to adjust:
   • Identification No = Batch Number Details noted earlier
   • StockItemID = ItemID noted earlier
4. Increase the goods in quantity by the required amount.

   If there are more than one occurrence of the same Batch Number, you may need to scroll to find the relevant line to adjust.

5. Make a note of the TraceableItemID for the line you adjust.
6. Access the TraceableBinItem table and use the TraceableItemID to search for batches to adjust.
7. Increase the goods in quantity by the required amount.

If a batch has been despatched in error

1. In Sage 200 search for View Batch/Serial No Details form (this form shows the quantity of stock from the traceability tables) > select relevant stock code > Make a note of the batch numbers which need to be adjusted and by how much. A stock take may be required to get correct quantities.
2. In SQL Server Management Studio access the Stock Item table for each Stock Code from the Code column and make a note of its ItemID.
3. Access the TraceableItem table and use the following criteria to search for batches to adjust:
   • Identification No = Batch Number Details noted earlier
   • StockItemID = ItemID noted earlier
4. Decrease the goods out quantity by the required amount.

   If there are more than one occurrence of the same Batch Number, you may need to scroll to find the relevant line to adjust.

5. Make a note of the TraceableItemID for the line you adjust.
6. Access the TraceableBinItem table and use the TraceableItemID to search for batches to adjust.
7. Decrease the goods out quantity by the required amount.

If you get errors when running balance ledgers

You may receive the following error when running balance ledgers:

The quantity of serial/batch numbers held at "Warehouse XYZ" for “Stock Code ABC” does not match the quantity in stock. This cannot be corrected automatically.

The problem can sometimes be with the MovementBalance table. Below are some simple worked examples with their potential fixes. If you encounter anything not listed here please contact the Sage 200 Manufacturing Support line for further assistance.

The MovementBalance lines with 1 or 3 in MovementBalanceTypeID, should not be edited or deleted. This would cause problems with confirmed cost calculations later in the manufacturing process. If you find these lines are causing the problem contact Sage Technical Support.

The MovementBalance lines with 2 in MovementBalanceTypeID may have links to lines in the StockHistoryShortfall and SOPDespatchReceiptLineShortfall tables. The lines in these tables may need to be removed before deleting the line in MovementBalance table.

Example A

If there is not supposed to be anything in stock but you find an entry within MovementBalance. As there is no stock, there does not need to be any entry in this table.

1. Remove the relevant MovementID entry.
2. Rerun balance ledgers which will now give a fixable error.

Example B

If the stock item has a costing method of 0 (FIFO), 1 (Average) or 2 (Standard) in its product group then the following method may help you track your way through the tables to identify what is missing.

If there is supposed to be 10 in stock for example, but the entries in MovementBalance table add up to 20.

1. Start by making a note of each MovementBalance.MovementID and MovementBalance.BinItemID for the stock code you are investigating. It will most likely be the earliest line which is causing the problem.
2. For each MovementBalance.BinItemID there should be a corresponding TraceableBinItem.BinItemID. Note: there may be a one-to-many relationship.

If you can't find a line in the TraceableBinItem table then consider deleting the corresponding line in MovementBalance provided the MovementBalanceTypeID is not 1 or 3. If it is 1 or 3 then contact Sage Technical Support for further assistance.

Example C

If the stock item has a costing method of 3 (actual) in its product group there may have entries in the TraceableMovementBal table to consider. The following method may help you track your way through the tables to identify what is missing.

There is supposed to be e.g. 10 in stock, but the entries in MovementBalance table add up to 20.

The following method may help you track your way through the tables to identify what is missing.

1. Start by making a note of each MovementBalance.MovementID and MovementBalance.BinItemID for the stock code you are investigating. It will most likely be the earliest line which is causing the problem.
2. For each MovementBalance.BinItemID there should be a corresponding TraceableBinItem.BinItemID, make a note of the TraceableBinItem.TraceableBinItemID. Note: there may be a one-to-many relationship.
3. Now that you have a list of MovementBalance.MovementID and TraceableBinItem.TraceableBinItemID there should be corresponding entries TraceableMovementBal.MovementBalaceID and TraceableMovementBal.TraceableBinItemID in the TraceableMovementBal table respectively.

If these lines do not exist in TraceableMovementBal table, and the MovementBalanceTypeID is not 1 or 3, then the corresponding line from the MovementBalance table should be deleted. Verify the data using balance ledgers within Sage 200 to resolve any outstanding discrepancies.

Sometimes you get an error in Prove Balances (commercials) where the cause is difficult to find. Use the View Buying Price screen to see the prices a stock item was bought at. This screen allows you to drill down to the warehouse and bin level. The information on the Receipts for Item screen may not add up to the quantities on the View Buying Price screen. Correcting this discrepancy may solve the error in prove balances.