Table and File Purge Processes
Table and file purges are processes for removing data from STARS tables and files, and archiving the removed data. If the data is not periodically purged, the volume of data becomes huge and unmanageable. This chapter will provide information on the purging of information in various STARS files and tables. The information will include the criteria used for and the timing of each table or file purge.
The STARS tables and files that are purged periodically are the Appropriation, Allocation, Document, General Ledger, Subsidiary, Grant, Project, Vendor Payment, Warrant Control, Warrant Detail, and History. In addition to purging the financial data for grants and projects, the related control and descriptor tables are purged.
The Grant and Project files and tables are used to collect information for a variety of reasons over an agency determined time period. While certain balances related to this information are used for statewide purposes, agencies control which grant or project financial information will be flagged for purging each year.
The data in the Appropriation, Allocation, Document, General Ledger, Subsidiary, Vendor Payment, Warrant Control, Warrant Detail, and History files are used not only by agency personnel, but also for statewide purposes. Because of the statewide nature, the State Controller's Office (SCO) maintains control over when and which of these files are purged.
File purges are processes for archiving data. If data is not periodically archived, the volume of data becomes unmanageable. Additionally, the online inquiry capabilities of STARS become less and less useful as each fiscal year is added. However, the old data may still need to be accessed for research, analysis, audit, or other purposes. Purging the files, allows continued access to current data while retaining access capabilities to the older data.
A variety of STARS tables are purged at different times during the year based on the type of table. Tables include Grant and Project Control and Descriptor tables, PCA, Index, Budget Unit, Organizational Control, Batch Log and Vendor Edit tables.
The Appropriation, Allocation, Document, General Ledger, Subsidiary, Grant, Project, Vendor Payment, Warrant Control, Warrant Detail, and History files each have their own specific purposes and so the time period for needing ready access to the information is different for some files. Also, the amount of data posted to each file varies significantly. Because of these factors there are different purge schedules and criteria used to determine when to purge which files and tables.
Each month, the SCO purges the Batch Control Log, the History file, and the Warrant Control/Detail files. These files and logs have massive amounts of data added each month. Without monthly purging, the relevance of the older data rapidly diminishes.
Each spring, the SCO rolls the index, PCA, Budget Unit and Organization Control tables to make duplicates for use in the following fiscal year. That process also purges the Prior Prior Year tables that are not being used on the document file and purges unused or obsolete vendor records from the STARS Vendor Edit Table (SO21).
The Appropriation file is purged annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. Three criteria for purging the data are the Trans Year/ Fiscal Year on the record, the System Management Table (SM) current effective date, and no outstanding/active prior year or older encumbrances. When the purge process is run, any record with a Trans Year that is three fiscal years old is archived. After the purge, the STARS appropriation screens are available for the current fiscal year, the prior fiscal year, and any older fiscal years that still have outstanding active encumbrances.
The Allocation file is archived annually has closed as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. Three criteria for purging the data are the Trans Year/ Fiscal Year on the record, the System Management Table (SM) current effective date, and no outstanding/active prior year or older encumbrances. When the purge process is run, any record with a Trans Year that is three fiscal years old is archived. After the purge, the STARS allocation screens are available for the current fiscal year, the prior fiscal year, and any older fiscal years that still have outstanding active encumbrances.
The Document file is purged annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. The purge programs look for any document/suffix that has a balance of zero and archives those records. Regardless of their fiscal year, encumbrance documents will stay on the file as long as they have a balance. Receivable documents with balances roll forward to the current fiscal year during the fiscal close weekend and active documents will always have the current fiscal year as part of the record
The General Ledger file is purged annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. The two criteria for purging the data is the Trans Year on the record and the System Management Table (SM) current effective date. When the purge process is run, any record with a Trans Year that is three fiscal years old is purged. After the purge process, the STARS general ledger file will have the current and previous fiscal year's data available for viewing.
The Subsidiary file is purged annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. The two criteria for purging the data is the Trans Year on the record and the System Management Table (SM) current effective date. When the purge process is run, any record with a Trans Year that is three fiscal years old is purged. After the purge process, the STARS subsidiary file will have the current and previous fiscal year's data available for viewing.
The Operating file is purged annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. The two criteria for purging the data is the Trans Year on the record and the System Management Table (SM) current effective date. When the purge process is run, any record with a Trans Year that is three fiscal years old is purged. After the purge process, the STARS operating file will have the current and previous fiscal year's data available for viewing.
The Vendor Payment file is purged annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. The two criteria for purging the data is the Fiscal Year on the record and the System Management Table (SM) current effective date. When the purge process is run, any records with a Fiscal Year that is three fiscal years old is archived. After the purge process, the STARS vendor payment file will have the current and previous fiscal year's data available for viewing.
Warrant Control and Warrant Detail records are purged on the second workday of each month. When a warrant has a status of redeemed or cancelled and a status date four months prior to the current date it is purged from the Warrant Control File. The actual purge date used is entered on the Descriptor Table D60 – Purge Control Date for WC and the warrant control purge indicator on the STARS-System Management Table is set to 'P'. The indicator is automatically set to 'N' by the system at the end of the nightly cycle. Both the D60 and the Purge Indicator are scheduled in ESP, a scheduling program used by the SCO Computer Service Center. ESP triggers the running of specific jobs that then updates the D60 – Purge Control Date for WC with the correct purge date and sets the Purge Indicator on the SM Table to 'P'.
The History file is the audit trail for every transaction processed in STARS. There are no online inquires for the History file and very few reports are created from this file. The file is purged on the first workday of each month. The history file purge is scheduled in ESP, a scheduling program used by the SCO Computer Service Center. ESP triggers the running of a specific job that updates the purge parameters in job DA809089 which is the job that purges the History file. The History file purge archives prior prior month's data. For example, the purge on April 1 would archive February's data.
The Grant file is archived annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. To purge Grant financial information and the related Grant Control table record, the following criteria must be met:
To summarize, in order for the Grant financial information and related Grant Control Table to be archived, there can be no Document File, Cash Control File, or General Ledger file balances associated with the Grant or Grant/Phase. Records that do successful purge will be noted on the DAFR0221 Preliminary Purge report or the DAFR1221 Final Purge report.
The Project file is archived annually as part of fiscal year end processing, and occurs in late July after the prior fiscal year has successfully closed. See the Projects chapter for additional information on projects. To purge Project financial information and the related Project Control (PC) table records, two criteria must be met. If the Project Control table purge indicator (PI) is 'Y', the program will read the Document File. If a document uses a Project/Phase and has a balance on the record, the record will not successfully purge. The reason for the record being retained will be explained on the DAFR0220 Preliminary Purge report or the DAFR1220 Final Purge report.
The Index table is purged each April or May as part of fiscal year end preparations. Prior to the beginning of a new fiscal year, current fiscal year indexes that do have end-dates are rolled forward or duplicated in the next fiscal year. As part of this process, the records that are older than two fiscal years are removed from the tables. For example, in April or May of 2009, the index table has fiscal year 2007, 2008 and 2009 records. When the roll and purge is done, 2007 indexes will be purged and 2010 versions of needed indexes will be created. Indexes that are being used on prior year encumbrances will not be purged until such time as the encumbrance no longer has a balance.
The PCA table is also purged each April or May as part of fiscal year end preparations. Prior to the beginning of a new fiscal year, current fiscal year PCA's that do have end-dates are rolled forward or duplicated in the next fiscal year. As part of this process, the records that are older than two fiscal years are removed from the tables. For example, in April or May of 2009, the PCA table has fiscal year 2007, 2008 and 2009 records. When the roll and purge is done, 2007 PCA's will be purged and 2010 versions of needed PCA's will be created. PCA's that are being used on prior year encumbrances will not be purged until such time as the encumbrance no longer has a balance.
As with the index and PCA tables, the Budget Unit table is also purged each April or May as part of fiscal year end preparations. Prior to the beginning of a new fiscal year, current fiscal year Budget Units that do have end-dates are rolled forward or duplicated in the next fiscal year. As part of this process, the records that are older than two fiscal years are removed from the tables. For example, in April or May of 2009, the Budget Unit table has fiscal year 2007, 2008 and 2009 records. When the roll and purge is done, 2007 Budget Units will be purged and 2010 versions of needed Budget Units will be created. Budget units that are being used on prior year encumbrances will not be purged until such time as the encumbrance no longer has a balance.
The Organization Control table is also purged each April or May as part of fiscal year end preparations. Prior to the beginning of a new fiscal year, current fiscal year Organization Control tables are rolled forward or duplicated in the next fiscal year. The organization table does not have an end date field. Unneeded Organization Control tables that are not needed in the next fiscal year are deleted from the table. As part of this process, the records that are older than two fiscal years are removed from the tables. For example, in April or May of 2009, the Organization Control table has fiscal year 2007, 2008 and 2009 records. When the roll and purge is done, 2007 Organization Control tables will be purged and 2010 versions of the records will be created.
The Vendor Edit Table is purged each April or May as part of fiscal year end preparations. The vendor edit purge process is:
The Last Paid Date is the last time the Vendor was paid by the State of Idaho – not by any specific agency. State Employee vendor records are purged only if their status is inactive.
The Batch Log (BL) Table stores Batch Header information to ensure that a duplicate batch cannot process. The actual purge date used is entered on the Descriptor Table D60 – Batch Control Log Purge Date. Both the D60 and job DA809095 – Purge Off Batch Log Records From the Daily History File are scheduled in ESP, a scheduling program used by the SCO Computer Service Center. ESP triggers the running of specific jobs that then update the D60 – Batch Log Purge Date with the correct purge date and triggers the running of job DA809095 which actually purges the needed records.
Each Table or File Purge could have its own complication in completing and solving those issues will depend on the error code associated with that issue. Those possibilities are too numerous and varied to be addressed in this document and will require individual assessment as they occur. Resolutions will be discussed with and agreed to by DSA Ops and CSC STARS Applications personnel.
Grant and Project purges often result in some flagged grants and projects not being purged due to balances on the document, general ledger or cash control files. Additional information on resolving those issues can be found in the STARS Manual Grants chapter or the STARS Manual Projects chapter.
Each spring a DAFR0218-Project Control Purge Indicator Report and a DAFR0219-Grant Control Purge Indicator Report are generated for each agency with grants and/or projects. The purge indicator reports list the agencies reports along with the purge indicator. Agencies have the opportunity to review those reports and determine which grants and projects are ready for purging. SCO will run two preliminary purge reports to provide agencies progress reports on their preparations for the final Grant and Project purge in late July. Agencies receive a DAFR0220 Project Purge Report or a DAFR0221 Grant Purge Report after completion of each preliminary purge. These reports will know indicate which grants or projects will or won't successfully purge in the final purge process. The reports also explain what balances may prevent the records from successfully purging. After the final purge process is run, agencies will receive a DAFR1220 Final Project Purge Report and a DAFR1221 Final Grant Purge Report.
There are no other reports generated for other purged files.
There are no reports showing specifically what records were purged from the various tables. However, you can request the following reports of the existing records on the tables.