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.
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:
,convert(varchar,CAST(CSF.CUSTBLNC as money),1) Current_Balance
,convert(varchar,cast(PMTS.ORTRXAMT as money),1) Total_Payment
,convert(varchar,cast(APL.APPTOAMT as money),1) Apply_to_Amount
select 'WORK' STATUS,CUSTNMBR,DOCNUMBR,DOCDATE,RMDTYPAL,ORTRXAMT,CHEKNMBR from RM10201
select 'OPEN' STATUS,CUSTNMBR,DOCNUMBR,DOCDATE,RMDTYPAL,ORTRXAMT,CHEKNMBR from RM20101
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
select APFRDCNM,APFRMAPLYAMT,APPTOAMT,APFRDCDT,APTODCNM,APTODCDT,ApplyToGLPostDate from RM20201
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
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).