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).