Item Allocation Quantity Does Not Match Sum of Allocated Orders in Microsoft Dynamics GP

Issue

This is an issue in Dynamics GP that I have seen a few times, especially with clients that have a number of third-party products or customizations. The ITEM INQUIRY window is showing a certain quantity allocated, but when you drill down on the allocation, the allocated orders do not add up to the total allocated quantity.

You might try to run the ITEM RECONCILE, but it doesn’t make any changes to the allocation. You might try to do a manual update to the item allocation quantity in the backend, but as soon as you run the ITEM RECONCILE again, it reverses the changes you just made.

So at this point, what do you do?

Diagnostic Tools

Item_Allocation_Detail.sql - As it turns out, there are a number of possible inventory allocations that do not show up in the ITEM ALLOCATION INQUIRY window. However, the following script will show all the allocation areas by replicating the Dynamics GP calculation for inventory allocation.
Download Script

Mfg_Item_Allocation_Detail.sql - If the allocation discrepancy is in the manufacturing module, this script joins the allocations to the MO master to help trace the transaction.
Download Script

Resolution

The diagnostic queries should show you where your allocation quantities are coming from, and you should be able to make an adjustment to the allocation by removing unnecessary records, or repairing records that are legitimate but not showing in the GP inquiry.

Below I have documented a couple of allocation repairs I've made after running the diagnostic queries.

Case 1: PO Return
Symptom: Allocated quantity does not match allocation inquiry.

In this first case, the allocation detail script found that there was a PO return that was being included in the allocation calculation but not showing up in the allocation inquiry. A detailed examination of the PO return showed that the related Purchase Order had been completed and moved to history months ago. The PO return header and lines had been successfully moved to history (POP30300, POP30310), however the lines were still floating in the open tables as well (POP10300, POP10310). I suspect that these lines were not removed in subsequent runs of the REMOVE COMPLETED POS utility because the related PO had been removed, so it had become something of an orphaned receipt.

Since the PO record was in the history tables, I deleted the related lines in the open tables through the back end. After deleting the lines from the work tables, I ran the ITEM RECONCILE on just the individual inventory item. The RECONCILE showed the allocation had been changed to the allocation amount we were expecting based on the outstanding orders in the ITEM ALLOCATION INQUIRY window.

Case 2: MO Bin Allocation
Symptom: Allocated quantity matches allocation inquiry but bin inquiry shows that there are quantities allocated.

In this case, the allocation detail script showed that there was an allocation in the MOP1900 (Trx_Bin_WORK).

Further investigation showed that there were dozens of lines in that same table causing the same issue for multiple items. Running the below queries, I suspect the allocations were previously processed but these records were orphaned in the MOP1900.

--FROM ALLOCATION DETAIL QUERY

select MANUFACTUREORDER_I, ITEMNMBR, ATYALLOC, LOCNCODE, BIN

from MOP1900 where ATYALLOC > 0 and ITEMNMBR = 'ITEM# 1

'

--MO MASTER

select * from WO010032 where MANUFACTUREORDER_I = 'MO0000005943'

--TRX BIN WORK

select * from MOP1900 --where ITEMNMBR = 'ITEM# 1'

order by DEX_ROW_ID

-- where MANUFACTUREORDER_I = 'MO0000005943'  --PICKNUMBER = 'MOPICK000055440'

--MO RECEIPT MASTER AND DETAIL

select * from MOP1100 where MANUFACTUREORDER_I = 'MO0000005943' --MORCT0000007242

select * from MOP1110 where MOPRCTNM = 'MORCT0000007242'

--PICKDOC HEADER AND LINES

select * from MOP1200 where PICKNUMBER = 'MOPICK000055440'

select * from MOP1210 where PICKNUMBER = 'MOPICK000055440' and ITEMNMBR = 'ITEM# 1'

--PICKLIST SITE AND BIN QTYS

select * from MOP1400 where MANUFACTUREORDER_I = 'MO0000005943' and ITEMNMBR = 'ITEM# 1'

select * from MOP1410 where MANUFACTUREORDER_I = 'MO0000005943' and ITEMNMBR = 'ITEM# 1'

Again, I removed the orphaned records from the work tables and ran ITEM RECONCILE on just the individual inventory item. The RECONCILE modified the allocated quantities in the BIN QUANTITY INQUIRY to match the allocation shown in the ITEM INQUIRY.

Summary

As you can see, the causes of the allocation difference can vary but are typically caused by orphaned records that still contribute to the allocation quantity but are no longer being picked up by Dynamics GP. While it's certainly possible to make a front end inventory allocation adjustment to fix the quantity, I am more inclined to maintain the integrity of the records in the backend.

These diagnostic tools should help you identify where the ghost allocations are coming from, at which point an assessment can be done on a case-by-case basis to determine a fix that makes sense.

Feel free to reach out should you have any questions or need additional information.

Written by:
Alvin Liu, Dynamics GP Consultant, FMT Consultants

Posted by: Jakob Bechgaard