I have 2 invoices back from 2008 for whatever reason (I'm sure there was some sort of data issue back then) they are in the History PM30200 Tables with nothing applied to them. Yes, it has been sitting on the Aging reports for 3+ years.....I look and see that there was probably some sort of payment made on them at one point back in 2008, but those payment records are simply gone. Since you can't void historical "Invoices" I think I have no way of doing this but manually removing the record from PM30200 and inserting into the PM20000. But it's not that easy I guess. I need to also move the distributions from History to Open and also the PM00400 Keys records from 3 to 2 (History to Open). We need to ultimately void these invoices, so I need the Void to work without a hitch.
Has anyone done this? We have PSTL and I don't see anything there. Obviously Check Links does not pick up on it or I wouldn't be posting this. Anything that I am missing?
What happens when you try to void the historical transaction?
Was a solution ever provided because we have the ** exact same issue ** occurring (even as far as very old transactions, missing apply record). Our payment run appeared to have been interrupted while the last payment of the batch was being applied to two invoices. The payment transaction was sitting in the PM00400 table without the following data elements populated: VENDORID, TRXSORCE, CHEKBKID, BCHSOURC, USERID. The only items populated were the CNTRLNUM, CNTRLTYP, CSTATUS (was 0 mind you) DOCTYPE (also 0), DOCNUMBR, all three date fields and the DEX_ROW_ID field. When I tried to update the missing data and then run CheckLinks, the entire row was removed from the PM00400 table by Checklinks (report generated the comment ... The transaction for this keys record is missing. The keys have been removed...); however, the two invoices the payment was paying remain in the PM30200 table as though they have been paid. I ran Checklinks and Purchasing Reconcile over-and-over again, with no change to the two invoices (they both have a status of HIST and Unapplied Amount of zero (0.00) in the inquiry form). I think I need to insert the data showing in the PM30200 table IN TO the PM20000 table and then delete the data from the PM30200 table, run Checklinks and see if that resolves the issue...but I need a sanity check first please. Any help would be appreciated.
Have you deleted and recreated the PM Keys records?
You can use the Maintenance | Clear Data window to clear data on the Payables Key Master Logical files and then run Check Links on the Payables history Logical Files and payables Transaction Logical files.
I just tried your suggestion and the two invoices still show with a status of HIST as though they're paid, and yet, when clicking the Apply button, the corresponding payment data is not visible and the payment itself is not in the PM00400 table. Is there anything else I should try?
Sounds like you have missing data in the apply table. Can you try to void these documents out of history and then enter then again and apply them to each other. Make sure you turn off posting to the GL
Unfortunately, the payment record which should be associated with the two invoices in question is not visible in the Void Historical Transactions window (nor is it visible when drilling down to the invoices' apply information). The payment record was the one in the PM00400 table missing all the key data referenced in my earlier note. I need to somehow "trick" the system in to thinking these two invoices are OPEN (not WORK, because they're already posted through AP & GL) so I can apply the unapplied amount on an existing check, meant specifically for these two invoices.
Well you can temporarily turn off payables transaction posting through the GL. The enter two new payables documents and put notes on them as to why you are doing this and you will need to modify the document number slighlty to avoid duplicates. Then post these two tranasctions and apply the payment and then all three will move to history. When completed turn posting through GL back on and delete the ledger transaction that had come down as a result of these two entries. This will negate any affect on the ledger and then clean up your payables. Is your GL AP balance too low by this outstanding payment? I would suspect it is. So by entering these two payable you AP balance will go up to match what is in the ledger.
This was a good idea too, so I gave it a shot. Good news, it allows me to apply the payment and our Aged Trial Balance w/ Options report gives us the desired results; however, bad news is... the Historical Aged Trial Balance (HATB) report does not...and it's been the latter we've been told to use to tie out to our GL. Should we be using the ATBwO report instead?
I also considered the Transaction History Removal option and when I try this in my test company (running report ony) the report comes back with "Unable to remove this transaction: an open apply record exists or an apply record exists outside the selected range" so it seems, unless I delete the records directly from the history table, we're going to be stuck with these orphaned vouchers forever.
Aaah, and now the fun begins. We need to clean up the PM HATB. Can you send me a copy of the PM HATB with the errors identified. I will send you back some scripts to clean them up. You do have access to SSMS, correct?
Yes, I can get you the report. Do you want all suppliers/vendors, or just the one in question and through 31-Dec-2012? Also, how/where do I send you the output please? I do have access to SSMS as admin.
I am just looking for the troublesome ones. Vendor ID and document numbers.
If you click on my name you will see my contact information and my email address. Just attach the file and send it on over.
What did you do to resolve this issue? I have a client with the exact same issue. They have one invoice that was showing as open but when attempting to apply a payment or credit memo to it, it was showing it was fully applied. I reviewed the tables and it looks like something happened during the posting and the payment was not posted. I removed all the payment details from the PM00400, PM10200, PM30200 tables. When I ran checklinks it moved the voucher to History (PM30200) but now the Historical PM HATB is off by the amount. How did you fix the issue above?
Each one of these is different. My guess right now is that there are records in the PM30300 table that have to be removed. I would find the corresponding records to these transactions and remove them. I usually start by searching all the tables in the company database for the string containing the document or voucher number in question. Once you know that you pick and choose what needs to be removed.