Where can a payables invoice voucher live?

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (8)

Answers (3)

Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

...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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

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]

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Copy link

Link copied!

Thanks Donald, that worked very well.... and much better than hard-coding table names in my app!

Was this reply helpful? Yes No
Verified answer

Don Wisch 660 on at

Like (0)

Report
Copy link

Link copied!

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;

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (1)

Report
Copy link

Link copied!
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:

PM10100

PM10200

PM10100

PM10400

PM10201

PM10300

PM10500

PM10000

For that, You might be missing the following (in case it is open)

PM00400

PM10100

PM10500

PM80600

PM80700

TX30000

AAG20000 (in case you have analytical accounting activated)

GL20000 (in case it is posted to the general ledger)

For the historical, you might be missing the following:

PM00400

PM30600

PM30700

PM80300

PM80600

PM80700

TX30000

AAG20000 (in case you have analytical accounting activated)

GL20000 (in case it is posted to the general ledger)

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,

Was this reply helpful? Yes No