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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
