Here is a SQL view that will return all unapplied Receivables transactions in Dynamics GP. This will calculate how many days overdue unapplied transaction are. If something is not overdue, or if it is a credit transaction (payment, credit or return) the Days_Past_Due will be zero. For more SQL code like this, please visit my GP Reports and Receivables SQL Views pages.
CREATE VIEW view_Unapplied_AR_Trx AS -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_Unapplied_AR_Trx -- Created Sep 05, 2009 by Victoria Yudin, Flexible Solutions Inc -- For updates see https://victoriayudin.com/gp-reports/ -- Shows all unapplied Receivables transactions in Functional -- Currency only -- Tables used: -- CM - RM00101 - Customer Master -- CS - RM00103 – Customer Master Summary -- RM - RM20101 - Open Transactions -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ SELECT CM.CUSTNMBR Customer_ID, CM.CUSTNAME Customer_Name, CM.PYMTRMID Customer_Terms, CM.CUSTCLAS Customer_Class, CM.PRCLEVEL Price_Level, CASE RM.RMDTYPAL WHEN 1 THEN 'Sale / Invoice' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' ELSE 'Other' END Document_Type, RM.DOCNUMBR Document_Number, RM.DOCDATE Document_Date, RM.DUEDATE Due_Date, RM.ORTRXAMT Document_Amount, RM.CURTRXAM Unapplied_Amount, CASE WHEN RM.DUEDATE >= GETDATE() THEN 0 WHEN RM.RMDTYPAL in (7,8,9) THEN 0 ELSE DATEDIFF(DD, RM.DUEDATE, GETDATE()) END Days_Past_Due, CS.LASTPYDT Last_Payment_Date, CS.LPYMTAMT Last_Payment_Amount FROM RM20101 RM INNER JOIN RM00101 CM ON RM.CUSTNMBR = CM.CUSTNMBR INNER JOIN RM00103 CS ON RM.CUSTNMBR = CS.CUSTNMBR WHERE RM.VOIDSTTS = 0 AND RM.CURTRXAM <> 0 -- add permissions for DYNGRP GO GRANT SELECT ON view_Unapplied_AR_Trx 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
Perfect, very helpful.
LikeLike
Hi Victoria,
Found this while searching for an answer to a request from our Controller. She wants to know “if there is a way to have GP pop-up an alert while entering an order that the customer potentially has a credit that they “could” apply towards their order?” Is there a way something could be developed to do this – using your view as the source?
Thanks,
Mike
LikeLike
Hi Mike,
Just about anything can be done with a customization. 🙂
-Victoria
LikeLike
Victoria, I’m sure it’s been said many times before.. but I’ll re-iterate it…. YOU ROCK!
LikeLike
Awwww – thanks Mike! 🙂
-Victoria
LikeLike
Hi Victoria,
This will replace the “half-baked” query I was writing to do the same thing. Thanks!
I’m creating it for delivery by email, weekly, to our sales reps. My question is this….is it possible to create this same view but display the result in originating currency? I realize such a report would not necessarily tie to anything… I would filter by salesperson ID at the time of report delivery, so it does not need to tie to anything.
Thanks for your continuing contribution here.
Cheers
Ben
Financial Manager/Controller
5050 Central
LikeLike
Hi Ben,
It should certainly be possible, however, because the originating amounts are not stored in the same table for RM transactions, you would need to link in the MC020102 table for them. I believe you can link on
RM20101.RMDTYPAL = MC020102.RMDTYPAL AND RM20101.DOCNUMBR = MC020102.DOCNUMBR
and just add on to this view so you do not have to start from scratch.-Victoria
LikeLike
Victoria
Do you know of a fix that allows a appy payment to be fixed when the AR document is now looking like it had a casy apply but in fact it did not because end-user being knocked out during a cession. The payment shows open but no document to apply to. The AR Document shows open but no cash to apply to. I think the RM20101 AND the RM20201 are out of sync for this:
The customer ID is (‘101427’), the payment is (‘PYMNT0005822’)
and the AR DOCUMENT is (‘184782’)
LikeLike
Ron,
This can typically be fixed by running Check Links and then Reconcile. Have you tried that?
-Victoria
LikeLike
Hi Victoria,
I’ve been meaning to post my query….this is used in a Report Server Aged AR Report that is filtered by sales rep ID and sent by e-mail weekly. It shows the balance due in originating currency as well and it’s been working great.
Cheers
Ben
LikeLike
Thanks Ben,
I had to fix the code a little on the next to last line – I think the comments strip out less than and greater than signs. Hopefully I got it back to what you intended. Also, I took out references to the database name. 🙂
-Victoria
LikeLike
Victoria,
Thanks for the direct response. Oh, i forgot to tell you that i need the Sales/Invoice for the Receivables Management modules. Can you direct to me(if it’s not too much to ask) which RM tables can i extract the data for the unposted invoice entry and sales transactions?
Regards,
Neil
LikeLike
Neil,
Unposted invoices entered in the Receivables Management module are in the RM10301 table.
-Victoria
LikeLike
Hi Victoria,
Looked throughout the web just for some GP SQL Transactional queries but brought me no luck not until your Blog came out like a charm.
I have this question that I hope you could help me with.
I wanted to create a view that would generate the Unposted Invoice Entries and RM_Sales. Is there any possible means to do that? Since honestly, my bossed just dumped this task on me and i got lost in the middle not knowing all the tables of GP and where stuffs go after the transactions. I am new to GP Views handling.
Hoping for your reply. Thank you very much.
LikeLike
Neil,
GP has multiple modules where you can enter sales and each has their own set of tables. You will need to find out what modules you are using as a start. I have pages with table listings for 2 of these modules:
-Victoria
LikeLike
Hi Victoria,
I am using your view to display in smartlist the unapplied ar trx. My users are requesting that the document amount be displayed in negative for document type payments and credit memos. Is that possible? How do I do this in Smartlist Builder?
I apologize if this is not the right place for this question.
Regards,
Saras
LikeLike
Hi Saras,
That’s a great question. I would change the SQL view slightly to accomplish this. To do this, you can replace the line that says
RM.ORTRXAMT Document_Amount,
with the following:
CASE
WHEN RM.RMDTYPAL > 6 THEN RM.ORTRXAMT * -1
ELSE RM.ORTRXAMT
END Document_Amount,
You might also want to do something similar for the unapplied amount:
CASE
WHEN RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE RM.CURTRXAM
END Unapplied_Amount,
-Victoria
LikeLike
Hi Victoria,
Thanks for the prompt reply. How do I set it in Smartlist Builder? When I run the View in SQL Server, I can see the amounts are negative for the relevant doc types. But they still appear as positive in Smartlist. How do I set the display to negative in Smartlist Builder?
Regards,
Saras
LikeLike
Saras,
Since you’re not adding or changing any columns in the view, SmartList should automatically pick up the changes. I would check a few things:
If none of that helps, you may want to try creating a new SmartList based on the revised view to make sure it is picking up the changes properly.
-Victoria
LikeLike
Hi Victoria,
I guess I didn’t update the Smartlist correctly yesterday. I recreated the smartlist and it works fine. Thank you so much for the help.
Regards,
Saras
LikeLike
Hi Victoria,
It is a great resource!!!
Well is there a view or sql script which would be showing invoices with applied payments, credit memos etc., along with unapplied / due amount for that same invoice.
Tnanks,
Abdul Wasay
LikeLike
Abdul,
I believe my AR apply detail view can accomplish this.
-Victoria
LikeLike
It really helpful for tech. developers….:)
LikeLike