Message-ID: <471475E8.4030505@archonet.com>
Date: Tue, 16 Oct 2007 09:27:20 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.6 (X11/20070728)
MIME-Version: 1.0
To: Mike Adams <madams55075@comcast.net>
CC: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Refactored queries needing validation of syntactic equivalence
References: <470D5F81.5090403@comcast.net> <470DCFC7.3090901@archonet.com>
	<47110414.6060907@comcast.net>
In-Reply-To: <47110414.6060907@comcast.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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