<div>
Unless Great Plains has changed drastically in the last couple of years, you should be starting with the general ledger table, which is GL20000 I believe. That will give you every single financial transaction, both debits and credits. 
 
From there, you can link to the AP subledger (might be PM20000 ?), also the statement of accounts (GL1????). 
 
I'm doing this from memory, so I can't look anything up for you, but you will definitely need to start with the GL.
</div>

<div>
I believe this code will give you what you're looking for: <a href="http://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/" rel="ugc">http://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/</a> 
 
Let me know if it is missing any of the fields you're looking for and you need help finding them. 
 
Also, here is a list of the common tables for AP transactions in Dynamics GP: 
<a href="http://victoriayudin.com/gp-tables/pm-tables/" rel="ugc">http://victoriayudin.com/gp-tables/pm-tables/</a>
</div>

<div>
Thank you victoria, I think this is mostly what I am looking for. &nbsp;I think I also need a few more fields: 
 
1099Amount 
1099type(royalties, rents, interest, etc) 
Invoice tax amounts 
Financial Period - I think I can derive this from the Document Date, can't I? 
Discount Amount 
 
Thanks
</div>

<div>
Victoria, 
 
The query isn't working as is. &nbsp;Apparently &nbsp;TEN99Type and Ten99BoxNumber don't exist on all of those table. &nbsp;I'm going to attempt to fix up. 
 
Msg 207, Level 16, State 1, Line 2 
Invalid column name 'TEN99TYPE'. 
Msg 207, Level 16, State 1, Line 2 
Invalid column name 'TEN99BOXNUMBER'. 
 
I'm not sure if it's my GP version that's causing it. 
 
B
</div>

<div>
I think i fixed the issues with the query: 
 

<pre><code class="code-100 nolinks" id="code-20-39205037-1">SELECT D.VENDORID Vendor_ID,
 N.VENDNAME Vendor_Name,
 K.DOCNUMBR Document_Number,
 D.PSTGDATE GL_Posting_Date,
 K.DOCDATE Document_Date,
 case K.DOCTYPE
 when 1 then 'Invoice'
 when 2 then 'Finance Charge'
 when 3 then 'Misc Charge'
 when 4 then 'Return'
 when 5 then 'Credit Memo'
 when 6 then 'Payment'
 end Document_Type,
 G.ACTNUMST Account_Number,
 A.ACTDESCR Account_Name,
 case D.DISTTYPE
 when 1 then 'Cash'
 when 2 then 'Payable'
 when 3 then 'Discount Available'
 when 4 then 'Discount Taken'
 when 5 then 'Finance Charge'
 when 6 then 'Purchase'
 when 7 then 'Trade Disc.'
 when 8 then 'Misc. Charge'
 when 9 then 'Freight'
 when 10 then 'Taxes'
 when 11 then 'Writeoffs'
 when 12 then 'Other'
 when 13 then 'GST Disc'
 when 14 then 'PPS Amount'
 when 16 then 'Round'
 when 17 then 'Realized Gain'
 when 18 then 'Realized Loss'
 when 19 then 'Due To'
 when 20 then 'Due From'
 end Distribution_Type,
 D.DEBITAMT Debit_Amount,
 D.CRDTAMNT Credit_Amount,
 D.DistRef Distribution_Reference,
 T.BACHNUMB Batch_ID,
 T.TRXDSCRN Trx_Description,
 T.STAT Trx_Status,
 D.VCHRNMBR Voucher_Number,
 D.CURNCYID Currency_ID,
 coalesce(D.XCHGRATE,0) Exchange_Rate,
 D.ORDBTAMT Originating_Debit_Amount,
 D.ORCRDAMT Originating_Credit_Amount,
 case T.VOIDED
 when 0 then 'No'
 when 1 then 'Yes'
 end Voided,
 T.PORDNMBR PO_Number,

-- **********added 1099 columns**************
	T.TEN99AMNT [1099 Amount],
	case T.TEN99TYPE 
		 when 1 then 'Not a 1099 Vendor'
		 when 2 then 'Dividend'
		 when 3 then 'Interest'
		 when 4 then 'Miscellaneous'
		 else ''
		 end [1099 Type], 
	T.TEN99BOXNUMBER [1099 Box Number]
 
FROM
-- all open trx distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
 PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
 FROM PM10100
UNION ALL
-- all historical trx distributions
 SELECT P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
 P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
 FROM PM30600 P
 LEFT OUTER JOIN -- historical exchange rate
 MC020103 M
 ON P.VCHRNMBR = M.VCHRNMBR
 AND P.DOCTYPE = M.DOCTYPE) D
 
-- add document number and type
LEFT OUTER JOIN
 PM00400 K
 ON D.VCHRNMBR = K.CNTRLNUM
 AND D.CNTRLTYP = K.CNTRLTYP
 
--add GL account number
LEFT OUTER JOIN
 GL00105 G
 ON D.DSTINDX = G.ACTINDX
 
--add status, batch and trx description
-- **********added 1099 columns to all tables in query below**************
LEFT OUTER JOIN
 (SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
 STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
 FROM PM10000
 UNION
 SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
 STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
 FROM PM20000
 UNION
 SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
 STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
 FROM PM30200
 UNION
 SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
 STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
 FROM PM10300
 UNION
 SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN, 
 STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
 FROM PM10400) T
 ON T.VCHRNMBR = D.VCHRNMBR
 AND T.CNTRLTYP = D.CNTRLTYP
 
--add GL account name
LEFT OUTER JOIN
 GL00100 A
 ON A.ACTINDX = D.DSTINDX
 
--add vendor name
LEFT OUTER JOIN
 PM00200 N
 ON N.VENDORID = D.VENDORID
 
</code></pre>
</div>

<div>
Ah, sorry, I should have asked what version of GP you're on, those got added fairly recently. If you've got it working, great. If you need additional help, please let me know.
</div>

<div>
We're on version 9. 
 
We only use 1099-Miscellenious so I took a shortcut and assumed that if there was a 1099-amount &gt;&nbsp;0 then it was a type=4. &nbsp;Also, from what i was able to find on the web, the 1099box type is 7 for miscellaneous, so i took the shortcut there as well. &nbsp;Both are added above as CASE statements. &nbsp;Hopefully, you don't see a reason this thinking is flawed. 
 
Thanks.
</div>

<div>
Yep, with version 9 there was only one 1099 option available per vendor. We are on GP 2010 and there you have the option of changing the box number on each transaction if you need to, so that is why I added that detail at the transaction level. I think your change will work just fine if all your vendors are the same type and use the same box number. 
 
The alternative would be to not include the type and box number in that detail query and to get that information from the PM00200 table, which is already linked in.
</div>

<div>
Victoria, &nbsp; 
 
I noticed a few days ago that I'm getting vendors with null values in them. &nbsp;I've been trying to figure out where they are coming from to no avail. &nbsp;I'm thinking it's an outer join issue. &nbsp;Have you run into this with the view/proc that you gave me above? &nbsp;If so, where is the likely place that this is occurring? &nbsp;It appears to happen only for a small handful of vendors(our medical and dental insurance carriers). &nbsp;When I look in the GP app, it seems to show the transactions in question assigned to the proper vendor. 
 
 
Thanks.
</div>

<div>
I've run this on a number of different datasets and am not seeing nulls. Which fields are showing nulls?
</div>

<div>
VendorID is spaces and VendorName is Null. &nbsp;I've changed a little of the data to protect the vendor names and abbreviated the column names so that they'd fit as well as removing some of the columns that may not be relevant(but can repost with all if you think they are relevant). &nbsp;Here is what I see: 
 

<pre><code class="code-1 nolinks" id="code-20-39229803-1">Vendor_ID	Vendor_Name	Doc_Num	GL_Post_Date	Document_Type	Account_Number	Account_Name	Distribution_Type	Debit	Credit	Trx_Status
Shield 	Shield Inc.	Mar-13	3/7/2013	Invoice		000-2000 ACCOUNTS PAYABLE Payable		0	566.1	History
 	NULL		Mar-13	3/7/2013	Invoice		500-8344 EMPL BENE LIFE 	Purchase	413	0	History
 	NULL		Mar-13	3/7/2013	Invoice		700-8344 EMPL BENE LIFE 	Purchase	107.74	0	History
 	NULL		Mar-13	3/7/2013	Invoice		000-1140 RECE DUE FROM 	Purchase	377.09	0	History
National NAT'L LIFE INS Mar-13	3/7/2013	Invoice		000-2000 ACCOUNTS PAYABLE Payable		0	897.83	History
</code></pre>
 
We had thousands of records, but i just grabbed a few, including one before and one after a few of the records in question. &nbsp;This situation seems to be happening with about 4% of the records being returned on from the view. 
 
Thanks,
</div>

<div>
The Nulls are showing up because the vendor ID is coming in as blank. Can you see what these return: 
 

<pre><code class="code-1 nolinks" id="code-20-39229867-1">select * from PM10100 where VENDORID = ''
select * from PM30600 where VENDORID = ''
</code></pre>
</div>

<div>
The first query returned 0 records. 
The second query returned 2769 records. 
 
The view returns 2777 records with null vendors. 
 
What is it that would make the system put these blank values in VendorID in this table for some and not other vendors? &nbsp;70k+ records have vendor numbers. &nbsp;Also, can we somehow join one of the columns here to pick the vendorid back up elsewhere? &nbsp;The screen shows the correct vendor. 
 
Thanks
</div>

<div>
What this means is that for some reason you have 2769 records in the historical distribution table with no vendor ID. I would consider this an issue with your data, as this should not be happening. Are these all really old transactions? Are these imported? 
 
In any case, you can change the code to pull those from the transaction tables to see if that fixes the reporting side of it, at least: 

<pre><code class="code-100 nolinks" id="code-20-39229912-1">SELECT T.VENDORID Vendor_ID,
 N.VENDNAME Vendor_Name,
 K.DOCNUMBR Document_Number,
 D.PSTGDATE GL_Posting_Date,
 K.DOCDATE Document_Date,
 case K.DOCTYPE
 when 1 then 'Invoice'
 when 2 then 'Finance Charge'
 when 3 then 'Misc Charge'
 when 4 then 'Return'
 when 5 then 'Credit Memo'
 when 6 then 'Payment'
 end Document_Type,
 G.ACTNUMST Account_Number,
 A.ACTDESCR Account_Name,
 case D.DISTTYPE
 when 1 then 'Cash'
 when 2 then 'Payable'
 when 3 then 'Discount Available'
 when 4 then 'Discount Taken'
 when 5 then 'Finance Charge'
 when 6 then 'Purchase'
 when 7 then 'Trade Disc.'
 when 8 then 'Misc. Charge'
 when 9 then 'Freight'
 when 10 then 'Taxes'
 when 11 then 'Writeoffs'
 when 12 then 'Other'
 when 13 then 'GST Disc'
 when 14 then 'PPS Amount'
 when 16 then 'Round'
 when 17 then 'Realized Gain'
 when 18 then 'Realized Loss'
 when 19 then 'Due To'
 when 20 then 'Due From'
 end Distribution_Type,
 D.DEBITAMT Debit_Amount,
 D.CRDTAMNT Credit_Amount,
 D.DistRef Distribution_Reference,
 T.BACHNUMB Batch_ID,
 T.TRXDSCRN Trx_Description,
 T.STAT Trx_Status,
 D.VCHRNMBR Voucher_Number,
 D.CURNCYID Currency_ID,
 coalesce(D.XCHGRATE,0) Exchange_Rate,
 D.ORDBTAMT Originating_Debit_Amount,
 D.ORCRDAMT Originating_Credit_Amount,
 case T.VOIDED
 when 0 then 'No'
 when 1 then 'Yes'
 end Voided,
 T.PORDNMBR PO_Number,
	T.TEN99AMNT [1099 Amount],
	case T.TEN99TYPE 
		 when 1 then 'Not a 1099 Vendor'
		 when 2 then 'Dividend'
		 when 3 then 'Interest'
		 when 4 then 'Miscellaneous'
		 else ''
		 end [1099 Type], 
	T.TEN99BOXNUMBER [1099 Box Number]
 
FROM
-- all open trx distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
 PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
 FROM PM10100
UNION ALL
-- all historical trx distributions
 SELECT P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
 P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
 FROM PM30600 P
 LEFT OUTER JOIN -- historical exchange rate
 MC020103 M
 ON P.VCHRNMBR = M.VCHRNMBR
 AND P.DOCTYPE = M.DOCTYPE) D
 
-- add document number and type
LEFT OUTER JOIN
 PM00400 K
 ON D.VCHRNMBR = K.CNTRLNUM
 AND D.CNTRLTYP = K.CNTRLTYP
 
--add GL account number
LEFT OUTER JOIN
 GL00105 G
 ON D.DSTINDX = G.ACTINDX
 
--add status, batch and trx description
LEFT OUTER JOIN
 (SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
 STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
 FROM PM10000
 UNION
 SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
 STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
 FROM PM20000
 UNION
 SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
 STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT &gt; 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
 FROM PM30200
 UNION
 SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1, VENDORID,
 STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
 FROM PM10300
 UNION
 SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN, VENDORID,
 STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
 FROM PM10400) T
 ON T.VCHRNMBR = D.VCHRNMBR
 AND T.CNTRLTYP = D.CNTRLTYP
 
--add GL account name
LEFT OUTER JOIN
 GL00100 A
 ON A.ACTINDX = D.DSTINDX
 
--add vendor name
LEFT OUTER JOIN
 PM00200 N
 ON N.VENDORID = T.VENDORID
</code></pre>
</div>

<div>
I'll try your query, but these are new transactions. &nbsp;In the example above, they are from Mar 7 this year. &nbsp;I have some as far back as 2004, but I also have others that were posted today.
</div>

<div>
I would recommend starting a new question to see if others can recommend some options as to why you have blanks where data should be. You might also want to consider talking to Dynamics GP support about this.
</div>

<div>
I shall do that. &nbsp;You have been very helpful. &nbsp;Thank you.
</div>

<div>
<div class="content wysiwyg-content">
Hi all, 
 
I'm trying to create a query in Great Plains for AP Invoices. &nbsp;I need the results to include the &quot;normal&quot; vendor info(at least the vendorid, but name, etc), the invoice info(like number, entry date,amount, etc), and the G/L accounts to credit and debit. 
 
I have located a few tables that contain some of the data: 
 
pm00200 - Vendor info 
pm00300 - Vendor address 
pm00400 - Seems to be invoice stuff or at least part of it. 
pm30200 - Seems to be paid invoice stuff(i think). 
 
But I don't see anything that necessarily links them together(trxsorce?) and also gets me the Expense and Payable G/L accounts to credit and debit. 
 
Any ideas? 
 
Thank you
</div>
</div>

Hi all,

I'm trying to create a query in Great Plains for AP Invoices.  I need the results to include the "normal" vendor info(at least the vendorid, but name, etc), the invoice info(like number, entry date,amount, etc), and the G/L accounts to credit and debit.

I have located a few tables that contain some of the data:

pm00200 - Vendor info
pm00300 - Vendor address
pm00400 - Seems to be invoice stuff or at least part of it.
pm30200 - Seems to be paid invoice stuff(i think).

But I don't see anything that necessarily links them together(trxsorce?) and also gets me the Expense and Payable G/L accounts to credit and debit.

Any ideas?

Thank you

GP AP Invoicing Question

Microsoft Dynamics is a line of enterprise resource planning (ERP) and customer relationship management (CRM) software applications. Dynamics can be used with other Microsoft solutions, such as SharePoint, Yammer, Office 365, Azure and Outlook. Dynamics ERP comprises a group of enterprise-resource-planning products includes Dynamics AX (formerly Axapta), a multi-language, multi-currency enterprise software with business management features for financial, human resources, and operations management; Dynamics GP (formerly Great Plains Software); Dynamics NAV (formerly Navision) for financials, supply chain, and people; Dynamics SL (formerly Solomon IV)for project-driven organizations; Dynamics C5 (formerly Concorde C5) for manufacturing, supply chains, analytics and electronic commerce; and Dynamics CRM.

Microsoft Dynamics

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.