How do I investigate issues with traceable items, i.e. if a batch number is not able to dispatch or a serial number not available to allocate?

This could indicate a possible mismatch or anomaly somewhere in SQL.

Firstly, gather all the relevant information from the front end of the software (details about quantity in stock for the overall item, the bin and the batch/serial number etc.).

Then, view the item in the relevant SQL tables:

1. Backup and restore the database offsite.
2. Run Balance ledgers to see if this corrects the item.
3. View the item on the following SQL tables:
   StockItem
   BinItem
   WarehouseItem
   MovementBalance
   TraceableItem
   TraceableBinItem
   TraceableMovementBal (for actual cost items)
   
   

   TIP: To help with finding reserved stock in these locations, there is a script that can be run here

4. Look for discrepancies in figures compared to the front end of the software.
5. Look for quantity reserved figures (as they should not exist if off-site).
6. When looking in SQL compare a working traceable item to a non-working traceable item if necessary.

It is also worth running SQL profiler for the process you are trying to complete (for a working item and a non-working item) and then checking the corresponding tables in SQL.

Further advise on correcting batch\serial stock levels. Read more>