Mike -
We had an especially tough case a few years ago. There were several 3rd party products and not a few customizations as well. The client processes thousands of transactions daily. I will not name names, or point fingers, because as Trevor mentioned above, your mileage will vary.
What we did was come up with queries, which became the foundation for SSRS reports which identified the various types of errors specifically:
1. Cancelled Manufacturing Orders with Remaining Allocations
2. Allocations in Inventory tables don't Match Allocations in Manufacturing Tables
3. HITB does not match Inventory History
We then came up with resolutions to these errors:
1. Close the MO - the MO Close process would remove the allocations.
2. Run Inventory Reconcile for the affected items only
3. Run Inventory Reconcile for the affected items only
This approach made the problems manageable, because you were only dealing with the problems, and not running reconcile overnight on everything. Even with the thousands of transactions being processed, there were typically only a few problems a day.
It's a bit dated, and I am unsure what GP version you are on, but run the query below (it's a select statement and won't alter anything) to see if it can help identify just the problem transactions you are having.
----------------------------------------------------------
Select w.MANUFACTUREORDER_I,
m.ITEMNMBR,
w.STRTDATE,
m.TRXQTY-m.QTY_ISSUED_I,
w.MANUFACTUREORDERST_I
from MOP1210 m, MOP1200 m0, WO010032 w
where w.MANUFACTUREORDER_I = m.MANUFACTUREORDER_I
and w.MANUFACTUREORDERST_I = 6
and m.PICKNUMBER = m0.PICKNUMBER
and m.TRX_TYPE in (3)
and m0.posted = 0
and (m.TRXQTY-m.QTY_ISSUED_I) > 0
--#3 With MO #'s only
Select Distinct w.MANUFACTUREORDER_I
from MOP1210 m, MOP1200 m0, WO010032 w
where w.MANUFACTUREORDER_I = m.MANUFACTUREORDER_I
and w.MANUFACTUREORDERST_I = 5
and m.PICKNUMBER = m0.PICKNUMBER
and m.TRX_TYPE in (3)
and m0.posted = 0
and (m.TRXQTY-m.QTY_ISSUED_I) > 0
--#1 Mike confirms this is an older query and is less accurate than 2 & 3 (20120326) - this query picks up adjustments as erros, but allocations match
Select TRXQTY,ISSUEDQTY, TRXQTY-ISSUEDQTY as ALLOCATEDDETAIL, (I.ATYALLOC-(Select SUM(ATYALLOC) from SOP10200 where ITEMNMBR = I.ITEMNMBR)) as ITEMALLOCATION, rtrim(I.ITEMNMBR) as ITEMNMBR from
(
select rtrim(MOPL.ITEMNMBR) as ITEMNMBR,SUM(MOPL.TRXQTY) as TRXQTY, sum(MOPL.QTY_ISSUED_I) as ISSUEDQTY from MOP1210 MOPL (Nolock)
inner join WO010032 WO (Nolock)
on wo.MANUFACTUREORDER_I = MOPL.MANUFACTUREORDER_I
inner join MOP1200 MOPM (Nolock)
on MOPM.PICKNUMBER = MOPL.PICKNUMBER
and wo.MANUFACTUREORDERST_I <> 8
and mopm.POSTED = 0
--and mop.ATYALLOC !=0
--and mop.QTY_ISSUED_I !=0
and MOPL.TRX_TYPE = 3
--Order By wo.MANUFACTUREORDER_I
group by MOPL.ITEMNMBR
) a, IV00102 I where I.ITEMNMBR = a.ITEMNMBR and I.LOCNCODE = 'WAREHOUSE' and (TRXQTY-ISSUEDQTY) != (I.ATYALLOC-(Select SUM(ATYALLOC) from SOP10200 where ITEMNMBR = I.ITEMNMBR))
order by ITEMNMBR
------------------------------------------
