Prophet 21 shows negative on-hand inventory: why it happens and how to fix it
Prophet 21 negative inventory on hand how to fix
Also searched as
- 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
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.
Applies to: Prophet 21 distribution installations. Window and table names below are indicative; confirm in your release before running queries against production.
Trace and correct a negative quantity
- 1Start 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.
- 2Find the first transaction that took the balance below zero. That one, not the latest, is your clue.
- 3Classify 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.
- 4If 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.
- 5If 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.
- 6Review 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.
- 7Fix the process that caused it: receiving timeliness, location discipline, UOM setup, or a setting that allows selling below zero for items that should not.
Why distribution systems go negative
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
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.
-- 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
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
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.
Common 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.
How an ERP-grounded AI assistant handles this
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.
Frequently asked questions
Does negative on hand mean we lost stock?
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?
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?
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?
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.
Related
How to close job costing and the accounting period in Epicor
Before closing an accounting period in Epicor, run Capture Costs (or confirm background costing is current) to post all labor, material, subcontract, and burden to jobs, review the WIP report for jobs that should be closed or investigated, close completed jobs so their costs relieve WIP, and only then close the GL period, since GL close blocks further transaction posting into it.
How-toProphet 21 is slow or freezing: how to find and fix SQL Server blocking
When Prophet 21 hangs for many users at once, the cause is usually one SQL Server session blocking others, or a long-running report holding locks on hot tables such as order and inventory location rows. Find the head blocker with sys.dm_exec_requests, identify the statement and the P21 client or report behind it, and fix that, rather than restarting the application server.
CIO briefingEpicor Eclipse or Prophet 21: how a distributor should make the decision
Epicor sells both Eclipse and Prophet 21 to distributors, and they come from different histories and serve somewhat different verticals. Do not decide on feature lists alone: get Epicor's written support and roadmap position for your product, price a full migration honestly, and test your own critical workflows before committing. Staying put is often cheaper than moving, until it is not.
Error fixFixing Epicor BusinessObjectException: BPM Directive Errors
A BusinessObjectException that says "A Business Process Management (BPM) directive has raised the following error" means a directive on that business object stopped the transaction, either deliberately via a Raise Exception widget or accidentally via an unhandled .NET error in Custom Code. Expand the InnerException on the error dialog to see the real message and the directive name, then open BPM Designer for that object and method to find the widget that fired.
How-toFixing a Slow Epicor BAQ (Business Activity Query)
A slow BAQ in Epicor is usually caused by unindexed join columns, a subquery or calculated field forcing a table scan, or the BAQ pulling far more rows than the dashboard actually displays before filtering client-side. Fix it by reading the SQL Server execution plan Epicor generates, moving filters into the BAQ criteria instead of the dashboard filter panel, and replacing subqueries with joins where possible.
How-toEpicor MRP Runs but Generates No Suggestions
When Epicor's MRP process completes without a job or purchase suggestion for a part you expect one for, the cause is almost always the part's own configuration (Part Class Type, Make Direct, Non-MRP flag, planning Time Fence) or the demand not being linked in a way MRP recognizes, not a defect in the MRP engine. Work through the part's Planning tab, its safety stock and lead time setup, and the demand source (sales order line status, job material requirement) before assuming the run itself failed.
AI for ERPAI for Epicor Prophet 21, Built for Distribution Operations
Prophet 21 AI for distributors: on-prem or private-cloud LLM grounded in P21 order, inventory, and purchasing data, for order follow-up, forecasting, and pricing questions.
Stuck on Epicor Prophet 21?
Talk to engineers who work inside Epicor Prophet 21 every week, and who build private AI that answers these questions from your own ERP data.