SQL view for all unapplied Receivables transactions in Dynamics GP


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

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

  1. 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

    Like

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

    Like

  3. 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

    Like

    • 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

      Like

      • 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’)

        Like

      • 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

        Like

        • 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

          Like

  4. 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

    Like

  5. 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.

    Like

  6. 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

    Like

    • 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

      Like

      • 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

        Like

        • 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

          Like

          • 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

            Like

  7. 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

    Like

  8. It really helpful for tech. developers….:)

    Like

Trackbacks/Pingbacks

  1. 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. […]

    Like

  2. 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 […]

    Like

  3. 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 […]

    Like

  4. 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 […]

    Like

  5. 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 […]

    Like

  6. 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 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: