Microsoft Dynamics GP (Archived)

AP General Ledger Export via SQL Query

(0) Share

Report

Posted on by Napolo

766

I am attempting to create my first query to export the following columns:

Vendor Name, Vendor ID, DocType, VCHRNMBR, Doc Date, GL Account Number, GL ACCTDESCR, Payment Amount.

The goal, is to easily export a full Payables accounting, to easy filter by vendors and/or GL Expense types. To evaluate spend.

I have started the below Query, but so far nothing is populating. Any help with what I might be missing, would be appreciated.

SELECT     MC020103.DOCTYPE, MC020103.DCSTATUS, MC020103.VCHRNMBR, MC020103.DOCDATE, MC020103.VENDORID, PM00200.VENDORID AS Expr1,
                      PM00200.VENDNAME, MC020103.DEX_ROW_ID, GL00100.DEX_ROW_ID AS Expr2, GL00100.ACTDESCR, GL00100.ACTNUMBR_1
FROM         MC020103 INNER JOIN
                      PM00200 ON MC020103.VENDORID = PM00200.VENDORID INNER JOIN
                      GL00100 ON MC020103.DEX_ROW_ID = GL00100.DEX_ROW_ID
WHERE     (MC020103.DOCDATE BETWEEN CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-12-18 00:00:00', 102))

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Richard Wheeler 75,852 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

This version works for me. I would avoid the use of the BETWEEN in the WHERE clause

SELECT MC020103.DOCTYPE, MC020103.DCSTATUS, MC020103.VCHRNMBR, MC020103.DOCDATE, MC020103.VENDORID, PM00200.VENDORID AS Expr1,

PM00200.VENDNAME, MC020103.DEX_ROW_ID, GL00100.DEX_ROW_ID AS Expr2, GL00100.ACTDESCR, GL00100.ACTNUMBR_1

FROM MC020103 INNER JOIN

PM00200 ON MC020103.VENDORID = PM00200.VENDORID INNER JOIN

GL00100 ON MC020103.DEX_ROW_ID = GL00100.DEX_ROW_ID

WHERE (MC020103.DOCDATE >= '2015/01/01' AND MC020103.DOCDATE <= '2015/12/18')

Was this reply helpful? Yes No
Napolo 766 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Richard -

I used your exact statement, and I am still receiving 0 rows. After I execute, do I need to do anything additional to get my view of data?

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

If you remove the WHERE clause do you get any data?

Was this reply helpful? Yes No
Suggested answer

Jothi Krishnan N 1,897 on at

Like
a
(0)

Report
Copy link

Link copied!

Nicole - I believe u r looking for something like this... Just keep in mind when you write query that... dont use Dex_row_id column for any purpose...and do not use Multicurrency (MC) tables as primary tables when you join many tables.

select PM10100.VCHRNMBR,[dbo].[DYN_FUNC_Document_Type_PM_Trx](DOCTYPE) as Doctype,PM10100.VENDORID,VENDNAME, ACTNUMST, ACTDESCR, DEBITAMT,CRDTAMNT from PM20000

left join pm10100 on PM10100.VCHRNMBR = PM20000.VCHRNMBR

LEFT join pm00200 on PM00200.VENDORID = PM20000.VENDORID

left join GL00105 on GL00105.ACTINDX = PM10100.DSTINDX

left join GL00100 on GL00100.ACTINDX = PM10100.DSTINDX

union all

select PM30600.VCHRNMBR,[dbo].[DYN_FUNC_Document_Type_PM_Trx](pm30200.DOCTYPE) as Doctype,PM30600.VENDORID,VENDNAME, ACTNUMST, ACTDESCR, DEBITAMT,CRDTAMNT from PM30200

left join PM30600 on PM30600.VCHRNMBR = PM30200.VCHRNMBR and PM30200.DOCTYPE = PM30600.DOCTYPE

LEFT join pm00200 on PM00200.VENDORID = PM30200.VENDORID

left join GL00105 on GL00105.ACTINDX = PM30600.DSTINDX

left join GL00100 on GL00100.ACTINDX = PM30600.DSTINDX

order BY

VCHRNMBR

Also did you look at the payables distribution smartlist report.

Was this reply helpful? Yes No
Verified answer

Richard Wheeler 75,852 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Here is a slightly different version which will return the same data. I agree that there is no reason for the mutli-currency tables. Now this version will need some tweaking depending on exactly what it is you want. Please remember that every transaction will have at least two distributions. You could change this to show only payments or only invoices plus filter on the distribution type to show only debits to the expenses.

SELECT VENDNAME,VENDORID,DOCTYPE,VCHRNMBR,DOCDATE,ACTNUMST,ACTDESCR,DOCAMNT FROM (

SELECT T5.VENDNAME,T4.VENDORID,

CASE

WHEN T4.DOCTYPE = 1 THEN 'Invoice'

WHEN T4.DOCTYPE = 2 THEN 'Finance Charge'

WHEN T4.DOCTYPE = 3 THEN 'Miscellaneous Charges'

WHEN T4.DOCTYPE = 4 THEN 'Return'

WHEN T4.DOCTYPE = 5 THEN 'Credit Memo'

WHEN T4.DOCTYPE = 6 THEN 'Payment'

ELSE 'Unknown'

END AS DOCTYPE,

T4.VCHRNMBR,CONVERT(CHAR(10),T4.DOCDATE,101) AS DOCDATE,ACTNUMST,T2.ACTDESCR,T4.DOCAMNT

FROM PM10100 T1

INNER JOIN GL00100 T2 ON T1.DSTINDX=T2.ACTINDX

INNER JOIN GL00105 T3 ON T1.DSTINDX=T3.ACTINDX

INNER JOIN PM20000 T4 ON T1.VCHRNMBR=T4.VCHRNMBR

INNER JOIN PM00200 T5 ON T4.VENDORID=T5.VENDORID

UNION ALL

SELECT T5.VENDNAME,T4.VENDORID,

CASE

WHEN T4.DOCTYPE = 1 THEN 'Invoice'

WHEN T4.DOCTYPE = 2 THEN 'Finance Charge'

WHEN T4.DOCTYPE = 3 THEN 'Miscellaneous Charges'

WHEN T4.DOCTYPE = 4 THEN 'Return'

WHEN T4.DOCTYPE = 5 THEN 'Credit Memo'

WHEN T4.DOCTYPE = 6 THEN 'Payment'

ELSE 'Unknown'

END AS DOCTYPE,

T4.VCHRNMBR,CONVERT(CHAR(10),T4.DOCDATE,101) AS DOCDATE,ACTNUMST,T2.ACTDESCR,T4.DOCAMNT

FROM PM30600 T1

INNER JOIN GL00100 T2 ON T1.DSTINDX=T2.ACTINDX

INNER JOIN GL00105 T3 ON T1.DSTINDX=T3.ACTINDX

INNER JOIN PM30200 T4 ON T1.VCHRNMBR=T4.VCHRNMBR

INNER JOIN PM00200 T5 ON T4.VENDORID=T5.VENDORID) T

ORDER BY T.DOCDATE,T.VENDORID

Was this reply helpful? Yes No
Napolo 766 on at

Like
a
(0)

Report
Copy link

Link copied!

Wow - this is really great. THANK YOU BOTH very much!!

Was this reply helpful? Yes No
Napolo 766 on at

Like
a
(0)

Report
Copy link

Link copied!

Does this statement omit Voided transactions by chance? Or is there a way for me to output the doc status? From the designer, it already looks like every column is selected.

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Here is a version with the voided status. If you want to only see those that were not voided simply add a where clause on the outer select(WHERE T.VOIDED=0)

SELECT VENDNAME,VENDORID,DOCTYPE,VCHRNMBR,DOCDATE,ACTNUMST,ACTDESCR,DOCAMNT,VOIDED FROM (

SELECT T5.VENDNAME,T4.VENDORID,

CASE

WHEN T4.DOCTYPE = 1 THEN 'Invoice'

WHEN T4.DOCTYPE = 2 THEN 'Finance Charge'

WHEN T4.DOCTYPE = 3 THEN 'Miscellaneous Charges'

WHEN T4.DOCTYPE = 4 THEN 'Return'

WHEN T4.DOCTYPE = 5 THEN 'Credit Memo'

WHEN T4.DOCTYPE = 6 THEN 'Payment'

ELSE 'Unknown'

END AS DOCTYPE,

T4.VCHRNMBR,CONVERT(CHAR(10),T4.DOCDATE,101) AS DOCDATE,ACTNUMST,T2.ACTDESCR,T4.DOCAMNT,T4.VOIDED

FROM PM10100 T1

INNER JOIN GL00100 T2 ON T1.DSTINDX=T2.ACTINDX

INNER JOIN GL00105 T3 ON T1.DSTINDX=T3.ACTINDX

INNER JOIN PM20000 T4 ON T1.VCHRNMBR=T4.VCHRNMBR

INNER JOIN PM00200 T5 ON T4.VENDORID=T5.VENDORID

UNION ALL

SELECT T5.VENDNAME,T4.VENDORID,

CASE

WHEN T4.DOCTYPE = 1 THEN 'Invoice'

WHEN T4.DOCTYPE = 2 THEN 'Finance Charge'

WHEN T4.DOCTYPE = 3 THEN 'Miscellaneous Charges'

WHEN T4.DOCTYPE = 4 THEN 'Return'

WHEN T4.DOCTYPE = 5 THEN 'Credit Memo'

WHEN T4.DOCTYPE = 6 THEN 'Payment'

ELSE 'Unknown'

END AS DOCTYPE,

T4.VCHRNMBR,CONVERT(CHAR(10),T4.DOCDATE,101) AS DOCDATE,ACTNUMST,T2.ACTDESCR,T4.DOCAMNT,T4.VOIDED

FROM PM30600 T1

INNER JOIN GL00100 T2 ON T1.DSTINDX=T2.ACTINDX

INNER JOIN GL00105 T3 ON T1.DSTINDX=T3.ACTINDX

INNER JOIN PM30200 T4 ON T1.VCHRNMBR=T4.VCHRNMBR

INNER JOIN PM00200 T5 ON T4.VENDORID=T5.VENDORID) T

ORDER BY T.DOCDATE,T.VENDORID

Was this reply helpful? Yes No