You’re offline. This is a read only version of the page.
While using the Quick Query module in SL, is there a query available that shows payment application date and amount for an AR invoice?
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.
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
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,524
Super User 2024 Season 2
#2
Martin Dráb
228,469
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (