Hello,
I need to confirm if a payables invoice/voucher exists in Dynamics GP. Which tables should I check? (the following are the ones that I found but I would like to confirm if there are any others)
PM10000
PM10200
PM20000
PM30200
PM50503
PM50504
MC020103
others???
Thanks!
*This post is locked for comments
Not all of them. If you are looking for the transaction tables, then 1 will be for the work (un-posted), 2 for the open (posted), and 3 for the history (applied or voided)
When the transaction is voided or fully applied (paid), it will move from the work tables (PM2xxx)into the history (PM3xxx)
This is why I recommended reading the link "Date flow and tables names" for leslie vail, it provides an illustrative and comprehensive details for your case.
Please never hesitate to share any further inquiries,
...and, once the voucher is processed and paid, will it still show in any of the PM tables or is it then moved out of PM altogether?
Mahmoud thank you for that clarification!
I am only interested in a vouchers imported into the PM module and I assume that the voucher number returned by GetPMNextVoucherNumber() will be unique, at least within the PM module.
With that said, should I then only consider tables starting with "PM"?
Thank you!
That is a good suggestion, although, I would like to throw a single piece of information to be taken into consideration.
For your case, looking for the payable invoice number is not always limited to a "Voucher Number". For instance, the primary table that you excluded (PM00400, the PM Key master) holds the voucher number under the field "CNTRLNUM" or the control number.
For the script, it is definitely great provided that Dynamics GP tables reserve the "voucher number" just for the payable module, invoice number. Although, if you run the script above it will retrieve the following:
table_name schema_name column_name
CAM10000 dbo VCHRNMBR
CAM10100 dbo VCHRNMBR
CAM30000 dbo VCHRNMBR
CAM30100 dbo VCHRNMBR
MC020103 dbo VCHRNMBR
MC020105 dbo VCHRNMBR
PA50100 dbo VCHRNMBR
PA50103 dbo VCHRNMBR
PA50105 dbo VCHRNMBR
PM10000 dbo VCHNUMWK
PM10000 dbo VCHRNMBR
PM10100 dbo VCHRNMBR
PM10200 dbo VCHRNMBR
PM10201 dbo VCHRNMBR
PM10300 dbo VCHRNMBR
PM10400 dbo VCHRNMBR
PM10500 dbo VCHRNMBR
PM10600 dbo VCHRNMBR
PM10801 dbo VCHRNMBR
PM10900 dbo VCHRNMBR
PM10901 dbo VCHRNMBR
PM10902 dbo VCHRNMBR
PM20000 dbo VCHRNMBR
PM20100 dbo VCHRNMBR
PM20200 dbo VCHRNMBR
PM20401 dbo VCHRNMBR
PM30200 dbo VCHRNMBR
PM30300 dbo VCHRNMBR
PM30600 dbo VCHRNMBR
PM30700 dbo VCHRNMBR
PM30800 dbo VCHRNMBR
PM50100 dbo VCHRNMBR
PM50503 dbo VCHRNMBR
PM50504 dbo VCHRNMBR
PM80200 dbo VCHRNMBR
PM80300 dbo VCHRNMBR
PM80500 dbo VCHRNMBR
PM80600 dbo VCHRNMBR
PM80700 dbo VCHRNMBR
PM80800 dbo VCHRNMBR
PM80900 dbo VCHRNMBR
PM80905 dbo VCHRNMBR
POP10300 dbo VCHRNMBR
POP30300 dbo VCHRNMBR
PP000100 dbo VCHRNMBR
PP000101 dbo VCHRNMBR
PP100100 dbo VCHRNMBR
PP100101 dbo VCHRNMBR
SVC00203 dbo VCHRNMBR
SVC05600 dbo VCHNUMWK
SVC05601 dbo VCHNUMWK
SVC30203 dbo VCHRNMBR
SVC35600 dbo VCHNUMWK
SVC35601 dbo VCHNUMWK
As shown above, CAM is for the control account management module, POP for the purchase order processing module, SVC for the service module, PP for the revenue and expense deferral ...etc. Additionally, the PM tables retrieved are not limited to the transaction table, but some setup table and master tables as well.
This is just a quick note for you to consider.
Please never hesitate to share any further inquiries,
| [CNTRLNUM] |
Thanks Donald, that worked very well.... and much better than hard-coding table names in my app!
Voucher number can be found by executing the following SQL statement against any GP company DB. The results you get will vary on what modules you have installed. Please note that voucher number is used as 2 different names (VCHNUMWK or VCHRNMBR) and this is determined by the location of the document.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name IN('VCHNUMWK','VCHRNMBR')
ORDER BY schema_name, table_name;
Thank you for your reply!
I cannot use the stored procedure since (1) it is too generic (ie. does not target a specific field) and (2) needs to be installed which is not an option for me.
Regarding the tables, I had to exclude...
PM00400
TX30000
GL20000
...since they do not include the VCHRNMBR field and also had to exclude...
AAG20000
...since the table does not always exist. However, I assume that, even with those tables excluded, a voucher would still exist in one or more of the remaining tables below:
PM10000
PM1010
PM10200
PM1050
PM20000
PM30200
PM3060
PM3070
PM5050
PM5050
PM8030
PM8060
PM8070
MC020103
Again, I do NOT need to find all occurrences of the voucher - Rather, I simply need to know if it exists or not.
Thanks!
Initially, it depends on the status of the transaction. Whether it is saved, open or historical.
If the transaction is saved and not yet posted, consider the following:
For that, You might be missing the following (in case it is open)
For the historical, you might be missing the following:
Furthermore, I would suggest considering the spsearchalldb stored procedure, which searches all the database for a specific field value. So you might consider searching all your company data base for the voucher number and see the specific results which you will get. Here is a link for the stored procedure to be downloaded .
Additionally, to enrich your knowledge about the tables flow of payable transaction, consider this article by Leslie Vail Data Flow and Tables Name .
Your feedback is highly appreciated,
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
