Recreating a Payables Manual Payment in SQL - What tables?

(0) Share

Report

Posted on by K Day

7,365

I wanted to run this by some of the smart people here. What I am trying to do is recreate a payment in the PM historical tables. The situation now is that the user had some sort of hiccup (?) when posting a batch of Manual Payments and then kept trying to post it again, got batch recovery errors, and just tried to keep posting it until the batch got stuck in "posting" status. I got it out of that status following the KB steps, but it appears that this batch was already posted in part, somehow.

Basically, that batch only had 2 manual payments. The end result was that one of the payments made it through with no problem. The other one managed to somehow get posted into the open and the history table. So transactions by vendor was giving strange errors trying to build that temp table to populate the form because of duplicate keys.

Where we stand now is that the GL is fine, the payment was recorded. The invoice shows that it is paid on the vendor account. Drilling into the apply to part to see what payment was applied returns a blank screen, but no error messages. The payment is not listed in the inquiry by vendor. It is just gone. The payment is listed in the checkbook to clear for bank rec.

Drilling into that payment record from the GL journal entry inquiry returns, "this document no longer exists or....."

Checklinks and reconcile are not doing anything for me.

I basically want to rebuild that payment in SQL so it shows up in inquiry. What I wanted to do was copy (figuratively) the one successful record that posted out of the two of them had for information with the exception of some of the key fields. I know the document number it should be, the dates, what to apply it to, but wanted to confirm the tables that I need to use.

So far I have come up with:

PM00400 - PM Keys

PM30200 - Historical/Paid Transactions

PM30300 Apply To History

Possibly PM 30600 - GL Distributions - but I think this might be for cosmetic purposes

I am hpping after I get this stuff in, I can run Checklinks / Reconcile and it might help me and recreate some stuff I might have missed.

Does this sound like a feasible solution, or an I crazy for trying this?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Community Member on at

Like (0)

Report
Copy link

Link copied!

You are missing the PM30700 - PM Tax Hist file.

What you are doing is certainly not crazy. Needs must! However, is there no way you can delete the transaction and then re-enter it?

Its certainly easier to delete the transaction in SQL that re-create it in SQL...and at least if it goes into GP properly the second time, you are sure that all tables have been updated properly.

You could try searching all relevant tables for the document number, and deleting the resulting data.

Can you void the document? You say it is in the open table? if you delete it from the open can you void it from history and vice versa?

Try in a test company first, and try to deal with it through GP before resorting to SQL, but you should be able to handle in SQL.

Certainly do run a reconcile and checklinks afterwards even if it looks perfect...you don't want it cropping up again the next time someone runs a checklinks for another issue.

Was this reply helpful? Yes No
K Day 7,365 on at

Like (0)

Report
Copy link

Link copied!

I actually deleted in in the Open table through SQL because it was in both tables and it was supposed to be historical and applied. I am fortunate to have a test enviornment that I can use. The thing about the void is that it shows that this payment doesn't exist, even on the vendor inquiry screen. It doesn't even show up listed in transactions by vendor, so I doubt that I will be able to browse to it even void it. It really only seems like everything is good, but the payment simply is gone out of the PM30200 table. I have not had the time to do serious investigation in SQL, but I would imagine that the fact this invoice shows that it is paid (0.00 remaining balance) means there is soem pointer to an apply record that does not exist.

I also though about moving that transaction back to the open table, then doing the Manual Payment again and not letting it post to the GL, but that would create another entry in the chackbook. I suppose I could canck it out of the checkbook and not let that post through, but I'm getting confused just thinking about it.

This is why I really just want to recreate that payment so it just shows up when drill into the payment instead of it being a blank window.

Thanks for your suggestion.

Kevin

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report
Copy link

Link copied!

Since you went through all this trouble, won't it have been easier to remove the records from the tables you identified, run a check links to clear up any other misfits, then re-enter the transaction in GP using the interface?

Was this reply helpful? Yes No
K Day 7,365 on at

Like (0)

Report
Copy link

Link copied!

I guess that's why I am posting here. I haven't actually done any of this yet, just wanted to run it by others who might know more about this.

I have found that checklinks isn't really picking up on the fact that there is a missing record. Bottom line now is that there is a missing record and checklinks is not finding it. So I figured that if I can get enough information back into SQL, checklinks might finally pick up on it and try to fix it.

I'm going to be working on this tonight so I figured that maybe someone here might have had to do something like this before.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Yes, had lots of stuff like this before, and it generally is easir to delete the transaction through SQL, and re-enter it. If your invoice is showing fully paid, then this might be a problem. Can you void the invoice?

Was this reply helpful? Yes No
K Day 7,365 on at

Like (0)

Report
Copy link

Link copied!

I haven't tried that (I actually won't have access to their system until later tonight), but I think that might be worth a shot, although I would expect an error similar to if I tried to void an invoice that had a check written against it, where it tells me to first void the payment before voiding the invoice.

Kevin

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

Just curious if you had any success with this? I have an exact matching problem - down to the fact the client tried to post and post and post.

We found that the data is in the History and Open tables.

Interesting difference here is that our record is missing in the PM00400 and it is in the PM30200 and PM30300 - so would a simple rebuild of the PM Keys (PM00400) solve my problem?

We did remove the 1 invoice from the Open and all the other payments reappeared with the exception of the one Payment that covers all the invoices that were just being paid.

We did run checklinks after the fact and that did nothing to assist.

I know this goes back some time, but just hoping that someone might be able to reply and let me know if there was some satisfaction.

Was this reply helpful? Yes No
wperkinson3 2 on at

Like (0)

Report
Copy link

Link copied!

Did anyone get a resolution to this issue?

I am having the exact problem now with the payment record missing on the PM30200 table.

Will

Was this reply helpful? Yes No