Inventory Inquiry Allocated Total does not match Item Allocation Inquiry Total on Drill Down

A couple of years ago, a client who uses Dynamics GP 2010 Manufacturing discovered a problem. The amount Allocated in the Inventory Inquiry window disagreed with the Drill Down detail in the Item Allocation Inquiry window for some items. What was obvious was Numerous Manufacturing Orders were throwing off the totals in the Item Allocation Inquiry window. What we discovered was Item Allocation was accurate in the Inventory Inquiry window. True Item Allocation at the Inventory Control level appears to be correct, but the drilldown in the Item Allocation Inquiry window details was incorrect.

Most of the client’s operation was Make-to-Order manufacturing. As Manufacturing Orders were released, Dynamics GP allocated component inventory, as it was configured to do. With a high order volume/low order value and liberal cancellation policy, numerous orders were cancelled at various stages, using different methods, including an aborted attempt to cancel orders using custom code, which was meant to provide a single method to cancel Manufacturing Orders regardless of the production stage.

After much research, thought, discussion and data validation, we created a query to identify the records appearing in the Item Allocation Inquiry window, which are not included in the Allocation Totals in Item Inquiry window. Furthermore, we believe we have identified why the problem occurred.

The detail of the “issue” causing this is as follows:

When an MO is cancelled, typically, a Reverse Allocation entry is written to the MOP1210 (MOP Pick Line) table to properly offset the original allocation transaction written to the MOP1210 table at Manufacturing Order Release. This record adjusts the total allocation to zero. 

The problem appears to be, when some Manufacturing Orders were cancelled, the reverse allocation entry to MOP1210 table was not entered.  This presents a problem, in that the Great Plains method of pulling information into the Item Allocation Inquiry window verifies a cancelled MO also has the Reverse Allocation entry in these tables, and when it does not – the MO continues to appear in the inquiry window.

The only way to keep the cancelled Manufacturing Orders for items from appearing in the Item Allocation Inquiry window, which meet this criteria is to Close the problem Manufacturing Orders. Alternatively, knowing what we know, we could update the SQL tables holding the Picklist data that is incorrect – we felt this approach is not advisable, because it is performed outside the system; therefore, bad practice and hard to justify to auditors.

This query identifies the MO#, Item number and Quantity. When we cross referenced this data to the Quantity Master Allocation and the data in Item Allocation Inquiry, it proved to be the missing link.  This approach not only tells us, which inventory items have allocation issues, it also identifies the specific Manufacturing Orders causing the allocation issues, removing the necessity of either having someone review the allocation details for problem items and closing all cancelled work orders related to problem items.

Select  WO.MANUFACTUREORDER_I MO#,

            MPL.ITEMNMBR Item#,

            WO.STRTDATE 'Start_Date',

            MPL.TRXQTY-MPL.QTY_ISSUED_I Allocation_Detail,

            case WO.MANUFACTUREORDERST_I

                  when 1 then 'Quote/Estimate'

                  when 2 then 'Open'

                  when 3 then 'Released'

                  when 4 then 'Hold'

                  when 5 then 'Cancelled'

                  when 6 then 'Complete'

                  when 7 then 'Partially Received'

                  when 8 then 'Closed'

            END MO_Status 

from MOP1210 MPL, MOP1200 MPH, WO010032 WO

where WO.MANUFACTUREORDER_I = MPL.MANUFACTUREORDER_I

      and WO.MANUFACTUREORDERST_I = 6

      and MPL.PICKNUMBER = MPH.PICKNUMBER

      and MPL.TRX_TYPE in (3)

      and MPH.posted = 0

      and (MPL.TRXQTY-MPL.QTY_ISSUED_I) > 0

     

--alternate form of the query, may produce false positives

Select Trx_Qty,Issue_Qty, Trx_Qty-Issue_Qty as Allocation_Detail, (IA.ATYALLOC-(Select SUM(ATYALLOC) from SOP10200 where ITEMNMBR = IA.ITEMNMBR)) as Item_Allocation, rtrim(IA.ITEMNMBR) as Item#  from

(

select rtrim(MPL.ITEMNMBR) as Item# ,SUM(MPL.TRXQTY) as Trx_Qty, sum(MPL.QTY_ISSUED_I) as Issue_Qty  from MOP1210 MPL (Nolock)

      inner join WO010032 WO (Nolock)

      on WO.MANUFACTUREORDER_I = MPL.MANUFACTUREORDER_I

      inner join MOP1200 MPH (Nolock)

      on MPH.PICKNUMBER = MPL.PICKNUMBER

      and WO.MANUFACTUREORDERST_I <> 8

      and MPH.POSTED = 0

      and MPL.TRX_TYPE = 3

group by MPL.ITEMNMBR

) ALLOCATIONS,

IV00102 IA where IA.ITEMNMBR = ALLOCATIONS.Item#

and IA.LOCNCODE = 'WAREHOUSE'

and (Trx_Qty-Issue_Qty) != (IA.ATYALLOC-(Select SUM(ATYALLOC) from SOP10200 where ITEMNMBR = IA.ITEMNMBR))

order by ITEMNMBR

Because of the specificity of this query, which identifies the actual problem Manufacturing Orders, the number of records (specific MOs) identified by the new query is significantly lower than the number originally identified using our previous approach, which was to identify all open Manufacturing Orders for items, which met the out of balance criteria (6081).  There were approximately 1600 Manufacturing Orders, which needed to be closed manually (1670); less than 30% of the open order total.

Considering the drastic reduction in the number of work orders, which need to be cancelled, and the hours we invested in the analysis, and the risk associated with automation, we concentrated on the manual process to close the MOs, as opposed to continuing work on automation.

For reference I am including the logic that GP uses to fill the Item Allocation Detail window.

There is more to it, but this is where the problem is:

The MO has a status <> 8 (Closed) in the WO010032 table 

The MOP1210 has a TRX_TYPE of 1 or 3 (Allocation) and 

The quantity allocated in MOP1210 table TRXQTY-QTY_ISSUED_I > 0

When an MO is cancelled, the appropriate logic should be:

Marks the MO as cancelled in WO010032 (Updates Status = 5)

Creates a Reverse allocation entry in MOP1210

This is missing on all problem MOs – TRX_TYPE of 4

Change QTY_ISSUED of the original allocation entry in MOP1210 to the TRXQTY 

This is also missing on the problem MOs

In the end, the initial finding proved out: True Item Allocation at the Inventory Control level appears to be correct, and just the drill down in the Item Allocation Inquiry window details were incorrect. The issue was caused by Great Plains failing to create an offsetting transaction in the MOP1210 table, complicated by a design flaw in the Inventory Allocation Detail window which basically expects work orders to be closed after cancellation.