SQL view for all unapplied Receivables transactions in Dynamics GP

September 5, 2009 by Victoria Yudin 29 Comments

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

Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code

GP Reports code, GP SQL view, Receivables, SQL code

Service Pack 4 for Dynamics GP 10

What’s with all these dates?

29 Responses to “SQL view for all unapplied Receivables transactions in Dynamics GP”

hyou91 Reply September 7, 2020 at 6:41 am

Perfect, very helpful.

LikeLike
Mike Bailey Reply July 18, 2014 at 8:31 am

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
- Victoria Yudin Reply July 18, 2014 at 9:08 am
  
  Hi Mike,
  
  Just about anything can be done with a customization. 🙂
  
  -Victoria
  
  LikeLike
Mike Reply April 18, 2013 at 3:09 pm

Victoria, I’m sure it’s been said many times before.. but I’ll re-iterate it…. YOU ROCK!

LikeLike
- Victoria Yudin Reply April 18, 2013 at 3:16 pm
  
  Awwww – thanks Mike! 🙂
  
  -Victoria
  
  LikeLike
Ben Reply January 25, 2012 at 6:48 am

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
- Victoria Yudin Reply January 25, 2012 at 11:52 am
  
  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
  - Ron McVciar Reply March 9, 2012 at 2:15 pm
    
    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
    - Victoria Yudin Reply March 9, 2012 at 2:19 pm
      
      Ron,
      
      This can typically be fixed by running Check Links and then Reconcile. Have you tried that?
      
      -Victoria
      
      LikeLike
  - Ben Reply July 27, 2012 at 2:33 pm
    
    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.
    
    select T2.SLPRSNID as Salesperson_ID, T1.CUSTNMBR as Customer_Number, T2.CUSTNAME as Customer_Name, T2.CNTCPRSN as Contact_Person, T2.PHONE1 as Phone_1, T1.DOCNUMBR as Document_Number, T1.DOCDATE as Document_Date, T1.RMDTYPAL as RM_Document_Type_All, T4.ORCTRXAM as Originating_Current_Trx_Amount, T1.CURTRXAM as Current_Trx_Amount, T4.ORCTRXAM as CurrentOC, T3.LPYMTAMT as Last_Payment_Amount, T3.LASTPYDT as Last_Payment_Date, T2.CURNCYID as Currency_ID, T1.AGNGBUKT as Aging_Bucket from RM20101 T1 with (nolock) INNER JOIN RM00101 T2 with (nolock) on T2.[CUSTNMBR] = T1.[CUSTNMBR] INNER JOIN RM00103 T3 with (nolock) on T3.[CUSTNMBR] = T1.[CUSTNMBR] LEFT JOIN MC020102 T4 with (nolock) on T4.[RMDTYPAL] = T1.[RMDTYPAL] and T4.[DOCNUMBR] = T1.[DOCNUMBR] where T1.[CURTRXAM] <> 0 ORDER BY Document_Date
    
    Cheers
    Ben
    
    LikeLike
    - Victoria Yudin Reply July 27, 2012 at 4:17 pm
      
      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
Neil Reply November 4, 2011 at 8:11 pm

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
- Victoria Yudin Reply November 5, 2011 at 9:15 am
  
  Neil,
  
  Unposted invoices entered in the Receivables Management module are in the RM10301 table.
  
  -Victoria
  
  LikeLike
Neil Reply November 4, 2011 at 4:09 am

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
- Victoria Yudin Reply November 4, 2011 at 9:00 am
  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:
  
  Receivables Management (RM)
  
  Sales Order Processing (SOP)
  
  -Victoria
  
  LikeLike
saras Reply June 28, 2011 at 5:37 am

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
- Victoria Yudin Reply June 28, 2011 at 5:48 am
  
  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
  - Saras Reply June 29, 2011 at 12:09 am
    
    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
    - Victoria Yudin Reply June 29, 2011 at 5:50 am
      
      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:
      
      Confirm that the view really got updated and in the right database(s)
      
      Confirm that your SmartList is not set up to change signs for anything, if you already tried to add logic to the SmartList, then the changes in the view may be counteracting that logic
      
      Confirm that the SmartList is pointing at the right view
      
      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
      - Saras Reply June 29, 2011 at 10:46 pm
        
        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
Abdul Wasay Reply March 23, 2011 at 4:07 am

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
- Victoria Yudin Reply March 23, 2011 at 5:10 am
  
  Abdul,
  
  I believe my AR apply detail view can accomplish this.
  
  -Victoria
  
  LikeLike
Tauqir Abbas Reply September 11, 2009 at 2:44 pm

It really helpful for tech. developers….:)

LikeLike

Trackbacks/Pingbacks

DynamicAccounting.net | SQL View for Unapplied ReceivablesSQL View for Unapplied Receivables - DynamicAccounting.net - April 25, 2016
[…] Posted on September 8, 2009 by Mark Polino Victoria Yudin has a great new post up with SQL code to show all unapplied Receivables transactions in Dynamics GP. […]

LikeLike
SQL stored procedure for all open AR transactions by customer name | Victoria Yudin - November 24, 2014
[…] little variation on my Unapplied AR Transactions view, this SQL stored procedure will return all open (unapplied) receivables transactions for customers […]

LikeLike
SQL view for current Receivables aging in Dynamics GP | Victoria Yudin - January 26, 2012
[…] 25, 2012 by Victoria Yudin 2 Comments I have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return […]

LikeLike
Interesting Findings & Knowledge Sharing » SQL view for current Receivables aging in Dynamics GP - January 25, 2012
[…] have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return […]

LikeLike
SQL view for current Receivables aging in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 25, 2012
[…] 0 I have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return […]

LikeLike
SQL View for Unapplied Receivables - DynamicAccounting.net - September 8, 2009
[…] View for Unapplied Receivables Victoria Yudin has a great new post up with SQL code to show all unapplied Receivables transactions in Dynamics GP. Published: Tuesday, September 08, 2009, 03:00 […]

LikeLike