We currently have two different payment methods set up, one for our headquarters and one for a satellite plant. We are now centralizing the cash disbursments and I need to change all open AP documents payment method from 'abc' to 'xyz'. It appears that the tfacp200 tables hold more than solely the open documents it seems to add a record for the anticipated payment, one for the payment, and one for the payment reconciliation. How can I structure a query to grab only the open documents?

If the balances in open items are correct , u can check following conditions for open items

tfacp200.balh <> 0
tfacp200.tdoc = ""
tfacp200.docn = 0

If u need to get only purchase invoice/credit notes then u may add following conditions
tfacp200.tpay = purchase invoice / credit note

I tried using that in a query and I pulled records that were not open. I'll keep trying.

Each of the Invoice records for each step, (ei 'open/original inv' , matched, approved, payment doc, reconciled) has a sequence number. The 'open/original' invoice record is the 0th sequence. You could try a correlated subquery where the 0th sequence record transaction type and document number is NOT IN a subquery looking at records with a sequence number > 0.
???
hope it helps,
bb

What about a less technical end-around?

Select for payment all open invoices with Payment Method abc and due date before 31-Dec-2099 (or how ever far out your supplier relations folks have managed to stretch terms). You might have to temporarily change payment limits by supplier, bank, pmt method, etc. to get everything that's "open".

Dump the detail file and then delete the payment batch(es) before the controller tries a cash forecast and has a coronary.

You should be able to pull the open invoice IDs from the detail. Still apt to be some cleanup -- especially if you have partially paid invoices, installment invoices, etc -- but it might give you a quick hit list that you could use in a script to update payment method to xyz (or to dump, manipulate and reload the selected 0-seq records).

I sure would try it carefully in a test environment because I think there are a number of other referential links that may need to be updated.

Scott