Mike Adams wrote:
> So.....
> The first query should pull all 'MOM' records that have one or more
> corresponding, and possibly orphaned, unassigned receiving records
> belonging to the same po_cd and item_cd.
>
> The second query should pull all unassigned, and possibly orphaned
> receiving records that have one or more corresponding 'MOM' records once
> again matching on po_cd and item_cd.
>
> Using the results of both queries to double check each other, I can
> figure out which (if any) open records are, in fact, orphans and do an
> "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our
> accrual.
>
> Of course, our ERMS should take care of this automagically; but,
> tragically, it seems "real" inventory cost flow was attached to the
> system using duct tape, hot glue, and a couple of thumb tacks.
>
> So, given all the administriva above, have I actually refactored them
> correctly?
Well, clearly you could have multiple possible matches, because apart
from anything else you could in theory have multiple entries with the
same item-code on the same purchase-order-code. In practice it will be
rare, but it could happen.
However, since the purpose is to provide you with a list so you can make
manual changes there's no problem with that.
What I might be tempted to do is restrict the dates more - you had <=
'31 Oct 2007' I'd also apply >= '1 Aug 2007' (or whatever period is
reasonable). You can always run an unconstrained match once a month to
catch any that slip through the net, but presumably most will fall
within a 90-day period.
HTH
-- Richard Huxton Archonet Ltd