How to Speed up AP Comprehensive Cash Projection Report
Released Date: 07/03/2025
Version: 8.5 & Up
Q - When I go to A/P -> Report -> Cash Requirements Report -> Print -> Comprehensive Cash Projection Report, it takes two hours to run. How can I speed it up?
A - In general, you should consider purging some historical data to speed up the reporting function. The following are typical practices that can help to make this report run faster:
- Run Report on Server: When running an Elliott report on the PSQL server vs. on the workstation, the performance can be as much difference as 5 - 10 times. Since not all users can have access to a PSQL server console, it is suggested that you host deferred processing on a PSQL server. The workstation, instead of running the report directly, can choose to schedule the report to be run in deferred processing.
- Purge A/R Open Item File: The table AROPNFIL contains all receivable records like invoices, payments, credit memos, debit memos, and finance charges. When an invoice is fully paid, the associated records like payments and credit memos are still left in the AROPNFIL table. As a result, when you run an aging report, it will take longer as the fully paid open item records start to accumulate. It is recommended that you purge A/R open item files through AR -> Processing -> Purge Closed Open Items. Once the fully paid A/R Open Items are purged, they are moved to the ARHISFIL (A/R History) table. You still can view the history through a separate history inquiry function like AR -> Inquiry -> Customer Account Inquiry -> History Item Inquiry. The same principle applies to the A/R History Report. Once fully paid records are purged from AROPNFIL , various Elliott functions can run much faster, including the A/R Aging Report as well as the Comprehensive Cash Projection Report.
- Purge A/P Open item File: Similar to purging the A/R Open Item file, you can purge A/P Open Item to speed up the A/P side of the operation. You can do so by going to AP -> Processing -> Purge A/P Open Item File. Typically, the A/P Open item File size is smaller than the A/R Open item File. So this may not affect the performance as much.
The Comprehensive Cash Projection Report relies on ATP (Available to Promise) if you choose to project COP and PO data. It is recommended that you regenerate your ATP data at least on a weekly basis, if not daily. Since you should not perform ATP regen if you still have other Elliott activities, it is suggested that you run ATP Regen (I/M -> Utility -> ATP Regen & Process -> Generate -> Total Regen) at night through deferred processing.
If your ATP Regen takes a while to finish, then you should consider the following practice:
- Purge Posted Orders: When a sales order is invoiced and posted. It is stilled left in the sales order database (i.e., CPORDHDR & CPORDLIN...etc.) as a history order. This is a legacy logic left from three decades ago. Since then, we have added support for the invoice history (CPINVHDR & CPINVLIN...etc.) and order history (CPHODHDR & CPHODLIN...etc.) database. There's no reason to keep posted sales orders in the sales order database. A large sales order database full of order history records will slow down many processes in the COP module, including ATP Regen. Therefore, we suggest that you use COP -> Processing -> Purge Posted Orders to purge all posted orders. You still can access those purged posted orders in order history inquiry or through third-party reporting tools to retrieve data from CPHODHDR and CPHODLIN.
- Purge Closed PO: When a PO is fully received, invoiced and is closed, you can purge that PO. The data will be moved to the PO Audit Trail database so it is still there for you to access. But it will reduce the table size of POORDHDR and POORDLIN greatly to speed up all processes in the PO module, including ATP regen. You can do so by going to PO -> Processing -> Purge Closed Purchase Order.
EMK