Detailed Payment Application Report Ready for Export to Excel

For companies who have financing, or plan to have financing based on Receivables, the financing institution(s) require a look, not just into your aging, but customer payments, and the invoices these payments were applied to. 

Here’ the problem – there are three separate table types in GP, which contain most information in the system (Work, Open and History). Work files contain entries by users who have not posted them to GP – typically called provisional data. Open files contain transactions posted to Dynamics GP in Open Fiscal Periods/Years – current financial data. Finally, History files contain transactions posted to Dynamics GP in Historical or Closed Fiscal Periods/Years.  This table structure is one of the features which allow a company using Dynamics GP to support hundreds of users processing thousands of transactions an hour.

That being said, in order to tie Cash Application data to Payment Data, you must be able to match the data across all of these table types. For instance it is entirely possible a payment in the work table would be applied to an invoice in a historical table. Consequently, connecting all payment records to all applied documents is not simple, and could result in erroneous or duplicate data being rendered, if not done correctly. It would not do to send duplicate data to an institution who is considering offering financing based on the data, even if it were a simple error.

I created the following query, which identifies all payments and the documents applied to them: 

select

 CSF.CUSTNMBR Customer_Number

,CMR.CUSTNAME Customer_Name

,convert(varchar,CAST(CSF.CUSTBLNC as money),1) Current_Balance

,PMTS.DOCNUMBR Payment_Number

,convert(varchar(10),PMTS.DOCDATE,101) Payment_Date

,convert(varchar,cast(PMTS.ORTRXAMT as money),1) Total_Payment

,convert(varchar,cast(APL.APPTOAMT as money),1) Apply_to_Amount

,APL.APTODCNM Document_Applied_to

,convert(varchar(10),APL.ApplyToGLPostDate,101) Apply_to_Posting_Date

,PMTS.CHEKNMBR Customer_Check_Number

,PMTS.STATUS Doc_Status

from

RM00103 CSF

left join

(

select 'WORK' STATUS,CUSTNMBR,DOCNUMBR,DOCDATE,RMDTYPAL,ORTRXAMT,CHEKNMBR from RM10201

UNION

select 'OPEN' STATUS,CUSTNMBR,DOCNUMBR,DOCDATE,RMDTYPAL,ORTRXAMT,CHEKNMBR from RM20101

UNION

select 'HIST' STATUS,CUSTNMBR,DOCNUMBR,DOCDATE,RMDTYPAL,ORTRXAMT,CHEKNMBR from RM30101

) as PMTS on PMTS.CUSTNMBR = CSF.CUSTNMBR

Left join RM00101 CMR on CSF.CUSTNMBR = CMR.CUSTNMBR

Left join

(

select APFRDCNM,APFRMAPLYAMT,APPTOAMT,APFRDCDT,APTODCNM,APTODCDT,ApplyToGLPostDate from RM20201

UNION

select APFRDCNM,APFRMAPLYAMT,APPTOAMT,APFRDCDT,APTODCNM,APTODCDT,ApplyToGLPostDate from RM30201

) as APL on PMTS.DOCNUMBR = APL.APFRDCNM

where PMTS.RMDTYPAL = 9

and APL.APFRMAPLYAMT is not null

Order by PMTS.DOCNUMBR

Environments vary, so this query should be tested on your data prior to implementation as a foundation for reports or a SmartList. I have also linked tables like Customer Master and Summary so a lot of additional information can be included, should it be required.

To test, run the query against a database and export the information into Excel, then sub-total/count it on Payment Number, which will identify every payment with more than one document applied to it. Open the SmartList identified above and remove the date restriction (keep the Document Type = Payment restriction) and double click on the payments in the SmartList where multiple documents are indicated in the Excel file.  Verify the amounts and document numbers in Excel match those in Dynamics GP, which appeared in the drill down window. Once the data is validated, with little effort a view can be created from the query, which can be used as a foundation for a SmartList or an SSRS report (SQL Server Reporting Services). 

Cash Receipts Inquiry Applied Payments Drill Down

Sometimes users can find it particularly challenging to answer Customer’s questions about which invoices were applied to a particular payment. There are numerous ways to get at this information, but one of my favorite approaches is a SmartList

Navigation = SmartList > Sales > Receivables Transactions > Cash Receipts Entered Today

This SmartList is, by default, filtered for Payments with a Document Date of Today. It can easily be modified to restrict by date ranges, customer name/number and/or payment amounts then saved as various additional favorites (i.e. Payments by Customer, Payments by Amount, etc.)

In this SmartList, when you double click on a payment, Dynamics GP opens the Cash Receipts Inquiry Zoom Window, which contains detailed information about this payment (cash, check or credit card, dates, comments, etc.)  

This window also has an Apply Button. Clicking on the Apply Button opens the Applied To Debits Window, which presents a list of Documents Applied to the payment the user double clicked on.  From here, these documents can be accessed, viewed and printed.

If you just have folks on the phone with customers, this SmartList, which can be filtered by Customer, Dates, Document Numbers and Amounts, etc. and drills down into the specifics, once a particular payment is located, would likely do nicely to meet their needs.  

In some cases, this may not be the sum total of your problem, but on the off chance you do have folks in Finance or Collections struggling with this problem with some minor training, this may resolve most of the issues causing consternation.  If your needs go well beyond this, Dynamics GP has a Collections Management module available, with a host of features to improve customer interaction.