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 https://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 Aug 12, 2010 to add original total of Applied
-- to Doc and Applied To Doc Paid Off date
-- Updated Mar 23, 2011 to add unapplied amount of
-- Applied to Doc
-- Updated Jul 10, 2013 to add Payment Batch and PO Number
-- Updated Jul 15, 2013 to add Short Name
-- ************************************************************
SELECT T.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.SHRTNAME Short_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.BACHNUMB Payment_Batch,
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,
D.CSPORNBR Customer_PO_Number
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, BACHNUMB,
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, BACHNUMB,
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, CSPORNBR
FROM RM20101
UNION
SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
CURTRXAM = 0, CSPORNBR
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.
Victoria Yudin 
Hi Victoria,
Your report is very similar to what I am trying to achieve, If we are trying to get all the payments against invoices whether there is no payment or multiple payments do you have a sql view that covers this scenario? The line should have the invoice # and payment # if a payment is made if not the payment# would be blank. Off course, the invoice line would duplicate if there are multiple payments made to that invoice.
Thanks
Naraish
LikeLike
Hi Naraish,
I don’t have anything like this already published for receivables (I do for payables). You would basically need to flip this around – start with all debit transactions (invoices, debit memos, etc.) and then link to the applied payments and credits. I will put this on the list for future blog posts, but not sure when I will have time to get to it. If you need help with this sooner, it’s something I can provide as a consulting service.
-Victoria
LikeLike
Hi Victoria,
How do I contact you?
Regards
LikeLike
Hi Naraish,
You an email me at victoria @ flex – solutions.com. Please take the spaces out of the email address.
-Victoria
LikeLike
Victoria, thank you so much for your views!!! How would we add the GL account to this view?
LikeLike
Hi Tosha,
That actually might get complicated. What side do you want to see the GL accounts for (apply FROM or apply TO). What happens if there are 20 GL distributions for each transaction?
-Victoria
LikeLike
Hi Victoria,
I want to see the apply to and the apply from, but I only want to see one customer, and I want to narrow it down to 2 gl accounts. Will that be possible?
LikeLike
Hi Tosha,
I think it’s possible, but this is pretty much custom code just for you, since I cannot make this generic enough to be useful to others. If you wanted to do this yourself, you can check out my GL distribution code for AR transactions here: https://victoriayudin.com/2010/02/10/sql-view-with-all-gl-distributions-for-ar-transactions/. You would basically add the GL distribution detail to the apply logic.
-Victoria
LikeLike
Thank you so much for this view, Victoria, it’s made a great SmartList that our A/R coordinator relies on heavily now! I did have one further question/favor to ask, as I ran into some trouble converting the SmartList I setup into a “refreshable” Excel report that is linked to this SQL view.
For some of our customers, depending on the time range, this view in SmartList can get quite long, and it takes quite a while to export to Excel. I was using another one of your awesome pages (https://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/) to try and accomplish this, but I’m getting an error that the call won’t work since this was a “view” in SQL. I’m not all that comfortable in SQL, and I’ve been relying on many of the out-of-the-box Excel reports from SmartList & then tweaking the Commands from there, but since this is a whole separate custom SmartList joining different tables, I think that’s where I run into my limits.
Any help is appreciated!
Thanks!
– Pat
LikeLike
Hi Pat,
Thanks for your kind words.
Not 100% sure how to help with this. It might require some hands on help, which can’t be done in blog comments. 😦 Have you tried getting some help on this from either an IT resource or your Dynamics GP partner? That might be the best way to go.
-Victoria
LikeLike
Thanks for the reply, Victoria. It actually just took a little more education on my part with the SmartList Builder tool, and eOne had some decent documentation on how to convert anything built in that tool into a refreshable Excel report. It enabled me to still use your A/R Apply Detail view and have it run much faster in Excel vs. SmartList within GP.
Thanks again!
Pat
LikeLike
Hello Victoria
I have used RM30201 but the report shows one payment applied to all invoices even that payment only been applied to 2 invoices
https://community.dynamics.com/gp/f/32/t/348419
LikeLike
Nihad,
Why not just use this view? RM30201 is not enough anyway, you will need to add other tables to make sure your data is complete.
-Victoria
LikeLike
Hi Victoria,
First thing first – thank you for the time and effort you put into these pages. You make our life a lot easier.
I’m trying for a while now to get a FULL history of the AR invoices – original amount, everything applied to it (with some reasonable details), unpaid balance.
The difficulty comes form the fact that I need all these in both CAD and USD. Our functional currency is CAD.
Many of our clients work with us in both currencies and they want to see a separate statement for USD invoices.
Would this be possible?
Thank you,
LikeLike
Hi Florin,
You would need to add the MC020102 to this view. It’s not something I have to share, as I do not have too many customers using Multicurrency.
-Victoria
LikeLike
Hi Victoria – thanks a ton for this view, really saved me a lot of trouble. Just 1 question, have you done a similar thing for AP applied transactions, if yes can you point me to the right direction.
Regards
Sid
LikeLike
Hi Sid,
I have a few different views for AP apply information, you can see them in this list: https://victoriayudin.com/gp-reports/payables-sql-views/. They are called Apply Information, Payment Apply Detail, and Payment Apply Detail and GL Distributions,
-Victoria
LikeLike
Hi Victoria – Your queries look great! I am working on a project where I need to pull the A/R balances for my client as of various dates. I’m thinking I can get this from the history GL30000 table. Or from the receivables module RM30101. Does that make sense to you?
LikeLike
Hi Joe,
Do you mean the total balance of the AR General Ledger account? If so, yes, you should be able to do that with GL30000 and GL20000. If you are looking for the total balance of the AR subledger as of a particular date, that is much more complicated – there are a number of tables but also some complex logic that is needed. Can you give me a little bit more detail as to what you’re looking to do?
-Victoria
LikeLike
Hi Victoria,
I’ve been relying on your site for many years now and somehow missed this view until now. Thank you by the way for this and everything else you do. You have made my life easier more times than I can count. No question here, just wanted to say thanks since I’m pilfering another piece of code from your site. 🙂
LikeLike
Thank you Mark, I really appreciate that!
-Victoria
LikeLike
Victoria,
If we do a SOP return, will it create a record in the RM20201 and/or RM30201 tables when it gets applied to an invoice? I wasn’t sure if SOP Return application was stored elsewhere in GP, or in these same tables with the other RM application data.
Thanks!
Matt
LikeLike
Hi Matt,
When you post the return in SOP, it will post to RM20101 with an RMDTYPAL of 8. When you apply it to an invoice, it will add a record to RM20201. (When moved to history, it will move to RM30201.) In general, SOP invoices and returns are treated the same as RM invoices and returns once posted.
Hope that helps.
-Victoria
LikeLike
Victoria,
My company uses GP Project to manage and bill our grants. I noticed in your resources the GP tables for Project are not listed. When I process a billing transaction from Project it sends the bill to the A/R module for application of the payment. I could use a report that lists the payments against the bills (including payment and bill numbers and amount of the payment applied against each bill), the expenditure account number, the A/R account number, the revenue account number. It appears that the code above would be a good starting point to gather this information. Coded in the Project Card under Budget, under a specific cost category, under Accounts, and in the Time and Materials Accounts section; the expenditure, A/R, and revenue accounts are specifically identified. Is it possible to get to this information and include it against the report that I need?
Kris
LikeLike
Hi Kris,
If the account numbers you need are in the distributions of the AR invoice, you may not need to go back to the project module for them. You could combine this code with the code from my view.
I don’t foresee adding any details for the Project tables to my blog, as there are not that many companies (at least that I work with) that use Project Accounting module and I almost never write any code for it.
-Victoria
LikeLike
Victoria,
Thank you for your reply. The original expenditure account is not included in the distributions of the AR invoice (bill document).
Kris
LikeLike
Hi Kris,
Gotcha. In that case, you are going to have to link to the Project tables. If you don’t know them, you may need to talk to your GP Partner or Microsoft support to get help with finding the right tables and ways to link to them. Sorry not to be able to help more with this.
-Victoria
LikeLike
This report is exactly what my company needs, except we need to know what Customer Name and Salesperson ID is attached to the invoice the payment was applie to, not necessarily the customer the payment was posted to. The reason being is that when we run the report, if the payment was posted to the National account, we can’t tell what customer it actually was being applied to. The national account may have several different salespeople invoicing for it. Do you have any recommendations of changes to the SQL?
LikeLike
Hi Trudy,
Here is the code to accomplish that:
SELECT T.CUSTNMBR Customer_ID,CM.CUSTNAME Customer_Name,
CM.SHRTNAME Short_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.BACHNUMB Payment_Batch,
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,
D.CSPORNBR Customer_PO_Number,
D.CUSTNMBR Applied_To_Customer_ID,
D.SLPRSNID Applied_To_Salesperson_ID
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, BACHNUMB,
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, BACHNUMB,
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, CSPORNBR, CUSTNMBR, SLPRSNID
FROM RM20101
UNION
SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
CURTRXAM = 0, CSPORNBR, CUSTNMBR, SLPRSNID
FROM RM30101) D
ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR
-Victoria
LikeLike
Thank you so much Victoria!
In addition to the Customer number, how do I add the customer name from the invoice and the salesperson from the customer card that coincides with that customer name? I would also need the document date from the original invoice that I am applying the payment to.
LikeLike
Hi Trudy,
The applied to document date is already there. I just added the customer name and changed the salesperson to be taken from the applied to customer, not the transaction.
SELECT T.CUSTNMBR Customer_ID,CM.CUSTNAME Customer_Name,
CM.SHRTNAME Short_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.BACHNUMB Payment_Batch,
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,
D.CSPORNBR Customer_PO_Number,
D.CUSTNMBR Applied_To_Customer_ID,
AC.CUSTNAME Applied_To_Customer_Name,
AC.SLPRSNID Applied_To_Salesperson_ID
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, BACHNUMB,
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, BACHNUMB,
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, CSPORNBR, CUSTNMBR, SLPRSNID
FROM RM20101
UNION
SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
CURTRXAM = 0, CSPORNBR, CUSTNMBR, SLPRSNID
FROM RM30101) D
ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR
LEFT OUTER JOIN RM00101 AC
ON D.CUSTNMBR = AC.CUSTNMBR
-Victoria
LikeLike
Victoria,
This is exactly what we need. Thank you!
LikeLike
Victoria
I am using this code to get customers that have order since a given date:
(SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
case when RMDTYPAL = ‘1’ then (SLSAMNT + MISCAMNT – TRDISAMT)
else 0
end Amount
FROM RM20101
WHERE VOIDSTTS = 0
and DOCDATE > dateadd(mm, -24, dateadd(mm, datediff(mm, 0, getdate()), 0))
UNION ALL
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
case when RMDTYPAL = ‘1’ then (SLSAMNT + MISCAMNT – TRDISAMT)
else 0
end Amount
FROM RM30101
WHERE VOIDSTTS = 0
and DOCDATE > dateadd(mm, -24, dateadd(mm, datediff(mm, 0, getdate()), 0))) T
LEFT OUTER JOIN RM00101 C — customer master
ON T.CUSTNMBR = C.CUSTNMBR
GROUP BY T.CUSTNMBR, C.CUSTNAME
I would like to get a list of customers that have not ordered (inactive customers) since a given date:
Can you please help me with this?
thanks,
LikeLike
Ricky,
First things first, you need to define how you determine if customer ‘has not ordered’. Let’s, for example, say that means we have not invoiced them. If we can use that logic, then the following query will get you all customers set up in GP that have not been invoiced since the date you specify on the first line:
declare @date datetime = ‘2014-12-31’
select * from RM00101
where CUSTNMBR not in
(select CUSTNMBR from RM20101
where RMDTYPAL = 1 and VOIDSTTS = 0
and DOCDATE > @date)
and CUSTNMBR not in
(select CUSTNMBR from RM30101
where RMDTYPAL = 1 and VOIDSTTS = 0
and DOCDATE > @date)
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
Hope all is well! I am trying to export a smartlist containing this view to excel. However there are some formatting issues with the document_date and document_number . All date columns in the cell read =Date(2014,12,31) as well as the formula bar. Document_Number cell reads #NAME? but the formula bar reads the correct document number. The Apply_to_doc_number column does read the cell and formula bar correctly. Have you seen this before? I do receive the following error when exporting to excel ” We found a problem with some content in “worksheet name” Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes.” I click yes and get this error “Excel was able to open the file by repairing or removing the unreadable content. ” Repaired Records: Cell information from “worksheet name” I was able to export other smartlist successfully.
Thanks for your help!
LikeLike
Hi Shelly Ann,
I have not seen this before. I have set up this same view in SmartList Builder for a number of customers, I just tested exporting to Excel on one of their systems, everything looks fine.
I would suspect some system specific settings causing this. First place I would check is the regional settings on the computer where you are seeing this problem.
-Victoria
LikeLike
Hi Victoria,
Okay so I was able to create the view. The only issue I am having is that if someone posted an open receipt it is not showing up in this report. I don’t know if it is because it is based on the applied document, but if the receipt wasn’t applied to any document I still need to see that cash amount – preferably in the column that you added “unapplied amount” (which is great by the way). What would you recommend I do to pull ALL cash receipts in a given month into this report?
LikeLike
Hi Rachel,
You can change the INNER JOIN on lines 105 and 147 to LEFT OUTER JOIN to accomplish this.
-Victoria
LikeLike
Thanks Victoria! Please excuse my lack on knowledge, but is there a way to change that once the view has been created or should I remove the view and redo it with the changes?
LikeLike
Hi Rachel,
No problem. You can change the very first word in the code from CREATE to ALTER, change the lines I mentioned, and re-run it. It will update what’s already there. 🙂
-Victoria
LikeLike
Hi Victoria,
Is it possible to also show reporting or originating currency as well as the functional currency?
Thanks,
Audra
LikeLike
Hi Audra,
Just about anything is possible if you have the data. 🙂 In general, reports with other currencies are a lot of additional work and I don’t have much call for them from our typical customers, so I do not have them available to post. If this is something you need help with, we could provide this as a consulting project.
-Victoria
LikeLike
Hi Victoria,
I’m using the basics of the query above to pull payments in. I’m Unioning RM30101 and RM20101, however some of the invoices and credits are doubling up. For example if I have an invoice, payment and credit in the same month for 20.22, when I pull the query its pulling 40.44 for both the credit and the payment. Would you know why this would be? Here is an excerpt from my query.
FROM RM20101 RM (nolock)
inner JOIN SOP10106 SA (nolock)
ON sa.sopnumbe = rm.docnumbr
inner join SOP30300 (nolock)
on sop30300.SOPNUMBE = Rm.DOCNUMBR
inner join GL00105 g (nolock)
on g.actindx = SOP30300.SLSINDX
LikeLike
Hi Diane,
An excerpt is not really enough to understand your code or help you troubleshoot it. Can you attach your entire query? If you don’t want to show the entire thing, at least show a complete query that replicates your issue, including any select, group by, where, etc.
-Victoria
LikeLike
Hi,
Here is the full query:
select
d.Client,
d.GL_Account,
d.CRN,
d.date,
d.Revenue_Stream,
d.Total
from
(SELECT
RM.custnmbr as Client,
g.actnumst as GL_Account,
sa.sopnumbe as CRN,
RM.docdate as date,
SUM(RM.ortrxamt) AS Total,
case sa.USRDEF04
when ” then ‘Unknown’
else sa.usrdef04
end Revenue_Stream,
case sa.USRDEF05
when ” then SA.USRDEF04
else sa.USRDEF05
end Bill_Source_Code
FROM RM20101 RM (nolock)
inner JOIN SOP10106 SA (nolock)
ON sa.sopnumbe = rm.docnumbr
inner join SOP30300 (nolock)
on sop30300.SOPNUMBE = Rm.DOCNUMBR
inner join GL00105 g (nolock)
on g.actindx = SOP30300.SLSINDX
where DOCDATE > DATEADD(Month, -2, GETDATE())
and RMDTYPAL in (‘9’)
and VOIDSTTS= 0
and RM.custnmbr = ‘client’
group by RM.CUSTNMBR,g.actnumst, SA.SOPNUMBE, rm.docdate, SA.USRDEF05, SA.USRDEF04
Union All
SELECT
RM.custnmbr as Client,
g.actnumst as GL_Account,
sa.sopnumbe as CRN,
RM.docdate as Date,
SUM(RM.ortrxamt) AS Total,
case sa.USRDEF04
when ” then ‘Unknown’
else sa.usrdef04
end Revenue_Stream,
case sa.USRDEF05
when ” then SA.USRDEF04
else sa.USRDEF05
end Bill_Source_Code
FROM RM30101 RM (nolock)
inner JOIN SOP10106 SA (nolock)
ON sa.sopnumbe = rm.docnumbr
inner join SOP30300 (nolock)
on sop30300.SOPNUMBE = Rm.DOCNUMBR
inner join GL00105 g (nolock)
on g.actindx = SOP30300.SLSINDX
where DOCDATE > DATEADD(Month, -2, GETDATE())
and RMDTYPAL in (‘9’)
and VOIDSTTS= 0
and RM.custnmbr = ‘client’
group by RM.CUSTNMBR,g.actnumst,SA.SOPNUMBE, rm.docdate, SA.USRDEF05, SA.USRDEF04) d
Thank you
LikeLike
Diane,
Sorry, I can’t see how this would ever return any results. Unless I am misunderstanding this code, you’re selecting all records from RM20101 or RM30101 that are payments (RMDTYPAL 9), then you’re doing an inner join on the payment doc number = SOP number. This should never work unless your users are manually changing the payment number to the SOP invoice number, which seems unlikely.
-Victoria
LikeLike
Victoria,
It does return results, however it only duplicates when there is an invoice, payment and credit in the same month. Otherwise it pulls the correct information. I do see what you mean that its joining on too many items, but I don’t know of any other way to get the actnumst in GL00105, without joining on the SOP30300 table. Do you have any suggestions?
LikeLike
Diane,
If this is getting you results it’s complete coincidence. For example, here is the basic query based on yours, with everything but the link I am talking about taken out:
select rm.*, sop.*
from RM30101 rm
inner join SOP30300 sop
on rm.DOCNUMBR= sop.SOPNUMBE
where rm.RMDTYPAL = 9
I do get a few results from this, but they cannot be relied on. For example, I have a payment number 1002 for customer A dated in 2011. This query is linking it to invoice number 1002 for customer B dated in 2013. You should be able to look at the details of the data you are getting back and see something similar. There is no correlation between a payment number in the RM module and the SOP number in the SOP module, so you cannot link them this way.
Also, I don’t really see where you are using any of the code that I’ve provided here. I would recommend this: start by using the code on this page to create a view. This view will link the payments to the invoices for you properly. Then add in the invoice detail to be able to get the GL accounts.
-Victoria
LikeLike
Hi Victoria, thanks so much for creating the AR Apply SQL view…it makes a great SmartList query. My client has data in the Customer Short Name field they want on their AR Apply SmartList query. I know the Customer Short Name field is in the RM00101 table but I don’t know how to add it to the view. Would it be possible to add the Customer Short Name field to the AR Apply SQL view? I don’t know SQL myself so I need your help to add it to the view. Thanks in advance for any help you can provide.
_Julie
LikeLike
Hi Julie,
I have added the short name for you.
-Victoria
LikeLike
Thank you, thank you, thank you for adding the customer short name field to the AR apply SQL view and doing it so quickly. I appreciate it more than you know!!! Have a great day!!
-Julie
LikeLike
Ok. Thank you!
LikeLike
Victoria – I’m looking for the application records in GP before the Cash Receipts batch have been posted? I see a distribution work table but not an application table? Can you help?
Thanks
Kayla
LikeLike
Kayla,
These are in RM20201 with POSTED = 0.
-Victoria
LikeLike
Victoria,
This SQL view works great. We’ve been running various SmartLists and manually piecing this information together in Excel for years. I was just wondering if there is any way for this view to include the Item Number, Unit of Measure, and Salesperson ID for the applied invoice as well?
Thanks!!!
LikeLike
Hi Mark,
Glad the view is helping you.
To get the line item information, you could link to my view for all SOP lines – or take a portion of that view that only grabs what you need (since you only need a few fields and only from the posted transactions). If you need more help with that, let me know.
Keep in mind that if you have one check that paid 5 invoices, the current view will list this check five times, once for each invoice. If each of those invoices has 5 line items and you add the line item detail, you will now see the same check repeated 25 times, once for each line item.
Also, this might get a bit messy if you have partially applied invoices. Since GP does not apply payments to individual line items, there is no way to tell what line items got paid when an invoice is partially paid. So you would at the very least need to know that when looking at a report like this. If you are doing this for something like commissions reporting you may need to add additional information or calculations to take partial payments into account.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Hi Debbie,
No problem at all, glad you were able to figure it out.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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).
LikeLike
David,
There are many different RM tables…what specifically do you need to show?
-Victoria
LikeLike
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).
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Tom,
You would need a combination of this view and the GL Distributions for AR Transactions view to accomplish something like that.
-Victoria
LikeLike
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,
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Can anyone tell me what the tables are that are associated with the AP report in GP? Thank you
LikeLike
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: https://victoriayudin.com/gp-reports/pm-tables/. And I have published a number of Payables SQL views here: https://victoriayudin.com/gp-reports/.
-Victoria
LikeLike
Many Thanks!
Appreciate your efforts to publishing such a useful codes.
LikeLike
This is great. But how to add all invoices, even not paid yet? Thanks
LikeLike
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
LikeLike