September 5, 2009
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 other SQL code, please visit my GP Reports page.
~~~~~
CREATE VIEW view_Unapplied_AR_Trx AS /**************************************************************** view_Unapplied_AR_Trx Created Sep 05, 2009 by Victoria Yudin - Flexible Solutions, Inc. For updates see http://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 /** 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_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
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
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.DOCNUMBRand just add on to this view so you do not have to start from scratch.-Victoria
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
Neil,
Unposted invoices entered in the Receivables Management module are in the RM10301 table.
-Victoria
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.
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
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
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:
CASEWHEN 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:
CASEWHEN RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE RM.CURTRXAM
END Unapplied_Amount,
-Victoria
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
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
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
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
Abdul,
I believe my AR apply detail view can accomplish this.
-Victoria
It really helpful for tech. developers….:)