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.
