Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables

Q - Recently, we used Pervasive's rebuild utility to rebuild all Elliott database tables (i.e., all \Elliott7\DATA\*.BTR files).  After the rebuild, we noticed some strange things happening to our A/P and A/R modules.  For example, below is a sample of our A/R aging report for customer "01CSV." As you can see, invoice number 352545 should be the first record on the report, followed by the payment and credit memos afterward.  But as you can see we now have payments above and below the invoice record. This is confusing when we try to research accounts and resolve disputes with the customer.  Can this be corrected?


A - We think the problem is caused by Pervasive's rebuild utility. Generally speaking, you can use the Pervasive rebuild utility for all Elliott database tables. There are two exceptions: 

  • APOPNFIL.BTR: This is the A/P Open Item table. Rebuilding it with the  Pervasive rebuild utility can cause major problems with AP payment processing. 
  • AROPNFIL.BTR: This is the A/R Open Item table. Rebuilding this can cause minor inconveniences with A/R account inquiry or aging report printing. 

For AROPNFIL.BTR, there are two keys -- the primary key and the alternate key. The alternate key has two parts: 

  • Customer Number
  • Apply-To No. 

When the A/R aging report is printed by the Apply-To order, systems use the alternate key. As you can see in your example, customer # “01CVS “ has multiple AR Open Item records for the same Apply To “352545.” When the first key value of “01CVS “ + “352545” (i.e., the invoice record) was being written to the AROPNFIL.BTR table, there was no issue. When the second record of the same key value was being written, PSQL handled the duplicate alternate key value by creating a “link list.”  The first record is on the first node of the link list, the second record is on the second node, and so on and so forth by the chronological order of when the record was added. Therefore, when you print the A/R Aging Report, the record of the same apply-to value will appear in the same sequence. 

When you perform a rebuild through the Pervasive utility, unfortunately the original link list sequence of the duplicate key value is not guaranteed. Therefore, it shows up as the problem you reported. This is not a critical problem because the A/R aging report still works correctly other than the minor printing sequence issue. If you don’t like this, you can follow the procedure below to fix it:
(1) At the command prompt, go to your Elliott DATA folder, and export AROPNFIL.BTR to AROPNFIL.TXT:
      M:\Elliott7\DATA> BUTIL -SAVE AROPNFIL.BTR AROPNFIL.TXT
(2) At the command prompt, rename AROPNFIL.BTR to AROPNFIL.OTR
      M:\Elliott7\DATA> REN AROPNFIL.BTR AROPNFIL.OTR
(3) At the command prompt, clone an empty AROPNFIL.BTR from AROPNFIL.OTR.
      M:\Elliott7\DATA> BUTIL -CLONE AROPNFIL.BTR AROPNFIL.OTR
(4) At the command prompt, load AROPNFIL.TXT to AROPNFIL.BTR
      M:\Elliott7\DATA> BUTIL -LOAD AROPNFIL.TXT AROPNFIL.BTR
Now go print the A/R aging report again. It should print by the proper date sequence as you expected.

Why did the above procedure fix your problem? When you use the BUTIL -SAVE option, it exports AROPNFIL.BTR to AROPNFIL.TXT by the primary key sequence. AROPNFIL.BTR primary key consists of:

  • Customer Number
  • Document Date
  • Doc Number
  • Document Type
  • Apply To Number 

Therefore, the data in AROPNFIL.TXT is sorted by customer number first, then document date sequence second. When you use BUTIL-LOAD option to load AROPNFIL.TXT to AROPNFIL.BTR, it will write the alternate key by document date sequence within the same customer record, so the link list of the duplicate key value will be re-established correctly.

Of course, you should only apply the above procedure when all Elliott users are out of the system.

You should apply the same procedure for the APOPNFIL.BTR table. We think the strange issues with the AP module are related to the same problem. The AP module tends to behave badly when the APOPNFIL.BTR duplicate alternate key original link list sequence is disturbed. Therefore, we ask our users never use the Pervasive rebuild utility on APOPNFIL.BTR or APHISFIL.BTR (this is the history version of APOPNFIL with identical table structure). In your case, you might want to skip AROPNFIL.BTR and ARHISFIL.BTR as well.

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master

Feedback and Knowledge Base