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