SQL view with AR apply detail

This has been a hot topic in the newsgroups lately and several people have asked me if I have any code for Receivables (AR) apply information in Dynamics GP. Below is a view that should help you get started if you’re building a report to show AR apply information in Dynamics GP.

For more views like this, check out my GP Reports page.

For help with using this in SmartList Builder, take a look at my post on How to use a SQL view in SmartList Builder.

~~~~~

CREATE VIEW view_AR_Apply_Detail
AS

/*******************************************************************
view_AR_Apply_Detail
Created on Feb 15, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
Returns apply detail for all posted receivables transactions.
Only shows functional currency amounts.
Credit documents applied to more than one debit document
	will return multiple lines.
Tables used:
RM00101 – Customer Master
RM20101 - Open Transactions
RM20201 – Open Transactions Apply
RM30101 – Historical Transactions
RM30201 – Historical Transactions Apply
*******************************************************************/

SELECT   T.CUSTNMBR Customer_ID,
	CM.CUSTNAME Customer_Name,
	T.DOCDATE Document_Date,
	T.GLPOSTDT GL_Posting_Date,
	CASE T.RMDTYPAL
	     WHEN 7 THEN 'Credit Memo'
              WHEN 8 THEN 'Return'
              WHEN 9 THEN 'Payment'
              END AS RM_Doc_Type,
	T.docTypeNum Document_Type_and_Number,
	T.DOCNUMBR Document_Number,
	T.ORTRXAMT Original_Trx_Amount,
	T.CURTRXAM Current_Trx_Amount,
	T.amountApplied Total_Applied_Amount,
	A.APPTOAMT Amount_Applied,
	A.APTODCTY Applied_to_Doc_Type,
	A.debitType Applied_to_Doc_Type_Name,
	A.APTODCNM  Applied_to_Doc_Number,
	A.APTODCDT Applied_to_Document_Date,
	A.ApplyToGLPostDate Applied_to_GL_Posting_Date,
	A.DISTKNAM Discount,
	A.WROFAMNT Writeoff,
         A.DATE1 Apply_Document_Date,
         A.GLPOSTDT Apply_GL_Posting_Date

FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
	CASE RMDTYPAL
	  WHEN 7 THEN 'Credit Memo'
	  WHEN 8 THEN 'Return'
	  WHEN 9 THEN
	    CASE CSHRCTYP
	      WHEN 0 THEN 'Payment - Check ' +
   	        CASE CHEKNMBR
	          WHEN '' THEN ''
		 ELSE '#' + CHEKNMBR
		 END
	      WHEN 1 THEN 'Payment - Cash'
	      WHEN 2 THEN 'Payment - Credit Card'
	      END
	  END AS docTypeNum,
	DOCNUMBR, ORTRXAMT, CURTRXAM,
	ORTRXAMT - CURTRXAM amountApplied 

	FROM RM20101
	WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) 

	UNION 

	SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
	CASE RMDTYPAL
	  WHEN 7 THEN 'Credit Memo'
	  WHEN 8 THEN 'Return'
	  WHEN 9 THEN
	    CASE CSHRCTYP
	      WHEN 0 THEN 'Payment - Check ' +
	        CASE CHEKNMBR
		 WHEN '' THEN ''
		 ELSE '#' + CHEKNMBR
		 END
	        WHEN 1 THEN 'Payment - Cash'
	        WHEN 2 THEN 'Payment - Credit Card'
	        END
	  END AS docTypeNum,
	DOCNUMBR, ORTRXAMT, CURTRXAM,
	ORTRXAMT - CURTRXAM amountApplied 

	FROM RM30101
	WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)) T 

INNER JOIN RM00101 CM
	ON T.CUSTNMBR = CM.CUSTNMBR 

INNER JOIN
	(SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM,
	APFRDCTY,APFRDCNM,
	CASE APTODCTY
	  WHEN 1 THEN 'Sale / Invoice'
	  WHEN 2 THEN 'Scheduled Payment'
	  WHEN 3 THEN 'Debit Memo'
	  WHEN 4 THEN 'Finance Charge'
	  WHEN 5 THEN 'Service Repair'
	  WHEN 6 THEN 'Warranty'
	  END as debitType,
	APPTOAMT, ApplyToGLPostDate, APTODCDT, tO2.DISTKNAM,
	tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT 

	FROM RM20201 tO2 

	INNER JOIN RM20101 tO1
	ON tO2.APTODCTY = tO1.RMDTYPAL
           AND tO2.APTODCNM = tO1.DOCNUMBR 

	UNION 

	SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM,
	APFRDCTY, APFRDCNM,
	CASE APTODCTY
	  WHEN 1 THEN 'Sale / Invoice'
	  WHEN 2 THEN 'Scheduled Payment'
	  WHEN 3 THEN 'Debit Memo'
	  WHEN 4 THEN 'Finance Charge'
	  WHEN 5 THEN 'Service Repair'
	  WHEN 6 THEN 'Warranty'
	  END AS debitType,
	APPTOAMT, ApplyToGLPostDate, APTODCDT, tH2.DISTKNAM,
         tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT
	FROM RM30201 tH2 

	INNER JOIN RM30101 tH1
	ON tH2.APTODCTY = tH1.RMDTYPAL
	  AND tH2.APTODCNM = tH1.DOCNUMBR) A 

ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR

/** 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_AR_Apply_Detail 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.