Aging Shows Differently After Database Rebuild
A - Here is the reason why this happens and how you can fix this problem:
Tables That You Should Avoid Trying to Rebuild or Defragment
There are four tables in the Elliott database that are known for this type of problem. You should try to avoid using Pervasive's Rebuild or Defragmenter on these tables:- AROPNFIL.BTR - A/R Open Item File
- ARHISFIL.BTR - A/R History File
- APOPNFIL.BTR - A/P Open Item File
- APHISFIL.BTR - A/P History File
How to Fix Them Once It Happens
If you happen to rebuild and defragment these tables and notice this kind of issue, then you can take the following procedure to correct it. Below, we will give you an example of how to fix it for the AROPNFIL.BTR file. You can use the same principle for other tables.(1) Rename AROPNFIL.BTR File
Go to the command prompt and change your folder to our Elliott data directory, such as "M:\Elliott7\Data." Rename the BTR file to an OTR file:
M:\Elliott7\Data> REN AROPNFIL.BTR AROPNFIL.OTR
You will not be able to rename the file if someone currently has this table open. If this should happen, you can ask the user to exit and try again. It is important that your users do not access this file when you are performing this procedure, so it may be better to perform this action during an evening or on a weekend.
(2) Export AROPNFIL.OTR File
At the same command prompt, export this file by using BUTIL:
M:\Elliott7\Data> BUTIL -SAVE AROPNFIL.OTR AROPNFIL.TXT
(3) Create AROPNFIL.BTR File
At the same command prompt, use BUTIL to clone an empty AROPNFIL.BTR file:
M:\Elliott7\Data> BUTIL - CLONE AROPNFIL.BTR AROPNFIL.OTR
(4) Load AROPNFIL.TXT to AROPNFIL.BTR File
At the same command prompt, use BUTIL to load data back to AROPNFIL.BTR file:
M:\Elliott7\Data> BUTIL -LOAD AROPNFIL.TXT AROPNFIL.BTR
Why Does the Above Procedure Work?
All four of these tables have an "Apply-To" key. The Apply-To key consists of Customer# + Apply-To# or Vendor# + Apply-To#. The Apply-To key can have duplicate values. In the above example, Customer #406903 has four records that are the same as 558043. In order for the key to allow duplicate values, Pervasive implements them as a link list by chronological sequence. That is to say, when the invoice record, doc-no 558043, was written to the database, it was the first record in the link list. Then when the payment record, doc-no 367414, was written to the database, it was the second record in the link list, and so on and so forth. When the system performs a customer account inquiry, when it comes to doc-no 558043, the database engine returns the record by the link list sequence (first-in first-out) and everything looks fine.The problem starts to happen when you use the Pervasive Rebuild or Defragment utility, which does not necessarily honor the first-in first-out sequence for keys that contain duplicate values. Hence, you see the wrong sequence in A/R Account Inquiry.
When you use the command BUTIL -SAVE AROPNFIL.OTR AROPNFIL.TXT, it exports the AROPNFIL.OTR file by using the primary key sequence. The primary key does not contain a duplicate value and it is the same sequence as first-in first-out. Therefore, when you use BUTIL -LOAD AROPNFIL.TXT AROPNFIL.BTR, it loads the new table by using the first-in first-out sequence to re-establish the Apply-To key.
When Payment Date Is the Same As or Before the Invoice Date
In some rare situations, when the payment date is the same as or before the invoice date, the A/R Account Inquiry screen still may not look correct. The following is an example after going through the above manual rebuild procedure:
When you do a manual rebuild by using the above procedure, the loading sequence is by the primary key of the AROPNFIL.BTR file, which is Customer# + Date + Doc-No. In this case, the payment record and invoice have the same customer# + date. Since the payment document no. is smaller than the invoice no., the payment record is loaded to the table before the invoice when you do the manual rebuild. So that is what you see on the Inquiry Screen. This is a rare case. In addition, this is an aesthetic issue and does not affect Elliott functionality. If this is something that bothers you, you could try the following procedure:
- Go to re-apply the payment record and make the re-apply = 0
- Go to re-apply the payment record again and make the re-apply = 618656