# Prophet 21 shows negative on-hand inventory: why it happens and how to fix it > Source: https://netray.co/erp-help/p21-negative-inventory-on-hand-fix > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer Negative on hand in Prophet 21 means more was issued or shipped than was received into that item and location on the books. It is normally a sequencing problem, such as selling before receiving is posted, or a data problem, such as wrong location or unit of measure, rather than missing stock. Trace the transactions for the item and location, correct the root cause, then adjust and review cost. ## Kind error ## System epicor ## Erp Epicor Prophet 21 ## Module Inventory Management ## Query Prophet 21 negative inventory on hand how to fix ## Alternate Queries - P21 negative quantity on hand - Prophet 21 inventory shows negative after shipping - P21 on hand does not match physical count - why does P21 allow negative inventory ## Applies To Prophet 21 distribution installations. Window and table names below are indicative; confirm in your release before running queries against production. ## Steps ### Trace and correct a negative quantity **Items:** - Start with one item and one location. Use the item's inventory or transaction history inquiry to list every receipt, shipment, transfer and adjustment in date order. - Find the first transaction that took the balance below zero. That one, not the latest, is your clue. - Classify the cause: shipped before receipt was posted, receipt posted to a different location, a unit of measure mismatch, a transfer in transit, a return not received, or a manual adjustment error. - If stock physically exists, fix the books to match. Post the missing receipt or correct the wrong location with a transfer or correcting transaction, rather than adding a blind adjustment. - If stock physically does not exist, confirm with a physical count before adjusting. A count at the location is the audit-safe way to reset the quantity. - Review the cost impact. Items shipped at a zero or wrong cost while negative can leave cost of goods sold wrong, so involve accounting before closing the period. - Fix the process that caused it: receiving timeliness, location discipline, UOM setup, or a setting that allows selling below zero for items that should not. ## Sections ### Why distribution systems go negative **Body:** Distributors often sell what is on the dock before the receipt is keyed. The warehouse is right physically, but the system order of events is wrong: shipment first, receipt second. The balance goes negative for hours or days, then comes back to a correct positive. That is a timing issue, and the cure is faster receiving, not an adjustment. The more damaging cases are the ones that never come back. A receipt posted to location A while the item was sold from location B leaves A permanently high and B permanently negative, while the item total looks fine. Pick tickets and available-to-promise then become unreliable for both locations. Unit of measure is another quiet cause. Receiving a case quantity as eaches, or selling eaches against a stocking unit set up as cases, creates quantities that never reconcile. Check the item's UOM conversions whenever a negative looks wrong by a round multiple such as 12 or 100. ### Finding negatives in bulk **Body:** To see the extent of the problem, query inventory by location for quantities below zero. Table and column names vary by release, so have your DBA or consultant confirm them before using this on production, and run it on a reporting copy where you can. Group the output by location and by product group. If negatives cluster in one branch, the cause is that branch's process. If they cluster in one product group, suspect UOM or kit and assembly setup. **Code:** -- Indicative only: confirm table and column names in your release SELECT l.location_id, m.item_id, m.item_desc, l.qty_on_hand FROM inv_loc l JOIN inv_mast m ON m.inv_mast_uid = l.inv_mast_uid WHERE l.qty_on_hand < 0 ORDER BY l.qty_on_hand; ### Cost and accounting consequences **Body:** A negative balance usually means cost of goods sold was relieved at whatever cost the system had at that moment, which may be a stale average or zero. When the late receipt arrives, the cost basis can differ, and the variance lands somewhere you may not expect. Before correcting a large number of items, talk to accounting about the costing method in use and the period state. Adjusting quantities in a closed period or after inventory valuation reports have gone to auditors needs a controlled approach, not a quick fix. Keep a record of the before and after for each correction: the item, location, quantity, reason code and approver. This matters if your auditors ask why a block of adjustments occurred. ### Preventing it **Body:** Decide whether negative on hand should be allowed at all, by item or location, and set it deliberately. Many distributors allow it only in specific cases such as fast-moving counter items where receiving lags. Others block it to force receipt discipline. Track a simple daily measure: the count and value of negative item-locations. If it climbs, something in receiving or transfers has changed. Reviewing that list each morning costs minutes and catches problems while the paperwork is still on the desk. ## Pitfalls - Zeroing negatives with blind adjustments, which hides the real cause and can distort cost. - Looking only at the item total. A positive total can still contain a negative location and a matching overstated one. - Fixing the symptom each month with a cleanup while receiving habits stay the same. - Adjusting in a closed period without accounting's agreement. - Ignoring UOM conversions when quantities are off by a round multiple. ## Ai Angle An assistant grounded in your P21 data can list every negative item-location, group them by branch and product group, and for any single item show the transaction sequence that first crossed zero, with the query visible for verification. That turns an afternoon of reading history into a quick review. It stays read-only: corrections such as adjustments, transfers and receipt fixes remain human actions with your normal approvals. ## Faq ### Does negative on hand mean we lost stock? **Answer:** Not usually. It more often means transactions were recorded out of sequence or in the wrong place. The stock may be physically present or may have genuinely gone, so verify with a physical count of that location before posting a quantity adjustment. ### Should I turn off negative inventory entirely? **Answer:** It depends on your operation. Blocking it forces timely receiving but can stop shipments when the dock is ahead of the system. Many companies allow it for selected items or branches and monitor a daily negative list. Decide per item class rather than globally. ### Why is the item total fine but one location is negative? **Answer:** Typically stock was received into one location and sold from another. The two errors offset in the total. Fix it with a transfer or location correction, not an adjustment, so quantities and cost move together and the history stays honest. ### How does this affect cost of goods sold? **Answer:** Sales made while negative may relieve cost at a stale or zero cost, so margin looks wrong until receipts catch up. Review affected items with accounting, especially near period end, and understand your costing method before correcting. ## Search terms prophet 21 negative inventory, p21 negative on hand, p21 inventory adjustment, prophet 21 inventory not matching physical, p21 negative quantity location, p21 unit of measure inventory, distribution negative inventory fix, p21 cost of goods sold negative stock --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json