Commonly Used Tables:
PM00100 – Vendor Classes
PM00200 – Vendor Master
PM00201 – Vendor Master Summary
PM00202 – Vendor Period Summary
PM00203 – Vendor Accounts
PM00204 – 1099 Period Detail (only in GP 10.0 and higher)
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
PM80100 – Batch Headers (includes approval information)
MC020103 – Multicurrency Payables Transactions
SY06000 – Vendor EFT Information
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
7 – Scheduled Payment (thanks to Dennis Sisnarine for this)
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 – Dividend
3 – Interest
4 – Miscellaneous
5 – Withholding
PSTGSTUS (Posting Status) in PM10000:
10 – transaction being entered for the first time before it has been saved by the user
20 – saved and unposted transaction
30 – transaction that is currently posting (realtime)
40 – posted transaction
50 – transaction that has encountered an error during posting
60 – transaction that has been saved previously and had a status of 20 and is now being edited by a user
70 – transaction that has been posted previously in a recurring batch that still contains unposted transactions is now being edited by the user
PSTGSTUS (Posting Status) in PM10100:
0 – Unposted
1 – Posted
3 – Unposted [used for Computer Checks, not in the SDK]
CNTRLTYP (Control Type):
0 – Voucher types
1 – Payment types
2 – Printed alignment forms
DCSTATUS (Document Status):
0 – Reserved
1 – Work
2 – Open
3 – History
VENDSTTS (Vendor Status):
1 – Active
2 – Inactive
3 – Temporary
CREDTLMT (Credit Limit) in PM00200:
[Thanks to Bud Cool, a frequent contributor to the Microsoft GP Newsgroup, for this information!]
0 – No Credit
1 – Unlimited
2 – Amount
[Note: If CREDTLMT = 2 then CRLMTDLR contains the amount of the credit limit, otherwise CRLMTDLR is zero]
HISTTYPE (History Type):
0 – Calendar
1 – Fiscal
PYENTTYP (Payment Type):
0 – Check
1 – Cash
2 – Credit Card
3 – EFT
Writeoff:
0 – Not Allowed
1 – Unlimited
2 – Maximum Amount
Free On Board:
1 – None
2 – Origin
3 – Destination
—
Last Updated: July 31, 2022
Victoria Yudin 
Hi Victoria, thank you for all your work! I find it very helpful.
Do you know if there is a way to check what time invoices were posted in purchasing module? Date and more importantly time. Thank you
LikeLike
Hi Eugene,
There is a Date Posted (POSTEDDT) that you can see in the PM20000 and PM30200 tables. That will record the system date on when the Post button was clicked for each transaction. However, the time stamp is not tracked for most things in GP, including this.
There is the DEX_ROW_TS field in PM20000 – so right after an invoice is posted you can see the time it was posted (note this is in UTC). But in reality, the DEX_ROW_TS simply shows the last time a row was updated. So if an invoice is updated afterwards or once it’s paid and moved to the PM30200 table, you will lose the ability to see the time the invoice was posted.
Usually this kind of request stems from the need to track employee productivity. If this is important, I would recommend creating custom tracking for this data that would include not only the date and time but also the user in GP that is performing the posting.
-Victoria
LikeLike
Hi Victoria,
We have an instance where a check was cut using a check number that does not correspond to the physical stock check#. The check was presented to the bank and has cleared. Can we change check numbers in GP history records. I do not think it would be a bad idea to leave the GP ck# information as is.
LikeLike
Hi Hiren,
I would not recommend changing the check number in GP – you would have to do that in numerous tables and if it’s not updated properly in all of them, it could cause more issues than it fixes. For record keeping and audit purposes I would add a note to the check in GP saying that it was actually printed and cleared under a different check number. Otherwise, the only thing this impacts is the bank reconciliation for the month it clears in. Just my 2 cents.
-VY
LikeLike
Ok, so I feel like a complete noob… I had to restore the data in PM00200 after an accidental class change rolled forward. Somehow the restore removed the triggers from the table. Is there a simple way to recreate them?
LikeLike
Hi Brian,
If you’re talking about out of the box GP triggers, I would contact Microsoft GP support for help with this, as you may have broken other things that are not so evident by restoring the way you did if the triggers are missing and they would be in the best position to help you check everything.
If you’re talking about custom triggers, they would need to get recreated with the same scripts as originally were used to create them.
-Victoria
LikeLike
Hi Victoria
How can I see if check has cleared or outstanding in accounts payable without going thru check register? On the Vendor transaction on payment is there a feild where I could see it or a report using the check #.
Thank you for your help
LikeLike
Hi Denise,
There is nowhere in AP you can see this unless you are entering/storing this data somewhere “custom”. Out-of-the-box GP does not have a place for this or any functionality to report on it.
In fact, even in Bank Rec (assuming that’s what you mean by check register), unless you are actually updating cleared dates every day for outstanding checks, the only thing you can see is whether the check has been reconciled, which most companies only do once a month and not always right at month end, either.
All that said, if whether it’s reconciled or not in GP is enough (and if you are using Bank Rec in GP), you can create a SmartList to show just the check number and the reconciled status. While not in AP, it is a simple “report” you could give to users.
Hope that helps,
Victoria
LikeLike
is there a way to see who is creating vendor ID? We have several unauthorized IDs created.
LikeLike
I don’t believe this is possible in out-of-the-box GP. 😦
-Victoria
LikeLike
You can add under DOCTYPE (Document Type): 7 – Schedule Payment
I have noticed that this shows up on the PM table PM20000 for Schedule Payments
LikeLike
Thank you Dennis, I appreciate the info and have added it to the list.
-Victoria
LikeLike
Hi Victoria,
We are looking at doing some customer flow analysis at our retail stores and would like to know the time that invoices were generated/posted. I have looked at the TIME1 field but it doesn’t actually seem to store time data.
Is there any table that we could pull transaction times off of or is there a setting we can change to enable time logging?
LikeLike
Hi Steven,
As you’ve seen, GP does not store time for most things, only dates. And even dates and users are not always stored for everything you want. If this is important to track and you’re only talking about a few things, you could create some SQL triggers for it with a custom table storing the events. You have to be very careful when doing this to not impact normal processing and other integrating applications/processes. But it’s doable. If you’re looking to track a lit of different things, it might be worthwhile to look into an ISV product for this, something like Rockton’s Auditor or FastPath’s Audit Trail.
-Victoria
LikeLike
Thanks Victoria,
Really we were just looking at Invoices; however, that will still be a substantial number of transactions. I will talk to our developers to see if they can build this in for us.
Cheers,
Steven
LikeLike
Hi Victoria!
I would like to know, is it possible to have transactions with dist type of Recv but with a payable GL account (and vice versa with payable dist type)? Or is this a recording error? Thank you!
LikeLike
Hi KJ,
Dynamics GP does not check or enforce account numbers. So as long as the distribution types are correct for the transaction type and details, the account numbers can be anything you want.
-Victoria
LikeLike
That reply was fast, wow. Thank you.
LikeLike
Hi Victoria!
May I ask a follow-up question? I’m new to GP so I’ve been following your blog (big help, btw). May I ask, who decides the disttype? Is it automatically done if you (let’s say) record transaction in Receivables module?
And do you have any recommended readings (other than this awesome blog) for newbies like me to understand GP?
Thanks a lot!
LikeLike
Hi KJ,
When you enter a transaction on the main window, GP determines the distribution types based on that. While you can add to this and you can change the accounts, the totals for each distribution type must match the transaction details. It sounds like you’re trying to do something very specific here and I would recommend that you discuss the details with your GP Partner. Just because something will work technically does not mean it will not cause issues in other areas of the system. For example, you might be completely undermining the AP and AR reconciliations to the General Ledger by changing the “control accounts” for AR and AP transactions.
As far as reading for GP, there is a huge amount of information available, most online and free. There are also several books for sale, many are on specific topics. It’s really hard to recommend anything without knowing the specifics of what you want to learn and what you’re doing.
-Victoria
LikeLike
Hi – Which table will be best to look at the last date a Vendor was paid. Is it correct to use the POSTEDDT ?
USe RICO Select VENDORID,
DOCNUMBR,
DOCAMNT,
POSTEDDT From PM30200
Where VENDORID in ( ‘TRU001-01’,
LikeLike
Hi Junyad,
The POSTEDDT field holds the date a transaction was posted (meaning when someone clicked the Post button), which is not necessarily helpful if transactions are not always posted right away. The query below will show you the last payment date for each vendor:
select VENDORID, max(DOCDATE) Last_Payment
from PM00400
where DOCTYPE = 6
group by VENDORID
-Victoria
LikeLike
Hi Victoria, Thanks so much for your blog. I use it often.
Have you written any blogs about the Dynamics GP workflow tables? I ask, because we are using econnect to create Payables Management batches. This worked wonderfully until we turned on workflows for PM Batches. The batches appear normal until we submit, and then it’s clear we’ve missed something.
I’ve been comparing differently-created batches, and I cannot find a difference in PM tables, and I can’t really find which workflow tables might be involved.
Do you have any thoughts, or do you know which WF table might shed some light?
Thank you, Lynne
LikeLike
Hi Lynne,
Sorry, I don’t work much with workflow, so I don’t have anything on the tables for it. 😦
-Victoria
LikeLike
Lynne,
You might want to get in touch with Ian Grieve over at AzureCurve,co.uk
He wrote a book about WorkFlow 2.0 for GP and might know some of the details about how it works. He also has a reference page for GP tables that you can use to locate the data in GP : https://gptables.azurecurve.co.uk/
LikeLiked by 1 person
Great! Thank you, Beat.
-Lynne
LikeLike
Hi Victoria,
We have users who change the default PAY account on the Payables Transaction Entry Distribution window constantly and causes issues even though we instruct them to leave the default account. Is there any way to restrict changes the PAY type line alone in the window? In other accounting programs, this is normally the control account is normally grayed out so the users can’t change it.
Thanks in Advance
LikeLike
Hi Purnellsookhai,
There is no way to do this in GP without a customization. For what it’s worth, my personal stance on this is that if users have been instructed not to do something and are still doing it, the problem is not the accounting software.
-Victoria
LikeLike
If anyone is needing average days to pay I created this query for our PowerBI Dashboard. The first half of the query can be used to look at ADTP by vendor if you’re interested in that as well. Feel free to offer suggestions for improvement. Please note this looks at paid off invoice date so any short pays or partial payments won’t be factored in.
Select
VENDORID,
DOCDATE,
DINVPDOF,
DOCAMNT,
DATEDIFF(day,DINVPDOF,DOCDATE)*-1 AS DTP
INTO #DTPTABLE
From dbo.DimGPCEXPL_PaidTrans –PM30200
WHERE DOCTYPE = ‘1’
AND DOCDATE >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
Order BY DTP DESC
Select
YEAR(DOCDATE) AS [Year]
,MONTH(DOCDATE) AS [Month]
,DATENAME(MONTH,DOCDATE) AS [Month Name]
,AVG(DTP) AS DTP
FROM #DTPTABLE
WHERE YEAR(DOCDATE) = YEAR(getdate())
AND DOCDATE !> getdate()
GROUP BY YEAR(DOCDATE),MONTH(DOCDATE), DATENAME(MONTH,DOCDATE)
Order BY 1,2
DROP TABLE #DTPTABLE
LikeLike
I’m getting a “A save operation on table ‘POP_POLine’ (45)” error message and have been for about a week. Any ideas as to what is causing this? I’ve googled but can’t find an answer. Thank you,
Lynn
LikeLike
Lynn,
That error could be almost anything unfortunately. What specifically are you doing when you’re getting the error? Does it happen one one PO or all PO’s? For you user or all users?
-Victoria
LikeLike
Hi Victoria!
We are getting this error when trying to delete a PO. Any user that tries to delete is getting the same error. It is not for all PO’s…just some. I can’t figure out why it is only for some PO’s.
-Lynn
LikeLike
Hi Lynn,
What is the actual error you’re getting?
-Victoria
LikeLike
Hi! The error is “A save operation on table ‘POP_POLine” (45)”.
-Lynn
LikeLike
Hi Lynn,
Unfortunately, that is just a generic error that does not give us much. But if there was some business logic preventing what you’re doing it would give you a different error. So my guess is there may be a data issue of some sort with this particular PO. Have you tried reconciling this PO? go to Microsoft Dynamics GP | Tools | Utilities | Purchasing | Reconcile Purchasing Documents – enter the PO number in the From and To and select Reconcile and Print Report on the bottom, then click Process. Does that give you any results?
-Victoria
LikeLike
Hi again!
The reconcile did identify and fix a tax issue, however, when I try to cancel it in Edit Purchase Order I get the same error message.
-Lynn
LikeLike
Hi Lynn,
I would recommend asking for help either from your GP Partner or Microsoft Support on this. They will likely need to look at the data directly and see what is going on.
-Victoria
LikeLike
Thank you so much for checking into this for me!
-Lynn
LikeLike
Hi Victoria!
We are trying to cancel a PO that is in a Released status (for all users). We can cancel other PO’s but I have not checked their status.
-Lynn
LikeLike
Is there any way in SQL to link a payable in PM30200 back to the original invoice in SOP30200? If you look up the Payables Transaction Entry window it has a Description field, but come to find out it isn’t the text that gets put into the TRXDSCRN field in PM30200… I’m trying to work on an ACH process with our bank and one particular vendor (a credit card company) is mandating that we include this information…
LikeLike
Hi Blair,
There is no direct link from PM30200 to SOP30200. Sometimes you are able to do this with linking the PM data to the POP data to the SOP data, but not always and it really depends on the details of how all your transactions are entered into GP.
Separately, the Description on the Payables Transaction entry window DOES go into the TRXDSCRN column, but for invoices. If you’re entering a payment of an invoice with a credit card, which creates a new invoice under the credit card vendor, that invoice will not have the same TRXDSCRN as the original invoice. Easy enough to link with a report using the apply tables. Take a look at my various apply scripts here to see if any will help: https://victoriayudin.com/gp-reports/payables-sql-views/
-Victoria
LikeLike
All,
In an effort to reduce waste in our operation we are making an effort to reduce paper check payments. I have created the below query to analyze check payment usage. Feel free to adjust as necessary for your needs. Love the site, thanks for all the information.
SELECT
[VCHRNMBR]
,[VENDORID]
,[DOCDATE]
,[DOCNUMBR]
,[DOCAMNT]
,[BACHNUMB]
,[TRXSORCE]
,[BCHSOURC]
FROM PM30200
Where PYENTTYP = ‘0’
AND VOIDED = ‘0’
AND BCHSOURC = ‘XPM_Cchecks’
AND DOCDATE BETWEEN ‘startdate AND ‘enddate’
LikeLike
Hi Victoria,
Do you know which table houses the deferral module for purchasing? We use this to enter in invoices that are prepaid and then the system amortizes them based on the start date and end date.
LikeLike
Do you know what table links the payable transaction voucher number and the financial transaction journal entry number?
LikeLike
Tabitha,
This would be in the General Ledger transaction tables – GL20000, GL30000, GL10001. Note that you will only see voucher numbers in these tables if you are posting to the General Ledger in detail.
-Victoria
LikeLike
Victoria,
I ran a script, Select * from SY01200 to see the email address that is attached to the Vendors. I have over 1000 vendors in the vendor master, but only 600 show up on my select Statement. I thought maybe it’s only showing the vendors that have an email attached, but there are many that don’t have an email and they are showing up. So Why aren’t all the vendors in the SY01200 table.
Thanks
Philippa
LikeLike
Hi Philippa,
The SY01200 table stores the data on the Internet Information window for Vendors, Customers, Items and Employees. It’s not specific to email addresses. It could also show multiple lines per vendor because the internet information is specific to a vendor address ID, not just vendor ID. Hope that helps.
-Victoria
LikeLike
Hi Victoria,
We use GP2015.
Is there a way to see the document date for a vendor invoice or credit memo regardless of whether it was paid? We understand that PM20000 has open trx while PM30200 has paid/historical transactions. But is there a table that has a complete population of all documents with their respective document date?
Cheers,
Grant
LikeLike
Hi Grant,
PM00400 has all the document dates, but not much more data. Can I ask what you are trying to do and why you can’t use 2 tables (PM20000 and PM30200)?
-Victoria
LikeLike
Excellent! Thank you! I did not know about that one. (And I see you have it listed above, shame on me for not checking that one, I presumed that table was associated/similar to PM00200.)
I’m joining the document date, which we use as the vendor’s invoice date, to the GL to see the lag, in days, between the date an invoice posts to our GL and the actual invoice date. (This gives us an idea of how much $$ hits our GL each month that relates to prior period services/goods). (We’re trying to nail down/improve our accruals), so this is really helpful.
Love this site. Big fan! Cheers
LikeLike
Hi Victoria,
Great site! Big fan.
Question: Is there a single table that has the Document Date for invoices regardless of whether it has been paid? PM20000 is for open documents and PM30200 is for paid documents.
Asking because I’m trying to measure the $ of invoices that hit our GL each month that are from the prior month. When I query from PM30200, I’m getting blank records for invoices that are open/unpaid.
DECLARE @EndDate DATETIME
DECLARE @StartDate DATETIME
SELECT @StartDate = ‘5/1/2019’
SELECT @EndDate = ‘5/31/2019’
SELECT
JRNENTRY AS [JE Number]
,USWHPSTD AS [User]
,GL105.ACTNUMBR_1 AS [Co ID]
,GL105.ACTNUMBR_2 AS [Dept]
,GL105.ACTNUMBR_3 AS [GL]
,GL105.ACTNUMBR_4 AS [PC]
,GL105.ACTNUMST AS [Account Number]
,CASE GL100.PSTNGTYP
WHEN ‘0’ THEN ‘BS’
WHEN ‘1’ THEN ‘P&L’
END AS [Account Type]
,TRXDATE AS [GL Doc Date]
,PM.DOCDATE AS [AP Doc. Date]
,(ORPSTDDT – PM.DOCDATE) AS [AP Post Lag]
,ORMSTRID AS [Vendor ID]
,ORMSTRNM AS [Vendor Name]
,SOURCDOC AS [Source Document]
,REFRENCE AS [Reference]
,DSCRIPTN AS [Transaction Desc.]
,ACTDESCR AS [Account Desc.]
,ORDOCNUM AS [Document Number]
,DEBITAMT AS [Debit Amount]
,CRDTAMNT AS [Credit Amount]
,(DEBITAMT – CRDTAMNT) AS [Net Amount]
FROM RV_GP.dbo.GL20000 (nolock)
JOIN RV_GP.dbo.GL00105 GL105 ON GL20000.ACTINDX = GL105.ACTINDX
JOIN RV_GP.dbo.GL00100 GL100 ON GL105.ACTNUMBR_3 = GL100.MNACSGMT AND GL105.ACTINDX = GL100.ACTINDX
LEFT JOIN RV_GP.dbo.PM30200 PM ON GL20000.ORDOCNUM = PM.DOCNUMBR AND GL20000.ORMSTRID = PM.VENDORID
WHERE TRXDATE BETWEEN @StartDate AND @EndDate
AND GL105.ACTNUMBR_3 = ‘2000’
Keep up the great work!
LikeLike
Hi Grant,
I didn’t see this before replying to the previous message – try this, I changed your code around a bit and my SQL editor makes all the special words lower case. Note that if you are using the POP module this will still return nulls for the dates from invoices posted in POP and not PM.
Hope that helps,
-Victoria
LikeLike
PS – you might need to reformat some of this a little when you paste it back into SQL. For some reason some of the things like single quotes and double dashes get formatted wacky in the blog editor. Sorry about that.
LikeLike
Perfect! Yes, this works after tweaking a few quotes and the minus sign. I like how you incorporated the coalesce function. (never seen it used). This properly fetched the first non-null value. Thank you so much!
LikeLike
Glad that worked for you!
Coalesce is one of my favorite things to use in SQL. The beauty of it is that you can put any number of fields/values in a list, it will use the first non-null it finds in the order you specify. It seems more intuitive to me than using ISNULL and more functional.
Since you’re not really pulling much of anything else from the PM tables you could have probably used PM00400 here, but I wanted to show you how to do it with multiple tables and a coalesce, as that comes up often with GP data.
-Victoria
LikeLike
I found something odd in GP and I am curious if it is standard or we have a glitch. I input several AP transactions in a foreign currency. When I look at them in the entry window, I see the original amount entered. When I run an edit list for the batch, I see the amount based on the exchange rate. This is very confusing to users, so when they go to confirm the amount they are posting, the totals in the edit list do not match what they see online. Is that normal?
LikeLike
Hi Cory,
It sounds like you may want to switch to the Multicurrency version of the edit list report. To do that – go to Microsoft Dynamics GP | Tools | Setup | Posting | Posting, select Purchasing for the series and Payables Trx Entry for the Origin (I believe that is the transaction type you’re asking about). Once the Series and Origin are selected – check the box over on the left for “Include Multicurrency Info” and click Save. Now when your users print the report they will see both the originating and the functional currency.
Not all, but many of the posting reports are available in multicurrency versions; they are not selected by default because many companies using GP do not use Multicurrency or – even if they do – they do not care to see the MC info on there.
Hope that helps.
-Victoria
LikeLike
That is a great help. Thanks very much
LikeLiked by 1 person
I’m new to GP.
Which table contains Vendor ‘s payment-terms and discount-percentage ?
Sam
LikeLike
Samuel,
Should be PM00200.
-Victoria
LikeLike
Under what circumstances would a record be in PM30300 with Posted = 0? I’m drawing a blank. I’ve tested a couple of scenarios with credit memos, but nothing I do generates a record in PM30300 with Posted = 0. Thanks in advance
LikeLike
Hi Sherry,
I have just checked a number of different datasets I have and not one of them has any records in PM30300 with POSTED = 0. Do you have a lot of these?
-Victoria
LikeLike
I do not. I just ran across a custom process being used to pull information about PM payments from GP to send to another system, and it has a where class where pm30300.posted = 1. Just wondering why. I am building a similar process, and I need to know if I need that restriction. Thanks!!!
LikeLike
Hi Yudin,
Can the amount of a payment in PM20000 change after it has been inserted into that table?
I would like to know how to tell when the amount of a payment is set in stone.
Thanks,
LikeLike
Hi Eric,
No, not through the user interface. Transactions in PM20000 are ‘posted’, meaning they cannot be changed. And, of course, it would be a very bad idea to do this directly in SQL, as it could easily cause data issues and inconsistencies in related tables.
-Victoria
LikeLike
Hi Victoria- I am new to GP AP module but very familiar with the SQL queries. I am tasked with creating a smart list which has vendor details (especially the Tax details for 1099 vendors), invoice details and payment details. Do you happen to have a SQL query to address this?
Thanks so much.
LikeLike
Hi Mass,
I have a lot of different code that might help, please take a look at this page: https://victoriayudin.com/gp-reports/payables-sql-views/.
-Victoria
LikeLike
Good Morning Victoria,
I am trying to locate the actual Vendor Discount Amount (i.e. 2%, 10%, etc). I looked under PM00200 but did not find it. I found instead the discount id.
Thank you.
LikeLike
Hi Carol,
The vendor discount amount is calculated for each transaction – if you are looking for that, you would need to look at the transaction tables.
-Victoria
LikeLike
Hi Victoria,
could you tell me what tables are used to interface with Stratton Warren I/P system? i need AP voucher, GL journal header and detail , PM line item file mapping
LikeLike
Joyce,
Sorry, I have not heard of “Stratton Warren I/P system”. I am guessing it’s either a 3rd party add on or a customization created for you. In either case, you would have to ask the company that created it for those details.
-Victoria
LikeLike
Hi Victoria,
I need to query PM payments that have been voided since a given date. For instance, I need to query payments voided in the last two days, e.g. payments voided since 10/17/2017.
Is VOIDPDATE a reliable field to use for that? Or would MODIFDT or DEX_ROW_TS be a better date field for this scenario?
Steve
LikeLike
Hi Steve,
I think DEX_ROW_TS is the most reliable field to use for this, you would need to do some testing to make sure. The VOIDPDATE is entered by the user – so that can be anything. The MODIFDT could be updated by something else possibly (I think, again might need some testing).
-Victoria
LikeLike
I am doing a report using PM30200 when DOCTYPE =1 which I believe are Invoices. I would like to include GLPOSTDT in the report, but it is not in that table. What is the best table I can use to pick up that additional field and what field(s) could I use to join the two tables? Thanks!
LikeLike
Hi Carol,
The GL Posting date is in field PSTGDATE in the PM30200 table.
-Victoria
LikeLike
Hello Victoria,
I have few transactions for vendor A that have PO, Invoice, Payments for dropship items. Some of the payments are open and some are posted. Now, my supervisor has asked me to change from vendor A to B for those transactions. Is there any utility available to do this or any sql script? Please suggest how to proceed.
Thanks & Regards,
Prakash Mishra
LikeLike
Hey Victoria! We have multiple company databases and very high volume of AP transactions. We have had vendors paid twice for the same invoice because the invoice was entered in error either in the wrong company database or in the correct database but under a different vendor id. I am looking for a way either in Smartlist or Excel Reports that the AP Supervisor can look before check runs to see if there are duplicates in the system. Document Date and Amount should always match where Vendor ID would match only some of the time. Thoughts on the best way to achieve this? Thanks!!
LikeLike
Hi Debbie,
I don’t see why you couldn’t create a custom SmartList (or another type of report) that would pull this data from all your companies and help with this verification. If you don’t have someone that can code this for you, it is something that we can create for you as a consulting project. Let me know if you’re interested in discussing this in more detail.
-Victoria
LikeLike
I have a smartlist that pulls all of the data from all companies but I can’t seem to get my mind to wrap around how to compare the rows to look for possible duplicates.
LikeLike
Debbie,
I don’t think you can do this in SmartList directly. If it were me, I would write some SQL code that would look at the unposted payments in the system and then find anything that looked similar based on some predefined criteria.
-Victoria
LikeLike
Hi Victoria,
I’m creating a SQL view for Payment Batch Review (pretty much taking the info from the edit list and putting it in a smartlist) I need to join PM10300 and PM20000 and I want to include the payment batch information as well as the invoices and credit notes associated with that payment batch. The only way I can see to joing them is by Vendor ID but when I do that I get all open invoices/credit notes for the vendor I selected (not just the invoices/CR in the actual batch but I get all the document for the vendors in my batch no matter if they are selected to be paid or not)
If you can help me join PM10300 and PM20000 or PM10200 to only include the documents for a specific vendor selected I would really appreciate it.
Thanks!
LikeLike
Hi Fab,
You should be able to get the apply information for any payment in PM10300 by using the PM10201 table.
Hope that helps,
-Victoria
LikeLike
Victoria, thank you again for your valuable help!
I was wondering if you have a view where the top 10 (or 20) percent of vendors and payments can be returned, for a date range. these would be either payments or invoiced amounts. Thanks!
LikeLike
Hi Bill,
Thanks for your kind words. I think I have done something similar to this in the past. Let me look around for it and I will post it once I find it.
-Victoria
LikeLike
Hi Bill,
Take a look at my latest blog post for an example of how you can do this: https://victoriayudin.com/2016/03/21/sql-code-to-show-top-x-percent-of-vendor-data/
-Victoria
LikeLike
Hi Victoria,
I have 2 recurring batches, when they were set up they were set up as not a 1099 vendors. They are a 1099 vendor, we’ve updated the VC but when we print our listings for 1099 vendors they still show up as not a 1099 vendor. Do we have to issue new batches?
LikeLike
Hi Debbie,
You can try going into each transaction and entering the amounts into the 1099 Amount field, but it might be better to delete and recreate the transactions so that all the new settings for the vendors are applied.
-Victoria
LikeLike
Hi Victoria,
I have an unposted batch that has 36 transactions but the Payables Batch Entry screen shows only 24 transactions. When I look at the PM10000 table I see 36 rows and I see all 36 transactions when I print the transactions. The batch total does not match the sum of the transactions either. Any ideas on where I could begin to look to track down the difference? Thanks for everything you put out here!
LikeLike
Hi Jacob,
Batch totals get out of whack once in a while. Doesn’t happen often, but there are a number of things that can cause them to not update properly, for example someone having the batch window open while transactions are being entered.
The good news is that when you post the batch, those batch totals are not used for anything. They are there for your information only. (And the occasional people that use them for posting controls, but that’s very rare.) When you post, the system will recalculate the totals as needed, same as what you’re seeing on the report you’re printing.
If you feel that you absolutely have to fix the totals, you can try running reconcile – Microsoft Dynamics GP | Tools | Utilities | Purchasing | Reconcile | Batches. But again, no need, just go ahead and post the batch if/when you’re ready.
-Victoria
LikeLike
Need to find the details that were printed on the check remittance detail. I’ve used several of the views you have posted but none of them show all items. In my example I have a check with 75 credit memos and invoices to which it was applied. The user would like to be able to retrieve these details on-demand. Running GP2010
LikeLike
Kat,
This is not possible in GP 2010, as GP does not store the details of what credit memos were printed on the remittance. I believe this is possible in GP 2015, but you would need to do some testing to confirm you can get everything you need.
-Victoria
LikeLike
I have an invoice that is in the PM30200, but with no apply information in the PM30300. When I run an HATB it shows up, but when I run just a regular aging, it does not appear. It also appears that a payment was voided that has that same VCH number. So, which is correct here, the HATB or the regular aging? If the HATB is correct then how do I get rid of the invoice in history with no apply information? I cannot void the invoice. Did the void of the payment somehow get interrupted and corrupted the apply on the invoice? Also, this is on our credit card vendor using your credit card method. I don’t know if that makes a difference.
LikeLike
Hi Sean,
Unfortunately, without seeing the actual data, it’s very difficult to suggest a fix and could easily cause more harm than good. I would recommend working with either your GP Partner or Microsoft Dynamics GP support to help you clear this up.
-Victoria
LikeLike
Hi Victoria, I am curious about the field “Parent Vendor ID” PARVENID in PM00200. This field does not appear on any data entry screen I can find. Is it a legacy item that no longer functions, or could I ask my consultant to add it to the vendor maintenance and vendor inquiry screen? The problem we are trying to solve for is multiple vendor ID’s for closely related or duplicate vendors. We often have operational reasons to maintain two separate vendors, but then need to do consolidated reporting, and currently don’t have a way to relate two vendors to one another.
LikeLike
Hi Pati,
My educated guess is that this field was added with a plan to use it in the future. I have not seen that used anywhere in Dynamics GP up to now. The danger of using it would be that in a future version of GP it might start being used, which would cause an issue with all your existing data in there. In addition, you need a modified window to be able to use the field.
Another option might be to use Extender and store your parent ID there, then you can be confident there will not be any conflicts in the future.
-Victoria
LikeLike
thank you Victoria. We will look into Extender as an option.
LikeLike
I tend to agree with Victoria. I would guess that GP is planning to mirror the National Accounts functionality on the receivables side.
This is commonly requested functionality, and is currently available from a number of ISV’s. If this is a big enough problem for you, you might consider purchasing one of these add-on products in the meantime. Google “Dynamics GP National Accounts”.
Other ways to group like vendors for reporting purposes would be to use Class ID’s and/or to add a prefix to your “like” vendors.
LikeLike
Hi Victoria,
I am receiving the below error message when exporting invoices, do you have an idea what this may be related to?
error message:
A save operation on table ‘VSync_SOP_HDR_DOC_WORK_HIST; (45)
LikeLike
Amber,
Do you have something from Vsync? It would probably be best to ask them, as it seems to be their SQL object giving you an error.
-Victoria
LikeLike
Victoria,
I’m trying to add the Account number that was used for the invoice. Since their are multiple account numbers for each vendor, I can’t use the vendor’s default account number. Is there another table I can tie to PM30200 to allow me to pull accounting number by invoice?
thanks!
LikeLike
Hi Grayson,
I have a view that may help with this: GL Distributions for AP Transactions.
-Victoria
LikeLike
Duplicate payments are showing up in PM30300. We suspect this is because there was an error when we first set up the company, and it has since been fixed. This is throwing off the 2014 Historical Trial Aged Balance. Can I delete the ghost duplicate payments from this table in order for the HTAB report to show what was actually paid? We would like for them to just be removed and for there not to be any additional void/journal entries needing to be done.
LikeLike
I would never ever recommend deleting any data from transactional tables without first having someone that really understands the Dynamics GP table structure and transaction flow well taking a look at it.
-Victoria
LikeLike
Do you recommend any books? I tried some, but they wound up being more focused on using Dynamics instead of the flow of data across tables. Thank you for all of the helpful information that you have posted!
LikeLike
Sorry, I am not aware of any books that would help you with enough information to be able do this yourself. You need to work with someone that can help you do this.
-Victoria
LikeLike
I use this site: https://www.gptablereference.com/ for table references. It doesn’t do a good job Like this site explaining flow, but it does contain the full table schematics which can be very useful. Not sure this will help, but it is a good table reference for GP and easy to search.
LikeLike
Victoria,
Where can I find the default accounts entered on vcard in SQL? I am specifically looking for the purchasing accounts.
Thanks,
Brian
LikeLike
Hi Brian,
What’s a ‘vcard’?
-Victoria
LikeLike
Victoria,
Sorry, vcard is vendor card. I was able to find the account I added under purchasing in the PM00200 table. There is a field called PMPRCHIX which references the account in the GL00100 table. The only issue I am facing now is that in GP when setting up the default purchasing account you have the option to add additional accounts clicking the button on the right with three little dots. I am unable to find out where this information is stored in SQL. Any ideas?
Thanks,
Brian
LikeLike
Brian,
Gotcha. Those accounts are in the PM00203 table – I just added it to this page. 🙂
-Victoria
LikeLike
Brian,
I have a vendor listing view that I’m trying to pull in the PM default account as well, but I don’t know how to link the two tables to get the PMPRCHIX to show as the account number instead of the account index number from GL00100. Do you have a view that you used? Thanks for your help!
LikeLike
Andrew,
You can join GL00105 which holds the concatenated account number – link from PM00203.PMPRCHIX to GL00105.ACTINDX.
-Victoria
LikeLike
Is there a way to tell who applied payment to an invoice? I didn’t see this info in PM30300.
Thanks
LikeLike
Jeremy,
GP does not store this, so unless you had something set up ahead of time, you’re not going to be able to tell this. 😦
-Victoria
LikeLike
Victoria,
Though it’s almost cliche at this point, I’d be remiss not to mention what an invaluable resource you are to me and everyone here.
I’m having a very strange problem and I’m all out of ideas for solving it.
There is a payables transaction (Invoice, DOCTYPE=1) that has found its way into the PM30200 table. On the front-end, we cannot select it to be paid, despite the fact that it has not been paid and still shows up as a part of outstanding A/P. The transaction shows an unapplied amount of 0, which tells me that something must be applied to it. There is no matching transaction on the PM30300 table.
It feels like the ideal solution would be for this transaction to migrate back over to PM20000, where it belongs and for it to stop pretending that it’s paid.
I’ve tried checklinks and reconciles and nothing seems to do the trick. Also, we can’t even void it. Any suggestions on how I might reason with this little guy?
Thanks!
-Luke
LikeLike
Hi Luke,
That’s definitely a strange one, and as many strange things as I have seen, not one I have run into before. Part of the problem with simply ‘moving’ the record from PM30200 to PM20000 is that there are a lot of related tables, like distributions, summaries, PM keys, etc. that also need to be considered and probably updated.
You might have already done this, but the only other thought I had was to see if (a) there are any other transactions like this and (b) see if there are any payments sitting in the PM30200 or PM20000 tables with transaction amount <> current amount also without apply records? If (a) is true, you might have a systematic issue and if (b) is true, you might simply be missing some apply records, which might be easier to fix.
I wonder if this might be worth a call to Microsoft Dynamics GP Support to see if they can help with fixing it? 😦
Sorry not to be able to offer more help on this,
-Victoria
LikeLike
Hi Victoria,
We are having a very hard time auditing transactions in GP. In the GL2000 table which is the main table for all transactions that have been posted and is the primary table we use each day to see a list of all work that has been Posted. It is a very important table as all financial info is derived from this table for the current open years.
We use the ORCTRNUM field in GL2000 to relate back to the Work tables that have been audited.
For Example , the PM1000 and the PM10300 are audited and each time a transaction is entered a summary of that transaction is inserted into an Audit table.
The VCHNUMWK and PMNTNMBR of those tables are what should enable use to relate back to the GL2000 tables ORCTRNUM
However what we are finding is that there are many duplicates in our GL2000 ORCTRNUM which seems odd as wouldn’t the ORCTRNUM be unique throughout the GL2000?
How can we prevent duplicates in our GL2000 ORCTRNUM field?
LikeLike
Hi Larry,
I am guessing you’re referring to GL20000 (not GL2000)? If you have a table called GL2000, then that’s not a GP table and not something I can speak to. If you’re looking at GL20000, this table will have a row for each individual line on a GL transaction. For regular (posting) GL accounts, that is a minimum of 2 lines per GL entry – a debit and a credit. In reality, many GL entries will have more than 2 lines. So you should always expect to see at least 2 rows in GL20000 with the same ORCTRNUM. How you relate these to the original transactions will vary greatly depending on the transactions themselves and your company’s accounting practices, not something easy to answer generically. Hope that helps.
-Victoria
LikeLike
Thanks Victoria, yes I meant the GL20000 table. Its hard to see here in the pasted transaction, but below, 00118874 is the ORCTRNUM in our GL for two separate unrelated transactions. One was actually in 2013 and one was in 2014. It looks like one was a transaction entry and one was a payment? Would the ORCTRNUM be the same for a payment and a payables entry?
Is there something we should change in GP to ensure that No transaction would ever use the same ORCTRNUM?
37396 rongl 1 2014-08-05 00:00:00.000 1900-01-01 11:57:01.000 1 1 XPM_Cchecks-08012014TWD-00118874
21339 ClarenceW 0 2013-12-12 00:00:00.000 1900-01-01 11:01:37.847 3 1 121213CW-PM_Trxent-00118874
LikeLike
Larry,
As you’re just discovered the Control Number (voucher number) in Payables is not unique. Together with either the Control Type or the Doc Type it will be unique. While you can probably change your Payables setup to have credit docs and debit docs use a different numbering scheme, it’s not necessary, and it would not help with historical transactions. Instead, you can link to the PM tables using both the ORCTRNUM and the ORTRXTYP – this will link to the DOCTYPE in PM tables.
-Victoria
LikeLike
Thank You!!!! I’ll give that a try.
LikeLike
Hi Victoria
I’m trying to repair some differences in some payables postings some time ago. It appears someone knew something was wrong because they have posted corrections as GL journals. I’m trying to get the headers back in, and (working in a copy of the company at present!) I’ve generated some records to insert into PM30200 generated from missing references from PM30300.VCHRNMBR.
What I’m now trying to do is have the “Reconcile to GL” report for that period show the payables headers to offset against the GL journals. This kind of works – but some documents for May 2011 have PM30300.ApplyToGLPostDate in March and April 2011, which kind of messes up the report if the date range starts with May 2011 – those documents don’t appear.
More widely, I’m not sure I understand what this means exactly – do you have any idea?
Thanks,
Tony
LikeLike
Tony,
I would be EXTREMELY careful about inserting transactional records directly into the tables, this can very easily go very wrong, and you could be left with a big mess in your GP data. I also don’t recommend getting advice on this online. You want someone that knows this stuff very well to look at your data and help you with whatever needs to be done. You also want that party to be responsible for the advice they are giving you, in the sense that they will support you if future issues arise from following their advice. You might actually be better off working with Microsoft Support on this.
I hope this makes sense.
-Victoria
LikeLike
Thanks Victoria – noted. I think what we might do is just get things straightened up in the test company so we can produce numbers for that year, make sure the ongoing balances match, and not do the equivalent inserts in the live company.
Whatever went wrong around that time though has left us with a bit of a mess anyway, and at least this process is helping us understand it. The occurrence of this corruption has led senior management to suggest that we should ditch GP and move immediately to something else, which is obviously something I would seek to avoid (not sure they have any conception how much work is involved!)
– Tony
LikeLike
Hi Victoria, If during the middle of the fiscal year we change the class code for certain item to make them warehouse (distributor) items instead of drop-ship (agent) items, then should we assign a new vendor id to those items with vendor class as distributor or should we just change the vendor class from agent to distributor. Please note that in our system Agent vendor have different accounts and distributor vendors have different accounts
LikeLike
Zafar,
In general there is no issue with changing a vendor class anytime you want. That said, your questions are very specific to how you are using GP and your business workflow. I would be doing you a disservice if I simply answered them without a lot more information about how you are using vendors and items and what reporting you are doing. It would be best to ask your GP partner or a consultant that is familiar with exactly how you’re using GP to help you with this.
-Victoria
LikeLike
Hi Victoria, can we change a vendor class from agent to distributor during the middle of the fiscal year.
LikeLike
Hi Victoria,
I’m on Dynamics 2010. Trying to create a SQL table on excel. We have two companies, and they each have different TAXSCHID, One company’s vendor ID starts with CC. And the account number for the tax account depends on the TAXSCHID. However, we realised some are entered incorrectly (TAXSCHID), and therefore the GL accounts are incorrect. So I want to run a list with Vendor ID, Taxschid, tax amount, and the GL account code. Note that I tried to get this from smart list, however, the tax account code was only taken from the master file, not from the actual transaction. Can this be fixed?
With SQL, I realise that I will need PM10100, PM30800, PM20000. And that I also need GL00105 or GL00100. However the GL tables do not link to the PM tables. And that data from the report are not 100% correct. Supervisor said it is because the tables are not linking.
Please help.
Carol
LikeLike
Carol,
I would start with my GL distributions for AP transactions view and then add the Tax Schedule ID to it.
-Victoria
LikeLike
thank you Victoria for all your posts and information you provide. We at BCBS of Nebraska are constantly saying “go ask Victoria”.
LikeLike
Thanks John, I really appreciate that! 🙂
-Victoria
LikeLike
Hi Victoria, Thanks for the all the helpful information you’ve posted. I am wondering if there is some kind of remittance or other printed report for Manual Payment entries in GP2010? We are looking to pay vendors using the online bill payment function in our banking website. When these are entered in PM, which I think would best be done through Manual Payment entry, we’d like some kind of remittance to print, something like the detail on a cheque stub, showing the invoices applied to the payment and the payment number/check number we assign when entering in Manual Payment entry screen. I can’t seem to find anything the suggests how this might be done since it doesn’t seem to be built in as part of the entry & posting routine. Do you have any suggestions, please?
Thank you
Judy
LikeLike
Hi Judy,
I am not aware of any existing report to do this, you would need to create a custom report.
-Victoria
LikeLike
Why not use Computer Cheques instead of Manual Payment entries in GP? Then you’d have the option to print the remittances. You can use a different numbering sequence to represent your online payments.
LikeLike
Hi Kimberley, Hhmmm, that sounds like a great possibility. How would it work? Because we don’t want cheques to print, with the payments already having been made, but if we enter the invoices in GP, wouldn’t it print cheques automatically when we Select Checks and do the rest of the cheque routine?
LikeLike
Hello
I am attempting to use smartlist builder to create a smartlist that will display Payments, Apply To invoices and the expense (purchase) portion of the distribution from the invoice.
I tried selecting PM Distribution History File and PM Paid Transaction History File. This gives all of the data but it does not tell me what Invoices are applied to a specific payment. Not sure if using SQL server Table is an option since I cannot find a common field to get the results I am looking for.
Any light that you can shed on this would be greatly appreciated.
Thank you
Fabian
LikeLike
Fabian,
I am not sure this is possible directly in SmartList Builder because you need to add a number of table unions. However, the good news is that I already have a SQL view published with this exact information: https://victoriayudin.com/2010/09/23/sql-view-for-payables-apply-detail-and-gl-distributions-in-dynamics-gp/. You can just use this code to create a SQL view, and then create a SmartList pointing to that view.
-Victoria
LikeLike
Victoria,
Question regarding PM00201. I can see the last check amt, check no in PM00201. But some vendors are paid through wire or EFT and I don’t see either the last wired amount or EFT amount in PM00201.
Is there any other table I should look at for wire and EFT payments.
Thanks
Sreedhar.
LikeLike
Sreedhar,
Posted and fully applied payments will be in PM30200. Posted and not fully applied payments will be in PM20000.
-Victoria
LikeLike
So EFT and Wire payments wont show up in PM00201?
LikeLike
Sreedhar,
In this case the field names in the PM00201 table can be taken literally. This table only stores the Last Check Number, Last Check Date and Last Check Amount. No other payment types are considered.
-Victoria
LikeLike
Ok. I got it. Thank a lot for all the information.
LikeLike
Hi Victoria,
Is there any way to manually change a check # within the Manual Payment Entry? I know if I select a Checkbook ID, it will use the next available check number that is set. However, I have some situations where I need to override that check number to something completely different. If I change the document number, would that number be printed on the check as the check #? Or do you know any other way of changing this check number without the usual sequence?
Thanks in advance!
LikeLike
Hi Chan,
If you need to be able to override the check number at the time of entering a manual payment, that can be enabled with a setting on the Checkbook Maintenance window. (There is a checkbox towards the bottom of the window for Override Check Number.)
If you’re using manual payment entry, no check is printed, so I am not quite sure what the rest of your question is – can you please clarify?
-Victoria
LikeLike
So essentially, I am trying to develop an integration to create a payment in PM and specify that check number during creation. Is there any way of doing that?
LikeLike
Chan,
What tool are you using to create your integration?
-Victoria
LikeLike
Hi Victoria,
We have a manual payment in GP 2013 that appears to be hung up. The details of the transaction are as follows (written by the user as it’ll probably make more sense than if I try to explain it).
“Go into the Manual Pymt screen..enter pymnt number (I use vendor name and invoice #), pick the checkbook I want to use (credit card) then choose which card from the dropdown, fill in doc # and copy it to comment, fill in amount to apply, click on distribution tab and copy the doc # info into those two lines after verifying code numbers are correct, go back to first page and click on the apply tab, choose open invoice to apply the payment to, go back to first page, verify all information is correct ant click on post.”
When she trys to apply it to a payable document it tells her that it’s already fully applied. However it does not show up when she goes to pay the credit card. The posted status in PM10200 is 0 and DCStatus in PM00400 is 2. Basically we need to know how to either delete the whole thing or force it to apply to the credit card in the initial step. I apologize if this makes little sense as I don’t really use GP nor am I a financial person. Sometimes we just have to support it as best we can. Thanks in advance for any help!
LikeLike
Hi Dave,
The first thing I would try it to run check links against the Purchasing series. Once that comes back with no errors (you might have to run it more than once for that) then run Reconcile. This might clear up the issue. If not, you’re going to have to fix the data in the SQL tables directly, which is not something I would want to give advice on without actually looking at the data.
-Victoria
LikeLike
Hey Dave, do you recall how did you fix this issue, i’m having the same exact issue, and i’m not sure what to do. i do have great knowledge of sql but unsure which tables to alter, if needs be that is.
LikeLike
Hi Victoria,
I am wondering if there is any way to change the document date on a posted invoice. We are currently using GP 10, so we don’t have the doc date verify tool yet. An AP rep has entered a bunch of invoices with an incorrect doc date and I’m not sure if this is something that can safely be modified through SQL. Thank you for your help!
LikeLike
Carrie,
I would not recommend modifying these directly in SQL. There any many tables involved and it would be very easy to cause an even bigger issue. If these are not paid yet, you can void them with the same doc and GL dates that they were entered with, then re-enter them with the correct dates.
Also, Doc Date Verify is available for GP 10.0. 🙂
-Victoria
LikeLike
Hi Victoria,
In preparing for year end, we’ve found a vendor check with less than a penny remaining in the CURTRXAM ($.0028). We only use 2 decimal points so I can’t figure out why it has more or how get the AP document to move from PM20000 to PM30200 without changing our system defaults. Any ideas?
LikeLike
Hi Amanda,
I have seen this happen when data is imported with more than 2 decimal places. GP will accept up to 5 decimal places, but will only show you what your currency is set up for. I am not aware of any method to clean this up without fixing it in the SQL tables. Since there could be many tables affected, you will most likely need to work with your GP Partner or Microsoft Dynamics GP Support so they can look at your data and help you fix this.
-Victoria
LikeLike
Hi victoria:
Do you happen to know how PM10300 table gets populated? I’ve done the following scenarios in GP to check if the table gets populated
1. Create an Invoice then instantly associate with payment by providing value on Cash field.
Result: Payment is associated to invoice on table PM10000 not on Pm10300
2. Create a Payment using Manual payment
Result: Payment is saved on PM10400
3. Create a payment then apply on Open Invoice
Result: Payment is saved on PM10400
All this scenario does not populate PM10300. Can you specify scenario wherein PM10300 gets populated?
Thanks! and great work by the way.
Kristoper
LikeLike
Hi Kristopher,
The PM10300 table gets populated when you create a batch of computer checks.
-Victoria
LikeLike
Hey victoria, we’re having a strange issue with our EFT payments. We use MICR, and every once in a while, our EFT batch goes straight to posting (no screen to print remittance reports, etc), and it processes with blank check numbers for each EFT transaction. we end up having to turn on duplicate check numbers, process the batch from batch recovery, void all the EFT payments, and then process the batch again. Any idea what may be happening?
I’ve sat with the person who processes the payments, and nothing appears to be different from a successful EFT batch, until the password is entered and it goes to process, and skips the remittance screen.
LikeLike
Hi Mike,
Sorry, I have not run into this before. When you say you’re using MICR, do you mean Mekorma’s MICR product? If so, I would suggest asking them if they have seen this before. Otherwise, you might need to turn on some logging before every EFT batch run to see if you can trap this in a log and examine in more detail.
-Victoria
LikeLike
Victoria,
Is there a table that holds remit to address that was printed on a check?
LikeLike
Hi Robert,
Unfortunately, no. The check will use the remit to address on the invoice(s) it was applied to. You can see this address ID in the VADCDTRO field in PM30200. This can link to the PM00300 table for the actual address. However, if the address was changed after printing the check, there is no record of it kept. 😦
-Victoria
LikeLike
Thank you Victoria
LikeLike
Hi Victoria,
Im trying to figure out the check numbers for my Purchasing Payment history transactions. I found pretty much everything that i need in PM30200 table however the check no/check book id is blank for all the transactions in the table. Is there any ither way i could find the check number. I appreciate your time and help.
Thanks,
Uma.
LikeLike
Hi Uma,
The check number should be the DOCNUMBR for payments in the PM30200 table.
-Victoria
LikeLike
Hi Victoria,
I thought docnumber is the invoice number. If DocNumber is the check number what would be the invoice number? 😦
LikeLike
Uma,
In PM30200 you have one record for each transaction. An invoice is one transaction (DOCTYPE = 1) a payment is another transaction (DOCTYPE = 6). Even if you enter them at the same time, GP will create 2 separate transactions. For an invoice, the DOCNUMBR field will hold the invoice number. For a payment with a check, the DOCNUMBR field will hold the check number.
-Victoria
LikeLike
Oh! ok So get check for the invoice do i have to join PM30200 to PM20000 on Vouchernumber?
LikeLike
Uma,
Why not just use my Apply Information view?
-Victoria
LikeLike
Hi Victoria,
I found a few records in our PM30200 where the DOCTYPE is 6 but the DOCNUMBR is not numeric (e.g. 12345A or 12345.). Is there no type restriction on the check number field? Thanks!
LikeLike
Hi Zalmy,
Check numbers in GP are strings, just like all other document numbers.
-Victoria
LikeLike
Hi Victoria,
Is there any other table than PM30200 where I can find PL Payment History information. Thanks for your time.
Thanks,
Chev.
LikeLike
Chev,
Can you please explain what you mean by “PL Payment History”? It’s not a term that I recognize.
-Victoria
LikeLike
hI Victoria,
sorry i must have been more specific. Im trying to find payment history for vendors with details like Invoice number, Check number, Payment date, Payment amount, Cleared status, Accounting year, period etc.
Thanks,
Chev.
LikeLike
Chev,
Not sure if I have a view with everything you are looking for, but please take a look at the list of payables views I have posted here and see if one of them will get close.
-Victoria
LikeLike
Hi Victoria,
Where would I find out if a vendor is tax exempt or not? -Thanks,
LikeLike
Joel,
You should be able to use the TEN99TYPE type field in the PM00200 table. Here are the values:
1 – Not a 1099 Vendor
2 – Divident
3 – Interest
4 – Miscellaneous
5 – Withholding
-Victoria
LikeLike
Thanks so much.
LikeLike
Are these 1099 Type values/descriptions stored in a SQL table? If so, which one?
LikeLike
Noah,
Are you referring to the values for the TEN99TYPE field? If so, I do not believe they are stored in a table anywhere.
-Victoria
LikeLike
Hi Victoria,
I have found your resources of great use. Thanks.
I am new to GP and involved with a data migration from GP10.0. I am attempting to identify the table/s where the account balance per vendor is recorded – historical if available or if not available current balance would be sufficient. Are you able to provide any assistance with table names/SQL to obtain this?
Thanks
Michelle
LikeLike
Hi Michelle,
GP does not store historical vendor balances. For the current balance you can look at table PM00201.
-Victoria
LikeLike
Great thanks.
LikeLike
Thanks so much for the resources you provide.
Is there anyway to retrieve the original invoice paid from a voided check? It appears once the check is voiced and invoice is unapplied the record is no longer in PM30300.
I need to be able to report on what the original invoice was at the time of the void.
Thanks
Frank
LikeLike
Frank,
This information is not stored anywhere in GP. So unless you create something custom to track it, there is no way to report on it.
-Victoria
LikeLike
I have a new accounting person using SBF and she is pointing out some shortcomings that I hope I can get around one way or another. Using an older version 9.00.0115
When paying a vendor you can select the invoices to pay but instead of showing the vendor invoice # is shows the voucher #. Well that isn’t helpful in any way. Can it be changed to show the vendor invoice #? My aged payables lists are incorrect as well. I select date ranges and it gives just odd results. I do see in the pm20000 table that a lot of valid invoices have a curtrxam value of 0. Some do and some don’t but as far as I can tell none of them should have a value of 0 and maybe why they are not showing on reports. Any idea why this might be happening?
Should I update to the latest in the 9.x series to fix some oddities we are seeing?
LikeLike
Russ,
The voucher showing instead of the vendor invoice number is something I remember from one of our SBF customers that we converted to Dynamics GP. I do not think a service pack will address this. And I am not aware of easy way to “change” this. I am not sure if anyone is offering customizations for SBF anymore, since it was discontinued a long time ago. This is definitely not the case in Dynamics GP, there you can select invoices using the invoice numbers.
As to your other question, there may be data that didn’t get posted properly, difficult to day without looking at it in a lot more detail. Again, I would not expect a service pack to fix it, this is more of a data issue. It’s been years since I looked at SBF, I don’t have it installed anywhere or have any customers using it anymore, so I cannot check, but if there is a check links option in there, I would try that to see if it fixes anything (make sure to make a backup first). Also, consider running the ‘historical aged trial balance’ instead, that may have better results.
-Victoria
LikeLike
Hi Victoria,
Good day to you, We received an error “Unhandled database exception:
A save operation on table ‘PM_Distribution_HIST’ has created a duplicate key”
After we trigger Bacth Recovery process.
Thanks to your site i found idea on how to trace it, but this time it has different outcome
as i search the table PM_Distribution_HIST(PM30600) no transaction found. Am i in the
right track? Any suggestions from you would be highly appreciated…
Thanks and God bless
RolanVH
LikeLike
Hi Rolan,
This kind of error may indicate that you have a transaction that is in multiple tables at the same time. This article by David Musgrave might help.
-Victoria
LikeLike
Hi Victoria
– so much thanks for quick response and links, God bless.
-RolanVH
LikeLike
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.
LikeLike
Ryan,
I believe you can use SmartList for this – under Purchasing > Payables Transactions add a column called Payment Entry Type.
-Victoria
LikeLike
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
LikeLike
Ryan,
Why would the data be incorrect? Are you pushing it directly into the tables? Or entering it incorrectly?
-Victoria
LikeLike
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
LikeLike
Ryan,
If the data is not entered anywhere, how can you report on it?
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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?
LikeLike
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
LikeLike
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…
LikeLike
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
LikeLike
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,
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Myrna,
Does this blog post help? https://victoriayudin.com/2012/10/11/voiding-a-partially-applied-payables-transaction-in-dynamics-gp/
-Victoria
LikeLike
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?
LikeLike
I managed to get it solved. I had to reset the record in SY00500 and it worked.
LikeLike
Billie Dee,
Thanks for the update, glad you got it resolved.
-Victoria
LikeLike
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,
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Victoria,
It shows unposted, but does not appear in a batch available to post.
LikeLike
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
LikeLike
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.
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
Hi Zafar,
That is in the SY01200 table. The field is INETINFO.
-Victoria
LikeLike
Hi Victoria,
Thank you so much. You are the best.
LikeLike
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!
LikeLike
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
LikeLike
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?
LikeLike
Bill,
That is also in the PM20000 table in the PYMTRMID field. Is that what you’re looking for?
-Victoria
LikeLike
I do believe this is it! Thanks a bunch!
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
DOCTYPE 7 is a payables scheduled payment. (Transactions…Payables…Scheduled Payments)
Bill
LikeLike
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
LikeLike
Rhon,
PM30600 holds distributions for historical transactions. PM10100 holds distributions for work and open transactions.
-Victoria
LikeLike
thank you so much victoria.
LikeLike
Your site has been immensely helpful for me and I wanted to thank you for that. I’ve also just purchased your book!
LikeLike
Hi Bill,
Thank you so much!
-Victoria
LikeLike
Victoria,
Thank you so much. This completely makes sense. now.
Cheers ~ Don
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Hi Ben,
Siva Venkataraman has a number of HATB scripts posted on his blog, here is one for the Payables HATB.
-Victoria
LikeLike
Quite possibly the best news I’ve received all week. THANK YOU, Victoria!
LikeLike
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!
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
Wise advice, I thank you for your support. We will do as you indicated.
LikeLike
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!
LikeLike
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
LikeLike
Much obliged..
LikeLike
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.
LikeLike
Sorry “Date Invoice Paid Off” (DINVPDOF) is in PM30200 not PM30300.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
Wow nice! That worked perfectly for me.
Much appreciated Victoria.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
Hi Victoria,
In which table can I find creditor e-mail addresses in GP10?
LikeLike
Khona,
If you are entering this on the Internet Information window, it is stored in the SY01200 table.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Wow, Okay, I overlooked that. How can I get the description of the box?
LikeLike
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
LikeLike
That is weird. But your suggestion does indeed work. Thanks.
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
Thanks for your help. It looks like someone was making changes to vendor addresses and that was clearing the shipment method.
Mike
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
ok – thank you so much Victoria. 🙂
LikeLike
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
LikeLike
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
LikeLike
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…
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
Andrew, have you tried running reconcile and check links?
-Victoria
LikeLike
I tried that first and it didn’t help. I ran Reconcile on Sales, Purchasing and Inventory, and checked links on the same modules.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
Thank you so much.
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Sweet. I’ll give it a whirl. Thanks for the quick response.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
hi Victoria
same error……….The Payables Transaction Entry window opens but cannot edit the record.
Thanks
Colin
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Ben,
Thanks for the compliment. 🙂 Please let me know how it turns out, I am curious to find out what the issue was.
-Victoria
LikeLike
Hi Victoria
is there anyway to know which user created a vendor or customer?
Thanks a lot
LikeLike
FAC,
As far as I can see, GP does not store this information.
-Victoria
LikeLike
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.
LikeLike
how is REMSUBTO or OREMSUBT computed in table POP10100?
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Sruthi,
Is this related to your vendor aging report question? If not, please take a look at my Bank Rec tables page: https://victoriayudin.com/gp-reports/bank-rec-tables/.
-Victoria
LikeLike
Hi Victoria,
Can you please tell me the query for vendor aging report .
Thanks
Sruthi
LikeLike
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: https://victoriayudin.com/gp-reports/.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Yes Victoria. Are there any existing GP reports which prints the over recieved items?
Regards,
Nisha
LikeLike
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
LikeLike
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.
LikeLike
Sri,
I am not a developer, so I do not do this kind of work myself.
-Victoria
LikeLike
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
LikeLike
Hi Sri,
Both Excel and SSRS are viable options for your report, however drilling down to GP would require custom coding.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thank you for your prompt response. I greatly appreciate your help.
LikeLike
Hi Victoria,
where can i find the vendor bank wire information? Thanks for you help.
Aaron
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
Jerri,
Glad you were able to figure it out! And thanks for the kind words. 🙂
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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!
LikeLike
Jerri,
Thanks for the update – glad you found what you needed! And thank you very much for your kind words.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Eric,
Thank you very much!
-Victoria
LikeLike
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
LikeLike
Shabnam,
I don’t believe there is an existing one for POP returns. You would need to create your own.
-Victoria
LikeLike
Really nice resource list Victoria, thanks for posting…
LikeLike
Thanks Victoria,
You are always helpful!
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thank you very much Victoria for your prompt response! That’s very detailed and helpful!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Jim,
There should be an automated solution for this, take a look at Mariano Gomez’s blog post about automated solutions.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
Victoria,
Thank you very much…….
🙂
-Jim
LikeLike
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
LikeLike
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
LikeLike
Thank you very much…..
-Jim
LikeLike
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
LikeLike
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
LikeLike
Hello,
can someone tell me please how do I find the aging table in payable like we have rm00103 for Receivable?
Thank you,
-Jim
LikeLike
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
LikeLike
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…………..
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Bab,
The PM30200 table hold the actual historical transactions. The PM30300 table holds apply information about historical transactions. Hope that helps to clarify.
-Victoria
LikeLike
I am sorry, I mean PM30300.
LikeLike
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?
LikeLike
This will helpful for technical developers of Great Plains
Thank you very much!!!!!!!!!
LikeLike
Victoria, what is the difference in the PM00201 and the PM00202 tables? I have several entries that are not matching…Thanks
LikeLike
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
LikeLike