Accrued Purchases Cleanup / Purchase Orders Paid but Not Matched

Recently, I was having a discussion with a colleague about Goods Received but Not Invoiced AKA Accrued Purchases in Dynamics GP.  Doing a bit of research, I found a really cool blog post. I am not going to restate everything in this well written post, which refers to a discussion between Richard Whaley and Frank Hamelly.

Cleaning up Received but Not Invoiced in Dynamics GP

One of the more interesting things that came up during the discussion was, when cleaning up these transactions you have to be exceedingly careful about identifying the transactions you NEED to clean up.  

Perhaps the most problematic issues is a Receipt that through lack of training, misunderstanding or oversight is not matched to the corresponding invoice. To add insult to injury this unmatched invoice is paid.  

I had what I call a 'Nominal Notion' about how to identify transactions, which fit into this category.  I designed a rough query, ran it against a client's production database, and sent the results in an email to have them investigate the few transactions produced.  

The idea behind the query is to compare paid transactions to Purchase Order at the purchases level (excluding freight, taxes and miscellaneous amounts).  At first, I matched Vendor ID's too, but on further consideration thought this was too restrictive, because the invoice might have been paid with a Credit Card... This was born out in my first use of the query.

If you find yourself in these murky waters, please feel free to experiment with this Nominal Notion, and provide whatever feedback you can.

Here's the query:

select distinct * from PM20000 PMTRX

inner join POP10100 POH

on poh.ORSUBTOT = pmtrx.PRCHAMNT

and BCHSOURC != 'Rcvg Trx Ivc'