PM Tables


Commonly Used Tables:
PM00200 – Vendor Master
PM00201 – Vendor Master Summary
PM00202 - Vendor Period Summary
PM00204 – 1099 Period Detail (only in GP 10.0 and GP 2010)
PM00300 – Vendor Address Master
PM00400 – PM Keys Master
PM10000 - Work/Unposted Transactions
PM10100 – GL Distributions for Work and Open Transactions
PM10200 – Apply To Work/Open
PM10300 – Work/Unposted Payments
PM10400 – Work/Unposted Manual Payments
PM20000 – Open/Posted Transactions
PM30200 – Historical/Paid Transactions
PM30300 – Apply To History
PM30600 – GL Distributions for Historical Transactions
MC020103 – Multicurrency Payables Transactions

Explanation of Document Status:
Work – unposted
Open – posted but not fully applied/paid
History – posted and fully applied/paid

DOCTYPE (Document Type):
1 – Invoice
2 – Finance Charge
3 – Misc Charge
4 – Return
5 – Credit Memo
6 – Payment

DISTTYPE (PM Distribution Type):
1 – Cash
2 – Payable
3 – Discount Available
4 – Discount Taken
5 – Finance Charge
6 – Purchase
7 – Trade Disc.
8 – Misc. Charge
9 – Freight
10 – Taxes
11 – Writeoffs
12 – Other
13 – GST Disc
14 – PPS Amount
16 – Round
17 – Realized Gain
18 – Relaized Loss
19 – Due To
20 – Due From

PM AGE BY:
0 – Due Date
1 – Document Date

TEN99TYPE (1099 Type):
1 – Not a 1099 Vendor
2 – Divident
3 – Interest
4 – Miscellaneous
5 – Withholding

PSTGSTUS (Posting Status) in PM10000:
10 - transaction being entered for the first time before it has been saved by the user
20 - saved and unposted transaction
30 - transaction that is currently posting (realtime)
40 - posted transaction
50 - transaction that has encountered an error during posting
60 - transaction that has been saved previously and had a status of 20 and is now being edited by a user
70 - transaction that has been posted previously in a recurring batch that still contains unposted transactions is now being edited by the user

PSTGSTUS (Posting Status) in PM10100:
0 – Unposted
1 – Posted
3 – Unposted  [used for Computer Checks, not in the SDK]

CNTRLTYP (Control Type):
0 – Voucher types
1 – Payment types
2 – Printed alignment forms

DCSTATUS (Document Status):
0 – Reserved
1 – Work
2 – Open
3 – History

VENDSTTS (Vendor Status):
1 – Active
2 – Inactive
3 – Temporary

CREDTLMT (Credit Limit) in PM00200:
[Thanks to Bud Cool, a frequent contributor to the Microsoft GP Newsgroup, for this information!]
0 – No Credit
1 – Unlimited
2 – Amount  
[Note: If CREDTLMT = 2 then CRLMTDLR contains the amount of the credit limit, otherwise CRLMTDLR is zero]

HISTTYPE (History Type):
0 – Calendar
1 – Fiscal

PYENTTYP (Payment Type):
0 – Check
1 – Cash
2 – Credit Card
3 – EFT

Last Updated: August 15, 2012

328 Responses to “PM Tables”

  1. Hi Victoria,

    Is there any way to manually change a check # within the Manual Payment Entry? I know if I select a Checkbook ID, it will use the next available check number that is set. However, I have some situations where I need to override that check number to something completely different. If I change the document number, would that number be printed on the check as the check #? Or do you know any other way of changing this check number without the usual sequence?

    Thanks in advance!

    Like this

    • Hi Chan,

      If you need to be able to override the check number at the time of entering a manual payment, that can be enabled with a setting on the Checkbook Maintenance window. (There is a checkbox towards the bottom of the window for Override Check Number.)

      If you’re using manual payment entry, no check is printed, so I am not quite sure what the rest of your question is – can you please clarify?

      -Victoria

      Like this

  2. Hi Victoria,
    We have a manual payment in GP 2013 that appears to be hung up. The details of the transaction are as follows (written by the user as it’ll probably make more sense than if I try to explain it).

    “Go into the Manual Pymt screen..enter pymnt number (I use vendor name and invoice #), pick the checkbook I want to use (credit card) then choose which card from the dropdown, fill in doc # and copy it to comment, fill in amount to apply, click on distribution tab and copy the doc # info into those two lines after verifying code numbers are correct, go back to first page and click on the apply tab, choose open invoice to apply the payment to, go back to first page, verify all information is correct ant click on post.”

    When she trys to apply it to a payable document it tells her that it’s already fully applied. However it does not show up when she goes to pay the credit card. The posted status in PM10200 is 0 and DCStatus in PM00400 is 2. Basically we need to know how to either delete the whole thing or force it to apply to the credit card in the initial step. I apologize if this makes little sense as I don’t really use GP nor am I a financial person. Sometimes we just have to support it as best we can. Thanks in advance for any help!

    Like this

    • Hi Dave,

      The first thing I would try it to run check links against the Purchasing series. Once that comes back with no errors (you might have to run it more than once for that) then run Reconcile. This might clear up the issue. If not, you’re going to have to fix the data in the SQL tables directly, which is not something I would want to give advice on without actually looking at the data.

      -Victoria

      Like this

  3. Hi Victoria,
    I am wondering if there is any way to change the document date on a posted invoice. We are currently using GP 10, so we don’t have the doc date verify tool yet. An AP rep has entered a bunch of invoices with an incorrect doc date and I’m not sure if this is something that can safely be modified through SQL. Thank you for your help!

    Like this

    • Carrie,

      I would not recommend modifying these directly in SQL. There any many tables involved and it would be very easy to cause an even bigger issue. If these are not paid yet, you can void them with the same doc and GL dates that they were entered with, then re-enter them with the correct dates.

      Also, Doc Date Verify is available for GP 10.0. :-)

      -Victoria

      Like this

  4. Hi Victoria,
    In preparing for year end, we’ve found a vendor check with less than a penny remaining in the CURTRXAM ($.0028). We only use 2 decimal points so I can’t figure out why it has more or how get the AP document to move from PM20000 to PM30200 without changing our system defaults. Any ideas?

    Like this

    • Hi Amanda,

      I have seen this happen when data is imported with more than 2 decimal places. GP will accept up to 5 decimal places, but will only show you what your currency is set up for. I am not aware of any method to clean this up without fixing it in the SQL tables. Since there could be many tables affected, you will most likely need to work with your GP Partner or Microsoft Dynamics GP Support so they can look at your data and help you fix this.

      -Victoria

      Like this

  5. Hi victoria:
    Do you happen to know how PM10300 table gets populated? I’ve done the following scenarios in GP to check if the table gets populated

    1. Create an Invoice then instantly associate with payment by providing value on Cash field.
    Result: Payment is associated to invoice on table PM10000 not on Pm10300
    2. Create a Payment using Manual payment
    Result: Payment is saved on PM10400

    3. Create a payment then apply on Open Invoice
    Result: Payment is saved on PM10400

    All this scenario does not populate PM10300. Can you specify scenario wherein PM10300 gets populated?

    Thanks! and great work by the way.
    Kristoper

    Like this

  6. Hey victoria, we’re having a strange issue with our EFT payments. We use MICR, and every once in a while, our EFT batch goes straight to posting (no screen to print remittance reports, etc), and it processes with blank check numbers for each EFT transaction. we end up having to turn on duplicate check numbers, process the batch from batch recovery, void all the EFT payments, and then process the batch again. Any idea what may be happening?

    I’ve sat with the person who processes the payments, and nothing appears to be different from a successful EFT batch, until the password is entered and it goes to process, and skips the remittance screen.

    Like this

    • Hi Mike,

      Sorry, I have not run into this before. When you say you’re using MICR, do you mean Mekorma’s MICR product? If so, I would suggest asking them if they have seen this before. Otherwise, you might need to turn on some logging before every EFT batch run to see if you can trap this in a log and examine in more detail.

      -Victoria

      Like this

  7. Victoria,
    Is there a table that holds remit to address that was printed on a check?

    Like this

    • Hi Robert,

      Unfortunately, no. The check will use the remit to address on the invoice(s) it was applied to. You can see this address ID in the VADCDTRO field in PM30200. This can link to the PM00300 table for the actual address. However, if the address was changed after printing the check, there is no record of it kept. :-(

      -Victoria

      Like this

  8. Hi Victoria,

    Im trying to figure out the check numbers for my Purchasing Payment history transactions. I found pretty much everything that i need in PM30200 table however the check no/check book id is blank for all the transactions in the table. Is there any ither way i could find the check number. I appreciate your time and help.

    Thanks,
    Uma.

    Like this

  9. Hi Victoria,

    Is there any other table than PM30200 where I can find PL Payment History information. Thanks for your time.

    Thanks,
    Chev.

    Like this

  10. Hi Victoria,

    Where would I find out if a vendor is tax exempt or not? -Thanks,

    Like this

  11. Hi Victoria,
    I have found your resources of great use. Thanks.

    I am new to GP and involved with a data migration from GP10.0. I am attempting to identify the table/s where the account balance per vendor is recorded – historical if available or if not available current balance would be sufficient. Are you able to provide any assistance with table names/SQL to obtain this?

    Thanks
    Michelle

    Like this

  12. Thanks so much for the resources you provide.

    Is there anyway to retrieve the original invoice paid from a voided check? It appears once the check is voiced and invoice is unapplied the record is no longer in PM30300.

    I need to be able to report on what the original invoice was at the time of the void.

    Thanks
    Frank

    Like this

  13. I have a new accounting person using SBF and she is pointing out some shortcomings that I hope I can get around one way or another. Using an older version 9.00.0115

    When paying a vendor you can select the invoices to pay but instead of showing the vendor invoice # is shows the voucher #. Well that isn’t helpful in any way. Can it be changed to show the vendor invoice #? My aged payables lists are incorrect as well. I select date ranges and it gives just odd results. I do see in the pm20000 table that a lot of valid invoices have a curtrxam value of 0. Some do and some don’t but as far as I can tell none of them should have a value of 0 and maybe why they are not showing on reports. Any idea why this might be happening?

    Should I update to the latest in the 9.x series to fix some oddities we are seeing?

    Like this

    • Russ,

      The voucher showing instead of the vendor invoice number is something I remember from one of our SBF customers that we converted to Dynamics GP. I do not think a service pack will address this. And I am not aware of easy way to “change” this. I am not sure if anyone is offering customizations for SBF anymore, since it was discontinued a long time ago. This is definitely not the case in Dynamics GP, there you can select invoices using the invoice numbers.

      As to your other question, there may be data that didn’t get posted properly, difficult to day without looking at it in a lot more detail. Again, I would not expect a service pack to fix it, this is more of a data issue. It’s been years since I looked at SBF, I don’t have it installed anywhere or have any customers using it anymore, so I cannot check, but if there is a check links option in there, I would try that to see if it fixes anything (make sure to make a backup first). Also, consider running the ‘historical aged trial balance’ instead, that may have better results.

      -Victoria

      Like this

  14. Hi Victoria,
    Good day to you, We received an error “Unhandled database exception:
    A save operation on table ‘PM_Distribution_HIST’ has created a duplicate key”
    After we trigger Bacth Recovery process.
    Thanks to your site i found idea on how to trace it, but this time it has different outcome
    as i search the table PM_Distribution_HIST(PM30600) no transaction found. Am i in the
    right track? Any suggestions from you would be highly appreciated…

    Thanks and God bless

    RolanVH

    Like this

  15. Victoria, I’m trying to print out a report for different types of payments (i. e. ACH, Wire, Check and credit card) for each perid, like from April 1, 2013 to April 30, 2013. Is there a way to do this in GP2010?

    Ryan D.

    Like this

    • Ryan,

      I believe you can use SmartList for this – under Purchasing > Payables Transactions add a column called Payment Entry Type.

      -Victoria

      Like this

      • I see! Thank you Victoria! But this only would work if each payment entry are coded correctly right? For example: 0=Cash, 1=Check, 2=Credit Card & 3=EFT. Otherwise the smartlists will not separate it.

        Ryan

        Like this

        • Ryan,

          Why would the data be incorrect? Are you pushing it directly into the tables? Or entering it incorrectly?

          -Victoria

          Like this

          • Victoria,

            Whoever is doing the cash application is not coding it or simply using auto-cash/auto payment, that’s why I can’t differentiate whether the payment that was posted is either cash, check, EFT or credit cards. What’s your suggestion(s) so that this report can be made?

            Ryan

            Like this

  16. Victoria – Any chance you can tell me what field/table the actually entry date for a payables transaction would be stored in? I need the date that it was physically entered and not the document date or transaction date. Any help you can give would be much appreciated, we’ve been searching everywhere for this. Thanks!

    Like this

    • Hi Tom,

      It sounds like you are looking for something like ‘created date’ which is not stored in GP. :-( The closest you can get may be the modified date (last date someone made a change), which is MODIFDT in both PM20000 and PM30200. In most companies this will be the same as the created date, but I can think of situations where they would be different.

      -Victoria

      Like this

  17. hi Victoria,

    i am trying to create a report and i could not find following fields. could you please let me know what table and name?

    vendor check#, check date
    vendor invoice#, and date

    thanks,

    aaron nguyen

    Like this

    • Aaron,

      Those fields are stored in many different tables. You might want to take a look at my page with Payables SQL views, I think you will probably find something helpful there. If not, can you give me a better idea of what you are trying to show on your report and I will try to help.

      -Victoria

      Like this

      • Victoria,

        i am trying to get data for sales tax auditing purposes. fields i am looking for include vendor name, vendor id, voucher#, vendor invoice, batch numb, PO#, frtamnt, taxamnt. i found some of them in PM00200 and PM30200.

        select top 500 a.VendName, a.VendorID, a.Address1, a.Address2, a.Address3, a.City, a.State, a.ZipCode,
        ” [Check#],
        ” [CheckDate],
        b.VCHRNMBR [Voucher#],
        ” [VendorInvoice#],
        ” VendorInvoiceDate,
        b.BACHNUMB [Batch#],
        b.DOCDATE BatchDate,
        b.PORDNMBR PoNumber,
        b.FRTAMNT FreightAmnt,
        b.TAXAMNT TaxAmntPaidtoVendor,
        b.DOCAMNT TotalInvoiceAmnt,
        ” GLDistibution –actdescr
        from PM00200 a (nolock) inner join PM30200 b (nolock) on a.VENDORID = b.VENDORID

        those fields with blank value are one i need your help.

        thanks,

        aaron

        Like this

        • Aaron,

          Are you looking to show the check and the invoice that it paid on the same line? If so, then you want to start with my SQL view for Payables payment apply detail in GP. The check # will be in the Payment_Document_Number column, check date is Payment_Date, invoice # is Apply_To_Doc_Number and invoice date is Apply_To_Doc_Date. You may need to add some of the other fields, but those should be easier than recreating the apply information query.

          -Victoria

          Like this

  18. My users have 2218 vendors they want to delete. doing it manually, even using the Mass Delete feature, will be very painful. They came to me in IT to see if I can help. Does anyone out there have a script to delete Vendors?

    Like this

  19. Hi Victoria
    I have got a question ,I have a client who wants to show the remit to address on the PM Transaction Posting Journal. In the previous versions adding the vendor address worked, but now there is a table that holds the selected information. However, that table, PM10300 Payment Work is not associated with the default report.
    I know there is a way to link the table but I can’t seem to figure it out.

    Thanks

    Vic

    Like this

  20. Victoria,

    We have been getting payables transactions that users say were never paid but are in the history table PM30200 with zero CURTRXAM and no payment applied to them. This seems to be happening more now but I can’t seem to find out why. We are on GP 2010 v 11.00.2044 (SP3). Any ideas as to what could be happening? There is only the one record in PM30200, no other tables. We end up removing and they re-enter.

    Thanks in advance

    Todd

    Like this

    • Hi Todd,

      I do not believe this is a known issue and I have not heard of something like this happening before. Have you confirmed that these transactions are not voided and are not entered for 0 dollars? Do you have any customizations or 3rd party products? Are these transactions imported or entered manually?

      -Victoria

      Like this

      • I think we may have found why but not how. We seem to be having some strange network issues where we are losing connection for a brief time and then reconnecting. it seems to casue some really weird issues. I will let you know exactly what we find when we finish our investigation.

        Thanks for the prompt reply.

        Like this

  21. Wanting to do some analysis of Vendors and Remit To Addresses and payments. Is it possible to obtain the Remit-To ID for a payment? Is the Remit-To ID stored in payables transaction tables? If so what would be the table and field name?

    Like this

  22. The amounts in the tables are not matching each other. We are on calendar year so I use histtype = ’0′ In Purchasing when I go to Cards/Summary and go to Yearly, the Amounts since last close and the calendar year do not match for about 50 vendors…

    Like this

    • Pamela,

      Unfortunately, ‘Amounts Since Last Close’ are not necessarily going to match the calendar year and that is expected behavior. I really wish MS would just take those options out, all they do is confuse people. Take a look at my year end close blog post and the discussions (as well as the comments) there about the AP and AR subledger year-end close. The AP and AR subledger year-end close is optional and can be done at any time, and about the only thing it does is update the ‘amounts since last close’, ‘year to date’ and ‘last year to date’ statistics.

      So, for example, if you ran the AP year-end close process and had not posted all of your 2011 payables transactions yet, the ‘amounts since last close’ will include whatever 2011 transactions were posted after the close, just as the label suggests. I typically advise users to only use the Calendar Year or Fiscal Year options when looking at the summaries, those will actually calculate the values shown.

      Hope that helps.
      -Victoria

      Like this

  23. How are the DOCAMNT and CURTRXAM fields used in the PM20000 table? I am seeing the CURTRXAM field zero sometimes and other times it equals the DOCAMNT or is a value less than the DOCAMNT. I assume it is less if a payment has been partially applied. The only reason I saw that they were different is b/c I am trying to use a downloaded AP aging for Smartlist and it only sums PM20000 transacctions where CURTRXAM is 0. Thanks,

    Like this

    • John,

      DOCAMNT is the original amount of the transaction, CURTRXAM is the amount still unapplied on it. You should not have any records in PM20000 where the CURTRXAM is 0, as GP should automatically move those to PM30200. If you do have these, then something has most likely gone wrong and you need to do some clean up on your data.

      -Victoria

      Like this

    • The reason the PM20000 table had transactions with a CURTRXAM amount of 0 is b/c check batches were setting in Remittance Processing still waiting to be posted. thank for the feedback.

      Like this

  24. Hi again, Victoria -
    I have another stuck payment batch. I can see what I assume are the invoices in PM10201 and I can see the payments in pm10301. But I cannot figure out how to “unmark” the batch so that my check processor can print these checks. Any suggest/recommendation would be appreciated. Yes – there was a system glitch while she was posting/printing.

    Like this

    • Hi Billie Dee,

      Last time you said you ‘reset’ the batch in SY00500. Most likely the fix is the same this time. Check batches are very tricky sometimes and the fix often depends on when it got stuck (what step of the process). If these kinds of ‘system glitches’ are happening often, I would strongly recommend trying to address whatever is causing them, so that you can avoid having to fix stuck batches as well as other potential data issues.

      -Victoria

      Like this

      • Thank you, again, Victoria. We’re trying that out. About the ongoing problem …. I know. We’re working on that, too. There’s no “quick” solution available at this time so I have do deal with the fires as they occur.

        Like this

  25. Victoria, I hope you can help me. I’m developing some integration that requires that I insert a new record in PM10100, as well as a number of other tables. The trouble I’m having is that I cannot find a specification on the definition of, or value needed for CURRNIDX. I’ve searched and dug for a relationship that this might have with another table that defines the values and types, but to no avail.

    Any guidance you can provide will be deeply appreciated.

    Thanks
    Jim

    Like this

    • Jim,

      CURRNIDX is the currency index, which you can find for every currency you have set up in GP in the MC40200 table of the system (DYNAMICS) database. If you are not using Multicurrency, you can just check what your other records in that table have and hard code that. If you are using Multicurrency, then you will need to pass in the right currency index.

      -Victoria

      Like this

      • Thank you very much for your explanation. It pointed to exactly where I needed to be. A simple look up call and I have all I need…..for that column. ;)

        Unfortunately, I ran across one other column in this same table that is baffling me and no information is making itself visible to me. The Distribution Sequence Number, DSTSQNUM column offers plenty of information and documentation about the fact that it is the sequence number for a given distribution, but there is nothing I can find that tells me what value to assign a new record. I have a feeling this one is going to be more involved.

        Thank you again. You have been a lifesaver.
        Jim

        Like this

  26. Hi Victoria,

    It’s me again Myrna, I have a problem again but this time in Payables Transaction Module where in I need to void transaction which was partially applied? Is it possible?

    Thank you and Best Regards.

    Myrna

    Like this

  27. Hi, Victoria –
    Thank you for being the #1 resource on GP. Though I’ve not posted before – I have used your advice and suggestions quite often. Thank you.
    Today: I have a lost check batch. I have been researching the following tables:
    PM10000 – doesn’t exist in the table
    PM20000 – items do exist in the table
    PM10100 – yes
    PM10200 – no
    PM10300 – the batch does exist here

    When the user ran Print Payables Checks she received: Unhandled database exception: A save operation on table ‘PM_Payment_WORK’ caused a sharing error.

    When we try to select the batch in Payables Batch Entry I get the message: This batch cannot be edited. Please verify the batch status using the Batch IDs window.

    I’m currently at a loss. Can you suggest a next plan of action?

    Like this

  28. Victoria, not sure if this is in the correct thread but I was wondering if there’s a way to change an invoice number in GP10 after the invoice has been posted. I considered changing all instances of the number via a query (it seems to show up mostly in PM tables in the company DB) but if there’s a cleaner way to do it I’d prefer that method. Thanks,

    Like this

    • Dave,

      There is no way to change an invoice number in the Dynamics GP user interface. Doing this directly in SQL is not something I would recommend because there could be dozens of tables involved, all with different names for the column with the invoice number. If you miss just one, you’re putting your data integrity as risk. It may be easier to void the invoice and re-enter it with the proper number.

      -Victoria

      Like this

  29. Hi Victoria

    I received the following error message:

    A save operation on table pm_transaction_open has created a duplicate key

    So I looked up this error message and found that I have a transaction in both the PM10000 table(work/unposted tranactions) and the PM20000 table (open/posted transactions)

    I know I should have the same transaction in both tables. The transaction should be in PM20000 table only. How should I go about getting rid of the transaction in the PM10000 table?

    Like this

    • Hi Erica,

      First step I would recommend would be to run check links, although it may not fix this. When I have seen this happen in the past, I have had to go into the database directly to fix this. Unfortunately, without looking at the data and the related transactions in other tables, it is impossible to say what exactly the fix should be. If you are 100% certain that everything in the PM20000 table and related tables is fine, you can delete the entry from the PM10000 table and run check links to remove all the related records for that unposted entry. If you are not 100% comfortable with this, I would recommend getting some help from your GP Partner or GP Support. I am not a big fan or recommending fixes like this directly in SQL without looking at it myself.

      -Victoria

      Like this

  30. I have an invoice that does not show up under the customer transaction inquiry. But the inventory is showing allocated. I have a printed copy of the invoice, but can’t find it in the system. I have run check links, but and reconcile but can’t seem to find this missing invoice.

    Any help would be great.

    Like this

    • Papillon,

      Sounds like maybe the invoice didn’t post yet. You posted this question under PM (payables) tables, but this sounds like a Sales Order Processing invoice? If I am misunderstanding, please let me know.

      First thing I would start with is looking for this invoice in SmartList. Under SmartList | Sales | Sales Transactions add a column called Document Status and then do a search for your invoice number. (The search should be SOP Number is equal to Your Invoice Number.) Does it come up? What is the Document Status?

      -Victoria

      Like this

      • Victoria,

        Sorry for posting in the wrong forum I am not sure whether I should switch, but I thought I would reply to your thread.

        The invoice does appear in my smartlist report. The document status is showing “New”, but when you try to open it, it states it has already been posted.

        Like this

        • Papillon,

          Document Status should only have values of Posted or Unposted. If you are seeing New, you are most likely looking at Sales Document Status, which is a different column. Can you confirm What the Document Status says for this invoice? Also, how many times does this invoice show up in the list on the Sales Transactions SmartList?

          -Victoria

          Like this

          • Victoria,

            It shows unposted, but does not appear in a batch available to post.

            Like this

            • Does it only show up once? Where are you looking to see that it’s not in a batch available to post?

              It’s possible that another user has the transaction locked. Unfortunately, without looking at it, it’s difficult to help further and you might need to get your GP Partner or GP Support to help you with this.

              If you want to try it on your own, you could try the following:

              Make sure all users are out of GP, make a backup, then run the following in SQL:

              delete from DYNAMICS..ACTIVITY
              delete from DYNAMICS..SY00800
              delete from DYNAMICS..SY00801
              delete from tempdb..DEX_LOCK
              delete from tempdb..DEX_SESSION
              

              Once that is done, run Check Links on the Sales Series.

              -Victoria

              Like this

              • No other user has it open. It only shows up once, it is in one batch, however when you run the Edit List for the batch is does not appear. And when you try and select it from the smartlist you can’t even open it, it just comes up and says it has already been posted.

                It is almost as if it partially posted, but it is not in a stuck batch.

                I will try running your script.

                Thank you for your help.

                Like this

  31. We are moving from GP10 to GP2010 and I noticed the AP section has changed a bit especially when it came to EFT is there any documentation on this?

    Like this

    • Hi Michael,

      I believe there is a section on EFT tables/transaction flows in the GP 2010 SDK. Look for it under Electronic Banking. That said, I do not believe too much has changed between GP 10 and GP 2010 for EFT. Was there something specific that you noticed a change in?

      -Victoria

      Like this

      • I will look into the document. I have nothing specific yet we are still in testing phase. I just saw a few things were different or added especially in check processing.

        Like this

  32. Hi Victoria, thank you very much for maintaining such a helpfull website.
    Any idea which table contains the data for Additional Information field of Internet Information form which we get when click i icon on Vendor Address Maintenance form.
    Regards.

    Like this

  33. Hello Victoria,

    Recently I wrote a Cash Requirements SQL report using the PM20000 table, everything on it was good until my accounting dept noticed an issue with available discounts. I had written the report to use the DISCAMNT when the due date was less than the current date which worked fine.

    Here’s what the problem is:
    Some of our vendors do not stick to their discount terms and change it per invoice or may set a specific due date based on when the invoice was shipped so instead of using the DISCAMNT or the DISCDATE (which is based of the vendor discount terms) they want to have the terms on the document instead. What table and what field would that be in? I’ve been looking for two days without finding anything… Thanks!

    Like this

    • Hi Bill,

      If the users are entering this with the invoices using the payment terms, then I believe the discount date will be stored in the PM20000 table in the DISCDATE field. For the percentage of the discount you will most likely need to go to the payment terms table – SY03300. Hope that helps.

      -Victoria

      Like this

      • Thanks for that Victoria, is there anyway to show the text of the Terms field? I’m told we have several vendors that we manually change the amount for and the date isn’t really what they’re looking for on the report. Some of our vendors accept discounted pricing even after the term they specify so we really want it to just show the Terms field. Some of the terms we have also have a set date or two set dates in a month that it gets paid on and this messes up the DISCDATE field so it shows (for example one that has a term of “1% 10th or 25th” means depending on when it comes in the discount of 1% it’s either on the 10th of the month or the 25th of the month depending on the date it came in) it automatically gives 1% for the 10 day period after the DOCDATE and not actually on the 10th.

        It’s really confusing and ultimately if they can have the terms field show they’ll just do the determination on their own. Is there any way of just showing that text?

        Like this

  34. Hi Victoria, As ever your site is super useful, always use it for reference!

    I have a question which I hope you can help with – We have a couple of hundred PM transactions on a single creditor which have been fully applied (i.e. current trx amount is zero) but the document status is still OPEN – I’ve tried reconciling the creditor account, and also running check links for purchasing, but nothing is moving these transactions into history.

    I have some SQL knowledge, so I could manually change them to history, but not certain of the implications…

    Many thanks
    Ben

    Like this

    • Hi Ben,

      Thanks for the kind words!

      I don’t typically recommend moving these to history manually, that would be the last resort. Can you see if you have duplicate apply records for these in the PM10200 and PM30300 tables? If these transactions are invoices, you’re looking for the APTODCNM to be the invoice number and APTODCTY to be 1. If you find the same record in both tables, then I would test deleting the record from PM10200, then running Reconcile for the vendor to see if that moves the invoice to history.

      -Victoria

      Like this

  35. Victoria – thank you so much for this excellent site! I appreciate both the information, and your super helpful and positive attitude!

    I do have a question, though, relating to the DOCTYPE field in the PM30200 table – in ALL of the documentation that I am able to find (which, of course, must not be everything) I can only find the values for 1-6. However, I am showing DOCTYPEs of 7 (a fairly small number, though – 60 out of 160k). What does this DOCTYPE translate to?

    Like this

    • Hi Joe,

      Thanks for the kind words!

      I have not seen a DOCTYPE of 7 in any of the data I have worked with. So it is either something pretty uncommon, or something custom. Can you take a look at the transactions for this doctype in the GP user interface and see if you notice a pattern?

      -Victoria

      Like this

      • Well – it’s odd, but nothing seems odd about these. They have descriptions like “MONTHLY RENT”, “parking increase”, and “REFILL POSTAGE MACHINE 11 PAYM”.

        This is version 10.00.1061.

        I guess I’ll just have to pull out a couple and have the client investigate.

        Thanks for responding so quickly, Victoria! :-)

        Joe

        Like this

  36. Hi Victoria,

    I just want to ask about the Physical Table name of Payables Transaction Entry Distribution?
    I’m tryin to find it on the PM tables. but I can’t see my sample data.

    Thank you so much.

    -Rhon

    Like this

  37. Your site has been immensely helpful for me and I wanted to thank you for that. I’ve also just purchased your book!

    Like this

  38. Victoria,

    Thank you so much. This completely makes sense. now.

    Cheers ~ Don

    Like this

  39. Victoria,

    What a great site you have built. Lots of great information. I do appreciate it. I have a question for you. Have you ever run into a situation where an AP check is voided but the voided check has no distributions?

    Thanks in advance.
    Don

    Like this

    • Hi Don,

      Thanks! Yes, this happens when a check is voided prior to being posted. For example: I am printing checks, I click Print on the Print Checks window and something goes wrong with the printing…or, like I just did yesterday, I had my printer set to duplex and my checks printed on both sides of the check stock. So, right away, I chose to Reprint the checks – this automatically voids the first set of checks and reprints a new set of them. The original set of checks, the one that was automatically voided, has no GL distributions. Hope this helps.

      -Victoria

      Like this

  40. Hey Victoria,
    Do you happen to know how to run a SQL query that mimics the PM HATB Detail Report? I’d like to add a few columns by doing a join statement that would include things like the GL Account #, which I could use the Voucher # to connect to, but I can’t figure out where the Due Period fields are found (i.e. Current, 1-30 Days, 31-60 Days). Also not sure how to run that query to look at the database As Of a certain date. I’ve looked all over for answers but can’t seem to find anything. Any thoughts would be helpful, thanks.

    Like this

  41. Hello Victoria,

    Awesome work, I can’t believe I hadn’t discovered you before!

    Please your kind help with this:

    For some reason, accounting stopped using the “Apply To” procedure in Payables since 2008! They passed payment transactions directly to the account through the GL Transactions entry form. Now we have thousands of open Payables Transactions since then (they did enter the invoices in the Payables Transaction form). Of course Vendor balances & aging is totally wrong.

    What would be your advice for us to do here keeping in mind that starting 1/1/2012 I trained our people to use the “Apply To” procedure properly. But all transaction before 1/1/2012 are still open.

    My ideas:
    - Delete all the old open/historical payable transactions from within the DB using SQL (don’t really know how to do that but willing to learn)
    - For each vendor create a cheque that will be “Applied To” all the old open transactions to close them and then void the cheque (don’t know how this affects the GL and whether it will mess anything up)

    We are still on GP 7.0 by-the-way.

    Much obliged and God bless you!

    Like this

    • ASH,

      I would recommend entering a Credit Memo (not a payment) for the total amount you want to clear out for each vendor. Apply the credit memos to all the old open transactions to wipe them out. If you don’t want these credit memos to change the GL (because it is already correct), you can use the same account for the debit and credit side of the credit memos.

      -Victoria

      Like this

      • OK, that sounds good. But we have so many Vendors it would be very time consuming to do this.

        How about deleting the open/historical transactions from within the DB? I’ve been researching the different tables involved to do this (PM10100, PM30200, PM30300, PM30600, etc..).. would you be able to point me to where I can disect this issue (from which tables to delete) and know how removing these records would affect other tables, totals, etc..

        With many thanks again!

        Like this

        • Ash,

          I would strongly discourage you from deleting anything directly in the database and would frankly not feel comfortable giving someone directions on this. Best case scenario, you will not have data that you need later. Worst case scenario, it will break other things. Have each of your users enter 10 a day of these. It will take some time, but it will all get done.

          -Victoria

          Like this

          • Wise advice, I thank you for your support. We will do as you indicated.

            Like this

          • Sorry to re-open the subject but I want to further ask:

            What if we don’t care about the purchasing transactions can we run “File>Maintenance>Clear Data” to clear the Purchasing transactions and then enter vendor opening balances for the year and start from scratch. Would that process produce this result or would it destroy some other data in the DB?

            Thanks!

            Like this

  42. Hello Victoria,

    First off thank you for the awesome GP resource!

    Sorry if you’ve covered this before but I’m looking for a way to link “Date Invoice Paid Off” in the PM30300 table to SOPNUMBE in SOP30200. I saw your Payables Apply Detail view but the apply to and apply from doc numbers don’t seem to correspond to any of the SOP numbers I could find.

    Looking for this linkage so that I can filter a list of SOPNUMBEs based on date when they’ve been paid.

    Thanks again.

    Like this

    • Sorry “Date Invoice Paid Off” (DINVPDOF) is in PM30200 not PM30300.

      Like this

      • Hi Alec,

        There is technically no SOP (Sales Order Processing) to PM (Payables Management) link in GP, however, depending your business and transaction flow it may be possible to link these with a few steps in-between (including inventory and POP).

        Can you give me some background on how you need to use this? For your business, what is the business logic linking invoices sent to customers (SOP) with payables invoices paid to vendors (PM)?

        -Victoria

        Like this

        • Sure. I’m trying to build a SQL view that will be used to calculate commissions for our sales reps in crystal. I have the view select and filter all the SOP line items that commission will be calculated for, the last filter needed is to select only invoice line items that were paid off on the previous month.

          We have smartlists that give this information, unfortunately I have no idea how the system is linking behind the scenes.

          I’m not well versed in financial stuff (learning slowly) but from what I gather posted invoices sit until we receive payment, then once a month the credit manager writes checks out to balance accounts and at that point the VP of finance can “see” which invoices are “paid off” in our system and calculate commissions.

          Sorry I’m not sure if that was any help. If I need to ask more detailed questions from our finance department please let me know.

          Thank you very much for looking at this for me!

          Like this

  43. Hi victoria, your site is very helpful!
    I recently ran into a strange issue during bank reconciliation. We are running GP 10, and reconciled on 1/9, the Outstanding Transactions Report gives a different (higher) total than the outstanding line on the Reconciliation Posting Journal. I went through the list, and it seems that all the transactions that make up the discrepancy were cleared on 1/3. As far as i know, these reports are automatically printed when you reconcile, so there couldn’t have been a date issue. in the past these have tied to the penny. Ever heard of this happening, what the cause could be? We run on a calendar year, but the year end processes were not run before these trx were cleared.

    thanks for your help!

    Like this

    • Hi Mike,

      This does not sound like something I have run into in the the past, but it’s really difficult to tell without actually seeing it. :-( Have you asked your GP partner to look at this for you and see if they can provide some guidance?

      -Victoria

      Like this

  44. Hi Victoria,

    In which table can I find creditor e-mail addresses in GP10?

    Like this

  45. Victoria,

    I’m a SQL DBA with minimal accounting skills but strong SQL skills. My controller noticed that in the AP SmartList showing vendor information that 1099 box number is not identified. I am trying to figure out how to do a SQL query to show it but I am greatly confused with the relationship between tables PM00200 and PM40104. There are multiple periods in 40104 for example but when I look at a particular 1099 vender in GP there is simply a box number in the options section of the vendor master. Can you shed some light on this for me? Do you have one of your spiffy views that I can use for a custom SmartList? Thanks – Joe

    Like this

    • Hi Joe,

      PM40104 is simply the setup of the minimums for each 1099 type/box combination. Are you looking to show the 1099 box set up for each vendor? If so, that is stored in the vendor master table (PM00200) in the TEN99BOXNUMBER field. If that’s not what you meant, please give me some more details.

      -Victoria

      Like this

      • Wow, Okay, I overlooked that. How can I get the description of the box?

        Like this

        • Joe,

          This is a weird one. From what I can see, the initial descriptions are hard-coded…at least I don’t see where they would be in a table. So the only place you can see them is on the 1099 Setup window (GP | Tools | Setup | Purchasing | Payables | click 1099 Setup button). However, as soon as you change one of those, they will show up in the PM40104 table. I suppose if you really wanted to have them in a table, you could go through each box, add an x or something to each description, save, then go back and take the x’s out and you will have populated the PM40104 with the descriptions that you can now link to in SQL.

          -Victoria

          Like this

          • That is weird. But your suggestion does indeed work. Thanks.

            Like this

            • Actually there is still something I don’t understand. A particular vendor in PM00200 has TEN99TYPE = 4 and TEN99BOXNUMBER = 7. However there are no rows in PM40104 with TEN99TYPE = 4. In fact based on the description it looks like its a box number with type = 3. What do you think about this?

              Like this

              • Joe,

                I am not seeing anything about this in the documentation, but based on the data in our GP, I am guessing that you need to subtract 1 from the TEN99TYPE when you are linking from PM00200 to PM40104. In other words, PM00200.TEN99TYPE-1 = PM40104.TEN99TYPE

                Looks like this was a little coding snafu when the new 1099 stuff was added a couple of versions ago. ;-(

                -Victoria

                Like this

                • Well, that will certainly work. It will be interesting to see if my SmartList breaks one day if this gets fixed in a update. Thanks so much for your help.

                  Like this

                  • Joe,

                    Glad that is working for you. In my experience it is very unlikely that values like this will ever change, and I have not seen GP do this in the past. This 1099 functionality was added in GP 10.0, which came out over 4 years ago. So this has been in the code for something like 5+ years if you consider the development and beta testing time frame. There is a misspelling in one of the inventory tables, where a field name says ‘ATY’ instead of ‘QTY’ and it’s been there as long as I remember. I think it’s just easier for all concerned to leave these things as they are. :-)

                    -Victoria

                    Like this

  46. Hi Victoria,

    I’ve got a bit of a mess on my hands that I’m hoping you can help me with. There are two separate but similar issues.

    1. A payables batch was created. There were 36 checks in the batch. Upon completing the batch it was realized that the wrong check date was used. The entire check batch was voided. The problem is that the batch got posted to the GL via Financial Series Post. But the void transactions were deleted in the financial batches. The user didn’t think that the initial transactions had posted to the GL so was attempting to prevent all of the check transactions from hitting the GL. But the result is that the transactions show as voided, but only the check batch process hit the GL. Any ideas?

    I want to be sure that the AP Aging doesn’t get messed up and out of alignment with the Balance sheet in the process of all this..

    2. Similar to the first issue, but this time it was an invoice that was voided rather than a check.

    When we enter invoices it is typically Dr 2191 (Accrued Purchases Clearing) & CR (Accts Payable). As a result of voiding the invoice in the purchasing module, the invoice shows voided. But the corresponding entries were deleted out of financial batches and never posted to the GL. I could make a entry to the GL, but would that throw off the AP aging?

    Sorry for the long post. Any help would be great!

    Like this

    • David,

      In general, I would recommend disallowing deleting transactions in the GL. That will only get you into more trouble – since there is no record of what was deleted you now have a much more difficult job when looking to fix this. Except for some exceptional circumstances, it is typically better to let everything post, then create adjustments. And it sounds like you already know that Purchasing invoices should not be voided in Payables. The user voiding this should have gotten a warning letting them know this is not advised. If this is a not an isolated incident, consider limiting access to the Void window(s) for users that may not fully understand the implications.

      All of that is for the future, though. Specifically for these issues, I am hesitant to offer advice without looking at the data in your system. There are just too many moving parts and with things having been deleted, I would not be comfortable that I have the full picture without checking it myself. For example – with the check batch there are also Bank Rec implications. For the voided invoice, there may also be Inventory to be considered. And with partial information, any fixes suggested may only make matters worse. I would recommend working with your GP partner or GP Support to work through these.

      -Victoria

      Like this

  47. Hi Victoria,

    I hope all is well. I’ve encountered a roadblock. I’ve noticed that there’s a payable transaction invoice in OPEN status with a non zero balance. When the user printed the checks she noticed a 0.53 cent balance. She voided the 0.53 cent and now it’s in OPEN status with a balance of 0.53 cent. How can we move this to HIST? Can we leave the invoice in OPEN status when executing our year end? Thanks Victoria.

    Like this

    • Hi Kevin,

      I am not aware of any way in GP to void an invoice partially, so I suspect it was not $0.53 left unpaid on the invoice that was voided. Maybe a check was created for the $0.53 cents and that check was voided? If you go to Inquiry | Purchasing | Transaction by Vendor, bring up the invoice and dins that invoice – what does is the Unapplied Amount for it?

      While it is certainly best to clean things up prior to performing month end or year end tasks, having an open balance on a transaction (whether right or not) should not prevent you from performing the year end close. Nor would having a transaction with an open status instead of history cause any problems for the year-end routine in payables. Although my 2 cents is that the payables year-end close is not needed for most companies anyway, and can simply be skipped. I talk more about that here.

      -Victoria

      Like this

  48. Hello Victoria,
    I’m trying to correct a payalbes transaction that has the wrong posting date, but when I go in Tools, Transaction, Financial, General, I selected Correct, backout a journal entry. I get this error message “This transacton was entered in Paybles Management and cannot be corrected in General Ledger, Use Payables Management to correct the transaction”. Is there anything that I can do?

    Like this

    • LaTeesha,

      If you correct this in the GL you will not fix the issue in Payables. That’s what the message is trying to tell you. If you’re ok with simply fixing it in the GL, you don’t need to use the ‘Correct’ option, you can simply enter a GL transaction. However, if you want to fix it in both Payables and GL, you may need to void the transaction in Payables, then re-enter it with the proper dates.

      -Victoria

      Like this

  49. Hi,
    We are having problems with shipment methods disappearing from vendor records. Once or twice a week anywhere between 1 and 10 vendors lose this setting. There are 3 vendors this occurs to regularly but they are not the only ones it happens too. Have you heard of this happening before? or know what might be causing it? We are using GP 10. Thanks for your help.

    Mike

    Like this

    • Hi Mike,

      I have not seen this happen by itself in out-of-the-box GP. And I cannot imagine a scenario where this would happen without either user intervention in the UI or some custom process updating records in the database. Not sure how much investigation you have done so far into this, but here are some questions that may help track this down:

      • Is it possible that a user is making changes to vendor classes and rolling down the changes?
      • Is is possible that a user is changing this during data entry for some reason? No one will probably admit to this, but it’s worth asking…
      • Shipping methods for vendors are address specific. Is it possible someone is changing the default address that is showing on the main vendor card? Or are the shipping methods disappearing from the Vendor Address Maintenance window as well?
      • Do you have any imports of data into GP going on? Do any of them update vendor information?
      • Do you have any SQL triggers on the PM00200 and PM00300 tables? Out of the box, I believe there is only one trigger enabled for each: zDT_PM00200U and zDT_PM00300U. (I am looking in GP 2010, but I believe it should be the same in GP 10.0.)
      • Take a look at the time stamp in the DEX_ROW_TS column in both the PM00200 and PM00300 tables. (Remember this is UCT, so you will need to convert to your time zone.) This will not tell you who changed the data, but will tell you when it was changed and might give you an idea as to the cause. For example, if all of them are changed at the same time, then it’s not a user changing these one at a time and if you know you have something else scheduled at the same time, that may give you some ideas.
      • Do you have anything in place that may be synchronizing data between GP and another system? We typically see this with CRM and customers, but the concept is the same.

      If none of this helps, you might consider creating a trigger on any changes to the PM00200 and PM00300 tables so that you can track down what is causing this.

      -Victoria

      Like this

      • Hi Victoria,

        Thanks for your help. It looks like someone was making changes to vendor addresses and that was clearing the shipment method.

        Mike

        Like this

  50. Hello Vic,
    My Summary and Detailed Inquiry are not the same,please help?, I tried utilities reconcile in all modules.Even check links ,still the problem persists.

    Like this

    • Aubrey,

      If Reconcile and Check Links did not fix it, you will need to investigate your data to find out what is causing the difference and based on that work on fixing it. If you do not have experience with this, I would recommend getting help fro your partner or GP Support for something like this.

      -Victoria

      Like this

  51. Hi Victoria,

    I have a scenario where i need to unapply some of the payments from invoices after the payment is created by select checks. I have a stored procedure that runs after the batch is created which removes the applied payments from the invoices and deletes the payment number if all the invoices are deleted for that Vendor. Everything works fine if the distribution accounts are default when the invoice was created (one debit account and one credit account).

    I am having problem when distribution in invoice is divided into multiple accounts. In this case i am not sure how the cash accounts are picked when the payment is created. If I need to unapply payment from one of the invoices how do i knw from which accounts i need to remove the invoice amounts.

    For example I have an invoice which has two purch accounts and two pay accounts.

    On the payment the pay accounts remain same but the purch accounts are replaced by two cash accounts? How does GP select those cash accounts for the payment?

    Thanks
    -Divyanshu

    Like this

    • Divyanshu,

      A payment created by GP should have one cash account and one or more Payables accounts, not the other way around, so it’s possible there is an issue with setup in your GP, where instead of a payables account something is set up with a cash account. However, my bigger question is why are you doing this? I am also not clear on why you need to do anything with GL accounts if all you’re doing is deleting apply records?

      As a general rule, manipulating data like this outside of the GP user interface is something I would advise against, as you are putting the integrity of your data as risk. At the very least, if you have decided that it is worth the risk and you absolutely have to do this, you need to work with someone that knows the transaction flow and tables in GP very well to help you create and test this.

      -Victoria

      Like this

  52. Hi Victoria,

    I’m hoping that you can help me. Here is the scenario:

    Purchase Order is raised in Workplace for an inventory item.
    The purchase order is then receipted through Great Plains.
    Then it is invoiced through Great Plains.
    When it’s invoiced, the invoicing batch gets frozen not allowing user to continute posting – the batch gets marked with the status of 1. When inquiring on this invoice through Enquiry –> Purchasing by Creditor, the invoice comes up blank – i.e. only the header exists. The details of the invoice can be seen through the POP module.

    Could you please help figure out what’s happening?

    Much appreciated,
    Depar

    Like this

  53. greetings. Great site, has been very helpful. I have a job where I ‘piece together’ tables from various AP systems. I’m very familiar with the pm30200 and pm30300 tables. I know that things that are voided in the historical trans do not join back to the ‘apply to’ table, yet I’d like to be able to put together the full detail for these voided checks and invoices (eg. to see what invoices went with the voided checks and vice versa).

    Is this possible? Do you know what other tables are needed in order to do this? thnx much, J

    Like this

    • Jay,

      To my knowledge GP does not keep track of the historical apply information for voids. Once a transaction is voided, all the apply records are deleted. Unless you create your own additional tracking for this, I am not sure how you can report on this.

      -Victoria

      Like this

      • Thanks for the quick reply. None of it really hinders us, but it’s the only system we work with that behaves like this. It’s also the only system where ‘credits’ are a means of paying off an invoice, whereas in the other systems the credits are negative invoices associated with a check. But the GP data model is all logical and rational. Now SAP otoh…

        Like this

  54. Hi Victoria,
    Could you please advise how it is possible to get the supplier balance as of any date by exporting the database to excel or access.

    Thanking you in advance

    Regards
    Niju David

    Like this

    • Niju,

      It sounds like you may be looking for a ‘historical balance’ – this is not possible without writing some pretty complicated code. Daily balances are not stored anywhere in the GP database. If you are looking for a current balance, you can get that from the CURRBLNC field in the PM00201 table.

      -Victoria

      Like this

  55. Hi Victoria,

    I hope all is well. The AP crew has an issue with confirming some checks.

    1) The user printed a Vendor Cheque Register Report and 2 checks were missing. It basically went from 02115 to 02118. MIssing checks were 02116 and 02117. Is there a way to pull a list of all the check #’s?

    2) This is an interco relationship. The Vendor Cheque Register Report indicates a payment for vendor A but the payment does not exist in the interco company. I ran the smartlist for both and couldn’t find that payment anywhere.

    Any help would be greatly appreciated. Thank you.

    Like this

    • Kevin,

      I am not sure if there are differences if you’re not using the US Bank Rec module…but have you tried looking in the Checkbook Register (this is different from the Vendor Check Register, which only looks at checks entered in payables)? On my install it is under Inquiry | Financial | Checkbook Register. Or, in SmartList, I would look in Bank Transactions (as opposed to Payables transactions).

      -Victoria

      Like this

  56. Hi Victoria
    Please could you help me with this problem
    I’m trying to void a Return posted from PM, because the user put an amount in the cash field by mistake, so that return went to Historical, and we got this message:
    “There is a return (No: XXX) with a payment marked for voiding. The payment on this return will not update the CBM when voided and would cause an imbalance in the General Ledger. Please manually enter contra entries for the return and payment.” What would the contra entries for the return and payment be?
    Thanks a lot

    Like this

    • Hi FAC,

      I have not worked with CBM, so I am not 100% sure what entry the void will create. You will need to look at the transaction(s) created by the void and determine what is missing. I suspect, since there was a cash increase entered with the return, you will need to enter a cash decrease to fix it.

      -Victoria

      Like this

  57. Hello Victoria, I have an issue with a checks that were applied to invoices, and then the checks were voided. Usually this would released the invoices so we can apply non-voided checks to them, but it didn’t happen. I have investigated PM30200,PM30300, and PM20100, but they only had one line in PM30200 and when I removed it in Test, it didn’t release the Invoice. Can you help?

    Like this

  58. Hi Victoria
    please I got a problem in the Inquiry / Finantial / Checkbook register

    I got a PM manual payment that was voided in Void Historical Transactions, but in Checkbook register inquiry is not Voided

    I check PM30200 and VOIDED = 1 but in CM20200 VOIDED = 0

    Am I missing some process? As far as i remember when I voided in PM it was also voided in CM

    Thanks a lot Victoria

    Like this

    • Hi FAC,

      I have seen this happen when you void a payment in Payables that has already been reconciled in Bank Rec. Since it is reconciled, the original transaction cannot be changed and instead a new ‘reversing’ transaction is created – it looks just like the original payment in CM20200, with the same CMTrxNum and CMTrxType, but there is a new CMRECNUM and the amount is negative. Hope this helps.

      -Victoria

      Like this

  59. Hi Victoria,
    Thank you so much.

    Like this

  60. Hi Victoria,
    We are planning to start electronic payments through our bank to our vendors by using EFT 80 byte files. Do we need to register EFT payable module? If yes, then how should we proceed and how can we have an estimate for the cost involved in purchasing and implementing the module?

    Like this

    • Hi Zafar,

      Yes, you would need to own and have registered the EFT for Payables module, however the details of what you would need specifically would depend on your GP licensing mode and where you are located. Your Dynamics GP partner would be in a much better position to answer questions about purchasing and implementing, as they will have more knowledge about your licensing as well as your environment.

      -Victoria

      Like this

  61. Vic,
    RE: VOIDED TRX
    Kindly help out on this. When you print creditors statements (Tool>Routines>Sales>Statements)
    it does print with VOIDED transactions,how can print the same statements without these voided transactions. However they are not used for calculating. Your input will b greatly appreciated.
    Regards,
    Charles

    Like this

    • Charles,

      If you move your paid transactions to history (Tools | Routines | Sales | Paid Transaction Removal), I believe the voids will come off the statement. Otherwise, you could possibly modify the statement report to exclude voids. Or you could do what I do and use a custom Crystal report for customer statements where you can add any logic you want easily. :-)

      -Victoria

      Like this

  62. Hello Victoria,

    I want to write an eMail notification procedure that notifies employees when we process an expense reimbursement. I see that I can find the transactions in PM030200 just fine. What I’d like to provide additionally are the documents that the payment applied to. I thought that would PM030300 joining like this:

    from
    PM30200 a
    inner join PM30300 b
    on a.VCHRNMBR = b.APTVCHNM

    However there are rows in PM30200 not in PM30300. I am confused. Can you help?

    Thanks.

    Like this

  63. Hi Victoria
    Please do you know what are these fields in PM30300?
    Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr

    I was using RLGANLOS field in a report to take the gain or loss ammount in an apply from different currencies transactions but it was not the correct current ammount GP was giving for a vendor.

    I realized that it was using the ammount in Settled_Loss_DebitCurrTr and it is different from RLGANLOS.

    I am kind of confused, does this happens when the apply date is different from the payment document date?
    i have another transactions where i use RLGANLOS and it fits the ammount in GP.

    I hope you can understand me.

    Thanks a lot Victoria

    Like this

    • Hi FAC,

      I do not work with Multicurrency transactions enough to easily answer this without additional testing and research. I would recommend checking on this with GP support so they can give you the best advice on what you are looking to do.

      -Victoria

      Like this

  64. Hi Victoria

    I have a PM batch that has distribution errors which I need to correct…….I’ve moved these transaction from one batch to another to enable me to post the transaction that had no errors. Now I cannot access these error records because of the following error message
    ” This voucher number already exists. Please enter a new voucher number”………..These transactions are currently in WORK.

    How do I resolve this issue….

    Kind Regards

    Like this

    • Hi Colin,

      How exactly did you move these transactions into another batch? In GP, or in the tables? How are you trying to access them? If you look them up in the Payables Transactions SmartList, does each transaction appear once or twice? What is the Document Status for them?

      -Victoria

      Like this

  65. Hi Victoria,

    I love your site and all of the great information that you provide. I’m hoping that I’m posting this question/comment in the right place. I have a client who has a very strange problem, and I can see what happened but I can’t really figure out why. I thought you might be able to shed some light on it so we can avoid having it happen again. The user who keyed these cheques doesn’t key very often, and I suspect that she did something wrong but I have no idea what.

    A cheque was written for Vendor A, and a second cheque was written for Vendor B (dated a month later). There were no commonalities between the vendors, the document numbers (being paid), the amounts, or the cheque numbers that I can find. However, the second cheque was assigned the same Payment Number as the first. As a result, the distribution for the second cheque got added to the first cheque. Then the first cheque got voided, so they both automatically got voided (distribution ultimately had 8 lines). Neither the second cheque nor the void showed up on Vendor B’s account at all, so the client re-entered them, which caused the bank to be out of balance because they had hit the GL in the first place. When I looked up the document by document number, it came up with Vendor B’s ID, but any other way I drilled down to it, it always associated the transaction with Vendor A. The client tried to fix the problem on their own and created dozens of cheques and voids, making it even more difficult to follow. The Vendor accounts all appear to be correct ultimately. I had to make manual entries to reverse the Vendor B cheque that didn’t show up in the vendor account, and the related void. They would have cancelled each other out, except that they happened in different periods.

    As for the tables, it’s hard to even articulate how this hit them. It doesn’t show up in the PM10XXX or PM20XXX tables. All of the lines appear to be associated with Vendor A and the correct amount of the original Vendor A cheque in the PM30XXX tables. However, the Control Number in PM00400 showed the data for the Vendor B cheque and no record of the Vendor A cheque. I’m not seeing the extra distribution lines in PM anywhere, although they show up in the drill-down. They appear to have only impacted the GL.

    I did run Check Links, and it made a small adjustment which was to change the Vendor ID to Vendor A’s ID in PM00400, which wasn’t correct either, because now Vendor A was associated with the Document #, etc that actually belonged to Vendor B!

    I also tried to reconcile each of the vendors in question using Utilities>Purchasing>Reconcile, and it shows these amounts as current balances before the reconciliation, and zero balances after the reconciliation although there is no obvious change from the front end as they appeared to have zero balances even before I ran the utility.

    This happened for two different vendor combos at roughly the same time (only different batches). Vendor B was the same in both cases (but different cheques), but they attached to different Vendor A’s. PMCHQ# was three digits apart in the first scenario, and right next to each other in the second. Second scenario was nine digits after the first if this makes any sense.

    So, now the Vendor balances appear to be correct and the GL balances appear to be correct (final reconciliation is still outstanding), but the information in the distributions and the drill-downs for the various documents are wrong. Should I be worried about this coming back to haunt me? Is there anything we can do to avoid this happening in the future?

    Thanks for your help. Have a great day!

    Kimberley

    Like this

    • Hi Kimberley,

      From what you’re describing, it sounds like something went wrong with the entry of the original check to Vendor B. And by ‘something’ I mean there was some kind of an issue between the client and the server, which allowed GP to do something that breaks a business/code rule. It’s hard to say if it will come back to haunt you, if running check links again changes the data again, that might be a problem and may be something you want to talk to GP support about to see if they have a way to fix this more permanently.

      The only way I can see this happening again is if that same situation occurred. Since it’s not something that is supposed to be allowed to happen and since we don’t know exactly what caused it (and most likely never will), I am not sure how you can actively prevent it in the future. Do you know if this client has a history of network connectivity type errors? Or any customizations around payables?

      -Victoria

      Like this

      • Hi Victoria,

        Thanks for your prompt response.

        I did try running check links a second time, and it did not result in any errors, so I think we’re okay there.

        This is a fairly new installation using terminal server. There are no customizations, and they haven’t had any connectivity issues that I’m aware of. Since November, there has been one hung batch.

        I think we should just leave it and hope for the best! :)

        Kimberley

        Like this

    • I changed the company addresses to display on my purchase orders recently. After I changed them, I get an error: “Error Registering table PM_Address_MSTR” I don’t know how to even begin to fix this. Please Help.

      Like this

    • Hi Victoria,

      Could you help me with the following please? I changed the company address recently that gets displayed in the ship to address on my purchase order print out, I did not change any IDs just the detail, but now I get an error: “Error registering table PM_Address_MSTR” Please help! I’m farely new to GP and I’m not sure where to begin.

      Like this

      • Sheila,

        Typically this error indicates a stuck record in the GP SQL Server, although it could be something else I have not seen before, also. If exiting GP and logging back in does not resolve this, it is not something you will be able to fix without knowledge and access to the SQL Server and I would recommend asking your GP administrator or GP partner for help with this.

        -Victoria

        Like this

        • Victoria,
          I have experienced a similar situation with a customer of ours. When the customer accesses Purchasing they receive an error titled “Error registering table POP_PO_Tax”. The error displays when trying to add a line item to the Purchase Order and then erases the line. I also receive errors registering tables on Manufacturing Order Entry window titled “error registering table syEmailReportSetup”.

          Things were working fine end of day the previous day. What would cause this error? The customer is running SQL server 2008 R2 on one server and then the users access a remote app from their desktops which is tied to a terminal server seperate from the SQL Server. The SQL Server GP installation worked fine and did not experience any errors on purchasing or manufacturing. The Terminal Server was the only machine experiencing the issue.

          Our remedy to the situation was to restart the Terminal Server which seems to have fixed the issue. Just wondering if you, or anyone else have experienced a similar situation and know of a concrete reason why it happens.

          The only visible change that I could attribute to the situation was that the SQL Server machine had restarted from installing some windows updates the night before. Could the update and restart have caused the Term Server from communicating appropriately with the SQL server?

          Thank You for your time
          Sean

          Like this

          • Hi Sean,

            I have not run into this before. Really impossible to tell exactly why this happened without a lot more investigation and possibly without being able to reproduce it. Glad you were able to resolve it by restarting. :-)

            -Victoria

            Like this

    • Hi Victoria…….

      Public holidays here in SA………therefore late reply.

      1 Tranx moved in GP
      2 Trying to access Transactions > Purchasing > Payables transactions
      3 Documents appear once
      4 Status in the PM10000 is 20 and in the
      PM00400 it is 1

      Hope that you can assist.

      Kind Regards
      Colin

      Like this

      • Colin,

        If you bring these up in SmartList again, then double click on one of them, that should open the Payable Transaction Entry window and allow you to edit the transaction. Does that work? Or does it also give you that same error?

        -Victoria

        Like this

        • hi Victoria

          same error……….The Payables Transaction Entry window opens but cannot edit the record.

          Thanks

          Colin

          Like this

          • Hi Colin,

            Have you run Check Links on the Purchasing series? If that doesn’t help, you’re probably going to need someone to take a look at your tables, which is really impossible in this type of forum. I would recommend talking to your GP partner or GP Support.

            -Victoria

            Like this

  66. Hey Victoria,
    Quick question, we had a PM batch that got stuck in batch recovery and had to be updated via SQL commands and then processed during a Check Links run. Everything looks to be posted successfully to the GL. The only bizarre issue now is that none of the Document Numbers show up for this batch when doing a Transaction by Document Inquiry. However, if you do a Transaction by Vendor Inquiry and open the Payables Payments Zoom on that transaction, the Document Number is present. Do you know what table the Transaction by Document Inquiry points to?

    Like this

    • Hi Ben,

      That should be pointing to the same exact set of tables as the Payables Transaction Inquiry – Vendor window. Can you reproduce this behavior on a different computer? (Or better yet on the SQL Server if you have the GP application installed there?)

      -Victoria

      Like this

      • Victoria,
        Yes, I was able to reproduce the issue on the SQL server with GP10 as well. We are a small shop with 80 GP users, so we only use one terminal server for all users to remote into (30 concurrent). Both servers are experiencing these symptoms.

        Like this

        • Ben,

          Sorry, not sure what to tell you, as I have not run into this before. If I was troubleshooting this, I would start by looking in the tables to see what’s there…and proceed depending on what I found. But without seeing this, it’s pretty difficult to troubleshoot. Have you tried getting help from your GP Partner or GP Support?

          -Victoria

          Like this

          • Victoria,
            I haven’t gone to my partner or GP support yet, thought I would try the cheaper and more intelligent route first. Thanks for the assistance, and always helpful site of yours. I’ll see what GP support has to say.

            Like this

  67. Hi Victoria
    is there anyway to know which user created a vendor or customer?
    Thanks a lot

    Like this

    • FAC,

      As far as I can see, GP does not store this information.

      -Victoria

      Like this

    • I believe Victoria is correct – GP doesn’t store this info natively. However it is possible to store it if necessary. My preferred method is to use a database trigger. For example, if you want to capture the user who creates a customer record, you can have your DBA create a trigger on the customer master table (rm00101) that will insert the new customer number and creating user into a custom table that stores the customer number and creating user.

      While there are ways to store that information in GP, all that I know of require that the user enter more data. The trigger method is completely transparent to the user, and requires no extra data entry.

      Like this

  68. how is REMSUBTO or OREMSUBT computed in table POP10100?

    Like this

    • Gaganiaro,

      According to the GP 2010 SDK:

      Remaining Subtotal (REMSUBTO) is “the sum of the originating remaining line item amounts in the related POP_POLine records converted to functional. Originating remaining line item amounts are calculated as follows: Quantity Remaining to Invoice * Originating Unit Cost. Quantity Remaining to Invoice = Quantity Ordered – Quantity Canceled – Posted to Date Invoiced – Unposted Invoiced + Posted Quantity Replaced – Quantity Invoice Adjustment”

      And Originating Remaining Subtotal (OREMSUBT) is “the sum of the remaining line item amounts in the related POP_POLine records. Remaining line item amounts are calculated as follows: Quantity Remaining to Invoice * Originating Unit Cost. Quantity Remaining to Invoice = Quantity Ordered – Quantity Canceled – Posted to Date Invoiced – Unposted Invoiced + Posted Quantity Replaced – Quantity Invoice Adjustment.”

      -Victoria

      Like this

      • thanks but i have a follow up question. in table POP10110, there is a field called OPOSTSUB which is i think the difference of REMSUBTO and SUBTOTAL in table POP10100. how is OPOSTSUB computed?

        by the way, where did you get that GP SDK?

        a lot of thanks, Victoria!

        Like this

        • Gaganiaro,

          The SDK can be installed from the GP media, but it is a separate installation. Typically it is under the Tools folder.

          Originating Posted Subtotal (OPOSTSUB) is defined as “amount posted to this line from receivings”.

          -Victoria

          Like this

          • in PM20000, there is a column called CURTRXAM which i believe is the remaining amount(unpaid). how is this computed? Also, is it enough to be the basis if I was instructed to get all the PO balances? Thanks a lot!

            Like this

            • Gaganiaro,

              Every time a transaction is entered that would change the amount still due on a payables transaction, the Current Transaction Amount (CURTRXAM) is changed. I would start by asking for some better specifications. I can think of a few different definitions for a ‘PO balance’, but why guess?

              -Victoria

              Like this

  69. Hi Vic,
    How can you activate the print option when you are doing cash payments.The moment you select payment method (Cash),the print button is greyed out,inactivated. How can it be activated and print payment vouchers.
    Trx>Fin>Bnk Mnget>Batches…..payment entry.

    Your input will b highly appreciated.
    Charles

    Like this

  70. Victoria,
    I’ve been using your blog as a reference for over a year now and I’m so grateful that you take the time to help so many people.
    I am currently at our Mexico operation and, for government compliance, I need to pull a list of checks that were cashed in a given time period along with the the taxes associated with them. We’ve been digging through the data and have determined the following:
    –We are able to associate the appropriate PM tables to find those invoices, returns, and credit memos that are interrelated. We are using tables PM30200 and PM30300. This brings with it the tax information as well as document totals.
    –We know that we want to join to the Bank Reconciliation system to determine whether the check has been cashed. The table with the most promise is CM20200.
    Now for our problem. We can’t reliably associate information in CM20200 with PM30300 or PM30200. We thought that CMLinkID linked with VENDORID and CMTrxNum linked with APFRDCNM, but we find that this doesn’t always work. Can you give us a recommendation on how to correctly link correctly from the CM to PM modules?

    Like this

    • Hi Matthew,

      You should be able to link PM30200 to CM20200 using something like this:

      SELECT PM30200.*, CM20200.*
      FROM PM30200
      INNER JOIN CM20200 ON
      PM30200.CHEKBKID = CM20200.CHEKBKID
      AND PM30200.DOCNUMBR = CM20200.CMTrxNum
      AND PM30200.DOCTYPE = 6 -- payments
      AND PM30200.PYENTTYP = 0 -- checks
      AND CM20200.CMTrxType = 3 -- checks

      You may need to add additional exclusions for voids, alignment forms and date ranges, but hopefully this gets you close.

      One other consideration – CM20200 stores the date that the bank reconciliation date as the ‘cleared’ or cashed date. This may be very different from when the check was actually cashed by your bank. Unless you are recording actual cleared dates in GP (which is unusual), or are performing a daily bank rec, you may not have this data to report on.

      -Victoria

      Like this

  71. Hi Vic,
    We have a problem in vendor balance (GP10 – SP3)..
    The balance from inside GP looks incorrect as it appears in smartlist and HATB less than the actual amount that appear from outside GP like (Vendor Statement) using crystal report.
    This difference appears in only one vendor balance while all other vendor balances are correct in the both sides!
    e.g: We have ten vendors (A,B,…..,J)
    All vendor balance appear correct anywhere you try to inquiry..but vendor C Balance appears incorrect from inside GP- correct from crystal report.. we discovered that by manual calcuations for vendor trx.
    Note that:
    - the dif. amount is typicaly equal to document amount of some manual payments trx for the vendor.
    - all those manual payment are posted and fully applied
    - check links, reconcile … already done for AP trx and vendors.

    What do you think it causes this problem? help plz!

    Like this

    • Hi Yassir,

      If the HATB is also reporting an incorrect balance and the difference is the amount of a manual payment, then, without actually looking at it, I would guess that manual payment is in the system twice somewhere or did not update the GP tables properly when it was being posted. You said you ran Check Links and Reconcile and it did not help, so at this point, the fix would need to be performed directly in the SQL tables. This is not something I would ever advise doing without the proper knowledge and experience, so I would recommend getting help on this from your GP Partner or GP Support.

      As to the question of what can cause this, unfortunately, it’s virtually impossible to answer this question unless you can reproduce the problem again. It could have been a connectivity issue between a user computer and the SQL Server, it could have been a manual change or import into the SQL database, etc. I have seen similar issues occur when a user crashes out of GP in the middle of entering a transaction.

      -Victoria

      Like this

  72. Hi Victoria,
    We have used ‘Void Open Payables Transactions’ to void a transaction posted from Inventory Receiving’s Transaction Entry. i cannot see the impact of the voided transaction on the Stock of the Inventory Item(s) .

    Can you tell me how and where can the stock of the item be reverted.

    Wasay

    Like this

  73. Hi again,

    I have a payment voucher number printed on the EFT Payment Register report….I need to link this back to the original invoice…how can I do this?

    Thanks
    Nev Browitt

    Like this

    • Nev,

      Just to clarify – every payables transaction in GP has a ‘voucher’ number. Are you looking to link the payment ‘voucher’ number to the payment itself? Or to the invoice this payment was applied to? It sounds like you’re looking for the latter. If so, my Payment Apply Detail code should help. If that is not your question can you please provide more detail?

      -Victoria

      Like this

  74. Hi all – GP9 SQL 2005

    Is there a way to retroactively report on the actual address line detail used on a cheque (not by reference to the master record as this may have changed)? We have some issues internally and I have been asked to show addresses used on previous cheques.

    Thanks for any and all contributions

    Stu

    Like this

    • Stu,

      Unfortunately, I do not believe this information is stored in GP. This is one of the reasons it is not recommended to change existing addresses, but to instead add new ones. I know this does not help you for historical reporting purposes, but maybe you can teach users to do this correctly for the future.

      -Victoria

      Like this

  75. Hi Victoria,

    When I post a Shipment/Invoice from Receivings Transaction entry window. The XCHGRATE,RATETPID,EXGTBLID columns in MC020103 are not updated. I have performed the checklinks maintenance and also cheked the rate types in which only one rate type is selected. FYI when I make a transaction from Payable Transaction Entry everything works fine. Can you please help me to ovecome this problem?

    Like this

  76. Hi Victoria,

    Your site is my new favorite gp resource!

    If you have any thoughts on this I would really appreciate it!

    We are trying to default the checkbox “Take Non-Qualifying Disounts” in Trans/Purchasing/Select Checks window without having to use Extender.

    Do you know of any why to accomplish this in the setup/tables?

    Thanks,

    Eric

    Like this

    • Hi Eric,

      Thanks for your kind words!

      I am not sure how you would default that checkbox with Extender, did you mean Modifier? The other option I can think of is a Dexterity customization. There is no way to control this checkbox in setup or directly in the tables.

      -Victoria

      Like this

  77. Hi Victoria,

    What table do I need to look in to see which account my PM Distributions have gone to?….I have the Distributions table PM10101 now I want to do a query that links the distributions to there respective distribution accounts.

    Thanks in advance
    Nev

    Like this

  78. Hi Victoria
    please could you help me with this, i hope u understand me, (my GP is in spanish)

    I’ve got a PM Transaction and it was fully applied with a payment but in the apply to Document window they enter a quantity in the write off field (we use that for tax purposes)
    that’s all ok the pm transaction was fully applied, the payment was fully applied either.
    Then they need to Void de PM transaction, so they have to Void the payment first, they voided the payment but when you look at the PM transaction there is still a current amount pending (the amount that they entered in the write off amount when applying in the first time)
    So i don’t know if there was a problem voiding the Payment and thats why the current amount does not match the Doc Amount or we have to do something else so the pm transactions is free
    I did checklinks and reconcile but i see PM20000 table and that PM transactions still has the write off amount in the WROFAMNT field
    what should i do, an update in the db?

    Thanks a lot for all your help and please pardon my english

    Like this

    • FAC,

      The write off on the transaction should have been ‘reversed’ when the transaction was voided, so it sounds like something may have gone wrong here. Normally I would recommend Reconcile and Check Links, but you have already tried that. I do not feel comfortable recommending changing something directly in the database without seeing the data myself. I would recommend talking to GP support or your GP Partner to see if they can help fix this.

      -Victoria

      Like this

  79. Hi victoria,

    I would like to know how to calculate cash in bank report , in which we would like to know the cleared and uncleared balances.

    Regards,
    Sruthi

    Like this

  80. Hi Victoria,

    Can you please tell me the query for vendor aging report .

    Thanks
    Sruthi

    Like this

  81. Trial Balance Report:
    We have a few transactions that are showing on our trial balance report that are posted in 2001. These are payments showing up, but why are we seeing these if they were already posted back in 2001? How can I fix it to where they don’t show up on the trial balance report? Any help would be appreciated. Thank you

    Like this

    • VC,

      There are numerous trial balance reports and each has multiple parameters. Without knowing what report you’re talking about and what parameters you are choosing, it’s very difficult to comment. Also, since these are such old transactions, I am wondering why this is coming up now? Was this same report fine before, but is not now? Were these transactions imported in or entered manually?

      -Victoria

      Like this

  82. Hi,
    I was looking at POP DIstribution file as well as PM Distribution file.

    I find that there are distribution types which are not shown in the respective distribtion type in your site. Example are like:
    Payables Management – I can see DISTTYPE like 16, 17 and 18 (from PM30600)

    Purchase Order Processing – I can see DISTYPE like 201 and 209 (from POP30390)

    Do you know what these distribution types represents?

    Also, I always find 0.01 amount generated in GL batches further to posting Foreign Currency Purchasing Invoice batches. I think this may be caused by rounding between the detail line and payables amount. As I did not setup any account in the Posting Account, the batch is stranded in GL. I want to know which setup I need to enter – Rounding Difference or Rounding Writeoff. Is this the right place for me to put in the account so that the system will automatically use this account and post the GL batch?

    Any advice is greatly appreciated.

    May

    Like this

    • May,

      I do not show these distribution type numbers in any of the documentation I have, nor in any test data I have looked at. Unfortunately, I have found that sometimes not everything is documented, so the information I have posted on this blog is a combination of what I have found in the documentation plus data I have discovered through my own experience.

      If you are manually entering there transactions and there are no customizations in place, these must be generated by GP – if you need to find out what they are, you can submit a support request to Microsoft or try asking on one of the GP forums.

      For your rounding differences and writeoffs – if this is multicurrency, you can set up the accounts to be used for all transactions within a company by going to GP > Tools > Setup > Posting > Posting Account and selecting Financial for the Series. You also have the option of setting up different accounts for each currency and rate type by going to GP > Tools > Setup > Financial > Rate Types > Accounts button.

      -Victoria

      Like this

  83. hi victoria,

    In few cases the goods recieved are greater than purchased items. Are you aware where to block such reciepts in GP?

    Regards,
    Nisha.

    Like this

    • Nisha,

      Are you saying that you would like to prevent users from being able to over- receive in the POP module? If so, I do not believe this is possible out-of-the-box in GP.

      -Victoria

      Like this

      • Yes Victoria. Are there any existing GP reports which prints the over recieved items?

        Regards,
        Nisha

        Like this

        • Nisha,

          I don’t think there is a stock report. If you’re creating one yourself, you would need to get the PO information from a union of POP10110 (Purchase Order Line Work) and POP30110 (Purchase Order Line History) and then compare it to the receipts (I think the POP10500 table is the best for this).

          -Victoria

          Like this

  84. Hi Victoria,

    Can you please give me an example of how to do the coding to drill down to GP from excel or ssrs.

    Regards,
    Sri.

    Like this

  85. Hi Victoria,

    I want to create a report to publish in sharepoint by passing the parameters . And the user wants to drilldown to the GP screen .

    Should we create the reports in excel and publish in sharepoint or use ssrs . But would like to know how to drilldown to GP

    Regards,
    Sri

    Like this

  86. Hi Victoria,

    Are you familiar with RTV Credits? In my testing, it appears that if you create a RTV Credit and post it in the RTV Shipping Window, an associated Payable (Credit Voucher) is created and linked to the RTV. I have an RTV without an associated Credit Voucher and it appears to be related to the Item.

    Any thoughts or suggestion would be greatly appareciated.

    Thanks.

    Brent

    Like this

    • Sorry Brent,

      Not one of the modules I work with all the time, so I think you might be better off posting this question on one of the GP newsgroups where you’d have a wider audience.

      -Victoria

      Like this

  87. hi,

    i want to modify manual payments posting journal report. I modified it but not able to see the changes in the print.
    can someone tell me how to go about this?

    Thanks.

    Like this

    • Hi Nish,

      You will need to grant users (including yourself) access to the newly modified report. Please review the setup manual for your version of Dynamics GP to determine how to do this.

      -Victoria

      Like this

      • hi victoria,

        I have given access to the users and myself to view the modified reports.. im using gp10.

        IS it to do with modifying reports after posting and before printing?
        Did you ever try to modify posting journal reports?

        Thanks.

        Like this

        • Nish,

          Not sure what you mean by “modifying reports after posting and before printing”? There are typically 2 very similar reports – an edit list and a posting journal. Perhaps you are not modifying the one you mean to? Or need to modify both?

          Once you’ve modified a report in GP 10, go to GP > Tools > Setup > System > Alternate/Modified Forms and Reports and change either the DEFAULT USER profile or whatever profile your users are assigned to so that they are pointing to the modified version of the report.

          -Victoria

          Like this

  88. Hi Victoria,

    I hope you can help me on this. Im having trouble on voiding open payable transaction. As far as I know this would only cause when I partially apply payment to the invoice or its on hold. But I already check it and im sure that there is no payment applied on it or its not even on hold. I also process the checklinks for this but still same error. Im not sure if there’s a hang table affected by this.

    Hoping for your response. Thanks in advance

    Tin

    Like this

    • Tin,

      Are you getting an error message when trying to void? If so, can you please post the exact wording of the error message?

      -Victoria

      Like this

      • Hi Victoria,

        Thanks for the reply. Yes, the error says: this document cannot be marked for voiding. it has been either partially applied or is on hold. But as what I said before neither the voucher number was in hold of had a partial payment.

        Thanks.

        Tin

        Like this

        • Tin,

          If there really is nothing applied to this document, you can try running a Reconcile on the payables transactions (Tools > Utilities > Purchasing > Reconcile) to see if that clears up the issue. If it does not, you may need to get some help correcting the data directly in the tables. This is not something that should be done without a good understanding of the tables involved and the implications.

          -Victoria

          Like this

          • Hi Victoria,

            I did as what you told, running a Reconcile, but the error is still the same. On document inquiry, under Unapplied Amount this field is already 0 (zero), but when i zoom on applied payments there were no details on it. Hope you can still extend your help on ths. Many Thanks!

            Tin

            Like this

            • Hi Tin,

              At this point, I would recommend getting some help either from your GP Partner or Dynamics GP Support. This is probably not going to be something that can be fixed ‘generically’ – someone will have to look at the actual data in your tables to determine what went wrong and recommend the best fix.

              -Victoria

              Like this

          • Hii my name is Luis Cevallos I am from Guayaquil-Ecuador I have troubles when I open the window Alternate/Modified Forms and Reports and select whatever user a message saying “This ID is being edited by another user.” I never see the windows I modified in modifier please I try many things like restore database, delete users from Dynamics GP see the log and execute step by step de stored procedures please help me.

            Thanks
            Have a nice day.

            Like this

            • Luis,

              I would recommend getting all users out of GP and running the following in SQL Server Management Studio:

              Delete DYNAMICS..SY00800
              Delete DYNAMICS..SY00801
              Delete DYNAMICS..ACTIVITY
              Delete TEMPDB..DEX_LOCK
              Delete TEMPDB..DEX_SESSION

              -Victoria

              Like this

  89. Hi Victoria

    I got a purchase invoice in 2009 and it has 2 payments 50% in 2009 and the other 50% in 2010, they need to now the pending amount of 2009 (like the PM balance historical report), if I check PM30200 the CURTRXAM field is 0. Is there a way to now the current transaction amount but just for 2009? is there a table where stores that kind of data (current amount after echa payment) or would I have to calculate it by the payments dates and amounts in PM30300?

    I hope u understand me (english is not my native :( )

    Thanks a lot you are the best!

    Like this

    • FAC,

      You will not find the values you are looking stored in any table. This is what the Historical Aged Trial Balance report in GP accomplishes and why it is so difficult to duplicate it on your own. To be able to calculate the balance as of a particular date, you would have to look at all transactions and their dates and come up with logic to take into account partial payments, voids, documents that may be applied on different dates, etc.

      -Victoria

      Like this

      • Hi Victoria

        I dont know if u could help me check this but i compare several transactions and they are just like GP

        What I do is: create a table where i join the transactions from PM20000 and PM30200
        VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, DOCAMNT and a CURTRXAM field equal to DOCAMNT

        then a cursor of payments of the desired range of dates from PM30300 update the CURTRXAM of the above table minus APPLDAMT of PM30300

        checking GP the CURTRXAM didnt match

        Checking the table PM30300 I got two fields where i have some data, WROFAMNT and RLGANLOS.

        Checking the transactions i realized that i have to do this:
        CURTRXAM = CURTRXAM – APPLDAMT – WROFAMNT in the purchase invoice
        and
        CURTRXAM = CURTRXAM – APPLDAMT + RLGANLOS in the payment

        and with that that matches with GP
        but i just cant figure it out how those two fields work

        Thanks a lot

        Like this

        • FAC,

          What exactly are you trying to achieve with all this? It may help to start from that direction, because I am not quite sure why you are looking at the data you are looking at. For example – PM30200 only holds historical transactions, meaning they are fully paid. If you’re looking for open amounts on transactions, that table should not be needed. Also, for apply information, don’t forget the PM10200 table which hold work and open apply information.

          -Victoria

          Like this

          • Hi Victoria Thanks again for replying so fast

            well they asked me to do a report of all current amount of PM transactions in year 2009

            just like Historical Aged Trial Balance report but in excel

            thats why i was trying to do all that in my previous post

            Thanks a lot Victoria

            Like this

            • FAC,

              If the Historical Aged Trial Balance is what you’re after, that is a tremendous amount of work, and not something I have code to publish.

              One option may be to modify the Historical Aged Trial Balance in Report Writer so that it is easier to export into Excel.

              -Victoria

              Like this

  90. Hi Victoria,

    What table in PM lists the “Void GL Posting Date”? What’s the field name? Is there a sql view that has been created to convert PM amounts to negative or positve values so that a person can tie the transaction to the GL. I am having a problem with the voided transactions and the periods they were voided in. For example if I query all payables transaction for a fiscal period using the posting date, the result will not give me the transactons that were posted in prior periods that have been voided (void GL posting date) in the fiscal period that I am querying for.

    You’re queries rock.

    Thanks in advance for any help.

    Doug

    Like this

    • Hi Doug,

      Thanks for the kind words!

      The Void GL Posting Date will be in the VOIDPDATE column in PM30200. One important thing to note – if you void a payables check before it is posted, this column will have a date of 1/1/1900, since it was never posted to begin with and nothing gets posted to the GL due to the void. If you’re looking for the Void Document Date, that will be populated into the DINVPDOF column for voided transactions.

      You can use something like this as a start to matching your payables to the GL:
      SQL view for all posted Payables transactions in Dynamics GP

      -Victoria

      Like this

  91. Hi Victoria,
    where can i find the vendor bank wire information? Thanks for you help.

    Aaron

    Like this

    • Aaron,

      Are you asking where to find it in the tables? GP does not really have a place for vendor bank wire information. If you have the EFT for payables module, then you may be storing it there. If not, you would need to find out from your users how and where they are entering this information.

      -Victoria

      Like this

      • Victoria,
        Thanks for your quick reply. I am new to GP and I don’t know if we have EFT for payables module.

        Thanks again for your help and your website.

        -Aaron

        Like this

  92. While in Dynamics GP, If I go to Purchasing\Inquiry\Transaction By Vendor, select a Vendor ID of a vendor I’ve imported transactions for, there are no transactions displayed.

    However, if I go to Purchasing\Inquiry\Transaction By Document, change the document to lookup by Vendor ID, select the same vendor I’ve imported transactions for, the transactions appear in the list.

    I also have an issue with these same transactions not being able to be opened via SmartList\Purchasing\Payables Transactions. I receive the error: A get/change operation on table ‘Batch_Headers’ could not find a record

    Is this normal behavior for Dynamics? Any help is greatly appreciated!

    Like this

    • Jerri,

      None of what you are describing is normal behavior in GP. The first question I would have is how exactly you are importing your transactions. Also, what version of GP are you on?

      -Victoria

      Like this

      • Thanks for replying Victoria! I didn’t think that was normal :-) I kept reviewing the data and found that if the batch source doesn’t match across all the SQL entries, I would get errors. Once I made them all the same, everything is working fine.

        Again, thanks for all of your help and your website, it’s been a huge time saver for me!

        Jerri

        Like this

  93. I’m inserting distributions into the PM10100 table via the stored procedure zDP_PM10100SI. However, when I pull that transaction up, and go to the Distributions window, none of the distributions are listed. Is there another table I need to insert data into for my distributions to show in this window?

    Like this

  94. Hi Victoria
    thanks for all of your replies
    You are very helpfull

    In the company I work there is a policy that they start calculating the due date of a purchase when the vendor brings their purchase invoice not the date in that invoice.
    i mean a purchase is made in September the first due date of payment September the 30th , but the vendor bring their purchase invoice on September the 15th so my company wants that the due date starts counting on that date (September 15th) so the new due date would be October 15th.

    Is there a way to update de DueDate field in PM20000 to do this or you have to update another tables as well.

    I hope I made myself clear, pardon my english

    Thanks in advance

    Like this

    • Fernando,

      In my experience most businesses will not enter an invoice until the goods have been received. Are you saying you are entering an invoice first, then receiving the goods, then want to go and change the due date of the invoice? If so, you can change the due date by going to Transactions > Purchasing > Edit Transaction Information. Bring up the invoice there and change the due date. You could also change the due date directly in PM20000, if you prefer. I personally prefer not to make ongoing changes to the database if there is another option.

      -Victoria

      Like this

  95. Hi Victoria,

    Good morning.

    While on the PM00200 can you add the following to the list:

    select MINPYTYP, MINPYPCT, MINPYDLR, * from pm00200

    – 0 = No Minimun radio button
    – 1 = Percent radio button. Percent value is in the MINPYPCT
    – 2 = Amount radio button. Amount value is in the MINPYDLR

    select MXIAFVND, MAXINDLR, * from pm00200

    – 0 = No Maximum radio button
    – 1 = Amount radio button. Amount value is in the MAXINDLR

    select WRITEOFF, MXWOFAMT, * from pm00200

    – 0 = No Writeoff radio button
    – 1 = Unlimited radio button
    – 2 = Maximum radio button. Amount value is in the MXWOFAMT

    Cheers.

    Regards,

    Eric

    Like this

    • Hi Victoria,

      My apologies for not clarifying the above scripts. As I clicked submit a little too early. :)

      All the scripts above are from the Creditor/Vendor Maintenance Options window (Cards >> Purchasing >> Creditor/Vendor >> Options button)

      The first one is for the Minimum Payment line.
      The second is for the Maximum Invoice Amt line.
      And the thrid for the Writeoffs line.

      Thanks.

      Regards,

      Eric

      The first one is for the Minimum Payment sec

      Like this

  96. Hi Victoria,

    In my company we would like to integrate purchase returns transaction via eConnect.
    But I cannot find any node for purchase (POP) returns transaction, nodes for this is available, if yes what is the name of it.

    Thanks in advance.

    Shabnam

    Like this

  97. Really nice resource list Victoria, thanks for posting…

    Like this

  98. Thanks Victoria,

    You are always helpful!

    Like this

  99. Hi, Victoria,

    My AP GL trial Balance does not tie to my AP aging balance. What type of entries could result the problem? How to solve it in general?

    Thanks in advance.

    Like this

  100. Hi Victoria,

    I have an credit amount of $149.31 from 2005 that I need to clear from my aging report. This credit is the result of a CM in the amount of 628.30 applied to two invoices for 243.30 and 235.69. The 628.30 CM and 243.30 invoice show as open items while the 235.69 invoice is posted to history. When I try to correct the open items, I get the message that the items are partially applied. I tried to generate a dummy invoice but received the message that I couldn’t apply that invoice until the others had been posted. I have no outstanding batches to post. How do I get this corrected now?

    Thanks,
    JoAnn

    Like this

    • JoAnn,

      Have you tried entering and posting an invoice for $149.31, then going to Apply Payables Documents and applying the remaining amount of the credit memo to this new invoice? I am not quite sure what specifically you tried when you say you ‘try to correct the open items’, do you mean you tried to void them?

      -Victoria

      Like this

      • I did try entering an invoice for the $149.31 and received a message that I could not apply this invoice until the other open invoice was posted. The open invoice could not be posted because there was nothing to post it against.

        Yes, by correcting I did mean I tried to void them.

        JoAnn

        Like this

        • JoAnn,

          Not being able to void is correct – you cannot void partially applied transactions in Payables.

          The rest of this doesn’t sounds right to me, though, and I am wondering if you are using the words ‘post’ and ‘apply’ interchangeably, while I am taking them to mean very different actions in GP. Can you please give me more detail? What is the name of the screen that you are on in GP, what specifically are you doing/clicking and what is the exact text of the error message?

          -Victoria

          Like this

  101. Thank you very much Victoria for your prompt response! That’s very detailed and helpful!

    Like this

  102. Hello, Victoria,

    I am trying to link GL 30000 with PM 30600 and PM 30200 but could not find related field. Thank you very much for your input.

    Lily

    Like this

    • Hi Lily,

      PM30200 and PM30600 will link on PM30200.DOCTYPE = PM30600.DOCTYPE AND PM30200.VCHRNMBR = PM30600.VCHRNMBR. That’s pretty straightforward.

      For GL30000, you could try linking on PM30600.DOCTYPE = GL30000.ORTRXTYP AND PM30600.VCHRNMBR = GL30000.ORCTRNUM AND PM30600.DSTSQNUM = GL30000.OrigSeqNum. However, I am not sure this will be everything you want. For example, there might very well be entries in PM30600 that are in GL20000, not in GL30000. Also, depending on how you’re posting to the GL, you might not be able to link from PM to GL in detail and may need to do it in summary, by GL30000.ORTRXSRC = PM30600.TRXSORCE.

      -Victoria

      Like this

  103. Victoria,

    I have one receivable batch with over 600 trx but can not post b/c I am getting error message ‘This batch is being edited by other user’ No one is in it.

    can you please help?

    Thank you,
    -Jim

    Like this

  104. Hello,

    I am trying to make a custom aged payables report – how are the transactions in PM20000 related to the accounts listed in GL00100?

    Thanks for any help,
    Kyle

    Like this

  105. Victoria,

    Do you know the table where the GL transactions would be? I looked at PM30600 but it doesn’t tell me the GL code. Is there a similar table for unposted trans?

    Thanks!

    Like this

    • Sherry,

      There are 2 tables that hold the GL distributions for payables transactions:
      PM10100 – GL Distributions for Work (unposted) and Open (posted but not paid) Transactions
      PM30600 – GL Distributions for Historical (posted and paid) Transactions

      These tables do not store the GL account numbers, only the account index (DSTINDX). To get the full account number, I typically link to the GL00105 table, since that holds the concatenated account number.

      -Victoria

      Like this

  106. Victoria,

    Thank you very much…….
    :-)

    -Jim

    Like this

  107. Hi Victoria,
    I have question. I have one sales batch that is been edited by someone but not one is log in to the system in SOP Table. SOP10100

    I am trying to post the batch but I am getting error like ‘The batch is been Edited by some other user’ How do i get rid of the batch….

    Can I still post this batch? If not, how do I delete the batch from SOP SOP10100?

    Thank you,
    -Jim

    Like this

  108. Thank you very much…..

    -Jim

    Like this

  109. Victoria,

    Thank you very much for you input…. I was just wondering how about pm40101 because that has all the buckets and when I go to GP Front end via Inq—Purchasing—Vendor Credit it give us the aging that is what I am looking to find.

    I will really appriciate if there is something like that I can find from back ent.

    Again,

    Thank you,
    -Jim

    Like this

    • Jim,

      PM40101 simply holds the ‘setup’ of what your payables aging buckets are. If you need this for reporting you would need to create your own view or stored procedure that calculates the totals in each aging bucket for each vendor.

      -Victoria

      Like this

  110. Hello,
    can someone tell me please how do I find the aging table in payable like we have rm00103 for Receivable?

    Thank you,
    -Jim

    Like this

  111. Victoria,

    Initially I also searched in SDK, but I didn’t get any information about PM Apply Tables(Why PM Apply Tables are not included in SDK?).
    Then only I approached you..

    Thank you for your response…………..

    Like this

    • Jegan,

      Unfortunately, the SDK is missing large chunks of information…sometimes a whole module, or, as you have found, a section of tables, will just not be in there. I have also found that some of the information is not exactly up to date. Anything that I find, whether through my own research or through the help of others, I plan to post up on this blog so that we can all benefit from it.

      -Victoria

      Like this

  112. Hi,

    I need some information about the field OAPYTORNDAMT(Originating Apply To Round Amount) of table PM10200 & PM20100 .
    I think that it’s value is depends upon the Muticurrency apply process.
    What are the calculations involved in this field value.

    Thanks
    Jegan

    Like this

    • Jegan,

      Unfortunately, I do not see any details on this field in the SDK. In these situations, I would go to GP Support and ask them for whatever information I am looking for. Sorry not to be of more help.

      -Victoria

      Like this

  113. Bab,

    The PM30200 table hold the actual historical transactions. The PM30300 table holds apply information about historical transactions. Hope that helps to clarify.

    -Victoria

    Like this

  114. I am sorry, I mean PM30300.

    Like this

  115. what is the different between these two tables?

    PM30200 – Historical/Paid Transactions
    PM30300 – Apply To History

    I thought PM30200 table holds Paid transactions as well. Am I wrong?

    Like this

  116. This will helpful for technical developers of Great Plains

    Thank you very much!!!!!!!!!

    Like this

    • Victoria, what is the difference in the PM00201 and the PM00202 tables? I have several entries that are not matching…Thanks

      Like this

      • Pamela,

        One table is a summary – one line per vendor, and the other is detail – one line per vendor, per year, per period, per period type (fiscal vs. calendar). What exactly are you comparing that’s not matching up?

        -Victoria

        Like this

Trackbacks/Pingbacks

  1. Interesting Findings & Knowledge Sharing » Key Table Information in Microsoft Dynamics GP - January 2, 2009

    [...] PM Tables – Click Here [...]

    Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,358 other followers

%d bloggers like this: