Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / AP General Ledger Expo...
Microsoft Dynamics GP (Archived)

AP General Ledger Export via SQL Query

(0) ShareShare
ReportReport
Posted on by Napolo 750

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

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: AP General Ledger Export via SQL Query

    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

  • Napolo Profile Picture
    Napolo 750 on at
    RE: AP General Ledger Export via SQL Query

    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.

  • Napolo Profile Picture
    Napolo 750 on at
    RE: AP General Ledger Export via SQL Query

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

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: AP General Ledger Export via SQL Query

    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

  • Suggested answer
    Jothi Krishnan N Profile Picture
    Jothi Krishnan N 1,865 on at
    RE: AP General Ledger Export via SQL Query

    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.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: AP General Ledger Export via SQL Query

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

  • Napolo Profile Picture
    Napolo 750 on at
    RE: AP General Ledger Export via SQL Query

    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?

    ap-shot.png

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: AP General Ledger Export via SQL Query

    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')

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans