Payment not showing applied in RM20101, why?

This question has suggested answer(s)

In the RM20101 table, why would a payment applied not show the payment or check number in the SQL table?  I can look directly at the payment and see that it was applied to the invoice.  Granted there is a .02 remaining on the invoice.

I am trying to write a Crystal report for a non-GP user to view payment history (including current payment status) and trying to pull the CHECKNMBR field from the RM20101 table in stead of the APFRDCNM field.  I want to see the actual check number.  It is not appearing in the window (see images below).

 

All Replies
  • I just looked at this field in my data that I know has Check Numbers in the payment.  I can see that the CHEKNMBR field is populated against the payment in RM20101 so not sure why it wouldn't in your case.

    Note - it is against the payment rather than the document the payment is applied to though.

  • You actually should use the apply to table to apply payments (and other adjustments) to your invoices and then take the document numbers from the open table.  You also need to link the history tables to get all of the transactions.

    Richard L. Whaley Author, Publisher, Consultant

    http://www.AccoladePublications.com

    Enhancing your Dynamics Knowledge!

  • I am pulling from both tables.  What I need to show on the report is the actual CHECK # that is recorded on the Payment Window.  Is there a way to get this to show up?  It is not a field in the Apply to table.

  • TLC,

    Try the view below - let me know if this is what you were looking for.

    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 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 on Aug 12, 2010 to add original total of Applied to Doc and

        Applied To Doc Paid Off date.

    Updated on Mar 23, 2011 to add unapplied amount of Applied to Doc.

    *******************************************************************/

    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,

    D.ORTRXAMT Applied_To_Doc_Total,

    D.DINVPDOF Applied_To_Date_Paid_Off,

    D.CURTRXAM Applied_To_Doc_Unapplied_Amount

    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

    INNER JOIN

    (SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM

    FROM RM20101

    UNION

    SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM = 0

    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

    Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITS
    ASCI, Inc.    *    PO Box 600965   *   Dallas, TX 75360    *   972-814-8550   *   leslievail@earthlink.net