How to Get All Credit Memo Documents in AR Open Item File

How to Get All Credit Memo Documents in AR Open Item File

Release Date: 12/24/2020
Version: 7.0 & Up

Q - How do we get a list of all credit amount documents in the AROPNFIL that are not applied to anything? Is there a way to get just these documents using the Expanded Aging Report?

A - You can use one of the following two methods: 

Method 1 - Use Expanded Aging Report
This method is suitable to your users since no special security is required. You can get all credit memo documents by going to A/R -> Reports -> Aging Reports -> Expanded Aging Report with CSV. View the exported CSV in Excel. To narrow down to those credit memos is actually pretty easy. All you have to do is to use the "Sort" function in Excel as follows:
  Sort by "Tp" Order A to Z
  Then by "ApplyTo" Order A to Z
See sample screen below:


The result is the following in Excel, which will show "Credit Memo" with no apply-to in the top section:


The "Tp" column refers to the document type, which can have the following values:
  • C = Credit Memo
  • D = Debit Memo
  • F = Finance Charge
  • I = Invoice
  • P = Payment
As for "ApplyTo" column, all open credit memos' apply-to will be zero.

Method 2 - Use SQL Statement 
Alternatively, you can use the following SQL statement in PCC (Pervasive Control Center), Crystal Reports, Excel Query, Web Services or any third-party tools:

If your database is converted to allow alphabetic document numbers, use the following SQL statement:
    select * from aropnfil where ar_opn_doc_tp = 'C' and ar_opn_apply_to = '000000'
If your database is not converted, then your document numbers are still in numeric format. Use the following SQL statement:
      select * from aropnfil where ar_opn_doc_tp = 'C' and ar_opn_apply_to = 0  

EMK


    • Related Articles

    • AROLOAD Accounts Receivable A/R Open Item File Maintenance

      A/R Open Item File Maintenance Application Overview The A/R Open Item File is the file where all transactions that occur between you and your customers are stored. As such, it is a very important file. Any time you want to know how much a customer ...

    • AP22S1 Accounts Payable Purge AP Open Item File

      Purge A/P Open Item File Application Overview Even when an invoice has been fully paid, the information on the A/P Open Item File pertaining to it remains on file until purged. If you never purged the A/P Open Item File, the disk would eventually get ...

    • ARSLSENT Accounts Receivable Sales and CR/DR Memo Processing

      Sales & CR/DR Memo Processing Application Overview Sales & CR/DR Memo Processing is an application that you will use very often. You will use it whenever you want to record in your Accounts Receivable new transactions that have occurred. For example, ...

    • AP05S1 Accounts Payable A/P Open Item Report

      Reports A/P Open Item Report Application Overview The A/P Open Item Report contains detailed information about the invoices on a vendor's account and summarized vendor aged totals aged by invoice or due date. The report shows each vendor's aged A/P ...

    • ARPRGSCN Accounts Receivable Purge Closed Open Items

      Purge Closed Open Items Application Overview Periodically, you will want to remove from the A/R Open Item File all transactions that have been fully paid off. Otherwise, the file could become very large over a period of time. This application will ...