Database Files – Retention Guidelines
The amount of time that you need to retain specific types of information depends on legal, regulatory, and tax issues that vary widely by country, state and the business you are in.
This section describes the different types of data stored in the StreamV Database and it describes the basic retention logic that applies to it. How long you retain each type of data listed should be based on your own specific requirements.
The database files in the system fall into the following basic categories for archive purposes.
1. Clear, copy empty: This method, clearing and then copying an empty file to the archive system is used for temporary or temp files such as sotemp, potemp, sptemp, and obsolete files such as ups, fedex orjob files.
2. Prune, copy all. This method, pruing the file on the live database and then completely copying it to the archive system, is used for internal files where you only need to keep a limited amount of history on hand, and where the status of the record is not updated after the record is created. Files in this category would include internal files such as changes, ophist, waretran.
3. Copy all – Table files, master files, control files, covetext, datadict, etc. Table and Master files are copied over to any archive system completely to allow transactions to be displayed on the archive system.
4. Copy by date. Transaction files (invoice lines, journal entries, receipts files with external transactions in them – record status is not updated after record create). Records older than the archive date are moved to the archive system. Records newer than the archive date
5. Copy by date and status. This method is used for files with open documents or transactions in them such as orders, invoices, ap invoices, inventory transaction records, pick and sohold records. When these files are archived, you need to keep all open records in the production system. You can only archive records that are closed and that are prior to the archive date. Open records prior to the archive date need to be deleted, or closed before you can remove the data prior to the archive date
Parent Files
Parent Files store data that is pointed to by other data in the system. Examples are Tables such as the
• Division and Department Tables
• Terms Table
• Product Line Table
• Shipvia Table
• Operator Table
• Salesman (Rep) Table
• Zipcode Table
There are also virtual tables that are used in a similar manner. Examples include
• Gencode Tables such as the Country, State, Language and Currency tables
• Longcode Tables such as those used for customer specific pricing, shipping , print routines. Longcode Access, MyCove Menu Records, etc
Parent files tend to have a relatively small number of records and since many other records in the may database point to them, the records in the table are usually never deleted.
The Status of records in Parent Files can be set to inactive to prevent use of inactive or obsolete records (product lines, operators, etc).
Retention Policy – Parent Files
Parent Files are usually completely copied over to any Archive Databases so that the records in the Archive have all Parent Data available.
Note: Older Parent Data can be removed from the system, but only after you have made sure that no child records still point to them or you have updated the child records to point to different parent data.
List of Parent Files in StreamV
|
Name |
Description |
File Number |
|
Deptcost |
Department Table |
2 |
|
Division |
Division Table |
5 |
|
Trancode |
Transactioni Code Table |
16 |
|
Longcode |
Long Code Table |
18 |
|
Gencode |
Generic Code Table |
19 |
|
Terms |
Payment Terms Table |
21 |
|
Viaterms |
Freight Terms Table |
23 |
|
Freight |
Shipping Methods Table |
24 |
|
|
|
|
Master Files
Master files include files such as the customer, vendor, catalog and inventory files. The records in master files are used during transaction processing and when you are displaying records that point to the master records.
Examples of Master Files include.
Company
Vendor
Contact
Vcontact
Manufacturer
Mcontact
Catalog
Inventory
Special
Serial Master
Bin Master
Data Retention – Master Files
Master Files are usually completely copied over to any Archive Databases so that the records in the Archive have all Parent Data available.
If you are sure that all transaction or other records pointing to the Master records have been removed from the system, you can also remove the Master records. This may require a substantial amount of work since each Master record may be pointed to from a number of child records in a number of different files.
Transaction Files
Sohead
Soline
Invoices
Transact
Pohead
Poline
Invtran
Serhist
Cctran
Data Retention – Transaction Files
The retention logic for transaction files varies based on whether the transaction record is updated or processed after it is created.
Some data such as invoice line items, and journal entries, can be archived based on the date it was created, as the data is not updated by the system after it is created.
Other data such as sales and purchase order headers and line, accounts receivable invoices,
Transaction Files – Internal
Waretran
Pick
Sohold
Data Retention – Internal Transaction files are used to document
Temporary Files
StreamV uses some temporary files during Sales and Purchase Order Processing. The temp files and records are used to handle orders that are being created (and have not been saved as permanent orders yet) and orders that are being modified or edited. The Temp files are used only during order processing and the records in the files do not need to be archived or stored on a long term basis.
Sotemp
Potemp
Sptemp
Fvsort
Retention Policy – Temporary Files
Temporary files should not have records in them unless current transactions are being processed (i.e. sales order or po edits). The files can be moved to the Archive System so the application can be opened, but any data copied over (normally none) to any Archive Database is not useful for compliance, historical or other purposes
Control Files
System Control files contain counters, system policies and other information that is required for both read only and operating versions of the system. Textdata
FVPAR
COVETEXT
Labels
System Information
Retention Policy – Contol Files
System Control files should be completely copied from the live to the archive system.
Data Dictionary
Historical Files – Some files in the system are used for historical purposes (to id when and by whom a record was last updated, or
Changes Tracking
Ophist
Changes
Log
Data Required for Compliance or Historical Reporting
Business Income and Expenses
General Ledger, AR and AP Invoice Detail (header and lines), Inventory, Inventory Transaction Details
Sales Tax
Invoice Header and Line Items, Taxcodes,
Cost of Sales
Invoice Line Items, Purchase Order Line Items, Inventory Transaction Detail, General Ledger Detail, Accounts Payable Invoice Detail.
Product Tracking
Serial and Serial History
More:
Stand Alone System