Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Recreating a Payables ...
Microsoft Dynamics GP (Archived)

Recreating a Payables Manual Payment in SQL - What tables?

(0) ShareShare
ReportReport
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

  • wperkinson3 Profile Picture
    wperkinson3 on at
    RE: Recreating a Payables Manual Payment in SQL - What tables?

    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

  • Bill Campbell Profile Picture
    Bill Campbell on at
    RE: Recreating a Payables Manual Payment in SQL - What tables?

    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.

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Re: Re: Re: Re: Recreating a Payables Manual Payment in SQL - What tables?

    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

  • Community Member Profile Picture
    Community Member on at
    Re: Re: Re: Re: Re: Recreating a Payables Manual Payment in SQL - What tables?

    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?

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Re: Re: Recreating a Payables Manual Payment in SQL - What tables?

    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.

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    Re: Re: Re: Recreating a Payables Manual Payment in SQL - What tables?

    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?

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Recreating a Payables Manual Payment in SQL - What tables?

    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 

  • Community Member Profile Picture
    Community Member on at
    Re: Recreating a Payables Manual Payment in SQL - What tables?

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,407 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans