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.
Good morning,
I have installed the AR Apply detail view but i’m finding there are a few invoices that do not show up on the report. The invoices have been applied just like the rest and have not been voided. I ran check links on the system, is there anything else you can suggest to get all the pertinent data listed in the report?
Hi Debbie,
Do the payments (or credits or returns) that were applied to the missing invoices show up on the results? Did you make any changes to the code I have posted, or use it as it is? Are you looking at the results directly in SQL or somewhere else?
-Victoria
No, nothing(invoice or payment) for that customer is showing in the results but i can open it in inquiry/sales/transaction by customer.
I used both SQL and smartlist to view the results. I copied the view directly as you have it, no changes were made to it.
Hi Debbie,
This code is performing a number of ‘inner joins’, meaning it will only return records where everything matches up and all related records exist in all tables. First thing I would check is what the Balance Type for this customer is – if it is ‘Balance Forward’, then there will be no apply records stored for that customer. Next, I would verify that apply records exist for this customer (maybe history has been deleted?) by running the following code (just change the customer ID on the bottom line to yours):
Please let me know what you find.
-Victoria
Thanks Victoria! I ran the query and had no results which tells me they DIDN’T apply the invoice! I feel so bad for wasting your time on this. Thank you so much for walking me through this.
Hi Debbie,
No problem at all, glad you were able to figure it out.
-Victoria
Quick question for you on the dates in the RM Apply Table. I’m finding discrepancies in the apply date (DATE1 vs GLPOSTDT) in my customer’s data. In my case the dates matter so it’s throwing me off. Does this make sense to you? It doesn’t to me! : )
Recent example: the customer imports a payment file nightly (cash receipts) all dated April 30th. Posts them via a third party product (postmaster). They have another nightly routine that runs to auto-apply payments to invoices overnight. They are using system date for that, so the apply date was May 1st.
I’m expecting the customers to appear on the RM Historical Aged report at April 30th as outstanding (since apply was May 1st). I intended to use DATE1 “apply date” as the key date in some reporting I’m doing.
The RM Apply Table shows 04/30/2012 as the GLPOSTDT and 05/01/2012 as the DATE1.
The HATB DOES NOT show this customer as outstanding at Apr 30th. The customer inquiry shows the apply date as May 1st.
GP appears to be using the GLPOSTDT for the report (which is correct, I’m running it based on GL date of Apr 30th) but why is it using 04/30/2012 as the GL post date on the apply?
Just wanted to run it by you… smells like a bug to me but I’m not quite sure.
Hi Jen,
Based on what you are describing, I believe that GP is actually working correctly. Let’s looks at all the dates involved, there are actually 6 of them:
The HATB looks at the 3 GL Posting Dates if you are running it using the GL Posting Date option and the 3 Doc Dates if you are using the Document Date option. Also important to note is that it will take the later of these 3 dates. For example: If you have an invoice with a GL Date of 4/25/2012 and a payment with a GL Date of 5/2/2012, but you use an apply GL Date of 4/30/2012, GP will let you enter it in this way, but the HATB will only show this as applied on GL Date of 5/2/2012. This is correct as far as I am concerned, because how can you apply something you do not have? I do not believe this is an issue in your scenario, just adding this in so that the information is complete.
So…from what you have said, the only question is why the Apply GL Posting Date populating with 4/30/2012 when you are using 5/1/2012 as the Apply Doc Date? When you apply transactions manually in GP, if you change the Apply Date on the Apply Sales Documents window, the Apply Posting Date will change with it, but it sounds like you are using a custom routine to auto-apply documents, so I would say you need to find out what/how that is populating both of the apply dates. If I have misunderstood the scenario you are describing, please let me know.
Hope this helps,
-Victoria
I’ll do some more testing but I get what you’re saying. We are specifying the apply date in the eConnect integration as “getdate()”, we are not specifying the apply GL posting date so it must be defaulting just as you describe, based on the later of the dates of the underlying documents.
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