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 https://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
-- Updated Aug 12, 2010 to add original total of Applied
--      to Doc and Applied To Doc Paid Off date
-- Updated Mar 23, 2011 to add unapplied amount of 
--      Applied to Doc
-- Updated Jul 10, 2013 to add Payment Batch and PO Number
-- Updated Jul 15, 2013 to add Short Name
-- ************************************************************

SELECT  T.CUSTNMBR Customer_ID,
	CM.CUSTNAME Customer_Name,
	CM.SHRTNAME Short_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.BACHNUMB Payment_Batch,
	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,
	D.ORTRXAMT Applied_To_Doc_Total,
	D.DINVPDOF Applied_To_Date_Paid_Off,
	D.CURTRXAM Applied_To_Doc_Unapplied_Amount,
	D.CSPORNBR Customer_PO_Number

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

INNER JOIN
	(SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, 
         CURTRXAM, CSPORNBR
	 FROM RM20101

	UNION 

	 SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, 
         CURTRXAM = 0, CSPORNBR
	 FROM RM30101) D

ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.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.

68 Responses to “SQL view with AR apply detail”

  1. Mathew Bitzegaio Reply June 28, 2017 at 2:25 pm

    Victoria,

    If we do a SOP return, will it create a record in the RM20201 and/or RM30201 tables when it gets applied to an invoice? I wasn’t sure if SOP Return application was stored elsewhere in GP, or in these same tables with the other RM application data.

    Thanks!

    Matt

    Like

    • Hi Matt,

      When you post the return in SOP, it will post to RM20101 with an RMDTYPAL of 8. When you apply it to an invoice, it will add a record to RM20201. (When moved to history, it will move to RM30201.) In general, SOP invoices and returns are treated the same as RM invoices and returns once posted.

      Hope that helps.
      -Victoria

      Like

  2. Victoria,

    My company uses GP Project to manage and bill our grants. I noticed in your resources the GP tables for Project are not listed. When I process a billing transaction from Project it sends the bill to the A/R module for application of the payment. I could use a report that lists the payments against the bills (including payment and bill numbers and amount of the payment applied against each bill), the expenditure account number, the A/R account number, the revenue account number. It appears that the code above would be a good starting point to gather this information. Coded in the Project Card under Budget, under a specific cost category, under Accounts, and in the Time and Materials Accounts section; the expenditure, A/R, and revenue accounts are specifically identified. Is it possible to get to this information and include it against the report that I need?

    Kris

    Like

    • Hi Kris,

      If the account numbers you need are in the distributions of the AR invoice, you may not need to go back to the project module for them. You could combine this code with the code from my view.

      I don’t foresee adding any details for the Project tables to my blog, as there are not that many companies (at least that I work with) that use Project Accounting module and I almost never write any code for it.

      -Victoria

      Like

      • Victoria,

        Thank you for your reply. The original expenditure account is not included in the distributions of the AR invoice (bill document).

        Kris

        Like

        • Hi Kris,

          Gotcha. In that case, you are going to have to link to the Project tables. If you don’t know them, you may need to talk to your GP Partner or Microsoft support to get help with finding the right tables and ways to link to them. Sorry not to be able to help more with this.

          -Victoria

          Like

      • This report is exactly what my company needs, except we need to know what Customer Name and Salesperson ID is attached to the invoice the payment was applie to, not necessarily the customer the payment was posted to. The reason being is that when we run the report, if the payment was posted to the National account, we can’t tell what customer it actually was being applied to. The national account may have several different salespeople invoicing for it. Do you have any recommendations of changes to the SQL?

        Like

        • Hi Trudy,

          Here is the code to accomplish that:

          SELECT T.CUSTNMBR Customer_ID,
          CM.CUSTNAME Customer_Name,
          CM.SHRTNAME Short_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.BACHNUMB Payment_Batch,
          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,
          D.ORTRXAMT Applied_To_Doc_Total,
          D.DINVPDOF Applied_To_Date_Paid_Off,
          D.CURTRXAM Applied_To_Doc_Unapplied_Amount,
          D.CSPORNBR Customer_PO_Number,
          D.CUSTNMBR Applied_To_Customer_ID,
          D.SLPRSNID Applied_To_Salesperson_ID
          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, BACHNUMB,
          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, BACHNUMB,
          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
          INNER JOIN
          (SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
          CURTRXAM, CSPORNBR, CUSTNMBR, SLPRSNID
          FROM RM20101
          UNION
          SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
          CURTRXAM = 0, CSPORNBR, CUSTNMBR, SLPRSNID
          FROM RM30101) D
          ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR

          -Victoria

          Like

          • Thank you so much Victoria!

            In addition to the Customer number, how do I add the customer name from the invoice and the salesperson from the customer card that coincides with that customer name? I would also need the document date from the original invoice that I am applying the payment to.

            Like

            • Hi Trudy,

              The applied to document date is already there. I just added the customer name and changed the salesperson to be taken from the applied to customer, not the transaction.

              SELECT T.CUSTNMBR Customer_ID,
              CM.CUSTNAME Customer_Name,
              CM.SHRTNAME Short_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.BACHNUMB Payment_Batch,
              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,
              D.ORTRXAMT Applied_To_Doc_Total,
              D.DINVPDOF Applied_To_Date_Paid_Off,
              D.CURTRXAM Applied_To_Doc_Unapplied_Amount,
              D.CSPORNBR Customer_PO_Number,
              D.CUSTNMBR Applied_To_Customer_ID,
              AC.CUSTNAME Applied_To_Customer_Name,
              AC.SLPRSNID Applied_To_Salesperson_ID
              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, BACHNUMB,
              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, BACHNUMB,
              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
              INNER JOIN
              (SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
              CURTRXAM, CSPORNBR, CUSTNMBR, SLPRSNID
              FROM RM20101
              UNION
              SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
              CURTRXAM = 0, CSPORNBR, CUSTNMBR, SLPRSNID
              FROM RM30101) D
              ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR
              LEFT OUTER JOIN RM00101 AC
              ON D.CUSTNMBR = AC.CUSTNMBR

              -Victoria

              Like

          • Victoria,

            This is exactly what we need. Thank you!

            Like

  3. Victoria
    I am using this code to get customers that have order since a given date:
    (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
    case when RMDTYPAL = ‘1’ then (SLSAMNT + MISCAMNT – TRDISAMT)
    else 0
    end Amount
    FROM RM20101
    WHERE VOIDSTTS = 0
    and DOCDATE > dateadd(mm, -24, dateadd(mm, datediff(mm, 0, getdate()), 0))
    UNION ALL
    SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
    case when RMDTYPAL = ‘1’ then (SLSAMNT + MISCAMNT – TRDISAMT)
    else 0
    end Amount
    FROM RM30101
    WHERE VOIDSTTS = 0
    and DOCDATE > dateadd(mm, -24, dateadd(mm, datediff(mm, 0, getdate()), 0))) T
    LEFT OUTER JOIN RM00101 C — customer master
    ON T.CUSTNMBR = C.CUSTNMBR
    GROUP BY T.CUSTNMBR, C.CUSTNAME

    I would like to get a list of customers that have not ordered (inactive customers) since a given date:
    Can you please help me with this?
    thanks,

    Like

    • Ricky,

      First things first, you need to define how you determine if customer ‘has not ordered’. Let’s, for example, say that means we have not invoiced them. If we can use that logic, then the following query will get you all customers set up in GP that have not been invoiced since the date you specify on the first line:

      declare @date datetime = ‘2014-12-31’
      select * from RM00101
      where CUSTNMBR not in
      (select CUSTNMBR from RM20101
      where RMDTYPAL = 1 and VOIDSTTS = 0
      and DOCDATE > @date)
      and CUSTNMBR not in
      (select CUSTNMBR from RM30101
      where RMDTYPAL = 1 and VOIDSTTS = 0
      and DOCDATE > @date)

      Hope that helps,
      -Victoria

      Like

  4. Hi Victoria,

    Hope all is well! I am trying to export a smartlist containing this view to excel. However there are some formatting issues with the document_date and document_number . All date columns in the cell read =Date(2014,12,31) as well as the formula bar. Document_Number cell reads #NAME? but the formula bar reads the correct document number. The Apply_to_doc_number column does read the cell and formula bar correctly. Have you seen this before? I do receive the following error when exporting to excel ” We found a problem with some content in “worksheet name” Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes.” I click yes and get this error “Excel was able to open the file by repairing or removing the unreadable content. ” Repaired Records: Cell information from “worksheet name” I was able to export other smartlist successfully.

    Thanks for your help!

    Like

    • Hi Shelly Ann,

      I have not seen this before. I have set up this same view in SmartList Builder for a number of customers, I just tested exporting to Excel on one of their systems, everything looks fine.

      I would suspect some system specific settings causing this. First place I would check is the regional settings on the computer where you are seeing this problem.

      -Victoria

      Like

  5. Hi Victoria,

    Okay so I was able to create the view. The only issue I am having is that if someone posted an open receipt it is not showing up in this report. I don’t know if it is because it is based on the applied document, but if the receipt wasn’t applied to any document I still need to see that cash amount – preferably in the column that you added “unapplied amount” (which is great by the way). What would you recommend I do to pull ALL cash receipts in a given month into this report?

    Like

  6. Hi Victoria,

    Is it possible to also show reporting or originating currency as well as the functional currency?

    Thanks,
    Audra

    Like

    • Hi Audra,

      Just about anything is possible if you have the data. 🙂 In general, reports with other currencies are a lot of additional work and I don’t have much call for them from our typical customers, so I do not have them available to post. If this is something you need help with, we could provide this as a consulting project.

      -Victoria

      Like

  7. Hi Victoria,
    I’m using the basics of the query above to pull payments in. I’m Unioning RM30101 and RM20101, however some of the invoices and credits are doubling up. For example if I have an invoice, payment and credit in the same month for 20.22, when I pull the query its pulling 40.44 for both the credit and the payment. Would you know why this would be? Here is an excerpt from my query.
    FROM RM20101 RM (nolock)
    inner JOIN SOP10106 SA (nolock)
    ON sa.sopnumbe = rm.docnumbr

    inner join SOP30300 (nolock)
    on sop30300.SOPNUMBE = Rm.DOCNUMBR

    inner join GL00105 g (nolock)
    on g.actindx = SOP30300.SLSINDX

    Like

    • Hi Diane,

      An excerpt is not really enough to understand your code or help you troubleshoot it. Can you attach your entire query? If you don’t want to show the entire thing, at least show a complete query that replicates your issue, including any select, group by, where, etc.

      -Victoria

      Like

      • Hi,
        Here is the full query:
        select
        d.Client,
        d.GL_Account,
        d.CRN,
        d.date,
        d.Revenue_Stream,
        d.Total

        from
        (SELECT
        RM.custnmbr as Client,
        g.actnumst as GL_Account,
        sa.sopnumbe as CRN,
        RM.docdate as date,
        SUM(RM.ortrxamt) AS Total,
        case sa.USRDEF04
        when ” then ‘Unknown’
        else sa.usrdef04
        end Revenue_Stream,
        case sa.USRDEF05
        when ” then SA.USRDEF04
        else sa.USRDEF05
        end Bill_Source_Code

        FROM RM20101 RM (nolock)
        inner JOIN SOP10106 SA (nolock)
        ON sa.sopnumbe = rm.docnumbr

        inner join SOP30300 (nolock)
        on sop30300.SOPNUMBE = Rm.DOCNUMBR

        inner join GL00105 g (nolock)
        on g.actindx = SOP30300.SLSINDX

        where DOCDATE > DATEADD(Month, -2, GETDATE())
        and RMDTYPAL in (‘9’)
        and VOIDSTTS= 0
        and RM.custnmbr = ‘client’
        group by RM.CUSTNMBR,g.actnumst, SA.SOPNUMBE, rm.docdate, SA.USRDEF05, SA.USRDEF04

        Union All

        SELECT
        RM.custnmbr as Client,
        g.actnumst as GL_Account,
        sa.sopnumbe as CRN,
        RM.docdate as Date,
        SUM(RM.ortrxamt) AS Total,
        case sa.USRDEF04
        when ” then ‘Unknown’
        else sa.usrdef04
        end Revenue_Stream,
        case sa.USRDEF05
        when ” then SA.USRDEF04
        else sa.USRDEF05
        end Bill_Source_Code

        FROM RM30101 RM (nolock)
        inner JOIN SOP10106 SA (nolock)
        ON sa.sopnumbe = rm.docnumbr

        inner join SOP30300 (nolock)
        on sop30300.SOPNUMBE = Rm.DOCNUMBR

        inner join GL00105 g (nolock)
        on g.actindx = SOP30300.SLSINDX

        where DOCDATE > DATEADD(Month, -2, GETDATE())
        and RMDTYPAL in (‘9’)
        and VOIDSTTS= 0
        and RM.custnmbr = ‘client’
        group by RM.CUSTNMBR,g.actnumst,SA.SOPNUMBE, rm.docdate, SA.USRDEF05, SA.USRDEF04) d

        Thank you

        Like

        • Diane,

          Sorry, I can’t see how this would ever return any results. Unless I am misunderstanding this code, you’re selecting all records from RM20101 or RM30101 that are payments (RMDTYPAL 9), then you’re doing an inner join on the payment doc number = SOP number. This should never work unless your users are manually changing the payment number to the SOP invoice number, which seems unlikely.

          -Victoria

          Like

          • Victoria,
            It does return results, however it only duplicates when there is an invoice, payment and credit in the same month. Otherwise it pulls the correct information. I do see what you mean that its joining on too many items, but I don’t know of any other way to get the actnumst in GL00105, without joining on the SOP30300 table. Do you have any suggestions?

            Like

            • Diane,

              If this is getting you results it’s complete coincidence. For example, here is the basic query based on yours, with everything but the link I am talking about taken out:

              select rm.*, sop.*
              from RM30101 rm
              inner join SOP30300 sop
              on rm.DOCNUMBR= sop.SOPNUMBE
              where rm.RMDTYPAL = 9

              I do get a few results from this, but they cannot be relied on. For example, I have a payment number 1002 for customer A dated in 2011. This query is linking it to invoice number 1002 for customer B dated in 2013. You should be able to look at the details of the data you are getting back and see something similar. There is no correlation between a payment number in the RM module and the SOP number in the SOP module, so you cannot link them this way.

              Also, I don’t really see where you are using any of the code that I’ve provided here. I would recommend this: start by using the code on this page to create a view. This view will link the payments to the invoices for you properly. Then add in the invoice detail to be able to get the GL accounts.

              -Victoria

              Like

  8. Hi Victoria, thanks so much for creating the AR Apply SQL view…it makes a great SmartList query. My client has data in the Customer Short Name field they want on their AR Apply SmartList query. I know the Customer Short Name field is in the RM00101 table but I don’t know how to add it to the view. Would it be possible to add the Customer Short Name field to the AR Apply SQL view? I don’t know SQL myself so I need your help to add it to the view. Thanks in advance for any help you can provide.
    _Julie

    Like

  9. Victoria – I’m looking for the application records in GP before the Cash Receipts batch have been posted? I see a distribution work table but not an application table? Can you help?

    Thanks
    Kayla

    Like

  10. Victoria,

    This SQL view works great. We’ve been running various SmartLists and manually piecing this information together in Excel for years. I was just wondering if there is any way for this view to include the Item Number, Unit of Measure, and Salesperson ID for the applied invoice as well?

    Thanks!!!

    Like

    • Hi Mark,

      Glad the view is helping you.

      To get the line item information, you could link to my view for all SOP lines – or take a portion of that view that only grabs what you need (since you only need a few fields and only from the posted transactions). If you need more help with that, let me know.

      Keep in mind that if you have one check that paid 5 invoices, the current view will list this check five times, once for each invoice. If each of those invoices has 5 line items and you add the line item detail, you will now see the same check repeated 25 times, once for each line item.

      Also, this might get a bit messy if you have partially applied invoices. Since GP does not apply payments to individual line items, there is no way to tell what line items got paid when an invoice is partially paid. So you would at the very least need to know that when looking at a report like this. If you are doing this for something like commissions reporting you may need to add additional information or calculations to take partial payments into account.

      -Victoria

      Like

  11. Good morning,

    I have installed the AR Apply detail view but i’m finding there are a few invoices that do not show up on the report. The invoices have been applied just like the rest and have not been voided. I ran check links on the system, is there anything else you can suggest to get all the pertinent data listed in the report?

    Like

    • Hi Debbie,

      Do the payments (or credits or returns) that were applied to the missing invoices show up on the results? Did you make any changes to the code I have posted, or use it as it is? Are you looking at the results directly in SQL or somewhere else?

      -Victoria

      Like

      • No, nothing(invoice or payment) for that customer is showing in the results but i can open it in inquiry/sales/transaction by customer.

        I used both SQL and smartlist to view the results. I copied the view directly as you have it, no changes were made to it.

        Like

        • Hi Debbie,

          This code is performing a number of ‘inner joins’, meaning it will only return records where everything matches up and all related records exist in all tables. First thing I would check is what the Balance Type for this customer is – if it is ‘Balance Forward’, then there will be no apply records stored for that customer. Next, I would verify that apply records exist for this customer (maybe history has been deleted?) by running the following code (just change the customer ID on the bottom line to yours):

          select a.* from 
          (select TableSource = 'Open',  CUSTNMBR Customer, 
          count(*) ApplyRecords from RM20201 
          group by CUSTNMBR
          union all 
          select TableSource = 'History',  CUSTNMBR Customer, 
          count(*) ApplyRecords from RM30201
          group by CUSTNMBR) a
          where a.Customer = 'aaronfit0001' --change this
          

          Please let me know what you find.

          -Victoria

          Like

  12. Quick question for you on the dates in the RM Apply Table. I’m finding discrepancies in the apply date (DATE1 vs GLPOSTDT) in my customer’s data. In my case the dates matter so it’s throwing me off. Does this make sense to you? It doesn’t to me! : )

    Recent example: the customer imports a payment file nightly (cash receipts) all dated April 30th. Posts them via a third party product (postmaster). They have another nightly routine that runs to auto-apply payments to invoices overnight. They are using system date for that, so the apply date was May 1st.

    I’m expecting the customers to appear on the RM Historical Aged report at April 30th as outstanding (since apply was May 1st). I intended to use DATE1 “apply date” as the key date in some reporting I’m doing.

    The RM Apply Table shows 04/30/2012 as the GLPOSTDT and 05/01/2012 as the DATE1.

    The HATB DOES NOT show this customer as outstanding at Apr 30th. The customer inquiry shows the apply date as May 1st.

    GP appears to be using the GLPOSTDT for the report (which is correct, I’m running it based on GL date of Apr 30th) but why is it using 04/30/2012 as the GL post date on the apply?

    Just wanted to run it by you… smells like a bug to me but I’m not quite sure.

    Like

    • Hi Jen,

      Based on what you are describing, I believe that GP is actually working correctly. Let’s looks at all the dates involved, there are actually 6 of them:

      1. Invoice Doc Date
      2. Invoice GL Posting Date
      3. Payment Doc Date
      4. Payment GL Posting Date
      5. Apply Doc Date
      6. Apply GL Posting Date

      The HATB looks at the 3 GL Posting Dates if you are running it using the GL Posting Date option and the 3 Doc Dates if you are using the Document Date option. Also important to note is that it will take the later of these 3 dates. For example: If you have an invoice with a GL Date of 4/25/2012 and a payment with a GL Date of 5/2/2012, but you use an apply GL Date of 4/30/2012, GP will let you enter it in this way, but the HATB will only show this as applied on GL Date of 5/2/2012. This is correct as far as I am concerned, because how can you apply something you do not have? I do not believe this is an issue in your scenario, just adding this in so that the information is complete.

      So…from what you have said, the only question is why the Apply GL Posting Date populating with 4/30/2012 when you are using 5/1/2012 as the Apply Doc Date? When you apply transactions manually in GP, if you change the Apply Date on the Apply Sales Documents window, the Apply Posting Date will change with it, but it sounds like you are using a custom routine to auto-apply documents, so I would say you need to find out what/how that is populating both of the apply dates. If I have misunderstood the scenario you are describing, please let me know.

      Hope this helps,
      -Victoria

      Like

      • I’ll do some more testing but I get what you’re saying. We are specifying the apply date in the eConnect integration as “getdate()”, we are not specifying the apply GL posting date so it must be defaulting just as you describe, based on the later of the dates of the underlying documents.

        Like

  13. Hello Victoria,

    I am very new to GP and looking for some guidence. I am looking to display Invoices in the SOP tables and their respective activity in the RM tables. What is the best way to display this? I am was thinking the SOPNUMBE (SOP) to APTODCNM (RM).

    Like

    • David,

      There are many different RM tables…what specifically do you need to show?

      -Victoria

      Like

      • I am looking to show the activity associated with Invoices in SOP and display the corresponding RM Current & Historical Transaction Headers (RM20101 & RM30101) as well as the activity detail in the RM (20201 & 30201).

        Like

        • David,

          Sorry, still not clear…can you give me an example of what your report would look like? What information do you actually need from SOP? Are you positive you cannot do all of it from RM? Or start with RM and link back to SOP for some of the detail needed?

          -Victoria

          Like

          • Victoria,

            I am looking to display an Invoice and its associated activity. The original Invoice line from my understanding is in the SOP and the Activity in the RM.

            On a side note, I have a document # in the RM tables starting with a DBT. I assume thats a DEBIT but cant seem to locate its corresponding record in the SOP Header table.

            Like

            • Hi David,

              I don’t mean to be difficult…but I have spent so much time and effort in the past changing reports that were started with the wrong specifications, that now I do all that I can to get the spec right upfront. Below are some thoughts/pointers for you (in no particular order):

              • The first question I always ask is that is the purpose of the report I am creating? Understanding that can really help gear the reporting in the right direction. By purpose I mean, how will it be used by your company? What is the business need that it will be addressing?
              • If you are using SOP for your invoicing, then yes, the details of all the invoices would be in SOP tables. Do you actually need to recreate your invoice with all the detail? If so, depending on what is on your invoices, you may need quite a few tables. Or do you simply need information from the header?
              • What do you mean by ‘associated activity’? Do you want to show any credits or returns applied to an invoice? If so, yes, that will be in the RM20201 and RM30201 tables. If you are only limiting your results to what has been applied to SOP invoices, because they will always be applied TO, you can link on the following: SOPNUMBE = APTODCNM AND APTODCTY = 1 AND SOPTYPE = 3
              • An important concept to understand is that the SOP module is where a subset of RM transactions are created. Data only flows from SOP to RM when SOP transactions are posted, nothing goes back to SOP from RM. Because SOP represents only a subset of all RM transactions, you may find many transactions entered directly in RM that you cannot link to SOP – this is most likely what you’re seeing with your DBT transactions. Since document numbers in GP are entirely customizable, it’s impossible to say what DBT means on your documents without more information. If the RMDTYPAL in the RM tables is 3, then these are Debit Memos created directly in the RM module and you will not see them in SOP. Because of this concept, if your goal is to capture all RM transactions, it may be best to start with RM, then link back to SOP for invoice details. On the other hand, if your goal is only to show SOP invoices and information about them from RM, then it may be best to start with SOP. This is why understanding the overall purpose of the report is so critical.
              • Another important concept with SOP and RM is that you can have multiple transactions with the same document number. What makes them unique is the type of transaction together with the doc number. So you have to be very careful when coding in these modules. Even if you say that every single one of your transaction types will always have its own numbering sequence, it’s still critical to code for the possibility that this will not always be the case. Otherwise, the first transaction that breaks this rule will case all your reporting to be incorrect. In addition, the document types are not numbered the same way. So an Invoice in SOP is SOPTYPE 3, but in RM it is RMDTYPAL 1.

              Hope this helps with some of your questions.

              -Victoria

              Like

              • Thanks for your reply. Much appreciated.

                – The purpose of my analysis is to show all activity related to an invoice (Debits, credits, reversals, voids, or anything a user in GP can possibly post associated with an invoice)
                – The DBT’s are in fact debits, but have no referential link to an invoice.

                Thank you again, I was able to confirm many of my assumptions of the system that I have been seeing on the back end.

                Regards,
                – Dave

                Like

                • Dave,

                  In GP, applying transactions is the typical way of ‘linking’ them. You can only apply credit transactions (payments, credit memos or returns) to debit transactions (like invoices, debit memos or finance charges). There is no way to apply a debit memo to an invoice. There is no built in mechanism to record something like…”I am entering a debit memo and it is related to invoice 12345.” Also, if you apply a payment to an invoice, then void the payment, that will delete the apply information, thus leaving no way to link the voided payment to the invoice. So, out-of-the-box, the only ‘related’ information you will be able to find for an invoice are non-voided applied credit transactions (RMDTYPAL of 7, 8, or 9).

                  If your company is using some other method of ‘linking’ related activity, then you need to find out what it is and how/where it is stored. If I were asked to create a report like this, I would ask for examples in the user interface – show me a list of all ‘related’ transaction for a particular invoice and what determines that they are related. You will need as many examples as it takes to capture all the permutations of this in your data.

                  -Victoria

                  Like

  14. Victoria-

    Attempting to make the link to the GL. In short we need to see the AR account the cash receipt relieved and compare that to the AR account the sales document posted to originally.

    Sincerely,

    Tom

    Like

  15. Hi Victoria

    thanks for the view of application receipt, there would be any way to add to this view, which user apply the receipt.

    Thank you,

    Like

    • Hi Iris,

      From a quick glance at the tables, out-of-the-box GP does not track the user who applied the receipt. So unless you turn on some kind of additional tracking, this information is not stored anywhere for us to be able to report on.

      -Victoria

      Like

  16. Victoria,

    I want to limit the above SQL view by the payment posting date range but with so many joins I’m unsure where to put the “Between” limiter. Would you have any suggestions.

    Thanks,

    Mark

    Like

  17. Victoria,

    I am trying to take the concept above one step further. I am trying to get the item number and item description from the SOP30300. The issue is that if there are multiple sales line items and multiple payments for a single invoice, it shows all of the sales line items for each payment even though each payment did not pay every line item. I am trying to figure out how to link the payment line item in RM20201 to the sales line item in SOP30300. Any suggestions?
    Nate

    Like

    • Hi Nate,

      Unless you have a customization to do this, out-of-the-box GP does not let you allocate a payment to a line item on an invoice. So if you have an invoice for a total of $500 with 5 line items of $100 each, and you apply a $300 payment to it, you do not have an option to apply it to 3 of the 5 line items. It’s just a partial payment against the $500 total.

      Do you have some kind of a customization to allow tracking of what line item is paid with a payment? If you do, then you would have to get the information from that customization. If not, there is no way to report on this, as the information is not entered or stored anywhere in GP.

      -Victoria

      Like

  18. Can anyone tell me what the tables are that are associated with the AP report in GP? Thank you

    Like

  19. Many Thanks!

    Appreciate your efforts to publishing such a useful codes.

    Like

  20. This is great. But how to add all invoices, even not paid yet? Thanks

    Like

    • Ilya,

      Not sure it would be so easy to simply add to this particular view, because it’s starting with credits and linking on the apply details. I think you’d have to structure this a little differently if you wanted a list of all invoices and then a listing of what was applied to them. You could start with something like my All Posted Receivables Transactions view and then add in the Apply details.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. gp2themax - September 15, 2010

    GPTip42day – SmartList with Cash Receipts and Invoices Applied…

    Here's a tip from the forums compliments of our friend and my fellow MVP, Victoria Yudin. Victoria…

    Like

  2. GPTip42day - SmartList with Cash Receipts and Invoices Applied - gp2themax - May 18, 2010

    […] using SmartList Builder.  You can take advantage of her work by clicking on this link – https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/ Thanks Victoria.  You're the best! Published: Tuesday, May 18, 2010, 06:06 […]

    Like

  3. SQL view with AR apply detail - DynamicAccounting.net - February 16, 2010

    […] view with AR apply detail Victoria Yudin's new post provides a SQL view with AR apply detail. Published: Tuesday, February 16, 2010, 01: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: