If you fully utilize the Order Entry, AR, and AP features of SQL-Ledger, the chance is that the onhand count in your parts list is incorrect. For example, you can close a sales order due to cancellation, but then you have forgotten to remove the quantity in the ship column. Now, you will find that the onhand count in your parts list is off by one, and you will have an unaccounted for item in the warehouse that more than likely isn’t going to add to your revenues.
So, here is the SQL that we run routinely to discover errors within the onhand count in our parts list:-
SELECT a.id, a.partnumber, a.assembly, a.onhand – b.onhand AS error
FROM (SELECT c.id, c.partnumber, c.assembly, -sum(round(cast(d.qty as numeric), 2)) AS onhand
FROM invoice d, parts c
WHERE c.id = d.parts_id
AND c.inventory_accno_id IS NOT NULL
GROUP BY 1, 2, 3) a, parts b
WHERE a.id = b.id
AND a.onhand – b.onhand <> 0;
Basically, we counted everything that we have bought and sold, so the balance if it isn’t equal to the onhand count then it will be revealed in this query–don’t forget to deduct shipped items on open sales orders.
I hope this is helpful, and if your business, like our, depends on real-time inventory check, then keeping your onhand count “tight” is a great idea.