AR Payment Application Date & Amount

(0) Share

Report

Posted on by Community Member 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

All responses (3)

Answers (0)

Carolyn Kolpien on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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.