Question Status

Suggested Answer
TLC asked a question on 4 Apr 2011 12:14 PM

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

 

Reply
Heather Roggeveen responded on 4 Apr 2011 11:42 PM

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.

Reply
Suggested Answer
Richard L. Whaley responded on 5 Apr 2011 10:16 AM

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!

Reply
TLC responded on 5 Apr 2011 3:55 PM

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.

Reply
Suggested Answer
Leslie Vail responded on 5 Apr 2011 7:57 PM

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

Reply
Suggested Answer
Richard L. Whaley responded on 5 Apr 2011 10:16 AM

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!

Reply
Suggested Answer
Leslie Vail responded on 5 Apr 2011 7:57 PM

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

Reply