I Am Running Out of Invoice Numbers -- I Need Direction to Archive Invoices
Q: Within the next 6 months we will run out of invoice numbers. I don't want to purge my invoice history because it contains valuable data that I would like to go back to in the future. So
I would prefer a method to archive my invoice history instead of purging it. Can you provide the steps needed to perform an archive of invoice history? Also, can we archive invoices without archiving orders?
A: First of all, you can upgrade to Elliott 8.5 and after to start using alphabetic invoice number to resolve this issue. Please read Elliott 8.5 release note for more details on this subject. If you can't upgrade to Elliott 8.5 or after for whatever reason, then you can following instructions in this document for archiving invoice history.
First of all, you should be aware that you can archive invoice history independent of orders. In this document, we will show you the steps needed to archive invoice history. The strategy is to use Elliott's Multi-Company feature and create an archive company for historical inquiry purposes. In Elliott, typically there will be a mapped network drive and a root Elliott folder to store data and programs. Just as a way to explain by example, the Elliott root folder may be:
M:\Elliott7
Throughout this document, we will refer to the M:\Elliott7 folder even though that is not necessary your actual Elliott root folder. With regard to any instructions in this document that refer to M:\Elliott7, you can just change it to your actual Elliott root folder. Your data will be stored in DATA or DATA_?? folders under the Elliott root folder. For example, your company 1 data will be stored in the M:\Elliott7\DATA folder and your company 2 data will be stored in M:\Elliott7\DATA_02 folder. Using this principle, you can see how Elliott supports up to 99 companies.
Let's say your current company is company 1, so your current data resides in the M:\Elliott7\DATA folder. Let's say that you wish to create an archive company 50 for archiving purposes. Its corresponding data folder will be M:\Elliott7\DATA_50. Throughout this document, we will refer M:\Elliott7\DATA as the current company data folder and M:\Elliott7\DATA_50 as the company created for archiving purposes.
So our strategy is to copy M:\Elliott7\DATA to M:\Elliott7\DATA_50. By doing so, we will create two Elliott companies that contain identical data initially. Since modern servers often come with disk space much larger than the Elliott database, we will not have disk space issues using this strategy. After the copying, we will delete invoice history data from the DATA folder so we can reset the invoice counter without worrying that the invoice number already exists in the invoice history database. Then company 50 can be used for archiving and history inquiries related to the invoice history that has been removed from the DATA folder.
To understand the background of this documentation, you should be aware of the database tables associated with invoice history. In Elliott, the following database tables make up the invoice history:
- CPINVHDR - Invoice Header
- CPINVLIN - Invoice Line Item
- CPINVOPT - Invoice Option (Kit/Feature or BOMP Material WO components)
- CPINVRTG - Invoice Routing (BOMP Plus Work Order Related)
- CPINVMTL - Invoice Material (BOMP Plus Work Order Related)
DIR CPINV*.BTR
Then, you will see these corresponding tables show up.
M:\ELLIOTT7\DATA>DIR CPINV*.BTR
Volume in drive M is Programs
Volume Serial Number is 2EFF-394C
Directory of M:\ELLIOTT7\DATA
07/10/2014 12:37 AM 1,065,222,144 CPINVHDR.BTR
07/10/2014 12:37 AM 1,214,373,888 CPINVLIN.BTR
07/10/2014 12:37 AM 61,440 CPINVMTL.BTR
07/10/2014 12:37 AM 65,536 CPINVOPT.BTR
07/10/2014 12:37 AM 57,344 CPINVRTG.BTR
5 File(s) 2,279,780,352 bytes
0 Dir(s) 372,678,299,648 bytes free
You may not see all of them depending on which Elliott features you are using. All these tables have the BTR extension. Throughout this document, we will use the command prompt to explain the actions that need to be taken. You can use Windows Explorer to achieve the same result.
You must make sure that when you perform this archive procedure, there are no Elliott users in the system. Therefore, it is recommended that you do this either in the evening or on a weekend. Furthermore, you can consider stopping the PSQL engine service on the database server while you perform the copying and delete steps to ensure there's no database activities can take place while you perform the archiving.
In addition, it is recommended that you do this at month end, or even better, year end. Therefore, it is easy for everyone in your organization to remember that the invoice history before a certain cut-off date will be in the archive company.
Archive Procedures
In a straightforward scenario, you will take the following steps:(1) Create a DATA_50 folder and copy all *.DAT and *.BTR files from e DATA to the DATA_50 folder.
(2) Follow the special procedure for tables exceeding 2 GB.
(3) Delete CPINV*.BTR files from the DATA folder.
(4) Initialize CPINV*.BTR files in the DATA folder.
(5) Delete other related tables.
(6) Apply NTFS Security to the New Archive DATA_50 Folder
(7) Change A/R Setup to use a new starting invoice number.
(8) Change Global Setup to point invoice history to the archive folder.
(9) Delete Invalid 5 Amigos Records.
(10) Delete COP Order Payment Records for Invoice History
(11) Generate P/O Reference Files
(12) Change the new company with data in the DATA_50 company name so it is recognized as an archive company.
(1) Create And Copy DATA_50 Archive Folder
Go to the command prompt and make sure your current path is M:\Elliott7. Then issue the following command:MD DATA_50
This will create the M:\Elliott7\DATA_50 folder. Now we need to copy Elliott data files. There are two types of Elliott data files, one type with the DAT extension, and another type with the BTR extension. So from M:\Elliott7 directory, you will issue the following commands:
COPY DATA\*.DAT DATA_50
COPY DATA\*.BTR DATA_50
Also, it is possible that you may want to place your laser form company logo images in the DATA folder. So you may want to use the following command:
COPY DATA\*.BMP DATA_50
You may also choose to copy the entire DATA folder to replace the above three commands:
COPY DATA\*.* DATA_50
However, this may copy additional files, such as spooled report files and log files, which are not necessary for archiving purposes.
(2) Special Procedure for Tables Exceeding 2GB
In PSQL, by default each data file has a maximum size of 2GB. For example, if the IMLSHST.BTR file exceeds 2GB, then it starts to create IMLSHST.^01, which is an integrated extension to IMLSHST.BTR. If IMLSHST.^01 exceeds 2GB, then it creates IMLSHST.^02, and so on. In that case, if you copy IMLSHST.BTR without copying IMLSHST.^01, or if you delete IMLSHST.BTR without deleting IMLSHST.^01, then you will receive a Btrieve file access error 002 when accessing that table.If you use serial number tracking, then the IMLSHST.BTR file will tend to be huge. NOTE.BTR is common too if you use note processing. IMINVTRX.BTR (I/M Inventroy Transaction Audit Trail), which are typically huge as well. The CPINVLIN.BTR file may also be large depending on the typical number of lines per invoice. Verify if you have any tables exceeding 2GB. I typically use the following command:
DIR DATA\*.BTR /O-S/P
The parameter /O-S/P means order by reverse sequence of file size (the large ones show up first) and pause one page at a time. This will cause the largest BTR files in the DATA folder to show up first. If you see that your largest BTR file is 2,147,475,456, then bingo -- you have a data file that exceeds two 2GB. If you do have data files exceeding 2GB, then you need to modify the above procedure to accommodate them accordingly.
Be aware that the *.^01 file has special properties so if you try to copy that file directly, you will not be able to copy. For example, if you use a command like
DIR DATA\IMLSHST.^01
then you will not see that file. But if you use
DIR DATA\IMLSHST.*
then you will see it. Also, if you use the "COPY DATA\IMLSHST.^01 DATA_50" command, it will not copy. However, if you use "COPY DATA\IMLSHST.* DATA_50," then it will copy. The following shows an example of an IMLSHST table that exceeds 2GB:
M:\ELLIOTT7\DATA>DIR IMLSHST.*
Volume in drive M is Programs
Volume Serial Number is 2EFF-394C
Directory of M:\ELLIOTT7\DATA
08/14/2014 04:36 PM 2,147,475,456 IMLSHST.BTR
08/14/2014 04:36 PM 334,995,456 IMLSHST.^01
2 File(s) 2,482,470,912 bytes
0 Dir(s) 372,537,167,872 bytes free
(3) Delete CPINV*.BTR Files
Go to the command prompt and make sure your current path is M:\Elliott7. Then issue the following command:DEL DATA\CPINV*.BTR
This will delete all invoice history files from the current company data folder.
(4) Initialize CPINV*.BTR Files
We need to create the empty CPINV*.BTR files we just deleted. Bring up Elliott and go to the current company (M:\Elliott7\DATA). From the main menu, choose Util-Setup, then File Utilities. From File Utilities, choose "Initialize," then choose "Customer Order Processing Files." From "Initialize COP Data Files," find all CPINV* files and initialize them individually.(5) Delete Other Related Tables
There are other related tables that should be deleted from the current company as well. This includes:IMLSHST - I/M Lot Serial History (if you use the serial number tracking feature)
CPBOXHST - COP Box History (if you use Shipping Verification)
CPBOXFHS - COP Shipping Box History (if you use Shipping Verification)
CPBOXHSR - COP Box Serial History (if you use Shipping Verification)
Follow the same principle as in steps (3)-(4) to delete the corresponding files and initialize them in the current company.
(6) Apply NTFS Security to the New Archive DATA_50 Folder
Depend on how you setup the NTFS security to your Elliott folder, you may have additional steps to apply NTFS security to the new DATA_50 folder. Please see the following knowledge base article for more details:http://support.elliott.com/knowledgebase/articles/654601-elliott-8-0-directory-structure-and-ntfs-rights
(7) Change A/R Setup for New Starting Invoice Number
In the current Elliott company, go to Accounts Receivable and choose "Util-Setup," then "A/R Setup." Choose "Change" and change the first field:1. Starting Invoice No
Set the starting invoice number to, say "1000," and save the change. You may choose whatever number you prefer, but you shouldn't choose a number that's too high or you'll reach the maximum of 999999 too quickly.
(8) Change Global Setup to Point Invoice History to Archive Folder
In the current Elliott company, go to Main Menu -> Util-Setup -> Global Setup -> Cop-Ctl -> Order/Invoice Inquiry. You will see the following two setups:4. Archive History is in Use ?
5. Archive History Data Path (CPINV*.* & IMLSHST.*)
Set the flag "4" to "Y." Then set "5" to "M:\ELLIOTT7\DATA_50."
The purpose of this step is so that if your users go to COP -> Inquiry -> Invoice History Inquiry, the system is actually smart enough to look for the invoice history database from both companies. It will look at the current company first for invoice history data. If it can't find it, then it will go the archive folder as specified in field 5 to look for it.
(9) Delete Invalid 5 Amigos Records
5 Amigos refers to the following five systemwide features in ElliottNotes
Attributes
eContact
Links
Events
Out of these 5 systemwide features, Notes, Attributes, eContacts and Links may be related to Invoice History. When the Invoice History database is deleted from the current company, there may be 5 Amigos records left behind, which become orphan records. In the short term, there may not be any immediate impact. But in the long run, this can lead to problems. For example, say that a NOTE record was created for invoice number 123456 and this NOTE record is not removed from the current company. After you reset the counter and when invoice number increases to 123456, you will find the new invoice 123456 will have this note attached to it, even though this note should belong to the invoice 123456 in the archive company. This can create serious confusion.
To resolve these problems, there are existing utilities that address them:
(a) For eContacts: Bring up Elliott and go to the current company. Go to Main Menu -> Util-Setup -> Global Setup -> Utilities -> eContact Integrity Check. This utility will remove all invalid eContacts relationships. If you normally attach eContacts to COP Sales Orders for order or shipping acknowledgment e-mail purposes, or if you e-mail PDF invoices to customers, then it is likely you will have eContacts attached to the invoice history database. You will probably see invalid eContact relationships deleted on this report when you run it.
(b) For Notes: Bring up Elliott and go to the current company. Go to Main Menu -> Util-Setup -> Global Setup -> Utilities -> Print or Purge Orphan Amigos. This utility can be used for Notes, Attributes, Events and Links. As for Notes, it is possible to attach Notes to Invoice Headers and Invoice Line Items. Therefore, you should first choose the "type of Amigo to Purge" as "N" (Notes). Then choose the Master File as "CPINVHDR" (COP Invoice Header). For the "Purge" option answer "Y." If you are not sure, you can answer "N" to the "Purge" question so the system can print a list of Notes records that would be deleted first. The following is an example screen:
Type of Amigo to Purge N
Master File CPINVHDR
Purge? Y
Repeat the above steps for the "CPINVLIN" Master File.
(d) For Links: It is unlikely that you will create a link for invoice history. But if you do, repeat the step (b) above for Links.
(10) Delete COP Order Payment Records for Invoice History
If you attached payment records to your COP Orders, then you are using the CPORDPMT table. As the order becomes an invoice and posts to invoice history, the corresponding payment records for invoice history are created. Unfortunately, we do not have a utility to purge records in the CPORDPMT table at the moment. So if you use this feature, you will have to go to PSQL Control Center and issue the following SQL statement to delete all the CPORDPMT records associate with the CPINVHDR table:Delete from CPORDPMT where CPORDPMT_FILE_NAME = 'CPINVHDR'
(11) Generate P/O Reference Files
If you set up your Elliott system so that the COP Purchase Order Number is searchable, or duplicate the Purchase Order number in COP so it can be detected, then you are using this CPPOREF table in Elliott. This table contains both Order and Invoice History data. Since the invoice history is now located in the archive company, you should get rid of the corresponding records in the CPPOREF table. To do so, you may go to COP -> Util-Setup -> Generate PO Reference(12) Change New Company to Reflect the Archive Name
Now go to the archive company 50 you just created. Go to Main Menu -> Util-Setup -> Company Setup. Change both Company Report Name and Display Name to reflect the fact that this is the archive data. I typically put down the date of archive at the end of the company name so people can see clearly when the archive folder was created.Additional Issues
COP Pickup Pending (CPPUDELV)
Few Elliott users use the Pickup Pending feature. But if you do, then you have a headache here. The primary key of this table is Invoice Number so if you don't purge it, it will create a future conflict. Most importantly, the pickup feature currently will not reference archive folder due to there's only few users using this feature. So after the invoice history database move to the archive folder, you will not be able to use the records in pickup pending database. You will have two choices here:(1) Use archive folder for pickup pending until all pickup pending records in the archive folder are gone.
(2) Do not use archive folder, instead issue credit memos for those pickup pending records, and re-issue the invoice in the current database.
In either case, you will need to delete and initialize the pickup pending table which is CPPUDELV from your M:\Elliott7\DATA folder.
Transfer Orders
If you use transfer orders and there are orders still in transit when this archive take place, then you should be aware of the following:
(1) Transfer orders receiving is based on invoice history. Therefore, you need to perform the receiving in your archive company when it finally arrived on the other end.
(2) The transit inventory is not going to moved to the final location in live company when you perform receiving of transferred orders at archive company, therefore, you will need to perform a manual transfer through I/M Inventory transaction processing to move the inventory over.
Serial Number Correction Tool
The Serial Number Correction Tool can't fixed the history records in archive company. Therefore, you need to use this tool on both the archive and live company. if that should happen.
Database rebuild require Elliott users or web services that may access above tables to stop. If you are using PSQL 12 and above, there's a "Defragmenter" utility that you can use to reclaim the database disk space back real time while your users is in the system.
On the other hand, the Invoice History database is integrated in many areas throughout the Elliott system. Because the historical invoice history database is now moved to a new company, much of this integration will not work anymore. The following are known areas of this limitation:
Other Considerations
There is no need to archive COP Sales History Transactions (CPHSTTRX) since the primary key of that table involves the invoice date. In addition, the CPHSTTRX table is very compact so it uses a lot less disk space than the invoice history database. As a matter of fact, CPHSTTRX will serve as an invaluable data source for COP History to bridge the gap due to the invoice history archiving.
Database Rebuild
The above procedure may result in certain tables records deleted. In PSQL database, when the data is deleted, it does not automatically return the disk space back. To reclaim the disk space and for better database performance, you could consider the following data files in the DATA folder:- NOTE.BTR - Notes table. This is especially true if you have shipping tracking number stores in the note table. As the invoice history is moved to the archive folder, the 5 Amigo Deleting steps can delete many note records attached to the invoice history.
- SYCONREL.BTR - Contact Relationship table. This is especially true if you have eContacts attached to the order and invoice history.
- CPPO*.BTR - PO Reference and related tables.
- CPORDPMT.BTR - Order Payment table.
Database rebuild require Elliott users or web services that may access above tables to stop. If you are using PSQL 12 and above, there's a "Defragmenter" utility that you can use to reclaim the database disk space back real time while your users is in the system.
Known Issues with Archiving
The purpose of creating an archive company is so you can look up any past invoice history records by going to this archive company. You can either go to COP Invoice History Inquiry, or COP Invoice History Reports. The purpose of step (5) above to set up the archive folder in Global Setup is so you can go to COP Invoice History in the current company and still be able to reference the archive company invoice history records.On the other hand, the Invoice History database is integrated in many areas throughout the Elliott system. Because the historical invoice history database is now moved to a new company, much of this integration will not work anymore. The following are known areas of this limitation:
- The GUI Invoice History (from A/R, Customer, Special Function, Invoice History Inquiry, or COP, Processing, Sales Desk, Invoice History Inquiry) does not support accessing archived invoice history.
- A/R Cash Receipt
- Look up customer number by invoice number
- Detail Interface from CSV file - when customer number is not provided
- COP Order Inquiry Drill Down to Invoice History
- Even though the traditional invoice history (COP -> Inquiry -> Invoice History -> Invoice Inquiry by Invoice) supports archived invoice history, but it does not support the associated archived notes (tracking number), attributes, eContacts and links.
- Invoice Inquiry sorting sequence: As we know usually the higher invoice number means newer invoice, but after the archive, and the invoice number is reset, this is not the case anymore.
Other Considerations
There is no need to archive COP Sales History Transactions (CPHSTTRX) since the primary key of that table involves the invoice date. In addition, the CPHSTTRX table is very compact so it uses a lot less disk space than the invoice history database. As a matter of fact, CPHSTTRX will serve as an invaluable data source for COP History to bridge the gap due to the invoice history archiving.Archive Timing
When you archive invoice history database with above procedure, even though you still can inquire the archive invoices in certain areas, but general speaking, especially for reporting, you will need to access the archive invoice history database from your archive company. This create a tricky issue if you need to perform this archive in the middle of the year. For example, if you try to run an invoice history report for year to day, you will find half of your data is in the archive company, and half of them is in the production company. It would be easier if you plan ahead and choose to archive right at the fiscal or calendar year end so your reports with more clean cut.
See related KB articles:
EMK