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

Current System with Production and Archive Databases

Creating the Archive Database