Sunday, May 31, 2015

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: 

 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
RM00103 CSF
left join
Left join
and APL.APFRMAPLYAMT is not null

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

No comments:

Post a Comment