What GL Table houses the voucher number in Great Plains?

0
August 17, 2011

Can someone tell me what GL Table houses the AP voucher number in Great Plains?  I need to produce a breakdown by voucher as to what amounts hit what GL account.  SInce the voucher has been paid I cannot seem to acomplish this.

Comments See all(0)

Add comment
0
August 17, 2011

Hi,

It sounds like you need to query paid vouchers.  But just in case you need to query others, here are all of the locations for vouchers:

Unposted vouchers:  PM10000 (PM Transaction Work)

Posted "Open" (unpaid or partially paid) vouchers:  PM20000  (PM Transaction Open)

"History" vouchers (fully paid):  PM30200  (PM Transaction History)

These tables will also contain credits and payments, not just vouchers, so you may need to filter based on DOCTYPE.  (1=Invoice, 4=Return, 5=Credit memo, 6=Check, etc.)

Since you mention you need to know what vouchers hit what GL accounts, you will probably want to join against the associated PM distributions table:

Unposted voucher distributions:  PM10100

Open voucher distributions:  PM20200

History voucher distributions:  PM30600

Attached are two queries that I've used to query vouchers and their associated distributions.  The first is just separate queries for Open and History (adjust and tweak as needed), and I'll separately post a stored procedure that I wrote that does Unions so they all return in a single result set.

Hope that helps, and if you need any other info, just let me know.

Thanks,

Steve Endow

Dynamics GP Certified Trainer

Dynamics GP Certified Business Solution Professional

/*OPEN AP FILES*/

SELECT TOP 10 * FROM PM20000

--Remove "TOP 10" for real query
SELECT TOP 10 'PM20000' AS TableName, 'Open' AS DocumentStatus, A.VENDORID, B.BACHNUMB, B.DOCNUMBR, B.PSTGDATE, B.DOCDATE, A.VCHRNMBR, A.TRXSORCE, A.DEBITAMT, 
A.CRDTAMNT, RTRIM(C.ACTNUMST) AS ACTNUMST, RTRIM(D.ACTDESCR) AS ACTDESCR, C.ACTNUMBR_3, B.DOCTYPE, A.DISTTYPE
FROM PM10100 A
JOIN PM20000 B ON B.VCHRNMBR = A.VCHRNMBR
JOIN GL00105 C ON C.ACTINDX = A.DSTINDX
JOIN GL00100 D ON D.ACTINDX = C.ACTINDX
WHERE A.VENDORID = '10-04004'
AND B.PSTGDATE > '2005-05-01'
ORDER BY B.DOCDATE, A.VCHRNMBR




/*Historical AP Files*/

SELECT TOP 10 * FROM PM30200

SELECT TOP 10 'PM30200' AS TableName, 'History' AS DocumentStatus, A.VENDORID, B.BACHNUMB, B.DOCNUMBR, B.PSTGDATE, B.DINVPDOF, B.DOCDATE, A.VCHRNMBR, A.TRXSORCE, A.DEBITAMT, 
A.CRDTAMNT, RTRIM(C.ACTNUMST) AS ACTNUMST, RTRIM(D.ACTDESCR), C.ACTNUMBR_3, B.DOCTYPE, A.DISTTYPE
FROM PM30600 A
JOIN PM30200 B ON B.VCHRNMBR = A.VCHRNMBR
JOIN GL00105 C ON C.ACTINDX = A.DSTINDX
JOIN GL00100 D ON D.ACTINDX = C.ACTINDX
WHERE A.VENDORID = '10-04004'
--AND B.PSTGDATE < '2005-09-01'
AND B.DOCTYPE <> 6 --Not a check
ORDER BY B.DOCDATE, A.VCHRNMBR
0
August 17, 2011

Try looking at these tables and columns.

As a future reference, a quick way to get in the general area of the possible tables is to use a query like this:






SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE '%VOUCH%'

ORDER BY TABLE_NAME, COLUMN_NAME






TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME
dbo FixedAssetsPurchase Voucher / Receipt Number
dbo HistoryRTVLines Voucher Number (WORK)
dbo HistoryRTVLines Voucher Number Invoice
dbo HistoryRTVLines Voucher Number Reimbursement
dbo HistoryRTVs Voucher Number (WORK)
dbo HistoryRTVs Voucher Number Invoice
dbo HistoryRTVs Voucher Number Reimbursement
dbo ME318302    ME_Select_by_Voucher_Che
dbo PA00002 paVoucherDescrPrefix
dbo PayablesApply_Open  Apply From Voucher Number
dbo PayablesApply_Open  Apply To Voucher Number
dbo PayablesTransactions    Voucher Number
dbo PayablesTransactions    Voucher Number Corrected
dbo PM20400 ORIG_VOUCHER_NUM
dbo PM30401 ORIG_VOUCHER_NUM
dbo PMV0100 ORIG_VOUCHER_NUM
dbo ReceivingsTransactions  Voucher Number
dbo RTVLines    Voucher Number (WORK)
dbo RTVLines    Voucher Number Invoice
dbo RTVLines    Voucher Number Reimbursement
dbo RTVs    Voucher Number (WORK)
dbo RTVs    Voucher Number Invoice
dbo RTVs    Voucher Number Reimbursement
dbo SVC05600    Voucher_Number_Invoice
dbo SVC05600    Voucher_Number_Reimburse
dbo SVC05601    Voucher_Number_Invoice
dbo SVC05601    Voucher_Number_Reimburse
dbo SVC35600    Voucher_Number_Invoice
dbo SVC35600    Voucher_Number_Reimburse
dbo SVC35601    Voucher_Number_Invoice
dbo SVC35601    Voucher_Number_Reimburse
0
August 17, 2011

And here is a query you can turn into a stored proc for returning all of the voucher info in a single result set.  

Hope it helps.

Thanks,

Steve Endow

Dynamics GP Certified Trainer

Dynamics GP Certified Business Solution Professional

/* WORK AP FILES */
SELECT TOP 10 'PM10000' AS TableName, 'Work' AS DocumentStatus, A.VENDORID, B.BACHNUMB, B.DOCNUMBR, B.PSTGDATE, B.DOCDATE, A.VCHRNMBR, A.TRXSORCE, A.DEBITAMT, 
A.CRDTAMNT, RTRIM(C.ACTNUMST) AS ACTNUMST, RTRIM(D.ACTDESCR) AS ACTDESCR, C.ACTNUMBR_3, B.DOCTYPE, A.DISTTYPE
FROM PM10100 A
JOIN PM10000 B ON B.VCHRNMBR = A.VCHRNMBR
JOIN GL00105 C ON C.ACTINDX = A.DSTINDX
JOIN GL00100 D ON D.ACTINDX = C.ACTINDX
WHERE B.DOCTYPE <> 6
AND A.VENDORID = '1TEST'
AND B.PSTGDATE BETWEEN '2007-01-01' AND '2017-12-31'
--ORDER BY B.DOCDATE, A.VCHRNMBR

UNION

/*OPEN AP FILES*/
SELECT TOP 5 'PM20000' AS TableName, 'Open' AS DocumentStatus, A.VENDORID, B.BACHNUMB, B.DOCNUMBR, B.PSTGDATE, B.DOCDATE, A.VCHRNMBR, A.TRXSORCE, A.DEBITAMT, 
A.CRDTAMNT, RTRIM(C.ACTNUMST) AS ACTNUMST, RTRIM(D.ACTDESCR) AS ACTDESCR, C.ACTNUMBR_3, B.DOCTYPE, A.DISTTYPE
FROM PM10100 A
JOIN PM20000 B ON B.VCHRNMBR = A.VCHRNMBR
JOIN GL00105 C ON C.ACTINDX = A.DSTINDX
JOIN GL00100 D ON D.ACTINDX = C.ACTINDX
WHERE B.DOCTYPE <> 6
AND A.VENDORID = '1TEST'
AND B.PSTGDATE BETWEEN '2007-01-01' AND '2017-12-31'
--ORDER BY B.DOCDATE, A.VCHRNMBR

UNION

/*Historical AP Files*/
SELECT TOP 5 'PM30200' AS TableName, 'History' AS DocumentStatus, A.VENDORID, B.BACHNUMB, B.DOCNUMBR, B.PSTGDATE, B.DOCDATE, A.VCHRNMBR, A.TRXSORCE, A.DEBITAMT, 
A.CRDTAMNT, RTRIM(C.ACTNUMST) AS ACTNUMST, RTRIM(D.ACTDESCR) AS ACTDESCR, C.ACTNUMBR_3, B.DOCTYPE, A.DISTTYPE
FROM PM30600 A
JOIN PM30200 B ON B.VCHRNMBR = A.VCHRNMBR
JOIN GL00105 C ON C.ACTINDX = A.DSTINDX
JOIN GL00100 D ON D.ACTINDX = C.ACTINDX
WHERE B.DOCTYPE <> 6
AND A.VENDORID = '1TEST'
AND B.PSTGDATE BETWEEN '2007-01-01' AND '2017-12-31'
--ORDER BY B.DOCDATE, A.VCHRNMBR
0
August 17, 2011

I proof and open a related question if I have trouble.

Thanks!

0
August 17, 2011

Steve,

Nice to see we have a GP professional actively on EE.  

Many of us have been catching GP as a flyby.  I'm just a DBA with familiarity with it. I'm not in it enough to know all the in's and out's.

Glad to have you on board.

Related Questions

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016

report values not showing up.

I have a crystal report which I pass a DataSet to using VB.NET. The report was working fine, but then I make some changes to the import query and now nothing shows up in the crystal report. Crystal do Read More

Views

1k

Votes

0

Answers

12

September 06, 2005

Event log doesn't overwrite as needed in SP4

Hello All, After upgrading from SP3 to SP4 on several Windows 2000 PRO machines everything looked fine, but from time to time applications can&#39;t write to the event log. When I try to view the appl Read More

Views

1k

Votes

0

Answers

9

May 11, 2003

"Failed to self-register XYZ.dll"

Hi there, I wrote a OLE-automation-server DLL in VB4.0. I use Installshield Express to install it as part of my program on the target computers. Now on some computers I get the message &quot;Failed to Read More

Views

1k

Votes

0

Answers

2

November 08, 1998

Please help understand these notes on image processing

Can someone please help me understand these lecture notes... On the right of the page; What does &quot;normalised by one notion of the area of a pixel&quot; mean? On the first formula for A, Is that a Read More

Views

1k

Votes

0

Answers

3

February 05, 2009

Can someone please explain this paragram on the chain rule in image processing

Please see the screenshot, How is 3133030 got from 10103322? Read More

Views

1k

Votes

0

Answers

1

January 05, 2009

AD on 2003

We have created an AD Domain on Windows 2000 Server with no problems. We just created an AD Domain on Windows 2003 and we&#39;re getting some weird problems. Both of these domains are behind firewalls Read More

Views

1k

Votes

0

Answers

15

May 11, 2003

ADDT ASP Upload Error " Type mismatch: 'tNG_isFileInsideBaseFolder' "

I am trying to create a simple insert record and upload image function on an ASP page built using Adobe Dreamweaver Developer Toolkit. I have done this many times before with no problem, however, i ha Read More

Views

1k

Votes

0

Answers

0

November 02, 2008

MYSQL Select query with custom ORDER BY

Hi, is it possible to customize the order of the returned rows in mysql? Example: I have a Table with a column &quot;name&quot;, now I want to have all entries ordered by name, but I want the entries Read More

Views

1k

Votes

0

Answers

5

July 02, 2010

Folder Redirection in Server 2003

We have a Windows Server 2003 Standard Ed. Is there a way to redirect the users my documents to a folder that has already been created on the server? Read More

Views

935

Votes

0

Answers

2

July 05, 2007

Partiton magic  version 8.0 having error 1523 while executing batch

I followed the partition magic 8.0 wizard to partition my hard disk while rebooting &#160;I came across this problem &#160;&quot;error 1523 while executing batch&quot; What can I do to solve this prob Read More

Views

860

Votes

0

Answers

2

May 11, 2003

Please explain this paragraph in image processing (screenshot attached)

Two questions; How is the equation (10.1-15) formed? &quot;are isotropic for rotation increments of 90 degrees and 45 degrees respectively&quot; What does this mean Read More

Views

658

Votes

0

Answers

7

January 05, 2009

xp_cmdshell with net use

Hi, when I use &#39;net use \192.168.0.1\c$ password /user:username&#39; at the command prompt, it works fine. &#160;But if i try to use it in sql with &quot; exec master..xp_cmdshell &#39;net use \19 Read More

Views

615

Votes

0

Answers

5

April 11, 2003

hp ux11

i have a hp ux11 server. and i edited the /etc/resolv.conf to use my win2003 server dns server. That went fine, however, I would like to go the other way and add an entry on my win2003 dns to resolve Read More

Views

357

Votes

0

Answers

3

October 12, 2009

Using an application over a LAN

I have written an application that needs to be used on a LAN. &#160;The application setup wizard takes care of registering everything on a single PC, but how do you go about writing a setup program th Read More

Views

314

Votes

0

Answers

9

August 07, 1998

Need subnet of 12 IPs.

Please, Can someone help me to create a subnet consisting of 12 IP&#39;s. Thanks.. Read More

Views

311

Votes

0

Answers

5

April 11, 2003

Drill down in a cross-tabl report

Hello, Would some experts to show me if it is possible to do drill-up or drill-down (or both) in a cross-tab report? I would also like to know if it is possible to control what fields to be displayed/ Read More

Views

253

Votes

0

Answers

0

September 06, 2005

Using RDO, Enterprise V Professional

We would like to use RDO on our project made up of 4 developers. Do we need to have a copy of Enterprise for each developer or is it possible to use Professional for those developers not writing the R Read More

Views

300

Votes

0

Answers

6

January 07, 1998

Can't Edit IIS Metabase.xml

We do this on every windows 2003 server and never had an issue. We cannot edit the IIS Metabase.xml We have stopped IIS and made change click save, no problem but it does not take the new information. Read More

Views

308

Votes

0

Answers

0

July 05, 2007

copy directorires - HP UNIX

Let&#39;s say my source file is /A/B/C/D/file1, and mytarget directory is /E/F/G, I want the result should look like this. /E/F/G/A/B/C/D/file1 I am using rsync to copy the files older than x no of da Read More

Views

318

Votes

0

Answers

14

September 12, 2009