Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Open AP SQL Query: Wro...
Microsoft Dynamics GP (Archived)

Open AP SQL Query: Wrong Debit Amount populating on a 2nd row of the another invoice.

(0) ShareShare
ReportReport
Posted on by Napolo 750

Here is the Query that I pulled.

select
    dis.VENDORID,
    inv.TRXDSCRN,
    inv.DOCDATE,
    inv.PONUMBER,
    inv.DOCNUMBR,
    gl.ACTNUMST,
    inv.DOCAMNT,
    dis.DEBITAMT,
    dis.CRDTAMNT,
    dis.DISTTYPE
from PM20000 as inv
    Left OUTER JOIN PM10100 as dis ON inv.VENDORID = dis.VENDORID
    Left OUTER JOIN gl00105 as gl ON gl.ACTINDX = dis.DSTINDX
WHERE dis.DISTTYPE = '6'
order by inv.VENDORID, DOCNUMBR

I am sure it has to do with my joins, but I have tried every scenario i can think of. My goal is to export all OPEN AP invoices along with the GL distributions (expense only), so that I can import into a new company via IM.

However, my output shows the correct document numbers, amounts, and GL but when it comes to the DEBITAMT Column, it seems to duplicate to how many invoices are open for the vendor.

Example: Doc ending in 201 should be $1720 but rows 2 and 3 show a debit amount of both open transactions.

ap.png

Thank you in advance for your assistance. 

*This post is locked for comments

  • Napolo Profile Picture
    Napolo 750 on at
    RE: Open AP SQL Query: Wrong Debit Amount populating on a 2nd row of the another invoice.

    Thank you!  That did it.

  • Verified answer
    Heather Roggeveen Profile Picture
    Heather Roggeveen 9,142 on at
    RE: Open AP SQL Query: Wrong Debit Amount populating on a 2nd row of the another invoice.

    Your reference is not unique.  You are getting two lines for each because you have two documents for that Vendor, but you are only linking on Vendor.  Try linking by Voucher Number instead:

    select

       dis.VCHRNMBR,

       dis.VENDORID,

       inv.TRXDSCRN,

       inv.DOCDATE,

       inv.PONUMBER,

       inv.DOCNUMBR,

       gl.ACTNUMST,

       inv.DOCAMNT,

       dis.DEBITAMT,

       dis.CRDTAMNT,

       dis.DISTTYPE

    from PM20000 as inv

       Left OUTER JOIN PM10100 as dis ON inv.VCHRNMBR = dis.VCHRNMBR

       Left OUTER JOIN gl00105 as gl ON gl.ACTINDX = dis.DSTINDX

    WHERE dis.DISTTYPE = '6'

    order by inv.VENDORID, DOCNUMBR

    Cheers

    Heather

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,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans