February 15, 2010
This has been a hot topic in the newsgroups lately and several people have asked me if I have any code for Receivables (AR) apply information in Dynamics GP. Below is a view that should help you get started if you’re building a report to show AR apply information in Dynamics GP.
For more views like this, check out my GP Reports page.
For help with using this in SmartList Builder, take a look at my post on How to use a SQL view in SmartList Builder.
~~~~~
CREATE VIEW view_AR_Apply_Detail AS /******************************************************************* view_AR_Apply_Detail Created on Feb 15, 2010 by Victoria Yudin - Flexible Solutions, Inc. For updates visit http://victoriayudin.com/gp-reports/ Returns apply detail for all posted receivables transactions. Only shows functional currency amounts. Credit documents applied to more than one debit document will return multiple lines. Tables used: RM00101 – Customer Master RM20101 - Open Transactions RM20201 – Open Transactions Apply RM30101 – Historical Transactions RM30201 – Historical Transactions Apply Updated on Aug 12, 2010 to add original total of Applied to Doc and Applied To Doc Paid Off date. Updated on Mar 23, 2011 to add unapplied amount of Applied to Doc. *******************************************************************/ SELECT T.CUSTNMBR Customer_ID, CM.CUSTNAME Customer_Name, T.DOCDATE Document_Date, T.GLPOSTDT GL_Posting_Date, CASE T.RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, T.docTypeNum Document_Type_and_Number, T.DOCNUMBR Document_Number, T.ORTRXAMT Original_Trx_Amount, T.CURTRXAM Current_Trx_Amount, T.amountApplied Total_Applied_Amount, A.APPTOAMT Amount_Applied, A.APTODCTY Applied_to_Doc_Type, A.debitType Applied_to_Doc_Type_Name, A.APTODCNM Applied_to_Doc_Number, A.APTODCDT Applied_to_Document_Date, A.ApplyToGLPostDate Applied_to_GL_Posting_Date, A.DISTKNAM Discount, A.WROFAMNT Writeoff, A.DATE1 Apply_Document_Date, A.GLPOSTDT Apply_GL_Posting_Date, D.ORTRXAMT Applied_To_Doc_Total, D.DINVPDOF Applied_To_Date_Paid_Off, D.CURTRXAM Applied_To_Doc_Unapplied_Amount FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL, CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT, CURTRXAM, ORTRXAMT - CURTRXAM amountApplied FROM RM20101 WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) UNION SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL, CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT, CURTRXAM, ORTRXAMT - CURTRXAM amountApplied FROM RM30101 WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)) T INNER JOIN RM00101 CM ON T.CUSTNMBR = CM.CUSTNMBR INNER JOIN (SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM, APFRDCTY,APFRDCNM, CASE APTODCTY WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Scheduled Payment' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' END as debitType, APPTOAMT, ApplyToGLPostDate, APTODCDT, tO2.DISTKNAM, tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT FROM RM20201 tO2 INNER JOIN RM20101 tO1 ON tO2.APTODCTY = tO1.RMDTYPAL AND tO2.APTODCNM = tO1.DOCNUMBR UNION SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM, APFRDCTY, APFRDCNM, CASE APTODCTY WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Scheduled Payment' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' END AS debitType, APPTOAMT, ApplyToGLPostDate, APTODCDT, tH2.DISTKNAM, tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT FROM RM30201 tH2 INNER JOIN RM30101 tH1 ON tH2.APTODCTY = tH1.RMDTYPAL AND tH2.APTODCNM = tH1.DOCNUMBR) A ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR INNER JOIN (SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM FROM RM20101 UNION SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM = 0 FROM RM30101) D ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR /** the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions **/ GO GRANT SELECT ON view_AR_Apply_Detail TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Hello Victoria,
I am very new to GP and looking for some guidence. I am looking to display Invoices in the SOP tables and their respective activity in the RM tables. What is the best way to display this? I am was thinking the SOPNUMBE (SOP) to APTODCNM (RM).
David,
There are many different RM tables…what specifically do you need to show?
-Victoria
I am looking to show the activity associated with Invoices in SOP and display the corresponding RM Current & Historical Transaction Headers (RM20101 & RM30101) as well as the activity detail in the RM (20201 & 30201).
David,
Sorry, still not clear…can you give me an example of what your report would look like? What information do you actually need from SOP? Are you positive you cannot do all of it from RM? Or start with RM and link back to SOP for some of the detail needed?
-Victoria
Victoria,
I am looking to display an Invoice and its associated activity. The original Invoice line from my understanding is in the SOP and the Activity in the RM.
On a side note, I have a document # in the RM tables starting with a DBT. I assume thats a DEBIT but cant seem to locate its corresponding record in the SOP Header table.
Hi David,
I don’t mean to be difficult…but I have spent so much time and effort in the past changing reports that were started with the wrong specifications, that now I do all that I can to get the spec right upfront. Below are some thoughts/pointers for you (in no particular order):
SOPNUMBE = APTODCNM AND APTODCTY = 1 AND SOPTYPE = 3Hope this helps with some of your questions.
-Victoria
Thanks for your reply. Much appreciated.
- The purpose of my analysis is to show all activity related to an invoice (Debits, credits, reversals, voids, or anything a user in GP can possibly post associated with an invoice)
- The DBT’s are in fact debits, but have no referential link to an invoice.
Thank you again, I was able to confirm many of my assumptions of the system that I have been seeing on the back end.
Regards,
- Dave
Dave,
In GP, applying transactions is the typical way of ‘linking’ them. You can only apply credit transactions (payments, credit memos or returns) to debit transactions (like invoices, debit memos or finance charges). There is no way to apply a debit memo to an invoice. There is no built in mechanism to record something like…”I am entering a debit memo and it is related to invoice 12345.” Also, if you apply a payment to an invoice, then void the payment, that will delete the apply information, thus leaving no way to link the voided payment to the invoice. So, out-of-the-box, the only ‘related’ information you will be able to find for an invoice are non-voided applied credit transactions (RMDTYPAL of 7, 8, or 9).
If your company is using some other method of ‘linking’ related activity, then you need to find out what it is and how/where it is stored. If I were asked to create a report like this, I would ask for examples in the user interface – show me a list of all ‘related’ transaction for a particular invoice and what determines that they are related. You will need as many examples as it takes to capture all the permutations of this in your data.
-Victoria
Victoria-
Attempting to make the link to the GL. In short we need to see the AR account the cash receipt relieved and compare that to the AR account the sales document posted to originally.
Sincerely,
Tom
Tom,
You would need a combination of this view and the GL Distributions for AR Transactions view to accomplish something like that.
-Victoria
Hi Victoria
thanks for the view of application receipt, there would be any way to add to this view, which user apply the receipt.
Thank you,
Hi Iris,
From a quick glance at the tables, out-of-the-box GP does not track the user who applied the receipt. So unless you turn on some kind of additional tracking, this information is not stored anywhere for us to be able to report on.
-Victoria
Victoria,
I want to limit the above SQL view by the payment posting date range but with so many joins I’m unsure where to put the “Between” limiter. Would you have any suggestions.
Thanks,
Mark
Mark,
I believe you can accomplish that by adding something like the following at the bottom:
WHERE T.GLPOSTDT BETWEEN '1/1/2011' AND '12/31/2011'-Victoria
Victoria,
I am trying to take the concept above one step further. I am trying to get the item number and item description from the SOP30300. The issue is that if there are multiple sales line items and multiple payments for a single invoice, it shows all of the sales line items for each payment even though each payment did not pay every line item. I am trying to figure out how to link the payment line item in RM20201 to the sales line item in SOP30300. Any suggestions?
Nate
Hi Nate,
Unless you have a customization to do this, out-of-the-box GP does not let you allocate a payment to a line item on an invoice. So if you have an invoice for a total of $500 with 5 line items of $100 each, and you apply a $300 payment to it, you do not have an option to apply it to 3 of the 5 line items. It’s just a partial payment against the $500 total.
Do you have some kind of a customization to allow tracking of what line item is paid with a payment? If you do, then you would have to get the information from that customization. If not, there is no way to report on this, as the information is not entered or stored anywhere in GP.
-Victoria
Can anyone tell me what the tables are that are associated with the AP report in GP? Thank you
VC,
There are dozens of AP reports in GP, can you be a little more specific as to what you’re looking for? In the meantime, I have a page which lists Payables tables that might help: http://victoriayudin.com/gp-reports/pm-tables/. And I have published a number of Payables SQL views here: http://victoriayudin.com/gp-reports/.
-Victoria
Many Thanks!
Appreciate your efforts to publishing such a useful codes.
This is great. But how to add all invoices, even not paid yet? Thanks
Ilya,
Not sure it would be so easy to simply add to this particular view, because it’s starting with credits and linking on the apply details. I think you’d have to structure this a little differently if you wanted a list of all invoices and then a listing of what was applied to them. You could start with something like my All Posted Receivables Transactions view and then add in the Apply details.
-Victoria