Skip to main content

Notifications

Microsoft Dynamics SL (Archived)

AR Payment Application Date & Amount

(0) ShareShare
ReportReport
Posted on by Microsoft Employee

While using the Quick Query module in SL, is there a query available that shows payment application date and amount for an AR invoice?

*This post is locked for comments

  • RE: AR Payment Application Date & Amount

    Hi Ctomlinson,

    If you have purchased the QQ module, it gives you the Quick Query Viewer screen with the New icon enabled, in System Manager to create custom views of your  own.  You could create a simple view in SQL Server Management Studio to give you the fields that you want and then add that view to the Quick Query menu.  

    To answer your question directly, there is no standard QQ view to show you payments on AR invoices.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Payment Application Date & Amount

    Hi

    The below query will resolve your needs

    Select B.CustID, C.Name, B.BatNbr as 'Invoice Batch Nbr' ,  B.RefNbr ,B.DocDate as 'Invoice Date', B.DueDate 'Invoice Due Date',  A.AdjdDocType, B.OrigDocAmt,  B.DocBal, PerEnt,PerPost, AdjgRefNbr 'Payment RefNbr' , A.DateAppl as 'Payment Applied Date', A.AdjgPerPost 'Adjusted PerPost' , CuryAdjdAmt as 'Adjusted Amount'

    From ARAdjust A JOIN ARDOC B On A.AdjdRefNbr =B.RefNbr and A.CustId =B.CustId Join Customer  C On  B.CustId =C.CustId

    Where A.CustId ='XXXXX'  and A.AdjgPerPost ='YYYYMM'

    Order by A.AdjdDocType

    Note : 'XXXXX' represents Customer ID and 'YYYYMM' represents PeriodPost (Payment Applied Period)

    PeriodPost should be in the following format 201402 for 02-2014

    Hope this helps

    Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Payment Application Date & Amount

    this is not a direct answer to your question but may be useful.  There are two other ways to determine what paid an AR invoice and when.

    First, there is the payment application report.  This is a period sensitive report with a beginning and ending period so you need to make sure what you enter will cover the posting period(s) of the payment(s) that paid the invoice.

    Second, and the better way for looking at one invoice, is to use the application inquiry/reversal screen.  this screen is normally used to look up a payment and what it was applied to but it is not well know that you can also use this screen to look up an invoice and what paid it.  To do the later, enter the customer number and then click the button to the right of the customer field.  that opens a new screen where you can enter the invoice number (enter into the field labeled as such and not the field labeled reference number), leave the begin and end period fields blank and click the find payment button and the grid will show you any payment or credit memo applied to that invoice.

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

News and Announcements

Announcing Category Subscriptions!

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 Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans