Skip to main content

Notifications

Microsoft Dynamics SL forum
Suggested answer

AR Payment Application Date & Amount

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?

  • 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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,469 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans