Aged Trial Balance report for GP AR module

The following view can be used to generate the aged trial balance report for GP AR module, the view will return all the paid / unpaid  receivables transactions in Dynamics GP of the following document types:

·        'Sale / Invoice'

·        'Debit Memo'

·        'Finance Charge'

·        'Service Repair'

·        'Warranty'

 Also this view will calculate how many days overdue unapplied transaction and will allocate the aged bucket of each transaction.

Note: this view will not show the credit documents (payments, return etc)



 CREATE VIEW DYNSOL_View_AR_AgedTB

AS

SELECT CR.CUSTNMBR [Customer No],

       CR.CUSTNAME [Customer Name],

       CR.PYMTRMID [Payment Terms],

       CR.CUSTCLAS [Customer Class],

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

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

       SUMR.LASTPYDT [Last Payment Date],

       SUMR.LPYMTAMT [Last Payment Amount],

          CASE WHEN CURTRXAM <> 0

          THEN        

               CASE

          WHEN RM.DUEDATE >= GETDATE() THEN 0

          ELSE DATEDIFF(DD, RM.DUEDATE, GETDATE())

          END

       ELSE 0 END   [No of Days Past],

 

       CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 0 and 30 THEN (RM.CURTRXAM) else 0 END  AS [0-30],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 31 and 60 THEN (RM.CURTRXAM) else 0 END  AS [31-60],  

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 61 and 90 THEN (RM.CURTRXAM) else 0 END  AS [61-90],  

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 91 and 120 THEN (RM.CURTRXAM) else 0 END  AS [91-120],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 121 and 150 THEN (RM.CURTRXAM) else 0 END  AS [121-150],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 151 and 180 THEN (RM.CURTRXAM) else 0 END  AS [151-180],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 >= 181  THEN (RM.CURTRXAM) else 0 END  AS [181=>]

FROM   RM20101 RM

     INNER JOIN

       RM00101 CR

       ON RM.CUSTNMBR = CR.CUSTNMBR

     INNER JOIN

       RM00103 SUMR

       ON RM.CUSTNMBR = SUMR.CUSTNMBR

WHERE  RM.VOIDSTTS = 0 AND RM.RMDTYPAL in (1,2,3,4,5,6)