Periodically, we have encountered issues with erroneous pending quantities in Sage 500 ERP. This is typically found on older versions of the product and Sage has done a nice job with tightening up the shipping process with recent releases. In fact, Sage Support created a data-fix (spDF_CleanupDistForCancelledShipment) to resolve these issues on version 7.0 and 7.2. Please contact Sage Support if you would like the stored procedure.
These pending quantities cause lots of issues because the system reserves the pending quantity and does not display the true quantity available, which affects picking and shipping. The pending quantities are stored in the timwhsebininvt table in the database in the pendqtydecrease and pendqtyincrease fields:
Using this data as an example, if we determine that itemkey 94 has no pending shipments or other entries to justify this pending quantity, we can go into timwhsebininvt and just set the pending columns to 0. This is not recommended because the pending quantity will just come back unless you fix the underlying issue. Every Sage 500 ERP installation loads a QtyOn job in SQL that serves to keep these pending quantities updating based on the transactions in the system:
A common offender of erroneous pending quantity is shipments that were cancelled and the associated inventory transactions were not properly removed. This can result in an entry in timinvttranlog that references a shipment transaction that no longer exists. You can check this with this query:
select
*
from timInvtTranLog
where TranType = 810 and TranStatus = 2 and
InvtTranKey not
in
(select InvtTranKey from tsoShipLine)
If you received any results, this condition can be resolved by changing the transtatus from 2 (pending) to 5 (void) in the timinvttranlog table. This will remove it from the QtyOn job process so the pending quantity will not return for the orphaned records. Then you can safely change the pendingqtydecrease to 0 in the timwhsebininvt table. NOTE: It is recommended that these changes be performed in a test environment so the results can be verified prior to implementing in production.