Attachment A

Attachment A

QCEW Operating Manual

Page 12-2

July 2003 Preparing and Transmitting the EQUI

Chapter 12 – Preparing and Transmitting the EQUI

The State systems generate files of EQUI records with the specified layout, including the required data elements, from the State micro file database. By using the standard system to update its own micro file and then generate an EQUI file, the State provides BLS with all its micro level corrections for all unlocked quarters. This ensures substantial consistency between State and BLS data.

States have several processing options, each with their own advantages and disadvantages. They are discussed in the first section of this chapter. The remainder of this chapter explains:

• State processing options and activities

• EQUI records and transaction types

• Formats for all data records

• The correction policy

• Header and trailer records

• File transmittal options

-------------------- Contents of Chapter 12 --------------------

12.1 State Processing to Generate a Clean Deliverable

12.2 Creating the Deliverable EQUI File

12.2.1 EQUI Records and Transaction Types

12.2.2 Special Measures to Maintain Consistency between Files

12.2.3 Corrections and Updates for Earlier Quarters

12.2.4 Header and Trailer Records

12.3 Transmitting the File

12.3.1 Transmittal Options

12.3.2 Using the QCEW Program Data Transmittal Form

12.4 Sending Test Files When Making System Changes

12.5 File Retention

12.1 State Processing to Generate a Clean Deliverable

States have several processing options within each standardized system:

• Edit micro data on an on-going basis and then run the integrated edit during the last few weeks of processing.

• Impute all missing or delinquent data when the first extract is loaded. Then edit all data through the integrated (micro and macro level) edits throughout the production cycle.

• Edit micro data on an on-going basis. At a selected cut-off, impute all missing or delinquent data. After that, include in the integrated edit all previously edited micro records as well as all new micro records not previously edited. The integrated edit output will list flagged new micro records, previously flagged records with remaining invalid errors, and all flagged macro records.

Each State should work with their regional office to ensure that their processing schedule will produce clean, timely data. Once an integrated edit is run for a current quarter, however, the State should not switch back to editing micro records only but should continue editing in an integrated edit mode.

Options

Option 1: Running the Micro Edit Before Running the Full Integrated Edit

Advantages:

• Reported micro data can be edited early in the production cycle. (Initial review of first micro extract.)

• Allows the user to edit, update, and assign comments to micro data prior to processing macro data.

Disadvantages:

• Less efficient than concurrently resolving micro problems and viewing impact on macro data.

• Does not utilize other data in the cell to assist in the micro data review.

Option 2: Running the Full Integrated Edit

Advantages:

• Easier to determine the impact of the micro records on the macro data, if data are simultaneously processed at both the micro and macro levels.

• Easier to monitor industry patterns and other economic occurrences

• More efficient to resolve all questions associated with a micro record at one time, including both micro issues and their impact on the macro level.

• Greater assurance that all edit flags are addressed.

Disadvantages:

• You must generate missing and delinquent data imputations prior to running the full integrated edit or the quarter will be missing numerous records and have significantly reduced employment and wage levels in various cells.

• You will review an imputed version and a reported version of the same micro record within a few weeks time when employers send late data.

Option 3: Combination of Approaches

(If two UI Tax File extracts are run, edit the first extract using the micro edit programs. Use the integrated edit for the second round of extracted micro data. The integrated edit should also be used to continue editing any unresolved micro errors/failures and imputed missing or delinquent micro data. If more than two extracts are run, this approach should be modified based on the timing of the extracts and when the imputations are generated.)

Advantages:

• Reported micro data can be edited early in the production cycle. (Initial review of first micro extract.)

• Comments can be assigned and problems corrected closer to the time the reports are actually received from the respondent.

• Once the integrated edit is run, all associated flagged micro records are listed with the macro cell. Your review of the previously processed micro records may identify an economic condition or pattern within the macro cell. It may take fewer resources to review the remainder of the records with similar data movement.

• You have an opportunity to easily determine the quality of the imputations on both the micro and macro data and address problems.

Disadvantages:

• You do not have a complete (imputed and reported) file early in the production cycle

• Records previously addressed reappear each time you review the record

See Appendix E to view a suggested quarterly State data processing sequence.

Tax System Cutoffs

To reduce regional and national office editing workload and to improve macro data quality, States should not import any new data from the tax system after a cutoff date, which the State should set in conjunction with the regional office. The cutoff data should reflect the last day that new data could be received in time to correct it before it is included in an EQUI.

12.2 Creating the Deliverable EQUI File

The State systems generate a file of EQUI records with the specified layout, including the required data elements, from the State micro file. State systems do not generate EQUI records for establishments with a Type of Coverage code of 8 (not covered). However, they do generate records for units with a Status Code of 2 (inactive) if additional information is available after the inactive status was first assigned. All remaining records are included on the EQUI, including master records with an MEEI code of 2.

The same file may also contain back quarter records, also in EQUI format. All records must include the appropriate Transaction Code , State FIPS Code, Year, Quarter, UI Account Number, and Reporting Unit Number to be loaded to BLS files. The format used for all data records can be viewed in Appendix K.

The update process allows States to submit the current quarter data to BLS along with corrections/updates for up to four previous quarters. States are able to continue correcting the current and previous quarters on a flow basis during the clean-up period.

The primary focus of the submittal policy is to efficiently ensure consistency between State and BLS data. States submit to BLS all changes made to open quarters of data. A mechanism in each of the standardized systems identifies all known corrections and new data. Each subsequent EQUI submittal contains all changes made since the last EQUI transmission. EQUI subset files are an exception since they will only include data for specific targeted UI/RUNs.

Back Up Files

It is essential that States keep a copy of every EQUI file sent to BLS since files can be lost or damaged in transmission and may need to be replaced. State systems provide the means for States to back up and save every EQUI file. State systems can generate a file of all records changed since the system last generated a file for BLS. However, it may be problematic to generate a file of all changes that were made since the last file that was successfully processed by BLS, if the most recent file is lost. Therefore, lost files should always be replaceable from a backup of EQUI files.

12.2.1 EQUI Records and Transaction Types

The standardized systems create the following types of record entries:

• Deleted records

• Initial current quarter active records

• Inactivated records

• Initial records for one or more back quarters of micro data

• Reactivated records

• Updates to current quarter micro data

• Updates to prior quarter micro data

• Updates to administrative or non-quarterly data

• Header record

• Trailer record

Five possible transaction type codes are used in the first position of each EQUI record to identify each entry. These codes are:

Transaction Codes and Types
Transaction Code	Transaction Type
H	Header record
T	Trailer record
D	Deleted record
F	Full data record (all fields provided)
U	Update record

Header Records (H):

The header record contains detailed, control information about the tape or cartridge used (e.g., volume serial number, record length, block size, creation due date and time); information identifying the processing State, year, and quarter; and information about the State editing parameters and tolerances. The format of the header record is provided in Appendix K.

Trailer Records (T):

The trailer record provides summary information on the number of records in the transmission, as well as, establishment counts and employment and wage totals by year, quarter, and ownership. BLS-Washington compares the trailer record with EQUI micro data to ensure that BLS was able to read all of the data included on the file. The format of the trailer record is provided in Appendix K.

Delete Records (D):

A “delete” or D record has the effect of removing all data on the file for that unit, regardless of how many quarters are on the file. States should delete erroneous UI Account Number and Reporting Unit Number combinations that never belonged on the micro file. “Delete” records should not be confused with “inactivate” records (units that are out of business, were sold, or subunits which ceased to be reported separately). A change in the Status Code to a “2” inactivates the record, but its historical data are retained on the file.

The standardized system will generate “delete” records with the following fields:

"Delete" Record Fields
Positions	Length	Data Element
1	1	Transaction Code = “D”
2-3	2	State FIPS Code
4-7	4	Year
8	1	Quarter
9-18	10	UI Account Number
19-23	5	Reporting Unit Number

To delete an entire multi-establishment family, a "D" record must be sent for each subunit as well as the master record.

If the user designates a record for deletion, the system will automatically inactivate data for the record for all open quarters. Deletions should be rare, since all quarters of data will effectively be removed. The user should be very cautious when deleting data. Only those records that were written to the micro file in error should be deleted.

If, however, only the current quarter micro data should be deleted or a particular segment of the file, the fields should be zero- or blank-filled on quarterly occurrence fields, as appropriate. Inactive records should be assigned an end of liability date and an inactive status (Status Code 2) on the micro file so that systems do not impute for missing data.

The State should maintain a transaction file or audit trail and back up files in case large segments of the file are incorrectly deleted. If a few records are incorrectly deleted, the State should add the data for all appropriate quarters.

Full Data Records (Transaction Code F):

These update records provide normal data using every field. Every EQUI data element that is present on the State micro file for the specified year/quarter is included on these records, whether or not they were transmitted to BLS already. The State systems generate these records in the EQUI format with the following fields:

• Transaction Code of "F" in the first position of the EQUI record

• State FIPS code

• Year

• Quarter

• UI Account Number

• Reporting Unit Number

• All other EQUI data elements as they exist on the State micro file

Update Records (U):

Update records contain manual updates to the BLS micro file. These are special updates applied in BLS to State data in unusual circumstances such as fixing a large error just prior to BLS publication. Each update record contains a State, Year, Quarter, UI, and RUN (the fields that comprise the key field). Besides the key field, update records contain data only for those individual fields (or field) that need correcting.

All required and optional data elements available on the State micro file are included on the EQUI data record, whether that quarter's data are being transmitted to BLS for the first time or because data provided before has changed. The initial quarterly deliverable EQUI file provides the current quarter's data for the first time, while EQUI correction files provide data corrections and other modifications, as well as new reporting units.

EQUI records are loaded to the BLS micro file and matched to BLS records based on State, Year, Quarter, UI, and RUN. If no matching BLS record exists, the BLS system creates the appropriate record. If a matching BLS record exists, Transaction Code F tells the BLS system to overlay all relevant data elements on the BLS micro file with the contents of the EQUI record. For example, an EQUI update record with Transaction Code F may have a blank Trade Name field. This indicates that no Trade Name is present on the State micro file; therefore, the Trade Name will become blank on the BLS file as well (whether or not it was blank before).

EQUI updates may result when imputations are replaced with reported data, unresolved errors are corrected based on employer information, additional comment information is provided to resolve earlier questions, etc.

Multiple Quarters/Multiple Record Types

EQUI files may:

• Include multiple quarters of data

• Include new reporting units (new UI/RUNs) not previously reported

• Include a new quarter of data for UI/RUNs reported previously

• Include data changes for UI/RUNs reported previously in the current or earlier quarters

This approach minimizes the number of small files being processed separately.

Data changes are reported as follows.

Initial Records for One or More Back Quarters of Data:

The first time a record is submitted for a prior quarter, that back quarter record includes all data fields in the EQUI format. This record includes current administrative or non-quarterly information, because only the set of most current administrative data is maintained for each record. For example, suppose a unit was not on the State micro file for an earlier quarter but research indicates that the physical location differed during that time period. Only the most recent address information is retained on the micro file and submitted on the EQUI file.

Inactive Records:

If a record is inactivated for a quarter and the EQUI submittal is the first submittal for that record for that quarter, then all fields are submitted. This includes zeroes in the employment and wages and "M" (missing) in the employment and wage indicator fields. A "2" (inactive) would be reported in the Status Code field. For records becoming inactive, the State systems send a full EQUI record only for the first inactive quarter. The State systems are not required to send EQUI records for subsequent quarters unless there is a change to at least one field on the EQUI (e.g., successor UI/RUN, end of liability date, quarterly data, etc.). If a record is already inactive and you are updating other data fields (e.g., end of liability date), the system will still send a full record.

Note: If data were reported or imputed for the time frame that is being inactivated, the employment and wage data will remain on the file unless the State physically alters the data to zero. Altering is not required since the Status Code will be searched in all aggregation programs, and output programs will bypass all inactive records. For example, inactive records are always excluded from the macro file.

Reactivated Records:

If a record is reactivated, it is submitted on the EQUI with a "1" (active) in the Status Code field. The Status Code field is a quarterly field. The State must also update this field for any quarter submitted as inactive but being reactivated.

Updates to Administrative or Non-quarterly Data:

Non-quarterly fields can be changed in any quarter consistent with the correction policy (explained in Section 12.2.3). Non-quarterly fields are updated on the BLS file regardless of the year/quarter as long as the year/quarter combination of the EQUI record is unlocked on BLS files. If the reference period is locked, the BLS system will not update the record.

Updates to back quarters of quarterly data:

All quarterly and non-quarterly fields are included on the EQUI record. If multiple quarters are submitted on the same EQUI, all the data will first be loaded to the BLS micro file and then edited. For example, if three quarters are submitted for the first time for a record, and all data are reported each time, the non-quarterly data will only be edited once. (If a record has an invalid EIN, and the invalid EIN is submitted on all three records, it will only fail the edit once.) In addition, the inter-quarter editing of the quarterly economic data is more accurate when all quarters are loaded first, and then edited.

Removing Data:

If you change a numeric field such as employment, wages, or contributions to zero on the State micro file, the system zero-fills that numeric field on the EQUI. If you change an alphanumeric field to blank on the State micro file, the system will blank out that field on the EQUI. This in turn converts the field on the BLS file to blanks.

An example of how changes to alphanumeric fields would be reported:

2005/1 submitted in August second line of a street address includes "Suite 7431"

2005/2 submitted in November second line of the street address is blank

2005/3 submitted in February second line of the street address is reported as blank

In the third quarter of this example, the field is reported as blank because it was already blanked out on both the State and BLS micro files in the prior quarter.

Macro Data

All macro file data are derived from the micro database file, in both the States and BLS. Once the micro file is updated, the changes are applied to the macro file to ensure that the micro files and macro files also remain in synch. Many data elements submitted on the EQUI will not effect macro data. Some of these include dates, addresses, names, telephone numbers, EINs, etc. Other elements like industry, county, and ownership codes, monthly employment, and total wages will effect the macro records. (Macro data processing is discussed in more detail in Chapter 10 – Macro File and the Integrated Edit.)

Some changes to micro data may result in two macro cells being changed. For instance, if a private sector restaurant is updated and re-coded from county code “031” to “059,” both macro cells 722110/50/031 and 722110/50/059 will be updated and edited.

Normally, all EQUI data available for the current quarter or any back quarter unlocked in BLS are submitted together. (Note that State systems never generate an EQUI record for a future quarter.) Once received in BLS-Washington, the data are loaded together and all quarters of data for a given record are processed and edited together. Once the current quarter file is submitted, corrections to it and/or other back quarters should be submitted in compliance with the correction policy.

12.2.2 Special Measures to Maintain Consistency between Files

The standard State systems provide the capability to generate special EQUI files that provide full data for one quarter only, or that provide data only for specified reporting units. Processing problems may sometimes occur that cause significant inconsistencies between data on the State micro file and data on the BLS micro file. Following consultation with their regional office, States can generate one of these special files to bring State and BLS data back in synch.

A one-quarter EQUI file provides an EQUI record for every status 1 (active) and status 2 (inactive) record on the State micro file for the specified quarter. This file replaces all data on the BLS micro file for the matching UI/RUNs for the specified quarter.

A second option for restoring consistency between State and BLS files, as well as for fixing serious errors just prior to BLS publication, is for the State to submit a subset EQUI file. This type of special file only provides data for specified reporting units. The State systems generate the subset EQUI file using, as input, a file of identifying information. The input file contains the State FIPS code, Year, Quarter, UI Account Number, and RUN for each record needed. The State system finds the matching record on its micro file and creates the corresponding EQUI records. These are full EQUI records containing all available data for the specified quarter, plus all available non-quarterly data. EQUI subset file records have a status of either 1 (active), 2 (inactive), or 9 (pending).

12.2.3 Corrections and Updates for Earlier Quarters

Consistency between State and BLS data is maintained by only updating the State micro file and providing BLS with all micro level corrections made to the State micro file for all unlocked quarters.

Extract Requirements

All States follow the same minimum requirements. States must extract all current quarter data and prior quarter data received since the previous extract. Note that the prior quarter is defined as the quarter immediately preceding the current reference quarter (e.g., if the current reference quarter is 2003/4, then the prior quarter is 2003/3).

These extracts include all data for the current quarter, including any non-quarterly or quarterly fields that may have changed. Each standard State system has procedures for locking selected fields. These are normally locked to protect data that have already been reviewed, so they are not replaced by data of lesser quality. Often the State UI tax system does not follow the same guidelines as those required by BLS. Each State system also has a mechanism to compare extracted data against locked data to manually determine if changes are needed. Review the EXPO-202 or WIN-202 documentation for specific details.

Prior quarter data extracts, as well as subsequent extracts for the current quarter, are also run to obtain information on retroactive accounts, set up since the last extract. Late data are loaded to the files to replace imputations. Current imputation procedures only allow one to two quarters of imputation before the data are zero-filled. Imputation studies have found that a number of establishments whose data are imputed remain active after the State has ceased to generate estimates for them. By replacing estimates with late data, the State can reduce the number of imputed quarters for the records. (On-going users such as the Current Employment Statistics (CES) staff should be notified when these changes occur and which quarters are impacted.)

In addition, any QCEW program information added to the tax file or other source files for inactive accounts that still reside on the State micro file must also be extracted to provide information on successors. For example, an account reports on the Quarterly Contributions Report (QCR) through 2005/2, is imputed for 2006/3 and 2006/4, and zero-filled for 2007/1 and 2007/2. Staff manually inactivates the record as of 2007/2. During the 2007/4 extract, the End of Liability Date and a Successor UI Account Number are also extracted for the record. The inactive record on the State micro file should be updated with this information so the correction will be provided to BLS. These data fields provide greater detail on deaths on the file, and predecessor/successor information, both of which will be used extensively for longitudinal research purposes as discussed in Chapter 5.

Key Features of the Correction Policy

The correction policy is summarized on the chart that follows. Specific points are listed below.

Automated Transfer of Corrections to BLS Files:

The standardized State systems generate EQUI correction records, which are submitted to BLS. This approach ensures substantial consistency between the files and minimizes errors.

Automated Correction of Macro Files from Micro Files:

All corrections made to the micro files are applied to the macro file when it is re-aggregated. No corrections are directly made to the macro data, either in the States or BLS.

Unlocked Quarters on State and BLS Files:

For those quarters that are unlocked on BLS files, all corrections made to the State files are submitted to BLS.

For the prior and current quarters, extract data from the tax file or other sources for all new information available since the last extract. This includes:

• Active accounts

• Retroactive or reactivated accounts

• Reported data for delinquent accounts

• Other corrections

These changes must be made to the State micro file so the State system will transmit them to BLS on the EQUI. Any current and prior quarter corrections that significantly impact earlier unlocked quarters are then made to both State and BLS files.

Inactive Accounts:

Any new information added to inactive records for any unlocked quarter maintained on the State micro file is sent to BLS for use in longitudinal research.

QCEW Correction Policy (See note at end of table.)
Any correction/update made in the State to quarters that are unlocked in BLS is transmitted to BLS to maintain consistency in the files.
Current (reference) Quarter (Quarterly Occurrence Data)	Example	Extract from UI tax and other information sources (required for quarters noted)	Example	Quarters which can be corrected/updated on BLS files	Example for BLS files
First	02/1	Fourth of prior year and first of reference year	01/4, 02/1	Must update fourth quarter of the prior year and the reference quarter; may update first, second, and third quarters of the prior year	01/4 and 02/1 required; may update 01/1, 01/2, and 01/3
Second	02/2	First and second of reference year	02/1, 02/2	Must update only first and second quarters of the reference year	02/1 and 02/2 required
Third	02/3	Second and third of reference year	02/2, 02/3	Must update second and third quarters of the reference year, may update first quarter of the reference year	02/2 and 02/3 required; may update 02/1
Fourth	02/4	Third and fourth of reference year	02/3, 02/4	Must update third and fourth quarters of the reference year, may update first and second quarters of the reference year	02/3 and 02/4 required; may update 02/1 and 02/2

Note: Changes are made to the correction policy from time to time. Such changes may affect the number of quarters that are extracted.

Records to Include with Administrative Data
Administrative Data	Data Fields	Which Records to Include?
Non Quarterly Data – active accounts	Any data for active accounts.	Included for all active single, sub-unit, or master UI- or UCFE-covered records on State micro file. Note: If a record is reporting retroactively, also include quarterly occurrence for the reference and prior quarter.
Non-Quarterly Data - inactive accounts	Missing End of Liability Date, Input (Setup) Date, Reactivation Date, Initial Liability Date, or Predecessor UI#, Predecessor RU#, Successor UI#, Successor RU#, Status Code, ARS Response Code, ARS Refile Year, Old Fields, etc.	Included for any inactive single, sub-unit, or master UI- or UCFE-covered record on State micro file that has not been archived off the file.

12.2.4 Header and Trailer Records

When State systems generate an EQUI file, header and trailer records are included that provide relevant information. Three types of information are included on the header and trailer records:

1. The date and time the file was created (header record). This is needed by BLS for the proper sequencing of update transactions. If BLS has more than one file from the State containing updates, and if the same record is updated on more than one of these files, the date and time allow BLS to apply the updates in the proper sequence.

2. Editing parameters and tolerances (header record). The parameters and tolerances used by the State to edit the data are included on the header record. Although parameters and tolerances can be modified each quarter, only the parameters and tolerances used for the most recent processing are submitted. The EXPO-202 and WIN-202 systems only maintain parameters for a point in time and they are applied to all data processed at that point in time and until the parameters and tolerances are changed. Parameters and tolerances are not maintained on a quarterly basis. Typically, States make only minor modifications to the parameters and tolerances once they are set.

3. Record counts and other data by year/quarter (trailer record). This information allows BLS to verify that it has all the data the State intended to provide.

Header Record

The header record includes information on the processing State, Year, Quarter, creation data and time of the file, and State editing parameters and tolerances. The Transaction Code is always H on the header record.

The header record includes the following tape information and file creation information:

• State FIPS Code

• Year

• Qtr

• SESA ID (zero-filled)

• Date file created (YYYYMMDD format)

• Time file created

• Record Length

• Block Size

The header record must include the same 23-position identifier as the trailer record. This includes the following information (see Appendix K):

• Transaction Code (always ‘H’)

• State FIPS Code

• Year

• Quarter

• SESA ID (zero-filled)

State parameters are provided to BLS for reference. BLS uses looser parameters for most edits to limit the size of edit listings.

Trailer Record

The trailer record includes summary information on the number of records in the transmission as well as establishment counts and employment and wage totals by year, quarter, and ownership. The Transaction Code is always T on the trailer record. BLS uses the trailer to compare the expected output of the EQUI file to the actual output to ensure that no records were lost either when the file was created or when it was loaded. Two sets of data are collected in the trailer record, including:

Record Counts per Year/Quarter for a maximum of five quarters (as well as a count of delete records)

Control Totals (include records with any MEEI code, any Status Code, and any Type of Coverage). Note that all records are included to ensure that the entire file is read and loaded to the BLS micro file.

The number of units reflects the number of EQUI records that provide or change data elements of any kind. Totals for Employment, Total Wages, Taxable Wages, and Contributions are summarized for the data on the EQUI file itself. For example, an EQUI record that changes only an address field or industry code is counted once in the Number of Units, but would be counted as zero for Employment Month One, for Employment Month Two, etc.

There may be EQUI records for the current quarter that do not include an Ownership Code. (Typically these records will change or correct current quarter data.) Data from such records are included in the Control Totals for the Current Quarter for All Ownerships, even though they cannot be counted by federal, State, local, or private sector ownership.

Note that the trailer record, whose layout is given in Appendix K, also contains a 23-position identifier including:

• Transaction Code (always ‘T’)

• State FIPS Code

• Year

• Quarter

• SESA ID (Always zero-filled)

12.3 Transmitting the File

There are five approved general methods for transmitting EQUI data to BLS-Washington. EQUI data files must not be sent to the BLS-Washington via e-mail under any circumstances due to BLS data security provisions. The five approved general methods are as follows:

1. Submitting as a Service Center State

2. Submitting tapes and cartridges directly to SunGard

3. Submitting files via 56K lines using EUSWeb

4. Submitting via CD-ROM

5. Submitting via FTP

The following criteria should be used in determining the appropriate method.

• If a State is using the Service Center at SunGard for processing, they should also use the Service Center for submission of the EQUI. (See option #1 below.)

• All other EQUI files should be submitted via tape or cartridge directly to SunGard (see option #2 below), unless the State cannot use the standard tape and cartridge format or the file is sufficiently small for an alternate method.

• If an EQUI file is less than 10MB (compressed or uncompressed), the State may submit via 56K lines using EUSWeb. (See option #3 below.) The State may submit a correction file or EQUI subset file via EUSWeb even if its full EQUI file was submitted via tape or cartridge.

• If an EQUI file fits on a CD-ROM, submittal of the EQUI using this method may be considered. (See option #4 below.)

• Non-Service Center States may send their files in via FTP. (See option #5 below.) FTP may only be used by States that have successfully completed FTP testing in coordination with DBES. Interested States should contact their regional office about testing.

The procedures for each of the methods are briefly outlined below.

12.3.1 Transmittal Options

1. Submitting as a Service Center State

For Service Center States, the creation of EQUI deliverables is done at SunGard. Though all handling is done on-site at SunGard, information regarding the exchange and processing of tapes must still be communicated.

1. All Service Center States must create their deliverable on a SunGard cartridge using the following naming conventions. Unlike tapes created in the State, DBES cannot bypass labels on tapes created at SunGard – they must contain the following dataset names:

For the initial quarterly submittal or an EQUI correction file, use the following naming convention:

DSN = ACTINT.ACCT.EQUI.DATA.STYRQ

Where:

ACTINT = the State's account and initial assigned at SunGard
(example = YBU10W which is Delaware's)

ACCT = the account at SunGard (example = A145 which is Delaware's)

ST = State postal abbreviation (example = DE for Delaware)

YRQ = the most recent processing year and quarter contained on the file
(example = 074 for the 2007 fourth quarter)

A complete example would be DSN = YBU10W.A145.EQUI.DATA.DE074 for the 2007/4 file from Delaware.

For a one-quarter EQUI file or an EQUI subset file (described in Section 12.2.2.), use the following naming convention:

DSN = ACTINT.ACCT.EQUI.FED.STYRQ

Where:

ACTINT = the State's account and initial assigned at SunGard
(example = YBU10W which is Delaware's)

ACCT = the account at SunGard (example = A145 which is Delaware's)

ST = State postal abbreviation (example = DE for Delaware)

YRQ = the most recent processing year and quarter contained on the file
(example = 074 for the 2007 fourth quarter)

A complete example would be DSN = YBU10W.A145.EQUI.FED.DE074 for the 2007/4 file from Delaware.

2. After creating the cartridge, the State should catalog their dataset and send DBES a completed QCEW data transmittal form. Preferably, send the transmittal form via e-mail

to the group name “EQUI_Submittals” with a copy to the regional office. This will get the form directly to a group of people responsible for EQUI processing or interested in file receipts. Alternately, the transmittal form may be faxed with a cover sheet addressed to “ATTN: EQUI Processing” (Fax # 202-691-7292) or sent via FedEx directly to DBES at the following address:

ATTN: EQUI Processing

Bureau of Labor Statistics, DBES

2 Massachusetts Avenue, NE – Room 5625

Washington, DC 20212

Phone: (202) 691-7300

If the transmittal form was faxed or sent via FedEx to the BLS-Washington rather than e-mailed, the State must also notify the regional office that the cartridge is at SunGard and is ready to be processed. The regional office will then notify DBES via e-mail. E-mail is more reliable and ensures that all of the relevant participants are notified. Similar to earlier directions, e-mail to the BLS-Washington should be sent to the group name “EQUI_Submittals.”

Note: For a small number of States, EQUI files are transmitted to SunGard via cartridges. From SunGard, the cartridges are sent to BLS-Washington where they are eventually sent back to the States for re-use.

2. Submitting Tapes and Cartridges Directly to SunGard

For this transmittal method, DBES will continue to supply tapes, and States should continue to use what is currently in circulation (WOU's and BU's). A State-owned tape/cartridge may be used if the deliverable needs to be sent and the State doesn’t have any WOU or BU tapes on hand. The procedures for sending the EQUI directly to SunGard are as follows:

1. The State prepares the file on a WOU, BU, or State-owned tape or cartridge. When using this method, if there is no way of correctly reading the Data Set Name, DBES will bypass labels on the files.

2. The State completes the QCEW Program Data transmittal form, marking the boxes and giving information about the tape as appropriate. (See the separate instructions for using the transmittal form.) If the file is a one-quarter file or an EQUI subset file, the State should designate it accordingly. Similarly, if the file is a test file rather than a production EQUI file, this should be clearly specified on the form.

3. The State mails the file and a copy of the transmittal form to the following address via FedEx:

SunGard eSources

600 Laurel Oak Road

Voorhees, New Jersey 08043

ATTN: Tape Library

PHONE # 1-800-628-9440 (choose option three)

4. The State sends another copy of the transmittal form to DBES in BLS-Washington. This copy must contain the ten-digit Federal Express Airbill Tracking Number so that DBES can track tapes when necessary. It is strongly recommended that the transmittal form be e-mailed to “EQUI_Submittals” with a “copy” e-mailed to the regional office. This is the preferred method. This will get the form directly to a group of people responsible for EQUI processing, or interested in file receipts. Alternately, the transmittal form may be faxed with a cover sheet addressed to “ATTN: EQUI Processing” (Fax # 202-691-7292) or sent via FedEx directly to DBES at the following address:

ATTN: EQUI Processing

Bureau of Labor Statistics, DBES

2 Massachusetts Avenue, NE – Room 5625

Washington, DC 20212

Phone: (202) 691-7300

5. If the transmittal form was faxed or sent via FedEx to BLS-Washington rather than e-mailed, the State must also notify the regional office as to when the file was mailed. The regional office will then notify DBES via e-mail. E-mail to BLS-Washington should be sent to the group name “EQUI_Submittals.”

6. When SunGard receives a file, they will log it into their Tape Management System and notify DBES so that the file can be processed.

7. After successful processing, SunGard will return the State tapes/cartridges to DBES for mailing back to the States for re-use.

NOTE: If the State chooses to supply any additional hardcopy materials, such as the JCL used to create the actual EQUI file, they should not be sent to SunGard, but transmitted via FedEx separately to DBES.

3. Submitting Files Using EUSWeb

Although used less frequently, EUSWeb is another method for transmitting the EQUI and update transaction files when they are less than 10MB (uncompressed or compressed) in size. (The limit of 10MB is too small for most initial EQUI files, but is generally adequate for correction files and subset files.) The procedures for using EUSWeb are documented in the EUSWEB User's Guide that can be accessed at http://199.221.111.170/content/EUSWEB.doc. States must conform to the proper format, naming conventions, and compression conventions given below.

For format, files must be saved in ASCII (text) format.

For the file name, States must use the following standard naming convention. This step is necessary for correct routing of the files. States should put their initial EQUI files in the MCR directory and put update files in the CRX directory.

For an initial quarterly EQUI submittal:

File Name = STMCRYQN.txt

Where:

ST = the State postal abbreviation (example = DE for Delaware)

MCR = the constant portion of the EQUI file name that is used for pollster routing

Y = the last digit of the most recent year

Q = most recent quarter

N = an incremental field in case a State has more than one file to transmit for the same year/quarter

A complete example would be file name = DEMCR741.TXT for Delaware’s initial submittal of their EQUI in 2007/4.

For a correction file:

File Name = STCRXYQN.txt

Where:

ST = the State postal abbreviation (example = DE for Delaware)

CRX = the constant portion of the correction file name that is used for pollster routing

Y = the last digit of the most recent year

Q = most recent quarter

N = an incremental field in case a State has more than one file to transmit for the same year/quarter

A complete example would be file name = DECRX742.TXT for Delaware’s second update transaction file in 2007/4.

Files over 1MB in size must be transmitted in zipped format. States may use a standard product such as PKZIP or WinZip. When zipping files, States should put each text file in a .zip file with the same naming convention (except for the extension). For example, the zipped version of DECRX832.TXT would be called DECRX832.ZIP.

Once the file has been posted to EUSWeb, the completed transmittal form should be sent via e-mail to DBES at the group name “EQUI_Submittals” with a copy to the regional office. This will get the form directly to a group of people responsible for EQUI processing, or interested in file receipts. Alternately, the transmittal form may be sent via fax or FedEx to DBES and the regional office notified via e-mail.

4. Submitting via CD-ROM

States can submit their EQUI files on CD-ROM without receiving advanced approval. States that have not previously used this transmittal method should check the CD before sending it and verify that the complete EQUI file is present. States using these methods should transmit the EQUI file directly to BLS-Washington using Federal Express. The completed transmittal form should be enclosed with the EQUI file.

Shipments should be sent via FedEx directly to DBES at the following address:

ATTN: EQUI Processing

Bureau of Labor Statistics, DBES

2 Massachusetts Avenue, NE – Room 5625

Washington, DC 20212

Phone: (202) 691-7300

The regional office also should be notified via e-mail of the transmission date.

5. Submitting via FTP

FTP (File Transfer Protocol) is not a relevant option for Service Center States as their files are generated at SunGard and do not need to be transferred. However, other States may find it useful as a means to make files available to BLS for processing on the SunGard mainframe on the same day they are generated in the State processing system. The FTP method requires a State to have a PC application for uploading files to the mainframe computer at SunGard as well as a SunGard account and initials. A brief procedures document is available from national office to interested States who request it. Send requests for this document to e-mail group 202ftp.

FTP may only be used by States that have successfully completed FTP testing in coordination with DBES. Until DBES certifies that a participating State has successfully completed testing, the State must use another approved method to transmit production files.

FTP test procedures are available on the State web (http://199.221.111.170/program/Es202). The testing process includes the following steps:

1. A State must assign an appropriate SunGard data set name that includes the correct initials.

2. A State must allocate appropriate disk space and formatting (record length and blocksize) at SunGard.

3. After uploading the test file to SunGard, a State must send an QCEW Data Transmittal form to national office staff involved in testing. An e-mail group (202ftp) has been set up for such purposes. The form should identify the file as a test file. The State should NOT send the transmittal form to the e-mail group used for production files (EQUI_Submittals).

States may not switch from an approved transmittal method to FTP without successfully completing FTP testing because there can be unexpected technical difficulties involved in setting connections and then uploading a fully usable file to SunGard. A State must successfully upload a complete and properly formatted test file via FTP before relying on FTP to transmit a timely deliverable or update file in production.

FTP transmittal may be used for initial quarterly deliverable EQUI files, correction files, or subset files. Very large States, however, may find this method impractical for their quarterly deliverable. The practical limits of file size and uploading time can be explored in the testing phase. States intending to use FTP for transmitting production files should complete testing at least two weeks before the due date for the quarterly deliverable.

12.3.2 Using the QCEW Program Data Transmittal Form

The QCEW Program Data Transmittal Form (Exhibit 12A) was designed to be stored and filled out electronically in Microsoft Word. (You may optionally print a blank form and complete it by hand.) The following procedures give some general guidelines for using the form. Please read the instructions before using or attempting to open the form file.

Acquiring the Transmittal Form

The EQUI data transmittal form is available via the Stateweb server. Connect to the Stateweb (http://199.221.111.170/program/Es202). On the left pane under the topic heading “QCEW Forms” select “EQUI Transmittal Form.” A read only version of the EQUI data transmittal template (*.dot) will open.

Saving the .DOT File

Before using the template, it is important to save it to your hard drive in the same location as your other user templates. To determine the correct drive, open Microsoft Word. Choose Options from the Tools menu and click on the tabbed dialog box named File Locations. Look to see which directory is specified for User Templates. Save the EQUI Transmittal file template in the same directory on your hard drive.

Accessing the Transmittal Form

To access the transmittal form in Microsoft Word, select File from the menu bar and then select New from the drop-down menu. (Do not use the New button on the Word toolbar.) We recommend that you open the file in this manner rather than using the possible alternate methods: choosing Open from the drop-down, using the New or Open button on the toolbar, or opening it from within Windows Explorer. This is an important step because these alternative methods may not allow you to save the form correctly after you fill it out. Choosing New from the file menu allows you to eventually save the completed form as a Microsoft Word document (.DOC) file. By saving the completed form as a document file, you preserve the original template for future use.

Completing the Transmittal Form

The transmittal form is fairly self-explanatory. In general, when filling out the form, make sure that only one box is checked in items with multiple check boxes. If you check “Other” box, be sure to include the appropriate information in the space provided. Finally, if additional information is helpful for any specific item or the file in general, provide the information in the “Remarks” section of the form. (For example, if the file is a test file.)

Saving the Transmittal Form

The State should save a copy of the completed form. This may be done electronically by saving it as a Microsoft Word document (.DOC). (Saving the form as a document rather than a template ensures that the original template is preserved on your hard drive for future use.) If it is saved electronically, we recommend that you use a file naming convention that identifies the year, quarter, and number of submittals within the quarter (1^st tape, revised tape, etc.). For example, the state of Virginia may save its first EQUI transmittal form for 2008/4 as EQUI2008‑4VA1.doc, where "…VA1" would indicate Virginia's first submittal. Subsequent submittals would be labeled "…VA2", "…VA3", etc. Alternately, a State may print and save a hard copy of the completed form.

Printing the Transmittal Form

After completing the form, print one (or more) copies using MS Word’s normal printing features. As a technical note, the form was created to print on one page using an HP printer and printer driver. Various other printers and/or drivers could potentially affect the appearance of the printed form. The form is designed to fit on one page.

Sending the Form

Send the form as appropriate, based on the method of data transmittal. For instance, if you are submitting directly to SunGard, remember to enclose a copy of the form with the tape and send a copy of the form to DBES, preferably via e-mail to "EQUI_Submittals." Regional offices may want a copy of the transmittal form as well.

EXHIBIT 12A QCEW Program Data Transmittal Form

12.4 Sending Test Files When Making System Changes

BLS-Washington strongly recommends that test EQUI files be submitted to BLS-Washington at least several weeks in advance of the deliverable whenever a State implements a new processing system or makes a major change to an existing processing system. (Making a normal version change, such as changing from EXPO Version 5.1 to Version 5.2, would not be considered a major change and would not require a test tape.)

The reason for the submittal of the test file is to determine if the new or changed processing system adversely affects the deliverable so that any identified problems can be corrected before the deliverable is submitted. Early identification of problems will make it more likely that corrective action can be taken before the actual QCEW data are needed for tabulation. The test files will not be stored on the databases at BLS-Washington, and corrections will not be processed against the test files.

The tape must contain all regular data elements so that the test file may be properly screened by the BLS EQUI processing system.

The regional office should notify BLS-Washington of any changes in the State's processing that might require the submittal of a test file and of the approximate mailing date and method of transmittal of this file.

12.5 File Retention

State systems should provide the means for States to easily archive and retain EQUI files as well as other files, including the oldest quarters of data that "roll off" the Micro file and the Macro file.

It is essential that States keep a copy of every EQUI file sent to BLS, whether it is the quarterly deliverable or a correction/cleanup file. This is because EQUI files can be lost or damaged in transmission and may need to be replaced. State systems will generate correction records by identifying all changes made to the data since the last EQUI file was generated. However, this tracking of data changes can be thrown off if the previous file was lost and needs to be replaced. Therefore, lost files should be replaced from backup copies, so State systems are not called upon to generate EQUI records a second time for data that were only changed once.

Current: BLS currently requires the following retention of files by the States:

• EQUI: retain in machine-readable form for a minimum of three years.

• ARS Control file: maintained until after completion of the next cycle for the same portion of the universe.

• Micro Files: retain in machine-readable form for a minimum of 3 years after submittal of data to BLS. It is strongly recommended that 5 years be retained.

• QCEW macro data: indefinitely in a usual manner

BLS should continue to retain data indefinitely.