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 used
20 - saved but unposted transaction
30 - transaction that is currently realtime posting
40 - posted transaction
50 - transactions that has encountered an error during posting
60 - transaction that has been saved previously and has a status of 20 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
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.
Ryan,
I believe you can use SmartList for this – under Purchasing > Payables Transactions add a column called Payment Entry Type.
-Victoria
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
Ryan,
Why would the data be incorrect? Are you pushing it directly into the tables? Or entering it incorrectly?
-Victoria
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
Ryan,
If the data is not entered anywhere, how can you report on it?
-Victoria
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!
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
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
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
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
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
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?
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
Hi Vic,
Sorry, I am not a good source for help with Report Writer, as I much prefer to work with Crystal and SQL for reporting and that is where my expertise is. I would recommend posting questions about Report Writer on the GP Community Forum.
-Victoria
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
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
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.
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?
Hi Ron,
GP does not store the remit to address with the payment, so you would need to examine the invoices and their remit to addresses. You could probably start with my SQL view for Payables payment apply detail in GP and add the invoice remit to address to that.
-Victoria
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…
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
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,
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
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.
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.
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
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.
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
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
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
Jim,
That is simply a sequence number that is typically part of what makes the line unique. It is also sometimes used for sorting. Before you are tempted to simply use 1,2,3, etc. for these, you might want to read this blog post: http://dynamicsgpblogster.blogspot.com/2008/07/scrolling-windows-and-line-sequence.html.
-Victoria
Victoria,
Everything is now golden and I’m wrapping up my project. Thank you again for your irreplaceable assistance. And thank you for this site which was an amazing resource for a month prior to directly asking questions.
If you’re ever in Phoenix, lunch is on me!
Jim
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
Myrna,
Does this blog post help? http://victoriayudin.com/2012/10/11/voiding-a-partially-applied-payables-transaction-in-dynamics-gp/
-Victoria
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?
I managed to get it solved. I had to reset the record in SY00500 and it worked.
Billie Dee,
Thanks for the update, glad you got it resolved.
-Victoria
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,
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
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?
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
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.
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
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.
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
Victoria,
It shows unposted, but does not appear in a batch available to post.
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:
Once that is done, run Check Links on the Sales Series.
-Victoria
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.
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?
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
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.
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.
Hi Zafar,
That is in the SY01200 table. The field is INETINFO.
-Victoria
Hi Victoria,
Thank you so much. You are the best.
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!
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
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?
Bill,
That is also in the PM20000 table in the PYMTRMID field. Is that what you’re looking for?
-Victoria
I do believe this is it! Thanks a bunch!
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
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
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?
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
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
DOCTYPE 7 is a payables scheduled payment. (Transactions…Payables…Scheduled Payments)
Bill
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
Rhon,
PM30600 holds distributions for historical transactions. PM10100 holds distributions for work and open transactions.
-Victoria
thank you so much victoria.
Your site has been immensely helpful for me and I wanted to thank you for that. I’ve also just purchased your book!
Hi Bill,
Thank you so much!
-Victoria
Victoria,
Thank you so much. This completely makes sense. now.
Cheers ~ Don
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
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
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.
Hi Ben,
Siva Venkataraman has a number of HATB scripts posted on his blog, here is one for the Payables HATB.
-Victoria
Quite possibly the best news I’ve received all week. THANK YOU, Victoria!
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!
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
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!
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
Wise advice, I thank you for your support. We will do as you indicated.
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!
Ash,
Unfortunately, you would lose everything you have already entered (correctly) since Jan 1, as there is no way to ‘selectively’ clear data. It’s pretty much all or nothing. Again, this is not an approach I would recommend.
-Victoria
Much obliged..
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.
Sorry “Date Invoice Paid Off” (DINVPDOF) is in PM30200 not PM30300.
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
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!
Hi Alec,
Thanks, yes, that does help. For what you have described, you should be looking in RM20101 and RM30101 for the Date the “Date Invoice Paid Off” field – those are the receivables tables. (Receivables tables have customer transactions, Payables, where you were looking, are vendor transactions.) I have a separate page with RM (Receivables Management) tables and also a page with SQL views for Receivables that might be helpful. For linking to SOP tables, SOPNUMBE links to DOCNUMBR and SOPTYPE 3 = RMDTYPAL 1 for invoices. If you’re also including returns in your reporting then SOPTYPE 4 = RMDTYPAL 8.
Hope that helps,
-Victoria
Wow nice! That worked perfectly for me.
Much appreciated Victoria.
Alec,
Excellent – thanks for the follow up. And sorry for misspelling your name before, I should know better than to type before finishing my first cup of coffee.
-Victoria
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!
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
Hi Victoria,
In which table can I find creditor e-mail addresses in GP10?
Khona,
If you are entering this on the Internet Information window, it is stored in the SY01200 table.
-Victoria
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
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
Wow, Okay, I overlooked that. How can I get the description of the box?
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
That is weird. But your suggestion does indeed work. Thanks.
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?
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.TEN99TYPELooks like this was a little coding snafu when the new 1099 stuff was added a couple of versions ago. ;-(
-Victoria
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.
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
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!
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
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.
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
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?
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
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
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:
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
Hi Victoria,
Thanks for your help. It looks like someone was making changes to vendor addresses and that was clearing the shipment method.
Mike
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.
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
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
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
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
Hi Depar,
I would start with running check links on the purchasing series to clear this up. If that does not help, you may need to get some help from your GP Partner or GP Support on this, since it will most likely involve clearing this up directly in the tables.
-Victoria
ok – thank you so much Victoria.
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
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
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…
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
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
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.
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
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
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
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?
Andrew, have you tried running reconcile and check links?
-Victoria
I tried that first and it didn’t help. I ran Reconcile on Sales, Purchasing and Inventory, and checked links on the same modules.
Andrew,
Since none of the standard processes helped, most likely you need to have someone that knows the tables well take a look to see what has gone wrong. This will probably be either your GP partner or GP support.
-Victoria
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
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
Hi Victoria,
Thank you so much.
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?
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
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
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
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.
Joe,
AP transactions are in PM20000 and PM30200. Apply information is in PM10200 and PM30300. My Apply Information view should help – maybe you can just take out the columns you don’t need and use it.
-Victoria
Sweet. I’ll give it a whirl. Thanks for the quick response.
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
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
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
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
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
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
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
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.
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.
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
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
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
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
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
hi Victoria
same error……….The Payables Transaction Entry window opens but cannot edit the record.
Thanks
Colin
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
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?
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
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.
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
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.
Ben,
Thanks for the compliment.
Please let me know how it turns out, I am curious to find out what the issue was.
-Victoria
Hi Victoria
is there anyway to know which user created a vendor or customer?
Thanks a lot
FAC,
As far as I can see, GP does not store this information.
-Victoria
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.
how is REMSUBTO or OREMSUBT computed in table POP10100?
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
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!
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
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!
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
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
Hi Charles,
It sounds like you may be using a non-US version of GP which allows for bank batches? I don’t have experience with this, as the US version of GP does not allow batching of bank transactions. I would recommend posting your question on the GP forum (https://community.dynamics.com/forums/32.aspx), where you can have access to a much broader audience.
-Victoria
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?
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
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!
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
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
Hi Wasay,
GP is behaving correctly and as expected. Unfortunately voiding the Payables invoice is not the right way to correct a transaction that originated as a receipt of inventory. Christina Phillips at Dynamics GP Land has a great post about this called Dance With The One Who Brung ‘Ya – Correcting Transactions in Dynamics GP.
At this point, the way to correct your inventory is to enter an inventory transaction.
-Victoria
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
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
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
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
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?
Sorry Waseem, this is not something I have come across before. I would recommend posting this question on the Dynamics GP customer forum or talking to GP Support if you need an answer right away.
-Victoria
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
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
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
Nev,
GL distributions for work and open payables transactions are in the PM10100 table. You might find this SQL view I posted helpful: GL Distribution for AP Transactions .
-Victoria
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
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
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
Sruthi,
Is this related to your vendor aging report question? If not, please take a look at my Bank Rec tables page: http://victoriayudin.com/gp-reports/bank-rec-tables/.
-Victoria
Hi Victoria,
Can you please tell me the query for vendor aging report .
Thanks
Sruthi
Hi Sruthi,
Can you please be a little more specific? There are a lot of different aging reports. I have some payables code posted on my GP Reports page that may help: http://victoriayudin.com/gp-reports/.
-Victoria
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
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
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
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
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.
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
Yes Victoria. Are there any existing GP reports which prints the over recieved items?
Regards,
Nisha
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
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.
Sri,
I am not a developer, so I do not do this kind of work myself.
-Victoria
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
Hi Sri,
Both Excel and SSRS are viable options for your report, however drilling down to GP would require custom coding.
-Victoria
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
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
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.
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
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.
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
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
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
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
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
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
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
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.
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
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!
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
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
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
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
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
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
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
Thank you for your prompt response. I greatly appreciate your help.
Hi Victoria,
where can i find the vendor bank wire information? Thanks for you help.
Aaron
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
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
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!
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
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
Jerri,
Glad you were able to figure it out! And thanks for the kind words.
-Victoria
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?
Jerri,
I typically do not recommend inserting transactional data directly into tables. To get support for this type of project, the best course of action is to either contact Microsoft GP developer support or post a question on the GP developer newsgroup.
-Victoria
I’m writing a tool using Visual Studio that is calling these stored procedures, I think I found what I needed, zDP_PM00400SI.
Thanks Victoria I love your site and all of your helpful information!
Jerri,
Thanks for the update – glad you found what you needed! And thank you very much for your kind words.
-Victoria
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
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
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
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
Eric,
Thank you very much!
-Victoria
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
Shabnam,
I don’t believe there is an existing one for POP returns. You would need to create your own.
-Victoria
Really nice resource list Victoria, thanks for posting…
Thanks Victoria,
You are always helpful!
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.
Lily,
The full answer to this would probably require a quite lengthy post, as there are many different scenarios and possible reasons that could cause this. Here are a few links that might help you:
Reconcile to GL Easily on the Rose Business Solutions blog
How to reconcile GL to Payables module? on MS GP Newsgroup
A/P subledger does not tie to the G/L on MS GP Newsgroup
-Victoria
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
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
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
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
Thank you very much Victoria for your prompt response! That’s very detailed and helpful!
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
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
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
Jim,
There should be an automated solution for this, take a look at Mariano Gomez’s blog post about automated solutions.
-Victoria
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
Kyle,
PM10100 holds the GL distributions for open payables transactions (those in PM20000). The DSTINDX in PM10100 will link to the ACTINDX in GL00100.
-Victoria
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!
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
Victoria,
Thank you very much…….
-Jim
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
Jim,
My personal 2 cents: if you are not 100% sure, don’t delete anything from tables. That said, here is a post by Doug Pitcher that may help for your question: http://rbsgp.blogspot.com/2009/01/stuck-batches-in-dynamics-gp.html.
-Victoria
Thank you very much…..
-Jim
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
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
Hello,
can someone tell me please how do I find the aging table in payable like we have rm00103 for Receivable?
Thank you,
-Jim
Jim,
There is no equivalent to the RM00103 table for Payables. There is also no Aging routine for Payables, which is what updates the RM00103 table in Receivables.
-Victoria
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…………..
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
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
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
Bab,
The PM30200 table hold the actual historical transactions. The PM30300 table holds apply information about historical transactions. Hope that helps to clarify.
-Victoria
I am sorry, I mean PM30300.
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?
This will helpful for technical developers of Great Plains
Thank you very much!!!!!!!!!
Victoria, what is the difference in the PM00201 and the PM00202 tables? I have several entries that are not matching…Thanks
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